/// <summary>
/// verify work order belongs to job
/// </summary>
/// <param name="jobID"></param>
/// <param name="woNo"></param>
/// <returns>boolean indicating work order belongs to job</returns>
private static bool valid_work_order(int jobID, string woNo, string connectionString)
{
bool isValid = false;
string projFinalID = $"{jobID}-{woNo}";
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
LogIt.LogInfo($"Validating work order \"{woNo}\" for job {jobID}");
string cmdText = "SELECT COUNT(ProjectFinalID) FROM MLG.POL.tblProjectFinal where ProjectFinalID = @projFinalID";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
cmd.Parameters.AddWithValue("@projFinalID", projFinalID);
conn.Open();
int rows = (int)cmd.ExecuteScalar();
isValid = (rows > 0);
}
}
}
catch (Exception ex)
{
LogIt.LogError($"Error validating work order \"{woNo}\" for job {jobID}: {ex.Message}");
}
return(isValid);
}
private DataSet GetAllSchools()
{
DataSet ds = new DataSet();
try
{
LogIt.LogInfo("Getting schools.");
using (ODBCClass o = new ODBCClass("MySql"))
{
var procName = "select SchoolID from SchoolInfo";
using (OdbcCommand oCommand = o.GetCommand(procName))
{
using (OdbcDataAdapter oAdapter = new OdbcDataAdapter(oCommand))
{
oAdapter.Fill(ds);
}
}
}
}
catch (Exception ex)
{
var msg = $"Error getting schools: {ex.Message}";
LogIt.LogError(msg);
}
return(ds);
}
/// <summary>
/// verify valid job
/// </summary>
/// <param name="jobID"></param>
/// <param name="connectionString"></param>
/// <returns>boolean indicating job exists in database</returns>
private static bool valid_job(int jobID, string connectionString)
{
bool isValid = false;
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
LogIt.LogInfo($"Validating job ID {jobID}");
string cmdText = "SELECT COUNT(JobID) FROM MLG.POL.tblJobs WHERE JobID = @jobID";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
cmd.Parameters.AddWithValue("@jobID", jobID);
conn.Open();
int rows = (int)cmd.ExecuteScalar();
isValid = (rows > 0);
}
}
}
catch (Exception ex)
{
LogIt.LogError($"Error validating job ID {jobID}: {ex.Message}");
}
return(isValid);
}
/// <summary>
/// adds a timesheet entry for supplied timesheet ID
/// </summary>
/// <param name="timesheetId">8-digit hashed ID of timesheet to add hours to</param>
/// <param name="jobId">AIMM job number</param>
/// <param name="workOrder">AIMM work order number</param>
/// <param name="startTime">Date-time work reported for</param>
/// <param name="regularHours">Number of regular hours reported</param>
/// <param name="overtimeHours">Number of overtime hours reported</param>
/// <param name="doubleOvertimeHours">Number of double overtime hours reported</param>
/// <param name="connectionString"></param>
/// <returns></returns>
private bool add_timesheet_detail(string timesheetId, int jobId, string workOrder, DateTime startTime, float regularHours, float overtimeHours, float doubleOvertimeHours, string connectionString)
{
bool result = false;
string estID = $"{jobId}-{workOrder}";
float hours = regularHours + overtimeHours + doubleOvertimeHours;
KeyValuePair <DateTime, DateTime> wkStartAndEnd = get_week_start_and_end(startTime, DayOfWeek.Wednesday);
string workDate = startTime.Date.ToString("yyyy-MM-dd 00:00:00.000");
string weDate = wkStartAndEnd.Value.ToString("yyyy-MM-dd 00:00:00.000");
;
string sql = "INSERT INTO MLG.POL.tblWeeklyTimeSheetEntries (WeeklyTimeSheetID, JobID, HoursWorked, EstimateID, EstimateTypeID, TheDate, "
+ "Notes, PerformanceLevel, AttendCode, NPReasonCode, Correction, JobErrorID, WeekEndingDate) VALUES "
+ $"( '{timesheetId}', {jobId}, {hours}, '{estID}', null, '{workDate}', null, null, null, null, 0, null, '{weDate}')";
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
var recs = cmd.ExecuteNonQuery();
result = (recs == 1);
}
}
}
catch (Exception ex)
{
string msg = $"Error adding timesheet detail for timesheet \"{timesheetId}\" for {startTime.ToShortDateString()}: {ex.Message}";
Status = msg;
LogIt.LogError(msg);
}
return(result);
}
/// <summary>
/// updates timesheet totals for supplied timesheet IDs
/// </summary>
/// <param name="timesheetsUpdated">List of timesheet IDs to be updated</param>
/// <param name="connectionString"></param>
/// <returns></returns>
private bool update_timesheet_totals(List <string> timesheetsUpdated, string connectionString)
{
bool result = false;
string where = string.Join("','", timesheetsUpdated);
string sql = "UPDATE ts SET ts.TotalHours = tse.HoursWorked, ts.RegHours = case when tse.HoursWorked <= 40 then tse.HoursWorked else 40 end, "
+ "ts.OTHours = case when tse.HoursWorked <= 40 then 0 else tse.HoursWorked - 40 end "
+ "FROM MLG.POL.tblWeeklyTimeSheets ts INNER JOIN "
+ "(SELECT WeeklyTimeSheetID, sum(HoursWorked) as HoursWorked FROM MLG.POL.tblWeeklyTimeSheetEntries GROUP BY WeeklyTimeSheetID) as tse "
+ $"ON tse.WeeklyTimeSheetID = ts.WeeklyTimeSheetID WHERE ts.WeeklyTimeSheetID in('{where}')";
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
var recs = cmd.ExecuteNonQuery();
result = (recs >= 1);
}
}
}
catch (Exception ex)
{
string msg = $"Error updating timesheet totals: {ex.Message}";
Status = msg;
LogIt.LogError(msg);
}
return(result);
}
private bool GetSettings()
{
try
{
doc = new ConfigXmlDocument();
doc.Load(settingsFile);
sendTo = GetSetting(doc, "SendTo");
sendParentTo = GetSetting(doc, "SendParentTo");
var tempCCs = GetSetting(doc, "SendCC");
if (tempCCs != "")
{
CCsTo = tempCCs.Split(',').ToList <string>();
}
var tempBCCs = GetSetting(doc, "SendBCC");
if (tempBCCs != "")
{
BCCsTo = tempBCCs.Split(',').ToList <string>();
}
var tempERRs = GetSetting(doc, "SendErrors");
if (tempERRs != "")
{
ERRsTo = tempERRs.Split(',').ToList <string>();
}
var tempFromDaysAgo = GetSetting(doc, "FromDaysAgo");
if (!int.TryParse(tempFromDaysAgo, out fromDaysAgo))
{
fromDaysAgo = 12;
}
var tempToDaysAgo = GetSetting(doc, "ToDaysAgo");
if (!int.TryParse(tempToDaysAgo, out toDaysAgo))
{
toDaysAgo = 5;
}
from_email = GetSetting(doc, "FromEmail");
from_name = GetSetting(doc, "FromName");
return(true);
}
catch (Exception ex)
{
Status = $"Error getting application settings: {ex.Message}";
LogIt.LogError(Status);
return(false);
}
}
/// <summary>
/// create a weekly timesheet for employee
/// </summary>
/// <param name="workDate">Date work reported for</param>
/// <param name="employeeID">ID of employee reporting work</param>
/// <param name="connectionString"></param>
/// <returns>(yymmXXXX)</returns>
private string create_timesheet(DateTime workDate, int employeeID, string connectionString)
{
string result = "";
// get start and end dates given the date from the excel file.
KeyValuePair <DateTime, DateTime> wkStartAndEnd = get_week_start_and_end(workDate, DayOfWeek.Wednesday);
var stDate = wkStartAndEnd.Key.ToString("yyyy-MM-dd 00:00:00.000");
var endDate = wkStartAndEnd.Value.ToString("yyyy-MM-dd 00:00:00.000");
// get list of already existing timesheet ids for the month
List <string> timesheetsUsedForMonth = get_timesheets_for_month(workDate, connectionString);
// build a timesheet id which hasn't been used for the month
string timesheetID = get_unique_timesheet_id(workDate, timesheetsUsedForMonth);
// get the crew
KeyValuePair <int, string> crewInfo = get_employee_crew(employeeID, connectionString);
// add timesheet record
string sql = "INSERT INTO MLG.POL.tblWeeklyTimeSheets (WeeklyTimeSheetID, EmployeeID, StartDate, EndDate, TotalHours, RegHours, OTHours, CrewID, CrewName) "
+ $"VALUES ('{timesheetID}', {employeeID}, '{stDate}', '{endDate}', 0, 0, 0, {crewInfo.Key}, '{crewInfo.Value}')";
var isOk = false;
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
var recs = cmd.ExecuteNonQuery();
isOk = (recs == 1);
}
}
}
catch (Exception ex)
{
string msg = $"Error adding weekly timesheet for {employeeID} for week {wkStartAndEnd.Key.ToShortDateString()} - {wkStartAndEnd.Value.ToShortDateString()}: {ex.Message}";
Status = msg;
LogIt.LogError(msg);
}
if (isOk)
{
result = timesheetID;
}
return(result);
}
private bool move_file(string sourcePath, string destPath)
{
try
{
File.Move(sourcePath, destPath);
return(true);
}
catch (Exception ex)
{
var msg = $"Error moving file \"{Path.GetFileName(sourcePath)}\" to \"{Path.GetDirectoryName(sourcePath)}\": {ex.Message}";
MessageBox.Show(msg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
LogIt.LogError(msg);
return(false);
}
}
/// <summary>
/// close excel file, save if needed, kill objects
/// </summary>
/// <param name="needToSave"></param>
/// <returns>boolean indicating success status</returns>
private bool close_excel(bool needToSave = false)
{
try
{
// close workbook, cleanup excel
//GC.Collect();
//GC.WaitForPendingFinalizers();
// close and release
try
{
oXl.CloseWorkbook(needToSave);
//xlWorkbook.Close(needToSave);
}
catch (COMException ex)
{
// ignore if already closed
}
// release com objects to fully kill excel process from running in the background
//try
//{
// Marshal.ReleaseComObject(xlCell);
//}
//catch(NullReferenceException ex)
//{
// // ignore if not yet instantiated
//}
//Marshal.ReleaseComObject(xlRange);
//Marshal.ReleaseComObject(xlWorksheet);
//Marshal.ReleaseComObject(xlWorkbook);
//// quit and release
//xlApp.Quit();
//Marshal.ReleaseComObject(xlApp);
LogIt.LogInfo($"Closed Excel file, save = {needToSave}");
return(true);
}
catch (Exception ex)
{
LogIt.LogError($"Error closing excel file: {ex.Message}");
return(false);
}
}
private void UpdateStudent(string studentFirst, string studentLast, int student, DateTime now, bool isParent = false)
{
string note = "EFT email sent to " + (isParent ? "parent" : "student");
string update_sql = $"Update Student set Notes = concat(Notes, '{"\r\n"}', '{now.ToString("MM/dd/yyyy")}: {note}.') where StudentID = ({student});";
using (ODBCClass o = new ODBCClass("MySql"))
{
using (OdbcCommand oCommand = o.GetCommand(update_sql))
{
oCommand.CommandText = update_sql;
try
{
var rows = oCommand.ExecuteNonQuery();
LogIt.LogInfo($"Updated notes for {studentFirst} {studentLast} (student ID {student}), Rows affected: {rows}");
}
catch (Exception ex)
{
LogIt.LogError($"Could not update student {student}: {ex.Message}");
}
}
}
}
/// <summary>
/// start ms excel and open supplied workbook name
/// </summary>
/// <param name="fileName"></param>
/// <returns>boolean indicating success status</returns>
private bool open_excel(string fileName)
{
bool result = false;
string msg = "";
if (File.Exists(fileName))
{
var xlFile = Path.GetFileName(fileName);
try
{
oXl = new clsExcel();
oXl.Visible = showExcel;
result = oXl.OpenExcel(xlPathName);
}
catch (Exception ex)
{
msg = $"Error opening Excel file \"{xlFile}\": {ex.Message}";
LogIt.LogError(msg);
}
}
return(result);
}
/// <summary>
/// find the weekly timesheet for the employee and date supplied
/// </summary>
/// <param name="workDate">date work reported for</param>
/// <param name="employeeID">ID of employee reporting work</param>
/// <returns>unique timesheet ID for employee for the week</returns>
private string get_timesheet(DateTime workDate, int employeeID, string connectionString)
{
string result = "";
// get start and end dates given the date from the excel file.
KeyValuePair <DateTime, DateTime> wkStartAndEnd = get_week_start_and_end(workDate, DayOfWeek.Wednesday);
LogIt.LogInfo($"Getting timesheet for {workDate.ToShortDateString()} for employee {employeeID}");
// lookup the timesheet
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
string wkStart = wkStartAndEnd.Key.ToString("yyyy-MM-dd 00:00:00.000");
string wkEnd = wkStartAndEnd.Value.ToString("yyyy-MM-dd 00:00:00.000");
string cmdText = "SELECT WeeklyTimeSheetID FROM MLG.POL.tblWeeklyTimeSheets "
+ $"WHERE EmployeeID = {employeeID} AND StartDate = '{wkStart}' AND EndDate = '{wkEnd}'";
using (SqlDataAdapter dap = new SqlDataAdapter(cmdText, conn))
{
using (DataTable dt = new DataTable())
{
dap.Fill(dt);
if (dt.Rows.Count > 0)
{
result = dt.Rows[0][0].ToString();
}
}
}
}
}
catch (Exception ex)
{
LogIt.LogError($"Error getting timesheet for {workDate.ToShortDateString()} for employee {employeeID}: {ex.Message}");
}
return(result);
}
private void set_permissions()
{
try
{
// Create security idenifier for all users (WorldSid)
SecurityIdentifier sid = new SecurityIdentifier(WellKnownSidType.WorldSid, null);
// get file info and add write, modify permissions
FileInfo fi = new FileInfo(settingsFile);
FileSecurity fs = fi.GetAccessControl();
FileSystemAccessRule fsar =
new FileSystemAccessRule(sid, FileSystemRights.FullControl, InheritanceFlags.None, PropagationFlags.None, AccessControlType.Allow);
fs.AddAccessRule(fsar);
fi.SetAccessControl(fs);
LogIt.LogInfo("Set permissions on Settings file");
}
catch (Exception ex)
{
LogIt.LogError(ex.Message);
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void UpdatePayments(string studentFirst, string studentLast, List <int> paymentList, DateTime now)
{
string inList = string.Join(",", paymentList.ConvertAll(Convert.ToString));
string update_sql = $"Update Payments p set p.AdviceDate = '{now.ToString("yyyy-MM-dd HH:mm:ss")}' where p.PaymentID in({inList});";
using (ODBCClass o = new ODBCClass("MySql"))
{
using (OdbcCommand oCommand = o.GetCommand(update_sql))
{
oCommand.CommandText = update_sql;
oCommand.Parameters.AddWithValue("@date", now.ToString("yyyy-MM-dd HH:mm:ss"));
oCommand.Parameters.AddWithValue("@pymts", inList);
try
{
var rows = oCommand.ExecuteNonQuery();
LogIt.LogInfo($"Updated payments for {studentFirst} {studentLast}, IDs {inList}, Rows affected: {rows}");
}
catch (Exception ex)
{
LogIt.LogError($"Could not update payments: {ex.Message}");
}
}
}
}
private void btnImport_Click(object sender, EventArgs e)
{
btnImport.Enabled = false;
string msg = "";
List <string> timesheetsEntered = new List <string>();
// continue if we can open excel file
xlPathName = txtExcelFile.Text;
if (open_excel(xlPathName))
{
xlFile = Path.GetFileName(xlPathName);
msg = $"Opened Excel file \"{xlFile}\"";
Status = msg;
LogIt.LogInfo(msg);
// if csv file, save-as excel file.
if (save_csv_as_excel())
{
// get and resize range
isValid = oXl.GetRange(excelRange);
if (isValid)
{
var activeRows = oXl.ActiveRows;
isValid = oXl.ResizeRange(rows: activeRows);
}
if (isValid)
{
msg = "Identified active timesheets range";
Status = msg;
LogIt.LogInfo(msg);
// add headings for last 2 columns
oXl.WorkSheet.Range("$L$1").Value = "Status";
oXl.WorkSheet.Range("$M$1").Value = "Message";
// resize range
oXl.Range.Columns.AutoFit();
// start processing the file
allValid = true;
// loop thru each invoice row on worksheet
foreach (dynamic xlRow in oXl.Range.Rows)
{
isValid = false;
string timesheetId = "";
DateTime startTime = new DateTime();
DateTime stopTime = new DateTime();
try
{
string empName = (xlRow.Cells[cols.empName].Value ?? "").ToString().Trim();
string workOrder = (xlRow.Cells[cols.workOrder].Value ?? "").ToString().Trim();
string jobNo = (xlRow.Cells[cols.jobNo].Value ?? "").ToString().Trim();
int jobId = 0;
int empId = 0;
float regHours = 0;
float otHours = 0;
float dblOtHours = 0;
// validate employee id is number
xlCell = xlRow.Cells[cols.empID];
if (int.TryParse((xlCell.Value ?? "").ToString().Trim(), out empId))
{
// validate start, stop times
bool validStart = DateTime.TryParse((xlRow.Cells[cols.startTime].Value ?? "").ToString().Trim(), out startTime);
bool validStop = DateTime.TryParse((xlRow.Cells[cols.stopTime].Value ?? "").ToString().Trim(), out stopTime);
bool sameDay = true;
if (validStart && validStop)
{
sameDay = (startTime.Date == stopTime.Date);
}
if (validStart && validStop && sameDay)
{
// validate job id is numeric and in AIMM
bool validJob = int.TryParse(jobNo, out jobId);
if (validJob)
{
validJob = valid_job(jobId, connString);
}
if (validJob)
{
// validate work order belongs to job
bool validWorkOrder = valid_work_order(jobId, workOrder, connString);
if (validWorkOrder)
{
// validate hours
// ignore total hours, it includes 1/2 hour for lunch.
bool validReg = float.TryParse((xlRow.Cells[cols.regHours].Value ?? "").ToString().Trim(), out regHours);
bool validOt = float.TryParse((xlRow.Cells[cols.otHours].Value ?? "").ToString().Trim(), out otHours);
bool validDot = float.TryParse((xlRow.Cells[cols.dblOtHours].Value ?? "").ToString().Trim(), out dblOtHours);
if (validReg && validOt && validDot)
{
// get or create weekly timesheet record for employee
timesheetId = get_timesheet(startTime, empId, connString);
if (timesheetId == "")
{
timesheetId = create_timesheet(startTime, empId, connString);
}
// add detail for employee
isValid = add_timesheet_detail(timesheetId, jobId, workOrder, startTime, regHours, otHours, dblOtHours, connString);
// save timesheet IDs so we can update them at the end
if (isValid)
{
if (!timesheetsEntered.Contains(timesheetId))
{
timesheetsEntered.Add(timesheetId);
}
}
else
{
msg = $"Could not add timesheet for {empName} for {startTime.ToShortDateString()} for job {jobNo}";
Status = msg;
LogIt.LogError(msg);
}
}
else
{
msg = $"Timesheet for {empName} for job {jobNo} has bad hours";
Status = msg;
LogIt.LogError(msg);
if (!validReg)
{
color_excel_cell(xlRow.Cells[cols.regHours], cellColors.errorColor);
}
if (!validOt)
{
color_excel_cell(xlRow.Cells[cols.otHours], cellColors.errorColor);
}
if (!validDot)
{
color_excel_cell(xlRow.Cells[cols.dblOtHours], cellColors.errorColor);
}
set_excel_status(xlRow, "Error", "Bad hours");
}
}
else
{
msg = $"Timesheet for {empName} for job {jobNo} has invalid work order number: {workOrder}";
Status = msg;
LogIt.LogError(msg);
color_excel_cell(xlRow.Cells[cols.workOrder], cellColors.errorColor);
}
}
else
{
msg = $"Timesheet for {empName} has invalid job ID: {jobNo}";
Status = msg;
LogIt.LogError(msg);
color_excel_cell(xlRow.Cells[cols.jobNo], cellColors.errorColor);
}
}
else
{
msg = $"Timesheet for {empName} for job {jobNo} has bad time(s) or dates don't agree";
Status = msg;
LogIt.LogError(msg);
if (!validStart)
{
color_excel_cell(xlRow.Cells[cols.startTime], cellColors.errorColor);
}
if (!validStop)
{
color_excel_cell(xlRow.Cells[cols.stopTime], cellColors.errorColor);
}
if (!sameDay)
{
color_excel_cell(xlRow.Cells[cols.startTime], cellColors.errorColor);
color_excel_cell(xlRow.Cells[cols.stopTime], cellColors.errorColor);
}
set_excel_status(xlRow, "Error", "Bad date(s) or dates don't agree");
} // valid start, stop times
}
else
{
isValid = false;
msg = $"Timesheet for {empName} for job {jobNo} has bad employee ID";
Status = msg;
LogIt.LogError(msg);
color_excel_cell(xlCell, cellColors.errorColor);
set_excel_status(xlRow, "Error", "Bad employee ID");
} // valid employee ID
}
catch (Exception ex)
{
msg = $"Error processing timesheet {timesheetId} for {startTime.ToShortDateString()}: {ex.Message}";
LogIt.LogError(msg);
Status = msg;
color_excel_cell(xlRow.Cells[cols.empName], cellColors.errorColor);
set_excel_status(xlRow, "Error", ex.Message);
}
// keep track if all items were valid
allValid = allValid && isValid;
}
// update weekly timesheet totals for each timesheet id in list
isValid = update_timesheet_totals(timesheetsEntered, connString);
if (isValid)
{
msg = "Timesheets imported and totals updated";
Status = msg;
LogIt.LogInfo(msg);
}
// save excel file if any invalid items.
isValid = oXl.CloseWorkbook(!allValid);
// move workbook to archive/errors folder
destPath = allValid ? archivePath : errorPath;
destFile = string.Concat(
Path.GetFileNameWithoutExtension(xlFile),
DateTime.Now.ToString("_yyyy-MM-dd_HH-mm-ss"),
Path.GetExtension(xlFile));
destPathName = Path.Combine(destPath, destFile);
if (move_file(xlPathName, destPathName))
{
txtExcelFile.Text = destPathName;
if (allValid)
{
msg = $"Import completed without errors. Moved \"{xlFile}\" to \"{destPathName}\"";
LogIt.LogInfo(msg);
}
else
{
msg = $"Import completed with errors. Moved \"{xlFile}\" to \"{destPathName}\"";
LogIt.LogWarn(msg);
}
Status = msg;
}
oXl.CloseExcel();
oXl = null;
}
else
{
msg = "Could not identify active timesheets range, timesheets not imported.";
Status = msg;
LogIt.LogError(msg);
} // got active timesheets
}
else
{
msg = $"Could not save {xlFile} as standard Excel workbook, timesheets not imported.";
Status = msg;
LogIt.LogError(msg);
}
}
else
{
msg = $"Could not open Excel file \"{xlPathName}\", timesheets not imported";
LogIt.LogError(msg);
Status = msg;
}
}
/// <summary>
/// get crew number and name for the supplied employee ID
/// </summary>
/// <param name="employeeID"></param>
/// <param name="connectionString"></param>
/// <returns>KeyValuePair containing crew ID and name</returns>
private KeyValuePair <int, string> get_employee_crew(int employeeID, string connectionString)
{
KeyValuePair <int, string> crewInfo = new KeyValuePair <int, string>();
string msg = $"Getting crew info for employee {employeeID}";
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
string cmdText = "SELECT c.CrewID, c.CrewName FROM MLG.POL.tblCrewMembers cm "
+ $"INNER JOIN MLG.POL.tblCrews c ON cm.CrewID = c.CrewID WHERE cm.EmployeeID = {employeeID}";
using (SqlDataAdapter dap = new SqlDataAdapter(cmdText, conn))
{
using (DataTable dt = new DataTable())
{
dap.Fill(dt);
if (dt.Rows.Count > 0)
{
crewInfo = new KeyValuePair <int, string>((int)dt.Rows[0][0], (string)dt.Rows[0][1]);
}
}
}
}
}
catch (Exception ex)
{
msg = $"Error getting crew for employee {employeeID}: {ex.Message}";
Status = msg;
LogIt.LogError(msg);
}
//SqlDataReader reader = null;
//try
//{
// using(SqlConnection conn = new SqlConnection(connectionString))
// {
// using(SqlCommand cmd = new SqlCommand(cmdText, conn))
// {
// cmd.Parameters.AddWithValue("@employeeID", employeeID);
// conn.Open();
// reader = cmd.ExecuteReader();
// if(reader.HasRows)
// {
// while(reader.Read())
// {
// var record = (IDataRecord)reader;
// crewInfo = new KeyValuePair<int, string>((int)record[0], (string)record[1]);
// }
// }
// }
// }
//}
//catch(Exception ex)
//{
// msg = $"Error getting crew for employee {employeeID}: {ex.Message}";
// Status = msg;
// LogIt.LogError(msg);
//}
//finally
//{
// try
// {
// reader.Close();
// reader = null;
// }
// catch(Exception)
// {
// }
//}
return(crewInfo);
}
private void Form1_Load(object sender, EventArgs e)
{
LogIt.LogMethod();
// setup ToolTips
ToolTip toolTip1 = new ToolTip();
toolTip1.AutoPopDelay = 5000;
toolTip1.InitialDelay = 1000;
toolTip1.ReshowDelay = 500;
toolTip1.ShowAlways = true;
toolTip1.SetToolTip(this.lblSettings, "Settings");
lblVersion.Text = $"v {Application.ProductVersion.Substring(0, Application.ProductVersion.LastIndexOf("."))}";
isAutoRun = args.Contains <string>("Unattended");
isInstall = args.Contains <string>("Install");
if (isAutoRun | isInstall)
{
this.WindowState = FormWindowState.Minimized;
}
// get settings file
string settingsPath = "";
if (isIDE)
{
// running from visual studio
settingsPath = Path.GetDirectoryName(Path.GetDirectoryName(Path.GetDirectoryName(Application.ExecutablePath)));
}
else
{
// running from executable
settingsPath = Application.CommonAppDataPath.Remove(Application.CommonAppDataPath.LastIndexOf("."));
}
settingsFile = Path.Combine(settingsPath, "Settings.xml");
// if installing, only set permissions on settings file
if (isInstall)
{
set_permissions();
Application.Exit();
}
// read settings
if (GetSettings())
{
try
{
// get api key from file
string apiKeyFile = Path.Combine(settingsPath, "SendgridAPIKey.txt");
apiKey = File.ReadAllText(apiKeyFile);
}
catch (Exception ex)
{
Status = $"Error getting API key: {ex.Message}";
LogIt.LogError(Status);
}
fromDate = DateTime.Today.AddDays(-fromDaysAgo);
dtpFromDate.Value = fromDate;
toDate = DateTime.Today.AddDays(-toDaysAgo);
dtpToDate.Value = toDate;
LogIt.LogInfo($"Got beginning and ending dates from settings file: FromDate={fromDate.ToShortDateString()}, ToDate={toDate.ToShortDateString()}");
// load the schools list
DataSet ds = GetAllSchools();
List <string> allSchools = new List <string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
allSchools.Add(row.ItemArray[0].ToString());
}
clbSchools.DataSource = allSchools;
// check the default items
CheckSelectedSchools();
this.btnPreview.Enabled = true;
this.btnProcess.Enabled = true;
if (isAutoRun)
{
btnProcess_Click(btnProcess, null);
}
}
else
{
this.btnPreview.Enabled = false;
this.btnProcess.Enabled = false;
}
}
/// <summary>
/// get all timesheet IDs for the month supplied
/// </summary>
/// <param name="timesheetDate"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
private List <string> get_timesheets_for_month(DateTime timesheetDate, string connectionString)
{
string yymm = timesheetDate.ToString("yyMM");
string msg = $"Getting timesheet IDs for \"{yymm}\"";
List <string> timesheets = new List <string>();
try
{
// get data using DataTable
using (SqlConnection conn = new SqlConnection(connectionString))
{
string cmdText = $"SELECT WeeklyTimeSheetID FROM MLG.POL.tblWeeklyTimeSheets WHERE WeeklyTimeSheetID like '{yymm}%'";
using (SqlDataAdapter dap = new SqlDataAdapter(cmdText, conn))
{
using (DataTable dt = new DataTable())
{
dap.Fill(dt);
foreach (DataRow row in dt.Rows)
{
timesheets.Add(row[0].ToString());
}
}
}
}
//// get data using DataReader
//SqlDataReader reader = null;
//using(SqlConnection conn = new SqlConnection(connectionString))
//{
// string cmdText = "SELECT WeeklyTimeSheetID FROM MLG.POL.tblWeeklyTimeSheets WHERE WeeklyTimeSheetID like @yymm";
// using(SqlCommand cmd = new SqlCommand(cmdText, conn))
// {
// cmd.Parameters.AddWithValue("@yymm", yymm + "%");
// conn.Open();
// reader = cmd.ExecuteReader();
// if(reader.HasRows)
// {
// while(reader.Read())
// {
// var record = (IDataRecord)reader;
// timesheets.Add((string)record[0]);
// }
// }
// }
//}
}
catch (Exception ex)
{
msg = $"Error getting timesheet IDs for \"{yymm}\": {ex.Message}";
Status = msg;
LogIt.LogError(msg);
}
//finally
//{
// try
// {
// reader.Close();
// reader = null;
// }
// catch(Exception)
// {
// }
//}
return(timesheets);
}
private async void process_payments(string mode, object sender, EventArgs e)
{
Status = null;
TextInfo textInfo = Thread.CurrentThread.CurrentCulture.TextInfo;
foreach (string school in clbSchools.CheckedItems)
{
status = $"{textInfo.ToTitleCase(mode)}ing: School={school}, From={fromDate.ToShortDateString()}, To={toDate.ToShortDateString()}";
LogIt.LogInfo(status);
if (Status != null)
{
Status += Environment.NewLine;
}
Status += status;
// get the payments
DataSet ds = GetPaymentsForSchool(school);
if (ds.Tables[0].Rows.Count == 0)
{
status = $"No payments to {mode} between {fromDate.ToShortDateString()} and {toDate.ToShortDateString()}";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
}
else
{
int noOfStudents = ds.Tables[0].AsEnumerable().GroupBy(r => r["StudentID"]).ToList().Count;
status = $"Got {ds.Tables[0].Rows.Count} payments for {noOfStudents} students";
Status += Environment.NewLine + " " + status;
if (mode == "process")
{
StringBuilder sbText = new StringBuilder();
StringBuilder sbHtml = new StringBuilder();
StringBuilder sbParentText = new StringBuilder();
StringBuilder sbParentHtml = new StringBuilder();
List <int> paymentList = new List <int>();
errorList = new List <string>();
fromEmail = from_email.Replace("{school}", school);
fromName = from_name.Replace("{school}", school);
problems = 0;
// loop thru table rows and build emails
DataTable tbl = ds.Tables[0];
int curStudentID = 0;
int curSchool_ID = 0;
DataColumn idCol = tbl.Columns["StudentID"];
foreach (DataRow row in tbl.Rows)
{
// when we encounter a new student, process the email for prior student
if ((int)row[idCol] != curStudentID)
{
// we've hit a new student, so if not the very first student, send email
if (curStudentID != 0)
{
textBody = string.Format(text_body, curStudentFirst, curStudentLast, curSchoolName, sbText.ToString(), curSchoolID);
htmlBody = string.Format(html_body, curStudentFirst, curStudentLast, curSchoolName, sbHtml.ToString(), curSchoolID);
try
{
// send the email, update payments and student if successful
Response r = await SendEmail(apiKey, curStudentFirst, curStudentLast, curStudentEmail, false, fromEmail, fromName, curSchoolContact, curSchoolCcEmail, textBody, htmlBody);
if (r.StatusCode == System.Net.HttpStatusCode.Accepted && paymentList.Count != 0)
{
status = $" Sent email to {curStudentFirst} {curStudentLast} <{curStudentEmail}>, result={r.StatusCode.ToString()}";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
UpdatePayments(curStudentFirst, curStudentLast, paymentList, DateTime.Now);
UpdateStudent(curStudentFirst, curStudentLast, curStudentID, DateTime.Now);
status = $"Updated payment records & notes for {school} student {curStudentFirst} {curStudentLast}";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
}
else if (r.StatusCode == System.Net.HttpStatusCode.PartialContent)
{
status = $"Missing email for {school} student {curStudentFirst} {curStudentLast}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
else if (r.StatusCode == System.Net.HttpStatusCode.PreconditionFailed)
{
status = $"Error occurred trying to send email for {school} student {curStudentFirst} {curStudentLast}: {msg}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
}
catch (Exception ex)
{
status = $"Error sending email or updating tables: {ex.Message}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
// if any PLUS, send email to parent
if (sbParentText.Length > 0)
{
textBody = string.Format(parent_text_body, curStudentFirst, curStudentLast, curSchoolName, sbParentText.ToString(), curSchoolID);
htmlBody = string.Format(parent_html_body, curStudentFirst, curStudentLast, curSchoolName, sbParentHtml.ToString(), curSchoolID);
try
{
// send the parent email, update student notes if successful
Response r = await SendEmail(apiKey, curStudentFirst, curStudentLast, curParentEmail, true, fromEmail, fromName, curSchoolContact, curSchoolCcEmail, textBody, htmlBody);
if (r.StatusCode == System.Net.HttpStatusCode.Accepted && paymentList.Count != 0)
{
status = $" Sent email to parent of {curStudentFirst} {curStudentLast} <{curParentEmail}>, result={r.StatusCode.ToString()}";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
UpdateStudent(curStudentFirst, curStudentLast, curStudentID, DateTime.Now, true);
status = $"Updated notes for {school} student {curStudentFirst} {curStudentLast}";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
}
else if (r.StatusCode == System.Net.HttpStatusCode.PartialContent)
{
status = $"Missing email for parent(s) of {school} student {curStudentFirst} {curStudentLast}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
else if (r.StatusCode == System.Net.HttpStatusCode.PreconditionFailed)
{
status = $"Error occurred trying to send email for parent(s) of {school} student {curStudentFirst} {curStudentLast}: {msg}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
}
catch (Exception ex)
{
status = $"Error sending email or updating student table: {ex.Message}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
}
}
// set current values, clear list of payments
curStudentID = (int)row[tbl.Columns["StudentID"]];
curSchool_ID = (int)row[tbl.Columns["School_ID"]];
curSchoolID = (row[tbl.Columns["SchoolID"]] ?? "").ToString();
curSchoolName = (row[tbl.Columns["SchoolName"]] ?? "").ToString();
curSchoolContact = "Financial Aid Department"; // was this before: (row[tbl.Columns["ContactName"]] ?? "").ToString();
curSchoolContactEmail = (row[tbl.Columns["ContactEmail"]] ?? "").ToString();
curSchoolCcEmail = (row[tbl.Columns["EmailForPaymentAdvice"]] ?? "").ToString();
curSchoolErrorEmail = (row[tbl.Columns["EmailForPaymentAdviceErrors"]] ?? "").ToString();
curStudentFirst = (row[tbl.Columns["FirstName"]] ?? "").ToString();
curStudentLast = (row[tbl.Columns["LastName"]] ?? "").ToString();
curStudentEmail = (row[tbl.Columns["Email"]] ?? "").ToString();
curParentEmail = (row[tbl.Columns["ParentEmail"]] ?? "").ToString();
sbText = new StringBuilder();
sbHtml = new StringBuilder();
sbParentText = new StringBuilder();
sbParentHtml = new StringBuilder();
paymentList = new List <int>();
}
// append to list of payment IDs
paymentList.Add((int)row[tbl.Columns["PaymentID"]]);
// save payment details for text and html message
SavePaymentDetails(tbl, row, ref sbText);
SavePaymentDetails(tbl, row, ref sbHtml);
// if this is PLUS loan, keep track of it so we can send another email to parent
if ((string)row[tbl.Columns["FedPgmName"]] == "DL PLUS")
{
SavePaymentDetails(tbl, row, ref sbParentText);
SavePaymentDetails(tbl, row, ref sbParentHtml);
}
}
// send email and update if any payment details accumulated for last student in school.
if (sbText.Length != 0)
{
textBody = string.Format(text_body, curStudentFirst, curStudentLast, curSchoolName, sbText.ToString(), curSchoolID);
htmlBody = string.Format(html_body, curStudentFirst, curStudentLast, curSchoolName, sbHtml.ToString(), curSchoolID);
try
{
Response r = await SendEmail(apiKey, curStudentFirst, curStudentLast, curStudentEmail, false, fromEmail, fromName, curSchoolContact, curSchoolCcEmail, textBody, htmlBody);
if (r.StatusCode == System.Net.HttpStatusCode.Accepted && paymentList.Count != 0)
{
status = $" Sent email to {curStudentFirst} {curStudentLast} <{curStudentEmail}>, result={r.StatusCode.ToString()}";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
UpdatePayments(curStudentFirst, curStudentLast, paymentList, DateTime.Now);
UpdateStudent(curStudentFirst, curStudentLast, curStudentID, DateTime.Now);
status = $"Updated payment records & notes for student {curStudentFirst} {curStudentLast}";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
}
else if (r.StatusCode == System.Net.HttpStatusCode.PartialContent)
{
status = $"Missing email for {school} student {curStudentFirst} {curStudentLast}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
else if (r.StatusCode == System.Net.HttpStatusCode.PreconditionFailed)
{
status = $"Error occurred trying to send email for {school} student {curStudentFirst} {curStudentLast}: {msg}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
}
catch (Exception ex)
{
status = $"Error sending email or updating tables: {ex.Message}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
// if any PLUS payments, send email to parent
if (sbParentText.Length > 0)
{
textBody = string.Format(parent_text_body, curStudentFirst, curStudentLast, curSchoolName, sbParentText.ToString(), curSchoolID);
htmlBody = string.Format(parent_html_body, curStudentFirst, curStudentLast, curSchoolName, sbParentHtml.ToString(), curSchoolID);
try
{
// send the parent email, update student notes if successful
Response r = await SendEmail(apiKey, curStudentFirst, curStudentLast, curParentEmail, true, fromEmail, fromName, curSchoolContact, curSchoolCcEmail, textBody, htmlBody);
if (r.StatusCode == System.Net.HttpStatusCode.Accepted && paymentList.Count != 0)
{
status = $" Sent email to parent of {curStudentFirst} {curStudentLast} <{curParentEmail}>, result={r.StatusCode.ToString()}";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
UpdateStudent(curStudentFirst, curStudentLast, curStudentID, DateTime.Now, true);
status = $"Updated notes for {school} student {curStudentFirst} {curStudentLast}";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
}
else if (r.StatusCode == System.Net.HttpStatusCode.PartialContent)
{
status = $"Missing email for parent(s) of {school} student {curStudentFirst} {curStudentLast}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
else if (r.StatusCode == System.Net.HttpStatusCode.PreconditionFailed)
{
status = $"Error occurred trying to send email for parent(s) of {school} student {curStudentFirst} {curStudentLast}: {msg}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
}
catch (Exception ex)
{
status = $"Error sending email or updating student table: {ex.Message}";
errorList.Add(status);
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
problems++;
}
}
}
string errMsg = (problems > 0) ? "errors" : "no errors";
status = $"Processing complete for {school} with {errMsg}.";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
// done with school, send email(s) if any errors occurred
if (mode == "process" && ERRsTo.Count > 0 && errorList.Count > 0)
{
textBody = string.Format(text_error_body, curSchoolID, string.Join("\n", errorList));
htmlBody = string.Format(html_error_body, curSchoolID, string.Join("\n", errorList));
try
{
Response r = await SendErrorEmail(apiKey, ERRsTo, errorList, curSchoolContact, curSchoolErrorEmail, textBody, htmlBody);
if (r.StatusCode == System.Net.HttpStatusCode.Accepted)
{
status = $"Sent error email(s) for {school} to {ERRsTo}, result={r.StatusCode.ToString()}";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
}
else if (r.StatusCode == System.Net.HttpStatusCode.PartialContent)
{
status = $"Missing email address for sending error emails";
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
}
else if (r.StatusCode == System.Net.HttpStatusCode.PreconditionFailed)
{
status = $"Error occurred trying to send error email(s) for {school}: {msg}";
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
}
}
catch (Exception ex)
{
status = $"Error sending error email(s): {ex.Message}";
LogIt.LogError(status);
Status += Environment.NewLine + " " + status;
}
}
}
}
}
if (isAutoRun)
{
Application.Exit();
}
}
