Page MenuHomeIn-Portal Phabricator
Files F1102346

reports_event_handler.php
No OneTemporary
Actions

File Metadata

Created
Mon, Aug 18, 5:59 PM

reports_event_handler.php
View Options

<?php
/**
* @version $Id: reports_event_handler.php 16798 2024-08-16 08:27:17Z 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(kEvent $event)
{
$this->Application->LinkVar('reports_finish_t');
$progress_t = $this->Application->GetVar('progress_t');
$event->redirect = $progress_t;
$field_values = $this->getSubmittedFields($event);
/** @var kDBItem $object */
$object = $event->getObject( Array('skip_autoload' => true) );
$object->SetFieldsFromHash($field_values);
$event->setEventParam('form_data', $field_values);
$object->UpdateFormattersMasterFields();
$field_values['offset'] = 0;
$field_values['export_ignore'] = '';
$table_name = TABLE_PREFIX . 'ses_' . $this->Application->GetSID(Session::PURPOSE_REFERENCE) . '_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
$field_values['id_field'] = $field_values['export_ignore'] = 'CategoryId';
$field_values['grid'] = 'ByCategory';
$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
$field_values['id_field'] = $field_values['export_ignore'] = 'PortalUserId';
$field_values['grid'] = 'ByUser';
$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
$field_values['id_field'] = $field_values['export_ignore'] = 'ProductId';
$field_values['grid'] = 'ByProduct';
$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
$field_values['id_field'] = 'Marketplace';
$field_values['grid'] = '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);
}
}
$event->CallSubEvent('OnResetSorting');
//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);
$grid = $field_values['grid'];
$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['Fields']['CategoryId'] = Array ('type' => 'int', 'default' => null);
$new_options['Grids'][$grid] = Array (
'Icons' => Array (
'default' => 'icon16_item.png',
'module' => 'core',
),
'Fields' => Array (
'CategoryId' => Array ('title' => 'column:la_fld_Id', 'filter_block' => 'grid_range_filter', 'hidden' => 1),
'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'][$grid]['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, array('CategoryId', 'CategoryName'), $grid),
'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['Fields']['PortalUserId'] = Array ('type' => 'int', 'default' => null);
$new_options['Grids'][$grid] = Array (
'Icons' => Array (
'default' => 'icon16_item.png',
'module' => 'core',
),
'Fields' => Array (
'PortalUserId' => Array ('title' => 'column:la_fld_Id', 'filter_block' => 'grid_range_filter', 'hidden' => 1),
'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['Fields']['ProductId'] = Array ('type' => 'int', 'default' => null);
$new_options['Grids'][$grid] = Array (
'Icons' => Array (
'default' => 'icon16_item.png',
'module' => 'core',
),
'Fields' => Array (
'ProductId' => Array ('title' => 'column:la_fld_Id', 'filter_block' => 'grid_range_filter', 'hidden' => 1),
'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'][$grid]['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, array('ProductId', 'ProductName'), $grid),
'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 (
'type' => 'int',
'formatter' => 'kOptionsFormatter',
'options' => Array (
1 => 'la_OnlineStore',
2 => 'la_eBayMarketplace',
),
'use_phrases' => 1,
'default' => 1
);
$new_options['Grids'][$grid] = 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, array('Marketplace'), $grid),
'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 Config options.
* @param array $exclude_fields Exclude fields.
* @param string $grid Grid.
*
* @return array
*/
function GetMetricOptions(&$a_config_options, array $exclude_fields, $grid)
{
$a_ret = array();
foreach ( $a_config_options['Grids'][$grid]['Fields'] as $field => $a_options ) {
if ( in_array($field, $exclude_fields) ) {
continue;
}
$a_ret[$field] = isset($a_options['title']) ? $a_options['title'] : 'column:la_fld_' . $field;
}
return $a_ret;
}
function OnChangeStatistics($event)
{
$this->Application->StoreVar('ChartMetric', $this->Application->GetVar('metric'));
}
function OnPieChart($event)
{
$ChartHelper = $this->Application->recallObject('ChartHelper');
$this->Application->setContentType('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');
$this->Application->setContentType('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)
{
/** @var kDBList $report */
$report = $this->Application->recallObject($event->getPrefixSpecial(),'rep_List',Array('skip_counting'=>true,'per_page'=>-1) );
/** @var kDBItem $ReportItem */
$ReportItem = $this->Application->recallObject('rep.item', 'rep', Array('skip_autoload' => true));
$report_options = unserialize($this->Application->RecallVar('report_options'));
$a_grids = $this->Application->getUnitOption('rep', 'Grids');
$a_fields = $a_grids[$report_options['grid']]['Fields'];
if ( $report_options['export_ignore'] ) {
unset($a_fields[$report_options['export_ignore']]);
}
$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) {
// TODO: maybe this should be SetDBFieldsFromHash instead, because all data comes from inside.
$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
// TODO: maybe this should be SetDBFieldsFromHash instead, because all data comes from inside.
$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);
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();
}
}

Event Timeline

In-Portal CMS · In-Portal Community Pages · In-Portal Issue Tracker · In-Portal Repository Browser · Quality Assurance · In-Portal CI Server