<?php
class Table_Orders
{
    protected $_table = null;

    public function __construct()
    {
        $this->_table = new DbTable_Orders();
    }

    public function getAdapter()
    {
        return $this->_table->getAdapter();
    }

    public static function getInstance()
    {
        return new Table_Orders();
    }

    /**
     * @param $row
     * @return mixed
     */
    public function add($row)
    {
        return $this->_table->insert($row);
    }


    /**
     * @param $row
     * @param $value
     * @param string $field
     * @return mixed
     */
    public function update($row, $value, $field = "order_id")
    {
        $where = $this->_table->getAdapter()->quoteInto("{$field}= ?", $value);
        return $this->_table->update($row, $where);
    }
    
    /**
     * @param array $row
     * @param array $values
     * @param string $field
     */
    public function updateIn($row, $values, $field = "order_code")
    {
    	$where = $this->_table->getAdapter()->quoteInto("{$field} IN(?)", $values);
    	return $this->_table->update($row, $where);
    }

    /**
     * @param $value
     * @param string $field
     * @return mixed
     */
    public function delete($value, $field = "order_id")
    {
        $where = $this->_table->getAdapter()->quoteInto("{$field}= ?", $value);
        return $this->_table->delete($where);
    }

    /**
     * @param $value
     * @param string $field
     * @param string $colums
     * @return mixed
     */
    public function getByField($value, $field = 'order_id', $colums = "*")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $colums);
        $select->where("{$field} = ?", $value);
        return $this->_table->getAdapter()->fetchRow($select);
    }

    public function getByFieldUpdate($value, $field = 'order_id', $colums = "*"){
        $db = $this->_table->getAdapter();
        $query = $db->quoteInto("SELECT {$colums} FROM orders WHERE {$field} = ? FOR UPDATE",$value);
        return $db->fetchRow($query);
        /*        $select = $this->_table->getAdapter()->select();
                $table = $this->_table->info('name');
                $select->from($table, $colums);
                $select->where("{$field} = ?", $value);
                return $this->_table->getAdapter()->fetchRow($select); */
    }

    public function getAll()
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, "*");
        return $this->_table->getAdapter()->fetchAll($select);
    }

    /**
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     * @return array|string
     */
    public function getByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $type);
        $select->where("1 =?", 1);
        /*CONDITION_START*/

        if (isset($condition["order_code"]) && $condition["order_code"] != "") {
            $select->where("orders.order_code in(?)", $condition["order_code"]);
        }
        if(isset($condition["new_reference_no"])&&$condition['new_reference_no']!=""){
            $select->where("(orders.order_code = '".$condition['new_reference_no']."') or (reference_no = ?)",$condition['new_reference_no']);
        }
        if (isset($condition["not_order_code"]) && $condition["not_order_code"] != "") {
        	$select->where("orders.order_code != ?", $condition["not_order_code"]);
        }
        if (isset($condition["customer_id"]) && $condition["customer_id"] != "") {
            $select->where("customer_id = ?", $condition["customer_id"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
            $select->where("customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["warehouse_id"]) && $condition["warehouse_id"] != "") {
            $select->where("warehouse_id = ?", $condition["warehouse_id"]);
        }
        if (isset($condition["order_type"]) && $condition["order_type"] != "") {
            $select->where("order_type = ?", $condition["order_type"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("sm_code = ?", $condition["sm_code"]);
        }
        if (isset($condition["order_status"]) && $condition["order_status"] != "") {
            $select->where("order_status = ?", $condition["order_status"]);
        }
        if (isset($condition["order_status_arr"]) && $condition["order_status_arr"] != "") {
        	$select->where("order_status in (?)", $condition["order_status_arr"]);
        }
        if (isset($condition["except_order_status_delete"])) {
            $select->where("order_status > ?", 0);
        }
        if (isset($condition["problem_status"]) && $condition["problem_status"] != "") {
            $select->where("problem_status = ?", $condition["problem_status"]);
        }
        if (isset($condition["underreview_status"]) && $condition["underreview_status"] != "") {
            $select->where("underreview_status = ?", $condition["underreview_status"]);
        }
        if (isset($condition["order_pick_type"]) && $condition["order_pick_type"] != "") {
            $select->where("order_pick_type = ?", $condition["order_pick_type"]);
        }
        if (isset($condition["reference_no"]) && $condition["reference_no"] != "") {
            $select->where("reference_no = ?", $condition["reference_no"]);
        }
        if (isset($condition["picker_id"]) && $condition["picker_id"] != "") {
            $select->where("picker_id = ?", $condition["picker_id"]);
        }
        if (isset($condition["add_time_start"]) && $condition["add_time_start"] != "") {
        	$select->where("add_time >=?", $condition["add_time_start"] . ' 00:00:00');
        }
        
        if (isset($condition["add_time_end"]) && $condition["add_time_end"] != "") {
        	$select->where("add_time <=?", $condition["add_time_end"] . ' 23:59:59');
        }
        if(isset($condition["order_mode_type"]) && $condition["order_mode_type"] != ""){
        	$select->where("order_mode_type = ?", $condition["order_mode_type"]);
        }
        if(isset($condition["sm_code_in"]) && $condition["sm_code_in"] != ""){
        	$select->where("sm_code in (?)", $condition["sm_code_in"]);
        }
        if(isset($condition["ref_tracking_number"]) && $condition["ref_tracking_number"] != ""){
        	$select->where("ref_tracking_number = ?", $condition["ref_tracking_number"]);
        }
        if((isset($condition['ship_time_start'])&&$condition['ship_time_start']!="")||(isset($condition['ship_time_end'])&&$condition['ship_time_end']!="")){
            $select->join("order_operation_time","orders.order_id=order_operation_time.order_id",array());
            if($condition['ship_time_start']!=""){
                $select->where("order_operation_time.ship_time >= ? ",$condition['ship_time_start']." 00:00:00");
            }
            if($condition['ship_time_end']!=""){
                $select->where("order_operation_time.ship_time <= ? ",$condition['ship_time_end']." 23:59:59");
            }
        }
        if( isset($condition["insurance"]) && $condition["insurance"] !== "" ) {
            $select->where("insurance = ?", $condition["insurance"]);
        }
        //echo $select;//exit;
        /*CONDITION_END*/
        if ('count(*)' == $type) {
            return $this->_table->getAdapter()->fetchOne($select);
        } else {
            if (!empty($orderBy)) {
                $select->order($orderBy);
            }
            if ($pageSize > 0 and $page > 0) {
                $start = ($page - 1) * $pageSize;
                $select->limit($pageSize, $start);
            }
            $sql = $select->__toString();
            return $this->_table->getAdapter()->fetchAll($sql);
        }
    }

    /*
     * @已提交订单
     * @处理待下架
     */
    public function getShipmentByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $type);
        $select->where("1 =?", 1);
        /*CONDITION_START*/

        $select->where("order_status = 4");
        if (isset($condition["order_code"]) && $condition["order_code"] != "") {
            $select->where("order_code = ?", $condition["order_code"]);
        }

        if (isset($condition["dateFor"]) && $condition["dateFor"] != "") {
            $select->where("add_time >=?", $condition["dateFor"] . ' 00:00:00');
        }

        if (isset($condition["dateTo"]) && $condition["dateTo"] != "") {
            $select->where("add_time <=?", $condition["dateTo"] . ' 23:59:59');
        }

        if (isset($condition["customer_id"]) && $condition["customer_id"] != "") {
            $select->where("customer_id = ?", $condition["customer_id"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
            $select->where("customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["warehouse_id"]) && $condition["warehouse_id"] != "") {
            $select->where("warehouse_id = ?", $condition["warehouse_id"]);
        }
        if (isset($condition["order_type"]) && $condition["order_type"] != "") {
            $select->where("order_type = ?", $condition["order_type"]);
        }

        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("sm_code = ?", $condition["sm_code"]);
        }

        if (isset($condition["order_pick_type"]) && is_array($condition["order_pick_type"])) {
            $select->where("order_pick_type in(?) ", $condition["order_pick_type"]);
        }
        if (isset($condition["reference_no"]) && $condition["reference_no"] != "") {
            $select->where("reference_no = ?", $condition["reference_no"]);
        }
        /*CONDITION_END*/
        if ('count(*)' == $type) {
            $sql = $select->__toString();
            return $this->_table->getAdapter()->fetchOne($select);
        } else {
            if (!empty($orderBy)) {
                $select->order($orderBy);
            }
            if ($pageSize > 0 and $page > 0) {
                $start = ($page - 1) * $pageSize;
                $select->limit($pageSize, $start);
            }
            $sql = $select->__toString();
            return $this->_table->getAdapter()->fetchAll($sql);
        }
    }



    public function getLeftJoinAllByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $type);
        $select->joinLeft('order_address_book', 'order_address_book.order_id=orders.order_id',array('oab_country_id'));
        $select->joinLeft('order_operation_time', 'order_operation_time.order_id=orders.order_id',array('process_time','pack_time','ship_time'));
        $select->joinLeft('country', 'country.country_id=order_address_book.oab_country_id',array('country_name_en','country_code'));
        $select->where("1 =?", 1);
        /*CONDITION_START*/

        if (isset($condition["order_status"]) && $condition["order_status"] != "") {
            $select->where("order_status = ?", $condition["order_status"]);
        }

        if (isset($condition["order_code"]) && $condition["order_code"] != "") {
            $select->where("order_code = ?", $condition["order_code"]);
        }

        if (isset($condition["reference_no"]) && $condition["reference_no"] != "") {
            $select->where("reference_no = ?", $condition["reference_no"]);
        }

        if (isset($condition["dateFor"]) && $condition["dateFor"] != "") {
            $select->where("add_time >=?", $condition["dateFor"]);
        }
        if (isset($condition["dateTo"]) && $condition["dateTo"] != "") {
            $select->where("add_time <=?", $condition["dateTo"]);
        }

        if (isset($condition["printDateFor"]) && $condition["printDateFor"] != "") {
            $select->where("process_time >=?", $condition["printDateFor"]);
        }
        if (isset($condition["printDateTo"]) && $condition["printDateTo"] != "") {
            $select->where("process_time <=?", $condition["printDateTo"]);
        }

        if (isset($condition["packDateFor"]) && $condition["packDateFor"] != "") {
            $select->where("pack_time >=?", $condition["packDateFor"]);
        }
        if (isset($condition["packDateTo"]) && $condition["packDateTo"] != "") {
            $select->where("pack_time <=?", $condition["packDateTo"]);
        }
         if (isset($condition["confirmDateFor"]) && $condition["confirmDateFor"] != "") {
            $select->where("confirm_time >=?", $condition["confirmDateFor"]);
        }
        if (isset($condition["confirmDateTo"]) && $condition["confirmDateTo"] != "") {
            $select->where("confirm_time <=?", $condition["confirmDateTo"]);
        }
        if (isset($condition["shipDateFor"]) && $condition["shipDateFor"] != "") {
            $select->where("ship_time >=?", $condition["shipDateFor"]);
        }
        if (isset($condition["shipDateTo"]) && $condition["shipDateTo"] != "") {
            $select->where("ship_time <=?", $condition["shipDateTo"]);
        }

        if (isset($condition["customer_id"]) && $condition["customer_id"] != "") {
            $select->where("customer_id = ?", $condition["customer_id"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
            $select->where("customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["warehouse_id"]) && $condition["warehouse_id"] != "") {
            $select->where("warehouse_id = ?", $condition["warehouse_id"]);
        }
        if (isset($condition["order_type"]) && $condition["order_type"] != "") {
            $select->where("order_type = ?", $condition["order_type"]);
        }

        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("sm_code = ?", $condition["sm_code"]);
        }
           if (isset($condition["sm_code_in"]) && $condition["sm_code_in"] != "") {
            $select->where("sm_code in (?)", $condition["sm_code_in"]);
        }

        if (isset($condition["order_pick_type"]) && is_array($condition["order_pick_type"])) {
            $select->where("order_pick_type in(?) ", $condition["order_pick_type"]);
        }
        if (isset($condition["print_no"]) && $condition["print_no"]!=''&&!empty($condition['print_no'])) {
            $select->where("print_no in(?) ", $condition["print_no"]);
        }
        /*CONDITION_END*/
        if ('count(*)' == $type) {
           // echo   $select->__toString();die;
            return $this->_table->getAdapter()->fetchOne($select);
        } else {
            if (!empty($orderBy)) {
                $select->order($orderBy);
            }
            if ($pageSize > 0 and $page > 0) {
                $start = ($page - 1) * $pageSize;
                $select->limit($pageSize, $start);
            }
            $sql = $select->__toString();

            return $this->_table->getAdapter()->fetchAll($sql);
        }
    }
	public function getCustomerOrderStatusGroup($condition, $type,$group)
	{
	    $select = $this->_table->getAdapter()->select();
	    $table = $this->_table->info('name');
	    $select->from($table, $type);
	    $select->where("1 =?", 1);
	
	    if(isset($condition["orders_code"]) && $condition["orders_code"] != ""){
	        $select->where("orders_code = ?",$condition["orders_code"]);
	    }
	    if(isset($condition["customer_id"]) && $condition["customer_id"] != ""){
	        $select->where("customer_id = ?",$condition["customer_id"]);
	    }
	    if(isset($condition["warehouse_id"]) && $condition["warehouse_id"] != ""){
	        $select->where("warehouse_id = ?",$condition["warehouse_id"]);
	    }
	    if(isset($condition["shipping_method"]) && $condition["shipping_method"] != ""){
	        $select->where("shipping_method = ?",$condition["shipping_method"]);
	    }
	
	    if(isset($condition["problem_status"]) && $condition["problem_status"] != ""){
	        $select->where("problem_status = ?",$condition["problem_status"]);
	    }
	    if(isset($condition["underreview_status"]) && $condition["underreview_status"] != ""){
	        $select->where("underreview_status = ?",$condition["underreview_status"]);
	    }
	    
	    if(isset($condition["customer_order_code"]) && $condition["customer_order_code"] != ""){
	        $select->where("customer_order_code = ?",$condition["customer_order_code"]);
	    }
	    if(isset($condition["add_time_start"]) && $condition["add_time_start"] != ""){
	    	$select->where("add_time >= ?",$condition["add_time_start"]);
	    }
	    if(isset($condition["add_time_end"]) && $condition["add_time_end"] != ""){
	    	$select->where("add_time <= ?",$condition["add_time_end"]);
	    }
	    $select->group($group);
	
	    $sql = $select->__toString();
// 	    echo $sql;exit;
	    $result = $this->_table->getAdapter()->fetchAll($sql);
	
	    return $result;
	}
	
	/**
	 * 计算订单的重量
	 * @author solar
	 * @param string $order_code
	 * @return number
	 */
	public function calculateWeight($order_code) {
		$select = $this->_table->getAdapter()->select();
		$select->from('order_product as op', array('sum(product_weight*op_quantity) as weight'));
		$select->joinLeft('product as p', 'op.product_id=p.product_id');
		$select->where('op.order_code=?', $order_code);
		return $this->_table->getAdapter()->fetchOne($select);
	}
	/**
	 * 仓库状态统计
	 * @author solar
	 * @param int $customer_id
	 * @param int $model_type
	 * @param int $warehouse_id
	 */
	public function stat($customer_id, $model_type, $warehouse_id) {
		$select = $this->_table->getAdapter()->select();
		$table = $this->_table->info('name');
		$select->from($table, 'order_status, count(*) as num');
		$select->where('customer_id=?', $customer_id);
		$select->where("order_mode_type=?", $model_type);
		if($warehouse_id!=0) $select->where('warehouse_id=?', $warehouse_id);
		$select->group('order_status');
		return $this->_table->getAdapter()->fetchAll($select);
	}

    /**
     * 统计订单的HsCode数量
     * @author solar
     * @param string $order_code
     * @return int
     */
    public function countHsCode($order_code) {
        $select = $this->_table->getAdapter()->select();
        $select->from('order_product as op', array('count(distinct p.hs_code) as num'));
        $select->joinLeft('product as p', 'op.product_id=p.product_id', '');
        $select->where('op.order_code=?', $order_code);
        return $this->_table->getAdapter()->fetchOne($select);
    }
	
	/**
	 * @author colin-yang
	 * @todo 更加条件获取一行
	 */
	public function getRowByCondition($condition = array(),$type = '*'){
		$select = $this->_table->getAdapter()->select();
		$table = $this->_table->info('name');
		$select->from($table, $type);
		$select->where("1 =?", 1);
		/*CONDITION_START*/
		
		if (isset($condition["order_code"]) && $condition["order_code"] != "") {
			$select->where("order_code = ?", $condition["order_code"]);
		}
		if (isset($condition["customer_id"]) && $condition["customer_id"] != "") {
			$select->where("customer_id = ?", $condition["customer_id"]);
		}
		if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
			$select->where("customer_code = ?", $condition["customer_code"]);
		}
		if (isset($condition["reference_no"]) && $condition["reference_no"] != "") {
			$select->where("reference_no = ?", $condition["reference_no"]);
		}
		$select->limit(1);
		return $this->_table->getAdapter()->fetchRow($select);
	}
		
    /**
     * 获取查询sql
     */
    public function getSql($condition = array()){
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, '*');
        $select->where("1 =?", 1);
        /*CONDITION_START*/

        if (isset($condition["order_code"]) && $condition["order_code"] != "") {
            $select->where("orders.order_code in(?)", $condition["order_code"]);
        }
        if(isset($condition["new_reference_no"])&&$condition['new_reference_no']!=""){
            $select->where("(orders.order_code = '".$condition['new_reference_no']."') or (reference_no = ?)",$condition['new_reference_no']);
        }
        if (isset($condition["not_order_code"]) && $condition["not_order_code"] != "") {
            $select->where("orders.order_code != ?", $condition["not_order_code"]);
        }
        if (isset($condition["customer_id"]) && $condition["customer_id"] != "") {
            $select->where("customer_id = ?", $condition["customer_id"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
            $select->where("customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["warehouse_id"]) && $condition["warehouse_id"] != "") {
            $select->where("warehouse_id = ?", $condition["warehouse_id"]);
        }
        if (isset($condition["order_type"]) && $condition["order_type"] != "") {
            $select->where("order_type = ?", $condition["order_type"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("sm_code = ?", $condition["sm_code"]);
        }
        if (isset($condition["order_status"]) && $condition["order_status"] != "") {
            $select->where("order_status = ?", $condition["order_status"]);
        }
        if (isset($condition["order_status_arr"]) && $condition["order_status_arr"] != "") {
            $select->where("order_status in (?)", $condition["order_status_arr"]);
        }
        if (isset($condition["except_order_status_delete"])) {
            $select->where("order_status > ?", 0);
        }
        if (isset($condition["problem_status"]) && $condition["problem_status"] != "") {
            $select->where("problem_status = ?", $condition["problem_status"]);
        }
        if (isset($condition["underreview_status"]) && $condition["underreview_status"] != "") {
            $select->where("underreview_status = ?", $condition["underreview_status"]);
        }
        if (isset($condition["order_pick_type"]) && $condition["order_pick_type"] != "") {
            $select->where("order_pick_type = ?", $condition["order_pick_type"]);
        }
        if (isset($condition["reference_no"]) && $condition["reference_no"] != "") {
            $select->where("reference_no = ?", $condition["reference_no"]);
        }
        if (isset($condition["picker_id"]) && $condition["picker_id"] != "") {
            $select->where("picker_id = ?", $condition["picker_id"]);
        }
        if (isset($condition["add_time_start"]) && $condition["add_time_start"] != "") {
            $select->where("add_time >=?", $condition["add_time_start"] . ' 00:00:00');
        }

        if (isset($condition["add_time_end"]) && $condition["add_time_end"] != "") {
            $select->where("add_time <=?", $condition["add_time_end"] . ' 23:59:59');
        }
        if(isset($condition["order_mode_type"]) && $condition["order_mode_type"] != ""){
            $select->where("order_mode_type = ?", $condition["order_mode_type"]);
        }
        if(isset($condition["ref_tracking_number"]) && $condition["ref_tracking_number"] != ""){
            $select->where("ref_tracking_number = ?", $condition["ref_tracking_number"]);
        }
        if((isset($condition['ship_time_start'])&&$condition['ship_time_start']!="")||(isset($condition['ship_time_end'])&&$condition['ship_time_end']!="")){
            $select->join("order_operation_time","orders.order_id=order_operation_time.order_id",array());
            if($condition['ship_time_start']!=""){
                $select->where("order_operation_time.ship_time >= ? ",$condition['ship_time_start']." 00:00:00");
            }
            if($condition['ship_time_end']!=""){
                $select->where("order_operation_time.ship_time <= ? ",$condition['ship_time_end']." 23:59:59");
            }
        }
        return $select->__toString();
    }

}