using CallCenter.Utility;
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 FlowAnalysisController : BaseController
    {
        //流量分析

        //日流量图
        public ActionResult GetDayCount(string sdate,string edate)
        {
            DataTable dt = GetdayData(sdate, edate);

            int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            string[] counts = new string[24];
            for (int i = 0; i < hours.Length; i++)
            {
                var jtlist = dt.Select(" hor=" + hours[i] + " ");

                counts[i]= jtlist.Count() > 0 ? jtlist[0]["con"].ToString() : "0";
            }
            var obj = new
            {
                hours = hours,
                counts = counts
            };
            return Success("获取日流量图标数据成功", obj);
        }
        //日流量表格
        public ActionResult GetDayCountTable(string sdate, string edate)
        {
            DataTable dt = GetDayDataT(sdate, edate);
            return Success("获取日流量表格数据成功", dt);
        }
        //导出日流量数据
        public ActionResult ExptDayList(string sdate, string edate)
        {
            NPOIHelper npoi = new NPOIHelper();
            DataTable dt = GetDayDataT(sdate, edate);
            
            if (npoi.ExportToExcel("日流量数据", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
        #region 日流量数据读取
        public DataTable GetDayDataT(string sdate, string edate)
        {
            DataTable dtNew = new DataTable();
            #region
            DataColumn dc1 = new DataColumn("时刻", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("数量", Type.GetType("System.String"));
            dtNew.Columns.Add(dc1);
            dtNew.Columns.Add(dc2);
            #endregion
            DataTable dt = GetdayData(sdate, edate);
            int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            string[] counts = new string[24];
            for (int i = 0; i < hours.Length; i++)
            {
                DataRow drnew = dtNew.NewRow();
                var jtlist = dt.Select(" hor=" + hours[i] + " ");

                drnew["时刻"] = hours[i];
                drnew["数量"] = jtlist.Count() > 0 ? jtlist[0]["con"].ToString() : "0";
                dtNew.Rows.Add(drnew);
            }
            return dtNew;
        }
        public DataTable GetdayData(string sdate, string edate)
        {
            string sql = "";
            if (sdate != null && sdate.Trim() != "")
                sql += " and datediff(day, BeginTime, '" + sdate + "') <= 0";
            else
                sql += " and datediff(day, BeginTime, getdate()) <= 0";
            if (edate != null && edate.Trim() != "")
                sql += " and datediff(day, BeginTime, '" + edate + "') >= 0";
            else
                sql = " and datediff(day, BeginTime, getdate()) >= 0";
            DataTable dt = DbHelperSQL.Query(" select datepart(hh,BeginTime) hor,count(1) con from T_Call_CallRecords where 1=1 " + sql + " group by datepart(hh,BeginTime) ").Tables[0];
            
            return dt;
        }
        #endregion
        #region 月流量数据读取
        public DataTable GetMonthDataT(string sdate, string edate)
        {
            DataTable dtNew = new DataTable();
            #region
            DataColumn dc1 = new DataColumn("年月", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("数量", Type.GetType("System.String"));
            dtNew.Columns.Add(dc1);
            dtNew.Columns.Add(dc2);
            #endregion

            DateTime dt1 = System.DateTime.Now;
            DateTime dt2 = System.DateTime.Now;

            string sql = "";
            if (sdate != null && sdate.Trim() != "")
            {
                dt1 = Convert.ToDateTime(sdate.Trim());
            }
            if (edate != null && edate.Trim() != "")
            {
                dt2 = Convert.ToDateTime(edate.Trim());
            }

            int Month = (dt2.Year - dt1.Year) * 12 + (dt2.Month - dt1.Month) + 1;

            sql += " and datediff(month, BeginTime, '" + dt1.ToString("yyyy-MM-dd") + "') <= 0";
            sql += " and datediff(month, BeginTime, '" + dt2.ToString("yyyy-MM-dd") + "') >= 0";

            DataTable dt = DbHelperSQL.Query(" select DATEPART(year,BeginTime) years,DATEPART(month,BeginTime) mon,count(1) con from T_Call_CallRecords where 1=1 " + sql + " group by DATEPART(year,BeginTime),DATEPART(month,BeginTime) ").Tables[0];

            string[] dts = new string[Month];
            string[] counts = new string[Month];
            for (int i = 0; i < Month; i++)
            {
                DataRow drnew = dtNew.NewRow();
                DateTime dtn = dt1.AddMonths(i);
                var jtlist = dt.Select(" mon=" + dtn.Month + " and years=" + dtn.Year + " ");

                drnew["年月"] = dtn.ToString("yyyy-MM");
                drnew["数量"] = jtlist.Count() > 0 ? jtlist[0]["con"].ToString() : "0";
                dtNew.Rows.Add(drnew);
            }
            
            return dtNew;
        }
        #endregion

        //月流量图
        public ActionResult GetMonthCount(string sdate,string edate)
        {
            DateTime dt1 = System.DateTime.Now;
            DateTime dt2 = System.DateTime.Now;

            string sql = "";
            if (sdate != null && sdate.Trim() != "")
            {
                dt1 = Convert.ToDateTime(sdate.Trim());
            }
            if (edate != null && edate.Trim() != "")
            {
                dt2 = Convert.ToDateTime(edate.Trim());
            }

            int Month = (dt2.Year - dt1.Year) * 12 + (dt2.Month - dt1.Month)+1;

            sql += " and datediff(month, BeginTime, '" + dt1.ToString("yyyy-MM-dd") + "') <= 0";
            sql += " and datediff(month, BeginTime, '" + dt2.ToString("yyyy-MM-dd") + "') >= 0";

            DataTable dt = DbHelperSQL.Query(" select DATEPART(year,BeginTime) years,DATEPART(month,BeginTime) mon,count(1) con from T_Call_CallRecords where 1=1 " + sql + " group by DATEPART(year,BeginTime),DATEPART(month,BeginTime) ").Tables[0];
            
            string[] dts = new string[Month];
            string[] counts = new string[Month];
            for (int i = 0; i < Month; i++)
            {
                DateTime dtn = dt1.AddMonths(i);
                var jtlist = dt.Select(" mon=" + dtn.Month + " and years=" + dtn.Year + " ");

                dts[i] = dtn.ToString("yyyy-MM");
                counts[i] = jtlist.Count() > 0 ? jtlist[0]["con"].ToString() : "0";
            }
            var obj = new
            {
                months = dts,
                counts = counts
            };
            return Success("加载成功", obj);
        }

        //月流量表格
        public ActionResult GetMonthCountTable(string sdate, string edate)
        {
            DataTable dt = GetMonthDataT(sdate, edate);
            return Success("获取月流量表格数据成功", dt);
        }
        //导出月流量数据
        public ActionResult ExptMonthList(string sdate, string edate)
        {
            NPOIHelper npoi = new NPOIHelper();
            DataTable dt = GetMonthDataT(sdate, edate);

            if (npoi.ExportToExcel("月流量数据", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
    }
}