set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[PRptsys_dongli]
@beginTime varchar(100),
@endTime varchar(100),
@storename varchar(100),
@username varchar(100),
@usernumber varchar(100),
@saleareacode VARCHAR(100),
@xwPageIndex int, --开始页码
@xwPageSize int --行数
as
declare @sql varchar(8000)
declare @where varchar(2000)
set @where=' where 1=1 '
if @beginTime is not null and @beginTime <>''
set @where = @where + ' and convert(varchar(10),visittime,120) >= '''+@beginTime+''' '
if @endTime is not null and @endTime <>''
set @where = @where + ' and convert(varchar(10),visittime,120) <= '''+@endTime+''''
if @storename is not null and @storename <>''
set @where = @where + ' and storename like ''%'+@storename+'%'' '
if @username is not null and @username <>''
set @where = @where + ' and username like ''%'+@username+'%'' '
if @usernumber is not null and @usernumber <>''
set @where = @where + ' and UserNumber like ''%'+@usernumber+'%'' '
if @saleareacode IS NOT NULL AND @saleareacode <>''
set @where = @where + ' and (SaleAreacode ='''+@saleareacode+'''' +'or saleareacode like '''+@saleareacode+'.%'')'
set @sql='(SELECT t1.id,t2.StoreName,t4.saleareacode,t4.saleareaname
,t1.miaoshu1,t1.img_photo1,t1.miaoshu2,t1.img_photo2,t1.miaoshu3,t1.img_photo3,t3.usernumber,CONVERT(VARCHAR(100),t1.visittime,20) AS visittime,t3.UserName,t3.mobilephone,t1.col1,t1.col2,t1.col3,t1.col4
FROM dbo.sys_dongli t1 LEFT JOIN dbo.TBasStore t2 ON t1.storeguid = t2.Guid
LEFT JOIN dbo.TBasUserInfo t3 ON t1.usernumber = t3.UserNumber
left join tbassalearea t4 on t2.saleareaid = t4.saleareaid
) aa'
EXEC dbo.PSysPager_Rpt1
@Tables = @sql,
@PK = 'visittime',
@countFields = 'null as id,
null as storename ,
null as saleareacode,
null as saleareaname,
null as miaoshu1 ,
null as img_photo1 ,
null as miaoshu2 ,
null as img_photo2 ,
null as miaoshu3,
null as img_photo3 ,
null as usernumber,
null as visittime,
null as username ,
null as mobilephone,
null as col1,
null as col2,
null as col3,
null as col4 ',
@Fields = 'id,storename,saleareacode,saleareaname,miaoshu1,img_photo1,miaoshu2,img_photo2,miaoshu3,img_photo3,usernumber,
visittime,username,mobilephone,col1,col2,col3,col4',
@Filter = @where ,--Where条件
@xwPageIndex = @xwPageIndex, --开始页码
@xwPageSize = @xwPageSize --行数