public IDataReader GetOrderCountByEtaMonth(Int32 companyid)
{
DateTime today = DateTime.Today;
DateTime _firstday = new DateTime(today.Year, today.Month, 1);
DateTime _lastday = _firstday.AddMonths(1).AddDays(-1);
Aggregate _agr1 = Aggregate.GroupBy(OrderTable.EtaColumn);
Aggregate _agr2 = Aggregate.Count(OrderTable.OrderIDColumn, "SumOrderID");
Aggregate[] _agrs = { _agr1, _agr2 };
SubSonic.SqlQuery _qry = new SubSonic.SqlQuery();
if (companyid != -1) //restrict to specified company
{
_qry = new Select(_agrs).From(OrderTable.Schema).
Where(OrderTable.CompanyIDColumn).IsEqualTo(companyid).And(OrderTable.EtaColumn).IsBetweenAnd(_firstday, _lastday);
}
else
{
_qry = new Select(_agrs).From(OrderTable.Schema).
Where(OrderTable.EtaColumn).IsBetweenAnd(_firstday, _lastday);
}
IDataReader _rd = _qry.ExecuteReader();
return(_rd);
}
public string getordersXml(string username, string password)
{
string _xml = "";
int _companyid = wwi_security.getuserIds(username, password, "company");
if (_companyid > 0)
{
string[] _cols = { "view_order.CargoReady", "view_order.CompanyName", "view_order.ConsigneeName", "view_order.ContactName", "view_order.ContainerNumber",
"view_order.CustomersRef", "view_order.DateOrderCreated", "view_order.destplace", "view_order.destport", "view_order.dtupdated",
"view_order.EstPallets", "view_order.EstVolume", "view_order.EstWeight", "view_order.ETA", "view_order.ETS", "view_order.ETW",
"view_order.ExWorksDate", "view_order.HouseBLNUmber", "view_order.Impression", "view_order.ISBN", "view_order.JobClosed",
"view_order.Joined", "view_order.Name", "view_order.OrderID", "view_order.OrderNumber", "view_order.originplace",
"view_order.originport", "view_order.printer_name", "view_order.Title", "view_order.UnitPricePerCopy", "view_order.WarehouseDate" };
SubSonic.SqlQuery _qry = DB.Select(_cols).From("view_order").Where("CompanyID").IsEqualTo(_companyid).And("JobClosed").IsEqualTo(false);
System.IO.StringWriter _sw = new System.IO.StringWriter();
ViewOrderCollection _order = new ViewOrderCollection();
_order.LoadAndCloseReader(_qry.ExecuteReader());
DataTable _dt = (DataTable)_order.ToDataTable();
//parse to xml sformatted string
_dt.WriteXml(_sw);
_xml = _sw.ToString();
}
return(_xml);
}
//end get costing summary
protected void dxcbkcostingloosev1_Callback(object sender, DevExpress.Web.ASPxClasses.CallbackEventArgsBase e)
{
Int32 _quoteid = this.dxhfpriceview.Contains("qr") ? (Int32)this.dxhfpriceview.Get("qr") : 0;
if (_quoteid > 0)
{
string[] _cols = { "costing_summary.quote_id", "costing_summary.summary_type", "costing_summary.pre_part", "costing_summary.pre_full",
"costing_summary.pre_thc20", "costing_summary.pre_thc40", "costing_summary.pre_thclcl", "costing_summary.pre_docs",
"costing_summary.pre_origin", "costing_summary.pre_haul20", "costing_summary.pre_haul40", "costing_summary.freight_lcl",
"costing_summary.freight_20", "costing_summary.freight_40", "costing_summary.freight_40hq", "costing_summary.on_dest_lcl",
"costing_summary.on_pier_etc", "costing_summary.on_dest_20", "costing_summary.on_dest_40", "costing_summary.on_docs",
"costing_summary.on_customs", "costing_summary.on_part", "costing_summary.on_full", "costing_summary.on_haul20",
"costing_summary.on_haul40", "costing_summary.on_shunt20", "costing_summary.on_shunt40", "costing_summary.on_pallets",
"costing_summary.on_other", "price_values.tot_copies", "price_values.loose_name" };
SubSonic.SqlQuery _query = DAL.Pricer.DB.Select(_cols).From(DAL.Pricer.Tables.CostingSummary).LeftOuterJoin("price_values", "quote_id", "costing_summary", "quote_id").Where("quote_id").IsEqualTo(_quoteid).And("summary_type").IsEqualTo("loose");
IDataReader _rd = _query.ExecuteReader();
DataTable _dt = new DataTable();
_dt.Load(_rd);
this.rptcosting2.DataSource = _dt;
this.rptcosting2.DataBind();
_rd.Close();
}
else
{
//set_error_msg("No quote reference has been found");
//this.dxpagepriceview.ActiveTabIndex = 4;
}
}
protected void dxcbotitle_ItemRequestedByValue(object source, DevExpress.Web.ASPxEditors.ListEditItemRequestedByValueEventArgs e)
{
ASPxComboBox _combo = ((ASPxComboBox)this.dxgridTitles.FindEditRowCellTemplateControl(
this.dxgridTitles.Columns["colTitle"] as GridViewDataComboBoxColumn, "dxcbotitle"));
Int32 _id = 0;
if (e.Value != null)
{
_id = wwi_func.vint(e.Value.ToString());
}
//use datareaders - much faster than loading into collections
string[] _cols = { "ItemTable.TitleID, ItemTable.Title" };
SubSonic.SqlQuery _query = new SubSonic.SqlQuery();
_query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.ItemTable).WhereExpression("TitleID").IsEqualTo(_id);
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "TitleID";
_combo.ValueType = typeof(Int32);
_combo.TextField = "Title";
_combo.DataBind();
}
public IDataReader GetOrderCountByEtaWeek(Int32 companyid)
{
System.Globalization.CultureInfo _ci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.DayOfWeek _fd = _ci.DateTimeFormat.FirstDayOfWeek;
DateTime _firstday = DateTime.Today.AddDays(-(DateTime.Today.DayOfWeek - _fd));
DateTime _lastday = _firstday.AddDays(4); //monday to friday + 4
Aggregate _agr1 = Aggregate.GroupBy(OrderTable.EtaColumn);
Aggregate _agr2 = Aggregate.Count(OrderTable.OrderIDColumn, "SumOrderID");
Aggregate[] _agrs = { _agr1, _agr2 };
SubSonic.SqlQuery _qry = new SubSonic.SqlQuery();
if (companyid != -1) //restrict to specified company
{
_qry = new Select(_agrs).From(OrderTable.Schema).
Where(OrderTable.CompanyIDColumn).IsEqualTo(companyid).And(OrderTable.EtaColumn).IsBetweenAnd(_firstday, _lastday);
}
else
{
_qry = new Select(_agrs).From(OrderTable.Schema).
Where(OrderTable.EtaColumn).IsBetweenAnd(_firstday, _lastday);
}
IDataReader _rd = _qry.ExecuteReader();
return(_rd);
}
} //end shipment size data select
/// <summary>
/// detail grid for costing 1 (pre-palletised)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void costing1_BeforePerformDataSelect(object sender, EventArgs e)
{
ASPxGridView _detail = (ASPxGridView)sender;
//11/11/2010 can't use the ordernumber as keyfield on grid cause it might not be unique
//have to get row value
//Int32 _ordernumber = (Int32)_detail.GetMasterRowKeyValue(); //(sender as ASPxGridView).GetMasterRowKeyValue();
String[] _keys = { "quote_Id" };
Int32 _quoteid = (Int32)_detail.GetMasterRowFieldValues(_keys);
if (_quoteid > 0)
{
//var _nquery = new linq_pricerDataContext().costing_summaries.Where(c => c.quote_Id == _quoteid && c.summary_type == "pre-palletised");
//var _nquery = from c in new linq_pricerDataContext().costing_summaries
// where c.quote_Id == _quoteid && c.summary_type == "pre-palletised"
// select c;
////int _count = _nquery.Count();
//_detail.DataSource = _nquery;
//datareader is faster?
SubSonic.SqlQuery _query = DAL.Pricer.DB.Select().From(DAL.Pricer.Tables.CostingSummary).WhereExpression("quote_id").IsEqualTo(_quoteid).And("summary_type").IsEqualTo("pre-palletised");
IDataReader _rd = _query.ExecuteReader();
DataTable _dt = new DataTable();
_dt.Load(_rd);
_detail.DataSource = _dt;
_rd.Close();
}
} //end costing1 data select
/// <summary>
/// incremental filtering and partial loading of name and address book for speed
/// both ItemsRequestedByFilterCondition and ItemRequestedByValue must be set up for this to work
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcbocompany_ItemsRequestedByFilterCondition(object source, DevExpress.Web.ASPxEditors.ListEditItemsRequestedByFilterConditionEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
string _filter = !string.IsNullOrEmpty(e.Filter) ? e.Filter: "";
{
//use datareaders - much faster than loading into collections
string[] _cols = { "NameAndAddressBook.CompanyID, NameAndAddressBook.CompanyName, NameAndAddressBook.Customer" };
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex +1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("%{0}%", e.Filter.ToString())).And("Customer").IsEqualTo(true) ;
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex + 1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("%{0}%", e.Filter.ToString()));
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "CompanyID";
_combo.TextField = "CompanyName";
_combo.DataBind();
//use sqldatasource
//this.sdsCompany.ConnectionString = ConfigurationManager.ConnectionStrings["PublishipSQLConnectionString"].ToString();
//this.sdsCompany.SelectCommand = @"SELECT [CompanyID], [CompanyName], [Customer] FROM (select [CompanyID], [CompanyName], [Customer], row_number()over(order by t.[CompanyName]) as [rn] from [NameAndAddressBook] as t where (([CompanyName]) LIKE @filter)) as st where st.[rn] between @startIndex and @endIndex;";
//this.sdsCompany.SelectParameters.Clear();
//this.sdsCompany.SelectParameters.Add("filter", TypeCode.String, string.Format("%{0}%", _filter));
//this.sdsCompany.SelectParameters.Add("startindex", TypeCode.Int32, (e.BeginIndex + 1).ToString());
//this.sdsCompany.SelectParameters.Add("endindex", TypeCode.Int32, (e.EndIndex + 1).ToString());
//_combo.DataSource = this.sdsCompany;
//_combo.DataBind();
}
}
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;
}
}
//14/07/14 dxcboVesselID_ItemRequestedByValue and dxcboVesselID_ItemsRequestedByFilterCondition DEPRECATED
//can't use OnItemsRequestedByFilterCondition and OnItemRequestedByValue on this combo as server-side filtring makes the search case sensitive
//incremental filtering for large datasets on combos
/// <summary>
/// incremental filtering and partial loading of name and address book for speed
/// both ItemsRequestedByFilterCondition and ItemRequestedByValue must be set up for this to work
/// company name is only available to publiship users
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcbocompany_ItemsRequestedByFilterCondition(object source, DevExpress.Web.ASPxEditors.ListEditItemsRequestedByFilterConditionEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
//if (Page.Session["user"] != null) //if publiship user allow filter to be used otherwise filter null
//{
// Int32 _companyid = wwi_func.vint(((UserClass)Page.Session["user"]).CompanyId.ToString());
// if (_companyid == -1)
// {
string _filter = !string.IsNullOrEmpty(e.Filter) ? e.Filter : "";
//use datareaders - much faster than loading into collections
string[] _cols = { "CompanyID", "CompanyName", "Address1", "Address2", "Address3", "CountryName", "TelNo", "Customer" };
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex + 1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("%{0}%", e.Filter.ToString()));
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From("view_delivery_address").Paged(e.BeginIndex + 1, e.EndIndex + 1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("{0}%", e.Filter.ToString()));
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "CompanyID";
_combo.ValueType = typeof(int);
_combo.TextField = "CompanyName";
_combo.DataBindItems();
// }
//}
}
//incremental filtering for large datasets on combos
/// <summary>
/// incremental filtering and partial loading of vessels for speed
/// both ItemsRequestedByFilterCondition and ItemRequestedByValue must be set up for this to work
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcboVesselID_ItemsRequestedByFilterCondition(object source, DevExpress.Web.ASPxEditors.ListEditItemsRequestedByFilterConditionEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
string _filter = !string.IsNullOrEmpty(e.Filter) ? e.Filter : "";
//use datareaders - much faster than loading into collections
string[] _cols = { "VoyageID", "Joined", "ETS", "ETA", "DestinationPortID", "OriginPortID" };
string[] _sort = { "Joined" };
//additional filters on this dll
string _originportid = this.dxhfOrder.Contains("ptstart") ? this.dxhfOrder.Get("ptstart").ToString() : "";
string _destportid = this.dxhfOrder.Contains("ptend") ? this.dxhfOrder.Get("ptend").ToString() : "";
if (_originportid != "" && _destportid != "")
{
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex + 1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("%{0}%", e.Filter.ToString()));
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From("Page2VesselView").Paged(e.BeginIndex + 1, e.EndIndex + 1, "VoyageID").Where("Joined").Like(string.Format("{0}%", e.Filter.ToString())).And("DestinationPortID").IsEqualTo(_destportid).And("OriginPortID").IsEqualTo(_originportid).OrderAsc(_sort);
string test = _query.ToString();
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "VoyageID";
_combo.TextField = "Joined";
_combo.DataBindItems();
}
}
protected void dxcbocompany_ItemRequestedByValue(object source, DevExpress.Web.ASPxEditors.ListEditItemRequestedByValueEventArgs e)
{
//check value of e
//when itemrequestedby fucntionality is being used in the grid edit template this will not be set when a new record is initialised
//and will display as a js error "obejct reference not set to an instance of an object".
//You can verify this by setting the ASPxGridView.EnableCallBacks property to "false"
//this will then display the server error page not the js error message
if (e.Value != null)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
Int32 _id = 0;
//if (Page.Session["user"] != null) //if publiship user allow filter to be used otherwise filter null
//{
// Int32 _companyid = wwi_func.vint(((UserClass)Page.Session["user"]).CompanyId.ToString());
// if (_companyid == -1)
// {
_id = wwi_func.vint(e.Value.ToString());
//use datareaders - much faster than loading into collections
string[] _cols = { "CompanyID, CompanyName", "Address1", "Address2", "Address3", "CountryName", "TelNo", "Customer" };
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).WhereExpression("CompanyID").IsEqualTo(_id);
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From("view_delivery_address").WhereExpression("CompanyID").IsEqualTo(_id);
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "CompanyID";
_combo.TextField = "CompanyName";
_combo.DataBindItems();
// }
//}
}
}
/// <summary>
/// container list for this order deprecated just drop resutls to a label not editable on this form
/// </summary>
protected void bind_containers_deprecated()
{
string[] _cols = { "ContainerSubID", "ContainerNumber" };
Int32 _orderno = wwi_func.vint(wwi_security.DecryptString(get_token("pno"), "publiship"));
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From("view_order_container").Where("OrderNumber").IsEqualTo(_orderno);
ASPxListBox _lst;
if (this.fmvShipment.CurrentMode == FormViewMode.ReadOnly)
{
_lst = (ASPxListBox)this.fmvShipment.FindControl("dxlstContainerView");
}
else
{
_lst = (ASPxListBox)this.fmvShipment.FindControl("dxlstContainerEdit");
}
if (_lst != null)
{
IDataReader _rd = _query.ExecuteReader();
_lst.TextField = "ContainerNumber";
_lst.ValueField = "ContainerSubID";
_lst.ValueType = typeof(int);
_lst.DataSource = _rd;
_lst.DataBindItems();
}
}
//end bind vessel
/// <summary>
/// container list for this order
/// </summary>
protected void bind_containers()
{
string[] _cols = { "ContainerSubID", "ContainerNumber" };
Int32 _orderno = wwi_func.vint(wwi_security.DecryptString(get_token("pno"), "publiship"));
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From("view_order_container").Where("OrderNumber").IsEqualTo(_orderno);
ASPxLabel _lbl;
if (this.fmvShipment.CurrentMode == FormViewMode.ReadOnly)
{
_lbl = (ASPxLabel)this.fmvShipment.FindControl("dxlblContainerListView");
}
else
{
_lbl = (ASPxLabel)this.fmvShipment.FindControl("dxlblContainerListEdit");
}
if (_lbl != null)
{
_lbl.Text = "";
//enumerate to label
IDataReader _rd = _query.ExecuteReader();
while (_rd.Read())
{
_lbl.Text += _rd["ContainerNumber"].ToString() + Environment.NewLine;
}
}
}
protected void dxcbocompany_ItemRequestedByValue(object source, ListEditItemRequestedByValueEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
Int32 _id = 0;
//if (Page.Session["user"] != null) //if publiship user allow filter to be used otherwise filter null
//{
// Int32 _companyid = wwi_func.vint(((UserClass)Page.Session["user"]).CompanyId.ToString());
// if (_companyid == -1)
// {
if (e.Value != null)
{
_id = wwi_func.vint(e.Value.ToString());
}
//use datareaders - much faster than loading into collections
string[] _cols = { "CompanyID, CompanyName", "Address1", "Address2", "Address3", "CountryName", "TelNo", "Customer" };
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).WhereExpression("CompanyID").IsEqualTo(_id);
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From("view_delivery_address").WhereExpression("CompanyID").IsEqualTo(_id);
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "CompanyID";
_combo.ValueType = typeof(int);
_combo.TextField = "CompanyName";
_combo.DataBindItems();
// }
//}
}
/// <summary>
/// summary details for order
/// </summary>
protected void bind_summary(string mode)
{
try
{
if (mode != "insert")
{
Int32 _rowcount = 0;
Int32 _orderno = wwi_func.vint(wwi_security.DecryptString(get_token("pno"), "publiship")) ;
string[] _cols = { "OrderNumber", "PublishipOrder", "OfficeIndicator", "DateOrderCreated", "JobClosed", "HotJob" };
SubSonic.SqlQuery _q = new SubSonic.SqlQuery();
//_q = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.OrderTable).WhereExpression("OrderNumber").IsEqualTo(_orderno);
_q = DB.Select().From("view_order_summary").Where("OrderNumber").IsEqualTo(_orderno);
IDataReader _rd = _q.ExecuteReader();
while (_rd.Read())
{
this.dxlblOrderNo.Text = _rd["OrderNumber"].ToString();
//this.dxlblOfficeIndicator.Text = _rd["OfficeIndicator"].ToString();
//this.dxlblCreated.Text = "Date created: " + Convert.ToDateTime(_rd["DateOrderCreated"].ToString()).ToShortDateString();
//publiship order
bool? _checked = wwi_func.vbool(_rd["PublishipOrder"].ToString());
this.dximgJobPubliship.ClientVisible = _checked.Value;
this.dxlblJobPubliship.ClientVisible = _checked.Value;
//job closed
_checked = wwi_func.vbool(_rd["JobClosed"].ToString());
this.dximgJobClosed.ClientVisible = _checked.Value;
this.dxlblJobClosed.ClientVisible = _checked.Value;
//hot job
_checked = wwi_func.vbool(_rd["HotJob"].ToString());
this.dximgJobHot.ClientVisible = _checked.Value;
this.dxlblJobHot.ClientVisible = _checked.Value;
_rowcount++;
}
if (_rowcount == 0)
{
this.dxlblInfo.Text = "No record found for Order number " + _orderno.ToString();
this.dxpnlMsg.ClientVisible = true;
}
}
else //new record
{
this.dxlblOrderNo.Text = "New record";
}
}
catch (Exception ex)
{
string _err = ex.Message.ToString();
this.dxlblErr.Text = _err;
this.dxpnlErr.ClientVisible = true;
}
}
/// <summary>
/// summary details for order
/// </summary>
protected void bind_summary(string mode)
{
try
{
if (mode != "insert")
{
Int32 _rowcount = 0;
Int32 _orderno = wwi_func.vint(wwi_security.DecryptString(get_token("pno"), "publiship"));
string[] _cols = { "OrderNumber", "PublishipOrder", "OfficeIndicator", "DateOrderCreated", "JobClosed", "HotJob" };
SubSonic.SqlQuery _q = new SubSonic.SqlQuery();
//_q = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.OrderTable).WhereExpression("OrderNumber").IsEqualTo(_orderno);
_q = DB.Select().From("view_order_summary").Where("OrderNumber").IsEqualTo(_orderno);
IDataReader _rd = _q.ExecuteReader();
while (_rd.Read())
{
this.dxlblOrderNo.Text = _rd["OrderNumber"].ToString();
//this.dxlblOfficeIndicator.Text = _rd["OfficeIndicator"].ToString();
//this.dxlblCreated.Text = "Date created: " + Convert.ToDateTime(_rd["DateOrderCreated"].ToString()).ToShortDateString();
//publiship order
bool?_checked = wwi_func.vbool(_rd["PublishipOrder"].ToString());
this.dximgJobPubliship.ClientVisible = _checked.Value;
this.dxlblJobPubliship.ClientVisible = _checked.Value;
//job closed
_checked = wwi_func.vbool(_rd["JobClosed"].ToString());
this.dximgJobClosed.ClientVisible = _checked.Value;
this.dxlblJobClosed.ClientVisible = _checked.Value;
//hot job
_checked = wwi_func.vbool(_rd["HotJob"].ToString());
this.dximgJobHot.ClientVisible = _checked.Value;
this.dxlblJobHot.ClientVisible = _checked.Value;
_rowcount++;
}
if (_rowcount == 0)
{
this.dxlblInfo.Text = "No record found for Order number " + _orderno.ToString();
this.dxpnlMsg.ClientVisible = true;
}
}
else //new record
{
this.dxlblOrderNo.Text = "New record";
}
}
catch (Exception ex)
{
string _err = ex.Message.ToString();
this.dxlblErr.Text = _err;
this.dxpnlErr.ClientVisible = true;
}
}
protected string get_cartons_html(string orderid)
{
//html table formating
string _htmltable = "<p><table cellpadding=\"4px\" cellspacing=\"0px\"; border=\"1\" style=\"border: solid 1px #aca899; border-collapse:collapse;\">{0}</table></p>";
//html formatting for rows
//if cartons couunt = 0 we don't need columns for carton dimensions
string _htmlrow = "<tr><td bgcolor=\"#e8edff\">{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td></tr>";
//id comparisons
string _currentid = null;
string _id = null;
//riw pos in reader
int _row = 1;
//stringbuilder for table
StringBuilder _msg = new StringBuilder();
//get titles and cartons based on order id
string[] _cols2 = { "PublishipAdvanceTitleTable.PATitleID", "PublishipAdvanceTitleTable.PAOrderID", "PublishipAdvanceTitleTable.Title", "PublishipAdvanceCartonTable.CartonLength", "PublishipAdvanceCartonTable.CartonWidth", "PublishipAdvanceCartonTable.CartonHeight", "PublishipAdvanceCartonTable.CartonWeight" };
SubSonic.SqlQuery _query = new SubSonic.SqlQuery();
_query = DAL.Logistics.DB.Select(_cols2).From("PublishipAdvanceTitleTable").LeftOuterJoin("PublishipAdvanceCartonTable", "PATitleID", "PublishipAdvanceTitleTable", "PATitleID").Where("PAOrderID").IsEqualTo(orderid).OrderAsc("PATitleID");
IDataReader _rd = _query.ExecuteReader();
//append header row
string[] _items = { "Title", "Carton #", "Carton Length", "Carton Width", "Carton Height", "Carton Weight" };
_msg.AppendFormat(_htmlrow, _items);
while (_rd.Read())
{
_id = _rd["PATitleID"] != null ? _rd["PATitleID"].ToString() : ""; //_rd["Title"] != null ? _rd["Title"].ToString() : "";
string _title = "";
if (_id != _currentid || _row == 1)
{
//new title row to table
_currentid = _id;
_title = _rd["Title"] != null ? _rd["Title"].ToString() : "";
}
//append new row to table
_items[0] = _title;
_items[1] = _row.ToString();
_items[2] = _rd["CartonLength"] != null ? _rd["CartonLength"].ToString() : "";
_items[3] = _rd["CartonWidth"] != null ? _rd["CartonWidth"].ToString() : "";
_items[4] = _rd["CartonHeight"] != null ? _rd["CartonHeight"].ToString() : "";
_items[5] = _rd["CartonWeight"] != null ? _rd["CartonWeight"].ToString() : "";
_msg.AppendFormat(_htmlrow, _items);
//increment row number
_row++;
}
_htmltable = string.Format(_htmltable, _msg.ToString());
return(_htmltable);
}
//end incremental filtering of company name
#endregion
#region addresses by companyid
protected void bind_cbo_addresses(Int32 companyid)
{
SubSonic.SqlQuery query = new SubSonic.SqlQuery();
IDataReader _rd;
try
{
//testing************
//DataTable _dt = new DataTable();
//_dt.Columns.Add("DeliveryAddress", typeof(string));
//_dt.Columns.Add("DestinationCountry", typeof(string));
//_dt.Columns.Add("ID", typeof(int));
//DataRow _dr1 = _dt.NewRow();
//_dr1["DeliveryAddress"] = "Test address 1";
//_dr1["DestinationCountry"] = "UK";
//_dr1["ID"] = 1;
//_dt.Rows.Add(_dr1);
//DataRow _dr2 = _dt.NewRow();
//_dr2["DeliveryAddress"] = "Another address";
//_dr2["DestinationCountry"] = "USA";
//_dr2["ID"] = 2;
//_dt.Rows.Add(_dr2);
//this.dxcboaddress.DataSource = _dt;
//this.dxcboaddress.ValueField = "ID";
//this.dxcboaddress.DataBind();
string[] _cols = { "PublishipAdvanceOrderTable.Payee", "PublishipAdvanceOrderTable.DeliveryAddress, PublishipAdvanceOrderTable.DestinationCountry, PublishipAdvanceOrderTable.CompanyID, PublishipAdvanceOrderTable.OrderID" };
if (companyid != -1)
{
query = DAL.Logistics.DB.Select(_cols).From("PublishipAdvanceOrderTable").Where("CompanyID").IsEqualTo(companyid).And("DeliveryAddress").IsNotNull().Distinct().OrderAsc("Payee");
}
else
{
query = DAL.Logistics.DB.Select(_cols).From("PublishipAdvanceOrderTable").And("DeliveryAddress").IsNotNull().Distinct().OrderAsc("Payee");
}
//string _sql = query.ToString();
_rd = query.ExecuteReader();
//this.dxcboaddress.DataSource = _rd;
//need a unique value field or data will not bind, but no need to specify textfield as we have multiple columnns
//this.dxcboaddress.ValueField = "OrderID";
//this.dxcboaddress.DataBind();
this.dxgridAddress.DataSource = _rd;
this.dxgridAddress.KeyFieldName = "OrderID";
this.dxgridAddress.DataBind();
}
catch (Exception ex)
{
show_error_message(ex.Message.ToString());
}
}
protected void bind_eta_grid(int voyageid)
{
string[] _cols = { "VoyageETASubTable.VoyageETASubID", "VoyageETASubTable.VoyageID", "VoyageETASubTable.DestinationPortID", "VoyageETASubTable.ETA", "PortTable.PortName" };
SubSonic.SqlQuery _qry = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.VoyageETASubTable).
LeftOuterJoin(DAL.Logistics.PortTable.PortIDColumn, DAL.Logistics.VoyageETASubTable.DestinationPortIDColumn)
.WhereExpression("VoyageID").IsEqualTo(voyageid);
DataTable _dt = _qry.ExecuteDataSet().Tables[0];
this.dxgrdETA.DataSource = _dt;
this.dxgrdETA.KeyFieldName = "VoyageETASubID";
this.dxgrdETA.DataBind();
}
public string get_company_group(int requestCompanyId)
{
string _cg = "0";
string[] _tcols = { "NameAndAddressBook.CompanyId", "NameAndAddressBook.Pricer_Group" };
SubSonic.SqlQuery _qry = DAL.Logistics.DB.Select(_tcols).From(DAL.Logistics.Tables.NameAndAddressBook).Where("CompanyID").IsEqualTo(requestCompanyId);
DataSet _dt = _qry.ExecuteDataSet();
if (_dt.Tables.Count > 0 && _dt.Tables[0].Rows.Count > 0)
{
_cg = !string.IsNullOrEmpty(_dt.Tables[0].Rows[0]["Pricer_Group"].ToString()) ? _dt.Tables[0].Rows[0]["Pricer_Group"].ToString() : "0";
}
return(_cg);
}
protected void dxcbocompany_ItemRequestedByValue(object source, DevExpress.Web.ASPxEditors.ListEditItemRequestedByValueEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
//use datareaders - much faster than loading into collections
string[] _cols = { "NameAndAddressBook.CompanyID, NameAndAddressBook.CompanyName, NameAndAddressBook.Customer" };
Int32 _id = wwi_func.vint(e.Value.ToString());
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).WhereExpression("CompanyID").IsEqualTo(_id);
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "CompanyID";
_combo.TextField = "CompanyName";
_combo.DataBind();
}
//end send advance email
#endregion
#region company filters
/// <summary>
/// incremental filtering and partial loading of name and address book for speed
/// both ItemsRequestedByFilterCondition and ItemRequestedByValue must be set up for this to work
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcbocompany_ItemsRequestedByFilterCondition(object source, DevExpress.Web.ASPxEditors.ListEditItemsRequestedByFilterConditionEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
string _filter = !string.IsNullOrEmpty(e.Filter) ? e.Filter : "";
{
//use datareaders - much faster than loading into collections
string[] _cols = { "NameAndAddressBook.CompanyID, NameAndAddressBook.CompanyName, NameAndAddressBook.Customer" };
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex +1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("%{0}%", e.Filter.ToString())).And("Customer").IsEqualTo(true) ;
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex + 1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("%{0}%", e.Filter.ToString()));
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "CompanyID";
_combo.TextField = "CompanyName";
_combo.DataBind();
}
}
protected void dxcbotitle_ItemRequestedByValue(object source, DevExpress.Web.ASPxEditors.ListEditItemRequestedByValueEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
int _printerid = wwi_func.vint(this.dxlblprinter2.Text.ToString());
//use datareaders - much faster than loading into collections
string[] _cols = { "PublishipAdvanceTitleTable.PATitleID, PublishipAdvanceTitleTable.Title, PublishipAdvanceOrderTable.PrinterID" };
Int32 _id = wwi_func.vint(e.Value.ToString());
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.PublishipAdvanceOrderTable).LeftOuterJoin("PublishipAdvanceTitleTable", "PAOrderID", "PublishipAdvanceOrderTable", "OrderID").WhereExpression("PATitleID").IsEqualTo(_id).And("PrinterId").IsEqualTo(_printerid);
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "PATitleID";
_combo.TextField = "Title";
_combo.DataBind();
}
/// <summary>
/// applies to the Title column editform template
/// incremental filtering and partial loading of name and address book for speed
/// both ItemsRequestedByFilterCondition and ItemRequestedByValue must be set up for this to work
/// company name is only available to publiship users
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcbotitle_ItemsRequestedByFilterCondition(object source, DevExpress.Web.ASPxEditors.ListEditItemsRequestedByFilterConditionEventArgs e)
{
//ASPxComboBox _combo = (ASPxComboBox)source;
ASPxComboBox _combo = ((ASPxComboBox)this.dxgridTitles.FindEditRowCellTemplateControl(
this.dxgridTitles.Columns["colTitle"] as GridViewDataComboBoxColumn, "dxcbotitle"));
//use datareaders - much faster than loading into collections
string[] _cols = { "ItemTable.TitleID, ItemTable.Title" };
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex +1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("%{0}%", e.Filter.ToString())).And("Customer").IsEqualTo(true) ;
SubSonic.SqlQuery _query = new SubSonic.SqlQuery();
_query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.ItemTable).Paged(e.BeginIndex + 1, e.EndIndex + 1, "TitleID").WhereExpression("Title").StartsWith(string.Format("{0}%", e.Filter.ToString()));
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "TitleID";
_combo.ValueType = typeof(Int32);
_combo.TextField = "Title";
_combo.DataBind();
}
protected void dxcboCountry_ItemsRequestedByFilterCondition(object source, ListEditItemsRequestedByFilterConditionEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
string _filter = !string.IsNullOrEmpty(e.Filter) ? e.Filter : "";
//use datareaders - much faster than loading into collections
string[] _cols = { "CountryID, CountryName" };
string[] _order = { "CountryName" };
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex + 1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("%{0}%", e.Filter.ToString()));
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.CountryTable).Paged(e.BeginIndex + 1, e.EndIndex + 1).Where("CountryName").StartsWith(string.Format("{0}%", _filter));
IDataReader _rd = _query.ExecuteReader();
_combo.ValueField = "CountryID";
_combo.ValueType = typeof(int);
_combo.TextField = "CountryName";
_combo.DataBindItems();
}
/// <summary>
/// DEPRECATED we are using incremental filtering for origin ports and destination ports
/// origin port combo
/// </summary>
/// <param name="voyageid">from voyage combobox int</param>
protected void bind_origin_port()
{
GridViewDataComboBoxColumn _combo = (GridViewDataComboBoxColumn)this.dxgrdETS.Columns["colOriginPortID"];
if (_combo != null)
{
string[] _cols = { "PortTable.PortID, PortTable.PortName" };
string[] _order = { "PortName" };
SubSonic.SqlQuery _qry = new SubSonic.SqlQuery();
_qry = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.PortTable).OrderAsc(_order);
//bind origin ports
IDataReader _rd = _qry.ExecuteReader();
_combo.PropertiesComboBox.DataSource = _rd;
_combo.PropertiesComboBox.ValueField = "PortID";
_combo.PropertiesComboBox.ValueType = typeof(int);
_combo.PropertiesComboBox.TextField = "PortName";
//can't databinditems here, do it in cell editor intialise
}
}
//14/07/14 dxcboVesselID_ItemRequestedByValue and dxcboVesselID_ItemsRequestedByFilterCondition DEPRECATED
//can't use OnItemsRequestedByFilterCondition and OnItemRequestedByValue on this combo as server-side filtring makes the search case sensitive
//incremental filtering for large datasets on combos
/// <summary>
/// incremental filtering and partial loading of name and address book for speed
/// both ItemsRequestedByFilterCondition and ItemRequestedByValue must be set up for this to work
/// company name is only available to publiship users
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcboCountry_ItemRequestedByValue(object source, ListEditItemRequestedByValueEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
Int32 _id = 0;
if (e.Value != null)
{
_id = wwi_func.vint(e.Value.ToString());
}
string[] _cols = { "CountryID, CountryName" };
string[] _order = { "CountryName" };
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.CountryTable).WhereExpression("CountryID").IsEqualTo(_id);
IDataReader _rd = _query.ExecuteReader();
_combo.ValueField = "CountryID";
_combo.ValueType = typeof(int);
_combo.TextField = "CountryName";
_combo.DataBindItems();
}
/// <summary>
/// bind port combo when editting
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void dxgrdETA_CellEditorInitialize(object sender, ASPxGridViewEditorEventArgs e)
{
if (e.Column.FieldName == "DestinationPortID")
{
ASPxComboBox _combo = (ASPxComboBox)e.Editor;
string[] _cols = { "PortTable.PortID, PortTable.PortName" };
string[] _order = { "PortName" };
SubSonic.SqlQuery _qry = new SubSonic.SqlQuery();
_qry = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.PortTable).OrderAsc(_order);
//bind origin ports
IDataReader _rd = _qry.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "PortID";
_combo.ValueType = typeof(int);
_combo.TextField = "PortName";
_combo.DataBindItems();
}
}
public IList <Int32> get_orders_by_houseBL(string housebl)
{
IList <Int32> _orders = null;
try
{
string[] _cols = { "OrderTable.OrderNumber" };
SubSonic.SqlQuery _qry = new SubSonic.SqlQuery();
_qry = new Select(_cols).From(OrderTable.Schema).
Where(OrderTable.HouseBLNUmberColumn).IsEqualTo(housebl);
_orders = _qry.ExecuteTypedList <Int32>();
}
catch (Exception ex)
{
string _error = ex.Message.ToString();
}
return(_orders);
}
//end pdf
#endregion
#region incremental filtering for previous titles
/// <summary>
/// incremental filtering and partial loading of name and address book for speed
/// both ItemsRequestedByFilterCondition and ItemRequestedByValue must be set up for this to work
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcbotitle_ItemsRequestedByFilterCondition(object source, DevExpress.Web.ASPxEditors.ListEditItemsRequestedByFilterConditionEventArgs e)
{
//restricted to logged in user
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
int _printerid = wwi_func.vint(this.dxlblprinter2.Text.ToString());
string _filter = !string.IsNullOrEmpty(e.Filter) ? e.Filter : "";
{
//use datareaders - much faster than loading into collections
string[] _cols = { "PublishipAdvanceTitleTable.PATitleID, PublishipAdvanceTitleTable.Title, PublishipAdvanceOrderTable.PrinterID" };
//string[] _cols = { "PublishipAdvanceTitleTable.PATitleID, PublishipAdvanceTitleTable.Title" };
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.PublishipAdvanceOrderTable).LeftOuterJoin("PublishipAdvanceTitleTable", "PAOrderID", "PublishipAdvanceOrderTable", "OrderID").Paged(e.BeginIndex + 1, e.EndIndex + 1, "PATitleID").WhereExpression("Title").Like(string.Format("%{0}%", e.Filter.ToString())).And("PrinterId").IsEqualTo(_printerid);
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.PublishipAdvanceTitleTable).Paged(e.BeginIndex + 1, e.EndIndex + 1, "PATitleID").WhereExpression("Title").Like(string.Format("%{0}%", e.Filter.ToString()));
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "PATitleID";
_combo.TextField = "Title";
_combo.DataBind();
}
}
//container search
protected void dxcboContainer_ItemRequestedByValue(object source, DevExpress.Web.ASPxEditors.ListEditItemRequestedByValueEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
Int32 _id = 0;
if (e.Value != null)
{
_id = wwi_func.vint(e.Value.ToString());
}
//use datareaders - much faster than loading into collections
string[] _cols = { "ContainerSubID", "ContainerNumber" };
SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Views.PearsonRugbyContainerView).WhereExpression("ContainerSubID").IsEqualTo(_id);
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "ContainerSubID";
_combo.TextField = "ContainerNumber";
_combo.DataBindItems();
}
/// <summary>
/// DEPRECATED we are using incremental filtering for origin ports and destination ports
/// origin port combo
/// </summary>
/// <param name="voyageid">from voyage combobox int</param>
protected void bind_origin_port()
{
GridViewDataComboBoxColumn _combo = (GridViewDataComboBoxColumn)this.dxgrdETS.Columns["colOriginPortID"];
if (_combo != null)
{
string[] _cols = { "PortTable.PortID, PortTable.PortName" };
string[] _order = { "PortName" };
SubSonic.SqlQuery _qry = new SubSonic.SqlQuery();
_qry = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.PortTable).OrderAsc(_order);
//bind origin ports
IDataReader _rd = _qry.ExecuteReader();
_combo.PropertiesComboBox.DataSource = _rd;
_combo.PropertiesComboBox.ValueField = "PortID";
_combo.PropertiesComboBox.ValueType = typeof(int);
_combo.PropertiesComboBox.TextField = "PortName";
//can't databinditems here, do it in cell editor intialise
}
}
protected void dxcboDestinationPortID_ItemRequestedByValue(object source, DevExpress.Web.ASPxEditors.ListEditItemRequestedByValueEventArgs e)
{
ASPxComboBox _combo = ((ASPxComboBox)this.dxgrdETS.FindEditRowCellTemplateControl(
this.dxgrdETS.Columns["colDestinationPortID"] as GridViewDataComboBoxColumn, "dxcboDestinationPortID"));
Int32 _id = 0;
if (e.Value != null) { _id = wwi_func.vint(e.Value.ToString()); }
//use datareaders - much faster than loading into collections
string[] _cols = { "PortTable.PortID, PortTable.PortName" };
SubSonic.SqlQuery _query = new SubSonic.SqlQuery();
_query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.PortTable).WhereExpression("PortID").IsEqualTo(_id);
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "PortID";
_combo.ValueType = typeof(Int32);
_combo.TextField = "PortName";
_combo.DataBind();
}
protected void dxcbocompany_ItemRequestedByValue(object source, DevExpress.Web.ASPxEditors.ListEditItemRequestedByValueEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
Int32 _id = 0;
if (Page.Session["user"] != null) //if publiship user allow filter to be used otherwise filter null
{
Int32 _companyid = wwi_func.vint(((UserClass)Page.Session["user"]).CompanyId.ToString());
if (_companyid == -1 || has_company_filters(_companyid))
//if (_companyid == -1 || _companyid == 1865)
{
if (e.Value != null) { _id = wwi_func.vint(e.Value.ToString()); }
//use datareaders - much faster than loading into collections
string[] _cols = { "NameAndAddressBook.CompanyID, NameAndAddressBook.CompanyName, NameAndAddressBook.Customer" };
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).WhereExpression("CompanyID").IsEqualTo(_id);
SubSonic.SqlQuery _query = new SubSonic.SqlQuery();
if (_companyid == -1)
{
_query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).WhereExpression("CompanyID").IsEqualTo(_id);
}
else
{
//Int32[] _args = { 1865, 1167, 22848 };
//System.Collections.ArrayList
//get list of all possible ID's otherwise search will just find the 1st match in xml
IList<string> _args = wwi_func.array_from_xml("xml\\company_iso.xml", "companylist/company[id='" + _companyid + "']/visibleitems/item/itemid");
_query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).WhereExpression("CompanyID").In(_args).OrderAsc(new string[] { "CountryID", "CompanyName" });
}
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "CompanyID";
_combo.TextField = "CompanyName";
_combo.DataBind();
}
}
}
/// <summary>
/// incremental filtering and partial loading of name and address book for speed
/// both ItemsRequestedByFilterCondition and ItemRequestedByValue must be set up for this to work
/// company name is only available to publiship users
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcbocompany_ItemsRequestedByFilterCondition(object source, DevExpress.Web.ASPxEditors.ListEditItemsRequestedByFilterConditionEventArgs e)
{
DevExpress.Web.ASPxEditors.ASPxComboBox _combo = (DevExpress.Web.ASPxEditors.ASPxComboBox)source;
if (Page.Session["user"] != null) //if publiship user allow filter to be used otherwise filter null
{
Int32 _companyid = wwi_func.vint(((UserClass)Page.Session["user"]).CompanyId.ToString());
if (_companyid == -1 || has_company_filters(_companyid))
//if (_companyid == -1 || _companyid == 1865 )
{
string _filter = !string.IsNullOrEmpty(e.Filter) ? e.Filter : "";
//use datareaders - much faster than loading into collections
string[] _cols = { "NameAndAddressBook.CompanyID, NameAndAddressBook.CompanyName, NameAndAddressBook.Customer" };
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex +1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("%{0}%", e.Filter.ToString())).And("Customer").IsEqualTo(true) ;
SubSonic.SqlQuery _query = new SubSonic.SqlQuery();
if (_companyid == -1)
{
_query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex + 1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("%{0}%", e.Filter.ToString()));
}
else
{
//check in xml file company_filter for to see if this user can select any companies (use "companylist/company[@id=''" if you want to search on attributes)
IList<string> _args = wwi_func.array_from_xml("xml\\company_iso.xml", "companylist/company[id='" + _companyid + "']/visibleitems/item/itemname");
//string[] _args = { "Education Development Corporation", "Usborne Publishing Limited", "Kane Miller Book Publishers" };
_query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex + 1, "CompanyID").WhereExpression("CompanyName").In(_args);
}
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "CompanyID";
_combo.TextField = "CompanyName";
_combo.DataBind();
}
}
}
//end address
/// <summary>
/// titles detail grid
/// </summary>
/// <summary>
/// applies to the Title column editform template
/// incremental filtering and partial loading of name and address book for speed
/// both ItemsRequestedByFilterCondition and ItemRequestedByValue must be set up for this to work
/// company name is only available to publiship users
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcbotitle_ItemsRequestedByFilterCondition(object source, DevExpress.Web.ASPxEditors.ListEditItemsRequestedByFilterConditionEventArgs e)
{
string _orderno = this.dxlblOrderNo.Text.ToString();
if (_orderno != "")
{
ASPxComboBox _combo = (ASPxComboBox)source;
//ASPxComboBox _combo = ((ASPxComboBox)this.dxgridTitles.FindEditRowCellTemplateControl(
// this.dxgridTitles.Columns["colTitle"] as GridViewDataComboBoxColumn, "dxcbotitle"));
//use datareaders - much faster than loading into collections
string[] _cols = { "ItemTable.TitleID, ItemTable.Title" };
SubSonic.SqlQuery _query = new SubSonic.SqlQuery();
_query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.ItemTable).Paged(e.BeginIndex + 1, e.EndIndex + 1, "TitleID").Where("Title").StartsWith(string.Format("{0}%", e.Filter.ToString())).And("OrderNumber").IsEqualTo(_orderno);
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "TitleID";
_combo.ValueType = typeof(Int32);
_combo.TextField = "Title";
_combo.DataBind();
}
}
public IList<Int32> get_orders_by_houseBL(string housebl)
{
IList<Int32> _orders = null;
try
{
string[] _cols = { "OrderTable.OrderNumber" };
SubSonic.SqlQuery _qry = new SubSonic.SqlQuery();
_qry = new Select(_cols).From(OrderTable.Schema).
Where(OrderTable.HouseBLNUmberColumn).IsEqualTo(housebl);
_orders = _qry.ExecuteTypedList<Int32>();
}
catch (Exception ex)
{
string _error = ex.Message.ToString();
}
return _orders;
}
//end incremental filtering of company name
#endregion
#region addresses by companyid
protected void bind_cbo_addresses(Int32 companyid)
{
SubSonic.SqlQuery query = new SubSonic.SqlQuery();
IDataReader _rd;
try
{
//testing************
//DataTable _dt = new DataTable();
//_dt.Columns.Add("DeliveryAddress", typeof(string));
//_dt.Columns.Add("DestinationCountry", typeof(string));
//_dt.Columns.Add("ID", typeof(int));
//DataRow _dr1 = _dt.NewRow();
//_dr1["DeliveryAddress"] = "Test address 1";
//_dr1["DestinationCountry"] = "UK";
//_dr1["ID"] = 1;
//_dt.Rows.Add(_dr1);
//DataRow _dr2 = _dt.NewRow();
//_dr2["DeliveryAddress"] = "Another address";
//_dr2["DestinationCountry"] = "USA";
//_dr2["ID"] = 2;
//_dt.Rows.Add(_dr2);
//this.dxcboaddress.DataSource = _dt;
//this.dxcboaddress.ValueField = "ID";
//this.dxcboaddress.DataBind();
string[] _cols = { "PublishipAdvanceOrderTable.Payee", "PublishipAdvanceOrderTable.DeliveryAddress, PublishipAdvanceOrderTable.DestinationCountry, PublishipAdvanceOrderTable.CompanyID, PublishipAdvanceOrderTable.OrderID" };
if (companyid != -1)
{
query = DAL.Logistics.DB.Select(_cols).From("PublishipAdvanceOrderTable").Where("CompanyID").IsEqualTo(companyid).And("DeliveryAddress").IsNotNull().Distinct().OrderAsc("Payee");
}
else
{
query = DAL.Logistics.DB.Select(_cols).From("PublishipAdvanceOrderTable").And("DeliveryAddress").IsNotNull().Distinct().OrderAsc("Payee");
}
//string _sql = query.ToString();
_rd = query.ExecuteReader();
//this.dxcboaddress.DataSource = _rd;
//need a unique value field or data will not bind, but no need to specify textfield as we have multiple columnns
//this.dxcboaddress.ValueField = "OrderID";
//this.dxcboaddress.DataBind();
this.dxgridAddress.DataSource = _rd;
this.dxgridAddress.KeyFieldName = "OrderID";
this.dxgridAddress.DataBind();
}
catch (Exception ex)
{
show_error_message(ex.Message.ToString());
}
}
protected string get_titles_html(string orderid)
{
//html table formating
string _htmltable = "<p><table cellpadding=\"4px\" cellspacing=\"0px\"; border=\"1\" style=\"border: solid 1px #aca899; border-collapse:collapse;\">{0}</table></p>";
//html formatting for rows
//if cartons couunt = 0 we don't need columns for carton dimensions
string _htmlrow = "<tr><td bgcolor=\"#ffffff\">{0}</td></tr>";
//id comparisons
string _currentid = null;
string _id = null;
//riw pos in reader
int _row = 1;
//stringbuilder for table
StringBuilder _msg = new StringBuilder();
//get titles and cartons based on order id
string[] _cols2 = { "PublishipAdvanceTitleTable.PATitleID", "PublishipAdvanceTitleTable.PAOrderID", "PublishipAdvanceTitleTable.Title" };
SubSonic.SqlQuery _query = new SubSonic.SqlQuery();
_query = DAL.Logistics.DB.Select(_cols2).From("PublishipAdvanceTitleTable").Where("PAOrderID").IsEqualTo(orderid).OrderAsc("PATitleID");
IDataReader _rd = _query.ExecuteReader();
//append header row
_msg.AppendFormat(_htmlrow, "Title");
while (_rd.Read())
{
_id = _rd["PATitleID"] != null ? _rd["PATitleID"].ToString() : ""; //_rd["Title"] != null ? _rd["Title"].ToString() : "";
string _title = "";
if (_id != _currentid || _row == 1)
{
//new title row to table
_currentid = _id;
_title = _rd["Title"] != null ? _rd["Title"].ToString() : "";
}
//append new row to table
_msg.AppendFormat(_htmlrow, _title);
//increment row number
_row++;
}
_htmltable = string.Format(_htmltable, _msg.ToString());
return _htmltable;
}
public UserClass Login(string txtUser, string txtPassword)
{
//26/05/2011 we now encrypt passwords
txtPassword = wwi_security.DecryptString(txtPassword, "publiship");
//initialise
UserClass UserLogin = new UserClass();
UserLogin.ID = Guid.Empty;
int _rowcount = 0;
IDataReader _rd;
SubSonic.SqlQuery query = new SubSonic.SqlQuery();
try
{
string[] _cols = { "ContactTable.ContactID", "ContactTable.ContactName", "ContactTable.Name", "ContactTable.ContactInitials", "ContactTable.CompanyID", "ContactTable.DefaultView", "ContactTable.IsEditor", "ContactTable.EMail", "ContactTable.ControllingOfficeID", "NameAndAddressBook.CompanyName", "NameAndAddressBook.TelNo", "NameAndAddressBook.Pricer_Group" };
//query = DB.Select(_cols).From("ContactTable").LeftOuterJoin("NameAndAddressBook", "CompanyID", "ContactTable", "CompanyID").Where("ContactName").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true);
//have check ContactName AND Name as usage is inconsistent in database
query = DB.Select(_cols).From("ContactTable").LeftOuterJoin("NameAndAddressBook", "CompanyID", "ContactTable", "CompanyID").
Where("ContactName").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true).
Or("Name").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true);
_rd = query.ExecuteReader();
while (_rd.Read())
{
UserLogin.ID = Guid.NewGuid();
UserLogin.UserId = (Int32)_rd["ContactID"];
UserLogin.UserName = Convert.ToString(_rd["ContactName"]); ; //Convert.ToString(_rd["ContactName"]);
UserLogin.UserInitials = _rd["ContactInitials"] != null ? Convert.ToString(_rd["ContactInitials"]) : "";
UserLogin.OfficeId = -1; //external client
UserLogin.CompanyId = (Int32)_rd["CompanyID"];
UserLogin.DefaultView = (Int32)_rd["DefaultView"];
UserLogin.IsEditor = (byte)_rd["IsEditor"];
UserLogin.mailTo = _rd["EMail"] != null ? Convert.ToString(_rd["EMail"]) : "";
UserLogin.OfficeName = _rd["CompanyName"] != null ? Convert.ToString(_rd["CompanyName"]) : "";
UserLogin.telNo = _rd["TelNo"] != null ? Convert.ToString(_rd["TelNo"]) : "";
UserLogin.controlOfficeId = (Int32)_rd["ControllingOfficeID"]; //this should be the new controller office link id
UserLogin.companyGroup = _rd["Pricer_Group"] != null ? wwi_func.vint(_rd["Pricer_Group"].ToString()) : 0;
UserLogin.loginValue = 1; //successful login
_rowcount++;
}
//Query _qryb = new Query(Tables.ContactTable).WHERE("ContactName", Comparison.Equals, txtUser).AND("Password", Comparison.Equals, txtPassword).AND("Live", Comparison.Equals, true);
//ContactTableCollection _contact = new ContactTableCollection();
//_contact.LoadAndCloseReader(_qryb.ExecuteReader());
//
//if (_contact.Count != 0)
// {
// UserLogin.ID = Guid.NewGuid();
// UserLogin.UserId = (Int32)_contact[0].ContactID; ;
// UserLogin.UserName = (String)_contact[0].ContactName;
// UserLogin.UserInitials = (String)_contact[0].ContactInitials;
// UserLogin.OfficeId = -1; //external client
// UserLogin.CompanyId = (Int32)_contact[0].CompanyID;
// UserLogin.DefaultView = (Int32)_contact[0].DefaultView;
// UserLogin.IsEditor = (Int32)_contact[0].IsEditor;
//}
if (_rowcount == 0) //try internal user table instead
{
//string[] _cols2 = { "EmployeesTable.EmployeeID, EmployeesTable.Name, EmployeesTable.OfficeID, EmployeesTable.DefaultView, EmployeesTable.IsEditor, EmployeesTable.EmailAddress", "OfficeTable.OfficeName" };
string[] _cols2 = { "EmployeesTable.EmployeeID", "EmployeesTable.Name", "EmployeesTable.OfficeID", "EmployeesTable.DefaultView", "EmployeesTable.IsEditor", "EmployeesTable.EmailAddress", "OfficeTable.OfficeName" };
query = DB.Select(_cols2).From("EmployeesTable").LeftOuterJoin("OfficeTable", "OfficeID", "EmployeesTable", "OfficeID").Where("Name").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true);
_rd = query.ExecuteReader();
while (_rd.Read())
{
UserLogin.ID = Guid.NewGuid();
UserLogin.UserId = (Int32)_rd["EmployeeID"];
UserLogin.UserName = (String)_rd["Name"];
UserLogin.UserInitials = ""; //does not apply to internal user
UserLogin.OfficeId = (Int32)_rd["OfficeID"];
UserLogin.CompanyId = -1; //does not apply to internal user
UserLogin.DefaultView = (Int32)_rd["DefaultView"];
UserLogin.IsEditor = (byte)_rd["IsEditor"];
UserLogin.mailTo = _rd["EmailAddress"] != null ? Convert.ToString(_rd["EmailAddress"]) : "";
UserLogin.OfficeName = _rd["OfficeName"] != null ? Convert.ToString(_rd["OfficeName"]) : "";
UserLogin.telNo = "";
UserLogin.controlOfficeId = -1; //does not apply to internal user
UserLogin.companyGroup = 0;
UserLogin.loginValue = 1; //successful login
_rowcount++;
}
//build query using username and password
//check smaller employees table THEN contact table
//Query _qry = new Query(Tables.EmployeesTable).WHERE("Name", Comparison.Equals, txtUser).AND("Password", Comparison.Equals, txtPassword).AND("Live", Comparison.Equals, true);
//EmployeesTableCollection _employ = new EmployeesTableCollection();
//_employ.LoadAndCloseReader(_qry.ExecuteReader());
//
//if (_employ.Count != 0)
//{
// UserLogin.ID = Guid.NewGuid();
// UserLogin.UserId = (Int32)_employ[0].EmployeeID;
// UserLogin.UserName = (String)_employ[0].Name;
// UserLogin.UserInitials = "";
// UserLogin.OfficeId = (Int32)_employ[0].OfficeID;
// UserLogin.CompanyId = -1;
// UserLogin.DefaultView = (Int32)_employ[0].DefaultView;
// UserLogin.IsEditor = (Int32)_employ[0].IsEditor;
//
}
}
catch (Exception ex)
{
string _ex = ex.Message.ToString();
//set guid or login will end up returning as null
UserLogin.ID = Guid.NewGuid();
//return indicator that there was an error do not return error message as we want to hide that from user
UserLogin.loginValue = 0;
}
finally
{
if (UserLogin.ID == Guid.Empty) { UserLogin = null; }
//if (UserLogin.ID == Guid.Empty) return null;
//else
//{
// return UserLogin;
//}
}
return UserLogin;
}
public IDataReader GetOrderCountByEtaMonth(Int32 companyid)
{
DateTime today = DateTime.Today;
DateTime _firstday = new DateTime(today.Year, today.Month, 1);
DateTime _lastday = _firstday.AddMonths(1).AddDays(-1);
Aggregate _agr1 = Aggregate.GroupBy(OrderTable.EtaColumn);
Aggregate _agr2 = Aggregate.Count(OrderTable.OrderIDColumn, "SumOrderID");
Aggregate[] _agrs = {_agr1, _agr2};
SubSonic.SqlQuery _qry = new SubSonic.SqlQuery();
if (companyid != -1) //restrict to specified company
{
_qry = new Select(_agrs).From(OrderTable.Schema).
Where(OrderTable.CompanyIDColumn).IsEqualTo(companyid).And(OrderTable.EtaColumn).IsBetweenAnd(_firstday, _lastday);
}
else
{
_qry = new Select(_agrs).From(OrderTable.Schema).
Where(OrderTable.EtaColumn).IsBetweenAnd(_firstday, _lastday);
}
IDataReader _rd = _qry.ExecuteReader();
return _rd;
}
/// <summary>
/// bind port combo when editting
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void dxgrdETA_CellEditorInitialize(object sender, ASPxGridViewEditorEventArgs e)
{
if (e.Column.FieldName == "DestinationPortID")
{
ASPxComboBox _combo = (ASPxComboBox)e.Editor;
string[] _cols = { "PortTable.PortID, PortTable.PortName" };
string[] _order = { "PortName" };
SubSonic.SqlQuery _qry = new SubSonic.SqlQuery();
_qry = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.PortTable).OrderAsc(_order);
//bind origin ports
IDataReader _rd = _qry.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "PortID";
_combo.ValueType = typeof(int);
_combo.TextField = "PortName";
_combo.DataBindItems();
}
}
protected void formOrder_DataBound(object sender, EventArgs e)
{
try
{
OrderTable _row = (OrderTable)this.formOrder.DataItem;
//icons
//publiship order
this.dximgJobPubliship.ClientVisible = _row != null? _row.PublishipOrder: false;
this.dxlblJobPubliship.ClientVisible = _row != null ? _row.PublishipOrder : false;
//job closed
this.dximgJobClosed.ClientVisible = _row != null ? _row.JobClosed : false;
this.dxlblJobClosed.ClientVisible = _row != null ? _row.JobClosed : false;
//hot job
this.dximgJobHot.ClientVisible = _row != null ? _row.HotJob : false;
this.dxlblJobHot.ClientVisible = _row != null ? _row.HotJob : false;
string _office = "";
int _officeid = 0;
if (this.formOrder.CurrentMode == FormViewMode.Insert)
{
//order number and office in header
this.dxlblOrderNo.Text = "[New Order]";
_officeid = Page.Session["user"] != null ? (int)((UserClass)Page.Session["user"]).OfficeId : 0;
_office = wwi_func.lookup_xml_string("xml\\office_names.xml", "value", _officeid.ToString(), "name");
}
else
{
this.dxlblOrderNo.Text = _row.OrderNumber.ToString();
_office = _row != null ? _row.OfficeIndicator : "";
_officeid = wwi_func.vint(wwi_func.lookup_xml_string("xml\\office_names.xml", "name", _office, "value"));
}
this.dxlbOrderDetails1.Text = "|" + _office;
//just pass it as a param
//this.dxhfOfficeID.Clear();
//this.dxhfOfficeID.Add("officeid", _officeid.ToString());
//16/10/13 view returned to this page and single summary query used to populate look-up values
//view moved to seperate page (Order_View.aspx) so we can avoid all these calls to the database!
if (this.formOrder.CurrentMode == FormViewMode.Edit || this.formOrder.CurrentMode == FormViewMode.Insert)
{
//have to bind the standard ddls here or they don't populate for new orders
bind_company_combos(_officeid);
bind_origin_combos(-1);
bind_dest_combos();
sub_decks();
}
else //readonly
{
string _test ="";
SubSonic.SqlQuery _q = new SubSonic.SqlQuery();
_q = DB.Select().From("view_order_summary").Where("OrderNumber").IsEqualTo(_row.OrderNumber);
DataTable _dt = _q.ExecuteDataSet().Tables[0];
//labels
ASPxLabel _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewController");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["OrderController"].ToString(); }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewContact");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["ContactName"].ToString(); }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewOps");
if (_lbl != null) { _lbl.Text = _lbl.Text = _dt.Rows[0]["OpsController"].ToString(); }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblContactEmail");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["EMail"].ToString(); }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewCompany");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["CompanyName"].ToString(); }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewPrinter");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["PrinterName"].ToString(); }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewCountry");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["OriginCountry"].ToString(); }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewOrigin");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["PlaceName"].ToString(); }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewOriginPort");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["OriginPort"].ToString(); }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewAgentAtOrigin");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["OriginAgent"].ToString();
_test = _dt.Rows[0]["OriginAgent"].ToString(); }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewDestPort");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["DestinationPort"].ToString(); }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewFinal");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["FinalDestination"].ToString(); }
//260211 some older jobs have an origin controller but no origin agent in those cases don't display the origin controller
if (!string.IsNullOrEmpty(_test))
{
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewOriginController");
if (_lbl != null) { _lbl.Text = _dt.Rows[0]["OriginPortController"].ToString(); }
}
//date formatting
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblDateCreated");
if (_lbl != null) { _lbl.Text = _lbl.Text != "" ? wwi_func.vdatetime(_lbl.Text).ToShortDateString() : ""; }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewExWorks");
if (_lbl != null) { _lbl.Text = _lbl.Text != "" ? wwi_func.vdatetime(_lbl.Text).ToShortDateString() : ""; }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewBookingReceived");
if (_lbl != null) { _lbl.Text = _lbl.Text != "" ? wwi_func.vdatetime(_lbl.Text).ToShortDateString() : ""; }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewCargoReady");
if (_lbl != null) { _lbl.Text = _lbl.Text != "" ? wwi_func.vdatetime(_lbl.Text).ToShortDateString() : ""; }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewDueWarehouse");
if (_lbl != null) { _lbl.Text = _lbl.Text != "" ? wwi_func.vdatetime(_lbl.Text).ToShortDateString() : ""; }
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewDocsApprovedDate");
if (_lbl != null) { _lbl.Text = _lbl.Text != "" ? wwi_func.vdatetime(_lbl.Text).ToShortDateString() : ""; }
//addresses
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblCompanyAddress");
if (_lbl != null)
{
_lbl.Text = _dt.Rows[0]["Address1"].ToString() + Environment.NewLine + _dt.Rows[0]["Address2"].ToString() +
Environment.NewLine + _dt.Rows[0]["Address3"].ToString() + Environment.NewLine + _dt.Rows[0]["CountryName"].ToString() +
Environment.NewLine + _dt.Rows[0]["TelNo"].ToString();
}
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblPrinterAddress");
if (_lbl != null)
{
_lbl.Text = _dt.Rows[0]["PrinterAdd1"].ToString() + Environment.NewLine + _dt.Rows[0]["PrinterAdd2"].ToString() +
Environment.NewLine + _dt.Rows[0]["PrinterAdd3"].ToString() + Environment.NewLine + _dt.Rows[0]["PrinterCountry"].ToString() +
Environment.NewLine + _dt.Rows[0]["PrinterTel"].ToString();
}
_lbl = (ASPxLabel)this.formOrder.FindControl("dxlblOriginAgentAddress");
if (_lbl != null)
{
_lbl.Text = _dt.Rows[0]["OriginAgentAddress1"].ToString() + Environment.NewLine + _dt.Rows[0]["OriginAgentAddress2"].ToString() +
Environment.NewLine + _dt.Rows[0]["OriginAgentAddress3"].ToString() + Environment.NewLine + _dt.Rows[0]["OriginAgentCountry"].ToString() +
Environment.NewLine + _dt.Rows[0]["OriginAgentTel"].ToString();
}
//deprecated look-up code
//if (this.formOrder.CurrentMode == FormViewMode.ReadOnly)
//{
// //text values for view template
// ASPxLabel _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewController");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("Name", "EmployeesTable", "EmployeeID", _row.OrderControllerID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewContact");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("ContactName", "ContactTable", "ContactID", _row.ContactID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewOps");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("Name", "EmployeesTable", "EmployeeID", _row.OperationsControllerID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewCompany");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("CompanyName", "view_delivery_address", "CompanyID", _row.CompanyID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewPrinter");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("PrinterName", "PrinterView", "CompanyID", _row.PrinterID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewCountry");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("CountryName", "CountryTable", "CountryID", _row.CountryID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewOrigin");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("PlaceName", "PlacesTable", "PlaceID", _row.OriginPointID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewOriginPort");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("PortName", "PortTable", "PortID", _row.PortID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewAgentAtOrigin");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("OriginAgent", "OriginAgentView", "OriginAgentID", _row.AgentAtOriginID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewDestPort");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("PortName", "PortTable", "PortID", _row.DestinationPortID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewFinal");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("PlaceName", "PlacesTable", "PlaceID", _row.FinalDestinationID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblViewOriginController");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("Name", "EmployeesTable", "EmployeeID", _row.OriginPortControllerID); }
// //address labels
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblCompanyAddress");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_multi_values("Address1,Address2,Address3,CountryName,TelNo", "view_delivery_address", "CompanyID", _row.CompanyID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblContactEmail");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_value("EMail", "ContactTable", "ContactID", _row.ContactID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblPrinterAddress");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_multi_values("PrinterAdd1,PrinterAdd2,PrinterAdd3,PrinterCountry,PrinterTel", "PrinterView", "CompanyID", _row.PrinterID); }
// _lbl = (ASPxLabel)this.formOrder.FindControl("dxlblOriginAgentAddress");
// if (_lbl != null) { _lbl.Text = wwi_func.lookup_multi_values("OriginAgentAddress1,OriginAgentAddress2,OriginAgentAddress3,OriginAgentCountry", "OriginAgentView", "OriginAgentID", _row.AgentAtOriginID); }
//}
}
}
catch (Exception ex)
{
string _ex = ex.Message.ToString();
this.dxlblErr.Text = _ex;
this.dxpnlErr.Visible = true;
}
}
//end incremental filtering of vessel name
/// <summary>
/// origin ports in ETS grid
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcboOriginPortID_ItemsRequestedByFilterCondition(object source, DevExpress.Web.ASPxEditors.ListEditItemsRequestedByFilterConditionEventArgs e)
{
//ASPxComboBox _combo = (ASPxComboBox)source;
ASPxComboBox _combo = ((ASPxComboBox)this.dxgrdETS.FindEditRowCellTemplateControl(
this.dxgrdETS.Columns["colOriginPortID"] as GridViewDataComboBoxColumn, "dxcboOriginPortID"));
//use datareaders - much faster than loading into collections
string[] _cols = { "PortTable.PortID, PortTable.PortName" };
//SubSonic.SqlQuery _query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.NameAndAddressBook).Paged(e.BeginIndex + 1, e.EndIndex +1, "CompanyID").WhereExpression("CompanyName").Like(string.Format("%{0}%", e.Filter.ToString())).And("Customer").IsEqualTo(true) ;
SubSonic.SqlQuery _query = new SubSonic.SqlQuery();
_query = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.PortTable).Paged(e.BeginIndex + 1, e.EndIndex + 1, "PortID").WhereExpression("PortName").StartsWith(string.Format("{0}%", e.Filter.ToString()));
IDataReader _rd = _query.ExecuteReader();
_combo.DataSource = _rd;
_combo.ValueField = "PortID";
_combo.ValueType = typeof(Int32);
_combo.TextField = "PortName";
_combo.DataBind();
}
/// <summary>
/// order details summary using order id (pid) or we can use order number (pno) as other tables are bound to
/// order number not id
/// </summary>
protected void bind_summary()
{
Int32 _orderno = wwi_func.vint(wwi_security.DecryptString(get_token("pno"),"publiship")); //999909;
if (_orderno > 0)
{
SubSonic.SqlQuery _q = new SubSonic.SqlQuery();
_q = DB.Select().From("view_order_summary").Where("OrderNumber").IsEqualTo(_orderno);
IDataReader _rd = _q.ExecuteReader();
//drop data into table
while (_rd.Read())
{
//checkboxes
this.dxckEditJobPubliship.Value = _rd["PublishipOrder"];
this.dxckEditIssueDl.Value = _rd["ExpressBL"];
this.dxckEditFumigation.Value = _rd["FumigationCert"];
this.dxckEditGSP.Value = _rd["GSPCert"];
this.dxckEditPacking.Value = _rd["PackingDeclaration"];
this.dxckEditJobHot.Value = _rd["HotJob"];
this.dxckEditPalletised.Value = _rd["Palletise"];
this.dxckEditDocsAppr.Value = _rd["DocsRcdAndApproved"];
this.dxckJobClosed.Value = _rd["JobClosed"];
//icon for publiship job
this.dxlblJobPubliship.ClientVisible = this.dxckEditJobPubliship.Checked;
this.dximgJobPubliship.ClientVisible = this.dxckEditJobPubliship.Checked;
//icon for hot job
this.dxlblJobHot.ClientVisible = this.dxckEditJobHot.Checked;
this.dximgJobHot.ClientVisible = this.dxckEditJobHot.Checked;
//icon for job closed
this.dxlblJobClosed.ClientVisible = this.dxckJobClosed.Checked;
this.dximgJobClosed.ClientVisible = this.dxckJobClosed.Checked;
//labels
this.dxlblOfficeIndicator.Text = _rd["OfficeIndicator"].ToString();
this.dxlblDateCreated.Text = _rd["DateOrderCreated"].ToString();
this.dxlblViewController.Text = _rd["OrderController"].ToString();
this.dxlblViewContact.Text = _rd["ContactName"].ToString();
this.dxlblViewOps.Text = _rd["OpsController"].ToString();
this.dxlblContactEmail.Text = _rd["EMail"].ToString();
this.dxlblViewCompany.Text = _rd["CompanyName"].ToString();
this.dxlblViewPrinter.Text = _rd["PrinterName"].ToString();
this.dxlblViewDocs.Text = _rd["OtherDocsRequired"].ToString();
this.dxlblViewCountry.Text = _rd["OriginCountry"].ToString();
this.dxlblViewOrigin.Text = _rd["PlaceName"].ToString();
this.dxlblViewOriginPort.Text = _rd["OriginPort"].ToString();
this.dxlblViewAgentAtOrigin.Text = _rd["OriginAgent"].ToString();
this.dxlblViewDestPort.Text = _rd["DestinationPort"].ToString();
this.dxlblViewFinal.Text = _rd["FinalDestination"].ToString();
this.dxlblViewCustomerRef.Text = _rd["CustomersRef"].ToString();
this.dxlblViewRemarksAgent.Text = _rd["Remarks"].ToString();
this.dxlblViewRemarksToCustomer.Text = _rd["RemarksToCustomer"].ToString();
this.dxlblViewSellingRate.Text = _rd["Sellingrate"].ToString();
this.dxlblViewSellingAgent.Text = _rd["SellingrateAgent"].ToString();
//260211 some older jobs have an origin controller but no origin agent in those cases don't display the origin controller
if (!string.IsNullOrEmpty(this.dxlblViewAgentAtOrigin.Text.ToString()))
{
this.dxlblViewOriginController.Text = _rd["OriginPortController"].ToString();
}
//dates
this.dxlblViewBookingReceived.Text = !string.IsNullOrEmpty(_rd["BookingReceived"].ToString()) ? Convert.ToDateTime(_rd["BookingReceived"].ToString()).Date.ToString() : "";
this.dxlblViewCargoReady.Text = !string.IsNullOrEmpty(_rd["CargoReady"].ToString()) ? Convert.ToDateTime(_rd["CargoReady"].ToString()).Date.ToString(): "";
//this.dxlblViewWarehouse.Text = !string.IsNullOrEmpty(_rd["WarehouseDate"].ToString()) ? Convert.ToDateTime(_rd["WarehouseDate"].ToString()).Date.ToString(): "";
this.dxlblViewExWorks.Text = !string.IsNullOrEmpty(_rd["ExWorksDate"].ToString()) ? Convert.ToDateTime(_rd["ExWorksDate"].ToString()).Date.ToString() : "";
this.dxlblViewDocsApprovedDate.Text = !string.IsNullOrEmpty(_rd["DocsApprovedDate"].ToString()) ? Convert.ToDateTime(_rd["DocsApprovedDate"].ToString()).Date.ToString(): "";
//addresses
this.dxlblCompanyAddress.Text = _rd["Address1"].ToString() + Environment.NewLine + _rd["Address2"].ToString() +
Environment.NewLine + _rd["Address3"].ToString() + Environment.NewLine + _rd["CountryName"].ToString() +
Environment.NewLine + _rd["TelNo"].ToString();
this.dxlblPrinterAddress.Text = _rd["PrinterAdd1"].ToString() + Environment.NewLine + _rd["PrinterAdd2"].ToString() +
Environment.NewLine + _rd["PrinterAdd3"].ToString() + Environment.NewLine + _rd["PrinterCountry"].ToString() +
Environment.NewLine + _rd["PrinterTel"].ToString();
this.dxlblOriginAgentAddress.Text = _rd["OriginAgentAddress1"].ToString() + Environment.NewLine + _rd["OriginAgentAddress2"].ToString() +
Environment.NewLine + _rd["OriginAgentAddress3"].ToString() + Environment.NewLine + _rd["OriginAgentCountry"].ToString() +
Environment.NewLine + _rd["OriginAgentTel"].ToString();
//order no and order id to hidden fields
this.dxhfOrder.Add("pno", wwi_security.EncryptString( _rd["OrderNumber"].ToString(), "publiship"));
this.dxhfOrder.Add("pid", wwi_security.EncryptString(_rd["OrderID"].ToString(), "publiship"));
this.dxlblOrderNo.Text = _rd["OrderNumber"].ToString();
}
}
else
{
string _ex = "No Order number";
this.dxlblErr.Text = _ex;
this.pnlErr.Visible = true;
}
}
public IDataReader GetOrderCountByEtaWeek(Int32 companyid)
{
System.Globalization.CultureInfo _ci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.DayOfWeek _fd = _ci.DateTimeFormat.FirstDayOfWeek;
DateTime _firstday = DateTime.Today.AddDays(-(DateTime.Today.DayOfWeek - _fd));
DateTime _lastday = _firstday.AddDays(4); //monday to friday + 4
Aggregate _agr1 = Aggregate.GroupBy(OrderTable.EtaColumn);
Aggregate _agr2 = Aggregate.Count(OrderTable.OrderIDColumn, "SumOrderID");
Aggregate[] _agrs = { _agr1, _agr2 };
SubSonic.SqlQuery _qry = new SubSonic.SqlQuery();
if (companyid != -1) //restrict to specified company
{
_qry = new Select(_agrs).From(OrderTable.Schema).
Where(OrderTable.CompanyIDColumn).IsEqualTo(companyid).And(OrderTable.EtaColumn).IsBetweenAnd(_firstday, _lastday);
}
else
{
_qry = new Select(_agrs).From(OrderTable.Schema).
Where(OrderTable.EtaColumn).IsBetweenAnd(_firstday, _lastday);
}
IDataReader _rd = _qry.ExecuteReader();
return _rd;
}
//end selecting
protected void fmvShipment_DataBound(object sender, EventArgs e)
{
try
{
OrderTable _row = (OrderTable)this.fmvShipment.DataItem;
//publiship order
this.dximgJobPubliship.ClientVisible = _row != null ? _row.PublishipOrder : false;
this.dxlblJobPubliship.ClientVisible = _row != null ? _row.PublishipOrder : false;
//job closed
this.dximgJobClosed.ClientVisible = _row != null ? _row.JobClosed : false;
this.dxlblJobClosed.ClientVisible = _row != null ? _row.JobClosed : false;
//hot job
this.dximgJobHot.ClientVisible = _row != null ? _row.HotJob : false;
this.dxlblJobHot.ClientVisible = _row != null ? _row.HotJob : false;
//order number and office in header
this.dxlblOrderNo.Text = _row.OrderNumber.ToString();
string[] _cols = { "OrderNumber", "PublishipOrder", "OfficeIndicator", "DateOrderCreated", "JobClosed", "HotJob", "PortID", "DestinationPortID", "VesselID", "PackageTypeID", "FCLLCL" };
SubSonic.SqlQuery _q = new SubSonic.SqlQuery();
_q = DAL.Logistics.DB.Select(_cols).From(DAL.Logistics.Tables.OrderTable).WhereExpression("OrderNumber").IsEqualTo(_row.OrderNumber);
DataTable _dt = _q.ExecuteDataSet().Tables[0];
if (_dt.Rows.Count > 0)
{
string _vessel = wwi_func.lookup_value("Joined", "VoyageTable", "VoyageID", wwi_func.vint(_dt.Rows[0]["VesselID"].ToString()));
string _package = wwi_func.lookup_value("PackageType", "PackageTypeTable", "PackageTypeID", wwi_func.vint(_dt.Rows[0]["PackageTypeID"].ToString()));
string _fcllcl = wwi_func.lookup_xml_string("//xml//ddl_items.xml", "ddls", "FCLLCL", "value", _dt.Rows[0]["FCLLCL"].ToString(), "name");
this.dxhfOrder.Remove("ptstart"); //origin port
this.dxhfOrder.Remove("ptend"); //destination port
this.dxhfOrder.Remove("vssl"); //vessel name
this.dxhfOrder.Add("ptstart", _dt.Rows[0]["PortID"].ToString());
this.dxhfOrder.Add("ptend", _dt.Rows[0]["DestinationPortID"].ToString());
this.dxhfOrder.Add("vssl", _vessel);
//containers list in all views
bind_containers();
if (this.fmvShipment.CurrentMode != FormViewMode.ReadOnly)
{
//bind dlls here or won't populate on new record
bind_fcl_lcl();
bind_package_type();
}
else
{
//set values for dlls in ReadOnly mode
ASPxLabel _lbl = (ASPxLabel)this.fmvShipment.FindControl("dxlblFieldVessel");
if (_lbl != null) { _lbl.Text = _vessel; }
_lbl = (ASPxLabel)this.fmvShipment.FindControl("dxlblFieldPackageTypeID");
if (_lbl != null) { _lbl.Text = _package; }
_lbl = (ASPxLabel)this.fmvShipment.FindControl("dxlblFieldFCLLCL");
if (_lbl != null) { _lbl.Text = _fcllcl; }
//date formatting
_lbl = (ASPxLabel)this.fmvShipment.FindControl("dxlblFieldETS");
if (_lbl != null) { _lbl.Text = _lbl.Text != "" ? wwi_func.vdatetime(_lbl.Text).ToShortDateString() : ""; }
_lbl = (ASPxLabel)this.fmvShipment.FindControl("dxlblFieldETA");
if (_lbl != null) { _lbl.Text = _lbl.Text != "" ? wwi_func.vdatetime(_lbl.Text).ToShortDateString() : ""; }
_lbl = (ASPxLabel)this.fmvShipment.FindControl("dxlblFieldJobClosureDate");
if (_lbl != null) { _lbl.Text = _lbl.Text != "" ? wwi_func.vdatetime(_lbl.Text).ToShortDateString() : ""; }
}//endif
}//endif
}
catch (Exception ex)
{
string _err = ex.Message.ToString();
this.dxlblErr.Text = _err;
this.dxpnlErr.ClientVisible = true;
}
}
