<?php
/**
* @version	$Id: reports_event_handler.php 15149 2012-03-04 09:05:03Z 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 ReportsEventHandler extends kDBEventHandler {

	/**
	 * Allows to override standard permission mapping
	 *
	 * @return void
	 * @access protected
	 * @see kEventHandler::$permMapping
	 */
	protected function mapPermissions()
	{
		parent::mapPermissions();

		$permissions = Array (
			// user can view any form on front-end
			'OnRunReport' => Array ('self' => 'view'),
			'OnUpdateConfig' => Array ('self' => 'view'),
			'OnChangeStatistics' => Array ('self' => 'view'),
			'OnPieChart' => Array ('self' => 'view'),
			'OnPrintChart' => Array ('self' => 'view'),
			'OnExportReport' => Array ('self' => 'view'),
		);

		$this->permMapping = array_merge($this->permMapping, $permissions);
	}

	function OnRunReport($event)
	{
		$this->Application->LinkVar('reports_finish_t');
		$progress_t = $this->Application->GetVar('progress_t');
		$event->redirect = $progress_t;

		$items_info = $this->Application->GetVar( $event->getPrefixSpecial(true) );
		if($items_info) $field_values = array_shift($items_info);

		$object = $event->getObject( Array('skip_autoload' => true) );
		$object->SetFieldsFromHash($field_values, $this->getRequestProtectedFields($field_values));
		$object->UpdateFormattersMasterFields();

		$field_values['offset'] = 0;
		$table_name = TABLE_PREFIX.'ses_'.$this->Application->GetSID().'_SaleReport';
		$field_values['table_name'] = $table_name;
		$this->Conn->Query('DROP TABLE IF EXISTS '.$table_name);

		$filter_value = '';
		$from = $object->GetDBField('FromDateTime');
		$to = $object->GetDBField('ToDateTime');

		$day_seconds = 23 * 60 * 60 + 59 * 60 + 59;
		if ($from && !$to) {
			$to = $from + $day_seconds;
		}
		elseif (!$from && $to) {
			$from = $to - $day_seconds;
		}

		if ($from && $to) {
			$filter_value = 'AND o.OrderDate >= '.$from.' AND o.OrderDate <= '.$to;
		}

		$ebay_table_fields = '';
		$ebay_joins = '';
		$ebay_query_fields = '';

		$user_id = $this->Application->RecallVar('user_id');
		$sql = 'DELETE FROM '.TABLE_PREFIX.'UserPersistentSessionData
			WHERE
				PortalUserId = "'.$user_id.'"
				AND VariableName LIKE \'rep_columns_%\'';
		$this->Conn->Query($sql);

		if ($this->Application->isModuleEnabled('in-auction'))
		{
			if (in_array($field_values['ReportType'], Array(1,5)))  // not overall.
			{
				$ebay_table_fields = ',
					StoreQty int(11) NOT NULL DEFAULT 0,
					eBayQty int(11) NOT NULL DEFAULT 0,
					StoreAmount double(10,4) NOT NULL DEFAULT 0,
					eBayAmount double(10,4) NOT NULL DEFAULT 0,
					StoreProfit double(10,4) NOT NULL DEFAULT 0,
					eBayProfit double(10,4) NOT NULL DEFAULT 0';

				$ebay_joins = '
					LEFT JOIN '.TABLE_PREFIX.'eBayOrderItems AS eod
					ON od.OptionsSalt = eod.OptionsSalt
				';

				$ebay_query_fields = ',
					SUM(IF(ISNULL(eod.OptionsSalt), od.Quantity, 0)) as StoreQty,
					SUM(IF(ISNULL(eod.OptionsSalt), 0, od.Quantity)) as eBayQty,
					SUM(IF(ISNULL(eod.OptionsSalt), od.Price * od.Quantity, 0)) as StoreAmount,
					SUM(IF(ISNULL(eod.OptionsSalt), 0, od.Price * od.Quantity)) as eBayAmount,
					SUM(IF(ISNULL(eod.OptionsSalt), (od.Price - od.Cost) * od.Quantity, 0)) as StoreProfit,
					SUM(IF(ISNULL(eod.OptionsSalt), 0, (od.Price - od.Cost) * od.Quantity)) as eBayProfit
				';
			}

		}

		if ($field_values['ReportType'] == 1) { // by Category
			$q = 'CREATE TABLE '.$table_name.' (
							CategoryId int(11) NOT NULL DEFAULT 0,
							Qty int(11) NOT NULL DEFAULT 0,
							Cost double(10,4) NOT NULL DEFAULT 0,
							Amount double(10,4) NOT NULL DEFAULT 0,
							Tax double(10,4) NOT NULL DEFAULT 0,
							Shipping double(10,4) NOT NULL DEFAULT 0,
							Processing double(10,4) NOT NULL DEFAULT 0,
							Profit double(10,4) NOT NULL DEFAULT 0
							'.$ebay_table_fields.'
						)';
			$field_values['total'] = $this->Conn->GetOne('SELECT COUNT(*) FROM '.TABLE_PREFIX.'Categories');
			$this->Conn->Query($q);

			$q = 'INSERT INTO '.$field_values['table_name'].'
						SELECT
							c.CategoryId,
							SUM(od.Quantity) as Qty,
							SUM(od.Cost * od.Quantity) as Cost,
							SUM(od.Price * od.Quantity) as SaleAmount,
							SUM(o.VAT * od.Price * od.Quantity / o.SubTotal) as Tax,
							SUM(o.ShippingCost * od.Price * od.Quantity / o.SubTotal) as Shipping,
							SUM(o.ProcessingFee * od.Price * od.Quantity / o.SubTotal) as Processing,
							SUM((od.Price - od.Cost) * od.Quantity) as Profit'
							.$ebay_query_fields.'
						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)
							AND
							ci.PrimaryCat = 1
							'.$filter_value.'
						GROUP BY c.CategoryId
						HAVING NOT ISNULL(CategoryId)
						';
			$this->Conn->Query($q);
		}
		elseif ($field_values['ReportType'] == 2) { // by User
			$q = 'CREATE TABLE '.$table_name.' (
							PortalUserId int(11) NOT NULL DEFAULT 0,
							Qty int(11) NOT NULL DEFAULT 0,
							Cost double(10,4) NOT NULL DEFAULT 0,
							Amount double(10,4) NOT NULL DEFAULT 0,
							Tax double(10,4) NOT NULL DEFAULT 0,
							Shipping double(10,4) NOT NULL DEFAULT 0,
							Processing double(10,4) NOT NULL DEFAULT 0,
							Profit double(10,4) NOT NULL DEFAULT 0
						)';
			$field_values['total'] = $this->Conn->GetOne('SELECT COUNT(*) FROM '.TABLE_PREFIX.'Categories');
			$this->Conn->Query($q);

			$q = 'INSERT INTO '.$field_values['table_name'].'
						SELECT
							u.PortalUserId,
							SUM(od.Quantity) as Qty,
							SUM(od.Cost * od.Quantity) as Cost,
							SUM(od.Price * od.Quantity) as SaleAmount,
							SUM(o.VAT * od.Price * od.Quantity / o.SubTotal) as Tax,
							SUM(o.ShippingCost * od.Price * od.Quantity / o.SubTotal) as Shipping,
							SUM(o.ProcessingFee * od.Price * od.Quantity / o.SubTotal) as Processing,
							SUM((od.Price - od.Cost) * od.Quantity) as Profit
						FROM '.TABLE_PREFIX.'Orders AS o
						LEFT JOIN '.TABLE_PREFIX.'OrderItems AS od
						ON od.OrderId = o.OrderId
						LEFT JOIN '.TABLE_PREFIX.'Users AS u
						ON u.PortalUserId = o.PortalUserId
						WHERE
							o.Status IN (4,6)
							'.$filter_value.'
						GROUP BY u.PortalUserId
						HAVING NOT ISNULL(PortalUserId)
						';
			$this->Conn->Query($q);
		}
		elseif ($field_values['ReportType'] == 5) { // by Product
			$q = 'CREATE TABLE '.$table_name.' (
							ProductId int(11) NOT NULL DEFAULT 0,
							Qty int(11) NOT NULL DEFAULT 0,
							Cost double(10,4) NOT NULL DEFAULT 0,
							Amount double(10,4) NOT NULL DEFAULT 0,
							Tax double(10,4) NOT NULL DEFAULT 0,
							Shipping double(10,4) NOT NULL DEFAULT 0,
							Processing double(10,4) NOT NULL DEFAULT 0,
							Profit double(10,4) NOT NULL DEFAULT 0'
							.$ebay_table_fields.'
						)';
			$field_values['total'] = $this->Conn->GetOne('SELECT COUNT(*) FROM '.TABLE_PREFIX.'Products');
			$this->Conn->Query($q);

			$q = 'INSERT INTO '.$field_values['table_name'].'
						SELECT
							p.ProductId,
							SUM(od.Quantity) as Qty,
							SUM(od.Cost * od.Quantity) as Cost,
							SUM(od.Price * od.Quantity) as SaleAmount,
							SUM(o.VAT * od.Price * od.Quantity / o.SubTotal) as Tax,
							SUM(o.ShippingCost * od.Price * od.Quantity / o.SubTotal) as Shipping,
							SUM(o.ProcessingFee * od.Price * od.Quantity / o.SubTotal) as Processing,
							SUM((od.Price - od.Cost)  * od.Quantity) as Profit'
							.$ebay_query_fields.'
						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)
						';
			$this->Conn->Query($q);
		}
		elseif ($field_values['ReportType'] == 12) { // Overall
			$q = 'CREATE TABLE '.$table_name.' (
							Marketplace tinyint(1) NOT NULL DEFAULT 0,
							Qty int(11) NOT NULL DEFAULT 0,
							Cost double(10,4) NOT NULL DEFAULT 0,
							Amount double(10,4) NOT NULL DEFAULT 0,
							Tax double(10,4) NOT NULL DEFAULT 0,
							Shipping double(10,4) NOT NULL DEFAULT 0,
							Processing double(10,4) NOT NULL DEFAULT 0,
							Profit double(10,4) NOT NULL DEFAULT 0
						)';
			$this->Conn->Query($q);

			if ($this->Application->isModuleEnabled('in-auction'))
			{
				$field_values['total'] = 2;

				$q = 'INSERT INTO '.$field_values['table_name'].'
							SELECT
								1 AS Marketplace,
								SUM(IF(ISNULL(eod.OptionsSalt), od.Quantity, 0)) as Qty,
								SUM(IF(ISNULL(eod.OptionsSalt), od.Cost * od.Quantity, 0)) as Cost,
								SUM(IF(ISNULL(eod.OptionsSalt), od.Price * od.Quantity, 0)) as SaleAmount,
								SUM(IF(ISNULL(eod.OptionsSalt), o.VAT * od.Price * od.Quantity / o.SubTotal, 0)) as Tax,
								SUM(IF(ISNULL(eod.OptionsSalt), o.ShippingCost * od.Price * od.Quantity / o.SubTotal, 0)) as Shipping,
								SUM(IF(ISNULL(eod.OptionsSalt), o.ProcessingFee * od.Price * od.Quantity / o.SubTotal, 0)) as Processing,
								SUM(IF(ISNULL(eod.OptionsSalt), (od.Price - od.Cost)  * od.Quantity, 0)) as Profit
							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;
				$this->Conn->Query($q);


				$q = 'INSERT INTO '.$field_values['table_name'].'
							SELECT
								2 AS Marketplace,
								SUM(IF(ISNULL(eod.OptionsSalt), 0, od.Quantity)) as Qty,
								SUM(IF(ISNULL(eod.OptionsSalt), 0, od.Cost * od.Quantity)) as Cost,
								SUM(IF(ISNULL(eod.OptionsSalt), 0, od.Price * od.Quantity)) as SaleAmount,
								SUM(IF(ISNULL(eod.OptionsSalt), 0, o.VAT * od.Price * od.Quantity / o.SubTotal)) as Tax,
								SUM(IF(ISNULL(eod.OptionsSalt), 0, o.ShippingCost * od.Price * od.Quantity / o.SubTotal)) as Shipping,
								SUM(IF(ISNULL(eod.OptionsSalt), 0, o.ProcessingFee * od.Price * od.Quantity / o.SubTotal)) as Processing,
								SUM(IF(ISNULL(eod.OptionsSalt), 0, (od.Price - od.Cost)  * od.Quantity)) as Profit
							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;
				$this->Conn->Query($q);
			} else {
				$field_values['total'] = 1;
				$q = 'INSERT INTO '.$field_values['table_name'].'
							SELECT
								1 AS Marketplace,
								SUM(od.Quantity) as Qty,
								SUM(od.Cost * od.Quantity) as Cost,
								SUM(od.Price * od.Quantity) as SaleAmount,
								SUM(o.VAT * od.Price * od.Quantity / o.SubTotal) as Tax,
								SUM(o.ShippingCost * od.Price * od.Quantity / o.SubTotal) as Shipping,
								SUM(o.ProcessingFee * od.Price * od.Quantity / o.SubTotal) as Processing,
								SUM((od.Price - od.Cost)  * od.Quantity) as Profit
							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;
				$this->Conn->Query($q);

			}
		}

		$vars = array('rep_Page', 'rep_Sort1', 'rep_Sort1_Dir', 'rep_Sort2', 'rep_Sort2_Dir');
		foreach ($vars as $var_name) {
			$this->Application->RemoveVar($var_name);
		}

		//temporary
		$event->redirect = $this->Application->GetVar('reports_finish_t');

		$field_values['from'] = $from;
		$field_values['to'] = $to;

		$this->Application->StoreVar('report_options', serialize($field_values));
	}

	function OnUpdateConfig($event)
	{
		$report = $this->Application->RecallVar('report_options');
		if (!$report) {
			return ;
		}

		$field_values = unserialize($report);

		$rep_options = $this->Application->getUnitOptions('rep');
		$new_options = Array ();

		$new_options['TableName'] = $field_values['table_name'];

		$new_options['Fields'] = Array (
			'Qty' => Array ('type' => 'float', 'formatter' => 'kFormatter', 'format' => '%d', 'default' => 0, 'totals' => 'sum'),
			'Cost' => Array ('type' => 'float', 'formatter' => 'kFormatter', 'format' => '%.2f', 'default' => 0, 'totals' => 'sum'),
			'Amount' => Array ('type' => 'float', 'formatter' => 'kFormatter', 'format' => '%.2f', 'default' => 0, 'totals' => 'sum'),
			'Tax' => Array ('type' => 'float', 'formatter' => 'kFormatter', 'format' => '%.2f', 'default' => 0, 'totals' => 'sum'),
			'Shipping' => Array ('type' => 'float', 'formatter' => 'kFormatter', 'format' => '%.2f', 'default' => 0, 'totals' => 'sum'),
			'Processing' => Array ('type' => 'float', 'formatter' => 'kFormatter', 'format' => '%.2f', 'default' => 0, 'totals' => 'sum'),
			'Profit' => Array ('type' => 'float', 'formatter' => 'kFormatter', 'format' => '%.2f', 'default' => 0, 'totals' => 'sum'),
		);

		if ( $this->Application->isModuleEnabled('in-auction') ) {
			if ( in_Array ($field_values['ReportType'], Array (1, 5)) ) {
				$new_options['Fields'] += Array (
					'StoreQty' => Array ('type' => 'int', 'formatter' => 'kFormatter', 'format' => '%d', 'default' => 0, 'totals' => 'sum'),
					'StoreAmount' => Array ('type' => 'float', 'formatter' => 'kFormatter', 'format' => '%.2f', 'default' => 0, 'totals' => 'sum'),
					'StoreProfit' => Array ('type' => 'float', 'formatter' => 'kFormatter', 'format' => '%.2f', 'default' => 0, 'totals' => 'sum'),
					'eBayQty' => Array ('type' => 'int', 'formatter' => 'kFormatter', 'format' => '%d', 'default' => 0, 'totals' => 'sum'),
					'eBayAmount' => Array ('type' => 'float', 'formatter' => 'kFormatter', 'format' => '%.2f', 'default' => 0, 'totals' => 'sum'),
					'eBayProfit' => Array ('type' => 'float', 'formatter' => 'kFormatter', 'format' => '%.2f', 'default' => 0, 'totals' => 'sum'),
				);
			}
		}

		if ($field_values['ReportType'] == 1) { // by Category

			$new_options['ListSQLs'][''] =
				'SELECT %1$s.* %2$s FROM %1$s
				 LEFT JOIN '.TABLE_PREFIX.'Categories AS c
				 ON c.CategoryId = %1$s.CategoryId';
			$new_options['Grids']['Default'] = Array (
				'Icons' => Array (
					'default' => 'icon16_item.png',
					'module' => 'core',
				),
				'Fields' => Array (
					'CategoryName' => Array ('title' => 'la_col_CategoryName', 'filter_block' => 'grid_like_filter'),
					'Qty' => Array ('td_style' => 'text-align: center', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'StoreQty' => Array ('title' => 'la_col_StoreQty', 'td_style' => 'text-align: center', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'eBayQty' => Array ('title' => 'la_col_eBayQty', 'td_style' => 'text-align: center', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Cost' => Array ('td_style' => 'text-align: right', 'total' => 'sum', 'hidden' => 1, 'filter_block' => 'grid_range_filter'),
					'Amount' => Array ('title' => 'la_col_GMV', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'StoreAmount' => Array ('title' => 'la_col_StoreGMV', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'eBayAmount' => Array ('title' => 'la_col_eBayGMV', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Tax' => Array ('title' => 'la_col_Tax', 'td_style' => 'text-align: right', 'total' => 'sum', 'hidden' => 1, 'filter_block' => 'grid_range_filter'),
					'Shipping' => Array ('title' => 'la_col_Shipping', 'td_style' => 'text-align: right', 'total' => 'sum', 'hidden' => 1, 'filter_block' => 'grid_range_filter'),
					'Processing' => Array ('title' => 'la_col_Processing', 'td_style' => 'text-align: right', 'total' => 'sum', 'hidden' => 1, 'filter_block' => 'grid_range_filter'),
					'Profit' => Array ('title' => 'la_col_Profit', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'StoreProfit' => Array ('title' => 'la_col_StoreProfit', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'eBayProfit' => Array ('title' => 'la_col_eBayProfit', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
				),

			);

			if (!$this->Application->isModuleEnabled('in-auction')) {
				$a_fields =& $new_options['Grids']['Default']['Fields'];
				unset($a_fields['StoreQty']);
				unset($a_fields['eBayQty']);
				unset($a_fields['StoreAmount']);
				unset($a_fields['eBayAmount']);
				unset($a_fields['StoreProfit']);
				unset($a_fields['eBayProfit']);
			}

			$new_options['VirtualFields'] = array_merge($rep_options['VirtualFields'], Array (
				'CategoryName' => Array ('type' => 'string', 'default' => ''),
	            'Metric' => Array (
	            	'type' => 'int',
	            	'formatter' => 'kOptionsFormatter',
	            	'options' => $this->GetMetricOptions($new_options, 'CategoryName'),
	            	'use_phrases' => 1,
	            	'default' => 0,
	            ),
			));

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

			// products root category
			$products_category_id = $this->Application->findModule('Name', 'In-Commerce', 'RootCat');
			// get root category name
			$sql = 'SELECT LENGTH(l' . $lang . '_CachedNavbar)
					FROM ' . TABLE_PREFIX . 'Categories
					WHERE CategoryId = '.$products_category_id;
			$root_length = $this->Conn->GetOne($sql) + 4;

			$new_options['CalculatedFields'][''] = array(
				'CategoryName' => 'REPLACE(SUBSTR(c.l'.$lang.'_CachedNavbar, '.$root_length.'), "&|&", " > ")',
			);
		}
		elseif ($field_values['ReportType'] == 2) { // by User
			$new_options['ListSQLs'][''] =
				'SELECT %1$s.* %2$s FROM %1$s
				 LEFT JOIN '.TABLE_PREFIX.'Users AS u
				 ON u.PortalUserId = %1$s.PortalUserId';

			$new_options['Grids']['Default'] = Array (
				'Icons' => Array (
								'default' => 'icon16_item.png',
								'module' => 'core',
							),
				'Fields' => Array (
					'Login' => Array ('filter_block' => 'grid_like_filter'),
					'FirstName' => Array ('filter_block' => 'grid_like_filter'),
					'LastName' => Array ('filter_block' => 'grid_like_filter'),
					'Qty' => Array ('td_style' => 'text-align: center', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Cost' => Array ('td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Amount' => Array ('title' => 'la_col_GMV', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Tax' => Array ('title' => 'la_col_Tax', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Shipping' => Array ('title' => 'la_col_Shipping', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Processing' => Array ('title' => 'la_col_Processing', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Profit' => Array ('title' => 'la_col_Profit', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
				),
			);

			$new_options['VirtualFields'] = array_merge($rep_options['VirtualFields'], Array (
				'Login' => Array ('type' => 'string', 'default' => ''),
				'FirstName' => Array ('type' => 'string', 'default' => ''),
				'LastName' => Array ('type' => 'string', 'default' => ''),
			));

			$new_options['CalculatedFields'][''] = Array (
				'Login' => 'u.Username',
				'FirstName' => 'u.FirstName',
				'LastName' => 'u.LastName',
			);
		}
		elseif ($field_values['ReportType'] == 5) { // by Product

			$new_options['ListSQLs'][''] =
				'SELECT %1$s.* %2$s FROM %1$s
				 LEFT JOIN '.TABLE_PREFIX.'Products AS p
				 ON p.ProductId = %1$s.ProductId';

			$new_options['Grids']['Default'] = Array (
				'Icons' => Array (
					'default' => 'icon16_item.png',
					'module' => 'core',
					),
				'Fields' => Array (
					'ProductName' => Array ('title' => 'la_col_ProductName', 'filter_block' => 'grid_like_filter'),
					'Qty' => Array ('td_style' => 'text-align: center', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'StoreQty' => Array ('title' => 'la_col_StoreQty', 'td_style' => 'text-align: center', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'eBayQty' => Array ('title' => 'la_col_eBayQty', 'td_style' => 'text-align: center', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Cost' => Array ('td_style' => 'text-align: right', 'total' => 'sum', 'hidden' => 1, 'filter_block' => 'grid_range_filter'),
					'Amount' => Array ('title' => 'la_col_GMV', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'StoreAmount' => Array ('title' => 'la_col_StoreGMV', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'eBayAmount' => Array ('title' => 'la_col_eBayGMV', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Tax' => Array ('title' => 'la_col_Tax', 'td_style' => 'text-align: right', 'total' => 'sum', 'hidden' => 1, 'filter_block' => 'grid_range_filter'),
					'Shipping' => Array ('title' => 'la_col_Shipping', 'td_style' => 'text-align: right', 'total' => 'sum', 'hidden' => 1, 'filter_block' => 'grid_range_filter'),
					'Processing' => Array ('title' => 'la_col_Processing', 'td_style' => 'text-align: right', 'total' => 'sum', 'hidden' => 1, 'filter_block' => 'grid_range_filter'),
					'Profit' => Array ('title' => 'la_col_Profit', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'StoreProfit' => Array ('title' => 'la_col_StoreProfit', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'eBayProfit' => Array ('title' => 'la_col_eBayProfit', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
				),

			);

			if (!$this->Application->isModuleEnabled('in-auction'))
			{
				$a_fields =& $new_options['Grids']['Default']['Fields'];
				unset($a_fields['StoreQty']);
				unset($a_fields['eBayQty']);
				unset($a_fields['StoreAmount']);
				unset($a_fields['eBayAmount']);
				unset($a_fields['StoreProfit']);
				unset($a_fields['eBayProfit']);
			}


			$new_options['VirtualFields'] = array_merge($rep_options['VirtualFields'], Array (
				'ProductName' => Array ('type' => 'string', 'default' => ''),
	            'Metric' => Array (
	            	'type' => 'int',
	            	'formatter' => 'kOptionsFormatter',
	            	'options' => $this->GetMetricOptions($new_options, 'ProductName'),
	            	'use_phrases' => 1,
	            	'default' => 0
	            ),
			));

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

			$new_options['CalculatedFields'][''] = Array (
				'ProductName' => 'p.l'.$lang.'_Name',
			);
		}
		elseif ($field_values['ReportType'] == 12) { // Overall

			$new_options['ListSQLs'][''] =
				'SELECT %1$s.* %2$s FROM %1$s';

			$new_options['Fields']['Marketplace'] = Array (
				'formatter' => 'kOptionsFormatter',
				'options' => Array (
					1 => 'la_OnlineStore',
					2 => 'la_eBayMarketplace',
				),
				'use_phrases' => 1,
				'default' => 1
			);

			$new_options['Grids']['Default'] = Array(
				'Icons' => Array(
					'default' => 'icon16_item.png',
					'module' => 'core',
				),
				'Fields' => Array(
					'Marketplace' => Array ('title' => 'la_col_Marketplace', 'filter_block' => 'grid_options_filter'),
					'Qty' => Array ('td_style' => 'text-align: center', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Cost' => Array ('td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Amount' => Array ('title' => 'la_col_GMV', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Tax' => Array ('title' => 'la_col_Tax', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Shipping' => Array ('title' => 'la_col_Shipping', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Processing' => Array ('title' => 'la_col_Processing', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
					'Profit' => Array ('title' => 'la_col_Profit', 'td_style' => 'text-align: right', 'total' => 'sum', 'filter_block' => 'grid_range_filter'),
				),

			);


			$new_options['VirtualFields'] = array_merge($rep_options['VirtualFields'], array(
	            'Metric' => Array (
	            	'type' => 'int',
	            	'formatter' => 'kOptionsFormatter',
	            	'options' => $this->GetMetricOptions($new_options, 'Marketplace'),
	            	'use_phrases' => 1,
	            	'default' => 0
	            ),
			));

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

		}

		$new_options['ListSortings'] = Array(
			'' => Array(
				'Sorting' => Array('Amount' => 'desc'),
			)
		);

		foreach ($new_options as $key => $val) {
			$this->Application->setUnitOption('rep', $key, $val);
		}
	}

	/**
	 * Enter description here...
	 *
	 * @param kdbItem $object
	 * @param string $search_field
	 * @param string $value
	 * @param string $type
	 */
	function processRangeField(&$object, $search_field, $type)
	{
		$value = $object->GetField($search_field);
		if (!$value) return false;

		$lang_current = $this->Application->recallObject('lang.current');
		$dt_separator = getArrayValue($object->GetFieldOptions($search_field), 'date_time_separator');
		if (!$dt_separator) {
			$dt_separator = ' ';
		}

		$time = ($type == 'from') ? adodb_mktime(0, 0, 0) : adodb_mktime(23, 59, 59);
		$time = adodb_date($lang_current->GetDBField('InputTimeFormat'), $time);

		$full_value = $value.$dt_separator.$time;

		$formatter = $this->Application->recallObject( $object->GetFieldOption($search_field, 'formatter') );

		$value_ts = $formatter->Parse($full_value, $search_field, $object);

		if ( $object->GetErrorPseudo($search_field) ) {
			// invalid format -> ignore this date in search
			$object->RemoveError($search_field);

			return false;
		}
		return $value_ts;
	}

	/**
	 * Generate Metric Field Options
	 *
	 * @param array $a_config_options
	 * @param string $exclude_field
	 */
	function GetMetricOptions(&$a_config_options, $exclude_field)
	{
		$a_ret = Array();
		foreach ($a_config_options['Grids']['Default']['Fields'] AS $field => $a_options)
		{
			if ($field == $exclude_field)
			{
				continue;
			}
			$a_ret[$field] = $a_options['title'];
		}
		return $a_ret;
	}

	function OnChangeStatistics($event)
	{
		$this->Application->StoreVar('ChartMetric', $this->Application->GetVar('metric'));
	}

	function OnPieChart($event)
	{

		$ChartHelper = $this->Application->recallObject('ChartHelper');

		header("Content-type: image/png");

		$width = $event->getEventParam('width');
		if (!$width) {
			$width = 800;
		}

		$height = $event->getEventParam('height');
		if (!$height) {
			$height = 600;
		}

		$a_data = unserialize($this->Application->RecallVar('graph_data'));
		$chart = new LibchartPieChart($width, $height);

		$dataSet = new LibchartXYDataSet();
		foreach ($a_data AS $key=>$a_values)
		{
			$dataSet->addPoint(new LibchartPoint($a_values['Name'], $a_values['Metric']));
//			$dataSet->addPoint(new LibchartPoint($a_values['Name'].' ('.$a_values['Metric'].')', $a_values['Metric']));
		}

		$chart->setDataSet($dataSet);

		$chart->setTitle($this->Application->RecallVar('graph_metric'));
		$chart->render();
		$event->status = kEvent::erSTOP;
	}

	/** Generates png-chart output
	 *
	 * @param kEvent $event
	 */

	function OnPrintChart($event)
	{
		$ChartHelper = $this->Application->recallObject('ChartHelper');

		header("Content-type: image/png");

		$width = $this->Application->GetVar('width');
		if ($width == 0)
		{
			$width = 800;
		}

		$height = $this->Application->GetVar('height');
		if ($height == 0)
		{
			$height = 400;
		}

		$chart = new LibchartLineChart($width, $height);

		$a_labels = unserialize($this->Application->RecallVar('graph_labels'));

		if ($this->Application->isModuleEnabled('in-auction'))
		{
			$serie1 = new LibchartXYDataSet();
			$a_serie = unserialize($this->Application->RecallVar('graph_serie1'));
			foreach ($a_labels AS $key=>$value)
			{
				$serie1->addPoint(new LibchartPoint($value, $a_serie[$key]));
			}
		}

		$serie2 = new LibchartXYDataSet();
		$a_serie = unserialize($this->Application->RecallVar('graph_serie2'));
		foreach ($a_labels AS $key=>$value)
		{
			$serie2->addPoint(new LibchartPoint($value, $a_serie[$key]));
		}

		$dataSet = new LibchartXYSeriesDataSet();
		if ($this->Application->isModuleEnabled('in-auction'))
		{
			$dataSet->addSerie($this->Application->RecallVar('graph_serie1_label'), $serie1);
		}

		$dataSet->addSerie($this->Application->RecallVar('graph_serie2_label'), $serie2);

		$chart->setDataSet($dataSet);

		$chart->setTitle($this->Application->RecallVar('graph_metric'));
		$Plot =& $chart->getPlot();
		$Plot->setGraphCaptionRatio(0.7);
		$chart->render();

		$event->status = kEvent::erSTOP;

	}

	function OnExportReport($event)
	{
		$report = $this->Application->recallObject($event->getPrefixSpecial(),'rep_List',Array('skip_counting'=>true,'per_page'=>-1) );
		/* @var $report kDBList*/

		$ReportItem = $this->Application->recallObject('rep.item', 'rep', Array('skip_autoload' => true));
		/* @var $ReportItem kDBItem*/

 		$a_grids = $this->Application->getUnitOption('rep', 'Grids');
 		$a_fields = $a_grids['Default']['Fields'];
		$ret = '';

		foreach ($a_fields AS $field => $a_props)
		{
			$ret .= '<commas>'.$field.'<commas><tab>';
		}

		$ret = substr($ret, 0, strlen($ret) - 5).'<cr>';


		$report->Query(true);
		$report->GoFirst();

		$counter = 0;
		$a_totals = Array();

		foreach ($a_fields AS $field => $a_props) {
			$counter++;
			if ($counter == 1)
			{
				continue;
			}
			$a_totals[$field] = 0;
		}

		foreach($report->Records as $a_row) {
			$ReportItem->SetFieldsFromHash($a_row);
			$row = '';
			foreach ($a_fields AS $field => $a_props)
			{
				$row .= '<commas>'.$ReportItem->GetField($field).'<commas><tab>';
				$a_totals[$field] += $a_row[$field];
			}
			$ret .= substr($row, 0, strlen($row) - 5).'<cr>';
		}

		// totals
		$ReportItem->SetFieldsFromHash($a_totals);
		$counter = 0;
		foreach ($a_fields AS $field => $a_props)
		{
			$counter++;
			if ($counter == 1)
			{
				$row = '<commas><commas><tab>';
				continue;
			}
			$row .= '<commas>'.$ReportItem->GetField($field).'<commas><tab>';
		}
		$ret .= substr($row, 0, strlen($row) - 5).'<cr>';

		$ret = str_replace("\r",'', $ret);
		$ret = str_replace("\n",'', $ret);
		$ret = str_replace('"','\'\'', $ret);

		$ret = str_replace('<commas>','"', $ret);
		$ret = str_replace('<tab>',',', $ret);
		$ret = str_replace('<cr>',"\r", $ret);

		$report_options = unserialize($this->Application->RecallVar('report_options'));

		switch ($report_options['ReportType'])
		{
			case 1:
				$file_name = '-ByCategory';
				break;
			case 2:
				$file_name = '-ByUser';
				break;
			case 5:
				$file_name = '-ByProduct';
				break;
			case 12:
				$file_name = '';
				break;
		}

		header("Content-type: application/txt");
		header("Content-length: ".(string)strlen($ret));
		header("Content-Disposition: attachment; filename=\"".html_entity_decode('SalesReport'.$file_name.'-'.date('d-M-Y').'.csv')."\"");
		header("Pragma: no-cache"); //some IE-fixing stuff
		echo $ret;
		exit();
	}
}