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;
using YTSoft.DBUtility;

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


        #region  风险等级报表
        /// <summary>
        /// 饼图
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <returns></returns>
        [HttpGet]
        public string GetPieChart(string stime, string etime)
        {
            string levelsql = String.Format("  select l.level,ISNULL(c.cnt,0) count from (  select '一级' level  union select '二级' level  union select '三级' level  ) l left join(  select F_Level, Count(1) cnt from T_Wo_WorkOrderBase where F_CREATEDATE >='{0}' and F_CREATEDATE <='{1}' group by F_Level  ) c on l.level = c.F_Level  ", stime, etime);
            var dataModel = DbHelperSQL.Query(levelsql).Tables[0];
            return JsonConvert.SerializeObject(dataModel);
        }

        /// <summary>
        /// 折线图，时间范围是一个月以内，x坐标显示日，大于一个月 x坐标是月；时间范围不能大于1年
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <returns></returns>
        [HttpGet]
        public object GetLineChart(string stime, string etime)
        {
            DateTime starttime = Convert.ToDateTime(stime);
            DateTime endtime = Convert.ToDateTime(etime);
            TimeSpan ts = endtime.Subtract(starttime);
            string groupby = "";
            string orderby = "";
            List<string> datelist = new List<string>();

            if (ts.Days > 365)
            {
                //var obj = new
                //{
                //    status = "error",
                //    text = "日期间隔不能大于1年"
                //};
                return Error("日期间隔不能大于1年");
            }
            else if (ts.Days <= 31)
            {
                groupby = " group by  convert(varchar(10), F_CREATEDATE,120)";
                orderby = "order by convert(varchar(10), F_CREATEDATE,120) asc";
                string yijisql = " select count(1) cnt,convert(varchar(10), F_CREATEDATE,120) currentday from T_Wo_WorkOrderBase where F_CREATEDATE>'" + stime + "' and F_CREATEDATE<='" + etime + "' and F_Level='一级'" + groupby + orderby;
                DataTable yijidt = DbHelperSQL.Query(yijisql).Tables[0];
                for (DateTime i = starttime; i < endtime; i = i.AddDays(1))
                {
                    datelist.Add(i.ToString("yyyy-MM-dd"));

                }

                for (DateTime i = starttime; i < endtime; i = i.AddDays(1))
                {

                    var dr = yijidt.Select("currentday='" + i.ToString("yyyy-MM-dd") + "'");
                    if (dr == null || dr.Count() == 0)
                    {
                        var newdr = yijidt.NewRow();
                        newdr["cnt"] = 0;
                        newdr["currentday"] = i.ToString("yyyy-MM-dd");
                        yijidt.Rows.Add(newdr);
                    }
                }

                string erjisql = " select count(1) cnt,convert(varchar(10), F_CREATEDATE,120) currentday from T_Wo_WorkOrderBase where F_CREATEDATE>'" + stime + "' and F_CREATEDATE<='" + etime + "' and F_Level='二级'" + groupby + orderby;
                DataTable erjidt = DbHelperSQL.Query(erjisql).Tables[0];

                for (DateTime i = starttime; i < endtime; i = i.AddDays(1))
                {
                    var dr = erjidt.Select("currentday='" + i.ToString("yyyy-MM-dd") + "'");
                    if (dr == null || dr.Count() == 0)
                    {
                        var newdr = erjidt.NewRow();
                        newdr["cnt"] = 0;
                        newdr["currentday"] = i.ToString("yyyy-MM-dd");
                        erjidt.Rows.Add(newdr);
                    }
                }

                string sanjisql = " select count(1) cnt,convert(varchar(10), F_CREATEDATE,120) currentday from T_Wo_WorkOrderBase where F_CREATEDATE>'" + stime + "' and F_CREATEDATE<='" + etime + "' and F_Level='三级'" + groupby + orderby;
                DataTable sanjidt = DbHelperSQL.Query(sanjisql).Tables[0];

                for (DateTime i = starttime; i < endtime; i = i.AddDays(1))
                {
                    var dr = sanjidt.Select("currentday='" + i.ToString("yyyy-MM-dd") + "'");
                    if (dr == null || dr.Count() == 0)
                    {
                        var newdr = sanjidt.NewRow();
                        newdr["cnt"] = 0;
                        newdr["currentday"] = i.ToString("yyyy-MM-dd");
                        sanjidt.Rows.Add(newdr);
                    }
                }

                yijidt.DefaultView.Sort = " currentday asc";
                var yiji = yijidt.DefaultView.ToTable();

                erjidt.DefaultView.Sort = " currentday asc";
                var erji = erjidt.DefaultView.ToTable();

                sanjidt.DefaultView.Sort = " currentday asc";
                var sanji = sanjidt.DefaultView.ToTable();

                var obj = new
                {

                    yiji = yiji.AsEnumerable().Select(v => v.Field<object>("cnt")).ToArray(),
                    erji = erji.AsEnumerable().Select(v => v.Field<object>("cnt")).ToArray(),
                    sanji = sanji.AsEnumerable().Select(v => v.Field<object>("cnt")).ToArray(),
                    datelist = datelist
                };

                return Success("", obj);
            }
            else
            {
                groupby = " group by convert(varchar(7), F_CREATEDATE,120)";
                orderby = " order by convert(varchar(7), F_CREATEDATE,120) asc";

                endtime = endtime.AddDays(1 - endtime.Day).AddMonths(1).AddDays(-1);
                string yijisql = " select count(1) cnt,convert(varchar(7), F_CREATEDATE,120) currentday from T_Wo_WorkOrderBase where F_CREATEDATE>'" + stime + "' and F_CREATEDATE<='" + etime + "' and F_Level='一级'" + groupby + orderby;
                DataTable yijidt = DbHelperSQL.Query(yijisql).Tables[0];
                for (DateTime i = starttime; i <= endtime; i = i.AddMonths(1))
                {
                    datelist.Add(i.ToString("yyyy-MM"));
                }
                for (DateTime i = starttime; i <= endtime; i = i.AddMonths(1))
                {

                    var dr = yijidt.Select("currentday='" + i.ToString("yyyy-MM") + "'");
                    if (dr == null || dr.Count() == 0)
                    {
                        var newdr = yijidt.NewRow();
                        newdr["cnt"] = 0;
                        newdr["currentday"] = i.ToString("yyyy-MM");
                        yijidt.Rows.Add(newdr);
                    }
                }

                string erjisql = " select count(1) cnt,convert(varchar(7), F_CREATEDATE,120) currentday from T_Wo_WorkOrderBase where F_CREATEDATE>'" + stime + "' and F_CREATEDATE<='" + etime + "' and F_Level='二级'" + groupby + orderby;
                DataTable erjidt = DbHelperSQL.Query(erjisql).Tables[0];

                for (DateTime i = starttime; i < endtime; i = i.AddDays(1))
                {
                    var dr = erjidt.Select("currentday='" + i.ToString("yyyy-MM") + "'");
                    if (dr == null || dr.Count() == 0)
                    {
                        var newdr = erjidt.NewRow();
                        newdr["cnt"] = 0;
                        newdr["currentday"] = i.ToString("yyyy-MM");
                        erjidt.Rows.Add(newdr);
                    }
                }

                string sanjisql = " select count(1) cnt,convert(varchar(7), F_CREATEDATE,120) currentday from T_Wo_WorkOrderBase where F_CREATEDATE>'" + stime + "' and F_CREATEDATE<='" + etime + "' and F_Level='三级'" + groupby + orderby;
                DataTable sanjidt = DbHelperSQL.Query(sanjisql).Tables[0];

                for (DateTime i = starttime; i < endtime; i = i.AddDays(1))
                {
                    var dr = sanjidt.Select("currentday='" + i.ToString("yyyy-MM") + "'");
                    if (dr == null || dr.Count() == 0)
                    {
                        var newdr = sanjidt.NewRow();
                        newdr["cnt"] = 0;
                        newdr["currentday"] = i.ToString("yyyy-MM");
                        sanjidt.Rows.Add(newdr);
                    }
                }

                yijidt.DefaultView.Sort = " currentday asc";
                var yiji = yijidt.DefaultView.ToTable();

                erjidt.DefaultView.Sort = " currentday asc";
                var erji = erjidt.DefaultView.ToTable();

                sanjidt.DefaultView.Sort = " currentday asc";
                var sanji = sanjidt.DefaultView.ToTable();



                var obj = new
                {

                    yiji = yiji.AsEnumerable().Select(v => v.Field<object>("cnt")).ToArray(),
                    erji = erji.AsEnumerable().Select(v => v.Field<object>("cnt")).ToArray(),
                    sanji = sanji.AsEnumerable().Select(v => v.Field<object>("cnt")).ToArray(),
                    datelist = datelist
                };

                return Success("", obj)
                    ;
            }


        }

        [HttpGet]
        public object GetTableList(string stime, string etime, string level)
        {
            string sqlwhere = "";
            if (string.IsNullOrEmpty(stime))
            {
                stime = DateTime.Now.AddDays(-30).ToString("yyyyMMdd") + " 00:00:00";
            }
            if (string.IsNullOrEmpty(etime))
            {
                etime = DateTime.Now.ToString("yyyyMMdd") + " 23:59:59";
            }
            sqlwhere += " and F_createdate>='" + stime + "' and F_Createdate <='" + etime + "'";
            if (!string.IsNullOrEmpty(level))
            {
                sqlwhere += " and F_level='" + level + "'";
            }
            string sql = " select F_LabelId ,F_level,F_labelName,count(1) cnt from T_Wo_WorkOrderBase  where F_LabelId>0  and F_level!=''  and  F_level !='自动'" + sqlwhere + " group by F_LabelId,F_labelName,F_level order by F_LabelId";
            var dt = DbHelperSQL.Query(sql).Tables[0];

            dt.Columns.Add("allpercent", typeof(string));// 总占比

            dt.Columns.Add("percent", typeof(string));// 占比
            dt.Columns.Add("total", typeof(string));
            if (dt != null && dt.Rows.Count > 0)
            {

                foreach (DataRow item in dt.Rows)
                {

                    int sum = dt.Select("F_LabelId='" + Convert.ToInt32(item["F_LabelId"].ToString()) + "'").Sum(x => x.Field<int>("cnt"));
                    item["total"] = sum;

                    item["percent"] = string.Format("{0:f2}%", (Convert.ToDecimal(item["cnt"].ToString()) / sum) * 100);

                    int allcnt = dt.Select("cnt >0").Sum(x => x.Field<int>("cnt"));

                    item["allpercent"] = string.Format("{0:f2}%", (Convert.ToDecimal(sum) / allcnt) * 100);


                }
                return Success("数据成功", dt);

            }
            else
            {
                return Success("没有数据");
            }
        }


    

        #endregion



    }


}