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

namespace PayRollDAL.Query.Applicant
{
    public static class ApplicantQB
    {
        // GB4 parity: Applicant.svc/SelectList — ApplicantDAL, Type == -1 (master query, all applicants).
        public const string GET_APPLICANT_SELECTLIST_MASTER = @"
WITH ApplicantSelectList AS
(
    SELECT
        app.APPLICANTID       AS ApplicantId,
        app.APPLICANTNAME     AS ApplicantName,
        app.MAILID            AS MailId,
        app.TOTALEXPERIENCE   AS TotalExperience,
        app.RELAVENTEXPERIENCE AS RelaventExperience,
        app.DATASOURCEID      AS DataSourceId,
        dsource.GCMCODE       AS DataSourceCode,
        dsource.GCMNAME       AS DataSourceName,
        app.MOBILE            AS Mobile,
        app.ALTERNATEMOBILE   AS AlternateMobile,
        app.CURRENTEMPLOYER   AS CurrentEmployer,
        app.EDUCATIONAL       AS Educational,
        ROW_NUMBER() OVER (ORDER BY app.APPLICANTNAME) AS RowNum
    FROM TAPPLICANT app
    JOIN MGCM dsource ON app.DATASOURCEID = dsource.GCMID
    WHERE 1 = 1
    /*CRITERIA*/
)
SELECT ApplicantId, ApplicantName, MailId, TotalExperience, RelaventExperience, DataSourceId, DataSourceCode, DataSourceName, Mobile, AlternateMobile, CurrentEmployer, Educational
FROM ApplicantSelectList
WHERE (@FirstNumber = -1 AND @MaxResult = -1)
   OR (RowNum > @FirstNumber AND RowNum <= @FirstNumber + @MaxResult)
ORDER BY RowNum;";

        public const string GET_APPLICANT_SELECTLIST_MASTER_COUNT = @"
SELECT COUNT(*) AS TotalCount
FROM TAPPLICANT app
JOIN MGCM dsource ON app.DATASOURCEID = dsource.GCMID
WHERE 1 = 1
/*CRITERIA*/;";

        // GB4 parity: Applicant.svc/SelectList, Type == 0 (transaction query — applicants not yet
        // added to a selection process for the given job, i.e. still available to add to a job pipeline).
        public const string GET_APPLICANT_SELECTLIST_TRANSACTION = @"
WITH ApplicantSelectList AS
(
    SELECT
        app.APPLICANTID       AS ApplicantId,
        app.APPLICANTNAME     AS ApplicantName,
        app.MAILID            AS MailId,
        app.TOTALEXPERIENCE   AS TotalExperience,
        app.RELAVENTEXPERIENCE AS RelaventExperience,
        app.DATASOURCEID      AS DataSourceId,
        dsource.GCMCODE       AS DataSourceCode,
        dsource.GCMNAME       AS DataSourceName,
        app.MOBILE            AS Mobile,
        app.ALTERNATEMOBILE   AS AlternateMobile,
        app.CURRENTEMPLOYER   AS CurrentEmployer,
        app.EDUCATIONAL       AS Educational,
        ROW_NUMBER() OVER (ORDER BY app.APPLICANTNAME) AS RowNum
    FROM TAPPLICANT app
    LEFT OUTER JOIN TSELECTION ts ON app.JOBID = ts.JOBID AND app.APPLICANTID = ts.APPLICANTID
    JOIN MGCM dsource ON app.DATASOURCEID = dsource.GCMID
    WHERE ts.APPLICANTID IS NULL
    /*CRITERIA*/
)
SELECT ApplicantId, ApplicantName, MailId, TotalExperience, RelaventExperience, DataSourceId, DataSourceCode, DataSourceName, Mobile, AlternateMobile, CurrentEmployer, Educational
FROM ApplicantSelectList
WHERE (@FirstNumber = -1 AND @MaxResult = -1)
   OR (RowNum > @FirstNumber AND RowNum <= @FirstNumber + @MaxResult)
ORDER BY RowNum;";

        public const string GET_APPLICANT_SELECTLIST_TRANSACTION_COUNT = @"
SELECT COUNT(*) AS TotalCount
FROM TAPPLICANT app
LEFT OUTER JOIN TSELECTION ts ON app.JOBID = ts.JOBID AND app.APPLICANTID = ts.APPLICANTID
JOIN MGCM dsource ON app.DATASOURCEID = dsource.GCMID
WHERE ts.APPLICANTID IS NULL
/*CRITERIA*/;";
    }
}