dbr.mail attachment

(18 posts) (2 voices)
  1. ajdjackson, Member


    Now that I've managed to get dbr.mail working hopefully this is a bit easier.

    I've a report on which I have a button that lets the user create a pdf of a report and email it.

    The email bit is working fine but I have a few issues with the pdf.

    1) The pdf attachment is corrupt and won't open

    2) The images are missing from the pdf export

    3) Attempting to head the header in pdf export fails.

    The main report on which the button resides contains the following code:

    CREATE PROCEDURE `sp_DBR_SS_Summary`(inPltID varchar(25))
    BEGIN ....... select 'dbr.html','<div class="buttons">';
    select 'dbr.report', 'sp_DBR_Email_Invoice', 'inPlot=1','new_window';
    select 'dbr.button','dbr.html:< img src="user/images/email.gif" alt="email" />','button';
    select inPltID;
    select 'dbr.html','</div>';

    The linked report has the following code:

    CREATE PROCEDURE `sp_DBR_Email_Invoice`(inPlot varchar(25))
    BEGIN select 'dbr.mail';
    select 'dbr.mail.sender','my email address','my name';
    select 'dbr.mail.debug', 0;
    select 'dbr.mail.attach', 'invoice.pdf', 'http://xxx.xxx.com/mydbr/report.php?r=294&m=7&h=c46701e7ab2da6c009322aea15a33a4c95ca9996&i=1&export=pdf','in_Plot=(inPlot)';
    select 'email address', 'Sender', 'Your Options', 'Dear Customer Please find attached a copy of your invoice for the options you have chosen for your home';

    The procedure called by dbr.mail.attach is:

    CREATE PROCEDURE `sp_DBR_Print_Invoice`(in_Plot varchar(25))
    select 'dbr.css','body {background:white;}';
    select 'dbr.export.options', 'orientation', 'portrait';
    select 'dbr.export.options', 'paper_size', 'A4';
    select 'dbr.export.options', 'font', 'Verdana';
    select 'dbr.export.options', 'font_size', 11;
    select 'dbr.export.options', 'font_width', '50px';
    select 'dbr.export.options', 'autosize', 1; /* Turn off autosize */
    /*select 'dbr.wkhtmltopdf', "--header-html ''"; /* Do not use header in export using wkhtmltopdf */ select 'dbr.title',""; select 'dbr.template', '#SS_PrintInv_Header';
    select 'dbr.css','.invcon {width:760px;}';
    select 'dbr.css','.invheader {width:80%;}'; select "" as 'company',
    "XX XX, XX" as 'address',
    curdate() as 'date',
    t.dc_HouseNumber as 'unit',
    t.dc_AddressLine1 as 'uaddress',
    t.dc_Town as 'town',
    t.dc_Postcode as 'postcode'
    from hilmark.devc_xref s
    join hilmark.devc_import t
    on s.PropertyID = t.dc_PropertyID
    where s.MIS_ID = in_Plot; select 'dbr.template', '#SS_Invoice_Detail'; select date_format(t.tblss_orders_Datel,"%d/%m/%Y") as 'odate',
    t.tblss_orders_ChoicesDesc as 'desc',
    t.tblss_orders_Qty as 'qty',
    t.tblss_orders_Qty * t.tblss_orders_ChoicesSP as 'total'
    from hilmark.tblss_orders t
    where t.tblss_orders_PlotID = in_Plot; select 'dbr.template', '#SS_Invoice_Footer'; select sum(t.tblss_orders_Qty * t.tblss_orders_ChoicesSP) as 'order_total'
    from hilmark.tblss_orders t
    where t.tblss_orders_PlotID = in_Plot
    group by in_Plot;

    Where this is failing is in the passing of the variable in the export to pdf. If I use CREATE PROCEDURE `sp_DBR_Print_Invoice`() and hardcode the variable the pdf is created correctly.

    Also if I remove the comments from select 'dbr.wkhtmltopdf', "--header-html ''"; the file again is corrupted.

    The invoice contains a company logo that displays OK on screen but is missing in the pdf.

    Sorry for all the questions.


  2. myDBR Team, Key Master


    1) For the dbr.mail.attach, there is no parameter which would allow for you to pass parameters like 'in_Plot=(inPlot)'. Instead, you need to place parameter to the URL as 'u'-parameter.

    2) Do you get images in the PDF if you just do PDF export from the same report? How are images placed in the report? Your report does not show any images. Do you use relative URL's in the images?

    3) Sounds like there is a problem in your user/export_header_pdf.php file. Copy the export_header_pdf.php file from myDBR.com. To do this, go to Server side files, click on export_header_pdf.php from myDBR.com and save it. This will give you a fresh copy. If that works, you can customize it.

    myDBR Team

  3. ajdjackson, Member


    I've updated the code as follows:
    select 'dbr.mail.attach', 'invoice.pdf', concat('http://xxx.xxx.com/mydbr/report.php?r=294&u1=',inPlot,'&m=7&h=c46701e7ab2da6c009322aea15a33a4c95ca9996&export=pdf');

    but still no further.

    Is the above correct?

    The report called above is looking for 1 parameter - CREATE PROCEDURE `sp_DBR_Print_Invoice`(inPlt_ID varchar(25))

    I'll look at your other suggestions once I've got this bit working.



  4. myDBR Team, Key Master

    Yes, that would be equivalent with the hardcoded value. Make sure that the result URL is a valid URL. You can just select it and run it in separate browser window.

    You can also try to open the corrupt PDF in text editor to see if there is an error inside.

    myDBR Team

  5. ajdjackson, Member


    Ran the url directly and got this error:

    There was an error (code:-1073741819) producing the pdf document
    Failed command: "C:\Program Files\wkhtmltopdf\bin\wkhtmltopdf.exe" -q -O Portrait -s A4 --margin-top "20" --header-spacing "8" --header-html "http://xxx.xxx.com/mydbr/user/export_header_pdf.php" --replace rdate "13/04/2016" --replace rtime "01:01:09 AM" --header-html '' C:\Windows\Temp\mydbr_export_2082264348.html -

    I replaced the export_header_pdf.php with the mydbr.com one but still got the same error.

    Without the &export=pdf the report runs as expected.



  6. myDBR Team, Key Master

    Did you update the export_header_pdf.php?

    Do you get any errors if you run the http://xxx.xxx.com/mydbr/user/export_header_pdf.php from browser?

    Use the export debug flag and run the wkhtmltopdf-command from command line to see. Replace the last '-' in the command with filename "output.pdf".

    myDBR Team

  7. ajdjackson, Member

    Hi and good evening

    You threw me a curved ball today :)

    I ran the updater to bring me up to 4.6.2 and the dbr.mail stopped working - nothing evening using debug option. I then spotted the new drop down in the environment settings. I changed the 'default' smtp to one of the others, saved and then reselected smtp. dbr.mail worked again.

    Still no pdf created though :(

    I am using the plain vanilla export header file from mydbr.com - no changes have been made to this.

    http://xxx.xxx.com/mydbr/user/export_header_pdf.php outputs the mydbr logo plus to horizontal rulers; one above and one below the logo.

    I ran the wkhtmltopdf from command line. Firstly I tested wkhtmltopdf http://google.com google.pdf and it produced a pdf of the google home page.

    I then tried it with wkhtmltopdf http://'http://xxx.xxx.com/mydbr/report.php?r=294&p1=BOWMWES_05&m=7&h=c46701e7ab2da6c009322aea15a33a4c95ca9996&i=1 output.pdf

    This failed! The error p1, m, h and i are not recognised as an internal or external command, operable program or batch file.

    I then tried replacing the & with ?. I got an output.pdf but it was a copy of the login page. I tried escaping the & with \& but it gave the error again.

    Any thoughts.


  8. myDBR Team, Key Master

    if you run:

    wkhtmltopdf http://'http://xxx.xxx.com/mydbr/report.php?r=294&p1=BOWMWES_05&m=7&h=c46701e7ab2da6c009322aea15a33a4c95ca9996&i=1 output.pdf

    wkhtmltopdf will call myDBR to run the report. As wkhtmltopdf has not logged in, myDBR will present the login screen instead of the report and wkhtmltopdf will convert that to PDF.

    What you should do instead, is to run the PDF report in question with debug option set:

    select 'dbr.export.options', 'debug', 1;

    myDBR then creates a HTML-document out of the report, places it in your tmp-directory and gives you the wkhtmltopdf-command with all the options that you can run from the command line.

    When you run the command from the command line, you may get more info what is wrong. If the command produces a PDF-document which is corrupt, try to open the document in text editor to see if it contains an error.

    myDBR Team

  9. ajdjackson, Member

    Hi good morning

    I've tried what you suggest above.

    The contents of the pdf are:

    <html><style>.term { margin-right:5px; margin-top:10px; margin-bottom:5px; padding:5px 20px 5px 20px; border:1px solid gray; display:table; background:rgb(70,70,70); color:rgb(248,248,248); } .debugtitle { font-weight: bold; text-decoration:underline; padding-bottom: 10px; } .errorLabel { color: black; font-size: 1.1em; background-color: white; border: 1px solid red; display: table; margin: 15px auto; padding: 10px; text-align: left; border-radius: 3px; } .note { padding:10px; border:1px solid gray; background-color: #EFEFEF; } </style><body><div class="debugtitle">wkhtmltopdf debug</div><div class="note">To test wkhtmltopdf pdf generation, copy the following command into the server's command line.
    myDBR saved the generated HTML file into C:\Windows\Temp\mydbr_export_1419447125.html. File size is: 8231 bytes.<div class="term">$ "C:\Program Files\wkhtmltopdf\bin\wkhtmltopdf.exe" -q -O Portrait -s A4 --margin-top "20" --header-spacing "8" --header-html "http://xxx.xxx.com/mydbr/user/export_header_pdf.php" --replace rdate "14/04/2016" --replace rtime "01:22:17 PM" --header-html '' C:\Windows\Temp\mydbr_export_1419447125.html -</div></div></body></html>

    The generated HTML file opens fine and is what was expected except for the missing logo.

    <!DOCTYPE html> <html class=" pdf"> <head> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta charset="UTF-8"> <meta name="description" content="Web reporting made easy with myDBR. myDBR is an advanced web reporting tool for MySQL, MS SQL Server and Sybase databases"> <meta name="version" content="4.6.2/2973"> <link rel="shortcut icon" href="http://xxx.xxx.com/mydbr/images/mydbr_fav.ico?v=2973" type="image/ico"> <title>Print Invoice - Hilmark MIS</title> <script type="text/javascript">mydbr_sorting = { separator: {thousand:",", decimal: "."}, dateformat: "d\/m\/Y h:i:s A", sum_prefix: "", min_prefix: "min", max_prefix: "max", count_prefix: "#", avg_prefix: "avg" }</script> <script type="text/javascript">jquery_date = "dd/mm/yy";jquery_time = "hh:mm:ss TT";</script> <link href="http://xxx.xxx.com/mydbr/lib/external/jquery/css/assets.css?v=2973" rel="stylesheet" type="text/css"> <link href="http://xxx.xxx.com/mydbr/lib/external/jquery/css/jquery-ui/jquery-ui.min.css?v=2973" rel="stylesheet" type="text/css"> <link href="http://xxx.xxx.com/mydbr/lib/external/jquery/js/select2/select2.min.css?v=2973" rel="stylesheet" type="text/css"> <link href="http://xxx.xxx.com/mydbr/interface/style.css?v=2973" rel="stylesheet" type="text/css"> <link href="http://xxx.xxx.com/mydbr/interface/normal.css?v=2973" rel="stylesheet" type="text/css"> <link href="http://xxx.xxx.com/mydbr/interface/themes/gray.css?v=2973" rel="stylesheet" type="text/css"> <link href="http://xxx.xxx.com/mydbr/interface/wkhtmltopdf.css?v=2973" rel="stylesheet" type="text/css"> <link href="http://xxx.xxx.com/mydbr/user/userstyle.css?1460629336&v=2973" rel="stylesheet" type="text/css"> <!--[if gt IE 8]> <link href="http://xxx.xxx.com/mydbr/interface/ie8ormore.css?v=2973" rel="stylesheet" type="text/css"> <![endif]--> <script type="text/javascript" src="http://xxx.xxx.com/mydbr/lib/external/js/modernizr.js?v=2973"></script> <script type="text/javascript" src="http://xxx.xxx.com/mydbr/lib/external/jquery/js/assets.js?v=2973"></script> <script type="text/javascript" src="http://xxx.xxx.com/mydbr/lib/javascript/assets.js?v=2973"></script> </head> <body> <form class="param_input param_input_pad invisible lightgray in_report_param" method="get" action="report.php"><div><input type="hidden" name="r" value="294"></div><div style="text-align: center;"><table class="center report_params report_params_294"><thead><tr class="title"><th colspan="2" class="paramTitle"></th></tr><tr class="row_header report_params_header_row"><th class="cell_header cell_header align_l">Parameter</th><th class="cell_header cell_header align_l">User input</th></tr><tr><th></th><th></th></tr></thead><tbody><tr class="param_row"><td class="input_label" style="">inPlt_ID:</td><td class="input_cell"><input style="width:25em;" class="" id="_u1" name="u1" value="BOWMEWS_05" type="text"></td></tr><tr><td colspan="2"></td></tr><tr><td class="input_label" colspan="2" style="width:200px; text-align: center;"><input type="submit" class="runreport button sub_button" value="Run report"><input type="hidden" name="m" value="7"><input type="hidden" name="h" value="c46701e7ab2da6c009322aea15a33a4c95ca9996"><input type="hidden" name="export" value="pdf"></td></tr></tbody></table></div><hr class="param"><script type="text/javascript"></script></form><div class="content report_294"> <table class="mydbr_popupframe"><tr><th class="border"><span></span></th></tr><tr><td><div class="popup_content" id="popup"></div></td></tr></table><input type="hidden" name="m" value="7"> <input type="hidden" id="db" name="db" value="mysql"> <style type="text/css">body {background:white;}</style><style type="text/css">.invheader {width:80%;}</style><style type="text/css">.invcon {width:760px;}</style><style type="text/css">.invheader {width:80%;}</style><div class="template"><style type="text/css">

    .invcon { box-sizing: border-box; padding-right:15px; padding-left:15px; margin-right:auto; margin-left:auto; width:90%;; margin-top:15px; background-color:white; overflow:hidden;


    .invheader { min-height:100px; overflow:hidden; background:#e5; margin:0 auto; width:80%;


    .invimage { -webkit-border-radius: 5px; -moz-border-radius: 5px; border-radius: 5px; padding:5px; margin: 10px 0 0 0; float: left; background-color: white; border:1px solid #; width:20%; display: inline; text-align:left;


    .invdate { float:right; width:75%; padding:5px; margin-top:20px; font-weight:bold; text-align:right; font-size:1rem; }

    .compadd { width:100%; float:left; text-align:left; margin:-5px 0 10px 2px; font-weight:bold; }

    .site { min-width:1px; clear:both; font-weight:bold; font-size:1.5rem; padding:20px; border:solid 2px; -webkit-border-radius: 5px; -moz-border-radius: 5px; border-radius: 5px; margin:0 auto; margin-top:20px; } </style><div class="invcon"> <div class="invheader"> <div class="invimage"> </div>

    <div class="invdate"> <p>Date: 14/04/2016</p> </div>

    <div class="compadd">19 XXX Road, XXX</div>

    <div class="site">5 xxx xxx, xxx. xxx xxx </div>

    </div> </div></div><div class="template"><style> .inv_rows { border: 1px solid #555; border-collapse: collapse; width:100%; padding:5px;font-size:1rem; font-weight:bold;} .inv_rows th { background-color: #E0E0E0; border-bottom: 1px solid #555 } .inv_rows th, .inv_rows td { padding: 5px } .inv_rows th.right, .inv_rows td.right { text-align: right } .inv_rows th.left, .inv_rows td.left { text-align: left }


    <div class="invcon">

    <div class="invheader">

    <table class="inv_rows">

    <thead> <tr> <th class="left" style="width: 20%">Order Date</th> <th class="left" style="width: 40%">Description</th> <th class="right" style="width: 20%">Qty</th> <th class="right" style="width: 20%">Total</th> </tr> <thead> <tr class="#alternate_row_color">

    <td class="left">08/04/2016</td> <td class="left">Sunroom</td> <td class="right">1</td> <td class="right">10,000</td> </tr> <tr class="#alternate_row_color">

    <td class="left">09/04/2016</td> <td class="left">Westcott 5 Stove</td> <td class="right">1</td> <td class="right">0</td> </tr></table> </div> </div></div><div class="template"><div class="invcon">

    <div class="invheader">

    <table class="inv_rows" style=" border-radius: 5px;-webkit-border-radius: 5px;-moz-border-radius: 5px;"> <tr>

    <td style="width: 20%"></td> <td style="width: 40%"></td> <td class="right" style="width: 20%">Total</td> <td class="right" style="width: 20%">10,000</td> </tr> <tr>

    <td style="width: 20%"></td> <td class="right" style="width: 40%"> </td> <td class="right" style="width: 20%"> </td> <td class="right" style="width: 20%"></td> </tr>


    </div> </div></div></div> <script type="text/javascript">mydbr_loc = new Array();mydbr_loc["MYDBR_AA_OK"] = 'OK';mydbr_loc["MYDBR_AA_CANCEL"] = 'Cancel';mydbr_loc["MYDBR_AA_COPY_TO_CLIPBOARD_OK"] = 'myDBR\nThe data has been copied to your clipboard.';mydbr_loc["MYDBR_IMPORT_FILE"] = 'File';mydbr_loc["MYDBR_IMPORT_IMPORT"] = 'Import';mydbr_loc["MYDBR_IMPORT_SELECT"] = 'Please select the file to be imported';mydbr_loc["MYDBR_AA_MULTILEVEL_SORT"] = 'Click here to sort.\nPress shift for a multilevel sort.';mydbr_loc["MYDBR_AA_ALL"] = 'All';</script><script type="text/javascript">jquery_date = 'dd/mm/yy'</script></body></html>

    Running from command line :

    C:\>"C:\Program Files\wkhtmltopdf\bin\wkhtmltopdf.exe" -q -O Portrait -s A4 --ma rgin-top "20" --header-spacing "8" --header-html "http://xxx.xxx.com/m ydbr/user/export_header_pdf.php" --replace rdate "14/04/2016" --replace rtime "0 1:22:17 PM" --header-html '' C:\Windows\Temp\mydbr_export_1419447125.html output .pdf QFont::setPixelSize: Pixel size <= 0 (0) QWin32PrintEngine::initialize: OpenPrinter failed (No printers were found.) QWin32PrintEngine::initialize: OpenPrinter failed (No printers were found.) Error: Failed loading page http:?page=1&section=&sitepage=1&title=Print Invoice - Hilmark MIS&subsection=&frompage=1&subsubsection=&rtime=01:22:17 PM&rdate=14/0 4/2016&topage=1&doctitle=Print Invoice - Hilmark MIS&sitepages=1&webpage=C:\Wind ows\Temp\mydbr_export_1419447125.html&time=11:39:30&date=14/04/2016 (sometimes i t will work just to ignore this error with --load-error-handling ignore) QPaintDevice: Cannot destroy paint device that is being painted

    Cheers and thanks again


  10. myDBR Team, Key Master

    the image is not shown as you are using relative path in the IMG-tag. Either use full path or better yet, embed the image using CSS.

    Secondly, your wkhtmltopdf seems to crash with error:

    QWin32PrintEngine::initialize: OpenPrinter failed (No printers were found.)

    What is the wkhtmltopdf version you have installed? Make sure you have the latest one.

    myDBR Team

  11. ajdjackson, Member


    Just upgraded and rebooted server.

    Environmental settings show - wkhtmltopdf ( (with patched qt))

    but getting the exact same error.

    I changed the report in the dbr.mail.attach to another one and it worked. So it's something in the report I'm trying to attach.

    The report is very simple - not too dissimilar from your template driven invoice one.


  12. myDBR Team, Key Master

    Try exporting the attachment report to PDF without mail. Just go to report in question, run it and export it to PDF.

    myDBR Team

  13. ajdjackson, Member


    Running directly without &export=pdf displays the invoice.

    Selecting export to pdf gives:

    There was an error (code:-1073741819) producing the pdf document
    Failed command: "C:\Program Files\wkhtmltopdf\bin\wkhtmltopdf.exe" -q --margin-top "20" --header-spacing "8" --header-html "http://xxx.xxx.com/mydbr/user/export_header_pdf.php" --replace rdate "14/04/2016" --replace rtime "04:02:40 PM" --header-html '' C:\Windows\Temp\mydbr_export_1218652426.html -

    So something in the report is crashing the export (?).


  14. myDBR Team, Key Master

    So go back one step. Enable the debug and use the command line to run the command.

    Determine first if the problem is in:
    1) The report itself (=can you export the report in PDF / do other report's PDF export work)
    2) the mail.attachment (=does the problem only happen when used in mail attachment)
    3) the export_header_pdf.php (=does the problem happen only if the header-html is not disabled)

    myDBR Team

  15. ajdjackson, Member


    That was the problem - I had select 'dbr.wkhtmltopdf', "--header-html ''"; in the invoice report.

    Once I commented that out a was able to create the attachment and mail it.

    However I've no idea where the other /*select 'dbr.wkhtmltopdf', "--header-html ''"; is.

    I also want to export without the header. So where do I put that line of code to suppress the header output?

    I've tried adding it to the report that has the dbr.mail commands but it still get outputted.



  16. myDBR Team, Key Master

    Run the myDBR updater and try with the select 'dbr.wkhtmltopdf', "--header-html ''" enabled.

    myDBR Team

  17. ajdjackson, Member


    Done that but still getting the header.

    If I put it back in the invoice report no pdf. I have it in the calling report with the dbr.mail.

    One thing what exactly do you when you mean by enabled? I just use this line select 'dbr.wkhtmltopdf', "--header-html ''"



  18. myDBR Team, Key Master

    myDBR will by default create a header for the PDF output. It does this by passing the export_header_pdf.php to the wkhtmltopdf's header-html option.

    When you include the header-html as dbr.wkhtmltopdf option, for example:

    select 'dbr.wkhtmltopdf', "--header-html ''"

    myDBR uses the passed HTML as header (in this case the header string is empty, so no header is created.

    You can use the export debug option to see the generated wkhtmltopdf-command.

    myDBR Team


You must log in to post.