HeBi12345_API/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/weixin/WxLoginContr...

using CallCenter.Utility;
using CallCenterApi.Common;
using CallCenterApi.DB;
using CallCenterApi.Interface.App_Start;
using CallCenterApi.Interface.Controllers.Base;
using CallCenterApi.Interface.Controllers.Sms;
using CallCenterApi.Model;
using CallCenterAPI.WechatSDK;
using CallCenterAPI.WechatSDK.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace CallCenterApi.Interface.Controllers.weixin
{
    
    public class WxLoginController : BaseController
    {
        private readonly BLL.T_Sys_UserAccount userAccountBLL = new BLL.T_Sys_UserAccount();
        private readonly BLL.T_Sys_RoleInfo roleBLL = new BLL.T_Sys_RoleInfo();
        /// <summary>
        /// 绑定微信号
        /// </summary>
        /// <param name="login"></param>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult Login(WxLoginDto login)
        {
            string rolecode = "";
            Dictionary<string, string> paras = new Dictionary<string, string>();
            string sql = " select * from T_Sys_UserAccount where F_UserCode=@F_UserCode and F_PassWord=@F_PassWord ";
            paras.Add("@F_UserCode", login.UserCode);
            paras.Add("@F_PassWord", login.Password);

            var dt = DbHelperSQL.Query(sql, paras).Tables[0];
            if (dt != null)
            {
                var user = userAccountBLL.GetModel(login.UserCode);
                user.F_WxOpenId = login.OpenId;
                if (userAccountBLL.Update(user))
                {
                    var rolemodel = roleBLL.GetModel(user.F_RoleId);
                    if (rolemodel != null)
                        rolecode = rolemodel.F_RoleCode;

                    var obj = new
                    {
                        openid = login.OpenId,
                        usercode = login.UserCode,
                        rolecode = rolecode

                    };
                    return Success("绑定成功", obj);
                }
                else
                {
                    return Error("绑定失败");
                }
            }
            else
            {
                return Error("账号或密码错误，请重新登录");
            }
        }




        /// <summary>
        /// 获取微信openid
        /// </summary>
        /// <param name="login"></param>
        /// <returns></returns>
        public ActionResult GetOpenId(WxLoginDto wld)
        {
            wld.RedirectUrl = RequestString.GetUrlReferrer();

            if (string.IsNullOrEmpty(wld.OpenId))
            {
                var temp = WxHelper.GetOpenId(wld);
                if (string.IsNullOrWhiteSpace(temp.OpenId))
                {
                    return Redirect("请求", temp.RedirectUrl);
                }
                wld.OpenId = temp.OpenId;
            }
            var model = new BLL.T_Sys_Users().GetModel(wld.OpenId);
            if (model == null)
            {
                model = new Model.T_Sys_Users();
                model.F_OpenId = wld.OpenId;
                model.F_Type = (int)EnumUserType.wechat;//1;
                model.F_CreateTime = DateTime.Now;
                new BLL.T_Sys_Users().Add(model);
            }

            return Success("成功", wld.OpenId);
        }

        /// <summary>
        /// 获取微信用户信息
        /// </summary>
        /// <param name="login"></param>
        /// <returns></returns>
        public ActionResult GetUserInfo(WxLoginDto wld)
        {
            wld.RedirectUrl = RequestString.GetUrlReferrer();

            if (string.IsNullOrEmpty(wld.OpenId))
            {
                var temp = WxHelper.GetUserInfo(wld);
                if (string.IsNullOrWhiteSpace(temp.OpenId))
                {
                    return Redirect("请求", temp.RedirectUrl);
                }
                wld.OpenId = temp.OpenId;
                wld.UserInfo = temp.UserInfo;
            }
            var model = new BLL.T_Sys_Users().GetModel(wld.OpenId);
            if (model == null)
            {
                model = new Model.T_Sys_Users();
                model.F_OpenId = wld.OpenId;
                model.F_Name = wld.UserInfo.nickname;
                model.F_ImgUrl = wld.UserInfo.headimgurl;
                model.F_Province = wld.UserInfo.province;
                model.F_City = wld.UserInfo.city;
                model.F_County = wld.UserInfo.country;
                model.F_Type = (int)EnumUserType.wechat;//1;
                model.F_CreateTime = DateTime.Now;
                new BLL.T_Sys_Users().Add(model);
            }
            return Success("成功", model);
        }
        /// <summary>
        /// 发送短信验证码
        /// </summary>
        /// <param name="mobile"></param>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult SendCode(string mobile)
        {

            int codes = new Random().Next(100000, 999999);
            string mag = "[\"" + codes.ToString() + "\"]";
            string v = SmsNewController.AddSmS(0, "您本次登录的验证码是" + codes.ToString() + "，10分钟内有效。", mobile, "1053396176871632896", mag,
           "");
            if (v == "")
            {
                return Error("发送成功");
            }
            else
            {
                return Error("发送失败");
            }
        }
        /// <summary>
        /// 修改微信用户信息
        /// </summary>
        /// <param name="login"></param>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult EditUserInfo(WxLoginDto wld,string code,string mobile)
        {
            wld.RedirectUrl = RequestString.GetUrlReferrer();

            if (string.IsNullOrEmpty(wld.OpenId))
            {
                var temp = WxHelper.GetUserInfo(wld);
                if (string.IsNullOrWhiteSpace(temp.OpenId))
                {
                    return Redirect("请求", temp.RedirectUrl);
                }
                wld.OpenId = temp.OpenId;
                wld.UserInfo = temp.UserInfo;
            }
            if (string.IsNullOrEmpty(mobile))
                return Error("请输入手机号码");
            string msgcount = "您的验证码是" + code + "，10分钟内有效。";
            if (string.IsNullOrEmpty(code))
                return Error("请输入验证码");
            var sms = new BLL.T_SMS_RecvSMS().GetModelList("Content='" + msgcount + "' and CallerNum='" + mobile + "'order by RecvTime desc");
            if (sms != null && sms.Count > 0)
            {
                var modelSms = sms.First();
                if ((DateTime.Now - modelSms.RecvTime).Minutes > 10)
                {
                    return Error("验证码已失效");
                }
            }
            else
                return Error("请输入正确验证码");



            var model = new BLL.T_Sys_Users().GetModel(wld.OpenId);
            if (model == null)
            {
                model = new Model.T_Sys_Users();
                model.F_OpenId = wld.OpenId;
                model.F_Name = wld.UserInfo.nickname;
                model.F_ImgUrl = wld.UserInfo.headimgurl;
                model.F_Province = wld.UserInfo.province;
                model.F_City = wld.UserInfo.city;
                model.F_Telphone = mobile;
                model.F_County = wld.UserInfo.country;
                model.F_Type = (int)EnumUserType.wechat;//1;
                model.F_CreateTime = DateTime.Now;
                new BLL.T_Sys_Users().Add(model);
            }
            else
            {
                model.F_Telphone = mobile;
                new BLL.T_Sys_Users().Update (model);
            }
            return Success("成功", wld.UserInfo);
        }

        /// <summary>
        /// 解除微信用户信息
        /// </summary>
        /// <param name="login"></param>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult RelieveUserInfo(WxLoginDto wld, string code, string mobile)
        {
            wld.RedirectUrl = RequestString.GetUrlReferrer();
            if (string.IsNullOrEmpty(mobile))
                return Error("请输入手机号码");
            string msgcount = "您的验证码是" + code + "，10分钟内有效。";
            if (string.IsNullOrEmpty(code))
                return Error("请输入验证码");
            var sms = new BLL.T_SMS_RecvSMS().GetModelList("Content='" + msgcount + "' and CallerNum='" + mobile + "'order by RecvTime desc");
            if (sms != null && sms.Count > 0)
            {
                var modelSms = sms.First();
                if ((DateTime.Now - modelSms.RecvTime).Minutes > 10)
                {
                    return Error("验证码已失效");
                }
            }
            else
                return Error("请输入正确验证码");
            if (string.IsNullOrEmpty(wld.OpenId))
            {
                var temp = WxHelper.GetUserInfo(wld);
                if (string.IsNullOrWhiteSpace(temp.OpenId))
                {
                    return Redirect("请求", temp.RedirectUrl);
                }
                wld.OpenId = temp.OpenId;
                wld.UserInfo = temp.UserInfo;
            }
            var model = new BLL.T_Sys_Users().GetModel(wld.OpenId);
            if (model != null)
            {

                model.F_Telphone = "";
                new BLL.T_Sys_Users().Update(model);
            }
            else
            {
                return Error("该用户不存在");
            }
            return Success("解除成功", wld.UserInfo);
        }

        /// <summary>
        /// 获取政务网用户
        /// </summary>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult GetZWWUser(string usercode, string signcode)
        {
            if (string.IsNullOrEmpty(usercode) || string.IsNullOrEmpty(signcode))
            {
                return Error("非法访问");
            }
            string zwwcode = Configs.GetValue("ZWWCode");
            string appsigncode = Configs.GetValue("APPSignCode");
            string jssigncode = EncryptHelper.MD5Encrypt(usercode + "|" + EncryptHelper.SHA1Encrypt(appsigncode + "|" + DateTime.Now.ToString("yyyyMMdd")));
            if (signcode != jssigncode)
            {
                return Error("非法访问");
            }

            Model.T_Sys_Users dModel = new Model.T_Sys_Users();
            usercode = zwwcode + usercode;
            var list = new BLL.T_Sys_Users().GetModelList(" F_OpenId='" + usercode + "' ");
            if (list.Count == 0)
            {
                dModel.F_OpenId = usercode;
                dModel.F_Type = (int)EnumUserType.zwwreg; //4;
                dModel.F_CreateTime = DateTime.Now;

                long n = new BLL.T_Sys_Users().Add(dModel);
                return Success("成功", usercode);
            }
            else
            {
                return Success("成功", usercode);
            }
        }

        #region 工单相关
        /// <summary>
        ///  获取工单列表
        /// </summary>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult GetList()
        {
            DataTable dt = new DataTable();
            //string sql = " and F_IsDelete=0 ";
            string sql = " and (F_IsEnabled=0 or F_IsDelete=0) ";

            string stropenid = HttpUtility.UrlDecode(RequestString.GetQueryString("openid"));
            string strstate = HttpUtility.UrlDecode(RequestString.GetQueryString("state"));
            string strname = HttpUtility.UrlDecode(RequestString.GetQueryString("name"));
            string strtel = HttpUtility.UrlDecode(RequestString.GetQueryString("tel"));
            string strkey = HttpUtility.UrlDecode(RequestString.GetQueryString("key"));
            string strstarttime = HttpUtility.UrlDecode(RequestString.GetQueryString("starttime"));
            string strendtime = HttpUtility.UrlDecode(RequestString.GetQueryString("endtime"));
            string strworkid = HttpUtility.UrlDecode(RequestString.GetQueryString("workid"));
            string strusercode = HttpUtility.UrlDecode(RequestString.GetQueryString("usercode"));
            int source = RequestString.GetInt("source", 0);
            int keyid = RequestString.GetInt("keyid", 0);
            int type = RequestString.GetInt("type", 0);
            int bigtype = RequestString.GetInt("bigtype", 0);
            int smalltype = RequestString.GetInt("smalltype", 0);
            int sourcearea = RequestString.GetInt("sourcearea", 0);

            int isdeal = RequestString.GetInt("isdeal", -1);

            string strpageindex = RequestString.GetQueryString("page");
            int pageindex = 1;
            string strpagesize = RequestString.GetQueryString("pagesize");
            int pagesize = 10;

            #region sql 语句相关处理
            //根据openid获取所有工单号
            var wxuser = new BLL.T_Sys_Users().GetModelList(" F_OpenId='" + stropenid.Trim() + "'").FirstOrDefault();
            sql += " and F_WorkOrderId in (select distinct F_WorkOrderId from T_Bus_UserWorkOrder where F_UserId='" + wxuser.F_Id + "') ";

            if (strstate.Trim() != "" && strstate != "undefined")
            {
                sql += " and F_WorkState = '" + strstate.Trim() + "' ";
            }
            if (strworkid.Trim() != "" && strworkid != "undefined")
            {
                sql += " and F_WorkOrderId like '%" + strworkid + "%' ";
            }
            if (strname.Trim() != "" && strname != "undefined")
            {
                sql += " and F_CusName like '%" + strname + "%' ";
            }
            if (strtel.Trim() != "" && strtel != "undefined")
            {
                sql += " and (F_CusPhone like '%" + strtel + "%' or F_ConPhone like '%" + strtel + "%') ";
            }
            if (strkey.Trim() != "" && strkey != "undefined")
            {
                sql += " and (F_ComTitle like '%" + strkey + "%' or F_ComContent like '%" + strkey + "%') ";
            }
            if (strusercode.Trim() != "" && strusercode != "undefined")
            {
                sql += " and F_CreateUser = '" + strusercode + "' ";
            }

            if (source != 0)
            {
                sql += " and F_InfoSource = '" + source + "' ";
            }
            if (keyid != 0)
            {
                sql += " and ','+F_Key+',' like '%," + keyid + ",%' ";
            }
            if (type != 0)
            {
                sql += " and F_InfoType = '" + type + "' ";
            }
            if (bigtype != 0)
            {
                sql += " and F_InfoConBigType = '" + bigtype + "' ";
            }
            if (smalltype != 0)
            {
                sql += " and F_InfoConSmallType = '" + smalltype + "' ";
            }

            if (sourcearea != 0)
            {
                sql += " and F_SourceArea = '" + sourcearea + "' ";
            }
            if (isdeal != -1)
            {
                if (isdeal == 1)
                {
                    sql += " and F_WorkState = '9' ";
                }
                else
                {
                    sql += " and F_WorkState != '9' ";
                }
            }
            if (strstarttime.Trim() != "" && strstarttime != "undefined")
            {
                sql += " and datediff(day,F_CreateTime,'" + strstarttime + "')<=0 ";
            }
            if (strendtime.Trim() != "" && strendtime != "undefined")
            {
                sql += " and datediff(day,F_CreateTime,'" + strendtime + "')>=0   ";
            }
            #endregion

            if (strpageindex.Trim() != "")
            {
                pageindex = Convert.ToInt32(strpageindex);
            }

            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
            string cols = "*,dbo.GetUserName(F_CreateUser) as UserName,dbo.GetDictionaryName(F_InfoType) as TypeName,"
                + "dbo.GetDictionaryName(F_InfoSource) as SourceName,dbo.GetWorkOrderAssign(F_WorkOrderId) as DeptName ";
            int recordCount = 0;
            dt = BLL.PagerBLL.GetListPager(
                "T_Bus_WorkOrder",
                "F_WorkOrderId",
                cols,
                sql,
                "ORDER BY F_CreateTime DESC",
                pagesize,
                pageindex,
                true,
                out recordCount);

            var obj = new
            {
                state = "success",
                message = "成功",
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());

        }

        /// <summary>
        ///  新增工单
        /// </summary>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult AddWorkOrder()
        {
            DataTable dt = new DataTable();
            string cusname = RequestString.GetFormString("cusname");
            string cussex = RequestString.GetFormString("cussex");
            string cusphone = RequestString.GetFormString("cusphone");
            string cusaddress = RequestString.GetFormString("cusaddress");
            string email = RequestString.GetFormString("email");
            string zipcode = RequestString.GetFormString("zipcode");
            string conname = RequestString.GetFormString("conname");
            string conphone = RequestString.GetFormString("conphone");
            string title = RequestString.GetFormString("title");
            string content = RequestString.GetFormString("content");
            string files = RequestString.GetFormString("files");
            int sourcearea = RequestString.GetInt("sourcearea", 0);
            string sourceaddress = RequestString.GetFormString("sourceaddress");
            int isresult = RequestString.GetInt("isresult", 0);
            string result = RequestString.GetFormString("result");
            string keys = RequestString.GetFormString("keys");
            string splituser = RequestString.GetFormString("splituser");
            int type = RequestString.GetInt("type", 0);
            int bigtype = RequestString.GetInt("bigtype", 0);
            int smalltype = RequestString.GetInt("smalltype", 0);
            int isprotect = RequestString.GetInt("isprotect", 0);
            int level = RequestString.GetInt("level", 0);
            int soursetype = 5;
            string stropenid = HttpUtility.UrlDecode(RequestString.GetFormString("openid"));
            var wxuser = new BLL.T_Sys_Users().GetModelList(" F_OpenId='" + stropenid.Trim() + "'").FirstOrDefault();
            if (wxuser.F_Type == (int)EnumUserType.zwwreg) { soursetype = 6; }
            //if (wxuser.F_Type == 4) { soursetype = 6; }

            workorder.WorkOrderController wo = new workorder.WorkOrderController();
            string workorderid = wo.AddWorkOrderBySource(soursetype, cusname, cussex, cusphone, cusaddress, email, zipcode, conname, conphone, title,
                content, sourcearea, sourceaddress, keys, splituser, type, bigtype, smalltype, isprotect, level, files);
            if (string.IsNullOrEmpty(workorderid))
            {
                return Error("新增失败");
            }
            else
            {
                Model.T_Bus_UserWorkOrder tbu = new Model.T_Bus_UserWorkOrder();
                tbu.F_UserId = wxuser.F_Id;
                tbu.F_WorkOrderId = workorderid;
                new BLL.T_Bus_UserWorkOrder().Add(tbu);

                return Success("新增成功", workorderid);
            }
        }

        /// <summary>
        ///  获取工单详情
        /// </summary>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult GetWorkOrder()
        {
            string strworkorderid = HttpUtility.UrlDecode(RequestString.GetQueryString("workorderid"));
            string stropenid = HttpUtility.UrlDecode(RequestString.GetQueryString("openid"));
            var wxuser = new BLL.T_Sys_Users().GetModelList(" F_OpenId='" + stropenid.Trim() + "'").FirstOrDefault();

            var tbu = new BLL.T_Bus_UserWorkOrder().GetList(" F_UserId='" + wxuser.F_Id + "' and F_WorkOrderId='" + strworkorderid + "' ").Tables[0];

            if (tbu.Rows.Count > 0)
            {
                string sql = "select *,dbo.GetUserName(F_CreateUser) as UserName,dbo.GetDictionaryName(F_InfoType) as TypeName1,"
                    //+ "dbo.GetDictionaryName(F_InfoConBigType) as TypeName2,dbo.GetDictionaryName(F_InfoConSmallType) as TypeName3,"
                    + "dbo.GetWorkOrderAssign(F_WorkOrderId) as DeptName,dbo.GetDictionaryName(F_InfoSource) as SourceName ,"
                    + "dbo.GetAreaName(F_SourceArea) as AreaName "
                    + " from T_Bus_WorkOrder where F_WorkOrderId ='" + strworkorderid + "' and (F_IsEnabled=0 or F_IsDelete=0)";
                var dt = DbHelperSQL.Query(sql).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    var configly = new BLL.T_Sys_SystemConfig().GetModelList(" F_ParamCode='PlayLeaveVoice' ").FirstOrDefault();
                    var config = new BLL.T_Sys_SystemConfig().GetModelList(" F_ParamCode='PlayPath' ").FirstOrDefault();
                    var configfj = new BLL.T_Sys_SystemConfig().GetModelList(" F_ParamCode='FileUrlPath' ").FirstOrDefault();

                    dt.Columns.Add("FilePath", typeof(string));
                    if (dt.Rows[0]["F_LeaveRecordId"] != null)
                    {
                        dt.Rows[0]["FilePath"] = GetLeavePath(dt.Rows[0]["F_LeaveRecordId"].ToString(), config.F_ParamValue);
                    }
                    else if (dt.Rows[0]["F_CallRecordId"] != null)
                    {
                        dt.Rows[0]["FilePath"] = GetCallPath(dt.Rows[0]["F_CallRecordId"].ToString(), config.F_ParamValue);
                    }
                    if (configfj != null)
                    {
                        dt = BindFileData(dt, configfj.F_ParamValue);
                    }

                    string gcsql = "select *,dbo.GetUserName(F_CreateUser) as UserName "
                                        + "from T_Bus_Operation where F_IsDelete=0 and F_WorkOrderId ='" + strworkorderid + "'";
                    var gcdt = DbHelperSQL.Query(gcsql).Tables[0];
                    gcdt.Columns.Add("File", typeof(object));
                    gcdt.Columns.Add("FilePath", typeof(string));
                    if (configfj != null || configly != null || config != null)
                    {
                        foreach (DataRow bldr in gcdt.Rows)
                        {
                            if (bldr["F_File"] != null && bldr["F_File"].ToString() != "" && configfj != null)
                            {
                                bldr["File"] = GetFileData(bldr["F_File"].ToString(), configfj.F_ParamValue);
                            }

                            if (bldr["F_LeaveRecordId"] != null && configly != null)
                            {
                                bldr["FilePath"] = GetLeavePath(bldr["F_LeaveRecordId"].ToString(), configly.F_ParamValue);
                            }
                            else if (bldr["F_CallRecordId"] != null && config != null)
                            {
                                bldr["FilePath"] = GetCallPath(bldr["F_CallRecordId"].ToString(), config.F_ParamValue);
                            }
                        }
                    }
                    var obj = new
                    {
                        data = dt,
                        item = gcdt
                    };

                    return Success("查询成功", obj);
                }
                else
                {
                    return Error("查询失败");
                }
            }
            else
            {
                return Error("查询失败");
            }

        }

        /// <summary>
        ///  获取工单数量
        /// </summary>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult GetWorkOrderCount()
        {
            string stropenid = HttpUtility.UrlDecode(RequestString.GetQueryString("openid"));
            int type = RequestString.GetInt("type", 0);

            var wxuser = new BLL.T_Sys_Users().GetModelList(" F_OpenId='" + stropenid.Trim() + "'").FirstOrDefault();

            string where= " F_WorkOrderId in (select F_WorkOrderId from T_Bus_UserWorkOrder where F_UserId='" + wxuser.F_Id + "') and (F_IsEnabled=0 or F_IsDelete=0) ";
            

            string sql = " select count(1) from T_Bus_WorkOrder where " + where ;
            string sqlblz = " select count(1) from T_Bus_WorkOrder where " + where + " and F_WorkState!=9 ";
            string sqlybl = " select count(1) from T_Bus_WorkOrder where " + where + " and F_WorkState =9 ";

            string sqltype = " select F_ValueId,F_Value,(select COUNT(1) from T_Bus_WorkOrder where (F_IsEnabled=0 or F_IsDelete=0) ";
            if (type == 1)
            {
                sqltype += " and F_WorkState!=9";
            }
            else if (type == 2)
            {
                sqltype += " and F_WorkState=9";
            }
            sqltype += " and  F_InfoType=F_ValueId and " + where + ") Count from  dbo.T_Sys_DictionaryValue where (F_PrentId=37 or F_ItemId=2) and F_State=0 ";

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

            var obj = new
            {
                total = DbHelperSQL.GetSingle(sql).ToString(),
                blzcount = DbHelperSQL.GetSingle(sqlblz).ToString(),
                yblcount = DbHelperSQL.GetSingle(sqlybl).ToString(),
                typedata = dt
            };

            return Success("成功", obj);
        }
        #endregion

        #region 获取下拉框
        /// <summary>
        /// 获取工单类型列表
        /// </summary>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult GetTypeList()
        {
            string sql = "select * from T_Sys_DictionaryValue where (F_PrentId=37 or F_ItemId=2) and F_State=0 ";
            var dt = DbHelperSQL.Query(sql).Tables[0];
            return Success("列表加载成功", dt);
        }

        /// <summary>
        /// 获取事发地域列表
        /// </summary>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult GetAreaList()
        {
            string sql = "select * from T_Sys_Area where isnull(F_PrentId,0)=0 and F_State=0 ";
            var dt = DbHelperSQL.Query(sql).Tables[0];
            return Success("列表加载成功", dt);
        }

        /// <summary>
        /// 获取工单类型列表
        /// </summary>
        /// <returns></returns>
        [WechatActionFilter]
        public ActionResult GetKeyList()
        {
            string sql = "select * from T_Sys_DictionaryValue where (F_PrentId=38 or F_ItemId=3) and F_State=0 ";
            var dt = DbHelperSQL.Query(sql).Tables[0];
            return Success("列表加载成功", dt);
        } 
        #endregion

        #region 附件和语音文件
        /// <summary>
        /// 获取留言路径
        /// </summary>
        /// <param name="lid">留言id</param>
        /// <param name="prefix">前缀</param>
        /// <returns></returns>
        public string GetLeavePath(string lid, string prefix)
        {
            string path = string.Empty;
            var liuyan = new BLL.T_Call_LeaveRecord().GetModelList(" F_Id='" + lid + "' ").FirstOrDefault();
            if (liuyan != null)
            {
                if (!string.IsNullOrEmpty(liuyan.F_RecFileUrl))
                {
                    path = prefix + liuyan.F_RecFileUrl;
                }
            }
            return path;
        }

        /// <summary>
        /// 获取通话录音路径
        /// </summary>
        /// <param name="cid">通话id</param>
        /// <param name="prefix">前缀</param>
        /// <returns></returns>
        public string GetCallPath(string cid, string prefix)
        {
            string path = string.Empty;
            var luyin = new BLL.T_Call_CallRecords().GetModelList(" CallRecordsId='" + cid + "' ").FirstOrDefault();
            if (luyin != null)
            {
                if (!string.IsNullOrEmpty(luyin.FilePath))
                {
                    var ym = prefix;
                    ym = ym.Substring(0, ym.Length - 1);
                    string lujing = luyin.FilePath.Substring(luyin.FilePath.IndexOf(':') + 1).Replace('\\', '/');
                    path = ym + lujing;

                    //if (!CommonHelper.FileIsExist(path))
                    //{
                    //    path = lujing;
                    //}
                }
            }
            return path;
        }

        /// <summary>
        /// 获取附件数据
        /// </summary>
        /// <param name="ids">附件id，多个用英文逗号,隔开</param>
        /// <param name="prefix">前缀</param>
        /// <returns></returns>
        public DataTable GetFileData(string ids, string prefix)
        {
            DataTable dt = DbHelperSQL.Query("select * from T_Sys_Accessories where F_FileId in (" + ids + ")").Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                dr["F_FileUrl"] = prefix + dr["F_FileUrl"].ToString();
            }

            return dt;
        }

        /// <summary>
        /// 绑定附件信息
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="prefix"></param>
        /// <returns></returns>
        public DataTable BindFileData(DataTable dt, string prefix)
        {
            dt.Columns.Add("File", typeof(object));
            foreach (DataRow dr in dt.Rows)
            {
                if (dr["F_File"] != null && dr["F_File"].ToString() != "")
                {
                    dr["File"] = GetFileData(dr["F_File"].ToString(), prefix);
                }
            }
            return dt;
        } 
        #endregion
    }
}