EchoTex_Payroll/HRM.DA/DA/Leave/ShortLeaveDA.cs

using Ease.Core;
using Ease.Core.DataAccess;
using Ease.Core.Utility;
using HRM.BO;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HRM.DA
{
    internal class ShortLeaveDA
    {
        #region Constructor

        public ShortLeaveDA()
        {
        }

        #endregion

        #region Insert function

        public static void Insert(TransactionContext tc, ShortLeave oItem)
        {
            string Sql = SQLParser.MakeSQL(@"INSERT INTO ShortLeave 
                                            (ShortLeaveId, TelephoneNo, ALTEMPID, EntryDate, TotalHour, DepartureTime, 
                                            ExpectedReturnTime, GatePassStatus, 
                                            LineManagerID, ApprovedBy, ApprovedDate, EmployeeID, 
                                            PayrollTypeID, Remarks, PlaceOfVisit, PurposeOfVisit) 
                                            VALUES(%n, %s, %s, %d, %n, %T, %T, %n, %n, %n, %d, %n, %n ,%s, %s, %s)", 
                oItem.ID, oItem.TelephoneNo, oItem.ALTEMPID, oItem.EntryDate, oItem.TotalHour, oItem.DepartureTime,
                oItem.ExpectedReturnTime, oItem.GatePassStatus, oItem.LineManagerID, oItem.ApprovedBy, oItem.ApprovedDate,
                oItem.EmployeeID, oItem.PayrollTypeID, oItem.Remarks, oItem.PlaceOfVisit, oItem.PurposeOfVisit);
            tc.ExecuteNonQuery(Sql);
                
        }

        #endregion

        #region Update function

        public static void Update(TransactionContext tc, ShortLeave oItem)
        {
            tc.ExecuteNonQuery(
                " UPDATE ShortLeave SET" +
                " TelephoneNo = %s, ALTEMPID = %s,EntryDate = %d,TotalHour = %n,DepartureTime = %T," +
                " DepartureEntryByID = %n, ReturnTime= %T, ReturnEntryByID = %n, ActualInTime = %T, ActualOutTime = %T," +
                " ExpectedReturnTime = %T, GatePassStatus = %n, LineManagerID = %n,ApprovedBy = %n, ApprovedDate = %d," +
                " EmployeeID = %n, PayrollTypeID = %n, Remarks = %s,PlaceOfVisit = %s, PurposeOfVisit = %s" +
                " WHERE ShortLeaveId=%n", oItem.TelephoneNo, oItem.ALTEMPID, oItem.EntryDate, oItem.TotalHour, oItem.DepartureTime,
                oItem.DepartureEntryByID, oItem.ReturnTime, oItem.ReturnEntryByID, oItem.ActualInTime, oItem.ActualOutTime,
                oItem.ExpectedReturnTime, oItem.GatePassStatus, oItem.LineManagerID, oItem.ApprovedBy, oItem.ApprovedDate,
                oItem.EmployeeID, oItem.PayrollTypeID, oItem.Remarks, oItem.PlaceOfVisit, oItem.PurposeOfVisit, oItem.ID);


        }
        public static void UpdateInAndOutTime(TransactionContext tc, ShortLeave oItem, Employee oEmp)
        {

            if (oItem.ActualInTime != TimeSpan.FromMinutes(0))
            {
                try
                {
                    if (oItem.ActualOutTime == TimeSpan.FromMinutes(0))
                    {
                        throw new Exception("There is no Depurture Time for this Employee.");
                    }
                    tc.ExecuteNonQuery(@"UPDATE ShortLeave SET ActualInTime = %T, ReturnEntryByID = %n, TotalHour = %n WHERE ShortLeaveId=%n", oItem.ActualInTime, oEmp.ID, oItem.TotalHour, oItem.ID);

                }
                catch (Exception e)
                {
                    #region Handle Exception

                    if (tc != null)
                        tc.HandleError();
                    ExceptionLog.Write(e);
                    throw new Exception("Failed to Update Gateman entry. Because " + e.Message, e);

                    #endregion
                }

                return;
            }
            if (oItem.ActualOutTime != TimeSpan.FromMinutes(0))
            {
                tc.ExecuteNonQuery(@"UPDATE ShortLeave SET ActualOutTime = %T, DepartureEntryByID = %n WHERE ShortLeaveId=%n", oItem.ActualOutTime, oEmp.ID, oItem.ID);
                return;
            }
        }
        #endregion

        #region ID Generation function

        public static int GetNewID(TransactionContext tc)
        {
            return tc.GenerateID("ShortLeave", "ShortLeaveID");
        }
        #endregion

        #region Delete function
        public static void Delete(TransactionContext tc, int empid)
        {
            tc.ExecuteNonQuery("DELETE * FROM ShortLeave WHERE EmployeeID = %n", empid);
        }

        #endregion


        #region Get function

        internal static IDataReader Get(TransactionContext tc, int id)
        {
            return tc.ExecuteReader(@"SELECT * FROM ShortLeave WHERE shortleaveid = %n", id);
        }

        

        internal static IDataReader GetByDate(TransactionContext tc, int empid, DateTime date)
        {
            string sql = SQLParser.MakeSQL(@"SELECT * FROM ShortLeave WHERE EmployeeID = %n AND EntryDate IN (%d)", empid, date);
            return tc.ExecuteReader(sql);
        }


        internal static DataTable Get(TransactionContext tc, int empid, int payrollTypeID, EnumGatePassStatus status, DateTime fromDate, DateTime toDate)
        {
            string sql = "";
            if (status == EnumGatePassStatus.None)
            {
                sql = SQLParser.MakeSQL(
                @"SELECT * FROM ShortLeave WHERE employeeid=%n AND payrollTypeID =%n AND entrydate BETWEEN %d AND %d order by entrydate",
                    empid, payrollTypeID, fromDate, toDate);
            }
            else
            {
                sql = SQLParser.MakeSQL(
                @"SELECT * FROM ShortLeave WHERE employeeid=%n AND payrollTypeID =%n AND gatepassstatus=%n AND entrydate BETWEEN %d AND %d order by entrydate",
                    empid, payrollTypeID, status, fromDate, toDate);
            }

            return tc.ExecuteDataTable(sql);
        }
        internal static DataTable getGatemanShortLeave(TransactionContext tc, EnumGatePassStatus status, DateTime fromDate, DateTime toDate)
        {
            string sql = SQLParser.MakeSQL(
            @"select sl.*, e.* from ShortLeave sl
                left join employee e on sl.EmployeeID = e.EMPLOYEEID 
                left join DESIGNATION d on d.DESIGNATIONID = e.DESIGNATIONID
                left join DEPARTMENT dep on dep.DEPARTMENTID = e.DEPARTMENTID
                left join LOCATION l on l.LOCATIONID = e.LOCATIONID
                WHERE sl.gatepassstatus=%n AND sl.entrydate BETWEEN %d AND %d order by entrydate", status, fromDate, toDate);

            return tc.ExecuteDataTable(sql);
        }
        internal static DataTable GetAppliedSortLeave(TransactionContext tc, int lmId, EnumGatePassStatus status)
        {
            string sql = SQLParser.MakeSQL(
                                            @"SELECT * FROM ShortLeave SL 
                                            JOIN EMPLOYEE E 
                                            ON SL.EMPLOYEEID = E.EmployeeID
                                            JOIN DEPARTMENT D
                                            ON E.DEPARTMENTID = D.DEPARTMENTID
                                            WHERE SL.LineManagerID = %n AND SL.GatePassStatus = %n 
                                            ORDER BY SL.EntryDate DESC
                                            ", lmId, status);

            return tc.ExecuteDataTable(sql);
        }

        internal static DataTable GetApprovedShortLeave(TransactionContext tc, int lmId, EnumGatePassStatus status, DateTime fromDate, DateTime toDate)
        {
            string sql = SQLParser.MakeSQL(
            @"SELECT sl.*, e.* FROM ShortLeave sl left join EMPLOYEE e on e.EMPLOYEEID = sl.EmployeeID
                WHERE sl.linemanagerid=%n AND gatepassstatus=%n AND entrydate BETWEEN %d AND %d order by entrydate DESC",
                lmId, status, fromDate, toDate);

            return tc.ExecuteDataTable(sql);
        }

        internal static void ApproveOrRejectShortLeave(TransactionContext tc, int shortLeaveid, EnumGatePassStatus status, int approver)
        {
            string sql = SQLParser.MakeSQL(
            @"UPDATE ShortLeave SET GatePassStatus = %n, ApprovedBy = %n, ApprovedDate = %d WHERE ShortLeaveId = %n"
            , status, approver, DateTime.Now, shortLeaveid);

            tc.ExecuteNonQuery(sql);
        }

        internal static void ApproveMultipleShortLeave(TransactionContext tc, string shortLeaveid, EnumGatePassStatus status, int approver)
        {
            string sql = SQLParser.MakeSQL(
            @"UPDATE ShortLeave SET GatePassStatus = %n, ApprovedBy = %n, ApprovedDate = %d WHERE ShortLeaveId IN(%q)"
            , status, approver, DateTime.Now, shortLeaveid);

            tc.ExecuteNonQuery(sql);
        }
        #endregion

    }
}