/// <summary>
/// Get Specific person
/// </summary>
/// <param name="personId"></param>
/// <param name="connection"></param>
/// <returns></returns>
public static Person GetPersonByPersonId(Guid personId, SqlConnection connection)
{
List<Person> list = new List<Person>();
try
{
SqlDataReader rdr = null;
connection.Open();
// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand(
"JOB_GetPersonByPersonID", connection);
// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which
//// will be passed to the stored procedure
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.UniqueIdentifier;
parameter.ParameterName = "@PersonID";
parameter.Value = personId;
cmd.Parameters.Add(parameter);
// execute the command
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Person person = new Person
(
new Guid(rdr.GetValue(0).ToString()),
rdr.GetValue(1).ToString(),
rdr.GetValue(2).ToString(),
rdr.GetValue(3).ToString(),
rdr.GetValue(4).ToString(),
true,
Convert.ToBoolean(rdr.GetValue(5).ToString())
);
list.Add(person);
}
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return list[0];
}
/// <summary>
/// Update Current Person with new values
/// </summary>
/// <param name="person"></param>
/// <param name="connection"></param>
public static void UpdatePerson(Person person, SqlConnection connection)
{
try
{
connection.Open();
// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand(
"JOB_UpdatePerson", connection);
// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which
// will be passed to the stored procedure
SetParametersForPerson(cmd, person);
// execute the command
cmd.ExecuteReader();
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// Get Personal that is busy for a specific date
/// </summary>
/// <param name="dateTime"></param>
/// <param name="connection"></param>
/// <returns></returns>
public static List<Person> GetBusyPersonalListForDate(DateTime dateTime, SqlConnection connection)
{
var list = new List<Person>();
try
{
SqlDataReader rdr = null;
connection.Open();
// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand(
"JOB_JobBusyPersonalByDate", connection);
// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which
// will be passed to the stored procedure
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Date;
parameter.ParameterName = "@JobStartDate";
parameter.Value = dateTime.ToShortDateString();
cmd.Parameters.Add(parameter);
// execute the command
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Person person = new Person
(
new Guid(rdr.GetValue(0).ToString()),
rdr.GetValue(1).ToString(),
rdr.GetValue(2).ToString(),
rdr.GetValue(3).ToString(),
rdr.GetValue(4).ToString(),
Convert.ToBoolean(rdr.GetValue(5).ToString()),
true
);
// Add To list
list.Add(person);
}
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return list;
}
private static void SetParametersForPerson(SqlCommand cmd, Person person)
{
cmd.Parameters.Add(new SqlParameter("@PersonID", person.PersonId));
cmd.Parameters.Add(new SqlParameter("@FirstName", person.Firstname));
cmd.Parameters.Add(new SqlParameter("@LastName", person.Lastname));
cmd.Parameters.Add(new SqlParameter("@Mobile", person.Mobile));
cmd.Parameters.Add(new SqlParameter("@Email", person.Email));
cmd.Parameters.Add(new SqlParameter("@Active", person.Active));
}
public static void SetPersonInactive(Person person)
{
person.Active = false;
DataLayer.UpdatePerson(person, sqlConnection);
}
public static Person AddPerson(MamutV2.MamutEmployee personMamut)
{
var person = GetPersonByName(personMamut.FirstName.Trim(), personMamut.LastName.Trim());
if (person != null)
{
Person newperson = new Person(person.PersonId, personMamut.FirstName.Trim(), personMamut.LastName.Trim(), personMamut.Phone.Trim(), string.Empty, true, person.Active);
DataLayer.UpdatePerson(newperson, sqlConnection);
return newperson;
}
else
{
Person newperson = new Person(Guid.NewGuid(), personMamut.FirstName.Trim(), personMamut.LastName.Trim(), personMamut.Phone.Trim(), string.Empty, true, true);
DataLayer.CreatePerson(newperson, sqlConnection);
return newperson;
}
}
public static Person AddPerson(PersonMamut personMamut)
{
var person = GetPersonByName(personMamut.FirstName, personMamut.LastName);
if (person != null)
{
Person newperson = new Person(person.PersonId, personMamut.FirstName, personMamut.LastName, personMamut.Mobile, personMamut.Email, true, person.Active);
DataLayer.UpdatePerson(newperson, sqlConnection);
return newperson;
}
else
{
Person newperson = new Person(Guid.NewGuid(), personMamut.FirstName, personMamut.LastName, personMamut.Mobile, personMamut.Email, true, true);
DataLayer.CreatePerson(newperson, sqlConnection);
return newperson;
}
}
