using NPOI.HSSF.UserModel;
using RMYY_CallCenter_Api.DB;
using RMYY_CallCenter_Api.Model;
using RMYY_CallCenter_Api.Models;
using RMYY_CallCenter_Api.Models.Enum;
using RMYY_CallCenter_Api.Models.Input;
using RMYY_CallCenter_Api.Utility;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.Mvc;

namespace RMYY_CallCenter_Api.Controllers
{
    public class ReportController : BaseController
    {
        private BLL.T_IntegratedScheduling_Base baseBll = new BLL.T_IntegratedScheduling_Base();
        Bll.T_Sys_GongDan gdbll = new Bll.T_Sys_GongDan();
        private Bll.T_Sys_Department departmentBLL = new Bll.T_Sys_Department();
        private static BLL.T_IntegratedScheduling_OperationLog logBll = new BLL.T_IntegratedScheduling_OperationLog();
        private BLL.T_IntegratedScheduling_Detail detailBll = new BLL.T_IntegratedScheduling_Detail();
        private Bll.T_Sys_SystemConfig configBll = new Bll.T_Sys_SystemConfig();
        private BLL.T_IntegratedScheduling_Process proBll = new BLL.T_IntegratedScheduling_Process();


        private static InternalMessagesController msg = new InternalMessagesController();

        //   工勤人员完工统计     综合调度类工单统计：
        //1、筛选项：时间范围、部门、人员、工单类型，可设置显示字段
        //2、列表项：姓名、工号、接单量、处理量、处理效率、超时量、超时率、好评量、好评率、差评量、差评率
        //3、指标说明（需要在表头上增加提示标签，悬浮标签显示对应公式）：
        //接单量 = 当前查询范围内已接单的数量；
        //处理量 = 范围内已接单工单中已处理的数量；处理效率 = 处理量/接单量；
        //超时量 = 范围内接单工单并超时未处理的工单；超时率 = 超时量/接单量；
        //好评量/差评量 = 范围内接单工单被好评或差评的数量；
        //好评率/差评率 = 好评量/(好评量 + 差评量)

        [AllowAnonymous]
        public ActionResult SchedulingReport(string dealman, string starttime = "", string endtime = "", int dealdept = 0, int isdc = 0)
        {

            int recordCount = 0;
            string sql = $" and F_IsDelete = 0  ";

            if (!string.IsNullOrEmpty(starttime))
            {
                sql += $" and F_CreateTime>='{starttime}'";
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sql += $" and F_CreateTime<'{endtime}'";
            }

            if (dealdept > 0)
            {
                sql += $" and F_DealDept = '{ dealdept }' ";
            }

            if (!string.IsNullOrEmpty(dealman))
            {
                sql += $" and F_DealMan = '{ dealman }' ";
                
            }

            string dealmansql = "select dbo.GetUserName(F_dealman) dealusername, F_dealman,COUNT(1) allcount   from [T_IntegratedScheduling_Base]   where F_dealman is not null and f_dealman != ''" + sql+" group by F_dealman ";

            DataTable dealmandt = DbHelperSQL.Query(dealmansql).Tables[0];
            // 接单量、处理量、处理效率、超时量、超时率、好评量、好评率、差评量、差评率

            string allordersql = "    F_dealman is not null and f_dealman != ''"+sql;


            List<Model.T_IntegratedScheduling_Base> modellist = baseBll.GetModelList(allordersql);

          


            dealmandt.Columns.Add("jiedanliang");
            dealmandt.Columns.Add("chuliliang");
           //处理效率 = 处理量/接单量；

            DataColumn chkColumn = new DataColumn();
            chkColumn.DataType =typeof(string);
            chkColumn.ColumnName = "chulixiaolv";
            chkColumn.DefaultValue = "0%";
            dealmandt.Columns.Add(chkColumn);


            DataColumn chkColumn1 = new DataColumn();
            chkColumn1.DataType = typeof(int);
            chkColumn1.ColumnName = "chaoshiliang";
            chkColumn1.DefaultValue = 0;
            dealmandt.Columns.Add(chkColumn1);

            DataColumn chkColumn2 = new DataColumn();
            chkColumn2.DataType = typeof(string);
            chkColumn2.ColumnName = "chaoshilv";
            chkColumn2.DefaultValue = "0%";
            dealmandt.Columns.Add(chkColumn2);

            DataColumn chkColumn3 = new DataColumn();
            chkColumn3.DataType = typeof(int);
            chkColumn3.ColumnName = "haopingliang";
            chkColumn3.DefaultValue = 0;
            dealmandt.Columns.Add(chkColumn3);

            DataColumn chkColumn4 = new DataColumn();
            chkColumn4.DataType = typeof(string);
            chkColumn4.ColumnName = "haopinglv";
            chkColumn4.DefaultValue = "0%";
            dealmandt.Columns.Add(chkColumn4);


            DataColumn chkColumn5 = new DataColumn();
            chkColumn5.DataType = typeof(int);
            chkColumn5.ColumnName = "chapingliang";
            chkColumn5.DefaultValue = 0;
            dealmandt.Columns.Add(chkColumn5);


            DataColumn chkColumn6 = new DataColumn();
            chkColumn6.DataType = typeof(string);
            chkColumn6.ColumnName = "chapinglv";
            chkColumn6.DefaultValue = "0%";
            dealmandt.Columns.Add(chkColumn6);


            if (dealmandt != null && dealmandt.Rows.Count > 0)
            {
                foreach (DataRow dr in dealmandt.Rows)
                {
                    List<string> jiedanlist = modellist.Where(x => x.F_DealMan == dr["F_DealMan"].ToString() && x.F_WorkOrderState > 1).Select(x => x.F_WorkOrderCode).ToList();
                    dr["jiedanliang"] = jiedanlist.Count;

                    List<string> chulilist = modellist.Where(x => x.F_DealMan == dr["F_DealMan"].ToString() && x.F_WorkOrderState > 2).Select(x => x.F_WorkOrderCode).ToList();
                    dr["chuliliang"] = chulilist.Count;

                    if (jiedanlist.Count != 0)
                    {
                        dr["chulixiaolv"] = Math.Round(decimal.Parse(chulilist.Count.ToString()) / jiedanlist.Count * 100, 2).ToString() + "%";
                    }

                    var chaoshilist = modellist.Where(x => x.F_Deadline < x.F_Endtime || (x.F_Endtime == null && x.F_Deadline < DateTime.Now)).ToList();
                    dr["chaoshiliang"] = chaoshilist.Count;
                    if (jiedanlist.Count != 0)
                    {
                        //超时率 = 超时量/接单量；
                        dr["chaoshilv"] = Math.Round(decimal.Parse(chaoshilist.Count.ToString()) / jiedanlist.Count * 100, 2).ToString() + "%";
                    }
                    //好评量/差评量 = 范围内接单工单被好评或差评的数量；
                    var haopinglist = modellist.Where(x => x.MYD == 1 || x.MYD == 0).ToList();

                    dr["haopingliang"] = haopinglist.Count;

                    var chapinglist= modellist.Where(x => x.MYD == 2).ToList();

                    dr["chapingliang"] = chapinglist.Count;


                    int pingjiacount= haopinglist.Count+ chapinglist.Count;
                    if (pingjiacount != 0)
                    {
                        //好评率/差评率 = 好评量/(好评量 + 差评量)
                        dr["haopinglv"] = Math.Round(decimal.Parse(haopinglist.Count.ToString()) / pingjiacount * 100, 2).ToString() + "%";

                        dr["chapinglv"] = Math.Round(decimal.Parse(chapinglist.Count.ToString()) / pingjiacount * 100, 2).ToString() + "%";
                    }
                   
                    



                }
            }


            if (isdc > 0)
            {
                 
                dealmandt.Columns.Remove("allcount");
 
                var msg = new NPOIHelper().ExportToExcel("工勤人员完工统计", dealmandt, new string[] { "姓名", "工号", "接单量", "处理量" , "处理效率", "超时量", "超时率", "好评量", "好评率", "差评量", "差评率" });
                if (msg == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }



         

            var obj = new
                {
                    state = "success",
                    message = "成功",
                    rows = dealmandt,
                    total = recordCount

                };
                return Content(obj.ToJson());
            }


        //        科室转接统计报表报表：
        //1、筛选项：时间范围（精确到时分秒）、科室下拉框，可设置显示字段
        //2、列表项：科室、转接电话量、接通量、未接通量、接通率（接通量/转接电话量），只显示有转接的科室数据，不需要进行分页
        public ActionResult TransferList(string starttime = "", string endtime = "",int  transferdept=0,int isdc=0)
        {
            Bll.T_Call_CallRecords bll = new Bll.T_Call_CallRecords();
            string sql = "   F_TransferToDept is not null";
         
            if (starttime.Trim() != "")
            {
                sql += " and F_TransferToTime>='" + Convert.ToDateTime(starttime).ToString("yyyy-MM-dd hh:mm:ss") + "' ";
            }
            if (endtime.Trim() != "")
            {
                sql += " and F_TransferToTime<='" + Convert.ToDateTime(endtime).ToString("yyyy-MM-dd hh:mm:ss") + "' ";
            }
            if (transferdept > 0)
            {
                sql += " and F_TransferToDept='" + transferdept + "'";
            }

            DataTable dt = DbHelperSQL.Query(" select F_transfertodept,[dbo].[GetDepName](F_TransferToDept)  transefername ,count(1) transfercount from T_Call_CallRecords where " + sql + " group by F_transfertodept ").Tables[0];

            List<Model.T_Call_CallRecords> modellist = bll.GetModelList(sql);

            DataColumn chkColumn = new DataColumn();
            chkColumn.DataType = typeof(int);
            chkColumn.ColumnName = "jietongliang";
            chkColumn.DefaultValue = 0;
            dt.Columns.Add(chkColumn);


            DataColumn chkColumn1 = new DataColumn();
            chkColumn1.DataType = typeof(int);
            chkColumn1.ColumnName = "weijietongliang";
            chkColumn1.DefaultValue = 0;
            dt.Columns.Add(chkColumn1);

            DataColumn chkColumn2 = new DataColumn();
            chkColumn2.DataType = typeof(string);
            chkColumn2.ColumnName = "jietonglv";
            chkColumn2.DefaultValue = "0%";
            dt.Columns.Add(chkColumn2);

         

            foreach (DataRow  dr in dt.Rows)
            {
                List<string> jietonglianglist = modellist.Where(x => x.F_TransferToDept ==Convert.ToInt32(  dr["F_TransferToDept"].ToString() )&& x.F_TransferCallState == 1).Select(x => x.F_TransferToPhone).ToList();
                dr["jietongliang"] = jietonglianglist.Count;


                List<string> weijietonglianglist = modellist.Where(x => x.F_TransferToDept == Convert.ToInt32(dr["F_TransferToDept"].ToString()) && x.F_TransferCallState == 0).Select(x => x.F_TransferToPhone).ToList();
                dr["weijietongliang"] = weijietonglianglist.Count;

                int countall =Convert.ToInt32(  dr["transfercount"].ToString());
                    
                if (countall != 0)
                {
                    //接通率（接通量/转接电话量）
                    dr["jietonglv"] = Math.Round(decimal.Parse(jietonglianglist.Count.ToString()) / countall * 100, 2).ToString() + "%";
                }
                }


            if (isdc > 0)
            {
                dt.Columns.Remove("F_transfertodept");
                //科室、转接电话量、接通量、未接通量、接通率
                var msg = new NPOIHelper().ExportToExcel("科室转接统计报表", dt, new string[] { "科室", "转接电话量", "接通量", "未接通量", "接通率" });
                if (msg == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }

            return Success("科室转接统计列表",dt);
        }


        //        参考原型图中的工单类型统计报表
        //1、筛选项：时间，可精确到时分秒
        //2、车辆调度按照申请部门所在院区统计
        //3、其他类型数据按照二级类型统计，包含二级以及所有子级工单数量（可与小杜沟通，跟他的大屏接口调用同一个公共方法）

        [AllowAnonymous]
        public ActionResult StatisticsGongDanTypeList(string starttime = "", string endtime = "",int isdc=0,int yqid=0)
        {
            string sql = "";
            //string sql = " and F_CreateUser in (select F_UserCode from T_Sys_UserAccount where F_RoleId in (select F_RoleId from T_Sys_Role where F_RoleCode = 'DDZX'))";


            //if (starttime.Trim() == "" && endtime.Trim() == "")
            //{
            //    sql += "  and  datediff(day, F_CreateTime, getdate()) = 0";
            //}
            if (yqid > 0)
            {
                sql += " and  ( select T_Woid from T_Sys_Department where F_DeptId = F_ProposerDept)='"+ yqid + "'";
            }
            if (starttime.Trim() != "")
            {
                sql += " and F_CreateTime>='" + Convert.ToDateTime(starttime).ToString("yyyy-MM-dd hh:mm:ss") + "' ";
            }
            if (endtime.Trim() != "")
            {
                sql += " and F_CreateTime<='" + Convert.ToDateTime(endtime).ToString("yyyy-MM-dd hh:mm:ss") + "' ";
            }
          
            DataTable dtzx = new DataTable();
            string ywzxsql = "select a. F_GDID F_Type , a.F_Name,'业务咨询',(SELECT COUNT(1) FROM T_Wo_CommonBase WHERE   F_IsDelete = 0"+sql+" AND F_SonType in (select F_GDId  from[dbo].[GetValueId](a.F_GDID))    ) COUNT from T_Sys_Gongdan a where F_ParentID = 1000 AND F_IsDelete = 0";

             dtzx= DbHelperSQL.Query(ywzxsql).Tables[0];

            DataTable dtzhdd = new DataTable();
            string zhddsql = "select a. F_GDID F_Type , a.F_Name,'综合调度',(SELECT COUNT(1) FROM T_Wo_CommonBase WHERE   F_IsDelete = 0  " + sql + "AND F_SonType in (select F_GDId  from[dbo].[GetValueId](a.F_GDID))  ) COUNT from T_Sys_Gongdan a where F_ParentID = 2000 AND F_IsDelete = 0";

            dtzhdd = DbHelperSQL.Query(zhddsql).Tables[0];


            DataTable dtgzbx = new DataTable();
            string gzbxsql = "select a. F_GDID F_Type , a.F_Name,'故障报修',(SELECT COUNT(1) FROM T_Wo_CommonBase WHERE   F_IsDelete = 0  " + sql + " AND F_SonType in (select F_GDId  from[dbo].[GetValueId](a.F_GDID))    ) COUNT from T_Sys_Gongdan a where F_ParentID = 3000 AND F_IsDelete = 0";

            dtgzbx = DbHelperSQL.Query(gzbxsql).Tables[0];


            DataTable dtcldd = new DataTable();
            //string clddsql = " select [T_Woid] ,[T_Woname],(select COUNT(1) from T_Car_WorkOrder WHERE   F_IsDel=0 and F_ProposerDeptId in  (select F_DeptId from T_Sys_Department where T_Woid = T_Wowo_repairyq.T_Woid) "+sql+") COUNT from T_Wowo_repairyq";

            string clddsql = "select a. F_GDID F_Type , a.F_Name,'车辆调度',(SELECT COUNT(1) FROM T_Wo_CommonBase WHERE   F_IsDelete = 0  " + sql + " AND F_SonType in (select F_GDId  from[dbo].[GetValueId](a.F_GDID))    ) COUNT from T_Sys_Gongdan a where F_ParentID = 3000 AND F_IsDelete = 0";
            dtcldd = DbHelperSQL.Query(clddsql).Tables[0];






            DataTable zsdt = new DataTable();
            string sqlzs = " select F_Type,COUNT(1) zscount,(select F_name from T_Sys_GongDan where F_GDId=F_Type) typename from T_Wo_CommonBase  where F_IsDelete = 0 "+sql+ "  group by  F_Type";
            zsdt = DbHelperSQL.Query(sqlzs).Tables[0];

            if (isdc > 0)
            {
                if (zsdt != null && zsdt.Rows.Count > 0)
                {
                    var msg = new NPOIHelper().SimpleExportToExcel(zsdt, dtzx, dtzhdd, dtgzbx, dtcldd);
                    if (msg == "")
                    {
                        return Success("导出成功");
                    }
                    else
                    {
                        return Error("导出失败");
                    }
                }
                else
                {
                    return Error("暂无数据可导出");
                }
              
            }

            var obj = new  {
                zongshudt=  zsdt,
                ywzxdt= dtzx,
                zhdddt= dtzhdd,
               gzbxdt= dtgzbx,
               cldddt= dtcldd

            };

            return Success("获取成功",obj);
        }

        public ActionResult GetYuanqu()
        {
            var list = new Bll.T_Wowo_repairyqbll().GetListall();
            return Success("院区列表",list);
        }


        public ActionResult GongDanTypeDetali(string starttime = "", string endtime = "",int typeid=0,int yqid=0)
        {
       
            DataTable dt = new DataTable();
            string sql = " and F_CreateUser in (select F_UserCode from T_Sys_UserAccount where F_RoleId in (select F_RoleId from T_Sys_Role where F_RoleCode = 'DDZX'))";
            //if (starttime.Trim() == "" && endtime.Trim() == "")
            //{
            //    sql += "  and  datediff(day, F_CreateTime, getdate()) = 0";
            //}
            if (starttime.Trim() != "")
            {
                sql += " and F_CreateTime>='" + Convert.ToDateTime(starttime).ToString("yyyy-MM-dd hh:mm:ss") + "' ";
            }
            if (endtime.Trim() != "")
            {
                sql += " and F_CreateTime<='" + Convert.ToDateTime(endtime).ToString("yyyy-MM-dd hh:mm:ss") + "' ";
            }

            //不是车辆调度的
            if (yqid == 0)
            {
                if (typeid == 0)
                {
                    return Error("typeid不能等于0或者null");
                }
            
                string ywzxsql = "select a. F_GDID F_Type , a.F_Name,(SELECT COUNT(1) FROM T_Wo_CommonBase WHERE   F_IsDelete = 0 AND F_SonType in (select F_GDId  from[dbo].[GetValueId](a.F_GDID))  " + sql + "  ) COUNT from T_Sys_Gongdan a where F_ParentID = '" + typeid + "' AND F_IsDelete = 0";

                dt = DbHelperSQL.Query(ywzxsql).Tables[0];
            }
            //车辆调度
            else
            {
                if (typeid == 0)
                {
                    string clddsql = "select a. F_GDID F_Type , a.F_Name,(SELECT COUNT(1) FROM T_Wo_CommonBase WHERE   F_IsDelete = 0 and F_ProposerDept in(select F_DeptId from T_Sys_Department where T_Woid = '" + yqid + "') AND F_SonType in (select F_GDId from[dbo].[GetValueId] (a.F_GDID)) " + sql + " ) COUNT from T_Sys_Gongdan a where F_ParentID = 4000 AND F_IsDelete = 0";
                    dt = DbHelperSQL.Query(clddsql).Tables[0];

                }
                else
                {
                    string clddsql = "select a. F_GDID F_Type , a.F_Name,(SELECT COUNT(1) FROM T_Wo_CommonBase WHERE   F_IsDelete = 0 and F_ProposerDept in(select F_DeptId from T_Sys_Department where T_Woid = '" + yqid + "') AND F_SonType in (select F_GDId from[dbo].[GetValueId] (a.F_GDID)) " + sql + " ) COUNT from T_Sys_Gongdan a where F_ParentID = '"+typeid+"' AND F_IsDelete = 0";
                    dt = DbHelperSQL.Query(clddsql).Tables[0];

                }
            }

            return Success("获取子级类型成功",dt);


        }



    }



}
