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  CarFuelParameterDA
    public class CarFuelParameterDA
    {
        #region  Constructor
        public CarFuelParameterDA() { }
        #endregion

        #region  Insert function
        internal static void Insert(TransactionContext tc, CarFuelParameter oItem)
        {
            tc.ExecuteNonQuery("INSERT INTO CarFuelParameter("
                + " CarFuelParameterID, CarFuelType, AmountToDistribute, AvgMonth, "
                + " ProvisionAmount, FlatAmount, FromDate, ToDate, Gender, Status, "
                + " IsActive, IsEarnedBasic, IsTaxable, CarFuelItemID, "
                + " CarFuelPeriodicity, PercentOfBasic, PercentOfGross, MinAmount, "
                + " MaxAmount, IsConfirmed, IsFractionate, IsWithException, "
                + " EntitleType, CarFuelAvgPayType, CarFuelAvgPayItemID, NoOfMonth, "
                + " CreatedBy, CreationDate,PayrollTypeID)" +

                " VALUES(%n, %n, %n, %n, "
                + " %n, %n, %d, %d, %n, %n, "
                + " %b, %b, %b, %n, "
                + " %n, %n, %n, %n, "
                + " %n, %b, %b, %b, "
                + " %n, %n, %n, %n, "
                + " %n, %d,%n)",
 
                oItem.ID.Integer, oItem.CarFuelType, oItem.AmountToDistribute, oItem.AvgMonth, 
                oItem.ProvisionAmount, oItem.FlatAmount, DataReader.GetNullValue(oItem.FromDate), DataReader.GetNullValue(oItem.ToDate), oItem.Gender, oItem.Status, 
                oItem.IsActive, oItem.IsEarnedBasic, oItem.IsTaxable, oItem.CarFuelItemID.Integer, 
                oItem.CarFuelPeriodicity, oItem.PercentOfBasic, oItem.PercentOfGross, DataReader.GetNullValue(oItem.MinAmount), 
                DataReader.GetNullValue(oItem.MaxAmount), oItem.IsConfirmed, oItem.IsFractionate, oItem.IsWithException, 
                oItem.EntitleType, oItem.CarFuelAvgPayType, DataReader.GetNullValue(oItem.CarFuelAvgPayItemID, IDType.Integer), oItem.NoOfMonth,
                DataReader.GetNullValue(Payroll.BO.User.CurrentUser.ID, IDType.Integer), DataReader.GetNullValue(DateTime.Now), oItem.PayrollTypeID.Integer);
        }
        internal static void InsertGrade(TransactionContext tc, CarFuelParameterGrade oItem)
        {
            tc.ExecuteNonQuery("INSERT INTO CarFuelParameterGrade("
                + " GradeID, CarFuelParameterID, CarFuelItemId, "
                + " CreatedBy, CreationDate,PayrollTypeID)" 
                
                + " VALUES(%n, %n, %n, "
                + " %n, %d,%n)", 
                
                oItem.GradeId.Integer, oItem.CarFuelParameterId.Integer, oItem.CarFuelItemId.Integer,
                DataReader.GetNullValue(oItem.CreatedBy.Integer), DataReader.GetNullValue(oItem.CreatedDate),oItem.PayrollTypeID.Integer);
        }
        #endregion

        #region  Update function
        internal static void Update(TransactionContext tc, CarFuelParameter oItem)
        {
            tc.ExecuteNonQuery("UPDATE CarFuelParameter SET "
                + " CarFuelType=%n, AmountToDistribute=%n, AvgMonth=%n, ProvisionAmount=%n, FlatAmount=%n, "
                + " FromDate=%d, ToDate=%d, Gender=%n, Status=%n, IsActive=%b, "
                + " IsEarnedBasic=%b, IsTaxable=%b, CarFuelItemID=%n, CarFuelPeriodicity=%n, "
                + " PercentOfBasic=%n, PercentOfGross=%n, MinAmount=%n, MaxAmount=%n, "
                + " IsConfirmed=%b, IsFractionate=%b, IsWithException=%n, EntitleType=%n, "
                + " CarFuelAvgPayType=%n, CarFuelAvgPayItemID=%n, NoOfMonth=%n, "
                + " ModifiedBy=%n, ModifiedDate=%d WHERE CarFuelParameterID=%n AND PayrollTypeID=%n", 
                
                oItem.CarFuelType, oItem.AmountToDistribute, oItem.AvgMonth, oItem.ProvisionAmount, oItem.FlatAmount, 
                DataReader.GetNullValue(oItem.FromDate), DataReader.GetNullValue(oItem.ToDate), oItem.Gender, oItem.Status, oItem.IsActive, 
                oItem.IsEarnedBasic, oItem.IsTaxable, oItem.CarFuelItemID.Integer, oItem.CarFuelPeriodicity, 
                oItem.PercentOfBasic, oItem.PercentOfGross, oItem.MinAmount, oItem.MaxAmount, 
                oItem.IsConfirmed, oItem.IsWithException, oItem.IsFractionate, oItem.EntitleType, 
                oItem.CarFuelAvgPayType, DataReader.GetNullValue(oItem.CarFuelAvgPayItemID, IDType.Integer), oItem.NoOfMonth,
                DataReader.GetNullValue(Payroll.BO.User.CurrentUser.ID, IDType.Integer), DataReader.GetNullValue(DateTime.Now), oItem.ID.Integer, oItem.PayrollTypeID.Integer);
        }
        #endregion

        #region Get Function
        internal static IDataReader GetCFD(TransactionContext tc, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * FROM CarFuelParameter where PayrollTypeID=%n", payrollTypeID);
        }
        internal static IDataReader Get(TransactionContext tc, ID nID, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * FROM CarFuelParameter WHERE CarFuelParameterID =%n AND PayrollTypeID=%n", nID.Integer, payrollTypeID);
        }
        internal static IDataReader GetGrades(TransactionContext tc, ID nCarFuelParameterID, int payrollTypeID)
        {
            return tc.ExecuteReader("Select * from CarFuelParameterGrade Where CarFuelParameterID= %n AND PayrollTypeID=%n", nCarFuelParameterID.Integer, payrollTypeID);
        }
        internal static IDataReader Get(TransactionContext tc, EnumStatus status, int payrollTypeID)
        {
            //return tc.ExecuteReader("SELECT * FROM CarFuelParameter WHERE Status= %n", status);
            if (status == EnumStatus.Regardless)
                return tc.ExecuteReader("SELECT * FROM CarFuelParameter Where PayrollTypeID=%n", payrollTypeID);
            else
                return tc.ExecuteReader("SELECT CarFuelParameter.* FROM CarFuelParameter, CarFuelItem Where CarFuelItem.CarFuelItemID=CarFuelParameter.CarFuelItemID AND CarFuelParameter.Status=%n AND CarFuelParameter.PayrollTypeID=%n", status, payrollTypeID);
        }
        internal static IDataReader Get(TransactionContext tc, EnumStatus status, EnumCarFuelType nType, int payrollTypeID)
        {
            if (status == EnumStatus.Regardless)
                return tc.ExecuteReader("SELECT * FROM CarFuelParameter WHERE CarFuelType = %n AND PayrollTypeID=%n Order By CarFuelPeriodicity", nType, payrollTypeID);
            else
                return tc.ExecuteReader("SELECT * FROM CarFuelParameter WHERE  CarFuelType = %n AND Status=%n AND PayrollTypeID=%n Order By CarFuelPeriodicity", nType, status, payrollTypeID);
                //return tc.ExecuteReader("SELECT CarFuelParameter.* FROM CarFuelParameter, CarFuelItem Where CarFuelItem.CarFuelItemID=CarFuelParameter.CarFuelItemID AND CarFuelParameter.Status=%n", status);
        }

        internal static IDataReader Get(TransactionContext tc, EnumStatus enumStatus, EnumCarFuelType enumCarFuelType, EnumEntitleType enumEntitleType, int payrollTypeID)
        {
            if (enumStatus == EnumStatus.Regardless)
                return tc.ExecuteReader("SELECT * FROM CarFuelParameter WHERE CarFuelType = %n AND PayrollTypeID=%n AND EntitleType=%n Order By CarFuelPeriodicity", enumCarFuelType, payrollTypeID, enumEntitleType);
            else
                return tc.ExecuteReader("SELECT * FROM CarFuelParameter WHERE  CarFuelType = %n AND Status=%n AND PayrollTypeID=%n AND EntitleType=%n Order By CarFuelPeriodicity", enumCarFuelType, enumStatus, payrollTypeID, enumEntitleType);
            //return tc.ExecuteReader("SELECT CarFuelParameter.* FROM CarFuelParameter, CarFuelItem Where CarFuelItem.CarFuelItemID=CarFuelParameter.CarFuelItemID AND CarFuelParameter.Status=%n", status);
        }

        internal static IDataReader Get(TransactionContext tc, EnumStatus enumStatus, EnumEntitleType enumEntitleType, int payrollTypeID)
        {
            if (enumStatus == EnumStatus.Regardless)
                return tc.ExecuteReader("SELECT * FROM CarFuelParameter WHERE PayrollTypeID=%n AND EntitleType=%n Order By CarFuelPeriodicity", payrollTypeID, enumEntitleType);
            else
                return tc.ExecuteReader("SELECT * FROM CarFuelParameter WHERE Status=%n AND PayrollTypeID=%n AND EntitleType=%n Order By CarFuelPeriodicity", enumStatus, payrollTypeID, enumEntitleType);
            //return tc.ExecuteReader("SELECT CarFuelParameter.* FROM CarFuelParameter, CarFuelItem Where CarFuelItem.CarFuelItemID=CarFuelParameter.CarFuelItemID AND CarFuelParameter.Status=%n", status);
        }

        #endregion

        #region  Delete function
        internal static void Delete(TransactionContext tc, ID nID, int payrollTypeID)
        {
            tc.ExecuteNonQuery("DELETE FROM CarFuelParameterGrade WHERE CarFuelParameterID=%n AND PayrollTypeID=%n", nID.Integer, payrollTypeID);
            tc.ExecuteNonQuery("DELETE FROM CarFuelParameterIndividual WHERE CarFuelParameterID=%n AND PayrollTypeID=%n", nID.Integer, payrollTypeID);
            tc.ExecuteNonQuery("DELETE FROM CarFuelParameter WHERE CarFuelParameterID=%n AND PayrollTypeID=%n", nID.Integer, payrollTypeID);
        }
        internal static void DeleteGrades(TransactionContext tc, ID nCarFuelParameterID, int payrollTypeID)
        {
            tc.ExecuteNonQuery("DELETE FROM CarFuelParameterGrade WHERE CarFuelParameterID=%n AND PayrollTypeID=%n", nCarFuelParameterID.Integer, payrollTypeID);
        }
        #endregion

        internal static IDataReader GetGrades(TransactionContext tc, int payrollTypeID)
        {
            string sql = SQLParser.MakeSQL(@"Select * from CarFuelParameterGrade Where PayrollTypeID=%n", payrollTypeID);
            return tc.ExecuteReader(sql);
        }
    }
    #endregion
}