using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RecruitmentDAL.Query.JobPosition
{
    public static class JobPositionQB
    {
        public const string GET_JOBPosition = @"
    SELECT
    JD.JOBPositionID     AS JobPositionId,
    JD.POSITIONTITLE        AS PositionTitle,
    JD.JOBTYPEID            AS JobTypeId,
    JD.DEPARTMENTID         AS DepartmentId,
    JD.LEVELID              AS LevelId,
    JD.EXPERIENCE           AS Experience,

    JD.OVERVIEW             AS Overview,
    JD.KEYRESPONSIBILITIES  AS KeyResponsibilities,
    JD.REQUIREDQUALIFICATION AS RequiredQualification,
    JD.TECHSKILLS           AS TechSkills,
    JD.EDUREQUIREMENTS      AS EduRequirements,

    JD.CREATEDBYID          AS CreatedById,
    JD.CREATEDON            AS CreatedOn,
    CU.USERNAME             AS CreatedByName,

    JD.MODIFIEDBYID         AS ModifiedById,
    JD.MODIFIEDON           AS ModifiedOn,
    MU.USERNAME             AS ModifiedByName,

    JD.SORTORDER            AS SortOrder,
    JD.STATUS               AS Status,
    JD.VERSION              AS Version,
    JD.TENANTID             AS TenantId
FROM MJOBPosition JD
LEFT JOIN MUSER CU ON CU.USERID = JD.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = JD.MODIFIEDBYID
LEFT JOIN MDEPARTMENT D ON D.DEPARTMENTID = JD.DEPARTMENTID
WHERE JD.JOBPositionID = @JobPositionId
  AND JD.TENANTID = @TenantId;
";

        public const string SAVE_JOBPosition = @"
    INSERT INTO MJOBPosition (
    JOBPositionID,
    POSITIONTITLE,
    JOBTYPEID,
    DEPARTMENTID,
    LEVELID,
    EXPERIENCE,
    OVERVIEW,
    KEYRESPONSIBILITIES,
    REQUIREDQUALIFICATION,
    TECHSKILLS,
    EDUREQUIREMENTS,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    STATUS,
    VERSION,
    TENANTID
)
VALUES (
    @JobPositionId,
    @PositionTitle,
    @JobTypeId,
    @DepartmentId,
    @LevelId,
    @Experience,
    @Overview,
    @KeyResponsibilities,
    @RequiredQualification,
    @TechSkills,
    @EduRequirements,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,
    @SortOrder,
    @Status,
    @Version,
    @TenantId
);
";
        public const string UPDATE_JOBPosition = @"
UPDATE MJOBPosition
SET
    POSITIONTITLE         = @PositionTitle,
    JOBTYPEID             = @JobTypeId,
    DEPARTMENTID          = @DepartmentId,
    LEVELID               = @LevelId,
    EXPERIENCE            = @Experience,
    OVERVIEW              = @Overview,
    KEYRESPONSIBILITIES   = @KeyResponsibilities,
    REQUIREDQUALIFICATION = @RequiredQualification,
    TECHSKILLS            = @TechSkills,
    EDUREQUIREMENTS       = @EduRequirements,
    MODIFIEDBYID          = @ModifiedById,
    MODIFIEDON            = @ModifiedOn,
    SORTORDER             = @SortOrder,
    STATUS                = @Status,
    VERSION               = @Version
WHERE JOBPositionID = @JobPositionId
  AND TENANTID = @TenantId;
";
   
        public const string DELETE_JOBPosition = @"
DELETE FROM MJOBPosition
WHERE JOBPositionID = @JobPositionId
  AND TENANTID = @TenantId;
";

        public const string GET_SELECTLIST_JOBPosition = @"
SELECT
   JD.JOBPositionID     AS JobPositionId,
    JD.POSITIONTITLE        AS PositionTitle,
    JD.JOBTYPEID            AS JobTypeId,
    JD.DEPARTMENTID         AS DepartmentId,
    JD.LEVELID              AS LevelId,
    JD.EXPERIENCE           AS Experience,

    JD.OVERVIEW             AS Overview,
    JD.KEYRESPONSIBILITIES  AS KeyResponsibilities,
    JD.REQUIREDQUALIFICATION AS RequiredQualification,
    JD.TECHSKILLS           AS TechSkills,
    JD.EDUREQUIREMENTS      AS EduRequirements,

    JD.CREATEDBYID          AS CreatedById,
    JD.CREATEDON            AS CreatedOn,
    CU.USERNAME             AS CreatedByName,

    JD.MODIFIEDBYID         AS ModifiedById,
    JD.MODIFIEDON           AS ModifiedOn,
    MU.USERNAME             AS ModifiedByName,

    JD.SORTORDER            AS SortOrder,
    JD.STATUS               AS Status,
    JD.VERSION              AS Version,
    JD.TENANTID             AS TenantId
FROM MJOBPosition JD
WHERE JD.STATUS = 1
  AND JD.TENANTID = @TenantId
ORDER BY JD.POSITIONTITLE;
";
    }
}