CREATE PROC [dbo].[GetIRIPrescription]
    @PrescriptionId BIGINT,
    @CalcStatus TINYINT
AS
DECLARE @OrgPercent FLOAT,
        @FactorId BIGINT,
        @ContractId BIGINT,
        @FranchisePercent FLOAT,
        @ContractSettingId INT,
        @FirstCoveredCon TINYINT,
        @NonInsuredCon TINYINT,
        @PatientAmountCon TINYINT,
        @FranchiseCon TINYINT,
        @SendItemsCon TINYINT,
        @TransactionId INT,
        @IranTechnicalFees MONEY,
        @PatientTechnicalFees MONEY,
        @PatientTechFees MONEY,
        @SimilarDrugsDifferAmount BIGINT;
------------------------------------------------------------------------------------------
SELECT @FactorId = PrescriptionNo,
       @ContractId = ContractId,
       @ContractSettingId = ContractSettingId
FROM dbo.IRI_Prescriptions
WHERE Id = @PrescriptionId;
------------------------------------------------------------------------------------------
SELECT @FranchisePercent = CAST(ISNULL(FranchisePercent, 0) AS TINYINT)
FROM dbo.IRI_InsuredContracts
WHERE Id = @ContractId;
------------------------------------------------------------------------------------------
SELECT @FirstCoveredCon = FirstCoveredCon,
       @NonInsuredCon = NonInsuredCon,
       @PatientAmountCon = PatientCon,
       @FranchiseCon = FranchiseCon,
       @SendItemsCon = SendItemsCon,
       @TransactionId = TransactionId,
       @IranTechnicalFees = IranTechnicalFees,
       @PatientTechnicalFees = PatientTechnicalFees
FROM dbo.IRI_ContractSettings
WHERE Id = @ContractSettingId;
------------------------------------------------------------------------------------------
IF @CalcStatus = 0
    UPDATE FD
    SET IsIRIObligation = 0,
        FD.IRIStatus = 1
    FROM dbo.TmpDrugHavaleh FD
    WHERE FD.Id_Havaleh = @FactorId;
-------------------------------------------------------------------------------------------
SELECT @PatientTechFees = CASE
                              WHEN FD.Status NOT IN ( 0, 1, 2, 3 )
                                   AND (@FranchiseCon = 4) THEN
                                  @PatientTechnicalFees
                              ELSE
                                  0
                          END
FROM dbo.TmpDrugHavaleh FD
WHERE Id_Havaleh = @FactorId
      AND FD.Status NOT IN ( 0, 1, 2, 3 )
      AND (@FranchiseCon = 4)
GROUP BY FD.Status;

SET @PatientTechFees = ISNULL(@PatientTechFees, 0);
------------------------------------------------------------------------------------------
SELECT @SimilarDrugsDifferAmount = CAST(ISNULL(SUM(Tot_Differ), 0) AS BIGINT)
FROM dbo.TmpDrugHavaleh
WHERE Status = 7
      AND Id_Havaleh = @FactorId;
------------------------------------------------------------------------------------------
IF @CalcStatus IN ( 0, 2 )
    UPDATE FH
    SET FH.FirstCoverAmount = tblCalc.FirstCoverAmount,
        FH.NonInsuredExpense = tblCalc.NonInsuredExpense + @SimilarDrugsDifferAmount,
        FH.TotalAmount = tblCalc.TotalAmount + @IranTechnicalFees + @SimilarDrugsDifferAmount,
        FH.FranchiseAmount = tblCalc.FranchiseAmount + @PatientTechFees,
        FH.CoveredAmount = CASE
                               WHEN
                               (
                                   tblCalc.ObligationRemainedAmount <> -1
                                   AND tblCalc.ObligationRemainedAmount < (tblCalc.TotalAmount + @IranTechnicalFees)
                                                                          - (tblCalc.FirstCoverAmount
                                                                             + tblCalc.FranchiseAmount
                                                                             + @PatientTechFees
                                                                             + tblCalc.NonInsuredExpense
                                                                             + tblCalc.ObligationRemainedAmount
                                                                            )
                               ) THEN
                                   tblCalc.ObligationRemainedAmount
                               ELSE
        (tblCalc.TotalAmount + @IranTechnicalFees)
        - (tblCalc.FirstCoverAmount + tblCalc.FranchiseAmount + @PatientTechFees + tblCalc.NonInsuredExpense)
                           END,
        FH.PatientAmount = @SimilarDrugsDifferAmount
                           + CASE
                                 WHEN @PatientAmountCon = 0 THEN
                                     tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense
                                 WHEN @PatientAmountCon = 1 THEN
                                     tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense + @IranTechnicalFees
                                 WHEN @PatientAmountCon = 2 THEN
                                     tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense + @PatientTechnicalFees
                                 ELSE
                                     tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense + PatientAmountNonInsurance
                             END,
        FH.ExtraObligationCeiling = CASE
                                        WHEN tblCalc.ObligationRemainedAmount = -1 THEN
                                            0
                                        WHEN tblCalc.ObligationRemainedAmount < (tblCalc.TotalAmount
                                                                                 + @IranTechnicalFees
                                                                                )
                                                                                - (tblCalc.FirstCoverAmount
                                                                                   + tblCalc.FranchiseAmount
                                                                                   + tblCalc.NonInsuredExpense
                                                                                  ) THEN
        ((tblCalc.TotalAmount + @IranTechnicalFees)
         - (tblCalc.FirstCoverAmount + tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense)
        )
        - tblCalc.ObligationRemainedAmount
                                        ELSE
                                            0
                                    END
    FROM TmpDrugH FH
        JOIN
        (
            SELECT ISNULL(SUM(   CASE
                                     WHEN FD.Status NOT IN ( 0, 1, 2, 3 )
                                          AND (@FirstCoveredCon = 0) THEN
                                         ISNULL(FD.Tot_forosh * (100 - FD.BimarPercent) / 100, 0)
                                     ELSE
                                         0
                                 END
                             ),
                          0
                         ) FirstCoverAmount,
                   ISNULL(
                             SUM(ROUND(
                                          CASE
                                              WHEN FD.Status NOT IN ( 0, 1, 2, 3 )
                                                   AND (@FranchiseCon = 0) THEN
                                                  FD.Tot_forosh * (FD.BimarPercent / 100)
                                              WHEN FD.Status IN ( 0, 3 )
                                                   AND (@FranchiseCon = 3) THEN
                                                  FD.Tot_forosh * (@FranchisePercent / 100)
                                              WHEN FD.Status NOT IN ( 0, 1, 2, 3 )
                                                   AND (@FranchiseCon = 2) THEN
                                                  FD.Tot_forosh * ((100 - FD.BimarPercent) / 100)
                                                  * (@FranchisePercent / 100)
                                              WHEN FD.Status NOT IN ( 0, 1, 2, 3 )
                                                   AND (@FranchiseCon = 4) THEN
                                          (FD.Tot_forosh * (FD.BimarPercent / 100))
                                              ELSE
                                                  0
                                          END,
                                          0
                                      )
                                ),
                             0
                         ) FranchiseAmount,
                   ISNULL(CAST(SUM(   CASE
                                          WHEN @NonInsuredCon = 0
                                               AND FD.IRIStatus = 0
                                               AND Tot_Differ > 0 THEN
                                              FD.Tot_Differ
                                          WHEN @NonInsuredCon = 0
                                               AND FD.IRIStatus = 0
                                               AND FD.Status IN ( 0, 1, 2, 3 ) THEN
                                              FD.Tot_forosh
                                          WHEN @NonInsuredCon = 1 THEN
                                              0
                                          WHEN @NonInsuredCon = 2
                                               AND FD.Status IN ( 0, 1, 2, 3 ) THEN
                                              FD.Tot_forosh
                                          WHEN @NonInsuredCon = 2
                                               AND FD.Status NOT IN ( 0, 1, 2, 3 ) THEN
                                              FD.Tot_Differ
                                          WHEN @NonInsuredCon = 3
                                               AND FD.Status NOT IN ( 0, 1, 2, 3 ) THEN
                                              FD.Tot_Differ
                                          WHEN @NonInsuredCon = 3
                                               AND FD.Status IN ( 1, 2 ) THEN
                                              FD.Tot_forosh
                                      END
                                  ) AS BIGINT),
                          0
                         ) NonInsuredExpense,
                   ISNULL(SUM(   CASE
                                     WHEN @PatientAmountCon = 3
                                          AND FD.Status IN ( 0, 1, 2, 3 ) THEN
                                         FD.Tot_forosh
                                     WHEN @PatientAmountCon = 3
                                          AND FD.Status NOT IN ( 0, 1, 2, 3 ) THEN
                                         FD.Tot_Differ
                                 END
                             ),
                          0
                         ) PatientAmountNonInsurance,
                   ISNULL(SUM(FD.Tot_forosh + FD.Tot_Differ), 0) AS TotalAmount,
                   FH.Id_Havaleh,
                   MIN(ObligationRemainedAmount) ObligationRemainedAmount
            FROM dbo.IRI_InsuredContracts IC
                JOIN dbo.IRI_Prescriptions PR
                    ON PR.ContractId = IC.Id
                JOIN dbo.TmpDrugH FH
                    ON FH.Id_Havaleh = PR.PrescriptionNo
                JOIN dbo.TmpDrugHavaleh FD
                    ON FD.Id_Havaleh = FH.Id_Havaleh
                LEFT JOIN dbo.IRI_DrugCodeLink DL
                    ON DL.DrugCode = FD.K_Code
            WHERE FH.Id_Havaleh = @FactorId
                  AND FD.Status <> 7
                  --AND IsIRIObligation = 1
                  AND IC.Id = @ContractId
            GROUP BY PR.TransactionCode,
                     PR.NationalCode,
                     IC.ContractNumber,
                     IC.InsuredPersonId,
                     IC.ObligationCode,
                     PR.ServiceId,
                     IC.ContractNumber,
                     FH.Id_Havaleh
        ) AS tblCalc
            ON tblCalc.Id_Havaleh = FH.Id_Havaleh
    WHERE FH.Id_Havaleh = @FactorId;
------------------------------------------------------------------------------------------
IF (@CalcStatus = 1 AND @SendItemsCon = 1)
    SELECT 'Without Any Items';
ELSE
    SELECT *,
           CoveredAmount = CAST(FD.TotalAmount - (FD.FirstCoverAmount + FD.Franchise + FD.NonInsuredExpense) AS BIGINT),
           PatientAmount = CAST(CASE
                                    WHEN @PatientAmountCon = 0 THEN
                                        FD.Franchise + FD.NonInsuredExpense
                                    WHEN @PatientAmountCon = 1 THEN
                                        FD.Franchise + FD.NonInsuredExpense + @IranTechnicalFees
                                    WHEN @PatientAmountCon = 2 THEN
                                        FD.Franchise + FD.NonInsuredExpense + @PatientTechnicalFees
                                    ELSE
                                        FD.Franchise + FD.NonInsuredExpense + PatientAmountNonInsurance
                                END AS BIGINT)
    FROM
    (
        SELECT '275' TopServiceCode,
               N'دارن' TopServiceDesc,
               DL.IranDrugCode ServiceCode,
               CASE
                   WHEN (IRIDrugs.Name IS NULL)
                        OR (IRIDrugs.Name = '') THEN
                       Drug.Name1
                   ELSE
                       IRIDrugs.Name
               END ServiceDesc,
               FD.Radif,
               Drug.Name1,
               CAST(FD.K_Qty1 AS BIGINT) Quantity,
               CAST(FD.K_Qty1 AS REAL) QuantityDeliver,
               CAST(ISNULL(DU.Qty_Consum, 0) AS VARCHAR(20)) AS CycleUse,
               CAST(ISNULL(DU.Daily, 0) AS VARCHAR(20)) AS ConsumptionFactor,
               CAST(FD.Price_Forosh AS BIGINT) AS TarifUnit,
               CAST(FD.K_Qty1 * FD.Price_Forosh AS BIGINT) AS Tarif,
               NonInsuredExpense = ISNULL(   CAST(CASE
                                                      WHEN @NonInsuredCon = 0
                                                           AND FD.IRIStatus = 0
                                                           AND Tot_Differ > 0 THEN
                                                          FD.Tot_Differ
                                                      WHEN @NonInsuredCon = 0
                                                           AND FD.IRIStatus = 0
                                                           AND FD.Status IN ( 0, 1, 2, 3 ) THEN
                                                          FD.Tot_forosh
                                                      WHEN @NonInsuredCon = 1 THEN
                                                          0
                                                      WHEN @NonInsuredCon = 2
                                                           AND FD.Status IN ( 0, 1, 2, 3 ) THEN
                                                          FD.Tot_forosh
                                                      WHEN @NonInsuredCon = 2
                                                           AND FD.Status NOT IN ( 0, 1, 2, 3 ) THEN
                                                          FD.Tot_Differ
                                                      WHEN @NonInsuredCon = 3
                                                           AND FD.Status NOT IN ( 0, 1, 2, 3 ) THEN
                                                          FD.Tot_Differ
                                                      WHEN @NonInsuredCon = 3
                                                           AND FD.Status IN ( 1, 2 ) THEN
                                                          FD.Tot_forosh
                                                  END AS BIGINT),
                                             0
                                         ),
               FirstCoverAmount = CAST(CASE
                                           WHEN FD.Status NOT IN ( 0, 1, 2, 3 )
                                                AND (@FirstCoveredCon = 0) /*AND (FD.IRIStatus <> 0) */ THEN
                                               ISNULL(FD.Tot_forosh * (100 - FD.BimarPercent) / 100, 0)
                                           ELSE
                                               0
                                       END AS BIGINT),
               CAST(FD.Tot_forosh + FD.Tot_Differ AS BIGINT) TotalAmount,
               Franchise = ISNULL(
                                     CAST(ROUND(
                                                   CASE
                                                       WHEN FD.Status NOT IN ( 0, 1, 2, 3 )
                                                            AND (@FranchiseCon = 0) THEN
                                                           FD.Tot_forosh * (FD.BimarPercent / 100)
                                                       WHEN FD.Status IN ( 0, 3 )
                                                            AND (@FranchiseCon = 3) THEN
                                                           FD.Tot_forosh * (@FranchisePercent / 100)
                                                       WHEN FD.Status NOT IN ( 0, 1, 2, 3 )
                                                            AND (@FranchiseCon = 2) THEN
                                                           FD.Tot_forosh * ((100 - FD.BimarPercent) / 100)
                                                           * (@FranchisePercent / 100)
                                                       WHEN FD.Status NOT IN ( 0, 1, 2, 3 )
                                                            AND (@FranchiseCon = 4) THEN
                                                   (FD.Tot_forosh * (FD.BimarPercent / 100))
                                                       ELSE
                                                           0
                                                   END,
                                                   0
                                               ) AS BIGINT),
                                     0
                                 ),
               ISNULL(   CASE
                             WHEN @PatientAmountCon = 3
                                  AND FD.Status IN ( 0, 1, 2, 3 ) THEN
                                 FD.Tot_forosh
                             WHEN @PatientAmountCon = 3
                                  AND FD.Status NOT IN ( 0, 1, 2, 3 ) THEN
                                 FD.Tot_Differ
                         END,
                         0
                     ) PatientAmountNonInsurance,
               FD.AutoId RowId,
               ISNULL(IRIStatus, 1) IRIStatus,
               DS.Status_Name DrugStatus,
               FD.BimarPercent PateintPercentage,
               IsIRIObligation = CASE
                                     WHEN DL.DrugCode IS NOT NULL
                                          AND DL.DrugCode <> ''
                                          AND DL.IranDrugCode IS NOT NULL
                                          AND DL.IranDrugCode <> '' THEN
                                         1
                                     ELSE
                                         0
                                 END,
               ISNULL(FD.Tot_Differ, 0) DifferAmount,
               FD.Status
        FROM dbo.TmpDrugHavaleh FD
            INNER JOIN dbo.KalaId Drug
                ON Drug.K_Code = FD.K_Code
            LEFT JOIN dbo.IRI_Drugs IRIDrugs
                ON IRIDrugs.Code = Drug.K_Code
            LEFT JOIN TblUMDrugHavaleh UMD
                ON UMD.autoid = FD.AutoId
            LEFT JOIN dbo.TblDrugUse DU
                ON DU.Use_Code = UMD.use_code
            LEFT JOIN dbo.IRI_DrugCodeLink DL
                ON DL.DrugCode = FD.K_Code
            INNER JOIN dbo.Define_Status DS
                ON FD.Status = DS.Status_Code
        WHERE FD.Status <> 7
              AND FD.Id_Havaleh = @FactorId
    ) AS FD
    ORDER BY Radif;
------------------------------------------------------------------------------------------
SELECT PR.TransactionCode,
       PR.NationalCode,
       Con.ContractNumber,
       Con.InsuredPersonId,
       Con.ObligationCode,
       PR.ServiceId,
       Con.ContractNumber,
       CAST(FH.ExtraObligationCeiling AS BIGINT) ExtraObligationCeiling,
       CAST(FH.CoveredAmount AS BIGINT) CoveredAmount,
       CAST(FH.FirstCoverAmount AS BIGINT) FirstCoverAmount,
       CAST(FH.FranchiseAmount AS BIGINT) FranchiseAmount,
       CAST(FH.NonInsuredExpense AS BIGINT) NonInsuredExpense,
       CAST(FH.TotalAmount AS BIGINT) TotalAmount,
       CAST(FH.PatientAmount AS BIGINT) PatientAmount,
       @IranTechnicalFees TechnicalFees
FROM dbo.IRI_InsuredContracts Con
    JOIN dbo.IRI_Prescriptions PR
        ON PR.ContractId = Con.Id
    JOIN dbo.TmpDrugH FH
        ON FH.Id_Havaleh = PR.PrescriptionNo
    JOIN
    (
        SELECT Id_Havaleh
        FROM dbo.TmpDrugHavaleh
        WHERE Status <> 7
        --AND IsIRIObligation = 1
        GROUP BY Id_Havaleh
    ) tmpFactorDetail
        ON tmpFactorDetail.Id_Havaleh = FH.Id_Havaleh
WHERE FH.Id_Havaleh = @FactorId
      AND Con.Id = @ContractId;