Announcement

Collapse
No announcement yet.

Nice lookin' reports

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Nice lookin' reports

    Hi Guys,
    I guess we all agree that SC is a good piece of software but is somewhat lacking on pdf capabilities. It's good for printing boring lists but when you need a good looking estimate/invoice/menu card..., you're busted!
    Ok, here's the deal: Design your reports with iReport from jasperforge.org and use the phpjasperxml class (http://www.simit.com.my/?q=PHPJasperXML) to funnel it through tcpdf. (all those software is open source!). Best of all, you don't need any kind of report server software.
    Even though phpjasperxml doesn't utilize all the bells and whistles iReport has to offer but i'm sure you too will be impressed.
    Depending on where you live and what your special needs are, you might have to fiddle a little bit around with phpjasperxml (character encoding).
    Anyway I#m very happy with it and wanted to share. I think it's worth a try.

    Take care

  • #2
    Re: Nice lookin' reports

    Good information!

    I'm not sure where we are supposed to put the phpjasperxml class files in relation to SC ???

    thanks,
    Chris

    Comment


    • #3
      Re: Nice lookin' reports

      What's working for me is: I created a subfolder "reports" in my project folder and copy everything related to the reports into it (jrxml-files, classes, images,...), keeping the same structure as in the original sample. In SC pack the filename for the xml file and the sql command in a session variable and redirect from SC (sc_redir(../reports/index.php)). That's it and works beautifully.

      Comment


      • #4
        Re: Nice lookin' reports

        Any chance of making an example app?

        Comment


        • #5
          Re: Nice lookin' reports

          Well there is not much of an app to do but I compiled a short description the way I did it.

          Download and install iReport.
          Download and extract phpjasperxml.
          You might want to get the latest tcpdf as well ( but that's optional).
          Create a subfolder (i.e. "reports") in documentroot/scriptcase/app/yourproject/.
          Copy class-folder and sample1.php from phpjasperxml to the folder created in the previous step.
          I renamed sample1.php to index.php and edited it as nessecary.
          Mine looks like this


          <?php
          session_start();

          include_once('class/tcpdf/tcpdf.php');
          include_once("class/PHPJasperXML.inc.php");

          $xml = simplexml_load_file($_SESSION[xml_file]); //variable for jrxml file
          $PHPJasperXML = new PHPJasperXML();

          $PHPJasperXML->arrayParameter=array("pdf_query"=>$_SESSION[pdf_query]); //variable for sql statement
          $PHPJasperXML->xml_dismantle($xml);

          $PHPJasperXML->transferDBtoArray($host,$user,$pass,$db);//you have to handle db-access yourself
          $PHPJasperXML->outpage("I"); //page output method I:standard output Download file
          ?>



          Design your report and the sql-query using iReport and make sure it works as expected. As mentioned in the wiki for phpjasperxml
          don't use any fancy number or date formating in iReport, use the sql tools instead.

          The last thing you want to do in the Designer is copy the sql statement you've created and save it somewhere and replace it
          with the parameter corresponding to the index.php. (in my case it's "$P{pdf_query}").
          Save your report and copy the file to your "reports" folder of your project.

          Now we're heading over to SC.
          Chose the application you want to print from and create a new php-button and label it "Print" or "PDF" or whatever you like.

          In the code area just paste your sql statement saved earlier, set the xml filename like the sample below and fire it up.

          $pdf_query="SELECT mit_firstname, mit_name, mit_address, mit_postcode, mit_city, invoice.rch_id, rch_date,
          REPLACE(CAST(rch_total AS CHAR), '.', ','), rpos_text, REPLACE(CAST(rpos_price AS CHAR), '.', ','),
          REPLACE(CAST(rpos_total AS CHAR), '.', ','), REPLACE(CAST(rpos_vat_amount AS CHAR), '.', ',')
          FROM customer, invoice, rposition
          WHERE customer.mit_id=invoice.mit_id and rposition.rch_id=invoice.rch_id and invoice.rch_id={rch_id}";//sql statement from iReport

          $xml_file="invoice.jrxml"; //filename of report created with iReport


          sc_set_global($pdf_query);
          sc_set_global($xml_file);

          sc_redir(../reports/index.php);

          That's it.

          One thing to remember, when you deploy your project don't forget to upload your "reports" folder separately because it is not included in SC.

          If you/somebody can figure out how to include it in SC would be great. I just didn't have the time so i took this way.

          Hope this helps.


          Edit:
          If you use a library with all your functions for your application you can ease up the database access a bit by creating a function.
          May be something like this:

          function report($pdf_query,$xml_file)
          {
          if(!isset($_SESSION['report']))
          {
          $_SESSION['report']=array('host'=>[sc_glo_servidor],'user'=>[sc_glo_usuario],'pass'=>sc_decode([sc_glo_senha]),
          'db'=>[sc_glo_banco],'pdf_query'=>'','xml_file'=>'');
          }

          $_SESSION['report']['xml_file']="../reports/".$xml_file;
          $_SESSION['report']['pdf_query']=$pdf_query;

          sc_redir(../reports/index.php,"","_blank");
          }

          And don't forget to alter your index.php in the reports folder.

          <?php
          session_start();

          include_once('class/tcpdf/tcpdf.php');
          include_once("class/PHPJasperXML.inc.php");

          $xml = simplexml_load_file($_SESSION['report']['xml_file']); //variable for jrxml file

          $PHPJasperXML = new PHPJasperXML();

          $PHPJasperXML->arrayParameter=array('pdf_query'=>$_SESSION['report']['pdf_query']); //variable for sql statement
          $PHPJasperXML->xml_dismantle($xml);

          $PHPJasperXML->transferDBtoArray($_SESSION['report']['host'],
          $_SESSION['report']['user'],
          $_SESSION['report']['pass'],
          $_SESSION['report']['db']);
          $PHPJasperXML->outpage("I"); //page output method I:standard output Download file
          ?>


          Have fun
          jsb
          Last edited by jsbinca; 05-14-2013, 02:13 PM.

          Comment


          • #6
            Re: Nice lookin' reports

            you rock!!! THANK YOU!!!

            Comment


            • #7
              I follow your help but my application donīt worked well. When I try open the jrxml by the form of my scripticase project the result is only a page in white. The conection with my server is good, the parameter is passing.

              Comment


              • #8
                Hi,
                check your browser to allow pup ups for your server.

                jsb

                Comment


                • #9
                  Hi Jsb,
                  I'm trying to use SC with reports developed using JasperStudio. I did a new report there and now I would like to launch it from SC print button.
                  I've followed your nice tutorial but as not programmer I've some difficult.
                  Have you got an easy sample project to share in order to study and adapt it ?

                  For istance here wich is the right sintax ?


                  $pdf_query = "SELECT * FROM gartes";
                  $PHPJasperXML->arrayParameter=array("pdf_query"=>$_SESSION[pdf_query]); //variable for sql statement

                  ManyThanks

                  Comment


                  • #10
                    Hi, a little step forward:

                    This is my code:

                    include_once("C:/Program Files (x86)/Scriptcase/V7_008/wwwroot/scriptcase/app/c4tm/reports_jasper/PHPJasperXML/class/tcpdf/tcpdf.php");
                    include_once("C:/Program Files (x86)/Scriptcase/V7_008/wwwroot/scriptcase/app/c4tm/reports_jasper/PHPJasperXML/class/PHPJasperXML.inc.php");

                    $pdf_query = "SELECT * FROM gartes";
                    $xml_file="C:/Program Files (x86)/Scriptcase/V7_008/wwwroot/scriptcase/app/c4tm/blank_word_sc/Track_Test_001.jrxml"; //filename of report created with iReport

                    sc_set_global($pdf_query);
                    sc_set_global($xml_file);

                    $xml = simplexml_load_file([xml_file]); //variable for jrxml file
                    $PHPJasperXML = new PHPJasperXML();

                    $PHPJasperXML->arrayParameter=array("pdf_query"=>[pdf_query]); //variable for sqlstatement
                    $PHPJasperXML->xml_dismantle($xml);

                    //$PHPJasperXML->transferDBtoArray($host,$user,$pass,$db);//you have to handle db-access yourself
                    $PHPJasperXML->outpage("I"); //page output method I:standard output Download file

                    sc_redir(../reports_jasper/PHPJasperXML/index.php,"","_blank");

                    Need I to manage also transferDBtoArray line also if I have an active connection ?

                    These are a part of all the errors:
                    Errore
                    Undefined property: PHPJasperXML::$footerbandheight
                    Errore
                    Undefined property: PHPJasperXML::$columnfooterbandheight
                    Errore
                    Undefined property: PHPJasperXML::$global_pointer
                    Errore
                    Undefined property: PHPJasperXML::$arrayVariable
                    Errore
                    Invalid argument supplied for foreach()
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Undefined offset: 0
                    Errore
                    Use of undefined constant scaleImage - assumed 'scaleImage'
                    Errore
                    Undefined variable: imagetype
                    Errore
                    Undefined property: PHPJasperXML::$global_pointer
                    Errore
                    Undefined property: PHPJasperXML::$arrayVariable
                    Errore
                    Invalid argument supplied for foreach()

                    Comment


                    • #11
                      Another step forward but still errors:
                      Please could you help me on fixing them ?

                      Errore
                      Undefined property: PHPJasperXML::$footerbandheight
                      Errore
                      Undefined property: PHPJasperXML::$columnfooterbandheight
                      Errore
                      Undefined property: PHPJasperXML::$footerbandheight
                      Errore
                      Undefined property: PHPJasperXML::$columnfooterbandheight
                      Errore
                      Undefined property: PHPJasperXML::$footerbandheight
                      Errore
                      Undefined property: PHPJasperXML::$columnfooterbandheight
                      Errore
                      Undefined property: PHPJasperXML::$footerbandheight
                      Errore
                      Undefined property: PHPJasperXML::$columnfooterbandheight
                      Errore
                      Undefined property: PHPJasperXML::$footerbandheight
                      Errore
                      Undefined property: PHPJasperXML::$columnfooterbandheight
                      Errore
                      Undefined property: PHPJasperXML::$footerbandheight
                      Errore
                      Undefined property: PHPJasperXML::$columnfooterbandheight
                      Errore
                      Undefined property: PHPJasperXML::$footerbandheight
                      Errore
                      Undefined property: PHPJasperXML::$columnfooterbandheight
                      Errore
                      Undefined property: PHPJasperXML::$footerbandheight
                      Errore
                      Undefined property: PHPJasperXML::$columnfooterbandheight
                      Errore
                      Undefined variable: valign
                      Errore
                      Undefined variable: valign
                      Errore
                      Undefined variable: valign
                      Errore
                      Undefined property: PHPJasperXML::$columnfooterbandheight
                      Errore
                      Undefined property: PHPJasperXML::$columnfooterbandheight
                      Fail to connect database

                      Comment


                      • #12
                        Hi giovannini,
                        sorry for beeing late on this.

                        First of all, you should avoid absolute path names because you run into trouble when you deploy the project.

                        Let's try this:
                        Edit your index.php

                        <?php
                        session_start();

                        include_once('class/tcpdf/tcpdf.php');
                        include_once("class/PHPJasperXML.inc.php");

                        $xml = simplexml_load_file($_SESSION['report']['xml_file']); //variable for jrxml file

                        $PHPJasperXML = new PHPJasperXML();

                        $PHPJasperXML->arrayParameter=array('pdf_query'=>$_SESSION['report']['pdf_query']); //variable for sql statement
                        $PHPJasperXML->xml_dismantle($xml);

                        $PHPJasperXML->transferDBtoArray($_SESSION['report']['host'],
                        $_SESSION['report']['user'],
                        $_SESSION['report']['pass'],
                        $_SESSION['report']['db']);
                        $PHPJasperXML->outpage("I");
                        ?>


                        Edit your code area of your PHP-button (I suppose you created one).

                        if(!isset($_SESSION['report']))
                        {
                        $_SESSION['report']=array('host'=>[sc_glo_servidor],'user'=>[sc_glo_usuario],'pass'=>sc_decode([sc_glo_senha]),
                        'db'=>[sc_glo_banco],'pdf_query'=>'','xml_file'=>'');
                        }

                        $pdf_query = "SELECT * FROM gartes";
                        $xml_file="../../blank_word_sc/Track_Test_001.jrxml";

                        $_SESSION['report']['xml_file']=$xml_file;
                        $_SESSION['report']['pdf_query']=$pdf_query;

                        sc_redir(../reports_jasper/PHPJasperXML/index.php,"","_blank");

                        That should do it.
                        Good luck.

                        jsb
                        Last edited by jsbinca; 11-20-2013, 10:51 AM.

                        Comment


                        • #13
                          WOW ! Thanks for support.
                          My code is miles away from yours.... I would like to try and then I'll report my new... status ! ;-)
                          Absolute path is only for testing phase becouse I had doubts also about that...

                          Comment


                          • #14
                            Better than before but now the result is an empty PDF page

                            Great improving.... Now I don't have any error but the result is a white pdf page. ;-(

                            Attached:
                            SC button application
                            index.php

                            Jaspersoft Studio report
                            Attached Files

                            Comment


                            • #15
                              Enable popup's for the server ip.

                              jsb

                              Comment

                              Working...
                              X