617 Star 1.2K Fork 434

卓源软件 / JeeSite4 JFlow

Create your Gitee Account
Explore and code with more than 5 million developers,Free private repositories !:)
Sign up
Clone or download
jflow_view.sql 3.63 KB
Copy Edit Web IDE Raw Blame History
袁丽娜 authored 2020-05-14 14:51 . no commit message
-- 重命名表
alter table port_dept rename port_dept_bak;
alter table port_emp rename port_emp_bak;
-- alter table port_empdept rename port_empdept_bak;
alter table port_deptemp rename port_deptemp_bak;
alter table port_station rename port_station_bak;
-- alter table port_empstation rename port_empstation_bak;
alter table port_deptempstation rename port_deptempstation_bak;
alter table port_deptstation rename port_deptstation_bak;
alter table port_stationtype rename port_stationtype_bak;
-- 创建部门视图 port_dept
create or replace view port_dept as
SELECT
o.office_code AS No,
o.office_name AS Name,
'' AS NameOfPath,
( CASE WHEN ( o.parent_code = '0' ) THEN '0' ELSE o.parent_code END ) AS ParentNo,
'' AS TreeNo,
'admin' AS Leader,
'' AS Tel,
o.tree_sort AS Idx,
( CASE WHEN ( o.tree_leaf = '0' ) THEN 1 ELSE 0 END ) AS IsDir,
'' AS OrgNo
FROM
js_sys_office o
WHERE
( o.status = '0' );
-- 创建员工视图 port_emp
create or replace view port_emp as
SELECT
u.user_code AS No,
e.emp_code AS EmpNo,
u.user_name AS Name,
'123' AS Pass,
( CASE WHEN ( e.office_code != '' ) THEN e.office_code ELSE (
select oo.office_code from js_sys_office oo where oo.parent_code = '0' limit 1
) END ) AS FK_Dept,
'' AS FK_Duty,
'admin' AS Leader,
u.extend_s1 AS SID,
u.mobile AS Tel,
u.email AS Email,
1 AS NumOfDept,
0 AS Idx,
'' AS `Ding_UserID`,
'' AS `Wei_UserID`,
'' AS `PinYin`,
'' AS `SignType`,
'' AS `OrgNo`
FROM
( js_sys_user u LEFT JOIN js_sys_employee e ON ( ( e.emp_code = u.ref_code ) ) )
WHERE
(
( ( u.status = '0' ) AND ( u.user_type = 'employee' ) AND ( e.status = '0' ) )
OR ( u.login_code = 'system' )
OR ( u.login_code = 'admin' )
);
-- 创建员工部门视图 port_empdept ,表在 JFlow 中已删除,使用 port_deptemp 替代
-- create or replace view port_empdept as
-- SELECT
-- e.No AS FK_Emp,
-- e.FK_Dept AS FK_Dept
-- FROM
-- port_emp e
-- WHERE
-- ( e.FK_Dept IS NOT NULL );
-- 创建部门员工视图 port_deptemp
create or replace view port_deptemp as
SELECT
concat(e.FK_Dept,'_',e.No) AS MyPk,
e.No AS FK_Emp,
e.FK_Dept AS FK_Dept,
'' AS FK_Duty,
0 AS DutyLevel,
'admin' AS Leader,
e.OrgNo AS OrgNo
FROM
port_emp e
WHERE
( e.FK_Dept IS NOT NULL );
-- 创建岗位角色视图 port_station
create or replace view port_station as
SELECT
r.role_code AS No,
r.role_name AS Name,
r.role_type AS FK_StationType,
'' AS DutyReq,
'' AS Makings,
'' AS OrgNo
FROM
js_sys_role r;
-- 创建员工岗位角色视图 port_empstation,表在 JFlow 中已删除,无需重命名,直接创建视图
create or replace view port_empstation as
SELECT
ur.user_code AS FK_Emp,
ur.role_code AS FK_Station
FROM
js_sys_user_role ur;
-- 创建部门员工岗位角色视图 port_deptempstation
create or replace view port_deptempstation as
SELECT
concat(e.FK_Dept,'_',e.No,'_',s.FK_Station) AS MyPk,
e.FK_Dept AS FK_Dept,
s.FK_Station FK_Station,
e.No AS FK_Emp,
e.OrgNo AS OrgNo
FROM
port_emp e, port_empstation s
WHERE
( e.No = s.FK_Emp AND e.FK_Dept IS NOT NULL );
-- 创建部门岗位角色视图 port_deptstation
create or replace view port_deptstation as
SELECT
e.FK_Dept AS FK_Dept,
s.FK_Station FK_Station
FROM
port_emp e, port_empstation s
WHERE
( e.No = s.FK_Emp AND e.FK_Dept IS NOT NULL );
-- 创建岗位角色类型视图 port_stationtype
create or replace view port_stationtype as
SELECT
d.dict_value AS No,
d.dict_label AS Name,
'' AS Idx,
'' AS OrgNo
FROM
js_sys_dict_data d
WHERE
( d.dict_type = 'sys_role_type' );
Java
1
https://gitee.com/thinkgem/jeesite4-jflow.git
git@gitee.com:thinkgem/jeesite4-jflow.git
thinkgem
jeesite4-jflow
JeeSite4 JFlow
v4.1_dev

Search