php 动态表头

一、表

CREATE TABLE `ly_admin_bill` (
  `fid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `ftid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '对象id',
  `fname` varchar(200) DEFAULT NULL COMMENT '中文名称',
  `ftable` varchar(200) DEFAULT NULL COMMENT '实例名称',
  `fprefix` varchar(200) DEFAULT 'ly_' COMMENT '前缀',
  `fipal` tinyint(1) DEFAULT '1' COMMENT '{1:无主从2:有主从}',
  `fstatus` tinyint(1) DEFAULT '1' COMMENT '状态{1:启用2:禁用}',
  PRIMARY KEY (`fid`)
) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8 COMMENT='系统单据表';

View Code

CREATE TABLE `ly_admin_fields` (
  `fid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `fname` varchar(255) DEFAULT NULL COMMENT '字段中文名称',
  `ftitle` varchar(255) DEFAULT NULL COMMENT '属性名称',
  `ftype` varchar(255) NOT NULL DEFAULT '' COMMENT '字段类型',
  `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '别名',
  `fcontent` varchar(255) DEFAULT NULL COMMENT '注释',
  `fnote` varchar(255) DEFAULT NULL COMMENT '备注',
  `fuse` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '常用字段{1:是2:否}',
  `fenter` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否必录{1:是2:否}',
  `fcount` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否合计',
  `fshow` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否显示{1:显示2:隐藏}',
  `fstatus` tinyint(1) unsigned DEFAULT '1' COMMENT '状态{1:启用2:禁用}',
  PRIMARY KEY (`fid`),
  KEY `fstatus` (`fstatus`),
  KEY `ftype` (`ftype`(191)),
  KEY `fshow` (`fshow`),
  KEY `ftitle` (`ftitle`)
) ENGINE=InnoDB AUTO_INCREMENT=390 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='字段表';

View Code

CREATE TABLE `ly_admin_option` (
  `fid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `fbid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '单据tid',
  `ffid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '字段id',
  `fsort` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  `fipal` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '关联表',
  PRIMARY KEY (`fid`)
) ENGINE=InnoDB AUTO_INCREMENT=393 DEFAULT CHARSET=utf8 COMMENT='字段单据关系表';

View Code

二、代码

1、动态表头 

 public function index()
  {
    $type   = input('type', '', 'trim');
    $fields = $this->search_fields($this->BILLTYPE);
    $field  = $this->fieldsToStr($fields);
    $data   = $this->model->get_list($field, $this->pageSize,0,$type,$this->get_search());
    if ($type == 'excel') {
      $field_Arr = array_column($fields, 'zname', 'field');
      $this->exploadExcel($field_Arr, $data['list'], "代理商指标导出");
    }
    return json(["data" => $data['list'], "field" => $fields, "total" => $data['total']]);
  }

View Code

 public function search_fields ($id,$type = '')
    {
        if(!$type){
            $where['f.fshow'] =["eq",1];
        }
        $where['f.fstatus'] = ["eq",1];
        $where['o.fbid'] = ["eq",$id];
        $list = db("admin_option o")
            ->where($where)
            ->field("f.fname as zname,f.ftitle as field,f.ftype as type,b.ftid,o.fipal as ipal,f.fenter as enter,b.ftable,f.alias,f.fcount")
            ->join("admin_bill b",'b.ftid=o.fbid','left')
            ->join("admin_fields f",'o.ffid=f.fid','left')
            ->order('o.fsort asc')
            ->select();
        foreach ($list as &$item)
        {
           if($item['ipal'])
           {
             $item['ftable'] = db("admin_bill")->where('ftid',$item['ipal'])->value("ftable");
           }
        }
        return $list;
    }

    public function fieldsToStr ($fields)
    {
        $field = "";
        if(count($fields)>1) {
            foreach ($fields as $v) {
                if($v['ipal']){
                    $tablename = db("admin_bill")->where('ftid',$v['ipal'])->value("ftable");
                    $field .= $tablename . "." . $v['field'] ." as ". $v['alias'] .",";
                }else{
                    $field .= $v['ftable']   . ".".$v['field'] . ",";
                }
            }
            $field = substr($field,0,-1);
        }elseif(count($fields) == 1){
            $field = $fields['ftid'] . ".".$field;
        }elseif(count($fields) < 1){
            //无显示字段
            $field = "";
        }
        return $field;
    }

View Code

2、动态筛选

  protected function get_search(){

        $search = $this->param['search'] ?? [];

        $Filter = new Filter($search);
        if(!$Filter->check()){
            throw new \Exception($Filter->errorMsg);
        }
        $Filter->search_handle();
        return $Filter->get();
    }

View Code

<?php
namespace search;
class Filter
{
    public $errorMsg = '';

    protected $search = null;
    protected $where = [];
    private $type = [
        '等于'     =>'eq',
        '不等于'   =>'neq',
        '大于'     =>'gt',
        '大于等于'  =>'egt',
        '小于'     =>'lt',
        '小于等于'  =>'elt',
        '包含'     =>'like',
        '不包含'   =>'not like',
        '左包含'   =>'like',
        '右包含'   =>'like',
    ];


    public function __construct($search=[])
    {
        $this->setSearch($search);
    }

    public function search_handle (){
        foreach ($this->search as $da){
            $da = json_decode($da,256);
            $this->where[$da['field']] = $this->subType($da['type'],$da['value']);
        }
    }


    public function check(){
        foreach ($this->search as $ar){
            $ar = json_decode($ar,256);

            if(count($ar) != 3){
//                return $this->setError('过滤条件数据格式不正确');
            }
            if(!$ar['field'] ){
                return $this->setError('param missing field');
            }
            if(!$ar['type']){
                return $this->setError('param missing type');
            }
            if(!$ar['value']){
                continue;
            }
        }
        return true;
    }


    private function setSearch($search){
        $this->search = $search;
    }

    public function get (){
        return $this->where;
    }

    private function setError ($errMsg){
        $this->errorMsg = $errMsg;
        return false;
    }

    private function subType($t,$v){
        switch ($t){
            case '包含':
                $v = '%'.$v.'%';
                break;
            case '不包含':
                $v = '%'.$v.'%';
                break;
            case '左包含':
                $v = '%'.$v;
                break;
            case '右包含':
                $v = $v.'%';
                break;
        }
        return [$this->type[$t],$v];
    }

    public function getType (){
        $a = array_keys($this->type);
        return json($a);
    }
}

View Code