Service Group Import

public class ImportServiceGroupMst
{
    public string GroupName { get; set; } = string.Empty;
    public string ComputeTypeCode { get; set; } = string.Empty;
    public string ComputeTypeName { get; set; } = string.Empty;
    public int ValidityDays { get; set; }
    public string TariffName { get; set; } = string.Empty;
    public decimal Amount { get; set; }
    public string ErrorCode { get; set; } = string.Empty;
    public string GroupCode { get; set; } = string.Empty;
}

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

            string pathdirname = $"\\CHSReports\\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("Service_Group_Model");
            worksheet.SheetView.FreezeRows(1);

            worksheet.Cell(1, 1).Value = "Trn Id";
            worksheet.Cell(1, 2).Value = "Group Name";
            worksheet.Cell(1, 3).Value = "Compute Type";
            worksheet.Cell(1, 4).Value = "Validity Days";
            worksheet.Cell(1, 5).Value = "Tariff Code";

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

            worksheet.Column(4).Style.NumberFormat.Format = "###0";

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

            var entitydata = await _IUtilityMethodsRepository.GetEntityMstInfo(dbname);
            var isURP = entitydata.gstin == "URP";
            
            if (isURP)
            {
                worksheet.Column(3).Hide();
            }

            var worksheet1 = WorkbookXL.Worksheets.Add("Instructions");
            worksheet1.Cell(1, 1).Value = "Column";
            worksheet1.Cell(1, 2).Value = "Description";
            worksheet1.Cell(2, 1).Value = "Trn Id";
            worksheet1.Cell(2, 2).Value = "Must be unique for every Group Code";
            worksheet1.Cell(3, 1).Value = "Group Name";
            worksheet1.Cell(3, 2).Value = "Must be 50 characters only";
            worksheet1.Cell(4, 1).Value = "Compute Type";
            worksheet1.Cell(4, 2).Value = "Must be I (Inclusive), E (Exclusive)";
            worksheet1.Cell(5, 1).Value = "Validity Days";
            worksheet1.Cell(5, 2).Value = "Must be between 0-365";
            worksheet1.Cell(6, 1).Value = "Tariff Code";
            worksheet1.Cell(6, 2).Value = "Must exist in Std Tariff Master";

            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;

            WorkbookXL.SaveAs(pathfilename);

            return randomfilename;
        }

        [HttpGet("{dbname},{xlfile}")]
        public async Task<string> ServiceGroupMstImportXl(string dbname, string xlfile)
        {
            var xlFileResult = " ";
            Random rnd = new();
            var randomNumber = rnd.Next(1000000000).ToString().Trim();
            var randomFileName = $"{randomNumber}.xlsx";

            string pathDirName1 = $"\\CHSReports\\Reports\\{dbname}";
            var pathName1 = $"{pathDirName1}\\{randomFileName}";
            var pathFileName1 = Path.GetFullPath(pathName1);
            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();

            if (columncount != 5 || firstcolname != "trn id" || lastcolname != "tariff code")
            {
                xlFileResult = "1"; //file is not in correct format
            }
            else if (rowcount == 1)
            {
                xlFileResult = "2"; // file does not contain any line
            }
            else
            {
                int row = 2;
                var error = " ";
                List<ImportServiceGroupMst> listdata = new();

                while (row <= rowcount)
                {
                    var trnIdCol = worksheet.Cell(row, 1);
                    var groupNameCol = worksheet.Cell(row, 2);
                    var computeTypeCol = worksheet.Cell(row, 3);
                    var validDaysCol = worksheet.Cell(row, 4);
                    var tariffCodeCol = worksheet.Cell(row, 5);

                    if (groupNameCol.GetString().Length == 0 ||  tariffCodeCol.GetString().Length == 0)
                    {
                        error = $"Z1 {error}";
                    }

                    if (groupNameCol.GetString().Length > 50)
                    {
                        error = $"Z2 {error}";
                    }
                    var entityGstIn = await _IUtilityMethodsRepository.GetEntityMstInfo(dbname);
                    if(entityGstIn.gstin != "URP")
                    {
                        if (!new[] { "I", "E"}.Contains(computeTypeCol.GetString()))
                        {
                            error = $"Z3 {error}";
                        }
                    }
                    else if (computeTypeCol.GetString().Length != 0)
                    {
                        error = $"Z3 {error}";
                    }

                    if (Convert.ToString(validDaysCol.DataType) != "Number" || Convert.ToString(trnIdCol.DataType) != "Number" )
                    {
                        error = "Z4" + " " + error;
                    }

                    if (tariffCodeCol.GetString().Length > 6)
                    {
                        error = "Z5" + " " + error;
                    }

                    if(Convert.ToString(validDaysCol.DataType) == "Number")
                    {   
                        if (validDaysCol.GetDouble() > 365 || validDaysCol.GetDouble() < 0)
                        {
                            error = "Z6" + " " + error;
                        }
                    }

                    if (error != " ")
                    {
                        worksheet.Cell(row, 6).Value = error;
                        error = "";
                    }
                    else
                    {
                        listdata.Add(new ImportServiceGroupMst
                        {
                            TrnId = (int)trnIdCol.GetDouble(),
                            GroupName = groupNameCol.GetString(),
                            ComputeTypeCode = entityGstIn.gstin == "URP" ? "Z" : computeTypeCol.GetString(),
                            ValidityDays = (int)validDaysCol.GetDouble(),
                            TariffCode = tariffCodeCol.GetString(),
                        });
                    }

                    row++;
                }

                worksheet.SheetView.FreezeRows(1);
                worksheet.Cell(1, 6).Value = "Error Code";
                worksheet.Cell(row + 1, 1).Value = "Import could not succeed";
                worksheet.Range(1, 1, 1, 6).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
                worksheet.Range(1, 1, 1, 6).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, 6).Style.Border.TopBorder = XLBorderStyleValues.Thin;
                worksheet.Range(1, 1, 1, 6).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
                worksheet.Range(1, 1, 1, 6).Style.Border.RightBorder = XLBorderStyleValues.Thin;
                worksheet.Range(1, 1, 1, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

                worksheet.Column(4).Style.NumberFormat.Format = "##0;[Red](##0)";

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

                var entitydata = await _IUtilityMethodsRepository.GetEntityMstInfo(dbname);
                var isURP = entitydata.gstin == "URP";
            
                if (isURP)
                {
                    worksheet.Column(3).Hide();
                }
                
                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 = "Group Name and Tariff Code must be filled";
                worksheet1.Cell(3, 1).Value = "Z2";
                worksheet1.Cell(3, 2).Value = "Group name can be up to 50 characters only.";
                worksheet1.Cell(4, 1).Value = "Z3";
                worksheet1.Cell(4, 2).Value = "Compute Type must be I (Inclusive), E (Exclusive), or blank (Unregistered Entity).";
                worksheet1.Cell(5, 1).Value = "Z4";
                worksheet1.Cell(5, 2).Value = "Validity Days and Trn Id must be a number";
                worksheet1.Cell(6, 1).Value = "Z5";
                worksheet1.Cell(6, 2).Value = "Tariff code can be up to 6 characters only.";
                worksheet1.Cell(7, 1).Value = "Z6";
                worksheet1.Cell(7, 2).Value = "Validity Days must be between 0 to 365";

                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 = 60;
                worksheet1.Column(2).Style.Alignment.WrapText = true;

                if (error == " ")
                {
                    var xldata = await _IServiceGroupMstRepository.ServiceGroupMstImportXl(listdata, dbname);

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

                    worksheet2.SheetView.FreezeRows(5);
                    worksheet2.Cell(1, 1).Value = "Entity Name : " + entitydata.entityname;
                    worksheet2.Cell(2, 1).Value = "Report Name : Service Group Master Import";
                    worksheet2.Cell(3, 1).Value = $"Report Date : {DateTime.Now.Date:dd-MMM-yy}";
                    worksheet2.Cell(4, 1).Value = xldata.Item2;

                    worksheet2.Cell(5, 1).Value = "Error Code";
                    worksheet2.Cell(5, 2).Value = "Trn Id";
                    worksheet2.Cell(5, 3).Value = "Group Code";
                    worksheet2.Cell(5, 4).Value = "Group Name";
                    worksheet2.Cell(5, 5).Value = "Compute Type";
                    worksheet2.Cell(5, 6).Value = "Compute Type Name";
                    worksheet2.Cell(5, 7).Value = "Validity Days";
                    worksheet2.Cell(5, 8).Value = "Tariff Code";
                    worksheet2.Cell(5, 9).Value = "Tariff Name";
                    worksheet2.Cell(5, 10).Value = "Amount";

                    worksheet2.Range(5, 1, 5, 10).Style.Fill.BackgroundColor = XLColor.LightSteelBlue;
                    worksheet2.Range(1, 1, 5, 10).Style.Font.Bold = true;
                    worksheet2.Range(4, 1, 4, 10).Style.Fill.BackgroundColor = XLColor.Yellow;

                    worksheet2.Range(5, 1, 5, 10).Style.Border.TopBorder = XLBorderStyleValues.Thin;
                    worksheet2.Range(5, 1, 5, 10).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
                    worksheet2.Range(5, 1, 5, 10).Style.Border.RightBorder = XLBorderStyleValues.Thin;
                    worksheet2.Range(5, 1, 5, 9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

                    int i = 5;
                    foreach (var row1 in xldata.Item1)
                    {
                        i++;
                        worksheet2.Cell(i, 1).Value = row1.ErrorCode;
                        worksheet2.Cell(i, 2).Value = row1.TrnId;
                        worksheet2.Cell(i, 3).Value = row1.GroupCode;
                        worksheet2.Cell(i, 4).Value = row1.GroupName;
                        worksheet2.Cell(i, 5).Value = row1.ComputeTypeCode;
                        worksheet2.Cell(i, 6).Value = row1.ComputeTypeName;
                        worksheet2.Cell(i, 7).Value = row1.ValidityDays;
                        worksheet2.Cell(i, 8).Value = row1.TariffCode;
                        worksheet2.Cell(i, 9).Value = row1.TariffName;
                        worksheet2.Cell(i, 10).Value = row1.Amount;
                    }

                    worksheet2.Column(7).Style.NumberFormat.Format = "##0;[Red](##0)";
                    worksheet2.Column(10).Style.NumberFormat.Format = "##0.00;[Red](##0.00)";
                    
                    worksheet2.Columns().AdjustToContents(5);
                    
                    worksheet2.Column(4).Width = 40;
                    worksheet2.Column(4).Style.Alignment.WrapText = true;
                    
                    if (isURP)
                    {
                        worksheet.Column(5).Hide();
                        worksheet.Column(6).Hide();
                    }

                    var worksheet3 = WorkbookXL1.Worksheets.Add("Error_Code");
                    worksheet3.Cell(1, 1).Value = "A";
                    worksheet3.Cell(1, 2).Value = "Tariff code not found in Std Tariff Master";
                    worksheet3.Cell(2, 1).Value = "B";
                    worksheet3.Cell(2, 2).Value = "Invalid tariff for compute type I (only taxable tariffs are allowed)";
                    worksheet3.Cell(3, 1).Value = "C";
                    worksheet3.Cell(3, 2).Value = "Group name, Validity Days and Compute Type must be same for every Group Code";
                    worksheet3.Cell(4, 1).Value = "D";
                    worksheet3.Cell(4, 2).Value = "Group name must be unique throughout the excel";
                    worksheet3.Cell(5, 1).Value = "E";
                    worksheet3.Cell(5, 2).Value = "Tariff code must be unique for every Trn Id";
                    worksheet3.Cell(6, 1).Value = "F";
                    worksheet3.Cell(6, 2).Value = "For compute type I only one line can be added for a single trnid";
                    worksheet3.Cell(7, 1).Value = "G";
                    worksheet3.Cell(7, 2).Value = "Tariff with multiple GST cannot be added in same line";

                    WorkbookXL1.SaveAs(pathFileName1);
                    xlFileResult = randomFileName;
                }
                else
                {
                    WorkbookXL.SaveAs(pathFileName1);
                    xlFileResult = randomFileName;
                }
            }

            System.IO.File.Delete(pathFileName2);
            return xlFileResult;
        }        
        [HttpGet("{dbname}")]
        public async Task<ActionResult> GetServiceGroupMstCnt(string dbname)
        {
            return Ok(await _IServiceGroupMstRepository.GetServiceGroupMstCnt(dbname));
        }

    public async Task<Tuple<List<ImportServiceGroupMst>, string>> ServiceGroupMstImportXl(List<ImportServiceGroupMst> listdata, string dbname)
    {
        var tempcreation = @"CREATE TABLE ##servicegrouptemp
        (
            id INT PRIMARY KEY IDENTITY(1,1),
            groupname NVARCHAR (50) COLLATE DATABASE_DEFAULT,
            computetypecode NVARCHAR (1) COLLATE DATABASE_DEFAULT,
            validitydays INT,
            tariffcode NVARCHAR (6) COLLATE DATABASE_DEFAULT,
            errorcode NVARCHAR(35) COLLATE DATABASE_DEFAULT,
            groupcode NVARCHAR(6) COLLATE DATABASE_DEFAULT,
            amount NUMERIC(12,2),
            trnid INT
        );";

        var inserttemp = @"INSERT INTO ##servicegrouptemp (groupname,computetypecode,validitydays,tariffcode,trnid)
        VALUES (UPPER(TRIM(@GroupName)),UPPER(@ComputeTypeCode),@ValidityDays,@TariffCode,@TrnId);";

        // ERROR CODE A – No tariff code in StdTariffMst
        var errorcodeA = @"UPDATE T SET ERRORCODE = COALESCE(RTRIM(ERRORCODE),'')+' A'
                       FROM ##servicegrouptemp T
                       WHERE NOT EXISTS (SELECT tariffcode FROM StdTariffMst S WHERE S.tariffcode = T.tariffcode AND S.isactive = 'Y'  AND taxgroup = 'NA' );";

        // ERROR CODE B – Invalid tariff for ComputeType I
        var errorcodeB = @"UPDATE T SET ERRORCODE = COALESCE(RTRIM(ERRORCODE),'')+' B'
                       FROM ##servicegrouptemp T
                       WHERE computetypecode = 'I'
                       AND NOT EXISTS (SELECT tariffcode FROM StdTariffMst S WHERE S.tariffcode = T.tariffcode AND S.taxind = 'Y' AND S.isactive='Y'  AND taxgroup = 'NA' );";

        // ERROR CODE C – different records for same trnid within Excel
        var errorcodeC = @"WITH A(groupname, validitydays, computetypecode, trnid) AS
        (
            SELECT DISTINCT groupname, validitydays, computetypecode, trnid
            FROM ##servicegrouptemp
        ),
        B(trnid,trnidCnt) AS
        (
            SELECT trnid, COUNT(*) AS trnidcnt
            FROM A
            GROUP BY trnid
        )
        UPDATE ##servicegrouptemp
        SET errorcode = COALESCE(RTRIM(errorcode), '') + ' C'
        FROM B
        WHERE ##servicegrouptemp.trnid = B.trnid AND B.trnidcnt > 1";

        // ERROR CODE D – group name must be unique
        var errorcodeD = @"WITH A(groupname) AS
        (
            SELECT groupname
            FROM ##servicegrouptemp
            GROUP BY groupname, trnid
        ),
        B(groupname, groupnamecnt) AS
        (
            SELECT groupname, COUNT(*) AS groupnamecnt
            FROM A
            GROUP BY groupname
        )
        UPDATE ##servicegrouptemp
        SET errorcode = COALESCE(RTRIM(errorcode), '') + ' D'
        FROM B
        WHERE ##servicegrouptemp.groupname = B.groupname AND B.groupnamecnt > 1";

        // ERROR CODE E – Duplicate tariff codes in Excel for same trnid
        var errorcodeE = @"WITH A (trnid, tariffcode) AS
        (
            SELECT trnid, tariffcode
            FROM ##servicegrouptemp
            WHERE computetypecode != 'I'
            GROUP BY trnid, tariffcode
            HAVING COUNT(*) > 1
        )
        UPDATE ##servicegrouptemp
        SET ERRORCODE = COALESCE(RTRIM(ERRORCODE),'')+' E'
        FROM A WHERE ##servicegrouptemp.trnid = A.trnid";

        // ERROR CODE F – Multiple inclusive lines with same TRN
        var errorcodeF = @"WITH A (trnid) AS
        (
            SELECT trnid FROM ##servicegrouptemp
            WHERE computetypecode = 'I'
            GROUP BY trnid HAVING COUNT(*) > 1
        )
        UPDATE ##servicegrouptemp SET ERRORCODE = COALESCE(RTRIM(ERRORCODE),'')+' F'
        FROM A
        WHERE ##servicegrouptemp.trnid = A.trnid";

        // ERROR CODE G – Multiple tax groups for same tariff
        var errorCodeG = @"WITH A (taxind,trnid) AS
        (
            SELECT taxind, trnid
            FROM ##servicegrouptemp T
            LEFT JOIN StdTariffMst S ON T.tariffcode = S.tariffcode
            WHERE computetypecode = 'E' AND taxind != 'N'
            GROUP BY trnid, taxind
        ),
        B(trnid, cnt) AS
        (
            SELECT trnid, COUNT(*) AS cnt
            FROM A
            GROUP BY trnid
        )
        UPDATE ##servicegrouptemp SET ERRORCODE = COALESCE(RTRIM(ERRORCODE),'')+' G'
        FROM B
        WHERE ##servicegrouptemp.trnid = B.trnid AND B.cnt > 1";

        var cnterror = @"SELECT COUNT(*) FROM ##servicegrouptemp WHERE COALESCE(errorcode,'') != ''";

        var groupCodeCntQuery = @"SELECT COUNT(DISTINCT trnid) FROM ##servicegrouptemp";

        var query = @"SELECT trnid, 
        groupname, computetypecode,
        validitydays, T.tariffcode,
        errorcode, groupcode, 
        IIF(computetypecode= 'I','Inclusive','Exclusive') AS computetypename, S.tariffname
        FROM ##servicegrouptemp T 
        LEFT JOIN stdtariffmst S ON T.tariffcode = S.tariffcode";
        
        var updateAmount = @"UPDATE ##servicegrouptemp
        SET amount = S.amount
        FROM stdtariffmst S
        WHERE ##servicegrouptemp.tariffcode = S.tariffcode";

        var updategroupcode = @"WITH A(trnid) AS
        (
            SELECT DISTINCT trnid FROM ##servicegrouptemp
        ),
        B (rowno, trnid) AS
        (
            SELECT ROW_NUMBER() OVER (ORDER BY trnid) AS rowno, trnid FROM A
        )
        UPDATE ##servicegrouptemp
        SET groupcode = RIGHT('000000' + CAST(B.rowno AS NVARCHAR(6)), 6)
        FROM B
        WHERE ##servicegrouptemp.trnid = B.trnid;";

        var finalinsertmain = @"INSERT INTO ServiceGroupMst(groupcode,groupname,groupchargetype,computetype,isactive,updatedon,validitydays)
        SELECT DISTINCT groupcode, groupname, @groupchargetype, computetypecode, @isactive, @updatedon, validitydays
        FROM ##servicegrouptemp";

        // Inclusive split computation
        var inclusiveInsert = @"WITH A(tariffrate, tariffcode, taxgroup, amount, groupcode) AS
        (
            SELECT 100 AS tariffrate, T.tariffcode, 'NA' AS taxgroup, amount, groupcode
            FROM ##servicegrouptemp T
            WHERE computetypecode = 'I'

            UNION ALL

            SELECT S.amount AS tariffrate, S.tariffcode, S.taxgroup, T.amount, groupcode
            FROM ##servicegrouptemp T
            LEFT JOIN StdTariffMst S ON 1=1
            WHERE T.computetypecode = 'I' AND taxgroup IN ('G1','G2') AND isactive='Y'
        ),
        B (computedamount, tariffcode, taxgroup, amount, groupcode)AS
        (
            SELECT CAST(ROUND(amount * tariffrate / 118, 2) AS NUMERIC(12,2)) AS computedamount, 
            tariffcode, taxgroup,amount, groupcode
            FROM A
        ),
        C(computedAmount, groupcode) AS
        (
            SELECT SUM(computedamount) AS computedAmount, groupcode
            FROM B
            GROUP BY groupcode
        ),
        D (roundoffamt, groupcode) AS
        (
            SELECT T.amount - computedamount AS roundoffamt,C.groupcode
            FROM C
            LEFT JOIN ##servicegrouptemp T ON C.groupcode = T.groupcode
            WHERE computetypecode = 'I'
        ), 
        E(amount, tariffcode, groupcode)  AS 
        (
            SELECT computedamount + roundoffamt, tariffcode, D.groupcode
            FROM B
            LEFT JOIN D ON b.groupcode = D.groupcode
            WHERE taxgroup = 'NA'

            UNION ALL

            SELECT computedamount, tariffcode, groupcode  FROM B
            WHERE taxgroup !='NA'
        )
        INSERT INTO ServiceGroupMst1 (amount,groupcode,tariffcode)
        SELECT amount,groupcode,tariffcode FROM E";

        var exclusiveInsert = @"WITH A (tariffcode,amount,groupcode) AS
        (
            SELECT T.tariffcode,S.amount,T.groupcode
            FROM ##servicegrouptemp T
            LEFT JOIN stdtariffmst S ON T.tariffcode = S.tariffcode
            WHERE computetypecode != 'I'
        )
        INSERT INTO servicegroupmst1 (tariffcode,amount,groupcode)
        SELECT tariffcode,amount,groupcode FROM A";

        var getTaxInd = @"SELECT ST.taxind, SG.groupcode
        FROM ##servicegrouptemp SG
        LEFT JOIN StdTariffMst ST ON SG.tariffcode = ST.tariffcode
        WHERE SG.computetypecode != 'I' AND taxgroup != 'NA';";
        
        var updateMainAmount = @"WITH A (groupcode, amount) AS 
        (
            SELECT groupcode, SUM(amount) AS amount
            FROM ServiceGroupMst1
            GROUP BY groupcode
        )
        UPDATE  ServiceGroupMst SET totalamount = A.amount
        FROM A
        WHERE A.groupcode = ServiceGroupMst.groupcode";

        using var connection = _DapperContext.SetClientConnection(dbname);
        connection.Open();

        // Temp table
        connection.Execute(tempcreation);

        // Insert uploaded Excel rows
        connection.Execute(inserttemp, listdata.Select(dataLine =>
        new{
            dataLine.GroupName,
            dataLine.ComputeTypeCode,
            dataLine.ValidityDays,
            tariffcode = dataLine.TariffCode.PadLeft(6, '0'),
            dataLine.Amount,
            dataLine.TrnId
        }));

        // Validation rules
        connection.Execute(errorcodeA);
        connection.Execute(errorcodeB);
        connection.Execute(errorcodeC);
        connection.Execute(errorcodeD);
        connection.Execute(errorcodeE);
        connection.Execute(errorcodeF);
        connection.Execute(errorCodeG);

        var errorcount = connection.QuerySingle<int>(cnterror);
        var groupCodeCnt = connection.QuerySingleOrDefault<int>(groupCodeCntQuery);

        string msg;
        if (errorcount == 0 && groupCodeCnt <= 1000)
        {
            connection.Execute(updategroupcode);
            connection.Execute(updateAmount);

            // Insert headers for compute types
            connection.Execute(finalinsertmain, new { groupchargetype = "F", isactive = "Y", updatedon = DateTime.Now.Date });

            // Inclusive allocations
            connection.Execute(inclusiveInsert);
            connection.Execute(exclusiveInsert);
            var taxData = connection.Query<dynamic>(getTaxInd);
            foreach (var item in taxData)
            {
                if (item.taxind == "Y")
                {
                    ComputeExclusiveValues(item.groupcode, dbname);
                }
                else if (item.taxind == "I")
                {
                    ComputeExclusiveIgstValues(item.groupcode, dbname);
                }

            }
            connection.Execute(updateMainAmount);
            msg = "Import Successfully Completed";
        }
        else
        {
            msg = "Import could not succeed due to validation errors";
        }

        var data = connection.Query<ImportServiceGroupMst>(query).ToList();
        return new Tuple<List<ImportServiceGroupMst>, string>(data, msg);
    }
public async Task<int> GetServiceGroupMstCnt(string dbname)
{
    var query = @"SELECT COUNT(*) FROM ServiceGroupMst";
    using var connection = _DapperContext.SetClientConnection(dbname);
    var data = await connection.QuerySingleOrDefaultAsync<int>(query);
    return data;
}