You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
174 lines
9.9 KiB
174 lines
9.9 KiB
<?php
|
|
#error_reporting(E_ALL);
|
|
#ini_set('display_errors', "On");
|
|
ob_start();
|
|
$user_id = '73376';
|
|
require_once "../database.php";
|
|
/**
|
|
|
|
|
|
|
|
*/
|
|
|
|
if ($_SERVER["REQUEST_METHOD"] == "POST" || $_SERVER["REQUEST_METHOD"] == "GET") {
|
|
|
|
// include "header.php";
|
|
// 载入db.php来连结资料库
|
|
require_once "../database.php";
|
|
require_once '../PHPExcel/PHPExcel.php';
|
|
require_once '../PHPExcel/IOFactory.php';
|
|
//$target_dir = "../inv-uploads/";
|
|
$target_file = 'data_collection_merge_template.xlsx';
|
|
$EXTENSION = pathinfo($target_file, PATHINFO_EXTENSION);
|
|
|
|
if ($EXTENSION == 'xls') {
|
|
$objReader = PHPExcel_IOFactory::createReader('Excel5'); //支持xls
|
|
} else {
|
|
$objReader = PHPExcel_IOFactory::createReader('Excel2007'); //支持xlsx是
|
|
}
|
|
|
|
|
|
$objPHPExcel = $objReader->load($target_file); //读取上传到服务器的文件
|
|
$sheet = $objPHPExcel->getSheet(0); //拿到第一个sheet数据
|
|
$highestRow = $sheet->getHighestRow(); // 取得总行数
|
|
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
|
|
$highestColumn = PHPExcel_Cell::columnIndexFromString($highestColumn); // 转换字母为对应的统计数字；
|
|
$excelArray = array();
|
|
$data = [];
|
|
$data['yyyymm'] = $_SERVER['REQUEST_METHOD'] == 'GET' ? $_GET['yyyymm'] : $_POST['yyyymm'];
|
|
$form_key = $_SERVER['REQUEST_METHOD'] == 'GET' ? $_GET['form_key'] : $_POST['form_key'];
|
|
$where_yyyymm = " and yyyymm like '" . $data['yyyymm'] . "%'";
|
|
$where_form_key = empty($form_key) ? "" : " and form_key like '" . $form_key . "'";
|
|
// $sql_get = "select *From ec_purchase_order where 1=1 $where_yyyymm $where_form_key ";
|
|
$sql_get = "select customerid ,customer_name,GROUP_CONCAT(item_no) item_list, GROUP_CONCAT(summary_price) summary_list
|
|
,max(yyyymm) yyyymm From ec_purchase_order
|
|
where 1=1 $where_yyyymm $where_form_key group by customerid ";
|
|
$res_get = mysqli_fetch_all(mysqli_query($link, $sql_get), MYSQLI_ASSOC);
|
|
$i = 2;
|
|
$remark1 = "1、自動櫃員機轉帳(ATM)暨網路ATM 繳款: (1)選「轉帳」(2)輸入華南銀行代號「008」(3) 輸入轉入帳號「繳款帳號」及「應繳金額」。若為跨行轉帳繳款需另支付手續費15元。";
|
|
$remark2 = "2、其他銀行或郵局匯款：填寫匯款單，收款行「華南銀行」，分支單位「福和分行」，戶名「永佳捷科技股份有限公司」，轉入帳號「繳款帳號」及 「應繳金額」，需另支付匯款手續費30元。";
|
|
$remark3 = "3、便利商店繳款：持本繳款通知書可至7-11、全家、萊爾富、OK 等便利商店直接繳款，2萬元內需另支付手續費10元，2萬元至4萬元內需另支付手續費15元。";
|
|
$remark4 = "4、華銀各分行繳款：本繳款通知書可直接繳款，不用支付手續費。";
|
|
$maxcol = 25;
|
|
#excel 第一行 从B3-H3
|
|
foreach ($res_get as $key => $val) {
|
|
// var_dump( $key);
|
|
// $_val = $sheet->getCellByColumnAndRow(0, $i++)->getCalculatedValue();
|
|
//if ($_val == "") return;
|
|
// $value = $sheet->getCell($key)->getValue();
|
|
// var_dump( $val);
|
|
#第10/11/12月取最后一位数字 . substr($val['customerid'], 1) . $bank_last_1
|
|
//$bank_last_1 = strlen(date('m')) == 2 ? substr(date('m'), 1) : date('m');
|
|
# 1 銀行編碼
|
|
$sheet->setCellValueExplicit('A' . ($key + 2), '95665', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#2 繳款帳號
|
|
$sheet->setCellValueExplicit('B' . ($key + 2), '', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#3 客戶屬性
|
|
$sheet->setCellValueExplicit('C' . ($key + 2), '維修與保養費', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#4 幣別
|
|
$sheet->setCellValueExplicit('D' . ($key + 2), 'TWD', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#5 客戶代號
|
|
$sheet->setCellValueExplicit('E' . ($key + 2), substr($val['customerid'], 1), \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
# 6客戶名稱
|
|
$sheet->setCellValueExplicit('F' . ($key + 2), $val['customer_name'], \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
# 7 繳費說明1
|
|
$sheet->setCellValueExplicit('G' . ($key + 2), $remark1, \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
# 8 繳費說明2
|
|
$sheet->setCellValueExplicit('H' . ($key + 2), $remark2, \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#9 繳費說明3
|
|
$sheet->setCellValueExplicit('I' . ($key + 2), $remark3, \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#10 繳費說明4
|
|
$sheet->setCellValueExplicit('J' . ($key + 2), $remark4, \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#11 繳費說明5
|
|
$sheet->setCellValueExplicit('K' . ($key + 2), '', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
|
|
#12 繳款截止日
|
|
$_yyyymm1 = date("Ymd", strtotime("+1 months", strtotime($val['yyyymm'] . "15")));
|
|
$_yyyymm2 = (int)$_yyyymm1 - 19110000;
|
|
$sheet->setCellValueExplicit('L' . ($key + 2), $_yyyymm2, \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#13 期別說明
|
|
$sheet->setCellValueExplicit('M' . ($key + 2), substr($_yyyymm2, 0, 3) . '年' . substr($val['yyyymm'], 4, 2) . "月", \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#14 繳費通路別-銀行
|
|
$sheet->setCellValueExplicit('N' . ($key + 2), '1', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
|
|
|
|
#15 繳費通路別-郵局
|
|
$sheet->setCellValueExplicit('O' . ($key + 2), '0', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#16 繳費通路別-超商
|
|
$sheet->setCellValueExplicit('P' . ($key + 2), '1', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#17應繳總額
|
|
$sql_fetch = "select sum(summary_price) val2 From ec_purchase_order
|
|
where 1=1 and customerid='" . $val['customerid'] . "' $where_yyyymm
|
|
$where_form_key group by customerid ";
|
|
list($summary_price) = mysqli_fetch_row(mysqli_query($link, $sql_fetch));
|
|
$sheet->setCellValueExplicit('Q' . ($key + 2), $summary_price, \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
|
|
|
|
#17 保留欄1
|
|
$sheet->setCellValueExplicit('R' . ($key + 2), '', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#18 保留欄2
|
|
$sheet->setCellValueExplicit('S' . ($key + 2), '', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
|
|
//select concat(item_no,'-',item_name) val1, sum(summary_price) val2 From ec_purchase_order group by customerid
|
|
|
|
// $sql_fetch = "select concat(item_no,'-',item_name) val1 , sum(summary_price) val2 From ec_purchase_order
|
|
// where 1=1 and customerid='".$val['customerid']."' $where_yyyymm $where_form_key group by customerid ";
|
|
// list($item_name,$summary_price)= mysqli_fetch_row(mysqli_query($link, $sql_fetch));
|
|
//list($item_name,$summary_price)
|
|
|
|
//A列:0 AA:26 T:19
|
|
// echo PHPExcel_Cell::stringFromColumnIndex(19);exit();
|
|
|
|
//customer_name,GROUP_CONCAT(item_no), GROUP_CONCAT(summary_price)
|
|
$item_list = explode(",", $val['item_list']);
|
|
$summary_list = explode(",", $val['summary_list']);
|
|
$col = 19; //T
|
|
for ($i = 0; $i < count($item_list); $i++) {
|
|
#19 收費項目
|
|
// echo PHPExcel_Cell::stringFromColumnIndex( $col ) . ($key + 2);
|
|
$sheet->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($col) . (1), "收費項目", \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
|
|
$sheet->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($col) . ($key + 2), $item_list[$i] , \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#20 應繳金額
|
|
$sheet->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($col + 1) . (1), "應繳金額", \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
// echo PHPExcel_Cell::stringFromColumnIndex( $col+1 ).($key + 2);
|
|
$sheet->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($col + 1) . ($key + 2), $summary_list[$i], \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#22 說明
|
|
$sheet->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($col + 2) . (1), "說明", \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
|
|
// echo PHPExcel_Cell::stringFromColumnIndex( $col +2). ($key + 2);
|
|
$sheet->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($col + 2) . ($key + 2), '', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
$col += 3;
|
|
if ($col > $maxcol) $maxcol = $col;
|
|
}
|
|
#23 收費項目
|
|
// $sheet->setCellValueExplicit('W' . ($key + 2), '', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#24 應繳金額
|
|
// $sheet->setCellValueExplicit('X' . ($key + 2), '', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#25 說明 普来特富(上海)智能系统有限公司
|
|
// $sheet->setCellValueExplicit('Y' . ($key + 2), '', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
#26 結尾符號
|
|
$sheet->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($maxcol) . (1), '結尾符號', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
// $data[$excel_field_mapping[$key]] = (empty($value) || $value == '') ? 'null' : "'" . $value . "'"; //读取excel数据到数组
|
|
}
|
|
|
|
//页面导出
|
|
header('Content-Encoding: Big5');
|
|
header('Content-type: text/csv; charset=Big5');
|
|
//设置下载的文件名称
|
|
header('Content-Disposition: attachment;filename="代收資料匯入檔".csv');
|
|
header('Cache-Control: max-age=0');
|
|
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
|
|
header('Cache-Control: cache, must-revalidate');
|
|
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV')->setDelimiter(',')
|
|
->setEnclosure('')
|
|
->setLineEnding(";\r\n");
|
|
$file_name = "tmp/" . strtotime("now") . ".csv";
|
|
$objWriter->save($file_name);
|
|
$file_data = file_get_contents($file_name);
|
|
$big5_file_data = iconv('UTF8', 'Big5', $file_data);
|
|
file_put_contents("php://output", $big5_file_data);
|
|
|
|
|
|
// $objWriter->save('php://output');
|
|
}
|
|
|