1DROP VIEW task_view;
2DROP VIEW thread_view;
3
4UPDATE thread SET state='opened' WHERE state='done';
5
6CREATE VIEW task_view
7  AS
8    SELECT
9      task.*,
10      task_program.name AS task_program_name,
11      thread.coordinator AS coordinator,
12      CASE	WHEN task.state = 'done' THEN NULL
13      WHEN task.plan_date >= date('now', '+1 month') THEN '0'
14      WHEN task.plan_date >= date('now') THEN '1'
15      ELSE '2' END AS priority
16    FROM task
17      LEFT JOIN task_program ON task.task_program_id = task_program.id
18      LEFT JOIN thread ON task.thread_id = thread.id;
19
20CREATE VIEW thread_view
21  AS
22    SELECT thread.id, thread.original_poster, thread.coordinator, thread.closed_by,
23      thread.private, thread.lock, thread.type,
24      thread.create_date, thread.last_activity_date, thread.last_modification_date, thread.close_date,
25      thread.title, thread.content, thread.content_html,
26      thread.task_count, thread.task_count_closed, thread.task_sum_cost,
27      label.id AS label_id,
28      label.name AS label_name,
29      (SELECT MAX(priority) FROM task_view WHERE task_view.thread_id = thread.id) AS priority,
30      CASE WHEN thread.state = 'proposal' THEN 0
31      WHEN thread.state = 'opened' AND thread.task_count = 0 THEN 1
32      WHEN thread.state = 'opened' THEN 2
33      WHEN thread.state = 'closed' THEN 3
34      WHEN thread.state = 'rejected' THEN 4 END AS sort,
35      CASE WHEN thread.state = 'opened' AND thread.task_count > 0 AND thread.task_count = thread.task_count_closed THEN 'done'
36      ELSE thread.state END AS state
37    FROM thread
38      LEFT JOIN thread_label ON thread.id = thread_label.thread_id
39      LEFT JOIN label ON label.id = thread_label.label_id;
40