Anyang_api/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/tel/CallrecordsC...

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

namespace CallCenterApi.Interface.Controllers.tel
{
    [Authority]
    public class CallrecordsController : BaseController
    {
        //获取通话记录列表
        public ActionResult GetList(string phone, string usercode,int isdc=0)
        {
            string sql = "";
            DataTable dt = new DataTable();

          //  int roleid = User.F_RoleId;
            string callstate = HttpUtility.UrlDecode(RequestString.GetQueryString("callstate"));
            string calltype = HttpUtility.UrlDecode(RequestString.GetQueryString("calltype"));
            string actiontype = HttpUtility.UrlDecode(RequestString.GetQueryString("actiontype"));
            string starttime = HttpUtility.UrlDecode(RequestString.GetQueryString("starttime"));
            string endtime = HttpUtility.UrlDecode(RequestString.GetQueryString("endtime"));
            string tasktype = HttpUtility.UrlDecode(RequestString.GetQueryString("tasktype"));
            string extnumber = HttpUtility.UrlDecode(RequestString.GetQueryString("extnumber"));
            string PhoneType = HttpUtility.UrlDecode(RequestString.GetQueryString("phoneType"));
            string group = HttpUtility.UrlDecode(RequestString.GetQueryString("group"));
            int callback = RequestString.GetInt("callback", 0);
            int waitTime = RequestString.GetInt("waitTime", -1);
            
            int type = RequestString.GetInt("type", 0);
            int islike = RequestString.GetInt("islike", 1);
            int notconnected = RequestString.GetInt("notconnected", 0);
            string CallbackCallid = HttpUtility.UrlDecode(RequestString.GetQueryString("callbackCallid"));
            string strpageindex = RequestString.GetQueryString("page");
            int pageindex = 1;
            string strpagesize = RequestString.GetQueryString("pagesize");
            int pagesize = 10;

            //if (roleid != 0)
            //{
            //    if (roleid == 1)
            //    {
            //        sql += " and UserCode='" + User.F_WorkNumber + "' ";
            //    }
            //}
            if (type != 0)
            {
                sql += " and F_CallInType = '" + type + "' ";
            }
            if (usercode != null && usercode.Trim() != "")
            {
                sql += " and UserCode in(" + usercode + ")";
            }
            if (PhoneType != null && PhoneType.Trim() != "")
            {
                if (PhoneType =="12366")
                {
                    sql += " and PhoneType in('12366','2214605')";
                }
                else 
                sql += " and PhoneType='"+ PhoneType + "'";
            }
            
            if (islike > 0)
            {
                if (phone != null && phone.Trim() != "")
                {
                    sql += " and CallNumber like '%" + phone + "%'";
                }
            }
            else
            {
                if (phone != null && phone.Trim() != "")
                {
                    sql += " and CallNumber = '" + phone + "' ";
                }
            }
            if (callback>0)
            {
                if (callback==1)
                {
                    sql += " and Callback is not null ";
                }
               else
                {
                    sql += " and Callback is null and  CallType=0 and CallState=0";
                }
            }
            if (!string .IsNullOrEmpty (CallbackCallid))
            {
                sql += "and CallbackCallid='" + CallbackCallid + "'";
            }
            if (!string .IsNullOrEmpty (group))
            {
                sql += " and UserCode in(select F_UserCode from T_Sys_UserAccount where F_UserName like '%"+ group + "%' )";
            }
            if (waitTime>-1)
            {
                sql += "and  (DATEDIFF (SECOND ,TalkStartTime,BeginTime)='"+ waitTime + "'  or ((DATEDIFF (SECOND ,TalkStartTime,EndTime)='"+ waitTime + "' and TalkStartTime is null )    ))";
            }
            if (callstate.Trim() != "")
            {
                if (callstate.Trim() == "0")
                {
                    sql += " and CallState=0 and isnull(UserCode,'')!='' ";
                }
                else if (callstate.Trim() == "3")
                {
                    sql += " and CallState=0 and CallType=0 and isnull(UserCode,'')='' ";
                }
                else
                {
                    sql += " and CallState='" + callstate + "'";
                }
            }

            if (notconnected>0)
            {
               if (notconnected!=2)
                {
                    string where = "";
                    if (starttime.Trim() != "")
                    {
                        where += " and BeginTime>='" + starttime + "' ";
                    }
                    if (endtime.Trim() != "")
                    {
                        where += " and BeginTime<='" + endtime + "' ";
                    }
                    sql += "and CallNumber in (select CallNumber from T_Call_CallRecords where CallType = 0" + where + "  group by CallNumber having SUM( case when CallState = 1 and DealType = 6 then 1 else 0 end )= 0)";
                }
                
            }
            if (starttime.Trim() != "")
            {
                sql += " and BeginTime>='" + starttime + "' ";
            }
            if (endtime.Trim() != "")
            {
                sql += " and BeginTime<='" + endtime + "' ";
            }

            //else
            //    sql += " and isnull(UserCode,'')<>'' ";
            if (calltype.Trim() != "")
            {
                if (calltype=="3")
                {
                    sql += " and CallType='1'";
                    sql += " and CallRecordsId in(select F_CallRecordId from T_Bus_VisitResult WITH(NOLOCK) where F_CallRecordId !='' and F_CallRecordId is not null )";
                }
                else 
                sql += " and CallType='" + calltype + "'";
            }
            if (tasktype.Trim() != "")
            {
                if (tasktype.Trim() == "2")
                {
                    sql += " and TaskType='" + tasktype + "'";
                }
                else if (tasktype.Trim() == "0")
                {
                    sql += " and (TaskType is null or TaskType!='2')";
                }
            }
            if (actiontype.Trim() != "")
            {
                sql += " and ActionType=" + actiontype.Trim();
            }
           
            if(extnumber.Trim()!="")
            {
                sql += " and isnull(ExtNumber,'')='" + extnumber.Trim() + "'";
            }

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

            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
            if (isdc > 0)
            {
                if (notconnected>0)
                {
                    var dtdc = DbHelperSQL.Query(" select   ROW_NUMBER()  OVER(ORDER BY CallRecordsId desc) 编号, CallNumber 电话号码,(case CallType when 0 then '呼入' else '呼出' end ) 呼叫方向,(case CallState when 0 then ( case  when CallType=0 and isnull(UserCode,'')='' then  '主动放弃' else '未接通'   end    ) else '已接通' end ) 呼叫状态,UserCode 坐席工号, "
                   + "BeginTime 开始时间,TalkStartTime 通话开始时间 ,TalkEndTime " +
                   " 通话结束时间 ,EndTime 结束时间 ,LongTime 通话时长 ,PhoneType " +
                   "电话类别"
                  + " from T_Call_CallRecords  a WITH(NOLOCK)  where 1=1 " + sql + "ORDER BY CallRecordsId desc").Tables[0];
                    var msg = new NPOIHelper().ExportToExcel("通话记录", dtdc);
                    if (msg == "")
                    {
                        return Success("导出成功");
                    }
                    else
                    {
                        return Error("导出失败");
                    }
                }
                else
                {
                    var dtdc = DbHelperSQL.Query(" select   ROW_NUMBER()  OVER(ORDER BY CallRecordsId desc) 编号, CallNumber 电话号码,(case CallType when 0 then '呼入' else '呼出' end ) 呼叫方向,(case CallState when 0 then ( case  when CallType=0 and isnull(UserCode,'')='' then  '主动放弃' else '未接通'   end    ) else '已接通' end ) 呼叫状态,UserCode 坐席工号,dbo.GetUserName(UserCode) as 坐席姓名, "
                   + "BeginTime 开始时间,TalkStartTime 通话开始时间 ,TalkEndTime " +
                   " 通话结束时间 ,EndTime 结束时间 ,LongTime 通话时长 ,PhoneType " +
                   "电话类别"
                  + " from T_Call_CallRecords  a WITH(NOLOCK)  where 1=1 " + sql + "ORDER BY CallRecordsId desc").Tables[0];
                    var msg = new NPOIHelper().ExportToExcel("通话记录", dtdc);
                    if (msg == "")
                    {
                        return Success("导出成功");
                    }
                    else
                    {
                        return Error("导出失败");
                    }
                }
               
            }
            else
            {
                int recordCount = 0;
                if (notconnected==2)
                {

                    dt = BLL.PagerBLL.GetListPager(
                   "(select COUNT(1) count, CallNumber from  T_Call_CallRecords where CallType = 0 "+sql +" group by CallNumber having SUM( case when CallState = 1 and DealType = 6 then 1 else 0 end) = 0) a",
                   "CallNumber",
                   "*",
                   "",
                   "order by a.count",
                   pagesize,
                   pageindex,
                   true,
                   out recordCount);

                }
                else
                {
                    
                    dt = BLL.PagerBLL.GetListPager(
                        "T_Call_CallRecords WITH(NOLOCK)",
                        "CallRecordsId",
                        "*,dbo.GetUserName(UserCode) as UserName, WorkOrderId,dbo.GetDictionaryName(F_CallInType) as TypeName",
                        sql,
                        "ORDER BY CallRecordsId desc ",
                        pagesize,
                        pageindex,
                        true,
                        out recordCount);

                    var config = new BLL.T_Sys_SystemConfig().GetModelList(" F_ParamCode='PlayPath' ").FirstOrDefault();
                    dt.Columns.Add("WaitTime", typeof(string));
                    foreach (DataRow dr in dt.Rows)
                    {
                        string path = dr["FilePath"] != null ? dr["FilePath"].ToString() : "";
                        if (path != "" && config != null && !string.IsNullOrEmpty(config.F_ParamValue))
                        {
                            var ym = config.F_ParamValue;
                            if (ym.Substring(ym.Length - 1) == "/")
                            {
                                ym = ym.Substring(0, ym.Length - 1);
                            }

                            string lujing = path.Substring(path.IndexOf(':') + 1).Replace('\\', '/');
                            string wlpath = ym + lujing;

                            dr["FilePath"] = wlpath;
                        }
                        if (dr["TalkStartTime"]!=null && dr["TalkStartTime"].ToString() != "" && dr["BeginTime"] != null
                            && dr["BeginTime"].ToString() != "")
                        {
                            var ltime = DateTime.Parse(dr["TalkStartTime"].ToString ());
                            TimeSpan ts = ltime - DateTime.Parse(dr["BeginTime"].ToString());
                            double tss = double.Parse(Math.Round(ts.TotalSeconds, 0).ToString());
                            dr["WaitTime"] = DateTimeConvert.parseTimeSeconds(tss, 1);
                        }
                        else if (dr["EndTime"] != null && dr["EndTime"].ToString() != "" && dr["BeginTime"] != null && dr["BeginTime"].ToString() != "")
                        {
                            var ltime = DateTime.Parse(dr["EndTime"].ToString());
                            TimeSpan ts = ltime - DateTime.Parse(dr["BeginTime"].ToString());
                            double tss = double.Parse(Math.Round(ts.TotalSeconds, 0).ToString());
                            dr["WaitTime"] = DateTimeConvert.parseTimeSeconds(tss, 1);
                        }
                        if (dr["PhoneType"] != null && dr["PhoneType"].ToString() != "")
                        {
                            if (dr["PhoneType"].ToString() == "2214605")
                            {
                                dr["PhoneType"] = "12366";
                            }
                        }

                    }
                }
             

                var obj = new
                {
                    state = "success",
                    message = "成功",
                    rows = dt,
                    total = recordCount
                };

                return Content(obj.ToJson());
            }
           


        }


        //获取通话记录列表
        public ActionResult GetNotconnectedList(string phone="")
        {
            string sql = "";
            DataTable dt = new DataTable();


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


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

            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
            if (phone != null && phone.Trim() != "")
            {
                sql += " and CallNumber = '" + phone + "'";
            }
            //  sql += "and CallType=0";
            int recordCount = 0;
                dt = BLL.PagerBLL.GetListPager(
                    "(select COUNT(1) count, CallNumber from  T_Call_CallRecords where CallType = 0  group by CallNumber having SUM( case when CallState = 1 and DealType = 6 then 1 else 0 end) = 0) a",
                    "CallNumber",
                    "*",
                    sql,
                    "order by a.count",
                    pagesize,
                    pageindex,
                    true,
                    out recordCount);



                var obj = new
                {
                    state = "success",
                    message = "成功",
                    rows = dt,
                    total = recordCount
                };

                return Content(obj.ToJson());
            



        }
        //获取未接来电
        public ActionResult GetListWJ(string phone)
        {

            string sql = "";
            DataTable dt = new DataTable();

            string starttime = HttpUtility.UrlDecode(RequestString.GetQueryString("starttime"));
            string endtime = HttpUtility.UrlDecode(RequestString.GetQueryString("endtime"));
            string ishf = HttpUtility.UrlDecode(RequestString.GetQueryString("ishf"));//是否回复 0否1是
            string ishfSuccess = HttpUtility.UrlDecode(RequestString.GetQueryString("ishfsuc"));//是否回复成功 0否1是

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


            if (phone != null && phone.Trim() != "")
            {
                sql += " and CallNumber like '%" + phone + "%'";
            }

            if (starttime.Trim() != "")
            {
                sql += " and datediff(day,BeginTime,'" + starttime.Trim() + "')<=0 ";
            }
            if (endtime.Trim() != "")
            {
                sql += " and datediff(day,BeginTime,'" + endtime.Trim() + "')>=0 ";
            }
            if (ishf.Trim() != "")
            {
                sql += " and F_wjishf=" + ishf.Trim() + " ";
            }
            if (ishfSuccess.Trim() != "")
            {
                sql += " and F_wjishfSuccess=" + ishfSuccess.Trim() + " ";
            }
            sql += " and CallState='0'";
            sql += " and CallType='0'";
            if (strpageindex.Trim() != "")
            {
                pageindex = Convert.ToInt32(strpageindex);
            }

            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
            int recordCount = 0;
            dt = BLL.PagerBLL.GetListPager(
                "T_Call_CallRecords WITH(NOLOCK)",
                "CallRecordsId",
                "*",
                sql,
                "ORDER BY CallRecordsId desc",
                pagesize,
                pageindex,
                true,
                out recordCount);

            var obj = new
            {
                state = "success",
                message = "成功",
                rows = dt,
                total = recordCount
            };

            return Content(obj.ToJson());

        }
      
        //获取转移或三方通话，均为手机号
        //rep_transfer只有转移的数据
        public ActionResult GetListZY(string phone, string usercode)
        {
            string sql = "";
            DataTable dt = new DataTable();

            string callstate = HttpUtility.UrlDecode(RequestString.GetQueryString("callstate"));
            string starttime = HttpUtility.UrlDecode(RequestString.GetQueryString("starttime"));
            string endtime = HttpUtility.UrlDecode(RequestString.GetQueryString("endtime"));

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

            sql += " and ActionType>1";//1正常呼入呼出、2转移、3三方通话

            if (usercode != null && usercode.Trim() != "")
            {
                sql += " and UserCode='" + usercode + "'";
            }

            if (phone != null && phone.Trim() != "")
            {
                sql += " and CallNumber like '%" + phone + "%'";
            }
            if (callstate.Trim() != "")
            {
                sql += " and CallState='" + callstate + "'";
            }

            if (starttime.Trim() != "")
            {
                sql += " and datediff(day,BeginTime,'" + starttime + "')<=0 ";
            }
            if (endtime.Trim() != "")
            {
                sql += " and datediff(day,BeginTime,'" + endtime + "')>=0 ";
            }

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

            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
            int recordCount = 0;
         
           
                dt = BLL.PagerBLL.GetListPager(
               "T_Call_CallRecords WITH(NOLOCK)",
               "CallRecordsId",
               "*",
               sql,
               "ORDER BY CallRecordsId desc",
               pagesize,
               pageindex,
               true,
               out recordCount);

                List<Model.T_Call_CallRecords> callList = new BLL.T_Call_CallRecords().DataTableToList(dt);

                var obj = new
                {
                    state = "success",
                    message = "成功",
                    rows = callList,
                    total = recordCount
                };

                return Content(obj.ToJson());
           
           

        }
        /// <summary>
        /// 更新回拨
        /// </summary>
        /// <returns></returns>
        [Authority]
        public ActionResult UpdateCallback(string callid)
        {

           var b = new BLL.T_Call_CallRecords().UpdateCallback(callid);
            if (b )
            {
                return Success("更新成功");
            }
            else
            {
                return Success("更新失败");
            }
        }
        /// <summary>
        /// 更新回拨
        /// </summary>
        /// <returns></returns>
        [Authority]
        public ActionResult UpdateCallbackid(string callid,string p_callid)
        {

            var b = new BLL.T_Call_CallRecords().UpdateCallbackid(callid, p_callid);
            if (b)
            {
                return Success("更新成功");
            }
            else
            {
                return Success("更新失败");
            }
        }
        //删除通话记录
        public ActionResult DelCallRecord(string[] ids)
        {
            if (ids != null && ids.Length > 0)
            {
                string idd = " ";
                foreach (string str in ids)
                {
                    idd += str + ",";
                }
                if (new BLL.T_Call_CallRecords().DeleteList(idd.TrimEnd(',')))
                {
                    return Success("删除成功");
                }
                else
                    return Error("删除失败");
            }
            else
            {
                return Error("请选择要删除的记录");
            }

        }
    }
}