public static SalesOrderHeaderCollection GetSalesOrderHeaderCollection ( DateTime FromDate, DateTime ToDate, string FirstName, string LastName, int OrderID, string OrderNumber, string CityName, string StateName, string CountryName, string Zip, int maxNumberOfItemsToReturn, bool prefetch ) { var relations = new RelationCollection(); var Filter = SalesOrderHeaderFilters.FilterByDateOrderIDOrderNumberCustomerNameAddress(relations, FromDate, ToDate, OrderID, OrderNumber, FirstName, LastName, CityName, StateName, CountryName, Zip); ISortExpression Sort = new SortExpression { SalesOrderHeaderFields.OrderDate | SortOperator.Ascending }; var Orders = new SalesOrderHeaderCollection(); //note Orders.SupportsSorting = true; IPrefetchPath Prefetch = prefetch ? new PrefetchPath((int)EntityType.SalesOrderHeaderEntity) { SalesOrderHeaderEntityBase.PrefetchPathCustomerViewRelated } : null; Orders.GetMulti(Filter, maxNumberOfItemsToReturn, Sort, relations, Prefetch); return(Orders); }
public SalesOrderHeaderCollection GetAllSalesOrderHeadersCollection() { IDBManager dbm = new DBManager(); SalesOrderHeaderCollection cols = new SalesOrderHeaderCollection(); try { IDataReader reader = dbm.ExecuteReader(CommandType.StoredProcedure, "SelectSalesOrderHeadersAll"); while (reader.Read()) { SalesOrderHeader SOH = new SalesOrderHeader(); SOH.SalesOrderID = Int32.Parse(reader["SalesOrderID"].ToString()); SOH.DueDate = DateTime.Parse(reader["OrderDate"].ToString()); SOH.OrderDate = DateTime.Parse(reader["DueDate"].ToString()); SOH.ShipDate = DateTime.Parse(reader["ShipDate"].ToString()); SOH.Status = Byte.Parse(reader["Status"].ToString()); SOH.OnlineOrderFlag = bool.Parse(reader["OnlineOrderFlag"].ToString()); SOH.SalesOrderNumber = reader["SalesOrderNumber"].ToString(); SOH.PurchaseOrderNumber = reader["PurchaseOrderNumber"].ToString(); SOH.CustomerID = Int32.Parse(reader["CustomerID"].ToString()); SOH.SalesPersonID = Int32.Parse(reader["SalesPersonID"].ToString()); SOH.BillToAddressID = Int32.Parse(reader["BillToAddressID"].ToString()); SOH.ShipToAddressID = Int32.Parse(reader["ShipToAddressID"].ToString()); SOH.ShipMethodID = Int32.Parse(reader["ShipMethodID"].ToString()); SOH.PaymentMethodID = Int32.Parse(reader["PaymentMethodID"].ToString()); SOH.CurrencyRateID = Int32.Parse(reader["CurrencyRateID"].ToString()); SOH.SubTotal = decimal.Parse(reader["SubTotal"].ToString()); SOH.TaxAmt = decimal.Parse(reader["TaxAmt"].ToString()); SOH.Freight = decimal.Parse(reader["Freight"].ToString()); SOH.TotalDue = decimal.Parse(reader["TotalDue"].ToString()); SOH.Comment = reader["Comment"].ToString(); SOH.ModifiedDate = DateTime.Parse(reader["ModifiedDate"].ToString()); cols.Add(SOH); } } catch (Exception ex) { log.Write(ex.Message, "GetAllSalesOrderHeadersCollection"); throw (ex); } finally { dbm.Dispose(); } return(cols); }
public SalesOrderHeaderCollection GetAllSalesOrderHeadersDynamicCollection(string whereExpression, string orderBy) { IDBManager dbm = new DBManager(); SalesOrderHeaderCollection cols = new SalesOrderHeaderCollection(); int id = 0; try { dbm.CreateParameters(2); dbm.AddParameters(0, "@WhereCondition", whereExpression); dbm.AddParameters(1, "@OrderByExpression", orderBy); IDataReader reader = dbm.ExecuteReader(CommandType.StoredProcedure, "SelectSalesOrderHeadersDynamic"); while (reader.Read()) { SalesOrderHeader SOH = new SalesOrderHeader(); SOH.SalesOrderID = Int32.Parse(reader["SalesOrderID"].ToString()); id = SOH.SalesOrderID; SOH.OrderDate = DateTime.Parse(reader["OrderDate"].ToString()); SOH.DueDate = DateTime.Parse(reader["DueDate"].ToString()); SOH.ShipDate = DateTime.Parse(reader["ShipDate"].ToString()); SOH.Status = Byte.Parse(reader["Status"].ToString()); SOH.OnlineOrderFlag = bool.Parse(reader["OnlineOrderFlag"].ToString()); SOH.SalesOrderNumber = reader["SalesOrderNumber"].ToString(); SOH.PurchaseOrderNumber = reader["PurchaseOrderNumber"].ToString(); SOH.CustomerID = Int32.Parse(reader["CustomerID"].ToString()); SOH.SalesPersonID = Int32.Parse(reader["SalesPersonID"].ToString()); if (reader["BillToAddressID"] != DBNull.Value) { SOH.BillToAddressID = Int32.Parse(reader["BillToAddressID"].ToString()); } else { SOH.BillToAddressID = 0; } if (reader["ShipToAddressID"] != DBNull.Value) { SOH.ShipToAddressID = Int32.Parse(reader["ShipToAddressID"].ToString()); } else { SOH.ShipToAddressID = 0; } SOH.ShipMethodID = Int32.Parse(reader["ShipMethodID"].ToString()); SOH.PaymentMethodID = Int32.Parse(reader["PaymentMethodID"].ToString()); SOH.CurrencyRateID = Int32.Parse(reader["CurrencyRateID"].ToString()); SOH.SubTotal = decimal.Parse(reader["SubTotal"].ToString()); SOH.TaxAmt = decimal.Parse(reader["TaxAmt"].ToString()); SOH.Freight = decimal.Parse(reader["Freight"].ToString()); SOH.TotalDue = decimal.Parse(reader["TotalDue"].ToString()); SOH.Comment = reader["Comment"].ToString(); SOH.ModifiedDate = DateTime.Parse(reader["ModifiedDate"].ToString()); cols.Add(SOH); } } catch (Exception ex) { log.Write(ex.Message, "GetAllSalesOrderHeadersDynamicCollection, SalesOrderID:" + id.ToString()); 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(); } }