USE Cadelio

GO

ALTER VIEW VPatient AS 
SELECT p.Pati_ID, p.Pati_FirstName, p.Pati_BirthName, p.Pati_LastName, p.Pati_BirthDate, p.Pati_Gender, p.Pati_Address, p.Pati_PostCode, p.Pati_CityID, p.Pati_PhoneNumber, p.Pati_MobileNumber, p.Pati_Sport1, p.Pati_Sport2, p.Pati_Profession, p.Pati_Title, p.Pati_FirstNameIfDiff, 
             p.Pati_LastNameIfDiff, p.Pati_SecurityNumber, p.Pati_MailAddress, p.Pati_PracticionerID, p.Pati_Remark, p.Pati_CreatedBy, p.Pati_CreatedDate, p.Pati_UpdatedBy, p.Pati_UpdatedDate, p.Pati_Deleted, p.Pati_Synchronised, p.Pati_Podosat6ID, p.Pati_AgencyID, p.Pati_Weight, 
             p.Pati_FootSize, p.Pati_Height, p.Pati_HeadQuarterID, p.Pati_RedFlags, p.Pati_Contrindications, p.Pati_Precautions, p.Pati_IMC, p.Pati_Alimentation, p.Pati_FamilialCadre, p.Pati_PhysicalActivitys, p.Pati_Hobbies, p.Pati_ProfessionelCadre, p.Pati_Sleeping, p.Pati_LifeLine, 
             p.Pati_FullName, p.Pati_RemindNote, p.Pati_OfficeNumber, p.Pati_Memo, p.Pati_AnamnesisMemo, p.Pati_CountryCode, p.Pati_SentToSigmaRS, p.Pati_SentToFreeStep, p.Pati_FreeStepID, p.Pati_Status, p.Pati_Reason, c.City_Name, ccCountry.Capt_FR AS Country, 
             ccSport1.Capt_FR AS Sport1, ccSport2.Capt_FR AS Sport2, ccProfession.Capt_FR AS Profession, ccGender.Capt_FR AS Gender, ccTitle.Capt_FR AS Title, prac.Prac_FullName AS Practitioner, ccStatus.Capt_FR AS PatientStatus, ccReason.Capt_FR AS PatientReason, 
             p.Pati_Various1, p.Pati_Various2, p.Pati_Various4, p.Pati_Various3, p.Pati_BirthPlace, p.Pati_UsedName
FROM   dbo.Patient AS p LEFT OUTER JOIN
             dbo.City AS c ON p.Pati_CityID = c.City_ID LEFT OUTER JOIN
             dbo.CustomCaption AS ccCountry ON p.Pati_CountryCode = ccCountry.Capt_Code AND ccCountry.Capt_Family = 'country' LEFT OUTER JOIN
             dbo.CustomCaption AS ccSport1 ON p.Pati_Sport1 = ccSport1.Capt_Code AND ccSport1.Capt_Family = 'Pati_Sport' LEFT OUTER JOIN
             dbo.CustomCaption AS ccSport2 ON p.Pati_Sport2 = ccSport2.Capt_Code AND ccSport2.Capt_Family = 'Pati_Sport' LEFT OUTER JOIN
             dbo.CustomCaption AS ccProfession ON p.Pati_Profession = ccProfession.Capt_Code AND ccProfession.Capt_Family = 'Pati_Profession' LEFT OUTER JOIN
             dbo.CustomCaption AS ccGender ON p.Pati_Gender = ccGender.Capt_Code AND ccGender.Capt_Family = 'Pati_Gender' LEFT OUTER JOIN
             dbo.CustomCaption AS ccTitle ON p.Pati_Title = ccTitle.Capt_Code AND ccTitle.Capt_Family = 'Pati_Title' LEFT OUTER JOIN
             dbo.Practitioner AS prac ON p.Pati_PracticionerID = prac.Prac_ID LEFT OUTER JOIN
             dbo.CustomCaption AS ccStatus ON p.Pati_Status = ccStatus.Capt_Code AND ccStatus.Capt_Family = 'Pati_Status' LEFT OUTER JOIN
             dbo.CustomCaption AS ccReason ON p.Pati_Reason = ccReason.Capt_Code AND ccReason.Capt_Family = 'Pati_Reason'
WHERE (p.Pati_Deleted IS NULL)

GO


IF (SELECT COUNT(*) FROM sys.Columns WHERE Name = 'Exbo_EpineiliaqFace') = 0
BEGIN
ALTER TABLE EX_Body ADD Exbo_EpineiliaqFace VARCHAR(2), Exbo_EpineiliaqFace_Comment VARCHAR(250)
END 

GO

IF (SELECT COUNT(*) FROM CustomCaption WHERE Capt_Code = 'lbl_EpineIliaqFace' AND Capt_Family = 'labels') = 0
BEGIN
INSERT INTO CustomCaption (Capt_Family, Capt_Code, Capt_FR, Capt_EN) VALUES ('labels', 'lbl_EpineIliaqFace', 'Épines iliaques antéro supérieur', 'Epine iliaque antero supérieur') 
END 

GO

IF (SELECT COUNT(*) FROM CustomCaption WHERE Capt_Code = 'Exbo_EpineiliaqFace' AND Capt_Family = 'Report-entity-tab-group-fields') = 0
BEGIN
INSERT INTO CustomCaption (Capt_Family, Capt_Code, Capt_FR, Capt_EN) VALUES ('Report-entity-tab-group-fields', 'Exbo_EpineiliaqFace', 'Épines iliaques antéro supérieur', 'Epine iliaque antéro superieur') 
INSERT INTO CustomCaption (Capt_Family, Capt_Code, Capt_FR, Capt_EN) VALUES ('Report-entity-tab-group-fields', 'Exbo_EpineiliaqFace-compare', 'Épines iliaques antéro supérieur', 'Epine iliaque antéro supérieur') 
UPDATE CadelioReportEntityTabGroupFields SET Fl_Code = 'Exbo_EpineIliaqFace' WHERE Fl_ID = '98477F60-0615-4B81-921C-9289B29A95D9'
UPDATE CadelioReportEntityTabGroupFields SET Fl_Code = 'Exbo_EpineIliaqFace-compare' WHERE Fl_ID = '76C70335-FD94-488B-B287-B4446EAD153D'
END 

GO

--Genuvarrum plus
IF (SELECT COUNT(*) FROM CustomCaption WHERE Capt_Code = 'Exbo_Genuvarrum_Plus' AND Capt_Family = 'Report-entity-tab-group-fields') = 0
BEGIN

INSERT INTO CadelioReportEntityTabGroupFields (Fl_ID, Fl_GroupID, Fl_Code) VALUES (NEWID(), '3916E9BA-71C4-475F-A076-1861689E2F63', 'Exbo_Genuvarrum_Plus-compare')
INSERT INTO CadelioReportEntityTabGroupFields (Fl_ID, Fl_GroupID, Fl_Code) VALUES (NEWID(), '7274780D-B27F-45EE-9BCA-101F96E75A53', 'Exbo_Genuvarrum_Plus')
INSERT INTO CadelioReportEntityTabGroupFields (Fl_ID, Fl_GroupID, Fl_Code) VALUES (NEWID(), '4772D5AB-46FA-4E22-8FD6-3BF71F00BD12', 'Exbo_Genuvarrum_Plus-compare')
INSERT INTO CadelioReportEntityTabGroupFields (Fl_ID, Fl_GroupID, Fl_Code) VALUES (NEWID(), 'B54C0D43-C7A8-4915-AA9A-9D07A5A36AF6', 'Exbo_Genuvarrum_Plus')

INSERT INTO CustomCaption (Capt_Family, Capt_Code, Capt_FR, Capt_EN) VALUES ('Report-entity-tab-group-fields', 'Exbo_Genuvarrum_Plus', 'Génuvarum plus', 'Genuvarum plus') 
INSERT INTO CustomCaption (Capt_Family, Capt_Code, Capt_FR, Capt_EN) VALUES ('Report-entity-tab-group-fields', 'Exbo_Genuvarrum_Plus-compare', 'Génuvarum plus', 'Genuvarum plus') 

END 



--Genuvalgum plus
IF (SELECT COUNT(*) FROM CustomCaption WHERE Capt_Code = 'Exbo_Genuvalgum_Plus' AND Capt_Family = 'Report-entity-tab-group-fields') = 0
BEGIN

INSERT INTO CadelioReportEntityTabGroupFields (Fl_ID, Fl_GroupID, Fl_Code) VALUES (NEWID(), '4772D5AB-46FA-4E22-8FD6-3BF71F00BD12', 'Exbo_Genuvalgum_Plus-compare')
INSERT INTO CadelioReportEntityTabGroupFields (Fl_ID, Fl_GroupID, Fl_Code) VALUES (NEWID(), '7274780D-B27F-45EE-9BCA-101F96E75A53', 'Exbo_Genuvalgum_Plus')
INSERT INTO CadelioReportEntityTabGroupFields (Fl_ID, Fl_GroupID, Fl_Code) VALUES (NEWID(), '3916E9BA-71C4-475F-A076-1861689E2F63', 'Exbo_Genuvalgum_Plus-compare')
INSERT INTO CadelioReportEntityTabGroupFields (Fl_ID, Fl_GroupID, Fl_Code) VALUES (NEWID(), 'B54C0D43-C7A8-4915-AA9A-9D07A5A36AF6', 'Exbo_Genuvalgum_Plus')

INSERT INTO CustomCaption (Capt_Family, Capt_Code, Capt_FR, Capt_EN) VALUES ('Report-entity-tab-group-fields', 'Exbo_Genuvalgum_Plus', 'Génuvalgum plus', 'Genuvalgum plus') 
INSERT INTO CustomCaption (Capt_Family, Capt_Code, Capt_FR, Capt_EN) VALUES ('Report-entity-tab-group-fields', 'Exbo_Genuvalgum_Plus-compare', 'Génuvalgum plus', 'genuvalgum plus') 

END