/// <summary>
/// Creator: Brandyn T. Coverdill
/// Created: 2020/04/16
/// Approver: Kaleb Bachert
/// Approver: Jesse Tomash
///
/// Method that updates a vendor.
/// </summary>
///
/// <remarks>
/// Updated By:
/// Updated:
/// Update:
/// </remarks>
public int updateVendor(int vendorId, string oldVendorName, string oldVendorAddress, string oldVendorPhone, string oldVendorEmail, string oldVendorState, string oldVendorCity, string oldVendorZip, string newVendorName, string newVendorAddress, string newVendorPhone, string newVendorEmail, string newVendorState, string newVendorCity, string newVendorZip)
{
int result = 0;
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_update_vendor", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@VendorID", vendorId);
cmd.Parameters.AddWithValue("@OldVendorName", oldVendorName);
cmd.Parameters.AddWithValue("@OldVendorAddress", oldVendorAddress);
cmd.Parameters.AddWithValue("@OldVendorPhone", oldVendorPhone);
cmd.Parameters.AddWithValue("@OldVendorEmail", oldVendorEmail);
cmd.Parameters.AddWithValue("@OldVendorState", oldVendorState);
cmd.Parameters.AddWithValue("@OldVendorCity", oldVendorCity);
cmd.Parameters.AddWithValue("@OldVendorZip", oldVendorZip);
cmd.Parameters.AddWithValue("@NewVendorName", newVendorName);
cmd.Parameters.AddWithValue("@NewVendorAddress", newVendorAddress);
cmd.Parameters.AddWithValue("@NewVendorPhone", newVendorPhone);
cmd.Parameters.AddWithValue("@NewVendorEmail", newVendorEmail);
cmd.Parameters.AddWithValue("@NewVendorState", newVendorState);
cmd.Parameters.AddWithValue("@NewVendorCity", newVendorCity);
cmd.Parameters.AddWithValue("@NewVendorZip", newVendorZip);
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(result);
}
/// <summary>
/// Creator: Ben Hanna
/// Created: 4/7/2020
/// Approver: Carl Davis 4/10/2020
///
/// Gets all kennel records and returns them to the up layers
/// </summary>
/// <remarks>
/// Updater:
/// Updated:
/// Update:
///
/// </remarks>
/// <returns> List of all kennel cleaning records in the DB<</returns>
public List <AnimalKennelCleaningRecord> SelectAllKennelCleaningRecords()
{
List <AnimalKennelCleaningRecord> cleaningRecords = new List <AnimalKennelCleaningRecord>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_all_kennel_cleaning_records");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var record = new AnimalKennelCleaningRecord()
{
FacilityKennelCleaningID = reader.GetInt32(0),
UserID = reader.GetInt32(1),
AnimalKennelID = reader.GetInt32(2),
Date = reader.GetDateTime(3),
Notes = reader.GetString(4)
};
cleaningRecords.Add(record);
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(cleaningRecords);
}
/// <summary>
/// NAME: Austin Gee
/// DATE: 3/12/2020
/// CHECKED BY: Michael Thompson
///
/// selects all locations
/// </summary>
/// <remarks>
/// UPDATED BY: NA
/// UPDATE DATE: NA
/// WHAT WAS CHANGED: NA
///
/// </remarks>
public List <Location> SelectAllLocations()
{
List <Location> locations = new List <Location>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_all_locations", conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var location = new Location();
location.LocationID = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
location.Name = reader.GetString(1);
}
location.Address1 = reader.GetString(2);
location.Address2 = reader.IsDBNull(3) ? null : reader.GetString(3);
location.City = reader.GetString(4);
location.State = reader.GetString(5);
location.Zip = reader.GetString(6);
locations.Add(location);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(locations);
}
// This returns a list of users from the database based upon what activityID is associated with them.
public List<User> SelectUsersByActivity(int activityID)
{
List<User> users = new List<User>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_person_by_activity", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ActivityID", activityID);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var person = new User();
person.PersonID = reader.GetInt32(0);
person.FirstName = reader.GetString(1);
person.LastName = reader.GetString(2);
users.Add(person);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return users;
}
/// <summary>
/// Robert Forbes
/// Created:
/// 2017/04/13
///
/// Retrieves all pickups related to a given driver.
/// </summary>
///
/// <remarks>
/// Aaron Usher
/// Updated:
/// 2017/04/21
///
/// Standardized method.
/// </remarks>
/// <remarks>
/// Robert Forbes
///
/// Updated:
/// 2017/04/30
///
/// Added Company order id to database table so all accessors were updated to use the new field
/// </remarks>
///
/// <param name="driverId"></param>
/// <returns></returns>
public static List <Pickup> RetrievePickupsForDriver(int?driverId)
{
var pickups = new List <Pickup>();
var conn = DBConnection.GetConnection();
var cmdText = @"sp_retrieve_pickup_from_search";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@DRIVER_ID", driverId);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
pickups.Add(new Pickup()
{
PickupId = reader.GetInt32(0),
SupplierId = reader.GetInt32(1),
WarehouseId = reader.GetInt32(2),
DriverId = reader.GetInt32(3),
EmployeeId = reader.GetInt32(4),
CompanyOrderId = reader.GetInt32(5)
});
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(pickups);
}
/// <summary>
/// Ryan Spurgetis
/// 4/27/2017
///
/// Retrieves a list of pickups picked up by drivers
/// </summary>
/// <param name="status"></param>
/// <returns></returns>
public static List <PickupLineAndProductName> RetrievePickupLinesReceived(bool?status)
{
var lines = new List <PickupLineAndProductName>();
var conn = DBConnection.GetConnection();
var cmdText = @"sp_retrieve_pickup_line_pickups_receieved";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PICK_UP_STATUS", status);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
lines.Add(new PickupLineAndProductName()
{
PickupLineId = reader.GetInt32(0),
PickupId = reader.GetInt32(1),
ProductId = reader.GetInt32(2),
Quantity = reader.GetInt32(3),
PickupStatus = reader.GetBoolean(4),
ProductName = reader.GetString(5)
});
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(lines);
}
/// <summary>
/// Creator: Tener Karar
/// Created: 2020/02/7
/// Approver: Steven Cardona
///
/// this method get all item in back room Edite
/// first, get a connection
/// next, we need a command object
/// set the command type
/// </summary>
///
/// <remarks>
/// Updater: Matt Deaton
/// Updated: 2020-03-11
/// Update: Added the ShelterItem item to the method, to display in data grid.
/// </remarks>
public List <Item> getAllItemsInBackRoom()
{
List <Item> items = new List <Item>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_retrieve_item_list", conn);
cmd.CommandType = CommandType.StoredProcedure;
// now that the command is set up, we can execute it
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var item = new Item();
item.ItemID = reader.GetInt32(0);
item.ItemName = reader.GetString(1);
item.ItemQuantity = reader.GetInt32(2);
item.ItemCategoryID = reader.GetString(3);
//item.ItemLocationID = reader.GetString(4);
item.Description = retrieveItemCategoryByItemCategoryID(item.ItemCategoryID);
item.ShelterItem = reader.GetBoolean(4);
items.Add(item);
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(items);
}
/// <summary>
/// Christian Lopez
/// Created:
/// 2017/03/22
///
/// Retrieves a list of all supplier invoices in the database
/// </summary>
///
/// <remarks>
/// Aaron Usher
/// Updated:
/// 2017/04/28
///
/// Standardized method.
/// </remarks>
///
/// <returns>List of all supplier invoices in the database.</returns>
public static List <SupplierInvoice> RetrieveSupplierInvoices()
{
var supplierInvoices = new List <SupplierInvoice>();
var conn = DBConnection.GetConnection();
var cmdText = @"sp_retrieve_supplier_invoice_list";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
supplierInvoices.Add(new SupplierInvoice
{
SupplierInvoiceId = reader.GetInt32(0),
SupplierId = reader.GetInt32(1),
InvoiceDate = reader.GetDateTime(2),
SubTotal = reader.GetDecimal(3),
TaxAmount = reader.GetDecimal(4),
Total = reader.GetDecimal(5),
AmountPaid = reader.GetDecimal(6),
Approved = reader.GetBoolean(7),
Active = reader.GetBoolean(8)
});
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(supplierInvoices);
}
/// <summary>
/// Christian Lopez
/// Created:
/// 2017/03/03
///
/// Get a list of all suppliers, regardless of status and approval.
/// </summary>
///
/// <remarks>
/// Aaron Usher
/// Updated:
/// 2017/04/21
///
/// Standardized method.
/// </remarks>
///
/// <returns>A list of all suppliers in the database.</returns>
public static List <Supplier> RetrieveSuppliers()
{
List <Supplier> suppliers = new List <Supplier>();
var conn = DBConnection.GetConnection();
var cmdText = @"sp_retrieve_supplier_list";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
suppliers.Add(new Supplier
{
SupplierID = reader.GetInt32(0),
UserId = reader.GetInt32(1),
IsApproved = reader.GetBoolean(2),
ApprovedBy = reader.IsDBNull(3) ? (int?)null : reader.GetInt32(3),
FarmName = reader.GetString(4),
FarmAddress = reader.GetString(5),
FarmCity = reader.GetString(6),
FarmState = reader.GetString(7),
FarmTaxID = reader.GetString(8),
Active = reader.GetBoolean(9)
});
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(suppliers);
}
/// <summary>
/// Robert Forbes
/// Created:2017/03/09
///
/// Creates a new delivery and returns the id of the delivery
/// </summary>
/// <remarks>
/// Aaron Usher
/// Updated: 2017/04/07
///
/// Standardized method.
/// </remarks>
/// <param name="delivery">Delivery to add to the database.</param>
/// <returns>The delivery Id of the newly created delivery</returns>
public static int CreateDeliveryAndRetrieveDeliveryId(Delivery delivery)
{
var result = 0;
var conn = DBConnection.GetConnection();
var cmdText = @"sp_create_delivery_return_delivery_id";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ROUTE_ID", SqlDbType.Int);
if (delivery.RouteId != null)
{
cmd.Parameters["@ROUTE_ID"].Value = delivery.RouteId;
}
else
{
cmd.Parameters["@ROUTE_ID"].Value = DBNull.Value;
}
cmd.Parameters.AddWithValue("@DELIVERY_DATE", delivery.DeliveryDate);
cmd.Parameters.AddWithValue("@VERIFICATION", delivery.Verification);
cmd.Parameters.AddWithValue("@STATUS_ID", delivery.StatusId);
cmd.Parameters.AddWithValue("@DELIVERY_TYPE_ID", delivery.DeliveryTypeId);
cmd.Parameters.AddWithValue("@ORDER_ID", delivery.OrderId);
cmd.Parameters.Add("@DELIVERY_ID", SqlDbType.Int).Direction = ParameterDirection.Output;
try
{
conn.Open();
cmd.ExecuteNonQuery();
result = Convert.ToInt32(cmd.Parameters["@DELIVERY_ID"].Value);
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
/// <summary>
/// Christian Lopez
/// Created:
/// 2017/03/22
///
/// Gets a list of invoice lines for the given invoice id
/// </summary>
///
/// <remarks>
/// Aaron Usher
/// Updated:
/// 2017/04/28
///
/// Standardized method.
/// </remarks>
///
/// <param name="invoiceId">Id of the given invoice.</param>
/// <returns>The lines of the relevant invoice.</returns>
public static List <SupplierInvoiceLine> RetrieveInvoiceLinesByInvoiceId(int invoiceId)
{
var supplierInvoiceLines = new List <SupplierInvoiceLine>();
var conn = DBConnection.GetConnection();
var cmdText = @"sp_retrieve_supplier_invoice_lines_by_supplier_invoice_id";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SUPPLIER_INVOICE_ID", invoiceId);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
supplierInvoiceLines.Add(new SupplierInvoiceLine
{
SupplierInvoiceId = reader.GetInt32(0),
ProductLotId = reader.GetInt32(1),
QuantitySold = reader.GetInt32(2),
PriceEach = reader.GetDecimal(3),
ItemDiscount = reader.GetDecimal(4),
ItemTotal = reader.GetDecimal(5)
});
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(supplierInvoiceLines);
}
/// <summary>
/// Creator: Ryan Morganti
/// Created: 2020/05/05
/// Approver: Steve Coonrod
///
/// Database Access method for inserting a new Request Record, and using it's
/// Identity key as the primary key for the DepartmentRequest record.
/// </summary>
/// <remarks>
/// Updater:
/// Updated:
/// Update:
///
/// </remarks>
/// <param name="request"></param>
/// <returns></returns>
public int InsertNewDepartmentRequest(DepartmentRequest request)
{
int result = 0;
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_insert_request", conn);
var cmd2 = new SqlCommand("sp_insert_new_department_request", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd2.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@DateCreated", DateTime.Now);
cmd.Parameters.AddWithValue("RequestTypeID", request.RequestTypeID);
cmd.Parameters.AddWithValue("RequestingUserID", request.RequestingUserID);
cmd.Parameters.AddWithValue("@Open", true);
cmd.Parameters.Add("@RequestID", SqlDbType.Int).Direction = ParameterDirection.Output;
try
{
conn.Open();
cmd.ExecuteScalar();
request.RequestID = (int)cmd.Parameters["@RequestID"].Value;
cmd2.Parameters.AddWithValue("@DeptRequestID", request.RequestID);
cmd2.Parameters.AddWithValue("@RequestingUserID", request.RequestingUserID);
cmd2.Parameters.AddWithValue("@RequestGroupID", request.RequestorGroupID);
cmd2.Parameters.AddWithValue("@RequestedGroupID", request.RequesteeGroupID);
cmd2.Parameters.AddWithValue("@RequestTopic", request.Topic);
cmd2.Parameters.AddWithValue("@RequestBody", request.Body);
result = cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(result);
}
/// <summary>
///
/// CREATOR: Steve Coonrod
/// CREATED: 2020/4/10
/// APPROVER: Matt Deaton
///
/// This method adds a new Social Media Request to the DB
///
/// </summary>
/// <remarks>
///
/// UPDATER: NA
/// UPDATED: NA
/// UPDATE: NA
///
/// </remarks>
/// <param name="request"></param>
/// <returns></returns>
public int InsertSocialMediaRequest(SocialMediaRequest request)
{
int requestID = 0;
//Connection
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_insert_social_media_request", conn);
cmd.CommandType = CommandType.StoredProcedure;
//Parameters
cmd.Parameters.Add("@DateCreated", SqlDbType.DateTime);
cmd.Parameters.Add("@RequestTypeID", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@RequestingUserID", SqlDbType.Int);
cmd.Parameters.Add("@Open", SqlDbType.Bit);
cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 500);
cmd.Parameters.Add("@RequestID", SqlDbType.Int).Direction = ParameterDirection.Output;
//Values
cmd.Parameters["@DateCreated"].Value = request.DateCreated;
cmd.Parameters["@RequestTypeID"].Value = request.RequestTypeID;
cmd.Parameters["@RequestingUserID"].Value = request.RequestingUserID;
cmd.Parameters["@Open"].Value = request.Open;
cmd.Parameters["@Title"].Value = request.Title;
cmd.Parameters["@Description"].Value = request.Description;
try
{
conn.Open();
cmd.ExecuteScalar();
requestID = (int)cmd.Parameters["@RequestID"].Value;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(requestID);
}
/// <summary>
/// Creator: Michael Thompson
/// Created: 2/19/2020
/// Checked By: Austin Gee, 2/21/2020
///
/// This method if for passing the Animal's profile description and image path to the database. It returns True if 1 row is effected
/// It will return False if zero rows effected
/// </summary>
/// <remarks>
/// Updater: Michael Thompson
/// Updated: 4/25/2020
/// Update: Updating to books specifications for images
/// Approver: Austin Gee
/// </remarks>
/// <param name="animalID"></param>
/// <param name="profileDescription"></param>
/// <param name="profileImageData"></param>
/// <param name="profileImageMimeType"></param>
/// <returns>True if the animal was updated and false if it was not</returns>
public bool UpdateAnimalProfile(int animalID, string profileDescription, byte[] profileImageData, string profileImageMimeType)
{
bool updateSuccess = false;
// connection
var conn = DBConnection.GetConnection();
// cmd
var cmd = new SqlCommand("sp_update_animal_profile");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
// parameters
cmd.Parameters.Add("@AnimalID", SqlDbType.Int);
cmd.Parameters.Add("@ProfileImageData", SqlDbType.VarBinary);
cmd.Parameters.Add("@ProfileImageMimeType", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@ProfileDescription", SqlDbType.NVarChar, 500);
// values
cmd.Parameters["@AnimalID"].Value = animalID;
cmd.Parameters["@ProfileImageData"].Value = profileImageData;
cmd.Parameters["@ProfileImageMimeType"].Value = profileImageMimeType;
cmd.Parameters["@ProfileDescription"].Value = profileDescription;
// execute the command
try
{
conn.Open();
int rows = cmd.ExecuteNonQuery();
updateSuccess = (rows == 1);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(updateSuccess);
}
/// Creator: Chase Schulte
/// Created: 2020/03/28
/// Approver: Kaleb Bachert
///
/// Method for Select Availability By UserID
/// </summary>
///
/// <remarks>
/// Updater
/// Updated:
/// Update:
/// </remarks>
/// <param name="userID"></param>
/// <returns></returns>
public List <AvailabilityVM> SelectAvailabilityByUserID(int userID)
{
//Conn
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_availabilties_by_employee_id", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("UserID", userID);
List <AvailabilityVM> availabilities = new List <AvailabilityVM>();
try
{
conn.Open();
var reader = cmd.ExecuteReader();
//Reader
if (reader.HasRows)
{
while (reader.Read())
{
availabilities.Add(new AvailabilityVM()
{
AvailabilityID = reader.GetInt32(0),
EmployeeID = reader.GetInt32(1),
StartTime = reader.GetString(2),
EndTime = reader.GetString(3),
DayOfWeek = reader.GetString(4),
Name = reader.GetString(5) + " " + reader.GetString(6)
});
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(availabilities);
}
/// <summary>
/// Laura Simmonds
/// Created:
/// 2017/05/08
///
/// Invokes a stored procedure to retrieve supplier order line by order id and
/// update the amount on the order
///
/// <param name="productOrderId">The id to search on.</param>
/// <returns>List of order lines associated with the given product order.</returns>
public static List <SupplierOrderLine> RetrieveOrderLinesBySupplierOrderId(int supplierProductOrderId)
{
var orderLines = new List <SupplierOrderLine>();
var conn = DBConnection.GetConnection();
var cmdText = @"sp_retrieve_supplier_order_line";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(@"SUPPLIER_ORDER_ID", supplierProductOrderId);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
orderLines.Add(new SupplierOrderLine()
{
OrderLineID = reader.GetInt32(0),
SupplierProductOrderID = reader.GetInt32(1),
ProductID = reader.IsDBNull(2) ? (int?)null : reader.GetInt32(2),
ProductName = reader.GetString(3),
Price = reader.GetDecimal(4),
Quantity = reader.GetInt32(5)
});
}
reader.Close();
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(orderLines);
}
/// <summary>
/// Creator: Steven Cardona
/// Created: 02/11/2020
/// Approver: Zach Behrensmeyer
///
/// This method connects to the database and
/// selects all active users via the sp_select_all_active_users stored procedure
/// </summary>
/// <remarks>
/// Updater: Steven Cardona
/// Updated: 03/01/2020
/// Update: Added lines to pull Address lines from reader
/// </remarks>
/// <returns>Returns a list of PetUniverseUsers</returns>
public List <PetUniverseUser> SelectAllActivePetUniverseUsers()
{
List <PetUniverseUser> users = new List <PetUniverseUser>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_all_active_users", conn)
{
CommandType = CommandType.StoredProcedure
};
try
{
conn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
users.Add(new PetUniverseUser()
{
PUUserID = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
PhoneNumber = reader.GetString(3),
Email = reader.GetString(4),
Address1 = reader.GetString(5),
Address2 = reader.IsDBNull(6) ? null : reader.GetString(6),
City = reader.GetString(7),
State = reader.GetString(8),
ZipCode = reader.GetString(9),
Active = reader.GetBoolean(10),
});
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(users);
}
/// <summary>
/// Creator: Carl Davis
/// Created: 3/30/2020
/// Approver: Ethan Murphy 4/3/2020
///
/// Method to update a facility inspection item record
/// </summary>
/// <remarks>
/// Updater:
/// Updated:
/// Update:
/// </remarks>
/// <param name="oldFacilityInspectionItem"></param>
/// <param name="newFacilityInspectionItem"></param>
/// <returns>1 or 0 int depending if record was updated</returns>
public int UpdateFacilityInspectionItem(FacilityInspectionItem oldFacilityInspectionItem, FacilityInspectionItem newFacilityInspectionItem)
{
int result = 0;
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_update_facility_inspection_item", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FacilityInspectionItemID", oldFacilityInspectionItem.FacilityInspectionItemID);
cmd.Parameters.AddWithValue("@OldItemName", oldFacilityInspectionItem.ItemName);
cmd.Parameters.AddWithValue("@OldUserID", oldFacilityInspectionItem.UserID);
cmd.Parameters.AddWithValue("@OldFacilityInspectionID", oldFacilityInspectionItem.FacilityInpectionID);
cmd.Parameters.AddWithValue("@OldItemDescription", oldFacilityInspectionItem.ItemDescription);
cmd.Parameters.AddWithValue("@NewItemName", newFacilityInspectionItem.ItemName);
cmd.Parameters.AddWithValue("@NewUserID", newFacilityInspectionItem.UserID);
cmd.Parameters.AddWithValue("@NewFacilityInspectionID", newFacilityInspectionItem.FacilityInpectionID);
cmd.Parameters.AddWithValue("@NewItemDescription", newFacilityInspectionItem.ItemDescription);
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(result);
}
/// NAME: Hassan Karar
/// DATE: 2020/3/7
/// CHECKED BY:
/// <summary>
/// This method for the retrive list of job listing.
/// </summary>
/// <remarks>
/// UPDATED BY:
/// UPDATE DATE:
/// WHAT WAS CHANGED:
/// <param name="jobListing"></param>
/// </remarks>
///
public List <JobListing> GetAllJobListings()
{
List <JobListing> jobs = new List <JobListing>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_retrieve_JobListing", conn)
{
CommandType = CommandType.StoredProcedure
};
try
{
conn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
JobListing jobListing = new JobListing();
jobListing.JobListingID = reader.GetInt32(0);
jobListing.Position = jobListing.RoleID;
jobListing.RoleID = reader.GetString(1);
jobListing.Benefits = reader.GetString(2);
jobListing.Requirements = reader.GetString(3);
jobListing.StartingWage = reader.GetDecimal(4);
jobListing.Responsibilities = reader.GetString(5);
jobs.Add(jobListing);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(jobs);
}
/// <summary>
/// Creator: Cash Carlson
/// Created: 2020/02/21
/// Approver: Zach Behrensmeyer
///
/// Method to get Inventory Items
/// </summary>
/// <remarks>
/// Updater Cash Carlson
/// Updated: 2020/04/29
/// Update: Updated to add active field
/// </remarks>
/// <returns>A List of Inventory Items</returns>
public List <InventoryItems> SelectAllInventory()
{
List <InventoryItems> inventoryItems = new List <InventoryItems>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_all_products_items");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var inventoryItem = new InventoryItems();
inventoryItem.ProductID = reader.GetString(0);
inventoryItem.Name = reader.GetString(1);
inventoryItem.Brand = reader.GetString(2);
inventoryItem.Category = reader.GetString(3);
inventoryItem.Type = reader.GetString(4);
inventoryItem.Price = reader.GetDecimal(5);
inventoryItem.Quantity = reader.GetInt32(6);
inventoryItem.Active = reader.GetBoolean(7);
inventoryItems.Add(inventoryItem);
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(inventoryItems);
}
/// <summary>
/// Christian Lopez
/// Created:
/// 2017/03/08
///
/// Get a list of all products in the database
/// </summary>
///
/// <remarks>
/// Aaron Usher
/// Updated:
/// 2017/04/21
///
/// Standardized method.
/// </remarks>
///
///<returns>List of all products in the database.</returns>
public static List <Product> RetrieveProductList()
{
var products = new List <Product>();
var conn = DBConnection.GetConnection();
var cmdText = @"sp_retrieve_product_list";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
products.Add(new Product()
{
ProductId = reader.GetInt32(0),
Name = reader.GetString(1),
Description = reader.GetString(2),
UnitPrice = reader.GetDecimal(3),
ImageName = reader.GetString(4),
Active = reader.GetBoolean(5),
UnitOfMeasurement = reader.GetString(6),
DeliveryChargePerUnit = reader.GetDecimal(7)
});
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(products);
}
public User AuthenticateUser(string email, string passwordHash)
{
int result = 0; // this will be 1 if the user is authenticated
User user = null;
// get the SqlConnection object
var conn = DBConnection.GetConnection();
// next get a command object
var cmd = new SqlCommand("sp_authenticate_user", conn);
// set the command type
cmd.CommandType = CommandType.StoredProcedure;
// add the parameters to the command
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 250);
cmd.Parameters.Add("@PasswordHash", SqlDbType.NVarChar, 100);
// provide values for the parameters
cmd.Parameters["@Email"].Value = email;
cmd.Parameters["@PasswordHash"].Value = passwordHash;
// now that the command is ready,
// open a connection, execute and process results
try
{
conn.Open();
result = Convert.ToInt32(cmd.ExecuteScalar());
if (result == 1) // user authenticated
{
user = SelectUserByEmail(email);
}
else
{
throw new ApplicationException("User not found!");
}
}
catch (Exception up)
{
throw up;
}
return(user);
}
} // End AuthenticateUser()
/// <summary>
/// CREATED BY: Matt Deaton
/// DATE CREATED: 2020-05-09
///
/// Method to select users that are active.
///
/// </summary>
/// <param name="active"></param>
/// <returns></returns>
public List <User> SelectUsersByActive(bool active = true)
{
List <User> users = new List <User>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_users_by_active");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Active", SqlDbType.Bit);
cmd.Parameters["@Active"].Value = active;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var user = new User();
user.EmployeeID = reader.GetInt32(0);
user.FirstName = reader.GetString(1);
user.LastName = reader.GetString(2);
user.PhoneNumber = reader.GetString(3);
user.Email = reader.GetString(4);
user.Active = reader.GetBoolean(5);
users.Add(user);
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(users);
}// End SelectUsersByActive()
/// <summary>
/// Aaron Usher
/// Updated: 2017/04/20
///
/// Retrieves a list of packages based on the given package.
/// </summary>
///
/// <remarks>
/// Aaron Usher
/// Updated: 2017/04/20
///
/// Standardized method; fixed search bug where given package was passed back instead of found packages.
/// </remarks>
/// <param name="package">The package to search on.</param>
/// <returns>List of packages that are similar to the given package.</returns>
public static List <Package> RetrievePackageFromSearch(Package package)
{
var packages = new List <Package>();
var conn = DBConnection.GetConnection();
var cmdText = @"sp_retrieve_PACKAGE_from_search";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PACKAGE_ID", package.PackageId);
cmd.Parameters.AddWithValue("@DELIVERY_ID", package.DeliveryId);
cmd.Parameters.AddWithValue("@ORDER_ID", package.OrderId);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
packages.Add(new Package()
{
PackageId = reader.GetInt32(0),
DeliveryId = reader.GetInt32(1),
OrderId = reader.GetInt32(2)
});
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(packages);
}
/// <summary>
/// Creator: Thomas Dupuy
/// Created: 2020/02/06
/// Approver: Awaab Elamin
/// This method selects all appointments
/// </summary>
/// <remarks>
/// Updater: Thomas Dupuy
/// Updated: 2020/04/12
/// Update: Updated it to match the AppointmentLocationVM instead on the Appointment DTO
///
/// Updater: Mohamed Elamin
/// Updated: 2020/04/20
/// Update: I Added param, returns tags for the comments.
/// And updated the date format.
/// </remarks>
/// <returns>A list of active appointments</returns>
public List <AppointmentLocationVM> SelectAllActiveAppointments()
{
List <AppointmentLocationVM> appointments = new List <AppointmentLocationVM>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_all_appointments_by_active");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
AppointmentLocationVM newAppointment = new AppointmentLocationVM();
newAppointment.AppointmentID = reader.GetInt32(0);
newAppointment.AdoptionApplicationID = reader.GetInt32(1);
newAppointment.AppointmentTypeID = reader.GetString(2);
newAppointment.DateTime = reader.GetDateTime(3);
newAppointment.Notes = reader.IsDBNull(4) ? null : reader.GetString(4);
newAppointment.Decicion = reader.GetString(5);
newAppointment.LocationID = reader.GetInt32(6);
newAppointment.Active = reader.GetBoolean(7);
newAppointment.LocationName = reader.GetString(8);
newAppointment.LocationAddress1 = reader.GetString(9);
newAppointment.LocationAddress2 = reader.IsDBNull(10) ? null : reader.GetString(10);
newAppointment.LocationCity = reader.GetString(11);
newAppointment.LocationState = reader.GetString(12);
newAppointment.LocationZip = reader.GetString(13);
appointments.Add(newAppointment);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(appointments);
}
/// <summary>
/// Creator: Jesse Tomash
/// Created: 4/27/2020
/// Approver:
///
/// Method to select item by item id.
/// </summary>
///
/// <remarks>
/// Updated By:
/// Updated:
/// Update:
/// </remarks>
/// <param name="item"></param>
public Item SelectItemByItemID(int itemID)
{
Item item = null;
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_item_by_item_id", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ItemID", itemID);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
{
item = new Item()
{
ItemName = reader.GetString(0),
ItemCategoryID = reader.GetString(1),
ItemQuantity = reader.GetInt32(2),
Description = reader.GetString(3),
ShelterItem = reader.GetBoolean(4),
ItemID = reader.GetInt32(5),
ShelterThreshold = reader.IsDBNull(6) ? 0 : reader.GetInt32(6)
};
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(item);
}
/// <summary>
/// Creator: Brandyn T. Coverdill
/// Created: 2020/02/23
/// Approver: Dalton Reierson
/// Approver: Jesse Tomash
///
/// Method that gets a list of all items for inventory.
/// </summary>
///
/// <remarks>
/// Updated By: Brandyn T. Coverdill
/// Updated: 2020/03/03
/// Update: The Item Description was not getting fetched into the datagrid, so I added that field.
/// Approver: Jesse Tomash
///
/// Updated By: Matt Deaton
/// Updated: 2020-03-07
/// Update: Added ShelterItem field to show in retrieved list
///
/// </remarks>
public List <Item> getAllItems()
{
List <Item> items = new List <Item>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_retrieve_items", conn)
{
CommandType = CommandType.StoredProcedure
};
try
{
conn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
items.Add(new Item()
{
ItemID = reader.GetInt32(0),
ItemName = reader.GetString(1),
ItemQuantity = reader.GetInt32(2),
ItemCategoryID = reader.GetString(3),
Description = reader.GetString(4),
Active = reader.GetBoolean(5),
ShelterItem = reader.GetBoolean(6),
ShelterThreshold = reader.IsDBNull(7) ? 0 : reader.GetInt32(7)
});
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(items);
}
/// <summary>
/// Creator: Ryan Morganti
/// Created: 2020/03/19
/// Approver: Derek Taylor
///
/// Method used to query all the positions at PetUniverse from the DataBase
/// </summary>
/// <remarks>
/// Updater: NA
/// Updated: NA
/// Update: NA
/// </remarks>
public List <JobListing> SelectAllJobPositions()
{
List <JobListing> positions = new List <JobListing>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_all_job_listings", conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
JobListing job = new JobListing();
job.JobListingID = reader.GetInt32(0);
job.Position = reader.GetString(1);
job.Benefits = reader.GetString(2);
job.Requirements = reader.GetString(3);
job.StartingWage = reader.GetDecimal(4);
job.Responsibilities = reader.GetString(5);
job.Active = reader.GetBoolean(6);
positions.Add(job);
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(positions);
}
/// <summary>
/// Creator: Dalton Reierson
/// Created: 2020/03/09
/// Approver: Brandyn T. Coverdill
/// Approver: Jesee Tomash
///
/// Method to select all items by active field
/// </summary>
///
/// <remarks>
/// Updated By: Robert Holmes
/// Updated: 04/29/2020
/// Update: Made it safe to attempt when description is null.
///
/// Updated By: Brandyn T. Coverdill
/// Updated: 4/29/2020
/// Update: Added the ShelterItem field for item.
/// </remarks>
public List <Item> getAllItemsByActive(bool active)
{
List <Item> itemList = new List <Item>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_items_by_active", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Active", active);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
Item item = new Item();
item.ItemID = reader.GetInt32(0);
item.ItemName = reader.GetString(1);
item.ItemQuantity = reader.GetInt32(2);
item.ItemCategoryID = reader.GetString(3);
if (!reader.IsDBNull(4))
{
item.Description = reader.GetString(4);
}
//item.Active = reader.GetBoolean(5);
item.ShelterItem = reader.GetBoolean(5);
itemList.Add(item);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(itemList);
}
/// <summary>
/// Creator: Derek Taylor
/// Created: 2/14/2020
/// Approver: Ryan Morganti
///
/// This method is used to get all applicants
/// </summary>
/// <remarks>
/// Updater: NA
/// Updated: NA
/// Update: NA
/// </remarks>
/// <returns>List of Logs</returns>
public List <Applicant> SelectAllApplicants()
{
List <Applicant> applicants = new List <Applicant>();
var conn = DBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_all_applicants", conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
applicants.Add(new Applicant()
{
ApplicantID = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
MiddleName = reader.GetString(3),
Email = reader.GetString(4),
PhoneNumber = reader.GetString(5),
ApplicantStatus = reader.GetString(6),
ApplicationPostion = reader.GetString(7)
});
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(applicants);
}
