Hi,

We are making a business challenge with our sales team. We have : - 2 kinds of customers : new and old. - 2 kinds of sales people : commercial and ACC - 2 importants values : marge and recurrent

So we would calculate the value like this :

If the customer is new : marge + 12 recurrent if the customer is old : (marge + 12 recurrent) * 1,1

And to finish, if the sales is made by an ACC, here is the equation 2,5(marge + 12 recurrent) * 1,1

So here is a part of my function (which is working) :

//
        // CALCUL PO
        //
        $this->src('sales')
        ->pipe(new CalculatedColumn(array(
            "PO"=>function($data){
                if($data["Marge"]!=null && $data["Recurrent"]!=null )
                {

                    if ($data["Customer"]=='old' ){
                        return 1.1 * ($data["Marge"] + 12 * $data["Recurrent"]); 
                    }
                    else { return ($data["Marge"] + 12 * $data["Recurrent"]); }

                }
                else
                {
                    return 0;
                }
            }
        )))

        ->pipe(new Group(array(
            "by"=>"commercial",
            "sum"=>"PO",
        )))
        ->pipe(new Sort(array(
            "PO"=>"desc"
        )))
        ->pipe($this->dataStore("PO"));

My problem is that I just group with commercial, and dont take ACC column. So how could I create a new column that mix both, and then add another if to check if its an ACC ?

Thanks !!

Hi,

I would like 1 column, which would the "Rate" for the challenge. But to calculate this, we would need to check if is a commercial and ACC for the first part of the equation, then if it's a new custumer to add a "bonus" in the equation. Am I clear enough ?

So the full equation would be :

->pipe(new CalculatedColumn(array(
            "PO"=>function($data){
                $isACC = false;
                if ($row['ACC'] != null && trim($row['ACC']) != '' && trim($row['ACC']) != '-')
                    $isACC = true;
                if ($isACC) {

               
                    if ($data["Prospect_Client"]=='client' ){
                        return  ( 2.5 * ($data["Marge_FAS_Déclarée"] + 12 * $data["CA_Delta_REC"])*1.1 ); 
                    }
                    else { 
                        return ( 2.5 * ($data["Marge_FAS_Déclarée"] + 12 * $data["CA_Delta_REC"]) ) ; 

                }
                else
                {
                    return 0;
                }

                }
                else {
              if ($data["Prospect_Client"]=='client' ){
                        return  ( ($data["Marge_FAS_Déclarée"] + 12 * $data["CA_Delta_REC"])*1.1 ); 
                    }
                    else { 
                        return ( ($data["Marge_FAS_Déclarée"] + 12 * $data["CA_Delta_REC"]) ) ; 

                }
                else
                {
                    return 0;
                }
                }
            }
        )))

But with this I got this error : Parse error: syntax error, unexpected 'else' (T_ELSE)

Yes it seems ok. But then, if I want to create a unique column with both ACC and commercials (the 2 types of salesmen) to group how could I do ?

Like this ?

     $this->src('sales')
     ->pipe(new Filter($filter_args))

 ->pipe(new CalculatedColumn(array(
            "PO"=>function($data){

              if ($data["Prospect_Client"]=='client' ){
                        return  ( ($data["Marge_FAS_Déclarée"] + 12 * $data["CA_Delta_REC"])*1.1 ); 
                    }
                    else { 
                        return ( ($data["Marge_FAS_Déclarée"] + 12 * $data["CA_Delta_REC"]) ) ; 

                }
            }
        )))

 ->pipe(new Group(array(
            "by"=>"Ccial",
            "sum"=>"PO",
        )))
->pipe(new ColumnMeta(array(
    "Ccial" => array(
        "name" => "salesPerson"
    )
)))
->pipe(new Sort(array(
            "PO"=>"desc"
        )))

 ->pipe(new Group(array(
            "by"=>"salesPerson",
            "sum"=>"PO",
        )))
->saveTo($node1);



$this->src('sales')

     ->pipe(new Filter($filter_args))

->pipe(new CalculatedColumn(array(
            "PO"=>function($data){

if ($row['ACC'] != null && trim($row['ACC']) != '' && trim($row['ACC']) != '-') {
              if ($data["Prospect_Client"]=='client' ){
                        return  ( 2.5* ($data["Marge_FAS_Déclarée"] + 12 * $data["CA_Delta_REC"])*1.1 ); 
                    }
                    else { 
                       return  ( 2.5* ($data["Marge_FAS_Déclarée"] + 12 * $data["CA_Delta_REC"])*1); 

                }

            }
            }
        )))


 ->pipe(new Group(array(
            "by"=>"ACC",
            "sum"=>"PO",
        )))
->pipe(new ColumnMeta(array(
    "ACC" => array(
        "name" => "salesPerson"
    )
)))

->pipe(new Sort(array(
            "PO"=>"desc"
        )))

->pipe($node1)
->pipe(new Sort(array(
            "PO"=>"desc"
        )))
->pipe($this->dataStore("PO"));

Thank you. I understand the logic but I have some questions about the practice : - How do you make the combination of $row["ACC"] and $row["Ccial"] ? - In the PO => function ($row) I put this ?

if ($data["Prospect_Client"]=='client' )
{
    return  ( 2.5* ($data["Marge_FAS_Déclarée"] + 12 * $data["CA_Delta_REC"])*1.1 ); 
}
else
{ 
    return  ( 2.5* ($data["Marge_FAS_Déclarée"] + 12 * $data["CA_Delta_REC"])*1); 
}

Thanks and what about

    "saleMan"=>function($row)
    {
        // return combination of $row["ACC"] and $row["Ccial"]
    }

How is it possible to combine both inside one function ?

I have a doubt on how to write this query, i'm not able to write sum and if conditions in php koolreports could u plz help me out...

 select FLD_PRODUCT_NAME, sum(if(FLD_INVT_TRANS_TYPE = '1',FLD_QUANTITY,0)) as inQty, sum(if(FLD_INVT_TRANS_TYPE = '2',FLD_QUANTITY,0)) as outQty from tbl_product_master FULL JOIN tbl_invt_item_details GROUP BY FLD_PRODUCT_NAME.

i have tried writing it as :

public function setup()
{
    $this->src('tally')
   
    ->query(
        DB::table("tbl_product_master")
        ->join("tbl_invt_item_details",'tbl_product_master.FLD_ADD_DATE_TIME','=','tbl_invt_item_details.FLD_ADD_DATE_TIME')
 ->pipe(new CalculatedColumn(array(
        "isOne"=>"({FLD_INVT_TRANS_TYPE}=='1')?1:0",
      )))
 ->pipe(new CalculatedColumn(array(
       "istwo"=>"({FLD_INVT_TRANS_TYPE}=='2')?2:0",
     )))
 ->select(
            'tbl_product_master.FLD_PRODUCT_NAME',
            'tbl_invt_item_details.FLD_QUANTITY'
        )

This code throws errors sir......

public function setup() {

$this->src('tally')   
->query("select FLD_PRODUCT_NAME, FLD_INVT_TRANS_TYPE, FLD_QUANTITY 
    from tbl_product_master FULL JOIN tbl_invt_item_details on tbl_product_master.FLD_ADD_DATE_TIME = tbl_invt_item_details.FLD_ADD_DATE_TIME
") 
->pipe(new CalculatedColumn(array(
    "inQty"=>function($row){
        if ($row["FLD_INVT_TRANS_TYPE"] == 1) return $row["FLD_QUANTITY"];
        else return 0;
    },
    "outQty"=>function($row){
        if ($row["FLD_INVT_TRANS_TYPE"] == 2) return $row["FLD_QUANTITY"];
        else return 0;
    },
)))
    ->pipe(new Group(array(
        "by"=>array("FLD_PRODUCT_NAME"),
        "sum"=>array("inQty","outQty")
    )))

Yes I tried doing the code u have sent me sir....but it throws errors as...

Fatal error: Uncaught Exception: Query Error >> ["42S22",1054,"Unknown column 'tbl_product_master.FLD_PRODUCT_ID' in 'on clause'"] >> select FLD_PRODUCT_NAME, FLD_INVT_TRANS_TYPE, FLD_QUANTITY from tbl_product_master FULL JOIN tbl_invt_item_details on tbl_product_master.FLD_PRODUCT_ID = tbl_invt_item_details.FLD_PRODUCT_ID || Sql params = [] || Search params = [] in C:\xampp\htdocs\koolreport\core\src\datasources\PdoDataSource.php:493 Stack trace: #0 C:\xampp\htdocs\koolreport\core\src\datasources\PdoDataSource.php(549): koolreport\datasources\PdoDataSource->buildMetaData() #1 C:\xampp\htdocs\koolreport\core\src\KoolReport.php(384): koolreport\datasources\PdoDataSource->start() #2 C:\xampp\htdocs\FieldProduct_report\index.php(5): koolreport\KoolReport->run() #3 {main} thrown in C:\xampp\htdocs\koolreport\core\src\datasources\PdoDataSource.php on line 493