public void ImportOrders()
{
CustomerCollection NewCustomers = new CustomerCollection();
SalesOrderHeaderCollection NewCustomerOrders = new SalesOrderHeaderCollection();
SqlCompactConnection conn = new SqlCompactConnection();
SalesOrderDetailCollection sodCol = new SalesOrderDetailCollection();
try
{
// conn.connect();
lblStatus.Text = "Connected to mobile database";
RefreshForm();
DataTable dtSoh = new DataTable();
string sql = "select * from salesorderheader where status=1";
dtSoh = conn.GetDataTable(sql);
lblStatus.Text = "Received sales order header data (" + dtSoh.Rows.Count.ToString() + " records)";
RefreshForm();
//conn.Close();
//SalesOrderHeaderTableAdapter soh = new SalesOrderHeaderTableAdapter();
//SalesOrderDetailTableAdapter sod = new SalesOrderDetailTableAdapter();
SalesOrderHeader OrderHeader = new SalesOrderHeader();
int rows = dtSoh.Rows.Count;
if (rows == 0)
{
MessageBox.Show("There are no records to import", "MICS", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
conn.DeleteOldOrders();
return;
}
// conn = new SqlCompactConnection();
DataTable dtSod = new DataTable();
sql = "select * from salesorderdetail where salesorderid in (select salesorderid from salesorderheader where status=1)";
dtSod = conn.GetDataTable(sql);
lblStatus.Text = "Received sales order details data(" + dtSod.Rows.Count.ToString() + " records)";
RefreshForm();
int iCount = 0;
lblStatus.Text = "Copying sales order information";
RefreshForm();
progressBar1.Minimum = 1;
progressBar1.Maximum = dtSoh.Rows.Count;
foreach (DataRow dr in dtSoh.Rows)
{
OrderHeader = new SalesOrderHeader();
int OrderID = Int32.Parse(dr["SalesOrderID"].ToString());
OrderHeader.OrderDate = DateTime.Parse(dr["OrderDate"].ToString());
OrderHeader.ShipDate = DateTime.Parse(dr["ShipDate"].ToString());
OrderHeader.DueDate = DateTime.Parse(dr["DueDate"].ToString());
OrderHeader.SalesOrderNumber = dr["SalesOrderNumber"].ToString();
OrderHeader.PurchaseOrderNumber = dr["PurchaseOrderNumber"].ToString();
OrderHeader.CustomerID = Int32.Parse(dr["CustomerID"].ToString());
if (OrderHeader.CustomerID == 0)
{
//This is a new customer.
Customer NewCus = new Customer();
string comments = dr["Comment"].ToString();
//The comments has the territoryid and customer name in the format TerritoryID~CustomerName
int pos = comments.IndexOf("~");
string territory = comments.Substring(0, pos);
string CustName = comments.Substring(pos + 1);
NewCus.TerritoryID = Int32.Parse(territory);
NewCus.Name = CustName;
NewCus.AddressID = 0;
NewCus.BillingAddressID = 0;
NewCus.ModifiedDate = DateTime.Now;
OrderHeader.CustomerID = NewCus.AddCustomer(NewCus);
NewCus.CustomerID = OrderHeader.CustomerID;
NewCustomers.Add(NewCus);
}
OrderHeader.SalesPersonID = Int32.Parse(dr["SalesPersonID"].ToString());
OrderHeader.BillToAddressID = Int32.Parse(dr["BillToAddressID"].ToString());
OrderHeader.ShipToAddressID = Int32.Parse(dr["ShipToAddressID"].ToString());
OrderHeader.ShipMethodID = Int32.Parse(dr["ShipMethodID"].ToString());
OrderHeader.Status = byte.Parse(dr["Status"].ToString());
OrderHeader.SubTotal = decimal.Parse(dr["SubTotal"].ToString());
OrderHeader.TaxAmt = decimal.Parse(dr["TaxAmt"].ToString());
OrderHeader.TotalDue = decimal.Parse(dr["TotalDue"].ToString());
OrderHeader.Comment = "Mobile Order";
//save the mobile order id in currencyrateid
OrderHeader.CurrencyRateID = OrderID;
//save the mobile order id in currencyrateid
OrderHeader.CurrencyRateID = OrderID;
int ServerOrderID = OrderHeader.AddSalesOrderHeader(OrderHeader);
OrderHeader.SalesOrderID = ServerOrderID;
if (dr["CustomerID"].ToString() == "0")
{
//save orders of new customers in the collection
NewCustomerOrders.Add(OrderHeader);
}
//reset the status to 9 in the mobile
//Get order details in the mobile db
// DataTable dtSod = new DataTable();
// sql = "select * from salesorderdetail where salesorderid=" + OrderID.ToString();
// dtSod = conn.GetDataTable(sql);
//insert the details in the server
foreach (DataRow drow in dtSod.Rows)
{
SalesOrderDetail OrderDetails = new SalesOrderDetail();
if (OrderID == Int32.Parse(drow["SalesOrderID"].ToString()))
{
OrderDetails.SalesOrderID = ServerOrderID;
OrderDetails.ProductID = Int32.Parse(drow["ProductID"].ToString());
OrderDetails.OrderQty = short.Parse(drow["OrderQty"].ToString());
OrderDetails.UnitPrice = decimal.Parse(drow["UnitPrice"].ToString());
OrderDetails.SpecialOfferID = Int32.Parse(drow["SpecialOfferID"].ToString());
OrderDetails.UnitPriceDiscount = decimal.Parse(drow["UnitPriceDiscount"].ToString());
if (OrderDetails.UnitPriceDiscount > 0 && OrderDetails.SpecialOfferID == 0)
{
//check if the discount is coming from special offer deal.
SpecialOfferProduct sop = new SpecialOfferProduct();
DataSet ds = new DataSet();
ds = sop.GetDiscountByProduct(OrderDetails.ProductID, OrderDetails.OrderQty);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["SpecialOfferID"] != DBNull.Value)
{
OrderDetails.SpecialOfferID = Int32.Parse(ds.Tables[0].Rows[0]["SpecialOfferID"].ToString());
}
}
}
OrderDetails.LineTotal = decimal.Parse(drow["LineTotal"].ToString());
OrderDetails.CarrierTrackingNumber = "";
OrderDetails.AddSalesOrderDetail(OrderDetails);
}
}
iCount++;
//update the status of header record in the mobile to Uploaded
//soh.UpdateStatusByID(OrderID);
progressBar1.Value = iCount;
RefreshForm();
OrderHeader = null;
}
lblStatus.Text = "updating sales mobile status";
//conn.UpdateMobileStatus(dtSoh);
conn.UpdateMobileOrderHeader("status=9", "status=1");
MessageBox.Show(iCount.ToString() + " of " + rows.ToString() + " records imported successfully", "MICS", MessageBoxButtons.OK, MessageBoxIcon.Information);
//update new order customerid
for (int j = 0; j < NewCustomerOrders.Count; j++)
{
string update = "CustomerId=" + NewCustomerOrders[j].CustomerID.ToString();
string where = "SalesOrderID=" + NewCustomerOrders[j].CurrencyRateID.ToString();
conn.UpdateMobileOrderHeader(update, where);
}
//Add the new customer to the mobile database
if (NewCustomers.Count > 0)
{
conn.SynchForm = this;
conn.AddCustomer(NewCustomers);
}
progressBar1.Minimum = 0;
progressBar1.Maximum = 100;
lblStatus.Text = "Removing old orders";
progressBar1.Value = 25;
RefreshForm();
conn.DeleteOldOrders();
lblStatus.Text = "Finished Removing old orders";
progressBar1.Value = 100;
RefreshForm();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "MICS", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
conn.Dispose();
}
}