File: /var/www/html/amberconcept/statement_pdf.php
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
//include wp libriries and set timezone to HK
require_once('wp-load.php');
require_once('PHPExcel.php');
require_once('TCPDF/data/tcpdf_include.php');
date_default_timezone_set("Asia/Hong_Kong");
if(isset($_POST['statement_month']) && $_POST['statement_month']!="") {
if(isset($_POST['bill_to']) && $_POST['bill_to']!="") {
;
} else {
echo "Invalid Bill_to";
exit();
}
} else {
echo "Invalid Month";
exit();
}
function getHeaderString($wpdb, $bill_to){
$sql = "SELECT post_id, meta_value AS company_name FROM wp_postmeta WHERE meta_key = '_billing_address_index' AND meta_value = '".$bill_to."' LIMIT 1";
$result = $wpdb->get_results($sql);
$order_id = $result[0]->post_id;
$order = wc_get_order( $order_id );
// draw header table
return $htmlHeader = '<div id="headerSection" >
<table cellspacing="2" cellpadding="1">
<tr>
<td >
<table id="amber_table" cellspacing="1" cellpadding="1" style="text-align: left;">
<tr>
<td><img src="custom/image/Amber.png" width="150"></td>
</tr>
<tr>
<td>Amber Concept Limited</td>
</tr>
<tr>
<td>6A, Blk 1 Kin Ho Industrial Building<br>14-24 Au Pui Wan Street<br>Fo Tan, Hong Kong<br>Tel: 852-22785768</td>
</tr>
</table>
</td>
<td >
<table id="bill_to_table" cellspacing="1" cellpadding="2" border="0.01">
<tr>
<td style="font-size:10px; font-weight:bold; background-color:grey; color: white;">Date:</td>
</tr>
<tr>
<td>'.date("d-M-Y").'</td>
</tr>
<tr>
<td style="font-size:10px; font-weight:bold; background-color:grey; color: white;">Bill To: </td>
</tr>
<tr>
<td>'.$order->get_billing_company().'<br>'.$order->get_billing_address_1().' '.$order->get_billing_address_2().'<br>Tel: 852-'.$order->get_billing_phone().'
</td>
</tr>
</table>
</td>
</tr>
</table>
</div>';
}
function genLineTable($wpdb, $month, $bill_to){
$today = date("d-M-Y");
$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_to."'
GROUP BY order_master.ID ORDER BY ID DESC LIMIT 100
";
$result = $wpdb->get_results($sql);
$htmlLine ='<div id="headerSection2" style=""><table cellspacing="2" cellpadding="3" style="text-align:center">
<thead>
<tr style="background-color:grey; color:white; text-align:left"><th colspan="9">Billing Note:</th></tr>
<tr style="background-color:#eee;"><th colspan="9"></th></tr>
<tr style="background-color:grey; color:white">
<th style="line-height:1.5">Invoice<br>Number</th>
<th style="line-height:1.5">Customer PO<br>No.</th>
<th style="line-height:1.5">Shipment<br>Date</th>
<th style="line-height:3">Currency</th>
<th style="line-height:1.5">Amount<br>(HK$)</th>
<th style="line-height:1.5">Age<br>(Days)</th>
<th style="line-height:3">Due Date</th>
<th style="line-height:3">Status</th>
<th>Running Balance<br>(HK$)</th>
</tr>
</thead>
<tbody>';
$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("d-M-Y",$shipping_hash[0]['date_shipped']);
if($month >= substr($ship_date, 0, 7)){
$amount = $order->get_total();
$currency = "HKD";
$due_date = date("d-M-Y",$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'];
$htmlLine .= '<tr style="background-color:#eee">';
$htmlLine .= '<td>'.$line['number'].'</td>';
$htmlLine .= '<td>'.$line['po'].'</td>';
$htmlLine .= '<td>'.$line['ship_date'].'</td>';
$htmlLine .= '<td>'.$line['currency'].'</td>';
$htmlLine .= '<td>'.number_format($line['amount'],2,".",",").'</td>';
$htmlLine .= '<td>'.$line['age'].'</td>';
$htmlLine .= '<td>'.$line['due_date'].'</td>';
$htmlLine .= '<td>'.$line['status'].'</td>';
$htmlLine .= '<td>'.number_format($running_total,2,".",",").'</td>';
$htmlLine .= '</tr>';
}
$htmlLine .= '<tr><td colspan="9"></td></tr>';
$htmlLine .= '<tr style="background-color:grey; color:white">';
$htmlLine .= '<td colspan="7" style="text-align: left !important;">Payment can be made by company cheque, cash creditable at HK, or wire transfer to the following account:</td>';
$htmlLine .= '<td>Total (HK$)</td>';
$htmlLine .= '<td>'.number_format($running_total,2,".",",").'</td>';
$htmlLine .= '</tr>';
$htmlLine .= '</tbody>';
$htmlLine .= '<tfoot>';
$htmlLine .= '<tr style="background-color:grey; color:white">';
$htmlLine .= '<td colspan="1" style="text-align: left !important; line-height:3">Bank Name:</td>';
$htmlLine .= '<td colspan="3" style="text-align: left !important; line-height:3">Hang Seng Bank, Ltd.</td>';
$htmlLine .= '<td colspan="1" style="text-align: left !important; ">Account Name:</td>';
$htmlLine .= '<td colspan="2" style="text-align: left !important; line-height:3">Amber Concept Limited</td>';
$htmlLine .= '<td colspan="2" style="text-align: left !important;"></td>';
$htmlLine .= '</tr>';
$htmlLine .= '<tr style="background-color:grey; color:white">';
$htmlLine .= '<td colspan="1" style="text-align: left !important;">Bank Address:</td>';
$htmlLine .= '<td colspan="3" style="text-align: left !important;">Shops 9-10, 1/F, Tung Ning Bldg, 251 Des Voeux Road, Central, Hong Kong</td>';
$htmlLine .= '<td colspan="1" style="text-align: left !important; line-height:3">Swift Code:</td>';
$htmlLine .= '<td colspan="2" style="text-align: left !important; line-height:3">HASEHKHH</td>';
$htmlLine .= '<td colspan="2" style="text-align: left !important;"></td>';
$htmlLine .= '</tr>';
$htmlLine .= '</tfoot>';
$htmlLine .= '</table></div>';
return $htmlLine;
}
// Extend the TCPDF class to create custom Header and Footer
class MYPDF extends TCPDF {
public $htmlHeader;
public $htmlLine;
//Page header
public function Header() {
$this->writeHTMLCell(0, 0, '', '', $this->htmlHeader, 0, 1, 0, true, '', true);
$this->writeHTMLCell(0, 0, '', '', '<span style="font-size:20px; font-weight:bold; text-decoration: underline;">Monthly Statement</span>', 0, 1, 0, true, '', true);
$this->writeHTMLCell(0, 0, '', '', '', 0, 1, 0, true, '', true);
$this->writeHTMLCell(0, 0, '', '', $this->htmlLine, 0, 1, 0, true, '', true);
//$this->Line(10, 38, 200, 38, array('width' => 0.5));
}
// Page footer
public function Footer() {
$this->Line(10, 286, 200, 286, array('width' => 0.3));
$this->Cell(205, 0, 'Page '.$this->getAliasNumPage().'/'.$this->getAliasNbPages(), 0, false, 'R', 0, '', 0, false, 'T', 'M');
}
}
// create new PDF document
$pdf = new MYPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->htmlHeader = getHeaderString($wpdb, $_POST['bill_to']);
$pdf->htmlLine = genLineTable($wpdb, $_POST['statement_month'], $_POST['bill_to']);
// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('WH-HKMIS');
$pdf->SetTitle('AMBER STATEMENT');
$pdf->SetSubject('AMBER STATEMENT');
$pdf->SetKeywords('AMBER STATEMENT');
// set default header data
$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE, PDF_HEADER_STRING);
// set header and footer fonts
$pdf->setHeaderFont(Array('DroidSansFallback', '', PDF_FONT_SIZE_DATA));
$pdf->setFooterFont(Array('DroidSansFallback', '', PDF_FONT_SIZE_DATA));
// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
// set margins
$pdf->SetMargins(PDF_MARGIN_LEFT, 80, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(10);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, 65);
// set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
// ---------------------------------------------------------
// set font
$pdf->SetFont('DroidSansFallback', '', 7, '', true);
$pdf->Output($poObj->po_number.'.pdf', 'I');
//============================================================+
// END OF FILE
//============================================================+