create procedure [dbo].[MssWebOrderAdvancedSearch]
@inSysUserId int,
@inOrderNo varchar(23) = null,
@inEstimateNo varchar(32)= null,
@inCustomerName varchar(32) = null,
@inOrderStatusId int = null,
@inNationalAccountName varchar(30) = null,
@inGblNumber varchar(21) = null,
@inStarDate date = null,
@inEndDate date = null,
@inCreationDate date = null,
@inTypeOfMove int = null
as
begin
    ;with OrderInfoSecurityModule as
    (
        select SecModules.ModulePriKey
        from SecModules where SecModules.Description = 'Order Information' and
        SecModules.Active = 1
    ),
    AvailableBranches as (select SecProfileDetail.BranchPriKey
    from UserAccess
    cross join OrderInfoSecurityModule
    inner join SecProfileDetail on
        UserAccess.SecProfilePriKey = SecProfileDetail.SecProfilePriKey and
        OrderInfoSecurityModule.ModulePriKey = SecProfileDetail.ModulePriKey
    where UserAccess.SysUserID = @inSysUserId)
    select
        top 1000
        OrderId = Orders.Prikey,
        OrderNo = Orders.OrderNo,
        EstimateNo =  Orders.EstimateNo,
        CustomerName = Case Orders.OpenOrderInModule
            when 'Office & Industrial' then Orders.LastName
            else dbo.FormatLastNameFirstName(Orders.LastName, Orders.FirstName) end,
        OrderStatus = Orders.OrderStatus,
        NationalAccountName =  Orders.AccountName,
        GblNumber = MilitaryOrder.GBLNumber,
        StartLoad = Case Orders.OpenOrderInModule
            when 'Office & Industrial' then OfficeIndustrialOrder.JobStartDate
            else  Orders.StartLoad end,
        EndLoad = Case Orders.OpenOrderInModule
            when 'Office & Industrial' then OfficeIndustrialOrder.JobEndDate
            else  Orders.EndLoad end,
        CreationDate = Orders.CreatedOn
        from Orders
        inner join AvailableBranches a  on Orders.BranchPriKey = a.BranchPriKey
        left outer join OrderStatus on OrderStatus.Status =Orders.OrderStatus and OrderStatus.PriKey = @inOrderStatusId
        left outer join MoveType on MoveType.PriKey = Orders.MoveType
        left outer join OfficeIndustrialOrder on OfficeIndustrialOrder.OrderFID = orders.PriKey
        left outer join MilitaryOrder on MilitaryOrder.OrdersFID = orders.PriKey
        where (@inOrderNo is null or orders.OrderNo like @inOrderNo+'%') and
        (@inEstimateNo is null or Orders.EstimateNo like @inEstimateNo+'%') and
        (@inCustomerName is null or  
            (case Orders.OpenOrderInModule
                when 'Office & Industrial' then
                    case when Orders.LastName like @inCustomerName+'%' then 1 else 0 end
                else case when (Orders.LastName like @inCustomerName+'%') or (Orders.FirstName like @inCustomerName+'%')
                then 1 else 0 end
             end) = 1) and
        (@inOrderStatusId is null or Orders.OrderStatus = OrderStatus.Status) and
        (@inNationalAccountName is null or Orders.AccountName like @inNationalAccountName+'%') and
        (@inStarDate is null or
            (case Orders.OpenOrderInModule
                    when 'Office & Industrial' then
                        case when cast(OfficeIndustrialOrder.JobStartDate as date) >= @inStarDate then 1 else 0 end
                    else case when cast(Orders.StartLoad as date) >= @inStarDate  
                    then 1 else 0 end
            end) = 1) and
        (@inEndDate is null or
            (case Orders.OpenOrderInModule
                when 'Office & Industrial' then
                    case when cast(OfficeIndustrialOrder.JobEndDate as date) <= @inEndDate then 1 else 0 end
                else case when cast(Orders.EndLoad as date) <= @inEndDate
                then 1 else 0 end
            end) = 1) and
        (@inGblNumber is null or MilitaryOrder.GBLNumber like @inGblNumber+'%') and
        (@inCreationDate is null or cast(Orders.CreatedOn as date) = @inCreationDate) and
        (@inTypeOfMove is null or Orders.MoveType = @inTypeOfMove )
        order by Orders.CreatedOn desc
end
GO
GRANT EXECUTE ON  [dbo].[MssWebOrderAdvancedSearch] TO [MssExec]
GO