/// <summary>
/// CREATOR: Ryan Morganti
/// CREATED: 2020/04/16
/// APPROVER: Derek Taylor
///
/// Access layer method to insert new recurring donation records into the database.
/// This method will create a new donorID before linking it to the RecurringDonationInfo table.
/// Then will make the first donation, tying it to the RecurringDonationInfo in the RecurringDonation table.
/// </summary>
/// <remarks>
/// UPDATER: NA
/// UPDATED: NA
/// UPDATE: NA
///
/// </remarks>
public int InsertNewRecurringDonation(RecurringDonationVM donation)
{
int donorID;
int result;
int recurringDonationID;
int donationID;
string firstName = "";
string lastName = "";
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_name_by_username", conn);
var cmd2 = new SqlCommand("sp_insert_new_donor", conn);
var cmd3 = new SqlCommand("sp_insert_new_recurring_donation_info", conn);
var cmd4 = new SqlCommand("sp_insert_new_monetary_donation", conn);
var cmd5 = new SqlCommand("sp_insert_new_recurring_donation", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd2.CommandType = CommandType.StoredProcedure;
cmd3.CommandType = CommandType.StoredProcedure;
cmd4.CommandType = CommandType.StoredProcedure;
cmd5.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", donation.UserName);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
firstName = reader.GetString(0);
lastName = reader.GetString(1);
}
}
reader.Close();
cmd2.Parameters.AddWithValue("@FirstName", firstName);
cmd2.Parameters.AddWithValue("@LastName", lastName);
donorID = Convert.ToInt32(cmd2.ExecuteScalar());
cmd3.Parameters.AddWithValue("@UserName", donation.UserName);
cmd3.Parameters.AddWithValue("@DonorID", donorID);
cmd3.Parameters.AddWithValue("@DonationAmount", donation.DonationAmount);
cmd3.Parameters.AddWithValue("@Interval", donation.Interval);
recurringDonationID = Convert.ToInt32(cmd3.ExecuteScalar());
cmd4.Parameters.AddWithValue("@DonorID", donorID);
cmd4.Parameters.AddWithValue("@TypeOfDonation", "Monetary - Recurring");
cmd4.Parameters.AddWithValue("@DonationAmount", donation.DonationAmount);
donationID = Convert.ToInt32(cmd4.ExecuteScalar());
cmd5.Parameters.AddWithValue("@RecurringDonationID", recurringDonationID);
cmd5.Parameters.AddWithValue("@DonationID", donationID);
result = cmd5.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(result);
}
public User SelectUserByEmail(string email)
{
User user = null;
// get SqlConnection object
var conn = DBConnection.GetConnection();
// commands
var cmd1 = new SqlCommand("sp_select_user_by_email", conn);
var cmd2 = new SqlCommand("sp_select_roles_by_employeeid", conn);
// command types
cmd1.CommandType = CommandType.StoredProcedure;
cmd2.CommandType = CommandType.StoredProcedure;
// parameters
cmd1.Parameters.Add("@Email", SqlDbType.NVarChar, 250);
cmd2.Parameters.Add("@EmployeeID", SqlDbType.Int);
// values for first command, second requires first results
cmd1.Parameters["@Email"].Value = email;
try
{
// open the connection
conn.Open();
var reader1 = cmd1.ExecuteReader();
user = new User();
user.Email = email;
if (reader1.Read())
{ //Read gets one row with multiple columns
user.EmployeeID = reader1.GetInt32(0); // 0 represents table column
user.FirstName = reader1.GetString(1);
user.LastName = reader1.GetString(2);
user.PhoneNumber = reader1.GetString(3);
user.Active = reader1.GetBoolean(4);
}
else
{
throw new ApplicationException("User not found.");
}
reader1.Close();
// now cmd2 needs a parameter value
cmd2.Parameters["@EmployeeID"].Value = user.EmployeeID;
var reader2 = cmd2.ExecuteReader();
List <string> roles = new List <string>();
while (reader2.Read())
{
roles.Add(reader2.GetString(0));
}
reader2.Close();
user.Roles = roles;
}
catch (Exception up)
{
throw up;
}
return(user);
}
/// <summary>
/// Creator: Jaeho Kim
/// Created: 03/19/2020
/// Approver: Rasha Mohammed
///
/// Implementation for inserting a transaction.
/// </summary>
/// <remarks>
/// Updater: Robert Holmes
/// Updated: 04/21/2020
/// Update: Added CustomerEmail and StripeChargeID.
/// </remarks>
/// <param name="transaction">The transaction that is inserted to the database.</param>
/// <returns>rows effected</returns>
public int InsertTransaction(Transaction transaction)
{
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_insert_transaction", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TransactionDateTime", transaction.TransactionDateTime);
cmd.Parameters.AddWithValue("@TaxRate", transaction.TaxRate);
cmd.Parameters.AddWithValue("@SubTotalTaxable", transaction.SubTotalTaxable);
cmd.Parameters.AddWithValue("@SubTotal", transaction.SubTotal);
cmd.Parameters.AddWithValue("@Total", transaction.Total);
cmd.Parameters.AddWithValue("@TransactionTypeID", transaction.TransactionTypeID);
cmd.Parameters.AddWithValue("@EmployeeID", transaction.EmployeeID);
cmd.Parameters.AddWithValue("@TransactionStatusID", transaction.TransactionStatusID);
if (transaction.CustomerEmail != null)
{
cmd.Parameters.AddWithValue("@CustomerEmail", transaction.CustomerEmail);
}
else
{
cmd.Parameters.AddWithValue("@CustomerEmail", DBNull.Value);
}
if (transaction.StripeChargeID != null)
{
cmd.Parameters.AddWithValue("@StripeChargeID", transaction.StripeChargeID);
}
else
{
cmd.Parameters.AddWithValue("@StripeChargeID", DBNull.Value);
}
// Tax Exemption
if (!String.IsNullOrWhiteSpace(transaction.TaxExemptNumber))
{
cmd.Parameters.AddWithValue("@TaxExemptNumber", transaction.TaxExemptNumber);
}
else
{
cmd.Parameters.AddWithValue("@TaxExemptNumber", DBNull.Value);
}
cmd.Parameters.AddWithValue("@ReturnTransactionId", 0);
try
{
conn.Open();
TransactionID = Convert.ToInt32(cmd.ExecuteScalar());
transaction.TransactionID = TransactionID;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(TransactionID);
}
public static Employee GetEmployeeByEmail(string email)
{
Employee employee = null;
var conn = DBConnection.GetDBConnection();
string cmdText1 = @"sp_get_employee_info_by_email";
string cmdText2 = @"sp_get_employee_roles";
var cmd1 = new SqlCommand(cmdText1, conn);
var cmd2 = new SqlCommand(cmdText2, conn);
// command types
cmd1.CommandType = CommandType.StoredProcedure;
cmd2.CommandType = CommandType.StoredProcedure;
// parameters
cmd1.Parameters.Add("@Email", SqlDbType.NVarChar, 255);
cmd2.Parameters.Add("@Email", SqlDbType.NVarChar, 255);
// parameter values
cmd1.Parameters["@Email"].Value = email;
cmd2.Parameters["@Email"].Value = email;
try
{
int employeeID = 0;
string firstName = null;
string lastName = null;
List <string> roles = new List <string>();
conn.Open();
SqlDataReader theReader = cmd1.ExecuteReader();
if (theReader.HasRows)
{
theReader.Read();
employeeID = theReader.GetInt32(0);
firstName = theReader.GetString(1);
lastName = theReader.GetString(2);
}
else
{
throw new ApplicationException("Employee not found.");
}
//close this reader so that other readers can run
theReader.Close();
//here is the second reader for the second command execute reader.//////////
//run the second reader
SqlDataReader theSecondReader = cmd2.ExecuteReader();
if (theSecondReader.HasRows)
{
while (theSecondReader.Read())
{
string role = theSecondReader.GetString(0);
//add roles to employee role
roles.Add(role);
}
}
theSecondReader.Close();
//group employee attributes into the employee objects
//Build the second employee object based on email
employee = new Employee(employeeID, firstName, lastName, roles);
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(employee);
}
public int UpdateProduct(Product oldProduct, Product newProduct)
{
int result = 0;
var conn = DBConnection.GetDBConnection();
var cmd = new SqlCommand("sp_update_product", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ProductID", SqlDbType.Int);
cmd.Parameters.Add("@NewProductName", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@NewVendor", SqlDbType.NVarChar, 250);
cmd.Parameters.Add("@NewProductType", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@NewBuyPrice", SqlDbType.Decimal, 19);
cmd.Parameters["@NewBuyPrice"].Precision = 19;
cmd.Parameters["@NewBuyPrice"].Scale = 4;
cmd.Parameters.Add("@NewSalePrice", SqlDbType.Decimal, 19);
cmd.Parameters["@NewSalePrice"].Precision = 19;
cmd.Parameters["@NewSalePrice"].Scale = 4;
cmd.Parameters.Add("@NewQuantity", SqlDbType.Int);
cmd.Parameters.Add("@OldProductName", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@OldVendor", SqlDbType.NVarChar, 250);
cmd.Parameters.Add("@OldProductType", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@OldBuyPrice", SqlDbType.Decimal, 19);
cmd.Parameters["@OldBuyPrice"].Precision = 19;
cmd.Parameters["@OldBuyPrice"].Scale = 4;
cmd.Parameters.Add("@OldSalePrice", SqlDbType.Decimal, 19);
cmd.Parameters["@OldSalePrice"].Precision = 19;
cmd.Parameters["@OldSalePrice"].Scale = 4;
cmd.Parameters.Add("@OldQuantity", SqlDbType.Int);
cmd.Parameters["@ProductID"].Value = oldProduct.ProductID;
cmd.Parameters["@NewProductName"].Value = newProduct.ProductName;
cmd.Parameters["@NewVendor"].Value = newProduct.Vendor;
cmd.Parameters["@NewProductType"].Value = newProduct.ProductType;
cmd.Parameters["@NewBuyPrice"].Value = newProduct.BuyPrice;
cmd.Parameters["@NewSalePrice"].Value = newProduct.SalePrice;
cmd.Parameters["@NewQuantity"].Value = newProduct.Quantity;
cmd.Parameters["@OldProductName"].Value = oldProduct.ProductName;
cmd.Parameters["@OldVendor"].Value = oldProduct.Vendor;
cmd.Parameters["@OldProductType"].Value = oldProduct.ProductType;
cmd.Parameters["@OldBuyPrice"].Value = oldProduct.BuyPrice;
cmd.Parameters["@OldSalePrice"].Value = oldProduct.SalePrice;
cmd.Parameters["@OldQuantity"].Value = oldProduct.Quantity;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(result);
}
/// <summary>
/// James Heim
/// Created 2019-04-25
///
/// Select a TabLine by the specified ID.
/// </summary>
/// <param name="memberTabLineID"></param>
/// <returns></returns>
public MemberTabLine SelectMemberTabLineByID(int memberTabLineID)
{
MemberTabLine tabLine = null;
var conn = DBConnection.GetDbConnection();
var cmdText = @"sp_select_membertabline";
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MemberTabLineID", memberTabLineID);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
tabLine = new MemberTabLine()
{
MemberTabLineID = reader.GetInt32(0),
MemberTabID = reader.GetInt32(1),
OfferingID = reader.GetInt32(2),
Quantity = reader.GetInt32(3),
Price = (decimal)reader.GetSqlMoney(4),
DatePurchased = reader.GetDateTime(8)
};
// Employee ID is nullable.
if (reader.IsDBNull(5))
{
tabLine.EmployeeID = null;
}
else
{
tabLine.EmployeeID = reader.GetInt32(5);
}
// Discount is nullable.
if (reader.IsDBNull(6))
{
tabLine.Discount = 0;
}
else
{
tabLine.Discount = reader.GetDecimal(6);
}
// GuestID is nullable.
if (reader.IsDBNull(7))
{
tabLine.GuestID = null;
}
else
{
tabLine.GuestID = reader.GetInt32(7);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(tabLine);
}
/// <summary>
/// Creator: Thomas Dupuy
/// Created: 2020/04/12
/// Approver: Michael Thompson
/// This method selects an appointment by its id
/// </summary>
/// <remarks>
/// Updater: Mohamed Elamin
/// Updated: 2020/04/20
/// Update: I Added param, returns tags for the comments.
/// And updated the date format.
/// </remarks>
/// <param name="id"></param>
/// <returns> An appointment by appointment ID</returns>
public AppointmentLocationVM SelectAppointmentByID(int id)
{
AppointmentLocationVM appointment = new AppointmentLocationVM();
var conn = DBConnection.GetConnection();
var cmd1 = new SqlCommand("sp_select_appointment_by_appointment_id");
var cmd2 = new SqlCommand("sp_select_location_by_location_id");
cmd1.Connection = conn;
cmd2.Connection = conn;
cmd1.CommandType = CommandType.StoredProcedure;
cmd2.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@AppointmentID", id);
cmd2.Parameters.Add("@LocationID", SqlDbType.Int);
try
{
conn.Open();
var reader1 = cmd1.ExecuteReader();
if (reader1.Read())
{
appointment.AppointmentID = id;
appointment.AdoptionApplicationID = reader1.GetInt32(1);
appointment.AppointmentTypeID = reader1.GetString(2);
appointment.DateTime = reader1.GetDateTime(3);
appointment.Notes = reader1.GetString(4);
appointment.Decicion = reader1.GetString(5);
appointment.LocationID = reader1.GetInt32(6);
appointment.Active = reader1.GetBoolean(7);
}
else
{
throw new ApplicationException("User not found.");
}
cmd2.Parameters["@LocationID"].Value = appointment.LocationID;
var reader2 = cmd2.ExecuteReader();
if (reader2.Read())
{
appointment.LocationName = reader2.GetString(1);
appointment.LocationAddress1 = reader2.GetString(2);
appointment.LocationAddress2 = reader2.GetString(3);
appointment.LocationCity = reader2.GetString(4);
appointment.LocationState = reader2.GetString(5);
appointment.LocationZip = reader2.GetString(6);
}
else
{
throw new ApplicationException("Location not found.");
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(appointment);
}
public Collector RetrieveCollectorByUsername(string username)
{
Collector collector = null;
// connection
var conn = DBConnection.GetDBConnection();
// command text
var cmdText = @"sp_retrieve_collector_by_email";
// command
var cmd = new SqlCommand(cmdText, conn);
// Command type
cmd.CommandType = CommandType.StoredProcedure;
// parameters
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100);
// parameter value
cmd.Parameters["@Email"].Value = username;
// try-catch for command execution
try
{
// open connection
conn.Open();
// execute the command
var reader = cmd.ExecuteReader();
// Process the results (only one result, so no while loop here)
if (reader.HasRows) // HasRows is boolean. set to true if reader has rows...
{
reader.Read(); // read the next line in the result
// create a new user object
collector = new Collector()
{
CollectorID = reader.GetString(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
PhoneNumber = reader.GetString(3),
Email = reader.GetString(4),
Active = reader.GetBoolean(5)
};
if (collector.Active != true)
{
throw new ApplicationException("Not an active collector.");
}
}
else
{
throw new ApplicationException("Collector record not found.");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(collector);
}
