CREATE PROC [dbo].[sp_HIX_GetPrescriptionByID]
    @PrescriptionId NUMERIC
AS
    SELECT  Id_Havaleh [PrescriptionId] ,
            Tarikh_Paziresh [AdmissionDate] ,
            Nezam_No [AttendingDoctorInfoMedicalCouncilCode] ,
            Grade.Hix_Code [AttendingDoctorInfoSpeciality] ,
            DrugH.Note_Code [InsuranceNumber] ,
            Tarikh_Etebar [InsuranceBookletExpirationDate] ,
            Page_No [InsuranceBookletPageNumber] ,
            Resive_Name [PatientInfoFullName] ,
            Meli_Card_No [PatientInfoPatientId] ,
            [PatientInfoBirthDate] = CASE WHEN LEN(CAST(Age AS VARCHAR(4))) = 2
                                          THEN dbo.ShamsiToMiladi('13'
                                                              + CAST(Notebimeh.Age AS VARCHAR(2))
                                                              + '/01/01')
                                          WHEN LEN(CAST(Age AS VARCHAR(4))) = 4
                                          THEN dbo.ShamsiToMiladi(CAST(Notebimeh.Age AS VARCHAR(4))
                                                              + '/01/01')
                                          ELSE GETDATE()
                                     END ,
            Notebimeh.Mobile [PatientInfoMobielNumber] ,
            patientInfoGenderGender = CASE WHEN dbo.Notebimeh.TypeSex = '0'
                                           THEN 1
                                           WHEN dbo.Notebimeh.TypeSex = '1'
                                           THEN 0
                                           ELSE 2
                                      END ,
            dbo.Sahmiyeh.Drug_Code [PharmacyInfoPharmacyCode] ,
            DrugH.Nezam_No [tMedicalCouncilCode] ,
            DrugH.Nezam_No [PharmacyInfoPharmacistFounderInfo] ,
            DrugH.Nezam_No [PharmacyInfoDispenserInfo] ,
            DrugH.TotPriceDaryafti [PrescriptionCostInfoTotalCost] ,
            DrugH.TotPriceFaniBimar + DrugH.TotPriceFaniSazman [PrescriptionCostInfoTechnicalCost] ,
            DrugH.TotPriceKahandeh [PrescriptionCostInfoDiscount] ,
            DrugH.TotPriceBimar [PrescriptionCostInfoPayableByPatient] ,
            DrugH.TotPriceFaniBimar [PrescriptionCostInfoAutomation] ,
            DrugH.Tarikh_Noskheh [DispensedPrescriptionPrescriptionDate] ,
            dbo.Sahmiyeh.Hix_Code [InsuranceOrganizationCode]
    FROM    dbo.DrugH
            INNER JOIN dbo.Notebimeh ON Notebimeh.Note_Code = DrugH.Note_Code
                                        AND Notebimeh.Sazman_Code = DrugH.Sazman_Code
            INNER JOIN dbo.Sahmiyeh ON Sahmiyeh.Sazman_Code = DrugH.Sazman_Code
            INNER JOIN Grade ON Grade.Grade_Code = DrugH.Grade_Code
    WHERE   Id_Havaleh = @PrescriptionId 

    SELECT  DrugHavaleh.Id_Havaleh [PrescriptionId] ,
            KalaId.Meli_Code [AcceptedMedicationItemInfoGenericCode] ,
            K_Qty1 [AcceptedMedicationItemInfoMedicationCount] ,
            [CheckedOutMedicationItemInfoNationalDrugCode] = CASE
                                                              WHEN KalaId.Barcode IS NULL
                                                              OR dbo.KalaId.Barcode = ''
                                                              THEN dbo.KalaId.Meli_Code
                                                              ELSE KalaId.Meli_Code
                                                             END ,
            K_Qty1 [AcceptedMedicationItemInfoDeliveredMedicationCount] ,
            dbo.DrugH.Tarikh_Paziresh [CheckedOutMedicationItemInfoMedicationDeliverDate] ,
            Price_Forosh * K_Qty1 [CostsTotalCost] ,
            [CostsInsuranceContribution] = CASE WHEN DrugHavaleh.BimarPercent > 0
                                                THEN ROUND(Price_Forosh
                                                           * K_Qty1 * ( 100
                                                              - DrugHavaleh.BimarPercent )
                                                           / 100, 0)
                                                ELSE ROUND(Price_Forosh
                                                           * K_Qty1 * ( 100
                                                              - dbo.Sahmiyeh.Bimar_Percent )
                                                           / 100, 0)
                                           END ,
            [CostsPatientContribution] = CASE WHEN DrugHavaleh.BimarPercent > 0
                                              THEN ROUND(Price_Forosh * K_Qty1
                                                         * DrugHavaleh.BimarPercent
                                                         / 100, 0)
                                              ELSE ROUND(Price_Forosh * K_Qty1
                                                         * dbo.Sahmiyeh.Bimar_Percent
                                                         / 100, 0)
                                         END ,
            dbo.DrugHavaleh.Tot_Differ [CostsDifferenceOfInsuranceCoverAndPrice] ,
            [CostsPayableByPatient] = CASE WHEN DrugHavaleh.BimarPercent > 0
                                           THEN ROUND(Price_Forosh * K_Qty1
                                                      * DrugHavaleh.BimarPercent
                                                      / 100, 0)
                                           ELSE ROUND(Price_Forosh * K_Qty1
                                                      * dbo.Sahmiyeh.Bimar_Percent
                                                      / 100, 0)
                                      END ,
            N'سم بار در رنز' [FrequencyUsageContent]
    FROM    dbo.DrugHavaleh
            INNER JOIN dbo.DrugH ON DrugH.Id_Havaleh = DrugHavaleh.Id_Havaleh
            INNER JOIN dbo.KalaId ON KalaId.K_Code = DrugHavaleh.K_Code
            INNER JOIN dbo.Sahmiyeh ON Sahmiyeh.Sazman_Code = DrugH.Sazman_Code
    WHERE   DrugHavaleh.Id_Havaleh = @PrescriptionId AND dbo.DrugHavaleh.[Status] in ( '1' , '2' , '4' , '5' , '6' , '8' , '9')
