df-helper 0.2.26

use std::sync::mpsc;
use std::thread;
use json::{array, JsonValue, object};
use crate::{datetime, tools};
use crate::orm::fields::field;
use crate::orm::fields::field::Field;
use crate::orm::mode::mode::Mode;
use crate::orm::mode::mssql::Mssql;
use crate::orm::mode::mysql::Mysql;

pub struct Db {
    /// 当前数据库类型
    model: String,
    /// 当前的数据库
    database: String,
    /// 当前数据库配置名称
    default: String,
    /// 当前的数据库配置
    connection: JsonValue,
    /// 数据库配置集合
    connections: JsonValue,
    /// 数据表名称前缀
    prefix: String,
    /// id 系统生成 数据库生成
    idauto: bool,
    mode: Mode,

    fields: JsonValue,
    /// 具体数据
    table: String,
    field: Vec<String>,
    page: u64,
    limit: u64,
    pagelimit: String,
    /// 排序
    order: JsonValue,
    /// 分组
    group: String,
    /// 返回sql
    fetch_sql: bool,
    /// 去重
    distinct: bool,
    /// 新增id集合
    ids: JsonValue,

    where_and: JsonValue,
    where_or: JsonValue,
    /// 字段比较
    where_column: JsonValue,
    /// 累加累减
    setup: String,

    /// 操作符
    union: String,
    union_all: bool,
}

impl Db {
    /// 数据库链接配置
    ///
    /// ````json
    /// {
    ///     "type": "mysql",            数据库类型
    ///     "hostname": "127.0.0.1",    数据库地址
    ///     "hostport": 3306,           数据库端口
    ///     "database": "tests",        数据库名
    ///     "username": "root",         用户账号
    ///     "userpass": "11111111",     用户密码
    ///     "params": [],               链接参数
    ///     "charset": "utf8mb4",      语言
    ///     "prefix": ""                数据库前缀
    ///     "idauto": false,            主键在创建时方式 false 使用自定义生成 true 使用系统生成
    /// }
    /// ````
    pub fn connect(config: JsonValue) -> Self {
        let connections = config["connections"].clone();
        let default = config["default"].to_string();
        let connection = connections[default.clone()].clone();
        let database = connection["database"].to_string();
        let model = connection["type"].to_string();
        let prefix = connection["prefix"].to_string();
        let idauto = connection["idauto"].as_bool().unwrap();
        let mode = Db::_mode(model.as_str(), connection.clone());
        Self {
            database,
            default,
            connection,
            connections,
            model,
            prefix,
            idauto,
            mode,

            table: String::new(),
            field: vec![],
            fields: object! {},
            page: 0,
            limit: 0,
            pagelimit: String::new(),

            fetch_sql: false,
            order: array![],
            group: String::new(),
            distinct: false,
            ids: array![],
            where_and: array![],
            where_or: array![],
            where_column: array![],
            setup: String::new(),
            union: String::new(),
            union_all: false,
        }
    }
    fn _mode(model: &str, connection: JsonValue) -> Mode {
        match model {
            "mysql" => {
                let dsn = format!("mysql://{}:{}@{}:{}/{}", connection["username"], connection["userpass"], connection["hostname"], connection["hostport"], connection["database"]);
                Mode::Mysql(Mysql::connect(dsn.clone(), connection["database"].to_string()))
            }
            "mssql" => {
                let dsn = format!("mssql://{}:{}@{}:{}/{}", connection["username"], connection["userpass"], connection["hostname"], connection["hostport"], connection["database"]);
                Mode::Mssql(Mssql::connect(dsn.clone(), connection["database"].to_string()))
            }
            _ => {
                let dsn = format!("mysql://{}:{}@{}:{}/{}", connection["username"], connection["userpass"], connection["hostname"], connection["hostport"], connection["database"]);
                Mode::Mysql(Mysql::connect(dsn.clone(), connection["database"].to_string()))
            }
        }
    }
    /// 重载参数
    fn _reset(&mut self) {
        self.table = String::new();
        self.field = vec![];
        self.page = 0;
        self.limit = 0;
        self.pagelimit = String::new();

        self.fetch_sql = false;
        self.order = array![];
        self.group = String::new();
        self.distinct = false;
        self.ids = array![];
        self.where_and = array![];
        self.where_or = array![];
        self.where_column = array![];
        self.setup = String::new();
        self.union = String::new();
        self.union_all = false;
    }
    /// 添加配置
    pub fn add_config(mut self, default: &str, config: JsonValue) -> Self {
        if self.connections[default].is_empty() {
            self.connections[default] = config;
        }
        self
    }
    /// 切换配置
    pub fn set_connection(&mut self, default: &str) -> &mut Self {
        self.default = default.to_string();
        self.connection = self.connections[default].clone();
        self.model = self.connection["type"].to_string();
        self.database = self.connection["database"].to_string();
        self.mode = Db::_mode(self.model.as_str(), self.connection.clone());
        self
    }
    /// 获取配置清单
    pub fn get_connections(self) -> JsonValue {
        return self.connections;
    }
    /// 执行数据库
    pub fn database(database: &str, config: JsonValue) -> Self {
        let mut data = Db::connect(config);
        data.database = database.to_string();
        data
    }
    /// 查询数据表是否存在
    pub fn query_table(&mut self, table: &str) -> bool {
        self._reset();
        self.mode.query_table(table)
    }
    /// 创建数据表
    pub fn create_table(&mut self, table: &str, fields: JsonValue) -> bool {
        self._reset();
        self.table = table.to_string();
        let title = fields["title"].as_str().unwrap();
        let key = fields["key"].as_str().unwrap();

        // 唯一约束
        let mut unique_fields = String::new();
        let mut unique_name = String::new();
        let mut unique = String::new();
        for item in fields["unique"].members() {
            if unique_fields == "" {
                unique_fields = format!("`{}`", item);
                unique_name = format!("unique_{}", item);
            } else {
                unique_fields = format!("{},`{}`", unique_fields, item);
                unique_name = format!("{}_{}", unique_name, item);
            }
            unique = format!("UNIQUE KEY  `{}` ({})", unique_name, unique_fields);
        }

        // 唯一索引
        let mut index = String::new();
        for row in fields["index"].members() {
            let mut index_fields = String::new();
            let mut index_name = String::new();
            for item in row.members() {
                if index_fields == "" {
                    index_fields = format!("`{}`", item);
                    index_name = format!("index_{}", item);
                } else {
                    index_fields = format!("{},`{}`", index_fields, item);
                    index_name = format!("{}_{}", index_name, item);
                }
            }
            if index == "" {
                index = format!("INDEX `{}` ({})", index_name, index_fields);
            } else {
                index = format!("{},\r\nINDEX `{}` ({})", index, index_name, index_fields);
            }
        }

        let auto = fields["auto"].as_bool().unwrap();
        let mut fieldsql = String::new();

        for (name, field) in fields["fields"].entries() {
            let row = self._fields(auto, name, field.clone());
            if fieldsql == "" { fieldsql = format!("\r\n{}", row.clone()) } else { fieldsql = format!("{},\r\n{}", fieldsql, row.clone()); }
        }

        fieldsql = format!("{},\r\nPRIMARY KEY(`{}`)", fieldsql, key);

        if unique != "" {
            fieldsql = format!("{},\r\n{}", fieldsql, unique);
        }
        if index != "" {
            fieldsql = format!("{},\r\n{}", fieldsql, index);
        }

        let charset = self.connection["charset"].to_string();
        let collate = format!("{}_bin", charset);
        let sql = format!("create table IF NOT EXISTS {} ({}\r\n) ENGINE = InnoDB CHARSET = '{}' COLLATE '{}' comment '{}'", self._table(), fieldsql, charset, collate, title);
        let res = self.mode.create_table(sql.as_str().clone());
        return res;
    }
    /// 更新数据表
    pub fn update_table(&mut self, table: &str, info: JsonValue) -> bool {
        self._reset();
        self.table = table.to_string();
        let data = self.mode.fieldsinfo(self.table.as_str());
        let mut add = object! {};
        let mut del = object! {};
        let mut put = object! {};
        // let key = info["key"].clone();
        let auto = info["auto"].as_bool().unwrap();
        let fields = info["fields"].clone();
        for (field, item) in fields.entries() {
            if data[field].is_empty() {
                add[field] = item.clone();
            } else {
                let old = data[field].clone();
                let new = item.clone();

                let oldtext = format!("{}|{}|def:{}|r:{}|l:{}|{}|{}|{}|{}|{}", old["title"], old["mode"], old["def"], old["require"], old["length"], old["option"], old["dec"], old["api"], old["table"], old["fields"]);
                let newtext = format!("{}|{}|def:{}|r:{}|l:{}|{}|{}|{}|{}|{}", new["title"], new["mode"], new["def"], new["require"], new["length"], new["option"], new["dec"], new["api"], new["table"], new["fields"]);
                if oldtext != newtext {
                    println!("old> {}", old);
                    println!("new> {}", new);
                    println!("old> {}", oldtext);
                    println!("new> {}", newtext);
                    put[field] = item.clone();
                    continue;
                }
            }
        }
        for (field, item) in data.entries() {
            if fields[field].is_empty() {
                del[field] = item.clone();
            }
        }
        let mut sql = array![];

        // sql.push(format!("ALTER TABLE {} DROP UNIQUE", table)).unwrap();
        // sql.push(format!("ALTER TABLE {} DROP INDEX", table)).unwrap();
        // sql.push(format!("ALTER TABLE {} DROP PRIMARY KEY", table)).unwrap();
        // sql.push(format!("ALTER TABLE {} DROP UNIQUE {}", table,key)).unwrap();

        for (field, _) in del.entries() {
            sql.push(format!("ALTER TABLE {} DROP {}", table, field)).unwrap();
        }
        for (field, item) in add.entries() {
            sql.push(format!("ALTER TABLE {} ADD COLUMN({})", table, self._fields(auto.clone(), field, item.clone()))).unwrap();
        }
        for (field, item) in put.entries() {
            sql.push(format!("ALTER TABLE {} CHANGE {} {}", table, field, self._fields(auto.clone(), field, item.clone()))).unwrap();
        }

        // 唯一约束
        let mut unique_fields = String::new();
        let mut unique_name = String::new();
        let unique = {
            let mut unique=String::new();
            for item in fields["unique"].members() {
                if unique_fields == "" {
                    unique_fields = format!("`{}`", item);
                    unique_name = format!("unique_{}", item);
                } else {
                    unique_fields = format!("{},`{}`", unique_fields, item);
                    unique_name = format!("{}_{}", unique_name, item);
                }
                unique = format!("UNIQUE KEY  `{}` ({})", unique_name, unique_fields);
            }
            unique
        };

        // 唯一索引
        let mut index = String::new();
        for row in fields["index"].members() {
            let mut index_fields = String::new();
            let mut index_name = String::new();
            for item in row.members() {
                if index_fields == "" {
                    index_fields = format!("`{}`", item);
                    index_name = format!("index_{}", item);
                } else {
                    index_fields = format!("{},`{}`", index_fields, item);
                    index_name = format!("{}_{}", index_name, item);
                }
            }
            if index == "" {
                index = format!("INDEX `{}` ({})", index_name, index_fields);
            } else {
                index = format!("{},\r\nINDEX `{}` ({})", index, index_name, index_fields);
            }
        }

        if unique != "" {
            sql.push(format!("ALTER TABLE {} ADD {}", table, unique)).unwrap();
        }
        if index != "" {
            sql.push(format!("ALTER TABLE {} ADD {}", table, index)).unwrap();
        }
        // sql.push(format!("ALTER TABLE {} ADD PRIMARY KEY(`{}`)", table, key)).unwrap();

        let mut error = true;
        for item in sql.members() {
            let res = self.mode.update_table(item.as_str().unwrap());
            println!("{} {}", res, item);
            if !res {
                error = false;
            }
        }
        return error;
    }

    /// 字段信息
    pub fn fieldsinfo(&mut self) -> JsonValue {
        let mut data = self.mode.fieldsinfo(self.table.as_str());
        let fields = self.field.clone();
        let mut list = array![];

        fn row_data(field: &str, mut item: JsonValue) -> JsonValue {
            item["label"] = item["title"].clone();
            item["field"] = field.clone().into();
            item["name"] = field.clone().into();
            return item;
        }

        if fields.is_empty() {
            for (field, item) in data.entries_mut() {
                let row = row_data(field.clone().into(), item.clone());
                list.push(row.clone()).unwrap();
                self.fields[self.table.as_str().clone()][field.clone()] = row.clone();
            }
            return list;
        }
        for (field, item) in data.entries_mut() {
            for index in fields.iter() {
                if index == field {
                    let row = row_data(field.clone().into(), item.clone());
                    list.push(row.clone()).unwrap();
                    self.fields[self.table.as_str().clone()][field.clone()] = row.clone();
                }
            }
        }
        return list;
    }
    pub fn fieldsinfo_obj(&mut self) -> JsonValue {
        self.fieldsinfo();
        return self.fields[self.table.as_str().clone()].clone();
    }
    /// 字段sql拼接
    fn _fields(&mut self, auto: bool, field: &str, data: JsonValue) -> String {
        let mut row = format!("");
        let mode = data["mode"].as_str().unwrap();
        let require = data["require"].as_bool().unwrap_or(false);
        let title = data["title"].as_str().unwrap_or("未知标题");
        match mode {
            "key" => {
                let length = data["length"].as_i32().unwrap();
                row = field::Key::field(field, title, length, auto).sql();
            }
            "pass" => {
                let default = data["def"].as_str().unwrap();
                let length = data["length"].as_i32().unwrap();
                row = field::Str::pass(require.clone(), field.clone(), title.clone(), length.clone(), default.clone()).sql();
            }
            "string" => {
                let default = data["def"].as_str().unwrap_or("");
                let length = data["length"].as_i32().unwrap_or(20);
                row = field::Str::string(require.clone(), field.clone(), title.clone(), length.clone(), default.clone()).sql();
            }
            "table" => {
                let table = data["table"].as_str().unwrap();
                let fields = data["fields"].as_str().unwrap();
                let api = data["api"].as_str().unwrap();
                row = field::Table::field(require.clone(), field.clone(), title.clone(),
                                          table.clone(), fields.clone(), "", api.clone()).sql();
            }
            "text" => {
                let default = data["def"].as_str().unwrap_or("");
                row = field::Text::field(require.clone(), field.clone(), title.clone(), default.clone()).sql();
            }
            "file" => {
                let default = data["def"].as_str().unwrap_or("");
                let length = data["length"].as_i32().unwrap_or(1);
                row = field::File::field(require.clone(), field.clone(), title.clone(), length.clone(), default.clone()).sql();
            }
            "number" => {
                let length = data["length"].as_i32().unwrap();
                let dec = data["dec"].as_i32().unwrap();
                let default = data["def"].as_f32().unwrap();
                row = field::Number::field(require.clone(), field.clone(), title.clone(),
                                           length.clone(), dec.clone(),
                                           default.clone()).sql();
            }
            "switch" => {
                let default = data["def"].as_bool().unwrap_or(false);
                row = field::Switch::field(require.clone(), field.clone(), title.clone(),
                                           default.clone()).sql();
            }
            "datetime" => {
                let default = data["def"].as_str().unwrap();
                row = field::Date::datetime(require.clone(), field.clone(), title.clone(),
                                            default.clone()).sql();
            }
            "year" => {
                let default = data["def"].as_str().unwrap();
                row = field::Date::year(require.clone(), field.clone(), title.clone(),
                                        default.clone()).sql();
            }
            "date" => {
                let default = data["def"].as_str().unwrap();
                row = field::Date::date(require.clone(), field.clone(), title.clone(),
                                        default.clone()).sql();
            }
            "time" => {
                let default = data["def"].as_str().unwrap();
                row = field::Date::time(require.clone(), field.clone(), title.clone(),
                                        default.clone()).sql();
            }
            "timestamp" => {
                let dec = data["dec"].as_i32().unwrap();
                let default = data["def"].as_f64().unwrap_or(0.0);
                row = field::Timestamp::timestamp(require.clone(), field.clone(), title.clone(),
                                                  dec.clone(), default.clone()).sql();
            }
            "select" => {
                let option = data["option"].clone();
                let multiple = data["multiple"].as_bool().unwrap();
                row = field::Select::field(require.clone(), field.clone(), title.clone(),
                                           option.clone(), multiple.clone(), data["def"].clone()).sql();
            }
            _ => {}
        }
        format!("{}", row.clone())
    }
    /// 删除数据表
    ///
    /// * all true 删除表数据与表结构 false 只清空表数据不删除表结构
    pub fn delete_table(&mut self, table: &str, all: bool) -> bool {
        self._reset();
        self.table = table.to_string();
        let sql = {
            if all {
                format!("drop table {}", self._table())
            } else {
                format!("truncate table {}", self.table)
            }
        };
        return self.mode.delete_table(sql.as_str());
    }
    /// 条件拼装sql
    fn _where_sql(&mut self) -> String {
        let wheres = {
            let mut sql = {
                if self.where_and.len() <= 0 && self.where_or.len() <= 0
                {
                    "".to_string()
                } else {
                    let mut where_and = "".to_string();
                    for item in self.where_and.members() {
                        match item[1].as_str().unwrap() {
                            "between" => {
                                if where_and == "" {
                                    where_and = format!("`{}` BETWEEN '{}' AND '{}'", item[0], item[2][0], item[2][1]);
                                } else {
                                    where_and = format!("{} AND `{}` BETWEEN '{}' AND '{}'", where_and, item[0], item[2][0], item[2][1]);
                                }
                            }
                            "notbetween" => {
                                if where_and == "" {
                                    where_and = format!("`{}` NOT BETWEEN '{}' AND '{}'", item[0], item[2][0], item[2][1]);
                                } else {
                                    where_and = format!("{} AND {} NOT BETWEEN '{}' AND '{}'", where_and, item[0], item[2][0], item[2][1]);
                                }
                            }
                            "null" => {
                                let herad = {
                                    if where_and == "" {
                                        where_and
                                    } else {
                                        format!("{} AND", where_and)
                                    }
                                };
                                if item[2].as_bool().unwrap() {
                                    where_and = format!("{} `{}` IS NULL", herad, item[0]);
                                } else {
                                    where_and = format!("{} `{}` IS NOT NULL", herad, item[0]);
                                }
                            }
                            "in" => {
                                let inlist = {
                                    let mut list = "".to_string();
                                    for item in item[2].members() {
                                        if list == "" {
                                            list = format!("\"{}\"", item);
                                        } else {
                                            list = format!("{},\"{}\"", list, item);
                                        }
                                    }
                                    list
                                };
                                if where_and == "" {
                                    where_and = format!("`{}` {} ({})", item[0], item[1], inlist);
                                } else {
                                    where_and = format!("{} AND `{}` {} ({})", where_and, item[0], item[1], inlist);
                                }
                            }
                            "notin" => {
                                let inlist = {
                                    let mut list = "".to_string();
                                    for item in item[2].members() {
                                        if list == "" {
                                            list = format!("\"{}\"", item);
                                        } else {
                                            list = format!("{},\"{}\"", list, item);
                                        }
                                    }
                                    list
                                };
                                if where_and == "" {
                                    where_and = format!("`{}` NOT IN ({})", item[0], inlist);
                                } else {
                                    where_and = format!("{} AND `{}` NOT IN ({})", where_and, item[0], inlist);
                                }
                            }
                            _ => {
                                let key = item[0].clone();
                                let key = tools::string::split(key.to_string(), "|");
                                if key.len() > 1 {
                                    let mut tx = "".to_string();
                                    for keys in key.members() {
                                        if tx == "" {
                                            tx = format!("`{}` {} \"{}\"", keys, item[1], item[2]);
                                        } else {
                                            tx = format!("{} OR `{}` {} \"{}\"", tx, keys, item[1], item[2]);
                                        }
                                    }
                                    if where_and == "" {
                                        where_and = format!("{}", tx);
                                    } else {
                                        where_and = format!("{} AND ({})", where_and, tx);
                                    }
                                } else {
                                    if where_and == "" {
                                        where_and = format!("`{}` {} \"{}\"", item[0], item[1], item[2]);
                                    } else {
                                        where_and = format!("{} AND `{}` {} \"{}\"", where_and, item[0], item[1], item[2]);
                                    }
                                }
                            }
                        }
                    }
                    if self.where_or.len() > 0 && where_and != "" {
                        where_and = format!("({}) OR", where_and);
                    }
                    let mut where_or = "".to_string();

                    for item in self.where_or.members() {
                        match item[1].as_str().unwrap() {
                            "null" => {
                                let herad = {
                                    if where_and == "" {
                                        where_and
                                    } else {
                                        format!("{} OR", where_and)
                                    }
                                };
                                if item[2].as_bool().unwrap() {
                                    where_and = format!("{} `{}` IS NULL", herad, item[0]);
                                } else {
                                    where_and = format!("{} `{}` IS NOT NULL", herad, item[0]);
                                }
                            }
                            "in" => {
                                let inlist = {
                                    let mut list = "".to_string();
                                    for item in item[2].members() {
                                        if list == "" {
                                            list = format!("\"{}\"", item);
                                        } else {
                                            list = format!("{},\"{}\"", list, item);
                                        }
                                    }
                                    list
                                };
                                if where_or == "" {
                                    where_or = format!("`{}` IN ({})", item[0], inlist);
                                } else {
                                    where_or = format!("{} OR `{}` IN ({})", where_or, item[0], inlist);
                                }
                            }
                            "notin" => {
                                let inlist = {
                                    let mut list = "".to_string();
                                    for item in item[2].members() {
                                        if list == "" {
                                            list = format!("\"{}\"", item);
                                        } else {
                                            list = format!("{},\"{}\"", list, item);
                                        }
                                    }
                                    list
                                };
                                if where_or == "" {
                                    where_or = format!("`{}` NOT IN ({})", item[0], inlist);
                                } else {
                                    where_or = format!("{} OR `{}` NOT IN ({})", where_or, item[0], inlist);
                                }
                            }
                            "between" => {
                                if where_or == "" {
                                    where_or = format!("`{}` BETWEEN {} AND {}", item[0], item[2][0], item[2][1]);
                                } else {
                                    where_or = format!("{} OR `{}` BETWEEN {} AND {}", where_or, item[0], item[2][0], item[2][1]);
                                }
                            }
                            "notbetween" => {
                                if where_or == "" {
                                    where_or = format!("`{}` NOT BETWEEN {} AND {}", item[0], item[2][0], item[2][1]);
                                } else {
                                    where_or = format!("{} OR `{}` NOT BETWEEN {} AND {}", where_or, item[0], item[2][0], item[2][1]);
                                }
                            }
                            _ => {
                                let key = item[0].clone();
                                let key = tools::string::split(key.to_string(), "|");
                                if key.len() > 1 {
                                    let mut tx = "".to_string();
                                    for keys in key.members() {
                                        if tx == "" {
                                            tx = format!("`{}` {} \"{}\"", keys, item[1], item[2]);
                                        } else {
                                            tx = format!("{} OR `{}` {} \"{}\"", tx, keys, item[1], item[2]);
                                        }
                                    }
                                    if where_or == "" {
                                        where_or = format!("{}", tx);
                                    } else {
                                        where_or = format!("{} OR ({})", where_and, tx);
                                    }
                                } else {
                                    if where_or == "" {
                                        where_or = format!("`{}` {} \"{}\"", item[0], item[1], item[2]);
                                    } else {
                                        where_or = format!("{} OR `{}` {} \"{}\"", where_or, item[0], item[1], item[2]);
                                    }
                                }
                            }
                        }
                    }
                    format!("WHERE {} {}", where_and, where_or)
                }
            };
            if self.where_column.len() > 0 {
                let mut where_column = "".to_string();
                for item in self.where_column.members() {
                    if where_column == "" {
                        where_column = format!("`{}` {} `{}`", item[0], item[1], item[2]);
                    } else {
                        where_column = format!("{} AND `{}` {} `{}`", where_column, item[0], item[1], item[2]);
                    }
                }
                if sql.is_empty() {
                    sql = format!("WHERE ({})", where_column);
                } else {
                    sql = format!("{} AND ({})", sql, where_column);
                }
            }
            sql
        };
        wheres
    }
    /// table 前缀拼装
    fn _table(&mut self) -> String {
        let table = {
            if self.prefix == "" {
                self.table.clone()
            } else {
                format!("{}{}", self.prefix, self.table)
            }
        };
        if self.database == "" {
            table.clone()
        } else {
            format!("`{}`.`{}`", self.database, table)
        }
    }
    /// 查询sql拼装
    fn _select(&mut self) -> String {
        let table = self._table();
        let field = {
            let mut field = String::new();
            for item in self.field.iter() {
                if field == "" {
                    field = item.clone();
                } else {
                    field = format!("{},{}", field, item)
                }
            }
            if field == "" {
                field = "*".to_string()
            }
            if self.distinct {
                field = format!("DISTINCT {}", field)
            }
            field
        };
        let order = {
            let mut order = "".to_string();
            for item in self.order.members() {
                if order == "" {
                    order = format!("ORDER BY {} {}", item[0], item[1]);
                } else {
                    order = format!("{},{} {}", order, item[0], item[1]);
                }
            }
            order
        };
        let wheres = self._where_sql();
        let union = {
            if self.union == "" {
                self.union.to_string()
            } else {
                if self.union_all {
                    format!("UNION ALL SELECT {} FROM {} {}", field, self.union, wheres)
                } else {
                    format!("UNION SELECT {} FROM {} {}", field, self.union, wheres)
                }
            }
        };
        return format!("SELECT {} FROM {} {} {} {} {} {}", field, table, wheres, union, order, self.group, self.pagelimit);
    }
    /// 新增sql拼装
    fn _insert(&mut self, data: JsonValue) -> String {
        self.ids = array![];
        let table = self._table();
        let mut fields = "".to_string();
        let (tx, rx) = mpsc::channel();
        let mut fields_data = data[0].clone();
        let idauto = self.idauto;
        thread::spawn(move || {
            let mut fields = "".to_string();
            if !idauto {
                if fields_data["id"].is_empty() {
                    fields_data["id"] = "".into();
                }
            }
            for (k, _v) in fields_data.entries() {
                if fields == "" {
                    fields = format!("`{}`", k);
                } else {
                    fields = format!("{},`{}`", fields, k);
                }
            }
            tx.send(fields).unwrap();
        });

        for recived in rx {
            fields = recived
        }

        let mut thrs = vec![];

        let len = data.len();
        let num = {
            if len <= 100 {
                10
            } else if len > 100 && len <= 1000 {
                100
            } else if len > 1000 && len < 10000 {
                500
            } else if len >= 10000 && len < 100000 {
                1000
            } else {
                3000
            }
        };
        let count = len / num + 1;
        let fields_infos = self.mode.fieldsinfo(self.table.as_str());
        for index in 0..count {
            let start = {
                if index > 0 {
                    index * num
                } else {
                    index * num
                }
            };
            let end = {
                let endlen = (index + 1) * num;
                if len > endlen {
                    endlen
                } else {
                    len
                }
            };
            let list = data.clone();
            let pcindex = index.to_string().parse::<i64>().unwrap();
            let fields_info = fields_infos.clone();
            let idauto = self.idauto;
            let thr = thread::spawn(move || {
                let mut values = "".to_string();
                let mut ids = vec![];
                for index in start..end {
                    let mut item = list[index.clone()].clone();
                    if item["id"].is_empty() {
                        if !idauto {
                            let id = datetime::timestamp::timestamp("us") + pcindex;
                            item["id"] = format!("{:X}", id).into();
                        }
                    }
                    ids.push(item["id"].clone());
                    let mut row = "".to_string();
                    for (k, v) in item.entries() {
                        let key = fields_info[k].clone();
                        let mode = key["mode"].as_str().unwrap();
                        match mode {
                            "switch" => {
                                let t = {
                                    if v.clone() == JsonValue::from(1) {
                                        true
                                    } else {
                                        false
                                    }
                                };
                                if row == "" {
                                    row = format!("{}", t);
                                } else {
                                    row = format!("{},{}", row, t);
                                }
                            }
                            "int" => {
                                if row == "" {
                                    row = format!("{}", v);
                                } else {
                                    row = format!("{},{}", row, v);
                                }
                            }
                            "float" => {
                                if row == "" {
                                    row = format!("{}", v);
                                } else {
                                    row = format!("{},{}", row, v);
                                }
                            }
                            _ => {
                                if row == "" {
                                    row = format!("\"{}\"", v);
                                } else {
                                    row = format!("{},\"{}\"", row, v);
                                }
                            }
                        }
                    }
                    if values == "" {
                        values = format!("({})", row);
                    } else {
                        values = format!("{},({})", values, row);
                    }
                }
                (ids, values)
            });
            thrs.push(thr);
        }

        let mut values = "".to_string();
        for thr in thrs {
            let (ids, value) = thr.join().unwrap();
            if value.is_empty() {
                continue;
            }
            self.ids.push(ids.clone()).expect("id 加入 错误");
            if values == "" {
                values = format!("{}", value);
            } else {
                values = format!("{},{}", values, value);
            }
        }
        return format!("INSERT INTO {} ({}) VALUES {}", table, fields, values);
    }
    /// 更新sql拼装
    fn _update(&mut self, mut data: JsonValue) -> String {
        self.fieldsinfo();
        let table = self._table();
        let mut values = "".to_string();
        for (k, v) in data.entries_mut() {
            let field = self.fields[self.table.as_str().clone()][k].clone();
            match field["mode"].as_str().unwrap() {
                "switch" => {
                    if v.to_string() == "0" {
                        *v = JsonValue::from(false)
                    }
                    if v.to_string() == "1" {
                        *v = JsonValue::from(true)
                    }
                    let t = match v.as_bool().unwrap() {
                        false => {
                            JsonValue::from(0)
                        }
                        true => {
                            JsonValue::from(1)
                        }
                    };
                    *v = t.clone();
                }
                _ => {}
            }

            if values == "" {
                values = format!("`{}`=\"{}\"", k, v);
            } else {
                values = format!("{},`{}`=\"{}\"", values, k, v);
            }
        }
        if self.setup != "" {
            if values == "" {
                values = format!("{}", self.setup);
            } else {
                values = format!("{},{}", values, self.setup);
            }
        }
        let wheres = self._where_sql();
        return format!("UPDATE {} SET {} {}", table, values, wheres);
    }
    /// 批量更新
    /// field 更新主键
    fn _update_case(&mut self, field: &str, data: JsonValue) -> String {
        let table = self._table();
        let mut ids = array![];
        let mut setlist = object! {};
        for item in data.members() {
            let wheres_value = item[field].to_string();
            ids.push(wheres_value.clone()).expect("加入 wheres_fields 值");
            for (k, v) in item.entries() {
                if k == field {
                    continue;
                }
                if setlist[k].is_empty() {
                    setlist[k] = format!("WHEN {} THEN {}", wheres_value, v).into();
                } else {
                    setlist[k] = format!("{} WHEN {} THEN {}", setlist[k], wheres_value, v).into();
                }
            }
        }
        let mut values = "".to_string();
        for (key, item) in setlist.entries() {
            if values == "" {
                values = format!("`{}` = CASE `{}` {} END", key, field, item);
            } else {
                values = format!("{},`{}` = CASE `{}` {} END", values, key, field, item);
            }
        }
        self.where_and(field, "in", ids.into());
        let wheres = self._where_sql();
        return format!("UPDATE {} SET {} {}", table, values, wheres);
    }
    /// 删除sql拼装
    fn _delete(&mut self) -> String {
        let table = self._table();

        let wheres = self._where_sql();

        let order = {
            let mut order = "".to_string();
            for item in self.order.members() {
                if order == "" {
                    order = format!("ORDER BY {} {}", item[0], item[1]);
                } else {
                    order = format!("{},{} {}", order, item[0], item[1]);
                }
            }
            order
        };
        return format!("DELETE FROM {} {} {} {}", table, wheres, order, self.pagelimit);
    }


    /// 事务开始
    pub fn transaction(&mut self) -> bool {
        return self.mode.transaction();
    }
    /// 提交事务
    pub fn commit(&mut self) -> bool {
        return self.mode.commit();
    }
    /// 回滚事务
    pub fn rollback(&mut self) -> bool {
        return self.mode.rollback();
    }

    /// 执行数据表
    pub fn table(&mut self, name: &str) -> &mut Self {
        self._reset();
        self.table = name.to_string();
        self
    }

    /// 显示字段
    pub fn fields(&mut self, name: &str) -> &mut Self {
        self.field = vec![];
        let list = tools::string::split(name.to_string(), ",");
        for item in list.members() {
            self.field.push(item.to_string());
        }
        self
    }
    pub fn hidden(&mut self, name: &str) -> &mut Self {
        let list = tools::string::split(name.to_string(), ",");
        let fields = self.mode.fieldsinfo(self.table.as_str());
        let mut row = vec![];
        for (field, _) in fields.entries() {
            row.push(field.to_string());
        }
        for item in list.members() {
            row.retain(|items| if *items == item.to_string() { false } else { true });
        }
        self.field = row;
        self
    }

    /// 页码
    pub fn page(&mut self, mut page: u64) -> &mut Self {
        if page <= 0 {
            page = 1;
        }
        self.page = page;
        self.pagelimit = format!("LIMIT {},{}", (self.page - 1) * self.limit, self.limit);
        self
    }
    /// 返回SQL不执行
    pub fn fetch_sql(&mut self, open: bool) -> &mut Self {
        self.fetch_sql = open;
        self
    }
    /// 排序
    pub fn order(&mut self, field: &str, sort: i8) -> &mut Self {
        let mut model = "ASC";
        if sort != 0 {
            model = "DESC";
        }
        self.order.push(vec![field, model].clone()).expect("插入排序内容");
        self
    }
    /// 分组
    pub fn group(&mut self, fields: &str) -> &mut Self {
        self.fields(fields);
        self.group = format!("GROUP BY {}", fields);
        self
    }
    /// 限制
    pub fn limit(&mut self, mut limit: u64) -> &mut Self {
        if limit <= 0 {
            limit = 10;
        }
        if self.page <= 0 {
            self.page = 1;
        }
        self.limit = limit;
        if self.page == 1 {
            self.pagelimit = format!("LIMIT {}", self.limit);
        } else {
            self.pagelimit = format!("LIMIT {},{}", (self.page - 1) * self.limit, self.limit);
        }
        self
    }

    /// 去重复
    pub fn distinct(&mut self, open: bool) -> &mut Self {
        self.distinct = open;
        self
    }

    /// 两表同字段
    pub fn union(&mut self, table: &str, all: bool) -> &mut Self {
        self.union = table.to_string();
        self.union_all = all;
        self
    }
    /// 设置and条件
    pub fn where_and(&mut self, k: &str, compare: &str, mut v: JsonValue) -> &mut Self {
        if v == JsonValue::from(true) {
            v = 1.into();
        }
        if v == JsonValue::from(false) {
            v = 0.into();
        }
        self.where_and.push(array![k, compare, v]).expect("设置or条件 错误");
        self
    }

    /// 设置or条件
    pub fn where_or(&mut self, k: &str, compare: &str, mut v: JsonValue) -> &mut Self {
        if v == JsonValue::from(true) {
            v = 1.into();
        }
        if v == JsonValue::from(false) {
            v = 0.into();
        }
        self.where_or.push(array![k, compare, v]).expect("设置or条件 错误");
        self
    }

    pub fn where_column(&mut self, field1: &str, compare: &str, field2: &str) -> &mut Self {
        self.where_column.push(array![field1, compare, field2]).expect("设置where_column条件 错误");
        self
    }


    /// 自增
    pub fn inc(&mut self, field: &str, mut data: JsonValue) -> &mut Self {
        if data == 0 {
            data = JsonValue::from(1);
        }
        if self.setup == "" {
            self.setup = format!("`{}`={}+{}", field, field, data);
        } else {
            self.setup = format!("{},`{}`={}+{}", self.setup, field, field, data);
        }
        self
    }
    /// 自减
    pub fn dec(&mut self, field: &str, mut data: JsonValue) -> &mut Self {
        if data == 0 {
            data = JsonValue::from(1);
        }
        if self.setup == "" {
            self.setup = format!("`{}`= {}-{}", field, field, data);
        } else {
            self.setup = format!("{} , `{}`= {}-{}", self.setup, field, field, data);
        }
        self
    }


    /// 查询-单条记录
    pub fn find(&mut self) -> JsonValue {
        self.page(1);
        self.limit(1);
        let sql = self._select();
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.select(sql, self.table.clone(), self.field.clone());
        if res.len() >= 1 {
            return res[0].clone();
        }
        return res;
    }
    /// 查询-多条记录
    pub fn select(&mut self) -> JsonValue {
        let sql = self._select();
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.select(sql, self.table.clone(), self.field.clone());
        return res;
    }

    /// 查询-多条列记录
    pub fn column(&mut self) -> JsonValue {
        let sql = self._select();
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.select(sql, self.table.clone(), self.field.clone());
        if self.field.len() == 1 {
            let mut column = array![];
            for item in res.members() {
                column.push(item[self.field[0].clone()].clone()).expect("列加入错误");
            }
            return column;
        }
        return res;
    }


    /// 查询单一记录指定字段
    pub fn value(&mut self, field: &str) -> JsonValue {
        self.page(1);
        self.limit(1);
        self.fields(field);
        let sql = self._select();
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.select(sql, self.table.clone(), self.field.clone());
        if res.len() >= 1 {
            return res[0][field].clone();
        }
        return JsonValue::Null;
    }
    /// 添加记录
    pub fn add(&mut self, data: JsonValue) -> JsonValue {
        let only = data[0].is_empty();
        let sql = {
            if only {
                self._insert(array![data])
            } else {
                self._insert(data)
            }
        };
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.insert(sql);
        if res == self.ids.len() {
            if only {
                return self.ids[0].clone();
            }
            return self.ids.clone();
        }
        return res;
    }
    /// 更新记录
    pub fn update(&mut self, data: JsonValue) -> JsonValue {
        let sql = self._update(data);
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.update(sql);
        return res;
    }
    /// 批量更新记录
    /// field 更新主键
    pub fn update_case(&mut self, field: &str, data: JsonValue) -> JsonValue {
        let sql = self._update_case(field, data);
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.update(sql);
        return res;
    }
    /// 删除记录
    pub fn delete(&mut self) -> JsonValue {
        let sql = self._delete();
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.delete(sql);
        return res;
    }


    /// 统计数量
    pub fn count(&mut self) -> JsonValue {
        self.field = vec![];
        self.fields("COUNT(*) as count");
        let sql = self._select();
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        if self.group.is_empty() {
            let res = self.mode.aggregate_query("count", sql, self.field.clone());
            return res;
        }
        let res = self.mode.aggregate_query("count_group", sql, self.field.clone());
        return res;
    }
    /// 最大值
    pub fn max(&mut self, fields: &str) -> JsonValue {
        let list = tools::string::split(fields.to_string(), ",");
        for field in list.members() {
            self.fields(format!("MAX({}) as {}", field.clone(), field.clone()).as_str());
        }
        let sql = self._select();
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.aggregate_query("max", sql, self.field.clone());
        return res;
    }
    /// 最小值
    pub fn min(&mut self, fields: &str) -> JsonValue {
        let list = tools::string::split(fields.to_string(), ",");
        for field in list.members() {
            self.fields(format!("MIN({}) as {}", field.clone(), field.clone()).as_str());
        }
        let sql = self._select();
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.aggregate_query("min", sql, self.field.clone());
        return res;
    }
    /// 平均值
    pub fn avg(&mut self, fields: &str) -> JsonValue {
        let list = tools::string::split(fields.to_string(), ",");
        for field in list.members() {
            self.fields(format!("AVG({}) as {}", field.clone(), field.clone()).as_str());
        }
        let sql = self._select();
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.aggregate_query("avg", sql, self.field.clone());
        return res;
    }
    /// 求和
    pub fn sum(&mut self, fields: &str) -> JsonValue {
        let list = tools::string::split(fields.to_string(), ",");
        for field in list.members() {
            self.fields(format!("SUM({}) as {}", field.clone(), field.clone()).as_str());
        }
        let sql = self._select();
        if self.fetch_sql {
            return JsonValue::String(sql);
        }
        let res = self.mode.aggregate_query("sum", sql, self.field.clone());
        return res;
    }
}