/* 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;
}
