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