日期:2014-05-17 浏览次数:20668 次
ALTER proc [dbo].[BusinessQuery_SelectForZY]
@InputMan varchar(50),
@InputDepartment varchar(20),
@EndInputDate datetime ,
@StartInputDate datetime,
@Consignor varchar(100),
@DeliveryListNum varchar(50),
@ApproveNum varchar(20),
@PermissionsCode VARCHAR(10)
as
begin
declare @paySql varchar(5000)
set @paySql ='select
c.BusinessNum,
c.InputMan [录入人],
c.InputDate [录入时间],
c.DeliveryListNum [提单号],
c.ApproveNum [核销单号],
c.CustomsNum [海关编号],
CONVERT(VARCHAR(10),c.DeclareDate,120) [申报日期],
b.Consignor [付费单位],
c.Operate_Name [货主],
c.ConveyanceName [船名],
c.VoyageNum [航次],
(sum(case f.CostType when ''0'' then f.ShouldMoney else 0 end )-sum(case f.CostType when ''1'' then f.ShouldMoney else 0 end)) [收入]
from bus_BusinessDetail b,customs_CustomsDeclaration c,cost_FeeInfo f
where b.BusinessNum=c.BusinessNum
and b.BusinessNum=f.BusinessNum
and c.BusinessNum=f.BusinessNum
and f.Flag_ManagerCheckCost = ''1'' --查询已经审核的
'
if @InputMan is not null
begin
set @PaySql = @PaySql+ ' and c.InputMan = '''+@InputMan+''''
end
if @InputDepartment is not null
begin
set @PaySql = @PaySql+ ' and c.InputDepartment = '''+@InputDepartment+''''
end
IF @StartInputDate IS NOT NULL AND @EndInputDate IS NOT NULL
BEGIN
SET @PaySql = @PaySql + ' and c.InputDate BETWEEN ''' + CONVERT(VARCHAR(50),
@StartInputDate,120) + ''' AND ''' + CONVERT(VARCHAR(50),@EndInputDate,120)
+ ''''
end
if @Consignor is not null
begin
set @PaySql = @PaySql+' and b.Consignor='''+@Consignor+''''
end
if @DeliveryListNum is not null
begin
set @PaySql=@PaySql+' and c.DeliveryListNum='''+@DeliveryListNum+''''
end
if @ApproveNum is not null
begin
set @PaySql=@PaySql+' and c.ApproveNum='''+@ApproveNum+''''
end
if @PermissionsCode is not null
begin
set @PaySql=@PaySql+' and b.PermissionsCode='''+@PermissionsCode+''''
end
set @PaySql=@PaySql+' group by c.BusinessNum,c.InputMan,c.InputDate,c.DeliveryListNum,c.ApproveNum,c.CustomsNum,
c.DeclareDate,b.Consignor,c.Operate_Name,c.ConveyanceName,c.VoyageNum'