<?php
/**
 * IMPORTANT NOTE
 * The code of this system is closed and a private proprietary of Flex Solutions.
 * A written authorization/license from Flex Solutions is required before using or modifying this code.
 * Email: sales@flexsolution.biz, Phone: +962795342585, Website: http://www.flexsolutions.biz
 */
 
/**
 * Dashboard
 * 
 * Yii controller to control Dashboard actions
 * 
 * @version 1.0
 * @author Amaney
 * @package application.controllers
 */
class DashboardReportController extends Controller {

    /**
     * @var string the default layout for the views. Defaults to '//layouts/column2', meaning
     * using two-column layout. See 'protected/views/layouts/column2.php'.
     */
    public $layout = '//layouts/column2';

    /**
     * @return array action filters
     */
    public function filters() {
	return array(
	    'accessControl', // perform access control for CRUD operations
	);
    }

    /**
     * Specifies the access control rules.
     * This method is used by the 'accessControl' filter.
     * @return array access control rules
     */
    public function accessRules() {
	return array(
	    array('allow', // allow all users to perform 'index' and 'view' actions
		'actions' => array('view', 'viewTIDDetail', 'viewAssetDetail', 'TIDExportToExcel', 'AssetExportToExcel', 'ExportToExcel'),
		'users' => array('*'),
	    //'roles' => array(User::SYSTEM_ADMIN_ROLE, User::LOGISTICS_ADMIN_ROLE),
	    ),
	    array('deny', // deny all users
		'users' => array('*'),
	    ),
	);
    }

    public function actionView() {

	$model = new SummaryDashboardPerSegment;
	$is_current = true;
	$date = 'Today';

	$section_visibility_array = 'tids,vehicles,radioactives,renewalprogress';
	$selected_segments = SummaryDashboardPerSegment::getSegmentList($is_current, $date, false);
	//ajax request that update the dashboard.
	if (isset($_POST['ajax']) && isset($_POST['is_current']) && isset($_POST['date']) && isset($_POST['section_visibility_array']) && isset($_POST['selected_segments'])) {


	    $section_visibility_array = $_POST['section_visibility_array'];
	    //json array is different from php array so we need to make it like php array
	    $selected_segments = array();
	    $segment_visibility_array = json_decode($_POST['selected_segments']);
	    foreach ($segment_visibility_array as $key => $element)
		$selected_segments[$key] = $element;

	    echo $this->renderPartial('_view_dashboard_table', array('model' => $model, 'is_current' => $_POST['is_current'], 'date' => $_POST['date'], 'section_visibility_array' => $section_visibility_array, 'selected_segments' => $selected_segments), true);
	    return;
	}
	//submit request to choose date, segments and sections
	if (isset($_POST['SummaryDashboardPerSegment'])) {
	    $model->attributes = $_POST['SummaryDashboardPerSegment'];
	    if ($model->section_drop_down != null) {
		if (in_array('All', $model->section_drop_down))
		    $section_visibility_array = 'tids,vehicles,radioactives,renewalprogress';
		else {
		    $section_visibility_array = '';
		    foreach ($model->section_drop_down as $section)
			$section_visibility_array .=$section . ',';
		}
	    }

	    if ($model->segment_name != null) {
		if (in_array('all', $model->segment_name))
		    $selected_segments = SummaryDashboardPerSegment::getSegmentList($is_current, $date, false);
		else {
		    $segment_visibility_array = NULL;
		    foreach ($model->segment_name as $segment) {
			$key = $this->getKey($selected_segments, $segment);
			if ($key !== false)
			    $segment_visibility_array[$this->getKey($selected_segments, $segment)] = $selected_segments[$this->getKey($selected_segments, $segment)];
		    }
		    $selected_segments = $segment_visibility_array;
		}
	    }
	    $date = $model->date;
	    $is_current = (($model->date == null || $model->date == '') || ($model->date == 'Today')) ? true : false;
	    Yii::app()->session[Yii::app()->user->id . '_dashboard_section_visibility'] = $section_visibility_array;
	    Yii::app()->session[Yii::app()->user->id . '_dashboard_selected_segments'] = json_encode($selected_segments);
	    Yii::app()->session[Yii::app()->user->id . '_dashboard_date'] = $date;
	    Yii::app()->session[Yii::app()->user->id . '_dashboard_is_current'] = $is_current;
	} else {
	    if (isset(Yii::app()->session[Yii::app()->user->id . '_dashboard_section_visibility'])) {
		$section_visibility_array = Yii::app()->session[Yii::app()->user->id . '_dashboard_section_visibility'];
	    }
	    if (isset(Yii::app()->session[Yii::app()->user->id . '_dashboard_selected_segments'])) {
		$selected_segments = array();
		$segment_visibility_array = json_decode(Yii::app()->session[Yii::app()->user->id . '_dashboard_selected_segments']);
		foreach ($segment_visibility_array as $key => $element)
		    $selected_segments[$key] = $element;
	    }
	    if (isset(Yii::app()->session[Yii::app()->user->id . '_dashboard_date'])) {
		$date = Yii::app()->session[Yii::app()->user->id . '_dashboard_date'];
	    }
	    if (isset(Yii::app()->session[Yii::app()->user->id . '_dashboard_is_current'])) {
		$is_current = Yii::app()->session[Yii::app()->user->id . '_dashboard_is_current'];
	    }
	}
	$this->render('view', array(
	    'model' => $model,
	    'is_current' => $is_current,
	    'date' => $date,
	    'section_visibility_array' => $section_visibility_array,
	    'selected_segments' => $selected_segments
	));
    }

    function getKey($array, $value) {
	if ($value == 'all')
	    return 'all';
	foreach ($array as $key => $element) {
	    if ($element == $value)
		return $key;
	}
	return false;
    }

    public function actionViewTIDDetail($segment = null, $status = null, $date =null, $renewal = null, $is_current = null) {
	if ($is_current) {
	    $model = new VTid('search');
	    if (isset($_GET['VTid']))
		$model->attributes = $_GET['VTid'];
	} else {
	    $model = new VTidDetailedDashboard('search');
	    if (isset($_GET['VTidDetailedDashboard']))
		$model->attributes = $_GET['VTidDetailedDashboard'];
	}
	$this->render('tid_detail', array(
	    'model' => $model,
	    'segment' => $segment,
	    'status' => $status,
	    'date' => $date,
	    'renewal' => $renewal,
	));
    }

    public function actionViewAssetDetail($type_of_asset_id = null, $segment = null, $status = null, $date =null, $is_current = null) {
	if ($is_current) {
	    $model = new VAsset('search');
	    if (isset($_GET['VAsset']))
		$model->attributes = $_GET['VAsset'];
	} else {
	    $model = new VAssetDetailedDashboard('search');
	    if (isset($_GET['VAssetDetailedDashboard']))
		$model->attributes = $_GET['VAssetDetailedDashboard'];
	}
	$this->render('asset_detail', array(
	    'model' => $model,
	    'segment' => $segment,
	    'status' => $status,
	    'date' => $date,
	    'type_of_asset' => $type_of_asset_id,
	));
    }

    /*
     * Export Asset Detail information to excel
     */

    public function actionAssetExportToExcel($type_of_asset_id = null, $segment = null, $status = null, $date =null, $is_current = null) {
	if ($is_current) {
	    $model = new VAsset('search');
	    if (isset($_GET['VAsset']))
		$model->attributes = $_GET['VAsset'];
	} else {
	    $model = new VAssetDetailedDashboard('search');
	    if (isset($_GET['VAssetDetailedDashboard']))
		$model->attributes = $_GET['VAssetDetailedDashboard'];
	}
//Export Data to Excel File
	$header = array(
	    'tid_no' => Yii::t('default', 'TID No'),
	    'invoice_number' => Yii::t('default', 'Invoice Number'),
	    'po_number' => Yii::t('default', 'PO Number'),
	    'segment_name' => Yii::t('default', 'Segment'),
	    'serial_number' => Yii::t('default', 'Serial Number'),
	    'asset_number' => Yii::t('default', 'Asset Number'),
	    'part_number' => Yii::t('default', 'Part Number'),
	    'type_of_asset_name' => Yii::t('default', 'Type of Asset'),
	    'description' => Yii::t('default', 'Description'),
	    'asset_status' => Yii::t('default', 'Asset Status'),
	);


	$column = "tid_no,invoice_number,po_number,segment_name,serial_number,asset_number,part_number,
	    type_of_asset_name,description,asset_status";


	if ($type_of_asset_id == Setting::getVehicleTypeOfAsset()) {
	    $header = array_merge($header, array(
			'plate_number' => Yii::t('default', 'Plate Number'),
			'chassis_number' => Yii::t('default', 'Chassis Number'),
			'expiry_date' => Yii::t('default', 'Vehicle Book Expiry Date'),
			'status' => Yii::t('default', 'Vehicle Book Status'),
		    ));
	    $column .= ",plate_number,chassis_number,expiry_date,status";
	} else
	if ($type_of_asset_id == Setting::getRadioActiveTypeOfAsset()) {
	    $header = array_merge($header, array(
			'radioactive_license_number' => Yii::t('default', 'Radioactive License Number'),
			'expiry_date' => Yii::t('default', 'Radioactive License Expiry Date'),
			'status' => Yii::t('default', 'Radioactive License Status'),
		    ));
	    $column .= ",radioactive_license_number,expiry_date,status";
	} else {
	    $header = array_merge($header, array(
			'expiry_date' => Yii::t('default', 'Expiry Date'),
			'status' => Yii::t('default', 'Status'),
		    ));
	    $column .= ",expiry_date,status";
	}


	if ($status == 'Expired' || $status == 'Fine' || $status == 'Days Count') {
	    $header = array_merge($header, array(
			'expiry_days' => Yii::t('default', 'Days Count'),			
		    ));
	    $column .= ",expiry_days";
	}

	if ($status == 'Grace') {
	    $header = array_merge($header, array(
			'grace_period' => Yii::t('default', 'Days to Expire'),
		    ));
	    $column .= ",grace_period";
	}





	$excel_file_name = Yii::t('default', 'Assets');
	$model->exportToExcel($excel_file_name, $model, $header, $column, $model->ModelCriteria(null, $type_of_asset_id, $segment, $status, $date));
    }

    /*
     * Export TID Detail information to excel
     */

    public function actionTIDExportToExcel($segment = null, $status = null, $date =null, $renewal = null, $is_current = null) {
	if ($is_current) {
	    $model = new VTid('search');
	    if (isset($_GET['VTid']))
		$model->attributes = $_GET['VTid'];
	} else {
	    $model = new VTidDetailedDashboard('search');
	    if (isset($_GET['VTidDetailedDashboard']))
		$model->attributes = $_GET['VTidDetailedDashboard'];
	}
//Export Data to Excel File
	$header = array(
	    'number' => Yii::t('default', 'Number'),
	    'tid_no' => Yii::t('default', 'TID No'),
	    'initial_issue_date' => Yii::t('default', 'Initial Issue Date'),
	    'customs_letter_no' => Yii::t('default', 'Customs Letter No'),
	    'customs_letter_date' => Yii::t('default', 'Customs Letter Date'),
	    'shipment_ref' => Yii::t('default', 'Shipment Ref'),
	    'point_of_entry_name' => Yii::t('default', 'Point of Entry'),
	    'original_documnets_delivery_date' => Yii::t('default', 'Original Documnets Delivery Date'),
	    'value' => Yii::t('default', 'Value'),
	    'ti_type_name' => Yii::t('default', 'TI Type'),
	    'last_status_ti_issue_date' => Yii::t('default', 'Issue Date'),
	    'last_status_ti_expiry_date' => Yii::t('default', 'Expiry Date'),
	    'cca_full_name' => Yii::t('default', 'CCA'),
	    'contract_reference' => Yii::t('default', 'Contract Ref'),
	    'last_status_ti_status' => Yii::t('default', 'Status'),
	    'last_status_comments' => Yii::t('default', 'Comments'),
	    'last_status_under_processing' => Yii::t('default', 'Under Processing'),
	    'renewal_stage_name' => Yii::t('default', 'Processing Stage'),
	    'multi_single' => Yii::t('default', 'Single Segment/Multi-Segment'),
	    'segment_name' => Yii::t('default', 'Segments'),
	);


	$column = "number,tid_no,initial_issue_date,customs_letter_no,
	    if(customs_letter_date = '0000-00-00','',customs_letter_date),
	    shipment_ref,point_of_entry_name,
	    if(original_documnets_delivery_date = '0000-00-00','',original_documnets_delivery_date),
	    if(isnull(value) || value = '','',CONCAT('$',value)),

	    ti_type_name,last_status_ti_issue_date,last_status_ti_expiry_date,cca_full_name,contract_reference,last_status_ti_status,last_status_comments,last_status_under_processing,renewal_stage_name,
             If( isnull( segment_name ) , '', If( LOCATE( ',', segment_name ) , 'Multi', 'Single' ) ) as multi_single,segment_name";

	if ($status == 'Expired' || $status == 'Fine' || $status == 'Days Count') {
	    $header = array_merge($header, array(
			'expiry_days' => Yii::t('default', 'Days Count'),
			'fine' => Yii::t('default', 'Fine'),
		    ));
	    $column .= ",expiry_days,fine";
	}

	if ($status == 'Grace') {
	    $header = array_merge($header, array(
			'grace_period' => Yii::t('default', 'Days to Expire'),
		    ));
	    $column .= ",grace_period";
	}

	$excel_file_name = Yii::t('default', 'Dashboard TIDs Detail');
	$model->exportToExcel($excel_file_name, $model, $header, $column, $model->ModelCriteria(null, $segment, $status, $date, $renewal));
    }

    /*
     * Export TID Detail information to excel
     */

    public function actionExportToExcel($is_current = null, $date = null, $section_visibility_array = null, $selected_segments = null) {

	$renewals = array();
	$dashboard_summary_records = SummaryDashboardPerSegment::getDataPerDate($is_current, $tids, $vehicles, $radioactives, $assets, $segments, $date);
	if ($dashboard_summary_records != null)
	    $renewals = SummaryDashboardPerSegment::getRenewalInfos($is_current, $dashboard_summary_records['segment_name'], $dashboard_summary_records['id'], $renewal_colors, $renewal_rowHeaders, $renewal_paramUrl);

	$colors = SummaryDashboardPerSegment::dashboardRowsColorsForExcel();

	$rowHeaders = SummaryDashboardPerSegment::dashboardRowHeader();

	$selected_segs = array();
//json array is different from php array so we need to make it like php array
	$segment_visibility_array = json_decode($selected_segments);
	$selected_segments = array();
	foreach ($segment_visibility_array as $key => $element)
	    $selected_segments[$key] = $element;

//map segment name with id as keys so if any change to segment name that will not affect the selected segment
	foreach ($segments as $colidx => $column)
	    if (array_key_exists($colidx, $selected_segments))
		$selected_segs[$column] = $column;

	$objPHPExcel = new PHPExcel();
	$objPHPExcel->setActiveSheetIndex(0);
	$objWorksheet = $objPHPExcel->getActiveSheet();
	PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
	$row = 4;
	$col = 3;

	$multi_seg_value = '';
	$none_seg_value = '';

	foreach ($segments as $key => $value) {
	    if ($key == 'Multi Segment')
		$multi_seg_value = $value;
	    elseif ($key == 'Non Segment')
		$none_seg_value = $value;
	    elseif (array_key_exists($key, $selected_segments)) {
		$this->setExcelValue($objWorksheet, $col, $row, $value, 'C3D9FF');
		$col++;
	    }
	}

//multi segment and none segment values must be at last
	$this->setExcelValue($objWorksheet, $col, $row, $multi_seg_value, 'C3D9FF');
	$col++;
	$this->setExcelValue($objWorksheet, $col, $row, $none_seg_value, 'C3D9FF');
	$col++;


	if (strpos($section_visibility_array, 'tids') !== false) {
	    $this->setExcelValue($objWorksheet, 2, 5, 'TIDs', 'C3D9FF');

	    $row = 6;
	    foreach ($tids as $key => $values) {
		$col = 2;
		$this->setExcelValue($objWorksheet, $col, $row, $rowHeaders[$key], $colors[$key]);

		$col++;
		$multi_seg_value = '';
		$none_seg_value = '';

		foreach ($values as $seg_key => $value) {
		    if ($seg_key == 'Multi Segment')
			$multi_seg_value = $value;
		    elseif ($seg_key == 'Non Segment')
			$none_seg_value = $value;
		    elseif (array_key_exists($seg_key, $selected_segs)) {
			$this->setExcelValue($objWorksheet, $col, $row, $value, $colors[$key]);
			$col++;
		    }
		}

//multi segment and none segment values must be at last
		$this->setExcelValue($objWorksheet, $col, $row, $multi_seg_value, $colors[$key]);
		$col++;
		$this->setExcelValue($objWorksheet, $col, $row, $none_seg_value, $colors[$key]);
		$col++;

		$row++;
	    }
	}

	if (strpos($section_visibility_array, 'vehicles') !== false) {
	    $this->setExcelValue($objWorksheet, 2, $row, 'Vehicles', 'C3D9FF');
	    $row++;


	    foreach ($vehicles as $key => $values) {
		$col = 2;
		$this->setExcelValue($objWorksheet, $col, $row, $rowHeaders[$key], $colors[$key]);

		$col++;
		$multi_seg_value = '';
		$none_seg_value = '';

		foreach ($values as $seg_key => $value) {
		    if ($seg_key == 'Multi Segment')
			$multi_seg_value = $value;
		    elseif ($seg_key == 'Non Segment')
			$none_seg_value = $value;
		    elseif (array_key_exists($seg_key, $selected_segs)) {
			$this->setExcelValue($objWorksheet, $col, $row, $value, $colors[$key]);
			$col++;
		    }
		}

//multi segment and none segment values must be at last
		$this->setExcelValue($objWorksheet, $col, $row, $multi_seg_value, $colors[$key]);
		$col++;
		$this->setExcelValue($objWorksheet, $col, $row, $none_seg_value, $colors[$key]);
		$col++;

		$row++;
	    }
	}


	if (strpos($section_visibility_array, 'radioactives') !== false) {
	    $this->setExcelValue($objWorksheet, 2, $row, 'Radioactive', 'C3D9FF');
	    $row++;

	    foreach ($radioactives as $key => $values) {
		$col = 2;
		$this->setExcelValue($objWorksheet, $col, $row, $rowHeaders[$key], $colors[$key]);

		$col++;
		$multi_seg_value = '';
		$none_seg_value = '';

		foreach ($values as $seg_key => $value) {
		    if ($seg_key == 'Multi Segment')
			$multi_seg_value = $value;
		    elseif ($seg_key == 'Non Segment')
			$none_seg_value = $value;
		    elseif (array_key_exists($seg_key, $selected_segs)) {
			$this->setExcelValue($objWorksheet, $col, $row, $value, $colors[$key]);
			$col++;
		    }
		}

//multi segment and none segment values must be at last
		$this->setExcelValue($objWorksheet, $col, $row, $multi_seg_value, $colors[$key]);
		$col++;
		$this->setExcelValue($objWorksheet, $col, $row, $none_seg_value, $colors[$key]);
		$col++;
		$row++;
	    }
	}



	if (strpos($section_visibility_array, 'vehicles') !== false || strpos($section_visibility_array, 'radioactives') !== false) {
	    foreach ($assets as $key => $values) {
		$col = 2;
		$this->setExcelValue($objWorksheet, $col, $row, $rowHeaders[$key], $colors[$key]);

		$col++;
		$multi_seg_value = '';
		$none_seg_value = '';

		foreach ($values as $seg_key => $value) {
		    if ($seg_key == 'Multi Segment')
			$multi_seg_value = $value;
		    elseif ($seg_key == 'Non Segment')
			$none_seg_value = $value;
		    elseif (array_key_exists($seg_key, $selected_segs)) {
			$this->setExcelValue($objWorksheet, $col, $row, $value, $colors[$key]);
			$col++;
		    }
		}

//multi segment and none segment values must be at last
		$this->setExcelValue($objWorksheet, $col, $row, $multi_seg_value, $colors[$key]);
		$col++;
		$this->setExcelValue($objWorksheet, $col, $row, $none_seg_value, $colors[$key]);
		$col++;
		$row++;
	    }
	}


	if (strpos($section_visibility_array, 'renewalprogress') !== false) {
	    $this->setExcelValue($objWorksheet, 2, $row, 'TIDs Under Processing', 'C3D9FF');
	    $row++;


	    foreach ($renewals as $key => $values) {

		if ($key == 'total_renewal') {
		    $col = 2;
		    $this->setExcelValue($objWorksheet, $col, $row, $renewal_rowHeaders[$key], '9F9F9F');

		    $col++;
		    $multi_seg_value = '';
		    $none_seg_value = '';

		    foreach ($values as $seg_key => $value) {
			if ($seg_key == 'Multi Segment')
			    $multi_seg_value = $value;
			elseif ($seg_key == 'Non Segment')
			    $none_seg_value = $value;
			elseif (array_key_exists($seg_key, $selected_segs)) {
			    $this->setExcelValue($objWorksheet, $col, $row, $value, '9F9F9F');
			    $col++;
			}
		    }
		} else {
		    $col = 2;
		    $this->setExcelValue($objWorksheet, $col, $row, $renewal_rowHeaders[$key], 'FAE49E');
		    $col++;
		    foreach ($values as $seg_key => $value) {
			if ($seg_key == 'Multi Segment')
			    $multi_seg_value = $value;
			elseif ($seg_key == 'Non Segment')
			    $none_seg_value = $value;
			elseif (array_key_exists($seg_key, $selected_segs)) {
			    $this->setExcelValue($objWorksheet, $col, $row, $value, 'FAE49E');
			    $col++;
			}
		    }
		}
		if ($key == 'total_renewal') {
//multi segment and none segment values must be at last
		    $this->setExcelValue($objWorksheet, $col, $row, $multi_seg_value, '9F9F9F');
		    $col++;
		    $this->setExcelValue($objWorksheet, $col, $row, $none_seg_value, '9F9F9F');
		    $col++;
		} else {
		    $this->setExcelValue($objWorksheet, $col, $row, $multi_seg_value, 'FAE49E');
		    $col++;
		    $this->setExcelValue($objWorksheet, $col, $row, $none_seg_value, 'FAE49E');
		    $col++;
		}
		$row++;
	    }
	}
	$file_name = '';
	if ($date != null && $date != 'Today')
	    $file_name = 'Dashboard-' . date("Y.m.d", strtotime($date));
	else
	    $file_name = 'Dashboard' . "-" . date('Y.m.d');

	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="' . $file_name . '.xlsx"');
	header('Cache-Control: max-age=0');
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');


	$objWriter->save('php://output');
	unset($objWriter);
	unset($objWorksheet);
// unset($this->objReader);
	unset($objPHPExcel);
	exit();
    }

    public

    function setExcelValue($objWorksheet, $col, $row, $value, $color) {
	$style_num = array(
	    'alignment' => array(
		'wrap' => false,
		'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
		'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
	    ),
	    'borders' => array(
		'allborders' => array(
		    'style' => PHPExcel_Style_Border::BORDER_THIN,
		    'color' => array('argb' => 'AFABAB'),
		)
	    )
	);
	$objWorksheet->setCellValueByColumnAndRow($col, $row, $value, true);
	$objWorksheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($col) . $row)->applyFromArray($style_num);
	$objWorksheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($col) . $row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objWorksheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($col) . $row)->getFill()->getStartColor()->setRGB($color);
	$objWorksheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($col))->setAutoSize(true);
    }

}