using System;

namespace RecruitmentDAL.Query.ProfileGenerator
{
    public static class ProfileGeneratorQB
    {
        #region GET

        public const string GET_PROFILEGENERATOR = @"
SELECT 
    PG.PROFILEGENERATORID     AS ProfileGeneratorId,

    PG.POSITIONID             AS PositionId,
    PG.DEPARTMENTID           AS DepartmentId,
    PG.LEVELID                AS LevelId,

    PG.INDUSTRYCONTEXT        AS IndustryContext,
    PG.ADDITIONALCONTEXT      AS AdditionalContext,
    PG.JOBDESCRIPTION         AS JobDescription,
    PG.KPIS                   AS Kpis,

    PG.TECHNICALSKILLS        AS TechnicalSkills,
    PG.SOFTSKILLS             AS SoftSkills,
    PG.DOMAINSKILLS           AS DomainSkills,

    PG.TECHNICALQUESTIONS     AS TechnicalQuestions,
    PG.MANAGERIALQUESTIONS    AS ManagerialQuestions,
    PG.SITUATIONALQUESTIONS   AS SituationalQuestions,

    PG.PSYCHOMETRICTOOLS      AS PsychometricTools,
    PG.EXPERIENCEQUALIFICATIONS AS ExperienceQualifications,

    -- BASE / STANDARD
    PG.SORTORDER              AS ProfileGeneratorSortOrder,
    PG.STATUS                 AS ProfileGeneratorStatus,
    PG.VERSION                AS ProfileGeneratorVersion,

    PG.CREATEDBYID            AS ProfileGeneratorCreatedById,
    UC.USERNAME               AS ProfileGeneratorCreatedByName,
    PG.CREATEDON              AS ProfileGeneratorCreatedOn,

    PG.MODIFIEDBYID           AS ProfileGeneratorModifiedById,
    UM.USERNAME               AS ProfileGeneratorModifiedByName,
    PG.MODIFIEDON             AS ProfileGeneratorModifiedOn

FROM TPROFILEGENERATOR PG

LEFT JOIN MUSER UC 
    ON PG.CREATEDBYID = UC.USERID

LEFT JOIN MUSER UM 
    ON PG.MODIFIEDBYID = UM.USERID

WHERE PG.PROFILEGENERATORID = @ProfileGeneratorId;
";

        #endregion


        #region SAVE

        public const string SAVE_PROFILEGENERATOR = @"
INSERT INTO TPROFILEGENERATOR
(
    POSITIONID,
    DEPARTMENTID,
    LEVELID,

    INDUSTRYCONTEXT,
    ADDITIONALCONTEXT,
    JOBDESCRIPTION,
    KPIS,

    TECHNICALSKILLS,
    SOFTSKILLS,
    DOMAINSKILLS,

    TECHNICALQUESTIONS,
    MANAGERIALQUESTIONS,
    SITUATIONALQUESTIONS,

    PSYCHOMETRICTOOLS,
    EXPERIENCEQUALIFICATIONS,

    TENANTID,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    STATUS,
    VERSION
)
VALUES
(
    @PositionId,
    @DepartmentId,
    @LevelId,

    @IndustryContext,
    @AdditionalContext,
    @JobDescription,
    @Kpis,

    @TechnicalSkills,
    @SoftSkills,
    @DomainSkills,

    @TechnicalQuestions,
    @ManagerialQuestions,
    @SituationalQuestions,

    @PsychometricTools,
    @ExperienceQualifications,

    @ProfileGeneratorTenantId,
    @ProfileGeneratorCreatedById,
    @ProfileGeneratorCreatedOn,
    @ProfileGeneratorModifiedById,
    @ProfileGeneratorModifiedOn,
    @ProfileGeneratorSortOrder,
    @ProfileGeneratorStatus,
    @ProfileGeneratorVersion
);
SELECT SCOPE_IDENTITY();
";

        #endregion


        #region UPDATE

        public const string UPDATE_PROFILEGENERATOR = @"
UPDATE TPROFILEGENERATOR SET

    POSITIONID = @PositionId,
    DEPARTMENTID = @DepartmentId,
    LEVELID = @LevelId,

    INDUSTRYCONTEXT = @IndustryContext,
    ADDITIONALCONTEXT = @AdditionalContext,
    JOBDESCRIPTION = @JobDescription,
    KPIS = @Kpis,

    TECHNICALSKILLS = @TechnicalSkills,
    SOFTSKILLS = @SoftSkills,
    DOMAINSKILLS = @DomainSkills,

    TECHNICALQUESTIONS = @TechnicalQuestions,
    MANAGERIALQUESTIONS = @ManagerialQuestions,
    SITUATIONALQUESTIONS = @SituationalQuestions,

    PSYCHOMETRICTOOLS = @PsychometricTools,
    EXPERIENCEQUALIFICATIONS = @ExperienceQualifications,

    MODIFIEDBYID = @ProfileGeneratorModifiedById,
    MODIFIEDON = @ProfileGeneratorModifiedOn

WHERE PROFILEGENERATORID = @ProfileGeneratorId;
";

        #endregion


        #region DELETE

        public const string DELETE_PROFILEGENERATOR = @"
DELETE FROM TPROFILEGENERATOR
WHERE PROFILEGENERATORID = @ProfileGeneratorId;
";

        #endregion


        #region SELECT LIST

        public const string GET_SELECTLIST_PROFILEGENERATOR = @"
SELECT 
    PROFILEGENERATORID     AS ProfileGeneratorId,

    POSITIONID             AS PositionId,
    DEPARTMENTID           AS DepartmentId,
    LEVELID                AS LevelId,

    INDUSTRYCONTEXT        AS IndustryContext,
    ADDITIONALCONTEXT      AS AdditionalContext,
    JOBDESCRIPTION         AS JobDescription,
    KPIS                   AS Kpis,

    TECHNICALSKILLS        AS TechnicalSkills,
    SOFTSKILLS             AS SoftSkills,
    DOMAINSKILLS           AS DomainSkills,

    TECHNICALQUESTIONS     AS TechnicalQuestions,
    MANAGERIALQUESTIONS    AS ManagerialQuestions,
    SITUATIONALQUESTIONS   AS SituationalQuestions,

    PSYCHOMETRICTOOLS      AS PsychometricTools,
    EXPERIENCEQUALIFICATIONS AS ExperienceQualifications,

    TENANTID               AS ProfileGeneratorTenantId,

    CREATEDBYID            AS ProfileGeneratorCreatedById,
    CREATEDON              AS ProfileGeneratorCreatedOn,
    MODIFIEDBYID           AS ProfileGeneratorModifiedById,
    MODIFIEDON             AS ProfileGeneratorModifiedOn,

    SORTORDER              AS ProfileGeneratorSortOrder,
    STATUS                 AS ProfileGeneratorStatus,
    VERSION                AS ProfileGeneratorVersion

FROM TPROFILEGENERATOR
WHERE STATUS = 1
ORDER BY PROFILEGENERATORID;
";

        #endregion
    }
}