/// <summary> /// Builds the update SQL. /// </summary> /// <returns></returns> protected void UpdateRecord(string primaryKeyValue) { Update qryUpdate = new Update(TableSchema); foreach(TableSchema.TableColumn col in TableSchema.Columns) { if(col.DataType != DbType.Binary && Utility.IsWritableColumn(col)) { Control ctrl = FindControl(col.IsPrimaryKey ? PK_ID + col.ColumnName : col.ColumnName); if(ctrl != null) { object oVal = Utility.GetDefaultControlValue(col, ctrl, false, true); oVal = TransformBooleanAndDateValues(oVal, col.DataType); qryUpdate.Set(col).EqualTo(oVal); } } } qryUpdate.Where(TableSchema.PrimaryKey).IsEqualTo(primaryKeyValue); qryUpdate.Execute(); }
/// <summary> /// deprecated version /// </summary> /// <param name="containerid"></param> /// <param name="vesselid"></param> /// <returns></returns> protected bool change_vessel_deprecated(int containerid, int vesselid) { bool _changed = false; DateTime _currentdate = DateTime.Now; //for testing containerid = 2332; try { //for testing q1 ******** //SqlQuery _s1 = new Select(DAL.Logistics.Tables.OrderTable); //_s1.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn); //_s1.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn); //_s1.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid); //DataTable _dt1 = _s1.ExecuteDataSet().Tables[0]; string[] _cols1 = { "OrderTable.OrderID", "OrderTable.VesselID", "OrderTable.VesselLastUpdated" }; DataTable _dt = new Select(_cols1).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).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]["OrderID"].ToString()); //update OrderTable _tb = new OrderTable(_id); _tb.VesselID = vesselid; _tb.VesselLastUpdated = _currentdate; _tb.Save(); } } //alternative method? //IList<int> _ids = 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).ExecuteTypedList<int>(); // //IList<int> _q1 = new SubSonic.Update(DAL.Logistics.Tables.OrderTable) // .Set(OrderTable.Columns.VesselID).EqualTo(vesselid) // .Set(OrderTable.Columns.VesselLastUpdated).EqualTo(_currentdate) // .Where(OrderTable.Columns.OrderID).In(_ids).ExecuteTypedList<int>(); //for testing q2 ******* //Update _s2 = new Update(DAL.Logistics.Tables.OrderTable); //_s2.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn); //_s2.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn); //_s2.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.OrderTable.VesselIDColumn); //_s2.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn); //_s2.InnerJoin(DAL.Logistics.VoyageETASubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn); //_s2.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid); //DataTable _dt2 = _s2.ExecuteDataSet().Tables[0]; //********************** //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' //get data with reader as we need the ETS and ETA values string[] _cols2 = { "OrderTable.OrderID", "VoyageETSSubTable.ETS", "VoyageETASubTable.ETA" }; _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]; 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; _tb.Save(); } //can't do this as causes error multi-part identifier can't be bound //Update _q2 = new Update(DAL.Logistics.Tables.OrderTable); // _q2.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn); // _q2.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn); // _q2.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.OrderTable.VesselIDColumn); // _q2.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn); // _q2.InnerJoin(DAL.Logistics.VoyageETASubTable .VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn); // _q2.Set(DAL.Logistics.OrderTable.EtsColumn).EqualTo(DAL.Logistics.VoyageETSSubTable.EtsColumn); // _q2.Set(DAL.Logistics.OrderTable.EtaColumn).EqualTo(DAL.Logistics.VoyageETASubTable.EtaColumn); // _q2.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid); //_test = _q2.ToString(); //_q2.Execute(); //for testing q3 ******* //SqlQuery _s3 = new Select(DAL.Logistics.Tables.ContainerTable); //_s3.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid); //DataTable _dt3 = _s3.ExecuteDataSet().Tables[0]; //********************** 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(); _changed = true; } catch (Exception ex) { string _er = ex.Message.ToString(); this.dxlblErr.Text = _er; this.dxpnlErr.Visible = true; } return _changed; }
//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; }
//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; }
private void cmdSave_Click(object sender, EventArgs e) { try { SqlQuery sqlTinhThanh = new Select().From(DmucDiachinh.Schema).Where(DmucDiachinh.Columns.MaDiachinh).IsEqualTo( Utility.sDbnull(txtMaTinhThanh.Text.Trim(), "")); if (sqlTinhThanh.GetRecordCount() <= 0 && txtMaTinhThanh.TextLength > 0 && txtTenTinhthanh.TextLength > 0)//Thêm mới { if (!IsValidDataTP()) return; var objDmucDiachinhT = new DmucDiachinh(); objDmucDiachinhT.MaDiachinh = Utility.sDbnull(txtMaTinhThanh.Text.Trim(), ""); objDmucDiachinhT.TenDiachinh = Utility.sDbnull(txtTenTinhthanh.Text.Trim(), ""); objDmucDiachinhT.LoaiDiachinh = 0; objDmucDiachinhT.MaCha = ""; objDmucDiachinhT.SttHthi = 0; objDmucDiachinhT.MotaThem = Utility.sDbnull(txtMoTaTinhThanh.Text.Trim(), ""); objDmucDiachinhT.IsNew = true; objDmucDiachinhT.Save(); Utility.SetMsg(lblMsgTP, "Thêm thành công TP", true); } else if (sqlTinhThanh.GetRecordCount() > 0) { if (!IsValidDataTP()) return; SqlQuery tinh = new Update(DmucDiachinh.Schema).Set(DmucDiachinh.Columns.TenDiachinh).EqualTo(Utility.sDbnull(txtTenTinhthanh.Text.Trim(), "")).Set( DmucDiachinh.Columns.MotaThem). EqualTo(Utility.sDbnull(txtMoTaTinhThanh.Text.Trim(), "")).Where(DmucDiachinh.Columns.MaDiachinh). IsEqualTo(Utility.sDbnull(txtMaTinhThanh.Text.Trim(), "")); tinh.Execute(); Utility.SetMsg(lblMsgTP, "Cập nhật thành công TP", true); } SqlQuery sqlQuanHuyen = new Select().From(DmucDiachinh.Schema).Where(DmucDiachinh.Columns.MaDiachinh).IsEqualTo( Utility.sDbnull(txtMaQuanHuyen.Text.Trim(), "")); if (sqlQuanHuyen.GetRecordCount() <= 0 && txtMaQuanHuyen.TextLength > 0 && txtTenQuanHuyen.TextLength > 0) { if (!IsValidDataQH()) return; var objDmucDiachinhH = new DmucDiachinh(); objDmucDiachinhH.MaDiachinh = Utility.sDbnull(txtMaQuanHuyen.Text.Trim(), ""); objDmucDiachinhH.TenDiachinh = Utility.sDbnull(txtTenQuanHuyen.Text.Trim(), ""); objDmucDiachinhH.LoaiDiachinh = 1; objDmucDiachinhH.MaCha = Utility.sDbnull(txtMaTinhThanh.Text.Trim(), ""); objDmucDiachinhH.SttHthi = 0; objDmucDiachinhH.MotaThem = Utility.sDbnull(txtMoTaQuanHuyen.Text.Trim(), ""); objDmucDiachinhH.IsNew = true; objDmucDiachinhH.Save(); Utility.SetMsg(lblMsgQH, "Thêm thành công Quận/Huyện", true); } else if (sqlQuanHuyen.GetRecordCount() > 0) { if (!IsValidDataQH()) return; SqlQuery quan = new Update(DmucDiachinh.Schema).Set(DmucDiachinh.Columns.TenDiachinh).EqualTo(Utility.sDbnull(txtTenQuanHuyen.Text.Trim(), "")).Set( DmucDiachinh.Columns.MotaThem). EqualTo(Utility.sDbnull(txtMoTaQuanHuyen.Text.Trim(), "")).Where(DmucDiachinh.Columns.MaDiachinh). IsEqualTo(Utility.sDbnull(txtMaQuanHuyen.Text.Trim(), "")); quan.Execute(); Utility.SetMsg(lblMsgQH, "Cập nhật thành công Quận/Huyện", true); } SqlQuery sqlXaPhuong = new Select().From(DmucDiachinh.Schema).Where(DmucDiachinh.Columns.MaDiachinh).IsEqualTo( Utility.sDbnull(txtMaXaPhuong.Text.Trim(), "")); if (sqlXaPhuong.GetRecordCount() <= 0 && txtMaXaPhuong.TextLength > 0 && txtTenXaPhuong.TextLength > 0) { if (!IsValidDataXP()) return; var objDmucDiachinhX = new DmucDiachinh(); objDmucDiachinhX.MaDiachinh = Utility.sDbnull(txtMaXaPhuong.Text.Trim(), ""); objDmucDiachinhX.TenDiachinh = Utility.sDbnull(txtTenXaPhuong.Text.Trim(), ""); objDmucDiachinhX.LoaiDiachinh = 2; objDmucDiachinhX.MaCha = Utility.sDbnull(txtMaQuanHuyen.Text.Trim(), ""); objDmucDiachinhX.SttHthi = 0; objDmucDiachinhX.MotaThem = Utility.sDbnull(txtMoTaXaPhuong.Text.Trim(), ""); objDmucDiachinhX.IsNew = true; objDmucDiachinhX.Save(); Utility.SetMsg(lblMsgXP, "Thêm thành công Xã/Phường", true); } else if (sqlXaPhuong.GetRecordCount() > 0) { if (!IsValidDataXP()) return; SqlQuery xa = new Update(DmucDiachinh.Schema).Set(DmucDiachinh.Columns.TenDiachinh).EqualTo( Utility.sDbnull(txtTenXaPhuong.Text.Trim(), "")).Set( DmucDiachinh.Columns.MotaThem). EqualTo(Utility.sDbnull(txtMoTaXaPhuong.Text.Trim(), "")).Where(DmucDiachinh.Columns.MaDiachinh). IsEqualTo(Utility.sDbnull(txtMaXaPhuong.Text.Trim(), "")); xa.Execute(); Utility.SetMsg(lblMsgXP, "Cập nhật thành công Xã/Phường", true); } m_blnHasChanged = true; LoadData(); } catch (Exception ex) { Utility.CatchException(ex); } }