qimall/console/controllers/ExportController.php

<?php

namespace console\controllers;

use addons\LuckyGroup\common\models\LuckyGroupOrder;
use addons\Salary\common\models\SalaryMerchant;
use addons\Store\common\models\Store;
use addons\Store\common\models\StoreGoods;
use addons\Store\common\models\StoreOrder;
use backend\models\AdminRole;
use common\models\backend\Admin;
use common\models\common\CapitalLog;
use common\models\goods\Goods;
use common\models\mall\Mall;
use common\models\mall\MallAddons;
use common\models\order\OrderDetail;
use common\models\order\OrderRefund;
use common\models\user\User;
use yii\console\Controller;
use common\models\order\Order;

class ExportController extends Controller
{
    private $te_read_table = ['qimall_addons_juhe_shop_goods'];//同一sql,存在换行，且数据比较大，无法导入的表

    private $no_data_table_arr = ['qimall_crontab_task_execute', 'qimall_region', 'qimall_region_copy1', 'qimall_addons_star_chain_region',
        'qimall_addons_wechat_video_shop_region', 'qimall_census_mall_goods_copy1', 'qimall_census_mall_payment_single_month_copy1', 'qimall_census_mall_single_day_copy1',
        'qimall_town_copy1',
        'qimall_global_var_map',
        'qimall_migration',
        'qimall_menu',
        'qimall_link_category',
        'qimall_link',
        'qimall_express',
        'qimall_diy_template',
        'qimall_diy_components',
        'qimall_crontab_task',
        'qimall_backend_log',
        'qimall_attachment',
        'qimall_admin_role',
        'qimall_admin',
        'qimall_addons_wechat_video_shop_express_company',
        'qimall_rbac_auth_item',
        'qimall_town',
        'qimall_system_version_log',
        'qimall_platform_order',
        'qimall_payment_trade_order',
        'qimall_addons',
        'qimall_addons_combo',
        'qimall_addons_wechat_video_shop_cate',
        'qimall_addons_store_addons',
        'qimall_addons_event',
        'qimall_addons_invoker',
        'qimall_addons_juhe_shop_category',
        'qimall_addons_juhe_shop_express',
        'qimall_addons_mch_menu',
        'qimall_addons_qijuhe_express',
        'qimall_addons_smart_form_components',
        'qimall_addons_star_chain_category',
        'qimall_addons_store_menu'
    ];//不需要导入数据的表

    private $all_read_table = ['qimall_addons_smart_form_template_column', 'qimall_member_benefit', 'qimall_addons_ai_spirit_use', 'qimall_addons_boss_level',
        'qimall_addons_business_card', 'qimall_addons_business_card_material', 'qimall_addons_card_material', 'qimall_addons_clockin_apply', 'qimall_addons_course',
        'qimall_addons_material', 'qimall_addons_mch', 'qimall_platform_setting'
    ];//字段内容有换行，且数据量少，可以file_get_content

    private $input_db = 'input_db';
    private $source_db = 'source_db';


    public function getTableArr()
    {
        return [

            'qimall_goods_attr' => [
                'field' => 'goods_id',
                'class' => \common\models\goods\Goods::class,
                'select' => 'id',
            ],
            'qimall_payment_trade_order' => [
                'field' => 'order_no',
                'class' => \common\models\order\Order::class,
                'select' => 'order_no',
            ],
            'qimall_goods_cate_relate' => [
                'field' => 'goods_id',
                'class' => \common\models\goods\Goods::class,
                'select' => 'id',
            ],
            'qimall_addons_store_goods_attr' => [
                'field' => 'goods_id',
                'class' => StoreGoods::class,
                'select' => 'id',
            ],
            'qimall_user_partition_relationship' => [
                'field' => 'user_id',
                'class' => User::class,
                'select' => 'id',
            ],
            'qimall_access_token' => [
                'field' => 'user_id',
                'class' => User::class,
                'select' => 'id',
            ],
            'qimall_user_extra' => [
                'field' => 'user_id',
                'class' => User::class,
                'select' => 'id',
            ],
            'qimall_order_marketing' => [
                'field' => 'order_detail_id',
                'class' => OrderDetail::class,
                'select' => 'id',
            ],
            'qimall_order_express' => [
                'field' => 'order_id',
                'class' => \common\models\order\Order::class,
                'select' => 'id',
            ],
            'qimall_payment_trade_log' => [
                'field' => 'trade_id',
                'class' => \common\models\common\PaymentTrade::class,
                'select' => 'id',
            ],
            'qimall_addons_salary_api_log' => [
                'field' => 'merchant_no',
                'class' => SalaryMerchant::class,
                'select' => 'merchant_no',
            ],
            'qimall_addons_store_goods_cate_relate' => [
                'field' => 'goods_id',
                'class' => StoreGoods::class,
                'select' => 'id',
            ],
            'qimall_user_address' => [
                'field' => 'user_id',
                'class' => User::class,
                'select' => 'id',
            ],
            'qimall_user_relationship_log' => [
                'field' => 'user_id',
                'class' => User::class,
                'select' => 'id',
            ],
            'qimall_order_refund_step' => [
                'field' => 'order_refund_id',
                'class' => OrderRefund::class,
                'select' => 'id',
            ],
            'qimall_addons_boss_bonus_user_log' => [
                'field' => 'user_id',
                'class' => User::class,
                'select' => 'id',
            ],
            'qimall_addons_smart_form_template_user_behavior' => [
                'field' => 'user_id',
                'class' => User::class,
                'select' => 'id',
            ],
            'qimall_addons_boss_bonus_user_partition_relationship' => [
                'field' => 'user_id',
                'class' => User::class,
                'select' => 'id',
            ],
            'qimall_addons_score_mall_goods_attr' => [
                'field' => 'goods_id',
                'class' => Goods::class,
                'select' => 'id',
            ],
            'qimall_addons_smart_form_template_user_form_info' => [
                'field' => 'user_id',
                'class' => User::class,
                'select' => 'id',
            ],

            'qimall_addons_coupon_goods_relate' => [
                'field' => 'goods_id',
                'class' => Goods::class,
                'select' => 'id',
            ],
            'qimall_addons_boss_bonus_user_relationship_log' => [
                'field' => 'user_id',
                'class' => User::class,
                'select' => 'id',
            ],
            'qimall_capital_error_log' => [
                'field' => 'user_id',
                'class' => User::class,
                'select' => 'id',
            ],
            'qimall_addons_lucky_group_order_marketing' => [
                'field' => 'order_id',
                'class' => LuckyGroupOrder::class,
                'select' => 'id',
            ],
            'qimall_addons_store_settlement_log' => [
                'field' => 'store_id',
                'class' => Store::class,
                'select' => 'id',
            ],
        ];
    }

    private function getCommonPath()
    {
//        return dirname(dirname(__DIR__)) . '\sql\/common\/';
        return dirname(dirname(dirname(__DIR__))) . '\sql\/common\/';
    }

    private function getMallPath($mall_id)
    {
//        return dirname(dirname(__DIR__)) . '\sql\/mall_' . $mall_id . '\/';
        return dirname(dirname(dirname(__DIR__))) . '\sql\/mall_' . $mall_id . '\/';
    }

    private function getStructPath()
    {
        return dirname(dirname(__DIR__)) . '\sql\/struct\/';

    }


    //导出mysql的表结构和数据的sql
    public function actionIndex()
    {
        ini_set('memory_limit', 1024);
        $malll_list = Mall::lists(['where' => [['>', 'id', 0]]]);
        $common_path = $this->getCommonPath();
        if (!is_dir($common_path)) {
            mkdir($common_path, 777, true);
        }
        $table_arr = $this->getTableArr();
        foreach ($malll_list as $mall) {
            $mall_id = $mall['id'];
            if ($mall_id == 1) continue;
            $path = $this->getMallPath($mall_id);
            if (!is_dir($path)) {
                mkdir($path, 777, true);
            }
            $sqls = "show tables";
            $table_list = \Yii::$app->get($this->source_db)->createCommand($sqls)->queryAll();//获取表名

            foreach ($table_list as $table) {

//                $table_name = $table['Tables in qimall'];//mycat
                $table_name = $table['Tables_in_qimall'];
                $temp_sql = 'show create table ' . $table_name;
                $table_field_type_arr = $this->getTableFieldType($table_name);
                $table_struct = \Yii::$app->get($this->source_db)->createCommand($temp_sql)->queryOne();//获取表名
                $table_field = $table_struct['Create Table'];
                $is_common = false;
                if (strpos($table_field, 'mall_id') === false) {
                    $to_file_name = $common_path . $table_name . ".sql"; // 导出文件名
                    $is_common = true;
                } else {
                    $to_file_name = $path . $table_name . ".sql"; // 导出文件名
                }
                if (file_exists($to_file_name)) continue;

                if (in_array($table_name, $this->no_data_table_arr)) {
                    continue;
                }

                $file_source = fopen($to_file_name, 'w+');
                $i = 1;
                $page_size = 1000;
                while (true) {
                    $sql = "select * from " . $table_name;
                    if (!in_array($table_name, ['qimall_common_sync_logs', 'qimall_sync_goods_logs'])) {
                        if (strpos($table_field, 'mall_id') !== false) {
                            $sql .= ' where mall_id in (' . $mall_id . ' ,0 )';
                        }
                    }
                    $sql .= ' limit ' . ($i - 1) * $page_size . ',' . $page_size;
                    $data_list = \Yii::$app->get($this->source_db)->createCommand($sql)->queryAll();//获取表数据
                    if ($data_list) {
                        if (isset($table_arr[$table_name])) {
                            $field = $table_arr[$table_name]['field'];
                            $class = $table_arr[$table_name]['class'];
                            $select = $table_arr[$table_name]['select'];
                            $fields = array_column($data_list, $field);
                            $list = $class::lists(['where' => [[$select => $fields], ['mall_id' => $mall_id]], 'select' => $select]);
                            $id_arr = array_column($list, $select);
                            $this->formatData($data_list, $table_name, $file_source, true, $field, $id_arr, null, $table_field_type_arr, $is_common);
                        } else {
                            switch ($table_name) {
                                case 'qimall_rbac_auth_item_child':
                                    $field = 'role_id';
                                    $role_ids = array_column($data_list, $field);
                                    $admin_role_list = AdminRole::lists(['where' => [['role_id' => $role_ids]], 'select' => 'role_id,admin_id']);
                                    $admin_ids = array_column($admin_role_list, 'admin_id');
                                    $admin_list = Admin::lists(['where' => [['id' => $admin_ids], ['mall_id' => $mall_id]], 'select' => 'id']);
                                    $admin_id_arr = array_column($admin_list, 'id');
                                    $role_id_arr = [];
                                    foreach ($admin_role_list as $v) {
                                        if (in_array($v['admin_id'], $admin_id_arr)) {
                                            $role_id_arr[] = $v['role_id'];
                                        }
                                    }
                                    $this->formatData($data_list, $table_name, $file_source, true, $field, $role_id_arr, null, $table_field_type_arr, $is_common);
                                    break;
                                case 'qimall_commission_log_relation':
                                case 'qimall_balance_log_relation':
                                case 'qimall_score_log_relation':
                                    $field = 'log_id';
                                    $source_type_arr = [
                                        'commission', 'commission_frozen', 'score', 'score_frozen', 'balance', 'balance_frozen'
                                    ];
                                    foreach ($source_type_arr as $source_type) {
                                        $commission_ids = [];
                                        foreach ($data_list as $v) {
                                            if ($v['source_type'] == $source_type) {
                                                $commission_ids[] = $v[$field];
                                            }
                                        }
                                        CapitalLog::$capitalType = $source_type;
                                        if (!empty($commission_ids)) {
                                            $list = CapitalLog::lists(['where' => [['id' => $commission_ids], ['mall_id' => $mall_id]], 'select' => 'id']);
                                            $id_arr = array_column($list, 'id');
                                            $this->formatData($data_list, $table_name, $file_source, true, $field, $id_arr, $source_type, $table_field_type_arr, $is_common);
                                        }
                                    }
                                    break;
                                case 'qimall_addons_score_expansion_rate_task':
                                    $field = 'order_id';
                                    $source_type_arr = [
                                        '{{%order}}' => Order::class,
                                        '{{%addons_store_order}}' => StoreOrder::class
                                    ];
                                    foreach ($source_type_arr as $k => $class) {
                                        $order_ids = [];
                                        foreach ($data_list as $v) {
                                            if ($v[$field] == $k) {
                                                $order_ids[] = $v[$field];
                                            }
                                        }
                                        if (!empty($order_ids)) {
                                            $list = $class::lists(['where' => [['id' => $order_ids], ['mall_id' => $mall_id]], 'select' => 'id']);
                                            $id_arr = array_column($list, 'id');
                                            $this->formatData($data_list, $table_name, $file_source, true, $field, $id_arr, 'source_table', $table_field_type_arr, $is_common);
                                        }
                                    }
                                    break;
                                case 'qimall_mall':
                                    foreach ($data_list as $row) {
                                        if ($row['id'] != $mall_id) {
                                            continue;
                                        }

                                        $insert_sql = 'INSERT INTO `' . $table_name . '` VALUES (';
                                        foreach ($row as $v) {
                                            $v = str_replace("\r\n", "", $v);
                                            $insert_sql .= "'" . $v . "', ";
                                        }
                                        $insert_sql = substr($insert_sql, 0, strlen($insert_sql) - 2);
                                        $insert_sql .= ");\r\n";
                                        fwrite($file_source, $insert_sql);

                                    }
                                    break;
                                default:
                                    $this->formatData($data_list, $table_name, $file_source, false, '', [], null, $table_field_type_arr, $is_common);
                            }
                        }
                    } else {
                        break;
                    }
                    $i++;
                }
                fclose($file_source);
                echo "{$table_name} 表数据生成成功\r\n";
            }
            dd(11111);
        }

    }

    public function actionIndexByTable($table)
    {
        ini_set('memory_limit', 1024);
        $malll_list = Mall::lists(['where' => [['>', 'id', 0]]]);
        $common_path = $this->getCommonPath();
        if (!is_dir($common_path)) {
            mkdir($common_path, 777, true);
        }
        $table_arr = $this->getTableArr();
        foreach ($malll_list as $mall) {
            $mall_id = $mall['id'];
            $mall_id = 2;
            $path = $this->getMallPath($mall_id);
            if (!is_dir($path)) {
                mkdir($path, 777, true);
            }

//                $table_name = $table['Tables in qimall'];//mycat
            $table_name = $table;
            $temp_sql = 'show create table ' . $table_name;
            $table_field_type_arr = $this->getTableFieldType($table_name);
            $table_struct = \Yii::$app->get($this->source_db)->createCommand($temp_sql)->queryOne();//获取表名
            $table_field = $table_struct['Create Table'];
            $is_common = false;
            if (strpos($table_field, 'mall_id') === false) {
                $is_common = true;
                $to_file_name = $common_path . $table_name . ".sql"; // 导出文件名
            } else {
                $to_file_name = $path . $table_name . ".sql"; // 导出文件名
            }

            if (in_array($table_name, $this->no_data_table_arr)) {
                continue;
            }

            $file_source = fopen($to_file_name, 'w+');
            $i = 1;
            $page_size = 1000;
            while (true) {
                $sql = "select * from " . $table_name;
                if (!in_array($table_name, ['qimall_common_sync_logs', 'qimall_sync_goods_logs'])) {
                    if (strpos($table_field, 'mall_id') !== false) {
                        $sql .= ' where mall_id in (' . $mall_id . ' ,0 )';
                    }
                }
                $sql .= ' limit ' . ($i - 1) * $page_size . ',' . $page_size;
                $data_list = \Yii::$app->get($this->source_db)->createCommand($sql)->queryAll();//获取表数据
                if ($data_list) {
                    if (isset($table_arr[$table_name])) {
                        $field = $table_arr[$table_name]['field'];
                        $class = $table_arr[$table_name]['class'];
                        $select = $table_arr[$table_name]['select'];
                        $fields = array_column($data_list, $field);
                        $list = $class::lists(['where' => [[$select => $fields], ['mall_id' => $mall_id]], 'select' => $select]);
                        $id_arr = array_column($list, $select);
                        $this->formatData($data_list, $table_name, $file_source, true, $field, $id_arr, null, $table_field_type_arr, $is_common);
                    } else {
                        switch ($table_name) {
                            case 'qimall_rbac_auth_item_child':
                                $field = 'role_id';
                                $role_ids = array_column($data_list, $field);
                                $admin_role_list = AdminRole::lists(['where' => [['role_id' => $role_ids]], 'select' => 'role_id,admin_id']);
                                $admin_ids = array_column($admin_role_list, 'admin_id');
                                $admin_list = Admin::lists(['where' => [['id' => $admin_ids], ['mall_id' => $mall_id]], 'select' => 'id']);
                                $admin_id_arr = array_column($admin_list, 'id');
                                $role_id_arr = [];
                                foreach ($admin_role_list as $v) {
                                    if (in_array($v['admin_id'], $admin_id_arr)) {
                                        $role_id_arr[] = $v['role_id'];
                                    }
                                }
                                $this->formatData($data_list, $table_name, $file_source, true, $field, $role_id_arr, null, $table_field_type_arr, $is_common);
                                break;
                            case 'qimall_commission_log_relation':
                            case 'qimall_balance_log_relation':
                            case 'qimall_score_log_relation':
                                $field = 'log_id';
                                $source_type_arr = [
                                    'commission', 'commission_frozen', 'score', 'score_frozen', 'balance', 'balance_frozen'
                                ];
                                foreach ($source_type_arr as $source_type) {
                                    $commission_ids = [];
                                    foreach ($data_list as $v) {
                                        if ($v['source_type'] == $source_type) {
                                            $commission_ids[] = $v[$field];
                                        }
                                    }
                                    CapitalLog::$capitalType = $source_type;
                                    if (!empty($commission_ids)) {
                                        $list = CapitalLog::lists(['where' => [['id' => $commission_ids], ['mall_id' => $mall_id]], 'select' => 'id']);
                                        $id_arr = array_column($list, 'id');
                                        $this->formatData($data_list, $table_name, $file_source, true, $field, $id_arr, $source_type, $table_field_type_arr, $is_common);
                                    }
                                }
                                break;
                            case 'qimall_addons_score_expansion_rate_task':
                                $field = 'order_id';
                                $source_type_arr = [
                                    '{{%order}}' => Order::class,
                                    '{{%addons_store_order}}' => StoreOrder::class
                                ];
                                foreach ($source_type_arr as $k => $class) {
                                    $order_ids = [];
                                    foreach ($data_list as $v) {
                                        if ($v[$field] == $k) {
                                            $order_ids[] = $v[$field];
                                        }
                                    }
                                    if (!empty($order_ids)) {
                                        $list = $class::lists(['where' => [['id' => $order_ids], ['mall_id' => $mall_id]], 'select' => 'id']);
                                        $id_arr = array_column($list, 'id');
                                        $this->formatData($data_list, $table_name, $file_source, true, $field, $id_arr, 'source_table', $table_field_type_arr, $is_common);
                                    }
                                }
                                break;
                            case 'qimall_mall':
                                foreach ($data_list as $row) {
                                    if ($row['id'] != $mall_id) {
                                        continue;
                                    }

                                    $insert_sql = 'INSERT INTO `' . $table_name . '` VALUES (';
                                    foreach ($row as $v) {
                                        $v = str_replace("\r\n", "", $v);
                                        $insert_sql .= "'" . $v . "', ";
                                    }
                                    $insert_sql = substr($insert_sql, 0, strlen($insert_sql) - 2);
                                    $insert_sql .= ");\r\n";
                                    fwrite($file_source, $insert_sql);

                                }
                                break;
                            default:
                                $this->formatData($data_list, $table_name, $file_source, false, '', [], null, $table_field_type_arr, $is_common);
                        }
                    }
                } else {
                    break;
                }
                $i++;
            }
            fclose($file_source);
            echo "{$table_name} 表数据生成成功\r\n";
            dd(222);
        }

    }


    public function actionIn($mall_id)
    {
        ini_set('memory_limit', 1024);
        if (!isset($mall_id)) return '请输入原商城id';
        $common_path = $this->getCommonPath();
        $this->inData($common_path);
        $mall_path = $this->getMallPath($mall_id);

        $this->inData($mall_path);
    }


    public function actionStruct()
    {

        $struct_path = $this->getStructPath();
        if (!is_dir($struct_path)) {
            mkdir($struct_path, 777, true);
        }
        $sqls = "show tables";
        $table_list = \Yii::$app->get($this->source_db)->createCommand($sqls)->queryAll();//获取表名
        foreach ($table_list as $table) {
            $table_name = $table['Tables_in_qimall'];
            $temp_sql = 'show create table ' . $table_name;
            $table_struct = \Yii::$app->get($this->source_db)->createCommand($temp_sql)->queryOne();//获取表名
            $table_field = $table_struct['Create Table'];
            $to_file_name = $struct_path . $table_name . ".sql"; // 导出文件名

            if (file_exists($to_file_name)) continue;
            $file_source = fopen($to_file_name, 'w+');

            $info = "-- ----------------------------\r\n";
            $info .= "-- Table structure for `" . $table_name . "`\r\n";
            $info .= "-- ----------------------------\r\n";
            $info .= "DROP TABLE IF EXISTS `" . $table_name . "`;\r\n";
            $sqlStr = $info . $table_field . ";\r\n\r\n";
            fwrite($file_source, $sqlStr);
            echo "{$table_name} 表结构生成成功\r\n";
        }


    }

    public function actionInStruct()
    {
        $struct_path = dirname(dirname(__DIR__)) . '\sql\/struct\/';
        $filename = scandir($struct_path);
        foreach ($filename as $k => $v) {
            if ($v == '.' || $v == '..') continue;
            $f = $struct_path . $v;
            $redis_key = 'export_in_struct:' . md5($f);
//            if (\Yii::$app->redis->get($redis_key)) continue;
            $str = file_get_contents($f);
            \Yii::$app->get($this->input_db)->createCommand($str)->execute();
            echo $v . '执行成功' . $k . "\r\n";
            \Yii::$app->redis->set($redis_key, 1, 'EX', 10 * 3600);
        }
    }


    //单条
    public function formatData($data_list, $table_name, $file_source, $is_bool, $field, $ids, $source_type = null, $table_field_type_arr = [])
    {
        foreach ($data_list as $row) {
            if ($is_bool) {
                if ((isset($source_type) && isset($row[$source_type]) && $source_type != $row[$source_type]) || !in_array($row[$field], $ids)) {
                    continue;
                }
            }
            $insert_sql = 'INSERT INTO `' . $table_name . '` VALUES (';
            foreach ($row as $field => $v) {
                $v = str_replace("\r\n", "", $v);
                $v = str_replace("'", "\'", $v);
                if (isset($table_field_type_arr[$field])) {
                    switch ($table_field_type_arr[$field]) {
                        case "json":
                            if (empty($v)) {
                                $v = json_encode($v);
                            } else {
                                if (strpos($v, '\"') !== false) {
                                    $v = str_replace('\"', '\\/"', $v);

                                } else {
                                    $v = str_replace('\\', "\\\\\\\\", $v);
                                }

                            }
                            break;
                        case "float":
                        case "decimal":
                        case "int":
                        case "tinyint":
                            if (empty($v)) {
                                $v = 0;
                            }
                            break;
                    }
                }


//                if (in_array($table_name, $this->table_json) && in_array($field, $this->table_json_field)) {
//                    if (empty($v)) {
//                        $v = json_encode($v);
//                    } else {
//                        $v = str_replace('\\', "\\\\\\\\", $v);
//                    }
//                }
//                if (in_array($table_name, $this->empty_str_to_0_table) && in_array($field, $this->empty_str_to_0)) {
//                    if (empty($v)) {
//                        $v = 0;
//                    }
//                }
                $insert_sql .= "'" . $v . "', ";
            }
            $insert_sql = substr($insert_sql, 0, strlen($insert_sql) - 2);
            $insert_sql .= ");\r\n";
            fwrite($file_source, $insert_sql);

        }
    }

    public function formatDataX($data_list, $is_bool, $field, $ids, $source_type = null)
    {
        $list = [];
        foreach ($data_list as $row) {
            if ($is_bool) {
                if ((isset($source_type) && isset($row[$source_type]) && $source_type != $row[$source_type]) || !in_array($row[$field], $ids)) {
                    continue;
                }
            }
            $list[] = $row;
        }
        return $list;
    }

    //批量
//    public function formatDataBatch($data_list, $table_name, $file_source, $is_bool, $field, $ids, $source_type = null, $table_field_type_arr = [],$is_common=true)
//    {
//        if($is_common){
//            foreach ($data_list as $row) {
//                if ($is_bool) {
//                    if ((isset($source_type) && isset($row[$source_type]) && $source_type != $row[$source_type]) || !in_array($row[$field], $ids)) {
//                        continue;
//                    }
//                }
//                $insert_sql = 'INSERT INTO `' . $table_name . '` VALUES (';
//                foreach ($row as $field => $v) {
//                    $v = str_replace("\r\n", "", $v);
//                    $v = str_replace("'", "\'", $v);
//                    if (isset($table_field_type_arr[$field])) {
//                        switch ($table_field_type_arr[$field]) {
//                            case "json":
//                                if (empty($v)) {
//                                    $v = json_encode($v);
//                                } else {
//                                    $v = str_replace('\\', "\\\\\\\\", $v);
//                                }
//                                break;
//                            case "float":
//                            case "decimal":
//                            case "int":
//                            case "tinyint":
//                                if (empty($v)) {
//                                    $v = 0;
//                                }
//                                break;
//                        }
//                    }
//
//
////                if (in_array($table_name, $this->table_json) && in_array($field, $this->table_json_field)) {
////                    if (empty($v)) {
////                        $v = json_encode($v);
////                    } else {
////                        $v = str_replace('\\', "\\\\\\\\", $v);
////                    }
////                }
////                if (in_array($table_name, $this->empty_str_to_0_table) && in_array($field, $this->empty_str_to_0)) {
////                    if (empty($v)) {
////                        $v = 0;
////                    }
////                }
//                    $insert_sql .= "'" . $v . "', ";
//                }
//                $insert_sql = substr($insert_sql, 0, strlen($insert_sql) - 2);
//                $insert_sql .= ");\r\n";
//                fwrite($file_source, $insert_sql);
//
//            }
//
//        }else{
//            if ($data_list) {
//                $insert_sql = 'INSERT INTO `' . $table_name . '` VALUES (';
//                foreach ($data_list as $row) {
//                    if ($is_bool) {
//                        if ((isset($source_type) && isset($row[$source_type]) && $source_type != $row[$source_type]) || !in_array($row[$field], $ids)) {
//                            continue;
//                        }
//                    }
//                    foreach ($row as $field => $v) {
//                        $v = str_replace("\r\n", "", $v);
//                        $v = str_replace("'", "\'", $v);
//                        if (isset($table_field_type_arr[$field])) {
//                            switch ($table_field_type_arr[$field]) {
//                                case "json":
//                                    if (empty($v)) {
//                                        $v = json_encode($v);
//                                    } else {
//                                        $v = str_replace('\\', "\\\\\\\\", $v);
//                                    }
//                                    break;
//                                case "float":
//                                case "decimal":
//                                case "int":
//                                case "tinyint":
//                                    if (empty($v)) {
//                                        $v = 0;
//                                    }
//                                    break;
//                            }
//                        }
//
//
//                        $insert_sql .= "'" . $v . "', ";
//                    }
//                    $insert_sql = substr($insert_sql, 0, strlen($insert_sql) - 2);
//                    $insert_sql .= "),(";
//                }
//                if ($insert_sql != ('INSERT INTO `' . $table_name . '` VALUES (')) {
//                    $insert_sql = trim($insert_sql, ',(');
//                    $insert_sql .= ";\r\n";
//                    fwrite($file_source, $insert_sql);
//                }
//            }
//
//        }
//
//    }


    public function getNoMallIdTable()
    {
        $common_path = $this->getCommonPath();
        $filename = scandir($common_path);
        $table_arr = [];
        foreach ($filename as $k => $v) {
            if ($v == '.' || $v == '..') continue;
            $table_arr[] = substr($v, 0, strpos($v, '.'));
        }
        return $table_arr;
    }


    protected function inData($path)
    {
        $filename = scandir($path);
        foreach ($filename as $k => $v) {
            if ($v == '.' || $v == '..') continue;
            $f = $path . $v;
            $redis_key = 'export_in:' . md5($f);
            if (\Yii::$app->redis->get($redis_key)) continue;
            $table_arr = explode('.', $v);
            $table = $table_arr[0];
            $sql = "select * from information_schema.TABLES where TABLE_NAME = '{$table}';";
            $res = \Yii::$app->get($this->input_db)->createCommand($sql)->execute();
            if (empty($res)) {
                echo $table . '表不存在' . "\r\n";
                continue;
            }
            if (in_array($table_arr[0], $this->te_read_table)) continue;
            if (in_array($table_arr[0], $this->all_read_table)) {
                $str = file_get_contents($f);
                \Yii::$app->get($this->input_db)->createCommand($str)->execute();
            } else {
                $source = fopen($f, 'r+');
                while (true) {
                    $r = fgets($source);
                    if ($r === false) break;
                    \Yii::$app->get($this->input_db)->createCommand($r)->execute();
                    var_dump($r);
                }
            }
            echo $table_arr[0] . '导入成功' . $k . "\r\n";
            \Yii::$app->redis->set($redis_key, 1, 'EX', 10 * 3600);
        }
    }


    public function getTableFieldType($table)
    {
        $sqls = "DESCRIBE {$table}";
        $list = \Yii::$app->get($this->source_db)->createCommand($sqls)->queryAll();//获取表名
        $arr = [];
        foreach ($list as $item) {
            if (substr($item['Type'], 0, strpos($item['Type'], '('))) {
                $arr[$item['Field']] = substr($item['Type'], 0, strpos($item['Type'], '('));
            }
            if ($item['Type'] == 'json') {
                $arr[$item['Field']] = $item['Type'];
            }
            if ($item['Type'] == 'float') {
                $arr[$item['Field']] = $item['Type'];
            }
        }
        return $arr;
    }


    public function actionInTableList($mall_id)
    {
        if (!isset($mall_id)) return '缺失商城id';
        // ini_set('memory_limit', 1024);
        $sqls = "show tables";
        $table_list = \Yii::$app->get($this->source_db)->createCommand($sqls)->queryAll();//获取表名
        $start_time = time();
        $error_table = [];
        foreach ($table_list as $k => $table) {
//            $table_name = $table['Tables in qimall'];//mycat
            $start_time_temp = time();
            $table_name = $table['Tables_in_qimall'];
            echo "开始导入表{$table_name}，表编号" . ($k + 1) . "\r\n";
            $res = $this->InTable($table_name, $mall_id);
            $end_time_temp = time();
            if ($res == true) {
                echo $table_name . '数据导入成功,耗时' . ($end_time_temp - $start_time_temp) . "秒\r\n";
            } else {
                $error_table[] = $table_name;
            }
        }
        $end_time = time();
        echo "耗时" . ($end_time - $start_time) . "秒\r\n";
        MallAddons::getInstallAddonsByMysql($mall_id);

    }


    public function actionInTable($table, $mall_id)
    {
        ini_set('memory_limit', 1024);
        $res = $this->InTable($table, $mall_id);
        var_dump($res);


    }

    public function InTable($table, $mall_id)
    {
        try {
            $redis_key = 'export_in:' . $table . $mall_id;
            if (\Yii::$app->redis->get($redis_key)) return true;


            $sql = "select * from information_schema.TABLES where TABLE_NAME = '{$table}';";
            $res = \Yii::$app->get($this->input_db)->createCommand($sql)->execute();
            if (empty($res)) {
                echo $table . '表不存在' . "\r\n";
                return false;
            }
            $table_name = $table;
            $table_field_type_arr = $this->getTableFieldType($table_name);
            if (in_array($table_name, $this->no_data_table_arr)) {
                return false;
            }
            $i = 1;
            $page_size = 1000;
            while (true) {
                if ($table_name == 'qimall_order_action') {
                    $sql = "select id from qimall_order where mall_id = {$mall_id} ";
                    $sql .= ' order by id asc limit ' . ($i - 1) * $page_size . ',' . $page_size;
                    $data_list = \Yii::$app->get($this->source_db)->createCommand($sql)->queryAll();//获取表数据
                    if ($data_list) {
                        $order_id_arr = array_column($data_list, 'id');
                        $order_ids_str = join(',', $order_id_arr);
                        $sqls = "select * from qimall_order_action where order_id in ({$order_ids_str})";
                        $order_action_list = \Yii::$app->get($this->source_db)->createCommand($sqls)->queryAll();//获取表数据
                        $fields = [];
                        $insert = [];
                        if (!empty($order_action_list)) {
                            foreach ($order_action_list as $item) {
                                $temp_field = [];
                                $temp_value = [];
                                foreach ($item as $field => $value) {
                                    $temp_field[] = $field;

                                    if (isset($table_field_type_arr[$field]) && $table_field_type_arr[$field] == 'json') {
                                        $temp_value[] = json_decode($value, true);

                                    } else {
                                        $temp_value[] = $value;
                                    }
                                }
                                $fields = $temp_field;
                                $insert[] = $temp_value;
                            }
                            \Yii::$app->get($this->input_db)->createCommand()->batchInsert($table_name, $fields, $insert)->execute();
                        }
                    } else {
                        break;
                    }

                } else {
                    $sql = "select * from " . $table_name;
                    if ($table_name == 'qimall_mall') {
                        $sql .= ' where id in (' . $mall_id . ' ,0 )';
                    } elseif (!in_array($table_name, ['qimall_common_sync_logs', 'qimall_sync_goods_logs'])) {
                        if (isset($table_field_type_arr['mall_id'])) {
                            $sql .= ' where mall_id in (' . $mall_id . ' ,0 )';
                        }
                    }
                    if (in_array($table_name, ['qimall_migration'])) {
                        $sql .= ' limit ' . ($i - 1) * $page_size . ',' . $page_size;
                    } else {
                        $sql .= ' order by id asc limit ' . ($i - 1) * $page_size . ',' . $page_size;
                    }
                    $data_list = \Yii::$app->get($this->source_db)->createCommand($sql)->queryAll();//获取表数据
                    $fields = [];
                    $insert = [];
                    if ($data_list) {
                        $table_arr = $this->getTableArr();
                        if (isset($table_arr[$table_name])) {
                            $field = $table_arr[$table_name]['field'];
                            $class = $table_arr[$table_name]['class'];
                            $select = $table_arr[$table_name]['select'];
                            $fields = array_column($data_list, $field);

                            $str_in = join(',', $fields);
                            $t = $this->getTableNameByClass($class);
                            $sql = "select {$select} from {$t} where mall_id={$mall_id} and {$select} in ({$str_in})";
                            $list = \Yii::$app->get($this->source_db)->createCommand($sql)->queryAll();//获取表数据

                            $id_arr = array_column($list, $select);
                            $data_list = $this->formatDataX($data_list, true, $field, $id_arr, null);
                        } else {
                            switch ($table_name) {
                                case 'qimall_rbac_auth_item_child':
                                    $field = 'role_id';
                                    $role_ids = array_column($data_list, $field);
                                    $str_in = join(',', $role_ids);
                                    $t = $this->getTableNameByClass(AdminRole::class);

                                    $sql = "select role_id,admin_id from {$t} where  role_id in ({$str_in})";
                                    $admin_role_list = \Yii::$app->get($this->source_db)->createCommand($sql)->queryAll();//获取表数据

                                    $admin_ids = array_column($admin_role_list, 'admin_id');

                                    $str_in = join(',', $admin_ids);
                                    $t = $this->getTableNameByClass(Admin::class);
                                    $sql = "select id from {$t} where mall_id = {$mall_id} and  id in ({$str_in})";

                                    $admin_list = \Yii::$app->get($this->source_db)->createCommand($sql)->queryAll();//获取表数据


                                    $admin_id_arr = array_column($admin_list, 'id');
                                    $role_id_arr = [];
                                    foreach ($admin_role_list as $v) {
                                        if (in_array($v['admin_id'], $admin_id_arr)) {
                                            $role_id_arr[] = $v['role_id'];
                                        }
                                    }
                                    $data_list = $this->formatDataX($data_list, true, $field, $role_id_arr, null);
                                    break;
                                case 'qimall_commission_log_relation':
                                case 'qimall_balance_log_relation':
                                case 'qimall_score_log_relation':
                                    $field = 'log_id';
                                    $source_type_arr = [
                                        'commission', 'commission_frozen', 'score', 'score_frozen', 'balance', 'balance_frozen'
                                    ];
                                    foreach ($source_type_arr as $source_type) {
                                        $commission_ids = [];
                                        foreach ($data_list as $v) {
                                            if ($v['source_type'] == $source_type) {
                                                $commission_ids[] = $v[$field];
                                            }
                                        }
                                        CapitalLog::$capitalType = $source_type;
                                        if (!empty($commission_ids)) {
                                            $str_in = join(',', $commission_ids);
                                            $t = $this->getTableNameByClass(CapitalLog::class);
                                            $sql = "select id from {$t} where mall_id = {$mall_id} and  id in ({$str_in})";
                                            $list = \Yii::$app->get($this->source_db)->createCommand($sql)->queryAll();//获取表数据

                                            $id_arr = array_column($list, 'id');
                                            $data_list = $this->formatDataX($data_list, true, $field, $id_arr, $source_type);
                                        }
                                    }
                                    break;
                                case 'qimall_addons_score_expansion_rate_task':
                                    $field = 'order_id';
                                    $source_type_arr = [
                                        '{{%order}}' => Order::class,
                                        '{{%addons_store_order}}' => StoreOrder::class
                                    ];
                                    foreach ($source_type_arr as $k => $class) {
                                        $order_ids = [];
                                        foreach ($data_list as $v) {
                                            if ($v[$field] == $k) {
                                                $order_ids[] = $v[$field];
                                            }
                                        }
                                        if (!empty($order_ids)) {
                                            $str_in = join(',', $order_ids);
                                            $t = $this->getTableNameByClass($class);
                                            $sql = "select id from {$t} where mall_id = {$mall_id} and  id in ({$str_in})";
                                            $list = \Yii::$app->get($this->source_db)->createCommand($sql)->queryAll();//获取表数据
                                            $id_arr = array_column($list, 'id');
                                            $data_list = $this->formatDataX($data_list, true, $field, $id_arr, 'source_table');
                                        }
                                    }
                                    break;
                                default:
                            }
                        }
                        if (!empty($data_list)) {
                            foreach ($data_list as $item) {
                                $temp_field = [];
                                $temp_value = [];
                                foreach ($item as $field => $value) {
                                    $temp_field[] = $field;

                                    if (isset($table_field_type_arr[$field]) && $table_field_type_arr[$field] == 'json') {
                                        $temp_value[] = json_decode($value, true);

                                    } else {
                                        $temp_value[] = $value;
                                    }
                                }
                                $fields = $temp_field;
                                $insert[] = $temp_value;
                            }
                            \Yii::$app->get($this->input_db)->createCommand()->batchInsert($table_name, $fields, $insert)->execute();
                        }
                    } else {
                        break;
                    }
                }
                $i++;
            }
            \Yii::$app->redis->set($redis_key, 1, 'EX', 10 * 3600);

            return true;
        } catch (\Exception $e) {
            var_dump($e->getMessage());
            return false;
        }
    }

    private function getTableNameByClass($class)
    {
        $t = $class::tableName();
        $t = str_replace('{{%', 'qimall_', $t);
        return str_replace('}}', '', $t);
    }

}