public DeliverySubTableCollection FetchAll() { DeliverySubTableCollection coll = new DeliverySubTableCollection(); Query qry = new Query(DeliverySubTable.Schema); coll.LoadAndCloseReader(qry.ExecuteReader()); return coll; }
public DeliverySubTableCollection FetchByQuery(Query qry) { DeliverySubTableCollection coll = new DeliverySubTableCollection(); coll.LoadAndCloseReader(qry.ExecuteReader()); return coll; }
public DeliverySubTableCollection FetchByOrderNumber(object OrderNumber) { DeliverySubTableCollection coll = new DeliverySubTableCollection().Where("OrderNumber", OrderNumber).Load(); return coll; }
public DeliverySubTableCollection FetchByID(object DeliveryID) { DeliverySubTableCollection coll = new DeliverySubTableCollection().Where("DeliveryID", DeliveryID).Load(); return coll; }
//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> /// 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; }