<?php
/**
 * @copyright ICZ Corporation (http://www.icz.co.jp/)
 * @license See the LICENCE file
 * @author <matcha@icz.co.jp>
 * @version $Id$
 */

class ExcelComponent extends Object
{
	var $errors = array();

	/**
	 * 出力用メソッド
	 *
	 * @param object $_controller
	 * @param array  $_field
	 * @param array  $_data
	 * @param string $_file_name
	 */
	function outputXls($_controller, $_field = array(), $_data = array(), $_file_name = "data")
	{
		//出力初期設定
		$_controller->layout = false;
		$_controller->autoRender = false;
		Configure::write('debug', 0);

		//読み込み
		App::import('vendor','phpexcel/phpexcel');

		$excel = new PHPExcel();
		$excel->setActiveSheetIndex(0);
		$sheet = $excel->getActiveSheet();

		//文字設定
		$sheet->getDefaultStyle()->getFont()->setName(mb_convert_encoding('ＭＳ ゴシック', 'UTF-8'));

		//列の幅指定
		$sheet->getColumnDimension('A')->setWidth(3);
		$sheet->getColumnDimension('B')->setWidth(16);//日付
		$sheet->getColumnDimension('C')->setWidth(8);//番号
		$sheet->getColumnDimension('D')->setWidth(24);//取引先
		$sheet->getColumnDimension('E')->setWidth(24);//件名
		$sheet->getColumnDimension('F')->setWidth(16);//自社担当者
		$sheet->getColumnDimension('G')->setWidth(22);//小計
		$sheet->getColumnDimension('H')->setWidth(22);//消費税
		$sheet->getColumnDimension('I')->setWidth(22);//合計
		$sheet->getColumnDimension('J')->setWidth(20);//納入場所
		$sheet->getColumnDimension('K')->setWidth(20);//有効期限

		//タイトル
		if($_controller->name === 'Quote'):
			$sheet->setTitle('見積書一覧');
			$sheet->setCellValue('B'.'2', '見積書一覧');
		elseif($_controller->name === 'Bill'):
			$sheet->setTitle('請求書一覧');
			$sheet->setCellValue('B'.'2', '請求書一覧');
		elseif($_controller->name === 'Delivery'):
			$sheet->setTitle('納品書一覧');
			$sheet->setCellValue('B'.'2', '納品書一覧');
		endif;
		$sheet->getStyle('B'.'2')->getFont()->setBold(true);

		$start = $_controller->params['data'][$_controller->name]['DATE1']['year']."-".$_controller->params['data'][$_controller->name]['DATE1']['month']."-".$_controller->params['data'][$_controller->name]['DATE1']['day'];
		$end   = $_controller->params['data'][$_controller->name]['DATE2']['year']."-".$_controller->params['data'][$_controller->name]['DATE2']['month']."-".$_controller->params['data'][$_controller->name]['DATE2']['day'];
		$n_str = $_controller->params['data'][$_controller->name]['DATE1']['year'].$_controller->params['data'][$_controller->name]['DATE1']['month'].$_controller->params['data'][$_controller->name]['DATE1']['day'];
		$n_end = $_controller->params['data'][$_controller->name]['DATE2']['year'].$_controller->params['data'][$_controller->name]['DATE2']['month'].$_controller->params['data'][$_controller->name]['DATE2']['day'];

		//期間の設定
		$sheet->setCellValue('D'.'3', '期間　'.date('Y年m月d日', strtotime($start)).'　～　'.date('Y年m月d日', strtotime($end)));

		//ウィンドウ枠の固定
		$sheet->freezePane('C6');

		//横初期位置
		$col = 'B';

		//フィールドの設定
		if(is_array($_field)):
			foreach($_field as $key => $value):

				//値の挿入
				$sheet->setCellValue($col.'5', $value);

				//スタイルの取得
				$style = $sheet->getStyle($col++.'5');

				//中央配置
				$style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

				//線の設定
				$style->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
				$style->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
				$style->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
				$style->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);

				//背景色の設定
				$style->getFill()->setFillType( PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCFFCC');

			endforeach;
		endif;

		//初期位置
		$row = 6;

		$subtotal  = 0;
		$sales_tax = 0;
		$total     = 0;

		//データの設定
		if(is_array($_data)):
			foreach($_data as $key1 => $value1):
				$col = 'B';
				if(is_array($value1)):
					foreach($value1 as $key2 => $value2):

						//１番左
						if(reset(array_keys($value1)) === $key2):
							$sheet->getStyle($col.$row)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						endif;

						//１番右
						if(end(array_keys($value1)) === $key2):
							$sheet->getStyle($col.$row)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						endif;

						//１番下
						if(end(array_keys($_data)) === $key1):
							$sheet->getStyle($col.$row)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
						endif;

						//値の挿入
						if($key2 == 6):
							$subtotal  += $value2;
							$sheet->getStyle($col.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
							$sheet->setCellValue($col++.$row, number_format($value2));
						elseif($key2 == 7):
							$sales_tax += $value2;
							$sheet->getStyle($col.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
							$sheet->setCellValue($col++.$row, number_format($value2));
						elseif($key2 == 8):
							$total     += $value2;
							$sheet->getStyle($col.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
							$sheet->setCellValue($col++.$row, number_format($value2));
						else:
							$sheet->setCellValue($col++.$row, $value2);
						endif;

					endforeach;
				endif;
				$row++;
			endforeach;
		endif;

		//横初期位置
		$col = 'B';

		//合計金額の設定
		if(is_array($_field)):
			foreach($_field as $key => $value):

				//１番左
				if(reset(array_keys($_field)) === $key):
					$sheet->setCellValue($col.$row, '合計金額');
					$sheet->getStyle($col.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
					$sheet->getStyle($col.$row)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
					$sheet->getStyle($col.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFFFFF99');
				endif;

				if($key == 6):
					$sheet->getStyle($col.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$sheet->setCellValue($col.$row, number_format($subtotal));
				elseif($key == 7):
					$sheet->getStyle($col.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$sheet->setCellValue($col.$row, number_format($sales_tax));
				elseif($key == 8):
					$sheet->getStyle($col.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$sheet->setCellValue($col.$row, number_format($total));
				endif;

				//１番右
				if(end(array_keys($_field)) === $key):
					$sheet->getStyle($col.$row)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
				endif;

				//スタイルの取得
				$style = $sheet->getStyle($col++.$row);

				//線の設定
				$style->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

			endforeach;
		endif;

		$savefile = $_file_name.$n_str.'-'.$n_end.'.xls';
		$savepath = TMP;

		$writer = new PHPExcel_Writer_Excel5($excel);
		$writer->save($savepath.'/'.$savefile);

		header("Content-Type: application/vnd.ms-excel");
		header("Content-Disposition: attachement; filename=${savefile}");
		header("Content-Length: ".filesize($savepath.'/'.$savefile));

		readfile($savepath.'/'.$savefile);
	}
}