<?php

namespace app\admin\controller\distinct;

use app\common\controller\Backend;
use think\Config;
use think\Db;
use think\Exception;


class Index extends Backend
{

    public function _initialize()
    {
        parent::_initialize();
    }

    public function index()
    {
        $tableList = [];
        $list = \think\Db::query("select
        TABLE_NAME,
        TABLE_COMMENT
        from
        INFORMATION_SCHEMA.Tables
        where
        table_schema = '" . Config::get('database.database') . "'");
        foreach ($list as $key => $row) {
            $tableList[] = reset($row);
            $tableCommnet[] = $row['TABLE_COMMENT'];
        }
        $this->assignconfig('tableList', $tableList);
        $this->assignconfig('tableComment', $tableCommnet);
        return $this->view->fetch();
    }

    public function get_field_list($isComment = false)
    {
        $dbname = Config::get('database.database');
        $prefix = Config::get('database.prefix');
        $table = $this->request->request('table');
        $sql = "SELECT * FROM `information_schema`.`columns` "
            . "WHERE TABLE_SCHEMA = ? AND table_name = ? "
            . "ORDER BY ORDINAL_POSITION";
        $columnList = Db::query($sql, [$dbname, $table]);
        $fieldlist = [];
        $Fields = [];
        foreach ($columnList as $index => $item) {
            $fieldlist[] = $item['COLUMN_NAME'];
            $comment[] = $item['COLUMN_COMMENT'];
            $Fields[$item['COLUMN_NAME']] = $item['COLUMN_COMMENT'];
        }
        if ($isComment) {
            return $Fields;
        }
        $this->success("", null, ['fieldlist' => $fieldlist, 'comment' => $comment]);
    }

    public function select()
    {
        $array_filed = $this->request->request('fields/a');
        $fileds = implode(',', $array_filed);
        $table = $this->request->request('table');
        $del = $this->request->request('field_del'); //软删除字段，包含软删除时需要排除此字段
        $delfiled = "";
        if ($del !== "-1") {
            $delfiled = " where {$del} is null";
        }
        $sql = "SELECT count(1) RepeatNum,{$fileds} FROM {$table} {$delfiled} GROUP BY {$fileds} HAVING RepeatNum > 1 order by RepeatNum DESC";
        $result = Db::query($sql);
        $cComment = $this->get_field_list(true);
        $colunmComment['RepeatNum'] = '重复次数';
        foreach ($array_filed as $key => $value) {
            $colunmComment[$value] = $cComment[$value];
        }
        $data = ['data' => $result, 'fields' => $colunmComment];
        $this->success('共查询到' . sizeof($result) . '条重复数据', null, $data);
    }


    public function delete($type)
    {
        $fileds_array = $this->request->request('fields/a');
        $fileds = implode(',', $fileds_array);
        $table = $this->request->request('table');
        $del = $this->request->request('field_del');
        Db::startTrans();
        try {
            //查询重复数据分组结果集
            $sql = "SELECT count(1) RepeatNum,{$fileds} FROM {$table} GROUP BY {$fileds} HAVING RepeatNum > 1 order by RepeatNum DESC";
            $result = Db::query($sql);
            $count = 0; //计数使用
            foreach ($fileds_array as $k => $v) {
                $fileds_p[] = $v . "= :" . $v; //参数化
            }
            $where = join(' and ', $fileds_p); //拼接单条查询语句
            $sql_id = "SELECT {$type}(id) as id from {$table} where {$where}"; //最大/小的id记录值
            $now = time();
            foreach ($result as $k => $v) {
                foreach ($fileds_array as $fk => $fv) {
                    $params[$fv] = $v[$fv];
                }
                $sql_keep_id = Db::query($sql_id, $params)[0]['id']; //查询保留id
                if ($del == "-1") {
                    $sql_del = "DELETE FROM {$table} WHERE {$where} AND id <> {$sql_keep_id}"; //删除
                } else {
                    $sql_del = "UPDATE {$table} SET {$del}={$now} WHERE {$where} AND id <> {$sql_keep_id}"; //软删除
                }
                $delnum = Db::execute($sql_del, $params);
                if ($delnum) {
                    $count += $delnum;
                } else {
                    throw new Exception("删除出错，进行回滚", 1);
                }
            }
            Db::commit();
            $this->success('操作成功，共删除数据' . $count . '条');
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
    }

    public function delsinger($type)
    {
        $row = $this->request->request('row/a');
        unset($row['RepeatNum']);
        $table = $this->request->request('table');
        $del = $this->request->request('field_del');
        foreach ($row as $k => $v) {
            if (is_null($v) || $v === "") {
                $fileds_p[] = "({$k} is null or {$k}='')";
                unset($row[$k]);
            } else {
                $fileds_p[] = $k . "=:" . $k;
            }
        }
        $where = join(' and ', $fileds_p);
        $sql_id = "SELECT {$type}(id) as id from {$table} where {$where}";
        $sql_keep_id = Db::query($sql_id, $row)[0]['id'];
        $now = time();
        if ($del == "-1") {
            $sql_del = "DELETE FROM {$table} WHERE {$where} AND id <> {$sql_keep_id}";
        } else {
            $sql_del = "UPDATE {$table} SET {$del}={$now} WHERE {$where} AND id <> {$sql_keep_id}"; //软删除
        }

        $delnum = Db::execute($sql_del, $row);
        $this->success('删除该条重复数据' . $delnum . '条');
    }
}
