Anyang_api/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/Assessment/DataAssessme...

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

namespace CallCenterApi.Interface.Controllers.Assessment
{
    public class DataAssessmentController : BaseController
    {
        private BLL.T_Sys_UserAccount userBLL = new BLL.T_Sys_UserAccount();
        private BLL.T_Data_Assessment dataBLL = new BLL.T_Data_Assessment();

        /// <summary>
        /// 获取月绩效考核数据
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        [Authority]
        public ActionResult GetDataList(string date,int deptid=0)
        {
            if (User == null)
                return Error("权限不足！");
            string sqlwhere = "";
            string strdate = date;
            if (string.IsNullOrEmpty(date))
                strdate = DateTime.Now.ToString("yyyyMM");
            if (deptid > 0)
                sqlwhere += " and F_DeptId=" + deptid;
            var datalist = dataBLL.GetModelList(" F_BatchNo='" + strdate + "'"+ sqlwhere + " order by F_DF_ZJ desc,F_JB_HandleCount desc ");
            var newlist = datalist.Select(x=>
             new{
                 x.F_Id,
                 x.F_DeptId,
                 x.F_DeptName,
                 x.F_BatchNo,
                 x.F_JB_HandleCount,
                 x.F_DF_BLCore,
                 x.F_DF_CSCore,
                 x.F_DF_GFCore,
                 x.F_DF_HFCore,
                 x.F_DF_TBCore,
                 x.F_DF_THCore,
                 x.F_DF_ZSKCore,
                 x.F_DF_ZJ
             });
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = newlist,
                total = datalist.Count
            };
            return Content(obj.ToJson());
        }

        /// <summary>
        /// 获取单位绩效考核详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [Authority]
        public ActionResult GetDataInfo(int dataid)
        {
            if (User == null)
                return Error("权限不足！");
            if (dataid <= 0)
                return Error("参数错误");
            var datamodel = dataBLL.GetModel(dataid);
            if (datamodel == null)
                return Error("获取信息失败");
            //获取分数及数量信息
            //获取扣分详情
            //查收不及时
            var sqlass = "select F_Id,F_WorkOrderId,F_CreateTime pdtime,F_SureTime cstime,dbo.GetRespTime(F_CreateTime) limittime,(select count(1) from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=2 and F_TypeID=data.F_Id) F_ISAppeal,(select top 1 F_Id from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=2 and F_TypeID=data.F_Id order by F_CreateTime desc) F_AppealID from [T_Data_AssignedRec_" + datamodel.F_BatchNo+ "] data where F_MainDeptId= " + datamodel.F_DeptId + " and F_SureTime>dbo.GetRespTime(F_CreateTime) order by F_WorkOrderId";//  and DATEDIFF(hour,F_CreateTime,F_SureTime)>4
            var asslist = DbHelperSQL.Query(sqlass).Tables[0];

            //办理超时
            var sqlfeed = "select F_Id,F_WorkOrderId,F_FeedbackTime,F_LimitTime,(select count(1) from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=1 and F_TypeID=data.F_Id) F_ISAppeal,(select top 1 F_Id from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=1 and F_TypeID=data.F_Id order by F_CreateTime desc) F_AppealID from [T_Data_Assigned_" + datamodel.F_BatchNo + "] data where F_MainDeptId= " + datamodel.F_DeptId + " and ISNULL(F_FeedbackTime,'')<>'' and F_FeedbackTime>F_LimitTime order by F_WorkOrderId";
            var feedlist = DbHelperSQL.Query(sqlfeed).Tables[0];

            //	申诉信息分类（1为办理情况，2为查收情况，3为办理规范，4为回访情况，5为退回情况，6为知识库索要更新不及时，7为知识库纠错情况，8为通报）

            //退单不及时
            var sqlback = "select F_Id,F_WorkOrderId,jbtime,thtime,limittime,F_ISAppeal,F_AppealID from (select feedback.F_Id,assigned.F_WorkOrderId,F_MaindeptId,assigned.F_CreateTime jbtime,feedback.F_CreateTime thtime,dbo.GetRespTime(assigned.F_CreateTime) limittime,(select count(1) from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=5 and F_TypeID=feedback.F_Id) F_ISAppeal,(select top 1 F_Id from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=5 and F_TypeID=feedback.F_Id order by F_CreateTime desc) F_AppealID from T_Data_Feedback_" + datamodel.F_BatchNo + " feedback join T_Data_Assigned_" + datamodel.F_BatchNo + " assigned on assigned.F_Id=feedback.F_AssignedId) back where F_MaindeptId=" + datamodel.F_DeptId + " and thtime>limittime order by F_WorkOrderId";//and DATEDIFF(hour,jbtime,thtime)>4
            var backlist = DbHelperSQL.Query(sqlback).Tables[0];

            //回访不满意
            var sqlvisit = "select F_Id,F_WorkOrderId,F_IsSatisfie,F_Result,F_CreateTime,F_ISAppeal,F_AppealID from (select visit.F_Id,visit.F_WorkOrderId,F_MaindeptId,visit.F_IsSatisfie,visit.F_Result,visit.F_CreateTime,(select count(1) from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=4 and F_TypeID=visit.F_Id) F_ISAppeal,(select top 1 F_Id from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=4 and F_TypeID=visit.F_Id order by F_CreateTime desc) F_AppealID from T_Data_Visit_" + datamodel.F_BatchNo + " visit join T_Bus_AssignedInfo on T_Bus_AssignedInfo.F_Id=visit.F_AssignedId) vvisit where F_MainDeptId=" + datamodel.F_DeptId + " and F_IsSatisfie<>1 order by F_WorkOrderId";
            var visitlist = DbHelperSQL.Query(sqlvisit).Tables[0];

            //知识库
            //索要情况
            var sqlzsksy = "select F_DemandsId as F_Id,F_Descript,F_CreateTime,F_DemandsId,(select count(1) from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=6 and F_TypeID=data.F_DemandsId) F_ISAppeal,(select top 1 F_Id from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=6 and F_TypeID=data.F_DemandsId order by F_CreateTime desc) F_AppealID from T_Data_RepositoryDemands_" + datamodel.F_BatchNo + " data where ISNULL(F_RepositoryId,'')='' and F_Deptid='" + datamodel.F_DeptId + "' order by F_DemandsId";
            var zsksylist = DbHelperSQL.Query(sqlzsksy).Tables[0];
            //纠错情况
            var sqlzskjc = "select F_Id,F_RepositoryId,F_Title,F_Reason,F_OptOn,(select count(1) from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=7 and F_TypeID=data.F_Id) F_ISAppeal,(select top 1 F_Id from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=7 and F_TypeID=data.F_Id order by F_CreateTime desc) F_AppealID from T_Data_RepositoryOperation_" + datamodel.F_BatchNo + " data where F_DeptId='" + datamodel.F_DeptId + "' order by F_OptOn";
            var zskjclist = DbHelperSQL.Query(sqlzskjc).Tables[0];

            //工单规范
            var sqlgdgf = "select F_Id,F_WorkOrderId,names,F_StandardIDS,(select count(1) from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=3 and F_TypeID=data.F_Id) F_ISAppeal,(select top 1 F_Id from T_Bus_AppealInfo where F_IsDelete=0 and F_Type=3 and F_TypeID=data.F_Id order by F_CreateTime desc) F_AppealID from T_Data_WorkOrderClose_" + datamodel.F_BatchNo + " data where ISNULL(F_IsStandard,0)>0 and F_MainDeptId='" + datamodel.F_DeptId + "' order by F_WorkOrderId";
            var gdgflist = DbHelperSQL.Query(sqlgdgf).Tables[0];

            //通报

            //匹配是否可申诉
            asslist = BindFileData(asslist, datamodel.F_SSLimittime, datamodel.F_DeptId, User.F_DeptId);
            feedlist = BindFileData(feedlist, datamodel.F_SSLimittime, datamodel.F_DeptId, User.F_DeptId);
            backlist = BindFileData(backlist, datamodel.F_SSLimittime, datamodel.F_DeptId, User.F_DeptId);
            visitlist = BindFileData(visitlist, datamodel.F_SSLimittime, datamodel.F_DeptId, User.F_DeptId);
            zsksylist = BindFileData(zsksylist, datamodel.F_SSLimittime, datamodel.F_DeptId, User.F_DeptId);
            zskjclist = BindFileData(zskjclist, datamodel.F_SSLimittime, datamodel.F_DeptId, User.F_DeptId);
            gdgflist = BindFileData(gdgflist, datamodel.F_SSLimittime, datamodel.F_DeptId, User.F_DeptId);

            var obj = new {
                data= datamodel,
                asslist,
                feedlist,
                backlist,
                visitlist,
                zsksylist,
                zskjclist,
                gdgflist,
            };

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

        private DataTable BindFileData(DataTable dt, DateTime? limittime,int deptid,int nowdeptid)
        {
            dt.Columns.Add("ssbutton", typeof(object));
            foreach (DataRow dr in dt.Rows)
            {
                if (dr["F_ISAppeal"] != null && dr["F_ISAppeal"].ToString() != "")
                {
                    //判断当前登录人是否=考核数据中的部门id
                    if (deptid == nowdeptid)
                    {
                        //申诉时限不为空；当前时间在申诉时间内
                        if (limittime != null && limittime > DateTime.Now)
                        {
                            //是否已申诉
                            if (int.Parse(dr["F_ISAppeal"].ToString()) <= 0)
                            {
                                dr["ssbutton"] = true;
                            }
                            else
                                dr["ssbutton"] = false;
                        }
                        else
                            dr["ssbutton"] = false;
                    }
                    else
                        dr["ssbutton"] = false;
                }
                else
                    dr["ssbutton"] = false;
            }
            return dt;
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        public ActionResult ExptList(string date, int deptid = 0)
        {
            NPOIHelper npoi = new NPOIHelper();
            string strdate = date;
            if (string.IsNullOrEmpty(date))
                strdate = DateTime.Now.ToString("yyyyMM");
            var sqlwhere = "";
            if (deptid > 0)
                sqlwhere += " and F_DeptId=" + deptid;

            var sql = "select ROW_NUMBER() OVER(ORDER BY F_DF_ZJ desc,F_JB_HandleCount desc) No,F_DeptName,F_DF_CSCore,F_DF_THCore,F_DF_BLCore,F_DF_HFCore,F_DF_ZSKCore,F_DF_GFCore,F_DF_ZJ,F_JB_HandleCount from T_Data_Assessment where F_BatchNo='" + strdate + "'" + sqlwhere + " order by F_DF_ZJ desc,F_JB_HandleCount desc ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            if (npoi.ExportToExcel("绩效考核数据-"+ date, dt, getcols()) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }

        private string[] getcols()
        {
            //序号	承办单位	查收要求（15）	工单退回要求（15）	及时办结情况（20）	回访评价（20）	知识库更新 （20）	工单办理结果填写规范及要求（10） 合计    办理数量

            String[] str = { "序号", "承办单位", "查收要求（15）", "工单退回要求（15）", "及时办结情况（20）", "回访评价（20）", "知识库更新 （20）", "工单办理结果填写规范及要求（10）", "合计","办理数量" };
            return str;
        }
        #region 督办统计数据（与绩效考核相似，所有放在此处）
        [Authority]
        public ActionResult GetDBDataList(string date, int deptid = 0)
        {
            if (User == null)
                return Error("权限不足！");
           string edate = "";
            if (User.F_RoleCode == "WLDW")
                deptid = User.F_DeptId;
            #region 
            string strdate = date;
            string sqlwhere = "";
            if (string.IsNullOrEmpty(date))
            {
                //strdate = DateTime.Now.ToString("yyyyMM");

                string sql = "select top 1 F_BatchNo  from  T_Data_Supervision order by F_BatchNo desc";
                string dates = DbHelperSQL.GetSingle(sql).ToString();
                if (!string.IsNullOrEmpty(dates))
                {
                    strdate += dates;
                }
                sqlwhere += " and F_BatchNo = '" + Convert.ToDateTime(strdate).ToString("yyyyMM") + "'";
            }
            //}
           if (!string.IsNullOrEmpty(date))
            {
               // strdate = DateTime.Now.ToString("yyyyMM");
                sqlwhere += " and F_BatchNo = '" +Convert.ToDateTime(strdate).ToString("yyyyMM")  + "'";

            }

            if (deptid > 0)
                sqlwhere += " and F_DeptId=" + deptid;

            var sqlass = "select * from T_Data_Supervision where 1=1  " + sqlwhere + " order by F_ReciveCount desc ";//此处以查收量倒叙排序
            #endregion
          var list = DbHelperSQL.Query(sqlass).Tables[0];
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = list,
                total = list.Rows.Count
            };
            return Content(obj.ToJson());
        }

        [Authority]
        public ActionResult GetDBDataList_1(DateTime ? starttime , DateTime? endtime, int deptid = 0,bool isdc=false,int source=0)
        {
            if (User == null)
                return Error("权限不足！");
            string strdate = ""; string edate = "";
            if (User.F_RoleCode == "WLDW")
                deptid = User.F_DeptId;
            #region 
            string month = "";
            if (starttime==null )
            {
                strdate = DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + "-21 00:00:00";
            }
            else
            {
                strdate = starttime.Value.ToString("yyyy-MM-dd HH:mm:ss");
                
            }
            if (endtime == null)
            {
                edate = DateTime.Now.ToString("yyyy-MM") + "-20 23:59:59";
                month = DateTime.Now.Month.ToString();
            }
            else
            {
                edate = endtime.Value .ToString("yyyy-MM-dd")+" 23:59:59" ;
                month = endtime.Value.Month.ToString();
            }
            string sourcesql = "";
            if (source >0)
            {
                sourcesql = source.ToString();
            }
            #endregion
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@deptid", deptid.ToString());
            paras.Add("@sdate", strdate);
            paras.Add("@edate", edate);
            paras.Add("@source", sourcesql);
            var list = DbHelperSQL.RunProcedure("P_DeptDBData", paras, "DeptData").Tables[0];
            //var list = DbHelperSQL.Query(sqlass).Tables[0];
            if (isdc)
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.DBExportToExcel(list,"督办数据统计",month, Convert.ToDateTime(strdate).ToString("yyyy-MM-dd"),
                    Convert.ToDateTime(edate).ToString("yyyy-MM-dd")
                   ) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = list,
                month ,
                strdate= Convert.ToDateTime(strdate).ToString("yyyy-MM-dd"),
                edate = Convert.ToDateTime(edate).ToString("yyyy-MM-dd")
            };
            return Content(obj.ToJson());
        }


        [Authority]
        public ActionResult GetDBDataList_2(DateTime? starttime, DateTime? endtime, int deptid3 = 0, bool isdc = false, int source = 0)
        {
            if (User == null)
                return Error("权限不足！");
            int deptid = 0;
            string strdate = ""; string edate = "";
            if (User.F_RoleCode == "WLDW")
                deptid = User.F_DeptId;
            #region 
            string month = "";
            if (starttime == null)
            {
                strdate = DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + "-21 00:00:00";
            }
            else
            {
                strdate = starttime.Value.ToString("yyyy-MM-dd HH:mm:ss");

            }
            if (endtime == null)
            {
                edate = DateTime.Now.ToString("yyyy-MM") + "-20 23:59:59";
                month = DateTime.Now.Month.ToString();
            }
            else
            {
                edate = endtime.Value.ToString("yyyy-MM-dd") + " 23:59:59";
                month = endtime.Value.Month.ToString();
            }
            string sourcesql = "";
            if (source > 0)
            {
                sourcesql = source.ToString();
            }
            #endregion
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@deptid", deptid.ToString());
            paras.Add("@sdate", strdate);
            paras.Add("@edate", edate);
            paras.Add("@source", sourcesql);
            paras.Add("@deptid3", deptid3.ToString ());
           
            var list = DbHelperSQL.RunProcedure("P_EJDeptDBData", paras, "EJDeptDBData").Tables[0];
            //var list = DbHelperSQL.Query(sqlass).Tables[0];
            if (isdc)
            {
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.DBEJExportToExcel(list, "督办数据统计", month, Convert.ToDateTime(strdate).ToString("yyyy-MM-dd"),
                    Convert.ToDateTime(edate).ToString("yyyy-MM-dd")
                   ) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = list,
                month,
                strdate = Convert.ToDateTime(strdate).ToString("yyyy-MM-dd"),
                edate = Convert.ToDateTime(edate).ToString("yyyy-MM-dd")
            };
            return Content(obj.ToJson());
        }
        [Authority]
        public ActionResult ExptDBDataList(string date, int deptid = 0)
        {
            if (User == null)
                return Error("权限不足！");
            string sqlwhere = "";
            string strdate = date;

            if (string.IsNullOrEmpty(date))
            {
                // strdate = DateTime.Now.ToString("yyyyMM");

                string sql = "select top 1 F_BatchNo  from  T_Data_Supervision order by F_BatchNo desc";
                string dates = DbHelperSQL.GetSingle(sql).ToString();
                if (!string.IsNullOrEmpty(dates))
                {
                    strdate += dates;
                }
            }
           

            if (!string.IsNullOrEmpty(date))
            {
                // strdate = DateTime.Now.ToString("yyyyMM");
                strdate =   Convert.ToDateTime(strdate).ToString("yyyyMM")  ;
            }
 

            if (deptid > 0)
                sqlwhere += " and F_DeptId=" + deptid;
            var sqlass = "select ROW_NUMBER() OVER(ORDER BY F_ReciveCount desc) No,F_DeptName,F_ReciveCount,F_TimeOutCount,F_UnFinishCount,F_BackCount,F_VisitCount,F_DissatisfiedCount from T_Data_Supervision "
                + "where  F_BatchNo = '" + strdate + "'" + sqlwhere + " order by F_ReciveCount desc ";//此处以查收量倒叙排序

            var list = DbHelperSQL.Query(sqlass).Tables[0];

            String[] str = { "序号", "承办单位", "承办件", "超期件", "未果件", "退单件", "群众评议总数", "不满意件" };
            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel("督办数据统计-" + date, list, str) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
        #endregion
    }
}