churen/service/option/lib/Import.php

<?php

namespace Option\Lib;

use Dever;

include('/share/lib/php/dever_package/excel/src/PHPExcel.php');
//include('/usr/share/php/dever_package/excel/src/PHPExcel.php');

class Import
{
    private $path = '/www/churen/data/';
    //private $path = '/data/www/churen/data/';
    private $name = 'qiquan_2022_09_28.xlsx';
    # 导入账户
    public function get_api()
    {
        Dever::config('base')->hook = true;
        $file = $this->path . $this->name;

        $data = Dever::excelImport($file, 1, 5, 'excel');

        $table = array();
        $table['head'] = array('序列号', '公司ID', '姓名', '手机号', '身份证号', '说明');
        $table['body'] = array();

        $table_status = array();
        $table_status['head'] = array('主账号', '子账号');
        $table_status['body'] = array();
        $table_status['body'][0][0] = 0;
        $table_status['body'][0][1] = 0;

        $table_error = array();
        $table_error['head'] = array('序列号', '说明');
        $table_error['body'] = array();

        $check = array();

        if ($data) {
            $member = array();
            foreach ($data as $k => $v) {
                $state = preg_match(Dever::rule('mobile'), $v['C']);
                if ($state) {
                    $key = trim($v['A']);
                    $where['key'] = trim($v['A']);
                    $where['mobile'] = trim($v['C']);
                    $where['company_id'] = (isset($v['F']) && $v['F']) ? trim($v['F']) : 1;
                    $where['clear'] = true;
                    $info = Dever::db('option/member')->one($where);
                    $update = $where;
                    $update['idcard'] = trim($v['D']);
                    $update['name'] = trim($v['B']);
                    $update['main'] = $v['E'] == 1 ? 1 : 2;
                    if (!$info) {
                        $update['status'] = 2;
                        $id = Dever::db('option/member')->insert($update);
                    } else {
                        $update['where_id'] = $info['id'];
                        $id = Dever::db('option/member')->update($update);
                    }
                    if (!$id) {
                        $table['body'][] = array($update['key'], $update['company_id'], $update['name'], $update['mobile'], $update['idcard'], '账户导入失败，请重新导入');
                    } else {
                        if ($update['main'] == 2) {
                            $table_status['body'][0][1] += 1;
                        }
                    }
                    if (isset($check[$key]) && $check[$key] == 1) {

                    } else {
                        $check[$key] = 2;
                    }
                    if ($id && $update['main'] == 1) {
                        $check[$key] = 1;
                        if (!isset($member[$key])) {
                            $member[$key] = array();
                        }
                        $account = Dever::db('option/account')->find(array('mid' => $id));
                        if (!$account) {
                            $member[$key] = Dever::db('option/account')->insert(array('mid' => $id, 'audit' => 1));
                        } else {
                            $member[$key] = $account['id'];
                        }
                        if (!$member[$key]) {
                            $table['body'][] = array($update['key'], $update['company_id'], $update['name'], $update['mobile'], $update['idcard'], '生成主账户失败');
                        } else {
                            $table_status['body'][0][0] += 1;
                        }
                    }
                }
            }

            if ($member) {
                foreach ($member as $k => $v) {
                    $where = array();
                    $where['option_key'] = $k;
                    $where['set_aid'] = $v;
                    $state = Dever::db('option/member')->updates($where);
                }
            }
        }
        $result['导入状态'] = array
        (
            'type' => 'table',
            'content' => $table_status,
        );

        $result['导入异常'] = array
        (
            'type' => 'table',
            'content' => $table,
        );

        foreach ($check as $k => $v) {
            if ($v == 2) {
                $table_error['body'][$k] = array($k, '无主账号');
            }
        }
        $result['数据异常'] = array
        (
            'type' => 'table',
            'content' => $table_error,
        );

        $html = Dever::show('', $result, false, false);
        return $html;
    }

    # 检测文档
    public function check_api()
    {
        Dever::config('base')->hook = true;
        $file = $this->path . $this->name;

        $data = Dever::excelImport($file, 1, 5, 'excel');

        $table = array();
        $table['head'] = array('序列号', '说明');
        $table['body'] = array();

        $table_status = array();
        $table_status['head'] = array('主账号', '子账号');
        $table_status['body'] = array();
        $table_status['body'][0][0] = 0;
        $table_status['body'][0][1] = 0;

        $check = array();
        $check_mobile = array();
        $yes_mobile = array();

        if ($data) {
            $member = array();
            foreach ($data as $k => $v) {
                $state = preg_match(Dever::rule('mobile'), $v['C']);
                if ($state) {
                    $key = $v['A'];
                    $mobile = $v['C'];
                    $company_id = (isset($v['F']) && $v['F']) ? trim($v['F']) : 1;
                    $update = array();
                    $update['main'] = $v['E'] == 1 ? 1 : 2;
                    if (!isset($check[$key])) {
                        $check[$key]['state'] = 2;
                        $check[$key]['num'] = 0;
                    }
                    if (isset($check[$key]) && $check[$key]['state'] == 1) {

                    } else {
                        $check[$key]['state'] = 2;
                    }
                    
                    if ($update['main'] == 1) {
                        $check[$key]['state'] = 1;
                        $check[$key]['num']++;
                        $table_status['body'][0][0] += 1;
                    } else {
                        $table_status['body'][0][1] += 1;
                    }

                    if (!isset($check_mobile[$mobile])) {
                        $check_mobile[$mobile] = 0;
                    }
                    
                    $check_mobile[$mobile]++;

                    /*
                    $member = Dever::db('option/member')->find(array('key' => $key, 'mobile' => $mobile));
                    if ($member) {
                        $yes_mobile[$mobile] = $key;
                    }
                    */
                }
            }
        }

        $result['数据统计'] = array
        (
            'type' => 'table',
            'content' => $table_status,
        );


        foreach ($check as $k => $v) {
            if ($v['state'] == 2) {
                $table['body'][$k] = array($k, '无主账号');
            }
            if ($v['state'] == 1 && $v['num'] > 1) {
                $table['body'][$k] = array($k, '主账户数量等于' . $v['num']);
            }
        }

        foreach ($check_mobile as $k => $v) {
            if ($v > 1) {
                $table['body'][$k] = array($k, '手机号数量为' . $v);
            }
        }

        if ($yes_mobile) {
            foreach ($yes_mobile as $k => $v) {
                $table['body'][$k] = array($k, '账户索引' . $v . '下的手机号' . $k . '已存在');
            }
        }

        $result['数据异常'] = array
        (
            'type' => 'table',
            'content' => $table,
        );
        $html = Dever::show('', $result, false, false);
        return $html;
    }

    # 导入交付记录
    public function get_jiaofu_api()
    {
        Dever::config('base')->hook = true;

        $check = Dever::input('check');

        $file = $this->path . $this->name;

        $data = Dever::excelImport($file, 2, 5, 'excel');

        $table = array();
        $table['head'] = array('公司ID', '手机号', '身份证号', '交付金额', '期权类型', '说明');
        $table['body'] = array();

        $table_status = array();
        $table_status['head'] = array('总条数', '总价值');
        $table_status['body'] = array();
        $table_status['body'][0][0] = 0;
        $table_status['body'][0][1] = 0;

        if ($data) {
            foreach ($data as $k => $v) {
                $mobile = trim($v['A']);
                $state = preg_match(Dever::rule('mobile'), $mobile);
                if ($state) {
                    $company_id = (isset($v['F']) && $v['F']) ? trim($v['F']) : 1;
                    if ($v['C'] > 0) {
                        $where['clear'] = true;
                        $where['mobile'] = $mobile;
                        $where['company_id'] = $company_id;
                        $type_name = trim($v['D']);
                        $type_info = Dever::db('option/company_qiquan')->find(array('name' => $type_name, 'company_id' => $where['company_id']));
                        if ($type_info) {
                            $type = $type_info['id'];

                            $info = Dever::db('option/member')->find($where);
                            if ($info) {
                                $idcard = strtoupper(trim($v['B']));
                                if ($info['idcard'] != $idcard) {
                                    $table['body'][] = array($company_id, $info['mobile'], $info['idcard'], $idcard, $type_name, '手机号和身份证不对应');
                                } else {
                                    $account = Dever::db('option/account')->find(array('id' => $info['aid'], 'clear' => true));
                                    if ($account && $account['status'] < 3) {
                                        if ($check == 1) {
                                            $state = true;
                                        } else {
                                            $state = Dever::load('option/lib/cash')->up($account['id'], $info['id'], $type, 'jiaofu', $v['C'], $v['E'], false);
                                        }
                                        
                                        if ($state) {
                                            $table_status['body'][0][0] += 1;
                                            $table_status['body'][0][1] += $v['C'];
                                        } else {
                                            $table['body'][] = array($company_id, $info['mobile'], $info['idcard'], $v['C'], $type_name, '导入到数据表失败');
                                        }
                                    } else {
                                        $table['body'][] = array($company_id, $info['mobile'], $info['idcard'], $v['C'], $type_name, '未找到主账户');
                                    }
                                }
                                
                            } else {
                                $table['body'][] = array($company_id, $mobile, $v['B'], $v['C'], $type_name, '未找到期权账户');
                            }
                        } else {
                            $table['body'][] = array($company_id, $mobile, $v['B'], $v['C'], $type_name, '期权类型不存在');
                        }
                        
                    } else {
                        $table['body'][] = array($company_id, $mobile, $v['B'], $v['C'], $v['D'], '交付金额为0');
                    }
                }
            }
        }
        
        $result['导入成功'] = array
        (
            'type' => 'table',
            'content' => $table_status,
        );

        $result['导入异常'] = array
        (
            'type' => 'table',
            'content' => $table,
        );
        $html = Dever::show('', $result, false, false);
        return $html;
    }

    # 导入发放记录
    public function get_fafang_api()
    {
        Dever::config('base')->hook = true;
        $check = Dever::input('check');
        $file = $this->path . $this->name;

        $data = Dever::excelImport($file, 3, 5, 'excel');

        $table = array();
        $table['head'] = array('手机号', '身份证号', '发放金额', '期权类型', '说明');
        $table['body'] = array();

        $table_status = array();
        $table_status['head'] = array('总条数', '总价值');
        $table_status['body'] = array();
        $table_status['body'][0][0] = 0;
        $table_status['body'][0][1] = 0;

        if ($data) {
            foreach ($data as $k => $v) {
                $mobile = trim($v['A']);
                $state = preg_match(Dever::rule('mobile'), $mobile);
                if ($state) {
                    $company_id = (isset($v['F']) && $v['F']) ? trim($v['F']) : 1;
                    if ($v['C'] > 0) {
                        $where['clear'] = true;
                        $where['mobile'] = $mobile;
                        $where['company_id'] = $company_id;
                        $type_name = trim($v['D']);
                        $type_info = Dever::db('option/company_qiquan')->find(array('name' => $type_name, 'company_id' => $where['company_id']));
                        if ($type_info) {
                            $type = $type_info['id'];
                            $info = Dever::db('option/member')->find($where);
                            if ($info) {
                                $idcard = strtoupper(trim($v['B']));
                                if ($info['idcard'] != $idcard) {
                                    $table['body'][] = array($company_id, $info['mobile'], $info['idcard'], $idcard, $type_name, '手机号和身份证不对应');
                                } else {
                                    $account = Dever::db('option/account')->find($info['aid']);
                                    if ($account && $account['status'] < 3) {
                                        if ($check == 1) {
                                            $state = true;
                                        } else {
                                            $state = Dever::load('option/lib/cash')->up($account['id'], $info['id'], $type, 'fafang', $v['C'], $v['E'], false);
                                        }
                                        if ($state) {
                                            $table_status['body'][0][0] += 1;
                                            $table_status['body'][0][1] += $v['C'];
                                        } else {
                                            $table['body'][] = array($company_id, $info['mobile'], $info['idcard'], $v['C'], $type_name, '交付价值不足');
                                        }
                                    } else {
                                        $table['body'][] = array($company_id, $info['mobile'], $info['idcard'], $v['C'], $type_name, '未找到主账户');
                                    }
                                }
                            } else {
                                $table['body'][] = array($company_id, $mobile, $v['B'], $v['C'], $type_name, '未找到期权账户');
                            }
                        } else {
                            $table['body'][] = array($company_id, $mobile, $v['B'], $v['C'], $type_name, '期权类型不存在');
                        }
                        
                    } else {
                        $table['body'][] = array($company_id, $mobile, $v['B'], $v['C'], $v['D'], '发放金额为0');
                    }
                } 
            }
        }
        $result['导入成功'] = array
        (
            'type' => 'table',
            'content' => $table_status,
        );

        $result['导入异常'] = array
        (
            'type' => 'table',
            'content' => $table,
        );
        $html = Dever::show('', $result, false, false);
        return $html;
    }
}