Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Stopwatch sw = new Stopwatch();
- sw.Start();
- DateTime startDate = new DateTime(2023, 4, 1);
- DateTime endDate = new DateTime(2023, 7, 1); //not inclusive
- string? RM_Lot = null;
- List<ExcelLine> elList;
- using SyteLineHelper h = new();
- //GROUP A
- List<LotList> lotLists = h.GetLotList(startDate, endDate, RM_Lot, config);
- foreach (LotList ll in lotLists.OrderBy(x => x.RM_TransDate).ThenBy(x => x.RM_Lot))
- {
- elList = new();
- Console.WriteLine("***********BEGIN LOT************");
- Console.WriteLine(string.Concat("RM Lot: ", ll.RM_Lot, ", RM Item: ", ll.RM_Item));
- row += 1;
- int rm_lot_row = row;
- ws.Cell(row, "A").Value = ll.RM_Lot;
- ws.Cell(row, "B").Value = ll.RM_TransDate.ToString("yyyy-MM-dd");
- ws.Cell(row, "C").Value = ll.RM_Item;
- ws.Cell(row, "D").Value = ll.RM_ItemDesc;
- ws.Cell(row, "E").Value = ll.RM_CommodityCode;
- string tracker = ll.RM_Lot;
- int level = 0;
- int oldRow = row;
- Guid id = Guid.NewGuid();
- Recurse(ll.RM_Lot, ll.RM_Lot, tracker, level, id);
- row = oldRow;
- WriteExcelLinesPass1(ll.RM_USD_FXRate);
- WriteExcelLinesPass2();
- WriteExcelLinesPass3();
- Console.WriteLine("***********END LOT************");
- Console.WriteLine();
- }
- //ws.Columns().AdjustToContents();
- // Freeze rows and columns in one shot
- ws.SheetView.Freeze(1, 1);
- ws.SheetView.ZoomScale = 85;
- var filePath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Test.xlsx");
- sw.Stop();
- TimeSpan timeTaken = sw.Elapsed;
- string foo = "Time taken: " + timeTaken.ToString(@"hh\:mm\:ss\.fff");
- Console.WriteLine(foo);
- workbook.SaveAs(filePath);
- ProcessStartInfo psInfo = new ProcessStartInfo
- {
- FileName = filePath,
- UseShellExecute = true
- };
- Process.Start(psInfo);
- Console.ReadLine();
- void ScrapSection(string lot, int row, IConfiguration config)
- {
- int scrap_row = row;
- List<ScrapList> scrapLists = h.GetScrapList(startDate, endDate, lot, config);
- foreach (ScrapList scrap in scrapLists)
- {
- ws.Cell(scrap_row, "AP").Value = scrap.Scrap_Qty; //Qty Scrapped/Written Off
- ws.Cell(scrap_row, "AQ").Value = scrap.Scrap_Amt; //Amt Scrapped/Written Off
- ws.Cell(scrap_row, "AR").Value = scrap.Item; //FG Qtm Item
- ws.Cell(scrap_row, "AS").Value = scrap.Lot; //FG Qty Lot
- ws.Cell(scrap_row, "AT").Value = scrap.Ref_Item; //Qty Ref Item
- scrap_row +=1;
- }
- }
- string Recurse(string RM_Lot, string lot, string tracker, int level, Guid parentId)
- {
- List<MatlTranList> mtList = h.GetMatlTranByLotList(startDate, endDate, lot, config);
- foreach (MatlTranList mt in mtList.OrderBy(x => x.CategorySort))
- {
- Console.WriteLine(String.Concat("Lot: ", RM_Lot, " Recurse Level: ", level));
- ExcelLine el = new();
- //row +=1;
- el.Id = Guid.NewGuid();
- el.ParentId = parentId;
- el.Row = 0;
- el.RMLot = RM_Lot;
- el.Level = level;
- switch (mt.Category)
- {
- case "Ship":
- el.CategoryType = Enums.CategoryType.Ship;
- break;
- case "Production":
- el.CategoryType = Enums.CategoryType.Production;
- break;
- case "External Receipts":
- el.CategoryType = Enums.CategoryType.ExternalReceipts;
- break;
- case "Internal Receipts":
- el.CategoryType = Enums.CategoryType.InternalReceipts;
- break;
- case "Scrap":
- el.CategoryType = Enums.CategoryType.Scrap;
- break;
- default:
- el.CategoryType = Enums.CategoryType.Other;
- break;
- }
- el.CategoryDesc = mt.Category;
- el.CategorySort = mt.CategorySort;
- el.Lot = mt.Lot;
- el.RefNum = mt.RefNum;
- el.RefLineSuf = mt.RefLineSuf;
- el.PackNumList = mt.PackNumList;
- el.Tracker = tracker;
- el.Item = mt.Item;
- el.UM = mt.UM;
- el.RMQty = 0;
- el.RMAmt = 0;
- el.QtyFactor = 0;
- el.QtyFactor = 0;
- el.QtyConsumed = mt.QtyConsumed;
- el.AmtConsumed = mt.AmtConsumed;
- if (mt.Category==null)
- {
- throw new ApplicationException("No Category!");
- }
- else if (el.CategoryType == Enums.CategoryType.Production)
- {
- string refNum = "000" + mt.RefLineSuf;
- string refLot = string.Concat(mt.RefNum, "-", refNum.Right(4));
- Recurse(RM_Lot, refLot, tracker + "|" + refLot, level + 1, el.Id);
- }
- elList.Add(el);
- }
- return tracker;
- }
- void WriteExcelLinesPass1(decimal fxRate)
- {
- foreach (ExcelLine el in elList
- .OrderBy(x => x.CategorySort)
- .ThenBy(x => x.CategoryType)
- .ThenBy(x => x.Level)
- .ThenBy(x => x.Tracker))
- {
- Console.WriteLine(String.Concat("Pass: 1 Row: ", row, " Level: ", el.Level));
- el.Row = row;
- ws.Cell(row, "A").Value = el.RMLot;
- ws.Cell(row, "G").Value = el.Level;
- ws.Cell(row, "H").Value = el.CategoryDesc;
- ws.Cell(row, "I").Value = el.Lot;
- ws.Cell(row, "J").Value = el.RefNum;
- ws.Cell(row, "K").Value = el.RefLineSuf;
- ws.Cell(row, "L").Value = el.Tracker;
- ws.Cell(row, "M").Value = el.Item;
- ws.Cell(row, "N").Value = el.UM;
- ws.Cell(row, COL_RMQty).FormulaA1 = string.Concat(COL_QtyConsumed, row);
- ws.Cell(row, COL_RMAmt).FormulaA1 = string.Concat(COL_AmtConsumed, row);
- ws.Cell(row, COL_QtyFactor).Value = 1; //Qty Factor
- ws.Cell(row, COL_AmtFactor).Value = 1; //Amt Factor
- ws.Cell(row, COL_QtyConsumed).Value = el.QtyConsumed;
- ws.Cell(row, COL_AmtConsumed).Value = el.AmtConsumed;
- ws.Cell(row, COL_USDFXRate).Value = fxRate;
- ws.Cell(row, COL_PackNumList).Value = el.PackNumList;
- if (el.CategoryType == Enums.CategoryType.Ship)
- {
- FGShippedList fgs = h.GetFGShippedListByCustomerOrderLine(startDate, endDate, el.RefNum, el.RefLineSuf, el.Lot, config).FirstOrDefault();
- ws.Cell(row, "A").Value = el.RMLot; //A
- ws.Cell(row, "V").Value = fgs.Customer; //Customer
- ws.Cell(row, "W").Value = fgs.ShipTo; //CustomerShipTo
- ws.Cell(row, "X").Value = fgs.Country; //Country
- ws.Cell(row, "Y").Value = fgs.Is_EU ? "Y" : "N"; //EU
- ws.Cell(row, "Z").Value = fgs.FG_CommCode; //FG_CommCode
- ws.Cell(row, "AA").Value = fgs.Is_ExportCommCode ? "Y" : "N"; //ValidExportCommCode
- ws.Cell(row, "AB").Value = fgs.Shipper; //Shipper
- ws.Cell(row, "AC").Value = fgs.Ship_Date.ToString("yyyy-MM-dd"); //Shipper
- ws.Cell(row, "AC").Value = fgs.Ship_Date.ToString("yyyy-MM-dd"); //Shipper
- ws.Cell(row, COL_ShippedLotQty).Value = fgs.ShippedLotQty; // 42.12345;
- ws.Cell(row, COL_COLineUnitPrice).Value = fgs.COLineUnitPrice; //42.1234;
- ws.Cell(row, COL_COCurrCode).Value = fgs.COCurrCode; //"USD";
- ws.Cell(row, COL_COFXRate).Value = fgs.COFXRate;
- }
- row += 1;
- }
- }
- void WriteExcelLinesPass2()
- {
- foreach (ExcelLine el in elList
- .Where(x => x.CategoryType != Enums.CategoryType.InternalReceipts)
- .OrderBy(x => x.Row))
- {
- Console.WriteLine(String.Concat("Pass: 2 Row: ", el.Row, " Level: ", el.Level));
- //For every tran type other than Finish,
- //use Finish tran type qty and amount factor to determine RM qty and amount
- int? finishRow = GetFinishRowNumber(el.ParentId); // ???
- if (finishRow != null)
- {
- ws.Cell(el.Row, COL_RMQty).FormulaA1 = String.Concat(COL_QtyConsumed, el.Row, "/", "$", COL_QtyFactor, "$", finishRow);
- ws.Cell(el.Row, COL_RMAmt).FormulaA1 = String.Concat(COL_AmtConsumed, el.Row, "/", "$", COL_AmtFactor, "$", finishRow);
- }
- else
- {
- ws.Cell(el.Row, COL_RMQty).FormulaA1 = String.Concat(COL_QtyConsumed, el.Row);
- ws.Cell(el.Row, COL_RMAmt).FormulaA1 = String.Concat(COL_AmtConsumed, el.Row);
- }
- }
- }
- void WriteExcelLinesPass3()
- {
- foreach (ExcelLine el in elList
- .Where(x => x.CategoryType == Enums.CategoryType.InternalReceipts)
- .OrderBy(x => x.Row))
- {
- Console.WriteLine(String.Concat("Pass: 3 Row: ", el.Row, " Level: ", el.Level));
- //For every "Finish" Tran Type, read RM qty and RM amount based on the consumption of predecessor lot consumption factor.
- //Then determine the Qty and Amount Factor
- GetPredessorLotConsumption2(el.ParentId, el.RefNum, el.RefLineSuf);
- ws.Cell(el.Row, COL_QtyFactor).FormulaA1 = String.Concat(COL_QtyConsumed, el.Row, "/", COL_RMQty, el.Row);
- ws.Cell(el.Row, COL_AmtFactor).FormulaA1 = String.Concat(COL_AmtConsumed, el.Row, "/", COL_RMAmt, el.Row);
- }
- }
- int? GetFinishRowNumber(Guid parentId)
- {
- int? row = null;
- ExcelLine el = elList.Where(x => x.ParentId == parentId
- && x.CategoryType == Enums.CategoryType.InternalReceipts).FirstOrDefault();
- if (el != null)
- {
- row = el.Row;
- }
- return row;
- }
- void GetPredessorLotConsumption2(Guid parentId, string refNumber, int RefLineSuf)
- {
- int? finishRow = GetFinishRowNumber(parentId);
- string s1 = string.Empty;
- string s2 = string.Empty;
- foreach (ExcelLine el in elList.Where(x => x.Id == parentId &&
- x.RefNum == refNumber &&
- x.RefLineSuf == RefLineSuf &&
- x.CategoryType == Enums.CategoryType.Production))
- {
- s1 += string.Concat(COL_RMQty, el.Row, ",");
- s2 += string.Concat(COL_RMAmt, el.Row, ",");
- }
- if (finishRow != null)
- {
- ws.Cell(finishRow.Value, COL_RMQty).FormulaA1 = string.Concat("-1*SUM(", s1.TrimEnd(','), ")");
- ws.Cell(finishRow.Value, COL_RMAmt).FormulaA1 = string.Concat("-1*SUM(", s2.TrimEnd(','), ")");
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement