<?php
/** 
 * @package     VikRestaurants
 * @subpackage  core
 * @author      E4J s.r.l.
 * @copyright   Copyright (C) 2023 E4J s.r.l. All Rights Reserved.
 * @license     http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
 * @link        https://vikwp.com
 */

// No direct access
defined('ABSPATH') or die('No script kiddies please!');

/**
 * Widget class used to support the "week reservations" statistics
 * for the restaurant reservations and the take-away orders.
 *
 * Displays a BAR chart showing the total number of received reservations/orders
 * for each day of the selected week (by default, the last 7 days will be used).
 *
 * @since 1.8
 */
class VREStatisticsWidgetWeekres extends VREStatisticsWidget
{
	/**
	 * @override
	 * Returns the form parameters required to the widget.
	 *
	 * @return 	array
	 */
	public function getForm()
	{
		return array(
			/**
			 * The initial date to take when a new session starts.
			 *
			 * @var select
			 */
			'start' => array(
				'type'     => 'select',
				'label'    => JText::translate('VRE_STATS_WIDGET_WEEKRES_INITIAL_DATE_FIELD'),
				'help'     => JText::translate('VRE_STATS_WIDGET_WEEKRES_INITIAL_DATE_FIELD_HELP'),
				'default'  => 'today',
				'options'  => array(
					'-1 week' => JText::translate('VRE_STATS_WIDGET_WEEKRES_INITIAL_DATE_OPT_LAST_WEEK'),
					'-1 day'  => JText::translate('VRE_STATS_WIDGET_WEEKRES_INITIAL_DATE_OPT_PREV_DAY'),
					'today'   => JText::translate('VRE_STATS_WIDGET_WEEKRES_INITIAL_DATE_OPT_TODAY'),
					'+1 day'  => JText::translate('VRE_STATS_WIDGET_WEEKRES_INITIAL_DATE_OPT_TOMORROW'),
				),
			),

			/**
			 * The date to use as week delimiter (ending date).
			 *
			 * The parameter is VOLATILE because, every time the session
			 * ends, we need to restore the field to an empty value, just
			 * to obtain the current date.
			 *
			 * @var calendar
			 */
			'date' => array(
				'type'     => 'calendar',
				'label'    => JText::translate('VRMANAGERESERVATION13'),
				'volatile' => true,
			),

			/**
			 * It is possible to filter the reservations by working
			 * shift. Since we are fetching records for several dates,
			 * the shifts dropdown would contain repeated options,
			 * specially in case of special days.
			 *
			 * For this reason, we need to use pre-built working shifts:
			 * - Lunch   05:00 - 16:59
			 * - Dinner  17:00 - 04:59
			 *
			 * The parameter is VOLATILE because, every time the session
			 * ends, we need to unset the shift filter.
			 *
			 * @var select
			 */
			'shift' => array(
				'type'     => 'select',
				'label'    => JText::translate('VRRESERVATIONSHIFTFILTER'),
				'default'  => '',
				'volatile' => true,
				'options'  => array(
					'0'    	=> JText::translate('VRRESERVATIONSHIFTSEARCH'),
					'5-16' 	=> JText::translate('VRSTATSSHIFTLUNCH'),
					'17-4' 	=> JText::translate('VRSTATSSHIFTDINNER'),
				),
			),

			/**
			 * The color to be used when displaying the chart.
			 * By default, a green color will be used.
			 *
			 * @var color
			 */
			'color' => array(
				'type'    => 'color',
				'label'   => JText::translate('VRE_UISVG_COLOR'),
				'default' => '41be6e',
			),
		);
	}

	/**
	 * @override
	 * Loads the dataset(s) that will be recovered asynchronously
	 * for being displayed within the widget.
	 *
	 * It is possible to return an array of records to be passed
	 * to a chart or directly the HTML to replace.
	 *
	 * @return 	mixed
	 */
	public function getData()
	{
		$dbo = JFactory::getDbo();

		// get date from request
		$filters = array();
		$filters['date']  = $this->getOption('date');
		$filters['shift'] = $this->getOption('shift');

		// use default date if invalid
		if (empty($filters['date']) || $filters['date'] == $dbo->getNullDate())
		{
			// fetch initial date
			$initial = strtotime($this->getOption('start', 'today'), VikRestaurants::now());

			// convert timestamp to date string
			$filters['date'] = date(VREFactory::getConfig()->get('dateformat'), $initial);
		}

		// create timestamp of specified date
		$end_ts   = VikRestaurants::createTimestamp($filters['date'], 23, 59);
		// go back by 7 days
		$start_ts = strtotime('-6 days 00:00:00', $end_ts);

		// init data
		$data = array();

		// create date iterator variable
		$dt = $start_ts;

		for ($i = 0; $i < 7; $i++)
		{
			// format label (use current timezone because we are working with UNIX timestamps)
			$label = JHtml::fetch('date', $dt, 'D d', date_default_timezone_get());

			$data[$label] = 0;

			// increase date by one
			$dt = strtotime('+1 day', $dt);
		}

		// build query
		$q = $dbo->getQuery(true);

		$q->select('COUNT(1) AS ' . $dbo->qn('count'));

		// Make sure the UNIX timestamps are converted to the timezone
		// used by the server, so that the hours won't be shifted.
		$q->select(sprintf(
			'DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(%s), @@session.time_zone, \'%s\'), \'%%Y-%%m-%%d\') AS %s',
			$dbo->qn('checkin_ts'),
			date('P'), // returns the string offset '+02:00'
			$dbo->qn('ymd')
		));

		if ($this->isGroup('restaurant'))
		{
			// load restaurant reservations
			$q->from($dbo->qn('#__vikrestaurants_reservation'));

			// exclude closures
			$q->where($dbo->qn('closure') . ' = 0');
			// exclude children reservations
			$q->where($dbo->qn('id_parent') . ' <= 0');

			// take all the approved statuses
			$approved = JHtml::fetch('vrehtml.status.find', 'code', ['restaurant' => 1, 'approved' => 1]);
		}
		else
		{
			// load take-away orders
			$q->from($dbo->qn('#__vikrestaurants_takeaway_reservation'));

			// take all the approved statuses
			$approved = JHtml::fetch('vrehtml.status.find', 'code', ['takeaway' => 1, 'approved' => 1]);
		}

		if ($approved)
		{
			// filter reservations/orders by status
			$q->where($dbo->qn('status') . ' IN (' . implode(',', array_map([$dbo, 'q'], $approved)) . ')');
		}
		
		$q->where($dbo->qn('checkin_ts') . ' BETWEEN ' . $start_ts . ' AND ' . $end_ts);
		
		if ($filters['shift'])
		{
			/**
			 * Since we are fetching records for several dates,
			 * the shifts dropdown would contain repeated
			 * options, specially in case of special days.
			 *
			 * For this reason, we need to use pre-built
			 * working shifts:
			 * - Lunch   05:00 - 16:59
			 * - Dinner  17:00 - 04:59
			 *
			 * @since 1.8
			 */
			list($fromhour, $tohour) = explode('-', $filters['shift']);

			if ((int) $fromhour < (int) $tohour)
			{
				// do not include MINUTES in query
				$q->where('DATE_FORMAT(FROM_UNIXTIME(' . $dbo->qn('checkin_ts') . '), \'%H\') BETWEEN ' . (int) $fromhour . ' AND ' . (int) $tohour);
			}
			else
			{
				// do not include MINUTES in query
				$q->andWhere(array(
					'DATE_FORMAT(FROM_UNIXTIME(' . $dbo->qn('checkin_ts') . '), \'%H\') >= ' . (int) $fromhour,
					'DATE_FORMAT(FROM_UNIXTIME(' . $dbo->qn('checkin_ts') . '), \'%H\') <= ' . (int) $tohour,
				), 'OR');
			}
		}
		
		$q->group($dbo->qn('ymd'));

		$dbo->setQuery($q);
		
		foreach ($dbo->loadObjectList() as $row)
		{
			// retrieve data key (use current timezone because we are working with UNIX timestamps)
			$key = JHtml::fetch('date', $row->ymd, 'D d', date_default_timezone_get());

			if (isset($data[$key]))
			{
				// increase reservations count
				$data[$key] += $row->count;
			}
		}

		return $data;
	}
}
