CREATE PROCEDURE [dbo].[Reverse_FactorFinal_Drug]
    @Msg_Num NUMERIC(18, 0) OUTPUT,
    @Id NUMERIC(18, 0),
    @TypeFactor CHAR(1),
    @ReverseFactor SMALLINT
AS
DECLARE @Sender VARCHAR(5);
DECLARE @Reciver VARCHAR(8);
DECLARE @SenderNew VARCHAR(5);
DECLARE @ReciverNew VARCHAR(8);
DECLARE @Radif SMALLINT;
DECLARE @K_Code VARCHAR(15);
DECLARE @K_Qty1 FLOAT;
DECLARE @Price_Kharid MONEY;
DECLARE @Price_Forosh MONEY;
DECLARE @Main_Price MONEY;
DECLARE @Status SMALLINT;
DECLARE @Takhfif_Flag BIT;
DECLARE @Exp_Flag BIT;
DECLARE @Point_Flag BIT;
DECLARE @Serial_Flag BIT;
DECLARE @Make_Flag BIT;
DECLARE @Hamrah_Flag BIT;
DECLARE @CtrMojodi CHAR(1);
DECLARE @Tot_Differ MONEY;
DECLARE @Link_Like MONEY;
DECLARE @OldAutoId NUMERIC(9);
DECLARE @LinkRecord NUMERIC(9);
DECLARE @NewAutoId NUMERIC(9);
DECLARE @KindFlag CHAR(1);
DECLARE @Nu_ForoshNew VARCHAR(8);
DECLARE @Nu_Bargasht_ForoshNew VARCHAR(8);
DECLARE @Nu_Forosh VARCHAR(8);
DECLARE @Nu_Bargasht_Forosh VARCHAR(8);
DECLARE @TransName NVARCHAR(35);
DECLARE @ResiveName NVARCHAR(35);
DECLARE @TransNameNew NVARCHAR(35);
DECLARE @ResiveNameNew NVARCHAR(35);
DECLARE @Price_Forosh_O MONEY;
DECLARE @Price_Sazman_O MONEY;
DECLARE @SazmanCode CHAR(3);
DECLARE @BimarPercent REAL;
DECLARE @FamilyDoctor DEC(18, 0);
DECLARE @JetId INT;
DECLARE @TypeOfMedicine TINYINT;


SET @Msg_Num = 0;
IF
(
    SELECT COUNT(*)FROM dbo.DrugH WHERE Id_Havaleh = @Id
) > 0
AND
(
    SELECT COUNT(*)FROM dbo.DrugHavaleh WHERE Id_Havaleh = @Id
) > 0
BEGIN
    BEGIN TRAN RV;
    DECLARE @Last_Id NUMERIC(18, 0);

    SELECT @KindFlag = DrugH.Kind_Flag,
           @Nu_ForoshNew = DrugH.Nu_Forosh,
           @Nu_Bargasht_ForoshNew = DrugH.Nu_Bargasht_Forosh,
           @TransNameNew = DrugH.Trans_Name,
           @ResiveNameNew = DrugH.Resive_Name,
           @SenderNew = DrugHavaleh.Sender,
           @FamilyDoctor = DrugH.FamilyDoctorCode,
           @ReciverNew = DrugHavaleh.Reciver
    FROM DrugH
        INNER JOIN DrugHavaleh
            ON (DrugH.Id_Havaleh = DrugHavaleh.Id_Havaleh)
    WHERE DrugH.Id_Havaleh = @Id
    GROUP BY DrugH.Nu_Forosh,
             DrugH.Nu_Bargasht_Forosh,
             DrugHavaleh.Sender,
             DrugHavaleh.Reciver,
             DrugH.Kind_Flag,
             DrugH.Trans_Name,
             DrugH.Resive_Name,
             FamilyDoctorCode;

    IF @TypeFactor = '1'
    BEGIN
        SET @Nu_Bargasht_Forosh = '';
        SET @KindFlag = '1';
        IF @ReverseFactor = 0
        BEGIN
            SET @Sender = @SenderNew;
            SET @Reciver = @ReciverNew;
            SET @Nu_Forosh = @Nu_ForoshNew;
            SET @TransName = @TransNameNew;
            SET @ResiveName = @ResiveNameNew;
        END;
        ELSE IF @ReverseFactor = 1
        BEGIN
            SET @Sender = @ReciverNew;
            SET @Reciver = @SenderNew;
            SET @Nu_Forosh = @Nu_Bargasht_ForoshNew;
            SET @TransName = @ResiveNameNew;
            SET @ResiveName = @TransNameNew;
        END;
    END;

    ELSE IF @TypeFactor = '2'
    BEGIN
        SET @Nu_Forosh = '';
        SET @KindFlag = '2';
        IF @ReverseFactor = 0
        BEGIN
            SET @Sender = @ReciverNew;
            SET @Reciver = @SenderNew;
            SET @Nu_Bargasht_Forosh = @Nu_ForoshNew;
            SET @TransName = @ResiveNameNew;
            SET @ResiveName = @TransNameNew;
        END;
        ELSE IF @ReverseFactor = 1
        BEGIN
            SET @Sender = @SenderNew;
            SET @Reciver = @ReciverNew;
            SET @Nu_Bargasht_Forosh = @Nu_Bargasht_ForoshNew;
            SET @TransName = @TransNameNew;
            SET @ResiveName = @ResiveNameNew;
        END;
    END;


    INSERT INTO dbo.TmpDrugH
    (
        Kind_Flag,
        Nu_Forosh,
        Nu_Bargasht_Forosh,
        Del_Flag,
        Note_Code,
        Dumy_Code,
        Grade_Code,
        Nezam_No,
        Resive_Name,
        Trans_Name,
        Sazman_Code,
        Radif_Noskheh,
        Computer_Code,
        Page_No,
        User_Cash,
        TotPriceBimar,
        TotPriceSazman,
        TotPriceDiffer,
        TotPriceAzad,
        TotPriceKala,
        TotPriceTaghizat,
        HOtc,
        TotPriceOTC,
        TotPriceFaniBimar,
        TotPriceFaniSazman,
        TotPriceDaryafti,
        TotPriceforosh,
        TotPriceAfzayandeh,
        TotPriceKahandeh,
        User_id,
        Tarikh_Etebar,
        Tarikh_Noskheh,
        Tarikh_Paziresh,
        kind_Save,
        PT_Code,
        Par_No,
        NumberQueue,
        FamilyDoctorCode,
        InternetVerificationUser,
        PrescriptionUser,
        PrescriptionType,
        PrescriptionId
    )
    SELECT @KindFlag,
           @Nu_Forosh,
           @Nu_Bargasht_Forosh,
           Del_Flag,
           Note_Code,
           Dumy_Code,
           Grade_Code,
           Nezam_No,
           @ResiveName,
           @TransName,
           Sazman_Code,
           Radif_Noskheh,
           Computer_Code,
           Page_No,
           User_Cash,
           TotPriceBimar,
           TotPriceSazman,
           TotPriceDiffer,
           TotPriceAzad,
           TotPriceKala,
           TotPriceTaghizat,
           HOtc,
           TotPriceOTC,
           TotPriceFaniBimar,
           TotPriceFaniSazman,
           TotPriceDaryafti,
           TotPriceforosh,
           TotPriceAfzayandeh,
           TotPriceKahandeh,
           User_id,
           Tarikh_Etebar,
           Tarikh_Noskheh,
           Tarikh_Paziresh,
           0,
           PT_Code,
           Par_No,
           NumberQueue,
           FamilyDoctorCode,
           InternetVerificationUser,
           PrescriptionUser,
           PrescriptionType,
           PrescriptionId
    FROM DrugH
    WHERE Id_Havaleh = @Id;
    IF @@Error > 0
    BEGIN
        ROLLBACK TRANSACTION RV;
        SET @Msg_Num = -5;
        RETURN @Msg_Num;
    END;

    SELECT @Last_Id = @@Identity;
    SELECT @SazmanCode = Sazman_Code
    FROM DrugH
    WHERE Id_Havaleh = @Id;


    DECLARE TmpDrugHavaleh_Cursor CURSOR LOCAL FOR
    SELECT AutoId,
           @Sender,
           @Reciver,
           Radif,
           K_Code,
           K_Qty1,
           Price_Kharid,
           Price_Forosh,
           Main_Price,
           Status,
           Takhfif_Flag,
           Point_Flag,
           Serial_Flag,
           Hamrah_Flag,
           Exp_Flag,
           Make_Flag,
           Tot_Differ,
           Link_Like,
           Link_Record,
           BimarPercent,
           JetId,
           TypeOfMedicine
    FROM DrugHavaleh
    WHERE Id_Havaleh = @Id
    ORDER BY AutoId,
             Link_Like,
             Link_Record;

    OPEN TmpDrugHavaleh_Cursor;

    FETCH NEXT FROM TmpDrugHavaleh_Cursor
    INTO @OldAutoId,
         @Sender,
         @Reciver,
         @Radif,
         @K_Code,
         @K_Qty1,
         @Price_Kharid,
         @Price_Forosh,
         @Main_Price,
         @Status,
         @Takhfif_Flag,
         @Point_Flag,
         @Serial_Flag,
         @Hamrah_Flag,
         @Exp_Flag,
         @Make_Flag,
         @Tot_Differ,
         @Link_Like,
         @LinkRecord,
         @BimarPercent,
         @JetId,
         @TypeOfMedicine;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @Link_Like <> 0
            SELECT @Link_Like = AutoId
            FROM TmpDrugHavaleh
            WHERE Id_Havaleh = @Last_Id
                  AND K_Code =
                  (
                      SELECT K_Code
                      FROM DrugHavaleh
                      WHERE Id_Havaleh = @Id
                            AND AutoId = @Link_Like
                  );

        IF @Status IN ( '7', '8' )
        BEGIN
            IF LEN(@Sender) = 3
                SELECT @Price_Forosh_O = Price_Forosh
                FROM Anbar
                WHERE A_Code = @Sender
                      AND K_Code = @K_Code;
            ELSE
                SELECT @Price_Forosh_O = Price_Forosh
                FROM Anbar
                WHERE A_Code = @Reciver
                      AND K_Code = @K_Code;
            SELECT @Price_Sazman_O = Price
            FROM Price_Sazman
            WHERE Sazman_Code = @SazmanCode
                  AND K_Code = @K_Code;
        END;

        SELECT @CtrMojodi = CtrMojodi
        FROM AnbarId
        WHERE A_Code = @Sender;

        IF @CtrMojodi = '2'
        BEGIN
            SELECT @CtrMojodi = CtrMojodi
            FROM dbo.KalaId
            WHERE K_Code = @K_Code;
        END;

        IF ISNULL(@LinkRecord, 0) <> 0
        BEGIN
            DECLARE @KCode VARCHAR(20);
            DECLARE @Qty MONEY;
            DECLARE @Price MONEY;
            SELECT @KCode = K_Code,
                   @Qty = K_Qty1,
                   @Price = Price_Forosh
            FROM dbo.DrugHavaleh
            WHERE AutoId = @LinkRecord
                  AND Id_Havaleh = @Id;

            SELECT @LinkRecord = AutoId
            FROM TmpDrugHavaleh
            WHERE K_Code = @KCode
                  AND K_Qty1 = @Qty
                  AND Price_Forosh = @Price
                  AND Id_Havaleh = @Last_Id;
        END;

        IF @Tot_Differ > 0
            SET @Main_Price = @Price_Forosh + ROUND(@Tot_Differ / @K_Qty1, 0);

        INSERT INTO dbo.TmpDrugHavaleh
        (
            Id_Havaleh,
            Sender,
            Reciver,
            Radif,
            K_Code,
            K_Qty1,
            Price_Kharid,
            Price_Forosh,
            Main_Price,
            Status,
            Takhfif_Flag,
            Point_Flag,
            Serial_Flag,
            Hamrah_Flag,
            Exp_Flag,
            Make_Flag,
            CtrMojodi,
            Tot_Differ,
            Link_Like,
            Link_Record,
            EditQty_Flag,
            Price_Forosh_O,
            Price_Sazman_O,
            BimarPercent,
            JetId,
            TypeOfMedicine
        )
        VALUES
        (@Last_Id, @Sender, @Reciver, @Radif, @K_Code, @K_Qty1, @Price_Kharid, @Price_Forosh, @Main_Price, @Status,
         @Takhfif_Flag, @Point_Flag, @Serial_Flag, @Hamrah_Flag, @Exp_Flag, @Make_Flag, @CtrMojodi, @Tot_Differ,
         @Link_Like, @LinkRecord, '1', @Price_Forosh_O, @Price_Sazman_O, @BimarPercent, @JetId, @TypeOfMedicine);


        IF @@Error > 0
        BEGIN
            ROLLBACK TRANSACTION RV;
            SET @Msg_Num = -5;
            RETURN @Msg_Num;
        END;

        SELECT @NewAutoId = @@Identity;


        IF EXISTS
        (
            SELECT *
            FROM DrugHavaleh
                INNER JOIN Over_Under_Row
                    ON DrugHavaleh.AutoId = Over_Under_Row.AutoId
            WHERE DrugHavaleh.Id_Havaleh = @Id
                  AND Over_Under_Row.AutoId = @OldAutoId
        )
            INSERT INTO dbo.Tmp_Over_Under_Row
            (
                AutoId,
                Name,
                Code_Act,
                Type,
                Price,
                [Percent],
                Code_Over_Under
            )
            SELECT @NewAutoId,
                   Over_Under_Row.Name,
                   Over_Under_Row.Code_Act,
                   Over_Under_Row.Type,
                   Over_Under_Row.Price,
                   Over_Under_Row.[Percent],
                   Over_Under_Row.Code_Over_Under
            FROM DrugHavaleh
                INNER JOIN Over_Under_Row
                    ON DrugHavaleh.AutoId = Over_Under_Row.AutoId
            WHERE (DrugHavaleh.Id_Havaleh = @Id)
                  AND (DrugHavaleh.AutoId = @OldAutoId);
        IF @@Error > 0
        BEGIN
            ROLLBACK TRANSACTION RV;
            SET @Msg_Num = -5;
            RETURN @Msg_Num;
        END;
        FETCH NEXT FROM TmpDrugHavaleh_Cursor
        INTO @OldAutoId,
             @Sender,
             @Reciver,
             @Radif,
             @K_Code,
             @K_Qty1,
             @Price_Kharid,
             @Price_Forosh,
             @Main_Price,
             @Status,
             @Takhfif_Flag,
             @Point_Flag,
             @Serial_Flag,
             @Hamrah_Flag,
             @Exp_Flag,
             @Make_Flag,
             @Tot_Differ,
             @Link_Like,
             @LinkRecord,
             @BimarPercent,
             @JetId,
             @TypeOfMedicine;
    END; --WHILE @@FETCH_STATUS = 0     

    CLOSE TmpDrugHavaleh_Cursor;
    DEALLOCATE TmpDrugHavaleh_Cursor;

    INSERT INTO dbo.Tmp_Over_Under_Factor_Drug
    (
        Id_Havaleh,
        Code_Over_Under,
        Price,
        [Percent],
        Act_Code,
        Guy_Code,
        Type,
        Name
    )
    SELECT @Last_Id,
           Over_Under_Factor_Drug.Code_Over_Under,
           Over_Under_Factor_Drug.Price,
           Over_Under_Factor_Drug.[Percent],
           Over_Under_Factor_Drug.Act_Code,
           Over_Under_Factor_Drug.Guy_Code,
           Over_Under_Factor_Drug.Type,
           Over_Under.Name
    FROM Over_Under_Factor_Drug
        INNER JOIN Over_Under
            ON Over_Under_Factor_Drug.Code_Over_Under = Over_Under.AutoId
    WHERE (Over_Under_Factor_Drug.Id_Havaleh = @Id)
          AND Over_Under_Factor_Drug.Price <> 0
          AND Over_Under.Action_Code IN ( '-1', '-2', '-3' );

    IF @@Error > 0
    BEGIN
        ROLLBACK TRANSACTION RV;
        SET @Msg_Num = -5;
        RETURN @Msg_Num;
    END;

    ------------------------------------------------------------------------------------------------
    COMMIT TRAN RV;
    SELECT @Msg_Num = @Last_Id;
    RETURN @Msg_Num;
END; -- IF (Select Count(*)  From Dbo.TmpHeadH Where Id_Havaleh = @TmpId  ) > 0   And  (Select Count(*)  From Dbo.TmpHavaleh  Where Id_Havaleh = @TmpId) > 0*/
ELSE
BEGIN
    SET @Msg_Num = -1;
    RETURN @Msg_Num;
    PRINT 'Not Exists For  FactorFinal';
END;
