everlight.eap/backend/UFP/DllUfpDal/VisitLogDal.cs

using Cksoft.Data;
using DllUfpEntity;
using DllUfpEntity.Dto;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using DllUfpDal.Extension;
using DllEapEntity.Dtos;
using System.Threading.Tasks;
using Cksoft.Data.Repository;

namespace DllUfpDal
{
    public class VisitLogDal
    {
        public IDatabase CurrDb { get; set; }
        public VisitLogDal(IDatabase db)
        {
            CurrDb = db;
        }

        public int Add(VisitLog log)
        {
            return CurrDb.Insert(log);
        }

        /// <summary>
        /// 新增日志
        /// </summary>
        /// <param name="logDto"></param>
        public int Log(VisitLogDto logDto)
        {
            string errorinfo = string.Empty;
            //var staff = CurrDb.FindListForCondition<Staff>($" and a.FCode='{logDto.UserCode}'",
            //    ref errorinfo).FirstOrDefault();
            //if (staff == null || staff.IsSA == 1)
            //    return -1;
            var sql = $"select id from function where url='{logDto.Url}' limit 0,1";
            var funcId = Convert.ToInt32(CurrDb.FindObject(sql) ?? "-1");
            if (funcId > 0)
            {
                var log = new VisitLog
                {
                    FunctionId = funcId,
                    UserCode = logDto.UserCode,
                    VisitTime = DateTime.Now
                };
                return Add(log);
            }
            return -1;
        }

        #region 页面数据展示
        /// <summary>
        /// 按日期获取报表总的访问次数
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public IEnumerable<VisitLogAnalysisDto> GetVisitLogByDate(DateTime startTime, DateTime endTime)
        {
            endTime = endTime.AddDays(1);
            var days = Math.Ceiling((endTime - startTime).TotalDays);
            var sql = $@"select date_format(visittime,'%Y-%m-%d') as date,count(1) as count from visitlog a RIGHT JOIN `function` fun on a.FunctionId=fun.ID
                        where a.visitTime>='{startTime.ToString("yyyy-MM-dd")}' and a.visitTime<='{endTime.ToString("yyyy-MM-dd")}'
                        group by date_format(visittime, '%Y-%m-%d')";
            var dtos = CurrDb.FindList<VisitLogAnalysisDto>(sql);
            var list = new List<VisitLogAnalysisDto>();
            for (int i = 0; i < days; i++)
            {
                var currDate = Convert.ToDateTime(startTime.AddDays(i).ToString("yyyy-MM-dd"));
                var temp = new VisitLogAnalysisDto
                {
                    Date = currDate,
                    Count = dtos.FirstOrDefault(c => c.Date == currDate)?.Count ?? 0
                };
                list.Add(temp);
            }
            return list;
        }

        /// <summary>
        /// 将List转换为DataTable
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public DataTable GetVisitLogTable(DateTime startTime, DateTime endTime)
        {
            var list = GetVisitLogByDate(startTime, endTime);
            return list.TransferToTable();
        }

        /// <summary>
        /// 分页获取人员访问报表次数
        /// </summary>
        /// <param name="filter">主表过滤条件</param>
        /// <param name="subFilter">连接表过滤条件</param>
        /// <param name="start">开始记录数</param>
        /// <param name="length">返回长度</param>
        /// <param name="total">记录总数</param>
        /// <returns></returns>
        public IEnumerable<VisitLogAnalysisDto> GetStaffVisitLog(string filter, string subFilter, int start, int length,
             out int total, string sort = "staffCode", string order = "asc")
        {
            var countSql = $@"select count(1) from 
                      (select usercode,count(1) as count from visitlog a RIGHT JOIN `function` fun on a.FunctionId=fun.ID
                      where 1=1 {filter} group by usercode) t 
                      left join staff b on t.usercode=b.fcode 
                      where  1=1 and b.FCode is not  null {subFilter}";
            total = Convert.ToInt32(CurrDb.FindList<string>(countSql).FirstOrDefault() ?? "0");
            var sql = $@"select b.FCode as StaffCode,b.FName as StaffName,t.count from 
                      (select usercode,count(1) as count from visitlog a RIGHT JOIN `function` fun on a.FunctionId=fun.ID
                      where 1=1 {filter} group by usercode) t 
                      left join staff b on t.usercode=b.fcode 
                      where 1=1 and b.FCode is not  null  {subFilter} order by {sort} {order} limit {start - 1},{length}";
            // 获取员工职级信息
            var datas = CurrDb.FindList<VisitLogAnalysisDto>(sql);
            if (datas != null && datas.Count() > 0)
            {
                var staffCodes = datas.Select(c => c.StaffCode).Distinct();
                var empInfos = this.GetEmployeeInfos(staffCodes);
                Parallel.ForEach(datas, item =>
                {
                    item.Level = empInfos.FirstOrDefault(c => c.StaffCode == item.StaffCode)?.FLevel ?? "--";
                    item.Duty = empInfos.FirstOrDefault(c => c.StaffCode == item.StaffCode)?.Duty ?? "--";
                    item.Department = empInfos.FirstOrDefault(c => c.StaffCode == item.StaffCode)?.Dept ?? "--";
                });
            }
            return datas;
        }

        /// <summary>
        /// 获取人员访问报表总次数
        /// </summary>
        /// <param name="filter">主表过滤条件</param>
        /// <param name="subFilter">连接表过滤条件</param>
        /// <returns></returns>
        public VisitLogAnalysisDto GetTotalInfo(string filter, string subFilter)
        {
            var sql = $@"select sum(count) count from 
                      (select usercode,count(1) as count from visitlog a RIGHT JOIN `function` fun on a.FunctionId=fun.ID
                      where 1=1 {filter} group by usercode) t 
                      left join staff b on t.usercode=b.fcode 
                      where 1=1 and b.FCode is not  null {subFilter} ";
            var dto = CurrDb.FindList<VisitLogAnalysisDto>(sql).FirstOrDefault();
            if (dto != null)
            {
                dto.StaffCode = "Total";
                dto.StaffName = "总计";
            }
            return dto;
        }

        /// <summary>
        /// 分页获取报表被访问次数
        /// </summary>
        /// <param name="filter">主表过滤条件</param>
        /// <param name="subFilter">连接表过滤条件</param>
        /// <param name="start">开始记录数</param>
        /// <param name="length">返回长度</param>
        /// <param name="total">记录总数</param>
        /// <returns></returns>
        public IEnumerable<VisitLogAnalysisDto> GetReportVisitLog(string filter, string subFilter, int start, int length,
             out int total, string sort = "count", string order = "asc")
        {
            var roots = CurrDb.FindList<EapFunction>("select * from function where parentId=0");
            var rootIds = roots.Select(c => c.ID);
            var children = CurrDb.FindList<EapFunction>($"select * from function where parentid in ({string.Join(",", rootIds)})");
            
            var Ids = rootIds.Concat(children.Select(c => c.ID));
            var grandson = CurrDb.FindList<EapFunction>($"select * from function where parentid in ({string.Join(",", Ids)})");
            var allIds = Ids.Concat(grandson.Select(c => c.ID));

            //var countSql = $@"select count(1) from 
            //                 (select count(1) count,FunctionId from visitlog a
            //                 group by FunctionId) t 
            //                 inner join function b on t.FunctionId=b.id 
            //                 inner join function c on b.parentid=c.id ";
            var countSql = $@"select count(1) from(select b.id as reportId,ifnull(concat(c.fname,'/',b.fname),b.fname) reportname,ifnull(count,0) count from
                       function b
                       left join function c on b.ParentID=c.id 
                       LEFT JOIN function f on c.ParentID=f.ID
                       left join 
                       (select t.FunctionId ReportId,count from 
                       (select count(1) count,FunctionId from visitlog a
                       where 1=1 {filter}
                       group by FunctionId) t) s
                       on b.id=s.reportId
                       where b.id in ({string.Join(",", allIds)}) and b.url!='/' and b.url!='' {subFilter} )tt where tt.reportname !=''";
            total = Convert.ToInt32(CurrDb.FindList<string>(countSql).FirstOrDefault() ?? "0");
            //var sql = $@"select t.FunctionId ReportId,concat(c.fname,'/',b.fname) as reportName,count from 
            //            (select count(1) count,FunctionId from visitlog a
            //            where 1=1 {filter}
            //            group by FunctionId) t 
            //            inner join function b on t.FunctionId=b.id 
            //            inner join function c on b.parentid=c.id  
            //            where 1=1 {subFilter} order by {sort} {order} limit {start - 1},{length}";

            // 统计报表访问记录时将所有报表包含进去
            var sql = $@"
                       select * from(select b.id as reportId,IFNULL(CONCAT(f.fname,'/',c.fname,'/',b.fname),ifnull(concat(c.fname,'/',b.fname),b.fname))  reportname,ifnull(count,0) count from
                       function b
                       left join function c on b.ParentID=c.id 
                       LEFT JOIN function f on c.ParentID=f.ID
                       left join 
                       (select t.url ReportId,count from 
                       (select count(1) count,fun.url url from visitlog a RIGHT JOIN `function` fun on a.FunctionId=fun.ID
                       where 1=1  {filter}
                       group by fun.url) t) s
                       on b.Url=s.reportId
                       where b.id in ({string.Join(",", allIds)}) and b.url!='/' and b.url!='' {subFilter} )tt where tt.reportname !='' 
                       order by {sort} {order} limit {start - 1},{length} ";
            return CurrDb.FindList<VisitLogAnalysisDto>(sql);
        }

        /// <summary>
        /// 获取人员访问报表总次数
        /// </summary>
        /// <param name="filter">主表过滤条件</param>
        /// <param name="subFilter">连接表过滤条件</param>
        /// <returns></returns>
        public VisitLogAnalysisDto GetReportTotalInfo(string filter, string subFilter)
        {
            var sql = $@"select sum(count) count from 
                         (select count(1) count,fun.Url Url from visitlog a  RIGHT JOIN `function` fun on a.FunctionId=fun.ID
                         where 1=1 {filter}
                         group by fun.Url) t 
                         inner join function b on t.Url=b.Url 
                         left join function c on b.parentid=c.id  
                         where 1=1 {subFilter}";
            var dto = CurrDb.FindList<VisitLogAnalysisDto>(sql).FirstOrDefault();
            if (dto != null)
            {
                dto.StaffCode = "Total";
                dto.StaffName = "总计";  
            }
            return dto;
        }

        public IEnumerable<StaffReportLogDto> GetStaffReportLogs(string filter, string subFilter)
        {
            var sql = $@" select b.FCode StaffCode,b.FName StaffName,t.date,ifnull(concat(d.fname,'/',c.fname),c.fname) reportName,t.count  
                        from
                        (select count(1) count,usercode,FunctionId,date_format(visitTime,'%Y-%m-%d') date 
                        from visitlog a 
                        where 1=1 {filter}
                        group by UserCode,FunctionId,date_format(visitTime,'%Y-%m-%d') ) t 
                        inner join staff b on t.usercode=b.fcode 
                        inner join function c on t.FunctionId=c.id 
                        left join function d on c.parentid=d.id 
                        where 1=1 {subFilter} order by staffcode,date desc,count desc";
            var entities = CurrDb.FindList<VisitLogAnalysisDto>(sql);
            var list = new List<StaffReportLogDto>();
            var no = 0;
            if (entities != null && entities.Count() > 0)
            {
                var staffCodes = entities.Select(c => c.StaffCode).Distinct();
                var empInfos = this.GetEmployeeInfos(staffCodes);
                var nameGroups = entities.GroupBy(c => new { c.StaffCode, c.StaffName });
                if (nameGroups != null && nameGroups.Count() > 0)
                {
                    foreach (var item in nameGroups)
                    {
                        var renderName = true;
                        no++;
                        var dateGroups = item.GroupBy(c => c.Date);
                        if (dateGroups != null && dateGroups.Count() > 0)
                        {
                            foreach (var dateItem in dateGroups)
                            {
                                var renderDate = true;
                                foreach (var reportItem in dateItem)
                                {
                                    var temp = new StaffReportLogDto
                                    {
                                        StaffCode = item.Key.StaffCode,
                                        StaffName = item.Key.StaffName,
                                        Department = empInfos.FirstOrDefault(c => c.StaffCode == item.Key.StaffCode)?.Dept ?? "--",
                                        NameRowSpan = item.Count(),
                                        TotalCount = item.Sum(c => c.Count),
                                        Date = dateItem.Key.Value.ToString("yyyy-MM-dd"),
                                        DateCount = dateItem.Sum(c => c.Count),
                                        DateRowSpan = dateItem.Count(),
                                        ReportName = reportItem.ReportName,
                                        Count = reportItem.Count,
                                        RenderName = renderName,
                                        RenderDate = renderDate,
                                        No = no
                                    };
                                    list.Add(temp);
                                    renderName = false;
                                    renderDate = false;
                                }
                            }
                        }
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 获取欧菲光员工职级信息
        /// </summary>
        /// <param name="staffCodes"></param>
        /// <returns></returns>
        public IEnumerable<EmployeeInfo> GetEmployeeInfos(IEnumerable<string> staffCodes)
        {
            var filter = $" and a.EMP_CODE in ({string.Join(",", staffCodes.Select(c => $"'{c}'"))})";
            using (IDatabase empDb = DbFactory.Base("OfilmDeptInfo"))
            {
                var sql = $"select EMP_CODE StaffCode,DEPT_NAME Dept,JOBNAME Duty from EAP_USER_ACCESS_V a where 1=1 {filter}";
                return empDb.FindList<EmployeeInfo>(sql);
            }
        }
        #endregion
    }
}