a.id AS "id",
a.member_code AS "memberCode",
m.name AS "memberName",
a.business_id AS "businessId",
a.charging_item_id AS "chargingItemId",
a.charging_item_num AS "chargingItemNum",
p.name AS "chargingItemName",
o.name AS "chargingItemOfficeName",
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",
u.name AS "updateBy.name",
a.update_date AS "updateDate",
a.remarks AS "remarks",
a.del_flag AS "delFlag",
sm.member_code as "cmember",
so.member_code as 'orgmember',
pr.before_change+pr.change_amount as 'balanceMoney'
LEFT JOIN sys_member m ON m.member_code = a.member_code
LEFT JOIN payment_charging_item p ON p.id = a.charging_item_id
LEFT JOIN jeesite_sys_office o ON o.id = p.dept
LEFT JOIN jeesite_sys_user u on u.id = a.update_by
LEFT JOIN sys_member sm on sm.user_id=a.update_by
LEFT JOIN sys_org so on so.user_id=a.update_by
LEFT JOIN sys_account_balance_change pr on pr.running_number=a.running_number
SELECT
SELECT
a.del_flag = #{DEL_FLAG_NORMAL}
and (so.member_code=#{cmember} or sm.member_code= #{cmember} or ( a.update_by='1' and a.member_code=#{cmember}))
AND a.running_number = #{runningNumber}
AND a.member_code = #{memberCode}
AND m.name like CONCAT('%', #{memberName}, '%')
AND p.dept = #{chargingItemOfficeId}
AND a.charging_item_id = #{chargingItemId}
AND a.payment_way = #{paymentWay}
AND a.update_by = #{updateBy.id}
AND a.payment_amount = #{paymentAmount}
AND a.payment_remarks LIKE concat('%',#{paymentRemarks},'%')
AND a.payment_time = ]]> #{paymentTimeStart}
AND a.payment_time #{paymentTimeEnd}
AND a.register_province = #{province}
AND a.register_province in
#{region.label}
ORDER BY ${page.orderBy}
ORDER BY ifnull(pr.create_date,a.create_date) DESC,pr.id DESC, a.create_date DESC
SELECT
sum(a.payment_amount)
FROM payment_record a
a.del_flag = #{DEL_FLAG_NORMAL}
and (so.member_code=#{cmember} or sm.member_code= #{cmember} or a.member_code=#{cmember} )
AND a.running_number = #{runningNumber}
AND a.member_code = #{memberCode}
AND m.name like CONCAT('%', #{memberName}, '%')
AND p.dept = #{chargingItemOfficeId}
AND a.charging_item_id = #{chargingItemId}
AND a.payment_way = #{paymentWay}
AND a.update_by = #{updateBy.id}
AND a.payment_amount = #{paymentAmount}
AND a.payment_remarks LIKE concat('%',#{paymentRemarks},'%')
AND a.payment_time = ]]> #{paymentTimeStart}
AND a.payment_time #{paymentTimeEnd}
AND a.register_province = #{province}
AND a.register_province in
#{region.label}
SELECT
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,
running_number_reverse,
register_province,
register_city,
biz_member_code
) 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},
#{runningNumberReverse},
#{registerProvince},
#{registerCity},
#{memberCode}
)
UPDATE payment_record SET payment_state = #{paymentState}
WHERE id = #{id}
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
a.id AS "id",
a.member_code AS "memberCode",
s.name AS "memberName",
a.business_id AS "businessId",
a.charging_item_id AS "chargingItemId",
a.charging_item_num AS "chargingItemNum",
c.name AS "chargingItemName",
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",
u.name AS "updateBy.name",
a.update_date AS "updateDate",
a.remarks AS "remarks",
a.del_flag AS "delFlag"
FROM
payment_record a
LEFT JOIN sys_org t on t.user_id = a.create_by
LEFT JOIN sys_member s on s.member_code = a.member_code
LEFT JOIN payment_charging_item c ON c.id = a.charging_item_id
LEFT JOIN jeesite_sys_user u ON u.id = a.update_by
a.del_flag = #{DEL_FLAG_NORMAL}
AND a.running_number = #{runningNumber}
AND t.member_code = #{memberCode}
AND a.member_code != #{memberCode}
AND a.payment_way = #{paymentWay}
AND a.payment_amount = #{paymentAmount}
AND a.payment_time = ]]> #{paymentTimeStart}
AND a.payment_time #{paymentTimeEnd}
ORDER BY ${page.orderBy}
ORDER BY a.update_date DESC
SELECT
a.id AS "id",
a.member_code AS "memberCode",
s.name AS "memberName",
a.business_id AS "businessId",
a.charging_item_id AS "chargingItemId",
a.charging_item_num AS "chargingItemNum",
c.name AS "chargingItemName",
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",
u.name AS "updateBy.name",
a.update_date AS "updateDate",
a.remarks AS "remarks",
a.del_flag AS "delFlag"
FROM
payment_record a
LEFT JOIN sys_org t on t.user_id = a.create_by
LEFT JOIN sys_member s on s.member_code = a.member_code
LEFT JOIN payment_charging_item c ON c.id = a.charging_item_id
LEFT JOIN jeesite_sys_user u ON u.id = a.update_by
a.del_flag = #{DEL_FLAG_NORMAL}
AND a.running_number = #{runningNumber}
SELECT
sum(a.payment_amount)
FROM
payment_record a
LEFT JOIN sys_org t on t.user_id = a.create_by
LEFT JOIN sys_member s on s.member_code = a.member_code
LEFT JOIN payment_charging_item c ON c.id = a.charging_item_id
LEFT JOIN jeesite_sys_user u ON u.id = a.update_by
a.del_flag = #{DEL_FLAG_NORMAL}
AND a.running_number = #{runningNumber}
AND t.member_code = #{memberCode}
AND a.member_code != #{memberCode}
AND a.payment_way = #{paymentWay}
AND a.payment_amount = #{paymentAmount}
AND a.payment_time = ]]> #{paymentTimeStart}
AND a.payment_time #{paymentTimeEnd}
SELECT
a.id AS "id",
a.member_code AS "memberCode",
s.name AS "memberName",
a.business_id AS "businessId",
a.charging_item_id AS "chargingItemId",
a.charging_item_num AS "chargingItemNum",
c.name AS "chargingItemName",
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",
u.name AS "updateBy.name",
a.update_date AS "updateDate",
a.remarks AS "remarks",
a.del_flag AS "delFlag"
FROM
payment_record a
LEFT JOIN training_institution t ON a.create_by = t.school_user_id
LEFT JOIN sys_member s on s.member_code = a.member_code
LEFT JOIN payment_charging_item c ON c.id = a.charging_item_id
LEFT JOIN jeesite_sys_user u ON u.id = a.update_by
a.del_flag = #{DEL_FLAG_NORMAL}
AND a.running_number = #{runningNumber}
AND t.school_member_code = #{memberCode}
AND a.payment_way = #{paymentWay}
AND a.payment_amount = #{paymentAmount}
AND a.payment_time = ]]> #{paymentTimeStart}
AND a.payment_time #{paymentTimeEnd}
ORDER BY ${page.orderBy}
ORDER BY a.update_date DESC
select IFNULL(sum(payment_amount),0) from payment_record
where charging_item_id='0' and del_flag=0
and (member_code = #{memberCode}
or update_by= (select user_id from sys_member where member_code = #{memberCode})
OR update_by= ( SELECT user_id FROM sys_org WHERE member_code = #{memberCode})
)
and payment_time ]]> #{paymentTimeStart}
select IFNULL(sum(payment_amount),0)from
(
SELECT
CASE
WHEN sm.member_code IS NOT NULL
THEN sm.member_code
WHEN so.member_code IS NOT NULL
THEN so.member_code
ELSE a.member_code
END AS payment_member_code,
a.payment_amount,
a.payment_way,
a.payment_time
FROM
payment_record a
LEFT JOIN
sys_member sm
ON
sm.user_id=a.update_by
LEFT JOIN
sys_org so
ON
so.user_id=a.update_by
where a.del_flag=0
SELECT
sm.member_code AS payment_member_code,
a.payment_amount,
a.payment_way,
a.payment_time
FROM
payment_record a
LEFT JOIN
sys_member sm
ON
sm.user_id=a.update_by
WHERE
a.del_flag=0
AND sm.member_code = #{memberCode}
UNION
SELECT
so.member_code AS payment_member_code,
a.payment_amount,
a.payment_way,
a.payment_time
FROM
payment_record a
LEFT JOIN
sys_member sm
ON
sm.user_id=a.update_by
LEFT JOIN
sys_org so
ON
so.user_id=a.update_by
WHERE
a.del_flag=0
AND sm.member_code IS NULL
AND so.member_code = #{memberCode}
UNION
SELECT
a.member_code AS payment_member_code,
a.payment_amount,
a.payment_way,
a.payment_time
FROM
payment_record a
LEFT JOIN
sys_member sm
ON
sm.user_id=a.update_by
LEFT JOIN
sys_org so
ON
so.user_id=a.update_by
WHERE
a.del_flag=0
AND sm.member_code IS NULL
AND so.member_code IS NULL
AND a.member_code = #{memberCode}
)t
where (payment_way='cku.org'or payment_way='25' or payment_way = '27')
and payment_member_code = #{memberCode}
and payment_time ]]> #{paymentTimeStart}
SELECT
sum(a.payment_amount)
FROM
payment_record a
LEFT JOIN training_institution t ON a.create_by = t.school_user_id
LEFT JOIN sys_member s on s.member_code = a.member_code
LEFT JOIN payment_charging_item c ON c.id = a.charging_item_id
LEFT JOIN jeesite_sys_user u ON u.id = a.update_by
a.del_flag = #{DEL_FLAG_NORMAL}
AND a.running_number = #{runningNumber}
AND t.school_member_code = #{memberCode}
AND a.payment_way = #{paymentWay}
AND a.payment_amount = #{paymentAmount}
AND a.payment_time = ]]> #{paymentTimeStart}
AND a.payment_time #{paymentTimeEnd}
SELECT
a.del_flag = #{DEL_FLAG_NORMAL}
AND a.member_code = #{memberCode}
AND a.charging_item_id='0'
AND (a.payment_way='cku.org' or a.payment_way='25' or a.charging_item_id='69')
AND NOT EXISTS (
SELECT user_id from sys_org org where a.create_by=org.user_id
)
AND a.payment_time = ]]> #{paymentTimeStart}
AND a.payment_time #{paymentTimeEnd}
ORDER BY ${page.orderBy}
ORDER BY a.update_date DESC
SELECT
sum(a.payment_amount)
FROM payment_record a
a.del_flag = #{DEL_FLAG_NORMAL}
AND a.member_code = #{memberCode}
AND a.charging_item_id='0'
AND (a.payment_way='cku.org' or a.payment_way='25' or a.charging_item_id='69')
AND NOT EXISTS (
SELECT user_id from sys_org org where a.create_by=org.user_id
)
AND a.payment_time = ]]> #{paymentTimeStart}
AND a.payment_time #{paymentTimeEnd}