<?php
if( !isset( $_SERVER['HTTP_X_REQUESTED_WITH'] ) || ( isset( $_SERVER['HTTP_X_REQUESTED_WITH'] ) && $_SERVER['HTTP_X_REQUESTED_WITH'] != 'com.pacim.sa' ) ) {

    require_once("send_mail.php");

    sendEmail(
       'report@pacim.de',
       'POSSIBLE ATTACK by ' . getIPAddress(),
       content_mail( $_SERVER ),
       TRUE
    );

    die('forbidden');
}

/**
 * @var \Fusio\Engine\ConnectorInterface $connector
 * @var \Fusio\Engine\ContextInterface $context
 * @var \Fusio\Engine\RequestInterface $request
 * @var \Fusio\Engine\Response\FactoryInterface $response
 * @var \Fusio\Engine\ProcessorInterface $processor
 * @var \Psr\Log\LoggerInterface $logger
 * @var \Psr\SimpleCache\CacheInterface $cache
 */

use PSX\Http\Exception as StatusCode;

/** @var \Doctrine\DBAL\Connection $connection */
$connection = $connector->getConnection('PaCIM-PAS');


$count = $connection->fetchColumn('SELECT
    COUNT(booking_id)
FROM pacim_booking
WHERE booking_begin = "' . $request->getUriFragment('date') . '"');


$arr_bookings = $connection->fetchAll('
SELECT
	c.customer_id,
    c.customer_firstname,
    SUBSTRING_INDEX(c.customer_firstname, " ", 1) AS customer_shortname,
    c.customer_lastname,
    c.customer_source,
    c.customer_email,
    c.customer_phone,
    c.customer_street,
    c.customer_city,
    c.customer_zipcode,
    c.customer_country,
    b.booking_no,
    UPPER(b.booking_serial) AS booking_serial,
    UPPER(REGEXP_REPLACE(TRIM(b.booking_serial), "^([0-9]{1,3}|[A-ZÄÖÜ]{1,3}).*?([0-9]{1,3}|[A-ZÄÖÜ]{1,3}).*?([A-ZÄÖÜ|0-9]{1,4})", "\\\1 - \\\2 \\\3")) AS license_plate,
    UPPER(REGEXP_REPLACE(TRIM(b.booking_serial), "^([0-9]{1,3}|[A-ZÄÖÜ]{1,3}).*?([0-9]{1,3}|[A-ZÄÖÜ]{1,3}).*?([A-ZÄÖÜ|0-9]{1,4})", "\\\1\\\2\\\3")) AS license_clear,
    b.booking_id,
    b.booking_checkin,
    b.booking_checkout,
    DATE_FORMAT(b.booking_checkin, "%H:%i") AS checkin_zeit,
    DATE_FORMAT(b.booking_checkin, "%d.%m.%Y") AS checkin_datum,
    b.booking_type,
    REPLACE(REPLACE(REPLACE(REPLACE(b.booking_type, "Hallenstellplatz", "Halle"), "Außenstellplatz", "Außen"), "Hallenparkplatz", "Halle"), "Außenparkplatz", "Außen") AS booking_parkplatz,
    (SELECT product_id FROM pacim_invoice_pos WHERE (product_id=8 OR product_id=10) AND invoice_id=b.booking_invoice LIMIT 1) AS booking_special,
    SUBSTR(b.booking_type, 1, 1) AS booking_short,
    (IF((b.booking_event = "" OR b.booking_event = "-"), (
        SELECT
        e.event_calendar_title AS booking_event
        FROM pacim_events e
        WHERE DATE_FORMAT(e.event_calendar_begin, "%Y-%m-%d") = b.booking_begin
        AND DATE_FORMAT(e.event_calendar_end, "%Y-%m-%d") = b.booking_end
        LIMIT 1), b.booking_event)) AS booking_event,
    IF(booking_event IS NOT NULL,
    	IF(ei.event_logo IS NOT NULL,
    		ei.event_logo,
    		"img/seaside/parken-warnemuende.png"
    	),
    	"img/seaside/parken-warnemuende.png"
    ) AS event_logo,
    b.booking_begin,
    b.booking_end,
    DATE_FORMAT(b.booking_begin, "%d.%m.") AS booking_von,
    DATE_FORMAT(b.booking_end, "%d.%m.%Y") AS booking_bis,
    DATE_FORMAT(b.booking_begin, "%Y/%m/%d") AS link_von,
    DATE_FORMAT(b.booking_end, "%Y/%m/%d") AS link_bis,
    DATEDIFF(b.booking_end, b.booking_begin) AS booking_duration,
    d.invoice_status,
    d.invoice_no,
    d.invoice_id,
    d.invoice_price,
    d.invoice_payment,
    d.invoice_coupon,
    d.invoice_date,
    (SELECT FORMAT(SUM(payment_value), 2) AS payed FROM pacim_payments WHERE payment_invoice=d.invoice_id) AS price_payed,
    (SELECT
     FORMAT(SUM(
         IF((product_id <= 2 AND (d.invoice_coupon = 2 OR invoice_pos_discount_type = 2)),
            (invoice_pos_price*0.9*invoice_pos_quantity*1.19),
            IF((product_id <= 2 AND (d.invoice_coupon = 1 OR invoice_pos_discount_type = 1)),
               ((invoice_pos_price*invoice_pos_quantity*1.19)-5),
               (invoice_pos_price*invoice_pos_quantity*1.19)
              ))
     ), 2) AS price
     FROM pacim_invoice_pos
     WHERE invoice_id=b.booking_invoice) AS price
FROM pacim_booking b
    LEFT JOIN pacim_customer c ON c.customer_id = b.booking_customer
    LEFT JOIN pacim_invoice d ON d.invoice_id = b.booking_invoice
    LEFT JOIN pacim_events_info ei ON ei.event_title = booking_event
WHERE b.booking_begin = "' . $request->getUriFragment('date') . '"
ORDER by b.booking_serial ASC');


$arr_collection = array();

if($count > 0) {
	
	foreach($arr_bookings as $key => $item) {
		
		$arr_collection[$key]['customer'] = array(
			'id' => $item['customer_id'],
			'firstname' => $item['customer_firstname'],
			'shortname' => $item['customer_shortname'],
			'lastname' => $item['customer_lastname'],
			'phone' => $item['customer_phone'],
			'email' => $item['customer_email'],
			'source' => $item['customer_source'],
			'street' => $item['customer_street'],
			'zipcode' => $item['customer_zipcode'],
			'city' => $item['customer_city']
		);
		
		$arr_collection[$key]['booking'] = array(
			'id' => $item['booking_id'],
			'no' => $item['booking_no'],
			'customer' => $item['customer_id'],
			'invoice' => $item['invoice_id'],
			'serial' => $item['booking_serial'],
			'license' => $item['license_plate'],
			'license_clear' => $item['license_clear'],
			'checkin' => $item['booking_checkin'],
			'checkout' => $item['booking_checkout'],
			'event' => $item['booking_event'],
			'type' => $item['booking_parkplatz'],
			'short' => $item['booking_short'],
			'valet' => $item['booking_special'],
			'begin' => $item['booking_begin'],
			'end' => $item['booking_end'],
			'duration' => $item['booking_duration'],
		);
		
		$arr_collection[$key]['event'] = array(
			'title' => $item['booking_event'],
			'logo' => $item['event_logo'],
			'von' => $item['booking_von'],
			'bis' => $item['booking_bis'],
		);
		
		$arr_collection[$key]['invoice'] = array(
			'id' => $item['invoice_id'],
			'no' => $item['invoice_no'],
			'price' => number_format( $item['price'], 2, '.', false ),
			'price_payed' => number_format( ( $item['price_payed'] ? $item['price_payed'] : ( $item['invoice_payment'] > 0 ? $item['price'] : 0 ) ), 2, '.', false ),
			'price_diff' => number_format( ( $item['price']-$item['price_payed'] ), 2, '.', false ),
			'payment' => ( $item['invoice_payment'] == 0 && $item['price_payed'] != null && ($item['price']-$item['price_payed']) == 0 ? 5 : $item['invoice_payment'] ),
			'customer' => $item['customer_id'],
			'coupon' => $item['invoice_coupon'],
			'status' => $item['invoice_status'],
			'date' => $item['invoice_date'],
			'positions' => $connection->fetchAll('
				SELECT
					invoice_pos_id AS pos_id,
					product_id AS product,
					invoice_pos_price AS price,
					invoice_pos_title AS titel,
					invoice_pos_discount_type AS discount_type,
					invoice_pos_discount AS discount
				FROM pacim_invoice_pos
				WHERE invoice_id = ' . $item['invoice_id']
			)
		);
	}
	
}

/*if (empty($arr_bookings)) {
    throw new StatusCode\NotFoundException('Entry not available');
}*/

return $response->build(200, [], array(
	'count' => $count,
	'date' => $request->getUriFragment('date'),
	'entries' => $arr_collection
));