<?php

/**
 * This is the model class for table "{{transfers}}".
 *
 * The followings are the available columns in table '{{transfers}}':
 * @property integer $id
 * @property string $delivery_notes
 * @property string $bill_of_exit
 * @property integer $ta_no
 * @property string $isInernal
 * @property integer $created_by
 * @property string $created_datetime
 */
class Transfers extends CActiveRecord
{
    /**
     * @return string the associated database table name
     */
    public function tableName()
    {
        return '{{transfers}}';
    }

    /**
     * @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('delivery_notes, transfer_ownership, bill_of_exit, ta_no, created_by, created_datetime', 'required'),
            array('ta_no, created_by', 'numerical', 'integerOnly' => true),
            array('bill_of_exit, bl_no, bl_date, neg_bank_name, payment_mode, payment_credit, value_neg, container_seal, vehicle_no', 'length', 'max' => 255),
            array('export_cost', 'length', 'max' => 15),
            array('area, inv_quantity, inv_amount, no_of_certs, payment_receipt, cites_cert_no, lead_time, validity, import_cert_obtained', 'length', 'max' => 255),
            array('isInernal, status', 'length', 'max' => 1),
            array('exp_inv_no, exp_packing_list, exp_declaration_no, exp_bl_no, imp_inv_no, imp_packing_list, imp_declaration_no', 'length', 'max' => 255),
            array('comment', 'length', 'max' => 500),
            // The following rule is used by search().
            // @todo Please remove those attributes that should not be searched.
            array('id, delivery_notes, bill_of_exit, ta_no, isInernal, created_by, created_datetime, comment', '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(
            'cites_items' => array(self::HAS_MANY, 'OutboundCites', 'transfer_id', 'order' => 'cites_items.id ASC'),
            'customer' => array(self::BELONGS_TO, 'Customers', 'customer_id'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels()
    {
        return array(
            'id' => 'ID',
            'delivery_notes' => 'Delivery Notes',
            'bill_of_exit' => 'Bill Of Exit',
            'ta_no' => 'Ta No',
            'isInernal' => 'Is Inernal',
            'created_by' => 'Created By',
            'created_datetime' => 'Created Datetime',
        );
    }

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

        $criteria = new CDbCriteria;

        $criteria->compare('id', $this->id);
        $criteria->compare('delivery_notes', $this->delivery_notes, true);
        $criteria->compare('bill_of_exit', $this->bill_of_exit, true);
        $criteria->compare('ta_no', $this->ta_no);
        $criteria->compare('isInernal', $this->isInernal, true);
        $criteria->compare('created_by', $this->created_by);
        $criteria->compare('created_datetime', $this->created_datetime, true);

        return new CActiveDataProvider($this, array(
            'criteria' => $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 Transfers the static model class
     */
    public static function model($className = __CLASS__)
    {
        return parent::model($className);
    }
    public function country_origins()
    {
        $labels = array();
        foreach (OutboundInvoiceItems::model()->findAll(array('select' => 'DISTINCT outb_contry_of_origin', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ')')) as $each) {
            if ($each->outb_contry_of_origin != '') {
                $labels[] = strtoupper($each->outb_contry_of_origin);
            }
        }
        return implode(',', $labels);
    }
    public function country_origins_labels()
    {
        $labels = array();
        foreach (OutboundInvoiceItems::model()->findAll(array('select' => 'DISTINCT outb_contry_of_origin', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ')')) as $each) {
            if ($each->outb_contry_of_origin != '') {
                $labels[] = strtoupper($each->coo->coo_name);
            }
        }
        return implode(',', $labels);
    }

    public function country_origins_labels_br()
    {
        $labels = array();
        foreach (OutboundInvoiceItems::model()->findAll(array('select' => 'DISTINCT outb_contry_of_origin', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ')')) as $each) {
            if ($each->outb_contry_of_origin != '') {
                $labels[] = strtoupper($each->coo->coo_name);
            }
        }
        return implode('<br>', $labels);
    }
    public function country_origins_labels_comma()
    {
        $labels = array();
        foreach (OutboundInvoiceItems::model()->findAll(array('select' => 'DISTINCT outb_contry_of_origin', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ')')) as $each) {
            if ($each->outb_contry_of_origin != '') {
                $labels[] = strtoupper($each->coo->coo_name);
            }
        }
        return implode(', ', $labels);
    }

    public function all_containers()
    {
        $labels = array();
        foreach (OutboundInvoiceItems::model()->findAll(array('select' => 'DISTINCT outb_container_no', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ')')) as $each) {
            if ($each->outb_container_no != '') {
                $labels[] = strtoupper($each->outb_container_no);
            }
        }
        return implode('<br>', array_unique($labels));
    }

    public function all_containers_aligned()
    {
        $label = '';
        foreach (OutboundInvoiceItems::model()->findAll(array('select' => 'DISTINCT outb_container_no', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ')', 'limit' => 2)) as $i => $each) {
            if ($each->outb_container_no != '' ) {
                if( $i > 0 ){
                    $label .= '<br>';
                }
                $label .= strtoupper($each->outb_container_no); 
            }
        }
        return $label;
    }

    public function all_containers_aligned_balance()
    {
        $label = '';
        foreach (OutboundInvoiceItems::model()->findAll(array('select' => 'DISTINCT outb_container_no', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ')')) as $i => $each) {
            if ($each->outb_container_no != '' && $i > 1 ) {
                if( $i > 2 ){
                    $label .= ', ';
                }
                $label .= strtoupper($each->outb_container_no); 
            }
        }
        return $label;
    }

    public function all_containers_with_count()
    {
        $masters = array(0 => 'None', 1 => '20 STD', 2 => '40 STD', 3 => '40 HC');
        $labels = array();
        foreach (OutboundInvoiceItems::model()->findAll(array('select' => 'outb_container_no, COUNT(id) AS count_of_containers', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ')', 'group' => 'outb_container_no')) as $each) {
            if ($each->outb_container_no != '') {
                $labels[] = strtoupper($each->outb_container_no) . ' 1  x ' . $masters[OutboundContainers::model()->find(array('condition' => 'transfer_id=' . $this->id . ' AND container_no="' . $each->outb_container_no . '"'))->container_size];
            }
        }
        return implode('<br>', $labels);
    }

    public function getTotal_quantity()
    {
        return (float) OutboundInvoiceItems::model()->find(array('select' => 'SUM(outb_quantity) AS outb_quantity', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ') AND outb_quantity>0'))->outb_quantity;
    }
    public function getTotal_cartons()
    {
        return (float) OutboundInvoiceItems::model()->find(array('select' => 'SUM(outb_remarks) AS outb_remarks', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ') AND outb_quantity>0'))->outb_remarks;
    }
    public function getTotal_weight()
    {
        return (float) OutboundInvoiceItems::model()->find(array('select' => 'SUM(outb_gross_wt) AS outb_gross_wt', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ') AND outb_quantity>0'))->outb_gross_wt;
    }
    public function getTotal_volume()
    {
        return (float) OutboundInvoiceItems::model()->find(array('select' => 'SUM(outb_cbm) AS outb_cbm', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ') AND outb_quantity>0'))->outb_cbm;
    }
    public function getTotal_value()
    {
        return (float) OutboundInvoiceItems::model()->find(array('select' => 'SUM(outb_invoice_rate) AS outb_invoice_rate', 'condition' => 'invoice_id IN(' . $this->delivery_notes . ') AND outb_quantity>0'))->outb_invoice_rate;
    }
    public function getTotal_freight()
    {
        return (float) OutboundInvoice::model()->find(array('select' => 'SUM(freight_charge) AS freight_charge', 'condition' => 'id IN(' . $this->delivery_notes . ')'))->freight_charge;
    }
    public function getTotal_interest()
    {
        return (float) OutboundInvoice::model()->find(array('select' => 'SUM(interest_charge) AS interest_charge', 'condition' => 'id IN(' . $this->delivery_notes . ')'))->interest_charge;
    }
    public function getTotal_other_charges()
    {
        return (float) OutboundInvoice::model()->find(array('select' => 'SUM(other_charge) AS other_charge', 'condition' => 'id IN(' . $this->delivery_notes . ')'))->other_charge;
    }
    public function getInvoice_details()
    {
        return $this->invoice_no . ' | ' . $this->customer->name;
    }
    public function get_invoices()
    {
        return CHtml::listdata(self::model()->findAll(array('condition' => 'is_posted="Y" AND is_connected="N"')), 'id', 'invoice_details');
    }

    public function generate_invoice()
    {
        $response = array();
        foreach (explode(',', $this->delivery_notes) as $each) {
            $response[] = OutboundInvoice::model()->findBypk($each)->invoice_no;
        }
        return implode(',', $response);
    }
    public function generate_invoice_1()
    {
        $response = array();
        foreach (explode(',', $this->delivery_notes) as $each) {
            $response[] = OutboundInvoice::model()->findBypk($each)->invoice_no;
        }
        return implode(' , ', $response);
    }
    public function generate_dns()
    {
        $response = array();
        foreach (explode(',', $this->delivery_notes) as $each) {
            $response[] = str_replace(',', '|', OutboundInvoice::model()->findBypk($each)->dispatch_notes);
        }
        return implode('|', $response);
    }

    public function getFrom_port()
    {
        $response = array();
        $arr_of_invoice = explode(',', $this->delivery_notes);
        return OutboundInvoice::model()->findBypk($arr_of_invoice[0])->from_port;
    }

    public function getTo_port()
    {
        $response = array();
        $arr_of_invoice = explode(',', $this->delivery_notes);
        return OutboundInvoice::model()->findBypk($arr_of_invoice[0])->destination;
    }

    public function count_of_20ftstd()
    {
        return Yii::app()->db->createCommand('SELECT COUNT(size) AS count_of FROM ( SELECT container_size AS size, container_size FROM nia_outbound_containers WHERE container_size=1 AND container_size >0 AND transfer_id=' . (int) $this->id . ' GROUP BY container_no,container_size ) AS A GROUP BY size')->queryScalar();
    }

    public function count_of_40ftstd()
    {
        return Yii::app()->db->createCommand('SELECT COUNT(size) AS count_of FROM ( SELECT container_size AS size, container_size FROM nia_outbound_containers WHERE container_size=2 AND container_size >0 AND transfer_id=' . (int) $this->id . ' GROUP BY container_no,container_size ) AS A GROUP BY size')->queryScalar();
    }

    public function count_of_20fthc()
    {
        return Yii::app()->db->createCommand('SELECT COUNT(size) AS count_of FROM ( SELECT container_size AS size, container_size FROM nia_outbound_containers WHERE container_size=3 AND container_size >0 AND transfer_id=' . (int) $this->id . ' GROUP BY container_no,container_size ) AS A GROUP BY size')->queryScalar();
    }

    public function count_of_all_container()
    {
        $std_20 = $this->count_of_20ftstd();
        $std_40 = $this->count_of_40ftstd();
        $hc_40 = $this->count_of_20fthc();

        if ($std_20 > 0)
            $result[] = $std_20 . 'x20 STD';
        if ($std_40 > 0)
            $result[] = $std_40 . 'x40 STD';
        if ($hc_40 > 0)
            $result[] = $hc_40 . 'x40 HC';
        return $result;
    }

    public function allCategories(){

        $result =  Yii::app()->db->createCommand('SELECT GROUP_CONCAT(DISTINCT HC.description) AS categories FROM {{outbound_invoice_items}} OI LEFT JOIN {{hscode}} HC ON HC.hs_code = OI.outb_hs_code WHERE OI.invoice_id IN (' . $this->delivery_notes . ') AND OI.outb_quantity>0')->queryRow();
        return $result['categories'];
    }
}
