Sales Report with product details

[sean seah]

Hi, Wish to make the sales report with the product details, which sql should I edit on?

"product_code" is a new attributes that I add in, since it is not inside the table "CubeCart_order_summary". Actually, it is from table "CubeCart_order_inventory".
And, How to sum the product_code? As the product code is in integer, digit form.
The below code is from admin_LsSCSi/sources/reports.index.inc.php

$fields = array(
        'title',
    'cart_order_id',
    'product_code', 
    'order_date', 
    'status',
    'subtotal',
    'discount',
    'shipping',
    'total_tax',
    'total',
    'customer_id',
    'first_name',
    'last_name',
    'company_name',
    'line1',
    'line2',
    'town',
    'state',
    'country',
    'postcode',
    'title_d',
    'first_name_d',
    'last_name_d',
    'company_name_d',
    'line1_d',
    'line2_d',
    'town_d',
    'state_d',
    'country_d',
    'postcode_d',
    'phone',
    'email',
    'gateway'
);

foreach ($GLOBALS['hooks']->load('admin.reports.order.pre') as $hook) include $hook;

$orders = $GLOBALS['db']->select('CubeCart_order_summary', $fields, $where);

foreach ($GLOBALS['hooks']->load('admin.reports.order.post') as $hook) include $hook;

if ($orders) {
    ## If we are wanting an external report start new External class
    if (isset($_POST['external_report']) && is_array($_POST['external_report'])) {
        $module_name = array_keys($_POST['external_report']);
        $external_class_path = 'modules/external/'.$module_name[0].'/external.class.php';
        if (file_exists($external_class_path)) {
            include $external_class_path;
            $external_report = new External($GLOBALS['config']->get($module_name[0]));
        }
    }

    ## Tally up totals
    $tally = array();
    $i   = 0;
    foreach ($orders as $order_summary) {
        $order_summary['status'] = $lang['order_state']['name_'.(int)$order_summary['status']];
        foreach ($order_summary as $field => $value) {
            if (in_array($field, array('subtotal', 'discount', 'shipping', 'total_tax', 'total'))) {
                if (!isset($tally[$field])) $tally[$field] = 0;
                $tally[$field] += $value;
            }
        }
        $order_summary['country']    = (is_numeric($order_summary['country'])) ? getCountryFormat($order_summary['country']) : $order_summary['country'];
        $order_summary['state']    = (is_numeric($order_summary['state'])) ? getStateFormat($order_summary['state']) : $order_summary['state'];
        $order_summary['country_d']    = (is_numeric($order_summary['country_d'])) ? getCountryFormat($order_summary['country_d']) : $order_summary['country_d'];
        $order_summary['state_d']    = (is_numeric($order_summary['state_d'])) ? getStateFormat($order_summary['state_d']) : $order_summary['state_d'];
        $order_summary['date']    = formatTime($order_summary['order_date'],false,true);

        ## Run line of external report data
        if (isset($external_report) && is_object($external_report)) $external_report->report_order_data($order_summary);

        unset($order_summary['order_date'], $values);
        foreach ($order_summary as $field => $value) {
            if ($i == 0) $headers[] = $field;
            $values[] = (is_numeric($value) || !strpos($value, ',')) ? $value : sprintf('"%s"', addslashes($value));
        }
        if ($i == 0 && $add_headers) $data[] = implode(',', $headers);
        $data[] = implode(',', $values);
        $smarty_data['report_date'][] = $order_summary;
        $i++;
    }
    $GLOBALS['smarty']->assign('REPORT_DATE', $smarty_data['report_date']);
    if (isset($_POST['download']) || (isset($_POST['external_report']) && is_array($_POST['external_report']))) {
        $GLOBALS['debug']->supress(true);
        if (isset($_POST['download'])) {
            $file_content  = implode("\r\n", $data);
            $file_name   = $lang['reports']['sales_data'].' '.$download_range;
        } else {
            $file_content  = $external_report->_report_data;
            $file_name   = ucfirst($module_name[0]).' '.$lang['reports']['data'].' '.$download_range;
        }
        deliverFile(false, false, $file_content, $file_name.'.csv');
        exit;
    }
    ## Show table footer
    $tally['orders'] = count($orders);
    foreach ($tally as $key => $value) {
        $tallyformatted[$key] = ($key=='orders') ? $value : sprintf('%.2F', $value);
    }
    $smarty_data['tally']  = $tallyformatted;
    $GLOBALS['smarty']->assign('DOWNLOAD', true);

    ## Get external module export code
    $where  = array('module' => 'external', 'status' => '1');
    ## Start classes for external reports
    if (($module = $GLOBALS['db']->select('CubeCart_modules', 'folder', $where)) !== false) {
        foreach ($module as $module_data) {
            if(file_exists(CC_ROOT_DIR.'/modules/external/'.$module_data['folder'])) {
                $module_data['description'] = ucfirst(str_replace('_',' ',$module_data['folder']));
                $smarty_data['export'][] = $module_data;
            }
        }
        $GLOBALS['smarty']->assign('EXPORT', $smarty_data['export']);
    }
} else {
    if (isset($_POST['download'])) httpredir(currentPage());
    $smarty_data['tally'] = array('orders' => 0);
}
$GLOBALS['smarty']->assign('TALLY', $smarty_data['tally']);
$GLOBALS['smarty']->assign('POST', $report_filter);

foreach ($GLOBALS['hooks']->load('admin.reports.order.filter') as $hook) include $hook;


Thanks~