public List <Relative> GetRelationsIDs(string studUserId, string newstudId, string userId)
{
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
MySqlCommand cmd;
MySqlDataReader dr = null;
string query = "SELECT studentrelations.relauserid FROM studentrelations WHERE studentrelations.studuserid = @studuserid AND studentrelations.relauserid NOT IN (SELECT studentrelations.relauserid FROM studentregistration WHERE studentrelations.studuserid = @newstudID)";
List <Relative> relList = new List <Relative>();
Relative rel;
try
{
con.Open();
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@studuserid", studUserId);
cmd.Parameters.AddWithValue("@newstudID", newstudId);
dr = cmd.ExecuteReader();
//new AuditLogService().AddAuditLog("LOADING RELATIVES BY STUDENT ID", userId, new UserService().GetUserName(userId), query, DateTime.Now);
if (dr.HasRows)
{
while (dr.Read())
{
rel = new Relative();
rel.RelaUserId = dr.GetString(0);
relList.Add(rel);
}
}
}
catch (MySqlException ex)
{
//new AuditLogService().AddAuditLog("ERROR LOADING RELATIVE BY STUDENT ID", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
catch (Exception ex)
{
//new AuditLogService().AddAuditLog("ERROR LOADING RELATIVE BY STUDENT ID", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
finally
{
dr.Close();
con.Close();
}
return(relList);
}
public bool AddRelative(Relative rel, string userId)
{
Boolean retVal = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
MySqlCommand cmd;
string query = "INSERT INTO `studentrelatives`(`userId`, `relType`, `firstName`, `lastName`, `otherName`, `mobile`, `tel`, `email`, `postAddress`, `nextOfKin`, `dateCreated`, `lastModified`) VALUES (@userId,@relType,@firstName,@lastName,@otherName,@mobile,@tel,@email,@postAddress,@nextOfKin,@dateCreated,@lastModified)";
try
{
con.Open();
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@userId", rel.RelaUserId);
cmd.Parameters.AddWithValue("@relType", rel.RelType);
cmd.Parameters.AddWithValue("@firstName", rel.FirstName);
cmd.Parameters.AddWithValue("@lastName", rel.LastName);
cmd.Parameters.AddWithValue("@otherName", rel.OtherName);
cmd.Parameters.AddWithValue("@mobile", rel.Mobile);
cmd.Parameters.AddWithValue("@tel", rel.Tel);
cmd.Parameters.AddWithValue("@email", rel.Email);
cmd.Parameters.AddWithValue("@postAddress", rel.PostAddress);
cmd.Parameters.AddWithValue("@nextOfKin", rel.NextOfKin);
cmd.Parameters.AddWithValue("@dateCreated", rel.DateCreated);
cmd.Parameters.AddWithValue("@lastModified", rel.LastModified);
int afRow = cmd.ExecuteNonQuery();
new AuditLogService().AddAuditLog("ADDING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
if (afRow > 0)
{
retVal = true;
}
}
catch (MySqlException ex)
{
new AuditLogService().AddAuditLog("ERROR ADDING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
catch (Exception ex)
{
new AuditLogService().AddAuditLog("ERROR ADDING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
finally
{
con.Close();
}
return(retVal);
}
public bool UpdateRelative(Relative rel, string userId)
{
Boolean retVal = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
MySqlCommand cmd;
string query = "UPDATE `studentrelatives` SET `userId`=@userId,`relType`=@relType,`firstName`=@firstName,`lastName`=@lastName,`otherName`=@otherName,`mobile`=@mobile,`tel`=@tel,`email`=@email,`postAddress`=@postAddress,`nextOfKin`=@nextOfKin,`lastModified`=@lastModified WHERE id=@id";
try
{
con.Open();
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@userId", rel.RelaUserId);
cmd.Parameters.AddWithValue("@relType", rel.RelType);
cmd.Parameters.AddWithValue("@firstName", rel.FirstName);
cmd.Parameters.AddWithValue("@lastName", rel.LastName);
cmd.Parameters.AddWithValue("@otherName", rel.OtherName);
cmd.Parameters.AddWithValue("@mobile", rel.Mobile);
cmd.Parameters.AddWithValue("@tel", rel.Tel);
cmd.Parameters.AddWithValue("@email", rel.Email);
cmd.Parameters.AddWithValue("@postAddress", rel.PostAddress);
cmd.Parameters.AddWithValue("@nextOfKin", rel.NextOfKin);
cmd.Parameters.AddWithValue("@lastModified", rel.LastModified);
cmd.Parameters.AddWithValue("@id", rel.Id);
int affecRow = cmd.ExecuteNonQuery();
new AuditLogService().AddAuditLog("UPDATING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
if (affecRow > 0)
{
retVal = true;
}
}
catch (MySqlException ex)
{
new AuditLogService().AddAuditLog("ERROR UPDATING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
catch (Exception ex)
{
new AuditLogService().AddAuditLog("ERROR UPDATING RELATIVE ", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
finally
{ con.Close(); }
return(retVal);
}
public bool AddRelation(Relative rel, string userId)
{
Boolean retVal = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
MySqlCommand cmd;
string query = "INSERT INTO `studentrelations`(`studuserid`, `relauserid`, `dateCreated`, `lastModified`) VALUES (@studuserid,@relauserid,@dateCreated,@lastModified)";
try
{
con.Open();
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@studuserid", rel.xStudUserId);
cmd.Parameters.AddWithValue("@relauserid", rel.RelaUserId);
cmd.Parameters.AddWithValue("@dateCreated", rel.DateCreated);
cmd.Parameters.AddWithValue("@lastModified", rel.LastModified);
int afRow = cmd.ExecuteNonQuery();
//new AuditLogService().AddAuditLog("ADDING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
if (afRow > 0)
{
retVal = true;
}
}
catch (MySqlException ex)
{
//new AuditLogService().AddAuditLog("ERROR ADDING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
catch (Exception ex)
{
//new AuditLogService().AddAuditLog("ERROR ADDING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
finally
{
con.Close();
}
return(retVal);
}
public List <Relative> GetAllRelativesByType(string type, string userId)
{
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
MySqlCommand cmd;
MySqlDataReader dr = null;
string query = "SELECT `id`, `userId`, `relType`, `firstName`, `lastName`, `otherName`, `mobile`, `tel`, `email`, `postAddress`, `nextOfKin`, `active`, `dateCreated`, `lastModified` FROM `studentrelatives` WHERE relType=@relType";
List <Relative> relList = new List <Relative>();
Relative rel;
try
{
con.Open();
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@relType", type);
dr = cmd.ExecuteReader();
new AuditLogService().AddAuditLog("LOADING RELATIVES BY TYPE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
if (dr.HasRows)
{
while (dr.Read())
{
rel = new Relative();
rel.Id = dr.GetInt32(0);
rel.RelaUserId = dr.GetString(1);
rel.RelType = dr.GetString(2);
rel.FirstName = dr.GetString(3);
rel.LastName = dr.GetString(4);
rel.OtherName = dr.GetString(5);
rel.Mobile = dr.GetString(6);
rel.Tel = dr.GetString(7);
rel.Email = dr.GetString(8);
rel.PostAddress = dr.GetString(9);
rel.NextOfKin = dr.GetString(10);
rel.Active = dr.GetInt32(11);
rel.DateCreated = dr.GetDateTime(12);
rel.LastModified = dr.GetDateTime(13);
rel.xFullName = dr.GetString(3) + " " + dr.GetString(5) + " " + dr.GetString(4);
relList.Add(rel);
}
}
}
catch (MySqlException ex)
{
new AuditLogService().AddAuditLog("ERROR LOADING RELATIVE BY TYPE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
catch (Exception ex)
{
new AuditLogService().AddAuditLog("ERROR LOADING RELATIVE BY TYPE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
finally
{
dr.Close();
con.Close();
}
return(relList);
}
public List <Relative> GetRelatives(string studUserId, string userId)
{
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
MySqlCommand cmd;
MySqlDataReader dr = null;
string query = "SELECT studentrelations.id,studentrelations.studuserid,studentrelations.relauserid,studentrelations.dateCreated,studentrelations.lastModified,studentrelatives.relType,studentrelatives.firstName,studentrelatives.lastName,studentrelatives.otherName,studentrelatives.mobile,studentrelatives.tel,studentrelatives.email,studentrelatives.postAddress,studentrelatives.nextOfKin,studentrelatives.active FROM studentrelations INNER JOIN studentrelatives ON studentrelations.relauserid = studentrelatives.userId WHERE studentrelations.studuserid = @studuserid";
List <Relative> relList = new List <Relative>();
Relative rel;
try
{
con.Open();
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@studuserid", studUserId);
dr = cmd.ExecuteReader();
new AuditLogService().AddAuditLog("LOADING RELATIVES BY STUDENT ID", userId, new UserService().GetUserName(userId), query, DateTime.Now);
if (dr.HasRows)
{
while (dr.Read())
{
rel = new Relative();
rel.Id = dr.GetInt32(0);
rel.xStudUserId = dr.GetString(1);
rel.RelaUserId = dr.GetString(2);
rel.DateCreated = dr.GetDateTime(3);
rel.LastModified = dr.GetDateTime(4);
rel.RelType = dr.GetString(5);
rel.FirstName = dr.GetString(6);
rel.LastName = dr.GetString(7);
rel.OtherName = dr.GetString(8);
rel.Mobile = dr.GetString(9);
rel.Tel = dr.GetString(10);
rel.Email = dr.GetString(11);
rel.PostAddress = dr.GetString(12);
rel.NextOfKin = dr.GetString(13);
rel.Active = dr.GetInt32(14);
rel.xFullName = dr.GetString(6) + " " + dr.GetString(7) + " " + dr.GetString(8);
relList.Add(rel);
}
}
}
catch (MySqlException ex)
{
new AuditLogService().AddAuditLog("ERROR LOADING RELATIVE BY STUDENT ID", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
catch (Exception ex)
{
new AuditLogService().AddAuditLog("ERROR LOADING RELATIVE BY STUDENT ID", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
finally
{
dr.Close();
con.Close();
}
return(relList);
}
