KoolReport's Forum

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

Multiple databases #455

Open arthur opened this topic on on Sep 11, 2018 - 7 comments

arthur commented on Sep 11, 2018

Hi,

I have an excel file with all the sells of the company. From this file I calculate the sells per seller. I also got a MYSQL database with the goals per seller. From the total sells and the goals, I can calculate the bonus per seller. I'm trying but can't figure out how. Here is my code :

$this->src('mysql_datasource')

->query("SELECT goal FROM dataviz where seller=:seller")
->params(array(":nom"=>$_SESSION['seller']))  

->saveTo($node1);

$this->src('sales')

  ->pipe(new Filter(array(
            array("Date","=",'JUILLET'),
            'or',
           array("Date","=",'AOÛT'),
            'or',
           array("Date","=",'SEPTEMBRE'),
        )))

        ->pipe(new Filter(array(
            array("seller","=",$_SESSION['seller']),
        )))


->pipe(new CalculatedColumn(array(
            "total_sells"=>function($data){
                        return  ( $data["Marge_FAS_Déclarée"] + 12 * $data["MARGE_DELTA_REC"] ); 
            }
        )))

->pipe(new CalculatedColumn(array(
            "bonus"=>function($data){
                        return  ( $data["total_sells"] - $data["goal"] ); 
            }
        )))

->pipe($node1)

->pipe($this->dataStore("bonus_calculate"));
KoolReport commented on Sep 12, 2018

You need to use the Join process to join two tables then you can calculate. Right now, you are union-ing the table

arthur commented on Sep 12, 2018

Ok thanks.

So if do this code I join both databases but can't do function with data from both databases.

$sql_source = $this->src('mysql_datasource')->query("SELECT goal FROM dataviz where name='peter'");

$purchase_source = $this->src('sales')
 ->pipe(new Filter(array(
            array("Date","=",'JANVIER'),
        )))
->pipe(new Filter(array(
array("seller","=","peter")
        )))

->pipe(new Group(array(
    "by"=>"Date",
    "sum"=>"sells"
)));


$join = new Join($sql_source,$purchase_source,array("name"=>"seller"));

->pipe(new CalculatedColumn(array(
            "bonus"=>function($data){
                        return  ($data["sells"] - $data["goal"] ); 
            }
        )))

$join->pipe($this->dataStore('together'));

The bonus column doesn't appear on my table

KoolReport commented on Sep 13, 2018

Since you match the name from sql to seller from excel, the name should be available so please add "Select goal,name ..."

David Winterburn commented on Sep 13, 2018

Hi Arthur,

Please change your code:


$join = new Join($sql_source,$purchase_source,array("name"=>"seller"));

->pipe(new CalculatedColumn(array(
            "bonus"=>function($data){
                        return  ($data["sells"] - $data["goal"] ); 
            }
        )))
$join->pipe($this->dataStore('together'));

to:


$join = new Join($sql_source,$purchase_source,array("name"=>"seller"));

$join->pipe(new CalculatedColumn(array(
            "bonus"=>function($data){
                        return  ($data["sells"] - $data["goal"] ); 
            }
        )))
->pipe($this->dataStore('together'));

Please let us know if there's any error message other than just said a column doesn't appear. Thanks!

David Winterburn commented on Sep 13, 2018

By the way, Arthur, your "mysql_datasource" source didn't have the "name" column (as you only selected "goal"). Please add the "name" column to the select clause for the Join process to work. Thanks!

arthur commented on Sep 13, 2018

Thanks, the new calculated column seems to work. But I got a problem to display in a table. It displays 3 row, instead of one, and for the "fas" column it displays 0.

Here is my real code

$sql_source = $this->src('mysql_datasource')->query("SELECT nom, fas, rec, mobile FROM dataviz where nom='ADV'");

$purchase_source = $this->src('sales')
 ->pipe(new Filter(array(
            array("Date","=",'JANVIER'),
        )))
->pipe(new Filter(array(
array("Ccial","=","ADV")
        )))

->pipe(new Group(array(
    "by"=>"Date",
    "sum"=>"Marge_FAS_Déclarée"
)));
        
$join = new Join($sql_source,$purchase_source,array("nom"=>"Ccial"));

$join->pipe(new CalculatedColumn(array(
            "bonus"=>function($data){
                        return  ($data["Marge_FAS_Déclarée"] * $data["fas"] ); 
            }
        )))
->pipe($this->dataStore('together'));

And here is the view code

Table::create(array(

    "dataStore"=>$this->dataStore('together'),

        "columns"=>array(
            "bonus"=>array(
                "label"=>"bonus"
            ),
"fas"=>array(
                "label"=>"fas"
            ),

 "Marge_FAS_Déclarée"=>array(
                "label"=>"Marge FAS"
            ),

  
 
        ),


  "cssClass"=>array(
        "table"=>"table table-hover table-bordered"
    ),
));
David Winterburn commented on Sep 14, 2018

Hi Arthur,

Would you please tell us how many rows does the query "SELECT nom, fas, rec, mobile FROM dataviz where nom='ADV'" return?

Regarding "fas" column value, please try changing the order of sources in Join like this as the second source will override the first one:

$join = new Join($purchase_source,$sql_source,array("nom"=>"Ccial"));

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

None