/*************/
/* create DB */
/*************/

USE master
GO

drop database urv2_ms
go

CREATE DATABASE urv2_ms
ON 
( NAME = urv2_data,
   FILENAME = 'E:\db\MSSQL\urv2.mdf',
   SIZE = 4,
   FILEGROWTH = 1 )
LOG ON
( NAME = urv2_log,
   FILENAME = 'E:\db\MSSQL\urv2.ldf',
   SIZE = 4,
   FILEGROWTH = 1 )
COLLATE Cyrillic_General_CI_AS
GO


USE urv2_ms
GO

create table GENIDTAB(
 ID    integer identity (1,1) not null primary key,
 CODE  varchar(30) COLLATE Cyrillic_General_CI_AS NOT NULL UNIQUE,
 VINT  integer,
 Descr varchar(120) COLLATE Cyrillic_General_CS_AS NULL
);
GO

CREATE PROCEDURE GetGenValue
 @iGenN varchar(30),
 @oNV int OUTPUT
AS
begin
 select @oNV=VINT+1 from GENIDTAB where CODE=@iGenN;
 update GENIDTAB set VINT = @oNV where CODE=@iGenN;
end
GO

insert into GENIDTAB(CODE,VINT) values ('GEN_UID',1);

create table LOGTIME(
 N      integer not null primary key,
 DT     datetime,
 EV_N   smallint,
 POBJ_N integer,
 CLI_N  integer
);
create index LOG_DT ON LOGTIME (DT);

CREATE TABLE CLITYPES(
 CLI_KIND integer NOT NULL primary key,
 KIND_OPIS varchar(100)
);

create table DEPARTM(
 CODE       integer not null primary key,
 KOD_DEP    varchar(10),
 NAIM_K     varchar(64),
 NAIM_D     varchar(255),
 PAR        integer,
 DATE_START datetime,
 DATE_END   datetime,
 REP        char(1) default 0
);
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_DEP',0,'DEPARTM.CODE');

create table JOBS(
 CODE       integer not null primary key,
 NAIM_K     varchar(64),
 NAIM_D     varchar(255),
 DATE_START datetime,
 DATE_END   datetime
);
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_JOB',0,'JOBS.CODE');

create table BRIGTAB(
 CODE    integer not null primary key,
 KOD     varchar(8),
 NAIM    varchar(50),
 COD_DEP integer not null
);
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_BRIG',0,'BRIGTAB.CODE');

create table CLI_IN_BRIG(
 CODE         integer identity (1,1) not null primary key,
 COD_BRIG     integer not null,
 USER_ID      integer not null,
 DATE_START   datetime,
 DATE_END     datetime
);

create table CLITAB(
 N        integer not null primary key,
 CODE     integer,
 NUMBER_  varchar(32),
 NUM_KD   varchar(8),
 F_NAME   varchar(64),
 S_NAME   varchar(64),
 M_NAME   varchar(64),
 CLI_KIND smallint,
 RAZR     varchar(8),
 P_OPL    varchar(8),
 COD_DEP  integer,
 COD_JOB  integer,
 COD_BRIG integer,
 VIP      char(1),
 POL      char(1) default 0
);
create index CLI_FNAME on CLITAB (F_NAME);
create index CLI_NMB on CLITAB (NUMBER_);
create index CLITAB_CODE on CLITAB (CODE);
create index CLI_DEP on CLITAB(COD_DEP);
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_CLITAB_ID',1,'CLITAB.N');

create table  PERS_HISTORY(
 CODE          integer not null primary key,
 USER_ID       integer not null,
 DATE_START    datetime not null,
 DATE_END      datetime, 
 DEPARTMENT_ID integer,
 PROFESSION_ID integer,
 REASON        smallint,
 NAKAZ         varchar(50) COLLATE Cyrillic_General_CI_AS
);
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_HISTORY',1,'PERS_HISTORY.CODE');
create index PERS_HIST on PERS_HISTORY(USER_ID);

create table SHABL(
 CODE   integer not null primary key,
 NAIM_K varchar(50),
 PERIOD integer,
 R_IN   integer,
 P_IN   integer,
 R_OUT  integer,
 P_OUT  integer,
 MAX_AB integer
);
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_SHABL',1,'SHABL.CODE');

create table SMENA(
 CODE   integer not null primary key,
 NAIM_K varchar(50),
 COD_SH integer not null
);
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_SMEN',1,'SMENA.CODE');
alter table SMENA add constraint fk_smen FOREIGN KEY (COD_SH) REFERENCES SHABL(CODE) on delete cascade;

create table SH_FIELD(
 CODE      integer identity (1,1) not null primary key,
 COD_SMEN  integer not null,
 NPP       integer not null,
 TIME_I    datetime,
 TIME_ALL  datetime,
 TIME_DIN1 datetime,
 TIME_DIN2 datetime
);
alter table SH_FIELD add constraint fk_shsm FOREIGN KEY (COD_SMEN) REFERENCES SMENA(CODE) on delete cascade;

create table KONTUR(
 CODE integer not null primary key,
 NAIM varchar(50) COLLATE Cyrillic_General_CI_AS
);
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_KONT',1,'KONTUR.CODE');

create table D_IN_KONT(
 COD_KONT integer not null CONSTRAINT fk_dkont REFERENCES KONTUR(CODE) on delete cascade,
 SNS_N    integer not null,
 constraint Kont_Door unique (COD_KONT,SNS_N)
);
GO

create table GRAPHS(
 CODE      integer not null primary key,
 CLI_N     integer,
 MON       smalldatetime,
 COD_KONT  integer  not null CONSTRAINT fk_grkont REFERENCES KONTUR(CODE),
 COD_SHABL integer CONSTRAINT fk_grsh REFERENCES SHABL(CODE),
 NORM_H    integer,
 NORM_M    integer
);
create index GR_CLI on GRAPHS(CLI_N);
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_GR',1,'GRAPHS.CODE');

create table TIMETABLE(
 CODE      integer identity (1,1) not null primary key,
 COD_GRAF  integer CONSTRAINT fk_GRAPH REFERENCES GRAPHS(CODE) on delete cascade,
 DAT       smalldatetime,
 TIME_I    smalldatetime,
 TIME_ALL  datetime,
 TIME_DIN1 smalldatetime,
 TIME_DIN2 smalldatetime,
 R_IN      integer,
 P_IN      integer,
 R_OUT     integer,
 P_OUT     integer,
 MAX_AB    integer,
 COD_SMEN  integer CONSTRAINT fk_ttsm REFERENCES SMENA(CODE)
);
create index TT_GR on TIMETABLE(COD_GRAF);

create table BUFER(
 UID       integer,
 NPP       integer,
 TIME_I    datetime,
 TIME_ALL  datetime,
 TIME_DIN1 datetime,
 TIME_DIN2 datetime,
 DAT       datetime,
 R_IN      integer,
 P_IN      integer,
 R_OUT     integer,
 P_OUT     integer,
 MAX_AB    integer,
 COD_SMEN  integer
);

create table TabCalc(
 U_ID     integer,
 CODE     integer,
 DT       datetime,
 EV_N     smallint,
 TypePass CHAR(1)
);

CREATE TABLE HLDTAB(
 N     integer identity (1,1) not null primary key,
 M     smallint not null,
 D     smallint not null,
 OPIS  varchar(32) COLLATE Cyrillic_General_CI_AS,
 BLOCK smallint
);

create table TIPE_ABS(
 CODE integer identity (1,1) not null primary key,
 NAIM_K varchar(50) COLLATE Cyrillic_General_CI_AS,
 NAIM_D Varchar(255) COLLATE Cyrillic_General_CI_AS,
 WTime  char(1),
 WDay   char(1) default 0,
 PDay   char(1) default 0,
 ColT13 integer,
 ColP5  integer,
 ColT1  integer
);

create table ABSENCE(
 CODE    integer identity (1,1) not null primary key,
 CLI_N   integer not null,
 COD_ABS integer not null,
 DAT_N   datetime not null,
 DAT_K   datetime null,
 COMMENT varchar(255) COLLATE Cyrillic_General_CI_AS
);
alter table ABSENCE add constraint fk_abt FOREIGN KEY (COD_ABS) REFERENCES TIPE_ABS(CODE) on delete cascade;
create index CLI_ABS on ABSENCE(CLI_N);

create table LeavePass(
 CODE         integer identity (1,1) not null primary key,
 USER_ID      integer not null,
 TypePass     CHAR(1) default 0,
 NDoc         varchar(10) COLLATE Cyrillic_General_CI_AS,
 DatBeg       smalldatetime,
 DatEnd       smalldatetime,
 Reason       varchar(255) COLLATE Cyrillic_General_CI_AS
);
create index ID_LvPass on LeavePass(USER_ID, DatBeg);

create table OVERTIME(
 CODE       integer identity (1,1) not null primary key,
 USER_ID    integer not null,
 DAT        datetime not null,
 TypeOver   CHAR(1) default 0 not null,
 DatBeg     datetime,
 DatEnd     datetime,
 Reason     varchar(255) COLLATE Cyrillic_General_CI_AS
);
CREATE INDEX ID_OVER on OVERTIME(USER_ID, DatBeg);

create table CLIGROUP(
 CODE integer not null primary key,
 NAIM varchar(255) COLLATE Cyrillic_General_CI_AS
);
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_CLIGROUP',1,'CLIGROUP.CODE');

create table CLI_IN_GR(
 COD_GR integer not null,
 CLI_N  integer not null unique
);
alter table CLI_IN_GR add constraint fk_cligr FOREIGN KEY (COD_GR) REFERENCES CLIGROUP(CODE) on delete cascade;

CREATE TABLE HIST_PCARD(
 HIST_ID      integer not null primary key,
 USER_ID      integer not null,
 CLI_N        integer null,
 DATE_START   smalldatetime null,
 CARD_STS     smallint not null,
 DATE_END     smalldatetime null,
 REASON       varchar(70) COLLATE Cyrillic_General_CI_AS null,
 FIO_OPER     varchar(70) COLLATE Cyrillic_General_CI_AS null,
 FIO_OPER_End varchar(70) COLLATE Cyrillic_General_CI_AS null
);
create index CHIST_ID on HIST_PCARD (USER_ID, DATE_START);
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_HIST_PCARD',1,'HIST_PCARD.HIST_ID');

create table Timekeepers(
 CODE     integer not null primary key,
 F_NAME   varchar(64) COLLATE Cyrillic_General_CI_AS,
 S_NAME   varchar(64) COLLATE Cyrillic_General_CI_AS,
 M_NAME   varchar(64) COLLATE Cyrillic_General_CI_AS,
 Nickname varchar(25) not null unique,
 PRIV     varchar(255) COLLATE Cyrillic_General_CI_AS,
 PASSW    varchar(20) COLLATE Cyrillic_General_CI_AS,
 DEP      varchar(2000) COLLATE Cyrillic_General_CI_AS
);
insert into Timekeepers(CODE,Nickname,PRIV,PASSW,DEP) values (1,'Старший табельщик','730522','','ALL');
insert into GENIDTAB(CODE,VINT,Descr) values ('GEN_TKeepers',2,'Timekeepers.CODE');

create table INSP_Params(
 TFire          integer,
 LastMinVersion smallint,
 LastRelease    smallint,
 LastBuild      integer,
 DoUpdate       char(1) null,
 NewVersionPass varchar(255) COLLATE Cyrillic_General_CI_AS null,
 RULang         char(1) null,
 NmDay          varchar(10) COLLATE Cyrillic_General_CI_AS null,
 NmEv           varchar(10) COLLATE Cyrillic_General_CI_AS null,
 NmNight        varchar(10) COLLATE Cyrillic_General_CI_AS null,
 NmOver         varchar(10) COLLATE Cyrillic_General_CI_AS null,
 NmWEnd         varchar(10) COLLATE Cyrillic_General_CI_AS null,
 NmNN           varchar(10) COLLATE Cyrillic_General_CI_AS,
 NmNUv          varchar(10) COLLATE Cyrillic_General_CI_AS,
 Term           decimal(5,2) null
);

insert into INSP_Params (TFire, LastMinVersion, LastRelease, LastBuild, DoUpdate) values (4,5,1,1,0);
update INSP_Params set NmDay='я', NmEv='в', NmNight='н', NmOver='с', NmWEnd='Вых', NmNN='н/я', NmNUv='нУв';

create table AddWorkTime(
 FNight char(1) null,
 NBeg   datetime null,
 NAll   datetime null,
 FEven  char(1) null,
 EBeg   datetime null,
 EAll   datetime null,
 EName  varchar(40) null
);

insert into AddWorkTime (EName,FNight,FEven) values ('',1,1);

create table OutTerritory(
 CODE       integer identity (1,1) not null primary key,
 CLI_N      integer not null,
 DATE_START datetime not null,
 DATE_END   datetime null
);

create table SummerTime(
 DAT    datetime not null unique,
 UpDown smallint
);

create table NewWeekEnd(
 DAT datetime not null unique
);

CREATE TABLE STRUCTURE(
 CHILD_ID      integer NOT NULL,
 PARENT_ID     integer null,
 POS           integer null
);

create table TAB_REP(
 CODE     integer identity (1,1) not null primary key,
 NAIM_K   varchar(50) COLLATE Cyrillic_General_CI_AS,
 COL_TAbs varchar(20) COLLATE Cyrillic_General_CI_AS
);

create table TAB_REP_COL(
 CODE     integer identity (1,1) not null primary key,
 COD_TR   integer not null,
 NUMCOL   integer not null,
 NN       smallint default 0,
 TTL_COL  varchar(255) COLLATE Cyrillic_General_CI_AS,
 KOD_COL  varchar(50) COLLATE Cyrillic_General_CI_AS
);
create unique index TREP_COL ON TAB_REP_COL(COD_TR, NUMCOL);
GO
insert into TIPE_ABS (NAIM_K, NAIM_D, ColT13, ColT1, WTime) values ('ОО', 'очередной отпуск', 11, 1, 0);
insert into TIPE_ABS (NAIM_K, NAIM_D, ColT13, ColT1, WTime) values ('УО', 'учебный отпуск', 12, 2, 0);
insert into TIPE_ABS (NAIM_K, NAIM_D, WTime) values ('К', 'командировка', '1');
insert into TIPE_ABS (NAIM_K, NAIM_D, ColT13, ColT1, WTime) values ('ГО', 'гос.обязанности', 13, 3, 0);
insert into TIPE_ABS (NAIM_K, NAIM_D, ColT13, ColT1, WTime) values ('Б', 'больничный', 14, 4, 0);

insert into HLDTAB (M, D, OPIS) values (1, 1, 'Новый Год');
insert into HLDTAB (M, D, OPIS) values (1, 7, 'Рождество');
insert into HLDTAB (M, D, OPIS) values (3, 8, 'Международный женский день');
insert into HLDTAB (M, D, OPIS) values (5, 1, 'День солидарности трудящихся');
insert into HLDTAB (M, D, OPIS) values (5, 2, 'День солидарности трудящихся');
insert into HLDTAB (M, D, OPIS) values (5, 9, 'День Победы');

set identity_insert TAB_REP on;
insert into TAB_REP (CODE, NAIM_K, COL_TAbs) values (1, 'Табель П5', 'ColP5');
insert into TAB_REP (CODE, NAIM_K, COL_TAbs) values (2, 'Табель выходов на работу', 'ColT13');
insert into TAB_REP (CODE, NAIM_K, COL_TAbs) values (3, 'Табель в одну строку', 'ColT1');
set identity_insert TAB_REP off;
GO

CREATE TABLE VERSIONTAB (
    SOFTNAME   VARCHAR(64) COLLATE Cyrillic_General_CS_AS,
    SOFTVER    VARCHAR(64) COLLATE Cyrillic_General_CS_AS,
    VERFIELD1  VARCHAR(64) COLLATE Cyrillic_General_CS_AS
);
INSERT INTO VERSIONTAB (SOFTNAME, VERFIELD1) VALUES ('INSPEKTOR','MSSQL');
GO


/* ПРОЦЕДУРЫ */

CREATE PROCEDURE FindUserCard
 @DR1   DATETIME,
 @DR2   DATETIME,
 @UsID  int,
 @Card1 int OUTPUT,
 @Card2 int OUTPUT,
 @BCard int OUTPUT
AS
begin
 DECLARE FCard CURSOR LOCAL
  FOR
   select CLI_N from HIST_PCARD where (USER_ID=@UsID) and (CARD_STS=1)
   and (DATE_START<=@DR2) and ((DATE_END>=@DR1) or (DATE_END is null))
   order by DATE_START
  FOR READ ONLY;
 OPEN FCard;
 FETCH from FCard into @Card1;
 while @@FETCH_STATUS=0
  begin
   FETCH NEXT from FCard into @Card2;
  end; 
 CLOSE FCard;
 DEALLOCATE FCard;

 if (@Card2 is null)
  set @Card2=0;
 
 if (@Card1 is null) or (@Card1=0)
  begin  /* проверяем блокировку пропуска*/
   select @BCard=CLI_N from HIST_PCARD where (USER_ID=@UsID) and (CARD_STS=2)
    and (DATE_START<=@DR2) and ((DATE_END>=@DR1) or (DATE_END is null));
  end;
end
GO



CREATE PROCEDURE SelManEvent
 @DV1    DATETIME, -- начало выборки событий
 @DV2    DATETIME, -- конец выборки событий
 @Card1  int,
 @Card2  int,
 @Kont   int,
 @CurrUs int,
 @NRow   int OUTPUT
AS
begin
 DECLARE @N int;
 DECLARE @EV_N int;
 DECLARE @POBJ_N int;
 DECLARE @DT datetime;
 DECLARE @DPrev datetime;
 set @DT=0;
 select @NRow=max(CODE) from TABCALC where U_ID=@CurrUs;
 if @NRow is null
  set @NRow=0;

 DECLARE VLog CURSOR LOCAL
  FOR
   select N, DT, EV_N, POBJ_N from LOGTIME
    where ((CLI_N=@Card1) or (CLI_N=@Card2))
    and (EV_N in (7,8,166,167))
    and (DT>=@DV1) and (DT<=@DV2)
    and (POBJ_N in (select SNS_N from D_IN_KONT where COD_KONT=@Kont))
   order by DT,N
  FOR READ ONLY;
 OPEN VLog;

 FETCH from VLog into @N, @DT, @EV_N, @POBJ_N;
 if @DT>=0
  begin   
   set @DPrev=0;
  end
 else
  begin  -- нет событий, выходим из процедуры
   CLOSE VLog;
   DEALLOCATE VLog;
   RETURN;
  end;

 /* Перебираем все события */
 While @@FETCH_STATUS=0
  Begin
   -- если одно и то же время, увеличиваем на секунду, чтобы не нарушить очередность
   if @DT=@DPrev
    set @DT=dateadd(Ss,1,@DT);
   -- въезд интерпретируем как вход
   if @EV_N=166
    set @EV_N=7;
   -- выезд интерпретируем как выход
   if @EV_N=167
    set @EV_N=8;
   -- вносим в служебную таблицу
   set @NRow=@NRow+1;
   insert into TABCALC(U_ID, CODE, DT, EV_N)
     values (@CurrUs, @NRow, @DT, @EV_N);
   set @DPrev=@DT;
   FETCH NEXT from VLog into @N, @DT, @EV_N, @POBJ_N;
  End;

 CLOSE VLog;
 DEALLOCATE VLog;
end
GO


CREATE PROCEDURE CheckTabCalc
 @CurrUs int,
 @ErrIn  bit OUTPUT, -- несколько входов подряд  
 @ErrOut bit OUTPUT,  -- несколько выходов подряд
 @NRow   int OUTPUT
AS
begin
 DECLARE @EV_N int;
 DECLARE @DT datetime;
 DECLARE @PEv  int;
 set @ErrIn=0;
 set @ErrOut=0;
 set @PEv=0;

 DECLARE UsLog CURSOR LOCAL SCROLL
  FOR
   select CODE, DT, EV_N from TABCALC
    where U_ID=@CurrUs
    order by DT
  FOR READ ONLY;
 OPEN UsLog;

 FETCH FIRST from UsLog into @NRow, @DT, @EV_N;
 while @@FETCH_STATUS=0
  begin
   if @EV_N=@PEv
    begin
     if @EV_N=7  -- несколько входов подряд
      begin
       set @ErrIn=1;
       -- оставляем только первый вход
       delete from TABCALC where (U_ID=@CurrUs) and (CODE=@NRow);
      end;
     if @EV_N=8  -- несколько выходов подряд
      begin
       set @ErrOut=1; 
       -- оставляем только последний выход
       FETCH PRIOR from UsLog into @NRow, @DT, @EV_N;
       if @EV_N=8
        delete from TABCALC where (U_ID=@CurrUs) and (CODE=@NRow);
       FETCH NEXT from UsLog into @NRow, @DT, @EV_N;
      end; 
    end;

   set @PEv=@EV_N;
   if @@FETCH_STATUS=0 
    FETCH NEXT from UsLog into @NRow, @DT, @EV_N;
  end;
 CLOSE UsLog;
 DEALLOCATE UsLog;
 if @NRow is null
  set @NRow=0;
end
GO


CREATE PROCEDURE InsCalcLPass
 @CurrUs  int,
 @UsID    int,
 @LogB    datetime,
 @LogE    datetime,
 @DBeg    datetime,
 @EndD    datetime,
 @Din1    datetime,
 @Din2    datetime,
 @MCod    int,
 @M       int OUTPUT
AS
begin
 DECLARE @NRow   int;
 DECLARE @TpPass char(1);
 DECLARE @DatBeg datetime;
 DECLARE @DatEnd datetime;
 DECLARE @D1     datetime;
 DECLARE @D2     datetime;
 set @M=0;
 DECLARE LPass CURSOR LOCAL
  FOR
   select CODE, TypePass, DatBeg, DatEnd from LEAVEPASS
    where (USER_ID=@UsID)
    and ((DatBeg<@LogE) and (DatEnd>=@LogB))
    order by DatBeg
  FOR READ ONLY;
 OPEN LPass;
 FETCH from LPass into @NRow, @TpPass, @DatBeg, @DatEnd;

 while @@FETCH_STATUS=0
  begin
   set @MCod=@MCod+1;
   insert into TABCALC(U_ID, CODE, DT, EV_N, TypePass)
    values (@CurrUs, @MCod, @DatBeg, 7, @TpPass);
   set @MCod=@MCod+1;
   insert into TABCALC(U_ID, CODE, DT, EV_N, TypePass)
    values (@CurrUs, @MCod, @DatEnd, 8, @TpPass);

   if @TpPass=1 -- если увольнительная не оплачиваемая
    if (@DatBeg<@EndD) and (@DatEnd>@DBeg)
     begin
      if @DatBeg>@DBeg
       set @D1=@DatBeg
      else
       set @D1=@DBeg;
      if @DatEnd>=@EndD
       set @D2=@EndD
      else
       set @D2=@DatEnd;
      set @M=@M+datediff(Mi,@D1,@D2); 

      -- вычитаем обеденный перерыв
      if (@D1<@Din2) and (@D2>@Din1)
       begin
        if @D1<=@Din1
         begin
          if @D2>=@Din2
           set @M=@M-datediff(Mi,@Din1,@Din2) 
          else
           set @M=@M-datediff(Mi,@Din1,@D2);
         end
        else
         begin
          if @D2>=@Din2
           set @M=@M-datediff(Mi,@D1,@Din2)
          else
           set @M=@M-datediff(Mi,@D1,@D2);
         end;
       end;
     end;

   FETCH NEXT from LPass into @NRow, @TpPass, @DatBeg, @DatEnd;
  end;
 CLOSE LPass;
 DEALLOCATE LPass;
end
GO

CREATE PROCEDURE CheckBigGraph
 @UsID  int,
 @DR    datetime,
 @DBeg  datetime,
 @DEnd  datetime,
 @FGrB  bit OUTPUT,
 @FGrE  bit OUTPUT
AS
begin
 DECLARE @DI datetime;
 DECLARE @DA datetime;
 DECLARE @D3 datetime;
 DECLARE @DNp datetime;

 /*проверка графика за предыдущий день*/
 set @D3=dateadd(Dd,-1,@DR); 
 set @DNp=dateadd(Dd,1-day(@D3),@D3);
 select @DI=TIME_I, @DA=TIME_ALL
  from GRAPHS g inner join TIMETABLE t
  on g.CODE=t.COD_GRAF where
  (g.CLI_N=@UsID) and (g.MON=@DNp) and (t.DAT=@D3);
 set @D3=dateadd(Hh,datepart(Hh,@DA),@DI);
 set @D3=dateadd(Mi,datepart(Mi,@DA),@D3);
 if @D3 > @DI 
  if datediff(Dd,@D3,@DBeg)=0
   if datediff(Hh,@D3,@DBeg)=0
    if datediff(Mi,@D3,@DBeg) < 5
     set @FGrB=1;

 /*проверка графика за следующий день*/
 set @D3=dateadd(Dd,1,@DR); 
 set @DNp=dateadd(Dd,1-day(@D3),@D3);
 select @DI=TIME_I, @DA=TIME_ALL
  from GRAPHS g inner join TIMETABLE t
  on g.CODE=t.COD_GRAF where
  (g.CLI_N=@UsID) and (g.MON=@DNp) and (t.DAT=@D3);
 set @D3=dateadd(Hh,datepart(Hh,@DA),@DI);
 set @D3=dateadd(Mi,datepart(Mi,@DA),@D3);
 if @D3 > @DI 
  if datediff(Dd,@DI,@DEnd)=0
   if datediff(Hh,@DI,@DEnd)=0
    if datediff(Mi,@DI,@DEnd) < 5
     set @FGrE=1;
end
GO