<?php

/**
 * This is the model class for table "{{purchase_order}}".
 *
 * The followings are the available columns in table '{{purchase_order}}':
 * @property integer $id
 * @property integer $crm_user_id
 * @property integer $vendor_id
 * @property integer $project_ids
 * @property integer $connected_file_id
 * @property integer $contact_id
 * @property string $order_date
 * @property string $required_date
 * @property integer $currency
 * @property integer $exchange_rate
 * @property string $reference
 * @property integer $payment_terms
 * file_id
 */
class PurchasingOrder extends CActiveRecord {

    /**
     * @return string the associated database table name
     */
    public $vendor_name;
    public $project_name;
    public $title;
    public $connected_file;
    public $tot_amount;
    public $purchasingordermonth;
    public $total_value;
    public $value;
    public $country;
    public $searchstartdate;
    public $searchenddate;

    public function tableName() {
        return '{{purchasing_order}}';
    }

    /**
     * @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('vendor_id,order_date, currency,payment_terms,vendor_contact_id', 'required'),
            array('crm_user_id,vendor_id,file_id,currency,payment_terms,invoice_type', 'numerical', 'integerOnly' => true),
            array('reference,title,shipping_by,pricing_conditions', 'length', 'max' => 255),
            array('exchange_rate,stock_grand_total,project_ids', 'numerical'),
            array('notes,remarks,shipping_address,terms_and_conditions,template_text', 'length', 'max' => 5000), 
            array('supplier_type', 'length', 'max' => 1),
            // The following rule is used by search().
            // @todo Please remove those attributes that should not be searched.
            array('id,shipping_by,crm_user_id,title,project_ids,stock_grand_total,completed,file_id,vendor_id,order_date, required_date, currency, exchange_rate, reference, payment_terms', '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(
            'file' => array(self::BELONGS_TO, 'MasterFile', 'file_id'),
            'vendor' => array(self::BELONGS_TO, 'Vendor', 'vendor_id'),
            'vendor_details' => array(self::BELONGS_TO, 'Vendor', 'vendor_id'),
            'user' => array(self::BELONGS_TO, 'User', 'crm_user_id'),
            'verifiedby' => array(self::BELONGS_TO, 'User', 'verified_by'),
            'items' => array(self::HAS_MANY, 'PurchasingOrderStockItem', 'purchasing_order_id','order' => 'items.id asc'),
            // 'netTotal' => array(self::STAT, 'PurchasingOrderStockItem', 'purchasing_order_id', 'select' => 'SUM(stock_item_amount)'),
            'materialReceipt' => array(self::HAS_MANY, 'PurchasingMaterialreceipt', 'po_id'),            
            'currencyname' => array(self::BELONGS_TO, 'MasCurrency', 'currency'),
            'paymentterms' => array(self::BELONGS_TO, 'MasPaymentTerm', 'payment_terms'),
            'adItems' => array(self::HAS_MANY, 'PurchasingOrderAddition', 'purchasing_order_id'),
            'dedItems' => array(self::HAS_MANY, 'PurchasingOrderDeduction', 'purchasing_order_id'),
            'netStockQuantity' => array(self::STAT, 'PurchasingOrderStockItem', 'purchasing_order_id', 'select' => 'SUM(stock_item_quantity)'),
            //'netnonStockQuantity' => array(self::STAT, 'PurchasingOrderNonStockItem', 'purchasing_order_id', 'select' => 'SUM(non_stock_item_quantity)'),
            'adTotal' => array(self::STAT, 'PurchasingOrderAddition', 'purchasing_order_id', 'select' => 'SUM(calculated_value)'),
            'dedTotal' => array(self::STAT, 'PurchasingOrderDeduction', 'purchasing_order_id', 'select' => 'SUM(calculated_value)'),
            'shippingby' => array(self::BELONGS_TO, 'MasShipping', 'shipping_by'),
            // 'totalordered' => array(self::STAT, 'PurchasingOrderStockItem', 'purchasing_order_id', 'select' => 'SUM(stock_item_quantity)'),
            'vendorcontact' => array(self::BELONGS_TO, 'VendorContact', 'vendor_contact_id'),
            'taxtype' => array(self::BELONGS_TO, 'MasInvoiceType', 'invoice_type'),
            'createdby' => array(self::BELONGS_TO, 'User', 'created_by'),
            'project_det' => array(self::BELONGS_TO, 'WsProject', 'project_ids'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
        return array(
            'id' => 'ID',
            'crm_user_id' => 'Crm User',
            'vendor_id' => 'Vendor',
            //'connected_file_id' => 'Connected File',
            //'contact_id' => 'Contact',
            'order_date' => 'Order Date',
            'required_date' => 'Required Date',
            'expected_delivery_date' => 'Expected Delivery Date',
            'currency' => 'Currency',
            'exchange_rate' => 'Exchange Rate',
            'reference' => 'Reference/Revision',
            'payment_terms' => 'Payment Terms',
            'file_id' => 'File ID',
            //'project' => 'Project',
            'pricing_conditions' => 'Pricing Conditions',
            //'first_party' => 'First party',
            //'second_party' => 'Second Party',
            'verified_by' => 'Verified By',
            'project_ids' => 'Project',
            'project_classification_id'=>'Project Classification',
        );
    }

    /**
     * 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 report() {
        $criteria = new CDbCriteria;
        $criteria->with = array('file', 'vendor');
        if (!empty($this->vendor_id))
            $condition[] = 't.vendor_id="' . $this->vendor_id . '"';
        if (!empty($this->project))
            $condition[] = 'project="' . $this->project . '"';
        if (!empty($this->country))
            $condition[] = 'vendor.billing_country="' . $this->country . '"';
        if (!empty($this->value))
            $condition[] = 'grand_total<="' . $this->value . '"';
        if (is_array($condition))
            $criteria->condition = implode(' AND ', $condition);
        if (!empty($this->searchstartdate) && !empty($this->searchenddate)) {
            $criteria->addBetweenCondition('order_date', $this->searchstartdate, $this->searchenddate);
        }
        /*$criteria->addCondition('       file.own_company_id=' . Yii::app()->user->master_user_company_id . ' AND 
                                        file.own_branch_id=' . Yii::app()->user->master_user_branch_id, 'AND');*/
        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'sort' => array(
                'defaultOrder' => 't.order_date DESC,t.id DESC,t.file_id DESC,t.vendor_id DESC',
            ),
            'pagination' => false
        ));
    }

    public function openpurchaseordersearch() {
        $criteria = new CDbCriteria;
        $criteria->with = array('file');
        $criteria->condition = 'file.current_process_status ="P_O" OR file.current_process_status="P_MR"';
        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'sort' => array(
                'defaultOrder' => 't.id DESC, t.file_id DESC ,t.required_date DESC',
            ),
            'pagination' => array('pageSize' => 5)
        ));
    }

    public function search($searchterm = null) {
        $criteria = new CDbCriteria;
        $searchterm = empty($searchterm) ? trim(Yii::app()->request->getParam('purchase_order_search')) : $searchterm;
        $criteria->with = array('file', 'file.company');
       /* $criteria->addCondition('       file.own_company_id=' . Yii::app()->user->master_user_company_id . ' AND 
                                        file.own_branch_id=' . Yii::app()->user->master_user_branch_id, 'AND');*/
        //((int) Yii::app()->session['company_id'] > 0 && !isset($_GET['month'])) ? $criteria->addCondition('t.company_id=' . Yii::app()->session['company_id'], 'AND') : '';
        if (!empty($searchterm)) {
            $criteria->addCondition('   t.id like "%' . $searchterm . '%" OR 
                                        t.file_id like "%' . $searchterm . '%" OR 
                                        company.company_name like "%' . $searchterm . '%" OR 
                                        file.file_title like "%' . $searchterm . '%"', 'AND');
        }

        if (isset($_GET['month']))
            $criteria->addCondition("YEAR(required_date)='" . date('Y') . "' AND MONTH(required_date) ='" . date('m', strtotime($_GET['month'])) . "'", 'AND');
        if (isset($_GET['purchasingordermonth']) && is_array($_GET['purchasingordermonth']))
            $_GET['purchasingordermonth'] = $_GET['purchasingordermonth'][0];
        if (isset($_GET['purchasingordermonth']))
            $criteria->addCondition("YEAR(order_date)='" . date('Y') . "' AND MONTH(order_date) ='" . $_GET['purchasingordermonth'] . "'", 'AND');
        if (isset($_GET['vendor_id'])) {
            $vendor_id = (int) ($_GET['vendor_id']);
            $criteria->addCondition("vendor_id=" . $vendor_id, 'AND');
        }
        if (isset($_GET['vendor_id']) && !isset($_GET['purchasingordermonth'])) {
            $criteria->group = 'MONTH(order_date)';
            $criteria->select = 'vendor_id,MONTH(`order_date`) as id, MONTHNAME(`order_date`) as purchasingordermonth, 
                SUM(grand_total*exchange_rate) as total_value';
        }
        //if (empty($searchterm) && !isset($_GET['purchasingordermonth']) && !isset($_GET['vendor_id']))
        //$criteria->addCondition('file.current_process_status="P_O" OR file.current_process_status="P_MR"', 'AND');

        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'sort' => array(
                'defaultOrder' => 't.id DESC, t.file_id DESC ,t.required_date DESC',
            ),
            'pagination' => (Yii::app()->controller->id != "export") ? array('pageSize' => 15) : false,
        ));
    }

    public function generatereport() {
        $criteria = new CDbCriteria;
        if (isset($_POST['rp_from_date']) && isset($_POST['rp_to_date'])) {
            $criteria->condition = 'order_date BETWEEN "' . $_POST['rp_from_date'] . '" AND "' . $_POST['rp_to_date'] . '"';
            if (isset($_POST['vendor_id']))
                $criteria->condition .= ' AND vendor_id in ( ' . $_POST['vendor_id'] . ')';
        }
        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'sort' => array(
                'defaultOrder' => 't.id DESC, t.file_id DESC ,t.required_date DESC',
            ),
            'pagination' => array('pageSize' => 500)
        ));
    }

    public function purchaseordersearch() {
        $criteria = new CDbCriteria;
        $criteria->with = array('file');
        if (isset($_POST['vendor_id'])) {
            $criteria->condition = 'vendor_id=' . $_POST['vendor_id'];
            $criteria->condition .= ' AND file.current_process_status in ("P_O","P_MR")';
        }
        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'sort' => array(
                'defaultOrder' => 't.id DESC, t.file_id DESC ,t.required_date DESC',
            ),
            'pagination' => array('pageSize' => 500)
        ));
    }

    public function purchaseorderforinvoicesearch() {
        $criteria = new CDbCriteria;
        $searchterm = empty($searchterm) ? trim(Yii::app()->request->getParam('openpurchase_search')) : $searchterm;
        $criteria->with = array('file', 'file.company');
        /*$criteria->addCondition('       file.own_company_id=' . Yii::app()->user->master_user_company_id . ' AND 
                                        file.own_branch_id=' . Yii::app()->user->master_user_branch_id, 'AND');*/
        //((int) Yii::app()->session['company_id'] > 0 && !isset($_GET['month'])) ? $criteria->addCondition('t.company_id=' . Yii::app()->session['company_id'], 'AND') : '';
        if (!empty($searchterm)) {
            $criteria->addCondition('   t.id like "%' . $searchterm . '%" OR 
                                        t.file_id like "%' . $searchterm . '%" OR 
                                        company.company_name like "%' . $searchterm . '%" OR 
                                        file.file_title like "%' . $searchterm . '%"', 'AND');
        }
        if (isset($_POST['vendor_id'])) {
            $criteria->addCondition('vendor_id=' . $_POST['vendor_id'],'AND');
            //$criteria->condition[]= 'file.current_process_status in ("M_R","P_PI","P_MR")';
        } /* else {
          $criteria->condition[] = 'file.current_process_status in ("M_R","P_MR")';
          } */
        $criteria->addCondition('file.current_process_status in ("M_R","P_PI","P_MR")','AND');
        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'sort' => array(
                'defaultOrder' => 't.id DESC, t.file_id DESC ,t.required_date DESC',
            ),
            'pagination' => array('pageSize' => 5)
        ));
    }

    public static function getPurchaseorder($current_process_status = null) {

        $condition = 'file.current_process_status="P_O"';
        $model = PurchasingOrder::model()->with('file')->findAll(array('condition' => $condition));
        return CHtml::listData($model, 'id', 'id');
    }

    public function getProduct() {
        $product = null;
        foreach ($this->items as $item)
            $product.=$item->description;
        return $product;
    }

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

    public function getTotals($ids) {
        $ids = implode(",", $ids);
        $connection = Yii::app()->db;
        if (!empty($ids)) {
            $command = $connection->createCommand("SELECT SUM(grand_total) FROM `crm_purchasing_order` where id in ($ids)");
            return $command->queryScalar();
        }
        else
            return "";
    }
    
    public function getGrand_total_bc() {
        return ($this->grand_total * $this->exchange_rate);
    }
   // code added sarika on 16-08-2022
    public  function gettotalordered(){
        if($this->id){
            $sql="SELECT SUM(stock_item_quantity) AS totalordered FROM erp_purchasing_order_stock_item WHERE purchasing_order_id=".$this->id ;
            $ordered_qty=Yii::app()->db->createCommand($sql)->queryScalar();
            return (float) $ordered_qty;
        }
    }
    public  function getnetTotal(){
       
        if($this->id){

            $sql="SELECT SUM(stock_item_amount) AS netTotal FROM erp_purchasing_order_stock_item WHERE purchasing_order_id=".$this->id ;
            // echo $sql;exit;
            $net_Total=Yii::app()->db->createCommand($sql)->queryScalar();
            return (float) $net_Total;
        }
    }

}
