protected void remove_selected_orders(int hblid) { if (this.dxlstSaved.Items.Count > 0 && hblid > 0) { try { //get house bl details HouseBLTable _hbl = new HouseBLTable(hblid); //DateTime? _nulldt = null; //use for clearing ets, eta if we need to //IList<string> _ordernos = new List<string>(); OrderTableCollection _col = new OrderTableCollection(); //step through selected list items and get order numbers for (int _ix = 0; _ix < this.dxlstSaved.Items.Count; _ix++) { if (this.dxlstSaved.Items[_ix].Selected == true) { string _no = this.dxlstSaved.Items[_ix].Text.ToString(); //_ordernos.Add(_no); OrderTable _tbl = new OrderTable("OrderNumber", _no); _tbl.HouseBLNUmber = null; //_h.HouseBLNumber; _tbl.HouseBLAdded = false; //_tbl.Ets = _nulldt; //should we clear this? ms access db doesn't bother //_tbl.Eta = _nulldt; //should we clear this? ms access db doesn't bother //_tbl.VesselLastUpdated = DateTime.Now; //should we clear this? ms access db doesn't bother _col.Add(_tbl); } //end if } //end loop //save _col.SaveAll(); //clear selected list and rebind other lists //saved orders list binding using hblnumber bind_housebl_orders(_hbl.HouseBLNumber.ToString()); //available orders list binding bind_housebl_orders(wwi_func.vint(_hbl.ConsigneeID.ToString()), wwi_func.vint(_hbl.OriginPort.ToString()), wwi_func.vint(_hbl.DestinationPort.ToString())); //clear selected list this.dxlstSelected.Items.Clear(); } catch (Exception ex) { string _ex = ex.Message.ToString(); this.dxlblErr.Text = _ex; this.dxpnlErr.ClientVisible = true; } } }
/// <summary> /// replacing objectdatasource /// </summary> protected void bind_formview(string viewmode) { string[] _key = { "OrderID" }; string _orderno = wwi_security.DecryptString(get_token("pno"), "publiship"); int _orderid = wwi_func.vint(wwi_security.DecryptString(get_token("pid"), "publiship")); try { OrderTableCollection _o = new OrderTableCollection(); OrderTable _t = viewmode != "Insert" ? new OrderTable(_orderid) : new OrderTable(); _o.Add(_t); this.formOrder.DataSource = _o; //303635; //183689; 303635 is the orderid for test record order number 999909 this.formOrder.DataKeyNames = _key; this.formOrder.DataBind(); } catch (Exception ex) { string _ex = ex.Message.ToString(); this.dxlblErr.Text = string.Format("Order Number {0}. Error: {1}", _orderno , _ex); this.dxpnlErr.ClientVisible = true; } }
/// <summary> /// replacing objectdatasource address data in in OrderTable /// </summary> protected void bind_formview(string viewmode) { string[] _key = { "OrderID" }; string _orderno = wwi_security.DecryptString(get_token("pno"), "publiship"); int _orderid = wwi_func.vint(wwi_security.DecryptString(get_token("pid"), "publiship")); try { OrderTableCollection _o = new OrderTableCollection(); OrderTable _t = viewmode != "Insert" ? new OrderTable(_orderid) : new OrderTable(); _o.Add(_t); this.fmvAddresses.DataSource = _o; //303635; //183689; 303635 is the orderid for test record order number 999909 this.fmvAddresses.DataKeyNames = _key; this.fmvAddresses.DataBind(); } catch (Exception ex) { string _ex = ex.Message.ToString(); this.dxlblErr.Text = string.Format("Order Number {0}. Error: {1}", _orderno, _ex); this.dxpnlErr.ClientVisible = true; } }
/// <summary> /// allocation of charges /// </summary> protected void bind_formview(string viewmode) { string[] _key = { "OrderID" }; string _orderno = wwi_security.DecryptString(get_token("pno"), "publiship"); int _orderid = wwi_func.vint(wwi_security.DecryptString(get_token("pid"), "publiship")); try { //have to use a collection as formview requires i IListSource, IEnumerable, or IDataSource. OrderTableCollection _tbc = new OrderTableCollection(); OrderTable _tbl = viewmode != "Insert" ? new OrderTable(_orderid) : new OrderTable(); _tbc.Add(_tbl); //bind formview to collection this.fmvCharge.DataSource = _tbc; this.fmvCharge.DataBind(); } catch (Exception ex) { string _ex = ex.Message.ToString(); this.dxlblErr.Text = string.Format("Order Number {0}. Error: {1}", _orderno, _ex); this.dxpnlErr.ClientVisible = true; } }
//end package type #endregion #region move container and orders /// <summary> /// move container to a different vessel /// 1. update Order table set VesselID = 'new vessel id', VesselLastUpdated = current date where containerid = N .Equivalent to access "ContainerVesselUpdateVesselQuery" /// 2. update Order table set ETS = VoyageETSSubTable.ETS, ETA=VoyageETSSubTable.ETA where containerid = N .Equivalent to access "ContainerVesselUpdateDatesQuery" /// 3. update Container table set VoyageID = 'new voyage id' where containerid = N .Equivalent to access ""ContainerVesselUpdateContainerQuery" /// </summary> /// <param name="containerid"></param> protected bool change_vessel(int containerid, int vesselid) { bool _changed = false; DateTime _currentdate = DateTime.Now; //for testing //containerid = 2332; using (SharedDbConnectionScope _sc = new SharedDbConnectionScope()) { using (System.Transactions.TransactionScope _ts = new System.Transactions.TransactionScope()) { try { //for testing q1 //string _q1 = "SELECT o.OrderID, VesselId, VesselLastUpdated FROM OrderTable as o INNER JOIN " + //"ContainerTable AS c INNER JOIN " + //"ContainerSubTable AS s ON c.ContainerID = s.ContainerID ON o.OrderNumber = s.OrderNumber WHERE (c.ContainerID = 2332);"; //object[] _p1 = { containerid }; //int _result = new SubSonic.CodingHorror().ExecuteScalar<int>(_q1, _p1); OrderTableCollection _q2 = new Select().From(DAL.Logistics.Tables.OrderTable) .InnerJoin(DAL.Logistics.ContainerSubTable.OrderNumberColumn, DAL.Logistics.OrderTable.OrderNumberColumn) .InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn) .Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteAsCollection<OrderTableCollection>(); for (int _ix = 0; _ix < _q2.Count; _ix++) { _q2[_ix].VesselID = vesselid; _q2[_ix].VesselLastUpdated = _currentdate; } _q2.SaveAll(); //does not work because the triggers on the ordertable cause errors //Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression //1st statement paramatised and using subsonic.codinghorror, table aliases prevent 'multi-part identifier can't be bound' tsql error //string _q1 = "UPDATE OrderTable " + // "SET VesselID = @vesselid, VesselLastUpdated = @lastupdated " + // "FROM OrderTable INNER JOIN " + // "ContainerTable AS c INNER JOIN " + // "ContainerSubTable AS s ON c.ContainerID = s.ContainerID ON OrderTable.OrderNumber = s.OrderNumber " + // "WHERE (c.ContainerID = @containerid);"; // //object[] _p1 = { vesselid, _currentdate, containerid }; //int _result = new SubSonic.CodingHorror().ExecuteScalar<int>(_q1, _p1); //for testing q2 //SELECT c.ContainerID, ets.ets, eta.eta, OrderTable.OrderID FROM ContainerTable AS c INNER JOIN //ContainerSubTable AS s ON c.ContainerID = s.ContainerID INNER JOIN //VoyageTable AS v INNER JOIN VoyageETASubTable AS eta ON v.VoyageID = eta.VoyageID //INNER JOIN VoyageETSSubTable AS ets ON v.VoyageID = ets.VoyageID INNER JOIN //OrderTable ON v.VoyageID = OrderTable.VesselID ON s.OrderNumber = OrderTable.OrderNumber //WHERE (c.ContainerID = @containerid); //object[] _p2 = { containerid }; //_result = new SubSonic.CodingHorror().ExecuteScalar<int>(_q2, _p2); //q2 string[] _cols2 = { "OrderTable.OrderID", "VoyageETSSubTable.ETS", "VoyageETASubTable.ETA" }; DataTable _dt = new Select(_cols2).From(DAL.Logistics.Tables.OrderTable) .InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn) .InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn) .InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.OrderTable.VesselIDColumn) .InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn) .InnerJoin(DAL.Logistics.VoyageETASubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn) .Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteDataSet().Tables[0]; if (_dt.Rows.Count > 0) { OrderTableCollection _ot = new OrderTableCollection(); for (int _ix = 0; _ix < _dt.Rows.Count; _ix++) { int _id = wwi_func.vint(_dt.Rows[_ix]["OrderID"].ToString()); DateTime _ets = wwi_func.vdatetime(_dt.Rows[_ix]["Ets"].ToString()); DateTime _eta = wwi_func.vdatetime(_dt.Rows[_ix]["Eta"].ToString()); //update OrderTable _tb = new OrderTable(_id); _tb.Ets = _ets; _tb.Eta = _eta; _ot.Add(_tb); } _ot.SaveAll(); } //does not work because the triggers on the ordertable cause errors //2nd statement paramatised and using subsonic.codinghorror, table aliases prevent 'multi-part identifier can't be bound' tsql error //string _q2 = "UPDATE OrderTable " + // "SET ETS = ets.ETS, ETA = eta.ETA " + // "FROM ContainerTable AS c INNER JOIN " + // "ContainerSubTable AS s ON c.ContainerID = s.ContainerID INNER JOIN " + // "VoyageTable AS v INNER JOIN " + // "VoyageETASubTable AS eta ON v.VoyageID = eta.VoyageID INNER JOIN " + // "VoyageETSSubTable AS ets ON v.VoyageID = ets.VoyageID INNER JOIN " + // "OrderTable ON v.VoyageID = OrderTable.VesselID ON s.OrderNumber = OrderTable.OrderNumber " + // "WHERE (c.ContainerID = @containerid);"; //object[] _p2 = { containerid }; //_result = new SubSonic.CodingHorror().ExecuteScalar<int>(_q2, _p2); //3rd statement Update _q3 = new Update(DAL.Logistics.Tables.ContainerTable); _q3.Set(DAL.Logistics.ContainerTable.VoyageIDColumn).EqualTo(vesselid); _q3.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid); //_test = _q3.ToString(); _q3.Execute(); //commit transaction _ts.Complete(); _changed = true; } catch (Exception ex) { string _er = ex.Message.ToString(); this.dxlblErr.Text = _er; this.dxpnlErr.ClientVisible = true; } } } return _changed; }
protected void remove_selected_orders(int hblid) { if (this.dxlstSaved.Items.Count > 0 && hblid > 0) { try { //get house bl details HouseBLTable _hbl = new HouseBLTable(hblid); //DateTime? _nulldt = null; //use for clearing ets, eta if we need to //IList<string> _ordernos = new List<string>(); OrderTableCollection _col = new OrderTableCollection(); //step through selected list items and get order numbers for (int _ix = 0; _ix < this.dxlstSaved.Items.Count; _ix++) { if (this.dxlstSaved.Items[_ix].Selected == true) { string _no = this.dxlstSaved.Items[_ix].Text.ToString(); //_ordernos.Add(_no); OrderTable _tbl = new OrderTable("OrderNumber", _no); _tbl.HouseBLNUmber = null; //_h.HouseBLNumber; _tbl.HouseBLAdded = false; //_tbl.Ets = _nulldt; //should we clear this? ms access db doesn't bother //_tbl.Eta = _nulldt; //should we clear this? ms access db doesn't bother //_tbl.VesselLastUpdated = DateTime.Now; //should we clear this? ms access db doesn't bother _col.Add(_tbl); }//end if }//end loop //save _col.SaveAll(); //clear selected list and rebind other lists //saved orders list binding using hblnumber bind_housebl_orders(_hbl.HouseBLNumber.ToString()); //available orders list binding bind_housebl_orders(wwi_func.vint(_hbl.ConsigneeID.ToString()), wwi_func.vint(_hbl.OriginPort.ToString()), wwi_func.vint(_hbl.DestinationPort.ToString())); //clear selected list this.dxlstSelected.Items.Clear(); } catch (Exception ex) { string _ex = ex.Message.ToString(); this.dxlblErr.Text = _ex; this.dxpnlErr.ClientVisible = true; } } }