/// <summary>
/// Get a contract by an ID with a specify transaction already opened
/// </summary>
/// <param name="id">ID of contract</param>
/// <param name="transaction">The transaction to use</param>
/// <returns>The contract with this ID</returns>
public Contract GetContractById(int id, SqlTransaction transaction)
{
var contracts = new List<Contract>();
var parameters = new NameValueCollection
{
{"@id", id.ToString()},
};
var c = new Contract();
var persons = new List<Person>();
var departments = new List<Department>();
using (var reader = DBUtils.ExecuteTransactionQuery("SELECT C.id, C.title, C.start, C.[end], C.fileId, C.xmlContent, C.userLogin, C.typeContractName, C.archived " +
"FROM [Contract] C " +
"WHERE C.id = @id"
, transaction, parameters))
{
if (reader.Read())
{
c = BindContract(reader);
}
}
using (var readerAssoc = DBUtils.ExecuteTransactionQuery("SELECT A.roleName, P.id AS personId, P.name, P.firstname " +
"FROM [Association] A " +
"INNER JOIN Person P " +
"ON A.person = P.id " +
"WHERE A.contractId = @id"
, transaction, parameters))
{
while (readerAssoc.Read())
{
var p = new Person
{
Id = (int) readerAssoc["personId"],
Role = (string) readerAssoc["roleName"],
FirstName = (string) readerAssoc["firstname"],
Name = (string) readerAssoc["name"]
};
persons.Add(p);
}
}
using (var readerDestination = DBUtils.ExecuteTransactionQuery("SELECT P.id AS departementId, P.name AS depName, I.id AS institutionId, I.name AS insName" +
" FROM [Destination] D" +
" INNER JOIN [Department] P" +
" ON D.department = P.id" +
" INNER JOIN [Institution] I" +
" ON P.institutionId = I.id" +
" WHERE D.contract = @id"
, transaction, parameters))
{
while (readerDestination.Read())
{
var d = new Department
{
Id = (int) readerDestination["departementId"],
Name = (string) readerDestination["depName"],
InstitutionName = (string) readerDestination["insName"],
InstitutionId = (int) readerDestination["institutionId"]
};
departments.Add(d);
}
}
c.persons = persons;
c.departments = departments;
contracts.Add(c);
return contracts.First();
}
/// <summary>
/// Bind the SQL Result to a Person object
/// </summary>
/// <param name="result">The result of a SQL query</param>
/// <returns>a new instance of Person with the values of the SQL Result</returns>
private static Person BindPerson(SqlResult result)
{
var department = new Department
{
Id = (int) result["departmentId"],
Name = (string) result["departmentName"],
InstitutionName = (string) result["institutionName"],
InstitutionId = (int) result["institutionId"]
};
var person = new Person
{
Id = (int) result["id"],
Name = (string) result["name"],
FirstName = (string) result["firstname"],
Email = (string) result["email"],
Phone = (string) result["phone"],
Archived = (bool) result["archived"],
Department = department
};
return person;
}