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"));

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

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"
    ),
));