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);
}
}
