public InvoiceDTO GetInvoiceBy(int id)
{
string queryString = "SELECT * FROM dbo.Invoice WHERE invoiceID = @id";
InvoiceDTO invoice = new InvoiceDTO();
try {
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryString, con))
{
cmd.Parameters.AddWithValue("invoiceID", SqlDbType.Int).Value = id;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
AccountDTO account = new AccountDTO();
ShippmentDTO shipping = new ShippmentDTO();
invoice = GenerateInvoice(reader, invoice, account, shipping);
//return product instance as data object
Debug.Print("InvoiceDAL: /FindByID/ " + invoice.GetID());
}
}
}
}
catch (Exception e)
{
e.GetBaseException();
Debug.Print(e.ToString());
}
return(invoice);
}
public IEnumerable <InvoiceDTO> GetInvoices()
{
string queryString = "SELECT * FROM dbo.Invoice";
List <InvoiceDTO> results = new List <InvoiceDTO>();
InvoiceDTO invoice;
AccountDTO account;
ShippmentDTO shipping;
try
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryString, con))
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
invoice = new InvoiceDTO();
account = new AccountDTO();
shipping = new ShippmentDTO();
invoice = GenerateInvoice(reader, invoice, account, shipping);
Debug.Print("InvoiceDAL: /FindAll/ " + invoice.GetID());
//add data objects to result-list
results.Add(invoice);
}
}
}
}
catch (Exception e)
{
e.GetBaseException();
}
return(results);
}
public IEnumerable <ShippmentDTO> FindAll()
{
string queryString = "SELECT * FROM dbo.Shippment";
List <ShippmentDTO> results = new List <ShippmentDTO>();
ShippmentDTO deliverer;
try
{
//The connection is automatically closed at the end of the using block.
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryString, con))
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
deliverer = new ShippmentDTO();
deliverer = GenerateDeliverer(reader, deliverer);
//return product instance as data object
Debug.Print("ShippmentDAL: /FindByID/ " + deliverer.GetID());
Debug.Print("ShippmentDAL: /FindByID/ " + deliverer.GetCompany());
//add data objects to result-list
results.Add(deliverer);
}
}
}
}
catch (Exception e)
{
e.GetBaseException();
}
return(results);
}
public ShippmentDTO FindBy(string name)
{
ShippmentDTO deliverer;
string queryString = "SELECT * FROM dbo.Shippment WHERE shipCompany = @name";
try
{
//The connection is automatically closed at the end of the using block.
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryString, con))
{
cmd.Parameters.AddWithValue("@name", name);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
deliverer = new ShippmentDTO();
deliverer = deliverer = GenerateDeliverer(reader, deliverer);
//return product instance as data object
Debug.Print("ShippmentDAL: /FindByID/ " + deliverer.GetID());
return(deliverer);
}
}
}
}
catch (Exception e)
{
e.GetBaseException();
}
return(null);
}
private static ShippmentDTO GenerateDeliverer(SqlDataReader reader, ShippmentDTO deliverer)
{
deliverer.SetID(Convert.ToInt32(reader["shippingID"]));
deliverer.SetCompany(reader["shipCompany"].ToString());
deliverer.SetCost(Convert.ToDecimal(reader["shipCost"]));
deliverer.SetDeliveryTime(Convert.ToInt32(reader["estimatedTime"]));
deliverer.SetStatus(Convert.ToInt32(reader["status"]));
deliverer.SetType(reader["shipType"].ToString());
return(deliverer);
}
public IEnumerable <InvoiceDTO> FindByStatus(int accountID, int paymentStatus)
{
string queryString = "SELECT * FROM dbo.Invoice WHERE accountID = @accountID AND paymentStatus = @paymentStatus";
List <InvoiceDTO> results = new List <InvoiceDTO>();
InvoiceDTO invoice;
AccountDTO account;
ShippmentDTO shipping;
try
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryString, con))
{
cmd.Parameters.AddWithValue("@accountID", SqlDbType.Int).Value = accountID;
cmd.Parameters.AddWithValue("@paymentStatus", SqlDbType.Int).Value = paymentStatus;
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
invoice = new InvoiceDTO();
account = new AccountDTO();
shipping = new ShippmentDTO();
invoice = GenerateInvoice(reader, invoice, account, shipping);
Debug.Print("InvoiceDAL: /FindInvoiceByStatus/ " + invoice.GetID());
//add data objects to result-list
results.Add(invoice);
}
}
}
}
catch (Exception e)
{
e.GetBaseException();
}
return(results);
}
public IEnumerable <InvoiceDTO> FindAllByCustomer(int accountID)
{
string queryString = "SELECT * FROM dbo.Invoice WHERE accountID = @accountID";
List <InvoiceDTO> results = new List <InvoiceDTO>();
InvoiceDTO invoice;
AccountDTO account;
ShippmentDTO shipping;
try
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryString, con))
{
cmd.Parameters.AddWithValue("@accountID", accountID);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
invoice = new InvoiceDTO();
account = new AccountDTO();
shipping = new ShippmentDTO();
invoice = GenerateInvoice(reader, invoice, account, shipping);
//return product instance as data object
Debug.Print("InvoiceDAL: /FindInvoiceBy/ " + invoice.GetID());
//add data objects to result-list
results.Add(invoice);
}
}
}
}
catch (Exception e)
{
e.GetBaseException();
}
return(results);
}
private static InvoiceDTO GenerateInvoice(SqlDataReader reader, InvoiceDTO invoice, AccountDTO account, ShippmentDTO shipping)
{
invoice.SetID(Convert.ToInt32(reader["invoiceID"]));
account.SetID(Convert.ToInt32(reader["accountID"]));
shipping.SetID(Convert.ToInt32(reader["shippingID"]));
invoice.SetCustomer(account);
invoice.SetShippment(shipping);
invoice.SetQuantity(Convert.ToInt32(reader["totalQuantity"]));
invoice.SetShippingCost(Convert.ToDecimal(reader["shippingCost"]));
invoice.SetTotal(Convert.ToDecimal(reader["totalProductCost"]));
invoice.SetTax(Convert.ToDecimal(reader["totalTax"]));
invoice.SetTotal(Convert.ToDecimal(reader["totalAmount"]));
invoice.SetOrderDate(Convert.ToDateTime(reader["orderDate"]));
invoice.SetPaymentDate(Convert.ToDateTime(reader["paymentDate"]));
invoice.SetStatus(Convert.ToInt32(reader["paymentStatus"]));
invoice.SetEmail(reader["customerMail"].ToString());
invoice.SetArrivalDate(Convert.ToDateTime(reader["arrivalDate"]));
invoice.SetPostDate(Convert.ToDateTime(reader["postageDate"]));
Debug.Print("InvoiceDAL: Invoice ID " + invoice.GetID());
return(invoice);
}
public object Post(ShippmentDTO request)
{
//APIResource resource = new APIResource("shippo_test_5f00f661c1f2f19191bfba82cc8575fddb06c202");
//APIResource resource = new APIResource("shippo_live_b248c98357917b42d991df307d6573359a901ea9");
try {
//to address
Hashtable toAddressTable = new Hashtable();
toAddressTable.Add("name", request.To.Name);
toAddressTable.Add("company", request.To.Company);
toAddressTable.Add("street_no", request.To.StreetNumber);
toAddressTable.Add("street1", request.To.Street1);
toAddressTable.Add("city", request.To.City);
toAddressTable.Add("state", request.To.State);
toAddressTable.Add("zip", request.To.Zip);
toAddressTable.Add("country", request.To.Country);
toAddressTable.Add("validate", "false");
Address address = resource.CreateAddress(toAddressTable);
// from address
Hashtable fromAddressTable = new Hashtable();
fromAddressTable.Add("name", request.From.Name);
fromAddressTable.Add("company", request.From.Company);
fromAddressTable.Add("street_no", request.From.StreetNumber);
fromAddressTable.Add("street1", request.From.Street1);
fromAddressTable.Add("city", request.From.City);
fromAddressTable.Add("state", request.From.State);
fromAddressTable.Add("zip", request.From.Zip);
fromAddressTable.Add("country", request.From.Country);
fromAddressTable.Add("validate", "false");
List <Hashtable> parcels = new List <Hashtable>();
foreach (var parcel in request.Parcels)
{
Hashtable parcelTable = new Hashtable();
parcelTable.Add("length", parcel.Length);
parcelTable.Add("width", parcel.Width);
parcelTable.Add("height", parcel.Height);
parcelTable.Add("distance_unit", parcel.Distance_unit);
parcelTable.Add("weight", parcel.Weight);
parcelTable.Add("mass_unit", parcel.Mass_unit);
parcels.Add(parcelTable);
}
var apiResponse = resource.CreateShipment(new Hashtable()
{
{ "address_to", toAddressTable },
{ "address_from", fromAddressTable },
{ "parcels", parcels },
{ "async", false }
});
var response = "{\"success\": \"success\" ," +
"\"shipment_id\" : \"" + apiResponse.ObjectId + "\"," +
"\"rates\" : " + JSON.stringify(apiResponse.Rates) + "}";
return(response);
}
catch (Exception e)
{
var response = "{\"error\": \"exception caught by server\" ," +
"\"details\" : " + e.Message.ToString() + "}";
return(response);
}
}
