| Server IP : 210.245.233.93 / Your IP : 216.73.216.226 Web Server : Apache/2.2.15 (CentOS) System : Linux webserver2.onesolution.com.hk 2.6.32-754.35.1.el6.x86_64 #1 SMP Sat Nov 7 12:42:14 UTC 2020 x86_64 User : apache ( 48) PHP Version : 5.3.3 Disable Function : exec, shell_exec, system, passthru, popen, proc_open, pcntl_exec MySQL : ON | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /var/www/hkosl.com/innoutstorage/webadmin/ |
Upload File : |
<?php
require_once("check_login.php");
require_once dirname(__FILE__) . '/PHPExcel_1.8.0/Classes/PHPExcel.php';
if (isset($_GET['start']) && isset($_GET['end'])) {
$filter_sql = "";
if (!empty($_GET['start']) && !empty($_GET['end'])) {
$filter_sql = " AND createdate >= '" . $_GET['start'] . "' AND '" . $_GET['end'] . "' >= createdate";
}
$sql = "select * from `order` where deleted = 0 " . $filter_sql . " order by id DESC";
$data = bind_pdo($sql, $parameters, "selectall");
$excel = new PHPExcel();
$sheet = $excel->getActiveSheet();
$sheet->setTitle("Daily sales report");
$sheet->getCellByColumnAndRow("A", 1)->setValueExplicit("Daily sales report");
$sheet->getStyle("A1")->getFont()->setBold(true);
$sheet->getCellByColumnAndRow("A", 2)->setValueExplicit("Date: " . date("d/m/Y"));
$header = array('日期', '合約編號', '單位編號', '面積(平方呎)', '類型', '客戶名稱', '租用期(月)', '每月租金', '總租金', '按金', '優惠', '智能咭金額', '合約總計(折扣後)', '付款方式');
foreach ($header as $k => $h) {
$sheet->getCellByColumnAndRow($k, 5)->setValueExplicit($h);
}
$row = 0;
$total_rent = 0;
$total_deposit = 0;
$total_discount = 0;
$total_services_charge = 0;
$total_fee = 0;
foreach ($data as $i => $r) {
$order_room_info = get_order_room($r["id"]);
$order_room_list = "";
foreach ($order_room_info as $detail) {
$room_info = get_room($detail["room_id"]);
$order_room_list .= check_combine_room($room_info["code"]) . ", ";
}
$order_room_list = substr_replace($order_room_list, "", -2);
$order_status = get_master_type_code("ORDER_STATUS", $r["status"]);
if($r["customer_typeid"] == "PERSONAL"){
$name = get_customer_name(rsa_crypt($r["customer_firstname"], 2), rsa_crypt($r["customer_lastname"], 2));
} else{
$name = $r["customer_companyname"];
}
//unit change
$unit = "";
if($r["createdate"] <= "2017-04-27 18:00:00"){
$unit = "(立方呎)";
}
$rent = $r["total_discounted_price"]*$r["rent_month"] - $r["last_paid"];
$discount = $r["free_first_month_deduct"]+$r["free_2_month_deduct"]+$r["discount_amount"];
$fee = $rent+$r["deposit"]+$r["total_order_product"]-$discount;
$paymethod = get_master_type_code("PAYMENT_METHOD", $r["paymethod"]);
if(empty($r["paymethod2"]))
$r["paymethod2"] = $r["paymethod"];
$paymethod2 = get_master_type_code("PAYMENT_METHOD", $r["paymethod2"]);
if($r['status'] != "VOID") {
$total_rent += $rent;
$total_deposit += $r["deposit"];
$total_discount += $discount;
$total_services_charge += $r["total_order_product"];
$total_fee += $fee;
}
$result = array(date('d/m/Y', strtotime($r['createdate'])), $r['code'], $order_room_list, $r['roughly_size'] . $unit, $order_status['name_tc'], $name, $r['rent_month'], ($r["total_discounted_price"]), ($rent), ($r["deposit"]), ($discount), ($r["total_order_product"]), ($fee), $paymethod2["name_tc"]);
$number_00_for_col = array('H','I','J','K','L','M');
$number_for_col = array(/*'D','G'*/);
$row = $i + 6;
foreach($result as $j => $v) {
$columnLetter = PHPExcel_Cell::stringFromColumnIndex($j);
if(in_array($columnLetter,$number_00_for_col)) {
//covert into number for column
$sheet->getCell($columnLetter . $row)->setValue($v);
$sheet->getCell($columnLetter . $row)
->getStyle($columnLetter . $row)
->getNumberFormat()
->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00
);
}else if(in_array($columnLetter,$number_for_col)){
$sheet->getCell($columnLetter . $row)->setValue($v);
$sheet->getCell($columnLetter . $row)
->getStyle($columnLetter . $row)
->getNumberFormat()
->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_NUMBER
);
}else {
$sheet->getCellByColumnAndRow($j, $row)->setValueExplicit($v);
}
}
}
if (!empty($data)) {
$sheet->getStyle('A' . $row . ':' . 'N' . $row)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$row++;
$styleArray = array(
'font' => array(
'underline' => PHPExcel_Style_Font::UNDERLINE_DOUBLE
)
);
/*$sheet->getCellByColumnAndRow(8, $row)->setValueExplicit(number_format($total_rent));
$sheet->getStyle('I' . $row)->applyFromArray($styleArray);
$sheet->getCellByColumnAndRow(9, $row)->setValueExplicit(number_format($total_deposit));
$sheet->getStyle('J' . $row)->applyFromArray($styleArray);
$sheet->getCellByColumnAndRow(10, $row)->setValueExplicit(number_format($total_discount));
$sheet->getStyle('K' . $row)->applyFromArray($styleArray);
$sheet->getCellByColumnAndRow(11, $row)->setValueExplicit(number_format($total_services_charge));
$sheet->getStyle('L' . $row)->applyFromArray($styleArray);
$sheet->getCellByColumnAndRow(12, $row)->setValueExplicit(number_format($total_fee));
$sheet->getStyle('M' . $row)->applyFromArray($styleArray);*/
$columnLetter = 'I';
$value = $total_rent;
$sheet->getCell($columnLetter . $row)->setValue($value);
$sheet->getCell($columnLetter . $row)
->getStyle($columnLetter . $row)
->getNumberFormat()
->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00
);
$columnLetter = 'J';
$value = $total_deposit;
$sheet->getCell($columnLetter . $row)->setValue($value);
$sheet->getCell($columnLetter . $row)
->getStyle($columnLetter . $row)
->getNumberFormat()
->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00
);
$columnLetter = 'K';
$value = $total_discount;
$sheet->getCell($columnLetter . $row)->setValue($value);
$sheet->getCell($columnLetter . $row)
->getStyle($columnLetter . $row)
->getNumberFormat()
->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00
);
$columnLetter = 'L';
$value = $total_services_charge;
$sheet->getCell($columnLetter . $row)->setValue($value);
$sheet->getCell($columnLetter . $row)
->getStyle($columnLetter . $row)
->getNumberFormat()
->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00
);
$columnLetter = 'M';
$value = $total_fee;
$sheet->getCell($columnLetter . $row)->setValue($value);
$sheet->getCell($columnLetter . $row)
->getStyle($columnLetter . $row)
->getNumberFormat()
->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00
);
}
$objWriter = new PHPExcel_Writer_Excel2007($excel);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="DateDetailedBreakdownReport_' . date('Ymd') . '.xlsx"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit();
}
?>
<!DOCTYPE html>
<html>
<head>
<?php require_once("html_head.php"); ?>
<script type="text/javascript">
</script>
<style>
</style>
</head>
<body>
<table width="1200" height="600" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="200" align="left" valign="top">
<table width="200" border="0" cellpadding="0" cellspacing="0">
<tr>
<td height="70" align="left" valign="middle"> </td>
</tr>
<tr>
<td width="200" align="left" valign="top"><!-- Main Menu -->
<?php require("menu.php"); ?><!-- End Main Menu --></td>
</tr>
</table>
</td>
<td width="1000" align="left" valign="top">
<table width="1000" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<table width="1000" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="70" class="pagetitletxt"> </td>
<td width="50" align="center" class="icontxt">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td class="pagetitletxt">
<b><img src="images/iconList.jpg" width="48" height="48" align="absmiddle"/>
匯出報表
</b></td>
</tr>
<tr>
<td height="25" align="left" valign="middle" class="msg"><?php if (isset($_GET["msg"])) echo $_GET['msg']; ?></td>
</tr>
<tr>
<td align="left" valign="middle">
<form action="" method="get">
<p>建立日期</p>
<input type="date" name="start" required> 至 <input type="date" name="end" required>
<button type="submit">確認</button>
</form>
</td>
</tr>
</table>
</td>
</tr>
</table>
<script>
$(document).ready( function () {
});
</script>
</body>
</html>