ebu_ecology_dev1/view-sql-server.sql

-- 流程类型视图，可用于数据集成或流览按钮
if
exists (select * from sysobjects where name = 'workflow_type_info_view')
drop view workflow_type_info_view
    go
create view workflow_type_info_view as
select wb.id,
       wb.workflowname,
       wt.typename,
       wb.workflowdesc,
       (case when wb.version is null then 1 else wb.version end) version
from workflow_base wb
         RIGHT JOIN workflow_type wt on wb.workflowtype = wt.id go


-- 流程表单视图，用于流览按钮或数据集成，配置流程类型表可以用字段联动获取流程表表名
if exists (select * from sysobjects where name = 'workflow_table_view')
drop view workflow_table_view
    go
create view workflow_table_view as
select base.id,
       base.workflowname,
       base.formid,
       bill.tablename,
       (case when base.version is null then 1 else base.version end) version
from workflow_bill bill
         join workflow_base base on base.formid = bill.id go

-- 流程明细表信息，可用流程主表查询对应的明细表信息，用于流览框
if exists (select * from sysobjects where name = 'workflow_detail_table_view')
drop view workflow_detail_table_view
    go
create view workflow_detail_table_view as
select (bill.id + '-' + base.id) id,
       bill.id                   bill_id,
       base.id                   workflow_id,
       base.workflowname,
       base.formid               main_formid,
       bill.tablename
from workflow_billdetailtable bill
         join workflow_base base on base.formid = bill.billid go

-- 流程和建模字段视图，更具流程和建模的billid可以查询流程和建模中的字段信息
-- 准备基础数据

DROP TABLE IF EXISTS cus_workflow_base_field_assist go
CREATE TABLE cus_workflow_base_field_assist
(
    id            INT PRIMARY KEY,
    fieldname     VARCHAR(255) NOT NULL,
    indexdesc     VARCHAR(255) NOT NULL,
    tablename     VARCHAR(255) NOT NULL,
    billid        INT          NOT NULL,
    showtablename VARCHAR(255) NOT NULL,
    fieldhtmltype VARCHAR(255) NOT NULL,
    fieldtype     INT          NOT NULL
)go
INSERT INTO cus_workflow_base_field_assist(id, fieldname, indexdesc, tablename, billid, showtablename, fieldhtmltype,
                                           fieldtype)
VALUES (-999, 'requestname', '请求名称：requestname', 'workflow_requestbase', 999, 'main table', '单行文本', '1') go
INSERT INTO cus_workflow_base_field_assist(id, fieldname, indexdesc, tablename, billid, showtablename, fieldhtmltype,
                                           fieldtype)
VALUES (-998, 'creater', '创建人：creater', 'workflow_requestbase', 999, 'main table', '单行文本', '1');
INSERT INTO cus_workflow_base_field_assist(id, fieldname, indexdesc, tablename, billid, showtablename, fieldhtmltype,
                                           fieldtype)
VALUES (-997, 'createdate', '创建日期：createdate', 'workflow_requestbase', 999, 'main table', '单行文本', '1') go
INSERT INTO cus_workflow_base_field_assist(id, fieldname, indexdesc, tablename, billid, showtablename, fieldhtmltype,
                                           fieldtype)
VALUES (-996, 'createtime', '创建时间：createtime', 'workflow_requestbase', 999, 'main table', '单行文本', '1') go
INSERT INTO cus_workflow_base_field_assist(id, fieldname, indexdesc, tablename, billid, showtablename, fieldhtmltype,
                                           fieldtype)
VALUES (-995, 'requestmark', '请求说明：requestmark', 'workflow_requestbase', 999, 'main table', '单行文本', '1') go
INSERT INTO cus_workflow_base_field_assist(id, fieldname, indexdesc, tablename, billid, showtablename, fieldhtmltype,
                                           fieldtype)
VALUES (-994, 'messagetype', '消息提醒：messagetype', 'workflow_requestbase', 999, 'main table', '单行文本', '1') go
INSERT INTO cus_workflow_base_field_assist(id, fieldname, indexdesc, tablename, billid, showtablename, fieldhtmltype,
                                           fieldtype)
VALUES (-993, 'mainrequestid', '主流程的请求id：mainrequestid', 'workflow_requestbase', 999, 'main table', '单行文本', '1') go
INSERT INTO cus_workflow_base_field_assist(id, fieldname, indexdesc, tablename, billid, showtablename, fieldhtmltype,
                                           fieldtype)
VALUES (-992, 'ecology_pinyin_search', 'ecology_拼音_搜索：ecology_pinyin_search', 'workflow_requestbase', 999, 'main table', '单行文本', '1') go
INSERT INTO cus_workflow_base_field_assist(id, fieldname, indexdesc, tablename, billid, showtablename, fieldhtmltype,
                                           fieldtype)
VALUES (-991, 'requestnamenew', '带标题字段的请求标题：requestnamenew', 'workflow_requestbase', 999, 'main table', '单行文本', '1') go
INSERT INTO cus_workflow_base_field_assist(id, fieldname, indexdesc, tablename, billid, showtablename, fieldhtmltype,
                                           fieldtype)
VALUES (-990, 'requestid', '请求id：requestid', 'workflow_requestbase', 999, 'main table', '单行文本', '1') go
INSERT INTO cus_workflow_base_field_assist(id, fieldname, indexdesc, tablename, billid, showtablename, fieldhtmltype,
                                           fieldtype)
VALUES (-989, 'workflowid', '工作流id：workflowid', 'workflow_requestbase', 999, 'main table', '单行文本', '1') go
-- 创建试图
    if exists (select * from sysobjects where name = 'workflow_field_table_view')
drop view workflow_field_table_view
    go
create view workflow_field_table_view as
select wb.id,
       wb.fieldname,
       (ht.indexdesc + '：' + wb.fieldname) indexdesc,
       (
           case
               when wb.detailtable is null then (select distinct tablename from workflow_bill where id = wb.billid)
               when wb.detailtable = ''
                   then (select distinct tablename from workflow_bill where id = wb.billid)
               else wb.detailtable
               end
           )                               tablename,
       billid,
       (
           case
               when wb.detailtable = '' then 'main table'
               when wb.detailtable is null then 'main table'
               else wb.detailtable end
           )                               showtablename,
       (case
            when wb.fieldhtmltype = '1' then '单行文本框'
            when wb.FIELDHTMLTYPE = '2' then '多行文本框'
            when wb.FIELDHTMLTYPE = '3' then '流览框'
            when wb.FIELDHTMLTYPE = '4' then 'check框'
            when wb.FIELDHTMLTYPE = '5' then '选择框'
            else '附件上传' end)           fieldhtmltype,
       wb.FIELDHTMLTYPE                    fieldtype
from workflow_billfield wb
         left join htmllabelindex ht on wb.fieldlabel = ht.id
union all
select *
from cus_workflow_base_field_assist go

-- 建模表信息视图
if exists (select * from sysobjects where name = 'mode_bill_info_view')
drop view mode_bill_info_view
    go
create view mode_bill_info_view as
select bill.id, bill.tablename, hti.indexdesc
from workflow_bill bill
         left join htmllabelindex hti on hti.id = bill.namelabel
where bill.id < 0
  and bill.tablename like 'uf%' go

-- 流程建模表信息
if exists (select * from sysobjects where name = 'workflow_mode_table_view')
drop view workflow_field_table_view
    go
CREATE VIEW workflow_mode_table_view
AS
select bill.ID           id,
       bill.TABLENAME    tablename,
       base.WORKFLOWNAME indexdesc,
       'workflow'        tabletype
from (
         workflow_bill bill join workflow_base base on ((base.FORMID = bill.ID))
         )
union all
select bill.ID        id,
       bill.TABLENAME tablename,
       hti.INDEXDESC  indexdesc,
       'mode'         tabletype
from (
         workflow_bill bill left join htmllabelindex hti on ((hti.ID = bill.NAMELABEL))
         )
where ((bill.ID < 0) and (bill.TABLENAME like 'uf%')) go

-- 流程节点信息视图
if exists (select * from sysobjects where name = 'workflow_node_info_view')
drop view workflow_node_info_view
    go
create view workflow_node_info_view as
select distinct nb.id,
                nb.nodename,
                (case when wb.version is null then 1 else wb.version end) version,
                fn.workflowid
from workflow_nodebase nb
         left join workflow_flownode fn on nb.id = fn.nodeid
         left join workflow_base wb on wb.id = fn.workflowid
    go