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

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);
}
}
}