DELETE FROM tblFormViewTemplate WHERE key1='pr_retsurgeries'
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',10,'בלוק',400,'BlockName','HTML',0,0,'','','_','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',20,'מטופל',400,'ClientHeader','HTML',0,0,'','','_','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',30,'',400,'','HTML',0,0,'<div style="background-color: rgba(239, 239, 239, 0.50);padding:10px"><h3><i class="fas fa-list"></i> פרטי ניתוח</h3>','','_','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',31,'ניתוח מרכזי',400,'KodName1','ddl',0,1,'Kod1','KodName1','_','','',4,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',32,'ניתוח משני',400,'KodName2','ddl',0,1,'Kod2','KodName2','_','','',4,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',33,'ניתוח משני',400,'KodName3','ddl',0,0,'Kod3','KodName3','_','','',4,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',40,'שעת התחלה',400,'Start','EditTextBox',0,1,'','','t,m','','',2,'',NULL,'',11)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',50,'משך (דקות)',400,'Duration','EditTextBox',0,0,'','','m,n|1','','',2,'',NULL,'',12)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',60,'',400,'','HTML',0,0,'</div>','','_','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',70,'',400,'','HTML',0,0,'<div style="background-color: rgba(239, 239, 239, 0.50);padding:10px"><h3><i class="fas fa-cog"></i> ציוד</h3>','','_','','',12,'',NULL,'',0)
GO
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',80,'ציוד קפיטלי',400,'CapitalList','ddlMulti',0,1,'CapitalList','CapitalListName','_','','',6,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',90,'ציוד מתכלה',400,'pritimConsumableList','ddlMulti',0,0,'pritimConsumableList','pritimConsumableListName','_','','',6,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',100,'משתלים',400,'pritimimplantsList','ddlMulti',0,0,'pritimimplantsList','pritimimplantsListName','_','','',6,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',110,'',400,'','HTML',0,0,'</div>','','_','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',120,'',400,'','HTML',0,0,'<div style="background-color: rgba(239, 239, 239, 0.50);padding:10px"><h3><i class="fa fa-hospital-o"></i> מידע רפואי</h3>','','_','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',125,'קוד אבחנה רפואית',200,'NameAvhana','Autocomplete',0,0,'KodAvhana','NameAvhana','_','pr_AvhanaAutocompleteHandler','',0,NULL,NULL,'',12)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',130,'סוג הרדמה',400,'Anesthesia','ddl',0,1,'AnesthesiaID','Anesthesia','_','','',2,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',140,'מספר ימי אשפוז',400,'IshpuzDays','EditTextBox',0,1,'','','n|0','','',1,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',150,'בידוד',400,'Insulation','ddl',0,1,'InsulationID','Insulation','_','','',2,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',160,'האם נדרש אשפוז מקדים',400,'IshpuzNeeded','ddl',0,1,'IshpuzNeededID','IshpuzNeeded','_','','',2,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',170,'האם נדרש טרום',400,'Trom','ddl',0,0,'TromID','Trom','_','','',2,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',180,'האם נדרש עוזר מנתח',400,'AssistantNeed','ddl',0,1,'AssistantNeedID','AssistantNeed','_','','',2,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',190,'כמות עוזרים',400,'AssistantNum','EditTextBox',0,1,'','','n|0','','',1,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',200,'האם נדרש טכנאי רנטגן',400,'Xray','ddl',0,1,'XrayID','Xray','_','','',2,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',210,'Frozen',400,'Frozen','ddl',0,1,'FrozenID','Frozen','_','','',2,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',220,'האם נדרש פתולוגיה',400,'Patology','ddl',0,0,'PatologyID','Patology','_','','',2,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',230,'',400,'','HTML',0,0,'</div>','','_','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',240,'',400,'','HTML',0,0,'<div style="background-color: rgba(239, 239, 239, 0.50);padding:10px"><h3><i class="fas fa-shekel-sign"></i> כספי</h3>','','_','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',250,'גורם מממן',400,'SponserName','ddl',0,1,'SponserID','SponserName','m','','',3,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',260,'שכר מנתח',400,'Wage','EditTextBox',0,1,'','','n|0','','',2,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',270,'עלות ניתוח (עבור פרטי)',400,'SurgerySum','EditTextBox',0,0,'','','n|0','','',2,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',280,'הערות',400,'SurgeryDetails','TextArea',0,0,'','','','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',290,'',400,'','HTML',0,0,'</div>','','_','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',300,'',400,'','HTML',0,0,'<div style="background-color: rgba(239, 239, 239, 0.50);padding:10px"><h3><i class=""fas fa-info-circle"></i> כללי</h3>','','_','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',310,'האם שומר שבת',400,'SabatKeep','ddl',0,0,'SabatKeepID','SabatKeep','_','','',2,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',320,'',400,'','HTML',0,0,'</div>','','_','','',12,'',NULL,'',0)
INSERT INTO [tblFormViewTemplate] ([Key1],[ordId],[FieldCap],[SizeCont],[FieldName],[ControlType],[bLock],[bNoBreakCell],[Param1],[Param2],[ValidatorStatus],[Param3],[ParentField],[InputSize],[AllowView],[AllowUpdate],[DEFAULT],[InputSize2])VALUES('pr_RetSurgeries',330,'סטאטוס',400,'StatusName','ddl',0,0,'StatusID','StatusName','_','','',2,'',NULL,'1',0)
GO
ALTER PROCEDURE pr_UpdateSurgery
@id INT = 0,
@ClientId INT=0,
@BlockId INT=0,
@block nvarchar(MAX)='',
@surgeryKod  nvarchar(100)='',
@Kod1 nvarchar(100)='',
@Kod2 nvarchar(100)='',
@Kod3 nvarchar(100)='',
@START nvarchar(MAX) = '',
@Duration INT = 0,
@CapitalList nvarchar(MAX) = '',
@pritimConsumableList nvarchar(MAX) = '',
@pritimimplantsList nvarchar(MAX) = '',
@AnesthesiaID INT=0,
@IshpuzDays INT=0,
@InsulationID INT=0,
@IshpuzNeededID INT=0,
@AssistantNum INT=0,
@AssistantNeedID INT=0,
@SponserID INT=0,
@TromID INT=0,
@XrayID INT=0,
@FrozenID INT=0,
@PatologyID INT=0,
@STATUS INT=0,
@Deductible money=0,
@Wage money=0,
@SurgerySum money=0,
@SurgeryDetails nvarchar(MAX)='',
@SabatKeepID INT=0,
@KodAvhana nvarchar(MAX)='',
@StatusID INT=0
AS
BEGIN
DECLARE @warning nvarchar(MAX)=''
IF (@SponserID=50 OR @SponserID=51) AND @SurgerySum=0
BEGIN
    SELECT 'err:לא הוגדר סכום לניתוח פרטי'
    RETURN
END
/*אימות נתונים*/
IF @id=0 AND @Blockid=0
BEGIN
    SELECT 'err:מנתח או בלוק לא מוגדרים'
    RETURN
END
IF @id>0
BEGIN
    SELECT @ClientId=clientid,@BlockId=BlockId FROM tblSurgeriesList WHERE id=@id
END
IF EXISTS (SELECT * FROM tblBlocks WHERE BlockStatus<>2 AND id=@BlockID)
BEGIN
    SELECT 'err:אין אפשרות להוסיף ניתוח לבלוק לא מאושר'
    RETURN
END
DECLARE @date1 datetime=NULL
--אתחול תאריך על ידי הבלוק
SELECT @Date1=Date1 FROM tblBlocks WHERE id=@BlockID
/*בדיקת חריגות בשעות*/
IF (
    @Date1+ @Start<(SELECT Date1+StartTime FROM tblBlocks WHERE id=@BlockID)
    OR DATEADD(MINUTE,@Duration, @Date1+ @START)>(SELECT Date1+EndTime FROM tblBlocks WHERE id=@BlockID)
    )
BEGIN
    SELECT 'err:הזמן שהוגדר חורג ממסגרת הבלוק'
    RETURN
END
/*בדיקת התנגשות עם ניתוח אחר או פעילות מנתח במקום אחר*/
IF EXISTS (SELECT  *  FROM tblSurgeriesList sl
INNER JOIN tblBlocks b ON b.id=sl.BlockID
WHERE sl.blockid=@BlockID  AND sl.id<>@id
AND b.Date1=@Date1 AND
    (
        @Date1+ @START BETWEEN b.Date1+[START] AND DATEADD(MINUTE,Duration, b.Date1+ sl.[START])
        OR
        DATEADD(MINUTE,@Duration, @Date1+ @START) BETWEEN b.Date1+[START] AND  DATEADD(MINUTE,Duration, Date1+ [START] )
)
)
BEGIN
    SELECT 'err: החדר הוקצה בשעות אלו עבור ניתוח אחר'
    RETURN
END
IF (@id = 0)
BEGIN
INSERT INTO tblSurgeriesList(
ClientId,
BlockId,
Kod1,
Kod2,
Kod3,
[START],
Duration,
AnesthesiaID,
IshpuzDays,
InsulationID,
IshpuzNeeded,
AssistantNum,
AssistantNeed,
SponserID,
Trom,
Xray,
Frozen,
Patology,
Deductible,
Wage,
SurgerySum,
SurgeryDetails,
bSabatKeep,
KodAvhana,
StatusID
)
SELECT
@ClientId,
@BlockId,
@Kod1,
@Kod2,
@Kod3,
@START,
@Duration,
@AnesthesiaID,
@IshpuzDays,
@InsulationID,
@IshpuzNeededID,
@AssistantNum,
@AssistantNeedID,
@SponserID,
@TromID,
@XrayID,
@FrozenID,
@PatologyID,
@Deductible,
@Wage,
@SurgerySum,
@SurgeryDetails,
@SabatKeepID,
@KodAvhana,
@StatusID
SET @id=scope_identity()
    /*עדכון ציוד קפיטלי במצב של חדש*/
    INSERT INTO tblSurgeriesCapital(surid,CapitalKod)
    SELECT @id,VALUE FROM UTILfn_Split(@CapitalList,',')  WHERE Value<>''
    /*ציוד מתכלה*/
    INSERT INTO tblSurgeriesEquipment(surid,nType,EquipKod)
    SELECT @id,1,VALUE FROM UTILfn_Split(@pritimConsumableList,',')  WHERE Value<>''
    /*משתלים*/
    INSERT INTO tblSurgeriesEquipment(surid,nType,EquipKod)
    SELECT @id,2,VALUE FROM UTILfn_Split(@pritimimplantsList,',')  WHERE Value<>''
    IF @warning<>''
        SELECT 'warning:' + @warning
    ELSE
        SELECT @id

END
ELSE
BEGIN
BEGIN TRANSACTION
BEGIN TRY
UPDATE tblSurgeriesList SET
Kod1=@Kod1,
Kod2=@Kod2,
Kod3=@Kod3,
START=@START,
Duration=@Duration,
AnesthesiaID=@AnesthesiaID,
IshpuzDays=@IshpuzDays,
InsulationID=@InsulationID,
IshpuzNeeded=@IshpuzNeededID,
AssistantNum= @AssistantNum,
SponserID=@SponserID,
Trom=@TromID,
Xray=@XrayID,
Frozen=@FrozenID,
Patology=@PatologyID,
Deductible=@Deductible,
Wage=@Wage,
SurgerySum=@SurgerySum,
SurgeryDetails=@SurgeryDetails,
bSabatKeep=@SabatKeepID,
KodAvhana=@KodAvhana,
StatusID=@StatusID
WHERE id=@id
/*עדכון ציוד קפיטלי במצב של חדש*/
DELETE FROM tblSurgeriesCapital WHERE surID=@id
INSERT INTO tblSurgeriesCapital(surid,CapitalKod)
SELECT @id,VALUE FROM UTILfn_Split(@CapitalList,',')  WHERE Value<>''
/*ציוד מתכלה*/
DELETE FROM tblSurgeriesEquipment WHERE surID=@id AND nType=1
INSERT INTO tblSurgeriesEquipment(surid,nType,EquipKod)
SELECT @id,1,VALUE FROM UTILfn_Split(@pritimConsumableList,',')  WHERE Value<>''
/*משתלים*/
DELETE FROM tblSurgeriesEquipment WHERE surID=@id AND nType=2
INSERT INTO tblSurgeriesEquipment(surid,nType,EquipKod)
SELECT @id,2,VALUE FROM UTILfn_Split(@pritimimplantsList,',')  WHERE Value<>''
SELECT @id
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT 'err:'+ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
END
END
GO
ALTER PROCEDURE pr_RetSurgeries
@ID INT=0,
@LANGUAGE nvarchar(MAX)='',
@ProfileID INT=0,
@blockID INT=0,
@block INT=0,
@Clientid INT=0,
@surgeryKod nvarchar(200)='',
@UseridCreate INT=0
AS
BEGIN
IF @id>0
    SELECT @blockID=BlockID,@Clientid=Clientid FROM tblSurgeriesList WHERE id=@ID
IF OBJECT_ID('tempdb..#tempTemaplate') IS NOT NULL  DROP TABLE #tempTemplate
SELECT * INTO #tempTemplate FROM tblFormViewTemplate WHERE Key1='pr_RetSurgeries' ORDER BY ordId
IF (@blockID>0)
    UPDATE #tempTemplate SET param1=isnull(
    (SELECT '<table class="mt-4"  width=100%><tr><th class="p-1">בלוק</th>
    <th class="p-1">תחום</th><th class="p-1">זמן</th><th class="p-1">מנתח</th><th class="p-1">תאריך</th></tr><tr>'
     + '<td class="p-1">' + CONVERT(nvarchar(MAX), @blockID) + '</td>'
      + '<td class="p-1">' + pm.Item + '</td>'
     + '<td class="p-1">' + b.starttime+ '-' + b.EndTime + '</td>'
     + '<td class="p-1">' + tblClients.NameView + '</td>'
     + '<td class="p-1">' + format(b.Date1, 'dd/MM/yyyy') + '</td></tr>'
     + '</table>'
     FROM tblBlocks b
    INNER JOIN tblClients ON tblClients.id=b.DoctorId
    INNER JOIN tbldefparitmiun pm ON pm.id=b.SubjectID
    WHERE b.id=@blockID) + '</hr>','')
    WHERE FieldName='BlockName'
IF (@Clientid>0)
        UPDATE #tempTemplate SET param1=isnull(
        (SELECT
         '<table id="tbClient" class="mt-4 p-1"  width=100%><tr><th class="p-1">מטופל</th><th class="p-1">שם האב</th><th class="p-1">מין</th><th class="p-1">ת.ז</th><th class="p-1">ת.לידה</th></tr><tr class="pl-1 pr-1">'
        + '<td class="p-1">' + c.nameview + '</td>'
        + '<td class="p-1">' + cd.FatherName + '</td>'
        + '<td class="p-1">' + (CASE  cd.genderid WHEN 1 THEN 'ז' WHEN 2 THEN 'נ' ELSE '' END) + '</td>'
        + '<td class="p-1">' + c.idCard + '</td>'
        + '<td class="p-1">' + CONVERT(nvarchar(10),cd.BirthDay,103) + '</td>'
        + '</tr></table>'
        FROM tblClients c
        INNER JOIN tblClientDetails cd ON cd.mainid=c.id
        WHERE @Clientid=c.id),'')
        WHERE FieldName='ClientHeader'
IF @id=0 UPDATE #tempTemplate SET [DEFAULT]=@surgeryKod WHERE FieldName='Kod1'
/*בחירת ערכים אחרי אתחול ראשוני והשמת פרטים*/
SELECT * FROM #tempTemplate
SELECT
CONVERT(DATE,b.Date1) AS Date1,
sl.[START],
sl.Duration,
sl.SurgerySum
,Stuff(
(
SELECT ','+CAST(cl.CapitalKod AS nvarchar)
FROM tblSurgeriesCapital cl
WHERE cl.surID=sl.id
FOR XML PATH('')
),1,1,'')  AS CapitalList
,Stuff(
(
SELECT ','+CAST(eq.EquipKod AS nvarchar)
FROM tblSurgeriesEquipment eq
WHERE eq.surID=sl.id AND eq.ntype=1
FOR XML PATH('')
),1,1,'')  AS pritimConsumableList
,Stuff(
(
SELECT ','+CAST(eq.EquipKod AS nvarchar)
FROM tblSurgeriesEquipment eq
WHERE eq.surID=sl.id AND eq.ntype=2
FOR XML PATH('')
),1,1,'')  AS pritimimplantsList
,pr.ParitName AS SurgeriesName,
pr.Kod AS SurgeriesKod,
sl.AnesthesiaID, tblDefAnesthesia.item AS Anesthesia,
sl.InsulationID, tblDefInsolation.item AS Insulation
,sl.KodAvhana
,(CASE WHEN IshpuzNeeded=1 THEN 'כן' ELSE 'לא' END) AS IshpuzNeeded,IshpuzNeeded AS IshpuzNeededID
,(CASE WHEN Patology=1 THEN 'כן' ELSE 'לא' END) AS Patology,Patology AS PatologyID
,(CASE WHEN Trom=1 THEN 'כן' ELSE 'לא' END) AS Trom,Trom AS TromID
,(CASE WHEN Xray=1 THEN 'כן' ELSE 'לא' END) AS Xray,Xray AS XrayID
,(CASE Frozen WHEN 0 THEN 'לא' WHEN 1 THEN 'כן' WHEN 2 THEN 'אולי' END) AS Frozen,Frozen AS FrozenID
,(CASE WHEN AssistantNeed=1 THEN 'כן' ELSE 'לא' END) AS AssistantNeed,AssistantNeed AS AssistantNeedID
,(CASE WHEN bSabatKeep=1 THEN 'כן' ELSE 'לא' END) AS SabatKeep,bSabatKeep AS SabatKeepID
,IshpuzDays
,Deductible
,Wage
,'' AS AssistantNum
,sl.SponserID,sp.NameView AS SponserName
,StatusID, tblDefSurgeryStatus.item AS StatusName,
sl.Kod1,sl.Kod2,sl.Kod3,pr.ParitName AS KodName1,pr2.ParitName AS KodName2,pr3.ParitName AS KodName3
FROM tblSurgeriesList sl
INNER JOIN tblPritim pr ON pr.Kod=sl.Kod1
LEFT JOIN tblPritim pr2 ON pr2.Kod=sl.Kod2
LEFT JOIN tblPritim pr3 ON pr3.Kod=sl.Kod3
LEFT JOIN tblDefAnesthesia ON tblDefAnesthesia.id=sl.AnesthesiaID
LEFT JOIN tblDefInsolation ON tblDefInsolation.id=sl.InsulationID
LEFT JOIN tblDefSurgeryStatus ON tblDefSurgeryStatus.id=sl.StatusID
INNER JOIN tblBlocks b ON b.id=sl.blockid
INNER JOIN tblClients sp ON sl.SponserID=sp.id
WHERE sl.id=@id
SELECT kod AS Kod1,(CASE WHEN isnull(pmd.KodIcd,'')<>'' THEN pmd.KodIcd+ '-' ELSE '' END) + paritname AS KodName1  FROM tblPritim pr
INNER JOIN tblBlocks b ON b.subjectid=pr.SubjectID
LEFT JOIN tblPritimMoreDetails pmd ON pmd.ParitKod=pr.Kod
WHERE b.id=@blockID
SELECT kod AS Kod2,(CASE WHEN isnull(pmd.KodIcd,'')<>'' THEN pmd.KodIcd+ '-' ELSE '' END) + paritname AS KodName2  FROM tblPritim pr
INNER JOIN tblBlocks b ON b.subjectid=pr.SubjectID
LEFT JOIN tblPritimMoreDetails pmd ON pmd.ParitKod=pr.Kod
WHERE b.id=@blockID
SELECT kod AS Kod3,(CASE WHEN isnull(pmd.KodIcd,'')<>'' THEN pmd.KodIcd+ '-' ELSE '' END) + paritname AS KodName3  FROM tblPritim pr
INNER JOIN tblBlocks b ON b.subjectid=pr.SubjectID
LEFT JOIN tblPritimMoreDetails pmd ON pmd.ParitKod=pr.Kod
WHERE b.id=@blockID
--ציוד קפיטלי
SELECT Capitalkod AS CapitalList, item AS CapitalListName FROM tblCapitalEquipment
--ציוד מתכלה
SELECT kod AS pritimConsumableList,ParitName AS pritimConsumableListtxt FROM tblpritim WHERE SubjectID=40
--משתלים
SELECT kod AS pritimimplantsList,ParitName AS pritimimplantsListtxt FROM tblpritim WHERE SubjectID=41
--סוגי הרדמה
SELECT id AS AnesthesiaID, item AS AnesthesiaName FROM tblDefAnesthesia
--סוגי בידוד
SELECT id AS InsulationID, item AS InsulationName FROM tblDefInsolation
--אשפוז מקדים
SELECT * FROM (VALUES (0,'לא'), (1,'כן')) t1 (IshpuzNeededID, IshpuzNeeded)
--טרום
SELECT * FROM (VALUES (0,'לא'), (1,'כן')) t1 (TromID, Trom)
--עוזר
SELECT * FROM (VALUES (0,'לא'), (1,'כן')) t1 (AssistantNeedID, AssistantNeed)
--Xray
SELECT * FROM (VALUES (0,'לא'), (1,'כן')) t1 (XrayID, Xray)
--Frozen
SELECT * FROM (VALUES (0,'לא'), (1,'כן'), (1,'אולי')) t1 (FrozenID, Frozen)
--פתלוגיה
SELECT * FROM (VALUES (0,'לא'), (1,'כן')) t1 (PatologyID, Patology)
SELECT s.id AS SponserID,s.NameView AS SponserName FROM tblClients  s WHERE s.nType=10
--שומר שבת
SELECT * FROM (VALUES (0,'לא'), (1,'כן')) t1 (SabatKeepID, SabatKeep)
SELECT id AS [StatusID], item AS StatusName FROM tblDefSurgeryStatus
END