using System;
using Payroll.BO;
using System.Data;
using System.Linq;
using Ease.CoreV35.Model;
using System.Data.SqlClient;
using Ease.CoreV35.DataAccess;
using System.Collections.Generic;
using Ease.CoreV35.DataAccess.SQL;

namespace Payroll.Service
{
    #region  BonusDA

    internal class BonusParameterDA
    {
        #region  Constructor

        private BonusParameterDA() { }

        #endregion

        #region  Insert function

        internal static void Insert(TransactionContext tc, BonusParameter item)
        {
            tc.ExecuteNonQuery("INSERT INTO BONUSPARAMETER(BonusParameterID,  BONUSID, FLATAMOUNT, NOOFBASIC, NOOFDAYS,NOOFGROSS,PERCENTOFGROSS,GROSSOFDAYS,PAYROLLTYPEID, NoOfEarnedBasic,ConfimRequired, CreatedBy, CreationDate, SequenceNo, Status,NoOfDisbusement,IsFestival,EligibleCuttoffDate,IsProrated,IsOverYearCalculation,TaxProjectInMonth,PerformanceBonusPercent,Q1,Q2,Q3,Q4)" +
            " VALUES(%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%d,%n,%n,%n,%n,%n,%b,%b,%n,%n,%n,%n,%n,%n)", item.ID.Integer, item.BonusID.Integer, item.FlatAmount, item.NoOfBasic, item.NoOfDays, item.NoOfGross, item.PercentOfGross, item.GrossOfDays, item.PayrollTypeID.Integer, item.NoOfEarnedBasic, item.ConfirmeRequired, item.CreatedBy.Integer, item.CreatedDate, item.Sequence, EnumStatus.Active, item.NoOfDisbusement, item.IsFestival, item.EligibleCuttoffDate, item.IsProrated, item.IsOverYearCalculation, item.TaxProjectInMonth,item.PerformanceBonusPercent,item.Q1,item.Q2,item.Q3,item.Q4);
        }

        internal static void Insert(TransactionContext tc, BonusParameter.BonusServiceLength item)
        {
            tc.ExecuteNonQuery("INSERT INTO BonusServiceLength(BonusParameterID,LengthOfService,EntitlePercent,BonusSLengthID)" +
                " VALUES(%n, %n,%n,%n)", item.BonusParameterID.Integer, item.LengthOfService,item.EntitlePercent,item.ID.Integer);
        }

        internal static void Insert(TransactionContext tc, BonusParameter.BonusSlab item)
        {
            tc.ExecuteNonQuery("INSERT INTO BonusSlab(BONUSPARAMETERID,SlabType,SlabAmount,Amount,AmountType,BonusSlabID)" +
                " VALUES(%n, %n,%n,%n,%n,%n)", item.BonusParameterID.Integer, item.SlabType, item.SlabAmount,item.Amount,item.AmountType,item.ID.Integer);
        }

        internal static void Insert(TransactionContext tc, BonusParameter.BonusParamSalaryItem item)
        {
            tc.ExecuteNonQuery("INSERT INTO BonusParamSalaryItem(BonusParameterID,AllowDeductID,[Percent],SalaryGroupCode,NoOfSalary,BonusParamSalaryItemID)" +
                " VALUES(%n, %n,%n,%n,%n,%n)", item.BonusParameterID.Integer, item.AllowDeductID, item.Percent,item.SalaryGroupCode,item.NoOfSalary,item.ID.Integer);
        }

        internal static void Insert(TransactionContext tc, BonusParameter.BonusParamAdjustItem item)
        {
            tc.ExecuteNonQuery("INSERT INTO BonusParamAdjustItem(BonusParameterID,Amount,BonusParamAdjustItemID,BonusAdjustItemID)" +
                " VALUES(%n, %n,%n,%n)", item.BonusParameterID.Integer, item.Amount, item.ID.Integer,item.BonusAdjustItemID);
        }

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, BonusParameter item)
        {
            tc.ExecuteNonQuery("UPDATE BONUSPARAMETER SET  BONUSID=%n, flatAmount=%n, noOfBasic=%n, noOfDays=%n,NOOFGROSS=%n,PERCENTOFGROSS=%n,GROSSOFDAYS=%n, payrollTypeID=%n, noOfEarnedBasic=%n,ConfimRequired=%n, ModifiedBy=%n, ModifiedDate=%d, SequenceNo=%n, Status=%n,NoOfDisbusement=%n,IsFestival=%n,EligibleCuttoffDate=%n,IsProrated=%b,IsOverYearCalculation=%b,TaxProjectInMonth=%n,PerformanceBonusPercent=%n,Q1=%n,Q2=%n,Q3=%n,Q4=%n" +
            " WHERE BonusParameterID=%n", item.BonusID.Integer, item.FlatAmount, item.NoOfBasic, item.NoOfDays, item.NoOfGross, item.PercentOfGross, item.GrossOfDays, item.PayrollTypeID.Integer, item.NoOfEarnedBasic, item.ConfirmeRequired, item.ModifiedBy.Integer, item.ModifiedDate, item.Sequence, EnumStatus.Active, item.NoOfDisbusement, item.IsFestival, item.EligibleCuttoffDate, item.IsProrated, item.IsOverYearCalculation,item.TaxProjectInMonth,item.PerformanceBonusPercent,item.Q1,item.Q2,item.Q3,item.Q4, item.ID.Integer);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc, EnumStatus status, ID PayrollTypeID)
        {
            if (EnumStatus.Regardless  != status)
            {
                return tc.ExecuteReader("SELECT * FROM BONUSPARAMETER Where Status=%n AND PayrollTypeID=%n Order By SequenceNo", status, PayrollTypeID.Integer);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM BONUSPARAMETER Where PayrollTypeID=%n  Order By SequenceNo", PayrollTypeID.Integer);
            }


        }

        internal static IDataReader Get(TransactionContext tc, ID nBonusParamID)
        {
            return tc.ExecuteReader("SELECT * FROM BONUSPARAMETER WHERE BONUSPARAMETERID=%n", nBonusParamID.Integer);
        }

        internal static IDataReader GetByBonusID(TransactionContext tc, ID nBonusID, ID PayrollTypeID)
        {
            return tc.ExecuteReader("SELECT * FROM BONUSPARAMETER WHERE BonusID=%n AND PayrollTypeID=%n", nBonusID.Integer, PayrollTypeID.Integer);
        }

        internal static IDataReader GetSerLengthByBonus(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader("Select * from BonusServiceLength Where BonusParameterID = %n", nID.Integer);
        }

        internal static IDataReader GetSlabByBonus(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader("Select * from BonusSlab Where BonusParameterID = %n", nID.Integer);
        }

        internal static IDataReader GetSalaryItemByBonus(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader("Select * from BonusParamSalaryItem Where BonusParameterID = %n", nID.Integer);
        }

        internal static IDataReader GetAdjustItemByBonus(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader("Select * from BonusParamAdjustItem Where BonusParameterID = %n", nID.Integer);
        }

        internal static DataSet GetByBonusIDAndTranType(TransactionContext tc, string bonusIDs, EnmSetupManagerTranType tranType)
        {
            string sql = string.Format(@"Select BP.*, BSD.TranID From BONUSPARAMETER As BP
                                            Left Join BonusSetupDetail BSD On BSD.SetupID = BP.BonusParameterID
                                            Where BP.BONUSID IN ({0}) AND BSD.TranType = {1}
                                            Order By BP.CreationDate Desc", bonusIDs, (int)tranType);

            return tc.ExecuteDataSet(sql);
        }
        #endregion

        #region  Delete function

        internal static void Delete(TransactionContext tc, ID nID)
        {
            tc.ExecuteNonQuery("UPDATE BONUSPARAMETER SET CreatedBy=%n,ModifiedBy=%n  Where BonusParameterID=%n", Payroll.BO.User.CurrentUser.ID.Integer, Payroll.BO.User.CurrentUser.ID.Integer, nID.Integer);
            tc.ExecuteNonQuery("DELETE FROM BonusParamAdjustItem WHERE BonusParameterID=%n", nID.Integer);
            tc.ExecuteNonQuery("DELETE FROM BonusServiceLength WHERE BonusParameterID=%n", nID.Integer);
            tc.ExecuteNonQuery("DELETE FROM BonusParamSalaryItem WHERE BonusParameterID=%n", nID.Integer);
            tc.ExecuteNonQuery("DELETE FROM BONUSPARAMETER WHERE BonusParameterID=%n", nID.Integer);
        }

        internal static void DeleteBonusSLength(TransactionContext tc, ID nID)
        {
            tc.ExecuteNonQuery("DELETE FROM BonusServiceLength WHERE BonusParameterID=%n", nID.Integer);
        }

        internal static void DeleteBonusSlab(TransactionContext tc, ID nID)
        {
            tc.ExecuteNonQuery("DELETE FROM BonusSlab WHERE BonusParameterID=%n", nID.Integer);
        }

        internal static void DeleteBonusSalaryItem(TransactionContext tc, ID nID)
        {
            tc.ExecuteNonQuery("DELETE FROM BonusParamSalaryItem WHERE BonusParameterID=%n", nID.Integer);
        }

        internal static void DeleteBonusAdjustItem(TransactionContext tc, ID nID)
        {
            tc.ExecuteNonQuery("DELETE FROM BonusParamAdjustItem WHERE BonusParameterID=%n", nID.Integer);
        }
        #endregion
    }

    #endregion
}