Bulk Update Item Mst

[HttpGet("{dbname}")]
public string BulkUpdateItemmstDownloadModel(string dbname)
{
    Random rnd = new();
    var randomnumber = rnd.Next(1000000000).ToString().Trim();
    var randomfilename = $"{randomnumber}.xlsx";

    string pathdirname = $"\\MFGReports\\Reports\\{dbname}";
    if (!Directory.Exists(pathdirname))
    {
        Directory.CreateDirectory(pathdirname);
    }

    var pathname = $"{pathdirname}\\{randomfilename}";
    var pathfilename = Path.GetFullPath(pathname);

    using var WorkbookXL = new XLWorkbook();

    var worksheet = WorkbookXL.Worksheets.Add("Update_Item_Master_Model");
    worksheet.SheetView.FreezeRows(1);

    worksheet.Cell(1, 1).Value = "Item Id";
    worksheet.Cell(1, 2).Value = "Item Group";
    worksheet.Cell(1, 3).Value = "Segment";
    worksheet.Cell(1, 4).Value = "Item Name";
    worksheet.Cell(1, 5).Value = "Item Short Name";
    worksheet.Cell(1, 6).Value = "GST Rate";
    worksheet.Cell(1, 7).Value = "Standard UOM";
    worksheet.Cell(1, 8).Value = "Alternate UOM";
    worksheet.Cell(1, 9).Value = "Conversion Factor Uom1->Uom2";
    worksheet.Cell(1, 10).Value = "Standard Packing";
    worksheet.Cell(1, 11).Value = "Standard Weight";
    worksheet.Cell(1, 12).Value = "Re-order Level";
    worksheet.Cell(1, 13).Value = "Maximum Order Level";
    worksheet.Cell(1, 14).Value = "Minimum Order Quantity";
    worksheet.Cell(1, 15).Value = "Lead Time";
    worksheet.Cell(1, 16).Value = "HSN Code";
    worksheet.Cell(1, 17).Value = "Category";
    worksheet.Cell(1, 18).Value = "Classification (Class Id)";
    worksheet.Cell(1, 19).Value = "Is Item released for sales?";
    worksheet.Cell(1, 20).Value = "High Priority";

    worksheet.Range(1, 1, 1, 20).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
    worksheet.Range(1, 1, 1, 20).Style.Font.Bold = true;
    worksheet.Range(1, 1, 1, 20).Style.Border.TopBorder = XLBorderStyleValues.Thin;
    worksheet.Range(1, 1, 1, 20).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
    worksheet.Range(1, 1, 1, 20).Style.Border.RightBorder = XLBorderStyleValues.Thin;
    worksheet.Range(1, 1, 1, 20).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

    // Get all active items and populate the template
    var activeItems = _IItemMstRepository.GetActiveItemsForBulkUpdate(dbname);

    int row = 2; // Start from row 2 (after header)
    foreach (var item in activeItems)
    {
    worksheet.Cell(row, 1).Value = item.itemid;
    worksheet.Cell(row, 2).Value = item.itemgroupid;
    worksheet.Cell(row, 3).Value = item.segmentcode;
    worksheet.Cell(row, 4).Value = item.itemname;
    worksheet.Cell(row, 5).Value = item.shortname;
    worksheet.Cell(row, 6).Value = item.gstcode;
    worksheet.Cell(row, 7).Value = item.uomid;
    worksheet.Cell(row, 8).Value = item.uom1id;
    worksheet.Cell(row, 9).Value = item.nfactor;
    worksheet.Cell(row, 10).Value = item.stdpkg;
    worksheet.Cell(row, 11).Value = item.stdweight;
    worksheet.Cell(row, 12).Value = item.rol;
    worksheet.Cell(row, 13).Value = item.maxol;
    worksheet.Cell(row, 14).Value = item.eoq;
    worksheet.Cell(row, 15).Value = item.leadtime;
    worksheet.Cell(row, 16).Value = item.hsncode;
    worksheet.Cell(row, 17).Value = item.categoryid;
    worksheet.Cell(row, 18).Value = item.classid;
    worksheet.Cell(row, 19).Value = item.releasestatus;
    worksheet.Cell(row, 20).Value = item.iscritical;
    row++;
    }

    worksheet.Column(9).Style.NumberFormat.Format = "##0.0000;[Red](##0.0000)";//conversion factor
    worksheet.Column(10).Style.NumberFormat.Format = "##0.000;[Red](##0.000)";//std pkg
    worksheet.Column(11).Style.NumberFormat.Format = "##0.000;[Red](##0.000)";//std weight
    worksheet.Column(12).Style.NumberFormat.Format = "##0;[Red](##0)";//reorder level
    worksheet.Column(13).Style.NumberFormat.Format = "##0;[Red](##0)";//max order level
    worksheet.Column(14).Style.NumberFormat.Format = "##0.000;[Red](##0.000)";//min order qty
    worksheet.Column(15).Style.NumberFormat.Format = "##0;[Red](##0)";//lead time

    worksheet.Columns().AdjustToContents(4);
            

    WorkbookXL.SaveAs(pathfilename);

    return randomfilename;
}

[HttpGet("{dbname},{xlfile},{yearlabel},{username}")]
public string BulkUpdateItemmst(string dbname, string xlfile, string yearlabel, string username)
{
    var xlfileresult = " ";

    Random rnd = new();
    var randomnumber = rnd.Next(1000000000).ToString().Trim();
    var randomfilename = $"{randomnumber}.xlsx";

    // for generating system generated excel
    string pathdirname1 = $"\\MFGReports\\Reports\\{dbname}";
    var pathname1 = $"{pathdirname1}\\{randomfilename}";
    var pathfilename1 = Path.GetFullPath(pathname1);

    // for reading user excel data
    var pathname2 = $"{pathdirname1}\\{xlfile}";
    var pathfilename2 = Path.GetFullPath(pathname2);

    using var WorkbookXL = new XLWorkbook(pathfilename2);
    var worksheet = WorkbookXL.Worksheets.Worksheet(1);

    var rowcount = worksheet.LastRowUsed().RowNumber();
    var columncount = worksheet.LastColumnUsed().ColumnNumber();
    var firstcolname = worksheet.FirstColumnUsed().FirstCellUsed().GetString().ToLower();
    var lastcolname = worksheet.LastColumnUsed().FirstCellUsed().GetString().ToLower();

    // 20 define total no. of columns hardcoded
    if (columncount != 20 || firstcolname != "item id" || lastcolname != "high priority")
    {
        xlfileresult = "1"; //file is not in correct format
    }
    else if (rowcount == 1)
    {
        xlfileresult = "2"; // file does not contain any line
    }
    else
    {
        int row = 2; // skip 1st header
        var error = " ";
        List<ItemMst> listdata = new();

        while (row <= rowcount)
        {
            // hardcoded columns are passed
            var itemIdCol = worksheet.Cell(row, 1);
            var itemGroupCodeCol = worksheet.Cell(row, 2);
            var itemSegmentCodeCol = worksheet.Cell(row, 3);
            var itemNameCol = worksheet.Cell(row, 4);
            var itemShortNameCol = worksheet.Cell(row, 5);
            var gstRateCodeCol = worksheet.Cell(row, 6);
            var stdUomCodeCol = worksheet.Cell(row, 7);
            var alternateUomCodeCol = worksheet.Cell(row, 8);
            var conversionFactorCol = worksheet.Cell(row, 9);
            var stdPackingCol = worksheet.Cell(row, 10);
            var stdWeightCol = worksheet.Cell(row, 11);
            var reOrderLevelCol = worksheet.Cell(row, 12);
            var maxOrderLevelCol = worksheet.Cell(row, 13);
            var minOrderQtyCol = worksheet.Cell(row, 14);
            var itemLeadTimeCol = worksheet.Cell(row, 15);
            var hsnCodeCol = worksheet.Cell(row, 16);
            var itemCategoryCodeCol = worksheet.Cell(row, 17);
            var itemClassCodeCol = worksheet.Cell(row, 18);
            var releaseStatusCol = worksheet.Cell(row, 19);
            var isCriticalCol = worksheet.Cell(row, 20);

            // Z1: All required fields should not be empty
            if (itemIdCol.GetString().Length == 0 || itemNameCol.GetString().Length == 0 ||
                itemShortNameCol.GetString().Length == 0 || itemGroupCodeCol.GetString().Length == 0 ||
                itemSegmentCodeCol.GetString().Length == 0 || gstRateCodeCol.GetString().Length == 0 ||
                stdUomCodeCol.GetString().Length == 0 || alternateUomCodeCol.GetString().Length == 0 ||
                itemCategoryCodeCol.GetString().Length == 0 || itemClassCodeCol.GetString().Length == 0 ||
                hsnCodeCol.GetString().Length == 0)
            {
                error = "Z1";
            }

            // Z2: Item Id should be 6 digits
            if (itemIdCol.GetString().Length > 6)
            {
                error = "Z2" + " " + error;
            }

            // Z3: All 2-digit code fields should be exactly 2 digits
            if (gstRateCodeCol.GetString().Length > 2 || stdUomCodeCol.GetString().Length > 2 ||
                alternateUomCodeCol.GetString().Length > 2 || itemCategoryCodeCol.GetString().Length > 2 ||
                itemClassCodeCol.GetString().Length > 2)
            {
                error = "Z3" + " " + error;
            }

            // Z4: All 3-digit code fields should be valid 3 digits
            if (itemGroupCodeCol.GetString().Length > 3 || itemSegmentCodeCol.GetString().Length > 3)
            {
                error = "Z4" + " " + error;
            }

            // Z5: All text fields should not exceed their length limits
            if (itemShortNameCol.GetString().Length > 100 || itemNameCol.GetString().Length > 150)
            {
                error = "Z5" + " " + error;
            }

            // Z6: All Y/N fields should be Y or N
            if ((releaseStatusCol.GetString() != "Y" && releaseStatusCol.GetString() != "N") ||
                (isCriticalCol.GetString() != "Y" && isCriticalCol.GetString() != "N"))
            {
                error = "Z6" + " " + error;
            }

            // Z7: All numeric fields should be valid numbers
            if (Convert.ToString(conversionFactorCol.DataType) != "Number" ||
                Convert.ToString(reOrderLevelCol.DataType) != "Number" ||
                Convert.ToString(minOrderQtyCol.DataType) != "Number" ||
                Convert.ToString(itemLeadTimeCol.DataType) != "Number" ||
                Convert.ToString(stdPackingCol.DataType) != "Number" ||
                Convert.ToString(stdWeightCol.DataType) != "Number" ||
                Convert.ToString(maxOrderLevelCol.DataType) != "Number")
            {
                error = "Z7" + " " + error;
            }
            else
            {
                // Z8: Conversion Factor should be within range (0-9999.9999)
                if (conversionFactorCol.GetDouble() < 0 || conversionFactorCol.GetDouble() > 9999.9999)
                {
                    error = "Z8" + " " + error;
                }

                // Z9: Re-Order Level should be within range (1-999999)
                if (reOrderLevelCol.GetDouble() < 1 || reOrderLevelCol.GetDouble() > 999999)
                {
                    error = "Z9" + " " + error;
                }

                // Z10: Min Order Qty should be within range (1-999999)
                if (minOrderQtyCol.GetDouble() < 1 || minOrderQtyCol.GetDouble() > 999999)
                {
                    error = "Z10" + " " + error;
                }

                // Z11: Lead Time should be within range (0-180)
                if (itemLeadTimeCol.GetDouble() < 0 || itemLeadTimeCol.GetDouble() > 180)
                {
                    error = "Z11" + " " + error;
                }

                // Z12: Standard Packing should be within range (1-999999999.999)
                if (stdPackingCol.GetDouble() < 1 || stdPackingCol.GetDouble() > 999999999.999)
                {
                    error = "Z12" + " " + error;
                }

                // Z13: Standard Weight should be within range (1-9999)
                if (stdWeightCol.GetDouble() < 1 || stdWeightCol.GetDouble() > 9999)
                {
                    error = "Z13" + " " + error;
                }

                // Z14: Max Order Level should be within range (0-999999)
                if (maxOrderLevelCol.GetDouble() < 0 || maxOrderLevelCol.GetDouble() > 999999)
                {
                    error = "Z14" + " " + error;
                }
            }

            if (error != " ")
            {
                // define last column for error code
                worksheet.Cell(row, 21).Value = error;
                error = "";
            }
            else
            {
                listdata.Add(new ItemMst
                {
                    ItemId = itemIdCol.GetString(),
                    ItemGroupCode = itemGroupCodeCol.GetString(),
                    ItemSegmentCode = itemSegmentCodeCol.GetString(),
                    ItemName = itemNameCol.GetString(),
                    ItemShortName = itemShortNameCol.GetString(),
                    GstRateCode = gstRateCodeCol.GetString(),
                    StdUomCode = stdUomCodeCol.GetString(),
                    AlternateUomCode = alternateUomCodeCol.GetString(),
                    ConversionFactor = (decimal)conversionFactorCol.GetDouble(),
                    StdPacking = (int)stdPackingCol.GetDouble(),
                    StdWeight = (decimal)stdWeightCol.GetDouble(),
                    ReOrderLevel = (int)reOrderLevelCol.GetDouble(),
                    MaxOrderLevel = (int)maxOrderLevelCol.GetDouble(),
                    MinOrderQty = (int)minOrderQtyCol.GetDouble(),
                    ItemLeadTime = (decimal)itemLeadTimeCol.GetDouble(),
                    HsnCode = hsnCodeCol.GetString(),
                    ItemCategoryCode = itemCategoryCodeCol.GetString(),
                    ItemClassCode = itemClassCodeCol.GetString(),
                    ReleaseStatus = releaseStatusCol.GetString(),
                    IsCritical = isCriticalCol.GetString()
                });
            }

            row++;
        }

        worksheet.SheetView.FreezeRows(1);

        worksheet.Cell(1, 21).Value = "Error Code";
        worksheet.Cell(row + 1, 1).Value = "Import could not succeed because of error";

        worksheet.Range(1, 1, 1, 21).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
        worksheet.Range(1, 1, 1, 21).Style.Font.Bold = true;
        worksheet.Cell(row + 1, 1).Style.Font.Bold = true;
        worksheet.Range(row + 1, 1, row + 1, 1).Style.Fill.BackgroundColor = XLColor.Yellow;

        worksheet.Range(1, 1, 1, 21).Style.Border.TopBorder = XLBorderStyleValues.Thin;
        worksheet.Range(1, 1, 1, 21).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
        worksheet.Range(1, 1, 1, 21).Style.Border.RightBorder = XLBorderStyleValues.Thin;

        worksheet.Range(1, 1, 1, 21).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

        worksheet.Column(9).Style.NumberFormat.Format = "##0.0000;[Red](##0.0000)";//conversion factor
        worksheet.Column(10).Style.NumberFormat.Format = "##0.000;[Red](##0.000)";//std pkg
        worksheet.Column(11).Style.NumberFormat.Format = "##0.000;[Red](##0.000)";//std weight
        worksheet.Column(12).Style.NumberFormat.Format = "##0;[Red](##0)";//reorder level
        worksheet.Column(13).Style.NumberFormat.Format = "##0;[Red](##0)";//max order level
        worksheet.Column(14).Style.NumberFormat.Format = "##0.000;[Red](##0.000)";//min order qty
        worksheet.Column(15).Style.NumberFormat.Format = "##0;[Red](##0)";//lead time
        worksheet.Columns().AdjustToContents(1);

        // ---------------------------------------------------------------------------

        var worksheet1 = WorkbookXL.Worksheets.Add("Error_Code");

        worksheet1.Cell(1, 1).Value = "Error Code";
        worksheet1.Cell(1, 2).Value = "Description";
        worksheet1.Cell(2, 1).Value = "Z1";
        worksheet1.Cell(2, 2).Value = "Required fields cannot be empty";
        worksheet1.Cell(3, 1).Value = "Z2";
        worksheet1.Cell(3, 2).Value = "Item Id must be exactly 6 digits";
        worksheet1.Cell(4, 1).Value = "Z3";
        worksheet1.Cell(4, 2).Value = "GST Rate, Standard UOM, Alternate UOM, Category, Class must be exactly 2 digits";
        worksheet1.Cell(5, 1).Value = "Z4";
        worksheet1.Cell(5, 2).Value = "Item Group must be exactly 3 digits, Segment cannot exceed 3 digits";
        worksheet1.Cell(6, 1).Value = "Z5";
        worksheet1.Cell(6, 2).Value = "Item Name can be upto 150 characters, Short Name can be upto 100 characters";
        worksheet1.Cell(7, 1).Value = "Z6";
        worksheet1.Cell(7, 2).Value = "Is item release status needs to be selected, High Priority needs to be selected";
        worksheet1.Cell(8, 1).Value = "Z7";
        worksheet1.Cell(8, 2).Value = "Numeric fields must be valid numbers";
        worksheet1.Cell(9, 1).Value = "Z8";
        worksheet1.Cell(9, 2).Value = "Conversion Factor can only be between 0-9999.9999";
        worksheet1.Cell(10, 1).Value = "Z9";
        worksheet1.Cell(10, 2).Value = "Re-Order Level can only be between 1 to 999999";
        worksheet1.Cell(11, 1).Value = "Z10";
        worksheet1.Cell(11, 2).Value = "Min Order Qty can only be between 1 to 999999";
        worksheet1.Cell(12, 1).Value = "Z11";
        worksheet1.Cell(12, 2).Value = "Leadtime can only be between 0-180";
        worksheet1.Cell(13, 1).Value = "Z12";
        worksheet1.Cell(13, 2).Value = "Standard Packing must be between 1-999999999.999";
        worksheet1.Cell(14, 1).Value = "Z13";
        worksheet1.Cell(14, 2).Value = "Standard Weight can only be between 1-9999";
        worksheet1.Cell(15, 1).Value = "Z14";
        worksheet1.Cell(15, 2).Value = "Max Order Level can only be between 0-999999";

        worksheet1.Range(1, 1, 1, 2).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
        worksheet1.Range(1, 1, 1, 2).Style.Font.Bold = true;

        worksheet1.Range(1, 1, 1, 2).Style.Border.TopBorder = XLBorderStyleValues.Thin;
        worksheet1.Range(1, 1, 1, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
        worksheet1.Range(1, 1, 1, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;

        worksheet1.Range(1, 1, 1, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

        worksheet1.Columns().AdjustToContents(1);
        worksheet1.Column(2).Width = 40;
        worksheet1.Column(2).Style.Alignment.WrapText = true;

        if (error == " ")
        {
            var companyData = _IUtilityMethodsRepository.GetCompanyMstInfo(dbname).Result;
            var xldata = _IItemMstRepository.BulkUpdateItemmst(listdata, dbname, yearlabel, username);

            using var WorkbookXL1 = new XLWorkbook();
            var worksheet2 = WorkbookXL1.Worksheets.Add("Bulk_Update_Item_Master");

            worksheet2.SheetView.FreezeRows(5);

            worksheet2.Cell(1, 1).Value = "Company Name : " + companyData.CompanyName;
            worksheet2.Cell(2, 1).Value = "Report Name : Bulk Update Item Master";
            worksheet2.Cell(3, 1).Value = "Report Date : " + DateTime.Now.Date.ToString("dd-MMM-yy");
            worksheet2.Cell(4, 1).Value = xldata.Item2;
            worksheet2.Cell(5, 1).Value = "Error Code";
            worksheet2.Cell(5, 2).Value = "Item Id";
            worksheet2.Cell(5, 3).Value = "Item Group Code";
            worksheet2.Cell(5, 4).Value = "Item Group Name";
            worksheet2.Cell(5, 5).Value = "Segment Code";
            worksheet2.Cell(5, 6).Value = "Segment Name";
            worksheet2.Cell(5, 7).Value = "Item Name";
            worksheet2.Cell(5, 8).Value = "Item Short Name";
            worksheet2.Cell(5, 9).Value = "GST Rate";
            worksheet2.Cell(5, 10).Value = "GST Rate Name";
            worksheet2.Cell(5, 11).Value = "Standard UOM Code";
            worksheet2.Cell(5, 12).Value = "Standard UOM Name";
            worksheet2.Cell(5, 13).Value = "Alternate UOM Code";
            worksheet2.Cell(5, 14).Value = "Alternate UOM Name";
            worksheet2.Cell(5, 15).Value = "Conversion Factor Uom1->Uom2";
            worksheet2.Cell(5, 16).Value = "Standard packing";
            worksheet2.Cell(5, 17).Value = "Standard weight";
            worksheet2.Cell(5, 18).Value = "Re-order level";
            worksheet2.Cell(5, 19).Value = "Maximum order level";
            worksheet2.Cell(5, 20).Value = "Minimum order quantity";
            worksheet2.Cell(5, 21).Value = "Lead time";
            worksheet2.Cell(5, 22).Value = "HSN / SAC Code";
            worksheet2.Cell(5, 23).Value = "Category Code";
            worksheet2.Cell(5, 24).Value = "Category Name";
            worksheet2.Cell(5, 25).Value = "Classification (Class Id)";
            worksheet2.Cell(5, 26).Value = "Item Class Name";
            worksheet2.Cell(5, 27).Value = "Is Item released for sales?";
            worksheet2.Cell(5, 28).Value = "High Priority";

            worksheet2.Range(5, 1, 5, 28).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
            worksheet2.Range(1, 1, 5, 28).Style.Font.Bold = true;
            worksheet2.Range(5, 1, 5, 28).Style.Border.TopBorder = XLBorderStyleValues.Thin;
            worksheet2.Range(5, 1, 5, 28).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
            worksheet2.Range(5, 1, 5, 28).Style.Border.RightBorder = XLBorderStyleValues.Thin;

            worksheet2.Range(5, 1, 5, 28).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

            worksheet2.Range(4, 1, 4, 4).Style.Fill.BackgroundColor = XLColor.Yellow;

            int i = 5;
            foreach (var row1 in xldata.Item1)
            {
                i++;
                worksheet2.Cell(i, 1).Value = row1.ErrorCode;
                worksheet2.Cell(i, 2).Value = row1.ItemId;
                worksheet2.Cell(i, 3).Value = row1.ItemGroupCode;
                worksheet2.Cell(i, 4).Value = row1.ItemGroupName;
                worksheet2.Cell(i, 5).Value = row1.ItemSegmentCode;
                worksheet2.Cell(i, 6).Value = row1.ItemSegmentName;
                worksheet2.Cell(i, 7).Value = row1.ItemName;
                worksheet2.Cell(i, 8).Value = row1.ItemShortName;
                worksheet2.Cell(i, 9).Value = row1.GstRateCode;
                worksheet2.Cell(i, 10).Value = row1.GstRateName;
                worksheet2.Cell(i, 11).Value = row1.StdUomCode;
                worksheet2.Cell(i, 12).Value = row1.StdUomName;
                worksheet2.Cell(i, 13).Value = row1.AlternateUomCode;
                worksheet2.Cell(i, 14).Value = row1.AlternateUomName;
                worksheet2.Cell(i, 15).Value = row1.ConversionFactor;
                worksheet2.Cell(i, 16).Value = row1.StdPacking;
                worksheet2.Cell(i, 17).Value = row1.StdWeight;
                worksheet2.Cell(i, 18).Value = row1.ReOrderLevel;
                worksheet2.Cell(i, 19).Value = row1.MaxOrderLevel;
                worksheet2.Cell(i, 20).Value = row1.MinOrderQty;
                worksheet2.Cell(i, 21).Value = row1.ItemLeadTime;
                worksheet2.Cell(i, 22).Value = row1.HsnCode;
                worksheet2.Cell(i, 23).Value = row1.ItemCategoryCode;
                worksheet2.Cell(i, 24).Value = row1.ItemCategoryName;
                worksheet2.Cell(i, 25).Value = row1.ItemClassCode;
                worksheet2.Cell(i, 26).Value = row1.ItemClassName;
                worksheet2.Cell(i, 27).Value = row1.ReleaseStatusName;
                worksheet2.Cell(i, 28).Value = row1.IsCriticalName;
            }

            worksheet2.Column(15).Style.NumberFormat.Format = "##0.0000;[Red](##0.0000)";//conversion factor
            worksheet2.Column(16).Style.NumberFormat.Format = "##0.000;[Red](##0.000)";//std pkg
            worksheet2.Column(17).Style.NumberFormat.Format = "##0.000;[Red](##0.000)";//std weight
            worksheet2.Column(18).Style.NumberFormat.Format = "##0;[Red](##0)";//reorder level
            worksheet2.Column(19).Style.NumberFormat.Format = "##0;[Red](##0)";//max order level
            worksheet2.Column(20).Style.NumberFormat.Format = "##0.000;[Red](##0.000)";//min order qty
            worksheet2.Column(21).Style.NumberFormat.Format = "##0;[Red](##0)";//lead time

            worksheet2.Columns().AdjustToContents(5);
            // groupname, segmentname, itemname, shortname, stdomname, alternate uomnmame, category name, class name
            int[] columnsToFormat1 = [ 4, 6, 7, 8, 12, 14,24, 26 ];

            foreach (int columnNumber in columnsToFormat1)
            {
                worksheet.Column(columnNumber).Width = 40;
                worksheet.Column(columnNumber).Style.Alignment.WrapText = true;
            }

            // -------------------------------------------------------------------------------

        var worksheet3 = WorkbookXL1.Worksheets.Add("Error_Code");
        worksheet3.Cell(1, 1).Value = "Error Code";
        worksheet3.Cell(1, 2).Value = "Description";
        worksheet3.Cell(2, 1).Value = "A";
        worksheet3.Cell(2, 2).Value = "Invalid Item Id";
        worksheet3.Cell(3, 1).Value = "B";
        worksheet3.Cell(3, 2).Value = "Duplicate Item Id in upload";
        worksheet3.Cell(4, 1).Value = "C";
        worksheet3.Cell(4, 2).Value = "Invalid Item Group Code";
        worksheet3.Cell(5, 1).Value = "D";
        worksheet3.Cell(5, 2).Value = "Invalid Segment Code";
        worksheet3.Cell(6, 1).Value = "E";
        worksheet3.Cell(6, 2).Value = "Invalid GST Rate Code";
        worksheet3.Cell(7, 1).Value = "F";
        worksheet3.Cell(7, 2).Value = "Invalid Standard UOM Code";
        worksheet3.Cell(8, 1).Value = "G";
        worksheet3.Cell(8, 2).Value = "Invalid Alternate UOM Code";
        worksheet3.Cell(9, 1).Value = "H";
        worksheet3.Cell(9, 2).Value = "Invalid HSN Code";
        worksheet3.Cell(10, 1).Value = "I";
        worksheet3.Cell(10, 2).Value = "Invalid Category Code";
        worksheet3.Cell(11, 1).Value = "J";
        worksheet3.Cell(11, 2).Value = "Invalid Class Code";

            worksheet3.Range(1, 1, 1, 2).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
            worksheet3.Range(1, 1, 1, 2).Style.Font.Bold = true;

            worksheet3.Range(1, 1, 1, 2).Style.Border.TopBorder = XLBorderStyleValues.Thin;
            worksheet3.Range(1, 1, 1, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
            worksheet3.Range(1, 1, 1, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;

            worksheet3.Range(1, 1, 1, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

            worksheet3.Columns().AdjustToContents(1);
            worksheet3.Column(2).Width = 40;
            worksheet3.Column(2).Style.Alignment.WrapText = true;

            WorkbookXL1.SaveAs(pathfilename1);
            xlfileresult = randomfilename;
        }
        else
        {
            //data type error sheet
            WorkbookXL.SaveAs(pathfilename1);
            xlfileresult = randomfilename;
        }
    }
    System.IO.File.Delete(pathfilename2); // deleting file in specific path after reading
    return xlfileresult;
}

public Tuple<List<ItemMst>, string> BulkUpdateItemmst(List<ItemMst> listdata, string dbname, string yearlabel, string username)
{
    var tempcreation = @"CREATE TABLE #BulkUpdateItemmst(
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    itemid NVARCHAR(6) COLLATE DATABASE_DEFAULT,
    itemgroupid NVARCHAR(3) COLLATE DATABASE_DEFAULT,
    segmentcode NVARCHAR(3) COLLATE DATABASE_DEFAULT,
    itemname NVARCHAR(150) COLLATE DATABASE_DEFAULT,
    shortname NVARCHAR(100) COLLATE DATABASE_DEFAULT,
    gstcode NVARCHAR(2) COLLATE DATABASE_DEFAULT,
    uomid NVARCHAR(2) COLLATE DATABASE_DEFAULT,
    uom1id NVARCHAR(2) COLLATE DATABASE_DEFAULT,
    nfactor NUMERIC(11, 4),
    Stdpkg INT,
    Stdweight NUMERIC(11, 3),
    rol INT,
    maxol INT,
    eoq INT,
    leadtime NUMERIC(3, 0),
    Hsncode NVARCHAR(8) COLLATE DATABASE_DEFAULT,
    categoryid NVARCHAR(2) COLLATE DATABASE_DEFAULT,
    classid NVARCHAR(2) COLLATE DATABASE_DEFAULT,
    ReleaseStatus NVARCHAR(1) COLLATE DATABASE_DEFAULT,
    IsCritical NVARCHAR(1) COLLATE DATABASE_DEFAULT,
    errorcode NVARCHAR(20) COLLATE DATABASE_DEFAULT)";

    var inserttemp = @"INSERT INTO #BulkUpdateItemmst (itemid,itemgroupid,
    segmentcode,itemname,shortname,gstcode,uomid,uom1id,nfactor,Stdpkg,Stdweight,
    rol,maxol,eoq,leadtime,Hsncode,categoryid,classid,ReleaseStatus,IsCritical)
    VALUES (@itemid,@itemgroupid,@segmentcode,UPPER(TRIM(@itemname)),UPPER(TRIM(@shortname)),@gstcode,@uomid,@uom1id,
    @nfactor,@Stdpkg,@Stdweight,@rol,@maxol,@eoq,@leadtime,@Hsncode,@categoryid,@classid,UPPER(@ReleaseStatus),UPPER(@IsCritical))";

    // Invalid Item Id
    var errorcodeA = @"UPDATE #BulkUpdateItemmst SET errorcode = COALESCE(RTRIM(ERRORCODE), '') $ ' A'
    WHERE NOT EXISTS (SELECT itemid FROM Inventorymst WHERE #BulkUpdateItemmst.itemid = Inventorymst.itemid AND isactive = 'Y')";

    // Duplicate Item Id in upload
    var errorcodeB = @"WITH A(itemid, cnt) AS
    (
        SELECT itemid, COUNT(*) AS cnt FROM #BulkUpdateItemmst
        GROUP BY itemid
        HAVING COUNT(*)>1
    )
    UPDATE #BulkUpdateItemmst SET errorcode=COALESCE(RTRIM(ERRORCODE), '') $ ' B' FROM A
    WHERE A.itemid = #BulkUpdateItemmst.itemid";

    // Invalid Item Group Code
    var errorcodeC = @"UPDATE #BulkUpdateItemmst SET errorcode = COALESCE(RTRIM(ERRORCODE), '') $ ' C'
    WHERE NOT EXISTS (SELECT IG.groupcode FROM itemgroupmst IG
    WHERE #BulkUpdateItemmst.itemgroupid = IG.groupcode AND IG.groupname != 'NA' )";

    // Invalid Segment Code
    var errorcodeD = @"UPDATE #BulkUpdateItemmst SET ERRORCODE = COALESCE(RTRIM(ERRORCODE), '') $ ' D'
    WHERE NOT EXISTS (SELECT segmentcode FROM segmentmst
    WHERE #BulkUpdateItemmst.segmentcode = segmentmst.segmentcode )";

    // Invalid GST Rate Code
    var errorcodeE = @"UPDATE #BulkUpdateItemmst SET ERRORCODE = COALESCE(RTRIM(ERRORCODE), '') $ ' E'
    WHERE NOT EXISTS (SELECT gstcode FROM gstrates
    WHERE IsActive = 'Y' AND #BulkUpdateItemmst.gstcode = gstrates.gstcode) ";

    // Invalid Standard UOM Code
    var errorcodeF = @"UPDATE #BulkUpdateItemmst SET ERRORCODE = COALESCE(RTRIM(ERRORCODE), '') $ ' F'
    WHERE NOT EXISTS (SELECT uomcode FROM uom
    WHERE #BulkUpdateItemmst.uomid = uom.uomcode)";

    // Invalid Alternate UOM Code
    var errorcodeG = @"UPDATE #BulkUpdateItemmst SET ERRORCODE = COALESCE(RTRIM(ERRORCODE), '') $ ' G'
    WHERE NOT EXISTS (SELECT uomcode FROM uom
    WHERE #BulkUpdateItemmst.uom1id = uom.uomcode)";

    // Invalid HSN Code
    var errorcodeH = @"UPDATE #BulkUpdateItemmst SET errorcode = COALESCE(RTRIM(ERRORCODE), '') $ ' H'
    WHERE NOT EXISTS (SELECT hsncode FROM hsnmst H
    WHERE #BulkUpdateItemmst.hsncode = H.hsncode)";

    // Invalid Category Code
    var errorcodeI = @"UPDATE #BulkUpdateItemmst SET ERRORCODE = COALESCE(RTRIM(ERRORCODE), '') $ ' I'
    WHERE NOT EXISTS (SELECT categorycode FROM categorymst
    WHERE #BulkUpdateItemmst.categoryid = categorymst.categorycode )";

    // Invalid Class Code
    var errorcodeJ = @"UPDATE #BulkUpdateItemmst SET ERRORCODE = COALESCE(RTRIM(ERRORCODE), '') $ ' J'
    WHERE NOT EXISTS (SELECT classcode FROM Inventoryclass
    WHERE #BulkUpdateItemmst.classid = Inventoryclass.classcode)";

    var cnterror = @"SELECT COUNT(ERRORCODE) AS CNT FROM #BulkUpdateItemmst WHERE COALESCE(ERRORCODE,'') != ''";

    var updateinventorymst = @"UPDATE Inventorymst SET itemgroupid=T.itemgroupid,
    segmentcode=T.segmentcode, itemname=T.itemname, shortname=T.shortname, gstcode=T.gstcode,
    uomid=T.uomid, uom1id=T.uom1id, nfactor=T.nfactor, Stdpkg=T.Stdpkg, Stdweight=T.Stdweight,
    rol=T.rol, maxol=T.maxol, eoq=T.eoq, leadtime=T.leadtime, Hsncode=T.Hsncode,
    categoryid=T.categoryid, classid=T.classid, ReleaseStatus=T.ReleaseStatus, IsCritical=T.IsCritical
    FROM #BulkUpdateItemmst T
    WHERE Inventorymst.itemid = T.itemid";

    var data = @"SELECT I.itemid, I.itemgroupid AS itemgroupcode, COALESCE(IG.Groupname, 'Invalid Group Name') AS itemgroupname,
    I.segmentcode AS itemsegmentcode, COALESCE(S.description, 'Invalid Segment Name') AS itemsegmentname,
    I.itemname, I.shortname AS itemshortname, I.gstcode AS gstratecode,
    COALESCE(G.Gstname, 'Invalid GST Name') AS GstRateName, I.uomid AS Stduomcode,
    COALESCE(U.uomname, 'Invalid Standard UOM Name') AS StdUomName,
    I.uom1id AS alternateuomcode, COALESCE(U1.uomname, 'Invalid Alternate UOM') AS AlternateUomName,
    I.nfactor AS conversionfactor, I.stdpkg AS stdpacking,
    I.stdweight, I.rol AS reorderlevel, I.maxol AS maxorderlevel,
    I.eoq AS minorderqty, I.leadtime AS itemleadtime, I.hsncode,
    I.categoryid AS itemcategorycode, COALESCE(C.Categoryname, 'Invalid Category Name') AS ItemCategoryName,
    I.classid AS itemclasscode, IIF(I.releasestatus = 'Y','Yes', IIF(I.releasestatus = 'N','No','Invalid')) AS releasestatusname,
    IIF(I.iscritical = 'Y', 'Yes', IIF(I.iscritical = 'N','No','Invalid')) AS iscriticalName,
    COALESCE(IC.classname, 'Invalid Item Class Name') AS ItemClassName,
    I.errorcode FROM #BulkUpdateItemmst I
    LEFT JOIN uom U ON I.uomid = U.uomcode
    LEFT JOIN uom U1 ON I.uom1id = U1.uomcode
    LEFT JOIN itemgroupmst IG ON I.itemgroupid = IG.groupcode
    LEFT JOIN Categorymst C ON I.categoryid = C.categorycode
    LEFT JOIN Inventoryclass IC ON I.classid = IC.classcode
    LEFT JOIN segmentmst S ON I.segmentcode = S.segmentcode
    LEFT JOIN GstRates G ON I.gstcode = G.gstcode
    ORDER BY I.id";

    using var connection = _DapperContext.SetClientConnection(dbname);
    connection.Open();
    connection.Execute(tempcreation); //create #temp

    foreach (var dataLine in listdata)
    {
        connection.Execute(inserttemp, //insert in #temp
        new
        {
            itemid = dataLine.ItemId.PadLeft(6, '0'),
            itemgroupid = dataLine.ItemGroupCode.PadLeft(3, '0'),
            segmentcode = dataLine.ItemSegmentCode.PadLeft(3, '0'),
            itemname = dataLine.ItemName,
            shortname = dataLine.ItemShortName,
            gstcode = dataLine.GstRateCode.PadLeft(2, '0'),
            uomid = dataLine.StdUomCode.PadLeft(2, '0'),
            uom1id = dataLine.AlternateUomCode.PadLeft(2, '0'),
            nfactor = dataLine.ConversionFactor,
            stdpkg = dataLine.StdPacking,
            stdweight = dataLine.StdWeight,
            rol = dataLine.ReOrderLevel,
            maxol = dataLine.MaxOrderLevel,
            eoq = dataLine.MinOrderQty,
            leadtime = dataLine.ItemLeadTime,
            hsncode = dataLine.HsnCode,
            categoryid = dataLine.ItemCategoryCode,
            classid = dataLine.ItemClassCode,
            releasestatus = dataLine.ReleaseStatus,
            iscritical = dataLine.IsCritical
        });
    }

    connection.Execute(errorcodeA);
    connection.Execute(errorcodeB);
    connection.Execute(errorcodeC);
    connection.Execute(errorcodeD);
    connection.Execute(errorcodeE);
    connection.Execute(errorcodeF);
    connection.Execute(errorcodeG);
    connection.Execute(errorcodeH);
    connection.Execute(errorcodeI);
    connection.Execute(errorcodeJ);

    var errorcount = connection.QuerySingleOrDefault<int>(cnterror);
    string? msg;
    msg = "Bulk update could not succeed because of errors in input";
    if (errorcount == 0)
    {
        connection.Execute(updateinventorymst);
        msg = "Bulk update of Item Master succeeded";
        _IUtilityMethodsRepository.InsertMFGLog(dbname, username, "000000", "Item Master", "Bulk Update", yearlabel, "Bulk Item Master update completed");
    }
    var finaldata = connection.Query<ItemMst>(data);
    var data1 = finaldata.ToList();

    return new Tuple<List<ItemMst>, string>(data1, msg);
}

public async Task<IEnumerable<dynamic>> GetActiveItemsForBulkUpdate(string dbname)
{
    var query = @"SELECT itemid, itemgroupid, segmentcode, itemname, shortname,
        gstcode, uomid, uom1id,nfactor, stdpkg, stdweight, rol, maxol, eoq,
        leadtime, hsncode, categoryid,classid, releasestatus, iscritical
        FROM inventorymst WHERE isactive = 'Y' ORDER BY itemid";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var data = await connection.QueryAsync<dynamic>(query);
    return data;
}