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

namespace HRM.DA
{
    internal class EmployeeConfirmationDA
    {
        #region Get

        internal static IDataReader Get(TransactionContext tc, int ID)
        {
            return tc.ExecuteReader("SELECT * FROM EmpConfirmation where EmpConfirmID=%n", ID);
        }

        internal static IDataReader GetByEmployeeID(TransactionContext tc, int ID)
        {
            return tc.ExecuteReader("SELECT * FROM EmpConfirmation where EmployeeID=%n", ID);
        }

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM EmpConfirmation");
        }
        internal static DataTable GetUpComingConfirmation(TransactionContext tc, DateTime Confirmmonth)
        {
            return tc.ExecuteDataTable(@"select a.*, b.EmpConfirmID, 0 month, b.Month prvExteded, a.CONFIRMMONTHDURATION confirmDuration, b.ConfirmDate from (
                        select e.EMPLOYEEID, e.EMPLOYEENO, g.confirmMonthDuration, e.NAME, e.JOININGDATE, 
                         DATEDIFF(mm, e.JOININGDATE, GetDate()) --+1 
                         duration ,
                        ( select lm.employeeno +' ' + lm.name from employee lm where lm.employeeid = e.linemanagerid) lineManager
                          from Employee e, GRADES g where e.GRADEID = g.GRADEID
                        and e.STATUS =1 and DATEDIFF(mm, e.JOININGDATE, %d) +1 > g.confirmMonthDuration
                        and g.confirmMonthDuration !=0 and e.DATEOFCONFIRMATION is null ) a
                        left join (
                        select c.EmpConfirmID, c.EmployeeID, c.Month, c.ConfirmDate from EmpConfirmation c, EMPLOYEE e where c.EmployeeID = e.EMPLOYEEID
                        and e.DATEOFCONFIRMATION is null and e.STATUS =1) b on a.EMPLOYEEID = b.EmployeeID", Confirmmonth);
        }
        internal static DataTable GetByDateRange(TransactionContext tc, DateTime fromDate, DateTime toDate, int payrollTypeId)
        {
            DataSet ds = tc.ExecuteDataSet(@"SELECT  TConf.EmpConfirmID,TConf.EmployeeID,TConf.[Month],
	                                Convert(varchar(20),TConf.ConfirmDate,106) ConfirmDate,
	                                TConf.CreatedBy,Convert(varchar(20),TConf.CreationDate,106) CreationDate,
	                                TConf.ModifiedBy,Convert(varchar(20),TConf.ModifiedDate,106) ModifiedDate,
	                                TEmp.EMPLOYEENO,TEmp.NAME As EmpName,
	                                Convert(varchar(20), TEmp.JOININGDATE,106) JOININGDATE  
                                    FROM 
                                    (SELECT * FROM EmpConfirmation
                                    Where ConfirmDate between %d and %d) AS TConf
                                    INNER JOIN 
                                    (SELECT * FROM EMPLOYEE
                                    WHERE STATUS = %n AND ISCONFIRMED = 0 AND PAYROLLTYPEID = %n) AS TEmp
                                    ON TEmp.EMPLOYEEID = TConf.EmployeeID", fromDate, toDate, (int)EnumEmployeeStatus.Live, payrollTypeId);

            return ds.Tables[0];
        }

        internal static void DeleteByEmployeeID(TransactionContext tc, int ID)
        {
            tc.ExecuteNonQuery("Delete FROM EmpConfirmation where EmployeeID=%n", ID);
        }

        #endregion

        #region Insert & Update

        internal static void Insert(TransactionContext tc, EmployeeConfirmation item)
        {
            string sSQL = SQLParser.MakeSQL(
                "INSERT INTO EmpConfirmation(EmpConfirmID, EmployeeID, Month, ConfirmDate, CreatedBy,CreationDate)" +
                " VALUES(%n, %n, %n, %d , %n, %d)", item.ID, item.EmployeeID, item.Month, item.ConfirmDate,
                DataReader.GetNullValue(item.CreatedBy), DataReader.GetNullValue(item.CreatedDate));
            tc.ExecuteNonQuery(sSQL);
        }

        internal static void Update(TransactionContext tc, EmployeeConfirmation item)
        {
            string sSQL = SQLParser.MakeSQL(
                "UPDATE EmpConfirmation SET EmployeeID=%n, Month=%n, ConfirmDate=%d, ModifiedBy=%n, ModifiedDate=%d" +
                " WHERE EmpConfirmID=%n", item.EmployeeID, item.Month, item.ConfirmDate,
                DataReader.GetNullValue(item.ModifiedBy), DataReader.GetNullValue(item.ModifiedDate), item.ID);
            tc.ExecuteNonQuery(sSQL);
        }

        #endregion

        #region Delete

        internal static void Delete(TransactionContext tc, int id)
        {
            tc.ExecuteNonQuery("Delete FROM EmpConfirmation where EmpConfirmID=%n", id);
        }

        #endregion
    }
}