public Hours(DateTime TimeIn, DateTime TimeOut, DateTime f)
{
DateTime NightStart = new DateTime(f.Year, f.Month, f.Day, 22, 00, 00);
DateTime NightEnd = new DateTime(f.Year, f.Month, f.Day, 6, 00, 00);
DateTime Midnight = new DateTime(f.Year, f.Month, f.Day, 0, 0, 0).AddDays(1); DateTime maxStart; DateTime minEnd; DateTime minStart; DateTime maxEnd;
// if not same
if (TimeIn > TimeOut)
{
TimeOut = TimeOut.AddDays(1);
NightEnd = NightEnd.AddDays(1);
// First Half
// 1: Max Selection, either nighstart or actuals
maxStart = TimeIn < NightStart ? NightStart : TimeIn;
minStart = TimeIn < NightStart ? TimeIn : NightStart;
TimeSpan d1_night = Midnight - maxStart;
TimeSpan d1_day = (NightStart - minStart > TimeSpan.FromSeconds(0)) ? NightStart - minStart : new TimeSpan(0, 0, 0);
if (TimeIn.DayOfWeek == DayOfWeek.Sunday)
{
SundayTotal += d1_day + d1_night;
}
// Second Half
minEnd = TimeOut < NightEnd ? TimeOut : NightEnd;
maxEnd = TimeOut > NightEnd ? TimeOut : NightEnd;
TimeSpan d2_night = minEnd - Midnight;
TimeSpan d2_day = (maxEnd - NightEnd > TimeSpan.FromSeconds(0)) ? maxEnd - NightEnd : new TimeSpan(0, 0, 0);
if (TimeOut.DayOfWeek == DayOfWeek.Sunday)
{
SundayTotal += d2_day + d2_night;
}
Attendance.HE_internal o = Attendance.IsHolidayToday_(TimeIn);
// Check if today is holiday.
if (Attendance.IsHolidayToday(TimeIn))
{
holiday_night += d1_night;
holiday_day += d1_day;
if (o.type == Enumeration.HolidayType.Regular)
{
if (TimeIn.DayOfWeek == DayOfWeek.Sunday)
{
holiday_regular_day_sunday += d1_day;
holiday_sunday_regular_night += d1_night;
}
else
{
}
}
else
{
Sunday_special_day += d1_day;
Sunday_special_night += d1_night;
}
}
else
{
normal_night += d1_night;
normal_day += d1_day;
if (TimeIn.DayOfWeek == DayOfWeek.Sunday)
{
Sunday_normal_day += d1_day;
Sunday_normal_night += d1_night;
}
}
//Check if tomorrow is holiday.
if (Attendance.IsHolidayTomorrow(TimeIn))
{
holiday_night += d2_night;
holiday_day += d2_day;
if (TimeOut.DayOfWeek == DayOfWeek.Sunday)
{
// Sunday_holiday_day += d2_day;
// Sunday_holiday_night += d2_night;
}
}
else
{
normal_night += d2_night;
normal_day += d2_day;
if (TimeOut.DayOfWeek == DayOfWeek.Sunday)
{
Sunday_normal_day += d2_day;
Sunday_normal_night += d2_night;
}
}
}
else
{
// if same day
NightEnd = new DateTime(f.Year, f.Month, f.Day, 6, 00, 00);
maxStart = TimeIn < NightStart ? TimeIn : NightStart;
minStart = TimeIn > NightStart ? TimeIn : NightStart;
minEnd = TimeOut < NightEnd ? TimeOut : NightEnd;
maxEnd = TimeOut > NightEnd ? TimeOut : NightEnd;
if (Attendance.IsHolidayToday(TimeIn))
{
holiday_night += (minEnd - maxStart) > TimeSpan.FromSeconds(0) ? minEnd - maxStart : new TimeSpan(0, 0, 0);
holiday_day += (TimeOut - NightEnd) > TimeSpan.FromSeconds(0) ? TimeOut - NightEnd : new TimeSpan(0, 0, 0);
holiday_day += (NightStart - minStart) > TimeSpan.FromSeconds(0) ? NightStart - minStart : new TimeSpan(0, 0, 0);
if (TimeIn.DayOfWeek == DayOfWeek.Sunday)
{
SundayTotal += holiday_night + holiday_day;
// Sunday_holiday_day += holiday_day;
// Sunday_holiday_night += holiday_night;
}
}
else
{
normal_night += (minEnd - maxStart) > TimeSpan.FromSeconds(0) ? minEnd - maxStart : new TimeSpan(0, 0, 0);
normal_day += (TimeOut - NightEnd) > TimeSpan.FromSeconds(0) ? TimeOut - NightEnd : new TimeSpan(0, 0, 0);
normal_day += (NightStart - minStart) > TimeSpan.FromSeconds(0) ? NightStart - minStart : new TimeSpan(0, 0, 0);
if (TimeIn.DayOfWeek == DayOfWeek.Sunday)
{
SundayTotal += normal_night + normal_day;
Sunday_normal_day += normal_day;
Sunday_normal_night += normal_night;
}
}
}
total = normal_day + normal_night + holiday_day + holiday_night;
}
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}';
");
}
