public void Insert(int? DeliveryNoteID,int? OrderNumber,DateTime? StatusDate,int? CurrentStatusID,DateTime? CurrentStatusDate,int? DeliveryAddress,bool Delivered,bool PODRequired,string SpecialInstructions,string PODurl,bool Added,byte[] Ts) { DeliverySubTable item = new DeliverySubTable(); item.DeliveryNoteID = DeliveryNoteID; item.OrderNumber = OrderNumber; item.StatusDate = StatusDate; item.CurrentStatusID = CurrentStatusID; item.CurrentStatusDate = CurrentStatusDate; item.DeliveryAddress = DeliveryAddress; item.Delivered = Delivered; item.PODRequired = PODRequired; item.SpecialInstructions = SpecialInstructions; item.PODurl = PODurl; item.Added = Added; item.Ts = Ts; item.Save(UserName); }
//end package type #endregion #region mark loaded /// <summary> /// mark container as loaded on board /// can't paramatise and use subsonic.codinghorror with table aliases prevent 'multi-part identifier can't be bound' tsql error and /// can't use an update query if multiple records are to be updated as it crashes the triggers on OrderTable: /// Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression /// 1. update container table set loadedonboard = -1, updated = current date where containerid = N .Equivalent to access "UpdateContainerLoadedQuery" /// 2. update all in order table set loadedonboard = -1 where containerid = N .Equivalent to access "UpdateOrderLoadedOnBoardQuery" /// 3. update deliverysubtable table set currentstatusid=1 where currentstatusid=12, statusdate = current date, currentstatusdate = [VoyageETSSubTable.ETS] /// where containerid = N .Equivalent to access "UpdateDeliveryStatusOnBoardQuery" /// </summary> /// <param name="containerid">int</param> protected bool mark_loaded_on_board(int containerid) { //method using subsonic.codinghorror for update statements 2 and 3 as subsonic does not handle aliasing well //and we need to get around the multi-part identifier can't be bound problem //int _onboard = 0; bool _onboard = true; DateTime _currentdate = DateTime.Now; int _result = 0; int _newstatusid = 1; int _currentstatusid = 12; //containerid = 125; //containerid 7 or 115 or 16316 or 125 was good for testing using (SharedDbConnectionScope _sc = new SharedDbConnectionScope()) { using (System.Transactions.TransactionScope _ts = new System.Transactions.TransactionScope()) { using (SqlConnection _cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["PublishipSQLConnectionString"].ToString())) { try { //1. update container table Update _q1 = new Update(DAL.Logistics.Tables.ContainerTable); _q1.Set(DAL.Logistics.ContainerTable.LoadedOnBoardColumn).EqualTo(_onboard); _q1.Set(DAL.Logistics.ContainerTable.UpdatedColumn).EqualTo(_currentdate); _q1.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid); //string _test = _q1.ToString(); _result = _q1.Execute(); //end //2. update ordertable OrderTableCollection _q2 = new Select().From(DAL.Logistics.Tables.OrderTable) .InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn) .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].ShippedOnBoard = _onboard; } _q2.SaveAll(); //end //3. get data we need ets from VoyageEtsSubtable //used containerid 125 for testing //return a datatable, can't use a reader here as you would get an error when you try and process the deliverysubtable DeliverySubTableCollection _q3 = new DeliverySubTableCollection(); string[] _cols = { "DeliverySubTable.DeliveryID", "VoyageETSSubTable.ETS" }; DataTable _dt = new Select(_cols).From(DAL.Logistics.Tables.DeliverySubTable) .InnerJoin(DAL.Logistics.ContainerSubTable.OrderNumberColumn, DAL.Logistics.DeliverySubTable.OrderNumberColumn) .InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn) .InnerJoin(DAL.Logistics.OrderTable.OrderIDColumn, DAL.Logistics.ContainerSubTable.OrderIDColumn) .InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.ContainerTable.VoyageIDColumn) .InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn) .Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid) .And(DAL.Logistics.DeliverySubTable.CurrentStatusIDColumn).IsEqualTo(_currentstatusid).ExecuteDataSet().Tables[0]; if (_dt.Rows.Count > 0) { for (int _ix = 0; _ix < _dt.Rows.Count; _ix++) { int _id = wwi_func.vint(_dt.Rows[_ix]["DeliveryID"].ToString()); DateTime _ets = wwi_func.vdatetime(_dt.Rows[_ix]["ETS"].ToString()); //update DeliverySubTable _tb = new DeliverySubTable(_id); _tb.CurrentStatusID = _newstatusid; _tb.StatusDate = _currentdate; _tb.CurrentStatusDate = _ets; _q3.Add(_tb); } _q3.SaveAll(); } //end } catch (Exception ex) { _onboard = false; string _er = ex.Message.ToString(); this.dxlblErr.Text = _er; this.dxpnlErr.ClientVisible = true; }//end try/catch }//end using SqlConnection }//end using TransactionScope }//end using SharedDbConnectionScope return _onboard; }
/// <summary> /// update /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void dxgridDeliveries_RowUpdating(object sender, DevExpress.Web.Data.ASPxDataUpdatingEventArgs e) { ASPxGridView _grd = (ASPxGridView)sender; int _orderno = wwi_func.vint(wwi_security.DecryptString(get_token("pno"), "publiship")); int _deliveryid = wwi_func.vint(e.Keys["DeliveryID"].ToString()); try { //courier details are joined to OrderTable on OrderNumber if (_orderno > 0 && _deliveryid > 0) { //get details DeliverySubTable _t = new DeliverySubTable(_deliveryid); //REQUIRED to line to ordertable _t.OrderNumber = _orderno; //dlls if (e.NewValues["DeliveryAddress"] != null) { _t.DeliveryAddress = wwi_func.vint(e.NewValues["DeliveryAddress"].ToString()); } if (e.NewValues["CurrentStatusID"] != null) { _t.CurrentStatusID = wwi_func.vint(e.NewValues["CurrentStatusID"].ToString()); } //dates if (e.NewValues["CurrentStatusDate"] != null) { _t.CurrentStatusDate = wwi_func.vdatetime(e.NewValues["CurrentStatusDate"].ToString()); } if (e.NewValues["StatusDate"] != null) { _t.StatusDate = wwi_func.vdatetime(e.NewValues["StatusDate"].ToString()); }//status last updated //tick boxes _t.Delivered = e.NewValues["Delivered"] != null ? wwi_func.vbool(e.NewValues["Delivered"].ToString()) == true ? true : false : false; _t.PODRequired = e.NewValues["PODRequired"] != null ? wwi_func.vbool(e.NewValues["PODRequired"].ToString()) == true ? true : false : false; //what is this field for and where is it updated? //_t.Added = wwi_func.vbool(e.NewValues["Added"].ToString()) == true ? true : false; //memo if (e.NewValues["SpecialInstructions"] != null) { _t.SpecialInstructions = e.NewValues["SpecialInstructions"].ToString(); } //insert record _t.Save(); } } catch (Exception ex) { string _ex = ex.Message.ToString(); this.dxlblErr.Text = string.Format("Delivery # {0} NOT updated. Error: {1}", _orderno, _ex); this.dxpnlErr.ClientVisible = true; } finally { //MUST call this after insert or error: Specified method is not supported e.Cancel = true; _grd.CancelEdit(); bind_deliveries(); } }
/// <summary> /// deprecated code can't get this to work /// </summary> /// <param name="containerid"></param> /// <returns></returns> protected bool mark_loaded_on_board_deprecated(int containerid) { bool _onboard = true; DateTime _currentdate = DateTime.Now; int _result = 0; int _statusid = 12; //containerid 7 or 16316 was good for testing using (SharedDbConnectionScope _sc = new SharedDbConnectionScope()) { using (System.Transactions.TransactionScope _ts = new System.Transactions.TransactionScope()) { try { //deprecated code //SubSonic.Query _qry1 = new SubSonic.Query(DAL.Logistics.Tables.ContainerTable); //_qry1.QueryType = QueryType.Update; //_qry1.AddUpdateSetting("LoadedOnBoard", _onboard); //_qry1.AddUpdateSetting("Updated", DateTime.Now.ToShortDateString()); //_qry1.WHERE("ContainerID", Comparison.Equals, containerid); //************* //for testing q1 ******** //SqlQuery _s1 = new Select(DAL.Logistics.Tables.ContainerTable); //_s1.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid); //DataTable _dt1 = _s1.ExecuteDataSet().Tables[0]; Update _q1 = new Update(DAL.Logistics.Tables.ContainerTable); _q1.Set(DAL.Logistics.ContainerTable.LoadedOnBoardColumn).EqualTo(_onboard); _q1.Set(DAL.Logistics.ContainerTable.UpdatedColumn).EqualTo(_currentdate); _q1.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid); //string _test = _q1.ToString(); _result = _q1.Execute(); //2. get associated orderid's //OrderTableCollection _q2 = new Select().From(DAL.Logistics.Tables.OrderTable) //.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn) //.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn) //.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteAsCollection<OrderTableCollection>(); //update records // for (int _ix = 0; _ix < _q2.Count; _ix++) // { // _q2[_ix].ShippedOnBoard = _onboard; // // } // // _q2.SaveAll(); //2. get associated orderid's as typed list IList<int> _s2 = new Select("OrderID").From(DAL.Logistics.Tables.OrderTable) .InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn) .InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn) .Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteTypedList<int>(); for (int _ix = 0; _ix < _s2.Count; _ix++) { //Update _q2 = new Update(DAL.Logistics.Tables.OrderTable); //_q2.Set(DAL.Logistics.OrderTable.ShippedOnBoardColumn).EqualTo(_onboard); //_q2.Where(DAL.Logistics.OrderTable.OrderIDColumn).InValues. } //can't use IN for update as container id is a nullable coumn //Update _q2 = new Update(DAL.Logistics.Tables.OrderTable); //_q2.Set(DAL.Logistics.OrderTable.ShippedOnBoardColumn).EqualTo(_onboard); //_q2.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).In(_s2); //string _test = _q2.ToString(); //_q2.Execute(); //can't use an update query here as there will likely be multiple orders to update and you will get 'multi-part identifier can't be bound' //Update _q2 = new Update(DAL.Logistics.Tables.OrderTable); // _q2.Set(DAL.Logistics.OrderTable.ShippedOnBoardColumn).EqualTo(_onboard); // _q2.From(DAL.Logistics.Tables.OrderTable); // _q2.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn); // _q2.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid); //.Execute(); // string _test = _q2.ToString(); //********************** string[] _cols = { "DeliverySubTable.DeliveryID", "VoyageETSSubTable.ETS" }; //SqlQuery _s3 = new Select(_cols).From(DAL.Logistics.Tables.DeliverySubTable) //.InnerJoin(DAL.Logistics.ContainerSubTable.OrderNumberColumn, DAL.Logistics.DeliverySubTable.OrderNumberColumn) //.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn) //.InnerJoin(DAL.Logistics.OrderTable.OrderIDColumn, DAL.Logistics.ContainerSubTable.OrderIDColumn) //.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.ContainerTable.VoyageIDColumn) //.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn) //.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid) //.And(DAL.Logistics.DeliverySubTable.CurrentStatusIDColumn).IsEqualTo(1); //string _test = _s3.ToString(); ///q3. get data we need ets from VoyageEtsSubtable so return a datareader and then build collection IDataReader _rd = new Select(_cols).From(DAL.Logistics.Tables.DeliverySubTable) .InnerJoin(DAL.Logistics.ContainerSubTable.OrderNumberColumn, DAL.Logistics.DeliverySubTable.OrderNumberColumn) .InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn) .InnerJoin(DAL.Logistics.OrderTable.OrderIDColumn, DAL.Logistics.ContainerSubTable.OrderIDColumn) .InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.ContainerTable.VoyageIDColumn) .InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn) .Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid) .And(DAL.Logistics.DeliverySubTable.CurrentStatusIDColumn).IsEqualTo(1).ExecuteReader(); DeliverySubTableCollection _q3 = new DeliverySubTableCollection(); _result = 0; while (_rd.Read()) { int _id = wwi_func.vint(_rd["DeliveryID"].ToString()); DateTime _dt = wwi_func.vdatetime(_rd["Ets"].ToString()); //update DeliverySubTable _tb = new DeliverySubTable(_id); _tb.CurrentStatusID = _statusid; _tb.StatusDate = _currentdate; _tb.CurrentStatusDate = _dt; _q3.Add(_tb); _result++; } if (_result > 0) { _q3.BatchSave(); } //can't use a query here as there will likely be multiple orders to update and you will get 'multi-part identifier can't be bound' //Update _q3 = new Update(DAL.Logistics.Tables.DeliverySubTable); //_q3.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn); // _q3.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn); // _q3.InnerJoin(DAL.Logistics.DeliverySubTable.OrderNumberColumn, DAL.Logistics.ContainerSubTable.OrderNumberColumn); // _q3.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.ContainerTable.VoyageIDColumn); // _q3.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn); // _q3.Set(DAL.Logistics.DeliverySubTable.CurrentStatusIDColumn).EqualTo(12); // _q3.Set(DAL.Logistics.DeliverySubTable.StatusDateColumn).EqualTo(_currentdate); // _q3.Set(DAL.Logistics.DeliverySubTable.CurrentStatusDateColumn).EqualTo(DAL.Logistics.VoyageETSSubTable.EtsColumn); // _q3.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid); // _q3.And(DAL.Logistics.DeliverySubTable.CurrentStatusIDColumn).IsEqualTo(1); //_test = _q3.ToString(); //_result = _q3.Execute(); //********************** //commit transaction _ts.Complete(); _onboard = true; } catch (Exception ex) { string _er = ex.Message.ToString(); this.dxlblErr.Text = _er; this.dxpnlErr.ClientVisible = true; } } } return _onboard; }
//end custom call back /// <summary> /// insert /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void dxgridDeliveries_RowInserting(object sender, DevExpress.Web.Data.ASPxDataInsertingEventArgs e) { ASPxGridView _grd = (ASPxGridView)sender; int _orderno = wwi_func.vint(wwi_security.DecryptString(get_token("pno"), "publiship")); int _newkey = 0; try { //courier details are joined to OrderTable on OrderNumber if (_orderno > 0) { //get details DeliverySubTable _t = new DeliverySubTable(); //REQUIRED to link to ordertable _t.OrderNumber = _orderno; //dlls if (e.NewValues["DeliveryAddress"] != null) { _t.DeliveryAddress = wwi_func.vint(e.NewValues["DeliveryAddress"].ToString()); } if (e.NewValues["CurrentStatusID"] != null) { _t.CurrentStatusID = wwi_func.vint(e.NewValues["CurrentStatusID"].ToString()); } //dates if (e.NewValues["CurrentStatusDate"] != null) { _t.CurrentStatusDate = wwi_func.vdatetime(e.NewValues["CurrentStatusDate"].ToString()); } if (e.NewValues["StatusDate"] != null) { _t.StatusDate = wwi_func.vdatetime(e.NewValues["StatusDate"].ToString()); }//status last updated //tick boxes _t.Delivered = e.NewValues["Delivered"] != null? wwi_func.vbool(e.NewValues["Delivered"].ToString()) == true? true: false: false; _t.PODRequired = e.NewValues["PODRequired"] != null? wwi_func.vbool(e.NewValues["PODRequired"].ToString()) == true ? true : false: false; //what is this field for and where is it updated? //_t.Added = wwi_func.vbool(e.NewValues["Added"].ToString()) == true ? true : false; //memo if (e.NewValues["SpecialInstructions"] != null) { _t.SpecialInstructions = e.NewValues["SpecialInstructions"].ToString(); } //insert record _t.Save(); //get new deliveryID and add to hidden fields - on rowinserted we can use it to populate the new delivery with titles _newkey = wwi_func.vint(_t.GetPrimaryKeyValue().ToString()); } } catch (Exception ex) { string _ex = ex.Message.ToString(); this.dxlblErr.Text = string.Format("Delivery # {0} NOT created. Error: {1}", _orderno, _ex); this.dxpnlErr.ClientVisible = true; } finally { //MUST call this after insert or error: Specified method is not supported e.Cancel = true; _grd.CancelEdit(); bind_deliveries(); _grd.FocusedRowIndex = _grd.FindVisibleIndexByKeyValue(_newkey); //normally we'd do this on rowinserted event but CancelEdit prevents rowinserted event from firing append_order_titles(_orderno, _newkey); } }