a.id AS "id", a.member_code AS "memberCode", a.pay_member_code AS "payMemberCode", a.charge_group_id AS "chargeGroupId", a.source_charge AS "sourceCharge", a.type AS "type", a.dept_id AS "deptId", a.charge_item_id AS "chargeItemId", a.charge_date AS "chargeDate", a.running_number AS "runningNumber", a.order_id AS "orderId", a.record_id AS "recordId", a.payment_time AS "paymentTime", a.payment_way AS "paymentWay", a.payment_num AS "paymentNum", a.price AS "price", a.payment_money AS "paymentMoney", a.installment_begin_date AS "installmentBeginDate", a.installment_end_date AS "installmentEndDate", a.create_by AS "createBy.id", a.create_date AS "createDate", a.update_by AS "updateBy.id", a.update_date AS "updateDate", a.remarks AS "remarks", a.del_flag AS "delFlag", a.business_states AS "businessStates", a.finish_time AS "finishTime", a.order_states AS "orderStates", a.old_running_number AS "oldRunningNumber", a.finance_voucher_process_state AS "financeVoucherProcessState", -- (a.payment_num * a.price) AS "totalMoney", p.name AS "chargingItemName", o.name AS "chargingItemOfficeName", u.name AS "updateBy.name", m.name AS "memberName", CASE WHEN ( pr.before_change + pr.change_amount > 0) THEN (pr.before_change + pr.change_amount ) ELSE m.account_balance END AS "accountBalance", IFNULL(IFNULL(s.show_code,g.show_code),s2.show_code) AS "showCode", -- IFNULL(od.total_price ,po.price) AS "orderPrice", po.price AS "orderPrice", po.update_by_name AS "updateByName.id", cku.charging_item_name AS "ckuName", cab.charging_item_name AS "cabName", IFNULL(a.receive_num,0) AS receiveNum LEFT JOIN sys_member m ON m.member_code = a.member_code LEFT JOIN payment_charging_item p ON p.id = a.source_charge LEFT JOIN jeesite_sys_office o ON o.id = p.dept LEFT JOIN show_apply s ON s.running_number = a.running_number LEFT JOIN groomer_register g ON g.running_number = a.running_number -- LEFT JOIN payment_order od ON od.order_code = a.running_number LEFT JOIN payment_record po ON po.running_number = a.running_number LEFT JOIN show_apply s2 ON s2.id = SUBSTR(po.business_id,1,LOCATE('#',po.business_id)-1) LEFT JOIN jeesite_sys_user u on u.id = po.update_by LEFT JOIN sys_account_balance_change pr on pr.running_number=a.running_number LEFT JOIN sa_cku_payment_item_total cku on cku.id=a.charge_item_id LEFT JOIN sa_cab_payment_item_total cab on cab.id=a.charge_item_id SELECT FROM sa_payment_detail_total a WHERE a.id = #{id} SELECT * FROM sa_payment_detail_total a WHERE a.running_number = #{runningNumber} SELECT * FROM sa_payment_detail_total a WHERE a.running_number = #{runningNumber} SELECT sum(a.price) FROM sa_payment_detail_total a WHERE a.del_flag = '0' AND a.business_states != '1' a.del_flag = #{DEL_FLAG_NORMAL} AND a.member_code = #{memberCode} AND a.pay_member_code = #{payMemberCode} AND a.source_charge = #{sourceCharge} AND a.running_number = #{runningNumber} AND a.old_running_number = #{oldRunningNumber} AND p.dept = #{chargingItemOfficeId} AND a.payment_way = #{paymentWay} AND a.payment_time = ]]> #{paymentTimeStart} AND a.payment_time #{paymentTimeEnd} AND a.finish_time = ]]> #{finishTimeStart} AND a.finish_time #{finishTimeEnd} AND a.order_states = #{orderStates} AND a.business_states = #{businessStates} AND a.record_id = #{recordId} AND a.order_id = #{orderId} SELECT FROM sa_payment_detail_total a ORDER BY ${page.orderBy} ORDER BY a.create_date DESC SELECT count(*) FROM sa_payment_detail_total a SELECT FROM sa_payment_detail_total a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC INSERT INTO sa_payment_detail_total( id, member_code, pay_member_code, charge_group_id, source_charge, type, dept_id, charge_item_id, charge_date, running_number, order_id, record_id, payment_time, payment_way , payment_num, price, payment_money, installment_begin_date, installment_end_date, create_by, create_date, update_by, update_date, remarks, del_flag, finish_time, order_states, old_running_number ) VALUES ( #{id}, #{memberCode}, #{payMemberCode}, #{chargeGroupId}, #{sourceCharge}, #{type}, #{deptId}, #{chargeItemId}, #{chargeDate}, #{runningNumber}, #{orderId}, #{recordId}, #{paymentTime}, #{paymentWay}, #{paymentNum}, #{price}, #{paymentMoney}, #{installmentBeginDate}, #{installmentEndDate}, #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{remarks}, #{delFlag}, #{finishTime}, #{orderStates}, #{oldRunningNumber} ) UPDATE sa_payment_detail_total SET member_code = #{memberCode}, pay_member_code = #{payMemberCode}, charge_group_id = #{chargeGroupId}, source_charge = #{sourceCharge}, type = #{type}, dept_id = #{deptId}, charge_item_id = #{chargeItemId}, charge_date = #{chargeDate}, running_number = #{runningNumber}, order_id = #{orderId}, record_id = #{recordId}, payment_time = #{paymentTime}, payment_way = #{paymentWay}, payment_num = #{paymentNum}, price = #{price}, payment_money = #{paymentMoney}, installment_begin_date = #{installmentBeginDate}, installment_end_date = #{installmentEndDate}, update_by = #{updateBy.id}, update_date = #{updateDate}, remarks = #{remarks}, business_states = #{businessStates}, finish_time = #{finishTime}, order_states = #{orderStates}, old_running_number = #{oldRunningNumber}, finance_voucher_process_state = #{financeVoucherProcessState}, receive_num = #{receiveNum} WHERE id = #{id} UPDATE sa_payment_detail_total SET del_flag = #{DEL_FLAG_DELETE} WHERE id = #{id} UPDATE sa_payment_detail_total SET business_states = #{businessStates} AND finish_time = now() WHERE running_number = #{runningNumber} SELECT * from sa_payment_detail_total WHERE del_flag = '0' AND business_states = "0" AND charge_item_id IN #{chargeItemId} AND business_states = '0' AND DATE_FORMAT(payment_time,'%Y-%m-%d') #{paymentTime} AND DATE_FORMAT(charge_date,'%Y-%m-%d') #{chargeDate} UPDATE sa_payment_detail_total SET business_states = '1', finish_time = #{date} WHERE id = #{id} UPDATE sa_payment_detail_total SET receive_num = IFNULL(receive_num,0)+ 1 WHERE running_number = #{runningNumber} AND charge_item_id = #{chargeItemId} SELECT * FROM sa_payment_detail_total a WHERE a.running_number = #{runningNumber} SELECT count（1） FROM sa_payment_detail_total a WHERE a.order_id = #{orderId} SELECT * FROM sa_payment_detail_total a WHERE a.record_id in #{item} UPDATE sa_payment_detail_total SET business_states = "1", finish_time = now() and order_id = #{orderId} and running_number = #{runningNumber} select id as payment_way, subject_code as account_code, subject_name as remarks from sa_finance_subject_mapping where type_id = '1' and del_flag = '0' select t.* from ( select '220301' as centry_account_code, member_code as centry_customer_code, pay_member_code as centry_payment_member_code, '' as centry_supplier_code, sum(payment_money) as centry_payment_money, payment_way as payment_way, sum(payment_money) as dentry_payment_money, case ifnull(source_charge,'1') when '0' then false else true end as order_flag from sa_payment_detail_total where type = 'cab' and payment_way is not null and payment_way != '25' and ifnull(order_states,'0') != '2' and member_code is not null and payment_time is not null and payment_time >= #{startDate} and payment_time <= #{endDate} group by member_code, pay_member_code, payment_way union all select '224107' as centry_account_code, '' as centry_customer_code, '' as centry_payment_member_code, '206' as centry_supplier_code, sum(payment_money) as centry_payment_money, payment_way as payment_way, sum(payment_money) as dentry_payment_money, true as order_flag from sa_payment_detail_total where type = 'cku' and payment_way is not null and payment_way != '25' and ifnull(order_states,'0') != '2' and member_code is not null and payment_time is not null and payment_time >= #{startDate} and payment_time <= #{endDate} group by payment_way union all select '224107' as centry_account_code, '' as centry_customer_code, '' as centry_pay_member_code, '206' as centry_supplier_code, sum(payment_money) as centry_payment_money, payment_way as payment_way, sum(payment_money) as dentry_payment_money, true as order_flag from sa_payment_detail_total where type = 'cku' and payment_way = '25' and ifnull(order_states,'0') != '2' and member_code is not null and finish_time is not null and finish_time >= #{startDate} and finish_time <= #{endDate} group by payment_way ) t order by t.payment_way, t.centry_account_code desc select '220301' as dentry_account_code, sum(temp.payment_money) as dentry_payment_money, temp.member_code as dentry_customer_code, temp.subject_code as centry_account_code, temp.yonyou_dept_code as centry_dept_code, temp.yonyou_dept_name as centry_dept_name, sum(temp.payment_money) as centry_payment_money, round(sum(temp.payment_money) * temp.tax_rate,2) as tax_money, temp.payment_way as payment_way, temp.tax_account_code as tax_account_code, temp.show_code as centry_item_code -- temp.charge_group_id, -- temp.charge_item_id, -- temp.tax_rate from ( select pdt.*, fsm.*, pit.tax_account_code, pit.tax_rate, case pit.item_type when 1 then ( select show_code from groomer_register where running_number = pdt.running_number) when 2 then ( select substring(substring_index(show_code,',',1),1,char_length (substring_index (show_code,',',1))-locate('-', reverse(substring_index (show_code,',',1)))) from show_apply where running_number = pdt.running_number) when 3 then ( select show_code from handler_register where running_number = pdt.running_number) when 4 then '001' else null end as show_code from ( select charge_group_id, charge_item_id, member_code, payment_way, payment_money, running_number from sa_payment_detail_total where charge_group_id is not null and ifnull(order_states,'0') = '0' and type = 'cab' and member_code is not null and payment_way is not null and finish_time is not null and finish_time >= #{startDate} and finish_time <= #{endDate} ) pdt join ( select id, subject_code, subject_name, yonyou_dept_code, yonyou_dept_name from sa_finance_subject_mapping where type_id='3') fsm on fsm.id = pdt.charge_group_id join sa_cab_payment_item_total pit on pdt.charge_item_id = pit.id ) temp group by temp.charge_group_id, temp.charge_item_id, temp.member_code, temp.payment_way, temp.subject_code, temp.yonyou_dept_code, temp.subject_name, temp.yonyou_dept_name, temp.tax_rate, temp.tax_account_code, temp.show_code select member_code as dentry_customer_code, '220301' as dentry_account_code, case current_month_flag when '0' then '' when '1' then '206' end as centry_supplier_code, case current_month_flag when '0' then '004' when '1' then '' end as centry_customer_code, case current_month_flag when '0' then '122101' when '1' then '224107' end as centry_account_code, pdt.current_month_flag, payment_way, case current_month_flag when '0' then sum(abs(payment_money)) when '1' then 0-sum(abs(payment_money)) end as centry_payment_money, case current_month_flag when '0' then sum(abs(payment_money)) when '1' then 0-sum(abs(payment_money)) end as dentry_payment_money from ( select member_code, payment_way, payment_money, date_format(payment_time,'%Y-%m')=date_format(now(),'%Y-%m') current_month_flag from sa_payment_detail_total where ifnull(order_states,'0') = '2' and type = 'cku' and payment_way is not null and payment_way != '25' and create_date is not null and create_date >= #{startDate} and create_date <= #{endDate} ) pdt group by pdt.member_code, pdt.current_month_flag, pdt.payment_way order by member_code, centry_account_code select '220301' dentry_account_code, -- 借科目 drawee as dentry_customer_code, sum(price) dentry_payment_money, '220301' centry_account_code, -- 贷科目 payee as centry_customer_code, sum(price) centry_payment_money from ( select drawee, payee, price from payment_pay_other where del_flag = '0' and add_time is not null and add_time >= #{startDate} and add_time <= #{endDate} ) t group by drawee, payee order by drawee SELECT * FROM sa_payment_detail_total a WHERE a.order_id in #{item} UPDATE sa_payment_detail_total SET receive_num = receive_num + 1 WHERE id = #{id} SELECT * FROM sa_payment_detail_total WHERE 1=1 and order_id = #{orderId} and running_number = #{runningNumber} limit 1; SELECT * FROM sa_payment_detail_total a WHERE a.del_flag='0' and a.old_running_number = #{oldRunningNumber}