public static string SerializeHp(HourProcessor hp)
{
using (MemoryStream stream = new MemoryStream()) {
new BinaryFormatter().Serialize(stream, hp);
return(Convert.ToBase64String(stream.ToArray()));
}
}
public void Add(HourProcessor e)
{
this.hp["nsu_proper_day_normal"] += e.hp["nsu_proper_day_normal"];
this.hp["nsu_proper_day_special"] += e.hp["nsu_proper_day_special"];
this.hp["nsu_proper_day_regular"] += e.hp["nsu_proper_day_regular"];
this.hp["nsu_proper_night_normal"] += e.hp["nsu_proper_night_normal"];
this.hp["nsu_proper_night_special"] += e.hp["nsu_proper_night_special"];
this.hp["nsu_proper_night_regular"] += e.hp["nsu_proper_night_regular"];
this.hp["nsu_overtime_day_normal"] += e.hp["nsu_overtime_day_normal"];
this.hp["nsu_overtime_day_special"] += e.hp["nsu_overtime_day_special"];
this.hp["nsu_overtime_day_regular"] += e.hp["nsu_overtime_day_regular"];
this.hp["nsu_overtime_night_normal"] += e.hp["nsu_overtime_night_normal"];
this.hp["nsu_overtime_night_special"] += e.hp["nsu_overtime_night_special"];
this.hp["nsu_overtime_night_regular"] += e.hp["nsu_overtime_night_regular"];
this.hp["sun_proper_day_normal"] += e.hp["sun_proper_day_normal"];
this.hp["sun_proper_day_special"] += e.hp["sun_proper_day_special"];
this.hp["sun_proper_day_regular"] += e.hp["sun_proper_day_regular"];
this.hp["sun_proper_night_normal"] += e.hp["sun_proper_night_normal"];
this.hp["sun_proper_night_special"] += e.hp["sun_proper_night_special"];
this.hp["sun_proper_night_regular"] += e.hp["sun_proper_night_regular"];
this.hp["sun_overtime_day_normal"] += e.hp["sun_overtime_day_normal"];
this.hp["sun_overtime_day_special"] += e.hp["sun_overtime_day_special"];
this.hp["sun_overtime_day_regular"] += e.hp["sun_overtime_day_regular"];
this.hp["sun_overtime_night_normal"] += e.hp["sun_overtime_night_normal"];
this.hp["sun_overtime_night_special"] += e.hp["sun_overtime_night_special"];
this.hp["sun_overtime_night_regular"] += e.hp["sun_overtime_night_regular"];
}
public static string AddDutyDetail(int aid, String TI_hr, String TI_min, String TI_ampm, String TO_hr, String TO_min, String TO_ampm, Days days, DateTime DateEffective, DateTime DateDismissed)
{
bool isOverlap = HasOverlap(aid, ($@"{TI_hr}:{TI_min}"), ($@"{TO_hr}:{TO_min}"), days, DateEffective, DateDismissed);
if (isOverlap)
{
return(">");
}
DateTime ti = DateTime.Parse($"3/1/0001 {TI_hr}:{TI_min} {TI_ampm}");
DateTime to = DateTime.Parse($"3/1/0001 {TO_hr}:{TO_min} {TO_ampm}");
HourProcessor hp = new HourProcessor(ti, to, ti, to);
DateTime to_props = ti.AddHours(8);
TimeSpan e = hp.GetTotalTS();
String q = $@"
INSERT INTO `msadb`.`dutydetails`
(`AID`,
`TI_hh`, `TI_mm`, `TI_period`,
`TO_hh`, `TO_mm`, `TO_period`,
`Mon`, `Tue`, `Wed`, `Thu`, `Fri`, `Sat`, `Sun`,
`DStatus`, `minutediff`,`to_actual_hh`,`to_actual_mm`,`to_actual_period`, `date_effective`, `date_dismissal`)
VALUES
('{aid}',
'{TI_hr}','{TI_min}','{TI_ampm}',
'{to_props:hh}','{to_props:mm}','{to_props:tt}',
'{ToInt32(days.Mon)}','{ToInt32(days.Tue)}','{ToInt32(days.Wed)}','{ToInt32(days.Thu)}','{ToInt32(days.Fri)}',
'{ToInt32(days.Sat)}','{ToInt32(days.Sun)}',
'{Enumeration.DutyDetailStatus.Pending}', {(int)e.TotalMinutes},
'{TO_hr}','{TO_min}','{TO_ampm}', '{DateEffective:yyyy-MM-dd}', '{DateDismissed:yyyy-MM-dd}');
";
//if (e.TotalHours > 8) { return "="; } else if (e.TotalHours < 8) { return "="; }
SQLTools.ExecuteNonQuery(q);
return("=");
}
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 HourProcessor operator +(HourProcessor c1, HourProcessor c2)
{
HourProcessor f = new HourProcessor();
string[] keys = f.hp.Keys.ToArray();
foreach (string key in keys)
{
f.hp[key] = c1.hp[key] + c2.hp[key];
}
f.total = c1.total + c2.total;
f.totalday = c1.totalday + c2.totalday;
f.totalnight = c1.totalnight + c2.totalnight;
return(f);
}
public static string UpdateDutyDetail(int did, String TI_hr, String TI_min, String TI_ampm, String TO_hr, String TO_min, String TO_ampm, Days days)
{
string wq = $@"SELECT * FROM msadb.dutydetails where did = '{did}';";
DataTable dt = SQLTools.ExecuteQuery(wq);
int aid;
if (dt.Rows.Count != 0)
{
aid = int.Parse(dt.Rows[0]["aid"].ToString());
}
else
{
MessageBox.Show("No duty details found.");
return("X");
}
DateTime deff = DateTime.Parse(dt.Rows[0]["date_effective"].ToString());
DateTime dend = DateTime.Parse(dt.Rows[0]["date_dismissal"].ToString());
bool isOverlap = HasOverlap(aid, did, ($@"{TI_hr}:{TI_min}"), ($@"{TO_hr}:{TO_min}"), days, deff, dend);
if (isOverlap)
{
return("olap");
}
DateTime ti = DateTime.Parse($"3/1/0001 {TI_hr}:{TI_min} {TI_ampm}");
DateTime to = DateTime.Parse($"3/1/0001 {TO_hr}:{TO_min} {TO_ampm}");
DateTime to_props = ti.AddHours(8);
HourProcessor hp = new HourProcessor(ti, to, ti, to);
TimeSpan e = hp.GetTotalTS();
//if (e.TotalHours > 8) { return ">"; } else if (e.TotalHours < 8) { return "<"; }
Console.Write(e.TotalMinutes);
String q = $@"
UPDATE `msadb`.`dutydetails` SET
`TI_hh`='{TI_hr}', `TI_mm`='{TI_min}', `TI_period`='{TI_ampm}',
`TO_actual_hh`='{TO_hr}', `TO_actual_mm`='{TO_min}', `TO_actual_period`='{TO_ampm}',
`TO_hh`='{to_props:hh}', `TO_mm`='{to_props:mm}', `TO_period`='{to_props:tt}',
`Mon`='{ToInt32(days.Mon)}', `Tue`='{ToInt32(days.Tue)}',
`Wed`='{ ToInt32(days.Wed)}', `Thu`='{ToInt32(days.Thu)}',
`Fri`='{ToInt32(days.Fri)}', `Sat`='{ToInt32(days.Sat)}',
`Sun`='{ToInt32(days.Sun)}', `minutediff`={(int)(e.TotalMinutes)}
WHERE `DID`='{did}';
";
SQLTools.ExecuteNonQuery(q);
return("=");
}
public static string[] GetAttendanceTooltip(int aid, int period, int month, int year)
{
string hpblob =
SQLTools.ExecuteSingleResult(
$@"SELECT hp FROM msadbarchive.period where gid={aid} and period={period} and month = {
month
} and year={year};");
HourProcessor h = (HourProcessor)Payroll._DeserializeObject(hpblob);
string[] a = new string[24];
string[] b =
{
#region +Keys
"nsu_proper_day_normal",
"nsu_overtime_day_normal",
"sun_proper_day_normal",
"sun_overtime_day_normal",
"nsu_proper_night_normal",
"nsu_overtime_night_normal",
"sun_proper_night_normal",
"sun_overtime_night_normal",
"nsu_proper_day_regular",
"nsu_overtime_day_regular",
"sun_proper_day_regular",
"sun_overtime_day_regular",
"nsu_proper_day_special",
"nsu_overtime_day_special",
"sun_proper_day_special",
"sun_overtime_day_special",
"nsu_proper_night_regular",
"nsu_overtime_night_regular",
"sun_proper_night_regular",
"sun_overtime_night_regular",
"nsu_proper_night_special",
"nsu_overtime_night_special",
"sun_proper_night_special",
"sun_overtime_night_special"
#endregion
};
for (int c = 0; c < b.Length; c++)
{
TimeSpan ts = h.hp[b[c]];
a[c] = (b[c][4] == 'p' ? "Regular" : "Overtime") + ": " + (((int)(ts.TotalHours)).ToString("00") + ":" + ((int)ts.Minutes).ToString("00")).ToString() + " hrs.";
}
return(a);
}
public string[] GetAttendanceTooltip()
{
HourProcessor h = TotalHours;
string[] a = new string [24];
string[] b =
{
#region +Keys
"nsu_proper_day_normal",
"nsu_overtime_day_normal",
"sun_proper_day_normal",
"sun_overtime_day_normal",
"nsu_proper_night_normal",
"nsu_overtime_night_normal",
"sun_proper_night_normal",
"sun_overtime_night_normal",
"nsu_proper_day_regular",
"nsu_overtime_day_regular",
"sun_proper_day_regular",
"sun_overtime_day_regular",
"nsu_proper_day_special",
"nsu_overtime_day_special",
"sun_proper_day_special",
"sun_overtime_day_special",
"nsu_proper_night_regular",
"nsu_overtime_night_regular",
"sun_proper_night_regular",
"sun_overtime_night_regular",
"nsu_proper_night_special",
"nsu_overtime_night_special",
"sun_proper_night_special",
"sun_overtime_night_special"
#endregion
};
for (int c = 0; c < b.Length; c++)
{
TimeSpan ts = h.hp[b[c]];
a[c] = (b[c][4] == 'p'?"Regular":"Overtime") + ": " + (((int)(ts.TotalHours)).ToString("00") + ":" + ((int)ts.Minutes).ToString("00")).ToString() + " hrs.";
}
return(a);
}
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}';
");
}