PDF Generation Example

PDF files are widely used as email attachments for use cases like invoicing and purchasing. Often the PDF files need to be generated automatically from database data without user intervention.

AppSynergy allows you to dynamically generate HTML and then convert that HTML into a PDF document stored in your database.

Example – Hello World

The following example calls AppSynergy's built-in HTML2PDF web service. Before you can call the web service you will need to create an API Key to allow access to your account (see Tools > API Keys... for details).

Once you have an API Key you can call the HTML2PDF web service. The process has two steps:

generate the HTML and request conversion to PDF, then
process the response to store the generated PDF file in a document field.

Make the Request

BEGIN
  
  DECLARE v_req_body MEDIUMTEXT;
    
  -- create the request object
  SET v_req_body = JSON_OBJECT(
      'filename','HelloWorld.pdf',
      'html', '<html><body><h1>Hello World</h1></body></html>'
    );
  
  -- make the request
  INSERT INTO parasql_http_request (
    request_method, 
    request_url, 
    post_data, 
    post_data_encoding, 
    callback_procedure, 
    opt_metadata
    )
  VALUES (
    'POST', 
    'https://www.appsynergy.com/api?action=HTML2PDF&apiKey=YOUR_API_KEY', 
    v_req_body, 
    'application/json', 
    'HelloWorld_CALLBACK', 
    JSON_OBJECT('InvoiceNumber', 12345) 
    );
  
END

Process the Response

An example of a callback_procedure to process the response is shown below. In this example the stored procedure is called HelloWorld_CALLBACK and it takes a single parameter of type BIGINT called param_request_id.

BEGIN

  DECLARE v_pdf_doc_field VARCHAR(255); -- Document Fields are declared as VARCHAR(255)
  DECLARE v_invoice_number BIGINT;

  -- get the response (including opt_metadata where we have stored the related invoice number)
  SELECT JSON_VALUE(response_body, '$.data.documentField'), JSON_VALUE(opt_metadata, '$.InvoiceNumber') 
    INTO v_pdf_doc_field, v_invoice_number
    FROM parasql_http_request 
    WHERE id = param_request_id; -- param_request_id is the procedure's parameter

  -- update the Invoices table to save the PDF
  UPDATE Invoices
    SET Invoice_PDF = v_pdf_doc_field
    WHERE Invoice_ID = v_invoice_number;

END

Additional Documentation

See the Outbound Web Request documentation for details on how to make RESTful API calls. See the Sending Email documentation for details on how to send an email with a document field as an attachment.

Supported HTML/CSS

The HTML to be used for PDF generation purposes must be in XHTML format. XHTML requires that all elements are closed (i.e. <br> will NOT work but <br/> will).

Most of CSS 2.1 is supported. In addition there are additional directives you can use for things like page numbers in footers or repeating the header of an HTML table across the top of multiple page.

CSS for page numbering:

@page { 
  @bottom-center { content: "Page " counter(page) " of " counter(pages); } 
}

CSS for making a table header repeat at the top of each printed page:

table {
  -fs-table-paginate: paginate; 
  -fs-page-break-min-height: 50px; 
}

Example: Invoice_GetHTML()

If you are generating complex HTML (e.g. for an invoice) it is usually best to create a dedicated stored function to do so. The function can take a parameter like an invoice number and return the generated HTML for that invoice.

The following pseduo example (it doesn't read from any tables) provides an otherwise complete example.

BEGIN

  DECLARE logoURL VARCHAR(2048) DEFAULT 'https://www.appsynergy.com/icons/logo/appsynergy/appsynergy-logo-large.png';
  DECLARE html MEDIUMTEXT;
  DECLARE v_invoice_terms VARCHAR(50) DEFAULT 'NET 30';

  -- start HTML
  SET html = '<html><head>';
  
  -- style sheet - non-standard CSS noted with comments below
  SET html = concat(html, '<style>');
  SET html = concat(html, 'body {font-family: sans-serif; font-size: 12px; line-height: 120%;}');
  SET html = concat(html, '@page { @bottom-center { content: "Page " counter(page) " of " counter(pages); } }'); -- page numbers
  SET html = concat(html, 'table {border-collapse: collapse; width: 650px; margin: 10px; font-size:inherit; font-family: inherit;}');
  SET html = concat(html, 'table { -fs-table-paginate: paginate; -fs-page-break-min-height: 50px; }' ); -- repeat table header on each page
  SET html = concat(html, 'table, th, td {border: 1px solid black;}');
  SET html = concat(html, 'tr {page-break-inside: avoid;}'); -- makes rows break cleanly
  SET html = concat(html, 'th {font-weight: bold; padding: 5px; background-color:rgb(245,245,245);}');
  SET html = concat(html, 'td {padding: 5px;}');
  SET html = concat(html, 'td.summary {text-align: right; border: none;}');
  SET html = concat(html, 'td.summary-data {text-align: right;}');
  SET html = concat(html, 'td:nth-child(4), td:nth-child(5) {text-align: right;}');
  SET html = concat(html, 'div.info-box {display:inline-block; vertical-align:top; width:300px; height:auto; margin:5px; border:0px solid black; padding:5px;}');
  SET html = concat(html, '</style>');
  
  -- open body
  SET html = concat(html, '</head><body>');
  
  -- box 1 - logo
  SET html = concat(html, '<div class="info-box"><img src="', logoURL, '" style="max-width:200px" /></div>'); -- the trailing / character on IMG tags is REQUIRED by XHTML 
  
  -- box 2 - INVOICE #
  SET html = concat(html, '<div class="info-box">');
  SET html = concat(html, '<b>INVOICE #:</b> ', 12345, '<br/>');
  SET html = concat(html, 'Invoice Date: ', DATE_FORMAT('2020-09-04', '%m/%d/%Y'), '<br/>');
  SET html = concat(html, 'Terms: ', parasql_escape_html(v_invoice_terms), '<br/>'); -- ALWAYS escape variables with parasql_escape_html()
  SET html = concat(html, '</div>');
  
  SET html = concat(html, '<br/>'); -- the trailing / character is REQUIRED by XHTML 
  
  -- box 3 - REMIT TO
  SET html = concat(html, '<div class="info-box">');
  SET html = concat(html, '<b>REMIT TO:</b>', '<br/>');
  SET html = concat(html, 'My Company LLC','<br/>');
  SET html = concat(html, '456 Main St.','<br/>');
  SET html = concat(html, 'New York, NY 10023','<br/>');
  SET html = concat(html, '</div>');
  
  -- box 4 - BILL TO
  SET html = concat(html, '<div class="info-box">');
  SET html = concat(html, '<b>BILL TO:</b>', '<br/>');
  SET html = concat(html, 'ACME LLC', '<br/>');
  SET html = concat(html, '123 Main St', '<br/>');
  SET html = concat(html, 'Palm Beach, FL 33458', '<br/>');
  SET html = concat(html, '</div>');
  
  SET html = concat(html, '<br/>');
  
  -- start table
  SET html = concat(html, '<table>');
  
  -- table header
  SET html = concat(html, '<thead><tr><th>Item #</th><th>Description</th><th>Qty</th><th>Unit Price</th><th>Total</th></tr></thead>');

  -- FOR EACH ROW - this would be in a loop
  SET html = concat(html, '<tr><td>4567</td><td>XYZ Product Description</td><td>10</td><td>$25.00</td><td>$250.00</td></tr>');
  SET html = concat(html, '<tr><td>4567</td><td>XYZ Product Description</td><td>10</td><td>$25.00</td><td>$250.00</td></tr>');
  SET html = concat(html, '<tr><td>4567</td><td>XYZ Product Description</td><td>10</td><td>$25.00</td><td>$250.00</td></tr>');

  -- table footer
  SET html = concat(html, '<tr><td class="summary" colspan="4">Subtotal:</td><td class="summary-data">$', 750.00, '</td></tr>');
  SET html = concat(html, '<tr><td class="summary" colspan="4">Shipping:</td><td class="summary-data">$', 0.00, '</td></tr>');
  SET html = concat(html, '<tr><td class="summary" colspan="4">Total:</td><td class="summary-data">$', 750.00, '</td></tr>');
  
  -- end table
  SET html = concat(html, '</table>');
  
  -- close body
  SET html = concat(html, '</body></html>');
  
  -- return the HTML
  RETURN html;
END

Diagnosing Problems

If you run into problems, try issuing a command like the following via Tools > SQL Console... to diagnose what went wrong:

SELECT * FROM parasql_http_request ORDER BY id DESC LIMIT 1