LeeWeiCheng
/
altob.st123
1
0
Fork 0
Code Issues 0 Pull Requests 0 Releases 0 Wiki Activity
VM暫存
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.
268 Commits
5 Branches
790KB
Branch: master
Branches Tags
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
altob.st123/models/Excel_model.php

697 lines
31KB
Raw Permalink Blame History 

  1. <?php             

  2. /*

  3. file: Excel_model.php 匯出報表專用

  4. */                   

  5. class Excel_model extends CI_Model 

  6. {         

  7. 	function __construct()

  8. 	{

  9. 		parent::__construct(); 

  10. 		$this->load->database(); 

  11. 		

  12. 		$this->now_str = date('Y-m-d H:i:s'); 

  13. 		

  14. 		ini_set('max_execution_time','300');

  15. 		ini_set('memory_limit','512M');

  16. 		

  17. 		// 載入 excel

  18. 		$this->load->library('excel');

  19.     }   

  20.      

  21. 	public function init($vars)

  22. 	{

  23. 		$this->vars = $vars;

  24.     } 

  25. 	

  26. 	// 會員名單報表

  27. 	public function export_members()

  28. 	{

  29. 		trigger_error(EXPORT_LOG_TITLE. '..start..' . __FUNCTION__);

  30. 		

  31. 		// 讀入廠站資料

  32. 		$sql = "

  33. 					select

  34. 						members.member_name as member_name,

  35. 						members.lpr as lpr,

  36. 						members.contract_no as contract_no,

  37. 						members.start_date as start_date,

  38. 						members.end_date as end_date,

  39. 						members.amt as amt,

  40. 						members.update_time as update_time,

  41. 						members.member_attr,

  42. 						members.fee_period,

  43. 						members.mobile_no,

  44. 						members.deposit,

  45. 						members.suspended,

  46. 						members.locked,

  47. 						members.valid_time

  48. 					from members

  49. 					ORDER BY update_time DESC

  50. 				";

  51. 		

  52. 		$results = $this->db->query($sql)->result_array();

  53. 		

  54. 		if(empty($results))

  55. 		{

  56. 			trigger_error(EXPORT_LOG_TITLE.'..no data..' . $this->db->last_query());

  57. 			return false;

  58. 		}

  59. 		

  60. 		//$total_count = $this->db->query($sql)->num_rows();

  61. 		

  62. 		// 產生 Excel

  63. 		$objPHPExcel = new PHPExcel();

  64. 		$objPHPExcel->setActiveSheetIndex(0);

  65. 		$col_A_mapping = array('col_name' => 'A', 'col_title' => '會員名稱', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  66. 		$col_B_mapping = array('col_name' => 'B', 'col_title' => '車牌號碼', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  67. 		$col_C_mapping = array('col_name' => 'C', 'col_title' => '合約代碼', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  68. 		$col_D_mapping = array('col_name' => 'D', 'col_title' => '開始時間', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  69. 		$col_E_mapping = array('col_name' => 'E', 'col_title' => '結束時間', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  70. 		$col_F_mapping = array('col_name' => 'F', 'col_title' => '租金', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  71. 		$col_G_mapping = array('col_name' => 'G', 'col_title' => '最後更新時間', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  72. 		$col_H_mapping = array('col_name' => 'H', 'col_title' => '身份', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  73. 		$col_I_mapping = array('col_name' => 'I', 'col_title' => '繳期', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  74. 		$col_J_mapping = array('col_name' => 'J', 'col_title' => '電話', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  75. 		$col_K_mapping = array('col_name' => 'K', 'col_title' => '押金', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  76. 		$col_L_mapping = array('col_name' => 'L', 'col_title' => '停權 (營管操作)', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  77. 		$col_M_mapping = array('col_name' => 'M', 'col_title' => '鎖車 (會員操作)', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  78. 		$col_N_mapping = array('col_name' => 'N', 'col_title' => '有效期限 (審核後更新)', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  79. 

  80. 		$raw_index = 1;

  81. 		$objPHPExcel->getActiveSheet()->setTitle('下載');

  82. 		$objPHPExcel->getActiveSheet()->setCellValue($col_A_mapping['col_name'].$raw_index, $col_A_mapping['col_title']);

  83. 		$objPHPExcel->getActiveSheet()->setCellValue($col_B_mapping['col_name'].$raw_index, $col_B_mapping['col_title']);

  84. 		$objPHPExcel->getActiveSheet()->setCellValue($col_C_mapping['col_name'].$raw_index, $col_C_mapping['col_title']);

  85. 		$objPHPExcel->getActiveSheet()->setCellValue($col_D_mapping['col_name'].$raw_index, $col_D_mapping['col_title']);

  86. 		$objPHPExcel->getActiveSheet()->setCellValue($col_E_mapping['col_name'].$raw_index, $col_E_mapping['col_title']);

  87. 		$objPHPExcel->getActiveSheet()->setCellValue($col_F_mapping['col_name'].$raw_index, $col_F_mapping['col_title']);

  88. 		$objPHPExcel->getActiveSheet()->setCellValue($col_G_mapping['col_name'].$raw_index, $col_G_mapping['col_title']);

  89. 		$objPHPExcel->getActiveSheet()->setCellValue($col_H_mapping['col_name'].$raw_index, $col_H_mapping['col_title']);

  90. 		$objPHPExcel->getActiveSheet()->setCellValue($col_I_mapping['col_name'].$raw_index, $col_I_mapping['col_title']);

  91. 		$objPHPExcel->getActiveSheet()->setCellValue($col_J_mapping['col_name'].$raw_index, $col_J_mapping['col_title']);

  92. 		$objPHPExcel->getActiveSheet()->setCellValue($col_K_mapping['col_name'].$raw_index, $col_K_mapping['col_title']);

  93. 		$objPHPExcel->getActiveSheet()->setCellValue($col_L_mapping['col_name'].$raw_index, $col_L_mapping['col_title']);

  94. 		$objPHPExcel->getActiveSheet()->setCellValue($col_M_mapping['col_name'].$raw_index, $col_M_mapping['col_title']);

  95. 		$objPHPExcel->getActiveSheet()->setCellValue($col_N_mapping['col_name'].$raw_index, $col_N_mapping['col_title']);

  96. 		

  97. 		$warning_style = array(

  98. 			//'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER),

  99. 			'font'  => array(

  100. 		        'bold'  => true,

  101. 				'color' => array('rgb' => 'FF0000'),

  102. 		        'size'  => 16,

  103. 		        'name'  => 'Verdana'

  104. 		    )

  105. 		);

  106. 		

  107. 		$hq_info = $this->vars['mcache']->get('info'); 

  108. 

  109. 		$count = 0;

  110. 		foreach($results as $rows)

  111. 		{

  112. 			$raw_index += 1;

  113. 

  114. 			$member_name = $rows['member_name'];

  115. 			$lpr = $rows['lpr'];

  116. 			$contract_no = $rows['contract_no'] ? $rows['contract_no'] : '';

  117. 			$start_date = $rows['start_date'];

  118. 			$end_date = $rows['end_date'];

  119. 			$amt = $rows['amt'] ? $rows['amt'] : '0';

  120. 			$update_time = $rows['update_time'];

  121. 			$member_attr = ( empty($hq_info['member_attr']) || empty($rows['member_attr']) || empty($hq_info['member_attr'][$rows['member_attr']]) ) ? '無' : $hq_info['member_attr'][$rows['member_attr']];

  122. 			$fee_period = ( empty($hq_info['period_name']) || empty($rows['fee_period']) || empty($hq_info['period_name'][$rows['fee_period']]) ) ? '無' : $hq_info['period_name'][$rows['fee_period']];

  123. 			$mobile_no = $rows['mobile_no'];

  124. 			$deposit = $rows['deposit'];

  125. 			$suspended = (empty($rows['suspended'])) ? '無' : '已停權';

  126. 			$locked = (empty($rows['locked'])) ? '無' : '已鎖車';

  127. 			$valid_time = $rows['valid_time'];

  128. 

  129. 			$count++;

  130. 

  131. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_A_mapping['col_name'].$raw_index, $member_name, $col_A_mapping['col_type']);

  132. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_B_mapping['col_name'].$raw_index, $lpr, $col_B_mapping['col_type']);

  133. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_C_mapping['col_name'].$raw_index, $contract_no, $col_C_mapping['col_type']);

  134. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_D_mapping['col_name'].$raw_index, $start_date, $col_D_mapping['col_type']);

  135. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_E_mapping['col_name'].$raw_index, $end_date, $col_E_mapping['col_type']);

  136. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_F_mapping['col_name'].$raw_index, $amt, $col_F_mapping['col_type']);

  137. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_G_mapping['col_name'].$raw_index, $update_time, $col_G_mapping['col_type']);

  138. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_H_mapping['col_name'].$raw_index, $member_attr, $col_H_mapping['col_type']);

  139. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_I_mapping['col_name'].$raw_index, $fee_period, $col_I_mapping['col_type']);

  140. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_J_mapping['col_name'].$raw_index, $mobile_no, $col_J_mapping['col_type']);

  141. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_K_mapping['col_name'].$raw_index, $deposit, $col_K_mapping['col_type']);

  142. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_L_mapping['col_name'].$raw_index, $suspended, $col_L_mapping['col_type']);

  143. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_M_mapping['col_name'].$raw_index, $locked, $col_M_mapping['col_type']);

  144. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_N_mapping['col_name'].$raw_index, $valid_time, $col_N_mapping['col_type']);

  145. 			

  146. 			// 設定 style

  147. 			if($valid_time != $end_date)

  148. 			{

  149. 				$objPHPExcel->getActiveSheet()->getStyle($col_N_mapping['col_name'].$raw_index)->applyFromArray($warning_style);	

  150. 			}

  151. 		}

  152. 		

  153. 		// 網站下載

  154. 		$filename_prefix = iconv('UTF-8', 'Big5', '會員資料 - '. STATION_NAME);

  155. 		$filename_postfix = iconv('UTF-8', 'Big5', '(現況)');

  156. 		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

  157. 		header('Content-Type: application/vnd.ms-excel');

  158. 		header('Content-Disposition: attachment;filename="' . $filename_prefix. ' - ' . $filename_postfix . '.xlsx');

  159. 		header('Cache-Control: max-age=0');

  160. 		$objWriter->save('php://output');

  161. 		

  162. 		trigger_error(EXPORT_LOG_TITLE . '..completed..' . __FUNCTION__ . '|count:' . $count);

  163. 		

  164. 		return true;

  165. 	}

  166. 	

  167.     

  168. 	// 進出記錄報表

  169. 	public function export_cario_data($query_year, $query_month)

  170. 	{

  171. 		ini_set('max_execution_time','300');

  172. 		ini_set('memory_limit','512M');

  173. 		

  174. 		trigger_error(EXPORT_LOG_TITLE. '..start..' . __FUNCTION__ . "|{$query_year},{$query_month}");

  175. 		

  176. 		// 讀入廠站資料

  177. 		$sql = "

  178. 				SELECT

  179. 					cario.obj_id AS plate_no,

  180. 					cario.in_time as in_time,

  181. 					cario.out_time as out_time,

  182. 					members.member_name as member_name,

  183. 					CONCAT( FLOOR(HOUR(TIMEDIFF(cario.in_time, cario.out_time)) / 24), ' 日 ',

  184. 						MOD(HOUR(TIMEDIFF(cario.in_time, cario.out_time)), 24), ' 時 ',

  185. 						MINUTE(TIMEDIFF(cario.in_time, cario.out_time)), ' 分') as time_period

  186. 				FROM cario

  187. 					left join members on cario.member_no = members.member_no

  188. 				WHERE cario.err = 0 and cario.obj_id != 'NONE'

  189. 					and YEAR(cario.in_time) = {$query_year} and MONTH(cario.in_time) = {$query_month}

  190. 					and cario.out_time is not null

  191. 				ORDER BY cario.in_time ASC

  192. 			";

  193. 		

  194. 		$results = $this->db->query($sql)->result_array();

  195. 		

  196. 		if(empty($results))

  197. 		{

  198. 			trigger_error(EXPORT_LOG_TITLE.'..no data..' . $this->db->last_query());

  199. 			return false;

  200. 		}

  201. 		

  202. 		//$total_count = $this->db->query($sql)->num_rows();

  203. 		

  204. 		// 產生 Excel

  205. 		$objPHPExcel = new PHPExcel();

  206. 		$objPHPExcel->setActiveSheetIndex(0);

  207. 		$col_A_mapping = array('col_name' => 'A', 'col_title' => '車牌號碼', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  208. 		$col_B_mapping = array('col_name' => 'B', 'col_title' => '進場時間', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  209. 		$col_C_mapping = array('col_name' => 'C', 'col_title' => '離場日期', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  210. 		$col_D_mapping = array('col_name' => 'D', 'col_title' => '停車時數', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  211. 		$col_E_mapping = array('col_name' => 'E', 'col_title' => '場站名稱', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  212. 		$col_F_mapping = array('col_name' => 'F', 'col_title' => '會員名稱', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  213. 		$raw_index = 1;

  214. 		$objPHPExcel->getActiveSheet()->setTitle('下載');

  215. 		$objPHPExcel->getActiveSheet()->setCellValue($col_A_mapping['col_name'].$raw_index, $col_A_mapping['col_title']);

  216. 		$objPHPExcel->getActiveSheet()->setCellValue($col_B_mapping['col_name'].$raw_index, $col_B_mapping['col_title']);

  217. 		$objPHPExcel->getActiveSheet()->setCellValue($col_C_mapping['col_name'].$raw_index, $col_C_mapping['col_title']);

  218. 		$objPHPExcel->getActiveSheet()->setCellValue($col_D_mapping['col_name'].$raw_index, $col_D_mapping['col_title']);

  219. 		$objPHPExcel->getActiveSheet()->setCellValue($col_E_mapping['col_name'].$raw_index, $col_E_mapping['col_title']);

  220. 		$objPHPExcel->getActiveSheet()->setCellValue($col_F_mapping['col_name'].$raw_index, $col_F_mapping['col_title']);

  221. 

  222. 		$count = 0;

  223. 		foreach($results as $rows)

  224. 		{

  225. 			$raw_index += 1;

  226. 

  227. 			$plate_no = $rows['plate_no'];

  228. 			$in_time = $rows['in_time'];

  229. 			$out_time = $rows['out_time'];

  230. 			$time_period = $rows['time_period'];

  231. 			$member_name = $rows['member_name'] ? $rows['member_name'] : '臨停';

  232. 

  233. 			$count++;

  234. 

  235. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_A_mapping['col_name'].$raw_index, $plate_no, $col_A_mapping['col_type']);

  236. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_B_mapping['col_name'].$raw_index, $in_time, $col_B_mapping['col_type']);

  237. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_C_mapping['col_name'].$raw_index, $out_time, $col_C_mapping['col_type']);

  238. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_D_mapping['col_name'].$raw_index, $time_period, $col_D_mapping['col_type']);

  239. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_E_mapping['col_name'].$raw_index, STATION_NAME, $col_E_mapping['col_type']);

  240. 			$objPHPExcel->getActiveSheet()->setCellValueExplicit($col_F_mapping['col_name'].$raw_index, $member_name, $col_F_mapping['col_type']); 

  241. 		}

  242. 

  243. 		// 儲存檔案

  244. 		/*

  245. 		$filename_prefix = iconv('UTF-8', 'Big5', '車牌號碼進出記錄 - '. STATION_NAME);

  246. 		$filename_postfix = iconv('UTF-8', 'Big5', $query_year . '年' .$query_month.'月份');

  247. 		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

  248. 		$objWriter->save(EXPORT_BASE.$filename_prefix.' - '.$filename_postfix.'.xlsx');

  249. 		*/

  250. 		

  251. 		// 網站下載

  252. 		$filename_prefix = iconv('UTF-8', 'Big5', '車牌號碼進出記錄 - '. STATION_NAME);

  253. 		$filename_postfix = iconv('UTF-8', 'Big5', $query_year . '年' .$query_month.'月份');

  254. 		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

  255. 		header('Content-Type: application/vnd.ms-excel');

  256. 		header('Content-Disposition: attachment;filename="' . $filename_prefix. ' - ' . $filename_postfix . '.xlsx');

  257. 		header('Cache-Control: max-age=0');

  258. 		$objWriter->save('php://output');

  259. 		

  260. 		trigger_error(EXPORT_LOG_TITLE . '..completed..' . __FUNCTION__ . '|count:' . $count);

  261. 		

  262. 		return true;

  263. 	}

  264. 	

  265. 	// 分時統計表

  266. 	public function export_cario_summery($station_name, $query_year, $query_month, $addr='', $phone_no='')

  267. 	{

  268. 		trigger_error(EXPORT_LOG_TITLE. '..start..' . __FUNCTION__ . "|{$station_name},{$query_year},{$query_month},{$addr},{$phone_no}");

  269. 

  270. 		$sql = "

  271. 				SELECT

  272. 					cario.in_time AS in_time,

  273. 					cario.out_time AS out_time,

  274. 					cario.member_no as member_no

  275. 				FROM cario

  276. 				WHERE cario.err = 0 and cario.obj_id != 'NONE'

  277. 					and cario.out_time is not null

  278. 					and

  279. 					(

  280. 						(

  281. 							YEAR(cario.in_time) = {$query_year} and MONTH(cario.in_time) = {$query_month}

  282. 						)

  283. 						OR

  284. 						(

  285. 							YEAR(cario.out_time) = {$query_year} and MONTH(cario.out_time) = {$query_month}

  286. 						)

  287. 					)

  288. 				ORDER BY cario.in_time asc

  289. 			";

  290. 

  291. 		$results = $this->db->query($sql)->result_array();

  292. 		

  293. 		if(empty($results))

  294. 		{

  295. 			trigger_error(EXPORT_LOG_TITLE.'..no data..' . $this->db->last_query());

  296. 			return false;

  297. 		}

  298. 

  299. 		// PART 1: 產生分時陣列

  300. 		$KEY_SHEET_INDEX = 'sheet_index';	// 所在 sheet index

  301. 		$KEY_TITLE_INDEX = 'title_index';	// 所在 title incex

  302. 		$KEY_VALUE_INDEX = 'value_index';	// 所在 value incex

  303. 		$KEY_VALUE = 'value';	// value

  304. 		$KEY_MEMBER = 1;// 1：代表會員, 0：代表臨停

  305. 		$KEY_NONE = 0;

  306. 		$results_summary = array();

  307. 

  308. 		$this_day = new DateTime("{$query_year}-{$query_month}");

  309. 		$this_day_first = $this_day->format('Y-m-01');

  310. 		$this_day_last = $this_day->format('Y-m-t');

  311. 		$day_first_time = new DateTime($this_day_first. '00:00:00');

  312. 		$day_last_time = new DateTime($this_day_last. '23:59:59');

  313. 

  314. 		trigger_error(EXPORT_LOG_TITLE. '..' . __FUNCTION__ . "|{$this_day_first},{$this_day_last}");

  315. 

  316. 		foreach($results as $rows)

  317. 		{

  318. 			$member_no_key = $rows['member_no'] > 0 ? $KEY_MEMBER : $KEY_NONE;

  319. 

  320. 			// fetch input

  321. 			$in_time = new DateTime($rows['in_time']);

  322. 			$out_time = new DateTime($rows['out_time']);

  323. 			$in_time_hour_str = $in_time->format('Y-m-d H:00:00');

  324. 			$out_time_hour_str = $out_time->format('Y-m-d H:59:59');

  325. 

  326. 			// trim by limit

  327. 			$day_start = ($in_time < $day_first_time) ? $day_first_time : new DateTime($in_time_hour_str);

  328. 			$day_end = ($out_time > $day_last_time) ? $day_last_time : new DateTime($out_time_hour_str);

  329. 

  330. 			// loop

  331. 			$day_period = new DatePeriod($day_start, DateInterval::createFromDateString('1 hours'), $day_end);

  332. 			foreach ($day_period as $day)

  333. 			{

  334. 				$key_lv1 = $day->format('Y-m-d');	// 日期

  335. 				$key_lv2 = $day->format('H');		// 小時

  336. 				$key_lv3 = $member_no_key;			// 身份

  337. 

  338. 				$column_index_0 = 'D';

  339. 				$row_index_0 = 9;

  340. 

  341. 				if(!array_key_exists($key_lv1, $results_summary))

  342. 				{

  343. 					$results_summary[$key_lv1] = array();

  344. 				}

  345. 				if(!array_key_exists($key_lv2, $results_summary[$key_lv1]))

  346. 				{

  347. 					$results_summary[$key_lv1][$key_lv2] = array();

  348. 

  349. 					// 計算位置 (資料)

  350. 					for($column_index_key_lv1 = $column_index_0, $i = 0 ; $i < ((intval($day->format('d')) - 1) % 7) ; $i++)

  351. 					{

  352. 						$column_index_key_lv1++;

  353. 						$column_index_key_lv1++;

  354. 					}

  355. 

  356. 					$results_summary[$key_lv1][$key_lv2][$KEY_NONE] = array();

  357. 					$results_summary[$key_lv1][$key_lv2][$KEY_NONE][$KEY_SHEET_INDEX] = floor(intval($day->format('d') - 1) / 7) + 1; 		// 指定 sheet index

  358. 					$results_summary[$key_lv1][$key_lv2][$KEY_NONE][$KEY_TITLE_INDEX] = $column_index_key_lv1.$row_index_0;					// 指定 title index

  359. 

  360. 					$results_summary[$key_lv1][$key_lv2][$KEY_MEMBER] = $results_summary[$key_lv1][$key_lv2][$KEY_NONE];	// copy

  361. 				}

  362. 				if(!array_key_exists($KEY_VALUE_INDEX, $results_summary[$key_lv1][$key_lv2][$key_lv3]))

  363. 				{

  364. 					// 計算位置 (資料)

  365. 					for($column_index_key_lv1 = $column_index_0, $i = 0 ; $i < ((intval($day->format('d')) - 1) % 7) ; $i++)

  366. 					{

  367. 						$column_index_key_lv1++;

  368. 						$column_index_key_lv1++;

  369. 					}

  370. 					if($key_lv3 == $KEY_NONE)

  371. 					{

  372. 						$column_index_key_lv1++;	// 臨停

  373. 					}

  374. 					$results_summary[$key_lv1][$key_lv2][$key_lv3][$KEY_VALUE_INDEX] = $column_index_key_lv1. ($row_index_0 + intval($key_lv2) + 2);

  375. 					$results_summary[$key_lv1][$key_lv2][$key_lv3][$KEY_VALUE] = 0;	// 初始值

  376. 				}

  377. 

  378. 				$results_summary[$key_lv1][$key_lv2][$key_lv3][$KEY_VALUE] += 1;

  379. 			}

  380. 		}

  381. 

  382. 		// PART 2: 產生EXCEL

  383. 

  384. 		// 產生 Excel

  385. 		$inputFileName = FILE_BASE . "excel/sample2.xlsx";

  386. 		trigger_error(__FUNCTION__ . "..read: {$inputFileName}.." . file_exists($inputFileName));

  387. 		

  388. 		try 

  389. 		{

  390. 			$inputFileType = PHPExcel_IOFactory::identify($inputFileName);

  391. 			$objReader = PHPExcel_IOFactory::createReader($inputFileType);

  392. 			$objPHPExcel = $objReader->load($inputFileName);

  393. 		} catch(Exception $e) 

  394. 		{

  395. 			die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());

  396. 		}

  397. 

  398. 		$objPHPExcel->setActiveSheetIndex(0);

  399. 

  400. 		$sheet = $objPHPExcel->getActiveSheet();

  401. 		$sheet->setTitle('tmp');

  402. 

  403. 		$title_style = array(

  404. 			'alignment' => array(

  405. 				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,

  406. 			),

  407. 			'font'  => array(

  408. 		        'bold'  => true,

  409. 		        //'color' => array('rgb' => 'FF0000'),

  410. 		        'size'  => 20,

  411. 		        'name'  => 'Verdana'

  412. 		    )

  413. 		);

  414. 		$lpr_style = array(

  415. 			'alignment' => array(

  416. 				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,

  417. 			)

  418. 		);

  419. 		$basic_style = array(

  420. 	        'alignment' => array(

  421. 	            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,

  422. 	        )

  423. 	    );

  424. 

  425. 		// 標題

  426. 		$sheet_data_title = $station_name . $query_year. '年'. $query_month .'月份停車場分時統計表';

  427. 		$sheet->setCellValueExplicit('A1', $sheet_data_title, PHPExcel_Cell_DataType::TYPE_STRING);

  428. 		$sheet->getStyle('A1')->applyFromArray($title_style);

  429. 

  430. 		// 停車場名稱

  431. 		$sheet->setCellValueExplicit('E2', $station_name, PHPExcel_Cell_DataType::TYPE_STRING);

  432. 		$sheet->getStyle('E2')->applyFromArray($basic_style);

  433. 

  434. 		// 地址

  435. 		$sheet->setCellValueExplicit('E3', $addr, PHPExcel_Cell_DataType::TYPE_STRING);

  436. 		$sheet->getStyle('E3')->applyFromArray($basic_style);

  437. 

  438. 		// 電話

  439. 		$sheet->setCellValueExplicit('E4', $phone_no, PHPExcel_Cell_DataType::TYPE_STRING);

  440. 		$sheet->getStyle('E4')->applyFromArray($basic_style);

  441. 

  442. 		// 複製 sheet

  443. 		$sheet_base = $objPHPExcel->getActiveSheet();

  444. 

  445. 		$sheet1 = clone $sheet_base;

  446. 		$sheet1->setTitle($query_month. '月1號至7號');

  447. 		$objPHPExcel->addSheet($sheet1);

  448. 

  449. 		$sheet2 = clone $sheet_base;

  450. 		$sheet2->setTitle($query_month. '月8號至14號');

  451. 		$objPHPExcel->addSheet($sheet2);

  452. 

  453. 		$sheet3 = clone $sheet_base;

  454. 		$sheet3->setTitle($query_month. '月15號至21號');

  455. 		$objPHPExcel->addSheet($sheet3);

  456. 

  457. 		$sheet4 = clone $sheet_base;

  458. 		$sheet4->setTitle($query_month. '月22號至28號');

  459. 		$objPHPExcel->addSheet($sheet4);

  460. 

  461. 		$sheet5 = clone $sheet_base;

  462. 		$sheet5->setTitle($query_month. '月底');

  463. 		$objPHPExcel->addSheet($sheet5);

  464. 

  465. 		foreach ($results_summary as $key_lv1 => $results_summary_lv1)

  466. 		{

  467. 			$lv1_count = 0;

  468. 			foreach ($results_summary_lv1 as $key_lv2 => $results_summary_lv2)

  469. 			{

  470. 				if(empty($results_summary[$key_lv1][$key_lv2][$KEY_NONE][$KEY_SHEET_INDEX])) continue;

  471. 

  472. 				// 設定 日期值

  473. 				if($lv1_count == 0)

  474. 				{

  475. 					$title_index = $results_summary[$key_lv1][$key_lv2][$KEY_NONE][$KEY_TITLE_INDEX];

  476. 					if(!empty($title_index))

  477. 					{

  478. 						$sheet_index = $results_summary[$key_lv1][$key_lv2][$KEY_NONE][$KEY_SHEET_INDEX];

  479. 						$objPHPExcel->setActiveSheetIndex($sheet_index);

  480. 						$objPHPExcel->getActiveSheet()->setCellValueExplicit($title_index, $key_lv1, PHPExcel_Cell_DataType::TYPE_STRING);

  481. 					}

  482. 				}

  483. 				$lv1_count++;

  484. 

  485. 				// 設定 資料值

  486. 				$member_value_index = $results_summary[$key_lv1][$key_lv2][$KEY_MEMBER][$KEY_VALUE_INDEX];

  487. 				$member_value = $results_summary[$key_lv1][$key_lv2][$KEY_MEMBER][$KEY_VALUE];

  488. 				if(!empty($member_value_index))

  489. 				{

  490. 					$objPHPExcel->getActiveSheet()->setCellValueExplicit($member_value_index, $member_value, PHPExcel_Cell_DataType::TYPE_STRING);

  491. 				}

  492. 

  493. 				$none_value_index = $results_summary[$key_lv1][$key_lv2][$KEY_NONE][$KEY_VALUE_INDEX];

  494. 				$none_value = $results_summary[$key_lv1][$key_lv2][$KEY_NONE][$KEY_VALUE];

  495. 				if(!empty($none_value_index))

  496. 				{

  497. 					$objPHPExcel->getActiveSheet()->setCellValueExplicit($none_value_index, $none_value, PHPExcel_Cell_DataType::TYPE_STRING);

  498. 				}

  499. 			}

  500. 		}

  501. 

  502. 		// 儲存檔案

  503. 		$filename_prefix = iconv('UTF-8', 'Big5', '分時統計表 - '. $station_name);

  504. 		$filename_postfix = iconv('UTF-8', 'Big5', $query_year . '年' .$query_month.'月份');

  505. 		

  506. 		$sheetCount = $objPHPExcel->getSheetCount();

  507. 		$objPHPExcel->removeSheetByIndex(0);

  508. 		/*

  509. 		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

  510. 		$objWriter->save(EXPORT_BASE.$filename_prefix.' - '.$filename_postfix.'.xlsx');

  511. 		

  512. 		trigger_error(EXPORT_LOG_TITLE . '..' . __FUNCTION__ . '..completed..');

  513. 		return true;

  514. 		*/

  515. 		// 網站下載

  516. 		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

  517. 		header('Content-Type: application/vnd.ms-excel');

  518. 		header('Content-Disposition: attachment;filename="' . $filename_prefix. ' - ' . $filename_postfix . '.xlsx');

  519. 		header('Cache-Control: max-age=0');

  520. 		$objWriter->save('php://output');

  521. 		return true;

  522. 	}

  523. 	

  524. 	// 進出記錄表

  525. 	public function export_cario_report($station_name, $query_year, $query_month, $addr='', $phone_no='', $member_attr=0)

  526. 	{

  527. 		trigger_error(EXPORT_LOG_TITLE. '..start..' . __FUNCTION__ . "|{$station_name},{$query_year},{$query_month},{$addr},{$phone_no}");

  528. 

  529. 		$where_member_attr = !empty($member_attr) ? " and members.member_attr = {$member_attr} " : "";

  530. 		

  531. 		$sql = "

  532. 			SELECT

  533. 				cario.obj_id AS plate_no,

  534. 				cario.in_time as in_time,

  535. 				cario.out_time as out_time,

  536. 				members.member_name as member_name,

  537. 				CONCAT( FLOOR(HOUR(TIMEDIFF(cario.in_time, cario.out_time)) / 24), ' 日 ',

  538. 					MOD(HOUR(TIMEDIFF(cario.in_time, cario.out_time)), 24), ' 時 ',

  539. 					MINUTE(TIMEDIFF(cario.in_time, cario.out_time)), ' 分') as time_period

  540. 			FROM cario

  541. 				left join members on cario.obj_id = members.lpr

  542. 			WHERE cario.err = 0 and cario.obj_id != 'NONE'

  543. 				and YEAR(cario.in_time) = {$query_year} and MONTH(cario.in_time) = {$query_month}

  544. 				and cario.out_time is not null

  545. 				{$where_member_attr}

  546. 			ORDER BY cario.in_time ASC

  547. 		";

  548. 		

  549. 		$results = $this->db->query($sql)->result_array();

  550. 		

  551. 		if(empty($results))

  552. 		{

  553. 			trigger_error(EXPORT_LOG_TITLE.'..no data..' . $this->db->last_query());

  554. 			return false;

  555. 		}

  556. 

  557. 		// PART 2: 產生EXCEL

  558. 

  559. 		// 產生 Excel

  560. 		$inputFileName = FILE_BASE . "excel/sample1.xlsx";

  561. 		trigger_error(__FUNCTION__ . "..read: {$inputFileName}.." . file_exists($inputFileName));

  562. 		

  563. 		try 

  564. 		{

  565. 			$inputFileType = PHPExcel_IOFactory::identify($inputFileName);

  566. 			$objReader = PHPExcel_IOFactory::createReader($inputFileType);

  567. 			$objPHPExcel = $objReader->load($inputFileName);

  568. 		} catch(Exception $e) 

  569. 		{

  570. 			die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());

  571. 		}

  572. 

  573. 		$objPHPExcel->setActiveSheetIndex(0);

  574. 		$col_A0_mapping = array('col_name' => 'A', 'col_title' => '', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  575. 		$col_A_mapping = array('col_name' => 'B', 'col_title' => '車號', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  576. 		$col_B_mapping = array('col_name' => 'C', 'col_title' => '進場時間(A)', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  577. 		$col_C_mapping = array('col_name' => 'E', 'col_title' => '出場時間(B)', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  578. 		$col_D_mapping = array('col_name' => 'G', 'col_title' => '停車時間', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  579. 

  580. 		$col_E0_mapping = array('col_name' => 'I', 'col_title' => '', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  581. 		$col_E_mapping = array('col_name' => 'J', 'col_title' => '車號', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  582. 		$col_F_mapping = array('col_name' => 'K', 'col_title' => '進場時間(A)', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  583. 		$col_G_mapping = array('col_name' => 'M', 'col_title' => '出場時間(B)', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  584. 		$col_H_mapping = array('col_name' => 'O', 'col_title' => '停車時間', 'col_type' => PHPExcel_Cell_DataType::TYPE_STRING);

  585. 

  586. 		$raw_index = 10;

  587. 		$sheet = $objPHPExcel->getActiveSheet();

  588. 		$sheet->setTitle('下載');

  589. 

  590. 		$title_style = array(

  591. 			'alignment' => array(

  592. 				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,

  593. 			),

  594. 			'font'  => array(

  595. 		        'bold'  => true,

  596. 		        //'color' => array('rgb' => 'FF0000'),

  597. 		        'size'  => 20,

  598. 		        'name'  => 'Verdana'

  599. 		    )

  600. 		);

  601. 		$lpr_style = array(

  602. 			'alignment' => array(

  603. 				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,

  604. 			)

  605. 		);

  606. 		$basic_style = array(

  607. 	        'alignment' => array(

  608. 	            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,

  609. 	        )

  610. 	    );

  611. 

  612. 		// 標題

  613. 		$sheet_data_title = $station_name . $query_year. '年'. $query_month .'月份停車場延時統計表';

  614. 		$sheet->setCellValueExplicit('A1', $sheet_data_title, PHPExcel_Cell_DataType::TYPE_STRING);

  615. 		$sheet->getStyle('A1')->applyFromArray($title_style);

  616. 

  617. 		// 停車場名稱

  618. 		$sheet->setCellValueExplicit('D2', $station_name, PHPExcel_Cell_DataType::TYPE_STRING);

  619. 		$sheet->getStyle('D2')->applyFromArray($basic_style);

  620. 

  621. 		// 地址

  622. 		$sheet->setCellValueExplicit('D3', $addr, PHPExcel_Cell_DataType::TYPE_STRING);

  623. 		$sheet->getStyle('D3')->applyFromArray($basic_style);

  624. 

  625. 		// 電話

  626. 		$sheet->setCellValueExplicit('D4', $phone_no, PHPExcel_Cell_DataType::TYPE_STRING);

  627. 		$sheet->getStyle('D4')->applyFromArray($basic_style);

  628. 

  629. 		$count = 0;

  630. 		foreach($results as $rows)

  631. 		{

  632. 			$member_name = $rows['member_name'];

  633. 			$plate_no = $rows['plate_no'];

  634. 			$in_time = $rows['in_time'];

  635. 			$out_time = empty($rows['out_time']) ? '無離場記錄或車辨失敗' : $rows['out_time'];

  636. 			$time_period = $rows['time_period'];

  637. 

  638. 			if($count % 2 == 0)

  639. 			{

  640. 				$raw_index += 1;

  641. 				$sheet->setCellValueExplicit($col_A0_mapping['col_name'].$raw_index, $member_name, $col_A0_mapping['col_type']);

  642. 				$sheet->setCellValueExplicit($col_A_mapping['col_name'].$raw_index, $plate_no, $col_A_mapping['col_type']);

  643. 				$sheet->setCellValueExplicit($col_B_mapping['col_name'].$raw_index, $in_time, $col_B_mapping['col_type']);

  644. 				$sheet->setCellValueExplicit($col_C_mapping['col_name'].$raw_index, $out_time, $col_C_mapping['col_type']);

  645. 				$sheet->setCellValueExplicit($col_D_mapping['col_name'].$raw_index, $time_period, $col_D_mapping['col_type']);

  646. 				//$sheet->getStyle("A1:B1")->applyFromArray($style);

  647. 

  648. 				$sheet->getStyle($col_A0_mapping['col_name'].$raw_index)->applyFromArray($basic_style);

  649. 				$sheet->getStyle($col_A_mapping['col_name'].$raw_index)->applyFromArray($lpr_style);

  650. 				$sheet->getStyle($col_B_mapping['col_name'].$raw_index)->applyFromArray($basic_style);

  651. 				$sheet->getStyle($col_C_mapping['col_name'].$raw_index)->applyFromArray($basic_style);

  652. 				$sheet->getStyle($col_D_mapping['col_name'].$raw_index)->applyFromArray($basic_style);

  653. 			}

  654. 			else

  655. 			{

  656. 				$sheet->setCellValueExplicit($col_E0_mapping['col_name'].$raw_index, $member_name, $col_E0_mapping['col_type']);

  657. 				$sheet->setCellValueExplicit($col_E_mapping['col_name'].$raw_index, $plate_no, $col_E_mapping['col_type']);

  658. 				$sheet->setCellValueExplicit($col_F_mapping['col_name'].$raw_index, $in_time, $col_F_mapping['col_type']);

  659. 				$sheet->setCellValueExplicit($col_G_mapping['col_name'].$raw_index, $out_time, $col_G_mapping['col_type']);

  660. 				$sheet->setCellValueExplicit($col_H_mapping['col_name'].$raw_index, $time_period, $col_H_mapping['col_type']);

  661. 

  662. 				$sheet->getStyle($col_E0_mapping['col_name'].$raw_index)->applyFromArray($basic_style);

  663. 				$sheet->getStyle($col_E_mapping['col_name'].$raw_index)->applyFromArray($lpr_style);

  664. 				$sheet->getStyle($col_F_mapping['col_name'].$raw_index)->applyFromArray($basic_style);

  665. 				$sheet->getStyle($col_G_mapping['col_name'].$raw_index)->applyFromArray($basic_style);

  666. 				$sheet->getStyle($col_H_mapping['col_name'].$raw_index)->applyFromArray($basic_style);

  667. 			}

  668. 

  669. 			$count++;

  670. 		}

  671. 		

  672. 		// 儲存檔案

  673. 		$filename_prefix = iconv('UTF-8', 'Big5', '車牌號碼進出記錄 - '. $station_name);

  674. 		$filename_postfix = iconv('UTF-8', 'Big5', $query_year . '年' .$query_month.'月份');

  675. 		

  676. 		$sheetCount = $objPHPExcel->getSheetCount();

  677. 		$objPHPExcel->removeSheetByIndex($sheetCount - 1);

  678. 

  679. 		/*

  680. 		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

  681. 		$objWriter->save(EXPORT_BASE.$filename_prefix.' - '.$filename_postfix.'.xlsx');

  682. 		

  683. 		trigger_error(EXPORT_LOG_TITLE . '..' . __FUNCTION__ . '..completed..' . "count: {$count}");

  684. 		return true;

  685. 		*/

  686. 		

  687. 		// 網站下載

  688. 		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

  689. 		header('Content-Type: application/vnd.ms-excel');

  690. 		header('Content-Disposition: attachment;filename="' . $filename_prefix. ' - ' . $filename_postfix . '.xlsx');

  691. 		header('Cache-Control: max-age=0');

  692. 		$objWriter->save('php://output');

  693. 		return true;

  694. 	}

  695. 	

  696. }