using System.Data;
using System.Data.Common;
using AccountsDAL.DTO.Voucher;
using AccountsDAL.Query.AccountPost;
using AccountsDAL.Query.Voucher;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using Newtonsoft.Json;

namespace AccountsDAL.CustomCode.Voucher
{
    public class VoucherDAL : IVoucherDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public VoucherDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        // ── Save/Update Voucher ───────────────────────────────────────────────
        // See VoucherQB.cs's Save/Update region for the full context on why this replaced the old
        // TVP/stored-procedure scaffold. Every DTO property name matches its SQL parameter/column
        // alias exactly, so the DTO is passed directly as the Dapper parameter object.

        public async Task InsertVoucherHeader(VoucherDTO voucher, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.SAVE_VOUCHER_HEADER, voucher, tx).ConfigureAwait(false);
        }

        public async Task UpdateVoucherHeader(VoucherDTO voucher, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.UPDATE_VOUCHER_HEADER, voucher, tx).ConfigureAwait(false);
        }

        public async Task InsertVoucherDetail(VoucherDetailDTO detail, DateTime headerVoucherDate, DateTime headerValidTillDate, LoginDTO loginDTO, DbTransaction tx)
        {
            var param = new DynamicParameters(detail);
            param.Add("HeaderVoucherDate", headerVoucherDate);
            param.Add("HeaderValidTillDate", headerValidTillDate);
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.SAVE_VOUCHER_DETAIL, param, tx).ConfigureAwait(false);
        }

        public async Task InsertVoucherAddon(VoucherAddonDTO addon, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.SAVE_VOUCHER_ADDON, addon, tx).ConfigureAwait(false);
        }

        public async Task InsertBillAllocation(VoucherBillAllocationDTO billAllocation, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.SAVE_BILL_ALLOCATION, billAllocation, tx).ConfigureAwait(false);
        }

        public async Task InsertCostCenter(VoucherCostCenterDTO costCenter, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.SAVE_COST_CENTER, costCenter, tx).ConfigureAwait(false);
        }

        public async Task InsertInstrument(VoucherInstrumentDTO instrument, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.SAVE_INSTRUMENT, instrument, tx).ConfigureAwait(false);
        }

        public async Task InsertVoucherTDS(VoucherTDSDTO tds, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.SAVE_VOUCHER_TDS, tds, tx).ConfigureAwait(false);
        }

        // Order respects FK depth — deepest children first, TVOUCHERDETAIL last. See VoucherQB.cs
        // comment for the fix over GB4's DELETE_VOUCHERCHILDADDON bug (never matched any row).
        public async Task DeleteChildrenExceptDetailsForVoucher(int voucherId, LoginDTO loginDTO, DbTransaction tx)
        {
            var param = new { VoucherId = voucherId };
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DELETE_VOUCHER_DETAIL_PARAMETERS_FOR_VOUCHER, param, tx).ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DELETE_VOUCHER_HEAD_ADDONS_FOR_VOUCHER, param, tx).ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DELETE_VOUCHER_LINE_ADDONS_FOR_VOUCHER, param, tx).ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DELETE_BILL_ALLOCATIONS_FOR_VOUCHER, param, tx).ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DELETE_COST_CENTERS_FOR_VOUCHER, param, tx).ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DELETE_INSTRUMENTS_FOR_VOUCHER, param, tx).ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DELETE_TDS_FOR_VOUCHER, param, tx).ConfigureAwait(false);
        }

        public async Task DeleteAllChildrenForVoucher(int voucherId, LoginDTO loginDTO, DbTransaction tx)
        {
            await DeleteChildrenExceptDetailsForVoucher(voucherId, loginDTO, tx).ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DELETE_VOUCHER_DETAILS_FOR_VOUCHER, new { VoucherId = voucherId }, tx).ConfigureAwait(false);
        }

        public async Task DetachBillAllocationAgainstReferences(int voucherId, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DETACH_BILL_ALLOCATION_AGAINST_REFERENCES_FOR_VOUCHER, new { VoucherId = voucherId }, tx).ConfigureAwait(false);
        }

        public async Task DeleteBizDimensionForVoucher(int voucherId, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DELETE_BIZDIMENSION_FOR_VOUCHER, new { VoucherId = voucherId }, tx).ConfigureAwait(false);
        }

        public async Task DeleteVoucherHeader(int voucherId, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DELETE_VOUCHER_HEADER, new { VoucherId = voucherId }, tx).ConfigureAwait(false);
        }

        public async Task SoftCancelVoucherHeader(int voucherId, int modifiedById, DateTime modifiedOn, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.SOFT_CANCEL_VOUCHER_HEADER,
                new { VoucherId = voucherId, ModifiedById = modifiedById, ModifiedOn = modifiedOn }, tx).ConfigureAwait(false);
        }

        public async Task DeleteVoucherDetailById(int voucherDetailId, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.DELETE_VOUCHER_DETAIL_BY_ID, new { VoucherDetailId = voucherDetailId }, tx).ConfigureAwait(false);
        }

        public async Task<List<int>> GetVoucherDetailIds(int voucherId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<int>(loginDTO, VoucherQB.GET_VOUCHER_DETAIL_IDS_FOR_VOUCHER, new { VoucherId = voucherId })
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<BizTransactionTypeForSaveDTO?> GetBizTransactionTypeForSave(int bizTransactionTypeId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<BizTransactionTypeForSaveDTO>(loginDTO, VoucherQB.GET_BIZ_TRANSACTION_TYPE_FOR_SAVE, new { BizTransactionTypeId = bizTransactionTypeId })
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task<PeriodDateRangeDTO?> GetPeriodDateRange(int periodId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<PeriodDateRangeDTO>(loginDTO, VoucherQB.GET_PERIOD_DATE_RANGE, new { PeriodId = periodId })
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task<AccountTypeInfoDTO?> GetAccountTypeInfo(int accountId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<AccountTypeInfoDTO>(loginDTO, VoucherQB.GET_ACCOUNT_TYPE_INFO, new { AccountId = accountId })
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task<List<VoucherEndValidationRowDTO>> ExecutePostSaveDbObjects(int voucherId, int clientId, LoginDTO loginDTO, DbTransaction tx)
        {
            var param = new { VoucherId = voucherId, ClientId = clientId, IsValidate = 1 };

            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.EXEC_POST2PENDINGBILL, param, tx).ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.FORCE_VOUCHER_POST_TRIGGER, param, tx).ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.EXEC_GROUPCURRENCYVOUCHERPOST, param, tx).ConfigureAwait(false);

            var rows = await _QueryExecutor
                .QueryAsync<VoucherEndValidationRowDTO>(loginDTO, VoucherQB.GET_VOUCHER_END_VALIDATION, param, tx)
                .ConfigureAwait(false);
            return rows.ToList();
        }

        // ── Read ──────────────────────────────────────────────────────────────

        public async Task<VoucherDTO?> GetVoucherHeader(int voucherId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<VoucherDTO>(loginDTO, VoucherQB.GET_VOUCHER, new { VoucherId = voucherId })
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task<List<VoucherDetailDTO>> GetVoucherDetails(int voucherId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<VoucherDetailDTO>(loginDTO, VoucherQB.GET_VOUCHER_DETAILS, new { VoucherId = voucherId })
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<List<VoucherAddonDTO>> GetVoucherAddons(int voucherId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<VoucherAddonDTO>(loginDTO, VoucherQB.GET_VOUCHER_ADDON, new { VoucherId = voucherId })
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<List<VoucherBillAllocationDTO>> GetVoucherBillAllocationsForVoucher(int voucherId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<VoucherBillAllocationDTO>(loginDTO, VoucherQB.GET_VOUCHER_BILL_ALLOCATION_FOR_VOUCHER, new { VoucherId = voucherId })
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<List<VoucherBillAllocationDTO>> GetVoucherBillAllocationsForDetail(int voucherDetailId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<VoucherBillAllocationDTO>(loginDTO, VoucherQB.GET_VOUCHER_BILL_ALLOCATION_FOR_DETAIL, new { VoucherDetailId = voucherDetailId })
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<List<VoucherCostCenterDTO>> GetVoucherCostCentersForVoucher(int voucherId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<VoucherCostCenterDTO>(loginDTO, VoucherQB.GET_VOUCHER_COST_CENTER_FOR_VOUCHER, new { VoucherId = voucherId })
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<List<VoucherInstrumentDTO>> GetVoucherInstrumentsForVoucher(int voucherId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<VoucherInstrumentDTO>(loginDTO, VoucherQB.GET_VOUCHER_INSTRUMENT_FOR_VOUCHER, new { VoucherId = voucherId })
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<List<VoucherTDSDTO>> GetVoucherTDSForVoucher(int voucherId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<VoucherTDSDTO>(loginDTO, VoucherQB.GET_VOUCHER_TDS_FOR_VOUCHER, new { VoucherId = voucherId })
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<List<VoucherDetailParameterDTO>> GetVoucherDetailParameters(int voucherId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<VoucherDetailParameterDTO>(loginDTO, VoucherQB.GET_VOUCHER_DETAIL_PARAMETER, new { VoucherId = voucherId })
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<int> GetVoucherBizTypeCostCenterRequiredCount(int voucherId, LoginDTO loginDTO)
        {
            return await _QueryExecutor
                .ExecuteScalarAsync<int>(loginDTO, VoucherQB.GET_VOUCHER_BIZ_TYPE_COST_CENTER_REQUIRED_COUNT, new { VoucherId = voucherId })
                .ConfigureAwait(false);
        }

        public async Task<List<int>> GetCostCategoryConditionAccountGroupIds(LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<int>(loginDTO, VoucherQB.GET_COST_CATEGORY_CONDITION_ACCOUNT_GROUP_IDS, new { })
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<List<AccountsDAL.DTO.TDSCategory.TDSCategoryDTO>> GetAllTDSCategoryAccounts(LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<AccountsDAL.DTO.TDSCategory.TDSCategoryDTO>(loginDTO, VoucherQB.GET_ALL_TDS_CATEGORY_ACCOUNTS, new { })
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<int?> GetInterVoucherDetailId(int interAccountId, int interVoucherId, double voucherDetailAmount, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<int>(loginDTO, VoucherQB.GET_INTER_VOUCHER_DETAIL_ID,
                    new { InterAccountId = interAccountId, InterVoucherId = interVoucherId, VoucherDetailAmount = voucherDetailAmount })
                .ConfigureAwait(false);
            var list = rows.ToList();
            return list.Count > 0 ? list[0] : (int?)null;
        }

        public async Task<InterSwapAccountDTO?> GetAccountForInterSwap(int accountId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<InterSwapAccountDTO>(loginDTO, VoucherQB.GET_ACCOUNT_FOR_INTER_SWAP, new { AccountId = accountId })
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task<string> GetVoucherTDS(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                var criteria = ParseVoucherTDSCriteria(criteriaDTO);
                var parameters = new
                {
                    criteria.AccountId,
                    criteria.VoucherTDSId
                };

                var result = await _QueryExecutor
                    .QueryAsync<VoucherTDSDTO>(loginDTO, VoucherQB.GET_VOUCHER_TDS, parameters)
                    .ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListVoucherTDS(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                var criteria = ParseVoucherTDSSelectListCriteria(criteriaDTO);
                var parameters = new
                {
                    criteria.AccountId,
                    criteria.Number
                };

                var result = await _QueryExecutor
                    .QueryAsync<VoucherTDSPicklistDTO>(loginDTO, VoucherQB.GET_SELECT_LIST_VOUCHERTDS, parameters)
                    .ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetBillAllocation(int voucherId, LoginDTO loginDTO)
        {
            try
            {
                var result = await _QueryExecutor
                    .QueryAsync<VoucherBillAllocationDTO>(loginDTO, VoucherQB.GET_BILL_ALLOCATION,
                        new { VoucherId = voucherId })
                    .ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetPendingBillAllocation(BillAllocationFilterDTO filterDTO, LoginDTO loginDTO)
        {
            try
            {
                var param = new
                {
                    AccountId = filterDTO?.AccountId ?? 0,
                    SubAccountId = filterDTO?.SubAccountId ?? 0,
                    OuId = filterDTO?.OuId ?? 0,
                    VoucherDate = (filterDTO != null && filterDTO.VoucherDate.Year != 1800)
                        ? filterDTO.VoucherDate.Date
                        : (DateTime?)null,
                    VoucherBillAllocationId = filterDTO?.VoucherBillAllocationId ?? 0,
                    DetailType = filterDTO?.DetailType ?? 99
                };

                var result = await _QueryExecutor
                    .QueryAsync<VoucherPendingBillAllocationDTO>(loginDTO, VoucherQB.GET_PENDING_BILL_ALLOCATION, param)
                    .ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> PaymentEntryValidationCount(string referenceNumber, LoginDTO loginDTO)
        {
            try
            {
                return await _QueryExecutor
                    .ExecuteScalarAsync<int>(loginDTO, VoucherQB.PAYMENT_ENTRY_VALIDATION_COUNT,
                        new { ReferenceNumber = referenceNumber })
                    .ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListBillAllocation(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                var criteria = ParseBillAllocationCriteria(criteriaDTO);
                var parameters = new
                {
                    criteria.AccountId,
                    criteria.SubAccountId,
                    criteria.OuId,
                    criteria.VoucherDate,
                    criteria.DetailType,
                    criteria.VoucherBillAllocationId,
                    criteria.Number,
                    TenantId = loginDTO.ClientId
                };

                var result = await _QueryExecutor
                    .QueryAsync<VoucherBillAllocationPicklistDTO>(loginDTO, VoucherQB.GET_SELECTLIST_BILL_ALLOCATION, parameters)
                    .ConfigureAwait(false);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static BillAllocationSelectListCriteria ParseBillAllocationCriteria(CriteriaDTO criteriaDTO)
        {
            var criteria = new BillAllocationSelectListCriteria();

            foreach (var section in criteriaDTO?.SectionCriteriaList ?? [])
            {
                foreach (var attribute in section.AttributesCriteriaList ?? [])
                {
                    var fieldName = attribute.FieldName?.Trim().ToLowerInvariant();
                    var value = Convert.ToString(attribute.FieldValue)?.Trim();

                    if (string.IsNullOrWhiteSpace(fieldName) || string.IsNullOrWhiteSpace(value))
                    {
                        continue;
                    }

                    switch (fieldName)
                    {
                        case "accountid":
                            criteria.AccountId = ToInt(value);
                            break;
                        case "subaccountid":
                            criteria.SubAccountId = ToInt(value);
                            break;
                        case "ouid":
                            criteria.OuId = ToInt(value);
                            break;
                        case "voucherdate":
                            criteria.VoucherDate = ToDate(value);
                            break;
                        case "detailtype":
                            criteria.DetailType = ToByte(value, 99);
                            break;
                        case "voucherbillallocationid":
                            criteria.VoucherBillAllocationId = ToInt(value);
                            break;
                        case "number":
                            criteria.Number = value;
                            break;
                    }
                }
            }

            return criteria;
        }

        private static int ToInt(string value)
        {
            return int.TryParse(value, out var result) ? result : 0;
        }

        private static byte ToByte(string value, byte defaultValue)
        {
            return byte.TryParse(value, out var result) ? result : defaultValue;
        }

        private static DateTime? ToDate(string value)
        {
            if (!DateTime.TryParse(value, out var result) || result.Year == 1800)
            {
                return null;
            }

            return result.Date;
        }

        private sealed class BillAllocationSelectListCriteria
        {
            public int AccountId { get; set; }
            public int SubAccountId { get; set; }
            public int OuId { get; set; }
            public DateTime? VoucherDate { get; set; }
            public byte DetailType { get; set; } = 99;
            public int VoucherBillAllocationId { get; set; }
            public string Number { get; set; } = string.Empty;
        }

        // Matches GB4 GetVoucherTDS's ApplyGetVoucherTDS field map exactly — AccountId/VoucherTDSId
        // are the only two dynamic filters GB4 supports for this method. 0 = "no filter" (GB5's
        // GET_VOUCHER_TDS uses "@Field = 0 OR ..." to make the filter optional).
        private static VoucherTDSCriteria ParseVoucherTDSCriteria(CriteriaDTO criteriaDTO)
        {
            var criteria = new VoucherTDSCriteria();

            foreach (var section in criteriaDTO?.SectionCriteriaList ?? [])
            {
                foreach (var attribute in section.AttributesCriteriaList ?? [])
                {
                    var fieldName = attribute.FieldName?.Trim().ToLowerInvariant();
                    var value = Convert.ToString(attribute.FieldValue)?.Trim();

                    if (string.IsNullOrWhiteSpace(fieldName) || string.IsNullOrWhiteSpace(value))
                    {
                        continue;
                    }

                    switch (fieldName)
                    {
                        case "accountid":
                            criteria.AccountId = ToInt(value);
                            break;
                        case "vouchertdsid":
                            criteria.VoucherTDSId = ToInt(value);
                            break;
                    }
                }
            }

            return criteria;
        }

        private sealed class VoucherTDSCriteria
        {
            public int AccountId { get; set; }
            public int VoucherTDSId { get; set; }
        }

        // Matches GB4 GetSelectListVoucherTDS's ApplyVoucherTDS field map exactly — AccountId/Number
        // (ReferenceNumber) are the only two dynamic filters GB4 supports for this method.
        private static VoucherTDSSelectListCriteria ParseVoucherTDSSelectListCriteria(CriteriaDTO criteriaDTO)
        {
            var criteria = new VoucherTDSSelectListCriteria();

            foreach (var section in criteriaDTO?.SectionCriteriaList ?? [])
            {
                foreach (var attribute in section.AttributesCriteriaList ?? [])
                {
                    var fieldName = attribute.FieldName?.Trim().ToLowerInvariant();
                    var value = Convert.ToString(attribute.FieldValue)?.Trim();

                    if (string.IsNullOrWhiteSpace(fieldName) || string.IsNullOrWhiteSpace(value))
                    {
                        continue;
                    }

                    switch (fieldName)
                    {
                        case "accountid":
                            criteria.AccountId = ToInt(value);
                            break;
                        case "number":
                            criteria.Number = value;
                            break;
                    }
                }
            }

            return criteria;
        }

        private sealed class VoucherTDSSelectListCriteria
        {
            public int AccountId { get; set; }
            public string Number { get; set; } = string.Empty;
        }

        public async Task<string> GetSelectListVoucher(int FirstNumber, int MaxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, bool IsCount = false)
        {
            try
            {
                // Was previously ignored entirely — a search for a VoucherNumber outside the
                // current page never reached the DB, so it could never be found. Parsed the same
                // way as ParseVoucherTDSSelectListCriteria's "number" field for consistency.
                string number = ParseVoucherSelectListNumber(criteriaDTO);

                // BIZTransactionTypeId was accepted in the request payload but never read —
                // callers filtering by transaction type got back the unfiltered page instead.
                int bizTransactionTypeId = ParseVoucherSelectListBizTransactionTypeId(criteriaDTO);

                // OrganizationUnit.Id/Period.Id were likewise sent by the picklist (matching legacy
                // GB4's own Voucher.svc/SelectList criteria) but never read here — callers scoped to
                // the current OU/Period got back vouchers from every OU and every period instead.
                int ouId = ParseVoucherSelectListIntField(criteriaDTO, "OrganizationUnit.Id", "OuId");
                int periodId = ParseVoucherSelectListIntField(criteriaDTO, "Period.Id", "PeriodId");

                if (!IsCount)
                {
                    var parameter = new
                    {
                        firstnumber = FirstNumber,
                        maxresult = MaxResult,
                        Number = number,
                        BizTransactionTypeId = bizTransactionTypeId,
                        OuId = ouId,
                        PeriodId = periodId
                    };
                    var result    = await _QueryExecutor.QueryAsync<VoucherPicklistDTO>(loginDTO, VoucherQB.GET_SELECTLIST_VOUCHER, parameter);
                    return JsonConvert.SerializeObject(result);
                }
                return "0";
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListVoucherInstrument(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                string number = "";
                string drawnOnBank = "";
                int instrumentType = -1;
                int bankAccountId = -1;

                if (criteriaDTO?.SectionCriteriaList?.Count > 0)
                {
                    var attributes = criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList;
                    if (attributes != null)
                    {
                        foreach (var item in attributes)
                        {
                            switch (item.FieldName?.ToLower())
                            {
                                case "number": number = item.FieldValue?.ToString() ?? ""; break;
                                case "drawnonbank": drawnOnBank = item.FieldValue?.ToString() ?? ""; break;
                                case "instrumenttype": instrumentType = Convert.ToInt32(item.FieldValue); break;
                                case "bankaccountid": bankAccountId = Convert.ToInt32(item.FieldValue); break;
                            }
                        }
                    }
                }

                var parameter = new { Number = number, DrawnOnBank = drawnOnBank, InstrumentType = instrumentType, BankAccountId = bankAccountId };
                var result = await _QueryExecutor.QueryAsync<VoucherInstrumentNumberPicklistDTO>(loginDTO, VoucherQB.GET_SELECTLIST_VOUCHER_INSTRUMENT, parameter);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Matches ParseVoucherTDSSelectListCriteria's field-name convention ("number") — accepts
        // "vouchernumber" too since callers of this specific endpoint aren't confirmed to use
        // exactly the same field name as the TDS picklist.
        private static string ParseVoucherSelectListNumber(CriteriaDTO criteriaDTO)
        {
            foreach (var section in criteriaDTO?.SectionCriteriaList ?? [])
            {
                foreach (var attribute in section.AttributesCriteriaList ?? [])
                {
                    var fieldName = attribute.FieldName?.Trim().ToLowerInvariant();
                    if (fieldName != "number" && fieldName != "vouchernumber")
                        continue;

                    var value = Convert.ToString(attribute.FieldValue)?.Trim();
                    if (!string.IsNullOrWhiteSpace(value))
                        return value;
                }
            }
            return string.Empty;
        }

        // -1 means "no filter" — matches the sentinel already used for firstnumber/maxresult
        // in VoucherQB.GET_SELECTLIST_VOUCHER.
        // Accepts both the flat "BIZTransactionTypeId" name and the dotted "BizTransactionType.Id"
        // form — the picklist framework's IsModuleBasedCriteria-style entries (see
        // picklistcriteria.json's "OrganizationUnit.Id"/"Period.Id") actually send the latter, which
        // this comparison previously missed entirely: the filter never matched, silently fell back to
        // -1 ("no filter"), and every voucher type came back instead of just the one selected.
        private static int ParseVoucherSelectListBizTransactionTypeId(CriteriaDTO criteriaDTO)
        {
            foreach (var section in criteriaDTO?.SectionCriteriaList ?? [])
            {
                foreach (var attribute in section.AttributesCriteriaList ?? [])
                {
                    var fieldName = attribute.FieldName?.Trim();
                    if (!string.Equals(fieldName, "BIZTransactionTypeId", StringComparison.OrdinalIgnoreCase)
                        && !string.Equals(fieldName, "BizTransactionType.Id", StringComparison.OrdinalIgnoreCase))
                        continue;

                    if (int.TryParse(Convert.ToString(attribute.FieldValue), out var value))
                        return value;
                }
            }
            return -1;
        }

        // -1 means "no filter". Matches on any of the given field names (case-insensitive) — used
        // for OrganizationUnit.Id/Period.Id, which the picklist sends dotted (matching legacy GB4's
        // own Voucher.svc/SelectList criteria) but a flat alias is accepted too for safety.
        private static int ParseVoucherSelectListIntField(CriteriaDTO criteriaDTO, params string[] fieldNames)
        {
            foreach (var section in criteriaDTO?.SectionCriteriaList ?? [])
            {
                foreach (var attribute in section.AttributesCriteriaList ?? [])
                {
                    var fieldName = attribute.FieldName?.Trim();
                    if (fieldName == null || !fieldNames.Any(f => string.Equals(fieldName, f, StringComparison.OrdinalIgnoreCase)))
                        continue;

                    if (int.TryParse(Convert.ToString(attribute.FieldValue), out var value))
                        return value;
                }
            }
            return -1;
        }

        // ── Write ─────────────────────────────────────────────────────────────

        public async Task<string> UpdateBillAllocation(VoucherBillAllocationDTO dto, LoginDTO loginDTO)
        {
            try
            {
                var param = new
                {
                    BillAllocationLineId = dto.VoucherBillAllocationId,
                    AllocatedAmount      = dto.VoucherBillAllocationAllocatedAmount,
                    Status               = dto.VoucherBillAllocationStatus,
                    ModifiedById         = loginDTO.UserId,
                    ModifiedOn           = DateTime.UtcNow
                };
                await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.UPDATE_BILL_ALLOCATION, param)
                                    .ConfigureAwait(false);
                return SuccessResponse.UpdateSuccess;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Raw fetch only — the actual auto-allocation algorithm (distributing a payment amount
        // across these pending bills, oldest-first) is business logic and lives in VoucherBLL.
        // VoucherDate uses the same "1800 sentinel year = not filtered" convention as
        // ParseBillAllocationCriteria/ToDate above, so BillAllocationFilterDTO's default value
        // (never touched by the caller) correctly comes through as "no date filter", not a literal
        // 1800-01-01 match.
        public async Task<List<VoucherBillAllocationDTO>> AutoPendingBillAllocation(BillAllocationFilterDTO filterDTO, LoginDTO loginDTO)
        {
            try
            {
                var param = new
                {
                    AccountId = filterDTO?.AccountId ?? 0,
                    SubAccountId = filterDTO?.SubAccountId ?? 0,
                    OuId = filterDTO?.OuId ?? 0,
                    VoucherDate = (filterDTO != null && filterDTO.VoucherDate.Year != 1800)
                        ? filterDTO.VoucherDate.Date
                        : (DateTime?)null,
                    VoucherBillAllocationId = filterDTO?.VoucherBillAllocationId ?? 0,
                    DetailType = filterDTO?.DetailType ?? 99,
                    TenantId = loginDTO.ClientId
                };

                var result = await _QueryExecutor
                    .QueryAsync<VoucherBillAllocationDTO>(loginDTO, VoucherQB.GET_AUTO_PENDING_BILL_ALLOCATION, param)
                    .ConfigureAwait(false);
                return result?.ToList() ?? [];
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Matches legacy GB4 VoucherBLL/DAL.BRSLoad's exact signature and dual-purpose design:
        // count=false returns the page of BRSLoadDTOs (JSON), count=true returns just the total
        // matching-row count (as a string) via GET_BRS_LOAD_COUNT — same filter WHERE clause as the
        // paged query, so both agree on what "matches". BRSLoadFilterDTO (the Bank/Date/Status-only
        // shape the old GB5 signature took) is legacy-GB4 dead code — confirmed via research that no
        // GB4 method actually consumes it — so this is a full replacement, not an extension.
        public async Task<string> LoadBRS(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, bool count = false)
        {
            try
            {
                var criteria = ParseBRSLoadCriteria(criteriaDTO);
                var param = new
                {
                    FirstNumber = firstNumber,
                    MaxResult = maxResult,
                    criteria.BankIds,
                    criteria.AccountIds,
                    criteria.DepositIds,
                    criteria.InstrumentLineIds,
                    criteria.InstrumentTypes,
                    criteria.ChequeType,
                    criteria.FromDate,
                    criteria.ToDate,
                    criteria.InstrumentStatus,
                    criteria.VoucherNarration,
                    criteria.OuId,
                    criteria.InstrumentNumberSortAscDesc,
                    criteria.InstrumentDateSortAscDesc,
                    criteria.InstrumentAmountSortAscDesc
                };

                if (count)
                {
                    int total = await _QueryExecutor
                        .ExecuteScalarAsync<int>(loginDTO, VoucherQB.GET_BRS_LOAD_COUNT, param)
                        .ConfigureAwait(false);
                    return total.ToString();
                }

                var result = await _QueryExecutor
                    .QueryAsync<BRSLoadDTO>(loginDTO, VoucherQB.GET_BRS_LOAD, param)
                    .ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static BRSLoadCriteria ParseBRSLoadCriteria(CriteriaDTO criteriaDTO)
        {
            var criteria = new BRSLoadCriteria();

            foreach (var section in criteriaDTO?.SectionCriteriaList ?? [])
            {
                foreach (var attribute in section.AttributesCriteriaList ?? [])
                {
                    var fieldName = attribute.FieldName?.Trim().ToLowerInvariant();
                    var value = Convert.ToString(attribute.FieldValue)?.Trim();

                    if (string.IsNullOrWhiteSpace(fieldName) || string.IsNullOrWhiteSpace(value))
                    {
                        continue;
                    }

                    switch (fieldName)
                    {
                        case "bankid":
                            criteria.BankIds = value;
                            break;
                        case "accountid":
                            criteria.AccountIds = value;
                            break;
                        case "depositid":
                            criteria.DepositIds = value;
                            break;
                        case "instrumentid":
                            criteria.InstrumentLineIds = value;
                            break;
                        case "instrumenttype":
                            criteria.InstrumentTypes = value;
                            break;
                        case "chequetype":
                            criteria.ChequeType = ToInt(value, -1);
                            break;
                        case "fromdate":
                            criteria.FromDate = ToDate(value);
                            break;
                        case "todate":
                            criteria.ToDate = ToDate(value);
                            break;
                        case "instrumentstatus":
                            criteria.InstrumentStatus = ToInt(value, -1);
                            break;
                        case "vouchernarration":
                            criteria.VoucherNarration = value;
                            break;
                        case "ouid":
                            criteria.OuId = ToInt(value, 0);
                            break;
                        case "instrumentnumbersortascdesc":
                            criteria.InstrumentNumberSortAscDesc = ToInt(value, -1);
                            break;
                        case "instrumentdatesortascdesc":
                            criteria.InstrumentDateSortAscDesc = ToInt(value, -1);
                            break;
                        case "instrumentamountsortascdesc":
                            criteria.InstrumentAmountSortAscDesc = ToInt(value, -1);
                            break;
                    }
                }
            }

            return criteria;
        }

        private static int ToInt(string value, int defaultValue)
        {
            return int.TryParse(value, out var result) ? result : defaultValue;
        }

        private sealed class BRSLoadCriteria
        {
            public string BankIds { get; set; } = string.Empty;
            public string AccountIds { get; set; } = string.Empty;
            public string DepositIds { get; set; } = string.Empty;
            public string InstrumentLineIds { get; set; } = string.Empty;
            public string InstrumentTypes { get; set; } = string.Empty;
            public int ChequeType { get; set; } = -1;
            public DateTime? FromDate { get; set; }
            public DateTime? ToDate { get; set; }
            public int InstrumentStatus { get; set; } = -1;
            public string VoucherNarration { get; set; } = string.Empty;
            public int OuId { get; set; }
            public int InstrumentNumberSortAscDesc { get; set; } = -1;
            public int InstrumentDateSortAscDesc { get; set; } = -1;
            public int InstrumentAmountSortAscDesc { get; set; } = -1;
        }

        // Matches legacy GB4 VoucherBLL.UpdateBRS's real signature (List<BRSLoadDTO>, not
        // List<int>+single shared status) — each item carries its own InstrumentId/InstrumentStatus/
        // Date, so a single call can mix Pending/Deposited/Realised/Bounce/Hold/Cancelled across
        // different instruments in one batch, same as GB4. This ports only the status/date column
        // flip ("Loop A") — bounce-voucher generation ("Loop B") is deferred per explicit scope
        // decision, so BRSLoadDTO's cheque-bounce-specific fields are accepted but not yet acted on.
        public async Task<string> UpdateBRS(List<BRSLoadDTO> brsLoadDTOs, LoginDTO loginDTO)
        {
            try
            {
                if (brsLoadDTOs == null || brsLoadDTOs.Count == 0)
                {
                    throw new ArgumentException("At least one instrument entry is required.");
                }

                var notFound = new List<int>();

                foreach (var item in brsLoadDTOs)
                {
                    string sql = item.InstrumentStatus switch
                    {
                        0 => VoucherQB.UPDATE_INSTRUMENT_STATUS_PENDING,
                        1 => VoucherQB.UPDATE_INSTRUMENT_STATUS_DEPOSITED,
                        2 => VoucherQB.UPDATE_INSTRUMENT_STATUS_REALISED,
                        3 => VoucherQB.UPDATE_INSTRUMENT_STATUS_BOUNCE,
                        4 => VoucherQB.UPDATE_INSTRUMENT_STATUS_HOLD,
                        5 => VoucherQB.UPDATE_INSTRUMENT_STATUS_CANCELLED,
                        _ => throw new ArgumentException($"Unsupported InstrumentStatus '{item.InstrumentStatus}' for InstrumentId {item.InstrumentId}.")
                    };

                    var param = new
                    {
                        InstrumentLineId = item.InstrumentId,
                        Date = item.Date
                    };

                    int rows = await _QueryExecutor.ExecuteAsync(loginDTO, sql, param).ConfigureAwait(false);
                    if (rows == 0)
                    {
                        notFound.Add(item.InstrumentId);
                    }
                }

                if (notFound.Count > 0)
                {
                    throw new InvalidOperationException(
                        $"No matching TINSTRUMENT row (INSTRUMENTLINEID) found for InstrumentId(s): {string.Join(", ", notFound)}.");
                }

                return SuccessResponse.UpdateSuccess;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> ReAccountPost(int voucherId, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.RE_ACCOUNT_POST,
                    new { VoucherId = voucherId })
                    .ConfigureAwait(false);
                return SuccessResponse.SaveSuccess;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Opening Balance Save/Update ─────────────────────────────────────────

        public async Task<int?> GetFirstPeriodId(LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<int?>(loginDTO, VoucherQB.GET_FIRST_PERIOD_ID, new { TenantId = loginDTO.ClientId })
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task<VoucherOnOUDTO?> GetVoucherOnOU(int bizTransactionTypeId, int ouId, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<VoucherOnOUDTO>(loginDTO, VoucherQB.GET_VOUCHER_ON_OU,
                    new { BizTransactionTypeId = bizTransactionTypeId, OUId = ouId })
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task SaveOpeningVoucherDetail(VoucherDetailDTO detail, int newVoucherDetailId, int voucherId, LoginDTO loginDTO, DbTransaction tx)
        {
            var param = new
            {
                VoucherDetailId = newVoucherDetailId,
                VoucherId = voucherId,
                Slno = detail.VoucherDetailSlno,
                AccountId = detail.VoucherAccountId,
                SubAccountId = detail.SubAccountId,
                AmountFC = detail.VoucherDetailAmountFC,
                CurrencyConversion = detail.VoucherDetailCurrencyConversion,
                Amount = detail.VoucherDetailAmount,
                InterOUId = detail.InterOUId,
                InterAccountId = detail.InterAccountId,
                InterSubAccountId = detail.InterSubAccountId,
                VoucherLineNarration = detail.VoucherDetailVoucherLineNarration,
                DetailType = detail.VoucherDetailDetailType,
                CurrencyId = detail.CurrencyId
            };

            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.INSERT_VOUCHER_DETAIL_OPENING, param, tx).ConfigureAwait(false);
        }

        public async Task UpdateOpeningVoucherDetail(VoucherDetailDTO detail, LoginDTO loginDTO, DbTransaction tx)
        {
            var param = new
            {
                VoucherDetailId = detail.VoucherDetailId,
                Slno = detail.VoucherDetailSlno,
                AccountId = detail.VoucherAccountId,
                SubAccountId = detail.SubAccountId,
                AmountFC = detail.VoucherDetailAmountFC,
                CurrencyConversion = detail.VoucherDetailCurrencyConversion,
                Amount = detail.VoucherDetailAmount,
                InterOUId = detail.InterOUId,
                InterAccountId = detail.InterAccountId,
                InterSubAccountId = detail.InterSubAccountId,
                VoucherLineNarration = detail.VoucherDetailVoucherLineNarration,
                DetailType = detail.VoucherDetailDetailType,
                CurrencyId = detail.CurrencyId
            };

            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.UPDATE_VOUCHER_DETAIL_OPENING, param, tx).ConfigureAwait(false);
        }

        public async Task SaveOpeningBillAllocation(VoucherBillAllocationDTO allocation, int newBillAllocationId, int voucherDetailId, LoginDTO loginDTO, DbTransaction tx)
        {
            var param = new
            {
                BillAllocationLineId = newBillAllocationId,
                VoucherDetailId = voucherDetailId,
                Slno = allocation.VoucherBillAllocationSlno,
                AllocationType = allocation.VoucherBillAllocationType,
                ReferenceNumber = allocation.VoucherBillAllocationReferenceNumber,
                ReferenceDate = allocation.VoucherBillAllocationReferenceDate,
                DueDate = allocation.VoucherBillAllocationDueDate,
                FullAmount = allocation.VoucherBillAllocationFullAmount,
                AllocatedAmount = allocation.VoucherBillAllocationAllocatedAmount,
                Status = allocation.VoucherBillAllocationStatus
            };

            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.INSERT_BILL_ALLOCATION_OPENING, param, tx).ConfigureAwait(false);
        }

        public async Task UpdateOpeningBillAllocation(VoucherBillAllocationDTO allocation, LoginDTO loginDTO, DbTransaction tx)
        {
            var param = new
            {
                BillAllocationLineId = allocation.VoucherBillAllocationId,
                Slno = allocation.VoucherBillAllocationSlno,
                AllocationType = allocation.VoucherBillAllocationType,
                ReferenceNumber = allocation.VoucherBillAllocationReferenceNumber,
                ReferenceDate = allocation.VoucherBillAllocationReferenceDate,
                DueDate = allocation.VoucherBillAllocationDueDate,
                FullAmount = allocation.VoucherBillAllocationFullAmount,
                AllocatedAmount = allocation.VoucherBillAllocationAllocatedAmount,
                Status = allocation.VoucherBillAllocationStatus
            };

            await _QueryExecutor.ExecuteAsync(loginDTO, VoucherQB.UPDATE_BILL_ALLOCATION_OPENING, param, tx).ConfigureAwait(false);
        }

        // ── Re-Account-Post (period/account/OU-scoped ledger rebuild) ───────────

        public async Task<List<int>> GetActiveOUIds(LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<int>(loginDTO, AccountPostQB.GET_ACTIVE_OU_IDS)
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task ReAccountPostingFast(int periodId, int accountId, int ouId, LoginDTO loginDTO)
        {
            var param = new { OUId = ouId, PeriodId = periodId, AccountId = accountId };

            if (periodId != -1)
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, AccountPostQB.DELETE_ACCOUNT_POST_FOR_PERIOD, param).ConfigureAwait(false);
                await _QueryExecutor.ExecuteAsync(loginDTO, AccountPostQB.INSERT_ACCOUNT_POST_FOR_PERIOD, param).ConfigureAwait(false);
            }
            else
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, AccountPostQB.DELETE_ACCOUNT_POST_FOR_ALL_PERIODS, param).ConfigureAwait(false);
                await _QueryExecutor.ExecuteAsync(loginDTO, AccountPostQB.INSERT_ACCOUNT_POST_FOR_ALL_PERIODS, param).ConfigureAwait(false);
            }

            await _QueryExecutor.ExecuteAsync(loginDTO, AccountPostQB.UPDATE_ACCOUNT_POST_DEBIT_CREDIT, param).ConfigureAwait(false);

            if (periodId == -1)
                await _QueryExecutor.ExecuteAsync(loginDTO, AccountPostQB.UPDATE_ACCOUNT_POST_OPENING_ALL_PERIODS, param).ConfigureAwait(false);
            else
                await _QueryExecutor.ExecuteAsync(loginDTO, AccountPostQB.UPDATE_ACCOUNT_POST_OPENING_SINGLE_PERIOD, param).ConfigureAwait(false);
        }

        public async Task<string> GetVoucherDetailForOpeningBalance(int bizTransactionTypeId, int ouId, int voucherAccountId, LoginDTO loginDTO)
        {
            var list = await _QueryExecutor
                .QueryAsync<VoucherDetailDTO>(loginDTO, VoucherQB.GET_VOUCHER_DETAIL_FOR_OPENING_BALANCE,
                    new { BizTransactionTypeId = bizTransactionTypeId, OUId = ouId, VoucherAccountId = voucherAccountId })
                .ConfigureAwait(false);
            return JsonConvert.SerializeObject(list);
        }

        public async Task<string?> FindDuplicateReferenceNumberVoucher(int ouId, int periodId, int bizTransactionTypeId, int accountId, string referenceNumber, LoginDTO loginDTO)
        {
            var rows = await _QueryExecutor
                .QueryAsync<string>(loginDTO, VoucherQB.CHECK_REFERENCE_NUMBER_DUPLICATION,
                    new { OUId = ouId, PeriodId = periodId, BizTransactionTypeId = bizTransactionTypeId, AccountId = accountId, ReferenceNumber = referenceNumber })
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task<string> GetUsedVoucherInBillAllocation(int voucherId, LoginDTO loginDTO)
        {
            var list = await _QueryExecutor
                .QueryAsync<VoucherBillAllocationPicklistDTO>(loginDTO, VoucherQB.GET_USED_VOUCHER_IN_BILL_ALLOCATION,
                    new { VoucherId = voucherId })
                .ConfigureAwait(false);
            return JsonConvert.SerializeObject(list);
        }

        public async Task<string> GetVoucherSummaryInfo(int voucherId, LoginDTO loginDTO)
        {
            var list = await _QueryExecutor
                .QueryAsync<VoucherSummaryInfoDTO>(loginDTO, VoucherQB.GET_VOUCHER_SUMMARY_INFO, new { VoucherId = voucherId })
                .ConfigureAwait(false);
            return JsonConvert.SerializeObject(list);
        }
    }
}