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?
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
   
   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
   
   $$
   
   

Reply

You must log in to post.