Page MenuHomeIn-Portal Phabricator
Files F785741

in-commerce
No OneTemporary
Actions

File Metadata

Created
Thu, Feb 13, 12:54 AM

in-commerce
View Options

Index: branches/5.2.x/units/gateways/gw_classes/paypal.php
===================================================================
--- branches/5.2.x/units/gateways/gw_classes/paypal.php (revision 16020)
+++ branches/5.2.x/units/gateways/gw_classes/paypal.php (revision 16021)
@@ -1,268 +1,269 @@
<?php
/**
* @version $Id$
* @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.
*/
require_once GW_CLASS_PATH.'/gw_base.php';
class kGWPayPal extends kGWBase
{
/**
* Returns payment form submit url
*
* @param Array $gw_params gateway params from payment type config
* @return string
*/
function getFormAction($gw_params)
{
return $gw_params['submit_url'];
}
/**
* Processed input data and convets it to fields understandable by gateway
*
* @param Array $item_data
* @param Array $tag_params additional params for gateway passed through tag
* @param Array $gw_params gateway params from payment type config
* @return Array
*/
function getHiddenFields($item_data, $tag_params, $gw_params)
{
$ret = Array();
$ret['item_name'] = 'Order #'.$item_data['OrderNumber'];
$ret['item_number'] = 'order:'.$item_data['OrderNumber'];
$selected_cur = $this->Application->RecallVar('curr_iso');
$available = explode(',', $gw_params['currency_code']);
$target = in_array($selected_cur, $available) ? $selected_cur : $available[0];
if( !$this->IsTestMode() )
{
$currency_iso = $gw_params['currency_code'];
$ret['amount'] = $this->ConvertCurrency($item_data['SubTotal'], $target);
$ret['shipping'] = $this->ConvertCurrency($item_data['ShippingCost'], $target);
$ret['tax'] = $this->ConvertCurrency($item_data['VAT'], $target);
}
else
{
$ret['amount'] = 1;
$ret['shipping'] = 0;
$ret['tax'] = 0;
}
$ret['quantity'] = 1;
$ret['cancel_return'] = $this->Application->HREF($tag_params['cancel_template'],'',Array('pass'=>'m'));
$ret['return'] = $this->Application->HREF($tag_params['return_template'],'',Array('pass'=>'m'));
$ret['no_note'] = 1; // customer is not prompted for notes
$ret['no_shipping'] = 1; // customer is not prompted for shipping address
$ret['rm'] = 2; // return method - POST
$ret['currency_code'] = $target;
$ret['invoice'] = $item_data['OrderNumber'];
$ret['business'] = $gw_params['business_account'];
// prepopulated fields
$ret['address_override'] = 1; // override user's stored address
$ret['email'] = $item_data['BillingEmail'];
list($first_name, $last_name) = explode(' ', $item_data['BillingTo']);
$ret['first_name'] = $first_name;
$ret['last_name'] = $last_name;
$ret['address1'] = $item_data['BillingAddress1'];
$ret['address2'] = $item_data['BillingAddress2'];
$ret['city'] = $item_data['BillingCity'];
$ret['state'] = $item_data['BillingState'];
$ret['zip'] = $item_data['BillingZip'];
$cs_helper = $this->Application->recallObject('CountryStatesHelper');
/* @var $cs_helper kCountryStatesHelper */
$ret['country'] = $cs_helper->getCountryIso( $item_data['BillingCountry'] );
$ret['notify_url'] = $this->getNotificationUrl() . '?sid=' . $this->Application->GetSID() . '&admin=1&order_id=' . $item_data['OrderId'];
$ret['cmd'] = '_xclick'; // act as "Buy Now" PayPal button
return $ret;
}
function getSubscriptionFields($item_data, $tag_params, $gw_params)
{
$ret = Array();
$ret['item_name'] = $item_data['item_name'];
$ret['item_number'] = $item_data['item_number'];
$ret['a1'] = $item_data['a1'];
$ret['p1'] = $item_data['p1'];
$ret['t1'] = $item_data['t1'];
$ret['a2'] = $item_data['a2'];
$ret['p2'] = $item_data['p2'];
$ret['t2'] = $item_data['t2'];
$ret['p3'] = $item_data['p3'];
$ret['t3'] = $item_data['t3'];
$ret['src'] = $item_data['src'];
$ret['sra'] = $item_data['sra'];
$ret['srt'] = $item_data['srt'];
$ret['custom'] = $item_data['OrderId'];
$currency_iso = $gw_params['currency_code'];
$ret['a3'] = $this->ConvertCurrency($item_data['a3'], $currency_iso);;
$ret['tax'] = $this->ConvertCurrency($item_data['VAT'], $currency_iso);
if( $this->Application->isDebugMode() )
{
}
else
{
}
// $ret['quantity'] = 1;
$ret['cancel_return'] = $this->Application->HREF($tag_params['cancel_template'],'',Array('pass'=>'m'));
$ret['return'] = $this->Application->HREF($tag_params['return_template'],'',Array('pass'=>'m'));
$ret['no_note'] = 1; // customer is not prompted for notes
$ret['no_shipping'] = 1; // customer is not prompted for shipping address
$ret['rm'] = 2; // return method - POST
$ret['currency_code'] = $gw_params['currency_code'];
$ret['invoice'] = $item_data['OrderNumber'];
$ret['business'] = $gw_params['business_account'];
// prepopulated fields
$ret['address_override'] = 1; // override user's stored address
$ret['email'] = $item_data['BillingEmail'];
list($first_name, $last_name) = explode(' ', $item_data['BillingTo']);
$ret['first_name'] = $first_name;
$ret['last_name'] = $last_name;
$ret['address1'] = $item_data['BillingAddress1'];
$ret['address2'] = $item_data['BillingAddress2'];
$ret['city'] = $item_data['BillingCity'];
$ret['state'] = $item_data['BillingState'];
$ret['zip'] = $item_data['BillingZip'];
$cs_helper = $this->Application->recallObject('CountryStatesHelper');
/* @var $cs_helper kCountryStatesHelper */
$ret['country'] = $cs_helper->getCountryIso( $item_data['BillingCountry'] );
$ret['notify_url'] = $this->getNotificationUrl() . '?sid='.$this->Application->GetSID().'&admin=1&order_id='.$item_data['OrderId'].'&payment_type_id='.$tag_params['payment_type_id'];
$ret['cmd'] = '_xclick-subscriptions'; // act as "Buy Now" PayPal button
$real_ret = array();
foreach ($ret as $key => $val)
{
if ($val == '') continue;
$real_ret[$key] = $val;
}
return $real_ret;
}
function processNotification($gw_params)
{
$payment_status = $_POST['payment_status']; // save payment_status for later proceeding
$_POST['cmd'] = '_notify-validate';
// status, of that PayPal server really has sent such notification to us
$status_map = Array('INVALID' => 0, 'VERIFIED' => 1);
$curl_helper = $this->Application->recallObject('CurlHelper');
/* @var $curl_helper kCurlHelper */
$curl_helper->SetPostData($_POST);
$n_status = $curl_helper->Send($gw_params['submit_url']); // INVALID, VERIFIED
$n_status = $status_map[$n_status];
$success = ($n_status == 1) && ($payment_status == 'Completed') ? 1:0 ; // 1:0 is on purpose, false will result an SQL error !
if (!$success) return;
$type = $_POST['txn_type'];
switch ($type)
{
case 'subscr_signup':
break;
case 'subscr_cancel':
break;
case 'subscr_failed':
break;
case 'subscr_payment':
$field_values = $this->Conn->GetRow('SELECT * FROM '.TABLE_PREFIX.'OrderItems WHERE OrderItemId = '.$_POST['item_number']);
$this->Application->HandleEvent(new kEvent('p:OnSubscriptionApprove', array('field_values' => $field_values)));
$success = 0; //this will eliminate OnCompleteOrder in gw_notify!
$org_order = $this->Application->recallObject('ord.-original', 'ord', Array('skip_autoload' => true));
/* @var $org_order kDBItem */
$org_order->Load($field_values['OrderId']);
$order = $this->Application->recallObject('ord.-paypal', 'ord');
$order->SetDBFieldsFromHash($org_order->GetFieldValues());
$order->SetDBField('SubTotal', $field_values['Price']);
$order->SetDBField('OriginalAmout', $field_values['Price']);
$order->SetDBField('OrderDate', adodb_mktime());
$order->UpdateFormattersSubFields();
$dup_item = false;
if ($org_order->GetDBField('Status') >= ORDER_STATUS_PROCESSED) {
$sql = 'SELECT MAX(SubNumber) FROM '.TABLE_PREFIX.'Orders WHERE Number = '.$org_order->GetDBField('Number');
$num = $this->Conn->GetOne($sql) + 1;
$order->SetDBField('SubNumber', $num);
$dup_item = true;
}
else {
$sql = 'SELECT MAX(Number) FROM '.TABLE_PREFIX.'Orders';
$num = $this->Conn->GetOne($sql) + 1;
$order->SetDBField('Number', $num);
$order->SetDBField('SubNumber', 0);
}
$order->SetDBField('PaymentType', $this->Application->GetVar('payment_type_id'));
$info = array(
'BillingTo' => $_POST['first_name'].' '.$_POST['last_name'],
'BillingCompany' => 'n/a (PayPal)',
'BillingPhone' => 'n/a (PayPal)',
'BillingFax' => '',
'BillingEmail' => $_POST['payer_email'],
'BillingAddress1' => 'n/a (PayPal)',
'BillingCity' => 'n/a (PayPal)',
'BillingState' => 'n/a (PayPal)',
'BillingZip' => 'n/a (PayPal)',
'BillingCountry' => '???',
);
+ // TODO: maybe this should be SetDBFieldsFromHash instead, because all data comes from inside.
$order->SetFieldsFromHash($info);
$order->SetDBField('Status', ORDER_STATUS_PROCESSED);
$order->Create();
if ($dup_item) {
$query = 'INSERT INTO '.TABLE_PREFIX.'OrderItems
(OrderId, ProductId, ProductName, Quantity, QuantityReserved, FlatPrice, Price, BackOrderFlag, Weight, ShippingTypeId, ItemData, OptionsSalt)
SELECT
'.$order->GetId().' AS OrderId, ProductId, ProductName, Quantity, QuantityReserved, FlatPrice, Price, BackOrderFlag, Weight, ShippingTypeId, ItemData, OptionsSalt
FROM '.TABLE_PREFIX.'OrderItems
WHERE OrderItemId = '.$field_values['OrderItemId'];
}
else {
$query = 'UPDATE '.TABLE_PREFIX.'OrderItems SET OrderId = %s WHERE OrderItemId = %s';
$query = sprintf($query, $order->GetId(), $field_values['OrderItemId']);
}
$this->Conn->Query($query);
break;
case 'subscr_eot':
break;
case 'subscr_modify':
break;
}
return $success;
}
- }
\ No newline at end of file
+ }
Index: branches/5.2.x/units/reports/reports_event_handler.php
===================================================================
--- branches/5.2.x/units/reports/reports_event_handler.php (revision 16020)
+++ branches/5.2.x/units/reports/reports_event_handler.php (revision 16021)
@@ -1,846 +1,848 @@
<?php
/**
* @version $Id$
* @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;
$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');
$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*/
$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) {
+ // 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);
$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();
}
}

Event Timeline

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