<?php

/**
 * This is the model class for table "{{master_file}}".
 *
 * The followings are the available columns in table '{{master_file}}':
 * @property integer $id
 * @property integer $crm_user_id
 * @property integer $customer_id
 * @property string $start_from
 * @property string $current_process_status
 * @property integer $lead_owner_id
 * @property string $isActive
 *
 * The followings are the available model relations:
 * @property AllocatedMaterials[] $allocatedMaterials
 * @property CreditNote[] $creditNotes
 * @property DeliveryNotes[] $deliveryNotes
 * @property Document[] $documents
 * @property Invoice[] $invoices
 * @property Lead[] $leads
 * @property Order[] $orders
 * @property Quotes[] $quotes
 */
class MasterFile extends CActiveRecord {

    /**
     * @return string the associated database table name
     */
    public $file_for_materialallocation;
    public $avariable;
    public $file_slash_id;

    //public $deliveredQuantity;



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

    /**
     * @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('crm_user_id, start_from, current_process_status', 'required'),
            array('crm_user_id, customer_id, ticket_id, lead_owner_id, approved_by', 'numerical', 'integerOnly' => true),
            array(' isActive, is_approved', 'length', 'max' => 1),
            array('current_process_status', 'length', 'max' => 2),
            array('file_title,date_of_approval', 'length', 'max' => 255),
            // The following rule is used by search().
            // @todo Please remove those attributes that should not be searched.
            array('id, crm_user_id,ticket_id, customer_id, start_from, current_process_status, lead_owner_id, isActive', '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(
            'allocatedMaterials' => array(self::HAS_MANY, 'AllocatedMaterial', 'file_id'),
            'creditNotes' => array(self::HAS_MANY, 'Creditnote', 'file_id'),
            'deliveryNotes' => array(self::HAS_MANY, 'DeliveryNote', 'file_id'),
            'lead' => array(self::HAS_ONE, 'Lead', 'file_id'),
            'order' => array(self::HAS_ONE, 'Order', 'file_id'),
            'quote' => array(self::HAS_ONE, 'Quotes', 'file_id'),
            // 'deliveredQuantity' => array(self::STAT, 'Order', 'file_id', 'select' => 'SUM(d_item.picked_qty)',
            //     'join' => '
            //         JOIN {{master_file}} as mf
            //         JOIN {{delivery_note}} as d_note
            //         JOIN {{delivery_item}} as d_item
            //         ON d_item.delivery_note_id = d_note.id AND mf.id=t.file_id AND mf.id=d_note.file_id',
            //     'condition'=>'d_note.status!="CN"'
            // ),
            'deliveredBonusQuantity' => array(self::STAT, 'Order', 'file_id', 'select' => 'SUM(d_item.picked_bonus_qty)',
                'join' => '
                    JOIN {{master_file}} as mf
                    JOIN {{delivery_note}} as d_note
                    JOIN {{delivery_item}} as d_item
                    ON d_item.delivery_note_id = d_note.id AND mf.id=t.file_id AND mf.id=d_note.file_id',
                'condition' => 'd_note.status!="CN"'
            ),
            // 'invoicedQuantity' => array(self::STAT, 'Order', 'file_id', 'select' => 'SUM(d_item.quantity)',
            //     'join' => '
            //         JOIN {{master_file}} as mf
            //         JOIN {{invoice}} as d_note
            //         JOIN {{invoice_item}} as d_item
            //         ON d_item.invoice_id = d_note.id AND mf.id=t.file_id AND mf.id=d_note.file_id',
            // ),
            'receivedQuantity' => array(self::STAT, 'PurchasingOrder', 'file_id', 'select' => 'SUM(mr_item.qcchecked_quantity)',
                'join' => '
                    JOIN {{master_file}} as mf
                    JOIN {{purchasing_material_receipt}} as mr_note
                    JOIN {{purchasing_material_receipt_item}} as mr_item
                    ON mr_item.purchasing_materialreceipt_id = mr_note.id AND mf.id=t.file_id AND mf.id=mr_note.file_id',
            ),
            'materialReceipt' => array(self::HAS_MANY, 'PurchasingMaterialreceipt', 'file_id'),
            'documents' => array(self::HAS_MANY, 'Document', 'file_id'),
            'invoices' => array(self::HAS_MANY, 'Invoice', 'file_id'),
            'purchaserequisition' => array(self::HAS_ONE, 'PurchasingRequisition', 'file_id'),
            'purchaseenquiry' => array(self::HAS_ONE, 'PurchasingEnquiry', 'file_id'),
            'purchaseorder' => array(self::HAS_ONE, 'PurchasingOrder', 'file_id'),
            'purchaseinvoice' => array(self::HAS_MANY, 'PurchasingInvoice', 'file_id'),
            'leadowner' => array(self::BELONGS_TO, 'User', 'lead_owner_id'),
            'approvedBy' => array(self::BELONGS_TO, 'User', 'approved_by'),
            'purchasecredit' => array(self::HAS_MANY, 'Purchasingcredit', 'file_id'),
            'profitCalc0' => array(self::HAS_ONE, 'ProfitCalc', 'file_id'),
            'customer' => array(self::BELONGS_TO, 'Customer', 'customer_id'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
        return array(
            'id' => 'ID',
            'crm_user_id' => 'Crm User',
            'customer_id' => 'Customer',
            'file_title' => 'File Title',
            'start_from' => 'Start From',
            'current_process_status' => 'Current Process Status',
            'lead_owner_id' => 'Lead Owner',
            'isActive' => 'Is Active',
        );
    }

    /**
     * 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('crm_user_id', $this->crm_user_id);
        $criteria->compare('customer_id', $this->customer_id);
        $criteria->compare('start_from', $this->start_from, true);
        $criteria->compare('current_process_status', $this->current_process_status, true);
        $criteria->compare('lead_owner_id', $this->lead_owner_id);
        $criteria->compare('isActive', $this->isActive, true);

        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
        ));
    }

    public function getsearch($searchterm = null) {
        $criteria = new CDbCriteria;
        $searchterm = empty($searchterm) ? trim(Yii::app()->request->getParam('leadowner_search')) : $searchterm;
        if (!empty($searchterm)) {

            $criteria->with = array('leadowner', 'company');
            $criteria->addCondition('   t.id like "%' . $searchterm . '%" OR
                                        company.company_name like "%' . $searchterm . '%" OR
                                        leadowner.first_name like "%' . $searchterm . '%" OR
                                        leadowner.last_name like "%' . $searchterm . '%"
                                            ', 'AND');
        } else {
            //$criteria->with = array('leadowner');
            $criteria->select = 'distinct t.lead_owner_id';
        }

        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'pagination' => (Yii::app()->controller->id != "export") ? array('pageSize' => 20) : false,
        ));
    }

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

    public static function getFiles($current_process_status = null, $notNeedlist = false) {
        $condition = 'isActive="Y"';
        if ($current_process_status != null) {
            if (is_array($current_process_status))
                $current_process_status = implode('","', $current_process_status);
            $condition .= ' AND current_process_status in ("' . $current_process_status . '")';
        }
        $model = self::model()->findAll(array('condition' => $condition, 'order' => 'id DESC,current_process_status'));
        if ($notNeedlist)
            return $model;
        else
            return CHtml::listData($model, 'id', 'id_with_status');
    }

    public static function getFilesForIncentiveCalc() {
        $criteria = new cdbCriteria;
        $current_process_status = implode('","', array("Q", "O", "D", "PD", "I", "PI", "CN"));
        if ($current_process_status != null) {
            if (is_array($current_process_status))
                $current_process_status = implode('","', $current_process_status);
            $condition .= ' AND current_process_status in ("' . $current_process_status . '")';
        }
        $model = self::model()->findAll(array('condition' => $condition, 'order' => 'id DESC,current_process_status'));
        if ($notNeedlist)
            return $model;
        else
            return CHtml::listData($model, 'id', 'id_with_status');
    }

    public static function getFilesForProfit() {

        //Code added by mereena on 11/12/2014 for approval by value configuration
        $file_string = "";
        $approval_type_order = "";
        $approval_type_quote = "";
        if (isset(Yii::app()->user->havePermissionOn['approval_type_order'])) {
            $approval_type_order = Yii::app()->user->havePermissionOn['approval_type_order'];
        }
        if (isset(Yii::app()->user->havePermissionOn['approval_type_quote'])) {
            $approval_type_quote = Yii::app()->user->havePermissionOn['approval_type_quote'];
        }

        if (isset(Yii::app()->user->havePermissionOn['sop_profit_calc_change_pricing_group']) && Yii::app()->user->havePermissionOn['sop_profit_calc_change_pricing_group'] > 0) {
            $file_string = '"Q", "O", "PD", "D", "PI", "I", "C"';
        } else {
            if ($approval_type_order == 'sop_order_appr_by_val' || $approval_type_order == 'sop_order_appr_by_pc') {
                $file_string = '"O"';
            }
            if ($approval_type_quote == 'sop_quote_appr_by_val' || $approval_type_quote == 'sop_quote_appr_by_pc') {
                if ($file_string != "")
                    $file_string .= ",";

                $file_string .= '"Q", "PD", "D", "PI", "I", "C"';
            }
        }
        //End code by Mereena
        //echo $file_string;
        $criteria = new cdbCriteria;
        $condition = 'isActive="Y"';
        if ($file_string != "") {
            $condition .= ' AND current_process_status in (' . $file_string . ')';
        }
        if ((int) Yii::app()->user->havePermissionOn['sop_lead_viewall'] == 1 || (int) Yii::app()->user->havePermissionOn['sop_order_viewall'] == 1 || (int) Yii::app()->user->havePermissionOn['sop_delivery_viewall'] == 1 || (int) Yii::app()->user->havePermissionOn['sop_invoice_viewall'] == 1 || (int) Yii::app()->user->havePermissionOn['sop_credit_notes_viewall'] == 1 || Yii::app()->user->authorised_user == 1) {

        } else {
            $condition .= ' AND lead_owner_id=' . Yii::app()->user->id;
        }

        $model = self::model()->findAll(array('condition' => $condition, 'order' => 'id DESC,current_process_status'));
        return CHtml::listData($model, 'id', 'id_with_status');

        /* $criteria = new cdbCriteria;
          $condition = 'isActive="Y"';
          $current_process_status = implode('","', array('Q', 'O', 'PD', 'D', 'PI', 'I', 'C'));
          if ($current_process_status != null) {
          if (is_array($current_process_status))
          $current_process_status = implode('","', $current_process_status);
          $condition.=' AND current_process_status in ("' . $current_process_status . '")';
          }
          $model = self::model()->findAll(array('condition' => $condition, 'order' => 'id DESC,current_process_status'));

          return CHtml::listData($model, 'id', 'id_with_status'); */
    }

    public function getId_with_status() {
        return $this->id . ':' . $this->status;
    }

    public function getId_with_title() {
        return $this->id . ' - ' . $this->file_title;
    }

    /* used in lead-getlist
     * modified jisha on 17-9-18
     */

    public function getStatus() {
        switch ($this->current_process_status) {
            case 'L':
                return 'Lead';
                break;
            case 'Q':
                return 'Quote';
                break;
            case 'O':
            case 'P_O':
                return 'Order';
                break;
            case 'D':
                return 'Delivery';
                break;
            case 'PD':
                return 'Delivery(P)';
                break;
            case 'I':
            case 'P_I':
                return 'Invoice';
                break;
            case 'PI':
            case 'P_PI':
                return 'Invoice(P)';
                break;
            case 'P_R':
                return 'Requisition';
                break;
            case 'P_E':
                return 'Enquiry';
                break;
            case 'M_R':
                return 'Receipt';
                break;
            case 'P_MR':
                return 'Receipt(P)';
                break;
            case 'P_C':
                return 'DN';
                break;
            case 'C':
                return 'CN';
                break;
            case 'B_M':
                return 'Bill of Mtl';
                break;
            case 'PR_O':
                return 'Prod.Order';
                break;
            case 'G_I':
                return 'Goods Issue';
                break;
            case 'G_R':
                return 'Goods Receipt';
                break;
        }
    }

    public function priority($status) {
        switch ($status) {
            case 'L':
                return 1;
                break;
            case 'Q':
                return 2;
                break;
            case 'O':
                return 3;
                break;
            /* case 'PD':
              return 4;
              break; */
            case 'PD':
            case 'D':
            case 'PI':
            case 'I':
            case 'C': /* because there are siituation after partial invoicing then to invoice */
                return 4;
                break;
            /* case 'I':
              return 7;
              break;
              case 'C':
              return 8;
              break; */
            default:
                return 0;
                break;
        }
    }

    public function getGrand_total_bc() { //used for getting the grand total from quotation or order based on the status
        switch ($this->current_process_status) {
            case 'Q':
                return ($this->quote->grand_total + $this->quote->adTotal - $this->quote->dedTotal) * $this->quote->exchange_rate;
                break;
            case 'O':
                return ($this->order->grand_total + $this->order->adTotal - $this->order->dedTotal) * $this->order->exchange_rate;
                break;
        }
    }

    public function getGrand_total_own_currency() { //used for getting the grand total from quotation or order based on the status
        switch ($this->current_process_status) {
            case 'Q':
                return ($this->quote->grand_total + $this->quote->adTotal - $this->quote->dedTotal);
                break;
            case 'O':
                return ($this->order->grand_total + $this->order->adTotal - $this->order->dedTotal);
                break;
        }
    }

    public function reCalculateSellAndCostValues() {
        $modelProfitCalc = new ProfitCalc;
        $profitCal = $modelProfitCalc->profitCalculation($this->id);
        $modelProfit = ProfitCalc::model()->findByAttributes(array('file_id' => $this->id));
        if (count($modelProfit) == 0)
            $modelProfit = new ProfitCalc;
        $modelProfit->crm_user_id = Yii::app()->user->id;
        $modelProfit->file_id = $this->id;
        $modelProfit->netsellingvalue = $profitCal['netsellingvalue'];
        $modelProfit->netcostvalue = $profitCal['netcostvalue'];
        $modelProfit->save();
        $modelProfit->refresh();
        $default_internal_pricing_group = PricingGroup::getDefaultPricingGroup("I")->id;
        foreach ($profitCal['model']->items as $eachItem) {
            $profitCalcItem = new ProfitCalcItem;
            $cond = 'price_group_id=' . $default_internal_pricing_group .
                    ' AND pricing_product_class_id=' . (int) $eachItem->product->product_class_id .
                    ' AND store_id=' . $eachItem->product->id;
            $costrate = PriceGroupProductRelation::model()->find($cond)->rate;
            $profitCalcItem->profit_calc_id = $modelProfit->id;
            $profitCalcItem->product_id = $eachItem->product_id;
            $profitCalcItem->cost_rate = $costrate;
            $profitCalcItem->save(false);
        }
        return $modelProfit;
    }

    public function getNetSellValue() {
        if (count($this->profitCalc0) == 0 || count($this->profitCalc0->items) == 0) {
            $modelProfit = $this->reCalculateSellAndCostValues();
            return $modelProfit->netsellingvalue;
        } else {
            return $this->profitCalc0->netsellingvalue;
        }
    }

    public function getNetCostValue() {
        if (count($this->profitCalc0) == 0 || count($this->profitCalc0->items) == 0) {
            $modelProfit = $this->reCalculateSellAndCostValues();
            $netcostvalue = $modelProfit->netcostvalue;
        } else {
            $netcostvalue = $this->profitCalc0->netcostvalue;
        }
        if ($this->current_process_status == "I") {
            foreach ($this->invoices as $eachInvoice) {
                if ($eachInvoice->paid == "Y") {
                    foreach ($eachInvoice->processedInvoice as $eachProcessedInvoice) {

                        if ($eachProcessedInvoice->recpt->received_date == $eachInvoice->invoice_date) {
                            $netcostvalue -= ($netcostvalue * $eachProcessedInvoice->received_amt / $this->netSellValue) * 0.01;
                        }
                    }
                } else {
                    $netcostvalue += $netcostvalue * ((strtotime(date('Y-m-d')) - strtotime($eachProcessedInvoice->invoice_date)) % 30);
                }
            }
        }
        return $netcostvalue;
    }

    public function getProfit() {
        $profit = $this->netSellValue - $this->netCostValue;
        return $profit;
    }

    public function getMinProfit() {

        if ($this->netCostValue <= 100000)
            $min_sales_profit_percentage = 0.15;
        else if ($this->netCostValue > 100000 && $this->netCostValue <= 300000)
            $min_sales_profit_percentage = 0.13;
        else // > 100000
            $min_sales_profit_percentage = 0.10;
        return $min_sales_profit_percentage;
    }

    public function getProfitExp() {

        $min_sales_profit = $this->netCostValue * $this->minProfit;
        return $min_sales_profit;
    }

    public function getAmountForIncentiveCalc() {
        $balance_to_calculate_sales_incentive = $this->profit - $this->profitExp;
        return $balance_to_calculate_sales_incentive;
    }

    public function getIncentive() {
        $incentiveAmnt = $this->amountForIncentiveCalc * 0.15;
        return $incentiveAmnt;
    }

    public function leadownersearch($searchterm = null) {
        print_r("enteret");
        die();
        $criteria = new CDbCriteria;
        $searchterm = empty($searchterm) ? trim(Yii::app()->request->getParam('lead_search')) : $searchterm;
        $criteria->with = array('leadowner', 'company');
        if (!empty($searchterm)) {
            $criteria->addCondition('   t.id like "%' . $searchterm . '%" OR
                                        t.file_id like "%' . $searchterm . '%"
                                        company.company_name like "%' . $searchterm . '%" OR
                                        leadowner.first_name like "%' . $searchterm . '%" OR
                                        leadowner.last_name like "%' . $searchterm . '%"
                                            ', 'AND');
        }

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

    public function openPoItems() {
        $product_id = (int) Yii::app()->request->getParam('product_id');

        $sql = 'SELECT
            order0.file_id,
            order_items.purchasing_order_id,
            st.part_number,
            order_items.quantity as ord_quantity,
            order0.order_date,
            order0.required_date,
            order0.expected_delivery_date,vendor.company_name as vendor_name
            FROM {{purchasing_order_item}} as order_items
            JOIN
            {{purchasing_order}} as order0 ON order0.id=order_items.purchasing_order_id
            JOIN
            {{store}} as st ON st.id=order_items.product_id
            JOIN
            {{company}} as vendor ON order0.vendor_id=vendor.id
            WHERE order_items.product_id=' . $product_id . '
            group by order_items.purchasing_order_id';
        $records = Yii::app()->db->createCommand($sql)->queryAll();
        $query = "DROP TABLE if exists open_stock_details; CREATE TEMPORARY TABLE open_stock_details (
                    part_number VARCHAR(15),
                    order_date VARCHAR(15),
                    required_date VARCHAR(15),
                    expected_delivery_date VARCHAR(15),
                    file_number  VARCHAR(255) ,
                    ord_quantity INTEGER,
                    qc_quantity INTEGER,
                    vendor_name  VARCHAR(255)
                )  Engine = Memory;";
        foreach ($records as $i => $eachRecord) {
            $sql0 = 'SELECT
            sum(mritem.qcchecked_quantity)
            FROM {{purchasing_material_receipt_item}} as mritem
            JOIN
            {{purchasing_material_receipt}} as mr

                 WHERE mr.po_id=' . $eachRecord['purchasing_order_id'] . ' AND product_id=' . $product_id . ' and mritem.purchasing_materialreceipt_id=mr.id
            group by mr.po_id';
            $qc_quantity = Yii::app()->db->createCommand($sql0)->queryScalar();
            if ($eachRecord['ord_quantity'] - $qc_quantity > 0)
                $query .= "INSERT INTO open_stock_details VALUES(
                                    '" . $eachRecord['part_number'] . "',
                                    '" . $eachRecord['order_date'] . "',
                                    '" . $eachRecord['required_date'] . "',
                                    '" . $eachRecord['expected_delivery_date'] . "',
                                    '" . $eachRecord['file_id'] . '/' . $eachRecord['purchasing_order_id'] . "',
                                    '" . $eachRecord['ord_quantity'] . "',
                                    '" . $qc_quantity . "',
                                    '" . $eachRecord['vendor_name'] . "'
);";
        }
        Yii::app()->db->createCommand($query)->execute();
        $query = "select * from open_stock_details";
        return new CSqlDataProvider($query, array(
            //'totalItemCount' => 10,
            /* 'sort' => array(
              'attributes' => array(
              'id', 'username', 'email',
              ),
              ), */
            'pagination' => false
        ));
    }

    public function getColor_of_row() {
        return ($this->is_approved == "N" ? "colorRed" : "white");
    }

    public static function getFilesForIncentive() {
        $criteria = new cdbCriteria;
        $condition = "is_approved='Y' AND current_process_status IN('I','P_I')";
        $model = new MasterFile();

        $model = $model->findAll(array('condition' => $condition, 'order' => 'id DESC'));
        return CHtml::listData($model, 'id', 'id_with_status');
    }

    public static function getMasterFiles() {
        $condition = 'isActive="Y"';
        $model = self::model()->findAll(array('condition' => $condition, 'order' => 'id DESC'));
        return CHtml::listData($model, 'id', 'id');
    }

    public static function getMasterFilesCustomer($customer_id) {
        $condition = 'isActive="Y" AND customer_id =' . $customer_id;
        $model = self::model()->findAll(array('condition' => $condition, 'order' => 'id DESC'));
        return CHtml::listData($model, 'id', 'id_with_title');
    }

//code added sarika on 26-08-2022
    public function getdeliveredQuantity() {
        if ($this->id) {
            $sql = "SELECT SUM(d_item.picked_qty)  FROM erp_order as t
              LEFT JOIN erp_master_file as mf
              ON mf.id=t.file_id
              LEFT JOIN erp_delivery_note as d_note
              ON  mf.id=d_note.file_id
              LEFT JOIN erp_delivery_item as d_item
              ON d_item.delivery_note_id = d_note.id
              WHERE t.file_id=$this->id
              AND d_note.status!='CN' ";
            $deliveredQuantity = Yii::app()->db->createCommand($sql)->queryScalar();
            return (float) $deliveredQuantity;
        }
    }

    public function getinvoicedQuantity() {
        if ($this->id) {
            $sql = "SELECT SUM(d_item.quantity)  FROM erp_order as t
              LEFT JOIN erp_master_file as mf
              ON mf.id=t.file_id
              LEFT JOIN erp_invoice as d_note
              ON  mf.id=d_note.file_id
              LEFT JOIN erp_invoice_item as d_item
              ON d_item.invoice_id = d_note.id
              WHERE t.file_id=$this->id ";
            $invoicedQuantity = Yii::app()->db->createCommand($sql)->queryScalar();
            return (float) $invoicedQuantity;
        }
    }

}
