-- =============================================
-- 視圖：dbo.V_KQClassInfo（班次類別、班次編號、班次名稱）
-- =============================================

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create View dbo.V_KQClassInfo AS 
		SELECT 'A' Class_Type,Class_No,Class_Name From dbo.KQ_Class
		UNION ALL
		SELECT 'B' Class_Type,Class_No,Class_Name From dbo.KQ_RuleClass
		UNION ALL
		SELECT 'C' Class_Type,Class_No,Class_Name From dbo.KQ_AgileClass
		UNION ALL
		SELECT 'D' Class_Type,Class_No,Class_Name From dbo.KQ_DefineClass

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 視圖：考勤人員考勤信息與核心平台部門、人員信息關聯
-- 此視圖作為以下異常情況所關聯的視圖
-- =============================================
IF OBJECT_ID('dbo.V_KQPerson') IS NOT NULL DROP VIEW dbo.V_KQPerson
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.V_KQPerson
AS
SELECT A.*,
      B.Person_No, 
      B.Person_Name, 
      B.Dept_ID, 
      B.Card_No, 
      C.Dept_No, 
      C.Dept_Name
FROM dbo.KQ_Person A LEFT OUTER JOIN
      dbo.ST_Person B ON B.Person_ID = A.Person_ID LEFT OUTER JOIN
      dbo.ST_Department C ON C.Dept_ID = B.Dept_ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 視圖：考勤班次調整信息與人員考勤信息關聯
-- =============================================
IF OBJECT_ID('dbo.V_KQAdjustClass') IS NOT NULL DROP VIEW dbo.V_KQAdjustClass
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.V_KQAdjustClass
AS
SELECT 
    A.ID_KEY, 
    A.Person_ID, 
    A.Adjust_Date, 
    A.Class_No, 
    A.Remark, 
    A.User_No, 
    A.Update_Date, 
    B.Person_No, 
    B.Person_Name, 
    B.Card_No, 
    B.Dept_No, 
    B.Dept_Name, 
    B.Dept_ID, 
    C.Class_Name
FROM dbo.KQ_AdjustClass A
LEFT OUTER JOIN dbo.V_KQPerson B ON B.Person_ID = A.Person_ID 
LEFT OUTER JOIN dbo.V_KQClassInfo C ON A.Class_No = C.Class_No

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 視圖：考勤調班補休信息與人員考勤信息關聯
-- =============================================
IF OBJECT_ID('dbo.V_KQAdjustRest') IS NOT NULL DROP VIEW dbo.V_KQAdjustRest
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.V_KQAdjustRest
AS
SELECT 
    A.ID_KEY, 
    A.Person_ID, 
    A.Adjust_Date, 
    A.Adjust_Hour, 
    A.Adjust_Class, 
    A.Remark, 
    A.User_No, 
    A.Update_Date, 
    B.Person_No, 
    B.Person_Name, 
    B.Card_No, 
    B.Dept_No, 
    B.Dept_Name, 
    B.Dept_ID
FROM dbo.KQ_AdjustRest A
LEFT OUTER JOIN dbo.V_KQPerson B ON B.Person_ID = A.Person_ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 視圖：考勤人員考勤信息與人員考勤信息關聯
-- =============================================
IF OBJECT_ID('dbo.V_KQAttendInfo') IS NOT NULL DROP VIEW dbo.V_KQAttendInfo
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.V_KQAttendInfo
AS
SELECT A.*, 
      D.Class_Name, 
      B.Person_Name, 
      B.Person_No, 
      B.Card_No, 
      C.Dept_Name, 
      C.Dept_No, 
      C.Dept_ID
FROM dbo.KQ_Person A
INNER JOIN dbo.ST_Person B ON A.Person_ID = B.Person_ID 
INNER JOIN dbo.ST_Department C ON B.Dept_ID = C.Dept_ID 
LEFT OUTER JOIN dbo.V_KQClassInfo D ON A.Class_No = D.Class_No
WHERE (A.Person_State = 0)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 視圖：考勤每天刷卡數據信息與人員檔案信息關聯
-- =============================================
IF OBJECT_ID('dbo.V_KQBrushCard') IS NOT NULL DROP VIEW dbo.V_KQBrushCard
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.V_KQBrushCard
AS
SELECT 
    dbo.KQ_BrushCard.Person_ID, 
    dbo.V_STPerson.person_no, 
    dbo.V_STPerson.person_name, 
    dbo.V_STPerson.dept_no, 
    dbo.V_STPerson.dept_name, 
    dbo.KQ_BrushCard.Card_No, 
    dbo.KQ_BrushCard.Brush_Date, 
    dbo.KQ_BrushCard.Brush_Data
FROM dbo.KQ_BrushCard 
LEFT OUTER JOIN dbo.V_STPerson ON dbo.KQ_BrushCard.Person_ID = dbo.V_STPerson.person_id


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 視圖：考勤補出勤補加班信息與人員考勤信息關聯
-- =============================================
IF OBJECT_ID('dbo.V_KQFillAttend') IS NOT NULL DROP VIEW dbo.V_KQFillAttend
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.V_KQFillAttend
AS
SELECT 
    A.ID_KEY, 
    A.Person_ID, 
    A.Fill_Date, 
    A.Fill_Hour, 
    A.Fill_Class, 
    A.Remark, 
    A.User_No, 
    A.Update_Date, 
    B.Person_No, 
    B.Person_Name, 
    B.Card_No, 
    B.Dept_No, 
    B.Dept_Name, 
    B.Dept_ID
FROM dbo.KQ_FillAttend A
LEFT OUTER JOIN dbo.V_KQPerson B ON B.Person_ID = A.Person_ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 視圖：考勤請假信息與人員考勤信息關聯
-- =============================================
IF OBJECT_ID('dbo.V_KQLeave') IS NOT NULL DROP VIEW dbo.V_KQLeave
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.V_KQLeave
AS
SELECT 
    A.ID_KEY, 
    A.Person_ID, 
    A.Kind_No, 
    A.Leave_Date, 
    A.Leave_Hour, 
    A.IsOutLeave, 
    A.Leave_Sect, 
    A.Remark, 
    A.User_No, 
    A.Update_Date,
    A.Leave_TimeBgn,
    A.Leave_TimeEnd, 
    B.Card_No, 
    B.Person_No, 
    B.Person_Name, 
    B.Dept_Name, 
    B.Dept_No, 
    B.Dept_ID,
    C.Kind_Name
FROM dbo.KQ_Leave A
LEFT OUTER JOIN dbo.V_KQPerson B ON B.Person_ID = A.Person_ID 
LEFT OUTER JOIN dbo.KQ_LeaveKind C ON A.Kind_No = C.Kind_No

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 視圖：考勤加班信息與人員考勤信息關聯
-- =============================================
IF OBJECT_ID('dbo.V_KQOvertime') IS NOT NULL DROP VIEW dbo.V_KQOvertime
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.V_KQOvertime
AS
SELECT 
    A.ID_KEY, 
    A.Person_ID, 
    A.Over_Date, 
    A.Over_Hour, 
    A.User_No, 
    A.Remark, 
    A.Update_Date, 
    B.Person_No, 
    B.Card_No, 
    B.Person_Name, 
    B.Dept_ID, 
    B.Dept_Name, 
    B.Dept_No
FROM dbo.KQ_Overtime A
LEFT OUTER JOIN dbo.V_KQPerson B ON A.Person_ID = B.Person_ID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 視圖：考勤增補刷卡信息與人員考勤信息關聯
-- =============================================
IF OBJECT_ID('dbo.V_KQSupplyTime') IS NOT NULL DROP VIEW dbo.V_KQSupplyTime
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.V_KQSupplyTime
AS
SELECT 
    A.ID_KEY, 
    A.Person_ID, 
    A.Supply_Date, 
    A.Supply_Time, 
    A.Supply_Reason, 
    A.Supply_Class, 
    A.Remark, 
    A.User_No, 
    A.Update_Date, 
    B.Person_No, 
    B.Person_Name, 
    B.Card_No, 
    B.Dept_No, 
    B.Dept_Name, 
    B.Dept_ID
FROM dbo.KQ_SupplyTime A
LEFT OUTER JOIN dbo.V_KQPerson B ON A.Person_ID = B.Person_ID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 人員班次調整歷史的視圖
-- =============================================
IF OBJECT_ID('dbo.V_KQAdjustClassHistory') IS NOT NULL DROP VIEW dbo.V_KQAdjustClassHistory
GO

CREATE VIEW dbo.V_KQAdjustClassHistory
AS
SELECT A.ID_KEY, A.Person_ID, 
      A.Adjust_Date, A.Class_No, 
      A.Remark, A.User_No, 
      A.Update_Date, B.Person_No, 
      B.Person_Name, B.Card_No, 
      B.Dept_No, B.Dept_Name, B.Dept_ID, 
      C.Class_Name
FROM dbo.KQ_AdjustClassHistory A
 LEFT OUTER JOIN dbo.V_KQPerson B ON A.Person_ID = B.Person_ID 
LEFT OUTER JOIN dbo.V_KQClassInfo C  ON A.Class_No = C.Class_No

GO

-- =============================================
-- 人員調班補休歷史的視圖
-- =============================================
IF OBJECT_ID('dbo.V_KQAdjustRestHistory') IS NOT NULL DROP VIEW dbo.V_KQAdjustRestHistory
GO

CREATE VIEW dbo.V_KQAdjustRestHistory
AS
SELECT A.ID_KEY, A.Person_ID, 
      A.Adjust_Date, A.Adjust_Hour, 
      A.Adjust_Class, A.Remark, 
      A.User_No, A.Update_Date, 
      B.Person_No, B.Person_Name, 
      B.Card_No, B.Dept_No, B.Dept_Name, 
      B.Dept_ID
FROM dbo.KQ_AdjustRestHistory A
LEFT OUTER JOIN  dbo.V_KQPerson B ON  A.Person_ID = B.Person_ID

GO

-- =============================================
-- 人員補出勤補加班歷史的視圖
-- =============================================
IF OBJECT_ID('dbo.V_KQFillAttendHistory') IS NOT NULL DROP VIEW dbo.V_KQFillAttendHistory
GO


CREATE VIEW dbo.V_KQFillAttendHistory
AS
SELECT A.ID_KEY, A.Person_ID, 
      A.Fill_Date, A.Fill_Hour, 
      A.Fill_Class, A.Remark, 
      A.User_No, A.Update_Date, 
      B.Person_No, B.Person_Name, 
      B.Card_No, B.Dept_No, B.Dept_Name, 
      B.Dept_ID
FROM dbo.KQ_FillAttendHistory A
LEFT OUTER JOIN dbo.V_KQPerson B ON  A.Person_ID = B.Person_ID

GO
-- =============================================
-- 人員請假登記歷史的視圖
-- =============================================
IF OBJECT_ID('V_KQLeaveHistory') IS NOT NULL DROP VIEW V_KQLeaveHistory
GO


CREATE VIEW V_KQLeaveHistory
AS
SELECT A.ID_KEY, A.Person_ID, 
      A.Kind_No, A.Leave_Date, 
      A.Leave_Hour, A.IsOutLeave, 
      A.Leave_Sect, A.Remark, 
      A.User_No, A.Update_Date, 
      A.Leave_TimeBgn,A.Leave_TimeEnd,
      B.Card_No, B.Person_No, 
      B.Person_Name, B.Dept_Name, 
      B.Dept_No,B.Dept_ID,
      C.Kind_Name 
FROM dbo.KQ_LeaveHistory A 
LEFT OUTER JOIN dbo.V_KQPerson B ON  A.Person_ID = B.Person_ID 
LEFT OUTER JOIN dbo.KQ_LeaveKind C ON A.Kind_No = C.Kind_No

GO
-- =============================================
-- 人員加班登記歷史的視圖
-- =============================================
IF OBJECT_ID('dbo.V_KQOvertimeHistory') IS NOT NULL DROP VIEW dbo.V_KQOvertimeHistory
GO


CREATE VIEW dbo.V_KQOvertimeHistory
AS
SELECT A.ID_KEY, A.Person_ID, 
      A.Over_Date, A.Over_Hour, 
      A.User_No, A.Remark, 
      A.Update_Date, B.Person_No, 
      B.Card_No, B.Person_Name, 
      B.Dept_ID, B.Dept_Name, 
      B.Dept_No
FROM dbo.KQ_OvertimeHistory A
LEFT OUTER JOIN dbo.V_KQPerson  B ON A.Person_ID = B.Person_ID

GO

-- =============================================
-- 人員增補刷卡登記歷史的視圖
-- =============================================
IF OBJECT_ID('dbo.V_KQSupplyTimeHistory') IS NOT NULL DROP VIEW dbo.V_KQSupplyTimeHistory
GO

CREATE VIEW dbo.V_KQSupplyTimeHistory
AS
SELECT A.ID_KEY, A.Person_ID, 
      A.Supply_Date, A.Supply_Time, 
      A.Supply_Reason, 
      A.Supply_Class, A.Remark, 
      A.User_No, A.Update_Date, 
      B.Person_No, B.Person_Name, 
      B.Card_No, B.Dept_No, B.Dept_Name, 
      B.Dept_ID
FROM dbo.KQ_SupplyTimeHistory A
LEFT OUTER JOIN dbo.V_KQPerson B ON A.Person_ID = B.Person_ID

GO

-- =============================================
-- 人員刷卡歷史的視圖
-- =============================================
IF OBJECT_ID('dbo.V_KQBrushCardHistory') IS NOT NULL DROP VIEW dbo.V_KQBrushCardHistory
GO

CREATE VIEW dbo.V_KQBrushCardHistory
AS
SELECT A.Person_ID, B.person_no, 
      B.person_name, B.dept_no, 
      B.dept_name, A.Card_No, 
      A.Brush_Date, A.Brush_Data, 
      A.ID_KEY
FROM dbo.KQ_BrushCardHistory A
LEFT OUTER JOIN dbo.V_STPerson B ON A.Person_ID = B.person_id

GO

-- =============================================
-- 取得班次的班次代號函數，返回代號（如A、AA等）.
-- 用于調整班次.
-- =============================================
if OBject_ID('dbo.KQ_fn_GetClassCode') IS NOT NULL
DROP FUNCTION dbo.KQ_fn_GetClassCode 
GO

CREATE FUNCTION dbo.KQ_fn_GetClassCode 
	(@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

-- ==============================================================
-- 取得最大班次編號，@ClassType為班次類型.
-- 返回最大班次編號.
-- ==============================================================

if OBject_ID('dbo.KQ_pd_GetMaxClassNo') IS NOT NULL
DROP PROCEDURE dbo.KQ_pd_GetMaxClassNo 
GO

CREATE PROCEDURE dbo.KQ_pd_GetMaxClassNo
	@ClassType VARCHAR(1) --指定的班次代號 'A' 正常班次，'B'規律班次，'C'靈活班次 'D'自定義班次
AS
   DECLARE @MaxClassNo VARCHAR(30),@ClassIndex int
   if @ClassType='A'
   begin
      Select @MaxClassNo=ISNull(MAX(Class_No),@ClassType+'00000') From dbo.KQ_Class
   end
   else if @ClassType='B'
   begin
     Select @MaxClassNo=ISNull(MAX(Class_No),@ClassType+'00000') From dbo.KQ_RuleClass
   end
  else if @ClassType='C'
   begin
     Select @MaxClassNo=ISNull(MAX(Class_No),@ClassType+'00000') From dbo.KQ_AgileClass
   end
  else if @ClassType='D'
   begin
     Select @MaxClassNo=ISNull(MAX(Class_No),@ClassType+'00000') From dbo.KQ_DefineClass
   end

   Set @ClassIndex=CAST(SUBSTRING(@MaxClassNo,2,5) AS INT)+1
   if @ClassIndex<99999
   begin
     SET @MaxClassNo= '00000'+Cast(@ClassIndex as Varchar)
     SET @MaxClassNo = SUBSTRING(@MaxClassNo,Len(@MaxClassNo)-4,5)
     SET @MaxClassNo = @ClassType+@MaxClassNo
  end
  else
    RAISERROR('Over Max Class_No  99999!', 16, 1)

 Select  @MaxClassNo
 
GO

-- =============================================
-- 得到人員的班次調整
-- =============================================

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

CREATE PROCEDURE dbo.KQ_pd_GetAdjustClass
 @ClassTmp VARCHAR(30),--轉化班次代號之后的臨時表名
 @PersonTmp VARCHAR(30),--部門樹中選擇的人員臨時表名
 @AdjustClassTmp VARCHAR(30),--班次調整臨時表
 @KQ_Adjustclass VARCHAR(30),--班次表（KQ_AdjustClass或者KQ_AdjustClassHistory）
 @Startdate DateTime--開始日期
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

 SET @SQLChild= ' SELECT Person_ID '+ @SQL_D +
                ' FROM (SELECT A.Person_ID,DATEDIFF(D, '''+CONVERT(VARCHAR(10),@Startdate,120)+''',Adjust_date)[D],'+
                       ' A.Class_No,C.ID_Num From '+@KQ_Adjustclass +' A '+
                       ' LEFT JOIN '+ @ClassTmp +' C ON C.Class_No=A.Class_No ' +
                       ' 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 ';
--取得日期對應的班次代號
 Set @I=1
 Set @SQL_D=''
 WHILE @I<32
 BEGIN
   SET @SQL_D=@SQL_D +',dbo.KQ_fn_GetClassCode(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=''
 --插入臨時表
 SET @SQL= 'INSERT INTO '+ @AdjustClassTmp+'(Person_ID,Person_Name,Dept_Name,Dept_NO,Person_NO'+ @SQL_F+')'
 SET @SQL=@SQL + ' SELECT E.Person_ID,Person_Name,Dept_Name,Dept_NO,Person_NO '+ @SQL_D+
             ' FROM '+@PersonTmp +' E LEFT JOIN ('+ @SQLChild + ') F ON F.Person_ID=E.Person_ID'+
             ' LEFT JOIN dbo.V_KQPerson B ON B.Person_ID=E.Person_ID ' 
EXEC(@SQL)

GO

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

CREATE PROCEDURE dbo.KQ_pd_UpdateAdjustClass
  @AClassCode VARCHAR(30),--轉化為班次代號之后的班次臨時表名
  @FAdjustEnd VARCHAR(30),--最后經處理的臨時表
  @FAdjustClassTmp VARCHAR(30),--在界面上所操作的當前臨時表
  @KQ_Adjustclass VARCHAR(30),--班次調整表名（KQ_AdjustClass或者KQ_AdjustClassHistory）
  @StartDate DATETIME,--表格中開始日期
  @User_No VARCHAR(20),
  @Remark VARCHAR(200)
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.Class_No,'+ @User_No +' AS User_NO,GetDate() AS Update_Date,'+@Remark +' AS Remark '+
                ' FROM '+ @FAdjustClassTmp +
                ' LEFT JOIN '+@AClassCode +'  A on A.Class_Code='+@Field+
                ' WHERE ISNull('+@Field+','''')<>'''''
    ELSE
     SET @SQL = @SQL + ' UNION ALL '+
             'SELECT Person_ID,'''+CAST(@FieldValue AS VARCHAR)+''' AS Adjust_Date,'+
                ' A.Class_No,'+ @User_No +' AS User_NO,GetDate() AS Update_Date,'+@Remark +' AS Remark '+
                ' FROM '+ @FAdjustClassTmp +
                ' LEFT JOIN '+@AClassCode +' A on A.Class_Code='+@Field+
                 ' WHERE ISNull('+@Field+','''')<>'''''
    SET @I=@I+1;
  END

-- 
  SET @SQL='INSERT INTO '+@FAdjustEnd +'(Person_ID,Adjust_Date,Class_No,User_NO,Update_Date,Remark)'+
           ' SELECT Person_ID,Adjust_Date,Class_No,User_NO,Update_Date,Remark  FROM ('+ @SQL +')A'
  EXEC(@SQL)

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

--修改班次日期相同的記錄
  SET @SQL ='UPDATE '+ @KQ_Adjustclass +' SET Class_No=A.Class_No,Remark=A.Remark,User_NO=A.User_No'+ 
            ',Update_Date=A.Update_Date FROM '+@FAdjustEnd+' A  WHERE '+@KQ_AdjustClass+'.Person_ID=A.Person_ID '+
           ' AND '+@KQ_AdjustClass+'.Adjust_Date=A.Adjust_Date '
  EXEC(@SQL)

--插入臨時表存在，但KQ_AdjustClass中不存在的記錄
  SET @SQL ='INSERT INTO '+ @KQ_Adjustclass +'(Person_ID,Adjust_Date,Class_No,User_NO,Update_Date,Remark)'+
          '  SELECT A.Person_ID,CONVERT(Varchar(10),A.Adjust_Date,126),'+
          '  A.Class_No,A.User_NO,A.Update_Date,A.Remark From '+ @FAdjustEnd+' A  ' +
          '  LEFT JOIN '+ @KQ_Adjustclass +' B On B.Person_ID=A.Person_ID and B.Adjust_Date=A.Adjust_Date '+
          '  WHERE ISNULL(B.Class_No,'''')=''''';
  
  EXEC(@SQL)

GO

-- =============================================
-- 同步人員考勤信息過程
-- =============================================
IF OBJECT_ID('dbo.KQ_pd_SynchronizePerson') IS NOT NULL
   DROP PROCEDURE dbo.KQ_pd_SynchronizePerson
GO

CREATE PROCEDURE dbo.KQ_pd_SynchronizePerson
   (@SystemNo VARCHAR(20),@UserNo VARCHAR(40))--子系統編號
AS
   DECLARE @Synchronize TINYINT --是否需要同步標志
	--1.檢查是否需要同步
   SELECT @Synchronize=Update_State1 FROM ST_SubSystem WHERE System_No=@SystemNo
   IF @Synchronize=0 
   BEGIN --需要同步
      --2.設置重新獲得使用考勤子系統權限的人員的標志
      UPDATE dbo.KQ_Person SET Person_State=0 WHERE Person_State=1
         AND EXISTS(SELECT Person_ID FROM dbo.ST_SubSysRight WHERE Person_ID=KQ_Person.Person_ID AND System_No=@SystemNo) --子系統權限
         AND EXISTS(SELECT Person_ID FROM dbo.ST_Card WHERE Person_ID=KQ_Person.Person_ID) --有卡號人員
      --3.設置沒有權限使用考勤子系統或在核心平台已經被注銷的人員的標志
      UPDATE dbo.KQ_Person SET Person_State=1 WHERE Person_State=0
         AND (NOT EXISTS(SELECT Person_ID FROM dbo.ST_SubSysRight WHERE Person_ID=KQ_Person.Person_ID AND System_No=@SystemNo) --子系統權限
          OR EXISTS(SELECT Person_ID FROM dbo.ST_Person WHERE Person_ID=KQ_Person.Person_ID AND Is_Del=1)
          OR NOT EXISTS(SELECT Person_ID FROM dbo.ST_Card WHERE Person_ID=KQ_Person.Person_ID)) --無卡號人員
      --4.插入有使用考勤子系統且考勤信息不存在的人員
      INSERT INTO dbo.KQ_Person(Person_ID,WorkTime_Mode,Overtime_Mode,Rest_Mode,Rest_Days,Person_State,User_No,Update_Date)
      SELECT A.Person_ID,1,1,1,'6,7',0,@UserNo,GETDATE() FROM dbo.ST_Person A
        LEFT JOIN dbo.KQ_Person B ON B.Person_ID=A.Person_ID
       WHERE Is_Del=0 AND B.Person_State IS NULL
         AND EXISTS(SELECT Person_ID FROM dbo.ST_SubSysRight WHERE Person_ID=A.Person_ID AND System_No=@SystemNo) --子系統權限
         AND EXISTS(SELECT Person_ID FROM dbo.ST_Card WHERE Person_ID=A.Person_ID) --有卡號人員
      --5.更改核心平台同步成功的標志
      UPDATE dbo.ST_SubSystem SET Update_State1=1 WHERE System_No=@SystemNo
   END
   SELECT @Synchronize
GO

-- =============================================
-- 用于合并刷卡數據.
-- =============================================
if OBject_ID('dbo.KQ_fn_TogetherBrush') IS NOT NULL
DROP FUNCTION dbo.KQ_fn_TogetherBrush 
GO

CREATE FUNCTION dbo.KQ_fn_TogetherBrush(@aPerson_ID int,@aDate VARCHAR(10),
  @aFlag int,@aCard_No VARCHAR(12))
RETURNS VARCHAR(255)
AS
BEGIN
  DECLARE @Brushs VARCHAR(1000) 
  SET @Brushs='' 
  /*****/
  if @aCard_No=''--根據person_id整理
  begin
    if @aFlag=10 --表示整理考勤記錄
      SELECT @Brushs=@Brushs+Brush_Time+' ' 
        FROM (SELECT TOP 100 Brush_Time FROM dbo.KQ_DownLoad 	
         WHERE Person_ID=@aPerson_ID
         and Brush_Date =@aDate
         and Data_Flag>=10 and Data_Flag<20  
         ORDER BY Brush_Time) a

    /****/
    else --表示整理外進出的記錄
      SELECT @Brushs=@Brushs+Brush_Time+' ' 
        FROM (SELECT TOP 100 Brush_Time FROM dbo.KQ_DownLoad 	
         WHERE Person_ID=@aPerson_ID
         and Brush_Date =@aDate
         and   Data_Flag=@aFlag 
         ORDER BY Brush_Time) a
  end
  else  --根據卡號整理
    SELECT @Brushs=@Brushs+Brush_Time+' ' 
      FROM (SELECT TOP 100 Brush_Time FROM dbo.KQ_DownLoad 	
       WHERE Card_No=@aCard_No
       and Brush_Date =@aDate
       ORDER BY Brush_Time) a
  /****/
  RETURN(@Brushs)
END
GO

-- =============================================
-- 得到自定義班次信息，從起始日期后31天的自定義班次
-- =============================================

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

CREATE PROCEDURE dbo.KQ_pd_GetDefineClass
 @ClassTmp VARCHAR(30),--轉化為班次代號的臨時表
 @DefineTmp VARCHAR(30),--自定義班次臨時表名
 @AdjustClassTmp VARCHAR(30),--班次調整臨時表
 @Startdate DateTime--開始日期
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
 SET @SQLChild = ' SELECT Class_No '+ @SQL_D +
                   ' FROM (SELECT A.Class_No,DATEDIFF(D, '''+CONVERT(VARCHAR(10),@Startdate,120)+''',WorkTime_date)[D],'+
                   '       A.Day_ClassNo,C.ID_NUM From dbo.KQ_DefineClassSub A '+
                   '  LEFT JOIN '+ @ClassTmp +' C ON C.Class_No=A.Day_ClassNo ' +
                   '  WHERE WorkTime_date>='''+CONVERT(VARCHAR(10),@Startdate,120) +''''+
                   '  and Exists (Select Class_No From '+@DefineTmp +' C Where A.Class_No=C.Class_No)'+
                   '  )D  '+
                   ' GROUP BY Class_No ';

--取得日期對應的班次代號

 Set @I=1
 Set @SQL_D=''
 WHILE @I<32
 BEGIN
   SET @SQL_D=@SQL_D +',dbo.KQ_fn_GetClassCode(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=''
 --插入臨時表
 SET @SQL= 'INSERT INTO '+ @AdjustClassTmp+'(Class_no,Class_Name'+ @SQL_F+')'
 SET @SQL=@SQL + ' SELECT E.Class_NO,Class_Name '+ @SQL_D+
             ' FROM '+@DefineTmp +' E LEFT JOIN ('+ @SQLChild + ') A ON A.Class_No=E.Class_No';
 EXEC(@SQL)

GO

-- =============================================
-- 更新自定義班次調整
-- =============================================

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

CREATE PROCEDURE dbo.KQ_pd_UpdateDefineClass
  @AClassCode VARCHAR(30),--轉化為班次代號的班次臨時表
  @FAdjustEnd VARCHAR(30),--最后班次調整臨時表
  @FAdjustClassTmp VARCHAR(30),--當前在界面上所操作的臨時表名
  @StartDate DATETIME--表格中的開始日期
AS
DECLARE  @SQL VARCHAR(8000);
DECLARE  @SQL_D VARCHAR(8000);
DECLARE  @I  Int,@FieldValue VARCHAR(100),@Field VARCHAR(20);
DECLARE  @FieldValueEnd 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,120);
    SET @Field=CAST('D'+CAST(@i as VARCHAR)as VARCHAR)
    IF @SQL = ''
     SET @SQL = 'SELECT B.Class_No,'''+CAST(@FieldValue AS VARCHAR)+''' AS Worktime_Date, '+
                ' A.Class_No AS Day_ClassNo,DATEPART(WEEKDAY,'+CHAR(39)+CAST(@FieldValue AS VARCHAR)+CHAR(39) +')-1 AS OfWeek'+
                 ' FROM '+@FAdjustClassTmp+' B LEFT JOIN '+@AClassCode+' A ON A.Class_Code=B.'+@Field+
                ' WHERE ISNull('+@Field+','''')<>'''''
   ELSE
    SET @SQL = @SQL + ' UNION ALL '+
             'SELECT B.Class_No,'''+CAST(@FieldValue AS VARCHAR)+''' AS Worktime_Date, '+
                ' A.Class_No AS Day_ClassNo,DATEPART(WEEKDAY,'+CHAR(39)+CAST(@FieldValue AS VARCHAR)+CHAR(39)+')-1 AS OfWeek'+
                 ' FROM '+@FAdjustClassTmp+' B LEFT JOIN '+@AClassCode+' A ON A.Class_Code=B.'+@Field+
                ' WHERE ISNull('+@Field+','''')<>'''''
   SET @I=@I+1;
 END

-- 
  SET @SQL='INSERT INTO '+@FAdjustEnd +'(Class_No,Worktime_Date,Day_ClassNo,OfWeek)'+
           ' SELECT Class_No,Worktime_Date,Day_ClassNo,OfWeek  FROM ('+ @SQL +')A'
 
  EXEC(@SQL)

--刪除不在臨時表的記錄
   SET @FieldValue =CONVERT(VARCHAR(10),@StartDate,120);
   SET @FieldValueEnd =CONVERT(VARCHAR(10),@StartDate+31,120);
  SET @SQL = 'DELETE KQ_DefineClassSub WHERE Exists (Select Class_NO From '+@FAdjustClassTmp+' A  '+
                '  Where A.Class_NO=KQ_DefineClassSub.Class_NO)'+
                ' AND NOT Exists(SELECT Class_NO FROM '+@FAdjustEnd+' B '+
                                ' WHERE B.Class_NO=dbo.KQ_DefineClassSub.Class_NO '+
                                ' AND B.Worktime_Date=KQ_DefineClassSub.Worktime_Date)'+
                ' AND WorkTime_Date>='+CHAR(39)+CAST(@FieldValue AS VARCHAR)+CHAR(39)+
                ' AND WorkTime_Date<='++CHAR(39)+CAST(@FieldValueEND AS VARCHAR)+CHAR(39)

 EXEC(@SQL)

--修改班次日期相同的記錄
 SET @SQL = 'UPDATE dbo.KQ_DefineClassSub SET '+
                'Day_ClassNo=B.Day_ClassNo'+
                ',OfWeek=(CASE WHEN B.OfWeek>0 THEN B.OfWeek ELSE 0 end)'+
                ' FROM '+ @FAdjustEnd+' B '+
                ' WHERE dbo.KQ_DefineClassSub.Class_No=B.Class_No '+
                ' AND dbo.KQ_DefineClassSub.Worktime_Date=B.Worktime_Date';
  EXEC(@SQL)

--插入臨時表存在，但KQ_DefineClassSub中不存在的記錄

  SET @SQL = ' INSERT INTO dbo.KQ_DefineClassSub(Class_No,Worktime_Date,Day_ClassNo,OfWeek)'+
                 ' SELECT A.Class_No,CONVERT(VARCHAR(10),A.Worktime_Date,120) AS Worktime_Date,A.Day_ClassNo,'+
                 ' (CASE WHEN A.OfWeek>0 THEN A.OfWeek ELSE 0 END)  FROM '+@FAdjustEnd+' A '+
                 ' WHERE NOT EXISTS(SELECT Class_NO FROM dbo.KQ_DefineClassSub B '+
                                   ' WHERE B.Class_No=A.Class_No and B.Worktime_Date=A.Worktime_Date)';

  
  EXEC(@SQL)

GO


-- =============================================
-- 批量處理人員的請假登記
-- =============================================

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

CREATE PROCEDURE dbo.KQ_pd_BatchUpdateLeave
  @TblName VARCHAR(30),--表名KQ_Leave或者KQ_LeaveHistory
  @SelPersonTmp VARCHAR(30),--部門樹中所選擇的人員
  @StartDate DATETIME,--開始日期（請假日期）
  @EndDate DATETIME,--結束日期
  @Kind_No VARCHAR(30),--假種編號
  @Leave_Hour Real,--請假時長
  @Leave_Sect Real,--請假時段
  @IsOutLeave int,--是否段外
  @User_No VARCHAR(20),--操作員
  @Remark VARCHAR(300),
  @Leave_TimeBgn Datetime,

  @Leave_TimeEnd Datetime,
  @Row Int output --返回成功記錄數
AS
DECLARE  @SQL Varchar(8000);
DECLARE  @LeaveDate DATETIME
DECLARE  @lsLeaveDate VARCHAR(10)
DECLARE  @W VARCHAR(2),@D VARCHAR(2)


  SET @SQL=''
  SET @ROW=0
  SET @LeaveDate=@StartDate 
 
  WHILE @LeaveDate <= @EndDate --循環處理
  BEGIN
    SET @lsLeaveDate= CONVERT(VARCHAR,@LeaveDate,120)
    SET @D=SUBSTRING(CONVERT(Varchar,@LeaveDate,126),9,2) 
    SET @W= CASE WHEN DATEPART(dw ,@LeaveDate)-1=0 THEN 7 ELSE  DATEPART(dw ,@LeaveDate)-1 End --取得星期
    SET @SQL = ' INSERT INTO  '   +  @TblName+'  '+'(Person_ID,Kind_No,Leave_Date,Leave_Hour,IsOutLeave,Leave_Sect,Remark,User_No,Leave_TimeBgn,Leave_TimeEnd,Update_Date)'+
               ' SELECT A.Person_ID,'''+@Kind_No +''' AS Kind_No ,''' + @lsLeaveDate + ''' AS Leave_Date, ' + CONVERT(VARCHAR,@Leave_Hour) + ' AS Leave_Hour,' +
                      CONVERT(VARCHAR,@IsOutLeave)  + ' AS IsOutLeave,' + CONVERT(VARCHAR,@Leave_Sect)+ ' AS Leave_Sect,''' +
                      @Remark + ''' AS Remark,''' +@User_No +''' AS User_No,'''+CONVERT(VARCHAR,@Leave_TimeBgn,120) +''','''+CONVERT(VARCHAR,@Leave_TimeEnd,120) +''',Getdate() AS Update_Date '+
               ' FROM '+ @SelPersonTmp+' A '+
               ' LEFT JOIN  '  +  @TblName +' B ON A.Person_ID=B.Person_ID'+' AND Leave_Date='''+@lsLeaveDate+''' AND Kind_No='''+@Kind_No+''''+
               ' WHERE EXISTS(SELECT Person_ID FROM ( '+' SELECT Person_ID,(CASE WHEN Rest_Mode=1 THEN '+
                      ' (CASE WHEN SUBSTRING(Rest_Days,CHARINDEX('''+@W+''',Rest_Days)-1,1)<>''.'' AND  '+
                      'CHARINDEX('''+@W+''',Rest_Days)>0 '+
                       ' THEN 1 ElSE 0 END) '+
                      ' ELSE CHARINDEX('''+@D+''',Rest_Days) END) AS Rest '+
                     ' FROM dbo.KQ_Person )C WHERE C.Person_ID=A.Person_ID AND C.Rest=0 '+
                      ' )'+
                    ' AND B.Kind_NO IS NULL'

   EXEC(@SQL);

   SET @ROW=@ROW+@@ROWCOUNT
   SET @LeaveDate=@LeaveDate+1--日期加1
  end;
GO


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

--數據分頁存儲過程
Create PROCEDURE SQLPageData
  @ASql          VARCHAR(8000),
  @APageNo       INT, --頁號
  @APageSize     INT, --每頁記錄數
  @AOnceMoreSize INT, --每頁多取下頁的前N條
  @APageCount    INT OUTPUT,
  @ARecordCount  INT OUTPUT
AS
BEGIN
  DECLARE @t int

  EXEC(@ASql)  
  SELECT @ARecordCount = @@rowcount
  IF (@ARecordCount <= @APageSize)
    SELECT @APageCount=1
  ELSE 
     SELECT @APageCount=CEILING((@ARecordCount - @AOnceMoreSize)/(@APageSize*1.0-@AOnceMoreSize))

  IF (@APageNo < 0)  SELECT @APageNo = @APageCount + @APageNo + 1

  SELECT @t = CASE  @APageNo
    WHEN 0   THEN @APageSize * -1
    WHEN  1  THEN 0
    ELSE (@APageNo-1)*(@APageSize-@AOnceMoreSize)
    END 

  SELECT @ASql= @ASql + ' SELECT * FROM #tmpData WHERE ID  BETWEEN ' + CONVERT(VARCHAR(10),@t+1) +
                        ' AND ' + CONVERT(VARCHAR(10), @t+@APageSize) + ' ORDER BY ID ' +
                        ' DROP TABLE #tmpData'
  EXEC(@ASql)  
 END
SET QUOTED_IDENTIFIER OFF
GO


--創建數據庫
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'STPhoto')
  CREATE DATABASE [STPhoto]  
GO

IF OBJECT_ID('STPhoto.dbo.ST_Photo') IS NULL
   CREATE TABLE STPhoto.dbo.ST_Photo (
          FileName  varchar(128) NULL,
          Photo     image NULL
   )
GO



IF NOT EXISTS (SELECT * FROM STPhoto.dbo.sysindexes WHERE NAME='IX_STPhoto_FileName')
 CREATE  INDEX [IX_STPhoto_FileName] ON STPHOTO.[dbo].[ST_Photo]([FileName]) ON [PRIMARY]
GO

