CEL_Payroll/Payroll.Service/OverTime/DA/OTProcessDA.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  OTProcessDA

    internal class OTProcessDA
    { 
        #region  Constructor

        private OTProcessDA() { }

        #endregion

        #region  Insert function

        internal static void Insert(TransactionContext tc, OTProcess item)
        {
            tc.ExecuteNonQuery("INSERT INTO OTProcess(ProcessId, ProcessMonth, empID, Amount, TermID, Hours, CreatedBy, CreationDate,TermParameterID,EmpOverTimeID,ArrearAmount,OTMonth,PayrollTypeID)" +
            " VALUES(%n, %d, %n, %n, %n, %n, %n, %d,%n,%n,%n,%d,%n)", item.ID.Integer, item.MonthDate, item.EmployeeID.Integer, item.Amount, item.TermID.Integer, item.TotalHour, item.CreatedBy.Integer, item.CreatedDate,item.TermParameterID.Integer,item.EmpOverTimeID.Integer,item.ArrearAmount,item.OTMonth,item.PayrollTypeID.Integer);
        }

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, OTProcess item)
        {
            tc.ExecuteNonQuery("UPDATE OTProcess SET month=%d, empID=%n, amount=%n, termID=%n, Hour=%n, ModifiedBy=%n, ModifiedDate=%d,TermParameterID=%n,EmpOverTimeID=%n,ArrearAmount=%n,OTMonth=%d,PayrollTypeID=%n" +
            " WHERE ProcessId=%n", item.MonthDate, item.EmployeeID, item.Amount, item.TermID, item.TotalHour, item.ModifiedBy.Integer, item.ModifiedDate, item.TermParameterID.Integer,item.EmpOverTimeID.Integer,item.ArrearAmount,item.OTMonth,item.PayrollTypeID.Integer,item.ID.Integer);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM OTProcess");
        }

        internal static DataSet GetOTProcessData(TransactionContext tc,DateTime Month,string sEmpID)
        {
            DataSet oOTMonthlySheets = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.Employeeid,E.NAME AS EmpName,isnull((select thismonthbasic from salarymonthly where 
                                                    employeeid=E.employeeid and salarymonth=OTP.OTMonth),E.basicsalary)BasicSalary,T.Name AS TermName,OTP.ProcessMonth,OTP.OTmonth,sum(OTP.Hours)Hours,sum(OTP.Amount)Amount
                                                FROM EMPLOYEE E,OTProcess OTP,Term T
                                                WHERE E.EMPLOYEEID=OTP.EmpID
                                                AND OTP.EmpID IN(%q)
                                                AND T.TermID=OTP.TermID And OTP.ProcessMonth=%d
                                                GROUP BY E.EMPLOYEENO,E.Employeeid,E.NAME,E.BasicSalary,T.Name,OTP.ProcessMonth,OTP.OTmonth
                                                 order by E.EmployeeNo,OTP.OTmonth
                                                 ", sEmpID, Month);
                oOTMonthlySheets = tc.ExecuteDataSet(sSQL);

            }
            catch(Exception ex)
            {
              throw new Exception(ex.Message); 
            }
            return oOTMonthlySheets;
        }

        internal static DataSet GetOTMonthRangeData(TransactionContext tc,DateTime FDate,DateTime TDate)
        {
            DataSet oOTMonthlySheets = new DataSet();
            try
            {
                oOTMonthlySheets = tc.ExecuteDataSet("SELECT Loc.DESCRIPTION AS Location,Dep.DESCRIPTION, Emp.EMPLOYEENO,Emp.NAME,RTrim(LTrim(DateName(MM,Pro.ProcessMonth))) OTMonth,Pro.Hours"
                                                      + " FROM EMPLOYEE AS Emp,OTProcess AS Pro,LOCATION as Loc,Department AS Dep"
                                                      + " WHERE Emp.EMPLOYEEID=Pro.EmpID"
                                                      + " AND Emp.DEPARTMENTID=Dep.DEPARTMENTID"
                                                      + " and Emp.LOCATIONID=Loc.LOCATIONID"
                                                      + " AND Pro.ProcessMonth BETWEEN %d AND %d"
                                                      + " GROUP BY Loc.DESCRIPTION,Dep.DESCRIPTION, Emp.EMPLOYEENO,Emp.NAME,Pro.ProcessMonth,Pro.Hours  order by Month(Pro.ProcessMonth)", FDate, TDate);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oOTMonthlySheets;
        }

        internal static DataSet GetBranchWiseOT(TransactionContext tc, DateTime Month)
        {
            DataSet oOTMonthlySheets = new DataSet();
            try
            {
                oOTMonthlySheets = tc.ExecuteDataSet("Select Loc.DESCRIPTION AS Location,Dep.DESCRIPTION AS Department,RTrim(LTrim(DateName(MM,Pro.ProcessMonth))) + ' ' + RTrim(LTrim(Year(Pro.ProcessMonth))) as Month,Sum(Pro.Hours ) as OTHours"
                                                     + " from EMPLOYEE as Emp,LOCATION as Loc,DEPARTMENT as Dep,OTProcess as Pro"
                                                     + " where Emp.EMPLOYEEID=Pro.EmpID"
                                                     + " and Emp.LOCATIONID=Loc.LOCATIONID"
                                                     + " and Emp.DEPARTMENTID=Dep.DEPARTMENTID"
                                                     + " and Pro.ProcessMonth=%d"
                                                     + " GROUP BY Loc.DESCRIPTION,Dep.DESCRIPTION,RTrim(LTrim(DateName(MM,Pro.ProcessMonth))) + ' ' + RTrim(LTrim(Year(Pro.ProcessMonth)))", Month);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oOTMonthlySheets;
        }

        internal static DataSet GetLocationWiseOT(TransactionContext tc, DateTime FDate, DateTime TDate)
        {
            DataSet oOTMonthlySheets = new DataSet();
            try
            {
                oOTMonthlySheets = tc.ExecuteDataSet("Select Loc.DESCRIPTION AS Location,Dep.DESCRIPTION AS Department,DateName(MM,Pro.ProcessMonth) as Month,Year(Pro.ProcessMonth) AS Year,Month(Pro.ProcessMonth),SUM(Pro.Hours) as OTHours" 
                                                     + " from EMPLOYEE as Emp,LOCATION as Loc,DEPARTMENT as Dep,OTProcess as Pro"
                                                     + " where Emp.EMPLOYEEID=Pro.EmpID"
                                                     + " and Emp.LOCATIONID=Loc.LOCATIONID"
                                                     + " and Emp.DEPARTMENTID=Dep.DEPARTMENTID"
                                                     + " and Pro.ProcessMonth between %d and %d"
                                                     + " group by Loc.DESCRIPTION,Dep.DESCRIPTION,DateName(MM,Pro.ProcessMonth),Year(Pro.ProcessMonth),Month(Pro.ProcessMonth)"
                                                     + " order By Loc.DESCRIPTION,Dep.DESCRIPTION,Month(Pro.ProcessMonth)", FDate, TDate);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oOTMonthlySheets;
        }

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

        internal static IDataReader IsProcessed(TransactionContext tc, DateTime dMonthDate, ID payrollTypeID)
        {
            return tc.ExecuteReader("SELECT Count(*) FROM OTProcess WHERE ProcessMonth=%d AND PayrollTypeID=%n",dMonthDate, payrollTypeID.Integer);
        }

        internal static IDataReader Get(TransactionContext tc, DateTime dMonthDate)
        {
            return tc.ExecuteReader("SELECT * FROM OTProcess WHERE ProcessMonth=%d", dMonthDate);
        }

        internal static IDataReader GetbyOtMonth(TransactionContext tc, string sEmpID, string sDates)
        {
            return tc.ExecuteReader("Select * from OTProcess Where EmpID in (%q) AND OTMonth in (%q) " +
                                     "AND TermparameterID in  (Select TermParameterID from TermParameter TP Where Type in (3,4)) Order By EmpID, OTMonth",
                                     sEmpID,sDates);

        }

        internal static DataSet GetCostCenterWiseOT(TransactionContext tc, DateTime dateTime, string sEmpID)
        {
            DataSet oOTMonthlySheets = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"SELECT   e.EmployeeNo, e.Name as EmpName,e.ACCOUNTNO,D.NAME AS Designation, 
                                                Sum(ot.Amount) as Amount ,CC.DESCRIPTION AS CostCenter
                                                FROM Employee e, OTProcess ot,CRG CC,EMPCOSTCENTER empcc,Designation D
                                                WHERE ot.ProcessMonth =%d 
                                                AND e.EmployeeId IN(%q) 
                                                AND e.EmployeeId = ot.EmpID 
                                                AND e.EMPLOYEEID = empcc.EMPLOYEEID
                                                AND CC.CRGID = empcc.COSTCENTERID
                                                AND empcc.CurrentCC=1 
                                                AND e.DESIGNATIONID = D.DESIGNATIONID 
                                                GROUP BY  e.EmployeeNo, e.Name,e.ACCOUNTNO,CC.DESCRIPTION,D.[NAME]
                                                ORDER BY  e.EmployeeNo", dateTime, sEmpID);
                oOTMonthlySheets = tc.ExecuteDataSet(sSQL);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oOTMonthlySheets;
        }
        
        #endregion

        #region  Delete function

        internal static void Delete(TransactionContext tc, ID nID)
        {
            tc.ExecuteNonQuery("DELETE FROM [OTProcess] WHERE ProcessId=%n", nID.Integer);
        }

        internal static void Delete(TransactionContext tc, DateTime dMonthDate, ID nPayrollTypeID)
        {
            tc.ExecuteNonQuery("DELETE FROM [OTProcess] WHERE ProcessMonth=%d AND PayrollTypeID=%n", dMonthDate, nPayrollTypeID.Integer);
        }

        #endregion
       
    }

    #endregion
}