File: /var/www/html/amberconcept/test.php
<head>
<link href="https://cdn.datatables.net/2.1.8/css/dataTables.dataTables.css" rel="stylesheet">
<link href="https://cdn.datatables.net/buttons/3.2.0/css/buttons.dataTables.css" rel="stylesheet">
<style>
table, td, th {
border: 1px solid;
}
table {
width: 100%;
border-collapse: collapse;
}
</style>
</head>
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
//include wp libriries and set timezone to HK
require_once('wp-load.php');
date_default_timezone_set("Asia/Hong_Kong");
global $wpdb, $sitepress;
$today = date("Y-m-d");
$statement_month = isset($_GET['statement_month'])?$_GET['statement_month']:"2024-10";
$bill_index = "Judy Leung Fotomax Inc. Unit 02-04, West Wing, 8/F Tsuen Wan Industrial Centre 220 Texaco Road, Tsuen Wan, HK Tsuen Wan NEW TERRITORIES 00852 HK cs@amberconcept.com 31891600";
//$post_status_string = implode("','", array('wc-pending', 'wc-processing', 'wc-completed', 'wc-shipping-progress') ); // shipped and paid order
$post_status_string = implode("','", array('wc-completed') ); // shipped order
$sql = "
SELECT * FROM wp_posts order_master
LEFT JOIN wp_postmeta order_detail ON order_master.ID = order_detail.post_id
WHERE order_master.post_type = 'shop_order' AND order_master.post_status IN ('{$post_status_string}')
AND order_detail.meta_key = '_billing_address_index' AND order_detail.meta_value = '".$bill_index."'
GROUP BY order_master.ID ORDER BY ID DESC LIMIT 100
";
$result = $wpdb->get_results($sql);
?>
<!-- Today: <?=$today;?> -->
<select name='bill_to' id='bill_to'>
<?php
$sql_select = "SELECT * FROM wp_posts order_master LEFT JOIN wp_postmeta order_detail ON order_master.ID = order_detail.post_id WHERE order_master.post_type = 'shop_order' AND order_master.post_status IN ('wc-completed') AND order_detail.meta_key = '_billing_address_index' group by order_detail.meta_value Order by order_detail.meta_value ASC";
$result_select = $wpdb->get_results($sql_select);
foreach($result_select as $bill_obj) {
echo "<option value='".$bill_obj->meta_value."'>";
echo $bill_obj->meta_value;
echo "</option>";
}
?>
</select>
<input type='month' name='statement_month' id='statement_month'>
<table width="100%" id='dataTable' class='stripe hover'>
<thead>
<tr>
<th>Statement Month</th>
<th>Order Status</th>
<th>Invoice Number</th>
<th>Customer PO no.</th>
<th>Shipment Date</th>
<th>Currency</th>
<th>Amount (HKD)</th>
<th>Age (Days)</th>
<th>Due Date</th>
<th>Status</th>
<th>Running Balance (HKD)</th>
</tr>
</thead>
<tbody>
<?php
$arr_data = [];
$running_total = 0;
foreach ($result as $order_obj ){
$order = wc_get_order($order_obj->ID);
$shipping_hash = get_post_meta( $order_obj->ID, '_wc_shipment_tracking_items', true );
$name = $order->get_billing_first_name()." ".$order->get_billing_last_name();
$number = $order->get_order_number();
$order_status = $order->get_status();
foreach ( $order->get_items() as $item_id => $item ) {
$po = $item->get_meta( 'fotomaxpo', true );
}
$ship_date = date("Y-m-d",$shipping_hash[0]['date_shipped']);
if($statement_month >= substr($ship_date, 0, 7)){
$amount = $order->get_total();
$currency = "HKD";
$due_date = date("Y-m-d",$shipping_hash[0]['date_shipped']+30*86400);
$age = (strtotime($today) - strtotime($ship_date)) / 86400;
$status = $age>30?"OVERDUE":"NORMAL";
$arr_data[] = [
"statement_month"=>substr($ship_date, 0, 7),
"name"=>$name,
"order_status"=>$order_status,
"number"=>$number,
"po"=>$po,
"ship_date"=>$ship_date,
"currency"=>$currency,
"amount"=>$amount,
"age"=>$age,
"due_date"=>$due_date,
"status"=>$status
];
}
}
usort($arr_data, function($a, $b) {
return $b['ship_date'] <=> $a['ship_date'];
});
foreach($arr_data as $line) {
$running_total += $line['amount'];
echo "<tr>";
echo "<td>".$line['statement_month']."</td>";
echo "<td>".$line['order_status']."</td>";
echo "<td>".$line['number']."</td>";
echo "<td>".$line['po']."</td>";
echo "<td>".$line['ship_date']."</td>";
echo "<td>".$line['currency']."</td>";
echo "<td>".number_format($line['amount'],2,".",",")."</td>";
echo "<td>".$line['age']."</td>";
echo "<td>".$line['due_date']."</td>";
echo "<td>".$line['status']."</td>";
echo "<td>".number_format($running_total,2,".",",")."</td>";
echo "</tr>";
}
?>
</tbody>
</table>
<script src="https://code.jquery.com/jquery-3.7.1.js"></script>
<script src="https://cdn.datatables.net/2.1.8/js/dataTables.js"></script>
<script src="https://cdn.datatables.net/buttons/3.2.0/js/dataTables.buttons.js"></script>
<script src="https://cdn.datatables.net/buttons/3.2.0/js/buttons.dataTables.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/3.2.0/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/3.2.0/js/buttons.print.min.js"></script>
<script>
$(document).ready( function () {
$('#dataTable').DataTable({
searching: false, paging: false, info: false, ordering: false,
dom: 'Bfrtip',
buttons: [
{
extend: 'excel', title: ''
},
{
extend: 'print', title: ''
}
],
});
} );
</script>