CreditRiskContractReadingService

        {
            var query = @"
                SELECT
                    c.[ContractId],
                    c.[ReportingDate],
                    c.[Entity],
                    cm.[ContractMasterId],
                    cm.[OriginContractId],
                    cm.[OriginSystemId],
                    c.[ProductType],
                    c.[ProductSubtype],
                    c.[BusinessUnit],
                    c.[CounterpartyId],
                    c.[BaseLgd],
                    c.[BasePd],
                    c.[CreditRating],
                    c.[CreditRatingAgency],
                    c.[Collectibility],
                    c.[IsRestructured],
                    c.[Currency],
                    c.[CurrentPd],
                    c.[DaysPastDue],
                    c.[DiscountRate],
                    c.[EffectiveInterestRate],
                    c.[EffectiveInterestRateIncludingFeesAndCosts],
                    c.[Segment],
                    c.[IsSecured],
                    c.[InitialPd],
                    c.[InterestRate],
                    c.[InterestSpread],
                    c.[InterestCurve],
                    c.[StartDate],
                    c.[MaturityDate],
                    c.[PrincipalAmount],
                    c.[OutstandingAmount],
                    c.[LimitAmount],
                    c.[AccruedInterest],
                    c.[PresentValue],
                    c.[PresentValueIncludingFeesAndCosts],
                    cn.[CounterpartyType],
                    cnm.[OriginCounterpartyId],
                    JSON_QUERY((
                        SELECT
                            ra.[Code] AS [ratingAgency],
                            rs.[Code] AS [ratingScale],
                            r.[Code] AS [rating]
                        FROM [ContractRatings] cr
                        JOIN [Ratings] r
                            ON cr.[RatingId] = r.[RatingId]
                        JOIN [RatingScales] rs
                            ON r.[RatingScaleId] = rs.[RatingScaleId]
                        JOIN [RatingAgencies] ra
                            ON rs.[RatingAgencyId] = ra.[RatingAgencyId]
                        WHERE cr.[ContractId] = c.[ContractId]
                            FOR JSON PATH
                    )) AS [ContractRatings],
                    JSON_QUERY((
                        SELECT
                            ra.[Code] AS [ratingAgency],
                            rs.[Code] AS [ratingScale],
                            r.[Code] AS [rating]
                        FROM [CounterpartyRatings] cr
                        JOIN [Ratings] r
                            ON cr.[RatingId] = r.[RatingId]
                        JOIN [RatingScales] rs
                            ON r.[RatingScaleId] = rs.[RatingScaleId]
                        JOIN [RatingAgencies] ra
                            ON rs.[RatingAgencyId] = ra.[RatingAgencyId]
                        WHERE cr.[CounterpartyId] = c.[CounterpartyId]
                            FOR JSON PATH
                    )) AS [CounterpartyRatings],
                    JSON_QUERY((
                        SELECT
                            cl.[CollateralId] AS [collateralId],
                            clm.[OriginSystemId] AS [originSystemId],
                            clm.[OriginCollateralId] AS [originCollateralId],
                            cl.[CollateralType] AS [collateralType],
                            JSON_VALUE(cl.Data, '$.additionalProperties.collateralTypeProperty') AS [collateralTypeProperty],
                            JSON_VALUE(cl.Data, '$.additionalProperties.collateralMaturityDate') AS [collateralMaturityDate],
                            cl.[CollateralAmount] AS [collateralAmount],
                            cl.[Currency] AS [currency],
                            @ReportingDate AS [reportingDate]
                        FROM [CollateralContracts] cc
                        JOIN [Collaterals] cl
                          ON cc.[CollateralId] = cl.[CollateralId]
                        JOIN [CollateralMaster] clm
                          ON clm.[CollateralMasterId] = cl.[CollateralMasterId]
                       WHERE cc.[ContractId] = c.[ContractId]
                         AND cl.[CollateralType] in ('101', '102', '282', '288', '289', '290', '291', '292','CDI')
                         FOR JSON PATH
                    )) AS [Collaterals],

                    ce.[GolonganDebitur],
                    ce.[LBUForm],
                    ce.[GolonganDebiturRWA],
                    ce.[LBUFormRWA],
                    ce.[SpecificProvisionAmount],
                    ce.[Stage],
                    ce.[Level],
                    ce.[OutstandingAmountRWA],
                    ce.[AccruedInterestRWA],
                    ce.[LtvRatio],
                    ce.[LtvRatioRWA],
                    ce.[LtvRatioRWAB4],
                    ce.[SecId],
                    ce.[MarketRate],
                    ce.[LendingType],
                    ce.[LandType],
                    ce.[GASReporting],
                    ce.[CommittedUndrawnAmount],
                    ce.[UncommittedUndrawnAmount],
                    ce.[GlNumber],
                    ce.[GlNumberRWA],
                    ce.[Financing],
                    ce.[FinancingB4],
                    ce.[LimitDebtor],
                    ce.[LimitDebtorB4],
                    ce.[BaselCustomerType],
                    ce.[BaselCustomerTypeB4],
                    ce.[BaselProductSubtype],
                    ce.[BaselProductSubtypeB4],
                    ce.[BaselEconomicSector],
                    ce.[BranchCode],

                    cpsd.Data AS [ContractPropsSpotDerivativeData],
                    cpsz.Data AS [ContractPropsSecuritizationData],

                    JSON_VALUE(cnp.Data, '$.netSalesCust') AS [NetSalesAmount],
                    mbca.[TransactorFlag],
                    mbca.[CollateralTypeProperty],

                    'Fix' as [PayLegInterestType],
                    'Floating' as [ReceiveLegInterestType]

                FROM [Contracts] c
                JOIN [ContractMaster] cm
                  ON cm.[ContractMasterId] = c.[ContractMasterId]
                JOIN [Counterparties] cn
                  ON cn.[CounterpartyId] = c.[CounterpartyId]
                JOIN [CounterpartyMaster] cnm
                  ON cn.[CounterpartyMasterId] = cnm.[CounterpartyMasterId]
                JOIN [ContractsExtension] ce
                  ON c.[ContractId] = ce.[ContractId]
           LEFT JOIN [ContractPropsSpotDerivative] cpsd
                  ON c.[ContractId] = cpsd.[ContractId]
           LEFT JOIN [ContractPropsSecuritization] cpsz
                  ON c.[ContractId] = cpsz.[ContractId]
           LEFT JOIN [CounterpartyProps] cnp
                  ON c.[CounterpartyId] = cnp.[CounterpartyId]
           LEFT JOIN [MandiriBaselContractAggregate] mbca
                  ON c.[ContractId] = mbca.[ContractId]
            "
            +
            // This SECTION IS FOR querying standalone contracts
            (SupportedContractType.Single.Equals(contractType) ?
            @"
           LEFT JOIN (SELECT DISTINCT cc.[ContractId]
                        FROM [CollateralContracts] cc
                        JOIN [CollateralClusters] clc
                          ON clc.[ClusterId] = cc.[ClusterId]
                       WHERE clc.[NumContracts] > 1) AS nn
                  ON c.[ContractId] = nn.[ContractId]
            "
            :
            // This SECTION IS FOR querying contracts IN a specified cluster
            @"
                JOIN (SELECT DISTINCT cc.[ContractId]
                        FROM [CollateralContracts] cc
                       WHERE cc.[ClusterId] = @ClusterId) AS clct
                  ON c.[ContractId] = clct.[ContractId]
            ")
            +
            @"
                WHERE 1=1
                AND c.[ReportingDate] = @ReportingDate
                AND c.[Entity] = @Entity
                AND cm.[OriginSystemId] <> 'ICS-IRB'
                AND (ce.[PsakClassification] <> 'T' OR ce.[PsakClassification] is NULL)
            "
            +
            // This SECTION IS FOR querying standalone contracts
            (SupportedContractType.Single.Equals(contractType) ?
            @"
                AND nn.[ContractId] IS NULL
            "
            :
            // This SECTION IS FOR querying contracts IN a specified cluster
            @"")
            ;