nm-project
/
aiccs-api
13
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
aiccs-api/db/init/AICCS/视图.sql

13 lines
7.2 KiB
MySQL
Raw Permalink Normal View History

docs: 优化迁移检测文档并添加数据库初始化脚本 - 精简迁移检测实施方案，聚焦核心检测机制 - 新增MyBatis-Plus持久层一致性检测机制设计 - 新增跨数据库SQL兼容性检测方案 - 添加db目录，包含达梦数据库建表SQL和视图定义 - 删除冗余的包重新计入公示SQL文件
2026-01-05 14:53:10 +08:00
CREATE OR REPLACE VIEW BASE_VIEW(BASECODE,BASENAME,CHILDCODE,CHILDNAME) AS SELECT 'regEntityType' AS "BASECODE",'企业类型' AS "BASENAME","TRENTTYPE".ENTTYPEID AS "CHILDCODE","TRENTTYPE".ENTTYPENAME AS "CHILDNAME" FROM TRENTTYPE WHERE (length(rtrim("TRENTTYPE".ENTTYPEID)) = 4 OR "TRENTTYPE".ENTTYPEID IN ('12100', '12200', '12500', '13100', '14100', '15000', '15100', '18000', '24000', '26000', '26100', '26200', '20000', '27000') ) UNION ALL SELECT 'EntityType' AS "BASECODE",'企业类型' AS "BASENAME","TRENTTYPE".ENTTYPEID AS "CHILDCODE","TRENTTYPE".ENTTYPENAME AS "CHILDNAME" FROM TRENTTYPE UNION ALL SELECT 'EntityClsType' AS "BASECODE",'企业类型' AS "BASENAME","TRENTTYPE".ENTTYPEID AS "CHILDCODE","TRENTTYPE".ENTTYPENAME AS "CHILDNAME" FROM TRENTTYPE WHERE length(rtrim("TRENTTYPE".ENTTYPEID)) = 4 UNION ALL SELECT 'EntPriseType' AS "BASECODE",'企业子类型' AS "BASENAME","TRENTPRISETYPE".ENTTYPEID AS "CHILDCODE","TRENTPRISETYPE".DISPLAYVALUE AS "CHILDNAME" FROM TRENTPRISETYPE UNION ALL SELECT "TRBASECODE".CODEID AS "BASECODE",'TRBaseCode' AS "BASENAME","TRBASECODE".CODE AS "CHILDCODE","TRBASECODE".NAME AS "CHILDNAME" FROM TRBASECODE UNION ALL SELECT 'AicCode' AS "BASECODE",'工商机构代码' AS "BASENAME","TRAICCODE".AICID AS "CHILDCODE","TRAICCODE".AICNAME AS "CHILDNAME" FROM TRAICCODE UNION ALL SELECT 'IndustryKind' AS "BASECODE",'行业门类' AS "BASENAME","TRINDUSTRYINFO".FULLCODE AS "CHILDCODE","TRINDUSTRYINFO".CODENAME AS "CHILDNAME" FROM TRINDUSTRYINFO WHERE length("TRINDUSTRYINFO".FULLCODE) = 1 UNION ALL SELECT 'IndustryCo' AS "BASECODE",'行业代码' AS "BASENAME",substr("TRINDUSTRYINFO".FULLCODE, 2, 5) AS "CHILDCODE","TRINDUSTRYINFO".CODENAME AS "CHILDNAME" FROM TRINDUSTRYINFO WHERE length("TRINDUSTRYINFO".FULLCODE) > 1 UNION ALL SELECT 'SupervisionMeasure' AS "BASECODE",'监管措施' AS "BASENAME","TSSUPERVISIONMEASURE".SUPMEASURECODE AS "CHILDCODE","TSSUPERVISIONMEASURE".SUPMEASURENAME AS "CHILDNAME" FROM TSSUPERVISIONMEASURE UNION ALL SELECT 'TSBizSerIllegalReas' AS "BASECODE",'企业列入严重违法企业名单原因' AS "BASENAME","TSBIZSERILLEGALREAS".REACODE AS "CHILDCODE","TSBIZSERILLEGALREAS".REANAME AS "CHILDNAME" FROM TSBIZSERILLEGALREAS UNION ALL SELECT 'Coin' AS "BASECODE",'货币' AS "BASENAME","TRCOIN".COINID AS "CHILDCODE","TRCOIN".NAME AS "CHILDNAME" FROM TRCOIN;
CREATE OR REPLACE VIEW case_report_view AS SELECT b.BIZSEQ AS "BIZSEQ",( SELECT SUBSTR(t.CASENO, 1, 6) || '000' || '1' || TO_CHAR(t.handlerDate, 'YYYYMMDD') || LPAD(( SELECT COUNT(*) + 5001 FROM tscase AS t2 WHERE (SUBSTR(t2.CASENO, 1, 6) = SUBSTR(t.CASENO, 1, 6) AND TRUNC(t2.handlerDate) = TRUNC(t.handlerDate) AND t2.BIZSEQ <= t.BIZSEQ )), 4, '0') FROM SYS.SYSDUAL) AS "CASENO",'监督检查' AS "CASESOU",REPLACE(REPLACE(t.situation, '\n', ' '), '\r', ' ') AS "CASECON",t.registTime AS "CLUETIME",'市场准入案件' AS "CASEAREA",t.caseName AS "CASENAME",REPLACE(REPLACE(t.situation, '\n', ' '), '\r', ' ') AS "CASEREASON",r.PUNISHORGCN AS "CASEFIAUTHNAME",t.handlerDate AS "CASEFIDATE",r.PUNISHORGCN AS "CASEDEPNAME",'给予行政处罚' AS "PENRESULT",'行政处罚决定执行完毕' AS "CASEENDTYPE",r.REVOKEDATE AS "CASEENDDATE",'法人或其他组织' AS "PARTYTYPE",e.ENTNAME AS "UNITNAME",e.PRIPID AS "PRIPID",e.UNISCID AS "UNISCID",e.NAME AS "LEREP",e.DOM AS "DOM",e.phone AS "TEL",e.CERTYPE AS "CERTYPE",e.CERNO AS "CERNO",REPLACE(REPLACE(t.hearingToApprRea, '\n', ' '), '\r', ' ') AS "ILLEGFACT",r.REVOKEDATE AS "PENDECISSDATE",'公司成立后无正当理由超过6 个月未开业，或者开业后自行停业连续 6 个月以上的行为' AS "ILLEGACTTYPENAME",'根据《公司法》《个人独资企业法》规定，(公司、个人独资企业)成立后无正当理由超过六个月未开业的，或者开业后自行停业连续六个月以上的，可以由登记机关吊销营业执照。' AS "PENBASIS",'' AS "DISCRETIONFACT",'' AS "DISCRETIONAPP",REPLACE(REPLACE(t.advicePunishContent, '\n', ' '), '\r', ' ') AS "PENCONTENT",'吊销许可证件' AS "PENTYPENAME",'' AS "CASEVAL",'' AS "PENAM",'' AS "REFAMOORD",'' AS "FORFAM",'' AS "FORGOODS",r.PUNISHORGCN AS "PENAUTHNAME",b.PRE_PUN_DEC_WRITSNO AS "PENDECNO",'主动履行' AS "EXETYPE",'是' AS "PUBLISH" FROM biz_rev_ent AS b,revokelist AS r,tscase AS t,e_baseinfo AS e WHERE (b.BIZSEQ = t.BIZSEQ AND t.caseNo = r.CASENO AND r.PRIPID = e.PRIPID AND b.PRIPID = r.PRIPID AND r.REVOKEDATE >= TO_DATE('2021-06-03', 'YYYY-MM-DD') ) ORDER BY "CASEFIDATE" ASC,"BIZSEQ" ASC;
CREATE OR REPLACE VIEW ENTLOCKVIEW(PRIPID,PUBLICFROM,ENTNAME) AS SELECT "ENTLOCK".PRIPID AS "PRIPID","ENTLOCK".PUNISHFROM AS "PUBLICFROM","ENTLOCK".ENTNAME AS "ENTNAME" FROM ENTLOCK;
CREATE OR REPLACE VIEW EX_GONGSHANG_21_GCLSHTZXYQY(SCZTBM,QYMC,UNISCID,ND,UPDATETIME) AS SELECT ( CASE WHEN "left"("SOV".ENTITYNO, 4) = '4401' THEN "SOV".ENTITYNO ELSE ( SELECT "E".PRIPID FROM E_BASEINFO AS E WHERE "E".UNISCID = "SOV".REGISTERNUM UNION SELECT "E".PRIPID FROM E_BASEINFO AS E WHERE "E".REGNO = "SOV".REGISTERNUM LIMIT( 1)) END ) AS "SCZTBM","SOV".ORGANIZENAME AS "QYMC",( CASE WHEN "left"("SOV".REGISTERNUM, 1) = '9' THEN "SOV".REGISTERNUM ELSE ( SELECT "E".UNISCID FROM E_BASEINFO AS E WHERE "E".REGNO = "SOV".REGISTERNUM LIMIT( 1)) END ) AS "UNISCID","APV".APPRAISEANNUAL AS "ND",( CASE WHEN NOT ("SOV".UPDATETIMESTAMP IS NULL ) THEN "SOV".UPDATETIMESTAMP ELSE "SOV".CREATEDATE END ) AS "UPDATETIME" FROM CREV.SYS_ORGANIZE_VIEW AS SOV LEFT JOIN CREV.ADMIN_PROJECT_VIEW AS APV ON "SOV".ORGANIZEID = "APV".ORGANIZEID WHERE ( NOT ("APV".APPRAISEANNUAL IS NULL ) AND "APV".STATE = '6' );
CREATE OR REPLACE VIEW TB_BATCH_GS_LIST AS SELECT "B".ENTNAME AS "SNAME","T".WRITSNO AS "SBOOKCODE","T".CREATETIME AS "SPUBLICTIME",( CASE "T".WRITSTYPE WHEN '32' THEN '1' ELSE '2' END ) AS "SGSTYPE","T".LAUPTIME AS "UPDATETIME",IF( ("B".ISREMOVE = '1' OR "B".ISHEARINGPUBLISH != '1' OR "B".ISDECISIONPUBLISH != '1' ), '1', '0') AS "DELFLAG" FROM TSWRIT AS T LEFT JOIN BIZ_REV_ENT AS B ON ("T".PRIPID = "B".PRIPID AND "T".BIZSEQ = "B".BIZSEQ ) WHERE ("T".WRITSTYPE IN ('32', '33') AND NOT ("B".BIZSEQ IS NULL ) );
CREATE OR REPLACE VIEW v_fraud_info AS SELECT fe.id AS "id",fe.bizSeq AS "bizSeq",fe.pripid AS "pripid",fe.entName AS "entName",fe.uniScId AS "uniScId",fe.regNo AS "regNo",fe.regTime AS "regTime",fe.regItem AS "regItem",fe.regOrg AS "regOrg",fe.regOrg_cn AS "regOrg_cn",fe.tel AS "tel",fe.noticeFrom AS "noticeFrom",fe.noticeTo AS "noticeTo",fe.name AS "name",fe.handleResult AS "handleResult",fe.handleRea AS "handleRea",fe.handleRea_cn AS "handleRea_cn",fe.judAuth AS "judAuth",fe.judAuth_cn AS "judAuth_cn",fe.judDate AS "judDate",fe.create_by AS "create_by",fe.create_time AS "create_time",fe.update_by AS "update_by",fe.update_time AS "update_time",bf.complaintReason AS "complaintReason",bf.complainerName AS "complainerName",bf.complainerCertNo AS "complainerCertNo",bf.complainerEntName AS "complainerEntName",bf.status AS "status",bf.linkType AS "linkType",bf.business_Type AS "businessType",t.WORKFLOWID AS "workflowId" FROM aiccs.t_fraud_ent AS fe,aiccs.t_biz_fraud AS bf,aiccs.tstasklist AS t WHERE (fe.bizSeq = bf.bizSeq AND bf.bizSeq = t.BIZSEQID );