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.
82 lines
4.2 KiB
82 lines
4.2 KiB
<?php
|
|
#error_reporting(E_ALL);
|
|
|
|
#ini_set('display_errors', "On");
|
|
ob_start();
|
|
$user_id = '';
|
|
require_once "../database.php";
|
|
|
|
if ($_SERVER["REQUEST_METHOD"] == "POST" || $_SERVER["REQUEST_METHOD"] == "GET") {
|
|
|
|
// include "header.php";
|
|
// 载入db.php来连结资料库
|
|
require_once "../database.php";
|
|
require_once 'Classes/PHPExcel.php';
|
|
require_once 'Classes/PHPExcel/IOFactory.php';
|
|
//$target_dir = "../inv-uploads/";
|
|
$target_file = 'bacth_invoice_template.xls';
|
|
$EXTENSION = pathinfo($target_file, PATHINFO_EXTENSION);
|
|
|
|
$objReader = PHPExcel_IOFactory::createReader('Excel5'); //支持xls
|
|
$objPHPExcel = $objReader->load($target_file); //读取上传到服务器的文件
|
|
$sheet = $objPHPExcel->getSheet(0); //拿到第一个sheet数据
|
|
$highestRow = $sheet->getHighestRow(); // 取得总行数
|
|
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
|
|
$highestColumn = PHPExcel_Cell::columnIndexFromString($highestColumn); // 转换字母为对应的统计数字；
|
|
$excelArray = array();
|
|
$success_in = 0;
|
|
$fail_in = 0;
|
|
$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 . "'";
|
|
|
|
|
|
$i = 2;
|
|
$sql_get = "select max(item_name) item_name ,sum(summary_price) summary_price ,max(customer_name) customer_name
|
|
,max(inv_customer_id) inv_customer_id 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);
|
|
#發票程式改一下，數量統一用1，單價用稅後合計。4/19日
|
|
#excel 第一行 从B3-H3
|
|
$yyy = date('Y') - 1911;
|
|
$first_day = $yyy . "/" . date('m/01');
|
|
$last_day = $yyy . "/" . date('m/t');
|
|
//is_numeric(substr($val['inv_customer_id'],0,1))
|
|
foreach ($res_get as $key => $val) {
|
|
$sheet->setCellValueExplicit('A' . ($key + 3), $key + 1, \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('B' . ($key + 3), date("Y-m-d"), \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('C' . ($key + 3), 1, \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('D' . ($key + 3), '電梯保養費', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('E' . ($key + 3), 1, \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('F' . ($key + 3), ((int) $val['summary_price']), \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('J' . ($key + 3), is_numeric(substr($val['inv_customer_id'], 0, 1)) ? $val['inv_customer_id'] : "", \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('G' . ($key + 3), '1', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('K' . ($key + 3), is_numeric(substr($val['inv_customer_id'], 0, 1)) ? '1' : '', \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('H' . ($key + 3), '0.05', \PHPExcel_Cell_DataType::TYPE_STRING); #税率
|
|
$sheet->setCellValueExplicit('S' . ($key + 3), $val['customer_name'], \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('T' . ($key + 3), $first_day . "-" . $last_day, \PHPExcel_Cell_DataType::TYPE_STRING);
|
|
}
|
|
//页面导出
|
|
header('Content-Type: application/vnd.ms-excel');
|
|
|
|
//设置下载的文件名称
|
|
header('Content-Disposition: attachment;filename=' . urlencode('批次開立發票') . '.xls');
|
|
header('Cache-Control: max-age=0');
|
|
|
|
header("Content-Type:application/octet-stream");
|
|
// If you're serving to IE over SSL, then the following may be needed
|
|
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
|
|
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
|
|
header('Pragma: public'); // HTTP/1.0
|
|
|
|
|
|
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
|
|
$objWriter->save('php://output');
|
|
}
|
|
|
|
#header("location: batch_invoice_export.php");
|
|
|
|
die();
|
|
|