Anyang_api/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/Question/Questionnair...

using CallCenter.Utility;
using CallCenterApi.Common;
using CallCenterApi.DB;
using CallCenterApi.Interface.Controllers.Base;
using CallCenterApi.Interface.Models.Filter;
using CallCenterApi.Interface.Models.Input;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace CallCenterApi.Interface.Controllers.Question
{
    [Authority]
    public class QuestionnaireController : BaseController
    {
        private readonly BLL.T_Ask_QuestionCategory qCategoryBLL = new BLL.T_Ask_QuestionCategory();
        private readonly BLL.T_Ask_Question questionBLL = new BLL.T_Ask_Question();
        private readonly BLL.T_Ask_QuestionItems questionItemBLL = new BLL.T_Ask_QuestionItems();
        private readonly BLL.T_Ask_PagerInfo pagerInfoBLL = new BLL.T_Ask_PagerInfo();
        private readonly BLL.T_Ask_PagerItems pagerInfoItemsBLL = new BLL.T_Ask_PagerItems();

        #region 问卷设置 - 试题问卷
        /// <summary>
        /// 问卷设置 - 试题问卷
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public ActionResult GetPagerInfoList(FilterPagerInfo filter)
        {
            string sql = "";
            if (!string.IsNullOrWhiteSpace(filter.Key))
            {
                sql += $" and (F_Title like '%{ filter.Key.Trim()}%' or F_Remark like '%{ filter.Key.Trim()}%') ";
            }
            var recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                    "T_Ask_PagerInfo",
                    "F_PagerId",
                    "*",
                    " and F_DeleteFlag=0 " + sql,
                    "ORDER BY F_PagerId desc",
                    filter.PageSize,
                    filter.PageIndex,
                    true,
                    out recordCount);
            var obj = new
            {
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());
        }
        public ActionResult GetPagerInfoModel(int pid)
        {
            var model = pagerInfoBLL.GetModel(pid);
            return Success("获取问卷成功", model);
        }
        public ActionResult CreateOrUpdatePager(Pagerinfo input)
        {
            var model = new Model.T_Ask_PagerInfo();
            if (input.Id <= 0)
            {
                model.F_Title = input.Title;
                model.F_StartText = input.StartText;
                model.F_EndText = input.EndText;
                model.F_Remark = input.Remark;
                model.F_Times = input.Times;
                model.F_DeleteFlag = 0;
                model.F_CreateOn = DateTime.Now;
                model.F_CreateBy = Utils.StrToInt(User.UserData["F_UserID"], 0);
                if (pagerInfoBLL.Add(model) > 0)
                    return Success("添加问卷成功" + ",操作人：" + User.UserData["F_UserCode"]);
                return Error("添加问卷失败" + ",操作人：" + User.UserData["F_UserCode"]);
            }
            model = pagerInfoBLL.GetModel(input.Id);
            model.F_Title = input.Title;
            model.F_StartText = input.StartText;
            model.F_EndText = input.EndText;
            model.F_Remark = input.Remark;
            model.F_Times = input.Times;
            model.F_DeleteFlag = 0;
            model.F_ModifyBy = Utils.StrToInt(User.UserData["F_UserID"], 0);
            model.F_ModifyOn = DateTime.Now;
            if (pagerInfoBLL.Update(model))
                return Success("修改问卷成功" + ",操作人：" + User.UserData["F_UserCode"]);
            return Error("修改问卷失败" + ",操作人：" + User.UserData["F_UserCode"]);
        }

        /// <summary>
        /// 选择试题 - 修改 - 选择试题（左侧）
        /// </summary>
        /// <returns></returns>
        public ActionResult GetQuestionNotItemsList(FilterQuestion filter)
        {
            StringBuilder sb = new StringBuilder();

            if (filter.CategoryId > 0)
            {
                sb.Append(" and F_CategoryId=" + filter.CategoryId);
            }
            if (filter.SelectType > 0)
            {
                sb.Append(" and F_Type=" + filter.SelectType);
            }
            if (filter.PagerId > 0)
            {
                sb.Append(" and F_QuestionId not in (SELECT [F_QuestionId] FROM [T_Ask_PagerItems] where [F_PagerId]=" + filter.PagerId + ") ");
            }
            if (!string.IsNullOrWhiteSpace(filter.Key))
            {
                sb.Append(" and (F_Title like '%" + filter.Key + "%' or F_Content like '%" + filter.Key + "%') ");
            }

            var recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                    "vw_Ask_Question",
                    "F_QuestionId",
                    "*",
                    " and F_DeleteFlag=0 " + sb.ToString(),
                    "ORDER BY F_QuestionId desc",
                    filter.PageSize,
                    filter.PageIndex,
                    true,
                    out recordCount);
            var quesitemlist = questionItemBLL.GetModelList("");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var items = "";
                string qid = dt.Rows[i]["F_QuestionId"].ToString();
                var quesitem = quesitemlist.Where(qi => qi.F_QuestionId == int.Parse(qid));
                if (quesitem.Count() > 0)
                {
                    foreach (var item in quesitem)
                    {
                        items += "；" + item.F_ItemName;
                    }
                }

                dt.Rows[i]["F_Content"] = items.Trim('；');
            }
            var obj = new
            {
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());
        }
        /// <summary>
        /// 选择试题 - 修改 - 选择试题（右侧）
        /// </summary>
        /// <returns></returns>
        public ActionResult GetQuestionPageList(FilterQuestion filter)
        {
            StringBuilder sb = new StringBuilder();

            if (filter.PagerId > 0)
            {
                sb.Append(" and F_PagerId=" + filter.PagerId);
            }

            var recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                    "vw_Ask_PagerQuestion",
                    "F_ItemId",
                    "*",
                    " and F_DeleteFlag=0 " + sb.ToString(),
                    "ORDER BY F_Sort desc",
                    filter.PageSize,
                    filter.PageIndex,
                    true,
                    out recordCount);

            var quesitemlist = questionItemBLL.GetModelList("");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var items = "";
                string qid = dt.Rows[i]["F_QuestionId"].ToString();
                var quesitem = quesitemlist.Where(qi => qi.F_QuestionId == int.Parse(qid));
                if (quesitem.Count() > 0)
                {
                    foreach (var item in quesitem)
                    {
                        items += "；" + item.F_ItemName;
                    }
                }

                dt.Rows[i]["F_Content"] = items.Trim('；');
            }

            var obj = new
            {
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());
        }
        /// <summary>
        /// 选择试题 - 修改 - 批量选入（左侧 -> 右侧）
        /// </summary>
        /// <returns></returns>
        public ActionResult AddPagerItems(string[] ids, string pid, string sort = "0")
        {
            //string[] arrid = ids.Split(',');
            if (ids.Count() > 0)
            {
                int intsort = 0;
                intsort = Convert.ToInt32(sort) + 1;
                int cc = 0;
                foreach (string squestionid in ids)
                {
                    Model.T_Ask_PagerItems model = new Model.T_Ask_PagerItems();
                    int pagerid = 0;
                    int questionid = 0;
                    pagerid = Convert.ToInt32(pid);
                    questionid = Convert.ToInt32(squestionid);
                    model.F_PagerId = pagerid;
                    model.F_QuestionId = questionid;
                    model.F_Sort = intsort;
                    intsort++;
                    if (pagerInfoItemsBLL.Add(model) > 0)
                    {
                        cc++;
                    }
                }
                if (cc == ids.Length)
                {
                    return Success("选入试题成功" + ",操作人：" + User.UserData["F_UserCode"]);
                }
            }
            return Error("选入试题失败" + ",操作人：" + User.UserData["F_UserCode"]);
        }
        /// <summary>
        /// 选择试题 - 修改 - 批量选出（右侧 -> 左侧）
        /// </summary>
        /// <returns></returns>
        public ActionResult DelPagerItems(string[] ids)
        {
            //string[] arrid = ids.Split(',');
            if (ids.Length > 0)
            {
                int cc = 0;
                foreach (string itemid in ids)
                {
                    if (pagerInfoItemsBLL.Delete(Convert.ToInt32(itemid)))
                    {
                        cc++;
                    }
                }
                if (cc == ids.Length)
                {
                    return Success("选出试题成功" + ",操作人：" + User.UserData["F_UserCode"]);
                }
            }
            return Error("选出试题失败" + ",操作人：" + User.UserData["F_UserCode"]);
        }

        /// <summary>
        /// 删除试题问卷(删除两表信息)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult DeletePagerInfo(int id = 0)
        {
            if (pagerInfoBLL.Delete(id))
            {
                pagerInfoItemsBLL.DeleteByPagerId(id);
                return Success("删除试题问卷成功" + ",操作人：" + User.UserData["F_UserCode"]);
            }
            else
            {
                return Error("删除试题问卷失败" + ",操作人：" + User.UserData["F_UserCode"]);
            }
        }
        #endregion

        #region 问卷设置 - 问卷信息
        public ActionResult GetPagerInfoQ(int pid)
        {
            var model = pagerInfoBLL.GetModel(pid);
            if (model != null)
            {
                var qlist = questionBLL.GetModelList(" F_DeleteFlag=0 and F_QuestionId in (select F_QuestionId from T_Ask_PagerItems where F_PagerId=" + pid + ")  order by F_QuestionId ");
                var qilist = questionItemBLL.GetModelList(" F_QuestionId in (select F_QuestionId from T_Ask_PagerItems where F_PagerId=" + pid + ")  order by F_ItemId ");

                var newmodel = new
                {
                    F_Title = model.F_Title,
                    F_StartText = model.F_StartText,
                    F_EndText = model.F_EndText,
                    F_Remark = model.F_Remark,
                    F_Questions = qlist.Select(q =>
                    {
                        return new
                        {
                            quesid = q.F_QuestionId,
                            questitle = q.F_Title,
                            questype = q.F_Type,
                            quescontent = q.F_Content,
                            quesremark = q.F_Remark,
                            quesitems = qilist.Where(qq => qq.F_QuestionId == q.F_QuestionId).Select(qi =>
                            {
                                return new
                                {
                                    itemid = qi.F_ItemId,
                                    itemname = qi.F_ItemName,
                                    itemremark = qi.F_Remark,
                                };
                            })
                        };
                    })
                };

                return Success("获取问卷试题信息成功", newmodel);
            }
            return Error("获取问卷试题信息失败");
        }

        public ActionResult GetPagerInfoA(int pid)
        {
            var model = pagerInfoBLL.GetModel(pid);
            var qlist = questionBLL.GetModelList(" F_DeleteFlag=0 and F_QuestionId in (select F_QuestionId from T_Ask_PagerItems where F_PagerId=" + pid + ")  order by F_QuestionId ");
            var qilist = questionItemBLL.GetModelList(" F_QuestionId in (select F_QuestionId from T_Ask_PagerItems where F_PagerId=" + pid + ")  order by F_ItemId ");
            var newmodel = new
            {
                F_Title = model.F_Title,
                F_StartText = model.F_StartText,
                F_EndText = model.F_EndText,
                F_Remark = model.F_Remark,
                F_Questions = qlist.Select(q =>
                {
                    return new
                    {
                        quesid = q.F_QuestionId,
                        questitle = q.F_Title,
                        questype = q.F_Type,
                        quescontent = q.F_Content,
                        quesremark = q.F_Remark,
                        quesitems = qilist.Where(qq => qq.F_QuestionId == q.F_QuestionId).Select(qi =>
                        {
                            return new
                            {
                                itemid = qi.F_ItemId,
                                itemname = qi.F_ItemName,
                                itemremark = qi.F_Remark,
                            };
                        })
                    };
                })
            };

            return Success("获取问卷试题信息成功", newmodel);
        }
        #endregion

        #region 试题管理 - 试题分类
        /// <summary>
        /// 试题管理 - 试题分类
        /// </summary>
        /// <param name="pagesize"></param>
        /// <param name="pageindex"></param>
        /// <returns></returns>
        public ActionResult GetQCategoryList(int pagesize = 10, int pageindex = 1)
        {
            //if (!Request.IsAuthenticated)
            //    return NoToken("未知错误，请重新登录");

            var recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                    "T_Ask_QuestionCategory",
                    "F_CategoryId",
                    "*",
                    "",
                    "ORDER BY F_CategoryId desc",
                    pagesize,
                    pageindex,
                    true,
                    out recordCount);
            var obj = new
            {
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());
        }
        public ActionResult GetQCategoryAlllist()
        {
            var alllist = qCategoryBLL.GetModelList(" 1=1 order by F_CategoryId desc");
            return Success("获取试题分类成功", alllist);
        }
        public ActionResult GetQCategoryModel(int id = 0)
        {
            //if (!Request.IsAuthenticated)
            //    return NoToken("未知错误，请重新登录");
            var model = qCategoryBLL.GetModel(id);
            if (model == null)
                return Error("当前数据不存在");
            return Success("", model);
        }
        public ActionResult CreateOrUpdateQCategory(int id = 0, string name = "")
        {
            //if (!Request.IsAuthenticated)
            //    return NoToken("未知错误，请重新登录");
            var model = new Model.T_Ask_QuestionCategory();
            if (string.IsNullOrWhiteSpace(name))
                return Error("请填写名称" + ",操作人：" + User.UserData["F_UserCode"]);
            if (id <= 0)
            {
                model.F_CategoryName = name;
                model.F_CreateOn = DateTime.Now;
                model.F_CreateBy = Utils.StrToInt(User.UserData["F_UserID"], 0);
                model.F_DeleteFlag = 0;
                if (qCategoryBLL.Add(model) > 0)
                    return Success("添加试题分类成功" + ",操作人：" + User.UserData["F_UserCode"]);
                return Error("添加试题分类失败" + ",操作人：" + User.UserData["F_UserCode"]);
            }
            model = qCategoryBLL.GetModel(id);
            model.F_CategoryName = name;
            if (qCategoryBLL.Update(model))
                return Success("修改试题分类成功" + ",操作人：" + User.UserData["F_UserCode"]);
            return Error("修改试题分类失败" + ",操作人：" + User.UserData["F_UserCode"]);
        }
        public ActionResult DeleteQCategory(int id = 0)
        {
            //if (!Request.IsAuthenticated)
            //    return NoToken("未知错误，请重新登录");
            if (qCategoryBLL.Delete(id))
                return Success("删除试题分类成功" + ",操作人：" + User.UserData["F_UserCode"]);
            return Error("删除试题分类失败" + ",操作人：" + User.UserData["F_UserCode"]);
        }
        #endregion

        #region 试题管理 - 试题列表
        public ActionResult GetQuestionList(FilterQuestion filter)
        {
            //if (!Request.IsAuthenticated)
            //    return NoToken("未知错误，请重新登录");
            StringBuilder sb = new StringBuilder();

            if (filter.CategoryId > 0)
            {
                sb.Append(" and F_CategoryId=" + filter.CategoryId);
            }
            if (filter.SelectType > 0)
            {
                sb.Append(" and F_Type=" + filter.SelectType);
            }

            if (!string.IsNullOrWhiteSpace(filter.Key))
            {
                sb.Append(" and (F_Title like '%" + filter.Key + "%' or F_Content like '%" + filter.Key + "%') ");
            }

            var recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                    "vw_Ask_Question",
                    "F_QuestionId",
                    "*",
                    " and F_DeleteFlag=0 " + sb.ToString(),
                    "ORDER BY F_QuestionId desc",
                    filter.PageSize,
                    filter.PageIndex,
                    true,
                    out recordCount);
            var quesitemlist = questionItemBLL.GetModelList("");

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var items = "";
                string qid = dt.Rows[i]["F_QuestionId"].ToString();
                var quesitem = quesitemlist.Where(qi => qi.F_QuestionId == int.Parse(qid));
                if (quesitem.Count() > 0)
                {
                    foreach (var item in quesitem)
                    {
                        items += "；" + item.F_ItemName;
                    }
                }

                dt.Rows[i]["F_Content"] = items.Trim('；');
            }

            var obj = new
            {
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());
        }
        public ActionResult GetQuestionModel(int id = 0)
        {
            if (id <= 0)
                return Error("参数不正确");
            var model = questionBLL.GetModel(id);
            if (model == null)
                return Error("当前数据不存在");
            return Success("获取试题成功", model);
        }
        public ActionResult CreateOrUpdateQuestion(QuesttionInput input)
        {
            DataTable dt = new DataTable();
            var model = new Model.T_Ask_Question();
            if (string.IsNullOrWhiteSpace(input.F_Title))
                return Error("请填写试题标题" + ",操作人：" + User.UserData["F_UserCode"]);
            //20180519 zhengbingbing
            #region 此模块暂时无用
            ////1问答题 2单选题 3多选题
            //if (input.F_Type > 1)
            //{
            //    //var questionItemList = questionItemBLL.DataTableToList(questionItemBLL.GetList($" F_QuestionId='{input.F_QuestionId}' ORDER BY F_Sort ").Tables[0]);
            //    #region 获取内容F_Content，格式：A.满意;B.一般;C.不满意;
            //    string[] arr1 = new string[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20" };
            //    string[] arr2 = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T" };
            //    dt = new BLL.T_Ask_QuestionItems().GetList(" F_QuestionId=" + input.F_QuestionId + " ORDER BY F_Sort ").Tables[0];
            //    for (int i = 0; i < dt.Rows.Count; i++)
            //    {
            //        string vsort = "";
            //        if (input.F_SortModel == 1)
            //        {
            //            vsort = arr2[i];
            //        }
            //        else
            //        {
            //            vsort = arr1[i];
            //        }
            //        input.F_Content += vsort + "." + dt.Rows[i]["F_ItemName"].ToString().Trim() + ";";
            //    }
            //    model.F_SortModel = input.F_SortModel;
            //    #endregion
            //}
            //else
            //{
            //    model.F_SortModel = null;
            //}
            #endregion
            if (input.F_QuestionId <= 0)
            {
                //model.F_SortModel = input.F_SortModel;
                model.F_QuestionId = Utils.StrToInt(User.UserData["F_UserID"], 0);
                model.F_CategoryId = input.F_CategoryId;
                model.F_Type = input.F_Type;
                model.F_Title = input.F_Title;
                model.F_Content = input.F_Content;
                model.F_Remark = input.F_Remark;
                model.F_CreateOn = DateTime.Now;
                model.F_CreateBy = Utils.StrToInt(User.UserData["F_UserID"], 0);
                model.F_ModifyOn = DateTime.Now;
                model.F_ModifyBy = Utils.StrToInt(User.UserData["F_UserID"], 0);
                model.F_DeleteFlag = 0;
                if (questionBLL.Add(model) > 0)
                    return Success("添加试题成功" + ",操作人：" + User.UserData["F_UserCode"]);
                return Error("添加试题失败" + ",操作人：" + User.UserData["F_UserCode"]);
            }
            model = questionBLL.GetModel(input.F_QuestionId);
            //model.F_SortModel = input.F_SortModel;
            model.F_CategoryId = input.F_CategoryId;
            model.F_Title = input.F_Title;
            model.F_Type = input.F_Type;
            model.F_Content = input.F_Content;
            model.F_Remark = input.F_Remark;
            model.F_ModifyOn = DateTime.Now;
            model.F_ModifyBy = Utils.StrToInt(User.UserData["F_UserID"], 0);
            if (questionBLL.Update(model))
                return Success("修改试题成功" + ",操作人：" + User.UserData["F_UserCode"]);
            return Error("修改试题失败" + ",操作人：" + User.UserData["F_UserCode"]);
        }
        /// <summary>
        ///  删除试题列表(删除两表信息)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult DeleteQuestion(int id = 0)
        {
            if (questionBLL.Delete(id))
            {
                questionItemBLL.DeleteByQuestionId(id); ;
                return Success("删除试题成功" + ",操作人：" + User.UserData["F_UserCode"]); ;
            }
            else
            {
                return Error("删除试题失败!" + ",操作人：" + User.UserData["F_UserCode"]);
            }
        }
        #endregion

        #region 试题管理 - 试题列表 -- 问题答案管理
        public ActionResult GetQuestionItemList(int pagesize = 10, int pageindex = 1, int id = 0)
        {
            //if (!Request.IsAuthenticated)
            //    return NoToken("未知错误，请重新登录");
            var sql = "";
            if (id > 0)
            {
                sql = " and F_QuestionId=" + id;
            }
            var recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                    "T_Ask_QuestionItems",
                    "F_ItemId",
                    "*",
                    "  " + sql,
                    "ORDER BY F_Sort ",
                    pagesize,
                    pageindex,
                    true,
                    out recordCount);
            var obj = new
            {
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());
        }
        public ActionResult GetQuestionItemModel(int id = 0)
        {
            var model = questionItemBLL.GetModel(id);
            if (model == null)
                return Error("当前数据不存在");
            return Success("", model);
        }
        public ActionResult CreateOrUpdateQuestionItem(QuesttionItemInput input)
        {
            var model = new Model.T_Ask_QuestionItems();
            if (input.Id <= 0)
            {
                model.F_ItemName = input.Name;
                model.F_QuestionId = input.QuestionId;
                model.F_Remark = input.Remark;
                //model.F_IsAnswer = input.IsAnswer;
                //model.F_Sort = input.Sort;
                //model.F_SortModel = input.SortMode;
                if (questionItemBLL.Add(model) > 0)
                    return Success("添加试题选项成功" + ",操作人：" + User.UserData["F_UserCode"]);
                return Error("添加试题选项失败" + ",操作人：" + User.UserData["F_UserCode"]);
            }
            model = questionItemBLL.GetModel(input.Id);
            model.F_ItemName = input.Name;
            model.F_QuestionId = input.QuestionId;
            model.F_Remark = input.Remark;
            //model.F_IsAnswer = input.IsAnswer;
            //model.F_Sort = input.Sort;
            //model.F_SortModel = input.SortMode;
            if (questionItemBLL.Update(model))
                return Success("修改试题选项成功" + ",操作人：" + User.UserData["F_UserCode"]);
            return Error("修改试题选项失败" + ",操作人：" + User.UserData["F_UserCode"]);
        }
        public ActionResult DeleteQuestionItme(int id = 0)
        {
            if (questionItemBLL.Delete(id))
                return Success("删除试题选项成功" + ",操作人：" + User.UserData["F_UserCode"]);
            return Error("删除试题选项失败" + ",操作人：" + User.UserData["F_UserCode"]);
        }
        #endregion

        #region 问卷调查报告数据简单查询
        public ActionResult GetReport()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            string TaskName = HttpUtility.UrlDecode(RequestString.GetQueryString("taskname"));
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("wenti");
            dtnew.Columns.Add("xuanxiang");
            dtnew.Columns.Add("shuliang");
            dtnew.Columns.Add("bili");
            //根据任务名获取任务ID
            string TaskID = "";
            var objo = DbHelperSQL.GetSingle("select F_TaskID from T_Call_OutTask where F_TaskName='" + TaskName + "'");
            if (objo != null)
            {
                TaskID = objo.ToString();
            }

            //List<Model.T_Ask_Question> qrlist = new List<Model.T_Ask_Question>();
            DataSet ds = DbHelperSQL.Query("SELECT * FROM T_Ask_Question WHERE F_QuestionId IN (select ta.F_QuestionId from  T_Ask_PagerItems ta where ta.F_PagerId=(select F_PagerID from T_Call_OutTask where F_TaskID='" + TaskID + "') ) and F_Type!=1");
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string content = dt.Rows[i]["F_Content"].ToString();
                    string question = dt.Rows[i]["F_Title"].ToString();
                    string[] item = content.Split(';');
                    //根据问题ID获取当前问题各选项答案总数
                    int totalnum = 0;
                    object obj0 = DbHelperSQL.GetSingle("select COUNT(1) from T_Call_OutAnswers WHERE F_TaskID='" + TaskID + "'  and F_QID='" + dt.Rows[i]["F_QuestionId"].ToString() + "' ");
                    if (obj0 != null && obj0.ToString() != "")
                    {
                        totalnum = Convert.ToInt32(obj0);
                    }
                    //根据问题ID查询问题项
                    DataSet dsi = DbHelperSQL.Query("SELECT* FROM T_Ask_QuestionItems where F_QuestionId = '" + dt.Rows[i]["F_QuestionId"].ToString() + "'");
                    if (dsi != null && dsi.Tables.Count > 0)
                    {
                        for (int j = 0; j < dsi.Tables[0].Rows.Count; j++)
                        {
                            string itemname = dsi.Tables[0].Rows[j]["F_ItemName"].ToString();
                            DataRow dr = dtnew.NewRow();
                            dr["wenti"] = question;
                            dr["xuanxiang"] = itemname;
                            //DataSet dss = DbHelperSQL.Query("select COUNT(1) from T_Call_OutAnswers WHERE F_TaskID='"+ TaskID + "'  and F_Answer='"+ item[j].ToString() + "' group by F_QID");
                            object obj = DbHelperSQL.GetSingle("select COUNT(1) from T_Call_OutAnswers WHERE F_TaskID='" + TaskID + "'  and F_Answer='" + itemname + "' and F_QID='" + dt.Rows[i]["F_QuestionId"].ToString() + "' ");
                            if (obj != null && obj.ToString() != "")
                            {
                                int num = Convert.ToInt32(obj);
                                dr["shuliang"] = num;
                                if (totalnum != 0)
                                {
                                    if (num > 0)
                                    {
                                        dr["bili"] = (num / totalnum * 100).ToString() + "%";
                                    }
                                    else
                                    {
                                        dr["bili"] = "0";
                                    }
                                }
                                else
                                { dr["bili"] = "0"; }
                            }
                            else
                            {
                                dr["shuliang"] = "0";
                                dr["bili"] = "0";
                            }
                            dtnew.Rows.Add(dr);
                        }
                    }
                    //for (int j = 0; j < item.Length; j++)
                    //{
                    //    if (item[j].Length > 0)
                    //    {
                    //        DataRow dr = dtnew.NewRow();
                    //        dr["wenti"] = question;
                    //        dr["xuanxiang"] = item[j].ToString();
                    //        //DataSet dss = DbHelperSQL.Query("select COUNT(1) from T_Call_OutAnswers WHERE F_TaskID='"+ TaskID + "'  and F_Answer='"+ item[j].ToString() + "' group by F_QID");
                    //        object obj = DbHelperSQL.GetSingle("select COUNT(1) from T_Call_OutAnswers WHERE F_TaskID='" + TaskID + "'  and F_Answer='" + item[j].ToString() + "' group by F_QID");
                    //        if (obj != null && obj.ToString() != "")
                    //        {
                    //            int num = Convert.ToInt32(obj);
                    //            dr["shuliang"] = num;
                    //            if (totalnum != 0)
                    //            {
                    //                dr["bili"] = (num / totalnum * 100).ToString() + "%";
                    //            }
                    //        }
                    //        dtnew.Rows.Add(dr);
                    //    }
                    //}

                }
            }
            res = Success("获取调查报表数据成功", dtnew);

            return res;
        }
        #endregion

        #region 问卷调查报表数据查询
        List<string> arlist = new List<string>();//表头问题分项
        List<string> checklist = new List<string>();//查询条件问题分项
        DataTable dtNews = new DataTable();
        DataTable dtCol = new DataTable();
        int columnsnum = 1;//左边留一空列，用于显示查询条件答案
        int questioncount = 0;
        //获取表头
        public ActionResult GetColumnList()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            string TaskName = HttpUtility.UrlDecode(RequestString.GetQueryString("taskname"));
            string Question = HttpUtility.UrlDecode(RequestString.GetQueryString("question"));
            //TaskName = "白居易";
            string TaskID = "";
            var obj = DbHelperSQL.GetSingle("select F_TaskID from T_Call_OutTask where F_TaskName='" + TaskName + "'");
            if (obj != null)
            {
                TaskID = obj.ToString();
            }

            columnsnum = 1;
            arlist.Clear();
            checklist.Clear();
            //dtCol.Rows.Clear();
            //dtCol.Columns.Clear();

            dtCol.Columns.Add("wenti", Type.GetType("System.String"));
            //dtCol.Columns.Add(new DataColumn("wenti", Type.GetType("System.String")));
            dtCol.Columns.Add("fenxiangshu", Type.GetType("System.Int32"));
            dtCol.Columns.Add("wentiid", Type.GetType("System.String"));
            DataSet ds = DbHelperSQL.Query("SELECT * FROM T_Ask_Question WHERE F_QuestionId IN (select ta.F_QuestionId from  T_Ask_PagerItems ta where ta.F_PagerId=(select F_PagerID from T_Call_OutTask where F_TaskID='" + TaskID + "') ) and F_Type!=1");
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                questioncount = dt.Rows.Count;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string content = dt.Rows[i]["F_Content"].ToString();//问题选项
                    string questions = dt.Rows[i]["F_Title"].ToString();//问题
                    string questionid = dt.Rows[i]["F_QuestionId"].ToString();//问题
                    string[] rowheader = content.Split(';');

                    if (questions != Question)
                    {
                        DataRow dr = dtCol.NewRow();
                        dr["wenti"] = questions;

                        //根据问题ID查询问题项
                        DataSet dsi = DbHelperSQL.Query("SELECT* FROM T_Ask_QuestionItems where F_QuestionId = (select F_QuestionId from T_Ask_Question where F_Title='" + questions + "')");
                        if (dsi != null && dsi.Tables.Count > 0)
                        {
                            for (int j = 0; j < dsi.Tables[0].Rows.Count; j++)
                            {
                                arlist.Add(dsi.Tables[0].Rows[j]["F_ItemName"].ToString());
                                columnsnum++;
                            }
                            dr["fenxiangshu"] = dsi.Tables[0].Rows.Count;
                        }
                        //dr["fenxiangshu"] = dt.Rows.Count;
                        dr["wentiid"] = questionid;
                        dtCol.Rows.Add(dr);
                        //    for (int j = 0; j < rowheader.Length; j++)
                        //{
                        //    if (rowheader[j].Length > 0)
                        //    {
                        //        arlist.Add(rowheader[j]);
                        //        columnsnum++;
                        //    }
                        //}
                    }
                    else
                    {
                        //根据问题ID查询问题项
                        DataSet dsi = DbHelperSQL.Query("SELECT* FROM T_Ask_QuestionItems where F_QuestionId = (select F_QuestionId from T_Ask_Question where F_Title='" + questions + "')");
                        if (dsi != null && dsi.Tables.Count > 0)
                        {
                            for (int j = 0; j < dsi.Tables[0].Rows.Count; j++)
                            {
                                checklist.Add(dsi.Tables[0].Rows[j]["F_ItemName"].ToString());

                            }
                        }
                        //for (int j = 0; j < rowheader.Length; j++)
                        //{
                        //    if (rowheader[j].Length > 0)
                        //    {
                        //        checklist.Add(rowheader[j]);
                        //    }
                        //}
                    }
                }
            }

            res = Success("获取调查问卷表头成功", dtCol);

            return res;
        }

        //获取数据
        public ActionResult GetDataList()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            string TaskName = HttpUtility.UrlDecode(RequestString.GetQueryString("taskname"));
            string Question = HttpUtility.UrlDecode(RequestString.GetQueryString("question"));

            int index1 = 0;
            int index2 = 0;
            GetColumnList();
            int rowcount = checklist.Count + 1;//行数

            string[,] newdata = new string[rowcount, columnsnum];
            string TaskID = "";
            var obj0 = DbHelperSQL.GetSingle("select F_TaskID from T_Call_OutTask where F_TaskName='" + TaskName + "'");
            if (obj0 != null)
            {
                TaskID = obj0.ToString();
            }
            string QID = "";
            var obj1 = DbHelperSQL.GetSingle("select F_QuestionId from T_Ask_Question where F_Title='" + Question + "' and F_Type!=1");
            if (obj1 != null)
            {
                QID = obj1.ToString();
            }
            //for (int i = 0; i < columnsnum; i++)
            //{
            //    dtNews.Columns.Add("Column" + (i + 1).ToString(), Type.GetType("System.String"));
            //}
            //添加问题选项
            //DataRow dr = dtNews.NewRow();
            //dr["Column1"] = "";
            newdata[index1, index2] = "";
            index2++;
            for (int j = 0; j < arlist.Count; j++)
            {
                //dr["Column" + (j + 2).ToString()] = arlist[j];
                newdata[index1, index2] = arlist[j];
                index2++;
            }
            //dtNews.Rows.Add(dr);
            index1++;//第一行添加结束

            //获取百分比
            List<string> rowlist = new List<string>();

            foreach (string items in checklist)
            {
                //rowlist.Clear();
                //rowlist.Add(items);
                int colnum = 2;
                index2 = 0;
                DataRow drr = dtNews.NewRow();
                //drr["Column1"] = items;
                newdata[index1, index2] = items;
                int n = 0;
                int len = 0;
                for (int m = 0; m < dtCol.Rows.Count; m++)
                {

                    //根据问题查询回答该问题的总次数
                    int totalnum = 0;
                    int num = 0;
                    object obj = DbHelperSQL.GetSingle("SELECT COUNT(1) FROM T_Call_OutAnswers WHERE F_CusTelID IN (select F_CusTelID from T_Call_OutAnswers where F_TaskID='" + TaskID + "' AND F_QID='" + QID + "' AND F_Answer='" + items + "') AND F_QID='" + dtCol.Rows[m]["wentiid"].ToString() + "'");//
                    if (obj != null && obj.ToString() != "")
                    {
                        totalnum = Convert.ToInt32(obj);
                    }
                    int fenxiang = Convert.ToInt32(dtCol.Rows[m]["fenxiangshu"].ToString());
                    len = len + fenxiang;
                    while (n < len)
                    {
                        index2++;
                        //同一问题下的分项
                        object obj2 = DbHelperSQL.GetSingle("SELECT COUNT(1) FROM T_Call_OutAnswers WHERE F_CusTelID IN (select F_CusTelID from T_Call_OutAnswers where F_TaskID='" + TaskID + "' AND F_QID='" + QID + "' AND F_Answer='" + items + "') AND F_QID='" + dtCol.Rows[m]["wentiid"].ToString() + "' and  F_Answer='" + newdata[0, n + 1] + "'");//dtNews.Rows[0]["Column" + colnum.ToString()].ToString()
                        if (obj2 != null && obj2.ToString() != "")
                        {
                            num = Convert.ToInt32(obj2);
                        }
                        if (totalnum > 0)
                        {
                            //drr["Column" + colnum.ToString()] = (num / totalnum * 100).ToString() + "%";
                            if (num > 0)
                            {
                                newdata[index1, index2] = (num / totalnum * 100).ToString() + "%";
                            }
                            else
                            { newdata[index1, index2] = "0"; }
                        }
                        else
                        { newdata[index1, index2] = "0"; }
                        n++;

                    }
                    colnum++;
                }
                index1++;
                //dtNews.Rows.Add(drr);
            }
            res = Success("获取调查问卷报表数据成功", newdata);

            return res;
        }
        #endregion

        #region 查询条件
        /// <summary>
        /// 获取任务名列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTaskList()
        {
            if (Request.IsAuthenticated)
            {
                DataTable dt = new DataTable();
                dt = new BLL.T_Call_OutTask().GetList("  F_DeleteFlag=0 ").Tables[0];
                return Success("加载成功", dt);
            }
            return NoToken("未知错误，请重新登录");
        }

        /// <summary>
        /// 获取问题列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetQList()
        {
            if (Request.IsAuthenticated)
            {
                DataTable dt = new DataTable();
                dt = new BLL.T_Ask_Question().GetList(" F_Type!=1").Tables[0];
                return Success("加载成功", dt);
            }
            return NoToken("未知错误，请重新登录");
        }
        #endregion

        //导出Excel
        #region 导出Excel
        #region 问卷调查报告数据简单查询导出Excel
        public ActionResult GetReportExpt()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            string TaskName = HttpUtility.UrlDecode(RequestString.GetQueryString("taskname"));
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("wenti");
            dtnew.Columns.Add("xuanxiang");
            dtnew.Columns.Add("shuliang");
            dtnew.Columns.Add("bili");
            //根据任务名获取任务ID
            string TaskID = "";
            var objo = DbHelperSQL.GetSingle("select F_TaskID from T_Call_OutTask where F_TaskName='" + TaskName + "'");
            if (objo != null)
            {
                TaskID = objo.ToString();
            }

            //List<Model.T_Ask_Question> qrlist = new List<Model.T_Ask_Question>();
            DataSet ds = DbHelperSQL.Query("SELECT * FROM T_Ask_Question WHERE F_QuestionId IN (select ta.F_QuestionId from  T_Ask_PagerItems ta where ta.F_PagerId=(select F_PagerID from T_Call_OutTask where F_TaskID='" + TaskID + "') ) and F_Type!=1");
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string content = dt.Rows[i]["F_Content"].ToString();
                    string question = dt.Rows[i]["F_Title"].ToString();
                    string[] item = content.Split(';');
                    //根据问题ID获取当前问题各选项答案总数
                    int totalnum = 0;
                    object obj0 = DbHelperSQL.GetSingle("select COUNT(1) from T_Call_OutAnswers WHERE F_TaskID='" + TaskID + "'  and F_QID='" + dt.Rows[i]["F_QuestionId"].ToString() + "' ");
                    if (obj0 != null && obj0.ToString() != "")
                    {
                        totalnum = Convert.ToInt32(obj0);
                    }
                    //根据问题ID查询问题项
                    DataSet dsi = DbHelperSQL.Query("SELECT* FROM T_Ask_QuestionItems where F_QuestionId = '" + dt.Rows[i]["F_QuestionId"].ToString() + "'");
                    if (dsi != null && dsi.Tables.Count > 0)
                    {
                        for (int j = 0; j < dsi.Tables[0].Rows.Count; j++)
                        {
                            string itemname = dsi.Tables[0].Rows[j]["F_ItemName"].ToString();
                            DataRow dr = dtnew.NewRow();
                            dr["wenti"] = question;
                            dr["xuanxiang"] = itemname;
                            //DataSet dss = DbHelperSQL.Query("select COUNT(1) from T_Call_OutAnswers WHERE F_TaskID='"+ TaskID + "'  and F_Answer='"+ item[j].ToString() + "' group by F_QID");
                            object obj = DbHelperSQL.GetSingle("select COUNT(1) from T_Call_OutAnswers WHERE F_TaskID='" + TaskID + "'  and F_Answer='" + itemname + "' and F_QID='" + dt.Rows[i]["F_QuestionId"].ToString() + "' ");
                            if (obj != null && obj.ToString() != "")
                            {
                                int num = Convert.ToInt32(obj);
                                dr["shuliang"] = num;
                                if (totalnum != 0)
                                {
                                    if (num > 0)
                                    {
                                        dr["bili"] = (num / totalnum * 100).ToString() + "%";
                                    }
                                    else
                                    {
                                        dr["bili"] = "0";
                                    }
                                }
                                else
                                { dr["bili"] = "0"; }
                            }
                            else
                            {
                                dr["shuliang"] = "0";
                                dr["bili"] = "0";
                            }
                            dtnew.Rows.Add(dr);
                        }
                    }

                }
            }
            //导出dtnew
            NPOIHelper npoi = new NPOIHelper();
            string[] col = { "问题", "选项", "数量", "比例" };
            if (npoi.ExportToExcel("问卷调查报告统计数据", dtnew, col) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
        #endregion

        #region 导出按任务和问题查询出的数据      
        public ActionResult GetDataListExpt()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            string TaskName = HttpUtility.UrlDecode(RequestString.GetQueryString("taskname"));
            string Question = HttpUtility.UrlDecode(RequestString.GetQueryString("question"));

            GetColumnList();
            string TaskID = "";
            var obj0 = DbHelperSQL.GetSingle("select F_TaskID from T_Call_OutTask where F_TaskName='" + TaskName + "'");
            if (obj0 != null)
            {
                TaskID = obj0.ToString();
            }
            string QID = "";
            var obj1 = DbHelperSQL.GetSingle("select F_QuestionId from T_Ask_Question where F_Title='" + Question + "'  and F_Type!=1");
            if (obj1 != null)
            {
                QID = obj1.ToString();
            }
            for (int i = 0; i < columnsnum; i++)
            {
                dtNews.Columns.Add("Column" + (i + 1).ToString(), Type.GetType("System.String"));
            }
            //添加第一行
            DataRow dr0 = dtNews.NewRow();
            dr0["Column1"] = Question;
            int cindex = 2;
            for (int i = 0; i < dtCol.Rows.Count; i++)
            {
                int itemcount = Convert.ToInt32(dtCol.Rows[i]["fenxiangshu"]);
                for (int j = 0; j < itemcount; j++)
                {
                    dr0["Column" + cindex] = dtCol.Rows[i]["wenti"];
                    cindex++;
                }
            }
            dtNews.Rows.Add(dr0);
            //添加问题选项
            DataRow dr = dtNews.NewRow();
            dr["Column1"] = "";
            for (int j = 0; j < arlist.Count; j++)
            {
                dr["Column" + (j + 2).ToString()] = arlist[j];
            }
            dtNews.Rows.Add(dr);

            //获取百分比
            List<string> rowlist = new List<string>();

            foreach (string items in checklist)
            {
                //rowlist.Clear();
                //rowlist.Add(items);
                int colnum = 2;
                int n = 0;
                int len = 0;
                DataRow drr = dtNews.NewRow();
                drr["Column1"] = items;
                for (int m = 0; m < dtCol.Rows.Count; m++)
                {
                    //根据问题查询回答该问题的总次数
                    int totalnum = 0;
                    int num = 0;
                    object obj = DbHelperSQL.GetSingle("SELECT COUNT(1) FROM T_Call_OutAnswers WHERE F_CusTelID IN (select F_CusTelID from T_Call_OutAnswers where F_TaskID='" + TaskID + "' AND F_QID='" + QID + "' AND F_Answer='" + items + "') AND F_QID='" + dtCol.Rows[m]["wentiid"].ToString() + "'");//
                    if (obj != null && obj.ToString() != "")
                    {
                        totalnum = Convert.ToInt32(obj);
                    }
                    #region
                    int fenxiang = Convert.ToInt32(dtCol.Rows[m]["fenxiangshu"].ToString());
                    len = len + fenxiang;
                    while (n < len)
                    {
                        //同一问题下的分项
                        object obj2 = DbHelperSQL.GetSingle("SELECT COUNT(1) FROM T_Call_OutAnswers WHERE F_CusTelID IN (select F_CusTelID from T_Call_OutAnswers where F_TaskID='" + TaskID + "' AND F_QID='" + QID + "' AND F_Answer='" + items + "') AND F_QID='" + dtCol.Rows[m]["wentiid"].ToString() + "' and  F_Answer='" + dtNews.Rows[1]["Column" + colnum.ToString()].ToString() + "'");
                        if (obj2 != null && obj2.ToString() != "")
                        {
                            num = Convert.ToInt32(obj2);
                        }
                        if (totalnum > 0)
                        {
                            //drr["Column" + colnum.ToString()] = (num / totalnum * 100).ToString() + "%";
                            if (num > 0)
                            {
                                drr["Column" + colnum.ToString()] = (num / totalnum * 100).ToString() + "%";
                            }
                            else
                            { drr["Column" + colnum.ToString()] = "0"; }
                        }
                        else
                        { drr["Column" + colnum.ToString()] = "0"; }
                        n++;
                        colnum++;
                    }
                    #endregion
                    #region 无用
                    //同一问题下的分项
                    /*object obj2 = DbHelperSQL.GetSingle("SELECT COUNT(1) FROM T_Call_OutAnswers WHERE F_CusTelID IN (select F_CusTelID from T_Call_OutAnswers where F_TaskID='" + TaskID + "' AND F_QID='" + QID + "' AND F_Answer='" + items + "') AND F_QID='" + dtCol.Rows[m]["wentiid"].ToString() + "' and  F_Answer='" + dtNews.Rows[1]["Column" + colnum.ToString()].ToString() + "'");//
                    if (obj2 != null && obj2.ToString() != "")
                    {
                        num = Convert.ToInt32(obj2);
                    }
                    if (totalnum > 0)
                    {
                        if (num > 0)
                        {
                            drr["Column" + colnum.ToString()] = (num / totalnum * 100).ToString() + "%";
                        }
                        else
                        { drr["Column" + colnum.ToString()] = "0"; }
                    }*/
                    #endregion

                }
                dtNews.Rows.Add(drr);
            }
            //res = Success("获取调查问卷报表数据成功", dtNews);

            //return res;
            //导出dtnew
            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel2("问卷调查报告统计数据", dtNews) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
        #endregion
        #endregion

        //2017-11-14
        #region  数据查询--获取排名
        public ActionResult GetRank()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            string startdate = HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) + " 00:00:00";
            string enddate = HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) + " 23:59:59";
            //startdate = "2017-11-12 00:00:00";
            //enddate = "2017-11-14 23:59:59";
            if (HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) == null || HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) == "")
            {
                startdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                //enddate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            if (HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) == null || HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) == "")
            {
                //startdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                enddate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("xiangzhen", Type.GetType("System.String"));
            dtnew.Columns.Add("zongshu", Type.GetType("System.Int32"));
            dtnew.Columns.Add("buanquan", Type.GetType("System.Int32"));
            dtnew.Columns.Add("zhishu", Type.GetType("System.Decimal"));
            int leiji = 0;
            int buanquanleiji = 0;
            //List<Model.T_Ask_Question> qrlist = new List<Model.T_Ask_Question>();
            //DataSet ds = DbHelperSQL.Query("SELECT * FROM T_Ask_QuestionItems WHERE F_ItemId IN (SELECT F_QIID FROM T_Call_OutAnswers WHERE F_CusTelID IN (select F_CusTelID from T_Call_OutAnswers where F_OptOn between '"+startdate+"' and '"+enddate +"' and F_QID=102 ) and F_QID=101 group by F_QIID)");//and (F_QIID=101 OR F_QIID=102)
            DataSet ds = DbHelperSQL.Query("SELECT * FROM T_Ask_QuestionItems where F_QuestionId='101'");// WHERE F_ItemId IN (SELECT F_QIID FROM T_Call_OutAnswers WHERE F_CusTelID IN (select F_CusTelID from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=102 ) and F_QID=101 group by F_QIID)");
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dtnew.NewRow();
                    string content = dt.Rows[i]["F_ItemName"].ToString();
                    string ItemID = dt.Rows[i]["F_ItemID"].ToString();
                    dr["xiangzhen"] = content;
                    //根据乡镇获取不安全数
                    int unsafenum = 0;
                    object obj0 = DbHelperSQL.GetSingle("SELECT COUNT(1) as buanquanshu FROM T_Call_OutAnswers WHERE F_CusTelID IN  (select F_CusTelID from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=102 and (F_QIID=101 OR F_QIID=102)) and  F_QID=101 and F_QIID=" + ItemID);
                    if (obj0 != null && obj0.ToString() != "")
                    {
                        unsafenum = Convert.ToInt32(obj0);
                    }
                    buanquanleiji += unsafenum;
                    dr["buanquan"] = unsafenum;
                    //根据乡镇获取总数
                    int totalnum = 0;
                    object obj1 = DbHelperSQL.GetSingle("SELECT COUNT(*) from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=101 and F_QIID=" + ItemID + "");
                    if (obj1 != null && obj1.ToString() != "")
                    {
                        totalnum = Convert.ToInt32(obj1);
                    }
                    leiji += totalnum;
                    dr["zongshu"] = totalnum;
                    //计算比例
                    if (totalnum > 0)
                    {
                        decimal per = Math.Round(Convert.ToDecimal(Convert.ToDecimal(totalnum - unsafenum) / totalnum * 100), 1);
                        dr["zhishu"] = per;
                    }
                    else
                    { dr["zhishu"] = 0; }
                    dtnew.Rows.Add(dr);

                }

            }
            dtnew.DefaultView.Sort = "zhishu DESC";
            DataTable dtTemp = dtnew.DefaultView.ToTable();
            //计算总计
            DataRow dr0 = dtTemp.NewRow();
            dr0["xiangzhen"] = "总计";
            dr0["zongshu"] = leiji;
            dr0["buanquan"] = buanquanleiji;
            if (leiji > 0)
            {
                decimal per = Math.Round(Convert.ToDecimal(Convert.ToDecimal(leiji - buanquanleiji) / leiji * 100), 1);
                dr0["zhishu"] = per;
            }
            else
            { dr0["zhishu"] = 0; }
            dtTemp.Rows.Add(dr0);
            res = Success("获取乡镇报表数据成功", dtTemp);

            return res;
        }
        #endregion

        #region  数据查询--获取不安全详细信息
        public ActionResult GetUnsafeInfo()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            string startdate = HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) + " 00:00:00";
            string enddate = HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) + " 23:59:59";
            if (HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) == null || HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) == "")
            {
                startdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                //enddate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            if (HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) == null || HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) == "")
            {
                //startdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                enddate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            //startdate = "2017-11-12 00:00:00";
            //enddate = "2017-11-14 23:59:59";
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("xiangzhen", Type.GetType("System.String"));
            dtnew.Columns.Add("dianhua", Type.GetType("System.String"));
            dtnew.Columns.Add("zhiye", Type.GetType("System.String"));
            dtnew.Columns.Add("yuanyin", Type.GetType("System.String"));

            //List<Model.T_Ask_Question> qrlist = new List<Model.T_Ask_Question>();
            //DataSet ds = DbHelperSQL.Query("SELECT * FROM T_Ask_QuestionItems WHERE F_ItemId IN (SELECT F_QIID FROM T_Call_OutAnswers WHERE F_CusTelID IN (select F_CusTelID from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=102 and (F_QIID=101 OR F_QIID=102)) and F_QID=101 group by F_QIID)");
            DataSet ds = DbHelperSQL.Query("select F_CusTelID,F_TaskID from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=102 and (F_QIID=101 OR F_QIID=102)");
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dtnew.NewRow();
                    string taskid = dt.Rows[i]["F_TaskID"].ToString();
                    string custelid = dt.Rows[i]["F_CusTelID"].ToString();
                    //获取乡镇
                    string xiangzhen = "";
                    object obj0 = DbHelperSQL.GetSingle("SELECT F_Answer  FROM T_Call_OutAnswers WHERE F_CusTelID ='" + custelid + "' and F_TaskID='" + taskid + "'  and F_QID=101");
                    if (obj0 != null)
                    {
                        xiangzhen = obj0.ToString();
                    }
                    dr["xiangzhen"] = xiangzhen;
                    //获取电话
                    string dianhua = "";
                    object obj2 = DbHelperSQL.GetSingle("SELECT F_Phone  FROM T_Call_OutTaskTelNum WHERE F_Id ='" + custelid + "' and F_TaskID='" + taskid + "'  ");
                    if (obj2 != null)
                    {
                        dianhua = obj2.ToString();
                    }
                    dr["dianhua"] = dianhua;
                    //获取职业
                    string zhiye = "";
                    object obj1 = DbHelperSQL.GetSingle("SELECT F_Answer  FROM T_Call_OutAnswers WHERE F_CusTelID ='" + custelid + "' and F_TaskID='" + taskid + "'  and F_QID=100");
                    if (obj1 != null)
                    {
                        zhiye = obj1.ToString();
                    }
                    dr["zhiye"] = zhiye;
                    //获取原因
                    string yuanyin = "";
                    object obj3 = DbHelperSQL.GetSingle("SELECT F_Answer  FROM T_Call_OutAnswers WHERE F_CusTelID ='" + custelid + "' and F_TaskID='" + taskid + "'  and F_QID=108");
                    if (obj3 != null)
                    {
                        yuanyin = obj3.ToString();
                    }
                    dr["yuanyin"] = yuanyin;
                    dtnew.Rows.Add(dr);

                }

            }
            dtnew.DefaultView.Sort = "xiangzhen ASC";
            DataTable dtTemp = dtnew.DefaultView.ToTable();
            res = Success("获取不安全信息统计数据成功", dtTemp);

            return res;
        }
        #endregion

        #region 数据查询--获取不安全因素比例
        public ActionResult GetUnsafeRate()
        {
            DataTable dtnew = GetData();

            return Success("获取不安全原因比例报表数据成功", dtnew);
        }
        public ActionResult GetUnsafeRateExpt()
        {
            DataTable dtnew = GetData();
            NPOIHelper npoi = new NPOIHelper();
            string[] col = { "不安全原因", "数量", "比例" };
            if (npoi.ExportToExcel("乡镇排名统计数据", dtnew, col) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
        private DataTable GetData()
        {
            string startdate = HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) + " 00:00:00";
            string enddate = HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) + " 23:59:59";
            if (HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) == null || HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) == "")
            {
                startdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
            }
            if (HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) == null || HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) == "")
            {
                enddate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("Reasons", Type.GetType("System.String"));
            dtnew.Columns.Add("Counts", Type.GetType("System.String"));
            dtnew.Columns.Add("Rates", Type.GetType("System.String"));
            int sumcount = 0;
            DataSet ds = DbHelperSQL.Query("SELECT * FROM T_Ask_QuestionItems where F_QuestionId='103'");
            DataSet dsc = DbHelperSQL.Query("select F_QIID,COUNT(*) c from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=103 group by F_QIID");
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dtnew.NewRow();
                    string content = dt.Rows[i]["F_ItemName"].ToString();
                    string ItemID = dt.Rows[i]["F_ItemID"].ToString();
                    dr["Reasons"] = content;
                    int count = 0;
                    if (dsc != null && dsc.Tables.Count > 0)
                    {
                        DataTable dtc = dsc.Tables[0];
                        for (int j = 0; j < dtc.Rows.Count; j++)
                        {
                            if (dtc.Rows[j]["F_QIID"].ToString().Equals(dt.Rows[i]["F_ItemID"].ToString()))
                            {
                                count = int.Parse(dtc.Rows[j]["c"].ToString());
                                sumcount += count;
                                break;
                            }
                        }
                    }
                    dr["Counts"] = count;
                    dr["Rates"] = 0;

                    dtnew.Rows.Add(dr);
                }

                for (int z = 0; z < dtnew.Rows.Count; z++)
                {
                    int count = 0;
                    count = int.Parse(dtnew.Rows[z]["Counts"].ToString());
                    if (sumcount > 0)
                    {
                        decimal perww = Math.Round(Convert.ToDecimal(Convert.ToDecimal(count) / sumcount * 100), 1);
                        dtnew.Rows[z]["Rates"] = perww;
                    }
                }
            }
            return dtnew;
        }
        #endregion

        #region 导出
        #region  数据查询--获取排名
        public ActionResult GetRankExpt()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            string startdate = HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) + " 00:00:00";
            string enddate = HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) + " 23:59:59";
            if (HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) == null || HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) == "")
            {
                startdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                //enddate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            if (HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) == null || HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) == "")
            {
                //startdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                enddate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            //startdate = "2017-11-12 00:00:00";
            //enddate = "2017-11-14 23:59:59";
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("paiming", Type.GetType("System.String"));
            dtnew.Columns.Add("xiangzhen", Type.GetType("System.String"));
            dtnew.Columns.Add("zongshu", Type.GetType("System.Int32"));
            dtnew.Columns.Add("buanquan", Type.GetType("System.Int32"));
            dtnew.Columns.Add("zhishu", Type.GetType("System.Decimal"));
            int leiji = 0;
            int buanquanleiji = 0;
            //List<Model.T_Ask_Question> qrlist = new List<Model.T_Ask_Question>();
            //DataSet ds = DbHelperSQL.Query("SELECT * FROM T_Ask_QuestionItems WHERE F_ItemId IN (SELECT F_QIID FROM T_Call_OutAnswers WHERE F_CusTelID IN (select F_CusTelID from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=102 ) and F_QID=101 group by F_QIID)");//and (F_QIID=101 OR F_QIID=102)
            DataSet ds = DbHelperSQL.Query("SELECT * FROM T_Ask_QuestionItems where F_QuestionId='101'");
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dtnew.NewRow();
                    string content = dt.Rows[i]["F_ItemName"].ToString();
                    string ItemID = dt.Rows[i]["F_ItemID"].ToString();
                    dr["xiangzhen"] = content;
                    //根据乡镇获取不安全数
                    int unsafenum = 0;
                    object obj0 = DbHelperSQL.GetSingle("SELECT COUNT(1) as buanquanshu FROM T_Call_OutAnswers WHERE F_CusTelID IN  (select F_CusTelID from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=102 and (F_QIID=101 OR F_QIID=102)) and  F_QID=101 and F_QIID=" + ItemID);
                    if (obj0 != null && obj0.ToString() != "")
                    {
                        unsafenum = Convert.ToInt32(obj0);
                    }
                    buanquanleiji += unsafenum;
                    dr["buanquan"] = unsafenum;
                    //根据乡镇获取总数
                    int totalnum = 0;
                    object obj1 = DbHelperSQL.GetSingle("SELECT COUNT(*) from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=101 and F_QIID=" + ItemID + "");
                    if (obj1 != null && obj1.ToString() != "")
                    {
                        totalnum = Convert.ToInt32(obj1);
                    }
                    dr["zongshu"] = totalnum;
                    leiji += totalnum;
                    //计算比例
                    if (totalnum > 0)
                    {
                        decimal per = Math.Round(Convert.ToDecimal(Convert.ToDecimal(totalnum - unsafenum) / totalnum * 100), 1);
                        dr["zhishu"] = per;
                    }
                    else
                    { dr["zhishu"] = 0; }
                    dtnew.Rows.Add(dr);

                }

            }
            dtnew.DefaultView.Sort = "zhishu DESC";
            DataTable dtTemp = dtnew.DefaultView.ToTable();
            for (int h = 0; h < dtTemp.Rows.Count; h++)
            {
                dtTemp.Rows[h]["paiming"] = h + 1;
            }
            //计算总计
            DataRow dr0 = dtTemp.NewRow();
            dr0["paiming"] = "";
            dr0["xiangzhen"] = "总计";
            dr0["zongshu"] = leiji;
            dr0["buanquan"] = buanquanleiji;
            if (leiji > 0)
            {
                decimal per = Math.Round(Convert.ToDecimal(Convert.ToDecimal(leiji - buanquanleiji) / leiji * 100), 1);
                dr0["zhishu"] = per;
            }
            else
            { dr0["zhishu"] = 0; }
            dtTemp.Rows.Add(dr0);
            //res = Success("获取乡镇报表数据成功", dtTemp);

            //return res;
            //导出dtnew
            NPOIHelper npoi = new NPOIHelper();
            string[] col = { "排名", "乡镇", "总数", "不安全", "指数" };
            if (npoi.ExportToExcel("乡镇排名统计数据", dtTemp, col) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
        #endregion

        #region  数据查询--获取不安全详细信息
        public ActionResult GetUnsafeInfoExpt()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            string startdate = HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) + " 00:00:00";
            string enddate = HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) + " 23:59:59";
            if (HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) == null || HttpUtility.UrlDecode(RequestString.GetQueryString("startdate")) == "")
            {
                startdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                //enddate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            if (HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) == null || HttpUtility.UrlDecode(RequestString.GetQueryString("enddate")) == "")
            {
                //startdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                enddate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            //startdate = "2017-11-12 00:00:00";
            //enddate = "2017-11-14 23:59:59";
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("xiangzhen", Type.GetType("System.String"));
            dtnew.Columns.Add("dianhua", Type.GetType("System.String"));
            dtnew.Columns.Add("zhiye", Type.GetType("System.String"));
            dtnew.Columns.Add("yuanyin", Type.GetType("System.String"));

            //List<Model.T_Ask_Question> qrlist = new List<Model.T_Ask_Question>();
            //DataSet ds = DbHelperSQL.Query("SELECT * FROM T_Ask_QuestionItems WHERE F_ItemId IN (SELECT F_QIID FROM T_Call_OutAnswers WHERE F_CusTelID IN (select F_CusTelID from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=102 and (F_QIID=101 OR F_QIID=102)) and F_QID=101 group by F_QIID)");
            DataSet ds = DbHelperSQL.Query("select F_CusTelID,F_TaskID from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=102 and (F_QIID=101 OR F_QIID=102)");
            if (ds != null && ds.Tables.Count > 0)
            {

                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    //return Success("当前日期没有可导出的数据");


                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dtnew.NewRow();
                        string taskid = dt.Rows[i]["F_TaskID"].ToString();
                        string custelid = dt.Rows[i]["F_CusTelID"].ToString();
                        //获取乡镇
                        string xiangzhen = "";
                        object obj0 = DbHelperSQL.GetSingle("SELECT F_Answer  FROM T_Call_OutAnswers WHERE F_CusTelID ='" + custelid + "' and F_TaskID='" + taskid + "'  and F_QID=101");
                        if (obj0 != null)
                        {
                            xiangzhen = obj0.ToString();
                        }
                        dr["xiangzhen"] = xiangzhen;
                        //获取电话
                        string dianhua = "";
                        object obj2 = DbHelperSQL.GetSingle("SELECT F_Phone  FROM T_Call_OutTaskTelNum WHERE F_Id ='" + custelid + "' and F_TaskID='" + taskid + "'  ");
                        if (obj2 != null)
                        {
                            dianhua = obj2.ToString();
                        }
                        dr["dianhua"] = dianhua;
                        //获取职业
                        string zhiye = "";
                        object obj1 = DbHelperSQL.GetSingle("SELECT F_Answer  FROM T_Call_OutAnswers WHERE F_CusTelID ='" + custelid + "' and F_TaskID='" + taskid + "'  and F_QID=100");
                        if (obj1 != null)
                        {
                            zhiye = obj1.ToString();
                        }
                        dr["zhiye"] = zhiye;
                        //获取原因
                        string yuanyin = "";
                        object obj3 = DbHelperSQL.GetSingle("SELECT F_Answer  FROM T_Call_OutAnswers WHERE F_CusTelID ='" + custelid + "' and F_TaskID='" + taskid + "'  and F_QID=108");
                        if (obj3 != null)
                        {
                            yuanyin = obj3.ToString();
                        }
                        dr["yuanyin"] = yuanyin;
                        dtnew.Rows.Add(dr);

                    }
                }
            }
            dtnew.DefaultView.Sort = "xiangzhen ASC";
            DataTable dtTemp = dtnew.DefaultView.ToTable();
            //res = Success("获取不安全信息统计数据成功", dtTemp);

            //return res;
            //导出dtnew
            NPOIHelper npoi = new NPOIHelper();
            string[] col = { "乡(镇、街道)", "机主电话", "职业", "原因" };
            if (npoi.ExportToExcel("不安全信息统计数据", dtTemp, col) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
        #endregion
        #endregion
    }
}