public static void UpdateCertifier(int ccid, string fn, string mn, string ln, string contactno)
{
var q =
$@"UPDATE `msadb`.`certifier` SET `fn`='{fn}', `mn`='{mn}', `ln`='{ln}', `contactno`='{contactno}' WHERE `ccid`='{ccid}';";
SQLTools.ExecuteNonQuery(q);
}
public static void AddCertifier(int cid, string fn, string mn, string ln, string contactno)
{
var q =
$@"INSERT INTO `msadb`.`certifier` (`cid`, `fn`, `ln`, `mn`,`contactno`) VALUES ('{cid}', '{fn}', '{ln}', '{mn}', '{contactno}');";
SQLTools.ExecuteNonQuery(q);
}
public static DataTable GetAllAssignmentDetails(int AID)
{
SQLTools.ExecuteNonQuery("call msadb.init_checkdate_assignmentstatus();");
/*
* On Status: What status? Assignment status?
* NTS: Is status supposed to be active when guard has duty? Or when guard has assignment?
*/
//return Gid, Name sa Guard, CID, name sa client, status
String q = @"/* return Gid, Name sa Guard, CID, name sa client, status */
select
guards.gid as gid,
client.cid as cid,
concat(ln,', ',fn,' ',mn) as guardname,
client.name as clientname,
case astatus when 1 then 'Active' when 2 then 'Inactive' end as assignmentstatus
from sduty_assignment
left join request_assign on sduty_assignment.RAID=request_assign.RAID
left join request on request.RID = request_assign.RID
left join client on request.cid = client.cid
left join guards on guards.gid = sduty_assignment.GID
where sduty_assignment.AID = " + AID;
return(SQLTools.ExecuteQuery(q));
}
public static DataTable GetDutyDetailList()
{
ExtraQueryParams = "GROUP BY concat(guards.ln, ', ', guards.fn, ' ', guards.mn) ORDER BY client.Name asc; ";
String q = "SELECT client.Name as 'Client Name' , concat(guards.ln, ', ', guards.fn, ' ', guards.mn) AS 'Guards Assigned', guards.LicenseNo as 'License Number', concat(request_assign.streetno, ' ', request_assign.streetname, ', ', request_assign.brgy, ', ', request_assign.city) as 'Assignment Address', concat(dutydetails.TI_hh, ':', dutydetails.TI_mm, ' ', dutydetails.TI_period) AS 'Shift Start', concat(dutydetails.TO_hh, ':', dutydetails.TO_mm, ' ', dutydetails.TO_period) AS 'Shift End', concat(CASE WHEN(dutydetails.Sun = 1) THEN 'S' END, CASE WHEN(dutydetails.Mon = 1) THEN 'M' END, CASE WHEN(dutydetails.Tue = 1) THEN 'T' END, CASE WHEN(dutydetails.Wed = 1) THEN 'W' END, CASE WHEN(dutydetails.Thu = 1) THEN 'Th' END, CASE WHEN(dutydetails.Fri = 1) THEN 'F' END, CASE WHEN(dutydetails.Sat = 1) THEN 'Sa' END) AS 'Shift Days', CAST(request_assign.ContractStart AS date) AS 'Contract Start', CAST(request_assign.ContractEnd AS DATE) AS 'Contract End' FROM sduty_assignment JOIN guards ON sduty_assignment.GID = guards.GID JOIN request_assign ON sduty_assignment.RAID = request_assign.RAID JOIN request ON request_assign.RID = request.RID JOIN client ON request.CID = client.CID LEFT JOIN dutydetails ON sduty_assignment.AID = dutydetails.AID " + ExtraQueryParams;
return(SQLTools.ExecuteQuery(q));
}
public static DataTable GetGuardsList()
{
ExtraQueryParams = "ORDER BY GStatus desc";
String q = "SELECT concat(ln,', ',fn,' ',mn) AS 'Full Name', CASE WHEN GStatus = 1 THEN 'Active' WHEN GStatus = 0 THEN 'Inactive' END as Status, CellNo as 'Cell Number', LicenseNo as 'License Number', SSS, TIN, PhilHealth as PHIC FROM msadb.guards " + ExtraQueryParams;
return(SQLTools.ExecuteQuery(q));
}
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 static void UpdateDutyDetailDates(int did, DateTime de, DateTime dd)
{
var q = $@"UPDATE `msadb`.`dutydetails` SET `date_effective`='{de:yyyy-MM-dd}', `date_dismissal`='{dd:yyyy-MM-dd}' WHERE `DID`='{did}';";
SQLTools.ExecuteNonQuery(q);
SQLTools.ExecuteNonQuery("call init_checkdate_dutydetail();");
}
/// <summary>
/// Gets all requests made on a specific date.
/// </summary>
/// <param name="date">DateTime object.</param>
/// <returns>DT columns: rid, name, dateentry, type</returns>
///
public static DataTable GetRequests(String searchkeyword, int ClientFilter, int TypeFilter, String SearchColumn, String orderby, DateTime date)
{
String q = @"select rid, name, dateentry,
case requesttype
when 1 then 'Assignment'
when 2 then 'Unassignment'
end as type,
case rstatus
when 1 then 'Pending'
when 2 then 'Approved'
when 3 then 'Active'
when 4 then 'Inactive'
when 5 then 'Declined'
end as status
from msadb.request
left join client on request.cid=client.cid
where dateentry='{0}' ";
if (ClientFilter != -1)
{
q += " and client.cid=" + ClientFilter;
}
if (TypeFilter != 0)
{
q += " and requesttype=" + TypeFilter;
}
searchkeyword = cleansearch(searchkeyword);
return(SQLTools.ExecuteQuery(q, SearchColumn, searchkeyword, "dateentry desc", new String[] { date.ToString("yyyy-MM-dd") }));
}
public static DataTable GetGuardsWithAssignment(string searchkeyword)
{
String q = @"select
guards.gid, d.did, sduty_assignment.aid,
concat(ln,', ',fn,' ',mn) as name,
concat(streetno, ', ', streetname, ', ', brgy, ', ', city) as Location,
case
when ti_hh is null then 'Unscheduled'
when ti_hh is not null then 'Scheduled'
end as schedule,
case astatus
when 1 then 'Active'
when 2 then 'Inactive'
when 3 then 'Approved'
end as Status,
case gender when 1 then 'Male' when 2 then 'Female' end as 'GENDER',
cellno as 'CONTACTNO',
case gstatus
when 0 then 'Inctive'
when 1 then 'Active'
when 2 then 'Pending Payroll'
when 1 then 'Pending Assignment'
end as 'STATUS'
from guards
left join sduty_assignment on sduty_assignment.gid=guards.gid
left join (select * from dutydetails where dstatus=1) as d on sduty_assignment.aid=d.aid
left join request_assign on request_assign.raid=sduty_assignment.raid
left join request on request_assign.rid=request.rid
where city is not null ";
DataTable dt = SQLTools.ExecuteQuery(q + searchkeyword + " group by guards.gid order by name asc");
return(dt);
}
public static void ApproveUnassignment(int RequestId)
{
DataTable de = SQLTools.ExecuteQuery($@"select * from request
left join request_unassign on request_unassign.RID = request.RID
where request.rid={RequestId};");
DateTime DateEffective = DateTime.Parse(de.Rows[0]["dateeffective"].ToString());
// 1.) Get all GIDs of guards in RID
DataTable GuardsToBeDismissed = SQLTools.ExecuteQuery(@"select guards.gid as gid, sduty_assignment.aid as aid from guards
left join sduty_assignment on sduty_assignment.GID = guards.gid
left join request_unassign on request_unassign.gid = guards.gid
where rid = " + RequestId + ";");
foreach (DataRow e in GuardsToBeDismissed.Rows)
{
// 1A) Set scheds to inactive
String q = @"UPDATE `msadb`.`dutydetails` SET `DStatus`='" + Enumeration.DutyDetailStatus.Inactive + "' WHERE `AID`='" + e["aid"] + "';";
SQLTools.ExecuteNonQuery(q);
// 2.) Set assignment to dismissed (IF they have schedules active)
q = $@"UPDATE `msadb`.`sduty_assignment` SET `AStatus`='{
Enumeration.AssignmentStatus.Inactive
}', UnassignedOn='{DateEffective:yyyy-MM-dd}' WHERE `gid`='{e["gid"]}';";
SQLTools.ExecuteNonQuery(q);
// 3.) Set guard to Inactive (BUT NOT DISMISSED)
q = $@"UPDATE `msadb`.`guards` SET `GStatus`='{Enumeration.GuardStatus.PendingPayroll}' WHERE `GID`='{e[0]}'";
SQLTools.ExecuteNonQuery(q);
SQLTools.ExecuteQuery($@"call init_checkdate_gstatus_onapprove({e[0]})");
}
// Step 4
UpdateRequestStatus(RequestId, Enumeration.RequestStatus.Approved, Login.LoggedInUser);
}
/*old
* public static DataTable ViewGuardsFromClient(int cid) {
* String q = @"select did, concat(ln,', ',fn,' ',mn) as Name, concat(streetno, ', ', streetname, ', ', brgy, ', ', city) as Location,concat(timein, '-', timeout,' ', days) as Schedule from guards left join sduty_assignment on guards.gid = sduty_assignment.gid
* left join dutydetails on sduty_assignment.aid = dutydetails.AID
* left join request_assign on sduty_assignment.raid = request_assign.raid
* left join request on request_assign.rid=request.rid
* where cid = 1;";
* DataTable dt = SQLTools.ExecuteQuery(q);
* foreach (DataRow e in dt.Rows) {
* String[] x = e["Schedule"].ToString().Split(' ');
* e.SetField("Schedule", (x[0] + ParseDays(x[1])));
* }
* return dt;
* }
*/
#endregion
#region View Assignments ✔Done
public static DataTable GetAssignmentsByClient(int cid, int filter, string searchkeyword)
{
String q = @"select
guards.gid, d.did, sduty_assignment.aid,
concat(ln,', ',fn,' ',mn) as name,
concat(streetno, ', ', streetname, ', ', brgy, ', ', city) as Location, client.name as cname,
case
when ti_hh is null then 'Unscheduled'
when ti_hh is not null then 'Scheduled'
end as schedule,
case astatus
when 1 then 'Active' when 2 then 'Inactive' when 3 then 'Approved' end as Status
from guards
left join sduty_assignment on sduty_assignment.gid=guards.gid
left join (select * from dutydetails where dstatus=1) as d on sduty_assignment.aid=d.aid
left join request_assign on request_assign.raid=sduty_assignment.raid
left join request on request_assign.rid=request.rid
left join client on request.cid = client.cid
where city is not null and sduty_assignment.unassignedon is null " +
(cid == -1 ? "" : " AND sduty_assignment.cid = " + cid + "");
if (filter == Enumeration.ScheduleStatus.Scheduled)
{
q += " AND ti_hh is not null";
}
else if (filter == Enumeration.ScheduleStatus.Unscheduled)
{
q += " AND ti_hh is null ";
}
q += searchkeyword;
q += " group by guards.gid";
DataTable dt = SQLTools.ExecuteQuery(q + " order by name asc");
return(dt);
}
public static DataTable GetAssignmentHistory(int gid)
{
return(SQLTools.ExecuteQuery($@"select aid, client.name, assignedon, unassignedon from sduty_assignment
left join request_assign on request_assign.raid = sduty_assignment.raid
left join request on request.rid = request_assign.rid
left join client on client.cid = request.cid where gid={gid}"));
}
/// <summary>
/// Adds an unassignment request for the specified guards.
/// </summary>
/// <param name="cid">Client ID</param>
/// <param name="gid">An array of Guard ID's to be dismissed</param>
/// <param name="ReportType">Enumeration.ReportType (Incident)</param>
/// <param name="pcompleting">Idk unsa ni na field.</param>
/// <param name="EventDate">Date of event.</param>
/// <param name="location">Brief location description</param>
/// <param name="description">Brief description of incident.</param>
public static void AddUnassignmentRequest(int cid, int[] gid, int ReportType, String pcompleting, DateTime EventDate,
String location, String description, DateTime DateEffective)
{
// 1.) Add Incident Report
String q = String.Format(@"INSERT INTO `msadb`.`incidentreport`
(`ReportType`, `DateEntry`, `PCompleting`, `EventDate`, `EventLocation`, `Description`)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}');",
ReportType, SQLTools.getDateTime(), pcompleting, EventDate.ToString("yyyy-MM-dd"), location, description
);
SQLTools.ExecuteNonQuery(q);
// 2.) Insert a dismissal request.\
// 2a: Get last inserted Incident Report (link this)
int IncidentId = int.Parse(SQLTools.getLastInsertedId("IncidentReport", "iid"));
// 2b: Insert request
q = "INSERT INTO `msadb`.`request` (`RequestType`, `CID`, `DateEntry`,`rstatus`) VALUES ('{0}', '{1}', '{2}','{3}')";
q = string.Format(q, Enumeration.RequestType.Dismissal, cid, SQLTools.getDateTime(), Enumeration.RequestStatus.Pending);
SQLTools.ExecuteNonQuery(q);
String lid = SQLTools.getLastInsertedId("request", "rid");
for (int c = 0; c < gid.Length; c++)
{
q = $@"INSERT INTO `msadb`.`request_unassign` (`RID`, `gid`, `iid`, `DateEffective`) VALUES ('{lid.ToString()}', '{ gid[c]}', '{IncidentId}', '{DateEffective:yyyy-MM-dd}');";
SQLTools.ExecuteNonQuery(q);
}
}
/// <summary>
/// TODO: Fill query.
/// Returns a DT of guards assigned on a specific request.
/// (Request must be approved + assigned)
/// </summary>
/// <param name="RequestId"></param>
/// <returns></returns>
public static DataTable GetRequestedGuards(int RequestId)
{
return(SQLTools.ExecuteQuery($@"Select concat(ln,', ',fn,' ',mn) from guards
right join SDuty_Assignment on SDuty_Assignment.gid = guards.gid
right join Request_Assign on Request_Assign.raid = SDuty_Assignment.raid
where SDuty_Assignment.raid = {RequestId}; "));
}
public static DataTable GetRequests(String searchkeyword, int ClientFilter, int TypeFilter, String ColumnToSortByAscDesc, String orderby)
{
String q = @"select rid, name, dateentry,
case requesttype when 1 then 'Assignment' when 2 then 'Unassignment' end as type,
case rstatus
when 1 then 'Pending'
when 2 then 'Approved'
when 3 then 'Active'
when 4 then 'Inactive'
when 5 then 'Declined'
end as status from msadb.request inner join client on request.cid=client.cid ";
q += " where 1=1 ";
if (ClientFilter != -1)
{
q += " and client.cid=" + ClientFilter;
}
if (TypeFilter != 0)
{
q += " and requesttype=" + TypeFilter;
}
searchkeyword = cleansearch(searchkeyword);
return(SQLTools.ExecuteQuery(q, ColumnToSortByAscDesc, searchkeyword, "dateentry desc"));
}
public static int CreateWithTaxBracket(double value, int excessmult)
{
var isnert_query = $@"INSERT INTO `msadb`.`withtax_value` (`value`, `excessmult`) VALUES ('{value}', '{excessmult}');";
SQLTools.ExecuteQuery(isnert_query);
return(SQLTools.GetInt("select last_insert_id()"));
}
public static DataTable GetPeriods(int GID)
{
return(SQLTools.ExecuteQuery($@"SELECT month, period, year
FROM msadbarchive.period
where GID='{GID}'
group by month,period,year order by year desc, month desc, period desc"));
}
public static DataTable GetAllGuards(string searchKeyWords, int mode)
{
string query;
string orderbyclause;
if (mode == 0)
{
query = $@"Select gid,concat(ln,', ',fn,' ',mn) as NAME,
case gstatus
when {Enumeration.GuardStatus.Active} then 'Active'
when {Enumeration.GuardStatus.Inactive} then 'Inactive'
when {Enumeration.GuardStatus.PendingPayroll} then 'Pending Payroll'
end as 'STATUS',
case gtype when 0 then 'Regular' when 1 then 'Officer' end as 'RANK', case gender when 1 then 'Male' when 2 then 'Female' end as 'GENDER',
cellno as 'CONTACTNO'
FROM Guards ";
orderbyclause = "ORDER BY NAME ASC;";
}
else
{
query = $@"Select Guards.gid,concat(ln,', ',fn,' ',mn) as NAME,
concat(StreetNo,', ', Brgy,', ',Street, ', ', City) As LOCATION, case gstatus
when { Enumeration.GuardStatus.Active}
then 'Active'
when { Enumeration.GuardStatus.Inactive}
then 'Inactive'
when { Enumeration.GuardStatus.PendingPayroll}
then 'Payroll Pending'
end as 'STATUS'
FROM Guards LEFT JOIN Address ON Address.GID = Guards.GID ";
orderbyclause = " AND Atype = 2 ORDER BY NAME ASC;";
}
return(SQLTools.ExecuteQuery(query + searchKeyWords + orderbyclause));
}
public static DataTable GetClientsList()
{
ExtraQueryParams = " ORDER BY Name asc";
String q = "SELECT Name as 'Name', CASE WHEN CStatus = 1 THEN 'Active' WHEN CStatus = 0 THEN 'Inactive' END as Status, concat(ClientStreetNo,' ', ClientStreet, ', ', ClientBrgy, ', ', ClientCity) as Address, Manager, ContactPerson as 'Contact Person', ContactNo as 'Contact Number' FROM msadb.client" + ExtraQueryParams;
return(SQLTools.ExecuteQuery(q));
}
public static int GetNumberOfDependents(int GID)
{
String q = @"SELECT count(DeID) FROM msadb.dependents where GID={0};";
q = string.Format(q, GID);
return(SQLTools.GetInt(q) - 2);
}
public static DataTable GetPeriods(int GID)
{
return(SQLTools.ExecuteQuery(@"SELECT month, period, year
FROM msadb.period
where GID = " + GID + @"
group by month,period,year order by year desc, month desc, period desc;"));
}
public void SetCertifiedBy(int AID, int cert)
{
Period p = GetCurrentPayPeriod();
String q = @"UPDATE `msadb`.`period` SET `certby`='" + cert + "' WHERE `GID`='" + GID + "' AND month='" + p.month + "' AND period='" + p.period + "' AND year='" + p.year + "';";
SQLTools.ExecuteNonQuery(q);
}
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 static DataTable GetAssignmentHistory(int GID)
{
return(SQLTools.ExecuteQuery($@"select aid, DATE_FORMAT(assignedon, '%Y-%m-%d') as AssignedOn, DATE_FORMAT(unassignedon, '%Y-%m-%d') as UnassignedOn,
name
from msadbarchive.sduty_assignment
left join msadb.client on msadb.client.cid = msadbarchive.sduty_assignment.cid
where gid = {GID};"));
}
public static void AddHoliday(SelectionRange r, string desc, int type, int trans)
{
var q = $@"
call msadb.proc_holiday_addholiday('{r.Start:yyyy-MM-dd}', '{r.End:yyyy-MM-dd}', '{desc}', {type}, {trans});
";
SQLTools.ExecuteNonQuery(q);
}
public string GetCertifiedBy()
{
return(SQLTools.ExecuteSingleResult($@"
select concat(ln,', ',fn,' ',mn) as certby from period
left join attendance on attendance.pid=period.pid
left join dutydetails on dutydetails.DID= attendance.DID
left join certifier on period.certby = certifier.ccid where month='{period.month}' and period = '{period.period}' and year='{period.year}' and aid='{AID}'"));
}
public static void AddClient(string name, string streetNo, string street, string brgy, string city, string contactPerson, string contactNo, string Manager, double OfficerRate)
{
String q = $@"INSERT INTO `msadb`.`Client`
(`Name`, `ClientStreetNo`, `ClientStreet`,`ClientBrgy`,`ClientCity`,`ContactPerson`,`ContactNo`,`Manager`,`CStatus` ,`ofcrate` )
VALUES ('{name}', '{streetNo}', '{street}', '{brgy}', '{city}', '{contactPerson}', '{contactNo}', '{Manager}', '{Enumeration.ClientStatus.Inactive}', '{OfficerRate}');";
SQLTools.ExecuteNonQuery(q);
}
/// <summary>
/// Returns Individual Time elements of DutyDetail.
/// </summary>
/// <param name="DID">Duty Detail ID</param>
/// <returns>Columns: ["ti_hh" , "ti_mm" , "ti_period" , "to_hh" , "to_mm" , "to_period"]</returns>
public static DataTable GetDutyDetailsDetails(int DID)
{
String q = @"select ti_hh, ti_mm, ti_period,
to_actual_hh as 'to_hh', to_actual_mm as 'to_mm', to_actual_period as 'to_period', date_effective, date_dismissal
from dutydetails where did=" + DID;
return(SQLTools.ExecuteQuery(q));
}
public static DataTable GetClients()
{
SQLTools.ExecuteQuery("call init_status_clientstatus()");
DataTable dt = new DataTable();
String query = "select cid, name from client;";
return(SQLTools.ExecuteQuery(query));
}
public static void InitGuardStatusAndDutyAssignments()
{
// If duty starts now, and not yet activated..
var w = $@"call init_checkdate_assignments()";
var z = $@"call init_checkdate_guardstatus()";
SQLTools.ExecuteQuery(z);
SQLTools.ExecuteQuery(w);
}
