<?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
 */
 
/**
 * This is the model class for table "tid".
 *
 * The followings are the available columns in table 'tid':
 * @property integer $id
 * @property integer $point_of_entry_id
 * @property string $number
 * @property string $tid_no
 * @property string $initial_issue_date
 * @property string $customs_letter_no
 * @property string $customs_letter_date
 * @property string $shipment_ref
 * @property string $original_documnets_delivery_date
 * @property string $value
 * @property string $exported_date
 * @property integer $created_by
 * @property string $created_date
 * @property integer $last_edited_by
 * @property string $last_edited_date
 *
 * The followings are the available model relations:
 * @property DashboardTids[] $dashboardTids
 * @property Document[] $documents
 * @property DocumentTypeTid[] $documentTypeTs
 * @property Invoice[] $invoices
 * @property PointOfEntry $pointOfEntry
 * @property TidStatus[] $tidStatuses
 *
 * @author Amaney
 * @package application.models
 */
class VTid extends TidansActiveRecord {

    /**
     * @return Integer the id of ti type of the status of the tid
     */
    public $ti_type_id;
    /**
     * @return Date the ti issue date of the status of the tid
     */
    public $ti_issue_date;
    /**
     * @return Date the ti expiry date of the status of the tid
     */
    public $ti_expiry_date;
    /**
     * @return Integer the id of contract of status of the tid
     */
    public $contract_id;
    /**
     * @return Integer the id of cca of the status of the tid
     */
    public $cca_id;
    /**
     * @return String the comments of the status of the tid
     */
    public $comments;
    /**
     * @return boolean the under processing of the status of the tid
     */
    public $under_processing;
    /**
     * @return Integer the id of renewal stage of the status of the tid
     */
    public $renewal_stage_id;
    /**
     * @return String the status of the tid
     */
    public $ti_status;
    /**
     * @return String the transferred contract id
     */
    public $transferred_to_contract_id;
    /**
     * Asset Information
     */
    public $segment_name, $serial_number, $asset_number, $part_number,
    $type_of_asset_name, $asset_status, $plate_number, $chassis_number,
    $radioactive_license_number, $invoice_number;

    /**
     * @return string the associated database table name
     */
    public function tableName() {
	return 'v_tid';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules() {
	// NOTE: you should only define rules for those attributes that
	// will receive user inputs.
	return array(
	    array('point_of_entry_id, number, tid_no, initial_issue_date, shipment_ref', 'required'),
	    array('point_of_entry_id', 'numerical', 'integerOnly' => true),
	    array('number, value', 'length', 'max' => 45),
	    array('tid_no, shipment_ref', 'length', 'max' => 20),
	    array('customs_letter_no', 'length', 'max' => 15),
	    array('ti_status,ti_type_id,ti_issue_date,ti_expiry_date,contract_id,cca_id,comments,under_processing,renewal_stage_id,customs_letter_date, original_documnets_delivery_date, exported_date', 'safe'),
	    // The following rule is used by search().
	    // @todo Please remove those attributes that should not be searched.
	    array('id,transferred_to_contract_id,point_of_entry_name,renewal_stage_name,grace_period,expiry_days,fine,
		last_status_ti_status,last_status_ti_type_id,last_status_ti_issue_date,last_status_ti_expiry_date,last_status_contract_id,last_status_cca_id,last_status_comments,last_status_under_processing,last_status_renewal_stage_id,
		ti_status,ti_type_id,ti_issue_date,ti_expiry_date,contract_id,cca_id,comments,under_processing,renewal_stage_id,
		point_of_entry_id, number, tid_no, initial_issue_date, customs_letter_no, customs_letter_date, shipment_ref, original_documnets_delivery_date, value, exported_date,
		segment_name,serial_number,asset_number,part_number,invoice_number,
		   type_of_asset_name,asset_status,plate_number,chassis_number,radioactive_license_number', 'safe', 'on' => 'search'),
	);
    }

    /**
     * @return array relational rules.
     */
    public function relations() {
	// NOTE: you may need to adjust the relation name and the related
	// class name for the relations automatically generated below.
	return array(
	    'dashboardTids' => array(self::HAS_MANY, 'DashboardTids', 'tid_id'),
	    'documents' => array(self::HAS_MANY, 'Document', 'tid_id'),
	    'documentTypeTs' => array(self::HAS_MANY, 'DocumentTypeTid', 'tid_id'),
	    'invoices' => array(self::HAS_MANY, 'Invoice', 'tid_id'),
	    'pointOfEntry' => array(self::BELONGS_TO, 'PointOfEntry', 'point_of_entry_id'),
	    'tidStatuses' => array(self::HAS_MANY, 'TidStatus', 'tid_id'),
	    'cca' => array(self::BELONGS_TO, 'Cca', 'last_status_cca_id'),
	    'contract' => array(self::BELONGS_TO, 'Contract', 'last_status_contract_id'),
	    'renewalStage' => array(self::BELONGS_TO, 'RenewalStage', 'last_status_renewal_stage_id'),
	    'tiType' => array(self::BELONGS_TO, 'TiType', 'last_status_ti_type_id'),
	    'assets' => array(self::HAS_MANY, 'VAsset', 'tid_id'),
	);
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
	return array(
	    'point_of_entry_id' => Yii::t('default', 'POE'),
	    'point_of_entry_name' => Yii::t('default', 'POE'),
	    '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'),
	    'original_documnets_delivery_date' => Yii::t('default', 'Original Documnets Delivery Date'),
	    'value' => Yii::t('default', 'Value'),
	    'exported_date' => Yii::t('default', 'Exported Date'),
	    'contract_id' => Yii::t('default', 'Contract Ref') . "<font class='red-star' color='red'> * </font>",
	    'cca_id' => Yii::t('default', 'CCA') . "<font class='red-star' color='red'> * </font>",
	    'ti_type_id' => Yii::t('default', 'TI Type') . "<font class='red-star' color='red'> * </font>",
	    'ti_issue_date' => Yii::t('default', 'Issue Date') . "<font class='red-star' color='red'> * </font>",
	    'ti_expiry_date' => Yii::t('default', 'Expiry Date') . "<font class='red-star' color='red'> * </font>",
	    'ti_status' => Yii::t('default', 'Status'),
	    'comments' => Yii::t('default', 'Comments'),
	    'renewal_stage_id' => Yii::t('default', 'Processing Stage'),
	    'renewal_stage_name' => Yii::t('default', 'Processing Stage'),
	    'under_processing' => Yii::t('default', 'Under Processing'),
	    'last_status_contract_id' => Yii::t('default', 'Contract Ref') . "<font class='red-star' color='red'> * </font>",
	    'last_status_cca_id' => Yii::t('default', 'CCA') . "<font class='red-star' color='red'> * </font>",
	    'last_status_ti_type_id' => Yii::t('default', 'TI Type') . "<font class='red-star' color='red'> * </font>",
	    'last_status_ti_issue_date' => Yii::t('default', 'Issue Date') . "<font class='red-star' color='red'> * </font>",
	    'last_status_ti_expiry_date' => Yii::t('default', 'Expiry Date') . "<font class='red-star' color='red'> * </font>",
	    'last_status_ti_status' => Yii::t('default', 'Status'),
	    'last_status_comments' => Yii::t('default', 'Comments'),
	    'last_status_renewal_stage_id' => Yii::t('default', 'Processing Stage'),
	    'last_status_under_processing' => Yii::t('default', 'Under Processing'),
	    'segment_name' => Yii::t('default', 'Segment'),
	    'serial_number' => Yii::t('default', 'Asset Serial Number'),
	    'asset_number' => Yii::t('default', 'Asset Number'),
	    'part_number' => Yii::t('default', 'Asset Part Number'),
	    'type_of_asset_name' => Yii::t('default', 'Asset Type'),
	    'asset_status' => Yii::t('default', 'Asset Status'),
	    'plate_number' => Yii::t('default', 'Plate Number'),
	    'chassis_number' => Yii::t('default', 'Chassis Number'),
	    'radioactive_license_number' => Yii::t('default', 'Radioactive License Number'),
	    'invoice_number' => Yii::t('default', 'Invoice Number'),
	    'expiry_days' => Yii::t('default', 'Days Count'),
	    'transferred_to_contract_id' => Yii::t('default', 'Transfer to Contract'),
	    'grace_period' => 'Days to Expire',
	);
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     *
     * Typical usecase:
     * - Initialize the model fields with values from filter form.
     * - Execute this method to get CActiveDataProvider instance which will filter
     * models according to data in model fields.
     * - Pass data provider to CGridView, CListView or any similar widget.
     *
     * @return CActiveDataProvider the data provider that can return the models
     * based on the search/filter conditions.
     */
    public function search($segment= null, $value= null, $date= null, $renewal= null) {
	// @todo Please modify the following code to remove attributes that should not be searched.

	$criteria = $this->ModelCriteria(null, $segment, $value, $date, $renewal);

	$pageSize = $this->yiiparam('pageSize');
	return new CActiveDataProvider($this, array(
	    'pagination' => array('pageSize' => $pageSize,),
	    'criteria' => $criteria,
	));
    }

    /**
     * Get the query used to get the current model information to be used in export to excel action
     * @return CDbCriteria the criteria used to get the current model information
     */
    public function ModelCriteria($with_invoices_assets = null, $segment= null, $value= null, $date= null, $renewal= null) {
	$criteria = new CDbCriteria;
	$criteria->compare('point_of_entry_name', $this->point_of_entry_id, true);
	$criteria->compare('number', $this->number, true);
	$criteria->compare('tid_no', $this->tid_no, true);
	$criteria->compare('initial_issue_date', $this->initial_issue_date, true);
	$criteria->compare('customs_letter_no', $this->customs_letter_no, true);
	$criteria->compare('customs_letter_date', $this->customs_letter_date, true);
	$criteria->compare('shipment_ref', $this->shipment_ref, true);
	$criteria->compare('original_documnets_delivery_date', $this->original_documnets_delivery_date, true);
	$criteria->compare('value', $this->value, true);
	$criteria->compare('exported_date', $this->exported_date, true);
	$criteria->compare('grace_period', $this->grace_period);
	$criteria->compare('expiry_days', $this->expiry_days);

	$fine = $this->fine;
	if ($fine != null && $fine != '') {
	    if (strpos($this->fine, ',') == false)
		if (strpos($this->fine, '$') === false)
		    $fine = '$' . number_format($this->fine, 0, '.', ',');
		else
		    $fine = '$' . number_format(substr($this->fine, 1), 0, '.', ',');
	    if (strpos($fine, '$') === false)
		$fine = '$' . $this->fine;
	}
	$criteria->compare('fine', $fine);


	//last tid info
	$criteria->compare('contract_reference', $this->last_status_contract_id, true);
	$criteria->compare('ti_type_name', $this->last_status_ti_type_id, true);
	$criteria->compare('last_status_ti_expiry_date', $this->last_status_ti_expiry_date, true);
	$criteria->compare('last_status_ti_status', $this->last_status_ti_status, true);
	$criteria->compare('renewal_stage_name', $this->last_status_renewal_stage_id, true);
	$criteria->compare('last_status_under_processing', $this->last_status_under_processing, true);

	if ($this->renewal_stage_name != null) {
	    $criteria->compare('renewal_stage_name', $this->renewal_stage_name, true);
	}

	if ($this->point_of_entry_name != null)
	    $criteria->compare('point_of_entry_name', $this->point_of_entry_name, true);
	//assets & invoice
	$ids = $this->setInvoiceAssetConditions($criteria);
	if ($ids != '')
	    $criteria->addCondition('t.id in (' . $ids . ')');


	$ids = $this->setTidStatusConditions($criteria);
	if ($ids != '')
	    $criteria->addCondition('t.id in (' . $ids . ')');


	if ($with_invoices_assets != null) {
	    $criteria->join = 'left join invoice on t.id = invoice.tid_id
				left join v_asset on invoice.id = v_asset.invoice_id
				left join user on invoice.last_edited_by = user.id';
	}


	if ($segment != 'Total' && $segment != 'Multi Segment' && $segment != 'Non Segment')
	    $criteria->compare('t.segment_name', $segment);
	elseif ($segment == 'Multi Segment')
	    $criteria->addCondition("t.segment_name like '%,%'");
	elseif ($segment == 'Non Segment')
	    $criteria->addCondition("t.segment_name is null");

	if ($value != null) {
	    if ($value != 'Total')
		if ($value == 'Fine' || $value == 'Days Count')
		    $criteria->compare('last_status_ti_status', 'Expired');
		else
		if (!$renewal)
		    $criteria->compare('last_status_ti_status', $value);
	}
	if ($renewal != null && $renewal) {
	    if ($value == 'Total Renewal')
		$criteria->addCondition('last_status_renewal_stage_id is not null');
	    else
		$criteria->compare('renewal_stage_name', $value);
	}

	return $criteria;
    }

    /**
     * Returns the static model of the specified AR class.
     * Please note that you should have this exact method in all your CActiveRecord descendants!
     * @param string $className active record class name.
     * @return Tid the static model class
     */
    public static function model($className=__CLASS__) {
	return parent::model($className);
    }

    /**
     * Save TID status information
     * @param string $error_msg the error msg if saving tid status failed.
     * @param Boolean $is_create_action if the request action is create or update
     * @return Boolean true if saving tid status successed , false otherwise
     */
    public function createUpdateStatus($is_create_action, &$error_msg) {
	$status = ($is_create_action) ? new TidStatus() : $this->getLastStatus();
	$status->ti_type_id = $this->ti_type_id;
	$status->cca_id = $this->cca_id;
	$status->contract_id = $this->contract_id;
	$status->renewal_stage_id = $this->renewal_stage_id;
	$status->comments = $this->comments;
	$status->under_processing = $this->under_processing;
	$status->ti_expiry_date = $this->ti_expiry_date;
	$status->ti_issue_date = $this->ti_issue_date;
	$status->ti_status = 'Valid';
	$status->tid_id = $this->id;
	if ($status->save())
	    return true;
	else {
	    $error_msg = CHtml::errorSummary($status);
	    return false;
	}
    }

    /**
     * Get the last status of tid
     * @return TidStatus the last tid status of the tid
     */
    public function getLastStatus() {
	$cr = new CDbCriteria();
	$cr->condition = 'tid_id = ' . $this->id;
	$cr->order = 'created_date desc';
	$cr->limit = 1;
	$model = TidStatus::model()->find($cr);
	return TidStatus::model()->find($cr);
    }

    /**
     * Fill information of last TID status    
     */
    public function fillLastStatusInfo() {
	$status = $this->getLastStatus();
	if ($status != null) {
	    $this->ti_type_id = $status->ti_type_id;
	    $this->cca_id = $status->cca_id;
	    $this->contract_id = $status->contract_id;
	    $this->renewal_stage_id = $status->renewal_stage_id;
	    $this->comments = $status->comments;
	    $this->under_processing = $status->under_processing;
	    $this->ti_expiry_date = $status->ti_expiry_date;
	    $this->ti_issue_date = $status->ti_issue_date;
	}
    }

    /**
     * Save TID documents
     * @param string $error_msg the error msg if saving tid documents failed.
     * @return Boolean true if saving tid documents successed , false otherwise
     */
    public function saveDocuments(&$error_msg) {
	return true;
    }

    /**
     *  generate the unique number for TID
     */
    protected function beforeValidate() {
	if ($this->isNewRecord) {
	    $this->number = $this->generateNumber();
	}
	return parent::beforeValidate();
    }

    /**
     * generate TID number
     *
     */
    public function generateNumber() {

	if ($this->initial_issue_date != null) {
	    $cr = new CDbCriteria();
	    $static_part = date("Y-m-d", strtotime($this->initial_issue_date)); //(date("y"));
	    $cr->select = "MAX(CAST(SUBSTRING(number, 10, length(number) - 9) AS UNSIGNED)) AS MaxNumber";
	    $cr->condition = "number like '%" . $static_part . "%'";

	    $model = Tid::model();
	    //Get max sequence number
	    $seq_number = $model->commandBuilder->createFindCommand(
			    $model->tableName(), $cr)->queryScalar();
	    //FORMAT THE NUMBER TO BE 3 DIGITS
	    $seq_number = $static_part . "-" . str_pad((string) ($seq_number + 1), 3, '0', STR_PAD_LEFT);

	    return $seq_number;
	}
	return null;
    }

    /**
     * check if tid status info is applied in the seach query and build the query for it
     * @param string $error_msg the error msg if query failed.
     * @return String the ids of TIds seperated by comma that satisfy the conditions
     */
    public function setTidStatusConditions() {
	$ids = "";
	if ($this->contract_id != null || $this->cca_id != null ||
		$this->ti_type_id != null || $this->ti_issue_date != null ||
		$this->ti_expiry_date != null || $this->ti_status != null ||
		$this->comments != null || $this->renewal_stage_id != null ||
		$this->under_processing != null || $this->transferred_to_contract_id != null) {

	    $criteria = new CDbCriteria();
	    $criteria->select = 't.tid_id';
	    $criteria->compare('contract.reference', $this->contract_id, true);
	    $criteria->compare('cca.full_name', $this->cca_id, true);
	    $criteria->compare('ti_type.name', $this->ti_type_id, true);
	    $criteria->compare('ti_issue_date', $this->ti_issue_date, true);
	    $criteria->compare('ti_expiry_date', $this->ti_expiry_date, true);
	    $criteria->compare('ti_status', $this->ti_status, true);
	    $criteria->compare('t.comments', $this->comments, true);
	    $criteria->compare('renewal_stage.name', $this->renewal_stage_id, true);
	    $criteria->compare('t.under_processing', $this->under_processing, true);
	    $criteria->compare('transferrd_contract.reference', $this->transferred_to_contract_id, true);

	    $criteria->join = 'inner join contract on contract.id = t.contract_id
			   inner join cca on cca.id = t.cca_id
			   inner join ti_type on ti_type.id = t.ti_type_id
			   left join renewal_stage on renewal_stage.id = t.renewal_stage_id
			   left join contract transferrd_contract on transferrd_contract.id = t.transferred_to_contract_id';

	    $model = new TidStatus();
	    $result = $model->commandBuilder->createFindCommand(
			    $model->tableName(), $criteria)->queryAll();

	    foreach ($result as $res) {
		$ids .= $res['tid_id'] . ",";
	    }
	    $ids = substr($ids, 0, -1);
	    if ($ids == '')
		$ids = '-1';
	}
	return $ids;
    }

    /**
     * check if asset and invoice info is applied in the seach query and build the query for it
     * @param string $error_msg the error msg if query failed
     * @return String the ids of TIds seperated by comma that satisfy the conditions
     */
    public function setInvoiceAssetConditions() {
	$ids = "";
	if ($this->invoice_number != null) {
	    $criteria = new CDbCriteria();
	    $criteria->select = 't.tid_id';
	    $criteria->compare('number', $this->invoice_number, true);
	    $model = new Invoice();
	    $result = $model->commandBuilder->createFindCommand(
			    $model->tableName(), $criteria)->queryAll();
	    foreach ($result as $res) {
		$ids .= $res['tid_id'] . ",";
	    }
	    $ids = substr($ids, 0, -1);
	    if ($ids == '')
		$ids = '-1';
	}
	if ($ids != '-1' && ($this->serial_number != null ||
		$this->asset_number != null || $this->part_number != null ||
		$this->asset_status != null || $this->plate_number != null ||
		$this->chassis_number != null || $this->radioactive_license_number != null ||
		$this->segment_name != null || $this->type_of_asset_name != null)) {

	    $criteria = new CDbCriteria();
	    $criteria->select = 't.tid_id';
	    $criteria->compare('invoice_number', $this->invoice_number, true);
	    $criteria->compare('serial_number', $this->serial_number, true);
	    $criteria->compare('asset_number', $this->asset_number, true);
	    $criteria->compare('part_number', $this->part_number, true);
	    $criteria->compare('asset_status', $this->asset_status, true);
	    $criteria->compare('plate_number', $this->plate_number, true);
	    $criteria->compare('chassis_number', $this->chassis_number, true);
	    $criteria->compare('radioactive_license_number', $this->radioactive_license_number, true);
	    $criteria->compare('segment_name', $this->segment_name, true);
	    $criteria->compare('type_of_asset_name', $this->type_of_asset_name, true);


	    $model = new VAsset();
	    $result = $model->commandBuilder->createFindCommand(
			    $model->tableName(), $criteria)->queryAll();

	    foreach ($result as $res) {
		$ids .= $res['tid_id'] . ",";
	    }
	    $ids = substr($ids, 0, -1);
	    if ($ids == '')
		$ids = '-1';
	}
	return $ids;
    }

    /**
     * Returns primary key for the current view
     *
     * @return String primary key column name
     */
    public function primaryKey() {
	return 'id';
    }

    /**
     * Get number of tid with the specified status
     * @param String $status the status of tid
     * @return Integer the number of tid with the specified status
     */
    public static function getTIDCount($status) {
	$result = null;
	if ($status != null && $status != '') {
	    $cr = new CDbCriteria();
	    $cr->select = "count(*) as tid_count";
	    $cr->condition = "last_status_ti_status ='" . $status . "'";
	    $model = VTid::model();
	    $result = $model->commandBuilder->createFindCommand(
			    $model->tableName(), $cr)->queryScalar();
	}
	if ($result == null)
	    $result = 0;
	return $result;
    }

}
