石基昆仑-交易资金流水查询

[landv]

---资金交易流水
select distinct
					
					sv.MembershipCardNumber as 会员卡号,
					sv.TransactionDate as 交易时间,
					sv.Amount as 金额,
					(CASE WHEN sv.OperationType = 0 THEN '储值'
		WHEN OperationType = 1  THEN '退费'
		WHEN OperationType = 2 THEN '预授权'
		WHEN OperationType = 3 THEN '消费支付'
		WHEN OperationType = 4 THEN '赠送'
		WHEN OperationType = 5 THEN '转账'
		WHEN OperationType = 6 THEN '冲账（支付退费）,取消冲账'
		WHEN OperationType = 7 THEN '调整'
		WHEN OperationType = 8 THEN '取消奖励（赠送退费）'
		WHEN OperationType = 9 THEN '过期'
		WHEN OperationType = 10 THEN '合并会员卡'
		WHEN OperationType = 11 THEN '还款'
		WHEN OperationType = 12 THEN '初始化剩余透支额度'
		WHEN OperationType = 13 THEN '透支额度调整'
		WHEN OperationType = 15 THEN '初始卡值'
		WHEN OperationType = 16 THEN '退卡'
		WHEN OperationType = 18 THEN '入账'
		WHEN OperationType = 19 THEN '换卡'
		END
		) AS 交易类型,
					sv.ThisBalance as 余额,
					sv.HotelCode as 酒店,
					sv.CheckNumber as 外部单号,
			
					t.TransactionNumber as 业务单号,
					u.Code as 操作编号,
					u.Name as 操作员,
					mct.Name as 卡类型,
					isnull(p.LastName,'''')+isnull(p.FirstName,'''') as 会员名称,
					dp.code as PlaceCode,
					dp.name as 交易地点,
					d.Name as 支付方式,
					m.CardFaceNumber as 卡面号,
					mca.Name  as 账户,
					ms.name as 注册渠道
			from LPS_StoredValueAccountHistory sv
			inner join LPS_MembershipCard m on sv.MembershipCardId=m.id
			left join dic_member_source ms on m.MemberSourceCode = ms.code
			left join dic_place dp on sv.PlaceCode=dp.code
			left join hotels h on sv.HotelCode=h.code
			left join dic_payment d on sv.PaymentMode=d.code
			left join LPS_Transaction t on sv.TransactionId=t.id
			inner join LPS_MembershipCardAccount mca on sv.MembershipCardAccountId=mca.id
			left join users u on sv.Insert_User=u.code
			inner join LPS_MembershipCardLevel mcl on m.MembershipCardLevelId=mcl.id
			inner join  LPS_MembershipCardType mct on m.MembershipCardTypeId=mct.id
			left join LPS_Profile p on m.ProfileId=p.id
			left join LPS_ConsumeHistory  c on sv.HistoryId=c.id
			where 1=1  and sv.TransactionDate >= '2024/9/1 0:00:00' and sv.TransactionDate < '2024/9/30 23:00:00'