alter procedure [dbo].[_1010_template_gozaresh_koli]
-- By Alich NeedSoft 1399/04/22

-- exec _1010_template_gozaresh_koli '1390/03/01','1399/03/26','','','','','','','','','','','','','','','','','','','','','',''

(

@fday char(10),
@tday char(10),
@ftime char(20),
@ttime char(20),
@code_user int,
@code_shift	int,
@code_shobeh int,
@code_pay int,
@code_bimeh int,
@code_bakhsh int,
@code_pezeshk int,
@code_dastyar int,
@code_darman int,
@code_bimar int,
@code_ranandeh int,
@code_yaraneh int,
@code_mokammel	int,
@sh_kargozini varchar(20),
@code_ertebat	varchar(20),
@code_shift_str	varchar(500),
@code_bimeh_str	varchar(500),
@code_bakhsh_str varchar(500),
@code_pezeshk_str varchar(500),
@lucp int

)
as
begin

set @fday=RTRIM(LTRIM(@fday))
set @tday=RTRIM(LTRIM(@tday))
set @ftime=RTRIM(LTRIM(@ftime))
set @ttime=RTRIM(LTRIM(@ttime))
set @sh_kargozini=RTRIM(LTRIM(@sh_kargozini))
set @code_ertebat=RTRIM(LTRIM(@code_ertebat))
set @code_shift_str=RTRIM(LTRIM(@code_shift_str))
set @code_bimeh_str=RTRIM(LTRIM(@code_bimeh_str))
set @code_bakhsh_str=RTRIM(LTRIM(@code_bakhsh_str))
set @code_pezeshk_str=RTRIM(LTRIM(@code_pezeshk_str))




--  تعريف متغير ها
DECLARE @mywhere nvarchar(3000);
DECLARE @mystmt nvarchar(3000);
DECLARE @mygroup nvarchar(500);
DECLARE @myorder nvarchar(500);
DECLARE @myhaving nvarchar(500);



--  از حالت نال در اوردن 
set @mywhere =''
set @mystmt=''
set @mygroup=''
set @myorder=''
set @myhaving=''



--set @mywhere=' where 1> 0 ';
set @mywhere=' ';
	
	      
if(LEN(@fday)=10) and (LEN(@tday)=10) 
set @mywhere =@mywhere+' and tarikh between '''+@fday+''''+' and '+''''+@tday+''''

if(@code_bimar>0) 
set @mywhere =@mywhere+' and code_bimar='+ltrim(@code_bimar)

if(@code_bimeh>0) 
set @mywhere =@mywhere+' and code_bimeh='+ltrim(@code_bimeh)

  
if(@code_bakhsh>0) 
set @mywhere =@mywhere+' and code_bakhsh='+ltrim(@code_bakhsh)


if(@code_pezeshk>0) 
set @mywhere =@mywhere+' and code_pezeshk='+ltrim(@code_pezeshk)

if(@code_darman>0) 
set @mywhere =@mywhere+' and code_darman='+ltrim(@code_darman)


if(@code_shift>0) 
set @mywhere =@mywhere+' and code_shift='+ltrim(@code_shift)

if(@code_pay>0) 
set @mywhere =@mywhere+' and code_pay='+ltrim(@code_pay)


if(@code_shobeh>0) 
set @mywhere =@mywhere+' and code_shobeh='+ltrim(@code_shobeh)


if(LEN(@sh_kargozini)>0) 
set @mywhere =@mywhere+' and sh_kargozini='''+@sh_kargozini+''''


if(LEN(@code_ertebat)>0) 
set @mywhere =@mywhere+' and code_ertebat='''+@code_ertebat+''''

if(LEN(@code_shift_str)>0) 
set @mywhere =@mywhere+@code_shift_str


if(LEN(@code_bimeh_str)>0) 
set @mywhere =@mywhere+@code_bimeh_str


if(LEN(@code_bakhsh_str)>0) 
set @mywhere =@mywhere+@code_bakhsh_str



if(LEN(@code_pezeshk_str)>0) 
set @mywhere =@mywhere+@code_pezeshk_str


if(@code_yaraneh>0) 
set @mywhere =@mywhere+' and code_yaraneh='+ltrim(@code_yaraneh)



if(@code_mokammel>0) 
set @mywhere =@mywhere+' and code_mokammel='+ltrim(@code_mokammel)


if(@code_dastyar>0) 
set @mywhere =@mywhere+' and code_dastyar='+ltrim(@code_dastyar)

    
 
set @mygroup=' group by code_ertebat '
set @myorder=' Order by code_ertebat '



set @mystmt=
	' select code_bimar ,'+
	'(select name+famil from bimaran where code_bimar=m.code_bimar) as name_bimar,'+
	'(select sum(r_bimeh) from morajeat where  code_bimar=m.code_bimar and code_noskheh=0 ' +@mywhere+' group by code_bimar) as s_bimeh_noskheh0,'+
	'(select sum(r_bimeh) from morajeat where  code_bimar=m.code_bimar and code_noskheh<>0 '+@mywhere+' group by code_bimar) as s_bimeh_noskheh1,'+
	'(select sum(r_bimeh) from morajeat where  code_bimar=m.code_bimar '+@mywhere + ' ) as s_r_bimeh'+
	' from morajeat m where 1> 0 '+@mywhere+'   group by code_bimar '


    --  set @mystmt= @mystmt+@mywhere+@mygroup+@myorder

    --  select @mywhere

    --  select @mystmt

     exec (@mystmt)


END

