public ActionResult BaseSearch()
{
SearchCompanyViewModel model = new SearchCompanyViewModel();
ViewBag.Data_CustomerCateID = Utilities.GetSelectListData(CustomerCateService.GetALL(),
x => x.ID,
x => x.CateName,
true, true);
return PartialView(model);
}
public ActionResult ExportAll(SearchCompanyViewModel model, int page = 1)
{
var query = GetSearch(model).ToList();
//Create new Excel workbook
var workbook = new HSSFWorkbook();
//Create new Excel sheet
var sheet = workbook.CreateSheet();
//(Optional) set the width of the columns
sheet.SetColumnWidth(0, 10 * 256);
sheet.SetColumnWidth(1, 30 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 20 * 256);
sheet.SetColumnWidth(6, 30 * 256);
sheet.SetColumnWidth(7, 30 * 256);
sheet.SetColumnWidth(8, 30 * 256);
sheet.SetColumnWidth(9, 30 * 256);
sheet.SetColumnWidth(10, 30 * 256);
//Create a header row
var headerRow = sheet.CreateRow(0);
//Set the column names in the header row
headerRow.CreateCell(0).SetCellValue("客户ID");
headerRow.CreateCell(1).SetCellValue("公司名称");
headerRow.CreateCell(2).SetCellValue("品牌名称");
headerRow.CreateCell(3).SetCellValue("城市");
headerRow.CreateCell(4).SetCellValue("行业");
headerRow.CreateCell(5).SetCellValue("当前关系程度");
headerRow.CreateCell(6).SetCellValue("传真");
headerRow.CreateCell(7).SetCellValue("电话");
headerRow.CreateCell(8).SetCellValue("地址");
headerRow.CreateCell(9).SetCellValue("录入者");
headerRow.CreateCell(10).SetCellValue("录入时间");
//(Optional) freeze the header row so it is not scrolled
sheet.CreateFreezePane(0, 1, 0, 1);
int rowNumber = 1;
//var shares = CustomerShareService.GetALL()
// .Include(x => x.CustomerCompany)
// .Where(x => x.MemberID == CookieHelper.MemberID)
// .Select(x => x.CustomerCompany).ToList();
//model.AddRange(shares);
//Populate the sheet with values from the grid data
foreach (CustomerCompany cc in query)
{
//Create a new row
var row = sheet.CreateRow(rowNumber++);
//Set values for the cells
row.CreateCell(0).SetCellValue(cc.ID);
row.CreateCell(1).SetCellValue(cc.Name);
row.CreateCell(2).SetCellValue(cc.BrandName);
var cityIds = Utilities.GetIdList(cc.CityValue);
var citys = CityCateService.GetALL().Where(x => cityIds.Contains(x.ID)).Select(x => x.CateName).ToList();
var cityName = string.Join("-", citys);
var industryIds = Utilities.GetIdList(cc.IndustryValue);
var industrys = IndustryCateService.GetALL().Where(x => industryIds.Contains(x.ID)).Select(x => x.CateName).ToList();
var industryName = string.Join("-", industrys);
row.CreateCell(3).SetCellValue(cityName);
row.CreateCell(4).SetCellValue(industryName);
row.CreateCell(5).SetCellValue(cc.RelationCate.CateName);
row.CreateCell(6).SetCellValue(cc.Fax);
row.CreateCell(7).SetCellValue(cc.Phone);
row.CreateCell(8).SetCellValue(cc.Address);
row.CreateCell(9).SetCellValue(cc.AddMember.NickName);
row.CreateCell(10).SetCellValue(cc.AddTime.ToString("yyyy-MM-dd"));
var customers = cc.Customer;
if (customers.Any())
{
var childtitlerow = sheet.CreateRow(rowNumber++);
childtitlerow.CreateCell(1).SetCellValue("客户人员信息");
var childheadrow = sheet.CreateRow(rowNumber++);
childheadrow.CreateCell(1).SetCellValue("类型");
childheadrow.CreateCell(2).SetCellValue("姓名");
childheadrow.CreateCell(3).SetCellValue("职位");
childheadrow.CreateCell(4).SetCellValue("录入者");
childheadrow.CreateCell(5).SetCellValue("生日类型");
childheadrow.CreateCell(6).SetCellValue("生日");
childheadrow.CreateCell(7).SetCellValue("电话");
childheadrow.CreateCell(8).SetCellValue("手机1");
childheadrow.CreateCell(9).SetCellValue("手机2");
childheadrow.CreateCell(10).SetCellValue("地址");
childheadrow.CreateCell(11).SetCellValue("QQ");
childheadrow.CreateCell(12).SetCellValue("爱好");
childheadrow.CreateCell(13).SetCellValue("邮箱");
}
foreach (var customer in customers)
{
var childrow = sheet.CreateRow(rowNumber++);
childrow.CreateCell(1).SetCellValue(customer.JobCate.CateName);
childrow.CreateCell(2).SetCellValue(customer.Name);
childrow.CreateCell(3).SetCellValue(customer.Jobs);
childrow.CreateCell(4).SetCellValue(customer.AddMember.NickName);
childrow.CreateCell(5).SetCellValue(customer.IsLeap ? "农历" : "阳历");
childrow.CreateCell(6).SetCellValue(customer.IsLeap
? (Utilities.ConvertToChineseYearStyle(customer.BirthDay.Year) + customer.BirthDay1)
: customer.BirthDay.ToString("yyyy-MM-dd"));
childrow.CreateCell(7).SetCellValue(customer.Phone);
childrow.CreateCell(8).SetCellValue(customer.Mobile);
childrow.CreateCell(9).SetCellValue(customer.Mobile1);
childrow.CreateCell(10).SetCellValue(customer.Address);
childrow.CreateCell(11).SetCellValue(customer.QQ);
childrow.CreateCell(12).SetCellValue(customer.Favorite);
childrow.CreateCell(13).SetCellValue(customer.Email);
}
sheet.CreateRow(rowNumber++);
}
//Write the workbook to a memory stream
MemoryStream output = new MemoryStream();
workbook.Write(output);
//Return the result to the end user
return File(output.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"客户信息.xls"); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
private IQueryable<CustomerCompany> GetSearch(SearchCompanyViewModel model)
{
var query = CustomerCompanyService.GetALL()
.Include(x => x.AddMember)
.Include(x => x.CustomerCate)
.Include(x => x.Customer);
if (model.ID != 0)
{
query = query.Where(x => x.ID == model.ID);
}
if (!string.IsNullOrEmpty(model.Name))
{
query = query.Where(x => x.Name.Contains(model.Name));
}
if (!string.IsNullOrEmpty(model.BrandName))
{
query = query.Where(x => x.BrandName.Contains(model.BrandName));
}
if (!string.IsNullOrEmpty(model.Customer))
{
query = query.Where(x => x.Customer.Any(c => c.Name.Contains(model.Customer)));
}
if (!string.IsNullOrEmpty(model.UserName))
{
query = query.Where(x => x.AddMember.NickName.Contains(model.UserName));
}
if (model.CustomerCateID != 0)
{
query = query.Where(x => x.CustomerCateID == model.CustomerCateID);
}
if (!string.IsNullOrEmpty(model.Mobile))
{
query = query.Where(x =>
x.Customer.Any(c =>
c.Mobile.Contains(model.Mobile)
|| c.Mobile1.Contains(model.Mobile)));
}
if (!string.IsNullOrEmpty(model.Phone))
{
query = query.Where(x =>
x.Customer.Any(c =>
c.Phone.Contains(model.Phone)) || x.Phone.Contains(model.Phone));
}
if (!string.IsNullOrEmpty(model.QQ))
{
query = query.Where(x => x.Customer.Any(c => c.QQ.Contains(model.QQ)));
}
if (!string.IsNullOrEmpty(model.Address))
{
query = query.Where(x =>
x.Customer.Any(c =>
c.Address.Contains(model.Address))
|| x.Address.Contains(model.Address));
}
if (!string.IsNullOrEmpty(model.Fax))
{
query = query.Where(x => x.Fax.Contains(model.Fax));
}
query = query.Where(x => x.AddTime < model.EndTime
&& x.AddTime > model.StartTime
&& x.Status > (int)CustomerCompanyStatus.Delete).OrderByDescending(x => x.AddTime);
return query;
}
public ActionResult Search(SearchCompanyViewModel model, int page = 1)
{
const int pageSize = 20;
var query = GetSearch(model);
var totalCount = query.Count();
var data = query.Skip((page - 1) * pageSize).Take(pageSize).ToList();
ViewBag.PageInfo = new PagingInfo()
{
TotalItems = totalCount,
CurrentPage = page,
ItemsPerPage = pageSize
};
return PartialView(data);
}
private IQueryable<CustomerCompany> GetSearch(SearchCompanyViewModel model)
{
var query = CustomerCompanyService.GetALL()
.Include(x => x.AddMember)
.Include(x => x.CustomerCate)
.Include(x => x.Customer);
if (model.ID != 0)
{
query = query.Where(x => x.ID == model.ID);
}
if (!string.IsNullOrEmpty(model.Name))
{
query = query.Where(x => x.Name.Contains(model.Name));
}
if (!string.IsNullOrEmpty(model.BrandName))
{
query = query.Where(x => x.BrandName.Contains(model.BrandName));
}
if (!string.IsNullOrEmpty(model.Customer))
{
query = query.Where(x => x.Customer.Any(c => c.Name.Contains(model.Customer)));
}
if (!string.IsNullOrEmpty(model.UserName))
{
query = query.Where(x => x.AddMember.NickName.Contains(model.UserName));
}
if (model.CustomerCateID != 0)
{
query = query.Where(x => x.CustomerCateID == model.CustomerCateID);
}
if (!string.IsNullOrEmpty(model.Mobile))
{
query = query.Where(x =>
x.Customer.Any(c =>
c.Mobile.Contains(model.Mobile)
|| c.Mobile1.Contains(model.Mobile)));
}
if (!string.IsNullOrEmpty(model.Phone))
{
query = query.Where(x =>
x.Customer.Any(c =>
c.Phone.Contains(model.Phone)) || x.Phone.Contains(model.Phone));
}
if (!string.IsNullOrEmpty(model.QQ))
{
query = query.Where(x => x.Customer.Any(c => c.QQ.Contains(model.QQ)));
}
if (!string.IsNullOrEmpty(model.Address))
{
query = query.Where(x =>
x.Customer.Any(c =>
c.Address.Contains(model.Address))
|| x.Address.Contains(model.Address));
}
if (!string.IsNullOrEmpty(model.Fax))
{
query = query.Where(x => x.Fax.Contains(model.Fax));
}
if (CookieHelper.CheckPermission("boss"))
{
}
else if (CookieHelper.CheckPermission("manager"))
{
var memberIds = MemberService.GetMemberIDs(CookieHelper.GetDepartmentID());
query = query.Where(x => memberIds.Contains(x.AddUser));
}
else
{
query = query.Where(x => x.AddUser == CookieHelper.MemberID);
}
if (model.IsInTime)
{
query = query.Where(x => x.AddTime < model.EndTime
&& x.AddTime > model.StartTime);
}
query = query.Where(x => x.Status > (int)CustomerCompanyStatus.Delete
&& x.CustomerCateID != 6
).OrderByDescending(x => x.AddTime);
return query;
}
public ActionResult Index()
{
SearchCompanyViewModel model = new SearchCompanyViewModel();
ViewBag.Data_CustomerCateID = Utilities.GetSelectListData(
CustomerCateService.GetALL()
.Where(x => x.ID != 6),
x => x.ID,
x => x.CateName,
true, true);
return View(model);
}
