create TRIGGER [dbo].[Ti_DrugHavaleh_Parvande] ON [dbo].[DrugHavaleh] 
FOR INSERT
AS

Declare @ParId Numeric
Declare @ParNo Varchar(9)
Declare @KCode Varchar(15)
Declare @KQty Money
Declare @Tahvil Money
Declare @SumKQty Int
Declare @ReferStep Tinyint
Declare @RowCount Smallint
Declare @PriceForosh Money
Declare @KindFlag Char(1)
Declare @KindSave Char(1)
Declare @RowDate   SmallDateTime
Declare @TahvilDate SmallDateTime
Declare @AutoId     BigInt
Declare @iCount     Smallint
Declare @Type_Par  Smallint   
DECLARE @UserId Smallint

Select @ParNo = Tbl_Hparvande.Par_No, @ParId = Tbl_HParvande.Par_Id, @KindFlag = DrugH.Kind_Flag , @KindSave = DrugH.Kind_Save, @UserId = DrugH.User_Id From DrugH Inner Join Inserted 
                    On(DrugH.Id_Havaleh = Inserted.Id_Havaleh)
                    Inner Join Tbl_HParvande On (DrugH.Par_No  = Tbl_Hparvande.Par_No)
                    Group By Tbl_Hparvande.Par_No, Tbl_HParvande.Par_Id, DrugH.Kind_Flag, DrugH.Kind_Save, Drugh.User_id 

 Select @Type_Par = Sum(IsNull(refer_day,0)) From tbl_DParvande 
 inner join KalaId on Kalaid.K_Code= tbl_DParvande.K_Code
 Where Parvande_Flag = '1' And Par_Id = @ParId

 IF (@ParNo  Is Not Null) and (@ParNo <> '')  And (@KindFlag = '1') And (@Type_Par = 0) 
 Begin
    Declare Cursor_Select Cursor Local For

    Select Table1.K_Code , Table2.SumKQty From (
    Select K_Code From Tbl_DParvande
    Group By K_Code) As Table1 
    Inner Join (
                         Select Sum(K_Qty1) SumKQty , K_Code From Inserted
                         Group By K_Code) As Table2
                         On (Table1.K_Code = Table2.K_Code)

    Open Cursor_Select
    Fetch Next From Cursor_Select Into @KCode, @SumKQty
    While @@Fetch_Status  = 0 
    Begin
        /*########################################################################################################*/
        IF @KindSave <> '3' 
        Begin
            Declare Cursor_Update Cursor Local For
            Select IsNull(Tbl_DParvande.K_Qty,0), IsNull(Tbl_DParvande.Tahvil,0),  Tbl_DParvande.Price_Forosh,
            Tbl_DParvande.AutoId, Tbl_DParvande.Refer_Step From Tbl_DParvande Inner Join Inserted On (Tbl_DParvande.K_Code = Inserted.K_Code)     
            Where Tbl_DParvande.K_Code = @KCode And (Tbl_DParvande.K_Qty <> Tbl_DParvande.Tahvil) And Par_Id = @ParId
            And  Tbl_DParvande.K_Qty > 0   And (Tahvil_Date = '' Or Tahvil_Date Is Null Or Convert(Varchar(10), Tahvil_Date, 111) = Convert(Varchar(10), GetDate(),111 ))
            Order By Tahvil_Date, Refer_Step
            Open Cursor_Update
            Fetch Next From Cursor_Update Into @KQty, @Tahvil , @PriceForosh, @AutoId, @ReferStep
            While @@Fetch_Status = 0 
            Begin
                IF @SumKQty - (@KQty - @Tahvil) >= 0 
                Begin
                    Update Tbl_DParvande Set Tahvil =  Tahvil + (@Kqty - @Tahvil) , Tahvil_Date  = GetDate() Where AutoId = @AutoId
                    Set @SumKQty = @SumKQty - (@KQty - @Tahvil)
                End
                Else
                Begin
                    Update Tbl_DParvande Set Tahvil =Tahvil + @SumKQty , Tahvil_Date  = GetDate()  Where AutoId = @AutoId
                    Set @SumKQty  = -1
                    Break
                End
                Fetch Next From Cursor_Update Into @KQty, @Tahvil ,@PriceForosh, @AutoId,@ReferStep
            End 
             ----------------------------------------------(در صورت باقي ماندن تعداد تحويلي  يك ركورد به پرونده اضافه شود)--------------------------------------
            IF  @SumKQty > 0 
            Begin
                 Select @AutoId = AutoId From Tbl_Dparvande          
                                                 Where  Convert(Varchar(10), Row_Date ,111) =  Convert(Varchar(10), GetDate(), 111) 
                                                 And K_Code = @KCode And Par_Id = @ParId 

                 IF (@AutoId Is Null Or @AutoId = '')
                 Begin
                     Select @RowCount = Max(IsNull(Row_No,0)) + 1 From Tbl_DParvande  Where Par_Id = @ParId
                     If @RowCount is null or @RowCount = ''
                         Set @RowCount = 1
                    Insert Into Tbl_DParvande (Par_Id, Row_No, K_Code, K_Qty, Tahvil, Tahvil_Date, Price_Forosh, User_Id)
                                                    Values(@ParId, @RowCount, @KCode, 0, @SumKqty,  Getdate(), @PriceForosh ,@UserId)
                End
                Else
                    Update Tbl_DParvande Set Tahvil =Tahvil + @SumKQty , Tahvil_Date  = GetDate() 
                    Where AutoId = @AutoId                   
            End
              ----------------------------------------------------------------------------------------------------------------------------------------------------------------
             Close Cursor_Update
             Deallocate Cursor_Update
        End --  IF @KindSave <> '3' 
        /*########################################################################################################*/
        Else 
        Begin
            Select @PriceForosh = Price_Forosh From Inserted Where K_Code = @KCode
            Select @iCount = Count(K_Code) From DrugHavaleh Inner Join DrugH 
                                            On  DrugH.Id_Havaleh = DrugHavaleh.Id_Havaleh
                                            Where DrugH.Par_No = @ParNo And DrugHavaleh.K_Code = @KCode    
            

            Select @AutoId = AutoId From Tbl_Dparvande          
                                            Where  Convert(Varchar(10), Row_Date ,111) =  Convert(Varchar(10), GetDate(), 111) 
                                             And K_Code = @KCode And Par_Id = @ParId 

            IF (@AutoId Is Null Or @AutoId = '')
            Begin 
                 Select @RowCount = Max(IsNull(Row_No,0)) + 1 From Tbl_DParvande  Where Par_Id = @ParId
                 If @RowCount is null or @RowCount = ''
                   Set @RowCount = 1
                 Select @ReferStep = Count(Row_Date) + 1 From (
                                                        Select Convert(Varchar(10), Row_Date ,111) Row_Date From Tbl_DParvande 
                                                         Where Convert(Varchar(10), Row_Date ,111) <>  Convert(Varchar(10), GetDate(), 111)  
                                                          And Refer_Step <> 0 And Par_Id = @ParId
                                                          Group By Convert(Varchar(10), Row_Date ,111)) As Table1

                 Insert Into Tbl_DParvande (Par_Id, Row_No, K_Code, Tahvil, K_Qty, Row_Date, Price_Forosh, Refer_Step, User_Id)
                                    Values(@ParId, @RowCount, @KCode, 0, @SumKqty,  Getdate(), @PriceForosh, @ReferStep, @UserId)
            End
            Else
            Begin
                  IF @iCount > 1 
                      Update Tbl_DParvande Set K_Qty =K_Qty + @SumKQty  Where AutoId = @AutoId
                  Else
                      Update Tbl_DParvande Set K_Qty =@SumKQty  Where AutoId = @AutoId
            End
            ----------------------------------------------------------------------------------------------------------------------------------------------------------------
        End --  IF @KindSave = '3' 
        /*########################################################################################################*/
        Fetch Next From Cursor_Select Into @KCode, @SumKQty
    End
    Close Cursor_Select
    Deallocate Cursor_Select

  -----------------------------------(در صورتي كه اقلام موجود در پذيرش در پرونده موجود نباشد به پرونده اضافه شود)-----------------------------------------
    Select @RowCount = Max(IsNull(Row_No,0)) + 1 From Tbl_DParvande  Where Par_Id = @ParId

    If @RowCount is null or @RowCount = ''
      Set @RowCount = 1

   IF @KindSave = '3'
   Begin
        Select @ReferStep = Count(Row_Date) + 1 From (
                                              Select Convert(Varchar(10), Row_Date ,111) Row_Date From Tbl_DParvande 
                                              Where Convert(Varchar(10), Row_Date ,111) <>  Convert(Varchar(10), GetDate(), 111)  
                                              And Refer_Step <> 0 And Par_Id = @ParId
                                              Group By Convert(Varchar(10), Row_Date ,111)) As Table1
       
       Insert Into Tbl_DParvande (Par_Id, Row_No, K_Code, Tahvil, K_Qty, Refer_Step, Price_Forosh, User_Id)
       Select ParId, RowId, K_Code, KQty , SumKQty, @ReferStep, Price_Forosh,@UserId From (
       Select @ParId as ParId, @RowCount As RowId , K_Code, 0 As KQty, Sum(K_Qty1) SumKQty, Price_Forosh   From Inserted 
       Group By K_Code, Price_Forosh) As Table1  Where Not Exists(Select K_Code  From Tbl_DParvande Where K_Code = Table1.K_Code
       And Tbl_DParvande.Par_Id = @ParId )
   End
   Else IF @KindSave <> '3'
   Begin
       Insert Into Tbl_DParvande (Par_Id, Row_No, K_Code, Tahvil, K_Qty, Tahvil_Date, Price_Forosh,User_Id)
       Select ParId, RowId, K_Code, SumKQty, KQty ,  Convert(Varchar(10), GetDate() , 111), Price_Forosh,@UserId From (
       Select @ParId as ParId, @RowCount As RowId , K_Code,  Sum(K_Qty1) SumKQty,  0 As KQty, Price_Forosh From Inserted 
       Group By K_Code, Price_Forosh) As Table1  Where Not Exists(Select K_Code  From Tbl_DParvande Where K_Code = Table1.K_Code
       And Tbl_DParvande.Par_Id = @ParId ) 
  End
End -- IF (@ParNo  Is Not Null) and (@ParNo <> '')  And (@KindFlag = '1') And (@KindSave <> '3')
