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

namespace HRM.DA
{
    internal class RecruitmentRequisitionDA
    {
        #region Get Functions

        public static IDataReader Get(TransactionContext tc, int empRequisitionID)
        {
            return tc.ExecuteReader("SELECT * from RECRUITMENTREQUISITION WHERE RecruitmentRequisitionID=%n", empRequisitionID);
        }

        public static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * from RECRUITMENTREQUISITION");
        }

        public static IDataReader GetByDepartmentID(TransactionContext tc, int departmentID)
        {
            return tc.ExecuteReader("SELECT * from EmployeeRequisition WHERE DepartmentID=%n", departmentID);
        }

        public static IDataReader GetRequisitionUserByReqID(TransactionContext tc, int reqID)
        {
            return tc.ExecuteReader("SELECT * from RequisitionUser WHERE EmpRequisitionID=%n", reqID);
        }

        #endregion

        #region Get Datasets

        internal static DataSet GetEmployeeRequisitionsByDepartmentID(TransactionContext tc, int departmentID,
            EnumEmpReqStatus reqStatus)
        {
            var empRequisitionDS = new DataSet();
            try
            {
                var sql = string.Empty;
                switch (reqStatus)
                {
                    case EnumEmpReqStatus.RM:
                        sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID ID, 
                                        '['+grd.Code+']-'+grd.Description GradeCodeName,empR.Position,
                                        empR.MaxEmployee,
                                        CONVERT(VARCHAR(30),empR.RequisitionDate,106) RequisitionDate,
                                        CASE 
	                                        When empR.Gender = 0
	                                        then 'Regardless'
                                            When empR.Gender = 1
	                                        then 'Male'
	                                        ELSE 'Female' 
                                        END Gender,
                                       CASE 
                                        When empR.Status = 0
                                        then 'Draft'
                                        When empR.Status = 1
                                        then 'Sent to Department Head'
                                        When empR.Status = 2 and empR.IsCompleted = 0
                                        then 'Sent to HR'
                                        When empR.Status = 2 and empR.IsCompleted = 1
                                        then 'Declined by HR'
                                        When empR.Status = 3 and empR.IsCompleted = 0
                                        then 'Send to CEO'
                                        When empR.Status = 3 and empR.IsCompleted = 1
                                        then 'Declined by CEO'
                                        ELSE 'Approved by CEO' 
                                        END Status
                                        FROM EmployeeRequisition empR,Grades grd
                                        WHERE empR.GradeID = grd.GradeID AND empR.DepartmentID=%n", departmentID);
                        break;
                    case EnumEmpReqStatus.DH:
                        sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID ID, 
                                        '['+grd.Code+']-'+grd.Description GradeCodeName,empR.Position,
                                        empR.MaxEmployee,
                                        CONVERT(VARCHAR(30),empR.RequisitionDate,106) RequisitionDate,
                                        CASE 
	                                        When empR.Gender = 0
	                                        then 'Regardless'
                                            When empR.Gender = 1
	                                        then 'Male'
	                                        ELSE 'Female' 
                                        END Gender,
                                       CASE 
                                        When empR.Status = 1
                                        then ' '
                                        When empR.Status = 2 and empR.IsCompleted = 0
                                        then 'Sent to HR'
                                        When empR.Status = 2 and empR.IsCompleted = 1
                                        then 'Declined by HR'
                                        When empR.Status = 3 and empR.IsCompleted = 0
                                        then 'Send to CEO'
                                        When empR.Status = 3 and empR.IsCompleted = 1
                                        then 'Declined by CEO'
                                        ELSE 'Approved by CEO' 
                                        END Status
                                        FROM EmployeeRequisition empR,Grades grd
                                        WHERE empR.GradeID = grd.GradeID AND
                                        empR.Status>0 And empR.DepartmentID=%n", departmentID);
                        break;
                    case EnumEmpReqStatus.Approved:
                        sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID ID, 
                                        '['+grd.Code+']-'+grd.Description GradeCodeName,empR.Position,
                                        empR.MaxEmployee,
                                        CONVERT(VARCHAR(30),empR.RequisitionDate,106) RequisitionDate,
                                        CASE 
	                                        When empR.Gender = 0
	                                        then 'Regardless'
                                            When empR.Gender = 1
	                                        then 'Male'
	                                        ELSE 'Female' 
                                        END Gender,
                                       CASE 
                                        When empR.Status = 1
                                        then 'Draft'
                                        When empR.Status = 2 and empR.IsCompleted = 0
                                        then 'Sent to HR'
                                        When empR.Status = 2 and empR.IsCompleted = 1
                                        then 'Declined by HR'
                                        When empR.Status = 3 and empR.IsCompleted = 0
                                        then 'Send to CEO'
                                        When empR.Status = 3 and empR.IsCompleted = 1
                                        then 'Declined by CEO'
                                        ELSE 'Approved by CEO' 
                                        END Status
                                        FROM EmployeeRequisition empR,Grades grd
                                        WHERE empR.GradeID = grd.GradeID AND
                                        empR.Status>0 And empR.Status=%n", (int)reqStatus);
                        break;
                }

                empRequisitionDS = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return empRequisitionDS;
        }

        internal static DataSet GetEmployeeRequisitionsForCEO(TransactionContext tc)
        {
            var empRequisitionDS = new DataSet();
            try
            {
                var sql = string.Empty;
                sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID ID,empR.DepartmentID,
                                         dpt.DESCRIPTION [Department],
                                        '['+grd.Code+']-'+grd.Description GradeCodeName,empR.Position,
                                        empR.MaxEmployee,
                                        CONVERT(VARCHAR(30),empR.RequisitionDate,106) RequisitionDate,
                                        CASE
                                            When empR.Gender = 0
	                                        then 'Regardless' 
	                                        When empR.Gender = 1
	                                        then 'Male'
	                                        ELSE 'Female' 
                                        END Gender,
                                       CASE 
                                        When empR.Status = 3 and empR.IsCompleted = 0
                                        then 'Sent by HR Head'
                                        When empR.Status = 3 and empR.IsCompleted = 1
                                        then 'Declined'
                                        ELSE 'Approved' 
                                        END Status
                                        FROM EmployeeRequisition empR,Grades grd,Department dpt
                                        WHERE empR.Status>2 And empR.GradeID = grd.GradeID 
                                        AND dpt.DEPARTMENTID = empR.DepartmentID
                                        Order By empR.DepartmentID");

                empRequisitionDS = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return empRequisitionDS;
        }

        internal static DataSet GetEmployeeRequisitionsForHR(TransactionContext tc)
        {
            var empRequisitionDS = new DataSet();
            try
            {
                var sql = string.Empty;
                sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID ID,empR.DepartmentID,
                                         dpt.DESCRIPTION Department,
                                        '['+grd.Code+']-'+grd.Description GradeCodeName,empR.Position,
                                        empR.MaxEmployee,
                                        CONVERT(VARCHAR(30),empR.RequisitionDate,106) RequisitionDate,
                                        CASE
                                            When empR.Gender = 0
	                                        then 'Regardless' 
	                                        When empR.Gender = 1
	                                        then 'Male'
	                                        ELSE 'Female' 
                                        END Gender,
                                       CASE 
                                        When empR.Status = 2 and empR.IsCompleted = 0
                                        then 'Sent by Department Head'
                                        When empR.Status = 2 and empR.IsCompleted = 1
                                        then 'Declined'
                                        When empR.Status = 3 and empR.IsCompleted = 0
                                        then 'Send to CEO'
                                        When empR.Status = 3 and empR.IsCompleted = 1
                                        then 'Declined by CEO'
                                        ELSE 'Approved by CEO' 
                                        END Status
                                        FROM EmployeeRequisition empR,Grades grd,Department dpt
                                        WHERE empR.Status>1 And empR.GradeID = grd.GradeID 
                                        AND dpt.DEPARTMENTID = empR.DepartmentID
                                        Order By empR.DepartmentID");

                empRequisitionDS = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return empRequisitionDS;
        }

        #endregion

        #region Insert Functions

        public static void Insert(TransactionContext tc, RecruitmentRequisition item)
        {
            var sql = SQLParser.MakeSQL(
                @"INSERT INTO dbo.RECRUITMENTREQUISITION (RecruitmentRequisitionID, DepartmentID, DepartmentHeadID, EmployeeID, ExpectedJoiningDate, 
                                            HRApprovedDate, MDApprovedDate, Position, GradeID, DesignationID, MaxEmployee, BudgetedHeadCount, BasicPay, JobDescription, Type, 
                                            RecruitmentMethod, KPI, FilePath, Justification, Academic, Technical, Age, Experience, Gender, Areas, ResignationDate, OtherRequirements, 
                                            Status, ReplacementDueTo, IsCompleted, IsHeadCount, CreatedBy, CreationDate, Remarks)
                                            VALUES (%n, %n, %n, %n, %d, %d, 
                                            %d, %s, %n, %n, %n, %n, %s, %s, %s, 
                                            %s, %s, %s, %s, %s, %s, %s, %s, %n, %s, %d, 
                                            %s, %n, %n, %n, %n, %n, %d, %s)",
                item.ID, item.DepartmentId, item.DepartmentHeadId, item.EmployeeId, item.ExpectedJoiningDate,
                item.HrApprovedDate,
                item.MDApprovedDate, item.Position, item.GradeId, item.DesignationId, item.MaxEmployee,
                item.BudgetedHeadCount, item.BasicPay,
                item.JobDescription, item.Type, item.RecruitmentMethod, item.KPI, item.FilePath, item.Justification,
                item.Academic, item.Technical,
                item.Age, item.Experience, item.Gender, item.Areas, item.ResignationDate, item.OtherRequirements,
                item.Status, item.ReplacementDueTo,
                item.IsCompleted, item.IsHeadCount, item.CreatedBy, item.CreatedDate, item.Remarks);
            tc.ExecuteNonQuery(sql);
        }

        public static void InsertRequisitionUser(TransactionContext tc, RequisitionUser oItem)
        {
            tc.ExecuteNonQuery(
                @"INSERT INTO dbo.RequisitionUser (RequisitionUserID, RecruitmentRequisitionID, EmployeeID, ProposedByID, ChangeDate, ChangeString, Remarks)
            VALUES (%n, %n, %n, %n, %d, %s, %s)", oItem.ID, oItem.RecruitmentRequisitionId, oItem.EmployeeId,
                oItem.Status, oItem.ChangeDate, oItem.ChangeString, oItem.Remarks);
        }

        #endregion

        #region Update Functions

        public static void Update(TransactionContext tc, RecruitmentRequisition item)
        {
            var sql = SQLParser.MakeSQL(
                @"UPDATE dbo.RecruitmentRequisition
                        SET DepartmentID = %n,
	                        DepartmentHeadID = %n,
	                        EmployeeID = %n,
	                        ExpectedJoiningDate = %d,
	                        HRApprovedDate = %d,
	                        MDApprovedDate = %d,
	                        Position = %s,
	                        GradeID = %n,
	                        DesignationID = %n,
	                        MaxEmployee = %n,
	                        BudgetedHeadCount = %n,
	                        BasicPay = %s,
	                        JobDescription = %s,
	                        Type = %s,
	                        RecruitmentMethod = %s,
	                        KPI = %s,
	                        FilePath = %s,
	                        Justification = %s,
	                        Academic = %s,
	                        Technical = %s,
	                        Age = %s,
	                        Experience = %s,
	                        Gender = %n,
	                        Areas = %s,
	                        ResignationDate = %d,
	                        OtherRequirements = %s,
	                        Status = %n,
	                        ReplacementDueTo = %n,
	                        IsCompleted = %n,
	                        IsHeadCount = %n,
	                        CreatedBy = %n,
	                        CreationDate = %d,
	                        Remarks = %s
	                        WHERE RecruitmentRequisitionID = %n", item.DepartmentId, item.DepartmentHeadId,
                item.EmployeeId, item.ExpectedJoiningDate,
                item.HrApprovedDate,
                item.MDApprovedDate, item.Position, item.GradeId, item.DesignationId, item.MaxEmployee,
                item.BudgetedHeadCount, item.BasicPay,
                item.JobDescription, item.Type, item.RecruitmentMethod, item.KPI, item.FilePath, item.Justification,
                item.Academic, item.Technical,
                item.Age, item.Experience, item.Gender, item.Areas, item.ResignationDate, item.OtherRequirements,
                item.Status, item.ReplacementDueTo,
                item.IsCompleted, item.IsHeadCount, item.CreatedBy, item.CreatedDate, item.Remarks, item.ID);
            tc.ExecuteNonQuery(sql);
        }

        internal static void ChangeStatus(TransactionContext tc, int empReqID, EnumEmpReqStatus enumEmpReqStatus)
        {
            string sql;
            if (enumEmpReqStatus == EnumEmpReqStatus.Approved)
                sql = SQLParser.MakeSQL(@"Update EmployeeRequisition
                                            Set Status =%n , IsCompleted = 1
                                            Where EmpRequisitionID=%n", (int)enumEmpReqStatus, empReqID);
            else
                sql = SQLParser.MakeSQL(@"Update EmployeeRequisition
                                            Set Status =%n 
                                            Where EmpRequisitionID=%n", (int)enumEmpReqStatus, empReqID);

            tc.ExecuteNonQuery(sql);
        }

        internal static void RequisitionDeclinedByHR(TransactionContext tc, RecruitmentRequisition employeeRequisition)
        {
            string sql;

            sql = SQLParser.MakeSQL(@"Update EmployeeRequisition
                                            Set Status =2 , IsCompleted = 1
                                            Where EmpRequisitionID=%n", employeeRequisition.ID);

            tc.ExecuteNonQuery(sql);
        }

        internal static void RequisitionDeclinedByCEO(TransactionContext tc, RecruitmentRequisition employeeRequisition)
        {
            string sql;

            sql = SQLParser.MakeSQL(@"Update EmployeeRequisition
                                            Set Status =3 , IsCompleted = 1
                                            Where EmpRequisitionID=%n", employeeRequisition.ID);

            tc.ExecuteNonQuery(sql);
        }

        #endregion

        #region Delete Functions

        public static void Delete(TransactionContext tc, int id)
        {
            tc.ExecuteNonQuery("Delete from RecruitmentRequisition where RecruitmentRequisitionID=%n", id);
        }

        public static void DeleteRequisitionUsers(TransactionContext tc, int empRequisitionID)
        {
            tc.ExecuteNonQuery("Delete from RequisitionUser Where RecruitmentRequisitionID = %n", empRequisitionID);
        }

        #endregion
    }
}