KoolReport's Forum

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

Help me on multi filter with csv data source. #857

Open Hassan Fawad opened this topic on on May 8, 2019 - 39 comments
Hassan Fawad commented on May 8, 2019

How to handle the post date. here is the code

<?php

require_once "../../../../autoload.php";

use \koolreport\KoolReport;
use \koolreport\processes\Filter;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\Group;
use \koolreport\processes\Limit;



class SakilaRental extends KoolReport
{
   
	use \koolreport\inputs\Bindable;
	use \koolreport\inputs\POSTBinding;
	
	 protected function defaultParamValues()
    {
        return array(
            "portalSelect2"=>array(),
            "countrySelect2"=>array(),
            "sizeSelect2"=>array(),
        );
    }
    
    protected function bindParamsToInputs()
    {
        return array(
            
			"Creative"=>"sizeSelect2",
            "Adunit"=>"portalSelect2",
			"Country"=>"countrySelect2",
        );
    }
	
	 public function settings()
    {
        return array(
            "dataSources"=>array(
                "6months"=>array(
                    "class"=>'\koolreport\datasources\CSVDataSource',
                    'filePath'=>dirname(__FILE__)."/Video-benchmarksNEW.csv",
                )
            )
        );
    }   
	   
	   
    protected function setup()
    {
	
		
		$query_params = array();
        if($this->params["sizeSelect2"]!=array())
        {
            $query_params[":Creative"] = $this->params["sizeSelect2"];
        }
        if($this->params["portalSelect2"]!=array())
        {
            $query_params[":Adunit"] = $this->params["portalSelect2"];
        }
        if($this->params["countrySelect2"]!=array())
        {
            $query_params[":Country"] = $this->params["countrySelect2"];
        }
		
	
        $this->src('6months')
		
		
		
		
		  
       
	    ->pipe(new Group(array(
           
			"by"=>"Creative",
            "avg"=>"CTR"
        )))
		
		 ->pipe(new Filter(array(
            array("Creative","contain","v"),
			
			
			
           	
        )))
		
		
		
		
		
		
        ->pipe($this->dataStore('ctr_by_size'));
		
		
		
    } 
}

How to write the filters for 3 inputs?

David Winterburn commented on May 9, 2019

Hi Hassan,

Please explain in detail so we understand your question. Thanks!

Hassan Fawad commented on May 9, 2019

Hi,

I have set 3 filters on my report. But when I click array is coming empty. and another thing is, I want to know how to handle dynamic filters based on inputs.

3 inputs = 3 multi selects Data source: csv

Hassan Fawad commented on May 14, 2019

so, no one will help?

David Winterburn commented on May 14, 2019

Hi Hassan,

I would suggest you place the filter process before the group process like this:

->pipe(new Filter(array(
	array("Creative","contain",$query_params[":Creative"]),
	array("field2","contain",$query_params[":field2"]),
	array("field3","contain",$query_params[":field3"]),
)))
->pipe(new Group(array(
	"by"=>"Creative",
	"avg"=>"CTR"
)))

Please try this and let us know if there's any problem. Thanks!

Hassan Fawad commented on May 14, 2019

After adding below lines before group. No data is showing up. nothing in table and nothing in filter dropdowns

->pipe(new Filter(array(

array("Creative","contain",$query_params[":Creative"]),
array("Adunit","contain",$query_params[":Adunit"]),
array("Country","contain",$query_params[":Country"]),

)))

David Winterburn commented on May 14, 2019

Print out your $query_params to find out their values. Check your array data values as well. And remember by default the filter process use AND operator for multiple filters.

Hassan Fawad commented on May 14, 2019

array is coming up empty. why?

Hassan Fawad commented on May 15, 2019

hey please, I need quick help on it. but you guys are delaying

Hassan Fawad commented on May 15, 2019

below filter is not working, though array is returning value

->pipe(new Filter(array(

array("Creative","=",$query_params[":Creative"]),

)))

print_r($query_params[":Creative"]); output: Array ( [0] => 400x300v )

no data is appearing particular filter

Hassan Fawad commented on May 15, 2019

and I need to implement below if conditions for filters.

/*if($query_params[":Creative"]==array() && $query_params[":Adunit"]==array() && $query_params[":Country"]==array())

	{
	
			->pipe(new Filter(array(
	 		array("Creative","contain","v"),
			)))
	}
	
	
	if($query_params[":Creative"]!=array() && $query_params[":Adunit"]==array() && $query_params[":Country"]==array())
	{
	
			->pipe(new Filter(array(
	 		array("Creative","contain","v"),
			array("Creative","=",$query_params[":Creative"]),
			)))
	}
	
	if($query_params[":Creative"]==array() && $query_params[":Adunit"]!=array() && $query_params[":Country"]==array())
	{
	
			->pipe(new Filter(array(
	 		array("Creative","contain","v"),
			array("Adunit","=",$query_params[":Adunit"]),
			)))
	}
	
	if($query_params[":Creative"]==array() && $query_params[":Adunit"]==array() && $query_params[":Country"]!=array())
	{
	
			->pipe(new Filter(array(
	 		array("Creative","contain","v"),
			array("Country","=",$query_params[":Country"]),
			)))
	}
	
	
	if($query_params[":Creative"]!=array() && $query_params[":Adunit"]!=array() && $query_params[":Country"]!=array())
	{
	
			->pipe(new Filter(array(
	 		array("Creative","contain","v"),
			array("Adunit","=",$query_params[":Adunit"]),
			array("Country","=",$query_params[":Country"]),
			
			)))
	}*/
Hassan Fawad commented on May 15, 2019

Need to implement same logic with CSV

https://www.koolreport.com/examples/reports/advanced/multiple_data_filters/

David Winterburn commented on May 15, 2019

Looks like your param is an array instead of a scalar value:

print_r($query_params[":Creative"]); output: Array ( [0] => 400x300v )

Your data row's "Creative" element is not an array. No wonder the filter returns empty result:

array("Creative","=",$query_params[":Creative"]),

Put the param value (not an array) to the filter. Try one filter first. If it works move on to multi filters.

Hassan Fawad commented on May 15, 2019

how to put param value? I need multiselect as in your example with mysql data source

Hassan Fawad commented on May 15, 2019

do you want me to give you my csv to get better idea?

Hassan Fawad commented on May 15, 2019

here is the code of my 3 dropdowns filters

<?php

                    Select2::create(array(
                        "name"=>"portalSelect2",
                        "multiple"=>true,
                        "dataStore"=>$this->src("6months")
						
						
						
						 ->pipe(new Group(array(
       
						"by"=>"Creative",
						"by"=>"Adunit",
        
    					))),
                        "dataBind"=>"Adunit",
                        "attributes"=>array(
                            "class"=>"form-control",
                        )
                    ));
                    ?>

<?php

                    Select2::create(array(
                        "name"=>"countrySelect2",
                        "multiple"=>true,
                        "dataStore"=>$this->src("6months")
						
						
						
						 ->pipe(new Group(array(
       
						"by"=>"Creative",
						"by"=>"Country",
        
    					))),
                        "dataBind"=>"Country",
                        "attributes"=>array(
                            "class"=>"form-control",
                        )
                    ));
                    ?>

<?php

                    Select2::create(array(
                        "name"=>"sizeSelect2",
                        "multiple"=>true,
                        "dataStore"=>$this->src("6months")
						
						->pipe(new Filter(array(
						array("Creative","contain","v"), 
						)))
						
						 ->pipe(new Group(array(
       
						"by"=>"Creative",
        
    					)))
						
						,
                        "dataBind"=>"Creative",
                        "attributes"=>array(
                            "class"=>"form-control",
                        )
                    ));
                    ?>
David Winterburn commented on May 15, 2019

You have an array with one element. Don't you know how to get the only element value from your array?

Hassan Fawad commented on May 15, 2019

what do you mean by? I could have multiple values from one array. normal php code to get multiple selected values is not working with koolreport framework. check above the code I have sent to you.

Hassan Fawad commented on May 15, 2019

so 3 filters = 3 arrays. all 3 arrays could return multiple values.

Hassan Fawad commented on May 15, 2019

there?

David Winterburn commented on May 16, 2019

Hi Hassan,

In case your parameters is an array from a multiple-value select, don't use the "equals", "=" or "contains" operator in your filter. Please try the "in" operator like this:

->pipe(new Filter(array(
array("Creative","in",$query_params[":Creative"]),
array("Adunit","in",$query_params[":Adunit"]),
array("Country","in",$query_params[":Country"]),
)))

Make sure your $query_params[$key] is not an empty array first.

Hassan Fawad commented on May 16, 2019

Thanks for the reply. It worked. An other help. I want below filter by default. and then the three filters based on below filter.

array("Creative","contain","v"),

Hassan Fawad commented on May 16, 2019

and I want, if user select value from one filter, it should return. as of now to get correct data, all three filters need to select

David Winterburn commented on May 16, 2019

Not sure if I fully understand your question but make a try of this code:

$filers = array();
if (! empty($query_params[":Creative"])) {
    array_push($filters, array("Creative","in",$query_params[":Creative"]));
}
if (! empty($query_params[":Adunit"])) {
    array_push($filters, array("Adunit","in",$query_params[":Adunit"]));
}
if (! empty($query_params[":Country"])) {
    array_push($filters, array("Country","in",$query_params[":Country"]));
}

...
->pipe(new Filter(array(
    array("Creative","contain","v"),
)))
->pipe(new Filter(array(
    $filters
)))
...

Let us know if it works for your case. Thanks!

Hassan Fawad commented on May 19, 2019

Hi,

Thanks for kind help. I had manage to to solve this issue with some other solution. I have now another issue. I have added one more filter that is date picker.

Default csv contains 6 months data. so by default report shows of 6 months. I need date filter of 30, 90 and custom.

can you write the function or tell in which file I need to write? Another thing every month we will update the csv. so this date filter should automatically update with max and min date according to the date column.

can you help me on this?

Hassan Fawad commented on May 21, 2019

there?

Hassan Fawad commented on May 22, 2019

could you please reply?

David Winterburn commented on May 24, 2019

Hi Hassan,

You could get the datetime value of 30 days ago with this command:

$format = 'y-m-d g:i'; //change this format string according to your data format
$_30daysAgo = date($format, strtotime('-30 days'));

Then you could use this value to compare to your data's datetime field to filter them.

Hassan Fawad commented on May 26, 2019

Hello,

I want one column with the average. AVG(colA/colB) how can I do this?

Hassan Fawad commented on May 28, 2019

there?

David Winterburn commented on Jun 4, 2019

Hi,

Sorry for the late reply! Please try this code for your average column problem:

...
->pipe (new \koolreport\processes\Map([
    "{value}" => function($row) {
        $row["avgCol"] = $row["colA"] / (float) $row["colB"];
        return $row;
    }
]))
...

By the way, it's best to post your new question as a new topic for others to see similar problems. Thanks!

Hassan Fawad commented on Jun 9, 2019

I didn't understand. here is my code

->pipe(new CalculatedColumn(array(

			  "NEWCTR"=>array(
   					 "exp"=>"{clicks}/{impressions}*100",
    				 "type"=>"number",
					
			))))
Hassan Fawad commented on Jun 10, 2019

now again you will reply after 10 days?

Hassan Fawad commented on Jun 10, 2019

I need the (colA/ColmB)100 as group. so tell me, how to do in the below code. Like in below I want a column to show (total_clicks/total_impression)100

->pipe(new Group(array(

				"by"=>"Creative",
				
				"sum"=>array("clicks","impressions")
				
				
				
       			
   		    )))
David Winterburn commented on Jun 10, 2019

Hi Hassan,

What happens if you use this code:

->pipe(new CalculatedColumn(array(
  "NEWCTR"=>array(
		 "exp"=>"{clicks}/{impressions}*100",
		 "type"=>"number",
		
))))
->pipe(new Group(array(
	"by"=>"Creative",
	"sum"=>array("NEWCTR")
)))

If there's any error message please post them for us. Thanks!

Hassan Fawad commented on Jun 10, 2019

Just checked, This is not working. it is showing wrong data

David Winterburn commented on Jun 10, 2019

We could not help you with this kind of response. Show us with data, code and screenshots what you think is wrong.

Hassan Fawad commented on Jun 10, 2019

give me your email, I'll send you code and csv

Hassan Fawad commented on Jun 10, 2019 
<?php
require_once "../../../../autoload.php";

use \koolreport\KoolReport;
use \koolreport\processes\Filter;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\Group;
use \koolreport\processes\Limit;
use \koolreport\processes\AggregatedColumn;
use \koolreport\processes\CalculatedColumn;



class SakilaRental extends KoolReport
{
   
	use \koolreport\inputs\Bindable;
	use \koolreport\inputs\POSTBinding;
	
	 protected function defaultParamValues()
    {
        return array(
            "portalSelect2"=>array(),
            "countrySelect2"=>array(),
            "sizeSelect2"=>array(),
			"dateRange"=>array("10/1/2018","3/31/2019"),
        );
    }
    
    protected function bindParamsToInputs()
    {
        return array(
            
			"Creative"=>"sizeSelect2",
            "Adunit"=>"portalSelect2",
			"Country"=>"countrySelect2",
			"cdate"=>"dateRange",
        );
    }
	
	 public function settings()
    {
        return array(
            "dataSources"=>array(
                "6months"=>array(
                    "class"=>'\koolreport\datasources\CSVDataSource',
                    'filePath'=>dirname(__FILE__)."/Video-benchmarksNEW.csv",
                )
            )
        );
    }   
	   
	   
    protected function setup()
    {
	
		
		$query_params = array();
        if($this->params["Creative"]!=array())
        {
           //echo "am here";
		   $query_params[":Creative"] = $this->params["Creative"];
        }
        if($this->params["Adunit"]!=array())
        {
            $query_params[":Adunit"] = $this->params["Adunit"];
        }
        if($this->params["Country"]!=array())
        {
            $query_params[":Country"] = $this->params["Country"];
        }
		
		if($this->params["cdate"]!=array())
        {
			 //echo "am here";
            $query_params[":cdate"] = $this->params["cdate"];
			
			$startInput = $query_params[":cdate"][0];
			$endInput = $query_params[":cdate"][1];
 			$newStartInput = date("n/j/Y", strtotime($startInput));
			$newEndInput = date("n/j/Y", strtotime($endInput));
        }
		
	
        
		if(!isset($newStartInput) && !isset($newEndInput))
		{
			$newStartInput = "10/1/2018";
			$newEndInput = "3/31/2019";
		
		}
		
		
		
		
		if($query_params[":Creative"]==array() && $query_params[":Adunit"]==array() && $query_params[":Country"]==array())
		{
				echo "am here";
		
				 $this->src('6months')
		
		
				->pipe(new Filter(array(
		
				array("Creative","contain",'v'),
				array("cdate",">=", $newStartInput),
   				array("cdate","<=",$newEndInput),
				
		
				)))
		
		
		
				/*->pipe(new AggregatedColumn(array(
    			    "total_imps"=>array("sum","impressions"),
					"total_clicks"=>array("sum","clicks"),
				)))
				->pipe(new CalculatedColumn(array(
   					"NCTR"=>"{total_clicks}/{total_imps}"
				)))
		
				->pipe(new CalculatedColumn(array(
    			  "NCTR"=>array(
       					 "exp"=>"{clicks}/{impressions}*100",
        				 "type"=>"number",
						 "decimals"=>2,
						
    			))))*/
				
				
				->pipe(new CalculatedColumn(array(
  			"NEWCTR"=>array(
		 		"exp"=>"{clicks}/{impressions}*100",
		 "type"=>"number",
		
	))))
->pipe(new Group(array(
	"by"=>"Creative",
	"sum"=>"NEWCTR"
)))
				
				
				
				
				/*->pipe(new Group(array(
           
					
					"by"=>"Creative",
					
					"sum"=>array("clicks","impressions")
					
					
					
           			
       		    )))*/
				
				
				
				
				
				
		
				->pipe($this->dataStore('ctr_by_size'));
		}
		
		
		if($query_params[":Creative"]!=array() && $query_params[":Adunit"]==array() && $query_params[":Country"]==array())
		{
		
				print_r($query_params[":Creative"]);
				
				$this->src('6months')
		
		
				->pipe(new Filter(array(
		
				array("Creative","contain",'v'),
				array("Creative","in",$query_params[":Creative"]),
				array("cdate",">=", $newStartInput),
   				array("cdate","<=",$newEndInput),
		
				)))
		
		
				
				
				
				/*->pipe(new AggregatedColumn(array(
    			    "total_imps"=>array("sum","impressions"),
					"total_clicks"=>array("sum","clicks"),
				)))
				->pipe(new CalculatedColumn(array(
   					
					"NCTR"=>"{total_clicks}/{total_imps}"
				)))*/
		
				
		
				->pipe(new Group(array(
           
					"by"=>"Creative",
					"sum"=>"clicks",
					"sum"=>"impressions"
					
					
					
       		    )))
		
				->pipe($this->dataStore('ctr_by_size'));
				
				
				
				
		}
		
		
		
		if($query_params[":Creative"]!=array() && $query_params[":Adunit"]!=array() && $query_params[":Country"]==array())
		{
		
				
				
				$this->src('6months')
		
		
				->pipe(new Filter(array(
		
				array("Creative","contain",'v'),
				array("Creative","in",$query_params[":Creative"]),
				array("Adunit","in",$query_params[":Adunit"]),
				array("cdate",">=", $newStartInput),
   				array("cdate","<=",$newEndInput),
				)))
		
		
				->pipe(new Group(array(
           
					"by"=>"Creative",
           			"avg"=>"{clicks}/{impressions}"
       		    )))
		
				->pipe($this->dataStore('ctr_by_size'));
				
				
				
				
		}
		
		
		
		if($query_params[":Creative"]!=array() && $query_params[":Adunit"]==array() && $query_params[":Country"]!=array())
		{
		
				
				
				$this->src('6months')
		
		
				->pipe(new Filter(array(
		
				array("Creative","contain",'v'),
				array("Creative","in",$query_params[":Creative"]),
				array("Country","in",$query_params[":Country"]),
				array("cdate",">=", $newStartInput),
   				array("cdate","<=",$newEndInput),
				)))
		
		
				->pipe(new Group(array(
           
					"by"=>"Creative",
           			"avg"=>"{clicks}/{impressions}"
       		    )))
		
				->pipe($this->dataStore('ctr_by_size'));
				
				
				
				
		}
		
		
		if($query_params[":Creative"]==array() && $query_params[":Adunit"]!=array() && $query_params[":Country"]!=array())
		{
		
				
				
				$this->src('6months')
		
		
				->pipe(new Filter(array(
		
				array("Creative","contain",'v'),
				array("Adunit","in",$query_params[":Adunit"]),
				array("Country","in",$query_params[":Country"]),
				array("cdate",">=", $newStartInput),
   				array("cdate","<=",$newEndInput),
				)))
		
		
				->pipe(new Group(array(
           
					"by"=>"Creative",
           			"avg"=>"{clicks}/{impressions}"
       		    )))
		
				->pipe($this->dataStore('ctr_by_size'));
				
				
				
				
		}
		
		
		
		if($query_params[":Creative"]==array() && $query_params[":Adunit"]!=array() && $query_params[":Country"]==array())
		{
		
				
				$this->src('6months')
		
		
				->pipe(new Filter(array(
		
				array("Creative","contain",'v'),
				array("Adunit","in",$query_params[":Adunit"]),
				array("cdate",">=", $newStartInput),
   				array("cdate","<=",$newEndInput),
		
				)))
		
		
				->pipe(new Group(array(
           
					"by"=>"Creative",
           			"avg"=>"{clicks}/{impressions}"
       		    )))
		
				->pipe($this->dataStore('ctr_by_size'));
				
				
				
				
		}
		
		if($query_params[":Creative"]==array() && $query_params[":Adunit"]==array() && $query_params[":Country"]!=array())
		{
		
				
				$this->src('6months')
		
		
				->pipe(new Filter(array(
		
				array("Creative","contain",'v'),
				array("Country","in",$query_params[":Country"]),
				array("cdate",">=", $newStartInput),
   				array("cdate","<=",$newEndInput),
				)))
		
		
				->pipe(new Group(array(
           
					"by"=>"Creative",
           			"avg"=>"{clicks}/{impressions}"
       		    )))
		
				->pipe($this->dataStore('ctr_by_size'));
				
				
				
				
		}
		
		
		if($query_params[":Creative"]!=array() && $query_params[":Adunit"]!=array() && $query_params[":Country"]!=array())
		{
		
				
				$this->src('6months')
		
		
				->pipe(new Filter(array(
		
				array("Creative","contain",'v'),
				array("Creative","in",$query_params[":Creative"]),
				array("Adunit","in",$query_params[":Adunit"]),
				array("Country","in",$query_params[":Country"]),
				array("cdate",">=", $newStartInput),
   				array("cdate","<=",$newEndInput),
		
				)))
		
		
				->pipe(new Group(array(
           
					"by"=>"Creative",
           			"avg"=>"{clicks}/{impressions}"
       		    )))
		
				->pipe($this->dataStore('ctr_by_size'));
				
				
				
				
		}
		
		
		
		
		
		
		
		
		
		
		
		
		
		
    } 
}
Hassan Fawad commented on Jun 10, 2019

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
None yet
Inputs

Designed and built with by KoolPHP Inc.

Code licensed MIT.