ShuangHui_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/report/BusinessCont...

using CallCenter.Utility;
using CallCenterApi.DB;
using CallCenterApi.Interface.Controllers.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace CallCenterApi.Interface.Controllers.report
{

  //  [Authority]
    public class BusinessController : BaseController
    {
        public ActionResult ExitUserCode(int deptid)
        {
            Model.T_Sys_Department deptModel = new BLL.T_Sys_Department().GetModel(deptid);
            string sql = "";
            if (deptModel != null)
            {
                sql += " and F_DeptId in ( select F_DeptId from T_Sys_Department where F_DeptCode like '" + deptModel.F_DeptCode + "%') ";
            }
            DataTable dt = new DataTable();
            DataTable depts = new DataTable();
            int recordCount = 0;
            depts = BLL.PagerBLL.GetListPager(
                                       "T_Sys_UserAccount",
                                       "F_UserId",
                                       "*",
                                       sql,
                                       "",
                                       1000000,
                                       1,
                                       true,
                                        out recordCount
                                      );
            List<Model.UserAccount> modelList = new BLL.UserAccount().DataTableToList(depts);
            #region 
            dt.Columns.Add("姓名");
            dt.Columns.Add("账号");
            dt.Columns.Add("密码");
            dt.Columns.Add("手机");
            dt.Columns.Add("角色");
            dt.Columns.Add("部门");
            dt.Columns.Add("职位");
            #endregion
            foreach (var it in modelList)
            {
                DataRow drNew = dt.NewRow();
                #region 基本字段
                drNew["姓名"] = it.F_UserName;//序号
                drNew["账号"] = it.F_UserCode;//姓名
                drNew["密码"] = it.F_Password;//地址
                drNew["手机"] = it.F_Mobile;
                drNew["角色"] = it.F_RoleName;
                drNew["部门"] = it.F_DeptName;
                drNew["职位"] = it.F_Post;
                ;
                dt.Rows.Add(drNew);
                #endregion
            }
            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel(deptModel.F_DeptName + "大区人员表", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");

            }
        }
        public ActionResult AreaComplaint(string stime="", string endtime = "", int isdc=0)
        {
          List <Complaint>   complaintlist = new List<Complaint>();
            DateTime strattime,enddatetime ;
            string where = "F_IsDelete = 0 and F_Area is not null and (F_Type=2 or (F_Duplicate in(2,0)and F_Type=3))";
            if (stime=="")
            {
                strattime = DateTime.Now.AddDays(-6);
                where += " and datediff(day,F_CreateTime,'" + strattime.ToString("yyyy-MM-dd") + "')<=0 ";
            }
            else
            {
                strattime = DateTime.Parse(stime);
                where += " and datediff(day,F_CreateTime,'" + stime + "')<=0 ";
            }
            if (endtime=="")
            {
                enddatetime = DateTime.Now;
                where += " and datediff(day,F_CreateTime,'" + DateTime.Now.ToString("yyyy-MM-dd") + "')>=0 ";
            }
            else
            {
                enddatetime = DateTime.Parse(endtime);
                where += " and datediff(day,F_CreateTime,'" + endtime + "')>=0 ";
            }
            var workoeder = new BLL.T_Bus_WorkOrder().GetModelList(where);
            var Area = new BLL.T_Sys_Department().GetModelList("isnull(F_ParentId, 1) = 1 and F_State = 1 and F_Layer = 1");
            float max = 0; float min = 0;
            string maxarea = "", minarea="";
            Complaint complaints = new Complaint();
            complaints.serialnumbe = 20;
            complaints.area = "合计";
            float Distributionrate = 0;
            float  documentary = 0;
            if (workoeder!=null )
            {
                for (int i=0;i < Area.Count;i ++ )
                {
                    Complaint complaint = new Complaint();
                    complaint.serialnumbe = i + 1;//序号
                    complaint.area = Area[i].F_DeptName;//大区
                    var group = workoeder.Where(p => p.F_Area == Area[i].F_DeptId.ToString()) ;//投诉总量
                    complaint.complaintnumber = group.Count();
                 

                    complaint.assignmentnumber= group.Where(p => p.F_State  ==1 || p.F_State == 5).Count ();//待分派
                    complaints.assignmentnumber += complaint.assignmentnumber;
                    float a = 0;int c = 0;int assignment=0;
                    for (DateTime dt = strattime; dt <= enddatetime; dt = dt.AddDays (1))
                      {
                        // c++;
                        assignment += group.Where(p => (p.F_TobeallocatedTime < dt.AddDays(1) && p.F_TobeallocatedTime >= dt
                         && p.F_TransferTime < dt.AddDays(1) && p.F_TransferTime >= dt
                               )
                            ).Count();
                  
                        //    var daycount= group.Where(p => (p.F_TransferTime < dt.AddDays (1)&&( p .F_TransferTime >= dt || p.F_AssignmenttIime==null) )
                        //    ).Count();
                        //
                        //   var today= group.Where(p => p.F_TransferTime >= dt && p.F_TransferTime < dt.AddDays(1)
                        //   ).Count();

                        //  if (daycount > 0)
                        //  a += (float)today / daycount;//当日分派率
                        //   else
                        //   a += 1;
                    }
                    complaint.assignmentrate = string.Format("{0:f2}%",(float ) assignment / complaint.complaintnumber * 100);
                    Distributionrate += assignment;
                    complaint.pendingnumber = group.Where(p => p.F_State == 3).Count();//待处理
                    complaints.pendingnumber += complaint.pendingnumber;
                    complaint.processingnumber = group.Where(p => p.F_State == 4).Count();//处理中
                    if (complaint.complaintnumber > 0)
                        complaint.processingnumberrate = string.Format("{0:f2}%", complaint.processingnumber /(float ) complaint.complaintnumber * 100);//
                    else
                        complaint.processingnumberrate = "100%";

                    complaints.processingnumber += complaint.processingnumber;
                    complaint.processednumber = group.Where(p => p.F_State == 10).Count();//已处理
                 
                    complaints.processednumber += complaint.processednumber;
                    float d = 0;
                    if (complaint.complaintnumber > 0)
                    {
                        d = complaint.processednumber / (float)complaint.complaintnumber;
                        complaint.processedrate = string.Format("{0:f2}%", complaint.processednumber / (float)complaint.complaintnumber * 100);//处理率
                    }
                    else
                    {
                        d = 1;
                        complaint.processedrate = "100%";
                    }
                    if (i == 0)
                    {
                        min = d;
                        max = d;
                        maxarea = Area[i].F_DeptName;
                        minarea = Area[i].F_DeptName;
                    }
                    else
                    {
                        if (d > max)
                        {
                            max = d;
                            maxarea = Area[i].F_DeptName ;
                        }
                        if (d < min)
                        {
                            min = d;
                            minarea = Area[i].F_DeptName;
                        }
                    }

                    int reminder= group.Where(p => p.F_DealType == 1 ).Count();//催单数量
                    documentary += reminder;
                    int perfect= group.Where(p => p.F_DealState == 1).Count();//完善数量
                    documentary += perfect;
                    int total = reminder + perfect;
                    if (group.Count()>0)
                        complaint.documentaryrate = string.Format("{0:f2}%", total / (float)group.Count() * 100);//跟单率
                    else
                        complaint.documentaryrate = "100%";
                    complaintlist.Add(complaint);
                }
                complaints.complaintnumber = workoeder.Count();
                if (workoeder.Count() > 0)
                complaints.assignmentrate = string.Format("{0:f2}%", Distributionrate / workoeder.Count() * 100); //当日分派率
                else
                    complaints.assignmentrate = "100%";
                if (complaints.complaintnumber > 0)
                    complaints.processedrate = string.Format("{0:f2}%", complaints.processednumber/(float )complaints.complaintnumber * 100);//处理率
                else
                    complaints.processedrate = "100%";
                if (complaints.complaintnumber > 0)
                    complaints.documentaryrate = string.Format("{0:f2}%", documentary / complaints.complaintnumber * 100);//跟单率
                else
                    complaints.documentaryrate = "100%";
                if (complaints.complaintnumber > 0)
                    complaints.processingnumberrate = string.Format("{0:f2}%", complaints.processingnumber / (float)complaints.complaintnumber * 100);//
                else
                    complaints.processingnumberrate = "100%";
                complaintlist.Add(complaints);
               
                if (isdc>0)
                {
                    NPOIHelper npoi = new NPOIHelper();
                    if (npoi.Complainthandling(strattime, enddatetime, complaintlist, maxarea,minarea ) == "")
                    {
                        return Success("导出成功");
                    }
                    else
                    {
                        return Error("导出失败");
                    }
                }
                else
                {
                    var obj = new
                    {
                        complaintlist,
                        maxarea,
                        minarea,
                    };
                    return Success("获取成功", obj.ToJson ());
                }
                
            }
            else
            {
                return Success("暂无数据");
            }

        }


        public ActionResult Afterprogress(string stime = "", string endtime = "", int isdc = 0)
        {
            List<Aftersale> aftersalelist = new List<Aftersale>();
            DateTime strattime, enddatetime;
            string where = "F_IsDelete = 0 and F_Area is not null and (F_Type=2 or (F_Duplicate in(2,0)and F_Type=3))";
            if (stime == "")
            {
                strattime =DateTime .Parse ( DateTime.Now.AddMonths (-1).ToString ("yyyy-MM-28"));
                where += " and datediff(day,F_CreateTime,'" + strattime.ToString("yyyy-MM-dd") + "')<=0 ";
            }
            else
            {
                strattime = DateTime.Parse(stime);
                where += " and datediff(day,F_CreateTime,'" + stime + "')<=0 ";
            }
            if (endtime == "")
            {
                enddatetime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-27"));
                where += " and datediff(day,F_CreateTime,'" + DateTime.Now.ToString("yyyy-MM-dd") + "')>=0 ";
            }
            else
            {
                enddatetime = DateTime.Parse(endtime);
                where += " and datediff(day,F_CreateTime,'" + endtime + "')>=0 ";
            }
            var workoeder = new BLL.T_Bus_WorkOrder().GetModelList(where);
            var Area = new BLL.T_Sys_Department().GetModelList("isnull(F_ParentId, 1) = 1 and F_State = 1 and F_Layer = 1");
            float max = 0; float min = 0;
            string maxarea = "", minarea = "";
            int Tobeassignment = 0;
            Aftersale complaints = new Aftersale();
            complaints.serialnumbe = "合计";
            complaints.receiving = 0;
            complaints.assignmentnumber = 0;
            complaints.area = "";
         
            if (workoeder != null)
            {
                Tobeassignment = workoeder.Where(x => x.F_State == 1 || x.F_State == 5).Count();
                for (int i = 0; i < Area.Count; i++)
                {
                    Aftersale aftersale = new Aftersale();
                    aftersale.serialnumbe = i + 1+"";//序号
                    aftersale.area = Area[i].F_DeptName;//大区
                    var group = workoeder.Where(p => p.F_Area == Area[i].F_DeptId.ToString());//投诉总量
                    aftersale.total = group.Count();
                    float a = 0; float b = 0; int assignment=0, receivingorders=0;
                    for (DateTime dt = strattime; dt <= enddatetime; dt = dt.AddDays(1))
                    {
                         assignment+= group.Where(p => (p.F_TobeallocatedTime < dt.AddDays(1) && p.F_TobeallocatedTime >= dt
                         && p.F_TransferTime < dt.AddDays(1) && p.F_TransferTime >= dt
                               )
                             ).Count();
                        receivingorders += group.Where(p => (p.F_TransferTime < dt.AddDays(1) && p.F_TransferTime >= dt
                 && p.F_Receiving < dt.AddDays(1) && p.F_Receiving >= dt
                       )
                    ).Count();
                    }
                    complaints.receiving += receivingorders;//接单条数
                    aftersale.receiving = receivingorders;//接单条数
                    complaints.assignmentnumber += assignment;
                    aftersale.assignmentnumber = assignment;//分派投诉
                    if (aftersale.total > 0)
                        aftersale.assignmentrate = string.Format("{0:f2}%", (float)assignment / aftersale.total * 100);
                    else
                        aftersale.assignmentrate = "100%";
                    if (aftersale.total > 0)
                        aftersale.receivingrate = string.Format("{0:f2}%", (float)receivingorders / aftersale.total * 100);//接单率
                    else
                        aftersale.receivingrate = "100%";
                    aftersale.processingnumber = group.Where( p => p.F_State == 4).Count();//处理中投诉
                    aftersale.processednumber = group.Where( p => p.F_State == 10).Count();//已处理投诉
                    float d = 0;
                    if (aftersale.total > 0)
                    {
                        d = (float)aftersale.processednumber / aftersale.total;
                        aftersale.processed = d;
                        aftersale.processedrate = string.Format("{0:f2}%", (float)aftersale.processednumber / aftersale.total * 100);//完结率
                    }
                    else
                    {
                        d = 1;
                        aftersale.processed = 1;
                        aftersale.processedrate = "100%";
                    }
                    if (i == 0)
                    {
                        min = d;
                        max = d;
                        maxarea = Area[i].F_DeptName;
                        minarea = Area[i].F_DeptName;
                    }
                    else
                    {
                        if (d > max)
                        {
                            max = d;
                            maxarea = Area[i].F_DeptName;
                        }
                        if (d < min)
                        {
                            min = d;
                            minarea = Area[i].F_DeptName;
                        }
                    }
                    int reminder = group.Where(p => p.F_DealType == 1).Count();//催单数量
                    if (aftersale.total > 0)
                        aftersale.reminderrate = string.Format("{0:f2}%", (float)reminder / aftersale.total * 100);//
                    else
                        aftersale.reminderrate = "100%";
                    aftersale.name = User(Area[i].F_DeptName);
                    aftersalelist.Add(aftersale);
                }
                complaints.total = workoeder.Count();
                if (complaints.total > 0)
                    complaints.assignmentrate = string.Format("{0:f2}%",(float ) complaints.assignmentnumber / complaints.total * 100); //当日分派率
                else
                    complaints.assignmentrate = "100%";

                if (complaints.total > 0)
                    complaints.receivingrate = string.Format("{0:f2}%", (float)complaints.receiving / complaints.total * 100); //当日分派率
                else
                    complaints.receivingrate = "100%";
                complaints.processingnumber = workoeder.Where(p => p.F_State == 4).Count();//处理中投诉
                complaints.processednumber = workoeder.Where(p => p.F_State == 10).Count();//已处理投诉
                if (complaints.total > 0)
                    complaints.processedrate = string.Format("{0:f2}%", (float)complaints.processednumber / complaints.total * 100);//完结率
                else
                    complaints.processedrate = "100%";
                int remind= workoeder.Where(p => p.F_DealType == 1).Count();//催单数量
                if (complaints.total > 0)
                    complaints.reminderrate = string.Format("{0:f2}%", (float)remind / complaints.total * 100);//
                else
                    complaints.reminderrate = "100%";
                complaints.name = "";
                int pendingnumber = workoeder.Where(p => p.F_State == 3).Count();//
                aftersalelist = aftersalelist.OrderByDescending(x => x.processed).ToList ();
                aftersalelist.Add(complaints);
                for (int i=0;i < aftersalelist.Count-1;i++)
                {
                    aftersalelist[i].serialnumbe = i+1+"";
                }
                if (isdc > 0)
                {
                    NPOIHelper npoi = new NPOIHelper();
                    if (npoi.AftersaleToExcel(strattime, enddatetime, aftersalelist, maxarea, minarea, pendingnumber, Tobeassignment) == "")
                    {
                        return Success("导出成功");
                    }
                    else
                    {
                        return Error("导出失败");
                    }
                }
                else
                {
                    var obj = new
                    {
                        aftersalelist,
                        maxarea,
                        minarea,
                        pendingnumber
                    };
                    return Success("获取成功", obj.ToJson ()
                        );
                }
                   
            }
            else
            {
                return Success("暂无数据");
            }

        }
       
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private string User(string DeptName )
        {
            string user="";
             if (DeptName== "湖南" || DeptName == "豫南" || DeptName == "豫北" || DeptName == "吉林" || DeptName == "闽赣" || DeptName == "综合")
            {
                var usermodel = new BLL.T_Sys_UserAccount().GetModel("8004");
                if (usermodel != null)
                    user = usermodel.F_UserName;

            }
            //else if (DeptName == "鲁西" || DeptName == "华东" || DeptName == "鲁东")
            //{
            //    var usermodel = new BLL.T_Sys_UserAccount().GetModel("8010");
            //    if (usermodel != null)
            //        user = usermodel.F_UserName;
            //}
            else if ( DeptName == "京津" || DeptName == "华东" || DeptName == "广东" || DeptName == "川渝" || DeptName == "陕宁" || DeptName == "晋蒙" || DeptName == "餐饮" || DeptName == "禽产品" || DeptName == "广海")
            {
                var usermodel = new BLL.T_Sys_UserAccount().GetModel("822353");
                if (usermodel != null)
                    user = usermodel.F_UserName;
            }
            else if (DeptName == "安徽" || DeptName == "湖北" || DeptName == "西北" || DeptName == "云贵" )
            {
                var usermodel = new BLL.T_Sys_UserAccount().GetModel("8012");
                if (usermodel != null)
                    user = usermodel.F_UserName;
            }
            else if ( DeptName == "辽宁" || DeptName == "黑龙江" || DeptName == "河北" || DeptName == "江苏" || DeptName == "鲁东" || DeptName == "鲁西" || DeptName == "生鲜品")
            {
              

                var usermodel = new BLL.T_Sys_UserAccount().GetModel("822249");
                if (usermodel != null)
                    user = usermodel.F_UserName;
            }
            return user;

        }
        /// <summary>
        /// 坐席话务量
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        public ActionResult GetAgentTelCount(string stime, string  endtime)
        {
            string where = " ";
            if (stime == "")
            {
                where += " and datediff(day,BeginTime,'" + DateTime.Now.ToString("yyyy-MM-dd") + "')<=0 ";
            }

            else
            {
              
                where += " and datediff(day,BeginTime,'" + stime + "')<=0 ";
            }
            if (endtime == "")
            {
               
                where += " and datediff(day,BeginTime,'" + DateTime.Now.ToString("yyyy-MM-dd") + "')>=0 ";
            }
            else
            {
                where += " and datediff(day,BeginTime,'" + endtime + "')>=0 ";
            }
          

            DataTable dtTel = DbHelperSQL.Query(" select * from T_Call_CallRecords where 1=1 " + where).Tables[0];

            string sql = "SELECT F_UserCode,F_UserName,F_WorkNumber,F_UserId FROM T_Sys_UserAccount where f_seatFlag=1 and F_DeleteFlag=0";
            DataTable dtUser = DbHelperSQL.Query(sql).Tables[0];

            string[] users = new string[dtUser.Rows.Count];//坐席工号/姓名
            int[] incount = new int[dtUser.Rows.Count];//呼入量
            int[] inconnectcount = new int[dtUser.Rows.Count];//呼入接通量
            int[] outcount = new int[dtUser.Rows.Count];//呼出量
            int[] outconnectcount = new int[dtUser.Rows.Count];//呼出接通量

            for (int i = 0; i < dtUser.Rows.Count; i++)
            {
                //坐席
                users[i] = "(" + dtUser.Rows[i]["F_UserCode"].ToString() + ")" + dtUser.Rows[i]["F_UserName"].ToString();
                //呼入量
                var inlist = dtTel.Select(" CallType=0 and UserCode='" + dtUser.Rows[i]["F_UserCode"].ToString() + "' ");
                incount[i] = inlist.Count();
                //呼入接通量
                var inconnectlist = dtTel.Select(" CallType=0 and CallState=1 and UserCode='" + dtUser.Rows[i]["F_UserCode"].ToString() + "' ");
                inconnectcount[i] = inconnectlist.Count();
                //呼出量
                var outlist = dtTel.Select(" CallType=1 and UserCode='" + dtUser.Rows[i]["F_UserCode"].ToString() + "' ");
                outcount[i] = outlist.Count();
                //呼出接通量
                var outconnectlist = dtTel.Select(" CallType=1 and CallState=1 and UserCode='" + dtUser.Rows[i]["F_UserCode"].ToString() + "' ");
                outconnectcount[i] = outconnectlist.Count();
            }
            var obj = new
            {
                users,
                incount,
                inconnectcount,
                outcount,
                outconnectcount
            };
            return Success("获取坐席话务量", obj);
        }
        public class Telephone
        {
            public List<Proportion> type;//诉求占比占比
            public List<Conversation> conversations ;//24小时来电量，接通量统计
            public List<TotalDate>  dates ;//话务总体数据统计展示
        }
        public class TotalDate
        {
            public string  average;
            public int connection;
            public int traffic;
        }
        public class Conversation
        {
            public int Incomingcall;//来电量
            public int time;
            public int Connect;//接通量
        }
        public class Proportion
        {
            public string name;
            public int total;
            public string  proportion;
        }
        public ActionResult GetTelephoneList(string stime, string endtime)
        {
            string sql = $"  F_IsDelete=0";
            string where = "";
            DataTable dt = new DataTable();
            if (stime == "")
            {
                sql += " and datediff(day,F_CreateTime,'" + DateTime.Now.ToString("yyyy-MM-dd") + "')<=0 ";
                where += "  datediff(day,BeginTime,'" + DateTime.Now.ToString("yyyy-MM-dd") + "')<=0 ";
            }
               
            else
            {
                sql += " and datediff(day,F_CreateTime,'" + stime + "')<=0 ";
                where += "  datediff(day,BeginTime,'" + stime + "')<=0 ";
            }
              
            if (endtime == "")
            {
                sql += " and datediff(day,F_CreateTime,'" + DateTime.Now.ToString("yyyy-MM-dd") + "')>=0   ";
                where += " and datediff(day,BeginTime,'" + DateTime.Now.ToString("yyyy-MM-dd") + "')>=0 ";
            }
            else
            {
                sql += " and datediff(day,F_CreateTime,'" + endtime + "')>=0   ";
                where += " and datediff(day,BeginTime,'" + endtime + "')>=0 ";
            }
               
            var workorder = new BLL.T_Bus_WorkOrder().GetModelList(sql);
            Telephone telephone = new Telephone();
            telephone.type = new List<Proportion>();
            for (int i=0;i <2;i++)
            {
                Proportion proportion = new Proportion();
                if (i == 0)
                    proportion.name = "咨询";
                else
                    proportion.name = "投诉";
                proportion.total = 0;
                proportion.proportion = "0.00%";
                foreach (var it in workorder)
                {
                    if (i == 0 && it.F_Type == 1)
                        proportion.total++;
                    else if (i == 1 && it.F_Type == 2)
                        proportion.total++;
                }
                telephone.type.Add(proportion);
            }
            Model.T_Call_CallRecords t_Call_CallRecords = new Model.T_Call_CallRecords();
            var CallRecords = new BLL.T_Call_CallRecords().GetModelList(where);
            telephone.conversations = new List<Conversation>();
            for (int i=0;i<24;i++)
            {
                Conversation conversation = new Conversation();
                conversation.Connect = 0;
                conversation.Incomingcall = 0;
                conversation. time = i;
                foreach (var it in CallRecords)
                {
                    var time = DateTime.Parse(it.BeginTime.ToString()).Hour;
                   if ( time ==i )
                    {
                        conversation.Incomingcall++;
                        if (it.CallState == 1)
                            conversation.Connect++;
                    }
                }
                telephone.conversations.Add(conversation);
            }
            int t = 0;
            telephone.dates = new List<TotalDate>();
            TotalDate totalDate = new TotalDate();
            totalDate.connection = 0;
            totalDate.traffic = 0;
            foreach (var it in CallRecords)
            {
                if(it.LongTime!=null )
                t +=(int ) it.LongTime;
                if (it.CallState == 1)
                    totalDate.connection++;
                totalDate.traffic++;
            }
            if (CallRecords.Count > 0)
                totalDate.average = string.Format("{0:f2}",
           (double ) t / CallRecords.Count);
            telephone.dates.Add(totalDate);
            return Success("获取成功", telephone);
        }
        public class BigDate
        {
            public List<Realtime> realtime;//当日工单数量实时
            public List<Acceptancetime> acceptancetimes ;//24小时时段受理量/处理量分布图
            public List<Quantity> states ;//工单各状态数量分布
            public List<Quantity> region;//受理区域饼图
        }
        public class Quantity
        {
            public string name;
            public int total;
        }
        public class Acceptancetime
        {
            public int time;
            public int accept;
            public int handle;
        }
        public class Realtime
        {
            public int time;
            public int total;
        }
        /// <summary>
        /// 工单分布情况表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult GetBigdataList(string stime, string endtime)
        {
            string sql = $"  F_IsDelete=0";
            DataTable dt = new DataTable();
            if (stime=="")
                sql += " and datediff(day,F_CreateTime,'" + DateTime.Now.ToString("yyyy-MM-dd") + "')<=0 ";
            else
                sql += " and datediff(day,F_CreateTime,'" + stime + "')<=0 ";
            if (endtime == "")
                sql += " and datediff(day,F_CreateTime,'" + DateTime.Now.ToString("yyyy-MM-dd") + "')>=0   ";
            else
                sql += " and datediff(day,F_CreateTime,'" + endtime + "')>=0   ";
            var workorder = new BLL.T_Bus_WorkOrder().GetModelList(sql);
            BigDate bigDate = new BigDate();
            bigDate.realtime = new List<Realtime>();
            bigDate.acceptancetimes = new List<Acceptancetime>();
            for (int i = 0; i < 24; i++)
            {
                Realtime realtime = new Realtime();
                Acceptancetime acceptancetime = new Acceptancetime();
                realtime.time = i;
                realtime.total = 0;
                acceptancetime.time = i;
                acceptancetime.accept = 0;
                acceptancetime.handle = 0;
                foreach (var it in workorder)
                {
                    var time = DateTime.Parse(it.F_CreateTime.ToString()).Hour;
                    if (time == i)
                    {
                        realtime.total++;
                        acceptancetime.accept++;
                    }
                    if(it .F_State ==4|| it.F_State == 10)
                    {
                        try
                        {
                            var handletime = DateTime.Parse(it.F_DealTime.ToString()).Hour;
                            if (handletime == i)
                            {
                                acceptancetime.handle++;
                            }

                        }
                        catch
                        { }
                       
                    }
                }
                bigDate.realtime.Add(realtime);
                bigDate.acceptancetimes.Add(acceptancetime);
            }
            bigDate.states = new List<Quantity>();
            bigDate.region = new List<Quantity>();
            for (int i=0;i<7;i ++)
            {
                Quantity state = new Quantity();
                state.total = 0;
                foreach (var it in workorder)
                {
                    switch (i)
                    {
                        case 0:
                            state.name = "待提交";
                            if (it.F_State == 0)
                                state.total++;
                            break;
                        case 1:
                            state.name = "待指派";
                            if (it.F_State == 1)
                                state.total++;
                            break;
                        case 2:
                            state.name  = "待处理";
                            if (it.F_State == 3)
                                state.total++;
                            break;
                        case 3:
                            state.name = "处理中";
                            if (it.F_State == 4)
                                state.total++;
                            break;
                        case 4:
                            state.name = "退回待提交";
                            if (it.F_State == 6)
                                state.total++;
                            break;
                        case 5:
                            state.name = "退回待指派";
                            if (it.F_State == 5)
                                state.total++;
                            break;
                        case 6:
                            state.name = "已处理";
                            if (it.F_State == 10)
                                state.total++;
                            break;
                    }
                }
                bigDate.states.Add(state);
            }
            var datement = new BLL.T_Sys_Department().GetModelList("isnull(F_ParentId, 1) = '1' and F_State = 1 and F_Layer = 1 order by F_Sort asc");
            if (datement!=null )
            {
                foreach (var it in datement)
                {
                    Quantity  quantity = new Quantity();
                    quantity.name = it.F_DeptName;
                    quantity.total = 0;
                    foreach (var iv in workorder)
                    {
                        if (iv .F_State >0 && !string .IsNullOrEmpty (iv.F_Area))
                        {
                            try
                            {
                                if (int.Parse(iv.F_Area) == it.F_DeptId)
                                    quantity.total++;
                            }
                            catch
                            {
                            }
                          
                        }
                    }
                    bigDate.region.Add(quantity);
                }
            }

            return Success("获取成功", bigDate);
        }
        /// <summary>
        /// 市场信息简报
        /// </summary>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult MarketInformation(string sdate, string edate,int isdc = 0)
        {
            int userId = CurrentUser.UserData.F_UserId;
            Model.T_Sys_UserAccount userModel = new BLL.T_Sys_UserAccount().GetModel(userId);
            string  stime = DateTime.Now.ToString("yyyy-MM")+ "-01";
            string etime = DateTime.Now.ToString("yyyy-MM-dd"); ;
            string sql = $"  F_IsDelete=0";
            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(sdate))
            {
                stime = sdate;
            }
            if (!string.IsNullOrEmpty(edate))
            {
                etime = edate;
            }
             sql += " and datediff(day,F_CreateTime,'" + stime + "')<=0 ";
              sql += " and datediff(day,F_CreateTime,'" + etime + "')>=0   ";
            var modellist = new BLL.T_Bus_WorkOrder().GetModelList(sql);
            var productcpunt = new BLL.T_Bus_WorkOrder().GetModelList(sql+ "and (F_Type=2 or (F_Duplicate in(2,0)and F_Type=3))") ;
            var CPDM = new BLL.T_Sys_DictionaryValue().GetModelList (" F_DictionaryFlag='" + "CPDM" + "' and F_State=1 ");
            var WTDM = new BLL.T_Sys_DictionaryValue().GetModelList(" F_DictionaryFlag='" + "WTDM" + "' and F_State=1 ");
            Market market = new Market();
            market.product = new List<Ification>();
            market.sediainvolved = new Ification();
           
            Ification product = new Ification();
            product.name = "总计";
            product.number = productcpunt.Count ;
            product.Proportion = "100%";
            market.product.Add(product);
            if (modellist!=null )
            {
               
                if (CPDM != null)
                {
                    foreach (var iv in CPDM)
                    {
                        Ification products = new Ification();
                        products.name = iv.F_Name;
                        products.number = 0;
                        foreach (var it in productcpunt)
                        {
                            if (iv.F_Name == it.F_ProductType)
                                products.number++;
                        }
                        if (productcpunt.Count > 0)
                            products.Proportion = string.Format("{0:f2}%", products.number / productcpunt.Count * 100);
                        else
                            products.Proportion = "0%";
                        market.product.Add(products);
                    }
                 }
                if (WTDM != null)
                 {
                    foreach (var iv in WTDM)
                    {
                        Ification products = new Ification();
                        products.name = iv.F_Name;
                        products.number = 0;
                        foreach (var it in productcpunt)
                        {
                            if (iv.F_Name == it.F_ProblemCode)
                                products.number++;
                        }
                        if (productcpunt.Count > 0)
                            products.Proportion = string.Format("{0:f2}%", products.number / productcpunt.Count * 100);
                        else
                            products.Proportion = "0%";
                        market.product.Add(products);
                    }
                }

             Ification sediainvolved = new Ification();
            foreach (var it in productcpunt)
            {
                if (it.F_Reason == "涉媒")
               sediainvolved.number++;
            }
            sediainvolved.name = "涉媒";
                if (productcpunt.Count > 0)
                    sediainvolved.Proportion = string.Format("{0:f2}%", sediainvolved.number / productcpunt.Count * 100);
                else
                    sediainvolved.Proportion = "0%";
                market.sediainvolved = sediainvolved;

           }
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = market,
            };
            if (isdc>0)
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.MarketExportToExcel(stime, etime, market) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            else 
            return Content(obj.ToJson()); ;

           
        }

        private class Timeslot
        {
            public int onetime { get; set; }
            public int twotime { get; set; }
            public int threetime { get; set; }
            public int stime { get; set; }
            public int etime { get; set; }
        }
        private Timeslot GetTimeslot (int mouth)
        {
            Timeslot timeslot = new Timeslot();
            DateTime date = DateTime.Parse(DateTime.Now.ToString("yyyy") + "-" + mouth + "-01");
            timeslot.onetime = date.AddMonths (-2).Month ;
            timeslot.twotime = date.AddMonths(-1).Month;
            timeslot.threetime = date.Month;
            timeslot.stime = date.AddMonths(-11).Month;
            timeslot.etime = date.AddMonths(-3).Month;
            return timeslot;
        }

        private Date  RetuDate(string name, Timeslot timeslot, List <Model .T_Bus_WorkOrder> modelList)
        {
            Date date = new Date() ;
            date.name = DateTime .Parse (name).ToString ("MM月dd日")  ;
            date.total = 0;
            date.MonthCount1 = 0;
            date.MonthCount1name = timeslot.stime  + "-" + timeslot.etime  + "月份日期";
            date.MonthCountmix1 = "0.00%";
            date.MonthCount2 = 0;
            date.MonthCount2name = timeslot.onetime  + "月份日期";
            date.MonthCountmix2 = "0.00%";
            date.MonthCount3 = 0;
            date.MonthCount3name = timeslot.twotime  + "月份日期";
            date.MonthCountmix3 = "0.00%";
            date.MonthCount4 = 0;
            date.MonthCount4name = timeslot.threetime  + "月份日期";
            date.MonthCountmix4 = "0.00%";
            date.MonthCount5 = 0;
            date.MonthCount5name = "不清楚日期";
            date.MonthCountmix5 = "0.00%";
            for (int j = 0; j < modelList.Count; j++)
            {
                string datetime = DateTime.Parse(modelList[j].F_CreateTime.ToString()).ToString("yyyy-MM-dd");
                if (datetime == name)
                {
                    date.total++;
                    if (!string.IsNullOrEmpty(modelList[j].F_ProductDate))
                    {
                        if (modelList[j].F_ProductDate!="不清")
                        {
                            string result = modelList[j].F_ProductDate.Replace(".", "");
                            var pronos = DateTime.ParseExact(result, "yyyyMMdd", null).Month;
                            if (timeslot.stime > timeslot.etime)
                            {
                                int t = 12 - timeslot.stime;
                                if (pronos >= timeslot.stime && pronos <= 12)
                                    date.MonthCount1++;
                                else if (pronos <= timeslot.etime)
                                    date.MonthCount1++;

                            }
                            else
                            {
                                if (pronos >= timeslot.stime && pronos <= timeslot.etime)
                                    date.MonthCount1++;
                            }
                            if (pronos == timeslot.onetime)
                                date.MonthCount2++;
                            if (pronos == timeslot.twotime)
                                date.MonthCount3++;
                            if (pronos == timeslot.threetime)
                                date.MonthCount4++;
                        }
                        else
                        {
                            date.MonthCount5++;
                        }

                    }
                    else
                    {
                        date.MonthCount5++;
                    }
                }
                
            }
            if (date.total > 0)
            {
                date.MonthCountmix1 = string.Format("{0:f2}%", (float)date.MonthCount1 / date.total*100);
                date.MonthCountmix2 = string.Format("{0:f2}%", (float)date.MonthCount2 / date.total * 100);
                date.MonthCountmix3 = string.Format("{0:f2}%", (float)date.MonthCount3 / date.total * 100);
                date.MonthCountmix4 = string.Format("{0:f2}%", (float)date.MonthCount4 / date.total * 100);
                date.MonthCountmix5 = string.Format("{0:f2}%", (float)date.MonthCount5 / date.total * 100);
            }
            return date;
        }
        /// <summary>
        /// 产品日期分布情况表
        /// </summary>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult Distribution(string sttime  ,string endtime, int isdc = 0)
        {
          //  int userId = CurrentUser.UserData.F_UserId;
         //   Model.T_Sys_UserAccount userModel = new BLL.T_Sys_UserAccount().GetModel(userId);
            string sql = $"  F_IsDelete=0";
            DateTime time=DateTime .Now ;
           
            DataTable dt = new DataTable();
           
            if (!string .IsNullOrEmpty (endtime))
            {
                time =DateTime .Parse (endtime);
            }
            Timeslot timeslot = GetTimeslot(time.Month );
            int days = 30;
            int day = 31;
            Product product = new Product();
            product.dates = new List<Date>();
            //时间筛选
            if (!string.IsNullOrEmpty(sttime))
            {
                DateTime stime = DateTime.Parse(sttime);
                System.TimeSpan ime = time - stime;
                days = (int )ime.TotalDays+1;
                sql += " and F_CreateTime>='" + stime.ToString("yyyy-MM-dd") + " 00:00:00' ";
                sql += " and F_CreateTime<='" + time.ToString("yyyy-MM-dd") + " 23:59:59' ";
                sql += $" and (F_Type=2 or (F_Duplicate in(2,0)and F_Type=3))";
                var modelLis = new BLL.T_Bus_WorkOrder().GetModelList(sql);
                for (int i = 0; i < days; i++)
                {
                    string name = "";
                    name = stime.AddDays(i).ToString("yyyy-MM-dd");
                    product.dates.Add(RetuDate(name, timeslot, modelLis));
                }
            }
            else
            {
                //系统时间
                if (time.Month == 1)
                {
                    days = 31;
                }
                else
                {
                    days = System.Threading.Thread.CurrentThread.CurrentUICulture.Calendar.GetDaysInMonth(time.Year, time.Month - 1);//获取天数
                }
                DateTime lastmonth = time.AddMonths(-1);
                sql += " and F_CreateTime>='" + lastmonth.ToString("yyyy-MM") + "-28" + " 00:00:00' ";
                if (time.Month != 12)
                {
                    sql += " and F_CreateTime<='" + time.ToString("yyyy-MM") + "-27" + " 23:59:59' ";
                    day = 27;
                }
                else
                {
                    sql += " and F_CreateTime<='" + time.ToString("yyyy-MM") + "-31" + " 23:59:59' ";
                }
                sql += $" and (F_Type=2 or (F_Duplicate in(2,0)and F_Type=3))";
                var modelLis = new BLL.T_Bus_WorkOrder().GetModelList(sql);
               
                int lastday = days - 27;
                #region 上月28号至最后一天

                if (time.Month != 1)
                {
                    for (int i = 0; i < lastday; i++)
                    {
                        string name = string.Format("{0}-{1}", lastmonth.ToString("yyyy-MM"), 28 + i);
                        product.dates.Add(RetuDate(name, timeslot, modelLis));
                    }
                }
                for (int i = 0; i < day; i++)
                {
                    string name = "";
                    if (i < 9)
                    {
                        name = string.Format("{0}-0{1}", time.ToString("yyyy-MM"), 1 + i);
                    }
                    else
                    {
                        name = string.Format("{0}-{1}", time.ToString("yyyy-MM"), 1 + i);
                    }
                    product.dates.Add(RetuDate(name, timeslot, modelLis));
                }
            }





            #endregion
            #region 本月一号至27号
            var modelList = new BLL.T_Bus_WorkOrder().GetModelList(sql);
            Date date = new Date();
            date.total = modelList.Count;
            date.name = "合计";
            date.MonthCount1 = 0;
            date.MonthCount1name = timeslot.stime + "-" + timeslot.etime + "月份日期";
            date.MonthCountmix1 = "0.00%";
            date.MonthCount2 = 0;
            date.MonthCount2name = timeslot.onetime + "月份日期";
            date.MonthCountmix2 = "0.00%";
            date.MonthCount3 = 0;
            date.MonthCount3name = timeslot.twotime + "月份日期";
            date.MonthCountmix3 = "0.00%";
            date.MonthCount4 = 0;
            date.MonthCount4name = timeslot.threetime + "月份日期";
            date.MonthCountmix4 = "0.00%";
            date.MonthCount5 = 0;
            date.MonthCount5name = "不清楚日期";
            date.MonthCountmix5 = "0.00%";
            for (int j = 0; j < modelList.Count; j++)
            {
                if (!string.IsNullOrEmpty(modelList[j].F_ProductDate))
                {
                    if (modelList[j].F_ProductDate!="不清")
                    {
                        string result = modelList[j].F_ProductDate.Replace(".", "");
                        var pronos = DateTime.ParseExact(result, "yyyyMMdd", null).Month;
                        if (timeslot.stime > timeslot.etime)
                        {
                            int t = 12 - timeslot.stime;
                            if (pronos >= timeslot.stime && pronos <= 12)
                                date.MonthCount1++;
                            else if (pronos <= timeslot.etime)
                                date.MonthCount1++;

                        }
                        else
                        {
                            if (pronos >= timeslot.stime && pronos <= timeslot.etime)
                                date.MonthCount1++;
                        }
                        if (pronos == timeslot.onetime)
                            date.MonthCount2++;
                        if (pronos == timeslot.twotime)
                            date.MonthCount3++;
                        if (pronos == timeslot.threetime)
                            date.MonthCount4++;
                    }
                    else
                    {
                        date.MonthCount5++;
                    }
                }
                else
                {
                    date.MonthCount5++;
                }
            }
            if (date.total > 0)
            {
                date.MonthCountmix1 = string.Format("{0:f2}%", (float)date.MonthCount1 / date.total*100);
                date.MonthCountmix2 = string.Format("{0:f2}%", (float)date.MonthCount2 / date.total * 100);
                date.MonthCountmix3 = string.Format("{0:f2}%", (float)date.MonthCount3 / date.total * 100);
                date.MonthCountmix4 = string.Format("{0:f2}%", (float)date.MonthCount4 / date.total * 100);
                date.MonthCountmix5 = string.Format("{0:f2}%", (float)date.MonthCount5 / date.total * 100);
            }
            else
            {
                if (date.total > 0)
                {
                    date.MonthCountmix1 = "0.00%";
                    date.MonthCountmix2 = "0.00%";
                    date.MonthCountmix3 = "0.00%";
                    date.MonthCountmix4 = "0.00%";
                    date.MonthCountmix5 = "0.00%";
                }
            }
            product.dates.Add(date);
            product.factory = new List<Factory>();
            var EquipmentNumber = new BLL.T_Sys_EquipmentNumber().GetModelList("F_IsDelete=0");

            Model.T_Sys_EquipmentNumber t_Sys_EquipmentNumber = new Model.T_Sys_EquipmentNumber();
            t_Sys_EquipmentNumber.F_Production = "不清"; ;
            if (EquipmentNumber!=null )
            {
                EquipmentNumber.Add(t_Sys_EquipmentNumber);
                int total = 0;
                foreach (var it in EquipmentNumber)
                {

                    Factory factory = new Factory();
                    factory.name = it.F_Production;
                    factory.number = 0;
                    factory.proportion = "0.00%";
                    for (int i=0;i < modelList.Count;i++)
                    {
                        if (!string .IsNullOrEmpty (modelList[i].F_Manufacturer))
                        {
                            if (modelList[i].F_Manufacturer .Trim ()== it.F_Production)
                            {
                                total++;
                                factory.number++;
                            }
                        }
                        
                    }
                    if (modelList.Count > 0)
                        factory.proportion = string.Format("{0:f2}%", (float)factory.number / modelList.Count*100);
                    product.factory.Add(factory);
                }
                product.factory.Sort((a, b) => b.number.CompareTo(a.number));
                Factory factor = new Factory();
                factor.name = "合计";
                factor.number = total;
                factor.proportion = "0.00%";
                if (modelList.Count > 0)
                    factor.proportion = string.Format("{0:f2}%", (float)total / modelList.Count * 100);
                product.factory.Add(factor);
            }
            var problem = new BLL.T_Sys_DictionaryValue().GetModelList("F_DictionaryFlag='WTDM'");
            product.problem = new List<Factory>();
            if (problem != null)
            {
                int total = 0;
                foreach (var it in problem)
                {
                    Factory factory = new Factory();
                    if (it.F_Name=="B")
                    {
                        factory.name = "变质发臭（B）";
                    }
                    else if (it.F_Name == "SHBS")
                    {
                        factory.name = "食后不适（SHBS)";
                    }
                    else if (it.F_Name == "BS")
                    {
                        factory.name = "生虫(BS)";
                    }
                    else if (it.F_Name == "P")
                    {
                        factory.name = "破袋发霉(P)";
                    }
                    else if (it.F_Name.Contains("Z"))
                    {
                        factory.name = "杂质(Z**)";
                    }
                    else if (it.F_Name.Contains("QSZ"))
                    {
                        factory.name = "少支(QSZ)";
                    }
                    else if (it.F_Name.Contains("QJL"))
                    {
                        factory.name = "计量不足(QJL)";
                    }
                    else
                    {
                        factory.name = "其他Q";
                    }
                    
                    factory.number = 0;
                    factory.proportion = "0.00%";
                    for (int i = 0; i < modelList.Count; i++)
                    {
                        if (!string .IsNullOrEmpty (modelList[i].F_ProblemCode))
                        {
                            if (modelList[i].F_ProblemCode.Trim () == it.F_Name)
                            {
                                total++;
                                factory.number++;
                            }
                        }
                       
                    }
                    if (modelList.Count>0)
                    factory.proportion = string.Format("{0:f2}%", (float)factory.number / modelList.Count * 100);
                    product.problem.Add(factory);
                }
                 product.problem.ForEach(c => {
                  var group = product.problem.Where(a => a.name == c.name);
                  c.number = group.Sum(x => x.number);
                     c.proportion = string.Format("{0:f2}%", (float)c.number / modelList.Count * 100);
                 });
                product.problem = product.problem.Distinct(new ProductNoComparer()).ToList();

                product.problem.Sort((a, b) => b.number.CompareTo(a.number));

                Factory factor = new Factory();
                factor.name = "合计";
                factor.number = total;
                factor.proportion = "0.00%";
                if (modelList.Count > 0)
                    factor.proportion = string.Format("{0:f2}%", (float)total / modelList.Count * 100);
                product.problem.Add(factor);
            }
           
            var productcode = new BLL.T_Sys_DictionaryValue().GetModelList("F_DictionaryFlag='CPDM'");
            product.product = new List<Factory>();
            if (productcode != null )
            {
                int total = 0;
                foreach (var it in productcode)
                {
                    Factory factory = new Factory();
                    factory.name = it.F_Describe;
                    factory.number = 0;
                    factory.proportion = "0.00%";
                    for (int i = 0; i < modelList.Count; i++)
                    {
                        if (!string.IsNullOrEmpty(modelList[i].F_ProductType))
                        {
                            if (modelList[i].F_ProductType.Trim () == it.F_Name)
                            {
                                total++;
                                factory.number++;
                            }
                        }
                    }
                    if (modelList.Count > 0)
                        factory.proportion = string.Format("{0:f2}%", (float)factory.number / modelList.Count * 100);
                    product.product.Add(factory);
                }

                product.product.ForEach(c => {
                    var group = product.product.Where(a => a.name == c.name);
                    c.number = group.Sum(x => x.number);
                    c.proportion = string.Format("{0:f2}%", (float)c.number / modelList.Count * 100);
                });
                product.product = product.product.Distinct(new ProductNoComparer()).ToList();

                product.product.Sort((a, b) => b.number.CompareTo(a.number));
                Factory factor = new Factory();
                factor.name = "合计";
                factor.number = total;
                factor.proportion = "0.00%";
                if (modelList.Count > 0)
                    factor.proportion = string.Format("{0:f2}%", (float)total / modelList.Count * 100);
                product.product.Add(factor);
            }
           
            if (isdc > 0)
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.DistributionToExcel(time, product) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            var msg = time.Year + "年" + time.Month + "月份投诉产品日期分布情况表";
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = product,
                msg= msg,
            };
            return Success("获取成功", obj);
            #endregion
           
        }
        class ProductNoComparer : IEqualityComparer<Factory>
        {
            public bool Equals(Factory p1, Factory p2)
            {
                if (p1 == null)
                    return p2 == null;
                return p1.name  == p2.name;
            }

            public int GetHashCode(Factory p)
            {
                if (p == null)
                    return 0;
                return p.name.GetHashCode();
            }
        }
        /// <summary>
        /// 工单来源
        /// </summary>
        /// <returns></returns>
        public ActionResult GetSeatSourceReport(string sdate, string edate, int isdc = 0)
        {
            int userId = CurrentUser.UserData.F_UserId;
            Model.T_Sys_UserAccount userModel = new BLL.T_Sys_UserAccount().GetModel(userId);

            DateTime stime = DateTime.Now;
            DateTime etime = DateTime.Now;

            if (!string.IsNullOrEmpty(sdate))
            {
                stime = DateTime.Parse(sdate);
            }
            if (!string.IsNullOrEmpty(edate))
            {
                etime = DateTime.Parse(edate);
            }
            if (string.IsNullOrEmpty(sdate) && string.IsNullOrEmpty(edate))
            {
                stime = new DateTime(stime.Year, stime.Month, 1);
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", stime.ToString("yyyy-MM-dd"));
            paras.Add("@edate", etime.ToString("yyyy-MM-dd"));
            paras.Add("@groupcode", userModel.groupcode);
            var obj = DbHelperSQL.RunProcedure("P_SeatSourceReport", paras, "SeatSourceReport");
            if (isdc > 0)
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("工单来源", obj.Tables[0]) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }
        public class Feedback
        {
            public string name;
            public int daynumber;
            public int mounthnumber;
            public int Totalnumber;
            public string Proportion;
        }
        BLL.T_Bus_WorkOrder workOrder = new BLL.T_Bus_WorkOrder();
        /// <summary>
        /// 市场反馈一览表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetFeedbacklList(string stime, string endtime)
        {
            string sql = $"  F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            List<Feedback> modelList = new List<Feedback>(5);
            string smonthtime = "";
            if (!string.IsNullOrEmpty(stime))
            {
                smonthtime = stime.Trim() + " 00:00:01 "; ;
            }
            else
            {
                smonthtime = DateTime.Now.Date.ToString("yyyy-MM-01 00:00:00");
            }
            if (string.IsNullOrEmpty(endtime))
            {
                endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }
            else
            {
                endtime = endtime + " 23:59:59 "; ;
            }
            string sdaytime = DateTime.Now.Date.ToString("yy" +
                "yy-MM-dd 00:00:00");
            var WTDM = new BLL.T_Sys_DictionaryValue().GetModelList(" F_DictionaryFlag='" + "WTDM" + "' and F_State=1 ");
            int daynumber = 0; int mounthnumber = 0;int Totalnumber = 0;
            if (WTDM!=null )
            {
                foreach (var it in WTDM)
                {
                    Feedback model = new Feedback();
                    model.name = it.F_Name ;
                    float a = workOrder.GetModelList(sql).Count;
                    model.daynumber = workOrder.GetModelList(sql + $" and datediff(day,F_CreateTime,'{sdaytime}')<=0 and datediff(day,F_CreateTime,'{endtime}')>=0 " + "and F_ProblemCode='" + it .F_Name +"'").Count;
                    daynumber += model.daynumber;
                    model.mounthnumber = workOrder.GetModelList(sql + $" and datediff(day,F_CreateTime,'{smonthtime}')<=0 and datediff(day,F_CreateTime,'{endtime}')>=0 " + "and F_ProblemCode='" + it.F_Name + "'").Count;
                    mounthnumber += model.mounthnumber;
                    model.Totalnumber = workOrder.GetModelList(sql + "and F_ProblemCode='"+it .F_Name  + "'").Count;
                    Totalnumber += model.Totalnumber;
                    model.Proportion = string.Format("{0:f2}%", model.Totalnumber / a * 100);
                    modelList.Add(model);
                }
            }
            Feedback modeltotal = new Feedback();
            modeltotal.name = "合计";
            float b = workOrder.GetModelList(sql).Count;
            modeltotal.daynumber = daynumber;
            modeltotal.mounthnumber = mounthnumber;
            modeltotal.Totalnumber = Totalnumber ;
            modeltotal.Proportion = string.Format("{0:f2}%", modeltotal.Totalnumber / b  * 100);
            modelList.Add(modeltotal);
            #endregion
            modelList.Sort((a, c) => c.Totalnumber .CompareTo(a.Totalnumber));
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = modelList
            };

            return Content(obj.ToJson()); ;
        }
        /// <summary>
        /// 工单类型报表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTypeReport(string sdate, string edate, int isdc = 0)
        {
            int userId = CurrentUser.UserData.F_UserId;
            Model.T_Sys_UserAccount userModel = new BLL.T_Sys_UserAccount().GetModel(userId);

            DateTime stime = DateTime.Now;
            DateTime etime = DateTime.Now;

            if (!string.IsNullOrEmpty(sdate))
            {
                stime = DateTime.Parse(sdate);
            }
            if (!string.IsNullOrEmpty(edate))
            {
                etime = DateTime.Parse(edate);
            }
            if (string.IsNullOrEmpty(sdate) && string.IsNullOrEmpty(edate))
            {
                stime = new DateTime(stime.Year, stime.Month, 1);
            }
            string where = " F_IsDelete=0 and datediff(day,F_CreateTime,'" + stime + "')<=0 "
                + " and datediff(day,F_CreateTime,'" + etime + "')>=0 and F_Type=F_DictionaryValueId ";
            string dicwhere = "";
            if (!string.IsNullOrEmpty(userModel.groupcode))
            {
                where += " and F_GroupCode = '" + userModel.groupcode + "' ";
                dicwhere += " and F_GroupCode = '" + userModel.groupcode + "' ";
            }
            string sql = " select F_Name TypeName,(select COUNT(1) from T_Bus_WorkOrder where "+ where 
                + " ) Count from dbo.T_Sys_DictionaryValue where F_DictionaryFlag='GDLX' and F_State=1 "+ dicwhere;

            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            if (isdc > 0)
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("工单类型", dt,new string[] { "工单类型", "数量"}) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }

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



        /// <summary>
        /// 投诉类型报表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetSmallTypeReport(string sdate, string edate, int isdc = 0)
        {
            int userId = CurrentUser.UserData.F_UserId;
            Model.T_Sys_UserAccount userModel = new BLL.T_Sys_UserAccount().GetModel(userId);

            DateTime stime = DateTime.Now;
            DateTime etime = DateTime.Now;

            if (!string.IsNullOrEmpty(sdate))
            {
                stime = DateTime.Parse(sdate);
            }
            if (!string.IsNullOrEmpty(edate))
            {
                etime = DateTime.Parse(edate);
            }
            if (string.IsNullOrEmpty(sdate) && string.IsNullOrEmpty(edate))
            {
                stime = new DateTime(stime.Year, stime.Month, 1);
            }
            string where = " F_IsDelete=0 and datediff(day,F_CreateTime,'" + stime + "')<=0 "
                + " and datediff(day,F_CreateTime,'" + etime + "')>=0 and F_SmallType=F_DictionaryValueId ";
            string dicwhere = "";
            if (!string.IsNullOrEmpty(userModel.groupcode))
            {
                where += " and F_GroupCode = '" + userModel.groupcode + "' ";
                dicwhere += " and F_GroupCode = '" + userModel.groupcode + "' ";
            }
            string sql = " select F_Name TypeName,(select COUNT(1) from T_Bus_WorkOrder where " + where
                + " ) Count from dbo.T_Sys_DictionaryValue where F_DictionaryFlag='TSLX' and F_State=1 "+ dicwhere;

            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            if (isdc > 0)
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("投诉类型", dt, new string[] { "投诉类型", "数量" }) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }

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

        /// <summary>
        /// 客诉数量报表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetWorkMonthCountReport(int isdc = 0)
        {
            int userId = CurrentUser.UserData.F_UserId;
            Model.T_Sys_UserAccount userModel = new BLL.T_Sys_UserAccount().GetModel(userId);

            //原因
            int type = RequestString.GetInt("type", 0);
            //产品类别
            int producttype = RequestString.GetInt("producttype", 0);
            //大区
            string strarea = RequestString.GetQueryString("area");
            //省份
            string strprov = RequestString.GetQueryString("province");
            //城市
            string strcity = RequestString.GetQueryString("city");

            string where = " F_IsDelete=0 and datediff(month,F_CreateTime,getdate())<=12 ";
            if (!string.IsNullOrEmpty(userModel.groupcode))
            {
                where += " and F_GroupCode = '" + userModel.groupcode + "' ";
            }
            if (type != 0)
            {
                where += " and F_Type = '" + type + "' ";
            }
            if (producttype != 0)
            {
                where += " and F_ProductType = '" + producttype + "' ";
            }
            if (strarea.Trim() != "" && strarea != "undefined")
            {
                where += " and F_Area = '" + strarea.Trim() + "' ";
            }
            if (strprov.Trim() != "" && strprov.Trim() != "undefined")
            {
                where += " and F_Province = '" + strprov.Trim() + "' ";
            }
            if (strcity.Trim() != "" && strcity.Trim() != "undefined")
            {
                where += " and F_City = '" + strcity.Trim() + "' ";
            }

            DataTable dt = new DataTable();
            dt.Columns.Add("年月", typeof(string));
            dt.Columns.Add("数量", typeof(string));

            int[] ints = new int[12];
            string[] months = new string[12];
            for (int i = 12; i > 0; i--)
            {
                var date = DateTime.Now.AddMonths(1 - i);
                string mon = date.ToString("yyyyMM");
                months[12 - i] = mon;
                ints[12 - i] = date.Month;

                DataRow dr = dt.NewRow();
                dr["年月"] = mon;
                dt.Rows.Add(dr);
            }

            var obj = new
            {
                months,
                counts = new int[12]
            };

            int[] count = new int[12];
            for (int i = 0; i < ints.Length; i++)
            {
                string sql = " select count(1) from dbo.T_Bus_WorkOrder where " + where + " and datepart(month, F_CreateTime)=" + ints[i];
                obj.counts[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
                dt.Rows[i]["数量"] = obj.counts[i];
            }

            if (isdc > 0)
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("客诉数量", dt) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            var result = new
            {
                chartdata = obj,
                list = dt
            };

            return Success("成功", result);
        }
        public ActionResult GetComplaint(int type=0)
        {
            DateTime? thisStart, thisEnd, lastStart, lastEnd;
            DateTime today = DateTime.Today;
            if (type==1)
            {
                 thisStart = new DateTime(today.Year, today.Month, 1);
                 thisEnd = thisStart.Value.AddMonths(1).AddDays(-1);
                // 上月第一天和最后一天
                 lastStart = thisStart.Value.AddMonths(-1);
                 lastEnd = thisStart.Value.AddDays(-1);
            }
            else if (type == 2)
            {
                int currentYear = today.Year;
                // 本年第一天和最后一天
                thisStart = new DateTime(currentYear, 1, 1);
                thisEnd = new DateTime(currentYear, 12, 31);
                // 上年第一天和最后一天
                lastStart = new DateTime(currentYear - 1, 1, 1);
                lastEnd = new DateTime(currentYear - 1, 12, 31);
            }
            else 
            {
                
                CultureInfo culture = CultureInfo.CurrentCulture;
                DayOfWeek firstDayOfWeek = culture.DateTimeFormat.FirstDayOfWeek;
                // 计算本周的起始和结束
                int diff = (today.DayOfWeek - firstDayOfWeek + 7) % 7;
                thisStart = today.AddDays(-diff);
                thisEnd = thisStart.Value.AddDays(6);
                lastStart = thisStart.Value.AddDays(-7);
                lastEnd = thisEnd.Value.AddDays(-7);
            }

            string sql = "SELECT " +
                "  d.F_Describe AS name," +
                $"   COUNT(1) " +
                " AS thisnumber " +
                "  FROM   T_Sys_DictionaryValue d  LEFT JOIN " +
                "    T_Bus_WorkOrder o   ON d.F_Name = o.F_ProblemCode " +
                "     AND o.F_IsDelete = 0    AND o.F_Duplicate != 5 " +
                "     AND(o.F_Type = 2 OR(o.F_Duplicate NOT IN(1, 4) AND o.F_Type = 3))" +
                  $"  and o.F_CreateTime >= '{thisStart.Value.ToString("yyyy-MM-dd") + " 00:00:00"}'   " +
                $"AND o.F_CreateTime < '{thisEnd.Value.ToString("yyyy-MM-dd") + " 23:59:59"}'" +
                "    where d.F_DictionaryFlag = 'WTDM' GROUP BY " +
                "     d.F_Describe ORDER BY     d.F_Describe ";

            string sql1 = "SELECT " +
               "  d.F_Describe AS name," +
               $"   COUNT(1) " +
               "  AS lastnumber " +
               "  FROM   T_Sys_DictionaryValue d  LEFT JOIN " +
               "    T_Bus_WorkOrder o   ON d.F_Name = o.F_ProblemCode " +
               "     AND o.F_IsDelete = 0    AND o.F_Duplicate != 5 " +
               "     AND(o.F_Type = 2 OR(o.F_Duplicate NOT IN(1, 4) AND o.F_Type = 3))" +
                 $"  and o.F_CreateTime >= '{lastStart.Value.ToString("yyyy-MM-dd") + " 00:00:00"}'   " +
                $"AND o.F_CreateTime < '{lastStart.Value.ToString("yyyy-MM-dd") + " 23:59:59"}'" +
               "    where d.F_DictionaryFlag = 'WTDM' GROUP BY " +
               "     d.F_Describe ORDER BY     d.F_Describe ";

            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            DataTable dt1 = DbHelperSQL.Query(sql1).Tables[0];
            int thisnumber = 0, otherthisnumber=0, lastnumber = 0, otherlastnumber = 0;
            int total = 0;
            foreach (DataRow row in dt1.Rows)
            {
                if (row["name"] != null)
                {
                    if (row["name"].ToString() == "破袋发霉" || row["name"].ToString() == "杂质异物"
                        || row["name"].ToString() == "变质异味")
                    {
                       
                        lastnumber += int.Parse(row["lastnumber"].ToString());
                    }
                    else
                    {
                       
                        otherlastnumber += int.Parse(row["lastnumber"].ToString());
                    }
                }
                else
                {
                  
                    otherlastnumber += int.Parse(row["lastnumber"].ToString());
                }
            }
            foreach (DataRow row in dt.Rows)
            {
                if (row["name"]!=null )
                {
                    if (row["name"].ToString() == "破袋发霉"|| row["name"].ToString() == "杂质异物"
                        ||row["name"].ToString() == "变质异味")
                    {
                        thisnumber += int.Parse(row["thisnumber"].ToString());
                       
                    }
                    else
                    {
                        otherthisnumber += int.Parse(row["thisnumber"].ToString());
                       
                    }
                }
                else
                {
                    otherthisnumber += int.Parse(row["thisnumber"].ToString());
                   
                }
                total += int.Parse(row["thisnumber"].ToString());
            }
            var obj = new
            {
                state = "success",
                message = "成功",
                data = new 
                {
                    total,
                thisnumber,
                thiscontrast = thisnumber - lastnumber,
                otherthisnumber,
                othercontrast = otherthisnumber - otherlastnumber}
            };
            return Content(obj.ToJson());
        }
        public ActionResult GetComplaintSituation(int type = 0)
        {
            DateTime? thisStart, thisEnd, lastStart, lastEnd;
            DateTime today = DateTime.Today;
            if (type == 1)
            {
                thisStart = new DateTime(today.Year, today.Month, 1);
                thisEnd = thisStart.Value.AddMonths(1).AddDays(-1);
                // 上月第一天和最后一天
                lastStart = thisStart.Value.AddMonths(-1);
                lastEnd = thisStart.Value.AddDays(-1);
            }
            else if (type == 2)
            {
                int currentYear = today.Year;
                // 本年第一天和最后一天
                thisStart = new DateTime(currentYear, 1, 1);
                thisEnd = new DateTime(currentYear, 12, 31);
                // 上年第一天和最后一天
                lastStart = new DateTime(currentYear - 1, 1, 1);
                lastEnd = new DateTime(currentYear - 1, 12, 31);
            }
            else
            {

                CultureInfo culture = CultureInfo.CurrentCulture;
                DayOfWeek firstDayOfWeek = culture.DateTimeFormat.FirstDayOfWeek;
                // 计算本周的起始和结束
                int diff = (today.DayOfWeek - firstDayOfWeek + 7) % 7;
                thisStart = today.AddDays(-diff);
                thisEnd = thisStart.Value.AddDays(6);
                lastStart = thisStart.Value.AddDays(-7);
                lastEnd = thisEnd.Value.AddDays(-7);
            }

            string sql = "SELECT " +
                "  d.F_Describe AS name," +
                $"   COUNT(1) " +
                " AS thisnumber " +
                "  FROM   T_Sys_DictionaryValue d  LEFT JOIN " +
                "    T_Bus_WorkOrder o   ON d.F_Name = o.F_ProblemCode " +
                "     AND o.F_IsDelete = 0    AND o.F_Duplicate != 5 " +
                "     AND(o.F_Type = 2 OR(o.F_Duplicate NOT IN(1, 4) AND o.F_Type = 3))" +
                  $"  and o.F_CreateTime >= '{thisStart.Value.ToString("yyyy-MM-dd") + " 00:00:00"}'   " +
                $"AND o.F_CreateTime < '{thisEnd.Value.ToString("yyyy-MM-dd") + " 23:59:59"}'" +
                "    where d.F_DictionaryFlag = 'WTDM' GROUP BY " +
                "     d.F_Describe ORDER BY     d.F_Describe ";


            string sql1 = "SELECT " +
              "  d.F_Describe AS name," +
              $"   COUNT(1) " +
              "  AS lastnumber " +
              "  FROM   T_Sys_DictionaryValue d  LEFT JOIN " +
              "    T_Bus_WorkOrder o   ON d.F_Name = o.F_ProblemCode " +
              "     AND o.F_IsDelete = 0    AND o.F_Duplicate != 5 " +
              "     AND(o.F_Type = 2 OR(o.F_Duplicate NOT IN(1, 4) AND o.F_Type = 3))" +
               $"  and o.F_CreateTime >= '{lastStart.Value.ToString("yyyy-MM-dd") + " 00:00:00"}'   " +
                $"AND o.F_CreateTime < '{lastEnd.Value.ToString("yyyy-MM-dd") + " 23:59:59"}'" +
              "    where d.F_DictionaryFlag = 'WTDM' GROUP BY " +
              "     d.F_Describe ORDER BY     d.F_Describe ";

            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            DataTable dt1 = DbHelperSQL.Query(sql1).Tables[0];
            int mustiness = 0, impurity = 0, gobad = 0, lastgobad = 0, lastmustiness = 0, lastimpurity = 0;
            string mustinessRate = "", impurityRate = "", gobadRate = "";
            int mustinesscontrast=0, impuritycontrast = 0, gobadcontrast = 0;
            foreach (DataRow row in dt1.Rows)
            {
                if (row["name"] != null)
                {
                    if (row["name"].ToString() == "破袋发霉")
                    {
                      
                        lastmustiness += int.Parse(row["lastnumber"].ToString());
                    }
                    else if (row["name"].ToString() == "杂质异物")

                    {
                     
                        lastimpurity += int.Parse(row["lastnumber"].ToString());
                    }
                    else if (row["name"].ToString() == "变质异味")
                    {
                      
                        lastgobad += int.Parse(row["lastnumber"].ToString());
                    }
                }
            }
            foreach (DataRow row in dt.Rows)
            {
                if (row["name"] != null)
                {
                    if (row["name"].ToString() == "破袋发霉" )
                    {
                        mustiness += int.Parse(row["thisnumber"].ToString());
                     
                    }
                    else if (row["name"].ToString() == "杂质异物")

                    {
                        impurity += int.Parse(row["thisnumber"].ToString());
                       
                    }
                    else if (row["name"].ToString() == "变质异味")
                    {
                        gobad += int.Parse(row["thisnumber"].ToString());
                       
                    }
                }
            }
            int total = mustiness + impurity + gobad;
            if (total==0)
            {
                mustinessRate = string.Format("{0:f2}%", mustiness * 100);
                impurityRate = string.Format("{0:f2}%", impurity * 100);
                gobadRate = string.Format("{0:f2}%", gobad * 100);
            }
            else
            {
                mustinessRate = string.Format("{0:f2}%", (float)mustiness / total * 100);
                impurityRate = string.Format("{0:f2}%", (float)impurity / total * 100);
                gobadRate = string.Format("{0:f2}%", (float)gobad / total * 100);
            }
            mustinesscontrast = mustiness - lastmustiness;
            impuritycontrast = impurity - lastimpurity;
            gobadcontrast = gobad - lastgobad;
            var obj = new
            {
                state = "success",
                message = "成功",
                data = new
                {
                    mustiness,
                    mustinesscontrast,
                    mustinessRate,
                    impurity,
                    impuritycontrast,
                    impurityRate,
                    gobad,
                    gobadcontrast,
                    gobadRate
                }
            };
            return Content(obj.ToJson());
        }

        public ActionResult GetComplaintCompleted(int type = 0)
        {
            DateTime? thisStart, thisEnd;
            DateTime today = DateTime.Today;
            if (type == 1)
            {
                thisStart = new DateTime(today.Year, today.Month, 1);
                thisEnd = thisStart.Value.AddMonths(1).AddDays(-1);
              
            }
            else if (type == 2)
            {
                int currentYear = today.Year;
                // 本年第一天和最后一天
                thisStart = new DateTime(currentYear, 1, 1);
                thisEnd = new DateTime(currentYear, 12, 31);
              
            }
            else
            {

                CultureInfo culture = CultureInfo.CurrentCulture;
                DayOfWeek firstDayOfWeek = culture.DateTimeFormat.FirstDayOfWeek;
                // 计算本周的起始和结束
                int diff = (today.DayOfWeek - firstDayOfWeek + 7) % 7;
                thisStart = today.AddDays(-diff);
                thisEnd = thisStart.Value.AddDays(6);
            }

            string sql = "SELECT " +
                "  d.F_Describe AS name," +
                $"   COUNT(1) " +
                " AS number," +
                $"   COUNT(CASE WHEN  o.F_State in(3,4) THEN 1 END) " +
                " AS receiving," +
                $"   COUNT(CASE WHEN  o.F_State =10 THEN 1 END) " +
                "  AS complete " +
                "  FROM   T_Sys_DictionaryValue d  LEFT JOIN " +
                "    T_Bus_WorkOrder o   ON d.F_Name = o.F_ProblemCode " +
                "     AND o.F_IsDelete = 0    AND o.F_Duplicate != 5 " +
                "     AND(o.F_Type = 2 OR(o.F_Duplicate NOT IN(1, 4) AND o.F_Type = 3))" +
                  $"  AND o.F_CreateTime >= '{thisStart.Value.ToString("yyyy-MM-dd") + " 00:00:00"}' " +
                 $"  AND o.F_CreateTime < '{thisEnd.Value.ToString("yyyy-MM-dd") + " 23:59:59"}'" +
                "    where d.F_DictionaryFlag = 'WTDM' GROUP BY " +
                "     d.F_Describe ORDER BY     d.F_Describe ";

          

            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            int number = 0, receiving = 0, complete = 0;
            string receivingrate = "100%", completerate = "100%";
            foreach (DataRow row in dt.Rows)
            {
                //if (row["name"] != null)
                //{
                //    if (row["name"].ToString() == "破袋发霉" || row["name"].ToString() == "杂质异物"
                //        || row["name"].ToString() == "变质异味")
                //    {
                //        number += int.Parse(row["number"].ToString());
                //        receiving += int.Parse(row["receiving"].ToString());
                //        complete += int.Parse(row["complete"].ToString());
                //    }
                   
                //}
                number += int.Parse(row["number"].ToString());
                receiving += int.Parse(row["receiving"].ToString());
                complete += int.Parse(row["complete"].ToString());
            }
            if (number> 0) { receivingrate = string.Format("{0:f2}%", (float)receiving / number * 100);
                completerate = string.Format("{0:f2}%", (float)complete / number * 100);
            }
            var obj = new
            {
                state = "success",
                message = "成功",
                data = new
                {
                    receivingrate,
                    completerate
                }
            };
            return Content(obj.ToJson());
        }
        private class ComplaintTrend
        {
            public string time { get; set; }
            public int  receiving { get; set; }
            public int complete { get; set; }
        }

        public ActionResult GetComplaintTrend(int type = 0)
        {
            DateTime? thisStart, thisEnd;
            DateTime today = DateTime.Today;string time = "CONVERT(VARCHAR(10)";
            if (type == 1)
            {
                thisStart = new DateTime(today.Year, today.Month, 1);
                thisEnd = thisStart.Value.AddMonths(1).AddDays(-1);

            }
            else if (type == 2)
            {
                int currentYear = today.Year;
                // 本年第一天和最后一天
                thisStart = new DateTime(currentYear, 1, 1);
                thisEnd = new DateTime(currentYear, 12, 31);
                time = "CONVERT(VARCHAR(7)";
            }
            else
            {

                CultureInfo culture = CultureInfo.CurrentCulture;
                DayOfWeek firstDayOfWeek = culture.DateTimeFormat.FirstDayOfWeek;
                // 计算本周的起始和结束
                int diff = (today.DayOfWeek - firstDayOfWeek + 7) % 7;
                thisStart = today.AddDays(-diff);
                thisEnd = thisStart.Value.AddDays(6);
            }

            string sql = "     SELECT " +
                "  d.F_Describe AS name, " +
                $"   {time}, o.F_CreateTime, 120) AS time, " +
                "   COUNT(1) AS number," +
                "   COUNT(CASE WHEN o.F_State IN(3, 4) THEN 1 END) AS receiving," +
                "   COUNT(CASE WHEN   o.F_State = 10 THEN 1 END) AS complete FROM " +
                "   T_Sys_DictionaryValue d  LEFT JOIN T_Bus_WorkOrder o" +
                "   ON d.F_Name = o.F_ProblemCode    AND o.F_IsDelete = 0" +
                "   AND o.F_Duplicate != 5    AND(o.F_Type = 2 OR(o.F_Duplicate NOT IN(1, 4) AND o.F_Type = 3)) " +
                $"  and o.F_CreateTime >= '{thisStart.Value.ToString("yyyy-MM-dd") + " 00:00:00"}'   " +
                $"AND o.F_CreateTime < '{thisEnd.Value.ToString("yyyy-MM-dd") + " 23:59:59"}'" +
                " WHERE    d.F_DictionaryFlag = 'WTDM' GROUP BY " +
                $"     d.F_Describe, {time}, o.F_CreateTime, 120) ORDER BY    d.F_Describe, time ";

            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            List<ComplaintTrend> complaintTrends = new List<ComplaintTrend>();
            if (type!=2)
            {
                for (DateTime i = thisStart.Value; i <= thisEnd; i = i.AddDays(1))
                {
                    ComplaintTrend complaintTrend = new ComplaintTrend();
                    complaintTrend.time = i.ToString("MM.dd");
                    complaintTrend.receiving = 
                        dt.Select($" time='{i.ToString("yyyy-MM-dd")}'").Sum(x => x.Field<int>("receiving"));
                    complaintTrend.complete =
                        dt.Select($" time='{i.ToString("yyyy-MM-dd")}'").Sum(x => x.Field<int>("receiving"));
                    complaintTrends.Add(complaintTrend);
                }
            }
            else
            {
                for (DateTime i = thisStart.Value; i <= thisEnd; i = i.AddMonths(1))
                {
                    ComplaintTrend complaintTrend = new ComplaintTrend();
                    complaintTrend.time = i.ToString("yyyy.MM");
                    complaintTrend.receiving =
                        dt.Select($" time='{i.ToString("yyyy-MM")}'").Sum(x => x.Field<int>("receiving"));
                    complaintTrend.complete =
                        dt.Select($" time='{i.ToString("yyyy-MM")}'").Sum(x => x.Field<int>("receiving"));
                    complaintTrends.Add(complaintTrend);
                }
            }


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