KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.

Integration Problem - Jquery Select / Select2 #587

Closed Giselle Machado opened this topic on on Jan 10 - 10 comments

Giselle Machado commented on Jan 10

I'm integrating my report into a CRM of my company that use CodeIgniter. However, I have been having an issue.

After plugging it in the Ixonn CRM using all the current navigation, I had problems when I select any option of my report (select2, date range, ajax loading… etc.). The field chosen disappeared (see Figure 4, Figure 5, Figure 6, and Figure 7). In the field place appeared the symbol “<”.

Figure 4

Figure 5

Figure 6

Figure 7

I'm using subreport and Ajax loading from koolReport.

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

require "functions/getCurrentDate.php";
require "Proposals_Fields.php";
require "Proposals_CustomFields.php";
require "Proposals_GroupBy.php";
require "Proposals_Totals.php";
require "Proposals_DateRange.php";
require "Proposals_Status.php";
require "Proposals_Staff.php";
require "Proposals_Expired.php";
require "Proposals_RelType.php";
require "Proposals_Currency.php";
require "Proposals_City.php";
require "Proposals_Zip.php";
require "Proposals_State.php";
require "Proposals_Country.php";
require "Proposals_CustomFieldValue.php";
require "Proposals_List.php";
require "functions/createGroupBy.php";
require "functions/createTableTotals.php";
require "functions/setPivotTableParams.php";
require "functions/setQueryExpiredFields.php"; 
require "functions/setQueryTableFields.php";
require "functions/verifyCustomFieldName.php";

class Proposals extends \koolreport\KoolReport
{
    use \koolreport\core\SubReport;
    use \koolreport\inputs\Bindable;
    use \koolreport\clients\Bootstrap;

    function settings()
    {
        return array(
	    "assets"=>array(
                "path"=>"../../../assets/koolreport_assets",
                "url"=>"http://localhost/ixonn/10B413-crm/assets/koolreport_assets",
            ),
            "subReports"=>array(
                "Proposals_Fields"=>Proposals_Fields::class,
                "Proposals_CustomFields"=>Proposals_CustomFields::class,
                "Proposals_GroupBy"=>Proposals_GroupBy::class,
                "Proposals_Totals"=>Proposals_Totals::class,
                "Proposals_DateRange"=>Proposals_DateRange::class,
                "Proposals_Status"=>Proposals_Status::class,
                "Proposals_Staff"=>Proposals_Staff::class,
                "Proposals_Expired"=>Proposals_Expired::class,
                "Proposals_RelType"=>Proposals_RelType::class,
                "Proposals_Currency"=>Proposals_Currency::class,
                "Proposals_City"=>Proposals_City::class,
                "Proposals_Zip"=>Proposals_Zip::class,
                "Proposals_State"=>Proposals_State::class,
                "Proposals_Country"=>Proposals_Country::class,
                "Proposals_CustomFieldValue"=>Proposals_CustomFieldValue::class,
                "ProposalsReport"=>ProposalsReport::class,
                "Proposals_List"=>Proposals_List::class,
            )
        );

    }
} 

Proposals_Fields.php ***

<?php

class Proposals_Fields extends \koolreport\KoolReport
{

  use \koolreport\codeigniter\Friendship;
  use \koolreport\inputs\Bindable;

  protected function defaultParamValues()
  {
    return array(
      "MultiSelectFields"=>array(),
    );
  }

  protected function bindParamsToInputs()
  {
    return array(
      "MultiSelectFields"=>"MultiSelectFields",
    );
  }

  public function settings()
  {
    return array(
        "assets"=>array(
            "path"=>"../../../assets/koolreport_assets",
            "url"=>"http://localhost/ixonn/10B413-crm/assets/koolreport_assets",
        ),
      );
  }
 
  protected function setup()
  {
  } 
}

Proposals_Fields.view.php ***

<?php 
    use \koolreport\inputs\Select2;

    echo "<div class='form-group'><b>Field(s)</b>";
    Select2::create(array(
        "name"=>"MultiSelectFields",
        "placeholder"=>"Select Field(s)",
        "multiple"=>true,
        "data"=>array("Subject"=>"subject","Date Created"=>"datecreated","Subtotal"=>"subtotal","Total Tax"=>"total_tax","Adjustment"=>"adjustment","Discount Total"=>"discount_total","Open Till"=>"open_till","Currency"=>"currency","City"=>"city","State"=>"state","Staff"=>"staff","Zip"=>"zip","Country"=>"country","Email"=>"email","Phone"=>"phone","Rel Type"=>"rel_type","Rel Name"=>"RelName"),
        "clientEvents"=>array(
            "change"=>"function(){
                subReport.update('Proposals_Fields',{
                    MultiSelectFields:$('#MultiSelectFields').val(),
                });                     
            }",
        ),
        "attributes"=>array(
            "class"=>"form-control",
            "size"=>1,
            "includeSelectAllOption"=>true, 
        ),
        "display"=>"horizontal"
    ));
    echo "</div>";
?>
Giselle Machado commented on Jan 10

When I debugged using the chrome developer tools, I found that the select disappeared during the line 6158 of the file jquery-1.10.2.js execution.

// See if we can take a shortcut and just use innerHTML

		if ( typeof value === "string" && !rnoInnerhtml.test( value ) &&
			( jQuery.support.htmlSerialize || !rnoshimcache.test( value )  ) &&
			( jQuery.support.leadingWhitespace || !rleadingWhitespace.test( value ) ) &&
			!wrapMap[ ( rtagName.exec( value ) || ["", ""] )[1].toLowerCase() ] ) {

			value = value.replace( rxhtmlTag, "<$1></$2>" );

			try {
				for (; i < l; i++ ) {
					// Remove element nodes and prevent memory leaks
					elem = this[i] || {};
					if ( elem.nodeType === 1 ) {
						jQuery.cleanData( getAll( elem, false ) );
						elem.innerHTML = value;
					}
				}

				elem = 0;

			// If using innerHTML throws an exception, use the fallback method
			} catch(e) {}
		}
KoolReport commented on Jan 10

I know that it is on localhost but is there any chance I can see the page online.

KoolReport commented on Jan 10

Just few comments:

  1. Currently it seems you hardcode the assets url which is not good when you make system online. You will need to change it.
  2. If you use the codeingiter friendship then there is no need to set assets path & url in settings()
  3. Your jQuery seems outdated, you may update it to v3
  4. You should not use the generic name like "MultiSelectFields". do you this this name in other subreport? It is because all subreport rendering in a page, and there will be same name for multiple widgets which may cause unexpected errors.
  5. We use the subreport when we need ajax update, for example the selection of a city depends on the previous selection of country. When select country, the subreport covering city will be updated with new list of cities. I say so because I see you have hard-code the data of select2 and call the subReport to update but there is no need to update.
Giselle Machado commented on Jan 10

Hi,

Thanks for the answer. I have another issue. I removed the hardcode assets and now I'm using just the Friendship. All good with that. Also, I removed the update.

However, now, the values are not passed to the Proposal_List.php. So, it's not updated the data and the result is empty.

Proposal.php <?php require APPPATH."libraries/koolreport/autoload.php";

require "functions/getCurrentDate.php"; require "Proposals_Fields.php"; require "Proposals_CustomFields.php"; require "Proposals_GroupBy.php"; require "Proposals_Totals.php"; require "Proposals_DateRange.php"; require "Proposals_Status.php"; require "Proposals_Staff.php"; require "Proposals_Expired.php"; require "Proposals_RelType.php"; require "Proposals_Currency.php"; require "Proposals_City.php"; require "Proposals_Zip.php"; require "Proposals_State.php"; require "Proposals_Country.php"; require "Proposals_CustomFieldValue.php"; require "Proposals_List.php"; require "functions/createGroupBy.php"; require "functions/createTableTotals.php"; require "functions/setPivotTableParams.php"; require "functions/setQueryExpiredFields.php"; require "functions/setQueryTableFields.php"; require "functions/verifyCustomFieldName.php";

class Proposals extends \koolreport\KoolReport {

use \koolreport\core\SubReport;
use \koolreport\inputs\Bindable;
use \koolreport\clients\Bootstrap;
use \koolreport\codeigniter\Friendship;

function settings()
{
    return array(
    // "assets"=>array(
 //            "path"=>"../../../assets/koolreport_assets",
 //            "url"=>"http://localhost/ixonn/10B413-crm/assets/koolreport_assets",
 //        ),
        "subReports"=>array(
            "Proposals_Fields"=>Proposals_Fields::class,
            "Proposals_CustomFields"=>Proposals_CustomFields::class,
            "Proposals_GroupBy"=>Proposals_GroupBy::class,
            "Proposals_Totals"=>Proposals_Totals::class,
            "Proposals_DateRange"=>Proposals_DateRange::class,
            "Proposals_Status"=>Proposals_Status::class,
            "Proposals_Staff"=>Proposals_Staff::class,
            "Proposals_Expired"=>Proposals_Expired::class,
            "Proposals_RelType"=>Proposals_RelType::class,
            "Proposals_Currency"=>Proposals_Currency::class,
            "Proposals_City"=>Proposals_City::class,
            "Proposals_Zip"=>Proposals_Zip::class,
            "Proposals_State"=>Proposals_State::class,
            "Proposals_Country"=>Proposals_Country::class,
            "Proposals_CustomFieldValue"=>Proposals_CustomFieldValue::class,
            "ProposalsReport"=>ProposalsReport::class,
            "Proposals_List"=>Proposals_List::class,
        )
    );

}

}

Proposal.view.php <!DOCTYPE> <html>

<head>
  <title>Proposals Report</title>
</head>
<body>

    <div class="container box-container">
        <div class="text-center">
            <h1>Proposals Report</h1>
        </div>
	    <div class="row">
	        <div class="col-md-6 offset-md-2">
	        	<h4>Settings</h4>
		        <?php 
		            $this->subReport("Proposals_Fields");

// $this->subReport("Proposals_CustomFields");

		            $this->subReport("Proposals_GroupBy");
		            $this->subReport("Proposals_Totals");
			    $this->subReport("Proposals_CustomFields");
		        ?>
	        </div>
	    </div>
	    <div class="row">
	        <div class="col-md-6 offset-md-2">
	        	<h4>Filters</h4>
		        <?php 
		            $this->subReport("Proposals_DateRange");
		            $this->subReport("Proposals_Status");	
		            $this->subReport("Proposals_Staff");	
		            $this->subReport("Proposals_Expired");	
		            $this->subReport("Proposals_RelType");	
		        ?>
	        </div>
	    </div>
	    <div class="row">
        	<h4 class="text-center">Aditional Filters</h4>
	        <div class="col-md-6 offset-md-2">
		        <?php 
		            $this->subReport("Proposals_Currency");
		            $this->subReport("Proposals_City");
		            $this->subReport("Proposals_Zip");
		            $this->subReport("Proposals_State");
		        ?>
	        </div>
	        <div class="col-md-6 offset-md-2">
		        <?php
		            $this->subReport("Proposals_Country"); 			        	
		        	$this->subReport("Proposals_CustomFieldValue");
		        ?>
	        </div>
	    </div>
	    <div class="form-group text-center row">
	        <button onclick="Proposals_List()" class="btn btn-success">List Proposals</button>
	    </div>
	    <div class="row">
		        <?php 
		            $this->subReport("Proposals_List"); 
		        ?>
	    </div>

		<script type="text/javascript">
		    function Proposals_List()
		    {
		        subReport.update("Proposals_List",{
		            // "MultiSelectFields":$('#MultiSelectFields').val(),
		            // "MultiSelectCustomFields":$('#MultiSelectCustomFields').val(),
		            // "SelectFieldsGroupBy":$('#SelectFieldsGroupBy').val(),
		            // "SelectFieldTotal":$('#SelectFieldTotal').val(),
		            // "checkBoxListTotals":$('#checkBoxListTotals').val(),
		            // "dateRange_start":$('#dateRange_start').val(),
		            // "dateRange_end":$('#dateRange_end').val(),
		            // "MultiSelectStatus":$('#MultiSelectStatus').val(),
		            // "MultiSelectStaff":$('#MultiSelectStaff').val(),
		            // "SelectExpired":$('#SelectExpired').val(),
		            // "SelectRelType":$('#SelectRelType').val(),
		            // "SelectLeadClient":$('#SelectLeadClient').val(),
		            // "SelectCurrency":$('#SelectCurrency').val(),
		            // "SelectCity":$('#SelectCity').val(),
		            // "SelectState":$('#SelectState').val(),
		            // "SelectZip":$('#SelectZip').val(),
		            // "SelectCountry":$('#SelectCountry').val(),
		            "SelectCustomField":$('#SelectCustomField').val(),
		            "SelectCustomFieldValue":$('#SelectCustomFieldValue').val(),
		        });

		    }
		</script>
	</div> 
</body>

</html>

Giselle Machado commented on Jan 10

__Proposal_List.php__

<?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 Proposals_List extends \koolreport\KoolReport { use \koolreport\codeigniter\Friendship; use \koolreport\inputs\Bindable;

function settings() {

  return array(

          "dataSources"=>array(
              "groupBySelect"=>array(
                  "class"=>'\koolreport\datasources\ArrayDataSource',
                  "dataFormat"=>"associate",
                  "groupBySelect"=>array(
                      array("assigned","Staff"),
                      array("city","City"),
                      array("country","Country"),
                      array("currency","Currency"),
                      array("state","State"),
                      array("status","Status"),
                      array("zip","Zip"),
                  )
              ), 
          ),
      );

}

function setup() {

  $today = getCurrentDate();
  $currentMonth = substr($today, 0, 8);

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

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

  //setup Query to create the table based on the fields selected
  if (isset($this->params["MultiSelectFields"])){
      $fielsSelected = setQueryTableFields($this->params["MultiSelectFields"]);    
      $MultiSelectFields = $this->params["MultiSelectFields"];
  }else{
      $fielsSelected = "";
      $MultiSelectFields = "";
  }           

  if(isset($this->params["MultiSelectCustomFields"]) and !empty($this->params["MultiSelectCustomFields"])){
      $fielsSelected .= "tblcustomfieldsvalues.value as ".verifyCustomFieldName($this->params["MultiSelectCustomFields"]).", ";
      $MultiSelectCustomFields = $this->params["MultiSelectCustomFields"];
  }else{
      $MultiSelectCustomFields = "";
  }

  $expiredQuery = "";
  if(isset($this->params["SelectExpired"])){
      $expiredQuery = setQueryExpiredFields($this->params["SelectExpired"]);
  }

  $selected_rel_type = "";
  if(isset($this->params["SelectRelType"])){
    switch ($this->params["SelectRelType"]) {
          case 'All':
              $SelectRelType = null;
              break;
          case 'Client':
              $SelectRelType = 'customer';
              break;
          case 'Lead':
              $SelectRelType = 'lead';
              break;
          default:
              $SelectRelType = null;
      }
  }else{
    $SelectRelType = "";
  }

  $SelectLeadClient = array();
  $leadClientID_selected = 0;
  $leadClientType_selected = "";

  if(isset($this->params["SelectLeadClient"]) and !empty($this->params["SelectLeadClient"])){
    $SelectLeadClient = $this->params["SelectLeadClient"];
    $leadClientID_selected = $SelectLeadClient[0];
    $leadClientType_selectedn = $SelectLeadClient[1];
  }

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

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

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

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

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

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

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

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

$start = '2015-01-01'; $end = '2019-01-01'; //$SelectCurrency[0] = 3;

//Table content

$this->src('default')
->query("
   SELECT CONCAT(tbloptions.value, LPAD(tblproposals.id, (select value from tbloptions where name = 'number_padding_prefixes'), '0')) as idproposal,
          tblproposals.subject,
          CONCAT(tblcurrencies.symbol, tblproposals.total) as total,
          ".$fielsSelected."
          (CASE tblproposals.status
            WHEN 1 THEN 'Draft'
            WHEN 2 THEN 'Sent'
            WHEN 3 THEN 'Open'
            WHEN 4 THEN 'Revised'
            WHEN 5 THEN 'Declined'
            WHEN 6 THEN 'Accepted'END) as status,
          tblproposals.city as cityC,
          tblcountries.short_name as countryC,
          tblcurrencies.name as currencyC,
          tblproposals.email as emailC, 
          tblproposals.phone as phoneC,
          tblproposals.state as stateC, 
          tblstaff.firstname as staffC,
          tblproposals.zip as zipC,
          tblproposals.total as totalC,
          tblproposals.subtotal as subtotalC,
          tblproposals.total_tax as total_taxC,
          tblproposals.adjustment as adjustmentC,
          tblproposals.discount_total as discount_totalC,
          tblproposals.rel_type as rel_typeC,
          IF(rel_type='lead',
            (select tblleads.name from tblleads where tblleads.id = tblproposals.rel_id),
          IF(rel_type='customer',
            (select tblclients.company from tblclients where tblclients.userid = tblproposals.rel_id),'')) as RelNameC,
          tblproposals.status as statusId   
      FROM tblproposals 
      LEFT JOIN tblstaff ON tblstaff.staffid = tblproposals.assigned 
      LEFT JOIN tblcurrencies ON tblcurrencies.id = tblproposals.currency
      LEFT JOIN tblcountries ON tblcountries.country_id = tblproposals.country
      LEFT JOIN tblcustomfieldsvalues on tblcustomfieldsvalues.relid = tblproposals.id and fieldto = 'proposal' 
      ".
      ((isset($this->params["MultiSelectCustomFields"]) and $this->params["MultiSelectCustomFields"]!="")?"AND tblcustomfieldsvalues.fieldid = :MultiSelectCustomFields":"") 
      ."
     JOIN tbloptions on tbloptions.name = 'proposal_number_prefix'
    WHERE tblproposals.datecreated > :start 
      AND tblproposals.datecreated < :end
     ".
    (($MultiSelectStatus !="")?"AND tblproposals.status IN :MultiSelectStatus":"") 
    ."
    ".
    (($MultiSelectStaff !="")?"AND tblproposals.assigned IN :MultiSelectStaff":"")
    ."
    ".
    ((isset($this->params["SelectExpired"]) and $this->params["SelectExpired"]!="")? $expiredQuery :"")
    ."
    ".
    ((isset($this->params["SelectRelType"]) and $this->params["SelectRelType"]!="" and $SelectRelType!="")? "AND tblproposals.rel_type = :SelectRelType" :"")
    ."
    ".
    ((isset($this->params["SelectLeadClient"]) and $this->params["SelectLeadClient"]!="")?"AND tblproposals.rel_type = :SelectRelType AND tblproposals.rel_id = :SelectLeadClient":"")
    ."
    ".
    (($SelectCurrency !="")?"AND tblproposals.currency IN :SelectCurrency":"")
    ."
    ".
    ((isset($this->params["SelectCity"]) and $this->params["SelectCity"] !="")?"AND tblproposals.city IN :SelectCity":"")
    ."
    ".
    ((isset($this->params["SelectZip"]) and $this->params["SelectZip"]!="")?"AND tblproposals.zip IN :SelectZip":"")
    ."
    ".
    ((isset($this->params["SelectState"]) and $this->params["SelectState"] !="")?"AND tblproposals.state IN :SelectState":"")
    ."
    ".
    ((isset($this->params["SelectCountry"]) and $this->params["SelectCountry"] !="")?"AND tblcountries.short_name IN :SelectCountry":"")
    ."
    ".
    ((isset($this->params["SelectCustomFieldValue"]) and $this->params["SelectCustomFieldValue"]!="")?"AND tblcustomfieldsvalues.value = :SelectCustomFieldValue":"")
    ."
")

->params(array(

    ":start"=>$start,
    ":end"=>$end,
    ":MultiSelectStatus"=>$MultiSelectStatus,
    ":MultiSelectStaff"=>$MultiSelectStaff,
    ":MultiSelectCustomFields"=>$MultiSelectCustomFields,
    ":SelectRelType"=>$SelectRelType,
    ":SelectLeadClient"=>$SelectLeadClient,
    ":SelectLeadClientRelType"=>$leadClientType_selected,
    ":SelectLeadClientRelID"=>$leadClientID_selected,
    ":SelectCurrency"=>$SelectCurrency,
    ":SelectCity"=>$SelectCity,
    ":SelectZip"=>$SelectZip,
    ":SelectState"=>$SelectState,
    ":SelectCountry"=>$SelectCountry,
    ":SelectCustomFieldValue"=>$SelectCustomFieldValue,
))

->saveTo($source);

//Original select $source->pipe($this->dataStore("result"));

//verify selected fields of Group By if(isset($this->params["SelectFieldsGroupBy"]) and !empty($this->params["SelectFieldsGroupBy"])){

$groupByTotal = $this->params["SelectFieldsGroupBy"];
$groupByTotalName = ucwords(str_replace("C", "", str_replace('_', " ", $this->params["SelectFieldsGroupBy"])));

}else{

$groupByTotal = "status";
$groupByTotalName = "Status";

}

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

//verify selected fields of totals if(isset($this->params["SelectFieldTotal"]) and !empty($this->params["SelectFieldTotal"])){

$pivotTotal = $this->params["SelectFieldTotal"];
$pivotTotalName = ucwords(str_replace("C", "", str_replace('_', " ", $this->params["SelectFieldTotal"])));

}else{

$pivotTotal = "totalC";
$pivotTotalName = "Total";

}

//Pivot Table Result $source->pipe(new ColumnRename(array(

    $pivotTotal=>$pivotTotalName,
    "idproposal"=>"Proposals",
)))

->pipe(new ColumnMeta(array(

$pivotTotalName=>array(
  'type' => 'number',
  "prefix" => "$",
),

))) ->pipe(new ColumnMeta(array(

'Proposals'=>array(
  'type' => 'number',
  'decimals'=>2,
),

))) ->pipe(new Pivot(array(

'dimensions'=>array(
  'row'=>$createPivotDimensions
),
'aggregates'=>array(
  'count'=>'Proposals',
  'sum'=>'Total',
  'min'=>'Total',
  'max'=>'Total',
  'avg'=>'Total',
)

))) ->pipe($this->dataStore('result3'));

//charts Result $source ->pipe(new Group(array( "by"=>$groupByTotal, "sum"=>"totalC", "count"=>"Proposals", ))) ->pipe($this->dataStore('groupedProposalsStatus'));

//Table result $source //Remove Columns ->pipe(new RemoveColumn(array("cityC","countryC","emailC","phoneC","stateC","staffC","zipC","totalC","subtotalC","total_taxC","adjustmentC","discount_totalC","statusId","currencyC","RelNameC","rel_typeC"))) //Rename Columns ->pipe(new ColumnRename(array(

    "total"=>"Total",
    "subject"=>"Subject",
    "idproposal"=>"Proposal #",
    "status"=>"Status",
    "staff"=>"Staff",
    "datecreated"=>"Date Created",
    "total"=>"Amount",
    "total_tax"=>"Total tax",
    "adjustment"=>"Adjustment",
    "discount_total"=>"Discount",
    "subtotal"=>"Subtotal",
    "currency"=>"Currency",
    "open_till"=>"Open Till",
    "rel_type"=>"Rel Type",
    "RelName"=>"Rel Name",
    "state"=>"State",
    "zip"=>"Zip",
    "country"=>"Country",
    "city"=>"City",
    "email"=>"Email",
    "phone"=>"Phone",
)))
->pipe($this->dataStore("result2"));

} } ?>

Giselle Machado commented on Jan 10

__Proposal_List.view.php__

<?php

use \koolreport\widgets\koolphp\Table;
use \koolreport\inputs\DateRangePicker;
use \koolreport\inputs\Select;
use \koolreport\inputs\Select2;
use \koolreport\inputs\CheckBoxList;
use \koolreport\datagrid\DataTables;
use \koolreport\drilldown\MultiView;
use \koolreport\chartjs\PieChart;
use \koolreport\chartjs\ColumnChart;
use \koolreport\pivot\widgets\PivotTable;
use \koolreport\pivot\widgets\PivotMatrix; 
    

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

if($this->dataStore("result")->countData()>0){

    if (isset($this->params["checkBoxListTotals"]) and !empty($this->params["checkBoxListTotals"]))
        $checkBoxListTotals = $this->params["checkBoxListTotals"];
    else
        $checkBoxListTotals = "";

    $pivotTableField1 = "";
    $pivotTableField2 = "";
    $pivotTableField3 = "";
    $pivotTableField4 = "";
    $pivotTableField5 = "";

    //createTableTotals
    $pivotTotal = "";
    if(isset($this->params["SelectFieldTotal"]) and !empty($this->params["SelectFieldTotal"])){
        $pivotTotal = $this->params["SelectFieldTotal"];
        $pivotTotalName = ucwords(str_replace("C", "", str_replace('_', " ", $this->params["SelectFieldTotal"])));
    }else{
        $pivotTotal = "totalC";
        $pivotTotalName = "Total";
    }
    if($checkBoxListTotals){
      foreach ($checkBoxListTotals as $key1 => $value1) {
        switch($value1){
          case '1':
              $pivotTableField1 = 'Proposals - count';
              break;
          case '2':
              $pivotTableField2 = $pivotTotalName.' - sum';
              break;
          case '3':
              $pivotTableField3 = $pivotTotalName.' - min';
              break;
          case '4':
              $pivotTableField4 = $pivotTotalName.' - max';
              break;
          case '5':
              $pivotTableField5 = $pivotTotalName.' - avg';
              break;  
        }
      }
    }elseif($pivotTotalName){
        $pivotTableField1 = 'Proposals - count';
        $pivotTableField2 = $pivotTotalName.' - sum';
        $pivotTableField3 = $pivotTotalName.' - min';
        $pivotTableField4 = $pivotTotalName.' - max';
        $pivotTableField5 = $pivotTotalName.' - avg';
    }else{
        $pivotTableField1 = 'Proposals - count';
        $pivotTableField2 = $pivotTotalName.' - sum';

    }

    echo '<div class="form-group">';
    $dataStore = $this->dataStore('result3');
    PivotTable::create(array(
        'dataStore'=>$dataStore,
        'rowDimension'=>'row',
        'measures'=>array(
            $pivotTableField1,
            $pivotTableField2,
            $pivotTableField3,
            $pivotTableField4,
            $pivotTableField5,
        ),
        'rowSort' => array(
            'staff - sum' => 'desc',
        ),
        'rowCollapseLevels' => array(1),
        'totalName' => 'All',
        'width' => '100%',
        
    ));

?>
<div class='row'>
    <div class="col-md-6">
        <?php 
             MultiView::create(array(
                "name"=>"proposalsMultiView", //before was salesMultiView
                "dataSource"=>$this->dataStore('groupedProposalsStatus'),
                "title"=>"Proposals ".$pivotTotalName." by ".$groupByTotalName,
                "views"=>array(
                    array(
                        "handler"=>"<i class='fa fa-pie-chart'></i>",
                        "widget"=>array(PieChart::class,array(
                            "title"=>"",
                            "beforeLabel"=>"totalC",
                            "columns"=>array(
                                $groupByTotal=>array("label"=>$groupByTotalName,"type"=>"name"),  
                                $pivotTotal=>array("label"=>"Value of ".$pivotTotalName),                                                                    
                            ),
                            "options"=>array(
                                "tooltips"=>array(
                                    "mode"=>"index",
                                    "intersect"=>true,
                                    "afterLabel"=>"total",
                                ),
                                "legend"=>array(
                                    "position"=>"top",
                                    "labeledValueText"=>"both",
                                )
                            )
                        ))
                    ),
                    array(
                        "handler"=>"<i class='fa fa-bar-chart'></i>",
                        "widget"=>array(ColumnChart::class,array(
                            "columns"=>array(
                                $groupByTotal=>array("label"=>$groupByTotalName,"type"=>"name"),   
                                $pivotTotal=>array("label"=>"Value of ".$pivotTotalName),                                
                            ),
                            "options"=>array(
                                "tooltips"=>array(
                                    "mode"=>"index",
                                    "intersect"=>true,
                                    "afterLabel"=>"total",
                                )
                            ),
                                "legend"=>array(
                                    "position"=>"index",
                                    "labeledValueText"=>"both",
                                )                                
                        ))
                    )
                ),
            ));
        ?>
    </div>
    <div class="col-md-6">
    <?php 
        MultiView::create(array(
            "name"=>"proposalsMultiView2", //before was salesMultiView
            "dataSource"=>$this->dataStore('groupedProposalsStatus'),
            "title"=>"Proposals Amount by ".$groupByTotalName,
            "views"=>array(
                array(
                    "handler"=>"<i class='fa fa-pie-chart'></i>",
                    "widget"=>array(PieChart::class,array(
                        "title"=>"",
                        "beforeLabel"=>"total",
                        "columns"=>array(
                            $groupByTotal=>array("label"=>$groupByTotalName,"type"=>"name"),  
                            "Proposals"=>array("label"=>"Amount","type"=>"number"),
                            //"total"=>array("label"=>"Value of Total"),                                                                    
                        ),
                        "options"=>array(
                            "tooltips"=>array(
                                "mode"=>"index",
                                "intersect"=>true,
                                "afterLabel"=>"total",
                            ),
                            "legend"=>array(
                                "position"=>"top",
                                "labeledValueText"=>"both",
                            )
                        )
                    ))
                ),
                array(
                    "handler"=>"<i class='fa fa-bar-chart'></i>",
                    "widget"=>array(ColumnChart::class,array(
                        "columns"=>array(
                            $groupByTotal=>array("label"=>$groupByTotalName,"type"=>"name"),   
                            "Proposals"=>array("label"=>"Amount","type"=>"number"),                                
                        ),
                        "options"=>array(
                            "tooltips"=>array(
                                "mode"=>"index",
                                "intersect"=>true,
                                "afterLabel"=>"total",
                            )
                        ),
                            "legend"=>array(
                                "position"=>"index",
                                "labeledValueText"=>"both",
                            )                                
                    ))
                )
            ),
        ));
    ?>
    </div>
</div>
<?php
// echo "CustomFieldValue: ".$this->params["CustomFieldValue"];
// echo "<br> SelectCustomField: ".$this->params["SelectCustomField"];
// echo "<br> MultiSelectFields: <pre>";
// print_r($this->params["MultiSelectFields"]);
// echo "</pre>";

/****/ // print_r($this->params["checkBoxListTotals"]);

// print_r($this->params["MultiSelectFields"]); // echo $this->params["dateRange"][0]; /*****/

    //Fields Table Result
    DataTables::create(array( 
        "dataSource"=>$this->dataStore("result2"),
        "options"=>array(
            "colReorder"=>true,
            "orders"=>array(array(1,"desc")),
            "searching"=>true,
            "paging"=>true,
            "themeBase"=>"bs4",
        )
    ));

}else{ ?>

<div class="alert alert-warning">
    <i class="glyphicon glyphicon-info-sign"> Sorry, no orders found.</i>
</div>

<?php
} ?> <hr>

KoolReport commented on Jan 10

In the main report view the Proposals.view.php, you should have a function to update the Proposals_List report (after click the Run Report for example). This function will gather all parameters from input controls to send to Proposal_Lists report. The proposal list report receive those parameters will perform query to render table or any charts in its view.

Giselle Machado commented on Jan 12

I'm doing that... did you check it?

__Proposals_view.php__ ...

		<script type="text/javascript">
		    function Proposals_List()
		    {
		        subReport.update("Proposals_List",{
		            "Proposals_MultiSelectFields":$('#Proposals_MultiSelectFields').val(),
		            "Proposals_MultiSelectCustomFields":$('#Proposals_MultiSelectCustomFields').val(),
		            "Proposals_SelectFieldsGroupBy":$('#Proposals_SelectFieldsGroupBy').val(),
		            "Proposals_SelectFieldTotal":$('#Proposals_SelectFieldTotal').val(),
		            "Proposals_checkBoxListTotals":$('#Proposals_checkBoxListTotals').val(),
		            "Proposals_dateRange_start":$('#Proposals_dateRange_start').val(),
		            "Proposals_dateRange_end":$('#Proposals_dateRange_end').val(),
		            "Proposals_MultiSelectStatus":$('#Proposals_MultiSelectStatus').val(),
		            "Proposals_MultiSelectStaff":$('#Proposals_MultiSelectStaff').val(),
		            "Proposals_SelectExpired":$('#Proposals_SelectExpired').val(),
		            "Proposals_SelectRelType":$('#Proposals_SelectRelType').val(),
		            "Proposals_SelectLeadClient":$('#Proposals_SelectLeadClient').val(),
		            "Proposals_SelectCurrency":$('#Proposals_SelectCurrency').val(),
		            "Proposals_SelectCity":$('#Proposals_SelectCity').val(),
		            "Proposals_SelectState":$('#Proposals_SelectState').val(),
		            "Proposals_SelectZip":$('#Proposals_SelectZip').val(),
		            "Proposals_SelectCountry":$('#Proposals_SelectCountry').val(),
		            "Proposals_SelectCustomField":$('#Proposals_SelectCustomField').val(),
		            "Proposals_SelectCustomFieldValue":$('#Proposals_SelectCustomFieldValue').val(),
		        });
		    }
		</script>
Giselle Machado commented on Jan 15

I've run the code separately of my software and it's working. And, I found an Asset that is using in the Report that isn't integrated. See the Figure below.

However, this asset isn't called in the same Report when I integrated it with my software. See the Figure below.

What should I look for in this case?

Giselle Machado commented on Feb 24

Hi. I solved the problem as the following: - application/libraries/koolreport/codeigniter/Friendship.php - Changed to:

<?php

        namespace koolreport\codeigniter;

        use \koolreport\core\Utility;

        trait Friendship
        {
            public function __constructFriendship()
            {

                //assets folder
                $assets = Utility::get($this->reportSettings,"assets");
                if($assets==null)
                {
                    $document_root = Utility::getDocumentRoot();
                    $script_folder = str_replace("\\","/",realpath(dirname($_SERVER["SCRIPT_FILENAME"])));
                    $asset_path = $script_folder."/assets";
                    $asset_url = Utility::str_replace_first($document_root,"",$script_folder)."/assets";
                    // if(!is_dir($asset_path."/koolreport_assets"))
                    // {
                    //     if(!is_dir($asset_path))
                    //     {
                    //         mkdir($asset_path,0755);
                    //     }
                    //     mkdir($asset_path."/koolreport_assets",0755);
                    // }
                    $assets = array(
                        "url"=>$asset_url."/koolreport_assets",
                        "path"=>$asset_path."/koolreport_assets",
                    );
                    $this->reportSettings["assets"] = $assets;
                }
                if ( ! file_exists($file_path = APPPATH.'config/'.ENVIRONMENT.'/database.php')
                    && ! file_exists($file_path = APPPATH.'config/database.php'))
                {
                    return;
                }

                include($file_path);
                $dbSources = array();
                foreach($db as $name=>$dbconfig)
                {
                    $dbSources[$name] = array(
                        "class"=>CIDataSource::class,
                        "name"=>$name,
                    );
                }
                $dataSources = Utility::get($this->reportSettings,"dataSources",array());
                $this->reportSettings["dataSources"] = array_merge($dbSources,$dataSources);

            }
        }
  • application/libraries/koolreport/src/core/ResourceManager.php
  • Changed the publishAssetFolder method to the following:

public function publishAssetFolder($fullLocalPath,$version="")

    {
        $fullLocalPath = Utility::standardizePathSeparator($fullLocalPath);
        $fullLocalPath = Utility::getSymbolicPath($fullLocalPath);
        $assets = Utility::get($this->report->getSettings(), "assets");

        $document_root = Utility::getDocumentRoot();

        $assetUrl = "";

        if (!$assets) {
            $assetUrl = str_replace($document_root, "", $fullLocalPath);
            if ($assetUrl==$fullLocalPath) {
                /**
                 * Happens when koolreport library is not within document root
                 * setup assets settings that move to the script folder
                 */

                $script_folder = Utility::standardizePathSeparator(
                    realpath(dirname($_SERVER["SCRIPT_FILENAME"]))
                );
                $asset_path = $script_folder."/koolreport_assets";            
                $asset_url = Utility::str_replace_first(
                    $document_root,
                    "",
                    $script_folder
                )."/koolreport_assets";
                // Giselle commented the 3 lines below
                // if (!is_dir($asset_path)) {
                //     mkdir($asset_path, 0755);
                // }
                $assets = array(
                    "path"=>$asset_path,
                    "url"=>$asset_url,
                );
                $assetUrl = "";
            }
        }

        if ($assets) {
            $targetAssetPath =  Utility::get($assets, "path");
            $targetAssetUrl = Utility::get($assets, "url");
            if (!$targetAssetPath) {
                throw new \Exception("Could not find path to report's assets folder");
            }
            $reportClassFolder = Utility::standardizePathSeparator(
                dirname(Utility::getClassPath($this->report))
            );

            if (strpos($targetAssetPath, "/")!== 0
                && is_dir($reportClassFolder."/".$targetAssetPath)
            ) {
                //Check if relative targetAssetPath existed
                $targetAssetPath = Utility::standardizePathSeparator(
                    realpath($reportClassFolder."/".$targetAssetPath)
                );
            } else if (is_dir($targetAssetPath)) {
                //Check if full targetAssetPath existed
                $targetAssetPath = Utility::standardizePathSeparator(
                    realpath($targetAssetPath)
                );
            } else {
                throw new \Exception("Report's assets folder not existed");
            }
            //-----------------------

            $objectFolderName = str_replace(
                dirname($fullLocalPath)."/",
                "",
                $fullLocalPath
            );

            $objectHashFolderName = crc32(
                "koolreport"
                .$fullLocalPath
                .@filemtime($fullLocalPath)
                .$this->report->version().$version
            );
            $objectHashFolderName = ($objectHashFolderName<0)
                ?abs($objectHashFolderName)."0"
                :"$objectHashFolderName";
            //-------------------------

            // Giselle removed the $objectHashFolderName
            // $objectTargetPath = $targetAssetPath."/".$objectHashFolderName;
            $objectTargetPath = $targetAssetPath;

            if (!is_dir($objectTargetPath)) {
                Utility::recurse_copy($fullLocalPath, $objectTargetPath);
            } else {
                //Do the check if file in widgetSourceAssetPath is changed,
                //If there is then copy again.
                //Currently do nothing for now
            }

            if ($targetAssetUrl) {
                // Giselle removed the $objectHashFolderName
                // $assetUrl = $targetAssetUrl."/".$objectHashFolderName;
                $assetUrl = $targetAssetUrl;
            } else {
                $assetUrl = str_replace($document_root, "", $objectTargetPath);
            }
        }

        return $assetUrl;
    }

Give tips to supporter for his good work

If you feel that supporter has done a good work, consider giving him some credit. Any amount put into the tips box below is appreciated. By doing so, you have contribtuted to the existence of KoolReport and the quality of support.

Tips box
None yet

None