protected void DataInit() { //初使化条件 PageIndex = Utils.GetInt(Utils.GetQueryStringValue("page"), 1); //省份 province = Utils.GetIntNull(Utils.GetQueryStringValue("province")); //城市 city = Utils.GetIntNull(Utils.GetQueryStringValue("city")); //单位名称 TxtUnitsName = Utils.GetQueryStringValue("UnitsName"); //菜系 CuisineValue = Utils.GetQueryStringValue("CuisineValue"); //查询条件初始化 判断 EyouSoft.Model.SupplierStructure.SupplierRestaurantSearchInfo RestaurantSearch = new EyouSoft.Model.SupplierStructure.SupplierRestaurantSearchInfo(); RestaurantSearch.Cuisine = CuisineValue; RestaurantSearch.Name = TxtUnitsName; if (province == null || province <= 0) { RestaurantSearch.ProvinceId = null; } else { RestaurantSearch.ProvinceId = Utils.GetIntNull(province.ToString()); } if (city == null || city <= 0) { RestaurantSearch.CityId = null; } else { RestaurantSearch.CityId = Utils.GetIntNull(city.ToString()); } IList <EyouSoft.Model.SupplierStructure.SupplierRestaurantInfo> list = null; list = Restaurantbll.GetRestaurants(SiteUserInfo.CompanyID, PageSize, PageIndex, ref RecordCount, RestaurantSearch); if (list.Count > 0 && list != null) { len = list.Count; //列表数据绑定 this.replist.DataSource = list; this.replist.DataBind(); //设置分页 BindPage(); } else { this.ExporPageInfoSelect1.Visible = false; } //初始化查询省份和城市 this.ucProvince1.ProvinceId = Utils.GetInt(province.ToString()); this.ucCity1.CityId = Utils.GetInt(city.ToString()); this.ucCity1.ProvinceId = Utils.GetInt(province.ToString()); list = null; }
/// <summary> /// 获取餐馆供应商信息集合 /// </summary> /// <param name="companyId">公司(专线)编号</param> /// <param name="pageSize">每页记录数</param> /// <param name="pageIndex">页索引</param> /// <param name="recordCount">总记录数</param> /// <param name="info">查询信息</param> /// <returns></returns> public IList <EyouSoft.Model.SupplierStructure.SupplierRestaurantInfo> GetRestaurants(int companyId, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.SupplierStructure.SupplierRestaurantSearchInfo info) { IList <EyouSoft.Model.SupplierStructure.SupplierRestaurantInfo> items = new List <EyouSoft.Model.SupplierStructure.SupplierRestaurantInfo>(); StringBuilder cmdQuery = new StringBuilder(); string tableName = "tbl_CompanySupplier"; string primaryKey = "Id"; string orderByString = "Id DESC"; StringBuilder fields = new StringBuilder(); #region fields fields.Append("Id,ProvinceId,ProvinceName,CityId,CityName,UnitName,TradeNum,(SELECT Cuisine FROM tbl_SupplierRestaurant AS A WHERE tbl_CompanySupplier.Id=A.SupplierId) AS Cuisine"); fields.Append(",(SELECT TOP 1 ContactName,ContactTel,ContactFax,ContactMobile FROM tbl_SupplierContact AS A WHERE A.SupplierId = tbl_CompanySupplier.[Id] FOR XML RAW,ROOT('ROOT')) AS Contacters "); #endregion #region 拼接查询条件 cmdQuery.AppendFormat(" CompanyId={0} AND IsDelete='0' AND SupplierType={1} ", companyId, (int)EyouSoft.Model.EnumType.CompanyStructure.SupplierType.餐馆); info = info ?? new EyouSoft.Model.SupplierStructure.SupplierRestaurantSearchInfo(); if (info.CityId.HasValue) { cmdQuery.AppendFormat(" AND CityId={0} ", info.CityId.Value); } if (!string.IsNullOrEmpty(info.Name)) { cmdQuery.AppendFormat(" AND UnitName LIKE '%{0}%' ", info.Name); } if (info.ProvinceId.HasValue) { cmdQuery.AppendFormat(" AND ProvinceId={0} ", info.ProvinceId.Value); } if (!string.IsNullOrEmpty(info.Cuisine)) { cmdQuery.AppendFormat(" AND EXISTS(SELECT 1 FROM tbl_SupplierRestaurant AS A WHERE A.SupplierId=tbl_CompanySupplier.Id AND A.Cuisine LIKE '%{0}%') ", info.Cuisine); } #endregion using (IDataReader rdr = DbHelper.ExecuteReader(this._db, pageSize, pageIndex, ref recordCount, tableName, primaryKey, fields.ToString(), cmdQuery.ToString(), orderByString)) { while (rdr.Read()) { items.Add(new EyouSoft.Model.SupplierStructure.SupplierRestaurantInfo() { Id = rdr.GetInt32(rdr.GetOrdinal("Id")), ProvinceId = rdr.GetInt32(rdr.GetOrdinal("ProvinceId")), ProvinceName = rdr["ProvinceName"].ToString(), CityId = rdr.GetInt32(rdr.GetOrdinal("CityId")), CityName = rdr["CityName"].ToString(), UnitName = rdr["UnitName"].ToString(), TradeNum = rdr.GetInt32(rdr.GetOrdinal("TradeNum")), SupplierContact = this.GetSupplierContactersByXml(rdr["Contacters"].ToString()), Cuisine = rdr["Cuisine"].ToString() }); } } return(items); }
/// <summary> /// 获取餐馆供应商信息集合 /// </summary> /// <param name="companyId">公司(专线)编号</param> /// <param name="pageSize">每页记录数</param> /// <param name="pageIndex">页索引</param> /// <param name="recordCount">总记录数</param> /// <param name="info">查询信息</param> /// <returns></returns> public IList <EyouSoft.Model.SupplierStructure.SupplierRestaurantInfo> GetRestaurants(int companyId, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.SupplierStructure.SupplierRestaurantSearchInfo info) { if (companyId < 1) { return(null); } return(dal.GetRestaurants(companyId, pageSize, pageIndex, ref recordCount, info)); }
/// <summary> /// 导出Excel /// </summary> public void CreateExcel(string FileName) { //省份 province = Utils.GetInt(Utils.GetQueryStringValue("province")); //城市 city = Utils.GetInt(Utils.GetQueryStringValue("city")); //单位名称 TxtUnitsName = Utils.GetQueryStringValue("UnitsName"); //菜系 CuisineValue = Utils.GetQueryStringValue("CuisineValue"); //列表数据绑定 Response.Clear(); Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls"); Response.ContentEncoding = System.Text.Encoding.Default; Response.ContentType = "application/ms-excel"; EyouSoft.Model.SupplierStructure.SupplierRestaurantSearchInfo RestaurantSearch = new EyouSoft.Model.SupplierStructure.SupplierRestaurantSearchInfo(); RestaurantSearch.Cuisine = CuisineValue; RestaurantSearch.Name = TxtUnitsName; if (province == null || province <= 0) { RestaurantSearch.ProvinceId = null; } else { RestaurantSearch.ProvinceId = Utils.GetIntNull(province.ToString()); } if (city == null || city <= 0) { RestaurantSearch.CityId = null; } else { RestaurantSearch.CityId = Utils.GetIntNull(city.ToString()); } //总记录数 int count = Utils.GetInt(Utils.GetFormValue("hidRecordCount")); if (count == 0) { count = 100; } IList <EyouSoft.Model.SupplierStructure.SupplierRestaurantInfo> list = null; list = Restaurantbll.GetRestaurants(SiteUserInfo.CompanyID, count, 1, ref RecordCount, RestaurantSearch); //取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符 System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\n", "所在地", "单位名称", "菜系", "联系人", "电话", "传真", "交易情况"); foreach (EyouSoft.Model.SupplierStructure.SupplierRestaurantInfo sr in list) { sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\n", sr.ProvinceName + "" + sr.CityName, sr.UnitName, sr.Cuisine, sr.SupplierContact == null ? "" : sr.SupplierContact[0].ContactName, sr.SupplierContact == null ? "" : sr.SupplierContact[0].ContactTel, sr.SupplierContact == null ? "" : sr.SupplierContact[0].ContactFax, sr.TradeNum); } Response.Write(sb.ToString()); Response.End(); list = null; }