· 6 years ago · Oct 23, 2019, 11:30 AM
1delete from tblFormViewTemplate where key1='pr_RetMoveBlock'
2INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[Default],[InputSize2])VALUES('pr_RetMoveBlock',30,'תאריך בלוק',0,'Date1','EditTextBox',0,1,'','','d,m','','',2,'','','',12)
3INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[Default],[InputSize2])VALUES('pr_RetMoveBlock',40,'ש.התחלה',0,'StartTime','EditTextBox',0,1,'','','t,m','','',2,'','','',12)
4INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[Default],[InputSize2])VALUES('pr_RetMoveBlock',50,'ש.סיום',0,'EndTime','EditTextBox',0,0,'','','t,m','','',2,'','','',12)
5INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[Default],[InputSize2])VALUES('pr_RetMoveBlock',60,'חדר ניתוח',0,'RoomName','ddl',0,0,'RoomID','RoomName','_','','',0,'','','',0)
6GO
7----------------------------------
8Create PROCEDURE pr_RetMoveBlock
9@ID int=0,
10@Language nvarchar(max)='',
11@ProfileID int=0
12as
13BEGIN
14select * from tblFormViewTemplate ft where ft.Key1='pr_RetMoveBlock'
15and
16EXISTS(select * from tblProfileAuth where ProfileID=@ProfileID and Key1=ft.AllowView or ft.AllowView='' or ISNULL(ft.AllowView, '#')='#')
17order by ordId
18select
19b.id,
20Date1,
21StartTime,
22EndTime,
23room.item as RoomName,
24room.id as RoomID
25from tblBlocks b
26inner join tblDefRooms room on b.RoomID=room.id
27where b.id=@id
28SELECT rm.id as RoomID,rm.Item+' ['+
29isnull(
30Stuff(
31(
32select ','+tp.Item
33FROM tblRoomsToSubject sb
34inner join tbldefparitmiun tp on tp.id=sb.SubjectId
35where sb.RoomId=rm.id and sb.bRecommendation=0
36FOR XML PATH('')
37),1,1,''),'')+']' as RoomName
38from tbldefrooms rm
39order by rm.id
40END
41
42--------------------
43Create procedure pr_MoveBlock
44@id int=0,
45@Date1 datetime='',
46@StartTime nvarchar(10)='',
47@EndTime nvarchar(10)='',
48@RoomID int=0,
49@UserIdCreate int=0
50as
51begin
52if not exists (select * from tblBlocks where id=@id)
53begin
54select 'err: הבלוק לא קיים'
55return
56end
57/*משמש לתיעוד בלוג*/
58declare @srcB nvarchar(max)=''
59declare @srcA nvarchar(max)=''
60set @srcB=isnull((SELECT top 1 * FROM tblBlocks where @id>0 and id=@id FOR XML RAW),'')
61/*ברירת מחדל זה 1*/
62declare @orgroomId int=0
63declare @CyclicCounter smallint=1
64set @orgRoomId=isnull(@RoomID,0)
65declare @warning nvarchar(max)=''
66declare @bHaveSubject bit=0
67declare @SubjectID int = (select SubjectId from tblBlocks where id=@id)
68if cast(@Date1 as date)<cast(getdate() AS DATE)
69begin
70select 'err:אין אפשרות לקבוע בלוק על תאריך היסטורי'
71return
72end
73if (DATEDIFF(MINUTE, @StartTime ,@EndTime)<30)
74begin
75select 'err:זמן הבלוק המינימלי הוא 30 דקות'
76return
77end
78re1:
79/*בדיקת אימות שהחדר לא תפוס=מאושר לטווח שהתבקש */
80if @RoomID>0 and exists (select * from tblBlocks where BlockStatus=2 and RoomID=@RoomID and id<>@id
81and Date1=@Date1 and
82(
83StartTime between @StartTime and @Endtime
84or
85EndTime between @StartTime and @Endtime
86or
87((@StartTime > StartTime and @EndTime < EndTime) or
88(@StartTime < StartTime and @EndTime > EndTime))))
89begin
90select 'err: החדר הוקצה בשעות אלו עבור בלוק'
91return
92end
93/*באם אין חדר ניסיון להביא חדר פנוי*/
94if @RoomID=0
95Begin
96/*שלב 1 תוך ניסיון לבחור תחום*/
97select top 1 @RoomID=rm.id from tbldefrooms rm
98inner join tblRoomsToSubject rs on rs.roomid=rm.id and rs.bRecommendation=1 and rs.subjectid=@SubjectID
99and not exists
100(
101select * from tblBlocks bl
102where bl.RoomID=rm.id
103and
104BlockStatus=1
105and Roomid=rm.id
106and (Date1=@Date1
107and StartTime between @StartTime and @Endtime
108or EndTime between @StartTime and @Endtime)
109or
110((@StartTime > StartTime and @EndTime < EndTime)
111or
112(@StartTime < StartTime and @EndTime > EndTime))
113)
114/*ניסיון שני בלי תחום*/
115if @RoomID is null or @RoomID=0
116Begin
117select top 1 @RoomID=rm.id from tbldefrooms rm
118inner join tblRoomsToSubject rs on rs.roomid=rm.id and rs.bRecommendation=0 and rs.subjectid=@SubjectID
119and not exists
120(
121select * from tblBlocks bl
122where bl.RoomID=rm.id
123and
124BlockStatus=1
125and RoomId=rm.id
126and (Date1=@Date1
127and StartTime between @StartTime and @Endtime
128or EndTime between @StartTime and @Endtime)
129or
130((@StartTime > StartTime and @EndTime < EndTime)
131or
132(@StartTime < StartTime and @EndTime > EndTime))
133)
134End
135End
136/*בדיקה לפני שממשיכים שיש חדר*/
137if @RoomID is null or @RoomID=0
138begin
139select 'err: לא אותר חדר עבור התאריך המבוקש'
140return
141end
142/*יש חדר לא תואם לברירת המחדל*/
143if (@RoomID > 0)
144begin
145if @bHaveSubject=1 and not exists (select * from tblRoomsToSubject where roomid=@RoomID and subjectid=@SubjectID and bRecommendation=1)
146begin
147set @warning=@warning+'תאריך: '+convert(nvarchar(10),@Date1,103)+ ' שים לב: החדר שנבחר לא ברשימת התחומים המעודפים לתחום הניתוח'+ '<br/>'
148end
149end
150
151update tblBlocks
152set
153Date1 = @Date1,
154StartTime = @StartTime,
155EndTime = @EndTime,
156RoomID = @RoomID
157where @id=id
158nxtlvl:
159declare @Retid int
160declare @ActionID int
161set @ActionID=(case when @id>0 then 2 else 1 end)
162if @id>0
163 set @Retid=@id
164else
165set @Retid=SCOPE_IDENTITY()
166set @srcA=isnull((SELECT top 1 * FROM tblBlocks where @Retid>0 and id=@Retid FOR XML RAW),'')
167exec [pr_UpdateBlockLog] @Retid,@ActionID,@UserIdCreate,@srcB,@srcA
168if @warning<>''
169 select 'warning:' + @warning
170else
171 select @Retid
172end
173go
174ALTER procedure pr_UpdateT17
175@ID int=0,
176@ClientId int=0,
177@Date1 datetime,
178@Date2 datetime,
179@Reference nvarchar(max)='',
180@Deductible int=0,
181@Kod1 nvarchar(max)='',
182@KodSapak nvarchar(max)='',
183@Kod2 nvarchar(max)='',
184@KodSapak2 nvarchar(max)='',
185@Kod3 nvarchar(max)='',
186@KodSapak3 nvarchar(max)='',
187@FileScan nvarchar(max)='',
188@EquipmentSum money=0,
189@UserIDCreate int=-1
190as
191begin
192declare @warning nvarchar(max)=''
193declare @DateSur date
194declare @SponserID int=0
195declare @Relatedid int=0
196select @ClientId=sl.Clientid,@DateSur=cast(b.date1 as date),@SponserID=SponserID from tblsurgerieslist sl
197inner join tblblocks b on b.id=sl.blockid
198where sl.id=@ID
199if @SponserID in (50,51)
200begin
201select 'err:אין אפשרות להזין טופס התחייבות למטופל פרטי'
202return
203end
204/*אימות נתונים*/
205if @Date2<@Date1
206begin
207select 'err:טווח התאריכים לא חוקי'
208return
209end
210if (@date1>@DateSur or @date2<@dateSur)
211begin
212set @warning=@warning+ 'שים לב: טווח התאריכים שנבחר לא תואם לתאריך הניתוח'
213end
214/*בדיקה ראשונה לגבי כמות מול כמות*/
215if (
216select sum
217(
218(case when sl.Kod1<>'' then 1 else 0 end)
219+(case when sl.Kod2<>'' then 1 else 0 end)
220+(case when sl.Kod3<>'' then 1 else 0 end)
221)
222from tblSurgeriesList sl where id=@id)
223<>
224sum(case when @Kod1<>'' then 1 else 0 end)
225+(case when @Kod2<>'' then 1 else 0 end)+(case when @Kod3<>'' then 1 else 0 end)
226begin
227set @warning=@warning+'נא לשים לב:בטופס המשולב נבחרו קודי חיוב בכמות נמוכה מכמות הניתוחים'
228end
229if not exists (select * from tbl17Forms where surid=@id)
230begin
231insert into tbl17Forms (ClientID, BeginDate, EndDate, Reference, kod1,kod2,kod3,Deductible,surid,FileScan,EquipmentSum, UserIdCreate)
232values (@Clientid, @Date1,@Date2, @Reference,@Kod1,@kod2,@kod3,@Deductible,@id,@FileScan,@EquipmentSum, @UserIDCreate)
233set @Relatedid= SCOPE_IDENTITY()
234--עדכון קובץ הסריקה
235if @FileScan<>''
236begin
237insert into tblClientDocs(ClientID,NameDoc,folderDoc,RelatedID,Memo) values (@ClientId,@FileScan,@ClientId,@Relatedid,'סריקת טופס 17 עבור ניתוח')
238end
239end
240else
241begin
242update tbl17forms
243set
244BeginDate = @Date1,
245EndDate = @Date2,
246Reference = @Reference,
247kod1 = @Kod1,
248kod2 = @Kod2,
249kod3 = @Kod3,
250Deductible = @Deductible,
251clientid=@ClientId,
252FileScan=@FileScan,
253EquipmentSum=@EquipmentSum,
254UserIdUpdate=@UserIDCreate
255where surid=@ID
256if @warning<>''
257select 'warning:' + @warning
258else
259select @ID
260end
261end
262go
263delete from tblGridViewTemplate where key1='pr_GetAll_SurgeriesToDay2'
264INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',0,'מטופל',5,'Client','ItemView',0,0,'Client','ClientName',0,'_','pr_ClientAutocompleteHandler','',0,'')
265INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',10,'גיל מטופל',5,'Age','ItemView',0,0,'','',0,'_','','',0,'')
266INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',20,'רופא',5,'DoctorName','ItemView',0,0,'DoctorId','DoctorName',0,'_','','',0,'')
267INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',20,'ת.ניתוח',5,'Date1','ItemView',0,0,'DoctorId','DoctorName',0,'_','','',0,'')
268INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',30,'ניתוח',1,'SurgeryName','ItemView',0,0,'SurgeryId','SurgeryName',0,'_','','',0,'')
269INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',40,'שעת<br>התחלה',1,'Start','ItemView',0,0,'','',0,'t','','',0,'')
270INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',50,'תאריך שחרור <br>מתוכנן',5,'DateR','ItemView',0,0,'','',0,'t','','',0,'')
271INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',60,'גורם<br>מממן',5,'SponserName','ItemView',0,0,'Client','ClientName',0,'_','pr_ClientAutocompleteHandler','',0,'')
272INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',70,'זמן <br>מתוכנן',5,'Duration','ItemView',0,0,'Client','ClientName',0,'_','pr_ClientAutocompleteHandler','',0,'')
273INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',80,'זמן <br>בפועל',5,'DurationReal','ItemView',0,0,'Client','ClientName',0,'_','pr_ClientAutocompleteHandler','',0,'')
274INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',80,'קוד <br>קופה',5,'t17_1','ItemView',0,0,'','',0,'_','','',0,'')
275INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',80,'קוד <br>קופה-2',5,'t17_2','ItemView',0,0,'','',0,'_','','',0,'')
276INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',80,'קוד<br> קופה-3',5,'t17_3','ItemView',0,0,'','',0,'_','','',0,'')
277INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',80,'טופס<br>התחייבות',5,'t17','ItemView',0,0,'','',0,'_','','',0,'')
278INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',90,'קוד Icd',5,'Kod1','ItemView',0,0,'Client','ClientName',0,'_','pr_ClientAutocompleteHandler','',0,'')
279INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',120,'קוד <br>בפועל',5,'A1','ItemView',0,0,'Client','ClientName',0,'_','pr_ClientAutocompleteHandler','',0,'')
280INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',130,'משתלים <br>מתוכנן',5,'A2','ItemView',0,0,'Client','ClientName',0,'_','pr_ClientAutocompleteHandler','',0,'')
281INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',140,'משתלים<br> בפועל',5,'A3','ItemView',0,0,'Client','ClientName',0,'_','pr_ClientAutocompleteHandler','',0,'')
282INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',150,'ימי אשפוז<br>מתוכנן',5,'A4','ItemView',0,0,'Client','ClientName',0,'_','pr_ClientAutocompleteHandler','',0,'')
283INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',160,'ימי אשפוז<br> בפועל',5,'A5','ItemView',0,0,'Client','ClientName',0,'_','pr_ClientAutocompleteHandler','',0,'')
284INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',170,'שולם',5,'moneyStatus','ItemView',0,0,'','',0,'_','pr_ClientAutocompleteHandler','',0,'')
285INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',180,'סטאטוס',5,'Status','ItemView',0,0,'','',0,'_','pr_ClientAutocompleteHandler','',0,'')
286INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',190,'יוצר<br>זימון',5,'ZimunCreator','ItemView',0,0,'','',0,'_','pr_ClientAutocompleteHandler','',0,'')
287INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',200,'יוצר<br>התחייבות',5,'ChargeCreator','ItemView',0,0,'','',0,'_','pr_ClientAutocompleteHandler','',0,'')
288INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',210,'יוצר<br>קבלת<br>מטופל',5,'RecieptCreator','ItemView',0,0,'','',0,'_','pr_ClientAutocompleteHandler','',0,'')
289INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default])VALUES('pr_GetAll_SurgeriesToDay2',220,'הושלמה<br>בקרה',5,'BakaraDone','ItemView',0,0,'','',0,'_','pr_ClientAutocompleteHandler','',0,'')
290GO
291ALTER procedure pr_GetAll_SurgeriesToDay2
292@LocationID Int=0, --באם נשלח אפס אזי החיפוש הוא על כל המרפאות
293@Lang nvarchar(100)='',
294@UserId int=0,
295@Profile int=0,
296@Token nvarchar(max)='',
297@uid int=0,
298@docfilter int=0,
299@roomfilter int=0,
300@typefilter int=0,
301@date1 nvarchar(100)='',
302@Date2 nvarchar(100)='',
303@SearchParam nvarchar(100)=''
304as
305BEGIN
306select * from tblGridViewTemplate where Key1='pr_GetAll_SurgeriesToDay2' order by ordId
307select
308tblSurgeriesList.id as RelatedID,
309FORMAT(
310isnull(moneyStatus,0), 'C', 'he-IL') as moneyStatus
311,
312--tblSurgeriesList.clientid as id,
313cast(tblSurgeriesList.ID as nvarchar)+ ':'+ cast(tblSurgeriesList.clientid as nvarchar) as id,
314PMD.KODICD AS KOD1,'' as A1,'' as A2,'' as A3,IshpuzDays as a4,
315DATEDIFF(DAY,b.date1,GETDATE()) as a5,
316RoomId as RoomName, room.item as RoomId,s.nameview as SponserName,
317convert(nvarchar(10),b.Date1,103) as Date1,
318tblSurgeriesList.Start as [Start],
319Duration,
320'<li>' +pr.paritname + '</li>'
321+(case when tblSurgeriesList.Kod2<>'' then '<li>'+pr2.ParitName else '' end) + '</li>'
322+(case when tblSurgeriesList.Kod3<>'' then '<li>'+pr3.ParitName else '' end) + '</li>'
323as SurgeryName,
324(case when tblSurgeriesList.id=24 then '<i class="fa fa-money" aria-hidden="true"></i>' else '<span class="badge badge-danger">איו כיסוי</span>' end) as m1,
325(case when tblSurgeriesList.id=170 then '<i class="fas fa-id-card"></i>' else '<span class="badge badge-danger">לא</span>' end) as m2,
326(case when tblSurgeriesList.id=11 then 'העברת כרטיס' else 'לא הגיע' end) as m3,
327w.NameView as DoctorName, w.id as DoctorId,tblSurgeriesList.Duration as Duration,
328(case when smd.surgerystary<>'' and smd.surgeryend<>'' then DATEDIFF(MINUTE,surgerystary,surgeryend) else '' end) as DurationReal,
329convert(nvarchar(10),DATEADD(dd,IshpuzDays,b.Date1),103) as dateR,
330pr.ParitName as SurgeryName,
331'' as CapitalName,t17_1,t17_2,t17_3,
332ss.Item as Status,
333(case when Reference<>'' AND filescan<>'' then
334'<a target="_blank" href="http://192.168.1.21:444/' +filescan + '">'+Reference+'</a>'
335WHEN
336Reference<>'' AND filescan='' then '<b><font color=red>!'+ Reference + '</font></b>'
337when tblSurgeriesList.SponserID not in (50,51) then 'נדרש טופס התחייבות' else '' end)
338as t17,
339tblClients2.NameView as ClientName, tblClients2.NameView as Client,
340(CONVERT(int,CONVERT(char(8),getdate(),112))-CONVERT(char(8),cd.BirthDay,112))/10000 as Age,
341'' as ZimunCreator,
342'' as ChargeCreator,
343'' as RecieptCreator,
344case when BakaraDone=0 then '<i class="fa fa-times"></i>' else '<i class="fa fa-check"></i>' end as BakaraDone
345from tblSurgeriesList
346inner join tblPritim pr on pr.Kod=tblSurgeriesList.kod1
347left join tblPritim pr2 on pr2.Kod=tblSurgeriesList.kod2
348left join tblPritim pr3 on pr3.Kod=tblSurgeriesList.kod3
349inner join tblBlocks b on b.id=tblSurgeriesList.Blockid
350inner join tblClients w on w.id=b.Doctorid
351INNER join tblDefRooms room on B.roomid=room.id
352inner join tblDefSurgeryStatus ss on ss.id=tblSurgeriesList.StatusID
353inner join tblclients tblClients2 on tblSurgeriesList.clientid=tblclients2.id
354inner join tblClientDetails cd on tblclients2.id=cd.mainid
355inner join tblclients s on tblSurgeriesList.sponserid=s.id
356LEFT JOIN tblPritimMoreDetails PMD ON PMD.ParitKod=PR.Kod
357left join tblSurgerieMoreDetails smd on smd.surid=tblSurgeriesList.id
358outer apply(
359select 1.17*sum(case when DocNameID in(15) then -1*docsum else DocSum end) as moneyStatus
360from tblHeshDocuments where DocNameID in(1,14,15,7,18) and tblHeshDocuments.ClientID=tblSurgeriesList.clientid
361) as tblMoneyStatus
362outer apply
363(
364select top 1 Reference,FileScan,
365lpd.kodsapak as t17_1,
366lpd2.kodsapak as t17_2,
367lpd3.kodsapak as t17_3
368FROM tbl17Forms t17
369inner join tblSurgeriesList sl on sl.id=t17.surId
370INNER JOIN tblListPrices lp ON lp.ParentID=sl.SponserID
371left join tblPritim p on p.kod=t17.kod1
372left join tblListPriceDetails lpd on lpd.ListID=lp.id and lpd.ParitKod=p.Kod
373left join tblPritim p2 on p2.kod=t17.kod2
374left join tblListPriceDetails lpd2 on lpd.ListID=lp.id and lpd2.ParitKod=p2.Kod
375left join tblPritim p3 on p3.kod=t17.kod3
376left join tblListPriceDetails lpd3 on lpd.ListID=lp.id and lpd3.ParitKod=p3.Kod
377where t17.surid=tblSurgeriesList.id
378) as t17
379where
3801=1
381and (@roomfilter=0 or room.id=@roomfilter)
382and (@docfilter=0 or w.id=@docfilter)
383and (@typefilter=0 or b.SubjectID=@typefilter)
384--and ((@date1<>'' or @date2<>'') or b.date1>=cast(getdate() as date))
385and (@date1='' or b.Date1>=@date1)
386and (@date2='' or b.Date1<=@date2)
387and (@SearchParam='' or ((tblClients2.NameView like '%' + @SearchParam + '%' ) or (tblClients2.idCard like '%' + @SearchParam+ '%')))
388and tblSurgeriesList.StatusID=100
389END
390go
391ALTER TABLE tblSurgeriesList
392add BakaraDone bit not null default(0)
393go
394create procedure pr_MarkBakaraDone
395@surgeryid int
396as
397begin
398update tblSurgeriesList
399set BakaraDone=1
400OUTPUT INSERTED.*
401where id=@surgeryid
402end
403go
404insert into tblEnvDataToEnvironment (Env, EnvKey, Orderid) values ('envSurgerieBakara', 'SurgeryScanAndHesh', 1)
405go