public SalesOrderDetailCollection GetAllSalesOrderDetailsCollection()
{
IDBManager dbm = new DBManager();
SalesOrderDetailCollection cols = new SalesOrderDetailCollection();
try
{
IDataReader reader = dbm.ExecuteReader(CommandType.StoredProcedure, "SelectSalesOrderDetailAll");
while (reader.Read())
{
SalesOrderDetail SOD = new SalesOrderDetail();
SOD.SalesOrderID = Int32.Parse(reader["SalesOrderID"].ToString());
SOD.SalesOrderDetailID = Int32.Parse(reader["SalesOrderDetailID"].ToString());
SOD.CarrierTrackingNumber = reader["CarrierTrackingNumber"].ToString();
SOD.OrderQty = short.Parse(reader["OrderQty"].ToString());
SOD.ProductID = Int32.Parse(reader["ProductID"].ToString());
SOD.SpecialOfferID = Int32.Parse(reader["SpecialOfferID"].ToString());
SOD.UnitPrice = decimal.Parse(reader["UnitPrice"].ToString());
SOD.UnitPriceDiscount = decimal.Parse(reader["UnitPriceDiscount"].ToString());
SOD.LineTotal = decimal.Parse(reader["LineTotal"].ToString());
SOD.ModifiedDate = DateTime.Parse(reader["ModifiedDate"].ToString());
cols.Add(SOD);
}
}
catch (Exception ex)
{
log.Write(ex.Message, "GetAllSalesOrderDetailsCollection");
throw (ex);
}
finally
{
dbm.Dispose();
}
return(cols);
}
public SalesOrderDetailCollection GetAllSalesOrderDetailsDynamicCollection(string whereExpression, string orderBy)
{
IDBManager dbm = new DBManager();
SalesOrderDetailCollection cols = new SalesOrderDetailCollection();
try
{
dbm.CreateParameters(2);
dbm.AddParameters(0, "@WhereCondition", whereExpression);
dbm.AddParameters(1, "@OrderByExpression", orderBy);
IDataReader reader = dbm.ExecuteReader(CommandType.StoredProcedure, "SelectSalesOrderDetailsDynamic");
while (reader.Read())
{
SalesOrderDetail SOD = new SalesOrderDetail();
SOD.SalesOrderID = Int32.Parse(reader["SalesOrderID"].ToString());
SOD.SalesOrderDetailID = Int32.Parse(reader["SalesOrderDetailID"].ToString());
SOD.CarrierTrackingNumber = reader["CarrierTrackingNumber"].ToString();
SOD.OrderQty = short.Parse(reader["OrderQty"].ToString());
SOD.ProductID = Int32.Parse(reader["ProductID"].ToString());
SOD.SpecialOfferID = Int32.Parse(reader["SpecialOfferID"].ToString());
if (reader["SpecialOfferDesc"] != DBNull.Value)
{
SOD.SpecialOfferDesc = reader["SpecialOfferDesc"].ToString();
}
else
{
SOD.SpecialOfferDesc = "";
}
SOD.UnitPrice = decimal.Parse(reader["UnitPrice"].ToString());
SOD.UnitPriceDiscount = decimal.Parse(reader["UnitPriceDiscount"].ToString());
SOD.LineTotal = decimal.Parse(reader["LineTotal"].ToString());
SOD.ModifiedDate = DateTime.Parse(reader["ModifiedDate"].ToString());
SOD.ProductName = reader["Name"].ToString();
SOD.Description = reader["Description"].ToString();
cols.Add(SOD);
}
}
catch (Exception ex)
{
log.Write(ex.Message, "GetAllSalesOrderDetailsDynamicCollection");
throw (ex);
}
finally
{
dbm.Dispose();
}
return(cols);
}
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();
}
}
