/* a method that return all shipped order */
public GiantTigerValues[] GetAllShippedOrder()
{
// local field for storing shipment value
List <GiantTigerValues> list = new List <GiantTigerValues>();
// grab all shipped
using (SqlConnection connection = new SqlConnection(Credentials.OrderHubCon))
{
string date = DateTime.Today.ToString("yyyy-MM-dd");
SqlCommand command = new SqlCommand("SELECT PoNumber, TrackingNumber, SelfLink FROM GiantTiger_Order " +
"WHERE EndofDay != 1 AND TrackingNumber != '' AND (ShipDate = \'" + date + "\' OR CompleteDate = \'" + date + "\')", connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
GiantTigerValues value = new GiantTigerValues
{
PoNumber = reader.GetString(0),
Package =
{
TrackingNumber = reader.GetString(1),
SelfLink = reader.GetString(2)
}
};
list.Add(value);
}
}
return(list.ToArray());
}
/* a method that return all the new order values */
public GiantTigerValues[] GetAllNewOrder()
{
// local field for storing order values
List <GiantTigerValues> list = new List <GiantTigerValues>();
DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection(Credentials.OrderHubCon))
{
SqlCommand command = new SqlCommand("SELECT PoNumber, WebOrderNo, OrderDate, ShipMethod, ShipToFirstName, ShipToLastName, ShipToStreet, ShipToAddress2, ShipToCity, ShipToZip, ShipToPhone, " +
"ShipToStoreNumber, OmsOrderNumber " +
"FROM GiantTiger_Order WHERE Complete ='False' ORDER BY PoNumber", connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
GiantTigerValues value = new GiantTigerValues();
string poNumber = reader.GetString(0);
value.PoNumber = poNumber;
value.WebOrderNo = reader.GetString(1);
value.OrderDate = reader.GetDateTime(2);
value.ShipMethod = reader.GetString(3);
value.ShipTo.Name = reader.GetString(4) + ' ' + reader.GetString(5);
value.ShipTo.Address1 = reader.GetString(6);
value.ShipTo.Address2 = reader.GetString(7);
value.ShipTo.City = reader.GetString(8);
value.ShipTo.PostalCode = reader.GetString(9);
value.ShipTo.DayPhone = reader.GetString(10);
value.StoreNumber = reader.GetString(11);
value.OmsOrderNumber = reader.GetString(12);
SqlDataAdapter adatper = new SqlDataAdapter("SELECT VendorSku, HostSku, Quantity, UnitCost, ClientItemId " +
"FROM GiantTiger_Order_Item WHERE PoNumber = \'" + poNumber + '\'', connection);
adatper.Fill(table);
for (int i = 0; i < table.Rows.Count; i++)
{
value.VendorSku.Add(table.Rows[i][0].ToString());
value.HostSku.Add(table.Rows[i][1].ToString());
value.Quantity.Add(Convert.ToInt32(table.Rows[i][2]));
value.UnitCost.Add(Convert.ToDouble(table.Rows[i][3]));
value.ClientItemId.Add(table.Rows[i][4].ToString());
}
table.Reset();
list.Add(value);
}
}
return(list.ToArray());
}
/* second version -> take from database */
public GiantTigerValues GenerateValue(string targetPo)
{
// local field for storing values
GiantTigerValues value = new GiantTigerValues();
using (SqlConnection connection = new SqlConnection(Credentials.OrderHubCon))
{
SqlCommand command = new SqlCommand("SELECT PoNumber, WebOrderNo, OrderDate, ShipMethod, ShipToFirstName, ShipToLastName, ShipToStreet, ShipToAddress2, ShipToCity, ShipToState, ShipToZip, ShipToPhone, " +
"ShipToStoreNumber, OmsOrderNumber, TrackingNumber, SelfLink, LabelLink " +
"FROM GiantTiger_Order WHERE PoNumber = \'" + targetPo + '\'', connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
reader.Read();
string poNumber = reader.GetString(0);
value.PoNumber = poNumber;
value.WebOrderNo = reader.GetString(1);
value.OrderDate = reader.GetDateTime(2);
value.ShipMethod = reader.GetString(3);
value.ShipTo.Name = reader.GetString(4) + ' ' + reader.GetString(5);
value.ShipTo.Address1 = reader.GetString(6);
value.ShipTo.Address2 = reader.GetString(7);
value.ShipTo.City = reader.GetString(8);
value.ShipTo.State = reader.GetString(9);
value.ShipTo.PostalCode = reader.GetString(10);
value.ShipTo.DayPhone = reader.GetString(11);
value.StoreNumber = reader.GetString(12);
value.OmsOrderNumber = reader.GetString(13);
value.Package.TrackingNumber = reader.GetString(14);
value.Package.SelfLink = reader.GetString(15);
value.Package.LabelLink = reader.GetString(16);
SqlDataAdapter adatper = new SqlDataAdapter("SELECT VendorSku, HostSku, Quantity, UnitCost, ClientItemId " +
"FROM GiantTiger_Order_Item WHERE PoNumber = \'" + poNumber + "\' ORDER BY ClientItemId", connection);
DataTable table = new DataTable();
adatper.Fill(table);
for (int i = 0; i < table.Rows.Count; i++)
{
value.VendorSku.Add(table.Rows[i][0].ToString());
value.HostSku.Add(table.Rows[i][1].ToString());
value.Quantity.Add(Convert.ToInt32(table.Rows[i][2]));
value.UnitCost.Add(Convert.ToDouble(table.Rows[i][3]));
value.ClientItemId.Add(table.Rows[i][4].ToString());
}
}
return(value);
}
/* a method that get all new order on the server and update to the database */
public void GetOrder()
{
// get all the new file on the order directory to local new order storing directory
IEnumerable <string> orderCheck = CheckOrderFile();
GetOrder(newOrderDir, orderCheck);
// return the po number that haved not been processed
Dictionary <string, string> dic = GetPoNumber();
dic = CheckOrder(dic);
// get information for each unprocessed order and update the them to the database
foreach (KeyValuePair <string, string> keyValue in dic)
{
GiantTigerValues value = GenerateValue(keyValue.Key, keyValue.Value);
AddNewOrder(value);
}
}
/* a method that add a new order to database */
private static void AddNewOrder(GiantTigerValues value)
{
using (SqlConnection connection = new SqlConnection(Credentials.OrderHubCon))
{
// add new order to database
string firstName = value.ShipTo.Name.Remove(value.ShipTo.Name.IndexOf(' '));
string lastName = value.ShipTo.Name.Substring(value.ShipTo.Name.IndexOf(' '));
SqlCommand command = new SqlCommand("INSERT INTO GiantTiger_Order " +
"(PoNumber, WebOrderNo, OrderDate, ShipMethod, ShipToFirstName, ShipToLastName, ShipToStreet, ShipToAddress2, ShipToCity, ShipToState, ShipToZip, ShipToCountry, ShipToPhone, ShipToStoreNumber, OmsOrderNumber, Complete, TrackingNumber, SelfLink, LabelLink, EndofDay) Values " +
"(\'" + value.PoNumber + "\',\'" + value.WebOrderNo + "\',\'" + value.OrderDate.ToString("yyyy-MM-dd") + "\',\'" + value.ShipMethod + "\',\'" + firstName.Replace("'", "''") + "\',\'" + lastName.Replace("'", "''") + "\',\'" + value.ShipTo.Address1.Replace("'", "''") + "\',\'" + value.ShipTo.Address2.Replace("'", "''") + "\',\'" + value.ShipTo.City.Replace("'", "''") +
"\',\'" + value.ShipTo.State + "\',\'" + value.ShipTo.PostalCode + "\','Canada',\'" + value.ShipTo.DayPhone + "\',\'" + value.StoreNumber + "\',\'" + value.OmsOrderNumber + "\','False',\'" + value.Package.TrackingNumber + "\',\'" + value.Package.LabelLink + "\',\'" + value.Package.LabelLink + "\','False')", connection);
connection.Open();
command.ExecuteNonQuery();
// add each item for the order to database
for (int i = 0; i < value.VendorSku.Count; i++)
{
command.CommandText = "INSERT INTO GiantTiger_Order_Item " +
"(PoNumber, VendorSku, HostSku, Quantity, UnitCost, ClientItemId, Shipped, Cancelled) Values" +
"(\'" + value.PoNumber + "\',\'" + value.VendorSku[i] + "\',\'" + value.HostSku[i] + "\'," + value.Quantity[i] + ',' + value.UnitCost[i] + ",\'" + value.ClientItemId[i] + "\','False', 'False')";
command.ExecuteNonQuery();
}
}
}
/* a method that save the packing slip pdf */
public static void CreatePackingSlip(GiantTigerValues value, int[] cancelIndex, bool preview)
{
// the case if all of the items in the order are cancelled -> don't need to print the packing slip
if (cancelIndex.Length >= value.VendorSku.Count)
{
return;
}
// first check if the save directory exist -> if not create it
if (!File.Exists(SavePath))
{
Directory.CreateDirectory(SavePath);
}
// initialize fields
Document doc = new Document(PageSize.LETTER, 0, 0, 0, 0);
string file = SavePath + "\\" + value.PoNumber + ".pdf";
PdfWriter writer = PdfWriter.GetInstance(doc, new FileStream(file, FileMode.Create));
// open the documents
doc.Open();
PdfContentByte contentByte = writer.DirectContent;
PdfContentByte draw = writer.DirectContent;
#region Logo and Barcode Set Up
// add giant tiger logo
Image logo = Image.GetInstance(Properties.Resources.giantTigerPackSlip, System.Drawing.Imaging.ImageFormat.Png);
logo.ScalePercent(20f);
logo.SetAbsolutePosition(40f, doc.PageSize.Height - 100f);
doc.Add(logo);
// add barcode
Barcode39 barcode39 = new Barcode39
{
Code = value.OmsOrderNumber,
StartStopText = false,
Font = null,
Extended = true
};
Image image = barcode39.CreateImageWithBarcode(contentByte, BaseColor.BLACK, BaseColor.BLACK);
image.ScaleAbsoluteHeight(40f);
image.SetAbsolutePosition(340f, doc.PageSize.Height - 80f);
contentByte.AddImage(image);
#endregion
// initialize local fields for text
BaseFont baseFont = BaseFont.CreateFont(BaseFont.HELVETICA, BaseFont.CP1252, false);
BaseFont boldFont = BaseFont.CreateFont(BaseFont.HELVETICA_BOLD, BaseFont.CP1252, false);
CMYKColor white = new CMYKColor(0f, 0f, 0f, 0f);
CMYKColor black = new CMYKColor(0f, 0f, 0f, 1f);
ColumnText ct = new ColumnText(draw);
#region Sold To
// sold to
Phrase text = new Phrase("SOLD TO / VENDU A", new Font(boldFont, 10));
ct.SetSimpleColumn(text, 40f, 655f, 200f, 670f, 0f, Element.ALIGN_LEFT);
ct.Go();
// sold to address
text = new Phrase(value.ShipTo.Name + '\n' + value.ShipTo.Address1 + '\n' + value.ShipTo.Address2 + '\n' + value.ShipTo.City + ", " + value.ShipTo.State + ' ' + value.ShipTo.PostalCode + "\nCanada",
new Font(baseFont, 9));
ct.SetSimpleColumn(text, 42f, 568f, 177f, 668f, 10f, Element.ALIGN_LEFT);
ct.Go();
#endregion
#region Ship To
// ship to
text = new Phrase("SHIP TO / EXPEDIE A", new Font(boldFont, 10f));
ct.SetSimpleColumn(text, 300f, 655f, 450f, 670f, 0f, Element.ALIGN_LEFT);
ct.Go();
// ship to address
text = new Phrase(value.ShipTo.Name + '\n' + value.ShipTo.Address1 + '\n' + value.ShipTo.Address2 + '\n' + value.ShipTo.City + ", " + value.ShipTo.State + ' ' + value.ShipTo.PostalCode + "\nCanada\n"
+ value.ShipTo.DayPhone, new Font(baseFont, 9f));
ct.SetSimpleColumn(text, 302f, 568f, 447f, 668f, 10f, Element.ALIGN_LEFT);
ct.Go();
#endregion
#region Draw First Box
draw.SetColorFill(black);
draw.MoveTo(40f, 580f);
draw.LineTo(doc.PageSize.Width - 40f, 580f);
draw.LineTo(doc.PageSize.Width - 40f, 543f);
draw.LineTo(40f, 543f);
draw.LineTo(40f, 580f);
draw.ClosePathFillStroke();
draw.SetColorStroke(white);
draw.MoveTo(160f, 580f);
draw.LineTo(160f, 543f);
draw.Stroke();
draw.MoveTo(280f, 580f);
draw.LineTo(280f, 543f);
draw.Stroke();
draw.MoveTo(320f, 580f);
draw.LineTo(320f, 543f);
draw.Stroke();
draw.MoveTo(500f, 580f);
draw.LineTo(500f, 543f);
draw.Stroke();
draw.SetColorStroke(black);
draw.MoveTo(40f, 543f);
draw.LineTo(40f, 530f);
draw.Stroke();
draw.MoveTo(40f, 530f);
draw.LineTo(doc.PageSize.Width - 40f, 530f);
draw.Stroke();
draw.MoveTo(doc.PageSize.Width - 40f, 530f);
draw.LineTo(doc.PageSize.Width - 40f, 543f);
draw.Stroke();
draw.MoveTo(160f, 543f);
draw.LineTo(160f, 530f);
draw.Stroke();
draw.MoveTo(280f, 543f);
draw.LineTo(280f, 530f);
draw.Stroke();
draw.MoveTo(320f, 543f);
draw.LineTo(320f, 530f);
draw.Stroke();
draw.MoveTo(500f, 543f);
draw.LineTo(500f, 530f);
draw.Stroke();
#endregion
#region Messgae in First Box
text = new Phrase("SHIPPING METHOD \\\nMODE D'EXPEDITION", new Font(boldFont, 10, Font.NORMAL, white));
ct.SetSimpleColumn(text, 40f, 550f, 160f, 580f, 11f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase("ORDER DATE \\\nDATE DE LA\nCOMMANDE", new Font(boldFont, 10, Font.NORMAL, white));
ct.SetSimpleColumn(text, 160f, 540f, 280f, 580f, 11f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase("PAGE", new Font(boldFont, 10, Font.NORMAL, new CMYKColor(0f, 0f, 0f, 0f)));
ct.SetSimpleColumn(text, 280f, 550f, 320f, 580f, 11f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase("ORDER NUMBER \\\nNUMERO DE COMMANDE", new Font(boldFont, 10, Font.NORMAL, white));
ct.SetSimpleColumn(text, 320f, 550f, 500f, 580f, 11f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase("PO NUMBER \\\nBON DE\nCOMMANDE", new Font(boldFont, 10, Font.NORMAL, white));
ct.SetSimpleColumn(text, 500f, 540f, doc.PageSize.Width - 40f, 580f, 11f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase("Canada Post Ground", new Font(baseFont, 10));
ct.SetSimpleColumn(text, 40f, 523f, 160f, 533f, 0f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase(value.OrderDate.ToString("MM/dd/yyyy"), new Font(baseFont, 10));
ct.SetSimpleColumn(text, 160f, 523f, 280f, 533f, 0f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase("1 of 1", new Font(baseFont, 10));
ct.SetSimpleColumn(text, 280f, 523f, 320f, 533f, 0f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase(value.WebOrderNo, new Font(baseFont, 10));
ct.SetSimpleColumn(text, 320f, 523f, 500f, 533f, 0f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase(value.PoNumber, new Font(baseFont, 10));
ct.SetSimpleColumn(text, 500f, 523f, doc.PageSize.Width - 40f, 533f, 0f, Element.ALIGN_CENTER);
ct.Go();
#endregion
#region Draw Second Box
draw.MoveTo(40f, 520f);
draw.LineTo(doc.PageSize.Width - 40f, 520f);
draw.LineTo(doc.PageSize.Width - 40f, 483f);
draw.LineTo(40f, 483f);
draw.LineTo(40f, 520f);
draw.ClosePathFillStroke();
draw.SetColorStroke(white);
draw.MoveTo(130f, 520f);
draw.LineTo(130f, 483f);
draw.Stroke();
draw.MoveTo(220f, 520f);
draw.LineTo(220f, 483f);
draw.Stroke();
draw.MoveTo(400f, 520f);
draw.LineTo(400f, 483f);
draw.Stroke();
draw.MoveTo(480f, 520f);
draw.LineTo(480f, 483f);
draw.Stroke();
#endregion
#region Message in Second Box
text = new Phrase("QTY ORDERED \\\nQTE\nCOMMANDEE", new Font(boldFont, 10, Font.NORMAL, white));
ct.SetSimpleColumn(text, 40f, 480f, 130f, 520f, 11f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase("ITEM \\\nARTICLE", new Font(boldFont, 10, Font.NORMAL, white));
ct.SetSimpleColumn(text, 130f, 480f, 220f, 520f, 11f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase("DESCRIPTION", new Font(boldFont, 10, Font.NORMAL, white));
ct.SetSimpleColumn(text, 220f, 480f, 400f, 520f, 11f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase("QTY SHIPPED \\\nQTE EXPEDIEE", new Font(boldFont, 10, Font.NORMAL, white));
ct.SetSimpleColumn(text, 400f, 480f, 480f, 520f, 11f, Element.ALIGN_CENTER);
ct.Go();
text = new Phrase("VENDOR SKU \\\nNO D'ARTICLE DU\nFOURNISSEUR", new Font(boldFont, 10, Font.NORMAL, white));
ct.SetSimpleColumn(text, 480f, 480f, doc.PageSize.Width - 40f, 520f, 11f, Element.ALIGN_CENTER);
ct.Go();
// item addition
draw.SetColorStroke(black);
draw.SetLineWidth(0.25f);
float height = 480f;
// adding items
for (int i = 0; i < value.VendorSku.Count; i++)
{
// if the item is cancelled, skip this item
if (cancelIndex.Any(j => i == j))
{
continue;
}
// draw box
draw.MoveTo(40f, height);
draw.LineTo(40f, height - 10f);
draw.Stroke();
draw.MoveTo(40f, height - 10f);
draw.LineTo(doc.PageSize.Width - 40f, height - 10f);
draw.Stroke();
draw.MoveTo(doc.PageSize.Width - 40f, height - 10f);
draw.LineTo(doc.PageSize.Width - 40f, height);
draw.Stroke();
draw.MoveTo(130f, height);
draw.LineTo(130f, height - 10f);
draw.Stroke();
draw.MoveTo(220f, height);
draw.LineTo(220f, height - 10f);
draw.Stroke();
draw.MoveTo(400f, height);
draw.LineTo(400f, height - 10f);
draw.Stroke();
draw.MoveTo(480f, height);
draw.LineTo(480f, height - 10f);
draw.Stroke();
// qty
text = new Phrase(value.Quantity[i].ToString(), new Font(baseFont, 10));
ct.SetSimpleColumn(text, 40f, height - 19f, 130, height - 9f, 0f, Element.ALIGN_CENTER);
ct.Go();
// item
text = new Phrase(value.ClientItemId[i], new Font(baseFont, 10));
ct.SetSimpleColumn(text, 130f, height - 19f, 220f, height - 9f, 0f, Element.ALIGN_CENTER);
ct.Go();
// description
text = new Phrase("", new Font(baseFont, 10));
ct.SetSimpleColumn(text, 220f, height - 19f, 400f, height - 9f, 0f, Element.ALIGN_CENTER);
ct.Go();
// qty shipped
text = new Phrase(value.Quantity[i].ToString(), new Font(baseFont, 10));
ct.SetSimpleColumn(text, 400f, height - 19f, 480f, height - 9f, 0f, Element.ALIGN_CENTER);
ct.Go();
// vendor sku
text = new Phrase(value.VendorSku[i], new Font(baseFont, 10));
ct.SetSimpleColumn(text, 480f, height - 19f, doc.PageSize.Width - 40f, height - 9f, 0f, Element.ALIGN_CENTER);
ct.Go();
// decrease height for next item
height -= 10f;
}
#endregion
#region Ending Boxes
#region Top Box
// restore width
draw.SetLineWidth(1f);
// draw box
draw.MoveTo(40f, 250f);
draw.LineTo(doc.PageSize.Width - 40f, 250f);
draw.LineTo(doc.PageSize.Width - 40f, 235f);
draw.LineTo(40f, 235f);
draw.LineTo(40f, 250f);
draw.ClosePathFillStroke();
// message in the box
text = new Phrase("Thank you for ordering from Giant Tiger! Merci d’avoir placé une commande chez Tigre Géant!", new Font(boldFont, 10f, Font.NORMAL, white));
ct.SetSimpleColumn(text, 40f, 224f, doc.PageSize.Width - 40f, 239f, 0f, Element.ALIGN_CENTER);
ct.Go();
#endregion
#region Bottom Box
// draw box
draw.MoveTo(40f, 225f);
draw.LineTo(doc.PageSize.Width - 40f, 225f);
draw.Stroke();
draw.MoveTo(doc.PageSize.Width - 40f, 225f);
draw.LineTo(doc.PageSize.Width - 40f, 50f);
draw.Stroke();
draw.MoveTo(doc.PageSize.Width - 40f, 50f);
draw.LineTo(40f, 50f);
draw.Stroke();
draw.MoveTo(40f, 50f);
draw.LineTo(40f, 225f);
draw.Stroke();
draw.MoveTo(doc.PageSize.Width / 2, 225f);
draw.LineTo(doc.PageSize.Width / 2, 50f);
draw.Stroke();
// message in the left box
text = new Phrase("Didn't receive your entire order or questions about your order?\n" +
"You may receive your order in separate shipments. To track your order status. Please log into My Account at gianttiger.com.\n\r" +
"Want to Return or Exchange an Item?\n" +
"If you are not satisfied with your order for any reason please contact our Customer Service Team at 1-844-99-GIANT (44268) or email [email protected]. " +
"For in-store returns or exchanges, your shipment confirmation email is required. To review our return policy, please visit gianttiger.com/ReturnPolicy.",
new Font(baseFont, 9f));
ct.SetSimpleColumn(text, 50f, 55f, doc.PageSize.Width / 2 - 10, 225f, 10f, Element.ALIGN_LEFT);
ct.Go();
// message in the right box
text = new Phrase("Votre commande est incomplète ou vous avez des questions à son sujet?\n" +
"Il se peut que vous receviez votre commande dans des envois distincts. Pour suivre l'état de votre commande, s’il vous plaît vous connecter à Mon Compte au tigregeant.com.\n\r" +
"Vous voulez retourner ou échanger un article?\nSi vous n'êtes pas satisfait avec votre achat, veuillez communiquer avec notre service à la clientèle au 1-844-99-GIANT (44268) " +
"ou envoyer un courriel à [email protected]. Les échanges et les remboursements peuvent être effectués à n’importe quel de nos magasins avec la présentation du courriel confirmant " +
"l'expédition de votre commande. Pour consulter notre politique de retour, s'il vous plaît visitez tigregeant.com/PolitiqueRetour.",
new Font(baseFont, 9f));
ct.SetSimpleColumn(text, doc.PageSize.Width / 2 + 10f, 55f, doc.PageSize.Width - 50f, 225f, 10f, Element.ALIGN_LEFT);
ct.Go();
#endregion
#endregion
doc.Close();
if (!preview)
{
return;
}
// start the pdf for previewing
if (System.Diagnostics.Process.GetProcessesByName(file).Length < 1)
{
System.Diagnostics.Process.Start(file);
}
}
/* a method that generate GiantTigerValues object for the given po number (first version -> take from local) */
public GiantTigerValues GenerateValue(string targetPo, string filePath)
{
// field for return
GiantTigerValues value = new GiantTigerValues();
using (TextFieldParser parser = new TextFieldParser(filePath))
{
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
// local field for header data
Dictionary <int, string> headerList = new Dictionary <int, string>();
// getting headers and its coressponding index
string[] fields = parser.ReadFields();
int length = fields.Length;
for (int i = 0; i < length; i++)
{
headerList.Add(i, fields[i]);
}
// getting order items
while (!parser.EndOfData)
{
// get the data for the order
string[] data = parser.ReadFields();
// the case if the order is not desired -> skip this
if (targetPo != data[0])
{
continue;
}
for (int i = 0; i < length; i++)
{
switch (headerList[i])
{
case "PO#":
value.PoNumber = data[i];
break;
case "WebOrderNo":
value.WebOrderNo = data[i];
break;
case "OrderDate":
value.OrderDate = DateTime.ParseExact(data[i], "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture);
break;
case "ShipMethod":
value.ShipMethod = data[i];
break;
case "Ship-ToFName":
value.ShipTo.Name = data[i];
break;
case "Ship-ToLName":
value.ShipTo.Name += ' ' + data[i];
break;
case "Ship-ToStreet":
value.ShipTo.Address1 = data[i];
break;
case "Ship-ToAddr2":
value.ShipTo.Address2 = data[i];
break;
case "Ship-ToCity":
value.ShipTo.City = data[i];
break;
case "ShipToState":
value.ShipTo.State = data[i];
break;
case "Ship-ToZip":
value.ShipTo.PostalCode = data[i];
break;
case "Ship-ToPhone":
value.ShipTo.DayPhone = data[i];
break;
case "Store#":
value.StoreNumber = data[i];
break;
case "VendorSKU#":
value.VendorSku.Add(data[i]);
break;
case "Quantity":
value.Quantity.Add(int.Parse(data[i]));
break;
case "UnitCost":
value.UnitCost.Add(double.Parse(data[i]));
break;
case "Item#":
value.HostSku.Add(data[i]);
break;
case "ClientItemID":
value.ClientItemId.Add(data[i]);
break;
case "OrderNo":
value.OmsOrderNumber = data[i];
break;
}
}
}
}
return(value);
}
/* a method that generate csv order and upload to the ftp server and update database */
public void GenerateCsv(GiantTigerValues value, Dictionary <int, string> cancelList)
{
// fields for database update
SqlConnection connection = new SqlConnection(Credentials.OrderHubCon);
SqlCommand command = new SqlCommand {
Connection = connection
};
connection.Open();
#region CSV Generation and Item Database Update
// fields for csv generation
const string delimiter = ",";
string[][] ship = new string[value.VendorSku.Count - cancelList.Count + 1][];
string[][] cancel = new string[cancelList.Count + 1][];
int shipIndex = 1;
int cancelIndex = 1;
// adding header
ship[0] = new[] { "PO#", "Item#", "Quantity", "shipMethod", "Tracking#", "Invoice#", "Tax", "Package Freight" };
cancel[0] = new[] { "PO#", "HostSKU#", "CancelQty", "CancelCode" };
// writing content of csv file
for (int i = 0; i < value.VendorSku.Count; i++)
{
// the case if the item is cancelled -> write it in cancel file
if (cancelList.Keys.Contains(i))
{
string cancelCode;
switch (cancelList[i])
{
case "Customer Request":
cancelCode = "CR";
break;
case "Incorrect product setup":
cancelCode = "IP";
break;
default:
cancelCode = "OS";
break;
}
cancel[cancelIndex] = new[] { value.PoNumber, value.ClientItemId[i], value.Quantity[i].ToString(), cancelCode };
cancelIndex++;
// update item to cancelled to database
command.CommandText = "UPDATE GiantTiger_Order_Item SET Cancelled = 'True' WHERE PoNumber = \'" + value.PoNumber + "\' AND ClientItemId = \'" + value.ClientItemId[i] + '\'';
command.ExecuteNonQuery();
continue;
}
ship[shipIndex] = new[] { value.PoNumber, value.ClientItemId[i], value.Quantity[i].ToString(), value.ShipMethod, value.Package.TrackingNumber, GetInvoiceNumber(), "", "" };
shipIndex++;
// update item to shipped to database
command.CommandText = "UPDATE GiantTiger_Order_Item SET Shipped = 'True' WHERE PoNumber = \'" + value.PoNumber + "\' AND ClientItemId = \'" + value.ClientItemId[i] + '\'';
command.ExecuteNonQuery();
}
#endregion
#region CSV File Export
// the case if there is cancel file -> export and upload to server
if (cancelIndex > 1)
{
// writing csv file
StringBuilder sb = new StringBuilder();
for (int i = 0; i < cancel.GetLength(0); i++)
{
sb.AppendLine(string.Join(delimiter, cancel[i]));
}
// save file to local
string path = completeOrderDir + "\\" + value.PoNumber + "_Cancel.csv";
File.WriteAllText(path, sb.ToString());
// upload file to ftp server
ftp.Upload(CANCEL_DIR + '/' + value.PoNumber + "_Cancel.csv", path);
}
// the case if there is ship file -> export and upload to server
if (shipIndex > 1)
{
// writing csv file
StringBuilder sb = new StringBuilder();
for (int i = 0; i < ship.GetLength(0); i++)
{
sb.AppendLine(string.Join(delimiter, ship[i]));
}
// save file to local
string path = completeOrderDir + "\\" + value.PoNumber + "_Ship.csv";
File.WriteAllText(path, sb.ToString());
// upload file to ftp server
ftp.Upload(SHIP_DIR + '/' + value.PoNumber + "_Ship.csv", path);
}
#endregion
// master database update
command.CommandText = "UPDATE GiantTiger_Order SET TrackingNumber = \'" + value.Package.TrackingNumber + "\', CompleteDate = \'" + DateTime.Today.ToString("yyyy-MM-dd HH:mm:ss") + "\', SelfLink = \'" + value.Package.SelfLink + "\', LabelLink = \'" + value.Package.LabelLink + "\', "
+ "Complete = 'True' WHERE PoNumber = \'" + value.PoNumber + '\'';
command.ExecuteNonQuery();
connection.Close();
}
