CREATE TRIGGER [dbo].[TI_OverUnder] ON  [dbo].[TmpDrugHavaleh]
   AFTER INSERT
AS 
BEGIN
  Declare @IdHavaleh Bigint
  Declare @Name Nvarchar(30)
  Declare @ActCode Smallint
  Declare @UserId Smallint
  Declare @Sender  Varchar(5)
  Declare @SazmanCode Varchar(3)
  Declare @CodeOverUnder Smallint
  Declare @KindFlag Char(1)
  Declare @Price Money
  Declare @SumForosh Money
  Declare @Percent Real
  Declare @Type Char(1)
  
  Select @IdHavaleh = Id_Havaleh, @Sender = Sender From Inserted Group By Id_Havaleh, Sender
  Select @UserId = User_Id, @SazmanCode = Sazman_Code, @KindFlag = Kind_Flag  From TmpDrugH Where Id_Havaleh = @IdHavaleh
  Select @CodeOverUnder = AutoId, @Name = Name, @ActCode = Act_Code From Over_Under Where AutoId = 28
  Select @SumForosh = Sum(K_Qty1 * Price_Forosh)  From TmpDrugHavaleh Where Id_Havaleh = @IdHavaleh
  IF @KindFlag = '1'
  Begin
    IF (Select Count(*) From TmpDrugHavaleh Where K_Code = '1' And Id_Havaleh = @IdHavaleh) = 1 
    Begin
      IF (@SazmanCode <> '01' And  @SazmanCode <> '02' And @SazmanCode <> '08')
      Begin
        Set @Price = 1700
        Set @type = '0'
      End
      Else IF @SazmanCode = '08'
      Begin
        Set @Price = 4000
        Set @type = '0'
      End
      Else
      Begin
        Set @Price = 11000
        Set @type = '0'
      End
      Set @Percent = (@Price * 100 /@SumForosh)
      Insert Into Tmp_Over_Under_Factor_Drug (Id_Havaleh , Name, Code_Over_Under, Type, Price, [Percent], Act_Code, Guy_Code)
                                       Values(@IdHavaleh, @Name, @CodeOverUnder, @Type, @Price, @Percent, @ActCode, @Sender)
    End                                   
    Else IF (Select Count(*) From TmpDrugHavaleh Where K_Code = '2' And Id_Havaleh = @IdHavaleh) = 1 
    Begin
      IF (@SazmanCode <> '01' And  @SazmanCode <> '02' And @SazmanCode <> '08')
      begin
        Set @Price = 1500
        Set @type = '1'
      end
      Else IF @SazmanCode = '08'
      Begin
        Set @Price = 5000
        Set @type = '1'
      End
      Else
      Begin
        Set @Price = 9000
        Set @type = '0'
      End
      Set @Percent = (@Price * 100 /@SumForosh)
      Insert Into Tmp_Over_Under_Factor_Drug (Id_Havaleh , Name, Code_Over_Under, Type, Price, [Percent], Act_Code, Guy_Code)
                                       Values(@IdHavaleh, @Name, @CodeOverUnder, @Type, @Price, @Percent, @ActCode, @Sender)
     
    End                                   
    Else IF (Select Count(*) From TmpDrugHavaleh Where K_Code = '3' And Id_Havaleh = @IdHavaleh) = 1 
    Begin
      IF (@SazmanCode <> '01' And  @SazmanCode <> '02' And @SazmanCode <> '08')
      begin
        Set @Price = 2100
        Set @type = '1'
      end
      Else IF @SazmanCode = '08'
      Begin
        Set @Price = 3000
        Set @type = '0'
      End
      Else
      Begin
        Set @Price = 8000
        Set @type = '0'
      End
      Set @Percent = (@Price * 100 /@SumForosh)
      Insert Into Tmp_Over_Under_Factor_Drug (Id_Havaleh , Name, Code_Over_Under, Type, Price, [Percent], Act_Code, Guy_Code)
                                       Values(@IdHavaleh, @Name, @CodeOverUnder, @Type, @Price, @Percent, @ActCode, @Sender)
    End                                   
  End  
END
