a.id AS "id", a.member_code AS "memberCode", m.name AS "memberName", m.account_balance AS "memberBalance", a.business_id AS "businessId", a.charging_item_id AS "chargingItemId", a.charging_item_num AS "chargingItemNum", a.payment_amount AS "paymentAmount", a.price AS "price", a.payment_way AS "paymentWay", a.running_number AS "runningNumber", a.payment_state AS "paymentState", a.out_trade_no AS "outTradeNo", a.payment_time AS "paymentTime", a.payment_remarks AS "paymentRemarks", a.update_by_name AS "updateByName.id", a.alipay_s AS "alipayS", a.create_date AS "createDate", a.create_by AS "createBy.id", a.update_by AS "updateBy.id", a.update_date AS "updateDate", a.remarks AS "remarks", a.del_flag AS "delFlag" LEFT JOIN sys_member m ON m.member_code = a.member_code SELECT FROM payment_record a WHERE a.id = #{id} SELECT FROM payment_record a a.del_flag = #{DEL_FLAG_NORMAL} AND a.member_code = #{memberCode} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC select m.member_code AS "memberCode", m. NAME AS "memberName", CASE WHEN (t.price > 0) THEN t.price ELSE 0 END "uncompletedIncome", case when (sb.before_change + sb.change_amount) >0 then (sb.before_change + sb.change_amount) else 0 END as 'memberBalance' from sys_member m LEFT JOIN (select max(create_date),member_code,before_change,change_amount from ( select * from sys_account_balance_change where create_date #{endPaymentTime} order by create_date desc) as c GROUP BY member_code ) as sb on sb.member_code=m.member_code LEFT JOIN ( SELECT member_code, SUM(price) AS price FROM sa_payment_detail_total WHERE business_states != '1' GROUP BY member_code ) t ON m.member_code = t.member_code m.del_flag = #{DEL_FLAG_NORMAL} AND m.member_code = #{memberCode} AND m.account_balance = #{memberBalance} GROUP BY t.member_code ORDER BY memberBalance DESC SELECT m.member_code AS "memberCode", m. NAME AS "memberName", CASE WHEN (t.price > 0) THEN t.price ELSE 0 END "uncompletedIncome", m.account_balance AS "memberBalance" from sys_member m LEFT JOIN ( SELECT member_code, SUM(price) AS price FROM sa_payment_detail_total WHERE business_states != '1' GROUP BY member_code ) t ON m.member_code = t.member_code m.del_flag = #{DEL_FLAG_NORMAL} AND m.member_code = #{memberCode} AND m.account_balance = #{memberBalance} ORDER BY ${page.orderBy} ORDER BY m.account_balance DESC select sum(account_balance) as total from sys_member a where a.del_flag = 0 select sum(sy.before_change+sy.change_amount) as 'total' from sys_member m LEFT JOIN (select max(create_date) as time,member_code from ( select * from sys_account_balance_change where create_date < #{endPaymentTime} ) as c GROUP BY member_code ) as sb on sb.member_code=m.member_code left JOIN sys_account_balance_change sy on sb.time=sy.create_date and sb.member_code=sy.member_code m.del_flag = #{DEL_FLAG_NORMAL} AND m.member_code = #{memberCode} AND m.account_balance = #{memberBalance} select '充值总金额' as "totalType", IFNULL(sum(payment_amount),0) as "totalPrice" from payment_record where del_flag = '0' and payment_state='2' and charging_item_id='0' and member_code like 'HZ%' AND member_code = #{memberCode} AND payment_time = ]]> #{startPaymentTime} AND payment_time #{endPaymentTime} UNION select '余额总扣费' as "totalType", IFNULL(sum(r.payment_amount),0) as "totalPrice" from payment_record r where r.del_flag = '0' and r.payment_state='2' and r.charging_item_id!='215' and (r.payment_way='cku.org' or r.payment_way='25') and ( member_code like 'HZ%' or EXISTS ( SELECT user_id from sys_org o where r.create_by=o.user_id ) ) AND r.member_code = #{memberCode} AND r.payment_time = ]]> #{startPaymentTime} AND r.payment_time #{endPaymentTime} select '充值总金额' as "totalType", IFNULL(sum(payment_amount),0) as "totalPrice" from payment_record where del_flag = '0' and payment_state='2' and charging_item_id='0' and member_code like 'MRHZ%' AND member_code = #{memberCode} AND payment_time = ]]> #{startPaymentTime} AND payment_time #{endPaymentTime} UNION select '余额总扣费' as "totalType", IFNULL(sum(r.payment_amount),0) as "totalPrice" from payment_record r where r.del_flag = '0' and r.payment_state='2' and (r.payment_way='cku.org' or r.payment_way='25') and (member_code like 'MRHZ%' or EXISTS ( SELECT school_user_id from training_institution t where r.create_by=t.school_user_id ) ) AND r.member_code = #{memberCode} AND r.payment_time = ]]> #{startPaymentTime} AND r.payment_time #{endPaymentTime} select '充值总金额' as "totalType", IFNULL(sum(payment_amount),0) as "totalPrice" from payment_record where del_flag = '0' and payment_state='2' and charging_item_id='0' and member_code NOT like 'MRHZ%' and member_code NOT like 'HZ%' AND member_code = #{memberCode} AND payment_time = ]]> #{startPaymentTime} AND payment_time #{endPaymentTime} UNION select '余额总扣费' as "totalType", IFNULL(sum(r.payment_amount),0) as "totalPrice" from payment_record r where r.del_flag = '0' and r.payment_state='2' and (r.payment_way='cku.org' or r.payment_way='25') and member_code NOT like 'MRHZ%' and member_code NOT like 'HZ%' and NOT EXISTS ( SELECT user_id from sys_org o where r.create_by=o.user_id ) and NOT EXISTS ( SELECT school_user_id from training_institution t where r.create_by=t.school_user_id ) AND r.member_code = #{memberCode} AND r.payment_time = ]]> #{startPaymentTime} AND r.payment_time #{endPaymentTime} SELECT FROM payment_record a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC INSERT INTO payment_record( id, member_code, business_id, charging_item_id, charging_item_num, payment_amount, price, payment_way, running_number, payment_state, out_trade_no, payment_time, payment_remarks, update_by_name, alipay_s, create_date, create_by, update_by, update_date, remarks, del_flag ) VALUES ( #{id}, #{memberCode}, #{businessId}, #{chargingItemId}, #{chargingItemNum}, #{paymentAmount}, #{price}, #{paymentWay}, #{runningNumber}, #{paymentState}, #{outTradeNo}, #{paymentTime}, #{paymentRemarks}, #{updateByName.id}, #{alipayS}, #{createDate}, #{createBy.id}, #{updateBy.id}, #{updateDate}, #{remarks}, #{delFlag} ) UPDATE payment_record SET member_code = #{memberCode}, business_id = #{businessId}, charging_item_id = #{chargingItemId}, charging_item_num = #{chargingItemNum}, payment_amount = #{paymentAmount}, price = #{price}, payment_way = #{paymentWay}, running_number = #{runningNumber}, payment_state = #{paymentState}, out_trade_no = #{outTradeNo}, payment_time = #{paymentTime}, payment_remarks = #{paymentRemarks}, update_by_name = #{updateByName.id}, alipay_s = #{alipayS}, update_by = #{updateBy.id}, update_date = #{updateDate}, remarks = #{remarks} WHERE id = #{id} UPDATE payment_record SET remarks = concat(IFNULL(remarks,''),#{remarks}), del_flag = #{DEL_FLAG_DELETE} WHERE id = #{id} select m.member_code AS "memberCode", m. NAME AS "memberName", CASE WHEN (t.price > 0) THEN t.price ELSE 0 END "uncompletedIncome", case when (sy.before_change + sy.change_amount) >0 then (sy.before_change + sy.change_amount) else 0 END as 'memberBalance' from sys_member m LEFT JOIN (select max(create_date) as time,member_code from ( select * from sys_account_balance_change where create_date #{endPaymentTime} ) as c GROUP BY member_code ) as sb on sb.member_code=m.member_code left JOIN sys_account_balance_change sy on sb.time=sy.create_date and sb.member_code=sy.member_code LEFT JOIN ( SELECT member_code, SUM(price) AS price FROM sa_payment_detail_total WHERE business_states != '1' and create_date #{endPaymentTime} GROUP BY member_code ) t ON m.member_code = t.member_code m.del_flag = #{DEL_FLAG_NORMAL} AND m.member_code = #{memberCode} AND m.account_balance = #{memberBalance} GROUP BY t.member_code ORDER BY memberBalance DESC