-- =============================================
-- 正常刷卡记录报表的视图
-- =============================================
IF OBJECT_ID('dbo.V_MJNormalRecord') IS NOT NULL DROP VIEW dbo.V_MJNormalRecord
GO

CREATE VIEW dbo.V_MJNormalRecord AS
   SELECT N.BrushCard_Time, N.Ctrl_No, C.Ctrl_Name, N.Door_No, D.Door_Name, 
          N.Card_No, P.Person_No, P.Person_Name, N.Event_Code, f.Event_Name, 
          N.Is_InDoor,g.Dept_No,g.Dept_Name
     FROM dbo.MJ_NormalRecord N 
     LEFT OUTER JOIN dbo.MJ_Controller C ON N.Ctrl_No = C.Ctrl_No 
     LEFT OUTER JOIN dbo.MJ_Door D ON N.Ctrl_No = D.Ctrl_No AND N.Door_No = D.Door_No 
     LEFT OUTER JOIN dbo.ST_Person P ON N.Person_ID = P.Person_ID
     LEFT OUTER JOIN dbo.MJ_EventInfo f ON N.Event_Code = f.Event_Code
     LEFT OUTER JOIN dbo.ST_DepartMent g on P.Dept_ID = g.Dept_ID
GO

-- =============================================
-- 异常刷卡记录报表的视图
-- =============================================
IF OBJECT_ID('dbo.V_MJAbnormRecord') IS NOT NULL DROP VIEW dbo.V_MJAbnormRecord
GO

CREATE VIEW dbo.V_MJAbnormRecord AS
   SELECT A.BrushCard_Time, A.Ctrl_No, C.Ctrl_Name, A.Door_No, D.Door_Name, A.Card_No, 
          P.Person_No, P.Person_Name, f.Event_Name, A.Event_Code,
          A.Is_InDoor,g.Dept_No,g.Dept_Name
     FROM dbo.MJ_AbnormRecord A 
     LEFT OUTER JOIN dbo.MJ_Controller C ON A.Ctrl_No = C.Ctrl_No 
     LEFT OUTER JOIN dbo.MJ_Door D ON A.Ctrl_No = D.Ctrl_No And A.Door_No = D.Door_No 
     LEFT OUTER JOIN dbo.ST_Person P ON A.Card_No = P.Card_No 
     LEFT OUTER JOIN dbo.MJ_EventInfo f ON A.Event_Code = f.Event_Code
     LEFT OUTER JOIN dbo.ST_DepartMent g on P.Dept_ID = g.Dept_ID
GO

-- =============================================
-- 报警事件记录报表的视图
-- =============================================
IF OBJECT_ID('dbo.V_MJAlarmRecord') IS NOT NULL DROP VIEW dbo.V_MJAlarmRecord
GO

CREATE VIEW dbo.V_MJAlarmRecord AS
   SELECT A.Alarm_Time, A.Ctrl_No, C.Ctrl_Name, A.Dot_Name, f.Event_Name, 
          A.Event_Code
     FROM dbo.MJ_AlarmRecord A 
     LEFT OUTER JOIN dbo.MJ_Controller C ON A.Ctrl_No = C.Ctrl_No 
     LEFT OUTER JOIN dbo.MJ_EventInfo f ON A.Event_Code = f.Event_Code
GO

-- =============================================
-- 人员门禁信息的视图
-- =============================================
IF OBJECT_ID('dbo.V_MJPersonInfo') IS NOT NULL DROP VIEW dbo.V_MJPersonInfo
GO

CREATE VIEW dbo.V_MJPersonInfo AS
SELECT A.Person_ID,
       P.Person_No,
       P.Person_Name,
       P.Card_No,
       P.OnWork_Date,
       A.Door_Pwd,
       A.Person_State,
       P.Dept_ID,
       D.Dept_No,
       D.Dept_Name
FROM dbo.MJ_Person A
LEFT JOIN dbo.ST_Person P ON A.Person_ID = P.Person_ID
INNER JOIN dbo.ST_Department D ON P.Dept_ID = D.Dept_ID
WHERE (A.Person_State = 0)
GO

-- =============================================
-- 人员进出时段的视图
-- =============================================
IF OBJECT_ID('dbo.V_MJPersonIntOutTime') IS NOT NULL DROP VIEW dbo.V_MJPersonIntOutTime
GO

CREATE VIEW dbo.V_MJPersonIntOutTime AS
SELECT R.Person_ID,
       P.Person_No,
       R.Ctrl_No,
       C.Ctrl_Name,
       R.Door_No,
       D.Door_Name,
       R.PsnTime_No,
       T.PsnTime_Name
FROM dbo.MJ_PsnDoorRight R
LEFT JOIN dbo.ST_Person P ON R.Person_ID = P.Person_ID
LEFT JOIN dbo.MJ_Controller   C ON R.Ctrl_No = C.Ctrl_No
LEFT JOIN dbo.MJ_Door   D ON R.Door_No = D.Door_No AND R.Ctrl_No = D.Ctrl_No
LEFT JOIN dbo.MJ_PsnInOutTime T ON R.PsnTime_No = T.PsnTime_No
GO

-- =============================================
-- 控制器联动的视图
-- =============================================
IF OBJECT_ID('dbo.V_MJDotLinkage') IS NOT NULL DROP VIEW dbo.V_MJDotLinkage
GO

CREATE VIEW dbo.V_MJDotLinkage AS
SELECT L.Linkage_No,
       L.Linkage_Name,
       L.Ctrl_No,
       C.Ctrl_Name,
       C.Ctrl_Type,
       L.Dot_No,
       D.Dot_Name,
       L.Is_Input,
       L.Dot_State
FROM dbo.MJ_DotLinkage L
LEFT JOIN dbo.MJ_Controller C ON L.Ctrl_No = C.Ctrl_No
LEFT JOIN dbo.MJ_CtrlDot    D ON L.Dot_No =  D.Dot_No AND L.Ctrl_No = D.Ctrl_No
GO

-- =============================================
-- 取得最大的时段编号函数
-- =============================================

IF OBJECT_ID('dbo.MJ_fn_GetMaxTimeNo') IS NOT NULL
	DROP FUNCTION dbo.MJ_fn_GetMaxTimeNo
GO

CREATE FUNCTION dbo.MJ_fn_GetMaxTimeNo
	(@Tag TINYINT =0) --标志(0-表示人进出时段,1-表示门开关时段)
RETURNS INT
AS
BEGIN
   DECLARE @MaxTimeNo INT,@Count INT
   IF @Tag = 0 --人进出时段
   BEGIN
      SELECT @MaxTimeNo=ISNULL(MAX(PsnTime_No), 0),@Count=COUNT(*) FROM MJ_PsnInOutTime
      IF @MaxTimeNo <> @Count --中间有不连续的时段编号
      BEGIN
         SELECT @MaxTimeNo=ISNULL(MIN(Time_No),0)
           FROM (SELECT A.PsnTime_No,(SELECT TOP 1 PsnTime_No FROM MJ_PsnInOutTime WHERE PsnTime_No<A.PsnTime_No ORDER BY PsnTime_No DESC) Time_No
                   FROM MJ_PsnInOutTime A 
                ) B
          WHERE PsnTime_No-Time_No<>1
      END      
   END
   ELSE --门开关时段
   BEGIN
     SELECT @MaxTimeNo=ISNULL(MAX(DoorTime_No), 0),@Count=COUNT(*) FROM MJ_DoorSwitchTime
      IF @MaxTimeNo <> @Count --中间有不连续的时段编号
      BEGIN
         SELECT @MaxTimeNo=ISNULL(MIN(Time_No),0)
           FROM (SELECT A.DoorTime_No,(SELECT TOP 1 DoorTime_No FROM MJ_DoorSwitchTime WHERE DoorTime_No<A.DoorTime_No ORDER BY DoorTime_No DESC) Time_No
                   FROM MJ_DoorSwitchTime A 
                ) B
          WHERE DoorTime_No-Time_No<>1
      END       
   END
   SET @MaxTimeNo = @MaxTimeNo + 1
   IF @MaxTimeNo > 50 SET @MaxTimeNo = -1
   RETURN @MaxTimeNo
END
GO

-- =============================================
-- 同步核心平台的人员信息到门禁系统的过程
-- =============================================
IF OBJECT_ID('MJ_pd_SynchronizePerson') IS NOT NULL
   DROP PROCEDURE MJ_pd_SynchronizePerson
GO

CREATE PROCEDURE MJ_pd_SynchronizePerson
   (@SystemNo VARCHAR(20),@UserNo VARCHAR(40))--子系统编号
AS
   SET @SystemNo='MJ'
   DECLARE @Synchronize TINYINT --是否需要同步标志
   DECLARE @IsUseCardSN TINYINT --是否使用IC卡SN作为卡号
	--1.检查是否需要同步
   SELECT @Synchronize=Update_State1 FROM ST_SubSystem WHERE System_No=@SystemNo
   IF @Synchronize=0 
   BEGIN --需要同步
      --2.设置重新获得使用门禁子系统权限的人员的标志
      UPDATE MJ_Person SET Person_State=0 WHERE Person_State=1
         AND EXISTS(SELECT Person_ID FROM ST_SubSysRight WHERE Person_ID=MJ_Person.Person_ID AND System_No=@SystemNo) --子系统权限
         AND EXISTS(SELECT Person_ID FROM ST_Card WHERE Person_ID=MJ_Person.Person_ID) --有卡号人员
      --3.设置没有权限使用门禁子系统或在核心平台已经被注销的人员的标志
      UPDATE MJ_Person SET Person_State=1 WHERE Person_State=0
         AND (NOT EXISTS(SELECT Person_ID FROM ST_SubSysRight WHERE Person_ID=MJ_Person.Person_ID AND System_No=@SystemNo) --子系统权限
          OR EXISTS(SELECT Person_ID FROM ST_Person WHERE Person_ID=MJ_Person.Person_ID AND Is_Del=1)
          OR NOT EXISTS(SELECT Person_ID FROM ST_Card WHERE Person_ID=MJ_Person.Person_ID)) --无卡号人员
      --4.插入有使用门禁子系统且门禁信息不存在的人员
      SELECT @IsUseCardSN=Param_Value FROM MJ_sysParam WHERE Param_Code='ISUSECARD_SN'
      IF @IsUseCardSN IS NULL SET @IsUseCardSN=0
      --
      IF @IsUseCardSN = 0 --使用卡号
      BEGIN
         INSERT INTO MJ_Person(Person_ID,Door_Pwd,Person_State,User_No,Update_Date,Acs_Card)
         SELECT A.Person_ID,'111111',0,@UserNo,GETDATE(),dbo.MJ_fn_Card10To8(A.Card_No) FROM ST_Person A
           LEFT JOIN MJ_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 ST_SubSysRight WHERE Person_ID=A.Person_ID AND System_No=@SystemNo) --子系统权限
            AND EXISTS(SELECT Person_ID FROM ST_Card WHERE Person_ID=A.Person_ID) --有卡号人员
         --4.1转换变更卡号的卡号
         UPDATE MJ_Person SET Acs_Card=dbo.MJ_fn_Card10To8(A.Card_No)
           FROM ST_Person A WHERE MJ_Person.Person_ID=A.Person_ID AND ISNULL(MJ_Person.Acs_Card,0)<>dbo.MJ_fn_Card10To8(A.Card_No)
     END
     ELSE  --使用SN
     BEGIN
        INSERT INTO MJ_Person(Person_ID,Door_Pwd,Person_State,User_No,Update_Date,Acs_Card)
        SELECT A.Person_ID,'111111',0,@UserNo,GETDATE(),dbo.MJ_fn_Card10To8(ISNULL(A.Card_SN,0)) FROM ST_Person A
          LEFT JOIN MJ_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 ST_SubSysRight WHERE Person_ID=A.Person_ID AND System_No=@SystemNo) --子系统权限
           AND EXISTS(SELECT Person_ID FROM ST_Card WHERE Person_ID=A.Person_ID) --有卡号人员
        --4.1转换变更卡号的卡号
        UPDATE MJ_Person SET Acs_Card=dbo.MJ_fn_Card10To8(ISNULL(A.Card_SN,0))
          FROM ST_Person A WHERE MJ_Person.Person_ID=A.Person_ID AND ISNULL(MJ_Person.Acs_Card,0)<>dbo.MJ_fn_Card10To8(ISNULL(A.Card_SN,0)) 
     END
      --5.更改核心平台同步成功的标志
      UPDATE ST_SubSystem SET Update_State1=1 WHERE System_No=@SystemNo      
   END
   SELECT @Synchronize
GO

-- =============================================
-- 增加控制器后，自动生成门与控制点的过程
-- =============================================

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

CREATE PROCEDURE dbo.MJ_pd_GenDotAndDoor
@ctrl_no varchar(4),
@is_Extend int,
@Ctrl_Type int
AS

DECLARE @ResType int, @IdxNo int, @ResNumber int,@ResName varchar(20),@IsExtend bit

DECLARE  tmp_resparam  CURSOR FOR SELECT  Res_Type, Idx_No, Res_Name, Res_Number,Is_Extend FROM  MJ_Resparam  
   where Ctrl_Type=@Ctrl_Type and (Is_Extend=0 or Is_Extend = @Is_Extend) order by Res_Type ,Idx_No

OPEN tmp_resparam
  FETCH NEXT FROM tmp_resparam into @ResType,  @IdxNo, @ResName,  @ResNumber,@IsExtend

  WHILE @@fetch_status = 0
  BEGIN
    Set @IdxNo=1
    WHILE @ResNumber >0    
    BEGIN
      ---生成门--
      if @ResType=0 
	INSERT  INTO MJ_Door (Ctrl_No, Door_No, Door_Name,DoorTime_No)  
	  VALUES(@ctrl_no,convert(varchar,@IdxNo), @ResName+'('+convert(varchar,@IdxNo)+')',1)
      else
       ---生成点--
        INSERT INTO MJ_CtrlDot (Ctrl_No, Dot_No, Dot_Name,  Dot_Type, Is_Input,Is_Extend) 
          VALUES(@ctrl_no, Convert(varchar,@ResType)+'_'+ Convert(varchar,@IdxNo),@ResName+'('+convert(varchar,@IdxNo)+')', 
                 @ResType, (Case when @ResType=1 then 1 else 0 end),@IsExtend)

      SELECT @ResNumber = @ResNumber - 1
      SELECT @IdxNo = @IdxNo + 1
    END

    FETCH NEXT FROM tmp_resparam into @ResType,  @IdxNo, @ResName,  @ResNumber,@IsExtend
  END
  
CLOSE tmp_resparam
DEALLOCATE tmp_resparam

GO

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

CREATE PROCEDURE dbo.MJ_pd_SaveAlarmRecord
   @CardNo    VARCHAR(20),
   @OccurTime DATETIME,
   @CtrlNo    VARCHAR(20),
   @DoorNo    INT,   
   @EventCode INT,
   @DoorFlag  VARCHAR(20),
   @IsInDoor  BIT
AS
   DECLARE @DotName VARCHAR(40),@DotNo VARCHAR(40),@AcsCard VARCHAR(20)
   SET @AcsCard=@CardNo
   SELECT @CardNo=A.Card_No
     FROM MJ_Person C
     LEFT JOIN ST_Person A ON A.Person_ID=C.Person_ID
    WHERE Acs_Card=@AcsCard
   IF (@EventCode>63) AND (@EventCode<80)
   BEGIN
      IF (@EventCode>63) AND (@EventCode<72) --输入点
         SET @DotNo='1_'+CAST((@EventCode-63) AS VARCHAR)
      ELSE --输出点
         SET @DotNo='2_'+CAST((@EventCode-71) AS VARCHAR)
      SELECT @DotName=Dot_Name FROM MJ_CtrlDot WHERE Ctrl_No=@CtrlNo AND Dot_No=@DotNo
   END
   ELSE
   BEGIN
      SELECT @DotName=Door_Name+@DoorFlag FROM MJ_Door WHERE Ctrl_No=@CtrlNo AND Door_No=@DoorNo
   END
   INSERT INTO MJ_AlarmRecord(Event_Code,Ctrl_No,Dot_Name,Alarm_Time)
   VALUES(@EventCode,@CtrlNo,@DotName,@OccurTime)
GO

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

CREATE PROCEDURE dbo.MJ_pd_SaveNormalRecord
   @CardNo    VARCHAR(20),
   @OccurTime DATETIME,
   @CtrlNo    VARCHAR(20),
   @DoorNo    INT,
   @EventCode INT,
   @DoorFlag  VARCHAR(20),
   @IsInDoor  BIT
AS
   DECLARE @PersonID INT,@DoorName VARCHAR(40),@AcsCard VARCHAR(20)
   DECLARE @APBNo VARCHAR(4),@HideBackNo VARCHAR(4)
   SET @AcsCard=@CardNo
   SELECT @PersonID=A.Person_ID,@CardNo=A.Card_No
     FROM MJ_Person C
     LEFT JOIN ST_Person A ON A.Person_ID=C.Person_ID
    WHERE Acs_Card=@AcsCard
   IF @PersonID IS NULL SET @PersonID=-1
   SELECT @DoorName=Door_Name+@DoorFlag FROM MJ_Door WHERE Ctrl_No=@CtrlNo AND Door_No=@DoorNo
   INSERT INTO MJ_NormalRecord(Person_ID,Card_No,Ctrl_No,Door_No,Door_Name,BrushCard_Time,Event_Code,Is_InDoor)
   VALUES(@PersonID,@CardNo,@CtrlNo,@DoorNo,@DoorName,@OccurTime,@EventCode,@IsInDoor)
   IF @EventCode=0 OR @EventCode=2
   BEGIN
      IF @IsInDoor=1
      BEGIN
         UPDATE MJ_Person SET LastIn_Time=@OccurTime,LastIn_Ctrl=@CtrlNo,LastIn_Door=@DoorNo
          WHERE Person_ID=@PersonID AND (LastIn_Time IS NULL OR LastIn_Time<@OccurTime)
         IF @PersonID<>-1
         BEGIN
            SELECT @APBNo=CASE WHEN Is_APB=1 THEN ISNULL(APB_No,'') ELSE '' END,
                   @HideBackNo=CASE WHEN Is_HiddenBack=1 THEN ISNULL(HideBack_No,'') ELSE '' END
              FROM MJ_Door
             WHERE Ctrl_No=@CtrlNo AND Door_No=@DoorNo 
            IF @APBNo<>''
            BEGIN
               IF NOT EXISTS(SELECT Person_ID FROM MJ_PersonLastInfo WHERE Person_ID=@PersonID AND APB_No=@APBNo)
                  INSERT INTO MJ_PersonLastInfo(Person_ID,APB_No,LastIn_Time,LastIn_Ctrl,LastIn_Door)
                  VALUES(@PersonID,@APBNo,@OccurTime,@CtrlNo,@DoorNo)
               ELSE
                  UPDATE MJ_PersonLastInfo SET LastIn_Time=@OccurTime,LastIn_Ctrl=@CtrlNo,LastIn_Door=@DoorNo
                   WHERE Person_ID=@PersonID AND APB_No=@APBNo
            END
            IF @HideBackNo<>''
            BEGIN
               IF NOT EXISTS(SELECT Person_ID FROM MJ_PersonLastInfo WHERE Person_ID=@PersonID AND HideBack_No=@HideBackNo)
                  INSERT INTO MJ_PersonLastInfo(Person_ID,HideBack_No,LastIn_Time,LastIn_Ctrl,LastIn_Door)
                  VALUES(@PersonID,@HideBackNo,@OccurTime,@CtrlNo,@DoorNo)
               ELSE
                  UPDATE MJ_PersonLastInfo SET LastIn_Time=@OccurTime,LastIn_Ctrl=@CtrlNo,LastIn_Door=@DoorNo
                   WHERE Person_ID=@PersonID AND HideBack_No=@HideBackNo
            END
         END
      END
      ELSE
      BEGIN
         UPDATE MJ_Person SET LastOut_Time=@OccurTime,LastOut_Ctrl=@CtrlNo,LastOut_Door=@DoorNo
          WHERE Person_ID=@PersonID AND (LastOut_Time IS NULL OR LastOut_Time<@OccurTime)
         IF @PersonID<>-1
         BEGIN
            SELECT @APBNo=CASE WHEN Is_APB=1 THEN ISNULL(APB_No,'') ELSE '' END,
                   @HideBackNo=CASE WHEN Is_HiddenBack=1 THEN ISNULL(HideBack_No,'') ELSE '' END
              FROM MJ_Door
             WHERE Ctrl_No=@CtrlNo AND Door_No=@DoorNo 
            IF @APBNo<>''
            BEGIN
               IF NOT EXISTS(SELECT Person_ID FROM MJ_PersonLastInfo WHERE Person_ID=@PersonID AND APB_No=@APBNo)
                  INSERT INTO MJ_PersonLastInfo(Person_ID,APB_No,LastOut_Time)
                  VALUES(@PersonID,@APBNo,@OccurTime)
               ELSE
                  UPDATE MJ_PersonLastInfo SET LastOut_Time=@OccurTime
                   WHERE Person_ID=@PersonID AND APB_No=@APBNo
            END
            IF @HideBackNo<>''
            BEGIN
               IF NOT EXISTS(SELECT Person_ID FROM MJ_PersonLastInfo WHERE Person_ID=@PersonID AND HideBack_No=@HideBackNo)
                  INSERT INTO MJ_PersonLastInfo(Person_ID,HideBack_No,LastOut_Time)
                  VALUES(@PersonID,@HideBackNo,@OccurTime)
               ELSE
                  UPDATE MJ_PersonLastInfo SET LastOut_Time=@OccurTime
                   WHERE Person_ID=@PersonID AND HideBack_No=@HideBackNo
            END
         END
      END
   END
GO

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

CREATE PROCEDURE dbo.MJ_pd_SaveControlInfo
   @CardNo    VARCHAR(20),
   @OccurTime DATETIME,
   @CtrlNo    VARCHAR(20),
   @DoorNo    INT,
   @EventCode TINYINT,
   @DoorFlag  VARCHAR(20),
   @EventType TINYINT,
   @IsInDoor  BIT = 1
AS
   UPDATE MJ_SysParam SET Param_Value=@CardNo WHERE Param_Code='Last_READCARD' -- 记录读到卡号
   DECLARE @PersonID INT,@Description VARCHAR(40),@RecordColor TINYINT,@InfoStyle VARCHAR(20)
   DECLARE @RunInfo VARCHAR(80),@AlarmInfo VARCHAR(80),@StateInfo VARCHAR(40),@AcsCard VARCHAR(20)
   DECLARE @PersonName VARCHAR(40),@DeptName VARCHAR(40),@EventName VARCHAR(20)
   DECLARE @APBNo VARCHAR(4),@HideBackNo VARCHAR(4)
       SET @PersonID=-1
       SET @PersonName='      '
       SET @DeptName='      '
   --
   SET @AcsCard=@CardNo
   SELECT @PersonID=A.Person_ID,@PersonName=Person_Name,@DeptName=Dept_Name,@CardNo=A.Card_No
     FROM MJ_Person C
     LEFT JOIN ST_Person A ON A.Person_ID=C.Person_ID
     LEFT JOIN ST_Department B ON B.Dept_ID=A.Dept_ID
    WHERE Acs_Card=@AcsCard AND Person_State=0
   IF @CardNo IS NULL SET @CardNo=@AcsCard
   --
   IF @EventCode=0 OR @EventCode=2
   BEGIN
      IF @IsInDoor=1
      BEGIN
         UPDATE MJ_Person SET LastIn_Time=@OccurTime,LastIn_Ctrl=@CtrlNo,LastIn_Door=@DoorNo
          WHERE Person_ID=@PersonID AND (LastIn_Time IS NULL OR LastIn_Time<@OccurTime)
         IF @PersonID<>-1
         BEGIN
            SELECT @APBNo=CASE WHEN Is_APB=1 THEN ISNULL(APB_No,'') ELSE '' END,
                   @HideBackNo=CASE WHEN Is_HiddenBack=1 THEN ISNULL(HideBack_No,'') ELSE '' END
              FROM MJ_Door
             WHERE Ctrl_No=@CtrlNo AND Door_No=@DoorNo 
            IF @APBNo<>''
            BEGIN
               IF NOT EXISTS(SELECT Person_ID FROM MJ_PersonLastInfo WHERE Person_ID=@PersonID AND APB_No=@APBNo)
                  INSERT INTO MJ_PersonLastInfo(Person_ID,APB_No,LastIn_Time,LastIn_Ctrl,LastIn_Door)
                  VALUES(@PersonID,@APBNo,@OccurTime,@CtrlNo,@DoorNo)
               ELSE
                  UPDATE MJ_PersonLastInfo SET LastIn_Time=@OccurTime,LastIn_Ctrl=@CtrlNo,LastIn_Door=@DoorNo
                   WHERE Person_ID=@PersonID AND APB_No=@APBNo
            END
            IF @HideBackNo<>''
            BEGIN
               IF NOT EXISTS(SELECT Person_ID FROM MJ_PersonLastInfo WHERE Person_ID=@PersonID AND HideBack_No=@HideBackNo)
                  INSERT INTO MJ_PersonLastInfo(Person_ID,HideBack_No,LastIn_Time,LastIn_Ctrl,LastIn_Door)
                  VALUES(@PersonID,@HideBackNo,@OccurTime,@CtrlNo,@DoorNo)
               ELSE
                  UPDATE MJ_PersonLastInfo SET LastIn_Time=@OccurTime,LastIn_Ctrl=@CtrlNo,LastIn_Door=@DoorNo
                   WHERE Person_ID=@PersonID AND HideBack_No=@HideBackNo
            END
         END
      END
      ELSE
      BEGIN
         UPDATE MJ_Person SET LastOut_Time=@OccurTime,LastOut_Ctrl=@CtrlNo,LastOut_Door=@DoorNo
          WHERE Person_ID=@PersonID AND (LastOut_Time IS NULL OR LastOut_Time<@OccurTime)
         IF @PersonID<>-1
         BEGIN
            SELECT @APBNo=CASE WHEN Is_APB=1 THEN ISNULL(APB_No,'') ELSE '' END,
                   @HideBackNo=CASE WHEN Is_HiddenBack=1 THEN ISNULL(HideBack_No,'') ELSE '' END
              FROM MJ_Door
             WHERE Ctrl_No=@CtrlNo AND Door_No=@DoorNo 
            IF @APBNo<>''
            BEGIN
               IF NOT EXISTS(SELECT Person_ID FROM MJ_PersonLastInfo WHERE Person_ID=@PersonID AND APB_No=@APBNo)
                  INSERT INTO MJ_PersonLastInfo(Person_ID,APB_No,LastOut_Time)
                  VALUES(@PersonID,@APBNo,@OccurTime)
               ELSE
                  UPDATE MJ_PersonLastInfo SET LastOut_Time=@OccurTime
                   WHERE Person_ID=@PersonID AND APB_No=@APBNo
            END
            IF @HideBackNo<>''
            BEGIN
               IF NOT EXISTS(SELECT Person_ID FROM MJ_PersonLastInfo WHERE Person_ID=@PersonID AND HideBack_No=@HideBackNo)
                  INSERT INTO MJ_PersonLastInfo(Person_ID,HideBack_No,LastOut_Time)
                  VALUES(@PersonID,@HideBackNo,@OccurTime)
               ELSE
                  UPDATE MJ_PersonLastInfo SET LastOut_Time=@OccurTime
                   WHERE Person_ID=@PersonID AND HideBack_No=@HideBackNo
            END
         END
      END
   END
   --
   SELECT @Description=Door_Name FROM MJ_Door WHERE Ctrl_No=@CtrlNo AND Door_No=@DoorNo
   --处理事件
   SELECT @RecordColor=Record_Color,@InfoStyle=Info_Style,@EventName=Event_Name FROM MJ_EventInfo
    WHERE Event_Type=@EventType AND Event_Code=@EventCode
   IF @EventType=1 SET @EventName=CAST(@EventCode AS VARCHAR)+':'+@EventName           
   IF @InfoStyle='S1' 
   BEGIN
      SET @RunInfo=@CardNo+'--'+@PersonName+'--'+@DeptName+'--允许通过--时间:'+CONVERT(VARCHAR(8),@OccurTime,108)
      SET @StateInfo='允许通过'
   END
   ELSE IF @InfoStyle='S2' 
   BEGIN
      SET @RunInfo='['+@EventName+']--时间:'+CONVERT(VARCHAR(19),@OccurTime,121)
      SET @StateInfo=@EventName
   END
   ELSE IF @InfoStyle='S3' 
   BEGIN
      IF @EventCode<>26 
         SET @RunInfo='[报警]--'+@EventName+'--时间:'+CONVERT(VARCHAR(19),@OccurTime,121)
      ELSE
      BEGIN
         SET @RunInfo='[报警]--'+@EventName+'-不足人数'+@CardNo+'人'+'--时间:'+CONVERT(VARCHAR(19),@OccurTime,121)
         SET @CardNo=''
      END
      SET @StateInfo=@EventName
   END
   ELSE IF @InfoStyle='S4' 
   BEGIN
      SET @RunInfo='['+@EventName+']'+@CardNo+'--'+@PersonName+'--'+@DeptName+'--允许通过--时间:'+CONVERT(VARCHAR(8),@OccurTime,108)
      SET @StateInfo='允许通过-'+@EventName
   END
   ELSE IF @InfoStyle='S5' 
   BEGIN
      SET @RunInfo=@CardNo+'--'+@PersonName+'--'+@DeptName+'--禁止通过-'+@EventName+'--时间:'+CONVERT(VARCHAR(8),@OccurTime,108)
      SET @StateInfo='禁止通过-'+@EventName
   END              
   IF @RunInfo IS NOT NULL
   BEGIN
      IF @RecordColor=4 SET @AlarmInfo=@Description+@EventName+'，知道了'
      SET @Description=@Description+@DoorFlag
      --更新记录
      IF @CardNo='0' SET @CardNo=''
      IF NOT EXISTS(SELECT Ctrl_No FROM MJ_ControlInfo WHERE Ctrl_No=@CtrlNo AND Door_No=@DoorNo)
      BEGIN
         INSERT INTO MJ_ControlInfo(Person_ID,Card_No,Ctrl_No,Door_No,[Description],Run_Time,Run_Info,Alarm_Info,Door_State,Record_Color,State_Info,Update_Time)
         VALUES(@PersonID,@CardNo,@CtrlNo,@DoorNo,@Description,@OccurTime,@RunInfo,@AlarmInfo,0,@RecordColor,@StateInfo,GETDATE())
      END
      ELSE
      BEGIN
         UPDATE MJ_ControlInfo 
            SET Person_ID=@PersonID,Card_No=@CardNo,Run_Time=@OccurTime,Run_Info=@RunInfo,
                Alarm_Info=@AlarmInfo,Record_Color=@RecordColor,[Description]=@Description,
                State_Info=@StateInfo,Update_Time=GETDATE()
          WHERE Ctrl_No=@CtrlNo AND Door_No=@DoorNo
      END
   END
GO

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

CREATE PROCEDURE dbo.MJ_pd_SaveAbnormRecord
   @CardNo    VARCHAR(20),
   @OccurTime DATETIME,
   @CtrlNo    VARCHAR(20),
   @DoorNo    INT,
   @EventCode INT,
   @DoorFlag  VARCHAR(20),
   @IsInDoor  BIT
AS
   DECLARE @DoorName VARCHAR(40),@AcsCard VARCHAR(20)
   SET @AcsCard=@CardNo
   SELECT @CardNo=A.Card_No
     FROM MJ_Person C
     LEFT JOIN ST_Person A ON A.Person_ID=C.Person_ID
    WHERE Acs_Card=@AcsCard
   IF @CardNo IS NULL SET @CardNo=@AcsCard
   SELECT @DoorName=Door_Name FROM MJ_Door WHERE Ctrl_No=@CtrlNo AND Door_No=@DoorNo
   SET @DoorName=@DoorName+@DoorFlag
   INSERT INTO MJ_AbnormRecord(Event_Code,Card_No,Ctrl_No,Door_No,Door_Name,BrushCard_Time,Is_InDoor)
   VALUES(@EventCode,@CardNo,@CtrlNo,@DoorNo,@DoorName,@OccurTime,@IsInDoor)
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'MJ_tg_SyncPerson' AND type = 'TR')
   DROP TRIGGER MJ_tg_SyncPerson
GO

CREATE TRIGGER dbo.MJ_tg_SyncPerson
    ON MJ_Person
FOR UPDATE,INSERT
AS 
   DECLARE @IsDelete BIT,@IsInsert BIT,@IsUpdate BIT
	IF EXISTS(SELECT Person_ID FROM DELETED)  SET @IsDelete=1 ELSE SET @IsDelete=0
   IF EXISTS(SELECT Person_ID FROM INSERTED) SET @IsInsert=1 ELSE SET @IsInsert=0
   IF @IsInsert=1 AND @IsDelete=1 
      SELECT @IsUpdate=1,@IsDelete=0,@IsInsert=0
   ELSE
      SET @IsUpdate=0
   
   --1.更改密码   
   IF @IsUpdate=1 AND UPDATE(Door_Pwd)
   BEGIN
      INSERT INTO MJ_ChangeHistory(Person_ID,Change_State,Change_Date)
      SELECT Person_ID,8,GETDATE() FROM INSERTED
   END 
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'MJ_tg_SyncPsnDoorRight' AND type = 'TR')
   DROP TRIGGER MJ_tg_SyncPsnDoorRight
GO

CREATE TRIGGER dbo.MJ_tg_SyncPsnDoorRight
    ON MJ_PsnDoorRight
FOR UPDATE,INSERT,DELETE
AS 
   DECLARE @IsDelete BIT,@IsInsert BIT,@IsUpdate BIT
	IF EXISTS(SELECT Person_ID FROM DELETED)  SET @IsDelete=1 ELSE SET @IsDelete=0
   IF EXISTS(SELECT Person_ID FROM INSERTED) SET @IsInsert=1 ELSE SET @IsInsert=0
   IF @IsInsert=1 AND @IsDelete=1 
      SELECT @IsUpdate=1,@IsDelete=0,@IsInsert=0
   ELSE
      SET @IsUpdate=0
   
   --1.更改人进出时段规律或者首卡开门权限   
   IF @IsUpdate=1 AND (UPDATE(PsnTime_No) OR UPDATE(FirstCardOpen))
   BEGIN
      INSERT INTO MJ_ChangeHistory(Person_ID,Change_State,Change_Value,Change_Ctrl,Change_Date)
      SELECT Person_ID,3,Door_No,Ctrl_No,GETDATE() FROM INSERTED
   END 
   --2.新增人进出段规律
   IF @IsInsert=1
   BEGIN
      INSERT INTO MJ_ChangeHistory(Person_ID,Change_State,Change_Value,Change_Ctrl,Change_Date)
      SELECT Person_ID,1,Door_No,Ctrl_No,GETDATE() FROM INSERTED
   END 
   --3.删除门权限
   IF @IsDelete=1
   BEGIN
      INSERT INTO MJ_ChangeHistory(Person_ID,Change_State,Change_Value,Change_Ctrl,Change_Date)
      SELECT Person_ID,4,Door_No,Ctrl_No,GETDATE() FROM DELETED
   END 
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ST_tg_AcsSyncPerson' AND type = 'TR')
   DROP TRIGGER ST_tg_AcsSyncPerson
GO

CREATE TRIGGER dbo.ST_tg_AcsSyncPerson
    ON ST_Person
FOR UPDATE
AS 
  DECLARE @IsDelete BIT,@IsInsert BIT,@IsUpdate BIT
	IF EXISTS(SELECT Person_ID FROM DELETED)  SET @IsDelete=1 ELSE SET @IsDelete=0
   IF EXISTS(SELECT Person_ID FROM INSERTED) SET @IsInsert=1 ELSE SET @IsInsert=0
   IF @IsInsert=1 AND @IsDelete=1 
      SELECT @IsUpdate=1,@IsDelete=0,@IsInsert=0
   ELSE
      SET @IsUpdate=0
   
   --1.更改卡号/删除卡号   
   IF @IsUpdate=1 AND UPDATE(Card_No)
   BEGIN
      --换卡或补卡
      INSERT INTO MJ_ChangeHistory(Person_ID,Change_State,Change_Value,Change_Date)
      SELECT A.Person_ID,2,dbo.MJ_fn_Card10To8(B.Card_No),GETDATE() FROM INSERTED A
        LEFT JOIN DELETED B ON B.Person_ID=A.Person_ID
        LEFT JOIN MJ_Person C ON C.Person_ID=A.Person_ID 
       WHERE A.Card_No<>'' AND A.Card_No IS NOT NULL
         AND B.Card_No<>'' AND B.Card_No IS NOT NULL
         AND C.Person_ID IS NOT NULL
      --删除卡号
      INSERT INTO MJ_ChangeHistory(Person_ID,Change_State,Change_Value,Change_Date)
      SELECT A.Person_ID,5,dbo.MJ_fn_Card10To8(B.Card_No),GETDATE() FROM INSERTED A
        LEFT JOIN DELETED B ON B.Person_ID=A.Person_ID
        LEFT JOIN MJ_Person C ON C.Person_ID=A.Person_ID 
       WHERE A.Card_No IS NULL OR A.Card_No=''
         AND C.Person_ID IS NOT NULL
      --注销后重新发卡
      INSERT INTO MJ_ChangeHistory(Person_ID,Change_State,Change_Value,Change_Ctrl,Change_Date)
      SELECT A.Person_ID,9,NULL,NULL,GETDATE() FROM INSERTED A
        LEFT JOIN DELETED B ON B.Person_ID=A.Person_ID
        LEFT JOIN MJ_Person C ON C.Person_ID=A.Person_ID
       WHERE (B.Card_No IS NULL OR B.Card_No='')
         AND (A.Card_No<>'' AND A.Card_No IS NOT NULL)
         AND C.Person_ID IS NOT NULL
   END 
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ST_tg_AcsSyncCard' AND type = 'TR')
   DROP TRIGGER ST_tg_AcsSyncCard
GO

CREATE TRIGGER dbo.ST_tg_AcsSyncCard
    ON ST_Card
FOR UPDATE
AS 
   DECLARE @IsDelete BIT,@IsInsert BIT,@IsUpdate BIT
   IF EXISTS(SELECT Person_ID FROM DELETED)  SET @IsDelete=1 ELSE SET @IsDelete=0
   IF EXISTS(SELECT Person_ID FROM INSERTED) SET @IsInsert=1 ELSE SET @IsInsert=0
   IF @IsInsert=1 AND @IsDelete=1
      SELECT @IsUpdate=1,@IsDelete=0,@IsInsert=0
   ELSE
      SET @IsUpdate=0
   
   --1.挂失/解挂
   IF @IsUpdate=1 AND UPDATE(Card_State)
   BEGIN
      --挂失
      INSERT INTO MJ_ChangeHistory(Person_ID,Change_State,Change_Date)
      SELECT A.Person_ID,6,GETDATE() FROM INSERTED A
        LEFT JOIN MJ_Person B ON B.Person_ID=A.Person_ID 
       WHERE Card_State=1 AND B.Person_ID IS NOT NULL
      --解挂
      INSERT INTO MJ_ChangeHistory(Person_ID,Change_State,Change_Date)
      SELECT A.Person_ID,7,GETDATE() FROM INSERTED A
        LEFT JOIN MJ_Person B ON B.Person_ID=A.Person_ID  
       WHERE Card_State=0 AND B.Person_ID IS NOT NULL
   END
   --2.更改卡的有效期
   IF @IsUpdate=1 AND (UPDATE(Card_NewDate) OR UPDATE(Card_ValidDate))
   BEGIN
      INSERT INTO MJ_ChangeHistory(Person_ID,Change_State,Change_Date)
      SELECT A.Person_ID,8,GETDATE() FROM INSERTED A
        LEFT JOIN MJ_Person B ON B.Person_ID=A.Person_ID 
       WHERE Card_State=0 AND B.Person_ID IS NOT NULL
   END
GO

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

CREATE FUNCTION dbo.MJ_fn_Card10To8(@CardNo VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
   DECLARE @Result VARCHAR(20),@Converted BIT
   SELECT @Converted=CAST(Param_Value AS BIT) FROM MJ_SysParam WHERE Param_Code='CARDTOWIGAND26'
   IF (@Converted = 1)
   BEGIN
      IF (@CardNo IS NOT NULL) AND (@CardNo <> '')
      BEGIN
         DECLARE @b2 TINYINT, @b1 TINYINT, @b0 TINYINT, @Card BIGINT
             SET @Card=CAST(@CardNo AS BIGINT)
             SET @Card=@Card % (256*256*256)
             SET @b2=@Card / (256*256)
             SET @Card=@Card % (256*256)
             SET @b1=@Card / 256
             SET @b0=@Card % 256
             SET @Card=@b2 * 100000 + @b1 * 256 + @b0
         SET @Result=CAST(@Card AS VARCHAR)
      END
      ELSE
         SET @Result=NULL
   END
   ELSE
      SET @Result=@CardNo
   RETURN @Result
END
GO

IF OBJECT_ID('MJ_vi_ChangeHistory') IS NOT NULL
   DROP VIEW MJ_vi_ChangeHistory
GO

CREATE VIEW MJ_vi_ChangeHistory
AS
   SELECT C.Acs_Card [Card_No],Door_No,PsnTime_No,Door_Pwd,Card_NewDate,Card_ValidDate,
          A.Change_Value,A.Change_State,A.Change_Date,Change_Ctrl,ISNULL(Ctrl_No,Change_Ctrl) [Ctrl_No],FirstCardOpen
     FROM MJ_ChangeHistory A
     LEFT JOIN MJ_PsnDoorRight B ON B.Person_ID=A.Person_ID AND B.Ctrl_No=A.Change_Ctrl AND B.Door_No=A.Change_Value
     LEFT JOIN MJ_Person C ON C.Person_ID=A.Person_ID
     LEFT JOIN ST_Card D ON D.Person_ID=A.Person_ID
    WHERE Change_State<>2 AND Change_State<5
    UNION ALL
   SELECT C.Acs_Card [Card_No],Door_No,PsnTime_No,Door_Pwd,Card_NewDate,
          CASE WHEN A.Change_State<>6 THEN Card_ValidDate ELSE '2000-01-01' END [Card_ValidDate],
          A.Change_Value,A.Change_State,A.Change_Date,Change_Ctrl,Ctrl_No,FirstCardOpen
     FROM MJ_ChangeHistory A
     LEFT JOIN MJ_PsnDoorRight B ON B.Person_ID=A.Person_ID
     LEFT JOIN MJ_Person C ON C.Person_ID=A.Person_ID
     LEFT JOIN ST_Card D ON D.Person_ID=A.Person_ID
    WHERE Change_State=2 OR Change_State>5
    UNION ALL
   SELECT C.Acs_Card [Card_No],NULL,NULL,Door_Pwd,NULL,NULL,          
          A.Change_Value,A.Change_State,A.Change_Date,Ctrl_No,Ctrl_No,CAST(0 AS BIT)
     FROM (SELECT A1.*,A2.Ctrl_No FROM MJ_ChangeHistory A1,MJ_Controller A2
            WHERE Change_State=2 OR Change_State=5
          ) A
     LEFT JOIN MJ_Person C ON C.Person_ID=A.Person_ID   
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'MJ_tg_DoorMeetingMode' AND type = 'TR')
   DROP TRIGGER MJ_tg_DoorMeetingMode
GO

CREATE TRIGGER dbo.MJ_tg_DoorMeetingMode
    ON MJ_Door
FOR UPDATE,INSERT,DELETE
AS 
   DECLARE @IsDelete BIT,@IsInsert BIT,@IsUpdate BIT,@IsMeetingMode BIT
   IF EXISTS(SELECT Ctrl_No FROM DELETED)  SET @IsDelete=1 ELSE SET @IsDelete=0
   IF EXISTS(SELECT Ctrl_No FROM INSERTED) SET @IsInsert=1 ELSE SET @IsInsert=0

   IF @IsInsert=1 AND @IsDelete=1 
      SELECT @IsUpdate=1,@IsDelete=0,@IsInsert=0
   ELSE
      SET @IsUpdate=0

   IF @IsUpdate=1 AND UPDATE(MeetingMode)
      SELECT @IsMeetingMode=MeetingMode FROM INSERTED
   IF (@IsInsert=1 AND EXISTS(SELECT MeetingMode FROM INSERTED WHERE MeetingMode=1)) OR (@IsUpdate=1 AND @IsMeetingMode=1)
   BEGIN
      IF NOT EXISTS(SELECT ID_Key FROM MJ_MeetingInfo WHERE Ctrl_No IN (SELECT Ctrl_No FROM INSERTED) AND Door_No IN (SELECT Door_No FROM INSERTED))
         INSERT INTO MJ_MeetingInfo(Ctrl_No,Door_No,Bgn_Date,Bgn_Time,End_Date,End_Time) 
           SELECT Ctrl_No,Door_No,CONVERT(varchar(10), getdate(), 120 ),'00:00',CONVERT(varchar(10), getdate(), 120),'23:59' FROM INSERTED
   END
GO

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

CREATE FUNCTION dbo.MJ_fn_GetBrushCardTime(
   @PersonID INT,
   @First BIT,
   @DateBgn DATETIME,
   @DateEnd DATETIME
)
RETURNS BIGINT
AS
BEGIN
   DECLARE @BrushCardTime DATETIME,@IDKEY BIGINT
   --取出时间段的数据
   SELECT @BrushCardTime=(CASE WHEN @First=1 THEN MIN(BrushCard_Time) ELSE MAX(BrushCard_Time) END)
     FROM MJ_NormalRecord
    WHERE Person_ID=@PersonID AND BrushCard_Time>=@DateBgn AND BrushCard_Time<=@DateEnd
   --取出时间相同的唯一记录
   IF @BrushCardTime IS NOT NULL 
      SELECT @IDKEY=MAX(ID_KEY) FROM MJ_NormalRecord WHERE Person_ID=@PersonID AND BrushCard_Time=@BrushCardTime
   RETURN @IDKEY
END
GO


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

CREATE PROCEDURE MJ_pd_LoadPersonIn
@UserNo  VARCHAR(20),
@CtrlNo  VARCHAR(20),
@DoorNo  VARCHAR(20)
AS
BEGIN
	SELECT a.Person_Id,a.Door_No,a.Ctrl_No,d.Ctrl_Name,b.Person_No,b.Person_Name,  
	b.Dept_ID,c.Dept_No,c.Dept_Name,b.Card_No,e.PsnTime_Name FROM Mj_psndoorright a 
	LEFT join st_person b ON a.Person_id = b.Person_ID  LEFT join st_department c
	ON b.dept_id = c.dept_id  LEFT join mj_Controller d ON a.Ctrl_No = d.Ctrl_No 
	LEFT join Mj_PsnInOutTime e ON a.PsnTime_No = e.PsnTime_No  
	WHERE (a.ctrl_no in (SELECT DISTINCT @CtrlNo FROM mj_door)) and 
	 (a.Door_No in (SELECT DISTINCT @DoorNo FROM mj_door)) and 
	b.Dept_ID in (SELECT A.Dept_ID FROM ST_DeptRight B  LEFT JOIN ST_Department
	 A ON B.Dept_ID=A.Dept_ID  WHERE B.User_No= @UserNo)  Order By c.Dept_No
END

GO