Exporting Multiple "Templates" into a .PDF at Once

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

    Hi,
    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?

    Posted 2 weeks ago #
  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

    Posted 2 weeks ago #
  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?

    Posted 2 weeks ago #
  4. myDBR Team, Key Master

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

    --
    myDBR Team

    Posted 2 weeks ago #
  5. rpark, Member

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

    Posted 2 weeks ago #
  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
    CURSOR FOR
    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
    $$

    Posted 1 week ago #

Reply

You must log in to post.