YIHE_API/MadRunFabric.../CallCenterAp.../QC_CheckList...

using MadRunFabric.Common;
using MadRunFabric.Model;
using CallCenterApi.IRepositories;
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Extensions.Logging;
using MadRunFabric.Common.Options;
using Microsoft.Extensions.Options;
using System.Linq;
using MongoDB.Driver;
using MadRunFabric.Model.CallCenterApi;

namespace CallCenterApi.Repositories
{
    public class QC_CheckListRepository : BaseRepository<QC_CheckList, string>, IQC_CheckListRepository
    {
        protected readonly ILogger<BaseRepository<QC_CheckList, string>> _logger;
        protected readonly IMongoCollection<mw_call_records> _collection_mw_call_records;
        protected readonly IMongoCollection<QC_CheckType> _collection_qc_checktype;
        protected readonly IMongoCollection<QC_CheckScore> _collection_qc_checkscore;
        protected readonly IMongoCollection<Sys_SystemConfig> _collection_sys_systemconfig;
        protected readonly IMongoCollection<Sys_User_Account> _collection_sys_user_account;

        public QC_CheckListRepository(IOptions<MongodbOptions> settings, ILogger<BaseRepository<QC_CheckList, string>> logger) : base(settings, logger)
        {
            _logger = logger;
            _collection_mw_call_records = _context.GetCollection<mw_call_records>();
            _collection_qc_checktype = _context.GetCollection<QC_CheckType>();
            _collection_qc_checkscore = _context.GetCollection<QC_CheckScore>();
            _collection_sys_systemconfig = _context.GetCollection<Sys_SystemConfig>();
            _collection_sys_user_account = _context.GetCollection<Sys_User_Account>();
        }

        /// <summary>
        /// 分页列表 - 根据分页查询数据列表 包含查询条件，显示字段，排序 
        /// </summary>
        /// <param name="usercode"></param>
        /// <param name="calltype"></param>
        /// <param name="telephone"></param>
        /// <param name="checktype"></param>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="recordcount"></param>
        /// <returns></returns>
        public IEnumerable<object> GetListsByPage(string usercode, int calltype, string telephone, int checktype,
            string stime, string etime, int pageindex, int pagesize, out int recordcount)
        {
            var query = from p in _collection_mw_call_records.AsQueryable()
                        join c in _collection.AsQueryable() on p.id equals c.recordid into checkinfo
                        join u in _collection_sys_user_account.AsQueryable() on p.agent_code equals u.usercode into userinfo
                        select new
                        {
                            p.id,
                            p.unique_id,
                            p.callnumber,
                            p.calltype,
                            p.callstate,
                            p.agent_code,
                            username = userinfo.Count() > 0 ? userinfo.First().username : "",
                            p.FilePath,
                            p.begintime,
                            p.begintime_ivr,
                            p.endtime_ivr,
                            p.longs_ivr,
                            p.begintime_ring,
                            p.endtime_ring,
                            p.longs_ring,
                            p.begintime_talk,
                            p.endtime_talk,
                            p.longs_talk,
                            p.endtime,
                            p.longs,
                            checktype = checkinfo.Count() > 0 ? 1 : 0,
                            checkdate = checkinfo.Count() > 0 ? checkinfo.First().createtime : DateTime.MinValue,
                            checkscore = checkinfo.Count() > 0 ? checkinfo.First().qctotalscore : 0
                        };

            #region 查询条件
            if (!string.IsNullOrEmpty(telephone))
                query = query.Where(it => it.callnumber.Contains(telephone));
            if (!string.IsNullOrEmpty(usercode))
                query = query.Where(it => it.agent_code .Contains ( usercode));
            if (calltype != -1)
                query = query.Where(it => it.calltype == calltype);
            if (checktype != -1)
                query = query.Where(it => it.checktype == checktype);

            if (!string.IsNullOrEmpty(stime))
                query = query.Where(it => it.begintime >= Convert.ToDateTime(stime + " 00:00:00"));
            if (!string.IsNullOrEmpty(etime))
                query = query.Where(it => it.begintime <= Convert.ToDateTime(etime + " 23:59:59"));

            #endregion

            recordcount = query.Count();
            var list = query.OrderByDescending(p => p.id).Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();

            return list;
        }

        /// <summary>
        /// 详情 - 获取详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object GetDetails(string id)
        {
            //var recordmodel = _collection_mw_call_records.Find(p => p.id == id).FirstOrDefault();
            var query = from p in _collection_mw_call_records.AsQueryable()
                        join u in _collection_sys_user_account.AsQueryable() on p.agent_code equals u.usercode into userinfo
                        where p.id == id
                        select new
                        {
                            p.id,
                            p.unique_id,
                            p.callnumber,
                            p.calltype,
                            p.callstate,
                            p.agent_code,
                            username = userinfo.Count() > 0 ? userinfo.First().username : "",
                            p.FilePath,
                            p.begintime,
                            p.begintime_ivr,
                            p.endtime_ivr,
                            p.longs_ivr,
                            p.begintime_ring,
                            p.endtime_ring,
                            p.longs_ring,
                            p.begintime_talk,
                            p.endtime_talk,
                            p.longs_talk,
                            p.endtime,
                            p.longs
                        };
            var recordmodel = query.FirstOrDefault();
            if (recordmodel != null)
            {
                var pathmodel = _collection_sys_systemconfig.Find(p => p.paramcode == "PlayPath" && p.isdelete == false).FirstOrDefault();
                var recordpath = pathmodel?.paramvalue ?? "";

                var scoremodel = _collection.Find(p => p.recordid == recordmodel.id).FirstOrDefault();
                if (scoremodel != null)
                {
                    var obj = new
                    {
                        recordmodel,
                        recordpath,
                        scoremodel,
                        checktype = 1
                    };
                    return obj;
                }
                else
                {
                    var typelist = _collection_qc_checktype.Find(p => p.isdelete == 0).ToList();
                    var scorelist = _collection_qc_checkscore.Find(p => p.isdelete == 0).ToList();
                    var checkmodel = typelist.Where(p => p.parentid == null).Select(p =>
                    {
                        var tlist = typelist.Where(s => s.parentid == p.id).Select(s =>
                        {
                            var slist = scorelist.Where(o => o.typeid == s.id);
                            return new
                            {
                                s.name,
                                s.sort,
                                score = slist.Sum(o => o.score),
                                qcscore = 0,
                                item = slist.Select(o => new
                                {
                                    name = o.title,
                                    o.sort,
                                    o.score,
                                    qcscore = 0
                                }).OrderBy(o => o.sort)
                            };
                        }).OrderBy(s => s.sort);

                        return new
                        {
                            p.name,
                            p.sort,
                            score = tlist.Sum(s => s.score),
                            qcscore = 0,
                            item = tlist
                        };
                    }).OrderBy(p => p.sort);

                    var obj = new
                    {
                        recordmodel,
                        recordpath,
                        checkmodel,
                        checktype = 0
                    };
                    return obj;
                }
            }

            return null;
        }

        /// <summary>
        /// 获取坐席统计数据
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <returns></returns>
        public object GetSeatTotal(string stime, string etime, string usercode)
        {
            var query = from p in _collection_mw_call_records.AsQueryable()
                        join c in _collection.AsQueryable() on p.id equals c.recordid into checkinfo
                        join u in _collection_sys_user_account.AsQueryable() on p.agent_code equals u.usercode into userinfo
                        select new
                        {
                            p.id,
                            p.agent_code,
                            username = userinfo.Count() > 0 ? userinfo.First().username : "",
                            p.begintime,
                            checktype = checkinfo.Count() > 0 ? 1 : 0,
                            checkscore = checkinfo.Count() > 0 ? checkinfo.First().qctotalscore : 0
                        };

            #region 查询条件
            if (!string.IsNullOrEmpty(stime))
                query = query.Where(it => it.begintime >= Convert.ToDateTime(stime + " 00:00:00"));
            if (!string.IsNullOrEmpty(etime))
                query = query.Where(it => it.begintime <= Convert.ToDateTime(etime + " 23:59:59"));
            if (!string.IsNullOrEmpty(usercode))
                query = query.Where(it => it.agent_code == usercode);
            #endregion

            var checklist = query.Where(p => p.checktype == 1);
            if (checklist.Count() > 0)
            {
                var obj = new
                {
                    callcount = query.Count(),
                    checkcount = checklist.Count(),
                    avgscore = Math.Round(checklist.Average(p => p.checkscore), 2),
                    maxscore = checklist.Max(p => p.checkscore),
                    minscore = checklist.Min(p => p.checkscore)
                };

                return obj;
            }
            else
            {
                var obj = new
                {
                    callcount = query.Count(),
                    checkcount = checklist.Count(),
                    avgscore = 0,
                    maxscore = 0,
                    minscore = 0
                };

                return obj;
            }
        }

        /// <summary>
        /// 获取坐席月度排行
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <param name="usercode"></param>
        /// <returns></returns>
        public object GetSeatMonthRank(string stime, string etime, string usercode)
        {
            var query = from p in _collection_mw_call_records.AsQueryable()
                        join c in _collection.AsQueryable() on p.id equals c.recordid into checkinfo
                        join u in _collection_sys_user_account.AsQueryable() on p.agent_code equals u.usercode into userinfo
                        select new
                        {
                            p.id,
                            p.agent_code,
                            username = userinfo.Count() > 0 ? userinfo.First().username : "",
                            p.begintime,
                            checktype = checkinfo.Count() > 0 ? 1 : 0,
                            checkscore = checkinfo.Count() > 0 ? checkinfo.First().qctotalscore : 0
                        };

            #region 查询条件
            if (!string.IsNullOrEmpty(stime))
                query = query.Where(it => it.begintime >= Convert.ToDateTime(stime + " 00:00:00"));
            if (!string.IsNullOrEmpty(etime))
                query = query.Where(it => it.begintime <= Convert.ToDateTime(etime + " 23:59:59"));
            #endregion

            var checklist = query.Where(p => p.checktype == 1);
            if (checklist.Count() > 0)
            {
                var ranklist = checklist.GroupBy(p => new { p.agent_code, p.username }).Select(p => new
                {
                    usercode = p.Key.agent_code,
                    p.Key.username,
                    avgscore = p.Average(q => q.checkscore),
                    maxscore = p.Max(q => q.checkscore),
                    minscore = p.Min(q => q.checkscore)
                }).OrderByDescending(p => p.avgscore).ToList().Select((p, i) => new
                {
                    p.usercode,
                    p.username,
                    avgscore = Math.Round(p.avgscore, 2),
                    p.maxscore,
                    p.minscore,
                    rank = i + 1
                });
                int rank = 0;
                if (!string.IsNullOrEmpty(usercode))
                {
                    var rankmodel = ranklist.Where(p => p.usercode == usercode).FirstOrDefault();
                    rank = rankmodel?.rank ?? 0;

                    query = query.Where(it => it.agent_code == usercode);

                    var total = new
                    {
                        callcount = query.Count(),
                        checkcount = checklist.Count(),
                        rank,
                        avgscore = Math.Round(checklist.Average(p => p.checkscore), 2),
                        maxscore = checklist.Max(p => p.checkscore),
                        minscore = checklist.Min(p => p.checkscore)
                    };

                    var obj = new
                    {
                        total,
                        ranklist
                    };

                    return obj;
                }
                else
                {
                    var total = new
                    {
                        callcount = query.Count(),
                        checkcount = checklist.Count(),
                        maxscore = ranklist.Max(p => p.avgscore),
                        minscore = ranklist.Min(p => p.avgscore)
                    };

                    var obj = new
                    {
                        total,
                        ranklist
                    };

                    return obj;
                }
            }
            else
            {

                if (!string.IsNullOrEmpty(usercode))
                {
                    query = query.Where(it => it.agent_code == usercode);
                    var total = new
                    {
                        callcount = query.Count(),
                        checkcount = 0,
                        rank = 0,
                        avgscore = 0,
                        maxscore = 0,
                        minscore = 0,
                    };

                    var obj = new
                    {
                        total,
                        ranklist = new List<string>()
                    };

                    return obj;
                }
                else
                {
                    var total = new
                    {
                        callcount = query.Count(),
                        checkcount = checklist.Count(),
                        maxscore = 0,
                        minscore = 0
                    };
                    var obj = new
                    {
                        total,
                        ranklist = new List<string>()
                    };

                    return obj;
                }
            }
        }

        /// <summary>
        /// 获取坐席月度分布图
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <returns></returns>
        public object GetSeatMonthChart(string stime, string etime)
        {
            var query = from p in _collection_mw_call_records.AsQueryable()
                        join c in _collection.AsQueryable() on p.id equals c.recordid into checkinfo
                        join u in _collection_sys_user_account.AsQueryable() on p.agent_code equals u.usercode into userinfo
                        select new
                        {
                            p.id,
                            p.agent_code,
                            username = userinfo.Count() > 0 ? userinfo.First().username : "",
                            p.begintime,
                            checktype = checkinfo.Count() > 0 ? 1 : 0,
                            checkscore = checkinfo.Count() > 0 ? checkinfo.First().qctotalscore : 0
                        };

            #region 查询条件
            if (!string.IsNullOrEmpty(stime))
                query = query.Where(it => it.begintime >= Convert.ToDateTime(stime + " 00:00:00"));
            if (!string.IsNullOrEmpty(etime))
                query = query.Where(it => it.begintime <= Convert.ToDateTime(etime + " 23:59:59"));
            #endregion
            
            var checklist = query.Where(p => p.checktype == 1);
            if (checklist.Count() > 0)
            {
                var totalavgscore = checklist.Average(p => p.checkscore);
                var seatlist = checklist.GroupBy(p => new { p.agent_code, p.username }).Select(p => new
                {
                    usercode = p.Key.agent_code,
                    p.Key.username,
                    avgscore = p.Average(q => q.checkscore)
                }).ToList().Select(p => new
                {
                    p.usercode,
                    p.username,
                    avgscore = Math.Round(p.avgscore, 2),
                    checkscore = checklist.Where(c => c.agent_code == p.usercode).Select(c => c.checkscore)
                });

                var obj = new
                {
                    totalavgscore,
                    seatlist
                };

                return obj;
            }
            else
            {
                var obj = new
                {
                    totalavgscore = 0,
                    seatlist = new List<string>()
                };

                return obj;
            }
        }

        //(string usercode, int calltype, string dept, int checktype,
        //string stime, string etime, int pageindex, int pagesize, out int recordcount)
        public IEnumerable<object> GetLists(  string dept,string stime, string etime, int pageindex, int pagesize, out int recordcount)
        {
            var query = from p in _collection_mw_call_records.AsQueryable()
                        join c in _collection.AsQueryable() on p.id equals c.recordid into checkinfo
                        join u in _collection_sys_user_account.AsQueryable() on p.agent_code equals u.usercode into userinfo
                        select new
                        {
                            p.id,
                            createuser = checkinfo.Count() > 0 ? checkinfo.First().createusername : "",
                            username = userinfo.Count() > 0 ? userinfo.First().username : "",
                            p.agent_code,
                            checkcount=1,
                                                        
                            deptid= userinfo.Count() > 0 ? userinfo.First().dept_id  : "",
                            
                           
                            p.begintime,
                            
                            
                            checktype = checkinfo.Count() > 0 ? 1 : 0,
                            checkdate = checkinfo.Count() > 0 ? checkinfo.First().createtime : DateTime.MinValue,
                            checkscore = checkinfo.Count() > 0 ? checkinfo.First().qctotalscore : 0,
                            checkresult = checkinfo.Count() > 0 ? checkinfo.First().result  : null  
                        };

            #region 查询条件
            query = query.Where(it => it.checkresult!=null );
            if (!string.IsNullOrEmpty(dept))
                query = query.Where(it => it.deptid.Contains(dept));
            
            if (!string.IsNullOrEmpty(stime))
                query = query.Where(it => it.begintime >= Convert.ToDateTime(stime + " 00:00:00"));
            if (!string.IsNullOrEmpty(etime))
                query = query.Where(it => it.begintime <= Convert.ToDateTime(etime + " 23:59:59"));

            #endregion

            recordcount = query.Count();
            var list = query.OrderByDescending(p => p.id).Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();

            return list;
        }
    }
}