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}'; "); }