CREATE PROCEDURE [dbo].[sp_CreateParByReferDay] @tmpIdHavaleh  Numeric(18,0)
As 
DECLARE @Par_Id Numeric(9)
DECLARE @Kind_Flag Char(1)
DECLARE @Par_No Varchar(12)
DECLARE @K_Code Varchar(15)
DECLARE @K_Qty  MONEY
DECLARE @UserId SMALLINT

Select @Par_No = Par_No , @Kind_Flag = Kind_Flag , @UserId  = User_id
From TmpDrugH 
Where Id_Havaleh = @tmpIdHavaleh

if Not Exists(Select Par_No From tbl_HParvande Where Par_No = @Par_No )
AND 
( (Select SUM(ISNULL(Refer_Day,0))  From TmpDrugHavaleh Where Id_Havaleh = @tmpIdHavaleh and ISNULL(Refer_Day,0) > 0 ) > 0  )  
Begin
    if(@Kind_Flag = 1)
    Begin
		INSERT INTO tbl_HParvande 
		( Par_No, PT_Code, Sazman_Code, Note_Code,Family,Name ,Nezam_No , Par_Status, User_Id)
		SELECT Par_No ,ISNULL(PT_Code ,'0') , Sazman_Code , Note_Code, Resive_Name , ' '  ,Nezam_No , '0', @UserId
		FROM     TmpDrugH
		WHERE Id_Havaleh = @tmpIdHavaleh

		SELECT  @Par_Id = @@IDENTITY

		INSERT INTO tbl_DParvande
		(Par_Id, Row_No, K_Code, K_Qty, Price_Forosh , Refer_Day ,Refer_Step, Tahvil_Date, Tahvil, User_Id )
		SELECT @Par_Id , Radif, TmpDrugHavaleh.K_Code, K_Qty1, Price_Forosh ,Refer_Day ,  1, GETDATE(), K_Qty1,@UserId
		FROM         TmpDrugHavaleh
		Inner Join KalaId On KalaId.K_Code = TmpDrugHavaleh.K_Code And KalaId.Parvande_Flag = 1
		WHERE Id_Havaleh = @tmpIdHavaleh
   End
End
else
Begin
	Select @Par_Id  = Par_id 
	From tbl_HParvande
	Where Par_No = (Select Par_No From TmpDrugH Where Id_Havaleh = @tmpIdHavaleh)
	
    if(@Kind_Flag = 1)
    Begin
		INSERT INTO tbl_DParvande
		(Par_Id, Row_No, K_Code, K_Qty, Price_Forosh , Refer_Day ,Refer_Step, Tahvil_Date, Tahvil, User_Id )
		SELECT @Par_Id , Radif, TmpDrugHavaleh.K_Code, K_Qty1, Price_Forosh ,Refer_Day ,
		 ( 	   Select MAX(Refer_Step)+1 from tbl_DParvande where Par_Id = @Par_Id) , GETDATE(), K_Qty1, @UserId
		FROM  TmpDrugHavaleh
		Inner Join KalaId On KalaId.K_Code = TmpDrugHavaleh.K_Code AND KalaId.Parvande_Flag = 1
		WHERE Id_Havaleh = @tmpIdHavaleh
	end
	if(@Kind_Flag = 2)
	Begin
	  
		DECLARE Par_Cursor CURSOR FOR
		Select tmpDrugHavaleh.K_Code , K_Qty1 ,@Par_Id 
		From TmpDrugH
		Inner Join TmpDrugHavaleh On TmpDrugHavaleh.Id_Havaleh = tmpDrugH.Id_Havaleh
		Inner join KalaId on TmpDrugHavaleh.K_Code = Kalaid.K_Code
		Where KalaId.Parvande_Flag = 1 and TmpDrugH.Id_Havaleh = @tmpIdHavaleh
		Order By tmpDrugHavaleh.K_Code

		OPEN Par_Cursor
		FETCH NEXT FROM Par_Cursor
		Into @K_Code , @K_Qty , @Par_Id
		WHILE @@FETCH_STATUS = 0
		BEGIN
		   
		   Update tbl_DParvande
		   Set Tahvil = ( Tahvil - @K_Qty )
		   Where AutoId = 
		   (
			   Select Max(AutoId)
			   From tbl_DParvande
			   Where Par_Id = @Par_Id and K_Code = @K_Code
			   Group By K_Code
		   )
		   
		   FETCH NEXT FROM Par_Cursor
		   Into @K_Code , @K_Qty , @Par_Id
		END

		CLOSE Par_Cursor
		DEALLOCATE Par_Cursor		
	End
End

Update tbl_HParvande
Set Mobile = Case When (ISNULL(tbl_HParvande.Mobile,'0') = '0' Or tbl_HParvande.Mobile = '' ) then  Notebimeh.Mobile end
From tbl_HParvande 
Inner Join Notebimeh On Notebimeh.Note_Code = tbl_HParvande.Note_Code and tbl_HParvande.Sazman_Code = Notebimeh.Sazman_Code
Where tbl_HParvande.Par_Id = @Par_Id


