public int CreatePackage(Package p)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewPackage";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("SpecificationID", p.SpecificationID);
cmd.Parameters.AddWithValue("GoodsID", p.GoodsID);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
// Creates a new package
public int create(int goodsID, int specificationID)
{
// Establishes package models
PackageModel packageModel = new PackageModel();
// Holds the new package
Package newPackage = new Package();
// Stored details for the package
newPackage.GoodsID = goodsID;
newPackage.SpecificationID = specificationID;
// Adds the object to the database
int packageID = packageModel.CreatePackage(newPackage);
// Returns the package ID
return packageID;
}
// Finds Transactions with the specified details in the order object.
public List<Transaction> SearchTransactions(Package p)
{
throw new NotImplementedException();
}
// Calls the main method to get packages.
public Package SearchPackage(Package p)
{
return SearchPackage(p.ID);
}
// This is the main method to get a package from the packages ID within the databse.
public Package SearchPackage(int ID)
{
var p = new Package();
using (connect = new MySqlConnection(_connectionString))
{
try
{
string query = "ListPackage";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("PackageID", ID);
connect.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
p.ID = int.Parse(reader["Package_ID"].ToString());
p.SpecificationID = int.Parse(reader["Specification_ID"].ToString());
p.GoodsID = int.Parse(reader["Goods_ID"].ToString());
}
connect.Close();
}
catch (InvalidOperationException ioException)
{
connect.Close();
}
return p;
}
}
public List<Package> GetPackagesList()
{
var packageList = new List<Package>();
using (connect = new MySqlConnection(_connectionString))
{
try
{
string query = "ListPackage";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
connect.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var p = new Package();
p.ID = int.Parse(reader["Package_ID "].ToString());
p.SpecificationID = int.Parse(reader["Specification_ID"].ToString());
p.GoodsID = int.Parse(reader["Goods_ID"].ToString());
packageList.Add(p);
}
connect.Close();
}
catch (InvalidOperationException ioException)
{
connect.Close();
}
return packageList;
}
}
public void EditPackage(Package p)
{
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "EditPackage";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("PackageID", p.ID);
cmd.Parameters.AddWithValue("SpecificationID", p.Specification);
cmd.Parameters.AddWithValue("GoodsID", p.Goods);
cmd.ExecuteNonQuery();
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
}
// Calculates the total cost of the order
private int calcPrice(int accountType, int deliveryBand, Package thisPackage)
{
int runningTotal = calcBaseCost(thisPackage);
float modifier = 0f;
// Determines account benefit modifier
switch (accountType)
{
case 1: modifier = 1;
break;
case 2: modifier = 0.9f;
break;
default: modifier = 1;
break;
}
// Determines delivery modifier
switch (deliveryBand)
{
case 1: modifier *= 2; // Next Day Delivery
break;
case 2: modifier *= 1.6f; // Express 1-2 Days Delivery
break;
case 3: modifier *= 1.2f; // (Next) Saturday Delivery
break;
case 4: modifier *= 1; // Standard 3-5 Days Delivery
break;
default: modifier *= 1; // Not reached
break;
}
// Apply modifiers
float finalCost = (float)runningTotal * modifier;
// Return the calculated total
return (int)finalCost;
}
// Calculates the base cost of the package based on dimensions / weight
private int calcBaseCost(Package thisPackage)
{
// In pence
int runningCost = 0;
SpecificationModel specModel = new SpecificationModel();
// Iterates through the packages in the list
Specification thisSpec = specModel.SearchSpecification(thisPackage.SpecificationID);
// Works out delivery band
if (thisSpec.Length <= 42 && thisSpec.Width <= 34 && thisSpec.Height <= 27 && thisSpec.Weight <= 100)
{
runningCost += 1500;
}
else if (thisSpec.Length <= 50 && thisSpec.Width <= 45 && thisSpec.Height <= 34 && thisSpec.Weight <= 250)
{
runningCost += 2100;
}
else if (thisSpec.Length <= 60 && thisSpec.Width <= 54 && thisSpec.Height <= 41 && thisSpec.Weight <= 400)
{
runningCost += 3000;
}
else if (thisSpec.Length <= 96 && thisSpec.Width <= 15 && thisSpec.Height <= 15)
{
runningCost += 1750;
}
else // Catch
{
// Outwith package band fee
runningCost += 3500;
}
// Returns the collective running cost
return runningCost;
}
