<?php
class Table_InventoryBatch
{
    protected $_table = null;

    public function __construct()
    {
        $this->_table = new DbTable_InventoryBatch();
    }

    public function getAdapter()
    {
        return $this->_table->getAdapter();
    }

    public static function getInstance()
    {
        return new Table_InventoryBatch();
    }

    /**
     * @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 = "ib_id")
    {
        $where = $this->_table->getAdapter()->quoteInto("{$field}= ?", $value);
        return $this->_table->update($row, $where);
    }

    /**
     * @param $value
     * @param string $field
     * @return mixed
     */
    public function delete($value, $field = "ib_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 = 'ib_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 getByWhere($where)
    {
    	if(empty($where)) return array();
    	$select = $this->_table->getAdapter()->select();
    	$table = $this->_table->info('name');
    	$select->from($table, '*');
    	foreach($where as $field=>$value) {
    		$select->where($field.'=?', $value);
    	}
    	return $this->_table->getAdapter()->fetchRow($select);
    }
    
    public function listByWhere($where)
    {
    	if(empty($where)) return array();
    	$select = $this->_table->getAdapter()->select();
    	$table = $this->_table->info('name');
    	$select->from($table, '*');
    	foreach($where as $field=>$value) {
    		$select->where($field.'=?', $value);
    	}
    	return $this->_table->getAdapter()->fetchAll($select);
    }
    
    public function getForUpdate($ib_id) {
    	$sql = 'select * from inventory_batch where ib_id='.$ib_id.' for update;';
    	return $this->_table->getAdapter()->fetchRow($sql);
    }
    
    public function updateByField($row, $field) {
    	$aWhere = array();
    	foreach($field as $key=>$value) {
    		$aWhere[] = $this->_table->getAdapter()->quoteInto("{$key}= ?", $value);
    	} 
    	return $this->_table->update($row, implode(' AND ', $aWhere));
    }

    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["lc_code"]) && $condition["lc_code"] != ""){
            $select->where("lc_code = ?",$condition["lc_code"]);
        }
        if(isset($condition["product_id"]) && $condition["product_id"] != ""){
            $select->where("product_id = ?",$condition["product_id"]);
        }
        if(isset($condition['lc_code_like'])&&$condition['lc_code_like']!=""){
            $select->where("inventory_batch.lc_code like ?","%".$condition["lc_code_like"]."%");
        }
        if(isset($condition["box_code"]) && $condition["box_code"] != ""){
            $select->where("box_code = ?",$condition["box_code"]);
        }
        if(isset($condition["product_barcode"]) && $condition["product_barcode"] != ""){
            $select->where("product_barcode = ?",$condition["product_barcode"]);
        }
        if(isset($condition["reference_no"]) && $condition["reference_no"] != ""){
            $select->where("reference_no = ?",$condition["reference_no"]);
        }
        if(isset($condition["application_code"]) && $condition["application_code"] != ""){
            $select->where("application_code = ?",$condition["application_code"]);
        }
        if(isset($condition["supplier_id"]) && $condition["supplier_id"] != ""){
            $select->where("supplier_id = ?",$condition["supplier_id"]);
        }
        if(isset($condition["warehouse_id"]) && $condition["warehouse_id"] != ""){
            $select->where("warehouse_id = ?",$condition["warehouse_id"]);
        }
        if(isset($condition["receiving_code"]) && $condition["receiving_code"] != ""){
            $select->where("receiving_code = ?",$condition["receiving_code"]);
        }
        if(isset($condition["receiving_id"]) && $condition["receiving_id"] != ""){
            $select->where("receiving_id = ?",$condition["receiving_id"]);
        }
        if(isset($condition["lot_number"]) && $condition["lot_number"] != ""){
            $select->where("lot_number = ?",$condition["lot_number"]);
        }
        if(isset($condition["ib_status"]) && $condition["ib_status"] != ""){
            $select->where("ib_status = ?",$condition["ib_status"]);
        }
        if(isset($condition["ib_hold_status"]) && $condition["ib_hold_status"] != ""){
            $select->where("ib_hold_status = ?",$condition["ib_hold_status"]);
        }
        if(isset($condition["ib_quantity"]) && $condition["ib_quantity"] != ""){
            $select->where("ib_quantity = ?",$condition["ib_quantity"]);
        }
        if(isset($condition["ib_note"]) && $condition["ib_note"] != ""){
            $select->where("ib_note = ?",$condition["ib_note"]);
        }
        if(isset($condition["customer_code"]) && $condition["customer_code"] != ""){
            $select->where("customer_code = ?",$condition["customer_code"]);
        }		
        if(isset($condition["product_id_array"]) && $condition["product_id_array"] != "" && is_array($condition["product_id_array"])){
            $select->where("product_id IN(?)",$condition["product_id_array"]);
        }
        if(isset($condition['ib_quantity_gt']) && $condition['ib_quantity_gt'] !==''){
            $select->where('ib_quantity > ?',$condition['ib_quantity_gt']);
        }
        if(isset($condition['LC_CODE_NOT_REGEXP'])&&$condition['LC_CODE_NOT_REGEXP']!=""){
            $select->where("lc_code NOT REGEXP ?",$condition['LC_CODE_NOT_REGEXP']);
        }
        /*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 getLeftJoinByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $type);
        $select->joinLeft('location', 'location.lc_code='.$table.'.lc_code',array('wa_code'));
        $select->joinLeft('product', 'product.product_id='.$table.'.product_id',array('product_sku','product_title','goods_id','hs_goods_name'));
        $select->joinLeft('receiving', 'receiving.receiving_id='.$table.'.receiving_id',array('reference_no'));
        $select->where("1 =?", 1);
        /*CONDITION_START*/

        if(isset($condition["lc_code"]) && $condition["lc_code"] != ""){
            $select->where("inventory_batch.lc_code = ?",$condition["lc_code"]);
        }
        if(isset($condition['lc_code_like'])&&$condition['lc_code_like']!=""){
            $select->where("inventory_batch.lc_code like ?","%".$condition["lc_code_like"]."%");
        }
        if(isset($condition["product_id"]) && $condition["product_id"] != ""){
            $select->where("inventory_batch.product_id = ?",$condition["product_id"]);
        }
        if(isset($condition["box_code"]) && $condition["box_code"] != ""){
            $select->where("box_code = ?",$condition["box_code"]);
        }
        if(isset($condition["product_barcode"]) && $condition["product_barcode"] != ""){
            $select->where("inventory_batch.product_barcode = ?",$condition["product_barcode"]);
        }
        if(isset($condition["reference_no"]) && $condition["reference_no"] != ""){
            $select->where("inventory_batch.reference_no = ?",$condition["reference_no"]);
        }
        if(isset($condition["application_code"]) && $condition["application_code"] != ""){
            $select->where("application_code = ?",$condition["application_code"]);
        }
        if(isset($condition["supplier_id"]) && $condition["supplier_id"] != ""){
            $select->where("supplier_id = ?",$condition["supplier_id"]);
        }
        if(isset($condition["warehouse_id"]) && $condition["warehouse_id"] != ""){
            $select->where("inventory_batch.warehouse_id = ?",$condition["warehouse_id"]);
        }
        if(isset($condition["receiving_code"]) && $condition["receiving_code"] != ""){
            $select->where("inventory_batch.receiving_code = ?",$condition["receiving_code"]);
        }
        if(isset($condition["receiving_id"]) && $condition["receiving_id"] != ""){
            $select->where("receiving_id = ?",$condition["receiving_id"]);
        }
        if(isset($condition["lot_number"]) && $condition["lot_number"] != ""){
            $select->where("lot_number = ?",$condition["lot_number"]);
        }
        if(isset($condition["ib_status"]) && $condition["ib_status"] != ""){
            $select->where("ib_status = ?",$condition["ib_status"]);
        }
        if(isset($condition["ib_hold_status"]) && $condition["ib_hold_status"] != ""){
            $select->where("ib_hold_status = ?",$condition["ib_hold_status"]);
        }
        if(isset($condition["ib_quantity"]) && $condition["ib_quantity"] != ""){
            $select->where("ib_quantity = ?",$condition["ib_quantity"]);
        }
        if(isset($condition["ib_note"]) && $condition["ib_note"] != ""){
            $select->where("ib_note = ?",$condition["ib_note"]);
        }
        if(isset($condition["customer_code"]) && $condition["customer_code"] != ""){
            $select->where("inventory_batch.customer_code = ?",$condition["customer_code"]);
        }
        if(isset($condition["customer_codes"]) && $condition["customer_codes"] != ""){
            $select->where("inventory_batch.customer_code IN(?)",$condition["customer_codes"]);
        }
        if(isset($condition["product_id_array"]) && $condition["product_id_array"] != "" && is_array($condition["product_id_array"])){
            $select->where("inventory_batch.product_id IN(?)",$condition["product_id_array"]);
        }
        if(isset($condition["ib_quantity_gt0"]) && $condition["ib_quantity_gt0"] != ""){
            $select->where("ib_quantity > ?",0);
        }
        if(isset($condition["wa_code"]) && $condition["wa_code"] != ""){
            $select->where("wa_code = ?",$condition["wa_code"]);
        }
        if(isset($condition['LC_CODE_NOT_REGEXP'])&&$condition['LC_CODE_NOT_REGEXP']!=""){
            $select->where("inventory_batch.lc_code NOT REGEXP ?",$condition['LC_CODE_NOT_REGEXP']);
        }
        if(isset($condition["hs_code"]) && $condition["hs_code"] != ""){
            $select->where("product.hs_code = ?",$condition["hs_code"]);
        }
        if(isset($condition["product_sku"]) && $condition["product_sku"] != ""){
            $select->where("product.product_sku = ?",$condition["product_sku"]);
        }
        if(isset($condition["goods_id"]) && $condition["goods_id"] != ""){
            $select->where("product.goods_id = ?",$condition["goods_id"]);
        }
        if(isset($condition['hs_code_like'])&&$condition['hs_code_like']!=""){
            $select->where("product.hs_code like ?","%".$condition["hs_code_like"]."%");
        }
        if(isset($condition['product_sku_like'])&&$condition['product_sku_like']!=""){
            $select->where("product.product_sku like ?","%".$condition["product_sku_like"]."%");
        }
        if(isset($condition['goods_id_like'])&&$condition['goods_id_like']!=""){
            $select->where("product.goods_id like ?","%".$condition["goods_id_like"]."%");
        }
        if(isset($condition['wa_code_like'])&&$condition['wa_code_like']!=""){
            $select->where("location.wa_code like ?","%".$condition["wa_code_like"]."%");
        }
        if(isset($condition['lc_code_like2'])&&$condition['lc_code_like2']!=""){
            $select->where("inventory_batch.lc_code like ?","%".$condition["lc_code_like2"]."%");
        }
        if(isset($condition['hs_goods_name_like'])&&$condition['hs_goods_name_like']!=""){
            $select->where("product.hs_goods_name like ?","%".$condition["hs_goods_name_like"]."%");
        }
        if(isset($condition['empty_hs_serial_no'])&&$condition['empty_hs_serial_no']!=""){
            $select->where("hs_serial_no =  ?","");
        }
        if(isset($condition['ib_quantity'])&&$condition['ib_quantity']!=""){
            $select->where("ib_quantity = ?",$condition["ib_quantity"]);
        }
        //empty_serial_or_batch
        if(isset($condition['empty_serial_or_batch'])&&$condition['empty_serial_or_batch']!=""){
            $select->where("hs_serial_no =  '' or batch_goods_id = ? ","");
        }
        if(isset($condition["hs_serial_no"]) && $condition["hs_serial_no"] != ""){
            $select->where("hs_serial_no = ?",$condition["hs_serial_no"]);
        }
        if(isset($condition["batch_goods_id"]) && $condition["batch_goods_id"] != ""){
            $select->where("batch_goods_id = ?",$condition["batch_goods_id"]);
        }

        /*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);
        }
    }

    /**
     * 可选择盘点列表查询
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     */
    public function getTakeStockLocations($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
    	$select = $this->_table->getAdapter()->select();
    	$table = $this->_table->info('name');
    	$select->from($table.' as ib', $type);
    	$select->joinLeft('product as p', 'ib.product_barcode=p.product_barcode', 'customer_code');
    	if(isset($condition["wa_code"]) && $condition["wa_code"] != ""){
    		$select->joinLeft('location as l', 'ib.lc_code=l.lc_code', '');
    		$select->where("l.wa_code = ?",$condition["wa_code"]);
    	}    	
    	/*CONDITION_START*/
    	if(isset($condition["warehouse_id"]) && $condition["warehouse_id"] != ""){
    		$select->where("ib.warehouse_id = ?",$condition["warehouse_id"]);
    	}
    	if(isset($condition["lc_code"]) && $condition["lc_code"] != ""){
    		$select->where("ib.lc_code = ?",$condition["lc_code"]);
    	}
    	if(isset($condition["product_barcode"]) && $condition["product_barcode"] != ""){
    		$select->where("ib.product_barcode = ?",$condition["product_barcode"]);
    	}
    	if(isset($condition["customer_code"]) && $condition["customer_code"] != ""){
    		$select->where("p.customer_code = ?",$condition["customer_code"]);
    	}
    	
    	$select->where('ib.ib_hold_status = 0');
    	
    	/*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 listTakeStockLocation($condition) {
    	$select = $this->_table->getAdapter()->select();
    	$table = $this->_table->info('name');
    	$select->from($table.' as ib', array('lc_code','product_barcode','ib_quantity','ib_status','ib_hold_status'));
    	$select->joinLeft('product as p', 'ib.product_barcode=p.product_barcode', 'customer_code');
    	$select->joinLeft('inventory_batch_outbound AS ibo', 'ib.ib_id=ibo.ib_id', 'ibo_id');
    	if(isset($condition["wa_code"]) && $condition["wa_code"] != ""){
    		$select->joinLeft('location as l', 'ib.warehouse_id=l.warehouse_id and ib.lc_code=l.lc_code', '');
    		$select->where("l.wa_code = ?",$condition["wa_code"]);
    	}
    	/*CONDITION_START*/
    	if(isset($condition["warehouse_id"]) && $condition["warehouse_id"] != ""){
    		$select->where("ib.warehouse_id = ?",$condition["warehouse_id"]);
    	}
    	if(isset($condition["lc_code"]) && $condition["lc_code"] != ""){
    		$select->where("ib.lc_code = ?",$condition["lc_code"]);
    	}
    	if(isset($condition["product_barcode"]) && $condition["product_barcode"] != ""){
    		$select->where("ib.product_barcode = ?",$condition["product_barcode"]);
    	}
    	if(isset($condition["customer_code"]) && $condition["customer_code"] != ""){
    		$select->where("p.customer_code = ?",$condition["customer_code"]);
    	}
    	//$select->where('ibo.ibo_id is null');
    	$select->order(array('ib.lc_code','ib.product_barcode'));
    	$sql = $select->__toString();
    	return $this->_table->getAdapter()->fetchAll($sql);
    }
    
    /**
     * 根据ib_id数组查询盘点记录
     * @author solar
     * @param array $ib_ids
     * @return array
     */
    public function getTakeStockAll($ib_ids) {
    	if(!is_array($ib_ids) || empty($ib_ids)) return array();
    	$select = $this->_table->getAdapter()->select();
    	$table = $this->_table->info('name');
    	$select->from($table.' as ib', "*");
    	$select->joinLeft('product as p', 'ib.product_barcode=p.product_barcode', 'customer_code');
    	$select->where('ib.ib_id IN(?)', $ib_ids);
    	return $this->_table->getAdapter()->fetchAll($select);
    }

    public function getRecordByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $type);
        $select->joinLeft('inventory_batch_serial as ibs', 'ibs.ib_id='.$table.'.ib_id',array(
            'ibs_quantity', 'hs_serial_no as ibs_hsn','batch_goods_id as ibs_bgi',  'ib_id as ibs_ib_id', 'product_id as pid', 'ibs_id', 'swo_code','customer_code as ibs_cc'));
        $select->joinLeft('product as p', 'p.product_id='.$table.'.product_id',array('goods_id', 'product_sku', 'customer_code', 'product_title'));
        $select->joinInner('product_inventory as pi', 'pi.product_id='.$table.'.product_id',array('pi_reserved', 'pi_shipped'));

        $select->where("1 =?", 1);

        /*CONDITION_START*/
        if(isset($condition['goods_id']) && $condition['goods_id'] != ''){
            $select->where("p.goods_id = ?",$condition["goods_id"]);
        }

        if(isset($condition['product_sku']) && $condition['product_sku'] != ''){
            $select->where("p.product_sku = ?",$condition["product_sku"]);
        }

        if(isset($condition['hs_serial_no']) && $condition['hs_serial_no'] != ''){
            $select->where($table.".hs_serial_no IN(?)",array($condition["hs_serial_no"]));
            $select->orWhere("ibs.hs_serial_no IN(?)",array($condition["hs_serial_no"]));
        }

        if(!empty($condition['ib_ids']) && is_array($condition['ib_ids'])){
            $select->where($table.".ib_id IN(?)",array($condition["ib_ids"]));
        }

        /*CONDITION_END*/

        if ('count(*)' == $type) {
            return $this->_table->getAdapter()->fetchOne($select);
        } else {
            if (!empty($orderBy)) {
                $select->order($orderBy);
            }
            if ($pageSize > 0 && $page > 0) {
                $start = ($page - 1) * $pageSize;
                $select->limit($pageSize, $start);
            }
            $sql = $select->__toString();
            return $this->_table->getAdapter()->fetchAll($sql);
        }
    }
    
}