自定义查询

引言

• 系统新增数据之后，需要有列表进行数据的查询，3.0代码架构支持自定义查询，下面以期刊任职列表页进行说明

配置页面和权限

• 先去router/index.ts配置出对应的列表，配置的name后续会用到，下文说的路由的name就是这里配置的
  

• 配置列表显示名字，表名是message，Id等于配置路由时的name
  

• 配置列表权限，表名是iam_authorities，Id等于配置路由时的name，parent_Id等于列表的父级菜单Id
  

• 配置出页面和权限之后，系统在进入页面时会去请求接口/queries/{id}/executions,id就是路由的name,然后就会根据自定义查询返回对应的数据，生成表单。

  配置自定义查询

• 在query_definitions表中插入一条数据

  insert into query_definitions(id, code, name, parameters, query, parent_feature_id, fields, default_sort, require_authorities, tenant_id, actions)
  values ('journal-appointment.list', '0092', '期刊任职列表', '[
  {
    "key": "q",
    "type": "text",
    "label": "关键词",
    "placeholder": "请输入姓名或者工号查询",
    "span": 12
  },
  {
    "key": "department",
    "type": "department-selector",
    "label": "所属部门",
    "placeholder": "请选择所属部门",
    "span": 12
  },
  {
    "key": "journalName",
    "type": "text",
    "label": "期刊名称",
    "placeholder": "请输入期刊名称",
    "span": 12
  },
  {
    "key": "journalISSN",
    "type": "text",
    "label": "期刊ISSN号",
    "placeholder": "请输入期刊ISSN号",
    "span": 12
  },
  {
    "key": "journalInfo",
    "type": "text",
    "label": "期刊收录情况",
    "placeholder": "请输入期刊收入情况",
    "span": 12
  },
  {
    "key": "appointedPosition",
    "type": "text",
    "label": "聘任职务",
    "placeholder": "请选择聘任职务",
    "span": 12
  },
  {
    "key": "datetimeRange",
    "type": "date-range",
    "label": "任职日期",
    "placeholder": "请输入任职日期",
    "span": 12
  },
  {
    "key": "appointState",
    "type": "association-job-state",
    "label": "任职状态",
    "placeholder": "请选择任职状态",
    "span": 12
  },
  {
    "key": "processSchedule",
    "type": "text",
    "label": "流程进度",
    "placeholder": "请输入流程进度",
    "span": 12
  }
  ]', 'SELECT
    ja.id id,
        pp.code user_code,
        pp.name user_name,
        dp.name department_name,
        dj.name journal_name,
        ja.appointed_position,
        st.name appoint_state,
        ja.start_date,
        ja.end_date,
        ja.state,
        ja.task_id,
        ja.process_schedule process_schedule
  FROM personnel_journal_appointments ja
  left join database_journals dj  on ja.journal_id = dj.id and dj.tenant_id = :tenantId
  left join (select journal_id,string_agg(database_names, '','' ORDER BY journal_id) database_names from database_journal_inclusion_infos
  group by journal_id) djd on dj.id = djd.journal_id
  left join personnel_persons pp on ja.user_id = pp.id  and pp.tenant_id = :tenantId
  left Join personnel_departments dp on pp.department_id = dp.id and dp.tenant_id = :tenantId
  left JOIN tags pt on ja.appointed_position = pt.id and pt.tenant_id = :tenantId
  left JOIN tags st on ja.appoint_state = st.id and st.tenant_id = :tenantId
  where ja.deleted = false AND ja.state <> ''draft''  AND ja.tenant_id = :tenantId
  AND (CAST(:q AS TEXT) IS NULL OR pp.name LIKE ''%'' || :q || ''%'' OR pp.code LIKE ''%'' || :q || ''%'')
  AND (CAST(:department AS TEXT) is null OR pp.department_id = :department)
  AND (CAST(:journalName AS TEXT) is null OR dj.name LIKE ''%'' || :journalName || ''%'')
  AND (CAST(:journalISSN AS TEXT) is null OR dj.issn = :journalISSN )
  AND (CAST(:journalInfo AS TEXT) is null OR djd.database_names like  ''%'' || :journalInfo || ''%'' )
  AND (CAST(:appointedPosition AS TEXT) is null OR ja.appointed_position = :appointedPosition)
  AND ((CAST (:datetimeRange_start AS text) is null or CAST (:datetimeRange_end AS text) is null)
    OR ((ja.start_date >= CAST (:datetimeRange_start AS timestamp) And ja.start_date <= CAST(:datetimeRange_end AS timestamp))
        OR (ja.end_date >= CAST (:datetimeRange_start AS timestamp) And ja.end_date <= CAST(:datetimeRange_end AS timestamp))))
  AND (CAST(:appointState AS TEXT) is null OR ja.appoint_state = :appointState)
  AND (CAST(:processSchedule AS TEXT) IS NULL OR ja.process_schedule like ''%'' || :processSchedule || ''%'')', '', '[
  {
    "key": "id",
    "type": "journal-appointment",
    "hidden": true,
    "primary": true
  },
  {
    "key": "processSchedule",
    "type": "button",
    "width": "120px",
    "label": "流程进度",
    "align": "left"
  },
  {
    "key": "userCode",
    "type": "text",
    "width": "120px",
    "label": "工号",
    "align": "left"
  },
  {
    "key": "userName",
    "type": "text",
    "width": "120px",
    "label": "姓名",
    "align": "left"
  },
  {
    "key": "departmentName",
    "type": "text",
    "width": "200px",
    "label": "所属部门",
    "align": "left"
  },
  {
    "key": "journalName",
    "type": "text",
    "width": "200px",
    "label": "期刊名称",
    "align": "left"
  },
  {
    "key": "appointedPosition",
    "type": "text",
    "width": "200px",
    "label": "聘任职务",
    "align": "left"
  },
  {
    "key": "startDate",
    "type": "date",
    "width": "200px",
    "label": "任职开始日期",
    "align": "left"
  },
  {
    "key": "endDate",
    "type": "date",
    "width": "200px",
    "label": "任职结束日期",
    "align": "left"
  },
  {
    "key": "appointState",
    "type": "text",
    "width": "200px",
    "label": "任职状态",
    "align": "left"
  }
  ]', '[{
  "prop": "ja.created_date",
  "order": "desc"
  }]', 'authenticated', 'a1fa8c64c75f4bc5b520bd6b8af5f6f9', '[]')

• 自定义查询表query_definitions
  

• Id

  Id的值等于配置路由时的name

• code

  code根据序号自增

• name

  自定义查询的名字

• parameters

  数据结构如下：
  
  key是sql语句中变量的参数名，type是要使用的组件，label是要显示的名字，placeholder组件的提示语，span是组件要占的宽度
  key=q时代表的是模糊查询通常会放在列表页，其他查询条件会在高级查询的对话框里

• query

  
  这是列表查询时要执行的sql语句，里面的:参数名就是paremeters字段里面定义的key。sql返回结果的列名会在fields中使用到。sql语句要返回id,task_id,state,以便后续操作使用

• parent_feature_id

  暂未使用

• fields

  
  key是绑定列的名称，根据sql语句的返回结果绑定列的数据，系统会将sql语句的列名（如”user_code”）转换成小驼峰的命名形式，所以这里的key也需要使用小驼峰（如”userCode”）的形式要绑定列。type是要使用的组件。width表格列的宽度。label表格列显示的名字。align文字显示格式。hidden是否显示列。primary是否为主键列这个字段是后续的增删改操作需要用到的。

• defalult_sort

  用来控制列表的排序,prop来指定排序的字段，order指定升序降序
  

• require_authorities

  用来控制访问列表的权限

• tenant_id

  租户ID

• actions

  暂无使用