protected void BindCarTeamsBySearh() { //生成车队类 EyouSoft.Model.SupplierStructure.SupplierCarTeam carTeam = new EyouSoft.Model.SupplierStructure.SupplierCarTeam(); //生成查询条件方法 EyouSoft.Model.SupplierStructure.SupplierCarTeamSearchInfo searInfo = new EyouSoft.Model.SupplierStructure.SupplierCarTeamSearchInfo(); #region 控件赋值 string carType = Server.UrlDecode(Utils.GetQueryStringValue("cartype")); string carName = Server.UrlDecode(Utils.GetQueryStringValue("carname")); int cityId = Utils.GetInt(Utils.GetQueryStringValue("cityId")); int provinceId = Utils.GetInt(Utils.GetQueryStringValue("proid")); this.ucProvince1.ProvinceId = provinceId; this.ucCity1.ProvinceId = provinceId; this.ucCity1.CityId = cityId; this.car_type.Value = carType; this.carTeam_name.Value = carName; #endregion searInfo.CarType = carType; searInfo.CityId = cityId; searInfo.Name = carName; searInfo.ProvinceId = provinceId; IList <EyouSoft.Model.SupplierStructure.SupplierCarTeam> carTeams = carTeamManager.GetCarTeams(CurrentUserCompanyID, pageSize, pageIndex, ref recordCount, searInfo); this.rptList.DataSource = carTeams; this.rptList.DataBind(); BindExportPage(); }
/// <summary> /// 导出Excel /// </summary> public void CreateExcel(string FileName) { int province = this.ucProvince1.ProvinceId; int city = this.ucCity1.CityId; string carTeam_Name = Utils.GetQueryStringValue("carname"); string carType = Utils.GetQueryStringValue("cartype"); IList <EyouSoft.Model.SupplierStructure.SupplierCarTeam> list = null; EyouSoft.Model.SupplierStructure.SupplierCarTeamSearchInfo searchInfo = new EyouSoft.Model.SupplierStructure.SupplierCarTeamSearchInfo(); searchInfo.CarType = carType; searchInfo.CityId = city; searchInfo.Name = carTeam_Name; searchInfo.ProvinceId = province; list = carTeamManager.GetCarTeams(SiteUserInfo.CompanyID, 1, 1, ref recordCount, searchInfo); if (recordCount > 0) { list = carTeamManager.GetCarTeams(SiteUserInfo.CompanyID, recordCount, 1, ref recordCount, searchInfo); } Response.Clear(); Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls"); Response.ContentEncoding = System.Text.Encoding.Default; Response.ContentType = "application/ms-excel"; //取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符 StringBuilder sb = new StringBuilder(); sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\n", "所在地", "单位名称", "地址", "联系人姓名", "手机", "传真", "交易次数"); foreach (EyouSoft.Model.SupplierStructure.SupplierCarTeam ct in list) { sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\n", ct.ProvinceName + " " + ct.CityName, ct.UnitName, ct.UnitAddress, ct.SupplierContact.Count == 0 ? "" : ct.SupplierContact[0].ContactName, ct.SupplierContact.Count == 0 ? "" : ct.SupplierContact[0].ContactMobile, ct.SupplierContact.Count == 0 ? "" : ct.SupplierContact[0].ContactFax, ct.TradeNum); } Response.Write(sb.ToString()); Response.End(); }
/// <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.SupplierCarTeam> GetCarTeams(int companyId, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.SupplierStructure.SupplierCarTeamSearchInfo info) { if (companyId < 1) { return(null); } return(dal.GetCarTeams(companyId, pageSize, pageIndex, ref recordCount, info)); }
/// <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.SupplierCarTeam> GetCarTeams(int companyId, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.SupplierStructure.SupplierCarTeamSearchInfo info) { IList <EyouSoft.Model.SupplierStructure.SupplierCarTeam> items = new List <EyouSoft.Model.SupplierStructure.SupplierCarTeam>(); 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"); fields.Append(",(SELECT TOP 1 ContactName,ContactTel,ContactFax FROM tbl_SupplierContact AS A WHERE A.SupplierId = tbl_CompanySupplier.[Id] FOR XML RAW,ROOT('ROOT')) AS Contacters "); fields.Append(",(SELECT TOP 1 CarType FROM tbl_SupplierCarInfo AS A WHERE A.PrivaderId = tbl_CompanySupplier.[Id] FOR XML RAW,ROOT('ROOT')) AS Cars "); #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.SupplierCarTeamSearchInfo(); if (info.CityId.HasValue) { if (info.CityId.Value != 0) { 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) { if (info.ProvinceId.Value != 0) { cmdQuery.AppendFormat(" AND ProvinceId={0} ", info.ProvinceId.Value); } } if (!string.IsNullOrEmpty(info.CarType)) { cmdQuery.AppendFormat(" AND EXISTS(SELECT 1 FROM tbl_SupplierCarInfo AS A WHERE A.PrivaderId=tbl_CompanySupplier.Id AND CarType='{0}') ", info.CarType); } #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.SupplierCarTeam() { Id = rdr.IsDBNull(rdr.GetOrdinal("Id")) ? 0 : rdr.GetInt32(rdr.GetOrdinal("Id")), ProvinceId = rdr.IsDBNull(rdr.GetOrdinal("ProvinceId")) ? 0 : rdr.GetInt32(rdr.GetOrdinal("ProvinceId")), ProvinceName = rdr["ProvinceName"].ToString(), CityId = rdr.IsDBNull(rdr.GetOrdinal("CityId")) ? 0 : rdr.GetInt32(rdr.GetOrdinal("CityId")), CityName = rdr["CityName"].ToString(), UnitName = rdr["UnitName"].ToString(), TradeNum = rdr.IsDBNull(rdr.GetOrdinal("TradeNum")) ? 0 : rdr.GetInt32(rdr.GetOrdinal("TradeNum")), SupplierContact = this.GetSupplierContactersByXml(rdr["Contacters"].ToString()), CarsInfo = this.GetSupplierCarsByXml(rdr["Cars"].ToString()) }); } } return(items); }