<?php


namespace app\impexp\controller;


use app\admin\model\ElectricMeter;
use app\admin\model\ElectricMeterType;
use app\admin\model\StaffModel;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

class FormatData
{
    /**
     * 解析导入数据
     * @param string|null $file
     * @param int $sheet
     * @return array
     * @throws \Exception
     */
    public function getData(string $file = null,int $sheet = 0){
        try {
            /* 转码 */
            $file = iconv("utf-8", "gb2312", $file);

            if (empty($file) OR !file_exists($file)) {
                throw new \Exception('文件不存在!');
            }

            /** @var Xlsx $objRead */
            $objRead = IOFactory::createReader('Xlsx');

            if (!$objRead->canRead($file)) {
                /** @var Xls $objRead */
                $objRead = IOFactory::createReader('Xls');

                if (!$objRead->canRead($file)) {
                    throw new \Exception('只支持导入Excel文件！');
                }
            }

            /* 只读内容，可以大幅度提升读取Excel效率 */
            $objRead->setReadDataOnly(true);

            /* 建立excel对象 */
            $obj = $objRead->load($file);

            /* 获取指定的sheet表 */
            $currSheet = $obj->getSheet($sheet);

            if (isset($options['mergeCells'])) {
                /* 读取合并行列 */
                $options['mergeCells'] = $currSheet->getMergeCells();
            }

            /* 取得最大的列号 */
            $columnH = $currSheet->getHighestColumn();
            /* 兼容原逻辑，循环时使用的是小于等于 */
            $columnCnt = Coordinate::columnIndexFromString($columnH);

            /* 获取总行数 */
            $rowCnt = $currSheet->getHighestRow();
            $data   = [];

            /* 读取内容 */
            for ($_row = 1; $_row <= $rowCnt; $_row++) {
                $isNull = true;

                for ($_column = 1; $_column <= $columnCnt; $_column++) {
                    $cellName = Coordinate::stringFromColumnIndex($_column);
                    $cellId   = $cellName . $_row;
                    $cell     = $currSheet->getCell($cellId);

                    if (isset($options['format'])) {
                        /* 获取格式 */
                        $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                        /* 记录格式 */
                        $options['format'][$_row][$cellName] = $format;
                    }

                    if (isset($options['formula'])) {
                        /* 获取公式，公式均为=号开头数据 */
                        $formula = $currSheet->getCell($cellId)->getValue();

                        if (0 === strpos($formula, '=')) {
                            $options['formula'][$cellName . $_row] = $formula;
                        }
                    }

                    if (isset($format) && 'm/d/yyyy' == $format) {
                        /* 日期格式翻转处理 */
                        $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                    }

                    $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());

                    if (!empty($data[$_row][$cellName])) {
                        $isNull = false;
                    }
                }

                /* 判断是否整行数据为空，是的话删除该行数据 */
                if ($isNull) {
                    unset($data[$_row]);
                }
            }
            return $data;
        } catch (\Exception $e) {
            throw $e;
        }
    }

    //获取数据.字段映射.生成查询条件.存储数据.
    //将多维数组拆分成查询条件
    public function parseToTableData(){
        $data = [];

        foreach ($data as $k => $v){
            $condition = [];
            //从第二个元素开始查找
            if($k > 0){
                //抄表单
                $condition['copy'] = [
                    'staff_id' => $this->getStaffId($v['payment_staff']),
                    'em_id' => $this->getEmId($v['em_numb']),
                    'current_number' => $v['current_number'],
                    'current_time' => strtotime($v['current_time']),
                    'current_amount' => 0,
                    'remark' => $v['copy_remark']
                ];

                //插入抄表单数据 返回copy
                $copy_id = '';
                //账单
                $condition['statement'] = [
                    'copy_id' => $copy_id,
                    'emt_id' => $this->getEmType($v['emt_id']),    //电表类型
                    'repo_power' => $v['repo_power'],//报移动电量
                    'yd_unit' => $v['yd_unit'],
                    'financ_unit' => $v['financ_unit'],
                    'recharge' => $v['recharge'],
                    'power_over' => $v['power_over'],
                    'financ_pay' => $v['financ_pay'], //移动结算金额
                    'remark' => $v['state_remark'] //备注
                ];

                //缴费单
                $condition['payment'] = [
                    'cp_id' => $copy_id,
                    'check_numb' => $v['check_numb'],
                    'pay_sid' => $this->getStaffId($v['pay_staff']),
                    'pay_date' => strtotime($v['pay_date']),
                    'tax_rate' => $v['tax_rate'],
                    'tax_amount' =>  $this->calculationTaxAmount($v['financ_pay'],$v['tax_rate']),
                    'pay_way' => $this->getPayWay($v['pay_way']),
                    'invoice_type' => $this->getInvoiceType($v['invoice_type'])
                ];

                //结算单
                $condition['settle'] = [
                    'cp_id' => $copy_id,
                    'client' => $this->getClientId($v['client']),
                    'amount' => $v['amount'],
                    'settle_date' => strtotime($v['settle_date']) ?: time(),
                    'invoice_numb' => $v['invoice_numb'] ?: '000000000',
                    'settle_sid' => $this->getStaffId($v['settle_staff'])
                ];

                $condition['order'] = [
                    'order_id' => '',
                    'em_id' => '',
                    'cp_id' => '',
                    'statement_id' => '',
                    'payment_id' => '',
                    'settle_id' => '',
                    'settle_status' => '',
                    'settle_time' => '',
                    'settle_uid' => '',
                    'payment_status' => '',
                    'payment_time' => '',
                    'payment_uid' => '',
                    'status' => ''
                ];
            }
        }
    }

    /**
     * 通过 财务支出金额与税率 计算税额
     * @param $financ_pay
     * @param $tax_rate
     * @return float
     */
    public function calculationTaxAmount($financ_pay,$tax_rate){
        return round($financ_pay / (1 + $tax_rate) * $tax_rate, 2);
    }

    /**
     * 通过员名字获取ID
     * @param $staff_name
     * @return mixed
     */
    public function getStaffId($staff_name){
        $staff = StaffModel::get(['name'=>$staff_name]);
        return $staff->id;
    }

    /**
     * 获取发票类型
     * @param $invoice
     * @return int
     */
    public function getInvoiceType($invoice){
        if($invoice == '增值税专用发票'){
            return 1;
        } else {
            return 2;
        }
    }

    /**
     * 获取支付方式
     * @param $payway
     * @return int
     */
    public function getPayWay($payway){
        if(is_numeric($payway)){
            return 1;
        } else if($payway == '电汇'){
            return 2;
        } else {
            return 3;
        }
    }

    //通过 电表号查询电表ID
    public function getEmId($emNumb){
        $em = ElectricMeter::get(['number'=>$emNumb]);
        return $em->id;
    }

    //通过电表类型名称 查询电表类型ID
    public function getEmType($emtName){
        $emt = ElectricMeterType::get(['name'=>$emtName]);
        return $emt->id;
    }

    //获取客户类型 移动还是铁塔
    public function getClientId($clientName){
        if($clientName == '移动') return 1;
        else return 2;
    }
}