using System;
using System.Data;
using Ease.CoreV35.DataAccess;
using Ease.CoreV35.Model;
using Payroll.BO;


namespace Payroll.Service
{
    class EmployeeRequisitionDA
    {

        #region Get Functions

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

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

        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)
        {
            DataSet empRequisitionDS = new DataSet();
            try
            {
                string sql = string.Empty;
                switch (reqStatus)
                {
                    case EnumEmpReqStatus.RM:
                        sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID as 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 as 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 as [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 as 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 as 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 as [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 as 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 as 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 as [Status]
                                        FROM EmployeeRequisition empR,Grades grd
                                        WHERE empR.GradeID = grd.GradeID AND
                                        empR.[Status]>0 And empR.[Status]=%n", (int)reqStatus);
                        break;
                    default:
                        break;
                }
                empRequisitionDS = tc.ExecuteDataSet(sql);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return empRequisitionDS;
        }

        internal static DataSet GetEmployeeRequisitionsForCEO(TransactionContext tc)
        {
            DataSet empRequisitionDS = new DataSet();
            try
            {
                string sql = string.Empty;
                sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID as ID,empR.DepartmentID,
                                         dpt.DESCRIPTION as [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 as 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 as [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)
        {
            DataSet empRequisitionDS = new DataSet();
            try
            {
                string sql = string.Empty;
                sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID as ID,empR.DepartmentID,
                                         dpt.DESCRIPTION as [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 as 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 as [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, EmployeeRequisition oItem)
        {
            string sql = SQLParser.MakeSQL(@"INSERT INTO EmployeeRequisition
                                 (EmpRequisitionID,DepartmentID,RequisitionDate,Position,
                                  GradeID,MaxEmployee,BasicPay,JobDescription,
                                  Justification,Academic,Technical,Age,
                                  Experience,Gender,Areas,OtherRequirements,
                                  Status,IsCompleted,IsHeadCount,CreatedBy,CreationDate)" +
                                  @" VALUES(%n,%n,%d,%s,
                                            %n,%n,%s,%s,
                                            %s,%s,%s,%s,
                                            %s,%n,%s,%s,
                                            %n,%b,%b,%n,%d)",
                                  oItem.ID.Integer, oItem.DepartmentID.Integer, oItem.RequisitionDate, oItem.Position, oItem.GradeID.Integer, oItem.MaxEmployee, DataReader.GetNullValue(oItem.BasicPay), DataReader.GetNullValue(oItem.JobDescription),
  DataReader.GetNullValue(oItem.Justification), DataReader.GetNullValue(oItem.Academic), DataReader.GetNullValue(oItem.Technical), DataReader.GetNullValue(oItem.Age),
 DataReader.GetNullValue(oItem.Experience), (int)oItem.Gender, DataReader.GetNullValue(oItem.Areas), DataReader.GetNullValue(oItem.OtherRequirements),
 (int)oItem.Status, oItem.IsCompleted, oItem.IsHeadCount, oItem.CreatedBy.Integer, oItem.CreatedDate);
            tc.ExecuteNonQuery(sql);
        }

        public static void InsertRequisitionUser(TransactionContext tc, RequisitionUser oItem)
        {
            tc.ExecuteNonQuery("INSERT INTO RequisitionUser(RequisitionUserID,EmpRequisitionID,EmployeeID,Status,ChangeDate,ChangeString,Remarks)" +
             " VALUES(%n,%n,%n,%n,%d,%s,%s)", oItem.ID.Integer, oItem.EmpRequisitionID.Integer, oItem.EmployeeID.Integer, oItem.Status, oItem.ChangeDate, DataReader.GetNullValue(oItem.ChangeString), DataReader.GetNullValue(oItem.Remarks));

        }

        #endregion

        #region Update Functions

        public static void Update(TransactionContext tc, EmployeeRequisition oItem)
        {
            string sql = SQLParser.MakeSQL(@"Update EmployeeRequisition SET DepartmentID=%n,RequisitionDate=%d,Position=%s,GradeID=%n, MaxEmployee=%n,BasicPay=%s,JobDescription=%s,Justification=%s,Academic=%s,Technical=%s,Age=%s,Experience=%s,Gender=%n,Areas=%s,OtherRequirements=%s,Status=%n,IsCompleted=%b,IsHeadCount=%b,CreatedBy=%n,CreationDate=%d" +
          " Where EmpRequisitionID = %n", oItem.DepartmentID.Integer, oItem.RequisitionDate, oItem.Position, oItem.GradeID.Integer, oItem.MaxEmployee, DataReader.GetNullValue(oItem.BasicPay), DataReader.GetNullValue(oItem.JobDescription), DataReader.GetNullValue(oItem.Justification), DataReader.GetNullValue(oItem.Academic), DataReader.GetNullValue(oItem.Technical), DataReader.GetNullValue(oItem.Age), DataReader.GetNullValue(oItem.Experience), (int)oItem.Gender, DataReader.GetNullValue(oItem.Areas), DataReader.GetNullValue(oItem.OtherRequirements), (int)oItem.Status, oItem.IsCompleted, oItem.IsHeadCount, oItem.CreatedBy.Integer, oItem.CreatedDate, oItem.ID.Integer);
            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, EmployeeRequisition employeeRequisition)
        {
            string sql;
           
                sql = SQLParser.MakeSQL(@"Update EmployeeRequisition
                                            Set Status =2 , IsCompleted = 1
                                            Where EmpRequisitionID=%n",employeeRequisition.ID.Integer);
            
            tc.ExecuteNonQuery(sql);
        }

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

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

            tc.ExecuteNonQuery(sql);
        }

        #endregion

        #region Delete Functions

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

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

        #endregion


        
    }
}