sql 日结

--生成日结数据

====================================
-- 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