<?php
/**
* @version	$Id: reports_tag_processor.php 15009 2012-01-06 20:37:54Z alex $
* @package	In-Commerce
* @copyright	Copyright (C) 1997 - 2009 Intechnic. All rights reserved.
* @license	Commercial License
* This software is protected by copyright law and international treaties.
* Unauthorized reproduction or unlicensed usage of the code of this program,
* or any portion of it may result in severe civil and criminal penalties,
* and will be prosecuted to the maximum extent possible under the law
* See http://www.in-portal.org/commercial-license for copyright notices and details.
*/

defined('FULL_PATH') or die('restricted access!');

class ReportsTagProcessor extends kDBTagProcessor {

	function ReportStatus($params)
	{
		$field_values = $this->CalcReport($params);

		if ($field_values['offset'] == $field_values['total']) {
			$this->Application->Redirect($this->Application->RecallVar('reports_finish_t'));
			$this->Application->RemoveVar('report_options');
		}
		else {
			$this->Application->StoreVar('report_options', serialize($field_values));
		}

		return $field_values['offset']  * 100 / $field_values['total'];
	}

	function CalcReport($params)
	{
		$field_values = unserialize($this->Application->RecallVar('report_options'));
		$per_step = 20;

		$cats = $this->Conn->Query('SELECT * FROM '.TABLE_PREFIX.'Categories ORDER BY CategoryId LIMIT '.$field_values['offset'].', '.$per_step);

		foreach ($cats as $a_cat) {
			if ($field_values['Recursive']) {
				$cat_filter = 'c.ParentPath LIKE '.$this->Conn->qstr($a_cat['ParentPath'].'%');
			}
			else {
				$cat_filter = 'c.CategoryId = '.$a_cat['CategoryId'];
			}

			$q = 'INSERT INTO '.$field_values['table_name'].'
						SELECT
							c.CategoryId,
							SUM(od.Quantity) as Qty,
							SUM(od.Cost) as Cost,
							SUM(od.Price) as SaleAmount,
							0 as Tax,
							0 as Shipping,
							0 as Processing,
							SUM(od.Price - od.Cost) as Profit
						FROM '.TABLE_PREFIX.'Orders AS o
						LEFT JOIN '.TABLE_PREFIX.'OrderItems AS od
						ON od.OrderId = o.OrderId
						LEFT JOIN '.TABLE_PREFIX.'Products AS p
						ON p.ProductId = od.ProductId
						LEFT JOIN '.TABLE_PREFIX.'CategoryItems AS ci
						ON ci.ItemResourceId = p.ResourceId
						LEFT JOIN '.TABLE_PREFIX.'Categories AS c
						ON c.CategoryId = ci.CategoryId
						WHERE
							o.Status = 4
							AND
							ci.PrimaryCat = 1
							AND
							'.$cat_filter.'
						GROUP BY c.CategoryId';
			$this->Conn->Query($q);

			$field_values['offset']++;
		}

		return $field_values;
	}

	function ReportTypeEquals($params)
	{
		$field_values = unserialize($this->Application->RecallVar('report_options'));
		return ($field_values['ReportType'] == $params['value']);
	}

	function CalculateChart($params)
	{

		$a_report_options = unserialize($this->Application->RecallVar('report_options'));
		$metric = $this->Application->RecallVar('ChartMetric');
		if ($metric == '')
		{
			// get first option from unit config
			$a_virtual_fields = $this->Application->getUnitOption('rep', 'VirtualFields');
			foreach ($a_virtual_fields['Metric']['options'] AS $field => $label)
			{
				$metric = $field;
				break;
			}
		}


		$object =& $this->Application->recallObject('rep.params', null, Array('skip_autoload' => true));
		/* @var $object kDBItem */

		$object->setID(1);
		$object->SetDBField('Metric', $metric);

		if (!($a_report_options['from'] && $a_report_options['to'])) {
			// calculate from & to as extreme order dates
			$sql = 'SELECT MAX(OrderDate) AS date_to, MIN(OrderDate) AS date_frm
				FROM '.TABLE_PREFIX.'Orders
				WHERE
					Status IN (4,6)
			';
			$a_dates = $this->Conn->GetRow($sql);
			$a_report_options['from'] = adodb_mktime(0, 0, 0, date('m', $a_dates['date_frm']), date('d', $a_dates['date_frm']), date('Y', $a_dates['date_frm']));
			$a_report_options['to'] = adodb_mktime(0, 0, 0, date('m', $a_dates['date_to']), date('d', $a_dates['date_to']) + 1, date('Y', $a_dates['date_to'])) - 1;
		}

		$filter_value = 'AND o.OrderDate >= '.$a_report_options['from'].' AND o.OrderDate <= '.$a_report_options['to'];


		if ($a_report_options['ReportType'] == 12)
		{
			// Overall

			$selected_days = round(($a_report_options['to'] - $a_report_options['from'] + 1) / 3600 / 24);
			// determine date interval
			if ($selected_days < 2)
			{
				$step_seconds = 3600;
				$step_labels = Array();
				for ($i=0; $i<24; $i++)
				{
					$hour = str_pad($i, 2, '0', STR_PAD_LEFT);
					$step_labels[$i] = $hour;
				}
			} elseif (
				($selected_days < 31)
				|| (date('mY', $a_report_options['from']) == date('mY', $a_report_options['to']))
			)
			{
				$step_seconds = 24*3600;
				$step_labels = Array();
				$curr_date = $a_report_options['from'];
				while ($curr_date <= $a_report_options['to'])
				{
					$curr_date += $step_seconds;
					$step_labels[] = date('d-M', $curr_date);
				}
			} else {
				$start_year = date('Y', $a_report_options['from']);
				$start_month = date('m', $a_report_options['from']);
				$end_month_year = date('Ym', $a_report_options['to']);
				// big interval - move from date to the first day ot the month
				$a_report_options['from'] = adodb_mktime(0, 0, 0, date('m', $a_report_options['from']), 1, date('Y', $a_report_options['from']));
				$curr_time = $a_report_options['from'];
				while (date('Ym', $curr_time) <= $end_month_year)
				{
					$step_labels[date('Ym', $curr_time)] = date('M-Y', $curr_time);
					// add month
					$curr_time = adodb_mktime(0,0,0, date('m', $curr_time) + 1, 1, date('Y', $curr_time));
				}
				$step_seconds = 0;
			}

			$a_expressions = Array(
				'Qty' => 'od.Quantity',
				'Cost' => 'od.Cost * od.Quantity',
				'Amount' => 'od.Price * od.Quantity',
				'Tax' => 'o.VAT * od.Price * od.Quantity / o.SubTotal',
				'Shipping' => 'o.ShippingCost * od.Price * od.Quantity / o.SubTotal',
				'Processing' => 'o.ProcessingFee * od.Price * od.Quantity / o.SubTotal',
				'Profit' => '(od.Price - od.Cost)  * od.Quantity',
			);

			if ($step_seconds)
			{
				$period_sql = 'FLOOR(
								(o.OrderDate - '.$a_report_options['from'].')
								/'.$step_seconds.'
							)';
			} else {
				$period_sql = 'CONCAT(YEAR(FROM_UNIXTIME(o.OrderDate)),LPAD(MONTH(FROM_UNIXTIME(o.OrderDate)), 2, \'0\'))';
			}

			if ($this->Application->isModuleEnabled('in-auction'))
			{
				$sql = 'SELECT
							'.$period_sql.' AS Period,
							SUM(IF(ISNULL(eod.OptionsSalt), '.$a_expressions[$metric].', 0)) as StoreMetric,
							SUM(IF(ISNULL(eod.OptionsSalt), 0, '.$a_expressions[$metric].')) as eBayMetric
					FROM '.TABLE_PREFIX.'Orders AS o
					LEFT JOIN '.TABLE_PREFIX.'OrderItems AS od
					ON od.OrderId = o.OrderId
					LEFT JOIN '.TABLE_PREFIX.'eBayOrderItems AS eod
					ON od.OptionsSalt = eod.OptionsSalt
					WHERE
						o.Status IN (4,6)
						'.$filter_value.'
						GROUP BY Period';
			} else 	{

				$sql = 'SELECT
							'.$period_sql.' AS Period,
							SUM('.$a_expressions[$metric].') as StoreMetric,
							0 as eBayMetric
					FROM '.TABLE_PREFIX.'Orders AS o
					LEFT JOIN '.TABLE_PREFIX.'OrderItems AS od
					ON od.OrderId = o.OrderId
					WHERE
						o.Status IN (4,6)
						'.$filter_value.'
						GROUP BY Period';

			}

			$a_data = $this->Conn->Query($sql, 'Period');

			// create series array
			$a_serie1 = Array();
			$a_serie2 = Array();
			foreach ($step_labels AS $key => $label)
			{
				$a_serie1[$key] = (isset($a_data[$key]['eBayMetric']) && !is_null($a_data[$key]['eBayMetric'])) ? $a_data[$key]['eBayMetric'] : 0;
				$a_serie2[$key] = (isset($a_data[$key]['StoreMetric']) && !is_null($a_data[$key]['StoreMetric'])) ? $a_data[$key]['StoreMetric'] : 0;
			}

			$show_date_from = date('m/d/Y', $a_report_options['from']);
			$show_date_to = date('m/d/Y', $a_report_options['to']);
			$show_date = ($show_date_from == $show_date_to) ? $show_date_to : $show_date_from.' - '.$show_date_to;

			$this->Application->StoreVar('graph_metric', $object->GetField('Metric').' :: ('.$show_date.') :: '.DOMAIN);
			$this->Application->StoreVar('graph_serie1', serialize($a_serie1));
			$this->Application->StoreVar('graph_serie2', serialize($a_serie2));

			$this->Application->StoreVar('graph_serie1_label', $this->Application->Phrase('la_eBayMarketplace'));
			$this->Application->StoreVar('graph_serie2_label', $this->Application->Phrase('la_OnlineStore'));
			$this->Application->StoreVar('graph_labels', serialize($step_labels));

			return;

		}


		$ebay_joins = '';
		if ($this->Application->isModuleEnabled('in-auction'))
		{
			$ebay_joins = '
				LEFT JOIN '.TABLE_PREFIX.'eBayOrderItems AS eod
				ON od.OptionsSalt = eod.OptionsSalt
			';
		}

		if ($a_report_options['ReportType'] == 1)
		{
			// pie chart by category

			$a_expressions = Array(
				'Qty' => 'od.Quantity',
				'Cost' => 'od.Cost * od.Quantity',
				'Amount' => 'od.Price * od.Quantity',
				'Tax' => 'o.VAT * od.Price * od.Quantity / o.SubTotal',
				'Shipping' => 'o.ShippingCost * od.Price * od.Quantity / o.SubTotal',
				'Processing' => 'o.ProcessingFee * od.Price * od.Quantity / o.SubTotal',
				'Profit' => '(od.Price - od.Cost)  * od.Quantity',
				'StoreQty' => 'IF(ISNULL(eod.OptionsSalt), od.Quantity, 0)',
				'eBayQty' => 'IF(ISNULL(eod.OptionsSalt), 0, od.Quantity)',
				'StoreAmount' => 'IF(ISNULL(eod.OptionsSalt), od.Price * od.Quantity, 0)',
				'eBayAmount' => 'IF(ISNULL(eod.OptionsSalt), 0, od.Price * od.Quantity)',
				'StoreProfit' => 'IF(ISNULL(eod.OptionsSalt), (od.Price - od.Cost) * od.Quantity, 0)',
				'eBayProfit' => 'IF(ISNULL(eod.OptionsSalt), 0, (od.Price - od.Cost) * od.Quantity)',
			);

			$lang = $this->Application->GetVar('m_lang');

			$sql = 'SELECT
						LEFT(c.l'.$lang.'_Name, 60) AS Name,
						c.CategoryId,
						SUM('.$a_expressions[$metric].') as Metric
				FROM '.TABLE_PREFIX.'Orders AS o
				LEFT JOIN '.TABLE_PREFIX.'OrderItems AS od
				ON od.OrderId = o.OrderId
				LEFT JOIN '.TABLE_PREFIX.'Products AS p
				ON p.ProductId = od.ProductId
				LEFT JOIN '.TABLE_PREFIX.'CategoryItems AS ci
				ON ci.ItemResourceId = p.ResourceId
				LEFT JOIN '.TABLE_PREFIX.'Categories AS c
				ON c.CategoryId = ci.CategoryId
				'.$ebay_joins.'
				WHERE
					o.Status IN (4,6)
					'.$filter_value.'
				GROUP BY c.CategoryId
				HAVING NOT ISNULL(CategoryId)
				ORDER BY Metric DESC
				LIMIT 0,8
				';
			$a_data = $this->Conn->Query($sql, 'CategoryId');

			$other_metric = 0;
			if (count($a_data) > 7)
			{
				// gather ids for "others" call
				$ids = join(',', array_keys($a_data));
				$sql = 'SELECT
							SUM('.$a_expressions[$metric].')
					FROM '.TABLE_PREFIX.'Orders AS o
					LEFT JOIN '.TABLE_PREFIX.'OrderItems AS od
					ON od.OrderId = o.OrderId
					LEFT JOIN '.TABLE_PREFIX.'Products AS p
					ON p.ProductId = od.ProductId
					LEFT JOIN '.TABLE_PREFIX.'CategoryItems AS ci
					ON ci.ItemResourceId = p.ResourceId
					LEFT JOIN '.TABLE_PREFIX.'Categories AS c
					ON c.CategoryId = ci.CategoryId
					'.$ebay_joins.'
					WHERE
						o.Status IN (4,6)
						'.$filter_value.'
						AND c.CategoryId NOT IN ('.$ids.')
					';
				$other_metric = $this->Conn->GetOne($sql);
				if ($other_metric != 0)
				{
					$a_data[0] = Array(
						'Metric' => $other_metric,
						'Name' => $this->Application->Phrase('la_text_Others'),
					);
				}
			}


			$show_date_from = date('m/d/Y', $a_report_options['from']);
			$show_date_to = date('m/d/Y', $a_report_options['to']);
			$show_date = ($show_date_from == $show_date_to) ? $show_date_to : $show_date_from.' - '.$show_date_to;

			$this->Application->StoreVar('graph_metric', $this->Application->Phrase('la_text_ReportByTopProductCategories').' '.$object->GetField('Metric').' :: ('.$show_date.') :: '.DOMAIN);
			$this->Application->StoreVar('graph_data', serialize($a_data));
			return;
		}


		if ($a_report_options['ReportType'] == 5)
		{
			// pie chart by product

			$a_expressions = Array(
				'Qty' => 'od.Quantity',
				'Cost' => 'od.Cost * od.Quantity',
				'Amount' => 'od.Price * od.Quantity',
				'Tax' => 'o.VAT * od.Price * od.Quantity / o.SubTotal',
				'Shipping' => 'o.ShippingCost * od.Price * od.Quantity / o.SubTotal',
				'Processing' => 'o.ProcessingFee * od.Price * od.Quantity / o.SubTotal',
				'Profit' => '(od.Price - od.Cost)  * od.Quantity',
				'StoreQty' => 'IF(ISNULL(eod.OptionsSalt), od.Quantity, 0)',
				'eBayQty' => 'IF(ISNULL(eod.OptionsSalt), 0, od.Quantity)',
				'StoreAmount' => 'IF(ISNULL(eod.OptionsSalt), od.Price * od.Quantity, 0)',
				'eBayAmount' => 'IF(ISNULL(eod.OptionsSalt), 0, od.Price * od.Quantity)',
				'StoreProfit' => 'IF(ISNULL(eod.OptionsSalt), (od.Price - od.Cost) * od.Quantity, 0)',
				'eBayProfit' => 'IF(ISNULL(eod.OptionsSalt), 0, (od.Price - od.Cost) * od.Quantity)',
			);

			$lang = $this->Application->GetVar('m_lang');

			$sql = 'SELECT
						LEFT(p.l'.$lang.'_Name, 60) AS Name,
						p.ProductId,
						SUM('.$a_expressions[$metric].') as Metric
				FROM '.TABLE_PREFIX.'Orders AS o
				LEFT JOIN '.TABLE_PREFIX.'OrderItems AS od
				ON od.OrderId = o.OrderId
				LEFT JOIN '.TABLE_PREFIX.'Products AS p
				ON p.ProductId = od.ProductId
				'.$ebay_joins.'
				WHERE
					o.Status IN (4,6)
					'.$filter_value.'
				GROUP BY p.ProductId
				HAVING NOT ISNULL(ProductId)
				ORDER BY Metric DESC
				LIMIT 0,8
				';
			$a_data = $this->Conn->Query($sql, 'ProductId');

			$other_metric = 0;
			if (count($a_data) > 7)
			{
				// gather ids for "others" call
				$ids = join(',', array_keys($a_data));
				$sql = 'SELECT
							SUM('.$a_expressions[$metric].')
					FROM '.TABLE_PREFIX.'Orders AS o
					LEFT JOIN '.TABLE_PREFIX.'OrderItems AS od
					ON od.OrderId = o.OrderId
					LEFT JOIN '.TABLE_PREFIX.'Products AS p
					ON p.ProductId = od.ProductId
					'.$ebay_joins.'
					WHERE
						o.Status IN (4,6)
						'.$filter_value.'
						AND p.ProductId NOT IN ('.$ids.')
					';
				$other_metric = $this->Conn->GetOne($sql);
				if ($other_metric != 0)
				{
					$a_data[0] = Array(
						'Metric' => $other_metric,
						'Name' => $this->Application->Phrase('la_Others'),
					);
				}
			}


			$show_date_from = date('m/d/Y', $a_report_options['from']);
			$show_date_to = date('m/d/Y', $a_report_options['to']);
			$show_date = ($show_date_from == $show_date_to) ? $show_date_to : $show_date_from.' - '.$show_date_to;

			$this->Application->StoreVar('graph_metric', $this->Application->Phrase('la_text_ReportByTopProducts').' '.$object->GetField('Metric').' :: ('.$show_date.') :: '.DOMAIN);
			$this->Application->StoreVar('graph_data', serialize($a_data));
		}

	}

	function GetRandom($params)
	{
		return rand(1,10000000);
	}

	function IsPHPxOrGreater($params)
	{
		$curver = explode(".", phpversion());
		return ($curver[0] >= $params['version']);
	}
}