internal static object GetLatenessReport(string resumptionGrace, string startDate, string endDate)
{
DataTable result = new DataTable();
List <AttendanceDetailsObject> res = new List <AttendanceDetailsObject>();
string query = " SELECT " +
" eid_base_data.PROJECTPRIMARYCODE as 'PPCode'," +
" eid_base_data.WEBACCESSPIN as 'UserId'," +
" eid_base_data.TITLEPREFIX as 'Title'," +
" eid_base_data.SURNAME as 'Surname'," +
" eid_base_data.FIRSTNAME as 'First_Name'," +
" eid_base_data.MIDDLENAME as 'Middle_Name'," +
" eid_attendance_log.ClockDate," +
" COUNT(eid_attendance_log.ClockTime) as 'Clock_Count'," +
" MIN(eid_attendance_log.ClockTime) as 'Clock_In'," +
" MAX(eid_attendance_log.ClockTime) as 'Clock_Out'," +
" TIMEDIFF(MAX(eid_attendance_log.TransactionDateTime), MIN(eid_attendance_log.TransactionDateTime)) as 'Time_Spent'" +
" FROM" +
" eid_attendance_log" +
" INNER JOIN eid_base_data ON eid_attendance_log.userPrimaryCode = eid_base_data.PROJECTPRIMARYCODE" +
string.Format(" WHERE eid_attendance_log.ClockDate>= '{0}' AND eid_attendance_log.ClockDate <= '{1}'", startDate, endDate) +
" GROUP BY" +
" eid_attendance_log.userPrimaryCode, eid_attendance_log.ClockDate" +
string.Format(" HAVING Clock_In > '{0}' ORDER BY eid_attendance_log.TransactionDateTime", resumptionGrace)
;
MySqlDataAdapter Adapter = new MySqlDataAdapter(query, myConn);
Adapter.Fill(result);
foreach (DataRow row in result.Rows)
{
double weeklyWorkHours = Math.Round(TimeSpan.Parse(row["Time_Spent"].ToString()).TotalHours, 0);
string summarisedWorkHours = weeklyWorkHours > 40 ? "40 Hours" : weeklyWorkHours.ToString() + " Hours";
AttendanceDetailsObject obj = new AttendanceDetailsObject(
row["UserId"].ToString(),
row["Title"].ToString(),
row["Surname"].ToString(),
row["First_Name"].ToString(),
row["Middle_Name"].ToString(),
FindUserMinistry(row["PPCode"].ToString(), Settings.Default.CDataField1),
((DateTime)row["ClockDate"]).ToString("yyyy-MMM-dd"),
row["Clock_Count"].ToString(),
row["Clock_In"].ToString(),
row["Clock_Out"].ToString(),
Math.Round(TimeSpan.Parse(row["Time_Spent"].ToString()).TotalHours, 0).ToString()
);
res.Add(obj);
}
return(res);
}
internal static List <AttendanceDetailsObject> GeMovementReport(string startDate, string endDate)
{
DataTable result = new DataTable();
List <AttendanceDetailsObject> res = new List <AttendanceDetailsObject>();
string query = " SELECT " +
" eid_base_data.PROJECTPRIMARYCODE as 'PPCode'," +
" eid_base_data.WEBACCESSPIN as 'UserId'," +
" eid_base_data.TITLEPREFIX as 'Title'," +
" eid_base_data.SURNAME as 'Surname'," +
" eid_base_data.FIRSTNAME as 'First_Name'," +
" eid_base_data.MIDDLENAME as 'Middle_Name'," +
" eid_attendance_log.ClockDate as 'Date'," +
" eid_attendance_log.ClockTime as 'Clock_Time'" +
" FROM" +
" eid_attendance_log" +
" LEFT JOIN eid_base_data ON eid_attendance_log.userPrimaryCode = eid_base_data.PROJECTPRIMARYCODE" +
string.Format(" WHERE eid_attendance_log.ClockDate>= '{0}' AND eid_attendance_log.ClockDate <= '{1}'", startDate, endDate) +
" ORDER BY eid_attendance_log.TransactionDateTime";
MySqlDataAdapter Adapter = new MySqlDataAdapter(query, myConn);
Adapter.Fill(result);
foreach (DataRow row in result.Rows)
{
AttendanceDetailsObject obj = new AttendanceDetailsObject(
row["UserId"].ToString(),
row["Title"].ToString(),
row["Surname"].ToString(),
row["First_Name"].ToString(),
row["Middle_Name"].ToString(),
FindUserMinistry(row["PPCode"].ToString(), Settings.Default.CDataField1),
((DateTime)row["Date"]).ToString("yyyy-MMM-dd"),
null,
row["Clock_Time"].ToString(),
null,
null
);
res.Add(obj);
}
return(res);
}
internal static List <AttendanceDetailsObject> GeAbsenteeReport(string startDate, string endDate)
{
DataTable result = new DataTable();
List <AttendanceDetailsObject> res = new List <AttendanceDetailsObject>();
string query = " SELECT " +
" eid_base_data.PROJECTPRIMARYCODE as 'PPCode'," +
" WEBACCESSPIN as 'UserId'," +
" TITLEPREFIX as 'Title'," +
" SURNAME as 'Surname'," +
" FIRSTNAME as 'First_Name'," +
" MIDDLENAME as 'Middle_Name'" +
" FROM" +
" eid_base_data" +
" WHERE PROJECTPRIMARYCODE NOT IN" +
string.Format(" ( SELECT userPrimaryCode from eid_attendance_log WHERE ClockDate>= '{0}' AND ClockDate <= '{1}')", startDate, endDate) +
" ORDER BY Surname, Firstname, Middlename";
MySqlDataAdapter Adapter = new MySqlDataAdapter(query, myConn);
Adapter.Fill(result);
foreach (DataRow row in result.Rows)
{
AttendanceDetailsObject obj = new AttendanceDetailsObject(
row["UserId"].ToString(),
row["Title"].ToString(),
row["Surname"].ToString(),
row["First_Name"].ToString(),
row["Middle_Name"].ToString(),
FindUserMinistry(row["PPCode"].ToString(), Settings.Default.CDataField1),
null,
null,
null,
null,
null
);
res.Add(obj);
}
return(res);
}
internal static object GetTimeSpentSummary(string startDate, string endDate, out string expextedWorkHours)
{
List <AttendanceDetailsObject> res = new List <AttendanceDetailsObject>();
double summarisedWorkHours = ((((Convert.ToDateTime(endDate) - Convert.ToDateTime(startDate)).TotalDays) + 1) * 8);
expextedWorkHours = summarisedWorkHours.ToString();
var rawResult = GetSummaryReport(startDate, endDate);
if (rawResult.Count > 0)
{
var distinctUser = rawResult.GroupBy(x => x.ProjectPrimaryCode).Select(y => new { ProjectPrimaryCode = y.Key });
var distinctDates = rawResult.GroupBy(x => x.Date).Select(y => new { Date = y.Key });
foreach (var user in distinctUser)
{
string timeSpent = GetTotalTimeSpentbyUser(user.ProjectPrimaryCode, rawResult.ToList(), summarisedWorkHours);
var userRes = rawResult.FirstOrDefault(x => x.ProjectPrimaryCode == user.ProjectPrimaryCode);
AttendanceDetailsObject AttendanceDetailsObject = new AttendanceDetailsObject(
userRes.UserId,
userRes.Title,
userRes.Surname,
userRes.FirstName,
userRes.MiddleName,
userRes.Ministry,
userRes.Date,
userRes.ClockCount,
userRes.ClockIn,
userRes.ClockOut,
timeSpent
);
res.Add(AttendanceDetailsObject);
}
}
return(res);
}
