291 lines
16 KiB
C#
291 lines
16 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using Ease.CoreV35.DataAccess;
|
|
using Ease.CoreV35.Model;
|
|
using Payroll.BO;
|
|
using System.Data;
|
|
|
|
namespace Payroll.Service.Attendence.DA
|
|
{
|
|
#region BuyerDailyAttProcessDA
|
|
|
|
internal class BuyerDailyAttProcessDA
|
|
{
|
|
#region Constructor
|
|
|
|
private BuyerDailyAttProcessDA() { }
|
|
|
|
#endregion
|
|
|
|
#region Insert function
|
|
|
|
internal static void Insert(TransactionContext tc, BuyerDailyAttProcess item)
|
|
{
|
|
|
|
tc.ExecuteNonQuery("INSERT INTO BuyerDailyAttProcess(BuyerDailyAttProcessID, BuyerID, EmployeeID, AttnDate, ShiftID, InTime, OutTime, AttnType, Comments, IsManualEntry, LateHour, EarlyHour, OTHour, ReferenceID, CreatedBy, CreatedDate)" +
|
|
" VALUES(%n, %n, %n, %d, %n, %D, %D, %n, %s, %b, %n, %n, %n, %n, %n, %d)", item.ID.Integer, item.BuyerID.Integer, item.EmployeeID.Integer, item.AttnDate, DataReader.GetNullValue(item.ShiftID.Integer), DataReader.GetNullValue(item.InTime), DataReader.GetNullValue(item.OutTime), item.AttenType, item.Comments, item.IsManualEntry, item.LateHour, item.EarlyHour, item.OTHour, DataReader.GetNullValue(item.ReferenceID, IDType.Integer), item.CreatedBy.Integer, item.CreatedDate);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Update function
|
|
|
|
internal static void Update(TransactionContext tc, BuyerDailyAttProcess item)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE BuyerDailyAttProcess SET BuyerID=%n, EmployeeID=%n, AttnDate=%d, ShiftID=%n, InTime=%D, OutTime=%D, AttnType=%n, Comments=%s, IsManualEntry=%b, LateHour=%n, EarlyHour=%n, OTHour=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%d" +
|
|
" WHERE BuyerDailyAttProcessID=%n", item.BuyerID.Integer, item.EmployeeID.Integer, item.AttnDate, DataReader.GetNullValue(item.ShiftID.Integer), item.InTime, item.OutTime, item.AttenType, item.Comments, item.IsManualEntry, item.LateHour, item.EarlyHour, item.OTHour, DataReader.GetNullValue(item.ReferenceID.Integer), item.ModifiedBy.Integer, item.ModifiedDate, item.ID.Integer);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Get Function
|
|
|
|
internal static IDataReader Get(TransactionContext tc)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM BuyerDailyAttProcess");
|
|
|
|
}
|
|
|
|
internal static IDataReader GetDailyEmployeeAbsent(TransactionContext tc, ID empID, DateTime dateTime, ID buyerID)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM BuyerDailyAttProcess where EmployeeID=%n and BuyerID=%n and AttnDate=%d", empID.Integer, buyerID.Integer, dateTime);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, DateTime attnDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM BuyerDailyAttProcess WHERE AttnDate=%d", attnDate);
|
|
|
|
}
|
|
|
|
internal static IDataReader GetManualEntry(TransactionContext tc, DateTime attnDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM BuyerDailyAttProcess WHERE AttnDate=%d And IsManualEntry=1", attnDate);
|
|
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, ID empID, DateTime fromDate, DateTime toDate, ID buyerID)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM BuyerDailyAttProcess WHERE EmployeeID=%n AND BUYERID=%n AND AttnDate BETWEEN %d AND %d order by AttnDate ASC", empID.Integer, buyerID.Integer, fromDate, toDate);
|
|
return tc.ExecuteReader(sql);
|
|
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, DateTime attnDate, ID shiftID, EnumAttendanceType attnType)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM BuyerDailyAttProcess WHERE AttnDate=%d AND ShiftID=%n AND AttnType=%n", attnDate, shiftID.Integer, attnType);
|
|
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, ID nID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM BuyerDailyAttProcess WHERE BuyerDailyAttProcessID=%n", nID);
|
|
}
|
|
|
|
internal static DataSet GetMonthlyAttn(TransactionContext tc, DateTime dFromDate, DateTime dToDate, ID buyerID, string sEmpID)
|
|
{
|
|
DataSet monthlyAttn = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL("select e.EMPLOYEENO, e.NAME, des.NAME As Designation, g.DESCRIPTION As Grade, e.JOININGDATE, e.GROSSSALARY, Pr.Present, Lt.Late, Ab.Absent, HD.Holiday,Lv.Leave, "
|
|
+ "((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, "
|
|
+ "((Case when Pr.Present is null then 0 else Pr.Present end) + (Case when HD.Holiday is Null Then 0 Else HD.Holiday end)) As TotalAttn "
|
|
+ "from BUYERDAILYATTPROCESS d "
|
|
+ "inner join EMPLOYEE e on e.EMPLOYEEID=d.EmployeeID "
|
|
+ "inner join DESIGNATION des on des.DESIGNATIONID = e.DESIGNATIONID "
|
|
+ "inner join GRADES g on g.GRADEID = e.GRADEID "
|
|
|
|
+ "left outer join "
|
|
+ "("
|
|
+ "select EmployeeID, count(DailyAttnProcessID) as Present "
|
|
+ "from DailyAttnProcess "
|
|
+ "where AttnType=1 and AttnDate between %d and %d "
|
|
+ "group by EmployeeID "
|
|
+ ") Pr on d.EmployeeID=Pr.EmployeeID "
|
|
|
|
+ "left outer join "
|
|
+ "("
|
|
+ "select EmployeeID, count(DailyAttnProcessID) as Late "
|
|
+ "from DailyAttnProcess "
|
|
+ "where AttnType=11 and AttnDate between %d and %d "
|
|
+ "group by EmployeeID "
|
|
+ ") Lt on d.EmployeeID=Lt.EmployeeID "
|
|
|
|
+ "left outer join "
|
|
+ "("
|
|
+ "select EmployeeID, count(DailyAttnProcessID) as Absent "
|
|
+ "from DailyAttnProcess "
|
|
+ "where AttnType=2 and AttnDate between %d and %d "
|
|
+ "group by EmployeeID "
|
|
+ ") Ab on d.EmployeeID=Ab.EmployeeID "
|
|
|
|
+ "left outer join "
|
|
+ "("
|
|
+ "select EmployeeID, count(DailyAttnProcessID) as Holiday "
|
|
+ "from DailyAttnProcess "
|
|
+ "where AttnType IN(5,8) and AttnDate between %d and %d "
|
|
+ "group by EmployeeID "
|
|
+ ") HD on d.EmployeeID=HD.EmployeeID "
|
|
|
|
+ "left outer join "
|
|
+ "("
|
|
+ "select EmployeeID, count(DailyAttnProcessID) as Leave "
|
|
+ "from DailyAttnProcess "
|
|
+ "where AttnType=4 and AttnDate between %d and %d "
|
|
+ "group by EmployeeID "
|
|
+ ") Lv on d.EmployeeID=Lv.EmployeeID "
|
|
|
|
+ "where AttnDate between %d and %d AND BuyerID=%n AND d.EmployeeID IN(%q) "
|
|
|
|
+ "group by e.EMPLOYEENO, e.NAME, des.NAME, g.DESCRIPTION, e.JOININGDATE, e.GROSSSALARY, "
|
|
+ "Pr.Present,Lt.Late,Ab.Absent,HD.Holiday,Lv.Leave", "%", dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, buyerID.Integer, sEmpID);
|
|
|
|
monthlyAttn = tc.ExecuteDataSet(sql);
|
|
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return monthlyAttn;
|
|
}
|
|
|
|
internal static DataSet GetDailyInOut(TransactionContext tc, DateTime attnDate, ID buyerID, string sEmpID)
|
|
{
|
|
DataSet dailyInOut = new DataSet();
|
|
try
|
|
{
|
|
//string sql = SQLParser.MakeSQL("select e.EMPLOYEENO,e.Name,des.NAME as Designation,d.InTime,d.OutTime,d.LateHour,d.OTHour,d.AttnType,d.Comments,s.ShortName "
|
|
// + "from BUYERDAILYATTPROCESS d inner join EMPLOYEE e on e.EMPLOYEEID = d.EmployeeID "
|
|
// + "inner join CardOperation c on c.CardID = e.CardID "
|
|
// + "inner join DESIGNATION des on des.DESIGNATIONID = e.DESIGNATIONID "
|
|
// + "inner join Shift s on s.ShiftID = d.ShiftID "
|
|
// + "where AttnDate=%d AND d.EmployeeID IN(%q) AND BuyerID=%n AND ATTNTYPE not in(%n)", attnDate, sEmpID, buyerID.Integer, EnumAttendanceType.Absent);
|
|
|
|
string sql = SQLParser.MakeSQL("select A.CardNumber, e.EMPLOYEENO, e.Name, deg.NAME as Designation, d.AttnType, d.Comments,d.InTime,d.OutTime,d.LateHour,d.OTHour,s.ShortName,dept.DEPARTMENTID,dept.DESCRIPTION as DepartmentName "
|
|
+ "from BUYERDAILYATTPROCESS D, AccessCard A, Employee e, DESIGNATION deg,Shift s, DEPARTMENT dept where E.EMPLOYEEID = d.EmployeeID "
|
|
+ "AND E.CardID = A.AccessCardID "
|
|
+ " AND deg.DESIGNATIONID = e.DESIGNATIONID "
|
|
+ " AND s.ShiftID = d.ShiftID "
|
|
+ " AND dept.DEPARTMENTID = e.DEPARTMENTID "
|
|
+ "AND AttnDate=%d AND E.EmployeeID IN(%q) AND AttnType not in(%n) and D.BuyerID=%n", attnDate, sEmpID, EnumAttendanceType.Absent, buyerID.Integer);
|
|
|
|
dailyInOut = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return dailyInOut;
|
|
}
|
|
|
|
internal static DataSet GetMonthlyDetail(TransactionContext tc, DateTime dFromDate, DateTime dToDate, ID buyerID, string sEmpID)
|
|
{
|
|
DataSet monthlyDetail = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL("select e.EMPLOYEENO,e.Name,des.NAME as Designation, "
|
|
+ "e.JOININGDATE,e.GROSSSALARY,d.AttnDate,d.AttnType "
|
|
+ "from BUYERDAILYATTPROCESS d inner join EMPLOYEE e on e.EMPLOYEEID = d.EmployeeID "
|
|
+ "inner join DESIGNATION des on des.DESIGNATIONID = e.DESIGNATIONID "
|
|
+ "where AttnDate between %d and %d AND BuyerID=%n AND d.EmployeeID IN(%q) "
|
|
+ "order by d.AttnDate", dFromDate, dToDate, buyerID.Integer, sEmpID);
|
|
|
|
monthlyDetail = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return monthlyDetail;
|
|
}
|
|
|
|
internal static DataSet GetDailyAbsent(TransactionContext tc, DateTime attnDate, ID buyerID, string sEmpID)
|
|
{
|
|
DataSet dailyAbsent = new DataSet();
|
|
try
|
|
{
|
|
//string sql = SQLParser.MakeSQL("select c.CardNumber,e.EMPLOYEENO,e.Name,des.NAME as Designation,d.AttnType,d.Comments "
|
|
// + "from BUYERDAILYATTPROCESS d inner join EMPLOYEE e on e.EMPLOYEEID = d.EmployeeID "
|
|
// + "inner join CardOperation c on c.CardID = e.CardID "
|
|
// + "inner join DESIGNATION des on des.DESIGNATIONID = e.DESIGNATIONID "
|
|
// + "where AttnDate=%d AND d.EmployeeID IN(%q) AND AttnType=%n AND BUYERID=%n", attnDate, sEmpID, EnumAttendanceType.Absent, buyerID.Integer);
|
|
|
|
//string sql = SQLParser.MakeSQL("select A.CardNumber, e.EMPLOYEENO, e.Name, deg.NAME as Designation, d.AttnType, d.Comments "
|
|
// + "from DailyAttnProcess D, AccessCard A, Employee e, DESIGNATION deg where E.EMPLOYEEID = d.EmployeeID "
|
|
// + "AND E.CardID = A.AccessCardID "
|
|
// + " AND deg.DESIGNATIONID = e.DESIGNATIONID "
|
|
// + "AND AttnDate=%d AND E.EmployeeID IN(%q) AND AttnType=%n", attnDate, sEmpID, EnumAttendanceType.Absent);
|
|
|
|
string sql = SQLParser.MakeSQL("select DISTINCT A.CardNumber, e.EMPLOYEENO, e.Name, deg.NAME as Designation, d.AttnType, d.Comments "
|
|
+ "from BUYERDAILYATTPROCESS D, AccessCard A, Employee e, DESIGNATION deg where E.EMPLOYEEID = d.EmployeeID "
|
|
+ "AND E.CardID = A.AccessCardID "
|
|
+ " AND deg.DESIGNATIONID = e.DESIGNATIONID "
|
|
+ "AND AttnDate=%d AND E.EmployeeID IN(%q) AND AttnType=%n", attnDate, sEmpID, EnumAttendanceType.Absent);
|
|
|
|
dailyAbsent = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return dailyAbsent;
|
|
}
|
|
|
|
internal static DataSet GetMonthlyAttnData(TransactionContext tc, DateTime attnMonth, ID empID)
|
|
{
|
|
DataSet monthlyDataBuyreWise = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL("select buyerID, EmployeeiD,Sum(OtHour) as OTHour from BuyerDailyAttProcess "
|
|
+ "where "
|
|
+ "AttnDate between %d and %d "
|
|
+ "AND EmployeeID=%n "
|
|
+ "group by BuyerID,EmployeeID", GlobalFunctions.FirstDateOfMonth(attnMonth), GlobalFunctions.LastDateOfMonth(attnMonth), empID.Integer);
|
|
|
|
monthlyDataBuyreWise = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return monthlyDataBuyreWise;
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Delete function
|
|
|
|
internal static void Delete(TransactionContext tc, ID nID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM [BuyerDailyAttProcess] WHERE BuyerDailyAttProcessID=%n", nID.Integer);
|
|
}
|
|
|
|
internal static void Delete(TransactionContext tc, ID empID, DateTime attnDate)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM [BuyerDailyAttProcess] WHERE EmployeeID=%n AND AttnDate=%d", empID.Integer, attnDate);
|
|
}
|
|
internal static void DeletewithoutManualEdit(TransactionContext tc, DateTime attnDate)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM BuyerDailyAttProcess WHERE AttnDate=%d AND IsManualEntry=%b", attnDate, false);
|
|
}
|
|
#endregion
|
|
|
|
internal static bool IsExist(TransactionContext tc, ID empID, DateTime attnDate, ID buyerID)
|
|
{
|
|
bool Exist = false;
|
|
Object obj = tc.ExecuteScalar("Select * FROM BuyerDailyAttProcess WHERE EmployeeID=%n AND AttnDate=%d AND BuyerID=%n", empID.Integer, attnDate, buyerID.Integer);
|
|
Exist = Convert.ToInt32(obj) > 0 ? true : false;
|
|
return Exist;
|
|
}
|
|
|
|
|
|
}
|
|
|
|
#endregion
|
|
}
|