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 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));
}
/// <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 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()"));
}
/*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 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 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 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 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);
}
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 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>
/// 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 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 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 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 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 GetClients()
{
SQLTools.ExecuteQuery("call init_status_clientstatus()");
DataTable dt = new DataTable();
String query = "select cid, name from client;";
return(SQLTools.ExecuteQuery(query));
}
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};"));
}
/// <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 GetAssignmentDetails(int aid)
{
// Location, ContractStart, ContractEnd
String q = @"SELECT address as location,
AssignedOn as contractstart, UnassignedOn as contractend FROM msadbarchive.sduty_assignment
where msadbarchive.sduty_assignment.aid=" + aid + ";";
return(SQLTools.ExecuteQuery(q));
}
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);
}
public static DataTable GetAllClientDetails(String searchKeyWords)
{
searchKeyWords = CleanSearch(searchKeyWords);
var q = $@"SELECT cid, name, CONCAT(Clientstreetno,' ',Clientstreet,', ', Clientbrgy,', ',Clientcity) AS contactno,
case cstatus when {Enumeration.ClientStatus.Active} then 'Active' when {Enumeration.ClientStatus.Inactive} then 'Inactive'
end as status FROM client" + searchKeyWords;
return(SQLTools.ExecuteQuery(q));
}
public static DataTable GetGuardsToBeUnassigned(int RID)
{
String q = @"select guards.gid, concat(ln,', ',fn,' ',mn) as name from request_unassign
left join guards on guards.gid = request_unassign.gid
left join request on request.RID = request_unassign.RID
where request.RID = " + RID;
return(SQLTools.ExecuteQuery(q));
}
/// <summary>
/// Gets the details of a specific ASSIGNMENT Request
/// </summary>
/// <param name="rid">Request ID.</param>
/// <returns></returns>
public static DataTable GetAssignmentRequestDetails(int rid)
{
String q = @"SELECT name, concat(streetno,', ',streetname,', ',brgy,', ',city) as Location,
contractstart, contractend, noguards, request.rstatus, uname
FROM request left join request_assign on request_assign.rid = request.rid left join client on request.cid = client.cid
left join account on account.accid = request.processedby "
+ " where request.rid={0}";;
return(SQLTools.ExecuteQuery(q, null, null, null, new String[] { rid.ToString() }));
}
public static DataTable GetIncidentInvolved(int rid)
{
var q = @"select concat (ln, ', ', fn, ' ', mn) as name,
case InvolvementType when 1 then 'Involved' when 2 then 'Witness' end as InvType from request_unassign
left join request on request_unassign.RID = request.RID
left join incidentreport on request_unassign.IID = incidentreport.IID
left join personsinvolved on incidentreport.IID = personsinvolved.IID where request.RID = " + rid + " group by name";
return(SQLTools.ExecuteQuery(q));
}
public static Payroll GetPayroll(int GID, int month, int period, int year)
{
Payroll py = new Payroll(SQLTools.ExecuteQuery($@"select * from msadbarchive.payroll where
gid = {GID}
and period = {period}
and year = {year}
and month = {month} ").Rows[0]);
return(py);
}
public static DataTable GetAttendanceSummary(int year, int month, int period, int gid)
{
return(SQLTools.ExecuteQuery(
$@"SELECT pid, concat(ln,', ',fn,' ',mn) as 'certby', holiday_day, holiday_night, normal_day, normal_night, total FROM msadbarchive.period
left join msadb.certifier on msadb.certifier.ccid = msadbarchive.period.certby
where month = {month}
and period={period}
and year = {year}
and gid={gid};"));
}
/// <summary>
/// Returns DataTable: Location, ContractStart, ContractEnd
/// of guard with corresponding AID number.
/// </summary>
/// <param name="aid"></param>
/// <returns></returns>
public static DataTable GetAssignmentDetails(int aid)
{
// Location, ContractStart, ContractEnd
String q = @"SELECT concat(streetno, ', ', streetname, ', ', brgy, ', ', city) as Location, ContractStart, ContractEnd FROM msadb.sduty_assignment
left join request_assign on request_assign.raid=sduty_assignment.raid
left join request on request.rid=request_assign.rid
where sduty_assignment.aid=" + aid + ";";
return(SQLTools.ExecuteQuery(q));
}
public static DataTable GetIncidentReport(int rid)
{
var q = @"select case incidentreport.ReportType when 1 then 'Injury' when 2 then 'Accident' when 3 then 'Complaint' end as Type,
incidentreport.EventDate as EventDate,
incidentreport.EventLocation as Location,
incidentreport.Description as Description from request_unassign
left join request on request_unassign.RID = request.RID
left join incidentreport on request_unassign.IID = incidentreport.IID
left join personsinvolved on incidentreport.IID = personsinvolved.IID where request.RID = " + rid + " group by Description";
return(SQLTools.ExecuteQuery(q));
}
public static void CreateWithTaxQuery(DateTime date_effective)
{
SingleTransactionQuery =
"insert into `msadb`.`withtax_bracket` (`estatus`, `bracket`, `taxid`, `contrib_id`) VALUES ";
var insert_contribdetail =
$@"INSERT INTO `msadb`.`contribdetails`
(`date_effective`, `date_dissolved`, `type`, `status`)
VALUES ('{date_effective.ToString("yyyy-MM-dd")}', '{"9999-12-31"}', '{2}', '{2}');";
SQLTools.ExecuteQuery(insert_contribdetail);
contrib_id = SQLTools.GetInt("select last_insert_id()");
}
