#Reset weekly capacity
#=====================
CREATE PROCEDURE UpdateCapacity()
BEGIN
	DECLARE days INT DEFAULT 1;
	WHILE days <= 5 DO
		INSERT INTO
		capacity (id_user, capacity_date)
		select employeeNumber, DATE_ADD(CURDATE(), INTERVAL days DAY) from users where id_org_level in (6,8,10,11,12,13,14) AND status NOT IN ('Exited', 'Extended leave');
		SET days = days + 1;
	END WHILE;
	
	INSERT INTO
	capacity (id_user, capacity, capacity_date)
	select employeeNumber, 0, DATE_ADD(CURDATE(), INTERVAL 6 DAY) from users where id_org_level in (6,8,10,11,12,13,14) AND status NOT IN ('Exited', 'Extended leave');
	
	INSERT INTO
	capacity (id_user, capacity, capacity_date)
	select employeeNumber, 0, DATE_ADD(CURDATE(), INTERVAL 7 DAY) from users where id_org_level in (6,8,10,11,12,13,14) AND status NOT IN ('Exited', 'Extended leave');
	
	INSERT INTO
	capacity_next_week (id_user, week_no, year)
	SELECT 
	employeeNumber, 
	DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 7 DAY), '%u'),
	DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 7 DAY), '%Y') 
	FROM users where id_org_level in (6,8,10,11,12,13,14) AND status NOT IN ('Exited', 'Extended leave');
END;

#=====
#VIEWS
#=====

# ================================
# Associate current week capacity
# ================================
CREATE VIEW associate_current_week_capacity AS
SELECT
u.employeeNumber, u.name, d.id id_department, d.department, o.level, CAST(DATE_FORMAT(c.capacity_date, '%u') AS UNSIGNED) week_no, u.status,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Mon' then c.capacity END) as Mon,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Tue' then c.capacity END) as Tue,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Wed' then c.capacity END) as Wed,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Thu' then c.capacity END) as Thu,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Fri' then c.capacity END) as Fri,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Sat' then c.capacity END) as Sat,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Sun' then c.capacity END) as Sun
FROM 
users as u, capacity as c, departments as d, org_levels as o
WHERE
u.employeeNumber = c.id_user AND
u.id_department = d.id AND
u.status NOT IN ('Exited', 'Extended leave') AND
u.id_org_level = o.id AND
u.id_org_level != 3 AND
DATE_FORMAT(c.capacity_date, '%u') = DATE_FORMAT(CURDATE(), '%u') AND
YEAR(c.capacity_date) = YEAR(CURDATE())
GROUP BY u.name, u.employeeNumber, week_no, id_department, level, status ORDER BY u.name;

# =========================================
# Associates report - Matter/Associate view
# =========================================
CREATE VIEW report_associates AS
SELECT a.id_matter, GROUP_CONCAT(u.name ORDER BY u.name ASC SEPARATOR ', ') associates, CAST(DATE_FORMAT(a.date_assigned, '%u') AS UNSIGNED) week_no, YEAR(a.date_assigned) year
FROM
users u, assignments a where u.employeeNumber = a.assigned_to
GROUP BY a.id_matter, week_no, year
ORDER BY year DESC, week_no DESC, a.id_matter DESC;

# =====================================
# Partners report - Matter/Partner view
# =====================================
CREATE VIEW report_partners AS
SELECT mp.id_matter, m.details, m.requirement, GROUP_CONCAT(u.name ORDER BY u.name ASC SEPARATOR ', ') partners, m.added_by
FROM users u, matter_partners mp, matters m
WHERE
u.employeeNumber = mp.id_partner AND
m.id = mp.id_matter
GROUP BY mp.id_matter, m.details, m.requirement
ORDER BY mp.id_matter DESC;

# =================================================================================
# Assignments - Combined Matter/Partner & Matter/Associate view to show assignments
# =================================================================================
CREATE VIEW report_assignments AS
SELECT
rp.*, ra.associates, ra.week_no, ra.year
FROM report_partners rp, report_associates ra
WHERE
rp.id_matter = ra.id_matter;

# ===========================================================
# Associate 3-Week Hours - Billable hours of the last 3 weeks
# ===========================================================
CREATE VIEW associate_three_week_hours AS
SELECT
id_user, week_no, billable_hours, total_hours
from associate_hours
WHERE
year = YEAR(CURDATE()) AND
week_no 
BETWEEN 
IF(DATE_FORMAT(CURDATE(), '%u') - 3 < 0, 1, DATE_FORMAT(CURDATE(), '%u') - 3)
AND
DATE_FORMAT(CURDATE(), '%u') - 1
ORDER BY id_user ASC, week_no DESC;
# ====


# ==========================
# Associate 3-Week averages
# ==========================
CREATE VIEW associate_three_week_avg AS
SELECT
id_user, AVG(billable_hours) billable_average, AVG(total_hours) total_average
FROM associate_three_week_hours
GROUP BY id_user;

# =================================
# Associate weekly average capacity
# =================================
CREATE VIEW associate_avg_total_weekly_capacity AS
SELECT
c.id_user, u.id_department, AVG(c.capacity) avg_capacity, SUM(c.capacity) total_capacity, CAST(DATE_FORMAT(c.capacity_date, '%u') AS UNSIGNED) week_no
FROM
capacity c, users u
WHERE 
c.id_user = u.employeeNumber AND
DATE_FORMAT(c.capacity_date, '%a') NOT IN ('Sat', 'Sun') AND
YEAR(c.capacity_date) = YEAR(CURDATE())
GROUP BY c.id_user, week_no, id_department;

# ==============================================================================================
# Department 3-Week Hours - Average number of billable hours per department for the last 3 weeks
# ==============================================================================================
CREATE VIEW dept_three_week_hours AS
SELECT
d.id, a.week_no, AVG(a.billable_hours) billable_hours, AVG(a.total_hours) total_hours
FROM
departments d, associate_hours a, users u
WHERE
a.week_no 
BETWEEN 
IF(DATE_FORMAT(CURDATE(), '%u') - 3 < 0, 1, DATE_FORMAT(CURDATE(), '%u') - 3)
AND 
DATE_FORMAT(CURDATE(), '%u') - 1
AND
a.year = YEAR(CURDATE()) AND
a.id_user = u.employeeNumber AND
u.id_department = d.id AND
u.status = 'Available'
GROUP BY d.id, a.week_no
ORDER BY d.id ASC, a.week_no DESC;
# ====


# =============================================
# Department weekly total and billable averages
# =============================================
CREATE VIEW dept_weekly_averages AS
SELECT
d.id, a.week_no, AVG(a.billable_hours) avg_billable_hours, AVG(a.total_hours) avg_total_hours, AVG(av.total_capacity) avg_capacity
FROM
departments d, associate_hours a, users u, associate_avg_total_weekly_capacity av
WHERE
a.year = YEAR(CURDATE()) AND
a.id_user = u.employeeNumber AND
av.id_department = d.id AND
av.week_no = a.week_no AND
u.id_department = d.id AND
u.status = 'Available'
GROUP BY d.id, a.week_no
ORDER BY d.id, a.week_no;

# ====================================================================================
# Department 3-Week Average - Average of total and billable hours for the last 3 weeks
# ====================================================================================
CREATE VIEW dept_three_week_avg AS
SELECT
id, AVG(billable_hours) billables_average, AVG(total_hours) totals_average
FROM dept_three_week_hours
GROUP BY id;

# ======================================================================
# Associate 3-Week averages and variance from department 3-Week average
# ======================================================================
CREATE VIEW associate_three_week_average_and_variance AS
SELECT
a.*,
(a.billable_average - da.billables_average) billable_variance,
(a.total_average - da.totals_average) totals_variance
FROM associate_three_week_avg a, dept_three_week_avg da, departments d, users u
WHERE
a.id_user = u.employeeNumber AND
u.id_department = d.id AND
da.id = d.id;

# ====================
# Associate graph data
# ====================
CREATE VIEW associate_graph_data AS
SELECT
h.id_user, h.week_no, h.billable_hours, h.total_hours, c.total_capacity weekly_capacity, d.avg_billable_hours dept_billables, d.avg_total_hours dept_totals
FROM
associate_hours h, associate_avg_total_weekly_capacity c, dept_weekly_averages d, users u
WHERE
h.id_user = u.employeeNumber AND
u.id_department = d.id AND
h.id_user = c.id_user AND
h.week_no = c.week_no AND
c.week_no = d.week_no AND
h.year = YEAR(CURDATE())
ORDER BY id_user, week_no;

# ==========
# Dashboard
# ==========
CREATE VIEW dashboard_data AS
SELECT 
c.employeeNumber, c.name, c.level, c.Mon, c.Tue, c.Wed, c.Thu, c.Fri, c.Sat, c.Sun, SUBSTRING(nw.capacity, 1, 1) next_week,
a.billable_average, a.total_average, a.billable_variance, a.totals_variance,
t.billable_hours, t.total_hours,
c.id_department, c.status
FROM
associate_current_week_capacity c, associate_three_week_average_and_variance a, associate_hours t, capacity_next_week nw
WHERE
t.year = YEAR(CURDATE()) AND
c.employeeNumber = a.id_user AND
c.employeeNumber = t.id_user AND
a.id_user = t.id_user AND
c.employeeNumber = nw.id_user AND
YEAR(nw.created_at) = YEAR(CURDATE()) AND
nw.week_no = DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 7 DAY), '%u') AND
nw.year = DATE_FORMAT(CURDATE(), '%Y') AND
t.week_no = DATE_FORMAT(CURDATE(), '%u') - 1 AND
t.year = DATE_FORMAT(CURDATE(), '%Y')
ORDER BY c.name;
# ====


# Dashboard matters
CREATE VIEW dashboard_matters AS
SELECT
m.details, m.requirement, DATE_FORMAT(m.created_at, '%Y-%m-%d') matter_date, DATE_FORMAT(m.created_at, '%u') week_no, m.added_by, u.id_department,
(SELECT GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') FROM users, matter_partners where users.employeeNumber = matter_partners.id_partner and matter_partners.id_matter = m.id) partners,
(SELECT GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') FROM users, assignments where users.employeeNumber = assignments.assigned_to AND assignments.id_matter = m.id AND assignments.reassigned = 'N') associates
FROM matters m, users u
WHERE
m.added_by = u.employeeNumber
ORDER BY m.created_at DESC;

# ===================================================================================================================
# ===================================================================================================================
# ===================================================================================================================

# END OF YEAR VIEWS