Hey there, welcome back to the epic saga of our REST Work Diary! This blog post turned out to be a bit longer than I initially thought, but hey, stick around, and you won't regret it.

Today, I'm pumped to guide you through the magic of integrating the REST API for currency conversion into a view and transforming this view into an app page. Brace yourself because we're diving into two app pages: the first one is 'My Earnings,' giving you the lowdown on active clients, their hourly fee, and earnings in both USD and EUR. I'm in the EU, so I decided to implement the USD-EUR conversion, but feel free to play around with any currency you fancy.

Now, onto our second page, 'Hours Worked.' It's your go-to for a yearly business hours overview. You'll get the client breakdown, total hours worked, and make use of Oracle APEX settings to spice things up. This page can be a game-changer, especially for solo entrepreneurs, helping you identify those high-value clients and make savvy business decisions.

Quick tip from the trenches: Always juggle at least two gigs. If one falters, having a backup gig can save the day. Diversify your income, my friend, and you'll thank yourself later.

‍

Hours Worked View

Before I go off topic, let's kick things off with the 'Hours Worked' view! A few steps stand between us and glory. Create a simple SQL statement for a view, test it in the Oracle APEX' SQL Workshop, and when you're loving the result, create the view either through SQL Scripts or the Object Browser. Oracle APEX's immediate feedback is a lifesaver, trust me.

‍

‍

Here's my SQL for this view:

 CREATE OR REPLACE FORCE EDITIONABLE VIEW "HOURS_WORKED" ("ENTRY_ID", "CLIENT_ID", "HOURS_WORKED") AS

 SELECT

w.entry_id,

w. client_client_id AS client_id,

((EXTRACT(HOUR FROM (w.end_timestamp - w.start_timestamp)) * 60

   + EXTRACT(MINUTE FROM (w.end_timestamp - w.start_timestamp))

   - NVL(EXTRACT(HOUR FROM (w.end_lunch_break - w.start_lunch_break)) * 60

       + EXTRACT(MINUTE FROM (w.end_lunch_break - w.start_lunch_break)), 0)

   ) / 60.0) AS hours_worked

FROM wd_entry w

JOIN client c ON w.client_client_id = c.client_id;

‍

Once we are happy with the view, we can then define it under the RESTful Services section in Oracle APEX, and use it in our app as a Shared resource. If you don't remember how we did this, I'll link the two relevant blogs for you.

From here it's pretty straightforward: we'll add another page in our app, and voila, here is the end result.

‍

‍

My Earnings View

Now, the 'My Earnings' page follows the same principle. Check out the SQL below:

‍

CREATE OR REPLACE FORCE EDITIONABLE VIEW "MY_EARNINGS" ("ENTRY_ID", "CLIENT_ID", "HOURLY_FEE", "EARNINGS_IN_USD", "LATEST_EXCHANGE_RATE", "EARNINGS_IN_EUR") DEFAULT COLLATION "USING_NLS_COMP"  AS

 SELECT

   w.entry_id,

   w.client_client_id AS client_id,

   c.hourly_fee,

   ((EXTRACT(HOUR FROM (w.end_timestamp - w.start_timestamp)) * 60

   + EXTRACT(MINUTE FROM (w.end_timestamp - w.start_timestamp))

   - NVL(EXTRACT(HOUR FROM (w.end_lunch_break - w.start_lunch_break)) * 60

       + EXTRACT(MINUTE FROM (w.end_lunch_break - w.start_lunch_break)), 0)

   ) / 60.0) * c.hourly_fee AS earnings_in_usd,

   ce.value AS latest_exchange_rate,

   ((EXTRACT(HOUR FROM (w.end_timestamp - w.start_timestamp)) * 60

   + EXTRACT(MINUTE FROM (w.end_timestamp - w.start_timestamp))

   - NVL(EXTRACT(HOUR FROM (w.end_lunch_break - w.start_lunch_break)) * 60

       + EXTRACT(MINUTE FROM (w.end_lunch_break - w.start_lunch_break)), 0)

   ) / 60.0) * c.hourly_fee * ce.value AS earnings_in_eur

FROM wd_entry w

JOIN client c ON w.client_client_id = c.client_id

LEFT JOIN (

   SELECT *

   FROM currency_exchange_rate

   WHERE LAST_UPDATED_AT = (SELECT MAX(LAST_UPDATED_AT) FROM currency_exchange_rate)

) ce ON 1=1;

‍

‍

And there you have it! Feast your eyes on the result below. Any burning questions? Hit me up via the contact page. Always happy to discuss my SQL :).

‍

‍

Stay tuned for the next blog in this series where we'll supercharge our app even more before I jet off to new projects! Let me know your thoughts on this master plan, folks!

‍