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