File: /var/www/html/breadsecret.com_bak20260325/report/monthlySalesReportIncompleted.php
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
// email setting
define("MAIL_HOST","mail.winghung.com");
define("MAIL_PORT","2525");
define("MAIL_USER","admin@winghung.com");
define("MAIL_PASSWORD","IdRbc(0wxD4#");
define("MAIL_ADMIN","admin@winghung.com");
// import plugin
include '/var/www/html/breadsecret.com/wp-load.php';
include '/var/www/html/breadsecret.com/custom/PHPExcel.php';
include '/var/www/html/breadsecret.com/custom/PHPExcel/IOFactory.php';
// set timezone
date_default_timezone_set("Asia/Hong_Kong");
global $wpdb, $current_site, $sitepress;
// week start day and end day
$last_month_end_date = date("Y-m-t", strtotime("-1 months"));
// report path
$filePath = '/var/www/html/ossfs/Breadsecret/monthlySalesReportIncompleted/';
$fileName = "BSMonthlySalesReportIncompleted_".$last_month_end_date;
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
$styleArray = array(
'font' => array(
'bold' => true,
)
);
function num2Letter($num) {
$num = intval($num);
if ($num <= 0)
return false;
$letterArr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
$letter = '';
do {
$key = ($num - 1) % 26;
$letter = $letterArr[$key] . $letter;
$num = floor(($num - $key) / 26);
} while ($num > 0);
return $letter;
}
// set add column width
for ($n = 1; $n <= 16; $n++) {
$col = num2Letter($n);
$objPHPExcel->getActiveSheet()->getColumnDimension($col)->setWidth('20');
}
// set specific column width
// $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth('60');
$objPHPExcel->getActiveSheet()->getStyle('A1:T1')->applyFromArray($styleArray);
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'Complete Date')
->setCellValue('B1', 'Delivery Date')
->setCellValue('C1', 'Order Number')
->setCellValue('D1', 'Order Status')
->setCellValue('E1', 'Order Date')
->setCellValue('F1', 'First Name (Billing)')
->setCellValue('G1', 'Payment Method Title')
->setCellValue('H1', 'Order Total Amount (HKD)')
->setCellValue('I1', 'Order Total Amount - Refund Amount (HKD)')
->setCellValue('J1', 'Strip Fee (HKD)')
->setCellValue('K1', 'Payment')
->setCellValue('L1', 'Payment Terms')
->setCellValue('M1', 'Order Shipping Amount (HKD)')
->setCellValue('N1', 'Order Subtotal Amount (HKD)')
->setCellValue('O1', 'Cart Discount Amount (HKD)')
->setCellValue('P1', 'Order Total Fee (HKD)')
->setCellValue('Q1', 'System Order ID')
->setCellValue('R1', 'Transaction Ref. #')
->setCellValue('S1', 'Handling Fee (HKD)')
->setCellValue('T1', 'Liquidation Amount (HKD)')
;
$post_status = implode("','", array('wc-pending', 'wc-processing', 'wc-on-hold') );
//get all orders
$result = $wpdb->get_results("SELECT * FROM wp_posts WHERE post_type = 'shop_order' AND post_status in ('{$post_status}') AND post_date <= '".$last_month_end_date." 23:59:59'");
//$orders = wc_get_orders(array(
// 'limit'=>-1,
//'type'=> 'shop_order',
//'date_completed'=> $query_initial_date.'...'. $query_final_date,
//'order'=> 'asc'
// )
//);
$arrData = array();
foreach($result as $ordObj){
$arrOrder = array();
$order = new WC_Order($ordObj->ID);
$arrOrder['ordID'] = $ordObj->ID;
$arrOrder['ordNumber'] = $order->get_order_number();
$arrOrder['completeDate'] = get_post_meta($ordObj->ID, "_completed_date", true);
$arrOrder['deliveryDate'] = get_post_meta($ordObj->ID, "delivery_date", true);
$arrOrder['orderDate'] = $order->get_date_created()->date_i18n('Y-m-d H:i:s');
$arrOrder['orderStatus'] = ucfirst($order->get_status());
$arrOrder['billTo'] = $order->get_formatted_billing_full_name();
$arrOrder['paymentMethodTitle'] = $order->get_payment_method_title();
$arrOrder['paymentMethod'] = get_post_meta($ordObj->ID, "Payment", true);
$arrOrder['paymentTerms'] = get_post_meta($ordObj->ID, "payment_terms", true);
$arrOrder['orderTotalAmount'] = $order->get_total();
$arrOrder['refundAmount'] = get_post_meta($ordObj->ID, "_refund_amount", true)==""?0:get_post_meta($ordObj->ID, "_refund_amount", true);
$arrOrder['netOrderTotalAmount'] = $order->get_total() - (get_post_meta($ordObj->ID, "_refund_amount", true)==""?0:get_post_meta($ordObj->ID, "_refund_amount", true));
$arrOrder['stripeFee'] = get_post_meta($ordObj->ID, "_stripe_fee", true)==""?0:get_post_meta($ordObj->ID, "_stripe_fee", true);
$arrOrder['orderShippingAmount'] = $order->get_shipping_total();
$arrOrder['orderSubtotalAmount'] = $order->get_subtotal();
$arrOrder['cartDiscountAmount'] = $order->get_discount_total();
$fee_total = 0;
foreach( $order->get_items('fee') as $item_id => $item_fee ){
$fee_name = $item_fee->get_name();
$fee_total = $item_fee->get_total();
}
$arrOrder['orderTotalFee'] = $fee_total;
$arrOrder['transactionReferenceNo'] = get_post_meta($ordObj->ID, "transactionreferenceno", true);
$arrOrder['handlingFee'] = get_post_meta($ordObj->ID, "handling_fee", true)==""?0:get_post_meta($ordObj->ID, "handling_fee", true);
$arrOrder['liquidationAmount'] = get_post_meta($ordObj->ID, "liquidation_amount", true)==""?0:get_post_meta($ordObj->ID, "liquidation_amount", true);
array_push($arrData, $arrOrder);
}
$xlsRow = 2;
if (count($arrData)>0){
foreach($arrData as $data){
$objPHPExcel->setActiveSheetIndex(0)
->setCellValueExplicit('A'.$xlsRow, $data['completeDate'], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValueExplicit('B'.$xlsRow, $data['deliveryDate'], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValueExplicit('C'.$xlsRow, $data['ordNumber'], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValueExplicit('D'.$xlsRow, $data['orderStatus'], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValueExplicit('E'.$xlsRow, $data['orderDate'], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValueExplicit('F'.$xlsRow, $data['billTo'], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValueExplicit('G'.$xlsRow, $data['paymentMethodTitle'], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValueExplicit('H'.$xlsRow, $data['orderTotalAmount'], PHPExcel_Cell_DataType::TYPE_NUMERIC)
->setCellValueExplicit('I'.$xlsRow, $data['netOrderTotalAmount'], PHPExcel_Cell_DataType::TYPE_NUMERIC)
->setCellValueExplicit('J'.$xlsRow, $data['stripeFee'], PHPExcel_Cell_DataType::TYPE_NUMERIC)
->setCellValueExplicit('K'.$xlsRow, $data['paymentMethod'], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValueExplicit('L'.$xlsRow, $data['paymentTerms'], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValueExplicit('M'.$xlsRow, $data['orderShippingAmount'], PHPExcel_Cell_DataType::TYPE_NUMERIC)
->setCellValueExplicit('N'.$xlsRow, $data['orderSubtotalAmount'], PHPExcel_Cell_DataType::TYPE_NUMERIC)
->setCellValueExplicit('O'.$xlsRow, $data['cartDiscountAmount'], PHPExcel_Cell_DataType::TYPE_NUMERIC)
->setCellValueExplicit('P'.$xlsRow, $data['orderTotalFee'], PHPExcel_Cell_DataType::TYPE_NUMERIC)
->setCellValueExplicit('Q'.$xlsRow, $data['ordID'], PHPExcel_Cell_DataType::TYPE_NUMERIC)
->setCellValueExplicit('R'.$xlsRow, $data['transactionReferenceNo'], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValueExplicit('S'.$xlsRow, $data['handlingFee'], PHPExcel_Cell_DataType::TYPE_NUMERIC)
->setCellValueExplicit('T'.$xlsRow, $data['liquidationAmount'], PHPExcel_Cell_DataType::TYPE_NUMERIC)
;
$xlsRow++;
}
} else {
$objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit('A'.$xlsRow, "No Record Found", PHPExcel_Cell_DataType::TYPE_STRING);
}
// create excel and save at OSS
$objPHPExcel->getActiveSheet()->setTitle("BSMonthlySalesReportIncompleted");
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($filePath.$fileName.'.xls');
// define email content
//$arr_email = array("samiel_tsang@winghung.com");
$arr_email = array("kam_lee@winghung.com","katherine_li@winghung,com","kimi_yu@winghung.com");
$cc = "kei_lee@winghung.com";
$bcc = "samiel_tsang@winghung.com";
//$arr_email = array("kei_lee@winghung.com");
$subject = "[Breadsecret] Monthly Sales Report for Incomplete Orders (until ".$last_month_end_date.")";
$ordHead = "<style type='text/css'>
.tg {border-collapse:collapse;border-spacing:0;border-color:#aaa;border:none;width:100%}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:0px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:0px;overflow:hidden;word-break:normal;border-color:#aaa;color:#fff;background-color:#00bbd9;}
.tg .tg-j2zy{background-color:#eee;vertical-align:top}
.tg .tg-baqh{text-align:center;vertical-align:top}
.tg .tg-zczf{background-color:#eee;text-align:right;vertical-align:top}
.tg .tg-lqy6{text-align:right;vertical-align:top}
.tg .tg-yw4l{vertical-align:top}</style>";
$message = $ordHead;
$message .= "<table class='tg'>";
$message .= "<tr>";
$message .= "<th class='tg-baqh'>".$subject."</th>";
$message .= "</tr>";
$message .= "<tr>";
$message .= "<td class='tg-yw4l'><b>Dear recipient,<br><br>This is an auto email sent from Bread Secret Website. Do not reply this email directly.<br><br>Attached please find monthly sales report for incomplete orders until ".$last_month_end_date.")</b></td>";
$message .= "</tr>";
$message .= "<tr>";
$message .= "<td class='tg-yw4l'><b>Bread Secret</b></td>";
$message .= "</tr>";
$message .= "</table>";
// send email
date_default_timezone_set('Etc/UTC');
require_once('/var/www/html/breadsecret.com/custom/PHPMailer-master/PHPMailerAutoload.php');
$mail = new PHPMailer;
$mail->CharSet = 'UTF-8';
$mail->isSMTP();
$mail->SMTPDebug = 0;
$mail->Debugoutput = 'html';
$mail->Host = MAIL_HOST;
$mail->Port = MAIL_PORT;
$mail->SMTPAuth = true;
$mail->Username = MAIL_USER;
$mail->Password = MAIL_PASSWORD;
$mail->setFrom(MAIL_ADMIN, MAIL_ADMIN);
$mail->addAttachment($filePath.$fileName.'.xls');
foreach($arr_email as $to){
$to = trim($to);
$mail->addAddress($to, $to);
}
$mail->addCC($cc, $cc);
$mail->addBCC($bcc, $bcc);
$mail->Subject = $subject;
$mail->isHTML(true);
$mail->Body = $message;
$mail->AltBody = $message;
$mail->send();
?>