You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
94 lines
4.2 KiB
94 lines
4.2 KiB
using System;
|
|
using System.Collections.Generic;
|
|
|
|
using PetaPoco;
|
|
using Pingchuan.BeijingSafeguard.Model;
|
|
|
|
namespace Pingchuan.BeijingSafeguard.DAL
|
|
{
|
|
public class TaskDAL : BaseDAL
|
|
{
|
|
public static void Add(Task task)
|
|
{
|
|
db.Insert(task);
|
|
}
|
|
|
|
public static List<TaskDTO> GetList(DateTime startTime, DateTime endTime)
|
|
{
|
|
string sql = $@"select t.*, o.name org_name, u.real_name user_name from tasks t
|
|
left join orgs o on o.id = t.org_id
|
|
left join users u on u.id = t.user_id
|
|
where t.release_time >= @0 and t.release_time < @1 order by t.release_time desc";
|
|
return db.Fetch<TaskDTO>(sql, startTime, endTime);
|
|
}
|
|
|
|
public static List<Task> GetList(int userId, string regionCode, DateTime startTime, DateTime endTime, List<string> tags)
|
|
{
|
|
string sql = GetSql(userId, regionCode, startTime, endTime, tags);
|
|
|
|
return db.Fetch<Task>(sql);
|
|
}
|
|
|
|
public static void Delete(string id)
|
|
{
|
|
db.Execute("DELETE FROM tasks WHERE id = @0", id);
|
|
}
|
|
|
|
public static List<Task> GetTaskIdByRegion(string region)
|
|
{
|
|
string sql = $@"SELECT g.task_id id FROM tags g, tasks t WHERE g.task_id = t.id AND t.region = '{region}'";
|
|
|
|
return db.Fetch<Task>(sql);
|
|
}
|
|
|
|
public static string GetSql(int userId, string regionCode, DateTime startTime, DateTime endTime, List<string> tags)
|
|
{
|
|
if (tags != null)
|
|
{
|
|
string tagsString = string.Join(",", tags.ToArray());
|
|
|
|
return $@"select c.*
|
|
from tasks c, tags t
|
|
where c.user_id = {userId} and c.region = '{regionCode}' and c.release_time >= '{startTime}' and c.release_time < '{endTime}' and c.id = t.task_id and t.`name` in({tagsString})
|
|
order by c.release_time desc";
|
|
}
|
|
|
|
return $@"select c.* from tasks c
|
|
where c.user_id = {userId} and c.region = '{regionCode}' and c.release_time >= '{startTime}' and c.release_time < '{endTime}'
|
|
order by c.release_time desc";
|
|
}
|
|
|
|
public static Pagination<Statistic> Statistics(string typeCode, DateTime fromTime, DateTime toTime, int pageIndex, int pageSize)
|
|
{
|
|
if (typeCode == "user")
|
|
return UserStatistics(fromTime, toTime, pageIndex, pageSize);
|
|
else if (typeCode == "org")
|
|
return OrgStatistics(fromTime, toTime, pageIndex, pageSize);
|
|
else
|
|
return null;
|
|
}
|
|
|
|
public static Pagination<Statistic> UserStatistics(DateTime fromTime, DateTime toTime, int pageIndex, int pageSize)
|
|
{
|
|
string sql = $@"select temp.*, u.real_name user_name, o.id org_id, o.name org_name, o.name org_name2 from (
|
|
select user_id, count(*) compute_count, max(create_time) last_compute_time from tasks
|
|
where create_time >= @0 and create_time < @1
|
|
group by user_id) temp
|
|
left join users u on u.id = temp.user_id
|
|
left join orgs o on o.id = u.org_id";
|
|
Page<Statistic> page = db.Page<Statistic>(pageIndex, pageSize, sql, fromTime, toTime, pageIndex, pageSize);
|
|
return Pagination<Statistic>.FromPage(page);
|
|
}
|
|
|
|
public static Pagination<Statistic> OrgStatistics(DateTime fromTime, DateTime toTime, int pageIndex, int pageSize)
|
|
{
|
|
string sql = $@"select temp.*, o.name org_name, o.name org_name2 from (
|
|
select org_id, count(*) compute_count, max(create_time) last_compute_time from tasks
|
|
where create_time >= @0 and create_time < @1
|
|
group by org_id) temp
|
|
left join orgs o on o.id = temp.org_id";
|
|
Page<Statistic> page = db.Page<Statistic>(pageIndex, pageSize, sql, fromTime, toTime, pageIndex, pageSize);
|
|
return Pagination<Statistic>.FromPage(page);
|
|
}
|
|
}
|
|
}
|