namespace TMSDAL.Query.Competency
{
    public static class CompetencyQB
    {
        public const string GET_COMPETENCY = @"
SELECT
    c.COMPETENCYID          AS CompetencyId,
    c.COMPETENCYCODE        AS CompetencyCode,
    c.COMPETENCYNAME        AS CompetencyName,
    c.COMPETENCYSHORTNAME   AS CompetencyShortName,
    c.DOMAINID              AS DomainId,
    c.GROUPID               AS GroupId,
    c.COMPETENCYTYPE        AS CompetencyType,
    c.MAXLEVELNO            AS MaxLevelNo,
    c.ISACTIVE              AS IsActive,
    c.DISPLAYORDER          AS DisplayOrder,
    c.DESCRIPTION           AS Description,
    c.TENANTID              AS TenantId,
    c.CREATEDBYID           AS CreatedById,
    c.CREATEDON             AS CreatedOn,
    c.MODIFIEDBYID          AS ModifiedById,
    c.MODIFIEDON            AS ModifiedOn
FROM DBO.MCOMPETENCY c
WHERE c.COMPETENCYID = @CompetencyId
  AND c.TENANTID = @TenantId;";

        public const string SAVE_COMPETENCY = @"
INSERT INTO DBO.MCOMPETENCY
(
    COMPETENCYID,
    COMPETENCYCODE,
    COMPETENCYNAME,
    COMPETENCYSHORTNAME,
    DOMAINID,
    GROUPID,
    COMPETENCYTYPE,
    MAXLEVELNO,
    ISACTIVE,
    DISPLAYORDER,
    DESCRIPTION,
    TENANTID,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @CompetencyId,
    @CompetencyCode,
    @CompetencyName,
    @CompetencyShortName,
    @DomainId,
    @GroupId,
    @CompetencyType,
    @MaxLevelNo,
    @IsActive,
    @DisplayOrder,
    @Description,
    @TenantId,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn
);";

        public const string UPDATE_COMPETENCY = @"
UPDATE DBO.MCOMPETENCY
SET
    COMPETENCYCODE      = @CompetencyCode,
    COMPETENCYNAME      = @CompetencyName,
    COMPETENCYSHORTNAME = @CompetencyShortName,
    DOMAINID            = @DomainId,
    GROUPID             = @GroupId,
    COMPETENCYTYPE      = @CompetencyType,
    MAXLEVELNO          = @MaxLevelNo,
    ISACTIVE            = @IsActive,
    DISPLAYORDER        = @DisplayOrder,
    DESCRIPTION         = @Description,
    MODIFIEDBYID        = @ModifiedById,
    MODIFIEDON          = @ModifiedOn
WHERE COMPETENCYID = @CompetencyId
  AND TENANTID = @TenantId;";

        public const string DELETE_COMPETENCY = @"
DELETE FROM DBO.MCOMPETENCY
WHERE COMPETENCYID = @CompetencyId
  AND TENANTID = @TenantId;";

        public const string GET_SELECTLIST_COMPETENCY = @"
SELECT
    c.COMPETENCYID      AS CompetencyId,
    c.COMPETENCYCODE    AS CompetencyCode,
    c.COMPETENCYNAME    AS CompetencyName
FROM DBO.MCOMPETENCY c
WHERE c.ISACTIVE = 1
  AND c.TENANTID = @TenantId
ORDER BY c.COMPETENCYNAME;";

        public const string GET_COMPETENCY_LIST = @"
SELECT
    c.COMPETENCYID          AS CompetencyId,
    c.COMPETENCYCODE        AS CompetencyCode,
    c.COMPETENCYNAME        AS CompetencyName,
    c.COMPETENCYSHORTNAME   AS CompetencyShortName,
    c.DOMAINID              AS DomainId,
    sd.DOMAINNAME           AS DomainName,
    c.GROUPID               AS GroupId,
    sg.GROUPNAME            AS GroupName,
    c.COMPETENCYTYPE        AS CompetencyType,
    c.MAXLEVELNO            AS MaxLevelNo,
    c.ISACTIVE              AS IsActive,
    c.DISPLAYORDER          AS DisplayOrder,
    c.DESCRIPTION           AS Description,
    c.CREATEDON             AS CreatedOn
FROM DBO.MCOMPETENCY c
LEFT JOIN DBO.MSKILLDOMAIN sd
    ON sd.DOMAINID = c.DOMAINID
LEFT JOIN DBO.MSKILLGROUP sg
    ON sg.GROUPID = c.GROUPID
WHERE c.TENANTID = @TenantId
ORDER BY c.DISPLAYORDER, c.COMPETENCYNAME;";
    }
}