INSERT INTO temp_huifu_wallet_reconciliation(trade_serial_no, create_time, merchant_no, request_serial_no, trade_amount, trade_status, bank_trade_type, trade_start_time, trade_end_time, expend_account_no, expend_account_name, expend_bank_code, income_account_no, income_account_name, incomed_bank_code, income_cer_no, merchant_retain, remark, service_charge ) VALUES (#{item.tradeSerialNo}, NOW(), #{item.merchantNo}, #{item.requestSerialNo}, #{item.tradeAmount}, #{item.tradeStatus}, #{item.bankTradeType}, #{item.tradeStartTime}, #{item.tradeEndTime}, #{item.expendAccountNo}, #{item.expendAccountName}, #{item.expendBankCode}, #{item.incomeAccountNo}, #{item.incomeAccountName}, #{item.incomedBankCode}, #{item.incomeCerNo}, #{item.merchantRetain}, #{item.remark}, #{item.serviceCharge} ) INSERT INTO eb_huifu_wallet_reconciliation(trade_serial_no, create_time, merchant_no, trade_date, request_serial_no, trade_amount, trade_status, bank_trade_type, trade_start_time, trade_end_time, expend_account_no, expend_account_name, expend_bank_code, income_account_no, income_account_name, incomed_bank_code, income_cer_no, merchant_retain, remark, service_charge, expend_uni_id, income_uni_id) SELECT a.trade_serial_no, a.create_time, a.merchant_no, date_format(a.trade_start_time, '%Y%m%d') AS tradeDate, a.request_serial_no, a.trade_amount, a.trade_status, a.bank_trade_type, a.trade_start_time, a.trade_end_time, a.expend_account_no, a.expend_account_name, a.expend_bank_code, a.income_account_no, a.income_account_name, a.incomed_bank_code, a.income_cer_no, a.merchant_retain, a.remark, a.service_charge, b.uni_id, c.uni_id FROM temp_huifu_wallet_reconciliation a LEFT JOIN eb_huifu_wallet_account b ON b.wallet_account_no = a.expend_account_no LEFT JOIN eb_huifu_wallet_account c ON c.wallet_account_no = a.income_account_no WHERE NOT EXISTS(SELECT 1 FROM eb_huifu_wallet_reconciliation WHERE trade_date = date_format(a.trade_start_time, '%Y%m%d') AND trade_serial_no = a.trade_serial_no AND expend_account_no = a.expend_account_no AND income_account_no = a.income_account_no) UPDATE eb_huifu_wallet_reconciliation a ,temp_huifu_wallet_reconciliation b SET a.update_time = NOW() , a.trade_amount = b.trade_amount, a.trade_status = b.trade_status, a.bank_trade_type = b.bank_trade_type, a.trade_start_time = b.trade_start_time, a.trade_end_time = b.trade_end_time, a.expend_account_no = b.expend_account_no, a.expend_account_name = b.expend_account_name, a.expend_bank_code = b.expend_bank_code, a.income_account_no = b.income_account_no, a.income_account_name = b.income_account_name, a.incomed_bank_code = b.incomed_bank_code, a.income_cer_no = b.income_cer_no, a.merchant_retain = b.merchant_retain, a.remark = b.remark , a.service_charge = b.service_charge WHERE a.trade_serial_no = b.trade_serial_no AND a.expend_account_no = b.expend_account_no AND a.income_account_no = b.income_account_no AND a.trade_date = date_format(b.trade_start_time, '%Y%m%d') DELETE FROM temp_huifu_wallet_reconciliation SELECT a.id, a.trade_serial_no tradeSerialNo, a.bank_trade_type bankTradeType, a.trade_status tradeStatus, a.trade_start_time tradeStartTime, CONCAT(a.trade_amount,'') tradeAmount, a.expend_account_name expendAccountName, a.expend_account_no expendAccountNo, d.branch_name AS expendBank, a.income_account_name incomeAccountName, a.income_account_no incomeAccountNo, e.branch_name AS incomeBank, a.income_cer_no incomeCerNo, a.remark FROM eb_huifu_wallet_reconciliation a LEFT JOIN eb_bank_config d ON d.branch_code = a.expend_bank_code LEFT JOIN eb_bank_config e ON e.branch_code = a.incomed_bank_code WHERE 1=1 -- AND a.bankTradeType !='801' AND a.income_uni_id = #{uniId} AND a.expend_uni_id = #{uniId} AND a.trade_date = ]]> #{startDate} AND a.trade_date #{endDate} AND a.bank_trade_type = #{bankTradeType} AND a.expend_account_name LIKE CONCAT('%',#{accountName},'%') AND a.income_account_name LIKE CONCAT('%',#{accountName},'%') ORDER BY a.trade_start_time DESC SELECT SUM(a.trade_amount) AS sumAmount, COUNT(1) AS totalCount, SUM(IF(INSTR(a.trade_status,'成功'),a.trade_amount,0)) AS successAmount, SUM(IF(INSTR(a.trade_status,'成功'),1,0)) AS successCount, SUM(IF(INSTR(a.trade_status,'失败'),a.trade_amount,0)) AS errorAmount, SUM(IF(INSTR(a.trade_status,'失败'),1,0)) AS errorCount, SUM(IF(INSTR(a.trade_status,'初始化') OR INSTR(a.trade_status,'处理中'),a.trade_amount,0)) AS processingAmount, SUM(IF(INSTR(a.trade_status,'初始化') OR INSTR(a.trade_status,'处理中'),1,0)) AS processingCount FROM eb_huifu_wallet_reconciliation a WHERE 1=1 -- AND bank_trade_type !='801' AND a.income_uni_id = #{uniId} AND a.expend_uni_id = #{uniId} AND a.trade_date = ]]> #{startDate} AND a.trade_date #{endDate} AND a.bank_trade_type = #{bankTradeType} AND a.expend_account_name LIKE CONCAT('%',#{accountName},'%') AND a.income_account_name LIKE CONCAT('%',#{accountName},'%') SELECT a.trade_serial_no tradeSerialNo, a.request_serial_no requestSerialNo, a.bank_trade_type bankTradeType, a.trade_status tradeStatus, a.expend_account_no expendAccountNo, a.income_account_no incomeAccountNo FROM eb_huifu_wallet_reconciliation a WHERE 1=1 AND a.trade_serial_no = #{tradeSerialNo}