Anyang_api/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/report/DataAnalysis...

using CallCenter.Utility;
using CallCenter.Utility.Time;
using CallCenterApi.DB;
using CallCenterApi.Interface.Controllers.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace CallCenterApi.Interface.Controllers.report
{
    [Authority]
    public class DataAnalysisController : BaseController
    {
        // GET: DataAnalysis

        private BLL.T_Sys_DictionaryValue dicValueBLL = new BLL.T_Sys_DictionaryValue();

        ///// <summary>
        ///// 获取关键词分析报表
        ///// </summary>
        ///// <param name="date"></param>
        ///// <param name="isdc"></param>
        ///// <returns></returns>
        //public ActionResult GetKeyReport(string date,int isdc=0)
        //{
        //    DateTime dttime = DateTime.Now;
        //    if (!string.IsNullOrEmpty(date))
        //    {
        //        if (!DateTime.TryParse(date, out dttime))
        //        {
        //            dttime = DateTime.Now;
        //        }
        //    }
        //    int weeknow = Convert.ToInt32(dttime.DayOfWeek);
        //    weeknow = (weeknow == 0 ? 6 : (weeknow - 1));
        //    int daydiff = (-1) * weeknow;

        //    string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 ";
        //    var typelist = dicValueBLL.GetModelList(" F_ItemId=3 and F_State=0 ");
        //    var list = typelist.Select(p => {
        //        var sqlkey = sql + "and ','+F_Key+',' like '%," + p.F_ValueId + ",%'";
        //        return new
        //        {
        //            name = p.F_Value,
        //            daycount = DbHelperSQL.GetSingle(sqlkey + $" and datediff(day,F_CreateTime,'{dttime.ToString("yyyy-MM-dd")}')=0").ToString(),
        //            weekcount = DbHelperSQL.GetSingle(sqlkey + $" and datediff(day,F_CreateTime,'{dttime.AddDays(daydiff).ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{dttime.ToString("yyyy-MM-dd")}')>=0").ToString(),
        //            monthcount = DbHelperSQL.GetSingle(sqlkey + $" and datediff(day,F_CreateTime,'{dttime.AddDays(-1 * dttime.Day).ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{dttime.ToString("yyyy-MM-dd")}')>=0").ToString(),
        //            total= DbHelperSQL.GetSingle(sqlkey).ToString()
        //        };
        //    });
        //    string[] cols = { "关键词", "当日数量", "当周数量", "当月数量","总数量" };

        //    if (isdc > 0) {
        //        var dt = list.OrderByDescending(p=>p.total).ToList().ToDataTable(new string[] { "name", "daycount", "weekcount", "monthcount", "total" });
        //        var msg = new NPOIHelper().ExportToExcel($"关键词分析统计", dt, cols);
        //        if (msg == "")
        //        {
        //            return Success("导出成功");
        //        }
        //        else
        //        {
        //            return Success("导出失败");
        //        }
        //    }
        //    var obj = new {
        //        cols= cols,
        //        list= list
        //    };

        //    return Success("成功", obj);
        //}


        ///// <summary>
        ///// 获取关键词报表
        ///// </summary>
        ///// <param name="date"></param>
        ///// <param name="isdc"></param>
        ///// <returns></returns>
        //public ActionResult GetKeyReport(string date, int isdc = 0)
        //{
        //    DateTime dttime = DateTime.Now;
        //    if (!string.IsNullOrEmpty(date))
        //    {
        //        if (!DateTime.TryParse(date, out dttime))
        //        {
        //            dttime = DateTime.Now;
        //        }
        //    }
        //    var dt = DbHelperSQL.Query("select * from dbo.GetKeyReport('" + dttime.ToString("yyyy-MM-dd") + "') order by total desc").Tables[0];
        //    string[] cols = { "关键词", "当日数量", "当周数量", "当月数量", "总数量" };

        //    if (isdc > 0)
        //    {
        //        var msg = new NPOIHelper().ExportToExcel($"关键词分析统计", dt, cols);
        //        if (msg == "")
        //        {
        //            return Success("导出成功");
        //        }
        //        else
        //        {
        //            return Success("导出失败");
        //        }
        //    }
        //    var obj = new
        //    {
        //        cols = cols,
        //        list = dt
        //    };

        //    return Success("成功", obj);
        //}

        /// <summary>
        /// 获取关键词报表
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetKeyReport(string date, int isdc = 0)
        {
            DateTime dttime = DateTime.Now;
            if (!string.IsNullOrEmpty(date))
            {
                if (!DateTime.TryParse(date, out dttime))
                {
                    dttime = DateTime.Now;
                }
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@date", dttime.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_KeyReport", paras, "KeyReport");
            return Success("成功", obj);
        }

        /// <summary>
        /// 获取关键词报表
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetKeyReport_1(string sdate, string edate, int isdc = 0)
        {
            DateTime dttime = DateTime.Now;
            if (!string.IsNullOrEmpty(sdate))
            {
                sdate = dttime.ToString("yyyy-MM-dd") + " 00:00:00";
            }
            if (!string.IsNullOrEmpty(edate))
            {
                edate = dttime.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            var sql = "select F_Key  ,COUNT(1) as keycount ,dbo.GetDictionaryName(F_Key) as keyname  from T_Bus_WorkOrder where F_CreateTime between '"+ sdate + "' and '"+ edate + "' and F_IsDelete = 0   group by F_Key order by keycount desc";
            var db = DbHelperSQL.Query(sql).Tables[0];
            return Success("成功", db);
        }
        //public ActionResult GetKeyReport1(string date, int isdc = 0)
        //{
        //    DateTime dttime = DateTime.Now;
        //    if (!string.IsNullOrEmpty(date))
        //    {
        //        if (!DateTime.TryParse(date, out dttime))
        //        {
        //            dttime = DateTime.Now;
        //        }
        //    }
        //    string weeksdate = "", weekedate = "", monthsdate = "", monthedate = "";
        //    DateTools.GetStartEndTime(1, dttime.ToString("yyyy-MM-dd"),0, out weeksdate, out weekedate);
        //    DateTools.GetStartEndTime(2, dttime.ToString("yyyy-MM-dd"),0, out monthsdate, out monthedate);
        //    string whereday = " and datediff(day,F_CreateTime,'"+ dttime.ToString("yyyy-MM-dd") + "')=0) ";
        //    string whereweek = " and datediff(day,F_CreateTime,'" + weeksdate + "')<=0) and datediff(day,F_CreateTime,'" + weekedate + "')>=0) ";
        //    string wheremonth = " and datediff(month,F_CreateTime,'" + weeksdate + "')=0) ";

        //    //Dictionary<string, string> paras = new Dictionary<string, string>();
        //    //paras.Add("@date", dttime.ToString("yyyy-MM-dd"));
        //    //var obj = DbHelperSQL.RunProcedure("P_KeyReport", paras, "KeyReport");
        //    //return Success("成功", obj);

        //    var newobj = new DataSet();
        //    var keylist = dicValueBLL.GetModelList("F_PrentId=38 and F_State=0");
        //    var zjTable = DbHelperSQL.Query("select F_Key,count(1) counts from dbo.T_Bus_WorkOrder where F_IsDelete = 0 group by F_Key").Tables[0];
        //    var dayTable = DbHelperSQL.Query("select F_Key,count(1) counts from dbo.T_Bus_WorkOrder where F_IsDelete = 0 " + whereday + " group by F_Key").Tables[0];
        //    var weekTable = DbHelperSQL.Query("select F_Key,count(1) counts from dbo.T_Bus_WorkOrder where F_IsDelete = 0 " + whereweek + " group by F_Key").Tables[0];
        //    var monthTable = DbHelperSQL.Query("select F_Key,count(1) counts from dbo.T_Bus_WorkOrder where F_IsDelete = 0 " + wheremonth + " group by F_Key").Tables[0];

        //    foreach (var item in keylist)
        //    {
        //        int zjcounts = 0, daycounts = 0, weekcounts = 0, monthcounts = 0;
        //        DataRow[] drzj = zjTable.Select(" F_Key='" + item.F_ValueId + "'");
        //        if (drzj.Length > 0)
        //            zjcounts = (from DataRow dr in drzj select dr.Field<int>("counts")).FirstOrDefault();

        //        DataRow[] drday = dayTable.Select(" F_Key='" + item.F_ValueId + "'");
        //        if (drday.Length > 0)
        //            daycounts = (from DataRow dr in drday select dr.Field<int>("counts")).FirstOrDefault();

        //        DataRow[] drweek = weekTable.Select(" F_Key='" + item.F_ValueId + "'");
        //        if (drweek.Length > 0)
        //            weekcounts = (from DataRow dr in drweek select dr.Field<int>("counts")).FirstOrDefault();

        //        DataRow[] drmonth = monthTable.Select(" F_Key='" + item.F_ValueId + "'");
        //        if (drmonth.Length > 0)
        //            monthcounts = (from DataRow dr in drmonth select dr.Field<int>("counts")).FirstOrDefault();
        //    }

        //    return Success("成功", newobj);
        //}

        /// <summary>
        /// 获取简报周月
        /// </summary>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <param name="monthdate"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetSimpleReport(string type,string sdate, string edate, string monthdate, int isdc = 0)
        {
            DateTime stime = DateTime.Now;
            DateTime etime = DateTime.Now;


            if (type=="2")//月报
            {
                if (string.IsNullOrEmpty(monthdate))
                {
                    monthdate = stime.ToString("yyyy-MM");
                }
                stime = DateTime.Parse(monthdate + "-01");
                etime = stime.AddMonths(1).AddDays(-1);
            }
            else if (type == "1")//周报
            {
                if (!string.IsNullOrEmpty(sdate))
                {
                    stime = DateTime.Parse(sdate);
                }
                if (!string.IsNullOrEmpty(edate))
                {
                    etime = DateTime.Parse(edate);
                }
                if (string.IsNullOrEmpty(sdate) && string.IsNullOrEmpty(edate))
                {
                    int weeknow = Convert.ToInt32(stime.DayOfWeek);
                    weeknow = (weeknow == 0 ? 6 : (weeknow - 1));
                    int daydiff = (-1) * weeknow;

                    stime = stime.AddDays(daydiff);
                }
            }

            //string sqlhw = " select count(1) from dbo.T_Call_CallRecords where CallType = 0 ";
            //string sqlgd = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 ";
            //var hw = new
            //{
            //    ldcount = DbHelperSQL.GetSingle(sqlhw + $" and datediff(day,BeginTime,'{stime.ToString("yyyy-MM-dd")}')<=0 and datediff(day,BeginTime,'{etime.ToString("yyyy-MM-dd")}')>=0").ToString(),
            //    jtcount = DbHelperSQL.GetSingle(sqlhw + $" and CallState=1 and datediff(day,BeginTime,'{stime.ToString("yyyy-MM-dd")}')<=0 and datediff(day,BeginTime,'{etime.ToString("yyyy-MM-dd")}')>=0").ToString(),
            //    yxcount = DbHelperSQL.GetSingle(sqlgd + $" and F_InfoSource=1 and datediff(day,F_CreateTime,'{stime.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{etime.ToString("yyyy-MM-dd")}')>=0").ToString()
            //};

            //string sqlly = $" select F_Value Source,(select COUNT(1) from T_Bus_WorkOrder where datediff(day,F_CreateTime,'{stime.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{etime.ToString("yyyy-MM-dd")}')>=0 and F_IsDelete=0 "
            //             + " and  F_InfoSource=F_ValueId) Count from  dbo.T_Sys_DictionaryValue where F_ItemId=1 and F_State=0 ";
            //var gd = new
            //{
            //    gdcl = new
            //    {
            //        slweekcount = DbHelperSQL.GetSingle(sqlgd + $" and datediff(day,F_CreateTime,'{stime.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{etime.ToString("yyyy-MM-dd")}')>=0").ToString(),
            //        blweekcount = DbHelperSQL.GetSingle(sqlgd + $" and F_WorkOrderID in (select F_WorkOrderID from T_Bus_Feedback where F_State=1 and F_IsDelete=0 and F_Type in (1,2) and datediff(day,F_CreateTime,'{stime.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{etime.ToString("yyyy-MM-dd")}')>=0)").ToString(),
            //        wjweekcount = DbHelperSQL.GetSingle(sqlgd + $" and F_WorkState=9 and datediff(day,F_CloseTime,'{stime.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CloseTime,'{etime.ToString("yyyy-MM-dd")}')>=0").ToString()
            //    },
            //    gdly = DbHelperSQL.Query(sqlly).Tables[0],
            //};
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", stime.ToString("yyyy-MM-dd"));
            paras.Add("@edate", etime.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_SimpleReport", paras, "SimpleReport");
            if (isdc == 0)
            {
                return Success("成功", obj);
            }
            else
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.SimpleExportToExcel(obj) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
        }

        /// <summary>
        /// 获取舆情分析
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetKeyCountReport(string sdate, string edate, int isdc = 0)
        {
            DateTime stime = DateTime.Now;
            DateTime etime = DateTime.Now;

            if (!string.IsNullOrEmpty(sdate))
            {
                stime = DateTime.Parse(sdate);
            }
            if (!string.IsNullOrEmpty(edate))
            {
                etime = DateTime.Parse(edate);
            }
            if(string.IsNullOrEmpty(sdate) && string.IsNullOrEmpty(edate))
            {
                stime = new DateTime(stime.Year, stime.Month, 1);
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", stime.ToString("yyyy-MM-dd"));
            paras.Add("@edate", etime.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_KeyCountReport", paras, "KeyCountReport");
            if (isdc > 0)
            {
                string[] cols = { "关键词", "统计数量", "排名" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("舆情分析",obj.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }

        /// <summary>
        /// 获取工单报表
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetWorkReport(string sdate, string edate, int isdc = 0)
        {
            DateTime stime = DateTime.Now;
            DateTime etime = DateTime.Now;

            if (!string.IsNullOrEmpty(sdate))
            {
                stime = DateTime.Parse(sdate);
            }
            if (!string.IsNullOrEmpty(edate))
            {
                etime = DateTime.Parse(edate);
            }
            if (string.IsNullOrEmpty(sdate) && string.IsNullOrEmpty(edate))
            {
                stime = new DateTime(stime.Year, stime.Month, 1); ;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", stime.ToString("yyyy-MM-dd"));
            paras.Add("@edate", etime.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_WorkReport", paras, "WorkReport");
            if (isdc > 0)
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("工单报表", obj.Tables[0]) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }

        /// <summary>
        /// 获取话务报表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTelReport()
        {
            Dictionary<string, string> paras = new Dictionary<string, string>();
            var obj = DbHelperSQL.RunProcedure("P_ComplexReport", paras, "ComplexReport");
            return Success("成功", obj);
        }

        /// <summary>
        /// 获取话务报表
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetTellReport(string sdate, string edate, int isdc = 0)
        {
            DateTime stime = DateTime.Now;
            DateTime etime = DateTime.Now;

            if (!string.IsNullOrEmpty(sdate))
            {
                stime = DateTime.Parse(sdate);
            }
            if (!string.IsNullOrEmpty(edate))
            {
                etime = DateTime.Parse(edate);
            }
            if (string.IsNullOrEmpty(sdate) && string.IsNullOrEmpty(edate))
            {
                stime = new DateTime(stime.Year, stime.Month, 1); ;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", stime.ToString("yyyy-MM-dd"));
            paras.Add("@edate", etime.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_TellReport", paras, "TellReport");
            if (isdc > 0)
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("话务报表", obj.Tables[0]) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }

        /// <summary>
        /// 获取工单报表
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetOrderReport(string sdate, string edate, int isdc = 0)
        {
            DateTime stime = DateTime.Now;
            DateTime etime = DateTime.Now;

            if (!string.IsNullOrEmpty(sdate))
            {
                stime = DateTime.Parse(sdate);
            }
            if (!string.IsNullOrEmpty(edate))
            {
                etime = DateTime.Parse(edate);
            }
            if (string.IsNullOrEmpty(sdate) && string.IsNullOrEmpty(edate))
            {
                stime = new DateTime(stime.Year, stime.Month, 1); ;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", stime.ToString("yyyy-MM-dd"));
            paras.Add("@edate", etime.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_OrderReport", paras, "OrderReport");
            if (isdc > 0)
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("工单报表", obj.Tables[0]) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }
        /// <summary>
        /// 获取营商分析报表 工单满意度统计
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetYSSatisfaction(string date, int isdc = 0)
        {
            DateTime dttime = DateTime.Now;
            if (!string.IsNullOrEmpty(date))
            {
                if (!DateTime.TryParse(date, out dttime))
                {
                    dttime = DateTime.Now;
                }
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@date", dttime.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_YSSatisfaction", paras, "YSSatisfaction");
            return Success("成功", obj);
        }
        /// <summary>
        /// 获取营商分析报表 转办工单办理时长统计
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetYSDuration(string date, int isdc = 0)
        {
            DateTime dttime = DateTime.Now;
            if (!string.IsNullOrEmpty(date))
            {
                if (!DateTime.TryParse(date, out dttime))
                {
                    dttime = DateTime.Now;
                }
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@date", dttime.ToString("yyyy")+"-01-01");
            var obj = DbHelperSQL.RunProcedure("P_YSDuration", paras, "YSDuration");
            return Success("成功", obj);
        }
    }
}