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