<?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 "v_tid_detailed_dashboard".
 *
 * The followings are the available columns in table 'v_tid_detailed_dashboard':
 * @property integer $id
 * @property integer $summary_dashboard_per_segment_id
 * @property integer $summary_dashboard_per_segment_and_renewal_id
 * @property integer $segment_id
 * @property string $segment_name
 * @property integer $tid_id
 * @property integer $tid_status_id
 * @property string $status
 * @property integer $renewal_stage_id
 * @property integer $grace_period
 * @property integer $days_count
 * @property double $fine
 * @property string $number
 * @property string $tid_no
 * @property string $value
 * @property string $point_of_entry_name
 * @property string $last_status_under_processing
 * @property string $renewal_stage_name
 * @property string $last_status_ti_expiry_date
 * @property string $last_status_ti_issue_date
 * @property integer $last_status_ti_type_id
 * @property integer $last_status_cca_id
 * @property integer $last_status_contract_id
 * @property string $ti_type_name
 * @property string $cca_full_name
 * @property string $contract_name
 * @property string $contract_reference
 */
class VTidDetailedDashboard 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_detailed_dashboard';
    }

    /**
     * @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('segment_name, tid_id, tid_status_id, last_status_ti_status, number, tid_no, point_of_entry_name', 'required'),
	    array('id, segment_id, tid_id, tid_status_id, renewal_stage_id, grace_period, expiry_days, last_status_ti_type_id, last_status_cca_id, last_status_contract_id', 'numerical', 'integerOnly' => true),
	    array('fine', 'numerical'),
	    array('segment_name', 'length', 'max' => 500),
	    array('last_status_ti_status', 'length', 'max' => 18),
	    array('number, value, renewal_stage_name', 'length', 'max' => 45),
	    array('tid_no', 'length', 'max' => 20),
	    array('point_of_entry_name, ti_type_name, cca_full_name', 'length', 'max' => 150),
	    array('last_status_under_processing', 'length', 'max' => 3),
	    array('contract_name, contract_reference', 'length', 'max' => 30),
	    array('last_status_ti_expiry_date, last_status_ti_issue_date', 'safe'),
	    // The following rule is used by search().
	    // @todo Please remove those attributes that should not be searched.
	    array('id,
		segment_id, segment_name, tid_id, tid_status_id, last_status_ti_status, renewal_stage_id, grace_period, expiry_days, fine, number, tid_no, value,
		point_of_entry_name,transferred_to_contract_id,fine,
		last_status_under_processing, renewal_stage_name, last_status_ti_expiry_date, last_status_ti_issue_date, last_status_ti_type_id, last_status_cca_id,
		last_status_contract_id, ti_type_name, cca_full_name, contract_name, contract_reference,
		,comments,point_of_entry_id,initial_issue_date, customs_letter_no, customs_letter_date, shipment_ref, original_documnets_delivery_date, value, exported_date,
		ti_status,ti_type_id,ti_issue_date,ti_expiry_date,contract_id,cca_id,comments,under_processing,renewal_stage_id,		
		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(
	    'tid' => array(self::BELONGS_TO, 'Tid', 'tid_id'),
	);
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
	return array(
	    'point_of_entry_name' => Yii::t('default', 'POE'),
	    'point_of_entry_id' => 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', 'Asset 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'),
	    'id' => 'ID',
	    'segment_id' => Yii::t('default', 'Segment'),
	    'segment_name' => Yii::t('default', 'Segment'),
//	    'tid_id' => 'Tid',
//	    'tid_status_id' => 'Tid Status',
	    'status' => Yii::t('default', 'Status'),
	    'renewal_stage_id' => Yii::t('default', 'Processing Stage'),
	    'grace_period' => Yii::t('default', 'Days to Expire'),
	    'expiry_days' => Yii::t('default', 'Days Count'),
	    'fine' => Yii::t('default', 'Fine'),
	    'ti_type_name' => Yii::t('default', 'TI Type'),
	    'cca_full_name' => Yii::t('default', 'CCA'),
	    'contract_name' => Yii::t('default', 'Contract'),
	    'contract_reference' => Yii::t('default', 'Contract Ref'),
	);
    }

    /**
     * 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) {
	// @todo Please modify the following code to remove attributes that should not be searched.

	$criteria = new CDbCriteria;

	$criteria->compare('id', $this->id);

	$criteria->compare('segment_id', $this->segment_id);
	$criteria->compare('segment_name', $this->segment_name, true);
	$criteria->compare('tid_id', $this->tid_id);
	$criteria->compare('tid_status_id', $this->tid_status_id);
	$criteria->compare('last_status_ti_status', $this->last_status_ti_status, true);
	$criteria->compare('renewal_stage_id', $this->renewal_stage_id);
	$criteria->compare('grace_period', $this->grace_period);
	$criteria->compare('expiry_days', $this->expiry_days);
	//$criteria->compare('fine', $this->fine);
	$criteria->compare('number', $this->number, true);
	$criteria->compare('tid_no', $this->tid_no, true);
	$criteria->compare('value', $this->value, true);
	$criteria->compare('point_of_entry_name', $this->point_of_entry_id, true);


	$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('last_status_under_processing', $this->last_status_under_processing, true);
	$criteria->compare('renewal_stage_name', $this->renewal_stage_id, true);
	$criteria->compare('last_status_ti_expiry_date', $this->last_status_ti_expiry_date, true);
	$criteria->compare('last_status_ti_issue_date', $this->last_status_ti_issue_date, true);
	$criteria->compare('last_status_ti_type_id', $this->last_status_ti_type_id);
	$criteria->compare('last_status_cca_id', $this->last_status_cca_id);
	$criteria->compare('last_status_contract_id', $this->last_status_contract_id);
	$criteria->compare('ti_type_name', $this->ti_type_name, true);
	$criteria->compare('cca_full_name', $this->cca_full_name, true);
	$criteria->compare('contract_name', $this->contract_name, true);
	$criteria->compare('contract_reference', $this->contract_reference, 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('exported_date', $this->exported_date, true);

	if ($this->transferred_to_contract_id != null) {
	    $criteria->join = 'left join contract transferrd_contract on transferrd_contract.id = t.transferred_to_contract_id';
	    $criteria->compare('transferrd_contract.reference', $this->transferred_to_contract_id, true);
	}

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

	//assets & invoice
	$ids = $this->setInvoiceAssetConditions($criteria);
	if ($ids != '')
	    $criteria->addCondition('t.tid_id in (' . $ids . ')');


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

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

	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('renewal_stage_id is not null');
	    else
		$criteria->compare('renewal_stage_name', $value);
	}

	if ($date != null)
	    $criteria->addCondition("date ='" . $date . "'");

	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 VTidDetailedDashboard the static model class
     */
    public static function model($className=__CLASS__) {
	return parent::model($className);
    }

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

    /**
     * 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) {

	    $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->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';

	    $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;
    }

}
