public SearchResult SearchOrderByProductionOrder(SearchOrderArgs args)
{
if (String.IsNullOrEmpty(args.OrderBy))
{
args.OrderBy = "[OrderNo] ASC";
}
SqlCommand cmd = new SqlCommand();
StringBuilder mbBuilder = new StringBuilder();
mbBuilder.AppendFormat("(");
mbBuilder.AppendFormat(@"SELECT [BE_Order].[OrderID]
,[BE_Order].[CustomerID]
,[BE_Order].[PartnerID]
,[BE_Order].[OrderNo]
,[BE_Order].[OutOrderNo]
,[BE_Order].[OrderType]
,[BE_Order].[PartnerName]
,[BE_Order].[CustomerName]
,[BE_Order].[Address]
,[BE_Order].[Mobile]
,[BE_Order].[BookingDate]
,[BE_Order].[ShipDate]
,[BE_Order].[StepNo]
,[BE_Order].[Remark]
,[BE_Order].[Created]
,[BE_Order].[CreatedBy]
,[BE_Order].[Modified]
,[BE_Order].[ModifiedBy]
,[BE_Order].[SalesMan]
,[BE_Order].[Status]
FROM [BE_Order] with(nolock)
LEFT JOIN [dbo].[BE_ProductionOrder] with(nolock)
ON [BE_Order].OrderID=[BE_ProductionOrder].OrderID
WHERE 1=1
AND [BE_Order].Status='H' and [BE_ProductionOrder].OrderID IS NULL" );
mbBuilder.AppendFormat(") mb");
string baseSql = string.Format("(SELECT mb.* FROM {0}) baseSql", mbBuilder.ToString());
return(this.SearchBaseSqls(baseSql, cmd, args.OrderBy, args.RowNumberFrom, args.RowNumberTo));
}
public SearchResult SearchOrder(SearchOrderArgs args)
{
if (String.IsNullOrEmpty(args.OrderBy))
{
args.OrderBy = "[OrderNo] ASC";
}
SqlCommand cmd = new SqlCommand();
StringBuilder mbBuilder = new StringBuilder();
mbBuilder.AppendFormat("(");
mbBuilder.AppendFormat(@"SELECT [BE_Order].[OrderID]
,[BE_Order].[CustomerID]
,[BE_Order].[PartnerID]
,[BE_Order].[OrderNo]
,[BE_Order].[OutOrderNo]
,[BE_Order].[OrderType]
,[BE_Order].[PartnerName]
,[BE_Order].[CustomerName]
,[BE_Order].[Address]
,[BE_Order].[Mobile]
,[BE_Order].[BookingDate]
,[BE_Order].[ShipDate]
,[BE_Order].[StepNo]
,[BE_Order].[Remark]
,[BE_Order].[Created]
,[BE_Order].[CreatedBy]
,[BE_Order].[Modified]
,[BE_Order].[ModifiedBy]
,[BE_Order].[SalesMan]
,[TotalLength]
,[TotalAreal]
,[BE_Order].[Status]
FROM
[BE_Order] with(nolock)
LEFT JOIN
(
select OrderID,Sum(TotalLength) as TotalLength,Sum(TotalAreal) as TotalAreal from BE_OrderProduct Group By OrderID
) tb on [BE_Order].[OrderID] = tb.[OrderID]
WHERE 1=1" );
this.SetParameters_In(mbBuilder, cmd, "BE_Order].[OrderID", "mbIDs", args.OrderIDs);
this.SetParameters_In(mbBuilder, cmd, "BE_Order].[OrderType", "mbOrderTypes", args.OrderTypes);
this.SetParameters_In(mbBuilder, cmd, "BE_Order].[Status", "mbStatus", args.Status);
if (args.PartnerID.HasValue)
{
this.SetParameters_Equals(mbBuilder, cmd, "BE_Order].[PartnerID", "mbPartnerID", args.PartnerID.Value);
}
if (args.CustomerID.HasValue)
{
this.SetParameters_Equals(mbBuilder, cmd, "BE_Order].[CustomerID", "mbCustomerID", args.CustomerID.Value);
}
if (!string.IsNullOrEmpty(args.OrderNo))
{
this.SetParameters_Contains(mbBuilder, cmd, "BE_Order].[OrderNo", "mbOrderNo", args.OrderNo);
}
if (!string.IsNullOrEmpty(args.OutOrderNo))
{
this.SetParameters_Contains(mbBuilder, cmd, "BE_Order].[OutOrderNo", "mbOutOrderNo", args.OutOrderNo);
}
if (!string.IsNullOrEmpty(args.Mobile))
{
this.SetParameters_Contains(mbBuilder, cmd, "BE_Order].[Mobile", "mbMobile", args.Mobile);
}
if (!string.IsNullOrEmpty(args.Address))
{
this.SetParameters_Contains(mbBuilder, cmd, "BE_Order].[Address", "mbAddress", args.Address);
}
if (!string.IsNullOrEmpty(args.CustomerName))
{
this.SetParameters_Contains(mbBuilder, cmd, "BE_Order].[CustomerName", "mbCustomerName", args.CustomerName);
}
if (!string.IsNullOrEmpty(args.PartnerName))
{
this.SetParameters_Contains(mbBuilder, cmd, "PartnerName", "mbPartnerName", args.PartnerName);
}
if (!string.IsNullOrEmpty(args.StepNo))
{
this.SetParameters_Equals(mbBuilder, cmd, "BE_Order].[StepNo", "mbStepNo", args.StepNo);
}
//if (!string.IsNullOrEmpty(args.Resource))
//{
// mbBuilder.AppendFormat(@" and [BE_Order].[OrderID] in (
// select
// OrderID
// from
// BE_OrderResource with(nolock)
// where
// [Resource] in (
// select
// RoleName
// from
// BE_Role with(nolock)
// where
// RoleID in(
// select
// RoleID
// from
// BE_User2Role with(nolock)
// LEFT JOIN BE_User with(nolock) on BE_User2Role.UserID = BE_User.UserID
// where
// UserCode = N'{0}'
// )
// )
// or [Resource] = N'{0}'
// )", args.Resource);
//}
this.SetParameters_Between(mbBuilder, cmd, "BookingDate", "mbBookingDate", args.BookingDateFrom, args.BookingDateTo);
this.SetParameters_Between(mbBuilder, cmd, "ShipDate", "mbBookingDate", args.ShipDateFrom, args.ShipDateTo);
mbBuilder.AppendFormat(") mb");
string baseSql = string.Format("(SELECT mb.* FROM {0}) baseSql", mbBuilder.ToString());
return(this.SearchBaseSqls(baseSql, cmd, args.OrderBy, args.RowNumberFrom, args.RowNumberTo));
}