KoolReport's Forum

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

CalculatedColumn and ColumnMeta #259

Open arthur opened this topic on on Apr 24, 2018 - 18 comments

arthur commented on Apr 24, 2018

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 !!

KoolReport commented on Apr 26, 2018

Arthur, we are sorry for our late reply on this question. I will get our expert David to answer you as soon as possible. Thank you very much for your patience.

David Winterburn commented on Apr 26, 2018

Hi Arthur,

Would you please clarify the calculated column more, how many new columns do you want to create, 1 or 2? If you want only 1 calculated column, you could check if a data row is either 'commercial' or 'ACC' before return a formula. If you want 2 calculated columns, you could add another like "PO2" for ACC accounts. Sorry if we misunderstood your question. Thanks!

arthur commented on Apr 26, 2018

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 ?

David Winterburn commented on Apr 26, 2018

Hi Arthur,

In that case, please check if a data row is either "commerical" or "ACC" like this:

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

Please let us know if this meets your condition. Thanks!

arthur commented on Apr 26, 2018

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)

KoolReport commented on Apr 26, 2018

This is not logic error, just syntax of php language, may be you use else without if. Please check!

arthur commented on Apr 26, 2018

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 ?

David Winterburn commented on Apr 26, 2018

Hi Arthur,

You could create a new column called "salesMan" with value being combination of "commercial" and "ACC", another column for calculating PO. Then group by the new column "salesMan" with sum by "PO". Thanks!

arthur commented on Apr 26, 2018

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"));
KoolReport commented on Apr 26, 2018

David suggest you a simpler method

  1. Create "saleMan" column which is combination of ACC and Commercial
  2. Create "PO" column
  3. Group by "saleMan" and sum "PO"
$this->src("sales")
->pipe(new Filter($filter_args))
->pipe(new CalculatedColumn(array(
    "saleMan"=>function($row)
    {
        // return combination of $row["ACC"] and $row["Ccial"]
    },
    "PO"=>function($row)
    {
         // return value PO, take into account the value of $row["Ccial"] and $row["ACC"]
    }
)))
->pipe(new Group(array(
    "by"=>"saleMan",
    "sum"=>"PO"
)))
->pipe($this->dataStore("PO"));
arthur commented on May 3, 2018

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); 
}
KoolReport commented on May 4, 2018

Hi Arthur,

If you use $row in "PO"=>function($row), you should use variable name $row instead of $data.

arthur commented on May 4, 2018

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 ?

KoolReport commented on May 4, 2018

There you can check the value of ACC and Ccial and return single value base on their value. For example, If Ccial has value, return Ccial. If Ccial is null, then return ACC if ACC is not null. If both of them is null, return your defined default value.

Above just the example, you must have your own rule. Even you must combine with other values in the row to produce "saleMan" value

Jisha Cheriyan commented on Sep 7

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'
        )
Sebastian Morales commented on Sep 14

Pls try this:

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")
        )))
Jisha Cheriyan commented on Sep 17

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

Sebastian Morales commented 1 day ago

There's a sql error in your query. The message said right there that column tbl_product_master.FLD_PRODUCT_ID doesn't exist.

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
help needed

None