CREATE PROCEDURE sp_Auto_Calc_Rows_Matab
	@Sazman_Code varchar(3), @Start_Date DateTime , @End_Date DateTime , 
	@stYears varchar(200)  , @Conditions char(1)  , @Grade  char(1), @Nezam_No nvarchar(10),
	@PT_Code varchar(3) 
AS

declare @stYear  as varchar(15)

declare @stCommAnd    varchar(8000)
declare @stCondition  varchar(250) 

Set @stCommAnd = 
'Declare @Id_Havaleh Numeric(9) '+
'Declare @iRow_Index Int '+
'Declare @stUpdate_Year varchar(20) '+
'Declare @Nezam_No nVarchar(10) '+
'Declare @Tarikh smallDateTime ' +
'Declare @Meli_Code Char(1) ' +
'Set @iRow_Index = 1  '+
'Declare cur_List_bime Cursor '+
'For '
While CHARINDEX(',' , @stYears) <> 0
begin
   --print @stYears  
    Set  @stYear  = SubString(@stYears ,0 , CHARINDEX(',' , @stYears))
	if @Conditions = 0  
	   Set  @stCondition = ''
	else
	if @Conditions = 1  
	     Set  @stCondition  = '  And  '+@stYear+'.Grade.Status = '+ @Grade
	else
	if @Conditions = 2  
	     Set  @stCondition = '  And  '+@stYear+'.Grade.Status != '+ @Grade
	else
	if @Conditions = 3
	     Set  @stCondition = '  And  '+@stYear+'.DrugH.Nezam_No = '''+ @Nezam_No+''''
	
	if @PT_Code <> '' 
	     Set  @stCondition = @stCondition +  '  And  '+@stYear+'.DrugH.Pt_Code = '+ @PT_Code
 
 set @stCommAnd = @stCommAnd +
	'Select '+@stYear+'.DrugH.Id_Havaleh ,'''+@stYear+''','++@stYear+'.DrugH.Nezam_No ,'+@stYear+'.DrugH.Tarikh,'+
	'Meli_Code = Case When '+@stYear+'.KalaId.Meli_Code  in (''000995'' , ''000996'' , ''000997'' , ''000998'' , ''000999'') then 1 else  2 End '+
	'From '+@stYear+'.DrugH '+
	'Inner Join '+@stYear+'.DrugHavaleh On '+@stYear+'.DrugHavaleh.Id_Havaleh = '+@stYear+'.DrugH.Id_Havaleh '  +
    'Inner Join '+@stYear+'.KalaId      On '+@stYear+'.KalaId.K_Code          = '+@stYear+'.DrugHavaleh.K_Code '+
	'Inner Join '+@stYear+'.Doctor      On '+@stYear+'.Doctor.Nezam_No        = '+@stYear+'.DrugH.Nezam_No '    +
	'Inner Join '+@stYear+'.Sahmiyeh    On '+@stYear+'.Sahmiyeh.Sazman_Code   = '+@stYear+'.DrugH.Sazman_Code ' +
	'Inner Join '+@stYear+'.Grade       On '+@stYear+'.DrugH.Grade_Code       = '+@stYear+'.Grade.Grade_Code '  +	
	'Where '+@stYear+'.DrugH.TotPriceSazman > 0 And '+@stYear+'.DrugH.Tarikh_Noskheh Between '''+Convert(Varchar(10),@Start_Date,111) +
	''' And '''+Convert(Varchar(10),@End_Date,111 ) +
	''' And '+@stYear+'.DrugH.Kind_Flag = 1 And '+@stYear+'.DrugH.Del_Flag = 1 And '+@stYear+'.DrugH.Sazman_Code='''+@Sazman_Code +'''' + @stCondition  

 Set @stYears = SubString(@stYears , Len(@stYear)+2 , Len(@stYears) - Len(@stYear)-1)
 if (LEN(@stYears) > 0 )
  Set @stCommAnd = @stCommAnd + ' UNION '
end 
 Set @stCommAnd = @stCommAnd + 'Order By Meli_Code ,DrugH.Nezam_No , DrugH.Tarikh '
 Set @stCommAnd  = @stCommAnd  + 
								'Open cur_List_Bime  '+
								'Fetch Next From cur_List_Bime '+
								'Into @id_Havaleh , @stUpdate_Year ,@Nezam_No , @Tarikh , @Meli_Code '+
								'While @@FETCH_STATUS = 0  '+
								'Begin '+
								  'Exec dbo.sp_UpdateRow  @stUpdate_Year, @Id_Havaleh , @iRow_Index '+
								  'Set @iRow_Index = @iRow_Index + 1 '+
								  'Fetch Next From cur_List_Bime '+
								  'Into @id_Havaleh , @stUpdate_Year ,@Nezam_No , @Tarikh , @Meli_Code '+ 
								'End '+
								'Close cur_List_Bime '+
								'Deallocate  cur_List_Bime '+
								'print Cast(@iRow_Index-1  as varchar(4)) ' 
Execute (@stCommAnd)

