98 lines
3.4 KiB
C#
98 lines
3.4 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using Ease.CoreV35.DataAccess;
|
|
using Payroll.BO;
|
|
using System.Data;
|
|
using Ease.CoreV35.Model;
|
|
using Ease.CoreV35.Utility;
|
|
using Payroll.BO;
|
|
|
|
namespace Payroll.Service.Attendence.DA
|
|
{
|
|
#region ShiftTermDA
|
|
|
|
internal class ShiftTermDA
|
|
{
|
|
#region Constructor
|
|
|
|
private ShiftTermDA() { }
|
|
|
|
#endregion
|
|
|
|
#region Insert function
|
|
|
|
internal static void Insert(TransactionContext tc, ShiftTerm item)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO ShiftTerm(ShiftTermID, ShiftID, WeekendTermID, HolidayTermID, CreatedBy, CreatedDate)" +
|
|
" VALUES(%n, %n, %n, %n, %n, %d)", item.ID.Integer, item.ShiftID.Integer, item.WeekendTermID.Integer, item.HolidayTermID.Integer, item.CreatedBy.Integer, item.CreatedDate);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Update function
|
|
|
|
internal static void Update(TransactionContext tc, ShiftTerm item)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE ShiftTerm SET ShiftID=%n, WeekendTermID=%n, HolidayTermID=%n, ModifiedBy=%n, ModifiedDate=%d" +
|
|
" WHERE ShiftTermID=%n", item.ShiftID.Integer, item.WeekendTermID.Integer, item.HolidayTermID.Integer, item.ModifiedBy.Integer, item.ModifiedDate, item.ID.Integer);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Get Function
|
|
|
|
internal static IDataReader Get(TransactionContext tc)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM ShiftTerm");
|
|
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, ID nID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM ShiftTerm WHERE ShiftTermID=%n", nID);
|
|
}
|
|
|
|
internal static IDataReader GetByShiftID(TransactionContext tc, ID shiftID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM ShiftTerm WHERE ShiftID=%n", shiftID.Integer);
|
|
}
|
|
|
|
internal static DataSet GetEmpOT(TransactionContext tc, DateTime date, ID termID)
|
|
{
|
|
DataSet empOT = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL("select d.EmployeeID, "
|
|
+ "((sum (floor(d.OTHour))+ floor(((sum (d.OTHour-floor(d.OTHour)))*100)/60)) + cast(((cast(((sum(d.OTHour-floor(d.OTHour)))*100)as int))%q60) as float)/100) As TotalOT "
|
|
+ "from DailyAttnProcess d "
|
|
+ "where d.ShiftID in(select distinct shiftID from ShiftTerm st, ShiftTermDetail std, Term t "
|
|
+ "where st.ShiftTermID=std.ShiftTermID and std.TermID=t.TermID and t.TermID=%n) "
|
|
+ "and d.AttnDate between %d and %d "
|
|
+ "Group By d.EmployeeID", "%", termID.Integer, GlobalFunctions.FirstDateOfMonth(date), GlobalFunctions.LastDateOfMonth(date));
|
|
|
|
empOT = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return empOT;
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Delete function
|
|
|
|
internal static void Delete(TransactionContext tc, ID nID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM [ShiftTerm] WHERE ShiftTermID=%n", nID);
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
|
|
#endregion
|
|
}
|