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; $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(kEvent $event) { $report = $this->Application->RecallVar('report_options'); if ( !$report ) { return; } $field_values = unserialize($report); $config = $event->getUnitConfig('rep'); $config->setTableName($field_values['table_name']); $config->addFields(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)) ) { $config->addFields(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 $config->setListSQLsBySpecial('', ' SELECT %1$s.* %2$s FROM %1$s LEFT JOIN ' . TABLE_PREFIX . 'Categories AS c ON c.CategoryId = %1$s.CategoryId'); if ( $this->Application->isModuleEnabled('in-auction') ) { $config->addGrids(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'), ), ), 'Default'); } else { $config->addGrids(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'), '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'), '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'), ), ), 'Default'); } $config->addVirtualFields(Array ( 'CategoryName' => Array ('type' => 'string', 'default' => ''), 'Metric' => Array ( 'type' => 'int', 'formatter' => 'kOptionsFormatter', 'options' => $this->GetMetricOptions($config, '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; $config->addCalculatedFieldsBySpecial('', 'REPLACE(SUBSTR(c.l'.$lang.'_CachedNavbar, '.$root_length.'), "&|&", " > ")', 'CategoryName'); } elseif ($field_values['ReportType'] == 2) { // by User $config->setListSQLsBySpecial('', ' SELECT %1$s.* %2$s FROM %1$s LEFT JOIN ' . TABLE_PREFIX . 'Users AS u ON u.PortalUserId = %1$s.PortalUserId'); $config->addGrids(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'), ), ), 'Default'); $config->addVirtualFields(Array ( 'Login' => Array ('type' => 'string', 'default' => ''), 'FirstName' => Array ('type' => 'string', 'default' => ''), 'LastName' => Array ('type' => 'string', 'default' => ''), )); $config->addCalculatedFieldsBySpecial('', Array ( 'Login' => 'u.Username', 'FirstName' => 'u.FirstName', 'LastName' => 'u.LastName', )); } elseif ($field_values['ReportType'] == 5) { // by Product $config->setListSQLsBySpecial('', ' SELECT %1$s.* %2$s FROM %1$s LEFT JOIN '.TABLE_PREFIX.'Products AS p ON p.ProductId = %1$s.ProductId'); if ( $this->Application->isModuleEnabled('in-auction') ) { $config->addGrids(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'), ), ), 'Default'); } else { $config->addGrids(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'), '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'), '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'), ), ), 'Default'); } $config->addVirtualFields(Array ( 'ProductName' => Array ('type' => 'string', 'default' => ''), 'Metric' => Array ( 'type' => 'int', 'formatter' => 'kOptionsFormatter', 'options' => $this->GetMetricOptions($config, 'ProductName'), 'use_phrases' => 1, 'default' => 0 ) )); $lang = $this->Application->GetVar('m_lang'); $config->addCalculatedFieldsBySpecial('', 'p.l' . $lang . '_Name', 'ProductName'); } elseif ($field_values['ReportType'] == 12) { // Overall $config->setListSQLsBySpecial('', 'SELECT %1$s.* %2$s FROM %1$s'); $config->addFields(Array ( 'Marketplace' => Array ( 'formatter' => 'kOptionsFormatter', 'options' => Array (1 => 'la_OnlineStore', 2 => 'la_eBayMarketplace'), 'use_phrases' => 1, 'default' => 1 ) )); $config->addGrids(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'), ), ), 'Default'); $config->addVirtualFields(Array ( 'type' => 'int', 'formatter' => 'kOptionsFormatter', 'options' => $this->GetMetricOptions($config, 'Marketplace'), 'use_phrases' => 1, 'default' => 0 ), 'Metric'); } $config->setListSortingsBySpecial('', Array( 'Sorting' => Array('Amount' => 'desc'), )); } /** * 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') ? mktime(0, 0, 0) : mktime(23, 59, 59); $time = 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 kUnitConfig $config * @param string $exclude_field * @return Array */ function GetMetricOptions(kUnitConfig $config, $exclude_field) { $grid = $config->getGridByName('Default'); $ret = Array (); foreach ($grid['Fields'] as $field => $field_options) { if ( $field == $exclude_field ) { continue; } $ret[$field] = $field_options['title']; } return $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) { $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*/ $grid = $this->Application->getUnitConfig('rep')->getGridByName('Default'); $a_fields = $grid['Fields']; $ret = ''; foreach ($a_fields AS $field => $a_props) { $ret .= ''.$field.''; } $ret = substr($ret, 0, strlen($ret) - 5).''; $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 .= ''.$ReportItem->GetField($field).''; $a_totals[$field] += $a_row[$field]; } $ret .= substr($row, 0, strlen($row) - 5).''; } // 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 = ''; continue; } $row .= ''.$ReportItem->GetField($field).''; } $ret .= substr($row, 0, strlen($row) - 5).''; $ret = str_replace("\r",'', $ret); $ret = str_replace("\n",'', $ret); $ret = str_replace('"','\'\'', $ret); $ret = str_replace('','"', $ret); $ret = str_replace('',',', $ret); $ret = str_replace('',"\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(); } }