private DataSet GetAllSchools()
{
DataSet ds = new DataSet();
try
{
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);
}
}
}
Status = "";
}
catch (Exception ex)
{
Status = $"Error getting schools: {ex.Message}";
}
return(ds);
}
private DataSet GetPaymentsForSchool(string school)
{
DataSet ds = new DataSet();
try
{
//if(Status != null)
// Status += Environment.NewLine;
status = $"Getting payments made";
LogIt.LogInfo(status);
Status += Environment.NewLine + " " + status;
using (ODBCClass o = new ODBCClass("MySql"))
{
var procName = "payments_made(?,?,?)";
var parms = new KeyValuePair <string, string>[] {
new KeyValuePair <string, string>("_school", school),
new KeyValuePair <string, string>("_fromDate", fromDate.ToString("yyyy-MM-dd")),
new KeyValuePair <string, string>("_toDate", toDate.ToString("yyyy-MM-dd"))
};
using (OdbcCommand oCommand = o.GetCommand(procName, parms))
{
using (OdbcDataAdapter oAdapter = new OdbcDataAdapter(oCommand))
{
oAdapter.Fill(ds);
}
}
}
}
catch (Exception)
{
}
return(ds);
}
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}");
}
}
}
}
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}");
}
}
}
}