以订单为导向-以削减成本为手段=最大利润化目标。先上结果图
此报表已经完全开发完成，包括查看已付款订单，等待付款订单，所有订单，发货中订单的统计，此份代码适用 zencart lightinthebox订单统计报表分析 导出excel 和图表统计
经朋友要求，结合现有订单对lightinthebox ERP 进销存 销售利润分析，可以修改实际销售价格，最终形成统计利润。
而实际销售价格最好以淘宝交易 折扣优惠的形式给出：
更新中。。。。
url地址为http://localhost/webbuildceo_lb/admin/super_orders.php?oID=13&action=edit
在文件 F:\wamp\www\webbuildceo_lb\admin\includes\languages\english\super_orders.php 定义要用到的常量
super_orders.php中添加如下常量定义，红色字段为数据库中新增字段
<?php
require(DIR_WS_LANGUAGES . $_SESSION['language'] . '/' . 'order_status_email.php');
define('HEADING_TITLE_ORDERS_LISTING', 'Orders Listing');
define('HEADING_TITLE_ORDER_DETAILS', 'Order #');
define('HEADING_TITLE_SEARCH', 'Order ID:');
define('HEADING_TITLE_STATUS', 'Status:');
define('HEADING_REOPEN_ORDER', 'Re-Open');define('TABLE_HEADING_ORDERS_ID','ID');
define('TABLE_HEADING_STATUS_HISTORY', 'Status History');
define('TABLE_HEADING_ADD_COMMENTS', 'Add Comments');
define('TABLE_HEADING_FINAL_STATUS', 'Close Order');
define('TABLE_HEADING_COMMENTS', 'Comments');
define('TABLE_HEADING_CUSTOMERS', 'Customers');
define('TABLE_HEADING_ORDER_TOTAL', 'Order Total');
define('TABLE_HEADING_PAYMENT_METHOD', 'Payment Method');
define('TABLE_HEADING_DATE_PURCHASED', 'Date Purchased');
define('TABLE_HEADING_STATUS', 'Status');
define('TABLE_HEADING_TYPE', 'Order Type');
define('TABLE_HEADING_ACTION', 'Action');
define('TABLE_HEADING_QUANTITY', 'Qty.');
define('TABLE_HEADING_PRODUCTS_MODEL', 'Model');
define('TABLE_HEADING_PRODUCTS', 'Products');
define('TABLE_HEADING_TAX', 'Tax');
define('TABLE_HEADING_TOTAL', 'Total');
define('TABLE_HEADING_PRICE_EXCLUDING_COST', 'Cost(ex)');
define('TABLE_HEADING_PRICE_EXCLUDING_TAX', 'Price (ex)');
define('TABLE_HEADING_PRICE_INCLUDING_TAX', 'Price (inc)');
define('TABLE_HEADING_TOTAL_EXCLUDING_TAX', 'Total (ex)');
define('TABLE_HEADING_TOTAL_INCLUDING_TAX', 'Total (inc)');
define('TABLE_HEADING_CUSTOMER_NOTIFIED', 'Customer Notified');
define('TABLE_HEADING_DATE_ADDED', 'Date Added');define('TABLE_HEADING_ADMIN_NOTES', 'Admin Notes');
define('TABLE_HEADING_AUTHOR', 'Author');
define('TABLE_HEADING_ADD_NOTES', 'Add New Note');
define('TABLE_HEADING_KARMA', 'Karma');
define('TEXT_WARN_NOT_VISIBLE', ' (this information is CONFIDENTIAL)');
define('TEXT_TOTAL_KARMA', 'Total Karma: ');
define('TEXT_ADMIN_NOTES_NONE', 'Customer has no reviews');define('PAYMENT_TABLE_NUMBER', 'Number');
define('PAYMENT_TABLE_NAME', 'Payor Name');
define('PAYMENT_TABLE_AMOUNT', 'Amount');
define('PAYMENT_TABLE_TYPE', 'Type');
define('PAYMENT_TABLE_POSTED', 'Date Posted');
define('PAYMENT_TABLE_MODIFIED', 'Last Modified');
define('PAYMENT_TABLE_ACTION', 'Action');
define('ALT_TEXT_ADD', 'ADD');
define('ALT_TEXT_UPDATE', 'UPDATE');
define('ALT_TEXT_DELETE', 'DELETE');define('ENTRY_PAYMENT_DETAILS', 'Payment Details');
define('ENTRY_CUSTOMER_ADDRESS', 'Customer Address:');
define('ENTRY_SHIPPING_ADDRESS', 'Shipping Address:');
define('ENTRY_BILLING_ADDRESS', 'Billing Address:');
define('ENTRY_PAYMENT_METHOD', 'Payment Method:');
define('ENTRY_CREDIT_CARD_TYPE', 'Credit Card Type:');
define('ENTRY_CREDIT_CARD_OWNER', 'Credit Card Owner:');
define('ENTRY_CREDIT_CARD_NUMBER', 'Credit Card Number:');
define('ENTRY_CREDIT_CARD_CVV', 'Credit Card CVV Number:');
define('ENTRY_CREDIT_CARD_EXPIRES', 'Credit Card Expires:');
define('ENTRY_SUB_TOTAL', 'Sub-Total:');
define('ENTRY_TAX', 'Tax:');
define('ENTRY_SHIPPING', 'Shipping:');
define('ENTRY_TOTAL', 'Total:');
define('ENTRY_AMOUNT_APPLIED', 'Amount Applied:');
define('ENTRY_BALANCE_DUE', 'Balance Due:');
define('ENTRY_DATE_PURCHASED', 'Date Purchased:');
define('ENTRY_STATUS', 'Status:');// CMJ - BOF modified per http://www.zen-cart.com/forum/showpost.php?p=639013&postcount=1147//define('ENTRY_NOTIFY_CUSTOMER', 'Notify Customer?');
//define('ENTRY_NOTIFY_COMMENTS', 'Append Comments?');
define('ENTRY_NOTIFY_CUSTOMER', 'Check here to send change in status email to customer');
define('ENTRY_NOTIFY_COMMENTS', 'Check here to add these comments to the change in status email');
// CMJ - BOF modified per http://www.zen-cart.com/forum/showpost.php?p=636329&postcount=1147define('HEADING_COLOR_KEY', 'Color Key:');
define('TEXT_PURCHASE_ORDERS', 'Purchase Order');
define('TEXT_PAYMENTS', 'Payment');
define('TEXT_REFUNDS', 'Refund');define('TEXT_MAILTO', 'mailto');
define('TEXT_STORE_EMAIL', 'web');
define('TEXT_WHOIS_LOOKUP', 'whois');
define('TEXT_ICON_LEGEND', 'Action Icon Legend:');
define('TEXT_BILLING_SHIPPING_MISMATCH', 'Billing and Shipping do not match');
define('TEXT_INFO_HEADING_DELETE_ORDER', 'Delete Order - ');
define('TEXT_INFO_DELETE_INTRO', 'Are you sure you want to delete this order?');
define('TEXT_INFO_RESTOCK_PRODUCT_QUANTITY', 'Restock product quantity');
define('TEXT_DATE_ORDER_CREATED', 'Date Created:');
define('TEXT_DATE_ORDER_LAST_MODIFIED', 'Last Modified:');
define('TEXT_INFO_PAYMENT_METHOD', 'Payment Method:');
define('TEXT_INFO_SHIPPING_METHOD', 'Shipping Method:');
define('TEXT_ALL_ORDERS', 'All Orders');
define('TEXT_NO_ORDER_HISTORY', 'No Order History Available');
define('TEXT_DISPLAY_ONLY', '(Display Only)');define('ERROR_ORDER_DOES_NOT_EXIST', 'Error: Order does not exist.');
define('SUCCESS_ORDER_UPDATED', 'Success: Order has been successfully updated.');
define('WARNING_ORDER_NOT_UPDATED', 'Warning: Nothing to change. The order was not updated.');
define('SUCCESS_MARK_COMPLETED', 'Success: Order #%s is complete!');
define('WARNING_MARK_CANCELLED', 'Warning: Order #%s has been cancelled');
define('WARNING_ORDER_REOPEN', 'Warning: Order #%s has been reopened');define('ENTRY_ORDER_ID','Order #');
define('TEXT_INFO_ATTRIBUTE_FREE', ' - <span class="alert">FREE</span>');define('TEXT_DOWNLOAD_TITLE', 'Order Download Status');
define('TEXT_DOWNLOAD_STATUS', 'Status');
define('TEXT_DOWNLOAD_FILENAME', 'Filename');
define('TEXT_DOWNLOAD_MAX_DAYS', 'Days');
define('TEXT_DOWNLOAD_MAX_COUNT', 'Count');define('TEXT_DOWNLOAD_AVAILABLE', 'Available');
define('TEXT_DOWNLOAD_EXPIRED', 'Expired');
define('TEXT_DOWNLOAD_MISSING', 'Not on Server');define('IMAGE_ICON_STATUS_CURRENT', 'Status - Available');
define('IMAGE_ICON_STATUS_EXPIRED', 'Status - Expired');
define('IMAGE_ICON_STATUS_MISSING', 'Status - Missing');define('SUCCESS_ORDER_UPDATED_DOWNLOAD_ON', 'Download was successfully enabled');
define('SUCCESS_ORDER_UPDATED_DOWNLOAD_OFF', 'Download was successfully disabled');
define('TEXT_MORE', '... more');define('TEXT_INFO_IP_ADDRESS', 'IP Address: ');
define('TEXT_NEW_WINDOW', ' (New Window)');
define('IMAGE_SHIPPING_LABEL', 'Shipping Label');
define('ICON_ORDER_DETAILS', 'Display Order Details');
define('ICON_ORDER_PRINT', 'Print Data Sheet' . TEXT_NEW_WINDOW);
define('ICON_ORDER_INVOICE', 'Display Invoice' . TEXT_NEW_WINDOW);
define('ICON_ORDER_PACKINGSLIP', 'Display Packing Slip' . TEXT_NEW_WINDOW);
define('ICON_ORDER_SHIPPING_LABEL', 'Display Shipping Label' . TEXT_NEW_WINDOW);
define('ICON_ORDER_DELETE', 'Delete Order');
define('ICON_EDIT_CONTACT', 'Edit Contact Data');
define('ICON_EDIT_PRODUCT', 'Split Order');
define('ICON_EDIT_TOTAL', 'Edit Order Totals');
define('ICON_EDIT_HISTORY', 'Edit Status History');
define('ICON_CLOSE_STATUS', 'Close Status');
define('ICON_MARK_COMPLETED', 'Mark Order Completed');
define('ICON_MARK_CANCELLED', 'Mark Order Cancelled');define('MINI_ICON_ORDERS', 'Show Customer\'s Orders');
define('MINI_ICON_INFO', 'Show Customer\'s Profile');define('BUTTON_TO_LIST', 'Order List');
define('BUTTON_SPLIT', 'Split Packing Slip');
define('SELECT_ORDER_LIST', 'Jump to Order:');define('TEXT_NO_PAYMENT_DATA', 'No Payment Data Available');
define('TEXT_PAYMENT_DATA', 'Order Payment Data');
?>
接着修改控制器
F:\wamp\www\webbuildceo_lb\admin\super_orders.php 红色字段对应数据库新增字段
<tr>
<td><table border="0" width="100%" cellspacing="0" cellpadding="2">
<tr class="dataTableHeadingRow">
<?php if (sizeof($order->products) > 1) { ?>
<td class="dataTableHeadingContent"> </td>
<?php } ?>
<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS; ?></td>
<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS_MODEL; ?></td>
<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TAX; ?></td>
<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_PRICE_EXCLUDING_COST; ?></td>
<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_PRICE_EXCLUDING_TAX; ?></td>
<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_PRICE_INCLUDING_TAX; ?></td>
<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_EXCLUDING_TAX; ?></td>
<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_INCLUDING_TAX; ?></td>
</tr>
然后在finalprice前增加对应的取值：
'<td class="dataTableContent" align="right" valign="middle">' . $currencies->format($order->products[$i]['cost'], true, $order->info['currency'], $order->info['currency_value']) .
($order->products[$i]['onetime_charges'] != 0 ? '<br />' . $currencies->format($order->products[$i]['onetime_charges'], true, $order->info['currency'], $order->info['currency_value']) : '') .
实际列出订单的时候显示的商品为下列的sql语句组成：
select orders_products_id, products_id, products_name, products_model,
products_price, products_tax, products_quantity,
final_price, onetime_charges, product_is_free
from ORDERS_PRODUCTS
where orders_id = 11 order by orders_products_id
允许结果为——
orders_products_id | products_id | products_name | products_model | products_price | products_tax | products_quantity | final_price | onetime_charges | product_is_free | |||
| 17 | 2 | product 2 | web | 90.0000 | 0.0000 | 1 | 90.0000 | 0.0000 | 0 | ||
| | 18 | 1 | product 1 | web | 70.0000 | 0.0000 | 1 | 70.0000 | 0.0000 | 0 |
我们要计算利润，就必须先拿到其成本，用left左连接取出，构造sql左外连接语句如下：
SELECT o.orders_products_id, o.products_id, o.products_name, o.products_model, o.products_price, o.products_tax, o.products_quantity, o.final_price, o.onetime_charges, o.product_is_free,p.products_cost
FROM ORDERS_PRODUCTS o left join products p on o.products_id=p.products_id
WHERE o.orders_id =11
ORDER BY o.orders_products_id
接着修改order类：
$orders_products = $db->Execute("SELECT o.orders_products_id, o.products_id, o.products_name, o.products_model,
o.products_tax, o.products_quantity,
o.final_price, o.onetime_charges, o.product_is_free,p.products_cost
from " . TABLE_ORDERS_PRODUCTS ." o left join ".TABLE_PRODUCTS. " p on o.products_id=p.products_id
where o.orders_id = '" . (int)$order_id . "'
order by o.orders_products_id");将取出来的值放入数组：
$this->products[$index] = array('qty' => $new_qty,
'id' => $orders_products->fields['products_id'],
'name' => $orders_products->fields['products_name'],
'products_image' => $orders_products->fields['products_image'],
'model' => $orders_products->fields['products_model'],
'tax' => $orders_products->fields['products_tax'],
'cost' => $orders_products->fields['products_cost'],
'price' => $orders_products->fields['products_price'],
'onetime_charges' => $orders_products->fields['onetime_charges'],
'final_price' => $orders_products->fields['final_price'],
'product_is_free' => $orders_products->fields['product_is_free']);
