1,649
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE [wconf].[sp_wconf_details]
(
@userid int,
@type int,
@btime varchar(30),--注意月和日必须占两位;zhao4zhong1添加下面的转换后放松了此限制
@etime varchar(30),--注意月和日必须占两位;zhao4zhong1添加下面的转换后放松了此限制
@sname varchar(500),
@linenum int,
@pages int,
@totalpages int output,
@totalcounts int output
)
AS
set nocount on
begin
declare @counts int
declare @time varchar(20)
declare @rolecode varchar(20)
if @btime=''
begin
set @btime='2011-01-01'
end
if @etime=''
begin
set @etime=convert(varchar(10),getdate(),120)
end
select @btime=convert(varchar(10),convert(datetime,@btime,120),120) --将2016-5-1转换为2016-05-01 zhao4zhong1添加
select @etime=convert(varchar(10),convert(datetime,@etime,120),120) --将2016-5-1转换为2016-05-01 zhao4zhong1添加
set @time=@etime -- +' 23:59:59'
create table #tmp1(sno int identity(1,1),error varchar(2),companyname varchar(500),
username varchar(100),cno int,tpc nvarchar(400) default '',times datetime,
etimes datetime,iplcount int default 0,plcount int default 0,sec int default 0,
hostpass varchar(20),guestpass varchar(20),loginname varchar(100),pword varchar(50),handphone varchar(50),companyid int,userid int,mct varchar(3)
)
create table #tmp0(sno int,error varchar(2),companyname varchar(500),
username varchar(100),cno int,tpc nvarchar(400) default '',times datetime,
etimes datetime,iplcount int default 0,plcount int default 0,sec int default 0,
hostpass varchar(20),guestpass varchar(20),loginname varchar(100),pword varchar(50),handphone varchar(50),companyid int,userid int,
ftp varchar(2),balance numeric(10,2),mct varchar(3),salesModelID int
)
/*2012-12-27 入参增加userid 各种情况增加权限过滤 增加‘查询全部’情况*/
if @userid = -1
begin
if @type=0 -- 查询全部
begin
if @sname=''
begin
insert into #tmp1(error, tpc, hostPass, guestPass, companyname, username, loginname, pword, handphone, times, companyid, userid, mct)
select '0',aa.tpc,aa.hostPass,aa.guestPass,aa.companyname,aa.username,aa.loginname,aa.pword,aa.handphone,aa.times,aa.companyid,aa.userid,aa.mct
from (
select b.cno as cno,b.tpc as tpc,f.hostPass as hostPass,f.guestPass as guestPass,
e.companyname as companyname,a.username as username,a.loginname as loginname,a.pword as pword,
a.handphone as handphone,min(c.btm) as times,a.userid as userid,e.companyid as companyid,a.mct as mct
from wconf.cjms_user_userinfo a,wconf.wconf b,wconf.wconffee c,wconf.clmgr_ec_info e,wconf.Conf_Arrangement f
where a.userid=b.userid
and b.cno=c.cno
and a.companyid=e.companyid
and b.aid=f.aid
group by b.cno,b.tpc,f.HostPass,f.GuestPass,a.username,e.companyname,a.loginname,a.pword,a.handphone,a.userid,e.companyid,a.mct
) aa
where convert(varchar(10),aa.times,120)>=@btime --注意月和日必须占两位
and convert(varchar(10),aa.times,120)<=@time --注意月和日必须占两位