<?php

require_once "../../../load.koolreport.php";
use \koolreport\processes\Map;
use \koolreport\processes\Limit;
use \koolreport\processes\Filter;
use \koolreport\cube\processes\Cube;
use \koolreport\pivot\processes\Pivot;
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\Sort;
use \koolreport\processes\RemoveColumn;
use \koolreport\processes\OnlyColumn;
use \koolreport\core\Utility as Util;

class Report extends \koolreport\KoolReport


{
        use \koolreport\excel\BigSpreadsheetExportable;
        	use \koolreport\cloudexport\Exportable;


    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;

    protected function defaultParamValues()
    {
        return array(
            "dateRange"=>array(date("Y-m-d 00:00:00"),date("Y-m-d 23:59:59")),
            "textBox"=>"KoolReport is great",
            "select"=>"",
            "multiSelect"=>array(),
            "radioList"=>"",
            "checkBoxList"=>array(),
            "startDatePicker"=>date("Y-m-d 00:00:00"),
            "endDatePicker"=>date("Y-m-d 23:59:59"),
            "rangeSliderOne"=>array(50),
            "rangeSliderTwo"=>array(20,80),
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
                        "dateRange"=>"dateRange",
            "customers"=>"customers",
            "providers"=>"providers",

            "select",
            "multiSelect",
            "textBox",
            "radioList",
            "checkBoxList",
            "startDatePicker",
            "endDatePicker",
            "singleSelect2",
            "multipleSelect2",
            "singleBSelect",
            "multipleBSelect",
            "rangeSliderOne",
            "rangeSliderTwo",
        );
    }

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

        $this->src("databasetest")->query("

SELECT CONCAT(DAYNAME(start_datetime),' ',MONTHNAME(start_datetime),' ', DAY(start_datetime)) as concatday,
TIME(start_datetime) as time,
CONCAT(DAYNAME(end_datetime),' ',MONTHNAME(end_datetime),' ', DAY(end_datetime)) as concatendday,
TIME(end_datetime) as endtime,
CONCAT(DAYNAME(book_datetime),' ',MONTHNAME(book_datetime),' ', DAY(book_datetime)) as concatbookday,
TIME(book_datetime) as booktime,
TIME_FORMAT(start_datetime, '%h:%i%p') as starttimefixed, 
TIME_FORMAT(end_datetime, '%h:%i%p') as endtimefixed, 
TIME_FORMAT(book_datetime, '%h:%i%p') as booktimefixed, 
ea_appointments.id, 
YEAR(start_datetime) as year, 
company.name as companyname, 
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_appointments.notes as apptnotes, 
        ea_services.name as servicename, 
        ea_users.first_name as locationname, 
        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_services_providers on ea_services_providers.id_services=ea_appointments.id_services
        inner join ea_company_providers on ea_services_providers.id_users=ea_company_providers.id_provider
        inner join company on company.id=ea_company_providers.id_company
                inner join ea_users on ea_users.id=ea_company_providers.id_provider
        WHERE is_unavailable=1 AND ea_appointments.start_datetime BETWEEN :start AND :end AND ea_appointments.end_datetime BETWEEN :start AND :end
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, ea_users.notes, ea_appointments.notes, ea_appointments.id, company.name
ORDER BY YEAR(start_datetime), MONTH(start_datetime), MONTHNAME(start_datetime), DAY(start_datetime), DAYNAME(start_datetime), HOUR(start_datetime), MINUTE(start_datetime), ea_users.last_name, ea_users.first_name, company.name")


->params(array(
            ":start"=>$this->params["dateRange"][0],
            ":end"=>$this->params["dateRange"][1],

        ))
        

    ->pipe($this->dataStore("myappts"));     


        
        
    }
    
    
}
