<?php

/**
 *  This command responsible for call action which save data consistency 
 *
 * @version 0.1
 * @author Safaa
 * @package application.commands
 */
class SchedulerCommand extends CConsoleCommand {

    /**
     *   Get some description for the command
     */
    public function getHelp() {
	return <<<EOD
USAGE
  Scheduler

DESCRIPTION
  This command responsible for call action which save data consistency .

EOD;
    }

    /**
     * The following process is called when the system backup and remove the data that is earlier than 18 months
     */
    public function actionBackupAndRemoveDataEarlierThan18MonthesAgo() {
	Yii::log('Start Backup and Remove Data ', 'info', 'system.Scheduler.BackupAndRemoveDataMoreThan18Monthes');
	try {
	    $settings_array = Setting::getSettingsArray();
	    $backupDataBase_path = isset($settings_array["backup_file_path"]) && $settings_array["backup_file_path"] != null && $settings_array["backup_file_path"] != '' ? $settings_array["backup_file_path"] : Yii::app()->basePath . '\\backup\\';
	    if (!is_dir($backupDataBase_path))
		$backupDataBase_path = Yii::app()->basePath . '\\backup\\';
	    $slash = ( substr($backupDataBase_path, -1) == "\\") ? "" : "\\";
	    if (!is_dir($backupDataBase_path . $slash . "18_month_backup"))
		mkdir($backupDataBase_path . $slash . "18_month_backup");

	    $renewal_file_name = date('Y.m.d_H.i.s') . '__renewal_backup_remove.sql';
	    $DataBase_path = $backupDataBase_path . $slash . "18_month_backup\\";
	    $DataBase_path = str_replace("\\", "/", $DataBase_path);

	    $mysql_file_path = Yii::app()->params['mysqldump_file_path'];
	    $DataBase_host = Yii::app()->db->host;
	    $DataBase_user = Yii::app()->db->username;
	    $DataBase_pass = Yii::app()->db->password;
	    $DataBase_db = Yii::app()->db->dbname;
	    $table_name = "asset_detailed_dashboard tid_detailed_dashboard summary_dashboard_per_segment";
	    $summary_renewal_table = 'summary_dashboard_per_segment_and_renewal';


	    //get the period from console configuration and add one to skip the current year
	    $data_remained = 18;
	    //get date to start archiving
	    $date_to_remove = date("Y-m-d", strtotime(date("Y-m-d", strtotime(date("Y-m-d"))) . " -$data_remained month"));

	    $min_date = null;
	    $max_date = null;
	    $this->getMaxAndMinDateLessThan18Months($date_to_remove, $min_date, $max_date);

	    if ($min_date != null && $max_date != null)
		$file_name = '18_month_backup_' . date('Y.m.d_H.i.s') . '_from_' . $min_date . '_to_' . $max_date . '.sql';
	    else
		$file_name = '18_month_backup_' . date('Y.m.d_H.i.s') . '.sql';
	    //declare where clause for the mysqldump command since we don't want to dump the whole table, we want to keep the 5 years data in the archiving db
	    $where_clause = "date <= '" . $date_to_remove . "'";
	    $summary_renewal_where_clause = "summary_dashboard_per_segment_id in (select id from summary_dashboard_per_segment where date <= '" . $date_to_remove . "')";

	    $command = "$mysql_file_path/mysqldump --lock-all-tables -h " . $DataBase_host . " -u " . $DataBase_user . " --password=" . $DataBase_pass . " --no-create-db --no-create-info " . $DataBase_db . " " . $table_name . ' --where="' . $where_clause . '" > "' . $DataBase_path . "/$file_name" . '"';

	    //$command = $command = "$mysql_file_path\mysql --host=" . $DataBase_host . " --user=" . $DataBase_user . " --password=" . $DataBase_pass . " --database=" . $DataBase_db . ' --execute="' . $query . '"';
	    exec($command, $res, $result);


	    //if error happened send email to the concerned people
	    if (file_exists($DataBase_path . $file_name) && filesize($DataBase_path . $file_name) > 2) {
		$command = "$mysql_file_path/mysqldump --lock-all-tables -h " . $DataBase_host . " -u " . $DataBase_user . " --password=" . $DataBase_pass . " --no-create-db --no-create-info " . $DataBase_db . " " . $summary_renewal_table . ' --where="' . $summary_renewal_where_clause . '" > "' . $DataBase_path . "/$renewal_file_name" . '"';

		//$command = $command = "$mysql_file_path\mysql --host=" . $DataBase_host . " --user=" . $DataBase_user . " --password=" . $DataBase_pass . " --database=" . $DataBase_db . ' --execute="' . $query . '"';
		exec($command, $res, $result);
		if (file_exists($DataBase_path . $renewal_file_name) && filesize($DataBase_path . $renewal_file_name) > 1) {

		    $renewal_file_content = file_get_contents($DataBase_path . $renewal_file_name);
		    file_put_contents($DataBase_path . $file_name, $renewal_file_content, FILE_APPEND | LOCK_EX);
		    unlink($DataBase_path . $renewal_file_name);

		    $DataBase_db = Yii::app()->db->dbname;
		    $first_removal_query = "delete from `asset_detailed_dashboard` where date < '" . $date_to_remove . "';";
		    $second_removal_query = "delete from `tid_detailed_dashboard` where date < '" . $date_to_remove . "';";
		    $third_removal_query = "delete from `summary_dashboard_per_segment` where date < '" . $date_to_remove . "';";
		    $query = "Start transaction; begin; " . $first_removal_query . $second_removal_query . $third_removal_query . " commit;";
		    $command = "$mysql_file_path\mysql --host=" . $DataBase_host . " --user=" . $DataBase_user . " --password=" . $DataBase_pass . " --database=" . $DataBase_db . ' --execute="' . $query . '"';
		    exec($command, $res, $result);
		}
//                if ($result > 0)
	    }


	    Yii::log('End Backup and Remove Data ', 'info', 'system.Scheduler.BackupAndRemoveDataMoreThan18Monthes');
	} catch (Exception $ex) {
	    Yii::log(' RollBack ', 'error', 'system.Scheduler.BackupAndRemoveDataMoreThan18Monthes');
	    Yii::log($ex->__toString(), 'error', 'system.Scheduler.BackupAndRemoveDataMoreThan18Monthes');
	    return;
	}
    }

    /**
     * The following process is called daily to check expiry date of tids and assets, and modify their statuses if necessary
     * and to fix the current dashboard records if there are any error on it
     */

    public function actionUpdateTIDAndAssetStatus() {
	TidansActiveRecord::UpdateTidAndAssetStatues();
	if (!SummaryDashboardPerSegment::UpdateAllDashboardModels($error_msg))
	    Yii::log(PHP_EOL . "Error : " . $error_msg, 'error', 'system.Scheduler.UpdateTidAndAssetStatues');
    }

    /**
     * The following function is called for the first three days of each month to calculate history dashboard for the pervious month<br>
     */
    public function actionArchiveDashboardDataForPreviousMonth() {
	Yii::log('Start Building dashboard', 'info', 'system.Scheduler.BuildDashboardByDate');
	try {

	    if (SummaryDashboardPerSegment::CheckExistingDashboardRecords(date("Y-m-t", strtotime("-1 month"))))
		return;

	    $model = SummaryDashboardPerSegment::model();

	    $segment_names = Segment::getNamesForDropDown();
	    $transaction = Yii::app()->db->beginTransaction();
	    if (!SummaryDashboardPerSegment::BuildDashboardRowModel('Total', null, $error_msg, false)) {
		$transaction->rollback();
		Yii::log(PHP_EOL . "Error : " . $error_msg, 'error', 'system.Scheduler.BuildDashboardByDate');
		return;
	    }
	    if ($segment_names != null) {
		foreach ($segment_names as $id => $segment_name) {
		    if (!SummaryDashboardPerSegment::BuildDashboardRowModel($segment_name, $id, $error_msg, false)) {
			$transaction->rollback();
			Yii::log(PHP_EOL . "Error : " . $error_msg, 'error', 'system.Scheduler.BuildDashboardByDate');
			return;
		    }
		}
	    }
	    if (!SummaryDashboardPerSegment::BuildDashboardRowModel('Multi Segment', null, $error_msg, false)) {
		$transaction->rollback();
		Yii::log(PHP_EOL . "Error : " . $error_msg, 'error', 'system.Scheduler.BuildDashboardByDate');
		return;
	    }
	    if (!SummaryDashboardPerSegment::BuildDashboardRowModel('Non Segment', null, $error_msg, false)) {
		$transaction->rollback();
		Yii::log(PHP_EOL . "Error : " . $error_msg, 'error', 'system.Scheduler.BuildDashboardByDate');
		return;
	    }

	    if (!SummaryDashboardPerSegment::BuildTIDDashboardDetailQuery(date("Y-m-t", strtotime("-1 month")), $error_msg)) {
		$transaction->rollback();
		Yii::log(PHP_EOL . "Error : " . $error_msg, 'error', 'system.Scheduler.BuildDashboardByDate');
		return false;
	    }

	    if (!SummaryDashboardPerSegment::BuildAssetDashboardDetailQuery(date("Y-m-t", strtotime("-1 month")), $error_msg)) {
		$transaction->rollback();
		Yii::log(PHP_EOL . "Error : " . $error_msg, 'error', 'system.Scheduler.BuildDashboardByDate');
		return false;
	    }

	    $transaction->commit();
	    Yii::log('End Building dashboard', 'info', 'system.Scheduler.BuildDashboardByDate');
	} catch (Exception $ex) {
	    Yii::log(' RollBack ', 'error', 'system.Scheduler.BuildDailyDashboard');
	    Yii::log($ex->__toString(), 'error', 'system.Scheduler.BuildDailyDashboard');
	    return;
	}
    }

    /**
     * The following function is called for weekly notification
     * And then notifiy concerned people
     */

    public function actionWeeklyNotification() {
	Yii::log('Start Weekly Notification', 'info', 'system.Scheduler.WeeklyNotification');

	try {
	    $this->CalculateExpiredGraceTidsAssets('TIDANS - Weekly Notification');

	    Yii::log('End Weekly Notification', 'info', 'system.Scheduler.WeeklyNotification');
	} catch (Exception $ex) {
	    Yii::log(' RollBack ', 'error', 'system.Scheduler.WeeklyNotification');
	    Yii::log($ex->__toString(), 'error', 'system.Scheduler.WeeklyNotification');
	    return;
	}
    }

    /**
     * The following function is called for monthly notification
     * And then notifiy concerned people
     */

    public function actionMonthlyNotification() {
	Yii::log('Start Monthly Notification', 'info', 'system.Scheduler.MonthlyNotification');

	try {
	    $this->CalculateExpiredGraceTidsAssets('TIDANS - Monthly Notification');

	    Yii::log('End Monthly Notification', 'info', 'system.Scheduler.MonthlyNotification');
	} catch (Exception $ex) {
	    Yii::log(' RollBack ', 'error', 'system.Scheduler.MonthlyNotification');
	    Yii::log($ex->__toString(), 'error', 'system.Scheduler.MonthlyNotification');
	    return;
	}
    }

    /**
     * The following function is called when the system make full Backup
     */
    public function actionFullBackup() {
	Yii::log('Start Full Backup ', 'info', 'system.Scheduler.FullBackup');
	try {
	    $settings_array = Setting::getSettingsArray();

	    $backupDataBase_path = isset($settings_array["backup_file_path"]) && $settings_array["backup_file_path"] != null && $settings_array["backup_file_path"] != '' ? $settings_array["backup_file_path"] : Yii::app()->basePath . '\\backup\\';
	    if (!is_dir($backupDataBase_path))
		$backupDataBase_path = Yii::app()->basePath . '\\backup\\';
	    $slash = ( substr($backupDataBase_path, -1) == "\\") ? "" : "\\";
	    $mysqldump_file_path = Yii::app()->params['mysqldump_file_path'];
	    $backupDataBase_host = Yii::app()->db->host;
	    $backupDataBase_user = Yii::app()->db->username;
	    $backupDataBase_pass = Yii::app()->db->password;
	    $backupDataBase_db = Yii::app()->db->dbname;
	    $file_name = date('Y.m.d_H.i.s') . '__FullBackup.sql';
	    $command = "$mysqldump_file_path/mysqldump -h " . $backupDataBase_host . " -u " . $backupDataBase_user . " --password=" . $backupDataBase_pass . " --no-create-db --no-create-info " . $backupDataBase_db . ' > "' . $backupDataBase_path . "/$file_name" . '"';
	    exec($command);

	    //create zip file as backup file for the whole files in system photos , documents and mysql file
	    $mysql_path = $backupDataBase_path . $slash . $file_name;
	    $documents_path = Setting::getDocumentsPath();
	    $files_to_zip = array();
	    array_push($files_to_zip, $mysql_path);
	    array_push($files_to_zip, $documents_path);

	    $zip_name = $backupDataBase_path . $slash . date('Y.m.d_H.i.s') . '__FullBackup.zip';
	    $result = Document::create_zip($files_to_zip, $zip_name);
	    if ($result) {
		unlink($mysql_path);
		Yii::log('End Full Backup ', 'info', 'system.Scheduler.FullBackup');
	    }
	} catch (Exception $ex) {
	    Yii::log(' RollBack ', 'error', 'system.Scheduler.FullBackup');
	    Yii::log($ex->__toString(), 'error', 'system.Scheduler.FullBackup');
	    return;
	}
    }

    /**
     * The following function is called when the system make daily Backup
     */
    public function actionDailyBackup() {
	Yii::log('Start Daily Backup ', 'info', 'system.Scheduler.DailyBackup');
	try {
	    $backupDataBase = new BackupDataBase;
	    $settings_array = Setting::getSettingsArray();
	    $backupDataBase->path = isset($settings_array["backup_file_path"]) && $settings_array["backup_file_path"] != null && $settings_array["backup_file_path"] != '' ? $settings_array["backup_file_path"] : Yii::app()->basePath . '\\backup\\';
	    if (!is_dir($backupDataBase->path))
		$backupDataBase->path = Yii::app()->basePath . '\\backup\\';

	    $slash = ( substr($backupDataBase->path, -1) == "\\") ? "" : "\\";
	    //we want backup from yesterday
	    $from_date = date('Y-m-d H:i:s', strtotime('yesterday 00:00:00'));
	    $to_date = date('Y-m-d H:i:s', strtotime("yesterday 23:59:59"));
	    $backupDataBase->partialBackup(false, true, $from_date, $to_date, '_DailyBackup');
	    //create zip file as backup file for the whole files in system photos , documents and mysql file
	    $mysql_path = $backupDataBase->file_name;

	    $files_to_zip = array();
	    array_push($files_to_zip, $mysql_path);
	    $zip_name = $backupDataBase->path . $slash . date('Y.m.d_H.i.s') . '__DailyBackup.zip';
	    $result = Document::create_zip($files_to_zip, $zip_name);
	    if ($result) {
		unlink($mysql_path);
		Yii::log('End Daily Backupt ', 'info', 'system.Scheduler.DailyBackup');
	    }
	} catch (Exception $ex) {
	    Yii::log(' RollBack ', 'error', 'system.Scheduler.DailyBackup');
	    Yii::log($ex->__toString(), 'error', 'system.Scheduler.DailyBackup');
	    return;
	}
    }

    /**
     * calculate expired and grace tids and assets
     * @param String $subject the subject of the notification
     */
    function CalculateExpiredGraceTidsAssets($subject) {
	$expired_tids = VTid::getTIDCount('Expired');
	$grace_tids = VTid::getTIDCount('Grace');
	$now_date = date("Y-m-d");
	$condition = "status = 'Expired' and asset_status != 'Exported'";
	$expired_assets = VAsset::getAssetCount($condition);
	$condition = "status = 'Grace'"; //'(DATEDIFF(vehicle_book_expiry_date,\'' . $now_date . '\') > 0) and (DATEDIFF( DATE_SUB(`vehicle_book_expiry_date`, INTERVAL ' . Setting::getGracePeriod() . ' DAY),\'' . $now_date . '\')) < 0';
	$grace_assets = VAsset::getAssetCount($condition);

	$message_body = '<br/><br/>Total expired TIDs = ' . $expired_tids . '<br/>' .
		'Total grace period TIDs = ' . $grace_tids . '<br/>' .
		'Total grace vehicles book = ' . $grace_assets . '<br/>' .
		'Total expired vehicles book = ' . $expired_assets . '<br/><br/>' .
		'You can find the details in the attached excel sheet.';

	//Tid Attahment
	$model = VTid::model();
	$header = array(
	    'number' => Yii::t('default', 'Number'),
	    'tid_no' => Yii::t('default', 'TID No'),
	    'value' => Yii::t('default', 'Value'),
	    'point_of_entry_name' => Yii::t('default', 'Point of Entry'),
	    'last_status_ti_status' => Yii::t('default', 'Status'),
	    'ti_type_name' => Yii::t('default', 'TI Type'),
	    'last_status_ti_expiry_date' => Yii::t('default', 'Expiry Date'),
	    'contract_reference' => Yii::t('default', 'Contract Ref'),
	    'multi_single' => Yii::t('default', 'Single Segment/Multi-Segment'),
	    'segment_name' => Yii::t('default', 'Segments'),
	    'expiry_days' => Yii::t('default', 'Days Count'),
	    'fine' => Yii::t('default', 'Fine'),
	    'grace_period' => Yii::t('default', 'Days to Expire'),
	);
	$columns = "number,tid_no,value,point_of_entry_name,last_status_ti_status,ti_type_name,last_status_ti_expiry_date,contract_reference,
	    If( isnull( segment_name ) , '', If( LOCATE( ',', segment_name ) , 'Multi', 'Single' ) ) as multi_single ,segment_name,expiry_days,fine,grace_period";
	$condition = "last_status_ti_status = 'Expired' Or last_status_ti_status = 'Grace'";
	$this->SaveAttachment($model, $header, $columns, $condition, 'TIDs.xls');

	//Asset Attahment
	$model = VAsset::model();
	$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'),
	    'asset_status' => Yii::t('default', 'Asset Status'),
	    'chassis_number' => Yii::t('default', 'Chassis Number'),
	    'plate_number' => Yii::t('default', 'Plate Number'),
	    'vehicle_book_expiry_date' => Yii::t('default', 'Expiry Date'),
	    'vehicle_book_status' => Yii::t('default', 'Validity Status'),
	    'expiry_days' => Yii::t('default', 'Days Count'),
	    'grace_period' => Yii::t('default', 'Days to Expire'),
	);

	$columns = "tid_no,invoice_number,po_number,segment_name,serial_number,asset_number,part_number,
	    asset_status,chassis_number,plate_number,vehicle_book_expiry_date,vehicle_book_status,expiry_days,grace_period
	    ";
	$vehicle_type_of_asset = Setting::getVehicleTypeOfAsset();
	$condition = '';
	if ($vehicle_type_of_asset != null && $vehicle_type_of_asset != '')
	    $condition .= "type_of_asset_id =" . $vehicle_type_of_asset . ' and ';

	$grace_period = Setting::getGracePeriod();
	if ($grace_period != null && $grace_period != '')
	    $condition .= "((status = 'Expired' and asset_status != 'Exported') OR status = 'Grace')"; //grace_period <=" . $grace_period . ')';
 else
	    $condition .= "status = 'Expired'  and asset_status != 'Exported'";
	$this->SaveAttachment($model, $header, $columns, $condition, 'Vehicles.xls');
	TidansActiveRecord::SendNotification($subject, $message_body, null, array(
		    Yii::app()->basePath . '\\temp\\' . 'TIDs.xls',
		    Yii::app()->basePath . '\\temp\\' . 'Vehicles.xls')
	);
	if (file_exists(Yii::app()->basePath . '\\temp\\' . 'TIDs.xls'))
	    unlink(Yii::app()->basePath . '\\temp\\' . 'TIDs.xls');
	if (file_exists(Yii::app()->basePath . '\\temp\\' . 'Vehicles.xls'))
	    unlink(Yii::app()->basePath . '\\temp\\' . 'Vehicles.xls');
    }

    /**
     * get the data for the tid or asset attachmnet and then save them to a file
     * @param tidansActiveRecord $model the model the we need to export its info to excel file
     * @param string $header the header columns
     * @param string $columns the selected columns in the query
     * @param string $condition the condition if exist
     * @param string $attachment_name the name of the excel file
     * 
     */
    function SaveAttachment($model, $header, $columns, $condition, $attachment_name) {
	$criteria = new CDbCriteria;
	$criteria->select = $columns;
	if ($condition != '')
	    $criteria->condition = $condition;
	$data = $model->commandBuilder->createFindCommand(
			$model->tableName(), $criteria)->queryAll();
	array_unshift($data, $header);
	Yii::import('application.extensions.jphpexcel.JPhpExcel');
	$xls = new JPhpExcel('UTF-8', false, $attachment_name);
	$xls->addArray($data);

	$content = stripslashes(sprintf($xls->header, $xls->sEncoding));
	$content.= "\n<Worksheet ss:Name=\"" . $xls->sWorksheetTitle . "\">\n<Table>\n";
	foreach ($xls->lines as $line)
	    $content.= $line;

	$content.= "</Table>\n</Worksheet>\n";
	$content.= $xls->footer;
	file_put_contents(Yii::app()->basePath . '\\temp\\' . $attachment_name, $content);
    }

    public function is_sub_dir($path = NULL, $parent_folder = SITE_PATH) {

	//Get directory path minus last folder
	$dir = dirname($path);
	$folder = substr($path, strlen($dir));

	//Check the the base dir is valid
	$dir = realpath($dir);

	//Only allow valid filename characters
	$folder = preg_replace('/[^a-z0-9\.\-_]/i', '', $folder);

	//If this is a bad path or a bad end folder name
	if (!$dir OR !$folder OR $folder === '.') {
	    return false;
	}

	//Rebuild path
	$path = $dir . '/' . $folder;

	//If this path is higher than the parent folder
	if (strcasecmp($path, $parent_folder) > 0) {
	    return true;
	}

	return false;
    }

    public function getMaxAndMinDateLessThan18Months($date, &$min_date, &$max_date) {
	$cr = new CDbCriteria();
	$cr->select = "MIN( DATE ) as MinDate , MAX( DATE ) As MaxDate";
	$cr->condition = "DATE <=  '" . $date . "'";

	$model = SummaryDashboardPerSegment::model();
	$result = $model->commandBuilder->createFindCommand(
			$model->tableName(), $cr)->queryAll();

	if ($result != null) {
	    $min_date = $result[0]['MinDate'];
	    $max_date = $result[0]['MaxDate'];
	}
    }

}
