This is my first program I've written ever. Don't hold back on me. Any help and feedback is appreciated. This code wont work because it requires some keys, but I censored them out, but it does compile for me and works fine. I just want some feedback on dumb things I did and how to make it better if anyone has the time to help a beginner that is self-learning.
This application is using the Amazon MWS Order API to retrieve unshipped orders in our account and these orders are displayed in a datagridview. It then allows to a user to select among these orders and print the selection's order information to an Excel invoice template, and prints 3 copies of the invoice, then repeats for the next order that was selected. The last button on the form named confirm allows input from the Tracking Number column in the datagridview to confirm the amazon order with their system. numbers for the shipment into amazon and confirms the order.
program.cs
using System.Windows.Forms;
namespace MWSAutomate
{
class Program
{
public static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
Form1.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Linq.Expressions;
using System.Net.Mime;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Windows.Forms.VisualStyles;
using System.Xml;
using MarketplaceWebServiceOrders;
using MarketplaceWebServiceOrders.Model;
using System.Xml.Linq;
using MarketplaceWebService;
using MarketplaceWebService.Samples;
using Microsoft.Office.Interop.Excel;
using CheckBox = System.Windows.Forms.CheckBox;
namespace MWSAutomate
{
public partial class Form1 : Form
{
private CheckBox chk = new CheckBox();
const string accessKey = "accesskey";
const string secretKey = "secretkey";
const string applicationName = "MWS Automate";
const string applicationVersion = "1.0";
const string sellerid = "sellerid";
const string serviceUrl = "https://mws.amazonservices.com/Orders/2013-09-01";
const string connectionstring =
"Data Source = ./SQLEXPRESS; Initial Catalog = Alliance; User ID = userid; Password = password";
public int num;
public List<CompleteOrderDetails> items = new List<CompleteOrderDetails>();
public Form1()
{
InitializeComponent();
button2.Enabled = false;
button3.Enabled = false;
DataGridViewCheckBoxColumn chk = new DataGridViewCheckBoxColumn();
}
private void button1_Click(object sender, EventArgs e)
{
XNamespace ns = "https://mws.amazonservices.com/Orders/2013-09-01";
MarketplaceWebServiceOrdersConfig config = new MarketplaceWebServiceOrdersConfig
{
ServiceURL = serviceUrl
};
MarketplaceWebServiceOrdersClient client = new MarketplaceWebServiceOrdersClient(accessKey, secretKey,
applicationName, applicationVersion, config);
ProgramBLL sample = new ProgramBLL(client);
List<CompleteOrderDetails> orders = sample.StoreNewOrders(sample, ns).ToList();
if (orders.Count() == 0)
{
using (new CenterWinDialog(this))
{
MessageBox.Show("There are no orders at this time.", "Order Retrieval", MessageBoxButtons.OK);
}
}
var list = new BindingList<CompleteOrderDetails>(orders);
dataGridView1.DataSource = list;
checkBox1.CheckState = CheckState.Unchecked;
if (dataGridView1.Columns[0].Name != "Selection")
{
DataGridViewCheckBoxColumn chk = new DataGridViewCheckBoxColumn();
dataGridView1.Columns.Insert(0, chk);
chk.Name = "Selection";
}
foreach (DataGridViewRow item in dataGridView1.Rows)
{
DataGridViewCheckBoxCell cell = (DataGridViewCheckBoxCell) item.Cells[0];
cell.Value = false;
}
button2.Enabled = true;
button3.Enabled = true;
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
string text = textBox1.Text;
if (!int.TryParse(textBox1.Text, out num)) num = 0;
}
private void button2_Click(object sender, EventArgs e)
{
if (ParseInvoiceField()) return;
AddItems();
XNamespace ns = "https://mws.amazonservices.com/Orders/2013-09-01";
MarketplaceWebServiceOrdersConfig config = new MarketplaceWebServiceOrdersConfig
{
ServiceURL = serviceUrl
};
MarketplaceWebServiceOrdersClient client = new MarketplaceWebServiceOrdersClient(accessKey, secretKey,
applicationName, applicationVersion, config);
ProgramBLL sample = new ProgramBLL(client);
int status = sample.WriteInvoice(sample, ns, connectionstring, num, items);
CheckCompletion(status);
}
private void CheckCompletion(int status)
{
if (status == 1)
{
using (new CenterWinDialog(this))
MessageBox.Show("Success! Check Printer.", "Print Orders", MessageBoxButtons.OK);
}
else if (status == 0)
{
using (new CenterWinDialog(this))
{
MessageBox.Show("There are no orders at this time.", "Order Retrieval", MessageBoxButtons.OK);
}
}
}
private bool ParseInvoiceField()
{
if (string.IsNullOrWhiteSpace(textBox1.Text))
{
using (new CenterWinDialog(this))
MessageBox.Show("Please Enter a Invoice Number.", "Error", MessageBoxButtons.OK);
return true;
}
if (int.TryParse(textBox1.Text, out num) == false)
{
using (new CenterWinDialog(this))
MessageBox.Show("Please Enter a Valid Invoice Number.", "Error", MessageBoxButtons.OK);
return true;
}
return false;
}
private void AddItems()
{
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
bool flag = false;
CompleteOrderDetails item = new CompleteOrderDetails();
foreach (DataGridViewCell dc in dr.Cells)
{
DataGridViewCheckBoxCell cell = (DataGridViewCheckBoxCell) dr.Cells[0];
if ((bool) cell.Value)
{
if (dc.OwningColumn.Index == 1 && dc.Value != null)
{
item.Name = dc.Value.ToString();
}
else if (dc.OwningColumn.Index == 1 && dc.Value == null)
{
item.Name = string.Empty;
}
if (dc.OwningColumn.Index == 2 && dc.Value != null)
{
item.AddressLine1 = dc.Value.ToString();
}
else if (dc.OwningColumn.Index == 2 && dc.Value == null)
{
item.AddressLine1 = string.Empty;
}
if (dc.OwningColumn.Index == 3 && dc.Value != null)
{
item.AddressLine2 = dc.Value.ToString();
}
else if (dc.OwningColumn.Index == 3 && dc.Value == null)
{
item.AddressLine2 = string.Empty;
}
if (dc.OwningColumn.Index == 4 && dc.Value != null)
{
item.AddressLine3 = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 4 && dc.Value == null)
{
item.AddressLine3 = string.Empty;
}
if (dc.OwningColumn.Index == 5 && dc.Value != null)
{
item.City = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 5 && dc.Value == null)
{
item.City = string.Empty;
}
if (dc.OwningColumn.Index == 6 && dc.Value != null)
{
item.StateOrRegion = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 6 && dc.Value == null)
{
item.StateOrRegion = string.Empty;
}
if (dc.OwningColumn.Index == 7 && dc.Value != null)
{
item.AmazonOrderId = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 7 && dc.Value == null)
{
item.AmazonOrderId = string.Empty;
}
if (dc.OwningColumn.Index == 8 && dc.Value != null)
{
item.PostalCode = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 8 && dc.Value == null)
{
item.PostalCode = string.Empty;
}
if (dc.OwningColumn.Index == 9 && dc.Value != null)
{
item.Title = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 9 && dc.Value == null)
{
item.Title = string.Empty;
}
if (dc.OwningColumn.Index == 10 && dc.Value != null)
{
item.ItemPrice = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 10 && dc.Value == null)
{
item.ItemPrice = string.Empty;
}
if (dc.OwningColumn.Index == 11 && dc.Value != null)
{
item.ShippingPrice = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 11 && dc.Value == null)
{
item.ShippingPrice = string.Empty;
}
if (dc.OwningColumn.Index == 12 && dc.Value != null)
{
item.Quantity = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 12 && dc.Value == null)
{
item.Quantity = string.Empty;
}
if (dc.OwningColumn.Index == 13 && dc.Value != null)
{
item.TrackingNumber = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 13 && dc.Value == null)
{
item.TrackingNumber = string.Empty;
}
if (dc.OwningColumn.Index == 14 && dc.Value != null)
{
item.ASIN = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 14 && dc.Value == null)
{
item.ASIN = string.Empty;
}
if (dc.OwningColumn.Index == 15 && dc.Value != null)
{
item.OrderItemId = dc.Value.ToString() ?? null;
}
else if (dc.OwningColumn.Index == 15 && dc.Value == null)
{
item.OrderItemId = string.Empty;
}
}
else
{
flag = true;
break;
}
}
if (flag) continue;
items.Add(item);
}
}
private void groupBox1_Enter(object sender, EventArgs e)
{
}
private void checkBox1_CheckedChanged(object sender, EventArgs e)
{
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (checkBox1.Checked)
{
DataGridViewCheckBoxCell cell = (DataGridViewCheckBoxCell) row.Cells[0];
cell.Value = true;
}
else
{
DataGridViewCheckBoxCell cell = (DataGridViewCheckBoxCell) row.Cells[0];
cell.Value = false;
}
}
}
// This code is supposed to not allow the user to select certain cells. Not usre what if it works. Test this!
private int selectedCellRow = 0;
private int selectedCellColumn = 0;
private void dataGridView1_CellStateChanged(object sender, DataGridViewCellStateChangedEventArgs e)
{
if (e.Cell == null || e.StateChanged != DataGridViewElementStates.Selected)
return;
if (e.Cell.RowIndex == 0 || e.Cell.ColumnIndex == 0 || e.Cell.RowIndex == 1 && e.Cell.ColumnIndex == 1)
{
e.Cell.Selected = false;
dataGridView1.Rows[selectedCellRow].Cells[selectedCellColumn].Selected = true;
}
else
{
selectedCellRow = e.Cell.RowIndex;
selectedCellColumn = e.Cell.ColumnIndex;
}
}
private void button3_Click(object sender, EventArgs e)
{
items.Clear();
AddItems();
if (items.Count == 0)
{
using (new CenterWinDialog(this))
MessageBox.Show("Please Select an Order to Confirm", "No Selection", MessageBoxButtons.OK);
return;
}
XmlDocument doc = new XmlDocument();
XmlDeclaration xmlDeclaration = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
XmlElement root = doc.DocumentElement;
doc.InsertBefore(xmlDeclaration, root);
XmlElement amazonenvelope = doc.CreateElement(string.Empty, "AmazonEnvelope", string.Empty);
doc.AppendChild(amazonenvelope);
amazonenvelope.SetAttribute("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance");
amazonenvelope.SetAttribute("noNamespaceSchemaLocation", "http://www.w3.org/2001/XMLSchema-instance", "amzn-envelope.xsd");
XmlElement header = doc.CreateElement(string.Empty, "Header", string.Empty);
amazonenvelope.AppendChild(header);
XmlElement docversion = doc.CreateElement(string.Empty, "DocumentVersion", string.Empty);
XmlText text1 = doc.CreateTextNode("1.01");
docversion.AppendChild(text1);
header.AppendChild(docversion);
XmlElement merchantidentifier = doc.CreateElement(string.Empty, "MerchantIdentifier", string.Empty);
XmlText text2 = doc.CreateTextNode("A21KIG2SZJEA1Y");
merchantidentifier.AppendChild(text2);
header.AppendChild(merchantidentifier);
XmlElement messageType = doc.CreateElement(string.Empty, "MessageType", string.Empty);
XmlText text3 = doc.CreateTextNode("OrderFulfillment");
messageType.AppendChild(text3);
amazonenvelope.AppendChild(messageType);
XmlElement message = doc.CreateElement(string.Empty, "Message", string.Empty);
amazonenvelope.AppendChild(message);
for(int i = 0; i < items.Count; i++)
{
XmlElement messageId = doc.CreateElement(string.Empty, "MessageID", string.Empty);
XmlText text4 = doc.CreateTextNode("1");
messageId.AppendChild(text4);
message.AppendChild(messageId);
XmlElement orderFulfillment = doc.CreateElement(string.Empty, "OrderFulfillment", string.Empty);
message.AppendChild(orderFulfillment);
XmlElement amazonOrderId = doc.CreateElement(string.Empty, "AmazonOrderID", string.Empty);
XmlText text5 = doc.CreateTextNode(items[i].AmazonOrderId);
amazonOrderId.AppendChild(text5);
orderFulfillment.AppendChild(amazonOrderId);
XmlElement fulfillmentDate = doc.CreateElement(string.Empty, "FulfillmentDate", string.Empty);
XmlText text6 = doc.CreateTextNode(DateTime.UtcNow.ToString("s") + "Z");
fulfillmentDate.AppendChild(text6);
orderFulfillment.AppendChild(fulfillmentDate);
XmlElement fulfillmentData = doc.CreateElement(string.Empty, "FulfillmentData", string.Empty);
orderFulfillment.AppendChild(fulfillmentData);
XmlElement carrierCode = doc.CreateElement(string.Empty, "CarrierCode", string.Empty);
XmlText text7 = doc.CreateTextNode("UPS");
carrierCode.AppendChild(text7);
fulfillmentData.AppendChild(carrierCode);
XmlElement shippingMethod = doc.CreateElement(string.Empty, "ShippingMethod", string.Empty);
XmlText text8 = doc.CreateTextNode("Ground");
shippingMethod.AppendChild(text8);
fulfillmentData.AppendChild(shippingMethod);
XmlElement trackingNumber = doc.CreateElement(string.Empty, "ShipperTrackingNumber", string.Empty);
if (items[i].TrackingNumber.Count() != 19)
{
using (new CenterWinDialog(this))
MessageBox.Show("Invalid Tracking Number. Please enter a valid number.", "Invalid Tracking Info", MessageBoxButtons.OK);
return;
}
XmlText text9 = doc.CreateTextNode(items[i].TrackingNumber);
trackingNumber.AppendChild(text9);
fulfillmentData.AppendChild(trackingNumber);
XmlElement Item = doc.CreateElement(string.Empty, "Item", string.Empty);
orderFulfillment.AppendChild(Item);
XmlElement amazonOrderItem = doc.CreateElement(string.Empty, "AmazonOrderItemCode", string.Empty);
XmlText text10 = doc.CreateTextNode(items[i].OrderItemId);
amazonOrderItem.AppendChild(text10);
Item.AppendChild(amazonOrderItem);
XmlElement quantitynode = doc.CreateElement(string.Empty, "Quantity", string.Empty);
XmlText text11 = doc.CreateTextNode(items[i].Quantity);
quantitynode.AppendChild(text11);
Item.AppendChild(quantitynode);
}
doc.Save(
@"C:\Users\Daniel\Documents\Visual Studio 2015\Projects\MWSAutomate\MWSAutomate\bin\Debug\Feed.xml");
MarketplaceWebServiceSamples Order = new MarketplaceWebServiceSamples();
Order.ConfirmOrders();
using (new CenterWinDialog(this))
MessageBox.Show("Success! Shipment Confirmed.", "Confirm Orders", MessageBoxButtons.OK);
}
private void pictureBox1_Click(object sender, EventArgs e)
{
}
private void pictureBox1_Click_1(object sender, EventArgs e)
{
}
}
}
ProgramBLL.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using MarketplaceWebServiceOrders;
using MarketplaceWebServiceOrders.Model;
using System.IO;
using System.Linq;
using System.Net;
using System.Windows.Forms;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
namespace MWSAutomate
{
public class ProgramBLL
{
public void BusinessLogic()
{
const string accessKey = "acesskey";
const string secretKey = "secretkey";
const string applicationName = "MWS Automate";
const string applicationVersion = "1.0";
const string sellerid = "sellerid";
const string serviceUrl = "https://mws.amazonservices.com/Orders/2013-09-01";
MarketplaceWebServiceOrdersConfig config = new MarketplaceWebServiceOrdersConfig
{
ServiceURL = serviceUrl
};
MarketplaceWebServiceOrdersClient client = new MarketplaceWebServiceOrdersClient(accessKey, secretKey,
applicationName, applicationVersion, config);
ProgramBLL sample = new ProgramBLL(client);
try
{
XNamespace ns = "https://mws.amazonservices.com/Orders/2013-09-01";
}
catch (MarketplaceWebServiceOrdersException ex)
{
// Exception properties are important for diagnostics.
ResponseHeaderMetadata rhmd = ex.ResponseHeaderMetadata;
Console.WriteLine("Service Exception:");
if (rhmd != null)
{
Console.WriteLine("RequestId: " + rhmd.RequestId);
Console.WriteLine("Timestamp: " + rhmd.Timestamp);
}
Console.WriteLine("Message: " + ex.Message);
Console.WriteLine("StatusCode: " + ex.StatusCode);
Console.WriteLine("ErrorCode: " + ex.ErrorCode);
Console.WriteLine("ErrorType: " + ex.ErrorType);
throw ex;
}
}
private static DataSet GetAmazonProductTable(string connectionstring)
{
SqlConnection conn = null;
using (conn = new SqlConnection(connectionstring))
{
conn.Open();
SqlCommand cmd = new SqlCommand("usp_AmazonProductTitle", conn);
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet dataset = new DataSet();
da.Fill(dataset, "First Table");
da.SelectCommand.CommandText = "usp_AmazonPackage";
da.Fill(dataset, "Second Table");
return dataset;
}
}
}
private static int Print(Excel.Worksheet xlWorksheet, List<CompleteOrderDetails> items, int num, DataSet dataset, Excel.Application xlApp, Excel.Workbook xlWorkbook, Excel.Sheets xlSheets)
{
Excel.Range invoicenum = (Excel.Range) xlWorksheet.get_Range("NO", "NO");
Excel.Range AddressLine1 = (Excel.Range) xlWorksheet.get_Range("data5", "data5");
Excel.Range AddressLine2 = (Excel.Range) xlWorksheet.get_Range("E13", "E13");
Excel.Range AddressLine3 = (Excel.Range) xlWorksheet.get_Range("E14", "E14");
Excel.Range AddressLine4 = (Excel.Range) xlWorksheet.get_Range("E15", "E15");
Excel.Range FinalShipment = (Excel.Range) xlWorksheet.get_Range("E37", "E37");
Excel.Range UN = (Excel.Range) xlWorksheet.get_Range("data18", "data18");
Excel.Range HM = (Excel.Range) xlWorksheet.get_Range("J20", "J20");
Excel.Range HazardCode = (Excel.Range) xlWorksheet.get_Range("data21", "data21");
Excel.Range Package = (Excel.Range) xlWorksheet.get_Range("data24", "data24");
Excel.Range FullName = (Excel.Range) xlWorksheet.get_Range("E23", "E23");
Excel.Range Box = (Excel.Range) xlWorksheet.get_Range("E35", "E35");
Excel.Range Gallon = (Excel.Range) xlWorksheet.get_Range("E36", "E36");
Excel.Range quantity = (Excel.Range) xlWorksheet.get_Range("data17", "data17");
Excel.Range unitprice = (Excel.Range) xlWorksheet.get_Range("data19", "data19");
Excel.Range weight = (Excel.Range) xlWorksheet.get_Range("I20", "I20");
Excel.Range total = (Excel.Range) xlWorksheet.get_Range("L20", "L20");
Excel.Range unitship = (Excel.Range) xlWorksheet.get_Range("data28", "data28");
Excel.Range totalship = (Excel.Range) xlWorksheet.get_Range("L25", "L25");
Excel.Range sourceRange = (Excel.Range)xlWorksheet.get_Range("data5", "E15");
Excel.Range destinationRange = (Excel.Range)xlWorksheet.get_Range("J12", "J15");
Excel.Range date = (Excel.Range) xlWorksheet.get_Range("E17", "E17");
int i = 0;
foreach (var order in items)
{
string firstline = WebUtility.HtmlDecode(order.Name);
AddressLine1.Value2 = firstline.ToUpper();
string a = WebUtility.HtmlDecode(order.AddressLine1);
AddressLine2.Value2 = a.ToUpper();
AddressLine2.Value2 = a.ToUpper();
date.Value2 = DateTime.Now.ToString();
invoicenum.Value2 = "M" + num;
bool flag = false;
for (int j = 0; j < dataset.Tables[0].Rows.Count; j++)
{
if (string.Equals(order.ASIN, dataset.Tables[0].Rows[j][0].ToString(),
StringComparison.CurrentCultureIgnoreCase))
{
UN.Value2 = dataset.Tables[0].Rows[j][2] == DBNull.Value
? dataset.Tables[0].Rows[j][1]
: dataset.Tables[0].Rows[j][2] + ", " + dataset.Tables[0].Rows[j][1];
HM.Value2 = dataset.Tables[0].Rows[j][2] == DBNull.Value ? "" : "X";
if (dataset.Tables[0].Rows[j][3] == DBNull.Value)
HazardCode.Value2 = "NON-DOT REGULATED";
else
{
HazardCode.Value2 = dataset.Tables[0].Rows[j][4] == DBNull.Value
? dataset.Tables[0].Rows[j][3] + ", PG " + dataset.Tables[0].Rows[j][5]
: dataset.Tables[0].Rows[j][3] + ", " + dataset.Tables[0].Rows[j][4] + ", PG " +
dataset.Tables[0].Rows[j][5];
}
FullName.Value2 = dataset.Tables[0].Rows[j][6];
var number = int.Parse(order.Quantity);
for (int k = 0; k < dataset.Tables[1].Rows.Count; k++)
{
if (string.Equals(order.ASIN, dataset.Tables[1].Rows[k][0].ToString(),
StringComparison.CurrentCultureIgnoreCase))
{
if (string.Equals(order.Quantity, dataset.Tables[1].Rows[k][2].ToString(),
StringComparison.CurrentCultureIgnoreCase))
{
Box.Value2 = dataset.Tables[1].Rows[k][1];
Package.Value2 = dataset.Tables[1].Rows[k][4];
Gallon.Value2 = dataset.Tables[1].Rows[k][3] == DBNull.Value
? ""
: dataset.Tables[1].Rows[k][3];
break;
}
}
if (k == (dataset.Tables[1].Rows.Count - 1))
{
MessageBox.Show(
string.Format(
"The Product: {0} has produced an error (No matching reference quantity in database). Proceed to next order.",
dataset.Tables[0].Rows[j][0]), "Invoice Write Error", MessageBoxButtons.OK);
flag = true;
break;
}
}
if (flag) break;
if (dataset.Tables[0].Rows[j][8] == null)
{
weight.Value2 = "";
}
weight.Value2 = int.Parse(dataset.Tables[0].Rows[j][8].ToString())*number;
decimal operand1 = decimal.Parse(order.ItemPrice);
decimal operand2 = int.Parse(order.Quantity);
unitprice.Value2 = operand1/operand2;
total.Value2 = order.ItemPrice;
quantity.Value2 = order.Quantity;
unitship.Value2 = order.ShippingPrice;
totalship.Value2 = order.ShippingPrice;
break;
}
if (j == (dataset.Tables[0].Rows.Count -1))
{
MessageBox.Show(
string.Format(
"The Product: {0} is not in the database. Proceed to next order and update database for future orders.",
order.Title.ToString()), "Invoice Write Error", MessageBoxButtons.OK);
flag = true;
}
}
if (flag)
{
i++;
continue;
}
if (order.AddressLine2 != null)
{
AddressLine3.Value2 = order.AddressLine2.ToUpper();
AddressLine4.Value2 = order.City.ToUpper() + ", " + order.StateOrRegion.ToUpper() + " " + order.PostalCode;
}
else
{
AddressLine3.Value2 = order.City.ToUpper() + ", " + order.StateOrRegion.ToUpper() + " " + order.PostalCode;
AddressLine4.Value2 = "";
}
FinalShipment.Value2 = "FINAL SHIPMENT TO " + order.City.ToUpper() + ", " + order.StateOrRegion.ToUpper();
sourceRange.Copy(Type.Missing);
destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValuesAndNumberFormats, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
xlWorksheet.PrintOutEx();
xlWorksheet.PrintOutEx();
xlWorksheet.PrintOutEx();
num++;
}
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(xlSheets);
Marshal.FinalReleaseComObject(xlWorksheet);
xlWorkbook.Close(false, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
if (i == items.Count) return 0;
return 1;
}
private static Excel.Sheets GetWorksheet(Excel.Application xlApp, out Excel.Worksheet xlWorksheet, out Excel.Workbook xlWorkbook)
{
var mySheet = Path.Combine(Directory.GetCurrentDirectory(), "Isopropanol - Tech Grade.xlsx");
xlWorkbook = xlApp.Workbooks.Open(mySheet);
Excel.Sheets xlSheets = xlWorkbook.Worksheets;
string currentSheet = "Invoice";
xlWorksheet = (Excel.Worksheet) xlSheets.get_Item(currentSheet);
return xlSheets;
}
private static void StartExcel(out Excel.Application xlApp)
{
xlApp = new Excel.Application();
xlApp.Visible = false;
}
public int WriteInvoice(ProgramBLL sample, XNamespace ns, string connectionstring, int num, List<CompleteOrderDetails> items )
{
Excel.Application xlApp;
Excel.Worksheet xlWorksheet;
Excel.Workbook xlWorkbook;
StartExcel(out xlApp);
var xlSheets = GetWorksheet(xlApp, out xlWorksheet, out xlWorkbook);
DataSet dataset = GetAmazonProductTable(connectionstring);
int status = Print(xlWorksheet, items, num, dataset, xlApp, xlWorkbook, xlSheets);
return status;
}
public IEnumerable<CompleteOrderDetails> StoreNewOrders(ProgramBLL sample, XNamespace ns)
{
List<Order> ids;
XDocument xmlFragment1;
// IEnumerable<CompleteOrderDetails> orders = new IEnumerable<CompleteOrderDetails>();
IEnumerable<CompleteOrderDetails> orders = Enumerable.Empty<CompleteOrderDetails>();
var doc = CreateFragments(sample, ns, out ids, out xmlFragment1);
if (ids == null)
{
return orders;
}
LoopAmazonOrderId(ids, sample, xmlFragment1, ns);
JoinDetails(doc, ns, xmlFragment1, out orders);
ExecuteSqlTransaction(orders);
return orders;
}
public static void ExecuteSqlTransaction(IEnumerable<CompleteOrderDetails> orders)
{
string connectionstring = null;
SqlConnection conn;
SqlTransaction transaction;
connectionstring = "Data Source = ./SQLEXPRESS; Initial Catalog = Alliance; User ID = userid; Password = password";
using (conn = new SqlConnection(connectionstring))
{
conn.Open();
transaction = conn.BeginTransaction("Transaction");
string customerInsert =
"INSERT INTO AmazonCustomer (Name, AddressLine1, AddressLine2, AddressLine3, City, StateOrRegion, AmazonOrderId, PostalCode, Title, ItemPrice, ShippingPrice, Quantity, ASIN) VALUES (@Name, @AddressLine1, @AddressLine2, @AddressLine3, @City, @StateOrRegion, @AmazonOrderId, @PostalCode, @Title, @ItemPrice, @ShippingPrice, @Quantity, @ASIN)";
using (SqlCommand query = new SqlCommand(customerInsert))
{
query.Connection = conn;
query.Transaction = transaction;
query.Parameters.Add("@AmazonOrderId", SqlDbType.NVarChar, 150);
query.Parameters.Add("@Name", SqlDbType.NVarChar, 150);
query.Parameters.Add("@AddressLine1", SqlDbType.NVarChar, 150);
query.Parameters.Add("@AddressLine2", SqlDbType.NVarChar, 150);
query.Parameters.Add("@AddressLine3", SqlDbType.NVarChar, 150);
query.Parameters.Add("@City", SqlDbType.NVarChar, 150);
query.Parameters.Add("@StateOrRegion", SqlDbType.NVarChar, 150);
query.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 150);
query.Parameters.Add("@Title", SqlDbType.NVarChar, 150);
query.Parameters.Add("@ItemPrice", SqlDbType.NVarChar, 150);
query.Parameters.Add("@ShippingPrice", SqlDbType.NVarChar, 150);
query.Parameters.Add("@Quantity", SqlDbType.NVarChar, 150);
query.Parameters.Add("@ASIN", SqlDbType.NVarChar, 150);
try
{
foreach (var order in orders)
{
query.Parameters["@AmazonOrderId"].Value = order.AmazonOrderId ?? Convert.DBNull;
query.Parameters["@Name"].Value = order.Name ?? Convert.DBNull;
query.Parameters["@AddressLine1"].Value = order.AddressLine1 ?? Convert.DBNull;
query.Parameters["@AddressLine2"].Value = order.AddressLine2 ?? Convert.DBNull;
query.Parameters["@AddressLine3"].Value = order.AddressLine3 ?? Convert.DBNull;
query.Parameters["@City"].Value = order.City ?? Convert.DBNull;
query.Parameters["@StateOrRegion"].Value = order.StateOrRegion ?? Convert.DBNull;
query.Parameters["@PostalCode"].Value = order.PostalCode ?? Convert.DBNull;
query.Parameters["@Title"].Value = order.Title ?? Convert.DBNull;
query.Parameters["@ItemPrice"].Value = order.ItemPrice ?? Convert.DBNull;
query.Parameters["@ShippingPrice"].Value = order.ShippingPrice ?? Convert.DBNull;
query.Parameters["@Quantity"].Value = order.Quantity ?? Convert.DBNull;
query.Parameters["@ASIN"].Value = order.ASIN ?? Convert.DBNull;
query.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
}
public static XDocument CreateFragments(ProgramBLL sample, XNamespace ns, out List<Order> ids,
out XDocument xmlFragment1)
{
IMWSResponse response = sample.InvokeListOrders();
var responseXml = response.ToXML();
XDocument doc = System.Xml.Linq.XDocument.Parse(responseXml);
ids = GatherAmazonOrderId(sample, ns, ref responseXml);
xmlFragment1 = System.Xml.Linq.XDocument.Parse(responseXml);
return doc;
}
public static List<Order> GatherAmazonOrderId(ProgramBLL sample, XNamespace ns, ref string responseXml)
{
IMWSResponse response;
List<Order> ids = sample.ParseXmlOrders(responseXml, ns);
response = sample.InvokeListOrderItems(ids, 0);
if (response == null)
return null;
responseXml = response.ToXML();
return ids;
}
public static void JoinDetails(XDocument doc, XNamespace ns, XDocument xmlFragment1,
out IEnumerable<CompleteOrderDetails> orders)
{
var shippinginfo = GetShippingDetails(doc, ns);
var orderinfo = GetOrderDetails(xmlFragment1, ns);
orders = from o in shippinginfo
join c in orderinfo
on o.AmazonOrderId equals c.AmazonOrderId
select new CompleteOrderDetails()
{
AmazonOrderId = o.AmazonOrderId,
Name = o.Name,
AddressLine1 = o.AddressLine1,
AddressLine2 = o.AddressLine2,
AddressLine3 = o.AddressLine3,
City = o.City,
PostalCode = o.PostalCode,
StateOrRegion = o.StateOrRegion,
Title = c.Title,
ItemPrice = c.ItemPrice,
Quantity = c.Quantity,
ShippingPrice = c.ShippingPrice,
ASIN = c.ASIN,
OrderItemId = c.OrderItemId
};
}
public static void LoopAmazonOrderId(List<Order> ids, ProgramBLL sample, XDocument xmlFragment1, XNamespace ns)
{
IMWSResponse response;
string responseXml;
for (int i = 1; i < ids.Count; i++)
{
response = sample.InvokeListOrderItems(ids, i);
responseXml = response.ToXML();
XDocument xmlFragment2 = XDocument.Parse(responseXml);
xmlFragment1.Descendants(ns + "ListOrderItemsResult")
.LastOrDefault()
.AddAfterSelf(xmlFragment2.Descendants((ns + "ListOrderItemsResult")));
}
}
public static IEnumerable<Shipping> GetShippingDetails(XContainer docAddress, XNamespace ns)
{
return from address in docAddress.Descendants(ns + "Order")
select new Shipping()
{
Name = (string) address.Element(ns + "ShippingAddress").Element(ns + "Name"),
AddressLine1 = (string) address.Element(ns + "ShippingAddress").Element(ns + "AddressLine1"),
AddressLine2 = (string) address.Element(ns + "ShippingAddress").Element(ns + "AddressLine2"),
AddressLine3 = (string) address.Element(ns + "ShippingAddress").Element(ns + "AddressLine3"),
City = (string) address.Element(ns + "ShippingAddress").Element(ns + "City"),
StateOrRegion = (string) address.Element(ns + "ShippingAddress").Element(ns + "StateOrRegion"),
AmazonOrderId = (string) address.Element(ns + "AmazonOrderId"),
PostalCode = (string) address.Element(ns + "ShippingAddress").Element(ns + "PostalCode"),
};
}
public static IEnumerable<Order> GetOrderDetails(XContainer doc, XNamespace ns)
{
return from product in doc.Descendants(ns + "ListOrderItemsResult")
from order in product.Element(ns + "OrderItems").Elements(ns + "OrderItem")
select new Order()
{
Title = (string) order.Element(ns + "Title"),
ItemPrice = (string) order.Element(ns + "ItemPrice").Element(ns + "Amount"),
ShippingPrice = (string) order.Element(ns + "ShippingPrice").Element(ns + "Amount"),
Quantity = (string) order.Element(ns + "QuantityOrdered"),
AmazonOrderId = (string) product.Element(ns + "AmazonOrderId"),
ASIN = (string) order.Element(ns + "ASIN"),
OrderItemId = (string) order.Element(ns + "OrderItemId")
};
}
public List<Order> ParseXmlOrders(string xmlresponse, XNamespace ns)
{
List<Order> idList =
(
from e in XDocument.Parse(xmlresponse).Descendants(ns + "Order")
select new Order
{
AmazonOrderId = (string) e.Element(ns + "AmazonOrderId"),
}
).ToArray().ToList();
return idList;
}
public ListOrdersResponse InvokeListOrders()
{
// Create a request.
ListOrdersRequest request = new ListOrdersRequest();
string sellerId = 'sellerid';
request.SellerId = sellerId;
string mwsAuthToken = "example";
request.MWSAuthToken = mwsAuthToken;
string format = "MMM d, yyyy h:mm:ss tt PDT";
CultureInfo provider = CultureInfo.InvariantCulture;
DateTime now = DateTime.Now.Add(new TimeSpan(0, -10, 0));
DateTime createdAfter = DateTime.ParseExact("Jan 26, 2016 2:42:18 PM PDT", format, provider);
request.CreatedAfter = createdAfter;
request.CreatedBefore = now;
List<string> orderStatus = new List<string>(new string[] {"Unshipped", "PartiallyShipped"});
request.OrderStatus = orderStatus;
List<string> marketplaceId = new List<string>(new string[] {"marketplaceid});
request.MarketplaceId = marketplaceId;
decimal maxResultsPerPage = 11;
request.MaxResultsPerPage = maxResultsPerPage;
return client.ListOrders(request);
}
public ListOrderItemsResponse InvokeListOrderItems(List<Order> idList, int i)
{
// Create a request.
ListOrderItemsRequest request = new ListOrderItemsRequest();
string sellerId = "sellerid";
request.SellerId = sellerId;
string mwsAuthToken = "example";
request.MWSAuthToken = mwsAuthToken;
if (idList.Count == 0)
{
return null;
}
string amazonOrderId = idList[i].AmazonOrderId;
request.AmazonOrderId = amazonOrderId;
return client.ListOrderItems(request);
}
private readonly MarketplaceWebServiceOrders.MarketplaceWebServiceOrders client;
public ProgramBLL(MarketplaceWebServiceOrders.MarketplaceWebServiceOrders client)
{
this.client = client;
}
}
}
public class Shipping
{
public string Name { get; set; }
public string AddressLine1 { get; set; }
public string AddressLine2 { get; set; }
public string AddressLine3 { get; set; }
public string City { get; set; }
public string StateOrRegion { get; set; }
public string AmazonOrderId { get; set; }
public string PostalCode { get; set; }
}
public class Order
{
public string Title { get; set; }
public string ItemPrice { get; set; }
public string ShippingPrice { get; set; }
public string Quantity { get; set; }
public string AmazonOrderId { get; set; }
public string ASIN { get; set; }
public string OrderItemId { get; set; }
}
public class CompleteOrderDetails
{
public string Name { get; set; }
public string AddressLine1 { get; set; }
public string AddressLine2 { get; set; }
public string AddressLine3 { get; set; }
public string City { get; set; }
public string StateOrRegion { get; set; }
public string AmazonOrderId { get; set; }
public string PostalCode { get; set; }
public string Title { get; set; }
public string ItemPrice { get; set; }
public string ShippingPrice { get; set; }
public string Quantity { get; set; }
public string TrackingNumber { get; set; }
public string ASIN { get; set; }
public string OrderItemId { get; set; }
}
