<?php
require_once "../../../load.koolreport.php";
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;

class YearsMonthsCustomersCategories extends koolreport\KoolReport
{
    function settings()
    {
        $config = include "../../../config.php";
        return array(
            "dataSources"=>array(
                "databasetest"=>$config["databasetest"]            )
            
        );
    }
    function setup()
    {
        $node = $this->src('databasetest');
        
          $node->query("
SELECT CONCAT(DAY(start_datetime),' ','-',' ',DAYNAME(start_datetime)) as concatday, TIME(start_datetime) as time, CONCAT(HOUR(start_datetime), ':', MINUTE(start_datetime)) as concattime, YEAR(start_datetime) as year, MONTH(start_datetime) as monthnumber, MONTHNAME(start_datetime) as monthday, DAY(start_datetime) as daynumber, DAYNAME(start_datetime) as dayname, HOUR(start_datetime) as hour, MINUTE( start_datetime) as minute, COUNT(start_datetime) as countbookdate, sum(ea_services.price) as totalprice, ea_appointments.book_datetime, ea_appointments.start_datetime, 
        ea_appointments.end_datetime, 
        ea_services.name, 
        ea_users.first_name, 
        CONCAT(ea_users.first_name, ea_users.last_name) as apptperson,
        ea_users.last_name,
        ea_users.email, 
        
        ea_users.phone_number FROM `ea_appointments`
        inner JOIN ea_services on ea_appointments.id_services=ea_services.id 
inner join 
        ea_users 
        on 
        ea_appointments.id_users_customer=ea_users.id 
GROUP BY YEAR(start_datetime), MONTH(start_datetime), MONTHNAME(start_datetime), DAY(start_datetime), DAYNAME(start_datetime), concatday, time, HOUR(start_datetime), MINUTE(book_datetime), ea_services.name, ea_appointments.book_datetime,ea_appointments.start_datetime,ea_appointments.end_datetime, ea_users.first_name, ea_users.last_name, ea_users.email, ea_users.phone_number
ORDER BY YEAR(start_datetime), MONTH(start_datetime), MONTHNAME(start_datetime), DAY(start_datetime), DAYNAME(start_datetime), HOUR(start_datetime), MINUTE(book_datetime), ea_users.last_name, ea_users.first_name 

        ")
        /*->pipe(new Filter(array(
            array('name', '<', 'Am'),
            array('id', '>', 2003)
        )))*/
        /*->pipe(new ColumnMeta(array(
            "name"=>array(
                'type' => 'number',
                "prefix" => "$",
            ),
        )))*/
        ->pipe(new Pivot(array(
            "dimensions"=>array(
                "column"=>"",
                "row"=>"concatday, time, apptperson",
            ),
            "aggregates"=>array(
                "count"=>"first_name",
                
            )
        )))
        ->pipe($this->dataStore('customers'));  
    }
}
