<?php
class Table_ShipBatch
{
    protected $_table = null;

    public function __construct()
    {
        $this->_table = new DbTable_ShipBatch();
    }

    public function getAdapter()
    {
        return $this->_table->getAdapter();
    }

    public static function getInstance()
    {
        return new Table_ShipBatch();
    }

    /**
     * @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 = "sb_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 = "sb_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 = 'sb_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["sb_code"]) && $condition["sb_code"] != ""){
            $select->where("sb_code = ?",$condition["sb_code"]);
        }
        if(isset($condition["sm_code"]) && $condition["sm_code"] != ""){
            $select->where("sm_code = ?",$condition["sm_code"]);
        }
        if(isset($condition["warehouse_id"]) && $condition["warehouse_id"] != ""){
            $select->where("warehouse_id = ?",$condition["warehouse_id"]);
        }
        if(isset($condition["creater_id"]) && $condition["creater_id"] != ""){
            $select->where("creater_id = ?",$condition["creater_id"]);
        }
        if(isset($condition["sb_status"]) && $condition["sb_status"] != ""){
            $select->where("sb_status = ?",$condition["sb_status"]);
        }
        if(isset($condition["reference_no"]) && $condition["reference_no"] != ""){
            $select->where("reference_no = ?",$condition["reference_no"]);
        }
        if(isset($condition["sb_truck_no"]) && $condition["sb_truck_no"] != ""){
            $select->where("sb_truck_no = ?",$condition["sb_truck_no"]);
        }
        if(isset($condition["sb_status_arr"]) && $condition["sb_status_arr"] != ""){
        	$select->where("sb_status in (?)",$condition["sb_status_arr"]);
        }
        if(isset($condition['sb_ship_time_start'])&&$condition['sb_ship_time_start']!=""){
            $select->where("sb_ship_time>=?",$condition['sb_ship_time_start']." 00:00:00");
        }
        if(isset($condition['sb_ship_time_end'])&&$condition['sb_ship_time_end']!=""){
            $select->where("sb_ship_time<=?",$condition['sb_ship_time_end']." 23:59:59");
        }
        if(isset($condition['ship_time_start'])&&$condition['ship_time_start']!=""){
            $select->where("sb_ship_time>=?",$condition['ship_time_start']);
        }
        if(isset($condition['ship_time_end'])&&$condition['ship_time_end']!=""){
            $select->where("sb_ship_time<=?",$condition['ship_time_end']);
        }
        /*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 getLeftJoinUserByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $type);
        $select->joinLeft('user', 'user.user_id='.$table.'.creater_id',array('user_name'));
        $select->where("1 =?", 1);
        /*CONDITION_START*/

        if(isset($condition["sb_code"]) && $condition["sb_code"] != ""){
            $select->where("sb_code = ?",$condition["sb_code"]);
        }
        if(isset($condition["sm_code"]) && $condition["sm_code"] != ""){
            $select->where("sm_code = ?",$condition["sm_code"]);
        }
        if(isset($condition["warehouse_id"]) && $condition["warehouse_id"] != ""){
            $select->where("warehouse_id = ?",$condition["warehouse_id"]);
        }
        if(isset($condition["creater_id"]) && $condition["creater_id"] != ""){
            $select->where("creater_id = ?",$condition["creater_id"]);
        }
        if(isset($condition["sb_status"]) && $condition["sb_status"] != ""){
            $select->where("sb_status = ?",$condition["sb_status"]);
        }
        if(isset($condition["reference_no"]) && $condition["reference_no"] != ""){
            $select->where("reference_no = ?",$condition["reference_no"]);
        }
        if(isset($condition["sb_truck_no"]) && $condition["sb_truck_no"] != ""){
            $select->where("sb_truck_no = ?",$condition["sb_truck_no"]);
        }
        if(isset($condition["dateFor"]) && $condition["dateFor"] != ""){
            $select->where("sb_add_time >= ?",$condition["dateFor"]);
        }
        if(isset($condition["dateTo"]) && $condition["dateTo"] != ""){
            $select->where("sb_add_time <= ?",$condition["dateTo"]);
        }
        /*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 getJoinOrderByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = ""){
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        if($type=="count(*)"){
            $select->from($table, "COUNT(DISTINCT ship_batch.sb_code)");
        }else{
            $select->from($table,$type);
        }
        $select->joinLeft('user', 'user.user_id='.$table.'.creater_id',array('user_name'));
        $select->joinLeft('ship_batch_detail', 'ship_batch_detail.sb_code='.$table.'.sb_code',array());
        $select->joinLeft("outbound_bag_detail","outbound_bag_detail.ob_no=ship_batch_detail.ob_no",array());
        $select->joinLeft("orders","orders.order_code=outbound_bag_detail.order_code",array());
        $select->where("1 =?", 1);
        /*CONDITION_START*/

        if(isset($condition["sb_code"]) && $condition["sb_code"] != ""){
            $select->where($table.".sb_code = ?",$condition["sb_code"]);
        }
        if(isset($condition["sm_code"]) && $condition["sm_code"] != ""){
            $select->where($table.".sm_code = ?",$condition["sm_code"]);
        }
        if(isset($condition["warehouse_id"]) && $condition["warehouse_id"] != ""){
            $select->where($table.".warehouse_id = ?",$condition["warehouse_id"]);
        }
        if(isset($condition["creater_id"]) && $condition["creater_id"] != ""){
            $select->where($table.".creater_id = ?",$condition["creater_id"]);
        }
        if(isset($condition["sb_status"]) && $condition["sb_status"] != ""){
            $select->where($table.".sb_status = ?",$condition["sb_status"]);
        }
        if(isset($condition["reference_no"]) && $condition["reference_no"] != ""){
            $select->where($table.".reference_no = ?",$condition["reference_no"]);
        }
        if(isset($condition["sb_truck_no"]) && $condition["sb_truck_no"] != ""){
            $select->where($table.".sb_truck_no = ?",$condition["sb_truck_no"]);
        }
        if(isset($condition["dateFor"]) && $condition["dateFor"] != ""){
            $select->where($table.".sb_add_time >= ?",$condition["dateFor"]);
        }
        if(isset($condition["dateTo"]) && $condition["dateTo"] != ""){
            $select->where($table.".sb_add_time <= ?",$condition["dateTo"]);
        }
        if(isset($condition['order_code'])&&$condition['order_code']!=""){
            $select->where(" orders.order_code = ?",$condition['order_code']);
        }
        if(isset($condition['order_reference_no'])&&$condition['order_reference_no']!=""){
            $select->where(" orders.reference_no = ?",$condition['order_reference_no']);
        }
        if(isset($condition['ship_time_start'])&&$condition['ship_time_start']!=""){
            $select->where("sb_ship_time>=?",$condition['ship_time_start']);
        }
        if(isset($condition['ship_time_end'])&&$condition['ship_time_end']!=""){
            $select->where("sb_ship_time<=?",$condition['ship_time_end']);
        }
        if(isset($condition['project_type'])&&$condition['project_type']!=""){
            $select->where($table.".project_type=?",$condition['project_type']);
        }        
	    if(isset($condition["sb_status_arr"]) && $condition["sb_status_arr"] != ""){
            $select->where("sb_status in (?)",$condition["sb_status_arr"]);
        }
        if(isset($condition["out_area"]) && $condition["out_area"] != ""){
            $select->where($table.".out_area = ?",$condition["out_area"]);
        }
        if(isset($condition["ob_no"]) && $condition["ob_no"] != ""){
            $select->where("ship_batch_detail.ob_no = ?",$condition["ob_no"]);
        }
        //echo $select;exit();
        /*CONDITION_END*/ 
        if ('count(*)' == $type) {
            return $this->_table->getAdapter()->fetchOne($select);
        } else {
            $select->group($table.".sb_code");
            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);
        }
    }
    
    /**
     * 查询出货总单的所有订单号数组
     * @author solar
     * @param string $sb_code
     * @return array
     */
    public function getOrderCodes($sb_code) {
    	$aOrderCode = array();
    	$select = $this->_table->getAdapter()->select();
    	$select->from('ship_batch_detail as sbd');
    	$select->joinLeft('outbound_bag_detail as obd', 'sbd.ob_no=obd.ob_no', 'order_code');
    	$select->where('sbd.sb_code=?', $sb_code);
    	$list = $this->_table->getAdapter()->fetchAll($select);
    	foreach($list as $row) $aOrderCode[] = $row['order_code'];
    	return $aOrderCode;
    }

    public function getProductByCondition($condition){
        $select = $this->_table->getAdapter()->select();
        $select->from("ship_batch_detail as sbd");
        $select->joinLeft('outbound_bag_detail as obd','sbd.ob_no=obd.ob_no');
        $field1 = array('product_id','sum(op_quantity) as quantity','sum(op_declared_value*op_quantity) as declared_value','op_declared_value');
        $select->joinLeft('order_product as op', "obd.order_code=op.order_code",$field1);
        $field2 = array ('customer_id','goods_id','hs_goods_name','product_title','product_title_en','currency_code','hs_code','pu_code','product_declared_value','product_weight');
        $select->joinLeft('product as p', 'op.product_id=p.product_id', $field2);
        $field3 = array('hum_id','hum_quantity_law','hum_quantity_second');
        $select->joinLeft('hs_uom_map as hum', 'op.product_id=hum.product_id', $field3);
        $select->joinLeft('hs_uom as hu', 'p.hs_code=hu.hs_code', array('pu_code_law', 'pu_code_second'));
        $select->where('sbd.sb_code = ?', $condition['sb_code']);
        $select->where('p.hs_code = ?', $condition['hs_code']);
        if(isset($condition['pu_code'])){
            $select->where('p.pu_code = ?',$condition['pu_code']);
        }
        if(isset($condition['pu_code_law'])){
            $select->where('hu.pu_code_law = ?',$condition['pu_code_law']);
        }
        if(isset($condition['pu_code_second'])){
            $select->where('hu.pu_code_second = ?',$condition['pu_code_second']);
        }
        if(isset($condition['currency_code'])){
            $select->where('p.currency_code = ?',$condition['currency_code']);
        }
        $select->group('op.product_id');
        return $this->_table->getAdapter()->fetchAll($select);
    }

    public function getSplit20($sb_code){
        $sql = "SELECT DISTINCT concat(p.hs_code,'|',p.pu_code,'|',hu.pu_code_law,'|',hu.pu_code_second,'|',p.currency_code) FROM ship_batch_detail as sbd JOIN outbound_bag_detail as obd ON sbd.ob_no=obd.ob_no JOIN order_product as op ON obd.order_code=op.order_code JOIN product as p ON op.product_id=p.product_id JOIN hs_uom_map as hum ON op.product_id=hum.product_id JOIN hs_uom as hu ON p.hs_code=hu.hs_code WHERE 1 ";
        $sql.= " and sbd.sb_code= '".$sb_code."'";
        return $this->_table->getAdapter()->fetchCol($sql);
    }
    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=' sum(op.op_quantity) as quantity,sum(op.op_declared_value*op.op_quantity) as declared_value,p.product_declared_value as op_declared_value,
            p.product_id,p.goods_id,p.hs_code,p.hs_goods_name,
            sum(op.op_quantity*p.product_weight) as cw,
            p.pu_code,
            hu.pu_code_law,
            hu.pu_code_second,
            sum(hum.hum_quantity_law*op.op_quantity) as hum_quantity_law,
            sum(hum.hum_quantity_second*op.op_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_detail` AS `sbd`
            inner JOIN `ship_batch_attribute` AS `sba` ON sbd.sb_code=sba.sb_code
            inner JOIN `outbound_bag_detail` AS `obd` ON obd.ob_no=sbd.ob_no 
            inner JOIN `order_product` AS `op` ON op.order_code=obd.order_code 
            inner JOIN `product` AS `p` ON p.product_id=op.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.="(sbd.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";
        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!='' " ;
            if ($pageSize > 0 and $page > 0) {
                $start = ($page - 1) * $pageSize;
                $sql.=" limit ".$start.','.$pageSize;
            }
        }
        return $this->_table->getAdapter()->query($sql)->fetchAll();
    }
    /**
     * 
     * @param array $sb_codes
     */
    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
    	sb.sb_code AS 'sb_code',
    	sbd.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',
    	op.op_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',
    	op.op_declared_value AS 'op_declared_value',
    	o.customer_code AS  'customer_code'";
        }
        
        $sql = "SELECT ";
        $sql.= $sql_select; 
        $sql.=" FROM
        	ship_batch AS sb
        INNER JOIN ship_batch_detail AS sbd ON sb.sb_code = sbd.sb_code
        INNER JOIN outbound_bag_detail AS obd ON sbd.ob_no = obd.ob_no
        INNER JOIN ship_order AS so ON so.order_code = obd.order_code
        INNER JOIN orders AS o ON o.order_code = so.order_code
        INNER JOIN order_product AS op ON op.order_code = so.order_code
        INNER JOIN product AS p ON p.product_id = op.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 (sb.sb_code in ('".$condition['sb_code']."'))";
            }else{
                $sql .= "AND (sb.sb_code = ('".$condition['sb_code']."'))";
            }
        }
        if(isset($condition["sm_code"]) && $condition["sm_code"] != ""){
            $sql .= "AND (sb.sm_code = ('".$condition['sm_code']."'))";
        }
        if(isset($condition["warehouse_id"]) && $condition["warehouse_id"] != ""){
            $sql .= "AND (sb.warehouse_id = ('".$condition['warehouse_id']."'))";
        }
        if(isset($condition["creater_id"]) && $condition["creater_id"] != ""){
            $sql .= "AND (sb.creater_id = ('".$condition['creater_id']."'))";
        }
        if(isset($condition["sb_status"]) && $condition["sb_status"] != ""){
            $sql .= "AND (sb.sb_status = ('".$condition['sb_status']."'))";
        }
        if(isset($condition["reference_no"]) && $condition["reference_no"] != ""){
            $sql .= "AND (sb.reference_no = ('".$condition['reference_no']."'))";
        }
        if(isset($condition["sb_truck_no"]) && $condition["sb_truck_no"] != ""){
            $sql .= "AND (sb.sb_truck_no = ('".$condition['sb_truck_no']."'))";
        }
        if(isset($condition["dateFor"]) && $condition["dateFor"] != ""){
            $sql .= "AND (sb.sb_add_time >= ('".$condition['dateFor']."'))";
        }
        if(isset($condition["dateTo"]) && $condition["dateTo"] != ""){
            $sql .= "AND (sb.sb_add_time <= ('".$condition['dateTo']."'))";
        }
        if(isset($condition['order_code'])&&$condition['order_code']!=""){
            $sql .= "AND (o.order_code = ('".$condition['order_code']."'))";
        }
        if(isset($condition['order_reference_no'])&&$condition['order_reference_no']!=""){
           $sql .= "AND (o.reference_no = ('".$condition['order_reference_no']."'))";
        }
        if(isset($condition['ship_time_start'])&&$condition['ship_time_start']!=""){
            $sql .= "AND (sb.sb_ship_time >= ('".$condition['ship_time_start']."'))";
        }
        if(isset($condition['ship_time_end'])&&$condition['ship_time_end']!=""){
            $sql .= "AND (sb.sb_ship_time <= ('".$condition['ship_time_end']."'))";
        }
        if(isset($condition['project_type'])&&$condition['project_type']!=""){
            $sql .= "AND (sb.project_type = ('".$condition['project_type']."'))";
        }
        if(isset($condition["sb_status_arr"]) && $condition["sb_status_arr"] != ""){
            $sql .= "AND (sb.sb_status in ('".$condition['sb_status_arr']."'))";
        }
        if(isset($condition["out_area"]) && $condition["out_area"] != ""){
            $sql .= "AND (sb.out_area = ('".$condition['out_area']."'))";
        }
        if(isset($condition["ob_no"]) && $condition["ob_no"] != ""){
            $sql .= "AND (sbd.ob_no = ('".$condition['ob_no']."'))";
        }
        //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 getTradeCountry($sb_code,$goods_id){
        $sql = "SELECT country.trade_country FROM ship_batch_detail JOIN outbound_bag_detail ON outbound_bag_detail.ob_no=ship_batch_detail.ob_no JOIN order_product ON order_product.order_code=outbound_bag_detail.order_code JOIN order_address_book ON order_address_book.order_code=outbound_bag_detail.order_code JOIN product ON product.product_id=order_product.product_id JOIN country ON country.country_id=order_address_book.oab_country_id join orders on orders.order_code=outbound_bag_detail.order_code WHERE orders.order_mode_type=1 and sb_code='".$sb_code."' AND (trade_country='502' or trade_country='303') AND goods_id='".$goods_id."' order by orders.order_code asc";
        return $this->_table->getAdapter()->fetchAll($sql);
    }
}