public void ImportPyments()
{
Payment p = null;
try
{
SqlCompactConnection conn = new SqlCompactConnection();
conn.connect();
lblStatus.Text = "Connected to mobile database";
RefreshForm();
string sql = "select * from payment";
DataTable dtPayments = conn.GetDataTable(sql);
int rows = dtPayments.Rows.Count;
if (rows == 0)
{
lblStatus.Text += "\nNo payments found";
//MessageBox.Show("There are no records to import", "MICS", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
lblStatus.Text = "Importing Payments..";
progressBar1.Minimum = 0;
progressBar1.Maximum = rows;
RefreshForm();
int count = 0;
foreach (DataRow dr in dtPayments.Rows)
{
p = new Payment();
p.InvoiceID = Int32.Parse(dr["InvoiceId"].ToString());
p.PaymentType = "S";
p.PaymentDate = DateTime.Parse(dr["PaymentDate"].ToString());
p.Amount = decimal.Parse(dr["Amount"].ToString());
p.CheckNumber = dr["CheckNumber"].ToString();
p.Comments = "Mobile Payment";
if (dr["Comments"].ToString() != String.Empty)
{
p.Comments += " : " + dr["Comments"].ToString();
}
//p.PaymentCode = dr["PaymentCode"].ToString();;
p.ModifiedDate = DateTime.Today;
p.AddPayment(p);
lblStatus.Text = "removing mobile payment..";
sql = "delete payment where InvoiceId=" + p.InvoiceID;
conn.Execute(sql);
UpdatePaymentStatus(p.InvoiceID);
progressBar1.Value = count++;
RefreshForm();
}
lblStatus.Text = rows.ToString() + " payments imported successfully";
progressBar1.Value = rows;
RefreshForm();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void UpdateMobileSalesOrder()
{
SqlCompactConnection conn = new SqlCompactConnection();
try
{
conn.connect();
DataTable dt = new DataTable();
string sql = "select salesorderid,salespersonid from salesorderheader where status <> 1";
dt = conn.GetDataTable(sql);
int rows = dt.Rows.Count;
int i = 0;
progressBar1.Minimum = 0;
progressBar1.Maximum = rows;
foreach (DataRow dr in dt.Rows)
{
//Get sales order data from server.
SalesOrderHeader soh = new SalesOrderHeader();
SalesOrderHeaderCollection SohCol = new SalesOrderHeaderCollection();
string where = "CurrencyRateID=" + dr["SalesOrderID"].ToString();
where += " and SalesPersonID=" + dr["SalesPersonID"].ToString();
string orderby = "SalesOrderID";
SohCol = soh.GetSalesOrderHeaderCollection(where, orderby);
int count = SohCol.Count;
if (count == 0)
{
continue;
}
//update salesorder in mobile.
SohCol[0].Status = 9;
soh.UpdateSalesOrderHeaderMobile(SohCol[0]);
//get server order details
SalesOrderDetail sod = new SalesOrderDetail();
SalesOrderDetailCollection SodCol = new SalesOrderDetailCollection();
where = "SalesOrderID=" + SohCol[0].SalesOrderID;
orderby = "SalesOrderID";
SodCol = sod.GetSalesOrderDetailCollection(where, orderby);
//Delete sales order details from mobile
sod.DeleteSalesOrderDetailMobile(SohCol[0].CurrencyRateID);
//copy sales order details from the server to the mobile.
for (int j = 0; j < SodCol.Count; j++)
{
SodCol[j].SalesOrderID = SohCol[0].CurrencyRateID;
sod.AddSalesOrderDetailsMobile(SodCol[j]);
}
i++;
progressBar1.Value = i;
RefreshForm();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "MICS", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.CloseDatabase();
conn = null;
}
}
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();
}
}