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

------訂餐數據明細查詢
CREATE  PROCEDURE dbo.SF_Order_pd_OrderDetailQry
@qryType INT,				--查詢類別
@Dept VARCHAR(20),			--部門條件或員工編號 ,根據查詢類別來確定
@Repast VARCHAR(500),			--餐別條件
@DateBgn DATETIME,			--起始日期
@DateEnd DATETIME,			--結束日期
@UserNo VARCHAR(40)
AS

set nocount on

Declare @BgnDate varchar(10),@EndDate varchar(10)
SET @BgnDate = CONVERT(CHAR(10),  @DateBgn, 120)
SET @EndDate = CONVERT(CHAR(10),  @DateEnd, 120)

/*訂餐數據*/ 
IF @qryType=1 /** 按部門 + 日期 + 餐別**/
   SELECT ST_Department.Dept_Name, ST_Person.Person_Name, 
      ST_Person.Person_No, SF_OrderData.Order_date, 
      SF_OrderData.Repast_No, SF_Repast.Repast_Name, 
      SF_OrderData.Moc_No
   FROM dbo.SF_Repast RIGHT OUTER JOIN
      SF_OrderData ON 
      SF_Repast.Repast_No = SF_OrderData.Repast_No LEFT OUTER JOIN
      ST_Department INNER JOIN
      ST_Person ON ST_Department.Dept_ID = ST_Person.Dept_ID ON 
      SF_OrderData.Person_ID = ST_Person.Person_ID
    WHERE  Dept_No Like @Dept
     AND   charindex(SF_OrderData.Repast_no, @Repast) > 0 
     AND   Order_date <= @EndDate
     AND   Order_date >= @BgnDate
     AND (@UserNo='admin' OR ST_Person.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE User_No=@UserNo))
    ORDER BY ST_Department.Dept_No,ST_Person.Person_No,SF_OrderData.Order_Date
ELSE  /** 按人員 + 日期 + 餐別**/
   SELECT ST_Department.Dept_Name, ST_Person.Person_Name, 
      ST_Person.Person_No, SF_OrderData.Order_date, 
      SF_OrderData.Repast_No, SF_Repast.Repast_Name, 
      SF_OrderData.Moc_No
   FROM dbo.SF_Repast RIGHT OUTER JOIN
      SF_OrderData ON 
      SF_Repast.Repast_No = SF_OrderData.Repast_No LEFT OUTER JOIN
      ST_Department INNER JOIN
      ST_Person ON ST_Department.Dept_ID = ST_Person.Dept_ID ON 
      SF_OrderData.Person_ID = ST_Person.Person_ID
    WHERE  ST_Person.Person_No =@Dept
     AND   charindex(SF_OrderData.Repast_no, @Repast) > 0 
     AND   Order_date <= @EndDate
     AND   Order_date >= @BgnDate
     AND (@UserNo='admin' OR ST_Person.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE User_No=@UserNo))
    ORDER BY ST_Department.Dept_No,ST_Person.Person_No,SF_OrderData.Order_Date
GO

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

--訂餐數據匯總過程：
CREATE    PROCEDURE  dbo.SF_Order_pd_OrderTotalQry
@TotalType INT,
@Dept VARCHAR(20),
@Repast VARCHAR(500),
@BDate DATETIME,
@EDate DATETIME,
@UserNo VARCHAR(40)
AS

set nocount on 

Declare @str   varchar(5),@BgnDate varchar(12),@EndDate varchar(12),@SQL   varchar(1000)
Declare @i  int, @j  int

set @BgnDate=convert(varchar(10),@BDate,120)
set @EndDate=convert(varchar(10),@EDate,120)

/*創建 人員餐別表*/
IF object_ID('SF_OrderDataTotal') is not null 
   DROP table SF_OrderDataTotal 

SELECT @SQL=
(case @TotalType
 when 0 then    /*按部門匯總*/
   ' Create Table SF_OrderDataTotal([Dept_No] [varchar] (20) null '
 when 1 then    /* 按人員匯總*/
   ' Create Table SF_OrderDataTotal([Person_Id] [int] null '
 when 2 then    /*按食堂匯總*/
   ' Create Table SF_OrderDataTotal([Area_No] [varchar] (10) null '
 when 3 then    /* 按餐別*/
   ' Create Table SF_OrderDataTotal([Person_Id] [int] null '
end) 

set @j=1 
while @j < len(@Repast) 
begin
  set @i=charindex(',',@Repast,@j)
  if @i>@j 
  begin
     set @str=substring(@Repast,@j,@i-@j)
     set @j=@i+1
  end
  else
  begin
     set @str=substring(@Repast,@j,Len(@Repast)-@j+1)
     set @j=500
  end
  
  if Len(@Str)>0 
    set @SQL=@SQL+' , [R'+@str+'] [int] default 0' 
end

set @SQL=@SQL+')'
Exec(@SQL)

/**收集訂餐數據**/
IF object_ID('tempdb..#TempOrderData') is not null
   DROP table #TempOrderData

SELECT ST_Department.Dept_No, ST_Person.Person_No, 
      SF_OrderData.Person_ID, SF_OrderData.Order_date, 
      SF_OrderData.Repast_No, SF_OrderMoc.Area_No
INTO  #TempOrderData
FROM SF_OrderMoc RIGHT OUTER JOIN
      SF_OrderData ON 
      SF_OrderMoc.Moc_No = SF_OrderData.Moc_No LEFT OUTER JOIN
      ST_Department INNER JOIN
      ST_Person ON ST_Department.Dept_ID = ST_Person.Dept_ID ON 
      SF_OrderData.Person_ID = ST_Person.Person_ID
WHERE  ST_Department.Dept_No like @Dept
 AND   charindex(Repast_no, @Repast) > 0 
 AND   Order_date <= @EndDate
 AND   Order_date >= @BgnDate


/**插入記錄**/
SELECT @SQL=
(case @TotalType
 when 0 then    /*按部門匯總*/
   ' INSERT INTO SF_OrderDataTotal(Dept_No) '
  +'  select Dept_No from #TempOrderData Group by Dept_No' 
 when 1 then    /* 按人員匯總*/
   ' INSERT INTO SF_OrderDataTotal(Person_Id) '
  +'  select Person_Id from #TempOrderData Group by Person_Id' 
 when 2 then    /*按食堂匯總*/
   ' INSERT INTO SF_OrderDataTotal(Area_No) '
  +'  select Area_No from #TempOrderData Group by Area_No'
 when 3 then    /* 按餐別*/
   ' INSERT INTO SF_OrderDataTotal(Person_Id) Values(0)'

end) 
EXEC(@SQL)


/**匯總**/
set @j=1 
while @j < len(@Repast) 
begin
  set @i=charindex(',',@Repast,@j)
  if @i>@j 
  begin
     set @str=substring(@Repast,@j,@i-@j)
     set @j=@i+1
  end
  else
  begin
     set @str=substring(@Repast,@j,Len(@Repast)-@j+1)
     set @j=500
  end
  
  if Len(@Str)>0 
  begin
    SELECT @SQL=
    (case @TotalType
	 when 0 then    /*按部門匯總*/
	   'Update SF_OrderDataTotal set R'+@str+' = '
	        +' (Select Count(*) from #TempOrderData '
	        +'  where #TempOrderData.Dept_No = SF_OrderDataTotal.Dept_No'
                +'  and   #TempOrderData.Repast_No = '+char(39)+@str+char(39)
                +')'
         when 1 then    /* 按人員匯總*/
	   'Update SF_OrderDataTotal set R'+@str+' = '
	        +' (Select Count(*) from #TempOrderData '
	        +'  where #TempOrderData.Person_Id = SF_OrderDataTotal.Person_Id'
                +'  and   #TempOrderData.Repast_No = '+char(39)+@str+char(39)
                +')'
	 when 2 then    /*按食堂匯總*/
	   'Update SF_OrderDataTotal set R'+@str+' = '
	        +' (Select Count(*) from #TempOrderData '
	        +'  where #TempOrderData.Area_No = SF_OrderDataTotal.Area_No '
                +'  and   #TempOrderData.Repast_No = '+char(39)+@str+char(39)
                +')'
	 when 3 then    /* 按餐別*/
	   'Update SF_OrderDataTotal set R'+@str+' = '
	        +' (Select Count(*) from #TempOrderData '
	        +'  where #TempOrderData.Repast_No = '+char(39)+@str+char(39)
                +')'
     end)
     EXEC(@SQL)   
  end 
end

/**返回**/
SELECT @SQL=
(case @TotalType
 when 0 then    /*按部門匯總*/
   'Select B.Dept_Name, A.* from SF_OrderDataTotal A left join ST_Department B on A.Dept_No=B.Dept_No
     WHERE ''' + @UserNo + '''=''admin'' OR B.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE User_No=''' + @UserNo + ''')
    ORDER BY B.Dept_No'
 when 1 then    /* 按人員匯總*/
   'SELECT ST_Department.Dept_Name , ST_Department.Dept_No,
       ST_Person.Person_Name, ST_Person.Person_No,
       SF_OrderDataTotal.*
   FROM ST_Department INNER JOIN
     ST_Person ON
     ST_Department.Dept_ID = ST_Person.Dept_ID RIGHT OUTER JOIN
     SF_OrderDataTotal ON
     ST_Person.Person_ID = SF_OrderDataTotal.Person_Id
   WHERE ''' + @UserNo + '''=''admin'' OR ST_Person.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE User_No=''' + @UserNo + ''')
   ORDER BY ST_Department.dept_No,ST_Person.Person_No'
 when 2 then    /*按食堂匯總*/
   'Select B.Area_Name, A.* from SF_OrderDataTotal A left join SF_OrderArea B on A.Area_No=B.Area_No'
 when 3 then    /* 按餐別*/
   'Select * from SF_OrderDataTotal'
end)
EXEC(@SQL)
GO

IF OBJECT_ID('dbo.SF_Order_pd_UnUseTotalQry_R') IS NOT NULL DROP PROCEDURE SF_Order_pd_UnUseTotalQry_R
GO
------訂餐未用餐數據匯總查詢
CREATE PROCEDURE dbo.SF_Order_pd_UnUseTotalQry_R
@qryType INT,				--查詢類別
@Dept   VARCHAR(20),			--部門條件或員工編號 ,根據查詢類別來確定
@Repast VARCHAR(500),			--餐別條件
@Bdate DATETIME ,			--起始日期
@Edate DATETIME,		                --結束日期
@UserNo VARCHAR(40)
AS

SET NOCOUNT ON

DECLARE @str VARCHAR(5)
DECLARE @BgnDate VARCHAR(12)
DECLARE @EndDate VARCHAR(12)
DECLARE @SQL  VARCHAR(1000)
DECLARE @i  INT
DECLARE @j  INT

SET @BgnDate=CONVERT(VARCHAR(10),@BDate,120)
SET @EndDate=CONVERT(VARCHAR(10),@EDate,120)

/*創建 人員餐別表*/
IF object_ID('SF_OrderUnUseDataTotal') is not null DROP table SF_OrderUnUseDataTotal

SET @SQL=' Create Table SF_OrderUnUseDataTotal([Person_Id] [int] null'
SET @j=1
WHILE @j < LEN(@Repast)
BEGIN
	SET @i=CHARINDEX(',',@Repast,@j)
	IF @i>@j
	BEGIN
		SET @str=SUBSTRING(@Repast,@j,@i-@j)
		SET @j=@i+1
	END
	ELSE
	BEGIN
		SET @str=SUBSTRING(@Repast,@j,LEN(@Repast)-@j+1)
		SET @j=500
	END

	IF LEN(@Str)>0
	SET @SQL=@SQL+' , [R'+@str+'] [INT] DEFAULT 0'
END

SET @SQL=@SQL+')'
EXEC(@SQL)

/**插入訂餐未用餐人員***/

IF @qryType=0
INSERT INTO SF_OrderUnUseDataTotal([Person_Id])
SELECT DC_OrderData.Person_Id
FROM ST_Department INNER JOIN
ST_Person ON
ST_Department.Dept_ID = ST_Person.Dept_ID RIGHT OUTER JOIN
DC_OrderData ON
ST_Person.Person_ID = DC_OrderData.Person_Id
WHERE DC_OrderData.Data_type=0 --已訂未用餐
and ST_Department.Dept_No like @Dept
and CHARINDEX(Repast_No,@Repast)>0
and Order_Date <= @EndDate
and Order_Date >= @BgnDate

GROUP BY DC_OrderData.Person_Id

ELSE
INSERT INTO SF_OrderUnUseDataTotal([Person_Id]) Values(0)

/****匯總******/
SET @j=1
WHILE @j < len(@Repast)
BEGIN
	SET @i=CHARINDEX(',',@Repast,@j)
	IF @i>@j
	BEGIN
		SET @str=SUBSTRING(@Repast,@j,@i-@j)
		SET @j=@i+1
	END
	ELSE
	BEGIN
		SET @str=SUBSTRING(@Repast,@j,LEN(@Repast)-@j+1)
		SET @j=500
	END
	IF Len(@Str)>0
	BEGIN
		IF @qryType=0
		SET @SQL='Update SF_OrderUnUseDataTotal set R'+@str+' = '
		+' (Select Count(*) from DC_OrderData '
		+'  where DC_OrderData.Data_Type=0 and DC_OrderData.Repast_No = '    + CHAR(39) + @str + CHAR(39)
		+'  and   DC_OrderData.Order_Date <= '  + CHAR(39) + @EndDate + CHAR(39)
		+'  and   DC_OrderData.Order_Date >= '  + CHAR(39) + @BgnDate + CHAR(39)
		+'  and   DC_OrderData.Person_Id = SF_OrderUnUseDataTotal.Person_Id '
		+' )'
		IF @qryType=1
		SET @SQL='Update SF_OrderUnUseDataTotal set R'+@str+' = '
		+' (Select Count(*) FROM ST_Department INNER JOIN
		ST_Person ON
		ST_Department.Dept_ID = ST_Person.Dept_ID RIGHT OUTER JOIN
		DC_OrderData ON
		ST_Person.Person_ID = DC_OrderData.Person_Id'
		+'  where DC_OrderData.Data_Type=0 and ST_Department.Dept_No like '     + CHAR(39) + @Dept + CHAR(39)
		+'  and   DC_OrderData.Repast_No = '  + CHAR(39) + @str + CHAR(39)
		+'  and   DC_OrderData.Order_Date <= '+ CHAR(39) + @EndDate + CHAR(39)
		+'  and   DC_OrderData.Order_Date >= '+ CHAR(39) + @BgnDate + CHAR(39)
		+' )'
		+' where Person_Id = 0 '
		EXEC(@SQL)
	END
END

SELECT ST_Department.Dept_Name, ST_Department.Dept_No,
       	ST_Person.Person_Name, ST_Person.Person_No,SF_OrderUnUseDataTotal.*
	FROM ST_Department
	INNER JOIN ST_Person ON  ST_Department.Dept_ID = ST_Person.Dept_ID 
	RIGHT OUTER JOIN SF_OrderUnUseDataTotal ON ST_Person.Person_ID = SF_OrderUnUseDataTotal.Person_Id
   WHERE (@UserNo='admin' OR ST_Person.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE User_No=@UserNo))
	ORDER BY ST_Department.dept_No,ST_Person.Person_No
GO

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

------未用餐數據明細查詢
CREATE     PROCEDURE dbo.SF_Order_pd_UnUseDetailQry
@qryType INT,			--查詢類別
@Dept   VARCHAR(20),		--部門條件或員工編號 ,根據查詢類別來確定
@Repast VARCHAR(500),		--餐別條件
@Bdate DATETIME,		--起始日期
@Edate DATETIME			--結束日期
AS

set nocount on

declare @BgnDate varchar(12)
declare @EndDate varchar(12)

set @BgnDate=convert(varchar(10),@BDate,120)
set @EndDate=convert(varchar(10),@EDate,120)

TRUNCATE TABLE SF_OrderUnUseData

/*創建臨時表*/
IF object_ID('tempdb..#TempOderDate') is null
CREATE TABLE #TempOderDate([Person_Id] [int] NULL,[DS_Date] [varchar] (20) NULL,[Repast_No] [varchar] (10) NULL)
ELSE
TRUNCATE TABLE #TempOderDate

IF object_ID('tempdb..#TempConsume') is null
CREATE TABLE #TempConsume([Person_Id] [int] NULL ,[DS_Date] [varchar] (20) NULL,[Repast_No] [varchar] (10) NULL)
ELSE
TRUNCATE TABLE #TempConsume


/*訂餐數據*/
IF @qryType=1
INSERT INTO #TempOderDate(Person_Id, DS_Date, Repast_no)
SELECT SF_OrderData.Person_id, Order_Date, Repast_no
FROM ST_Department INNER JOIN
ST_Person ON
ST_Department.Dept_ID = ST_Person.Dept_ID RIGHT OUTER JOIN
SF_OrderData ON
ST_Person.Person_ID = SF_OrderData.Person_Id
WHERE  Dept_No Like @Dept
AND   charindex(Repast_no, @Repast) > 0
AND   Order_date <= @EndDate
AND   Order_date >= @BgnDate
ELSE
INSERT INTO #TempOderDate(Person_Id, DS_Date, Repast_no)
SELECT SF_OrderData.Person_id, Order_Date, Repast_no
FROM SF_OrderData LEFT JOIN
ST_Person ON
ST_Person.Person_ID = SF_OrderData.Person_Id
WHERE  Person_No =@Dept
AND   charindex(Repast_no, @Repast) > 0
AND   Order_date <= @EndDate
AND   Order_date >= @BgnDate


/*用餐數據*/
INSERT INTO #TempConsume(Person_Id, DS_Date, Repast_no)
SELECT Person_id, CONVERT(varchar(10),Consume_Time,120), Repast_no
FROM XF_Consume X
where Exists (Select 1 from #TempOderDate where #TempOderDate.Person_Id =X.Person_Id)
AND   Consume_Time <= @EndDate + ' 23:59:59'
AND   Consume_Time >= @BgnDate + ' 00:00:00'

/*訂餐未用餐數據*/
INSERT INTO SF_OrderUnUseData(Person_Id , Order_Date , Repast_No)
SELECT A.Person_Id , A.DS_Date , A. Repast_No FROM #TempOderDate A
WHERE not Exists(Select 1 from #TempConsume B
where A.DS_date=B.DS_date
and   A.Repast_No=B.repast_No
and   A.person_id=B.Person_id and charindex(Repast_no, @Repast) > 0)

/**/
Drop table #TempOderDate
Drop table #TempConsume


GO


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

------未用餐數據匯總查詢
CREATE        PROCEDURE dbo.SF_Order_pd_UnUseTotalQry
@qryType INT,				--查詢類別
@Dept   VARCHAR(20),			--部門條件或員工編號 ,根據查詢類別來確定
@Repast VARCHAR(500),			--餐別條件
@Bdate DATETIME ,			--起始日期
@Edate DATETIME		                --結束日期
AS

SET NOCOUNT ON

DECLARE @str VARCHAR(5)
DECLARE @BgnDate VARCHAR(12)
DECLARE @EndDate VARCHAR(12)
DECLARE @SQL  VARCHAR(1000)
DECLARE @i  INT
DECLARE @j  INT

SET @BgnDate=CONVERT(VARCHAR(10),@BDate,120)
SET @EndDate=CONVERT(VARCHAR(10),@EDate,120)

/*創建 人員餐別表*/
IF object_ID('SF_OrderUnUseDataTotal') is not null
DROP table SF_OrderUnUseDataTotal

SET @SQL=' Create Table SF_OrderUnUseDataTotal([Person_Id] [int] null'
SET @j=1
WHILE @j < LEN(@Repast)
BEGIN
  SET @i=CHARINDEX(',',@Repast,@j)
  IF @i>@j
  BEGIN
    SET @str=SUBSTRING(@Repast,@j,@i-@j)
    SET @j=@i+1
  END
  ELSE
  BEGIN
    SET @str=SUBSTRING(@Repast,@j,LEN(@Repast)-@j+1)
    SET @j=500
  END

 IF LEN(@Str)>0
   SET @SQL=@SQL+' , [R'+@str+'] [INT] DEFAULT 0'
END

SET @SQL=@SQL+')'
EXEC(@SQL)

/**插入未用餐人員***/
IF @qryType=0
  INSERT INTO SF_OrderUnUseDataTotal([Person_Id])
  SELECT SF_OrderUnUseData.Person_Id
  FROM ST_Department INNER JOIN
  ST_Person ON
  ST_Department.Dept_ID = ST_Person.Dept_ID RIGHT OUTER JOIN
  SF_OrderUnUseData ON
  ST_Person.Person_ID = SF_OrderUnUseData.Person_Id
  WHERE ST_Department.Dept_No like @Dept
  and   CHARINDEX(Repast_No,@Repast)>0
  and   Order_Date <= @EndDate
  and   Order_Date >= @BgnDate
  
  GROUP BY SF_OrderUnUseData.Person_Id

ELSE
  INSERT INTO SF_OrderUnUseDataTotal([Person_Id]) Values(0)

/****匯總******/
SET @j=1
WHILE @j < len(@Repast)
BEGIN
  SET @i=CHARINDEX(',',@Repast,@j)
  IF @i>@j
  BEGIN
    SET @str=SUBSTRING(@Repast,@j,@i-@j)
    SET @j=@i+1
  END
  ELSE
  BEGIN
    SET @str=SUBSTRING(@Repast,@j,LEN(@Repast)-@j+1)
    SET @j=500
  end

  IF Len(@Str)>0
  BEGIN
  IF @qryType=0
    SET @SQL='Update SF_OrderUnUseDataTotal set R'+@str+' = '
    +' (Select Count(*) from SF_OrderUnUseData '
    +'  where SF_OrderUnUseData.Repast_No = '    + CHAR(39) + @str + CHAR(39)
    +'  and   SF_OrderUnUseData.Order_Date <= '  + CHAR(39) + @EndDate + CHAR(39)
    +'  and   SF_OrderUnUseData.Order_Date >= '  + CHAR(39) + @BgnDate + CHAR(39)
    +'  and   SF_OrderUnUseData.Person_Id = SF_OrderUnUseDataTotal.Person_Id '
    +' )'
  IF @qryType=1
    SET @SQL='Update SF_OrderUnUseDataTotal set R'+@str+' = '
    +' (Select Count(*) FROM ST_Department INNER JOIN
    ST_Person ON
    ST_Department.Dept_ID = ST_Person.Dept_ID RIGHT OUTER JOIN
    SF_OrderUnUseData ON
    ST_Person.Person_ID = SF_OrderUnUseData.Person_Id'
    +'  where ST_Department.Dept_No like '     + CHAR(39) + @Dept + CHAR(39)
    +'  and   SF_OrderUnUseData.Repast_No = '  + CHAR(39) + @str + CHAR(39)
    +'  and   SF_OrderUnUseData.Order_Date <= '+ CHAR(39) + @EndDate + CHAR(39)
    +'  and   SF_OrderUnUseData.Order_Date >= '+ CHAR(39) + @BgnDate + CHAR(39)
    +' )'
    +' where Person_Id = 0 '

  EXEC(@SQL)
END
end


GO




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

------增補消費,扣除未用餐
CREATE   PROCEDURE dbo.SF_Order_pd_UnUseQry
@Bdate DATETIME,		--起始日期
@Edate DATETIME			--結束日期
AS

set nocount on 

declare @BgnDate varchar(12)
declare @EndDate varchar(12)
  
set @BgnDate=convert(varchar(10),@BDate,120)
set @EndDate=convert(varchar(10),@EDate,120)

/**插入手工消費***/
INSERT INTO XF_Consume(Person_id,System_No,Consume_Fund,Consume_Copy,Consume_Mode,Consume_Time,Repast_No,Moc_No,Data_Type)
                SELECT Person_id,'SF',isnull(Price,0),1,1,Order_Date,SF_OrderUnUsedata.Repast_No,'DC0001',1
  from SF_OrderUnUsedata left join SF_Repast on SF_OrderUnUsedata.Repast_No=SF_Repast.Repast_No
  where  Order_Date<=@EndDate
  and    Order_Date>=@BgnDate

/**更新余額**/
UPDATE ST_Card SET 
  Card_balance = Card_balance - A.Fund ,
  Subsidy_Fund = Subsidy_Fund - A.Fund 
FROM 
 (SELECT Person_id,Sum(isnull(Price,0)) as Fund
  from SF_OrderUnUsedata left join SF_Repast on SF_OrderUnUsedata.Repast_No=SF_Repast.Repast_No
  where  Order_Date<=@EndDate
  and    Order_Date>=@BgnDate
  Group by Person_Id) A
WHERE ST_Card.Person_Id=A.Person_Id

/**刪除已扣除的明細**/
DELETE FROM  SF_OrderUnUsedata
  WHERE  Order_Date<=@EndDate
  and    Order_Date>=@BgnDate 

GO

IF OBJECT_ID('SF_fn_IsOrderMeal') IS NOT NULL
  DROP FUNCTION SF_fn_IsOrderMeal 
GO

CREATE FUNCTION dbo.SF_fn_IsOrderMeal 
	(@PersonID INT, 
    @RepastNo VARCHAR(20),
    @Date DATETIME)
RETURNS BIT
AS
BEGIN
   DECLARE @Return BIT,@CurDate VARCHAR(10),@TimeBgn VARCHAR(5),@TimeEnd VARCHAR(5),@CurTime VARCHAR(5)
   SET @CurTime=CONVERT(VARCHAR(5),@Date,108)  
   SELECT @TimeBgn=Bgn_Time,@TimeEnd=End_Time FROM SF_Repast
    WHERE Repast_No=@RepastNo
   --跨天餐別在零晨消費,訂餐日期應為前一天
   IF (@TimeBgn>@TimeEnd) AND (@CurTime<=@TimeEnd)
      SET @CurDate=CONVERT(VARCHAR(10),@Date-1,120)
   ELSE
      SET @CurDate=CONVERT(VARCHAR(10),@Date,120)   
   IF EXISTS(SELECT Person_ID FROM SF_OrderData 
              WHERE Person_ID=@PersonID AND Order_Date=@CurDate 
                AND Repast_No=@RepastNo
            )      
      SET @Return = 1
   ELSE
      SET @Return = 0
   RETURN @Return
END
GO

IF OBJECT_ID('dbo.SF_Order_pd_OrderDetailQry_R') IS NOT NULL DROP PROCEDURE SF_Order_pd_OrderDetailQry_R
GO
--訂餐數據明細
CREATE  PROCEDURE dbo.SF_Order_pd_OrderDetailQry_R
@qryType INT,				--查詢類別
@Dept VARCHAR(20),			--部門條件或員工編號 ,根據查詢類別來確定
@Repast VARCHAR(500),			--餐別條件
@DateBgn DATETIME,			--起始日期
@DateEnd DATETIME,			--結束日期
@UserNo VARCHAR(40)
AS

set nocount on

Declare @BgnDate varchar(10),@EndDate varchar(10)
SET @BgnDate = CONVERT(CHAR(10),  @DateBgn, 120)
SET @EndDate = CONVERT(CHAR(10),  @DateEnd, 120)

/*訂餐數據*/
IF @qryType=1 
 	/** 按部門 + 日期 + 餐別**/
	SELECT ST_Department.Dept_Name,ST_Person.Person_No,ST_Person.Person_Name,
	O.Order_Date,O.Order_Time,O.Repast_No,R.Repast_Name,O.Moc_No_DC[Moc_No_DC] 
	FROM DC_OrderData O
	LEFT JOIN SF_Repast R On O.Repast_No=R.Repast_No
	LEFT OUTER JOIN ST_Department 
	INNER JOIN ST_Person ON ST_Department.Dept_ID = ST_Person.Dept_ID ON O.Person_ID = ST_Person.Person_ID
	WHERE --(Data_Type=0 OR Data_Type=1) 
	 Dept_No Like @Dept
   AND (@UserNo='admin' OR ST_Person.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE User_No=@UserNo))
	AND charindex(O.Repast_no, @Repast) > 0
	AND Order_date <= @EndDate
	AND Order_date >= @BgnDate
	ORDER BY ST_Department.Dept_No,ST_Person.Person_No,O.Order_Date
ELSE  
        /** 按人員 + 日期 + 餐別**/
	SELECT ST_Department.Dept_Name,ST_Person.Person_No,ST_Person.Person_Name,
	O.Order_Date,O.Order_Time,O.Repast_No,R.Repast_Name,O.Moc_No_DC[Moc_No_DC] 
	FROM DC_OrderData O
	LEFT JOIN SF_Repast R On O.Repast_No=R.Repast_No
	LEFT OUTER JOIN ST_Department 
	INNER JOIN ST_Person ON ST_Department.Dept_ID = ST_Person.Dept_ID ON O.Person_ID = ST_Person.Person_ID
	WHERE --(Data_Type=0 OR Data_Type=1) 
	 ST_Person.Person_No =@Dept
   AND (@UserNo='admin' OR ST_Person.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE User_No=@UserNo))
	AND charindex(O.Repast_no, @Repast) > 0
	AND Order_date <= @EndDate
	AND Order_date >= @BgnDate
	ORDER BY ST_Department.Dept_No,ST_Person.Person_No,O.Order_Date
GO

IF OBJECT_ID('dbo.SF_Order_pd_OrderTotalQry_R') IS NOT NULL DROP PROCEDURE SF_Order_pd_OrderTotalQry_R
GO
--訂餐數據匯總過程：
CREATE PROCEDURE  dbo.SF_Order_pd_OrderTotalQry_R
@TotalType INT,
@Dept VARCHAR(20),
@Repast VARCHAR(500),
@BDate DATETIME,
@EDate DATETIME,
@UserNo VARCHAR(40)
AS
set nocount on

Declare @str   varchar(5),@BgnDate varchar(12),@EndDate varchar(12),@SQL   varchar(1000)
Declare @i  int, @j  int

set @BgnDate=convert(varchar(10),@BDate,120)
set @EndDate=convert(varchar(10),@EDate,120)

/*創建 人員餐別表*/
IF object_ID('SF_OrderDataTotal') is not null
DROP table SF_OrderDataTotal

SELECT @SQL=
(case @TotalType
when 0 then    /*按部門匯總*/
' Create Table SF_OrderDataTotal([Dept_No] [varchar] (20) null '
when 1 then    /* 按人員匯總*/
' Create Table SF_OrderDataTotal([Person_Id] [int] null '
when 2 then    /*按食堂匯總*/
' Create Table SF_OrderDataTotal([Area_No] [varchar] (10) null '
when 3 then    /* 按餐別*/
' Create Table SF_OrderDataTotal([Person_Id] [int] null '
end)

set @j=1
while @j < len(@Repast)
begin
set @i=charindex(',',@Repast,@j)
if @i>@j
begin
set @str=substring(@Repast,@j,@i-@j)
set @j=@i+1
end
else
begin
set @str=substring(@Repast,@j,Len(@Repast)-@j+1)
set @j=500
end

if Len(@Str)>0
set @SQL=@SQL+' , [R'+@str+'] [int] default 0'
end

set @SQL=@SQL+')' 
Exec(@SQL)

/**收集訂餐數據**/
IF object_ID('tempdb..#TempOrderData') is not null
DROP table #TempOrderData

SELECT ST_Department.Dept_No, ST_Person.Person_No,
DC_OrderData.Person_ID, DC_OrderData.Order_date,
DC_OrderData.Repast_No, ST_MOC.Area_No, XF_Area.Area_Name
INTO  #TempOrderData
FROM ST_MOC RIGHT OUTER JOIN
DC_OrderData ON
ST_MOC.Moc_No = DC_OrderData.Moc_No_DC
Left JOIN XF_Area ON ST_MOC.Area_No = XF_Area.Area_No 
LEFT OUTER JOIN ST_Department INNER JOIN ST_Person ON ST_Department.Dept_ID = ST_Person.Dept_ID ON
DC_OrderData.Person_ID = ST_Person.Person_ID
WHERE  ST_Department.Dept_No like @Dept
AND   charindex(Repast_no, @Repast) > 0
AND   Order_date <= @EndDate
AND   Order_date >= @BgnDate

/**插入記錄**/
SELECT @SQL=
(case @TotalType
when 0 then    /*按部門匯總*/
' INSERT INTO SF_OrderDataTotal(Dept_No) '
+'  select Dept_No from #TempOrderData Group by Dept_No'
when 1 then    /* 按人員匯總*/
' INSERT INTO SF_OrderDataTotal(Person_Id) '
+'  select Person_Id from #TempOrderData Group by Person_Id'
when 2 then    /*按食堂匯總*/
' INSERT INTO SF_OrderDataTotal(Area_No) '
+'  select Area_No from #TempOrderData Group by Area_No'
when 3 then    /* 按餐別*/
' INSERT INTO SF_OrderDataTotal(Person_Id) Values(0)'

end)
EXEC(@SQL)

/**匯總**/
set @j=1
while @j < len(@Repast)
begin
set @i=charindex(',',@Repast,@j)
if @i>@j
begin
set @str=substring(@Repast,@j,@i-@j)
set @j=@i+1
end
else
begin
set @str=substring(@Repast,@j,Len(@Repast)-@j+1)
set @j=500
end

if Len(@Str)>0
begin
SELECT @SQL=
(case @TotalType
when 0 then    /*按部門匯總*/
'Update SF_OrderDataTotal set R'+@str+' = '
+' (Select Count(*) from #TempOrderData '
+'  where #TempOrderData.Dept_No = SF_OrderDataTotal.Dept_No'
+'  and   #TempOrderData.Repast_No = '+char(39)+@str+char(39)
+')'
when 1 then    /* 按人員匯總*/
'Update SF_OrderDataTotal set R'+@str+' = '
+' (Select Count(*) from #TempOrderData '
+'  where #TempOrderData.Person_Id = SF_OrderDataTotal.Person_Id'
+'  and   #TempOrderData.Repast_No = '+char(39)+@str+char(39)
+')'
when 2 then    /*按食堂匯總*/
'Update SF_OrderDataTotal set R'+@str+' = '
+' (Select Count(*) from #TempOrderData '
+'  where #TempOrderData.Area_No = SF_OrderDataTotal.Area_No '
+'  and   #TempOrderData.Repast_No = '+char(39)+@str+char(39)
+')'
when 3 then    /* 按餐別*/
'Update SF_OrderDataTotal set R'+@str+' = '
+' (Select Count(*) from #TempOrderData '
+'  where #TempOrderData.Repast_No = '+char(39)+@str+char(39)
+')'
end)
EXEC(@SQL)
end
end

/**返回**/
SELECT @SQL=
(case @TotalType
when 0 then    /*按部門匯總*/
'Select B.Dept_Name, A.* from SF_OrderDataTotal A left join ST_Department B on A.Dept_No=B.Dept_No
WHERE ''' + @UserNo + '''=''admin'' OR B.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE User_No=''' + @UserNo + ''')
ORDER BY B.Dept_No'
when 1 then    /* 按人員匯總*/
'SELECT ST_Department.Dept_Name , ST_Department.Dept_No,
ST_Person.Person_Name, ST_Person.Person_No,
SF_OrderDataTotal.*
FROM ST_Department INNER JOIN
ST_Person ON
ST_Department.Dept_ID = ST_Person.Dept_ID RIGHT OUTER JOIN
SF_OrderDataTotal ON
ST_Person.Person_ID = SF_OrderDataTotal.Person_Id
WHERE ''' + @UserNo + '''=''admin'' OR ST_Person.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE User_No=''' + @UserNo + ''')
ORDER BY ST_Department.dept_No,ST_Person.Person_No'
when 2 then    /*按食堂匯總*/
'Select B.Area_Name, A.* from SF_OrderDataTotal A left join XF_Area B on A.Area_No=B.Area_No'
when 3 then    /* 按餐別*/
'Select * from SF_OrderDataTotal'
end)
EXEC(@SQL)
GO

IF OBJECT_ID('dbo.SF_Order_pd_UnUseDetailQry_R') IS NOT NULL DROP PROCEDURE SF_Order_pd_UnUseDetailQry_R
GO
--訂餐未用餐明細
CREATE  PROCEDURE dbo.SF_Order_pd_UnUseDetailQry_R
@qryType INT,				--查詢類別
@Dept VARCHAR(20),			--部門條件或員工編號 ,根據查詢類別來確定
@Repast VARCHAR(500),			--餐別條件
@DateBgn DATETIME,			--起始日期
@DateEnd DATETIME,			--結束日期
@UserNo VARCHAR(40)
AS

Declare @BgnDate varchar(10),@EndDate varchar(10)
SET @BgnDate = CONVERT(CHAR(10),  @DateBgn, 120)
SET @EndDate = CONVERT(CHAR(10),  @DateEnd, 120)

/*訂餐數據*/
IF @qryType=1 
 	/** 按部門 + 日期 + 餐別**/
	SELECT ST_Department.Dept_Name,ST_Person.Person_No,ST_Person.Person_Name,
	O.Order_Date,O.Order_Time,O.Repast_No,R.Repast_Name,O.Moc_No_DC[Moc_No_DC] 
	FROM DC_OrderData O
	LEFT JOIN SF_Repast R On O.Repast_No=R.Repast_No
	LEFT OUTER JOIN ST_Department 
	INNER JOIN ST_Person ON ST_Department.Dept_ID = ST_Person.Dept_ID ON O.Person_ID = ST_Person.Person_ID
	WHERE Data_Type=0
   AND (@UserNo='admin' OR ST_Person.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE User_No=@UserNo))
	AND Dept_No Like @Dept
	AND charindex(O.Repast_no, @Repast) > 0
	AND Order_date <= @EndDate
	AND Order_date >= @BgnDate
	ORDER BY ST_Department.Dept_No,ST_Person.Person_No,O.Order_Date
ELSE  
        /** 按人員 + 日期 + 餐別**/
	SELECT ST_Department.Dept_Name,ST_Person.Person_No,ST_Person.Person_Name,
	O.Order_Date,O.Order_Time,O.Repast_No,R.Repast_Name,O.Moc_No_DC[Moc_No_DC] 
	FROM DC_OrderData O
	LEFT JOIN SF_Repast R On O.Repast_No=R.Repast_No
	LEFT OUTER JOIN ST_Department 
	INNER JOIN ST_Person ON ST_Department.Dept_ID = ST_Person.Dept_ID ON O.Person_ID = ST_Person.Person_ID
	WHERE Data_Type=0
	AND ST_Person.Person_No =@Dept
   AND (@UserNo='admin' OR ST_Person.Dept_ID IN (SELECT Dept_ID FROM ST_DeptRight WHERE User_No=@UserNo))
	AND charindex(O.Repast_no, @Repast) > 0
	AND Order_date <= @EndDate
	AND Order_date >= @BgnDate
	ORDER BY ST_Department.Dept_No,ST_Person.Person_No,O.Order_Date
GO

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

--2009.11.5 實時訂餐系統增加
CREATE PROCEDURE dbo.XF_pd_CardOrder
   (@OrderDate   DATETIME,    --訂餐日期
    @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)<90
   --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 <> 3 --正常消費或假寫卡或脫機消費
   BEGIN
      IF (@Options = 0) OR (@Options = 4) SET @ConsumeTime=GETDATE() --非脫機記錄取數據庫的當前時間
      IF (@Options = 0) OR (@Options = 2) SET @DataType=0 --聯機消費或脫機消費
      ELSE SET @DataType=8 --假寫卡
      IF @DataType=8 RETURN --假寫卡記錄,暫時不處理    
      DECLARE @DownloadTime DATETIME,@IDKEYDC INT,@OrderTime DATETIME,@DataTypeDC INT
      IF (@Options = 5) OR (@Options = 2) --脫機記錄
         SET @DownloadTime=GETDATE()
      --1.更新余額和補貼金額
      UPDATE ST_Card 
         SET @DBBalance=Card_Balance-@ConsumeFund,Card_Balance=@DBBalance
       WHERE Person_ID=@PersonID
      --處理已用餐但未訂餐的情況
      IF @DownloadTime IS NOT NULL --脫機情況
      BEGIN
         --判斷訂餐記錄是否存在
         SELECT @IDKEYDC=ID_KEY,@OrderTime=Consume_Time,@DataTypeDC=Data_Type FROM DC_OrderData 
          WHERE Person_ID=@PersonID AND Order_Date=CONVERT(VARCHAR(10),@OrderDate,121)
            AND Repast_No=@RepastNo
         --訂餐記錄如果存在就更新信息 
         IF @IDKEYDC IS NOT NULL
         BEGIN
            IF @DataTypeDC<>2 RETURN --如果是重復的訂餐記錄就不處理
            --1.更新訂餐表
            UPDATE DC_OrderData SET Order_Time=@ConsumeTime,Moc_No_DC=@MocNo,Repast_No=@RepastNo,Data_Type=1
             WHERE ID_KEY=@IDKEYDC
            --2.更新消費臨時表中的記錄
            UPDATE XF_ConsumeTemp 
               SET @IDKEYDC=Is_Commited, 
                   Consume_Fund=@ConsumeFund,Before_Fund=@BeforeFund,Card_Balance=@Balance,
                   DB_Balance=@DBBalance,Consume_Copy=@Copies 
             WHERE Person_ID=@PersonID AND Consume_Time=@OrderTime
            --3.更新消費表中的記錄
            IF @IDKEYDC<>0 --當前消費記錄已經提交時,需要再次更新消費表
            BEGIN
               UPDATE XF_Consume 
                  SET Consume_Fund=@ConsumeFund,Before_Fund=@BeforeFund,Card_Balance=@Balance,
                      DB_Balance=@DBBalance,Consume_Copy=@Copies 
                WHERE Person_ID=@PersonID AND Consume_Time=@OrderTime
            END
            RETURN --訂餐記錄已存在就不用插入新的記錄 
         END
      END
      --結束
      --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,0,0,@RepastNo,
             -1,@MocNo,@HardNo,@Balance,@DBBalance,0,@DataType,0,@DownloadTime
      --3.插入訂餐記錄
      INSERT INTO DC_OrderData(Person_ID,Order_Date,Order_Time,Repast_No,Moc_No_DC,Data_Type,Download_Time)
      SELECT @PersonID,CONVERT(VARCHAR(10),@OrderDate,121),@ConsumeTime,@RepastNo,@MocNo,0,@DownloadTime
   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,0,0,@RepastNo,
             -1,@MocNo,@HardNo,@DBBalance,@Balance,0,0,GETDATE()
   END
GO


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

CREATE PROCEDURE dbo.XF_pd_CardUseMeal
   (@IsCheckOrder BIT,         --是否需要檢查訂餐表
    @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      
   IF @Options = 3 RETURN --不處理有卡無人的情況
   DECLARE @DataType TINYINT,@DownloadTime DATETIME,@OrderTime DATETIME,@IDKEYDC INT,@DataTypeDC INT   
   --正常消費或假寫卡或脫機消費   
   IF (@Options = 0) OR (@Options = 4) SET @ConsumeTime=GETDATE() --非脫機記錄取數據庫的當前時間
   IF (@Options = 0) OR (@Options = 2) SET @DataType=0 --聯機消費或脫機消費
   ELSE SET @DataType=8 --假寫卡
   IF @DataType=8 RETURN --不處理假寫卡的情況
   --非假寫卡記錄   
   IF (@Options = 5) OR (@Options = 2) --脫機記錄
      SET @DownloadTime=GETDATE()
   SELECT @OrderTime=Order_Time,@IDKEYDC=ID_KEY,@DataTypeDC=Data_Type FROM DC_OrderData 
    WHERE Person_ID=@PersonID AND Order_Date=CONVERT(VARCHAR(10),@ConsumeTime,121)
      AND Repast_No=@RepastNo
   --有訂餐記錄存在
   IF @OrderTime IS NOT NULL
   BEGIN
      IF @DataTypeDC<>0 RETURN --如果是重復的訂餐消費記錄就不處理
      --1.更新訂餐表
      UPDATE DC_OrderData SET Consume_Time=@ConsumeTime,Moc_No_SF=@MocNo,Data_Type=1
       WHERE ID_KEY=@IDKEYDC
      --2.更新消費臨時表中的記錄
      UPDATE XF_ConsumeTemp 
         SET @IDKEYDC=Is_Commited, 
             Consume_Time=@ConsumeTime,Moc_No=@MocNo,Download_Time=@DownloadTime,COM_No=@HardNo         
       WHERE Person_ID=@PersonID AND Consume_Time=@OrderTime
      --3.更新消費表中的記錄
      IF @IDKEYDC<>0 --當前消費記錄已經提交時,需要再次更新消費表
      BEGIN
         UPDATE XF_Consume 
            SET Consume_Time=@ConsumeTime,Moc_No=@MocNo,Download_Time=@DownloadTime,COM_No=@HardNo
          WHERE Person_ID=@PersonID AND Consume_Time=@OrderTime
      END
   END
   ELSE IF @IsCheckOrder=1 --有用餐記錄沒有訂餐記錄需要補訂餐記錄
   BEGIN
      --1.插入消費記錄
      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,0,0,0,0,0,@RepastNo,
             -1,@MocNo,@HardNo,@Balance,@DBBalance,0,@DataType,0,@DownloadTime
      --2.插入訂餐記錄
      INSERT INTO DC_OrderData(Person_ID,Order_Date,Order_Time,Repast_No,Moc_No_DC,Data_Type,Download_Time,Consume_Time,Moc_No_SF)
      SELECT @PersonID,CONVERT(VARCHAR(10),@ConsumeTime,121),@ConsumeTime,@RepastNo,@MocNo,2,@DownloadTime,@ConsumeTime,@MocNo
   END
GO

IF OBJECT_ID('SF_fn_HasOrderData') IS NOT NULL
  DROP FUNCTION SF_fn_HasOrderData 
GO

CREATE FUNCTION dbo.SF_fn_HasOrderData 
	(@PersonID INT, 
    @RepastNo VARCHAR(20),
    @Date DATETIME)
RETURNS BIT
AS
BEGIN
   DECLARE @Return BIT,@CurDate VARCHAR(10)
   SET @CurDate=CONVERT(VARCHAR(10),@Date,121)   
   IF EXISTS(SELECT Person_ID FROM DC_OrderData 
              WHERE Person_ID=@PersonID AND Order_Date=@CurDate 
                AND Repast_No=@RepastNo AND Data_Type<>1
            )      
      SET @Return = 1
   ELSE
      SET @Return = 0
   RETURN @Return
END
GO

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

--撤銷訂餐
CREATE  PROCEDURE dbo.XF_pd_CancelOrder
   (@OrderDate   DATETIME,    --訂餐日期
    @PersonID    INT,         --人員標識號
    @CardNo      VARCHAR(20), --卡號
    @SystemNo    VARCHAR(20), --子系統編號
    @Balance     MONEY,       --卡余額(本次消費后的余額)
    @DBBalance   MONEY,       --庫余額(本次消費后的余額)
    @BeforeFund  MONEY,       --打折前的消費金額
    @ConsumeFund MONEY,       --打折后的消費金額
    @Copies      INT,         --消費份數
    @RepastNo    VARCHAR(20), --餐別編號
    @HardNo      VARCHAR(20), --硬件編號
    @MocNo       VARCHAR(20), --終端機編號
    @UserName    Varchar(30)  --操作人員

   )
AS   
   DECLARE @Is_Commited int,@IDKEYDC INT
   --0.更新IC卡余額
   UPDATE ST_Card SET Update_Time=GETDATE(),IC_Balance=@Balance,Card_Balance=@Balance WHERE Person_ID=@PersonID 
   --1.插入訂餐日志記錄
   INSERT INTO DC_OrderDataLog(
      Person_ID,Order_Date,Order_Time,Repast_No,Moc_No_DC,
      Data_Type,Download_Time,Consume_Time,Moc_No_SF,Remark,
      Card_Balance,DB_Balance,Consume_Fund,Del_Date,User_Name) 
	SELECT Person_ID,Order_Date,Order_Time,Repast_No,Moc_No_DC,
	       Data_Type,Download_Time,Consume_Time,Moc_No_SF,Remark,
	       @Balance,@DBBalance,@ConsumeFund,GETDATE(),@UserName 
     FROM DC_OrderData 
	 WHERE Person_ID=@PersonID AND Order_Time=@OrderDate AND Repast_No=@RepastNo      
   --2.刪除訂餐記錄
   DELETE DC_OrderData 
    WHERE Person_ID=@PersonID AND Order_Time=@OrderDate 
      AND Repast_No=@RepastNo AND Data_Type=0
   SELECT TOP 1 @IDKEYDC=ID_KEY,@Is_Commited=Is_Commited FROM XF_ConsumeTemp 
    WHERE Person_ID=@PersonID AND Consume_Time=@OrderDate AND Repast_No=@RepastNo 
   --訂餐記錄如果存在就更新信息 
   IF @Is_Commited IS NOT NULL
   BEGIN
      --3.插入消費記錄
      DELETE XF_ConsumeTemp WHERE ID_KEY=@IDKEYDC
   END
   ELSE
      SET @Is_Commited=1  
   --    
   IF @Is_Commited=1 
   BEGIN      
      DELETE XF_Consume WHERE Person_ID=@PersonID AND Consume_Time=@OrderDate AND Repast_No=@RepastNo 
   END  
GO
