-- =============================================
-- 视图：dbo.V_XFPsnConsume（消费表和结算后的消费表，以及人员部门信息）
-- =============================================
IF OBJECT_ID('dbo.V_XFPsnConsume') IS NOT NULL DROP VIEW dbo.V_XFPsnConsume
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create View dbo.V_XFPsnConsume AS 
	    SELECT A.Person_ID, A.System_No, A.Consume_Mode, A.Consume_Time, 
            A.Consume_Fund, A.Consume_Copy, A.Consume_TimeLen, A.Consume_Capacity, 
            A.Repast_No, A.Ware_ID, A.MOC_No, A.COM_No, A.Card_Balance, 
            A.DB_Balance, A.Card_SmallAccount, A.Data_Type, A.Remark, B.Person_NO, 
            B.Person_Name, B.Dept_ID, B.Dept_NO, B.Dept_Name, D .MOC_Name
      FROM XF_Consume A LEFT JOIN
            V_STPerson B ON B.Person_ID = A.Person_ID LEFT JOIN
            ST_MOC D ON A.MOC_NO = D .MOC_NO
      UNION ALL
      SELECT A.Person_ID, A.System_No, A.Consume_Mode, A.Consume_Time, 
            A.Consume_Fund, A.Consume_Copy, A.Consume_TimeLen, A.Consume_Capacity, 
            A.Repast_No, A.Ware_ID, A.MOC_No, A.COM_No, A.Card_Balance, 
            A.DB_Balance, A.Card_SmallAccount, A.Data_Type, A.Remark, B.Person_NO, 
            B.Person_Name, B.Dept_ID, B.Dept_NO, B.Dept_Name, D .MOC_Name
      FROM XF_Consumehistory A LEFT JOIN
            V_STPerson B ON B.Person_ID = A.Person_ID LEFT JOIN
            ST_MOC D ON A.MOC_NO = D .MOC_NO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE VIEW dbo.V_XFSectArea
AS
   SELECT * FROM 
     (SELECT Sect_No[Area_No],Sect_No,Sect_Name,1[IsSect],Sect_No[Term_No] FROM XF_Section
       UNION ALL SELECT Area_No,Sect_No+Area_No,Area_Name,0,Sect_No[Term_No]
        FROM XF_Area
     ) A
GO


-- =============================================
-- 取得指定场所编号的最大下属场所编号
-- =============================================
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'XF_pd_GetMaxAreaNo' AND type = 'P')
    DROP PROCEDURE dbo.XF_pd_GetMaxAreaNo
GO

CREATE PROCEDURE dbo.XF_pd_GetMaxAreaNo
	@AreaNo VARCHAR(30), --指定的场所编号
        @SysTemNO VARCHAR(10)--子系统名
AS
   DECLARE @MaxAreaNo VARCHAR(30),@AreaIndex INT

   IF @AreaNo<> ''
   BEGIN  
      SELECT @MaxAreaNo=ISNULL(MAX(Area_No),@AreaNo+'00') FROM XF_Area
      WHERE Area_No LIKE @AreaNo+'%' AND LEN(Area_No)=LEN(@AreaNo)+2
        AND SUBSTRING(Area_No,1,2)=@SysTemNo

      SET @AreaIndex=CAST(SUBSTRING(@MaxAreaNo,LEN(@AreaNo)+1,2) AS INT)+1
      IF @AreaIndex<99
      BEGIN
        IF @AreaIndex<10 
          SELECT @MaxAreaNo=@AreaNo+'0'+CAST(@AreaIndex AS VARCHAR)
        ELSE   
          SELECT @MaxAreaNo=@AreaNo+CAST(@AreaIndex AS VARCHAR)
			END
			ELSE
			 RAISERROR('Over Max Area No 6', 16, 1)
  END
  ELSE BEGIN
    SELECT @MaxAreaNo=ISNULL(MAX(Area_No),@AreaNo+'00') FROM XF_Area
    WHERE  LEN(Area_No)= 4 and SUBSTRING(Area_No,1,2)=@SysTemNo
 
   SET @AreaIndex=CAST(SUBSTRING(@MaxAreaNo,3,2) AS INT)+1
   IF @AreaIndex<99 
   BEGIN
      IF @AreaIndex<10 
         SELECT @MaxAreaNo=@SysTemNo+@AreaNo+'0'+CAST(@AreaIndex AS VARCHAR)
      ELSE   
         SELECT @MaxAreaNo=@SysTemNo+@AreaNo+CAST(@AreaIndex AS VARCHAR)
   END
   ELSE
     RAISERROR('Over Max Area No 6!', 16, 1)

   END  

  IF LEN(@MaxAreaNo)> 12 
    RAISERROR('Over Max Area No 6!', 16, 1)
  ELSE
    SELECT @MaxAreaNo
GO

-------------------------
-------------------------
IF OBJECT_ID('XF_pd_UploadConsumeCache') IS NOT NULL
   DROP PROCEDURE XF_pd_UploadConsumeCache
GO

CREATE PROCEDURE dbo.XF_pd_UploadConsumeCache
AS   
   DECLARE @MaxIDKey BIGINT
    SELECT @MaxIDKey=ISNULL(MAX(ID_Key), 0) FROM XF_ConsumeTemp
   --1.提交未提交的消费缓存数据
   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,Data_Type,Download_Time
    FROM XF_ConsumeTemp WITH (HOLDLOCK) WHERE Is_Commited=0 AND ID_Key<=@MaxIDKey 

   --2.更新提交标志
   UPDATE XF_ConsumeTemp SET Is_Commited=1 WHERE Is_Commited=0 AND ID_Key<=@MaxIDKey

   --3.删除纠错过的记录或假写卡记录
   DELETE FROM XF_ConsumeTemp WHERE Is_Commited=1 AND Data_Type<>0 AND Data_Type<>7 AND Data_Type<>2

   --4.删除已经不是当天的记录（未结算的时长记录不能被删除）
   DECLARE @Date VARCHAR(10)
       SET @Date=CONVERT(VARCHAR(10), GETDATE(),121)
   DELETE FROM XF_ConsumeTemp
    WHERE Is_Commited=1 AND Data_Type<>7 AND Consume_Time<@Date+' 00:00:00'
GO

---------------------
-- =============================================
--更新键值信息
-- =============================================

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

CREATE PROCEDURE dbo.XF_pd_UpdatekeyInfo
  @KeyEndTbl VARCHAR(30),--最后临时表
  @KeyType Integer--键值编号
AS
DECLARE  @SQL VARCHAR(8000);
DECLARE  @SQL_D VARCHAR(8000);
DECLARE  @I  Int,@FieldValue VARCHAR(100),@Field VARCHAR(20);

  SET @SQL=''
  SET @SQL=' DElETE FROM XF_KeyType Where Key_Type='+Cast(@KeyType as VARCHAR);
  EXEC(@SQL)

  SET @I=0 ;
  SET @SQL=''

  
  WHILE  @I< 10
  BEGIN
    SET @Field=CAST('K'+CAST(@i as VARCHAR)as VARCHAR)
    SET @FieldValue=CAST(CAST(@i as VARCHAR)+ ' AS Key_Value ' as VARCHAR)
    IF @SQL = ''
      SET @SQL ='SELECT Key_Type,Key_TypeName,'+@FieldValue+','+ @Field +' AS Key_Fund FROM '+@KeyEndTbl +' WHERE '+@Field+' IS NOT NULL'

    ELSE
    SET @SQL = @SQL + ' UNION ALL '+
              'SELECT Key_Type,Key_TypeName,'+@FieldValue+','+@Field+' AS Key_Fund FROM '+@KeyEndTbl +' WHERE '+@Field+' IS NOT NULL'   
   SET @I=@I+1;
 END

  SET @SQL='INSERT INTO dbo.XF_KeyType (Key_Type,Key_TypeName,Key_Value,Key_Fund)'+
           ' SELECT Key_Type,Key_TypeName,Key_Value,Key_Fund  FROM ('+ @SQL +')A Where Key_Type='+Cast(@KeyType as VARCHAR);
 
  EXEC(@SQL)
 
GO

--------------------------
IF OBJECT_ID('XF_pd_TransferAccount') IS NOT NULL
   DROP PROCEDURE XF_pd_TransferAccount
GO

CREATE PROCEDURE dbo.XF_pd_TransferAccount
   (@PersonID INT,         --人员标识号
    @TransferFund MONEY,   --转账金额
    @CardNo VARCHAR(40),   --操作人员卡号
    @Balance MONEY,        --转帐前的余额
    @MocNo VARCHAR(20),    --终端机编号
    @Date DATETIME         --转帐时间
   )
AS   
   --0.更新IC卡余额
   UPDATE ST_Card 
      SET Update_Time=@Date,IC_Balance=@Balance
    WHERE Person_ID=@PersonID AND Update_Time<@Date AND DATEDIFF(day,Update_Time,@Date)<200
   DECLARE @UserNo VARCHAR(40) --转帐操作员编号
   --1.取操作人员编号
   SELECT @UserNo=User_No FROM ST_UserInfo WHERE Card_No=@CardNo
   IF @UserNo IS NULL SET @UserNo=@CardNo
   --2.插入转帐记录
  INSERT ST_TransferAccount(Person_ID,Transfer_Date,Transfer_Fund,User_No,Card_Balance,Moc_No,Remark)
  SELECT @PersonID,@Date,@TransferFund,@UserNo,@Balance+@TransferFund,@MocNo,'终端转帐'
  --3.更新余额
  UPDATE ST_Card SET Card_Balance=Card_Balance-@TransferFund WHERE Person_ID=@PersonID
GO
---------------------------
IF OBJECT_ID('XF_pd_PrepareConsumeCache') IS NOT NULL
   DROP PROCEDURE XF_pd_PrepareConsumeCache
GO

CREATE PROCEDURE dbo.XF_pd_PrepareConsumeCache
AS   
   --Data_Type(0.硬件消费、1.硬件纠错、2.软件消费、3.软件纠错(负记录)、4.软件纠错(正记录)、7.未结算时长消费记录、8.假写卡记录)
   --1.清空消费缓存表
   DECLARE @UnCommitCount INT
   SELECT @UnCommitCount=COUNT(*) FROM XF_ConsumeTemp WHERE Is_Commited=0 --取未提交的消费记录数
   IF (@UnCommitCount = 0) --如果消费记录已经全部提交
      TRUNCATE TABLE XF_ConsumeTemp --清除数据表
   ELSE
      DELETE XF_ConsumeTemp WHERE Is_Commited=1

   --2.取当天的所有消费记录
   DECLARE @Date VARCHAR(10),@MinIDKey BIGINT
       SET @Date=CONVERT(VARCHAR(10), GETDATE(),121)

   SELECT @MinIDKey=ID_Key FROM XF_Consume WHERE Consume_Time>=@Date+' 00:00:00' AND Consume_Time<=@Date+' 23:59:59'
    ORDER BY ID_Key DESC

   IF @MinIDKey IS NULL SET @MinIDKey=0 

   INSERT INTO XF_ConsumeTemp(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,Is_Commited,Source_ID)
   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,Data_Type,1,B1.ID_KEY
     FROM (SELECT ID_KEY FROM XF_Consume WHERE Consume_Time>=@Date+' 00:00:00' AND Consume_Time<=@Date+' 23:59:59') A1 
     LEFT JOIN (SELECT * FROM XF_Consume WHERE ID_Key>=@MinIDKey) B1 ON B1.ID_Key=A1.ID_Key
    WHERE Data_Type=0 OR Data_Type=2
GO

---------------------------
IF OBJECT_ID('XF_pd_CleanupRepeatConsume') IS NOT NULL
   DROP PROCEDURE XF_pd_CleanupRepeatConsume
GO

CREATE  PROCEDURE dbo.XF_pd_CleanupRepeatConsume
AS
    --Get begin time
   DECLARE @BeginTime DATETIME
   SELECT @BeginTime=CAST(Param_Value AS DATETIME) FROM ST_SysParam WHERE Param_Code='Repeat.BeginTime'
   IF EXISTS(SELECT * FROM ST_CardInfo WHERE UPPER(SUBSTRING(Card_Type,1,2))='IC')
   BEGIN  
   	DECLARE @ConsumeTable TABLE(Person_ID INT,Consume_Time DATETIME,Consume_Fund MONEY,
   	Card_Balance MONEY,Data_Type TINYINT,ID_Key BIGINT)
      /**/
   	INSERT INTO @ConsumeTable
      SELECT Person_ID,Consume_Time,0,Card_Balance,0,MIN(B1.ID_Key)[ID_Key]
        FROM (SELECT ID_KEY FROM XF_Consume WITH (HOLDLOCK) WHERE Consume_Time>@BeginTime) A1 
        LEFT JOIN XF_Consume B1 ON B1.ID_Key=A1.ID_Key
       WHERE Data_Type=0 
       GROUP BY Person_ID,Consume_Time,Card_Balance HAVING COUNT(*)>1
   	--1.Backup
   	--1.1.Consume
   	INSERT INTO XF_ConsumeCleanup(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,Remark,Download_Time)
   	SELECT A.Person_ID,System_No,Consume_Mode,A.Consume_Time,Before_Fund,A.Consume_Fund,Consume_Copy,Consume_TimeLen,
             Consume_Capacity,Repast_No,Ware_ID,MOC_No,COM_No,A.Card_Balance,DB_Balance,Card_SmallAccount,A.Data_Type,
             Remark,Download_Time
     	  FROM @ConsumeTable B,XF_Consume A
   	 WHERE A.Consume_Time>@BeginTime AND A.ID_Key<>B.ID_Key AND A.Person_ID=B.Person_ID AND A.Consume_Time=B.Consume_Time
   	   AND A.Card_Balance=B.Card_Balance AND A.Data_Type=0   	
   	--2.Update
   	UPDATE ST_Card SET Card_Balance=Card_Balance+C.Consume_Fund
   	  FROM (SELECT SUM(A.Consume_Fund)[Consume_Fund],A.Person_ID FROM XF_Consume A,
   	          (SELECT * FROM @ConsumeTable WHERE Data_Type=0) B
   	         WHERE A.Consume_Time>@BeginTime AND A.Data_Type=0 AND A.Person_ID=B.Person_ID
                 AND A.Consume_Time=B.Consume_Time AND A.Card_Balance=B.Card_Balance AND A.ID_Key<>B.ID_Key
   	         GROUP BY A.Person_ID
   	       ) C
   	 WHERE ST_Card.Person_ID=C.Person_ID
   	--3.Delete
   	DELETE XF_Consume
   	  FROM (SELECT * FROM @ConsumeTable WHERE Data_Type=0) A
       WHERE A.Consume_Time>@BeginTime AND A.Data_Type=0 AND A.Person_ID=XF_Consume.Person_ID 
         AND A.Consume_Time=XF_Consume.Consume_Time
      	AND A.Card_Balance=XF_Consume.Card_Balance AND A.ID_Key<>XF_Consume.ID_Key
   END
   ELSE
   BEGIN
      /*取出存在重复消费人员的名单和一个有用的记录的Id_key*/
   	if OBJECT_ID('tempDB..#RP')<>0 drop table #RP
      /**/ 
      SELECT MIN(B1.ID_Key) AS ID_Key,Person_ID,Consume_Time,Consume_Fund 
        INTO #RP
        FROM (SELECT ID_KEY FROM XF_Consume WHERE Consume_Time>@BeginTime) A1 
        LEFT JOIN XF_Consume B1 ON B1.ID_Key=A1.ID_Key
      --2009.10.27 修正品种消费模式存在的BUG
       WHERE Ware_ID=-1 AND Data_Type=0
       GROUP BY Person_ID,Consume_Time,Consume_Fund  HAVING COUNT(*)>1
       /**/
   	if @@ROWCOUNT > 0 
   	begin
   		/*将重复的记录取出来*/
   		if OBJECT_ID('tempDB..#RP1')<>0 drop table #RP1
   		select A.* into #RP1 from XF_Consume A , #RP B  
   		  where A.person_ID=B.person_ID and A.consume_Time=B.Consume_Time and A.consume_fund=B.consume_fund 
          and A.id_Key<>B.id_key and Ware_ID=-1 AND A.Data_Type=0
   		/*合计重复的金额*/
   		if OBJECT_ID('tempDB..#RP2')<>0 drop table #RP2
   		select person_ID,Sum(consume_fund) as Funds Into #RP2 from #RP1
   		  group by person_ID
   		/*将重复消费的金额返回数据库*/
   		update ST_Card set Card_Balance=ST_Card.Card_Balance +#RP2.Funds from #RP2 
   		  where ST_Card.person_ID=#RP2.person_ID
   		/*保存并删除重复的记录*/
   		insert into xf_consumecleanup
                    (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,remark,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,data_type,remark,download_time
   		from #RP1
   
   		Delete from XF_Consume where id_key in (select id_Key from #RP1)
   		/*删除临时表*/
   		drop table #RP1
   		drop table #RP2
   	end
   	drop table #RP  
   END
   --Update begin time
   UPDATE ST_SysParam SET Param_Value=CONVERT(VARCHAR(50), A.Consume_Time, 121)
     FROM (SELECT ISNULL(MAX(Consume_Time),GETDATE()) [Consume_Time]
             FROM XF_Consume
          ) A
    WHERE Param_Code='Repeat.BeginTime'
GO

------------------------
IF OBJECT_ID('XF_pd_CardSupply') IS NOT NULL
   DROP PROCEDURE XF_pd_CardSupply
GO

CREATE PROCEDURE dbo.XF_pd_CardSupply
   (@PersonID INT,        --人员标识号
    @SupplyFund MONEY,    --充值金额
    @FundOld MONEY,       --原充值金额
    @CardNo VARCHAR(40),  --操作人员卡号
    @Balance MONEY,       --充值前的余额
    @IDKey INT,           --被纠正的记录ID
    @SupplyDate DATETIME, --充值日期
    @MocNo VARCHAR(20),   --终端机编号
    @IsCorrect BIT        --是否是纠错记录 
   )
AS   
  if Exists(Select 1 from ST_SupplyFund where Person_ID=@PersonID and Supply_Fund=@SupplyFund and Supply_Date=@SupplyDate) Return
   --0.更新IC卡余额
   UPDATE ST_Card 
      SET Update_Time=@SupplyDate,IC_Balance=@Balance
    WHERE Person_ID=@PersonID AND Update_Time<@SupplyDate AND DATEDIFF(day,Update_Time,@SupplyDate)<200
   DECLARE @UserNo VARCHAR(40) --充值操作员编号
   --1.取操作人员编号
   SELECT @UserNo=User_No FROM ST_UserInfo WHERE Card_No=@CardNo
   IF @UserNo IS NULL SET @UserNo=@CardNo
   IF @IsCorrect = 1 --充值纠错
   BEGIN
      --2.插入充值存款记录
      INSERT ST_SupplyFund(Person_ID,Supply_Date,Supply_Fund,User_No,Card_Balance,Data_Type,Remark)
      SELECT @PersonID,@SupplyDate,-@FundOld,@UserNo,@Balance-@SupplyFund+@FundOld,2,'充值纠错'
      UNION ALL SELECT @PersonID,@SupplyDate,@SupplyFund,@UserNo,@Balance-@SupplyFund,2,'纠错充值'
      --3.更新余额和补贴金额
      UPDATE ST_Card SET Card_Balance=Card_Balance+@SupplyFund-@FundOld  WHERE Person_ID=@PersonID
      --4.标识记录已经纠错过
      UPDATE ST_SupplyFund SET Data_Type=2 WHERE ID_KEY=@IDKey
   END
   ELSE
   BEGIN  
      --2.插入充值存款记录
      INSERT ST_SupplyFund(Person_ID,Supply_Date,Supply_Fund,User_No,Card_Balance,Data_Type,Moc_No,Remark)
      SELECT @PersonID,@SupplyDate,@SupplyFund,@UserNo,@Balance-@SupplyFund,1,@MocNo,'终端充值' 
      --3.更新余额和补贴金额
      UPDATE ST_Card SET Card_Balance=Card_Balance+@SupplyFund WHERE Person_ID=@PersonID
   END
GO


------------------------
IF OBJECT_ID('XF_pd_CardConsume') IS NOT NULL
   DROP PROCEDURE XF_pd_CardConsume
GO

CREATE PROCEDURE dbo.XF_pd_CardConsume
   (@PersonID INT,         --人员标识号
    @CardNo VARCHAR(20),   --卡号
    @SystemNo VARCHAR(20), --子系统编号
    @ConsumeMode TINYINT,  --消费模式
    @ConsumeTime DATETIME, --消费时间
    @Balance MONEY,        --卡余额(本次消费后的余额)
    @DBBalance MONEY,      --库余额(本次消费后的余额)
    @BeforeFund MONEY,     --打折前的消费金额
    @ConsumeFund MONEY,    --打折后的消费金额
    @FundOld MONEY,        --原消费金额
    @Copies INT,           --消费份数
    @Quantity INT,         --消费数量
    @TimeLen INT,          --消费时长
    @RepastNo VARCHAR(20), --餐别编号
    @WareID VARCHAR(20),   --商品编号
    @MocNo VARCHAR(20),    --终端机编号
    @HardNo VARCHAR(20),   --硬件编号
    @IDKey INT,            --纠错记录的ID_KEY
    @Options TINYINT,      --记录选项(0表示消费,1表示纠错,2表示脱机,3表示有卡无人的脱机记录,
                           --        4表示联机假写卡记录,5表示脱机纠错记录,6表示脱机假写卡记录)
    @SubsidyFund MONEY     --本次发放补贴金额
   )
AS   
   --0.更新IC卡余额
   UPDATE ST_Card 
      SET Update_Time=@ConsumeTime,IC_Balance=@Balance
    WHERE Person_ID=@PersonID AND Update_Time<=@ConsumeTime AND DATEDIFF(day,Update_Time,@ConsumeTime)<200
   --Update begin time
   UPDATE ST_SysParam SET Param_Value=CONVERT(VARCHAR(50), @ConsumeTime, 121)
    WHERE Param_Code='Fake.BeginTime' AND CAST(Param_Value AS DATETIME)>@ConsumeTime
   --
   UPDATE ST_SysParam SET Param_Value=CONVERT(VARCHAR(50), @ConsumeTime, 121)
    WHERE Param_Code='Repeat.BeginTime' AND CAST(Param_Value AS DATETIME)>@ConsumeTime
   --
   DECLARE @DataType TINYINT   
   IF @Options = 1 --消费纠错
   BEGIN
      --1.更新余额和补贴金额
      UPDATE ST_Card 
         SET @DBBalance=Card_Balance-@ConsumeFund+@FundOld,Card_Balance=@DBBalance
       WHERE Person_ID=@PersonID
      --2.插入消费记录
      INSERT INTO XF_ConsumeTemp(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,Is_Commited) 
      SELECT @PersonID,@SystemNo,@ConsumeMode,GETDATE(),-Before_Fund,-Consume_Fund,-Consume_Copy,-Consume_TimeLen,-Consume_Capacity,@RepastNo,
             Ware_ID,@MocNo,@HardNo,@Balance,@DBBalance,0,2,0 FROM XF_ConsumeTemp WHERE ID_KEY=@IDKEY --纠错记录
      UNION ALL SELECT @PersonID,@SystemNo,@ConsumeMode,GETDATE(),@BeforeFund,@ConsumeFund,@Copies,@TimeLen,@Quantity,@RepastNo,
                       @WareID,@MocNo,@HardNo,@Balance,@DBBalance,0,2,0 --新消费记录
      --3.更新纠错标识
      UPDATE XF_ConsumeTemp SET Data_Type=2 WHERE ID_KEY=@IDKey
      --4.更新已提交的纠错标识
      UPDATE XF_Consume SET Data_Type=2 
        FROM (SELECT Source_ID FROM XF_ConsumeTemp WHERE ID_KEY=@IDKey AND Is_Commited=1) A
       WHERE ID_KEY=A.Source_ID
   END
   ELSE IF @Options <> 3 --正常消费或假写卡或脱机消费、纠错
   BEGIN
      IF (@Options = 0) OR (@Options = 4) SET @ConsumeTime=GETDATE() --非脱机记录取数据库的当前时间
      IF (@Options = 0) OR (@Options = 2) SET @DataType=0 --联机消费或脱机消费
      ELSE IF @Options = 5 SET @DataType=2 --脱机纠错
      ELSE SET @DataType=8 --假写卡
      IF @DataType<>8 --非假写卡记录
      BEGIN         
         IF (@Options <> 5) AND (@Options <> 2) --非脱机记录
         BEGIN
            --1.更新余额和补贴金额
            UPDATE ST_Card 
               SET @DBBalance=Card_Balance-@ConsumeFund,Card_Balance=@DBBalance
             WHERE Person_ID=@PersonID
            --2.插入消费记录
            INSERT INTO XF_ConsumeTemp(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,Is_Commited)
            SELECT @PersonID,@SystemNo,@ConsumeMode,@ConsumeTime,@BeforeFund,@ConsumeFund,@Copies,@TimeLen,@Quantity,@RepastNo,
                   @WareID,@MocNo,@HardNo,@Balance,@DBBalance,0,@DataType,0
         END
         ELSE  --脱机记录
         BEGIN 
            --1.更新余额和补贴金额
            UPDATE ST_Card 
               SET @DBBalance=Card_Balance-@ConsumeFund,Card_Balance=@DBBalance
             WHERE Person_ID=@PersonID
            --2.插入消费记录
            INSERT INTO XF_ConsumeTemp(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,Is_Commited,Download_Time)
            SELECT @PersonID,@SystemNo,@ConsumeMode,@ConsumeTime,@BeforeFund,@ConsumeFund,@Copies,@TimeLen,@Quantity,@RepastNo,
                   @WareID,@MocNo,@HardNo,@Balance,@DBBalance,0,@DataType,0,GETDATE()
         END
      END
      ELSE --假写卡记录
      BEGIN
         --2.插入消费记录
         IF (@Options = 4) --非脱机记录
         BEGIN
            INSERT INTO XF_ConsumeFake(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)
            SELECT @PersonID,@SystemNo,@ConsumeMode,@ConsumeTime,@BeforeFund,@ConsumeFund,@Copies,@TimeLen,@Quantity,@RepastNo,
                   @WareID,@MocNo,@HardNo,@Balance,@DBBalance,0,@DataType
         END
         ELSE  --脱机记录
         BEGIN 
            INSERT INTO XF_ConsumeFake(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 @PersonID,@SystemNo,@ConsumeMode,@ConsumeTime,@BeforeFund,@ConsumeFund,@Copies,@TimeLen,@Quantity,@RepastNo,
                  @WareID,@MocNo,@HardNo,@Balance,@DBBalance,0,@DataType,GETDATE()
         END
      END
   END
   ELSE
   BEGIN
      SET @DBBalance=0
      --1.插入有卡无人消费记录
      INSERT INTO ST_NoPersonCard(Card_No,System_No,Consume_Mode,Consume_Time,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_Date)
      SELECT @CardNo,@SystemNo,@ConsumeMode,@ConsumeTime,@ConsumeFund,@Copies,@TimeLen,@Quantity,@RepastNo,
             @WareID,@MocNo,@HardNo,@DBBalance,@Balance,0,0,GETDATE()
   END
GO

IF OBJECT_ID('XF_pd_CardSupplySmall') IS NOT NULL
   DROP PROCEDURE XF_pd_CardSupplySmall
GO
CREATE PROCEDURE dbo.XF_pd_CardSupplySmall
   (@PersonID INT,        --人员标识号
    @SupplyFund MONEY,    --充值金额
    @Balance MONEY,       --转帐后的余额
    @CardNo VARCHAR(40),  --操作人员卡号
    @SupplyDate DATETIME, --充值日期
    @MocNo VARCHAR(20)    --终端机编号
   )
AS  
   --0.更新IC卡余额 
   UPDATE ST_Card 
      SET Update_Time=@SupplyDate,IC_Balance=@Balance
    WHERE Person_ID=@PersonID AND Update_Time<@SupplyDate AND DATEDIFF(day,Update_Time,@SupplyDate)<200
   DECLARE @UserNo VARCHAR(40) --充值操作员编号
   --1.取操作人员编号
   SELECT @UserNo=User_No FROM ST_UserInfo WHERE Card_No=@CardNo
   --2.插入充值存款记录
   INSERT ST_SupplyFund(Person_ID,Supply_Date,Supply_Fund,User_No,Card_Balance,Data_Type,Moc_No,Remark)
   SELECT @PersonID,@SupplyDate,@SupplyFund,@UserNo,0,1,@MocNo,'SupplySmall'
   --3.插入转帐记录
   INSERT ST_TransferAccount(Person_ID,Transfer_Date,Transfer_Fund,User_No,Card_Balance,Moc_No,Remark)
   SELECT @PersonID,@SupplyDate,@SupplyFund,@UserNo,0,@MocNo,'SupplySmall'
 
GO

IF OBJECT_ID('XF_pd_CardTakeFund') IS NOT NULL
   DROP PROCEDURE XF_pd_CardTakeFund
GO
CREATE PROCEDURE dbo.XF_pd_CardTakeFund
   (@PersonID INT,        --人员标识号
    @TakeFund MONEY,      --取款金额
    @Balance MONEY,       --取款前的余额
    @CardNo VARCHAR(40),  --操作人员卡号
    @TakeDate DATETIME,   --取款日期
    @MocNo VARCHAR(20)   --终端机编号
   )
AS   
   --0.更新IC卡余额
   UPDATE ST_Card 
      SET Update_Time=@TakeDate,IC_Balance=@Balance
    WHERE Person_ID=@PersonID AND Update_Time<@TakeDate AND DATEDIFF(day,Update_Time,@TakeDate)<200
   DECLARE @UserNo VARCHAR(40) --充值操作员编号
   --1.取操作人员编号
   SELECT @UserNo=User_No FROM ST_UserInfo WHERE Card_No=@CardNo
   IF @UserNo IS NULL SET @UserNo=@CardNo
    --2.插入取款记录
   INSERT ST_TakeFund(Person_ID,Take_Date,Take_Fund,User_No,Card_Balance,Moc_No,Remark)
   SELECT @PersonID,@TakeDate,@TakeFund,@UserNo,@Balance+@TakeFund,@MocNo,'MocTakeFund' 
   --3.更新余额和补贴金额
   UPDATE ST_Card SET Card_Balance=Card_Balance-@TakeFund WHERE Person_ID=@PersonID 
GO

IF OBJECT_ID('XF_pd_CleanupFakeConsume') IS NOT NULL
   DROP PROCEDURE XF_pd_CleanupFakeConsume
GO

CREATE PROCEDURE dbo.XF_pd_CleanupFakeConsume
AS
   --Get begin time
   DECLARE @BeginTime DATETIME
   SELECT @BeginTime=CAST(Param_Value AS DATETIME) FROM ST_SysParam WHERE Param_Code='Fake.BeginTime'
   --1.SELECT
   DECLARE @Table TABLE(ID_KEY BIGINT, Person_ID INT,Consume_Time DATETIME,Consume_Fund MONEY,
     Card_Balance MONEY,DB_Balance MONEY,Moc_No VARCHAR(20),COM_No VARCHAR(20))
   DECLARE @FakeConsume TABLE(ID_KEY BIGINT,Person_ID INT,Consume_Fund MONEY)
   DECLARE @Interval TINYINT
   
   SET @Interval=3
   
   INSERT INTO @Table
   SELECT ID_KEY,A.Person_ID,Consume_Time,A.Consume_Fund,A.Card_Balance,DB_Balance,A.Moc_No,COM_No
     FROM (SELECT Person_ID,Consume_Fund,Card_Balance,Moc_No
             FROM (SELECT ID_KEY FROM XF_Consume WHERE Consume_Time>@BeginTime) A1
             LEFT JOIN XF_Consume B1 ON B1.ID_KEY=A1.ID_KEY
            WHERE Data_Type=0 AND Consume_Fund<>0
            GROUP BY Person_ID,Consume_Fund,Card_Balance,Moc_No
           HAVING COUNT(*)>1
          ) B,XF_Consume A
    WHERE Consume_Time>@BeginTime AND A.Person_ID=B.Person_ID AND A.Consume_Fund=B.Consume_Fund
      AND A.Card_Balance=B.Card_Balance AND A.Moc_No=B.Moc_No
    ORDER BY A.Person_ID,Consume_Time
   
   INSERT INTO @FakeConsume
   SELECT ID_Key,Person_ID,Consume_Fund
     FROM (SELECT ID_Key,Person_ID,Consume_Fund,Consume_Time,
                  (SELECT TOP 1 Consume_Time FROM @Table WHERE Person_ID=A.Person_ID AND Consume_Fund=A.Consume_Fund
                      AND Card_Balance=A.Card_Balance AND Moc_No=a.Moc_No AND Consume_Time<A.Consume_Time 
                    ORDER BY Consume_Time DESC
                  ) dDateEnd
             FROM @Table A
          ) B
    WHERE dDateEnd IS NOT NULL AND DATEDIFF(s, dDateEnd, Consume_Time)<=@Interval
   --2.Backup
   INSERT INTO XF_ConsumeCleanup(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,Remark,Download_Time)
   SELECT C.Person_ID,System_No,Consume_Mode,C.Consume_Time,Before_Fund,C.Consume_Fund,
          Consume_Copy,Consume_TimeLen,Consume_Capacity,Repast_No,Ware_ID,MOC_No,COM_No,Card_Balance,DB_Balance,
          Card_SmallAccount,Data_Type,Remark,Download_Time
     FROM XF_Consume C,@FakeConsume D 
    WHERE D.ID_Key=C.ID_Key
   --3.UPDATE
   UPDATE ST_Card SET Card_Balance=Card_Balance+C.Consume_Fund
     FROM (SELECT Person_ID,SUM(Consume_Fund)[Consume_Fund]
             FROM @FakeConsume GROUP BY Person_ID
          ) C
    WHERE ST_Card.Person_ID=C.Person_ID
   --4.DELETE
   DELETE XF_Consume FROM @FakeConsume C WHERE C.ID_Key=XF_Consume.ID_Key 
   --Update begin time
   UPDATE ST_SysParam SET Param_Value=CONVERT(VARCHAR(50), A.Consume_Time, 121)
     FROM (SELECT ISNULL(MAX(Consume_Time),GETDATE()) [Consume_Time]
             FROM XF_Consume
          ) A
    WHERE Param_Code='Fake.BeginTime'
GO

IF OBJECT_ID('XF_pd_GetCardTypeRepast') IS NOT NULL
   DROP PROCEDURE XF_pd_GetCardTypeRepast 
GO
CREATE  Procedure XF_pd_GetCardTypeRepast 
(
@ConsumeTimeEnd VarChar(20),
@ConsumeTimeBng VarChar(20)
)
AS
DECLARE @SQL VARCHAR(2000),
        @RepastNo VARCHAR(10)

DECLARE repast_cursor CURSOR FOR
--
SELECT Repast_No FROM SF_Repast
OPEN repast_cursor
--
FETCH NEXT FROM repast_cursor
INTO @RepastNo
--
    SET @SQL='SELECT C.Type_No,[Type_Name],'
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL=@SQL+'SUM(CASE WHEN Repast_No='''+ @RepastNo + '''' +
        ' THEN CountAll ELSE 0 END) [Count' + @RepastNo +'],' +
        'SUM(CASE WHEN Repast_No=''' + @RepastNo + '''' +
        ' THEN FundsAll ELSE 0 END) [Funds' + @RepastNo + '],'
        FETCH NEXT FROM repast_cursor
        INTO @RepastNo
    END
--
CLOSE repast_cursor
DEALLOCATE repast_cursor

SET @SQL=@SQL+'SUM(CountAll)[CountAll],SUM(FundsAll) [FundsAll]' +
'  FROM  ' +
'  (SELECT Type_No,Repast_No,SUM(Consume_Copy)[CountAll],SUM(Consume_Fund) [FundsAll]' +
'  FROM ST_Card A' +
'  LEFT JOIN XF_Consume B ON B.Person_ID=A.Person_ID' +
'  WHERE Consume_Time >=''' + @ConsumeTimeBng + ''' and Consume_Time <= '''+ @ConsumeTimeEnd + '''' +
'   AND B.SysTem_No='''+'SF'''+
'  GROUP BY Type_No,Repast_No ' +
'  Union All ' + 
'  SELECT Type_No,Repast_No,SUM(Consume_Copy)[CountAll],SUM(Consume_Fund) [FundsAll]' +
'  FROM ST_Card A' +
'  LEFT JOIN XF_ConsumeHistory B ON B.Person_ID=A.Person_ID' +
'  WHERE Consume_Time >=''' + @ConsumeTimeBng + ''' and Consume_Time <= '''+ @ConsumeTimeEnd + '''' +
'   AND B.SysTem_No='''+'SF'''+
'  GROUP BY Type_No,Repast_No ' +
'  ) C' +
'  LEFT JOIN ST_CardType D ON D.Type_No=C.Type_No' +
    '  GROUP BY  C.Type_No,[Type_Name]'
EXEC(@SQL)
GO


IF OBJECT_ID('SF_pd_CanBeiHuiZongBeg') IS NOT NULL
   DROP PROCEDURE SF_pd_CanBeiHuiZongBeg
GO

CREATE procedure SF_pd_CanBeiHuiZongBeg
  @Is_Check bit,
  @BegDateTime Varchar(255),
  @EndDateTime Varchar(255),
  @BegDate Varchar(255)
as 
begin
  Declare @rec Varchar(50),    @ref Varchar(50),   
               @RepastCount Varchar(50),   @RepastFunds Varchar(50),
              @Repast_No Varchar(3),   @SQLCreate NVarchar(4000)

  Set @rec='rec'  set @ref='ref'
  set @RepastCount='RepastCount'  set @RepastFunds='RepastFunds'
 
  /*过虑数据临时表*/
  if Object_Id('Tempdb..##Temp_Consume') is not Null 
      Drop Table ##Temp_Consume

   Create Table  ##Temp_Consume (
	                     ID_KEY bigint IDENTITY (1, 1) NOT NULL ,
	                     Person_ID  int NOT NULL ,
	                     Consume_Time  datetime NOT NULL ,
	                     Consume_Fund money NOT NULL ,
	                     Consume_Copy int NULL ,
                                   Repast_No  varchar (3) COLLATE Chinese_PRC_CI_AS NULL
     ) On  [PRIMARY]
    
     Alter Table  ##Temp_Consume  With NoCheck Add  Primary Key   Clustered    ( ID_Key)  ON [PRIMARY]
     Alter Table  ##Temp_Consume With NoCheck Add Constraint DF_Temp_Consume Default (getdate()) For Consume_Time
     Create  Index IX_Temp_Consume_Person_ID ON  ##Temp_Consume(Person_ID, Consume_Time) ON [PRIMARY]

     Insert Into ##Temp_Consume(Person_ID,Consume_Time,Consume_Fund,Consume_Copy,Repast_No)
           Select Person_ID,Consume_Time,Consume_Fund,Consume_Copy,Repast_No From XF_Consume
           Where  (CONVERT(CHAR(16),  Consume_Time, 20) >= @BegDateTime) And  (CONVERT(CHAR(16),  Consume_Time, 20) <= @EndDateTime)
                  AND System_No='SF'   
     UNION ALL
           Select Person_ID,Consume_Time,Consume_Fund,Consume_Copy,Repast_No From XF_ConsumeHistory
           Where  (CONVERT(CHAR(16),  Consume_Time, 20) >= @BegDateTime) And  (CONVERT(CHAR(16),  Consume_Time, 20) <= @EndDateTime)
                  AND System_No='SF'   
  /*创建部门信息临时表*/
  if Object_Id('Tempdb..##SF_PsnRepastRpt') is not Null 
     Drop Table ##SF_PsnRepastRpt

  Create Table ##SF_PsnRepastRpt
              ( Person_ID int ,
                Person_No varChar(20) Not Null,
                Person_Name varchar(20) Null,
                Dept_No varchar(30) Null,
                Dept_Name varchar(30) Null)

  if @Is_Check=0 
     Insert Into ##SF_PsnRepastRpt (Person_ID,Person_No,Person_Name,Dept_No,Dept_Name)
         Select dbo.ST_Person.Person_ID,dbo.ST_Person.Person_No,dbo.ST_Person.Person_Name,dbo.ST_Department.Dept_No,
             dbo.ST_Department.Dept_Name From dbo.ST_Person,dbo.ST_Department 
                /*JenanWang 2006.12.12 不统计注销人员*/
             Where ST_Person.Is_Del=0 AND dbo.ST_Person.Dept_ID=dbo.ST_Department.Dept_ID 
               /*peihb 2008-12-26 过滤在本系统中不可使用人员*/
              and dbo.ST_Person.Person_ID in (select Person_ID from ST_SubSysRight where system_no='SF')
  else
      Insert into ##SF_PsnRepastRpt (Person_ID,Person_No,Person_Name,Dept_No,Dept_Name)
          Select dbo.ST_Person.Person_ID,dbo.ST_Person.Person_No,dbo.ST_Person.Person_Name,dbo.ST_Department.Dept_No,
              dbo.ST_Department.Dept_Name From dbo.ST_Person,dbo.ST_Department 
              Where dbo.ST_Person.Dept_ID=dbo.ST_Department.Dept_ID 
              and dbo.ST_Person.Person_ID in (select Person_ID from ST_SubSysRight where system_no='SF')

  /*汇总的临时表*/
  if Object_Id('Tempdb..##SF_RepastSum') is not Null 
     Drop Table ##SF_RepastSum

    /*创建返回结果集*/
  if Object_Id('Tempdb..##SF_PsnDayRpt') is not Null 
     Drop Table ##SF_PsnDayRpt
  Select  @SQLCreate=''
  Declare Repast_cur2 Cursor Local Forward_only Static Read_only For
      Select  Repast_No  From SF_Repast Order By Repast_No
  Open Repast_cur2
  Fetch Next From Repast_cur2 Into @Repast_No
  While (@@fetch_status=0)
  Begin
    Select @SQLCreate = @SQLCreate+@RepastCount+@Repast_No+' int NULL Default 0 ,'+@RepastFunds+@Repast_No+' money NULL Default 0, ';
    Fetch Next From Repast_cur2 Into @Repast_No
  End;
  Close Repast_cur2
  Deallocate Repast_cur2
  
  Select @SQLCreate  = ' Create Table ##SF_PsnDayRpt(Consume_time varchar(10) Not NULL, '+
                                       @SQLCreate+'RepastCountSum money NULL Default 0.00,RepastFundsSum money NULL Default 0.00,'+
                                       ' Person_No varChar(20) Not Null,'+
                                        ' Person_Name varchar(20) Null,Dept_No varchar(30) Null,Dept_Name varchar(30) Null )';

  Exec sp_executesql @SQLCreate;
end;

GO


IF OBJECT_ID('SF_pd_CanBeiHuiZongCal') IS NOT NULL
   DROP PROCEDURE SF_pd_CanBeiHuiZongCal
GO

CREATE procedure SF_pd_CanBeiHuiZongCal
  @Is_Check bit,
  @BegDateTime Varchar(255),
  @EndDateTime Varchar(255),
  @BegDate Varchar(255)
as 
begin
  Declare @SelGroup NVarchar(4000),  @sField  Varchar(2000),
          @sValue  Varchar(2000),      @Display  Varchar(2000),
          @DisplaySum1 Varchar(2000),    @DisPlaySum2 Varchar(2000),
          @Repast_No Varchar(255),   @Bgn_Time Varchar(255),
          @End_Time Varchar(255),     @sBegDate Varchar(255),
          @sEndDate varchar(255),     @rec Varchar(50),
          @ref Varchar(50),     @RepastCount Varchar(50),  
          @RepastFunds Varchar(50),    @SqlStr Varchar(2000),
          @sField1 Varchar(2000)

  Set @rec='rec'  set @ref='ref'
  set @RepastCount='RepastCount'  set @RepastFunds='RepastFunds'

  /*汇总的临时表*/
  if Object_Id('Tempdb..##SF_RepastSum') is not Null 
     Drop Table ##SF_RepastSum

  Set @DisplaySum1 = '';  Set @Display = '';  Set @DisPlaySum2 = '';  Set @SelGroup = '';  Set @sField = '';  Set @sValue = '';  Set @sField1='';
  /*获取参别信息*/
  Declare Repast_cur2 Cursor Local Forward_only Static Read_only For
      Select  Repast_No,Bgn_Time,End_Time  From dbo.SF_Repast Order By Repast_No
  Open Repast_cur2
  Fetch Next From Repast_cur2 Into @Repast_No,@Bgn_Time,@End_Time
  While (@@fetch_status=0)
  Begin
    Select @sBegDate=@BegDate+' '+@Bgn_Time
    If @Bgn_Time <= @End_Time
      Select @sEndDate=@BegDate+' '+@End_Time
    Else
      Select @sEndDate=convert(char(10),Cast(@BegDate as DateTime)+1,20)+' '+@End_Time
    
    
    /*餐别起始时间和结束时间都为零点时表示不统计该餐别，汇总时直接sum(0) as RepastField  peihb 2010-04-16*/
    IF @Bgn_Time='00:00' AND @End_Time='00:00'
    BEGIN
      Select @sField= @sField+','+@Rec+@Repast_No+','+@Ref+@Repast_No;
      Select @sField1=@sField1+','+@RepastCount+@Repast_No+','+@RepastFunds+@Repast_No;
      Select @sValue = @sValue +',0,0.00'	
      Select @Display = @Display+',sum(isnull(0,0)) '+@Rec+@Repast_No+',sum(isnull(0,0)) '+@Ref+@Repast_No;
      Select @DisplaySum1 = @DisplaySum1+'+sum(isnull(0,0))';
      Select @DisPlaySum2 = @DisPlaySum2+'+sum(isnull(0,0))';	
    END 
    ELSE
    BEGIN
      Select @sField= @sField+','+@Rec+@Repast_No+','+@Ref+@Repast_No;
      Select @sField1=@sField1+','+@RepastCount+@Repast_No+','+@RepastFunds+@Repast_No;
      Select @sValue = @sValue +',0,0.00'
      Select @Display = @Display+',sum(isnull('+@Rec+@Repast_No+',0)) '+@Rec+@Repast_No+',sum(isnull('+@Ref+@Repast_No+',0)) '+@Ref+@Repast_No;
      Select @DisplaySum1 = @DisplaySum1+'+sum(isnull('+@Rec+@Repast_No+',0))';
      Select @DisPlaySum2 = @DisPlaySum2+'+sum(isnull('+@Ref+@Repast_No+',0))';	
    END	
    
    Select @SelGroup = @SelGroup+',Case When (CONVERT(CHAR(16),Consume_Time, 20) Between '''+@sBegDate+''' And '''+@sEndDate+'''AND Repast_No=''' + @Repast_No + ''') then Consume_Copy end as Rec'+@Repast_No+
                                ',Case When (CONVERT(CHAR(16),Consume_Time, 20) Between '''+@sBegDate+''' And '''+@sEndDate+'''AND Repast_No=''' + @Repast_No + ''') then Consume_Fund end as Ref'+@Repast_No;
    /*返回临时数据集*/
    Fetch Next From Repast_cur2 Into @Repast_No,@Bgn_Time,@End_Time
  End;
  Close Repast_cur2
  Deallocate Repast_cur2
  /**/
  Set @Display = SubString(@Display,2,len(@Display))
  Set @DisplaySum1 = SubString(@DisplaySum1,2,len(@DisplaySum1))+' RepastCountSum'
  Set @DisPlaySum2 = SubString(@DisPlaySum2,2,len(@DisPlaySum2))+' RepastFundsSum'
  Set @SelGroup = SubString(@SelGroup,2,len(@SelGroup))

  Select @SelGroup = 'Select Person_ID,'+@Display+','+@DisplaySum1+','+@DisPlaySum2+' Into ##SF_RepastSum'+
                    ' From (Select Person_ID,'+@SelGroup+' from ##Temp_Consume) bb Group By Person_ID';

  Exec sp_executesql   @SelGroup
  Delete ##Temp_Consume where   (CONVERT(CHAR(16),  Consume_Time, 20) >= @BegDate+' '+' 00:00' ) And  (CONVERT(CHAR(16),  Consume_Time, 20) <= @BegDate+' '+' 23:59')
 /*插入职员信息*/
  Select @SelGroup=' Insert into ##SF_RepastSum(Person_ID'+@sField+',RepastCountSum,RepastFundsSum)
                    Select Person_ID'+@sValue+',0,0.00 From ##SF_PsnRepastRpt where Person_ID not in (select Person_ID from ##SF_RepastSum)'
  Exec sp_executesql @SelGroup

  /**/
  Select  @SelGroup  = ' Insert Into ##SF_PsnDayRpt(Consume_time,Person_No,Person_Name,Dept_No,Dept_Name'+@sField1+',RepastCountSum,RepastFundsSum) '
                                     +'  Select '''+@BegDate+''',P.Person_No, P.Person_Name,P.Dept_No,P.Dept_Name'+@sField+',S.RepastCountSum,S.RepastFundsSum From ##SF_PsnRepastRpt P,##SF_RepastSum S Where P.Person_ID = S.Person_ID'
  Exec sp_executesql @SelGroup
end;

GO