766 lines
45 KiB
C#
766 lines
45 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 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
|
|
}
|