namespace TMSDAL.Query.ProgrammeCatalogue
{
    public static class ProgrammeCatalogueQB
    {
        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_CATALOGUE = @"
SELECT  tp.PROGRAMMEID          AS ProgrammeId,
        tp.PROGRAMMECODE        AS ProgrammeCode,
        tp.PROGRAMMETITLE       AS ProgrammeTitle,
        tp.SHORTDESCRIPTION     AS ShortDescription,
        tp.FULLDESCRIPTION      AS FullDescription,
        tp.TRAININGDOMAINID     AS TrainingDomainId,
        td.DOMAINNAME           AS DomainName,
        tp.PROGRAMMETYPE        AS ProgrammeType,
        tp.DELIVERYMODE         AS DeliveryMode,
        tp.COMPLEXITYLEVEL      AS ComplexityLevel,
        tp.DIFFICULTYLEVEL      AS DifficultyLevel,
        tp.PROVIDERTYPE         AS ProviderType,
        tp.DURATIONHOURS        AS DurationHours,
        tp.ISREFRESHER          AS IsRefresher,
        tp.HASASSESSMENT        AS HasAssessment,
        tp.HASPREASSESSMENT     AS HasPreAssessment,
        tp.CERTONCOMPLETE       AS CertOnComplete,
        tp.CERTVALIDITYDAYS     AS CertValidityDays,
        tp.RENEWALWINDOWDAYS    AS RenewalWindowDays,
        tp.MINCAPACITY          AS MinCapacity,
        tp.MAXCAPACITY          AS MaxCapacity,
        tp.COSTPERPARTICIPANT   AS CostPerParticipant,
        tp.LEARNINGOBJECTIVES   AS LearningObjectives,
        tp.TARGETAUDIENCE       AS TargetAudience,
        tp.PREREQUISITES        AS Prerequisites,
        tp.PROGRAMMEVERSIONNO   AS ProgrammeVersionNo,
        tp.ISACTIVE             AS IsActive,
        tp.VERSION              AS Version,
        tp.STATUS               AS Status,
        tp.SORTORDER            AS SortOrder,
        tp.SOURCETYPE           AS SourceType,
        tp.TENANTID             AS TenantId,
        tp.CREATEDBYID          AS CreatedById,
        tp.CREATEDON            AS CreatedOn,
        tp.MODIFIEDBYID         AS ModifiedById,
        tp.MODIFIEDON           AS ModifiedOn
FROM    DBO.MTRAININGPROGRAMME tp
LEFT JOIN DBO.MTRAININGDOMAIN td
       ON td.TRAININGDOMAINID = tp.TRAININGDOMAINID
      AND td.TENANTID = tp.TENANTID
WHERE   tp.PROGRAMMEID = @ProgrammeId
AND     tp.TENANTID    = @TenantId";

        // =====================================================
        // GET LIST
        // =====================================================
        public const string GET_CATALOGUE_LIST = @"
SELECT  tp.PROGRAMMEID          AS ProgrammeId,
        tp.PROGRAMMECODE        AS ProgrammeCode,
        tp.PROGRAMMETITLE       AS ProgrammeTitle,
        tp.SHORTDESCRIPTION     AS ShortDescription,
        tp.FULLDESCRIPTION      AS FullDescription,
        tp.TRAININGDOMAINID     AS TrainingDomainId,
        td.DOMAINNAME           AS DomainName,
        tp.PROGRAMMETYPE        AS ProgrammeType,
        tp.DELIVERYMODE         AS DeliveryMode,
        tp.COMPLEXITYLEVEL      AS ComplexityLevel,
        tp.DIFFICULTYLEVEL      AS DifficultyLevel,
        tp.PROVIDERTYPE         AS ProviderType,
        tp.DURATIONHOURS        AS DurationHours,
        tp.ISREFRESHER          AS IsRefresher,
        tp.HASASSESSMENT        AS HasAssessment,
        tp.HASPREASSESSMENT     AS HasPreAssessment,
        tp.CERTONCOMPLETE       AS CertOnComplete,
        tp.CERTVALIDITYDAYS     AS CertValidityDays,
        tp.RENEWALWINDOWDAYS    AS RenewalWindowDays,
        tp.MINCAPACITY          AS MinCapacity,
        tp.MAXCAPACITY          AS MaxCapacity,
        tp.COSTPERPARTICIPANT   AS CostPerParticipant,
        tp.LEARNINGOBJECTIVES   AS LearningObjectives,
        tp.TARGETAUDIENCE       AS TargetAudience,
        tp.PREREQUISITES        AS Prerequisites,
        tp.PROGRAMMEVERSIONNO   AS ProgrammeVersionNo,
        tp.ISACTIVE             AS IsActive,
        tp.VERSION              AS Version,
        tp.STATUS               AS Status,
        tp.SORTORDER            AS SortOrder,
        tp.SOURCETYPE           AS SourceType,
        tp.TENANTID             AS TenantId,
        tp.CREATEDBYID          AS CreatedById,
        tp.CREATEDON            AS CreatedOn,
        tp.MODIFIEDBYID         AS ModifiedById,
        tp.MODIFIEDON           AS ModifiedOn
FROM    DBO.MTRAININGPROGRAMME tp
LEFT JOIN DBO.MTRAININGDOMAIN td
       ON td.TRAININGDOMAINID = tp.TRAININGDOMAINID
      AND td.TENANTID = tp.TENANTID
WHERE   tp.TENANTID = @TenantId
ORDER BY tp.PROGRAMMETITLE";

        // =====================================================
        // GET PAGED (filtered catalogue view)
        // =====================================================
        public const string GET_CATALOGUE_PAGED = @"
SELECT  tp.PROGRAMMEID        AS ProgrammeId,
        tp.PROGRAMMECODE      AS ProgrammeCode,
        tp.PROGRAMMETITLE     AS ProgrammeTitle,
        tp.PROGRAMMETYPE      AS ProgrammeType,
        tp.DELIVERYMODE       AS DeliveryMode,
        tp.COMPLEXITYLEVEL    AS ComplexityLevel,
        td.DOMAINNAME         AS DomainName,
        tp.DURATIONHOURS      AS DurationHours,
        tp.ISACTIVE           AS IsActive,
        tp.CREATEDON          AS CreatedOn
FROM    DBO.MTRAININGPROGRAMME tp
LEFT JOIN DBO.MTRAININGDOMAIN td
       ON td.TRAININGDOMAINID = tp.TRAININGDOMAINID
      AND td.TENANTID = tp.TENANTID
WHERE   tp.TENANTID = @TenantId
AND     (@IsActive IS NULL OR tp.ISACTIVE = @IsActive)
AND     (@ProgrammeType IS NULL OR tp.PROGRAMMETYPE = @ProgrammeType)
AND     (@DomainId IS NULL OR tp.TRAININGDOMAINID = @DomainId)
ORDER BY tp.PROGRAMMETITLE";

        public const string GET_CATALOGUE_PAGED_COUNT = @"
SELECT COUNT(1)
FROM DBO.MTRAININGPROGRAMME tp
WHERE tp.TENANTID = @TenantId
AND (@IsActive IS NULL OR tp.ISACTIVE = @IsActive)
AND (@ProgrammeType IS NULL OR tp.PROGRAMMETYPE = @ProgrammeType)
AND (@DomainId IS NULL OR tp.TRAININGDOMAINID = @DomainId)";

        // =====================================================
        // GET SELECT LIST
        // =====================================================
        public const string GET_SELECTLIST_CATALOGUE = @"
SELECT  PROGRAMMEID     AS ProgrammeId,
        PROGRAMMECODE   AS ProgrammeCode,
        PROGRAMMETITLE  AS ProgrammeTitle
FROM    DBO.MTRAININGPROGRAMME
WHERE   ISACTIVE = 0
AND     TENANTID = @TenantId
ORDER BY PROGRAMMETITLE";

        // =====================================================
        // SAVE
        // =====================================================
        public const string SAVE_CATALOGUE = @"
INSERT INTO DBO.MTRAININGPROGRAMME
(
    PROGRAMMEID, PROGRAMMECODE, PROGRAMMETITLE,
    SHORTDESCRIPTION, FULLDESCRIPTION,
    TRAININGDOMAINID,
    PROGRAMMETYPE, DELIVERYMODE, COMPLEXITYLEVEL,
    DIFFICULTYLEVEL, PROVIDERTYPE,
    DURATIONHOURS,
    ISREFRESHER, HASASSESSMENT, HASPREASSESSMENT, CERTONCOMPLETE,
    CERTVALIDITYDAYS, RENEWALWINDOWDAYS,
    MINCAPACITY, MAXCAPACITY,
    COSTPERPARTICIPANT,
    LEARNINGOBJECTIVES, TARGETAUDIENCE, PREREQUISITES,
    PROGRAMMEVERSIONNO,
    ISACTIVE,
    VERSION, STATUS, SORTORDER, SOURCETYPE,
    TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @ProgrammeId, @ProgrammeCode, @ProgrammeTitle,
    @ShortDescription, @FullDescription,
    @TrainingDomainId,
    @ProgrammeType, @DeliveryMode, @ComplexityLevel,
    @DifficultyLevel, @ProviderType,
    @DurationHours,
    @IsRefresher, @HasAssessment, @HasPreAssessment, @CertOnComplete,
    @CertValidityDays, @RenewalWindowDays,
    @MinCapacity, @MaxCapacity,
    @CostPerParticipant,
    @LearningObjectives, @TargetAudience, @Prerequisites,
    @ProgrammeVersionNo,
    @IsActive,
    @Version, @Status, @SortOrder, @SourceType,
    @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";

        // =====================================================
        // UPDATE
        // =====================================================
        public const string UPDATE_CATALOGUE = @"
UPDATE  DBO.MTRAININGPROGRAMME
SET     PROGRAMMECODE       = @ProgrammeCode,
        PROGRAMMETITLE      = @ProgrammeTitle,
        SHORTDESCRIPTION    = @ShortDescription,
        FULLDESCRIPTION     = @FullDescription,
        TRAININGDOMAINID    = @TrainingDomainId,
        PROGRAMMETYPE       = @ProgrammeType,
        DELIVERYMODE        = @DeliveryMode,
        COMPLEXITYLEVEL     = @ComplexityLevel,
        DIFFICULTYLEVEL     = @DifficultyLevel,
        PROVIDERTYPE        = @ProviderType,
        DURATIONHOURS       = @DurationHours,
        ISREFRESHER         = @IsRefresher,
        HASASSESSMENT       = @HasAssessment,
        HASPREASSESSMENT    = @HasPreAssessment,
        CERTONCOMPLETE      = @CertOnComplete,
        CERTVALIDITYDAYS    = @CertValidityDays,
        RENEWALWINDOWDAYS   = @RenewalWindowDays,
        MINCAPACITY         = @MinCapacity,
        MAXCAPACITY         = @MaxCapacity,
        COSTPERPARTICIPANT  = @CostPerParticipant,
        LEARNINGOBJECTIVES  = @LearningObjectives,
        TARGETAUDIENCE      = @TargetAudience,
        PREREQUISITES       = @Prerequisites,
        PROGRAMMEVERSIONNO  = @ProgrammeVersionNo,
        ISACTIVE            = @IsActive,
        MODIFIEDBYID        = @ModifiedById,
        MODIFIEDON          = @ModifiedOn
WHERE   PROGRAMMEID = @ProgrammeId
AND     TENANTID    = @TenantId";

        // =====================================================
        // DELETE
        // =====================================================
        public const string DELETE_CATALOGUE = @"
DELETE FROM DBO.MTRAININGPROGRAMME
WHERE PROGRAMMEID = @ProgrammeId
AND   TENANTID    = @TenantId";
    }
}