CHS - [GSTR Reco Model]

Doubts

For GSTR Reco model based sheet should I add total line also through query so that it can be used in other sheets or will it be calculated through excel sheet?
For TB Repository method of TB will be called?
JV sheet will be added through system or it will be taken manually?
In GSTR Working values from GSTR 3B Working sheet is also used. So how that should be taken?
GSTR Working values should I take code wise and dump in excel sheet?

Sheets in GSTR Reco Model Report

GSTR Working Values coming through TB
JV Values coming through GSTR Working and Previous year not shown in 2b sheet
{Year} Not Shown in 2B GSTR 2B/3B Reco Model
{Year} Shown in 2B But Not in 3B GSTR 2B/3B Reco Model
Trial_Balance From Financial Report
Mar {year} Summary GSTR 3B Working sheet for March
{year - 1} Not Shown in 2B GSTR 2B/3B Reco Model

GSTR Working

Columns	Property Name
Sr No	SrNo
A/c Particulars	AcName

Points

For individualAccounts instead of passing hardcoded mainac and subac can we introduce a column like ind G means all that mainac and subac should be taken in the GSTR Working ?
For total row to be printed need to do more survey. Not able to find a simple and proper solution. Needs to be discussed.

var workSheet = WorkbookXL.Worksheets.Add("GSTR_Working");
workSheet.SheetView.FreezeRows(1);

workSheet.Cell(1, 1).Value = "A/c  Particulars";
workSheet.Cell(1, 2).Value = "Amount";

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

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

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


var individualAccounts = new List<(string MainA, string SubA)>
{
    ("030091", "000001"), // CGST COLLECTED  ON SALES @ 9%
    ("030092", "000001")  // SGST COLLECTED  ON SALES @ 9%
};

int i = 5;
var data = ledgerReportXlData.Item2; //Trial Balance
foreach (var row in data)
{
    string mainA = row.MainAcCode;
    string subA = row.SubAcCode;
    string accName = row.MainSubAcName;
    var closingDr = row.ClgDebitAmount;
    var closingCr = row.ClgCreditAmount;

    foreach (var acc in individualAccounts)
    {
        if (acc.MainA == mainA && acc.SubA == subA)
        {
            i++;
            workSheet.Cell(i, 1).Value = accName;
            workSheet.Cell(i, 2).Value = closingCr - closingDr;
        }
    }
}

JV

Columns	Property Name
A/c Name	AcName
Notes	Notes
Debit Amount	DebitAmt
Credit Amount	CreditAmt

{_PostLogin.yearlabel} Not Shown In 2B Sheet.

Columns	Property Name
Bill No	BillNo
Bill Date	BillDate
Vendor Name	VendorName
GSTIN	Gstin
Vendor Bill No	VendorBillNo
Vendor Bill Date	VendorBillDate
CGST Amount	CgstAmt
SGST Amount	SgstAmt
IGST Amount	IgstAmt
Total GST Amount	TotalGstAmt
Input Tax Credit	InputTaxCreditName
Shown In 2B	Gstr2bDate

{_PostLogin.yearlabel} Shown in 2B But Not in 3B.

Columns	Property Name
Bill No	BillNo
Bill Date	BillDate
Vendor Name	VendorName
GSTIN	Gstin
Vendor Bill No	VendorBillNo
Vendor Bill Date	VendorBillDate
CGST Amount	CgstAmt
SGST Amount	SgstAmt
IGST Amount	IgstAmt
Total GST Amount	TotalGstAmt
Input Tax Credit	InputTaxCreditName
Shown In 2B	Gstr2bDate
Availed In 3B	Gstr3bDate

{_PostLogin.yearlabel - 1 year} Not Shown In 2B Sheet.

Columns	Property Name
Bill No	BillNo
Bill Date	BillDate
Vendor Name	VendorName
GSTIN	Gstin
Vendor Bill No	VendorBillNo
Vendor Bill Date	VendorBillDate
CGST Amount	CgstAmt
SGST Amount	SgstAmt
IGST Amount	IgstAmt
Total GST Amount	TotalGstAmt
Input Tax Credit	InputTaxCreditName
Shown In 2B	Gstr2bDate

Query for sheets based on GSTR Reco Model

DECLARE @previousYearFromDate DATETIME = '01-Apr-23';
DECLARE @previousYearToDate DATETIME = '31-Mar-24';
DECLARE @currentYearFromDate DATETIME = '01-Apr-24';
DECLARE @currentYearToDate DATETIME = '31-Mar-25';

CREATE TABLE #gstrreco
(
    billno NVARCHAR(6) COLLATE DATABASE_DEFAULT,
    billdate DATETIME,
    vendorname NVARCHAR(75) COLLATE DATABASE_DEFAULT,
    gstin NVARCHAR(15) COLLATE DATABASE_DEFAULT,
    vendorbillno NVARCHAR (20) COLLATE DATABASE_DEFAULT,
    vendorbilldate DATETIME,
    billamt NUMERIC(11,2),
    cgstamt NUMERIC(10,2),
    sgstamt NUMERIC(10,2),
    igstamt NUMERIC(10,2),
    totalgstamt NUMERIC(10,2),
    gstr2bdate DATETIME,
    gstr3bdate DATETIME,
    inputtaxcreditname NVARCHAR(50) COLLATE DATABASE_DEFAULT 
)

WITH A(billno,billdate,vendorname,gstin,vendorbillno,vendorbilldate,
billamt,cgstamt,sgstamt,igstamt,totalgstamt,gstr2bdate,gstr3bdate,inputtaxcreditname)AS
(
    SELECT B.billno,B.dated,REPLACE(V.vendorname,',','') AS vendorname,V1.gstin, 
    B.vendorbillno ,B.vendorbilldate , 
    B.billamt,B.cgstamt,B.sgstamt,B.igstamt,(B.cgstamt+B.sgstamt+B.igstamt) AS totalgstamt,
    B.gstr2bdate ,gstr3bdate, IIF(inputtaxcredit = '1','Is Eligible',
    IIF(inputtaxcredit = '2','Not Eligible','Not Applicable')) AS inputtaxcreditname
    FROM billchs B 
    LEFT JOIN vendormst V ON B.vendorid = V.vendorid 
    LEFT JOIN vendormst1 V1 ON B.vendorid + B.branchid = V1.vendorid+V1.branchid
    WHERE B.cgstamt+B.sgstamt+B.igstamt > 0 AND CONVERT(DATE, B.dated) BETWEEN @previousYearFromDate AND @currentYearToDate
)
INSERT INTO #gstrreco (billno,billdate,vendorname,gstin,vendorbillno,vendorbilldate,
billamt,cgstamt,sgstamt,igstamt,totalgstamt,gstr2bdate,gstr3bdate,inputtaxcreditname)
SELECT billno,billdate,vendorname,gstin,vendorbillno,vendorbilldate,
billamt,cgstamt,sgstamt,igstamt,totalgstamt,gstr2bdate,gstr3bdate,inputtaxcreditname FROM A


-- {_PostLogin.yearlabel} Not Shown In 2B Sheet.
SELECT billno,billdate,vendorname,gstin,vendorbillno,vendorbilldate,
billamt,cgstamt,sgstamt,igstamt,totalgstamt,gstr2bdate,inputtaxcreditname 
FROM #gstrreco
WHERE gstr2bdate = '01-Jan-01' AND CONVERT(DATE, billdate) BETWEEN @currentYearFromDate AND @currentYearToDate
ORDER BY billdate, billno DESC

-- {_PostLogin.yearlabel} Shown in 2B But Not in 3B.
SELECT billno,billdate,vendorname,gstin,vendorbillno,vendorbilldate,
billamt,cgstamt,sgstamt,igstamt,totalgstamt,gstr2bdate,gstr3bdate,inputtaxcreditname 
FROM #gstrreco
WHERE CONVERT(DATE, gstr3bdate) = @currentYearToDate 
AND gstr2bdate != '01-Jan-01'
AND CONVERT(DATE, billdate) BETWEEN @currentYearFromDate AND @currentYearToDate
ORDER BY billdate, billno DESC

-- {_PostLogin.yearlabel - 1 year} Not Shown In 2B Sheet.
SELECT billno,billdate,vendorname,gstin,vendorbillno,vendorbilldate,
billamt,cgstamt,sgstamt,igstamt,totalgstamt,gstr2bdate,inputtaxcreditname 
FROM #gstrreco
WHERE gstr2bdate != '01-Jan-01'
AND CONVERT(DATE, billdate) BETWEEN @previousYearFromDate AND @previousYearToDate
ORDER BY billdate, billno DESC

PdfExtractor CHS Under Development