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