<?php
/**
 * Created by JetBrains PhpStorm.
 * User: Administrator
 * Date: 17-10-27
 * Time: 下午4:28
 * To change this template use File | Settings | File Templates.
 */
class Table_ShipBatchGoods
{
    protected $_table = null;

    public function __construct()
    {
        $this->_table = new DbTable_ShipBatchGoods();
    }

    public function getAdapter()
    {
        return $this->_table->getAdapter();
    }

    public static function getInstance()
    {
        return new Table_ShipBatchGoods();
    }

    /**
     * @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 = "sbg_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 = "sbg_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 = 'sbg_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 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["sbg_id"]) && $condition["sbg_id"] != ""){
            $select->where("sbg_id = ?",$condition["sbg_id"]);
        }
        if(isset($condition["sb_code"]) && $condition["sb_code"] != ""){
            $select->where("sb_code = ?",$condition["sb_code"]);
        }
        if(isset($condition["product_id"]) && $condition["product_id"] != ""){
            $select->where("product_id = ?",$condition["product_id"]);
        }
        if(isset($condition["goods_id"]) && $condition["goods_id"] != ""){
            $select->where("goods_id = ?",$condition["goods_id"]);
        }
        if(isset($condition["good_quantity"]) && $condition["good_quantity"] != ""){
            $select->where("good_quantity = ?",$condition["good_quantity"]);
        }
        if(isset($condition["from_product_id"]) && $condition["from_product_id"] != ""){
            $select->where("from_product_id = ?",$condition["from_product_id"]);
        }
        if(isset($condition["order_code"]) && $condition["order_code"] != ""){
            $select->where("order_code = ?",$condition["order_code"]);
        }
        if(isset($condition["ob_no"]) && $condition["ob_no"] != ""){
            $select->where("ob_no = ?",$condition["ob_no"]);
        }
        if(isset($condition["hs_serial_no"]) && $condition["hs_serial_no"] != ""){
            $select->where("hs_serial_no = ?",$condition["hs_serial_no"]);
        }
        /*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 getHsinfoExportBySbcode($condition = array(), $type = '', $pageSize = 0, $page = 1, $orderBy = ""){
        if($type != ''){
            if($type == 'count(*)'){
                $sql_select = "COUNT(*)";
            }else{
                $sql_select = $type;
            }
        }else{
            $sql_select = " DISTINCT
       sbg.product_id as product_id,
       sbg.hs_serial_no as hs_serial_no,
       sbg.batch_goods_id as batch_goods_id,
       sbg.from_product_id as from_product_id,
    	sbg.sb_code AS 'sb_code',
    	sbg.ob_no AS 'ob_no',
    	so.order_code AS 'order_code',
    	o.reference_no AS 'reference_no',
    	so.tracking_number AS 'tracking_number',
    	p.product_sku AS 'product_sku',
    	sum(sbg.good_quantity) AS 'op_quantity',
    	p.pu_code AS pu_code,
    	p.goods_id AS 'goods_id',
    	p.hs_code AS 'hs_code',
    	p.hs_goods_name AS 'hs_goods_name',
    	p.product_title AS 'product_title_cn',
        p.product_weight AS 'product_weight',
    	o.customer_code AS  'customer_code'";
        }

        $sql = "SELECT ";
        $sql.= $sql_select;
        $sql.=" FROM ship_batch_goods AS sbg
        INNER JOIN orders AS o ON o.order_code = sbg.order_code
        INNER JOIN ship_order AS so ON so.order_code = sbg.order_code
        INNER JOIN product AS p ON p.product_id = sbg.from_product_id
        WHERE (1=1) ";
        if(isset($condition["sb_code"]) && $condition["sb_code"] != ""){
            if(is_array($condition['sb_code'])){
                $condition['sb_code']=implode("','", $condition['sb_code']);
                $sql .= "AND (sbg.sb_code in ('".$condition['sb_code']."'))";
            }else{
                $sql .= "AND (sbg.sb_code = ('".$condition['sb_code']."'))";
            }
        }
        if(isset($condition['order_code'])&&$condition['order_code']!=""){
            $sql .= "AND (sbg.order_code = ('".$condition['order_code']."'))";
        }
        if(isset($condition["ob_no"]) && $condition["ob_no"] != ""){
            $sql .= "AND (sbg.ob_no = ('".$condition['ob_no']."'))";
        }
        $sql.=" group by p.goods_id,sbg.order_code,sbg.hs_serial_no,sbg.batch_goods_id";
        //echo $sql;exit();
        if($type=="count(*)"){
            return $this->_table->getAdapter()->fetchOne($sql);
        }else{
            if (!empty($orderBy)) {
                $sql .= "order by ".$orderBy;
            }
            if ($pageSize > 0 and $page > 0) {
                $start = ($page - 1) * $pageSize;
                $sql .= " limit ".$start.','.$pageSize;
            }
            return $this->_table->getAdapter()->query($sql)->fetchAll();
        }
    }

    public function getProductsByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = ""){
        /*
          sum(op.op_quantity) as quantity 申报数量
          sum(op.op_declared_value*op.op_quantity) as declared_value 总价格
          op.op_declared_value 单价
          /p.product_declared_value 单价
          p.product_id 商品id 取商品规格用
          p.goods_id 料件号id
          p.hs_code 海关编码
          p.hs_goods_name 海关品名
          sum(op.op_quantity*p.product_net_weight) as cnw 总净重量
          p.pu_code 申报单位
          hu.pu_code_law 法定单位,
          hu.pu_code_second 第二单位
          hum.hum_quantity_law 法定数量,
          hum.hum_quantity_second 第二数量
          c.currency_name 币制
         */
        $sql_select=' sbg.hs_serial_no,sbg.batch_goods_id,sbg.from_product_id as from_product_id,sbg.order_code AS order_code,sbg.product_id as op_product_id,
    	sbg.sb_code AS sb_code,sum(sbg.good_quantity) as quantity,p.product_declared_value as op_declared_value,
            p.product_id,p.goods_id,p.hs_code,p.hs_goods_name,
            sum(sbg.good_quantity*p.product_weight) as cw,
            p.pu_code,
            hu.pu_code_law,
            hu.pu_code_second,
            sum(hum.hum_quantity_law*sbg.good_quantity) as hum_quantity_law,
            sum(hum.hum_quantity_second*sbg.good_quantity) as hum_quantity_second,
            c.currency_name ';
        //$sql_count=' count(p.product_id) as count';
        $sql="SELECT ";
        $sql.=$sql_select;
        $sql.=" FROM `ship_batch_goods` AS `sbg`
            inner JOIN `ship_batch_attribute` AS `sba` ON sbg.sb_code=sba.sb_code
            inner JOIN `product` AS `p` ON p.product_id=sbg.from_product_id
            inner JOIN `hs_uom` AS `hu` ON hu.hs_code=p.hs_code
            inner JOIN `hs_uom_map` AS `hum` ON hum.product_id=p.product_id
            inner JOIN `currency` AS `c` ON p.currency_code=c.currency_code
            WHERE p.product_id!='' and ";
        if(!empty($condition['sb_code'])){
            if(is_array($condition['sb_code']))$condition['sb_code']=implode("','", $condition['sb_code']);
            $sql.="(sbg.sb_code in ('".$condition['sb_code']."')) AND";
        }
        $sql.=" (sba.trade_mode = '".$condition['trade_mode']."')";
        //$sql.=" group by p.goods_id,p.product_id,op.op_declared_value";
        $sql.=" group by p.goods_id,sbg.hs_serial_no,sbg.order_code";
        if($type=="count"){
            $sql="select count(*) as c from (".$sql.") as total where product_id!=''";
        }else{
            $sql="select *  from (".$sql.") as total where product_id!='' " ;
            //echo $sql;
            //exit;
            if ($pageSize > 0 and $page > 0) {
                $start = ($page - 1) * $pageSize;
                $sql.=" limit ".$start.','.$pageSize;
            }
        }
        return $this->_table->getAdapter()->query($sql)->fetchAll();
    }

}