-- =============================================
-- 巡更系統函數：巡更人員日報的增補巡更事件時長處理
-- =============================================
IF OBJECT_ID('dbo.XG_fn_AddTimeLen') IS NOT NULL
   DROP FUNCTION dbo.XG_fn_AddTimeLen
GO

CREATE FUNCTION dbo.XG_fn_AddTimeLen
	      (@PersonID INT,
         @Route_ID INT,
         @SiteID INT,
         @WorkDate VARCHAR(10),
         @PlanTime VARCHAR(5)
         )
RETURNS DATETIME
AS
BEGIN
   DECLARE @Date DATETIME,@TimeLen INT
       SET @Date=@WorkDate+' '+@PlanTime
   SELECT @TimeLen=ISNULL(SUM(Deal_TimeLen), 0)
    FROM XG_SupplyEvent 
    WHERE Person_ID=@PersonID AND Route_ID=@Route_ID
      AND DATEADD(DAY,After_Days,Supply_Date)<@Date     
     SET @Date=DATEADD(n, @TimeLen, @Date)
   RETURN @Date
END
GO


-- =============================================
-- 巡更系統函數：巡更路線日報的增補巡更事件時長處理
-- =============================================
IF OBJECT_ID('dbo.XG_fn_RouteAddTimeLen') IS NOT NULL
   DROP FUNCTION dbo.XG_fn_RouteAddTimeLen
GO

CREATE FUNCTION dbo.XG_fn_RouteAddTimeLen
      	(@RouteID INT,
         @SiteID INT,
         @WorkDate VARCHAR(10),
         @PlanTime VARCHAR(5)
         )
RETURNS DATETIME
AS
BEGIN
   DECLARE @Date DATETIME,@TimeLen INT
       SET @Date=@WorkDate+' '+@PlanTime
   SELECT @TimeLen=ISNULL(SUM(Deal_TimeLen), 0) FROM XG_SupplyEvent 
    WHERE Route_ID=@RouteID
      AND DATEADD(DAY,After_Days,Supply_Date)<@Date
     SET @Date=DATEADD(n, @TimeLen, @Date)
   RETURN @Date
END
GO

-- =============================================
-- 巡更系統視圖
-- =============================================
-- =============================================
-- 視圖：人員信息與路線，增補巡更記錄
-- 此視圖作為以下異常情況所關聯的視圖
-- =============================================
IF OBJECT_ID('dbo.V_XGPsnPatrol') IS NOT NULL 
  DROP VIEW dbo.V_XGPsnPatrol
GO

CREATE VIEW dbo.V_XGPsnPatrol
AS
Select A.ID_Key,A.Person_ID,A.Route_ID,A.Site_ID,A.Supply_Date,A.Supply_Time,A.Reason,
A.Audit_Man,B.Person_No,B.Person_Name,B.Station_ID,D.Site_NO,D.Site_Name,A.Event_NO,E.Event_Name,
(CASE WHEN ISNULL(C.Route_No,'')=''   THEN '0000' ELSE C.Route_No END)AS Route_No,
(CASE WHEN ISNULL(C.Route_Name,'')='' THEN '休息' ELSE C.Route_Name END)AS Route_Name,
A.After_Days,F.Station_Name
FROM dbo.XG_SupplyPatrol A
LEFT JOIN dbo.XG_Person B ON A.Person_ID=B.Person_ID
LEFT JOIN dbo.XG_Route  C ON A.Route_ID=C.Route_ID
LEFT JOIN dbo.XG_Site   D ON A.Site_ID=D.Site_ID
LEFT JOIN dbo.XG_Event  E ON A.Event_NO=E.Event_No
LEFT JOIN dbo.XG_PatrolStation F ON B.Station_ID=F.Station_ID
GO


-- =============================================
-- 視圖：人員信息與路線，增補巡更事件
-- 此視圖作為以下異常情況所關聯的視圖
-- =============================================
IF OBJECT_ID('dbo.V_XGPsnEvent') IS NOT NULL 
  DROP VIEW dbo.V_XGPsnEvent
GO

CREATE VIEW dbo.V_XGPsnEvent
AS
Select A.ID_Key,A.Person_ID,A.Route_ID,A.Site_ID,A.Supply_Date,A.Reason,
A.Audit_Man,A.Event_No,A.Deal_TimeLen,A.After_Days,
B.Person_No,B.Person_Name,B.Station_ID,D.Site_NO,D.Site_Name,
C.Route_No,C.Route_Name,E.Event_Name,F.Station_Name
FROM dbo.XG_SupplyEvent A
LEFT JOIN dbo.XG_Person B ON A.Person_ID=B.Person_ID
LEFT JOIN dbo.XG_Route  C ON A.Route_ID=C.Route_ID
LEFT JOIN dbo.XG_Site   D ON A.Site_ID=D.Site_ID
LEFT JOIN dbo.XG_Event  E ON A.Event_NO=E.Event_NO
LEFT JOIN dbo.XG_PatrolStation F ON B.Station_ID=F.Station_ID
GO

-- =============================================
-- 視圖：人員信息與路線，排班班次
-- 此視圖作為以下異常情況所關聯的視圖
-- =============================================
IF OBJECT_ID('dbo.V_XGPsnWork') IS NOT NULL 
  DROP VIEW dbo.V_XGPsnWork
GO

CREATE VIEW dbo.V_XGPsnWork
AS
Select A.ID_Key,A.Person_ID,B.Person_No,B.Person_Name,B.Station_ID,A.Work_Date,
(CASE WHEN ISNULL(C.Route_No,'')='' THEN '0000' ELSE C.Route_No END)AS Route_No,
(CASE WHEN ISNULL(C.Route_Name,'')='' THEN '休息' ELSE C.Route_Name END)AS Route_Name,
A.Route_ID,F.Station_Name
FROM dbo.XG_Work A
LEFT JOIN dbo.XG_Person B ON A.Person_ID=B.Person_ID
LEFT JOIN dbo.XG_Route  C ON A.Route_ID=C.Route_ID
LEFT JOIN dbo.XG_PatrolStation F ON B.Station_ID=F.Station_ID
GO

-- =============================================
-- 視圖：人員信息與路線，調整班次
-- 此視圖作為以下異常情況所關聯的視圖
-- =============================================
IF OBJECT_ID('dbo.V_XGPsnAdjust') IS NOT NULL 
  DROP VIEW dbo.V_XGPsnAdjust
GO

CREATE VIEW dbo.V_XGPsnAdjust
AS
Select A.ID_Key,A.Person_ID,B.Person_No,B.Person_Name,B.Station_ID,A.Adjust_Date,
(CASE WHEN ISNULL(C.Route_No,'')='' THEN '0000' ELSE C.Route_No END)AS Route_No,
(CASE WHEN ISNULL(C.Route_Name,'')='' THEN '休息' ELSE C.Route_Name END)AS Route_Name,
A.Route_ID,A.Reason,A.Audit_Man,F.Station_Name
FROM dbo.XG_AdjustWork A
LEFT JOIN dbo.XG_Person B ON A.Person_ID=B.Person_ID
LEFT JOIN dbo.XG_Route  C ON A.Route_ID=C.Route_ID
LEFT JOIN dbo.XG_PatrolStation F ON B.Station_ID=F.Station_ID
GO

-- =============================================
-- 視圖：增補巡更事件.增補巡更記錄
-- 此視圖作為以兩個異常情況所關聯的視圖 保証每一個人同一只有一條激勵
-- =============================================
IF OBJECT_ID('dbo.V_XGException') IS NOT NULL 
  DROP VIEW dbo.V_XGException
GO

CREATE VIEW dbo.V_XGException
AS
Select Person_ID,Supply_Date 
FROM (
Select Person_ID,CONVERT(VARCHAR(10),Supply_Date,126)AS Supply_Date 	
 FROM dbo.XG_SupplyEvent Group by Person_ID,Supply_Date
union all
Select Person_ID,Supply_Date 	
 FROM dbo.XG_SupplyPatrol Group by Person_ID,Supply_Date)A
Group by Person_ID,Supply_Date
GO

-- =============================================
--巡更記錄（結合增補巡更記錄）作為視圖V_XGDownLoad
-- =============================================
IF OBJECT_ID('V_XGDownLoad') IS NOT NULL
   DROP   VIEW V_XGDownLoad
GO
CREATE VIEW V_XGDownLoad AS
SELECT D.Person_id,W.Route_ID,D.Site_ID,D.Patrol_Date,D.Patrol_Time,D.Event_No
FROM   XG_Download D
LEFT JOIN XG_Work  W ON D.Person_ID = W.Person_ID AND D.Patrol_Date = W.Work_Date
--增補巡更記錄
UNION ALL
SELECT D.Person_ID,D.Route_ID,D.Site_ID,DATEADD(DAY, D.After_Days,D.Supply_Date) ,D.Supply_Time,D.Event_No
FROM   XG_SupplyPatrol D
GO

-- =============================================
-- 視圖：正常安排班次與巡更班次調整的組合
-- =============================================
IF OBJECT_ID('V_XGWork') IS NOT NULL
   DROP VIEW V_XGWork
GO
CREATE VIEW V_XGWork AS
SELECT Person_ID, Adjust_Date Work_Date, Route_ID
FROM   XG_AdjustWork
UNION ALL
SELECT Person_ID, Work_Date Adjust_Date, Route_ID
FROM   XG_Work
WHERE  NOT EXISTS
      (SELECT Person_ID,Adjust_Date FROM XG_AdjustWork
       WHERE  Person_ID = XG_Work.Person_ID
          AND Adjust_Date = XG_Work.Work_Date)
GO

-- =============================================
-- 視圖：獲得巡更人員班次的具體信息(不含增補巡更事件)
-- =============================================
IF OBJECT_ID('V_XGWorkPersonNoEvent') IS NOT NULL
   DROP VIEW V_XGWorkPersonNoEvent
GO
CREATE VIEW V_XGWorkPersonNoEvent AS
SELECT A.Person_ID,A.Work_Date,A.Route_ID,
       C.Site_ID,C.After_Days,C.Plan_Time,C.In_Range,C.Out_Range
FROM   V_XGWork  A
LEFT JOIN XG_RouteSite C ON A.Route_ID = C.Route_ID
GO

-- =============================================
-- 視圖：獲得巡更人員班次的具體信息(含增補巡更事件)
-- =============================================
IF OBJECT_ID('V_XGWorkPerson') IS NOT NULL
   DROP VIEW V_XGWorkPerson
GO
CREATE VIEW V_XGWorkPerson AS
  SELECT Person_ID,Work_Date,Route_ID,Site_ID,After_Days,Plan_Time,In_Range,Out_Range,
         SUBSTRING(CONVERT(VARCHAR(10),dbo.XG_fn_AddTimeLen(Person_ID,Route_ID,Site_ID,DATEADD(DAY,After_Days,Work_Date),Plan_Time),108),1,5)[Supply_Time]
  FROM V_XGWorkPersonNoEvent
GO

-- =============================================
-- 視圖：獲得巡更路線班次的具體信息（未增補巡更事件之前的視圖）
-- =============================================
IF OBJECT_ID('V_XGWorkRouteNoEvent') IS NOT NULL
   DROP VIEW V_XGWorkRouteNoEvent
GO

CREATE  VIEW V_XGWorkRouteNoEvent as
   SELECT Work_Date,A.Route_ID,
          Site_ID,After_Days,Plan_Time,In_range,Out_range 
     FROM (SELECT Route_ID,Work_Date FROM V_XGWork GROUP BY Route_ID,Work_Date) A
     LEFT JOIN XG_RouteSite B ON B.Route_ID = A.Route_ID
GO

-- =============================================
-- 視圖：獲得巡更路線班次的具體信息（增補巡更事件之后的視圖）
-- =============================================
IF OBJECT_ID('V_XGWorkRoute') IS NOT NULL
   DROP VIEW V_XGWorkRoute
GO

CREATE  VIEW V_XGWorkRoute as
  SELECT Work_Date,Route_ID,Site_ID,After_Days,Plan_Time,In_Range,Out_Range,
         --增補事件處理：時長處理,調用函數dbo.XG_fn_RouteAddTimeLen()
         SUBSTRING(CONVERT(VARCHAR(10),dbo.XG_fn_RouteAddTimeLen(Route_ID,Site_ID,DATEADD(DAY,After_Days,Work_Date),Plan_Time),108),1,5)[Supply_Time]
  FROM V_XGWorkRouteNoEvent
GO

-- =============================================
-- 視圖：關于巡更路線日報的相關視圖
--       用于路線日報查詢
-- =============================================
IF OBJECT_ID('V_XGRouteDaily') IS NOT NULL
   DROP VIEW V_XGRouteDaily
GO

CREATE  VIEW V_XGRouteDaily AS
SELECT R.Route_No,R.Route_Name,D.Patrol_Date,D.Patrol_Week,S.Site_No,S.Site_Name,
       D.Patrol_State,D.Plan_Time,D.Fact_Time,E.Event_Name
FROM XG_RouteDaily D
	Left Join XG_Route R On D.Route_ID = R.Route_ID
	Left Join XG_Site  S On D.Site_ID  = S.Site_ID
	Left Join XG_Event E On D.Event_No = E.Event_No
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 OBJECT_ID('dbo.XG_pd_PsnDaily') IS NOT NULL
   DROP PROCEDURE dbo.XG_pd_PsnDaily
GO

CREATE PROCEDURE dbo.XG_pd_PsnDaily
  @BgnDate varchar(10), @EndDate varchar(10), @TableName varchar(40),
  @Station_No varchar(25)
AS
  CREATE TABLE #SelectPerson (Person_ID INT) 
  EXEC('INSERT INTO #SelectPerson SELECT Person_ID FROM ' + @TableName) 
	--1.根據巡更排班和巡更記錄統計數據
	SELECT A.Person_ID,Work_Date[Patrol_Date],A.Route_ID,A.Site_ID,A.After_Days,
	       Plan_Time,Patrol_Time,Event_No,0[Patrol_State],
	       CASE WHEN Patrol_Time IS NOT NULL THEN 
	          DATEDIFF(n,Supply_Time, Patrol_Time)
	       ELSE
	          NULL
	       END [Interval],In_Range,Out_Range
	  INTO #PsnDaily
	  FROM V_XGWorkPerson A
	  LEFT JOIN V_XGDownLoad B ON B.Person_ID = A.Person_ID AND B.Site_ID = A.Site_ID
	                          AND DATEADD(DAY, A.After_Days, A.Work_Date) = B.Patrol_Date
                            --糾正同一天有相同巡更點的記錄
                  				  AND ABS(DATEDIFF(n, Supply_Time, Patrol_Time)) <= Out_Range
    WHERE A.Work_Date >= @BgnDate AND A.Work_Date <= @EndDate  --限制日期范圍
      AND A.Person_ID IN (SELECT Person_ID FROM #SelectPerson) --限制選中人員

	--2.糾正同一天在有相同巡更點的重復巡更記錄
	DELETE FROM #PsnDaily 
	  WHERE EXISTS(SELECT Person_ID FROM
	                 (SELECT Person_ID,Site_ID,Patrol_Date,Plan_Time,MIN(Patrol_Time)[Patrol_Time] 
                    FROM #PsnDaily
	                  GROUP BY Person_ID,Site_ID,Patrol_Date,Plan_Time
	                  HAVING COUNT(*)>1
	                  ) A 
	                WHERE A.Person_ID=#PsnDaily.Person_ID AND A.Site_ID=#PsnDaily.Site_ID
	                  AND A.Patrol_Date=#PsnDaily.Patrol_Date AND A.Plan_Time=#PsnDaily.Plan_Time
	                  AND A.Patrol_Time<>#PsnDaily.Patrol_Time
	               )
  --根據增補巡更事件更新巡更事件編號
  UPDATE #PsnDaily 
    SET  Event_No = S.Event_No
    FROM   #PsnDaily D,XG_SupplyEvent S
    WHERE  D.Person_ID = S.Person_ID AND D.Route_ID = S.Route_ID 
       AND D.Site_ID = S.Site_ID 
       AND D.Plan_Time = SUBSTRING(CONVERT(VARCHAR(20),S.Supply_Date+S.After_Days,108),1,5)

--3.未巡路線處理(要路線上所有的巡更點巡更時間為NULL才能確定本條路線未巡)
  -- 插入路線為未巡的記錄﹔
	INSERT INTO #PsnDaily(Person_ID,Route_ID,Patrol_Date,After_Days,Plan_Time,Patrol_State,Interval)
	  SELECT Person_ID,Route_ID,Patrol_Date,0,'',4,NULL 
    FROM #PsnDaily  WHERE Route_ID <> 0
    GROUP BY Person_ID,Patrol_Date,Route_ID
	  HAVING COUNT(*)=SUM(CASE WHEN Patrol_Time IS NULL THEN 1 ELSE 0 END)
	-- 刪除路線未巡的所有巡更點記錄﹔
	DELETE FROM #PsnDaily FROM 
        (SELECT Person_ID,Route_ID,Patrol_Date FROM #PsnDaily GROUP BY Person_ID,Patrol_Date,Route_ID
           HAVING COUNT(*)=SUM(CASE WHEN Patrol_Time IS NULL THEN 1 ELSE 0 END)) A
         WHERE Patrol_State<>4 AND A.Person_ID=#PsnDaily.Person_ID AND A.Route_ID=#PsnDaily.Route_ID
      	   AND A.Patrol_Date=#PsnDaily.Patrol_Date

--4.保存巡更路線日報統計數據到數據庫中
  --清除巡更人員日報在數據庫相關的統計條件
  Delete From XG_PsnDaily Where Patrol_Date >= @BgnDate
                            And Patrol_Date <= @EndDate
                            And Person_ID IN (SELECT Person_ID FROM #SelectPerson) 
  --插入巡更人員日報的統計結果
  SET DATEFIRST 1 --設置每周的第一天為哪一天（此設置為正常周一為1）
  Insert Into XG_PsnDaily 
       Select Person_ID,Patrol_Date,DatePart(WeekDay,Patrol_Date)[Patrol_Week],Route_ID,Site_ID,
              CASE WHEN Site_ID IS NULL THEN 4       --未巡4
	            WHEN ABS(Interval)<=In_Range THEN 0    --准時0
	            WHEN Interval<0 THEN 1                 --早到1
	            WHEN Interval>0 THEN 2                 --遲到2
        	    ELSE 3                                 --漏巡3
	       END [Patrol_State],
              After_Days,Plan_Time,Patrol_Time Fact_Time,Event_No 
       From   #PsnDaily

	--5.顯示數據
	SELECT P.Person_No,Person_Name,Patrol_Date,DatePart(WeekDay,Patrol_Date)[Patrol_Week],Route_Name,Site_Name,After_Days,Plan_Time,Patrol_Time Fact_Time,Event_Name,
	       CASE WHEN M.Site_ID IS NULL THEN 4          --未巡4
	            WHEN ABS(Interval)<=In_Range THEN 0    --准時0
	            WHEN Interval<0 THEN 1                 --早到1
	            WHEN Interval>0 THEN 2                 --遲到2
        	    ELSE 3                                 --漏巡3
	       END [Patrol_State],In_Range,Out_Range,M.Person_ID,M.Route_ID,M.Site_ID
	FROM #PsnDaily M
	  LEFT JOIN XG_Person P ON M.Person_ID = P.Person_ID
	  LEFT JOIN XG_Route  R ON M.Route_ID  = R.Route_ID
	  LEFT JOIN XG_Site   S ON M.Site_ID   = S.Site_ID
	  LEFT JOIN XG_Event  E ON M.Event_No  = E.Event_No
          LEFT JOIN XG_PatrolStation F ON R.Station_ID = F.Station_ID
          Where F.Station_NO Like @Station_No --and P.XGClass=0 
	ORDER BY Person_No,Patrol_Date,Route_Name,M.After_Days,M.Plan_Time
  --刪除選中人員的過渡臨時表
  DROP TABLE #SelectPerson 
GO

-- =============================================
-- 巡更系統存儲過程：巡更路線日報匯總統計
-- =============================================
IF OBJECT_ID('dbo.XG_pd_RouteDaily') IS NOT NULL
   DROP PROCEDURE dbo.XG_pd_RouteDaily
GO

CREATE PROCEDURE dbo.XG_pd_RouteDaily
  @BgnDate varchar(10), @EndDate varchar(10), @TableName varchar(40),
  @sStationNo Varchar(25)
AS
  DECLARE @Table TABLE(Route_ID INT, Patrol_Date VARCHAR(10))
  DECLARE @WorkRoute TABLE(Route_ID INT, Work_Date VARCHAR(10))
  DECLARE @DateBgn DATETIME, @DateEnd DATETIME
    SET @DateBgn=@BgnDate
    SET @DateEnd=@EndDate

  CREATE TABLE #SelectRoute (Route_ID INT) 
  EXEC('INSERT INTO #SelectRoute SELECT Route_ID FROM ' + @TableName) 

--1.根據巡更排班和巡更記錄統計數據
  SELECT Work_Date[Patrol_Date],A.Route_ID,A.Site_ID,
       After_Days,Plan_Time,Supply_Time,Patrol_Time,Event_No,0 [Patrol_State],NULL [NtWork],
       CASE WHEN Patrol_Time IS NOT NULL THEN 
          DATEDIFF(n, Supply_Time, Patrol_Time)
       ELSE
          NULL
       END [Interval],In_Range,Out_Range
  INTO #RouteDaily
  FROM V_XGWorkRoute A
  LEFT JOIN V_XGDownLoad B ON B.Route_ID = A.Route_ID AND B.Site_ID = A.Site_ID
                          AND DATEADD(DAY, A.After_Days, A.Work_Date) = B.Patrol_Date
                          AND ABS(DATEDIFF(n, Supply_Time, Patrol_Time)) <= Out_Range
  WHERE A.Work_Date >= @BgnDate AND A.Work_Date <= @EndDate  --限制日期范圍
    AND A.Route_ID IN (SELECT Route_ID FROM #SelectRoute)    --限制選中路線

--2.糾正同一天在有相同巡更點的重復巡更記錄
	DELETE FROM #RouteDaily 
	  WHERE EXISTS(SELECT Site_ID FROM
	                 (SELECT Route_ID,Site_ID,Patrol_Date,Plan_Time,MIN(Patrol_Time)[Patrol_Time] FROM #RouteDaily
	                   GROUP BY Route_ID,Site_ID,Patrol_Date,Plan_Time
	                  HAVING COUNT(*)>1
	                 ) A 
	                WHERE A.Route_ID=#RouteDaily.Route_ID
                    AND A.Site_ID=#RouteDaily.Site_ID
	                  AND A.Patrol_Date=#RouteDaily.Patrol_Date AND A.Plan_Time=#RouteDaily.Plan_Time
	                  AND A.Patrol_Time<>#RouteDaily.Patrol_Time
	               )

  --根據增補巡更事件更新巡更事件編號
  UPDATE #RouteDaily 
    SET  Event_No = S.Event_No
    FROM   #RouteDaily D,XG_SupplyEvent S
    WHERE  D.Route_ID = S.Route_ID AND D.Site_ID = S.Site_ID 
       AND D.Plan_Time = SUBSTRING(CONVERT(VARCHAR(20),S.Supply_Date+S.After_Days,108),1,5)

--3.未巡路線處理(要路線上所有的巡更點巡更時間為NULL才能確定本條路線未巡)
  -- 插入路線為未巡的記錄﹔
	INSERT INTO #RouteDaily(Route_ID,Patrol_Date,Plan_Time,Patrol_State,Interval)
	  SELECT Route_ID,Patrol_Date,'',4,NULL FROM #RouteDaily GROUP BY Patrol_Date,Route_ID
	  HAVING COUNT(*)=SUM(CASE WHEN Patrol_Time IS NULL THEN 1 ELSE 0 END)

	-- 刪除路線未巡的所有巡更點記錄﹔
	DELETE FROM #RouteDaily FROM (SELECT Route_ID,Patrol_Date FROM #RouteDaily GROUP BY Patrol_Date,Route_ID
	  HAVING COUNT(*)=SUM(CASE WHEN Patrol_Time IS NULL THEN 1 ELSE 0 END)) A
	  WHERE Patrol_State<>4 AND A.Route_ID=#RouteDaily.Route_ID AND A.Patrol_Date=#RouteDaily.Patrol_Date

  --獲得路線安排的日期記錄放到臨時表中
  INSERT INTO @WorkRoute 
  SELECT Route_ID,Work_Date FROM V_XGWorkRoute 
  WHERE  Route_ID IN (SELECT Route_ID FROM #SelectRoute)
  GROUP BY Route_ID,Work_Date
  
	WHILE @DateBgn<=@DateEnd
	BEGIN  
	  INSERT INTO @Table
    SELECT Route_ID,CONVERT(VARCHAR(10), @DateBgn, 121) 
      FROM (SELECT Route_ID FROM @WorkRoute GROUP BY Route_ID) A
    SET @DateBgn=@DateBgn + 1
	END
  --插入路線未安排的記錄
  INSERT INTO #RouteDaily(Route_ID,Patrol_Date,Patrol_State,NtWork) 
  SELECT A.*,5[Patrol_State],-1 FROM @Table A
    LEFT JOIN @WorkRoute B ON B.Route_ID=A.Route_ID AND B.Work_Date=A.Patrol_Date
  WHERE B.Work_Date IS NULL 

--4.保存巡更路線日報統計數據到數據庫中
  --清除巡更人員日報在數據庫相關的統計條件
  SET DATEFIRST 1 --設置每周的第一天為哪一天（此設置為正常周一為1）
  Delete From XG_RouteDaily Where Patrol_Date >= @BgnDate
                            And Patrol_Date <= @EndDate
                            And Route_ID IN (SELECT Route_ID FROM #SelectRoute) 
  --插入巡更人員日報的統計結果
  Insert Into XG_RouteDaily 
       Select Route_ID,Patrol_Date,DatePart(WeekDay,Patrol_Date)[Patrol_Week],Site_ID,
	       CASE WHEN NtWork IS NULL AND Site_ID IS NULL THEN 4        --未巡4
              WHEN NtWork =-1 AND Site_ID IS NULL THEN 5            --未安排5
	            WHEN ABS(Interval)<=In_Range THEN 0    --准時0
	            WHEN Interval<0 THEN 1                 --早到1
	            WHEN Interval>0 THEN 2                 --遲到2
   	     ELSE 3                                      --漏巡3
	       END [Patrol_State],
              After_Days,Plan_Time,Patrol_Time Fact_Time,Event_No 
       From   #RouteDaily

--5.顯示數據
  SELECT route_no,route_name,Patrol_Date,DatePart(WeekDay,Patrol_Date)[Patrol_Week],site_no,site_name,ISNULL(After_Days,-1)[After_Days],plan_time,patrol_time[Fact_Time],
       CASE WHEN patrol_time IS NULL THEN ''
            ELSE event_name
       END [event_name],Interval,
       CASE WHEN NtWork IS NULL AND S.Site_ID IS NULL THEN 4        --未巡4
            WHEN NtWork =-1 AND S.Site_ID IS NULL THEN 5            --未安排5
            WHEN ABS(Interval)<=In_Range THEN 0    --准時0
            WHEN Interval<0 THEN 1                 --早到1
            WHEN Interval>0 THEN 2                 --遲到2
	    ELSE 3                                       --漏巡3
       END [Patrol_State],In_Range,Out_Range
  FROM #RouteDaily M
   left join XG_Route  R on M.route_id  = R.route_id
   left join XG_Site   S on M.site_id   = S.site_id
   left join XG_Event  E on M.event_no  = E.event_no
   left join XG_PatrolStation f on r.station_id = f.station_id
   where f.station_no like @sStationNo
   order by Route_no,Patrol_Date,M.After_Days,M.plan_time
  --刪除選中人員的過渡臨時表
  DROP TABLE #SelectRoute 

GO

-- =============================================
-- 取得路線代號函數，返回代號（如A、AA等）.
-- 用于路線.
-- =============================================
if OBject_ID('XG_fn_GetRouteCode') IS NOT NULL
DROP FUNCTION XG_fn_GetRouteCode 
go

CREATE FUNCTION XG_fn_GetRouteCode 
	(@Index INT)
RETURNS VARCHAR(2)
AS
BEGIN
   DECLARE @Return VARCHAR(2)
   SELECT @Return=CASE WHEN ISNULL(@Index,0)>0 THEN
                    CASE WHEN @Index<27 THEN CHAR(@Index+64) 
                    ELSE 
                        CHAR(64+(@Index-@Index%26)/26)+CHAR(65+(@Index-1)%26) 
                    END
                  ELSE
                     ''
                  END
   RETURN @Return
END
GO

-- =============================================
-- 得到人員排班的路線,調整巡更班次
-- =============================================

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

CREATE PROCEDURE dbo.XG_pd_GetWorkData
 @RouteTmp VARCHAR(30),--轉化路線代號之后的臨時表名
 @PersonTmp VARCHAR(30),--部門樹中選擇的人員臨時表名
 @WorkTmp VARCHAR(30),--路線排班臨時表
 @Startdate DateTime,--開始日期
 @DataType Int,--更新數據類型0：巡更排班 1：調整巡更班次
 @StationNo VARCHAR(30)
AS

DECLARE  @SQL VARCHAR(8000);
DECLARE  @SQL_D VARCHAR(8000);
DECLARE  @SQLChild VARCHAR(8000);
DECLARE  @SQL_F VARCHAR(8000);
DECLARE  @I  Int

SET @I=1 ;
SET @SQL=''
SET @SQL_D=''  
SET @SQLChild=''

 WHILE  @I<32
 BEGIN
   SET @SQL_D= @SQL_D +',' + 'SUM(CASE WHEN D=' +CAST(@I-1 AS VARCHAR)+ ' THEN ID_Num ELSE 0 END) [D'+ CAST(@I AS VARCHAR) +']';
   SET @I=@I+1;
 END
 if @DataType=0 --巡更排班  
 begin
 SET @SQLChild= ' SELECT Person_ID '+ @SQL_D +
                ' FROM (SELECT A.Person_ID,DATEDIFF(D, '''+CONVERT(VARCHAR(10),@Startdate,120)+''',Work_date)[D],'+
                       ' A.Route_ID,C.ID_Num From XG_Work A '+
                       ' LEFT JOIN '+ @RouteTmp +' C ON C.Route_ID=A.Route_ID ' +
                       ' WHERE EXISTS (SELECT Person_ID FROM '+@PersonTmp +
                                       ' C WHERE A.Person_ID=C.Person_ID)'+
                         ' AND Work_date>='''+CONVERT(VARCHAR(10),@Startdate,120) +''''+
                   '  )D  '+
                ' GROUP BY Person_ID ';
 end
 else begin --巡更排班

  SET @SQLChild= ' SELECT Person_ID,Reason '+ @SQL_D +
                ' FROM (SELECT A.Person_ID,DATEDIFF(D, '''+CONVERT(VARCHAR(10),@Startdate,120)+''',Adjust_date)[D],'+
                       ' A.Route_ID,C.ID_Num,A.Reason From XG_AdjustWork A '+
                       ' LEFT JOIN '+ @RouteTmp +' C ON C.Route_ID=A.Route_ID ' +
                       ' WHERE EXISTS (SELECT Person_ID FROM '+@PersonTmp +
                                       ' C WHERE A.Person_ID=C.Person_ID)'+
                         ' AND Adjust_date>='''+CONVERT(VARCHAR(10),@Startdate,120) +''''+
                   '  )D  '+
                ' GROUP BY Person_ID,Reason ';

end;
--取得日期對應的班次代號
 Set @I=1
 Set @SQL_D=''
 WHILE @I<32
 BEGIN
   SET @SQL_D=@SQL_D +',RTRIM(dbo.XG_fn_GetRouteCode(D'+CAST(@I AS VARCHAR)+')) [D'+CAST(@I AS VARCHAR)+']'; 
   SET @I=@I+1;
 END

--生成31個日期字段
 SET @SQL_F=''
 Set @I=1
 WHILE @I<32
 BEGIN
   SET @SQL_F=@SQL_F +',D'+CAST(@I AS VARCHAR) 
   SET @I=@I+1;
 END

 SET @SQL=''
 --插入臨時表
 if @DataType=0 --巡更排班 
 begin
 SET @SQL= 'INSERT INTO '+ @WorkTmp+'(Person_ID,Person_Name,Person_NO'+ @SQL_F+')'
 SET @SQL=@SQL + ' SELECT E.Person_ID,Person_Name,Person_NO '+ @SQL_D+
             ' FROM '+@PersonTmp +' E '+
             ' LEFT JOIN XG_Person D ON D.Person_ID=E.Person_ID '+
             ' LEFT JOIN ('+ @SQLChild + ') F ON F.Person_ID=E.Person_ID '+
             ' WHERE D.Station_ID IN (SELECT Station_ID FROM dbo.XG_PatrolStation WHERE Station_No LIKE '''+@StationNo+'%'')'
 end
 else --調整巡更班次
 begin
   SET @SQL= 'INSERT INTO '+ @WorkTmp+'(Person_ID,Person_Name,Person_NO,Reason '+ @SQL_F+')'
   SET @SQL=@SQL + ' SELECT E.Person_ID,Person_Name,Person_NO,Reason '+ @SQL_D+
             ' FROM '+@PersonTmp +' E '+
             ' LEFT JOIN XG_Person D ON D.Person_ID=E.Person_ID'+
             ' LEFT JOIN ('+ @SQLChild + ') F ON F.Person_ID=E.Person_ID '+
             ' WHERE D.Station_ID IN (SELECT Station_ID FROM dbo.XG_PatrolStation WHERE Station_No LIKE '''+@StationNo+'%'')'
 end
EXEC(@SQL)

GO

-- =============================================
-- 更新人員的巡更排班
-- =============================================
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'XG_pd_UpdateWork' AND type = 'P')
    DROP PROCEDURE dbo.XG_pd_UpdateWork
GO

CREATE  PROCEDURE dbo.XG_pd_UpdateWork
  @ARouteCode VARCHAR(30),--轉化為路線代號之后的路線臨時表名
  @FWorkEnd VARCHAR(30),--最后經處理的臨時表
  @FWorkTmp VARCHAR(30),--在界面上所操作的當前臨時表
  @StartDate DATETIME--表格中開始日期
AS
DECLARE  @SQL VARCHAR(8000);
DECLARE  @SQL_D VARCHAR(8000);
DECLARE  @I  Int,@FieldValue VARCHAR(100),@Field VARCHAR(20);
DECLARE  @DATE DateTime

SET @I=1 ;
SET @SQL=''
  
  --處理前轉化數據
  WHILE  @I< 32
  BEGIN
    SET @DATE=@StartDate+@I-1;
    SET @FieldValue =CONVERT(VARCHAR(10),@StartDate+@I-1,126);
    SET @Field=CAST('D'+CAST(@i as VARCHAR)as VARCHAR)
    IF @SQL = ''
     SET @SQL = 'SELECT Person_ID,'''+CAST(@FieldValue AS VARCHAR)+''' AS Work_Date, '+
                ' A.Route_ID'+
                ' FROM '+ @FWorkTmp +
                ' LEFT JOIN '+@ARouteCode +'  A on A.Route_Code='+@Field+
                ' WHERE ISNull('+@Field+','''')<>'''''
   ELSE
    SET @SQL = @SQL + ' UNION ALL '+
                ' SELECT Person_ID,'''+CAST(@FieldValue AS VARCHAR)+''' AS Work_Date,'+
                ' A.Route_ID'+
                ' FROM '+ @FWorkTmp +
                ' LEFT JOIN '+@ARouteCode +' A on A.Route_Code='+@Field+
                ' WHERE ISNull('+@Field+','''')<>'''''

   SET @I=@I+1;
 END

--插入到新最后排班路線臨時表 
  SET @SQL='INSERT INTO '+@FWorkEnd +'(Person_ID,Work_Date,Route_ID)'+
           ' SELECT Person_ID,Work_Date,Route_ID FROM ('+ @SQL +')A'
   EXEC(@SQL)

--刪除不在臨時表的記錄
  SET @SQL ='DELETE XG_Work '+
                ' WHERE EXISTS (SELECT Person_ID FROM '+ @FWorkTmp +' A '+
                               ' WHERE A.Person_ID='+'XG_Work.Person_ID)'

--09-11-20 無序排班時 下載記錄會產生相同日期的XG_Work記錄，固屏蔽掉
/*                ' AND NOT EXISTS(SELECT Person_ID FROM '+@FWorkEnd+' B '+
                               ' WHERE B.Person_ID='+'XG_Work.Person_ID '+
                               ' AND B.Work_Date='+'XG_Work.Work_Date)'

                ' AND NOT EXISTS(SELECT Person_ID FROM V_XGException C '+
                               ' WHERE C.Person_ID='+'XG_Work.Person_ID '+
                               ' AND C.Supply_Date='+'XG_Work.Work_Date)'
*/
                              
   EXEC(@SQL)

--修改排班日期相同的巡更路線記錄
  SET @SQL ='UPDATE XG_Work SET Route_ID=A.Route_ID'+ 
              ' FROM '+@FWorkEnd+' A WHERE XG_Work.Person_ID=A.Person_ID '+
                ' AND XG_Work.Work_Date=A.Work_Date '+
                ' AND NOT EXISTS(SELECT Person_ID FROM V_XGException C '+
                               ' WHERE C.Person_ID='+'A.Person_ID '+
                               ' AND C.Supply_Date='+'A.Work_Date)'


  EXEC(@SQL)

--插入臨時表存在，但XG_Work中不存在的記錄
  SET @SQL ='INSERT INTO XG_Work (Person_ID,Work_Date,Route_ID)'+
          '  SELECT A.Person_ID,CONVERT(VARCHAR(10),A.Work_Date,126),'+
          '  A.Route_ID From '+ @FWorkEnd+' A  ' +
          '  LEFT JOIN XG_Work B On B.Person_ID=A.Person_ID and B.Work_Date=A.Work_Date '+
          '  WHERE B.Route_ID IS NULL'
  EXEC(@SQL)

GO
-- =============================================
-- 更新人員的巡更調班
-- =============================================
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'XG_pd_UpdateAdjustWork' AND type = 'P')
    DROP PROCEDURE dbo.XG_pd_UpdateAdjustWork
GO

CREATE PROCEDURE dbo.XG_pd_UpdateAdjustWork
  @ARouteCode VARCHAR(30),--轉化為路線代號之后的路線臨時表名
  @FWorkEnd VARCHAR(30),--最后經處理的臨時表
  @FWorkTmp VARCHAR(30),--在界面上所操作的當前臨時表
  @StartDate DATETIME,--表格中開始日期
  @Audit_Man Varchar(30)--審核人
AS
DECLARE  @SQL VARCHAR(8000);
DECLARE  @SQL_D VARCHAR(8000);
DECLARE  @I  Int,@FieldValue VARCHAR(100),@Field VARCHAR(20);
DECLARE  @DATE DateTime

SET @I=1 ;
SET @SQL=''
  
  --處理前轉化數據
  WHILE  @I< 32
  BEGIN
    SET @DATE=@StartDate+@I-1;
    SET @FieldValue =CONVERT(VARCHAR(10),@StartDate+@I-1,126);
    SET @Field=CAST('D'+CAST(@i as VARCHAR)as VARCHAR)
    IF @SQL = ''
     SET @SQL = 'SELECT Person_ID,'''+CAST(@FieldValue AS VARCHAR)+''' AS Adjust_Date, '+
                ' A.Route_ID,'+@Audit_Man+' AS Audit_Man, Reason'+
                ' FROM '+ @FWorkTmp +
                ' LEFT JOIN '+@ARouteCode +'  A on A.Route_Code='+@Field+
                ' WHERE ISNull('+@Field+','''')<>'''''
   ELSE
    SET @SQL = @SQL + ' UNION ALL '+
                ' SELECT Person_ID,'''+CAST(@FieldValue AS VARCHAR)+''' AS Adjust_Date,'+
                ' A.Route_ID,'+@Audit_Man+' AS Audit_Man,Reason'+
                ' FROM '+ @FWorkTmp +
                ' LEFT JOIN '+@ARouteCode +' A on A.Route_Code='+@Field+
                ' WHERE ISNull('+@Field+','''')<>'''''
   SET @I=@I+1;
 END

--插入到新最后排班路線臨時表 
  SET @SQL='INSERT INTO '+@FWorkEnd +'(Person_ID,Adjust_Date,Route_ID,Audit_Man,Reason)'+
           ' SELECT Person_ID,Adjust_Date,Route_ID,Audit_Man,Reason FROM ('+ @SQL +')A'
   EXEC(@SQL)

--刪除不在臨時表的記錄
  SET @SQL ='DELETE XG_AdjustWork '+
                ' WHERE EXISTS (SELECT Person_ID FROM '+ @FWorkTmp +' A '+
                               ' WHERE A.Person_ID='+'XG_AdjustWork.Person_ID)'+
                ' AND NOT EXISTS(SELECT Person_ID FROM '+@FWorkEnd+' B '+
                               ' WHERE B.Person_ID='+'XG_AdjustWork.Person_ID '+
                               ' AND B.Adjust_Date='+'XG_AdjustWork.Adjust_Date)';
  EXEC(@SQL)

--修改調整日期相同的調整記錄
  SET @SQL ='UPDATE XG_AdjustWork SET Route_ID=A.Route_ID,Audit_Man=A.Audit_Man,Reason=A.Reason'+ 
            ' FROM '+@FWorkEnd+' A WHERE XG_AdjustWork.Person_ID=A.Person_ID '+
            ' AND XG_AdjustWork.Adjust_Date=A.Adjust_Date '
  EXEC(@SQL)

--插入臨時表存在，但XG_AdjustWork中不存在的記錄
  SET @SQL ='INSERT INTO XG_AdjustWork (Person_ID,Adjust_Date,Route_ID,Audit_Man,Reason)'+
          '  SELECT A.Person_ID,CONVERT(VARCHAR(10),A.Adjust_Date,126),'+
          '  A.Route_ID,A.Audit_Man,A.Reason From '+ @FWorkEnd+' A  ' +
          '  LEFT JOIN XG_AdjustWork B On B.Person_ID=A.Person_ID and B.Adjust_Date=A.Adjust_Date '+
          '  WHERE B.Route_ID IS NULL'
  EXEC(@SQL)
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('XG_vi_Download') IS NOT NULL
   DROP VIEW XG_vi_Download
GO

CREATE VIEW XG_vi_Download
AS
  SELECT Moc_No,A.Route_No,E.Route_Name,ISNULL(B.Person_No,A.Person_No)[Person_No],Person_Name,
  ISNULL(cast(C.Card_No as int),cast(A.Card_No as int))[Card_No],C.Station_ID[Station_ID],Site_No,Site_Name,
  Patrol_Date,Patrol_Time,ISNULL(D.Event_Name,A.Event_No)[Event_Name]
  FROM XG_Download A
  LEFT JOIN XG_Person B ON B.Person_ID=A.Person_ID
  LEFT JOIN XG_Site C ON C.Site_ID=A.Site_ID
  LEFT JOIN XG_Event D ON D.Event_No=A.Event_No
  LEFT JOIN XG_Route E ON A.Route_No=cast(E.Route_No as int)
GO

-- =============================================
-- 插入巡更人員及路線
-- =============================================
IF OBJECT_ID('XG_pd_InsertXGWork') IS NOT NULL Drop Procedure XG_pd_InsertXGWork
Go
CREATE Procedure XG_pd_InsertXGWork
@WorkDateBgn Varchar(25),
@WorkDateEnd Varchar(25),
@Station_No Varchar(25)
AS
BEGIN
    /*自由班次*/
    --1.刪除記錄
    DELETE FROM XG_Work WHERE Person_ID In (SELECT Person_ID FROM XG_Person 
                                            WHERE Station_ID IN (SELECT Station_ID FROM XG_PatrolStation WHERE Station_No LIKE @Station_No+'%') 
                                              AND XGClass = 1)
    And Work_Date >= @WorkDateBgn And Work_Date <= @WorkDateEnd
    --2.插入記錄
    INSERT INTO XG_Work(Person_ID,Work_Date,Route_ID)
    SELECT DISTINCT A.Person_ID,A.Patrol_Date,B.Route_ID FROM XG_Download A Left Join XG_Route B On
    A.Route_No = B.Route_No Left Join XG_Person C ON A.Person_ID = C.Person_ID
    WHERE Patrol_Date >= @WorkDateBgn And Patrol_Date <= @WorkDateEnd 
      AND C.Station_ID IN (SELECT Station_ID FROM XG_PatrolStation WHERE Station_No LIKE @Station_No+'%') 
      AND C.XGClass = 1 AND B.Route_ID IS NOT NULL	


    /*固定路線*/
    --1.刪除記錄
    DELETE FROM XG_Work WHERE Person_ID In (SELECT Person_ID FROM XG_Person
                                            WHERE Station_ID IN (SELECT Station_ID FROM XG_PatrolStation WHERE Station_No LIKE @Station_No+'%') 
                                               AND XGClass = 2)
    And Work_Date >= @WorkDateBgn And Work_Date <= @WorkDateEnd
    --2.插入記錄
    DECLARE @DateTable TABLE(Work_Date VARCHAR(10))
    DECLARE @DateBgn DATETIME,@DateEnd DATETIME
    SET @DateBgn=CAST(@WorkDateBgn AS DATETIME)
    SET @DateEnd=CAST(@WorkDateEnd AS DATETIME)
    WHILE @DateBgn<=@DateEnd
    BEGIN
       INSERT INTO @DateTable(Work_Date) VALUES(CONVERT(VARCHAR(10), @DateBgn, 120))
       SET @DateBgn=@DateBgn+1
    END

    INSERT INTO XG_Work(Person_ID,Work_Date,Route_ID)
    SELECT Person_ID,Work_Date,Route_ID 
    FROM XG_Person A,@DateTable B
    WHERE A.Station_ID IN (SELECT Station_ID FROM XG_PatrolStation WHERE Station_No LIKE @Station_No+'%') 
    And XGClass=2   
     
End
Go


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

CREATE PROCEDURE dbo.st_pd_GetMaxStationNo
	@StationNo VARCHAR(30) --指定的部門編號
AS	
DECLARE @MaxStationNo VARCHAR(30)
DECLARE @StationIndex INT

   SELECT @MaxStationNo=ISNULL(MAX(Station_No),@StationNo+'00') FROM dbo.XG_PatrolStation 
    WHERE Station_No LIKE @StationNo+'%' AND LEN(Station_No)=LEN(@StationNo)+2
   SET @StationIndex=CAST(SUBSTRING(@MaxStationNo,LEN(@StationNo)+1,2) AS INT)+1

   IF @StationIndex<99 
   BEGIN
      IF @StationIndex<10 
         SELECT @StationNo+'0'+CAST(@StationIndex AS VARCHAR)
      ELSE   
         SELECT @StationNo+CAST(@StationIndex AS VARCHAR)
   END
   ELSE
     RAISERROR('Over Max Department No 99!', 16, 1)
GO

-- =============================================
-- 插入巡檢點
-- =============================================
IF OBJECT_ID('dbo.XG_pd_AddSite') IS NOT NULL
   DROP PROCEDURE dbo.XG_pd_AddSite
GO

CREATE PROCEDURE dbo.XG_pd_AddSite
  @CardNo VARCHAR(40),
  @StationID INT
AS
--    DECLARE @CardNo VARCHAR(40),@StationID INT
--        SET @CardNo='12345678'
--        SET @StationID=3
   --判斷地點是否存在,若存在并且沒有被使用先刪除再增加
   DECLARE @SiteID INT   
   SELECT @SiteID=Site_ID FROM XG_Site WHERE Card_No=@CardNo
   IF (@SiteID IS NOT NULL) AND
     (NOT EXISTS(SELECT Site_ID FROM XG_RouteSite WHERE Site_ID=@SiteID)) AND
     (NOT EXISTS(SELECT Site_ID FROM XG_Download WHERE Site_ID=@SiteID))
   BEGIN
      DELETE FROM XG_Site WHERE Site_ID=@SiteID
      SET @SiteID=NULL    
   END
   --若地點不存在就增加一個新的地點
   IF @SiteID IS NULL
   BEGIN
      DECLARE @@SiteID INT,@SiteNo VARCHAR(40)
      EXECUTE dbo.st_pd_GetMaxIDEx 'XG_Site',@@SiteID OUTPUT
          SET @SiteNo='S'+RIGHT(CAST(@@SiteID+10000 AS VARCHAR),4)
      INSERT INTO XG_Site(Site_ID,Site_No,Site_Name,Card_No,Remark,Station_ID)
      SELECT @@SiteID,@SiteNo,@SiteNo,@CardNo,@SiteNo + '-' + @CardNo,@StationID
   END
GO

IF OBJECT_ID('XG_Pd_EventTotal') IS NOT NULL
   DROP PROCEDURE XG_Pd_EventTotal
GO
-- 站點事件統計
/*
Exec XG_Pd_EventTotal '2009-01-10','2009-11-25',10
*/
CREATE PROCEDURE dbo.XG_Pd_EventTotal
@BgnDate DATETIME,
@EndDate DATETIME,
@Station_No Varchar(25)
AS

    DECLARE @sSQL VARCHAR(5000) 
    DECLARE @UpdateField VARCHAR(5000)

    IF object_ID('tempdb..#XG_Download') IS NOT NULL
    Drop table #XG_Download
    CREATE TABLE #XG_Download([Station_No] VARCHAR(20) DEFAULT '0',
			[Station_Name] VARCHAR (20) DEFAULT '0',		
			[Event_No] VARCHAR (10) NULL,	
			[Consume_Count] INT DEFAULT 0)	
    DELETE #XG_Download
    INSERT INTO #XG_Download(Station_No,Station_Name,Event_No,Consume_Count) 
	SELECT 
        (CASE WHEN Station_No IS NULL THEN '---' ELSE Station_No END)Station_No,
        (CASE WHEN Station_Name IS NULL THEN '---' ELSE Station_Name END)Station_Name,
	Event_No,COUNT(*)[Count] 
	FROM (SELECT Event_No,Site_ID,Patrol_Date FROM XG_Download 
               UNION ALL 
              SELECT Event_No,Site_ID,Supply_Date[Patrol_Date] FROM dbo.XG_SupplyPatrol)  A 
	LEFT JOIN XG_Site B ON A.Site_ID=B.Site_ID 
	LEFT JOIN XG_PatrolStation C ON B.Station_ID=C.Station_ID 
	WHERE C.Station_No Like @Station_No+'%' AND Patrol_Date >= @BgnDate and Patrol_Date <= @EndDate 
	GROUP BY C.Station_No,C.Station_Name,Event_No       

    --定義處理游標------
    SET @UpdateField=''
    DECLARE Moc_cursor CURSOR FOR SELECT Event_No FROM XG_Event 
    DECLARE @Event_No VARCHAR(10)

    OPEN Moc_cursor
    FETCH NEXT FROM Moc_cursor INTO @Event_No
    WHILE @@fetch_status = 0
    BEGIN        
	SET @UpdateField= @UpdateField+
            ' sum(case when Event_No='''+@Event_No+''' then consume_count else 0 end) '''+@Event_No+''','
	FETCH NEXT FROM Moc_cursor INTO @Event_No
    END

    SET @UpdateField=SUBSTRING(@UpdateField, 1, LEN(@UpdateField)-1) 

    CLOSE Moc_cursor
    DEALLOCATE Moc_cursor                                 
    --關閉游標------

    --2.返回結果
    SET @sSQL= ' select Station_No,Station_Name, ' + @UpdateField + ' from #XG_Download '+
	' group by Station_No,Station_Name'+
	' order by Station_No,Station_Name ' 
EXEC(@sSQL)

GO
