关于报表

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    --行数