ditie2.0_web/web/YTSoft.BaseC.../Controllers/Report/ReportOtherC...

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using System.Web.Script.Serialization;
using YTSoft.BaseCallCenter.Model;
using YTSoft.BaseCallCenter.MVCWeb.Commons;
using YTSoft.BaseCallCenter.MVCWeb.Models;
using YTSoft.Common;

namespace YTSoft.BaseCallCenter.MVCWeb.Controllers
{
    public class ReportOtherController : BaseController
    {
        BLL.ReportBLL busReport = new BLL.ReportBLL();

        #region 其他指标
        public ActionResult GetindexView()
        {
            WorkOrderMyModel model = new WorkOrderMyModel();

            return View(model);
        }
        /// <summary>
        /// 获取工单信息
        /// </summary>
        /// <param name="page">当前页码</param>
        /// <param name="limit">每页数据量</param>
        /// <param name="sqlWhere">查询条件</param>
        /// <returns></returns>
        [ActionName("GetindexData")]
        [HttpGet]
        public string GetindexData(DateTime? NowDateTime, string dateParty)
        {
            //数据结果集
            ResponseData dataModel = new ResponseData();

            try
            {
                string startDate = "";
                string endDate = "";
                if (!string.IsNullOrEmpty(dateParty))
                {
                    startDate = dateParty.Substring(0, 10);
                    endDate = dateParty.Substring(12);

                }
                else
                {
                    startDate = endDate = DateTime.Now.ToString("yyyy-MM-dd");
                }

                DataTable datas = null;
                DataTable newTable = new DataTable();
                //newTable.Columns.Add("ID");
                newTable.Columns.Add("指标名称");
                newTable.Columns.Add("指标值");
                DataRow dataRow;
                int OvertimeCount = 0;

                #region 接通率
                dataRow = newTable.NewRow();
                datas = busReport.GetOtherData1(startDate, endDate);
                if (datas != null && datas.Rows.Count > 0)
                {
                    string data1 = "-";
                    int tempTotal = 0;
                    int tempint = 0;
                    foreach (DataRow thisRow in datas.Rows)
                    {
                        if (thisRow["CallState"].ToInt32() == 1)
                        {
                            tempint = thisRow["num"].ToInt32();
                        }
                        tempTotal += thisRow["num"].ToInt32();
                       
                    }
                    if (tempTotal != 0)
                    {
                        data1 = (tempint / (double)tempTotal).ToString("0.00%");
                    }

                   
                    dataRow["指标名称"] = "接通率";
                    dataRow["指标值"] = data1;
                  
                }
                else
                {
                    dataRow["指标名称"] = "接通率";
                    dataRow["指标值"] = "-";
                }
                newTable.Rows.Add(dataRow);
                #endregion

                #region 流失率
                datas = busReport.GetOtherData2(startDate, endDate);
                dataRow = newTable.NewRow();
                if (datas != null && datas.Rows.Count > 0)
                {
                    string data1 = "-";
                    int tempTotal = 0;
                    int tempint = 0;
                    foreach (DataRow thisRow in datas.Rows)
                    {
                        if (thisRow["CallState"].ToInt32() == 0)
                        {
                            tempint = thisRow["num"].ToInt32();
                        }
                        tempTotal += thisRow["num"].ToInt32();

                    }
                    if (tempTotal != 0)
                    {
                        data1 = (tempint / (double)tempTotal).ToString("0.00%");
                    }

                    dataRow["指标名称"] = "流失率";
                    dataRow["指标值"] = data1;

                }
                else
                {
                    dataRow["指标名称"] = "流失率";
                    dataRow["指标值"] = "-";
                }
                newTable.Rows.Add(dataRow);
                #endregion

                #region 按时转出率
                datas = busReport.GetOtherData3(startDate, endDate);
                if (datas != null && datas.Rows.Count > 0)
                {
                    string data1 = "-";
                    int tempTotal = 0;
                    int tempint = 0;
                    foreach (DataRow thisRow in datas.Rows)
                    {
                        if (thisRow["CallState"].ToInt32() == 1)
                        {
                            tempint = thisRow["num"].ToInt32();
                        }
                        tempTotal += thisRow["num"].ToInt32();

                    }
                    if (tempTotal != 0)
                    {
                        data1 = (tempint / (double)tempTotal).ToString("0.00%");
                    }
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "按时转出率";
                    dataRow["指标值"] = data1;
                    newTable.Rows.Add(dataRow);
                }
                #endregion

                #region 首呼解决率
                datas = busReport.GetOtherData10(startDate, endDate);
                if (datas != null && datas.Rows.Count > 0)
                {
                    string data1 = "100.00%";
                    string data2 = "-";
                    string data3 = "-";
                    int tempTotal = 0;
                    int tempint = 0;
                    int tempTotal1 = 0;
                    int tempint1 = 0;
                    int tempTotal2 = 0;
                    int tempint2 = 0;
                    foreach (DataRow thisRow in datas.Rows)
                    {
                        //if (thisRow["F_HOUSING"].ToMyString() == "咨询")
                        //{
                        //    if (thisRow["CallState"].ToInt32() == 1)
                        //    {
                        //        tempint = thisRow["num"].ToInt32();
                        //    }
                        //    tempTotal += thisRow["num"].ToInt32();
                        //}
                        if (thisRow["F_HOUSING"].ToMyString() == "建议")
                        {
                            if (thisRow["CallState"].ToInt32() == 1)
                            {
                                tempint1 = thisRow["num"].ToInt32();
                            }
                            tempTotal1 += thisRow["num"].ToInt32();
                        }
                        if (thisRow["F_HOUSING"].ToMyString() == "投诉")
                        {

                            if (thisRow["CallState"].ToInt32() == 1)
                            {
                                tempint2 = thisRow["num"].ToInt32();
                            }
                            tempTotal2 += thisRow["num"].ToInt32();
                        }

                    }
                    //if (tempTotal != 0)
                    //{
                    //    data1 = (tempint / (double)tempTotal).ToString("0.00%");
                    //}
                    if (tempTotal1 != 0)
                    {
                        data2 = (tempint1 / (double)tempTotal1).ToString("0.00%");
                    }
                    if (tempTotal2 != 0)
                    {
                        data3 = (tempint2 / (double)tempTotal2).ToString("0.00%");
                    }
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "咨询客服化解率";
                    dataRow["指标值"] = data1;
                    newTable.Rows.Add(dataRow);
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "建议客服化解率";
                    dataRow["指标值"] = data2;
                    newTable.Rows.Add(dataRow);
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "投诉客服化解率";
                    dataRow["指标值"] = data3;
                    newTable.Rows.Add(dataRow);
                }

                #endregion

                #region 按时办结率
                datas = busReport.GetOtherData4(startDate, endDate);
                if (datas != null && datas.Rows.Count > 0)
                {
                    string data1 = "-";
                    string data2 = "-";
                    string data3 = "-";
                    int tempTotal = 0;
                    int tempint = 0;
                    int tempTotal1 = 0;
                    int tempint1 = 0;
                    int tempTotal2 = 0;
                    int tempint2 = 0;
                    foreach (DataRow thisRow in datas.Rows)
                    {
                        

                        if (thisRow["CallState"].ToInt32() == 0)
                        {
                            OvertimeCount +=  thisRow["num"].ToInt32();
                        }

                        if (thisRow["F_HOUSING"].ToMyString() == "咨询")
                        {
                            if (thisRow["CallState"].ToInt32() == 1)
                            {
                                tempint = thisRow["num"].ToInt32();
                            }
                            tempTotal += thisRow["num"].ToInt32();
                        }
                        if (thisRow["F_HOUSING"].ToMyString() == "建议")
                        {

                            if (thisRow["CallState"].ToInt32() == 1)
                            {
                                tempint1 = thisRow["num"].ToInt32();
                            }
                            tempTotal1 += thisRow["num"].ToInt32();
                        }
                        if (thisRow["F_HOUSING"].ToMyString() == "投诉")
                        {

                            if (thisRow["CallState"].ToInt32() == 1)
                            {
                                tempint2 = thisRow["num"].ToInt32();
                            }
                            tempTotal2 += thisRow["num"].ToInt32();
                        }

                    }
                    if (tempTotal != 0)
                    {
                        data1 = (tempint / (double)tempTotal).ToString("0.00%");
                    }
                    if (tempTotal1 != 0)
                    {
                        data2 = (tempint1 / (double)tempTotal1).ToString("0.00%");
                    }
                    if (tempTotal2 != 0)
                    {
                        data3 = (tempint2 / (double)tempTotal2).ToString("0.00%");
                    }
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "咨询按时办结率";
                    dataRow["指标值"] = data1;
                    newTable.Rows.Add(dataRow);
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "建议按时办结率";
                    dataRow["指标值"] = data2;
                    newTable.Rows.Add(dataRow);
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "投诉按时办结率";
                    dataRow["指标值"] = data3;
                    newTable.Rows.Add(dataRow);
                }
                #endregion

                #region 按时答复率
                datas = busReport.GetOtherData5(startDate, endDate);
                if (datas != null && datas.Rows.Count > 0)
                {
                    string data1 = "-";
                    int tempTotal = 0;
                    int tempint = 0;
                    foreach (DataRow thisRow in datas.Rows)
                    {
                        if (thisRow["CallState"].ToInt32() == 1)
                        {
                            tempint = thisRow["num"].ToInt32();
                        }
                        tempTotal += thisRow["num"].ToInt32();

                    }
                    if (tempTotal != 0)
                    {
                        data1 = (tempint / (double)tempTotal).ToString("0.00%");
                    }
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "按时答复率";
                    dataRow["指标值"] = data1;
                    newTable.Rows.Add(dataRow);
                }
                #endregion

                #region  超时工单数
                dataRow = newTable.NewRow();
                dataRow["指标名称"] = "超时工单数";
                dataRow["指标值"] = OvertimeCount;
                newTable.Rows.Add(dataRow);
                #endregion

                #region 定责指标
                datas = busReport.GetOtherData6(startDate, endDate);
                if (true)
                {
                    string data1 = "-";
                    string data2 = "-";
                    string data3 = "-";
                    string data4 = "-";
                    int tempTotal1 = 0;
                    int tempint1 = 0;
                    int tempTotal2 = 0;
                    int tempint2 = 0;
                    int tempTotal3 = 0;
                    int tempint3 = 0;
                    int tempTotal4 = 0;
                    int tempint4 = 0;
                    if (datas != null && datas.Rows.Count > 0)
                    {

                        foreach (DataRow thisRow in datas.Rows)
                        {
                            // 根据事件概况，初步定性为无效投诉
                            // 根据事件概况，初步定性为有效无责投诉
                            // 根据事件概况，初步定性为一级有责投诉
                            // 根据事件概况，初步定性为二级有责投诉
                            // 根据事件概况，初步定性为三级有责投诉



                            if (thisRow["F_SERVICENATURE"].ToMyString().Contains("无效投诉"))
                            {
                                tempint1 += thisRow["num"].ToInt32();
                                tempTotal1 += thisRow["num"].ToInt32();
                            }
                            else if (thisRow["F_SERVICENATURE"].ToMyString().Contains("责投诉"))
                            {
                                tempint2 += thisRow["num"].ToInt32();
                                tempTotal1 += thisRow["num"].ToInt32();
                            }
                            if (thisRow["F_SERVICENATURE"].ToMyString().Contains("有责投诉"))
                            {
                                tempint3 += thisRow["num"].ToInt32();
                            }
                            else if (thisRow["F_SERVICENATURE"].ToMyString().Contains("有效无责投诉"))
                            {
                                tempint4 += thisRow["num"].ToInt32();
                            }

                        }            
                    }
                    if (tempTotal1 != 0)
                    {
                        data1 = (tempint1 / (double)tempTotal1).ToString("0.00%");
                        data2 = (tempint2 / (double)tempTotal1).ToString("0.00%");
                        data3 = (tempint3 / (double)tempTotal1).ToString("0.00%");
                        data4 = (tempint4 / (double)tempTotal1).ToString("0.00%");
                    }
                    #region 指标赋值
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "无效投诉数量";
                    dataRow["指标值"] = tempint1;
                    newTable.Rows.Add(dataRow);
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "无效投诉占比";
                    dataRow["指标值"] = data1;
                    newTable.Rows.Add(dataRow);

                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "有效投诉数量";
                    dataRow["指标值"] = tempint2;
                    newTable.Rows.Add(dataRow);
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "有效投诉占比";
                    dataRow["指标值"] = data2;
                    newTable.Rows.Add(dataRow);

                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "有责投诉数量";
                    dataRow["指标值"] = tempint3;
                    newTable.Rows.Add(dataRow);
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "有责投诉占比";
                    dataRow["指标值"] = data3;
                    newTable.Rows.Add(dataRow);

                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "有效无责数量";
                    dataRow["指标值"] = tempint4;
                    newTable.Rows.Add(dataRow);
                    dataRow = newTable.NewRow();
                    dataRow["指标名称"] = "有效无责占比";
                    dataRow["指标值"] = data4;
                    newTable.Rows.Add(dataRow);
                    #endregion
                }
                #endregion

                dataModel.code = 0;
                dataModel.data = newTable;

            }
            catch (Exception ex)
            {
                dataModel.code = 200;
                dataModel.msg = ex.Message;
            }


            return JsonConvert.SerializeObject(dataModel);
        }

        #endregion

        #region 定责指标
        public ActionResult GetDZView()
        {
            WorkOrderMyModel model = new WorkOrderMyModel();

            return View(model);
        }
        /// <summary>
        /// 获取工单信息
        /// </summary>
        /// <param name="page">当前页码</param>
        /// <param name="limit">每页数据量</param>
        /// <param name="sqlWhere">查询条件</param>
        /// <returns></returns>
        [ActionName("GetDZData")]
        [HttpGet]
        public string GetDZData(DateTime? NowDateTime, string dateParty)
        {
            //数据结果集
            ResponseData dataModel = new ResponseData();

            try
            {
                string startDate = "";
                string endDate = "";
                if (!string.IsNullOrEmpty(dateParty))
                {
                    startDate = dateParty.Substring(0, 10);
                    endDate = dateParty.Substring(12);

                }
                else
                {
                    startDate = endDate = DateTime.Now.ToString("yyyy-MM-dd");
                }
                DataTable newTable = new DataTable();
                newTable.Columns.Add("一级分类");
                newTable.Columns.Add("一级数量");
                newTable.Columns.Add("一级占比");
                newTable.Columns.Add("二级分类");
                newTable.Columns.Add("二级数量");
                newTable.Columns.Add("二级占比");
                newTable.Columns.Add("三级分类");
                newTable.Columns.Add("三级数量");
                newTable.Columns.Add("三级占比");

                DataRow dataRow;

                #region 统计
                DataTable  datas7 = busReport.GetOtherData7(startDate, endDate);
                DataTable  datas8 = busReport.GetOtherData8(startDate, endDate);
                DataTable  datas9 = busReport.GetOtherData9(startDate, endDate);

                int totle7 = 0;
                int totle8 = 0;
                int totle9 = 0;

                #region 计算总数 百分比
                if (datas7 != null && datas7.Rows.Count > 0)
                {
                    foreach (DataRow dr in datas7.Rows)
                    {
                        totle7 += dr["num"].ToInt32();
                    }                    
                }
                if (datas8 != null && datas8.Rows.Count > 0)
                {
                    foreach (DataRow dr in datas8.Rows)
                    {
                        totle8 += dr["num"].ToInt32();
                    }
                }
                if (datas9 != null && datas9.Rows.Count > 0)
                {
                    foreach (DataRow dr in datas9.Rows)
                    {
                        totle9 += dr["num"].ToInt32();
                    }
                }
                #endregion

                for (int i = 0; i < 10; i++)
                {
                    dataRow = newTable.NewRow();
                    #region 一级分类
                    if (totle7 > 0&&datas7.Rows.Count>i)
                    {
                        dataRow["一级分类"] = datas7.Rows[i]["name"];
                        int tempn = datas7.Rows[i]["num"].ToInt32();
                        dataRow["一级数量"] = tempn;
                        dataRow["一级占比"] = (tempn/ (double)totle7).ToString("0.00%");
                    }
                    else
                    {
                        dataRow["一级分类"] = "-";
                        dataRow["一级数量"] = "-";
                        dataRow["一级占比"] = "-";
                    }
                    #endregion
                    #region 二级分类
                    if (totle8 > 0 && datas8.Rows.Count > i)
                    {
                        dataRow["二级分类"] = datas8.Rows[i]["name"];
                        int tempn = datas8.Rows[i]["num"].ToInt32();
                        dataRow["二级数量"] = tempn;
                        dataRow["二级占比"] = (tempn / (double)totle8).ToString("0.00%");
                    }
                    else
                    {
                        dataRow["二级分类"] = "-";
                        dataRow["二级数量"] = "-";
                        dataRow["二级占比"] = "-";
                    }
                    #endregion
                    #region 三级分类
                    if (totle9 > 0 && datas9.Rows.Count > i)
                    {
                        dataRow["三级分类"] = datas9.Rows[i]["name"];
                        int tempn = datas9.Rows[i]["num"].ToInt32();
                        dataRow["三级数量"] = tempn;
                        dataRow["三级占比"] = (tempn / (double)totle9).ToString("0.00%");
                    }
                    else
                    {
                        dataRow["三级分类"] = "-";
                        dataRow["三级数量"] = "-";
                        dataRow["三级占比"] = "-";
                    }
                    #endregion
                    newTable.Rows.Add(dataRow);
                }
                #endregion

                dataModel.code = 0;
                dataModel.data = newTable;

            }
            catch (Exception ex)
            {
                dataModel.code = 200;
                dataModel.msg = ex.Message;
            }


            return JsonConvert.SerializeObject(dataModel);
        }


        [AcceptVerbs(HttpVerbs.Post)]
        public string GetDZDataExcel(DateTime? NowDateTime, string dateParty)
        {
            //数据结果集
            ResponseData dataModel = new ResponseData();

            try
            {
                string startDate = "";
                string endDate = "";
                if (!string.IsNullOrEmpty(dateParty))
                {
                    startDate = dateParty.Substring(0, 10);
                    endDate = dateParty.Substring(12);

                }
                else
                {
                    startDate = endDate = DateTime.Now.ToString("yyyy-MM-dd");
                }
                DataTable newTable = new DataTable();
                newTable.Columns.Add("一级分类");
                newTable.Columns.Add("一级数量");
                newTable.Columns.Add("一级占比");
                newTable.Columns.Add("二级分类");
                newTable.Columns.Add("二级数量");
                newTable.Columns.Add("二级占比");
                newTable.Columns.Add("三级分类");
                newTable.Columns.Add("三级数量");
                newTable.Columns.Add("三级占比");

                DataRow dataRow;

                #region 统计
                DataTable datas7 = busReport.GetOtherData7(startDate, endDate);
                DataTable datas8 = busReport.GetOtherData8(startDate, endDate);
                DataTable datas9 = busReport.GetOtherData9(startDate, endDate);

                int totle7 = 0;
                int totle8 = 0;
                int totle9 = 0;

                #region 计算总数 百分比
                if (datas7 != null && datas7.Rows.Count > 0)
                {
                    foreach (DataRow dr in datas7.Rows)
                    {
                        totle7 += dr["num"].ToInt32();
                    }
                }
                if (datas8 != null && datas8.Rows.Count > 0)
                {
                    foreach (DataRow dr in datas8.Rows)
                    {
                        totle8 += dr["num"].ToInt32();
                    }
                }
                if (datas9 != null && datas9.Rows.Count > 0)
                {
                    foreach (DataRow dr in datas9.Rows)
                    {
                        totle9 += dr["num"].ToInt32();
                    }
                }
                #endregion

                for (int i = 0; i < 10; i++)
                {
                    dataRow = newTable.NewRow();
                    #region 一级分类
                    if (totle7 > 0 && datas7.Rows.Count > i)
                    {
                        dataRow["一级分类"] = datas7.Rows[i]["name"];
                        int tempn = datas7.Rows[i]["num"].ToInt32();
                        dataRow["一级数量"] = tempn;
                        dataRow["一级占比"] = (tempn / (double)totle7).ToString("0.00%");
                    }
                    else
                    {
                        dataRow["一级分类"] = "-";
                        dataRow["一级数量"] = "-";
                        dataRow["一级占比"] = "-";
                    }
                    #endregion
                    #region 二级分类
                    if (totle8 > 0 && datas8.Rows.Count > i)
                    {
                        dataRow["二级分类"] = datas8.Rows[i]["name"];
                        int tempn = datas8.Rows[i]["num"].ToInt32();
                        dataRow["二级数量"] = tempn;
                        dataRow["二级占比"] = (tempn / (double)totle8).ToString("0.00%");
                    }
                    else
                    {
                        dataRow["二级分类"] = "-";
                        dataRow["二级数量"] = "-";
                        dataRow["二级占比"] = "-";
                    }
                    #endregion
                    #region 三级分类
                    if (totle9 > 0 && datas9.Rows.Count > i)
                    {
                        dataRow["三级分类"] = datas9.Rows[i]["name"];
                        int tempn = datas9.Rows[i]["num"].ToInt32();
                        dataRow["三级数量"] = tempn;
                        dataRow["三级占比"] = (tempn / (double)totle9).ToString("0.00%");
                    }
                    else
                    {
                        dataRow["三级分类"] = "-";
                        dataRow["三级数量"] = "-";
                        dataRow["三级占比"] = "-";
                    }
                    #endregion
                    newTable.Rows.Add(dataRow);
                }
                #endregion

                dataModel.code = 0;
                dataModel.data = newTable;

            }
            catch (Exception ex)
            {
                dataModel.code = 200;
                dataModel.msg = ex.Message;
            }

            ResponseDataModel NewData = new ResponseDataModel();

            ExcelReadWrite erw = new ExcelReadWrite();
            byte[] Filebyte = erw.GetExcelByte(dataModel.data, "sheet1", true);

            int officeVersion = erw.OfficeType();

            if (officeVersion == 1 || officeVersion == 2)
            {
                NewData.Message = DateTime.Now.ToString("yyyy-MM-dd") + "定责分类统计汇总表.xls";
            }
            else
            {
                NewData.Message = DateTime.Now.ToString("yyyy-MM-dd") + "定责分类统计汇总表.xlsx";
            }
            NewData.Code = "0";
            NewData.Data = JsonConvert.SerializeObject(Filebyte);

            return JsonConvert.SerializeObject(NewData);
        }

        #endregion

    }


}