<?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",

            "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()
    {
        if($this->params["customers"] !== ''){
            $dude = " AND ea_services.name= :service";
        }else{
            $dude = "";
        }
        
        $this->src("databasetest")->query("
SELECT name FROM `ea_services`

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

        $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(end_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, 
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, 
CONCAT("."'".Config::BASE_URL."'".",'/index.php/backend?edit=y&apptid=', ea_appointments.id) as editurl, 
CONCAT("."'".Config::BASE_URL."'".",'/index.php/backend?del=y&apptid=', ea_appointments.id) as deleteurl, 
ea_appointments.book_datetime, 
ea_appointments.start_datetime, ea_appointments.end_datetime, 
        ea_users.notes as usernotes, 
        ea_users.address as address, 
        ea_users.city as city, 
        ea_users.state as state, 
        ea_users.zip_code as zipcode,
        ea_appointments.notes as apptnotes, 
        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.birthdate, 
        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 
        WHERE ea_appointments.is_unavailable=0 AND ea_appointments.start_datetime BETWEEN :start AND :end AND ea_appointments.end_datetime BETWEEN :start AND :end".$dude."
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
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
")
    
->params(array(
            ":start"=>$this->params["dateRange"][0],
            ":end"=>$this->params["dateRange"][1],
            ":service"=>$this->params["customers"],

        ))
        

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


        
        
    }
    
    
}
