SHARE
TWEET

Untitled

a guest Aug 25th, 2019 72 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ALTER proc pr_RetAdministrativeAppointment
  2. @LocationId int,
  3. @ID INT=0,
  4. @Language nvarchar(max)='',
  5. @ProfileID int=0,
  6. @ClientId int=0,
  7. @Cyclic int=0
  8. as
  9. begin
  10. select * from tblFormViewTemplate where key1='pr_RetAdministrativeAppointment' order by ordid
  11. select tblVisitList.id,
  12. convert(date, Date1) as Date1,
  13. convert(time, Date1) as Time1,
  14. TorKindID as [type], tbldefManagerTor.Item as typeCap,
  15. YomanId as Yoman, tblClients.NameView as YomanName,
  16. TorTime as nRange
  17. from tblVisitList
  18. left join tbldefManagerTor on tblDefManagerTor.id = TorKindID
  19. left join tblYoman yoman on yoman.id=YomanId
  20. left join tblClients on tblClients.id = yoman.workerid
  21. where tblVisitList.ID=@ID
  22. select Id as [type], item as typecap from tbldefManagerTor
  23. 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
  24. end
  25. go
  26. --pr_GetYomanBusinessHours 92,'2019-09-17','2019-09-17'
  27. --select top 10 * from tblVisitList order by id desc
  28. --select dbo.fn_checkYomanAllowTime(23,'2019-10-02','2019-10-02','2019-10-02 09:40',45)
  29. CREATE function fn_checkYomanAllowTime
  30. (
  31. @yomanid int,
  32. @DateS date,
  33. @DateE date,
  34. @DateTor datetime,
  35. @nRange int
  36. )
  37. RETURNS bit
  38. as
  39. begin
  40. declare @bRet bit=0
  41. ;with tbldates as (      
  42. select dateadd(day,Num1,@DateS)  as oDate,y.id as uYomanid from qNums      
  43. CROSS join tblyoman  y      
  44. where      
  45. dateadd(day,Num1,@DateS)>=@DateS and dateadd(day,Num1,@DateS)<=@DateE  
  46. and y.ID=@yomanid    
  47. )      
  48. ,tbl1 as (      
  49. select uYomanid,odate,      
  50. tbTimeReg.*,tblNotworkDay.*,tblVac.*,      
  51. 1 as RegYomanTimes from tbldates      
  52. /* ื‘ื“ื™ืงื” ืจืืฉื•ื ื” ืฉืœ ื—ื’ื™ื ืื• ื™ืžื™ ืคืขื™ืœื•ืช ื—ืœืงื™ื™ื */      
  53. outer apply      
  54. (      
  55. select top 1 yna.TimeS as TimeS_1,yna.TimeE as TimeE_1,TypeInActiveID      
  56. from tblYomanInactivityTimes yna where      
  57. 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                            
  58. ) as tblNotworkDay      
  59. /* ื‘ื“ื™ืงื” ืฉื ื™ื” ืฉืœ ืฉื™ื ื•ื™ ื–ืžื ื™ */      
  60. outer apply      
  61. (      
  62. select top 1 TimeS as TimeS_2,TimeE as TimeE_2,VacationTypeId from tblVacation where YomanId=uYomanid and bdel=0      
  63. and cast(DateS as DATE)<=oDate and  cast(DateE as DATE)>=oDate and (DayID=0 or DayID=DATEPART(W,oDate))      
  64. order by VacationTypeId desc,id desc                            
  65. ) as tblVac      
  66. outer apply      
  67. (      
  68. select top 1 StartTime as TimeS_R,EndTime as TimeE_R from tblYomanTimes      
  69. where YomanID=uYomanid      
  70. and date1<=oDate and date2>=oDate      
  71. and KindID=0 and DayID=DATEPART(W,oDate)                      
  72. ) as tbTimeReg      
  73. ),tblRealTime as (      
  74. --ืกื™ื“ื•ืจ ื”ื–ืžื ื™ื ื”ื•ื ื‘ืฆื•ืจื” ื”ื‘ืื”      
  75. --ืฉืœื‘ 1 ื‘ืื ื™ืฉ ื—ื•ืคืฉื” ืื–ื™ ื’ื•ื‘ืจ ืขืœ ื”ื›ืœ      
  76. -- ื‘ืื ืื™ืŸ ืื–ื™ ื‘ืื ื™ืฉ ืงื•ื“ 1 =ืฉื™ื ื•ื™ ื–ืžื ื™ ืื–ื™ ื’ื•ื‘ืจ ืขืœ ื”ื’ื“ืจื” ืจื’ื™ืœื”      
  77. --ื‘ื›ืœ ื”ืชื ื™ื” ืขืœ ื–ืžืŸ ืกื™ื•ื ืžืชื—ืฉื‘ื™ื ื‘ืื ื™ืฉ ื–ืžืŸ ืื™ ืคืขื™ืœื•ืช      
  78. select odate,uYomanid,      
  79. (case      
  80. when TypeInActiveID=1 then '' --ื–ืžืŸ ืื™ ืคืขื™ืœื•ืช      
  81. when  VacationTypeId=1 then TimeS_2 --ืฉื™ื ื•ื™ ื–ืžื ื™      
  82. when  VacationTypeId in (2,3,4,5) then '' --ืฉื™ื ื•ื™ ื–ืžื ื™      
  83. else      
  84. Times_r      
  85. end) as Times_real,      
  86. (case      
  87. when TypeInActiveID=1 then '' --ื–ืžืŸ ืื™ ืคืขื™ืœื•ืช      
  88. when  VacationTypeId in (2,3,4,5) then '' --ืฉื™ื ื•ื™ ื–ืžื ื™    
  89. when  VacationTypeId=1 then (case when TypeInActiveID=0 and TimeE_2>TimeS_1 then  TimeS_1 else TimeE_2 end) --ืฉื™ื ื•ื™ ื–ืžื ื™      
  90. else (case when TypeInActiveID=0 and TimeE_r>TimeS_1 then  TimeS_1 else TimeE_R end)      
  91. end) as TimeE_real      
  92. from tbl1      
  93. ),tblPre as      
  94. (      
  95. select * from tblRealTime where Times_real<>''      
  96. )
  97. --select times_real as [start],TimeE_real as [end],'['+cast(DATEPART(dw,odate)-1 as nvarchar)+']' as Day from tblPre
  98. select @bRet=1 from tblPre where times_real<=CAST(@DateTor as time)
  99. and TimeE_real>= cast(DATEADD(MINUTE, @nRange,@DateTor) as time)  
  100. return isnull(@bRet,0)
  101. end
  102. ALTER proc pr_UpdateAdministrativeAppointment
  103. @ID int=0,
  104. @ClientID int=1,
  105. @Yoman NVARCHAR(MAX)='',
  106. @Date1 datetime='',
  107. @Time1 nvarchar(5)='',
  108. @nRange int,
  109. @LocationId int=0,
  110. @type int=0,
  111. @Cyclic int=0,
  112. @tStampCreate datetime='',                                            
  113. @tStampUpdate datetime='',                                            
  114. @UserIDCreate int=0,                                            
  115. @UserIDUpdate int=0    
  116. as
  117. begin
  118. SET @tStampCreate = GetDate()
  119. SET @tStampUpdate = GetDate()
  120. if @Cyclic = 0 set @Cyclic = 1
  121. while @Cyclic > 0
  122. begin
  123. declare @TempDate datetime = dateadd(week, @cyclic - 1, @Date1)
  124. declare @torid int=0
  125. declare @msg nvarchar(max)=''
  126. declare @msgs nvarchar(max)=''
  127. DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
  128. SELECT value from UTILfn_Split(@Yoman,',')
  129. DECLARE @yomanid Int
  130. DECLARE @yomanName nvarchar(max)=''
  131. -- Open the cursor
  132. OPEN c
  133. FETCH NEXT FROM c INTO @yomanid
  134. WHILE (@@FETCH_STATUS = 0)
  135. BEGIN
  136. select @yomanName=w.NameView from tblyoman y
  137. inner join tblclients w on y.WorkerId=w.id
  138. where y.id=@yomanid
  139. /*ื‘ื“ื™ืงื” ืฉื–ื”ื” ื–ืžืŸ ืคืขื™ืœื•ืช ื•ืœื ื—ื•ืคืฃ ืœืชื•ืจ ืื—ืจ*/
  140. if (dbo.fn_checkYomanAllowTime(@yomanid,@TempDate,@TempDate,convert(nvarchar(10),@TempDate,101)+' '+@Time1,@nRange)=0)
  141. begin
  142.  set @msg='<br>'+ @msg + @yomanName +': ื”ื–ืžืŸ ืฉื ื‘ื—ืจ ืœืžื˜ืคืœ ื—ื•ืจื’ ืžื–ืžื ื™ ื”ืขื‘ื•ื“ื”'+ '<br>'
  143. end
  144. else if exists (select * from tblVisitList v where v.bDel=0 and v.YomanID=@yomanid
  145. and
  146.  (
  147.  convert(nvarchar(10),@TempDate,101)+' '+@Time1 > v.Date1 and convert(nvarchar(10),@TempDate,101)+' '+@Time1< DATEADD(MINUTE,v.TorTime,v.Date1)
  148.  or
  149.  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)
  150.  )
  151. )
  152. /*ื‘ื“ื™ืงืช ื”ืชื ื’ืฉื•ืช ืขื ืชื•ืจ ืงื™ื™ื*/
  153. begin
  154.  set @msg='<br>'+ @msg +@yomanName + ': ื”ื–ืžืŸ ืฉื ื‘ื—ืจ ื—ื•ืคืฃ ืœืชื•ืจ ืงื™ื™ื' + '<br>'
  155. end
  156. else
  157. begin
  158.  INSERT INTO [tblVisitList](                                            
  159.  ClientID,Yomanid,ParitKod,Date1,TorTime,ManagerTorID,/*ManagerTorID,GroupManagerTor,paritkod,nColor,*/                                        
  160.  UserIdCreate,tStampCreate,UserIdUpdate,tStampUpdate                                            
  161.  )                                            
  162.  values  (                                            
  163.  @ClientID,@YomanID,'',convert(nvarchar(10),@TempDate,101)+' '+@Time1,@nRange,@type,/*@ManagerTorID,@GroupManagerTor,'',@nColor,*/                                            
  164.  @UserIdUpdate,@tStampCreate,@UserIDUpdate,@tStampUpdate                                            
  165.  )
  166.  set @torid=SCOPE_IDENTITY()  
  167.  exec pr_TorLogUpdate @torid,1,@UserIDCreate
  168. end  
  169. FETCH NEXT FROM c INTO @yomanid
  170. END
  171. -- Close and deallocate the cursor
  172. CLOSE c
  173. DEALLOCATE c
  174.  set @msgs = @msgs + ', ' + @msg  
  175. SET @Cyclic = @Cyclic - 1
  176.  end
  177. if @msgs <> ''
  178.     select 'warning: ' + @msgs
  179. else
  180.     select 1
  181. end
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top