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); }
public Int32 get_total_orders(Int32 companyId) { Int32 _count = 0; ViewOrderCollection _order = new ViewOrderCollection(); Where _wh = new Where(); _wh.ColumnName = "CompanyId"; _wh.ParameterValue = companyId; _wh.DbType = DbType.Int32; _order.Where(_wh); _count = _order.Count; return _count; }
/// <summary> /// get data table for main grid DEPRECATED using Linq instead /// </summary> /// <param name="pageIndex">startpage for pagination</param> /// <param name="pageSize">number of records to return for pagination</param> /// <returns>datatable</returns> public DataTable get_datatable() { //using a collection as it's the only way to directly apply a query string! //query to grid // DataTable _dt = new DataTable(); try { String _companyid = ""; //check to see if we have a company id //user MUST be logged in for advanced searches ConnectionStringSettings _cts = ConfigurationManager.ConnectionStrings["WWI_intra"]; SqlConnection _cn = new SqlConnection(); _cn.ConnectionString = _cts.ConnectionString; _cn.Open(); //need to switch this off when we are testing! String _qry = ""; //// //Session.Remove("user"); //// if (Page.Session["user"] != null) { _companyid = ((UserClass)Page.Session["user"]).CompanyId.ToString(); _qry = " WHERE [CompanyID] = " + _companyid; } String _filter = ""; if (Session["filter"] != null) { _filter = Session["filter"].ToString(); if (_companyid != string.Empty) { _qry += " AND " + _filter; } else { _qry = " WHERE " + _filter; } } //Session["filter"] = "OrderNumber = -1"; //default so we do not load any records in start up THIS MESSES UP EXPORT! String _select = "SELECT [OrderNumber],[HouseBLNUmber],[CustomersRef],[DateOrderCreated],[originport],[destport],[VesselName],[ETS],[ETA],[destplace],[originplace],[mintitle],[JobClosed],[ContainerNumber], [CompanyID] FROM [view_order]" + _qry + ";"; SqlCommand _cmd = new SqlCommand(_select, _cn); ViewOrderCollection _order = new ViewOrderCollection(); _order.LoadAndCloseReader(_cmd.ExecuteReader()); _dt = (DataTable)_order.ToDataTable(); _cmd.Dispose(); _cn.Close(); _cn.Dispose(); } catch (Exception _err) { Response.Write(_err.Message.ToString()); } return _dt; }
// public DataTable get_datatable_subsonic() { //using a collection as it's the only way to directly apply a query string! //query to grid //Int32 _page = this.gridOrder.PageIndex; //Int32 _pagesize = this.gridOrder.SettingsPager.PageSize; // String _companyid = ""; String _filter = ""; //check to see if we have a company id //user MUST be logged in for advanced searches Query _qry = new Query(Views.ViewOrder); //need to switch this off when we are testing! Page.Session.Remove("user"); if (Page.Session["user"] != null) { _companyid = ((UserClass)Page.Session["user"]).CompanyId.ToString(); _qry.WHERE("CompanyID = " + _companyid); //_query = "WHERE CompanyId = " + _companyid; } if (Session["filter"] != null) { _filter = Session["filter"].ToString(); if (_companyid != string.Empty) { _qry.AND(_filter); } else { _qry.WHERE(_filter); } //if (_companyid != string.Empty) { _query+= "AND " + _filter; } else {_query = " WHERE " + _filter ; } } //InlineQuery _qry = new InlineQuery(); //_qry.PageIndex = _page+1; //gridview page is 0 based, but subsonic is 1 based //_qry.PageSize = _pagesize; ViewOrderCollection _order = new ViewOrderCollection(); _order.LoadAndCloseReader(_qry.ExecuteReader()); DataTable _dt = (DataTable)_order.ToDataTable(); return _dt; }
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; }