CREATE PROCEDURE sp_get_Shosp_for_scheduler_Editor_job_allocation
@intPageNo numeric,
@intpageSize numeric,
@search as varchar(8000),
@search1 as varchar(8000)
AS
create table #tbl (id numeric identity,shop_id numeric, 
shop_title varchar(120), 
program_id numeric, 
program_name varchar(120), 
start_Date datetime, 
end_Date datetime, 
Target_Date datetime,
location varchar(120),
client varchar(120),
client_id numeric,
Scheduler varchar(120),
Editor varchar(120),
msp_employee_id numeric,
location_id numeric,
Shop_order numeric)

Create table #tbl1 (id numeric identity,
shop_id numeric, 
shop_title varchar(120), 
program_id numeric, 
program_name varchar(120), 
shop_start_Date datetime, 
shop_end_Date datetime, 
Target_Date datetime,
location varchar(120),
client_name varchar(120),
client_id numeric,
assigned_Scheduler varchar(120),
assigned_Editor varchar(120),
sche_msp_employee_id numeric,
editor_msp_employee_id numeric,
location_id numeric,
Shop_order numeric)

declare @rows as numeric
declare @str as varchar(8000) 
set @str='insert into #tbl(shop_id, 
shop_title,
Shop_order, 
start_Date,
Target_Date, 
end_Date, 
program_id, 
program_name, 
location,
location_id, 
client,
client_id)
	select s.shop_id,
s.shop_title,
s.Shop_order,
s.shop_start_Date,
s.shop_end_Date,
p.program_end_Date,
p.program_id,
cast(p.program_id as varchar(20))+''/''+p.program_name,
cast(l.location_id as varchar(20)) +''/''+ l.shopping_mall,
l.location_id,
cast(c.client_id as varchar(20)) + c.client_company_name,
c.client_id
from tbl_shops s inner join tbl_programs  P 
	on P.program_id = s.program_id
inner join tbl_client_locations L
	on l.location_id = S.location_id
inner join tbl_client C
	on C.client_id = l.client_id
left join tbl_scheduler_shop SS
	on SS.shop_id = S.shop_id
left join tbl_editor_shop ES
	on ES.shop_id = S.shop_id
where ' +@search
execute( @str)
set @rows = @@rowcount
if @rows=0 and @search1 <>''
begin
	set @str='insert into #tbl(shop_id, 
shop_title, 
Shop_order,
start_Date,
Target_Date, 
end_Date, 
program_id, 
program_name, 
location,
location_id, 
client,
client_id)
select s.shop_id,
s.shop_title,
s.Shop_order
s.shop_start_Date,
s.shop_end_Date,
p.program_end_Date,
p.program_id,
cast(p.program_id as varchar(20))+''/''+p.program_name,
cast(l.location_id as varchar(20)) +''/''+ l.shopping_mall,
l.location_id,
cast(c.client_id as varchar(20)) + c.client_company_name,
c.client_id
from tbl_shops s inner join tbl_programs  P 
	on P.program_id = s.program_id
inner join tbl_client_locations L
	on l.location_id = S.location_id
inner join tbl_client C
	on C.client_id = l.client_id
left join tbl_scheduler_shop SS
	on SS.shop_id = S.shop_id
left join tbl_editor_shop ES
	on ES.shop_id = S.shop_id
where ' +@search1
	execute( @str)
	set @rows= @@rowcount
end
select @rows TOTAL_RECORDS
declare @firstRec integer
declare @lastRec integer 
set @firstRec = (@intPageNo - 1) * @intPageSize +1
set @lastRec = (@firstRec + @intPageSize)-1

if @intPageno=0 
begin 
	set @firstRec = 1
	set @lastRec = @rows
end

insert into #tbl1(shop_id ,
shop_title ,
Shop_order,
program_id ,
program_name , 
shop_start_Date ,
shop_end_Date , 
Target_Date ,
location ,
client_name,
client_id)
select shop_id ,
shop_title ,
Shop_order,
program_id ,
program_name , 
start_Date ,
end_Date , 
Target_Date,
location ,
client,
client_id
from #tbl where id >= @firstRec and id<=@lastRec

declare @sid as numeric
declare @name as varchar(200)
declare @id as numeric
declare  cor cursor for select shop_id from #tbl1 
open cor
fetch next from cor into @sid 
while @@fetch_status =0 
begin
	if(select count(*) from tbl_scheduler_shop where shop_id=@sid)>0
	begin
	select @id= msp_employee_id, @name = cast(msp_employee_id as varchar(20))+'/' + last_name from tbl_msp_employee 
		where msp_employee_id = (select msp_employee_id from tbl_scheduler_shop where shop_id=@sid)
	update #tbl1 set sche_msp_employee_id=@id, assigned_scheduler = @name where shop_id= @sid

	end
if(select count(*) from tbl_editor_shop where shop_id=@sid)>0
	begin
	select @id= msp_employee_id, @name = cast(msp_employee_id as varchar(20)) +'/'+ last_name from tbl_msp_employee 
		where msp_employee_id = (select msp_employee_id from tbl_editor_shop where shop_id=@sid)
	update #tbl1 set editor_msp_employee_id=@id,  assigned_editor = @name where shop_id= @sid
	end
	
	fetch next from cor into @sid 
end
close cor
deallocate cor
select * from #tbl1
drop table #tbl
drop table #tbl1
GO

update c 
  set assigned_scheduler = cast(a.msp_employee_id as varchar(20))+'/' + last_name 
from tbl_msp_employee a inner join tbl_scheduler_shop b
      on a.msp_employee_id = b.msp_employee_id
    inner join #tbl1 c 
      on a.msp_employee_id = c.sche_msp_employee_id
where (select count(*) from tbl_scheduler_shop where shop_id = b.shop_id) > 0