Easy APEX email using JsPDF

 In my previous post, I explained how to easily create a PDF document of a part of a web page created with APEX.

Now that we know how to create, download and possibly print the PDF, the question arises: can we email this PDF file?

So, here is our APEX again, now with button Email:

The JavaScript to create the PDF file is more or less the same. Instead of downloading the file, we now upload the file to the database, and email the file from there using APEX_MAIL.

The JavaScript function called from the Click Dynamic Action is:

function mailJsPDF() {

    // This isn't mentioned anywhere, but it is needed since 2.0!

    window.jsPDF = window.jspdf.jsPDF;

    var doc = new jsPDF ();

    doc.html(document.getElementById('container'), {

       callback: function (doc) {

         // Save blob:

         var blob = new Blob ( [doc.output('blob')], {type: 'application.pdf'} );

         // Base64 encode:

         var reader = new FileReader();

         reader.onload = function () {

            // Since it contains the Data URI, we should remove the prefix and keep only Base64 string

            var b64 = reader.result.replace(/^data:.+;base64,/, '');

            //Send the blob to the server via Ajax process

            apex.server.process(

              'UPLOAD',

               {

                 p_clob_01: b64

               },

               {

                success: function(data) {

                    alert("The invoice was mailed");

                 }

               }

            );

         };

         reader.readAsDataURL(blob);

       },

            width: 170,

        windowWidth:700,

        margin: [20, 0, 0, 20],

        html2canvas: {

          scale: .238

        }    

    });

  }

The UPLOAD process gets the uploaded CLOB, converts it back to a BLOB, and emails it as an attachment. In a real application you will probably also store the generated PDF file in the database, get the email address from the website etc.

declare

  l_clob   clob;

  l_blob   blob;

  l_id     number;

begin

  --Get the clob passed in from the Ajax process

  l_clob := apex_application.g_clob_01;

  apex_debug.message('Uploaded: '||dbms_lob.substr(l_clob, 200, 1));

  -- Covert it back to a blob

  l_blob := APEX_WEB_SERVICE.CLOBBASE642BLOB (p_clob => l_clob);

  l_id := APEX_MAIL.SEND(

        p_to        => 'you@gmail.com',

        p_from      => 'me@gmail.com',

        p_subj      => 'Invoice with attachment',

        p_body      => 'Goodday, see the attachment.',

        p_body_html => 'Goodday, see the attachment.');

  APEX_MAIL.ADD_ATTACHMENT(

        p_mail_id    => l_id,

        p_attachment => l_blob,

        p_filename   => 'invoice.pdf',

        p_mime_type  => 'application/pdf');

  COMMIT;

  APEX_MAIL.PUSH_QUEUE();

 

  --Write some JSON out for the response

  apex_json.open_object();

  apex_json.write('status', 'success');

  apex_json.close_object();  

end;

 

Drag and Drop Shopping Cart

At SQL Integrator we are just beginning to explore the possibilities of Apex. We do that by teaching each other via workshops. When somebody dives into a subject, he presents the result to the other Apex workshop participants. A good way to learn Apex.

I had no idea how to do anything with Ajax. After some web surfing (mainly at w3schools.com) I tried some simple examples myself. Most of them were taken from here and here.
A quite fancy example is the drag and drop planboard, but this was a bit too complicated for me to start with (especially since some secrets were not explicitly revealed).

So, I came up with this simple shopping cart demo. There was not much programming involved, since most of the Javascript code was downloaded from script.aculo.us.

Blogger is trying to interpret tags when you just want to publish the code, so bear in mind that you have to replace the [] pair with <> in the next pieces of code.

The left region is a report based on:

select '[div class="boxwide" id="'||product_id||'"]'||
   product_name||'[/div]' product
from demo_product_info

Each product gets a unique id so that you know which product is dragged. The class boxwide is a CSS class that puts the product name in a box.

Note that is is generally not a good idea to put HTML in your query. It is a better idea to put this in the HTML Expression on the Column Attributes page. In this the query should be:

select product_id
     ,product_name
from demo_product_info

And the HTML Expression:

[div class="boxwide" id=#PRODUCT_ID#]#PRODUCT_NAME#[/div]

The region on the right is an empty HTML region withe a region header:

[div id="cart"]

and footer:

[/div]

This identifies the region where products can be dropped.

Next, you have to put some Javascript in the page header to call application processes and the script.aculo.us libraries.

[script src="#WORKSPACE_IMAGES#prototype.js" type="text/javascript"][/script]
[script src="#WORKSPACE_IMAGES#scriptaculous.js" type="text/javascript"][/script]
[link type="text/css" href="#WORKSPACE_IMAGES#dragdrop.css" rel="stylesheet"]
[script language="javascript" type="text/javascript"]
function addProduct(element, dropon, event) {
sendData(element.id);
}
function sendData (prod) {
var get = new htmldb_Get(null,$x('pFlowId').value,'APPLICATION_PROCESS=AddToCart',0);
get.addParam('x01',prod)
gReturn = get.get();
$x('cart').innerHTML = gReturn;
}
function clearCart () {
var get = new htmldb_Get(null,$x('pFlowId').value,'APPLICATION_PROCESS=ClearCart',0);
gReturn = get.get();
$x('cart').innerHTML = gReturn;
}
function getCart () {
var get = new htmldb_Get(null,$x('pFlowId').value,'APPLICATION_PROCESS=GetCart',0);
gReturn = get.get();
$x('cart').innerHTML = gReturn;
}
[/script]

In the footer of the page put this Javascript code to identify which elements are involved in the drag and drop process:

[script type="text/javascript"]
var products = document.getElementsByClassName('boxwide');
for (var i = 0; i < products.length; i++) {
 new Draggable(products[i].id, {ghosting:false, revert:true}) 
} 
Droppables.add('cart', {onDrop:addProduct})
[/script] 

Next you define the appication process AddToCart that is called when an item is dropped on the shopping cart region:

declare
  l_string   varchar2(32767);
begin
  insert into demo_shopping_cart(sessionid, product_id)
  values(v('APP_SESSION'), to_number(wwv_flow.g_x01) );
  commit;

  l_string := get_shopping_cart(v('APP_SESSION'));
  htp.p(l_string);
end;

Get_shopping_cart is a function in the database that returns the items currently in the cart for the session:

CREATE OR REPLACE function get_shopping_cart(i_sessionid in number)
return varchar2 is
cursor c_cart is
  select '[div class="boxwide" id="cart_'||id||'"]'||
         product_name||'[/div]' product_name
  from demo_shopping_cart crt
  ,    demo_product_info prd
  where crt.product_id = prd.product_id
  and crt.sessionid = i_sessionid
  order by id;
  l_string varchar2(32767);
begin
  for r_cart in c_cart loop
     l_string := l_string || r_cart.product_name;
  end loop;
  return l_string;
end;

Similarly, you can create the ClearCart and GetCart application processes that are called when you click the Clear Cart button and when you open the page.