I am trying to go through the exercise of converting an excel spreadsheet into apex and have stumbled across a few interesting tricks and tidbits.
One thing that I have noted is that stuff done in a spreadsheet can be automated via navigation menus in apex. I talk about this in another blog on how to create a navigation system based on parts of a service that you want to get you to the calculation that you need. This is much better if you don’t really know what you want and need to be lead through a menu system to help you decide on the service that you are looking for.
To create a calculator for metered and un-metered services in a spreadsheet requires two workbooks. You can tab between the two and enter data into each spreadsheet. If something like a pricelist is entered into a unique spreadsheet, static references and dynamic calculations can be easily. For example, we can create a workbook for archive – metered storage services and a workbook for archive – unmetered services which will be blank since this is not a service that is offered. If we create a third workbook called pricelist, we can enter the pricing for archive services into the pricelist spreadsheet and reference it from the other sheets. For archive cloud services you need to answer four basic questions; how many months, how much you will start archiving, how much you will end up with, and how much do we expect to read back during that period. We should see the following as questions
The cost will be calculated as
In Apex, this is done a little differently with a sequence of select statements and formatting statements to get the right answer
select ' sub-part: ' || PRICELIST.PART_NUMBER || ' - Archive Storage Capacity ' as Description, to_char(PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2, '$999,990') as PRICE from PRICELIST PRICELIST where PRICELIST.PART_NUMBER = 'B82623' UNION select ' sub-part: ' || PRICELIST.PART_NUMBER || ' - Archive Retrieval ' as Description, to_char(PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:RETRIEVE_ARCHIVE/100), '$999,990') as PRICE from PRICELIST PRICELIST where PRICELIST.PART_NUMBER = 'B82624' UNION select ' sub-part: ' || PRICELIST.PART_NUMBER || ' - Archive Deletes ' as Description, to_char(PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:DELETE_ARCHIVE/100), '$999,990') as PRICE from PRICELIST PRICELIST where PRICELIST.PART_NUMBER = 'B82629' UNION select ' sub-part: ' || PRICELIST.PART_NUMBER || ' - Archive Small Files ' as Description, to_char(:SMALL_ARCHIVE, '$999,990') as PRICE from PRICELIST PRICELIST where PRICELIST.PART_NUMBER = 'B82630' UNION select ' sub-part: ' || PRICELIST.PART_NUMBER || ' - Outbound Data Transfer ' as Description, to_char(PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:RETRIEVE_ARCHIVE/100), '$999,990') as PRICE from PRICELIST PRICELIST where PRICELIST.PART_NUMBER = '123456' UNION select ' Total:' as Description, to_char(sum(price), '$999,990') as Price from ( select PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2 as price from PRICELIST where pricelist.part_number = 'B82623' UNION select PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:RETRIEVE_ARCHIVE/100) as price from PRICELIST where pricelist.part_number = 'B82624' UNION select PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:DELETE_ARCHIVE/100) as price from PRICELIST where pricelist.part_number = 'B82629' UNION select PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:RETRIEVE_ARCHIVE/100) as price from PRICELIST where pricelist.part_number = '123456' );
The variables :INITIAL_ARCHIVE replaces F7, :FINAL_ARCHIVE replaces F8, and :RETRIEVE_ARCHIVE replaces F9. Rather than referring to the pricelist spreadsheet, we enter the pricing information into a database and do a select statement with the part_number being the key for the lookup. This allows for a much more dynamic pricebook and allows us to update and add items without risk of breaking the spreadsheet linkages. We can also use REST apis to create and update pricing using an outside program to keep our price calculator up to date and current. Using a spreadsheet allows users to have out of date versions and there really is not any way of communicating to users who have downloaded the spreadsheet that there are updates unless we are all using the same document control system.
Note that we can do running totals by doing a sum from a select … union statement. This allows us to compare two different services like Amazon Glacier and Oracle Archive easily on the same page. The only thing that we need to add is the cost of Glacier in the database and generate the select statements for each of the Glacier components. We can do this and use a REST api service nightly or weekly to verify the pricing of the services to keep the information up to date.
The select statements that we are use are relatively simple. The difficult part is the calculation and formatting out the output. For the bulk of the select statements we are passing in variables entered into a form and adding or multiplying values to get quantities of objects that cost money. We then look up the price from the database and print out dollar or quantity amounts of what needs to be ordered. The total calculation is probably the most complex because it uses a sum statement that takes the results of a grouping of select statements and reformats it into a dollar or quantity amount.
An example of the interfaces would look like
a traditional spreadsheet
and in Application Express 5.0
