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