public ActionResult ExportExcelToDownLoad(string excelName = "测试") { ExtentionComplainBag cbag = new ExtentionComplainBag(); ComplainParam pra = new ComplainParam(); pra.seltime = DateTime.Now; pra.selline = ""; pra.selkeywords = ""; cbag = new Admin_ExcelResourceForComplainLogic().GetAnalysisByComplain(this.pageSize, this.page, out this.totalCount, pra); string filename = excelName + ".xls"; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); Response.Clear(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); InitializeWorkbook(hssfworkbook); GenerateData(hssfworkbook, cbag.list); GetExcelStream(hssfworkbook).WriteTo(Response.OutputStream); Response.End(); return new EmptyResult(); }
/// <summary> /// 获取制定日期的投诉统计(菜单:分省投诉万投比) /// </summary> /// <param name="BeginTime"></param> /// <param name="EndTime"></param> public ExtentionComplainBag GetAnalysisByComplain(int PageSize, int PageIndex, out int totalCount, ComplainParam pra) { //统计非包月用户数 //取出所有数据 var ComplainTable = from p in _db.Admin_ExcelResourceForComplain select p; //取出所有CP数据 var CPcompanyTable = from p in _db.Admin_CPcompany select p; //取出所有地区 var allArea = from p in ComplainTable group p by p.Province into g select new { g.Key }; //取出投诉表与cp表关联的所有CP var allCp = from p in ComplainTable join s in CPcompanyTable on p.CPid equals s.ID group p by new { p.CPid, s.CPname, p.SourceLevel } into t select new { t.Key.CPid, t.Key.CPname, t.Key.SourceLevel }; //非包月付费用户数累计表数据(按cp和省份分组) var notBaoyue = from p in _db.Admin_NotBaoyuePayBillPlayCount group p by new { p.Province, p.CpID } into g select new { Province = g.Key.Province, CpID = g.Key.CpID, AddNum = g.Sum(p => p.AddNum),//新增 LeaveNum = g.Sum(p => p.LeaveNum),//遗留 NumTotal = g.Sum(p => p.NumTotal)//单省分非包月付费用户数 }; //分析结果实体,用户装载最后的分析结果 List<ComplainAnalysisList> cpList = new List<ComplainAnalysisList>(); //循环所有CP分类 foreach (var item in allCp) { //筛选出一个cp var cpComplain = ComplainTable.Where(s => s.CPid == item.CPid); //按地区分组统计 //1.新增 var AddPop = from p in cpComplain join s in allCp on p.CPid equals s.CPid where p.OrderTime > pra.seltime group p by p.Province into g select new { g.Key, AddCount = g.Count() }; //2.遗留 var leavePop = from p in cpComplain join s in allCp on p.CPid equals s.CPid where p.OrderTime <= pra.seltime group p by p.Province into g select new { g.Key, AddCount = g.Count() }; //新增日期的昨天记录 DateTime praseltime = pra.seltime.AddDays(-1); var yesterdayAddPop = from p in cpComplain join s in allCp on p.CPid equals s.CPid where p.OrderTime > praseltime group p by p.Province into g select new { g.Key, AddCount = g.Count() }; //按按cp分类的实体 ComplainAnalysisList cpModel = new ComplainAnalysisList(); //3.添加结果到分析结果实体 foreach (var area in allArea) { ComplainAnalysisModel caModel = new ComplainAnalysisModel(); cpModel.caList = new List<ComplainAnalysisModel>(); cpModel.CpName = item.CPname; cpModel.SourceLevel = item.SourceLevel; caModel.privnce = area.Key; caModel.notBaoyuePayUserNum = notBaoyue.Where(s => s.CpID == item.CPid && s.Province == area.Key).FirstOrDefault() != null ? notBaoyue.Where(s => s.CpID == item.CPid && s.Province == area.Key).FirstOrDefault().NumTotal : 0; caModel.yesterdayAddNum = yesterdayAddPop.Where(s => s.Key == area.Key).FirstOrDefault() != null ? yesterdayAddPop.Where(s => s.Key == area.Key).FirstOrDefault().AddCount : 0; caModel.AddNum = AddPop.Where(s => s.Key == area.Key).FirstOrDefault() != null ? AddPop.Where(s => s.Key == area.Key).FirstOrDefault().AddCount : 0; caModel.LeaveNum = leavePop.Where(s => s.Key == area.Key).FirstOrDefault() != null ? leavePop.Where(s => s.Key == area.Key).FirstOrDefault().AddCount : 0; if (caModel.notBaoyuePayUserNum == 0) { caModel.proportion = 0; } else { caModel.proportion = caModel.AddNum / caModel.notBaoyuePayUserNum * 10000; } cpModel.caList.Add(caModel); } //添加到列表 cpList.Add(cpModel); } var query = cpList.OrderBy(s => s.SourceLevel).ToList(); #region 查询条件过滤 //关键词 if (!string.IsNullOrEmpty(pra.selkeywords))//关键词非空 { query = query.Where(s => s.CpName.Contains(pra.selkeywords)).ToList(); } //线级 if (pra.selline != "0" && pra.selline != "")//线级选择非空 { string level = pra.selline == "1" ? "一线" : "二线"; query = query.Where(s => s.SourceLevel.Contains(level)).ToList(); } #endregion totalCount = query.Count(); var cbag = new ExtentionComplainBag(); if (PageIndex < 0 || PageSize < 0) { return null; } if (PageIndex == 1 && PageSize > totalCount) { cbag.list = query.ToList(); } else if (PageIndex == 1 && PageSize > 0) { cbag.list = query.Take(PageSize).ToList(); } else { cbag.list = query.Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList(); } cbag.par = pra; return cbag; }
public ActionResult ComplainManage(FormCollection form) { ExtentionComplainBag cbag = new ExtentionComplainBag(); if (form.Count != 0 || Request.QueryString.Count != 0)//有选择条件 { ComplainParam pra = new ComplainParam(); //post请求 if (Request.HttpMethod.Equals("POST", StringComparison.OrdinalIgnoreCase)) { if (form["SelTime"] != "" && form["SelTime"] != null) { pra.seltime = Convert.ToDateTime(form["SelTime"]); } else { pra.seltime = DateTime.Now.AddDays(-1); } } else if (DTRequest.GetQueryString("seltime") != "") { pra.seltime = Convert.ToDateTime(DTRequest.GetQueryString("seltime")); } else { pra.seltime = DateTime.Now.AddDays(-1); } //post请求 if (Request.HttpMethod.Equals("POST", StringComparison.OrdinalIgnoreCase)) { if (form["SelLine"] != "" && form["SelLine"] != null) { pra.selline = form["SelLine"]; } else { pra.selline = ""; } } else if (DTRequest.GetQueryString("SelLine") != "") { pra.selline = DTRequest.GetQueryString("SelLine"); } else { pra.selline = ""; } //post请求 if (Request.HttpMethod.Equals("POST", StringComparison.OrdinalIgnoreCase)) { if (form["SelKeywords"] != "" && form["SelKeywords"] != null) { pra.selkeywords = form["SelKeywords"]; } else { pra.selkeywords = ""; } } else if (DTRequest.GetQueryString("SelKeywords") != "") { pra.selkeywords = DTRequest.GetQueryString("SelKeywords"); } else { pra.selkeywords = ""; } this.pageSize = GetPageSize(20); //每页数量 this.page = DTRequest.GetQueryInt("page", 1); ViewBag.txtKeywords = this.keywords; cbag = new Admin_ExcelResourceForComplainLogic().GetAnalysisByComplain(this.pageSize, this.page, out this.totalCount, pra); //绑定页码 ViewBag.txtPageNum = this.pageSize.ToString(); string pageUrl = Utils.CombUrlTxt("../admin/ComplainManage", "seltime={0}&selline={1}&selkeywords={2}&page={3}", pra.seltime.ToString(), pra.selline, pra.selkeywords, "__id__"); ViewBag.PageContent = Utils.OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8); return View(cbag); } else { ComplainParam pra = new ComplainParam(); pra.seltime = DateTime.Now.AddDays(-1); pra.selline = ""; pra.selkeywords = ""; this.pageSize = GetPageSize(20); //每页数量 //post请求,点击查询返回第一页 if (Request.HttpMethod.Equals("POST", StringComparison.OrdinalIgnoreCase)) { this.page = 1; } else { this.page = DTRequest.GetQueryInt("page", 1); } ViewBag.txtKeywords = this.keywords; cbag = new Admin_ExcelResourceForComplainLogic().GetAnalysisByComplain(this.pageSize, this.page, out this.totalCount, pra); //绑定页码 ViewBag.txtPageNum = this.pageSize.ToString(); string pageUrl = Utils.CombUrlTxt("../admin/ComplainManage", "seltime={0}&selline={1}&selkeywords={2}&page={3}", pra.seltime.ToString(), pra.selline, pra.selkeywords, "__id__"); ViewBag.PageContent = Utils.OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8); return View(cbag); } }