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; } }