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