跳到主要内容
登录

github/tai

公开

Tai · Windows 软件与网站使用时长统计。上游 Planshit/Tai 的源码镜像，非灵栈原创；MIT 许可。来源：https://github.com/Planshit/Tai 。本次为手动同步，暂不提供安装包。

浏览文件
main

分支

标签

WebData.cs

877 行 · 35.3 KB历史下载
1using Core.Librarys;
2using Core.Librarys.Browser;
3using Core.Librarys.SQLite;
4using Core.Models.Data;
5using Core.Models.Db;
6using Core.Models.WebPage;
7using Core.Servicers.Interfaces;
8using CsvHelper;
9using Newtonsoft.Json;
10using Npoi.Mapper;
11using NPOI.SS.Formula.Functions;
12using Org.BouncyCastle.Security;
13using System;
14using System.Collections.Generic;
15using System.Diagnostics;
16using System.Globalization;
17using System.IO;
18using System.Linq;
19using System.Linq.Expressions;
20
21using System.Threading.Tasks;
22using static System.Windows.Forms.AxHost;
23
24namespace Core.Servicers.Instances
25{
26 public class WebData : IWebData
27 {
28 private readonly IDatabase _database;
29 private readonly object _createUrlLocker = new object();
30 public WebData(IDatabase database)
31 {
32 _database = database;
33 }
34
35 #region AddUrlBrowseTime
36 public void AddUrlBrowseTime(Site site_, int duration_, DateTime? dateTime_ = null)
37 {
38 Debug.WriteLine("AddUrlBrowseTime");
39 Task.Run(() =>
40 {
41 try
42 {
43 if (string.IsNullOrEmpty(site_.Url))
44 {
45 return;
46 }
47
48 var dateTime = dateTime_.HasValue ? dateTime_.Value : DateTime.Now;
49 var logTime = new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, dateTime.Hour, 0, 0);
50 if (dateTime.Minute == 59 && dateTime.Second == 59)
51 {
52 AddUrlBrowseTime(site_, duration_, logTime.AddHours(1));
53 return;
54 }
55 // 当前时段时长
56 int nowTimeDuration = duration_;
57 // 下一个时段时长
58 int nextTimeDuration = 0;
59
60 // 当前时段最大使用时长
61 int nowTimeMaxDuration = (60 - dateTime.Minute) * 60;
62
63 if (duration_ > 3600)
64 {
65 nowTimeDuration = 3600;
66 nextTimeDuration = duration_ - 3600;
67 }
68 if (nowTimeDuration > nowTimeMaxDuration)
69 {
70 nextTimeDuration += nowTimeDuration - nowTimeMaxDuration;
71
72 nowTimeDuration = nowTimeMaxDuration;
73 }
74
75 //var db = _database.GetContext("AddUrlBrowseTime");
76 //using (var db = new TaiDbContext())
77 using (var db = _database.GetWriterContext())
78 {
79 // 获取站点信息
80 string domain = UrlHelper.GetDomain(site_.Url);
81 var site = db.WebSites.Where(m => m.Domain == domain).FirstOrDefault();
82 Debug.WriteLine("AddUrlBrowseTime 获取站点数据");
83
84 if (site == null)
85 {
86 site = db.WebSites.Add(new Models.Db.WebSiteModel()
87 {
88 Title = UrlHelper.GetName(site_.Url),
89 Domain = domain,
90 });
91
92 db.SaveChanges();
93 }
94
95 // 获取链接
96 var url = GetCreateUrl(db, site_);
97 if (url == null)
98 {
99 throw new Exception("在创建URL时异常");
100 }
101
102 // 记录
103 var log = db.WebBrowserLogs.Where(m => m.LogTime == logTime && m.UrlId == url.ID).FirstOrDefault();
104
105 if (log != null)
106 {
107 // 时长
108 int duration = log.Duration + nowTimeDuration;
109 // 判断是否超出
110 if (log.Duration + nowTimeDuration > 3600)
111 {
112 duration = 3600;
113 nextTimeDuration += log.Duration + nowTimeDuration - 3600;
114 }
115
116 // 更新记录
117 log.Duration = duration;
118 }
119 else
120 {
121 // 新记录
122 log = new Models.Db.WebBrowseLogModel()
123 {
124 Duration = nowTimeDuration,
125 UrlId = url.ID,
126 LogTime = logTime,
127 SiteId = site.ID,
128 };
129
130
131 db.WebBrowserLogs.Add(log);
132 }
133 site.Duration += nowTimeDuration;
134 db.SaveChanges();
135 }
136 if (nextTimeDuration > 0)
137 {
138 AddUrlBrowseTime(site_, nextTimeDuration, logTime.AddHours(1));
139 }
140 }
141 catch (Exception ex)
142 {
143 Logger.Error($"在更新链接[{site_.Url}]时长[{duration_}]时异常，{ex}");
144 }
145 finally
146 {
147 _database.CloseWriter();
148 }
149 });
150
151 }
152 #endregion
153
154 #region UpdateUrlFavicon
155 public void UpdateUrlFavicon(Site site_, string iconFile_)
156 {
157 if (string.IsNullOrEmpty(iconFile_) || string.IsNullOrEmpty(site_.Url))
158 {
159 return;
160 }
161
162 Debug.WriteLine("UpdateUrlFavicon");
163
164 Task.Run(() =>
165 {
166
167 // 判断是否是主域名
168 bool isIndex = UrlHelper.IsIndexUrl(site_.Url);
169
170 //// 获取主域名
171 //string domain = UrlHelper.GetDomain(site_.Url);
172
173 // 获取站点信息
174 using (var db = _database.GetWriterContext())
175 {
176 // 更新站点图标
177 var site = GetCreateWebSite(db, site_.Url);
178 Debug.WriteLine("UpdateUrlFavicon 获取站点数据");
179 if (site != null && (string.IsNullOrEmpty(site.IconFile) || (isIndex && site.IconFile != iconFile_)))
180 {
181 site.IconFile = iconFile_;
182 }
183
184 // 更新链接图标
185 var url = GetCreateUrl(db, site_);
186 if (url != null)
187 {
188 url.IconFile = iconFile_;
189 }
190
191 db.SaveChanges();
192 _database.CloseWriter();
193 }
194 });
195
196 }
197 #endregion
198
199 #region GetCreateSite
200 /// <summary>
201 /// 输入URL获取站点信息,不存在时创建
202 /// </summary>
203 /// <param name="url_">URL</param>
204 /// <returns></returns>
205 private WebSiteModel GetCreateWebSite(TaiDbContext db, string url_)
206 {
207 // 获取主域名
208 string domain = UrlHelper.GetDomain(url_);
209
210 //using (var db = _database.GetWriterContext())
211 //{
212 var site = db.WebSites.Where(m => m.Domain == domain).FirstOrDefault();
213 if (site == null)
214 {
215 site = db.WebSites.Add(new WebSiteModel()
216 {
217 Title = UrlHelper.GetName(url_),
218 Domain = domain,
219 });
220 db.SaveChanges();
221 }
222
223 return site;
224 //}
225 }
226 #endregion
227 #region GetCreateUrl
228 /// <summary>
229 /// 获取url数据,不存在时创建
230 /// </summary>
231 /// <param name="url_"></param>
232 /// <returns></returns>
233 private Models.Db.WebUrlModel GetCreateUrl(TaiDbContext db, Site site_)
234 {
235 lock (_createUrlLocker)
236 {
237 var result = db.WebUrls.Where(m => m.Url == site_.Url).FirstOrDefault();
238 if (result == null)
239 {
240 result = db.WebUrls.Add(new Models.Db.WebUrlModel()
241 {
242 Url = site_.Url,
243 Title = site_.Title,
244 });
245 db.SaveChanges();
246 }
247 else
248 {
249 if (result.Title != site_.Title)
250 {
251 result.Title = site_.Title;
252 db.SaveChanges();
253 }
254 }
255 return result;
256
257 }
258 }
259 #endregion
260
261 #region GetDateRangeWebSiteList
262 public List<WebSiteModel> GetDateRangeWebSiteList(DateTime start, DateTime end, int take = 0, int skip = -1, bool isTime_ = false)
263 {
264 if (isTime_)
265 {
266 start = new DateTime(start.Year, start.Month, start.Day, start.Hour, 0, 0);
267 end = new DateTime(end.Year, end.Month, end.Day, end.Hour, 59, 59);
268 }
269 else
270 {
271 start = new DateTime(start.Year, start.Month, start.Day, 0, 0, 0);
272 end = new DateTime(end.Year, end.Month, end.Day, 23, 59, 59);
273 }
274 using (var db = _database.GetReaderContext())
275 {
276 var data = db.WebBrowserLogs
277 .Where(m => m.LogTime >= start && m.LogTime <= end && m.SiteId != 0)
278 .GroupBy(m => m.SiteId)
279 .Select(s => new
280 {
281 //Title = s.FirstOrDefault().Site.Title,
282 //Domain = s.FirstOrDefault().Site.Domain,
283 //CategoryID = s.FirstOrDefault().Site.CategoryID,
284 //IconFile = s.FirstOrDefault().Site.IconFile,
285 //ID = s.FirstOrDefault().Site.ID,
286 Site = s.FirstOrDefault().Site,
287 Duration = s.Sum(m => m.Duration),
288 });
289
290 if (skip > 0 && take > 0)
291 {
292 data = data.OrderByDescending(m => m.Duration).Skip(skip).Take(take);
293 }
294 else if (skip > 0)
295 {
296 data = data.OrderByDescending(m => m.Duration).Skip(skip);
297 }
298 else if (take > 0)
299 {
300 data = data.OrderByDescending(m => m.Duration).Take(take);
301 }
302 else
303 {
304 data = data.OrderByDescending(m => m.Duration);
305 }
306 var list = data.Select(s => new
307 {
308 Alias = s.Site.Alias,
309 Title = s.Site.Title,
310 Domain = s.Site.Domain,
311 CategoryID = s.Site.CategoryID,
312 IconFile = s.Site.IconFile,
313 ID = s.Site.ID,
314 Duration = s.Duration,
315 }).ToList();
316
317 var result = JsonConvert.DeserializeObject<List<WebSiteModel>>(JsonConvert.SerializeObject(list));
318 return result;
319 }
320 }
321
322
323 #endregion
324
325 #region GetWebSiteCategories
326 public List<WebSiteCategoryModel> GetWebSiteCategories()
327 {
328 using (var db = _database.GetReaderContext())
329 {
330 var result = db.WebSiteCategories.ToList();
331 return result;
332 }
333 }
334
335
336 #endregion
337
338 #region CreateWebSiteCategory
339 public WebSiteCategoryModel CreateWebSiteCategory(WebSiteCategoryModel data_)
340 {
341 using (var db = _database.GetWriterContext())
342 {
343 var result = db.WebSiteCategories.Add(data_);
344 db.SaveChanges();
345 _database.CloseWriter();
346 return result;
347 }
348 }
349 #endregion
350
351 #region UpdateWebSiteCategory
352 public void UpdateWebSiteCategory(WebSiteCategoryModel data_)
353 {
354 using (var db = _database.GetWriterContext())
355 {
356 db.Entry(data_).State = System.Data.Entity.EntityState.Modified;
357 db.SaveChanges();
358 _database.CloseWriter();
359 }
360 }
361 #endregion
362
363 #region DeleteWebSiteCategory
364 public void DeleteWebSiteCategory(WebSiteCategoryModel data_)
365 {
366 Task.Run(() =>
367 {
368 using (var db = _database.GetWriterContext())
369 {
370 // 取消网站关联分类
371 db.Database.ExecuteSqlCommand("update WebSiteModels set CategoryID=0 where CategoryID=" + data_.ID);
372
373 // 删除分类
374 db.Entry(data_).State = System.Data.Entity.EntityState.Deleted;
375 db.SaveChanges();
376 _database.CloseWriter();
377 }
378 });
379 }
380 #endregion
381 #region GetWebSites
382 public List<WebSiteModel> GetWebSites(int categoryId_)
383 {
384 using (var db = _database.GetReaderContext())
385 {
386 var result = db.WebSites.Where(m => m.CategoryID == categoryId_).ToList();
387 return result;
388 }
389 }
390 #endregion
391 #region GetWebSitesCount
392 public int GetWebSitesCount(int categoryId_)
393 {
394 using (var db = _database.GetReaderContext())
395 {
396 var result = db.WebSites.Where(m => m.CategoryID == categoryId_).Count();
397 return result;
398 }
399 }
400
401
402
403 //public List<WebSiteModel> GetWebSites(Expression<Func<WebSiteModel, bool>> predicate_)
404 //{
405 // using (var db = new TaiDbContext())
406 // {
407 // return db.WebSites.Where(predicate_).ToList();
408 // }
409 //}
410 #endregion
411 #region GetUnSetCategoryWebSites
412 public List<WebSiteModel> GetUnSetCategoryWebSites()
413 {
414 using (var db = _database.GetReaderContext())
415 {
416 var result = db.WebSites.Where(m => m.CategoryID == 0).ToList();
417 return result;
418 }
419 }
420
421
422 #endregion
423
424 public void UpdateWebSitesCategory(int[] siteIds_, int categoryId_)
425 {
426 Task.Run(() =>
427 {
428 using (var db = _database.GetWriterContext())
429 {
430 string sql = $"update WebSiteModels set CategoryID={categoryId_} where ID in ({string.Join(",", siteIds_)})";
431 db.Database.ExecuteSqlCommand(sql);
432 db.SaveChanges();
433 _database.CloseWriter();
434 }
435 });
436 }
437
438 public List<CommonDataModel> GetCategoriesStatistics(DateTime start_, DateTime end_)
439 {
440 using (var db = _database.GetReaderContext())
441 {
442 var sql = "select sum(WebBrowseLogModels.Duration) as Value,WebSiteCategoryModels.ID as ID,WebSiteCategoryModels.Name as Name from WebBrowseLogModels join WebSiteModels on WebBrowseLogModels.SiteId=WebSiteModels.ID JOIN WebSiteCategoryModels on WebSiteModels.CategoryID=WebSiteCategoryModels.ID WHERE WebBrowseLogModels.LogTime>='" + start_.ToString("yyyy-MM-dd 00:00:00") + "' and WebBrowseLogModels.LogTime<= '" + end_.ToString("yyyy-MM-dd 23:59:59") + "' GROUP BY WebSiteCategoryModels.ID";
443
444 var data = db.Database.SqlQuery<CommonDataModel>(sql).ToList();
445 // 未分类
446
447 sql = "select sum(WebBrowseLogModels.Duration) as Value,WebSiteModels.CategoryID as ID,WebSiteModels.Title as Name from WebBrowseLogModels left join WebSiteModels on WebBrowseLogModels.SiteId=WebSiteModels.ID WHERE WebSiteModels.CategoryID=0 AND WebBrowseLogModels.LogTime>='" + start_.ToString("yyyy-MM-dd 00:00:00") + "' and WebBrowseLogModels.LogTime<= '" + end_.ToString("yyyy-MM-dd 23:59:59") + "' Group BY WebSiteModels.CategoryID";
448 var noCategoryData = db.Database.SqlQuery<CommonDataModel>(sql).ToList();
449 var result = data.Concat(noCategoryData).ToList();
450 return result;
451 }
452 }
453
454 public WebSiteCategoryModel GetWebSiteCategory(int categoryId_)
455 {
456 using (var db = _database.GetReaderContext())
457 {
458 var result = db.WebSiteCategories.Where(m => m.ID == categoryId_).FirstOrDefault();
459 return result;
460 }
461 }
462
463 public List<CommonDataModel> GetBrowseDataStatistics(DateTime start_, DateTime end_, int siteId_ = 0)
464 {
465 using (var db = _database.GetReaderContext())
466 {
467 var start = new DateTime(start_.Year, start_.Month, start_.Day, 0, 0, 0);
468 var end = new DateTime(end_.Year, end_.Month, end_.Day, 23, 59, 59);
469 var dbResult = db.WebBrowserLogs.Where(m => m.LogTime >= start && m.LogTime <= end);
470 if (siteId_ > 0)
471 {
472 dbResult = dbResult.Where(m => m.SiteId == siteId_);
473 }
474
475 var data = dbResult.GroupBy(m => m.LogTime)
476 .Select(m => new
477 {
478 Value = m.Sum(s => s.Duration),
479 Time = m.FirstOrDefault().LogTime
480 }).ToList();
481
482 var result = new List<CommonDataModel>();
483
484 if (start_ == end_)
485 {
486
487 // 获取24小时数据
488 for (int i = 0; i < 24; i++)
489 {
490 var time = new DateTime(start_.Year, start_.Month, start_.Day, i, 0, 0);
491 var item = data.Where(m => m.Time == time).FirstOrDefault();
492 var dataItem = new CommonDataModel()
493 {
494 ID = i,
495 Name = i.ToString(),
496 Value = item != null ? item.Value : 0,
497 };
498 result.Add(dataItem);
499 }
500 }
501 else
502 {
503 // 获取日期范围数据
504 var days = (end_ - start_).TotalDays + 1;
505 if (days <= 31)
506 {
507 // 按天
508 for (int i = 0; i < days; i++)
509 {
510 var date = start_.AddDays(i);
511 var startTime = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0);
512 var endTime = new DateTime(date.Year, date.Month, date.Day, 23, 59, 59);
513
514 var value = data.Where(m => m.Time >= startTime && m.Time <= endTime).Sum(m => m.Value);
515 var dataItem = new CommonDataModel()
516 {
517 ID = i,
518 Name = i.ToString(),
519 Value = value,
520 };
521 result.Add(dataItem);
522 }
523 }
524 else
525 {
526 // 按月
527 for (int i = 0; i < 12; i++)
528 {
529 var date = new DateTime(start_.Year, i + 1, 1, 0, 0, 0);
530 var startTime = new DateTime(date.Year, date.Month, 1, 0, 0, 0);
531 var endTime = new DateTime(date.Year, date.Month, DateTime.DaysInMonth(date.Year, date.Month), 23, 59, 59);
532
533 var value = data.Where(m => m.Time >= startTime && m.Time <= endTime).Sum(m => m.Value);
534 var dataItem = new CommonDataModel()
535 {
536 ID = i,
537 Name = i.ToString(),
538 Value = value,
539 };
540 result.Add(dataItem);
541 }
542 }
543 }
544 return result;
545 }
546 }
547
548 private class CategoryStatisticModel
549 {
550 public int Duration { get; set; }
551 public DateTime LogTime { get; set; }
552 public int CategoryID { get; set; }
553 }
554 public List<ColumnDataModel> GetBrowseDataByCategoryStatistics(DateTime start_, DateTime end_)
555 {
556 using (var db = _database.GetReaderContext())
557 {
558
559
560 var start = new DateTime(start_.Year, start_.Month, start_.Day, 0, 0, 0);
561 var end = new DateTime(end_.Year, end_.Month, end_.Day, 23, 59, 59);
562
563 // 查询分类
564 var categorySql = "select sum(WebBrowseLogModels.Duration) as Duration,LogTime,WebSiteModels.CategoryID as CategoryID from WebBrowseLogModels LEFT JOIN WebSiteModels on WebBrowseLogModels.SiteId=WebSiteModels.ID LEFT JOIN WebSiteCategoryModels on WebSiteModels.CategoryID=WebSiteCategoryModels.ID WHERE LogTime>='" + start.ToString("yyyy-MM-dd 00:00:00") + "' and LogTime<= '" + end.ToString("yyyy-MM-dd 23:59:59") + "' GROUP BY WebSiteModels.CategoryID";
565
566 var categories = db.Database.SqlQuery<CategoryStatisticModel>(categorySql).ToList();
567
568 // 查询数据
569 var dataSql = "select sum(WebBrowseLogModels.Duration) as Duration,LogTime,WebSiteModels.CategoryID as CategoryID from WebBrowseLogModels LEFT JOIN WebSiteModels on WebBrowseLogModels.SiteId=WebSiteModels.ID LEFT JOIN WebSiteCategoryModels on WebSiteModels.CategoryID=WebSiteCategoryModels.ID WHERE LogTime>='" + start.ToString("yyyy-MM-dd 00:00:00") + "' and LogTime<= '" + end.ToString("yyyy-MM-dd 23:59:59") + "' GROUP BY WebBrowseLogModels.LogTime,WebSiteModels.CategoryID";
570
571 var data = db.Database.SqlQuery<CategoryStatisticModel>(dataSql).ToList();
572 var result = new List<ColumnDataModel>();
573
574 if (start_ == end_)
575 {
576 // 获取24小时数据
577 foreach (var category in categories)
578 {
579 result.Add(new ColumnDataModel()
580 {
581 CategoryID = category.CategoryID,
582 Values = new double[24]
583 });
584 }
585 for (int i = 0; i < 24; i++)
586 {
587 var time = new DateTime(start_.Year, start_.Month, start_.Day, i, 0, 0);
588 foreach (var category in categories)
589 {
590 var log = data.Where(m => m.CategoryID == category.CategoryID && m.LogTime == time).FirstOrDefault();
591 if (log != null)
592 {
593 var resultItem = result.Where(m => m.CategoryID == category.CategoryID).FirstOrDefault();
594 resultItem.Values[i] = log.Duration;
595 }
596 }
597 }
598 }
599 else
600 {
601 // 获取日期范围数据
602 int days = (int)(end_ - start_).TotalDays + 1;
603 if (days <= 31)
604 {
605 // 按天
606 foreach (var category in categories)
607 {
608 result.Add(new ColumnDataModel()
609 {
610 CategoryID = category.CategoryID,
611 Values = new double[days]
612 });
613 }
614
615 for (int i = 0; i < days; i++)
616 {
617 var date = start_.AddDays(i);
618 var startTime = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0);
619 var endTime = new DateTime(date.Year, date.Month, date.Day, 23, 59, 59);
620
621 foreach (var category in categories)
622 {
623 var duration = data.Where(m => m.CategoryID == category.CategoryID && m.LogTime >= startTime && m.LogTime <= endTime).Sum(m => m.Duration);
624
625 var resultItem = result.Where(m => m.CategoryID == category.CategoryID).FirstOrDefault();
626 resultItem.Values[i] = duration;
627 }
628 }
629 }
630 else
631 {
632 // 按月
633 foreach (var category in categories)
634 {
635 result.Add(new ColumnDataModel()
636 {
637 CategoryID = category.CategoryID,
638 Values = new double[12]
639 });
640 }
641 for (int i = 0; i < 12; i++)
642 {
643 var date = new DateTime(start_.Year, i + 1, 1, 0, 0, 0);
644 var startTime = new DateTime(date.Year, date.Month, 1, 0, 0, 0);
645 var endTime = new DateTime(date.Year, date.Month, DateTime.DaysInMonth(date.Year, date.Month), 23, 59, 59);
646
647 foreach (var category in categories)
648 {
649 var duration = data.Where(m => m.CategoryID == category.CategoryID && m.LogTime >= startTime && m.LogTime <= endTime).Sum(m => m.Duration);
650
651 var resultItem = result.Where(m => m.CategoryID == category.CategoryID).FirstOrDefault();
652 resultItem.Values[i] = duration;
653 }
654 }
655 }
656 }
657 return result;
658 }
659 }
660
661 public int GetBrowseDurationTotal(DateTime start_, DateTime end_)
662 {
663 start_ = new DateTime(start_.Year, start_.Month, start_.Day, 0, 0, 0);
664 end_ = new DateTime(end_.Year, end_.Month, end_.Day, 23, 59, 59);
665
666 using (var db = _database.GetReaderContext())
667 {
668 var data = db.WebBrowserLogs.Where(m => m.LogTime >= start_ && m.LogTime <= end_);
669 var result = data.Any() ? data.Sum(m => m.Duration) : 0;
670 return result;
671 }
672 }
673
674 public int GetBrowseSitesTotal(DateTime start_, DateTime end_)
675 {
676 start_ = new DateTime(start_.Year, start_.Month, start_.Day, 0, 0, 0);
677 end_ = new DateTime(end_.Year, end_.Month, end_.Day, 23, 59, 59);
678 using (var db = _database.GetReaderContext())
679 {
680 var result = db.WebBrowserLogs.Where(m => m.LogTime >= start_ && m.LogTime <= end_).GroupBy(m => m.SiteId).Count();
681 return result;
682 }
683 }
684
685 public int GetBrowsePagesTotal(DateTime start_, DateTime end_)
686 {
687 start_ = new DateTime(start_.Year, start_.Month, start_.Day, 0, 0, 0);
688 end_ = new DateTime(end_.Year, end_.Month, end_.Day, 23, 59, 59);
689 using (var db = _database.GetReaderContext())
690 {
691 var result = db.WebBrowserLogs.Where(m => m.LogTime >= start_ && m.LogTime <= end_).GroupBy(m => m.UrlId).Count();
692 return result;
693 }
694 }
695
696 public List<WebBrowseLogModel> GetBrowseLogList(DateTime start_, DateTime end_, int siteId_ = 0)
697 {
698 start_ = new DateTime(start_.Year, start_.Month, start_.Day, 0, 0, 0);
699 end_ = new DateTime(end_.Year, end_.Month, end_.Day, 23, 59, 59);
700 using (var db = _database.GetReaderContext())
701 {
702 var query = (from log in db.WebBrowserLogs
703 join url in db.WebUrls on log.UrlId equals url.ID
704 join site in db.WebSites on log.SiteId equals site.ID
705 where log.LogTime >= start_ && log.LogTime <= end_ && log.SiteId == siteId_
706 orderby log.LogTime descending
707 select new
708 {
709 Duration = log.Duration,
710 ID = log.ID,
711 LogTime = log.LogTime,
712 Site = site,
713 SiteId = log.SiteId,
714 Url = url,
715 UrlId = log.UrlId,
716 }).ToList().Select(s => new WebBrowseLogModel
717 {
718 Duration = s.Duration,
719 ID = s.ID,
720 LogTime = s.LogTime,
721 Site = s.Site,
722 SiteId = s.SiteId,
723 Url = s.Url,
724 UrlId = s.UrlId,
725 });
726
727 var result = query.ToList();
728 return result;
729 }
730 }
731
732 public WebSiteModel GetWebSite(int id_)
733 {
734 using (var db = _database.GetReaderContext())
735 {
736 var result = db.WebSites.Where(m => m.ID == id_).FirstOrDefault();
737 return result;
738 }
739 }
740
741 public WebSiteModel GetWebSite(string domain)
742 {
743 using (var db = _database.GetReaderContext())
744 {
745 var result = db.WebSites.Where(m => m.Domain.ToLower() == domain.ToLower()).FirstOrDefault();
746 return result;
747 }
748 }
749
750 public void Clear(DateTime start_, DateTime end_)
751 {
752 end_ = new DateTime(end_.Year, end_.Month, DateTime.DaysInMonth(end_.Year, end_.Month));
753
754 using (var db = _database.GetReaderContext())
755 {
756 //db.Database.ExecuteSqlCommand("update WebSiteModels set Duration=0");
757
758 db.Database.ExecuteSqlCommand("delete from WebBrowseLogModels where LogTime>='" + start_.Date.ToString("yyyy-MM-01 00:00:00") + "' and LogTime<= '" + end_.Date.ToString("yyyy-MM-dd 23:59:59") + "'");
759 }
760 }
761
762 public List<WebSiteModel> GetWebSiteLogList(DateTime start_, DateTime end_)
763 {
764 start_ = new DateTime(start_.Year, start_.Month, start_.Day, 0, 0, 0);
765 end_ = new DateTime(end_.Year, end_.Month, end_.Day, 23, 59, 59);
766 using (var db = _database.GetReaderContext())
767 {
768 var query = (from log in db.WebBrowserLogs
769 where log.LogTime >= start_ && log.LogTime <= end_
770 join site in db.WebSites on log.SiteId equals site.ID into newSite
771 join category in db.WebSiteCategories on log.Site.CategoryID equals category.ID into newCategory
772 from nc in newCategory.DefaultIfEmpty()
773 select new
774 {
775 ID = log.ID,
776 UrlId = log.UrlId,
777 Duration = log.Duration,
778 SiteId = log.SiteId,
779 Site = log.Site,
780 Category = nc
781 })
782 .ToList();
783 var result = query.GroupBy(m => m.SiteId).Select(s => new WebSiteModel
784 {
785 Duration = s.Sum(m => m.Duration),
786 ID = s.FirstOrDefault().Site.ID,
787 Title = s.FirstOrDefault().Site.Title,
788 Domain = s.FirstOrDefault().Site.Domain,
789 IconFile = s.FirstOrDefault().Site.IconFile,
790 CategoryID = s.FirstOrDefault().Site.CategoryID,
791 Category = s.FirstOrDefault().Category,
792 Alias = s.FirstOrDefault().Site.Alias,
793 }).ToList();
794
795 //var result = query.Select(s => new WebSiteModel
796 //{
797 // Duration = s.Duration,
798 // ID = s.Site.ID,
799 // Title = s.Site.Title,
800 // Domain = s.Site.Domain,
801 // IconFile = s.Site.IconFile,
802 // //Category = s.Category,
803 // CategoryID = s.Site.CategoryID,
804 //}).ToList();
805 //return result;
806 //return query;
807 return result;
808 }
809 }
810
811 public void Clear(int siteId_)
812 {
813 using (var db = _database.GetReaderContext())
814 {
815 db.Database.ExecuteSqlCommand("delete from WebBrowseLogModels where SiteId = " + siteId_);
816 db.Database.ExecuteSqlCommand("update WebSiteModels set Duration = 0 where ID = " + siteId_);
817 }
818 }
819
820 public void Export(string dir_, DateTime start_, DateTime end_)
821 {
822 start_ = new DateTime(start_.Year, start_.Month, 1, 0, 0, 0);
823 end_ = new DateTime(end_.Year, end_.Month, DateTime.DaysInMonth(end_.Year, end_.Month), 23, 59, 59);
824
825 using (var db = _database.GetReaderContext())
826 {
827 var webSiteData = db.WebBrowserLogs.Where(m => m.LogTime >= start_ && m.LogTime <= end_)
828 .ToList()
829 .Select(m => new
830 {
831 时间 = m.LogTime,
832 标题 = m.Url.Title,
833 网址 = m.Url.Url,
834 时长 = m.Duration,
835 });
836
837
838 var mapper = new Mapper();
839 mapper.Put(webSiteData, "浏览记录");
840
841 string name = $"Tai网页统计数据({start_.ToString("yyyy年MM月")}-{end_.ToString("yyyy年MM月")})";
842 if (start_.Year == end_.Year && start_.Month == end_.Month)
843 {
844 name = $"Tai网页统计数据({start_.ToString("yyyy年MM月")})";
845 }
846 mapper.Save(Path.Combine(dir_, $"{name}.xlsx"));
847
848 // 导出csv
849 using (var writer = new StreamWriter(Path.Combine(dir_, $"{name}.csv"), false, System.Text.Encoding.UTF8))
850 using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
851 {
852 csv.WriteRecords(webSiteData);
853 }
854 }
855 }
856
857 public WebSiteModel Update(WebSiteModel website_)
858 {
859 using (var db = _database.GetWriterContext())
860 {
861 var website = db.WebSites.FirstOrDefault(m => m.ID == website_.ID);
862 if (website != null)
863 {
864 website.Alias = website_.Alias;
865 website.Domain = website_.Domain;
866 website.Title = website_.Title;
867 db.SaveChanges();
868 _database.CloseWriter();
869 }
870 return website;
871 //string sql = $"update WebSiteModels set CategoryID={categoryId_} where ID in ({string.Join(",", siteIds_)})";
872 //db.Database.ExecuteSqlCommand(sql);
873 //db.SaveChanges();
874 }
875 }
876 }
877}