ALTER proc pr_RetAdministrativeAppointment
@LocationId int,
@ID INT=0,
@Language nvarchar(max)='',
@ProfileID int=0,
@ClientId int=0,
@Cyclic int=0
as
begin
select * from tblFormViewTemplate where key1='pr_RetAdministrativeAppointment' order by ordid
select tblVisitList.id,
convert(date, Date1) as Date1,
convert(time, Date1) as Time1,
TorKindID as [type], tbldefManagerTor.Item as typeCap,
YomanId as Yoman, tblClients.NameView as YomanName,
TorTime as nRange
from tblVisitList
left join tbldefManagerTor on tblDefManagerTor.id = TorKindID
left join tblYoman yoman on yoman.id=YomanId
left join tblClients on tblClients.id = yoman.workerid
where tblVisitList.ID=@ID
select Id as [type], item as typecap from tbldefManagerTor
select yoman.id as Yoman, client.nameview as YomanName from tblYoman yoman left join tblClients client on client.id=yoman.workerid where @locationid=yoman.locationid
end
go
--pr_GetYomanBusinessHours 92,'2019-09-17','2019-09-17'
--select top 10 * from tblVisitList order by id desc
--select dbo.fn_checkYomanAllowTime(23,'2019-10-02','2019-10-02','2019-10-02 09:40',45)
CREATE function fn_checkYomanAllowTime
(
@yomanid int,
@DateS date,
@DateE date,
@DateTor datetime,
@nRange int
)
RETURNS bit
as
begin
declare @bRet bit=0
;with tbldates as (
select dateadd(day,Num1,@DateS)  as oDate,y.id as uYomanid from qNums
CROSS join tblyoman  y
where
dateadd(day,Num1,@DateS)>=@DateS and dateadd(day,Num1,@DateS)<=@DateE
and y.ID=@yomanid
)
,tbl1 as (
select uYomanid,odate,
tbTimeReg.*,tblNotworkDay.*,tblVac.*,
1 as RegYomanTimes from tbldates
/* בדיקה ראשונה של חגים או ימי פעילות חלקיים */
outer apply
(
select top 1 yna.TimeS as TimeS_1,yna.TimeE as TimeE_1,TypeInActiveID
from tblYomanInactivityTimes yna where
cast(yna.DateS as DATE)<=oDate and  cast(yna.DateE as DATE)>=oDate and (yna.DayID=0 or DayID=DATEPART(W,oDate)) order by yna.TypeInActiveID desc,yna.id desc
) as tblNotworkDay
/* בדיקה שניה של שינוי זמני */
outer apply
(
select top 1 TimeS as TimeS_2,TimeE as TimeE_2,VacationTypeId from tblVacation where YomanId=uYomanid and bdel=0
and cast(DateS as DATE)<=oDate and  cast(DateE as DATE)>=oDate and (DayID=0 or DayID=DATEPART(W,oDate))
order by VacationTypeId desc,id desc
) as tblVac
outer apply
(
select top 1 StartTime as TimeS_R,EndTime as TimeE_R from tblYomanTimes
where YomanID=uYomanid
and date1<=oDate and date2>=oDate
and KindID=0 and DayID=DATEPART(W,oDate)
) as tbTimeReg
),tblRealTime as (
--סידור הזמנים הוא בצורה הבאה
--שלב 1 באם יש חופשה אזי גובר על הכל
-- באם אין אזי באם יש קוד 1 =שינוי זמני אזי גובר על הגדרה רגילה
--בכל התניה על זמן סיום מתחשבים באם יש זמן אי פעילות
select odate,uYomanid,
(case
when TypeInActiveID=1 then '' --זמן אי פעילות
when  VacationTypeId=1 then TimeS_2 --שינוי זמני
when  VacationTypeId in (2,3,4,5) then '' --שינוי זמני
else
Times_r
end) as Times_real,
(case
when TypeInActiveID=1 then '' --זמן אי פעילות
when  VacationTypeId in (2,3,4,5) then '' --שינוי זמני
when  VacationTypeId=1 then (case when TypeInActiveID=0 and TimeE_2>TimeS_1 then  TimeS_1 else TimeE_2 end) --שינוי זמני
else (case when TypeInActiveID=0 and TimeE_r>TimeS_1 then  TimeS_1 else TimeE_R end)
end) as TimeE_real
from tbl1
),tblPre as
(
select * from tblRealTime where Times_real<>''
)
--select times_real as [start],TimeE_real as [end],'['+cast(DATEPART(dw,odate)-1 as nvarchar)+']' as Day from tblPre
select @bRet=1 from tblPre where times_real<=CAST(@DateTor as time)
and TimeE_real>= cast(DATEADD(MINUTE, @nRange,@DateTor) as time)
return isnull(@bRet,0)
end
ALTER proc pr_UpdateAdministrativeAppointment
@ID int=0,
@ClientID int=1,
@Yoman NVARCHAR(MAX)='',
@Date1 datetime='',
@Time1 nvarchar(5)='',
@nRange int,
@LocationId int=0,
@type int=0,
@Cyclic int=0,
@tStampCreate datetime='',
@tStampUpdate datetime='',
@UserIDCreate int=0,
@UserIDUpdate int=0
as
begin
SET @tStampCreate = GetDate()
SET @tStampUpdate = GetDate()
if @Cyclic = 0 set @Cyclic = 1
while @Cyclic > 0
begin
declare @TempDate datetime = dateadd(week, @cyclic - 1, @Date1)
declare @torid int=0
declare @msg nvarchar(max)=''
declare @msgs nvarchar(max)=''
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
SELECT value from UTILfn_Split(@Yoman,',')
DECLARE @yomanid Int
DECLARE @yomanName nvarchar(max)=''
-- Open the cursor
OPEN c
FETCH NEXT FROM c INTO @yomanid
WHILE (@@FETCH_STATUS = 0)
BEGIN
select @yomanName=w.NameView from tblyoman y
inner join tblclients w on y.WorkerId=w.id
where y.id=@yomanid
/*בדיקה שזהה זמן פעילות ולא חופף לתור אחר*/
if (dbo.fn_checkYomanAllowTime(@yomanid,@TempDate,@TempDate,convert(nvarchar(10),@TempDate,101)+' '+@Time1,@nRange)=0)
begin
 set @msg='<br>'+ @msg + @yomanName +': הזמן שנבחר למטפל חורג מזמני העבודה'+ '<br>'
end
else if exists (select * from tblVisitList v where v.bDel=0 and v.YomanID=@yomanid
and
 (
 convert(nvarchar(10),@TempDate,101)+' '+@Time1 > v.Date1 and convert(nvarchar(10),@TempDate,101)+' '+@Time1< DATEADD(MINUTE,v.TorTime,v.Date1)
 or
 dateadd(MINUTE,@nRange,(convert(nvarchar(10),@TempDate,101)+' '+@Time1)) > v.Date1 and dateadd(MINUTE,@nRange,(convert(nvarchar(10),@TempDate,101)+' '+@Time1))< DATEADD(MINUTE,v.TorTime,v.Date1)
 )
)
/*בדיקת התנגשות עם תור קיים*/
begin
 set @msg='<br>'+ @msg +@yomanName + ': הזמן שנבחר חופף לתור קיים' + '<br>'
end
else
begin
 INSERT INTO [tblVisitList](
 ClientID,Yomanid,ParitKod,Date1,TorTime,ManagerTorID,/*ManagerTorID,GroupManagerTor,paritkod,nColor,*/
 UserIdCreate,tStampCreate,UserIdUpdate,tStampUpdate
 )
 values  (
 @ClientID,@YomanID,'',convert(nvarchar(10),@TempDate,101)+' '+@Time1,@nRange,@type,/*@ManagerTorID,@GroupManagerTor,'',@nColor,*/
 @UserIdUpdate,@tStampCreate,@UserIDUpdate,@tStampUpdate
 )
 set @torid=SCOPE_IDENTITY()
 exec pr_TorLogUpdate @torid,1,@UserIDCreate
end
FETCH NEXT FROM c INTO @yomanid
END
-- Close and deallocate the cursor
CLOSE c
DEALLOCATE c
 set @msgs = @msgs + ', ' + @msg
SET @Cyclic = @Cyclic - 1
 end
if @msgs <> ''
    select 'warning: ' + @msgs
else
    select 1
end