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.Basic.DA
{
    class ChildrenEducationDA
    {
        #region Constructor
        public ChildrenEducationDA()
        { 
        
        }
        #endregion

        #region Insert Function
        internal static void Insert(TransactionContext tc,ChildrenEducation item)
        {

            tc.ExecuteNonQuery("Insert Into ChildrenEducation (ChildrenEducationID,FromMonth,NoofChildren,Amount,CreatedBy,CreatedDate,EmployeeID )" +
                "Values (%n,%d,%n,%n,%n,%d,%n )", item.ID.Integer, item.FromMonth, item.NoOfChildren, item.Amount, item.CreatedBy.Integer, item.CreatedDate, item.EmployeeID.Integer);
        }

        #endregion

        #region Update Function
        internal static void Update(TransactionContext tc, ChildrenEducation item)
        {
            tc.ExecuteNonQuery("Update ChildrenEducation set FromMonth=%d, NoofChildren=%n, Amount=%n, CreatedBy=%n, CreatedDate=%d, EmployeeID=%n Where ChildrenEducationID=%n",item.FromMonth ,item .NoOfChildren ,item.Amount ,item.CreatedBy.Integer,item .CreatedDate , item.EmployeeID.Integer, item.ID.Integer );
        }

        internal static void UpdateByEmpID(TransactionContext tc, ChildrenEducation item)
        {
            tc.ExecuteNonQuery("Update ChildrenEducation set FromMonth=%d, NoofChildren=%n, Amount=%n Where EmployeeID=%n", item.FromMonth, item.NoOfChildren, item.Amount, item.EmployeeID.Integer);
        }

        #endregion

        #region Delete Function
        internal static void Delete(TransactionContext tc, ID id)
        {

            tc.ExecuteNonQuery("Delete from ChildrenEducation where ChildrenEducationID=%n",id .Integer );
        }

        internal static void Delete(TransactionContext tc, ChildrenEducation item)
        {

            tc.ExecuteNonQuery("Delete from ChildrenEducation where EmployeeID=%n and Month(FromMonth)=%n and  Year(FromMonth)=%n", item.EmployeeID.Integer,item.FromMonth.Month,item.FromMonth.Year);
        }

        internal static void DeleteByEmpID(TransactionContext tc, ID employeeID)
        {

            tc.ExecuteNonQuery("Delete from ChildrenEducation where EmployeeID=%n", employeeID.Integer);
        }

        #endregion
        #region Get Function

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

        internal static IDataReader Get(TransactionContext tc, DateTime date)
        {
            DateTime startDate=GlobalFunctions.FirstDateOfMonth(date);
            DateTime endDate=GlobalFunctions.LastDateOfMonth(date);

            string sql = SQLParser.MakeSQL("SELECT * FROM ChildrenEducation WHERE FromMonth Between %d AND %d", startDate, endDate);
            return tc.ExecuteReader(sql);
        
        }

        internal static IDataReader Get(TransactionContext tc)
        {
          
            return tc.ExecuteReader("Select * from ChildrenEducation");
        }

        internal static bool IsExist(TransactionContext tc, ChildrenEducation childrenEducation)
        {
            object ob = tc.ExecuteScalar("SELECT COUNT(*) FROM ChildrenEducation WHERE ChildrenEducationID=%n", childrenEducation.ChildrenEducationID .Integer );
            return Convert.ToInt32(ob) > 0;
        }
        internal static bool IsExist(TransactionContext tc, ID EmployeeID)
        {
            object ob = tc.ExecuteScalar("SELECT COUNT(*) FROM ChildrenEducation WHERE EmployeeID=%n", EmployeeID.Integer);
            return Convert.ToInt32(ob) > 0;
        }

        internal static bool IsExist(TransactionContext tc, ID EmployeeID, DateTime fromMonth)
        {
            object ob = tc.ExecuteScalar("SELECT COUNT(*) FROM ChildrenEducation WHERE EmployeeID=%n and FromMonth=%d", EmployeeID.Integer, fromMonth);
            return Convert.ToInt32(ob) > 0;
        }


        #endregion
    }
}