Exporting Multiple "Templates" into a .PDF at Once

(6 posts) (2 voices)
  1. rpark, Member

    When I refer to "Templates" I am referring to the Invoice Demo in which data from the DB is reported out as an invoice using an HTML based Template.

    I have a use case very similar to an Invoice, but I'm creating Work Orders from data in the DB. This being said, some clients will have up to 200 Work Orders at a time which need to be batch PDF'd and then sent out as a single file; with each page of the PDF being a new Work Order.

    I am trying to conceptualize if MyDBR offers batch reporting to PDF such as what I describe above with the Work Orders?

  2. myDBR Team, Key Master

    What you do is that you create one work order per page (use a cursor to go through the work orders) and then simply put a page break between the work orders. The result will be one PDF with all the work orders in it.

    myDBR Team

  3. rpark, Member

    Thank you for helping me conceptualize this!

    So just grab a Cursor for your result set, then iterate through it drawing Template w/ a page break for each Cursor row..

    Seems conceptually simple, I assume I can also track the last Cursor result to prevent it from having a page break leading to a blank page in the PDF?

  4. myDBR Team, Key Master

    Sure, you have the full control over the poage breaks.

    myDBR Team

  5. rpark, Member

    You guys have great service, it was one of my decisions to pursue this product, thanks for keeping it up!

  6. rpark, Member

    For anyone else who wants to do this, I want to "pay this forward" by showing a working implementation of this concept:

    DROP PROCEDURE IF EXISTS sp_DBR_workorder_report_listing
    CREATE PROCEDURE `sp_DBR_workorder_report_listing`(inIds text) #Parameter inIds accepts record IDs from a selectable report
    BEGIN DECLARE finished INTEGER DEFAULT 0; #To loop a Cursor you need a counter that can stop the loop, this is that counter variable
    DECLARE tag_number VARCHAR(6) DEFAULT ""; #Arbitrary variable declaration, the Cursor needs to have each field declared as a variable for it to be usable in the report DECLARE curFugitiveMeasurements #Declare the cursor and its select statement
    select fm.TAG_NUMBER
    from database.ss_fugitive_measurements as fm
    WHERE find_in_set(fm.ID,inIds); #find_in_ids(field,values) is a MYSQL command that will iterate through the comma-separated IDs matching them to the fm.ID column in the database DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; #We must declare when the handler for when the loop has iterated through all records and the loop needs to end
    OPEN curFugitiveMeasurements; #Cursors must be opened and closed, start by opening it of course getFugitiveMeasurements: LOOP #Define the loop (can reference loop by name) FETCH curFugitiveMeasurements INTO tag_number; #Fetch the DB record under the Cursor and place its value into the variable defined for the report
    IF finished = 1 THEN #Loop will check on each run if this is the end of the Cursor or not, if it is, we LEAVE the loop
    LEAVE getFugitiveMeasurements;
    END IF; SELECT 'dbr.template','#workorder_row'; #Here I define the Template to output this Cursor's record to select
    tag_number as 'tag_number'; #Here I define the variable into the field the Template is looking for. Unfortunately they are named the same... END LOOP getFugitiveMeasurements; #Define the end of the loop
    CLOSE curFugitiveMeasurements; #Close the Cursor to release resources and prevent issues with future Cursors END


You must log in to post.