<?php

/**
 * This is the model class for table "{{order_details}}".
 *
 * The followings are the available columns in table '{{order_details}}':
 * @property integer $id
 * @property integer $order_id
 * @property integer $category_id
 * @property integer $region_id
 * @property integer $product_id
 * @property string $model
 * @property string $base_code
 * @property integer $quantity_ordered
 * @property integer $quantity_confirmed
 * @property integer $quantity_shipped
 * @property integer $quantity_backlog
 * @property integer $created_by
 * @property string $created_date_time
 * @property integer $updated_by
 * @property string $updated_date_time
 */
class OrderDetails extends CActiveRecord {

    /**
     * @return string the associated database table name
     */
    public $amount_ordered, $amount_confirmed, $amount_shipped;

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

    /**
     * @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('order_id, category_id, product_id, model, quantity_ordered', 'required'),
            array('order_id, category_id, product_id, quantity_ordered,quantity_confirmed, created_by, updated_by,region_id', 'numerical', 'integerOnly' => true),
            array('model, base_code', 'length', 'max' => 255),
            // The following rule is used by search().
            // @todo Please remove those attributes that should not be searched.
            array('id, order_id, category_id, product_id, model, base_code, quantity_ordered, quantity_confirmed, quantity_shipped, quantity_backlog, created_by, created_date_time, updated_by, updated_date_time', '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(
            'parent_det' => array(self::BELONGS_TO, 'Order', 'order_id'),
            'category_det' => array(self::BELONGS_TO, 'MasProductCategory', 'category_id'),
            'product_det' => array(self::BELONGS_TO, 'Inventory', 'product_id'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
        return array(
            'id' => 'ID',
            'order_id' => 'Order',
            'category_id' => 'Category',
            'product_id' => 'Product',
            'model' => 'Model',
            'base_code' => 'Base Code',
            'quantity_ordered' => 'Quantity Ordered',
            'quantity_confirmed' => 'Quantity Confirmed',
            'quantity_shipped' => 'Quantity Shipped',
            'quantity_backlog' => 'Quantity Backlog',
            'created_by' => 'Created By',
            'created_date_time' => 'Created Date Time',
            'updated_by' => 'Updated By',
            'updated_date_time' => 'Updated Date Time',
        );
    }

    /**
     * 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('order_id', $this->order_id);
        $criteria->compare('category_id', $this->category_id);
        $criteria->compare('product_id', $this->product_id);
        $criteria->compare('model', $this->model, true);
        $criteria->compare('base_code', $this->base_code, true);
        $criteria->compare('quantity_ordered', $this->quantity_ordered);
        $criteria->compare('quantity_confirmed', $this->quantity_confirmed);
        $criteria->compare('quantity_shipped', $this->quantity_shipped);
        $criteria->compare('quantity_backlog', $this->quantity_backlog);
        $criteria->compare('created_by', $this->created_by);
        $criteria->compare('created_date_time', $this->created_date_time, true);
        $criteria->compare('updated_by', $this->updated_by);
        $criteria->compare('updated_date_time', $this->updated_date_time, 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 OrderDetails the static model class
     */
    public static function model($className = __CLASS__) {
        return parent::model($className);
    }

    /* Function for calculating shipping confirmation- distribution of qty between distributers */

    public static function updateShippingConfirmation($month, $year) {

        if ($year > 0 && $month > 0) {
            $mod_his = SupplyConfirmation::model()->find(array('condition' => 'month = "' . $month . '" and year = "' . $year . '"'));
            if (!empty($mod_his)) {
                $model_inventory = Inventory::model()->findAll();
                $total_bklog = 0;
                foreach ($model_inventory as $i => $row) {
                    $new_goods_start_date = (int) $row['goods_in_year'] . '-' . (int) $row['goods_in_month'] . '-01';
                    $new_goods_end_date = (int) $row['goods_in_end_year'] . '-' . (int) $row['goods_in_end_month'] . '-01';
                    $goodsmonth = date('m', strtotime($new_goods_end_date . ' -1 months'));
                    $goodsyear = date('Y', strtotime($new_goods_end_date . ' -1 months'));
                    $totordersum = (int) OrderDetails::model()->find(array('select' => 'SUM(quantity_ordered) AS quantity_ordered',
                                'condition' => 't.order_date >= :startDate AND t.order_date <= :endDate AND product_id = :productId AND quantity_ordered > 0 AND is_done = "N"',
                                'params' => array(
                                    ':startDate' => $new_goods_start_date,
                                    ':endDate' => $new_goods_end_date,
                                    ':productId' => (int) $row['id']
                        )))->quantity_ordered;
                    $mod_order_details = OrderDetails::model()->findAll(array(
                        'condition' => 't.order_date >= :startDate AND t.order_date <= :endDate AND product_id = :productId AND quantity_ordered > 0 AND is_done = "N"',
                        'params' => array(
                            ':startDate' => $new_goods_start_date,
                            ':endDate' => $new_goods_end_date,
                            ':productId' => (int) $row['id']
                        ),
                        'order' => 'quantity_ordered ASC'
                    ));
                    /* if($goodsmonth== $month && $goodsyear == $year){                    
                      $totordersum = (int)OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered','condition'=>'parent_det.month='.$goodsmonth.' AND parent_det.year='.$goodsyear.' AND product_id='.(int)$row['id']))->quantity_ordered;
                      $mod_order_details= OrderDetails::model()->with('parent_det')->findAll(array('condition'=>'parent_det.month='.$goodsmonth.' AND parent_det.year='.$goodsyear.' AND product_id='.(int)$row['id'].' AND quantity_ordered>0 AND is_done="N"','order'=>'quantity_ordered asc'));
                      }else{
                      $totordersum = (int)OrderDetails::model()->with('parent_det')->find(array('select'=>'SUM(quantity_ordered) AS quantity_ordered','condition'=>'parent_det.month='.$goodsmonth.' AND parent_det.year='.$goodsyear.' AND product_id='.(int)$row['id']))->quantity_ordered;
                      $mod_order_details= OrderDetails::model()->with('parent_det')->findAll(array('condition'=>'parent_det.month='.$goodsmonth.' AND parent_det.year='.$goodsyear.' AND product_id='.(int)$row['id'].' AND quantity_ordered>0 AND is_done="N"','order'=>'quantity_ordered asc'));
                     */
                    $total_inv = (int) InventoryUploadDetails::model()->with('parent_det')->find([
                                'select' => 'SUM(total_quantity) AS total_quantity',
                                'condition' => 'parent_det.month=' . $month . ' AND parent_det.year=' . $year . ' AND product_id=' . (int) $row['id'] . ' AND parent_det.type="Inventory"'
                            ])->total_quantity;

                    $total_goods_in = (int) InventoryUploadDetails::model()->with('parent_det')->find([
                                'select' => 'SUM(total_quantity) AS total_quantity',
                                'condition' => '(parent_det.month=' . $month . ' AND parent_det.year=' . $year . ') AND parent_det.type="GoodsIn" AND product_id=' . (int) $row['id']
                            ])->total_quantity;

                    $total_confirmed = (int) OrderDetails::model()->with('parent_det')->find([
                                'select' => 'SUM(quantity_confirmed) AS quantity_confirmed',
                                'condition' => '(parent_det.month=' . $month . ' AND parent_det.year=' . $year . ') AND product_id=' . (int) $row['id']
                            ])->quantity_confirmed;

                    $unshipped_sales = (int) UploadUnshippedDetails::model()->with('parent_det')->find([
                                'condition' => 'parent_det.month=' . $month . ' AND parent_det.year=' . $year . ' AND product_id=' . (int) $row['id']
                            ])->quantity;

                    $total_bklog = (int) UploadBacklogDetails::model()->with('parent_det')->find([
                                'condition' => 'parent_det.month=' . $month . ' AND parent_det.year=' . $year . ' AND product_id=' . (int) $row['id']
                            ])->quantity;
                    $totreceived = $total_inv + $total_goods_in - ($unshipped_sales + $total_bklog); //+$totordersum
                    $modSupplyDetails = new SupplyDetails();
                    $modSupplyDetails->supply_confirm_id = (int) $mod_his->id;
                    $modSupplyDetails->product_id = (int) $row['id'];
                    $modSupplyDetails->month = (int) $goodsmonth;
                    $modSupplyDetails->year = (int) $goodsyear;
                    $modSupplyDetails->total_order = (int) $totordersum;
                    $modSupplyDetails->goods_in = (int) $total_goods_in;
                    $modSupplyDetails->inventory = (int) $total_inv;
                    $modSupplyDetails->unshipped = (int) $unshipped_sales;
                    $modSupplyDetails->backlog = (int) $total_bklog;
                    $modSupplyDetails->created_by = (int) Yii::app()->user->id;
                    $modSupplyDetails->created_date_time = date('Y-m-d H:i:s');
                    $modSupplyDetails->save(false);

                    //($totreceived<0)?$totreceived=0:'';
                    $bal_rec = $totreceived;
                    $bal_ord = $totordersum;
                    if (!empty($mod_order_details)) {
                        foreach ($mod_order_details as $eachorderdetail) {
                            $ordersum = (int) $eachorderdetail['quantity_ordered'];
                            $modelordDet = new OrderDetails();
                            $modelordDet = $modelordDet->findByPk((int) $eachorderdetail['id']);
                            if ($totordersum <= $totreceived) {
                                $dis_sum = (int) $ordersum;
                            } else {
                                if ($totreceived < 0) {
                                    $dis_sum = 0;
                                } else {
                                    $bal_ord = $bal_ord - $ordersum;
                                    if ($totordersum > 0) {
                                        if ($bal_ord == 0) {
                                            if ($ordersum >= $bal_rec) {
                                                $dis_sum = $bal_rec > 0 ? $bal_rec : 0;
                                            } else {
                                                $dis_sum = $ordersum;
                                            }
                                        } else {
                                            $dis_sum = round(($totreceived / $totordersum) * $ordersum);
                                            $dis_sum = ($dis_sum > $bal_rec) ? $bal_rec : $dis_sum;
                                        }
                                    } else
                                        $dis_sum = 0;
                                }
                            }
                            $bal_rec = $bal_rec - $dis_sum;
                            $modelordDet->quantity_confirmed = (int) $dis_sum;
                            $modelordDet->supply_confirm_id = (int) $mod_his->id;
                            $modelordDet->is_done = 'Y';
                            $modelordDet->save(false);
                        }
                    }
                }
                return 1;
            } else {
                return 0;
            }
        } else {
            return 0;
        }
    }

    public static function updateShippingConfirmationByProduct($month, $year, $product_id) {
        if ($year > 0 && $month > 0 && (int) $product_id > 0) {
            $mod_his = SupplyConfirmation::model()->find(array('condition' => 'month = "' . $month . '" and year = "' . $year . '"'));
            if (!empty($mod_his)) {
                $row = Inventory::model()->findByPK((int) $product_id);
                $new_goods_start_date = (int) $row['goods_in_year'] . '-' . (int) $row['goods_in_month'] . '-01';
                $new_goods_end_date = (int) $row['goods_in_end_year'] . '-' . (int) $row['goods_in_end_month'] . '-01';
                $goodsmonth = date('Y', strtotime($new_goods_end_date, ' -1 months'));
                $goodsyear = date('Y', strtotime($new_goods_end_date, ' -1 months'));
                

                //$totordersum = (int) OrderDetails::model()->with('parent_det')->find(array('select' => 'SUM(quantity_ordered) AS quantity_ordered', 'condition' => 'parent_det.month=' . $goodsmonth . ' AND parent_det.year=' . $goodsyear . ' AND product_id=' . (int) $row['id']))->quantity_ordered;
                //$mod_order_details = OrderDetails::model()->with('parent_det')->findAll(array('condition' => 'parent_det.month=' . $goodsmonth . ' AND parent_det.year=' . $goodsyear . ' AND product_id=' . (int) $row['id'] . ' AND quantity_ordered>0 AND is_done="N"', 'order' => 'quantity_ordered asc'));
                $totordersum = (int) OrderDetails::model()->with('parent_det')->find(array('select' => 'SUM(quantity_ordered) AS quantity_ordered',
                            'condition' => 'order_date >= :startDate AND order_date <= :endDate AND product_id = :productId AND quantity_ordered > 0 AND is_done = "N"',
                            'params' => array(
                                ':startDate' => $new_goods_start_date,
                                ':endDate' => date('Y-m-d', strtotime('+1 month', strtotime($new_goods_end_date))),
                                ':productId' => (int) $row['id']
                    )))->quantity_ordered;
                $mod_order_details = OrderDetails::model()->findAll(array(
                    'condition' => 'order_date >= :startDate AND order_date <= :endDate AND product_id = :productId AND quantity_ordered > 0 AND is_done = "N"',
                    'params' => array(
                        ':startDate' => $new_goods_start_date,
                        ':endDate' => date('Y-m-d', strtotime('+1 month', strtotime($new_goods_end_date))),
                        ':productId' => (int) $row['id']
                    ),
                    'order' => 'order_date,quantity_ordered ASC'
                ));    
                //$totordersum = (int) OrderDetails::model()->with('parent_det')->find(array('select' => 'SUM(quantity_ordered) AS quantity_ordered', 'condition' => 'parent_det.month=' . $month . ' AND parent_det.year=' . $year . ' AND product_id=' . (int) $row['id']))->quantity_ordered;
                $total_inv = (int) InventoryUploadDetails::model()->with('parent_det')->find(array('select' => 'SUM(total_quantity) AS total_quantity', 'condition' => 'parent_det.month=' . $month . ' AND parent_det.year=' . $year . ' AND product_id=' . (int) $row['id'] . ' AND parent_det.type="Inventory"'))->total_quantity;
                $total_goods_in = (int) InventoryUploadDetails::model()->with('parent_det')->find(array('select' => 'SUM(total_quantity) AS total_quantity', 'condition' => '(parent_det.month=' . $month . ' AND parent_det.year=' . $year . ') AND parent_det.type="GoodsIn" AND product_id=' . (int) $row['id']))->total_quantity;
                $total_confirmed = (int) OrderDetails::model()->with('parent_det')->find(array('select' => 'SUM(quantity_confirmed) AS quantity_confirmed', 'condition' => '(parent_det.month=' . $month . ' AND parent_det.year=' . $year . ') AND product_id=' . (int) $row['id']))->quantity_confirmed;
                $unshipped_sales = (int) UploadUnshippedDetails::model()->with('parent_det')->find(array('condition' => 'parent_det.month=' . $month . ' AND parent_det.year=' . $year . ' AND product_id=' . (int) $row['id']))->quantity;
                $total_bklog = (int) UploadBacklogDetails::model()->with('parent_det')->find(array('condition' => 'parent_det.month=' . $month . ' AND parent_det.year=' . $year . ' AND product_id=' . (int) $row['id']))->quantity;
                $totreceived = $total_inv + $total_goods_in - ($unshipped_sales + $total_bklog); //+$totordersum
                //($totreceived<0)?$totreceived=0:'';
                $modSupplyDetails = new SupplyDetails();
                $modSupplyDetails->supply_confirm_id = (int) $mod_his->id;
                $modSupplyDetails->product_id = (int) $row['id'];
                $modSupplyDetails->goods_in = (int) $total_goods_in;
                $modSupplyDetails->inventory = (int) $total_inv;
                $modSupplyDetails->unshipped = (int) $unshipped_sales;
                $modSupplyDetails->backlog = (int) $total_bklog;
                $modSupplyDetails->created_by = (int) Yii::app()->user->id;
                $modSupplyDetails->created_date_time = date('Y-m-d H:i:s');
                $modSupplyDetails->save(false);

                $bal_rec = $totreceived;
                $bal_ord = $totordersum;

                foreach ($mod_order_details as $eachorderdetail) {
                    $ordersum = (int) $eachorderdetail['quantity_ordered'];
                    $modelordDet = new OrderDetails();
                    $modelordDet = $modelordDet->findByPk((int) $eachorderdetail['id']);
                    if ($totordersum <= $totreceived) {
                        $dis_sum = (int) $ordersum;
                    } else {
                        $bal_ord = $bal_ord - $ordersum;
                        if ($totordersum > 0) {
                            if ($bal_ord == 0) {
                                if ($ordersum >= $bal_rec) {
                                    if ($bal_rec > 0)
                                        $dis_sum = $bal_rec;
                                    else
                                        $dis_sum = 0;
                                } else {
                                    $dis_sum = $ordersum;
                                }
                            } else {
                                $dis_sum = round(($totreceived / $totordersum) * $ordersum);
                                if ($dis_sum > $bal_rec) {
                                    $dis_sum = $bal_rec;
                                }
                            }
                        } else
                            $dis_sum = 0;
                    }
                    $bal_rec = $bal_rec - $dis_sum;
                    $modelordDet->quantity_confirmed = (int) $dis_sum;
                    $modelordDet->supply_confirm_id = (int) $mod_his->id;
                    $modelordDet->save(false);
                }
                return 1;
            } else {
                return 0;
            }
        } else {
            return 0;
        }
    }
}