110 lines
7.3 KiB
C#
110 lines
7.3 KiB
C#
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 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,EmployeeID)" +
|
|
" VALUES(%n, %s, %s, %n, %n, %n, %d, %n, %n, %n, %n, %n, %s, %n, %d,%n,%s,%n)", oItem.ID.Integer, oItem.Description, oItem.GLCode, Convert.ToInt16(oItem.IsConfirmed), Convert.ToInt16(oItem.IsEmployeeDetail), oItem.JVTypeID.Integer, oItem.MonthDate, oItem.SequenceNo, Convert.ToInt16(oItem.BookToCC), Convert.ToInt16(oItem.BookToPC), Convert.ToInt16(oItem.ShowCC), oItem.Side, oItem.SideCode, DataReader.GetNullValue(oItem.CreatedBy.Integer), DataReader.GetNullValue(oItem.CreatedDate), Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer,oItem.GroupCode,DataReader.GetNullValue(oItem.EmployeeID,IDType.Integer) );
|
|
tc.ExecuteNonQuery("INSERT INTO JVSetup(JVSetupID, Description, GLCode, IsConfirmed, IsEmployeeDetail, JVTypeID, MonthDate, SequenceNo, BookToCC, BookToPC, ShowCC, Side, SideCode, CreatedBy, CreationDate,payrollTypeID,EMPLOYEETYPE,GroupCode,EmployeeID)" +
|
|
" VALUES(%n, %s, %s, %n, %n, %n, %d, %n, %n, %n, %n, %n, %s, %n, %d,%n,%n,%s,%n)", oItem.ID.Integer, oItem.Description, oItem.GLCode, Convert.ToInt16(oItem.IsConfirmed), Convert.ToInt16(oItem.IsEmployeeDetail), oItem.JVTypeID.Integer, oItem.MonthDate, oItem.SequenceNo, Convert.ToInt16(oItem.BookToCC), Convert.ToInt16(oItem.BookToPC), Convert.ToInt16(oItem.ShowCC), oItem.Side, oItem.SideCode, DataReader.GetNullValue(oItem.CreatedBy.Integer), DataReader.GetNullValue(oItem.CreatedDate), Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer, (int)oItem.EMPLOYEETYPE, oItem.GroupCode, DataReader.GetNullValue(oItem.EmployeeID, IDType.Integer));
|
|
}
|
|
#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,EmployeeID=%n " +
|
|
"WHERE JVSetupID=%n", oItem.Description, oItem.GLCode, Convert.ToInt16(oItem.IsConfirmed), Convert.ToInt16(oItem.IsEmployeeDetail), oItem.JVTypeID.Integer, oItem.MonthDate, oItem.SequenceNo, Convert.ToInt16(oItem.ShowCC), Convert.ToInt16(oItem.BookToCC), Convert.ToInt16(oItem.BookToPC), oItem.Side, oItem.SideCode, DataReader.GetNullValue(oItem.ModifiedBy.Integer), DataReader.GetNullValue(oItem.ModifiedDate),(int)oItem.EMPLOYEETYPE,oItem.GroupCode,DataReader.GetNullValue(oItem.EmployeeID,IDType.Integer), oItem.ID.Integer);
|
|
}
|
|
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.Integer), DataReader.GetNullValue(oItem.ModifiedDate), oItem.ID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region Get Function
|
|
internal static IDataReader Get(TransactionContext tc)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM JVSetup Where PayrollTypeID=%n", Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|
}
|
|
internal static IDataReader Get(TransactionContext tc, ID nID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM JVSetup WHERE JVSetupID =%n AND PayrollTypeID=%n", nID.Integer, Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|
}
|
|
internal static IDataReader GetByJVType(TransactionContext tc, ID jvTypeID)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL("SELECT * FROM JVSetup Where JVTypeID=%n ORDER BY GROUPCODE", jvTypeID.Integer);
|
|
return tc.ExecuteReader(sSQL);
|
|
}
|
|
internal static IDataReader GetByJVTypeAndEffectDate(TransactionContext tc, ID jvTypeID, DateTime dEffectDate)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL("SELECT * FROM JVSetup Where JVTypeID=%n AND MonthDate >=%d AND MonthDate <=%d AND PayrollTypeID=%n ORDER BY GROUPCODE", jvTypeID.Integer, dEffectDate, dEffectDate, Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|
return tc.ExecuteReader(sSQL);
|
|
}
|
|
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)
|
|
{
|
|
DateTime dMDate = DateTime.Now;
|
|
object obj = tc.ExecuteScalar("SELECT MAX(MonthDate) FROM JVSetup WHERE MonthDate<=%d and payrolltypeid=%n", dGivenDate.Date, Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|
if (obj != DBNull.Value)
|
|
{
|
|
dMDate = Convert.ToDateTime(obj);
|
|
}
|
|
return dMDate;
|
|
}
|
|
internal static IDataReader GetByMonthDate(TransactionContext tc, DateTime dtMonthDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM JVSetup WHERE MonthDate>=%d AND MonthDate<=%d AND PayrollTypeID=%n ORDER BY SequenceNo", GlobalFunctions.FirstDateOfMonth(dtMonthDate), GlobalFunctions.LastDateOfMonth(dtMonthDate), Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|
}
|
|
internal static IDataReader GetByJVTypeAndMonth(TransactionContext tc, ID jvTypeID, DateTime dDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM JVSetup Where JVTypeID=%n AND (MonthDate>=%d AND MonthDate<=%d)", jvTypeID.Integer, GlobalFunctions.FirstDateOfMonth(dDate), GlobalFunctions.LastDateOfMonth(dDate));
|
|
}
|
|
#endregion
|
|
|
|
#region Delete function
|
|
internal static void Delete(TransactionContext tc, ID nID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM JVSetup WHERE JVSetupID=%n", nID.Integer);
|
|
}
|
|
internal static void DeleteDetail(TransactionContext tc, ID nJVSetupID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM JVSetupDetail WHERE JVSetupID=%n", nJVSetupID.Integer);
|
|
}
|
|
internal static void DeleteCC(TransactionContext tc, ID nJVSetupID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM JVSetupCC WHERE JVSetupID In(Select JVSetupID from JVSetup where JVSetupID=%n and PayrollTypeID=%n)", nJVSetupID.Integer, Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|
//tc.ExecuteNonQuery("DELETE FROM [JVSetupCC] WHERE JVSetupID=%n", nJVSetupID.Integer);
|
|
}
|
|
#endregion
|
|
}
|
|
#endregion
|
|
}
|