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); } }