CelHRTeam/CEL_Payroll
4
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
CEL_Payroll/Payroll.Service/Attendence/DA/DailyAttnProcessDA.cs

766 lines
45 KiB
C#
Raw Permalink Normal View History

Initiate
2024-09-17 14:30:13 +06:00
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 Payroll.BO;
namespace Payroll.Service
{
#region DailyAttnProcessDA
internal class DailyAttnProcessDA
{
#region Constructor
private DailyAttnProcessDA() { }
#endregion
#region Insert function
internal static void Insert(TransactionContext tc, DailyAttnProcess item)
{
tc.ExecuteNonQuery("INSERT INTO DailyAttnProcess(DailyAttnProcessID, EmployeeID, AttnDate, ShiftID, InTime, OutTime,WorkDayType, AttnType, Comments, IsManualEntry, LateHour, EarlyHour, OTHour, ReferenceID, CreatedBy, CreatedDate)" +
" VALUES(%n, %n, %d, %n, %D, %D, %n, %n, %s,%b, %n, %n, %n, %n, %n, %d)", item.ID.Integer, item.EmployeeID.Integer, item.AttnDate, DataReader.GetNullValue(item.ShiftID.Integer), DataReader.GetNullValue(item.InTime), DataReader.GetNullValue(item.OutTime), item.WorkDayType, item.AttenType, item.Comments, item.IsManualEntry, item.LateHour, item.EarlyHour, item.OTHour, DataReader.GetNullValue(item.ReferenceID.Integer), item.CreatedBy.Integer, item.CreatedDate);
}
#endregion
#region Update function
internal static void Update(TransactionContext tc, DailyAttnProcess item)
{
tc.ExecuteNonQuery("UPDATE DailyAttnProcess SET EmployeeID=%n, AttnDate=%d, ShiftID=%n, InTime=%D, OutTime=%D,WorkDayType=%n, AttnType=%n, Comments=%s,Reason=%s, IsManualEntry=%b,IsLate=%b, LateHour=%n, EarlyHour=%n, OTHour=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%d" +
" WHERE DailyAttnProcessID=%n", item.EmployeeID.Integer, item.AttnDate, DataReader.GetNullValue(item.ShiftID.Integer), item.InTime, item.OutTime,item.WorkDayType,item.AttenType, item.Comments,item.Reason, item.IsManualEntry,item.IsLate, item.LateHour, item.EarlyHour, item.OTHour, DataReader.GetNullValue(item.ReferenceID.Integer), item.ModifiedBy.Integer, item.ModifiedDate, item.ID.Integer);
}
internal static void UpdateLeave(TransactionContext tc, ID employeeID, ID leaveID, DateTime leavaDate, int n)
{
//if (n == 1)
//{
tc.ExecuteNonQuery("UPDATE DailyAttnProcess SET InTime=null, OutTime=null, OTHour = 0.0,WorkDayType=%n, AttnType=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%d" +
" WHERE EmployeeID=%n AND AttnDate=%d ",EnumWorkPlanDayType.WorkingDay, EnumAttendanceType.Leave, leaveID.Integer, User.CurrentUser.ID.Integer, DateTime.Now, employeeID.Integer, leavaDate);
//}
//else
//{
// tc.ExecuteNonQuery("UPDATE BuyerDailyAttProcess SET InTime=null, OutTime=null, OTHour = 0.0,WorkDayType=%n, AttnType=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%d" +
// " WHERE EmployeeID=%n AND AttnDate=%d ", EnumAttendanceType.Leave, leaveID.Integer, User.CurrentUser.ID.Integer, DateTime.Now, employeeID.Integer, leavaDate);
//}
}
#endregion
#region Get Function
internal static IDataReader Get(TransactionContext tc)
{
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess");
}
internal static IDataReader GetProcessByWPG(TransactionContext tc, EnumWorkPlanGroup wpg, DateTime attnDate)
{
//string sql = SQLParser.MakeSQL("SELECT * FROM DailyAttnProcess where DailyAttnProcessID=3021145");
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess where AttnDate=%d AND EmployeeID IN "
+ "(SELECT EmployeeID FROM EmployeeWorkPlanSetup WHERE WorkPlanGroupID=%n)", attnDate, wpg);
// return tc.ExecuteReader(sql);
}
internal static IDataReader Get(TransactionContext tc, DateTime attnDate)
{
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE AttnDate=%d", attnDate);
}
internal static IDataReader GetManualProcess(TransactionContext tc, DateTime attnDate)
{
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE AttnDate=%d And IsManualEntry=1", attnDate);
}
//new
internal static IDataReader GetWhrOutTimeIsNull(TransactionContext tc, DateTime attnDate, ID shiftID)
{
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE AttnDate=%d AND ShiftID=%n AND Intime IS NOT NULL AND OutTime IS NULL", attnDate, shiftID.Integer);
}
//
internal static IDataReader Get(TransactionContext tc, ID empID, DateTime fromDate, DateTime toDate)
{
string sql = SQLParser.MakeSQL("SELECT * FROM DailyAttnProcess WHERE EmployeeID=%n AND AttnDate BETWEEN %d AND %d order by AttnDate ASC", empID.Integer, fromDate, toDate);
return tc.ExecuteReader(sql);
}
internal static IDataReader Get(TransactionContext tc, string empID, DateTime fromDate, DateTime toDate)
{
string sql = SQLParser.MakeSQL("SELECT * FROM DailyAttnProcess WHERE EmployeeID IN(%q) AND AttnDate BETWEEN %d AND %d order by AttnDate ASC", empID, fromDate, toDate);
return tc.ExecuteReader(sql);
}
internal static IDataReader Get(TransactionContext tc, DateTime fromDate, DateTime toDate)
{
string sql = SQLParser.MakeSQL("SELECT * FROM DailyAttnProcess WHERE AttnDate BETWEEN %d AND %d order by AttnDate ASC", fromDate, toDate);
return tc.ExecuteReader(sql);
}
internal static IDataReader Get(TransactionContext tc, DateTime attnDate, ID shiftID, EnumAttendanceType attnType)
{
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess 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 DailyAttnProcess WHERE DailyAttnProcessID=%n", nID);
}
internal static DataSet GetMonthlyAttn(TransactionContext tc, DateTime dFromDate, DateTime dToDate, string sEmpID)
{
DataSet monthlyAttn = new DataSet();
try
{
string sql = SQLParser.MakeSQL("select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo, e.NAME, des.NAME As Designation, dept.[DESCRIPTION] Department, g.DESCRIPTION As Grade, e.JOININGDATE, e.GROSSSALARY, IsNull(Pr.Present,0) Present, IsNull(Lt.Late, 0) Late, IsNull(Ab.Absent, 0) Absent, IsNull(HD.Holiday, 0) Holiday, IsNull(Lv.Leave,0) Leave, "
+ "Isnull(OD.OutsideDuty, 0) OutsideDuty, Isnull(DL.Delays, 0) Delays, Isnull(EL.Early, 0) Early, "
+ "((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)+(Case when Lv.Leave is Null Then 0 Else Lv.Leave end)) As TotalAttn "
+ "from (SELECT * FROM DailyAttnProcess WHERE AttnDate between %d and %d And EmployeeID IN(%q)) d "
+ "inner join EMPLOYEE e on e.EMPLOYEEID=d.EmployeeID "
+ "inner join DESIGNATION des on des.DESIGNATIONID = e.DESIGNATIONID "
+ "INNER JOIN Department dept ON dept.DEPARTMENTID=e.DepartmentID "
+ "inner join GRADES g on g.GRADEID = e.GRADEID "
+ "LEFT JOIN AccessCard ac ON e.CardID = ac.AccessCardID "
+ "left outer join "
+ "("
+ "select EmployeeID, count(DailyAttnProcessID) as Present "
+ "from DailyAttnProcess "
+ "where AttnType IN(1,11) 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 "
+ "left outer join "
+ "("
+ "select EmployeeID, count(DailyAttnProcessID) as OutsideDuty "
+ "from DailyAttnProcess "
+ "where AttnType=7 and AttnDate between %d and %d "
+ "group by EmployeeID "
+ ") OD on d.EmployeeID=OD.EmployeeID "
+ "left outer join "
+ "("
+ "select EmployeeID, count(DailyAttnProcessID) as Delays "
+ "from DailyAttnProcess "
+ "where AttnType=3 and AttnDate between %d and %d "
+ "group by EmployeeID "
+ ") DL on d.EmployeeID=DL.EmployeeID "
+ "left outer join "
+ "("
+ "select EmployeeID, count(DailyAttnProcessID) as Early "
+ "from DailyAttnProcess "
+ "where AttnType=12 and AttnDate between %d and %d "
+ "group by EmployeeID "
+ ") EL on d.EmployeeID=EL.EmployeeID "
+ "group by e.EMPLOYEENO, e.NAME,ac.CardNumber, des.NAME, g.DESCRIPTION, e.JOININGDATE, e.GROSSSALARY, "
+ "Pr.Present,Lt.Late,Ab.Absent,HD.Holiday,Lv.Leave, OD.OutsideDuty, dl.Delays, EL.Early, dept.[DESCRIPTION] ", "%", dFromDate, dToDate, sEmpID, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate, dFromDate, dToDate);
monthlyAttn = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return monthlyAttn;
}
internal static DataSet GetDailyInOut(TransactionContext tc, DateTime attnDate, string sEmpID)
{
DataSet dailyInOut = new DataSet();
try
{
string sql = SQLParser.MakeSQL(@"SELECT tab1.* FROM Employee e,
(select e.employeeid,e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo, e.Name, deg.NAME as Designation,cg.DESCRIPTION AS EmpType,
d.AttnType as Status, d.Comments,d.InTime,d.OutTime,d.OTHour,IsNULL(s.ShortName,'') as Shift,
IsNull(dept.DEPARTMENTID,0) DeptID,IsNull(dept.DESCRIPTION,'') as DeptName,d.ReferenceID
from Employee e
INNER join(SELECT * FROM DailyAttnProcess
WHERE AttnDate=%d And EmployeeID IN(%q)) D
ON E.EMPLOYEEID = d.EmployeeID
INNER Join DESIGNATION deg
ON deg.DESIGNATIONID = e.DESIGNATIONID
Left JOIN DEPARTMENT dept
ON dept.DEPARTMENTID = e.DEPARTMENTID
Left JOIN Category cg
ON E.CATEGORYID = cg.CATEGORYID
LEFT JOIN Shift s
ON s.ShiftID = D.ShiftID
LEFT JOIN AccessCard ac
ON e.CardID = ac.AccessCardID)tab1
where e.employeeid=tab1.employeeID and e.status=1", attnDate, sEmpID);
dailyInOut = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return dailyInOut;
}
internal static DataSet GetDailyDataByStatus(TransactionContext tc, DateTime attnDate,string sStatus ,string sEmpID)
{
DataSet dailyInOut = new DataSet();
try
{
string sql;
if (sStatus != string.Empty)
{
sql = SQLParser.MakeSQL(@"select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo,e.Name,deg.NAME as Designation,cg.DESCRIPTION AS EmpType,
d.AttnType as Status, d.Comments,d.InTime,d.OutTime,d.OTHour,IsNULL(s.ShortName,'') as Shift,
dept.DEPARTMENTID DeptID,dept.DESCRIPTION as DeptName,d.ReferenceID
from (SELECT * FROM DailyAttnProcess
WHERE AttnDate=%d And AttnType IN(%q) And EmployeeID IN(%q)) D
INNER Join Employee e
ON E.EMPLOYEEID = d.EmployeeID
INNER Join DESIGNATION deg
ON deg.DESIGNATIONID = e.DESIGNATIONID
INNER JOIN DEPARTMENT dept
ON dept.DEPARTMENTID = e.DEPARTMENTID
INNER JOIN Category cg
ON E.CATEGORYID = cg.CATEGORYID
LEFT JOIN Shift s
ON s.ShiftID = D.ShiftID
LEFT JOIN AccessCard ac
ON e.CardID = ac.AccessCardID", attnDate, sStatus, sEmpID);
}
else
{
sql = SQLParser.MakeSQL(@"select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo, e.Name, deg.NAME as Designation,cg.DESCRIPTION AS EmpType,
d.AttnType as Status, d.Comments,d.InTime,d.OutTime,d.OTHour,IsNULL(s.ShortName,'') as Shift,
dept.DEPARTMENTID DeptID,dept.DESCRIPTION as DeptName,d.ReferenceID
from (SELECT * FROM DailyAttnProcess
WHERE AttnDate=%d And EmployeeID IN(%q)) D
INNER Join Employee e
ON E.EMPLOYEEID = d.EmployeeID
INNER Join DESIGNATION deg
ON deg.DESIGNATIONID = e.DESIGNATIONID
INNER JOIN DEPARTMENT dept
ON dept.DEPARTMENTID = e.DEPARTMENTID
INNER JOIN Category cg
ON E.CATEGORYID = cg.CATEGORYID
LEFT JOIN Shift s
ON s.ShiftID = D.ShiftID
LEFT JOIN AccessCard ac
ON e.CardID = ac.AccessCardID", attnDate, sEmpID);
}
dailyInOut = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return dailyInOut;
}
internal static DataSet GetDateRangeDataByStatus(TransactionContext tc, DateTime attnFromDate, DateTime attnToDate, string sStatus, string sEmpID)
{
DataSet dailyInOut = new DataSet();
try
{
string sql;
if (sStatus != string.Empty)
{
sql = SQLParser.MakeSQL(@"select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo,e.Name,deg.NAME as Designation,cg.DESCRIPTION AS EmpType,
d.AttnType as Status, d.Comments,d.AttnDate,d.InTime,d.OutTime,d.OTHour,D.ShiftID,IsNULL(s.ShortName,'') as Shift,
dept.DEPARTMENTID DeptID,dept.DESCRIPTION as DeptName,d.ReferenceID
from (SELECT * FROM DailyAttnProcess
WHERE AttnDate between %d and %d And AttnType IN(%q) And EmployeeID IN(%q)) D
INNER Join Employee e
ON E.EMPLOYEEID = d.EmployeeID
INNER Join DESIGNATION deg
ON deg.DESIGNATIONID = e.DESIGNATIONID
INNER JOIN DEPARTMENT dept
ON dept.DEPARTMENTID = e.DEPARTMENTID
INNER JOIN Category cg
ON E.CATEGORYID = cg.CATEGORYID
LEFT JOIN Shift s
ON s.ShiftID = D.ShiftID
LEFT JOIN AccessCard ac
ON e.CardID = ac.AccessCardID", attnFromDate,attnToDate, sStatus, sEmpID);
}
else
{
sql = SQLParser.MakeSQL(@"select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo, e.Name, deg.NAME as Designation,cg.DESCRIPTION AS EmpType,
d.AttnType as Status, d.Comments,d.AttnDate,d.InTime,d.OutTime,d.OTHour,D.ShiftID,IsNULL(s.ShortName,'') as Shift,
dept.DEPARTMENTID DeptID,dept.DESCRIPTION as DeptName,d.ReferenceID
from (SELECT * FROM DailyAttnProcess
WHERE AttnDate between %d and %d And EmployeeID IN(%q)) D
INNER Join Employee e
ON E.EMPLOYEEID = d.EmployeeID
INNER Join DESIGNATION deg
ON deg.DESIGNATIONID = e.DESIGNATIONID
INNER JOIN DEPARTMENT dept
ON dept.DEPARTMENTID = e.DEPARTMENTID
INNER JOIN Category cg
ON E.CATEGORYID = cg.CATEGORYID
LEFT JOIN Shift s
ON s.ShiftID = D.ShiftID
LEFT JOIN AccessCard ac
ON e.CardID = ac.AccessCardID", attnFromDate,attnToDate, sEmpID);
}
dailyInOut = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return dailyInOut;
}
internal static DataSet GetDailyInOutAndAbsent(TransactionContext tc, DateTime attnDate, string sEmpID)
{
DataSet dailyInOutAndAbsent = new DataSet();
try
{
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 DailyAttnProcess 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)", attnDate, sEmpID);
dailyInOutAndAbsent = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return dailyInOutAndAbsent;
}
internal static DataSet GetDailyAbsent(TransactionContext tc, DateTime attnDate, 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 DailyAttnProcess 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", attnDate, sEmpID, EnumAttendanceType.Absent);
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);
dailyAbsent = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return dailyAbsent;
}
internal static DataSet GetMonthlyDetail(TransactionContext tc, DateTime dFromDate, DateTime dToDate, string sEmpID)
{
DataSet monthlyDetail = new DataSet();
try
{
string sql = string.Empty;
sql = SQLParser.MakeSQL(@"select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo,e.Name,des.NAME as Designation,
e.JOININGDATE,e.GROSSSALARY,d.AttnDate,d.AttnType
from (SELECT * FROM DailyAttnProcess
WHERE AttnDate between %d and %d And EmployeeID IN(%q)) d
inner join EMPLOYEE e
on e.EMPLOYEEID = d.EmployeeID
inner join DESIGNATION des on des.DESIGNATIONID = e.DESIGNATIONID
LEFT JOIN AccessCard ac ON e.CardID = ac.AccessCardID
group by e.EMPLOYEENO,e.Name,des.NAME, e.JOININGDATE,e.GROSSSALARY,d.AttnDate,d.AttnType,ac.CardNumber
order by d.AttnDate", dFromDate, dToDate, sEmpID);
monthlyDetail = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return monthlyDetail;
}
internal static DataSet GetMonthlyLongAbsent(TransactionContext tc, DateTime dFromDate, DateTime dToDate, int totalAbsentDays)
{
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 DailyAttnProcess d INNER JOIN "
+ "(SELECT EmployeeID,absCount=SUM(CASE WHEN AttnType=%n THEN 1 ELSE 0 END) from DailyAttnProcess "
+ "WHERE AttnDate between %d and %d GROUP BY EmployeeID) abs ON D.EmployeeID=abs.EmployeeID 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 abs.absCount>=%n group by e.EMPLOYEENO,e.Name,des.NAME, e.JOININGDATE,e.GROSSSALARY,d.AttnDate,d.AttnType "
+ "order by d.AttnDate", EnumAttendanceType.Absent, dFromDate, dToDate, dFromDate, dToDate, totalAbsentDays);
monthlyDetail = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return monthlyDetail;
}
internal static DataSet GetEmpAttenInfo(TransactionContext tc, string sEmpID)
{
DataSet empAttenInfo = new DataSet();
try
{
string sql = SQLParser.MakeSQL("select e.EMPLOYEENO,e.Name,ac.CardNumber,s.ShortName,w.Name "
+ "from employee e inner join AccessCard ac on ac.AccessCardID = e.CardID "
+ "inner join EmployeeWorkPlanSetup es on es.EmployeeID = e.EMPLOYEEID "
+ "inner join shift s on s.ShiftID = es.ShiftID "
+ "inner join WorkPlanGroup w on w.WorkPlanGroupID = es.WorkPlanGroupID WHERE e.EMPLOYEEID IN(%q) order by e.EMPLOYEENO ", sEmpID);
empAttenInfo = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return empAttenInfo;
}
internal static DataSet GetEmpCardInfo(TransactionContext tc, string sEmpID)
{
DataSet empAttenInfo = new DataSet();
try
{
string sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO AS IDNo,
e.NAME,e.JOININGDATE AS DOJ,
IsNULL(e.[PhotoPath],'') AS EmpPhoto,
IsNULL(des.NAME,'') AS Desig,
IsNULL(gd.DESCRIPTION,'') AS Grade,
IsNULL(gd.COLORCODE,'White') AS GradeColor,
IsNULL(dept.DESCRIPTION,'') AS Dept,
IsNULL(acrd.CardNumber,'') AS CardNo,
CASE
WHEN dept.TIRE = 4 THEN dept.DESCRIPTION
WHEN dept.TIRE = 5 THEN (SELECT Description FROM DEPARTMENT WHERE DEPARTMENTID = dept.PARENTID)
ELSE ''
END Section,
CASE
WHEN e.BLOODGROUP =1 THEN 'NONE'
WHEN e.BLOODGROUP =2 THEN 'A+'
WHEN e.BLOODGROUP =3 THEN 'A-'
WHEN e.BLOODGROUP =4 THEN 'B+'
WHEN e.BLOODGROUP =5 THEN 'B-'
WHEN e.BLOODGROUP =6 THEN 'O+'
WHEN e.BLOODGROUP =7 THEN 'O-'
WHEN e.BLOODGROUP =8 THEN 'AB+'
WHEN e.BLOODGROUP =9 THEN 'AB-'
WHEN e.BLOODGROUP =10 THEN 'NA'
ELSE ''
END BG
FROM
(SELECT * FROM EMPLOYEE WHERE EMPLOYEEID IN (%q)) e
LEFT JOIN DESIGNATION des
ON e.DESIGNATIONID = des.DESIGNATIONID
LEFT JOIN GRADES gd
ON e.GRADEID = gd.GRADEID
LEFT JOIN DEPARTMENT dept
ON e.DEPARTMENTID = dept.DEPARTMENTID
LEFT JOIN AccessCard acrd
ON e.CardID = acrd.AccessCardID
", sEmpID);
empAttenInfo = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return empAttenInfo;
}
internal static DataSet GetEmpCardInfoPerPage(TransactionContext tc, string sEmpID)
{
DataSet empAttenInfo = new DataSet();
try
{
string sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO AS IDNo,
e.NAME,e.JOININGDATE AS DOJ,
IsNULL(e.[PhotoPath],'') AS EmpPhoto,
IsNULL(des.NAME,'') AS Desig,
IsNULL(gd.DESCRIPTION,'') AS Grade,
IsNULL(gd.COLORCODE,'White') AS GradeColor,
IsNULL(dept.DESCRIPTION,'') AS Dept,
IsNULL(acrd.CardNumber,'') AS CardNo,
CASE
WHEN dept.TIRE = 4 THEN dept.DESCRIPTION
WHEN dept.TIRE = 5 THEN (SELECT Description FROM DEPARTMENT WHERE DEPARTMENTID = dept.PARENTID)
ELSE ''
END Section,
CASE
WHEN e.BLOODGROUP =1 THEN 'NONE'
WHEN e.BLOODGROUP =2 THEN 'A+'
WHEN e.BLOODGROUP =3 THEN 'A-'
WHEN e.BLOODGROUP =4 THEN 'B+'
WHEN e.BLOODGROUP =5 THEN 'B-'
WHEN e.BLOODGROUP =6 THEN 'O+'
WHEN e.BLOODGROUP =7 THEN 'O-'
WHEN e.BLOODGROUP =8 THEN 'AB+'
WHEN e.BLOODGROUP =9 THEN 'AB-'
WHEN e.BLOODGROUP =10 THEN 'NA'
ELSE ''
END BG,
IsNULL(CardOpp.AssignDate,'') AS IssueDate,
CASE
WHEN dept.TIRE = 3 THEN dept.DESCRIPTION
WHEN dept.TIRE = 4 THEN (SELECT Description FROM DEPARTMENT WHERE DEPARTMENTID = dept.PARENTID)
ELSE ''
END AS EmpType,
ISNULL(e.NationalID,'') AS NationalID,
IsNULL(cts.ADDVill,'') ADDVill,
IsNULL(cts.ADDDist,'') ADDDist,
IsNULL(cts.ADDPO,'') ADDPO,
IsNULL(cts.ADDPS,'') ADDPS,
IsNULL(cts.ADDPhone,'') ADDPhone
FROM
(SELECT * FROM EMPLOYEE WHERE EMPLOYEEID IN (%q)) e
LEFT JOIN DESIGNATION des
ON e.DESIGNATIONID = des.DESIGNATIONID
LEFT JOIN GRADES gd
ON e.GRADEID = gd.GRADEID
LEFT JOIN GRADESEGMENT gds
ON gd.GRADESEGMENTID = gds.GRADESEGMENTID
LEFT JOIN DEPARTMENT dept
ON e.DEPARTMENTID = dept.DEPARTMENTID
LEFT JOIN AccessCard acrd
ON e.CardID = acrd.AccessCardID
LEFT JOIN
(SELECT EmployeeID,CardID,MAX(AssignDate) AS AssignDate
FROM CardOperation
GROUP BY EmployeeID,CARDID) CardOpp
ON CardOpp.EmployeeID = e.EmployeeID
AND CardOpp.CardID = e.CardID
LEFT JOIN
(SELECT EmployeeID,ParmanentAddress AS ADDVill,
ISNULL(dis.NAME,'') ADDDist,
'' AS ADDPO,
ISNULL(thn.NAME,'') ADDPS,
ParmanentMobile AS ADDPhone
FROM EMPCONTACT ec
LEFT JOIN
District dis
ON ec.PARMANENTDISTRICTID = dis.DISTRICTID
LEFT JOIN
THANA thn
ON ec.PARMANENTTHANAID = thn.THANAID) cts
ON e.EmployeeID = cts.EmployeeID", sEmpID);
empAttenInfo = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return empAttenInfo;
}
internal static DataSet GetHolidays(TransactionContext tc, DateTime attnDate, ID empID)
{
DataSet holidays = new DataSet();
try
{
string sql = SQLParser.MakeSQL("select count(EmployeeID) as Holidays from DailyAttnProcess "
+ " where EmployeeID=%n and AttnDate between %d And %d and AttnType IN(%n,%n) ", empID.Integer,
GlobalFunctions.FirstDateOfMonth(attnDate), GlobalFunctions.LastDateOfMonth(attnDate), EnumAttendanceType.Holiday, EnumAttendanceType.WeeklyHoliday);
holidays = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return holidays;
}
internal static DataSet GetPresentDays(TransactionContext tc, DateTime attnDate, ID empID)
{
DataSet presentDays = new DataSet();
try
{
string sql = SQLParser.MakeSQL("select count(EmployeeID) as PresentDays from DailyAttnProcess "
+ " where EmployeeID=%n and AttnDate between %d And %d and AttnType In(1,11) ", empID.Integer,
GlobalFunctions.FirstDateOfMonth(attnDate), GlobalFunctions.LastDateOfMonth(attnDate));
presentDays = tc.ExecuteDataSet(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return presentDays;
}
internal static IDataReader GetLastProcessDate(TransactionContext tc)
{
try
{
string sql = SQLParser.MakeSQL("SELECT MAX(AttnDate) as LastProcessDate FROM DailyAttnProcess WHERE IsManualEntry=0");
return tc.ExecuteReader(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
internal static IDataReader GetLastProcessDateByPayrollType(TransactionContext tc, ID PayrolltypeID)
{
try
{
string sql = SQLParser.MakeSQL(@"SELECT MAX(AttnDate) as LastProcessDate
FROM DailyAttnProcess dap,
Employee e
WHERE dap.IsManualEntry = 0
AND dap.EmployeeID = e.EmployeeID
AND e.PayrollTypeID = %n", PayrolltypeID.Integer);
return tc.ExecuteReader(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public static IDataReader GetDailyEmployeeAbsent(TransactionContext tc, DateTime dateTime, ID nID)
{
try
{
string sql = SQLParser.MakeSQL("Select * from DailyAttnProcess where EmployeeID=%n and AttnDate=%d and AttnType=%n", nID.Integer, dateTime, EnumAttendanceType.Absent);
return tc.ExecuteReader(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
internal static IDataReader GetEmployeesFirstAttendances(TransactionContext tc, string employeeIDs)
{
try
{
string sql = SQLParser.MakeSQL(@"select d.* from DailyAttnProcess d,
(SELECT MIN(AttnDate) as AttnDate
,[EmployeeID]
FROM DailyAttnProcess
WHERE EmployeeID IN(%q) and attnType=%n
group by EmployeeID
)temp
WHERE temp.EmployeeID = d.EmployeeID
AND temp.AttnDate = d.AttnDate
order by d.EmployeeID", employeeIDs, EnumAttendanceType.Present);
return tc.ExecuteReader(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
internal static IDataReader Get(TransactionContext tc, ID empID, ID shiftID, DateTime fromdate, DateTime todate, EnumAttendanceType enumAttendanceType)
{
try
{
string sql = SQLParser.MakeSQL("Select * from DailyAttnProcess where EmployeeID=%n AND ShiftID=%n AND AttnDate BETWEEN %d AND %d AND AttnType=%n",
empID.Integer, shiftID.Integer, fromdate, todate, enumAttendanceType);
return tc.ExecuteReader(sql);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion
#region Delete function
internal static void Delete(TransactionContext tc, ID nID)
{
tc.ExecuteNonQuery("DELETE FROM [DailyAttnProcess] WHERE DailyAttnProcessID=%n", nID.Integer);
}
internal static void Delete(TransactionContext tc, ID empID, DateTime attnDate)
{
tc.ExecuteNonQuery("DELETE FROM [DailyAttnProcess] WHERE EmployeeID=%n AND AttnDate=%d", empID.Integer, attnDate);
}
internal static void Deletewithoutmanulentry(TransactionContext tc, DateTime attnDate)
{
tc.ExecuteNonQuery("DELETE FROM DailyAttnProcess WHERE AttnDate=%d AND IsManualEntry=%b", attnDate, false);
}
#endregion
internal static bool IsExist(TransactionContext tc, ID empID, DateTime attnDate)
{
bool Exist = false;
Object obj = tc.ExecuteScalar("Select * FROM DailyAttnProcess WHERE EmployeeID=%n AND AttnDate=%d", empID.Integer, attnDate);
Exist = Convert.ToInt32(obj) > 0 ? true : false;
return Exist;
}
}
#endregion
}
Reference in New Issue Copy Permalink