Autobio_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/report/TotalCallCon...

using CallCenter.Utility;
using CallCenterApi.Interface.Controllers.Base;
using System;
using System.Collections;
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 TotalCallController : BaseController
    {
        BLL.T_Call_CallRecords bll = new BLL.T_Call_CallRecords();
        //总呼叫量统计

        //获取表头
        public ActionResult GetColumnList()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                String[] str = { "月份", "日期", "总数" };
                res = Success("获取总呼叫量统计表头成功", str);
            }
            return res;
        }

        //获取数据
        public ActionResult GetDataList(string years, string dpt)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                DataTable dtNew = new DataTable();
                #region
                DataColumn dc1 = new DataColumn("月份", Type.GetType("System.String"));
                DataColumn dc2 = new DataColumn("日期", Type.GetType("System.String"));
                DataColumn dc3 = new DataColumn("总数", Type.GetType("System.String"));
                DataColumn dc4 = new DataColumn("Count", Type.GetType("System.String"));

                dtNew.Columns.Add(dc1);
                dtNew.Columns.Add(dc2);
                dtNew.Columns.Add(dc3);
                dtNew.Columns.Add(dc4);
                #endregion
                var sql = "";
                if (dpt != null && dpt.Trim() != "")
                {
                    sql += $" AND UserCode IN (SELECT F_UserCode FROM dbo.T_Sys_UserAccount WHERE F_DeptId = {dpt} AND f_seatFlag=1 and F_WorkNumber!='') ";
                }
                //DataTable dt = new BLL.T_Call_CallRecords().GetList("1=1").Tables[0];
                var list = bll.DataTableToList(bll.GetList($" 1=1 {sql} ").Tables[0]);
                DateTime dtNow;
                int Count = 0;
                int DayCount = 0;
                
                if (string.IsNullOrEmpty(years))
                {
                    dtNow = DateTime.Now;
                }
                else
                {
                    dtNow = DateTime.Parse(years + "年");
                }
                int monthcount = 0;
                for (int i = 1; i <= 12; i++)
                {
                    DataRow drNew = dtNew.NewRow();

                    DateTime beginTime = Convert.ToDateTime(dtNow.Year + "-" + i + "-01 00:00:00");
                    int days = DateTime.DaysInMonth(dtNow.Year, beginTime.Month);
                    //DataRow[] dtRow = dt.Select("begintime >='" + beginTime.ToString() + "' and  beginTime<'" + beginTime.AddDays(days).AddSeconds(-1) + "'");
                    var listByTime = list.Where(x => x.BeginTime >= Convert.ToDateTime(beginTime.ToString()) && x.BeginTime < beginTime.AddDays(days).AddSeconds(-1));

                    //if (dtRow.Length > 0)
                    if(listByTime.Count()>0)
                    {
                        //Count += dtRow.Length;
                        Count += listByTime.Count();
                        for (int j = 1; j <= days; j++)
                        {
                            DataRow dr1 = dtNew.NewRow();
                            //if (dtRow.CopyToDataTable().Select("begintime >='" + beginTime.AddDays(j - 1) + "' and  beginTime<'" + beginTime.AddDays(j).AddSeconds(-1) + "'").Length > 0)
                            if(listByTime.Where(x=>x.BeginTime>=beginTime.AddDays(j-1) && x.BeginTime<beginTime.AddDays(j).AddSeconds(-1)).Count()>0)
                            {
                                DayCount += 1;

                                dr1["月份"] = beginTime.Month + "月";
                                dr1["日期"] = beginTime.AddDays(j - 1).ToShortDateString();
                                // dr1["总数"] = dtRow.CopyToDataTable().Select("begintime >='" + beginTime.AddDays(j - 1) + "' and  beginTime<'" + beginTime.AddDays(j).AddSeconds(-1) + "'").Length;
                                dr1["总数"] = listByTime.Where(x => x.BeginTime >= beginTime.AddDays(j - 1) && x.BeginTime < beginTime.AddDays(j).AddSeconds(-1)).Count();
                                dr1["Count"] = "1";
                                dtNew.Rows.Add(dr1);
                            }
                        }

                        if (DayCount != 0)
                        {
                            drNew["月份"] = beginTime.Month + "月";
                            drNew["日期"] = "总计";
                            //drNew["总数"] = dtRow.Length;
                            drNew["总数"] = listByTime.Count();
                            drNew["Count"] = DayCount.ToString();
                            dtNew.Rows.Add(drNew);
                        }
                        DayCount = 0;
                        monthcount += 1;
                    }
                }
                if (monthcount != 0)
                {
                    DataRow drzj = dtNew.NewRow();
                    drzj["月份"] = dtNow.Year + "年";
                    drzj["日期"] = "总计";
                    drzj["总数"] = Count;
                    drzj["Count"] = monthcount.ToString();
                    dtNew.Rows.Add(drzj);
                }

                res = Success("获取呼叫量数据成功", dtNew);
            }
            return res;
        }

        private DataTable GetData(string years, string dpt)
        {
            DataTable dtNew = new DataTable();
            try {
                #region
                DataColumn dc1 = new DataColumn("月份", Type.GetType("System.String"));
                DataColumn dc2 = new DataColumn("日期", Type.GetType("System.String"));
                DataColumn dc3 = new DataColumn("总数", Type.GetType("System.String"));

                dtNew.Columns.Add(dc1);
                dtNew.Columns.Add(dc2);
                dtNew.Columns.Add(dc3);
                #endregion
                var sql = "";
                if (dpt != null && dpt.Trim() != "")
                {
                    sql += $" AND UserCode IN (SELECT F_UserCode FROM dbo.T_Sys_UserAccount WHERE F_DeptId = {dpt} AND f_seatFlag=1 and F_WorkNumber!='') ";
                }
                //DataTable dt = new BLL.T_Call_CallRecords().GetList("1=1").Tables[0];
                var list = bll.DataTableToList(bll.GetList($" 1=1 {sql} ").Tables[0]);
                DateTime dtNow;
                int Count = 0;
                int DayCount = 0;

                if (string.IsNullOrEmpty(years))
                {
                    dtNow = DateTime.Now;
                }
                else
                {
                    dtNow = DateTime.Parse(years + "年");
                }
                int monthcount = 0;
                for (int i = 1; i <= 12; i++)
                {
                    DataRow drNew = dtNew.NewRow();

                    DateTime beginTime = Convert.ToDateTime(dtNow.Year + "-" + i + "-01 00:00:00");
                    int days = DateTime.DaysInMonth(dtNow.Year, beginTime.Month);
                    //DataRow[] dtRow = dt.Select("begintime >='" + beginTime.ToString() + "' and  beginTime<'" + beginTime.AddDays(days).AddSeconds(-1) + "'");
                    var listByTime = list.Where(x => x.BeginTime >= Convert.ToDateTime(beginTime.ToString()) && x.BeginTime < beginTime.AddDays(days).AddSeconds(-1));
                    //if (dtRow.Length > 0)
                    if (listByTime.Count() > 0)
                    {
                        //Count += dtRow.Length;
                        Count += listByTime.Count();
                        for (int j = 1; j <= days; j++)
                        {
                            DataRow dr1 = dtNew.NewRow();
                            //if (dtRow.CopyToDataTable().Select("begintime >='" + beginTime.AddDays(j - 1) + "' and  beginTime<'" + beginTime.AddDays(j).AddSeconds(-1) + "'").Length > 0)
                            if (listByTime.Where(x => x.BeginTime >= beginTime.AddDays(j - 1) && x.BeginTime < beginTime.AddDays(j).AddSeconds(-1)).Count() > 0)
                            {
                                DayCount += 1;

                                dr1["月份"] = beginTime.Month + "月";
                                dr1["日期"] = beginTime.AddDays(j - 1).ToShortDateString();
                                //dr1["总数"] = dtRow.CopyToDataTable().Select("begintime >='" + beginTime.AddDays(j - 1) + "' and  beginTime<'" + beginTime.AddDays(j).AddSeconds(-1) + "'").Length;
                                dr1["总数"] = listByTime.Where(x => x.BeginTime >= beginTime.AddDays(j - 1) && x.BeginTime < beginTime.AddDays(j).AddSeconds(-1)).Count();

                                dtNew.Rows.Add(dr1);
                            }
                        }

                        if (DayCount != 0)
                        {
                            drNew["月份"] = beginTime.Month + "月";
                            drNew["日期"] = "总计";
                            //drNew["总数"] = dtRow.Length;
                            drNew["总数"] = listByTime.Count();
                            dtNew.Rows.Add(drNew);
                        }
                        DayCount = 0;
                        monthcount += 1;
                    }
                }
                if (monthcount != 0)
                {
                    DataRow drzj = dtNew.NewRow();
                    drzj["月份"] = dtNow.Year + "年";
                    drzj["日期"] = "总计";
                    drzj["总数"] = Count;
                    dtNew.Rows.Add(drzj);
                }
            }
            catch (Exception ex)
            { }
            return dtNew;
        }

        public ActionResult ExptList(string years, string dpt)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                NPOIHelper npoi = new NPOIHelper();
                DataTable dt = GetData(years, dpt);
                if (npoi.ExportToExcel("总呼叫量数据", dt) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return res;
        }

        //获取图形数据
        public ActionResult GetChartData(string years, string dpt)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                string[] arr=new string[12];
                var sql = "";
                if (dpt != null && dpt.Trim() != "")
                {
                    sql += $" AND UserCode IN (SELECT F_UserCode FROM dbo.T_Sys_UserAccount WHERE F_DeptId = {dpt} AND f_seatFlag=1 and F_WorkNumber!='') ";
                }
                //DataTable dt = new BLL.T_Call_CallRecords().GetList("1=1").Tables[0];
                var list = bll.DataTableToList(bll.GetList($" 1=1 {sql} ").Tables[0]);
                DateTime dtNow;

                if (string.IsNullOrEmpty(years))
                {
                    dtNow = DateTime.Now;
                }
                else
                {
                    dtNow = DateTime.Parse(years + "年");
                }
                for (int i = 1; i <= 12; i++)
                {
                    DateTime beginTime = Convert.ToDateTime(dtNow.Year + "-" + i + "-01 00:00:00");
                    int days = DateTime.DaysInMonth(dtNow.Year, beginTime.Month);
                    //DataRow[] dtRow = dt.Select("begintime >='" + beginTime.ToString() + "' and  beginTime<'" + beginTime.AddDays(days).AddSeconds(-1) + "'");
                    var listByTime = list.Where(x => x.BeginTime >= Convert.ToDateTime(beginTime.ToString()) && x.BeginTime < beginTime.AddDays(days).AddSeconds(-1));

                    //if (dtRow.Length > 0)
                    if (listByTime.Count() > 0)
                    {
                        //arr[i-1] = dtRow.Length.ToString();
                        arr[i-1] = listByTime.Count().ToString();
                    }
                    else
                    {
                        arr[i-1] = "0";
                    }
                }

                res = Success("获取呼叫量图形数据成功", arr);
            }
            return res;
        }
    }
}