namespace AccountsDAL.Query.ETDS { public static class EtdsQB { // Ported from GB4 EtdsDAL.UnDoPostingTDSReturnForm (TDSCategory.svc's // /ETDS/TDSUndoPosting/ operation) — reverses an e-TDS quarterly return "file // generation/filing" batch by hard-deleting every row (across 7 tables) tied to the // matching TDSRETURNID(s) for a given OU+Period+FormType+PeriodType. Unrelated to // voucher/TDS-payment posting (TVOUCHER/TVOUCHERTDS/TACCOUNTPOST are never touched here). // // Schema confirmed live via INFORMATION_SCHEMA.COLUMNS (2026-07-25). No TENANTID column // on any of these 7 tables (same per-tenant-database pattern already established for the // Voucher/BillAllocation table family this session) — matches GB4's own lack of a tenant // filter here. // // GB4 built the ID list as a comma-joined string spliced into raw SQL via // string.Replace(":tdsreturnids", ...) — replaced here with Dapper's native IN-list // parameter support (a real parameterized query, not string concatenation). public const string GET_TDS_RETURN_IDS_FOR_UNDO = @" SELECT DISTINCT RET.TDSRETURNID FROM TTDSRETURN RET INNER JOIN TTDSRETURNBATCH RETBAT ON RET.TDSRETURNID = RETBAT.TDSRETURNID WHERE RET.OUID = @OUId AND RET.PERIODID = @PeriodId AND RET.FILETYPE = @FileType AND RETBAT.FORMNUMBER = @FormNumber AND RETBAT.PERIOD = @PeriodType;"; // Delete order respects FK dependency depth (deepest children first, TTDSRETURN last) — // same order GB4 used. public const string DELETE_TDS_SECTION6A = @" DELETE FROM TTDSSECTION6A WHERE TDSSALARYDETAILID IN ( SELECT TDSSALARYDETAILID FROM TTDSSALARYDETAIL WHERE TDSRETURNID IN @TDSReturnIds );"; public const string DELETE_TDS_SECTION16 = @" DELETE FROM TTDSSECTION16 WHERE TDSSALARYDETAILID IN ( SELECT TDSSALARYDETAILID FROM TTDSSALARYDETAIL WHERE TDSRETURNID IN @TDSReturnIds );"; public const string DELETE_TDS_SALARY_DETAIL = @" DELETE FROM TTDSSALARYDETAIL WHERE TDSRETURNID IN @TDSReturnIds;"; public const string DELETE_TDS_RETURN_EMPLOYEE = @" DELETE FROM TTDSRETURNEMPLOYEE WHERE TDSRETURNID IN @TDSReturnIds;"; public const string DELETE_TDS_RETURN_CHALLAN = @" DELETE FROM TTDSRETURNCHALLAN WHERE TDSRETURNID IN @TDSReturnIds;"; public const string DELETE_TDS_RETURN_BATCH = @" DELETE FROM TTDSRETURNBATCH WHERE TDSRETURNID IN @TDSReturnIds;"; public const string DELETE_TDS_RETURN = @" DELETE FROM TTDSRETURN WHERE TDSRETURNID IN @TDSReturnIds;"; // ── Form24Q123 / ETDSForm (GetTDSReturnForm) ───────────────────────────── // Ported from GB4 EtdsDAL.GetTDSReturnForm's 7 helper Get* methods and their SQL // constants (EtdsQueryBuilder.cs GET_ETDS_FORM_TDSRETURN*) — a read-only operation that // fetches an already-posted e-TDS return filing batch (by TDSReturnId) and serializes it // into the NSDL/TIN e-TDS RPU flat-file import format. Column aliases here are used // VERBATIM as the flat-file field names by the BLL layer — do not rename them, the BLL // string-building code depends on these exact DTO property names via Dapper's // column-to-property mapping. // // GB4 bound a single ":tdsreturnid" parameter via naive string.Replace — parameterized // here as @TDSReturnId instead. Date-formatting (DDMMYYYY via CONVERT+substring) and // CASE/CAST expressions are kept identical to GB4's SQL since the flat-file format // requires those exact string shapes. public const string GET_ETDS_FORM_TDSRETURN = @" SELECT TDSRET.TDSRETURNID AS TDSReturnId, 1 AS LineNumber, 'FH' AS RecordType, TDSRET.FILETYPE AS FileType, 'R' AS UploadType, SUBSTRING(CONVERT(VARCHAR(10), TDSRET.FILEDATE, 103), 1, 2) + SUBSTRING(CONVERT(VARCHAR(10), TDSRET.FILEDATE, 103), 4, 2) + SUBSTRING(CONVERT(VARCHAR(10), TDSRET.FILEDATE, 103), 7, 4) AS FileCreationDate, TDSRET.FILESEQUENCE AS FileSequenceNumber, 'D' AS UploaderType, TDSRET.TAN AS TAN, TDSRET.TOTALBATCHES AS TotalBatches, 'GOODBOOKS' AS SoftwareName FROM TTDSRETURN TDSRET WHERE TDSRET.TDSRETURNID = @TDSReturnId;"; public const string GET_ETDS_FORM_TDSRETURN_BATCH = @" SELECT BATCH.TDSRETURNBATCHID AS TDSReturnBatchId, BATCH.TDSRETURNID AS TDSReturnId, BATCH.RUNSLNO AS TDSReturnBatchLineNumber, 'BH' AS RecordType, BATCH.BATCHNUMBER AS BatchNumber, BATCH.NUMBEROFCHALLAN AS NumberOfChallan, BATCH.FORMNUMBER AS FormNumber, '' AS TransactionType, '' AS BatchUpdation, '' AS OriginalToken, CASE WHEN BATCH.ISPREVIOUSPERIODFILED = 0 THEN BATCH.PREVIOUSTOKENNUMBER ELSE '' END AS PreviousTokenNumber, '' AS TokenNumber, '' AS TokenNumberDate, '' AS EmployerLastTAN, BATCH.TAN AS EmployerTAN, '' AS TIN, BATCH.PAN AS EmployerPAN, BATCH.ASSESMENTYEAR AS AssesmentYear, BATCH.FINANCIALYEAR AS FinancialYear, BATCH.PERIOD AS Period, BATCH.NAME AS EmployerName, BATCH.BRANCH AS EmployerBranch, BATCH.ADDRESS1 AS EmployerAddress1, BATCH.ADDRESS2 AS EmployerAddress2, BATCH.ADDRESS3 AS EmployerAddress3, BATCH.ADDRESS4 AS EmployerAddress4, BATCH.ADDRESS5 AS EmployerAddress5, BATCH.STATE AS EmployerState, BATCH.PINCODE AS EmployerPinCode, BATCH.EMAIL AS EmployerEmail, BATCH.STDCODE AS EmployerSTDCode, BATCH.PHONE AS EmployerPhone, CASE WHEN BATCH.ISADDRESSCHANGED = 0 THEN 'Y' WHEN BATCH.ISADDRESSCHANGED = 1 THEN 'N' END AS IsAddressChanged, BATCH.DEDUCTORTYPE AS DeductorType, BATCH.PERSONNAME AS ResponsiblePersonName, BATCH.DESIGNATION AS PersonDesignation, BATCH.PADDRESS1 AS PersonAddress1, BATCH.PADDRESS2 AS PersonAddress2, BATCH.PADDRESS3 AS PersonAddress3, BATCH.PADDRESS4 AS PersonAddress4, BATCH.PADDRESS5 AS PersonAddress5, BATCH.PSTATE AS PersonState, BATCH.PPINCODE AS PersonPinCode, BATCH.PEMAIL AS PersonEmail, BATCH.PMOBILE AS PersonMobile, BATCH.PSTDCODE AS PersonSTDCode, BATCH.PPHONE AS PersonPhone, CASE WHEN BATCH.ISPADDRESSCHANGED = 0 THEN 'Y' WHEN BATCH.ISPADDRESSCHANGED = 1 THEN 'N' END AS IsPersonAddressChanged, BATCH.TOTALDEPOSITAMOUNT AS TotalDepositAmount, '' AS NumberOfUnmatchedChallan, CAST(BATCH.COUNTOFRECORDS AS NVARCHAR(9)) AS CountOfSalaryRecords, CAST(BATCH.TOTALGROSSINCOME AS NVARCHAR(9)) AS TotalGrossIncome, 'N' AS AOApproval, CASE WHEN BATCH.ISPREVIOUSPERIODFILED = 0 THEN 'Y' WHEN BATCH.ISPREVIOUSPERIODFILED = 1 THEN 'N' END AS IsPreviousPeriodField, '' AS PreviousDeductorType, BATCH.STATENAME AS StateName, BATCH.PAOCODE AS PAOCode, BATCH.DDOCODE AS DDOCode, BATCH.MINISTRYNAME AS MinistryName, BATCH.MINISTRYNAMEOTHER AS MinistryNameOther, BATCH.PPAN AS PersonPAN, BATCH.PAOREGISTRATIONNUMBER AS PAORegistrationNumber, BATCH.DDOREGISTRATIONNUMBER AS DDORegistrationNumber, BATCH.ALTERNATESTDCODE AS EmployerAlternateSTDCode, BATCH.ALTERNATEPHONE AS EmployerAlternatePhone, BATCH.ALTERNATEEMAIL AS EmployerAlternateEmail, BATCH.PALTERNATESTDCODE AS PersonAlternateSTDCode, BATCH.PALTERNATEPHONE AS PersonAlternatePhone, BATCH.PALTERNATEEMAIL AS PersonAlternateEmail, BATCH.AIN AS AIN, BATCH.GSTIN AS GSTIN FROM TTDSRETURNBATCH BATCH WHERE BATCH.TDSRETURNID = @TDSReturnId;"; public const string GET_ETDS_FORM_TDSRETURN_CHALLAN = @" SELECT CHALLAN.TDSRETURNCHALLANID AS TDSReturnChallanId, CHALLAN.TDSRETURNID AS TDSReturnId, CHALLAN.TDSRETURNBATCHID AS TDSReturnBatchId, CHALLAN.SLNO AS LineNumber, 'CD' AS RecordType, BATCH.BATCHNUMBER AS BatchNumber, CHALLAN.RUNSLNO AS ChallanDetailRecordNumber, CHALLAN.COUNTOFDEDUCTEE AS CountOfDeductee, CASE WHEN CHALLAN.ISNILLCHALLAN = 0 THEN 'Y' WHEN CHALLAN.ISNILLCHALLAN = 1 THEN 'N' END AS IsNullChallan, '' AS ChallanUpdationIndicator, '' AS Filler2, '' AS Filler3, '' AS Filler4, CAST(CHALLAN.LASTCHALLANNUMBER AS NVARCHAR(5)) AS LastChallanSlNo, CHALLAN.CHALLANSERIALNUMBER AS ChallanSlNo, '' AS LastDDOSlNo, CAST(CHALLAN.DDOSERIALNUMBER AS NVARCHAR(9)) AS DDOSlNo, '' AS LastBankBranchCode, CHALLAN.BANKBRANCHCODE AS BankBranchCode, '' AS LastDateOfBankChallan, SUBSTRING(CONVERT(VARCHAR(10), CHALLAN.DATEOFBANKCHALLAN, 103), 1, 2) + SUBSTRING(CONVERT(VARCHAR(10), CHALLAN.DATEOFBANKCHALLAN, 103), 4, 2) + SUBSTRING(CONVERT(VARCHAR(10), CHALLAN.DATEOFBANKCHALLAN, 103), 7, 4) AS DataOfBankChallan, '' AS Filler5, '' AS Filler6, '' AS Section, CAST(CHALLAN.ITAMOUNT AS NUMERIC(18,2)) AS ItAmount, CAST(CHALLAN.SCAMOUNT AS NUMERIC(18,2)) AS SCAmount, CAST(CHALLAN.CESSAMOUNT AS NUMERIC(18,2)) AS CessAmount, CAST(CHALLAN.INTERESTAMOUNT AS NUMERIC(18,2)) AS InterestAmount, CAST(CHALLAN.OTHERAMOUNT AS NUMERIC(18,2)) AS OtherAmount, CAST(CHALLAN.TOTALAMOUNT AS NUMERIC(18,2)) AS TotalAmount, '' AS LastTotalAmount, CAST(CHALLAN.ANNEXUREDEPOSIT AS NUMERIC(18,2)) AS AnnexureDeposit, CAST(CHALLAN.ANNEXUREIT AS NUMERIC(18,2)) AS AnnexureIt, CAST(CHALLAN.ANNEXURESC AS NUMERIC(18,2)) AS AnnexureSC, CAST(CHALLAN.ANNEXURECESS AS NUMERIC(18,2)) AS AnnexureCess, CAST(CHALLAN.ANNEXURETOTAL AS NUMERIC(18,2)) AS AnnexureTotal, CAST(CHALLAN.ANNEXUREINTEREST AS NUMERIC(18,2)) AS AnnexureIntrest, CAST(CHALLAN.ANNEXUREOTHERS AS NUMERIC(18,2)) AS AnnexureOthers, '' AS ChequeNumber, CASE WHEN CHALLAN.ISBOOKORCASH = 0 THEN 'Y' WHEN CHALLAN.ISBOOKORCASH = 1 THEN 'N' ELSE '' END AS IsBookOrCash, '' AS Remarks, CAST(CAST(CHALLAN.FEEAMOUNT AS NUMERIC(18,2)) AS NVARCHAR(18)) AS FeeAmount, CAST(CHALLAN.MINORCHALLANHEAD AS NVARCHAR(18)) AS MinOrChallanHead FROM TTDSRETURNCHALLAN CHALLAN INNER JOIN TTDSRETURNBATCH BATCH ON CHALLAN.TDSRETURNBATCHID = BATCH.TDSRETURNBATCHID INNER JOIN TTDSRETURN TDSRET ON CHALLAN.TDSRETURNID = TDSRET.TDSRETURNID AND BATCH.TDSRETURNID = TDSRET.TDSRETURNID WHERE CHALLAN.TDSRETURNID = @TDSReturnId;"; public const string GET_ETDS_FORM_TDSRETURN_EMPLOYEE = @" SELECT EMP.TDSRETURNEMPLOYEEID AS TDSReturnEmployeeId, EMP.TDSRETURNID AS TDSReturnId, EMP.TDSRETURNCHALLANID AS TDSReturnChallanId, EMP.SLNO AS LineNumber, 'DD' AS RecordType, BATCH.BATCHNUMBER AS BatchNumber, CHALLAN.RUNSLNO AS ChallenDetailRecordNumber, EMP.RUNSLNO AS DetailSlNo, 'O' AS Mode, CAST(EMP.ECODE AS NVARCHAR(9)) AS EmployeeCode, EMP.DEDUCTEECODE AS Deductee, '' AS LastEmployeePAN, EMP.EPAN AS EmployeePAN, '' AS LastEmployeePANRefNo, EMP.EPANREF AS EmployeePANRefNo, EMP.ENAME AS EmployeeName, CAST(EMP.IT AS NUMERIC(18,2)) AS IncomeTax, CAST(EMP.SC AS NUMERIC(18,2)) AS Surcharge, CAST(EMP.CESS AS NUMERIC(18,2)) AS CESS, CAST(EMP.TOTAL AS NUMERIC(18,2)) AS Total, '' AS LastTotal, CAST(EMP.TOTALDEPOSITED AS NUMERIC(18,2)) AS TotalDeposited, '' AS LastTotalDeposited, '' AS TotalPurchase, CAST(EMP.AMOUNTPAID AS NUMERIC(18,2)) AS AmountPaid, SUBSTRING(CONVERT(VARCHAR(10), EMP.DATEPAYCREDIT, 103), 1, 2) + SUBSTRING(CONVERT(VARCHAR(10), EMP.DATEPAYCREDIT, 103), 4, 2) + SUBSTRING(CONVERT(VARCHAR(10), EMP.DATEPAYCREDIT, 103), 7, 4) AS DatePayCredit, SUBSTRING(CONVERT(VARCHAR(10), EMP.DATEDEDUCT, 103), 1, 2) + SUBSTRING(CONVERT(VARCHAR(10), EMP.DATEDEDUCT, 103), 4, 2) + SUBSTRING(CONVERT(VARCHAR(10), EMP.DATEDEDUCT, 103), 7, 4) AS DateDeduct, SUBSTRING(CONVERT(VARCHAR(10), EMP.DATEDEPOSIT, 103), 1, 2) + SUBSTRING(CONVERT(VARCHAR(10), EMP.DATEDEPOSIT, 103), 4, 2) + SUBSTRING(CONVERT(VARCHAR(10), EMP.DATEDEPOSIT, 103), 7, 4) AS DateDeposit, CAST(EMP.RATETAXDEDUCTED AS NVARCHAR(18)) AS RateTaxDeducted, '' AS GrossingUpIndicator, '' AS BookOrCashIndicator, '' AS FurnishingDate, EMP.REMARKS1 AS Remarks1, EMP.REMARKS2 AS Remarks2, EMP.REMARKS3 AS Remarks3, EMP.SECTION AS Section, EMP.CERTIFICATENUMBER AS CertificateNumber FROM TTDSRETURNEMPLOYEE EMP INNER JOIN TTDSRETURNCHALLAN CHALLAN ON EMP.TDSRETURNCHALLANID = CHALLAN.TDSRETURNCHALLANID INNER JOIN TTDSRETURNBATCH BATCH ON CHALLAN.TDSRETURNBATCHID = BATCH.TDSRETURNBATCHID WHERE EMP.TDSRETURNID = @TDSReturnId;"; public const string GET_ETDS_FORM_TDSSALARY_DETAIL = @" SELECT SALDET.TDSSALARYDETAILID AS TDSSalaryDetailId, SALDET.TDSRETURNID AS TDSReturnId, SALDET.TDSRETURNCHALLANID AS TDSReturnChallanId, SALDET.TDSRETURNEMPLOYEEID AS TDSReturnEmployeeId, 'SD' AS RecordType, BATCH.BATCHNUMBER AS BatchNumber, SALDET.SLNO AS SlNo, SALDET.MODE AS Mode, '' AS Filter7, SALDET.EPAN AS EPan, SALDET.EPANREFNUMBER AS EPanRefNumber, SALDET.ENAME AS EName, SALDET.ECategory AS ECategory, SUBSTRING(CONVERT(VARCHAR(10), SALDET.EMPLOYMENTFROM, 103), 1, 2) + SUBSTRING(CONVERT(VARCHAR(10), SALDET.EMPLOYMENTFROM, 103), 4, 2) + SUBSTRING(CONVERT(VARCHAR(10), SALDET.EMPLOYMENTFROM, 103), 7, 4) AS EmploymentFrom, SUBSTRING(CONVERT(VARCHAR(10), SALDET.EMPLOYMENTTO, 103), 1, 2) + SUBSTRING(CONVERT(VARCHAR(10), SALDET.EMPLOYMENTTO, 103), 4, 2) + SUBSTRING(CONVERT(VARCHAR(10), SALDET.EMPLOYMENTTO, 103), 7, 4) AS EmploymentTo, CAST(SALDET.TOTALSALARY AS NUMERIC(18,2)) AS TotalSalary, '' AS Filter8, SALDET.COUNTOFSALARYDETAIL AS CountOfSalaryDetail, CAST(SALDET.TOTALDEDUCTION AS NUMERIC(18,2)) AS TotalDeduction, CAST(SALDET.SALARYINCOME AS NUMERIC(18,2)) AS SalaryIncome, CAST(SALDET.OTHERINCOME AS NUMERIC(18,2)) AS OtherInCome, CAST(SALDET.GROSSTOTALINCOME AS NUMERIC(18,2)) AS GrossTotalIncome, '' AS LastGrossTotalIncome, SALDET.COUNTOF6ADETAIL AS CountOf6ADetail, CAST(SALDET.GROSSTOTALDEDUCTION6A AS NUMERIC(18,2)) AS GrossTotalDeduction6A, CAST(SALDET.TOTALTAXABLEINCOME AS NUMERIC(18,2)) AS TotalTaxableIncome, CAST(SALDET.TOTALIT AS NUMERIC(18,2)) AS TotalIT, CAST(SALDET.TOTALSC AS NUMERIC(18,2)) AS TotalSC, CAST(SALDET.TOTALCESS AS NUMERIC(18,2)) AS TotalCess, CAST(SALDET.ITRELIEF AS NUMERIC(18,2)) AS ITRelief, CAST(SALDET.NETITPAYABLE AS NUMERIC(18,2)) AS NetItPayable, CAST(SALDET.TOTALTDS AS NUMERIC(18,2)) AS TotalTDS, CAST(SALDET.SHORTOREXCESS AS NUMERIC(18,2)) AS ShortOrExcess, CAST(SALDET.TOTALDEDUCTION80 AS NUMERIC(18,2)) AS TotalDeduction80, '' AS Remarks1, '' AS Remarks2, CAST(SALDET.CURRENTTAXABLE AS NUMERIC(18,2)) AS CurrentTaxable, CAST(SALDET.PREVIOUSTAXABLE AS NUMERIC(18,2)) AS PreviousTaxable, CAST(SALDET.CURRENTTOTALTDS AS NUMERIC(18,2)) AS CurrentTotalTDS, CAST(SALDET.PREVIOUSTOTALTDS AS NUMERIC(18,2)) AS PreviouTotalTDS, CASE WHEN SALDET.ISHIGHERRATE = 0 THEN 'Y' WHEN SALDET.ISHIGHERRATE = 1 THEN 'N' END AS IsHigherRate, CASE WHEN SALDET.ISRENTABOVE = 0 THEN 'Y' WHEN SALDET.ISRENTABOVE = 1 THEN 'N' END AS IsRentAbove, CASE WHEN SALDET.ISRENTABOVE = 0 THEN SALDET.COUNTOFLANDLOARDPAN WHEN SALDET.ISRENTABOVE = 1 THEN 0 END AS CountOfLandLoardPan, SALDET.LL1PAN AS LL1Pan, SALDET.LL1NAME AS LL1Name, SALDET.LL2PAN AS LL2Pan, SALDET.LL2NAME AS LL2Name, SALDET.LL3PAN AS LL3Pan, SALDET.LL3NAME AS LL3Name, SALDET.LL4PAN AS LL4Pan, SALDET.LL4NAME AS LL4Name, CASE WHEN SALDET.ISINTERESTPAID = 0 THEN 'Y' WHEN SALDET.ISINTERESTPAID = 1 THEN 'N' END AS IsInterestPaid, SALDET.COUNTOFLENDERPAN AS CountOfLenderPan, SALDET.LENDER1PAN AS Lender1Pan, SALDET.LENDER1NAME AS Lender1Name, SALDET.LENDER2PAN AS Lender2Pan, SALDET.LENDER2NAME AS Lender2Name, SALDET.LENDER3PAN AS Lender3Pan, SALDET.LENDER3NAME AS Lender3Name, SALDET.LENDER4PAN AS Lender4Pan, SALDET.LENDER4NAME AS Lender4Name, CASE WHEN SALDET.ISSUPERANNUATIONBYTRUSTEES = 0 THEN 'Y' WHEN SALDET.ISSUPERANNUATIONBYTRUSTEES = 1 THEN 'N' END AS IsSuperAnnuationByTrustees, SALDET.FUNDNAME AS FundName, SUBSTRING(CONVERT(VARCHAR(10), SALDET.CONTRIBUTIONFROMDATE, 103), 1, 2) + SUBSTRING(CONVERT(VARCHAR(10), SALDET.CONTRIBUTIONFROMDATE, 103), 4, 2) + SUBSTRING(CONVERT(VARCHAR(10), SALDET.CONTRIBUTIONFROMDATE, 103), 7, 4) AS ContributionFromDate, SUBSTRING(CONVERT(VARCHAR(10), SALDET.CONTRIBUTIONTODATE, 103), 1, 2) + SUBSTRING(CONVERT(VARCHAR(10), SALDET.CONTRIBUTIONTODATE, 103), 4, 2) + SUBSTRING(CONVERT(VARCHAR(10), SALDET.CONTRIBUTIONTODATE, 103), 7, 4) AS ContributionToDate, CASE WHEN SALDET.ISSUPERANNUATIONBYTRUSTEES = 0 THEN CAST(SALDET.CONTRIBUTIONAMOUNTREPAID AS NVARCHAR(18)) WHEN SALDET.ISSUPERANNUATIONBYTRUSTEES = 1 THEN '' END AS ContributionAmountRepaid, CASE WHEN SALDET.ISSUPERANNUATIONBYTRUSTEES = 0 THEN CAST(SALDET.AVGRATEOFTAXDEDUCTION AS NVARCHAR(18)) WHEN SALDET.ISSUPERANNUATIONBYTRUSTEES = 1 THEN '' END AS AvgRateOfTaxDeduction, CASE WHEN SALDET.ISSUPERANNUATIONBYTRUSTEES = 0 THEN CAST(SALDET.SUPERANNUATIONTDS AS NVARCHAR(18)) WHEN SALDET.ISSUPERANNUATIONBYTRUSTEES = 1 THEN '' END AS SuperAnnuationTDS, CASE WHEN SALDET.ISSUPERANNUATIONBYTRUSTEES = 0 THEN CAST(SALDET.GROSSTOTALOFSUPERANNUATIONFUND AS NVARCHAR(18)) WHEN SALDET.ISSUPERANNUATIONBYTRUSTEES = 1 THEN '' END AS GrossTotalOfSuperAnnuationFund, CAST(SALDET.GROSSSALARY AS NUMERIC(18,2)) AS GrossSalary, CAST(SALDET.PERQUISITES AS NUMERIC(18,2)) AS PerQuisites, CAST(SALDET.PROFITINLIEU AS NUMERIC(18,2)) AS ProfitInLieu, CAST(SALDET.TRAVEL AS NUMERIC(18,2)) AS Travel, CAST(SALDET.GRATUITY AS NUMERIC(18,2)) AS Gratuity, CAST(SALDET.COMMUTEDPENSION AS NUMERIC(18,2)) AS CommutedPension, CAST(SALDET.LEAVESALARY AS NUMERIC(18,2)) AS LeaveSalary, CAST(SALDET.HRA AS NUMERIC(18,2)) AS HRA, CAST(SALDET.OTHEREXEMPTION AS NUMERIC(18,2)) AS OtherExemption, CAST(SALDET.TOTALEXCEMPTION AS NUMERIC(18,2)) AS TotalException, CAST(SALDET.INCOMEFROMOTHERSOURCE AS NUMERIC(18,2)) AS IncodeFromOtherSource, CAST(SALDET.REBATE AS NUMERIC(18,2)) AS Rebate FROM TTDSSALARYDETAIL SALDET INNER JOIN TTDSRETURNCHALLAN CHALLAN ON SALDET.TDSRETURNCHALLANID = CHALLAN.TDSRETURNCHALLANID INNER JOIN TTDSRETURNBATCH BATCH ON CHALLAN.TDSRETURNBATCHID = BATCH.TDSRETURNBATCHID INNER JOIN TTDSRETURNEMPLOYEE EMP ON SALDET.TDSRETURNEMPLOYEEID = EMP.TDSRETURNEMPLOYEEID WHERE SALDET.TDSRETURNID = @TDSReturnId;"; public const string GET_ETDS_FORM_TDSSECTION16_DETAIL = @" SELECT SEC.TDSSECTION16ID AS TDSSection16Id, SEC.TDSSALARYDETAILID AS TDSSalaryDetailId, 'S16' AS RecordType, BATCH.BATCHNUMBER AS BatchNumber, SEC.SLNO AS SlNo, SEC.RUNNINGSLNO AS Section16SlNo, SEC.SECTIONID AS SectionId, CAST(SEC.TOTALDEDUCTION AS NUMERIC(18,2)) AS TotalDeduction FROM TTDSSECTION16 SEC INNER JOIN TTDSSALARYDETAIL SALDET ON SEC.TDSSALARYDETAILID = SALDET.TDSSALARYDETAILID INNER JOIN TTDSRETURNCHALLAN CHALLAN ON SALDET.TDSRETURNCHALLANID = CHALLAN.TDSRETURNCHALLANID INNER JOIN TTDSRETURNBATCH BATCH ON CHALLAN.TDSRETURNBATCHID = BATCH.TDSRETURNBATCHID WHERE SALDET.TDSRETURNID = @TDSReturnId;"; public const string GET_ETDS_FORM_TDSSECTION6A_DETAIL = @" SELECT SEC.TDSSECTION6AID AS TDSSection16Id, SEC.TDSSALARYDETAILID AS TDSSalaryDetailId, 'C6A' AS RecordType, BATCH.BATCHNUMBER AS BatchNumber, SEC.SLNO AS SlNo, SEC.RUNNINGSLNO AS Section6ASlNo, SEC.SECTION6AID AS Section6AId, CAST(SEC.TOTALAMOUNT AS NUMERIC(18,2)) AS TotalAmount FROM TTDSSECTION6A SEC INNER JOIN TTDSSALARYDETAIL SALDET ON SEC.TDSSALARYDETAILID = SALDET.TDSSALARYDETAILID INNER JOIN TTDSRETURNCHALLAN CHALLAN ON SALDET.TDSRETURNCHALLANID = CHALLAN.TDSRETURNCHALLANID INNER JOIN TTDSRETURNBATCH BATCH ON CHALLAN.TDSRETURNBATCHID = BATCH.TDSRETURNBATCHID WHERE SALDET.TDSRETURNID = @TDSReturnId;"; } }