//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(); // } //} }
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); }
/// <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 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; } }
//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 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(); }
//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, 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(); // } //} } }
} //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; } }
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; } }
//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_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); }
/// <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 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(); }
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(); }
//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 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> /// 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(); }
/// <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(); } }
//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(); } }
//end remove pod /// <summary> /// detail grid for shipment size /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void shipment_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().shipment_sizes.Where(c => c.quote_id == _quoteid); //_detail.DataSource = _nquery; SubSonic.SqlQuery _query = DAL.Pricer.DB.Select().From(DAL.Pricer.Tables.ShipmentSize).WhereExpression("quote_id").IsEqualTo(_quoteid); IDataReader _rd = _query.ExecuteReader(); DataTable _dt = new DataTable(); _dt.Load(_rd); _detail.DataSource = _dt; _rd.Close(); } } //end shipment size data select
//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(); }
//end get quote details /// <summary> /// load shipment details on callback /// set flag so we don't tr y and reload again /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void dxcbkshipv1_Callback(object sender, DevExpress.Web.ASPxClasses.CallbackEventArgsBase e) { Int32 _quoteid = this.dxhfpriceview.Contains("qr") ? (Int32)this.dxhfpriceview.Get("qr") : 0; if (_quoteid > 0) { //var _nquery = new linq_pricerDataContext().shipment_sizes.Where(c => c.quote_id == _quoteid); //_detail.DataSource = _nquery; SubSonic.SqlQuery _query = DAL.Pricer.DB.Select().From(DAL.Pricer.Tables.ShipmentSize).WhereExpression("quote_id").IsEqualTo(_quoteid); IDataReader _rd = _query.ExecuteReader(); DataTable _dt = new DataTable(); _dt.Load(_rd); this.rptshipment1.DataSource = _dt; this.rptshipment1.DataBind(); _rd.Close(); } else { //set_error_msg("No quote reference has been found"); //this.dxpagepriceview.ActiveTabIndex = 4; } }
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(); //this.sdsCompany.ConnectionString = ConfigurationManager.ConnectionStrings["PublishipSQLConnectionString"].ToString(); //this.sdsCompany.SelectCommand = @"SELECT [CompanyID], [CompanyName], [Customer] FROM [NNameAndAddressBook] WHERE (CompanyID = @ID)"; //this.sdsCompany.SelectParameters.Clear(); //this.sdsCompany.SelectParameters.Add("ID", TypeCode.Int32, e.Value.ToString()); //_combo.DataSource = this.sdsCompany; //_combo.DataBind(); }
//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 dxcboVesselID_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()); } //use datareaders - much faster than loading into collections string[] _cols = { "VoyageID", "Joined", "ETS", "ETA", "DestinationPortID", "OriginPortID" }; //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("Page2VesselView").WhereExpression("VoyageID").IsEqualTo(_id); IDataReader _rd = _query.ExecuteReader(); _combo.DataSource = _rd; _combo.ValueField = "VoyageID"; _combo.TextField = "Joined"; _combo.DataBindItems(); }
//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(); } }
//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> /// 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(); } }
/// <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(); } } }
/// <summary> /// encrypto1login for utf8/md5 encryption /// </summary> /// <param name="txtUser"></param> /// <param name="txtPassword"></param> /// <returns></returns> public static UserClass crypto1Login(string txtUser, string txtPassword) { txtUser = wwi_security.DecryptString(txtUser, "publiship"); 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" }; //string[] _cols = { "ContactTable.ContactID, ContactTable.ContactName, 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"]); 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++; } 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++; } } } 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); }
/// <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; } }
//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; }