KoolReport's Forum

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

DateTimePicker #2922

Open Augustine Arthur opened this topic on on Dec 29, 2022 - 5 comments
Augustine Arthur commented on Dec 29, 2022

I have two Select2 controls and DateTimePicker on my View. The select2 controls work well when I click on Submit button. However, the DateTimePicker doesn't work. Please any suggestions. Below is my display:

This is MyReportMortgage.view.php file:

<?php //Step 1: Load KoolReport require_once "../load.koolreport.php"; use \koolreport\processes\ValueMap; use \koolreport\processes\Sort; use \koolreport\clients\Bootstrap; use \koolreport\processes\ColumnMeta; use \koolreport\processes\Transpose; use \koolreport\processes\ColumnRename; use \koolreport\core\src\core\Utility; use \koolreport\cube\processes\Cube; use \koolreport\inputs\Select2; use \koolreport\processes\TimeBucket; use \koolreport\processes\Group; use \koolreport\processes\DateTimeFormat; //Step 2: Creating Report class class MyReportMortgage extends \koolreport\KoolReport

{

use \koolreport\export\Exportable;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;

use \koolreport\clients\Bootstrap;


protected function defaultParamValues()
{
    return array(
	"years"=>"2019",
     // "Years"=>"",
        "TypeOfApp"=> "",
		 "startDatePicker"=>date("M"),
        "endDatePicker"=>date("M"),
    
    );
}
 protected function bindParamsToInputs()
{
    return array(
        "years",
        "TypeOfApp",
		"startDatePicker",
        "endDatePicker",
     
    );
}
protected function settings()
{
    return array(
        "dataSources"=>array(
            "data"=>array(
                "connectionString"=>"mysql:host=localhost;dbname=mlnrgh",
                "username"=>"root",
                "password"=>"",
                "charset"=>"utf8"
            ),
        )
    );
}


protected function setup()
{
	$query_params = array();
    if($this->params["years"]!=array())
    {
        $query_params[":years"] = $this->params["years"];
    }
    if($this->params["TypeOfApp"]!=array())
    {
        $query_params[":TypeOfApp"] = $this->params["TypeOfApp"];
$this->src('data')->query("
        select
            RecDate,
            sum(ApplicationsRcvd) as 'ApplicationsReceived',
			sum(ApplicationProc) as 'ApplicationsProcessed',
			sum(TotalPending)as 'TotalPending',
			
			YEAR(RecDate) as 'Year',
			MONTHNAME(RecDate) as 'MONTH'
			
          
        from landtitlereg
		where 1=1
        ".(($this->params["years"]!=array())?"and YEAR(RecDate) in (:years)":"")."
        ".(($this->params["TypeOfApp"]!=array())?"and TypeOfApp in (:TypeOfApp)":"")."
         
        GROUP BY Year, TypeOfApp, MONTH
		ORDER BY FIELD(MONTH,'January','February','March','April','May','June','July','August','September','October','November','December')
    ")->params($query_params)
	
  


    ->pipe(new DateTimeFormat(array(
        "RecDate"=>array(
            "from"=>"Y-m-d",
            "to"=>"M",
        )
    )))
	
	
    ->pipe($this->dataStore("result"));

}

}

}

The MyreportMortgage.php file is:

<?php //Step 1: Load KoolReport require_once "../load.koolreport.php"; use \koolreport\processes\ValueMap; use \koolreport\processes\Sort; use \koolreport\clients\Bootstrap; use \koolreport\processes\ColumnMeta; use \koolreport\processes\Transpose; use \koolreport\processes\ColumnRename; use \koolreport\core\src\core\Utility; use \koolreport\cube\processes\Cube; use \koolreport\inputs\Select2; use \koolreport\processes\TimeBucket; use \koolreport\processes\Group; use \koolreport\processes\DateTimeFormat; //Step 2: Creating Report class class MyReportMortgage extends \koolreport\KoolReport

{

use \koolreport\export\Exportable;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;

use \koolreport\clients\Bootstrap;


protected function defaultParamValues()
{
    return array(
	"years"=>"2019",
     // "Years"=>"",
        "TypeOfApp"=> "",
		 "startDatePicker"=>date("M"),
        "endDatePicker"=>date("M"),
    
    );
}
 protected function bindParamsToInputs()
{
    return array(
        "years",
        "TypeOfApp",
		"startDatePicker",
        "endDatePicker",
     
    );
}
protected function settings()
{
    return array(
        "dataSources"=>array(
            "data"=>array(
                "connectionString"=>"mysql:host=localhost;dbname=mlnrgh",
                "username"=>"root",
                "password"=>"",
                "charset"=>"utf8"
            ),
        )
    );
}


protected function setup()
{
	$query_params = array();
    if($this->params["years"]!=array())
    {
        $query_params[":years"] = $this->params["years"];
    }
    if($this->params["TypeOfApp"]!=array())
    {
        $query_params[":TypeOfApp"] = $this->params["TypeOfApp"];
$this->src('data')->query("
        select
            RecDate,
            sum(ApplicationsRcvd) as 'ApplicationsReceived',
			sum(ApplicationProc) as 'ApplicationsProcessed',
			sum(TotalPending)as 'TotalPending',
			
			YEAR(RecDate) as 'Year',
			MONTHNAME(RecDate) as 'MONTH'
			
          
        from landtitlereg
		where 1=1
        ".(($this->params["years"]!=array())?"and YEAR(RecDate) in (:years)":"")."
        ".(($this->params["TypeOfApp"]!=array())?"and TypeOfApp in (:TypeOfApp)":"")."
         
        GROUP BY Year, TypeOfApp, MONTH
		ORDER BY FIELD(MONTH,'January','February','March','April','May','June','July','August','September','October','November','December')
    ")->params($query_params)
	
  


    ->pipe(new DateTimeFormat(array(
        "RecDate"=>array(
            "from"=>"Y-m-d",
            "to"=>"M",
        )
    )))
	
	
    ->pipe($this->dataStore("result"));

}

}

}
Sebastian Morales commented on Dec 30, 2022

I don't see the content of your report view file, only the setup file. Also where did you use "startDatePicker" and "endDatePicker" parameters in your setup?

Augustine Arthur commented on Dec 30, 2022

*__//Please this the process file: MyReportMortgage.php: I used the "startDatePicker",

  //      "endDatePicker", in both the protected function defaultParamValues() and the  protected function //bindParamsToInputs()*__

<?php
//Step 1: Load KoolReport
require_once "../load.koolreport.php";
use \koolreport\processes\ValueMap;
use \koolreport\processes\Sort;
use \koolreport\clients\Bootstrap;
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\Transpose;
use \koolreport\processes\ColumnRename;
use \koolreport\core\src\core\Utility;
use \koolreport\cube\processes\Cube;
use \koolreport\inputs\Select2;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\Group;
use \koolreport\processes\DateTimeFormat;
//Step 2: Creating Report class
class MyReportMortgage extends \koolreport\KoolReport

{
	use \koolreport\export\Exportable;
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;
	
	use \koolreport\clients\Bootstrap;
	
	
	protected function defaultParamValues()
    {
        return array(
		"years"=>"2019",
         // "Years"=>"",
            "TypeOfApp"=> "",
 __"startDatePicker"=>date("M"),
            "endDatePicker"=>date("M"),__
        
        );
    }
	 protected function bindParamsToInputs()
    {
        return array(
            "years",
            "TypeOfApp",
	*__"startDatePicker",
            "endDatePicker",__
         *
        );
    }
    protected function settings()
	{
        return array(
            "dataSources"=>array(
                "data"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=mlnrgh",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        );
    }
	
    protected function setup()
    {
		$query_params = array();
        if($this->params["years"]!=array())
        {
            $query_params[":years"] = $this->params["years"];
        }
        if($this->params["TypeOfApp"]!=array())
        {
            $query_params[":TypeOfApp"] = $this->params["TypeOfApp"];
	$this->src('data')->query("
            select
                RecDate,
                sum(ApplicationsRcvd) as 'ApplicationsReceived',
				sum(ApplicationProc) as 'ApplicationsProcessed',
				sum(TotalPending)as 'TotalPending',
				
				YEAR(RecDate) as 'Year',
				MONTHNAME(RecDate) as 'MONTH'
				
              
            from landtitlereg
			where 1=1
            ".(($this->params["years"]!=array())?"and YEAR(RecDate) in (:years)":"")."
            ".(($this->params["TypeOfApp"]!=array())?"and TypeOfApp in (:TypeOfApp)":"")."
             
            GROUP BY Year, TypeOfApp, MONTH
			ORDER BY FIELD(MONTH,'January','February','March','April','May','June','July','August','September','October','November','December')
        ")->params($query_params)
		
        ->pipe(new DateTimeFormat(array(
            "RecDate"=>array(
                "from"=>"Y-m-d",
                "to"=>"M",
            )
        )))
		
		
        ->pipe($this->dataStore("result"));
	  
}	

}
	}
Augustine Arthur commented on Dec 30, 2022

//This is the report view file: MyReportMortgage.view.php file

<?php
	use \koolreport\widgets\koolphp\Table;
    use \koolreport\inputs\Select2;
	use \koolreport\widgets\google;
    use \koolreport\widgets\google\PieChart;
	use \koolreport\clients\bootstrap;
	use \koolreport\widgets\google\ColumnChart;
	use \koolreport\widgets\google\BarChart;
	use \koolreport\inputs\DateTimePicker;
	use \koolreport\inputs\DateRangePicker;

?>

<div class="report-content">
<?php
$years = $this->params["years"];
//$years = $_POST["years"];
if (is_array($years)) $years = implode(", ", $T);

$TypeOfApp = $this->params["TypeOfApp"];

if (is_array($TypeOfApp)) $TypeOfApp= implode(", ", $TypeOfApp);
$pdfTitle = $TypeOfApp. " in ".$years . " ";
?>
    <div class="text-center">
        <h3>Application for Registration - <?php echo $pdfTitle ?></h3>
        
    </div>
	<div class="report-content">
    
	<form method="post">
        <div class="row">
            <div class="col-md-6">
                <div class="form-group">
                    <b>Select Year</b>
                    <?php 
                    Select2::create(array(
                        "multiple"=>false,
                        "name"=>"years",
                        "dataSource"=>$this->src("data")->query("
                            select YEAR(RecDate) as Year
                            from landtitlereg                            
							group by Year
                        "),
                        "attributes"=>array(
                            "class"=>"form-control"
							//"size"=>1
                        )
                    ));
                    ?>
                </div>    
  
                                   
                <div class="form-group">
                    <b>Select Type of Application</b>
                    <?php 
                    Select2::create(array(
                       "multiple"=>false,
                        "name"=>"TypeOfApp",
                        "dataSource"=>$this->src("data")->query("
                            select TypeOfApp
                            from landtitlereg
                            group by TypeOfApp
                        ")->params(
                            $this->params["years"]!=array()?
                            array(":years"=>$this->params["years"]):
                            array()
                        ),
                        "attributes"=>array(
                            "class"=>"form-control"
							//"size"=>1
                        )
                    ));
                    ?>                
                </div>  
				<div class="col-md-12 form-group">
                <strong>Month Range</strong>
                <div class="row">
                    <div class="col-md-6">
                        From Date:
                        <?php
                        DateTimePicker::create(array(
                        *____    *____"name"=>"startDatePicker",
                            "maxDate"=>"@endDatePicker",**
                            "format"=>"MMMM",
                            "themeBase"=>"bs4",
                        ));
                        ?>
                    </div>
                    <div class="col-md-6">
                        To Date:
                        <?php
                        DateTimePicker::create(array(
                           *____ "name"=>"endDatePicker",
                            "minDate"=>"@startDatePicker",*
                            "format"=>"MMMM",
                            "themeBase"=>"bs4",
							
                        ));
                        ?>
                    </div>
                </div>
            </div>
                <div class="form-group">
                    <button class="btn btn-primary">Submit</button>
					<button formaction="exportLicensesMortgage.php" class="btn btn-primary">Download PDF</button>
					
				
                </div>    
            </div>
        </div>
        
    </form>
    
    <?php
     Table::create(array(
	 
        "dataSource"=>$this->dataStore("result"),
		
		"columns"=>array(
				"MONTH"=>array(
				"label"=>"Month",
				"type"=>"Date",
               
				),
				

               "ApplicationsReceived"=>array(
			   "label"=>"Total Received",
                "type"=>"number",
			   ),
				"ApplicationsProcessed"=>array(
				"label"=>"Total Processed",
                "type"=>"number",
				),
				"TotalPending"=>array(
				"label"=>"Total Pending",
				"type"=>"number",
				),
				
           
			

        ),			
			"grouping"=>array(
            "Year"=>array(
			"RecDate"=>array(
			"from"=>"Y-m-d ",
			"to"=>"M",
    ),
                "calculate"=>array(
                "{ApplicationsReceived}"=>array("sum","ApplicationsReceived"),
				"{ApplicationsProcessed}"=>array("sum","ApplicationsProcessed"),
				"{TotalPending}"=>array("sum","TotalPending"),
				
			
                ),
                "top"=>"<b>Year {Year}</b>",
                "bottom"=>"<td><b>Total of year {Year}</b></td>
				<td><b>{ApplicationsReceived}</b></td>
				<td><b>{ApplicationsProcessed}</b></td>
				<td><b>{TotalPending}</b></td>
				
				
				",
            ),
			
        ),
		"paging"=>array(
            "pageSize"=>25
        ),
        
		"cssClass"=>array(
            "table"=>"table-bordered table-striped table-hover",
    )
) )
    ?>

<i class="fa fa-arrow-down" style="font-size:24px;"></i>
<pre style="font-weight:bold"><code>

</code></pre>
<i class="fa fa-arrow-down" style="font-size:24px;"></i>

    <div style="margin-top:20px;">
        <div class="row">
            
			<div class="col-md-8">
            <?php 
         ColumnChart::create(array(
        "dataStore"=>$this->dataStore('result'),
		"options"=>array(
                'title' => 'Chart Showing Application for Registration',
                'isStacked' => false
                ),		
        "columns"=>array(
            "RecDate"=>array(
                "label"=>"Month",
                "type"=>"Date",
               
            ),
            "ApplicationsReceived"=>array(
                "label"=>"Applications Received",
                "type"=>"number",
               
            ),
			"ApplicationsProcessed"=>array(
                "label"=>"Applications Processed",
                "type"=>"number",
               
            ), 
			"TotalPending"=>array(
                "label"=>"Total Pending",
                "type"=>"number",
               
            ),
			
        ),
        "width"=>"100%",
		
    ));
            ?>
        </div>
            <div class="col-md-8">
            <?php
            PieChart::create(array(
                "dataSource"=>$this->dataStore("result"),
				"options"=>array(
                'title' => 'Piechart Showing Applications - Mortgage',
                ),
			"columns"=>array(
            "RecDate"=>array(
                "label"=>"Month",
                "type"=>"Date",
               
            ),
            "ApplicationsReceived"=>array(
                "label"=>"Total Received",
                "type"=>"number",
               
            ),
			"ApplicationsProcessed"=>array(
                "label"=>"Total Processed",
                "type"=>"number",
               
            ),
			"TotalPending"=>array(
                "label"=>"Total Pending",
                "type"=>"number",
               
            ),
            ),
            
               
                "options"=>array(
                    "legend"=>array(
                        "position"=>"right"
                    ),
                   
                ) 
            ));
            ?>
            </div>
        </div>
    
    
    </div>

</div>
Sebastian Morales commented on Jan 3, 2023

1 . Your "year" parameter ("multiple" => false) is not of array type, you can not use it in sql query with WHERE ... IN clause but with WHERE ... = clause.

2 . You defined startDate and endDate parameters but you didn't use them in your sql query. Thus, it makes no difference to your data whichever date range values are.

Augustine Arthur commented on Jan 10, 2023

Thank you for the suggestions. I will implement them and give you feedback. Thanks

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

Designed and built with by KoolPHP Inc.

Code licensed MIT.