<?php

/**
 * @desc 客户帐单处理类
 */
class Process_CustomerBill
{
    protected $_date = "";
    protected $_userId = "";

    public function __construct()
    {
        $this->_userId = Service_User::getUserId();
        $this->_date = date('Y-m-d H:i:s');
    }

    /**
     * @获取帐单基本信息
     */
    public static function getBasicInfo($value = '', $field = "bcb_code")
    {
        $result = array("state" => 0, "message" => '帐单号不存在', "data" => array());
        $data = Table_BilCustomerBill::getInstance()->getJoinInnerSurplusByField($value, $field);
        if (!empty($data)) {
            $bilCustomerBillStatus = Process_BillConfig::bilCustomerBillStatus('auto');
            $bilWriteOffSign = Process_BillConfig::bilWriteOffSign('auto');
            $businessType = Process_BillConfig::businessType('auto');
            $bilReviewSign = Process_BillConfig::bilReviewSign('auto');
            $data['status_text'] = $bilCustomerBillStatus[$data['bcb_status']];
            $data['writeoff_sign_text'] = $bilWriteOffSign[$data['bcb_writeoff_sign']];
            $data['ds_code_text'] = isset($businessType[$data['ds_code']]) ? $businessType[$data['ds_code']] : "";
            $data['review_sign_text'] = $bilReviewSign[$data['bcb_review_sign']];

            $user_id_arr['creatorName'] = $data['bcb_creator_id'];
            $user_id_arr['confirmName'] = $data['bcb_confirm_id'];
            $user_id_arr['verifierName'] = $data['bcb_verifier_id'];
            $user_id_arr['deleteName'] = $data['bcb_delete_id'];

            $data['creatorName'] = '';
            $data['deleteName'] = '';
            $data['confirmName'] = '';
            $data['verifierName'] = '';

            $userRows = Service_User::getByCondition(array('user_id_arr' => $user_id_arr), array('user_id', 'user_name'), 0);

            $userArr = array_flip($user_id_arr);
            unset($user_id_arr);
            if (!empty($userRows)) {
                foreach ($userRows as $row) {
                    if (isset($userArr[$row['user_id']])) {
                        $data[$userArr[$row['user_id']]] = $row["user_name"];
                    }
                }
            }
            if ($data['creatorName'] == '') {
                $data['creatorName'] = "系统";
            }
            $result["state"] = 1;
            $result["data"] = $data;
        }
        return $result;
    }


    /**
     * @return string
     */
    public function getCustomerBillCode()
    {
        return Common_GetNumbers::getCode("bil_customer_bill_code", "", "B");
    }

    /**
     * @desc 根据客户费用生成客户帐单
     * @param array $param
     * @return array
     */
    public function createCustomerBillTransaction($param = array())
    {
        $result = array("state" => 0, "message" => "", "code" => '', "amount" => 0);

        $db = Common_Common::getAdapter();
        $db->beginTransaction();
        try {
            //获取客户信息
            $customerRow = Service_Customer::getByField($param['customer_code'], 'customer_code');
            if (empty($customerRow)) {
                throw new Exception("客户代码：" . $param['customer_code'] . ' 不存在');
            }
            $bcb_code = $this->getCustomerBillCode();
            //建立帐单主表信息
            $bcbAmount = 0;
            $biFeeQty = 0; //用于记录关联帐单条数
            $ds_code_arr = array();
            $sm_code_arr = array();
            $billArr = array(
                'customer_code' => $customerRow['customer_code'],
                'bcb_status' => 0,
                'bcb_code' => $bcb_code,
                'bcb_reference_no' => $param['reference_no'],
                'bcb_amount' => 0,
                'currency_code' => $customerRow['customer_currency'],
                'bcb_bill_start_date' => $param['bcb_bill_start_date'],
                'bcb_bill_date' => $param['bcb_bill_date'],
                'bcb_writeoff_sign' => 'n',
                'ds_code' => '',
                'sm_code' => '',
                'bcb_note' => $param['note'],
                'bcb_creator_id' => $this->_userId,
                'bcb_create_type' => '0', //人工创建
                'bcb_add_time' => $this->_date,
                'bcb_review_sign' => 'n',
                'bcb_update_time' => $this->_date,
            );

            if (!$billId = Service_BilCustomerBill::add($billArr)) {
                throw new Exception("建立帐单失败");
            }
            //应收费用
            $param['bbIdArr'] = array_unique($param['bbIdArr']);
            foreach ($param['bbIdArr'] as $bbId) {
                $businessRow = Service_BilBusiness::getByField($bbId, 'bb_id');
                if ($businessRow['bb_status'] == 'n' || strtoupper($businessRow['customer_code']) != strtoupper($customerRow['customer_code'])) {
                    continue;
                }
                $condition = array(
                    'bb_id' => $bbId,
                    'bi_status' => 2,
                );
                $incomeRows = Service_BilIncome::getByCondition($condition, '*', 0);
                if (empty($incomeRows)) {
                    continue;
                }

                //帐单数据汇总业务类型
                $ds_code_arr[$businessRow['ds_code']] = $businessRow['ds_code'];
                $sm_code_arr[$businessRow['sm_code']] = $businessRow['sm_code'];

                //处理费用对照关系
                foreach ($incomeRows as $ik => $iv) {
                    //记录条数
                    $biFeeQty++;

                    //更新帐单ID、状态修改已出帐
                    Service_BilIncome::update(array('bi_status' => 3, 'bcb_id' => $billId, 'bi_update_time' => $this->_date), $iv['bi_id'], 'bi_id');

                    //帐单的对照关系表
                    $mapArr = array(
                        'bcb_id' => $billId,
                        'bi_id' => $iv['bi_id'],
                    );
                    Service_BilCustomerBillMap::add($mapArr);

                    //帐单数据汇总Start
                    $bcbAmount += $iv['bi_amount'];
                    //帐单数据汇总End

                }

            }
            //更新帐单数据
            if ($biFeeQty) {
                //当存在费用时
                $upArr = array(
                    'bcb_status' => 1, //默认为草稿
                    'bcb_amount' => $bcbAmount,
                    'ds_code' => (count($ds_code_arr) == 1 ? current($ds_code_arr) : ''), //超过两种类型 使用空值
                    'sm_code' => (count($sm_code_arr) == 1 ? current($sm_code_arr) : ''),
                    // 'bcb_confirm_id' => $this->_userId,
                    // 'bcb_confirm_time' => $this->_date,
                );
                if (!Service_BilCustomerBill::update($upArr, $billId, 'bcb_id')) {
                    throw new Exception("建立帐单错误");
                }

                //按单据汇总各费用
                $this->AddCustomerBillFee($billId);

            } else {
                //没有费用明细，删除帐单，返回失败
                throw new Exception("没有找到匹配的费用记录");
            }

            //处理期初、期末 由于未审核先设置为0
            $spArr = array(
                'bcb_id' => $billId,
                'bcbs_period_begin_surplus' => 0,
                'bcbs_total_amount' => $bcbAmount,
                'bcbs_period_end_surplus' => 0,
                'bcbs_update_time' => $this->_date,
            );
            Service_BilCustomerBillSurplus::add($spArr);

            //帐单日志
            $log = array(
                'bcb_id' => $billId,
                'bcbl_status_from' => '1',
                'bcbl_status_to' => '1',
                'bcbl_note' => '建立客户帐单',
                'bcbl_ip' => Common_Common::getIP(),
                'bcbl_creator_id' => $this->_userId,
                'bcbl_add_time' => $this->_date,
            );
            Service_BilCustomerBillLog::add($log);

            $db->commit();

            $result['state'] = 1;
            $result['message'] = '操作成功';
            $result['code'] = $bcb_code;
            $result['amount'] = $bcbAmount;
        } catch (Exception $e) {
            $db->rollBack();
            $result['state'] = 0;
            $result['message'] = $e->getMessage();
        }
        return $result;
    }


    /**
     * @desc 根据客户代码生成帐单
     * @param array $param
     * @return array
     */
    public function createBillByCustomerTransactionOld($param = array())
    {
        $result = array("state" => 0, "message" => "", "data" => array());

        $db = Common_Common::getAdapter();
        $db->beginTransaction();
        try {
            //参考结构
            $customerCode = $param['customer_code'];
            $billDate = $param['bill_date'];
            $dsCode = $param['ds_code'];
            $note = $param['note'];
            $reference_no = $param['reference_no'];

            //获取客户信息
            $customerRow = Service_Customer::getByField($customerCode, 'customer_code');
            if (empty($customerRow)) {
                throw new Exception("客户代码：" . $customerCode . ' 不存在');
            }
            $customerCode = $customerRow['customer_code'];

            $bcb_code = $this->getCustomerBillCode();
            //建立帐单主表信息
            $bcbAmount = 0;
            $biFeeQty = 0; //用于记录关联帐单条数
            $ds_code_arr = array();
            $sm_code_arr = array();
            $billArr = array(
                'customer_code' => $customerRow['customer_code'],
                'bcb_status' => 0,
                'bcb_code' => $bcb_code,
                'bcb_reference_no' => $reference_no,
                'bcb_amount' => 0,
                'currency_code' => $customerRow['customer_currency'],
                'bcb_bill_date' => $billDate,
                'bcb_writeoff_sign' => 'n',
                'ds_code' => $dsCode,
                'sm_code' => '',
                'bcb_note' => $note,
                'bcb_creator_id' => $this->_userId,
                'bcb_create_type' => '0', //人工创建
                'bcb_add_time' => $this->_date,
                'bcb_review_sign' => 'n',
                'bcb_update_time' => $this->_date,

            );
            if (!$billId = Service_BilCustomerBill::add($billArr)) {
                throw new Exception("建立帐单失败");
            }
            //应收费用
            $groupCondition = array(
                'ds_code' => $dsCode,
                'customer_code' => $customerCode,
                'bi_chargeable_time' => $billDate, //小于此日期
            );

            $total = Service_BilIncome::getOrderGroupByCondition($groupCondition, 'count(*)');
            if (!$total) {
                throw new Exception("没有找到匹配的费用记录");
            }

            $loop = 0;
            $pageSize = 300; //每次三百,遍历处理
            $pageCount = ceil($total / $pageSize);
            while ($loop <= $pageCount) {
                $loop++;
                $businessRows = Service_BilIncome::getOrderGroupByCondition($groupCondition, '*', $pageSize, 1, array("bil_business.bb_id asc"));
                //按单号进行生成帐单明细、
                foreach ($businessRows as $bRow) {
                    if ($bRow['customer_code'] != $customerCode) {
                        throw new Exception("过滤条件异常");
                    }

                    $condition = array(
                        'bb_id' => $bRow['bb_id'],
                        'bi_status' => 2,
                        'bi_chargeable_time_lt' => $billDate, //计费时间
                    );
                    $incomeRows = Service_BilIncome::getBillByCondition($condition, '*', 0);
                    if (empty($incomeRows)) {
                        continue;
                    }

                    //类型统计
                    $ds_code_arr[$bRow['ds_code']] = $bRow['ds_code'];
                    $sm_code_arr[$bRow['sm_code']] = $bRow['sm_code'];


                    //处理费用对照关系
                    foreach ($incomeRows as $ik => $iv) {

                        //记录条数
                        $biFeeQty++;

                        //更新帐单ID、状态修改已出帐
                        Service_BilIncome::update(array('bi_status' => 3, 'bcb_id' => $billId, 'bi_update_time' => $this->_date), $iv['bi_id'], 'bi_id');

                        //帐单的对照关系表
                        $mapArr = array(
                            'bcb_id' => $billId,
                            'bi_id' => $iv['bi_id'],
                        );
                        Service_BilCustomerBillMap::add($mapArr);

                        //帐单费用汇总Start
                        $bcbAmount += $iv['bi_amount'];
                        //帐单费用汇总End
                    }
                }
            }

            //更新帐单数据
            if ($biFeeQty) {
                //当存在费用时
                $upArr = array(
                    'bcb_status' => 1, //默认为草稿
                    'bcb_amount' => $bcbAmount,
                    'ds_code' => (count($ds_code_arr) == 1 ? current($ds_code_arr) : ''), //超过两种类型 使用空值
                    'sm_code' => (count($sm_code_arr) == 1 ? current($sm_code_arr) : ''),
                    // 'bcb_confirm_id' => $this->_userId,
                    // 'bcb_confirm_time' => $this->_date,
                );
                if (!Service_BilCustomerBill::update($upArr, $billId, 'bcb_id')) {
                    throw new Exception("建立帐单错误");
                }

                //汇总费用
                $this->AddCustomerBillFee($billId);

            } else {
                //没有费用明细，删除帐单，返回失败
                throw new Exception("没有找到匹配的费用记录");
            }

            //处理期初、期末 由于未审核先设置为0
            $spArr = array(
                'bcb_id' => $billId,
                'bcbs_period_begin_surplus' => 0,
                'bcbs_total_amount' => $bcbAmount,
                'bcbs_period_end_surplus' => 0,
                'bcbs_update_time' => $this->_date,
            );
            Service_BilCustomerBillSurplus::add($spArr);

            //帐单日志
            $log = array(
                'bcb_id' => $billId,
                'bcbl_status_from' => '1',
                'bcbl_status_to' => '1',
                'bcbl_note' => '建立客户帐单',
                'bcbl_ip' => Common_Common::getIP(),
                'bcbl_creator_id' => $this->_userId,
                'bcbl_add_time' => $this->_date,
            );
            Service_BilCustomerBillLog::add($log);

            $db->commit();

            $result['state'] = 1;
            $result['message'] = '操作成功';
            $result['data'] = array(
                'code' => $bcb_code,
                'amount' => $bcbAmount,
                'customerCode' => $customerCode,
            );
        } catch (Exception $e) {
            $db->rollBack();
            $result['state'] = 0;
            $result['message'] = $e->getMessage();
        }
        return $result;
    }


    /**
     * @desc 根据客户代码生成帐单
     * @param array $param
     * @return array
     */
    public function createBillByCustomerTransaction($param = array())
    {
        $result = array("state" => 0, "message" => "", "data" => array());

        $db = Common_Common::getAdapter();
        $db->beginTransaction();
        try {
            //参考结构
            $customerCode = $param['customer_code'];
            $billDate = $param['bill_date'];
            $dsCode = $param['ds_code'];
            $note = $param['note'];
            $reference_no = $param['reference_no'];
            $billDateFrom = $param['billDateFrom'];
            $sm_code = $param['sm_code'];

            //获取客户信息
            $customerRow = Service_Customer::getByField($customerCode, 'customer_code');
            if (empty($customerRow)) {
                throw new Exception("客户代码：" . $customerCode . ' 不存在');
            }
            $customerCode = $customerRow['customer_code'];

            $bcb_code = $this->getCustomerBillCode();
            //建立帐单主表信息
            $bcbAmount = 0;
            $biFeeQty = 0; //用于记录关联帐单条数
            $ds_code_arr = array();
            $sm_code_arr = array();
            $billArr = array(
                'customer_code' => $customerRow['customer_code'],
                'bcb_status' => 0,
                'bcb_code' => $bcb_code,
                'bcb_reference_no' => $reference_no,
                'bcb_amount' => 0,
                'currency_code' => $customerRow['customer_currency'],
                'bcb_bill_start_date' => $billDateFrom,
                'bcb_bill_date' => $billDate,
                'bcb_writeoff_sign' => 'n',
                'ds_code' => $dsCode,
                'sm_code' => '',
                'bcb_note' => $note,
                'bcb_creator_id' => $this->_userId,
                'bcb_create_type' => '0', //人工创建
                'bcb_add_time' => $this->_date,
                'bcb_review_sign' => 'n',
                'bcb_update_time' => $this->_date,

            );
            if (!$billId = Service_BilCustomerBill::add($billArr)) {
                throw new Exception("建立帐单失败");
            }
            $ds_code_par = explode(',',$dsCode);
            $ds_code_arr = array();
            //赛维区外、区内订单分开出账单
            if(!empty($ds_code_par)){
                foreach($ds_code_par as $key=>$val){
                    if($val!=''){
                        $ds_code_arr[] = $val;
                    }else{
                        $ds_code_arr = array();
                        break;
                    }
                }
            }
            //应收费用
            $incomeCondition = array(
                //'ds_code' => $dsCode,
                'customer_code' => $customerCode,
                'bi_chargeable_time_start'=>$billDateFrom,
                'bi_chargeable_time' => $billDate, //小于此日期
                'sm_code'=>$sm_code
            );
            if(!empty($ds_code_arr)){
                $incomeCondition['ds_code_arr'] = $ds_code_arr;
            }

            $total = Service_BilIncome::getJoinInnerBusinessByCondition($incomeCondition, 'count(*)');
            if (!$total) {
                throw new Exception("没有找到匹配的费用记录");
            }

            $loop = 0;
            $pageSize = 2000; //每次三百,遍历处理
            $pageCount = ceil($total / $pageSize);
            while ($loop <= $pageCount) {
                $loop++;
                $businessIncomeRows = Service_BilIncome::getJoinInnerBusinessByCondition($incomeCondition, '*', $pageSize, 1, array("bil_business.bb_id asc"));
                //按单号进行生成帐单明细、
                foreach ($businessIncomeRows as $iv) {

                    //类型统计
                    $ds_code_arr[$iv['ds_code']] = $iv['ds_code'];
                    $sm_code_arr[$iv['sm_code']] = $iv['sm_code'];

                    //记录条数
                    $biFeeQty++;

                    //更新帐单ID、状态修改已出帐
                    Service_BilIncome::update(array('bi_status' => 3, 'bcb_id' => $billId, 'bi_update_time' => $this->_date), $iv['bi_id'], 'bi_id');

                    //帐单的对照关系表
                    $mapArr = array(
                        'bcb_id' => $billId,
                        'bi_id' => $iv['bi_id'],
                    );
                    Service_BilCustomerBillMap::add($mapArr);

                    //帐单费用汇总Start
                    $bcbAmount += $iv['bi_amount'];
                    //帐单费用汇总End
                }
            }

            //更新帐单数据
            if ($biFeeQty) {
                //当存在费用时
                $upArr = array(
                    'bcb_status' => 1, //默认为草稿
                    'bcb_amount' => $bcbAmount,
                    'ds_code' => (count($ds_code_arr) == 1 ? current($ds_code_arr) : ''), //超过两种类型 使用空值
                    'sm_code' => (count($sm_code_arr) == 1 ? current($sm_code_arr) : ''),
                    // 'bcb_confirm_id' => $this->_userId,
                    // 'bcb_confirm_time' => $this->_date,
                );
                if (!Service_BilCustomerBill::update($upArr, $billId, 'bcb_id')) {
                    throw new Exception("建立帐单错误");
                }

                //汇总费用
                $this->AddCustomerBillFee($billId);

            } else {
                //没有费用明细，删除帐单，返回失败
                throw new Exception("没有找到匹配的费用记录");
            }

            //处理期初、期末 由于未审核先设置为0
            $spArr = array(
                'bcb_id' => $billId,
                'bcbs_period_begin_surplus' => 0,
                'bcbs_total_amount' => $bcbAmount,
                'bcbs_period_end_surplus' => 0,
                'bcbs_update_time' => $this->_date,
            );
            Service_BilCustomerBillSurplus::add($spArr);

            //帐单日志
            $log = array(
                'bcb_id' => $billId,
                'bcbl_status_from' => '1',
                'bcbl_status_to' => '1',
                'bcbl_note' => '建立客户帐单',
                'bcbl_ip' => Common_Common::getIP(),
                'bcbl_creator_id' => $this->_userId,
                'bcbl_add_time' => $this->_date,
            );
            Service_BilCustomerBillLog::add($log);

            $db->commit();
            //$db->rollBack();

            $result['state'] = 1;
            $result['message'] = '操作成功';
            $result['data'] = array(
                'code' => $bcb_code,
                'amount' => $bcbAmount,
                'customerCode' => $customerCode,
                'currency_code' => $customerRow['customer_currency'],
            );
        } catch (Exception $e) {
            $db->rollBack();
            $result['state'] = 0;
            $result['message'] = $e->getMessage();
        }
        return $result;
    }

    /**
     * @desc 汇总帐单各类型
     * @param $billId
     * @return int //返回总金额
     */
    private function AddCustomerBillFee($billId)
    {
        //返回总金额
        $amount = 0;
        $feesRows = Table_BilIncome::getInstance()->getBillFeeGroup($billId);
        foreach ($feesRows as $fRow) {
            $amount += $fRow['amount'];
            $sumArr = array(
                'bcb_id' => $billId,
                'ds_code' => $fRow['ds_code'],
                'sm_code' => $fRow['sm_code'],
                'ft_code' => $fRow['ft_code'],
                'bcbf_amount' => $fRow['amount'],
                'bcbf_add_time' => $this->_date,
            );
            Service_BilCustomerBillFee::add($sumArr);
        }
        return $amount;
    }


    /**
     * @desc 审核客户帐单
     * @param array $param
     * @return array
     */
    public function checkCustomerBillTransaction($param = array())
    {
        $result = array("state" => 0, "message" => "", "code" => "");

        $db = Common_Common::getAdapter();
        $db->beginTransaction();
        try {
            //参数
            $opType = $param['opType'];
            $code = $param['code'];
            $note = $param['note'];
            $is_sales_verify = $param['is_sales_verify']; //是否验证销售员

            //帐单号不能为空
            if (empty($code)) {
                throw new Exception("帐单不存在");
            }

            $result['code'] = $code;

            //获取帐单信息
            $billRow = Service_BilCustomerBill::getByField($code, 'bcb_code');
            if (empty($billRow)) {
                throw new Exception('不存在');
            }

            $statusTo = $billRow['bcb_status'];
            $bilCustomerBillStatusArr = Process_BillConfig::bilCustomerBillStatus('auto');

            //是否成功更新
            $op = true;
            //根据操作类型进行
            switch ($opType) {
                case '0': //作废
                    $statusTo = 0;
                    //判断帐单状态
                    if ($billRow['bcb_status'] != '1') {
                        throw new Exception("状态[" . ($bilCustomerBillStatusArr[$billRow['bcb_status']]) . '],不允许操作');
                    }
                    $upRow = array(
                        'bcb_status' => 0,
                        'bcb_review_sign' => 'n',
                        'bcb_delete_id' => $this->_userId,
                        'bcb_delete_time' => $this->_date,
                        'bcb_update_time' => $this->_date,
                    );
                    $op = Service_BilCustomerBill::update($upRow, $billRow['bcb_id'], 'bcb_id');
                    //清理应付
                    if (!Service_BilIncome::update(array('bcb_id' => 0, 'bi_status' => 2, 'bi_update_time' => $this->_date), $billRow['bcb_id'], 'bcb_id')) {
                        throw new Exception("更新应付明细失败");
                    }
                    break;
                case '1': //确认
                    $statusTo = 2;
                    //判断帐单状态
                    if ($billRow['bcb_status'] != '1') {
                        throw new Exception("状态[" . ($bilCustomerBillStatusArr[$billRow['bcb_status']]) . '],不允许操作');
                    }
                    //判断销售员
                    if ($is_sales_verify) {
                        $customerRow = Service_Customer::getByField($billRow['customer_code'], 'customer_code', array("customer_saler_user_id"));
                        if ($customerRow['customer_saler_user_id'] != '0' && $customerRow['customer_saler_user_id'] != $this->_userId) {
                            //throw new Exception("您没有权限操作当前帐单");
                        }
                    }
                    $upRow = array(
                        'bcb_status' => 2,
                        'bcb_review_sign' => 'n',
                        'bcb_confirm_id' => $this->_userId,
                        'bcb_confirm_time' => $this->_date,
                        'bcb_update_time' => $this->_date,
                    );
                    $op = Service_BilCustomerBill::update($upRow, $billRow['bcb_id'], 'bcb_id');

                    break;
                case '2': //审核
                    //判断帐单状态
                    if ($billRow['bcb_status'] != '2') {
                        throw new Exception("状态[" . ($bilCustomerBillStatusArr[$billRow['bcb_status']]) . '],不允许操作');
                    }

                    $customerRow = Service_Customer::getByField($billRow['customer_code'], 'customer_code');

                    //重置汇总费用
                    Service_BilCustomerBillFee::delete($billRow['bcb_id'], 'bcb_id');
                    //帐单总金额
                    $totalAmount = $this->AddCustomerBillFee($billRow['bcb_id']);

                    $customerBalanceArr = Table_AccountPaymentRecords::getInstance()->getTotal('cm', $billRow['customer_code'], 1);
                    $amount = empty($customerBalanceArr['amount']) || !isset($customerBalanceArr['amount']) ? 0 : $customerBalanceArr['amount'];
                    $holdAmount = empty($customerBalanceArr['writeoff_amount']) || !isset($customerBalanceArr['writeoff_amount']) ? 0 : $customerBalanceArr['writeoff_amount'];
                    $balance = $amount - $holdAmount;

                    //渐减
                    $billAmount = $totalAmount;
                    //帐单核销状态
                    $bcb_writeoff_sign = 'n';

                    //预付 月结 都自动核销
                    /*if ($customerRow['cash_type'] != '') {
                        //核销帐单
                        $oCondition = array(
                            'apr_type' => '1',
                            'so_code' => 'cm',
                            'payer_code' => $billRow['customer_code'],
                            'apr_settle_sign' => 'y',
                            'apr_writeoff_sign' => 'n',
                        );
                        $aprRows = Service_AccountPaymentRecords::getByCondition($oCondition, '*', 0, 0, array("apr_transaction_time asc"));
                        foreach ($aprRows as $aRow) {
                            $break = false;
                            $aprAmount = $aRow['apr_amount'] - $aRow['apr_writeoff_amount'];
                            //异常
                            if (!$aprAmount) {
                                throw new Exception("金额异常");
                            }
                            if ($billAmount <= $aprAmount) {
                                $apr_writeoff_sign = $billAmount == $aprAmount ? 'y' : 'n';
                                $aprAmount = $billAmount;
                                $break = true;
                                //已扣完
                                $billAmount = 0;
                                $bcb_writeoff_sign = 'y';
                                Service_AccountPaymentRecords::update(array('apr_writeoff_amount' => ($aRow['apr_writeoff_amount'] + $aprAmount), 'apr_writeoff_sign' => $apr_writeoff_sign), $aRow['apr_id'], 'apr_id');
                            } else {
                                $billAmount -= $aprAmount;
                                $bcb_writeoff_sign = 'p';
                                Service_AccountPaymentRecords::update(array('apr_writeoff_amount' => ($aRow['apr_writeoff_amount'] + $aprAmount), 'apr_writeoff_sign' => 'y'), $aRow['apr_id'], 'apr_id');
                            }

                            //核销记录
                            $brArr = array(
                                'bcbbr_amount' => $aprAmount,
                                'apr_id' => $aRow['apr_id'],
                                'bcb_id' => $billRow['bcb_id'],
                                'bcbbr_surplus_amount' => $balance - $aprAmount,
                                'bcbbr_creator_id' => 0,
                                'bcbbr_add_time' => $this->_date,
                            );
                            Service_BilCustomerBillBalanceRecords::add($brArr);

                            if ($break) {
                                break;
                            }
                        }
                    }*/

                    //处理期初 期末
                    $spArr = array(
                        'bcbs_period_begin_surplus' => $balance, //期初=当时余额
                        'bcbs_total_amount' => 0,
                        'bcbs_period_end_surplus' => $balance - $totalAmount, //期末
                        'bcbs_update_time' => $this->_date,
                    );
                    Service_BilCustomerBillSurplus::update($spArr, $billRow['bcb_id'], 'bcb_id');

                    //当余额不及时，产生催款通知单
                    if ($balance - $totalAmount < 0) {
                        $noteArr = array(
                            'so_code' => 'cm',
                            'payer_code' => $billRow['customer_code'],
                            'fcn_code' => Common_GetNumbers::getCode('feeCreditNote', '', 'CN'),
                            'fcn_reference_no' => $billRow['bcb_code'],
                            'fcn_status' => 1,
                            'fcn_desc' => "帐单费用",
                            'fcn_amount' => abs($balance - $totalAmount),
                            'fcn_currency_code' => $billRow['currency_code'],
                            'fcn_creator_id' => $this->_userId,
                            'bcb_id' => $billRow['bcb_id'],
                            'fcn_add_time' => $this->_date,
                        );
                        Service_FeeCreditNote::add($noteArr);
                    }
                    //已全部核销转为已完结
                    $statusTo = $bcb_writeoff_sign == 'y' ? 4 : 3;
                    $upRow = array(
                        'bcb_status' => $statusTo,
                        'bcb_amount' => $totalAmount,
                        'bcb_review_sign' => 'n',
                        'bcb_writeoff_sign' => $bcb_writeoff_sign,
                        'bcb_verifier_id' => $this->_userId,
                        'bcb_finish_user' => $this->_userId,
                        'bcb_verify_time' => $this->_date,
                        'bcb_finish_time' => $this->_date,
                        'bcb_update_time' => $this->_date,
                    );
                    $op = Service_BilCustomerBill::update($upRow, $billRow['bcb_id'], 'bcb_id');
                    //扣除税金
                    $rate = Service_CustomerProcess::getTaxRate( $billRow['customer_code'] );
                    if( $rate === false  )
                        throw new Exception('该客户未设置签约主体');
                    $constant = Service_CustomerProcess::getConstant();

                    $tax = round( ( $totalAmount / $constant ) * $rate, 2 );
                    if( $tax > 0 ) {
                        $balanceRow = Service_CustomerBalance::getByField( $billRow['customer_code'], 'customer_code' );
                        $balanceRow = Service_CustomerBalance::getForUpdate($balanceRow['cb_id']);

                        $newTax = $balanceRow['frozen_tax_amount'] - $tax;
                        if( $newTax < 0 )
                            throw new Exception( '可用税金不足' );


                        $uResult = Service_CustomerBalance::update( array( 'frozen_tax_amount'=>$newTax, 'cb_update_time'=> $this->_date ), $balanceRow['cb_id'], 'cb_id');
                        if( !$uResult )
                            throw new Exception( '扣取税金失败' );
                        //费用日志
                        $log['cbl_type'] = 7;
                        $log['cbl_value'] = $tax;
                        $log['customer_id'] = $customerRow['customer_id'];
                        $log['customer_code'] = $customerRow['customer_code'];;
                        $log['cbl_note'] = '用户账单审核，扣除税金';
                        $log['cbl_current_value'] = $balanceRow['cb_value'];
                        $log['cbl_current_hold_value'] = $balanceRow['cb_hold_value'];
                        $log['currency_rate'] = 1;
                        $log['currency_code'] = $customerRow['customer_currency'];
                        $log['user_id'] = $this->_userId;
                        $log['cbl_refer_code'] = $code;
                        $log['fee_id'] = 0;
                        $log['arrive_time'] = $this->_date;
                        $log['application_code'] = 0;
                        $log['cbl_add_time'] = $this->_date;
                        $log['cbl_transaction_value'] = $tax;

                        $logResult = Service_CustomerBalanceLog::add( $log );
                        if( !$logResult )
                            throw new Exception('写入扣税金日志失败');
                    }

                    break;
                case '3': //审核不通过,根据帐单当前状态
                    $upRow['bcb_review_sign'] = 'y';
                    $upRow['bcb_update_time'] = $this->_date;
                    switch ((int)$billRow['bcb_status']) {
                        case 1:
                            //判断销售员
                            if ($is_sales_verify) {
                                $customerRow = Service_Customer::getByField($billRow['customer_code'], 'customer_code', array("customer_saler_user_id"));
                                if ($customerRow['customer_saler_user_id'] != '0' && $customerRow['customer_saler_user_id'] != $this->_userId) {
                                    throw new Exception("您没有权限操作当前帐单");
                                }
                            }
                            $upRow['bcb_confirm_id'] = $this->_userId;
                            $upRow['bcb_confirm_time'] = $this->_date;
                            break;
                        case 2:
                            //审核状态，不通过
                            $upRow['bcb_verifier_id'] = $this->_userId;
                            $upRow['bcb_verify_time'] = $this->_date;
                            break;
                        default:
                            throw new Exception("状态[" . ($bilCustomerBillStatusArr[$billRow['bcb_status']]) . '],不允许操作');
                            break;
                    }

                    //判断帐单状态
                    if ($billRow['bcb_status'] != '2') {
                        throw new Exception("状态[" . ($bilCustomerBillStatusArr[$billRow['bcb_status']]) . '],不允许操作');
                    }
                    //更新帐单
                    $op = Service_BilCustomerBill::update($upRow, $billRow['bcb_id'], 'bcb_id');

                    break;
                case '4':
                    $statusTo = 1;
                    //回退帐单，移至草稿状态
                    //判断帐单状态
                    if ($billRow['bcb_status'] != '2') {
                        throw new Exception("状态[" . ($bilCustomerBillStatusArr[$billRow['bcb_status']]) . '],不允许操作');
                    }

                    $upRow = array(
                        'bcb_status' => '1', //草稿
                        'bcb_review_sign' => 'y',
                        'bcb_verifier_id' => $this->_userId,
                        'bcb_verify_time' => $this->_date,
                        'bcb_update_time' => $this->_date,
                    );
                    $op = Service_BilCustomerBill::update($upRow, $billRow['bcb_id'], 'bcb_id');

                    break;
                default:
                    throw new Exception("操作类型异常");
                    break;
            }

            if (!$op) {
                throw new Exception("更新帐单信息失败");
            }

            //帐单日志
            $log = array(
                'bcb_id' => $billRow['bcb_id'],
                'bcbl_status_from' => $billRow['bcb_status'],
                'bcbl_status_to' => $statusTo,
                'bcbl_note' => $note,
                'bcbl_ip' => Common_Common::getIP(),
                'bcbl_creator_id' => $this->_userId,
                'bcbl_add_time' => $this->_date,
            );
            Service_BilCustomerBillLog::add($log);

            $db->commit();

            $result['state'] = 1;
            $result['message'] = '操作成功';

        } catch (Exception $e) {
            $db->rollBack();
            $result['state'] = 0;
            $result['message'] = $e->getMessage();
        }
        return $result;

    }


    /**
     * @desc 根据帐单号进行核销帐单
     * @param array $param
     * @return array
     */
    public function writeOffBillTransaction($param = array())
    {
        $result = array("state" => 0, "message" => "", "data" => array());

        $db = Common_Common::getAdapter();
        $db->beginTransaction();
        try {
            $apr_id = isset($param['apr_id']) ? $param['apr_id'] : '';
            $code = $param['code'];
            if ($code == '') {
                throw new Exception("单号不能为空");
            }
            $result['data']['code'] = $code;
            $result['data']['involvedAmount'] = 0;
            //获取帐单信息
            $billRow = Service_BilCustomerBill::getByField($code, 'bcb_code');
            if (empty($billRow)) {
                throw new Exception('不存在');
            }
            //状态
            $bilCustomerBillStatusArr = Process_BillConfig::bilCustomerBillStatus('auto');
            if ($billRow['bcb_status'] != '3') {
                throw new Exception("状态[" . ($bilCustomerBillStatusArr[$billRow['bcb_status']]) . '],不允许操作');
            }
            if ($billRow['bcb_writeoff_sign'] == 'y') {
                throw new Exception("已核销");
            }

            $customerBalanceArr = Table_AccountPaymentRecords::getInstance()->getTotal('cm', $billRow['customer_code'], 1);
            $amount = empty($customerBalanceArr['amount']) || !isset($customerBalanceArr['amount']) ? 0 : $customerBalanceArr['amount'];
            $holdAmount = empty($customerBalanceArr['writeoff_amount']) || !isset($customerBalanceArr['writeoff_amount']) ? 0 : $customerBalanceArr['writeoff_amount'];
            //当前可核销金额
            $balance = $amount - $holdAmount;

            //渐减
            $writeOffAmount = Table_BilCustomerBillBalanceRecords::getInstance()->getTotalAmount($billRow['bcb_id']);
            //未核销金额
            $billAmount = $involvedAmount = $notWriteOffAmount = $billRow['bcb_amount'] - $writeOffAmount;
            //帐单核销状态
            $bcb_writeoff_sign = $billRow['bcb_writeoff_sign'];

            //根据收款记录,核销帐单
            $oCondition = array(
                'apr_type' => '1',
                'so_code' => 'cm',
                'payer_code' => $billRow['customer_code'],
                'apr_settle_sign' => 'y',
                'apr_writeoff_sign' => 'n',
                'apr_id' => $apr_id, //指定收款记录
            );
            $aprRows = Service_AccountPaymentRecords::getByCondition($oCondition, '*', 0, 0, array("apr_transaction_time asc"));

            if (empty($aprRows)) {
                throw new Exception("没有足够的余额");
            }
            foreach ($aprRows as $aRow) {
                $break = false;
                $aprAmount = $aRow['apr_amount'] - $aRow['apr_writeoff_amount'];
                //异常
                if (!$aprAmount) {
                    throw new Exception("金额异常");
                }
                
                //本次可以核销的金额
                $involvedAmount -= $aprAmount;

                if ($billAmount <= $aprAmount) {
                    $apr_writeoff_sign = $billAmount == $aprAmount ? 'y' : 'n';
                    $aprAmount = $billAmount;
                    $break = true;
                    //已扣完
                    $billAmount = 0;
                    $bcb_writeoff_sign = 'y';
                    $aprUpRow = array('apr_writeoff_amount' => ($aRow['apr_writeoff_amount'] + $aprAmount), 'apr_writeoff_sign' => $apr_writeoff_sign);

                } else {
                    $billAmount -= $aprAmount;
                    $bcb_writeoff_sign = 'p';
                    $aprUpRow = array('apr_writeoff_amount' => ($aRow['apr_writeoff_amount'] + $aprAmount), 'apr_writeoff_sign' => 'y');
                }

                //更新收款记录\增加已核销金额
                Service_AccountPaymentRecords::update($aprUpRow, $aRow['apr_id'], 'apr_id');

                //核销记录
                $balance -= $aprAmount;
                $brArr = array(
                    'bcbbr_amount' => $aprAmount,
                    'apr_id' => $aRow['apr_id'],
                    'bcb_id' => $billRow['bcb_id'],
                    'bcbbr_surplus_amount' => $balance,
                    'bcbbr_creator_id' => $this->_userId,
                    'bcbbr_add_time' => $this->_date,
                );
                Service_BilCustomerBillBalanceRecords::add($brArr);

                if ($break) {
                    break;
                }
            }

            //已全部核销转为已完结
            $statusTo = $bcb_writeoff_sign == 'y' ? 4 : 3;

            $upRow = array(
                'bcb_status' => $statusTo,
                'bcb_writeoff_sign' => $bcb_writeoff_sign,
                'bcb_finish_user' => $this->_userId,
                'bcb_finish_time' => $this->_date,
                'bcb_update_time' => $this->_date,
            );
            Service_BilCustomerBill::update($upRow, $billRow['bcb_id'], 'bcb_id');

            //帐单日志
            $log = array(
                'bcb_id' => $billRow['bcb_id'],
                'bcbl_status_from' => $billRow['bcb_status'],
                'bcbl_status_to' => $statusTo,
                'bcbl_note' => "核销金额:" . ($involvedAmount < 0 ? $notWriteOffAmount : $notWriteOffAmount - $involvedAmount),
                'bcbl_ip' => Common_Common::getIP(),
                'bcbl_creator_id' => $this->_userId,
                'bcbl_add_time' => $this->_date,
            );
            Service_BilCustomerBillLog::add($log);

            $db->commit();

            $result['data']['involvedAmount'] = ($involvedAmount < 0 ? $notWriteOffAmount : $notWriteOffAmount - $involvedAmount);
            $result['state'] = 1;
            $result['message'] = "操作成功";
        } catch (Exception $e) {
            $db->rollBack();
            $result['state'] = 0;
            $result['message'] = $e->getMessage();
        }
        return $result;
    }

    /**
     * @desc 导出帐单明细
     * @param string $code
     * @return array
     */
    public static function getBillData($code = '')
    {
        //中英文对应
        $keyMap = array(
            "A" => '客户代码',
            "B" => '客户单号',
            "C" => '订单号',
            "D" => '跟踪号',
            "E" => '运输方式',
            "F" => '收货时间',
            "G" => '收件国家(CN)',
            "H" => '国家简称',
            "I" => '收货重量(KG)',
            "J" => '运费',
            "K" => '其它',
            "L" => '合计',
        );

        $row = Service_BilCustomerBill::getByField($code, 'bcb_code');

        $condition = array(
            'bcb_id' => $row['bcb_id']
        );
        $businessRows = Table_BilIncome::getInstance()->getBillGroupByCondition($condition, '*', 0);

        $csvData = array();
        $csvData[1] = $keyMap; //Excel 表头

        $countryArr = array();

        //总重 金额
        $totalWeight = $totalShipping = $totalOther = 0;

        $i = 2; //从第二行开始
        //单据
        foreach ($businessRows as $business) {
            //相加
            $totalWeight += $business['bb_charge_weight'];

            $k = $i++;
            $csvData[$k] = array(
                "A" => $business['customer_code'],
                "B" => $business['bb_reference_no'],
                "C" => $business['bb_refer_code'],
                "D" => $business['bb_service_number'],
                "E" => $business['sm_code'],
                "F" => $business['bb_charge_time'],
                "G" => '',
                "H" => $business['country_code'],
                "I" => $business['bb_charge_weight'],
                "J" => 0,
                "K" => 0,
                "L" => 0,
            );

            if (!empty($business['country_code']) && !isset($countryArr[$business['country_code']])) {
                $cRow = Service_Country::getByField($business['country_code'], 'country_code', array('country_name'));
                $csvData[$k]['G'] = $countryArr[$business['country_code']] = isset($cRow['country_name']) ? $cRow['country_name'] : '';
            } else {
                $csvData[$k]['G'] = '';
            }

            //同帐单及单据下各费用汇总
            $feeRows = Table_BilIncome::getInstance()->getBusinessFeeGroup($business['bb_id'], $row['bcb_id']);

            //目前只区别两种费用类型
            $shipping = $otherFee = 0;
            foreach ($feeRows as $fee) {
                if (strtolower($fee['ft_code']) == 'shipping') {
                    $shipping += $fee['amount'];
                } else {
                    $otherFee += $fee['amount'];
                }
            }

            $totalShipping += $shipping;
            $totalOther += $otherFee;

            $csvData[$k]['J'] = $shipping;
            $csvData[$k]['K'] = $otherFee;
            $csvData[$k]['L'] = $otherFee + $shipping;

        }
        //汇总重量、金额
        $csvData[count($csvData) + 1] = array(
            "H" => '合计',
            "I" => $totalWeight,
            "J" => $totalShipping,
            "K" => $totalOther,
            "L" => $totalShipping + $totalOther,
        );

        return $csvData;
    }


    /**
     * @desc 导出帐单明细
     * @param string $code
     * @return array
     */
    public static function getBillDetailData($code = '')
    {

        $row = Service_BilCustomerBill::getByField($code, 'bcb_code');
        return self::getwgBillDetailData($code);//暂时用一个模版
        switch($row['customer_code']){
            case 'E8888':
                return self::getwgBillDetailData($code);
                break;
            default:
                return self::getCommonBillDetailData($code);
        }

    }
    /**
     * @author william-fan
     * @todo 普通客户账单
     */
    public static function getCommonBillDetailData($code){
        //中英文对应
        $keyMap = array(
            "A" => '客户代码',
            "B" => '客户单号',
            "C" => '订单号',
            "D" => '跟踪号',
            "E" => '运输方式',
            "F" => '收货时间',
            "G" => '出货时间',
            "H" => '收件国家(CN)',
            "I" => '国家简称',
            "J" => '收货重量(KG)',
            "K" => '运输费',
            "L" => '提货费',
            "M" => '仓储费',
            "N" => '操作费',
            "O" => '单票费用',
            "P" => '中转运输费',
            "Q" => '报关费',
            "R" => '入库费',
            'S' => '燃油附加费',
            'T'=> '国内监管车运输费',
            'U'=> '香港派送费（非荃湾）',
            'V'=> '香港派送费（荃湾）',
            "W" => '其它',
            "X" => '小计',
        );

        $feeKey = array(
            'tihuo' => 'l',
            'cangchu' => 'm',
            'caozuo' => 'n',
            'yunshu' => 'p',
            'one_ticket_fee' => 'o',
            'shipping' => 'k',
            'baoguan' => 'q',
            'ruku'=>'r',
            'additional_fee'=>'s',
            'Transport Charge'=>'t',
            'paisong'=>'u',
            'paisong2'=>'v'
        );

        $row = Service_BilCustomerBill::getByField($code, 'bcb_code');

        $condition = array(
            'bcb_id' => $row['bcb_id']
        );
        $businessRows = Table_BilIncome::getInstance()->getBillGroupByCondition($condition, '*', 0);

        $csvData = array();
        $csvData[1] = $keyMap; //Excel 表头

        $countryArr = array();

        //总重 金额
        $totalWeight = $totalShipping = $totalOther = $totalFee = 0;

        $i = 2; //从第二行开始
        //单据
        foreach ($businessRows as $business) {
            //相加
            $totalWeight += $business['bb_charge_weight'];

            $k = $i++;
            $csvData[$k] = array(
                "A" => $business['customer_code'],
                "B" => $business['bb_reference_no'],
                "C" => $business['bb_refer_code'],
                "D" => $business['bb_service_number'],
                "E" => $business['sm_code'],
                "F" => $business['bb_charge_time'],
                "G" => $business['bb_declared_time'],
                "I" => $business['country_code'],
                "J" => $business['bb_charge_weight'],

            );

            if (!empty($business['country_code'])) {
                if(!isset($countryArr[$business['country_code']])){
                    $cRow = Service_Country::getByField($business['country_code'], 'country_code', array('country_name'));
                    $csvData[$k]['H'] = $countryArr[$business['country_code']] = isset($cRow['country_name']) ? $cRow['country_name'] : '';
                }else{
                    $csvData[$k]['H'] = $countryArr[$business['country_code']] = isset($cRow['country_name']) ? $cRow['country_name'] : '';
                }
            } else {
                $csvData[$k]['H'] = '';
            }

            //同帐单及单据下各费用汇总
            $feeRows = Table_BilIncome::getInstance()->getBusinessFeeGroup($business['bb_id'], $row['bcb_id']);
            //没有匹配类型时，统计放其它费用里面
            $otherFee = 0;
            $oFee = 0;
            foreach ($feeRows as $fee) {
                $ftCode = strtolower($fee['ft_code']);
                if (!isset($feeKey[$ftCode])) {
                    $otherFee += $fee['amount'];
                } else {
                    $csvData[$k][strtoupper($feeKey[$ftCode])] = $fee['amount'];
                }
                $oFee += $fee['amount'];
                $totalFee += $fee['amount'];
            }

            //其它
            $csvData[$k]['W'] = $otherFee;
            $csvData[$k]['X'] = $oFee;

        }

        //汇总重量、金额
        $csvData[count($csvData) + 1] = array(
            "W" => '合计(' . $row['currency_code'] . ')',
            "X" => $totalFee,
        );
        return $csvData;
    }
    /**
     * @author william-fan
     * @todo 获取沃光的账单
     */
    public static function getwgBillDetailData($code){
        //中英文对应
        $keyMap = array(
            "A" => '序号',
            "B" => '公司代码',
            "C" => '账单月份',
            "D" => '账单编码',
            "E" => '订单号',
            "F" => '交易订单号',
            "G" => '订单模式',
            "H" => '仓库',
            "I" => '订单类型',
            "J" => '运输方式代码',
            "K"  => '运输方式名称',
            "L" => '服务商单号',
            "M" => '国家（中文',
            "N" => '国家（英文）',
            "O" => '国家（二字码）',
            "P" => '是否偏远',
            "Q" => '计费重量（KG）',
            "R" => '提交时间',
            'S' => '完成时间',
            'T'=> '国内海关总申报价值',
            'U'=> '目的海关申报总价',
            'V'=> '包裹件数',
            "W" => '产品数量',
            "X" => '体积（长*宽*高CM）',
            "Y"=> '体积重（KG）',
            "Z"=> '实重(KG)',
            'AA'=>'收费重量',
            'AB'=>'分区',
            'AC'=>'报价+首重（重量KG）',
            'AD'=>'续重',
            'AE'=>'挂号+操作费',
            'AF'=>'折扣',
            'AG'=>'报价备注',
            'AH'=>'重量收费',
            'AI'=>'单票收费',
            'AJ'=>'偏远费',
            'AK'=>'燃油费率',
            'AL'=>'燃油费',
            'AM'=>'其它附加费',
            'AN'=>'附加费备注',
            'AO'=>'报关费',
            'AP'=>'物流操作费',
            'AQ'=>'库内拣货费',
            'AR'=>'入库费',
            'AS'=>'中港运输费',
            'AT'=>'监管车费',
            'AU'=>'仓租',
            'AV'=>'物料费',
            'AW'=>'管理费',
            'AX'=>'香港派送费',//香港派送费
            'AY'=>'运费赔偿',
            'AZ'=>'货物赔偿',
            'BA'=>'其它损失赔偿',
            'BB'=>'优惠+补贴+其它费用',
            'BC'=>'费用备注',
            'BD'=>'合计（RMB）',
            'BE'=>'合计（USD）',
            'BF'=>'合计（HKD）',
            'BG'=>'合计费用（客户币种）',
            'BH'=>'备注'
        );

        /*$feeKey = array(
            'one_ticket_fee' => 'AI',
            'PIANYUAN'=>'AJ',
            'additional_fee'=>'AL',
            'BAOGUAN'=>'AO',
            'WULIUCAOZUO'=>'AP',
            'CAOZUO'=>'AQ',
            'ruku'=>'AR',
            'yunshu' => 'AS',
	        'JIANGUANYUNSHU'=>'AT',
            'cangchu' => 'AU',
	        'WULIAO'=>'AV',
            'GUANLI'=>'AW'
        );*/

        $feeKey = array(
            'shipping' => 'AH',
            'one_ticket_fee' => 'AI',
            'pianyuan'=>'AJ',
            'additional_fee'=>'AL',
            'baoguan'=>'AO',
            'wuliucaozuo'=>'AP',
            'caozuo'=>'AQ',
            'ruku'=>'AR',
            'yunshu' => 'AS',
            'jianguanyunshu'=>'AT',
            'cangchu' => 'AU',
            'wuliao'=>'AV',
            'guanli'=>'AW',
            //'paisong'=>'AX',
        );

        $row = Service_BilCustomerBill::getByField($code, 'bcb_code');


        $condition = array(
            'bcb_id' => $row['bcb_id']
        );
        $businessRows = Table_BilIncome::getInstance()->getBillGroupByCondition($condition, '*', 0);
        //print_r($businessRows);

        $csvData = array();
        $csvData[1] = $keyMap; //Excel 表头

        $countryArr = array();

        //总重 金额
        $totalWeight = $totalShipping = $totalOther = $totalFee = 0;

        $orderModelArr = array(
            '0'=>'备货模式',
            '1'=>'集货模式',
        );
        $warehouses = Service_Warehouse::getByCondition();
        $warehouseArr = array();
        foreach($warehouses as $key=>$warehouse){
            $warehouseArr[$warehouse['warehouse_id']] = $warehouse['warehouse_code'];
        }
        //订单类型
        $orderTypeArr = array(
            '0'=>'普通',
            '1'=>'转仓',
            '2'=>'退货'
        );
        $remoteCityArr = array(
            '0'=>'否',
            '1'=>'是'
        );
        $isRemote='';
        $shipMethods = Service_ShippingMethod::getByCondition();
        $shipMethodArr = array();
        foreach($shipMethods as $key=>$ship){
            $shipMethodArr[$ship['sm_code']] = $ship;
        }

        //print_r($shipMethodArr);exit;

        $i = 2; //从第二行开始
        $no = 0;
        //单据
        foreach ($businessRows as $business) {
            //相加
            $totalWeight += $business['bb_charge_weight'];
            if(!isset($month)){
                $month = date("m",strtotime($business['bi_chargeable_time']));
            }

            $k = $i++;
            $no++;
            $orderModel = '';
            $order = Service_Orders::getByField($business['bb_refer_code'],'order_code');
            $warehouseCode = '';
            $orderType='';
            $sm_name ='';
            $isRemote='';
            $sbDeclareValue=0;
            $mdDeclareValue=0;
            $pNumber = 0;
            $volDetail='';
            $feeRate='';
            if(!empty($order)){
                if(isset($orderModelArr[$order['order_mode_type']])){
                    $orderModel = $orderModelArr[$order['order_mode_type']];
                }
                if(isset($warehouseArr[$order['warehouse_id']])){
                    $warehouseCode = $warehouseArr[$order['warehouse_id']];
                }
                if(isset($orderTypeArr[$order['order_type']])){
                    $orderType = $orderTypeArr[$order['order_type']];
                }
                if(isset($shipMethodArr[$order['sm_code']])){
                    $sm_name = $shipMethodArr[$order['sm_code']]['sm_name_cn'];
                }
                if(isset($remoteCityArr[$order['is_remote_city']])){
                    $isRemote = $remoteCityArr[$order['is_remote_city']];
                }
                $opcondition = array(
                    'order_code'=>$business['bb_refer_code'],
                );
                $orderProducts = Service_OrderProduct::getByCondition($opcondition,'*');
                foreach($orderProducts as $key=>$oproduct){
                    $sbDeclareValue += $oproduct['op_declared_value']*$oproduct['op_quantity'];
                    $mdDeclareValue += $oproduct['op_purpose_declared_value']*$oproduct['op_quantity'];
                    $pNumber+=$oproduct['op_quantity'];
                }

                $addressRow = Service_OrderAddressBook::getByField($order['order_code'], 'order_code');
                $smCondition = array(
                    'warehouse_id_arr' => array(0, (int) $order['warehouse_id']),
                    'sm_code'          => $order['sm_code'],
                    'country_id'       => (int) $addressRow['oab_country_id'],
                );
                //dhl有城市区别
                if(strtoupper($order['sm_code'])=='HKDHL'){
                    if($addressRow['oab_country_id']=='253'){
                        //美国的要加入城市
                        $conditionCity = array(
                            'country_id'=>$addressRow['oab_country_id'],
                            'city_name'=>$addressRow['oab_city']
                        );
                        $citys = Service_City::getByCondition($conditionCity,'*',1,1);
                        if(!empty($citys)){
                            $smCondition['city_id_arr'] = array(0, $citys[0]['city_id']);
                        }
                    }
                }
                $logisticOrder = Service_LogisticsCost::getByField($order['order_code'],'order_code');
                $feeRate=$shipMethodArr[$order['sm_code']]['sm_baf'];
            }
            $smArea = Service_SmCountryMap::getByCondition($smCondition,'*',1,1,array('warehouse_id desc', 'country_id desc', 'province_id desc', 'city_id desc'));


            $trackingNumber = '';
            $shipOrder = Service_ShipOrder::getByField($business['bb_refer_code'],'order_code');
            //print_r($shipOrder);


            $Total = 0;
            if(!empty($shipOrder)){
                $trackingNumber = $shipOrder['tracking_number'];
                //获取ship_order_detail
                $smCondition = array(
                    'order_code'=>$business['bb_refer_code']
                );
                $shipOrdersDetails = Service_ShipOrderDetail::getByCondition($smCondition,'*');
                //print_r($shipOrdersDetails);
                foreach($shipOrdersDetails as $key=>$so){
                    if($key==0){
                        $volDetail.=$so['sod_length'].'*'.$so['sod_width'].'*'.$so['sod_height'];
                    }else{
                        $volDetail.='+'.$so['sod_length'].'*'.$so['sod_width'].'*'.$so['sod_height'];
                    }
                }
                $order_operation_time = Service_OrderOperationTime::getByField($shipOrder['order_id'],'order_id');
                
                $smRow = Service_ShippingMethod::getByField($shipOrder['sm_code'],"sm_code","*");

                if($smRow['sm_is_volume']!="1") {
                    $Total = 1;
                }else{
                    $sodList = Service_ShipOrderDetail::getByCondition(array('order_id'=>$shipOrder['order_id']),"*");
                    if($smRow['sm_code']=="HKDHL"){
                        $Total = count($sodList);
                    }else{
                        foreach($sodList as $sodRow){
                            if($sodRow['sub_tracking_number']!="") $Total++;
                        }
                    }
                }

            }

            if($Total<1) $Total = 1;

            $sz = '';
            $xz = '';
            $sm_code = strtoupper($order['sm_code']);

            $swcondition = array(
                'sm_id' => $shipMethodArr[$sm_code]['sm_id'],
                'area_arr' => array("{$smArea[0]['area']}"),
                'warehouse_id_arr' => array(0, $order['warehouse_id']),
                'swp_fee_type' => $shipMethodArr[$sm_code]['sm_fee_type']
            );
            $smPriceRow = Table_ShipWeightPrice::getInstance()->getByInCondition($swcondition, '*', array('warehouse_id desc'));
            if(!empty($smPriceRow)){
                $xz = $smPriceRow["swp_exces_price"];
                $sz =$smPriceRow["swp_price"];
            }

            if ($business['bi_create_type']=="0") {
                $business['bi_note']='';
            }

            $csvData[$k] = array(
                "A" => $no,
                "B" => $business['customer_code'],
                'C'=>$month,
                "D" => '',
                "E" => $business['bb_refer_code'],
                "F" => $business['bb_reference_no'],
                "G" => $orderModel,
                "H" => $warehouseCode,
                "I" => $orderType,
                "J" => $order['sm_code'],
                "K"  => $sm_name,
                "L" => $trackingNumber,
                //"M" => '国家（中文',
                //"N" => '国家（英文）',
                //"O" => '国家（二字码）',
                "P" => $isRemote,
                "Q" => $business['bb_charge_weight'],
                "R" => $order_operation_time['submit_time'],//$business['bi_add_time'],
                'S' => $business['bi_chargeable_time'],
                'T'=> $sbDeclareValue,
                'U'=> $mdDeclareValue,
                'V'=> $Total,//$business['bb_pieces'],
                "W" => $pNumber,
                "X" => $volDetail,
                "Y"=> $shipOrder['so_vol_weight'],
                "Z"=> $shipOrder['so_weight'],
                'AA'=>$shipOrder['so_fee_weight']>0?$shipOrder['so_fee_weight']:$business['bb_charge_weight'],
                'AB'=>$smArea[0]['area'],
                'AC'=>$sz,
                'AD'=>$xz,
                'AE'=>'0',
                'AF'=>'',
                'AG'=>'报价备注',
                'AH'=>'',//$logisticOrder['transport_fee'],
                //'AI'=>'单票收费',
                //'AJ'=>'偏远费',
                'AK'=>$feeRate,//'燃油费率',
                //'AL'=>'燃油费',
                'AM'=>'',
                'AN'=>'',//'附加费备注',
                //'AO'=>'报关费',
                //'AP'=>'物流操作费',
                //'AQ'=>'库内拣货费',
                //'AR'=>'入库费',
                //'AS'=>'中港运输费',
                //'AT'=>'监管车费',
                //'AU'=>'仓租',
                //'AV'=>'物料费',
                //'AW'=>'管理费',
                'AX'=>'0',
                'AY'=>'',
                'AZ'=>'',
                'BA'=>'',
                'BB'=>'',
                //'BC'=>'合计',
                'BD'=>'',//'备注',
                'BE'=>'',
                'BF'=>'',
                'BG'=>'',
                'BH'=>$business['bi_note'],
            );

            if (!empty($business['country_code'])) {
                if(!isset($countryArr[$business['country_code']])){
                    $cRow = Service_Country::getByField($business['country_code'], 'country_code', array('country_name','country_name_en'));
                    $csvData[$k]['M'] = $countryArr[$business['country_code']]['country_name'] = isset($cRow['country_name']) ? $cRow['country_name'] : '';
                    $csvData[$k]['N'] = $countryArr[$business['country_code']]['country_name_en'] = isset($cRow['country_name_en']) ? $cRow['country_name_en'] : '';
                    $csvData[$k]['O'] = $business['country_code'];
                }else{
                    $csvData[$k]['M'] = $countryArr[$business['country_code']]['country_name'];
                    $csvData[$k]['N'] = $countryArr[$business['country_code']]['country_name_en'];
                    $csvData[$k]['O'] = $business['country_code'];
                }
            } else {
                $csvData[$k]['M'] = '';
                $csvData[$k]['N'] = '';
                $csvData[$k]['O'] = '';
            }



            //同帐单及单据下各费用汇总
            $feeRows = Table_BilIncome::getInstance()->getBusinessFeeGroup($business['bb_id'], $row['bcb_id']);
            $feeRowsOrg = Table_BilIncome::getInstance()->getBusinessFeeGroupOrg($business['bb_id'], $row['bcb_id']);
            $feeRowsRmb = Table_BilIncome::getInstance()->getBusinessFeeGroupRmb($business['bb_id'], $row['bcb_id']);
            //没有匹配类型时，统计放其它费用里面
            $otherFee = 0;
            $oFee = 0;
            $totalFeeRmb = 0;
            $totalFeeCustomer = 0;

            $paisong=0; //香港派送费
            $notOtherFeeArr = array(
                //'shipping',
                'paisong',
                'paisong2',
            );
            //print_r($feeRows);
            foreach ($feeRowsOrg as $fee) {
                $ftCode = strtolower($fee['ft_code']);
                if (!isset($feeKey[$ftCode])) {
                    if($ftCode=='paisong'||$ftCode=='paisong2'){
                        $paisong += $fee['org_amount'];
                    }
                    if(!in_array($ftCode,$notOtherFeeArr)){
                        $otherFee += $fee['org_amount'];
                    }
                } else {
                    $csvData[$k][strtoupper($feeKey[$ftCode])] = $fee['org_amount'];
                }
                //$oFee += $fee['org_amount'];
                //$totalFee += $fee['org_amount'];
            }
            foreach ($feeRowsRmb as $fee) {
                $totalFeeRmb += $fee['rmb_amount'];
            }
            foreach ($feeRows as $fee) {
                $totalFeeCustomer += $fee['amount'];
            }
            $totalFeeUsd=Service_Currency::converByCode('RMB','USD',$totalFeeRmb);
            $totalFeeHkd=Service_Currency::converByCode('RMB','HKD',$totalFeeRmb);
            //其它
            $csvData[$k]['AM'] = $otherFee;
            $csvData[$k]['AX'] = $paisong;
            $csvData[$k]['BD'] = $totalFeeRmb;
            $csvData[$k]['BE'] = $totalFeeUsd;
            $csvData[$k]['BF'] = $totalFeeHkd;
            $csvData[$k]['BG'] = $totalFeeCustomer;
        }
        //汇总重量、金额
        /*$csvData[count($csvData) + 1] = array(
            "W" => '合计(' . $row['currency_code'] . ')',
            "X" => $totalFee,
        );*/


        return $csvData;
    }
    /**
     * [getsjBillDetailData 使用上佳模板导出数据]
     * @param  [type] $code [账单号]
     * @return [type]       [description]
     */
    public static function getsjBillDetailData($code){
        //中英文对应
        /*$keyMap = array(
            "A" => '序号',
            "B" => '集货模式',
            "C" => '订单号',
            "D" => '交易订单号',
            "E" => '服务商单号',
            "F" => '交货日期',
            "G" => '出货日期',
            "H" => '收货类型',
            "I" => '出货件数',
            "J" => '产品数量',
            "K" => '出货重量（KG）',
            "L" => '计费重量（KG）',
            "M" => '目的国家',
            "N" => '走货渠道',
            "O" => '分区',
            "P" => '提货费（RMB）',              //TIHUO
            "Q" => '库内拣货费',                 //取的操作费，caozuo
            "R" => '入库费',                     //RUKU
            'S' => '仓租',                       //取仓储，CANGCHU
            'T'=> '报关费（RMB）',               //BAOGUAN
            'U'=> '集中申报费（RMB）',           //JZSBF
            'V'=> '香港派送费（RMB）',           //PAISONG,PAISONG2
            "W" => '物流运输费（RMB）',          //重量费用+燃油费+偏远费+单票费
            "X" => '中港运输费',                 //取的中转运输费，yunshu
            "Y"=> '监管车费',                    //取的监管运输，jianguanyunshu
            "Z"=> '其他费用（RMB）',
            'AA'=>'其他费用备注',
            'AB'=>'合计收费（RMB）',
            'AC'=>'合计费用(客户币种)',
            'AD'=>'备注'
        );*/

        $feeKey = array(
            'tihuo'             => 'P',
            'caozuo'            => 'Q',
            'ruku'              => 'R',
            'cangchu'           => 'S',
            'baoguan'           => 'T',
            'jzsbf'             => 'U',
            //'xgpsf'             => 'V',
            //'wlysf'             => 'W',
            'yunshu'            => 'X',
            'jianguanyunshu'    => 'Y'
        );

        $xgpsf = array('paisong','paisong2');//香港派送费
        $wlysf = array('shipping','one_ticket_fee','pianyuan','additional_fee');//重量费，单票费，派送费，燃油费

        $csvData = array();

        $row = Service_BilCustomerBill::getByField($code, 'bcb_code');

        if(isset($row['bcb_bill_date'])){
            $csvData[0]['month'] = date("Y年m月",strtotime($row['bcb_bill_date']));
        }
        $csvData[0]['code'] = $code;

        $condition = array(
            'bcb_id' => $row['bcb_id']
        );
        $businessRows = Table_BilIncome::getInstance()->getBillGroupByCondition($condition, '*', 0);

        //$csvData[1] = $keyMap; //Excel 表头

        $countryArr = array();

        //总重 金额
        $totalWeight = $totalShipping = $totalOther = $totalFee = 0;

        $orderModelArr = array(
            '0'=>'备货模式',
            '1'=>'集货模式',
        );
        //仓库
        $warehouses = Service_Warehouse::getByCondition();
        $warehouseArr = array();
        foreach($warehouses as $key=>$warehouse){
            $warehouseArr[$warehouse['warehouse_id']] = $warehouse['warehouse_code'];
        }
        //订单类型
        $orderTypeArr = array(
            '0'=>'普通',
            '1'=>'转仓',
            '2'=>'退货'
        );
        $remoteCityArr = array(
            '0'=>'否',
            '1'=>'是'
        );
        $isRemote='';
        $shipMethods = Service_ShippingMethod::getByCondition();
        $shipMethodArr = array();
        foreach($shipMethods as $key=>$ship){
            $shipMethodArr[$ship['sm_code']] = $ship;
        }

        //print_r($shipMethodArr);exit;

        $i = 1; //从第二行开始,第一行放统一信息
        $no = 0;
        $total_fee = array();

        //单据
        foreach ($businessRows as $business) {
            //相加
            $totalWeight += $business['bb_charge_weight'];
            if(!isset($month)){
                $month = date("m",strtotime($business['bi_chargeable_time']));
            }

            $k = $i++;
            $no++;
            $orderModel = '';
            $order = Service_Orders::getByField($business['bb_refer_code'],'order_code');
            $warehouseCode = '';
            $orderType='';
            $sm_name ='';
            $isRemote='';
            $sbDeclareValue=0;
            $mdDeclareValue=0;
            $pNumber = 0;
            $volDetail='';
            $feeRate='';
            if(!empty($order)){
                if(isset($orderModelArr[$order['order_mode_type']])){
                    $orderModel = $orderModelArr[$order['order_mode_type']];
                }
                if(isset($warehouseArr[$order['warehouse_id']])){
                    $warehouseCode = $warehouseArr[$order['warehouse_id']];
                }
                if(isset($orderTypeArr[$order['order_type']])){
                    $orderType = $orderTypeArr[$order['order_type']];
                }
                if(isset($shipMethodArr[$order['sm_code']])){
                    $sm_name = $shipMethodArr[$order['sm_code']]['sm_name_cn'];
                }
                if(isset($remoteCityArr[$order['is_remote_city']])){
                    $isRemote = $remoteCityArr[$order['is_remote_city']];
                }
                $condition = array(
                    'order_code'=>$business['bb_refer_code'],
                );
                $orderProducts = Service_OrderProduct::getByCondition($condition,'*');
                foreach($orderProducts as $key=>$oproduct){
                    $sbDeclareValue += $oproduct['op_declared_value'];
                    $mdDeclareValue += $oproduct['op_purpose_declared_value'];
                    $pNumber+=$oproduct['op_quantity'];
                }

                $addressRow = Service_OrderAddressBook::getByField($order['order_code'], 'order_code');
                $smCondition = array(
                    'warehouse_id_arr' => array(0, (int) $order['warehouse_id']),
                    'sm_code'          => $order['sm_code'],
                    'country_id'       => (int) $addressRow['oab_country_id'],
                );
                //dhl有城市区别
                if(strtoupper($order['sm_code'])=='HKDHL'){
                    if($addressRow['oab_country_id']=='253'){
                        //美国的要加入城市
                        $conditionCity = array(
                            'country_id'=>$addressRow['oab_country_id'],
                            'city_name'=>$addressRow['oab_city']
                        );
                        $citys = Service_City::getByCondition($conditionCity,'*',1,1);
                        if(!empty($citys)){
                            $smCondition['city_id_arr'] = array(0, $citys[0]['city_id']);
                        }
                    }
                }
               $logisticOrder = Service_LogisticsCost::getByField($order['order_code'],'order_code');
                $feeRate=$shipMethodArr[$order['sm_code']]['sm_baf'];
                $smArea = Service_SmCountryMap::getByCondition($smCondition,'*',1,1,array('warehouse_id desc', 'country_id desc', 'province_id desc', 'city_id desc'));
            }

            $trackingNumber = '';
            $shipOrder = Service_ShipOrder::getByField($business['bb_refer_code'],'order_code');
            //print_r($shipOrder);

            $Total = 0;
            if(!empty($shipOrder)){
                //服务商单号
                $trackingNumber = $shipOrder['tracking_number'];
                //交货日期
                $order_operation_time = Service_OrderOperationTime::getByField($shipOrder['order_id'],'order_id');
            
                $smRow = Service_ShippingMethod::getByField($shipOrder['sm_code'],"sm_code","*");

                if($smRow['sm_is_volume']!="1") {
                    $Total = 1;
                }else{
                    $sodList = Service_ShipOrderDetail::getByCondition(array('order_id'=>$shipOrder['order_id']),"*");
                    if($smRow['sm_code']=="HKDHL"){
                        $Total = count($sodList);
                    }else{
                        foreach($sodList as $sodRow){
                            if($sodRow['sub_tracking_number']!="") $Total++;
                        }
                    }
                }
            }

            if($Total<1) $Total = 1;
            if ($business['bi_create_type']=="0") {
                $business['bi_note']='';
            }
            $csvData[$k] = array(
                "A" => $no,
                "B" => $orderModel,
                "C" => $business['bb_refer_code'],
                "D" => $business['bb_reference_no'],
                "E" => $trackingNumber,
                "F" => isset($order_operation_time['submit_time']) ? $order_operation_time['submit_time'] : '',//$order_operation_time['submit_time'],//$business['bi_add_time'],
                "G" => $business['bb_declared_time'],
                "H" => '包裹',
                "I" => $Total,
                "J" => $pNumber,
                "K" => $business['bb_delivered_weight'],
                "L" => (isset($shipOrder['so_fee_weight']) && $shipOrder['so_fee_weight']>0)?$shipOrder['so_fee_weight']:$business['bb_charge_weight'],
                //"M" => '目的国家',
                "N" => $order['sm_code'],
                "O" => isset($smArea[0]['area']) ? $smArea[0]['area'] : '',
                "P" => '',//提货费
                "Q" => '',//AQ库内简练费
                "R" => '',//'AR'=>'入库费',
                "S" => '',//'AU'=>'仓租',
                "T" =>'',//'AO'=>'报关费',
                "U" =>'',//集中申报费
                "V" => 0,//'AX'=>'0',香港派送费
                //"W" => '',物流运输费
                "X" => '',//'AS'=>'中港运输费',
                "Y" => '',//'AT'=>'监管车费',
                //"Z" => '',//其他费用
                "AA" => '',//其他费用备注
                //"AB" => '',//'BC'=>'合计',
                //"AC" => '','BD'=>'',//'备注',
                'AD' => $business['bi_note'],
            );

            if (!empty($business['country_code'])) {
                if(!isset($countryArr[$business['country_code']])){
                    $cRow = Service_Country::getByField($business['country_code'], 'country_code', array('country_name','country_name_en'));
                    $csvData[$k]['M'] = $countryArr[$business['country_code']]['country_name'] = isset($cRow['country_name']) ? $cRow['country_name'] : '';
                }else{
                    $csvData[$k]['M'] = $countryArr[$business['country_code']]['country_name'];
                }
            } else {
                $csvData[$k]['M'] = '';
            }

            //$wlysf_fee = round($logisticOrder['transport_fee']/$logisticOrder['currency_rate'],4);//重量收费

            //同帐单及单据下各费用汇总
            $feeRowsOrg = Table_BilIncome::getInstance()->getBusinessFeeGroupOrg($business['bb_id'], $row['bcb_id']);
            $feeRows = Table_BilIncome::getInstance()->getBusinessFeeGroup($business['bb_id'], $row['bcb_id']);
            $feeRowsRmb = Table_BilIncome::getInstance()->getBusinessFeeGroupRmb($business['bb_id'], $row['bcb_id']);
            //没有匹配类型时，统计放其它费用里面

            //print_r($feeRows);
            //$str = '';

            $otherFee = 0;
            $xgpsf_fee = 0;
            $wlysf_fee = 0;
            //$totalFeeOrg = 0;
            $totalFeeCustomer = 0;
            $totalFeeRmb = 0;

            foreach ($feeRowsOrg as $fee) {

                $ftCode = strtolower($fee['ft_code']);
                if(in_array($ftCode, $wlysf)){
                    $wlysf_fee += $fee['org_amount'];//物流运输费

                }else if(in_array($ftCode,$xgpsf)){

                    $xgpsf_fee += $fee['org_amount'];//香港派送费

                }else if(!isset($feeKey[$ftCode])){
                    $otherFee += $fee['org_amount'];//其他费用     
                }else{
                    $csvData[$k][strtoupper($feeKey[$ftCode])] = $fee['org_amount'];
                }

                //$totalFeeOrg += $fee['org_amount'];
            }

            foreach ($feeRows as $fee) {
                $totalFeeCustomer += $fee['amount'];
            }
            foreach ($feeRowsRmb as $fee) {
                $totalFeeRmb += $fee['rmb_amount'];
            }

            //其它
            $csvData[$k]['W'] = $wlysf_fee;
            $csvData[$k]['V'] = $xgpsf_fee;
            $csvData[$k]['Z'] = $otherFee;
            $csvData[$k]['AB'] = $totalFeeRmb;
            $csvData[$k]['AC'] = $totalFeeCustomer;

            //$csvData[$k]['费用和'] = $totalFee;
            //$csvData[$k]['费用'] = $str;
            //$csvData[$k]['重量收费'] =  $logisticOrder['customer_currency_code'] ? round($logisticOrder['transport_fee']/$logisticOrder['customer_currency_code'],4) : '';

            $total_fee['I'] = isset($total_fee['I']) ? ($total_fee['I'] + $csvData[$k]['I']) : $csvData[$k]['I']; 
            $total_fee['J'] = isset($total_fee['J']) ? ($total_fee['J'] + $csvData[$k]['J']) : $csvData[$k]['J'];
            $total_fee['K'] = isset($total_fee['K']) ? ($total_fee['K'] + $csvData[$k]['K']) : $csvData[$k]['K'];
            $total_fee['L'] = isset($total_fee['L']) ? ($total_fee['L'] + $csvData[$k]['L']) : $csvData[$k]['L'];
            $total_fee['K'] = isset($total_fee['K']) ? ($total_fee['K'] + $csvData[$k]['K']) : $csvData[$k]['K'];
            $total_fee['P'] = isset($total_fee['P']) ? ($total_fee['P'] + $csvData[$k]['P']) : $csvData[$k]['P'];
            $total_fee['Q'] = isset($total_fee['Q']) ? ($total_fee['Q'] + $csvData[$k]['Q']) : $csvData[$k]['Q'];
            $total_fee['R'] = isset($total_fee['R']) ? ($total_fee['R'] + $csvData[$k]['R']) : $csvData[$k]['R'];
            $total_fee['S'] = isset($total_fee['S']) ? ($total_fee['S'] + $csvData[$k]['S']) : $csvData[$k]['S'];
            $total_fee['T'] = isset($total_fee['T']) ? ($total_fee['T'] + $csvData[$k]['T']) : $csvData[$k]['T'];
            $total_fee['U'] = isset($total_fee['U']) ? ($total_fee['U'] + $csvData[$k]['U']) : $csvData[$k]['U'];
            $total_fee['V'] = isset($total_fee['V']) ? ($total_fee['V'] + $csvData[$k]['V']) : $csvData[$k]['V'];
            $total_fee['W'] = isset($total_fee['W']) ? ($total_fee['W'] + $csvData[$k]['W']) : $csvData[$k]['W'];
            $total_fee['X'] = isset($total_fee['X']) ? ($total_fee['X'] + $csvData[$k]['X']) : $csvData[$k]['X'];
            $total_fee['Y'] = isset($total_fee['Y']) ? ($total_fee['Y'] + $csvData[$k]['Y']) : $csvData[$k]['Y'];
            $total_fee['Z'] = isset($total_fee['Z']) ? ($total_fee['Z'] + $csvData[$k]['Z']) : $csvData[$k]['Z'];
            $total_fee['AB'] = isset($total_fee['AB']) ? ($total_fee['AB'] + $csvData[$k]['AB']) : $csvData[$k]['AB'];
            $total_fee['AC'] = isset($total_fee['AC']) ? ($total_fee['AC'] + $csvData[$k]['AC']) : $csvData[$k]['AC'];
        }
        $csvData[] = $total_fee;
        $csvData[]['tallage'] = array('rmb'=>round($total_fee['AB']*0.06,6),'cCurrency'=>round($total_fee['AC']*0.06,6)) ;
        $csvData[]['all'] =  array('rmb'=>round($total_fee['AB']*0.06 + $total_fee['AB'],6),'cCurrency'=>round($total_fee['AB']*0.06 + $total_fee['AB'],6));
        //var_dump($csvData);
        //exit();
        return $csvData;       
    }

    public static function getswBillDetailData($code){
        //中英文对应
        /*$keyMap = array(
            "A" => '序号',
            "B" => '模式',             //区外
            "C" => '客户单号',
            "D" => '订单号',
            "E" => '服务商单号',
            "F" => '出货日期',         //取收货时间，receiving_batch_order表add_time
            "G" => '客户重量',
            "H" => '目的国家',
            "I" => '分区',
            "J" => '走货渠道',
            "K" => '提货费',           //空
            "L" => '运费',
            "M" => '挂号费',
            "N" => '其他费',           //空
            "O" => '合计费用（RMB）',
            "P" => '合计费用（HKD）',  //空
            "Q" => '备注1',            //空
            "R" => '备注2',            //空       
        );*/

        $feeKey = array(
            'one_ticket_fee' => 'M',//挂号费取单票费
            'shipping'       => 'L',//区外仓运费在logistics_cost表中没存
        );

        $csvData = array();

        $row = Service_BilCustomerBill::getByField($code, 'bcb_code');

        if(isset($row['bcb_bill_date'])){
            $csvData[0]['month'] = date("Y年m月",strtotime($row['bcb_bill_date']));
        }
        $csvData[0]['code'] = $code;

        $condition = array(
            'bcb_id' => $row['bcb_id']
        );
        $businessRows = Table_BilIncome::getInstance()->getBillGroupByCondition($condition, '*', 0);

        //$csvData[1] = $keyMap; //Excel 表头

        $countryArr = array();

        $shipMethods = Service_ShippingMethod::getByCondition();
        $shipMethodArr = array();
        foreach($shipMethods as $key=>$ship){
            $shipMethodArr[$ship['sm_code']] = $ship;
        }

        //print_r($shipMethodArr);exit;

        $i = 1; //从第二行开始,第一行放统一信息
        $no = 0;
        $total_fee = array();

        //单据
        foreach ($businessRows as $business) {

            $k = $i++;
            $no++;

            //$order = Service_Orders::getByField($business['bb_refer_code'],'order_code');
            $order = Service_LogisticsOrder::getByField($business['bb_refer_code'],'logistics_code');

            $sm_name ='';
            if(!empty($order)){

                if(isset($shipMethodArr[$order['sm_code']])){
                    $sm_name = $shipMethodArr[$order['sm_code']]['sm_name_cn'];
                }

                $addressRow = Service_LogisticsOrderAddress::getByField($order['logistics_code'], 'logistics_code');
                $smCondition = array(
                    'warehouse_id_arr' => array(0, (int) $order['warehouse_id']),
                    'sm_code'          => $order['sm_code'],
                    'country_id'       => (int) $addressRow['loa_country'],
                );
                //dhl有城市区别
                if(strtoupper($order['sm_code'])=='HKDHL'){
                    if($addressRow['loa_country']=='253'){
                        //美国的要加入城市
                        $conditionCity = array(
                            'country_id'=>$addressRow['loa_country'],
                            'city_name'=>$addressRow['loa_city']
                        );
                        $citys = Service_City::getByCondition($conditionCity,'*',1,1);
                        if(!empty($citys)){
                            $smCondition['city_id_arr'] = array(0, $citys[0]['city_id']);
                        }
                    }
                }
                $smArea = Service_SmCountryMap::getByCondition($smCondition,'*',1,1,array('warehouse_id desc', 'country_id desc', 'province_id desc', 'city_id desc'));
            }
            //$logisticOrder = Service_LogisticsOrder::getByField($business['bb_refer_code'],'logistics_code');

            $logisticCost = Service_LogisticsCost::getByField($business['bb_refer_code'],'order_code');
            //出货日期、客户重量
            $receivingBatchOrder = Service_ReceivingBatchOrder::getByField($business['bb_refer_code'],'logistics_code');
            //客户重量
            //$parcel_detail = Service_ParcelDetail::getByField($business['bb_refer_code'],'order_code');

            $trackingNumber = '';
            $shipOrder = Service_ShipOrder::getByField($business['bb_refer_code'],'order_code');
            
            if(!empty($shipOrder)){
                //服务商单号
                $trackingNumber = $shipOrder['tracking_number'];
            }

            //if($Total<1) $Total = 1;
            if ($business['bi_create_type']=="0") {
                $business['bi_note']='';
            }
            $csvData[$k] = array(
                "A" => $no,
                "B" => '区外',
                "C" => isset($order['reference_no']) ? $order['reference_no'] : '',
                "D" => isset($business['bb_refer_code']) ? $business['bb_refer_code'] : '',
                "E" => isset($order['ref_tracking_number']) ? $order['ref_tracking_number'] : '',
                "F" => isset($receivingBatchOrder['add_time']) ? $receivingBatchOrder['add_time'] : '',
                "G" => isset($receivingBatchOrder['weight']) ? $receivingBatchOrder['weight'] : '',
                //"H" => '目的国家',
                "I" => $smArea[0]['area'],
                "J" => $order['sm_code'],
                "K" => '',
                "L" => '',//$logisticCost['transport_fee'],//RMB
                'M' => '',
                'N' => '',
                'O' => '',
                'P' => '',
                'Q' => '',
                'R' => '',
                'Q' => $business['bi_note'],
            );

            if (!empty($business['country_code'])) {
                if(!isset($countryArr[$business['country_code']])){
                    $cRow = Service_Country::getByField($business['country_code'], 'country_code', array('country_name','country_name_en'));
                    $csvData[$k]['H'] = $countryArr[$business['country_code']]['country_name'] = isset($cRow['country_name']) ? $cRow['country_name'] : '';
                }else{
                    $csvData[$k]['H'] = $countryArr[$business['country_code']]['country_name'];
                }
            } else {
                $csvData[$k]['H'] = '';
            }

            //同帐单及单据下各费用汇总
            $feeRows = Table_BilIncome::getInstance()->getBusinessFeeGroup($business['bb_id'], $row['bcb_id']);
            $feeRmbRows = Table_BilIncome::getInstance()->getBusinessFeeGroupRMB($business['bb_id'], $row['bcb_id']);
            $feeRmbOrg = Table_BilIncome::getInstance()->getBusinessFeeGroupOrg($business['bb_id'], $row['bcb_id']);

            //没有匹配类型时，统计放其它费用里面
            //print_r($feeRows);
            //$str = '';
            //
            $totalFeeRmb = 0;
            $totalFeeCustomer = 0;
            
            foreach ($feeRmbOrg as $fee) {
                $ftCode = strtolower($fee['ft_code']);
                if(array_key_exists($ftCode, $feeKey)){
                    $csvData[$k][strtoupper($feeKey[$ftCode])] = $fee['org_amount'];//$fee['amount'];//单票费（此处作为挂号费）运费
                }
            }            
            foreach ($feeRmbRows as $fee) {
                $ftCode = strtolower($fee['ft_code']);
                if(array_key_exists($ftCode, $feeKey)){
                    $totalFeeRmb += $fee['rmb_amount'];
                }
            }
            foreach ($feeRows as $fee) {
                $ftCode = strtolower($fee['ft_code']);
                if(array_key_exists($ftCode, $feeKey)){
                    $totalFeeCustomer += $fee['amount'];
                }
            }

            $csvData[$k]['O'] = $totalFeeRmb;
            $csvData[$k]['P'] = $totalFeeCustomer;
            $total_fee['G'] = isset($total_fee['G']) ? ($total_fee['G'] + $csvData[$k]['G']) : $csvData[$k]['G'];
            $total_fee['L'] = isset($total_fee['L']) ? ($total_fee['L'] + $csvData[$k]['L']) : $csvData[$k]['L'];
            $total_fee['M'] = isset($total_fee['M']) ? ($total_fee['M'] + $csvData[$k]['M']) : $csvData[$k]['M'];
            $total_fee['O'] = isset($total_fee['O']) ? ($total_fee['O'] + $csvData[$k]['O']) : $csvData[$k]['O'];
            $total_fee['P'] = isset($total_fee['P']) ? ($total_fee['P'] + $csvData[$k]['P']) : $csvData[$k]['P'];
        }
        $csvData[] = $total_fee;
        $csvData[]['tallage'] = array('rmb'=>round($total_fee['O']*0.06,6),'cCurrency'=>round($total_fee['P']*0.06,6));
        $csvData[]['all'] =  array('rmb'=>round($total_fee['O']*0.06 + $total_fee['O'],6),'cCurrency'=>round($total_fee['P']*0.06 + $total_fee['P'],6));
        return $csvData;       
    }

    /**
     * @desc 导出帐单（按费用类型）
     * @param string $bcb_id
     * @param int $tax //税点
     * @return array
     */
    public static function getBillGroupData($bcb_id = '', $tax = 0,$currencyRow=array())
    {
        $taxNew = $tax / 100;
        $data = Table_BilIncome::getInstance()->getBillFeeTypeGroup($bcb_id);
        $totalAmount = $totalTaxAmount = 0;
        if (!empty($data)) {
            foreach ($data as $key => $val) {
                if (!empty($val['ft_code'])) {
                    $ftRow = Service_FeeType::getByField($val['ft_code'], 'ft_code');
                    $data[$key]['ft_name'] = $ftRow['ft_name_cn'];
                } else {
                    $data[$key]['ft_name'] = '';
                }
                if(!empty($currencyRow)){
                    $amount = Service_Currency::converByCode($currencyRow['currency_code'],$currencyRow['customer_currency_bill'],$val['amount']);
                    $data[$key]['amount'] = $amount;
                    $val['amount'] = $amount;
                }else{
                    $data[$key]['amount'] = round($val['amount'], 2);
                }
                //税点金额
                $data[$key]['tax_amount'] = round($val['amount'] + $val['amount'] * $taxNew, 2);
                $totalAmount += round($val['amount'], 2);
            }
        }
        $data['tax']=$tax;
        $totalTaxAmount = round($totalAmount + $totalAmount * $taxNew, 2);
        $amount_words = Common_AmountTool::get_amount($totalTaxAmount);
        return array('data' => $data, 'totalAmount' => $totalAmount, 'totalTaxAmount' => $totalTaxAmount, 'amountWords' => $amount_words);
    }


}