using HRM.BO;
using Ease.Core.DataAccess;
using System;
using System.Data;

namespace HRM.DA
{
    public class RecruitementProcessDA
    {
        internal static void Insert(RecruitementProcess obRecrProc, TransactionContext tc)
        {
            DateTime? endDate;
            if (obRecrProc.EndDate == DateTime.MinValue) endDate = null;
            else endDate = obRecrProc.EndDate;

            string sql = SQLParser.MakeSQL(@"Insert Into RECRUITEMENTPROCESS(RECRUITEMENTPROCESSID,RequisitionID,Code,
                         Description,StartDate,EndDate,ProcessStatus, CREATEDBY,CREATIONDATE) Values(%n,%n ,%s,%s,%d,%d,%n, %n,%D)", obRecrProc.ID,
                         obRecrProc.requisitionID, obRecrProc.Code,
                obRecrProc.Description, obRecrProc.StartDate, DataReader.GetNullValue(endDate),
                obRecrProc.ProcessStatus, obRecrProc.CreatedBy, obRecrProc.CreatedDate);
            tc.ExecuteNonQuery(sql);
        }

        internal static void Update(RecruitementProcess obRecrProc, TransactionContext tc)
        {
            DateTime? endDate;
            if (obRecrProc.EndDate == DateTime.MinValue) endDate = null;
            else endDate = obRecrProc.EndDate;
            string sql = SQLParser.MakeSQL(@"Update RECRUITEMENTPROCESS Set RequisitionID=%n, Code = %s,
                         Description = %s,StartDate = %d,EndDate = %d,ProcessStatus = %n, MODIFIEDBY=%n, MODIFICATIONDATE=%D Where RECRUITEMENTPROCESSID = %n",
                obRecrProc.requisitionID, obRecrProc.Code, obRecrProc.Description, obRecrProc.StartDate, DataReader.GetNullValue(endDate),
                obRecrProc.ProcessStatus, obRecrProc.ModifiedBy, obRecrProc.ModifiedDate, obRecrProc.ID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void Delete(int id, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Delete From RECRUITEMENTPROCESS Where RECRUITEMENTPROCESSID = %n", id);
            tc.ExecuteNonQuery(sql);
        }

        internal static IDataReader Get(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From RECRUITEMENTPROCESS");
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetByProcessId(TransactionContext tc, int procID)
        {
            string sql = SQLParser.MakeSQL(@"Select * From RECRUITEMENTPROCESS Where RECRUITEMENTPROCESSID = %n",
                procID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetByRequisitionID(TransactionContext tc, int requisitionID)
        {
            string sql = SQLParser.MakeSQL(@"Select * From RECRUITEMENTPROCESS Where RequisitionID = %n",
                requisitionID);
            return tc.ExecuteReader(sql);
        }
        internal static DataSet GetShortListedCandidates(int processID, TransactionContext tc)
        {
////            string sql = SQLParser.MakeSQL(@"
////                         select A.cvID candidateID,count(A.cvID) totalStep ,IsEmployee = 0 from 
////                         (select cvID from RecruitementCandidate  where processID = %n and IsEmployee = 0) A 
////                         inner join 
////                         (
////                         select * From recruitementSelectedCandidate Where processID = %n And (
////                         stepID in (select RecruitementStepID from RecruitementStep Where ProcessId = %n) And isSelected = 1 and IsEmployee = 0) 
////                         ) 
////                         B
////                         on 
////                         A.cvID = B.CandidateID 
////                         group by cvID having count(A.cvID) = (select count(*) from RecruitementStep where processID = %n)
////                         union 
////                         select A.employeeID candidateID,count(A.employeeID) totalStep,IsEmployee = 1 from 
////                         (select employeeID from RecruitementCandidate  where processID = %n and IsEmployee = 1) A 
////                         inner join 
////                         ( 
////                         select * From recruitementSelectedCandidate Where processID = %n And (
////                         stepID in (select RecruitementStepID from RecruitementStep Where ProcessId = %n) And isSelected = 1 and IsEmployee = 1) 
////                         ) 
////                         B
////                         on 
////                         A.employeeID = B.CandidateID 
////                         group by employeeID having count(A.employeeID) = (select count(*) from RecruitementStep where processID = %n)",processID,processID,processID,processID,processID,processID,processID,processID);

            string sql =
                SQLParser.MakeSQL(
                    @"select * From recruitementSelectedCandidate  Where processID = %n And (isSelected = 1 And 
                         stepID = (select MAX(RecruitementStepID) from RecruitementStep Where ProcessId = %n) )",
                    processID, processID);

            return tc.ExecuteDataSet(sql);
        }

        internal static IDataReader CheckCode(TransactionContext tc, string code)
        {
            string sql = SQLParser.MakeSQL(@"Select * From RECRUITEMENTPROCESS Where Code = %s", code);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetByCandediateID(TransactionContext tc, int nCandidateID)
        {
            string sql = SQLParser.MakeSQL(@"Select * from RecruitementCandidate where CandidateID=%n", nCandidateID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader Get(string query, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Select * From RECRUITEMENTPROCESS Where (%q)", query);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetProcess(TransactionContext tc, int status)
        {
            string sql = SQLParser.MakeSQL(@"Select * From RECRUITEMENTPROCESS Where ProcessStatus = %n", status);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetProcess(TransactionContext tc, int processID, int stepID, int empID)
        {
            string sql = SQLParser.MakeSQL(
                @"Select * From RECRUITEMENTPROCESS Where  RECRUITEMENTPROCESSID in (select ProcessId" +
                " from dbo.RecruitementBoardMember where EmployeeId=%n and StepId=%n and ProcessId=%n)",
                empID, stepID, processID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetProcess(TransactionContext tc, int status, int empID)
        {
            string sql = SQLParser.MakeSQL(
                @"Select * From RECRUITEMENTPROCESS Where ProcessStatus = %n and RECRUITEMENTPROCESSID in " +
                " (select ProcessId from dbo.RecruitementBoardMember where EmployeeId=%n)",
                status, empID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetProcessID(TransactionContext tc, int status, int empID, int stepID)
        {
            string sql = SQLParser.MakeSQL(
                @"Select * From RECRUITEMENTPROCESS Where  RECRUITEMENTPROCESSID in (select ProcessId" +
                " from dbo.RecruitementBoardMember where EmployeeId=%n and StepId=%n )", empID, stepID);
            return tc.ExecuteReader(sql);
        }

        internal static void UpdateRecruitmentMark(RecruitmentMarkExtend recruitmentMarkExtend, TransactionContext tc)
        {
            string sql =
                SQLParser.MakeSQL(@"Update RECRUITEMENTMEMBERWISEMARK set CandidateDescription=%s,IsRecommended=%n, OtherComment = %s
                                  where candidateId=%n and InterviewSessionID=%n",
                    recruitmentMarkExtend.CandidateDescription, recruitmentMarkExtend.IsRecommend, recruitmentMarkExtend.OtherComment, recruitmentMarkExtend.CandidateId, recruitmentMarkExtend.SessionId);
            tc.ExecuteNonQuery(sql);
        }
    }
}