public IEnumerable <Contribution> GetContributionsForContributor(int ContributorId)
{
using (var connection = new SqlConnection(_connectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Contributors c JOIN SimchaContributor sc ON sc.ContributorId = c.id JOIN Simchas s ON s.id = sc.SimchaId WHERE c.id = @Id";
command.Parameters.AddWithValue("@Id", ContributorId);
connection.Open();
List <Contribution> result = new List <Contribution>();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Contribution c = new Contribution
{
Amount = (int)(decimal)reader["Amount"],
ContributorId = (int)reader["ContributorId"],
SimchaId = (int)reader["SimchaId"],
Name = (string)reader["Name"],
Date = (DateTime)reader["Date"]
};
result.Add(c);
}
return(result);
}
}
public IEnumerable <SimchaContributor> GetContributionsForSimcha(int simchaId)
{
var contributors = GetContributors();
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM SimchaContributors WHERE SimchaId = @id";
cmd.Parameters.AddWithValue("@id", simchaId);
List <Contribution> contributions = new List <Contribution>();
conn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
contributions.Add(new Contribution
{
ContributorId = (int)reader["ContributorId"],
SimchaId = simchaId,
Amount = (decimal)reader["AmountContributing"],
Date = (DateTime)reader["Date"]
});
}
return(contributors.Select(contributor =>
{
var sc = new SimchaContributor();
sc.ContributorId = contributor.Id;
sc.FirstName = contributor.FirstName;
sc.LastName = contributor.LastName;
sc.AlwaysInclude = contributor.AlwaysInclude;
sc.Balance = contributor.Balance;
Contribution contribution = contributions.FirstOrDefault(c => c.ContributorId == contributor.Id);
if (contribution != null)
{
sc.Amount = contribution.Amount;
}
return sc;
}));
}
}