VisualQuery

The basic working of VisualQuery

Select and join tables here

Add filters here (filters and brackets are draggable)

Set groups by fields here (groups, havings, and brackets are draggable)

Add sorts here (sorts are draggable)

Set row's offset and limit here

Select query:
select * from customers where 1=0
Query with parameters:
select * from customers where 1=0
Parameters:
[]
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
{
    use \koolreport\visualquery\Bindable;

    protected $queryStr;
    protected $paramQuery;
    protected $sqlParams;

    public function defineSchemas()
    {
        return [
            "salesSchema" => array(
                "tables" => [
                    "customers"=>array(
                        "{meta}" => [
                            "alias" => "Table Customers"
                        ],
                        "customerNumber"=>array(
                            "alias"=>"Customer Number",
                        ),
                        "customerName"=>array(
                            "alias"=>"Customer Name",
                        ),
                    ),
                    "orders"=>array(
                        "{meta}" => [
                            "alias" => "Table Orders"
                        ],
                        "orderNumber"=>array(
                            "alias"=>"Order Number"
                        ),
                        "orderDay" => array(
                            "alias" => "Order Day",
                            "expression" => "date(orderDate)",
                            "type" => "date",
                        ),
                        "orderDate"=>array(
                            "alias"=>"Order Date",
                            "type" => "datetime"
                        ),
                        "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" => "Table 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"],
                ]
            ),
            "separator" => ".",
        ];
    }

    public function settings()
    {
        //Get default connection from config.php
        $config = include "../../../config.php";

        return array(
            "dataSources"=>array(
                "automaker"=>$config["automaker"]
            )
        );
    }   
    protected function setup()
    {
        // echo "post="; \koolreport\core\Utility::prettyPrint($_POST);

        $params = \koolreport\core\Utility::get($this->queryParams, 'visualquery1');
        $qb = $this->paramsToQueryBuilder($params);
        $this->queryStr = $params ? $qb->toMySQL() : "select * from customers where 1=0";
        $this->paramQuery = $params ? $qb->toMySQL(['useSQLParams' => "name"]) : "select * from customers where 1=0";
        $this->sqlParams = $qb->getSQLParams();

        $this
        ->src('automaker')
        ->query($this->paramQuery)
        ->params($this->sqlParams)
        ->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" => "salesSchema",
            "defaultValue" => [
                "selectDistinct" => false,
                "selectTables" => [
                    "orders",
                    "orderdetails",
                    "products",
                ],
                "selectFields" => [
                    "products.productName",
                ],
                "filters" => [
                    "(",
                    [
                        "field" => "orders.orderDay", 
                        "operator" => ">", 
                        "value1" => "2001-01-01", 
                        "value2" => "", 
                        "logic" => "and",
                        "toggle" => true,
                    ],
                    [
                        "field" => "products.productCode", 
                        "operator" => "nbtw", 
                        "value1" => "2", 
                        "value2" => "998", 
                        "logic" => "or",
                        "toggle" => true,
                    ],
                    ["products.productName", "<>", "a", "", "or", "toggle" => false],
                    ["products.productName", "nin", "a,b,c", "", "or"],
                    ["products.productName", "ctn", "a", "", "or"],
                    ")",
                ],
                "groups" => [
                    [
                        "field" => "orderdetails.cost", 
                        "aggregate" => "sum", 
                        "toggle" => true
                    ]
                ],
                "havings" => [
                    "(",
                    [
                        "field" => "sum(orderdetails.cost)", 
                        "operator" => ">", 
                        "value1" => "10000", 
                        "value2" => "", 
                        "logic" => "and",
                        "toggle" => true,
                    ],
                    ["products.productName", "<>", "a", "", "or", "toggle" => false],
                    ")",
                ],
                "sorts" => [
                    [
                        "field" => "sum(orderdetails.cost)", 
                        "direction" => "desc", 
                        "toggle" => true
                    ],
                    ["products.productName", "desc", "toggle" => false]
                ],
                "limit" => [
                    "offset" => 5,
                    "limit" => 10,
                    "toggle" => false,
                ]
            ],
            "activeTab" => "filters",
        ));
        ?>

        <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">
            <b>Select query:</b>
            <pre style="width:800px"><?php echo $this->queryStr; ?></pre>
            <b>Query with parameters:</b>
            <pre style="width:800px"><?php echo $this->paramQuery; ?></pre>
            <b>Parameters:</b>
            <?php \koolreport\core\Utility::prettyPrint($this->sqlParams); ?>
        </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