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)); } } }