VisualQuery

The basic working of VisualQuery

Select and join tables here

Add filters here

Set groups by fields here

Add sorts here

Set row's offset and limit here

select * from customers where 1=0
customerNumber customerName contactLastName contactFirstName phone addressLine1 addressLine2 city state postalCode country salesRepEmployeeNumber creditLimit

koolreport/visualquery is package to build query using UI.

//MyReport.php
class Report extends \koolreport\KoolReport
{
    use \koolreport\visualquery\Bindable;
    ...
//MyReport.view.php
<?php
    \koolreport\visualquery\VisualQuery::create(array(
        "name" => "visualquery1",
        ...
<?php
require_once "MyReport.php";

$report = new MyReport;
$report->run()->render();
<?php
//Step 1: Load KoolReport
require_once "../../../load.koolreport.php";

//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources"=>array(
                "automaker"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=automaker",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        ); 
    } 
    protected function setup()
    {
        $params = \koolreport\core\Utility::get($this->queryParams, 'visualquery1');
        $qb = $this->paramsToQueryBuilder($params);
        $this->queryStr = $queryStr = $params ? $qb->toMySQL() : "select * from customers where 1=0";
        
        $this
        ->src('automaker')
        ->query($queryStr)
        ->pipe(new \koolreport\processes\ColumnMeta([
            "Order Number" => [
                "type" => "string"
            ],
            "orderMonth" => [
                "type" => "string"
            ],
        ]))
        ->pipe($this->dataStore('vqDS'));
    }  

}
<?php
    use \koolreport\visualQuery\VisualQuery;
    use \koolreport\datagrid\DataTables;
?>
<form method="post">
    <div class="report-content">
        <div class="text-center">
            <h1>VisualQuery</h1>
            <p class="lead">
            The basic working of VisualQuery
            </p>
        </div>
        
        <?php
        \koolreport\visualquery\VisualQuery::create(array(
            "name" => "visualquery1",
            "themeBase" => "bs4",
            "schema" => array(
                "tables" => [
                    "customers"=>array(
                        "{meta}" => [
                            "alias" => "Customers"
                        ],
                        "customerNumber"=>array(
                            "alias"=>"Customer Number",
                        ),
                        "customerName"=>array(
                            "alias"=>"Customer Name",
                        ),
                    ),
                    "orders"=>array(
                        "{meta}" => [
                            "alias" => "Orders"
                        ],
                        "orderNumber"=>array(
                            "alias"=>"Order Number"
                        ),
                        "orderDate"=>array(
                            "alias"=>"Order Date"
                        ),
                        "orderMonth" => [
                            "expression" => "month(orderDate)",
                        ]
                        // "customerNumber"=>array(
                        //    "alias"=>"Customer Number"
                        // )
                    ),
                    "orderdetails"=>array(
                        "{meta}" => [
                            "alias" => "Order Details"
                        ],
                        // "orderNumber"=>array(
                        //     "alias"=>"Order Number"
                        // ),
                        "quantityOrdered"=>array(
                            "alias"=>"Quantity",
                            "type"=>"number",
                        ),
                        "priceEach"=>array(
                            "alias"=>"Price Each",
                            "type"=>"number",
                            "decimal"=>2,
                            "prefix"=>"$",
                        ),
                        // "productCode"=>array(
                        //     "alias"=>"Product Code"
                        // ),
                        "cost" => [
                            // "expression" => "orderdetails.quantityOrdered * orderdetails.priceEach",
                            "expression" => "quantityOrdered * priceEach",
                            "alias"=>"Cost",
                            "type"=>"number",
                            "decimal"=>2,
                            "prefix"=>"$",
                        ]
                    ),
                    "products"=>array(
                        "{meta}" => [
                            "alias" => "Products"
                        ],
                        "productCode"=>array(
                            "alias"=>"Product Code"),
                        "productName"=>array(
                            "alias"=>"Product Name"),
                    )
                ],
                "relations" => [
                    ["orders.customerNumber", "leftjoin", "customers.customerNumber"],
                    ["orders.orderNumber", "join", "orderdetails.orderNumber"],
                    ["orderdetails.productCode", "leftjoin", "products.productCode"],
                ]
            ),
            "defaultValue" => [
                "selectTables" => [
                    "orders",
                    "orderdetails",
                    "products",
                ],
                "selectFields" => [
                    "products.productName",
                ],
                "filters" => [
                    ["products.productCode", "btw", "2", "998", "or"],
                    // ["products.productName", "nbtw", "1", "", "and"],
                    ["products.productName", "<>", "a", "", "or"],
                    ["products.productName", "nin", "a", "", "or"],
                    // ["products.productName", "null", "a", "", "or"],
                    ["products.productName", "nnull", "a", "", "or"],
                    ["products.productName", "ctn", "a", "", "or"],
                    // ["products.productName", "nctn", "a", "", "or"],
                ],
                "groups" => [
                    ["orderdetails.cost", "sum"]
                ],
                "sorts" => [
                    ["products.productName", "desc"]
                ],
                "offset" => 5,
                "limit" => 10,
            ],
        ));
        ?>

        <button type='submit' class='btn btn-light' >Submit</button> 

        <style>
            pre {
                overflow-x: auto;
                white-space: pre-wrap;
                white-space: -moz-pre-wrap;
                white-space: -pre-wrap;
                white-space: -o-pre-wrap;
                word-wrap: break-word;
            }
        </style>
        <div style="margin: 30px; width:800px">
            <pre style="width:800px"><?php echo $this->queryStr; ?></pre>
        </div>

        <?php
            // print_r($this->dataStore('vqDS')->meta());
            DataTables::create(array(
                "name" => "charttable1",
                "dataSource" => $this->dataStore('vqDS'),
                // "columns" => ["Quantity", "Product Name"],
                "options" => [
                    "paging" => true
                ]
            ));
        ?>
    </div>
</form>

What People Are Saying

"KoolReport helps me very much in creating data report for my corporate! Keep up your good work!"
-- Alain Melsens

"The first use of your product. I was impressed by its easiness and powerfulness. This product is a great and amazing."
-- Dr. Lew Choy Onn

"Fantastic framework for reporting!"
-- Greg Schneider

Download KoolReport Get KoolReport Pro