KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Export PDF corrupted file - SubReport #706

Closed Giselle Machado opened this topic on on Feb 24, 2019 - 10 comments

Giselle Machado commented on Feb 24, 2019

I'm using subReport in my report and when I try to export to PDF the file is corrupted. I created a different Class just to check the EXPORT PDF function and it is working (MyPage.php). The problem is online when I use SubReport (Leads_List.php). I've tried the solution of the 'https://www.koolreport.com/forum/topics/624', but it is not working too. Can some one help me?

public function leads_report_plus_export()
{
    // require APPPATH."/reports/leads/MyPage.php";
    // $mypage = new MyPage;
    require APPPATH."/reports/leads/Leads_List.php";
    $mypage = new Leads_List;
    // $mypage->run()->render();
    $mypage->export('Leads_List')
    ->pdf(array(
        "format"=>"A4",
        "orientation"=>"portrait",
    ))
    ->toBrowser("mypage.pdf");
}
Giselle Machado commented on Feb 24, 2019

MyPage.php

<?php

require APPPATH."libraries/koolreport/autoload.php";

class MyPage extends \koolreport\KoolReport
{
    use \koolreport\export\Exportable;
}
Giselle Machado commented on Feb 24, 2019

Leads.php

<script src="<?php echo base_url('assets/plugins/jquery/jquery.js'); ?>"></script>
<script src="<?php echo base_url('assets/plugins/bootstrap/js/bootstrap.js'); ?>"></script>
<link rel="stylesheet" href="<?php echo base_url('assets/plugins/font-awesome/css/font-awesome.min.css'); ?>">

<script>
$(document).ready(function(){
  $('[data-toggle="tooltip"]').tooltip(); 
});
</script>
<?php

require APPPATH."libraries/koolreport/autoload.php";
require APPPATH."libraries/koolreport/functions/getCurrentDate.php";
require APPPATH."libraries/koolreport/functions/Leads_createGroupBy.php";
require APPPATH."libraries/koolreport/functions/createTableTotals.php";
require APPPATH."libraries/koolreport/functions/setPivotTableParams.php";
require APPPATH."libraries/koolreport/functions/setQueryExpiredFields.php";
require APPPATH."libraries/koolreport/functions/Leads_setQueryTableFields.php";
require APPPATH."libraries/koolreport/functions/verifyCustomFieldName.php";

require "Leads_Fields.php";
require "Leads_CustomFields.php";
require "Leads_CustomFieldValue.php";
require "Leads_GroupBy.php";
require "Leads_Totals.php"; 
require "Leads_DateRange.php";
require "Leads_Status.php";
require "Leads_Staff.php";
require "Leads_Country.php";
require "Leads_MaxAge.php"; 
require "Leads_Source.php";
require "Leads_State.php";
require "Leads_MaxStatusAge.php";
require "Leads_Zip.php";
require "Leads_City.php";
require "Leads_List.php";
require "Leads_Language.php";


class Leads extends \koolreport\KoolReport
{
    use \koolreport\codeigniter\Friendship;
    use \koolreport\core\SubReport;
    use \koolreport\inputs\Bindable;
    use \koolreport\clients\Bootstrap;
    use \koolreport\export\Exportable;

    function settings()
    {
        return array(
            "subReports"=>array(
                "Leads_Fields"=>Leads_Fields::class,
                "Leads_CustomFields"=>Leads_CustomFields::class,
                "Leads_CustomFieldValue"=>Leads_CustomFieldValue::class,
                "Leads_GroupBy"=>Leads_GroupBy::class,
                "Leads_Totals"=>Leads_Totals::class,
                "Leads_DateRange"=>Leads_DateRange::class,
                "Leads_Status"=>Leads_Status::class,
                "Leads_Staff"=>Leads_Staff::class,
                "Leads_Country"=>Leads_Country::class,
                "Leads_MaxAge"=>Leads_MaxAge::class,
                "Leads_Source"=>Leads_Source::class,
                "Leads_State"=>Leads_State::class,
                "Leads_MaxStatusAge"=>Leads_MaxStatusAge::class,
                "Leads_Zip"=>Leads_Zip::class,
                "Leads_City"=>Leads_City::class,
                "Leads_List"=>Leads_List::class,
                "Leads_Language"=>Leads_Language::class,
                "Leads_MaxAge"=>Leads_MaxAge::class,
            )
        );

    }
}
Giselle Machado commented on Feb 24, 2019

Leads_List.php


<?php

require APPPATH."libraries/koolreport/autoload.php";

if (!function_exists('getCurrentDate')){
  require APPPATH."libraries/koolreport/functions/getCurrentDate.php";
}

if (!function_exists('Leads_createGroupBy')){
  require APPPATH."libraries/koolreport/functions/Leads_createGroupBy.php";
}

use \koolreport\processes\Sort;
use \koolreport\processes\Group;
use \koolreport\processes\Limit;
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\RemoveColumn;
use \koolreport\processes\ColumnRename;
use \koolreport\pivot\processes\Pivot;
 
class Leads_List extends \koolreport\KoolReport
{ 
  use \koolreport\codeigniter\Friendship;
  use \koolreport\inputs\Bindable;
  use \koolreport\export\Exportable;  

  function settings()
  {
    return array(
      "dataSources"=>array(
        "groupBySelect"=>array(
          "class"=>'\koolreport\datasources\ArrayDataSource',
          "dataFormat"=>"associate",
          "groupBySelect"=>array(
            array("Status","statusC"),
            array("Source","sourceC"),
            array("Company","companyC"),
            array("City","cityC"),
            array("Country","countryC"),
            array("Language","languageC"),
            array("State","stateC"),
            array("Staff","staffC"),
            array("Zip","zipC"),
          )
        ),
      ),
    );
  }
  function setup()
  {
    //setup Query to create the table based on the fields selected
    if (isset($this->params["Leads_MultiSelectFields"])){
        $fielsSelected = Leads_setQueryTableFields($this->params["Leads_MultiSelectFields"]);
        $MultiSelectFields = $this->params["Leads_MultiSelectFields"];
    }else{
        $fielsSelected = "
        , tblleads.phonenumber as phone
        , datediff(current_date(), tblleads.dateadded) as age
        , datediff(current_date(), tblleads.last_status_change) as statusage
         ";
        $MultiSelectFields = "";
    }


    if(isset($this->params["Leads_SelectCustomField"]) and !empty($this->params["Leads_SelectCustomField"])){
      $fielsSelected .= ", tblcustomfieldsvalues.value as ".verifyCustomFieldName($this->params["Leads_SelectCustomField"]);
      $Leads_SelectCustomField = $this->params["Leads_SelectCustomField"];
    }else{
      $Leads_SelectCustomField = "";
    }
    
    if(isset($this->params["Leads_SelectCustomFields"]) and !empty($this->params["Leads_SelectCustomFields"])){
      $fielsSelected .= ", tblcustomfieldsvalues.value as ".verifyCustomFieldName($this->params["Leads_SelectCustomFields"]);
    } 
    
    $today = getCurrentDate();
    $currentMonth = substr($today, 0, 8);

    if(isset($this->params["Leads_dateRange_start"])){
        $start = substr($this->params["Leads_dateRange_start"], 0, 10);
    }else{
        $start = $currentMonth."01";
        $end = $today;
    }

    if(isset($this->params["Leads_dateRange_end"])){
        $end = substr($this->params["Leads_dateRange_end"], 0, 10);
    }else{
        $start = $currentMonth."01";
        $end = $today;
    }
    if(isset($this->params["Leads_SelectCity"])){
        $SelectCity = $this->params["Leads_SelectCity"];
    }else{
        $SelectCity = "";
    }
    if(isset($this->params["Leads_SelectLanguage"])){
        $SelectLanguage = $this->params["Leads_SelectLanguage"];
    }else{
        $SelectLanguage = "";
    }
    if(isset($this->params["Leads_SelectMaxAge"])){
        $SelectMaxAge = $this->params["Leads_SelectMaxAge"];
    }else{
        $SelectMaxAge = "";
    }
    if(isset($this->params["Leads_SelectMaxStatusAge"])){
        $SelectMaxStatusAge = $this->params["Leads_SelectMaxStatusAge"];
    }else{
        $SelectMaxStatusAge = "";
    }
    if(isset($this->params["Leads_SelectZip"])){
        $SelectZip = $this->params["Leads_SelectZip"];
    }else{
        $SelectZip = "";
    }

    if(isset($this->params["Leads_SelectState"])){
        $SelectState = $this->params["Leads_SelectState"];
    }else{
        $SelectState = "";
    }

    if(isset($this->params["Leads_SelectState"])){
        $SelectState = $this->params["Leads_SelectState"];
    }else{
        $SelectState = "";
    }
 
    if(isset($this->params["Leads_SelectCountry"])){
        $SelectCountry = $this->params["Leads_SelectCountry"];
    }else{
        $SelectCountry = "";
    }

    if(isset($this->params["Leads_SelectCustomFieldValue"])){
        $Leads_SelectCustomFieldValue = $this->params["Leads_SelectCustomFieldValue"];
    }else{
        $Leads_SelectCustomFieldValue = "";
    }

    if(isset($this->params["Leads_MultiSelectStaff"])){
        $MultiSelectStaff = $this->params["Leads_MultiSelectStaff"];
    }else{
        $MultiSelectStaff = "";
    }

    if(isset($this->params["Leads_MultiSelectStatus"])){
        $MultiSelectStatus = $this->params["Leads_MultiSelectStatus"];
    }else{
        $MultiSelectStatus = "";
    }

    if(isset($this->params["Leads_MultiSelectSource"])){
        $SelectSource = $this->params["Leads_MultiSelectSource"];
    }else{
        $SelectSource = "";
    }
// $start = '2015-01-01';
// $end = '2019-01-01';

   //Table content
    $this->src('default')
    ->query("SELECT tblleads.name as leadsC
                  , tblleads.email as emailC
                  , tblleads.phonenumber as phoneC
                  , tblleadsstatus.name as statusC
                  , tblleadsstatus.id as statusIDC
                  , tblleads.lastcontact as lastcontactC
                  , tblleads.is_public as publicC
                  , tblstaff.firstname as staffC
                  , datediff(current_date(), tblleads.dateadded) as ageC
                  , datediff(current_date(), tblleads.last_status_change) as statusageC
                  , CAST(tblcustomfieldsvalues.value as SIGNED) as customfieldvalueC
                  , tblcustomfieldsvalues.fieldid as customfieldidC
                  , tblleadssources.name as sourceC
                  , tblleads.company as companyC
                  , tblleads.zip as zipC
                  , tblleads.state as stateC
                  , tblleads.city as cityC
                  , tblcountries.short_name as countryC
                  , tblleads.default_language as languageC
                  ".$fielsSelected." 
               FROM tblleads
               LEFT JOIN tblstaff 
                      ON tblstaff.staffid = tblleads.assigned
               LEFT JOIN tblcountries 
                      ON tblcountries.country_id = tblleads.country
               LEFT JOIN tblleadsstatus 
                      ON tblleadsstatus.id = tblleads.status
               LEFT JOIN tblleadssources 
                      ON tblleadssources.id = tblleads.source
               LEFT JOIN tblcustomfieldsvalues on tblcustomfieldsvalues.relid = tblleads.id and fieldto = 'leads' 
      WHERE tblleads.dateadded >= :start 
        AND tblleads.dateadded <= :end
         ".
        (($MultiSelectStatus != "")?"AND tblleadsstatus.id IN :MultiSelectStatus":"")
        ."
        ".
        (($MultiSelectStaff != "")?"AND tblleads.assigned IN :MultiSelectStaff":"")
        ."
        ".
        (($SelectCity !="")?"AND tblleads.city IN :SelectCity":"")
        ."
        ".
        (($SelectZip !="")?"AND tblleads.zip IN :SelectZip":"")
        ."
        ".
        (($SelectState != "")?"AND tblleads.state IN :SelectState":"")
        ."
        ".
        (($SelectCountry !="")?"AND tblcountries.short_name IN :SelectCountry":"")
        ."
        ".
        (($SelectSource !="")?"AND tblleadssources.id IN :SelectSource":"")
        ."
        ".
        (($SelectLanguage !="")?"AND tblleads.default_language IN :SelectLanguage":"")
        ."
        ".
        (($SelectMaxAge !="")?"AND datediff(current_date(), tblleads.dateadded) >= :SelectMaxAge":"")
        ."
        ".
        (($SelectMaxStatusAge !="")?"AND datediff(current_date(), tblleads.last_status_change) >= :SelectMaxStatusAge":"")
        ."
        ".
        ((isset($this->params["Leads_SelectCustomField"]) and $this->params["Leads_SelectCustomField"]!="")?"AND tblcustomfieldsvalues.fieldid = :Leads_SelectCustomField":"")
        ."
         ".
        ((isset($this->params["Leads_SelectCustomFieldValue"]) and $this->params["Leads_SelectCustomFieldValue"]!="")?"AND tblcustomfieldsvalues.value = :Leads_SelectCustomFieldValue":"")
        ."
        group by tblleads.id
    ")
   ->params(array(
        ":start"=>$start,
        ":end"=>$end,
        ":MultiSelectStatus"=>$MultiSelectStatus,
        ":MultiSelectStaff"=>$MultiSelectStaff,
        ":SelectCity"=>$SelectCity,
        ":SelectZip"=>$SelectZip,
        ":SelectState"=>$SelectState,
        ":SelectCountry"=>$SelectCountry,
        ":Leads_SelectCustomFieldValue"=>$Leads_SelectCustomFieldValue,
        ":Leads_SelectCustomField"=>$Leads_SelectCustomField,
        ":SelectSource"=>$SelectSource,
        ":SelectLanguage"=>$SelectLanguage,
        ":SelectMaxAge"=>(int)$SelectMaxAge,
        ":SelectMaxStatusAge"=>(int)$SelectMaxStatusAge,
    ))
  ->saveTo($source);
  $source->pipe($this->dataStore("result1")); 

  //verify selected fields of Group By
  if(isset($this->params["Leads_SelectFieldsGroupBy"]) and !empty($this->params["Leads_SelectFieldsGroupBy"])){
    $groupByTotal = $this->params["Leads_SelectFieldsGroupBy"];
    $groupByTotalName = ucwords(str_replace("C", "", str_replace('_', " ", $this->params["Leads_SelectFieldsGroupBy"])));
  }else{
    $groupByTotal = "statusC";
    $groupByTotalName = "Status";
  }

  //verify GroupBySelection
  $createPivotDimensions = Leads_createGroupBy($groupByTotal);

  $pivotTotal = "customfieldvalueC";
  
  if(isset($this->params["Leads_SelectCustomFields"]) and !empty($this->params["Leads_SelectCustomFields"] !="")){ 
    $pivotTotalName = verifyCustomFieldName($this->params["Leads_SelectCustomFields"]);
  }else{
    //take the last custom field created
    $pivotTotalName = "customfieldvalueAll";
  }  

  $source->pipe(new ColumnRename(array(
        $pivotTotal=>$pivotTotalName,
        "leadsC"=>"Leads",
    )))
  ->pipe(new ColumnMeta(array(
    'Leads'=>array(
      'type' => 'number',
      'decimals'=>2,
    ),
  )))
  ->pipe(new Pivot(array(
    'dimensions'=>array(
      'row'=>$createPivotDimensions
      // 'row'=>"statusC, staffC, Leads"
    ),
    'aggregates'=>array(
      'count'=>'Leads',
      'sum'=>$pivotTotalName, 
      'min'=>$pivotTotalName,
      'max'=>$pivotTotalName,
      'avg'=>$pivotTotalName,
    )
  )))
  ->pipe($this->dataStore('result2'));

  //charts Result
  $source
  ->pipe(new ColumnRename(array(
    "leadsC"=>"Leads",
  ))) 
  ->pipe(new Group(array(
  "by"=>$groupByTotal,
  "sum"=>$pivotTotal,
  "count"=>"Leads",
  )))
  ->pipe($this->dataStore('groupedLeadsStatus'));

  //Table result
  $source
   //Remove Columns
   ->pipe(new RemoveColumn(array("phoneC","statusIDC","publicC","ageC","zipC","statusageC","customfieldvalueC","customfieldidC","sourceC","companyC","stateC","cityC","countryC","languageC")))
  //Rename Columns
   // echo ' <i class="fa fa-exclamation-circle" aria-hidden="true" data-toggle="tooltip" data-title="Leads Name Exclamation" data-original-title="" title=""></i>';
   ->pipe(new ColumnRename(array(
        "leadsC"=>_l('lead_add_edit_name'),
        "emailC"=>"Email",
        "phone"=>_l('lead_add_edit_phonenumber'),
        "statusC"=>"Status",
        // "age"=>'<i class="fa fa-exclamation-circle" aria-hidden="true" data-toggle="tooltip" data-title="Leads Age in days Exclamation" data-original-title="" title=""></i>Age (days)',
        "age"=>_l('kr_leads_age'),
        "statusage"=>_l('kr_leads_status_age'),
        "lastcontactC"=>_l('leads_dt_last_contact'),
        "staffC"=>'Staff',
        "staff"=>_l('staff_members'),
        "datecreated"=>"Date Created",
        "state"=>_l('state'),
        "zip"=>_l('zip'),
        "country"=>_l('country'),
        "city"=>_l('city'),
        "email"=>_l('email'),
    )))
    ->pipe($this->dataStore("result3"));

  }
}
?>



Andrew Borell commented on Feb 24, 2019

The link in your post is to an issue with character encoding. Its not clear if that's your issue. Maybe I can help you work around the problem? I wrote some code for an API to raster pages as pdf with phantomjs that I will share with you to work with until you identify the root cause.

I adapted my original source to your situation, but I do not support or warranty this code whatsoever. In fact I didnt even bother testing it but you should know pretty quick if there are any issues.

Hope it helps!

<?php 

require APPPATH."/reports/leads/Leads_List.php";

ob_start();
$mypage = new Leads_List;
$mypage->run()->render();
$myData = ob_get_contents();
ob_end_clean();

$myDoc = new makeDoc($myData,'PDF');
// now do whatever with this object.  
  

						
class makeDoc { 
	
	public function __construct($data,$ext){
		// this is configured a4, portrait. Add those options as parameters as you deem necessary. Pass them along to the command. 
		$this->_file_doc = '';
		$this->_file_ext = $ext;
		
		$guid = str_replace('{','',str_replace('}','',SELF::_create_guid()));
		$this->_input_file_name = $guid.'.HTML';
		// this directory must exist, with the phantomjs and dependencies.
		$this->_file_path = 'c:\\bin\\';
		
		if(!empty($data)){
			file_put_contents($this->_file_path.$this->_input_file_name, $data);
		}
		
		// left this open to raster other formats down the road. 
		if(strToUpper($ext)==='PDF'){ 
			$this->_output_file_name = $guid.'.PDF';
			if(file_exists($this->_file_path.$this->_output_file_name)){
				$this->_file_doc = SELF::_do_doc();		
				}
			}
		}

	 protected function _get_doc_cmd(){
		return '"'.$this->_file_path.'phantomjs" "'.$this->_file_path.'rasterize.js" "'.$this->_file_path.$this->_input_file_name.'" "'.$this->_file_path.$this->_output_file_name.'" A4';
		}
		
	 protected function _do_doc(){ 
		$tmp = '';
		$s = SELF::_get_doc_cmd();
		exec($s);
		if(file_exists($this->_file_path.$this->_output_file_name)){
			// remove base64_encode if you are not trying to ajaxify or api this data.  
			$tmp = base64_encode(file_get_contents($this->_file_path.$this->_output_file_name));
			// clean up our mess
			unlink($this->_file_path.$this->_output_file_name);
			unlink($this->_file_path.$this->_input_file_name);
			}
		return $tmp;
		}

	private function _create_guid(){
		if (function_exists('com_create_guid')){
			return com_create_guid();
		}
		else {
			mt_srand((double)microtime()*10000);
			$charid = strtoupper(md5(uniqid(rand(), true)));
			$hyphen = chr(45);
			$uuid = chr(123)
				.substr($charid, 0, 8).$hyphen
				.substr($charid, 8, 4).$hyphen
				.substr($charid,12, 4).$hyphen
				.substr($charid,16, 4).$hyphen
				.substr($charid,20,12)
				.chr(125);
			return $uuid;
		}
	}
} 
Murilo ZIlli commented on Feb 24, 2019

Im having the same problem. Apparently it is related to subreports.

Sorry Andrew but your code doesnt work whatsoever. I fixed several variables that I assumed were misplaced like the variable you use to call phantomjs binary is the same you use to save your file and in your example is c:/bin. But some stuff I couldnt figure it out like what is rasterize.js? I found this package https://github.com/modulesio/rasterize#readme but I have no idea if it is what you are using as it is quite small.

Andrew Borell commented on Feb 24, 2019

I assure you it works in my own code, however I had originally coded it to get the data by URL instead of writing an html file to disk. Yes, you need the phantomjs.exe and rasterize.js flies in the bin directory.

http://phantomjs.org/download.html

The rasterize.js file is in the examples directory when you extract. Just copy the exe and rasterize.js to c:\bin or wherever you configure it

Giselle Machado commented on Feb 27, 2019

Thanks Andrew! Your code worked for me. The problem in my code is the two times render() in the Leads_List. So, I need a way to use the pdf() and the exportToExcel() without having to render it to export the excel or print. Something like this:

.
.
.

<button onclick="

<?php 

$this->export('Leads_List')
->pdf(array("format"=>"A4","orientation"=>"portrait",))
->toBrowser("Leads_List.pdf"); 

?> 
" class="btn btn-success">This Print PDF
</button>
Andrew Borell commented on Feb 27, 2019

Why not just send it in your form data and choose how you want it returned before executing the method to return the data?

// LeadsListIndex.php 
	// get form data
	$form_data = json_decode(file_get_contents("php://input"));
	
	include 'LeadsList.php';
	$leads_list = new LeadsList($form_data);
	$req_doctype = isset($form_data->doctype) ? $form_data->doctype : '';
	
	switch (strToUpper($req_doctype)){
		case "PDF" :
			$leads_list->run()->export()->pdf(array(
				"format"=>"A4",
				"orientation"=>"portrait"
				))->toBrowser("leads_list.pdf");
			break; 
		case "XLSX" :
			$leads_list->run()->exportToExcel()->toBrowser("leads_list.xlsx");
			break; 
		default: 
			$leads_list->run()->render();
			break; 
		}


//LeadsList.php
	function settings(){
			return array(
			 "dataSources"=>array(
				"my_datasource"=>array(
					'host' => 'ip',
					'username' => 'user',
					'password' => 'pass',
					'dbname' => 'dbname',
					'class' => '\koolreport\datasources\MySQLDataSource' 
					),
				),
			);
		}
		
	function setup(){
		$params = array(); 
		$lead_status = ''; 
		
		if(isset($form_data->lead_status)){
			$lead_status = 'and lt.lead_status = :lead_status'
			$params[':lead_status'] = $form_data->lead_status;
			}

	$this->src('report')->query(
		"select lead_id, 
				lead_status,
				lead_first_name, 
				lead_last_name,
				lead_email,
			from lead_table lt  
			where lt.lead_id is not null 
				{$lead_status}
			;"
			)
			->params(
				$params
			)
		->pipe($this->dataStore("lead_list"))->data();

//LeadsList.view.php 
	// add some form(s) and buttons here to make the request for the "doctype"
	use \koolreport\datagrid\DataTables;
	DataTables::create(array(
		"dataSource"=>$this->dataStore('lead_list')
			,"name"=>"leads_list"
			,"columns"=>array(
				"lead_id"=>array(
					"label"=>"Lead ID",
					)
				,""=>array(
					"label"=>"Name",
					"formatValue"=>function($value,$row){
							return ucwords(strToLower($row['lead_last_name'])) . ', ' . ucwords(strToLower($row['lead_first_name']));
						}
					)
				,"lead_email"=>array(
					"label"=>"Email",
					)
					
				)
			,"options"=>array(
				"paging"=>true,
				"searching"=>true,
				"fixedHeader"=>true,
				)
		));
Andrew Borell commented on Feb 27, 2019

My only concern is if the request headers match the data you are trying to return. If you are expecting PDF but the content type is specified incorrectly in the headers it may be what what causing your original issue perhaps.

Giselle Machado commented on Mar 6, 2019

This solution didn't work for me because I'm using SubReport. So, I need to pass the data to export without to recreate the data (without redo this $leads_list = new LeadsList($form_data)).

The SubReport update my form result using AJAX. After that, I need to call the export function and pass the data.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
solved

Export