跳到主要内容
登录

github/tai

公开

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

浏览文件
main

分支

标签

Data.cs

810 行 · 34.5 KB历史下载
1using Core.Librarys.SQLite;
2using Core.Models;
3using Core.Servicers.Interfaces;
4using System;
5using System.Collections.Generic;
6using System.Diagnostics;
7using System.Linq;
8using System.Data.Entity;
9using System.Windows.Threading;
10using Core.Models.Data;
11using Core.Librarys;
12using Npoi.Mapper;
13using System.IO;
14using CsvHelper;
15using System.Globalization;
16using System.Threading.Tasks;
17using System.Windows;
18
19namespace Core.Servicers.Instances
20{
21 public class Data : IData
22 {
23 private readonly IAppData _appData;
24 private readonly IDatabase _database;
25 private readonly object setLock = new object();
26 public Data(IAppData appData_, IDatabase database_)
27 {
28 _appData = appData_;
29 _database = database_;
30 }
31
32 public void UpdateAppDuration(string process_, int duration_, DateTime startTime_)
33 {
34 // 过滤无效值
35 if (string.IsNullOrEmpty(process_) || duration_ <= 0 || startTime_ == DateTime.MinValue) return;
36
37 Logger.Info($"UpdateAppDuration,process:{process_},duration:{duration_},start:{startTime_}");
38 // 开始时间剩余最大统计时长
39 int startTimeMaxHoursDuration = (59 - startTime_.Minute) * 60 + (60 - startTime_.Second);
40 // 开始时间使用时长
41 int startTimeHoursDuration = duration_ > startTimeMaxHoursDuration ? startTimeMaxHoursDuration : duration_;
42 // 剩余时长
43 int outHoursDuration = duration_ - startTimeHoursDuration;
44 // 结束时间
45 DateTime endTime = new DateTime(startTime_.Year, startTime_.Month, startTime_.Day, startTime_.Hour, 0, 0);
46 // 时段使用数据
47 Dictionary<DateTime, int> durationHoursData = new Dictionary<DateTime, int>
48 {
49 { endTime, startTimeHoursDuration }
50 };
51 // 计算时段数据
52 if (outHoursDuration > 0)
53 {
54 int outHours = outHoursDuration / 3600;
55
56 DateTime outStartTime = new DateTime(startTime_.Year, startTime_.Month, startTime_.Day, startTime_.Hour, 0, 0);
57 for (int i = 0; i < outHours; i++)
58 {
59 outStartTime = outStartTime.AddHours(1);
60 int duration = 3600;
61 durationHoursData.Add(outStartTime, duration);
62 }
63 if (outHoursDuration % 3600 > 0)
64 {
65 outStartTime = outStartTime.AddHours(1);
66 int duration = outHoursDuration - outHours * 3600;
67 durationHoursData.Add(outStartTime, duration);
68 }
69 endTime = outStartTime;
70 }
71
72 // 计算每日统计数据
73 DateTime nextDayTime = new DateTime(startTime_.Year, startTime_.Month, startTime_.Day, 0, 0, 0).AddDays(1);
74 int startTimeMaxDayDuration = (int)(nextDayTime - startTime_).TotalSeconds;
75 // 开始时间使用时长
76 int startTimeDayDuration = duration_ > startTimeMaxDayDuration ? startTimeMaxDayDuration : duration_;
77 // 剩余时长
78 int outDayDuration = duration_ - startTimeDayDuration;
79 // 结束时间
80 DateTime endDayTime = startTime_.Date;
81 // 每日使用数据
82 Dictionary<DateTime, int> durationDayData = new Dictionary<DateTime, int>
83 {
84 { startTime_.Date, startTimeDayDuration }
85 };
86 if (outDayDuration > 0)
87 {
88 int outDays = outDayDuration / 86400;
89
90 DateTime outStartTime = new DateTime(startTime_.Year, startTime_.Month, startTime_.Day, 0, 0, 0);
91 for (int i = 0; i < outDays; i++)
92 {
93 outStartTime = outStartTime.AddDays(1);
94 int duration = 86400;
95 durationDayData.Add(outStartTime, duration);
96 }
97 if (outDayDuration % 86400 > 0)
98 {
99 outStartTime = outStartTime.AddDays(1);
100 int duration = outDayDuration - outDays * 86400;
101 durationDayData.Add(outStartTime, duration);
102 }
103 endDayTime = outStartTime.Date;
104 }
105
106 // 开始写入数据
107 try
108 {
109 using (var db = _database.GetWriterContext())
110 {
111 var app = db.App.Where(m => m.Name == process_).FirstOrDefault();
112 if (app == null)
113 {
114 _database.CloseWriter();
115 return;
116 }
117
118 // 更新app累计总时长
119 app.TotalTime += duration_;
120
121 // 更新每日数据
122 var dailyLogs = db.DailyLog.Where(m => m.Date >= startTime_.Date && m.Date <= endDayTime && m.AppModelID == app.ID).ToList();
123 foreach (var item in durationDayData)
124 {
125 var log = dailyLogs.Where(m => m.Date == item.Key).FirstOrDefault();
126 if (log == null)
127 {
128 //数据库中没有时则创建
129 db.DailyLog.Add(new DailyLogModel()
130 {
131 Date = item.Key,
132 AppModelID = app.ID,
133 Time = item.Value,
134 });
135 }
136 else
137 {
138 int time = log.Time + item.Value;
139 log.Time = time > 86400 ? 86400 : time;
140 }
141 }
142 // 更新时段数据
143 var startDataTime = new DateTime(startTime_.Year, startTime_.Month, startTime_.Day, startTime_.Hour, 0, 0);
144 var hoursLogs = db.HoursLog.Where(m => m.DataTime >= startDataTime && startDataTime <= endTime && m.AppModelID == app.ID).ToList();
145 foreach (var item in durationHoursData)
146 {
147 var log = hoursLogs.Where(m => startDataTime == item.Key).FirstOrDefault();
148 if (log == null)
149 {
150 // 没有记录时创建
151 db.HoursLog.Add(new HoursLogModel()
152 {
153 DataTime = item.Key,
154 AppModelID = app.ID,
155 Time = item.Value
156 });
157 }
158 else
159 {
160 int time = log.Time + item.Value;
161 log.Time = time > 3600 ? 3600 : time;
162 }
163 }
164 db.SaveChanges();
165 }
166 }
167 catch (Exception e)
168 {
169 Logger.Error($"UpdateAppDuration error!Process:{process_},Duration:{duration_},StartDateTime:{startTime_}.\r\nError:\r\n{e.Message}");
170 }
171 finally
172 {
173 _database.CloseWriter();
174 }
175 }
176
177 public List<DailyLogModel> GetTodaylogList()
178 {
179 using (var db = _database.GetReaderContext())
180 {
181 var today = DateTime.Now.Date;
182 var res = db.DailyLog.Where(m => m.Date == today && m.AppModelID != 0);
183 return res.ToList();
184 }
185 }
186
187 public IEnumerable<DailyLogModel> GetDateRangelogList(DateTime start, DateTime end, int take = -1, int skip = -1)
188 {
189
190 IEnumerable<AppModel> apps = _appData.GetAllApps();
191
192 using (var db = _database.GetReaderContext())
193 {
194 var data = db.DailyLog
195 .Where(m => m.Date >= start && m.Date <= end && m.AppModelID != 0)
196 .GroupBy(m => m.AppModelID)
197 .Select(m => new
198 {
199 Time = m.Sum(a => a.Time),
200 Date = m.FirstOrDefault().Date,
201 AppID = m.FirstOrDefault().AppModelID
202 });
203 if (skip > 0 && take > 0)
204 {
205 data = data.OrderByDescending(m => m.Time).Skip(skip).Take(take);
206 }
207 else if (skip > 0)
208 {
209 data = data.OrderByDescending(m => m.Time).Skip(skip);
210 }
211 else if (take > 0)
212 {
213 data = data.OrderByDescending(m => m.Time).Take(take);
214 }
215 else
216 {
217 data = data.OrderByDescending(m => m.Time);
218 }
219
220
221
222 //: db.DailyLog
223 //.Where(m => m.Date >= start && m.Date <= end && m.AppModelID != 0)
224 //.GroupBy(m => m.AppModelID)
225 //.Select(m => new
226 //{
227 // Time = m.Sum(a => a.Time),
228 // Date = m.FirstOrDefault().Date,
229 // AppID = m.FirstOrDefault().AppModelID
230 //})
231 //.OrderByDescending(m => m.Time)
232 //.Take(take);
233
234 var res = data
235 .ToList()
236 .Select(m => new DailyLogModel
237 {
238 Time = m.Time,
239 Date = m.Date,
240 AppModelID = m.AppID
241 }).ToList();
242
243 foreach (var log in res)
244 {
245 log.AppModel = _appData.GetApp(log.AppModelID);
246 }
247
248 return res;
249 }
250 }
251
252 public IEnumerable<DailyLogModel> GetThisWeeklogList()
253 {
254 DateTime weekStartDate = DateTime.Now, weekEndDate = DateTime.Now;
255 if (DateTime.Now.DayOfWeek == DayOfWeek.Monday)
256 {
257 weekStartDate = DateTime.Now.Date;
258 weekEndDate = DateTime.Now.Date.AddDays(6);
259 }
260 else
261 {
262 int weekNum = (int)DateTime.Now.DayOfWeek;
263 if (weekNum == 0)
264 {
265 weekNum = 7;
266 }
267 weekNum -= 1;
268 weekStartDate = DateTime.Now.Date.AddDays(-weekNum);
269 weekEndDate = weekStartDate.Date.AddDays(6);
270 }
271
272 return GetDateRangelogList(weekStartDate, weekEndDate);
273 }
274
275 public IEnumerable<DailyLogModel> GetLastWeeklogList()
276 {
277 DateTime weekStartDate = DateTime.Now, weekEndDate = DateTime.Now;
278
279 int weekNum = (int)DateTime.Now.DayOfWeek;
280 if (weekNum == 0)
281 {
282 weekNum = 7;
283 }
284
285
286 weekStartDate = DateTime.Now.Date.AddDays(-6 - weekNum);
287 weekEndDate = weekStartDate.AddDays(6);
288
289 return GetDateRangelogList(weekStartDate, weekEndDate);
290 }
291
292 public List<DailyLogModel> GetProcessMonthLogList(int appID, DateTime month)
293 {
294 //var app = appData.GetApp(processName);
295 //if (app == null)
296 //{
297 // return new List<DailyLogModel>();
298 //}
299 using (var db = _database.GetReaderContext())
300 {
301
302 var res = db.DailyLog.Include(m => m.AppModel).Where(
303 m =>
304 m.Date.Year == month.Year
305 && m.Date.Month == month.Month
306 && m.AppModelID == appID
307 );
308 if (res != null)
309 {
310 return res.ToList();
311 }
312 return new List<DailyLogModel>();
313 }
314 }
315
316
317 public void Clear(int appID, DateTime month)
318 {
319
320 using (var db = _database.GetReaderContext())
321 {
322
323 db.DailyLog.RemoveRange(
324 db.DailyLog.Where(m =>
325 m.AppModelID == appID
326 && m.Date.Year == month.Year
327 && m.Date.Month == month.Month));
328
329 db.HoursLog.RemoveRange(
330 db.HoursLog.Where(m => m.AppModelID == appID
331 && m.DataTime.Year == month.Year
332 && m.DataTime.Month == month.Month));
333
334 db.SaveChanges();
335 }
336 }
337
338 public DailyLogModel GetProcess(int appID, DateTime day)
339 {
340
341
342 using (var db = _database.GetReaderContext())
343 {
344
345
346 var res = db.DailyLog.Where(m =>
347 m.AppModelID == appID
348 && m.Date.Year == day.Year
349 && m.Date.Month == day.Month
350 && m.Date.Day == day.Day);
351 if (res != null)
352 {
353 return res.FirstOrDefault();
354 }
355 return null;
356 }
357 }
358
359 public struct CategoryHoursDataModel
360 {
361 public int Total { get; set; }
362 public int CategoryID { get; set; }
363 public DateTime Time { get; set; }
364
365 }
366
367 public List<ColumnDataModel> GetCategoryHoursData(DateTime date)
368 {
369 using (var db = _database.GetReaderContext())
370 {
371 // 查出有数据的分类
372
373 //var categorys = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,HoursLogModels.DataTime as Time from HoursLogModels join AppModels on AppModels.ID=HoursLogModels.AppModelID where AppModels.CategoryID<>0 and HoursLogModels.DataTime>='" + date.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and HoursLogModels.DataTime<= '" + date.Date.ToString("yyyy-MM-dd 23:59:59") + "' GROUP BY AppModels.CategoryID ").ToArray();
374
375
376 //var data = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,HoursLogModels.DataTime as Time from HoursLogModels join AppModels on AppModels.ID=HoursLogModels.AppModelID where AppModels.CategoryID<>0 and HoursLogModels.DataTime>='" + date.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and HoursLogModels.DataTime<= '" + date.Date.ToString("yyyy-MM-dd 23:59:59") + "' GROUP BY AppModels.CategoryID,HoursLogModels.DataTime ").ToArray();
377 var categorys = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,HoursLogModels.DataTime as Time from HoursLogModels join AppModels on AppModels.ID=HoursLogModels.AppModelID where HoursLogModels.DataTime>='" + date.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and HoursLogModels.DataTime<= '" + date.Date.ToString("yyyy-MM-dd 23:59:59") + "' GROUP BY AppModels.CategoryID ").OrderByDescending(m => m.CategoryID).ToArray();
378
379
380 var data = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,HoursLogModels.DataTime as Time from HoursLogModels join AppModels on AppModels.ID=HoursLogModels.AppModelID where HoursLogModels.DataTime>='" + date.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and HoursLogModels.DataTime<= '" + date.Date.ToString("yyyy-MM-dd 23:59:59") + "' GROUP BY AppModels.CategoryID,HoursLogModels.DataTime ").ToArray();
381
382 List<ColumnDataModel> list = new List<ColumnDataModel>();
383 foreach (var category in categorys)
384 {
385 list.Add(new ColumnDataModel()
386 {
387 CategoryID = category.CategoryID,
388 Values = new double[24]
389 });
390 }
391
392 for (int i = 0; i < 24; i++)
393 {
394 string hours = i < 10 ? "0" + i : i.ToString();
395 var time = date.ToString($"yyyy-MM-dd {hours}:00:00");
396 foreach (var category in categorys)
397 {
398 var log = data.Where(m => m.CategoryID == category.CategoryID && m.Time.ToString("yyyy-MM-dd HH:00:00") == time).FirstOrDefault();
399
400 var item = list.Where(m => m.CategoryID == category.CategoryID).FirstOrDefault();
401
402 item.Values[i] = log.Total;
403 }
404 }
405 return list;
406 }
407 }
408
409 public List<ColumnDataModel> GetCategoryRangeData(DateTime start, DateTime end)
410 {
411 using (var db = _database.GetReaderContext())
412 {
413 // 查出有数据的分类
414
415 //var categorys = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,DailyLogModels.Date as Time from DailyLogModels join AppModels on AppModels.ID=DailyLogModels.AppModelID where AppModels.CategoryID<>0 and DailyLogModels.Date>='" + start.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and DailyLogModels.Date<= '" + end.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' GROUP BY AppModels.CategoryID").ToArray();
416
417
418 //var data = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,DailyLogModels.Date as Time from DailyLogModels join AppModels on AppModels.ID=DailyLogModels.AppModelID where AppModels.CategoryID<>0 and DailyLogModels.Date>='" + start.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and DailyLogModels.Date<= '" + end.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' GROUP BY AppModels.CategoryID,DailyLogModels.Date ").ToArray();
419 var categorys = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,DailyLogModels.Date as Time from DailyLogModels join AppModels on AppModels.ID=DailyLogModels.AppModelID where DailyLogModels.Date>='" + start.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and DailyLogModels.Date<= '" + end.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' GROUP BY AppModels.CategoryID").OrderByDescending(m => m.CategoryID).ToArray();
420
421
422 var data = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,DailyLogModels.Date as Time from DailyLogModels join AppModels on AppModels.ID=DailyLogModels.AppModelID where DailyLogModels.Date>='" + start.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and DailyLogModels.Date<= '" + end.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' GROUP BY AppModels.CategoryID,DailyLogModels.Date ").ToArray();
423
424 var ts = end - start;
425 var days = ts.TotalDays + 1;
426
427
428 List<ColumnDataModel> list = new List<ColumnDataModel>();
429
430
431 foreach (var category in categorys)
432 {
433 list.Add(new ColumnDataModel()
434 {
435 CategoryID = category.CategoryID,
436 Values = new double[(int)days]
437 });
438 }
439
440
441
442 for (int i = 0; i < days; i++)
443 {
444 string day = i < 10 ? "0" + i : i.ToString();
445 var time = start.AddDays(i).ToString($"yyyy-MM-dd 00:00:00");
446 Debug.WriteLine(time);
447 foreach (var category in categorys)
448 {
449 var log = data.Where(m => m.CategoryID == category.CategoryID && m.Time.ToString("yyyy-MM-dd 00:00:00") == time).FirstOrDefault();
450
451 var item = list.Where(m => m.CategoryID == category.CategoryID).FirstOrDefault();
452
453 item.Values[i] = log.Total;
454 }
455 }
456 return list;
457 }
458 }
459
460 public List<ColumnDataModel> GetCategoryYearData(DateTime date)
461 {
462 using (var db = _database.GetReaderContext())
463 {
464 // 查出有数据的分类
465
466 var dateArr = Time.GetYearDate(date);
467
468 //var categorys = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,DailyLogModels.Date as Time from DailyLogModels join AppModels on AppModels.ID=DailyLogModels.AppModelID where AppModels.CategoryID<>0 and DailyLogModels.Date>='" + dateArr[0].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and DailyLogModels.Date<= '" + dateArr[1].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' GROUP BY AppModels.CategoryID").ToArray();
469
470
471 //var data = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,DailyLogModels.Date as Time from DailyLogModels join AppModels on AppModels.ID=DailyLogModels.AppModelID where AppModels.CategoryID<>0 and DailyLogModels.Date>='" + dateArr[0].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and DailyLogModels.Date<= '" + dateArr[1].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' GROUP BY AppModels.CategoryID,DailyLogModels.Date ").ToArray();
472
473 var categorys = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,DailyLogModels.Date as Time from DailyLogModels join AppModels on AppModels.ID=DailyLogModels.AppModelID where DailyLogModels.Date>='" + dateArr[0].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and DailyLogModels.Date<= '" + dateArr[1].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' GROUP BY AppModels.CategoryID").OrderByDescending(m => m.CategoryID).ToArray();
474
475
476 var data = db.Database.SqlQuery<CategoryHoursDataModel>("select sum(Time) as Total,AppModels.CategoryID,DailyLogModels.Date as Time from DailyLogModels join AppModels on AppModels.ID=DailyLogModels.AppModelID where DailyLogModels.Date>='" + dateArr[0].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and DailyLogModels.Date<= '" + dateArr[1].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' GROUP BY AppModels.CategoryID,DailyLogModels.Date ").ToArray();
477 //var ts = end - start;
478 //var days = ts.TotalDays + 1;
479
480
481 List<ColumnDataModel> list = new List<ColumnDataModel>();
482
483
484 foreach (var category in categorys)
485 {
486 list.Add(new ColumnDataModel()
487 {
488 CategoryID = category.CategoryID,
489 Values = new double[12]
490 });
491 }
492
493
494
495 for (int i = 1; i < 13; i++)
496 {
497 string month = i < 10 ? "0" + i : i.ToString();
498 var dayArr = Time.GetMonthDate(new DateTime(date.Year, i, 1));
499
500 Debug.WriteLine(dayArr);
501 foreach (var category in categorys)
502 {
503 var total = data.Where(m => m.CategoryID == category.CategoryID && m.Time >= dayArr[0] && m.Time <= dayArr[1]).Sum(m => m.Total);
504
505 var item = list.Where(m => m.CategoryID == category.CategoryID).FirstOrDefault();
506
507 item.Values[i - 1] = total;
508 }
509 }
510 return list;
511 }
512 }
513
514 public struct ColumnItemDataModel
515 {
516 public int Total { get; set; }
517 public int AppID { get; set; }
518 public DateTime Time { get; set; }
519
520 }
521
522 public List<ColumnDataModel> GetAppDayData(int appID, DateTime date)
523 {
524 using (var db = _database.GetReaderContext())
525 {
526
527 var data = db.Database.SqlQuery<ColumnItemDataModel>("select sum(Time) as Total,AppModelID as AppID,DataTime as Time from HoursLogModels where AppModelID=" + appID + " and DataTime>='" + date.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and DataTime<= '" + date.Date.ToString("yyyy-MM-dd 23:59:59") + "' GROUP BY AppModelID,DataTime ").ToArray();
528
529
530 List<ColumnDataModel> list = new List<ColumnDataModel>();
531
532
533 list.Add(new ColumnDataModel()
534 {
535 AppId = appID,
536 Values = new double[24]
537 });
538
539 var item = list[0];
540
541 for (int i = 0; i < 24; i++)
542 {
543 string hours = i < 10 ? "0" + i : i.ToString();
544 var time = date.ToString($"yyyy-MM-dd {hours}:00:00");
545
546 var log = data.Where(m => m.Time.ToString("yyyy-MM-dd HH:00:00") == time).FirstOrDefault();
547
548
549
550 item.Values[i] = log.Total;
551 }
552 return list;
553 }
554 }
555
556 public List<ColumnDataModel> GetAppRangeData(int appID, DateTime start, DateTime end)
557 {
558 using (var db = _database.GetReaderContext())
559 {
560 // 查出有数据的分类
561
562 var data = db.Database.SqlQuery<ColumnItemDataModel>("select sum(Time) as Total,AppModelID as AppID,Date as Time from DailyLogModels where AppModelID=" + appID + " and Date>='" + start.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and Date<= '" + end.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' GROUP BY Date ").ToArray();
563
564
565 var ts = end - start;
566 var days = ts.TotalDays + 1;
567
568
569 List<ColumnDataModel> list = new List<ColumnDataModel>();
570
571
572
573 list.Add(new ColumnDataModel()
574 {
575 AppId = appID,
576 Values = new double[(int)days]
577 });
578
579
580 var item = list[0];
581
582 for (int i = 0; i < days; i++)
583 {
584 string day = i < 10 ? "0" + i : i.ToString();
585 var time = start.AddDays(i).ToString($"yyyy-MM-dd 00:00:00");
586
587 var log = data.Where(m => m.Time.ToString("yyyy-MM-dd 00:00:00") == time).FirstOrDefault();
588
589 item.Values[i] = log.Total;
590 }
591 return list;
592 }
593 }
594
595 public List<ColumnDataModel> GetAppYearData(int appID, DateTime date)
596 {
597 using (var db = _database.GetReaderContext())
598 {
599 // 查出有数据的分类
600
601 var dateArr = Time.GetYearDate(date);
602
603
604 var data = db.Database.SqlQuery<ColumnItemDataModel>("select sum(Time) as Total,AppModelID as AppID,Date as Time from DailyLogModels where AppModelID=" + appID + " and Date>='" + dateArr[0].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and Date<= '" + dateArr[1].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' GROUP BY Date").ToArray();
605
606
607
608
609 List<ColumnDataModel> list = new List<ColumnDataModel>();
610
611
612 list.Add(new ColumnDataModel()
613 {
614 AppId = appID,
615 Values = new double[12]
616 });
617
618
619 var item = list[0];
620
621 for (int i = 1; i < 13; i++)
622 {
623 string month = i < 10 ? "0" + i : i.ToString();
624 var dayArr = Time.GetMonthDate(new DateTime(date.Year, i, 1));
625
626 Debug.WriteLine(dayArr);
627
628 var total = data.Where(m => m.Time >= dayArr[0] && m.Time <= dayArr[1]).Sum(m => m.Total);
629
630
631 item.Values[i - 1] = total;
632 }
633 return list;
634 }
635 }
636
637 public void ClearRange(DateTime start, DateTime end)
638 {
639 end = new DateTime(end.Year, end.Month, DateTime.DaysInMonth(end.Year, end.Month));
640
641 using (var db = _database.GetReaderContext())
642 {
643 db.Database.ExecuteSqlCommand("delete from DailyLogModels where Date>='" + start.Date.ToString("yyyy-MM-01 00:00:00") + "' and Date<= '" + end.Date.ToString("yyyy-MM-dd 23:59:59") + "'");
644
645 db.Database.ExecuteSqlCommand("delete from HoursLogModels where DataTime>='" + start.Date.ToString("yyyy-MM-01 00:00:00") + "' and DataTime<= '" + end.Date.ToString("yyyy-MM-dd 23:59:59") + "'");
646 }
647 }
648
649 public void ExportToExcel(string dir, DateTime start, DateTime end)
650 {
651 start = new DateTime(start.Year, start.Month, 1, 0, 0, 0);
652 end = new DateTime(end.Year, end.Month, DateTime.DaysInMonth(end.Year, end.Month), 23, 59, 59);
653
654 using (var db = _database.GetReaderContext())
655 {
656 var day = db.DailyLog.Where(m => m.Date >= start.Date && m.Date <= end.Date)
657 .ToList()
658 .Select(m => new
659 {
660 日期 = m.Date,
661 应用 = m.AppModel != null ? m.AppModel.Name : "未知",
662 描述 = m.AppModel != null ? m.AppModel.Description : "未知",
663 时长 = m.Time,
664 分类 = m.AppModel != null && m.AppModel.Category != null ? m.AppModel.Category.Name : "未知"
665 });
666
667 var hours = db.HoursLog.Where(m => m.DataTime >= start && m.DataTime <= end)
668 .ToList()
669 .Select(m => new
670 {
671 时段 = m.DataTime,
672 应用 = m.AppModel != null ? m.AppModel.Name : "未知",
673 描述 = m.AppModel != null ? m.AppModel.Description : "未知",
674 时长 = m.Time,
675 分类 = m.AppModel != null && m.AppModel.Category != null ? m.AppModel.Category.Name : "未知"
676 });
677 var mapper = new Mapper();
678 mapper.Put(day, "每日");
679 mapper.Put(hours, "时段");
680
681 string name = $"Tai数据({start.ToString("yyyy年MM月")}-{end.ToString("yyyy年MM月")})";
682 if (start.Year == end.Year && start.Month == end.Month)
683 {
684 name = $"Tai数据({start.ToString("yyyy年MM月")})";
685 }
686 mapper.Save(Path.Combine(dir, $"{name}.xlsx"));
687
688 // 导出csv
689 using (var writer = new StreamWriter(Path.Combine(dir, $"{name}-每日.csv"), false, System.Text.Encoding.UTF8))
690 using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
691 {
692 csv.WriteRecords(day);
693 }
694
695 using (var writer = new StreamWriter(Path.Combine(dir, $"{name}-时段.csv"), false, System.Text.Encoding.UTF8))
696 using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
697 {
698 csv.WriteRecords(hours);
699 }
700 }
701 }
702
703 public int GetDateRangeAppCount(DateTime start, DateTime end)
704 {
705 IEnumerable<AppModel> apps = _appData.GetAllApps();
706
707 using (var db = _database.GetReaderContext())
708 {
709 var res = db.DailyLog
710 .Where(m => m.Date >= start && m.Date <= end && m.AppModelID != 0)
711 .GroupBy(m => m.AppModelID)
712 .Count();
713
714 return res;
715 }
716 }
717
718 public IEnumerable<HoursLogModel> GetTimeRangelogList(DateTime time)
719 {
720 time = new DateTime(time.Year, time.Month, time.Day, time.Hour, 0, 0);
721 using (var db = _database.GetReaderContext())
722 {
723 var res = db.HoursLog.Where(m => m.DataTime == time).ToList();
724
725 foreach (var log in res)
726 {
727 log.AppModel = _appData.GetApp(log.AppModelID);
728 }
729
730 return res;
731 }
732 }
733
734 public struct TimeDataModel
735 {
736 public int Total { get; set; }
737 public DateTime Time { get; set; }
738 }
739 public double[] GetRangeTotalData(DateTime start, DateTime end)
740 {
741 using (var db = _database.GetReaderContext())
742 {
743 if (start.Date == end.Date)
744 {
745 // 获取24小时
746 var data = db.Database.SqlQuery<TimeDataModel>("select sum(Time) as Total,DataTime as Time from HoursLogModels where HoursLogModels.DataTime>='" + start.Date.ToString("yyyy-MM-dd 00:00:00") + "' and HoursLogModels.DataTime<= '" + start.Date.ToString("yyyy-MM-dd 23:59:59") + "' GROUP BY DataTime ").ToArray();
747
748 double[] result = new double[24];
749 for (int i = 0; i < 24; i++)
750 {
751 string hours = i < 10 ? "0" + i : i.ToString();
752 var time = start.ToString($"yyyy-MM-dd {hours}:00:00");
753 var log = data.Where(m => m.Time.ToString("yyyy-MM-dd HH:00:00") == time).FirstOrDefault();
754 result[i] = log.Total;
755 }
756 return result;
757 }
758 else
759 {
760 // 获取日期
761 var ts = end.Date - start.Date;
762 int days = (int)ts.TotalDays + 1;
763
764
765 var data = db.Database.SqlQuery<TimeDataModel>("select sum(Time) as Total,Date as Time from DailyLogModels where DailyLogModels.Date>='" + start.Date.ToString("yyyy-MM-dd 00:00:00") + "' and DailyLogModels.Date<= '" + end.Date.ToString("yyyy-MM-dd 23:59:59") + "' GROUP BY Date ").ToArray();
766
767 double[] result = new double[days];
768 for (int i = 0; i < days; i++)
769 {
770 var time = start.Date.AddDays(i).ToString($"yyyy-MM-dd 00:00:00");
771 var log = data.Where(m => m.Time.ToString("yyyy-MM-dd 00:00:00") == time).FirstOrDefault();
772 result[i] = log.Total;
773 }
774
775 return result;
776 }
777 }
778 }
779
780 public double[] GetMonthTotalData(DateTime date)
781 {
782 using (var db = _database.GetReaderContext())
783 {
784 var dateArr = Time.GetYearDate(date);
785 var data = db.Database.SqlQuery<TimeDataModel>("select sum(Time) as Total,Date as Time from DailyLogModels where Date>='" + dateArr[0].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' and Date<= '" + dateArr[1].Date.ToString("yyyy-MM-dd HH:mm:ss") + "' GROUP BY Date").ToArray();
786 double[] result = new double[12];
787
788 for (int i = 1; i < 13; i++)
789 {
790 string month = i < 10 ? "0" + i : i.ToString();
791 var dayArr = Time.GetMonthDate(new DateTime(date.Year, i, 1));
792 var total = data.Where(m => m.Time >= dayArr[0] && m.Time <= dayArr[1]).Sum(m => m.Total);
793
794 result[i - 1] = total;
795 }
796 return result;
797 }
798 }
799
800 public void Clear(int appID_)
801 {
802 using (var db = _database.GetReaderContext())
803 {
804 db.Database.ExecuteSqlCommand("delete from DailyLogModels where AppModelID = " + appID_);
805 db.Database.ExecuteSqlCommand("delete from HoursLogModels where AppModelID = " + appID_);
806 db.Database.ExecuteSqlCommand("update AppModels set TotalTime = 0 where ID = " + appID_);
807 }
808 }
809 }
810}