Exemple #1
0
        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;
        }
Exemple #3
0
        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);
            }
        }