CREATE PROCEDURE [dbo].[PISLoadPrescription] @Id UNIQUEIDENTIFIER
AS
BEGIN
    DECLARE @MedicationTerminology TABLE
    (
        [Code] [VARCHAR](20) NOT NULL,
        [Alias] [VARCHAR](10) NULL,
        [Term] [NVARCHAR](MAX) NULL,
        [PersianMeaning] [NVARCHAR](MAX) NULL,
        [TerminologyID] [NVARCHAR](150) NULL,
        [Type] [TINYINT] NOT NULL
    );
    INSERT INTO @MedicationTerminology
    (
        Code,
        Alias,
        Term,
        PersianMeaning,
        TerminologyID,
        [Type]
    )
    SELECT Code,
           Alias,
           Term,
           PersianMeaning,
           TerminologyID,
           [Type]
    FROM dbo.MedicationTerminology
    WHERE [Type] IN ( 3, 4, 6, 8, 11 );

    SELECT P.*,
           dbo.MiladiToShamsi(P.IssueDate) ShamsiIssueDate,
           dbo.MiladiToShamsi(P.InsuranceExpirationDate) ShamsiInsuranceExpirationDate,
           dbo.MiladiToShamsi(P.AdmissionDate) ShamsiAdmissionDate,
           dbo.MiladiToShamsi(P.PrescriptionExpiryDate) ShamsiPrescriptionExpiryDate,
           dbo.MiladiToShamsi(P.BirthDate) ShamsiBirthDate,
           GenderName = CASE
                            WHEN P.GenderCode = 1 THEN
                                N'مرد'
                            WHEN P.GenderCode = 2 THEN
                                N'زن'
                            WHEN P.GenderCode = 3 THEN
                                N'دوجنسي/ نامشخص'
                        END,
           MTEL.PersianMeaning EducationLevelName,
           MTJ.PersianMeaning JobName,
           CONVERT(VARCHAR(8), AdmissionDate, 108) AdmissionTime,
           AdmissionTypeName = CASE
                                   WHEN P.AdmissionTypeCode = '1' THEN
                                       N'سرپايي'
                                   WHEN P.AdmissionTypeCode = '2' THEN
                                       N'بستري'
                                   WHEN P.AdmissionTypeCode = '3' THEN
                                       N'انتقالي'
                                   WHEN P.AdmissionTypeCode = '4' THEN
                                       N'اورژانس'
                               END,
           MaritalStatusName = CASE
                                   WHEN P.MaritalStatusCode = '1' THEN
                                       N'طلاق گرفته'
                                   WHEN P.MaritalStatusCode = '2' THEN
                                       N'متأهل'
                                   WHEN P.MaritalStatusCode = '3' THEN
                                       N'مجرد'
                                   WHEN P.MaritalStatusCode = '4' THEN
                                       N'همسر فوت شده'
                               END,
           MTS.PersianMeaning SpecialtyName,
           RTrim(LTRIM(D.[Name] + ' ' + D.Family)) DrugMedicalFullName,
           G.Grade_Name DrugSpecialtyName,
           D.Nezam_No DrugMedicalId,
           D.Grade_Code DrugSpecialtyCode,
           DrugSpecialtyType = CASE
                                   WHEN G.Status = 1 THEN
                                       N'عمومي'
                                   WHEN G.Status = 2 THEN
                                       N'متخصص'
                                   WHEN G.Status = 3 THEN
                                       N'فوق تخصص'
                                   WHEN G.Status = 4 THEN
                                       N'ماما'
                                   WHEN G.Status = 5 THEN
                                       N'دندانپزشک'
                                   WHEN G.Status = 6 THEN
                                       N'اتباع'
                                   WHEN G.Status = 7 THEN
                                       N'فلوشيپ'
                               END,
           MTS.Alias MappedSpecialtyCode
    FROM PISReceivedPrescription P
        LEFT JOIN @MedicationTerminology MTEL
            ON MTEL.Code = P.EducationLevelCode
               AND MTEL.Type = 6
        LEFT JOIN @MedicationTerminology MTJ
            ON MTJ.Code = P.JobCode
               AND MTJ.[Type] = 8
        LEFT JOIN @MedicationTerminology MTS
            ON MTS.Code = P.SpecialtyCode
               AND MTS.Type = 11
        LEFT JOIN dbo.Doctor D
            ON D.Nezam_No = P.MedicalId
        LEFT JOIN dbo.Grade G
            ON G.Grade_Code = D.Grade_Code
    WHERE Id = @Id;

    SELECT Ins.Sazman_Code InsuranceCode,
           Ins.Sa_Name InsuranceName
    FROM dbo.PISReceivedPrescription p
        LEFT JOIN dbo.Sahmiyeh Ins
            ON (
                   Ins.PISCode = p.InsurerCode
                   AND Ins.PISCodeType = 3
               )
               OR
               (
                   Ins.PISCode = p.InsuranceBoxCode
                   AND Ins.PISCodeType = 4
               )
    WHERE Id = @Id
    ORDER BY Ins.Sa_Name;

END;