CEL_Payroll/Payroll.Service/Leave/DA/LeavePlanDA.cs

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

        #region ID Generation function
        public static int GetNewID(TransactionContext tc)
        {
            return tc.GenerateID("LeavePlan", "LeaveID");
        }
        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc, int empID)
        {
            return tc.ExecuteReader("SELECT * from LeavePlan WHERE EMPLOYEEID=%n", empID);
        }

        internal static IDataReader GetAllData(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * from LeavePlan ");
        }

        internal static IDataReader GetID(TransactionContext tc, int id)
        {
            return tc.ExecuteReader("SELECT * from LeavePlan WHERE LeavePlanID=%n", id);
        }

       

        internal static IDataReader GetPlanData(TransactionContext tc, int empID, int leaveYear)
        {
            string sSql = SQLParser.MakeSQL("SELECT * from LeavePlan WHERE EMPLOYEEID=%n AND LeaveYear=%n", empID, leaveYear);
            return tc.ExecuteReader(sSql);
        }

        internal static IDataReader GetByMonthRange(TransactionContext tc, int empID, DateTime dFromDate, DateTime dTodate)
        {
            string sSql = SQLParser.MakeSQL("Select * from LeavePlan where PlanFromDate between %d and %d and EmployeeID=%n order by PlanFromDate", dFromDate, dTodate, empID);
            return tc.ExecuteReader(sSql);
        }

        internal static bool IsEmpIDExist(int empID, int leaveYear, TransactionContext tc)
        {
            string str = SQLParser.MakeSQL("SELECT Count(*) from LeavePlan  where [EMPLOYEEID]=%n AND [LeaveYear]=%n ", empID, leaveYear);
            object ob = tc.ExecuteScalar(str);
            if (Convert.ToInt32(ob) > 0)
                return true;
            else
                return false;
        }

        internal static bool IsEmpApproved(int empID, int leaveYear, DateTime FormDate, DateTime ToDate, bool isApprove, TransactionContext tc)
        {
            int isApproved = 0;
            if (isApprove == true)
            {
                isApproved = 1;
            }
            //string str = SQLParser.MakeSQL("SELECT Count(*) from LeavePlan  where [EMPLOYEEID]=%n AND [LeaveYear]=%n"+
            //            " AND [PlanFromDate]=%d AND [PlanToDate]=%d AND [IsApproved]=%n", empID, leaveYear, FormDate, ToDate, isApproved);

            string str = SQLParser.MakeSQL("SELECT Count(*) from LeavePlan  where [EMPLOYEEID]=%n AND [LeaveYear]=%n" +
                        "AND [IsApproved]=%n", empID, leaveYear, isApproved);

            object ob = tc.ExecuteScalar(str);
            if (Convert.ToInt32(ob) > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region InsertFunction

        internal static void Insert(TransactionContext tc, LeavePlan oLeavePlan)
        {
            string sSql = SQLParser.MakeSQL("INSERT INTO LeavePlan(LeavePlanID, EMPLOYEEID, LeaveId,LeaveYear, PlanFromDate," +
                               " PlanToDate,PlanTotalDays,EntryDate,IsApproved,IsSubmitted)" +
                               " VALUES(%n,%n,%n,%n,%d,%d,%n,%d,%n,%n)",
            oLeavePlan.ID.Integer, oLeavePlan.EmpID, DataReader.GetNullValue(oLeavePlan.LeaveID.Integer), oLeavePlan.LeaveYear,
            DataReader.GetNullValue(oLeavePlan.PlanFromDate), DataReader.GetNullValue(oLeavePlan.PlanToDate), oLeavePlan.PlanTotalDays,
            DataReader.GetNullValue(oLeavePlan.EntryDate), Convert.ToInt32(oLeavePlan.IsApproved), Convert.ToInt32(oLeavePlan.IsSubmitted));
            tc.ExecuteNonQuery(sSql);
        }
        #endregion

        #region Update Function

        internal static void Update(TransactionContext tc, LeavePlan oLeavePlan)
        {
            tc.ExecuteNonQuery("UPDATE LeavePlan SET [EMPLOYEEID]=%n,[LeaveId]=%n,[LeaveYear]=%n,[PlanFromDate]=%d," +
                              " [PlanToDate]=%d,[PlanTotalDays]=%n,[EntryDate]=%d,[IsApproved]=%n where [LeavePlanID]=%n", +
                              oLeavePlan.EmpID, DataReader.GetNullValue(oLeavePlan.LeaveID.Integer),
                              oLeavePlan.LeaveYear, DataReader.GetNullValue(oLeavePlan.PlanFromDate),
                              DataReader.GetNullValue(oLeavePlan.PlanToDate), oLeavePlan.PlanTotalDays,
                              DataReader.GetNullValue(oLeavePlan.EntryDate),
                              Convert.ToInt32(oLeavePlan.IsApproved), oLeavePlan.ID.Integer);
        }

        internal static void UpdateApprove(TransactionContext tc, int empID, int leaveYear, DateTime FormDate, DateTime ToDate, bool isApproved)
        {
            int isApprove = 0;
            if (isApproved == true)
            {
                isApprove = 1;
            }
            string Ssql = SQLParser.MakeSQL("UPDATE LeavePlan SET [IsApproved]=%n WHERE [EMPLOYEEID]=%n AND [LeaveYear]=%n AND [PlanFromDate]=%d AND [PlanToDate]=%d",
                              isApprove, empID, leaveYear, FormDate, ToDate);
            tc.ExecuteNonQuery(Ssql);
        }

        internal static void UpDateLeavePlan(TransactionContext tc, LeavePlan oLeaveItem, int PlanID)
        {
            tc.ExecuteNonQuery("UPDATE LeavePlan SET [EMPLOYEEID]=%n,[LeaveId]=%n,[LeaveYear]=%n,[PlanFromDate]=%d," +
                              " [PlanToDate]=%d,[PlanTotalDays]=%n,[EntryDate]=%d,[IsApproved]=%n where [LeavePlanID]=%n", +
                              oLeaveItem.EmpID, DataReader.GetNullValue(oLeaveItem.LeaveID.Integer),
                              oLeaveItem.LeaveYear, DataReader.GetNullValue(oLeaveItem.PlanFromDate),
                              DataReader.GetNullValue(oLeaveItem.PlanToDate), oLeaveItem.PlanTotalDays,
                              DataReader.GetNullValue(oLeaveItem.EntryDate),
                              Convert.ToInt32(oLeaveItem.IsApproved), PlanID);
        }

        internal static void UpdateLeavePlanSubmit(TransactionContext tc, LeavePlan oLeaveItem)
        {
            tc.ExecuteNonQuery("UPDATE LeavePlan SET [IsSubmitted]=%n where EmployeeID=%n and LeaveYear=%n", +
                              Convert.ToInt32(oLeaveItem.IsSubmitted), oLeaveItem.EmpID, oLeaveItem.LeaveYear);
        }


        #endregion

        #region Delete Function

        internal static void Delete(int id, TransactionContext tc)
        {
            tc.ExecuteNonQuery("DELETE FROM [LeavePlan] WHERE LeavePlanID=%n", id);
        }
        #endregion
    }
    #endregion
}