public List <missingPieceInfo> FindMissingOrders(string storeName) { List <missingPieceInfo> miss = new List <missingPieceInfo>(); List <missingPieceInfo> missFilteredbyCustomerid = new List <missingPieceInfo>(); StoreContext dbStore; //get all invoices for the last day List <DataAccessLayer.AssemblyDB.Invoice> allInvoices = (from inv in dbassembly.Invoices where DbFunctions.TruncateTime(inv.MarkInDate) >= DateTime.Today select inv).ToList(); //group these by orderid var invsGroupedByOrderID = from inv2 in allInvoices group inv2 by inv2.OrderID into groupedinvs select groupedinvs; //Invoices are now grouped by orderid so at look at each group of invoices foreach (var AssemblyInvGroup in invsGroupedByOrderID) { int storeid = AssemblyInvGroup.First().StoreID; dbStore = GetDBStore(storeid); //find all the orderdetail objects with this orderid and group by orderid var ordersGroup = from order in dbStore.OrderDetails where AssemblyInvGroup.Key == order.OrderID group order by order.OrderID into groupedby select groupedby; if (ordersGroup.Count() > 0) { foreach (var orderDetailGroup in ordersGroup) { int piecesInOrderDetails = (int)orderDetailGroup.Sum(o => o.Pieces); OrderDetail orderDetail = orderDetailGroup.First(); List <AssemblyDB.AutoSort> inAutoPieces = (from auto in dbassembly.AutoSorts where auto.CustomerID == orderDetail.CustomerID && DbFunctions.TruncateTime(auto.InvoiceDate) >= DateTime.Today group auto by auto.ArticleCode into groupbyarticle select groupbyarticle.FirstOrDefault()).ToList(); //ignore number pieces mismatch if the item not marked in if (piecesInOrderDetails != inAutoPieces.Count && inAutoPieces.Count > 0) { missingPieceInfo info = new missingPieceInfo() { orderid = AssemblyInvGroup.Key, numInvoiced = inAutoPieces.Count.ToString(), numOrders = piecesInOrderDetails, storeid = orderDetail.StoreID, customerid = orderDetail.CustomerID, date = orderDetail.DueDate.ToString() }; miss.Add(info); } } } } //we have the possible mismatches between orderdetails pieces and autosort now group by customerid. there //might be multiple mismatches for same customer in which case we need to look at group as a whole // var groupedbyCust = miss.GroupBy(g => g.customerid).ToList(); foreach (var missGroup in groupedbyCust) { //these customer might have multiple orders if (missGroup.Count() > 1) { if ((int)missGroup.Sum(o => o.numOrders) != Int32.Parse(missGroup.First().numInvoiced)) { missGroup.First().numInvoiced = string.Format("Mult work orders {0}", missGroup.First().numInvoiced); missFilteredbyCustomerid.Add(missGroup.First()); } } else { missFilteredbyCustomerid.Add(missGroup.First()); } } return(missFilteredbyCustomerid.OrderBy(o => o.storeid).ToList());; }
public List <missingPieceInfo> FindMissingOrders(string storeName) { DataAccessLayer.AssemblyDB.Assembly assembly = new DataAccessLayer.AssemblyDB.Assembly(); ConnectionStringSettingsCollection connections = ConfigurationManager.ConnectionStrings; string StoreConnectionString = connections["StoreContext"].ConnectionString; db1OTS = new StoreContext(StoreConnectionString); // List<Invoice> invs = assembly.Invoices.Take(10).ToList(); StoreConnectionString = connections["Store2Context"].ConnectionString; db2OTS = new StoreContext(StoreConnectionString); StoreConnectionString = connections["Store3Context"].ConnectionString; db3OTS = new StoreContext(StoreConnectionString); StoreConnectionString = connections["Store4Context"].ConnectionString; db4OTS = new StoreContext(StoreConnectionString); List <missingPieceInfo> miss = new List <missingPieceInfo>(); DateTime prev = DateTime.Today.AddDays(-1); List <DataAccessLayer.AssemblyDB.Invoice> allInvoices = (from inv in assembly.Invoices where DbFunctions.TruncateTime(inv.InvoiceDate) >= prev select inv).ToList(); var invsGroupedByOrderID = from inv2 in allInvoices group inv2 by inv2.OrderID into groupedinvs select groupedinvs; foreach (var AssemblyInvGroup in invsGroupedByOrderID) { int storeid = AssemblyInvGroup.First().StoreID; dbOTS = db1OTS; switch (storeid) { case 2: dbOTS = db2OTS; break; case 3: dbOTS = db3OTS; break; case 4: dbOTS = db4OTS; break; } var ordersGroup = from order in dbOTS.OrderDetails where AssemblyInvGroup.Key == order.OrderID group order by order.OrderID into groupedby select groupedby; if (ordersGroup.Count() > 0) { foreach (var group in ordersGroup) { int piecesInOrders = (int)group.Sum(o => o.Pieces); DataAccessLayer.AssemblyDB.Invoice inv = AssemblyInvGroup.First(); if (group.Sum(o => o.Pieces) != AssemblyInvGroup.Sum(i => i.Pieces)) { missingPieceInfo info = new missingPieceInfo() { orderid = AssemblyInvGroup.Key, numInvoiced = (int)AssemblyInvGroup.Sum(i => i.Pieces), numOrders = piecesInOrders, storeid = inv.StoreID, date = inv.InvoiceDate.ToString() }; //before we mark this as an error, the clerk might have generated another invoice for this order to make up for //an error on their part so check if there are multiple invoices for this customer List <AssemblyDB.Invoice> inerror = (from inv1 in allInvoices where inv1.CustomerID == inv.CustomerID select inv1).ToList(); if (inerror.Count > 1) //must be more than one to bother checking { int TotalCount = (int)inerror.Sum(o => o.Pieces); if (TotalCount != piecesInOrders) { miss.Add(info); } } else { miss.Add(info); } } } } } return(miss.OrderBy(o => o.storeid).ToList());; }