CEL_Payroll/Payroll.Service/AllowDeduct/DA/AllowanceDeductionDA.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  AllowanceDeductionDA

    internal class AllowanceDeductionDA
    {
        #region  Constructor

        private AllowanceDeductionDA() { }

        #endregion

        #region  Insert function

        internal static void Insert(TransactionContext tc, AllowanceDeduction item)
        {
            tc.ExecuteNonQuery("INSERT INTO AllowanceDeduction(ALLOWDEDUCTID, code, name, allowOrDeduct, CreatedBy, CreationDate, SequenceNo, Status)" +
            " VALUES(%n, %s, %s, %n, %n, %d, %n, %n)", item.ID.Integer, item.Code, item.Name, item.AllowOrDeductType, item.CreatedBy.Integer, item.CreatedDate, item.Sequence, item.Status);
        }

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, AllowanceDeduction item)
        {
            tc.ExecuteNonQuery("UPDATE AllowanceDeduction SET code=%s, name=%s, allowOrDeduct=%n, ModifiedBy=%n, ModifiedDate=%d, SequenceNo=%n, Status=%n" +
            " WHERE ALLOWDEDUCTID=%n", item.Code, item.Name, item.AllowOrDeductType, item.ModifiedBy.Integer, item.ModifiedDate, item.Sequence, item.Status, item.ID.Integer);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc,EnumStatus status)
        {
            if(EnumStatus.Active==status || EnumStatus.Inactive==status)
            {
                return tc.ExecuteReader("SELECT * FROM AllowanceDeduction Where Status=%n AND ALLOWDEDUCTID>0  order by SequenceNo", status);
            }
            else
            {
               return tc.ExecuteReader("SELECT * FROM AllowanceDeduction Where ALLOWDEDUCTID>0  order by SequenceNo");
            }
            
        }

        internal static IDataReader GetAllowance(TransactionContext tc,EnumStatus status)
        {
            if (EnumStatus.Regardless  != status)
            {
                return tc.ExecuteReader("SELECT * FROM AllowanceDeduction Where ALLOWORDEDUCT=%n AND Status=%n Order by Name", EnumAllowOrDeduct.Allowance, status);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM AllowanceDeduction Where ALLOWORDEDUCT=%n Order by Name", EnumAllowOrDeduct.Allowance);
            }
            //return tc.ExecuteReader("SELECT * FROM AllowanceDeduction Where ALLOWORDEDUCT=%n AND AllowDeductID>0 AND Status=%n Order by SequenceNo", allowanceType, status);
        }

        internal static IDataReader GetAllowance(TransactionContext tc, EnumPeriodicity periodicity, EnumAllowOrDeduct allowOrdeduct)
        {
            string SSQL = string.Empty;
            if (EnumAllowOrDeduct.Allowance == allowOrdeduct)
            {
                SSQL = SQLParser.MakeSQL("Select * from AllowanceDeduction where AllowDeductID IN (Select AllowDeductID from ADParameterBasic where Periodicity=%n) AND AllowORDeduct=%n ", periodicity, allowOrdeduct);
            }
            else
            {
                SSQL = SQLParser.MakeSQL("Select * from AllowanceDeduction where AllowDeductID IN (Select AllowDeductID from ADParameterBasic where Periodicity=%n) AND AllowORDeduct=%n ", periodicity, allowOrdeduct);
            }
            return tc.ExecuteReader(SSQL);
        }

        internal static IDataReader GetAllowance(TransactionContext tc, EnumPeriodicity periodicity, EnumEntitleType enumEntitleType, EnumAllowOrDeduct allowOrdeduct)
        {
            string SSQL = string.Empty;
            if (EnumAllowOrDeduct.Allowance == allowOrdeduct)
            {
                SSQL = SQLParser.MakeSQL("Select * from AllowanceDeduction where AllowDeductID IN (Select AllowDeductID from ADParameterBasic where Periodicity=%n and EntitleType=%n) AND AllowORDeduct=%n ", periodicity,enumEntitleType, allowOrdeduct);
            }
            else
            {
                SSQL = SQLParser.MakeSQL("Select * from AllowanceDeduction where AllowDeductID IN (Select AllowDeductID from ADParameterBasic where Periodicity=%n and EntitleType=%n) AND AllowORDeduct=%n ", periodicity,enumEntitleType, allowOrdeduct);
            }
            return tc.ExecuteReader(SSQL);
        }

        internal static IDataReader GetDeduction(TransactionContext tc, EnumStatus status)
        {
            if (EnumStatus.Regardless  != status)
            {
                string sSQL = SQLParser.MakeSQL("SELECT * FROM AllowanceDeduction Where ALLOWORDEDUCT=%n AND ALLOWDEDUCTID>0 AND Status=%n Order by Name", EnumAllowOrDeduct.Deduction, status);
                return tc.ExecuteReader(sSQL);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM AllowanceDeduction Where ALLOWORDEDUCT=%n Order by Name", EnumAllowOrDeduct.Deduction);
            }
            //return tc.ExecuteReader("SELECT * FROM AllowanceDeduction Where ALLOWORDEDUCT=%n AND AllowDeductID>0 AND Status=%n Order by SequenceNo", allowanceType, status);
        }

        internal static IDataReader Get(TransactionContext tc, EnumStatus status,EnumAllowOrDeduct nType,EnumPeriodicity nPeriodcity)
        {
            return tc.ExecuteReader("Select DISTINCT ALLWD.* from  ALLOWANCEDEDUCTION as ALLWD,ADParameterBasic as ADPB"
                                  + " Where ALLWD.ALLOWDEDUCTID=ADPB.ALLOWDEDUCTID"
                                  + " AND ALLWD.Status=%n"
                                  + " AND ALLWD.ALLOWORDEDUCT=%n"
                                  + " AND ADPB.PERIODICITY=%n",status,nType,nPeriodcity);

        }

        internal static IDataReader Get(TransactionContext tc, EnumStatus status, EnumAllowOrDeduct nType)
        {
            return tc.ExecuteReader("Select DISTINCT ALLWD.* from  ALLOWANCEDEDUCTION as ALLWD,ADParameterBasic as ADPB"
                                  + " Where ALLWD.ALLOWDEDUCTID=ADPB.ALLOWDEDUCTID"
                                  + " AND ALLWD.Status=%n"
                                  + " AND ALLWD.ALLOWORDEDUCT=%n"
                                  + " AND (ADPB.PERIODICITY=%n"
                                  + " OR (ADPB.PERIODICITY=%n AND EntitleType=2))", status, nType, (int)EnumPeriodicity.OneOff, (int)EnumPeriodicity.Monthly);

        }

        //internal static IDataReader GetAllDeduType(TransactionContext tc, EnumAllowOrDeduct allowanceType, EnumStatus status)
        //{
        //    if (EnumStatus.Active == status)
        //    {
        //        return tc.ExecuteReader("SELECT * FROM AllowanceDeduction Where ALLOWORDEDUCT=%n AND AllowDeductID>0 AND Status=%n Order by SequenceNo", allowanceType, status);
        //    }
        //    else
        //    {
        //        return tc.ExecuteReader("SELECT * FROM AllowanceDeduction Where ALLOWORDEDUCT=%n Order by SequenceNo", allowanceType);
        //    } 
        //    //return tc.ExecuteReader("SELECT * FROM AllowanceDeduction Where ALLOWORDEDUCT=%n AND AllowDeductID>0 AND Status=%n Order by SequenceNo", allowanceType, status);
        //}

        internal static IDataReader GetByGradeID(TransactionContext tc, EnumStatus status, ID nGradeID, EnumAllowOrDeduct type)
        {
            return tc.ExecuteReader("SELECT * FROM ALLOWANCEDEDUCTION WHERE ALLOWDEDUCTID IN " +
                                    "(SELECT ALLOWDEDUCTID FROM ADPARAMETERBASIC WHERE ENTITLETYPE=%n AND ADParameterID IN " +
                                    "(SELECT Distinct ADPARAMETERID FROM ADPARAMETERGRADE WHERE GRADEID =%n))AND ALLOWORDEDUCT = %n AND Status=%n",EnumEntitleType.Individual, nGradeID.Integer, type, status);
        }

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

        internal static DataSet GetEmpAllowDeduc(TransactionContext tc, DateTime dEffectDate, ID AllowDeducID, ID PayrolltypeID)
        {
            DataSet oEmpHistories = new DataSet();
            try
            {

                oEmpHistories = tc.ExecuteDataSet("Select  ADPEmp.employeeid,ADPEmp.adparameterempid, ADPEmp.modifiedBy,ADPEmp.modifiedDate,Emp.EMPLOYEENO,Emp.NAME,Deg.NAME as Designation, gr.DESCRIPTION as Grade, ADPEmp.MONTHLYAMOUNT as Amount"
                                                    + " from ADPARAMETEREMPLOYEE as ADPEmp,EMPLOYEE as Emp,GRADES as gr,Designation as Deg"
                                                    + " where Emp.EMPLOYEEID=ADPemp.EMPLOYEEID"
                                                    + " AND Emp.DesignationID=Deg.DesignationID"
                                                    + " AND Emp.GradeID=gr.GradeID"
                                                    + " AND ADPEmp.ALLOWDEDUCTID=%n"
                                                    + " AND ADPEmp.FROMDATE between %d and %d AND Emp.PayrollTypeID=%n"
                                                    + " order by Emp.EMPLOYEENO ", AllowDeducID.Integer, 
                                                    GlobalFunctions.FirstDateOfMonth(dEffectDate),
                                                    GlobalFunctions.LastDateOfMonth(dEffectDate), PayrolltypeID.Integer);

            }

            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oEmpHistories;
        }
        internal static DataSet GetEmpAllowDeduc2(TransactionContext tc, DateTime dEffectDate, DateTime dEffectDate2, ID AllowDeducID, ID PayrolltypeID)
        {
            DataSet oEmpHistories = new DataSet();
            try
            {

                oEmpHistories = tc.ExecuteDataSet("Select  ADPEmp.employeeid,ADPEmp.adparameterempid, ADPEmp.modifiedBy,ADPEmp.modifiedDate,Emp.EMPLOYEENO,Emp.NAME,Deg.NAME as Designation, gr.DESCRIPTION as Grade, ADPEmp.MONTHLYAMOUNT as Amount"
                                                    + " from ADPARAMETEREMPLOYEE as ADPEmp,EMPLOYEE as Emp,GRADES as gr,Designation as Deg"
                                                    + " where Emp.EMPLOYEEID=ADPemp.EMPLOYEEID"
                                                    + " AND Emp.DesignationID=Deg.DesignationID"
                                                    + " AND Emp.GradeID=gr.GradeID"
                                                    + " AND ADPEmp.ALLOWDEDUCTID=%n"
                                                    + " AND ADPEmp.FROMDATE between %d and %d AND Emp.PayrollTypeID=%n"
                                                    + " order by Emp.EMPLOYEENO ", AllowDeducID.Integer,
                                                    GlobalFunctions.FirstDateOfMonth(dEffectDate),
                                                    GlobalFunctions.LastDateOfMonth(dEffectDate2), PayrolltypeID.Integer);

            }

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

        #endregion

        #region  Delete function

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

        #endregion

       
    }

    #endregion
}