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


namespace HRM.DA
{
    public class CandidateDA
    {
        internal static void Insert(Candidate obCan, TransactionContext tc)
        {
            int? cvId = null;
            if (obCan.CvID != 0)
            {
                cvId = obCan.CvID;
            }

            int? empID = null;
            if (obCan.EmployeeId != 0)
            {
                empID = obCan.EmployeeId;
            }

            DateTime? strdate = null;

            if (obCan.StartDate == DateTime.MinValue) strdate = null;
            else
            {
                strdate = obCan.StartDate;
            }


            string sql = SQLParser.MakeSQL(
                @"Insert Into RECRUITEMENTCANDIDATE(StartTime,StartDate,CandidateID,ProcessId,
                         IsEmployee,CvId,EmployeeId,IsSelected,PrimarySelected) Values(%s,%d,%n,%n,%n,%n,%n,%n,%n)",
                DataReader.GetNullValue(obCan.StartTime), DataReader.GetNullValue(strdate), obCan.ID, obCan.ProcessId,
                Convert.ToInt32(obCan.IsEmployee), DataReader.GetNullValue(cvId), DataReader.GetNullValue(empID),
                Convert.ToInt32(obCan.IsSelected), Convert.ToInt32(obCan.PrimarySelected));
            tc.ExecuteNonQuery(sql);
        }

        internal static void Update(Candidate obCan, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Update RECRUITEMENTCANDIDATE Set ProcessId = %n,IsEmployee = %n,
                         CvId = %n,EmployeeId = %n ,IsSelected = %n Where CandidateID = %n", obCan.ProcessId,
                Convert.ToInt32(obCan.IsEmployee),
                obCan.CvID, obCan.EmployeeId, obCan.ID, Convert.ToInt32(obCan.IsSelected));
            tc.ExecuteNonQuery(sql);
        }

        internal static void UpdateSelection(Candidate obCan, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Update RECRUITEMENTCANDIDATE Set PrimarySelected = %b
                        Where CandidateID = %n",
               obCan.PrimarySelected, obCan.ID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void DiscardCandidate(int CandidateId, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"Update RECRUITEMENTCANDIDATE Set PrimarySelected = %b
                        Where CandidateID = %n",
               false, CandidateId);
            tc.ExecuteNonQuery(sql);
        }

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

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

        internal static void DeleteCVCandidate(TransactionContext tc, int cvID)
        {
            string sql = SQLParser.MakeSQL(@"Delete From RECRUITEMENTCANDIDATE Where CvID = %n", cvID);
            tc.ExecuteNonQuery(sql);
        }
        internal static void DeleteCandidateCVCollection(TransactionContext tc, int candidateID)
        {
            string sql = SQLParser.MakeSQL(@"Delete From RECRUITEMENTCANDIDATE Where CandidateID = %n", candidateID);
            tc.ExecuteNonQuery(sql);
        }

        internal static IDataReader GetCanditates(TransactionContext tc, int processID)
        {
            string sql = SQLParser.MakeSQL(@"Select * From RECRUITEMENTCANDIDATE Where ProcessId = %n", processID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetCanditatebyCvID(TransactionContext tc, int cvID,int recruitmentId)
        {
            string sql = SQLParser.MakeSQL(@"Select * From RECRUITEMENTCANDIDATE Where cvID = %n and ProcessId=%n", cvID, recruitmentId);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetCanditatesbyCvID(TransactionContext tc, string cvIDs)
        {
            string sql = SQLParser.MakeSQL(@"Select * From RECRUITEMENTCANDIDATE Where cvID in (%q)", cvIDs);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetCanditateCvID(TransactionContext tc, int cvID, int currentrecruitmentId)
        {
            string sql = SQLParser.MakeSQL(@"Select * From RECRUITEMENTCANDIDATE Where cvID= %n and ProcessId<>%n", cvID, currentrecruitmentId);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetCanditates(TransactionContext tc, int processID, int assessmentStatus,
            int logInID)
        {
            string sql = SQLParser.MakeSQL(@"Select * From RECRUITEMENTCANDIDATE Where ProcessId = %n", processID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader Get(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"SELECT * FROM RECRUITEMENTCANDIDATE");
            return tc.ExecuteReader(sql);
        }
    }
}