zzdyrmyyAPI/代码/MadRunFabirc.../Distribution.../Distribution...

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

namespace DistributionApi.Repositories
{
    public class DistributionWorkOrderReportRepository : BaseRepository<Pro_Project_Info, string>, IDistributionWorkOrderReportRepository
    {
        protected readonly ILogger<BaseRepository<Pro_Project_Info, string>> _logger;
        protected readonly IMongoCollection<Distri_Goods_Type> _collection_distri_goods_type;
        protected readonly IMongoCollection<Distri_Goods> _collection_distri_goods;
        protected readonly IMongoCollection<Distri_Workorder> _collection_distri_workorder;
        protected readonly IMongoCollection<Sys_Building> _collection_sys_building;
        protected readonly IMongoCollection<Sys_Floor> _collection_sys_floor;
        protected readonly IMongoCollection<Sys_Role_Info> _collection_role_info;
        protected readonly IMongoCollection<Sys_User_Account> _collection_sys_user_account;
        public DistributionWorkOrderReportRepository(IOptions<MongodbOptions> settings,
            ILogger<BaseRepository<Pro_Project_Info, string>> logger) : base(settings, logger)
        {
            _collection_distri_goods_type = _context.GetCollection<Distri_Goods_Type>();  //获取集合
            _collection_distri_goods = _context.GetCollection<Distri_Goods>();  //获取集合
            _collection_distri_workorder = _context.GetCollection<Distri_Workorder>();  //获取集合
            _collection_sys_building = _context.GetCollection<Sys_Building>();  //获取集合
            _collection_sys_floor = _context.GetCollection<Sys_Floor>();  //获取集合
            _collection_role_info = _context.GetCollection<Sys_Role_Info>();
            _collection_sys_user_account = _context.GetCollection<Sys_User_Account>();  //获取集合
            _logger = logger;
        }

        #region 物品种类
        /// <summary>
        /// 物品种类运送量 - 总体统计报表
        /// </summary>
        /// <param name="projectid">项目id</param>
        /// <param name="departmentname">科室</param>
        /// <param name="usercode">用户code</param>
        /// <param name="stime">开始时间</param>
        /// <param name="etime">结束时间</param>
        /// <param name="userinfo">用户项目及相关信息</param>
        /// <returns></returns>
        public object GetWOCountForGoods(string projectid, string departmentname, string usercode, string stime, string etime, UserAccountInfoModel userinfo)
        {
            //, UserAccountInfoModel userinfo
            //物品分类一级
            var query = from goodstype in _collection_distri_goods_type.AsQueryable()
                        where goodstype.isdelete == 0 //&& goodstype.parent_id == null
                        select goodstype;

            var query_goods = from goods in _collection_distri_goods.AsQueryable()
                        where goods.isdelete == 0
                        select goods;

            var query_wo = from wo in _collection_distri_workorder.AsQueryable()
                           where wo.isdelete == 0 && (wo.wostate == (int)EnumDistributionState.finish || wo.wostate == (int)EnumDistributionState.evaluated)
                           select wo;

            query = query.Where(x => x.parent_id == null);
            if (!string.IsNullOrEmpty(projectid))
            {
                query = query.Where(it => it.projectid == projectid);
                query_wo = query_wo.Where(it => it.projectid == projectid);
            }
            if (userinfo.isallproject == 0)
            {
                query = query.Where(it => userinfo.projectlist.Contains(it.projectid));
                query_wo = query_wo.Where(it => userinfo.projectlist.Contains(it.projectid));
            }
            if (!string.IsNullOrEmpty(departmentname))
                query_wo = query_wo.Where(it => it.deliverylist.Select(x=>x.department_name).Contains(departmentname));
            if (!string.IsNullOrEmpty(usercode))
                query_wo = query_wo.Where(it => it.touser.Contains(usercode) || it.touser_elevator == usercode);
            DateTime nowtime = DateTime.Now;
            string start = nowtime.ToString("yyyy-MM-dd") + " 00:00:00";
            string end = nowtime.ToString("yyyy-MM-dd") + " 23:59:59";

            if (!string.IsNullOrEmpty(stime))
                query_wo = query_wo.Where(it => it.createtime >= DateTime.Parse(stime + " 00:00:00"));
            else
                query_wo = query_wo.Where(it => it.createtime >= DateTime.Parse(start));
            if (!string.IsNullOrEmpty(etime))
                query_wo = query_wo.Where(it => it.createtime <= DateTime.Parse(etime + " 23:59:59"));
            else
                query_wo = query_wo.Where(it => it.createtime <= DateTime.Parse(end));

            var list_wo = query_wo.ToList();
            var list = query.ToList().Select(p =>
            {
                //获取某一分类下的物品list<string> id
                var goodsids = query_goods.Where(x => x.kindid == p.id).Select(x=>x.id).ToList();
                //获取物品二级id
                //var list_goodtype_ids = query.Where(x => x.parent_id == p.id).Select(x => x.id);
                int goods_Sum = 0;
                if (goodsids != null && goodsids.Count > 0)
                    goods_Sum = list_wo.Sum(x => x.goodslist.Where(g => goodsids.Contains(g.goodsid)).Sum(g => g.num));    // list_goodtype_ids.Contains(g.goodsid;

                return new
                {
                    p.id,
                    p.kindname,
                    p.remark,
                    p.sortnum,
                    goodssum = goods_Sum
                };
            }).OrderByDescending(p => p.sortnum);

            var obj = new
            {
                list = list,
                total = list.Sum(x => x.goodssum)
            };
            return list;
        }

        /// <summary>
        /// 物品运送量 - 总体统计报表
        /// </summary>
        /// <param name="projectid">项目id</param>
        /// <param name="departmentname">科室</param>
        /// <param name="usercode">用户code</param>
        /// <param name="stime">开始时间</param>
        /// <param name="etime">结束时间</param>
        /// <param name="userinfo">用户项目及相关信息</param>
        /// <returns></returns>
        public object GetWOCountForGoodsDetail(string projectid, string departmentname, string usercode, string stime, string etime, UserAccountInfoModel userinfo)
        {
            var query_goods = from goods in _collection_distri_goods.AsQueryable()
                        where goods.isdelete == 0
                        select goods;

            var query_wo = from wo in _collection_distri_workorder.AsQueryable()
                           where wo.isdelete == 0 && (wo.wostate == (int)EnumDistributionState.finish || wo.wostate == (int)EnumDistributionState.evaluated)
                           select wo;
            
            if (!string.IsNullOrEmpty(projectid))
            {
                query_goods = query_goods.Where(it => it.projectid == projectid);
                query_wo = query_wo.Where(it => it.projectid == projectid);
            }
            if (userinfo.isallproject == 0)
            {
                query_goods = query_goods.Where(it => userinfo.projectlist.Contains(it.projectid));
                query_wo = query_wo.Where(it => userinfo.projectlist.Contains(it.projectid));
            }

            if (!string.IsNullOrEmpty(usercode))
                query_wo = query_wo.Where(it => it.touser.Contains(usercode) || it.touser_elevator == usercode);
            if (!string.IsNullOrEmpty(departmentname))
                query_wo = query_wo.Where(it => it.deliverylist.Select(x => x.department_name).Contains(departmentname));
            DateTime nowtime = DateTime.Now;
            string start = nowtime.ToString("yyyy-MM-dd") + " 00:00:00";
            string end = nowtime.ToString("yyyy-MM-dd") + " 23:59:59";

            if (!string.IsNullOrEmpty(stime))
                query_wo = query_wo.Where(it => it.createtime >= DateTime.Parse(stime + " 00:00:00"));
            else
                query_wo = query_wo.Where(it => it.createtime >= DateTime.Parse(start));
            if (!string.IsNullOrEmpty(etime))
                query_wo = query_wo.Where(it => it.createtime <= DateTime.Parse(etime + " 23:59:59"));
            else
                query_wo = query_wo.Where(it => it.createtime <= DateTime.Parse(end));

            var list_wo = query_wo.ToList();
            var list = query_goods.ToList().Select(p =>
            {
                int goods_Sum = list_wo.Sum(x => x.goodslist.Where(g => g.goodsid == p.id).Sum(g => g.num));

                return new
                {
                    p.id,
                    p.goodsname,
                    p.remark,
                    p.sortnum,
                    goodssum = goods_Sum
                };
            }).OrderByDescending(p => p.sortnum);

            var obj = new
            {
                list = list,
                total = list.Sum(x => x.goodssum)
            };
            return list;
        }
        
        #endregion

        #region 配送工单
        /// <summary>
        /// 医务科室运送报单量 - 统计报表
        /// </summary>
        /// <param name="projectid">项目id</param>
        /// <param name="usercode">用户code</param>
        /// <param name="stime">开始时间</param>
        /// <param name="etime">结束时间</param>
        /// <param name="userinfo">用户项目及相关信息</param>
        /// <returns></returns>
        public object GetWOCountForDepartment(string projectid, string usercode, string stime, string etime, UserAccountInfoModel userinfo)
        {
            var query_build = from build in _collection_sys_building.AsQueryable()
                              where build.isdelete == 0 //&& goodstype.parent_id == null
                              select build;

            var query = from floor in _collection_sys_floor.AsQueryable()
                        where floor.isdelete == 0
                        select floor;

            var query_wo = from wo in _collection_distri_workorder.AsQueryable()
                           where wo.isdelete == 0   && (wo.wostate == (int)EnumDistributionState.finish || wo.wostate == (int)EnumDistributionState.evaluated)
                           select wo;

            if (!string.IsNullOrEmpty(projectid))
            {
                query_build = query_build.Where(it => it.projectid == projectid);
                query_wo = query_wo.Where(it => it.projectid == projectid);
            }
            if (userinfo.isallproject == 0)
            {
                query_build = query_build.Where(it => userinfo.projectlist.Contains(it.projectid));
                query_wo = query_wo.Where(it => userinfo.projectlist.Contains(it.projectid));
            }

            if (!string.IsNullOrEmpty(usercode))
                query_wo = query_wo.Where(it => it.touser.Contains(usercode) || it.touser_elevator == usercode);
            DateTime nowtime = DateTime.Now;
            string start = nowtime.ToString("yyyy-MM-dd") + " 00:00:00";
            string end = nowtime.ToString("yyyy-MM-dd") + " 23:59:59";

            if (!string.IsNullOrEmpty(stime))
                query_wo = query_wo.Where(it => it.createtime >= DateTime.Parse(stime + " 00:00:00"));
            else
                query_wo = query_wo.Where(it => it.createtime >= DateTime.Parse(start));
            if (!string.IsNullOrEmpty(etime))
                query_wo = query_wo.Where(it => it.createtime <= DateTime.Parse(etime + " 23:59:59"));
            else
                query_wo = query_wo.Where(it => it.createtime <= DateTime.Parse(end));

            //获取科室（楼层）
            var buildids = query_build.Select(x => x.id).ToList();
            var list_Floor = query.ToList().Where(x => buildids.Contains(x.buildid)).ToList();
            //获取工单
            var list_Wo = query_wo.ToList();
            int create_allnum = 0,
                completed_allnum = 0,
                unfinished_allnum = 0,
                canceled_allnum = 0;
            double workhoursall = 0;
            //科室 select
            var list = list_Floor.Select(p =>
            {
                var list_WoItem = list_Wo.Where(x => x.deliverylist.Count > 0 && x.deliverylist.ToList().FirstOrDefault().department_name == p.remark);
                //下单量
                int create_num = list_WoItem.Count();
                //完工量
                var list_Finish = list_WoItem.Where(x => x.wostate == (int)EnumDistributionState.finish || x.wostate == (int)EnumDistributionState.evaluated);
                int completed_num = list_Finish.Count();
                //未完工量
                int unfinished_num = list_WoItem.Where(x => x.wostate < (int)EnumDistributionState.finish || x.wostate == (int)EnumDistributionState.backed).Count();
                //取消量
                int canceled_num = list_WoItem.Where(x => x.wostate == (int)EnumDistributionState.canceled).Count();
                //运送工时
                double workhours = 0;
                foreach (var item in list_Finish)
                {
                    var assigtime = item.assigtime;
                    var dealtime = item.dealtime;
                    TimeSpan ts = dealtime.Subtract(assigtime);
                    double hours = ts.TotalHours;       //工时
                    workhours += hours;
                }

                return new
                {
                    p.id,
                    p.floorname,
                    p.sortnum,
                    departmentname = p.remark,
                    create_num,
                    completed_num,
                    unfinished_num,
                    canceled_num,
                    workhours = double.Parse(workhours.ToString("f2"))
                };
            }).OrderByDescending(p => p.sortnum);

            //合计总数
            create_allnum = list.Sum(x => x.create_num);
            completed_allnum = list.Sum(x => x.completed_num);
            unfinished_allnum = list.Sum(x => x.unfinished_num);
            canceled_allnum = list.Sum(x => x.canceled_num);
            workhoursall = list.Sum(x => x.workhours);

            var obj = new
            {
                list = list,
                total = new
                {
                    create_allnum,      //下单量
                    completed_allnum,   //完工量
                    unfinished_allnum,  //未完工量
                    canceled_allnum,    //取消量
                    workhours = double.Parse(workhoursall.ToString("f2")) //运送工时
                }
            };
            return obj;
        }
        
        /// <summary>
        /// 员工运送工作量 - 统计报表
        /// </summary>
        /// <param name="projectid">项目id</param>
        /// <param name="usercode">用户code</param>
        /// <param name="stime">开始时间</param>
        /// <param name="etime">结束时间</param>
        /// <param name="userinfo">用户项目及相关信息</param>
        /// <returns></returns>
        public object GetWOCountForUser(string projectid, string usercode, string stime, string etime, UserAccountInfoModel userinfo)
        {
            var query = from user in _collection_sys_user_account.AsQueryable()
                        join r in _collection_role_info.AsQueryable() on user.role_id equals r.id into roleinfo
                        where user.delete_flag == false && user.type != 2
                        select new
                        {
                            user.id,
                            user.usercode,
                            user.username,
                            user.projectlist,
                            projectid = user.projectlist != null && user.projectlist.Count() > 0 ? user.projectlist.First() : null,
                            roleid = user.role_id,
                            rolecode = roleinfo != null && roleinfo.Count() > 0 ? roleinfo.First().role_code : null
                        };

            var query_wo = from wo in _collection_distri_workorder.AsQueryable()
                           where wo.isdelete == 0  // && (wo.wostate == (int)EnumDistributionState.finish || wo.wostate == (int)EnumDistributionState.evaluated)
                           select wo;

            if (!string.IsNullOrEmpty(projectid))
            {
                query = query.Where(it => it.projectlist.Contains(projectid));
                query_wo = query_wo.Where(it => it.projectid == projectid);
            }
            if (userinfo.isallproject == 0)
            {
                //query = query.Where(it => userinfo.projectlist.Contains(it.projectlist));???
                query_wo = query_wo.Where(it => userinfo.projectlist.Contains(it.projectid));
            }

            if (!string.IsNullOrEmpty(usercode))
            {
                query = query.Where(it => it.usercode == usercode);
                query_wo = query_wo.Where(it => (it.touser.Contains(usercode) || it.touser_elevator == usercode));
            }
            query = query.Where(it => it.rolecode == "SSY_DTG" || it.rolecode == "SSY_PSG");

            DateTime nowtime = DateTime.Now;
            string start = nowtime.ToString("yyyy-MM-dd") + " 00:00:00";
            string end = nowtime.ToString("yyyy-MM-dd") + " 23:59:59";

            if (!string.IsNullOrEmpty(stime))
                query_wo = query_wo.Where(it => it.createtime >= DateTime.Parse(stime + " 00:00:00"));
            else
                query_wo = query_wo.Where(it => it.createtime >= DateTime.Parse(start));
            if (!string.IsNullOrEmpty(etime))
                query_wo = query_wo.Where(it => it.createtime <= DateTime.Parse(etime + " 23:59:59"));
            else
                query_wo = query_wo.Where(it => it.createtime <= DateTime.Parse(end));

            
            //获取工单
            var list_Wo = query_wo.ToList();
            int create_allnum = 0,
                completed_allnum = 0,
                unfinished_allnum = 0,
                canceled_allnum = 0,
                offence_allnum = 0;
            double workhoursall = 0;
            double workhours_allavg = 0;
            //用户列表 select
            var list = query.ToList().Select(p =>
            {
                var list_WoItem = list_Wo.Where(x => x.touser.Contains(p.usercode) || x.touser_elevator == p.usercode);  // || x.touser_elevator == usercode 只统计运送
                //任务总量
                int create_num = list_WoItem.Count();
                //完工量
                var list_Finish = list_WoItem.Where(x => x.wostate == (int)EnumDistributionState.finish || x.wostate == (int)EnumDistributionState.evaluated);
                int completed_num = list_Finish.Count();
                //未完工量
                int unfinished_num = list_WoItem.Where(x => x.wostate < (int)EnumDistributionState.finish || x.wostate == (int)EnumDistributionState.backed).Count();
                //取消量
                int canceled_num = list_WoItem.Where(x => x.wostate == (int)EnumDistributionState.canceled).Count();
                //违规量
                int offence_num = list_WoItem.Where(x => x.isoffence == 1).Count();
                //运送工时
                double workhours = 0;
                foreach (var item in list_Finish)
                {
                    var assigtime = item.assigtime;
                    var dealtime = item.dealtime;
                    TimeSpan ts = dealtime.Subtract(assigtime);
                    double hours = ts.TotalHours;       //工时
                    workhours += hours;
                }
                workhoursall += workhours;
                //运送平均耗时
                double workhours_avg = 0;
                if (create_num > 0)
                    workhours_avg = Math.Round(((double)workhours / create_num), 2);

                return new
                {
                    p.id,
                    p.usercode,
                    p.username,
                    create_num,
                    completed_num,
                    unfinished_num,
                    canceled_num,
                    offence_num,
                    workhours = double.Parse(workhours.ToString("f2")),
                    workhours_avg = double.Parse(workhours_avg.ToString("f2"))
                };
            });

            create_allnum = list.Sum(x => x.create_num);
            completed_allnum = list.Sum(x => x.completed_num);
            unfinished_allnum = list.Sum(x => x.unfinished_num);
            canceled_allnum = list.Sum(x => x.canceled_num);
            offence_allnum = list.Sum(x => x.offence_num);
            workhoursall = list.Sum(x => x.workhours);

            //运送平均耗时  - 总数
            if (create_allnum > 0)
                workhours_allavg = Math.Round(((double)workhoursall / create_allnum), 2);

            var obj = new
            {
                list = list,
                total = new
                {
                    create_allnum,      //任务总量
                    completed_allnum,   //完工量
                    unfinished_allnum,  //未完工量
                    canceled_allnum,    //取消量
                    offence_allnum,     //违规量
                    workhours = double.Parse(workhoursall.ToString("f2")),    //运送工时
                    workhours_avg = double.Parse(workhours_allavg.ToString("f2"))   //运送平均耗时
                }
            };
            return obj;
        }

        #endregion

        #region 工单评价
        /// <summary>
        /// 员工满意度评价 - 统计报表
        /// </summary>
        /// <param name="projectid">项目id</param>
        /// <param name="usercode">用户code</param>
        /// <param name="stime">开始时间</param>
        /// <param name="etime">结束时间</param>
        /// <param name="userinfo">用户项目及相关信息</param>
        /// <returns></returns>
        public object GetWOEvaluateCountForUser(string projectid, string usercode, string stime, string etime, UserAccountInfoModel userinfo)
        {
            var query = from user in _collection_sys_user_account.AsQueryable()
                        join r in _collection_role_info.AsQueryable() on user.role_id equals r.id into roleinfo
                        where user.delete_flag == false && user.type != 2
                        select new
                        {
                            user.id,
                            user.usercode,
                            user.username,
                            user.projectlist,
                            projectid = user.projectlist != null && user.projectlist.Count() > 0 ? user.projectlist.First() : null,
                            roleid = user.role_id,
                            rolecode = roleinfo != null && roleinfo.Count() > 0 ? roleinfo.First().role_code : null
                        };

            var query_wo = from wo in _collection_distri_workorder.AsQueryable()
                           where wo.isdelete == 0 && wo.wostate == (int)EnumDistributionState.evaluated     // || wo.wostate == (int)EnumDistributionState.evaluated)
                           select wo;

            if (!string.IsNullOrEmpty(projectid))
            {
                query = query.Where(it => it.projectlist.Contains(projectid));
                query_wo = query_wo.Where(it => it.projectid == projectid);
            }
            if (userinfo.isallproject == 0)
            {
                //query = query.Where(it => userinfo.projectlist.Contains(it.projectlist));???
                query_wo = query_wo.Where(it => userinfo.projectlist.Contains(it.projectid));
            }

            if (!string.IsNullOrEmpty(usercode))
            {
                query = query.Where(it => it.usercode == usercode);
                query_wo = query_wo.Where(it => it.touser.Contains(usercode) || it.touser_elevator == usercode);
            }
            query = query.Where(it => it.rolecode == "SSY_DTG" || it.rolecode == "SSY_PSG");
            DateTime nowtime = DateTime.Now;
            string start = nowtime.ToString("yyyy-MM-dd") + " 00:00:00";
            string end = nowtime.ToString("yyyy-MM-dd") + " 23:59:59";

            if (!string.IsNullOrEmpty(stime))
                query_wo = query_wo.Where(it => it.createtime >= DateTime.Parse(stime + " 00:00:00"));
            else
                query_wo = query_wo.Where(it => it.createtime >= DateTime.Parse(start));
            if (!string.IsNullOrEmpty(etime))
                query_wo = query_wo.Where(it => it.createtime <= DateTime.Parse(etime + " 23:59:59"));
            else
                query_wo = query_wo.Where(it => it.createtime <= DateTime.Parse(end));


            //获取工单
            var list_Wo = query_wo.ToList();
            int create_allnum = 0,
                elevator1_count = 0,
                elevator2_count = 0,
                elevator3_count = 0,
                elevator4_count = 0,
                elevator5_count = 0;
            double elevatorall = 0;
            double workhours_avgrate = 0;
            //用户列表 select
            var list = query.ToList().Select(p =>
            {
                var list_WoItem = list_Wo.Where(x => x.touser.Contains(p.usercode) || x.touser_elevator == p.usercode);
                //任务总量
                int create_num = list_WoItem.Count();
                //1星 - 20分
                int elevator1_num = list_WoItem.Count(x => x.starnum == 1);
                //2星 - 40分
                int elevator2_num = list_WoItem.Count(x => x.starnum == 2);
                //3星 - 60分
                int elevator3_num = list_WoItem.Count(x => x.starnum == 3);
                //4星 - 80分
                int elevator4_num = list_WoItem.Count(x => x.starnum == 4);
                //5星 - 100分
                int elevator5_num = list_WoItem.Count(x => x.starnum == 5);        
                //运送满意度
                double workhours_rate = 0;
                //评价满意星值
                double elevatornum = elevator3_num
                                + elevator4_num
                                + elevator5_num;
                //平均满意度 - 百分比
                if (create_num > 0)
                    workhours_rate = Math.Round(((double)elevatornum * 100 / create_num), 2);

                return new
                {
                    p.id,
                    p.usercode,
                    p.username,
                    create_num,
                    elevator1_num,
                    elevator2_num,
                    elevator3_num,
                    elevator4_num,
                    elevator5_num,
                    elevatornum = double.Parse(elevatornum.ToString("f2")),
                    workhours_rate = double.Parse(workhours_rate.ToString("f2"))
                };
            });

            create_allnum = list.Sum(x => x.create_num);
            elevator1_count = list.Sum(x => x.elevator1_num);
            elevator2_count = list.Sum(x => x.elevator2_num);
            elevator3_count = list.Sum(x => x.elevator3_num);
            elevator4_count = list.Sum(x => x.elevator4_num);
            elevator5_count = list.Sum(x => x.elevator5_num);
            elevatorall = list.Sum(x => x.elevatornum);
            //运送平均耗时  - 总数
            if (create_allnum > 0)
                workhours_avgrate = Math.Round(((double)elevatorall * 100 / create_allnum), 2);

            var obj = new
            {
                list = list,
                total = new
                {
                    create_allnum,      //评价总量
                    elevator1_count,    //评价量（20分）
                    elevator2_count,    //评价量（40分）
                    elevator3_count,    //评价量（60分）
                    elevator4_count,    //评价量（80分）
                    elevator5_count,    //评价量（100分）
                    elevatorall= double.Parse(elevatorall.ToString("f2")),    //分数
                    workhours_avgrate = double.Parse(workhours_avgrate.ToString("f2"))   //运送平均耗时
                }
            };
            return obj;
        }

        #endregion

    }
}