CREATE PROC [dbo].[SupplementaryInsurance_CalcFormula]
    @FactorId BIGINT,
    @TotPrice MONEY,
    @TransactionId SMALLINT,
    @InsuranceCode VARCHAR(3),
    @PatientTechnicalRight MONEY,
    @Formula NVARCHAR(MAX)
AS
BEGIN
    DECLARE @IncDecValue MONEY = 0;
    DECLARE @NonInsuranceFlag BIT;
    DECLARE @PatientFlag BIT;
    DECLARE @GoodsFlag BIT;
    DECLARE @DifferFlag BIT;

    /*---------------------------------------------------------> Get Tamin Transaction Code Support */

    DECLARE @SupportTransactionCode SMALLINT = 0;

    IF EXISTS
    (
        SELECT 1
        FROM dbo.GeneralSetting
        WHERE Parameter = 'TAMIN_Settings_ActiveSaveSupport'
              AND Value = 'True'
    )
    BEGIN
        SELECT @SupportTransactionCode = CAST(ISNULL(Value, 0) AS SMALLINT)
        FROM dbo.GeneralSetting
        WHERE Parameter = 'TAMIN_Settings_TransactionCodeSupport';

        IF NOT EXISTS
        (
            SELECT [Name]
            FROM dbo.Over_Under
            WHERE AutoId = @SupportTransactionCode
        )
            SET @SupportTransactionCode = 0;
    END;
    SELECT @SupportTransactionCode SupportTransactionCode;

    /*---------------------------------------------------------> Clear Previouse Supplementary InsurancePrice At Tmp Detail*/

    DECLARE @CalcTechnicalRight INT =
    (
        SELECT [Percent]
        FROM dbo.SupplementaryInsurance
        WHERE Code = @InsuranceCode
    ) * @PatientTechnicalRight / 100;

    UPDATE dbo.TmpDrugHavaleh
    SET SupplementaryInsurancePrice = 0
    WHERE Id_Havaleh = @FactorId;

    /*---------------------------------------------------------> Get Calculated Flag From SupplementaryInsurance*/
    SELECT @NonInsuranceFlag = NonInsuranceFlag,
           @PatientFlag = PatientFlag,
           @GoodsFlag = GoodsFlag,
           @DifferFlag = DifferFlag
    FROM dbo.SupplementaryInsurance
    WHERE Code = @InsuranceCode;

    /*---------------------------------------------------------> Declare Table For Save Calculated Values*/
    DECLARE @SupplementaryInsurance AS TABLE
    (
        Autoid BIGINT,
        InsuranceCode VARCHAR(3),
        GoodsCode VARCHAR(15),
        DifferAmount MONEY,
        PatientAmount MONEY,
        GoodsAmount MONEY,
        NonInsuranceAmount MONEY,
        EquipmentAmount MONEY,
        SupportAmount MONEY,
        TotPrice MONEY
            DEFAULT (0),
        Status TINYINT,
        IsMultipleDiffer BIT,
        IsMultiplePatient BIT,
        IsMultipleGoods BIT,
        IsMultipleNonInsurance BIT
    );

    /*---------------------------------------------------------> Insert Into Declare Table From Supplementary Drug Condition*/
    INSERT INTO @SupplementaryInsurance
    (
        Autoid,
        InsuranceCode,
        GoodsCode,
        DifferAmount,
        PatientAmount,
        GoodsAmount,
        NonInsuranceAmount,
        EquipmentAmount,
        SupportAmount,
        TotPrice,
        Status,
        IsMultipleDiffer,
        IsMultiplePatient,
        IsMultipleGoods,
        IsMultipleNonInsurance
    )
    SELECT AutoId,
           InsuranceCode,
           GoodsCode,
           DifferAmount,
           PatientAmount,
           GoodsAmount,
           NonInsuranceAmount,
           EquipmentAmount,
           ISNULL(SupportAmount, 0),
           TotPrice,
           Status,
           IsMultipleDiffer,
           IsMultiplePatient,
           ISMultipleGoods,
           ISMultipleNonInsurance
    FROM
    (
        SELECT T.AutoId,
               S.InsuranceCode,
               T.K_Code GoodsCode,
               CASE
                   WHEN ISNULL(S.DifferAmount, 0) = 0 THEN
                       0
                   WHEN ISNULL(S.DifferAmount, 0) > 99 THEN
               (CASE
                    WHEN (ISNULL(S.DifferAmount, 0) <= (T.Tot_Differ / T.K_Qty1))
                         AND
                         (
                             S.Ceilling = 0
                             OR S.Ceilling >= T.K_Qty1
                         ) THEN
                        ISNULL(S.DifferAmount, 0) * T.K_Qty1
                    WHEN (ISNULL(S.DifferAmount, 0) <= (T.Tot_Differ / T.K_Qty1))
                         AND
                         (
                             S.Ceilling <> 0
                             AND S.Ceilling < T.K_Qty1
                         ) THEN
                        ISNULL(S.DifferAmount, 0) * S.Ceilling
                    ELSE
                        T.Tot_Differ
                END
               )
                   WHEN ISNULL(S.DifferAmount, 0) = 1 THEN
               (CASE
                    WHEN S.Ceilling = 0
                         OR S.Ceilling >= T.K_Qty1 THEN
                        T.Tot_Differ
                    WHEN S.Ceilling <> 0
                         AND S.Ceilling < T.K_Qty1 THEN
                        S.Ceilling * (T.Tot_Differ / T.K_Qty1)
                END
               )
                   WHEN ISNULL(S.DifferAmount, 0)
                        BETWEEN 2 AND 99 THEN
               (CASE
                    WHEN S.Ceilling = 0
                         OR S.Ceilling >= T.K_Qty1 THEN
                        S.DifferAmount * T.Tot_Differ / 100
                    WHEN S.Ceilling <> 0
                         AND S.Ceilling < T.K_Qty1 THEN
                        S.Ceilling * ((S.DifferAmount * T.Tot_Differ) / 100 / T.K_Qty1)
                END
               )
               END AS DifferAmount,
               CASE
                   WHEN ISNULL(S.PatientAmount, 0) = 0 THEN
                       0
                   WHEN ISNULL(S.PatientAmount, 0) > 99 THEN
               (CASE
                    WHEN (ISNULL(S.PatientAmount, 0) <= ((T.Tot_forosh * T.BimarPercent * .01) / T.K_Qty1)
                                                        - ISNULL(Tr.Price, 0)
                         )
                         AND
                         (
                             S.Ceilling = 0
                             OR S.Ceilling >= T.K_Qty1
                         ) THEN
                        ISNULL(S.PatientAmount, 0) * T.K_Qty1
                    WHEN (ISNULL(S.PatientAmount, 0) <= ((T.Tot_forosh * T.BimarPercent * .01) / T.K_Qty1)
                                                        - ISNULL(Tr.Price, 0)
                         )
                         AND
                         (
                             S.Ceilling <> 0
                             AND S.Ceilling < T.K_Qty1
                         ) THEN
                        ISNULL(S.PatientAmount, 0) * S.Ceilling
                    ELSE
               (T.Tot_forosh * T.BimarPercent * .01) - ISNULL(Tr.Price, 0)
                END
               )
                   WHEN ISNULL(S.PatientAmount, 0) = 1 THEN
               (CASE
                    WHEN S.Ceilling = 0
                         OR S.Ceilling >= T.K_Qty1 THEN
               (T.Tot_forosh * T.BimarPercent * .01) - ISNULL(Tr.Price, 0)
                    WHEN S.Ceilling <> 0
                         AND S.Ceilling < T.K_Qty1 THEN
               (S.Ceilling * T.Price_Forosh * T.BimarPercent * .01) - ISNULL(Tr.Price, 0)
                END
               )
                   WHEN ISNULL(S.PatientAmount, 0)
                        BETWEEN 2 AND 99 THEN
               (CASE
                    WHEN S.Ceilling = 0
                         OR S.Ceilling >= T.K_Qty1 THEN
               (((T.Tot_forosh * T.BimarPercent * .01) * S.PatientAmount) * .01) - ISNULL(Tr.Price, 0)
                    WHEN S.Ceilling <> 0
                         AND S.Ceilling < T.K_Qty1 THEN
               (S.Ceilling * T.Price_Forosh * T.BimarPercent * .01) - ISNULL(Tr.Price, 0)
                END
               )
               END AS PatientAmount,
               CASE
                   WHEN T.[Status] = 1 THEN
               (CASE
                    WHEN ISNULL(S.GoodsAmount, 0) = 0 THEN
                        0
                    WHEN ISNULL(S.GoodsAmount, 0) > 99 THEN
               (CASE
                    WHEN (ISNULL(S.GoodsAmount, 0) <= T.Price_Forosh)
                         AND
                         (
                             S.Ceilling = 0
                             OR S.Ceilling >= T.K_Qty1
                         ) THEN
                        ISNULL(S.GoodsAmount, 0) * T.K_Qty1
                    WHEN (ISNULL(S.GoodsAmount, 0) <= T.Price_Forosh)
                         AND
                         (
                             S.Ceilling <> 0
                             AND S.Ceilling < T.K_Qty1
                         ) THEN
                        ISNULL(S.GoodsAmount, 0) * S.Ceilling
                    ELSE
                        T.Tot_forosh
                END
               )
                    WHEN ISNULL(S.GoodsAmount, 0) = 1 THEN
               (CASE
                    WHEN S.Ceilling = 0
                         OR S.Ceilling >= T.K_Qty1 THEN
                        T.Tot_forosh
                    WHEN S.Ceilling <> 0
                         AND S.Ceilling < T.K_Qty1 THEN
                        S.Ceilling * T.Price_Forosh
                END
               )
                    WHEN ISNULL(S.GoodsAmount, 0)
                         BETWEEN 2 AND 99 THEN
               (CASE
                    WHEN S.Ceilling = 0
                         OR S.Ceilling >= T.K_Qty1 THEN
               (T.Tot_forosh * S.GoodsAmount) / 100
                    WHEN S.Ceilling <> 0
                         AND S.Ceilling < T.K_Qty1 THEN
               (S.Ceilling * T.Price_Forosh * S.GoodsAmount) / 100
                END
               )
                END
               )
                   ELSE
                       0
               END AS GoodsAmount,
               CASE
                   WHEN T.[Status] = 0 THEN
               (CASE
                    WHEN ISNULL(S.NonInsuranceAmount, 0) = 0 THEN
                        0
                    WHEN ISNULL(S.NonInsuranceAmount, 0) > 99 THEN
               (CASE
                    WHEN (ISNULL(S.NonInsuranceAmount, 0) <= T.Price_Forosh)
                         AND
                         (
                             S.Ceilling = 0
                             OR S.Ceilling >= T.K_Qty1
                         ) THEN
                        ISNULL(S.NonInsuranceAmount, 0) * T.K_Qty1
                    WHEN (ISNULL(S.NonInsuranceAmount, 0) <= T.Price_Forosh)
                         AND
                         (
                             S.Ceilling <> 0
                             AND S.Ceilling < T.K_Qty1
                         ) THEN
                        ISNULL(S.NonInsuranceAmount, 0) * S.Ceilling
                    ELSE
                        T.Tot_forosh
                END
               )
                    WHEN ISNULL(S.NonInsuranceAmount, 0) = 1 THEN
               (CASE
                    WHEN S.Ceilling = 0
                         OR S.Ceilling >= T.K_Qty1 THEN
                        T.Tot_forosh
                    WHEN S.Ceilling <> 0
                         AND S.Ceilling < T.K_Qty1 THEN
                        S.Ceilling * T.Price_Forosh
                END
               )
                    WHEN ISNULL(S.NonInsuranceAmount, 0)
                         BETWEEN 2 AND 99 THEN
               (CASE
                    WHEN S.Ceilling = 0
                         OR S.Ceilling >= T.K_Qty1 THEN
               (T.Tot_forosh * S.NonInsuranceAmount) / 100
                    WHEN S.Ceilling <> 0
                         AND S.Ceilling < T.K_Qty1 THEN
               (S.Ceilling * T.Price_Forosh * S.NonInsuranceAmount) / 100
                END
               )
                END
               )
                   ELSE
                       0
               END AS NonInsuranceAmount,
               CASE
                   WHEN T.[Status] = 2 THEN
                       T.Tot_forosh
                   ELSE
                       0
               END AS EquipmentAmount,
               ISNULL(ISNULL(Tr.Price, 0), 0) SupportAmount,
               0 TotPrice,
               T.[Status],
               CASE
                   WHEN ISNULL(S.DifferAmount, 0)
                        BETWEEN 2 AND 99 THEN
                       1
                   ELSE
                       0
               END AS IsMultipleDiffer,
               CASE
                   WHEN ISNULL(S.PatientAmount, 0)
                        BETWEEN 2 AND 99 THEN
                       1
                   ELSE
                       0
               END AS IsMultiplePatient,
               CASE
                   WHEN ISNULL(S.GoodsAmount, 0)
                        BETWEEN 2 AND 99 THEN
                       1
                   ELSE
                       0
               END AS ISMultipleGoods,
               CASE
                   WHEN ISNULL(S.NonInsuranceAmount, 0)
                        BETWEEN 2 AND 99 THEN
                       1
                   ELSE
                       0
               END AS ISMultipleNonInsurance
        FROM dbo.TmpDrugHavaleh T
            INNER JOIN dbo.SupplementaryInsurance_DrugCondition S
                ON S.GoodsCode = T.K_Code
            LEFT JOIN dbo.Tmp_Over_Under_Row_Drug Tr
                ON Tr.PrescriptionId = T.AutoId
                   AND Tr.Code_Over_Under = @SupportTransactionCode
        WHERE T.Id_Havaleh = @FactorId
              AND T.[Status] <> 7
              AND ISNULL(T.Serial_Flag, 0) = 1
    ) AS SupplementaryInsurance
    WHERE InsuranceCode = @InsuranceCode;

    /*---------------------------------------------------------> Insert Into Declare Table NOT Definded Supplementary Drug Condition*/

    INSERT INTO @SupplementaryInsurance
    (
        Autoid,
        InsuranceCode,
        GoodsCode,
        DifferAmount,
        PatientAmount,
        GoodsAmount,
        NonInsuranceAmount,
        EquipmentAmount,
        SupportAmount,
        TotPrice,
        Status,
        IsMultipleDiffer,
        IsMultiplePatient,
        IsMultipleGoods,
        IsMultipleNonInsurance
    )
    SELECT T.AutoId,
           '',
           T.K_Code,
           CASE
               WHEN T.[Status] = 7 THEN
                   0
               WHEN @DifferFlag = 0
                    AND T.Tot_Differ > 0 THEN
                   0
               WHEN @DifferFlag = 1
                    AND T.Tot_Differ > 0 THEN
                   T.Tot_Differ
           END AS DifferAmount,
           CASE
               WHEN T.[Status] = 7 THEN
                   0
               WHEN @PatientFlag = 0
                    AND T.[Status] IN ( 5, 6, 8, 9 ) THEN
                   0
               WHEN @PatientFlag = 1
                    AND T.[Status] IN ( 5, 6, 8, 9 ) THEN
           (T.Tot_forosh * T.BimarPercent * .01) - ISNULL(Tr.Price, 0)
           END AS PatientAmount,
           CASE
               WHEN T.[Status] = 7 THEN
                   0
               WHEN @GoodsFlag = 0
                    AND T.[Status] = 1 THEN
                   0
               WHEN @GoodsFlag = 1
                    AND T.[Status] = 1 THEN
                   T.Tot_forosh
           END AS GoodsAmount,
           CASE
               WHEN T.[Status] = 7 THEN
                   0
               WHEN @NonInsuranceFlag = 0
                    AND T.[Status] = 0 THEN
                   0
               WHEN @NonInsuranceFlag = 1
                    AND T.[Status] = 0 THEN
                   T.Tot_forosh
           END AS NonInsuranceAmount,
           CASE
               WHEN T.[Status] = 2 THEN
                   T.Tot_forosh
               ELSE
                   0
           END AS EquipmentAmount,
           ISNULL(ISNULL(Tr.Price, 0), 0) SupportAmount,
           0,
           T.[Status],
           0,
           0,
           0,
           0
    FROM dbo.TmpDrugHavaleh T
        LEFT JOIN dbo.Tmp_Over_Under_Row_Drug Tr
            ON Tr.PrescriptionId = T.AutoId
               AND Tr.Code_Over_Under = @SupportTransactionCode
    WHERE T.AutoId NOT IN
          (
              SELECT Autoid FROM @SupplementaryInsurance
          )
          AND T.Id_Havaleh = @FactorId
          AND ISNULL(T.Serial_Flag, 0) = 1;

    /*---------------------------------------------------------> Calculate Supplementary Insurance By Input Formula */
    UPDATE @SupplementaryInsurance
    SET TotPrice = T.TotalAmount
    FROM @SupplementaryInsurance S
        INNER JOIN
        (
            SELECT Status,
                   GoodsCode,
                   Autoid,
                   CASE
                       WHEN IsMultipleNonInsurance = 1 THEN
                           ISNULL(NonInsuranceAmount, 0)
                       ELSE
                           REPLACE(SUBSTRING(@Formula, 0, CHARINDEX('A', @Formula) - 1), '0.', '')
                           * ISNULL(NonInsuranceAmount, 0) / 100
                   END
                   + CASE
                         WHEN IsMultipleGoods = 1 THEN
                             GoodsAmount
                         ELSE
                             REPLACE(
                                        SUBSTRING(
                                                     @Formula,
                                                     CHARINDEX('A', @Formula) + 2,
                                                     CHARINDEX('B', @Formula) - CHARINDEX('A', @Formula) - 3
                                                 ),
                                        '0.',
                                        ''
                                    ) * ISNULL(GoodsAmount, 0) / 100
                     END
                   + CASE
                         WHEN IsMultipleDiffer = 1 THEN
                             DifferAmount
                         ELSE
                             REPLACE(
                                        SUBSTRING(
                                                     @Formula,
                                                     CHARINDEX('B', @Formula) + 2,
                                                     CHARINDEX('C', @Formula) - CHARINDEX('B', @Formula) - 3
                                                 ),
                                        '0.',
                                        ''
                                    ) * ISNULL(DifferAmount, 0) / 100
                     END
                   + CASE
                         WHEN IsMultiplePatient = 1 THEN
                             PatientAmount
                         ELSE
                             REPLACE(
                                        SUBSTRING(
                                                     @Formula,
                                                     CHARINDEX('C', @Formula) + 2,
                                                     CHARINDEX('D', @Formula) - CHARINDEX('C', @Formula) - 3
                                                 ),
                                        '0.',
                                        ''
                                    ) * ISNULL(PatientAmount, 0) / 100
                     END
                   + REPLACE(
                                SUBSTRING(
                                             @Formula,
                                             CHARINDEX('D', @Formula) + 2,
                                             CHARINDEX('E', @Formula) - CHARINDEX('D', @Formula) - 3
                                         ),
                                '0.',
                                ''
                            ) * ISNULL(EquipmentAmount, 0) / 100 TotalAmount
            FROM @SupplementaryInsurance
        ) AS T
            ON T.GoodsCode = S.GoodsCode
               AND T.Autoid = S.Autoid
               AND S.[Status] = T.Status;
    /*---------------------------------------------------------> Update Price In TmpDrugHavaleh And Over_Under_Factor_Drug */
    ---SELECT * FROM @SupplementaryInsurance

    UPDATE dbo.TmpDrugHavaleh
    SET SupplementaryInsurancePrice = S.TotPrice
    FROM dbo.TmpDrugHavaleh T
        INNER JOIN @SupplementaryInsurance S
            ON S.[Autoid] = T.[AutoId]
    WHERE T.Id_Havaleh = @FactorId;

    SELECT @IncDecValue =
    (
        SELECT SUM(TotPrice)FROM @SupplementaryInsurance
    ) + @CalcTechnicalRight;


    UPDATE dbo.Tmp_Over_Under_Factor_Drug
    SET Price = @IncDecValue,
        [Percent] = @IncDecValue * 100 / @TotPrice
    WHERE Id_Havaleh = @FactorId
          AND Code_Over_Under = @TransactionId;
END;