bCf5dxjw

1alter proc pr_GetClientsSurgeryDetails 
2@id int
3as
4begin
5select top 1 
6c.NameView, 
7Mobile,
8NameMainPersonContact,
9TelMainPersonContact,
10ParitName as SurgeryName,
11bl.Date1 + sl.Start as SurgeryDate,
12sponser.NameView as Sponser,
13sl.FileScan1 + ',' + sl.FileScan2 +
14sl.FileScan3 + ',' + sl.FileScan4 +
15sl.FileScan5 + ',' + sl.FileScan6 +
16sl.FileScan7 + ',' + sl.FileScan8 +
17sl.FileScan9 + ',' + sl.FileScan10 as Files,
18doctor.nameview as Doctor,
19isnull(t17.Deductible,0)
20+sl.SurgerySum
21+(case when sl.Wage>1 and (sl.SponserID in (50,51) or sl.WageBy=1)  then sl.Wage else 0 end) as Costs,
22isnull(t17.Deductible,0)
23+sl.SurgerySum
24+(case when sl.Wage>1 and (sl.SponserID in (50,51) or sl.WageBy=1)  then sl.Wage else 0 end)
25-
26isnull(SumK,0) as Charge,
27isnull(ex.CostsEx, 0) as CostsEx
28from tblClients c
29left join tblClientDetails cd on cd.mainid=c.id
30left join tblSurgeriesList sl on sl.ClientID=c.id
31left join tblBlocks bl on bl.id=sl.BlockID
32left join tblClients doctor on doctor.id=bl.DoctorId
33left join tblPritim pr on pr.Kod=sl.Kod1
34left join tblClients sponser on sponser.id=sl.SponserID
35left join tblSurgerieMoreDetails smd on smd.surid=sl.id
36left join tbl17Forms t17 on t17.surId=sl.id and t17.bDel=0
37outer apply (
38select sum(Quantity * Price) as CostsEx from tblClientCharges where ClientId=@id group by ClientId
39) as ex
40outer apply
41(
42select sum((case when p.docnameid=2 then 1 else -1 end)*DocSum) as SumK from tblHeshDocuments p 
43where p.ClientID=sl.ClientID
44and p.surid=sl.id
45and p.DocNameID in (2,18)
46) as tblK
47where c.id=@id
48and cast(bl.Date1 as date)<=cast(getdate() as date) 
49end
50
51go
52create table tblChameleonTrans (
53id int primary key not null identity(1,1),
54SurgeryId int not null default(0),
55ChameleonStatusId int not null default(0),
56ChameleonStatusText nvarchar(max) not null default('')
57)
58go
59alter table tblChameleonTrans
60add Date1 datetime not null
61go
62alter table tblChameleonTrans
63add Department nvarchar(100) not null default('')
64go
65alter proc pr_Chameleon_TransClient(
66@ID_NUM nvarchar(max)='',
67@Trans_type int,
68@Department_desc nvarchar(max)='',
69@DateAc nvarchar(100)=''
70)
71as
72begin
73declare @Surid int=0
74declare @KodIcdID int=0
75DECLARE @DateAct DATETIME=NULL 
76IF @DateAc<>''
77BEGIN
78if ISDATE(@DateAc)=1
79set @DateAct=cast(@DateAc as datetime)
80else
81set @DateAct=convert(datetime, @DateAc, 103)
82END
83select top 1 @Surid=sl.id from tblSurgeriesList sl
84inner join tblClients c on c.id=sl.ClientID
85inner join tblBlocks b on b.id=sl.blockid
86where 
87sl.StatusID=100
88--and convert(date,@SERV_DATE,103)=cast(b.Date1 as date)
89and
90Right('00000000000000000000' + CONVERT(NVARCHAR, c.idCard), 20)
91=
92Right('00000000000000000000' + CONVERT(NVARCHAR, @ID_NUM), 20)
93order by  sl.id desc
94if isnull(@Surid,0)=0 return
95if not exists (select * from tblSurgerieMoreDetails where surid=@Surid)
96Begin
97insert into tblSurgerieMoreDetails(surid,useridcreate) values (@Surid,10)
98end
99/*ניסיון להכניס נתונים לגבי הניתוח*/
100--התאוששות
101if @Trans_type=20 and @Department_desc in ('התאוששות בת ים','התאוששות ריינס') and  not @DateAct is null
102begin
103update tblSurgerieMoreDetails set RecoveryStart=convert(nvarchar(5),@DateAct,108) where surid=@Surid and RecoveryStart=''
104end
105--התאוששות
106if @Trans_type=20 and @Department_desc in ('אשפוז בת ים') and  not @DateAct  is null
107and exists (select * from tblSurgerieMoreDetails md where md.surid=@surid and md.SurgeryExit<>'' )
108begin
109update tblSurgerieMoreDetails set departmentStart=convert(nvarchar(5),@DateAct,108) where surid=@Surid and departmentStart=''
110end
111--עזיבה
112if @Trans_type in (50,60)  and  not @DateAct is null
113begin
114update tblSurgerieMoreDetails set ReleaseTime=convert(nvarchar(5),@DateAct,108),ReleaseDate=convert(nvarchar(10),@DateAct,103)
115where surid=@Surid and ReleaseTime=''
116end
117declare @ChameleonTransStatus nvarchar(max)=''
118if @Trans_type=50
119set @ChameleonTransStatus='שחרור הביתה מהתאוששות'
120else if @Trans_type=60
121set @ChameleonTransStatus='שחרור הביתה מאשפוז'
122else if @Trans_type=20 
123set @ChameleonTransStatus='כניסה ל-'+@Department_desc
124-- Added 23.03.2020 by Hagai Wild 
125insert into 
126tblChameleonTrans (SurgeryId, ChameleonStatusId, ChameleonStatusText, Date1, Department)
127values (@SurId, @Trans_type, @ChameleonTransStatus, @DateAct, @Department_desc)
128--
129if @ChameleonTransStatus<>'' 
130Begin
131update tblSurgeriesList set ChameleonTransStatus=@ChameleonTransStatus where id=@Surid
132end
133end
134go
135alter proc pr_GetClientRoute
136@ClientId int
137as
138begin
139select * from tblGridViewTemplate where key1='pr_GetClientRoute' order by ordid
140select distinct
141ct.id,
142case when cd.bSpecial=1 then '<strong style="color:red">' + ChameleonStatusText + '</strong>' else ChameleonStatusText end as ChameleonStatusText,
143Date1,
144Department
145from tblChameleonTrans ct
146inner join tblSurgeriesList sl on sl.id=ct.surgeryid
147inner join tblClients c on c.id=sl.clientid
148left join tblClientDetails cd on c.id=cd.mainid
149where ClientID=@ClientID
150union all 
151select 0 as id,
152'כניסה ל-חדר ניתוח' as ChameleonStatusText,
153'' as Date1,
154'תנועה עתידית' as Department
155where not exists (select * from tblChameleonTrans ct
156inner join tblSurgeriesList sl on sl.id=ct.SurgeryId
157where clientid=@ClientId and (ChameleonStatusText like 'כניסה ל-חדר ניתוח%' or ChameleonStatusText like 'שחרור הביתה%'))
158union all 
159select top 1
1600 as id,
161case when icu=0 then 'כניסה ל-התאוששות' else 'כניסה ל-טיפול נמרץ' end as ChameleonStatusText,
162'' as Date1,
163'תנועה עתידית' as Department
164from tblSurgeriesList sl
165inner join tblBlocks bl on bl.id=sl.blockid
166where cast(bl.date1 as date)<=cast(getdate() as Date) and clientid=@ClientId
167and not exists (select * from tblChameleonTrans ct
168inner join tblSurgeriesList sl on sl.id=ct.SurgeryId
169where clientid=@ClientId and (ChameleonStatusText like 'כניסה ל-התאוששות%' 
170or ChameleonStatusText like 'כניסה ל-טיפול נמרץ%' or ChameleonStatusText like 'שחרור הביתה%'))
171union all 
172select 0 as id,
173'כניסה ל-אשפוז' as ChameleonStatusText,
174'' as Date1,
175'תנועה עתידית' as Department
176where not exists (select * from tblChameleonTrans ct
177inner join tblSurgeriesList sl on sl.id=ct.SurgeryId
178where clientid=@ClientId and (ChameleonStatusText like 'כניסה ל-אשפוז%' or ChameleonStatusText like 'שחרור הביתה%'))
179end
180go
181delete from tblGridViewTemplate where key1='pr_GetClientRoute'
182INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default],[AllowView])VALUES('pr_GetClientRoute',0,'סטאטוס',20,'ChameleonStatusText','ItemView',0,0,'','',0,'_','','',4,'','')
183INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default],[AllowView])VALUES('pr_GetClientRoute',18,'תאריך',20,'Date1','ItemView',0,0,'','',0,'_','','',4,'','')
184INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default],[AllowView])VALUES('pr_GetClientRoute',28,'מחלקה',20,'Department','ItemView',0,0,'','',0,'_','','',4,'','')
185GO
186alter proc pr_RetDataForFamilies
187as
188begin
189select * from tblGridViewTemplate where key1='pr_RetDataForFamilies' order by ordid
190;with tbl1 as (
191select 
192sl.id,
193ROW_NUMBER() over (partition by sl.id order by ct.date1 desc) as row#,
194sl.id as SurId,
195client.Fname + ' ' + left(client.Lname, 1) as NameView, 
196ct.Department, 
197ct.Date1,
198ct.ChameleonStatusText
199from tblSurgeriesList sl
200inner join tblBlocks bl on bl.id=sl.BlockID
201inner join tblClients client on client.id=sl.ClientID
202left join tblChameleonTrans ct on ct.surgeryid=sl.id
203where -- icu=1 and
204cast(bl.Date1 as date)=cast(getdate() as date)
205)
206select * from tbl1 
207where row#=1
208and (ChameleonStatusText not like '%אשפוז%' or (ChameleonStatusText like 'כניסה ל-אשפוז%' and Date1 < dateadd(minute, 10, getdate())))
209end
210go
211delete from tblGridViewTemplate where key1='pr_RetDataForFamilies'
212INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default],[AllowView])VALUES('pr_RetDataForFamilies',0,'מזהה ניתוח',20,'SurId','ItemView',0,0,'','',0,'_','','',4,'','')
213INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default],[AllowView])VALUES('pr_RetDataForFamilies',18,'שם',20,'NameView','ItemView',0,0,'','',0,'_','','',4,'','')
214INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default],[AllowView])VALUES('pr_RetDataForFamilies',28,'יחידה',20,'Department','ItemView',0,0,'','',0,'_','','',4,'','')
215GO
216
217delete from tblGridViewTemplate where key1='pr_GetClientHistory'
218INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default],[AllowView])VALUES('pr_GetClientHistory',0,'ביקור',20,'Institute','ItemView',0,0,'','',0,'_','','',4,'','')
219INSERT INTO [tblGridViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[bTableKey],[ValidatorStatus],[Param3],[ParentField],[InputSize],[Default],[AllowView])VALUES('pr_GetClientHistory',18,'תאריך',20,'Date1','ItemView',0,0,'','',0,'_','','',4,'','')
220GO
221alter proc pr_GetClientHistory
222@clientId int
223as
224begin
225select * from tblGridViewTemplate where key1='pr_GetClientHistory' order by ordid
226select 
227vl.id,
228ParitName as Institute,
229Date1
230from tblVisitList vl
231inner join tblPritim pr on Kod=ParitKod
232where ClientID=@clientId
233union all 
234select
235sl.id,
236ParitName as Institute,
237Date1
238from tblSurgeriesList sl
239inner join tblPritim pr on pr.Kod=sl.Kod1
240left join tblBlocks bl on bl.id=BlockID
241where ClientID=@clientId and cast(Date1 as date) < cast(getdate() as date)
242order by Date1
243end
244go