-- SQL Query - Consumed units of a flat rate SELECT name, sum(u.amount) || ' ' || ' / ' || fs.amount || ' ' || s.units AS amount FROM CH23_USAGELOG u JOIN CH23_CUSTOMERFLATRATE cf ON (u.customerid = cf.customerid AND u.flatrateid = cf.flatrateid) JOIN CH23_CUSTOMER c ON (cf.customerid = c.customerid) JOIN CH23_PERSON p ON (c.personid = p.personid) JOIN CH23_SERVICE s ON (u.serviceid = s.serviceid) JOIN CH23_FLATRATESERVICE fs ON (fs.flatrateid = cf.flatrateid AND fs.serviceid = s.serviceid) WHERE email = :APP_USER AND EXTRACT(month from when) = EXTRACT(month from sysdate) GROUP BY s.serviceid, s.name, s.units, fs.amount; / -- SQL Query - Consumed units of extra services. SELECT name, SUM(amount) || ' ' || units AS amount FROM CH23_EXTRASERVICE JOIN CH23_CUSTOMER USING(customerid) JOIN CH23_PERSON USING (personid) JOIN CH23_SERVICE USING (serviceid) WHERE email = :APP_USER AND EXTRACT(month from startDate) = EXTRACT(month from sysdate) GROUP BY serviceid, name, units; / -- SQL Query for an invoice report SELECT f.price || ' €' as "Fixed Flatrate", SUM(CASE e.serviceid WHEN 1 THEN NVL(e.amount, 0) ELSE 0 END) || ' ' || listagg(CASE s.serviceid WHEN 1 THEN s.units ELSE '' END, '') AS "Extra SMS", SUM(CASE e.serviceid WHEN 2 THEN NVL(e.amount, 0) ELSE 0 END) || ' ' || listagg(CASE s.serviceid WHEN 2 THEN s.units ELSE '' END, '') AS "Extra Minutes", SUM(CASE e.serviceid WHEN 3 THEN NVL(e.amount, 0) ELSE 0 END) || ' ' || listagg(CASE s.serviceid WHEN 3 THEN s.units ELSE '' END, '') AS "Extra Data", SUM(CASE e.serviceid WHEN 4 THEN NVL(e.amount, 0) ELSE 0 END) || ' ' || listagg(CASE s.serviceid WHEN 4 THEN s.units ELSE '' END, '') AS "Extra MMS", (f.price + SUM(CASE e.serviceid WHEN 1 THEN NVL(e.amount, 0) * s.price ELSE 0 END) + SUM(CASE e.serviceid WHEN 2 THEN NVL(e.amount, 0) * s.price ELSE 0 END) + SUM(CASE e.serviceid WHEN 3 THEN NVL(e.amount, 0) * s.price ELSE 0 END) + SUM(CASE e.serviceid WHEN 4 THEN NVL(e.amount, 0) * s.price ELSE 0 END) || ' €') AS "Total" FROM CH23_CUSTOMER c JOIN CH23_PERSON p ON (p.personid = c.personid) JOIN CH23_CUSTOMERFLATRATE cf ON (cf.customerid = c.customerid) JOIN CH23_FLATRATE f ON (f.flatrateid = cf.flatrateid) JOIN CH23_FLATRATESERVICE fs ON (fs.flatrateid = f.flatrateid) JOIN CH23_SERVICE s ON (s.serviceid = fs.serviceid) LEFT JOIN CH23_EXTRASERVICE e ON (e.customerid = cf.customerid) WHERE p.email = :APP_USER AND (e.startdate IS NULL OR (EXTRACT(month from e.startdate) = EXTRACT(month from sysdate) AND EXTRACT(year from e.startdate) = EXTRACT(year from sysdate))) GROUP BY f.price; / -- SQL Query – Usage log SELECT name, phone, amount || ' ' || units AS amount, when FROM CH23_USAGELOG JOIN CH23_CUSTOMER USING (customerid) JOIN CH23_PERSON USING (personid) JOIN CH23_SERVICE USING (serviceid) WHERE email = :APP_USER ORDER BY when DESC; -- SQL Query - Statistics SELECT TO_CHAR(when, 'MM/YYYY') AS "Month", SUM(amount) AS Amount, CASE WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '01' THEN '#F8B195' WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '02' THEN '#F67280' WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '03' THEN '#C06C84' WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '04' THEN '#6C567B' WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '05' THEN '#ACDEAA' WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '06' THEN '#97D0ED' WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '07' THEN '#C23B23' WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '08' THEN '#6B7B8E' WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '09' THEN '#D5AF26' WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '10' THEN '#9AB5C1' WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '11' THEN '#74698C' WHEN SUBSTR(TO_CHAR(when, 'MM/YYYY'), 0, 2) = '12' THEN '#C1867B' ELSE 'black' END AS MY_COLOR FROM CH23_USAGELOG JOIN CH23_CUSTOMER USING (customerid) JOIN CH23_PERSON USING (personid) JOIN CH23_SERVICE USING (serviceid) WHERE email = :APP_USER AND serviceid = 2 GROUP BY TO_CHAR(when, 'MM/YYYY') ORDER BY 1; -- SQL Query - Customers SELECT firstname, lastname, email, address, phone FROM CH23_PERSON JOIN CH23_CUSTOMER USING (personid);