4092 lines
215 KiB
C#
4092 lines
215 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using Ease.CoreV35.DataAccess;
|
|
using System.Data;
|
|
using HRM.BO;
|
|
using Ease.Core.DataAccess;
|
|
using System.Security.Policy;
|
|
|
|
namespace HRM.DA
|
|
{
|
|
#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, AttenType, Comments,Reason, IsManualEntry,IsLate, LateHour, EarlyHour, OTHour, ReferenceID, CreatedBy, CreatedDate, OtRemarks, BenefitRemarks, TempShiftID, WFStatus, ApprovedOTHOUR, LMRemarks, DHRemarks, SalaryMonth, ActualInTime, ActualOutTime, CLAIMWFSTATUS)" +
|
|
// " VALUES(%n, %n, %d, %n, %D, %D, %n, %n, %s,%s,%b, %b, %n, %n, %n, %n, %n, %d, %s, %s, %n,%n,%n, %s, %s, %d,%D,%D, %n)",
|
|
// item.ID, item.EmployeeID, item.AttnDate, DataReader.GetNullValue(item.ShiftID),
|
|
// DataReader.GetNullValue(item.EmpInTime), DataReader.GetNullValue(item.EmpOutTime), item.WorkDayType,
|
|
// item.AttenType, item.Comments, item.Reason, item.IsManualEntry, item.IsLate, item.LateHour,
|
|
// item.EarlyHour, item.OTHour, DataReader.GetNullValue(item.ReferenceID, 0), item.CreatedBy,
|
|
// item.CreatedDate, item.OtRemarks, item.BenefitRemarks, item.TempShiftID,
|
|
// item.WFStatus, item.ApprovedOTHour, item.LMRemarks, item.DHRemarks, item.SalaryMonth, item.ActualInTime,
|
|
// item.ActualOutTime, (int)EnumClaimWFStatus.None);
|
|
//}
|
|
|
|
#endregion
|
|
|
|
#region Update function
|
|
|
|
//internal static void Update(TransactionContext tc, DailyAttnProcess item)
|
|
//{
|
|
// string sql = SQLParser.MakeSQL(
|
|
// "UPDATE DailyAttnProcess SET EmployeeID=%n, AttnDate=%d, ShiftID=%n, InTime=%D, OutTime=%D,WorkDayType=%n, AttenType=%n, Comments=%s,Reason=%s, IsManualEntry=%b,IsLate=%b, LateHour=%n, EarlyHour=%n, OTHour=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%d, OtRemarks=%s, BenefitRemarks=%s, TempShiftID=%n, WFStatus=%n, ApprovedOTHOUR=%n, LMRemarks=%s, DHRemarks=%s, SalaryMonth=%d, ActualInTime=%D, ActualOutTime=%D, ClaimWFStatus=%n" +
|
|
// " WHERE DailyAttnProcessID=%n", item.EmployeeID, item.AttnDate,
|
|
// item.ShiftID, item.InTime, item.OutTime, item.WorkDayType, item.AttenType,
|
|
// item.Comments, DataReader.GetNullValue(item.Reason), item.IsManualEntry,
|
|
// item.IsLate, item.LateHour, item.EarlyHour, item.OTHour, DataReader.GetNullValue(item.ReferenceID, 0),
|
|
// item.ModifiedBy, item.ModifiedDate, item.OtRemarks, item.BenefitRemarks,
|
|
// item.TempShiftID, item.WFStatus, item.ApprovedOTHour, item.LMRemarks,
|
|
// item.DHRemarks, item.SalaryMonth, item.ActualInTime, item.ActualOutTime, item.ClaimWFStatus, item.ID);
|
|
// tc.ExecuteNonQuery(
|
|
// "UPDATE DailyAttnProcess SET EmployeeID=%n, AttnDate=%d, ShiftID=%n, InTime=%D, OutTime=%D,WorkDayType=%n, AttenType=%n, Comments=%s,Reason=%s, IsManualEntry=%b,IsLate=%b, LateHour=%n, EarlyHour=%n, OTHour=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%d, OtRemarks=%s, BenefitRemarks=%s, TempShiftID=%n, WFStatus=%n, ApprovedOTHOUR=%n, LMRemarks=%s, DHRemarks=%s, SalaryMonth=%d, ActualInTime=%D, ActualOutTime=%D, ClaimWFStatus=%n" +
|
|
// " WHERE DailyAttnProcessID=%n", item.EmployeeID, item.AttnDate,
|
|
// item.ShiftID, item.InTime, item.OutTime, item.WorkDayType, item.AttenType,
|
|
// DataReader.GetNullValue(item.Comments), DataReader.GetNullValue(item.Reason), item.IsManualEntry,
|
|
// item.IsLate, item.LateHour, item.EarlyHour, item.OTHour, DataReader.GetNullValue(item.ReferenceID, 0),
|
|
// item.ModifiedBy, item.ModifiedDate, item.OtRemarks, item.BenefitRemarks,
|
|
// item.TempShiftID, item.WFStatus, item.ApprovedOTHour, item.LMRemarks,
|
|
// item.DHRemarks, item.SalaryMonth, item.ActualInTime, item.ActualOutTime, item.ClaimWFStatus, item.ID);
|
|
//}
|
|
|
|
internal static void UpdateEmployeeAttendance(TransactionContext tc, DailyAttnProcess item)
|
|
{
|
|
tc.ExecuteNonQuery(
|
|
"UPDATE DAILYATTNPROCESS SET SHIFTID = %n, INTIME = %d, OUTTIME = %d, OTHOUR = %n WHERE EMPLOYEEID = %n AND ATTNDATE= %d ",
|
|
item.ShiftID, item.InTime, item.OutTime, item.OTHour, item.EmployeeID, item.AttnDate);
|
|
}
|
|
|
|
internal static void ManualEditUpdate(TransactionContext tc, DailyAttnProcess item)
|
|
{
|
|
tc.ExecuteNonQuery(
|
|
"UPDATE DailyAttnProcess SET ShiftID=%n, InTime=%D, OutTime=%D, " +
|
|
"ActualInTime=%D, ActualOutTime=%D,AttenType=%n, Comments=%s,empRemarks=%s, IsManualEntry=%b,IsLate=%b, LateHour=%n, EarlyHour=%n, " +
|
|
"OTHour=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%D, WorkHour=%n" +
|
|
"" +
|
|
" WHERE DailyAttnProcessID=%n and EmployeeID=%n AND AttnDate=%d",
|
|
item.ShiftID, item.InTime, item.OutTime, item.ActualInTime, item.ActualOutTime, item.AttenType,
|
|
DataReader.GetNullValue(item.Comments), DataReader.GetNullValue(item.EmpRemarks), item.IsManualEntry,
|
|
item.IsLate, item.LateHour, item.EarlyHour, item.OTHour, item.ReferenceID,
|
|
item.ModifiedBy, item.ModifiedDate, item.WorkHour, item.ID, item.EmployeeID, item.AttnDate);
|
|
}
|
|
|
|
internal static void UpdateShiftID(TransactionContext tc, DateTime dtAtt, int employeeID, int nPayrollTypeID, int currentPayrollTypeID)
|
|
{
|
|
switch (currentPayrollTypeID)
|
|
{
|
|
case 2:
|
|
if (nPayrollTypeID == 7)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(@"UPDATE DAILYATTNPROCESS
|
|
SET ShiftID=
|
|
(CASE
|
|
WHEN ShiftID=2 THEN 20
|
|
WHEN ShiftID=5 THEN 22
|
|
WHEN ShiftID=3 THEN 21
|
|
WHEN ShiftID=6 THEN 23
|
|
WHEN ShiftID=7 THEN 24
|
|
WHEN ShiftID=12 THEN 25
|
|
WHEN ShiftID=13 THEN 26
|
|
WHEN ShiftID=19 THEN 27
|
|
ELSE ShiftID
|
|
END )
|
|
WHERE EMPLOYEEID IN (SELECT EMPLOYEEID FROM EMPLOYEE WHERE EmployeeID = %n And PAYROLLTYPEID = %n) AND ATTNDATE >= %d
|
|
AND SHIFTID IN (2,3,5,6,7,12,13,19)", employeeID, nPayrollTypeID, dtAtt);
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
else if (nPayrollTypeID == 3)
|
|
{
|
|
tc.ExecuteNonQuery(@"UPDATE DAILYATTNPROCESS
|
|
SET ShiftID=
|
|
CASE
|
|
WHEN ShiftID=2 THEN 28
|
|
WHEN ShiftID=5 THEN 30
|
|
WHEN ShiftID=3 THEN 29
|
|
WHEN ShiftID=14 THEN 33
|
|
WHEN ShiftID=12 THEN 31
|
|
WHEN ShiftID=13 THEN 32
|
|
WHEN ShiftID=15 THEN 34
|
|
ELSE ShiftID
|
|
END
|
|
WHERE EMPLOYEEID IN (SELECT EMPLOYEEID FROM EMPLOYEE WHERE EmployeeID = %n And PAYROLLTYPEID = %n) AND ATTNDATE >= %d
|
|
AND SHIFTID IN (2,3,5,15,12,13,14)", employeeID, nPayrollTypeID, dtAtt);
|
|
}
|
|
break;
|
|
case 3:
|
|
if (nPayrollTypeID == 2)
|
|
{
|
|
tc.ExecuteNonQuery(@"UPDATE DAILYATTNPROCESS
|
|
SET ShiftID=
|
|
CASE
|
|
WHEN ShiftID=28 THEN 2
|
|
WHEN ShiftID=30 THEN 5
|
|
WHEN ShiftID=29 THEN 3
|
|
WHEN ShiftID=33 THEN 14
|
|
WHEN ShiftID=31 THEN 12
|
|
WHEN ShiftID=32 THEN 13
|
|
WHEN ShiftID=34 THEN 15
|
|
ELSE ShiftID
|
|
END
|
|
WHERE EMPLOYEEID IN (SELECT EMPLOYEEID FROM EMPLOYEE WHERE EmployeeID = %n And PAYROLLTYPEID = %n) AND ATTNDATE >= %d
|
|
AND SHIFTID IN (28,30,29,33,31,32,34)", employeeID, nPayrollTypeID, dtAtt);
|
|
}
|
|
else if (nPayrollTypeID == 7)
|
|
{
|
|
tc.ExecuteNonQuery(@"UPDATE DAILYATTNPROCESS
|
|
SET ShiftID=
|
|
CASE
|
|
WHEN ShiftID=28 THEN 20
|
|
WHEN ShiftID=30 THEN 22
|
|
WHEN ShiftID=29 THEN 21
|
|
WHEN ShiftID=31 THEN 23
|
|
WHEN ShiftID=32 THEN 26
|
|
ELSE ShiftID
|
|
END
|
|
WHERE EMPLOYEEID IN (SELECT EMPLOYEEID FROM EMPLOYEE WHERE EmployeeID = %n And PAYROLLTYPEID = %n) AND ATTNDATE >= %d
|
|
AND SHIFTID IN (28,30,29,31,32)", employeeID, nPayrollTypeID, dtAtt);
|
|
}
|
|
break;
|
|
case 7:
|
|
if (nPayrollTypeID == 2)
|
|
{
|
|
tc.ExecuteNonQuery(@"UPDATE DAILYATTNPROCESS
|
|
SET ShiftID=
|
|
CASE
|
|
WHEN ShiftID=20 THEN 2
|
|
WHEN ShiftID=22 THEN 5
|
|
WHEN ShiftID=21 THEN 3
|
|
WHEN ShiftID=23 THEN 6
|
|
WHEN ShiftID=24 THEN 7
|
|
WHEN ShiftID=25 THEN 12
|
|
WHEN ShiftID=26 THEN 13
|
|
WHEN ShiftID=27 THEN 19
|
|
ELSE ShiftID
|
|
END
|
|
WHERE EMPLOYEEID IN (SELECT EMPLOYEEID FROM EMPLOYEE WHERE EmployeeID = %n And PAYROLLTYPEID = %n) AND ATTNDATE >= %d
|
|
AND SHIFTID IN (20,22,21,23,24,25,26,27)", employeeID, nPayrollTypeID, dtAtt);
|
|
}
|
|
else if (nPayrollTypeID == 3)
|
|
{
|
|
tc.ExecuteNonQuery(@"UPDATE DAILYATTNPROCESS
|
|
SET ShiftID=
|
|
CASE
|
|
WHEN ShiftID=20 THEN 28
|
|
WHEN ShiftID=22 THEN 30
|
|
WHEN ShiftID=21 THEN 29
|
|
WHEN ShiftID=25 THEN 31
|
|
WHEN ShiftID=26 THEN 32
|
|
ELSE ShiftID
|
|
END
|
|
WHERE EMPLOYEEID IN (SELECT EMPLOYEEID FROM EMPLOYEE WHERE EmployeeID = %n And PAYROLLTYPEID = %n) AND ATTNDATE >= %d
|
|
AND SHIFTID IN (20,22,21,25,26)", employeeID, nPayrollTypeID, dtAtt);
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
internal static void UpdateLeaveClearReason(TransactionContext tc, int employeeID, int leaveID, int userid, DateTime leavefromDate, DateTime leaveToDate)
|
|
{
|
|
tc.ExecuteNonQuery(
|
|
"UPDATE DailyAttnProcess SET Reason='', AttenType=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%D" +
|
|
" WHERE EmployeeID=%n AND AttnDate BETWEEN %d and %d and WORKDAYTYPE=%n", EnumAttendanceType.Leave,
|
|
leaveID, userid, DateTime.Now, employeeID, leavefromDate, leaveToDate, EnumWorkPlanDayType.WorkingDay);
|
|
}
|
|
internal static void UpdateLeave(TransactionContext tc, int employeeID, int leaveID, int userid, DateTime leavefromDate, DateTime leaveToDate)
|
|
{
|
|
tc.ExecuteNonQuery(
|
|
"UPDATE DailyAttnProcess SET AttenType=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%D" +
|
|
" WHERE EmployeeID=%n AND AttnDate BETWEEN %d and %d and WORKDAYTYPE=%n", EnumAttendanceType.Leave,
|
|
leaveID, userid, DateTime.Now, employeeID, leavefromDate, leaveToDate, EnumWorkPlanDayType.WorkingDay);
|
|
}
|
|
internal static void RevertLeaveStatus(TransactionContext tc, int employeeID, int leaveID, int userid, DateTime leavefromDate, DateTime leaveToDate)
|
|
{
|
|
tc.ExecuteNonQuery(
|
|
"UPDATE DailyAttnProcess SET AttenType=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%D" +
|
|
" WHERE EmployeeID=%n AND AttnDate BETWEEN %d and %d and WORKDAYTYPE=%n and AttenType=%n AND Intime is null and OutTime is null", EnumAttendanceType.Absent,
|
|
null, userid, DateTime.Now, employeeID, leavefromDate, leaveToDate, EnumWorkPlanDayType.WorkingDay, EnumAttendanceType.Leave);
|
|
|
|
tc.ExecuteNonQuery(
|
|
"UPDATE DailyAttnProcess SET AttenType=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%D" +
|
|
" WHERE EmployeeID=%n AND AttnDate BETWEEN %d and %d and WORKDAYTYPE=%n and AttenType=%n AND (Intime is not null OR OutTime is not null)",
|
|
EnumAttendanceType.Present, null, userid, DateTime.Now, employeeID, leavefromDate, leaveToDate, EnumWorkPlanDayType.WorkingDay, EnumAttendanceType.Leave);
|
|
|
|
}
|
|
|
|
internal static void UndoLeave(TransactionContext tc, int employeeID, int userid, DateTime leaveFromDate, DateTime leaveToDate)
|
|
{
|
|
string sSql = SQLParser.MakeSQL(
|
|
"UPDATE DailyAttnProcess SET AttenType=" +
|
|
" CASE WHEN InTime is Null and OutTime is null THEN %n ELSE %n END" +
|
|
"" +
|
|
", ReferenceID=null, ModifiedBy=%n, ModifiedDate=%D" +
|
|
" WHERE EmployeeID=%n AND AttnDate BETWEEN %d and %d and WORKDAYTYPE=%n", EnumAttendanceType.Absent, EnumAttendanceType.Present,
|
|
userid, DateTime.Now, employeeID, leaveFromDate, leaveToDate, EnumWorkPlanDayType.WorkingDay);
|
|
tc.ExecuteNonQuery(sSql);
|
|
}
|
|
|
|
internal static void Delete(TransactionContext tc, string empIDs, DateTime attnDate)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM [DailyAttnProcess] WHERE EmployeeID in(%q) AND AttnDate=%d", empIDs, attnDate);
|
|
}
|
|
|
|
internal static IDataReader getMyNotSubmittedAbsentList(TransactionContext tc, int employeeid)
|
|
{
|
|
|
|
string sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess" +
|
|
" where EmployeeID = %n and WFStatus=%n Order By AttnDate DESC",
|
|
employeeid, false);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetAttnDataByWFStatus(TransactionContext tc, int DailyAttnPocessID,
|
|
EnumWFAttnStatus status)
|
|
{
|
|
string sql = string.Empty;
|
|
if (status == EnumWFAttnStatus.None)
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess" +
|
|
" where DAILYATTNPROCESSID = %n and WFStatus in (0,5) Order By AttnDate asc",
|
|
DailyAttnPocessID);
|
|
else if (status == EnumWFAttnStatus.LMApproved)
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess" +
|
|
" where DAILYATTNPROCESSID = %n and WFStatus in (2,3) Order By AttnDate asc",
|
|
DailyAttnPocessID);
|
|
else
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess" +
|
|
" where DAILYATTNPROCESSID = %n and WFStatus=%n Order By AttnDate asc",
|
|
DailyAttnPocessID, status);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
//internal static void UpdateLateAttnBenefitStatus(TransactionContext tc, DailyAttnProcess dapItem)
|
|
//{
|
|
// tc.ExecuteNonQuery(
|
|
// "UPDATE DailyAttnProcess SET SalaryMonth=%d, BenefitRemarks=%s, ModifiedBy=%n, ModifiedDate=%d" +
|
|
// " WHERE DailyAttnProcessID=%n", dapItem.SalaryMonth, dapItem.BenefitRemarks, dapItem.ModifiedBy,
|
|
// dapItem.ModifiedDate, dapItem.ID);
|
|
//}
|
|
|
|
internal static IDataReader GetManualProcess(TransactionContext tc, DateTime attnDate, int payrollType)
|
|
{
|
|
return tc.ExecuteReader(
|
|
"SELECT dap.* FROM DailyAttnProcess dap INNER JOIN EMPLOYEE e on e.EmployeeID=dap.EmployeeID WHERE dap.AttnDate=%d And dap.IsManualEntry=1 AND e.Status=%n AND e.PayrollTypeID=%n ",
|
|
attnDate, EnumStatus.Active, payrollType);
|
|
}
|
|
|
|
public static DataSet GetPendingJobDetail(TransactionContext tc, int id)
|
|
{
|
|
DataSet dataSet = null;
|
|
//string sql = SQLParser.MakeSQL(@"select NVL(e.Name, '') Applier, NVL(lv.Description, '') Leave,
|
|
// TO_CHAR(APPFromDate,'DD Mon YYYY') FromDate,
|
|
// TO_CHAR(APPToDate,'DD Mon YYYY') ToDate,
|
|
// APPTotalDays TotalDays, Remarks
|
|
// from Leaveentry le
|
|
// Left Join Employee e
|
|
// on e.EmployeeID = le.EmpID
|
|
// Left Join Leave lv
|
|
// on lv.LeaveID = le.LeaveID
|
|
// Where TranID = %n ", id);
|
|
//dataSet = tc.ExecuteDataSet(sql);
|
|
return dataSet;
|
|
}
|
|
|
|
|
|
internal static IDataReader GetBySalaryMonth(TransactionContext tc, DateTime salaryMonth, int payrollTypeID)
|
|
{
|
|
return tc.ExecuteReader(@"SELECT dap.* FROM DailyAttnProcess dap
|
|
inner join Employee e on e.employeeid=dap.employeeid
|
|
WHERE dap.salaryMonth=%d and e.payrolltypeid=%n Order by e.employeeNo",
|
|
salaryMonth, payrollTypeID);
|
|
}
|
|
|
|
internal static DataTable GetTeamMembersAttnData(TransactionContext tc, int employeeID, DateTime fromdate,
|
|
DateTime toDate, EnumClaimWFStatus status)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(
|
|
@"SELECT dap.DAILYATTNPROCESSID DailyAttnID, dap.EmployeeID, e.EmployeeNo, e.Name EmpName, d.DESCRIPTION Department, wpg.Name Shift,
|
|
CAST(AttnDate as DATE) AttnDate,
|
|
CASE
|
|
WHEN dap.AttenType = 0 THEN 'NONE'
|
|
WHEN dap.AttenType = 1 THEN 'Present'
|
|
WHEN dap.AttenType = 2 THEN 'Absent'
|
|
WHEN dap.AttenType = 3 THEN 'Delay'
|
|
WHEN dap.AttenType = 11 THEN 'Late'
|
|
WHEN dap.AttenType = 12 THEN 'Early'
|
|
WHEN dap.AttenType = 4 THEN 'Leave'
|
|
WHEN dap.AttenType = 5 THEN 'Holiday'
|
|
WHEN dap.AttenType = 7 THEN 'OutSideDuty'
|
|
WHEN dap.AttenType = 8 THEN 'WeeklyHoliday'
|
|
WHEN dap.AttenType = 10 THEN 'Training'
|
|
WHEN dap.AttenType = 11 THEN 'Project'
|
|
ELSE ''
|
|
END AttenType,
|
|
s.NAME Relay,
|
|
CAST(dap.intime AS TIME) InTime,
|
|
CAST(dap.outtime as Time) OutTime,
|
|
CAST(dap.EMPREQUESTINTIME AS TIME) EMPREQUESTINTIME,
|
|
CAST(dap.EMPREQUESTOUTTIME as Time) EMPREQUESTOUTTIME,
|
|
CASE
|
|
WHEN dap.WORKDAYTYPE = 1 THEN 'WeeklyHoliday'
|
|
WHEN dap.WORKDAYTYPE = 2 THEN 'NationalHoliday'
|
|
WHEN dap.WORKDAYTYPE = 3 THEN 'WorkingDay'
|
|
WHEN dap.WORKDAYTYPE = 4 THEN 'HartalDay'
|
|
ELSE ''
|
|
END WORKDAYTYPE,
|
|
dap.EMPREMARKS
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN SHIFT s ON s.SHIFTID = dap.SHIFTID
|
|
Inner join Employee e on e.EmployeeID=dap.EmployeeID
|
|
Inner join Department d on d.DepartmentID=e.DepartmentID
|
|
Inner join EmployeeWorkPlanSetup empWps on empWps.EmployeeID=e.EmployeeID
|
|
Inner join WorkPlanGroup wpg on wpg.WorkPlanGroupID= empWps.WorkPlanGroupID
|
|
WHERE dap.EMPLOYEEID in (SELECT EmployeeID FROM Employee WHERE LINEMANAGERID=%n and Status = %n and PayrollTypeID =%n) AND dap.CLAIMWFSTATUS =%n and dap.ATTNDATE BETWEEN %d AND %d ORDER BY dap.ATTNDATE desc",
|
|
employeeID, EnumEmployeeStatus.Live, PayrollTypeIDConstant.Non_Management, status, fromdate,
|
|
toDate);
|
|
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return presentDays.Tables[0];
|
|
}
|
|
|
|
internal static DataTable GetTeamMembersAttnData(TransactionContext tc, string sEmpID, DateTime fromdate,
|
|
DateTime toDate, EnumClaimWFStatus status)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(
|
|
@"SELECT dap.DAILYATTNPROCESSID DailyAttnID, dap.EmployeeID, e.EmployeeNo, e.Name EmpName, d.DESCRIPTION Department, wpg.Name Shift,
|
|
CAST(AttnDate as DATE) AttnDate,
|
|
CASE
|
|
WHEN dap.AttenType = 0 THEN 'NONE'
|
|
WHEN dap.AttenType = 1 THEN 'Present'
|
|
WHEN dap.AttenType = 2 THEN 'Absent'
|
|
WHEN dap.AttenType = 3 THEN 'Delay'
|
|
WHEN dap.AttenType = 11 THEN 'Late'
|
|
WHEN dap.AttenType = 12 THEN 'Early'
|
|
WHEN dap.AttenType = 4 THEN 'Leave'
|
|
WHEN dap.AttenType = 5 THEN 'Holiday'
|
|
WHEN dap.AttenType = 7 THEN 'OutSideDuty'
|
|
WHEN dap.AttenType = 8 THEN 'WeeklyHoliday'
|
|
WHEN dap.AttenType = 10 THEN 'Training'
|
|
WHEN dap.AttenType = 11 THEN 'Project'
|
|
ELSE ''
|
|
END AttenType,
|
|
s.NAME Relay,
|
|
CAST(dap.intime AS TIME) InTime,
|
|
CAST(dap.outtime as Time) OutTime,
|
|
CAST(dap.EMPREQUESTINTIME AS TIME) EMPREQUESTINTIME,
|
|
CAST(dap.EMPREQUESTOUTTIME as Time) EMPREQUESTOUTTIME,
|
|
CASE
|
|
WHEN dap.WORKDAYTYPE = 1 THEN 'WeeklyHoliday'
|
|
WHEN dap.WORKDAYTYPE = 2 THEN 'NationalHoliday'
|
|
WHEN dap.WORKDAYTYPE = 3 THEN 'WorkingDay'
|
|
WHEN dap.WORKDAYTYPE = 4 THEN 'HartalDay'
|
|
ELSE ''
|
|
END WORKDAYTYPE,
|
|
dap.EMPREMARKS
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN SHIFT s ON s.SHIFTID = dap.SHIFTID
|
|
Inner join Employee e on e.EmployeeID=dap.EmployeeID
|
|
Inner join Department d on d.DepartmentID=e.DepartmentID
|
|
Inner join EmployeeWorkPlanSetup empWps on empWps.EmployeeID=e.EmployeeID
|
|
Inner join WorkPlanGroup wpg on wpg.WorkPlanGroupID= empWps.WorkPlanGroupID
|
|
WHERE dap.EMPLOYEEID in (%q) AND dap.CLAIMWFSTATUS =%n and dap.ATTNDATE BETWEEN %d AND %d ORDER BY dap.ATTNDATE desc",
|
|
sEmpID, EnumEmployeeStatus.Live, PayrollTypeIDConstant.Non_Management, status, fromdate, toDate);
|
|
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return presentDays.Tables[0];
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, string empID, DateTime fromDate, DateTime toDate,
|
|
EnumClaimWFStatus status)
|
|
{
|
|
string sTableName;
|
|
empID = IDHelper.GetIDs(tc, empID, out sTableName);
|
|
|
|
string sql = SQLParser.MakeSQL(
|
|
"SELECT dap.* FROM DailyAttnProcess dap Inner Join Employee e on e.EmployeeID=dap.EmployeeID WHERE dap.EmployeeID IN(%q) AND dap.AttnDate BETWEEN %d AND %d and dap.CLAIMWFSTATUS =%n order by e.Employeeno ASC",
|
|
empID, fromDate, toDate, status);
|
|
IDataReader dr = tc.ExecuteReader(sql);
|
|
|
|
if (sTableName != "")
|
|
tc.ExecuteNonQuery("Drop TAble %q", sTableName);
|
|
return dr;
|
|
}
|
|
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 GetshotList(TransactionContext tc, DateTime fromDate, DateTime toDate)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT DailyAttnProcessID, EmployeeID," +
|
|
" AttnDate, ShiftID, InTime, OutTime, WorkDayType, AttenType, " +
|
|
"IsLate, LateHour, EarlyHour, WorkHour, OTHour FROM DailyAttnProcess WHERE AttnDate BETWEEN %d AND %d order by AttnDate ASC", fromDate, toDate);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetshotList(TransactionContext tc, DateTime fromDate, DateTime toDate, int empid)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT DailyAttnProcessID, EmployeeID," +
|
|
" AttnDate, ShiftID, InTime, OutTime, WorkDayType, AttenType, " +
|
|
"IsLate, LateHour, EarlyHour, WorkHour, OTHour FROM DailyAttnProcess WHERE " +
|
|
" Employeeid=%n and AttnDate BETWEEN %d AND %d order by AttnDate ASC", empid, fromDate, toDate);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
internal static IDataReader GetshotList(TransactionContext tc, DateTime attnDate)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT DailyAttnProcessID, EmployeeID," +
|
|
" AttnDate, ShiftID, InTime, OutTime, WorkDayType, AttenType, " +
|
|
"IsLate, LateHour, EarlyHour, WorkHour, OTHour FROM DailyAttnProcess WHERE AttnDate = %d order by AttnDate ASC", attnDate);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetshotListForProdBonus(TransactionContext tc, string prodSetupIDs, DateTime fromDate, DateTime toDate)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"
|
|
IF OBJECT_ID('tempdb..#AttnofMonths') IS NOT NULL
|
|
DROP TABLE #AttnofMonths
|
|
select pt.EmployeeID, convert(varchar, FORMAT(pt.inTime,'dd MMM yyyy'), 1) as pdInTime into #AttnofMonths from ProdBonusAttn pt Inner join
|
|
ProductionBonusSetup pb on pt.ProdBonusSetupID = pb.ProductionBonusSetupID
|
|
where pb.ProductionBonusSetupID in (%q) and pt.inTime is not null;
|
|
SELECT DailyAttnProcessID, #AttnofMonths.EmployeeID,
|
|
AttnDate, ShiftID, InTime, OutTime, WorkDayType, AttenType,
|
|
IsLate, LateHour, EarlyHour, WorkHour, OTHour
|
|
FROM DailyAttnProcess inner join #AttnofMonths on DailyAttnProcess.EMPLOYEEID =#AttnofMonths.EmployeeID
|
|
and #AttnofMonths.pdInTime = DailyAttnProcess.AttnDate Where intime is not null and OutTime is not null
|
|
and(AttenType = %n or AttenType = %n or AttenType = %n or AttenType = %n or AttenType = %n or AttenType = %n) ;
|
|
DROP TABLE #AttnofMonths ", prodSetupIDs, EnumAttendanceType.Present, EnumAttendanceType.Delay, EnumAttendanceType.OutSideDuty, EnumAttendanceType.Late, EnumAttendanceType.Early, EnumAttendanceType.HalfDay
|
|
);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
internal static void UpdateLeave(TransactionContext tc, int employeeID, int leaveID, DateTime leavaDate,
|
|
EnumAttendanceType AttenType)
|
|
{
|
|
//if (n == 1)
|
|
//{
|
|
tc.ExecuteNonQuery(
|
|
"UPDATE DailyAttnProcess SET AttenType=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%d" +
|
|
" WHERE EmployeeID=%n AND AttnDate=%d ", AttenType, leaveID, 1, DateTime.Now, employeeID, leavaDate);
|
|
//}
|
|
//else
|
|
//{
|
|
// tc.ExecuteNonQuery("UPDATE BuyerDailyAttProcess SET InTime=null, OutTime=null, OTHour = 0.0,WorkDayType=%n, AttenType=%n, ReferenceID=%n, ModifiedBy=%n, ModifiedDate=%d" +
|
|
// " WHERE EmployeeID=%n AND AttnDate=%d ", EnumAttendanceType.Leave, leaveID, User.CurrentUser.ID, DateTime.Now, employeeID, leavaDate);
|
|
//}
|
|
}
|
|
|
|
internal static void RejectAttnStatusByLM(TransactionContext tc, int EmpID, DateTime attnDate, int linemanagerID,
|
|
string remarks, string reason)
|
|
{
|
|
|
|
tc.ExecuteNonQuery(
|
|
"UPDATE DailyAttnProcess SET wFStatus=%n, LinemanagerId=%n, REASON=%s, LMREMARKS=%s, lMApproveDate=%D, IsManualEntry=%b" +
|
|
" WHERE EmployeeID=%n AND AttnDate=%d ", EnumWFAttnStatus.Reject, linemanagerID, reason, remarks, DateTime.Today, true,
|
|
EmpID, attnDate);
|
|
}
|
|
|
|
internal static void ApproveAttnByLM(TransactionContext tc, DailyAttnProcess item)
|
|
{
|
|
|
|
string sql = SQLParser.MakeSQL(
|
|
"UPDATE DailyAttnProcess SET Intime=%D, outTime=%D, shiftID=%n, ActualInTime=%D, ActualOutTime=%D, ActualShiftID=%n" +
|
|
" ,AttenType=%n , LinemanagerId=%n, REASON=%s, LMREMARKS=%s, lMApproveDate=%D, wfStatus=%n, isLate=%b, IsManualEntry=%b, " +
|
|
" LateHour=%n, EarlyHour=%n, WorkHour=%n, OTHour=%n WHERE EmployeeID=%n AND AttnDate=%d ", item.InTime, item.OutTime,
|
|
item.ShiftID, item.ActualInTime, item.ActualOutTime, item.ActualShiftID, item.AttenType,
|
|
item.LineManagerID, item.Reason, item.LMRemarks, DateTime.Today, EnumWFAttnStatus.LMApproved, item.IsLate, item.IsManualEntry,
|
|
item.LateHour, item.EarlyHour, item.WorkHour, item.OTHour, item.EmployeeID, item.AttnDate);
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
|
|
internal static void RegularizationRequestbyEmp(TransactionContext tc, DailyAttnProcess item)
|
|
{
|
|
|
|
tc.ExecuteNonQuery(
|
|
"UPDATE DailyAttnProcess SET ActualInTime=%D, ActualOutTime=%D, EMPAPPLYDATE=%D, " +
|
|
"wfStatus=%n, EmpRemarks=%s , IsManualEntry=%b " +
|
|
"WHERE EmployeeID=%n AND AttnDate=%d ", item.ActualInTime, item.ActualOutTime, DateTime.Now, EnumWFAttnStatus.EmpSubmitted,
|
|
item.EmpRemarks, true, item.EmployeeID, item.AttnDate);
|
|
}
|
|
|
|
internal static void UndoAttendanceStatus(TransactionContext tc, int employeeID, DateTime attnDate,
|
|
EnumAttendanceType AttenType)
|
|
{
|
|
tc.ExecuteNonQuery(
|
|
"UPDATE DailyAttnProcess SET AttenType=%n, ReferenceID=null, ModifiedBy=%n, ModifiedDate=%d" +
|
|
" WHERE EmployeeID=%n AND AttnDate=%d ", AttenType, 1, DateTime.Now, employeeID, attnDate);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Get Function
|
|
|
|
internal static IDataReader Get(TransactionContext tc)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess");
|
|
}
|
|
|
|
internal static IDataReader GetByEmployeeID(TransactionContext tc, int employeeID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess Where EmployeeID =%n", employeeID);
|
|
}
|
|
internal static IDataReader GetByEmployeeID(TransactionContext tc, int employeeID, DateTime fromDate, DateTime toDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess Where EmployeeID =%n and AttnDate BETWEEN %d AND %d and intime is not null and OutTime is not null order by AttnDate ASC", employeeID, fromDate, toDate);
|
|
}
|
|
internal static IDataReader GetAttnByEmployeeID(TransactionContext tc, int employeeID, DateTime fromDate, DateTime toDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess Where EmployeeID =%n and AttnDate BETWEEN %d AND %d and intime is not null and OutTime is not null " +
|
|
" and (AttenType = %n or AttenType = %n or AttenType = %n or AttenType = %n or AttenType = %n or AttenType = %n) order by AttnDate ASC", employeeID, fromDate, toDate, EnumAttendanceType.Present, EnumAttendanceType.Delay, EnumAttendanceType.OutSideDuty,
|
|
EnumAttendanceType.Late, EnumAttendanceType.Early, EnumAttendanceType.HalfDay);
|
|
}
|
|
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, int payrollTypeID)
|
|
{
|
|
tc.CommandTimeOut = 6000;
|
|
return tc.ExecuteReader(@"SELECT dap.* FROM DailyAttnProcess dap
|
|
inner join Employee e on e.employeeid=dap.employeeid
|
|
WHERE dap.AttnDate=%d and e.payrolltypeid=%n Order by e.employeeNo", attnDate,
|
|
payrollTypeID);
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, DateTime attnDate, string empids, int payrollTypeID)
|
|
{
|
|
return tc.ExecuteReader(@"SELECT dap.* FROM DailyAttnProcess dap
|
|
inner join Employee e on e.employeeid=dap.employeeid
|
|
WHERE dap.AttnDate=%d and e.payrolltypeid=%n and dap.employeeid in (%q)", attnDate,
|
|
payrollTypeID, empids);
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, int empId, DateTime attnDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE AttnDate=%d and EmployeeID=%n", attnDate,
|
|
empId);
|
|
}
|
|
|
|
internal static IDataReader GetforMobileWithNoLock(TransactionContext tc, int empId, DateTime attnDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE AttnDate=%d and EmployeeID=%n", attnDate,
|
|
empId);
|
|
}
|
|
|
|
internal static IDataReader GetWithinDateRange(TransactionContext tc, int empId, DateTime attnFromDate,
|
|
DateTime attnToDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE AttnDate between %d and %d and EmployeeID=%n",
|
|
attnFromDate, attnToDate, empId);
|
|
}
|
|
|
|
internal static IDataReader GetManualProcess(TransactionContext tc, int PayrollTypeID, DateTime attnDate)
|
|
{
|
|
return tc.ExecuteReader(
|
|
"SELECT dap.* FROM DailyAttnProcess dap INNER JOIN EMPLOYEE e on e.EmployeeID=dap.EmployeeID WHERE dap.AttnDate=%d And dap.IsManualEntry=1 AND e.Status=%n AND e.PayrollTypeID=%n ",
|
|
attnDate, EnumStatus.Active, PayrollTypeID);
|
|
}
|
|
|
|
//new
|
|
internal static IDataReader GetWhrOutTimeIsNull(TransactionContext tc, DateTime attnDate, int shiftID)
|
|
{
|
|
return tc.ExecuteReader(
|
|
"SELECT * FROM DailyAttnProcess WHERE AttnDate=%d AND ShiftID=%n AND Intime IS NOT NULL AND OutTime IS NULL",
|
|
attnDate, shiftID);
|
|
}
|
|
//
|
|
|
|
internal static IDataReader Get(TransactionContext tc, int empID, DateTime fromDate, DateTime toDate)
|
|
{
|
|
string sql =
|
|
SQLParser.MakeSQL(
|
|
"SELECT * FROM DailyAttnProcess WHERE EmployeeID=%n AND AttnDate BETWEEN %d AND %d order by AttnDate DESC",
|
|
empID, fromDate, toDate);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, string empID, DateTime fromDate, DateTime toDate)
|
|
{
|
|
string sql =
|
|
SQLParser.MakeSQL(
|
|
"SELECT dap.* FROM DailyAttnProcess dap Inner Join Employee e on e.EmployeeID=dap.EmployeeID WHERE dap.EmployeeID IN(%q) AND dap.AttnDate BETWEEN %d AND %d order by dap.AttnDate DESC",
|
|
empID, fromDate, toDate);
|
|
IDataReader dr = tc.ExecuteReader(sql);
|
|
return dr;
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, DateTime fromDate, DateTime toDate, int PayrollTypeID)
|
|
{
|
|
string sql = SQLParser.MakeSQL(
|
|
"SELECT dap.* FROM DailyAttnProcess dap Inner Join Employee e on e.EmployeeID=dap.EmployeeID WHERE dap.AttnDate BETWEEN %d AND %d AND e.Status=%n AND e.PayrollTypeID=%n order by AttnDate ASC",
|
|
fromDate, toDate, EnumStatus.Active, PayrollTypeID);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, DateTime attnDate, int shiftID,
|
|
EnumAttendanceType AttenType)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE AttnDate=%d AND ShiftID=%n AND AttenType=%n",
|
|
attnDate, shiftID, AttenType);
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, int nID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess WHERE DailyAttnProcessID=%n", nID);
|
|
}
|
|
internal static DataSet GetMonthlyAttnSummary(TransactionContext tc, DateTime dFromDate, DateTime dToDate,
|
|
string sEmpID)
|
|
{
|
|
string sumOTSql = "CAST( sum(ROUND(d.OTHour, 0)) + FLOOR( (sum(d.OTHour % 1) *100 /60)) AS money) + CAST((sum(d.OTHour % 1) *100 %60)/100 AS money) As TotalOT";
|
|
DataSet monthlyAttn = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"select e.EMPLOYEENO,
|
|
IsNull(ac.CardNumber,'') as CardNo,
|
|
e.NAME,
|
|
des.NAME As Designation,
|
|
g.DESCRIPTION As Grade,
|
|
'Department'= case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
|
END ,
|
|
'Floor'=case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 2 then ''
|
|
END ,
|
|
'Section'= case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line'=case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
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,
|
|
%q,
|
|
((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 GRADES g on g.GRADEID = e.GRADEID
|
|
LEFT JOIN AccessCard ac ON e.CardID = ac.AccessCardID
|
|
Left JOIN DEPARTMENT dept5 ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4 ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3 ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2 ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
left outer join
|
|
(select EmployeeID, count(DailyAttnProcessID) as Present
|
|
from DailyAttnProcess where WORKDAYTYPE = 3 AND AttenType 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 WORKDAYTYPE = 3 AND latehour>0 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 WORKDAYTYPE = 3 AND AttenType=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 WORKDAYTYPE IN( 1,2) 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 AttenType=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 AttenType=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 AttenType=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 AttenType=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,
|
|
dept2.Tire,dept2.[DESCRIPTION],dept3.Tire,dept3.[DESCRIPTION],dept4.Tire,dept4.[DESCRIPTION],dept5.Tire,dept5.[DESCRIPTION]
|
|
, e.JOININGDATE, e.GROSSSALARY, Pr.Present,Lt.Late,Ab.Absent,HD.Holiday,Lv.Leave, OD.OutsideDuty, dl.Delays, EL.Early",
|
|
sumOTSql, dFromDate, dToDate, sEmpID,
|
|
dFromDate, dToDate,
|
|
dFromDate, dToDate,
|
|
dFromDate, dToDate,
|
|
dFromDate, dToDate,
|
|
dFromDate, dToDate,
|
|
dFromDate, dToDate,
|
|
dFromDate, dToDate,
|
|
dFromDate, dToDate
|
|
);
|
|
|
|
monthlyAttn = tc.ExecuteDataSet("%q",sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return monthlyAttn;
|
|
}
|
|
|
|
internal static DataSet GetMonthlyAttn(TransactionContext tc, DateTime dFromDate, DateTime dToDate,
|
|
string sEmpID)
|
|
{
|
|
DataSet monthlyAttn = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(
|
|
"select e.EMPLOYEENO,NVL(ac.CardNumber,'') CardNo, e.NAME, des.NAME Designation, g.DESCRIPTION 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) float)/100) 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)) 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 GRADES g on g.GRADEID = e.GRADEID "
|
|
+ "LEFT JOIN AccessCard ac ON e.CardID = ac.AccessCardID "
|
|
+ "left outer join "
|
|
+ "("
|
|
+ "select EmployeeID, count(DailyAttnProcessID) Present "
|
|
+ "from DailyAttnProcess "
|
|
+ "where AttenType 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) Late "
|
|
+ "from DailyAttnProcess "
|
|
+ "where AttenType=11 and AttnDate between %d and %d "
|
|
+ "group by EmployeeID "
|
|
+ ") Lt on d.EmployeeID=Lt.EmployeeID "
|
|
+ "left outer join "
|
|
+ "("
|
|
+ "select EmployeeID, count(DailyAttnProcessID) Absent "
|
|
+ "from DailyAttnProcess "
|
|
+ "where AttenType=2 and AttnDate between %d and %d "
|
|
+ "group by EmployeeID "
|
|
+ ") Ab on d.EmployeeID=Ab.EmployeeID "
|
|
+ "left outer join "
|
|
+ "("
|
|
+ "select EmployeeID, count(DailyAttnProcessID) Holiday "
|
|
+ "from DailyAttnProcess "
|
|
+ "where AttenType 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) Leave "
|
|
+ "from DailyAttnProcess "
|
|
+ "where AttenType=4 and AttnDate between %d and %d "
|
|
+ "group by EmployeeID "
|
|
+ ") Lv on d.EmployeeID=Lv.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", "%", dFromDate, dToDate, sEmpID, 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 e.EMPLOYEENO,ISNULL(ac.CardNumber,'') CardNo, e.Name, deg.NAME Designation,cg.DESCRIPTION EmpType,
|
|
d.AttenType Status, d.Comments,d.InTime,d.OutTime,d.OTHour,ISNULL(s.ShortName,'') Shift,
|
|
dept.DEPARTMENTID DeptID,dept.DESCRIPTION DeptName,d.ReferenceID
|
|
from (SELECT * FROM DailyAttnProcess
|
|
WHERE AttnDate=%d AND AttenType=1 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 Order By e.EMPLOYEENO asc", attnDate,
|
|
sEmpID);
|
|
dailyInOut = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return dailyInOut;
|
|
}
|
|
|
|
internal static DataSet GetLMDailyAttnReport(TransactionContext tc, DateTime attnDate, string sEmpID)
|
|
{
|
|
DataSet lmAttnReport = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"SELECT
|
|
emp.EmployeeNo, emp.Name, dsg.name AS DESIGNATION, sft.Name AS Shift,
|
|
sft.shortname AS ShiftGroup,
|
|
dapp.InTime, dapp.OutTime, dapp.AttenType
|
|
FROM Shift sft
|
|
INNER JOIN WorkPlanGroup wp
|
|
ON wp.ShiftID = sft.ShiftID
|
|
INNER JOIN EmployeeWorkPlanSetup ews
|
|
ON ews.WorkPlanGroupID = wp.WorkPlanGroupID
|
|
INNER JOIN EMPLOYEE emp
|
|
ON emp.employeeid = ews.EmployeeID
|
|
INNER JOIN DESIGNATION dsg
|
|
ON dsg.DESIGNATIONID = emp.EMPLOYEEID
|
|
INNER JOIN DailyAttnProcess dapp
|
|
ON emp.EMPLOYEEID = dapp.EmployeeID
|
|
WHERE emp.EmployeeID in (%q) AND dapp.AttnDate = %d", sEmpID,
|
|
attnDate);
|
|
lmAttnReport = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return lmAttnReport;
|
|
}
|
|
|
|
internal static DataSet GetTimeSheetAttnReport(TransactionContext tc, DateTime fromDate, DateTime toDate,
|
|
int sEmpID)
|
|
{
|
|
DataSet timeSheetAttnReport = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"SELECT
|
|
emp.EmployeeNo, emp.Name, dsg.name AS DESIGNATION, sft.Name AS Shift,
|
|
sft.shortname AS ShiftGroup, dapp.AttnDate,
|
|
dapp.InTime, dapp.OutTime, dapp.AttenType
|
|
FROM Shift sft
|
|
INNER JOIN WorkPlanGroup wp
|
|
ON wp.ShiftID = sft.ShiftID
|
|
INNER JOIN EmployeeWorkPlanSetup ews
|
|
ON ews.WorkPlanGroupID = wp.WorkPlanGroupID
|
|
INNER JOIN EMPLOYEE emp
|
|
ON emp.employeeid = ews.EmployeeID
|
|
INNER JOIN DESIGNATION dsg
|
|
ON dsg.DESIGNATIONID = emp.EMPLOYEEID
|
|
INNER JOIN DailyAttnProcess dapp
|
|
ON emp.EMPLOYEEID = dapp.EmployeeID
|
|
WHERE emp.EmployeeID = %n AND dapp.AttnDate between %d and %d", sEmpID,
|
|
fromDate, toDate);
|
|
timeSheetAttnReport = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return timeSheetAttnReport;
|
|
}
|
|
|
|
|
|
internal static DataSet GetPersonalInfoForDailyAttnReport(TransactionContext tc, int empID)
|
|
{
|
|
DataSet lmAttnReport = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"
|
|
SELECT DEPARTMENT.DESCRIPTION AS DEPARTMENT, LOCATION.DESCRIPTION AS LOCATION,
|
|
CRG.DESCRIPTION AS CostCenter
|
|
FROM EMPLOYEE
|
|
INNER JOIN DEPARTMENT ON DEPARTMENT.DEPARTMENTID = EMPLOYEE.DEPARTMENTID
|
|
INNER JOIN LOCATION ON LOCATION.LOCATIONID = EMPLOYEE.LOCATIONID
|
|
INNER JOIN EMPCOSTCENTER ON EMPCOSTCENTER.EMPLOYEEID = EMPLOYEE.EMPLOYEEID
|
|
INNER JOIN CRG ON CRG.CRGID = EMPCOSTCENTER.COSTCENTERID
|
|
WHERE Employee.EMPLOYEEID = %n AND EMPCOSTCENTER.CURRENTCC = 1
|
|
", empID);
|
|
lmAttnReport = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return lmAttnReport;
|
|
}
|
|
|
|
internal static DataSet GetPersonalInfoForTimeSheet(TransactionContext tc, int empID)
|
|
{
|
|
DataSet prsnlData = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"SELECT
|
|
ely.EmployeeNo, ely.Name, dapp.AttnDate, grd.description as Level,
|
|
dsg.name as Designation, loct.description as Location,
|
|
ely.joiningdate as JoiningDate, dept.description as Department
|
|
FROM EMPLOYEE ely
|
|
INNER JOIN DailyAttnProcess dapp
|
|
ON dapp.EmployeeID = ely.EMPLOYEEID
|
|
INNER JOIN GRADES grd
|
|
ON grd.GRADEID = ely.GRADEID
|
|
INNER JOIN LOCATION loct
|
|
ON loct.LOCATIONID = ely.LOCATIONID
|
|
INNER JOIN DEPARTMENT dept
|
|
ON ely.DEPARTMENTID = dept.DEPARTMENTID
|
|
INNER JOIN DESIGNATION dsg
|
|
ON dsg.DESIGNATIONID = ely.DESIGNATIONID
|
|
WHERE ely.EMPLOYEEID = %n", empID);
|
|
prsnlData = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return prsnlData;
|
|
}
|
|
|
|
|
|
internal static DataSet GetDailyInOut(TransactionContext tc, DateTime fromDate, DateTime toDate, string sEmpID)
|
|
{
|
|
DataSet dailyInOut = new DataSet();
|
|
try
|
|
{
|
|
string sTableName;
|
|
string machineName = Environment.MachineName;
|
|
machineName = machineName.Substring(0, 5) + DateTime.Now.ToString("hhmmss"); //Environment.MachineName;
|
|
|
|
// string sql = SQLParser.MakeSQL(@"select sg.get(e.EMPLOYEENO) EMPLOYEENO,NVL(ac.CardNumber,'') CardNo, sg.get(e.Name) Name, sg.get(deg.NAME) Designation,cg.DESCRIPTION EmpType,
|
|
// d.AttenType Status, d.Comments,d.InTime,d.OutTime,d.OTHour,NVL(s.ShortName,'') Shift,
|
|
// dept.DEPARTMENTID DeptID,dept.DESCRIPTION DeptName,d.ReferenceID, d.ATTNDATE ATTNDATE
|
|
// 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 Order By e.EMPLOYEENO, d.ATTNDATE asc", fromDate, toDate, sEmpID);
|
|
string sql = SQLParser.MakeSQL(@"select sg.get(e.EMPLOYEENO) EmployeeNo,
|
|
sg.get(e.Name) Name,
|
|
sg.get(deg.NAME) Designation,
|
|
CASE d.AttenType
|
|
WHEN 1 THEN 'Present'
|
|
WHEN 2 THEN 'Absent'
|
|
WHEN 3 THEN 'Delay'
|
|
WHEN 4 THEN 'Leave'
|
|
WHEN 5 THEN 'Holiday'
|
|
WHEN 7 THEN 'OutSideDuty'
|
|
WHEN 8 THEN 'WeeklyHoliday'
|
|
WHEN 11 THEN 'Late'
|
|
WHEN 12 THEN 'Early' END Status,
|
|
CASE d.WorkDayType
|
|
WHEN 1 THEN 'Rest Day'
|
|
WHEN 2 THEN 'National Holiday'
|
|
WHEN 3 THEN 'Working Day'
|
|
WHEN 4 THEN 'Hartal Day' END WorkDayType ,
|
|
d.InTime,
|
|
d.OutTime,
|
|
d.OTHour,
|
|
NVL(s.ShortName,'') Shift,
|
|
sg.get(e.EmailAddress) EmailAddress,
|
|
sg.get(e.MobileNO) MobileNO,
|
|
vwEmp.Division,
|
|
vwEmp.Department DeptName,
|
|
vwEmp.Unit,
|
|
d.ATTNDATE AttendanceDate,
|
|
EXTRACT(day FROM d.ATTNDATE) ""Day"",
|
|
CASE EXTRACT(month FROM d.ATTNDATE)
|
|
WHEN 1 THEN 'Jan'
|
|
WHEN 2 THEN 'Feb'
|
|
WHEN 3 THEN 'Mar'
|
|
WHEN 4 THEN 'Apr'
|
|
WHEN 5 THEN 'May'
|
|
WHEN 6 THEN 'Jun'
|
|
WHEN 7 THEN 'Jul'
|
|
WHEN 8 THEN 'Aug'
|
|
WHEN 9 THEN 'Sept'
|
|
WHEN 10 THEN 'Oct'
|
|
WHEN 11 THEN 'Nov'
|
|
WHEN 12 THEN 'Dec' END ""Month"",
|
|
EXTRACT(year FROM d.ATTNDATE) ""Year"",
|
|
vwEmp.LineManagerName LMName,
|
|
vwEmp.LineManagerNo LMNo,
|
|
vwEmp.LMEmailAddress LMEmail
|
|
from (SELECT * FROM DailyAttnProcess
|
|
WHERE AttnDate BETWEEN %d and %d And EmployeeID IN(%q)) D
|
|
LEFT Join Employee e
|
|
ON E.EMPLOYEEID = d.EmployeeID
|
|
LEFT Join DESIGNATION deg
|
|
ON deg.DESIGNATIONID = e.DESIGNATIONID
|
|
LEFT JOIN DEPARTMENT dept
|
|
ON dept.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN Shift s
|
|
ON s.ShiftID = D.ShiftID
|
|
LEFT JOIN VW_EMPLOYEEBASIC_%q vwEmp ON e.EMPLOYEEID = vwEmp.EMPLOYEEID Order By e.EMPLOYEENO, d.ATTNDATE asc",
|
|
fromDate, toDate, sEmpID, machineName);
|
|
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,NVL(ac.CardNumber,'') CardNo,e.Name,deg.NAME Designation,cg.DESCRIPTION EmpType,
|
|
// d.AttenType Status, d.Comments,d.InTime,d.OutTime,d.OTHour,NVL(s.ShortName,'') Shift,
|
|
// dept.DEPARTMENTID DeptID,dept.DESCRIPTION DeptName,d.ReferenceID
|
|
// from (SELECT * FROM DailyAttnProcess
|
|
// WHERE AttnDate=%d And AttenType 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 Order By e.EMPLOYEENO ASC", attnDate, sStatus, sEmpID);
|
|
// }
|
|
// else
|
|
// {
|
|
// sql = SQLParser.MakeSQL(@"select e.EMPLOYEENO,NVL(ac.CardNumber,'') CardNo, e.Name, deg.NAME Designation,cg.DESCRIPTION EmpType,
|
|
// d.AttenType Status, d.Comments,d.InTime,d.OutTime,d.OTHour,NVL(s.ShortName,'') Shift,
|
|
// dept.DEPARTMENTID DeptID,dept.DESCRIPTION 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 Order By e.EMPLOYEENO ASC", 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.AttenType as Status, d.Comments,d.InTime,d.OutTime,ISNULL(d.OTHour,0) OTHour,IsNULL(s.ShortName,'') as Shift,
|
|
d.attndate,
|
|
'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
|
END,
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
d.ReferenceID, dept5.DEPARTMENTID DeptID, dept5.[DESCRIPTION] DeptName
|
|
from (SELECT * FROM DailyAttnProcess
|
|
WHERE AttnDate=%d And AttenType 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
|
|
Left JOIN DEPARTMENT dept5
|
|
ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4
|
|
ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3
|
|
ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2
|
|
ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
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.AttenType as Status, d.Comments,d.InTime,d.OutTime,ISNULL(d.OTHour,0) OTHour,IsNULL(s.ShortName,'') as Shift,
|
|
d.attndate,
|
|
'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
|
END,
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
|
|
d.ReferenceID, dept5.DEPARTMENTID DeptID, dept5.[DESCRIPTION] DeptName
|
|
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
|
|
Left JOIN DEPARTMENT dept5
|
|
ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4
|
|
ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3
|
|
ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2
|
|
ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
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 GetDataByStatus(TransactionContext tc, DateTime fromDate, DateTime toDate, 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.AttenType as Status, d.Comments,d.InTime,d.OutTime,ISNULL(d.OTHour,0) OTHour,IsNULL(s.ShortName,'') as Shift,
|
|
d.attndate,
|
|
'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
|
END,
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
d.ReferenceID, dept5.DEPARTMENTID DeptID, dept5.[DESCRIPTION] DeptName
|
|
from (SELECT * FROM DailyAttnProcess
|
|
WHERE AttnDate BETWEEN %d AND %d And AttenType 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
|
|
Left JOIN DEPARTMENT dept5
|
|
ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4
|
|
ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3
|
|
ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2
|
|
ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
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
|
|
", fromDate, toDate, 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.AttenType as Status, d.Comments,d.InTime,d.OutTime,ISNULL(d.OTHour,0) OTHour,IsNULL(s.ShortName,'') as Shift,
|
|
d.attndate,
|
|
'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
|
END,
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
|
|
d.ReferenceID, dept5.DEPARTMENTID DeptID, dept5.[DESCRIPTION] DeptName
|
|
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
|
|
Left JOIN DEPARTMENT dept5
|
|
ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4
|
|
ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3
|
|
ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2
|
|
ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
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
|
|
", fromDate, toDate, sEmpID);
|
|
}
|
|
|
|
dailyInOut = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return dailyInOut;
|
|
}
|
|
|
|
|
|
internal static DataSet GetDateRangeWiseDataByStatusReport(TransactionContext tc, DateTime fromDate, DateTime toDate, 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.AttenType as Status, d.Comments,d.InTime,d.OutTime,ISNULL(d.OTHour,0) OTHour,IsNULL(s.ShortName,'') as Shift,
|
|
d.attndate,
|
|
'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
|
END,
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
d.ReferenceID, dept5.DEPARTMENTID DeptID, dept5.[DESCRIPTION] DeptName
|
|
from (SELECT * FROM DailyAttnProcess
|
|
WHERE AttnDate BETWEEN %d AND %d And AttenType 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
|
|
Left JOIN DEPARTMENT dept5
|
|
ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4
|
|
ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3
|
|
ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2
|
|
ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
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
|
|
", fromDate, toDate, 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.AttenType as Status, d.Comments,d.InTime,d.OutTime,ISNULL(d.OTHour,0) OTHour,IsNULL(s.ShortName,'') as Shift,
|
|
d.attndate,
|
|
'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
|
END,
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
|
|
d.ReferenceID, dept5.DEPARTMENTID DeptID, dept5.[DESCRIPTION] DeptName
|
|
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
|
|
Left JOIN DEPARTMENT dept5
|
|
ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4
|
|
ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3
|
|
ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2
|
|
ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
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
|
|
", fromDate, toDate, sEmpID);
|
|
}
|
|
|
|
dailyInOut = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return dailyInOut;
|
|
}
|
|
|
|
internal static DataSet GetDailyOddDataByStatusReport(TransactionContext tc, DateTime attnDate,
|
|
string sEmpID)
|
|
{
|
|
DataSet dailyInOut = new DataSet();
|
|
try
|
|
{
|
|
string sql = string.Empty;
|
|
|
|
sql = SQLParser.MakeSQL(
|
|
@"select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo,e.Name,deg.NAME as Designation,cg.DESCRIPTION AS EmpType,
|
|
d.AttenType as Status, d.Comments,d.InTime,d.OutTime,ISNULL(d.OTHour,0) OTHour,s.ShiftID as ShiftID,IsNULL(s.ShortName,'') as Shift,
|
|
d.attndate,
|
|
'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
|
END,
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
d.ReferenceID, dept5.DEPARTMENTID DeptID, dept5.[DESCRIPTION] DeptName
|
|
from DailyAttnProcess D
|
|
INNER Join Employee e
|
|
ON E.EMPLOYEEID = d.EmployeeID
|
|
INNER Join DESIGNATION deg
|
|
ON deg.DESIGNATIONID = e.DESIGNATIONID
|
|
Left JOIN DEPARTMENT dept5
|
|
ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4
|
|
ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3
|
|
ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2
|
|
ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
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
|
|
WHERE D.AttnDate = %d AND D.EmployeeID IN(%q)
|
|
AND (D.InTime IS NULL OR D.OutTime IS NULL)
|
|
AND NOT (D.InTime IS NULL AND D.OutTime IS NULL)
|
|
", attnDate, sEmpID);
|
|
|
|
|
|
dailyInOut = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return dailyInOut;
|
|
}
|
|
|
|
internal static DataSet GetDateWiseAttnRecords(TransactionContext tc, DateTime attnDate,
|
|
string sEmpID)
|
|
{
|
|
DataSet dailyInOut = new DataSet();
|
|
try
|
|
{
|
|
string sql = string.Empty;
|
|
|
|
sql = SQLParser.MakeSQL(
|
|
@"select e.EMPLOYEENO,IsNull(ac.CardNumber,'') as CardNo,e.Name,deg.NAME as Designation,cg.DESCRIPTION AS EmpType,
|
|
d.AttenType as Status, d.Comments,d.InTime,d.OutTime,ISNULL(d.OTHour,0) OTHour,IsNULL(s.ShortName,'') as Shift,
|
|
d.attndate,
|
|
'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
|
END,
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
d.ReferenceID, dept5.DEPARTMENTID DeptID, dept5.[DESCRIPTION] DeptName
|
|
from DailyAttnProcess D
|
|
INNER Join Employee e
|
|
ON E.EMPLOYEEID = d.EmployeeID
|
|
INNER Join DESIGNATION deg
|
|
ON deg.DESIGNATIONID = e.DESIGNATIONID
|
|
Left JOIN DEPARTMENT dept5
|
|
ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4
|
|
ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3
|
|
ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2
|
|
ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
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
|
|
WHERE D.AttnDate = %d AND D.EmployeeID IN(%q)
|
|
AND (D.InTime IS NULL OR D.OutTime IS NULL)
|
|
", attnDate, sEmpID);
|
|
|
|
|
|
dailyInOut = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return dailyInOut;
|
|
}
|
|
|
|
|
|
internal static DataSet GetDailyAbsent(TransactionContext tc, DateTime fromDate, DateTime toDate, string sEmpID,
|
|
EnumAttendanceType status, int payrollTypeID)
|
|
{
|
|
DataSet dailyInOut = new DataSet();
|
|
try
|
|
{
|
|
string sql;
|
|
|
|
sql = SQLParser.MakeSQL(
|
|
@"select sg.get(e.EMPLOYEENO) EMPLOYEENO,NVL(ac.CardNumber,'') CardNo, sg.get(e.Name) Name ,deg.NAME Designation,cg.DESCRIPTION EmpType,
|
|
d.AttenType Status, d.ATTNDATE, d.Comments,d.InTime,d.OutTime,d.OTHour,NVL(s.ShortName,'') Shift,
|
|
dept.DEPARTMENTID DeptID,dept.DESCRIPTION DeptName,d.ReferenceID
|
|
from (SELECT * FROM DailyAttnProcess
|
|
WHERE AttnDate Between %d AND %d And AttenType IN(%n) 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
|
|
WHERE e.PAYROLLTYPEID=%n
|
|
Order By e.EMPLOYEENO, d.ATTNDATE ASC", fromDate, toDate, status, sEmpID,
|
|
payrollTypeID);
|
|
dailyInOut = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return dailyInOut;
|
|
}
|
|
|
|
|
|
internal static DataSet GetDailyDataByStatus(TransactionContext tc, DateTime fromDate, DateTime toDate,
|
|
string sStatus, string sEmpID)
|
|
{
|
|
DataSet dailyInOut = new DataSet();
|
|
try
|
|
{
|
|
string sql;
|
|
|
|
if (sStatus != string.Empty)
|
|
{
|
|
sql = SQLParser.MakeSQL(
|
|
@"select e.EMPLOYEENO,NVL(ac.CardNumber,'') CardNo,e.Name,deg.NAME Designation,cg.DESCRIPTION EmpType,
|
|
d.AttenType Status, d.ATTNDATE, d.Comments,d.InTime,d.OutTime,d.OTHour,NVL(s.ShortName,'') Shift,
|
|
dept.DEPARTMENTID DeptID,dept.DESCRIPTION DeptName,d.ReferenceID
|
|
from (SELECT * FROM DailyAttnProcess
|
|
WHERE AttnDate Between %d AND %d And AttenType 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 Order By e.EMPLOYEENO, d.ATTNDATE ASC",
|
|
fromDate, toDate, sStatus, sEmpID);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL(
|
|
@"select e.EMPLOYEENO,NVL(ac.CardNumber,'') CardNo, e.Name, deg.NAME Designation,cg.DESCRIPTION EmpType,
|
|
d.AttenType Status, d.Comments,d.InTime,d.OutTime,d.OTHour,NVL(s.ShortName,'') Shift,
|
|
dept.DEPARTMENTID DeptID,dept.DESCRIPTION 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 Order By e.EMPLOYEENO, d.ATTNDATE ASC",
|
|
fromDate, toDate, sEmpID);
|
|
}
|
|
|
|
dailyInOut = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return dailyInOut;
|
|
}
|
|
|
|
|
|
internal static DataSet GetZandDocLeave(TransactionContext tc, DateTime fromdate, DateTime todate,
|
|
int PayrollTypeID)
|
|
{
|
|
DataSet dailyInOutAndAbsent = new DataSet();
|
|
try
|
|
{
|
|
//4, 'DL', 'Doctors Leave'
|
|
//21, 'ZL', 'Z Leave'
|
|
string sql = SQLParser.MakeSQL("select dap.Employeeid, dap.ReferenceID, Count(*) Total"
|
|
+ " from DailyAttnProcess dap Inner join Employee e on e.employeeID=dap.employeeID where "
|
|
+ " dap.ReferenceID IN (4,21) "
|
|
+ " "
|
|
+ " AND dap.AttenType = %n "
|
|
+ "AND dap.AttnDate BETWEEN %d AND %d and e.PayrollTypeID=%n Group by dap.Employeeid,dap.ReferenceID",
|
|
EnumAttendanceType.Leave, fromdate, todate, PayrollTypeID);
|
|
dailyInOutAndAbsent = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return dailyInOutAndAbsent;
|
|
}
|
|
|
|
|
|
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 Designation, d.AttenType, d.Comments,d.InTime,d.OutTime,d.LateHour,d.OTHour,s.ShortName,dept.DEPARTMENTID,dept.DESCRIPTION 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 Designation, d.AttenType, 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 AttenType=%n", attnDate, sEmpID, EnumAttendanceType.Absent);
|
|
|
|
|
|
string sql = SQLParser.MakeSQL(
|
|
"select A.CardNumber, e.EMPLOYEENO, e.Name, deg.NAME Designation, d.AttenType, 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 AttenType=%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,NVL(ac.CardNumber,'') CardNo,e.Name,des.NAME Designation,
|
|
e.JOININGDATE,e.GROSSSALARY,d.AttnDate,d.AttenType
|
|
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.AttenType,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 Designation, "
|
|
+ "e.JOININGDATE,e.GROSSSALARY,d.AttnDate,d.AttenType "
|
|
+ "from DailyAttnProcess d INNER JOIN "
|
|
+ "(SELECT EmployeeID,absCount=SUM(CASE WHEN AttenType=%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.AttenType "
|
|
+ "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,
|
|
'Department' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
|
END,
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END
|
|
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
|
|
Left JOIN DEPARTMENT dept5 ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4 ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3 ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2 ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
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 IDNo,
|
|
e.NAME,e.JOININGDATE DOJ,
|
|
NVL(e.PhotoPath,'') EmpPhoto,
|
|
NVL(des.NAME,'') Desig,
|
|
NVL(gd.DESCRIPTION,'') Grade,
|
|
NVL(gd.COLORCODE,'White') GradeColor,
|
|
NVL(dept.DESCRIPTION,'') Dept,
|
|
NVL(acrd.CardNumber,'') 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 GetHolidays(TransactionContext tc, DateTime attnDate, int empID)
|
|
{
|
|
DataSet holidays = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL("select count(EmployeeID) Holidays from DailyAttnProcess "
|
|
+ " where EmployeeID=%n and AttnDate between %d And %d and AttenType IN(%n,%n) ",
|
|
empID,
|
|
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 IDataReader GetAttnDataByWFStatus(TransactionContext tc, DateTime fromDate, DateTime toDate,
|
|
string empIDs, EnumWFAttnStatus status)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess
|
|
where EmployeeID in (%q) and WFStatus=%n and AttnDate between %d and %d Order By AttnDate desc",
|
|
empIDs, (int)status, fromDate, toDate);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetAttnDataByWFStatus(TransactionContext tc, string empIDs, EnumWFAttnStatus status)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess
|
|
where EmployeeID in (%q) and WFStatus=%n Order By AttnDate desc",
|
|
empIDs, (int)status);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static int GetAttnDataByWFStatusCount(TransactionContext tc, string empIDs, EnumWFAttnStatus status)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL(@"Select Count(*) from DailyAttnProcess
|
|
where EmployeeID in (%q) and WFStatus=%n", empIDs, (int)status);
|
|
|
|
return Convert.ToInt32(tc.ExecuteScalar(sql).ToString());
|
|
}
|
|
|
|
internal static IDataReader GetAttnSubmittedData(TransactionContext tc, string empIDs, DateTime fromDate,
|
|
DateTime toDate)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess
|
|
where EmployeeID in (%q) and WFStatus Not In (0,5) and AttnDate Between %d and %d order by AttnDate asc",
|
|
empIDs, fromDate, toDate);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetAttnSubmittedData(TransactionContext tc, string empIDs, bool IsCurrentMonth,
|
|
DateTime NextPayProcessDate)
|
|
{
|
|
DateTime fromDate = GlobalFunctions.FirstDateOfMonth(NextPayProcessDate);
|
|
fromDate = fromDate.AddMonths(-1);
|
|
fromDate = new DateTime(fromDate.Year, fromDate.Month, 11);
|
|
DateTime toDate = GlobalFunctions.LastDateOfMonth(NextPayProcessDate);
|
|
toDate = new DateTime(toDate.Year, toDate.Month, 10);
|
|
string sql = string.Empty;
|
|
if (IsCurrentMonth)
|
|
{
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess
|
|
where EmployeeID in (%q) and WFStatus Not In (0,5) and AttnDate Between %d and %d ",
|
|
empIDs, fromDate, toDate);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess
|
|
where EmployeeID in (%q) and WFStatus Not In (0,5)", empIDs);
|
|
}
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static DataSet GetAttnApprovedData(TransactionContext tc, string empIDs, EnumWFAttnStatus status,
|
|
DateTime fromdate, DateTime todate)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
string sql = string.Empty;
|
|
if (status == EnumWFAttnStatus.LMApproved)
|
|
{
|
|
sql = SQLParser.MakeSQL(
|
|
@"SELECT e.EmployeeID, e.EMPLOYEENO EmployeeNo, e.NAME Name, dept.DESCRIPTION Department, desig.NAME Designation, g.CODE Grade, SUM(dap.APPROVEDOTHOUR) TotalOT
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=dap.EMPLOYEEID
|
|
LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID=e.DEPARTMENTID
|
|
LEFT JOIN DESIGNATION desig ON desig.DESIGNATIONID=e.DESIGNATIONID
|
|
LEFT JOIN GRADES g ON g.GRADEID=e.GRADEID
|
|
WHERE dap.WFSTATUS IN (2,3) and e.EmployeeID in (%q) and dap.AttnDate between %d and %d GROUP BY e.EmployeeID, e.EMPLOYEENO, e.NAME, dept.DESCRIPTION, desig.NAME, g.CODE
|
|
order BY e.EMPLOYEENO", empIDs, fromdate, todate);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL(
|
|
@"SELECT e.EmployeeID, sg.get(e.EMPLOYEENO)EmployeeNo, sg.get(e.NAME)Name, dept.DESCRIPTION Department, sg.get(desig.NAME) Designation, g.CODE Grade, SUM(dap.APPROVEDOTHOUR) TotalOT
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=dap.EMPLOYEEID
|
|
LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID=e.DEPARTMENTID
|
|
LEFT JOIN DESIGNATION desig ON desig.DESIGNATIONID=e.DESIGNATIONID
|
|
LEFT JOIN GRADES g ON g.GRADEID=e.GRADEID
|
|
WHERE dap.WFSTATUS=%n and e.EmployeeID in (%q) and dap.AttnDate between %d and %d GROUP BY e.EmployeeID, e.EMPLOYEENO, e.NAME, dept.DESCRIPTION, desig.NAME, g.CODE
|
|
order BY e.EMPLOYEENO", status, empIDs, fromdate, todate);
|
|
}
|
|
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return presentDays;
|
|
}
|
|
|
|
|
|
internal static DataSet GetMonthlyOverTime(TransactionContext tc, DateTime fromdate, DateTime todate,
|
|
EnumWorkPlanDayType wpType)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
DateTime salaryMonth = GlobalFunctions.LastDateOfMonth(todate);
|
|
try
|
|
{
|
|
string sql = string.Empty;
|
|
if (wpType == EnumWorkPlanDayType.NationalHoliday)
|
|
{
|
|
sql = SQLParser.MakeSQL(
|
|
@"SELECT e.EMPLOYEEID, e.EMPLOYEENO, SUM(dap.APPROVEDOTHOUR) APPROVEDOTHOUR FROM DAILYATTNPROCESS dap
|
|
INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=dap.EMPLOYEEID
|
|
WHERE APPROVEDOTHOUR>0 AND dap.WORKDAYTYPE=%n AND dap.WFSTATUS=%n AND dap.SalaryMonth=%d
|
|
GROUP BY e.EMPLOYEEID, e.EMPLOYEENO", wpType, EnumWFAttnStatus.DHApproved,
|
|
salaryMonth);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL(
|
|
@"SELECT e.EMPLOYEEID, e.EMPLOYEENO, SUM(dap.APPROVEDOTHOUR) APPROVEDOTHOUR FROM DAILYATTNPROCESS dap
|
|
INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=dap.EMPLOYEEID
|
|
WHERE APPROVEDOTHOUR>0 AND dap.WORKDAYTYPE not in (%n) AND dap.WFSTATUS=%n AND dap.SalaryMonth=%d
|
|
GROUP BY e.EMPLOYEEID, e.EMPLOYEENO", EnumWorkPlanDayType.NationalHoliday,
|
|
EnumWFAttnStatus.DHApproved, salaryMonth);
|
|
}
|
|
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return presentDays;
|
|
}
|
|
|
|
internal static int GetAttnDataByWFStatusCountForMobile(TransactionContext tc, int empID, EnumWFAttnStatus status)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL(@"Select Count(*) from DailyAttnProcess With(NoLock)
|
|
where DailyAttnProcess.EmployeeID in ( select EmployeeID from Employee With(NoLock) where linemanagerid =%n)
|
|
and WFStatus=%n and AttnDate >%d", empID, (int)status, DateTime.Today.AddMonths(-3));
|
|
|
|
var rvalue = tc.ExecuteScalar(sql);
|
|
if (rvalue != DBNull.Value) return Convert.ToInt32(rvalue);
|
|
else return 0;
|
|
}
|
|
|
|
internal static DataSet GetAttnSubmittedData(TransactionContext tc, string empIDs, EnumWFAttnStatus status,
|
|
bool isCurrentMonth, DateTime NextPayProcessDate)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
DateTime fromDate = GlobalFunctions.FirstDateOfMonth(NextPayProcessDate);
|
|
fromDate = fromDate.AddMonths(-1);
|
|
fromDate = new DateTime(fromDate.Year, fromDate.Month, 11);
|
|
DateTime toDate = GlobalFunctions.LastDateOfMonth(NextPayProcessDate);
|
|
toDate = new DateTime(toDate.Year, toDate.Month, 10);
|
|
string sql = string.Empty;
|
|
if (!isCurrentMonth)
|
|
{
|
|
sql = SQLParser.MakeSQL(
|
|
@"SELECT e.EmployeeID, sg.get(e.EMPLOYEENO) EMPLOYEENO, sg.get(e.NAME) Name, dept.DESCRIPTION Department, sg.get(desig.NAME) Designation, g.CODE Grade, SUM(dap.APPROVEDOTHOUR) TotalOT
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=dap.EMPLOYEEID
|
|
LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID=e.DEPARTMENTID
|
|
LEFT JOIN DESIGNATION desig ON desig.DESIGNATIONID=e.DESIGNATIONID
|
|
LEFT JOIN GRADES g ON g.GRADEID=e.GRADEID
|
|
WHERE dap.WFSTATUS=%n and e.EmployeeID in (%q) GROUP BY e.EmployeeID, e.EMPLOYEENO, e.NAME, dept.DESCRIPTION, desig.NAME, g.CODE
|
|
order BY e.EMPLOYEENO", status, empIDs);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL(
|
|
@"SELECT e.EmployeeID, sg.get(e.EMPLOYEENO), sg.get(e.NAME) Name, dept.DESCRIPTION Department, sg.get(desig.NAME) Designation, g.CODE Grade, SUM(dap.APPROVEDOTHOUR) TotalOT
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=dap.EMPLOYEEID
|
|
LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID=e.DEPARTMENTID
|
|
LEFT JOIN DESIGNATION desig ON desig.DESIGNATIONID=e.DESIGNATIONID
|
|
LEFT JOIN GRADES g ON g.GRADEID=e.GRADEID
|
|
WHERE dap.WFSTATUS=%n and e.EmployeeID in (%q) and dap.AttnDate between %d and %d GROUP BY e.EmployeeID, e.EMPLOYEENO, e.NAME, dept.DESCRIPTION, desig.NAME, g.CODE
|
|
order BY e.EMPLOYEENO", status, empIDs, fromDate, toDate);
|
|
}
|
|
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return presentDays;
|
|
}
|
|
|
|
internal static DataSet GetAttnSubmittedData(TransactionContext tc, string empIDs, EnumWFAttnStatus status,
|
|
DateTime fromDate, DateTime toDate)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
string sql = string.Empty;
|
|
|
|
sql = SQLParser.MakeSQL(
|
|
@"SELECT e.EmployeeID, e.EMPLOYEENO, e.NAME Name, dept.DESCRIPTION Department, desig.NAME Designation, g.CODE Grade, SUM(dap.APPROVEDOTHOUR) TotalOT
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=dap.EMPLOYEEID
|
|
LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID=e.DEPARTMENTID
|
|
LEFT JOIN DESIGNATION desig ON desig.DESIGNATIONID=e.DESIGNATIONID
|
|
LEFT JOIN GRADES g ON g.GRADEID=e.GRADEID
|
|
WHERE dap.WFSTATUS=%n and e.EmployeeID in (%q) and dap.AttnDate between %d and %d GROUP BY e.EmployeeID, e.EMPLOYEENO, e.NAME, dept.DESCRIPTION, desig.NAME, g.CODE
|
|
order BY e.EMPLOYEENO", status, empIDs, fromDate, toDate);
|
|
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return presentDays;
|
|
}
|
|
|
|
internal static DataSet GetPresentDays(TransactionContext tc, DateTime attnDate, int empID)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL("select count(EmployeeID) PresentDays from DailyAttnProcess "
|
|
+ " where EmployeeID=%n and AttnDate between %d And %d and AttenType In(1,11) ",
|
|
empID,
|
|
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, int payrollType)
|
|
{
|
|
try
|
|
{
|
|
string sql =
|
|
SQLParser.MakeSQL(
|
|
"SELECT MAX(dap.AttnDate) LastProcessDate FROM DailyAttnProcess dap Inner Join Employee e on e.EmployeeID=dap.EmployeeID WHERE dap.IsManualEntry=0 AND e.PayrollTypeID =%n",
|
|
payrollType);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
internal static IDataReader GetLastProcessDateOfEmployee(TransactionContext tc, int employeeID)
|
|
{
|
|
try
|
|
{
|
|
string sql =
|
|
SQLParser.MakeSQL(
|
|
"SELECT MAX(dap.AttnDate) LastProcessDate FROM DailyAttnProcess dap Inner Join Employee e on e.EmployeeID=dap.EmployeeID WHERE dap.IsManualEntry=0 AND e.EmployeeID=%n",
|
|
employeeID);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
public static IDataReader GetDailyEmployeeAbsent(TransactionContext tc, DateTime dateTime, int nID)
|
|
{
|
|
try
|
|
{
|
|
string sql =
|
|
SQLParser.MakeSQL(
|
|
"Select * from DailyAttnProcess where EmployeeID=%n and AttnDate=%d and AttenType=%n", nID,
|
|
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) AttnDate
|
|
,[EmployeeID]
|
|
FROM DailyAttnProcess
|
|
WHERE EmployeeID IN(%q) and AttenType=%n
|
|
group by EmployeeID
|
|
)temp
|
|
WHERE temp.EmployeeID = d.EmployeeID
|
|
AND temp.AttnDate = d.AttnDate
|
|
order by d.EmployeeID", employeeIDs, EnumAttendanceType.Present);
|
|
|
|
IDataReader dr = tc.ExecuteReader(sql);
|
|
return dr;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, int empID, int 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 AttenType=%n",
|
|
empID, shiftID, 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, int nID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM DailyAttnProcess WHERE DailyAttnProcessID=%n", nID);
|
|
}
|
|
|
|
internal static void Delete(TransactionContext tc, int empID, DateTime attnDate)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM DailyAttnProcess WHERE EmployeeID=%n AND AttnDate=%d", empID, attnDate);
|
|
}
|
|
|
|
internal static void Delete(TransactionContext tc, int empID, DateTime attnDate, int dailyAttnProcessID)
|
|
{
|
|
//int dAttnProcessID = 0;
|
|
//string sql = SQLParser.MakeSQL("Select DAILYATTNPROCESSID from DailyAttnProcess WHERE EmployeeID=%n AND AttnDate=%d", empID, attnDate);
|
|
//Object obj = tc.ExecuteScalar(sql);
|
|
//dAttnProcessID = Convert.ToInt32(obj) > 0 ? Convert.ToInt32(obj) : 0;
|
|
//if (dAttnProcessID > 0)
|
|
//{
|
|
// tc.ExecuteNonQuery("DELETE FROM ATTNWFHISTORY WHERE DAILYATTNPROCESSID in (Select DAILYATTNPROCESSID from DailyAttnProcess WHERE EmployeeID=%n AND AttnDate=%d)", empID, attnDate);
|
|
//}
|
|
tc.ExecuteNonQuery(
|
|
"DELETE FROM ATTNWFHISTORY WHERE DAILYATTNPROCESSID in (Select DAILYATTNPROCESSID from DailyAttnProcess WHERE EmployeeID=%n AND AttnDate=%d)",
|
|
empID, attnDate);
|
|
tc.ExecuteNonQuery("DELETE FROM DailyAttnProcess WHERE EmployeeID=%n AND AttnDate=%d", empID, 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 IDataReader GetNew(TransactionContext tc, int 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, fromDate, toDate);
|
|
return tc.ExecuteReader(sql);
|
|
|
|
}
|
|
|
|
internal static DataSet GetTypeWiseAttnCount(TransactionContext tc, string empID, DateTime fromDate, DateTime toDate, params EnumAttendanceType[] attnType)
|
|
{
|
|
string attnTypes = attnType.Aggregate(new StringBuilder(), (sb, tp) => sb.AppendFormat("{0},", (int)tp), sb => sb.ToString().Trim(','));
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT EmployeeID,Count(*) AS Count FROM DailyAttnProcess
|
|
WHERE AttnDate BETWEEN %d AND %d
|
|
AND EmployeeID IN (select EmployeeID from employee where status=1)
|
|
AND AttenType in (%q)
|
|
GROUP BY EmployeeID", fromDate, toDate, attnTypes);
|
|
return tc.ExecuteDataSet(sSQL);
|
|
}
|
|
|
|
internal static DataSet GetEarnedLeaveOpening(TransactionContext tc, int employeeId, int leaveid, DateTime fromDate, DateTime toDate)
|
|
{
|
|
|
|
string sql = "";
|
|
|
|
|
|
sql = SQLParser.MakeSQL(@"SELECT TotalPresent,isnull(TotalAvailed,0) TotalAvailed
|
|
from
|
|
(SELECT employeeid, round(count(AttnDate) * 1.0 / 18, 0) TotalPresent FROM DailyAttnProcess
|
|
WHERE EmployeeID = %n
|
|
AND AttnDate BETWEEN %d AND %d
|
|
AND AttenType IN(1, 3, 11, 12, 6, 7)
|
|
GROUP BY employeeid)tab1
|
|
LEFT OUTER JOIN
|
|
(SELECT empid, sum(APRTOTALDAYS) TotalAvailed FROM LEAVEENTRY
|
|
WHERE EMPID = %n
|
|
AND(APRFROMDATE > %d AND APRTODATE <= %d)
|
|
AND leavestatus = 6
|
|
AND LEAVEID = %n
|
|
GROUP BY empid)tab2 ON tab1.employeeid = tab2.empid", employeeId,
|
|
fromDate, toDate, employeeId, fromDate, toDate, leaveid);
|
|
|
|
|
|
return tc.ExecuteDataSet(sql);
|
|
|
|
|
|
}
|
|
|
|
internal static DataTable GetEarnedLeaveOpeningByEmployees(TransactionContext tc, string employeeIds, string leaveids, DateTime fromDate, DateTime toDate)
|
|
{
|
|
|
|
string sql = "";
|
|
|
|
|
|
sql = SQLParser.MakeSQL(@"SELECT TotalPresent,isnull(TotalAvailed,0) TotalAvailed, EMPLOYEEID
|
|
from
|
|
(SELECT employeeid, round(count(AttnDate) * 1.0 / 18, 0) TotalPresent FROM DailyAttnProcess
|
|
WHERE EmployeeID in (%q)
|
|
AND AttnDate BETWEEN %d AND %d
|
|
AND AttenType IN(1, 3, 11, 12, 6, 7)
|
|
GROUP BY employeeid)tab1
|
|
LEFT OUTER JOIN
|
|
(SELECT empid, sum(APRTOTALDAYS) TotalAvailed FROM LEAVEENTRY
|
|
WHERE empid in (%q)
|
|
AND(APRFROMDATE > %d AND APRTODATE <= %d)
|
|
AND leavestatus = 6
|
|
AND LEAVEID in (%q)
|
|
GROUP BY empid)tab2 ON tab1.employeeid = tab2.empid", employeeIds,
|
|
fromDate, toDate, employeeIds, fromDate, toDate, leaveids);
|
|
|
|
|
|
return tc.ExecuteDataTable(sql);
|
|
|
|
|
|
}
|
|
internal static DataSet GetMonthlyKPIDetail(TransactionContext tc, DateTime dFromDate, DateTime dToDate, string sEmpID, List<Leave> oLeaves, EnumAttendanceType eAttnType)
|
|
{
|
|
DataSet monthlyDetail = new DataSet();
|
|
try
|
|
{
|
|
|
|
string sql = string.Empty;
|
|
|
|
StringBuilder statusCase = new StringBuilder();
|
|
|
|
StringBuilder foreColor = new StringBuilder();
|
|
|
|
#region Generating Cases
|
|
statusCase.Append("'KPIStatus' = case");
|
|
foreColor.Append("'ForeColor' = case");
|
|
List<EnumAttendanceType> attnTypes = Enum.GetValues(typeof(EnumAttendanceType)).Cast<EnumAttendanceType>().ToList();
|
|
|
|
foreach (EnumAttendanceType enItem in attnTypes)
|
|
{
|
|
|
|
switch (enItem)
|
|
{
|
|
case EnumAttendanceType.Absent:
|
|
statusCase.AppendFormat(" when dap.AttenType = {0} then '{1}'", (int)enItem, "AB");
|
|
break;
|
|
case EnumAttendanceType.Holiday:
|
|
statusCase.AppendFormat(" when dap.AttenType = {0} then '{1}'", (int)enItem, "FH");
|
|
break;
|
|
case EnumAttendanceType.WeeklyHoliday:
|
|
statusCase.AppendFormat(" when dap.AttenType = {0} then '{1}'", (int)enItem, "WD");
|
|
foreColor.AppendFormat(" when dap.AttenType = {0} then '{1}'", (int)enItem, "Red");
|
|
|
|
break;
|
|
case EnumAttendanceType.Leave:
|
|
|
|
foreach (Leave lvItem in oLeaves)
|
|
{
|
|
string leaveDescription = lvItem.Description;
|
|
string leaveCode = string.Empty;
|
|
|
|
#region leave status using short form of description
|
|
//switch (leaveDescription)
|
|
//{
|
|
// case "Accident Leave": leaveCode = "Ac.L"; break;
|
|
// case "Annual Leave": leaveCode = "AL"; break;
|
|
// case "Casual Leave": leaveCode = "CL"; break;
|
|
// case "Compensation Leave": leaveCode = "COM"; break;
|
|
// case "Leave Without Pay": leaveCode = "LWP"; break;
|
|
// case "Maternity Leave": leaveCode = "Mt.L"; break;
|
|
// case "Medical Leave": leaveCode = "ML"; break;
|
|
// case "Quarantine Leave": leaveCode = "QL"; break;
|
|
// case "Sick Leave": leaveCode = "SL"; break;
|
|
//}
|
|
#endregion
|
|
|
|
leaveCode = lvItem.Code;
|
|
statusCase.AppendFormat(" when dap.AttenType = {0} and isNULL(dap.ReferenceID,0) ={1} then '{2}'", (int)enItem, lvItem.ID, leaveCode);
|
|
}
|
|
|
|
break;
|
|
case EnumAttendanceType.Present: break;
|
|
case EnumAttendanceType.HalfDay: break;
|
|
case EnumAttendanceType.Delay: break;
|
|
case EnumAttendanceType.Late:
|
|
statusCase.AppendFormat(" when dap.AttenType = {0} then '{1}'", (int)enItem, "L-PR");
|
|
break;
|
|
case EnumAttendanceType.Early: break;
|
|
case EnumAttendanceType.OutSideDuty: break;
|
|
default:
|
|
break;
|
|
}
|
|
}
|
|
statusCase.Append(" when Convert(INT,FlooR(dap.OTHour)) >= 0 then 'PR' END ");
|
|
//statusCase.Append(" else ISNULL(RIGHT('00'+Convert(varchar(50),Convert(INT,FlooR(dap.OTHour))),2)+':'+ RIGHT('00'+Convert(varchar(50),Convert(INT,(dap.OTHour%1)*60)),2),'00:00') END ");
|
|
foreColor.Append(" else 'Black' END ");
|
|
|
|
#endregion
|
|
|
|
|
|
sql = SQLParser.MakeSQL(@"SELECT eInfo.Name EmployeeName,eInfo.EmployeeNo IDNo, eInfo.Designation,eInfo.Unit, eInfo.Department, eInfo.FunctionalUnit, eInfo.DepartmentID,
|
|
eInfo.GradeName GRADE,eInfo.PaymentModeName AS BC,dap.AttnDate,dap.AttenType,IsNULL(dap.OTHour,0) OTHour,
|
|
isNULL(dap.ReferenceID,0) ReferenceID,%q,ISNULL(Line,'') Line,ISNULL(eInfo.Section,'') Section,ISNULL(eInfo.Floor,'') Floor
|
|
FROM
|
|
vw_EmployeeImportantInfo eInfo
|
|
INNER JOIN DailyAttnProcess dap
|
|
ON eInfo.EmployeeID = dap.EmployeeId
|
|
And eInfo.EmployeeID IN(%q)
|
|
and dap.AttnDate between %d and %d",
|
|
statusCase, sEmpID, dFromDate, dToDate);
|
|
|
|
|
|
monthlyDetail = tc.ExecuteDataSet(sql);
|
|
monthlyDetail.Tables[0].TableName = "MonthlyKPIDetail";
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return monthlyDetail;
|
|
}
|
|
|
|
internal static IDataReader GetByStatus(TransactionContext tc, int empID, DateTime fromDate, DateTime toDate,
|
|
string status)
|
|
{
|
|
string sql =
|
|
SQLParser.MakeSQL(
|
|
"SELECT * FROM DailyAttnProcess WHERE EmployeeID=%n AND AttnDate BETWEEN %d AND %d AND CLAIMWFSTATUS IN (%q) AND ORDHour > 0 AND AttenType <> 11 order by AttnDate ASC",
|
|
empID, fromDate, toDate, status);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static DataTable GetAttnData(TransactionContext tc, int employeeID, DateTime fromdate, DateTime toDate)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
//string sql = SQLParser.MakeSQL(@"SELECT dap.DAILYATTNPROCESSID DailyAttnID, dap.DAILYATTNPROCESSID DailyAttnProcessID, TO_CHAR(AttnDate, 'DD-MM-YYYY') AttnDate,
|
|
// CASE
|
|
// WHEN dap.AttenType = 0 THEN 'NONE'
|
|
// WHEN dap.AttenType = 1 THEN 'Present'
|
|
// WHEN dap.AttenType = 2 THEN 'Absent'
|
|
// WHEN dap.AttenType = 3 THEN 'Delay'
|
|
// WHEN dap.AttenType = 11 THEN 'Late'
|
|
// WHEN dap.AttenType = 12 THEN 'Early'
|
|
// WHEN dap.AttenType = 4 THEN 'Leave'
|
|
// WHEN dap.AttenType = 5 THEN 'Holiday'
|
|
// WHEN dap.AttenType = 7 THEN 'OutSideDuty'
|
|
// WHEN dap.AttenType = 8 THEN 'WeeklyHoliday'
|
|
// WHEN dap.AttenType = 10 THEN 'Training'
|
|
// WHEN dap.AttenType = 11 THEN 'Project'
|
|
// ELSE ''
|
|
// END AttenType,
|
|
// s.NAME Relay, (to_char(dap.intime, 'HH24') || ':' || to_char(dap.intime, 'MI')) InTIME, (to_char(dap.OUTTIME, 'HH24') || ':' || to_char(dap.OUTTIME, 'MI')) OUTTIME,
|
|
// CASE
|
|
// WHEN dap.WORKDAYTYPE = 1 THEN 'WeeklyHoliday'
|
|
// WHEN dap.WORKDAYTYPE = 2 THEN 'NationalHoliday'
|
|
// WHEN dap.WORKDAYTYPE = 3 THEN 'WorkingDay'
|
|
// WHEN dap.WORKDAYTYPE = 4 THEN 'HartalDay'
|
|
// ELSE ''
|
|
// END WORKDAYTYPE,
|
|
// dap.EMPREMARKS, dap.LMREMARKS,
|
|
// CASE
|
|
// WHEN dap.CLAIMWFSTATUS = 0 THEN 'None'
|
|
// WHEN dap.CLAIMWFSTATUS = 1 THEN 'EmpSubmitted'
|
|
// WHEN dap.CLAIMWFSTATUS = 2 THEN 'LMApproved'
|
|
// WHEN dap.CLAIMWFSTATUS = 3 THEN 'Rejected'
|
|
// WHEN dap.CLAIMWFSTATUS = 4 THEN 'Not_Applicable'
|
|
// ELSE ''
|
|
// END WFStatus
|
|
// FROM DAILYATTNPROCESS dap
|
|
// INNER JOIN SHIFT s ON s.SHIFTID = dap.SHIFTID
|
|
// WHERE dap.EMPLOYEEID = %n and dap.ATTNDATE BETWEEN %d AND %d ORDER BY dap.ATTNDATE desc", employeeID, fromdate, toDate);
|
|
|
|
string sql = SQLParser.MakeSQL(
|
|
@"SELECT dap.DAILYATTNPROCESSID DailyAttnID, dap.DAILYATTNPROCESSID DailyAttnProcessID,
|
|
CAST(AttnDate as DATE) AttnDate,
|
|
CASE
|
|
WHEN dap.AttenType = 0 THEN 'NONE'
|
|
WHEN dap.AttenType = 1 THEN 'Present'
|
|
WHEN dap.AttenType = 2 THEN 'Absent'
|
|
WHEN dap.AttenType = 3 THEN 'Delay'
|
|
WHEN dap.AttenType = 11 THEN 'Late'
|
|
WHEN dap.AttenType = 12 THEN 'Early'
|
|
WHEN dap.AttenType = 4 THEN 'Leave'
|
|
WHEN dap.AttenType = 5 THEN 'Holiday'
|
|
WHEN dap.AttenType = 7 THEN 'OutSideDuty'
|
|
WHEN dap.AttenType = 8 THEN 'WeeklyHoliday'
|
|
WHEN dap.AttenType = 10 THEN 'Training'
|
|
WHEN dap.AttenType = 11 THEN 'Project'
|
|
ELSE ''
|
|
END AttenType,
|
|
s.NAME Relay,
|
|
CAST(dap.intime AS TIME) InTime,
|
|
cast(dap.outtime as Time) OutTime,
|
|
CASE
|
|
WHEN dap.WORKDAYTYPE = 1 THEN 'WeeklyHoliday'
|
|
WHEN dap.WORKDAYTYPE = 2 THEN 'NationalHoliday'
|
|
WHEN dap.WORKDAYTYPE = 3 THEN 'WorkingDay'
|
|
WHEN dap.WORKDAYTYPE = 4 THEN 'HartalDay'
|
|
ELSE ''
|
|
END WORKDAYTYPE,
|
|
dap.EMPREMARKS, dap.LMREMARKS,
|
|
CASE
|
|
WHEN dap.CLAIMWFSTATUS = 0 THEN 'None'
|
|
WHEN dap.CLAIMWFSTATUS = 1 THEN 'EmpSubmitted'
|
|
WHEN dap.CLAIMWFSTATUS = 2 THEN 'LMApproved'
|
|
WHEN dap.CLAIMWFSTATUS = 3 THEN 'Rejected'
|
|
WHEN dap.CLAIMWFSTATUS = 4 THEN 'Not_Applicable'
|
|
ELSE ''
|
|
END WFStatus
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN SHIFT s ON s.SHIFTID = dap.SHIFTID
|
|
WHERE dap.EMPLOYEEID = %n and dap.ATTNDATE BETWEEN %d AND %d ORDER BY dap.ATTNDATE desc",
|
|
employeeID, fromdate, toDate);
|
|
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return presentDays.Tables[0];
|
|
}
|
|
|
|
|
|
internal static DataTable GetAttnData(TransactionContext tc, int employeeID, DateTime fromdate, DateTime toDate,
|
|
string status)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
//string sql = SQLParser.MakeSQL(@"
|
|
// SELECT * FROM
|
|
// (
|
|
// SELECT dap.DAILYATTNPROCESSID DailyAttnID, dap.DAILYATTNPROCESSID DailyAttnProcessID,
|
|
// CAST(AttnDate as DATE) AttnDate,
|
|
// CASE
|
|
// WHEN dap.AttenType = 0 THEN 'NONE'
|
|
// WHEN dap.AttenType = 1 THEN 'Present'
|
|
// WHEN dap.AttenType = 2 THEN 'Absent'
|
|
// WHEN dap.AttenType = 3 THEN 'Delay'
|
|
// WHEN dap.AttenType = 11 THEN 'Late'
|
|
// WHEN dap.AttenType = 12 THEN 'Early'
|
|
// WHEN dap.AttenType = 4 THEN 'Leave'
|
|
// WHEN dap.AttenType = 5 THEN 'Holiday'
|
|
// WHEN dap.AttenType = 7 THEN 'OutSideDuty'
|
|
// WHEN dap.AttenType = 8 THEN 'WeeklyHoliday'
|
|
// WHEN dap.AttenType = 10 THEN 'Training'
|
|
// WHEN dap.AttenType = 11 THEN 'Project'
|
|
// ELSE ''
|
|
// END AttenType,
|
|
// s.NAME Relay,
|
|
// CAST(dap.intime as Time) InTime,
|
|
// CAST(dap.outtime as Time) OutTime,
|
|
// CASE
|
|
// WHEN dap.WORKDAYTYPE = 1 THEN 'WeeklyHoliday'
|
|
// WHEN dap.WORKDAYTYPE = 2 THEN 'NationalHoliday'
|
|
// WHEN dap.WORKDAYTYPE = 3 THEN 'WorkingDay'
|
|
// WHEN dap.WORKDAYTYPE = 4 THEN 'HartalDay'
|
|
// ELSE ''
|
|
// END WORKDAYTYPE,
|
|
// dap.EMPREMARKS
|
|
// FROM DAILYATTNPROCESS dap
|
|
// INNER JOIN SHIFT s ON s.SHIFTID = dap.SHIFTID
|
|
// WHERE dap.EMPLOYEEID = %n AND dap.ORDHour > 0 and NOT dap.CLAIMWFSTATUS IN (%q)
|
|
// and dap.ATTNDATE BETWEEN %d AND %d and NOT dap.AttenType in (%n)
|
|
// UNION
|
|
// SELECT dap.DAILYATTNPROCESSID DailyAttnID, dap.DAILYATTNPROCESSID DailyAttnProcessID,
|
|
// CAST(AttnDate as DATE) AttnDate,
|
|
// CASE
|
|
// WHEN dap.AttenType = 0 THEN 'NONE'
|
|
// WHEN dap.AttenType = 1 THEN 'Present'
|
|
// WHEN dap.AttenType = 2 THEN 'Absent'
|
|
// WHEN dap.AttenType = 3 THEN 'Delay'
|
|
// WHEN dap.AttenType = 11 THEN 'Late'
|
|
// WHEN dap.AttenType = 12 THEN 'Early'
|
|
// WHEN dap.AttenType = 4 THEN 'Leave'
|
|
// WHEN dap.AttenType = 5 THEN 'Holiday'
|
|
// WHEN dap.AttenType = 7 THEN 'OutSideDuty'
|
|
// WHEN dap.AttenType = 8 THEN 'WeeklyHoliday'
|
|
// WHEN dap.AttenType = 10 THEN 'Training'
|
|
// WHEN dap.AttenType = 11 THEN 'Project'
|
|
// ELSE ''
|
|
// END AttenType,
|
|
// s.NAME Relay,
|
|
// CAST(dap.intime as Time) InTime,
|
|
// CAST(dap.outtime as Time) OutTime,
|
|
// CASE
|
|
// WHEN dap.WORKDAYTYPE = 1 THEN 'WeeklyHoliday'
|
|
// WHEN dap.WORKDAYTYPE = 2 THEN 'NationalHoliday'
|
|
// WHEN dap.WORKDAYTYPE = 3 THEN 'WorkingDay'
|
|
// WHEN dap.WORKDAYTYPE = 4 THEN 'HartalDay'
|
|
// ELSE ''
|
|
// END WORKDAYTYPE,
|
|
// dap.EMPREMARKS
|
|
// FROM DAILYATTNPROCESS dap
|
|
// INNER JOIN SHIFT s ON s.SHIFTID = dap.SHIFTID
|
|
// WHERE dap.EMPLOYEEID = %n AND dap.AttenType = 2 and NOT dap.CLAIMWFSTATUS IN (%q)
|
|
// and dap.ATTNDATE BETWEEN %d AND %d and Not dap.AttenType in (%n)
|
|
// )
|
|
// ORDER BY ATTNDATE desc
|
|
// ", employeeID, status, fromdate, toDate, (int)EnumAttendanceType.Leave, employeeID, status, fromdate, toDate, (int)EnumAttendanceType.Leave);
|
|
|
|
|
|
string sql = SQLParser.MakeSQL(@"
|
|
|
|
SELECT dap.DAILYATTNPROCESSID DailyAttnID, dap.DAILYATTNPROCESSID DailyAttnProcessID,
|
|
CAST(AttnDate as DATE) AttnDate,
|
|
CASE
|
|
WHEN dap.AttenType = 0 THEN 'NONE'
|
|
WHEN dap.AttenType = 1 THEN 'Present'
|
|
WHEN dap.AttenType = 2 THEN 'Absent'
|
|
WHEN dap.AttenType = 3 THEN 'Delay'
|
|
WHEN dap.AttenType = 11 THEN 'Late'
|
|
WHEN dap.AttenType = 12 THEN 'Early'
|
|
WHEN dap.AttenType = 4 THEN 'Leave'
|
|
WHEN dap.AttenType = 5 THEN 'Holiday'
|
|
WHEN dap.AttenType = 7 THEN 'OutSideDuty'
|
|
WHEN dap.AttenType = 8 THEN 'WeeklyHoliday'
|
|
WHEN dap.AttenType = 10 THEN 'Training'
|
|
WHEN dap.AttenType = 11 THEN 'Project'
|
|
ELSE ''
|
|
END AttenType,
|
|
s.NAME Relay,
|
|
CAST(dap.intime as Time) InTime,
|
|
CAST(dap.outtime as Time) OutTime,
|
|
CASE
|
|
WHEN dap.WORKDAYTYPE = 1 THEN 'WeeklyHoliday'
|
|
WHEN dap.WORKDAYTYPE = 2 THEN 'NationalHoliday'
|
|
WHEN dap.WORKDAYTYPE = 3 THEN 'WorkingDay'
|
|
WHEN dap.WORKDAYTYPE = 4 THEN 'HartalDay'
|
|
ELSE ''
|
|
END WORKDAYTYPE,
|
|
dap.EMPREMARKS
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN SHIFT s ON s.SHIFTID = dap.SHIFTID
|
|
WHERE dap.EMPLOYEEID = %n AND dap.ORDHour > 0 and NOT dap.CLAIMWFSTATUS IN (%q)
|
|
and dap.ATTNDATE BETWEEN %d AND %d and NOT dap.AttenType in (%n)
|
|
UNION
|
|
SELECT dap.DAILYATTNPROCESSID DailyAttnID, dap.DAILYATTNPROCESSID DailyAttnProcessID,
|
|
CAST(AttnDate as DATE) AttnDate,
|
|
CASE
|
|
WHEN dap.AttenType = 0 THEN 'NONE'
|
|
WHEN dap.AttenType = 1 THEN 'Present'
|
|
WHEN dap.AttenType = 2 THEN 'Absent'
|
|
WHEN dap.AttenType = 3 THEN 'Delay'
|
|
WHEN dap.AttenType = 11 THEN 'Late'
|
|
WHEN dap.AttenType = 12 THEN 'Early'
|
|
WHEN dap.AttenType = 4 THEN 'Leave'
|
|
WHEN dap.AttenType = 5 THEN 'Holiday'
|
|
WHEN dap.AttenType = 7 THEN 'OutSideDuty'
|
|
WHEN dap.AttenType = 8 THEN 'WeeklyHoliday'
|
|
WHEN dap.AttenType = 10 THEN 'Training'
|
|
WHEN dap.AttenType = 11 THEN 'Project'
|
|
ELSE ''
|
|
END AttenType,
|
|
s.NAME Relay,
|
|
CAST(dap.intime as Time) InTime,
|
|
CAST(dap.outtime as Time) OutTime,
|
|
CASE
|
|
WHEN dap.WORKDAYTYPE = 1 THEN 'WeeklyHoliday'
|
|
WHEN dap.WORKDAYTYPE = 2 THEN 'NationalHoliday'
|
|
WHEN dap.WORKDAYTYPE = 3 THEN 'WorkingDay'
|
|
WHEN dap.WORKDAYTYPE = 4 THEN 'HartalDay'
|
|
ELSE ''
|
|
END WORKDAYTYPE,
|
|
dap.EMPREMARKS
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN SHIFT s ON s.SHIFTID = dap.SHIFTID
|
|
WHERE dap.EMPLOYEEID = %n AND dap.AttenType = 2 and NOT dap.CLAIMWFSTATUS IN (%q)
|
|
and dap.ATTNDATE BETWEEN %d AND %d and Not dap.AttenType in (%n)
|
|
|
|
", employeeID, status, fromdate, toDate, (int)EnumAttendanceType.Leave,
|
|
employeeID, status, fromdate, toDate, (int)EnumAttendanceType.Leave);
|
|
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return presentDays.Tables[0];
|
|
}
|
|
|
|
|
|
internal static DataSet MonthlyAttendanceSummaryForMailSchedular(TransactionContext tc, int id, DateTime fromDate, DateTime toDate, EnumAttendanceType attnType, int payrollTypeid)
|
|
{
|
|
// 'SPP Head Office' => for Synovia
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
string dateRange = $"'{fromDate:yyyy-MM-dd}' AND '{toDate:yyyy-MM-dd}'";
|
|
string sql;
|
|
sql = SQLParser.MakeSQL(@"
|
|
SELECT EMPLOYEE.EMPLOYEENO, Employee.NAME, DS.NAME AS Designation, aa.* FROM EMPLOYEE INNER JOIN
|
|
(
|
|
SELECT
|
|
E.EMPLOYEEID,
|
|
COUNT(CASE WHEN D.ATTNDATE BETWEEN %q AND D.workdaytype =3 THEN 1 ELSE NULL END) AS TotalWorkingDays,
|
|
COUNT(CASE WHEN D.ATTNDATE BETWEEN %q AND D.ATTENTYPE = 1 THEN 1 ELSE NULL END) AS TotalAttendedDays,
|
|
COUNT(CASE WHEN D.ATTNDATE BETWEEN %q AND D.ATTENTYPE = 2 THEN 1 ELSE NULL END) AS TotalAbsentDays,
|
|
SUM(CASE WHEN D.ATTNDATE BETWEEN %q AND D.LATEHOUR > 0 THEN 1 ELSE 0 END) AS TotalLateDays,
|
|
COUNT(CASE WHEN D.ATTNDATE BETWEEN %q AND D.ATTENTYPE = 3 THEN 1 ELSE NULL END) AS TotalLeaveDays
|
|
FROM
|
|
DAILYATTNPROCESS D
|
|
Inner JOIN
|
|
EMPLOYEE E ON E.EMPLOYEEID = D.EMPLOYEEID
|
|
WHERE E.LINEMANAGERID = %n
|
|
GROUP BY E.EMPLOYEEID ) aa
|
|
ON aa.Employeeid = EMPLOYEE.EMPLOYEEID
|
|
INNER JOIN DESIGNATION DS ON DS.DESIGNATIONID = EMPLOYEE.DESIGNATIONID", dateRange, dateRange, dateRange, dateRange, dateRange, id);
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return presentDays;
|
|
}
|
|
|
|
internal static DataSet MonthlyAttendanceSummaryForMailSchedularCordinator(TransactionContext tc, int id, DateTime fromDate, DateTime toDate, EnumAttendanceType attnType, int payrollTypeid)
|
|
{
|
|
// 'SPP Head Office' => for Synovia
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
string dateRange = $"'{fromDate:yyyy-MM-dd}' AND '{toDate:yyyy-MM-dd}'";
|
|
string sql;
|
|
sql = SQLParser.MakeSQL(@"
|
|
SELECT EMPLOYEE.EMPLOYEENO, Employee.NAME, DS.NAME AS Designation, aa.* FROM EMPLOYEE INNER JOIN
|
|
(
|
|
SELECT
|
|
E.EMPLOYEEID,
|
|
COUNT(CASE WHEN D.ATTNDATE BETWEEN %q AND D.workdaytype =3 THEN 1 ELSE NULL END) AS TotalWorkingDays,
|
|
COUNT(CASE WHEN D.ATTNDATE BETWEEN %q AND D.ATTENTYPE = 1 THEN 1 ELSE NULL END) AS TotalAttendedDays,
|
|
COUNT(CASE WHEN D.ATTNDATE BETWEEN %q AND D.ATTENTYPE = 2 THEN 1 ELSE NULL END) AS TotalAbsentDays,
|
|
SUM(CASE WHEN D.ATTNDATE BETWEEN %q AND D.LATEHOUR > 0 THEN 1 ELSE 0 END) AS TotalLateDays,
|
|
COUNT(CASE WHEN D.ATTNDATE BETWEEN %q AND D.ATTENTYPE = 3 THEN 1 ELSE NULL END) AS TotalLeaveDays
|
|
FROM
|
|
DAILYATTNPROCESS D
|
|
Inner JOIN
|
|
EMPLOYEE E ON E.EMPLOYEEID = D.EMPLOYEEID
|
|
WHERE E.EMPLOYEEID IN(SELECT employeId FROM EmployeeCordinator WHERE CordinatorID = %n)
|
|
GROUP BY E.EMPLOYEEID ) aa
|
|
ON aa.Employeeid = EMPLOYEE.EMPLOYEEID
|
|
INNER JOIN DESIGNATION DS ON DS.DESIGNATIONID = EMPLOYEE.DESIGNATIONID", dateRange, dateRange, dateRange, dateRange, dateRange, id);
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return presentDays;
|
|
}
|
|
|
|
internal static IDataReader AttendanceDataForMailSchedular(TransactionContext tc, DateTime fromDate, DateTime toDate, EnumAttendanceType attnType, int payrollTypeid)
|
|
{
|
|
// 'SPP Head Office' => for Synovia
|
|
string sql;
|
|
|
|
if (attnType == EnumAttendanceType.None)
|
|
{
|
|
sql = SQLParser.MakeSQL(@"
|
|
WITH cte2 AS
|
|
(
|
|
SELECT
|
|
LOCATIONID
|
|
FROM LOCATION
|
|
WHERE PARENTID IN (SELECT LOCATIONID FROM LOCATION WHERE DESCRIPTION = 'Head Office' OR DESCRIPTION = 'SPP Head Office')
|
|
UNION ALL
|
|
SELECT
|
|
i.LOCATIONID
|
|
FROM LOCATION i
|
|
INNER JOIN cte2 c ON c.LOCATIONID = i.PARENTID
|
|
),
|
|
cte AS (
|
|
SELECT * FROM cte2
|
|
UNION
|
|
SELECT LOCATIONID FROM LOCATION WHERE DESCRIPTION = 'Head Office' OR DESCRIPTION = 'SPP Head Office'
|
|
)
|
|
SELECT dap.DAILYATTNPROCESSID, e.EMPLOYEEID, e.EMPLOYEENO, e.NAME, des.NAME [DESIGNATION], dep.DESCRIPTION [DEPARTMENT], dap.AttnDate,
|
|
FORMAT(dap.AttnDate,'dddd') day, dap.InTime, dap.OutTime, dap.AttenType,
|
|
iif(dap.AttenType != 5, l.DESCRIPTION, nh.DESCRIPTION) NotPresentType,
|
|
lm.EMPLOYEENO LINEMANAGERNO, lm.NAME LINEMANAGERNAME
|
|
FROM DAILYATTNPROCESS dap
|
|
JOIN EMPLOYEE e ON dap.EMPLOYEEID = e.EMPLOYEEID
|
|
JOIN cte c ON c.LOCATIONID = e.LOCATIONID
|
|
LEFT JOIN EMPLOYEE lm ON e.LINEMANAGERID = lm.EMPLOYEEID
|
|
LEFT JOIN DESIGNATION des ON e.DESIGNATIONID = des.DESIGNATIONID
|
|
LEFT JOIN DEPARTMENT dep ON e.DEPARTMENTID = dep.DEPARTMENTID
|
|
LEFT JOIN LEAVE l ON l.LEAVEID = dap.ReferenceID
|
|
LEFT JOIN ATTNNATIONALHOLIDAY nh ON nh.ATTNNATIONALHOLIDAYID = dap.ReferenceID
|
|
WHERE e.PAYROLLTYPEID = %n AND e.STATUS = 1 AND dap.AttnDate BETWEEN %d AND %d
|
|
ORDER BY dep.DESCRIPTION ASC",
|
|
payrollTypeid, fromDate, toDate);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL(@"
|
|
WITH cte2 AS
|
|
(
|
|
SELECT
|
|
LOCATIONID
|
|
FROM LOCATION
|
|
WHERE PARENTID IN (SELECT LOCATIONID FROM LOCATION WHERE DESCRIPTION = 'Head Office' OR DESCRIPTION = 'SPP Head Office')
|
|
UNION ALL
|
|
SELECT
|
|
i.LOCATIONID
|
|
FROM LOCATION i
|
|
INNER JOIN cte2 c ON c.LOCATIONID = i.PARENTID
|
|
),
|
|
cte AS (
|
|
SELECT * FROM cte2
|
|
UNION
|
|
SELECT LOCATIONID FROM LOCATION WHERE DESCRIPTION = 'Head Office' OR DESCRIPTION = 'SPP Head Office'
|
|
)
|
|
SELECT dap.DAILYATTNPROCESSID, e.EMPLOYEEID, e.EMPLOYEENO, e.NAME, des.NAME [DESIGNATION], dep.DESCRIPTION [DEPARTMENT], dap.AttnDate,
|
|
FORMAT(dap.AttnDate,'dddd') day, dap.InTime, dap.OutTime, dap.AttenType,
|
|
iif(dap.AttenType != 5, l.DESCRIPTION, nh.DESCRIPTION) NotPresentType,
|
|
lm.EMPLOYEENO LINEMANAGERNO, lm.NAME LINEMANAGERNAME
|
|
FROM DAILYATTNPROCESS dap
|
|
JOIN EMPLOYEE e ON dap.EMPLOYEEID = e.EMPLOYEEID
|
|
JOIN cte c ON c.LOCATIONID = e.LOCATIONID
|
|
LEFT JOIN EMPLOYEE lm ON e.LINEMANAGERID = lm.EMPLOYEEID
|
|
LEFT JOIN DESIGNATION des ON e.DESIGNATIONID = des.DESIGNATIONID
|
|
LEFT JOIN DEPARTMENT dep ON e.DEPARTMENTID = dep.DEPARTMENTID
|
|
LEFT JOIN LEAVE l ON l.LEAVEID = dap.ReferenceID
|
|
LEFT JOIN ATTNNATIONALHOLIDAY nh ON nh.ATTNNATIONALHOLIDAYID = dap.ReferenceID
|
|
WHERE e.PAYROLLTYPEID = %n AND e.STATUS = 1 AND dap.AttenType = %n AND dap.AttnDate BETWEEN %d AND %d
|
|
ORDER BY dep.DESCRIPTION ASC",
|
|
payrollTypeid, attnType, fromDate, toDate);
|
|
}
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static DataSet GetDailyAttndanceBenefitDataFromVB(TransactionContext tc, DateTime attnDate)
|
|
{
|
|
DataSet oDataset = new DataSet();
|
|
|
|
|
|
try
|
|
{
|
|
string sBasicsql = SQLParser.MakeSQL(
|
|
@"SELECT e.EMPCODE, e.NAME, d.ATTNDATE, d.DSAHOURS, d.TSAHOURS, d.CONVEYANCE, d.ADDITIONALHOURS, d.OTAPPROVED, d.OTREMARKS, d.ORDINARYHOURS, d.PRESENTHOURS,
|
|
d.FESTIVAL, d.HARTAL,
|
|
d.AttenType FROM DAILYATTN d
|
|
INNER JOIN Payrollusernm.EMPBASICINFO e ON e.EMPLOYEEID=d.EMPLOYEEID
|
|
WHERE ATTNDATE = %d
|
|
ORDER BY e.EMPCODE", attnDate);
|
|
|
|
oDataset = tc.ExecuteDataSet(sBasicsql);
|
|
oDataset.Tables[0].TableName = "DailyAttn";
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return oDataset;
|
|
}
|
|
|
|
internal static DataSet GetMonthlyAttndanceBenefitDataFromVB(TransactionContext tc, int year, int month)
|
|
{
|
|
DataSet oDataset = new DataSet();
|
|
|
|
|
|
try
|
|
{
|
|
string sBasicsql = SQLParser.MakeSQL(
|
|
@"SELECT e.EMPCODE, e.NAME, a.ORDINARYHOURS, a.ADDITIONALHOURS, a.MEALDAYS, a.TEADAYS, a.DSAHOURS, a.TSAHOURS, a.ATTENDANCEDAY, a.OTHOURS FROM MONTHLYATTN a
|
|
INNER JOIN EMPBASICINFO e ON e.EMPLOYEEID=a.EMPLOYEEID
|
|
WHERE a.ATTNYEAR=%n AND a.ATTNMONTH=%n
|
|
ORDER BY e.EMPCODE", year, month);
|
|
|
|
oDataset = tc.ExecuteDataSet(sBasicsql);
|
|
oDataset.Tables[0].TableName = "MONTHLYATTN";
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return oDataset;
|
|
}
|
|
|
|
internal static bool IsExist(TransactionContext tc, int empID, DateTime attnDate)
|
|
{
|
|
bool Exist = false;
|
|
Object obj = tc.ExecuteScalar("Select * FROM DailyAttnProcess WHERE EmployeeID=%n AND AttnDate=%d", empID,
|
|
attnDate);
|
|
Exist = Convert.ToInt32(obj) > 0 ? true : false;
|
|
return Exist;
|
|
}
|
|
|
|
internal static int GetAttnDataByWFStatusCount(TransactionContext tc, DateTime fromDate, DateTime toDate,
|
|
EnumWFAttnStatus status)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL(@"Select Count(*) from DailyAttnProcess
|
|
where ClaimWFStatus=%n and attnDate between %d and %d", (int)status,
|
|
fromDate, toDate);
|
|
|
|
return Convert.ToInt32(tc.ExecuteScalar(sql).ToString());
|
|
}
|
|
internal static IDataReader GetAttnDataForMobile(TransactionContext tc, int empId, DateTime attnDate)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"SELECT dap.*, s.Name ShiftName FROM DailyAttnProcess dap
|
|
LEFT JOIN Shift s ON dap.ShiftID = s.ShiftID
|
|
WHERE AttnDate=%d and EmployeeID=%n", attnDate, empId);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetAttnDataForMobileWithNoLock(TransactionContext tc, int empId, DateTime attnDate)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"SELECT dap.*, s.Name ShiftName FROM DailyAttnProcess dap With(NoLock)
|
|
LEFT JOIN Shift s With(NoLock) ON dap.ShiftID = s.ShiftID
|
|
WHERE AttnDate=%d and EmployeeID=%n", attnDate, empId);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetRegularizableAttn(TransactionContext tc, int EmpID, DateTime fromDate, DateTime toDate)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess
|
|
where EmployeeID =%n and
|
|
(ATTENTYPE =%n OR ATTENTYPE =%n OR isLate =%b OR EarlyHour >0 OR InTime Is Null OR OutTime Is Null)
|
|
and AttnDate Between %d and %d and
|
|
(wfstatus=%n OR wfstatus=%n) AND WorkDayType =3 order by AttnDate desc",
|
|
EmpID, EnumAttendanceType.Late, EnumAttendanceType.Absent,
|
|
true, fromDate, toDate, EnumWFAttnStatus.None,
|
|
EnumWFAttnStatus.NotApplicable);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
internal static IDataReader GetAttnForStatement(TransactionContext tc, int EmpID, DateTime fromDate, DateTime toDate)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess
|
|
where EmployeeID =%n and AttnDate Between %d and %d order by AttnDate DESC",
|
|
EmpID, fromDate, toDate);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetAttnForStatementWithNoLock(TransactionContext tc, int EmpID, DateTime fromDate, DateTime toDate)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess With(NoLock)
|
|
where EmployeeID =%n and AttnDate Between %d and %d order by AttnDate DESC",
|
|
EmpID, fromDate, toDate);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetByLMID(TransactionContext tc, int LMID, DateTime AttnDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess Where EmployeeID in (SELECT EMPLOYEEID FROM EMPLOYEE WHERE LINEMANAGERID = %n) and AttnDate = %d", LMID, AttnDate);
|
|
}
|
|
|
|
internal static IDataReader GetByLMIDMobileWithNoLock(TransactionContext tc, int LMID, DateTime AttnDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM DailyAttnProcess With(NoLock) Where EmployeeID In (SELECT EMPLOYEEID FROM EMPLOYEE With(NoLock) WHERE LINEMANAGERID = %n) and AttnDate = %d", LMID, AttnDate);
|
|
}
|
|
|
|
internal static DataSet GetAttnSubmittedData(TransactionContext tc, string empIDs, DateTime nextPayProcessDate, EnumWFAttnStatus status, bool isCurrentMonth)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
DateTime fromDate = GlobalFunctions.FirstDateOfMonth(nextPayProcessDate);
|
|
fromDate = fromDate.AddMonths(-1);
|
|
fromDate = new DateTime(fromDate.Year, fromDate.Month, 11);
|
|
DateTime toDate = GlobalFunctions.LastDateOfMonth(nextPayProcessDate);
|
|
toDate = new DateTime(toDate.Year, toDate.Month, 10);
|
|
string sql = string.Empty;
|
|
if (!isCurrentMonth)
|
|
{
|
|
sql = SQLParser.MakeSQL(@"SELECT e.EmployeeID, e.EMPLOYEENO EMPLOYEENO, e.NAME Name, dept.DESCRIPTION Department, desig.NAME Designation, g.CODE Grade, SUM(dap.APPROVEDOTHOUR) TotalOT
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=dap.EMPLOYEEID
|
|
LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID=e.DEPARTMENTID
|
|
LEFT JOIN DESIGNATION desig ON desig.DESIGNATIONID=e.DESIGNATIONID
|
|
LEFT JOIN GRADES g ON g.GRADEID=e.GRADEID
|
|
WHERE dap.WFSTATUS=%n and e.EmployeeID in (%q) GROUP BY e.EmployeeID, e.EMPLOYEENO, e.NAME, dept.DESCRIPTION, desig.NAME, g.CODE
|
|
order BY e.EMPLOYEENO", status, empIDs);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL(@"SELECT e.EmployeeID, e.EMPLOYEENO, e.NAME Name, dept.DESCRIPTION Department, desig.NAME Designation, g.CODE Grade, SUM(dap.APPROVEDOTHOUR) TotalOT
|
|
FROM DAILYATTNPROCESS dap
|
|
INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=dap.EMPLOYEEID
|
|
LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID=e.DEPARTMENTID
|
|
LEFT JOIN DESIGNATION desig ON desig.DESIGNATIONID=e.DESIGNATIONID
|
|
LEFT JOIN GRADES g ON g.GRADEID=e.GRADEID
|
|
WHERE dap.WFSTATUS=%n and e.EmployeeID in (%q) and dap.AttnDate between %d and %d GROUP BY e.EmployeeID, e.EMPLOYEENO, e.NAME, dept.DESCRIPTION, desig.NAME, g.CODE
|
|
order BY e.EMPLOYEENO", status, empIDs, fromDate, toDate);
|
|
}
|
|
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return presentDays;
|
|
}
|
|
|
|
//internal static DataSet GetAttnSubmittedData(TransactionContext tc, int linemanagerid)
|
|
//{
|
|
// DataSet presentDays = new DataSet();
|
|
// try
|
|
// {
|
|
// DateTime fromDate = GlobalFunctions.FirstDateOfMonth(DateTime.Today);
|
|
// fromDate = fromDate.AddMonths(-1);
|
|
// DateTime toDate = GlobalFunctions.LastDateOfMonth(DateTime.Today);
|
|
// toDate = new DateTime(toDate.Year, toDate.Month, 10);
|
|
// string sql = string.Empty;
|
|
|
|
// sql = SQLParser.MakeSQL(@"SELECT e.EmployeeID, e.EMPLOYEENO, e.NAME Name, dept.DESCRIPTION Department, desig.NAME Designation, g.CODE Grade, SUM(dap.APPROVEDOTHOUR) TotalOT
|
|
// FROM DAILYATTNPROCESS dap
|
|
// INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=dap.EMPLOYEEID
|
|
// LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID=e.DEPARTMENTID
|
|
// LEFT JOIN DESIGNATION desig ON desig.DESIGNATIONID=e.DESIGNATIONID
|
|
// LEFT JOIN GRADES g ON g.GRADEID=e.GRADEID
|
|
// WHERE dap.WFSTATUS=%n and e.linemanagerid in (%q) and dap.AttnDate between %d and %d GROUP BY e.EmployeeID, e.EMPLOYEENO, e.NAME, dept.DESCRIPTION, desig.NAME, g.CODE
|
|
// order BY e.EMPLOYEENO", status, linemanagerid, fromDate, toDate);
|
|
|
|
|
|
// presentDays = tc.ExecuteDataSet(sql);
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// throw new Exception(ex.Message);
|
|
// }
|
|
// return presentDays;
|
|
//}
|
|
|
|
internal static DataSet GetAttnShiftAndWorkDatType(TransactionContext tc, int EmployeeID, DateTime AttnDate)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
string sql = string.Empty;
|
|
|
|
sql = SQLParser.MakeSQL(@"SELECT TOP 1 m.ShiftID, m.Type, w.shiftid defaultShiftID,S.InTime, S.FlexibleHour, S.LateCalcualtion FROM WorkPlanGroup w,
|
|
EMPLOYEEWORKPLANSETUP ew, MonthlyWorkPlan m, shift S
|
|
where w.WorkPlanGroupID = ew.WORKPLANGROUPID AND ew.WORKPLANGROUPID = w.WorkPlanGroupID AND ew.WORKPLANGROUPID = m.WorkPlanGroupID
|
|
AND S.ShiftID = m.ShiftID AND ew.EMPLOYEEID = %n AND m.WorkDate =%d", EmployeeID, AttnDate);
|
|
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return presentDays;
|
|
}
|
|
|
|
internal static DataSet GetAttnShiftAndWorkDatTypeMobileWithNoLock(TransactionContext tc, int EmployeeID, DateTime AttnDate)
|
|
{
|
|
DataSet presentDays = new DataSet();
|
|
try
|
|
{
|
|
string sql = string.Empty;
|
|
|
|
sql = SQLParser.MakeSQL(@"SELECT TOP 1 m.ShiftID, m.Type, w.shiftid defaultShiftID, S.InTime ShiftInTime, S.OutTime ShiftOutTime, S.FlexibleHour, S.LunchHour LunchHour, S.LateCalcualtion FROM WorkPlanGroup w With(NoLock),
|
|
EMPLOYEEWORKPLANSETUP ew With(NoLock), MonthlyWorkPlan m With(NoLock), shift S With(NoLock)
|
|
where w.WorkPlanGroupID = ew.WORKPLANGROUPID AND ew.WORKPLANGROUPID = w.WorkPlanGroupID AND ew.WORKPLANGROUPID = m.WorkPlanGroupID
|
|
AND S.ShiftID = m.ShiftID AND ew.EMPLOYEEID = %n AND m.WorkDate =%d", EmployeeID, AttnDate);
|
|
|
|
presentDays = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return presentDays;
|
|
}
|
|
internal static DataSet AttnDaysSummaryForSalaryProcess(TransactionContext tc, DateTime FromDate, DateTime ToDate)
|
|
{
|
|
|
|
|
|
StringBuilder presentCase = new StringBuilder();
|
|
|
|
StringBuilder absentCase = new StringBuilder();
|
|
StringBuilder holidayCase = new StringBuilder();
|
|
StringBuilder leaveCase = new StringBuilder();
|
|
|
|
#region Generating Cases
|
|
presentCase.Append("Case ");
|
|
absentCase.Append("Case ");
|
|
holidayCase.Append("Case ");
|
|
leaveCase.Append("Case ");
|
|
|
|
List<EnumAttendanceType> attnTypes = Enum.GetValues(typeof(EnumAttendanceType)).Cast<EnumAttendanceType>().ToList();
|
|
|
|
foreach (EnumAttendanceType enItem in attnTypes)
|
|
{
|
|
|
|
switch (enItem)
|
|
{
|
|
case EnumAttendanceType.Absent:
|
|
absentCase.AppendFormat(" when AttenType = {0} then 1 ", (int)enItem);
|
|
|
|
break;
|
|
case EnumAttendanceType.Holiday:
|
|
case EnumAttendanceType.WeeklyHoliday:
|
|
holidayCase.AppendFormat(" when AttenType = {0} then 1 ", (int)enItem);
|
|
|
|
break;
|
|
case EnumAttendanceType.Leave:
|
|
leaveCase.AppendFormat(" when AttenType = {0} then 1 ", (int)enItem);
|
|
break;
|
|
case EnumAttendanceType.Present:
|
|
case EnumAttendanceType.Delay:
|
|
case EnumAttendanceType.Late:
|
|
case EnumAttendanceType.Early:
|
|
case EnumAttendanceType.OutSideDuty:
|
|
presentCase.AppendFormat(" when AttenType = {0} then 1 ", (int)enItem);
|
|
|
|
break;
|
|
case EnumAttendanceType.HalfDay:
|
|
presentCase.AppendFormat(" when AttenType = {0} then 0.5 ", (int)enItem);
|
|
absentCase.AppendFormat(" when AttenType = {0} then 0.5 ", (int)enItem);
|
|
break;
|
|
default:
|
|
break;
|
|
}
|
|
}
|
|
|
|
presentCase.Append(" else 0 END ");
|
|
absentCase.Append(" else 0 END ");
|
|
holidayCase.Append(" else 0 END ");
|
|
leaveCase.Append(" else 0 END ");
|
|
|
|
#endregion
|
|
|
|
string sql = SQLParser.MakeSQL(@"select EmployeeID,
|
|
Count(EMPLOYEEID) TotalDays,
|
|
Sum(%q) TotalPresent,
|
|
Sum(%q) TotalAbsent,
|
|
Sum(%q) TotalHoliday,
|
|
Sum(%q) TotalLeave
|
|
from DailyAttnProcess
|
|
where AttnDate between %d and %d
|
|
group by EmployeeID", presentCase.ToString(), absentCase.ToString()
|
|
, holidayCase.ToString(), leaveCase.ToString(),
|
|
FromDate, ToDate);
|
|
|
|
return tc.ExecuteDataSet(sql);
|
|
}
|
|
internal static DataTable getMyTeamNotApprovedList(TransactionContext tc, int linemanagerid)
|
|
{
|
|
DataTable presentDays = new DataTable();
|
|
try
|
|
{
|
|
string sql = string.Empty;
|
|
|
|
presentDays = tc.ExecuteDataTable(@"SELECT dap.*, e.EmployeeID, EmployeeNo, e.Name, e.categoryID, e.GradeID,
|
|
LocationID, d.name as DesignationName, g.DESCRIPTION GradeName, dept.DESCRIPTION DepartmentName,
|
|
S.ShiftID, s.ShortName From Employee e
|
|
left join designation d on d.DESIGNATIONID = e.DESIGNATIONID
|
|
LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN GRADES g ON g.GRADEID = e.GRADEID
|
|
Inner JOIN DAILYATTNPROCESS dap ON dap.EMPLOYEEID = e.EMPLOYEEID
|
|
LEFT JOIN Shift s ON s.ShiftID = dap.ShiftID
|
|
WHERE dap.WFStatus = 1 AND e.linemanagerid =%n and e.status =1 ", linemanagerid);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return presentDays;
|
|
}
|
|
|
|
internal static DataTable getMyTeamNotApprovedListMobile(TransactionContext tc, int linemanagerid)
|
|
{
|
|
DataTable presentDays = new DataTable();
|
|
string sql = string.Empty;
|
|
|
|
presentDays = tc.ExecuteDataTable(@"SELECT dap.DailyAttnProcessID, dap.attnDate , dap.AttenType, dap.InTime, dap.OutTime,
|
|
dap.ActualInTime, dap.ActualOutTime, dap.WFStatus, dap.Comments, dap.ShiftID, dap.EmpRemarks,s.ShortName, e.EmployeeID, EmployeeNo, e.Name
|
|
From Employee e
|
|
Inner JOIN DAILYATTNPROCESS dap ON dap.EMPLOYEEID = e.EMPLOYEEID
|
|
LEFT JOIN Shift s ON s.ShiftID = dap.ShiftID
|
|
WHERE dap.WFStatus = 1 AND e.linemanagerid =%n and e.status =1 and dap.AttnDate >%d", linemanagerid, DateTime.Today.AddMonths(-3));
|
|
|
|
return presentDays;
|
|
}
|
|
|
|
internal static DataTable getMyTeamNotApprovedListMobileWithNoLock(TransactionContext tc, int linemanagerid)
|
|
{
|
|
DataTable presentDays = new DataTable();
|
|
string sql = string.Empty;
|
|
|
|
presentDays = tc.ExecuteDataTable(@"SELECT dap.DailyAttnProcessID, dap.attnDate , dap.AttenType, dap.InTime, dap.OutTime,
|
|
dap.ActualInTime, dap.ActualOutTime, dap.WFStatus, dap.Comments, dap.ShiftID, dap.EmpRemarks,s.ShortName, e.EmployeeID, EmployeeNo, e.Name
|
|
From Employee e With(NoLock)
|
|
Inner JOIN DAILYATTNPROCESS dap With(NoLock) ON dap.EMPLOYEEID = e.EMPLOYEEID
|
|
LEFT JOIN Shift s With(NoLock) ON s.ShiftID = dap.ShiftID
|
|
WHERE dap.WFStatus = 1 AND e.linemanagerid =%n and e.status =1 and dap.AttnDate >%d", linemanagerid, DateTime.Today.AddMonths(-3));
|
|
|
|
return presentDays;
|
|
}
|
|
|
|
|
|
internal static DataTable getMyTeamAbsentAndLeave(TransactionContext tc, int linemanagerid, DateTime fromdate, DateTime todate)
|
|
{
|
|
DataTable presentDays = new DataTable();
|
|
try
|
|
{
|
|
string sql = string.Empty;
|
|
|
|
presentDays = tc.ExecuteDataTable(@"SELECT distinct dap.*, e.EmployeeID, EmployeeNo, e.Name, e.categoryID, e.GradeID, l.Description leaveName,
|
|
LocationID, d.name as DesignationName, g.DESCRIPTION GradeName, dept.DESCRIPTION DepartmentName From Employee e
|
|
left join designation d on d.DESIGNATIONID = e.DESIGNATIONID
|
|
LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN GRADES g ON g.GRADEID = e.GRADEID
|
|
LEFT JOIN DAILYATTNPROCESS dap ON dap.EMPLOYEEID = e.EMPLOYEEID
|
|
LEFT JOIN leave l ON dap.referenceid = l.leaveid
|
|
WHERE dap.AttenType IN ( %n, %n) AND e.linemanagerid =%n and e.status =1
|
|
and dap.attndate between %d and %d ", EnumAttendanceType.Absent,
|
|
EnumAttendanceType.Leave, linemanagerid, fromdate, todate);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return presentDays;
|
|
}
|
|
|
|
|
|
internal static IDataReader GetAttnDataByWFStatusForLM(TransactionContext tc, DateTime fromDate, DateTime toDate, string empIDs, EnumWFAttnStatus status)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL(@"Select * from DailyAttnProcess
|
|
where EmployeeID in (%q) and WFStatus=%n Order By AttnDate desc", empIDs, (int)status, fromDate, toDate);
|
|
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static DataSet GetZandDocLeave(TransactionContext tc, DateTime fromdate, DateTime todate, EnumAttendanceType attnType, int payrollTypeId, int? refID)
|
|
{
|
|
DataSet dailyInOutAndAbsent = new DataSet();
|
|
try
|
|
{
|
|
//4, 'DL', 'Doctors Leave'
|
|
//21, 'ZL', 'Z Leave'
|
|
string sql = "";
|
|
if (refID == null)
|
|
{
|
|
sql = SQLParser.MakeSQL("select dap.Employeeid, Count(*) Total"
|
|
+ " from DailyAttnProcess dap Inner join Employee e on e.employeeID=dap.employeeID where "
|
|
+ " "
|
|
+ " "
|
|
+ " dap.AttenType = %n "
|
|
+ "AND dap.AttnDate BETWEEN %d AND %d and e.PayrollTypeID=%n Group by dap.Employeeid", attnType, fromdate, todate, payrollTypeId);
|
|
|
|
//sql = string.Format(@"select EmployeeID,
|
|
// SUM(Case
|
|
// When AttnType = {0} Then 1
|
|
// When AttnType = {1} Then 0.5
|
|
// End) as TotalAbsent from DailyAttnProcess
|
|
// where AttnDate between '{2}' AND '{3}'
|
|
// AND AttnType in ({0},{1})
|
|
// group by EmployeeID order by EmployeeID",
|
|
// (int)EnumAttendanceType.Absent, (int)EnumAttendanceType.HalfDay,
|
|
// fromdate,
|
|
// todate);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL("select dap.Employeeid, Count(*) Total"
|
|
+ " from DailyAttnProcess dap Inner join Employee e on e.employeeID=dap.employeeID where "
|
|
+ " "
|
|
+ " "
|
|
+ " dap.AttenType = %n and dap.ReferenceID=%n "
|
|
+ "AND dap.AttnDate BETWEEN %d AND %d and e.PayrollTypeID=%n Group by dap.Employeeid", attnType, refID, fromdate, todate, payrollTypeId);
|
|
|
|
}
|
|
dailyInOutAndAbsent = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return dailyInOutAndAbsent;
|
|
}
|
|
internal static DataSet GetUnauthorizeCount(TransactionContext tc, DateTime fromdate, DateTime todate, int payrollTypeId)
|
|
{
|
|
DataSet dailyInOutAndAbsent = new DataSet();
|
|
try
|
|
{
|
|
//4, 'DL', 'Doctors Leave'
|
|
//21, 'ZL', 'Z Leave'
|
|
string sql = "";
|
|
|
|
sql = string.Format(@"select EMPLOYEEID,
|
|
SUM(Case
|
|
When AttenType = {0} Then 1
|
|
When AttenType = {1} Then 0.5
|
|
End) as TOTAL from DailyAttnProcess
|
|
where AttnDate between '{2}' AND '{3}'
|
|
AND AttenType in ({0},{1})
|
|
group by EmployeeID order by EmployeeID",
|
|
(int)EnumAttendanceType.Absent, (int)EnumAttendanceType.HalfDay,
|
|
fromdate,
|
|
todate);
|
|
|
|
dailyInOutAndAbsent = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return dailyInOutAndAbsent;
|
|
}
|
|
|
|
|
|
internal static double GetStatusCount(TransactionContext tc, DateTime fromdate, DateTime todate, EnumAttendanceType attnType, int payrollTypeId,
|
|
int empid, int? refID)
|
|
{
|
|
double attncount = 0;
|
|
try
|
|
{
|
|
string empsql = "";
|
|
empsql = empsql + " and dap.EmployeeID =" + empid + " ";
|
|
|
|
|
|
string sql = "";
|
|
if (refID == null)
|
|
{
|
|
sql = SQLParser.MakeSQL("select Count(*) Total"
|
|
+ " from DailyAttnProcess dap where "
|
|
+ " "
|
|
+ " "
|
|
+ " dap.AttenType = %n "
|
|
+ " AND dap.AttnDate BETWEEN %d AND %d %q Group by dap.Employeeid",
|
|
attnType, fromdate, todate, empsql);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL("select dap.Employeeid, Count(*) Total"
|
|
+ " from DailyAttnProcess dap Inner join Employee e on e.employeeID=dap.employeeID where "
|
|
+ " "
|
|
+ " "
|
|
+ " dap.AttenType = %n and dap.ReferenceID=%n "
|
|
+ "AND dap.AttnDate BETWEEN %d AND %d and e.PayrollTypeID=%n " +
|
|
" %q Group by dap.Employeeid", attnType, refID, fromdate, todate, payrollTypeId, empsql);
|
|
|
|
}
|
|
|
|
var dvalue = tc.ExecuteScalar(sql);
|
|
if (dvalue != DBNull.Value)
|
|
{
|
|
return Convert.ToDouble(dvalue);
|
|
|
|
}
|
|
else return Convert.ToDouble(0);
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return attncount;
|
|
}
|
|
|
|
|
|
internal static DataSet GetWorkingDayLateCount(TransactionContext tc, DateTime fromdate, DateTime todate, int payrollTypeId)
|
|
{
|
|
DataSet dailyInOutAndAbsent = new DataSet();
|
|
try
|
|
{
|
|
//4, 'DL', 'Doctors Leave'
|
|
//21, 'ZL', 'Z Leave'
|
|
string sql = SQLParser.MakeSQL("select dap.Employeeid, Count(*) Total"
|
|
+ " from DailyAttnProcess dap Inner join Employee e on e.employeeID=dap.employeeID where "
|
|
+ " dap.IsLate = %b AND WorkDayType=%n"
|
|
+ "AND dap.AttnDate BETWEEN %d AND %d and e.PayrollTypeID=%n Group by dap.Employeeid ",
|
|
true, EnumWorkPlanDayType.WorkingDay, fromdate, todate, payrollTypeId);
|
|
dailyInOutAndAbsent = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return dailyInOutAndAbsent;
|
|
}
|
|
|
|
internal static DataSet GetMonthlyAttendanceData(TransactionContext tc, DateTime dFromDate, DateTime dToDate, string sEmpID)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"
|
|
SELECT dep.DESCRIPTION AS DEPNAME, e.EMPLOYEENO, e.NAME, des.NAME AS DESNAME,
|
|
loc.DESCRIPTION AS LOC, dap.AttnDate, dap.InTime, dap.OutTime, dap.WorkHour,
|
|
dap.LateHour, dap.AttenType, iif(dap.AttenType != 5, l.DESCRIPTION, nh.DESCRIPTION) AS WorkDayName
|
|
FROM DAILYATTNPROCESS dap
|
|
JOIN EMPLOYEE e ON dap.EMPLOYEEID = e.EMPLOYEEID
|
|
LEFT JOIN DEPARTMENT dep ON dep.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DESIGNATION des ON des.DESIGNATIONID = e.DESIGNATIONID
|
|
LEFT JOIN LOCATION loc ON loc.LOCATIONID = e.LOCATIONID
|
|
LEFT JOIN LEAVE l ON l.LEAVEID = dap.ReferenceID
|
|
LEFT JOIN ATTNNATIONALHOLIDAY nh ON nh.ATTNNATIONALHOLIDAYID = dap.ReferenceID
|
|
WHERE e.EMPLOYEEID IN (%q) AND dap.AttnDate BETWEEN %d AND %d
|
|
ORDER BY dep.DESCRIPTION",
|
|
sEmpID, dFromDate, dToDate);
|
|
|
|
return tc.ExecuteDataSet(sql);
|
|
}
|
|
|
|
internal static int GetAbsentCount(TransactionContext tc, DateTime fromDate, DateTime toDate, int empid, int payrollTypeId)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL("select Count(*) Total"
|
|
+ " from DailyAttnProcess dap Inner join Employee e on e.employeeID=dap.employeeID where "
|
|
+ " dap.AttnType = %n "
|
|
+ "AND dap.AttnDate BETWEEN %d AND %d and e.PayrollTypeID=%n and dap.Employeeid=%n",
|
|
EnumAttendanceType.Absent, fromDate, toDate, payrollTypeId, empid);
|
|
|
|
var dvalue = tc.ExecuteScalar(sql);
|
|
if (dvalue != DBNull.Value)
|
|
{
|
|
return Convert.ToInt32(dvalue);
|
|
|
|
}
|
|
else return 0;
|
|
}
|
|
internal static int GetLateCount(TransactionContext tc, DateTime fromDate, DateTime toDate, int empid, int payrollTypeId)
|
|
{
|
|
string sql = string.Empty;
|
|
sql = SQLParser.MakeSQL("select Count(*) Total"
|
|
+ " from DailyAttnProcess dap Inner join Employee e on e.employeeID=dap.employeeID where "
|
|
+ " "
|
|
+ " "
|
|
+ " dap.islate = %n "
|
|
+ "AND dap.AttnDate BETWEEN %d AND %d and e.PayrollTypeID=%n and dap.Employeeid=%n",
|
|
true, fromDate, toDate, payrollTypeId
|
|
, empid);
|
|
|
|
var dvalue = tc.ExecuteScalar(sql);
|
|
if (dvalue != DBNull.Value)
|
|
{
|
|
return Convert.ToInt32(dvalue);
|
|
|
|
}
|
|
else return Convert.ToInt32(0);
|
|
}
|
|
|
|
internal static DataTable GetAttendanceDashboard(TransactionContext tc, Employee oEmp)
|
|
{
|
|
string subSQL = SQLParser.MakeSQL(@"EMPLOYEEID IN(SELECT EMPLOYEEID as EmployeeID FROM EMPLOYEE
|
|
Where LINEMANAGERID = %n and status = %n )", oEmp.ID, EnumEmpStatus.Live);
|
|
|
|
string sSQL = SQLParser.MakeSQL(@"
|
|
select (select count (*) FROM EMPLOYEE where %q) AS Total,
|
|
(select count (*) FROM DAILYATTNPROCESS where ATTENTYPE = 1 AND ATTNDATE = getdate() AND %q ) AS Present,
|
|
(select count (*) FROM DAILYATTNPROCESS where ATTENTYPE = 2 AND ATTNDATE = getdate() AND %q ) AS Absent,
|
|
(select count (*) FROM DAILYATTNPROCESS where ATTENTYPE = 4 AND ATTNDATE = getdate() AND %q ) AS Leave,
|
|
(select count (*) FROM DAILYATTNPROCESS where ATTENTYPE = 8 AND ATTNDATE = getdate() AND %q ) AS WeeklyHoliday,
|
|
(select count (*) FROM DAILYATTNPROCESS where ISLATE = 1 AND ATTNDATE = getdate() AND %q ) AS Late,
|
|
(select ATTNDATE FROM DAILYATTNPROCESS where ATTNDATE = getdate()) as AttnDate",
|
|
subSQL, subSQL, subSQL, subSQL, subSQL, subSQL);
|
|
|
|
return tc.ExecuteDataTable(sSQL);
|
|
}
|
|
|
|
internal static DataTable getTopEmpAbsentData(TransactionContext tc, Employee oEmp, EnumAttendanceType type)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(@"
|
|
SELECT E.name as empName, count(*) as AbsentCount,
|
|
E.EmployeeId as EmpId, DAP.attentype
|
|
FROM DAILYATTNPROCESS DAP
|
|
JOIN EMPLOYEE E ON DAP.EMPLOYEEID = E.EMPLOYEEID
|
|
WHERE DAP.ATTENTYPE = %n
|
|
AND DAP.attndate<= DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) AND DAP.attndate>= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
|
|
AND E.LINEMANAGERID = %n AND E.status = %n
|
|
GROUP BY E.EMPLOYEEID, DAP.ATTENTYPE, E.NAME ORDER BY AbsentCount DESC", type, oEmp.ID, EnumEmpStatus.Live);
|
|
|
|
return tc.ExecuteDataTable(sSQL);
|
|
}
|
|
internal static DataTable getcorehrAbsentData(TransactionContext tc, Employee oEmp, EnumAttendanceType type)
|
|
{
|
|
string subSQL = "";
|
|
if (oEmp != null && oEmp.LineManagerID != null)
|
|
{
|
|
subSQL = SQLParser.MakeSQL(@"AND E.DEPARTMENTID = %n ", oEmp.DepartmentID);
|
|
}
|
|
string sSQL = SQLParser.MakeSQL(@"SELECT E.name as empName, count(*) as AbsentCount,
|
|
E.EmployeeId as EmpId, DAP.attentype
|
|
FROM DAILYATTNPROCESS DAP
|
|
JOIN EMPLOYEE E ON DAP.EMPLOYEEID = E.EMPLOYEEID
|
|
WHERE DAP.ATTENTYPE = %n
|
|
AND DAP.attndate<= DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) AND DAP.attndate>= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
|
|
AND E.status = %n %q
|
|
GROUP BY E.EMPLOYEEID, DAP.ATTENTYPE, E.NAME ORDER BY AbsentCount DESC", type, EnumEmpStatus.Live, subSQL);
|
|
|
|
return tc.ExecuteDataTable(sSQL);
|
|
}
|
|
//internal static DataSet AttnDaysSummaryForSalaryProcess(TransactionContext tc, DateTime FromDate, DateTime ToDate)
|
|
//{
|
|
|
|
|
|
// StringBuilder presentCase = new StringBuilder();
|
|
|
|
// StringBuilder absentCase = new StringBuilder();
|
|
// StringBuilder holidayCase = new StringBuilder();
|
|
// StringBuilder leaveCase = new StringBuilder();
|
|
|
|
// #region Generating Cases
|
|
// presentCase.Append("Case ");
|
|
// absentCase.Append("Case ");
|
|
// holidayCase.Append("Case ");
|
|
// leaveCase.Append("Case ");
|
|
|
|
// List<EnumAttendanceType> attnTypes = Enum.GetValues(typeof(EnumAttendanceType)).Cast<EnumAttendanceType>().ToList();
|
|
|
|
// foreach (EnumAttendanceType enItem in attnTypes)
|
|
// {
|
|
|
|
// switch (enItem)
|
|
// {
|
|
// case EnumAttendanceType.Absent:
|
|
// absentCase.AppendFormat(" when AttnType = {0} then 1 ", (int)enItem);
|
|
|
|
// break;
|
|
// case EnumAttendanceType.Holiday:
|
|
// case EnumAttendanceType.WeeklyHoliday:
|
|
// holidayCase.AppendFormat(" when AttnType = {0} then 1 ", (int)enItem);
|
|
|
|
// break;
|
|
// case EnumAttendanceType.Leave:
|
|
// leaveCase.AppendFormat(" when AttnType = {0} then 1 ", (int)enItem);
|
|
// break;
|
|
// case EnumAttendanceType.Present:
|
|
// case EnumAttendanceType.Delay:
|
|
// case EnumAttendanceType.Late:
|
|
// case EnumAttendanceType.Early:
|
|
// case EnumAttendanceType.OutSideDuty:
|
|
// presentCase.AppendFormat(" when AttnType = {0} then 1 ", (int)enItem);
|
|
|
|
// break;
|
|
// case EnumAttendanceType.HalfDay:
|
|
// presentCase.AppendFormat(" when AttnType = {0} then 0.5 ", (int)enItem);
|
|
// absentCase.AppendFormat(" when AttnType = {0} then 0.5 ", (int)enItem);
|
|
// break;
|
|
// default:
|
|
// break;
|
|
// }
|
|
// }
|
|
|
|
// presentCase.Append(" else 0 END ");
|
|
// absentCase.Append(" else 0 END ");
|
|
// holidayCase.Append(" else 0 END ");
|
|
// leaveCase.Append(" else 0 END ");
|
|
|
|
// #endregion
|
|
|
|
// string sql = SQLParser.MakeSQL(@"select EmployeeID,
|
|
// Count(EMPLOYEEID) TotalDays,
|
|
// Sum(%q) TotalPresent,
|
|
// Sum(%q) TotalAbsent,
|
|
// Sum(%q) TotalHoliday,
|
|
// Sum(%q) TotalLeave
|
|
// from DailyAttnProcess
|
|
// where AttnDate between %d and %d
|
|
// group by EmployeeID", presentCase.ToString(), absentCase.ToString()
|
|
// , holidayCase.ToString(), leaveCase.ToString(),
|
|
// FromDate, ToDate);
|
|
|
|
// return tc.ExecuteDataSet(sql);
|
|
//}
|
|
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(e.EMPSIGNATURE,'') AS EmpSign,
|
|
IsNULL(des.NAME,'') AS Desig,
|
|
IsNULL(gd.DESCRIPTION,'') AS Grade,
|
|
IsNULL(gd.COLORCODE,'White') AS GradeColor,
|
|
IsNULL(acrd.CardNumber,'') AS CardNo,
|
|
'Dept' = case when dept5.tire = 5 then dept2.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 2 then dept5.[DESCRIPTION]
|
|
END,
|
|
'Floor' = case when dept5.tire = 5 then dept3.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 3 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Section' = case when dept5.tire = 5 then dept4.[DESCRIPTION]
|
|
when dept5.tire = 4 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line' = case when dept5.tire = 5 then dept5.[DESCRIPTION]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
CASE
|
|
WHEN rl.Name = 'Hindu' Then 'Red'
|
|
WHEN rl.name IS NULL Then 'White'
|
|
ELSE 'White' END NameColor,
|
|
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,
|
|
|
|
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 dept5 ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4 ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3 ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2 ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
LEFT JOIN AccessCard acrd
|
|
ON e.CardID = acrd.AccessCardID
|
|
LEFT JOIN RELIGION rl
|
|
ON e.religionid = rl.religionid
|
|
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,
|
|
PermanentPO 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 GetEmpCardInfoPerPageBangla(TransactionContext tc, string sEmpID)
|
|
{
|
|
DataSet empAttenInfo = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO AS IDNo,
|
|
e.BANGLANAME NAME,e.JOININGDATE AS DOJ,
|
|
IsNULL(e.[PhotoPath],'') AS EmpPhoto,
|
|
ISNULL(e.EMPSIGNATURE,'') AS EmpSign,
|
|
IsNULL(des.BANGLANAME,'') AS Desig,
|
|
IsNULL(des.WorkType,'') AS WorkType,
|
|
IsNULL(gd.BanglaName,'') AS Grade,
|
|
IsNULL(gd.COLORCODE,'White') AS GradeColor,
|
|
IsNULL(acrd.CardNumber,'') AS CardNo,
|
|
'Dept' = case when dept5.tire = 5 then dept2.[BANGLADescription]
|
|
when dept5.tire = 4 then dept3.[BANGLADescription]
|
|
when dept5.tire = 3 then dept4.[BANGLADescription]
|
|
when dept5.tire = 2 then dept5.[BANGLADescription]
|
|
END,
|
|
'Floor' = case when dept5.tire = 5 then dept3.[BANGLADescription]
|
|
when dept5.tire = 4 then dept4.[BANGLADescription]
|
|
when dept5.tire = 3 then dept5.[BANGLADescription]
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Section' = case when dept5.tire = 5 then dept4.[BANGLADescription]
|
|
when dept5.tire = 4 then dept5.[BANGLADescription]
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
'Line' = case when dept5.tire = 5 then dept5.[BANGLADescription]
|
|
when dept5.tire = 4 then ''
|
|
when dept5.tire = 3 then ''
|
|
when dept5.tire = 2 then ''
|
|
END,
|
|
CASE
|
|
WHEN rl.Name = 'Hindu' Then 'Red'
|
|
WHEN rl.name IS NULL Then 'White'
|
|
ELSE 'White' END NameColor,
|
|
CASE
|
|
WHEN e.BLOODGROUP =1 THEN 'NONE'
|
|
WHEN e.BLOODGROUP =2 THEN 'G+'
|
|
WHEN e.BLOODGROUP =3 THEN 'G-'
|
|
WHEN e.BLOODGROUP =4 THEN 'we+'
|
|
WHEN e.BLOODGROUP =5 THEN 'we-'
|
|
WHEN e.BLOODGROUP =6 THEN 'I+'
|
|
WHEN e.BLOODGROUP =7 THEN 'I-'
|
|
WHEN e.BLOODGROUP =8 THEN 'Gwe+'
|
|
WHEN e.BLOODGROUP =9 THEN 'Gwe-'
|
|
WHEN e.BLOODGROUP =10 THEN 'NA'
|
|
ELSE ''
|
|
END BG,
|
|
IsNULL(CardOpp.AssignDate,'') AS IssueDate,
|
|
IsNULL(CardOpp.ExpireDate,'') AS ExpireDate,
|
|
ISNULL(e.NationalID,'') AS NationalID,
|
|
IsNULL(cts.ADDVill,'') ADDVill,
|
|
IsNULL(cts.ADDDist,'') ADDDist,
|
|
IsNULL(cts.ADDPO,'') ADDPO,
|
|
IsNULL(cts.ADDPS,'') ADDPS,
|
|
IsNULL(e.mobileno,'') ADDPhone,
|
|
IsNULL(EMPN.EmergencyMobile,'') NomineeMobile
|
|
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 dept5 ON dept5.DEPARTMENTID = e.DEPARTMENTID
|
|
LEFT JOIN DEPARTMENT dept4 ON dept4.DEPARTMENTID = dept5.PARENTID
|
|
LEFT JOIN DEPARTMENT dept3 ON dept3.DEPARTMENTID = dept4.PARENTID
|
|
LEFT JOIN DEPARTMENT dept2 ON dept2.DEPARTMENTID = dept3.PARENTID
|
|
LEFT JOIN AccessCard acrd
|
|
ON e.CardID = acrd.AccessCardID
|
|
LEFT JOIN RELIGION rl
|
|
ON e.religionid = rl.religionid
|
|
LEFT JOIN
|
|
(SELECT EmployeeID,CardID,MAX(AssignDate) AS AssignDate, Max(ExpireDate) AS ExpireDate
|
|
FROM CardOperation
|
|
GROUP BY EmployeeID,CARDID) CardOpp
|
|
ON CardOpp.EmployeeID = e.EmployeeID
|
|
AND CardOpp.CardID = e.CardID
|
|
LEFT JOIN
|
|
(SELECT EmployeeID,PERMANENTADDRESSINBANGLA AS ADDVill,
|
|
ISNULL(dis.BANGLANAME,'') ADDDist,
|
|
ParmanentPOInBangla AS ADDPO,
|
|
ISNULL(thn.BANGLANAME,'') 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
|
|
LEFT JOIN EMPCONTACT EMPN ON EMPN.EMPLOYEEID = e.EMPLOYEEID", sEmpID);
|
|
|
|
empAttenInfo = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return empAttenInfo;
|
|
}
|
|
|
|
internal static DataTable GetAttandanceDashboardData(TransactionContext tc, DateTime fromDate, DateTime toDate, int shiftId, string departmentIds, string employeeIds, bool appliedForRegularize,
|
|
int status, string otHourType, int otHour, int CurrentUserId, bool isCalledFromEss, string lineManager, string statusText, int payrollTypeId)
|
|
{
|
|
DataSet ds = new DataSet();
|
|
string orderby;
|
|
string whereClause = string.Empty;
|
|
string sql;
|
|
string sql1;
|
|
|
|
if (fromDate != null && toDate != null)
|
|
{
|
|
whereClause = SQLParser.MakeSQL("WHERE D.ATTNDATE BETWEEN %d AND %d", fromDate, toDate);
|
|
}
|
|
if (payrollTypeId != 0)
|
|
{
|
|
whereClause += SQLParser.MakeSQL(" AND E.PAYROLLTYPEID = %n", payrollTypeId);
|
|
}
|
|
if (shiftId != 0)
|
|
{
|
|
whereClause += SQLParser.MakeSQL(" AND S.ShiftID = %n", shiftId);
|
|
}
|
|
if (isCalledFromEss)
|
|
{
|
|
if (CurrentUserId != 0 && employeeIds == string.Empty)
|
|
{
|
|
whereClause += SQLParser.MakeSQL(" AND E.EMPLOYEEID IN ( SELECT Employeeid from EmployeeCordinator WHERE CordinatorID = %q)", CurrentUserId);
|
|
}
|
|
else if (employeeIds != string.Empty)
|
|
{
|
|
whereClause += SQLParser.MakeSQL(" AND E.EMPLOYEEID IN (%q)", employeeIds);
|
|
}
|
|
}
|
|
else
|
|
{
|
|
if (employeeIds != string.Empty)
|
|
{
|
|
whereClause += SQLParser.MakeSQL(" AND E.EMPLOYEEID IN (%q)", employeeIds);
|
|
}
|
|
}
|
|
if (lineManager != string.Empty)
|
|
{
|
|
whereClause += SQLParser.MakeSQL(" AND E.EMPLOYEEID IN ( SELECT EMPLOYEEID FROM EMPLOYEE WHERE LINEMANAGERID = %q)", lineManager);
|
|
}
|
|
|
|
if (departmentIds != string.Empty)
|
|
{
|
|
whereClause += SQLParser.MakeSQL(" AND E.DEPARTMENTID IN (%q)", departmentIds);
|
|
}
|
|
|
|
if (status == 11)
|
|
{
|
|
whereClause += SQLParser.MakeSQL(" AND D.islate = %n", 1);
|
|
}
|
|
if (status > 0 && status < 9)
|
|
{
|
|
if ((EnumAttendanceType)status == EnumAttendanceType.Holiday)
|
|
whereClause += SQLParser.MakeSQL(" AND (D.ATTENTYPE = %n OR D.WorkDayType=%n)", status, EnumWorkPlanDayType.NationalHoliday);
|
|
else if ((EnumAttendanceType)status == EnumAttendanceType.WeeklyHoliday)
|
|
whereClause += SQLParser.MakeSQL(" AND (D.ATTENTYPE = %n OR D.WorkDayType=%n)", status, EnumWorkPlanDayType.WeeklyHoliday);
|
|
else whereClause += SQLParser.MakeSQL(" AND D.ATTENTYPE = %n", status);
|
|
}
|
|
else if (status > 20)
|
|
{
|
|
whereClause += SQLParser.MakeSQL(" AND (D.EmpRemarks = %s or D.Reason = %s)", statusText, statusText);
|
|
}
|
|
if (appliedForRegularize)
|
|
{
|
|
whereClause += SQLParser.MakeSQL(" AND D.WFSTATUS = %n", 1);
|
|
}
|
|
if (otHourType != string.Empty && otHour > 0)
|
|
{
|
|
whereClause += SQLParser.MakeSQL(" AND D.OTHOUR %q %n", otHourType, otHour);
|
|
}
|
|
if (status == 9)
|
|
{
|
|
orderby = "ORDER BY t.ATTNDATE DESC, t.EMPLOYEENO, t.EMPLOYEENAME";
|
|
}
|
|
else
|
|
{
|
|
orderby = "ORDER BY D.ATTNDATE DESC, E.EMPLOYEENO, E.Name";
|
|
}
|
|
try
|
|
{
|
|
sql1 = SQLParser.MakeSQL(
|
|
@"SELECT
|
|
D.ATTNDATE,
|
|
D.OTHOUR,
|
|
E.EMPLOYEEID,
|
|
E.EMPLOYEENO,
|
|
E.NAME AS EMPLOYEENAME,
|
|
S.Name AS SHIFT, S.ShiftID,
|
|
D.EmpRemarks,
|
|
D.Reason,
|
|
D.ActualInTime,
|
|
D.ActualOutTime,
|
|
D.Wfstatus wfstatus,
|
|
D.LMREMARKS,
|
|
DATEDIFF(MINUTE, D.InTime, D.OutTime) / 60 As hourDifference,
|
|
CASE
|
|
WHEN D.WFSTATUS = 0 THEN 'No'
|
|
WHEN D.WFSTATUS = 1 THEN 'Yes'
|
|
ELSE 'Unknown'
|
|
END AS WFSTATUSBool,
|
|
CASE
|
|
WHEN D.ATTENTYPE = 0 THEN 'None'
|
|
WHEN D.ATTENTYPE = 1 THEN 'Present'
|
|
WHEN D.ATTENTYPE = 2 THEN 'Absent'
|
|
WHEN D.ATTENTYPE = 3 THEN 'Delay'
|
|
WHEN D.ATTENTYPE = 4 THEN 'Leave'
|
|
WHEN D.ATTENTYPE = 5 THEN 'Holiday'
|
|
WHEN D.ATTENTYPE = 6 THEN 'Weekly Off'
|
|
WHEN D.ATTENTYPE = 7 THEN 'Out Side Duty'
|
|
WHEN D.ATTENTYPE = 8 THEN 'WeeklyHoliday'
|
|
WHEN D.ATTENTYPE = 9 THEN 'Compensation'
|
|
WHEN D.ATTENTYPE = 10 THEN 'Manual Entry'
|
|
WHEN D.ATTENTYPE = 11 THEN 'Late'
|
|
WHEN D.ATTENTYPE = 12 THEN 'Early'
|
|
WHEN D.ATTENTYPE = 18 THEN 'Half Day'
|
|
WHEN D.ATTENTYPE = 22 THEN 'LOA'
|
|
ELSE 'Unknown'
|
|
END AS ATTENTYPESTRING,
|
|
D.ATTENTYPE AS ATTENTYPE,
|
|
D.InTime,
|
|
D.OutTime,
|
|
D.WORKHOUR,
|
|
D.WORKDAYTYPE,
|
|
D.LATEHOUR,
|
|
L.CODE As LeaveType,
|
|
D.COMMENTS,
|
|
CASE
|
|
WHEN D.ISFROMMOBILE = 1 THEN 'Yes'
|
|
WHEN D.ISFROMMOBILE = 0 THEN 'No'
|
|
ELSE 'Unknown'
|
|
END AS ISFROMMOBILE
|
|
FROM
|
|
DAILYATTNPROCESS D
|
|
INNER JOIN
|
|
EMPLOYEE E ON D.EMPLOYEEID = E.EMPLOYEEID
|
|
LEFT JOIN
|
|
Shift S ON D.SHIFTID = S.ShiftID
|
|
LEFT JOIN
|
|
LEAVE L ON D.REFERENCEID = L.LEAVEID %q", whereClause);
|
|
|
|
if (status == 9)
|
|
{
|
|
// for irregular work hour main query inserted as sub query
|
|
sql = SQLParser.MakeSQL("SELECT * From (%q) t Where t.hourDifference < 8 %q", sql1, orderby);
|
|
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL("%q %q", sql1, orderby);
|
|
}
|
|
ds = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return ds.Tables[0];
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
} |