CREATE OR REPLACE VIEW v_timesheets_alert AS

SELECT a.ts_date as_of_date,
	a.employee,
	TO_CHAR(a.ts_date,'DD-Mon') ts_day,
	substr(a.ts_desc,1,510) ts_desc,
	a.task_name,
	a.project_name,
	a.amount,
	a.unit_amount
FROM v_timesheets a
WHERE TO_CHAR(a.ts_date,'iw') = TO_CHAR(current_date-1,'iw')

CREATE OR REPLACE VIEW v_tasks_tracking AS 

SELECT b.id project_id,
   c.name project_name,
   a.id task_id,
   a.name task_name,
   --a.create_date,
   TO_CHAR(a.date_assign,'DD-Mon') assigned_on,
   g.partner_name assigned_by,
   f.partner_name assigned_to,
   TO_CHAR(a.date_deadline,'DD-Mon') deadline_on,
   a.priority,
   e.name status
FROM project_task a,
	project_project b,
	account_analytic_account c,
	project_task_type e,
	v_users f,
	v_users g
WHERE a.project_id = b.id
	AND b.analytic_account_id = c.id
	AND e.id = a.stage_id
	AND a.user_id = f.user_id
	AND a.create_uid = g.user_id
	AND e.name NOT IN ('Archived','Completed','Done','Solved')
	AND b.id <> 1

CREATE OR REPLACE VIEW v_crm_activities AS 
    SELECT a.id activity_id, CASE
    WHEN current_date = a.date_deadline::date THEN 'Planned Today'
    WHEN current_date > a.date_deadline::date THEN 'Delayed'
    WHEN current_date < a.date_deadline::date THEN 'Planned' END activity_status,
    b.name activity_type,
    c.id lead_id,
    c.name lead_name,
    a.summary activity,
    REGEXP_REPLACE(REGEXP_REPLACE(a.note, E'<.*?>', '', 'g' ), E'&nbsp;', '', 'g') activity_note,
    d.partner_name assigned_to,
    a.create_date::date assigned_on,
    a.date_deadline due_on,
    null done_on
    FROM mail_activity a,
    mail_activity_type b,
    crm_lead c,
    v_users d
    WHERE   a.activity_type_id = b.id
    AND a.res_id = c.id
    AND a.user_id = d.user_id
    AND a.res_model = 'crm.lead'
    UNION ALL
    SELECT  a.id activity_id,
    'Done' Activity_status,
    c.name activity_type,
    b.id lead_id,
    b.name lead_name,
    REPLACE(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(a.body, E'<.*?>', '', 'g' ), E'&nbsp;', '', 'g'),'',''),'','') activity, ''::text activity_note,
    d.partner_name done_by,
    null assigned_on,
    null due_on,
    a.date::date done_on FROM mail_message a,
    crm_lead b,
    mail_activity_type c,
    v_users d
    WHERE   a.res_id = b.id
    AND a.author_id = d.partner_id
    AND a.mail_activity_type_id = c.id
    --AND a.record_name = 'ENGIE | Italy ERP'

CREATE OR REPLACE VIEW v_crm_tracking_alert AS SELECT    
    a.id lead_id,
    b.name lead_stage,
    a.name lead_name,
    SUBSTR((SELECT x.activity FROM v_crm_activities x
    WHERE x.activity_id IN (select MAX(activity_id) activity_id FROM v_crm_activities
    WHERE activity_status = 'Done' group by lead_id)
    AND x.lead_id = a.id),1,510) last_activity,
    (SELECT TO_CHAR(x.done_on,'DD-Mon-YY') done_on FROM v_crm_activities x
    WHERE x.activity_id IN (
    SELECT MAX(activity_id) activity_id FROM v_crm_activities
    WHERE activity_status  = 'Done' group by lead_id) AND x.lead_id = a.id) last_activity_date,
    (SELECT x.assigned_to FROM v_crm_activities x
    WHERE x.activity_id IN (SELECT MAX(activity_id) activity_id FROM v_crm_activities
    WHERE activity_status  = 'Done' group by lead_id) AND x.lead_id = a.id) last_activity_by, SUBSTR((SELECT x.activity FROM v_crm_activities x
    WHERE x.due_on IN (SELECT MIN(z.due_on) FROM v_crm_activities z
    WHERE z.activity_status <> 'Done' AND z.lead_id = a.id) AND x.lead_id = a.id),1,510) next_activity,
    (SELECT TO_CHAR(x.due_on ,'DD-Mon-YY') due_on FROM v_crm_activities x
    WHERE x.due_on IN (SELECT min(z.due_on) FROM v_crm_activities z
    WHERE z.activity_status <> 'Done' AND z.lead_id = a.id) AND x.lead_id = a.id) next_activity_date,
    (SELECT x.assigned_to FROM v_crm_activities x
    WHERE x.due_on IN (SELECT min(z.due_on) FROM v_crm_activities z
    WHERE z.activity_status <> 'Done' AND z.lead_id = a.id) AND x.lead_id = a.id) next_activity_by,
    (SELECT x.activity_status FROM v_crm_activities x
    WHERE x.due_on IN (SELECT min(z.due_on) FROM v_crm_activities z
    WHERE z.activity_status <> 'Done' AND z.lead_id = a.id) AND x.lead_id = a.id) next_activity_status FROM crm_lead a, crm_stage b
    WHERE a.stage_id = b.id ORDER by
    b.name = 'New Leads' DESC,
    b.name = 'Proposition' DESC,
    b.name = 'Opportunity' DESC,
    b.name = 'Qualified Opportunity' DESC,
    b.name = 'Negotiation' DESC,
    b.name = 'Won' DESC,
    b.name = 'Lost' DESC

CREATE OR REPLACE VIEW v_users AS

SELECt  a.id user_id,
	a.login user_login,
	a.active user_active,
	b.id partner_id,
	b.name partner_name,
	b.display_name partner_display_name,
	b.active partner_active,
	b.email partner_email,
	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(b.mobile,' ',''),'-',''),')',''),'(',''),'+','') partner_mobile 
	FROM res_users a, res_partner b
	WHERE a.partner_id = b.id;

CREATE OR REPLACE VIEW v_timesheets AS 
	SELECT e.partner_name employee,
	a.date ts_date,
	a.name ts_desc,
	d.name task_name,
	c.name project_name,
	a.amount,
	a.unit_amount  
	--b.total_attendance,
	--b.total_timesheet,
	--b.total_difference,
	FROM account_analytic_line a LEFT JOIN project_task d ON a.task_id = d.id,
	project_project b,
	account_analytic_account c,
	v_users e,
	--hr_timesheet_attendance_report b
	WHERE a.project_id = b.id
	AND b.analytic_account_id = c.id
	AND a.user_id = e.user_id