public Contributor GetContributor(int contributorId)
{
SqlConnection connection = new SqlConnection(_connectionString);
SqlCommand command = connection.CreateCommand();
command.CommandText = @"SELECT c.*, SUM(d.Amount) AS TotalDeposits, SUM(co.Amount) As TotalContributions
FROM Contributors c
LEFT JOIN Deposits d
ON c.Id = d.ContributorId
LEFT JOIN Contributions co
ON c.Id = co.ContributorId
WHERE c.id = @id
GROUP BY c.AlwaysInclude, c.Cell, c.FirstName, c.Id, c.LastName";
command.Parameters.AddWithValue("@id", contributorId);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
reader.Read();
decimal totalDeposits = (reader["TotalDeposits"] == DBNull.Value) ? 0 : (decimal)reader["TotalDeposits"];
decimal totalContributions = (reader["TotalContributions"] == DBNull.Value) ? 0 : (decimal)reader["TotalContributions"];
Contributor c = new Contributor
{
Id = (int)reader["Id"],
FirstName = (string)reader["FirstName"],
LastName = (string)reader["LastName"],
Cell = (reader["Cell"] == DBNull.Value) ? null : (string)reader["Cell"],
balance = totalDeposits - totalContributions,
AlwaysInclude = (bool)reader["AlwaysInclude"]
};
return(c);
}
public int AddContributor(Contributor contributor)
{
SqlConnection connection = new SqlConnection(_connectionString);
SqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO Contributors VALUES(@firstName, @lastName, @cell, @alwaysInclude) SELECT @@IDENTITY ";
object value = contributor.Cell;
if (value == null)
{
value = DBNull.Value;
}
command.Parameters.AddWithValue("@firstName", contributor.FirstName);
command.Parameters.AddWithValue("@lastName", contributor.LastName);
command.Parameters.AddWithValue("@cell", value);
command.Parameters.AddWithValue("@alwaysInclude", contributor.AlwaysInclude);
connection.Open();
return(int.Parse(command.ExecuteScalar().ToString()));
}
public IEnumerable <Contributor> GetContributors(int?SimchaId)
{
SqlConnection connection = new SqlConnection(_connectionString);
SqlCommand command = connection.CreateCommand();
command.CommandText = @"SELECT c.*, SUM(d.Amount) AS TotalDeposits, SUM(co.Amount) As TotalContributions
FROM Contributors c
LEFT JOIN Deposits d
ON c.Id = d.ContributorId
LEFT JOIN Contributions co
ON c.Id = co.ContributorId
GROUP BY c.AlwaysInclude, c.Cell, c.FirstName, c.Id, c.LastName";
connection.Open();
if (SimchaId != null)
{
command.CommandText = command.CommandText.Insert(386, " WHERE co.SimchaId = @id");
command.Parameters.AddWithValue("@id", SimchaId);
}
SqlDataReader reader = command.ExecuteReader();
List <Contributor> contributors = new List <Contributor>();
while (reader.Read())
{
decimal totalDeposits = (reader["TotalDeposits"] == DBNull.Value) ? 0 : (decimal)reader["TotalDeposits"];
decimal totalContributions = (reader["TotalContributions"] == DBNull.Value) ? 0 : (decimal)reader["TotalContributions"];
Contributor c = new Contributor
{
Id = (int)reader["Id"],
FirstName = (string)reader["FirstName"],
LastName = (string)reader["LastName"],
Cell = (reader["Cell"] == DBNull.Value) ? null : (string)reader["Cell"],
balance = totalDeposits - totalContributions,
AlwaysInclude = (bool)reader["AlwaysInclude"]
};
contributors.Add(c);
}
return(contributors);
}