SQLize | PHPize | SQLtest

SELECT A.branch_code, COUNT(DISTINCT D.user_code) AS fc_co, -- daily Y,B,H COALESCE(Y.new_disburse_acc,0) AS daily_new_disburse_acc, COALESCE(Y.new_disburse_amt,0) AS daily_new_disburse_amt, COALESCE(B.paid_off_acc,0) AS daily_paid_off_acc, COALESCE(B.paid_off_amt,0) AS daily_paid_off_amt, COALESCE(B.paid_off_mix_acc,0) AS daily_paid_off_mix_acc, COALESCE(B.paid_off_mix_amt,0) AS daily_paid_off_mix_amt, (COALESCE(B.paid_off_acc,0) + COALESCE(B.paid_off_mix_acc,0)) AS daily_total_paid_off_acc, (COALESCE(B.paid_off_amt,0) + COALESCE(B.paid_off_mix_amt,0)) AS daily_total_paid_off_amt, COALESCE(H.total_income_collection_amount,0) AS daily_total_income_collection_amount, ((COALESCE(Y.new_disburse_amt,0) - (COALESCE(B.paid_off_amt,0) + COALESCE(B.paid_off_mix_amt,0))) - COALESCE(H.total_income_collection_amount,0)) AS daily_cash_out_amount, -- monthly Z,C,G COALESCE(Z.new_disburse_acc,0) AS fromto_date_new_disburse_acc, COALESCE(Z.new_disburse_amt,0) AS fromto_date_new_disburse_amt, COALESCE(C.paid_off_acc,0) AS fromto_date_paid_off_acc, COALESCE(C.paid_off_amt,0) AS fromto_date_paid_off_amt, COALESCE(C.paid_off_mix_acc,0) AS fromto_date_paid_off_mix_acc, COALESCE(C.paid_off_mix_amt,0) AS fromto_date_paid_off_mix_amt, (COALESCE(C.paid_off_acc,0) + COALESCE(C.paid_off_mix_acc,0)) AS fromto_date_total_paid_off_acc, (COALESCE(C.paid_off_amt,0) + COALESCE(C.paid_off_mix_amt,0)) AS fromto_date_total_paid_off_amt, COALESCE(G.total_income_collection_amount,0) AS fromto_date_total_income_collection_amount, ((COALESCE(Z.new_disburse_amt,0) - (COALESCE(C.paid_off_amt,0) + COALESCE(C.paid_off_mix_amt,0))) - COALESCE(G.total_income_collection_amount,0)) AS fromto_date_cash_out_amount FROM site_branch A -- current date -> to_date LEFT JOIN ( SELECT A.branch_code, COUNT(DISTINCT A.co_code) AS fc_co, SUM(IF(A.paid_status = "active", 1,0)) AS new_disburse_acc, SUM(IF(A.paid_status = "active", A.principal_balance,0)) AS new_disburse_amt, SUM(IF(A.paid_off_account > 0, 1,0)) AS paid_off_acc, SUM(IF(A.paid_off_amount > 0, A.paid_off_amount,0)) AS paid_off_amt, SUM(IF(A.paid_status = "is_mixed_schedule", 1,0)) AS paid_off_mix_acc, SUM(IF(A.paid_status = "is_mixed_schedule", A.paid_off_mix,0)) AS paid_off_mix_amt FROM ( SELECT * FROM( SELECT B.branch_code, A.user_code AS co_code, D.contract_sn, D.principal_balance, D.finish_time, D.last_disbursed_time, D.state, "No" AS is_mixed_schedule, 0 AS new_disbursed_principal, 0 AS paid_off_mix, IF(D.state >= 99,"no_mixed_schedule","active") AS paid_status, 0 AS paid_off_account, 0 AS paid_off_amount FROM site_branch B LEFT JOIN um_user A ON B.uid = A.branch_id LEFT JOIN loan_contract D ON A.uid = D.co_id LEFT JOIN ( SELECT A.uid, A.client_obj_guid, B.loan_cycle_type, A.create_time, A.disbursed_principal, B.is_repay_old_loan FROM loan_contract A LEFT JOIN member_credit_request B ON B.uid = A.credit_request_id INNER JOIN (SELECT MAX(uid) AS uid, client_obj_guid FROM loan_contract GROUP BY client_obj_guid) AS C ON C.uid = A.uid WHERE DATE_FORMAT(A.create_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') -- to_date ) E ON D.client_obj_guid = E.client_obj_guid WHERE DATE_FORMAT(D.last_disbursed_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04", '%Y-%m-%d') AND D.state >= 30 UNION ALL SELECT B.branch_code, A.user_code AS co_code, D.contract_sn, D.principal_balance, D.finish_time, D.last_disbursed_time, D.state, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0 , 'Yes', 'No') AS is_mixed_schedule, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0, D.disbursed_principal, 0) AS new_disbursed_principal, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0, G.principal, 0) AS paid_off_mix, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0, 'is_mixed_schedule', 'no_mixed_schedule') AS paid_status, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0 , 0, 1) AS paid_off_account, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0, 0, G.principal) AS paid_off_amount FROM site_branch B LEFT JOIN um_user A ON B.uid = A.branch_id LEFT JOIN loan_contract D ON A.uid = D.co_id LEFT JOIN ( SELECT A.uid, A.client_obj_guid, B.loan_cycle_type, A.create_time, A.disbursed_principal, B.is_repay_old_loan FROM loan_contract A LEFT JOIN member_credit_request B ON B.uid = A.credit_request_id INNER JOIN (SELECT MAX(uid) AS uid, client_obj_guid FROM loan_contract GROUP BY client_obj_guid) AS C ON C.uid = A.uid WHERE DATE_FORMAT(A.create_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') -- to_date ) E ON D.client_obj_guid = E.client_obj_guid LEFT JOIN ( SELECT A.uid, A.contract_id, SUM(IF(DATE_FORMAT(B.finish_time, '%Y-%m-%d') = DATE_FORMAT(A.business_time, '%Y-%m-%d'), A.principal,0)) AS principal, C.last_principal, C.last_interest AS interest FROM pic_bean.loan_log_collection A INNER JOIN pic_bean.loan_contract B ON A.contract_id = B.uid LEFT JOIN ( SELECT A.uid, A.contract_id, A.principal AS last_principal, A.interest AS last_interest FROM pic_bean.loan_log_collection A INNER JOIN pic_bean.loan_contract B ON A.contract_id = B.uid WHERE DATE_FORMAT(B.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01", '%Y-%m-%d') AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') -- from_date and to_date AND TRIM(A.remark) IN ('Pre-Repayment', 'Repayment') AND A.uid IN ( SELECT MAX(Y.uid) AS uid FROM pic_bean.loan_log_collection Y INNER JOIN pic_bean.loan_contract Z ON Y.contract_id = Z.uid WHERE DATE_FORMAT(Z.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01", '%Y-%m-%d') AND DATE_FORMAT(Z.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') AND DATE_FORMAT(Y.business_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01", '%Y-%m-%d') AND DATE_FORMAT(Y.business_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') -- from_date and to_date AND TRIM(Y.remark) IN ('Pre-Repayment', 'Repayment') GROUP BY Y.contract_id ) ) C ON C.contract_id = B.uid WHERE DATE_FORMAT(B.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01", '%Y-%m-%d') AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') -- from_date and to_date AND TRIM(A.remark) IN ('Pre-Repayment', 'Repayment') GROUP BY B.uid ) AS G ON G.contract_id = D.uid WHERE DATE_FORMAT(D.finish_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04", '%Y-%m-%d') AND D.state = 100 ) A -- GROUP BY A.contract_sn ) A GROUP BY A.branch_code ) AS B ON B.branch_code = A.branch_code -- as from date and to date LEFT JOIN ( SELECT A.branch_code, COUNT(DISTINCT A.co_code) AS fc_co, SUM(IF(A.paid_status = "active", 1,0)) AS new_disburse_acc, SUM(IF(A.paid_status = "active", A.principal_balance,0)) AS new_disburse_amt, SUM(IF(A.paid_off_account > 0, 1,0)) AS paid_off_acc, SUM(IF(A.paid_off_amount > 0, A.paid_off_amount,0)) AS paid_off_amt, SUM(IF(A.paid_status = "is_mixed_schedule", 1,0)) AS paid_off_mix_acc, SUM(IF(A.paid_status = "is_mixed_schedule", A.paid_off_mix,0)) AS paid_off_mix_amt FROM ( SELECT * FROM( SELECT B.branch_code, A.user_code AS co_code, D.contract_sn, D.principal_balance, D.finish_time, D.last_disbursed_time, D.state, "No" AS is_mixed_schedule, 0 AS new_disbursed_principal, 0 AS paid_off_mix, IF(D.state >= 99,"no_mixed_schedule","active") AS paid_status, 0 AS paid_off_account, 0 AS paid_off_amount FROM site_branch B LEFT JOIN um_user A ON B.uid = A.branch_id LEFT JOIN loan_contract D ON A.uid = D.co_id LEFT JOIN ( SELECT A.uid, A.client_obj_guid, B.loan_cycle_type, A.create_time, A.disbursed_principal, B.is_repay_old_loan FROM loan_contract A LEFT JOIN member_credit_request B ON B.uid = A.credit_request_id INNER JOIN (SELECT MAX(uid) AS uid, client_obj_guid FROM loan_contract GROUP BY client_obj_guid) AS C ON C.uid = A.uid WHERE DATE_FORMAT(A.create_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') -- to_date ) E ON D.client_obj_guid = E.client_obj_guid WHERE DATE_FORMAT(D.last_disbursed_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01", '%Y-%m-%d') AND DATE_FORMAT(D.last_disbursed_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') AND D.state >= 30 UNION ALL SELECT B.branch_code, A.user_code AS co_code, D.contract_sn, D.principal_balance, D.finish_time, D.last_disbursed_time, D.state, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0 , 'Yes', 'No') AS is_mixed_schedule, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0, D.disbursed_principal, 0) AS new_disbursed_principal, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0, G.principal, 0) AS paid_off_mix, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0, 'is_mixed_schedule', 'no_mixed_schedule') AS paid_status, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0 , 0, 1) AS paid_off_account, IF(E.uid > D.uid AND cbs_report.pic_find_mixed_schedule_loan(D.uid) > 0, 0, G.principal) AS paid_off_amount FROM site_branch B LEFT JOIN um_user A ON B.uid = A.branch_id LEFT JOIN loan_contract D ON A.uid = D.co_id LEFT JOIN ( SELECT A.uid, A.client_obj_guid, B.loan_cycle_type, A.create_time, A.disbursed_principal, B.is_repay_old_loan FROM loan_contract A LEFT JOIN member_credit_request B ON B.uid = A.credit_request_id INNER JOIN (SELECT MAX(uid) AS uid, client_obj_guid FROM loan_contract GROUP BY client_obj_guid) AS C ON C.uid = A.uid WHERE DATE_FORMAT(A.create_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') -- to_date ) E ON D.client_obj_guid = E.client_obj_guid LEFT JOIN ( SELECT A.uid, A.contract_id, SUM(IF(DATE_FORMAT(B.finish_time, '%Y-%m-%d') = DATE_FORMAT(A.business_time, '%Y-%m-%d'), A.principal,0)) AS principal, C.last_principal, C.last_interest AS interest FROM pic_bean.loan_log_collection A INNER JOIN pic_bean.loan_contract B ON A.contract_id = B.uid LEFT JOIN ( SELECT A.uid, A.contract_id, A.principal AS last_principal, A.interest AS last_interest FROM pic_bean.loan_log_collection A INNER JOIN pic_bean.loan_contract B ON A.contract_id = B.uid WHERE DATE_FORMAT(B.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01", '%Y-%m-%d') AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') -- from_date and to_date AND TRIM(A.remark) IN ('Pre-Repayment', 'Repayment') AND A.uid IN ( SELECT MAX(Y.uid) AS uid FROM pic_bean.loan_log_collection Y INNER JOIN pic_bean.loan_contract Z ON Y.contract_id = Z.uid WHERE DATE_FORMAT(Z.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01", '%Y-%m-%d') AND DATE_FORMAT(Z.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') AND DATE_FORMAT(A.business_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01", '%Y-%m-%d') AND DATE_FORMAT(A.business_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') -- from_date and to_date AND TRIM(Y.remark) IN ('Pre-Repayment', 'Repayment') GROUP BY Y.contract_id ) ) C ON C.contract_id = B.uid WHERE DATE_FORMAT(B.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01", '%Y-%m-%d') AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') -- from_date and to_date AND TRIM(A.remark) IN ('Pre-Repayment', 'Repayment') GROUP BY B.uid ) AS G ON G.contract_id = D.uid WHERE DATE_FORMAT(D.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01", '%Y-%m-%d') AND DATE_FORMAT(D.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') AND D.state = 100 ) A -- GROUP BY A.contract_sn ) A GROUP BY A.branch_code ) AS C ON C.branch_code = A.branch_code LEFT JOIN um_user D ON D.branch_id = A.uid LEFT JOIN ( SELECT B.branch_id, COUNT(A.contract_sn) AS new_disburse_acc, SUM(A.disbursed_principal) AS new_disburse_amt FROM loan_contract A LEFT JOIN um_user B ON B.uid = A.co_id WHERE DATE_FORMAT(A.last_disbursed_time,"%Y-%m-%d") >= DATE_FORMAT("2024-07-01","%Y-%m-%d") AND DATE_FORMAT(A.last_disbursed_time,"%Y-%m-%d") <= DATE_FORMAT("2024-07-04","%Y-%m-%d") AND A.state >= 30 GROUP BY B.branch_id ) AS Z ON Z.branch_id = A.uid LEFT JOIN ( SELECT B.branch_id, COUNT(A.contract_sn) AS new_disburse_acc, SUM(A.disbursed_principal) AS new_disburse_amt FROM loan_contract A LEFT JOIN um_user B ON B.uid = A.co_id WHERE DATE_FORMAT(A.last_disbursed_time,"%Y-%m-%d") = DATE_FORMAT("2024-07-04","%Y-%m-%d") AND A.state >= 30 GROUP BY B.branch_id ) AS Y ON Y.branch_id = A.uid LEFT JOIN ( SELECT C.uid AS branch_id, C.branch_code, SUM( collection_interest + collection_operation_fee + collected_loan_fee + collected_admin_fee + collection_real_estate_fee + collected_lock_period_payoff_fee + collected_lock_period_prepayment_fee + collected_commissions_refinance_fee + collected_charge_on_lost_receipt + collected_cadastral_reg_fee + collected_others_fee + collection_penalty ) AS total_income_collection_amount FROM um_user B LEFT JOIN ( SELECT IFNULL(E.user_code,"no_user") AS user_code, IFNULL(E.collected_principal, 0) AS collection_principal, (IFNULL(F.paid_off_principal, 0) - IFNULL(F.paid_off_mixed_schedule_principal, 0)) AS paid_off_principal, IFNULL(F.paid_off_mixed_schedule_principal, 0) AS paid_off_mixed_schedule_principal, ( IFNULL(E.collected_interest, 0) + IFNULL(F.paid_off_interest, 0) ) AS collection_interest, IFNULL(E.collected_operation_fee, 0) AS collection_operation_fee, IFNULL(E.collected_loan_fee, 0) AS collected_loan_fee, IFNULL(E.collected_admin_fee, 0) AS collected_admin_fee, IFNULL(E.collected_real_estate_fee, 0) AS collection_real_estate_fee, IFNULL(K.lock_period_payoff_fee, 0) AS collected_lock_period_payoff_fee, IFNULL(K.lock_period_prepayment_fee, 0) AS collected_lock_period_prepayment_fee, IFNULL(K.commissions_refinance_fee, 0) AS collected_commissions_refinance_fee, IFNULL(K.charge_on_lost_receipt, 0) AS collected_charge_on_lost_receipt, IFNULL(K.cadastral_reg_fee, 0) AS collected_cadastral_reg_fee, IFNULL(K.other_fee, 0) AS collected_others_fee, (IFNULL(E.collected_penalty, 0) + IFNULL(I.write_off_principal, 0)) AS collection_penalty, (IFNULL(I.write_off_principal, 0) + IFNULL(I.write_off_interest, 0)) AS collection_write_off_amount, ( IFNULL(E.collected_principal, 0) + IFNULL(F.paid_off_principal, 0) + IFNULL(E.collected_interest, 0) + IFNULL(F.paid_off_interest, 0) + IFNULL(I.write_off_interest, 0) + IFNULL(E.collected_operation_fee, 0) + IFNULL(E.collected_loan_fee, 0) + IFNULL(E.collected_admin_fee, 0) + IFNULL(E.collected_real_estate_fee, 0) + IFNULL(K.lock_period_payoff_fee, 0) + IFNULL(K.lock_period_prepayment_fee, 0) + IFNULL(K.commissions_refinance_fee, 0) + IFNULL(K.charge_on_lost_receipt, 0) + IFNULL(K.cadastral_reg_fee, 0) + IFNULL(K.other_fee, 0) + IFNULL(E.collected_penalty, 0) + IFNULL(I.write_off_principal, 0) ) AS total_collection_amount FROM ( SELECT user_code, currency, SUM(collected_principal) AS collected_principal, SUM(collected_interest) AS collected_interest, SUM(collected_operation_fee) AS collected_operation_fee, SUM(collected_real_estate_fee) AS collected_real_estate_fee, SUM(collected_penalty) AS collected_penalty, SUM(collected_loan_fee) AS collected_loan_fee, SUM(collected_admin_fee) AS collected_admin_fee FROM ( SELECT user_code, "USD" currency, 0 AS collected_principal, 0 AS collected_interest, 0 AS collected_operation_fee, 0 AS collected_real_estate_fee, 0 AS collected_penalty, 0 AS collected_loan_fee, 0 AS collected_admin_fee FROM um_user WHERE user_status = 1 AND user_code NOT IN ('TN800', 'timco') AND user_position IN ('credit_officer', 'chief_credit_officer') UNION SELECT "no_user" user_code, "USD" currency, 0 AS collected_principal, 0 AS collected_interest, 0 AS collected_operation_fee, 0 AS collected_real_estate_fee, 0 AS collected_penalty, 0 AS collected_loan_fee, 0 AS collected_admin_fee UNION SELECT IFNULL(um.user_code,"no_user") AS user_code, llc.currency, SUM(cbs_report.exchange_amount(llc.currency, (llc.principal * llc.delta_flag), llc.business_time, 0)) AS collected_principal, SUM(cbs_report.exchange_amount(llc.currency, (llc.interest * llc.delta_flag), llc.business_time, 0)) AS collected_interest, SUM(cbs_report.exchange_amount(llc.currency, (llc.operation_fee * llc.delta_flag), llc.business_time, 0)) AS collected_operation_fee, SUM(cbs_report.exchange_amount(llc.currency, (llc.cci_fee * llc.delta_flag), llc.business_time, 0)) AS collected_real_estate_fee, SUM(cbs_report.exchange_amount(llc.currency, (llc.penalty * llc.delta_flag), llc.business_time, 0)) AS collected_penalty, SUM(cbs_report.exchange_amount(llc.currency, (llc.loan_fee * llc.delta_flag), llc.business_time, 0)) AS collected_loan_fee, SUM(cbs_report.exchange_amount(llc.currency, (llc.admin_fee * llc.delta_flag), llc.business_time, 0)) AS collected_admin_fee FROM loan_log_collection llc LEFT JOIN um_user um ON llc.co_id = um.uid LEFT JOIN loan_contract lc ON llc.contract_id = lc.uid WHERE lc.state >= 20 AND DATE_FORMAT(llc.business_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND llc.uid NOT IN ( SELECT A.uid FROM loan_log_collection A INNER JOIN loan_contract B ON A.contract_id = B.uid WHERE B.state != - 100 AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND TRIM(A.remark) IN ('Pre-Repayment', 'Repayment') AND A.uid IN ( SELECT MAX(Y.uid) AS uid FROM loan_log_collection Y INNER JOIN loan_contract Z ON Y.contract_id = Z.uid WHERE DATE_FORMAT(Z.finish_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND DATE_FORMAT(Y.business_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND TRIM(Y.remark) IN ('Pre-Repayment', 'Repayment') GROUP BY Y.contract_id ) GROUP BY A.contract_id ) GROUP BY um.user_code) A GROUP BY A.user_code ) AS E LEFT JOIN ( -- user code have null SELECT user_code, currency, SUM(paid_off_principal) AS paid_off_principal, SUM(paid_off_interest) AS paid_off_interest, SUM(paid_off_mixed_schedule_principal) AS paid_off_mixed_schedule_principal, SUM(paid_off_mixed_schedule_interest) AS paid_off_mixed_schedule_interest FROM ( SELECT user_code, "USD" currency, 0 AS paid_off_principal, 0 AS paid_off_interest, 0 AS paid_off_mixed_schedule_principal, 0 AS paid_off_mixed_schedule_interest FROM um_user WHERE user_status = 1 AND user_code NOT IN ('TN800', 'timco') AND user_position IN ('credit_officer', 'chief_credit_officer') UNION SELECT "no_user" user_code, "USD" currency, 0 AS paid_off_principal, 0 AS paid_off_interest, 0 AS paid_off_mixed_schedule_principal, 0 AS paid_off_mixed_schedule_interest UNION SELECT IFNULL(D.user_code,"no_user") AS user_code, A.currency, SUM(IF(DATE_FORMAT(B.finish_time, '%Y-%m-%d') = DATE_FORMAT(A.business_time, '%Y-%m-%d'), cbs_report.exchange_amount(A.currency, (A.principal * A.delta_flag), B.finish_time, 0),0)) AS paid_off_principal, SUM(cbs_report.exchange_amount(A.currency, (C.last_interest * A.delta_flag), B.finish_time, 0)) AS paid_off_interest, SUM(IF(E.uid > B.uid AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') = DATE_FORMAT(A.business_time, '%Y-%m-%d') AND loan_cycle_type = 1, cbs_report.exchange_amount(A.currency, (A.principal * A.delta_flag), B.finish_time, 0), 0)) AS paid_off_mixed_schedule_principal, SUM(IF(E.uid > B.uid AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') = DATE_FORMAT(A.business_time, '%Y-%m-%d') AND loan_cycle_type = 1, cbs_report.exchange_amount(A.currency, (C.last_interest * A.delta_flag), B.finish_time, 0), 0)) AS paid_off_mixed_schedule_interest FROM loan_log_collection A INNER JOIN loan_contract B ON A.contract_id = B.uid LEFT JOIN ( SELECT A.uid, A.client_obj_guid, B.loan_cycle_type, DATE_FORMAT(A.create_time, '%Y-%m-%d') AS new_request_time FROM loan_contract A LEFT JOIN member_credit_request B ON B.uid = A.credit_request_id INNER JOIN (SELECT MAX(uid) AS uid, client_obj_guid FROM loan_contract GROUP BY client_obj_guid) AS C ON C.uid = A.uid WHERE DATE_FORMAT(A.create_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') ) E ON E.client_obj_guid = B.client_obj_guid LEFT JOIN um_user D ON A.co_id = D.uid LEFT JOIN ( SELECT A.uid, A.contract_id, A.principal AS last_principal, A.interest AS last_interest FROM pic_bean.loan_log_collection A INNER JOIN pic_bean.loan_contract B ON A.contract_id = B.uid WHERE DATE_FORMAT(B.finish_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04", '%Y-%m-%d') AND TRIM(A.remark) IN ('Pre-Repayment', 'Repayment') AND A.uid IN ( SELECT MAX(Y.uid) AS uid FROM pic_bean.loan_log_collection Y INNER JOIN pic_bean.loan_contract Z ON Y.contract_id = Z.uid WHERE DATE_FORMAT(Z.finish_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04", '%Y-%m-%d') AND DATE_FORMAT(Y.business_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04", '%Y-%m-%d') AND TRIM(Y.remark) IN ('Pre-Repayment', 'Repayment') GROUP BY Y.contract_id ) ) C ON C.contract_id = B.uid WHERE B.state != - 100 AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04", '%Y-%m-%d') AND TRIM(A.remark) IN ('Pre-Repayment', 'Repayment') GROUP BY D.user_code) AS A GROUP BY A.user_code ) AS F ON E.user_code = F.user_code COLLATE utf8_general_ci LEFT JOIN ( SELECT user_code, currency, SUM(write_off_principal) AS write_off_principal, SUM(write_off_interest) AS write_off_interest FROM ( SELECT user_code, "USD" currency, 0 AS write_off_principal, 0 AS write_off_interest FROM um_user WHERE user_status = 1 AND user_code NOT IN ('TN800', 'timco') AND user_position IN ('credit_officer', 'chief_credit_officer') UNION SELECT "no_user" user_code, "USD" currency, 0 AS write_off_principal, 0 AS write_off_interest UNION SELECT IFNULL(B.user_code,"no_user") AS user_code, A.currency, SUM(A.write_off_principal) AS write_off_principal, SUM(A.write_off_interest) AS write_off_interest FROM ( SELECT l.co_id, r.currency, SUM(cbs_report.exchange_amount(r.currency, r.recovery_principal, r.update_time, 0)) AS write_off_principal, SUM(cbs_report.exchange_amount(r.currency, r.recovery_interest, r.update_time, 0)) AS write_off_interest FROM biz_loan_writtenoff_recovery r INNER JOIN biz_loan_writtenoff w ON w.contract_id = r.contract_id INNER JOIN loan_contract l ON l.uid = w.contract_id WHERE DATE_FORMAT(r.update_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND w.state <> -1 GROUP BY l.co_id UNION SELECT l.co_id, r.currency, SUM(cbs_report.exchange_amount(r.currency, r.recovery_principal, r.update_time, 0)) AS write_off_principal, SUM(cbs_report.exchange_amount(r.currency, r.recovery_interest, r.update_time, 0)) AS write_off_interest FROM old_system_loan_written_off_recovery r INNER JOIN old_system_loan_written_off w ON w.uid = r.old_loan_id INNER JOIN loan_contract l ON w.contract_number = l.contract_sn WHERE DATE_FORMAT(r.update_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04",'%Y-%m-%d') GROUP BY l.co_id ) AS A LEFT JOIN um_user AS B ON A.co_id = B.uid GROUP BY B.user_code) AS A GROUP BY A.user_code ) AS I ON E.user_code = I.user_code LEFT JOIN ( SELECT user_code, currency, SUM(lock_period_payoff_fee) AS lock_period_payoff_fee, SUM(lock_period_prepayment_fee) AS lock_period_prepayment_fee, SUM(commissions_refinance_fee) AS commissions_refinance_fee, SUM(charge_on_lost_receipt) AS charge_on_lost_receipt, SUM(cadastral_reg_fee) AS cadastral_reg_fee, SUM(other_fee) AS other_fee FROM ( SELECT user_code, "USD" currency, 0 AS lock_period_payoff_fee, 0 AS lock_period_prepayment_fee, 0 AS commissions_refinance_fee, 0 AS charge_on_lost_receipt, 0 AS cadastral_reg_fee, 0 AS other_fee FROM um_user WHERE user_status = 1 AND user_code NOT IN ('TN800', 'timco') AND user_position IN ('credit_officer', 'chief_credit_officer') UNION SELECT "no_user" user_code, "USD" currency, 0 AS lock_period_payoff_fee, 0 AS lock_period_prepayment_fee, 0 AS commissions_refinance_fee, 0 AS charge_on_lost_receipt, 0 AS cadastral_reg_fee, 0 AS other_fee UNION SELECT IFNULL(C.user_code,"no_user") AS user_code, A.currency, SUM(IF(ifc_id = 1, cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS lock_period_payoff_fee, SUM(IF(ifc_id = 2, cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS lock_period_prepayment_fee, SUM(IF(ifc_id = 3, cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS commissions_refinance_fee, SUM(IF(ifc_id = 5, cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS charge_on_lost_receipt, SUM(IF(ifc_id = 6, cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS cadastral_reg_fee, SUM(IF(ifc_id NOT IN (1,2,3,5,6), cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS other_fee FROM biz_receive_ifc A INNER JOIN loan_contract B ON A.loan_contract_id = B.uid INNER JOIN um_user C ON A.co_id = C.uid WHERE DATE_FORMAT(A.update_time, '%Y-%m-%d') = DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND A.state = 100 GROUP BY C.user_code) AS A GROUP BY A.user_code ) AS K ON E.user_code = K.user_code GROUP BY E.user_code ) AS A ON A.user_code = B.user_code LEFT JOIN site_branch C ON C.uid = B.branch_id GROUP BY C.uid ) AS H ON H.branch_id = A.uid LEFT JOIN ( SELECT C.uid AS branch_id, C.branch_code, SUM( collection_interest + collection_operation_fee + collected_loan_fee + collected_admin_fee + collection_real_estate_fee + collected_lock_period_payoff_fee + collected_lock_period_prepayment_fee + collected_commissions_refinance_fee + collected_charge_on_lost_receipt + collected_cadastral_reg_fee + collected_others_fee + collection_penalty ) AS total_income_collection_amount FROM um_user B LEFT JOIN ( SELECT IFNULL(E.user_code,"no_user") AS user_code, IFNULL(E.collected_principal, 0) AS collection_principal, (IFNULL(F.paid_off_principal, 0) - IFNULL(F.paid_off_mixed_schedule_principal, 0)) AS paid_off_principal, IFNULL(F.paid_off_mixed_schedule_principal, 0) AS paid_off_mixed_schedule_principal, ( IFNULL(E.collected_interest, 0) + IFNULL(F.paid_off_interest, 0) ) AS collection_interest, IFNULL(E.collected_operation_fee, 0) AS collection_operation_fee, IFNULL(E.collected_loan_fee, 0) AS collected_loan_fee, IFNULL(E.collected_admin_fee, 0) AS collected_admin_fee, IFNULL(E.collected_real_estate_fee, 0) AS collection_real_estate_fee, IFNULL(K.lock_period_payoff_fee, 0) AS collected_lock_period_payoff_fee, IFNULL(K.lock_period_prepayment_fee, 0) AS collected_lock_period_prepayment_fee, IFNULL(K.commissions_refinance_fee, 0) AS collected_commissions_refinance_fee, IFNULL(K.charge_on_lost_receipt, 0) AS collected_charge_on_lost_receipt, IFNULL(K.cadastral_reg_fee, 0) AS collected_cadastral_reg_fee, IFNULL(K.other_fee, 0) AS collected_others_fee, (IFNULL(E.collected_penalty, 0) + IFNULL(I.write_off_principal, 0)) AS collection_penalty, (IFNULL(I.write_off_principal, 0) + IFNULL(I.write_off_interest, 0)) AS collection_write_off_amount, ( IFNULL(E.collected_principal, 0) + IFNULL(F.paid_off_principal, 0) + IFNULL(E.collected_interest, 0) + IFNULL(F.paid_off_interest, 0) + IFNULL(I.write_off_interest, 0) + IFNULL(E.collected_operation_fee, 0) + IFNULL(E.collected_loan_fee, 0) + IFNULL(E.collected_admin_fee, 0) + IFNULL(E.collected_real_estate_fee, 0) + IFNULL(K.lock_period_payoff_fee, 0) + IFNULL(K.lock_period_prepayment_fee, 0) + IFNULL(K.commissions_refinance_fee, 0) + IFNULL(K.charge_on_lost_receipt, 0) + IFNULL(K.cadastral_reg_fee, 0) + IFNULL(K.other_fee, 0) + IFNULL(E.collected_penalty, 0) + IFNULL(I.write_off_principal, 0) ) AS total_collection_amount FROM ( SELECT user_code, currency, SUM(collected_principal) AS collected_principal, SUM(collected_interest) AS collected_interest, SUM(collected_operation_fee) AS collected_operation_fee, SUM(collected_real_estate_fee) AS collected_real_estate_fee, SUM(collected_penalty) AS collected_penalty, SUM(collected_loan_fee) AS collected_loan_fee, SUM(collected_admin_fee) AS collected_admin_fee FROM ( SELECT user_code, "USD" currency, 0 AS collected_principal, 0 AS collected_interest, 0 AS collected_operation_fee, 0 AS collected_real_estate_fee, 0 AS collected_penalty, 0 AS collected_loan_fee, 0 AS collected_admin_fee FROM um_user WHERE user_status = 1 AND user_code NOT IN ('TN800', 'timco') AND user_position IN ('credit_officer', 'chief_credit_officer') UNION SELECT "no_user" user_code, "USD" currency, 0 AS collected_principal, 0 AS collected_interest, 0 AS collected_operation_fee, 0 AS collected_real_estate_fee, 0 AS collected_penalty, 0 AS collected_loan_fee, 0 AS collected_admin_fee UNION SELECT IFNULL(um.user_code,"no_user") AS user_code, llc.currency, SUM(cbs_report.exchange_amount(llc.currency, (llc.principal * llc.delta_flag), llc.business_time, 0)) AS collected_principal, SUM(cbs_report.exchange_amount(llc.currency, (llc.interest * llc.delta_flag), llc.business_time, 0)) AS collected_interest, SUM(cbs_report.exchange_amount(llc.currency, (llc.operation_fee * llc.delta_flag), llc.business_time, 0)) AS collected_operation_fee, SUM(cbs_report.exchange_amount(llc.currency, (llc.cci_fee * llc.delta_flag), llc.business_time, 0)) AS collected_real_estate_fee, SUM(cbs_report.exchange_amount(llc.currency, (llc.penalty * llc.delta_flag), llc.business_time, 0)) AS collected_penalty, SUM(cbs_report.exchange_amount(llc.currency, (llc.loan_fee * llc.delta_flag), llc.business_time, 0)) AS collected_loan_fee, SUM(cbs_report.exchange_amount(llc.currency, (llc.admin_fee * llc.delta_flag), llc.business_time, 0)) AS collected_admin_fee FROM loan_log_collection llc LEFT JOIN um_user um ON llc.co_id = um.uid LEFT JOIN loan_contract lc ON llc.contract_id = lc.uid WHERE lc.state >= 20 AND DATE_FORMAT(llc.business_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01",'%Y-%m-%d') AND DATE_FORMAT(llc.business_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND llc.uid NOT IN ( SELECT A.uid FROM loan_log_collection A INNER JOIN loan_contract B ON A.contract_id = B.uid WHERE B.state != - 100 AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01",'%Y-%m-%d') AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND TRIM(A.remark) IN ('Pre-Repayment', 'Repayment') AND A.uid IN ( SELECT MAX(Y.uid) AS uid FROM loan_log_collection Y INNER JOIN loan_contract Z ON Y.contract_id = Z.uid WHERE DATE_FORMAT(Z.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01",'%Y-%m-%d') AND DATE_FORMAT(Z.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND DATE_FORMAT(Y.business_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01",'%Y-%m-%d') AND DATE_FORMAT(Y.business_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND TRIM(Y.remark) IN ('Pre-Repayment', 'Repayment') GROUP BY Y.contract_id ) GROUP BY A.contract_id ) GROUP BY um.user_code ) A GROUP BY A.user_code ) AS E LEFT JOIN ( SELECT user_code, currency, SUM(paid_off_principal) AS paid_off_principal, SUM(paid_off_interest) AS paid_off_interest, SUM(paid_off_mixed_schedule_principal) AS paid_off_mixed_schedule_principal, SUM(paid_off_mixed_schedule_interest) AS paid_off_mixed_schedule_interest FROM ( SELECT user_code, "USD" currency, 0 AS paid_off_principal, 0 AS paid_off_interest, 0 AS paid_off_mixed_schedule_principal, 0 AS paid_off_mixed_schedule_interest FROM um_user WHERE user_status = 1 AND user_code NOT IN ('TN800', 'timco') AND user_position IN ('credit_officer', 'chief_credit_officer') UNION SELECT "no_user" user_code, "USD" currency, 0 AS paid_off_principal, 0 AS paid_off_interest, 0 AS paid_off_mixed_schedule_principal, 0 AS paid_off_mixed_schedule_interest UNION SELECT IFNULL(D.user_code,"no_user") AS user_code, A.currency, SUM(IF(DATE_FORMAT(B.finish_time, '%Y-%m-%d') = DATE_FORMAT(A.business_time, '%Y-%m-%d'), cbs_report.exchange_amount(A.currency, (A.principal * A.delta_flag), B.finish_time, 0),0)) AS paid_off_principal, SUM(cbs_report.exchange_amount(A.currency, (C.last_interest * A.delta_flag), B.finish_time, 0)) AS paid_off_interest, SUM(IF(E.uid > B.uid AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') = DATE_FORMAT(A.business_time, '%Y-%m-%d') AND loan_cycle_type = 1, cbs_report.exchange_amount(A.currency, (A.principal * A.delta_flag), B.finish_time, 0), 0)) AS paid_off_mixed_schedule_principal, SUM(IF(E.uid > B.uid AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') = DATE_FORMAT(A.business_time, '%Y-%m-%d') AND loan_cycle_type = 1, cbs_report.exchange_amount(A.currency, (C.last_interest * A.delta_flag), B.finish_time, 0), 0)) AS paid_off_mixed_schedule_interest FROM loan_log_collection A INNER JOIN loan_contract B ON A.contract_id = B.uid LEFT JOIN ( SELECT A.uid, A.client_obj_guid, B.loan_cycle_type, DATE_FORMAT(A.create_time, '%Y-%m-%d') AS new_request_time FROM loan_contract A LEFT JOIN member_credit_request B ON B.uid = A.credit_request_id INNER JOIN (SELECT MAX(uid) AS uid, client_obj_guid FROM loan_contract GROUP BY client_obj_guid) AS C ON C.uid = A.uid WHERE DATE_FORMAT(A.create_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04", '%Y-%m-%d') ) E ON E.client_obj_guid = B.client_obj_guid LEFT JOIN um_user D ON A.co_id = D.uid LEFT JOIN ( SELECT A.uid, A.contract_id, A.principal AS last_principal, A.interest AS last_interest FROM pic_bean.loan_log_collection A INNER JOIN pic_bean.loan_contract B ON A.contract_id = B.uid WHERE DATE_FORMAT(B.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01",'%Y-%m-%d') AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND TRIM(A.remark) IN ('Pre-Repayment', 'Repayment') AND A.uid IN ( SELECT MAX(Y.uid) AS uid FROM pic_bean.loan_log_collection Y INNER JOIN pic_bean.loan_contract Z ON Y.contract_id = Z.uid WHERE DATE_FORMAT(Z.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01",'%Y-%m-%d') AND DATE_FORMAT(Z.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND DATE_FORMAT(Y.business_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01",'%Y-%m-%d') AND DATE_FORMAT(Y.business_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND TRIM(Y.remark) IN ('Pre-Repayment', 'Repayment') GROUP BY Y.contract_id) ) C ON C.contract_id = B.uid WHERE B.state != - 100 AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01",'%Y-%m-%d') AND DATE_FORMAT(B.finish_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND TRIM(A.remark) IN ('Pre-Repayment', 'Repayment') GROUP BY D.user_code) AS A GROUP BY A.user_code ) AS F ON E.user_code = F.user_code LEFT JOIN ( SELECT user_code, currency, SUM(write_off_principal) AS write_off_principal, SUM(write_off_interest) AS write_off_interest FROM ( SELECT user_code, "USD" currency, 0 AS write_off_principal, 0 AS write_off_interest FROM um_user WHERE user_status = 1 AND user_code NOT IN ('TN800', 'timco') AND user_position IN ('credit_officer', 'chief_credit_officer') UNION SELECT "no_user" user_code, "USD" currency, 0 AS write_off_principal, 0 AS write_off_interest UNION SELECT IFNULL(B.user_code,"no_user") AS user_code, A.currency, SUM(A.write_off_principal) AS write_off_principal, SUM(A.write_off_interest) AS write_off_interest FROM ( SELECT l.co_id, r.currency, SUM(cbs_report.exchange_amount(r.currency, r.recovery_principal, r.update_time, 0)) AS write_off_principal, SUM(cbs_report.exchange_amount(r.currency, r.recovery_interest, r.update_time, 0)) AS write_off_interest FROM biz_loan_writtenoff_recovery r INNER JOIN biz_loan_writtenoff w ON w.contract_id = r.contract_id INNER JOIN loan_contract l ON l.uid = w.contract_id WHERE DATE_FORMAT(r.update_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01",'%Y-%m-%d') AND DATE_FORMAT(r.update_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND w.state <> -1 GROUP BY l.co_id UNION SELECT l.co_id, r.currency, SUM(cbs_report.exchange_amount(r.currency, r.recovery_principal, r.update_time, 0)) AS write_off_principal, SUM(cbs_report.exchange_amount(r.currency, r.recovery_interest, r.update_time, 0)) AS write_off_interest FROM old_system_loan_written_off_recovery r INNER JOIN old_system_loan_written_off w ON w.uid = r.old_loan_id INNER JOIN loan_contract l ON w.contract_number = l.contract_sn WHERE DATE_FORMAT(r.update_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01",'%Y-%m-%d') AND DATE_FORMAT(r.update_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04",'%Y-%m-%d') GROUP BY l.co_id ) AS A INNER JOIN um_user AS B ON A.co_id = B.uid GROUP BY B.user_code) A GROUP BY A.user_code ) AS I ON E.user_code = I.user_code LEFT JOIN ( SELECT user_code, currency, SUM(lock_period_payoff_fee) AS lock_period_payoff_fee, SUM(lock_period_prepayment_fee) AS lock_period_prepayment_fee, SUM(commissions_refinance_fee) AS commissions_refinance_fee, SUM(charge_on_lost_receipt) AS charge_on_lost_receipt, SUM(cadastral_reg_fee) AS cadastral_reg_fee, SUM(other_fee) AS other_fee FROM ( SELECT user_code, "USD" currency, 0 AS lock_period_payoff_fee, 0 AS lock_period_prepayment_fee, 0 AS commissions_refinance_fee, 0 AS charge_on_lost_receipt, 0 AS cadastral_reg_fee, 0 AS other_fee FROM um_user WHERE user_status = 1 AND user_code NOT IN ('TN800', 'timco') AND user_position IN ('credit_officer', 'chief_credit_officer') UNION SELECT "no_user" user_code, "USD" currency, 0 AS lock_period_payoff_fee, 0 AS lock_period_prepayment_fee, 0 AS commissions_refinance_fee, 0 AS charge_on_lost_receipt, 0 AS cadastral_reg_fee, 0 AS other_fee UNION SELECT IFNULL(C.user_code,"no_user") AS user_code, A.currency, SUM(IF(ifc_id = 1, cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS lock_period_payoff_fee, SUM(IF(ifc_id = 2, cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS lock_period_prepayment_fee, SUM(IF(ifc_id = 3, cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS commissions_refinance_fee, SUM(IF(ifc_id = 5, cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS charge_on_lost_receipt, SUM(IF(ifc_id = 6, cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS cadastral_reg_fee, SUM(IF(ifc_id NOT IN (1,2,3,5,6), cbs_report.exchange_amount(A.currency, A.amount, A.update_time, 0), 0)) AS other_fee FROM biz_receive_ifc A INNER JOIN loan_contract B ON A.loan_contract_id = B.uid INNER JOIN um_user C ON A.co_id = C.uid WHERE DATE_FORMAT(A.update_time, '%Y-%m-%d') >= DATE_FORMAT("2024-07-01",'%Y-%m-%d') AND DATE_FORMAT(A.update_time, '%Y-%m-%d') <= DATE_FORMAT("2024-07-04",'%Y-%m-%d') AND A.state = 100 GROUP BY C.user_code) AS A GROUP BY A.user_code ) AS K ON E.user_code = K.user_code GROUP BY E.user_code ) A ON A.user_code = B.user_code LEFT JOIN site_branch C ON C.uid = B.branch_id GROUP BY C.uid ) AS G ON G.branch_id = A.uid WHERE D.user_position IN ('credit_officer', 'chief_credit_officer') AND D.user_status = 1 AND RIGHT(D.user_code, 6) <> "OFFICE" AND IF("All" = "All", A.branch_code IS NOT NULL, A.branch_code = "All") GROUP BY A.uid ORDER BY A.uid ASC