yuxin_pms/财务权责明细表.sql

select mm.*, dm.themonth, dm.money
from (
    select m.*, n.kind
    from (
        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 '合同编号', crc.maintainer_id '合同维护人ID', mt_emp.name '合同维护人',
            cri.name '租客姓名', crc.begin_time '合同开始日期', crc.end_time '合同结束日期', DATEDIFF(crc.end_time, crc.begin_time)+1 '签约天数',
            crc.`type` '合同类型', crc.sign_type '成交方式',
                TRIM(TRAILING ',' FROM CONCAT(
                    IF(crc.contract_status = 1 AND crc.contract_sub_status = 1, '待处理,', ''),
                    IF(crc.contract_status = 1 AND crc.contract_sub_status = 2 AND crc.sign_status = 1, '待租客签字,', ''),
                    IF(crc.contract_status = 1 AND crc.contract_sub_status = 2 AND crc.sign_status = 0, '带租客实名,', ''),
                    IF(crc.contract_status = 2 AND crc.transition_type != 1 AND LENGTH(trim(crc.`down_payment_info`)) > 1 AND JSON_EXTRACT(crc.`down_payment_info`, '$.status') = 0, '待付款,', ''),
                    IF(crc.contract_status = 2 AND crc.transition_type != 1 AND crc.is_sync_tenant = 1, '待租客确认,', ''),
                    IF(crc.contract_status = 2 AND crc.transition_type != 1 AND DATE(NOW()) < crc.begin_time, '即将搬入,', ''),
                    IF(crc.contract_status = 2 AND crc.transition_type != 1 AND LENGTH(trim(crc.`down_payment_info`)) > 1 AND (JSON_EXTRACT(crc.`down_payment_info`, '$.status') = 1 AND DATE(NOW()) >= crc.begin_time AND DATE(NOW()) < DATE_ADD(DATE(crc.end_time), INTERVAL 30 DAY) ), '租约中,', ''),
                    IF(crc.contract_status = 2 AND crc.transition_type != 1 AND crc.`begin_time` <= DATE(NOW()) AND DATE(NOW()) >= DATE_ADD(DATE(crc.end_time), INTERVAL 30 DAY) AND DATE(NOW())< DATE(crc.end_time), '即将到期,', ''),
                    IF(crc.contract_status = 2 AND crc.transition_type != 1 AND DATE(NOW()) >= DATE(crc.end_time), '已到期,', ''),
                    IF(crc.contract_status = 2 AND crc.transition_type = 1, '已续约,', ''),
                    IF(crc.contract_status = 3 AND crc.terminate_type = 1, '正常退,', ''),
                    IF(crc.contract_status = 3 AND crc.terminate_type = 2, '违约退,', ''),
                    IF(crc.contract_status = 4 AND crc.invalid_type = 1, '已作废,', ''),
                    IF(crc.contract_status = 4 AND crc.invalid_type = 2, '已拒绝,', '')
                )) '合同状态',
            crc.approval_status '审批状态',
            0 'splitter',
            _total.subject_name '费用类型',
            _total.fee_subject_id
        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 yuxin_setting.setting_employee_info sign_emp on sign_emp.id=crc.sign_emp_id and sign_emp.is_delete=0
        left join yuxin_setting.setting_employee_info mt_emp on mt_emp.id=crc.maintainer_id and mt_emp.is_delete=0
        left join (
            select bd.biz_id, bd.fee_subject_id, sd.name 'subject_name'
            from yuxin_finance.fin_finance_bill_detail bd
            left join yuxin_setting.setting_dictionary sd on sd.id=bd.fee_subject_id
            where bd.is_valid=1 and bd.is_delete=0 and bd.biz_type=2
            group by bd.biz_id, bd.fee_subject_id, sd.name
        ) _total on _total.biz_id= crc.id
        where _total.fee_subject_id is not null
    ) m
    cross join (
        select '应付' as 'kind' union all
        select '应收' as 'kind' union all
        select '实收' as 'kind' union all
        select '实付' as 'kind' union all
        select '待收' as 'kind' union all
        select '待付' as 'kind') n) mm
left join yuxin_bi.bi_bill_detail_month dm
    on dm.fee_subject_id=mm.fee_subject_id and
       dm.kind=mm.kind and
       dm.contract_id=mm.合同ID;