public static OrdersList GetOrdersByName(string ClientName, int statusFilter, OrderSortFields sort, DateTime lowDate, DateTime hiDate, int startIndex, int size, int AcctgOrderLineId)
{
using (var dc = new DCFactory<StoreDataContext>())
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Order>(o => o.OrderLines);
dc.DataContext.LoadOptions = options;
dc.DataContext.Log = new DebugTextWriter();
IEnumerable<Order> orders = new List<Order>();
int TotalCount = 0;
int ClientCount = 0;
var Clients = dc.DataContext.Users.Where(x => x.Clientname.Contains(ClientName)).Select(x => x.AcctgID).AsEnumerable();
try
{
ClientCount = Clients.Count();
}
catch
{
}
//применить сортировку
string SortExpr = "";
string StatusFilter = "";
string OrderlineAcctgIdFilter = "";
if (AcctgOrderLineId > 0)
{
OrderlineAcctgIdFilter = "and (select count(*) from dbo.OrderLines c where c.OrderId = a.OrderId and c.AcctgOrderLineId = " + AcctgOrderLineId.ToString() + ") > 0";
}
switch (sort)
{
//case OrderSortFields.OrderIdAsc: orders = orders.OrderBy(o => o.OrderID); break;
//case OrderSortFields.OrderIdDesc: orders = orders.OrderByDescending(o => o.OrderID); break;
//case OrderSortFields.OrderDateAsc: orders = orders.OrderBy( o => o.OrderDate ); break;
//case OrderSortFields.OrderDateDesc: orders = orders.OrderByDescending(o => o.OrderDate); break;
//case OrderSortFields.TotalAsc: orders = orders.OrderBy( o => o.Total ); break;
//case OrderSortFields.TotalDesc: orders = orders.OrderByDescending( o => o.Total); break;
//case OrderSortFields.Status: orders = orders.OrderBy(o => o.Status).ThenBy(o => o.OrderID); break;
//case OrderSortFields.StatusDesc: orders = orders.OrderByDescending( o => o.Status ).ThenBy( o => o.OrderID ); break;
case OrderSortFields.OrderIdAsc: SortExpr = "ORDER BY a.OrderID"; break;
case OrderSortFields.OrderIdDesc: SortExpr = "ORDER BY a.OrderID DESC"; break;
case OrderSortFields.Status: SortExpr = "ORDER BY Status"; break;
}
switch (statusFilter)
{
case 0: StatusFilter = "AND a.Status > 0"; break; //Все
case 1: StatusFilter = "AND a.Status = 1"; break; //Новый
case 2: StatusFilter = "AND a.Status = 2"; break; //В работе
case 3: StatusFilter = "AND a.Status = 3"; break; //Архив
case 4: StatusFilter = "AND a.Status > 0 AND a.Status < 3"; break; //Активные
}
if (string.IsNullOrEmpty(ClientName))
{
orders = dc.DataContext.ExecuteQuery<Order>(@"SELECT *
FROM (SELECT ROW_NUMBER() OVER ( " + SortExpr + @" ) AS RowNum, a.*, b.ClientName + ' ' + b.ContactPersonPhone as ClientName
FROM dbo.Orders a, dbo.Users b, dbo.OrderLines c
WHERE a.OrderId = C.OrderId and OrderDate >= {0} AND OrderDate <= {1} AND a.UserId = b.UserId AND c.InternalFranchName = {4} " + OrderlineAcctgIdFilter + " " + StatusFilter + @") AS RowConstrainedResult
WHERE RowNum > {2}
AND RowNum <= {2} + {3}
ORDER BY RowNum", lowDate, hiDate, startIndex, size, SiteContext.Current.InternalFranchName);
TotalCount = dc.DataContext.ExecuteQuery<int>(@"SELECT COUNT(*)
FROM dbo.Orders a, dbo.Users b, dbo.OrderLines c
WHERE a.OrderId = C.OrderId AND OrderDate >= {0} AND OrderDate <= {1} AND a.UserId = b.UserId AND c.InternalFranchName = {2} " + OrderlineAcctgIdFilter + " " + StatusFilter,
lowDate,
hiDate,
SiteContext.Current.InternalFranchName).FirstOrDefault();
}
else
//orders = dc.DataContext.Orders.Where(o => Clients.Contains(o.UserID)).ToArray().AsEnumerable();
{
if (ClientCount > 0)
{
orders = dc.DataContext.ExecuteQuery<Order>(@"SELECT *
FROM (SELECT ROW_NUMBER() OVER ( " + SortExpr + @" ) AS RowNum, a.*, b.ClientName + ' ' + b.ContactPersonPhone as ClientName
FROM dbo.Orders a, dbo.Users b, dbo.OrderLines c
WHERE a.OrderId = C.OrderId and OrderDate >= {0} AND OrderDate <= {1} " + OrderlineAcctgIdFilter + " " + StatusFilter + @" AND a.UserID = b.UserId AND c.InternalFranchName = {4} AND a.ClientId in ('" + Clients.Select(x => x.ToString()).Aggregate((prev, next) => prev + "','" + next) +
@"')) AS RowConstrainedResult
WHERE RowNum > {2} AND RowNum <= {2} + {3}
ORDER BY RowNum", lowDate, hiDate, startIndex, size, AcctgRefCatalog.RmsFranches[SiteContext.Current.InternalFranchName].InternalFranchName);
try
{
TotalCount = dc.DataContext.ExecuteQuery<int>(@"SELECT COUNT(*)
FROM dbo.Orders a, dbo.Users b,dbo.OrderLines c
WHERE a.OrderId = C.OrderId and OrderDate >= {0} " + OrderlineAcctgIdFilter + " " + StatusFilter + @" AND a.UserId = b.UserId AND OrderDate <= {1} AND CAST(a.ClientID as NVarChar(50)) in ('" + Clients.Select(x => x.ToString()).Aggregate((prev, next) => prev + "','" + next) + "')", lowDate, hiDate).FirstOrDefault();
}
catch
{
}
}
}
//применить фильтры
// orders = statusFilter == OrderStatusFilter.ActiveOrders ? orders.Where(o => o.Status != OrderStatus.Completed) : orders.Where(o => o.Status == OrderStatus.Completed);
OrdersList res = new OrdersList();
res.Orders = orders.ToArray();
res.Orders.Each((x) => x.ClientName = dc.DataContext.Users.Where(y => y.AcctgID ==x.ClientID).FirstOrDefault().Clientname); //Нужно загрузить здесь, так как после диспоуза дтаконтекста юзер будет не доступен
res.Orders.Each((x) => x.ClientSaldo = decimal.Round(LightBO.GetUserLightBalance(dc.DataContext.Users.Where(y => y.AcctgID == x.ClientID).FirstOrDefault().UserID, DateTime.Now)));
res.Orders.Each((x) => x.ActiveOrdersSum = decimal.Round(LightBO.GetUserActiveOrdersSum(dc.DataContext.Users.Where(y => y.AcctgID == x.ClientID).FirstOrDefault().UserID)));
res.Orders.Each((x) => x.PrepaymentPercent = dc.DataContext.ExecuteQuery<decimal>("select PrepaymentPercent from dbo.Users where UserId = {0}", dc.DataContext.Users.Where(y => y.AcctgID == x.ClientID).FirstOrDefault().UserID).FirstOrDefault());
res.Orders.Each((x) => x.ActiveOrdersSumToDate = LightBO.GetUserActiveOrdersSumToDate /*GetUserLightDebt*/(dc.DataContext.Users.Where(y => y.AcctgID == x.ClientID).FirstOrDefault().UserID));
res.Orders.Each((x) => x.ClientSaldoToDate = LightBO.GetUserLightBalanceWithDelay(dc.DataContext.Users.Where(y => y.AcctgID == x.ClientID).FirstOrDefault().UserID));
res.Orders.Each((x) => x.PaymentLimit = LightBO.GetPaymentLimit(dc.DataContext.Users.Where(y => y.AcctgID == x.ClientID).FirstOrDefault().UserID));
res.TotalCount = TotalCount;
res.StartIndex = startIndex;
return res;
}
}
public static OrdersList GetOrders( string clientId, OrderStatusFilter statusFilter, OrderSortFields sort, int startIndex, int size )
{
using( var dc = new DCFactory<StoreDataContext>() )
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Order>( o => o.OrderLines );
dc.DataContext.LoadOptions = options;
dc.DataContext.Log = new DebugTextWriter();
var orders = dc.DataContext.Orders.Where( o => o.ClientID == clientId ).ToArray().AsQueryable();
//применить фильтры
orders = statusFilter == OrderStatusFilter.ActiveOrders ? orders.Where( o => o.Status != OrderStatus.Completed ) : orders.Where( o => o.Status == OrderStatus.Completed );
//применить сортировку
switch( sort )
{
case OrderSortFields.OrderIdAsc: orders = orders.OrderBy( o => o.OrderID ); break;
case OrderSortFields.OrderIdDesc: orders = orders.OrderByDescending( o => o.OrderID ); break;
//case OrderSortFields.OrderDateAsc: orders = orders.OrderBy( o => o.OrderDate ); break;
//case OrderSortFields.OrderDateDesc: orders = orders.OrderByDescending( o => o.OrderDate ); break;
//case OrderSortFields.TotalAsc: orders = orders.OrderBy( o => o.Total ); break;
//case OrderSortFields.TotalDesc: orders = orders.OrderByDescending( o => o.Total ); break;
case OrderSortFields.Status: orders = orders.OrderBy( o => o.Status ).ThenBy( o => o.OrderID ); break;
//case OrderSortFields.StatusDesc: orders = orders.OrderByDescending( o => o.Status ).ThenBy( o => o.OrderID ); break;
}
OrdersList res = new OrdersList();
res.TotalCount = orders.Count();
res.Orders = orders.Skip( startIndex ).Take( size ).ToArray();
res.StartIndex = startIndex;
return res;
}
}
