yuxin_pms/合同应收实收报表.sql

select 
	crc.tenant_id, 
    hhb.dept_id '房源所属门店ID', house_sd.name '房源所属门店名称', hhr.address '房源地址', hhr.house_area '房源面积',
    crc_sd.id '合同所属门店/部门ID', crc_sd.name '合同所属门店/部门',
    crc.id '合同ID', crc.contract_no '合同编号', cri.name '租客姓名', cri.phone '租客手机号',
    sd.name '费用类型', detail.original_money '费用金额', detail.attribution_type '费用承担方', 
    detail.begin_time '费用周期（开始）', detail.end_time '费用周期（结束）',
    detail.fee_status '收付款状态', detail.predict_time '应收付时间', detail.overdue '逾期天数', detail.practical_time '实收付时间', 
	detail.payment_account_type '支付方式', detail.payment_account '支付账号', detail.affirm_emp_id '账单经办人ID', affirm_emp.name '账单经办人',
	detail.affirm_time '账单提交时间'
from yuxin_contract.cont_renter_contract crc
left join yuxin_house.hse_house_room hhr on hhr.is_delete=0 and hhr.id=crc.house_id
left join yuxin_house.hse_house_base hhb on hhb.is_delete=0 and hhb.id=crc.house_id
left join yuxin_setting.setting_department house_sd on house_sd.id=hhb.dept_id and house_sd.is_delete=0
left join yuxin_house.hse_community hc on hc.id=hhb.community_id and hc.is_delete=0
left join yuxin_setting.setting_employee_dept crc_ed on crc_ed.emp_id=crc.maintainer_id and crc_ed.is_delete=0
left join yuxin_setting.setting_department crc_sd on crc_sd.id=crc_ed.dept_id and crc_sd.is_delete=0
left join yuxin_contract.cont_renter_info cri on cri.is_delete=0 and cri.customer_type=1 and cri.contract_id=crc.id
left join (
    select bd.biz_id, bd.fee_subject_id, bd.original_money, bd.attribution_type, bd.begin_time, bd.end_time,
    	bd.fee_status, bd.predict_time, bd.practical_time, 
    	bd.payment_account_type, bd.payment_account, bd.affirm_emp_id, bd.affirm_time,
    	if(bd.practical_time>bd.predict_time, DATEDIFF(bd.practical_time, bd.predict_time)+1, '-') 'overdue'
    from yuxin_finance.fin_finance_bill_detail bd
    where bd.is_valid=1 and bd.is_delete=0 and bd.biz_type=2 and bd.fee_direction=1
) detail on detail.biz_id=crc.id
left join yuxin_setting.setting_dictionary sd on sd.id=detail.fee_subject_id
left join yuxin_setting.setting_employee_info affirm_emp on affirm_emp.id=detail.affirm_emp_id and affirm_emp.is_delete=0