CREATE OR REPLACE 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 left outer join department as fromdepartment on (astiumcdr.src_department  = fromdepartment.id)
           left outer join department as dialdepartment on (astiumcdr.dial_department = dialdepartment.id)
           left outer join department as ansdepartment  on (astiumcdr.ans_department  = ansdepartment.id)
           left outer join company    as fromcompany    on (astiumcdr.src_company     = fromcompany.id)
           left outer join company    as dialcompany    on (astiumcdr.dial_company    = dialcompany.id)
           left outer join company    as anscompany     on (astiumcdr.ans_company     = anscompany.id),
 contact, contact_company
WHERE
     contact.id=contact_company.contact
 AND contact.username=USER
 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;

