Пример #1
0
        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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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}';
                            ");
        }