File: /var/www/html/amberconcept/export_data.php
<?php
//include wp libriries and set timezone to HK
require_once('wp-load.php');
require_once('PHPExcel.php');
date_default_timezone_set("Asia/Hong_Kong");
global $wpdb;
$filename = 'Amber_commission_'.time();
function get_currency_rate($currency_code){
global $wpdb;
$result = $wpdb->get_results("SELECT * FROM `custom_currency` WHERE currency_code = '".trim($currency_code)."' LIMIT 1");
if($result)
return $result[0]->currency_rate;
else {
return "";
}
}
function xlsHeader($wsObj, $arr_header) {
for($i = 0; $i < count($arr_header); $i++) {
$wsObj->setCellValueByColumnAndRow($i, 1, $arr_header[$i]);
}
}
function writeOutXls($objPHPExcel, $filename, $path='') {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
header('Cache-Control: max-age=0');
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize ' => '128MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
$arr_order_status = array(
"wc-on-hold" => "On-hold",
"wc-pending" => "Pending payment",
"wc-processing" => "Processing",
"wc-completed" => "Shipped",
"wc-partial-shipped" => "Partially Shipped",
"wc-updated-tracking" => "Updated Tracking",
"wc-cancelled" => "Cancelled",
"wc-refunded" => "Refunded",
"wc-failed" => "Failed",
"wc-shipping-progress" => "Completed"
);
$xlsObj = new PHPExcel();
$arr_header = array(
'Order No.',
'Order Date',
'Order Status',
'Country',
'Bill To',
'Tracking Info',
'Cost - Shipping (HKD)',
'Cost 1 Supplier',
'Cost 1 PO#',
'Cost 1 Currency',
'Cost 1 Amount',
'Cost 2 Supplier',
'Cost 2 PO#',
'Cost 2 Currency',
'Cost 2 Amount',
'Cost 3 Supplier',
'Cost 3 PO#',
'Cost 3 Currency',
'Cost 3 Amount',
'Cost Subtotal (HKD)',
'Order Subtotal (HKD)',
'Freight (HKD)',
'Discount (HKD)',
'Inv. Total (HKD)',
'Payment Charge (HKD)',
'Rebate To',
'Rebate Amount (HKD)',
'Shipment cost margin /(loss)(HKD)',
'Payment Method',
'Received Amount (HKD)',
'Received Date',
'Payment Remark',
'Last Ship Date',
'Completed Date',
'Net Total Order Amt.',
'Net Total Order Cost',
'Net Order Markup',
'Markup%',
'Commission%',
'Commission',
'Claimed'
);
$xlsObj->getActiveSheet()->getStyle ('G')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('K')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('O')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('S')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('T')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('U')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('V')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('W')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('X')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('Y')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('AA')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('AB')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('AD')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('AI')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('AJ')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('AK')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('AL')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('AM')->getNumberFormat()->setFormatCode ("0.00");
$xlsObj->getActiveSheet()->getStyle ('AN')->getNumberFormat()->setFormatCode ("0.00");
$styleArray = array(
'font' => array(
'bold' => true,
)
);
$xlsObj->getActiveSheet()->getStyle('A1:AO1')->applyFromArray($styleArray);
/*
foreach(range('A','AF') as $columnID) {
$xlsObj->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}
*/
for ($i = 'A'; $i != $xlsObj->getActiveSheet()->getHighestColumn(); $i++) {
$xlsObj->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);
}
$wsObj = $xlsObj->getActiveSheet();
xlsHeader($wsObj, $arr_header);
$xlsRow = 2;
$count = 0;
if($_GET['order_status']=="") {
$post_status = implode("','", array('wc-pending', 'wc-processing', 'wc-on-hold', 'wc-completed', 'wc-cancelled', 'wc-refunded', 'wc-failed','wc-shipping-progress', 'wc-partial-shipped') );
} else {
$post_status = $_GET['order_status'];
}
$sql = "SELECT * FROM $wpdb->posts WHERE post_type = 'shop_order' AND post_status IN ('{$post_status}') ";
if(trim($_GET['order_no'])!="") {
$sql .= "AND ID = '".trim($_GET['order_no'])."' ";
}
if($_GET['order_date_from']!="") {
$sql .= "AND post_date >= '".$_GET['order_date_from']."' ";
}
if($_GET['order_date_to']!="") {
$sql .= "AND post_date <= '".$_GET['order_date_to']."' ";
}
$sql .= "ORDER BY ID DESC";
$result = $wpdb->get_results($sql);
foreach($result as $orderObj){
$order = wc_get_order( $orderObj->ID );
$arr_country = explode("<br/>", $order->get_formatted_billing_address());
$country = array_filter($arr_country, 'trim');
//$completed_date = date('Y-m-d', strtotime($order->get_date_completed()));
$notes = $wpdb->get_results("SELECT * FROM wp_comments WHERE comment_post_ID = '".$orderObj->ID."' AND comment_content = 'Order status changed from Shipped to Completed.' ORDER BY comment_date DESC LIMIT 1");
$completed_date = substr($notes[0]->comment_date,0,10);
$show = true;
if($_GET['completed_date_from']!="" && $_GET['completed_date_to']!="") {
if($_GET['completed_date_from'] > $completed_date || $_GET['completed_date_to'] < $completed_date){
$show = false;
}
} else if($_GET['completed_date_from']!="") {
if($_GET['completed_date_from'] > $completed_date){
$show = false;
}
} else if($_GET['completed_date_to']!=""){
if($_GET['completed_date_to'] < $completed_date){
$show = false;
}
}
if($_GET['order_bill_to']!=""){
if (stripos($order->get_formatted_billing_full_name(), $_GET['order_bill_to']) === false) {
$show = false;
}
}
if($_GET['order_rebate_to']!=""){
if (stripos($order->get_meta('Rebate_to', true), $_GET['order_rebate_to']) === false) {
$show = false;
}
}
if($_GET['order_po_no']!=""){
if (stripos($order->get_meta('Cost1_po', true), $_GET['order_po_no']) === false) {
if (stripos($order->get_meta('Cost2_po', true), $_GET['order_po_no']) === false) {
if (stripos($order->get_meta('Cost3_po', true), $_GET['order_po_no']) === false) {
$show = false;
}
}
}
}
$shipping_hash = get_post_meta( $orderObj->ID, '_wc_shipment_tracking_items', true );
$str_shipping_hash = serialize($shipping_hash);
if($_GET['order_shipping_provider']!=""){
if (stripos($str_shipping_hash, $_GET['order_shipping_provider']) === false) {
$show = false;
}
}
if($_GET['order_tracking_no']!=""){
if (stripos($str_shipping_hash, $_GET['order_tracking_no']) === false) {
$show = false;
}
}
if($show) {
$shipping_cost = 0;
$cost_1_amount = 0;
$cost_2_amount = 0;
$cost_3_amount = 0;
$cost_1_supplier = "";
$cost_2_supplier = "";
$cost_3_supplier = "";
$cost_1_currency = "";
$cost_2_currency = "";
$cost_3_currency = "";
$cost_1_po = "";
$cost_2_po = "";
$cost_3_po = "";
$cost_subtotal = 0;
$rebate_to = "";
$rebate_amount = 0;
$payment_charge = 0;
$payment_received = 0;
$shipment_cost_margin = 0;
$tracking_info = "";
$claimed = "";
//$order_status = $order->get_status() == 'shipping-progress'?'shipped':$order->get_status();
$order_status = $arr_order_status[$orderObj->post_status];
$last_ship_date = "";
if(metadata_exists("post", $orderObj->ID, '_wc_shipment_tracking_items')){
$shipping_hash = $order->get_meta('_wc_shipment_tracking_items', true);
foreach($shipping_hash as $ship_line) {
$tracking_info .= $ship_line['tracking_provider']." / ".$ship_line['tracking_number']." / ".date("Y-m-d",$ship_line['date_shipped'])." / ".number_format($ship_line['shipping_cost'],2)."\r\n";
$shipping_cost += $ship_line['shipping_cost'];
if(date("Y-m-d",$ship_line['date_shipped'])>$last_ship_date) {
$last_ship_date = date("Y-m-d",$ship_line['date_shipped']);
}
}
}
if(metadata_exists("post", $orderObj->ID, 'Cost1_amount')){
$cost_1_amount = $order->get_meta('Cost1_amount', true);
}
if(metadata_exists("post", $orderObj->ID, 'Cost2_amount')){
$cost_2_amount = $order->get_meta('Cost2_amount', true);
}
if(metadata_exists("post", $orderObj->ID, 'Cost3_amount')){
$cost_3_amount = $order->get_meta('Cost3_amount', true);
}
if(metadata_exists("post", $orderObj->ID, 'Cost1_supplier')){
$cost_1_supplier = $order->get_meta('Cost1_supplier', true);
}
if(metadata_exists("post", $orderObj->ID, 'Cost2_supplier')){
$cost_2_supplier = $order->get_meta('Cost2_supplier', true);
}
if(metadata_exists("post", $orderObj->ID, 'Cost3_supplier')){
$cost_3_supplier = $order->get_meta('Cost3_supplier', true);
}
if(metadata_exists("post", $orderObj->ID, 'Cost1_currency')){
$cost_1_currency = $order->get_meta('Cost1_currency', true);
if($cost_1_currency!="")
$cost_subtotal += $cost_1_amount*get_currency_rate($cost_1_currency);
}
if(metadata_exists("post", $orderObj->ID, 'Cost2_currency')){
$cost_2_currency = $order->get_meta('Cost2_currency', true);
if($cost_2_currency!="")
$cost_subtotal += $cost_2_amount*get_currency_rate($cost_2_currency);
}
if(metadata_exists("post", $orderObj->ID, 'Cost3_currency')){
$cost_3_currency = $order->get_meta('Cost3_currency', true);
if($cost_3_currency!="")
$cost_subtotal += $cost_3_amount*get_currency_rate($cost_3_currency);
}
if(metadata_exists("post", $orderObj->ID, 'Cost1_po')){
$cost_1_po = $order->get_meta('Cost1_po', true);
}
if(metadata_exists("post", $orderObj->ID, 'Cost2_po')){
$cost_2_po = $order->get_meta('Cost2_po', true);
}
if(metadata_exists("post", $orderObj->ID, 'Cost3_po')){
$cost_3_po = $order->get_meta('Cost3_po', true);
}
if(metadata_exists("post", $orderObj->ID, 'Rebate_to')){
$rebate_to = $order->get_meta('Rebate_to', true);
}
if(metadata_exists("post", $orderObj->ID, 'Rebate_amount')){
$rebate_amount = $order->get_meta('Rebate_amount', true);
}
/*
if(metadata_exists("post", $orderObj->ID, '_paypal_transaction_fee')){
$payment_charge = -1 * $order->get_meta('_paypal_transaction_fee', true);
} elseif(metadata_exists("post", $orderObj->ID, '_stripe_fee')){
$payment_charge = -1 * $order->get_meta('_stripe_fee', true);
}
*/
if(metadata_exists("post", $orderObj->ID, 'Payment_charge')){
$payment_charge = -1*$order->get_meta('Payment_charge', true);
}
//$total_cost = $shipping_cost + $product_cost;
$discount = -1 * $order->get_discount_total();
$payment_received = $order->get_total() + $payment_charge;
//$net_total_order_amt = $order->get_subtotal() + $discount + $payment_charge;
$shipment_cost_margin = $order->get_shipping_total() - $shipping_cost;
if(metadata_exists("post", $orderObj->ID, 'claimed')){
$claimed = $order->get_meta('claimed', true);
}
if($claimed=="1"){
$display_claimed = "Y";
} else {
$display_claimed = "N";
}
$wsObj->setCellValueExplicitByColumnAndRow(0, $xlsRow, $order->get_id(), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(1, $xlsRow, date('Y-m-d', strtotime($order->get_date_created())), PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(2, $xlsRow, $order_status, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(3, $xlsRow, end($country), PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(4, $xlsRow, $order->get_formatted_billing_full_name(), PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(5, $xlsRow, $tracking_info, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(6, $xlsRow, number_format($shipping_cost,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(7, $xlsRow, $cost_1_supplier, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(8, $xlsRow, $cost_1_po, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(9, $xlsRow, $cost_1_currency, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(10, $xlsRow, number_format($cost_1_amount,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(11, $xlsRow, $cost_2_supplier, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(12, $xlsRow, $cost_2_po, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(13, $xlsRow, $cost_2_currency, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(14, $xlsRow, number_format($cost_2_amount,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(15, $xlsRow, $cost_3_supplier, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(16, $xlsRow, $cost_3_po, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(17, $xlsRow, $cost_3_currency, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(18, $xlsRow, number_format($cost_3_amount,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(19, $xlsRow, number_format($cost_subtotal,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(20, $xlsRow, number_format($order->get_subtotal(),2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(21, $xlsRow, number_format($order->get_shipping_total(),2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(22, $xlsRow, number_format($discount,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(23, $xlsRow, number_format($order->get_total(),2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(24, $xlsRow, number_format($payment_charge,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
// $wsObj->setCellValueExplicitByColumnAndRow(24, $xlsRow, number_format($payment_received,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(25, $xlsRow, $rebate_to, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(26, $xlsRow, number_format($rebate_amount,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(27, $xlsRow, number_format($shipment_cost_margin,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(28, $xlsRow, ($order->get_payment_method_title()==""?$order->get_meta('Payment', true):$order->get_payment_method_title()), PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(29, $xlsRow, (is_null($order->get_date_paid())?"0.00":$payment_received), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(30, $xlsRow, (is_null($order->get_date_paid())?"":date('Y-m-d', strtotime($order->get_date_paid()))), PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(31, $xlsRow, $order->get_meta('Payment_remark', true), PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(32, $xlsRow, $last_ship_date, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(33, $xlsRow, $completed_date, PHPExcel_Cell_DataType::TYPE_STRING);
$wsObj->setCellValueExplicitByColumnAndRow(34, $xlsRow, number_format($order->get_total(),2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$net_total_order_cost = $shipping_cost + $cost_subtotal - $payment_charge + $rebate_amount;
$wsObj->setCellValueExplicitByColumnAndRow(35, $xlsRow, number_format($net_total_order_cost,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$net_order_markup = $order->get_total() - $net_total_order_cost;
$wsObj->setCellValueExplicitByColumnAndRow(36, $xlsRow, number_format($net_order_markup,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$markup_percentage = $net_order_markup / $net_total_order_cost;
$wsObj->setCellValueExplicitByColumnAndRow(37, $xlsRow, number_format($markup_percentage,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$commission_percentage = ($markup_percentage >= 1 ? 5 : ($markup_percentage > 0.5 ? 2.5 : ($markup_percentage > 0 ? 1.5 : 0) ) );
$wsObj->setCellValueExplicitByColumnAndRow(38, $xlsRow, number_format($commission_percentage,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$commission = $order->get_total() * $commission_percentage / 100;
$wsObj->setCellValueExplicitByColumnAndRow(39, $xlsRow, number_format($commission,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
$wsObj->setCellValueExplicitByColumnAndRow(40, $xlsRow, $display_claimed, PHPExcel_Cell_DataType::TYPE_STRING);
$xlsRow++;
}
}
writeOutXls($xlsObj, $filename);
?>