/// <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);
}
}
public static void AddUnassignmentRequestNoIncident(int cid, int[] gid, DateTime DateEffective)
{
// 2b: Insert request
var 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`, `DateEffective`) VALUES ('{lid.ToString()}', '{ gid[c]}', '{DateEffective:yyyy-MM-dd}');";
SQLTools.ExecuteNonQuery(q);
}
}
private void GEditDetailsBTN_Click(object sender, EventArgs e)
{
if (DataVal())
{
try {
if (GEditDetailsBTN.Text.Equals("ADD"))
{
Client.AddClient(NameBX.Text.Replace("'", string.Empty), LocationStreetNoBX.Text.Replace("'", string.Empty), LocationStreetNameBX.Text.Replace("'", string.Empty),
LocationBrgyBX.Text.Replace("'", string.Empty), LocationCityBX.Text.Replace("'", string.Empty), ContactBX.Text.Replace("'", string.Empty), ContactNoBX.Text.Replace("'", string.Empty), ManagerBX.Text.Replace("'", string.Empty), double.Parse(BasicPayBX.Value.ToString("N2")));
var dep = int.Parse(SQLTools.getLastInsertedId("Client", "cid"));
foreach (DataGridViewRow row in CertifiersGRD.Rows)
{
InsertDependent(dep, row.Cells[4].Value.ToString().Replace("'", string.Empty), row.Cells[1].Value.ToString().Replace("'", string.Empty), row.Cells[2].Value.ToString().Replace("'", string.Empty), row.Cells[3].Value.ToString().Replace("'", string.Empty));
}
}
else
{
Client.UpdateClient(Cid.ToString(), NameBX.Text.Replace("'", string.Empty), LocationStreetNoBX.Text.Replace("'", string.Empty),
LocationStreetNameBX.Text.Replace("'", string.Empty),
LocationBrgyBX.Text.Replace("'", string.Empty), LocationCityBX.Text.Replace("'", string.Empty), ContactBX.Text.Replace("'", string.Empty), ContactNoBX.Text.Replace("'", string.Empty), ManagerBX.Text.Replace("'", string.Empty), double.Parse(BasicPayBX.Value.ToString("N2")));
foreach (DataGridViewRow row in CertifiersGRD.Rows)
{
if (row.Cells[0].Value.ToString().Equals("-1"))
{
InsertDependent(row.Cells[4].Value.ToString().Replace("'", string.Empty), row.Cells[1].Value.ToString().Replace("'", string.Empty), row.Cells[2].Value.ToString().Replace("'", string.Empty), row.Cells[3].Value.ToString().Replace("'", string.Empty));
}
else if (row.Cells[0].Value.ToString().Contains("Del"))
{
Client.RemoveCertifier(int.Parse(CertifiersGRD.SelectedRows[0].Cells[0].Value.ToString().Replace("Del", string.Empty)));
}
else
{
UpdateDependent(row.Cells[1].Value.ToString().Replace("'", string.Empty), row.Cells[2].Value.ToString().Replace("'", string.Empty), row.Cells[3].Value.ToString().Replace("'", string.Empty), row.Cells[1].Value.ToString().Replace("'", string.Empty), int.Parse(row.Cells[0].Value.ToString()));
}
}
ViewRef.RefreshData();
}
Reference.ClientsRefreshClientsList();
Close();
}
catch (Exception ex) {
ShowErrorBox("Clients Editing", ex.Message);
}
}
}
/// <summary>
/// Adds an assignment request. Note: This isn't an actual assignment yet.
/// </summary>
/// <param name="CID">Client ID (the requester)</param>
/// <param name="AssStreetNo"></param>
/// <param name="AssStreetName"></param>
/// <param name="AssBrgy"></param>
/// <param name="AssCity"></param>
/// <param name="ContractStart"></param>
/// <param name="ContractEnd">DateTime ang ipass diri</param>
/// <param name="NoGuards">Number of Guards</param>
public static void AddAssignmentRequest(int CID, string AssStreetNo, string AssStreetName, string AssBrgy, string AssCity, DateTime ContractStart, DateTime ContractEnd, int NoGuards)
{
String madeon = DateTime.Now.ToString("yyyy-MM-dd");
String q1 = String.Format("INSERT INTO `msadb`.`request` (`RequestType`, `CID`, `DateEntry`,`rstatus`) VALUES ('{0}', '{1}', '{2}','{3}');",
Enumeration.RequestType.Assignment, CID, madeon, Enumeration.RequestStatus.Pending);
SQLTools.ExecuteNonQuery(q1);
String rid = SQLTools.getLastInsertedId("request", "rid");
String query = String.Format("INSERT INTO `msadb`.`request_assign` " +
" ( `ContractStart`, `ContractEnd`, `streetno`, `streetname`, `brgy`, `city`,`noguards`,`rid`)" +
" VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}');",
ContractStart.ToString("yyyy-MM-dd"), ContractEnd.ToString("yyyy-MM-dd"),
AssStreetNo, AssStreetName, AssBrgy, AssCity, NoGuards, rid);
Console.WriteLine("AddAssignmentRequest: \n" + query);
SQLTools.ExecuteNonQuery(query);
}
private void DismissBTN_Click(object sender, EventArgs e)
{
if (DataValidation())
{
var giDs = new int[GuardsGRD.RowCount];
for (var i = 0; i < GuardsGRD.RowCount; i++)
{
giDs[i] = int.Parse(GuardsGRD.Rows[i].Cells[0].Value.ToString());
}
if (EnableIncidentCHKBX.Checked)
{
Scheduling.AddUnassignmentRequest(Cid, giDs, IncidentTypeCMBX.SelectedIndex, Login.UserName,
DateDTPKR.Value,
LocationBX.Text.Replace("'", string.Empty), DescriptionBX.Text.Replace("'", string.Empty),
DateEffective.Value);
try {
_iid = int.Parse(SQLTools.getLastInsertedId("IncidentReport", "IID"));
}
catch { }
try {
foreach (DataGridViewRow row in DepsGRD.Rows)
{
InsertDependent(GetRelationshipIndex(row.Cells[4].Value.ToString()),
row.Cells[1].Value.ToString().Replace("'", string.Empty),
row.Cells[2].Value.ToString().Replace("'", string.Empty),
row.Cells[3].Value.ToString().Replace("'", string.Empty));
}
}
catch { }
}
else
{
Scheduling.AddUnassignmentRequestNoIncident(Cid, giDs, DateEffective.Value);
}
RylMessageBox.ShowDialog("Your Request has been added", "Request Added", MessageBoxButtons.OK,
MessageBoxIcon.Information);
Reference.SchedLoadSidePnl();
Close();
}
}
public Attendance(int AID, int month, int periodx, int year)
{
SQLTools.ExecuteNonQuery($@"call msadb.init_checkdate_all();");
this.AID = AID;
period = new Period(periodx, month, year);
Console.Write(period.period);
DataRow drSdutyAssign = SQLTools.ExecuteQuery($"select * from sduty_assignment where aid='{this.AID}'").Rows[0];
GID = int.Parse(drSdutyAssign["gid"].ToString());
CID = int.Parse(drSdutyAssign["cid"].ToString());
// Only add attendance on months between contract start and contract end (inclusive)'.
// So i need to retrieve ContractStart and ContractEnd of the request.
int raid = int.Parse(drSdutyAssign["raid"].ToString());
DataRow drra = SQLTools.ExecuteQuery($@"SELECT * FROM msadb.request_assign where raid='{raid}'").Rows[0];
// Insert new period
String ax = $@"INSERT IGNORE INTO `msadb`.`period`
(`GID`, `month`, `period`, `year`, `cid`) VALUES
('{GID}', '{month}', '{periodx}', '{year}', '{CID}')";
SQLTools.ExecuteNonQuery(ax);
string ifn = SQLTools.getLastInsertedId("period", "pid");
DataTable x = SQLTools.ExecuteQuery("select did, mon,tue,wed,thu,fri,sat,sun,date_effective, date_dismissal from dutydetails where AID =" + AID);
long tcs = DateTime.Parse(drra["contractstart"].ToString()).Ticks;
long tce = DateTime.Parse(drra["contractend"].ToString()).Ticks;
foreach (DataRow duties in x.Rows)
{
long lcs = DateTime.Parse(duties["date_effective"].ToString()).Ticks;
long lce = DateTime.Parse(duties["date_dismissal"].ToString()).Ticks;
// COmpare kung later pa sa assigment span ang duty detail mag-effect.
DateTime cs = new DateTime(Math.Max(tcs, lcs));
DateTime ce = new DateTime(Math.Min(tce, lce));
List <int> dutydates = new List <int>();
int did = int.Parse(duties["did"].ToString());
if (duties["mon"].ToString() == "1")
{
dutydates.AddRange(period.Mon);
}
if (duties["tue"].ToString() == "1")
{
dutydates.AddRange(period.Tue);
}
if (duties["wed"].ToString() == "1")
{
dutydates.AddRange(period.Wed);
}
if (duties["thu"].ToString() == "1")
{
dutydates.AddRange(period.Thu);
}
if (duties["fri"].ToString() == "1")
{
dutydates.AddRange(period.Fri);
}
if (duties["sat"].ToString() == "1")
{
dutydates.AddRange(period.Sat);
}
if (duties["sun"].ToString() == "1")
{
dutydates.AddRange(period.Sun);
}
Stopwatch sw = new Stopwatch();
sw.Start();
DateTime zero = new DateTime(1, 1, 1, 0, 0, 0);
sw.Stop();
TimeSpan ts = sw.Elapsed;
SQLTools.message("Finished inserting in " + ts.ToString("ss\\.ff") + " seconds");
SQLTools.message("Yes!");
int count = 0;
int max = dutydates.Count;
int insertables = 0;
String q = @"INSERT IGNORE INTO `msadb`.`attendance` (`DID`, `date`, `PID`) VALUES ";
foreach (int date in dutydates)
{
DateTime d = new DateTime(period.year, period.month, date);
count++;
if (cs <= d && d <= ce)
{
q += @"('" + did + "','" + d.ToString("yyyy-MM-dd HH:mm:ss") + "','" + ifn + "')";
q += ",\n";
insertables++;
}
}
// Do not execute INSERT if there is duty dates is empty.
// Otherwise this will lead to a SQL syntax error.
// Bc bulk insert.
if (insertables != 0)
{
SQLTools.ExecuteNonQuery(q.Substring(0, q.Length - 2));
}
}
}
private void GEditDetailsBTN_Click(object sender, EventArgs e)
{
if (CheckInput())
{
#region Adding
if (GEditDetailsBTN.Text.Equals("ADD"))
{
try {
Guard.AddGuardBasicInfo(FirstNameBX.Text.Replace("'", string.Empty), MiddleNameBX.Text.Replace("'", string.Empty), LastNameBX.Text.Replace("'", string.Empty), BirthdateBX.Value,
_gender, HeightBX.Text.Replace(".", string.Empty), WeightBX.Text.Replace(".", string.Empty), ReligionBX.Text.Replace("'", string.Empty), CVStatusBX.SelectedIndex,
CellNoBX.Text.Replace(".", string.Empty), TellNoBX.Text.Replace(".", string.Empty), LicenseNoBX.Text, SSSNoBX.Text, TINNoBX.Text,
PhilHealthBX.Text, PrevAgencyBX.Text.Replace("'", string.Empty), PrevAssBX.Text.Replace("'", string.Empty), EdAttBX.SelectedIndex, CourseBX.Text,
MilTrainBX.Text.Replace("'", string.Empty), EmergBX.Text.Replace("'", string.Empty), EmergencyNoBX.Text.Replace("'", string.Empty), _guardType);
}
catch (Exception ex) {
ShowErrorBox("Saving Guards", ex.Message);
}
try {
Gid = int.Parse(SQLTools.getLastInsertedId("Guards", "GID"));
}
catch (Exception ex) {
ShowErrorBox("Saving Guards", ex.Message);
}
try {
InsertAdd(1, BirthplaceStreetNoBX.Text, BirthplaceStreetNameBX.Text, BirthplaceCityBX.Text,
BirthplaceBrgyBX.Text);
InsertAdd(2, PermStreetNoBX.Text, PermStreetNameBX.Text, PermCityBX.Text, PermBrgyBX.Text);
InsertAdd(3, TempStreetNoBX.Text, TempStreetNameBX.Text, TempCityBX.Text, TempBrgyBX.Text);
}
catch (Exception ex) {
ShowErrorBox("Saving Guards", ex.Message);
}
try {
InsertDependent(4, FatherFirstBX.Text.Replace("'", string.Empty), FatherMiddleBX.Text.Replace("'", string.Empty), FatherLastBX.Text.Replace("'", string.Empty));
InsertDependent(5, MotherFirstBX.Text.Replace("'", string.Empty), MotherMiddleBX.Text.Replace("'", string.Empty), MotherLastBX.Text.Replace("'", string.Empty));
if (!CheckName(SpouseFirstBX, SpouseMiddleBX, SpouseLastBX) && CVStatusBX.SelectedIndex != 1)
{
InsertDependent(6, SpouseFirstBX.Text.Replace("'", string.Empty), SpouseMiddleBX.Text.Replace("'", string.Empty), SpouseLastBX.Text.Replace("'", string.Empty));
}
foreach (DataGridViewRow row in DepGRD.Rows)
{
InsertDependent(GetRelationshipIndex(row.Cells[4].Value.ToString()), row.Cells[1].Value.ToString().Replace("'", string.Empty),
row.Cells[2].Value.ToString().Replace("'", string.Empty), row.Cells[3].Value.ToString().Replace("'", string.Empty));
}
}
catch (Exception ex) {
ShowErrorBox("Saving Guards", ex.Message);
}
}
#endregion
#region Updating
else if (GEditDetailsBTN.Text.Equals("UPDATE"))
{
try {
Guard.UpdateGuardBasicInfo(Gid, FirstNameBX.Text.Replace("'", string.Empty), MiddleNameBX.Text.Replace("'", string.Empty), LastNameBX.Text.Replace("'", string.Empty),
BirthdateBX.Value, _gender, HeightBX.Text.Replace(".", string.Empty), WeightBX.Text.Replace(".", string.Empty), ReligionBX.Text.Replace("'", string.Empty),
CVStatusBX.SelectedIndex, CellNoBX.Text.Replace(".", string.Empty), TellNoBX.Text.Replace(".", string.Empty), LicenseNoBX.Text, SSSNoBX.Text,
TINNoBX.Text, PhilHealthBX.Text, PrevAgencyBX.Text.Replace("'", string.Empty), PrevAssBX.Text.Replace("'", string.Empty), EdAttBX.SelectedIndex,
CourseBX.Text.Replace("'", string.Empty), MilTrainBX.Text.Replace("'", string.Empty), EmergBX.Text.Replace("'", string.Empty), EmergencyNoBX.Text.Replace("'", string.Empty));
Guard.UpdateGuardType(Gid, _guardType);
}
catch (Exception ex) {
ShowErrorBox("Saving Guards", ex.Message);
}
try {
UpdateAdd(BirthplaceStreetNoBX.Text.Replace("'", string.Empty), BirthplaceStreetNameBX.Text.Replace("'", string.Empty), BirthplaceCityBX.Text.Replace("'", string.Empty),
BirthplaceBrgyBX.Text, 1);
UpdateAdd(PermStreetNoBX.Text.Replace("'", string.Empty), PermStreetNameBX.Text.Replace("'", string.Empty), PermCityBX.Text.Replace("'", string.Empty), PermBrgyBX.Text.Replace("'", string.Empty), 2);
UpdateAdd(TempStreetNoBX.Text.Replace("'", string.Empty), TempStreetNameBX.Text.Replace("'", string.Empty), TempCityBX.Text.Replace("'", string.Empty), TempBrgyBX.Text.Replace("'", string.Empty), 3);
}
catch (Exception ex) {
ShowErrorBox("Saving Guards", ex.Message);
}
try {
UpdateDependent(FatherFirstBX.Text.Replace("'", string.Empty), FatherMiddleBX.Text.Replace("'", string.Empty), FatherLastBX.Text.Replace("'", string.Empty), 4);
UpdateDependent(MotherFirstBX.Text.Replace("'", string.Empty), MotherMiddleBX.Text.Replace("'", string.Empty), MotherLastBX.Text.Replace("'", string.Empty), 5);
if (!CheckName(SpouseFirstBX, SpouseMiddleBX, SpouseLastBX))
{
UpdateDependent(SpouseFirstBX.Text.Replace("'", string.Empty), SpouseMiddleBX.Text.Replace("'", string.Empty), SpouseLastBX.Text.Replace("'", string.Empty), 6);
}
foreach (DataGridViewRow row in DepGRD.Rows)
{
if (row.Cells[0].Value.ToString().Equals("-1"))
{
InsertDependent(GetRelationshipIndex(row.Cells[4].Value.ToString()), row.Cells[1].Value.ToString().Replace("'", string.Empty),
row.Cells[2].Value.ToString().Replace("'", string.Empty), row.Cells[3].Value.ToString().Replace("'", string.Empty));
}
else if (row.Cells[0].Value.ToString().Contains("Del"))
{
Guard.RemoveDependent(int.Parse(row.Cells[0].Value.ToString().Replace("Del", string.Empty)));
}
else
{
UpdateDependent(row.Cells[1].Value.ToString().Replace("'", string.Empty), row.Cells[2].Value.ToString().Replace("'", string.Empty), row.Cells[3].Value.ToString().Replace("'", string.Empty),
GetRelationshipIndex(row.Cells[4].Value.ToString()), int.Parse(row.Cells[0].Value.ToString()));
}
}
ViewRef.RefreshData();
}
catch (Exception ex) {
ShowErrorBox("Saving Guards", ex.Message);
}
}
#endregion
Reference.GuardsRefreshGuardsList();
Close();
}
}
