Page MenuHomeIn-Portal Phabricator
Files F1949920

reports_tag_processor.php
No OneTemporary
Actions

File Metadata

Created
Thu, Oct 29, 9:08 AM

reports_tag_processor.php
View Options

<?php
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.'Category 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.'Category 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));
$object->ID = 1;
$object->SetDBField('Metric', $metric);
// echo '<pre>';
// print_r($a_report_options);
// echo date('m/d/Y H:i:s', $a_report_options['from']).'<br>';
// echo date('m/d/Y H:i:s', $a_report_options['to']).'<br>';
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\'))';
}
$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';
$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('ebay'))
{
$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.'Category 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.'Category 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_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_TopCategories_by').' '.$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_TopProducts_by').' '.$object->GetField('Metric').' :: ('.$show_date.') :: '.DOMAIN);
$this->Application->StoreVar('graph_data', serialize($a_data));
}
}
function GetRandom($params)
{
return rand(1,10000000);
}
}

Event Timeline

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