<?php

class Table_BilIncome
{
    protected $_table = null;

    public function __construct()
    {
        $this->_table = new DbTable_BilIncome();
    }

    public function getAdapter()
    {
        return $this->_table->getAdapter();
    }

    public static function getInstance()
    {
        return new Table_BilIncome();
    }

    /**
     * @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 = "bi_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 = "bi_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 = 'bi_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["bcb_id"]) && $condition["bcb_id"] != "") {
            $select->where("bcb_id = ?", $condition["bcb_id"]);
        }
        //数组
        if (isset($condition["bi_create_type_arr"]) && is_array($condition["bi_create_type_arr"]) && !empty($condition["bi_create_type_arr"])) {
            $select->where("bi_create_type in(?)", $condition["bi_create_type_arr"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
            $select->where("customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["bb_id"]) && $condition["bb_id"] != "") {
            $select->where("bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["ft_code"]) && $condition["ft_code"] != "") {
            $select->where("ft_code = ?", $condition["ft_code"]);
        }
        if (isset($condition["ut_code"]) && $condition["ut_code"] != "") {
            $select->where("ut_code = ?", $condition["ut_code"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("sm_code = ?", $condition["sm_code"]);
        }
        if (isset($condition["bi_creator_id"]) && $condition["bi_creator_id"] != "") {
            $select->where("bi_creator_id = ?", $condition["bi_creator_id"]);
        }
        if (isset($condition["bi_amount"]) && $condition["bi_amount"] != "") {
            $select->where("bi_amount = ?", $condition["bi_amount"]);
        }
        if (isset($condition["currency_code"]) && $condition["currency_code"] != "") {
            $select->where("currency_code = ?", $condition["currency_code"]);
        }
        if (isset($condition["currency_rate"]) && $condition["currency_rate"] != "") {
            $select->where("currency_rate = ?", $condition["currency_rate"]);
        }
        if (isset($condition["bi_status"]) && $condition["bi_status"] != "") {
            $select->where("bi_status = ?", $condition["bi_status"]);
        }
        //数组
        if (isset($condition["bi_status_arr"]) && is_array($condition["bi_status_arr"]) && !empty($condition["bi_status_arr"])) {
            $select->where("bi_status in(?)", $condition["bi_status_arr"]);
        }
        if (isset($condition["bi_balance_sign"]) && $condition["bi_balance_sign"] != "") {
            $select->where("bi_balance_sign = ?", $condition["bi_balance_sign"]);
        }
        //红冲蓝补
        if (isset($condition["bi_return_sign"]) && $condition["bi_return_sign"] != "") {
            $select->where("bi_return_sign = ?", $condition["bi_return_sign"]);
        }
        /*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);
        }
    }


    /**
     * @desc 用于批量生成客户帐单
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     * @return array|string
     */
    public function getAllByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $types = $type == 'count(*)' ? "COUNT(DISTINCT(bil_income.customer_code))" : $type;
        $select->from($table, $types);
        $select->joinInner("customer", "customer.customer_code=bil_income.customer_code", array("cash_type"));
        $select->joinInner("bil_business", "bil_business.bb_id=bil_income.bb_id", array("ds_code", "warehouse_id", "bb_sales_id", "bb_status"));
        $select->where("1 =?", 1);
        /*CONDITION_START*/
        //必须为生效
        $select->where("bil_business.bb_status = 'y'");
        //未生成帐单
        $select->where("bil_income.bcb_id = '0'");
        //已审核状态
        $select->where("bil_income.bi_status = '2'");

        if (isset($condition["bcb_id"]) && $condition["bcb_id"] != "") {
            $select->where("bcb_id = ?", $condition["bcb_id"]);
        }
        if (isset($condition["cash_type"]) && $condition["cash_type"] != "") {
            $select->where("customer.cash_type = ?", $condition["cash_type"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
            $select->where("bil_income.customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["bb_id"]) && $condition["bb_id"] != "") {
            $select->where("bil_income.bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["bb_sales_id"]) && $condition["bb_sales_id"] !== "") {
            $select->where("bil_business.bb_sales_id = ?", $condition["bb_sales_id"]);
        }
        if (isset($condition["ft_code"]) && $condition["ft_code"] != "") {
            $select->where("bil_income.ft_code = ?", $condition["ft_code"]);
        }
        if (isset($condition["ds_code"]) && $condition["ds_code"] != "") {
            $select->where("bil_business.ds_code = ?", $condition["ds_code"]);
        }
        if (isset($condition["ds_code_arr"]) && $condition["ds_code_arr"] != "") {
            $select->where("bil_business.ds_code in (?)", $condition["ds_code_arr"]);
        }
        if (isset($condition["ut_code"]) && $condition["ut_code"] != "") {
            $select->where("bil_income.ut_code = ?", $condition["ut_code"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("bil_income.sm_code = ?", $condition["sm_code"]);
        }
        if (isset($condition["bi_amount"]) && $condition["bi_amount"] != "") {
            $select->where("bi_amount = ?", $condition["bi_amount"]);
        }
        if (isset($condition["bi_status"]) && $condition["bi_status"] != "") {
            $select->where("bi_status = ?", $condition["bi_status"]);
        }
        if (isset($condition["bi_chargeable_time_start"]) && $condition["bi_chargeable_time_start"] != "") {
            $select->where("bi_chargeable_time >= ?", $condition["bi_chargeable_time_start"]);
        }
        //计费时间
        if (isset($condition["bi_chargeable_time"]) && $condition["bi_chargeable_time"] != "") {
            $select->where("bi_chargeable_time <= ?", $condition["bi_chargeable_time"]);
        }

        //费用汇总
        $select->group(array("bil_income.customer_code"));

        /*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);
        }
    }

    /**
     * @desc 获取费用单据明细
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     * @return array|string
     */
    public function getFeeDetailByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $type);
        $select->joinLeft("fee_type", "fee_type.ft_code=bil_income.ft_code", array("ft_name_cn"));
        $select->joinLeft("customer", "customer.customer_code=bil_income.customer_code", array("customer_currency"));
        $select->joinLeft("bil_business", "bil_income.bb_id=bil_business.bb_id");
        $select->joinLeft("bil_business_attach", "bil_income.bb_id=bil_business_attach.bb_id");
        $select->where("1 =?", 1);
        /*CONDITION_START*/
        if (isset($condition["bb_id"]) && $condition["bb_id"] !== "") {
            $select->where("bil_income.bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["bcb_id"]) && $condition["bcb_id"] != "") {
            $select->where("bil_income.bcb_id = ?", $condition["bcb_id"]);
        }
        if (isset($condition["bb_refer_code"]) && $condition["bb_refer_code"] != "") {
            $select->where("bil_business_attach.bb_refer_code = ?", $condition["bb_refer_code"]);
        }
        if (isset($condition["bb_reference_no"]) && $condition["bb_reference_no"] != "") {
            $select->where("bil_business_attach.bb_reference_no = ?", $condition["bb_reference_no"]);
        }
        if (isset($condition["bb_service_number"]) && $condition["bb_service_number"] != "") {
            $select->where("bil_business_attach.bb_service_number = ?", $condition["bb_service_number"]);
        }
        if (isset($condition["ft_code"]) && $condition["ft_code"] != "") {
            $select->where("bil_income.ft_code = ?", $condition["ft_code"]);
        }
        if (isset($condition["ds_code"]) && $condition["ds_code"] != "") {
            $select->where("bil_business.ds_code = ?", $condition["ds_code"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("bil_income.sm_code = ?", $condition["sm_code"]);
        }

        /*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);
        }
    }


    /**
     * @desc 查看应收费用
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     * @return array|string
     */
    public function getNotAuditFeesByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $type);
        $select->joinInner("customer", "customer.customer_code=bil_income.customer_code", array("cash_type", "customer_currency","trade_name"));
        $select->joinInner("bil_business", "bil_business.bb_id=bil_income.bb_id", array("ds_code", "warehouse_id", "bb_sales_id", "bb_status"));
        $select->joinInner("bil_business_attach", "bil_business_attach.bb_id=bil_income.bb_id", array("bb_refer_code", "bb_reference_no", "bb_service_number"));
        $select->joinLeft("fee_type", "fee_type.ft_code=bil_income.ft_code", array("ft_name_cn"));
        $select->joinLeft("user as u1", "u1.user_id=bil_income.bi_creator_id", array("u1.user_name as creator"));
        $select->joinLeft("user as u2", "u2.user_id=bil_income.bi_verifier_id", array("u2.user_name as verifier"));
        $select->where("1 =?", 1);
        /*CONDITION_START*/
        //必须为生效
        $select->where("bil_business.bb_status = 'y'");

        if (isset($condition["bcb_id"]) && $condition["bcb_id"] != "") {
            $select->where("bcb_id = ?", $condition["bcb_id"]);
        }
        if (isset($condition["cash_type"]) && $condition["cash_type"] != "") {
            $select->where("customer.cash_type = ?", $condition["cash_type"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
            $select->where("bil_income.customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["bb_id"]) && $condition["bb_id"] != "") {
            $select->where("bil_income.bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["bb_sales_id"]) && $condition["bb_sales_id"] !== "") {
            $select->where("bil_business.bb_sales_id = ?", $condition["bb_sales_id"]);
        }
        if (isset($condition["bb_refer_code"]) && $condition["bb_refer_code"] !== "") {
            $select->where("bil_business_attach.bb_refer_code = ?", $condition["bb_refer_code"]);
        }
        if (isset($condition["ft_code"]) && $condition["ft_code"] != "") {
            $select->where("bil_income.ft_code = ?", $condition["ft_code"]);
        }
        if (isset($condition["ds_code"]) && $condition["ds_code"] != "") {
            $select->where("bil_business.ds_code = ?", $condition["ds_code"]);
        }
        if (isset($condition["ut_code"]) && $condition["ut_code"] != "") {
            $select->where("bil_income.ut_code = ?", $condition["ut_code"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("bil_income.sm_code = ?", $condition["sm_code"]);
        }
        if (isset($condition["bi_status"]) && $condition["bi_status"] !== "") {
            $select->where("bi_status = ?", $condition["bi_status"]);
        }
        if (isset($condition["bi_create_type"]) && $condition["bi_create_type"] !== "") {
            $select->where("bi_create_type = ?", $condition["bi_create_type"]);
        }
        if (isset($condition["bi_rp_type"]) && $condition["bi_rp_type"] !== "") {
            $select->where("bi_rp_type = ?", $condition["bi_rp_type"]);
        }
        if (isset($condition["bi_withdraw_sign"]) && $condition["bi_withdraw_sign"] !== "") {
            $select->where("bi_withdraw_sign = ?", $condition["bi_withdraw_sign"]);
        }
        //计费时间
        if (isset($condition["bi_chargeable_time_gt"]) && $condition["bi_chargeable_time_gt"] != "") {
            $select->where("bi_chargeable_time >= ?", $condition["bi_chargeable_time_gt"]);
        }
        if (isset($condition["billDateForm"]) && $condition["billDateForm"] != "") {
            $select->where("bi_chargeable_time >= ?", $condition["billDateForm"]);
        }
        if (isset($condition["billDateTo"]) && $condition["billDateTo"] != "") {
            $select->where("bi_chargeable_time <= ?", $condition["billDateTo"]);
        }

        if (isset($condition["createDateForm"]) && $condition["createDateForm"] != "") {
            $select->where("bi_add_time >= ?", $condition["createDateForm"]);
        }
        if (isset($condition["createDateTo"]) && $condition["createDateTo"] != "") {
            $select->where("bi_add_time <= ?", $condition["createDateTo"]);
        }
        if (isset($condition["bilVerifyDateFrom"]) && $condition["bilVerifyDateFrom"] != "") {
            $select->where("bi_verify_time >= ?", $condition["bilVerifyDateFrom"]);
        }
        if (isset($condition["bilVerifyDateTo"]) && $condition["bilVerifyDateTo"] != "") {
            $select->where("bi_verify_time <= ?", $condition["bilVerifyDateTo"]);
        }
        if (isset($condition["chargeDateForm"]) && $condition["chargeDateForm"] != "") {
            $select->where("bb_charge_time >= ?", $condition["chargeDateForm"]);
        }
        if (isset($condition["chargeDateTo"]) && $condition["chargeDateTo"] != "") {
            $select->where("bb_charge_time <= ?", $condition["chargeDateTo"]);
        }
        /*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);
        }
    }


    /**
     * @desc 查看未出帐费用
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     * @return array|string
     */
    public function getNotAuditOrdersByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $type);
        $select->joinLeft("customer", "customer.customer_code=bil_income.customer_code", array("cash_type", "customer_currency"));
        $select->joinLeft("bil_business", "bil_business.bb_id=bil_income.bb_id", array("ds_code", "warehouse_id", "bb_sales_id", "bb_status"));
        $select->joinLeft("bil_business_attach", "bil_business_attach.bb_id=bil_income.bb_id", array("bb_refer_code", "bb_reference_no", "bb_service_number"));
        $select->joinLeft("fee_type", "fee_type.ft_code=bil_income.ft_code", array("ft_name_cn"));
        $select->where("1 =?", 1);
        /*CONDITION_START*/
        //必须为生效
        $select->where("bil_business.bb_status = 'y'");
        //未生成帐单
        $select->where("bil_income.bcb_id = '0'");
        //已审核状态
        $select->where("bil_income.bi_status = '2'");

        if (isset($condition["bcb_id"]) && $condition["bcb_id"] != "") {
            $select->where("bcb_id = ?", $condition["bcb_id"]);
        }
        if (isset($condition["cash_type"]) && $condition["cash_type"] != "") {
            $select->where("customer.cash_type = ?", $condition["cash_type"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
            $select->where("bil_income.customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["bb_id"]) && $condition["bb_id"] != "") {
            $select->where("bil_income.bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["bb_sales_id"]) && $condition["bb_sales_id"] !== "") {
            $select->where("bil_business.bb_sales_id = ?", $condition["bb_sales_id"]);
        }
        if (isset($condition["ft_code"]) && $condition["ft_code"] != "") {
            $select->where("bil_income.ft_code = ?", $condition["ft_code"]);
        }
        if (isset($condition["ds_code"]) && $condition["ds_code"] != "") {
            $select->where("bil_business.ds_code = ?", $condition["ds_code"]);
        }
        if (isset($condition["ut_code"]) && $condition["ut_code"] != "") {
            $select->where("bil_income.ut_code = ?", $condition["ut_code"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("bil_income.sm_code = ?", $condition["sm_code"]);
        }
        //计费时间
        if (isset($condition["bi_chargeable_time"]) && $condition["bi_chargeable_time"] != "") {
            $select->where("bi_chargeable_time <= ?", $condition["bi_chargeable_time"]);
        }
        /*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);
        }
    }


    /**
     * @desc 查看未出帐费用(按单号汇总)
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     * @return array|string
     */
    public function getNotAuditOrderGroupByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $types = ($type) == 'count(*)' ? "COUNT(DISTINCT(bil_income.bb_id))" : $type;
        $select->from($table, $types);
        $select->joinLeft("customer", "customer.customer_code=bil_income.customer_code", array("cash_type", "customer_currency"));
        $select->joinLeft("bil_business", "bil_business.bb_id=bil_income.bb_id", array("ds_code", "warehouse_id", "bb_sales_id", "bb_status"));
        $select->joinLeft("bil_business_attach", "bil_business_attach.bb_id=bil_income.bb_id", array("bb_refer_code", "bb_reference_no", "bb_service_number"));
        //$select->joinLeft("fee_type", "fee_type.ft_code=bil_income.ft_code", array("ft_name_cn"));
        $select->where("1 =?", 1);
        /*CONDITION_START*/
        //必须为生效
        $select->where("bil_business.bb_status = 'y'");
        //未生成帐单
        $select->where("bil_income.bcb_id = '0'");
        //已审核状态
        $select->where("bil_income.bi_status = '2'");

        if (isset($condition["bcb_id"]) && $condition["bcb_id"] != "") {
            $select->where("bcb_id = ?", $condition["bcb_id"]);
        }
        if (isset($condition["cash_type"]) && $condition["cash_type"] != "") {
            $select->where("customer.cash_type = ?", $condition["cash_type"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
            $select->where("bil_income.customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["bb_id"]) && $condition["bb_id"] != "") {
            $select->where("bil_income.bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["bb_sales_id"]) && $condition["bb_sales_id"] !== "") {
            $select->where("bil_business.bb_sales_id = ?", $condition["bb_sales_id"]);
        }
        if (isset($condition["ft_code"]) && $condition["ft_code"] != "") {
            $select->where("bil_income.ft_code = ?", $condition["ft_code"]);
        }
        if (isset($condition["ds_code"]) && $condition["ds_code"] != "") {
            $select->where("bil_business.ds_code = ?", $condition["ds_code"]);
        }
        if (isset($condition["ut_code"]) && $condition["ut_code"] != "") {
            $select->where("bil_income.ut_code = ?", $condition["ut_code"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("bil_income.sm_code = ?", $condition["sm_code"]);
        }
        if (isset($condition["bi_chargeable_time_start"]) && $condition["bi_chargeable_time_start"] != "") {
            $select->where("bi_chargeable_time >= ?", $condition["bi_chargeable_time_start"]);
        }
        //单号
        if (isset($condition["bb_refer_code"]) && $condition["bb_refer_code"] !== "") {
            $select->where("bil_business_attach.bb_refer_code = ?", $condition["bb_refer_code"]);
        }
        //计费时间
        if (isset($condition["bi_chargeable_time"]) && $condition["bi_chargeable_time"] != "") {
            $select->where("bi_chargeable_time <= ?", $condition["bi_chargeable_time"]);
        }

        //费用汇总
        $select->group(array("bil_income.bb_id"));
        /*CONDITION_END*/
        if ('count(*)' == $type) {
            return count($this->_table->getAdapter()->fetchAll($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);
        }
    }


    /**
     * @desc 用于指定日期生成客户帐单
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     * @return array|string
     */
    public function getJoinInnerBusinessByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $type);
        $select->joinInner("bil_business", "bil_business.bb_id=bil_income.bb_id", array("ds_code", "warehouse_id", "bb_sales_id", "bb_status"));
        $select->where("1 =?", 1);
        /*CONDITION_START*/
        //必须为生效
        $select->where("bil_business.bb_status = 'y'");
        //未生成帐单
        $select->where("bil_income.bcb_id = '0'");
        //已审核状态
        $select->where("bil_income.bi_status = '2'");

        if (isset($condition["bcb_id"]) && $condition["bcb_id"] != "") {
            $select->where("bcb_id = ?", $condition["bcb_id"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
            $select->where("bil_income.customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["bb_id"]) && $condition["bb_id"] != "") {
            $select->where("bil_income.bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["bb_sales_id"]) && $condition["bb_sales_id"] !== "") {
            $select->where("bil_business.bb_sales_id = ?", $condition["bb_sales_id"]);
        }
        if (isset($condition["ft_code"]) && $condition["ft_code"] != "") {
            $select->where("bil_income.ft_code = ?", $condition["ft_code"]);
        }
        if (isset($condition["ds_code"]) && $condition["ds_code"] != "") {
            $select->where("bil_business.ds_code = ?", $condition["ds_code"]);
        }
        if (isset($condition["ds_code_arr"]) && $condition["ds_code_arr"] != "") {
            $select->where("bil_business.ds_code in (?)", $condition["ds_code_arr"]);
        }
        if (isset($condition["ut_code"]) && $condition["ut_code"] != "") {
            $select->where("bil_income.ut_code = ?", $condition["ut_code"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("bil_income.sm_code = ?", $condition["sm_code"]);
        }
        if (isset($condition["bi_chargeable_time_start"]) && $condition["bi_chargeable_time_start"] != "") {
            $select->where("bi_chargeable_time >= ?", $condition["bi_chargeable_time_start"]);
        }
        //计费时间
        if (isset($condition["bi_chargeable_time"]) && $condition["bi_chargeable_time"] != "") {
            $select->where("bi_chargeable_time <= ?", $condition["bi_chargeable_time"]);
        }
        /*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);
        }
    }


    /**
     * @desc 查看未出帐费用(按单号汇总),用于生成帐单
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     * @return array|string
     */
    public function getOrderGroupByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $types = ($type) == 'count(*)' ? "COUNT(DISTINCT(bil_income.bb_id))" : $type;
        $select->from($table, $types);
        $select->joinLeft("bil_business", "bil_business.bb_id=bil_income.bb_id", array("ds_code", "warehouse_id", "bb_sales_id", "bb_status"));
        $select->where("1 =?", 1);
        /*CONDITION_START*/
        //必须为生效
        $select->where("bil_business.bb_status = 'y'");
        //未生成帐单
        $select->where("bil_income.bcb_id = '0'");
        //已审核状态
        $select->where("bil_income.bi_status = '2'");

        if (isset($condition["bcb_id"]) && $condition["bcb_id"] != "") {
            $select->where("bcb_id = ?", $condition["bcb_id"]);
        }
        if (isset($condition["bb_id"]) && $condition["bb_id"] != "") {
            $select->where("bil_income.bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["bi_create_type"]) && $condition["bi_create_type"] !== "") {
            $select->where("bil_income.bi_create_type = ?", $condition["bi_create_type"]);
        }
        if (isset($condition["bb_sales_id"]) && $condition["bb_sales_id"] !== "") {
            $select->where("bil_business.bb_sales_id = ?", $condition["bb_sales_id"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] !== "") {
            $select->where("bil_business.customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["ft_code"]) && $condition["ft_code"] != "") {
            $select->where("bil_income.ft_code = ?", $condition["ft_code"]);
        }
        if (isset($condition["ds_code"]) && $condition["ds_code"] != "") {
            $select->where("bil_business.ds_code = ?", $condition["ds_code"]);
        }
        if (isset($condition["ds_code_arr"]) && !empty($condition["ds_code_arr"]) && is_array($condition["ds_code_arr"])) {
            $select->where("bil_business.ds_code in(?)", $condition["ds_code_arr"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("bil_income.sm_code = ?", $condition["sm_code"]);
        }
        //计费时间
        if (isset($condition["bi_chargeable_time"]) && $condition["bi_chargeable_time"] != "") {
            $select->where("bi_chargeable_time <= ?", $condition["bi_chargeable_time"]);
        }

        /*CONDITION_END*/
        if ('count(*)' == $type) {
            return $this->_table->getAdapter()->fetchOne($select);
        } else {
            //费用汇总
            $select->group(array("bil_income.bb_id"));
            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
     * @return array|string
     */
    public function getBillByCondition($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["bcb_id"]) && $condition["bcb_id"] != "") {
            $select->where("bcb_id = ?", $condition["bcb_id"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] != "") {
            $select->where("customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["bb_id"]) && $condition["bb_id"] != "") {
            $select->where("bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["bi_status"]) && $condition["bi_status"] != "") {
            $select->where("bi_status = ?", $condition["bi_status"]);
        }
        //小于
        if (isset($condition["bi_chargeable_time_lt"]) && $condition["bi_chargeable_time_lt"] != "") {
            $select->where("bi_chargeable_time <= ?", $condition["bi_chargeable_time_lt"]);
        }

        /*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);
        }
    }


    /**
     * @desc 汇总帐单各费用
     * @param string $bcb_id
     * @return mixed
     */
    public function getBillFeeGroup($bcb_id = '')
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $colums = array(
            'sum(bi_amount) as amount',
            'ft_code',
            'sm_code',
            'bil_business.ds_code',
        );
        $select->from($table, $colums);
        $select->joinLeft("bil_business", "bil_business.bb_id=bil_income.bb_id", array("ds_code", "warehouse_id", "bb_sales_id", "bb_status"));
        $select->where("bcb_id = ?", $bcb_id);
        $select->group(array('bil_income.ft_code', 'bil_income.sm_code', 'bil_business.ds_code'));
        $sql = $select->__toString();
        return $this->_table->getAdapter()->fetchAll($sql);
    }


    /**
     * @desc 获取帐单下全部单据
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     * @return array|string
     */
    public function getBillGroupByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $types = ($type) == 'count(*)' ? "COUNT(DISTINCT(bil_income.bb_id))" : $type;
        $select->from($table, $types);
        $select->joinLeft("bil_business", "bil_business.bb_id=bil_income.bb_id");
        $select->joinLeft("bil_business_attach", "bil_business_attach.bb_id=bil_income.bb_id", array("bb_refer_code", "bb_service_number", "bb_reference_no"));
        $select->where("1 =?", 1);
        /*CONDITION_START*/
        //必须为生效
        $select->where("bil_business.bb_status = 'y'");
        //已出帐状态
        $select->where("bil_income.bi_status = 3");

        if (isset($condition["bcb_id"]) && $condition["bcb_id"] !== "") {
            $select->where("bcb_id = ?", $condition["bcb_id"]);
        }
        if (isset($condition["bb_id"]) && $condition["bb_id"] != "") {
            $select->where("bil_income.bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["bb_sales_id"]) && $condition["bb_sales_id"] !== "") {
            $select->where("bil_business.bb_sales_id = ?", $condition["bb_sales_id"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] !== "") {
            $select->where("bil_business.customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["ft_code"]) && $condition["ft_code"] != "") {
            $select->where("bil_income.ft_code = ?", $condition["ft_code"]);
        }
        if (isset($condition["ds_code"]) && $condition["ds_code"] != "") {
            $select->where("bil_business.ds_code = ?", $condition["ds_code"]);
        }
        if (isset($condition["ut_code"]) && $condition["ut_code"] != "") {
            $select->where("bil_income.ut_code = ?", $condition["ut_code"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("bil_income.sm_code = ?", $condition["sm_code"]);
        }
        //计费时间
        if (isset($condition["bi_chargeable_time"]) && $condition["bi_chargeable_time"] != "") {
            $select->where("bi_chargeable_time <= ?", $condition["bi_chargeable_time"]);
        }
        if (isset($condition["bb_id_gt"]) && $condition["bb_id_gt"] != "") {
            $select->where("bil_income.bb_id >= ?", $condition["bb_id_gt"]);
        }
        /*CONDITION_END*/
        if ('count(*)' == $type) {
            return $this->_table->getAdapter()->fetchOne($select);
        } else {
            //费用汇总
            $select->group(array("bil_income.bb_id"));
            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);
        }
    }

    /**
     * @desc 同一个单据及帐单内费用汇总，客户币种
     * @param string $bb_id
     * @param string $bcb_id
     * @return array
     */
    public function getBusinessFeeGroup($bb_id = '', $bcb_id = '')
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $colums = array(
            'sum(bi_amount) as amount',
            'sum(bi_org_amount*currency_rate) as org_amount',
            'ft_code',
        );
        $select->from($table, $colums);
        $select->where("bcb_id = ?", $bcb_id);
        $select->where("bb_id = ?", $bb_id);
        $select->group(array('bil_income.ft_code'));
        $sql = $select->__toString();
        return $this->_table->getAdapter()->fetchAll($sql);
    }
    /**
     * @desc 同一个单据及帐单内费用汇总，原币种转换成人民币
     * @param string $bb_id
     * @param string $bcb_id
     * @return array
     */
    public function getBusinessFeeGroupRMB($bb_id = '', $bcb_id = '')
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $colums = array(
            'sum(bi_org_amount*currency_rate) as rmb_amount',
            'ft_code',
        );
        $select->from($table, $colums);
        $select->where("bcb_id = ?", $bcb_id);
        $select->where("bb_id = ?", $bb_id);
        $select->group(array('bil_income.ft_code'));
        $sql = $select->__toString();
        return $this->_table->getAdapter()->fetchAll($sql);
    }
    /**
     * [getBusinessFeeGroupOrg 同一个单据及帐单内费用汇总，原币种]
     * @param  string $bb_id  [description]
     * @param  string $bcb_id [description]
     * @return [type]         [description]
     */
    public function getBusinessFeeGroupOrg($bb_id = '', $bcb_id = ''){
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $colums = array(
            'sum(bi_org_amount) as org_amount',
            'ft_code',
        );
        $select->from($table, $colums);
        $select->where("bcb_id = ?", $bcb_id);
        $select->where("bb_id = ?", $bb_id);
        $select->group(array('bil_income.ft_code'));
        $sql = $select->__toString();
        return $this->_table->getAdapter()->fetchAll($sql);        
    }

    /**
     * @desc 同一个帐单内费用类型及计费单位汇总
     * @param string $bcb_id
     * @return array
     */
    public function getBillFeeTypeGroup( $bcb_id = '')
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $colums = array(
            'sum(bi_amount) as amount',
            'ft_code',
            'ut_code',
        );
        $select->from($table, $colums);
        $select->where("bcb_id = ?", $bcb_id);
        $select->group(array('bil_income.ft_code',"bil_income.ut_code"));
        $sql = $select->__toString();
        return $this->_table->getAdapter()->fetchAll($sql);
    }


    /**
     * @desc 获取费用单据明细
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     * @return array|string
     */
    public function getFeeDetailByBusinessCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $select->from($table, $type);
        $select->joinLeft("fee_type", "fee_type.ft_code=bil_income.ft_code", array("ft_name_cn"));
        $select->joinLeft("user as u1", "u1.user_id=bil_income.bi_creator_id", array("u1.user_name as creator"));
        $select->joinLeft("user as u2", "u2.user_id=bil_income.bi_verifier_id", array("u2.user_name as verifier"));
        $select->where("1 =?", 1);
        /*CONDITION_START*/
        if (isset($condition["bb_id"]) && $condition["bb_id"] !== "") {
            $select->where("bil_income.bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["bcb_id"]) && $condition["bcb_id"] != "") {
            $select->where("bil_income.bcb_id = ?", $condition["bcb_id"]);
        }
        if (isset($condition["ft_code"]) && $condition["ft_code"] != "") {
            $select->where("bil_income.ft_code = ?", $condition["ft_code"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("bil_income.sm_code = ?", $condition["sm_code"]);
        }

        /*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);
        }
    }

    /**
     * @desc 用于重新计费(查找未出帐单据)
     * @param array $condition
     * @param string $type
     * @param int $pageSize
     * @param int $page
     * @param string $orderBy
     * @return array|string
     */
    public function getGroupByCondition($condition = array(), $type = '*', $pageSize = 0, $page = 1, $orderBy = "")
    {
        $select = $this->_table->getAdapter()->select();
        $table = $this->_table->info('name');
        $types = ($type) == 'count(*)' ? "COUNT(DISTINCT(bil_income.bb_id))" : $type;
        $select->from($table, $types);
        $select->joinLeft("bil_business", "bil_business.bb_id=bil_income.bb_id", array("ds_code", "warehouse_id", "bb_sales_id", "bb_status"));
        $select->joinLeft("bil_business_attach", "bil_income.bb_id=bil_business_attach.bb_id", array("bb_refer_code"));
        $select->where("1 =?", 1);
        /*CONDITION_START*/
        //必须为生效
        $select->where("bil_business.bb_status = 'y'");
        //未生成帐单
        $select->where("bil_income.bcb_id = '0'");

        if (isset($condition["bi_status"]) && $condition["bi_status"] != "") {
            $select->where("bil_income.bi_status = ?", $condition["bi_status"]);
        }
        if (isset($condition["bcb_id"]) && $condition["bcb_id"] != "") {
            $select->where("bcb_id = ?", $condition["bcb_id"]);
        }
        if (isset($condition["bb_id"]) && $condition["bb_id"] != "") {
            $select->where("bil_income.bb_id = ?", $condition["bb_id"]);
        }
        if (isset($condition["bb_sales_id"]) && $condition["bb_sales_id"] !== "") {
            $select->where("bil_business.bb_sales_id = ?", $condition["bb_sales_id"]);
        }
        if (isset($condition["customer_code"]) && $condition["customer_code"] !== "") {
            $select->where("bil_business.customer_code = ?", $condition["customer_code"]);
        }
        if (isset($condition["customer_code_arr"]) && is_array($condition["customer_code_arr"]) && !empty($condition["customer_code_arr"])) {
            $select->where("bil_business.customer_code in(?)", $condition["customer_code_arr"]);
        }

        if (isset($condition["ds_code"]) && $condition["ds_code"] !== "") {
            $select->where("bil_business.ds_code = ?", $condition["ds_code"]);
        }
        if (isset($condition["ft_code"]) && $condition["ft_code"] != "") {
            $select->where("bil_income.ft_code = ?", $condition["ft_code"]);
        }
        if (isset($condition["sm_code"]) && $condition["sm_code"] != "") {
            $select->where("bil_income.sm_code = ?", $condition["sm_code"]);
        }

        if (isset($condition["ds_code_arr"]) && !empty($condition["ds_code_arr"]) && is_array($condition["ds_code_arr"])) {
            $select->where("bil_business.ds_code in(?)", $condition["ds_code_arr"]);
        }

        //计费时间
        if (isset($condition["bi_chargeable_time"]) && $condition["bi_chargeable_time"] != "") {
            $select->where("bi_chargeable_time <= ?", $condition["bi_chargeable_time"]);
        }

        /*CONDITION_END*/
        if ('count(*)' == $type) {
            return $this->_table->getAdapter()->fetchOne($select);
        } else {
            //费用汇总
            $select->group(array("bil_income.bb_id"));
            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);
        }
    }

}