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)
    -----------------------------------------------------------------------------------------------------------------------------
   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  
   
     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
       -- در صورت عوض شدن بايد تعداد همراه به همان نسبت کالاي اصلي اضافه شود
       -- 1=تعداد قابل عوض شدن است      
       -- 0=تعداد ثابت است
       ------------------------------------------------------------------------------------------------
       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 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,
         Case
            When KalaId.Darou_Flag <> '6' then Kalaid.Darou_Flag
            When KalaId.Darou_Flag = '6'  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 = '6'  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 = '6' )
         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 @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'
         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 (@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 
                         Set @SumDiffer  = (@HK_Qty * @StatusMainPrice) - (@HK_Qty * @PriceSazman)
                       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' 
                   Set @StatusField = @Darou_Flag
                 Else
                 Begin
                    Set @StatusField      = @S_PriceSazman
                   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 
                       Set @SumDiffer  = (@HK_Qty * @StatusMainPrice) - (@HK_Qty * @PriceSazman)
                     Else
                     Begin
                       Set @SumDiffer     = 0
                       Set @StatusForosh  = @Price_Forosh
                     End
                   End
                 End
             End
           End
          ------------------------------------------------------------------------------------------------
    
           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
        End
        Fetch Next From HamrahFetch  Into @HK_Code , @HK_Qty, @EditQty
     End
     ----------------------------------------------------------------------------------------------------------------------------
     CLOSE HamrahFetch
     DEALLOCATE HamrahFetch
End
