public OrderTableCollection FetchByQuery(Query qry)
{
OrderTableCollection coll = new OrderTableCollection();
coll.LoadAndCloseReader(qry.ExecuteReader());
return(coll);
}
protected void update_ordertable_eta(int portid, int vesselid, DateTime?dteta)
{
try
{
SubSonic.SqlQuery _qry = DB.Select().From(DAL.Logistics.Tables.OrderTable).Where("DestinationPortID").IsEqualTo(portid).And("VesselID").IsEqualTo(vesselid);
OrderTableCollection _orders = new OrderTableCollection();
_orders.LoadAndCloseReader(_qry.ExecuteReader());
if (_orders.Count > 0)
{
for (int _ix = 0; _ix < _orders.Count; _ix++)
{
_orders[_ix].Eta = dteta;
_orders[_ix].VesselLastUpdated = DateTime.Now;
}
_orders.BatchSave();
//this.dxlblInfo.Text = string.Format("{0} Orders have been updated", _orders.Count.ToString());
//this.dxpnlMsg.Visible = true;
}
}
catch (Exception ex)
{
string _err = ex.Message.ToString();
//this.dxlblErr.Text = _err;
//this.dxpnlErr.Visible = true;
}
}
public OrderTableCollection FetchAll()
{
OrderTableCollection coll = new OrderTableCollection();
Query qry = new Query(OrderTable.Schema);
coll.LoadAndCloseReader(qry.ExecuteReader());
return coll;
}
public OrderTableCollection FetchAll()
{
OrderTableCollection coll = new OrderTableCollection();
Query qry = new Query(OrderTable.Schema);
coll.LoadAndCloseReader(qry.ExecuteReader());
return(coll);
}
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 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>
/// 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>
/// 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;
}
}
protected void update_ordertable_eta(int portid, int vesselid, DateTime? dteta)
{
try
{
SubSonic.SqlQuery _qry = DB.Select().From(DAL.Logistics.Tables.OrderTable).Where("DestinationPortID").IsEqualTo(portid).And("VesselID").IsEqualTo(vesselid);
OrderTableCollection _orders = new OrderTableCollection();
_orders.LoadAndCloseReader(_qry.ExecuteReader());
if (_orders.Count > 0)
{
for (int _ix = 0; _ix < _orders.Count; _ix++)
{
_orders[_ix].Eta = dteta;
_orders[_ix].VesselLastUpdated = DateTime.Now;
}
_orders.BatchSave();
//this.dxlblInfo.Text = string.Format("{0} Orders have been updated", _orders.Count.ToString());
//this.dxpnlMsg.Visible = true;
}
}
catch (Exception ex)
{
string _err = ex.Message.ToString();
//this.dxlblErr.Text = _err;
//this.dxpnlErr.Visible = 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;
}
}
}
public OrderTableCollection PodFetchByNo(object OrderNumber)
{
OrderTableCollection coll = new OrderTableCollection().Where("OrderNumber", OrderNumber).Load();
return coll;
}
public OrderTableCollection PodFetchByID(object OrderID)
{
OrderTableCollection coll = new OrderTableCollection().Where("OrderID", OrderID).Load();
return coll;
}
public OrderTableCollection PodFetchByNo(object OrderNumber)
{
OrderTableCollection coll = new OrderTableCollection().Where("OrderNumber", OrderNumber).Load();
return(coll);
}
public OrderTableCollection PodFetchByID(object OrderID)
{
OrderTableCollection coll = new OrderTableCollection().Where("OrderID", OrderID).Load();
return(coll);
}
/// <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;
}
}
public OrderTableCollection FetchByQuery(Query qry)
{
OrderTableCollection coll = new OrderTableCollection();
coll.LoadAndCloseReader(qry.ExecuteReader());
return coll;
}
