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