EchoTex_Payroll/HRM.DA/DA/JV/JVSetupDA.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Ease.Core.Model;
using System.Data.SqlClient;
using Ease.Core.DataAccess;
using Ease.Core.DataAccess.SQL;
using System.Data;
using HRM.BO;

namespace HRM.DA
{
    #region JVSetupDA

    public class JVSetupDA
    {
        #region Constructor

        public JVSetupDA()
        {
        }

        #endregion

        #region Insert function

        internal static void Insert(TransactionContext tc, JVSetup oItem)
        {
            string sSQL = SQLParser.MakeSQL(
                @"INSERT INTO JVSetup(JVSetupID, Description, GLCode, IsConfirmed, IsEmployeeDetail, JVTypeID, MonthDate, SequenceNo, BookToCC, BookToPC, ShowCC, Side, SideCode, CreatedBy, CreationDate,payrollTypeID, GroupCode)" +
                " VALUES(%n, %s, %s, %n, %n, %n, %d, %n, %n, %n, %n, %n, %s, %n, %d,%n, %s)", oItem.ID, oItem.Description,
                oItem.GLCode, Convert.ToInt16(oItem.IsConfirmed), Convert.ToInt16(oItem.IsEmployeeDetail),
                oItem.JVTypeID, oItem.MonthDate, oItem.SequenceNo, Convert.ToInt16(oItem.BookToCC),
                Convert.ToInt16(oItem.BookToPC), Convert.ToInt16(oItem.ShowCC), oItem.Side, oItem.SideCode,
                DataReader.GetNullValue(oItem.CreatedBy), DataReader.GetNullValue(oItem.CreatedDate),
                oItem.PayrollTypeID, oItem.GroupCode);
            tc.ExecuteNonQuery(
                "INSERT INTO JVSetup(JVSetupID, Description, GLCode, IsConfirmed, IsEmployeeDetail, JVTypeID, MonthDate, SequenceNo, BookToCC, BookToPC, ShowCC, Side, SideCode, CreatedBy, CreationDate,payrollTypeID,EMPLOYEETYPE, GroupCode)" +
                " VALUES(%n, %s, %s, %n, %n, %n, %d, %n, %n, %n, %n, %n, %s, %n, %d,%n,%n, %s)", oItem.ID,
                oItem.Description, oItem.GLCode, Convert.ToInt16(oItem.IsConfirmed),
                Convert.ToInt16(oItem.IsEmployeeDetail), oItem.JVTypeID, oItem.MonthDate, oItem.SequenceNo,
                Convert.ToInt16(oItem.BookToCC), Convert.ToInt16(oItem.BookToPC), Convert.ToInt16(oItem.ShowCC),
                oItem.Side, oItem.SideCode, DataReader.GetNullValue(oItem.CreatedBy),
                DataReader.GetNullValue(oItem.CreatedDate), oItem.PayrollTypeID, (int)oItem.EMPLOYEETYPE, oItem.GroupCode);
        }

        #endregion

        #region Update function

        internal static void Update(TransactionContext tc, JVSetup oItem)
        {
            tc.ExecuteNonQuery(
                "UPDATE JVSetup SET Description=%s, GLCode=%s, IsConfirmed=%n, IsEmployeeDetail=%n, JVTypeID=%n, MonthDate=%d, SequenceNo=%n, ShowCC=%n, BookToCC=%n, BookToPC=%n, Side=%n, SideCode=%s, ModifiedBy=%n, ModifiedDate=%d, EMPLOYEETYPE=%n,GroupCode=%s " +
                "WHERE JVSetupID=%n", oItem.Description, oItem.GLCode, Convert.ToInt16(oItem.IsConfirmed),
                Convert.ToInt16(oItem.IsEmployeeDetail), oItem.JVTypeID, oItem.MonthDate, oItem.SequenceNo,
                Convert.ToInt16(oItem.ShowCC), Convert.ToInt16(oItem.BookToCC), Convert.ToInt16(oItem.BookToPC),
                oItem.Side, oItem.SideCode, DataReader.GetNullValue(oItem.ModifiedBy),
                DataReader.GetNullValue(oItem.ModifiedDate), (int)oItem.EMPLOYEETYPE, oItem.GroupCode, oItem.ID);
        }

        internal static void UpdateSequence(TransactionContext tc, JVSetup oItem)
        {
            tc.ExecuteNonQuery("UPDATE JVSetup SET SequenceNo=%n, ModifiedBy=%n, ModifiedDate=%d" +
                               "WHERE JVSetupID=%n", oItem.SequenceNo, DataReader.GetNullValue(oItem.ModifiedBy),
                DataReader.GetNullValue(oItem.ModifiedDate), oItem.ID);
        }

        #endregion

        #region Get Function

        internal static IDataReader GetbyPayrollTypeid(TransactionContext tc, int payrolltypeid)
        {
            return tc.ExecuteReader("SELECT * FROM JVSetup Where PayrollTypeID=%n", payrolltypeid);
        }

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

        internal static IDataReader GetByJVType(TransactionContext tc, int jvTypeID)
        {
            string sSQL = SQLParser.MakeSQL("SELECT * FROM JVSetup Where JVTypeID=%n", jvTypeID);
            return tc.ExecuteReader(sSQL);
        }

        internal static IDataReader GetLatestSetupByJVType(TransactionContext tc, int payrolltyepid, int jvTypeID)
        {
            string sSQL = SQLParser.MakeSQL("SELECT * FROM JVSetup Where JVTypeID=%n and PayrollTypeID=%n and MonthDate = (" +
                " select max(MonthDate) from JVSetup where JVTypeID=%n and PayrollTypeID=%n)",  jvTypeID, payrolltyepid, jvTypeID, payrolltyepid);
            return tc.ExecuteReader(sSQL);
        }

        internal static IDataReader GetByJVTypeAndEffectDate(TransactionContext tc, int jvTypeID, DateTime dEffectDate)
        {
            string sSQL = SQLParser.MakeSQL(@"SELECT * FROM JVSetup Where JVTypeID=%n AND MonthDate >=%d AND 
                    MonthDate <=%d ORDER BY JVSetupID", jvTypeID, dEffectDate, dEffectDate);
            return tc.ExecuteReader(sSQL);
        }
        internal static DateTime? getMaxDate(TransactionContext tc, int payrolltypeid, int jvTypeID)
        {
            string sSQL = SQLParser.MakeSQL(@"SELECT MAX(MonthDate) FROM JVSetup Where payrolltypeid =%n and JVTypeID=%n  
            ORDER BY JVSetupID", payrolltypeid, jvTypeID);
            var item = tc.ExecuteScalar(sSQL);
            if(item == DBNull.Value)
            {
                return null;    
            }
            else
            {
                return Convert.ToDateTime(item);    
            }
             
        }
        internal static int GetSequenceNo(TransactionContext tc)
        {
            int nSequnceNo = 0;
            object obj = tc.ExecuteScalar("SELECT Max(SequenceNo) FROM JVSetup");
            if (obj != DBNull.Value)
            {
                nSequnceNo = Convert.ToInt32(obj);
            }

            return nSequnceNo;
        }

        internal static DateTime GetMaxDate(TransactionContext tc, DateTime dGivenDate, int payorlltypeid)
        {
            DateTime dMDate = DateTime.Now;
            object obj = tc.ExecuteScalar("SELECT MAX(MonthDate) FROM JVSetup WHERE MonthDate<=%d and payrolltypeid=%n",
                dGivenDate.Date, payorlltypeid);
            if (obj != DBNull.Value)
            {
                dMDate = Convert.ToDateTime(obj);
            }

            return dMDate;
        }

        internal static IDataReader GetByMonthDate(TransactionContext tc, DateTime dtMonthDate, int payorlltypeid)
        {
            return tc.ExecuteReader(@"SELECT * FROM JVSetup WHERE MonthDate>=%d AND MonthDate<=%d AND 
                    PayrollTypeID=%n ORDER BY SequenceNo", PayrollGlobalFunctions.PayrollFirstDateOfMonth(dtMonthDate),
                PayrollGlobalFunctions.PayrollLastDateOfMonth(dtMonthDate), payorlltypeid);
        }

        internal static IDataReader GetByJVTypeAndMonth(TransactionContext tc, int jvTypeID, DateTime dDate)
        {
            return tc.ExecuteReader("SELECT * FROM JVSetup Where JVTypeID=%n AND (MonthDate>=%d AND MonthDate<=%d)",
                jvTypeID, PayrollGlobalFunctions.PayrollFirstDateOfMonth(dDate),
                PayrollGlobalFunctions.PayrollLastDateOfMonth(dDate));
        }

        #endregion

        #region Delete function

        internal static void Delete(TransactionContext tc, int nID)
        {
            tc.ExecuteNonQuery("DELETE FROM JVSetup WHERE JVSetupID=%n", nID);
        }

        internal static void DeleteDetail(TransactionContext tc, int nJVSetupID)
        {
            tc.ExecuteNonQuery("DELETE FROM JVSetupDetail WHERE JVSetupID=%n", nJVSetupID);
        }

        internal static void DeleteCC(TransactionContext tc, int nJVSetupID)
        {
            tc.ExecuteNonQuery(@"DELETE FROM JVSetupCC WHERE JVSetupID In(Select JVSetupID 
                from JVSetup where JVSetupID=%n )", nJVSetupID
            );
            //tc.ExecuteNonQuery("DELETE FROM [JVSetupCC] WHERE JVSetupID=%n", nJVSetupID);
        }
        internal static void DeleteGrades(TransactionContext tc, int nJVSetupID)
        {
            tc.ExecuteNonQuery(@"DELETE FROM JVSetupGrade WHERE JVSetupID In(Select JVSetupID 
                from JVSetup where JVSetupID=%n )", nJVSetupID
            );
            //tc.ExecuteNonQuery("DELETE FROM [JVSetupCC] WHERE JVSetupID=%n", nJVSetupID);
        }

        #endregion
    }

    #endregion
}