delete from tblFormViewTemplate where key1='pr_RetMoveBlock'
INSERT 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)
INSERT 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)
INSERT 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)
INSERT 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)
GO
----------------------------------
Create PROCEDURE pr_RetMoveBlock
@ID int=0,
@Language nvarchar(max)='',
@ProfileID int=0
as
BEGIN
select  * from tblFormViewTemplate ft where ft.Key1='pr_RetMoveBlock'
and
EXISTS(select * from tblProfileAuth where ProfileID=@ProfileID and Key1=ft.AllowView or ft.AllowView='' or ISNULL(ft.AllowView, '#')='#')
order by ordId
select
b.id,
Date1,
StartTime,
EndTime,
room.item as RoomName,
room.id as RoomID
from tblBlocks b
inner join tblDefRooms room on b.RoomID=room.id
where b.id=@id
SELECT rm.id as RoomID,rm.Item+' ['+
isnull(
Stuff(
(
select ','+tp.Item
FROM tblRoomsToSubject sb
inner join tbldefparitmiun tp on tp.id=sb.SubjectId
where sb.RoomId=rm.id and sb.bRecommendation=0
FOR XML PATH('')
),1,1,''),'')+']'  as RoomName
from tbldefrooms  rm
order by rm.id
END

--------------------
Create  procedure pr_MoveBlock
@id int=0,
@Date1 datetime='',
@StartTime nvarchar(10)='',
@EndTime nvarchar(10)='',
@RoomID int=0,
@UserIdCreate int=0
as
begin
if not exists (select * from tblBlocks where id=@id)
begin
select 'err: הבלוק לא קיים'
return
end
/*משמש לתיעוד בלוג*/
declare @srcB nvarchar(max)=''
declare @srcA nvarchar(max)=''
set @srcB=isnull((SELECT top 1 * FROM tblBlocks where @id>0 and id=@id FOR XML RAW),'')
/*ברירת מחדל זה 1*/
declare @orgroomId int=0
declare @CyclicCounter smallint=1
set @orgRoomId=isnull(@RoomID,0)
declare @warning nvarchar(max)=''
declare  @bHaveSubject bit=0
declare @SubjectID int = (select SubjectId from tblBlocks where id=@id)
if cast(@Date1 as date)<cast(getdate() AS DATE)
begin
select 'err:אין אפשרות לקבוע בלוק על תאריך היסטורי'
return
end
if (DATEDIFF(MINUTE, @StartTime ,@EndTime)<30)
begin
select 'err:זמן הבלוק המינימלי הוא 30 דקות'
return
end
re1:
/*בדיקת אימות שהחדר לא תפוס=מאושר לטווח שהתבקש */
if @RoomID>0 and exists (select  *  from tblBlocks where BlockStatus=2 and RoomID=@RoomID  and id<>@id
and Date1=@Date1 and
(
StartTime between @StartTime and @Endtime
or
EndTime between @StartTime and @Endtime
or
((@StartTime > StartTime and @EndTime < EndTime) or
(@StartTime < StartTime and @EndTime > EndTime))))
begin
select 'err: החדר הוקצה בשעות אלו עבור בלוק'
return
end
/*באם אין חדר ניסיון להביא חדר פנוי*/
if @RoomID=0
Begin
/*שלב 1 תוך ניסיון לבחור תחום*/
select top 1 @RoomID=rm.id from tbldefrooms rm
inner join tblRoomsToSubject rs on rs.roomid=rm.id and rs.bRecommendation=1 and rs.subjectid=@SubjectID
and not exists
(
select * from tblBlocks bl
where bl.RoomID=rm.id
and
BlockStatus=1
and Roomid=rm.id
and (Date1=@Date1
and StartTime between @StartTime and @Endtime
or EndTime between @StartTime and @Endtime)
or
((@StartTime > StartTime and @EndTime < EndTime)
or
(@StartTime < StartTime and @EndTime > EndTime))
)
/*ניסיון שני בלי תחום*/
if @RoomID is null or @RoomID=0
Begin
select top 1 @RoomID=rm.id from tbldefrooms rm
inner join tblRoomsToSubject rs on rs.roomid=rm.id and rs.bRecommendation=0 and rs.subjectid=@SubjectID
and not exists
(
select * from tblBlocks bl
where bl.RoomID=rm.id
and
BlockStatus=1
and RoomId=rm.id
and (Date1=@Date1
and StartTime between @StartTime and @Endtime
or EndTime between @StartTime and @Endtime)
or
((@StartTime > StartTime and @EndTime < EndTime)
or
(@StartTime < StartTime and @EndTime > EndTime))
)
End
End
/*בדיקה לפני שממשיכים שיש חדר*/
if @RoomID is null or @RoomID=0
begin
select 'err: לא אותר חדר עבור התאריך המבוקש'
return
end
/*יש חדר לא תואם לברירת המחדל*/
if (@RoomID > 0)
begin
if @bHaveSubject=1  and not exists (select * from tblRoomsToSubject where  roomid=@RoomID and subjectid=@SubjectID and bRecommendation=1)
begin
set @warning=@warning+'תאריך: '+convert(nvarchar(10),@Date1,103)+ ' שים לב: החדר שנבחר לא ברשימת התחומים המעודפים לתחום הניתוח'+ '<br/>'
end
end

update tblBlocks
set
Date1 = @Date1,
StartTime = @StartTime,
EndTime = @EndTime,
RoomID = @RoomID
where @id=id
nxtlvl:
declare @Retid int
declare @ActionID int
set @ActionID=(case when @id>0 then 2 else 1 end)
if @id>0
 set @Retid=@id
else
set @Retid=SCOPE_IDENTITY()
set @srcA=isnull((SELECT top 1 * FROM tblBlocks where @Retid>0 and id=@Retid FOR XML RAW),'')
exec [pr_UpdateBlockLog] @Retid,@ActionID,@UserIdCreate,@srcB,@srcA
if @warning<>''
 select 'warning:' + @warning
else
 select @Retid
end
go
ALTER  procedure pr_UpdateT17
@ID int=0,
@ClientId int=0,
@Date1 datetime,
@Date2 datetime,
@Reference nvarchar(max)='',
@Deductible int=0,
@Kod1 nvarchar(max)='',
@KodSapak nvarchar(max)='',
@Kod2 nvarchar(max)='',
@KodSapak2 nvarchar(max)='',
@Kod3 nvarchar(max)='',
@KodSapak3 nvarchar(max)='',
@FileScan nvarchar(max)='',
@EquipmentSum money=0,
@UserIDCreate int=-1
as
begin
declare @warning nvarchar(max)=''
declare @DateSur date
declare @SponserID int=0
declare @Relatedid int=0
select @ClientId=sl.Clientid,@DateSur=cast(b.date1 as date),@SponserID=SponserID from tblsurgerieslist  sl
inner join tblblocks b on b.id=sl.blockid
where sl.id=@ID
if @SponserID in (50,51)
begin
select 'err:אין אפשרות להזין טופס התחייבות למטופל פרטי'
return
end
/*אימות נתונים*/
if @Date2<@Date1
begin
select 'err:טווח התאריכים לא חוקי'
return
end
if (@date1>@DateSur or @date2<@dateSur)
begin
set @warning=@warning+ 'שים לב: טווח התאריכים שנבחר לא תואם לתאריך הניתוח'
end
/*בדיקה ראשונה לגבי כמות מול כמות*/
if (
select sum
(
(case when sl.Kod1<>'' then 1 else 0 end)
+(case when sl.Kod2<>'' then 1 else 0 end)
+(case when sl.Kod3<>'' then 1 else 0 end)
)
from tblSurgeriesList sl where id=@id)
<>
sum(case when @Kod1<>'' then 1 else 0 end)
+(case when @Kod2<>'' then 1 else 0 end)+(case when @Kod3<>'' then 1 else 0 end)
begin
set @warning=@warning+'נא לשים לב:בטופס המשולב נבחרו קודי חיוב בכמות נמוכה מכמות הניתוחים'
end
if not exists (select * from tbl17Forms where surid=@id)
begin
insert into tbl17Forms (ClientID, BeginDate, EndDate, Reference, kod1,kod2,kod3,Deductible,surid,FileScan,EquipmentSum, UserIdCreate)
values (@Clientid, @Date1,@Date2, @Reference,@Kod1,@kod2,@kod3,@Deductible,@id,@FileScan,@EquipmentSum, @UserIDCreate)
set @Relatedid= SCOPE_IDENTITY()
--עדכון קובץ הסריקה
if @FileScan<>''
begin
insert into tblClientDocs(ClientID,NameDoc,folderDoc,RelatedID,Memo) values (@ClientId,@FileScan,@ClientId,@Relatedid,'סריקת טופס 17 עבור ניתוח')
end
end
else
begin
update tbl17forms
set
BeginDate =  @Date1,
EndDate = @Date2,
Reference = @Reference,
kod1 = @Kod1,
kod2 = @Kod2,
kod3 = @Kod3,
Deductible = @Deductible,
clientid=@ClientId,
FileScan=@FileScan,
EquipmentSum=@EquipmentSum,
UserIdUpdate=@UserIDCreate
where surid=@ID
if @warning<>''
select 'warning:' + @warning
else
select @ID
end
end
go
delete from tblGridViewTemplate where key1='pr_GetAll_SurgeriesToDay2'
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
INSERT 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,'')
GO
ALTER  procedure pr_GetAll_SurgeriesToDay2
@LocationID Int=0, --באם נשלח אפס אזי החיפוש הוא על כל המרפאות
@Lang nvarchar(100)='',
@UserId int=0,
@Profile int=0,
@Token nvarchar(max)='',
@uid int=0,
@docfilter int=0,
@roomfilter int=0,
@typefilter int=0,
@date1 nvarchar(100)='',
@Date2 nvarchar(100)='',
@SearchParam nvarchar(100)=''
as
BEGIN
select  * from tblGridViewTemplate where Key1='pr_GetAll_SurgeriesToDay2' order by ordId
select
tblSurgeriesList.id as RelatedID,
FORMAT(
isnull(moneyStatus,0), 'C', 'he-IL') as moneyStatus
,
--tblSurgeriesList.clientid as id,
cast(tblSurgeriesList.ID as nvarchar)+ ':'+ cast(tblSurgeriesList.clientid as nvarchar) as id,
PMD.KODICD AS KOD1,'' as A1,'' as A2,'' as A3,IshpuzDays as a4,
DATEDIFF(DAY,b.date1,GETDATE()) as a5,
RoomId as RoomName, room.item as RoomId,s.nameview as SponserName,
convert(nvarchar(10),b.Date1,103) as Date1,
tblSurgeriesList.Start as [Start],
Duration,
'<li>' +pr.paritname + '</li>'
+(case when tblSurgeriesList.Kod2<>'' then '<li>'+pr2.ParitName else '' end) + '</li>'
+(case when tblSurgeriesList.Kod3<>'' then '<li>'+pr3.ParitName else '' end) + '</li>'
as SurgeryName,
(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,
(case when tblSurgeriesList.id=170 then '<i class="fas fa-id-card"></i>' else '<span class="badge badge-danger">לא</span>' end) as m2,
(case when tblSurgeriesList.id=11 then 'העברת כרטיס' else 'לא הגיע' end) as m3,
w.NameView as DoctorName, w.id as DoctorId,tblSurgeriesList.Duration as Duration,
(case when smd.surgerystary<>'' and smd.surgeryend<>'' then DATEDIFF(MINUTE,surgerystary,surgeryend) else '' end)  as DurationReal,
convert(nvarchar(10),DATEADD(dd,IshpuzDays,b.Date1),103) as dateR,
pr.ParitName as SurgeryName,
'' as CapitalName,t17_1,t17_2,t17_3,
ss.Item as Status,
(case when Reference<>'' AND filescan<>'' then
'<a target="_blank" href="http://192.168.1.21:444/'  +filescan + '">'+Reference+'</a>'
WHEN
Reference<>'' AND filescan='' then  '<b><font color=red>!'+ Reference + '</font></b>'
when tblSurgeriesList.SponserID not in (50,51) then 'נדרש טופס התחייבות' else '' end)
as t17,
tblClients2.NameView as ClientName, tblClients2.NameView as Client,
(CONVERT(int,CONVERT(char(8),getdate(),112))-CONVERT(char(8),cd.BirthDay,112))/10000 as Age,
'' as ZimunCreator,
'' as ChargeCreator,
'' as RecieptCreator,
case when BakaraDone=0 then '<i class="fa fa-times"></i>' else '<i class="fa fa-check"></i>' end as BakaraDone
from tblSurgeriesList
inner join tblPritim pr on pr.Kod=tblSurgeriesList.kod1
left join tblPritim pr2 on pr2.Kod=tblSurgeriesList.kod2
left join tblPritim pr3 on pr3.Kod=tblSurgeriesList.kod3
inner join tblBlocks b on b.id=tblSurgeriesList.Blockid
inner join tblClients w on w.id=b.Doctorid
INNER join tblDefRooms room on B.roomid=room.id
inner join tblDefSurgeryStatus ss on ss.id=tblSurgeriesList.StatusID
inner join tblclients  tblClients2 on tblSurgeriesList.clientid=tblclients2.id
inner join tblClientDetails cd on tblclients2.id=cd.mainid
inner join tblclients  s on tblSurgeriesList.sponserid=s.id
LEFT JOIN tblPritimMoreDetails PMD ON PMD.ParitKod=PR.Kod
left join tblSurgerieMoreDetails smd on smd.surid=tblSurgeriesList.id
outer apply(
select 1.17*sum(case when DocNameID in(15) then -1*docsum else DocSum  end) as moneyStatus
from tblHeshDocuments where  DocNameID in(1,14,15,7,18) and   tblHeshDocuments.ClientID=tblSurgeriesList.clientid
) as tblMoneyStatus
outer apply
(
select top 1 Reference,FileScan,
lpd.kodsapak as t17_1,
lpd2.kodsapak as t17_2,
lpd3.kodsapak as t17_3
FROM tbl17Forms t17
inner join tblSurgeriesList sl on sl.id=t17.surId
INNER JOIN tblListPrices lp ON lp.ParentID=sl.SponserID
left join tblPritim p on p.kod=t17.kod1
left join tblListPriceDetails lpd on lpd.ListID=lp.id and lpd.ParitKod=p.Kod
left join tblPritim p2 on p2.kod=t17.kod2
left join tblListPriceDetails lpd2 on lpd.ListID=lp.id and lpd2.ParitKod=p2.Kod
left join tblPritim p3 on p3.kod=t17.kod3
left join tblListPriceDetails lpd3 on lpd.ListID=lp.id and lpd3.ParitKod=p3.Kod
where t17.surid=tblSurgeriesList.id
) as t17
where
1=1
and (@roomfilter=0 or room.id=@roomfilter)
and (@docfilter=0 or w.id=@docfilter)
and (@typefilter=0 or b.SubjectID=@typefilter)
--and ((@date1<>'' or @date2<>'') or b.date1>=cast(getdate() as date))
and (@date1=''  or b.Date1>=@date1)
and (@date2='' or b.Date1<=@date2)
and (@SearchParam='' or   ((tblClients2.NameView like  '%' + @SearchParam +  '%' ) or (tblClients2.idCard like  '%' + @SearchParam+  '%')))
and tblSurgeriesList.StatusID=100
END
go
ALTER TABLE tblSurgeriesList
add BakaraDone bit not null default(0)
go
create procedure pr_MarkBakaraDone
@surgeryid int
as
begin
update tblSurgeriesList
set BakaraDone=1
OUTPUT INSERTED.*
where id=@surgeryid
end
go
insert into tblEnvDataToEnvironment (Env, EnvKey, Orderid) values ('envSurgerieBakara', 'SurgeryScanAndHesh', 1)
go