public JsonResult MSU_Create(UsersModalModel jsonString)
{
string StudentNumber = jsonString.StudentNumber;
string FirstName = jsonString.FirstName;
string LastName = jsonString.LastName;
string Email = jsonString.Email;
string Notes = jsonString.Notes;
//Notes field is allowed to be null, therfore account for string variation
if (Notes != null)
{
Notes = "'" + Notes + "'";
}
else
{
Notes = "NULL";
}
bool createStatus = mugshareusers.CreateQuery(StudentNumber, FirstName, LastName, Email, Notes);
IsTrueOrFalse model = new IsTrueOrFalse(createStatus);
return(ResultJson(model));
}
public JsonResult MSU_Edit(UsersModalModel jsonString)
{
string UserKey = jsonString.UserKey;
string StudentNumber = jsonString.StudentNumber;
string FirstName = jsonString.FirstName;
string LastName = jsonString.LastName;
string Email = jsonString.Email;
bool MugInUse = jsonString.MugInUse;
string DateOfRental = jsonString.DateOfRental;
string Notes = jsonString.Notes;
//Date of rental field is allowed to be null, therfore account for string variation
if (DateOfRental != null)
{
DateOfRental = "'" + DateOfRental + "'";
}
else
{
DateOfRental = "NULL";
}
//Notes field is allowed to be null, therfore account for string variation
if (Notes != null)
{
Notes = "'" + Notes + "'";
}
else
{
Notes = "NULL";
}
bool editStatus = mugshareusers.EditQuery(UserKey, StudentNumber, FirstName, LastName, Email, MugInUse, DateOfRental, Notes);
IsTrueOrFalse model = new IsTrueOrFalse(editStatus);
return(ResultJson(model));
}
/*
* Function: GetRecord
*
* Get data for mug share users modals.
*
* Returns:
*
* model - a UsersModalModel model of the record with the primary key equal to the UserKey
*/
public static UsersModalModel GetRecord(string UserKey)
{
SqlConnection dbconnection = new SqlConnection();
SqlCommand dbcommand = new SqlCommand();
SqlDataReader dbreader;
string connectionstring = ConfigurationManager.ConnectionStrings["MugShareDB"].ToString();
string queryString = "SELECT * FROM MugShareUsers WHERE pKey = " + UserKey;
UsersModalModel model = new UsersModalModel();
try
{
if (dbconnection.State == ConnectionState.Closed)
{
dbconnection.ConnectionString = connectionstring; dbconnection.Open();
}
dbcommand.Connection = dbconnection;
dbcommand.CommandTimeout = 600;
dbcommand.CommandText = queryString;
dbcommand.CommandType = CommandType.Text;
dbreader = dbcommand.ExecuteReader();
if (dbreader.HasRows)
{
dbreader.Read();
if (!string.IsNullOrEmpty(dbreader["pKey"].ToString()))
{
model.UserKey = dbreader["pKey"].ToString();
}
if (!string.IsNullOrEmpty(dbreader["StudentNumber"].ToString()))
{
model.StudentNumber = dbreader["StudentNumber"].ToString();
}
if (!string.IsNullOrEmpty(dbreader["FirstName"].ToString()))
{
model.FirstName = dbreader["FirstName"].ToString();
}
if (!string.IsNullOrEmpty(dbreader["LastName"].ToString()))
{
model.LastName = dbreader["LastName"].ToString();
}
if (!string.IsNullOrEmpty(dbreader["Email"].ToString()))
{
model.Email = dbreader["Email"].ToString();
}
if (dbreader["MugInUse"].ToString() == "True")
{
model.MugInUse = true;
}
else
{
model.MugInUse = false;
}
if (!string.IsNullOrEmpty(dbreader["DateOfRental"].ToString()))
{
string temp = dbreader["DateOfRental"].ToString();
model.DateOfRental = temp.Substring(0, temp.Length - 12);
}
if (!string.IsNullOrEmpty(dbreader["TotalMugsBorrowed"].ToString()))
{
model.TotalMugsBorrowed = dbreader["TotalMugsBorrowed"].ToString();
}
if (!string.IsNullOrEmpty(dbreader["Notes"].ToString()))
{
model.Notes = dbreader["Notes"].ToString();
}
}
return(model);
}
catch (Exception e)
{
throw new Exception(@"Mug-Share Application GetRecord() failed : ", e);
}
finally
{
if (dbconnection.State == ConnectionState.Open)
{
dbconnection.Close();
}
}
}
public JsonResult MSU_Excel(string ExcelPath)
{
//string connectionString = @"C:\Users\Logan\Downloads\StudentInfoExample.xlsx";
string connectionString = ExcelPath;
string sheetName = "Sheet1";
bool createStatus = false;
//tempString is the hardcoded working string, and connectionString is the same string passed through ajax
//if(tempString != connectionString)
//{
// IsTrueOrFalse temp = new IsTrueOrFalse(false);
// return ResultJson(temp);
//}
List <UsersModalModel> list = new List <UsersModalModel>();
//connect to excel document and get row data
var excelFile = new ExcelQueryFactory(connectionString);
var studentRecords = from a in excelFile.Worksheet(sheetName) select a;
//create UsersModalModel models containing the data from excel document
foreach (var a in studentRecords)
{
UsersModalModel model = new UsersModalModel();
model.StudentNumber = a["STUD_NO"];
model.FirstName = a["GIVEN_NAME"];
model.LastName = a["SURNAME"];
model.Email = a["EMAIL_ADDRESS"];
model.TotalMugsBorrowed = "0";
list.Add(model);
}
//get ride of duplicates based on student numbers
list = list.GroupBy(x => x.StudentNumber).Select(y => y.First()).ToList();
//delete all records from the MugShareUsers table in database
if (list.Count > 0)
{
string clearMSUTable = "DELETE FROM MugShareUsers";
createStatus = mugshareusers.QueryProcessor(clearMSUTable);
}
//make query string to input excel data into database
if (createStatus)
{
string dataEntryQuery = "INSERT INTO MugShareUsers (StudentNumber, FirstName, LastName, Email, MugInUse, TotalMugsBorrowed) VALUES ";
foreach (UsersModalModel element in list)
{
string recordValues = "(" + element.StudentNumber + ", '" + element.FirstName + "', '" + element.LastName + "', '" + element.Email + "', 'false', 0), ";
dataEntryQuery += recordValues;
}
dataEntryQuery = dataEntryQuery.Substring(0, dataEntryQuery.Length - 2);
createStatus = mugshareusers.QueryProcessor(dataEntryQuery);
}
IsTrueOrFalse boolModel = new IsTrueOrFalse(createStatus);
return(ResultJson(boolModel));
}
public JsonResult MSU_GetRecord(string UserKey)
{
UsersModalModel model = mugshareusers.GetRecord(UserKey);
return(ResultJson(model));
}
