USE [LHZWeb_WWW]
GO
/****** Object:  StoredProcedure [dbo].[SelectSaleOrderByLike]    Script Date: 05/29/2012 09:52:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

------------------------------------------------
-- Generated By:   dell using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[SelectSaleOrderByLike]
-- Date Generated: 2011年8月19日
-- Alter By: Li.Lei    Alter Date: 12/19/2011
------------------------------------------------
ALTER PROCEDURE [dbo].[SelectSaleOrderByLike]
(
    @OrderNo varchar(20),
    @Email varchar(50),
    @SKU varchar(20),
    @GoodsName nvarchar(200),
    @OrderDateTo  datetime,
    @OrderDateFrom  datetime,
    @Consignee  nvarchar(20),
    @paystatus varchar(5),
    @shipment nvarchar(5),
    @IsGiftCard char(1),
    @NotRmk varchar(20),
    @PageSize  int,
    @Page  int,
    @TotalNum int out )
as
declare @sql nvarchar(2500),@r_sql nvarchar(1000) = '';

----------- 公用查询条件开始 -----------------------
if(ISNULL(@OrderNo,'') != '') begin
    set @r_sql += ' and OrderNo like '''+'%' + @OrderNo +'%'+'''';
end
if(DATEDIFF(DAY,ISNULL(@OrderDateFrom,0),'1900-01-1 0:00:00') != 0) begin
    set @r_sql += ' and @OrderDateFrom <= OrderDate ';
end
if(DATEDIFF(DAY,ISNULL(@OrderDateTo,0),'1900-01-1 0:00:00') != 0) begin
    set @r_sql += ' and @OrderDateTo >= OrderDate';
end
if(ISNULL(@Consignee,'') != '') begin
    set @r_sql += ' and Consignee like '''+'%' + @Consignee +'%'+''''
end
if(ISNULL(@Email,'') != '') begin
    set @r_sql += ' and Email like '''+'%'+@Email+'%'+'''';
end
if(ISNULL(@paystatus,'-1') != '-1') begin
    set @r_sql += ' and payStatus = ' + str(@paystatus);
end
if(ISNULL(@shipment,'-1') != '-1') begin
    set @r_sql += ' and shipment = ' + @shipment;
end
if(ISNULL(@IsGiftCard,'') != '') begin
    set @r_sql += ' and IsGiftCard = ''' + @IsGiftCard+'''';
end

--未备注过滤条件 add by lilei on2011-10-19
if (@NotRmk!='') begin
    if (@NotRmk='STO') begin
        set @r_sql += ' and Shipment=1 and ISNULL(SalesOrder.Remark,'''')=''''';
    end
    if (@NotRmk='COE') begin
        set @r_sql += ' and Shipment=3 and ISNULL(SalesOrder.Remark,'''')=''''';
    end
    if (@NotRmk='CODZJS') begin
        set @r_sql += ' and Shipment=4 and ISNULL(SalesOrder.Remark,'''')=''''';
    end
end
------------ end ------------------------------------

    if (ISNULL(@SKU,'')='' and  ISNULL(@GoodsName,'')='')    
    begin

set @sql = '
    Select @TotalNum = count(*) from SalesOrder Where Valid = 1
';
set @sql += @r_sql;

exec sp_executesql @sql,N'@TotalNum int output,@OrderDateFrom datetime,@OrderDateTo datetime',
            @TotalNum output,@OrderDateFrom,@OrderDateTo;
            
set @sql = '
Select T2.* ,
        (Select Name from Area Where Area.[ID]=T2.Province) ProvinceName,
        (Select Name FROM Area Where Area.[ID]=T2.City) CityName,
        (Select Name FROM Area Where Area.[ID]=T2.District) DistrictName 
    from 
(Select SalesOrder.*,c.Amount CouponAmount,(ROW_NUMBER() over(Order by SalesOrder.[ID] desc)) as rn from SalesOrder
left join Coupon c  on c.CouponNo=SalesOrder.CouponNo
 Where SalesOrder.Valid=1'
set @sql += @r_sql;
set @sql += ')T2';  -- Where  rn > (@Page-1)*@PageSize and rn<=@Page*@PageSize

--如果@PageSize不为-1返回全部数据
if (@PageSize!=-1)
begin
set @sql += ' Where  rn > (@Page-1)*@PageSize and rn<=@Page*@PageSize';
end

exec sp_executesql @sql,N'@page int,@PageSize int,@OrderDateFrom datetime,@OrderDateTo datetime',
                @Page,@PageSize,@OrderDateFrom,@OrderDateTo;

    end 
    else 
    begin
    
if(ISNULL(@GoodsName,'') != '') begin
    set @r_sql += ' and OrderItem.GoodsName like '''+'%'+@GoodsName+'%'+'''';
end
if(ISNULL(@SKU,'') != '')
begin
set @r_sql += ' and OrderItem.SKU like '''+'%' + @SKU + '%'+'''';
end
set @r_sql += N' and OrderItem.Vali