public static void CopyWOLineKittableToDB(List <WOLineKittableResultSet> dataSet, long UID, DateTime DateRun) { ConnectProdDbEntities connect = null; try { connect = new ConnectProdDbEntities(); connect.Configuration.AutoDetectChangesEnabled = false; string regexPattern = @"\{\*?\\[^{}]+}|[{}]|\\\n?[A-Za-z]+\n?(?:-?\d+)?[ ]?"; Regex rgx = new Regex(regexPattern); int count = 0; foreach (var line in dataSet) { ++count; var record = new WOLineKittableResultSet(); record.UID = UID; record.WONumber = line.WONumber; record.PartNumber = line.PartNumber; record.RespCode = line.RespCode; record.PlannedIssueQty = line.PlannedIssueQty; record.ActualIssueQty = line.ActualIssueQty; record.ActualKitNeed = line.ActualKitNeed; record.PlannedIssueDate = line.PlannedIssueDate; record.Kittable = line.Kittable; record.PNF = line.PNF; record.OverKitted = line.OverKitted; record.BatchNotes = line.BatchNotes != null?rgx.Replace(line.BatchNotes, "").Replace("\r", "").Replace("\n", "").ToLower() : ""; record.CommercialNotes = line.CommercialNotes != null?rgx.Replace(line.CommercialNotes, "").Replace("\r", "").Replace("\n", "").ToLower() : ""; record.DateRun = DateRun.Date; connect = AddToContextWOLineKittable(connect, record, count, 500, true); } connect.SaveChanges(); } finally { if (connect != null) { connect.Dispose(); } } }
private static ConnectProdDbEntities AddToContextWOLineKittable(ConnectProdDbEntities context, WOLineKittableResultSet entity, int count, int commitCount, bool recreateContext) { context.Set <WOLineKittableResultSet>().Add(entity); if (count % commitCount == 0) { context.SaveChanges(); if (recreateContext) { context.Dispose(); context = new ConnectProdDbEntities(); context.Configuration.AutoDetectChangesEnabled = false; } } return(context); }
public static void GenerateWOKittableReport() { Console.WriteLine("Starting Kittable Report Operation..." + DateTime.Now); using (var cDb = new ConnectProdDbEntities()) { cDb.Database.CommandTimeout = 400000; //check table for existing run for this day var todayDate = DateTime.Now.Date; var checkUID = cDb.WOKittableResultSets.Count() > 0 ? cDb.WOKittableResultSets.Select(x => x.UID).Max() : 0; var checkDate = cDb.WOKittableResultSets.Count() > 0 ? cDb.WOKittableResultSets.Select(x => x.DateRun).Max().Date : DateTime.MinValue.Date; if (checkDate == todayDate) { cDb.WOKittableResultSets.RemoveRange(cDb.WOKittableResultSets.Where(x => x.DateRun == todayDate)); cDb.SaveChanges(); } var setUID = ++checkUID; var checkLineUID = cDb.WOLineKittableResultSets.Count() > 0 ? cDb.WOLineKittableResultSets.Select(x => x.UID).Max() : 0; var checkLineDate = cDb.WOLineKittableResultSets.Count() > 0 ? cDb.WOLineKittableResultSets.Select(x => x.DateRun).Max().Date : DateTime.MinValue.Date; if (checkLineDate == todayDate) { cDb.WOLineKittableResultSets.RemoveRange(cDb.WOLineKittableResultSets.Where(x => x.DateRun == todayDate)); cDb.SaveChanges(); } var setLineUID = ++checkLineUID; using (var crdb = new ConnectReportDbEntities()) { crdb.Database.CommandTimeout = 400000; //Clear Tables //crdb.Database.ExecuteSqlCommand("truncate table WOKittable_WOOpenLines"); //crdb.Database.ExecuteSqlCommand("truncate table WOKittable_WOClosedLines"); //End Clear Tables using (var rdb = new thas01ReportEntities()) { rdb.Database.CommandTimeout = 400000; //Open Queries try { Console.WriteLine("Begin Populate"); // Run Inserts rdb.THAS_CONNECT_PopulateWOKittable_WOOpenLines(); rdb.THAS_CONNECT_PopulateWOKittable_WOClosedLines(); Console.WriteLine("Begin Retreival"); // End Inserts var allWOLines = crdb.WOKittable_WOOpenLines.OrderBy(x => x.PlannedIssueDate).ThenBy(w => w.WorksOrderNumber).ToList(); var allWOQuantities = rdb.THAS_CONNECT_WOKittable_GetAllOpenPlannedQuantities().ToList(); //var allWOLines = new List<WOKittable_WOOpenLines>(); //var allWOQuantities = new List<THAS_CONNECT_WOKittable_GetAllOpenPlannedQuantities_Result>(); Console.WriteLine("All WO Lines & WO Quantities Retreived..."); //Closed Queries //var closedWOLines = crdb.WOKittable_WOClosedLines.ToList(); var closedWOLines = crdb.WOKittable_WOClosedLines.ToList(); var closedWOQuantities = rdb.THAS_CONNECT_WOKittable_GetAllClosedPlannedQuantities().ToList(); //var closedWOQuantities = rdb.THAS_CONNECT_WOKittable_GetAllClosedPlannedQuantities().ToList(); Console.WriteLine("All Closed WO Lines & Closed WO Quantities Retreived..."); //End Closed Queries //var goodPartStock = rdb.THAS_CONNECT_WOKittable_GoodStock().ToList(); var exports = new List <WOKittingGroupedExport>(); string regexPattern = @"\{\*?\\[^{}]+}|[{}]|\\\n?[A-Za-z]+\n?(?:-?\d+)?[ ]?"; Regex rgx = new Regex(regexPattern); int totalCount = allWOLines.Select(y => y.WorksOrderNumber).Distinct().ToList().Count; int totalCount2 = allWOLines.Count(); int ctr = 0; int ctr2 = 0; //Console.WriteLine("--- " + DateTime.Now + " There are a total of " + totalCount + " lines to process. ---"); var kittingShortageList = new List <KittingShortage>(); Console.WriteLine("Begin Foreach"); var woLineList = new List <WOLineKittableResultSet>(); var alreadyIssuedLines = allWOLines.Where(x => x.IsFullyIssued).ToList(); foreach (var woLine in alreadyIssuedLines) { var overkitted = woLine.ActualIssueQty - woLine.ActualKitNeed; var line = new WOLineKittableResultSet { WONumber = woLine.WorksOrderNumber, ActualIssueQty = woLine.ActualIssueQty, ActualKitNeed = woLine.ActualKitNeed, PartNumber = woLine.CompPartNumber, PlannedIssueDate = woLine.PlannedIssueDate, PlannedIssueQty = woLine.PlannedIssueQty, Kittable = woLine.PlannedIssueQty, BatchNotes = woLine.BatchNotes, CommercialNotes = woLine.CommercialNotes, RespCode = woLine.RespCode, PNF = 0.0m, OverKitted = overkitted }; woLineList.Add(line); } Console.WriteLine("--- " + DateTime.Now + " There are a total of " + allWOLines.Where(x => !x.IsFullyIssued).Count() + " lines to process. ---"); foreach (var woLine in allWOLines.Where(x => !x.IsFullyIssued)) { var overkitted = woLine.ActualIssueQty - woLine.ActualKitNeed; var kittable = 0.0m; var thisStock = woLine.Stock; var thisDemand = allWOLines.Where(x => x.CompPartNumber == woLine.CompPartNumber && x.WOKittable_ID <= woLine.WOKittable_ID).ToList(); var totalDemand = thisDemand.Sum(y => y.PlannedIssueQty) - thisDemand.Sum(y => y.ActualIssueQty); var remainStock = thisStock - totalDemand < 0 ? 0 : thisStock - totalDemand; var pnf = 0.0m; if (remainStock >= (woLine.PlannedIssueQty - woLine.ActualIssueQty)) { //fully kittable kittable += woLine.PlannedIssueQty; } else if (remainStock == 0 && woLine.ActualIssueQty > 0) { if (woLine.ActualIssueQty <= woLine.PlannedIssueQty) { kittable += woLine.ActualIssueQty; } else { kittable += woLine.PlannedIssueQty; } } else if (remainStock == 0 && woLine.ActualIssueQty <= 0) { kittable += 0; } else if (remainStock > 0 && woLine.ActualIssueQty > 0) { if ((remainStock + woLine.ActualIssueQty) >= woLine.PlannedIssueQty) { kittable += woLine.PlannedIssueQty; } else { kittable += (remainStock + woLine.ActualIssueQty); } } else { kittable += remainStock; } if (woLine.ActualIssueQty > 0.01m && kittable > 0.0m) { pnf = kittable - woLine.ActualKitNeed; } var line = new WOLineKittableResultSet { WONumber = woLine.WorksOrderNumber, ActualIssueQty = woLine.ActualIssueQty, ActualKitNeed = woLine.ActualKitNeed, PartNumber = woLine.CompPartNumber, CommercialNotes = woLine.CommercialNotes, BatchNotes = woLine.BatchNotes, RespCode = woLine.RespCode, PlannedIssueDate = woLine.PlannedIssueDate, PlannedIssueQty = woLine.PlannedIssueQty, Kittable = kittable, PNF = pnf, OverKitted = overkitted }; woLineList.Add(line); ++ctr2; if (ctr2 == 1 || ctr2 % 1000 == 0) { Console.WriteLine("--- " + DateTime.Now + " sitting at " + ctr2 + " lines processed. ---"); } } woLineList.GroupBy(y => y.WONumber).ToList().ForEach( wo => { WOKittingGroupedExport export = new WOKittingGroupedExport(); export.WONumber = wo.First().WONumber; export.ResponsibilityCode = wo.First().RespCode; export.WOTotalPlannedQty = allWOQuantities.First(x => x.WorksOrderNumber == wo.First().WONumber).WOTotalPlannedQty.Value; export.TotalPlannedQty = wo.Sum(x => x.PlannedIssueQty); export.TotalActualQty = wo.Sum(x => x.ActualIssueQty); export.TotalKittable = wo.Sum(x => x.Kittable); export.totalActualKitNeed = wo.Sum(x => x.ActualKitNeed); export.CommercialNotes = rgx.Replace(wo.First().CommercialNotes.Replace("\r", "").Replace("\n", "").ToLower(), ""); export.BatchNotes = rgx.Replace(wo.First().BatchNotes.Replace("\r", "").Replace("\n", "").ToLower(), ""); export.PartsNotFound = wo.Sum(x => x.PNF); export.OverKitted = wo.Sum(x => x.OverKitted); exports.Add(export); ++ctr; if (ctr == 1 || ctr % 1000 == 0) { Console.WriteLine("--- " + DateTime.Now + " sitting at " + ctr + " lines processed. ---"); } } ); Console.WriteLine("Begin Closed"); closedWOLines.GroupBy(y => y.WorksOrderNumber).ToList().ForEach(wo => { var val = wo.Sum(x => x.PlannedIssueQty); WOKittingGroupedExport export = new WOKittingGroupedExport(); export.WONumber = wo.First().WorksOrderNumber; export.ResponsibilityCode = wo.First().RespCode; export.WOTotalPlannedQty = closedWOQuantities.First(x => x.WorksOrderNumber == wo.First().WorksOrderNumber).WOTotalPlannedQty.Value; export.TotalPlannedQty = val; export.TotalActualQty = val; export.TotalKittable = val; export.totalActualKitNeed = val; export.CommercialNotes = rgx.Replace(wo.First().CommercialNotes.Replace("\r", "").Replace("\n", "").ToLower(), ""); export.BatchNotes = rgx.Replace(wo.First().BatchNotes.Replace("\r", "").Replace("\n", "").ToLower(), ""); export.PartsNotFound = 0; export.OverKitted = 0; exports.Add(export); }); try { Console.WriteLine("Begin 1st Copy"); //copy to db table CopyWOKittableToDB(exports, setUID, todayDate); //end copy Console.WriteLine("End 1st Copy"); } catch (Exception ex) { Console.WriteLine(ex.Message + ex.InnerException); SendMail("Fail 1st Copy", "Failure"); } try { Console.WriteLine("Begin 2nd Copy"); //copy to db table CopyWOLineKittableToDB(woLineList, setLineUID, todayDate); //end copy Console.WriteLine("End 2nd Copy"); } catch (Exception ex) { Console.WriteLine(ex.Message + ex.InnerException); SendMail("Fail 2nd Copy", "Failure"); } string excelName = "WOKittableReport_" + DateTime.Now.ToString("dd-MM-yy HH.mm.ss tt"); FileInfo fileInfo2; string theDate2 = DateTime.Now.ToString("yyyyMMdd"); string theDateHours2 = DateTime.Now.ToString("yyyyMMdd HH.mm.ss"); if (CreateDirectoryStructure(out fileInfo2, theDate2, theDateHours2, @"WOKittable", @"MRP Standup Reports", false)) //MRP Standup Reports { using (ExcelPackage excelPackage = new ExcelPackage(fileInfo2)) { var workSheet = excelPackage.Workbook.Worksheets.Add("AllKittable"); workSheet.Cells["A1"].LoadFromCollection(exports, true, OfficeOpenXml.Table.TableStyles.Medium2); int rowCount = workSheet.Dimension.Rows; workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns(); workSheet.View.ZoomScale = 75; excelPackage.Save(); } } } catch (Exception ex) { Console.WriteLine("Problem retreiving closed wo lines & quantities..." + ex.Message + ex.InnerException); SendMail("Failed Populate", "Failure"); } } } Console.WriteLine("Completed Kittable Operation..." + DateTime.Now); } }