CREATE TRIGGER [dbo].[TI_HamrahDrug]
ON [dbo].[TmpDrugHavaleh]
FOR INSERT
AS
IF (
   (
       SELECT inserted.Hamrah_Flag FROM inserted
   ) = 1
   )
   AND (
       (
           SELECT ISNULL(inserted.EditQty_Flag, 0)FROM inserted
       ) <> 1
       )
BEGIN
    DECLARE @IdHavaleh NUMERIC(18, 0);
    DECLARE @Sender VARCHAR(5);
    DECLARE @Reciver VARCHAR(5);
    DECLARE @Autoid NUMERIC(9);
    DECLARE @K_Code VARCHAR(15);
    DECLARE @HK_Code VARCHAR(15);
    DECLARE @K_Qty REAL;
    DECLARE @HK_Qty REAL;
    DECLARE @Status CHAR(1);
    DECLARE @StatusField CHAR(1);
    DECLARE @msg_num NUMERIC(18, 0);
    DECLARE @Sazman_Code VARCHAR(3);
    DECLARE @Grade_Code VARCHAR(3);
    DECLARE @Darou_Flag CHAR(1);
    DECLARE @Price MONEY;
    DECLARE @Price_Forosh MONEY;
    DECLARE @StatusMainPrice MONEY;
    DECLARE @StatusForosh MONEY;
    DECLARE @SumDiffer MONEY;
    DECLARE @Takhfif CHAR(1);
    DECLARE @Point BIT;
    DECLARE @Hamrah BIT;
    DECLARE @Make BIT;
    DECLARE @Exp BIT;
    DECLARE @Serial BIT;
    DECLARE @CtrMojodi CHAR(1);
    DECLARE @Radif SMALLINT;
    DECLARE @EditQty Flag;
    DECLARE @KindFlag CHAR(1);
    DECLARE @Farma CHAR(1);
    DECLARE @S_PriceSazman CHAR(1);
    DECLARE @Active CHAR(1);
    DECLARE @PriceSazman MONEY;
    DECLARE @SendRecive VARCHAR(3);
    DECLARE @StatusMainField CHAR(1);
    DECLARE @BimarPercent REAL;
    DECLARE @CashBimarpercent REAL;
    SELECT @Sazman_Code = TmpDrugH.Sazman_Code,
           @KindFlag = TmpDrugH.Kind_Flag,
           @Grade_Code = TmpDrugH.Grade_Code
    FROM TmpDrugH
        INNER JOIN Inserted
            ON TmpDrugH.Id_Havaleh = Inserted.Id_Havaleh
    GROUP BY Sazman_Code,
             Kind_Flag,
             Grade_Code;
    SELECT @IdHavaleh = Id_Havaleh,
           @Sender = Sender,
           @Reciver = Reciver,
           @Autoid = AutoId,
           @K_Code = K_Code,
           @Radif = Radif,
           @K_Qty = K_Qty1,
           @StatusMainField = Status
    FROM Inserted;

    SELECT @CashBimarpercent = Bimar_Percent
    FROM Sahmiyeh
    WHERE Sazman_Code = @Sazman_Code;

    DECLARE HamrahFetch CURSOR FOR
    SELECT K_Code_H,
           K_Qty1,
           EditQty_Flag
    FROM Hamrah_Kala
    WHERE K_Code = @K_Code;
    OPEN HamrahFetch;
    FETCH NEXT FROM HamrahFetch
    INTO @HK_Code,
         @HK_Qty,
         @EditQty;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @KindFlag = '1'
            SET @SendRecive = @Sender;
        ELSE
            SET @SendRecive = @Reciver;
        SELECT @Active = Active_Flag
        FROM Anbar
        WHERE K_Code = @HK_Code
              AND A_Code = @SendRecive;
        IF @Active = 0
        BEGIN
            SELECT @Exp = Exp_Flag,
                   @Serial = Serial_flag,
                   @Point = Point_Flag,
                   @Make = Make_Flag,
                   @Hamrah = Hamrah_Flag,
                   @Price_Forosh = Price_Forosh,
                   @Darou_Flag = Darou_Flag,
                   @Price = Price,
                   @BimarPercent = BimarPercent
            FROM
            (
                SELECT KalaId.K_Code,
                       KalaId.Name1,
                       KalaId.Name2,
                       Anbar.A_Code,
                       Anbar.Mojodi,
                       KalaId.Exp_Flag,
                       KalaId.Serial_flag,
                       KalaId.Point_Flag,
                       KalaId.Make_Flag,
                       KalaId.Hamrah_Flag,
                       KalaId.Barcode,
                       Anbar.Price_Forosh,
                       (
                           SELECT ISNULL(BimarPercent, 0)
                           FROM Price_Sazman
                           WHERE Sazman_Code = @Sazman_Code
                                 AND Price_Sazman.K_Code = KalaId.K_Code
                       ) BimarPercent,
                       CASE
                           WHEN KalaId.Darou_Flag <> '6' THEN
                               KalaId.Darou_Flag
                           WHEN KalaId.Darou_Flag IN ( '6', '4', '9' ) THEN
                           (
                               SELECT ISNULL(Price_Sazman.Status, 0)
                               FROM Price_Sazman
                               WHERE Sazman_Code = @Sazman_Code
                                     AND Price_Sazman.K_Code = KalaId.K_Code
                           )
                       END AS Darou_Flag,
                       CASE
                           WHEN KalaId.Darou_Flag <> '6' THEN
                               0
                           WHEN KalaId.Darou_Flag IN ( '4', '6', '9' ) THEN
                           (
                               SELECT ISNULL(Price_Sazman.Price, 0)
                               FROM Price_Sazman
                               WHERE Sazman_Code = @Sazman_Code
                                     AND Price_Sazman.K_Code = KalaId.K_Code
                                     AND Status IN ( '4', '6', '9' )
                           )
                       END AS Price,
                       KalaId.Meli_Code
                FROM KalaId
                    INNER JOIN Anbar
                        ON KalaId.K_Code = Anbar.K_Code
                WHERE (Anbar.Active_Flag = '0')
                      AND (Anbar.A_Code = @SendRecive)
            ) PublicTable
            WHERE K_Code = @HK_Code;
            IF @BimarPercent IS NULL
               OR @BimarPercent = ''
                SET @BimarPercent = @CashBimarpercent;

            IF @EditQty = 1
                SET @HK_Qty = @HK_Qty * @K_Qty;

            EXEC @CtrMojodi = ChkCtrMojodi @HK_Code, @SendRecive, 0;

            IF (@Darou_Flag IS NULL)
               OR (@Darou_Flag = '')
                SET @Darou_Flag = '0';

            ----- here 		
            IF @Price_Forosh < @Price
                SET @Price_Forosh = @Price;

            SET @StatusForosh = @Price_Forosh;


            SET @SumDiffer = 0;
            SET @StatusField = '';

            IF (@Sazman_Code = '01')
               AND (@Darou_Flag <> '1')
               AND (@Darou_Flag <> '2')
               AND (@Darou_Flag <> '6')
                SET @StatusField = '0';
            ELSE IF (@Sazman_Code = '02')
                    AND (@Darou_Flag <> '1')
                    AND (@Darou_Flag <> '2')
                    AND (@Darou_Flag <> '6')
                SET @StatusField = '3';
            ELSE IF (
                        (@Sazman_Code = '01')
                        OR (@Sazman_Code = '02')
                    )
                    AND
                    (
                        (@Darou_Flag = '1')
                        OR (@Darou_Flag = '2')
                    )
                SET @StatusField = @Darou_Flag;

            ELSE
            BEGIN
                EXEC @Farma = Sp_SetFarmaCope @HK_Code, @Sazman_Code, @Grade_Code, '';
                SELECT @S_PriceSazman = Status
                FROM Price_Sazman
                WHERE Sazman_Code = @Sazman_Code
                      AND K_Code = @HK_Code;
                IF @BimarPercent = 0
                    SET @BimarPercent = @CashBimarpercent;

                IF (@Farma <> '9')
                BEGIN

                    IF @Farma = '5'
                    BEGIN
                        IF @Darou_Flag = '0'
                            SET @StatusField = '5';
                        ELSE
                        BEGIN
                            SET @StatusMainPrice = 0;
                            IF @Darou_Flag <> '6'
                                SET @StatusField = @Darou_Flag;
                            ELSE
                            BEGIN
                                IF (@Price <> 0)
                                   AND (@Price < @StatusForosh)
                                BEGIN
                                    SET @StatusField = @S_PriceSazman;
                                    IF (@S_PriceSazman = '0' OR @S_PriceSazman = '3')
                                        SET @StatusMainPrice = 0;
                                    ELSE
                                        SET @StatusMainPrice = @StatusForosh;
                                    SET @PriceSazman = @Price;
                                    IF @PriceSazman > @StatusForosh
                                        SET @PriceSazman = 0;
                                    SET @StatusForosh = @PriceSazman;
                                    IF @PriceSazman <> 0
                                    BEGIN
                                        SET @SumDiffer = (@HK_Qty * @StatusMainPrice) - (@HK_Qty * @PriceSazman);
                                        IF @BimarPercent = 0
                                            SET @BimarPercent = @CashBimarpercent;
                                    END;
                                    ELSE
                                    BEGIN
                                        SET @SumDiffer = 0;
                                        SET @StatusForosh = @Price_Forosh;
                                    END;
                                END;
                            END;
                        END;
                    END;
                    ELSE
                        SET @StatusField = '0';
                END;
                ELSE
                BEGIN
                    SET @StatusMainPrice = 0;
                    IF @Darou_Flag <> '6'
                    BEGIN
                        ---Here 
                        IF @S_PriceSazman IN ( 4, 9 )
                           AND @Price_Forosh > @Price
                        BEGIN
                            SET @SumDiffer = @HK_Qty * (@Price_Forosh - @Price);
                            SET @StatusForosh = @Price;
                            SET @StatusMainPrice = @Price_Forosh;
                        END;

                        SET @StatusField = @Darou_Flag;
                    END;
                    ELSE
                    BEGIN
                        SET @StatusField = @S_PriceSazman;
                        IF (@Price <> 0)
                           AND (@Price < @StatusForosh)
                        BEGIN

                            --here		
                            IF @StatusForosh < @Price
                                SET @StatusForosh = @Price;


                            SET @StatusField = @S_PriceSazman;
                            IF (@S_PriceSazman = '0' OR @S_PriceSazman = '3')
                                SET @StatusMainPrice = 0;
                            ELSE
                                SET @StatusMainPrice = @StatusForosh;
                            SET @PriceSazman = @Price;

                            IF @PriceSazman > @StatusForosh
                                SET @PriceSazman = 0;
                            SET @StatusForosh = @PriceSazman;

                            IF @PriceSazman <> 0
                            BEGIN
                                SET @SumDiffer = (@HK_Qty * @StatusMainPrice) - (@HK_Qty * @PriceSazman);
                                IF @BimarPercent = 0
                                    SET @BimarPercent = @CashBimarpercent;
                            END;
                            ELSE
                            BEGIN
                                SET @SumDiffer = 0;
                                SET @StatusForosh = @Price_Forosh;
                            END;
                        END;
                    END;

                END;
            END;

            IF @StatusField IN ( '0', '1', '3', '4', '8', '' )
                SET @BimarPercent = 0;

            SET @Radif = @Radif + 1;
            IF (@StatusMainField = '0')
               AND
               (
                   @StatusField = '5'
                   OR @StatusField = '6'
               )
                SET @StatusField = '0';

            EXEC Add_One_Kala_Drug @msg_num,
                                   @IdHavaleh,
                                   @Sender,
                                   @Reciver,
                                   @HK_Code,
                                   @HK_Qty,
                                   @StatusField,
                                   @StatusForosh,
                                   @StatusMainPrice,
                                   @SumDiffer,
                                   @Exp,
                                   @Point,
                                   @Serial,
                                   @Takhfif,
                                   @Hamrah,
                                   @Make,
                                   @CtrMojodi,
                                   @Radif,
                                   @Autoid,
                                   @BimarPercent;
        END;
        FETCH NEXT FROM HamrahFetch
        INTO @HK_Code,
             @HK_Qty,
             @EditQty;
    END;
    CLOSE HamrahFetch;
    DEALLOCATE HamrahFetch;
END;