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


namespace HRM.DA
{
    class ProdBonusProcessDA
    {
        #region Get function
        internal static IDataReader Get(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM ProdBonusProcessEmp");
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetAllProdBonusProcessEmps(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM ProdBonusProcessEmp");
            return tc.ExecuteReader(sql);
        }
        internal static IDataReader GetProdBonusProcessEmp(TransactionContext tc, int nID)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM ProdBonusProcessEmp Where ProdBonusProcessID = %n", nID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetAllProdBonusProcessItems(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM ProdBonusProcessItem");
            return tc.ExecuteReader(sql);
        }
        internal static IDataReader GetProdBonusProcessItem(TransactionContext tc, int nID)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM ProdBonusProcessItem Where ProdBonusProcessID = %n", nID);
            return tc.ExecuteReader(sql);
        }
        internal static IDataReader Get(TransactionContext tc, DateTime dtSalaryMonth)
        {
            string sql = SQLParser.MakeSQL("select * from ProdBonusProcess where SalaryMonth=%d", dtSalaryMonth);
            return tc.ExecuteReader(sql);
        }
        internal static DataSet GetDetailItems(TransactionContext tc, DateTime dtSalaryMonth)
        {
            //            string sql = SQLParser.MakeSQL(@"SELECT emp.EMPLOYEENO, pbl.LineName, pbpi.*
            //                                            FROM ProdBonusProcess pbp,ProdBonusProcessItem pbpi,ProdBonusLine pbl,Employee emp
            //                                            WHERE pbp.ProdBonusProcessID=pbpi.ProdBonusProcessID
            //                                            AND pbp.SalaryMonth=%d
            //                                            AND pbpi.ProdBonusLineID=pbl.ProdBonusLineID
            //                                            AND pbpi.EmployeeID=emp.EMPLOYEEID", dtSalaryMonth);

            string sql = SQLParser.MakeSQL(@"SELECT emp.EMPLOYEENO, pbpe.*
                                            FROM ProdBonusProcess pbp,ProdBonusProcessEmp pbpe
                                            ,Employee emp
                                            WHERE pbp.ProdBonusProcessID=pbpe.ProdBonusProcessID
                                            AND pbpe.EmployeeID=emp.EMPLOYEEID
                                            AND pbp.SalaryMonth=%d", dtSalaryMonth);
            return tc.ExecuteDataSet(sql);
        }
        internal static IDataReader GetByPMPYear(TransactionContext tc, int nPmpyearID)
        {
            string sql =
                SQLParser.MakeSQL(
                    "select * from ProdBonusProcess where objectivecategoryid in(select objectivecategoryid from objectivecategory where  pmpyearid=%n)",
                    nPmpyearID);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader Get(TransactionContext tc, int id)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM ProdBonusProcess WHERE ProdBonusProcessID = %n", id);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetProdBonusProcess(TransactionContext tc, int iD)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM ProdBonusProcess WHERE ObjectiveCategoryID = %n", iD);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetProdBonusEmp(TransactionContext tc, int Id, DateTime salaryMonth)
        {
            string sql = SQLParser.MakeSQL(@"SELECT pbpe.* 
                                             FROM 
                                             ProdBonusProcess pbp,
                                             ProdBonusProcessEmp pbpe
                                             WHERE pbp.ProdBonusProcessID = pbpe.ProdBonusProcessID
                                             AND pbp.SalaryMonth = %d
                                             AND pbpe.EmployeeID = %n", salaryMonth.PayrollLastDateOfMonth(), Id);
            return tc.ExecuteReader(sql);
        }
        #endregion

        #region Insert function
        internal static void Save(TransactionContext tc, ProdBonusProcess item)
        {
            string sql =
                SQLParser.MakeSQL(
                    "Insert Into ProdBonusProcess(ProdBonusProcessID, SalaryMonth, ProcessDate) Values(%n, %d, %d)",
                    item.ID, item.SalaryMonth, item.ProcessDate);
            tc.ExecuteNonQuery(sql);
        }

        internal static void SaveEmp(TransactionContext tc, ProdBonusProcessEmp item)
        {
            string sql = SQLParser.MakeSQL(
                "Insert Into ProdBonusProcessEmp(ProdBonusProcessEmpID,ProdBonusProcessID,Description, EmployeeID, Amount) Values(%n,%n,%s, %n, %n)",
                item.ID, item.ProdBonusProcessID, item.Description, item.EmployeeID, item.Amount);
            tc.ExecuteNonQuery(sql);
        }

        internal static void SaveItem(TransactionContext tc, ProdBonusProcessItem item)
        {
            string sql = SQLParser.MakeSQL(
                "Insert Into ProdBonusProcessItem(ProdBonusProcessItemID,ProdBonusProcessID, ProdBonusSetupID,ProdBonusLineID,EmployeeID, Amount,OTHour,AchievePercent,WorkingHour) Values(%n,%n, %n, %n,%n, %n,%n, %n,%n)",
                item.ID, item.ProdBonusProcessID, item.ProdBonusSetupID, item.ProdBonusLineID, item.EmployeeID,
                item.Amount, item.OTHour, item.AchievePercent, item.WorkingHour);
            tc.ExecuteNonQuery(sql);
        }

        #endregion

        #region Update function
        internal static void Update(TransactionContext tc, ProdBonusProcess item)
        {
            string sql =
                SQLParser.MakeSQL(
                    "Update ProdBonusProcess Set SalaryMonth = %d, ProcessDate = %d Where  ProdBonusProcessID = %n",
                    item.SalaryMonth, item.ProcessDate, item.ID);
            tc.ExecuteNonQuery(sql);
        }
        #endregion

        #region Delete function
        internal static void Delete(TransactionContext tc, int id)
        {
            string sql = SQLParser.MakeSQL("Delete From ProdBonusProcess Where ProdBonusProcessID = %n", id);
            tc.ExecuteNonQuery(sql);
        }

        internal static void DeleteBySalaryMonth(TransactionContext tc, DateTime dSalaryMonth)
        {
            string sql =
                SQLParser.MakeSQL(
                    "Delete From ProdBonusProcessItem Where ProdBonusProcessID =(select ProdBonusProcessID from ProdBonusProcess where Month(SalaryMonth)=%n AND Year(SalaryMonth)=%n)",
                    dSalaryMonth.Month, dSalaryMonth.Year);
            tc.ExecuteNonQuery(sql);
            sql = SQLParser.MakeSQL(
                "Delete From ProdBonusProcessEmp Where ProdBonusProcessID =(select ProdBonusProcessID from ProdBonusProcess where Month(SalaryMonth)=%n AND Year(SalaryMonth)=%n)",
                dSalaryMonth.Month, dSalaryMonth.Year);
            tc.ExecuteNonQuery(sql);
            sql = SQLParser.MakeSQL("Delete From ProdBonusProcess where Month(SalaryMonth)=%n AND Year(SalaryMonth)=%n",
                dSalaryMonth.Month, dSalaryMonth.Year);
            tc.ExecuteNonQuery(sql);
        }

        #endregion

    }
}