using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace AccountsDAL.Query.Account { public static class AccountQB { public const string GET_ACCOUNT_ID = @"SELECT A.ACCOUNTID AS AccountId, A.ACCOUNTCODE AS AccountCode, A.ACCOUNTNAME AS AccountName, A.ACCOUNTSHORTNAME AS AccountShortName, A.CODEDEFINEID AS CodeDefineId, CD.CATEGORYCODE AS CodeDefineCategoryCode, CD.CATEGORYNAME AS CodeDefineCategoryName, A.ACCOUNTTYPE AS AccountType, 'ControlAccount' AS AccountTypeName, A.ACCOUNTGROUPID AS AccountGroupId, AG.ACCOUNTGROUPCODE AS AccountGroupCode, AG.ACCOUNTGROUPNAME AS AccountGroupName, AG.ACCOUNTGROUPSHORTNAME AS AccountGroupShortName, AG.ACCOUNTSCHEDULEID AS AccountGroupAccountGroupAccountScheduleId, AGS.ACCOUNTSCHEDULECODE AS AccountGroupAccountGroupAccountScheduleCode, AGS.ACCOUNTSCHEDULENAME AS AccountGroupAccountGroupAccountScheduleName, AGS.ACCOUNTSCHEDULENATURE AS AccountGroupAccountGroupAccountScheduleNature, A.CONTROLACCOUNTID AS ParentId, PARENT.ACCOUNTCODE AS ParentCode, PARENT.ACCOUNTNAME AS ParentName, PARENT.ACCOUNTSHORTNAME AS ParentShortName, A.FAVOURINGNAME AS AccountFavouringName, A.BILLALLOCATIONTYPE AS AccountBillAllocationType, A.COMBINEACCOUNTID AS CombineAccountId, COMBINE.ACCOUNTCODE AS CombineAccountCode, COMBINE.ACCOUNTNAME AS CombineAccountName, A.CURRENCYID AS CurrencyId, C.CURRENCYCODE AS CurrencyCode, C.CURRENCYNAME AS CurrencyName, A.INTERESTRATE AS AccountInterestRate, A.LEDGERPRINTTYPE AS AccountLedgerPrintType, A.SUMMARYPERIOD AS AccountSummaryPeriod, A.ISCOMPANY AS AccountIsCompany, A.ISINTERCOMPANY AS AccountIsInterCompany, A.ISTDSAPPLICABLE AS AccountIsTDSApplicable, A.TDSSTATE AS AccountTDSState, A.TDSCATEGORYID AS TDSCategoryId, T.TDSCATEGORYCODE AS TDSCategoryCode, T.TDSCATEGORYNAME AS TDSCategoryName, A.ISSUBACCOUNTREQUIRED AS AccountIsSubAccountRequired, BB.BANKID AS BankId, BB.SWIFT AS BankSwiftCode, BB.BANKBRANCHID AS BankBranchId, BB.BANKBRANCHNAME AS BankBranchName, A.BANKACCOUNTNUMBER AS AccountBankAccountNumber, A.BANKACCOUNTTYPE AS AccountBankAccountType, BB.IFSC AS AccountIFSCCode, A.SAFETYLIMIT AS AccountSafetyLimit, A.MINIMUMBALANCE AS AccountMinimumBalance, A.OURCODEWITHPARTY AS AccountOurCodeWithParty, A.ISCREDITALLOWED AS AccountIsCreditAllowed, A.CREDITLIMIT AS AccountCreditLimit, A.ISDEBITALLOWED AS AccountIsDebitAllowed, A.DEBITLIMIT AS AccountDebitLimit, A.ISBLOCKED AS AccountIsBlocked, A.SORTORDER AS AccountSortOrder, A.STATUS AS AccountStatus, A.VERSION AS AccountVersion, A.CREATEDBYID AS AccountCreatedById, U_CREATED.USERNAME AS AccountCreatedByName, A.CREATEDON AS AccountCreatedOn, A.MODIFIEDBYID AS AccountModifiedById, U_MODIFIED.USERNAME AS AccountModifiedByName, A.MODIFIEDON AS AccountModifiedOn, A.SOURCETYPE AS AccountSourceType, A.TENANTID AS BaseTenantId, A.ACCOUNTSCHEDULENATURE AS AccountAccountScheduleNature, A.TDSSECTION AS AccountTDSSection, A.ACCOUNTBANKBRANCHID AS AccountBankBranchId, BB.BANKBRANCHNAME AS AccountBankBranchName, BB.MAILID AS AccountBankBranchMailId, BB.PHONE AS AccountBankBranchPhone, BB.MOBILE AS AccountBankBranchMobile, BB.BANKID AS AccountBankBranchBankId, BB.BANKBRANCHNAME AS AccountBankBranchBankName, BA.ADDRESSID AS AccountBankBranchAddressId, BA.ADDRESSLINE1 AS AccountBankBranchAddress1, BA.ADDRESSLINE2 AS AccountBankBranchAddress2, BA.ADDRESSLINE3 AS AccountBankBranchAddress3, BA.ADDRESSLINE4 AS AccountBankBranchAddress4, BA.ADDRESSLINE5 AS AccountBankBranchAddress5, BA.ZIPCODE AS AccountBankBranchZipCode, BA.STATEID AS AccountBankBranchStateId, S.STATECODE AS AccountBankBranchStateCode, S.STATENAME AS AccountBankBranchStateName, BA.CITYID AS AccountBankBranchCityId, CT.CITYCODE AS AccountBankBranchCityCode, CT.CITYNAME AS AccountBankBranchCityName, BA.COUNTRYID AS AccountBankBranchCountryId, CN.COUNTRYCODE AS AccountBankBranchCountryCode, CN.COUNTRYNAME AS AccountBankBranchCountryName, A.APPLICABLECOSTCENTERIDS AS AccountApplicableCostCenterIds, -- STRING_SPLIT/STRING_AGG need SQL Server 2017+/compat level 140+ — some environments (e.g. -- QC) run an older compat level where those are flagged as an invalid object name. FOR XML PATH + STUFF is -- the pre-2016-compatible equivalent: pad both the CSV and each candidate ID with commas so a -- LIKE match can never hit a partial-number false positive (e.g. id 12 inside 123). ( SELECT ISNULL(STUFF(( SELECT ',' + CC.COSTCENTERNAME FROM MCOSTCENTER CC WHERE ',' + A.APPLICABLECOSTCENTERIDS + ',' LIKE '%,' + CAST(CC.COSTCENTERID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS AccountApplicableCostCenterNames, PB.PARTYBRANCHCODE AS AccountBankBranchCode, PB.PARTYBRANCHCODE AS AccountBankBranchBankCode, PB.PARTYBRANCHCODE AS BankCode, PB.PARTYBRANCHNAME AS BankName, PB.PARTYBRANCHCODE AS BankBranchCode, PB.SEQUENCENUMBER AS VoucherNumber FROM MACCOUNT A LEFT JOIN MCODEDEFINE CD ON A.CODEDEFINEID = CD.CODEDEFINEID LEFT JOIN MACCOUNTGROUP AG ON A.ACCOUNTGROUPID = AG.ACCOUNTGROUPID LEFT JOIN MACCOUNTSCHEDULE AGS ON AG.ACCOUNTSCHEDULEID = AGS.ACCOUNTSCHEDULEID LEFT JOIN MACCOUNT PARENT ON A.CONTROLACCOUNTID = PARENT.ACCOUNTID LEFT JOIN MACCOUNT COMBINE ON A.COMBINEACCOUNTID = COMBINE.ACCOUNTID LEFT JOIN MCURRENCY C ON A.CURRENCYID = C.CURRENCYID LEFT JOIN MTDSCATEGORY T ON A.TDSCATEGORYID = T.TDSCATEGORYID LEFT JOIN MBANKBRANCH BB ON A.ACCOUNTBANKBRANCHID = BB.BANKBRANCHID LEFT JOIN MUSER U_CREATED ON A.CREATEDBYID = U_CREATED.USERID LEFT JOIN MUSER U_MODIFIED ON A.MODIFIEDBYID = U_MODIFIED.USERID LEFT JOIN MADDRESS BA ON BB.ADDRESSID = BA.ADDRESSID LEFT JOIN MSTATE S ON BA.STATEID = S.STATEID LEFT JOIN MCITY CT ON BA.CITYID = CT.CITYID LEFT JOIN MCOUNTRY CN ON BA.COUNTRYID = CN.COUNTRYID LEFT JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID = A.ACCOUNTID WHERE A.ACCOUNTID = @accountid; "; // Full-DTO account list — same column set/joins as GET_ACCOUNT_ID (returns AccountDTO, not // AccountPicklistDTO) but for every matching row instead of one, criteria-filtered like // Get_Select_List_Account below rather than paged (mirrors GetShowScheduleList's shape — // no FirstNumber/MaxResult, whole-CriteriaDTO List call, not a SelectList). public const string GET_ACCOUNT_LIST = @"SELECT A.ACCOUNTID AS AccountId, A.ACCOUNTCODE AS AccountCode, A.ACCOUNTNAME AS AccountName, A.ACCOUNTSHORTNAME AS AccountShortName, A.CODEDEFINEID AS CodeDefineId, CD.CATEGORYCODE AS CodeDefineCategoryCode, CD.CATEGORYNAME AS CodeDefineCategoryName, A.ACCOUNTTYPE AS AccountType, 'ControlAccount' AS AccountTypeName, A.ACCOUNTGROUPID AS AccountGroupId, AG.ACCOUNTGROUPCODE AS AccountGroupCode, AG.ACCOUNTGROUPNAME AS AccountGroupName, AG.ACCOUNTGROUPSHORTNAME AS AccountGroupShortName, AG.ACCOUNTSCHEDULEID AS AccountGroupAccountGroupAccountScheduleId, AGS.ACCOUNTSCHEDULECODE AS AccountGroupAccountGroupAccountScheduleCode, AGS.ACCOUNTSCHEDULENAME AS AccountGroupAccountGroupAccountScheduleName, AGS.ACCOUNTSCHEDULENATURE AS AccountGroupAccountGroupAccountScheduleNature, A.CONTROLACCOUNTID AS ParentId, PARENT.ACCOUNTCODE AS ParentCode, PARENT.ACCOUNTNAME AS ParentName, PARENT.ACCOUNTSHORTNAME AS ParentShortName, A.FAVOURINGNAME AS AccountFavouringName, A.BILLALLOCATIONTYPE AS AccountBillAllocationType, A.COMBINEACCOUNTID AS CombineAccountId, COMBINE.ACCOUNTCODE AS CombineAccountCode, COMBINE.ACCOUNTNAME AS CombineAccountName, A.CURRENCYID AS CurrencyId, C.CURRENCYCODE AS CurrencyCode, C.CURRENCYNAME AS CurrencyName, A.INTERESTRATE AS AccountInterestRate, A.LEDGERPRINTTYPE AS AccountLedgerPrintType, A.SUMMARYPERIOD AS AccountSummaryPeriod, A.ISCOMPANY AS AccountIsCompany, A.ISINTERCOMPANY AS AccountIsInterCompany, A.ISTDSAPPLICABLE AS AccountIsTDSApplicable, A.TDSSTATE AS AccountTDSState, A.TDSCATEGORYID AS TDSCategoryId, T.TDSCATEGORYCODE AS TDSCategoryCode, T.TDSCATEGORYNAME AS TDSCategoryName, A.ISSUBACCOUNTREQUIRED AS AccountIsSubAccountRequired, BB.BANKID AS BankId, BB.SWIFT AS BankSwiftCode, BB.BANKBRANCHID AS BankBranchId, BB.BANKBRANCHNAME AS BankBranchName, A.BANKACCOUNTNUMBER AS AccountBankAccountNumber, A.BANKACCOUNTTYPE AS AccountBankAccountType, BB.IFSC AS AccountIFSCCode, A.SAFETYLIMIT AS AccountSafetyLimit, A.MINIMUMBALANCE AS AccountMinimumBalance, A.OURCODEWITHPARTY AS AccountOurCodeWithParty, A.ISCREDITALLOWED AS AccountIsCreditAllowed, A.CREDITLIMIT AS AccountCreditLimit, A.ISDEBITALLOWED AS AccountIsDebitAllowed, A.DEBITLIMIT AS AccountDebitLimit, A.ISBLOCKED AS AccountIsBlocked, A.SORTORDER AS AccountSortOrder, A.STATUS AS AccountStatus, A.VERSION AS AccountVersion, A.CREATEDBYID AS AccountCreatedById, U_CREATED.USERNAME AS AccountCreatedByName, A.CREATEDON AS AccountCreatedOn, A.MODIFIEDBYID AS AccountModifiedById, U_MODIFIED.USERNAME AS AccountModifiedByName, A.MODIFIEDON AS AccountModifiedOn, A.SOURCETYPE AS AccountSourceType, A.TENANTID AS BaseTenantId, A.ACCOUNTSCHEDULENATURE AS AccountAccountScheduleNature, A.TDSSECTION AS AccountTDSSection, A.ACCOUNTBANKBRANCHID AS AccountBankBranchId, BB.BANKBRANCHNAME AS AccountBankBranchName, BB.MAILID AS AccountBankBranchMailId, BB.PHONE AS AccountBankBranchPhone, BB.MOBILE AS AccountBankBranchMobile, BB.BANKID AS AccountBankBranchBankId, BB.BANKBRANCHNAME AS AccountBankBranchBankName, BA.ADDRESSID AS AccountBankBranchAddressId, BA.ADDRESSLINE1 AS AccountBankBranchAddress1, BA.ADDRESSLINE2 AS AccountBankBranchAddress2, BA.ADDRESSLINE3 AS AccountBankBranchAddress3, BA.ADDRESSLINE4 AS AccountBankBranchAddress4, BA.ADDRESSLINE5 AS AccountBankBranchAddress5, BA.ZIPCODE AS AccountBankBranchZipCode, BA.STATEID AS AccountBankBranchStateId, S.STATECODE AS AccountBankBranchStateCode, S.STATENAME AS AccountBankBranchStateName, BA.CITYID AS AccountBankBranchCityId, CT.CITYCODE AS AccountBankBranchCityCode, CT.CITYNAME AS AccountBankBranchCityName, BA.COUNTRYID AS AccountBankBranchCountryId, CN.COUNTRYCODE AS AccountBankBranchCountryCode, CN.COUNTRYNAME AS AccountBankBranchCountryName, A.APPLICABLECOSTCENTERIDS AS AccountApplicableCostCenterIds, ( SELECT ISNULL(STUFF(( SELECT ',' + CC.COSTCENTERNAME FROM MCOSTCENTER CC WHERE ',' + A.APPLICABLECOSTCENTERIDS + ',' LIKE '%,' + CAST(CC.COSTCENTERID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS AccountApplicableCostCenterNames, PB.PARTYBRANCHCODE AS AccountBankBranchCode, PB.PARTYBRANCHCODE AS AccountBankBranchBankCode, PB.PARTYBRANCHCODE AS BankCode, PB.PARTYBRANCHNAME AS BankName, PB.PARTYBRANCHCODE AS BankBranchCode, PB.SEQUENCENUMBER AS VoucherNumber FROM MACCOUNT A LEFT JOIN MCODEDEFINE CD ON A.CODEDEFINEID = CD.CODEDEFINEID LEFT JOIN MACCOUNTGROUP AG ON A.ACCOUNTGROUPID = AG.ACCOUNTGROUPID LEFT JOIN MACCOUNTSCHEDULE AGS ON AG.ACCOUNTSCHEDULEID = AGS.ACCOUNTSCHEDULEID LEFT JOIN MACCOUNT PARENT ON A.CONTROLACCOUNTID = PARENT.ACCOUNTID LEFT JOIN MACCOUNT COMBINE ON A.COMBINEACCOUNTID = COMBINE.ACCOUNTID LEFT JOIN MCURRENCY C ON A.CURRENCYID = C.CURRENCYID LEFT JOIN MTDSCATEGORY T ON A.TDSCATEGORYID = T.TDSCATEGORYID LEFT JOIN MBANKBRANCH BB ON A.ACCOUNTBANKBRANCHID = BB.BANKBRANCHID LEFT JOIN MUSER U_CREATED ON A.CREATEDBYID = U_CREATED.USERID LEFT JOIN MUSER U_MODIFIED ON A.MODIFIEDBYID = U_MODIFIED.USERID LEFT JOIN MADDRESS BA ON BB.ADDRESSID = BA.ADDRESSID LEFT JOIN MSTATE S ON BA.STATEID = S.STATEID LEFT JOIN MCITY CT ON BA.CITYID = CT.CITYID LEFT JOIN MCOUNTRY CN ON BA.COUNTRYID = CN.COUNTRYID LEFT JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID = A.ACCOUNTID WHERE (@SearchText IS NULL OR @SearchText = '' OR A.ACCOUNTCODE LIKE '%' + @SearchText + '%' OR A.ACCOUNTNAME LIKE '%' + @SearchText + '%' OR A.ACCOUNTSHORTNAME LIKE '%' + @SearchText + '%') AND (@StatusEquals IS NULL OR A.STATUS = @StatusEquals) AND (@StatusNotEquals IS NULL OR A.STATUS <> @StatusNotEquals) ORDER BY A.ACCOUNTCODE; "; // @SearchText filters AccountCode/AccountName/AccountShortName (LIKE, OR'd) — mirrors // PartyQB.GET_SELECTLIST_PARTY's fix: this query previously ignored CriteriaDTO entirely, // always returning the same static first-N-by-id page regardless of what was typed. The // pagination boundary is also 0-based-inclusive (not "+1") so a search matching exactly one // row (RowNum 1) isn't silently skipped when FirstNumber=1. public const string Get_Select_List_Account = @" WITH PagedAccount AS ( SELECT MA.ACCOUNTID AS Id, MA.ACCOUNTCODE AS Code, MA.ACCOUNTNAME AS Name, MA.ACCOUNTSHORTNAME AS ShortName, MA.BANKACCOUNTTYPE AS AccountType, MA.BANKACCOUNTNUMBER AS BankAccountNumber, MA.ACCOUNTGROUPID AS AccountGroupId, ROW_NUMBER() OVER (ORDER BY MA.ACCOUNTID) AS RowNum FROM MACCOUNT MA WHERE (@SearchText IS NULL OR @SearchText = '' OR MA.ACCOUNTCODE LIKE '%' + @SearchText + '%' OR MA.ACCOUNTNAME LIKE '%' + @SearchText + '%' OR MA.ACCOUNTSHORTNAME LIKE '%' + @SearchText + '%') AND (@StatusEquals IS NULL OR MA.STATUS = @StatusEquals) AND (@StatusNotEquals IS NULL OR MA.STATUS <> @StatusNotEquals) ) SELECT Id, Code, Name, ShortName, AccountType, BankAccountNumber, AccountGroupId FROM PagedAccount WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND (@firstnumber + @maxresult - 1)); "; // ── Save / Update / Delete ──────────────────────────────────────────── //public const string CHECK_ACCOUNT_IN_VOUCHERS = @" // SELECT CASE WHEN EXISTS ( // SELECT 1 FROM TVOUCHERDETAIL WHERE ACCOUNTID = @AccountId // ) THEN 1 ELSE 0 END"; public const string SAVE_ACCOUNT = @" INSERT INTO MACCOUNT ( ACCOUNTID, ACCOUNTCODE, ACCOUNTNAME, ACCOUNTSHORTNAME, ACCOUNTTYPE, ACCOUNTGROUPID, CONTROLACCOUNTID, CURRENCYID, COMBINEACCOUNTID, ACCOUNTBANKBRANCHID, TDSCATEGORYID, INTERESTRATE, FAVOURINGNAME, BILLALLOCATIONTYPE, LEDGERPRINTTYPE, SUMMARYPERIOD, ISCOMPANY, ISINTERCOMPANY, ISTDSAPPLICABLE, TDSSTATE, ISSUBACCOUNTREQUIRED, BANKACCOUNTNUMBER, BANKACCOUNTTYPE, SAFETYLIMIT, MINIMUMBALANCE, OURCODEWITHPARTY, ISCREDITALLOWED, CREDITLIMIT, ISDEBITALLOWED, DEBITLIMIT, ISBLOCKED, SORTORDER, STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, ACCOUNTSCHEDULENATURE, TDSSECTION, APPLICABLECOSTCENTERIDS,CODEDEFINEID,DEFAULTADDRESSID,TENANTID ) VALUES ( @AccountId, @AccountCode, @AccountName, @AccountShortName, @AccountType, @AccountGroupId, @ParentId, @CurrencyId, @CombineAccountId, @AccountBankBranchId, @TDSCategoryId, @AccountInterestRate, @AccountFavouringName, @AccountBillAllocationType, @AccountLedgerPrintType, @AccountSummaryPeriod, @AccountIsCompany, @AccountIsInterCompany, @AccountIsTDSApplicable, @AccountTDSState, @AccountIsSubAccountRequired, @AccountBankAccountNumber, @AccountBankAccountType, @AccountSafetyLimit, @AccountMinimumBalance, @AccountOurCodeWithParty, @AccountIsCreditAllowed, @AccountCreditLimit, @AccountIsDebitAllowed, @AccountDebitLimit, @AccountIsBlocked, @AccountSortOrder, @AccountStatus, @AccountVersion, @AccountSourceType, @AccountCreatedById, @AccountCreatedOn, @AccountModifiedById, @AccountModifiedOn, @AccountAccountScheduleNature, @AccountTDSSection, @AccountApplicableCostCenterIds,@CodeDefineId,@DefaultAddressId,@BaseTenantId )"; public const string UPDATE_ACCOUNT = @" UPDATE MACCOUNT SET ACCOUNTCODE = @AccountCode, ACCOUNTNAME = @AccountName, ACCOUNTSHORTNAME = @AccountShortName, ACCOUNTTYPE = @AccountType, ACCOUNTGROUPID = @AccountGroupId, CONTROLACCOUNTID = @ParentId, CURRENCYID = @CurrencyId, COMBINEACCOUNTID = @CombineAccountId, ACCOUNTBANKBRANCHID = @AccountBankBranchId, TDSCATEGORYID = @TDSCategoryId, INTERESTRATE = @AccountInterestRate, FAVOURINGNAME = @AccountFavouringName, BILLALLOCATIONTYPE = @AccountBillAllocationType, LEDGERPRINTTYPE = @AccountLedgerPrintType, SUMMARYPERIOD = @AccountSummaryPeriod, ISCOMPANY = @AccountIsCompany, ISINTERCOMPANY = @AccountIsInterCompany, ISTDSAPPLICABLE = @AccountIsTDSApplicable, TDSSTATE = @AccountTDSState, ISSUBACCOUNTREQUIRED = @AccountIsSubAccountRequired, BANKACCOUNTNUMBER = @AccountBankAccountNumber, BANKACCOUNTTYPE = @AccountBankAccountType, SAFETYLIMIT = @AccountSafetyLimit, MINIMUMBALANCE = @AccountMinimumBalance, OURCODEWITHPARTY = @AccountOurCodeWithParty, ISCREDITALLOWED = @AccountIsCreditAllowed, CREDITLIMIT = @AccountCreditLimit, ISDEBITALLOWED = @AccountIsDebitAllowed, DEBITLIMIT = @AccountDebitLimit, ISBLOCKED = @AccountIsBlocked, SORTORDER = @AccountSortOrder, STATUS = @AccountStatus, VERSION = @AccountVersion, MODIFIEDBYID = @AccountModifiedById, MODIFIEDON = @AccountModifiedOn, SOURCETYPE = @AccountSourceType, ACCOUNTSCHEDULENATURE = @AccountAccountScheduleNature, TDSSECTION = @AccountTDSSection, APPLICABLECOSTCENTERIDS = @AccountApplicableCostCenterIds, CODEDEFINEID = @CodeDefineId, DEFAULTADDRESSID = @DefaultAddressId, TENANTID = @BaseTenantId WHERE ACCOUNTID = @AccountId"; //public const string DELETE_ACCOUNT = @" // DELETE FROM MACCOUNT WHERE ACCOUNTID = @AccountId"; // ── Additional read queries ─────────────────────────────────────────── public const string GET_ACCOUNT_DETAILS_SQL = @" SELECT A.ACCOUNTID AS AccountId, A.ACCOUNTCODE AS AccountCode, A.ACCOUNTNAME AS AccountName, A.ACCOUNTGROUPID AS AccountGroupId, A.FAVOURINGNAME AS AccountFavouringName, A.CURRENCYID AS CurrencyId, C.CURRENCYCODE AS CurrencyCode, AG.ACCOUNTSCHEDULEID AS AccountGroupId, S.ACCOUNTSCHEDULENATURE AS AccountGroupAccountGroupAccountScheduleNature, A.TDSCATEGORYID AS TDSCategoryId, T.TDSCATEGORYNAME AS TDSCategoryName, A.ISTDSAPPLICABLE AS AccountIsTDSApplicable, A.ACCOUNTTYPE AS AccountType, A.BILLALLOCATIONTYPE AS AccountBillAllocationType, A.CREDITLIMIT AS AccountCreditLimit FROM MACCOUNT A LEFT JOIN MCURRENCY C ON A.CURRENCYID = C.CURRENCYID LEFT JOIN MACCOUNTGROUP AG ON A.ACCOUNTGROUPID = AG.ACCOUNTGROUPID LEFT JOIN MACCOUNTSCHEDULE S ON AG.ACCOUNTSCHEDULEID = S.ACCOUNTSCHEDULEID LEFT JOIN MTDSCATEGORY T ON A.TDSCATEGORYID = T.TDSCATEGORYID WHERE A.ACCOUNTID = @AccountId"; public const string GET_ACCOUNT_PICKLIST_FOR_POPUP = @" SELECT A.ACCOUNTID AS Id, A.ACCOUNTCODE AS Code, A.ACCOUNTNAME AS Name, A.ACCOUNTSHORTNAME AS ShortName, A.ACCOUNTTYPE AS AccountType, A.BANKACCOUNTNUMBER AS BankAccountNumber, A.ACCOUNTGROUPID AS AccountGroupId FROM MACCOUNT A WHERE A.ISBLOCKED = 0 AND (@SearchKeyword IS NULL OR @SearchKeyword = '' OR A.ACCOUNTCODE LIKE @SearchKeyword + '%' OR A.ACCOUNTNAME LIKE @SearchKeyword + '%') ORDER BY A.ACCOUNTNAME"; public const string GET_ACCOUNT_VS_COMPANY_SELECTLIST = @" WITH PagedAccount AS ( SELECT A.ACCOUNTID AS Id, A.ACCOUNTCODE AS Code, A.ACCOUNTNAME AS Name, A.ACCOUNTSHORTNAME AS ShortName, PA.CREDITALLOWED AS IsCreditAllowed, PA.CREDITLIMIT AS CreditLimit, PA.DEBITALLOWED AS IsDebitAllowed, PA.DEBITLIMIT AS DebitLimit, A.ACCOUNTTYPE AS AccountType, AD.PHONE AS Phone, AD.MOBILE AS Mobile, PB.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, A.ACCOUNTGROUPID AS AccountGroupId, A.BANKACCOUNTNUMBER AS BankAccountNumber, ROW_NUMBER() OVER (ORDER BY A.ACCOUNTNAME) AS RowNum FROM MACCOUNT A INNER JOIN MPARTYACCOUNTOU PA ON PA.ACCOUNTID = A.ACCOUNTID AND PA.OUID = @ouid LEFT JOIN MPARTYBRANCH PB ON PB.PARTYID = A.ACCOUNTID LEFT JOIN MADDRESS AD ON AD.ADDRESSID = PB.DEFAULTADDRESSID WHERE A.ACCOUNTID <> -1 AND A.ISBLOCKED = 1 AND A.ACCOUNTTYPE <> 4 AND (@status = -1 OR A.STATUS = @status) AND (@accountscode IS NULL OR A.ACCOUNTCODE LIKE '%' + @accountscode + '%' OR A.ACCOUNTNAME LIKE '%' + @accountscode + '%') AND (@accountsname IS NULL OR A.ACCOUNTCODE LIKE '%' + @accountsname + '%' OR A.ACCOUNTNAME LIKE '%' + @accountsname + '%') ) SELECT Id, Code, Name, ShortName, IsCreditAllowed, CreditLimit, IsDebitAllowed, DebitLimit, AccountType, Phone, Mobile, PartyBranchId, PartyBranchCode, PartyBranchName, AccountGroupId, BankAccountNumber FROM PagedAccount WHERE RowNum BETWEEN (@firstnumber + 1) AND (@firstnumber + @maxresult) ORDER BY RowNum;"; public const string GET_POPUP_DETAILS = @" SELECT A.ACCOUNTID AS AccountId, A.ACCOUNTCODE AS AccountCode, A.ACCOUNTNAME AS AccountName, CASE A.ACCOUNTTYPE WHEN 0 THEN 'Cash' WHEN 1 THEN 'Bank' WHEN 2 THEN 'General Ledger' WHEN 3 THEN 'SL Control' WHEN 4 THEN 'GL Control' WHEN 5 THEN 'Subsidiary Books' ELSE 'Unknown' END AS AccountType, A.ACCOUNTGROUPID AS AccountGroupId, AG.ACCOUNTGROUPCODE AS AccountGroupCode, AG.ACCOUNTGROUPNAME AS AccountGroupName, CASE A.ISBLOCKED WHEN 1 THEN 'Blocked' ELSE 'Active' END AS AccountIsBlocked, A.CONTROLACCOUNTID AS ControlAccountId, PA.ACCOUNTCODE AS ControlAccountCode, PA.ACCOUNTNAME AS ControlAccountName, CASE A.BILLALLOCATIONTYPE WHEN 0 THEN 'None' WHEN 1 THEN 'Bill-wise' WHEN 2 THEN 'On Account' ELSE 'Unknown' END AS AccountBillAllocationType, CASE A.STATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted' WHEN 3 THEN 'Amended' WHEN 4 THEN 'Inactive' WHEN 5 THEN 'Archived' ELSE 'Unknown' END AS AccountStatus, ISNULL((SELECT SUM(AP.OPENINGBALANCE) FROM TACCOUNTPOST AP WHERE AP.ACCOUNTID = A.ACCOUNTID), 0) AS Balance, CASE WHEN ISNULL((SELECT SUM(AP.OPENINGBALANCE) FROM TACCOUNTPOST AP WHERE AP.ACCOUNTID = A.ACCOUNTID), 0) >= 0 THEN 'Dr' ELSE 'Cr' END AS DC, (SELECT COUNT(1) FROM MADDRESS AD INNER JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID = AD.OWNERID WHERE PB.PARTYID = A.ACCOUNTID) AS NoOfAddress, (SELECT COUNT(1) FROM MCONTACT CO INNER JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID = CO.OWNERID WHERE PB.PARTYID = A.ACCOUNTID) AS NoOfContact FROM MACCOUNT A LEFT JOIN MACCOUNTGROUP AG ON A.ACCOUNTGROUPID = AG.ACCOUNTGROUPID LEFT JOIN MACCOUNT PA ON A.CONTROLACCOUNTID = PA.ACCOUNTID WHERE A.ACCOUNTID = @AccountId"; public const string GET_ACCOUNT_PARAMETER = @"SELECT ROW_NUMBER() OVER (ORDER BY C.PARAMETERSETLINEID) AS SlNo, E.PARAMETERSETID AS ParameterSetId, E.PARAMETERSETCODE AS ParameterSetCode, E.PARAMETERSETNAME AS ParameterSetName, D.PARAMETERID AS ParameterId, D.PARAMETERCODE AS ParameterCode, D.PARAMETERNAME AS ParameterName, F.UOMID AS UOMId, F.UOMCODE AS UOMCode, F.UOMNAME AS UOMName, D.MINVALUE AS MinValue, D.MAXVALUE AS MaxValue FROM MPARTYBRANCHINFO A INNER JOIN ( SELECT MIN(A.PARTYBRANCHINFOID) AS PARTYBRANCHINFOID FROM MPARTYBRANCHINFO A INNER JOIN MPARAMETERSET B ON A.PARAMETERSETID = B.PARAMETERSETID INNER JOIN MPARTYBRANCH C ON A.PARTYBRANCHID = C.PARTYBRANCHID WHERE C.PARTYID = @accountid ) B ON A.PARTYBRANCHINFOID = B.PARTYBRANCHINFOID INNER JOIN MPARAMETERSETDETAIL C ON A.PARAMETERSETID = C.PARAMETERSETID INNER JOIN MPARAMETER D ON C.PARAMETERID = D.PARAMETERID INNER JOIN MPARAMETERSET E ON A.PARAMETERSETID = E.PARAMETERSETID INNER JOIN MUOM F ON D.UOMID = F.UOMID ORDER BY C.PARAMETERSETLINEID;"; public const string GET_SELECTLIST_CONTROLACCOUNT = @"SELECT A.ACCOUNTID AS Id, A.ACCOUNTCODE AS Code, A.ACCOUNTNAME AS Name, A.ACCOUNTSHORTNAME AS ShortName, A.ISCREDITALLOWED AS IsCreditAllowed, A.CREDITLIMIT AS CreditLimit, A.ISDEBITALLOWED AS IsDebitAllowed, A.DEBITLIMIT AS DebitLimit, A.ACCOUNTTYPE AS AccountType FROM MACCOUNT A WHERE A.ACCOUNTTYPE IN (4) AND (@Id IS NULL OR @Id = '' OR A.ACCOUNTID LIKE '%' + @Id + '%') AND ((@Code IS NULL OR @Code = '') AND (@Name IS NULL OR @Name = '') OR A.ACCOUNTCODE LIKE '%' + @Code + '%' OR A.ACCOUNTNAME LIKE '%' + @Name + '%' ); "; // GeneralLedger has no dedicated table in this schema (confirmed: no MGENERALLEDGER // table anywhere in this module) — the GB4 GeneralLedger entity is chart-of-accounts // data (ChartOfAccountId/ControlAccountId fields), so this select list is served // directly off MACCOUNT, same table/columns as GET_SELECTLIST_CONTROLACCOUNT, just // without that query's ACCOUNTTYPE IN (4) control-account restriction. public const string GET_SELECTLIST_GENERALLEDGER = @"SELECT A.ACCOUNTID AS Id, A.ACCOUNTCODE AS Code, A.ACCOUNTNAME AS Name FROM MACCOUNT A WHERE (@Id IS NULL OR @Id = '' OR A.ACCOUNTID LIKE '%' + @Id + '%') AND ((@Code IS NULL OR @Code = '') AND (@Name IS NULL OR @Name = '') OR A.ACCOUNTCODE LIKE '%' + @Code + '%' OR A.ACCOUNTNAME LIKE '%' + @Name + '%' ); "; public const string DELETE_ACCOUNT = @" DELETE FROM MACCOUNT WHERE ACCOUNTID = @AccountId"; public const string DELETE_ACCOUNT_BALANCE = @" DELETE FROM MACCOUNTBALANCE WHERE ACCOUNTID = @AccountId"; public const string DELETE_ACCOUNT_POST = @" DELETE FROM TACCOUNTPOST WHERE ACCOUNTID = @AccountId"; public const string CHECK_ACCOUNT_IN_VOUCHERS = @" SELECT COUNT(1) FROM TVOUCHERDETAIL WHERE VOUCHERACCOUNTID = @AccountId"; public const string CHECK_ACCOUNT_EXISTS = @" SELECT COUNT(1) FROM MACCOUNT WHERE ACCOUNTID = @AccountId"; // FKMPARTY_ACCOUNTLINKID / FKMPARTY_CONTROLACCOUNTID — MPARTY keeps a live FK to MACCOUNT // via both columns (see PartyBLL.LinkPartyAccountAsync, which sets ACCOUNTLINKID = PartyId // for every non-account Party's auto-created ledger sub-account). Deleting an Account still // referenced by either column violates the FK — check first so we can surface a clear error // instead of the raw SQL exception. public const string CHECK_ACCOUNT_LINKED_TO_PARTY = @" SELECT COUNT(1) FROM MPARTY WHERE ACCOUNTLINKID = @AccountId OR CONTROLACCOUNTID = @AccountId"; public const string DELETE_PARTY_ACCOUNT_OU = @" DELETE FROM MPARTYACCOUNTOU WHERE ACCOUNTID = @AccountId"; public const string GET_ACCOUNT_PICKLIST_FOR_COMPANY_VS_ACCOUNT_NEW = @" WITH BaseQuery AS ( SELECT A.ACCOUNTID AS Id, A.ACCOUNTCODE AS Code, A.ACCOUNTNAME AS Name, A.ACCOUNTSHORTNAME AS ShortName, P.CREDITALLOWED AS IsCreditAllowed, P.CREDITLIMIT AS CreditLimit, P.DEBITALLOWED AS IsDebitAllowed, P.DEBITLIMIT AS DebitLimit, CASE WHEN PBS.PARTYBRANCHINFOID IS NULL THEN '' ELSE 'P' END AS Parameter FROM MACCOUNT A INNER JOIN MPARTYACCOUNTOU P ON P.ACCOUNTID = A.ACCOUNTID OUTER APPLY ( SELECT MIN(PBI.PARTYBRANCHINFOID) AS PARTYBRANCHINFOID FROM MPARTYBRANCHINFO PBI INNER JOIN MPARAMETERSET PS ON PBI.PARAMETERSETID = PS.PARAMETERSETID INNER JOIN MPARTYBRANCH PBR ON PBI.PARTYBRANCHID = PBR.PARTYBRANCHID WHERE PBR.PARTYID = A.ACCOUNTID ) PBS WHERE A.ISBLOCKED = 1 AND A.ACCOUNTTYPE <> 4 AND (@ouid IS NULL OR P.OUID = @ouid) ) SELECT * FROM BaseQuery WHERE (@isvendor = 1 OR EXISTS ( SELECT 1 FROM MPARTY PR WHERE PR.PARTYID = BaseQuery.Id AND PR.ISACCOUNT = 0 AND PR.IsVendor = 0 )) AND (@iscustomer = 1 OR EXISTS ( SELECT 1 FROM MPARTY PR WHERE PR.PARTYID = BaseQuery.Id AND PR.ISACCOUNT = 0 AND PR.IsCustomer = 0 )) AND (@onlyparty = 1 OR EXISTS ( SELECT 1 FROM MPARTY PR WHERE PR.PARTYID = BaseQuery.Id AND PR.ISACCOUNT = 0 )) AND (@accountcode IS NULL OR @accountcode = '' OR Code LIKE '%' + @accountcode + '%' OR Name LIKE '%' + @accountname + '%' OR ShortName LIKE '%' + @accountname + '%') ORDER BY Name OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY;"; public const string GET_PARTY_FROM_BUTTON_FOR_ACCOUNT_PICKLIST = @" SELECT A.PARTYID AS Id, A.PARTYCODE AS Code, ADDR.PHONE AS Phone, ADDR.MOBILE AS Mobile, PBR.PARTYBRANCHCODE AS PartyBranchCode, PBR.PARTYBRANCHNAME AS PartyBranchName, PBR.PARTYBRANCHID AS PartyBranchId, A.PARTYNAME AS Name, PA.CREDITALLOWED AS IsCreditAllowed, PA.DEBITALLOWED AS IsDebitAllowed, AC.ACCOUNTTYPE AS AccountType FROM MPARTY A INNER JOIN MPARTYACCOUNTOU PA ON PA.PARTYID = A.PARTYID INNER JOIN MACCOUNT AC ON AC.ACCOUNTID = A.ACCOUNTLINKID INNER JOIN MPARTYBRANCH PBR ON PBR.PARTYID = A.PARTYID INNER JOIN MADDRESS ADDR ON ADDR.ADDRESSID = PBR.DEFAULTADDRESSID WHERE A.STATUS = 1 AND AC.ISBLOCKED = 1 AND PA.PARTYACCOUNTOUTYPE = 0 AND (@ouid IS NULL OR PA.OUID = @ouid) AND (@issales = 1 OR PA.ISSALESAPPLICABLE = 0) AND (@accountcode IS NULL OR @accountcode = '' OR A.PARTYCODE LIKE '%' + @accountcode + '%' OR A.PARTYNAME LIKE '%' + @accountname + '%' OR ADDR.PHONE LIKE '%' + @accountname + '%' OR ADDR.MOBILE LIKE '%' + @accountname + '%' OR PBR.PARTYBRANCHNAME LIKE '%' + @accountname + '%') ORDER BY A.PARTYNAME OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY;"; // Lightweight paged query for the Account Tree navigator's lazy-loaded Account leaves — // deliberately separate from GET_ACCOUNT_PICKLIST_FOR_POPUP (general-purpose account // picker) since the tree only ever needs Id/Code/Name/AccountType for one Group at a time. // Paged via QueryPagedAsync per repo convention (CLAUDE.md: "paginate all list endpoints"). public const string GET_ACCOUNT_FLAT_BY_GROUP = @" SELECT A.ACCOUNTID AS Id, A.ACCOUNTCODE AS Code, A.ACCOUNTNAME AS Name, A.ACCOUNTTYPE AS AccountType FROM MACCOUNT A WHERE A.ACCOUNTGROUPID = @AccountGroupId ORDER BY A.ACCOUNTCODE OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY"; } }