SQLEnumController.php

<?php
namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Contracts\Validation\Validator;

use App\Http\Requests;
use App\Http\Controllers\Controller;
use Config;
use DB;
use Exception;
use Log;

class SQLEnumController extends Controller
{
    private function buildSelectStatement(Request $request, $field, $config, $datas, $schema)
    {
        $vals = [];
        if (isset($schema->adv_config->filter) && isset($schema->adv_config->filter->$field))
            $vals[] = $schema->adv_config->filter->$field;

        foreach ($datas as $data) {
            $val = null;
            if (is_array($data) && isset($data[$field])) {
                $val = $data[$field];
            } else if (isset($data->$field)) {
                $val = $data->$field;
            }

            if (is_array($val)) {
                foreach ($val as $v)
                    $vals[] = $v;
            } else if ($val !== null) {
                $vals[] = $val;
            }
        }

        if (count($vals) == 0)
            return [null, null];

        $sql = preg_replace('/{[^\}]+}/', implode(',', array_fill(0, count($vals), '?')), $config->mapSql);
        return [$sql, $vals];
    }

    private function buildUnionStatement(Request $request, $field, $config, $datas, $schema)
    {
        $sql  = $config->sql;
        $vals = [];
        $sqls = [];

        preg_match_all('/{([^\}]+)}/', $sql, $fields);
        $sql = preg_replace('/{[^\}]+}/', '?', $sql);

        if ($sql != $config->sql) {
            foreach ($datas as $data) {
                foreach ($fields[1] as $f) {
                    if (is_array($data))
                        $vals[] = $data[$f];
                    else
                        $vals[] = $data->$f;
                }

                $sqls[] = $sql;
            }

            $sql = implode(' UNION ALL ', $sqls);
        }

        return [$sql, $vals];
    }

    private function fillEnumsData(Request $request, $field, $schema, $datas)
    {
        $config = $schema->adv_config->sqlDicts->$field;
        // 不需要转换
        if ($config->type == 0)
            return true;

        // datas是object(数据库接口)或则是array(history接口转换过来的), 所以对取值需要兼容
        // 对于history接口的数据，field对应的字段可能不存在
        // (数据库增加了字段，老的历史数据会不存在这个字段)
        $host   = $config->host;
        $port   = $config->port;
        $user   = $config->user;
        $passwd = $config->passwd;
        $dbname = $config->dbname;
        $sql    = '';
        $vals   = [];

        if (isset($config->mapSql)) {
            list($sql, $vals) = $this->buildSelectStatement($request, $field, $config, $datas, $schema);
        } else {
            list($sql, $vals) = $this->buildUnionStatement($request, $field, $config, $datas, $schema);
        }

        if ($sql == null)
            return true;

        $confname = $host . ':' . $port;

        $conn = [
            'driver'    => 'mysql',
            'host'      => $host,
            'port'      => $port,
            'database'  => $dbname,
            'username'  => $user,
            'password'  => $passwd,
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ];

        Config::set('database.connections.' . $confname, $conn);
        $result = DB::connection($confname)->select($sql, $vals);

        $out = [];
        $ret = [];
        if (count($result) == 0)
            return $ret;
        if (count((array)($result[0])) == 1)
            return $ret;

        foreach ($result as $item) {
            $arr = [];
            foreach ($item as $k => $v) {
                $arr[] = $v;
                if (count($arr) == 2)
                    break;
            }

            $out[$arr[0]] = $arr[1];
        }

        foreach ($datas as $data) {
            $val = is_array($data) ? (isset($data[$field]) ? $data[$field] : "") : $data->$field;
            if (is_array($val)) {
                foreach ($val as $v) {
                    if (isset($out[$v]))
                        $ret[$v] = $out[$v];
                    else
                        $ret[$v] = $v;
                }
            } else {
                if (isset($out[$val]))
                    $ret[$val] = $out[$val];
                else
                    $ret[$val] = $val;
            }
        }

        if (isset($schema->adv_config->filter) && isset($schema->adv_config->filter->$field)) {
            $v = $schema->adv_config->filter->$field;
            if (isset($out[$v]))
                $ret[$v] = $out[$v];
            else
                $ret[$v] = $v;
        }

        return $ret;
    }

    public function getEnumsByData(Request $request, $schema, $datas)
    {
        if (!isset($schema->adv_config) || !isset($schema->adv_config->sqlDicts))
            return [];

        $ret = [];
        foreach ($schema->adv_config->sqlDicts as $f => $config)
            $ret[$f] = $this->fillEnumsData($request, $f, $schema, $datas);

        return $ret;
    }

    public function getEnumsByQuery(Request $request)
    {
        $ret = $this->getEnums($request);
        if ($ret['errcode'] != 0)
            return ['q' => $request->input('q', ''), 'results' => []];

        $result = [];
        foreach ($ret['data'] as $data) {
            $result[] = ['id' => $data['key'], 'text' => $data['name']];
        }

        return ['q' => $request->input('q', ''), 'results' => $result];
    }

    public function getEnums(Request $request)
    {
        $host   = $request->input('host', '');
        $port   = $request->input('port', '');
        $user   = $request->input('user', '');
        $passwd = $request->input('passwd', '');
        $dbname = $request->input('dbname', '');
        $sql    = $request->input('sql', '');
        $type   = $request->input('type', 0);

        if (empty($host) || empty($port) || empty($user) || empty($passwd) || empty($dbname) || empty($sql))
            return ["errcode" => 404, "errmsg" => "Bad request"];

        $vals   = [];
        $q = null;
        if (preg_match_all('/{([^\}]+)}/', $sql, $fields) > 0) {
            foreach ($fields[1] as $f) {
                if ($f == 'user.email') {
                    $vals[] = $request->user->email;
                } else if ($f == 'user.userId') {
                    $vals[] = $request->user->userId;
                } else {
                    if ($f == 'request.q') {
                        $q = $request->input('q', '');
                        if ($q == '')
                            return ['errcode' => 404, "errmsg" => "Bad request"];
                        $q = str_replace(['%', '_', '\'', '"', ';'], ['\%', '\_', '', '', ''], $q);
                        continue;
                    }

                    $v = $request->input($f, '');
                    if (strlen($v) == 0)
                        return ["errcode" => 404, "errmsg" => "Bad request: {$f}的值为空"];
                    $vals[] = $v;
                }
            }
        }

        if (!empty($q))
            $sql = preg_replace('/{request.q}/', $q, $sql);
        $sql = preg_replace('/{[^\}]+}/', '?', $sql);

        $confname = $host . ':' . $port;

        $conn = [
            'driver'    => 'mysql',
            'host'      => $host,
            'port'      => $port,
            'database'  => $dbname,
            'username'  => $user,
            'password'  => $passwd,
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ];

        Config::set('database.connections.' . $confname, $conn);

        $result = DB::connection($confname)->select($sql, $vals);

        $out = [];
        foreach ($result as $item) {
            $arr = [];
            foreach ($item as $k => $v) {
                $arr[] = $v;
                if ($type != 0 || count($arr) == 2)
                    break;
            }

            if (count($arr) == 1) {
                $arr[] = $arr[0];
            } else if (!empty($q)) {
                $arr[1] = $arr[1] . '(' . $arr[0] . ')';
            }

            $out[] = [ 'key' => $arr[0], 'name' => $arr[1] ];
        }

        return ['errcode' => 0, 'data' => $out];
    }
};