exec sp_helptext SVB_ListLoopBOut

CREATE PROCEDURE SVB_ListLoopBOut

@CompanyName VarChar(64),

@DeliverSN VarChar(32),

@GBOutHID VarChar(32),

@Status Int,

@TankCode VarChar(32),

@ICompanyName VarChar(128),

@IsUpdate Int,

@PageIndex Int,

@PageSize Int,

@Begin datetime,

@End datetime,

@Count Int output

AS

Set @CompanyName=replace(@CompanyName,'''','''''')

Set @DeliverSN=replace(@DeliverSN,'''','''''')

Set @GBOutHID=replace(@GBOutHID,'''','''''')

Set @TankCode=replace(@TankCode,'''','''''')

Set @ICompanyName=replace(@ICompanyName,'''','''''')

declare @select varchar(1000) --查询字段

declare @Tablename varchar(500)-- 查询表包含连接字符串

declare @filed varchar(100) --排序字段

declare @order int -- 0 asc 1：desc

declare @strWhere varchar(2000) --查询条件

SET @select='SVB.zLoopHID,SVB.zStatus,BC.zNameCHN,zICompanyName,zOrigReqOutID,'

SET @select=@select+' CASE WHEN SVB.zBookingDate = ''1900-01-01'' THEN '''' ELSE CONVERT(VARCHAR(16),SVB.zBookingDate,120) END AS zBookingDate,'

SET @select=@select+' CASE WHEN SVB.zSVBExpired = ''1900-01-01'' THEN '''' ELSE CONVERT(VARCHAR(16),SVB.zSVBExpired,120) END AS zSVBExpired,'

SET @select=@select+' SVB.zDeliverSN,zIssueDate,'

SET @select=@select+' SVB.zDateUpdated,SVB.zHandledBy,SVB.zCheckedBy,sum(zAmount)as zAmount '

--(select sum(zAmount) from zSVBgoodTruckOUTDetail where zGBOutHID=SVB.zGBOutHID)as zAmount,

--(select sum(isnull(zAmtOut,0)) from zTmogoodOut where zJoBID=SVB.zGBOutHID )as zFactOutAmt '

set @Tablename='zSvbGoodTruckOutLoopHead SVB '

set @Tablename=@Tablename+' inner join zSvbGoodTruckOutLoopDetail SD on SVB.zLoopHID=SD.zLoopHID '

set @Tablename=@Tablename+' inner join zCrsreqOutDetail CD on CD.zReqOutDID=SD.zReqOutDID '

set @Tablename=@Tablename+' inner join zCrsreqOutHead CH on CH.zReqOutHID=CD.zReqOutHID '

-- SET @Tablename=@Tablename+' inner join zFamTankInfo FT on FT.zTankID=SVB.zTankID '

SET @Tablename=@Tablename+' INNER JOIN zBasCompanyInfo BC on BC.zCompanyID =SVB.zCompanyID'

SET @strWhere=' 1=1 '

if(@CompanyName!='')

begin

SET @strWhere=@strWhere+' AND (BC.zNameCHN LIKE ''%'+@CompanyName+'%'' or BC.zShortName like ''%'+@CompanyName+'%'')'

end

if(@DeliverSN!='')

begin

SET @strWhere=@strWhere+' AND SVB.zDeliverSN LIKE ''%'+@DeliverSN+'%'' '

end

if(@GBOutHID!='')

begin

SET @strWhere=@strWhere+' AND SVB.zGBOUTHID LIKE ''%'+@GBOutHID+'%'' '

end

if(@ICompanyName!='')

begin

SET @strWhere=@strWhere+' AND ZICompanyName LIKE ''%'+@ICompanyName+'%'' '

end

SET @strWhere=@strWhere+' AND zIssueDate between '''+convert(varchar,@Begin,20)+''' and '''+convert(varchar,@End,20)+ ''''

SET @strWhere=@strWhere+' AND SVB.zStatus=(case when '+CAST(isnull(@Status,-1) AS VARCHAR)+'<0 then SVB.zStatus when '+CAST(@Status AS VARCHAR)+' between 0 and 2 then '+CAST(@Status AS VARCHAR)+' else SVB.zStatus end)'

SET @strWhere=@strWhere+' group by SVB.zLoopHID,SVB.zStatus,BC.zNameCHN,zICompanyName,zOrigReqOutID,SVB.zBookingDate,zSVBExpired,SVB.zDeliverSN,zIssueDate,SVB.zDateUpdated,SVB.zHandledBy,SVB.zCheckedBy '

set @order=1

set @filed=' SVB.zDateUpdated '

exec pro_fenye @select,@Tablename

,@filed,@order,@IsUpdate,@strWhere,@PageSize,@PageIndex,@Count output