using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Diagnostics.Metrics;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using Microsoft.Identity.Client;
using Org.BouncyCastle.Crypto;
namespace AccountsDAL.Query.Instrument
{
public static class InstrumentQB
{
public const string GET_INSTRUMNET = @"
WITH InstrumentIds AS
(
SELECT
CASE
WHEN CleanValue <> ''
AND (
CleanValue NOT LIKE '%[^0-9]%'
OR (
LEFT(CleanValue, 1) = '-'
AND LEN(CleanValue) > 1
AND SUBSTRING(CleanValue, 2, LEN(CleanValue)) NOT LIKE '%[^0-9]%'
)
)
THEN CAST(CleanValue AS INT)
END AS InstrumentId
FROM
(
SELECT LTRIM(RTRIM(Split.a.value('.', 'NVARCHAR(50)'))) AS CleanValue
FROM
(
SELECT CAST('' + REPLACE(ISNULL(@INSTRUMENTID, ''), ',', '') + '' AS XML) AS XmlVal
) A
CROSS APPLY XmlVal.nodes('/X') Split(a)
) X
)
SELECT
I.INSTRUMENTID AS InstrumentId,
I.INSTRUMENTNAME AS InstrumentName,
I.INSTRUMENTTYPE AS InstrumentType,
I.ACCOUNTPOSTTYPE AS InstrumentAccountPostType,
I.POSTDATEDTYPE AS InstrumentPostDatedType,
I.VALIDITYPERIODINDAYS AS InstrumentValidityPeriodInDays,
I.INSTRUMENTSTATUS AS InstrumentStatusOfInstrument,
I.ISAPPLICABLEFORBANK AS InstrumentIsApplicableForBank,
I.ISAPPLICABLEFORCOUNTERTRANSACTION AS InstrumentIsApplicableforCounterTransaction,
I.ACCOUNTID AS AccountId,
A.ACCOUNTCODE AS AccountCode,
A.ACCOUNTNAME AS AccountName,
IPP.PROCESSNAME AS InstrumentAccountPostTypeName,
IPD.PROCESSNAME AS InstrumentPostDatedTypeName,
BK.BANKBRANCHNAME AS ISApplicableforBankName,
CNT.COUNTERNAME AS ISApplicableforCounterTransactionName,
I.CREATEDBYID AS InstrumentCreatedById,
I.CREATEDON AS InstrumentCreatedOn,
I.MODIFIEDBYID AS InstrumentModifiedById,
I.MODIFIEDON AS InstrumentModifiedOn,
U1.USERNAME AS InstrumentCreatedByName,
U2.USERNAME AS InstrumentModifiedByName,
I.OUIDS AS InstrumentOuIds,
ISNULL(OU_DATA.OuNames, 'NONE') AS InstrumentOuNames,
I.OUGROUPIDS AS InstrumentOuGroupIds,
ISNULL(OUGROUP_DATA.OuGroupNames, 'NONE') AS InstrumentOuGroupNames,
I.SORTORDER AS InstrumentSortOrder,
I.STATUS AS InstrumentStatus,
I.VERSION AS InstrumentVersion
FROM MINSTRUMENT I
LEFT JOIN MACCOUNT A
ON I.ACCOUNTID = A.ACCOUNTID
LEFT JOIN MPROCESS IPP
ON I.ACCOUNTPOSTTYPE = IPP.PROCESSID
LEFT JOIN MPROCESS IPD
ON I.POSTDATEDTYPE = IPD.PROCESSID
LEFT JOIN MBANKBRANCH BK
ON I.ISAPPLICABLEFORBANK = BK.BANKID
LEFT JOIN MCOUNTER CNT
ON I.ISAPPLICABLEFORCOUNTERTRANSACTION = CNT.COUNTERID
LEFT JOIN MUSER U1
ON I.CREATEDBYID = U1.USERID
LEFT JOIN MUSER U2
ON I.MODIFIEDBYID = U2.USERID
OUTER APPLY
(
SELECT
STUFF((
SELECT ',' + MOU.ORGANIZATIONUNITNAME
FROM
(
SELECT
CASE
WHEN CleanValue NOT IN ('', '-1')
THEN CAST(CleanValue AS INT)
END AS OUID
FROM
(
SELECT LTRIM(RTRIM(SplitOu.a.value('.', 'NVARCHAR(50)'))) AS CleanValue
FROM
(
SELECT CAST('' + REPLACE(ISNULL(I.OUIDS, ''), ',', '') + '' AS XML) AS XmlVal
) OuXml
CROSS APPLY XmlVal.nodes('/X') SplitOu(a)
) CleanOu
) IDS
INNER JOIN MORGANIZATIONUNIT MOU
ON MOU.OUID = IDS.OUID
WHERE IDS.OUID IS NOT NULL
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS OuNames
) OU_DATA
OUTER APPLY
(
SELECT
STUFF((
SELECT ',' + OG.ORGANIZATIONGROUPNAME
FROM
(
SELECT
CASE
WHEN CleanValue NOT IN ('', '-1')
THEN CAST(CleanValue AS INT)
END AS ORGANIZATIONGROUPID
FROM
(
SELECT LTRIM(RTRIM(SplitGrp.a.value('.', 'NVARCHAR(50)'))) AS CleanValue
FROM
(
SELECT CAST('' + REPLACE(ISNULL(I.OUGROUPIDS, ''), ',', '') + '' AS XML) AS XmlVal
) GrpXml
CROSS APPLY XmlVal.nodes('/X') SplitGrp(a)
) CleanGrp
) IDS
INNER JOIN MORGANIZATIONGROUP OG
ON OG.ORGANIZATIONGROUPID = IDS.ORGANIZATIONGROUPID
WHERE IDS.ORGANIZATIONGROUPID IS NOT NULL
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS OuGroupNames
) OUGROUP_DATA
WHERE I.INSTRUMENTID IN
(
SELECT InstrumentId
FROM InstrumentIds
WHERE InstrumentId IS NOT NULL
);
";
// Full-DTO instrument list (InstrumentDTO, not InstrumentPicklistDTO) — mirrors GET_INSTRUMNET's
// column set/joins, but filtered by CriteriaDTO (search text against INSTRUMENTNAME + optional
// Status Equal/NotEqual) instead of a comma-separated Id list, with no FirstNumber/MaxResult
// paging and no dynamic OrderBy — same shape as AccountQB.GET_ACCOUNT_LIST.
public const string GET_INSTRUMENT_LIST = @"
SELECT
I.INSTRUMENTID AS InstrumentId,
I.INSTRUMENTNAME AS InstrumentName,
I.INSTRUMENTTYPE AS InstrumentType,
I.ACCOUNTPOSTTYPE AS InstrumentAccountPostType,
I.POSTDATEDTYPE AS InstrumentPostDatedType,
I.VALIDITYPERIODINDAYS AS InstrumentValidityPeriodInDays,
I.INSTRUMENTSTATUS AS InstrumentStatusOfInstrument,
I.ISAPPLICABLEFORBANK AS InstrumentIsApplicableForBank,
I.ISAPPLICABLEFORCOUNTERTRANSACTION AS InstrumentIsApplicableforCounterTransaction,
I.ACCOUNTID AS AccountId,
A.ACCOUNTCODE AS AccountCode,
A.ACCOUNTNAME AS AccountName,
IPP.PROCESSNAME AS InstrumentAccountPostTypeName,
IPD.PROCESSNAME AS InstrumentPostDatedTypeName,
BK.BANKBRANCHNAME AS ISApplicableforBankName,
CNT.COUNTERNAME AS ISApplicableforCounterTransactionName,
I.CREATEDBYID AS InstrumentCreatedById,
I.CREATEDON AS InstrumentCreatedOn,
I.MODIFIEDBYID AS InstrumentModifiedById,
I.MODIFIEDON AS InstrumentModifiedOn,
U1.USERNAME AS InstrumentCreatedByName,
U2.USERNAME AS InstrumentModifiedByName,
I.OUIDS AS InstrumentOuIds,
ISNULL(OU_DATA.OuNames, 'NONE') AS InstrumentOuNames,
I.OUGROUPIDS AS InstrumentOuGroupIds,
ISNULL(OUGROUP_DATA.OuGroupNames, 'NONE') AS InstrumentOuGroupNames,
I.SORTORDER AS InstrumentSortOrder,
I.STATUS AS InstrumentStatus,
I.VERSION AS InstrumentVersion
FROM MINSTRUMENT I
LEFT JOIN MACCOUNT A
ON I.ACCOUNTID = A.ACCOUNTID
LEFT JOIN MPROCESS IPP
ON I.ACCOUNTPOSTTYPE = IPP.PROCESSID
LEFT JOIN MPROCESS IPD
ON I.POSTDATEDTYPE = IPD.PROCESSID
LEFT JOIN MBANKBRANCH BK
ON I.ISAPPLICABLEFORBANK = BK.BANKID
LEFT JOIN MCOUNTER CNT
ON I.ISAPPLICABLEFORCOUNTERTRANSACTION = CNT.COUNTERID
LEFT JOIN MUSER U1
ON I.CREATEDBYID = U1.USERID
LEFT JOIN MUSER U2
ON I.MODIFIEDBYID = U2.USERID
OUTER APPLY
(
SELECT
STUFF((
SELECT ',' + MOU.ORGANIZATIONUNITNAME
FROM
(
SELECT
CASE
WHEN CleanValue NOT IN ('', '-1')
THEN CAST(CleanValue AS INT)
END AS OUID
FROM
(
SELECT LTRIM(RTRIM(SplitOu.a.value('.', 'NVARCHAR(50)'))) AS CleanValue
FROM
(
SELECT CAST('' + REPLACE(ISNULL(I.OUIDS, ''), ',', '') + '' AS XML) AS XmlVal
) OuXml
CROSS APPLY XmlVal.nodes('/X') SplitOu(a)
) CleanOu
) IDS
INNER JOIN MORGANIZATIONUNIT MOU
ON MOU.OUID = IDS.OUID
WHERE IDS.OUID IS NOT NULL
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS OuNames
) OU_DATA
OUTER APPLY
(
SELECT
STUFF((
SELECT ',' + OG.ORGANIZATIONGROUPNAME
FROM
(
SELECT
CASE
WHEN CleanValue NOT IN ('', '-1')
THEN CAST(CleanValue AS INT)
END AS ORGANIZATIONGROUPID
FROM
(
SELECT LTRIM(RTRIM(SplitGrp.a.value('.', 'NVARCHAR(50)'))) AS CleanValue
FROM
(
SELECT CAST('' + REPLACE(ISNULL(I.OUGROUPIDS, ''), ',', '') + '' AS XML) AS XmlVal
) GrpXml
CROSS APPLY XmlVal.nodes('/X') SplitGrp(a)
) CleanGrp
) IDS
INNER JOIN MORGANIZATIONGROUP OG
ON OG.ORGANIZATIONGROUPID = IDS.ORGANIZATIONGROUPID
WHERE IDS.ORGANIZATIONGROUPID IS NOT NULL
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS OuGroupNames
) OUGROUP_DATA
WHERE (@SearchText IS NULL OR @SearchText = ''
OR I.INSTRUMENTNAME LIKE '%' + @SearchText + '%')
AND (@StatusEquals IS NULL OR I.STATUS = @StatusEquals)
AND (@StatusNotEquals IS NULL OR I.STATUS <> @StatusNotEquals)
ORDER BY I.INSTRUMENTNAME;
";
public const string SAVE_INSTRUMENT = @"
INSERT INTO MINSTRUMENT
(
INSTRUMENTID,
INSTRUMENTNAME,
INSTRUMENTTYPE,
ACCOUNTPOSTTYPE,
POSTDATEDTYPE,
VALIDITYPERIODINDAYS,
INSTRUMENTSTATUS,
ISAPPLICABLEFORBANK,
ISAPPLICABLEFORCOUNTERTRANSACTION,
ACCOUNTID,
SORTORDER,
STATUS,
VERSION,
CREATEDBYID,
CREATEDON,
MODIFIEDBYID,
MODIFIEDON,
OUIDS,
OUGROUPIDS,
TENANTID
)
VALUES
(
@InstrumentId,
@InstrumentName,
@InstrumentType,
@InstrumentAccountPostType,
@InstrumentPostDatedType,
@InstrumentValidityPeriodInDays,
@InstrumentStatusOfInstrument,
@InstrumentIsApplicableForBank,
@InstrumentIsApplicableforCounterTransaction,
@AccountId,
@InstrumentSortOrder,
@InstrumentStatus,
@InstrumentVersion,
@InstrumentCreatedById,
@InstrumentCreatedOn,
@InstrumentModifiedById,
@InstrumentModifiedOn,
@InstrumentOuIds,
@InstrumentOuGroupIds,
@TenantId
)";
public const string UPDATE_INSTRUMENT = @"
UPDATE MINSTRUMENT
SET
INSTRUMENTNAME = @InstrumentName,
INSTRUMENTTYPE = @InstrumentType,
ACCOUNTPOSTTYPE = @InstrumentAccountPostType,
POSTDATEDTYPE = @InstrumentPostDatedType,
VALIDITYPERIODINDAYS = @InstrumentValidityPeriodInDays,
INSTRUMENTSTATUS = @InstrumentStatusOfInstrument,
ISAPPLICABLEFORBANK = @InstrumentIsApplicableForBank,
ISAPPLICABLEFORCOUNTERTRANSACTION = @InstrumentIsApplicableforCounterTransaction,
ACCOUNTID = @AccountId,
MODIFIEDBYID = @InstrumentModifiedById,
MODIFIEDON = @InstrumentModifiedOn,
SORTORDER = @InstrumentSortOrder,
STATUS = @InstrumentStatus,
VERSION = @InstrumentVersion,
OUIDS = @InstrumentOuIds,
OUGROUPIDS = @InstrumentOuGroupIds
WHERE
INSTRUMENTID = @InstrumentId
AND TENANTID = @TenantId;
";
public const string DELETE_INSTRUMENT = @"DELETE FROM MINSTRUMENT
WHERE INSTRUMENTID = @InstrumentId;";
public const string GET_SELECTLIST_INSTRUMENT = @"SELECT
A.InstrumentId AS Id,
A.InstrumentName AS Name
FROM MINSTRUMENT A
Left JOIN MORGANIZATIONUNIT OU ON OU.OUID = A.OUIDS";
}
}