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 );

    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,
           MTIB.Alias DrugInsuranceCode,
           Insurer.Sa_Name DrugInsuranceName
    FROM PISReceivedPrescription P
        LEFT JOIN @MedicationTerminology MTIN
            ON MTIN.Code = P.InsurerCode
               AND MTIN.Type = 3
        LEFT JOIN @MedicationTerminology MTIB
            ON MTIB.Code = P.InsuranceBoxCode
               AND MTIB.Type = 4
        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 dbo.Sahmiyeh Insurer
            ON Insurer.Sazman_Code = MTIB.Alias
    WHERE Id = @Id;
END;
