using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace AccountsDAL.Query.TDSCategory { public static class TDSCategoryQB { public const string GET_TDSCATEGORY = @"SELECT A.TDSCATEGORYID AS TDSCategoryId, A.TDSCATEGORYCODE AS TDSCategoryCode, A.TDSCATEGORYNAME AS TDSCategoryName, A.SECTION AS TDSCategorySection, A.REFERENCESECTION AS TDSCategoryReferenceSection, A.FORM AS TDSCategoryForm, A.TDSPERCENT AS TDSCategoryTDSPercent, A.SCPERCENT AS TDSCategorySCPercent, A.CESSPERCENT AS TDSCategoryCessPercent, A.DEDUCTIONACCOUNTID AS DeducationAccountId, B.ACCOUNTCODE AS DeducationAccountCode, B.ACCOUNTNAME AS DeducationAccountName, A.PAIDACCOUNTID AS PaidAccountId, C.ACCOUNTCODE AS PaidAccountCode, C.ACCOUNTNAME AS PaidAccountName, A.SUFFEREDACCOUNTID AS SufferedAccountId, D.ACCOUNTCODE AS SufferedAccountCode, D.ACCOUNTNAME AS SufferedAccountName, A.CLAIMACCOUNTID AS ClaimAccountId, E.ACCOUNTCODE AS ClaimAccountCode, E.ACCOUNTNAME AS ClaimAccountName, A.REMARKS AS TDSCategoryRemarks, A.SORTORDER AS TDSCategorySortOrder, A.STATUS AS TDSCategoryStatus, A.VERSION AS TDSCategoryVersion, A.CREATEDBYID AS TDSCategoryCreatedById, A.CREATEDON AS TDSCategoryCreatedOn, F.EMPLOYEENAME AS TDSCategoryCreatedByName, A.MODIFIEDBYID AS TDSCategoryModifiedById, A.MODIFIEDON AS TDSCategoryModifiedOn, G.EMPLOYEENAME AS TDSCategoryModifiedByName, A.ISCOMPANY AS TDSCategoryIsCompany, A.AMOUNTLIMIT AS TDSCategoryAmountLimit, A.TRANSACTIONLIMIT AS TDSCategoryTransactionLimit FROM MTDSCATEGORY A LEFT JOIN MACCOUNT B ON B.ACCOUNTID = A.DEDUCTIONACCOUNTID LEFT JOIN MACCOUNT C ON C.ACCOUNTID = A.PAIDACCOUNTID LEFT JOIN MACCOUNT D ON D.ACCOUNTID = A.SUFFEREDACCOUNTID LEFT JOIN MACCOUNT E ON E.ACCOUNTID = A.CLAIMACCOUNTID LEFT JOIN MEMPLOYEE F ON F.EMPLOYEEID = A.CREATEDBYID LEFT JOIN MEMPLOYEE G ON G.EMPLOYEEID = A.MODIFIEDBYID WHERE A.TDSCATEGORYID = @tdscategoryid;"; public const string GET_SELECTLIST_TDSCATEGORY = @"WITH PagedTDSCategory AS ( SELECT TC.TDSCATEGORYID AS Id, TC.TDSCATEGORYCODE AS Code, TC.TDSCATEGORYNAME AS Name, TC.TDSPERCENT AS TDSPercent, TC.SCPERCENT AS SCPercent, TC.CESSPERCENT AS CessPercent, ROW_NUMBER() OVER (ORDER BY TC.TDSCATEGORYNAME) AS RowNum FROM MTDSCATEGORY TC WHERE TC.STATUS = 1 ) SELECT Id, Code, Name, TDSPercent, SCPercent, CessPercent FROM PagedTDSCategory WHERE RowNum BETWEEN (@firstnumber + 1) AND (@firstnumber + @maxresult) ORDER BY RowNum;"; public const string SAVE_TDSCATEGORY = @"INSERT INTO MTDSCATEGORY ( TDSCATEGORYID, TDSCATEGORYCODE, TDSCATEGORYNAME, SECTION, REFERENCESECTION, FORM, TDSPERCENT, SCPERCENT, CESSPERCENT, DEDUCTIONACCOUNTID, PAIDACCOUNTID, SUFFEREDACCOUNTID, CLAIMACCOUNTID, REMARKS, SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID, ISCOMPANY, AMOUNTLIMIT, TRANSACTIONLIMIT ) VALUES ( @TDSCategoryId, @TDSCategoryCode, @TDSCategoryName, @TDSCategorySection, @TDSCategoryReferenceSection, @TDSCategoryForm, @TDSCategoryTDSPercent, @TDSCategorySCPercent, @TDSCategoryCessPercent, @DeducationAccountId, @PaidAccountId, @SufferedAccountId, @ClaimAccountId, @TDSCategoryRemarks, @TDSCategorySortOrder, @TDSCategoryStatus, @TDSCategoryVersion, @TDSCategoryCreatedById, @TDSCategoryCreatedOn, @TDSCategoryModifiedById, @TDSCategoryModifiedOn, @SourceType, @TenantId, @TDSCategoryIsCompany, @TDSCategoryAmountLimit, @TDSCategoryTransactionLimit );"; public const string UPDATE_TDSCATEGORY = @"UPDATE MTDSCATEGORY SET TDSCATEGORYCODE = @TDSCategoryCode, TDSCATEGORYNAME = @TDSCategoryName, SECTION = @TDSCategorySection, REFERENCESECTION = @TDSCategoryReferenceSection, FORM = @TDSCategoryForm, TDSPERCENT = @TDSCategoryTDSPercent, SCPERCENT = @TDSCategorySCPercent, CESSPERCENT = @TDSCategoryCessPercent, DEDUCTIONACCOUNTID = @DeducationAccountId, PAIDACCOUNTID = @PaidAccountId, SUFFEREDACCOUNTID = @SufferedAccountId, CLAIMACCOUNTID = @ClaimAccountId, REMARKS = @TDSCategoryRemarks, SORTORDER = @TDSCategorySortOrder, STATUS = @TDSCategoryStatus, VERSION = @TDSCategoryVersion + 1, MODIFIEDBYID = @TDSCategoryModifiedById, MODIFIEDON = @TDSCategoryModifiedOn, SOURCETYPE = @SourceType, TENANTID = @TenantId, ISCOMPANY = @TDSCategoryIsCompany, AMOUNTLIMIT = @TDSCategoryAmountLimit, TRANSACTIONLIMIT = @TDSCategoryTransactionLimit WHERE TDSCATEGORYID = @TDSCategoryId;"; public const string DELETE_TDSCATEGORY = @"DELETE FROM MTDSCATEGORY WHERE TDSCATEGORYID = @tdscategoryid;"; // Ported from GB4 TDSCategoryBLL/DAL.GetTDSCAtegoryForAccountNew (TDSCategory.svc's // /Account/?AccountId= operation) — given an account, returns every MTDSCATEGORY row that // references it in any of its four role-account slots (Deduction/Paid/Suffered/Claim). // Reuses the exact SELECT/JOIN shape of GET_TDSCATEGORY above (same confirmed columns), // only the WHERE clause differs. GB4's HQL WHERE was // "1=1 and Deduction=:id or Paid=:id or Suffered=:id or Claim=:id" — due to operator // precedence with no parentheses this parses as a plain 4-way OR (the leading "1=1 AND" // only binds to the first term and is a no-op once OR'd with the rest) — ported directly // as that 4-way OR, not "cleaned up" into anything narrower. public const string GET_TDSCATEGORY_FOR_ACCOUNT = @"SELECT A.TDSCATEGORYID AS TDSCategoryId, A.TDSCATEGORYCODE AS TDSCategoryCode, A.TDSCATEGORYNAME AS TDSCategoryName, A.SECTION AS TDSCategorySection, A.REFERENCESECTION AS TDSCategoryReferenceSection, A.FORM AS TDSCategoryForm, A.TDSPERCENT AS TDSCategoryTDSPercent, A.SCPERCENT AS TDSCategorySCPercent, A.CESSPERCENT AS TDSCategoryCessPercent, A.DEDUCTIONACCOUNTID AS DeducationAccountId, B.ACCOUNTCODE AS DeducationAccountCode, B.ACCOUNTNAME AS DeducationAccountName, A.PAIDACCOUNTID AS PaidAccountId, C.ACCOUNTCODE AS PaidAccountCode, C.ACCOUNTNAME AS PaidAccountName, A.SUFFEREDACCOUNTID AS SufferedAccountId, D.ACCOUNTCODE AS SufferedAccountCode, D.ACCOUNTNAME AS SufferedAccountName, A.CLAIMACCOUNTID AS ClaimAccountId, E.ACCOUNTCODE AS ClaimAccountCode, E.ACCOUNTNAME AS ClaimAccountName, A.REMARKS AS TDSCategoryRemarks, A.SORTORDER AS TDSCategorySortOrder, A.STATUS AS TDSCategoryStatus, A.VERSION AS TDSCategoryVersion, A.CREATEDBYID AS TDSCategoryCreatedById, A.CREATEDON AS TDSCategoryCreatedOn, A.MODIFIEDBYID AS TDSCategoryModifiedById, A.MODIFIEDON AS TDSCategoryModifiedOn, A.ISCOMPANY AS TDSCategoryIsCompany, A.AMOUNTLIMIT AS TDSCategoryAmountLimit, A.TRANSACTIONLIMIT AS TDSCategoryTransactionLimit FROM MTDSCATEGORY A LEFT JOIN MACCOUNT B ON B.ACCOUNTID = A.DEDUCTIONACCOUNTID LEFT JOIN MACCOUNT C ON C.ACCOUNTID = A.PAIDACCOUNTID LEFT JOIN MACCOUNT D ON D.ACCOUNTID = A.SUFFEREDACCOUNTID LEFT JOIN MACCOUNT E ON E.ACCOUNTID = A.CLAIMACCOUNTID WHERE A.DEDUCTIONACCOUNTID = @AccountId OR A.PAIDACCOUNTID = @AccountId OR A.SUFFEREDACCOUNTID = @AccountId OR A.CLAIMACCOUNTID = @AccountId;"; // Ported from GB4 TDSCategoryBLL/DAL.GetTDSDetailsFromVoucher (TDSCategory.svc's // /GetTDSDetailsFromVoucher/ operation). Despite the operation name, GB4's own criteria // map has NO VoucherId field at all — it never filters to one specific voucher. It's // really a "pending/unpaid TDS lines, optionally by category and credit-date range, // scoped to the caller's current OU" listing. Ported with the same behavior, not // "fixed" to add a VoucherId filter that GB4 itself never had. // TVOUCHERTDS schema (VOUCHERTDSID/TDSCATEGORYID/REFERENCENUMBER/REFERENCEDATE/ACCOUNTID/ // CREDITDATE/TOTALAMOUNT/PAIDTDSAMOUNT/ROUNDEDAMOUNT/VOUCHERLINEID, no TENANTID column, // true FK to TVOUCHERDETAIL is VOUCHERLINEID not VOUCHERDETAILID) already confirmed live // and in use by VoucherQB.GET_VOUCHER_TDS above. // GB4 built the OU filter via raw string concatenation ("AND VC.OUID=" + WorkOUId) — // parameterized here instead, consistent with every other port this session. // Only VTDS.TOTALAMOUNT/TDSCategoryName/ReferenceNumber/ReferenceDate/AccountName/ // CreditDate/TotalAmount/RoundedAmount are populated — every other VoucherTDSDTO field // stays at its default, matching GB4's own minimal projection exactly. public const string GET_TDSDETAILS_FOR_VOUCHER = @" SELECT VTDS.VOUCHERTDSID AS VoucherTDSId, TDSCAT.TDSCATEGORYNAME AS TDSCategoryName, VTDS.REFERENCENUMBER AS VoucherTDSReferenceNumber, VTDS.REFERENCEDATE AS VoucherTDSReferenceDate, ACC.ACCOUNTNAME AS AccountName, VTDS.CREDITDATE AS VoucherTDSCreditDate, CAST(VTDS.TOTALAMOUNT - VTDS.PAIDTDSAMOUNT AS FLOAT) AS VoucherTDSTotalAmount, CAST(VTDS.ROUNDEDAMOUNT - VTDS.PAIDTDSAMOUNT AS FLOAT) AS VoucherTDSRoundedAmount FROM TVOUCHERTDS VTDS INNER JOIN MTDSCATEGORY TDSCAT ON VTDS.TDSCATEGORYID = TDSCAT.TDSCATEGORYID INNER JOIN MACCOUNT ACC ON ACC.ACCOUNTID = VTDS.ACCOUNTID INNER JOIN TVOUCHERDETAIL VCD ON VCD.VOUCHERDETAILID = VTDS.VOUCHERLINEID INNER JOIN TVOUCHER VC ON VC.VOUCHERID = VCD.VOUCHERID WHERE (VTDS.TOTALAMOUNT - VTDS.PAIDTDSAMOUNT) > 0 AND VC.OUID = @OUId AND (@CategoryId = -1 OR TDSCAT.TDSCATEGORYID = @CategoryId) AND (@PeriodFromDate IS NULL OR VTDS.CREDITDATE >= @PeriodFromDate) AND (@PeriodToDate IS NULL OR VTDS.CREDITDATE <= @PeriodToDate);"; } }