import express from 'express';
import { v4 as uuid, validate } from 'uuid';
import database from '../database';
import { isOfType } from '../util';
import { requireVerification, Token } from './auth';
import { generateFromFlatResult } from './task';
const project = express();
project.use(requireVerification);
project.get('/', async (req, res) => {
try {
const projects = await database('team_members')
.innerJoin('team_projects', 'team_members.team_id', 'team_projects.team_id')
.innerJoin('projects', 'team_projects.project_id', 'projects.id')
.select({
id: 'projects.id',
name: 'projects.name',
text: 'projects.text',
color: 'projects.color',
status: 'projects.status',
deadline: 'projects.deadline',
})
.where({
'team_members.user_id': req.body.token.id,
})
.groupBy('projects.id');
res.status(200).json({
status: 'success',
projects: projects,
});
} catch (e) {
res.status(400).json({
status: 'error',
message: 'failed to get project',
});
}
});
project.get('/:uuid', async (req, res) => {
try {
const id = req.params.uuid;
if (validate(id)) {
const projects = await database('team_members')
.innerJoin('team_projects', 'team_members.team_id', 'team_projects.team_id')
.innerJoin('projects', 'team_projects.project_id', 'projects.id')
.innerJoin({ 'tms': 'team_projects' }, 'projects.id', 'tms.project_id')
.select({
id: 'projects.id',
name: 'projects.name',
text: 'projects.text',
color: 'projects.color',
status: 'projects.status',
deadline: 'projects.deadline',
team_id: 'tms.team_id',
})
.where({
'team_members.user_id': req.body.token.id,
'projects.id': id,
})
.groupBy('tms.team_id');
if (projects.length >= 1) {
res.status(200).json({
status: 'success',
project: {
id: projects[0].id,
name: projects[0].name,
text: projects[0].text,
status: projects[0].status,
color: projects[0].color,
deadline: projects[0].deadline,
teams: projects.map(task => task.team_id),
}
});
} else {
res.status(404).json({
status: 'error',
message: 'project not found',
});
}
} else {
res.status(400).json({
status: 'error',
message: 'malformed uuid',
});
}
} catch (e) {
res.status(400).json({
status: 'error',
message: 'failed to get project',
});
}
});
project.get('/:uuid/tasks', async (req, res) => {
try {
const id = req.params.uuid;
if (validate(id)) {
const tasks = await database('team_members')
.innerJoin('team_projects', 'team_members.team_id', 'team_projects.team_id')
.innerJoin('tasks', 'team_projects.project_id', 'tasks.project_id')
.leftJoin('task_requirements', 'tasks.id', 'task_requirements.task_id')
.leftJoin('task_dependencies', 'tasks.id', 'task_dependencies.task_id')
.leftJoin('task_assignees', 'tasks.id', 'task_assignees.task_id')
.select({
id: 'tasks.id',
project: 'tasks.project_id',
name: 'tasks.name',
text: 'tasks.text',
icon: 'tasks.icon',
status: 'tasks.status',
priority: 'tasks.priority',
created: 'tasks.created',
edited: 'tasks.edited',
requirement_role: 'task_requirements.role_id',
requirement_time: 'task_requirements.time',
assigned_user: 'task_assignees.user_id',
assigned_time: 'task_assignees.time',
assigned_finished: 'task_assignees.finished',
dependentcy: 'task_dependencies.requires_id',
})
.where({
'team_members.user_id': req.body.token.id,
'team_projects.project_id': id,
});
res.status(200).json({
status: 'success',
tasks: generateFromFlatResult(tasks),
});
} else {
res.status(400).json({
status: 'error',
message: 'malformed uuid',
});
}
} catch (e) {
res.status(400).json({
status: 'error',
message: 'failed to get tasks',
});
}
});
project.get('/:uuid/assigned', async (req, res) => {
try {
const id = req.params.uuid;
if (validate(id)) {
const users = await database('team_members')
.innerJoin('team_projects', 'team_members.team_id', 'team_projects.team_id')
.innerJoin('tasks', 'team_projects.project_id', 'tasks.project_id')
.innerJoin('task_assignees', 'tasks.id', 'task_assignees.task_id')
.innerJoin('users', 'task_assignees.user_id', 'users.id')
.select({
id: 'users.id',
username: 'users.user_name',
email: 'users.email',
realname: 'users.real_name',
})
.sum({ time: 'task_assignees.time' })
.where({
'team_members.user_id': req.body.token.id,
'team_projects.project_id': id,
})
.groupBy('users.id');
res.status(200).json({
status: 'success',
assigned: users,
});
} else {
res.status(400).json({
status: 'error',
message: 'malformed uuid',
});
}
} catch (e) {
res.status(400).json({
status: 'error',
message: 'failed to get assignees',
});
}
});
project.get('/:uuid/work', async (req, res) => {
try {
const id = req.params.uuid;
if (validate(id)) {
const since = (req.query.since ?? 0) as number;
const work = await database({ ut: 'team_members' })
.innerJoin('team_projects', 'ut.team_id', 'team_projects.team_id')
.innerJoin('tasks', 'team_projects.project_id', 'tasks.project_id')
.innerJoin('workhours', 'tasks.id', 'workhours.task_id')
.select({
id: 'workhours.id',
task: 'workhours.task_id',
user: 'workhours.user_id',
started: 'workhours.started',
finished: 'workhours.finished',
})
.where({
'ut.user_id': req.body.token.id,
'team_projects.project_id': id,
})
.andWhere('workhours.started', '>=', since)
.groupBy('workhours.id');
res.status(200).json({
status: 'success',
work: work,
});
} else {
res.status(400).json({
status: 'error',
message: 'malformed uuid',
});
}
} catch (e) {
res.status(400).json({
status: 'error',
message: 'failed get work',
});
}
});
interface AddProjectBody {
teams: Array<string>;
name: string;
text: string;
color: string;
deadline?: string;
token: Token;
}
project.post('/', async (req, res) => {
if (isOfType<AddProjectBody>(req.body, [['teams', 'object'], ['name', 'string'], ['text', 'string'], ['color', 'string']])) {
try {
const team_ids = req.body.teams;
for (const team_id of team_ids) {
if (!validate(team_id)) {
res.status(400).json({
status: 'error',
message: 'malformed uuid',
});
return;
}
}
const color = req.body.color;
if (!color.match(/^#[0-9a-fA-F]{6}$/)) {
res.status(400).json({
status: 'error',
message: 'malformed color',
});
} else {
const project_id = uuid();
const team = await database('team_members')
.select({ id: 'team_members.team_id' })
.where({
'team_members.user_id': req.body.token.id,
})
.whereIn('team_members.team_id', team_ids);
if (team.length === team_ids.length) {
await database.transaction(async transaction => {
await transaction('projects').insert({
id: project_id,
name: req.body.name,
text: req.body.text,
color: req.body.color,
deadline: req.body.deadline ? new Date(req.body.deadline) : null,
status: 'open',
})
await transaction('team_projects').insert(
team_ids.map(team_id => ({
project_id: project_id,
team_id: team_id,
}))
);
});
res.status(200).json({
status: 'success',
id: project_id,
});
} else {
res.status(404).json({
status: 'error',
message: 'team not found',
});
}
}
} catch (e) {
res.status(400).json({
status: 'error',
message: 'failed to create project',
});
}
} else {
res.status(400).json({
status: 'error',
message: 'missing request fields',
});
}
});
interface UpdateProjectBody {
remove_teams?: Array<string>;
add_teams?: Array<string>;
name?: string;
text?: string;
color?: string;
status?: string;
deadline?: string;
token: Token;
}
project.put('/:uuid', async (req, res) => {
if (isOfType<UpdateProjectBody>(req.body, [])) {
try {
const id = req.params.uuid;
if (validate(id)) {
const add_team_ids = req.body.add_teams ?? [];
const remove_team_ids = req.body.remove_teams ?? [];
for (const team_id of add_team_ids.concat(remove_team_ids)) {
if (!validate(team_id)) {
res.status(400).json({
status: 'error',
message: 'malformed uuid',
});
return;
}
}
const projects = await database('team_members')
.innerJoin('team_projects', 'team_members.team_id', 'team_projects.team_id')
.innerJoin('projects', 'team_projects.project_id', 'projects.id')
.select({ id: 'projects.id' })
.where({
'team_members.user_id': req.body.token.id,
'projects.id': id,
});
if (projects.length >= 1) {
await database.transaction(async transaction => {
if (req.body.name || req.body.text || req.body.color || req.body.status || req.body.deadline) {
await transaction('projects')
.update({
name: req.body.name,
text: req.body.text,
color: req.body.color,
status: req.body.status,
deadline: req.body.deadline,
}).where({
id: id,
});
}
if (remove_team_ids.length !== 0) {
await transaction('team_projects')
.delete()
.where({
'project_id': id,
})
.whereIn('team_id', remove_team_ids);
}
if (add_team_ids.length !== 0) {
await transaction('team_projects').insert(
add_team_ids.map(team_id => ({
project_id: id,
team_id: team_id,
}))
);
}
});
res.status(200).json({
status: 'success',
});
} else {
res.status(400).json({
status: 'error',
message: 'project not found',
});
}
} else {
res.status(400).json({
status: 'error',
message: 'malformed uuid',
});
}
} catch (e) {
res.status(400).json({
status: 'error',
message: 'failed to create project',
});
}
} else {
res.status(400).json({
status: 'error',
message: 'missing request fields',
});
}
});
export default project;