KoolReport's Forum

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

Cards: Assigning value being submit #2330

Closed Niall McGuirk opened this topic on on Sep 10, 2021 - 1 comments

Niall McGuirk commented on Sep 10, 2021

I'm using date inputs to get a date value which then determines the value to show in the Card. However, when I load the page, I get an error

Prior to submit:

but the value is set in the Card:

After submit button pressed:

Which is resolved when I press submit. I gather it is because the compare Period value is not being set prior to the button submit.

How can I resolve this?

MyReport.php

<?php
//Step 1: Load KoolReport
require_once "../../load.koolreport.php";

//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;

    //KoolReports Forum DefaultParamValues()
    protected function defaultParamValues()
{
    $periodStore = $this->src("dealhackdb")->query("
        select Distinct YearMonth
        from tbl_Metrics
        Order by YearMonth Desc Limit 0, 1;
    ")
    ->pipe(new \koolreport\core\DataStore)
    ->requestDataSending(); //Force to perform query data

    return array( //because multiple = false on select2, should be a string/number (not an array, hence array($period...) is gone vv
        "activeperiod"=>$periodStore->get(0,"YearMonth"),
        "compareperiod"=>$periodStore->get(1,"YearMonth"),
    );
}
    
    //Justin's defaultParamValues()
//    protected function defaultParamValues()
//    {
//        return array(
//            //"activeperiod"=>array("2107"),
//            //"compareperiod"=>array("2106"),
//            
//            //activeperiod" is assoc array name/key
//            "activeperiod"=>array($this->src("kazzicom_kpi")->query("
//                            select Distinct YearMonth
//                            from tbl_KPIMetrics
//                            Order by YearMonth Desc Limit 0,1")),
//            //limit starting row, how many down to show
//            //eg limit 5, 5; would display record 6,7,8,9,10 
//            "compareperiod"=>array($this->src("kazzicom_kpi")->query("
//                            select Distinct YearMonth
//                            from tbl_KPIMetrics
//                            Order by YearMonth Desc Limit 1,1")),
//        );
//    }
    
    protected function bindParamsToInputs()
    {
        return array(
            "activeperiod",
            "compareperiod",
        );
    }

    protected function settings()
    {
        $config = include "../../config.php";
        return array(
            "dataSources"=>$config
        );
    }

    protected function setup()
    {
        
 
    }
}

MyReport.View.php

<?php
    use \koolreport\widgets\koolphp\Card;
    use \koolreport\inputs\Select2;
?>
<div class="report-content">
    <div class="text-center">
        <h1>KPI Summary</h1>
        <p class="lead">
            Shows the Sales KPIs for this month.
        </p>
    </div>
<!--    DATE SELECTION FORM-->
    <form method="post">
        <div class="row" >
            <div class="col-md-6">
                <div class="form-group">
                    <b>Active Period</b>
                    <?php 
                    //LISTS MONTHYEARS FROM METRICS TABLE, INPUT BOX 1
                    Select2::create(array(
                        "multiple"=>false,
                        "name"=>"activeperiod",
                        "dataSource"=>$this->src("dealhackdb")->query("
                            select Distinct YearMonth
                            from tbl_metrics
                            Order by YearMonth Desc
                        "),
                        "attributes"=>array(
                            "class"=>"form-control"
                        )
                    ));
                    ?>
                </div>    
  
            </div>
            <div class="col-md-6">
                <div class="form-group">
                    <b>Compare To Period</b>
                    <?php 
                    //LISTS MONTHYEARS IN METRICS TABLE, INPUT BOX 2
                    Select2::create(array(
                        "multiple"=>false,
                        "name"=>"compareperiod",
                        "dataSource"=>$this->src("dealhackdb")->query("
                            select Distinct YearMonth
                            from tbl_Metrics 
                            Order by YearMonth Desc Limit 1,100
                        "),
                        "attributes"=>array(
                            "class"=>"form-control"
                        )
                    ));
                    ?>
                </div>    
  
                <div class="form-group">
                    <button class="btn btn-primary">Submit</button>
                </div>    
            </div>
        </div>
        
    </form>
<!--        CARD 1-->
    <div class="row">
        <div class="col-md-3 mx-auto">
            <?php
            Card::create(array(       //CARD 1 CONFIG
                "value"=>$this->src("dealhackdb")->query("Select 
                                                          count(distinct tbl_dealhack.user_id) as User_count
                                                          From 
                                                          tbl_dealhack
                                                          left join tbl_profile on tbl_profile.user_id = tbl_dealhack.user_id
                                                          left join tbl_companytribe on tbl_companytribe.id = tbl_profile.TribeID"),
                "baseValue"=>$this->src("dealhackdb")->query("SELECT MetricValue FROM tbl_Metrics where YearMonth= :compareperiod and MetricID=1")->params(array(
                ":compareperiod"=>$this->params["compareperiod"],
                )),
                
                
//                Kazzicom version: "baseValue"=>$this->src("delv_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='DELV' and YearMonth=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 MONTH), '%y%m') and MetricID=1"),
                "title"=>"Number of Users",
                "cssClass"=>array(
                    "card"=>"bg-info",
                    "title"=>"text-white",
                    "value"=>"text-white"
                )
            ));
            ?>
            
        </div> <!-- CARD1 END
           CARD 2-->
        <div class="col-md-3 mx-auto">
            <?php
            Card::create(array(   //CARD 2 CONFIG
                "value"=>$this->src("dealhackdb")->query("Select 
                                                          count(distinct TribeName) as Tribe_count
                                                          From 
                                                          tbl_dealhack
                                                          left join tbl_profile on tbl_profile.user_id = tbl_dealhack.user_id
                                                          left join tbl_companytribe on tbl_companytribe.id = tbl_profile.TribeID;"),
                
                "baseValue"=>$this->src("dealhackdb")->query("SELECT MetricValue FROM tbl_Metrics where YearMonth= :compareperiod and MetricID=2")->params(array(
                ":compareperiod"=>$this->params["compareperiod"],
                
//                Kazzicom version: "baseValue"=>$this->src("kazzicom_kpi")->query("SELECT MetricValue FROM tbl_KPIMetrics where Client='Kazzicom' and YearMonth= :compareperiod and MetricID=2")->params(array(
//                ":compareperiod"=>$this->params["compareperiod"],
                )),
                //->requestDataSending(),
                
                "title"=>"Number of Tribes",
                "cssClass"=>array(
                    "card"=>"bg-warning",
                    "title"=>"text-white",
                    "value"=>"text-white"
                )
            ));
            ?>
        </div> <!--CARD2 END-->
    </div> <!-- CARD ROW1 END-->

  


</div>
KoolReport commented on Sep 11, 2021

This issue is here, you do this:

    $periodStore = $this->src("dealhackdb")->query("
        select Distinct YearMonth
        from tbl_Metrics
        Order by YearMonth Desc Limit 0, 1;
    ")

The above query will return only 1 row which is not I meant to do. It should return 2 rows, the first row is will contain value for activeperiod and second row contains value for compareperiod.

The query should be:

    $periodStore = $this->src("dealhackdb")->query("
        select Distinct YearMonth
        from tbl_Metrics
        Order by YearMonth Desc Limit 2;
    ")

Hope that helps.

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
solved

None