public IEnumerable <Contributor> GetContributors()
{
var contributors = new List <Contributor>();
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
connection.Open();
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' FROM Contributors c";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var contributor = new Contributor();
contributor.Id = (int)reader["Id"];
contributor.FirstName = (string)reader["FirstName"];
contributor.LastName = (string)reader["LastName"];
contributor.CellNumber = (string)reader["CellNumber"];
contributor.Date = (DateTime)reader["Date"];
contributor.AlwaysInclude = (bool)reader["AlwaysInclude"];
contributor.Balance = (decimal)reader["Balance"];
contributors.Add(contributor);
}
}
return(contributors);
}
public void AddContributor(Contributor c, decimal deposit)
{
if (string.IsNullOrEmpty(c.Name) || c.Date == null || string.IsNullOrEmpty(c.Cell))
{
return;
}
DateTime a = new DateTime(0001, 1, 1);
if (c.Date == a)
{
c.Date = DateTime.Now;
}
using var connection = new SqlConnection(_connectionString);
using var command = connection.CreateCommand();
command.CommandText = @"INSERT INTO Contributors(name,createdDate,cell,AlwaysInclude)
VALUES (@name, @date, @cell, @alwaysInclude)
SELECT SCOPE_IDENTITY()";
command.Parameters.AddWithValue("@name", c.Name);
command.Parameters.AddWithValue("@cell", c.Cell);
command.Parameters.AddWithValue("@date", c.Date);
command.Parameters.AddWithValue("@alwaysInclude", c.AlwaysInclude);
connection.Open();
int id = (int)(decimal)command.ExecuteScalar();
AddDeposit(new Deposit {
Amount = deposit, ContributorId = id, Date = c.Date
});
}
public IEnumerable <Contributor> GetContributors()
{
var contributors = new List <Contributor>();
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
connection.Open();
cmd.CommandText = "SELECT * FROM Contributors";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var contributor = new Contributor();
contributor.Id = (int)reader["Id"];
contributor.FirstName = (string)reader["FirstName"];
contributor.LastName = (string)reader["LastName"];
contributor.CellNumber = (string)reader["CellNumber"];
contributor.Date = (DateTime)reader["Date"];
contributor.AlwaysInclude = (bool)reader["AlwaysInclude"];
contributor.Balance = GetContributorBalance(contributor.Id);
contributors.Add(contributor);
}
}
return(contributors);
}
public ActionResult Edit(Contributor contributor)
{
var mgr = new SimchaFundManager(Properties.Settings.Default.ConStr);
mgr.UpdateContributor(contributor);
TempData["Message"] = "Contributor updated successfully";
return RedirectToAction("Index");
}
public void SetContributionAmountForContributorForSimcha(int simchaId, Contributor contributor)
{
var contributions = GetContributionsForSimcha(simchaId);
var contribution = contributions.FirstOrDefault(c => c.ContributorId == contributor.Id);
if (contribution != null)
{
contributor.Amount = contribution.Amount;
}
}
public void EditContributor(Contributor c)
{
using var connection = new SqlConnection(_connectionString);
using var command = connection.CreateCommand();
command.CommandText = @"UPDATE Contributors SET Name=@name, cell=@cell,alwaysInclude=@alwaysInclude,createdDate=@date WHERE id=@id";
command.Parameters.AddWithValue("@id", c.Id);
command.Parameters.AddWithValue("@alwaysInclude", c.AlwaysInclude);
command.Parameters.AddWithValue("@name", c.Name);
command.Parameters.AddWithValue("@cell", c.Cell);
command.Parameters.AddWithValue("@date", c.Date);
connection.Open();
command.ExecuteNonQuery();
}
public ActionResult New(Contributor contributor, decimal initialDeposit)
{
var mgr = new SimchaFundManager(Properties.Settings.Default.ConStr);
mgr.AddContributor(contributor);
var deposit = new Deposit
{
Amount = initialDeposit,
ContributorId = contributor.Id,
Date = contributor.Date
};
mgr.AddDeposit(deposit);
TempData["Message"] = "New Contributor Created! Id: " + contributor.Id;
return RedirectToAction("index");
}
public void AddContributor(Contributor contributor)
{
using (var sqlConnection = new SqlConnection(_connectionString))
using (var cmd = sqlConnection.CreateCommand())
{
sqlConnection.Open();
cmd.CommandText = @"INSERT INTO Contributors (FirstName, LastName, CellNumber, Date, AlwaysInclude)
VALUES (@firstName, @lastName, @cellNumber, @date, @alwaysInclude); SELECT SCOPE_IDENTITY()";
cmd.Parameters.AddWithValue("@firstName", contributor.FirstName);
cmd.Parameters.AddWithValue("@lastName", contributor.LastName);
cmd.Parameters.AddWithValue("@cellNumber", contributor.CellNumber);
cmd.Parameters.AddWithValue("@date", contributor.Date);
cmd.Parameters.AddWithValue("@alwaysInclude", contributor.AlwaysInclude);
contributor.Id = (int)(decimal)cmd.ExecuteScalar();
}
}
public void UpdateContributor(Contributor contributor)
{
using (var sqlConnection = new SqlConnection(_connectionString))
using (var cmd = sqlConnection.CreateCommand())
{
sqlConnection.Open();
cmd.CommandText = @"UPDATE Contributors SET FirstName = @firstName, LastName = @lastName, CellNumber = @cellNumber,
Date = @date, AlwaysInclude = @alwaysInclude WHERE Id = @id";
cmd.Parameters.AddWithValue("@firstName", contributor.FirstName);
cmd.Parameters.AddWithValue("@lastName", contributor.LastName);
cmd.Parameters.AddWithValue("@cellNumber", contributor.CellNumber);
cmd.Parameters.AddWithValue("@date", contributor.Date);
cmd.Parameters.AddWithValue("@alwaysInclude", contributor.AlwaysInclude);
cmd.Parameters.AddWithValue("@id", contributor.Id);
cmd.ExecuteNonQuery();
}
}
public void UpdateContributor(Contributor contributor)
{
using (var sqlConnection = new SqlConnection(_connectionString))
using (var cmd = sqlConnection.CreateCommand())
{
sqlConnection.Open();
cmd.CommandText = @"UPDATE Contributors SET FirstName = @firstName, LastName = @lastName, CellNumber = @cellNumber,
Date = @date, AlwaysInclude = @alwaysInclude WHERE Id = @id";
cmd.Parameters.AddWithValue("@firstName", contributor.FirstName);
cmd.Parameters.AddWithValue("@lastName", contributor.LastName);
cmd.Parameters.AddWithValue("@cellNumber", contributor.CellNumber);
cmd.Parameters.AddWithValue("@date", contributor.Date);
cmd.Parameters.AddWithValue("@alwaysInclude", contributor.AlwaysInclude);
cmd.Parameters.AddWithValue("@id", contributor.Id);
cmd.ExecuteNonQuery();
}
}
public void AddContributor(Contributor contributor)
{
using (var sqlConnection = new SqlConnection(_connectionString))
using (var cmd = sqlConnection.CreateCommand())
{
sqlConnection.Open();
cmd.CommandText = @"INSERT INTO Contributors (FirstName, LastName, CellNumber, Date, AlwaysInclude)
VALUES (@firstName, @lastName, @cellNumber, @date, @alwaysInclude); SELECT @@Identity";
cmd.Parameters.AddWithValue("@firstName", contributor.FirstName);
cmd.Parameters.AddWithValue("@lastName", contributor.LastName);
cmd.Parameters.AddWithValue("@cellNumber", contributor.CellNumber);
cmd.Parameters.AddWithValue("@date", contributor.Date);
cmd.Parameters.AddWithValue("@alwaysInclude", contributor.AlwaysInclude);
contributor.Id = (int)(decimal)cmd.ExecuteScalar();
}
}
public void NewContributor(Contributor contributor, decimal amount)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "insert into contributors values (@firstName, @lastName, @cell, @alwaysInclude) select scope_identity()";
cmd.Parameters.AddWithValue("@firstName", contributor.FirstName);
cmd.Parameters.AddWithValue("@lastName", contributor.LastName);
cmd.Parameters.AddWithValue("@cell", contributor.Cell);
cmd.Parameters.AddWithValue("@alwaysInclude", contributor.AlwaysInclude);
connection.Open();
int id = (int)(decimal)cmd.ExecuteScalar();
cmd.CommandText = "insert into deposits values (@id, @amount, getdate())";
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@amount", amount);
cmd.ExecuteNonQuery();
}
}
public Contributor GetContributorById(int id)
{
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT * FROM Contributors
WHERE Id = @id";
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
var contributor = new Contributor
{
FirstName = (string)reader["FirstName"],
LastName = (string)reader["LastName"],
Balance = GetBalance(id)
};
return(contributor);
}
}
public IEnumerable <Contributor> GetContributors()
{
using (SqlConnection connection = new SqlConnection(_connectionString))
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "select * from contributors";
connection.Open();
List <Contributor> results = new List <Contributor>();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Contributor contributor = new Contributor();
contributor.Id = (int)reader["Id"];
contributor.Name = (string)reader["Name"];
contributor.PhoneNumber = (string)reader["PhoneNumber"];
contributor.AlwaysInclude = (bool)reader["AlwaysInclude"];
contributor.Date = (DateTime)reader["Date"];
contributor.Balance = GetBalance(contributor.Id);
results.Add(contributor);
}
return(results);
}
}
public Contributor GetContributorById(int id)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "Select * from Contributors where Id=@contributorId";
cmd.Parameters.AddWithValue("@contributorId", id);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (!reader.Read())
{
return(null);
}
Contributor contributor = new Contributor();
contributor.Id = (int)reader["Id"];
contributor.Name = (string)reader["Name"];
contributor.PhoneNumber = (string)reader["PhoneNumber"];
contributor.AlwaysInclude = (bool)reader["AlwaysInclude"];
contributor.Date = (DateTime)reader["Date"];
contributor.Balance = GetBalance(contributor.Id);
return(contributor);
}
}
public IEnumerable<Contributor> GetContributors()
{
var contributors = new List<Contributor>();
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
connection.Open();
cmd.CommandText = "SELECT * FROM Contributors";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var contributor = new Contributor();
contributor.Id = (int)reader["Id"];
contributor.FirstName = (string)reader["FirstName"];
contributor.LastName = (string)reader["LastName"];
contributor.CellNumber = (string)reader["CellNumber"];
contributor.Date = (DateTime)reader["Date"];
contributor.AlwaysInclude = (bool)reader["AlwaysInclude"];
contributor.Balance = GetContributorBalance(contributor.Id);
contributors.Add(contributor);
}
}
return contributors;
}
