public IEnumerable <SimchaContributor> GetSimchaContributorsOneQuery(int simchaId)
{
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT *,
(
(SELECT ISNULL(SUM(d.Amount), 0) FROM Deposits d WHERE d.ContributorId = c.Id)
-
(SELECT ISNULL(SUM(co.Amount), 0) FROM Contributions co WHERE co.ContributorId = c.Id)
) as 'Balance', (
SELECT Amount from Contributions WHERE SimchaId = @simchaId AND ContributorId = c.Id
) as 'Amount' FROM Contributors c";
cmd.Parameters.AddWithValue("@simchaId", simchaId);
connection.Open();
var reader = cmd.ExecuteReader();
List <SimchaContributor> result = new List <SimchaContributor>();
while (reader.Read())
{
var contributor = new SimchaContributor();
contributor.ContributorId = (int)reader["Id"];
contributor.FirstName = (string)reader["FirstName"];
contributor.LastName = (string)reader["LastName"];
contributor.AlwaysInclude = (bool)reader["AlwaysInclude"];
contributor.Balance = (decimal)reader["Balance"];
contributor.Amount = reader.GetOrNull <decimal?>("Amount");
result.Add(contributor);
}
return(result);
}
}
public IEnumerable <SimchaContributor> GetSimchaContributors(int simchaId)
{
var simchaContributors = new List <SimchaContributor>();
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT c.Id, c.FirstName, c.LastName, c.AlwaysInclude,
cb.Amount from Contributors c
LEFT JOIN Contributions cb
ON c.Id = cb.ContributorId
WHERE cb.SimchaId = @simchaId OR cb.SimchaId is NULL";
cmd.Parameters.AddWithValue("@simchaId", simchaId);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
SimchaContributor sc = new SimchaContributor();
sc.FirstName = (string)reader["FirstName"];
sc.LastName = (string)reader["LastName"];
sc.ContributorId = (int)reader["Id"];
object amount = reader["Amount"];
if (amount != DBNull.Value)
{
sc.Amount = (decimal)amount;
}
sc.AlwaysInclude = (bool)reader["AlwaysInclude"];
sc.Balance = GetContributorBalance(sc.ContributorId);
simchaContributors.Add(sc);
}
return(simchaContributors);
}
}
public IEnumerable <SimchaContributor> GetSimchaContributorsEasy(int simchaId)
{
IEnumerable <Contributor> contributors = GetContributors();
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Contributions WHERE SimchaId = @simchaId";
cmd.Parameters.AddWithValue("@simchaId", simchaId);
connection.Open();
var reader = cmd.ExecuteReader();
List <Contribution> contributions = new List <Contribution>();
while (reader.Read())
{
Contribution contribution = new Contribution
{
Amount = (decimal)reader["Amount"],
SimchaId = simchaId,
ContributorId = (int)reader["ContributorId"]
};
contributions.Add(contribution);
}
List <SimchaContributor> result = new List <SimchaContributor>();
foreach (Contributor contributor in contributors)
{
SimchaContributor sc = new SimchaContributor();
sc.FirstName = contributor.FirstName;
sc.LastName = contributor.LastName;
sc.AlwaysInclude = contributor.AlwaysInclude;
sc.ContributorId = contributor.Id;
sc.Balance = contributor.Balance;
Contribution contribution = contributions.FirstOrDefault(c => c.ContributorId == contributor.Id);
if (contribution != null)
{
sc.Amount = contribution.Amount;
}
result.Add(sc);
}
return(result);
}
}
public IEnumerable <SimchaContributor> GetSimchaContributors(int simchaId)
{
IEnumerable <Contributor> contributors = GetContributors();
using (SqlConnection connection = new SqlConnection(_connectionString))
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "Select * from contributions where SimchaId=@simchaId";
cmd.Parameters.AddWithValue("@simchaId", simchaId);
List <Contribution> contributions = new List <Contribution>();
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
contributions.Add(new Contribution
{
SimchaId = (int)reader["SimchaId"],
ContributorId = (int)reader["ContributorId"],
Amount = (decimal)reader["Amount"]
});
}
List <SimchaContributor> SimchaContributors = new List <SimchaContributor>();
foreach (Contributor c in contributors)
{
SimchaContributor sc = new SimchaContributor();
sc.ContributorId = c.Id;
sc.Name = c.Name;
sc.AlwaysInclude = c.AlwaysInclude;
sc.Balance = GetBalance(c.Id);
Contribution contribution = contributions.FirstOrDefault(contrib => contrib.ContributorId == c.Id);
if (contribution != null)
{
sc.Amount = contribution.Amount;
}
SimchaContributors.Add(sc);
}
return(SimchaContributors);
}
}
public IEnumerable <SimchaContributor> GetSimchaContributors()
{
using (var con = new SqlConnection(_connectionString))
using (var cmd = con.CreateCommand())
{
cmd.CommandText = @"select * from Contributors";
List <SimchaContributor> list = new List <SimchaContributor>();
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SimchaContributor c = new SimchaContributor();
c.ContributorId = (int)reader["Id"];
c.FirstName = (string)reader["FirstName"];
c.LastName = (string)reader["LastName"];
c.AlwaysInclude = (bool)reader["AlwaysInclude"];
c.Balance = CurrentBalance(c.ContributorId);
list.Add(c);
}
return(list);
}
}
public IEnumerable <SimchaContributor> GetSimchaContributors(int simchaId)
{
IEnumerable <Contributors> contributors = GetContributors();
var connection = new SqlConnection(_connectionString);
var cmd = connection.CreateCommand();
connection.Open();
cmd.CommandText = "SELECT * FROM Contributors WHERE Id = @id";
cmd.Parameters.AddWithValue("@id", simchaId);
var reader = cmd.ExecuteReader();
List <Contributions> contributions = new List <Contributions>();
while (reader.Read())
{
Contributions contribution = new Contributions
{
Amount = (decimal)reader["Amount"],
SimchaId = simchaId,
ContributorId = (int)reader["ContributorId"]
};
contributions.Add(contribution);
}
return(contributors.Select(contributor =>
{
var sc = new SimchaContributor();
sc.FirstName = contributor.FirstName;
sc.LastName = contributor.LastName;
sc.AlwaysInclude = contributor.AlwaysInclude;
sc.ContributorId = contributor.Id;
sc.Balance = contributor.Balance;
Contributions contribution = contributions.FirstOrDefault(c => c.ContributorId == contributor.Id);
if (contribution != null)
{
sc.Amount = contribution.Amount;
}
return sc;
}));
}
public IEnumerable<SimchaContributor> GetSimchaContributorsEasy(int simchaId)
{
IEnumerable<Contributor> contributors = GetContributors();
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Contributions WHERE SimchaId = @simchaId";
cmd.Parameters.AddWithValue("@simchaId", simchaId);
connection.Open();
var reader = cmd.ExecuteReader();
List<Contribution> contributions = new List<Contribution>();
while (reader.Read())
{
Contribution contribution = new Contribution
{
Amount = (decimal)reader["Amount"],
SimchaId = simchaId,
ContributorId = (int)reader["ContributorId"]
};
contributions.Add(contribution);
}
List<SimchaContributor> result = new List<SimchaContributor>();
foreach (Contributor contributor in contributors)
{
SimchaContributor sc = new SimchaContributor();
sc.FirstName = contributor.FirstName;
sc.LastName = contributor.LastName;
sc.AlwaysInclude = contributor.AlwaysInclude;
sc.ContributorId = contributor.Id;
sc.Balance = contributor.Balance;
Contribution contribution = contributions.FirstOrDefault(c => c.ContributorId == contributor.Id);
if (contribution != null)
{
sc.Amount = contribution.Amount;
}
result.Add(sc);
}
return result;
}
}
public IEnumerable<SimchaContributor> GetSimchaContributors(int simchaId)
{
var simchaContributors = new List<SimchaContributor>();
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT c.Id, c.FirstName, c.LastName, c.AlwaysInclude,
cb.Amount from Contributors c
LEFT JOIN Contributions cb
ON c.Id = cb.ContributorId
WHERE cb.SimchaId = @simchaId OR cb.SimchaId is NULL";
cmd.Parameters.AddWithValue("@simchaId", simchaId);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
SimchaContributor sc = new SimchaContributor();
sc.FirstName = (string)reader["FirstName"];
sc.LastName = (string)reader["LastName"];
sc.ContributorId = (int)reader["Id"];
object amount = reader["Amount"];
if (amount != DBNull.Value)
{
sc.Amount = (decimal)amount;
}
sc.AlwaysInclude = (bool)reader["AlwaysInclude"];
sc.Balance = GetContributorBalance(sc.ContributorId);
simchaContributors.Add(sc);
}
return simchaContributors;
}
}
