--st_pd_AccountRep会计出纳报表
IF OBJECT_ID('st_pd_AccountRep') IS NOT NULL DROP PROCEDURE st_pd_AccountRep
GO

CREATE PROCEDURE dbo.st_pd_AccountRep
   @UserNo VARCHAR(8000),   --出纳员编号
   @UserName VARCHAR(8000), --出纳员名称
   @DateBgn DATETIME,       --统计起始日期
   @DateEnd DATETIME        --统计终止日期
AS
-- DECLARE @UserNo VARCHAR(8000),@UserName VARCHAR(8000),@DateBgn DATETIME,@DateEnd DATETIME
-- SELECT @UserNo='AllUsers',@UserName='所有出纳员', @DateBgn='2005-01-01',@DateEnd=GETDATE()
   DECLARE @sBgnDate VARCHAR(16), @sEndDate VARCHAR(16)
   SET @sBgnDate = '' +  CONVERT(CHAR(16), @DateBgn, 20)  + ''
   SET @sEndDate = '' + CONVERT(CHAR(16), @DateEnd, 20)  + ''

   DECLARE @CardCount INT, @CardCharge MONEY, @CardForegif MONEY                  --取得开户次数、开户卡费用、开户卡押金
   DECLARE @SupplyCount INT, @SupplyFund MONEY                                    --存款次数、总存款金额
   DECLARE @SubsidyCount INT, @SubsidyFund MONEY                                  --补贴次数、总补贴金额
   DECLARE @AlterCount INT, @AlterFee MONEY                                       --补卡次数、总补卡费用
   DECLARE @ChangeCount INT                                                       --换卡次数
   DECLARE @TakeCount INT, @TakeFund MONEY                                        --取款次数、总取款金额
   DECLARE @BackCount INT, @BackFund MONEY, @BackForegift MONEY,@SmallFund MONEY  --销卡次数、销卡退主帐户金额、销卡退押金、销卡退小帐户金额
   DECLARE @TransferCount INT, @TransferFund MONEY                                --小金额转账次数、小金额转账总金额
   DECLARE @Count INT                                                             --总计次数
   DECLARE @Money MONEY                                                           --应缴金额
   DECLARE @SUserNo VARCHAR(20),@Pos INT

   --v3.1 peihb add 2008-11-03
   DECLARE @AllConsumeMoney MONEY, @AllConsumeCount INT				--总消费金额，总消费次数	

   
  -- SET @DateEnd=DATEADD(second, -1, @DateEnd + 1)
   IF @UserNo='AllUsers'
   BEGIN
      --1.取得开户次数、开户卡费用、开户卡押金(加上历史记录)
      SELECT @CardCount=COUNT(*), @CardCharge=ROUND(ISNULL(SUM(Card_Charge),0), 2), @CardForegif=ROUND(ISNULL(SUM(Card_Foregift),0), 2)
        FROM dbo.ST_Card WHERE CONVERT(CHAR(16), Card_NewDate, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Card_NewDate, 20) <= @sEndDate
      SELECT @CardCount=@CardCount+COUNT(*), @CardCharge=@CardCharge+ROUND(ISNULL(SUM(Card_Charge),0), 2),
             @CardForegif=@CardForegif+ROUND(ISNULL(SUM(Back_Foregift),0), 2)
        FROM dbo.ST_DeleteCard WHERE  CONVERT(CHAR(16), New_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), New_Date, 20) <= @sEndDate
      SELECT @CardCount=@CardCount+COUNT(*), @CardCharge=@CardCharge+ROUND(ISNULL(SUM(Card_Charge),0), 2),
             @CardForegif=@CardForegif+ROUND(ISNULL(SUM(Back_Foregift),0), 2)
        FROM dbo.ST_DeleteCardHistory WHERE CONVERT(CHAR(16), New_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), New_Date, 20) <= @sEndDate
   
      --2.存款次数、总存款金额(加上历史记录)
      SELECT @SupplyCount=COUNT(*), @SupplyFund=ROUND(ISNULL(SUM(Supply_Fund),0), 2)
        FROM dbo.ST_SupplyFund WHERE  CONVERT(CHAR(16), Supply_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Supply_Date, 20) <= @sEndDate
      SELECT @SupplyCount=@SupplyCount + COUNT(*), @SupplyFund=@SupplyFund + ROUND(ISNULL(SUM(Supply_Fund),0), 2)
        FROM dbo.ST_SupplyFundHistory WHERE  CONVERT(CHAR(16), Supply_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Supply_Date, 20) <= @sEndDate

      --3.补贴次数、总补贴金额(加上历史记录)
      SELECT @SubsidyCount=COUNT(*), @SubsidyFund=ROUND(ISNULL(SUM(Subsidy_Fund),0), 2)
        FROM dbo.ST_Subsidy WHERE CONVERT(CHAR(16), Subsidy_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Subsidy_Date, 20) <= @sEndDate
      SELECT @SubsidyCount=@SubsidyCount + COUNT(*), @SubsidyFund=@SubsidyFund + ROUND(ISNULL(SUM(Subsidy_Fund),0), 2)
        FROM dbo.ST_SubsidyHistory WHERE CONVERT(CHAR(16), Subsidy_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Subsidy_Date, 20) <= @sEndDate

      --4.补卡次数、总补卡费用(加上历史记录)
      SELECT @AlterCount=COUNT(*), @AlterFee=ROUND(ISNULL(SUM(Alter_Fee),0), 2)
        FROM dbo.ST_AlterCard WHERE Alter_Type=1 AND  CONVERT(CHAR(16), Alter_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Alter_Date, 20) <= @sEndDate
      SELECT @AlterCount=@AlterCount + COUNT(*), @AlterFee=@AlterFee + ROUND(ISNULL(SUM(Alter_Fee),0), 2)
        FROM dbo.ST_AlterCardHistory WHERE Alter_Type=1 AND  CONVERT(CHAR(16), Alter_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Alter_Date, 20) <= @sEndDate

      --4.1.换卡次数(加上历史记录)
      SELECT @ChangeCount=COUNT(*)
        FROM dbo.ST_AlterCard WHERE Alter_Type=0 AND  CONVERT(CHAR(16), Alter_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Alter_Date, 20) <= @sEndDate
      SELECT @ChangeCount=@ChangeCount + COUNT(*)
        FROM dbo.ST_AlterCardHistory WHERE Alter_Type=0 AND  CONVERT(CHAR(16), Alter_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Alter_Date, 20) <= @sEndDate
   
      --5.取款次数、总取款金额(加上历史记录)
      SELECT @TakeCount=COUNT(*), @TakeFund=ROUND(ISNULL(SUM(Take_Fund),0), 2)
        FROM dbo.ST_TakeFund WHERE  CONVERT(CHAR(16), Take_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Take_Date, 20) <= @sEndDate
      SELECT @TakeCount=@TakeCount + COUNT(*), @TakeFund=@TakeFund + ROUND(ISNULL(SUM(Take_Fund),0), 2)
        FROM dbo.ST_TakeFundHistory WHERE   CONVERT(CHAR(16), Take_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Take_Date, 20) <= @sEndDate
   
      --6.销卡次数、销卡退金额、销卡退押金(加上历史记录)
      SELECT @BackCount=COUNT(*), @BackFund=ROUND(ISNULL(SUM(Back_Fund),0), 2),@SmallFund=ROUND(ISNULL(SUM(Back_SmallFund),0), 2), 
             @BackForegift=ROUND(ISNULL(SUM(Back_Foregift),0), 2)
        FROM dbo.ST_DeleteCard WHERE  CONVERT(CHAR(16), Del_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Del_Date, 20) <= @sEndDate
      SELECT @BackCount=@BackCount + COUNT(*), @BackFund=@BackFund + ROUND(ISNULL(SUM(Back_Fund),0), 2),@SmallFund=@SmallFund + ROUND(ISNULL(SUM(Back_SmallFund),0), 2), 
             @BackForegift=@BackForegift + ROUND(ISNULL(SUM(Back_Foregift),0), 2)
        FROM dbo.ST_DeleteCardHistory WHERE  CONVERT(CHAR(16), Del_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Del_Date, 20) <= @sEndDate
   
      --7.小金额转账次数、小金额转账总金额(加上历史记录)
      SELECT @TransferCount=COUNT(*), @TransferFund=ROUND(ISNULL(SUM(Transfer_Fund),0), 2)
        FROM dbo.ST_TransferAccount WHERE CONVERT(CHAR(16), Transfer_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Transfer_Date, 20) <= @sEndDate
      SELECT @TransferCount=@TransferCount + COUNT(*), @TransferFund=@TransferFund + ROUND(ISNULL(SUM(Transfer_Fund),0), 2)
        FROM dbo.ST_TransferAccountHistory WHERE  CONVERT(CHAR(16), Transfer_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Transfer_Date, 20) <= @sEndDate

   END
   ELSE --不是所有的出纳员
   BEGIN
      --0.取得选中的出纳员
      IF OBJECT_ID('tempdb..#SelectPerson') IS NOT NULL DROP TABLE #SelectPerson
      SELECT User_No INTO #SelectPerson FROM ST_UserInfo WHERE 1=0
      WHILE (@UserNo <> '' )
      BEGIN
         SET @Pos = CHARINDEX(',', @UserNo)
         IF @Pos = 0
         BEGIN
            SET @SUserNo=@UserNo
            SET @UserNo=''
         END
         ELSE
         BEGIN
            SET @SUserNo=SUBSTRING(@UserNo, 1, @Pos - 1)
            SET @UserNo=SUBSTRING(@UserNo,@Pos + 1, 8000)
         END
         INSERT INTO #SelectPerson(User_No) VALUES(@SUserNo)
      END
      --1.取得开户次数、开户卡费用、开户卡押金(加上历史记录)
      SELECT @CardCount=COUNT(*), @CardCharge=ROUND(ISNULL(SUM(Card_Charge),0), 2), @CardForegif=ROUND(ISNULL(SUM(Card_Foregift),0), 2)
        FROM dbo.ST_Card  WHERE CONVERT(CHAR(16), Card_NewDate, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Card_NewDate, 20) <= @sEndDate AND User_No IN (SELECT User_No FROM #SelectPerson)
    
      SELECT @CardCount=@CardCount+COUNT(*), @CardCharge=@CardCharge+ROUND(ISNULL(SUM(Card_Charge),0), 2),
             @CardForegif=@CardForegif+ROUND(ISNULL(SUM(Back_Foregift),0), 2)
        FROM dbo.ST_DeleteCard WHERE CONVERT(CHAR(16), New_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), New_Date, 20) <= @sEndDate
              AND New_User_No IN (SELECT User_No FROM #SelectPerson)
     
        SELECT @CardCount=@CardCount+COUNT(*), @CardCharge=@CardCharge+ROUND(ISNULL(SUM(Card_Charge),0), 2),
             @CardForegif=@CardForegif+ROUND(ISNULL(SUM(Back_Foregift),0), 2)
        FROM dbo.ST_DeleteCardHistory  WHERE CONVERT(CHAR(16), New_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), New_Date, 20) <= @sEndDate
             AND New_User_No IN (SELECT User_No FROM #SelectPerson)
   
      --2.存款次数、总存款金额(加上历史记录)
      SELECT @SupplyCount=COUNT(*), @SupplyFund=ROUND(ISNULL(SUM(Supply_Fund),0), 2)
        FROM dbo.ST_SupplyFund WHERE CONVERT(CHAR(16), Supply_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Supply_Date, 20) <= @sEndDate
             AND User_No IN (SELECT User_No FROM #SelectPerson)  
      SELECT @SupplyCount=@SupplyCount + COUNT(*), @SupplyFund=@SupplyFund + ROUND(ISNULL(SUM(Supply_Fund),0), 2)
        FROM dbo.ST_SupplyFundHistory WHERE CONVERT(CHAR(16), Supply_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Supply_Date, 20) <= @sEndDate
             AND User_No IN (SELECT User_No FROM #SelectPerson)  
   
      --3.补贴次数、总补贴金额(加上历史记录)
      SELECT @SubsidyCount=COUNT(*), @SubsidyFund=ROUND(ISNULL(SUM(Subsidy_Fund),0), 2)
        FROM dbo.ST_Subsidy WHERE CONVERT(CHAR(16), Subsidy_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Subsidy_Date, 20) <= @sEndDate
              AND User_No IN (SELECT User_No FROM #SelectPerson)
      SELECT @SubsidyCount=@SubsidyCount + COUNT(*), @SubsidyFund=@SubsidyFund + ROUND(ISNULL(SUM(Subsidy_Fund),0), 2)
        FROM dbo.ST_SubsidyHistory WHERE  CONVERT(CHAR(16), Subsidy_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Subsidy_Date, 20) <= @sEndDate
             AND User_No IN (SELECT User_No FROM #SelectPerson)
   
      --4.补卡次数、总补卡费用(加上历史记录)
      SELECT @AlterCount=COUNT(*), @AlterFee=ROUND(ISNULL(SUM(Alter_Fee),0), 2)
        FROM dbo.ST_AlterCard WHERE Alter_Type=1 AND  CONVERT(CHAR(16), Alter_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Alter_Date, 20) <= @sEndDate
             AND User_No IN (SELECT User_No FROM #SelectPerson)
      SELECT @AlterCount=@AlterCount + COUNT(*), @AlterFee=@AlterFee + ROUND(ISNULL(SUM(Alter_Fee),0), 2)
        FROM dbo.ST_AlterCardHistory  WHERE Alter_Type=1 AND 
             CONVERT(CHAR(16), Alter_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Alter_Date, 20) <= @sEndDate
             AND User_No IN (SELECT User_No FROM #SelectPerson)

      --4.1.换卡次数(加上历史记录)
      SELECT @ChangeCount=COUNT(*)
        FROM dbo.ST_AlterCard WHERE Alter_Type=0 AND  CONVERT(CHAR(16), Alter_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Alter_Date, 20) <= @sEndDate 
        AND User_No IN (SELECT User_No FROM #SelectPerson)
      SELECT @ChangeCount=@ChangeCount + COUNT(*)
        FROM dbo.ST_AlterCardHistory WHERE Alter_Type=0 AND  CONVERT(CHAR(16), Alter_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Alter_Date, 20) <= @sEndDate 
        AND User_No IN (SELECT User_No FROM #SelectPerson)
   
      --5.取款次数、总取款金额(加上历史记录)
      SELECT @TakeCount=COUNT(*), @TakeFund=ROUND(ISNULL(SUM(Take_Fund),0), 2)
        FROM dbo.ST_TakeFund WHERE  CONVERT(CHAR(16), Take_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Take_Date, 20) <= @sEndDate
             AND User_No IN (SELECT User_No FROM #SelectPerson)
      SELECT @TakeCount=@TakeCount + COUNT(*), @TakeFund=@TakeFund + ROUND(ISNULL(SUM(Take_Fund),0), 2)
        FROM dbo.ST_TakeFundHistory WHERE   CONVERT(CHAR(16), Take_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Take_Date, 20) <= @sEndDate
             AND User_No IN (SELECT User_No FROM #SelectPerson)
   
      --6.销卡次数、销卡退金额、销卡退押金(加上历史记录)
      SELECT @BackCount=COUNT(*), @BackFund=ROUND(ISNULL(SUM(Back_Fund),0), 2), @BackForegift=ROUND(ISNULL(SUM(Back_Foregift),0), 2),
             @SmallFund=ROUND(ISNULL(SUM(Back_SmallFund),0), 2)
        FROM dbo.ST_DeleteCard WHERE  CONVERT(CHAR(16), Del_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Del_Date, 20) <= @sEndDate
 	AND User_No IN (SELECT User_No FROM #SelectPerson)
      SELECT @BackCount=@BackCount + COUNT(*), @BackFund=@BackFund + ROUND(ISNULL(SUM(Back_Fund),0), 2), @BackForegift=@BackForegift + ROUND(ISNULL(SUM(Back_Foregift),0), 2),
             @SmallFund=@SmallFund + ROUND(ISNULL(SUM(Back_SmallFund),0), 2)
        FROM dbo.ST_DeleteCardHistory WHERE  CONVERT(CHAR(16), Del_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Del_Date, 20) <= @sEndDate
	 AND User_No IN (SELECT User_No FROM #SelectPerson)
   
      --7.小金额转账次数、小金额转账总金额(加上历史记录)
      SELECT @TransferCount=COUNT(*), @TransferFund=ROUND(ISNULL(SUM(Transfer_Fund),0), 2)
        FROM dbo.ST_TransferAccount WHERE  CONVERT(CHAR(16), Transfer_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Transfer_Date, 20) <= @sEndDate
	AND User_No IN (SELECT User_No FROM #SelectPerson)   
      SELECT @TransferCount=@TransferCount + COUNT(*), @TransferFund=@TransferFund + ROUND(ISNULL(SUM(Transfer_Fund),0), 2)
        FROM dbo.ST_TransferAccountHistory WHERE  CONVERT(CHAR(16), Transfer_Date, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Transfer_Date, 20) <= @sEndDate
	AND User_No IN (SELECT User_No FROM #SelectPerson)   

   END
   --8.总计次数
   SET @Count=@CardCount+@SupplyCount+@SubsidyCount+@AlterCount+@TakeCount+@BackCount+@TransferCount
   
   --9.应缴金额
   SET @Money=ROUND(@CardCharge+@CardForegif+@SupplyFund+@AlterFee-@TakeFund-@BackFund-@BackForegift-@SmallFund, 2)
   

   --10.总消费金额、总消费次数(加上历史记录)
   SELECT @AllConsumeCount=COUNT(*), @AllConsumeMoney=ROUND(ISNULL(SUM(Consume_Fund),0), 2)
   FROM dbo.XF_Consume WHERE  CONVERT(CHAR(16), Consume_Time, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Consume_Time, 20)   <= @sEndDate
   SELECT @AllConsumeCount=@AllConsumeCount + COUNT(*), @AllConsumeMoney=@AllConsumeMoney + ROUND(ISNULL(SUM(Consume_Fund),0), 2)
   FROM dbo.XF_ConsumeHistory WHERE  CONVERT(CHAR(16), Consume_Time, 20) >=  @sBgnDate AND CONVERT (CHAR(16), Consume_Time, 20) <= @sEndDate

SELECT @CardCount[Card_Count], @SupplyCount[Supply_Count], @SubsidyCount[Subsidy_Count], @AlterCount[Alter_Count],
	@TakeCount[Take_Count], @BackCount[Back_Count], @TransferCount[Transfer_Count],@ChangeCount[Change_Count],
	@CardCharge[Card_Charge], @CardForegif[Card_Foregif], @SupplyFund[Supply_Fund], @SubsidyFund[Subsidy_Fund],@SmallFund[Small_Fund],
	@AlterFee[Alter_Fee], @TakeFund[Take_Fund], @BackFund[Back_Fund], @BackForegift[Back_Foregift], @TransferFund[Transfer_Fund],
	@Count[Count], @Money[Mny_Sum], @sBgnDate[Date_Bgn], @sEndDate[Date_End], @UserName[User_Name],
	@AllConsumeCount[AllConsumeCount],@AllConsumeMoney[AllConsumeMoney]
GO

--st_pd_CardStatusRep卡状态统计报表
IF OBJECT_ID('dbo.st_pd_CardStatusRep') IS NOT NULL DROP PROCEDURE dbo.st_pd_CardStatusRep
GO

CREATE PROCEDURE dbo.st_pd_CardStatusRep
   @UserNo   VARCHAR(40),   --用户编号
   @DeptID   VARCHAR(8000), --出纳员编号
   @DeptName VARCHAR(8000), --出纳员名称
   @DateBgn  DATETIME,      --统计起始日期
   @DateEnd  DATETIME       --统计终止日期
AS
-- DECLARE @DeptID VARCHAR(8000),@DeptName VARCHAR(8000),@DateBgn DATETIME,@DateEnd DATETIME,@UserNo VARCHAR(40)
-- SELECT @DeptID='AllDepartments',@DeptName='所有部门', @DateBgn='2005-01-01',@DateEnd=GETDATE(),@UserNo='Admin'

 DECLARE @SDeptID VARCHAR(20),@Pos INT
   DECLARE @sBgnDate VARCHAR(16), @sEndDate VARCHAR(16)
   SET @sBgnDate = '' +  CONVERT(CHAR(16), @DateBgn, 20)  + ''
   SET @sEndDate = '' +  CONVERT(CHAR(16), @DateEnd, 20)  + ''

  -- SET @DateEnd=DATEADD(second, -1, @DateEnd + 1)

   IF @DeptID <> 'AllDepartments' --选择的不是所有部门
   BEGIN   
      --1.取得选择的部门并插入到临时表#SelectDept中
      IF OBJECT_ID('tempdb..#SelectDept') IS NOT NULL DROP TABLE #SelectDept
      SELECT Dept_ID INTO #SelectDept FROM dbo.ST_Department WHERE 1=0
      WHILE (@DeptID <> '' )
      BEGIN
         SET @Pos = CHARINDEX(',', @DeptID)
         IF @Pos = 0
         BEGIN
            SET @SDeptID=@DeptID
            SET @DeptID=''
         END
         ELSE
         BEGIN
            SET @SDeptID=SUBSTRING(@DeptID, 1, @Pos - 1)
            SET @DeptID=SUBSTRING(@DeptID, @Pos + 1, 8000)
         END
         INSERT INTO #SelectDept(Dept_ID) VALUES(@SDeptID)
      END
      --2.查询符合条件的记录
      SELECT @DeptName[Select_Dept],@DateBgn[Date_Bgn],@DateEnd[Date_End],Dept_Name,COUNT(*)[Card_Count],
             SUM(CASE WHEN Card_State=0 THEN 1 ELSE 0 END)[Using_Count],
             SUM(CASE WHEN Card_State=1 THEN 1 ELSE 0 END)[Loss_Count],
             SUM(CASE WHEN Card_State=2 THEN 1 ELSE 0 END)[Logout_Count]
        FROM (SELECT Person_ID,Card_State,Card_NewDate FROM dbo.ST_Card
               UNION ALL SELECT Person_ID,2[Card_Sate],Del_Date[Card_NewDate] FROM dbo.ST_DeleteCard
             ) A
        LEFT JOIN dbo.ST_Person B ON B.Person_ID=A.Person_ID
        LEFT JOIN dbo.ST_Department C ON C.Dept_ID=B.Dept_ID
        WHERE CONVERT(CHAR(16),  A.Card_NewDate, 20)  >=  @sBgnDate and 
	          CONVERT(CHAR(16),  A.Card_NewDate, 20) <=  @sEndDate 
         AND C.Is_Del=0 AND C.Dept_ID IN (SELECT Dept_ID FROM #SelectDept)
       GROUP BY C.Dept_Name
   END
   ELSE --选择的是所有部门
   BEGIN
      SELECT @DeptName[Select_Dept],@sBgnDate[Date_Bgn],@sEndDate[Date_End],Dept_Name,COUNT(*)[Card_Count],
             SUM(CASE WHEN Card_State=0 THEN 1 ELSE 0 END)[Using_Count],
             SUM(CASE WHEN Card_State=1 THEN 1 ELSE 0 END)[Loss_Count],
             SUM(CASE WHEN Card_State=2 THEN 1 ELSE 0 END)[Logout_Count]
        FROM (SELECT Person_ID,Card_State,Card_NewDate FROM dbo.ST_Card
               UNION ALL SELECT Person_ID,2[Card_Sate],Del_Date[Card_NewDate] FROM dbo.ST_DeleteCard
             ) A
        LEFT JOIN dbo.ST_Person B ON B.Person_ID=A.Person_ID
        LEFT JOIN dbo.ST_Department C ON C.Dept_ID=B.Dept_ID
        WHERE CONVERT(CHAR(16),  A.Card_NewDate, 20)  >=  @sBgnDate and 
	          CONVERT(CHAR(16),  A.Card_NewDate, 20) <=  @sEndDate 
         AND C.Is_Del=0 AND C.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE Check_State=0 AND User_No=@UserNo)
       GROUP BY C.Dept_Name
   END 
GO

-- =============================================
-- 取得指定部门编号的最大下属部门编号
-- =============================================

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'st_pd_GetMaxDeptNo' AND type = 'P')
    DROP PROCEDURE dbo.st_pd_GetMaxDeptNo
GO

CREATE PROCEDURE dbo.st_pd_GetMaxDeptNo
	@DeptNo VARCHAR(30) --指定的部门编号
AS
	DECLARE @MaxDeptNo VARCHAR(30),@DeptIndex INT
   SELECT @MaxDeptNo=ISNULL(MAX(Dept_No),@DeptNo+'00') FROM dbo.ST_Department 
    WHERE Dept_No LIKE @DeptNo+'%' AND LEN(Dept_No)=LEN(@DeptNo)+2
   SET @DeptIndex=CAST(SUBSTRING(@MaxDeptNo,LEN(@DeptNo)+1,2) AS INT)+1
   IF @DeptIndex<99 
   BEGIN
      IF @DeptIndex<10 
         SELECT @DeptNo+'0'+CAST(@DeptIndex AS VARCHAR)
      ELSE   
         SELECT @DeptNo+CAST(@DeptIndex AS VARCHAR)
   END
   ELSE
     RAISERROR('Over Max Department No 99!', 16, 1)
GO

-- =============================================
-- 取得指定表的最大ID
-- =============================================

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'st_pd_GetMaxID' AND type = 'P')
    DROP PROCEDURE dbo.st_pd_GetMaxID
GO

CREATE PROCEDURE dbo.st_pd_GetMaxID
	@TableName VARCHAR(40) --指定表的名称
AS
	DECLARE @MaxTableID INT
   SELECT @MaxTableID=Current_ID FROM dbo.ST_Identity WHERE Table_Name=@TableName
   IF @MaxTableID IS NULL --如果记录不存在就插入一条新的记录
   BEGIN
      INSERT INTO dbo.ST_Identity(Table_Name,Current_ID) VALUES(@TableName,1)
      SET @MaxTableID=1
   END
   UPDATE dbo.ST_Identity SET Current_ID=Current_ID+1 WHERE Table_Name=@TableName
   SELECT @MaxTableID
GO

-- =============================================
-- 取得指定表的最大ID
-- =============================================

IF EXISTS(SELECT name FROM sysobjects WHERE name='st_pd_GetMaxIDEx' AND type='P')
    DROP PROCEDURE dbo.st_pd_GetMaxIDEx
GO

CREATE PROCEDURE dbo.st_pd_GetMaxIDEx 
	(@TableName VARCHAR(40),    
    @MaxTableID INT OUTPUT,
    @Increment INT = 1)
AS
   SELECT @MaxTableID=Current_ID FROM dbo.ST_Identity WHERE Table_Name=@TableName
   IF @MaxTableID IS NULL --如果记录不存在就插入一条新的记录
   BEGIN
      INSERT INTO dbo.ST_Identity(Table_Name,Current_ID) VALUES(@TableName,1)
      SET @MaxTableID=1
   END
   UPDATE dbo.ST_Identity SET Current_ID=Current_ID+@Increment WHERE Table_Name=@TableName
GO


-- =============================================
-- 自动维护部门权限信息触发器
-- =============================================
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'st_tg_ManageDeptRight' 
	   AND 	  type = 'TR')
    DROP TRIGGER dbo.st_tg_ManageDeptRight
GO

CREATE TRIGGER dbo.st_tg_ManageDeptRight
ON dbo.ST_Department
FOR DELETE,INSERT,UPDATE
AS 
   DECLARE @IsDelete BIT,@IsInsert BIT,@IsUpdate BIT
	IF EXISTS(SELECT Dept_ID FROM DELETED)  SET @IsDelete=1 ELSE SET @IsDelete=0
   IF EXISTS(SELECT Dept_ID FROM INSERTED) SET @IsInsert=1 ELSE SET @IsInsert=0   
   IF @IsInsert=1 AND @IsDelete=1 
      SELECT @IsUpdate=1,@IsDelete=0,@IsInsert=0
   ELSE
      SET @IsUpdate=0

   IF @IsInsert=1 --新增部门
   BEGIN
      INSERT INTO dbo.ST_DeptRight(Dept_ID,User_No,Check_State)
      SELECT Dept_ID,'Admin',0 FROM INSERTED
   END
   ELSE IF @IsUpdate=1 AND UPDATE(Is_Del) --注销部门
   BEGIN
      DELETE dbo.ST_DeptRight WHERE Dept_ID IN (SELECT Dept_ID FROM INSERTED)
   END
   IF @IsDelete=1 --删除部门
   BEGIN
      DELETE dbo.ST_DeptRight WHERE Dept_ID IN (SELECT Dept_ID FROM DELETED)
   END
GO

-- =============================================
-- 自动维护系统权限信息触发器
-- =============================================
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'st_tg_ManageRight' 
	   AND 	  type = 'TR')
    DROP TRIGGER dbo.st_tg_ManageRight
GO

CREATE TRIGGER dbo.st_tg_ManageRight
ON dbo.ST_MenuInfo
FOR DELETE,INSERT
AS 
   DECLARE @IsDelete BIT,@IsInsert BIT
	IF EXISTS(SELECT Menu_ID FROM DELETED)  SET @IsDelete=1 ELSE SET @IsDelete=0
   IF EXISTS(SELECT Menu_ID FROM INSERTED) SET @IsInsert=1 ELSE SET @IsInsert=0
   IF @IsInsert=1 --新增菜单
   BEGIN
      --1.增加权限列表
      INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
      SELECT 0,Menu_No,'使用' FROM INSERTED
      --2.增加系统权限
      INSERT INTO dbo.ST_RightInfo(Menu_ID,Form_Name,Right_Values)
      SELECT Menu_ID,Menu_No,'0' FROM INSERTED
      --3.增加角色权限默认管理员组
      INSERT INTO dbo.ST_RoleRight(Role_No,Menu_ID,Right_Values)
      SELECT 'Administrators',Menu_ID,'0' FROM INSERTED
   END
   ELSE IF @IsDelete=1
   BEGIN
      --1.删除角色权限
      DELETE FROM dbo.ST_RoleRight WHERE Menu_ID IN (SELECT Menu_ID FROM DELETED)
      --2.删除系统权限
      DELETE FROM dbo.ST_RightInfo WHERE Menu_ID IN (SELECT Menu_ID FROM DELETED)
      --3.删除权限列表
      DELETE FROM dbo.ST_RightValue WHERE Form_Name IN (SELECT Menu_No FROM DELETED)
   END
GO

-- =============================================
-- 视图：核心平台人员与部门、表码进行关联
-- =============================================

IF OBJECT_ID('dbo.V_STPerson') IS NOT NULL DROP VIEW dbo.V_STPerson
GO

CREATE VIEW dbo.V_STPerson as
SELECT t.dept_name,t.dept_no, p.dept_id,p.card_no,
p.person_id,p.person_name,p.person_no,p.cert_name,p.cert_code,
p.duty,p.title,d.dict_value as [nationality],
[sex]=
CASE 
  WHEN p.sex =1 THEN '男' 
  WHEN p.sex =2 THEN '女'
  ELSE NULL 
END,
p.birthday,
[is_marry]=
CASE 
  WHEN p.is_marry =0 THEN '未婚' 
  WHEN p.is_marry =1 THEN '已婚' 
  ELSE NULL
END,
p.nativePlace,p.address,p.residence,p.is_del,p.remark
FROM dbo.ST_person p 
LEFT OUTER JOIN dbo.st_department t  
on t.dept_id=p.dept_id
LEFT OUTER JOIN dbo.st_dictionary d
on d.dict_id=p.nationality and Table_name = 'BM_NATIONALITY'

GO		
		
-- ==============================================================
-- 转换卡号函数，aOldCard为旧卡号，aCardMaxLen为转换的卡号最长长度.
-- 返回转换后的卡号.
-- ==============================================================

if OBject_ID('dbo.st_fn_ConvertCard') IS NOT NULL
DROP FUNCTION dbo.st_fn_ConvertCard 
GO

Create FUNCTION dbo.st_fn_ConvertCard (@aOldCard varchar(12),@aCardMaxLen INT=10)
RETURNS VARCHAR(12)
AS
BEGIN
   Declare @V0 bigint
   Declare @d1 bigint
   Declare @d2 bigint
   Declare @d3 bigint
   Declare @d4 bigint

   DECLARE @Return VARCHAR(12)

   Set @V0=Cast(@aOldCard as bigint)
   --第1位
   Set  @d1= cast(@V0/(256*256*256) as int)
   Set  @V0= @V0 - @d1*256*256*256
   --第2位
   Set  @d2= cast(@V0/(256*256) as int)
   Set  @V0= @V0 - @d2*256*256
   --第3,4位
   Set  @d3= cast(@V0/256 as int)
   Set  @d4= @V0 - @d3*256
   Set  @Return=
     Case 
       when @aCardMaxLen>8 and @aCardMaxLen<=10 then @aOldCard  
       when @aCardMaxLen>3 and @aCardMaxLen<=6  then @d3*256+@d4
       when @aCardMaxLen>6 and @aCardMaxLen<=8  then @d2*256*256+@d3*256+@d4
       when @aCardMaxLen>0 and @aCardMaxLen<=3  then @d4
       else '0'
     End
   
   RETURN @Return
END
GO

-- =============================================
-- 自动设置核心平台与各子系统人员档案的同步标志.
-- =============================================
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'st_tg_SyncPerson' 
	   AND 	  type = 'TR')
    DROP TRIGGER dbo.st_tg_SyncPerson
GO

CREATE TRIGGER dbo.st_tg_SyncPerson
ON dbo.ST_Person
FOR UPDATE
AS 
   DECLARE @IsDelete BIT,@IsInsert BIT,@IsUpdate BIT
	IF EXISTS(SELECT Person_ID FROM DELETED)  SET @IsDelete=1 ELSE SET @IsDelete=0
   IF EXISTS(SELECT Person_ID FROM INSERTED) SET @IsInsert=1 ELSE SET @IsInsert=0
   IF @IsInsert=1 AND @IsDelete=1 
      SELECT @IsUpdate=1,@IsDelete=0,@IsInsert=0
   ELSE
      SET @IsUpdate=0
   IF @IsUpdate=1 AND (UPDATE(Is_Del) OR UPDATE(Card_No))--更改人员档案的注销标志或卡号字段
   BEGIN
      UPDATE dbo.ST_SubSystem SET Update_State1=0
   END 
GO

-- =============================================
-- 自动设置核心平台与各子系统人员档案的同步标志.
-- =============================================
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'st_tg_SyncSubSysRight' 
	   AND 	  type = 'TR')
    DROP TRIGGER st_tg_SyncSubSysRight
GO

CREATE TRIGGER dbo.st_tg_SyncSubSysRight
ON ST_SubSysRight
FOR INSERT,DELETE
AS 
   DECLARE @IsDelete BIT,@IsInsert BIT
	IF EXISTS(SELECT Person_ID FROM DELETED)  SET @IsDelete=1 ELSE SET @IsDelete=0
   IF EXISTS(SELECT Person_ID FROM INSERTED) SET @IsInsert=1 ELSE SET @IsInsert=0
   IF (@IsInsert=1) OR (@IsDelete=1) --新增子系统权限或者删除子系统权限
   BEGIN
      UPDATE ST_SubSystem SET Update_State1=0,Update_State2=0,Update_State3=0
   END 
GO

-- =============================================
-- 人员调整卡的视图
-- =============================================
IF OBJECT_ID('dbo.V_STAlterCard') IS NOT NULL DROP VIEW dbo.V_STAlterCard
GO

CREATE VIEW dbo.V_STAlterCard
AS
SELECT dbo.ST_Person.Person_No, 
			dbo.ST_AlterCard.Person_ID, 
      dbo.ST_AlterCard.Alter_Date, 
      dbo.ST_AlterCard.User_No, 
      dbo.ST_AlterCard.Card_No, 
      dbo.ST_AlterCard.NewCard_No, 
      dbo.ST_Department.Dept_No, 
      dbo.ST_Department.Dept_Name, 
      dbo.ST_Person.Person_Name, 
      dbo.ST_Person.Dept_ID, 
      dbo.ST_AlterCard.Alter_Type, 
      dbo.ST_AlterCard.Alter_Fee
FROM dbo.ST_AlterCard LEFT OUTER JOIN
      dbo.ST_Person ON 
      dbo.ST_AlterCard.Person_ID = dbo.ST_Person.Person_ID LEFT OUTER JOIN
      dbo.ST_Department ON dbo.ST_Department.Dept_ID = dbo.ST_Person.Dept_ID

GO

-- =============================================
--人员卡的视图
-- =============================================

IF OBJECT_ID('dbo.V_STPersonCard') IS NOT NULL DROP VIEW dbo.V_STPersonCard
GO

CREATE VIEW dbo.V_STPersonCard
AS
SELECT dbo.ST_Person.Person_ID, 
			dbo.ST_Person.Dept_ID, 
			dbo.ST_Person.Card_No, 
      dbo.ST_Person.Person_No, 
      dbo.ST_Person.Person_Name, 
      dbo.ST_Department.Dept_Name, 
      dbo.ST_Card.Type_No, 
      dbo.ST_Card.Card_Password, 
      dbo.ST_Card.Card_NewDate, 
      dbo.ST_Card.Card_ValidDate, 
      dbo.ST_Card.Card_State, 
      dbo.ST_Card.Card_SmallBalance, 
      dbo.ST_Card.Card_Balance, 
      dbo.ST_Card.Subsidy_Fund, 
      dbo.ST_CardType.Type_Name, 
      dbo.ST_Department.Dept_No, 
      dbo.ST_CardType.Small_Discount, 
      dbo.ST_CardType.Max_IcFunds,
      dbo.ST_Person.Offwork_Date 
FROM dbo.ST_Person LEFT OUTER JOIN
      dbo.ST_Department ON 
      dbo.ST_Person.Dept_ID = dbo.ST_Department.Dept_ID LEFT OUTER JOIN
      dbo.ST_Card ON 
      dbo.ST_Person.Person_ID = dbo.ST_Card.Person_ID LEFT OUTER JOIN
      dbo.ST_CardType ON dbo.ST_Card.Type_No = dbo.ST_CardType.Type_No
WHERE (dbo.ST_Person.Is_Del = 0)

GO
-- =============================================
--人员的补卡
-- =============================================

IF OBJECT_ID('dbo.V_STSubSidy') IS NOT NULL DROP VIEW dbo.V_STSubSidy
GO

CREATE VIEW dbo.V_STSubSidy
AS
SELECT dbo.ST_Person.Card_No, 
			dbo.ST_Person.Person_No, 
			dbo.ST_Subsidy.Person_ID, 
      dbo.ST_Subsidy.Subsidy_Date, 
      dbo.ST_Subsidy.User_No, 
      dbo.ST_Subsidy.Subsidy_Fund, 
      dbo.ST_Subsidy.Plan_Fund, 
      dbo.ST_Department.Dept_No, 
      dbo.ST_Department.Dept_Name, 
      dbo.ST_CardType.Type_Name, 
      dbo.ST_Person.Person_Name, 
      dbo.ST_Person.Dept_ID, 
      dbo.ST_Card.Type_No
FROM dbo.ST_Subsidy LEFT OUTER JOIN
      dbo.ST_Person ON 
      dbo.ST_Subsidy.Person_ID = dbo.ST_Person.Person_ID LEFT OUTER JOIN
      dbo.ST_Department ON 
      dbo.ST_Person.Dept_ID = dbo.ST_Department.Dept_ID LEFT OUTER JOIN
      dbo.ST_Card ON 
      dbo.ST_Person.Person_ID = dbo.ST_Card.Person_ID LEFT OUTER JOIN
      dbo.ST_CardType ON dbo.ST_Card.Type_No = dbo.ST_CardType.Type_No
Go

-- =============================================
--是否已经安装子系统
-- =============================================
IF OBJECT_ID('dbo.ST_fn_IsInstalled') IS NOT NULL
  DROP FUNCTION dbo.ST_fn_IsInstalled 
GO

CREATE FUNCTION dbo.ST_fn_IsInstalled 
	(@Flag TINYINT = 0)
RETURNS BIT
AS
BEGIN
   DECLARE @Return BIT,@Sum INT
   SET @Return = 0
   IF @Flag = 0 
   BEGIN
      SELECT @Sum=SUM(CASE WHEN IsCreateTable=1 THEN 1 ELSE 0 END) FROM ST_SubSystem WHERE System_No='SF' OR System_No='LH'
      IF @Sum > 0 SET @Return = 1
   END
   RETURN @Return
END
GO

-----------
IF OBJECT_ID('dbo.ST_pd_GetNameList') IS NOT NULL
   DROP PROCEDURE dbo.ST_pd_GetNameList
GO

CREATE PROCEDURE dbo.ST_pd_GetNameList
   (@NameMode TINYINT) --验证名单方式  
AS   
   IF @NameMode = 1 --验证黑名单(黑名单记录和挂失记录)
   BEGIN
      SELECT DISTINCT CAST(Card_No AS BIGINT) FROM
      (SELECT Card_No FROM ST_BlackPerson
        UNION ALL SELECT Card_No FROM ST_Card A
                    LEFT JOIN ST_Person B ON B.Person_ID=A.Person_ID
                   WHERE A.Card_State=1

      ) D ORDER BY CAST(Card_No AS BIGINT)
   END
   ELSE --验证红名单
   BEGIN
      SELECT Card_No FROM ST_Card A
        LEFT JOIN ST_Person B ON B.Person_ID=A.Person_ID
       WHERE A.Card_State=0 ORDER BY CAST(Card_No AS BIGINT)
   END
GO

-----------
--个人平衡报表
-----------
IF OBJECT_ID('dbo.st_pd_PersonalBalanceRep') IS NOT NULL
   DROP PROCEDURE dbo.st_pd_PersonalBalanceRep
GO

CREATE PROCEDURE st_pd_PersonalBalanceRep
   @DeptNo  VARCHAR(40),      --部门编号
   @UserNo  VARCHAR(40),       --用户编号
   @IsDel    BIT             --只否显示注销人员 
AS

DECLARE   @AccountDate Varchar(10)   --财务结算日期
IF @IsDel =0
BEGIN   
   SELECT @AccountDate=Param_Value FROM dbo.ST_SysParam Where Param_Code = 'SettleDate'
   SELECT C.Person_No,C.Person_Name,B.Dept_No,B.Dept_Name,ISNULL(Card_Balance,0)[Card_Balance],Card_No,
          ISNULL(Supply_Fund,0)[Supply_Fund],ISNULL(Consume_Fund,0)[Consume_Fund],
          ISNULL(G.Subsidy_Fund,0)[Subsidy_Fund],ISNULL(Take_Fund,0)[Take_Fund],
          ISNULL(Transfer_Fund,0)[Transfer_Fund],
          ISNULL(Supply_Fund,0)+ISNULL(G.Subsidy_Fund,0)-(ISNULL(Consume_Fund,0)+ISNULL(Take_Fund,0)+ISNULL(Transfer_Fund,0)+ISNULL(Card_Balance+ISNULL(M.Back_Fund,0),0))[Mny_Balance]
     FROM ST_Card A
     LEFT JOIN ST_Person C ON C.Person_ID=A.Person_ID
     LEFT JOIN ST_Department B ON B.Dept_ID=C.Dept_ID
     --0.计算销卡的退金额
     LEFT JOIN (SELECT ROUND(ISNULL(SUM(Back_Fund), 0), 2)[Back_Fund],Person_ID 
                FROM ST_DeleteCard 
                GROUP BY Person_ID
                ) M ON M.Person_ID=C.Person_ID
     --1.计算存款金额
     LEFT JOIN (SELECT SUM(Supply_Fund)[Supply_Fund],Person_ID 
                 FROM  (SELECT SUM(Supply_Fund)[Supply_Fund],Person_ID 
                        FROM ST_SupplyFund WHERE Supply_Date >= @AccountDate
                        GROUP BY Person_ID
            				Union All
            		      SELECT Supply_Fund,Person_ID 
                        FROM ST_PersonAccount 
                       ) A
                GROUP BY Person_ID
                ) D ON D.Person_ID=C.Person_ID
     --2.计算消费金额
     LEFT JOIN (SELECT SUM(Consume_Fund)[Consume_Fund],Person_ID
                 FROM (SELECT SUM(Consume_Fund)[Consume_Fund],Person_ID 
                       FROM XF_Consume WHERE Consume_Time >= @AccountDate + ' 23:59:59'
                       GROUP BY Person_ID
                       Union All
                       SELECT (EM_ConsumeFund + XF_ConsumeFund)[Consume_Fund],Person_ID 
                       FROM ST_PersonAccount
                       ) A
               GROUP BY Person_ID
          ) E ON E.Person_ID=C.Person_ID
     --3.计算补贴金额
     LEFT JOIN (SELECT SUM(Subsidy_Fund)[Subsidy_Fund],Person_ID
                FROM (SELECT SUM(Subsidy_Fund)[Subsidy_Fund],Person_ID 
                       FROM ST_Subsidy  WHERE Subsidy_Date >= @AccountDate
                       GROUP BY Person_ID
      					Union All
                      SELECT Subsidy_Fund,Person_ID 
                      FROM ST_PersonAccount
                     ) A
                 GROUP BY Person_ID
               ) G ON G.Person_ID=C.Person_ID
     --4.计算取款金额
     LEFT JOIN (SELECT SUM(Take_Fund)[Take_Fund],Person_ID 
                FROM (SELECT SUM(Take_Fund)[Take_Fund],Person_ID
                      FROM ST_TakeFund  WHERE Take_Date >= @AccountDate
                      GROUP BY Person_ID					
                    Union All
                      SELECT Take_Fund,Person_ID 
                      FROM ST_PersonAccount
                     ) A
                 GROUP BY Person_ID
               ) H ON H.Person_ID=C.Person_ID
      --6.计算转帐金额
     LEFT JOIN (SELECT SUM(Transfer_Fund)[Transfer_Fund],Person_ID 
                FROM (SELECT SUM(Transfer_Fund)[Transfer_Fund],Person_ID
                      FROM ST_TransferAccount WHERE Transfer_Date >= @AccountDate
                      GROUP BY Person_ID
                    Union All
                      SELECT Transfer_Fund,Person_ID 
                      FROM ST_PersonAccount
                      ) A
                GROUP BY Person_ID
               ) N ON N.Person_ID=C.Person_ID
   WHERE B.Dept_No LIKE @DeptNo+'%' AND B.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE Check_State=0 AND User_No=@UserNo)
   ORDER BY B.Dept_No,C.Person_No
END
ELSE
BEGIN
   SELECT @AccountDate=Param_Value FROM dbo.ST_SysParam Where Param_Code = 'SettleDate'
   SELECT A.Person_No,A.Person_Name,B.Dept_No,B.Dept_Name,ISNULL(Card_Balance,0)[Card_Balance],Card_No,
          ISNULL(Supply_Fund,0)[Supply_Fund],ISNULL(Consume_Fund,0)[Consume_Fund],
          ISNULL(G.Subsidy_Fund,0)[Subsidy_Fund],ISNULL(Take_Fund,0)[Take_Fund],
          ISNULL(Transfer_Fund,0)[Transfer_Fund],
          ISNULL(Supply_Fund,0)+ISNULL(G.Subsidy_Fund,0)-(ISNULL(Consume_Fund,0)+ISNULL(Take_Fund,0)+ISNULL(Transfer_Fund,0)+ISNULL(Card_Balance+ISNULL(M.Back_Fund,0),0))[Mny_Balance]
     FROM ST_Person A
     LEFT JOIN  ST_Card C ON A.Person_ID=C.Person_ID
     LEFT JOIN ST_Department B ON A.Dept_ID=B.Dept_ID
     --0.计算销卡的退金额
     LEFT JOIN (SELECT ROUND(ISNULL(SUM(Back_Fund), 0), 2)[Back_Fund],Person_ID 
                FROM ST_DeleteCard 
                GROUP BY Person_ID
                ) M ON M.Person_ID=C.Person_ID
     --1.计算存款金额
     LEFT JOIN (SELECT SUM(Supply_Fund)[Supply_Fund],Person_ID 
                 FROM  (SELECT SUM(Supply_Fund)[Supply_Fund],Person_ID 
                        FROM ST_SupplyFund WHERE Supply_Date >= @AccountDate
                        GROUP BY Person_ID
            				Union All
            		      SELECT Supply_Fund,Person_ID 
                        FROM ST_PersonAccount 
                       ) A
                GROUP BY Person_ID
                ) D ON D.Person_ID=C.Person_ID
     --2.计算消费金额
     LEFT JOIN (SELECT SUM(Consume_Fund)[Consume_Fund],Person_ID
                 FROM (SELECT SUM(Consume_Fund)[Consume_Fund],Person_ID 
                       FROM XF_Consume WHERE Consume_Time >= @AccountDate + ' 23:59:59'
                       GROUP BY Person_ID
                       Union All
                       SELECT (EM_ConsumeFund + XF_ConsumeFund)[Consume_Fund],Person_ID 
                       FROM ST_PersonAccount
                       ) A
               GROUP BY Person_ID
          ) E ON E.Person_ID=C.Person_ID
     --3.计算补贴金额
     LEFT JOIN (SELECT SUM(Subsidy_Fund)[Subsidy_Fund],Person_ID
                FROM (SELECT SUM(Subsidy_Fund)[Subsidy_Fund],Person_ID 
                       FROM ST_Subsidy  WHERE Subsidy_Date >= @AccountDate
                       GROUP BY Person_ID
      					Union All
                      SELECT Subsidy_Fund,Person_ID 
                      FROM ST_PersonAccount
                     ) A
                 GROUP BY Person_ID
               ) G ON G.Person_ID=C.Person_ID
     --4.计算取款金额
     LEFT JOIN (SELECT SUM(Take_Fund)[Take_Fund],Person_ID 
                FROM (SELECT SUM(Take_Fund)[Take_Fund],Person_ID
                      FROM ST_TakeFund  WHERE Take_Date >= @AccountDate
                      GROUP BY Person_ID					
                    Union All
                      SELECT Take_Fund,Person_ID 
                      FROM ST_PersonAccount
                     ) A
                 GROUP BY Person_ID
               ) H ON H.Person_ID=C.Person_ID
      --6.计算转帐金额
     LEFT JOIN (SELECT SUM(Transfer_Fund)[Transfer_Fund],Person_ID 
                FROM (SELECT SUM(Transfer_Fund)[Transfer_Fund],Person_ID
                      FROM ST_TransferAccount WHERE Transfer_Date >= @AccountDate
                      GROUP BY Person_ID
                    Union All
                      SELECT Transfer_Fund,Person_ID 
                      FROM ST_PersonAccount
                      ) A
                GROUP BY Person_ID
               ) N ON N.Person_ID=C.Person_ID
   WHERE B.Dept_No LIKE @DeptNo+'%' AND B.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE Check_State=0 AND User_No=@UserNo)
   ORDER BY B.Dept_No,A.Person_No
END

GO

--个人充值明细报表
IF OBJECT_ID('ST_pd_SupplyFundList') IS NOT NULL DROP PROCEDURE ST_pd_SupplyFundList
GO

/*******************************************/
/* 类型：报表                              */
/* 模块来源：核心平台                      */
/* 报表名称：个人充值明细报表          */
/*                                         */
/*******************************************/
CREATE PROCEDURE dbo.ST_pd_SupplyFundList
(@DateBgn VarChar(40),      --起始日期
@DateEnd VarChar(40),      --结束日期
@PersonID Int,             --人员标识号
@DeptNo VarChar(256) = '', --部门编号
@UserNo VarChar(40) = ''   --操作员编号
)
AS
Declare @sBgnDate VarChar(16), @sEndDate VarChar(16)
Set @sBgnDate = '' +  Convert(Char(16), @DateBgn, 20)  + ''
Set @sEndDate = '' +  Convert(Char(16), @DateEnd, 20)  + ''
Begin
    If @UserNo='' --按人员查询
        Select A.*,B.Is_Del,B.Card_No,B.Person_Name,B.Person_No,C.Dept_No,C.Dept_Name
        From (

             /*获取取款记录表的数据*/
              Select * From ST_SupplyFund
               Where Person_ID = @PersonID AND Convert(Char(16), Supply_Date, 20) >=  @sBgnDate  and  Convert(Char(16), Supply_Date, 20) <=  @sEndDate
              Union All
              Select * From ST_SupplyFundHistory
               Where Person_ID = @PersonID AND Convert(Char(16), Supply_Date, 20) >=  @sBgnDate  and  Convert(Char(16), Supply_Date, 20) <=  @sEndDate
              ) A
        Left Join ST_Person B On B.Person_ID=A.Person_ID
        Left Join ST_Department C On C.Dept_ID=B.Dept_ID
    Else
        Select A.*,B.Is_Del,B.Card_No,B.Person_Name,B.Person_No,C.Dept_No,C.Dept_Name
        From (

             /*获取取款记录表的数据*/
              Select * From ST_SupplyFund
               Where  Convert(Char(16),  Supply_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Supply_Date, 20) <=  @sEndDate
              Union All
              Select * From ST_SupplyFundHistory
               Where  Convert(Char(16),  Supply_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Supply_Date, 20) <=  @sEndDate
              ) A
        Left Join ST_Person B On B.Person_ID=A.Person_ID
        Left Join ST_Department C On C.Dept_ID=B.Dept_ID
       /**跟据部门及部门所涉及的用户显示权限进行数据过虑*/
         Where  C.Dept_No LIKE @DeptNo+'%'
           AND Exists (Select Dept_ID From ST_DeptRight Where C.Dept_ID =ST_DeptRight.Dept_ID
            AND User_No=@UserNo AND Check_State=0)
END
GO

-- 报表名称：个人取款明细报表                  
IF OBJECT_ID('ST_pd_TakeFundList') IS NOT NULL DROP PROCEDURE ST_pd_TakeFundList
GO

/*******************************************/
/* 类型：报表                              */
/* 模块来源：核心平台                      */
/* 报表名称：个人取款明细报表                  */
/*                                         */
/*******************************************/
CREATE  PROCEDURE dbo.ST_pd_TakeFundList
(@DateBgn VarChar(40),      --起始日期
@DateEnd VarChar(40),      --结束日期
@PersonID INT,             --人员标识号
@DeptNo VarChar(256) = '', --部门编号
@UserNo VarChar(40) = ''   --操作员编号
)

AS
Declare @sBgnDate VarChar(16), @sEndDate VarChar(16)
SET @sBgnDate = '' +  Convert(Char(16), @DateBgn, 20)  + ''
SET @sEndDate = '' +  Convert(Char(16), @DateEnd, 20)  + ''

Begin
    If @UserNo='' --按人员查询
            Select A.*,B.Is_Del,B.Card_No,B.Person_Name,B.Person_No,C.Dept_No,C.Dept_Name
            From (

             /*获取小取款记录表的数据*/
                  Select * From ST_TakeFund
                   Where Person_ID=@PersonID AND
                    Convert(Char(16),  Take_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Take_Date, 20) <=  @sEndDate
                  Union All
                  Select * From ST_TakeFundHistory
                   Where Person_ID=@PersonID AND Convert(Char(16),  Take_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Take_Date, 20) <=  @sEndDate
                 ) A
            Left Join ST_Person B On B.Person_ID=A.Person_ID
            Left Join ST_Department C On C.Dept_ID=B.Dept_ID
    ELSE
            Select A.*,B.Is_Del,B.Card_No,B.Person_Name,B.Person_No,C.Dept_No,C.Dept_Name
            From (

             /*获取小取款记录表的数据*/
                  Select * From ST_TakeFund
                   Where Convert(Char(16),  Take_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Take_Date, 20) <=  @sEndDate
                  Union All
                  Select * From ST_TakeFundHistory
                   Where Convert(Char(16),  Take_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Take_Date, 20) <=  @sEndDate
                 ) A
            Left Join ST_Person B On B.Person_ID=A.Person_ID
            Left Join ST_Department C On C.Dept_ID=B.Dept_ID
       /**跟据部门及部门所涉及的用户显示权限进行数据过虑*/
             Where  C.Dept_No LIKE @DeptNo+'%'
              AND EXISTS (Select Dept_ID From ST_DeptRight Where C.Dept_ID =ST_DeptRight.Dept_ID
               AND User_No=@UserNo AND Check_State=0)
END
GO

--个人转账明细报表
IF OBJECT_ID('ST_pd_TransferAccountList') IS NOT NULL DROP PROCEDURE ST_pd_TransferAccountList
GO

/*******************************************/
/* 类型：报表                              */
/* 模块来源：核心平台                      */
/* 报表名称：个人转账明细报表                  */
/*                                         */
/*******************************************/
CREATE PROCEDURE dbo.ST_pd_TransferAccountList
(@DateBgn VarChar(40),      --起始日期
@DateEnd VarChar(40),      --结束日期
@PersonID INT,             --人员标识号
@DeptNo VarChar(256) = '', --部门编号
@UserNo VarChar(40) = ''   --操作员编号
)
AS
Declare @sBgnDate VarChar(16), @sEndDate VarChar(16)
Set @sBgnDate = '' +  Convert(Char(16), @DateBgn, 20)  + ''
Set @sEndDate = '' +  Convert(Char(16), @DateEnd, 20)  + ''
Begin
    If @UserNo='' --按人员查询
        Select A.*,B.Is_Del,B.Card_No,B.Person_Name,B.Person_No,C.Dept_No,C.Dept_Name
        From (

             /*获取小金额转账表的数据*/
              Select * From ST_TransferAccount
               Where Person_ID=@PersonID AND  Convert(Char(16),  Transfer_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
              Union All
              Select * From ST_TransferAccountHistory
               Where Person_ID=@PersonID AND Convert(Char(16),  Transfer_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
              ) A
        Left JOin ST_Person B On B.Person_ID=A.Person_ID
        Left JOin ST_Department C On C.Dept_ID=B.Dept_ID
    Else
        Select A.*,B.Is_Del,B.Card_No,B.Person_Name,B.Person_No,C.Dept_No,C.Dept_Name
        From (

             /*获取小金额转账表的数据*/
              Select * From ST_TransferAccount
               Where Convert(Char(16),  Transfer_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
              Union All
              Select * From ST_TransferAccountHistory
               Where Convert(Char(16),  Transfer_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
             ) A
        Left JOin ST_Person B On B.Person_ID=A.Person_ID
        Left JOin ST_Department C On C.Dept_ID=B.Dept_ID
       /**跟据部门及部门所涉及的用户显示权限进行数据过虑*/
         Where  C.Dept_No LIKE @DeptNo+'%'
          AND EXISTS (Select Dept_ID From ST_DeptRight Where C.Dept_ID =ST_DeptRight.Dept_ID
           AND User_No=@UserNo AND Check_State=0)
END
GO

--注销人员明细
IF OBJECT_ID('st_pd_DelDetail') IS NOT NULL DROP PROCEDURE st_pd_DelDetail
GO

/*******************************************/
/* 类型：报表                              */
/* 模块来源：核心平台                      */
/* 报表名称：注销人员明细                  */
/*                                         */
/*******************************************/

CREATE PROCEDURE st_pd_DelDetail
@UserNo NVarChar(40), --用户编号
@aValue NVarChar(20),--部门树选择传递进来的值
@DelDateBgn NVarChar(20),--开始日期
@DelDateEnd NVarChar(20), --结束日期
@IsDept  Int       --是否选择的是部门
AS

Begin
  IF @IsDept = 1  --判断是否是部门
Begin
Select a.Dept_No,a.Dept_Name,a.PersOn_No,a.PersOn_Name,a.Card_No,b.* From 
(
  Select Card_No,IS_Del,PersOn_No,Dept_No,PersOn_Name,Dept_Name,PersOn_ID From V_STPersOn  
  Where Exists(
		Select Dept_ID From ST_DeptRight  
		Where Dept_ID=V_STPersOn.Dept_ID AND Check_State=0    and User_No = @UserNo
               ) AND Is_Del=1 AND (Dept_No like @aValue + '%')
) a 
Left JOIN ST_DeleteCard b On a.PersOn_ID = b.PersOn_ID 
Where COnvert(Char(16),b.Del_Date,20) >=@DelDateBgn
AND   COnvert(Char(16),b.Del_Date,20) <=@DelDateEnd
End
End
GO

--补贴领取明细
IF OBJECT_ID('st_pd_TakeSubsidyDetail') IS NOT NULL DROP PROCEDURE st_pd_TakeSubsidyDetail
GO

/*******************************************************/
/* 类型：报表                                           */
/* 模块来源：核心平台                                    */
/* 报表名称：补贴领取明细                                */
/*修改说明：                                            */
/*2008-11-11  新增补贴状态过滤                          */
/*******************************************************/

CREATE PROCEDURE st_pd_TakeSubsidyDetail
@Dept_No NVarChar(20), --过滤条件
@DateTimeBegin NVarChar(20),--开始日期
@DateTimeEnd NVarChar(20),--结束日期
@ApplyState Int  --0表示领取失败，1表示领取成功

AS
Declare @SQL NVarChar(4000);
Declare @ApplyStateSQL NVarChar(200);
Declare @OrderSQL NVarChar(200);

Set @SQL = ' ';
Set @ApplyStateSQL = ' ';
Set @OrderSQL = ' ';

Begin
Set @SQL = 'SELECT a.person_id,c.person_no,a.card_no,a.apply_time,a.subsidy_fund,a.apply_state,d.dept_no, '+
    ' a.moc_no,a.card_balance,c.person_name,b.subsidy_fund bSubsidy_fund FROM xf_subsidy a '+
     ' LEFT join st_card b ON a.person_id = b.person_id ' +
      ' LEFT join st_person c ON b.person_id = c.person_id '+
       ' LEFT join st_department d ON c.dept_id = d.dept_id '+
        ' Where d.dept_no like  ''' + @Dept_No + '''' +
         ' and  Apply_time >= ''' + @DateTimeBegin + '''' +
          ' and Apply_time  <= ''' + @DateTimeEnd + '''';

Set @ApplyStateSQL = ' and (a.apply_state =0 ) ';--or a.apply_state =3

Set @OrderSQL = ' order by d.Dept_No, a.Person_id, a.Apply_Time';

If @ApplyState = 0
    Set @SQL = @SQL + @ApplyStateSQL + @OrderSQL;
Else
    Set @SQL = @SQL + @OrderSQL;

Exec(@SQL)

END
GO
--个人换用新卡明细报表
IF OBJECT_ID('ST_pd_AlterCardList') IS NOT NULL DROP PROCEDURE ST_pd_AlterCardList
GO

/*******************************************/
/* 类型：报表                              */
/* 模块来源：核心平台                      */
/* 报表名称：个人换用新卡明细报表          */
/*                                         */
/*******************************************/
CREATE PROCEDURE dbo.ST_pd_AlterCardList
(
    @AlterType TinyInt,        --更改类型
    @DateBgn VarChar(40),      --起始日期
    @DateEnd VarChar(40),      --结束日期
    @PersonID Int,             --人员标识号
    @DeptNo VarChar(256) = '', --部门编号
    @UserNo VarChar(40) = ''   --操作员编号
)
AS
Declare @sBgnDate VarChar(16), @sEndDate VarChar(16)
Set @sBgnDate = '' +  Convert(Char(16), @DateBgn, 20)  + ''
Set @sEndDate = '' +  Convert(Char(16), @DateEnd, 20)  + ''
    If @UserNo='' --按人员查询
        Select A.*,B.Is_Del,B.Person_Name,B.Person_No,C.Dept_No,C.Dept_Name
        From (

              /*获取卡变更表的数据*/
              Select * From ST_AlterCard
               Where Person_ID=@PersonID AND Alter_Type=@AlterType
                AND Convert(Char(16),  Alter_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Alter_Date, 20) <=  @sEndDate
              Union ALL
              Select * From ST_AlterCardHistory
               Where Person_ID=@PersonID AND Alter_Type=@AlterType
                AND Convert(Char(16),  Alter_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Alter_Date, 20) <=  @sEndDate

              ) A
        Left Join ST_Person B ON B.Person_ID=A.Person_ID
        Left Join ST_Department C ON C.Dept_ID=B.Dept_ID
    Else
        Select A.*,B.Is_Del,B.Person_Name,B.Person_No,C.Dept_No,C.Dept_Name
        From (

              /*获取卡变更表的数据*/
              Select * From ST_AlterCard
               Where Alter_Type=@AlterType AND Convert(Char(16),Alter_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Alter_Date, 20) <=  @sEndDate
              Union ALL
              Select * From ST_AlterCardHistory
               Where Alter_Type=@AlterType AND Convert(Char(16),Alter_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Alter_Date, 20) <=  @sEndDate

              ) A
        Left Join ST_Person B ON B.Person_ID=A.Person_ID
        Left Join ST_Department C ON C.Dept_ID=B.Dept_ID
       /**跟据部门及部门所涉及的用户显示权限进行数据过虑*/
         Where  C.Dept_No LIKE @DeptNo+'%'
          AND EXISTS (Select Dept_ID From ST_DeptRight Where C.Dept_ID =ST_DeptRight.Dept_ID
           AND User_No=@UserNo AND Check_State=0)
GO

--个人发放补贴明细报表 
IF OBJECT_ID('ST_pd_SubsidyList') IS NOT NULL DROP PROCEDURE ST_pd_SubsidyList
GO

/*******************************************/
/* 类型：报表                              */
/* 模块来源：核心平台                      */
/* 报表名称：个人发放补贴明细报表          */
/*                                         */
/*******************************************/
CREATE PROCEDURE dbo.ST_pd_SubsidyList
(
    @DateBgn VarChar(40),      --起始日期
    @DateEnd VarChar(40),      --结束日期
    @PersonID Int,             --人员标识号
    @DeptNo VarChar(256) = '', --部门编号
    @UserNo VarChar(40) = ''   --操作员编号
)
AS
Declare @sBgnDate VarChar(16), @sEndDate VarChar(16)
Set @sBgnDate = '' +  Convert(Char(16), @DateBgn, 20)  + ''
Set @sEndDate = '' +  Convert(Char(16), @DateEnd, 20)  + ''
    If @UserNo='' --按人员查询
        Select A.*,B.Is_Del,B.Person_Name,B.Person_No,C.Dept_No,C.Dept_Name,Card_No
        From (

             /*获取补贴记录表的数据*/
              Select * From ST_Subsidy
               Where Person_ID = @PersonID AND Convert(Char(16), Subsidy_Date, 20) >=  @sBgnDate  and  Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate
              Union ALL
              Select * From ST_SubsidyHistory
               Where Person_ID = @PersonID AND Convert(Char(16), Subsidy_Date, 20) >=  @sBgnDate  and  Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate

              ) A
        Left Join ST_Person B On B.Person_ID=A.Person_ID
        Left Join ST_Department C On C.Dept_ID=B.Dept_ID
    Else
        Select A.*,B.Is_Del,B.Person_Name,B.Person_No,C.Dept_No,C.Dept_Name,Card_No
        From (

             /*获取补贴记录表的数据*/
              Select * From ST_Subsidy
               Where Convert(Char(16), Subsidy_Date, 20)  >=  @sBgnDate  and  Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate
              Union ALL
              Select * From ST_SubsidyHistory
               Where  Convert(Char(16), Subsidy_Date, 20) >=  @sBgnDate  and  Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate

              ) A
        Left Join ST_Person B On B.Person_ID=A.Person_ID
        Left Join ST_Department C On C.Dept_ID=B.Dept_ID
       /**跟据部门及部门所涉及的用户显示权限进行数据过虑*/
         Where  C.Dept_No LIKE @DeptNo+'%'
          AND EXISTS (Select Dept_ID From ST_DeptRight Where C.Dept_ID =ST_DeptRight.Dept_ID
           AND User_No=@UserNo AND Check_State=0)
GO

--处理假写卡
IF OBJECT_ID('XF_pd_ConsumeMatchingAll') IS NOT NULL DROP PROCEDURE XF_pd_ConsumeMatchingAll
GO

/*********************************************/
/* 类型：业务计算                            */
/* 模块来源：核心平台                        */
/* 过程名称：处理假写卡                      */
/*                                           */
/*********************************************/
CREATE PROCEDURE dbo.XF_pd_ConsumeMatchingAll
AS

Declare @PersonID INT
--1.获取所有有假写卡记录的人员
Declare persons_cursor Cursor Local Forward_only Static Read_only for 
    Select Distinct Person_ID From XF_ConsumeFake Where Data_Type=8
/*formerly：  Declare persons_cursor Cursor Local  for 
   Select Person_ID From XF_ConsumeFake Where Data_Type=8 Group By Person_ID */
Open persons_cursor
Fetch Next From persons_cursor Into @PersonID
While @@Fetch_STATUS = 0
BEGIN
   /*处理指定人员的假写卡记录*/
    Execute XF_pd_ConsumeMatchingPerson @PersonID
    Fetch Next From persons_cursor Into @PersonID
End

Close persons_cursor
DealLocate persons_cursor
GO

--处理假写卡内套过程
IF OBJECT_ID('XF_pd_ConsumeMatchingPerson') IS NOT NULL DROP PROCEDURE XF_pd_ConsumeMatchingPerson
GO

/*********************************************/
/* 类型：业务计算                            */
/* 模块来源：核心平台                        */
/* 过程名称：处理假写卡内套过程              */
/*                                           */
/*********************************************/
CREATE PROCEDURE dbo.XF_pd_ConSumeMatchingPerson
(
@PersonID INT
)
AS
Declare @ConSumeTimeBgn DateTime,/*消费开始日期*/
             @ConSumeTimeEnd DateTime,/*消费结事日期*/
             @CardBalance Money,/*主帐户余额*/
             @ICBalance Money,/*卡余额存*/
             @DBBalance Money,/*库余额*/
             @BeForeBalance Money,/*消费金额*/
             @IDKey INT,/*关键字段*/ 
             @Result Tinyint,
             @OtherFund Money,/*发放金额*/
             @ConSumeFund Money/*消费金额*/

/*1.取得卡余额*/
Select @ICBalance=Card_Balance 
From (
    Select Top 1 Card_Balance
    From (
        Select Person_ID,Max(ConSume_Time)[ConSume_Time] From XF_ConSume
         Where Person_ID=@PersonID Group BY Person_ID) B
    Left Join (Select Card_Balance,ConSume_Time From XF_ConSume Where Person_ID=@PersonID) A On B.ConSume_Time=A.ConSume_Time
) C

/*2.取得库余额*/
Select @DBBalance=Card_Balance
     From St_Card Where Person_ID=@PersonID

/*或者该人员已经被注销*/
IF (@ICBalance = @DBBalance) OR (@DBBalance IS NULL) 
Begin
    Select @ConSumeTimeBgn=Max(ConSume_Time) From XF_ConSume
     Where Person_ID=@PersonID
    Update XF_ConSumeFake Set Data_Type=82,Matching_Time=GetDate() Where Person_ID=@PersonID AND Consume_Time<=@ConSumeTimeBgn AND Data_Type=8 --更新数据标识为无效
    Return --如果卡库平衡就不处理
End

/*3.处理假写卡记录*/
Declare conSumes_Cursor Cursor Local Forward_only Static Read_only for 
    Select ConSume_Time,ConSume_Fund,Card_Balance,ID_Key From XF_ConSumeFake
    Where Person_ID=@PersonID AND Data_Type=8
    Order By ConSume_Time Desc
Open conSumes_Cursor
Fetch Next From conSumes_Cursor Into @ConSumeTimeBgn,@ConSumeFund,@CardBalance,@IDKey
While @@FETCH_STATUS = 0
Begin
    /*如果卡库平衡，就不处理相应操作*/
    IF (@ICBalance = @DBBalance)
    Begin
        Update XF_ConSumeFake Set Data_Type=82,Matching_Time=GetDate() Where Person_ID=@PersonID AND Data_Type=8 --更新数据标识为无效
        Break
    End

   /*获取消费金额*/
    Select Top 1 @BeForeBalance=ConSume_Fund+Card_Balance,@ConSumeTimeEnd=ConSume_Time From XF_ConSume
         Where Person_ID=@PersonID AND ConSume_Time>@ConSumeTimeBgn Order By ConSume_Time
 
     /*初始化为不处理*/
    Set @Result = 83
    /*是否找到下一条消费记录*/
    IF (@BeForeBalance IS NOT NULL) 
    Begin
        /*假写卡是否可以匹配得上*/
        IF (@BeForeBalance <> @CardBalance) 
        Begin
          /*充值金额*/
            Select @OtherFund=IsNull(Sum(Supply_Fund),0) From ST_SupplyFund
                Where Person_ID=@PersonID AND (Supply_Date>=@ConSumeTimeBgn AND Supply_Date<=@ConSumeTimeEnd)
         /*取款金额*/
            Select @OtherFund=@OtherFund-IsNull(Sum(Take_Fund),0) From ST_TakeFund
                Where Person_ID=@PersonID AND (Take_Date>=@ConSumeTimeBgn AND Take_Date<=@ConSumeTimeEnd)
         /*转帐金额*/
            Select @OtherFund=@OtherFund-IsNull(Sum(Transfer_Fund),0) From ST_TransferAccount
                Where Person_ID=@PersonID AND (Transfer_Date>=@ConSumeTimeBgn AND Transfer_Date<=@ConSumeTimeEnd)
           /*出纳处理后的假写卡是否可以匹配得上*/
            If (@BeForeBalance = @CardBalance+@OtherFund) 
             /*假定卡有效*/
                Set @Result = 81
            Else If (@BeForeBalance > @CardBalance) 
            /*假定卡无效*/
                Set @Result = 82
        End
        Else
        -- JenanWang 2008.1.8 修正
        Begin
            Select Top 1 @BeForeBalance=Card_Balance From XF_ConSume
                Where Person_ID=@PersonID AND ConSume_Time<@ConSumeTimeBgn Order By ConSume_Time Desc
           /*假写卡是否为重复记录*/
            If (@BeForeBalance <> @CardBalance) 
                /*假写有效*/
                Set @Result = 81 
            Else
                /*假定卡无效*/
                Set @Result = 82 
        End
        /*假写卡记录需要处理*/
        IF (@Result < 83) 
        Begin
            /*更新数据标识*/
            Update XF_ConSumeFake Set Data_Type=@Result,Matching_Time=GetDate() Where ID_Key=@IDKey 
            /*假写卡有效 */
            IF (@Result = 81) 
            Begin
               /*插入消费记录*/
                Insert Into XF_ConSume(Person_ID,System_No,ConSume_Mode,ConSume_Time,BeFore_Fund,ConSume_Fund,ConSume_Copy,
                    ConSume_TimeLen,ConSume_Capacity,Repast_No,Ware_ID,Moc_No,COM_No,Card_Balance,
                        DB_Balance,Card_SmallAccount,Data_Type,Download_Time)
                Select Person_ID,System_No,ConSume_Mode,ConSume_Time,BeFore_Fund,ConSume_Fund,ConSume_Copy,
                    ConSume_TimeLen,ConSume_Capacity,Repast_No,Ware_ID,Moc_No,COM_No,Card_Balance,
                        DB_Balance,Card_SmallAccount,0,Download_Time
                From XF_ConSumeFake Where ID_Key=@IDKey
               /*更新卡号金额表中的余额*/
                Update ST_Card Set Card_Balance=Card_Balance-@ConSumeFund Where Person_ID=@PersonID 
                /*更新@DBBalance值*/
                Set @DBBalance=@DBBalance-@ConSumeFund 
            End
        End
    End
    Fetch Next From conSumes_Cursor Into @ConSumeTimeBgn,@ConSumeFund,@CardBalance,@IDKey
End
CLOSE conSumes_Cursor
DEALLOCATE conSumes_Cursor
GO

--个人综合报表  
IF OBJECT_ID('st_pd_PersonalRep') IS NOT NULL DROP PROCEDURE st_pd_PersonalRep
GO

/*******************************************/
/* 类型：报表                              */
/* 模块来源：核心平台                      */
/* 报表名称：个人综合报表                  */
/*                                         */
/*******************************************/
CREATE  PROCEDURE st_pd_PersonalRep
@DeptNo  VarChar(40),    --部门编号
@UserNo  VarChar(40),    --用户编号
@DateBgn DateTime,       --统计起始日期
@DateEnd DateTime,        --统计终止日期
@IsDel   Bit             --是否显示注销人员
AS
Declare @sBgnDate VarChar(16), @sEndDate VarChar(16)
Set @sBgnDate = '' +  Convert(Char(16), @DateBgn, 20)  + ''
Set @sEndDate = '' +  Convert(Char(16), @DateEnd, 20)  + ''

IF Object_ID('MyTemp..#temp1') Is NOT NULL
    Drop Table #temp1
Create Table #temp1 (Person_ID int)
--是否显示注销人员
IF @IsDel=0
Begin
    /*统计发生业务的人员*/
    Insert  Into  #temp1(Person_ID)
        Select C.Person_ID
        From ST_Card A
        Left Join ST_Person C ON C.Person_ID=A.Person_ID
        Left Join ST_Department B ON B.Dept_ID=C.Dept_ID
        Where B.Dept_No LIKE @DeptNo+'%' AND B.Dept_ID IN (Select Dept_ID From ST_DeptRight Where Check_State=0 AND User_No=@UserNo)
        Order By B.Dept_No,C.Person_No
    /*查询相关信息*/
    Select C.Person_No,C.Person_Name,B.Dept_No,B.Dept_Name,Card_Balance,C.Card_No,
        IsNull(Supply_Fund,0) as [Supply_Fund],IsNull(ConSume_Fund,0) as [ConSume_Fund],
        IsNull(D.Subsidy_Fund,0) as [Subsidy_Fund],IsNull(Take_Fund,0) as [Take_Fund],
        IsNull(Transfer_Fund,0) as [Transfer_Fund],
        IsNull(Supply_Fund,0)+IsNull(D.Subsidy_Fund,0)-(IsNull(ConSume_Fund,0)+IsNull(Take_Fund,0)+IsNull(Transfer_Fund,0)+Card_Balance)[Mny_Balance]
      From ST_Card A
      Left Join ST_Person C ON C.Person_ID=A.Person_ID
      Left Join ST_Department B ON B.Dept_ID=C.Dept_ID
      Left Join (
      /*统计发生金额*/
       Select Person_ID,Sum(Supply_Fund) Supply_Fund,Sum(ConSume_Fund) ConSume_Fund,Sum(Subsidy_Fund) Subsidy_Fund,Sum(Take_Fund) Take_Fund,Sum(Transfer_Fund) Transfer_Fund
       From
       (
      /*1.累计存款金额(当前的数据)*/                                                                                   
          Select Person_ID,Sum(Supply_Fund) as Supply_Fund/*存款金额*/,0 as ConSume_Fund/*消费金额*/ ,0 as Subsidy_Fund  /*补贴金额*/,0 as Take_Fund/*取款金额*/  ,0 as Transfer_Fund /*转帐金额*/ 
          From ST_SupplyFund
          Where Convert(Char(16), Supply_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Supply_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1)
          Group By Person_ID
          Union All
      /*累计存款金额(历史的数据)*/
          Select Person_ID,Sum(Supply_Fund) as Supply_Fund,0,0,0,0
          From ST_SupplyFundHistory
          Where Convert(Char(16), Supply_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Supply_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1) 
              Group By Person_ID
          Union All
      /*2.累计消费金额(当前的数据)*/
          Select Person_ID,0,Sum(ConSume_Fund)[ConSume_Fund],0,0,0
          From XF_ConSume
          Where Convert(Char(16), ConSume_Time, 20)  >=  @sBgnDate and
              Convert(Char(16), ConSume_Time, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1) 
          Group By Person_ID
          Union All
      /*累计消费金额(历史的数据)*/
          Select Person_ID,0,Sum(ConSume_Fund)[ConSume_Fund],0,0,0
          From XF_ConSumeHistory
          Where Convert(Char(16), ConSume_Time, 20)  >=  @sBgnDate and
              Convert(Char(16), ConSume_Time, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1)
              Group By Person_ID
          Union All
      /*3.累计补贴金额(当前的数据)*/
          Select Person_ID,0,0,Sum(Subsidy_Fund)[Subsidy_Fund],0,0
          From ST_Subsidy
          Where Convert(Char(16), Subsidy_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1) 
              Group By Person_ID
          Union All
      /*累计补贴金额(历史的数据)*/
          Select Person_ID,0,0,Sum(Subsidy_Fund)[Subsidy_Fund],0,0
          From ST_SubsidyHistory
          Where Convert(Char(16), Subsidy_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1)
              Group By Person_ID
          Union All
      /*4.累计取款金额(当前的数据)*/
          Select Person_ID,0,0,0,Sum(Take_Fund)[Take_Fund],0
          From ST_TakeFund
          Where Convert(Char(16), Take_Date, 20)  >=  @sBgnDate and
             Convert(Char(16), Take_Date, 20) <=  @sEndDate
             and Person_ID in (Select Distinct Person_ID From #temp1) 
             Group By Person_ID
          Union All
      /*累计取款金额(历史的数据)*/
          Select Person_ID,0,0,0,Sum(Take_Fund)[Take_Fund],0
          From ST_TakeFundHistory
          Where Convert(Char(16), Take_Date, 20)  >=  @sBgnDate and
             Convert(Char(16), Take_Date, 20) <=  @sEndDate
             and Person_ID in (Select Distinct Person_ID From #temp1)
             Group By Person_ID
          Union All
      /*5.累计转帐金额(当前的数据)*/
          Select Person_ID,0,0,0,0,Sum(Transfer_Fund)[Transfer_Fund]
          From ST_TransferAccount
          Where Convert(Char(16), Transfer_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1) 
              Group By Person_ID
          Union All
      /*累计转帐金额(历史的数据)*/
          Select Person_ID,0,0,0,0,Sum(Transfer_Fund)[Transfer_Fund]
          From ST_TransferAccountHistory
          Where Convert(Char(16), Transfer_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1)
              Group By Person_ID
    ) ss  Group By Person_ID) D  on A.Person_ID=D.Person_ID
    Where B.Dept_No LIKE @DeptNo+'%' AND B.Dept_ID IN (Select Dept_ID From ST_DeptRight Where Check_State=0 AND User_No=@UserNo)
        Order By B.Dept_No,C.Person_No
END
ELSE
Begin
    /*统计发生业务的人员*/
    Insert  Into  #temp1(Person_ID)
        Select C.Person_ID
        From ST_Person C 
        Left Join ST_Card A ON C.Person_ID=A.Person_ID
        Left Join ST_Department B ON B.Dept_ID=C.Dept_ID
        Where B.Dept_No LIKE @DeptNo+'%' AND B.Dept_ID IN (Select Dept_ID From ST_DeptRight Where Check_State=0 AND User_No=@UserNo)
    /*查询相关信息*/
    Select A.Person_No,A.Person_Name,B.Dept_No,B.Dept_Name,IsNull(Card_Balance,0)[Card_Balance],Card_No,A.IS_DEL,
        IsNull(Supply_Fund,0)[Supply_Fund],IsNull(ConSume_Fund,0)[ConSume_Fund],
        IsNull(I.Subsidy_Fund,0)[Subsidy_Fund],IsNull(Take_Fund,0)[Take_Fund],
        IsNull(Transfer_Fund,0)[Transfer_Fund],
        IsNull(Supply_Fund,0)+IsNull(I.Subsidy_Fund,0)-(IsNull(ConSume_Fund,0)+IsNull(Take_Fund,0)+IsNull(Transfer_Fund,0)+Card_Balance)[Mny_Balance]
      From ST_Person A
      Left Join ST_Card C ON  A.Person_ID=C.Person_ID
      Left Join ST_Department B ON A.Dept_ID=B.Dept_ID
      Left Join (
      /*统计发生金额*/
       Select Person_ID,Sum(Supply_Fund) Supply_Fund,Sum(ConSume_Fund) ConSume_Fund,Sum(Subsidy_Fund) Subsidy_Fund,Sum(Take_Fund) Take_Fund,Sum(Transfer_Fund) Transfer_Fund
       From
       (
      /*1.累计存款金额(当前的数据)*/
          Select Person_ID,Sum(Supply_Fund)[Supply_Fund],0 as ConSume_Fund,0 as Subsidy_Fund,0 as Take_Fund,0 as Transfer_Fund
          From ST_SupplyFund
          Where Convert(Char(16), Supply_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Supply_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1)
          Group By Person_ID
          Union All
      /*累计存款金额(历史的数据)*/
          Select Person_ID,Sum(Supply_Fund)[Supply_Fund],0,0,0,0
          From ST_SupplyFundHistory
          Where Convert(Char(16), Supply_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Supply_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1)
          Group By Person_ID
          Union All
      /*2.累计消费金额(当前的数据)*/
          Select  Person_ID,0,Sum(ConSume_Fund)[ConSume_Fund],0,0,0
          From XF_ConSume
          Where Convert(Char(16), ConSume_Time, 20)  >=  @sBgnDate and
              Convert(Char(16), ConSume_Time, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1) 
          Group By Person_ID
          Union All
      /*累计消费金额(历史的数据)*/
          Select Person_ID,0,Sum(ConSume_Fund)[ConSume_Fund],0,0,0
          From XF_ConSumeHistory
          Where Convert(Char(16), ConSume_Time, 20)  >=  @sBgnDate and
             Convert(Char(16), ConSume_Time, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1)
          Group By Person_ID
          Union All
      /*3.累计补贴金额(当前的数据)*/
          Select Person_ID,0,0,Sum(Subsidy_Fund)[Subsidy_Fund],0,0
          From ST_Subsidy
          Where Convert(Char(16), Subsidy_Date, 20)  >=  @sBgnDate and
             Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1)
          Group By Person_ID
          Union All
      /*累计补贴金额(历史的数据)*/
          Select Person_ID,0,0,Sum(Subsidy_Fund)[Subsidy_Fund],0,0
          From ST_SubsidyHistory
          Where Convert(Char(16), Subsidy_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1) 
          Group By Person_ID
          Union All
      /*4.累计取款金额(当前的数据)*/
          Select Person_ID,0,0,0,Sum(Take_Fund)[Take_Fund],0
          From ST_TakeFund
          Where Convert(Char(16), Take_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Take_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1) 
          Group By Person_ID
          Union All
      /*累计取款金额(历史的数据)*/
          Select Person_ID,0,0,0,Sum(Take_Fund)[Take_Fund],0
          From ST_TakeFundHistory
          Where Convert(Char(16), Take_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Take_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1) 
          Group By Person_ID
          Union All
      /*5.累计转帐金额(当前的数据)*/
          Select Person_ID,0,0,0,0,Sum(Transfer_Fund)[Transfer_Fund]
          From ST_TransferAccount
          Where Convert(Char(16), Transfer_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1)
          Group By Person_ID
          Union All
      /*累计转帐金额(历史的数据)*/
          Select Person_ID,0,0,0,0,Sum(Transfer_Fund)[Transfer_Fund]
          From ST_TransferAccountHistory
          Where Convert(Char(16), Transfer_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
              and Person_ID in (Select Distinct Person_ID From #temp1)
          Group By Person_ID
    ) ss  Group By Person_ID) I  On A.Person_ID=I.Person_ID
    Where B.Dept_No LIKE @DeptNo+'%' AND B.Dept_ID IN (Select Dept_ID From ST_DeptRight Where Check_State=0 AND User_No=@UserNo)
    Order By B.Dept_No,A.Person_No
End
Drop Table #temp1
GO

--系统平衡表 
IF OBJECT_ID('st_pd_SysBalanceRep') IS NOT NULL DROP PROCEDURE st_pd_SysBalanceRep
GO

/*******************************************/
/* 类型：报表                              */
/* 模块来源：核心平台                      */
/* 报表名称：系统平衡表                    */
/*                                         */
/*******************************************/
CREATE PROCEDURE dbo.st_pd_SysBalanceRep
AS
    Declare @CardCharge Money, @CardForegift Money  --开户卡费用、开户卡押金
    Declare @SupplyFund Money                      --总存款金额
    Declare @SubsidyFund Money                     --总补贴金额
    Declare @AlterFee Money                        --总补卡费用
    Declare @TakeFund Money                        --总取款金额
    Declare @BackFund Money, @BackForegift Money   --销卡退金额、销卡退押金
    Declare @TransferFund Money                    --小金额转账总金额
    Declare @CardBalance Money                     --总账户余额
    Declare @Fund Money                            --总消费金额
    Declare @Money Money                           --平衡差额

/*1.取得开户卡费用、开户卡押金、总账户余额*/
Select @CardCharge=Round(IsNull(Sum(Card_Charge),0), 2), @CardForegift=Round(IsNull(Sum(Card_Foregift),0), 2),
    @CardBalance=Round(IsNull(Sum(Card_Balance),0), 2)
From dbo.ST_Card

Select @CardCharge=@CardCharge+Round(IsNull(Sum(Card_Charge), 0), 2),
    @CardForegift=@CardForegift+Round(IsNull(Sum(Back_Foregift),0), 2)
From dbo.ST_DeleteCard

Select @CardCharge=@CardCharge+Round(IsNull(Sum(Card_Charge), 0), 2),
    @CardForegift=@CardForegift+Round(IsNull(Sum(Back_Foregift),0), 2)
From dbo.ST_PersonAccount

/*2.总存款金额*/
Select @SupplyFund=Round(IsNull(Sum(Supply_Fund),0), 2) From dbo.ST_SupplyFund
Select @SupplyFund=@SupplyFund+Round(IsNull(Sum(Supply_Fund),0), 2) From dbo.ST_PersonAccount

/*3.总补贴金额*/
Select @SubsidyFund=Round(IsNull(Sum(Subsidy_Fund),0), 2) From dbo.ST_Subsidy
Select @SubsidyFund=@SubsidyFund+Round(IsNull(Sum(Subsidy_Fund),0), 2) From dbo.ST_PersonAccount

/*4.总补卡费用*/
Select @AlterFee=Round(IsNull(Sum(Alter_Fee),0), 2) From dbo.ST_AlterCard
Select @AlterFee=@AlterFee+Round(IsNull(Sum(AlterCard_Fund),0), 2) From dbo.ST_PersonAccount

/*5.总取款金额*/
Select @TakeFund=Round(IsNull(Sum(Take_Fund),0), 2) From dbo.ST_TakeFund
Select @TakeFund=@TakeFund+Round(IsNull(Sum(Take_Fund),0), 2) From dbo.ST_PersonAccount

/*6.销卡退金额、销卡退押金*/
Select @BackFund=Round(IsNull(Sum(Back_Fund),0), 2), @BackForegift=Round(IsNull(Sum(Back_Foregift),0), 2)
From dbo.ST_DeleteCard
Select @BackFund=@BackFund+Round(IsNull(Sum(Back_Fund),0), 2), @BackForegift=@BackForegift+Round(IsNull(Sum(Back_Foregift),0), 2)
From dbo.ST_PersonAccount

/*7.小金额转账总金额*/
Select @TransferFund=Round(IsNull(Sum(Transfer_Fund),0), 2) From dbo.ST_TransferAccount
Select @TransferFund=@TransferFund+Round(IsNull(Sum(Transfer_Fund),0), 2) From dbo.ST_PersonAccount

/*8.总消费金额*/
Select @Fund=Round(IsNull(Sum(ConSume_Fund),0), 2) From dbo.XF_ConSume
Select @Fund=@Fund+Round(IsNull(Sum(EM_ConSumeFund),0), 2) From dbo.ST_PersonAccount
Select @Fund=@Fund+Round(IsNull(Sum(XF_ConSumeFund),0), 2) From dbo.ST_PersonAccount

/*9.平衡差额*/
SET @Money=Round((@SupplyFund+@SubsidyFund)-(@Fund+@CardBalance+@TakeFund+@BackFund+@TransferFund), 2)
Select GetDate()  as [Balance_Time], @CardCharge as [Card_Charge], @CardForegift as [Card_Foregift], @SupplyFund as [Supply_AllFund], @SubsidyFund as [Subsidy_AllFund],
@AlterFee as [Alter_Fee], @TakeFund as [Take_AllFund], @BackFund as [Back_Fund], @BackForegift as [Back_Foregift], @TransferFund as [Transfer_AllFund],
@Money as [System_Balance], @CardBalance as [Card_AllBalance],@Fund as [ConSume_AllFund],@CardForegift-@BackForegift as [Remain_AllForegift]
GO

--个人综合报表
IF OBJECT_ID('st_pd_PersonalRepAll') IS NOT NULL DROP PROCEDURE st_pd_PersonalRepAll
GO

/*******************************************/
/* 类型：报表                              */
/* 模块来源：核心平台                      */
/* 报表名称：个人综合报表                  */
/*                                         */
/*******************************************/
CREATE  PROCEDURE st_pd_PersonalRepAll
@DeptNo  VarChar(40),    --部门编号
@UserNo  VarChar(40),    --用户编号
@DateBgn DateTime,       --统计起始日期
@DateEnd DateTime,        --统计终止日期
@IsDel   Bit             --是否显示注销人员
AS
Declare @sBgnDate VarChar(16), @sEndDate VarChar(16)
Set @sBgnDate = '' +  Convert(Char(16), @DateBgn, 20)  + ''
Set @sEndDate = '' +  Convert(Char(16), @DateEnd, 20)  + ''
--是否显示注销人员
IF @IsDel=0
Begin
    /*查询相关信息*/
    Select C.Person_No,C.Person_Name,B.Dept_No,B.Dept_Name,Card_Balance,C.Card_No,
        IsNull(Supply_Fund,0) as [Supply_Fund],IsNull(ConSume_Fund,0) as [ConSume_Fund],
        IsNull(D.Subsidy_Fund,0) as [Subsidy_Fund],IsNull(Take_Fund,0) as [Take_Fund],
        IsNull(Transfer_Fund,0) as [Transfer_Fund],
        IsNull(Supply_Fund,0)+IsNull(D.Subsidy_Fund,0)-(IsNull(ConSume_Fund,0)+IsNull(Take_Fund,0)+IsNull(Transfer_Fund,0)+Card_Balance)[Mny_Balance]
      From ST_Card A
      Left Join ST_Person C ON C.Person_ID=A.Person_ID
      Left Join ST_Department B ON B.Dept_ID=C.Dept_ID
      Left Join (
      /*统计发生金额*/
       Select Person_ID,Sum(Supply_Fund) Supply_Fund,Sum(ConSume_Fund) ConSume_Fund,Sum(Subsidy_Fund) Subsidy_Fund,Sum(Take_Fund) Take_Fund,Sum(Transfer_Fund) Transfer_Fund
       From
       (
      /*1.累计存款金额(当前的数据)*/                                                                                   
          Select Person_ID,Sum(Supply_Fund) as Supply_Fund/*存款金额*/,0 as ConSume_Fund/*消费金额*/ ,0 as Subsidy_Fund  /*补贴金额*/,0 as Take_Fund/*取款金额*/  ,0 as Transfer_Fund /*转帐金额*/ 
          From ST_SupplyFund
          Where Convert(Char(16), Supply_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Supply_Date, 20) <=  @sEndDate
          Group By Person_ID
          Union All
      /*累计存款金额(历史的数据)*/
          Select Person_ID,Sum(Supply_Fund) as Supply_Fund,0,0,0,0
          From ST_SupplyFundHistory
          Where Convert(Char(16), Supply_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Supply_Date, 20) <=  @sEndDate
              Group By Person_ID
          Union All
      /*2.累计消费金额(当前的数据)*/
          Select Person_ID,0,Sum(ConSume_Fund)[ConSume_Fund],0,0,0
          From XF_ConSume
          Where Convert(Char(16), ConSume_Time, 20)  >=  @sBgnDate and
              Convert(Char(16), ConSume_Time, 20) <=  @sEndDate
          Group By Person_ID
          Union All
      /*累计消费金额(历史的数据)*/
          Select Person_ID,0,Sum(ConSume_Fund)[ConSume_Fund],0,0,0
          From XF_ConSumeHistory
          Where Convert(Char(16), ConSume_Time, 20)  >=  @sBgnDate and
              Convert(Char(16), ConSume_Time, 20) <=  @sEndDate
              Group By Person_ID
          Union All
      /*3.累计补贴金额(当前的数据)*/
          Select Person_ID,0,0,Sum(Subsidy_Fund)[Subsidy_Fund],0,0
          From ST_Subsidy
          Where Convert(Char(16), Subsidy_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate
              Group By Person_ID
          Union All
      /*累计补贴金额(历史的数据)*/
          Select Person_ID,0,0,Sum(Subsidy_Fund)[Subsidy_Fund],0,0
          From ST_SubsidyHistory
          Where Convert(Char(16), Subsidy_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate
              Group By Person_ID
          Union All
      /*4.累计取款金额(当前的数据)*/
          Select Person_ID,0,0,0,Sum(Take_Fund)[Take_Fund],0
          From ST_TakeFund
          Where Convert(Char(16), Take_Date, 20)  >=  @sBgnDate and
             Convert(Char(16), Take_Date, 20) <=  @sEndDate
             Group By Person_ID
          Union All
      /*累计取款金额(历史的数据)*/
          Select Person_ID,0,0,0,Sum(Take_Fund)[Take_Fund],0
          From ST_TakeFundHistory
          Where Convert(Char(16), Take_Date, 20)  >=  @sBgnDate and
             Convert(Char(16), Take_Date, 20) <=  @sEndDate
             Group By Person_ID
          Union All
      /*5.累计转帐金额(当前的数据)*/
          Select Person_ID,0,0,0,0,Sum(Transfer_Fund)[Transfer_Fund]
          From ST_TransferAccount
          Where Convert(Char(16), Transfer_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
              Group By Person_ID
          Union All
      /*累计转帐金额(历史的数据)*/
          Select Person_ID,0,0,0,0,Sum(Transfer_Fund)[Transfer_Fund]
          From ST_TransferAccountHistory
          Where Convert(Char(16), Transfer_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
              Group By Person_ID
    ) ss  Group By Person_ID) D  on A.Person_ID=D.Person_ID
    /*过虑数据*/
    Where B.Dept_No LIKE @DeptNo+'%' AND B.Dept_ID IN (Select Dept_ID From ST_DeptRight Where Check_State=0 AND User_No=@UserNo)
        Order By B.Dept_No,C.Person_No  
END
ELSE
Begin
    /*查询相关信息*/
    Select A.Person_No,A.Person_Name,B.Dept_No,B.Dept_Name,IsNull(Card_Balance,0)[Card_Balance],Card_No,A.IS_DEL,
        IsNull(Supply_Fund,0)[Supply_Fund],IsNull(ConSume_Fund,0)[ConSume_Fund],
        IsNull(I.Subsidy_Fund,0)[Subsidy_Fund],IsNull(Take_Fund,0)[Take_Fund],
        IsNull(Transfer_Fund,0)[Transfer_Fund],
        IsNull(Supply_Fund,0)+IsNull(I.Subsidy_Fund,0)-(IsNull(ConSume_Fund,0)+IsNull(Take_Fund,0)+IsNull(Transfer_Fund,0)+Card_Balance)[Mny_Balance]
      From ST_Person A
      Left Join ST_Card C ON  A.Person_ID=C.Person_ID
      Left Join ST_Department B ON A.Dept_ID=B.Dept_ID
      Left Join (
      /*统计发生金额*/
       Select Person_ID,Sum(Supply_Fund) Supply_Fund,Sum(ConSume_Fund) ConSume_Fund,Sum(Subsidy_Fund) Subsidy_Fund,Sum(Take_Fund) Take_Fund,Sum(Transfer_Fund) Transfer_Fund
       From
       (
      /*1.累计存款金额(当前的数据)*/
          Select Person_ID,Sum(Supply_Fund)[Supply_Fund],0 as ConSume_Fund,0 as Subsidy_Fund,0 as Take_Fund,0 as Transfer_Fund
          From ST_SupplyFund
          Where Convert(Char(16), Supply_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Supply_Date, 20) <=  @sEndDate
          Group By Person_ID
          Union All
      /*累计存款金额(历史的数据)*/
          Select Person_ID,Sum(Supply_Fund)[Supply_Fund],0,0,0,0
          From ST_SupplyFundHistory
          Where Convert(Char(16), Supply_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Supply_Date, 20) <=  @sEndDate
          Group By Person_ID
          Union All
      /*2.累计消费金额(当前的数据)*/
          Select  Person_ID,0,Sum(ConSume_Fund)[ConSume_Fund],0,0,0
          From XF_ConSume
          Where Convert(Char(16), ConSume_Time, 20)  >=  @sBgnDate and
              Convert(Char(16), ConSume_Time, 20) <=  @sEndDate
          Group By Person_ID
          Union All
      /*累计消费金额(历史的数据)*/
          Select Person_ID,0,Sum(ConSume_Fund)[ConSume_Fund],0,0,0
          From XF_ConSumeHistory
          Where Convert(Char(16), ConSume_Time, 20)  >=  @sBgnDate and
             Convert(Char(16), ConSume_Time, 20) <=  @sEndDate
          Group By Person_ID
          Union All
      /*3.累计补贴金额(当前的数据)*/
          Select Person_ID,0,0,Sum(Subsidy_Fund)[Subsidy_Fund],0,0
          From ST_Subsidy
          Where Convert(Char(16), Subsidy_Date, 20)  >=  @sBgnDate and
             Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate
          Group By Person_ID
          Union All
      /*累计补贴金额(历史的数据)*/
          Select Person_ID,0,0,Sum(Subsidy_Fund)[Subsidy_Fund],0,0
          From ST_SubsidyHistory
          Where Convert(Char(16), Subsidy_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Subsidy_Date, 20) <=  @sEndDate
          Group By Person_ID
          Union All
      /*4.累计取款金额(当前的数据)*/
          Select Person_ID,0,0,0,Sum(Take_Fund)[Take_Fund],0
          From ST_TakeFund
          Where Convert(Char(16), Take_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Take_Date, 20) <=  @sEndDate
          Group By Person_ID
          Union All
      /*累计取款金额(历史的数据)*/
          Select Person_ID,0,0,0,Sum(Take_Fund)[Take_Fund],0
          From ST_TakeFundHistory
          Where Convert(Char(16), Take_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Take_Date, 20) <=  @sEndDate
          Group By Person_ID
          Union All
      /*5.累计转帐金额(当前的数据)*/
          Select Person_ID,0,0,0,0,Sum(Transfer_Fund)[Transfer_Fund]
          From ST_TransferAccount
          Where Convert(Char(16), Transfer_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
          Group By Person_ID
          Union All
      /*累计转帐金额(历史的数据)*/
          Select Person_ID,0,0,0,0,Sum(Transfer_Fund)[Transfer_Fund]
          From ST_TransferAccountHistory
          Where Convert(Char(16), Transfer_Date, 20)  >=  @sBgnDate and
              Convert(Char(16), Transfer_Date, 20) <=  @sEndDate
          Group By Person_ID
    ) ss  Group By Person_ID) I  On A.Person_ID=I.Person_ID
    Where B.Dept_No LIKE @DeptNo+'%' AND B.Dept_ID IN (Select Dept_ID From ST_DeptRight Where Check_State=0 AND User_No=@UserNo)
    Order By B.Dept_No,A.Person_No
End
GO


--卡类押金费用报表
IF OBJECT_ID('st_pd_CardForegiftCharge') IS NOT NULL DROP PROCEDURE st_pd_CardForegiftCharge
GO

/*******************************************/
/* 类型：报表                              */
/* 模块来源：核心平台                      */
/* 报表名称：卡类押金费用报表              */
/*                                         */
/*******************************************/

Create PROCEDURE st_pd_CardForegiftCharge
@UserNo   VARCHAR(8000),
@sDateBgn NVARCHAR(20),
@SDateEnd NVARCHAR(20)
AS
DECLARE @SUserNo VARCHAR(20)
DECLARE @Pos INT
SET @SUserNo = ''
SET @Pos = 0
IF @UserNo = 'AllUsers'
BEGIN

SELECT C.Type_No,C.Type_Name,Sum(A.Card_Foregift) Card_Foregift,Sum(A.Card_Charge) Card_Charge,SUM(A.Alter_Fee) Alter_Fee from 
(select Person_ID,Card_Foregift,Card_Charge,0[Alter_Fee] from ST_Card
 WHERE Card_NewDate >= @sDateBgn AND Card_NewDate <= @sDateEnd
 union all 
 select Person_ID,0,0,Alter_Fee from ST_AlterCard
 WHERE Alter_Date >= @sDateBgn AND Alter_Date <= @sDateEnd 
) A LEFT JOIN ST_Card B on A.Person_ID=B.Person_ID Left join ST_CardType C On B.Type_No = C.Type_No
GROUP BY C.Type_NO,C.Type_Name Order By C.Type_No

END
ELSE
BEGIN
--0.取得选中的出纳员
IF OBJECT_ID('tempdb..#SelectPerson') IS NOT NULL DROP TABLE #SelectPerson
SELECT User_No INTO #SelectPerson FROM ST_UserInfo WHERE 1=0
WHILE (@UserNo <> '' )
BEGIN
SET @Pos = CHARINDEX(',', @UserNo)
IF @Pos = 0
BEGIN
SET @SUserNo=@UserNo
SET @UserNo=''
END
ELSE
BEGIN
SET @SUserNo=SUBSTRING(@UserNo, 1, @Pos - 1)
SET @UserNo=SUBSTRING(@UserNo,@Pos + 1, 8000)
END
INSERT INTO #SelectPerson(User_No) VALUES(@SUserNo)
END
--1.取数据
SELECT C.Type_No,C.Type_Name,Sum(A.Card_Foregift) Card_Foregift,Sum(A.Card_Charge) Card_Charge,SUM(A.Alter_Fee) Alter_Fee  from 
(select Person_ID,Card_Foregift,Card_Charge,0[Alter_Fee] from ST_Card
 WHERE Card_NewDate >= @sDateBgn AND Card_NewDate <= @sDateEnd and User_No in (SELECT User_No FROM #SelectPerson)
 union all 
 select Person_ID,0,0,Alter_Fee from ST_AlterCard
 WHERE Alter_Date >= @sDateBgn AND Alter_Date <= @sDateEnd and User_No in (SELECT User_No FROM #SelectPerson)
) A LEFT JOIN ST_Card B on A.Person_ID=B.Person_ID Left join ST_CardType C On B.Type_No = C.Type_No
GROUP BY C.Type_NO,C.Type_Name Order By C.Type_No

END


GO