/* Pre: * Post: Retrieves a list of all shipments loaded onto the input truck * @returns a list of shipments */ public static List<Shipment> GetTruckShipments(int truckId) { List<Shipment> shipments = new List<Shipment>(); DataTable table = new DataTable(); SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["GoodsDeliveryConnectionString"].ConnectionString); try { connection.Open(); string storedProc = "TruckSelectShipments"; SqlCommand cmd = new SqlCommand(storedProc, connection); SqlDataAdapter adapter = new SqlDataAdapter(cmd); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@truckId", truckId); adapter.Fill(table); for (int i = 0; i < table.Rows.Count; i++) { double length = Convert.ToDouble(table.Rows[i]["Length"]); double width = Convert.ToDouble(table.Rows[i]["Width"]); double height = Convert.ToDouble(table.Rows[i]["Height"]); double weight = Convert.ToDouble(table.Rows[i]["MaxWeight"]); string street = table.Rows[i]["ShippingStreet"].ToString(); string city = table.Rows[i]["ShippingCity"].ToString(); string state = table.Rows[i]["ShippingState"].ToString(); int zip = Convert.ToInt32(table.Rows[i]["ShippingZip"]); UtilityClass.ShippingSpeed speed = (UtilityClass.ShippingSpeed)table.Rows[0]["ShippingSpeedId"]; Address address = new Address(street, city, state, zip); Shipment shipment = new Shipment(length, width, height, weight, address, speed); shipments.Add(shipment); } } catch (Exception e) { } connection.Close(); return shipments; }
/* * Pre: * Post: The shipment information is added to the database * @returns the id of the shipment */ public static int AddToDatabase(Shipment shipment) { int id = -1; DataTable table = new DataTable(); SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["GoodsDeliveryConnectionString"].ConnectionString); try { connection.Open(); string storedProc = "ShipmentNew"; SqlCommand cmd = new SqlCommand(storedProc, connection); SqlDataAdapter adapter = new SqlDataAdapter(cmd); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@customerId", shipment.customer.id); cmd.Parameters.AddWithValue("@length", shipment.length); cmd.Parameters.AddWithValue("@width", shipment.width); cmd.Parameters.AddWithValue("@height", shipment.height); cmd.Parameters.AddWithValue("@weight", shipment.weight); cmd.Parameters.AddWithValue("@cost", shipment.cost); cmd.Parameters.AddWithValue("@itemValue", shipment.itemValue); cmd.Parameters.AddWithValue("@shippingSpeedId", shipment.shippingSpeed); cmd.Parameters.AddWithValue("@firstName", shipment.firstName); cmd.Parameters.AddWithValue("@lastName", shipment.lastName); cmd.Parameters.AddWithValue("@street", shipment.deliveryAddress.street); cmd.Parameters.AddWithValue("@city", shipment.deliveryAddress.city); cmd.Parameters.AddWithValue("@state", shipment.deliveryAddress.state); cmd.Parameters.AddWithValue("@zip", shipment.deliveryAddress.zip); cmd.Parameters.AddWithValue("@nameOnCard", shipment.creditCard.nameOnCard); cmd.Parameters.AddWithValue("@cardType", shipment.creditCard.cardType); cmd.Parameters.AddWithValue("@cardNum", shipment.creditCard.cardNum); cmd.Parameters.AddWithValue("@securityCode", shipment.creditCard.securityCode); cmd.Parameters.AddWithValue("@expirationMonth", shipment.creditCard.expirationMonth); cmd.Parameters.AddWithValue("@expirationYear", shipment.creditCard.expirationYear); cmd.Parameters.AddWithValue("@billStreet", shipment.creditCard.billingAddress.street); cmd.Parameters.AddWithValue("@billCity", shipment.creditCard.billingAddress.city); cmd.Parameters.AddWithValue("@billState", shipment.creditCard.billingAddress.state); cmd.Parameters.AddWithValue("@billZip", shipment.creditCard.billingAddress.zip); if (shipment.shipmentType != null) cmd.Parameters.AddWithValue("@shipmentTypeId", shipment.shipmentType.id); else cmd.Parameters.AddWithValue("@shipmentTypeId", -1); if (shipment.insuranceType != null) cmd.Parameters.AddWithValue("@insuranceTypeId", shipment.insuranceType.id); else cmd.Parameters.AddWithValue("@insuranceTypeId", -1); adapter.Fill(table); //get id if (table.Rows.Count == 1) id = Convert.ToInt32(table.Rows[0]["Id"]); } catch (Exception e) { id = -1; } connection.Close(); return id; }
/* * Pre: * Post: If the payment information is valid, submit the shipment */ protected void Submit_Click(object sender, EventArgs e) { Page.Validate("Payment"); //collect information and enter into database if (Page.IsValid) { double length = Convert.ToDouble(Length.Text); double width = Convert.ToDouble(Width.Text); double height = Convert.ToDouble(Height.Text); double weight = Convert.ToDouble(Weight.Text); double cost = Convert.ToDouble(Cost.Text.Substring(1)); Customer customer = DbInterfacePerson.GetCustomer(Session["username"].ToString()); Address deliveryAddress = GetDeliveryAddress(); CreditCard card = GetCreditCard(); double value = 0; if (ItemValue.Text.Length > 0) value = Convert.ToDouble(ItemValue.Text); ShipmentType type = null; if (ShipmentType.SelectedIndex > 0) type = new ShipmentType(Convert.ToInt32(ShipmentType.SelectedValue), ShipmentType.SelectedItem.Text); UtilityClass.ShippingSpeed speed = UtilityClass.ShippingSpeed.Normal; if (ShippingSpeed.SelectedIndex == 1) speed = UtilityClass.ShippingSpeed.Express; else if (ShippingSpeed.SelectedIndex == 2) speed = UtilityClass.ShippingSpeed.Urgent; InsuranceType insurance = null; if (Insurance.SelectedIndex > 0) insurance = new InsuranceType(Convert.ToInt32(Insurance.SelectedValue), Insurance.SelectedItem.Text); Shipment shipment = new Shipment(customer, length, width, height, weight, type, insurance, value, cost, FirstName.Text, LastName.Text, deliveryAddress, card, false, speed); //if successfully added, show confirmation message int id = shipment.AddToDatabase(); if (id != -1) { string trackingNum = GetTrackingNumber(id); ConfirmLabel.Text = "Thank you for using the La Crosse Parcel Service! Your shipment has been registered and is being processed. You will be notified when your shipment is accepted"; MainPage.Visible = false; Confirmation.Visible = true; UtilityClass.SendEmail(customer.email, "Shipment in Processing", "Thank you for using the La Crosse Parcel Service! " + "Your tracking number is " + trackingNum + ". Please visit 138.49.101.81/Account/Login to view your shipment status."); } } }
/* * Pre: * Post: Retrieves the shipment information neded for truck placement * @returns the info of the shipment */ public static Shipment GetShipmentForTruck(int shipmentId) { Shipment shipment = null; DataTable table = new DataTable(); SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["GoodsDeliveryConnectionString"].ConnectionString); try { connection.Open(); string storedProc = "ShipmentSelectTruckInfo"; SqlCommand cmd = new SqlCommand(storedProc, connection); SqlDataAdapter adapter = new SqlDataAdapter(cmd); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@shipmentId", shipmentId); adapter.Fill(table); //get shipment info if (table.Rows.Count == 1) { double length = Convert.ToDouble(table.Rows[0]["Length"]); double width = Convert.ToDouble(table.Rows[0]["Width"]); double height = Convert.ToDouble(table.Rows[0]["Height"]); double weight = Convert.ToDouble(table.Rows[0]["Weight"]); string street = table.Rows[0]["ShippingStreet"].ToString(); string city = table.Rows[0]["ShippingCity"].ToString(); string state = table.Rows[0]["ShippingState"].ToString(); int zip = Convert.ToInt32(table.Rows[0]["ShippingZip"]); string username = table.Rows[0]["Username"].ToString(); string password = table.Rows[0]["Password"].ToString(); string firstName = table.Rows[0]["FirstName"].ToString(); string lastName = table.Rows[0]["LastName"].ToString(); string phone = table.Rows[0]["Phone"].ToString(); int customerId = Convert.ToInt32(table.Rows[0]["PersonId"]); string email = table.Rows[0]["Email"].ToString(); UtilityClass.ShippingSpeed speed = (UtilityClass.ShippingSpeed)table.Rows[0]["ShippingSpeedId"]; Customer customer = new Customer(customerId, firstName, lastName, phone, email, password, UtilityClass.UserTypes.Customer); Address address = new Address(street, city, state, zip); shipment = new Shipment(length, width, height, weight, address, speed); shipment.customer = customer; } } catch (Exception e) { shipment = null; } connection.Close(); return shipment; }