public Hours GetAttendanceSummary()
{
String q = $@"
select atid, dutydetails.did, DATE_FORMAT(date, '%Y-%m-%d') as Date, SUBSTRING(DAYNAME(DATE_FORMAT(date, '%Y-%m-%d')) FROM 1 FOR 3) as day,
concat (ti_hh,':',ti_mm,' ',ti_period, ' - ',to_actual_hh,':',to_actual_mm,' ',to_actual_period) as Schedule,
timein,
TimeOut,
' ' as normal_day, ' ' as normal_night, ' ' as holiday_day, ' ' as holiday_night, ' ' as total, CONCAT(`year`, '-',period.month,'-', (DATE_FORMAT(date, '%d')), ' ') as Datex,
CONCAT(to_hh,':',to_mm,' ',to_period) as asto,
CONCAT(ti_hh,':',ti_mm,' ',ti_period) as asti
from attendance
left join dutydetails
on dutydetails.did=attendance.did
left join period
on period.pid=attendance.pid
where period = '{period.period}'
and month = '{period.month}'
and year = '{period.year}'
and period.gid = {this.GID}
and aid = {AID}
order by date asc
";
DataTable d = SQLTools.ExecuteQuery(q);
foreach (DataRow f in d.Rows)
{
var sti = f["Datex"].ToString() + f["TimeIn"].ToString();
var sto = f["Datex"].ToString() + f["TimeOut"].ToString();
var sti_c = f["Datex"].ToString() + f["asti"].ToString();
var sto_c = f["Datex"].ToString() + f["asto"].ToString();
DateTime ti = DateTime.Parse(sti);
DateTime to = DateTime.Parse(sto);
HourProcessor proc = new HourProcessor(ti, to, DateTime.Parse(sti_c), DateTime.Parse(sto_c));
hourlist.Add(proc);
f["normal_day"] = proc.GetNormalDay();
f["normal_night"] = proc.GetNormalNight();
f["holiday_day"] = proc.GetHolidayDay();
f["holiday_night"] = proc.GetHolidayNight();
f["total"] = proc.GetTotal();
}
Hours h = new Hours();
attendance_cached = d;
TimeSpan holiday_day, holiday_night, normal_day, normal_night, total;
holiday_day = holiday_night = normal_day = normal_night = total = new TimeSpan();
foreach (HourProcessor x in hourlist)
{
TotalHours += x;
h.holiday_day += x.GetHolidayDayTS();
h.holiday_night += x.GetHolidayNightTS();
h.normal_day += x.GetNormalDayTS();
h.normal_night += x.GetNormalNightTS();;
h.total += x.GetTotalTS();
int pc = 1 + 1;
}
return(h);
}
public DataTable GetAttendance_View(int month, int period, int year)
{
String q = $@"
select atid, dutydetails.did,
CONCAT((DATE_FORMAT(date, '%d')), ' / ' ,
(CONCAT (ti_hh,':',ti_mm,' ',SUBSTRING(ti_period,1,1), '-',to_actual_hh,':',to_actual_mm,SUBSTRING(to_actual_period,1,1)))) as Schedule,
concat( SUBSTRING(timein,1,7), '-' ,SUBSTRING(timeout,1,7)) as ti_to,
' ' as normal_day, ' ' as normal_night, ' ' as holiday_day, ' ' as holiday_night, ' ' as total, timein, timeout, CONCAT(`year`, '-',period.month,'-', (DATE_FORMAT(date, '%d')), ' ') as Date
from attendance
left join dutydetails
on dutydetails.did=attendance.did
left join period
on period.pid=attendance.pid
where period = '{period}'
and month = '{month}'
and year = '{year}'
and gid = '{GID}'
and aid='{AID}'
order by date asc
";
DataTable d = SQLTools.ExecuteQuery(q);
foreach (DataRow f in d.Rows)
{
DateTime ti = DateTime.Parse(f["Date"].ToString() + f["TimeIn"].ToString());
DateTime to = DateTime.Parse(f["Date"].ToString() + f["TimeOut"].ToString());
HourProcessor proc = new HourProcessor(ti, to, ti, to);
hourlist.Add(proc);
f["normal_day"] = proc.GetNormalDay();
f["normal_night"] = proc.GetNormalNight();
f["holiday_day"] = proc.GetHolidayDay();
f["holiday_night"] = proc.GetHolidayNight();
f["total"] = proc.GetTotal();
}
Hours h = new Hours();
attendance_cached = d;
TimeSpan holiday_day, holiday_night, normal_day, normal_night, total;
holiday_day = holiday_night = normal_day = normal_night = total = new TimeSpan();
foreach (HourProcessor x in hourlist)
{
h.holiday_day += x.GetHolidayDayTS();
h.holiday_night += x.GetHolidayNightTS();
h.normal_day += x.GetNormalDayTS();
h.normal_night += x.GetNormalNightTS();;
h.total += x.GetTotalTS();
}
return(d);
}
public static void ComputeArchivedHours(int GuardId)
{
string q = $@"
select msadbarchive.period.pid, msadbarchive.attendance.atid, msadbarchive.dutydetails.did, DATE_FORMAT(msadbarchive.attendance.date, '%Y-%m-%d') as Date, SUBSTRING(DAYNAME(DATE_FORMAT(msadbarchive.attendance.date, '%Y-%m-%d')) FROM 1 FOR 3) as day,
concat (msadbarchive.dutydetails.ti_hh,':',msadbarchive.dutydetails.ti_mm,' ',msadbarchive.dutydetails.ti_period, ' - ',msadbarchive.dutydetails.to_hh,':',msadbarchive.dutydetails.to_mm,' ',msadbarchive.dutydetails.to_period) as Schedule,
msadbarchive.attendance.timein,
msadbarchive.attendance.TimeOut
from msadbarchive.attendance
left join msadbarchive.dutydetails
on msadbarchive.dutydetails.did=msadbarchive.attendance.did
left join msadbarchive.period
on msadbarchive.period.pid=msadbarchive.attendance.pid
where msadbarchive.period.gid = '{GuardId}'
order by date asc;";
DataTable dt = SQLTools.ExecuteQuery(q);
var hourlist = new List <HourProcessor>();
bool firstiter = true;
int CurrentPid = 0;
DataRow LastDataRow;
foreach (DataRow dr in dt.Rows)
{
int ThisPid = int.Parse(dr["PID"].ToString());
if (firstiter)
{
CurrentPid = int.Parse(dr["PID"].ToString());
firstiter = false;
}
if (CurrentPid != ThisPid)
{
Hours h = new Hours();
TimeSpan holiday_day, holiday_night, normal_day, normal_night, total;
holiday_day = holiday_night = normal_day = normal_night = total = new TimeSpan();
foreach (HourProcessor x in hourlist)
{
h.holiday_day += x.GetHolidayDayTS();
h.holiday_night += x.GetHolidayNightTS();
h.normal_day += x.GetNormalDayTS();
h.normal_night += x.GetNormalNightTS();;
h.total += x.GetTotalTS();
}
SQLTools.ExecuteNonQuery($@"
UPDATE `msadbarchive`.`period` SET
`holiday_day`='{h.GetHolidayDay()}',
`holiday_night`='{h.GetHolidayNight()}',
`normal_day`='{h.GetNormalDay()}',
`normal_night`='{h.GetNormalNight()}'
`total` = '{h.GetTotal()}'
WHERE `PID`='{CurrentPid}';
");
CurrentPid = ThisPid;
hourlist.Clear();
}
DateTime ti = Attendance.GetDateTime_(dr["TimeIn"].ToString());
DateTime to = Attendance.GetDateTime_(dr["TimeOut"].ToString());
HourProcessor proc = new HourProcessor(ti, to, ti, to);
hourlist.Add(proc);
SQLTools.ExecuteNonQuery($@"
UPDATE `msadbarchive`.`attendance` SET
`normal_day`='{proc.GetNormalDay()}',
`normal_night`='{proc.GetNormalNight()}',
`holiday_day`='{proc.GetHolidayDay()}',
`holiday_night`='{proc.GetHolidayNight()}',
`total`='{proc.GetTotal()}'
WHERE `AtID`='{dr["atid"]}';
");
LastDataRow = dr;
}
// The General Attendance sumamry starts here.
// i shoulve put more comments damn,
// kani ang totalsummary, gi convert to hourprocessor for dat breakdonw,
HourProcessor cumhours = new HourProcessor();
Hours lh = new Hours();
foreach (HourProcessor x in hourlist)
{
lh.holiday_day += x.GetHolidayDayTS();
lh.holiday_night += x.GetHolidayNightTS();
lh.normal_day += x.GetNormalDayTS();
lh.normal_night += x.GetNormalNightTS();;
lh.total += x.GetTotalTS();
cumhours += x;
}
SQLTools.ExecuteNonQuery($@"
UPDATE `msadbarchive`.`period` SET
`holiday_day`='{lh.GetHolidayDay()}',
`holiday_night`='{lh.GetHolidayNight()}',
`normal_day`='{lh.GetNormalDay()}',
`normal_night`='{lh.GetNormalNight()}',
`hp`='{Data.SerializeHp(cumhours)}',
`total` = '{lh.GetTotal()}'
WHERE `PID`='{CurrentPid}';
");
}