EchoTex_Payroll/HRM.DA/DA/ResignationRequest/ResignationRequestDA.cs

using HRM.BO;
using Ease.Core.DataAccess;
using System;
using System.Data;
using Microsoft.Data.SqlClient;

namespace HRM.DA
{
    #region ResignationRequestDA

    internal class ResignationRequestDA
    {
        #region Constructor

        private ResignationRequestDA()
        {
        }

        #endregion

        #region Insert function

        internal static void Insert(TransactionContext tc, EmpResignationRequest item)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO EmpResignationRequest(ResignationRequestId, RequestDate, EmployeeID, LastWorkingDate, ResignationDate, WFStatus," +
                " NextApproverEmpID, CleranceInitiateDate,CleranceInitiateBy,ClearanceStatus,IsDiscontinue,DiscontinueDate,DiscontinueBy,CreationDate,CreatedBy,ResignationType)" +
                " VALUES(%n, %d, %n, %d, %d, %n, %n, %d,%n,%n,%b,%d,%n,%d,%n,%n)", item.ID, item.RequestDate, item.EmployeeID, item.LastWorkingDate,
                item.ResignationDate, item.WFStatus, item.NextApproverEmpID, item.CleranceInitiateDate, item.CleranceInitiateBy,
                item.ClearanceStatus, item.IsDiscontinue, item.DiscontinueDate, item.DiscontinueBy, item.CreatedDate, item.CreatedBy, item.ResignationType == null ? null : (int)item.ResignationType) ;
        }

        #endregion

        #region Update function

        internal static void Update(TransactionContext tc, EmpResignationRequest item)
        {
            tc.ExecuteNonQuery(
                "UPDATE EmpResignationRequest SET RequestDate=%d, EmployeeID=%n, LastWorkingDate=%d, ResignationDate=%d, WFStatus=%n, NextApproverEmpID=%n, CleranceInitiateDate=%d,CleranceInitiateBy=%n,ClearanceStatus=%n,IsDiscontinue=%b" +
                " DiscontinueDate=%d,DiscontinueBy=%n,ResignationType=%n" +
                " WHERE ResignationRequestId=%n", item.RequestDate, item.EmployeeID, item.LastWorkingDate,
                item.LastWorkingDate, item.ResignationDate, item.WFStatus, item.NextApproverEmpID, item.CleranceInitiateDate, item.CleranceInitiateBy,
                item.ClearanceStatus, item.IsDiscontinue, item.DiscontinueDate, item.DiscontinueBy, item.ResignationType == null ? null : (int)item.ResignationType);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader(@"SELECT empreg.*,e.EMPLOYEENO,e.NAME EmpName,d.NAME designation,dept.DESCRIPTION Department,e.JOININGDATE
                                    FROM EmpResignationRequest empreg
                                    INNER JOIN EMPLOYEE AS e ON empreg.EmployeeID=e.EMPLOYEEID
                                    INNER JOIN Designation AS d ON d.DESIGNATIONID=e.DesignationID
                                    INNER JOIN Department AS dept ON dept.DEPARTMENTID=e.DEPARTMENTID
                                    Order By ResignationRequestId desc");
        }
        internal static IDataReader Get(TransactionContext tc, EnumResignStatus status)
        {
            return tc.ExecuteReader(@"SELECT empreg.*,e.EMPLOYEENO,e.NAME EmpName,d.NAME designation,dept.DESCRIPTION Department,e.JOININGDATE
                                    FROM EmpResignationRequest empreg
                                    INNER JOIN EMPLOYEE AS e ON empreg.EmployeeID=e.EMPLOYEEID
                                    INNER JOIN Designation AS d ON d.DESIGNATIONID=e.DesignationID
                                    INNER JOIN Department AS dept ON dept.DEPARTMENTID=e.DEPARTMENTID
                                    where empreg.wfStatus=%n  Order By ResignationRequestId desc", status);
        }

        internal static IDataReader GetFsHeadWithResgEmployee(TransactionContext tc)
        {
          return tc.ExecuteReader(@"SELECT reg.EmployeeID,e.EMPLOYEENO,e.NAME,ItemCode,SUM(ChangedValue) Amount
                        FROM EmpResignationRequest reg
                        INNER JOIN EMPLOYEE AS e ON e.EMPLOYEEID=reg.EmployeeID
                        LEFT JOIN FSTRAN AS f ON reg.EmployeeID=f.EMPLOYEEID
                        LEFT JOIN FSTRANdetail AS fd ON f.fstranid=fd.tranid
                        GROUP BY FSTRANTYPE,EMPLOYEENO,NAME,ItemCode,reg.EMPLOYEEID");
        }

        internal static IDataReader GetResignationByEmployeeId(TransactionContext tc,int employeeId)
        {
           return tc.ExecuteReader(@"SELECT TOP 1 reg.*
                                     FROM EmpResignationRequest reg WHERE reg.EmployeeID=%n", employeeId);
        }

        internal static IDataReader Get(TransactionContext tc, DateTime fromDate, DateTime toDate)
        {
            return tc.ExecuteReader(@"SELECT e.EmployeeNO EmployeeNO, e.name EmpName, r.*, 
                                     d.NAME designation,dept.DESCRIPTION Department,e.JOININGDATE
                                     FROM EmpResignationRequest r
                                     INNER join Employee e  on  r.EmployeeID=e.EmployeeID  
                                     INNER JOIN Designation AS d ON d.DESIGNATIONID=e.DesignationID
                                     INNER JOIN Department AS dept ON dept.DEPARTMENTID=e.DEPARTMENTID
                                     WHERE r.RequestDate between %d and %d", fromDate, toDate);
        }


        internal static IDataReader GetByEmpID(TransactionContext tc, int empID)
        {
            return tc.ExecuteReader(@"SELECT e.EmployeeNO EmployeeNO, e.name EmpName, r.*, 
                                     d.NAME designation,dept.DESCRIPTION Department,e.JOININGDATE
                                     FROM EmpResignationRequest r
                                     INNER join Employee e  on  r.EmployeeID=e.EmployeeID  
                                     INNER JOIN Designation AS d ON d.DESIGNATIONID=e.DesignationID
                                     INNER JOIN Department AS dept ON dept.DEPARTMENTID=e.DEPARTMENTID
                                     WHERE e.EMPLOYEEID=%n", empID);
        }

        //internal static IDataReader Get(TransactionContext tc, EnumStatus status)
        //{
        //    if (EnumStatus.Active == status)
        //    {
        //        return tc.ExecuteReader("SELECT * FROM EmpResignationRequest where Status=%n order by SequenceNO", status);
        //    }
        //    else
        //    {
        //        return tc.ExecuteReader("SELECT * FROM EmpResignationRequest order by SequenceNO");
        //    }
        //}

        internal static IDataReader Get(TransactionContext tc, int nID)
        {
            return tc.ExecuteReader("SELECT * FROM EmpResignationRequest Where ResignationRequestId=%n", nID);
        }

        internal static IDataReader GetByReferenceId(TransactionContext tc, int refId)
        {
            return tc.ExecuteReader("SELECT * FROM EmpResignationAttachment where EmpRegRequestID= %n", refId);
        }

        internal static void UpdateResignationRequestApproveStatus(TransactionContext tc, int pkid, EnumwfStatus status, int userId, DateTime lastworkingDate, string remarks, string resignationReason)
        {
            string ssql = "";

            ssql = SQLParser.MakeSQL(
              "UPDATE EmpResignationRequest SET WFStatus=%n,ResignationRequest=%s,Remarks=%s,LastWorkingDate=%d,ApprovedBy=%n,ApprovedDate=%d WHERE ResignationRequestId=%n",
              (int)EnumwfStatus.Approve, resignationReason, remarks, lastworkingDate, userId, DateTime.Now, pkid);

            tc.ExecuteNonQuery(ssql);
        }

        #endregion

        #region Delete function

        internal static void Delete(TransactionContext tc, int nID)
        {
         //   tc.ExecuteNonQuery("DELETE FROM FSHead Where FSHeadID=%n", nID);
        }

        internal static void updateStatus(TransactionContext tc, int nID, EnumResignStatus status)
        {
            tc.ExecuteNonQuery("UPDATE EmpResignationRequest set WFStatus=%n Where ResignationRequestId=%n", status, nID);
        }
        #endregion

        public static void InsertFileAttachment(EmpResignationAttachment item, string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                string commandText = @"INSERT INTO EmpResignationAttachment(EmpRegRequestID, FileData, OriginalFileName,Extension) Values (@EmpRegRequestID,@FileData,@OriginalFileName,@Extension)";
                cmd.CommandText = commandText;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@EmpRegRequestID", SqlDbType.Int);
                cmd.Parameters["@EmpRegRequestID"].Value = item.EmpRegRequestID;

                cmd.Parameters.Add("@OriginalFileName", SqlDbType.VarChar);
                cmd.Parameters["@OriginalFileName"].Value = item.OriginalFileName;

                cmd.Parameters.Add("@FileData", SqlDbType.VarBinary);
                cmd.Parameters["@FileData"].Value = item.FileAsByteArray;

                cmd.Parameters.Add("@Extension", SqlDbType.VarChar);
                cmd.Parameters["@Extension"].Value = item.Extension;

                cmd.ExecuteNonQuery();
                cmd.Dispose();

                connection.Close();
            }
        }

        internal static void UpdateResignationRequestStatus(TransactionContext tc, int pkid, EnumResignStatus status, int? nextApproverID)
        {
            string ssql = "";
            ssql = SQLParser.MakeSQL(
              "UPDATE EmpResignationRequest SET WFstatus=%n,NextApproverEmpID=%n WHERE ResignationRequestId=%n",
              (int)status, nextApproverID,
              pkid);

            tc.ExecuteNonQuery(ssql);
        }

        internal static void UpdateResignationRequestResignStatus(TransactionContext tc, int pkid, EnumResignStatus status)
        {
            string ssql = "";
            ssql = SQLParser.MakeSQL(
              "UPDATE EmpResignationRequest SET WFstatus=%n WHERE ResignationRequestId=%n",
              (int)status,
              pkid);

            tc.ExecuteNonQuery(ssql);
        }
    }

    #endregion
}