--生成日结数据 ====================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: 资金日结
-- =============================================
ALTER PROCEDURE [UC].[P_Fin_DailySummary_Insert]
AS
BEGIN TRY
SET NOCOUNT ON ;
DECLARE @ErrorID INT= 0
DECLARE @vcExecInfo VARCHAR(1)=''
SET NOCOUNT ON ;
DECLARE @dtDate VARCHAR(15)= CONVERT(VARCHAR(15), DATEADD(day, ( -1 ), GETDATE()), 23)
DECLARE @dtYesterdayDate VARCHAR(15)= CONVERT(VARCHAR(15), DATEADD(day, ( -2 ), GETDATE()), 23)
DECLARE @dtTodayDate VARCHAR(15)= CONVERT(VARCHAR(15), DATEADD(day, ( 0 ), GETDATE()), 23) --DELETE UC.Fin_DailySummary
--WHERE dtBalanceDate = @dtDate INSERT INTO UC.Fin_DailySummary ( dtBalanceDate, vcCustomerNo, vcLoginId, nCustomerType, dmYesterdayBalance, dmTodayBalance, dmRechargeAmount, dmWithDrawAmount, dmSellGoldAmount, dmBuyGoldAmount, dmGoldCompensate, dmWithDrawFee, dmRechargeFee, dmGoldSellFee, dmAdvertAmount, dmRechargeAdjust, dmWithDrawAdjust, dmGoldOrderAdjust )
SELECT @dtDate, a.vcCustomerNo, a.vcUserName,a.nCustomerType,0 AS dmYesterdayBalance,ISNULL(a.dmCurrentFund-ISNULL(c.dmFund,0),0) AS dmTodayBalance, ISNULL(b.dmRechargeAmount, 0) AS dmRechargeAmount, ISNULL(b.dmWithDrawAmount, 0) AS dmWithDrawAmount,ISNULL(b.dmSellGoldAmount, 0) AS dmSellGoldAmount, ISNULL(b.dmBuyGoldAmount, 0) AS dmBuyGoldAmount, ISNULL(b.dmGoldCompensate, 0) AS dmGoldCompensate, ISNULL(b.dmWithdrawFee, 0) AS dmWithDrawFee, ISNULL(b.dmRechargeFee, 0) AS dmRechargeFee,0 AS dmGoldSellFee,0 AS dmAdvertAmount,ISNULL(b.dmRechargeAdjust, 0) AS dmRechargeAdjust,ISNULL(b.dmWithDrawAdjust, 0) AS dmWithDrawAdjust,ISNULL(b.dmGoldOrderAdjust, 0) AS dmGoldOrderAdjust
FROM UC.Cus_Customer AS a
LEFT JOIN ( SELECT vcCustomerNo,
ISNULL(SUM(CASE nFundType
WHEN 10 THEN dmFund
ELSE 0 END), 0) AS dmRechargeAmount,
ISNULL(SUM(CASE nFundType
WHEN 14 THEN dmFund
ELSE 0 END), 0) AS dmWithDrawAmount,
ISNULL(SUM(CASE nFundType
WHEN 22 THEN dmFund
ELSE 0 END), 0) AS dmBuyGoldAmount,
ISNULL(SUM(CASE nFundType
WHEN 23 THEN dmFund
ELSE 0 END), 0) AS dmSellGoldAmount,
ISNULL(SUM(CASE nFundType
WHEN 17 THEN dmFund
ELSE 0 END), 0) AS dmGoldCompensate,
ISNULL(SUM(CASE nFundType
WHEN 11 THEN dmFund
ELSE 0 END), 0) AS dmRechargeFee,
ISNULL(SUM(CASE nFundType
WHEN 18 THEN dmFund
ELSE 0 END), 0) AS dmWithdrawFee,
ISNULL(SUM(CASE nAdjustType
WHEN 11 THEN dmFund
ELSE 0 END), 0) AS dmRechargeAdjust,
ISNULL(SUM(CASE nAdjustType
WHEN 12 THEN dmFund
ELSE 0 END), 0) AS dmWithDrawAdjust,
ISNULL(SUM(CASE nAdjustType
WHEN 13 THEN dmFund
ELSE 0 END), 0) AS dmGoldOrderAdjust
FROM UC.Fin_CustomerFundJour
WHERE CONVERT(VARCHAR(10),dtCreateDate,120) = @dtDate
GROUP BY vcCustomerNo ) AS b
ON a.vcCustomerNo = b.vcCustomerNo
LEFT JOIN (SELECT SUM(dmFund) AS dmFund,vcCustomerNo
FROM UC.Fin_CustomerFundJour
WHERE CONVERT(VARCHAR(10),dtCreateDate,120) = @dtTodayDate
GROUP BY vcCustomerNo) AS c
ON a.vcCustomerNo = c.vcCustomerNo --修改前日余额
UPDATE Fin_DailySummary
SET dmYesterdayBalance = c.dmTodayBalance
FROM Fin_DailySummary AS a ,
( SELECT b.dmTodayBalance, b.vcCustomerNo
FROM Fin_DailySummary AS b
WHERE b.dtBalanceDate = @dtYesterdayDate ) c
WHERE a.vcCustomerNo = c.vcCustomerNo AND a.dtBalanceDate = @dtDate END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRAN ;
EXEC P_Pub_GetErrorMsgExecInfo @ErrorID OUT,@vcExecInfo OUT
END CATCH
<--------------------------------------------------------------------分割线-------------------------------------------------------------------------------------->
--日结数据查询 -- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: 客户资金日结查询
-- =============================================
ALTER PROCEDURE [UC].[P_Admin_Fin_DailySummary_Search]
@vcBeginDate VARCHAR(50) ,
@vcEndDate VARCHAR(50) ,
@nCustomerType INT ,
@vcSearValue VARCHAR(100) ,
@nPageIndex INT ,
@nPageSize INT ,
@nTotleRecord INT OUT ,
@nPageCount INT OUT
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @nRowStart INT
DECLARE @nRowEnd INT
DECLARE @vcSearchBeginDate VARCHAR(50)
DECLARE @vcSearchEndDate VARCHAR(50)
SET @nRowEnd = @nPageIndex * @nPageSize
SET @nRowStart = @nRowEnd - @nPageSize + 1 --设置默认值
IF @vcBeginDate = ''
SET @vcBeginDate = CONVERT(VARCHAR(10), GETDATE() - 1, 120)
IF @vcEndDate = ''
SET @vcEndDate = CONVERT(VARCHAR(10), GETDATE(), 120) SET @vcSearchBeginDate = @vcBeginDate
SET @vcSearchEndDate = @vcEndDate SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY vcCustomerNo DESC ) AS RowIndex, *
FROM UC.Fin_DailySummary
WHERE ( @vcSearValue = '' OR ( vcLoginId = @vcSearValue OR vcCustomerNo = @vcSearValue ) ) AND ( @nCustomerType = 0 OR nCustomerType = @nCustomerType ) AND dtBalanceDate BETWEEN @vcBeginDate AND @vcEndDate ) AS cte
WHERE RowIndex BETWEEN @nRowStart AND @nRowEnd IF NOT EXISTS ( SELECT 1
FROM UC.Fin_DailySummary
WHERE CONVERT(VARCHAR(10), dtBalanceDate, 120) = @vcSearchBeginDate )
BEGIN
--如果不存在按照开始日期查询的数据 取数据库最开始的时间
SELECT DISTINCT TOP 1
@vcSearchBeginDate = CONVERT(VARCHAR(10), dtBalanceDate, 120)
FROM UC.Fin_DailySummary
ORDER BY CONVERT(VARCHAR(10), dtBalanceDate, 120) ASC
END
IF NOT EXISTS ( SELECT 1
FROM UC.Fin_DailySummary
WHERE CONVERT(VARCHAR(10), dtBalanceDate, 120) = @vcSearchEndDate )
BEGIN
--如果不存在按照结束日期查询的数据 取数据库最新的时间
SELECT DISTINCT TOP 1
@vcSearchEndDate = CONVERT(VARCHAR(10), dtBalanceDate, 120)
FROM UC.Fin_DailySummary
ORDER BY CONVERT(VARCHAR(10), dtBalanceDate, 120) DESC
END IF @nCustomerType = 0
BEGIN
--查询所有客户分类时 --按照客户分类汇总信息
SELECT CASE nCustomerType
WHEN 10 THEN '正式客户合计' + CONVERT(VARCHAR(10), COUNT(1)) + '条'
WHEN 11 THEN '测试客户合计' + CONVERT(VARCHAR(10), COUNT(1)) + '条'
WHEN 12 THEN '内部客户合计' + CONVERT(VARCHAR(10), COUNT(1)) + '条'
END AS vcCustomerNo, SUM(dmRechargeAmount) AS dmRechargeAmount, SUM(dmWithDrawAmount) AS dmWithDrawAmount, SUM(dmSellGoldAmount) AS dmSellGoldAmount, SUM(dmBuyGoldAmount) AS dmBuyGoldAmount, SUM(dmGoldCompensate) AS dmGoldCompensate, SUM(dmWithDrawFee) AS dmWithDrawFee, SUM(dmRechargeFee) AS dmRechargeFee, SUM(dmGoldSellFee) AS dmGoldSellFee, SUM(dmAdvertAmount) AS dmAdvertAmount, SUM(dmRechargeAdjust) AS dmRechargeAdjust, SUM(dmWithDrawAdjust) AS dmWithDrawAdjust, SUM(dmGoldOrderAdjust) AS dmGoldOrderAdjust, nCustomerType
INTO #summary
FROM UC.Fin_DailySummary
WHERE ( @vcSearValue = '' OR ( vcLoginId = @vcSearValue OR vcCustomerNo = @vcSearValue ) ) AND dtBalanceDate BETWEEN @vcBeginDate AND @vcEndDate
GROUP BY nCustomerType --汇总所有的记录
SELECT '合计' + CONVERT(VARCHAR(10), COUNT(1)) + '条' AS vcCustomerNo, SUM(dmRechargeAmount) AS dmRechargeAmount, SUM(dmWithDrawAmount) AS dmWithDrawAmount, SUM(dmSellGoldAmount) AS dmSellGoldAmount, SUM(dmBuyGoldAmount) AS dmBuyGoldAmount, SUM(dmGoldCompensate) AS dmGoldCompensate, SUM(dmWithDrawFee) AS dmWithDrawFee, SUM(dmRechargeFee) AS dmRechargeFee, SUM(dmGoldSellFee) AS dmGoldSellFee, SUM(dmAdvertAmount) AS dmAdvertAmount, SUM(dmRechargeAdjust) AS dmRechargeAdjust, SUM(dmWithDrawAdjust) AS dmWithDrawAdjust, SUM(dmGoldOrderAdjust) AS dmGoldOrderAdjust
INTO #allsummary
FROM UC.Fin_DailySummary
WHERE ( @vcSearValue = '' OR ( vcLoginId = @vcSearValue OR vcCustomerNo = @vcSearValue ) ) AND dtBalanceDate BETWEEN @vcBeginDate AND @vcEndDate --按照客户分类 汇总前日余额
SELECT SUM(dmYesterdayBalance) AS dmYesterdayBalance, nCustomerType
INTO #yesterday
FROM UC.Fin_DailySummary
WHERE ( @vcSearValue = '' OR ( vcLoginId = @vcSearValue OR vcCustomerNo = @vcSearValue ) ) AND CONVERT(VARCHAR(10), dtBalanceDate, 120) = @vcSearchBeginDate
GROUP BY nCustomerType --按照客户分类 汇总当日余额
SELECT SUM(dmTodayBalance) AS dmTodayBalance, nCustomerType
INTO #today
FROM UC.Fin_DailySummary
WHERE ( @vcSearValue = '' OR ( vcLoginId = @vcSearValue OR vcCustomerNo = @vcSearValue ) ) AND CONVERT(VARCHAR(10), dtBalanceDate, 120) = @vcSearchEndDate
GROUP BY nCustomerType --汇总所有客户的前日余额
SELECT SUM(dmYesterdayBalance) AS dmYesterdayBalance
INTO #allyesterday
FROM UC.Fin_DailySummary
WHERE ( @vcSearValue = '' OR ( vcLoginId = @vcSearValue OR vcCustomerNo = @vcSearValue ) ) AND CONVERT(VARCHAR(10), dtBalanceDate, 120) = @vcSearchBeginDate --汇总所有客户的当日余额
SELECT SUM(dmTodayBalance) AS dmTodayBalance
INTO #alltoday
FROM UC.Fin_DailySummary
WHERE ( @vcSearValue = '' OR ( vcLoginId = @vcSearValue OR vcCustomerNo = @vcSearValue ) ) AND CONVERT(VARCHAR(10), dtBalanceDate, 120) = @vcSearchEndDate --返回结果(根据客户分类汇总的记录以及所有客户的汇总记录)
SELECT #summary.vcCustomerNo, #yesterday.dmYesterdayBalance, #today.dmTodayBalance, #summary.dmRechargeAmount, #summary.dmWithDrawAmount, #summary.dmSellGoldAmount, #summary.dmBuyGoldAmount, #summary.dmGoldCompensate, #summary.dmWithDrawFee, #summary.dmRechargeFee, #summary.dmGoldSellFee, #summary.dmAdvertAmount, #summary.dmRechargeAdjust, #summary.dmWithDrawAdjust, #summary.dmGoldOrderAdjust
FROM #summary
INNER JOIN #yesterday
ON #summary.nCustomerType = #yesterday.nCustomerType
INNER JOIN #today
ON #summary.nCustomerType = #today.nCustomerType
UNION ALL
( SELECT #allsummary.vcCustomerNo, #allyesterday.dmYesterdayBalance, #alltoday.dmTodayBalance, #allsummary.dmRechargeAmount, #allsummary.dmWithDrawAmount, #allsummary.dmSellGoldAmount, #allsummary.dmBuyGoldAmount, #allsummary.dmGoldCompensate, #allsummary.dmWithDrawFee, #allsummary.dmRechargeFee, #allsummary.dmGoldSellFee, #allsummary.dmAdvertAmount, #allsummary.dmRechargeAdjust, #allsummary.dmWithDrawAdjust, #allsummary.dmGoldOrderAdjust
FROM #allsummary
CROSS JOIN #allyesterday
CROSS JOIN #alltoday) END
ELSE
BEGIN
--按照客户分类查询时 --根据条件汇总数据
SELECT '合计' + CONVERT(VARCHAR(10), COUNT(1)) + '条' AS vcCustomerNo, SUM(dmRechargeAmount) AS dmRechargeAmount, SUM(dmWithDrawAmount) AS dmWithDrawAmount, SUM(dmSellGoldAmount) AS dmSellGoldAmount, SUM(dmBuyGoldAmount) AS dmBuyGoldAmount, SUM(dmGoldCompensate) AS dmGoldCompensate, SUM(dmWithDrawFee) AS dmWithDrawFee, SUM(dmRechargeFee) AS dmRechargeFee, SUM(dmGoldSellFee) AS dmGoldSellFee, SUM(dmAdvertAmount) AS dmAdvertAmount, SUM(dmRechargeAdjust) AS dmRechargeAdjust, SUM(dmWithDrawAdjust) AS dmWithDrawAdjust, SUM(dmGoldOrderAdjust) AS dmGoldOrderAdjust
INTO #temp1
FROM UC.Fin_DailySummary
WHERE ( @vcSearValue = '' OR ( vcLoginId = @vcSearValue OR vcCustomerNo = @vcSearValue ) ) AND ( @nCustomerType = 0 OR nCustomerType = @nCustomerType ) AND dtBalanceDate BETWEEN @vcBeginDate AND @vcEndDate --根据条件汇总前日余额
SELECT SUM(dmYesterdayBalance) AS dmYesterdayBalance
INTO #allyesterday1
FROM UC.Fin_DailySummary
WHERE ( @vcSearValue = '' OR ( vcLoginId = @vcSearValue OR vcCustomerNo = @vcSearValue ) ) AND ( @nCustomerType = 0 OR nCustomerType = @nCustomerType ) AND CONVERT(VARCHAR(10), dtBalanceDate, 120) = @vcSearchBeginDate --根据条件汇总当日余额
SELECT SUM(dmTodayBalance) AS dmTodayBalance
INTO #alltoday1
FROM UC.Fin_DailySummary
WHERE ( @vcSearValue = '' OR ( vcLoginId = @vcSearValue OR vcCustomerNo = @vcSearValue ) ) AND ( @nCustomerType = 0 OR nCustomerType = @nCustomerType ) AND CONVERT(VARCHAR(10), dtBalanceDate, 120) = @vcSearchEndDate --返回结果
SELECT #temp1.vcCustomerNo, #allyesterday1.dmYesterdayBalance, #alltoday1.dmTodayBalance, #temp1.dmRechargeAmount, #temp1.dmWithDrawAmount, #temp1.dmSellGoldAmount, #temp1.dmBuyGoldAmount, #temp1.dmGoldCompensate, #temp1.dmWithDrawFee, #temp1.dmRechargeFee, #temp1.dmGoldSellFee, #temp1.dmAdvertAmount, #temp1.dmRechargeAdjust, #temp1.dmWithDrawAdjust, #temp1.dmGoldOrderAdjust
FROM #temp1
CROSS JOIN #allyesterday1
CROSS JOIN #alltoday1 END --返回所有的记录数以及分页数
SELECT @nTotleRecord = COUNT(1), @nPageCount = COUNT(1) / @nPageSize + CASE COUNT(1) % @nPageSize
WHEN 0 THEN 0
ELSE 1
END
FROM UC.Fin_DailySummary
WHERE ( @vcSearValue = '' OR ( vcLoginId = @vcSearValue OR vcCustomerNo = @vcSearValue ) ) AND ( @nCustomerType = 0 OR nCustomerType = @nCustomerType ) AND dtBalanceDate BETWEEN @vcBeginDate AND @vcEndDate END