USE [Warehouse]
GO
/****** Object:  StoredProcedure [dbo].[SP_T_Operation_SelectAll]    Script Date: 07/19/2012 14:21:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------
--用途：模糊查询 
--项目名称：
--说明：
--时间：2012-7-17 15:00:36
------------------------------------
ALTER PROCEDURE [dbo].[SP_T_Operation_SelectAll_InOut]
(
    @SheetID varchar(20),
    @date1 varchar(25),
    @date2 varchar(25),
    @SheetOwner varchar(10),
    @CostCenterCode varchar(10),
    @ProductName nvarchar(100)
)
AS
BEGIN
    DECLARE @sql nvarchar(MAX)
    SET @sql ='
            select 
            a.SheetID,a.OperationDate,a.OriginalSheetID,            
            c.ProductName,a.ReturnReason,
            case
            when a.OriginalSheetID="" or a.OriginalSheetID is NULL then b.Quantity
            end as outs,
            case
            when a.OriginalSheetID<>"" or a.OriginalSheetID is not NULL then b.Quantity
            end as ins,
            b.UnitPrice,
            d.UserName,b.CostCenterCode
            from T_Operation a left join 
            T_OperationDetail b on a.SheetID=b.SheetID 
            left join T_Product c on b.ProductID=c.ProductID
            left join T_User d on a. SheetOwner=d.UserID
            where 1=1
            '            
    IF @SheetType IS NOT NULL AND @SheetType <>''
        SET @sql=@sql+' and A.SheetType ='+@SheetType;
        
    IF @date1 IS NOT NULL AND @date1<>'' and @date2 IS NOT NULL AND @date2<>''
        SET @sql=@sql+' and A.OperationDate >='''+ CONVERT(varchar(100), @date1, 120)+''' and A.OperationDate <='''+CONVERT(varchar(100), @date2, 120)+'''';
        
    IF @SheetOwner IS NOT NULL AND @SheetOwner<>''
        SET @sql=@sql+' and d.UserName LIKE(''%'+@SheetOwner+'%'')';
        
    IF @CostCenterCode IS NOT NULL AND @CostCenterCode<>''
        SET @sql=@sql+' and B.CostCenterCode LIKE(''%'+@CostCenterCode+'%'')';
        
    IF @ProductName IS NOT NULL AND @ProductName<>''
        SET @sql=@sql+' and C.ProductName LIKE(''%'+@ProductName+'%'')';
    
    PRINT (@sql)
    
    EXEC (@sql)
    
END

case
when a.OriginalSheetID="" or a.OriginalSheetID is NULL then b.Quantity
end as outs,
case
when a.OriginalSheetID<>"" or a.OriginalSheetID is not NULL then b.Quantity
end as ins,