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