yuxin_pms/合同业务明细表.sql

select m.*,
    IF((_this_month.应收), (_this_month.应收), 0) '本月应收',
    IF((_this_month.已收), (_this_month.已收), 0) '本月已收',
    IF((_this_month.应付), (_this_month.应付), 0) '本月应付',
    IF((_this_month.已付), (_this_month.已付), 0) '本月已付',
    IF((_until_this_month.应收), (_until_this_month.应收), 0) '截止至本月应收',
    IF((_until_this_month.已收), (_until_this_month.已收), 0) '截止至本月已收',
    IF((_until_this_month.应付), (_until_this_month.应付), 0) '截止至本月应付',
    IF((_until_this_month.已付), (_until_this_month.已付), 0) '截止至本月已付',
    IF((_until_this_month.应收-_until_this_month.已收), (_until_this_month.应收-_until_this_month.已收), 0) '截止至本月待收',
    IF((_until_this_month.应付-_until_this_month.已付), (_until_this_month.应付-_until_this_month.已付), 0) '截止至本月待付',
    IF((_over_due.应收-_over_due.已收), (_over_due.应收-_over_due.已收), 0) '截止至本日逾期待收',
    IF((_over_due.应付-_over_due.已付), (_over_due.应付-_over_due.已付), 0) '截止至本日逾期待付',
    IF((_until_last_month.已收), (_until_last_month.已收), 0) '截止至上月已收',
	IF((_until_last_month.已付), (_until_last_month.已付), 0) '截止至上月已付',
    IF(_this_month_audited.收入, _this_month_audited.收入, 0) '本月审核通过收入',
    IF(_this_month_audited.支出, _this_month_audited.支出, 0) '本月审核通过支出',
    IF(_until_this_month_audited.收入, _until_this_month_audited.收入, 0) '截止至本月审核通过收入',
    IF(_until_this_month_audited.支出, _until_this_month_audited.支出, 0) '截止至本月审核通过支出',
    IF(_until_last_month_audited.收入, _until_last_month_audited.收入, 0) '截止至上月审核通过收入',
    IF(_until_last_month_audited.支出, _until_last_month_audited.支出, 0) '截止至上月审核通过支出',
    IF(_this_month_auditing.收入, _this_month_auditing.收入, 0) '本月待审核收入',
    IF(_this_month_auditing.支出, _this_month_auditing.支出, 0) '本月待审核支出',
    IF(_until_this_month_auditing.收入, _until_this_month_auditing.收入, 0) '截止至本月待审核收入',
    IF(_until_this_month_auditing.支出, _until_this_month_auditing.支出, 0) '截止至本月待审核支出'
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 '费用类型',
        IF((_total.应收), (_total.应收), 0) '应收总金额',
        IF((_total.应付), (_total.应付), 0) '应付总金额',
        _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',
            SUM(IF(bd.fee_direction=1,0,1)*bd.original_money) '应付',
            SUM(IF(bd.fee_direction=1,0,1)*bd.occurred_money) '已付',
            SUM(IF(bd.fee_direction=1,1,0)*bd.original_money) '应收',
            SUM(IF(bd.fee_direction=1,1,0)*bd.occurred_money) '已收'
        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
left join (
    select bd.biz_id, bd.fee_subject_id,
    	SUM(IF(bd.fee_direction=1,0,1)*bd.original_money) '应付',
    	SUM(IF(bd.fee_direction=1,0,1)*bd.occurred_money) '已付',
    	SUM(IF(bd.fee_direction=1,1,0)*bd.original_money) '应收',
    	SUM(IF(bd.fee_direction=1,1,0)*bd.occurred_money) '已收'
    from yuxin_finance.fin_finance_bill_detail bd
    where bd.is_valid=1 and bd.is_delete=0 and bd.biz_type=2 and MONTH(bd.predict_time) = MONTH(CURRENT_DATE()) AND YEAR(bd.predict_time) = YEAR(CURRENT_DATE())
    group by bd.biz_id, bd.fee_subject_id
) _this_month on _this_month.biz_id=m.合同ID and m.fee_subject_id=_this_month.fee_subject_id
left join (
    select bd.biz_id, bd.fee_subject_id,
    	SUM(IF(bd.fee_direction=1,0,1)*bd.original_money) '应付',
    	SUM(IF(bd.fee_direction=1,0,1)*bd.occurred_money) '已付',
    	SUM(IF(bd.fee_direction=1,1,0)*bd.original_money) '应收',
    	SUM(IF(bd.fee_direction=1,1,0)*bd.occurred_money) '已收'
    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.predict_time < CURRENT_DATE() and bd.not_occurred_money>0
    group by bd.biz_id, bd.fee_subject_id
) _over_due on _over_due.biz_id=m.合同ID and m.fee_subject_id=_over_due.fee_subject_id
left join (
    select bd.biz_id,
    	SUM(IF(bd.fee_direction=1,0,1)*bd.original_money) '应付',
    	SUM(IF(bd.fee_direction=1,0,1)*bd.occurred_money) '已付',
    	SUM(IF(bd.fee_direction=1,1,0)*bd.original_money) '应收',
    	SUM(IF(bd.fee_direction=1,1,0)*bd.occurred_money) '已收'
    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.practical_time < CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE)
    group by bd.biz_id
) _until_last_month on _until_last_month.biz_id=m.合同ID and m.fee_subject_id=_over_due.fee_subject_id
left join (
    select bd.biz_id, bd.fee_subject_id,
    	SUM(IF(bd.fee_direction=1,0,1)*bd.original_money) '应付',
    	SUM(IF(bd.fee_direction=1,0,1)*bd.occurred_money) '已付',
    	SUM(IF(bd.fee_direction=1,1,0)*bd.original_money) '应收',
    	SUM(IF(bd.fee_direction=1,1,0)*bd.occurred_money) '已收'
    from yuxin_finance.fin_finance_bill_detail bd
    where bd.is_valid=1 and bd.is_delete=0 and bd.biz_type=2 and MONTH(bd.predict_time) <= MONTH(CURRENT_DATE()) AND YEAR(bd.predict_time) <= YEAR(CURRENT_DATE())
    group by bd.biz_id, bd.fee_subject_id
) _until_this_month on _until_this_month.biz_id=m.合同ID and m.fee_subject_id=_over_due.fee_subject_id
left join (
    select bf.biz_id, sd.name, bd.fee_subject_id,
        SUM(IF(bd.fee_direction=1,1,0)*bf.total_money) '收入',
        SUM(IF(bd.fee_direction=1,0,1)*bf.total_money) '支出'
    from yuxin_bi.bi_bill_flow_detail bfd
    left join yuxin_finance.fin_finance_bill_flow bf on bf.id=bfd.flow_id and bf.biz_type=2
    left join yuxin_finance.fin_finance_bill_detail bd on bd.id=bfd.bill_detail_id and bf.biz_type=2
    left join yuxin_setting.setting_dictionary sd on sd.id=bd.fee_subject_id
    where bf.biz_id is not null and bf.audit_status=2 and MONTH(bd.audit_time) = MONTH(CURRENT_DATE()) AND YEAR(bd.audit_time) = YEAR(CURRENT_DATE())
    group by bf.biz_id, bd.fee_subject_id, sd.name
) _this_month_audited on _this_month_audited.biz_id= m.合同ID and m.fee_subject_id=_this_month_audited.fee_subject_id
left join (
    select bf.biz_id, sd.name, bd.fee_subject_id,
        SUM(IF(bd.fee_direction=1,1,0)*bf.total_money) '收入',
        SUM(IF(bd.fee_direction=1,0,1)*bf.total_money) '支出'
    from yuxin_bi.bi_bill_flow_detail bfd
    left join yuxin_finance.fin_finance_bill_flow bf on bf.id=bfd.flow_id and bf.biz_type=2
    left join yuxin_finance.fin_finance_bill_detail bd on bd.id=bfd.bill_detail_id and bf.biz_type=2
    left join yuxin_setting.setting_dictionary sd on sd.id=bd.fee_subject_id
    where bf.biz_id is not null and bf.audit_status=2 and MONTH(bd.audit_time) <= MONTH(CURRENT_DATE()) AND YEAR(bd.audit_time) <= YEAR(CURRENT_DATE())
    group by bf.biz_id, bd.fee_subject_id, sd.name
) _until_this_month_audited on _until_this_month_audited.biz_id= m.合同ID and m.fee_subject_id=_until_this_month_audited.fee_subject_id
left join (
    select bf.biz_id, sd.name, bd.fee_subject_id,
        SUM(IF(bd.fee_direction=1,1,0)*bf.total_money) '收入',
        SUM(IF(bd.fee_direction=1,0,1)*bf.total_money) '支出'
    from yuxin_bi.bi_bill_flow_detail bfd
    left join yuxin_finance.fin_finance_bill_flow bf on bf.id=bfd.flow_id and bf.biz_type=2
    left join yuxin_finance.fin_finance_bill_detail bd on bd.id=bfd.bill_detail_id and bf.biz_type=2
    left join yuxin_setting.setting_dictionary sd on sd.id=bd.fee_subject_id
    where bf.biz_id is not null and bf.audit_status=1 and MONTH(bd.audit_time) = MONTH(CURRENT_DATE()) AND YEAR(bd.audit_time) = YEAR(CURRENT_DATE())
    group by bf.biz_id, bd.fee_subject_id, sd.name
) _this_month_auditing on _this_month_auditing.biz_id= m.合同ID and m.fee_subject_id=_this_month_auditing.fee_subject_id
left join (
    select bf.biz_id, sd.name, bd.fee_subject_id,
        SUM(IF(bd.fee_direction=1,1,0)*bf.total_money) '收入',
        SUM(IF(bd.fee_direction=1,0,1)*bf.total_money) '支出'
    from yuxin_bi.bi_bill_flow_detail bfd
    left join yuxin_finance.fin_finance_bill_flow bf on bf.id=bfd.flow_id and bf.biz_type=2
    left join yuxin_finance.fin_finance_bill_detail bd on bd.id=bfd.bill_detail_id and bf.biz_type=2
    left join yuxin_setting.setting_dictionary sd on sd.id=bd.fee_subject_id
    where bf.biz_id is not null and bf.audit_status=1 and MONTH(bd.audit_time) <= MONTH(CURRENT_DATE()) AND YEAR(bd.audit_time) <= YEAR(CURRENT_DATE())
    group by bf.biz_id, bd.fee_subject_id, sd.name
) _until_this_month_auditing on _until_this_month_auditing.biz_id= m.合同ID and m.fee_subject_id=_until_this_month_auditing.fee_subject_id
left join (
    select bf.biz_id, sd.name, bd.fee_subject_id,
        SUM(IF(bd.fee_direction=1,1,0)*bf.total_money) '收入',
        SUM(IF(bd.fee_direction=1,0,1)*bf.total_money) '支出'
    from yuxin_bi.bi_bill_flow_detail bfd
    left join yuxin_finance.fin_finance_bill_flow bf on bf.id=bfd.flow_id and bf.biz_type=2
    left join yuxin_finance.fin_finance_bill_detail bd on bd.id=bfd.bill_detail_id and bf.biz_type=2
    left join yuxin_setting.setting_dictionary sd on sd.id=bd.fee_subject_id
    where bf.biz_id is not null and bf.audit_status=2 and MONTH(bd.audit_time) < CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE)
    group by bf.biz_id, bd.fee_subject_id, sd.name
) _until_last_month_audited on _until_last_month_audited.biz_id= m.合同ID and m.fee_subject_id=_until_last_month_audited.fee_subject_id
;