/// <summary>
/// Get a person with a given username
/// </summary>
/// <param name="username">the person's username</param>
/// <returns>the person</returns>
public static Person GetPerson(string username)
{
Person person = null;
try
{
SqlConnection db = new SqlConnection(ConnectionManager.GetUnauthentifiedConnectionString());
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
try
{
SqlCommand cmd = new SqlCommand("SELECT P.id_person as id_person, P.email, P.firstname, P.lastname, P.username, P.pType AS pType, P.timestamp " +
"FROM [Person] P WHERE P.username = @username; ", db, transaction);
cmd.Parameters.Add("@username", SqlDbType.Char).Value = username;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
string firstname = rdr.GetString(rdr.GetOrdinal("firstname"));
string lastname = rdr.GetString(rdr.GetOrdinal("lastname"));
string email = rdr.GetString(rdr.GetOrdinal("email"));
int id_person = rdr.GetInt32(rdr.GetOrdinal("id_person"));
string dbType = rdr.GetString(rdr.GetOrdinal("pType"));
person = new Person(Person.dbTypes[dbType], id_person, firstname, lastname, username, email, "");
byte[] buffer = new byte[100];
rdr.GetBytes(rdr.GetOrdinal("timestamp"), 0, buffer, 0, 100);
person.setTimestamp(buffer);
}
rdr.Close();
transaction.Commit();
}
catch (SqlException sqlError)
{
System.Diagnostics.Debug.WriteLine(sqlError.Message);
System.Diagnostics.Debug.WriteLine(sqlError.StackTrace);
transaction.Rollback();
throw new GrException(sqlError, Messages.errProd);
}
db.Close();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
throw (ex is GrException) ? ex : new GrException(ex, Messages.errProd);
}
return person;
}
/// <summary>
/// Gets the list of all Persons
/// </summary>
/// <returns>the list of all Persons</returns>
public static List<Person> ListPerson()
{
List<Person> list = new List<Person>();
try
{
SqlConnection db = new SqlConnection(ConnectionManager.GetUnauthentifiedConnectionString());
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
SqlCommand cmd = new SqlCommand("SELECT * from Person ORDER BY firstname;", db, transaction);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
string firstname = rdr.GetString(rdr.GetOrdinal("firstname"));
string lastname = rdr.GetString(rdr.GetOrdinal("lastname"));
string email = rdr.GetString(rdr.GetOrdinal("email"));
string username = rdr.GetString(rdr.GetOrdinal("username"));
int id_person = rdr.GetInt32(rdr.GetOrdinal("id_person"));
PersonType personType = PersonType.User;
switch (rdr.GetString(rdr.GetOrdinal("pType")))
{
case "RM":
personType = PersonType.ResourceManager;
break;
case "RE":
personType = PersonType.Responsible;
break;
case "US":
personType = PersonType.User;
break;
}
Person person = new Person(personType, id_person, firstname, lastname, username, email, "");
list.Add(person);
}
rdr.Close();
transaction.Commit();
}
catch (SqlException sqlException)
{
transaction.Rollback();
System.Diagnostics.Debug.WriteLine(sqlException.Message);
System.Diagnostics.Debug.WriteLine(sqlException.StackTrace);
throw new GrException(sqlException, Messages.errProd);
}
db.Close();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
throw (ex is GrException) ? ex : new GrException(ex, Messages.errProd);
}
return list;
}
/// <summary>
/// Gets the list of all the ResourceManagers
/// </summary>
/// <returns>list of ResourceManagers</returns>
public static List<Person> GetResourceManagers()
{
List<Person> list = new List<Person>();
try
{
SqlConnection db = new SqlConnection(ConnectionManager.GetConnectionString());
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
SqlCommand cmd = new SqlCommand("SELECT RM.id_person as id_person, RM.firstname, RM.lastname, RM.email, RM.username, RM.password, RM.timestamp " +
"FROM ResourceManager RM " +
"ORDER BY RM.lastname;", db, transaction);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
string firstname = rdr.GetString(rdr.GetOrdinal("firstname"));
string lastname = rdr.GetString(rdr.GetOrdinal("lastname"));
string email = rdr.GetString(rdr.GetOrdinal("email"));
string username = rdr.GetString(rdr.GetOrdinal("username"));
string password = rdr.GetString(rdr.GetOrdinal("password"));
int id_person = rdr.GetInt32(rdr.GetOrdinal("id_person"));
Person person = new Person(PersonType.Responsible, id_person, firstname, lastname, username, email, password);
list.Add(person);
}
rdr.Close();
transaction.Commit();
}
catch (SqlException sqlError)
{
System.Diagnostics.Debug.WriteLine(sqlError.Message);
System.Diagnostics.Debug.WriteLine(sqlError.StackTrace);
transaction.Rollback();
throw new GrException(sqlError, Messages.errProd);
}
db.Close();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
throw (ex is GrException) ? ex : new GrException(ex, Messages.errProd);
}
return list;
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="person">person object of "Person" type</param>
public Person2(Person person) {
pType = person.pType;
ID = person.ID;
FirstName = person.FirstName;
LastName = person.LastName;
Email = person.Email;
Password = person.Password;
Username = person.Username;
Timestamp = person.Timestamp;
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////
// CD: XML methods - should always be called from Reservation C(R)UD methods in other models (User?)
////////////////////////////////////////////////////////////////////////////////////////////////////////////
private String BuildScriptDataXML(Person person, Slot slot, List<string> machines)
{
// create document
XmlDocument doc = new XmlDocument();
// create root <command> node
XmlNode commandNode = doc.CreateElement("command");
doc.AppendChild(commandNode);
// create <username> child node and add to <command> node
XmlNode usernameNode = doc.CreateElement("username");
XmlText usernameTextNode = doc.CreateTextNode(escXML(person.Username));
usernameNode.AppendChild(usernameTextNode);
commandNode.AppendChild(usernameNode);
// create <startTime> child node and add to <command> node
XmlNode startTimeNode = doc.CreateElement("startTime");
XmlAttribute minutesAttribute = doc.CreateAttribute("minutes");
minutesAttribute.Value = escXML(slot.Start.ToString("mm"));
XmlAttribute hoursAttribute = doc.CreateAttribute("hours");
hoursAttribute.Value = escXML(slot.Start.ToString("hh"));
startTimeNode.Attributes.Append(hoursAttribute);
startTimeNode.Attributes.Append(minutesAttribute);
commandNode.AppendChild(startTimeNode);
// create <startDate> child node and add to <command> node
XmlNode startDateNode = doc.CreateElement("startDate");
string startDate = String.Format("{0:d.M.yyyy}", slot.Start);
startDateNode.AppendChild(doc.CreateTextNode(startDate)); // skipping escXML() here
commandNode.AppendChild(startDateNode);
// create <machines> node and <machine> child nodes and add to <command> node
XmlNode machinesNode = doc.CreateElement("machines");
machines.ForEach(delegate(string machine)
{
XmlNode machineNode = doc.CreateElement("machine");
XmlNode nameNode = doc.CreateElement("name");
nameNode.AppendChild(doc.CreateTextNode(escXML(machine)));
machineNode.AppendChild(nameNode);
machinesNode.AppendChild(machineNode);
});
commandNode.AppendChild(machinesNode);
// return a XML string representation of the <command> node
StringWriter sw = new StringWriter();
XmlTextWriter xw = new XmlTextWriter(sw);
doc.WriteTo(xw);
return sw.ToString();
}
// CD: this is really an Update
public void InsertCommandXML(Person person, Slot slot, List<string> machines, SqlConnection db, SqlTransaction transaction)
{
if (machines == null)
{
machines = new List<string>();
}
byte[] timestamp = this.getByteTimestamp();
SqlCommand cmd = new SqlCommand("SELECT * FROM SlotRange R " +
"WHERE R.[id_slotRange]=@id AND R.timestamp=@timestamp;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = this.id_slotRange;
cmd.Parameters.Add("@timestamp", SqlDbType.Binary).Value = timestamp;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
rdr.Close();
// before inserting, delete any previously existing reservations
cmd = new SqlCommand("UPDATE SlotRange " +
"SET scriptDataXML.modify('delete (/script/command[username=sql:variable(\"@username\")])') " +
"WHERE id_slotRange=@id_slotRange", db, transaction);
cmd.Parameters.Add("@username", SqlDbType.Char).Value = person.Username;
cmd.Parameters.Add("@id_slotRange", SqlDbType.Int).Value = this.id_slotRange;
cmd.ExecuteNonQuery();
string xml_string = BuildScriptDataXML(person, slot, machines);
cmd = new SqlCommand("UPDATE SlotRange " +
"SET scriptDataXML.modify('insert sql:variable(\"@xml_string\") as last into (/script)[1]') " +
"WHERE id_slotRange = @id_slotRange ", db, transaction);
cmd.Parameters.Add("@id_slotRange", SqlDbType.Int).Value = this.id_slotRange;
cmd.Parameters.Add("@xml_string", SqlDbType.Xml).Value = xml_string;
cmd.ExecuteNonQuery();
//transaction.Commit();
}
else
{
rdr.Close();
throw new GrException(Messages.recommencerEdit);
}
}
