Hi,

This is probably a views calc issue (not having any luck with posting my issue there). So apologies for posting it here too.

I'm trying to create a custom report to show sum of cost price for orders.

However, when I try to calculate ubercart cost price I get the following error:

user warning: Unknown column 'uc_orders.order_total_cost' in 'field list' query: SELECT COUNT(*) FROM (SELECT MAX('') AS order_id, MAX('SUM') AS TOTAL_SUM, MAX('') AS users_name, MAX('') AS uc_orders_primary_email, MAX('') AS delivery_countries_country_name, MAX('') AS uc_orders_created, MAX('') AS uc_orders_order_status, SUM(uc_orders.order_total_cost) AS unknown, MAX('') AS uc_orders_order_total FROM uc_orders uc_orders LEFT JOIN users users ON uc_orders.uid = users.uid LEFT JOIN uc_countries delivery_countries ON uc_orders.delivery_country = delivery_countries.country_id WHERE users.uid in ('33') ) count_alias in /home/.../public_html/modules/views/plugins/views_plugin_pager.inc on line 141.

Calculations work fine with sell price, but doesn't seem to work with cost price. I've tried it on a different website and I'm still getting the same error.

Comments

longwave’s picture

longwave
he/him
Primary language English
Location UK
commented
Project: Ubercart » Ubercart Views
Version: 6.x-2.9 » 6.x-3.x-dev
Component: Reports » Miscellaneous
Category: bug » support
Status: Active » Postponed (maintainer needs more info)

Moving to uc_views, as this is where Views support of orders is provided in 6.x-2.x.

The total cost field is calculated at display time, and is not stored directly in the database, which is probably why Views Calc cannot process it. Perhaps if you create a view of ordered products and try to sum the cost field there, that will work? The cost of an individual ordered product is stored directly.

Exporting your view and posting it here will also help others to reproduce the problem.

mrawat’s picture

mrawat commented

Thanks longwave,

I've installed the current 3.x-dev version on one of the website which has gotten rid of the errors however, it still does not calculate the sum of the cost price.

I basically cloned the existing 'orders' view, added 'Ubercart order: Total cost' under fields then selected 'Views Calc Table' as the style summing 'Cost' and 'Total Cost'

The 'Total Cost' values are displayed in the rows but are not calculated.

This is the view exported:

$view = new view;
$view->name = 'orders';
$view->description = 'Order listing';
$view->tag = '';
$view->base_table = 'uc_orders';
$view->human_name = '';
$view->core = 0;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['title'] = 'Orders';
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['access']['perm'] = 'view all orders';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['style_plugin'] = 'views_calc';
$handler->display->display_options['style_options']['columns'] = array(
  'order_id' => 'order_id',
  'name' => 'name',
  'primary_email' => 'primary_email',
  'country_name' => 'country_name',
  'created' => 'created',
  'order_total' => 'order_total',
  'order_total_cost' => 'order_total_cost',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'order_id' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'has_calc' => 0,
    'calc' => array(),
  ),
  'name' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'has_calc' => 0,
    'calc' => array(),
  ),
  'primary_email' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'has_calc' => 0,
    'calc' => array(),
  ),
  'country_name' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'has_calc' => 0,
    'calc' => array(),
  ),
  'created' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'has_calc' => 0,
    'calc' => array(),
  ),
  'order_total' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'has_calc' => 1,
    'calc' => array(
      'SUM' => 'SUM',
    ),
  ),
  'order_total_cost' => array(
    'align' => '',
    'separator' => '',
    'has_calc' => 1,
    'calc' => array(
      'SUM' => 'SUM',
    ),
  ),
);
$handler->display->display_options['style_options']['override'] = 1;
$handler->display->display_options['style_options']['sticky'] = 0;
$handler->display->display_options['style_options']['empty_table'] = 0;
$handler->display->display_options['style_options']['detailed_values'] = '0';
/* Field: Ubercart order: Order ID */
$handler->display->display_options['fields']['order_id']['id'] = 'order_id';
$handler->display->display_options['fields']['order_id']['table'] = 'uc_orders';
$handler->display->display_options['fields']['order_id']['field'] = 'order_id';
$handler->display->display_options['fields']['order_id']['label'] = 'Order id';
$handler->display->display_options['fields']['order_id']['link_to_order'] = 1;
/* Field: User: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'users';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['link_to_user'] = 1;
/* Field: Ubercart order: Email address */
$handler->display->display_options['fields']['primary_email']['id'] = 'primary_email';
$handler->display->display_options['fields']['primary_email']['table'] = 'uc_orders';
$handler->display->display_options['fields']['primary_email']['field'] = 'primary_email';
$handler->display->display_options['fields']['primary_email']['label'] = 'Email';
/* Field: Ubercart order: Recipient's country name */
$handler->display->display_options['fields']['country_name']['id'] = 'country_name';
$handler->display->display_options['fields']['country_name']['table'] = 'delivery_countries';
$handler->display->display_options['fields']['country_name']['field'] = 'country_name';
$handler->display->display_options['fields']['country_name']['label'] = 'Delivery country';
/* Field: Ubercart order: Creation date */
$handler->display->display_options['fields']['created']['id'] = 'created';
$handler->display->display_options['fields']['created']['table'] = 'uc_orders';
$handler->display->display_options['fields']['created']['field'] = 'created';
$handler->display->display_options['fields']['created']['label'] = 'Date';
/* Field: Ubercart order: Total */
$handler->display->display_options['fields']['order_total']['id'] = 'order_total';
$handler->display->display_options['fields']['order_total']['table'] = 'uc_orders';
$handler->display->display_options['fields']['order_total']['field'] = 'order_total';
$handler->display->display_options['fields']['order_total']['revision'] = 'themed-original';
/* Field: Ubercart order: Total cost */
$handler->display->display_options['fields']['order_total_cost']['id'] = 'order_total_cost';
$handler->display->display_options['fields']['order_total_cost']['table'] = 'uc_orders';
$handler->display->display_options['fields']['order_total_cost']['field'] = 'order_total_cost';
$handler->display->display_options['fields']['order_total_cost']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['order_total_cost']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['order_total_cost']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['order_total_cost']['alter']['external'] = 0;
$handler->display->display_options['fields']['order_total_cost']['alter']['replace_spaces'] = 0;
$handler->display->display_options['fields']['order_total_cost']['alter']['trim_whitespace'] = 0;
$handler->display->display_options['fields']['order_total_cost']['alter']['nl2br'] = 0;
$handler->display->display_options['fields']['order_total_cost']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['order_total_cost']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['order_total_cost']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['order_total_cost']['alter']['trim'] = 0;
$handler->display->display_options['fields']['order_total_cost']['alter']['html'] = 0;
$handler->display->display_options['fields']['order_total_cost']['element_label_colon'] = 1;
$handler->display->display_options['fields']['order_total_cost']['element_default_classes'] = 1;
$handler->display->display_options['fields']['order_total_cost']['hide_empty'] = 0;
$handler->display->display_options['fields']['order_total_cost']['empty_zero'] = 0;
$handler->display->display_options['fields']['order_total_cost']['hide_alter_empty'] = 1;
$handler->display->display_options['fields']['order_total_cost']['set_precision'] = 0;
$handler->display->display_options['fields']['order_total_cost']['precision'] = '0';
$handler->display->display_options['fields']['order_total_cost']['format_plural'] = 0;
$handler->display->display_options['fields']['order_total_cost']['revision'] = 'themed-original';
/* Filter: User: Name */
$handler->display->display_options['filters']['uid']['id'] = 'uid';
$handler->display->display_options['filters']['uid']['table'] = 'users';
$handler->display->display_options['filters']['uid']['field'] = 'uid';
$handler->display->display_options['filters']['uid']['value'] = '';
$handler->display->display_options['filters']['uid']['exposed'] = TRUE;
$handler->display->display_options['filters']['uid']['expose']['operator'] = 'uid_op';
$handler->display->display_options['filters']['uid']['expose']['label'] = 'Username';
$handler->display->display_options['filters']['uid']['expose']['identifier'] = 'uid';
$handler->display->display_options['filters']['uid']['expose']['remember'] = 1;
$handler->display->display_options['filters']['uid']['expose']['reduce'] = 0;

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->display->display_options['path'] = 'orders';
$translatables['orders'] = array(
  t('Defaults'),
  t('Orders'),
  t('more'),
  t('Apply'),
  t('Reset'),
  t('Sort by'),
  t('Asc'),
  t('Desc'),
  t('Order id'),
  t('Name'),
  t('Email'),
  t('Delivery country'),
  t('Date'),
  t('Total'),
  t('.'),
  t(','),
  t('Total cost'),
  t('Username'),
  t('Page'),
);
mrawat’s picture

mrawat commented

Sorry, the above export was not calculating either the Total nor the Total Cost for some reason.

I reordered the fields so Total was at the end. Total is now calculated but still not Total Cost.