DROP VIEW v_stats_calls;
CREATE VIEW v_stats_calls AS
SELECT
 astiumcdr.sessionid, astiumcdr.start_time,
 date_part('hour', astiumcdr.start_time) as hour, date_part('minute', astiumcdr.start_time) as minute,
 astiumcdr.src_num   as from_number,  astiumcdr.src_name  as from_name,  astiumcdr.src_type  as from_type,
 fromdepartment.name as from_department,
 fromcompany.name    as from_company,
 astiumcdr.dial_num  as dial_number,  astiumcdr.dial_name as dial_name,  astiumcdr.dial_type as dial_type,
 dialdepartment.name as dial_department,
 dialcompany.name    as dial_company,
 astiumcdr.ans_num   as ans_number,   astiumcdr.ans_name  as ans_name,   astiumcdr.ans_type  as ans_type,
 ansdepartment.name  as ans_department,
 anscompany.name     as ans_company,
 astiumcdr.call_duration as totaltime, astiumcdr.bill_duration as calltime, astiumcdr.ddi
FROM
 astiumcdr,
 department as fromdepartment, department as dialdepartment, department as ansdepartment,
 company as fromcompany, company as dialcompany, company as anscompany,
 contact, contact_company
WHERE
     contact.id=contact_company.contact
 AND contact.username=USER
 AND  astiumcdr.src_department  = fromdepartment.id
 AND  astiumcdr.dial_department = dialdepartment.id
 AND  astiumcdr.ans_department  = ansdepartment.id
 AND  astiumcdr.src_company     = fromcompany.id
 AND  astiumcdr.dial_company    = dialcompany.id
 AND  astiumcdr.ans_company     = anscompany.id
 AND (astiumcdr.src_company     = contact_company.company OR
      astiumcdr.dial_company    = contact_company.company OR astiumcdr.ans_company=contact_company.company)
 ORDER BY start_time DESC;
GRANT SELECT ON v_stats_calls TO GROUP astium_users;

