-- exec p_wlw_jcjl 'DXGS-A5-LHSD-XJ2','','','','2019-04-11,'2019-04-11','0'
ALTER procedure [dbo].[p_wlw_jcjl]
(@sdbh varchar(20),@empbm varchar(20),@empgz varchar(20),@empkh varchar(20),@bdate varchar(20),@edate varchar(20),@dn int, @page int, @limit int ) --, @rsCount int OUT
as
begin
create table #emp(
sd_bh varchar(20),
emp_kh varchar(20),
emp_xm varchar(10),
emp_xb varchar(2),
emp_bm varchar(50),
emp_zw varchar(50),
emp_gz varchar(50),
emp_dh varchar(50)
)
declare @empfilter varchar(500)
set @empfilter=' 1=1 '
/*if (@sdbh != '')*/ set @empfilter=@empfilter+' and sd_bh='''+@sdbh+''''
if (@empbm != '') set @empfilter=@empfilter+' and emp_bm='''+@empbm+''''
if (@empgz != '') set @empfilter=@empfilter+' and emp_gz='''+@empgz+''''
if (@empkh != '') set @empfilter=@empfilter+' and emp_kh='''+@empkh+''''
declare @sql varchar(1000)
set @sql='insert into #emp(emp_kh,emp_xm,emp_xb,emp_bm,emp_zw,emp_gz,emp_dh) select emp_kh,emp_xm,emp_xb,emp_bm,emp_zw,emp_gz,emp_dh from d_wlw_empinfo where '+@empfilter
print @sql
exec (@sql)
create table #path(
path_id int,
sd_bh varchar(20),
emp_kh varchar(20),
start_date varchar(20),
end_date varchar(20)
)
declare @pathfilter varchar(500)
set @pathfilter=' 1=1 '
/*if (@sdbh != '') */ set @pathfilter=@pathfilter+' and sd_bh='''+@sdbh+''''
if (@bdate != '') set @pathfilter=@pathfilter+' and left(emp_cjsj,10)>='''+@bdate+''''
if (@edate != '') set @pathfilter=@pathfilter+' and left(emp_cjsj,10)<='''+@edate+''''
if (@empkh != '') set @pathfilter=@pathfilter+' and emp_kh='''+@empkh+''''
set @sql='insert into #path(path_id,sd_bh, emp_kh,start_date,end_date) '
set @sql=@sql+' SELECT M.path_id, M.sd_bh, M.emp_kh, M.start_date, D.end_date'+
' FROM (
SELECT path_id, sd_bh, emp_kh, emp_cjsj AS start_date, row_number() OVER(PARTITION BY emp_kh ORDER BY path_id) AS order_id
FROM d_wlw_rydwpath
WHERE '+@pathfilter+ ' AND dnw_flag = 1
) AS M
left outer join (
SELECT path_id, sd_bh, emp_kh, emp_cjsj AS end_date, row_number() OVER(PARTITION BY emp_kh ORDER BY path_id) AS order_id
FROM d_wlw_rydwpath
WHERE '+@pathfilter+ 'AND dnw_flag = 0
) AS D ON D.emp_kh = M.emp_kh AND D.order_id = M.order_id
ORDER BY M.emp_kh'
exec (@sql)
declare @strSql nvarchar(max)
declare @rsCount int
if (@dn=0)
begin
SET @strSql = 'SELECT TOP '+STR(@limit)+' emp_kh, emp_xm, emp_xb, emp_bm, emp_zw, emp_gz, emp_dh, start_date, end_date
FROM(
SELECT
ROW_NUMBER () OVER (ORDER BY M.start_date, M.emp_kh) PAGE_ROW_NUMBER,
M.emp_kh,E.emp_xm,E.emp_xb,E.emp_bm,E.emp_zw,E.emp_gz,E.emp_dh,M.start_date,M.end_date
FROM #path M
INNER JOIN #emp E on E.emp_kh=M.emp_kh
) AS PAGE_TABLE_ALIAS
WHERE
PAGE_ROW_NUMBER > '+STR((@page-1)*@limit+1)+'
ORDER BY
PAGE_ROW_NUMBER
'
print @strSql
--exec (@strSql)
SET @strSql = '
SELECT @total=COUNT(*) FROM (
SELECT
M.emp_kh,E.emp_xm,E.emp_xb,E.emp_bm,E.emp_zw,E.emp_gz,E.emp_dh,M.start_date,M.end_date
FROM #path M
INNER JOIN #emp E on E.emp_kh=M.emp_kh
) AS T
'
print @strSql
--exec sp_executesql @strSql,N'@total int out',@total=@rsCount output
select @rsCount=10
print @rsCount
end
else
begin
select M.emp_kh,E.emp_xm,E.emp_xb,E.emp_bm,E.emp_zw,E.emp_gz,E.emp_dh,M.start_date,M.end_date
from #path M
inner join #emp E on E.emp_kh=M.emp_kh
where M.end_date is null
order by M.start_date,M.emp_kh
end
-- EXEC (@strSql)
end