<?php
/**
 *
 * @author：llbjj
 * @DateTime：2022/5/5 13:32
 * @Description：
 *
 */
namespace Application\Service\DB;

use Application\Common\Com;
use Application\Common\Crypt;
use Application\Service\BaseService;
use Application\Service\DB\Admin\Role;
use Application\Service\DB\Item\Patient;
use Application\Service\DB\Item\Patientattrs;
use Application\Service\Exceptions\DbException;
use Application\Service\Extension\Helper\ArrayHelper;
use Application\Service\Extension\Laminas;
use Application\Service\Extension\Swoole\ConnectPool;
use Interop\Container\ContainerInterface;
use Laminas\Db\Adapter\Exception\InvalidQueryException;
use Laminas\Db\Exception\RuntimeException;
use Laminas\Db\Sql\Expression;
use Laminas\Db\Sql\Join;
use Laminas\Db\Sql\Predicate\Predicate;
use Laminas\Db\Sql\Predicate\PredicateSet;
use Laminas\Db\Sql\Select;
use Laminas\Db\Sql\Sql;
use Laminas\Db\Sql\Where;
use Laminas\Db\TableGateway\TableGateway;
use Laminas\Json\Json;
use Laminas\Stdlib\ArrayUtils;
use Laminas\Validator\Db\NoRecordExists;
use Laminas\Validator\Exception\InvalidArgumentException;
use phpDocumentor\Reflection\Types\Mixed;
use Swoole\Coroutine;
use Laminas\Db\Sql\Predicate\Operator;


class AbstractDb extends BaseService
{

    private string $_cacheKey = '';

    protected $dbAdapter;   //数据库适配器
    protected $tableName;  //表名称
    protected $primaryKey = 'id';   //主键ID
    protected $cryptFields = [];
    protected $tableGateway;
    protected $is_create_info = 1;  //表中是否有 create_time, create_user_id
    protected $is_update_info = 1;  // 表中是否有 update_time, update_user_id
    protected $cryptKey = 'ykeEdc';   // 加密key值
    protected $is_print_sql = 1;
    protected $placeKey = '=|=';

    /** @var null|Join */
    protected array $joins = [];

    protected $alias;

    private bool $isMemoryCache = false;

    protected static array $memoryCache = [];

    /**
     * db profile info.
     * @var array
     */
    protected static array $profile = [];
    /**
     * 是否输出sql
     * @var bool
     */
    protected bool $printSql = false;
    public array $maskingFieldList = [];   // 手机号字段列表

    protected $checkNameItemId = '';//识别结果表时间类型转换，项目ID临时做全局

    protected bool $isShowMaskingField = false;   // 是否可以显示脱敏字段完整信息

    public function __construct($dbAdapter, ContainerInterface $container)
    {
        $this->dbAdapter = $dbAdapter;
        $this->init();
        parent::__construct($container);
        // 初始是否显示脱敏字段完整信息
        $this->initShowMaskingField();
    }

    /**
     * Notes: 加密字段
     * User: llbjj
     * DateTime: 2022/5/6 10:03
     *
     * @param array $data
     */
    public function encryptField(array &$data)
    {
        if (empty($this->cryptFields) and empty($data)) return $data;
        foreach ($this->cryptFields as $cryptField) {
            //if(isset($data[$cryptField]) and $data[$cryptField] != '') $data[$cryptField] = Crypt::blockCipher()->encrypt($data[$cryptField]);
            if (isset($data[$cryptField]) and $data[$cryptField] != '') {
                $data[$cryptField] = str_ireplace('\\', "{$this->placeKey}", $data[$cryptField]);
                $data[$cryptField] = new Expression("HEX(AES_ENCRYPT('{$data[$cryptField]}', '{$this->cryptKey}'))");
            }
            //if(isset($data[$cryptField]) and $data[$cryptField] != '') $data[$cryptField] = new Expression("HEX(AES_ENCRYPT('{$data[$cryptField]}', '{$this->cryptKey}'))");
        }
    }

    /**
     * Notes: 解密字段
     * User: llbjj
     * DateTime: 2022/5/6 10:04
     *
     * @param array $data
     */
    public function decryptField(array $data)
    {
        if (empty($this->cryptFields) and empty($data)) return $data;
        foreach ($this->cryptFields as $cryptField) {
            foreach ($data as &$datum) {
                if (is_array($datum)) {
                    // if(isset($datum[$cryptField])  and $datum[$cryptField] != '') $datum[$cryptField] = Crypt::blockCipher()->decrypt($datum[$cryptField]);
                    if (isset($datum[$cryptField]) and $datum[$cryptField] != '') $datum[$cryptField] = str_ireplace($this->placeKey, '\\', $datum[$cryptField]);
                } else {
                    // if(isset($data[$cryptField])  and $data[$cryptField] != '') $data[$cryptField] = Crypt::blockCipher()->decrypt($data[$cryptField]);
                    if (isset($data[$cryptField]) and $data[$cryptField] != '') $data[$cryptField] = str_ireplace($this->placeKey, '\\', $data[$cryptField]);
                    break;
                }

                /*if(is_array($datum)){
                    if(isset($datum[$cryptField])  and $datum[$cryptField] != '') $datum[$cryptField] = Crypt::blockCipher()->decrypt($datum[$cryptField]);
                }else{
                    if(isset($data[$cryptField])  and $data[$cryptField] != '') $data[$cryptField] = Crypt::blockCipher()->decrypt($data[$cryptField]);
                    break;
                }*/
            }
        }
        return $data;
    }

    /**
     * Notes: 获取多条数据
     * User: llbjj
     * DateTime: 2022/5/5 13:45
     *
     * @param array $data ['where'=>'', 'columns'=>[], 'order'=>'', 'limit'=>'', 'offset'=>'', 'group' => []]
     * where String|array   查询条件
     * columns array       查询列，默认 全部
     * order String|array   排序
     * limit int        分页显示的数量
     * offset int       分页起始数
     * group String|array 分组
     * @return array|Mixed
     */
    public function fetchAll(array $data = [], $printSql = false)
    {
        $this->handleExecuteCryptField($data,'query');

        return $this->execute('query', [
            'where' => $data
        ]);
    }

    public function fetchAllWithPagination($conditions = [], string $pageParam = 'page', string $pageSizeParam = 'limit')
    {
        $providerData = [];
        if (is_object($conditions)) {
            $providerData['where'] = $conditions;
        } elseif (is_array($conditions)) {
            foreach ($conditions as $key => $condition) {
                $providerData[$key] = $condition;
            }
        }
        // 现在如果没传页码就展示全部数据
        if ($_POST[$pageParam]) {
            $providerData['limit'] = $_POST[$pageSizeParam] ?? Laminas::$app->getRequest()->getPost($pageSizeParam, 10);

            $providerData['offset'] = ($providerData['limit']) * (($_POST[$pageParam] ?? Laminas::$app->getRequest()->getPost($pageParam, 1)) - 1);
        }

        $getCountProvider = function ($providerData) {
            unset($providerData['limit']);
            unset($providerData['offset']);
            $providerData['columns'] = [new Expression('COUNT(`id`) as `count`')];
            return $providerData;
        };
        return [
            'data' => $this->fetchAll($providerData),
            'count' => intval($this->fetchOne($getCountProvider($providerData))['count'] ?? 0)
        ];
    }

    public function find(int $id)
    {
        $condition = [
            'where' => [
                'id' => $id
            ]
        ];
        return $this->fetchOne($condition);
    }

    /**
     * Notes: 获取单条数据
     * User: llbjj
     * DateTime: 2022/5/5 13:44
     *
     */
    public function fetchOne(array $data = []) {
        $data['order'] = $data['order'] ?? [$this->primaryKey];
        $data['limit'] = 1;
        $data['offset'] = 0;

        $row = self::fetchAll($data);
        if (is_array($row)) {
            return array_pop($row);
        }

        return $row;
    }

    /**
     * Notes: 获取当个字段的信息, 以主键为键值
     * User: llbjj
     * DateTime: 2022/5/5 13:14
     *
     * @param string|Expression $field
     * @param array|string|Where $where
     * @param mixed $order
     * @return array
     */
    public function fetchCol($field, $where, $order = []) {
        $searchData = [
            'where' => $where,
            'columns' => [$this->primaryKey, $field],
            'order' => $order
        ];
        $datas = self::fetchAll($searchData);

        if(empty($datas)) return [];
        $result = [];
        foreach($datas as $data){
            $result[$data[$this->primaryKey]] = $data[$field];
        }
        unset($datas);

        return $result;
    }

    /**
     * Notes: 获取总数
     * User: llbjj
     * DateTime: 2022/5/5 13:44
     *
     */
    public function getCount($where = '1') {
        //添加Where实例条件的判断
        if($where instanceof Where){
            $this->_replaceCryptFieldsInWhereObject($where);
        }elseif(isset($where['where']) && is_string($where['where'])){
            foreach($this->cryptFields as $field) {
                $where['where'] = str_ireplace($field." ", "AES_DECRYPT(UNHEX(`{$field}`), '{$this->cryptKey}') ", $where['where']);
            }
        }else{
            foreach($this->cryptFields as $field) {
                $where = str_ireplace($field." ", "AES_DECRYPT(UNHEX(`{$field}`), '{$this->cryptKey}') ", $where);
            }
        }

        return $this->tableGateway->select(function($select) use ($where) {
            if ($where instanceof Where) {
                $select->where($where);
            } elseif (is_array($where) && !isset($where['where'])) {
                $whereObj = new Where();
                $this->setWhereParamsByArray($where, $whereObj);

                $select->where($whereObj);
            }else if(isset($where['where']) && is_array($where['where'])) {
                $whereObj = new Where();
                $this->setWhereParamsByArray($where['where'], $whereObj);

                $select->where($whereObj);
            } else {
                $select->where($where);
            }
            $select->columns([$this->primaryKey]);
            return $select;
        })->count();
    }

    /**
     * @param Where $whereObj
     * @return void
     * 递归替换 Where 对象中含有加密字段的条件
     * 唐伯虎不虎
     */
    private function _replaceCryptFieldsInWhereObject(Where $whereObj) {
        $predicates = $whereObj->getPredicates();
        foreach ($predicates as $key => $predicateData) {
            [$predicate, $combine] = $predicateData;

            //处理 Expression
            if ($predicate instanceof \Laminas\Db\Sql\Expression) {
                $expr = $predicate->getExpression();
                foreach ($this->cryptFields as $field) {
                    $expr = str_ireplace(
                        $field . ' ',
                        "AES_DECRYPT(UNHEX(`{$field}`), '{$this->cryptKey}') ",
                        $expr
                    );
                }
                $predicate->setExpression($expr);
            }

            //处理 OperatorPredicate
            if ($predicate instanceof \Laminas\Db\Sql\Predicate\Operator) {
                $left = $predicate->getLeft();
                if (is_string($left)) {
                    foreach ($this->cryptFields as $field) {
                        if (stripos($left, $field) !== false) {
                            $predicate->setLeft(
                                new \Laminas\Db\Sql\Expression(
                                    "AES_DECRYPT(UNHEX(`{$field}`), '{$this->cryptKey}')"
                                )
                            );
                        }
                    }
                }
            }

            // 处理 Nested Where
            if ($predicate instanceof Where) {
                $this->_replaceCryptFieldsInWhereObject($predicate);
            }
        }
    }


    /**
     * Notes: 更新表is_del字段， 0、未删除；1、删除
     * User: llbjj
     * DateTime: 2022/5/5 13:43
     *
     */
    public function upDelState($where, $upDelStatus = 1) {
        $upData = ['is_del' => $upDelStatus];
        return $this->update($upData, $where);
    }

    /**
     * Notes: 保存数据
     * User: llbjj
     * DateTime: 2022/5/5 13:41
     *
     */
    public function save(array $saveData) {
        if(empty($saveData[$this->primaryKey])) return $this->insert($saveData);
        else {
            // 查询数据是否存在，不存在个插入
            $count = $this->getCount([$this->primaryKey => $saveData[$this->primaryKey]]);
            if($count)  {
                return $this->update($saveData, $this->primaryKey.' = '.$saveData[$this->primaryKey]);
            }else {
                return $this->insert($saveData);
            }
        }
    }

    /**
     * Notes: 新增（插入）数据
     * User: llbjj
     * DateTime: 2022/5/5 13:40
     *
     * return  int 返回插入的ID值
     */
    public function insert(array $insertData) {
        $this->encryptField($insertData);
        if($this->is_create_info){
            if (method_exists($this, 'getCreateTime')) {
                $insertData['create_time'] = $this->getCreateTime();
            }else{
                $insertData['create_time'] = time();
            }

            if (php_sapi_name() != 'cli') {
                if (!isset($insertData['create_user_id'])){
                    $insertData['create_user_id'] = $this->LocalService()->identity->getId();
                }
            }
        }
        $this->handleSaveData($insertData);
        $affectedRowNum = $this->tableGateway->insert($insertData);
        return $affectedRowNum ? $this->tableGateway->getLastInsertValue() : 0;
    }

    /**
     * Notes: 更新数据
     * User: llbjj
     * DateTime: 2022/5/5 13:40
     *
     * return int 受影响的行数
     */
    public function update(array $updateData,  $where) {
        $this->encryptField($updateData);
        if($this->is_update_info){
            $updateData['update_time'] = (isset($updateData['update_time']) && !empty($updateData['update_time']))?$updateData['update_time']:time();
            if (php_sapi_name() != 'cli') {
                $updateData['update_user_id'] = isset($updateData['update_user_id'])?$updateData['update_user_id']: $this->LocalService()->identity->getId();
            }
        }

        $this->handleSaveData($updateData);

        return $this->execute('update', [
            'data' => $updateData,
            'where' => $where
        ]);
        return $this->tableGateway->update($updateData, $where);
    }

    /**
     * Notes: 根据ID值更新单个字段
     * User: llbjj
     * DateTime: 2022/5/5 13:40
     *
     */
    public function updateField($id, $field, $value) {
        $where = [$this->primaryKey => $id];
        $upData = [$field=>$value];
        return $this->update($upData, $where);
    }

    /**
     * Notes: 获取单条数据中单个字段值
     * User: llbjj
     * DateTime: 2022/5/5 13:16
     *
     * @param $column
     * @param string|array|Where $where
     * @param string|array $order
     * @return mixed|string|void|null
     */
    public function getOneFieldVal($column, $where = '1', $order = 'id'){
        if($column){
            if(is_string($order) and $order == 'id' and $this->primaryKey != 'id'){
                $order = $this->primaryKey;
            }else if(is_array($order) and in_array('id', $order)){
                foreach($order as $orderKey => $orderVal){
                    if($orderVal == 'id'){
                        $order[$orderKey] = $this->primaryKey;
                    }
                }
            }

            $result = $this->fetchOne(['where' => $where, 'columns' => [$column], 'order' => $order]);

//            return !empty($result) ? array_pop($result) : '';
            return !empty($result) ? $result[$column] : '';
        }else{
            echo new RuntimeException('字段名称不能为空！');
        }
    }

    /**
     * 获取表名
     * @return mixed
     */
    public function getTableName(): string
    {
        return $this->tableName;
    }

    /**
     * 获取适配器
     * @return \Laminas\Db\Adapter\AdapterInterface
     */
    public function getAdapter()
    {
        return $this->tableGateway->getAdapter();
    }

    public function join($name, $on, $columns = Select::SQL_STAR, $type = Select::JOIN_INNER): AbstractDb
    {
        if (class_exists(Coroutine::class) && Coroutine::getCid() > -1) {
            $this->joins[Coroutine::getCid()][] = [$name, $on, $columns, $type];
        } else {
            $this->joins[] = [$name, $on, $columns, $type];
        }
        return $this;
    }

    private function getJoins()
    {
        if (class_exists(Coroutine::class) && Coroutine::getCid() > -1  && isset($this->joins[Coroutine::getCid()])) {
            return $this->joins[Coroutine::getCid()];
        } else {
            return $this->joins;
        }
    }

    public function alias(string $alias)
    {
        if (class_exists(Coroutine::class) && Coroutine::getCid() > -1) {
            $this->alias[Coroutine::getCid()] = $alias;
        } else {
            $this->alias = $alias;
        }
        return $this;
    }

    public function getAlias()
    {
        if (class_exists(Coroutine::class) && Coroutine::getCid() > -1 && isset($this->alias[Coroutine::getCid()])) {
            return $this->alias[Coroutine::getCid()];
        } elseif (is_string($this->alias)) {
            return $this->alias;
        }

        return null;
    }

    /**
     * Notes:
     * User: llbjj
     * DateTime: 2022/5/20 15:18
     *
     * @param $field_name 字段名称
     * @param $field_val 字段值
     * @param $exclude  查询条件 where是字符串类型，代表查询条件；where是数组，代表是排除条件，数组格式['field'=>'field_name', 'value' => 'field_value']
     * @param string $errMsg 错误提示信息
     */
    public function check_field_repeat($field_name, $field_val, $exclude = '', $errMsg = '此字段值已存在') {
        if (is_array($exclude) && !(count($exclude) === 2 && isset($exclude['field']) && isset($exclude['value']))) {
            if( $this->getCount(ArrayUtils::merge($exclude, [$field_name => $field_val])) ) {
                $errMsg = $errMsg ?? '此字段值已存在';
                throw new InvalidArgumentException($errMsg);
            }
        }else {
            $noRecordParams = [
                'adapter' => $this->dbAdapter,
                'table' => $this->tableName,
                'field' => $field_name,
            ];
            if($exclude) $noRecordParams['exclude'] = $exclude;
            //验证配置
            $validator = new NoRecordExists($noRecordParams);

            if(!empty($this->cryptFields) and in_array($field_name, $this->cryptFields)) $field_val = Crypt::blockCipher()->encrypt($field_val);
            //开始验证
            if(!$validator->isValid($field_val)){
                $errMsg = $errMsg ?? '此字段值已存在';
                throw new InvalidArgumentException($errMsg);
            }
        }
    }

    /**
     * Notes: 获取下一次的最大排序值
     * User: llbjj
     * DateTime: 2022/5/21 11:18
     *
     * @param $order_field
     * @param string|array|Where $where
     * @return int|Mixed|string|void|null
     */
    public function getMaxOrder($order_field, $where = []){
        if(is_array($where)){
            $where['is_del'] = 0;
        }else if(is_string($where)){
            $where = empty($where) ? 'is_del = 0' : $where.' and is_del = 0';
        }else if($where instanceof Where) {
            $where->equalTo('is_del', 0);
        }
        $maxOrder = $this->getOneFieldVal($order_field, $where, $order_field.' desc');

        if(empty($maxOrder) || intval($maxOrder) < 10) return 10;

        $max_order_arr = str_split((String)$maxOrder);
        array_pop($max_order_arr);
        array_push($max_order_arr,0);
        return implode($max_order_arr) + 10;
    }


    public function cache(string $cacheKey = '', int $duration = 3600)
    {
        $this->_cacheKey = true;
        return $this;
    }

    public function memoryCache(): AbstractDb
    {
        $this->isMemoryCache = false;
        return $this;
    }


    /**
     * @throws DbException
     */
    private function execute(string $method = 'query', array $params = [])
    {
        try {
            $isCachedVal = false;
            if ($method === 'query') {
                $sqlObject = $this->tableGateway->getSql();
                if (class_exists(Coroutine::class) && Coroutine::getCid() > -1) {
                    $sqlObject = clone $this->tableGateway->getSql();
                }
                if ($this->getAlias()) {
                    $sqlObject->setTable([$this->getAlias() => $this->getTableName()]);
                }
                $sqlObject = $sqlObject->select();
                if ($this->getJoins()) {
                    foreach ($this->getJoins() as $join) {
                        $sqlObject->join(...$join);
                    }
                }

                if($method === 'query' && ($this->tableName == 'yikeen_item_identificationresultchange' || $this->tableName == 'yikeen_item_csae' || $this->tableName == 'yikeen_item_patientworkannex')){
                    $queryRange = 0;
                    $columns = [];
                    if(isset($params['where']) ){
                        if (!isset($params['where']['columns'])  || empty($params['where']['columns']) ) {
                            // 如果没有指定字段，默认查询 *
                            $columns[] = '*';
                            $queryRange = 1;
                        } else {
                            // 如果有指定字段
                            foreach ($params['where']['columns'] as $key => $col) {
                                $columns[$key] = $col;
                            }
                        }
                    }else{
                        $columns[] = '*';
                        $queryRange = 1;
                    }
                    // 处理 collect_date，无论 columns 是空还是不空都要处理
                    if (
                        ($this->checkNameItemId > 0 && (in_array('collect_date', $columns)))
                        ||
                        ($queryRange == 1 && $this->checkNameItemId > 0) // 如果没有指定字段也要处理
                    ) {
                        // 移除原来的 collect_date（如果存在）
                        $columns = array_filter($columns, fn($c) => $c !== 'collect_date');
                        // 添加格式化后的 collect_date
                        $dateFormat = $this->LocalService()->itemCheckdate->getDbDateFormat($this->checkNameItemId);
                        $columns['collect_date'] = new Expression("DATE_FORMAT(FROM_UNIXTIME(collect_date), ?)", $dateFormat);
                    }

                    $params['where']['columns'] = $columns;
                }
                $executeMethod = 'selectWith';
            } elseif ($method === 'update') {
                $sqlObject = $this->tableGateway->getSql()->update();
                $sqlObject->set($params['data']);
                $sqlObject->where($params['where']);
                $executeMethod = 'updateWith';
            }

            $this->handleExecuteParams($params, $sqlObject, $method);
            // echo $this->tableGateway->getSql()->buildSqlString($sqlObject);exit;
            if ($this->printSql === true) {
                return $this->tableGateway->getSql()->buildSqlString($sqlObject);
            }

            $startExecuteTime = microtime(true) * 1000;

            if (class_exists(Coroutine::class) && Coroutine::getCid() > -1) {
                $statement = ConnectPool::db()->prepare($this->tableGateway->getSql()->buildSqlString($sqlObject));
                $statement->execute();
                $result = $statement->fetchAll(\PDO::FETCH_ASSOC);
            } else {
                if ($this->isMemoryCache === true) {
                    $memoryCachedKey = md5($this->tableGateway->getSql()->buildSqlString($sqlObject));
                    if (array_key_exists($memoryCachedKey, self::$memoryCache)) {
                        $isCachedVal = true;
                        $result = self::$memoryCache[$memoryCachedKey];
                    }
                }
                if ($isCachedVal === false) {
                    $result = $this->tableGateway->$executeMethod($sqlObject);
                }
            }

            if (is_object($result)) {
                $result = $result->toArray();
            }

            if($this->checkNameItemId == '' && $method === 'query' && ($this->tableName == 'yikeen_item_identificationresultchange' || $this->tableName == 'yikeen_item_csae' || $this->tableName == 'yikeen_item_patientworkannex')){

                $redis = Laminas::$serviceManager->redisExtend->setDatabase(6)->getRedisInstance();
                if(!isset($params['columns']) || in_array('collect_date',$params['columns'])){
                    foreach($result as &$v){
                        if($v['collect_date'] ?? '' ) {
                            $dateType = $redis->get("DateFormat:{$v['item_id']}");
                            $DateFormat = !empty($dateType) ? $dateType : 'Y-m-d' ;
                            $v['collect_date'] = date($DateFormat, $v['collect_date']);
                        }
                    }
                }
            }

            if($method === 'query' && !empty($result) && $isCachedVal === false) {
                $result = self::decryptField($result);
                if (!$this->isShowMaskingField && !empty($this->maskingFieldList)) {
                    array_walk_recursive($result, function (&$value, $key) {

                        foreach ($this->maskingFieldList as $field) {
                            if(is_array($value) && isset($value[$field])) {
                                $value[$field] = Com::maskingPhoneNumber($value['field']);
                            }else {
                                if($field == $key) $value = Com::maskingPhoneNumber($value);
                            }
                        }
                    });
                }
                // 受试者隐私扩展属性脱敏（扩展属性都放在再patient_content字段中）
                $this->handleMaskingPatientattrValue($result);
            }

//            if ($method === 'query' && !empty($result)) {
//                $result = self::decryptField($result);
//            }
            if (!empty($this->is_print_sql)){
                $this->setDbProfile([
                    'cache' => $isCachedVal,
                    'duration' => round(((microtime(true) * 1000) - $startExecuteTime), 2) . ' ms',
                    'executeSql' => $this->tableGateway->getSql()->buildSqlString($sqlObject),
                    'file' => debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 10)
                ]);
            }

            // 缓存
            if ($this->isMemoryCache === true) {
                self::$memoryCache[$memoryCachedKey] = $result;
            }

            //受试者隐私属性过滤
            if($method === 'query' && $this->tableName == 'yikeen_item_patient'){
                $event = Laminas::$app->getMvcEvent();
                if ($event instanceof \Laminas\Mvc\MvcEvent) {
                    $routeMatch = $event->getRouteMatch();
                    if ($routeMatch) {
                        $controllerName = $routeMatch->getParam('controller', 'default-controller');
                        $actionName = $routeMatch->getParam('action', 'default-action');
                        if($controllerName.$actionName != 'Item\Controller\PatientControlleredit'){

                            $string = $params['where']['where'];
//                        $IDidentifying = preg_match('/\bid\s*=\s*\d+/', $string, $matches);

                            if (preg_match('/item_id\s*=\s*\d+/', $string, $matches) ) {
                                $itemIdWhere = $matches[0];  // 提取完整的item_id部分
                                $patientHideAttrs = $this->LocalService()->patientattrs->fetchCol('attr_field',"is_hide = 1 and is_del = 0 and ".$itemIdWhere);

                                $predefine = ['name'=>'patient_name','sex'=>'patient_sex','number'=>'patient_number','ns'=>'patient_name_easy'];

                                if(!empty($patientHideAttrs) && !empty($result)) {

                                    foreach($result as $key=>$value)
                                    {
                                        foreach ($patientHideAttrs as $attrK =>$attrV){
                                            if(isset($result[$key][$predefine[$attrV]])) $result[$key][$predefine[$attrV]] = '';
                                        }
                                    }

                                }
                            }
                        }
                    }
                }

            }

            return $result;
        } catch (InvalidQueryException $exception) {
            throw new DbException($exception->getMessage(), $this->tableGateway->getSql()->buildSqlString($sqlObject));
        } finally {
            $this->isMemoryCache = false;
        }
    }

    /**
     * 处理拼接参数
     * @param array $params
     * @param $sqlObject
     * @param string $method
     */
    private function handleExecuteParams(array $params, $sqlObject, string $method = 'query')
    {
        if ($method == 'query' && isset($params['where'])) {
            foreach ($params['where'] as $key => $param) {
                if (method_exists($sqlObject, $key) && $param) {
                    if ($key == 'columns') {
                        $param = [$param, true];
                        $sqlObject->$key(...$param);
                    } else if($key == 'where') {
                        if (is_array($param)) {
                            $whereObj = new Where();
                            $this->setWhereParamsByArray($param, $whereObj);
                            $sqlObject->where($whereObj);
                        }else {
                            $sqlObject->$key($param);
                        }
                    }else {
                        $sqlObject->$key($param);
                    }
                }
            }
        }
    }

    /**
     * @param array $params
     * @param Where $whereObj
     * @param string $Combination
     * @return void
     */
    private function setWhereParamsByArray(array $params, Where &$whereObj, string $Combination = PredicateSet::OP_AND) : void
    {
        foreach ($params as $key => $value) {
            if(is_array($value) && ( is_numeric($key) || in_array(strtoupper($key), [PredicateSet::OP_OR, PredicateSet::OP_AND]) ) ) {
                if( ArrayHelper::isOneDimensionalArray($value)) {
                    $where = new Where();
                    $where->addPredicates($value, is_numeric($key) ? PredicateSet::OP_AND : strtoupper($key));

                    $whereObj->addPredicates([ $where ], $Combination);
                }else {
                    //foreach($value as $index => $item) {
                    $where = new Where();
                    // $this->setWhereParamsByArray($item, $where, is_numeric($index) ? PredicateSet::OP_AND : strtoupper($index));
                    $this->setWhereParamsByArray($value, $where, is_numeric($key) ? PredicateSet::OP_AND : strtoupper($key));

                    $whereObj->addPredicates([ $where ], $Combination);
                    //}
                }
            }else {
                $whereObj->addPredicates([
                    $key => $value,
                ], $Combination);
            }
        }
    }

    private function setDbProfile($message = [])
    {
        self::$profile[] = $message;
    }

    public static function getDbProfile()
    {
        return self::$profile;
    }

    private function getTableFields(){
        $sql = 'show full fields from `'.$this->tableName.'`';
        return $this->dbAdapter->query($sql, [])->toArray();
    }

    /**
     * Notes: 保存数据前处理int类型字段的数据。为空或空字符串，赋值为 0
     * User: llbjj
     * DateTime: 2022/9/27 16:56
     *
     * @param array $saveData
     */
    private function handleSaveData(array &$saveData) {
        $tableFieldData = $this->getTableFields();
        $checkFieldData = [];
        foreach($tableFieldData as &$fieldData) {
            if(strpos($fieldData['Type'], 'int') !== false) {
                array_push($checkFieldData, $fieldData['Field']);
            }
        }

        foreach($saveData as $field => &$val) {
            if(in_array($field, $checkFieldData)) {
                $val = $val ?: 0;
            }
        }
    }

    /*
     * 处理加密字段
     */
    private function handleExecuteCryptField(array &$data,$method) {
        if(!empty($this->cryptFields)) {
            // 重构查询列
            if(!isset($data['columns']) || $data['columns'] == ['*']) {
                $data['columns'] = [];
                foreach(self::getTableFields() as &$fieldData) {
                    array_push($data['columns'], $fieldData['Field']);
                }
            }

            // 处理机密字段
            if($cryptFields = array_intersect($this->cryptFields, $data['columns'])) {
                foreach($cryptFields as $field) {
                    $data['columns'][$field] = new Expression("AES_DECRYPT(UNHEX(`{$field}`), '{$this->cryptKey}')");
                    if (isset($data['columns'][current(array_keys($data['columns'], $field))])) {
                        unset($data['columns'][current(array_keys($data['columns'], $field))]);
                    }
                }
            }

            // 处理where查询条件，目前先支持字符串where，后面根据情况再兼容数组类型
            if(isset($data['where']) && is_string($data['where'])) {
                foreach($this->cryptFields as $field) {
                    if($field == 'patient_name'){
                        $data['where'] = preg_replace('/\bpatient_name\b/i', "AES_DECRYPT(UNHEX(`{$field}`), '{$this->cryptKey}')", $data['where']);
                    }else{
                        $data['where'] = str_ireplace($field, "AES_DECRYPT(UNHEX(`{$field}`), '{$this->cryptKey}')", $data['where']);
                    }
                }
            }
        }
        if($method === 'query' && in_array($this->tableName, [
                'yikeen_item_identificationresultchange',
                'yikeen_item_csae',
                'yikeen_item_patientworkannex',
//                'yikeen_item_patient',
//                'yikeen_item_patientattr',
            ]))
        {

            if(is_string($data['where'])){
                preg_match('/item_id\s*=\s*(\d+)/', $data['where'], $matches);
                $this->checkNameItemId = isset($matches[1]) ? (int)$matches[1] : '';
            }elseif(is_array($data['where'])){
                if(isset($data['where']['item_id'])) $this->checkNameItemId = $data['where']['item_id'];
            }elseif(is_object($data['where'])){
                foreach ($data['where']->getPredicates() as $row) {
                    [$combine, $predicate] = $row;
                    if ($predicate instanceof Operator) {
                        if ($predicate->getLeft() === 'item_id') {
                            $this->checkNameItemId = $predicate->getRight();
                            break;
                        }
                    }
                }
            }


            //项目ID参数不存在，追加
            if(isset($data['columns']) && !in_array('item_id',$data['columns'])){
                $data['columns'][] = 'item_id';
            }
//            if(!isset($data['columns']) && $this->checkNameItemId > 0){
//                $sqlColumns = '*';
//                $dateFormat = $this->LocalService()->itemCheckdate->getDbDateFormat($this->checkNameItemId);
//                $dateFormatQuoted = "'{$dateFormat}'";
//                $collectDateExpr = "DATE_FORMAT(collect_date, $dateFormatQuoted) AS collect_date";
//                $sqlColumns .= ', ' . $collectDateExpr;
//                $data['columns'][] = new Expression($sqlColumns);
//            }else{
//                if($this->checkNameItemId > 0 && isset($data['columns']) && in_array('collect_date',$data['columns'])){
//                    $dateFormat = $this->LocalService()->itemCheckdate->getDbDateFormat($this->checkNameItemId);
//                    $dateFormatQuoted = "'{$dateFormat}'";
//                    $data['columns']['collect_date'] = new Expression("DATE_FORMAT(collect_date, $dateFormatQuoted)");
//                }
//            }

        }
    }

    public function isSetInfo(bool $status = false): AbstractDb
    {
        $this->is_create_info = $status;
        $this->is_update_info = $status;
        return $this;
    }

    public function isShowMaskingField(bool $isShowMaskingField = false): AbstractDb
    {
        $this->isShowMaskingField = $isShowMaskingField;
        return $this;
    }

    public function getExecuteSql()
    {
        $this->printSql = true;
        return $this;
    }


    //受试者表，隐私字段不进行处理，加密
    public function patientPrivacy()
    {
//        if($this->tableName == 'yikeen_item_patient')
    }

    /**
     * @note 使用edc数据库查询(暂时先这么用，后面需要再兼容其他)
     * @return $this
     */
    public function edcDbAdapter(bool $isEdc = false) : AbstractDb
    {
        if($isEdc) {
            $this->dbAdapter = $this->LocalService()->edcDbAdapter;
            $this->init();
        }

        return $this;
    }

    private function init() {

        $this->tableGateway = new TableGateway($this->tableName, $this->dbAdapter);

        // 非开发机关闭调试
        if (stripos($_SERVER['HTTP_HOST'] ?? '', 'kingdone') === false) {
            $this->is_print_sql = 0;
        }

        if (Db::$isTransaction === true) {
            $adapterId = spl_object_id($this->dbAdapter);
            if (!isset(Db::$transactionMap[$adapterId])) {
                Db::$transactionMap[$adapterId] = $this->dbAdapter;
                $this->dbAdapter->getDriver()->getConnection()->beginTransaction();
            }
        }
    }

    /**
     * @note 初始是否显示脱敏字段完整信息
     * @return void
     */
    private function initShowMaskingField() {
        $this->isShowMaskingField = $this->LocalService()->identity->isValidMaskingAdmin();
    }

    private function handleMaskingPatientattrValue(array &$patientDataList)
    {
        if ( $this->tableName == 'yikeen_item_patient' && ! $this->isShowMaskingField) {
            foreach($patientDataList as $patientField => &$patientData) {
                if (is_array($patientData)) {
                    $this->handleMaskingPatientattrValue($patientData);
                }else if($patientField === Patient::PATIENT_ATTR_CONTENT_FIELD) {
                    $privacyPatientAttrs = $this->LocalService()->patientattrs->getPrivacyPatientAtts($patientDataList['item_id'] ?? 0);
                    $patientData = Json::decode($patientData, Json::TYPE_ARRAY);

                    foreach($patientData as &$patientAttrData) {
                        if(in_array( $patientAttrData['field'], $privacyPatientAttrs)) {
                            $patientAttrData['value'] = Com::maskingPhoneNumber($patientAttrData['value']);

                            $mapPatientFieldProp =  Patientattrs::CUSTOM_ATTR_MAP_PATIENT_FIELD[$patientAttrData['field']] ?? '';
                            if(
                                $mapPatientFieldProp && isset($patientDataList[$mapPatientFieldProp])
                            ) {
                                $patientDataList[$mapPatientFieldProp] = Com::maskingPhoneNumber($patientDataList[$mapPatientFieldProp]);
                            }
                        }
                    }

                    $patientData = Json::encode($patientData);
                }
            }
        }
    }
}
