需求:人才技能大类和小类编辑,增减。然后需要反映到技能自评的页面
沟通结果:
任务:人才技能大类和小类编辑,增减。然后需要反映到技能自评的页面,
涉及到两个应用,一个是数智化平台,进行编辑;
一个是人才自评,展示自评打分页面;
在原有基础上创建新页面开发,优先实现功能
技术链接:
https://www.bejson.com/doc/layui/demo/rate.html需要导入的插件/文件
过程:
技能表JAS_EMP_SKILL_NEW新加字段is_show(是否显示),默认全部显示
alter table JAS_EMP_SKILL_NEW
add IS_SHOW number default 1
/
comment on column JAS_EMP_SKILL_NEW.IS_SHOW is '是否显示(1显示 0 不显示 默认显示)'
/
原始页面的技能评分是静态的,无法满足当前需求,所以要做成动态的
现在创建一个新的页面进行样式展示
值得注意的点,需要导入对应的layui文件(css和js文件)
html代码中的对应css/js文件也要替换掉
呈现效果:
代码:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Layui-BeJSON.com</title>
<meta name="renderer" content="webkit">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="#WORKSPACE_FILES#static/layui-v2.6.13/layui/css/layui.css" media="all">
<!-- 注意:如果你直接复制所有代码到本地,上述css路径需要改成你本地的 -->
</head>
<body>
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>基础效果</legend>
</fieldset>
<div id="test1"></div>
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>显示文字</legend>
</fieldset>
<div id="test2"></div>
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>半星效果</legend>
</fieldset>
<div id="test3"></div>
<div><div id="test4"></div></div>
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>自定义内容</legend>
</fieldset>
<div id="test5"></div>
<div><div id="test6"></div></div>
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>自定义长度</legend>
</fieldset>
<div id="test7"></div>
<div><div id="test8"></div></div>
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>只读</legend>
</fieldset>
<div id="test9"></div>
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>自定义主题色</legend>
</fieldset>
<ul>
<li><div id="test10"></div></li>
<li><div id="test11"></div></li>
<li><div id="test12"></div></li>
<li><div id="test13"></div></li>
<li><div id="test14"></div></li>
</ul>
<!-- <script src="../layui/dist/layui.js" charset="utf-8"></script> -->
<script src="#WORKSPACE_FILES#static/layui-v2.6.13/layui/layui.js" charset="utf-8"></script>
<!-- 注意:如果你直接复制所有代码到本地,上述 JS 路径需要改成你本地的 -->
<script>
layui.use(['rate'], function(){
var rate = layui.rate;
//基础效果
rate.render({
elem: '#test1'
})
//显示文字
rate.render({
elem: '#test2'
,value: 2 //初始值
,text: true //开启文本
});
//半星效果
rate.render({
elem: '#test3'
,value: 2.5 //初始值
,half: true //开启半星
})
rate.render({
elem: '#test4'
,value: 3.5
,half: true
,text: true
})
//自定义文本
rate.render({
elem: '#test5'
,value: 3
,text: true
,setText: function(value){ //自定义文本的回调
var arrs = {
'1': '极差'
,'2': '差'
,'3': '中等'
,'4': '好'
,'5': '极好'
};
this.span.text(arrs[value] || ( value + "星"));
}
})
rate.render({
elem: '#test6'
,value: 1.5
,half: true
,text: true
,setText: function(value){
this.span.text(value);
}
})
//自定义长度
rate.render({
elem: '#test7'
,length: 3
});
rate.render({
elem: '#test8'
,length: 10
,value: 8 //初始值
});
//只读
rate.render({
elem: '#test9'
,value: 4
,readonly: true
});
//主题色
rate.render({
elem: '#test10'
,value: 3
,theme: '#FF8000' //自定义主题色
});
rate.render({
elem: '#test11'
,value: 3
,theme: '#009688'
});
rate.render({
elem: '#test12'
,value: 2.5
,half: true
,theme: '#1E9FFF'
})
rate.render({
elem: '#test13'
,value: 2.5
,half: true
,theme: '#2F4056'
});
rate.render({
elem: '#test14'
,value: 2.5
,half: true
,theme: '#FE0000'
})
});
</script>
</body>
</html>
展现后还要结合数据进行动态展示,
方案:
-
将查询的值赋值到apex页项
-
用js解析成json,循环渲染评分
-- 大类
select json_arrayagg(
json_object(
key 'id' value skill_id,
key 'skill_parent_id' value skill_parent_id,
key 'skill_name' value skill_name,
key 'is_show' value is_show
) returning clob
)
from JAS_EMP_SKILL_NEW
where SKILL_PARENT_ID = 0;
-- 技能
select json_arrayagg(
json_object(
key 'id' value skill_id,
key 'skill_parent_id' value skill_parent_id,
key 'skill_parent_name' value skill_parent_name,
key 'skill_name' value skill_name,
key 'is_show' value is_show
) returning clob
)
from (
select s.*, p.SKILL_NAME skill_parent_name
from (
select *
from JAS_EMP_SKILL_NEW
start with SKILL_PARENT_ID = 0
connect by prior SKILL_ID = SKILL_PARENT_ID) s
left join JAS_EMP_SKILL_NEW p on s.SKILL_PARENT_ID = p.SKILL_ID) where SKILL_PARENT_ID>0;
初版js取值渲染(有缺陷)
var datas = JSON.parse(JSON.parse(JSON.stringify($v('P14_SKILLS'))));
console.log(datas);
console.log(datas[0]);
console.log(datas[0].skill_name);
for (var i = 0; i < datas.length; i++) {
console.log(datas[i]);
console.log(datas[i].score);
var score = datas[i].score
var div = `<div id="ja_skill_${i + 1}"></div>`;
$('#skill_div').append(div);
layui.use(['rate'], function () {
var rate = layui.rate;
rate.render({
elem: '#ja_skill_' + i + 1
, value: score //初始值
})
})
}
有缺陷,elem: '#ja_skill_' + i + 1 是先取值拼接,拼接后是字符串,举个例子,原本取值3(for循环从0开始),拼接后不是ja_skill_3,而是ja_skill_21。
P14_SKILL_CATEGORY设置值
select json_arrayagg(
json_object(
key 'id' value d.skill_id,
key 'skill_name' value skill.SKILL_NAME,
key 'score' value d.score
) returning clob
)
from JAS_EMP_SKILL_NEW skill
left join(
select s.skill_id, r.EMP_CODE, r.score
from JAS_EMP_SKILL_NEW s
left join JAS_EMP_SKILL_SCORE_NEW r on s.SKILL_ID = r.SKILL_ID
where r.EMP_CODE = :USER_JOB_NUMBER) d on skill.SKILL_ID = d.SKILL_ID
where SKILL_PARENT_ID > 0
and skill.SKILL_PARENT_ID in (select * from table ( SPLITSTR(:P14_SKILL_CATEGORY,':') ));
后来发现取值正常,渲染失败,尝试过延迟加载,但是渲染还是有问题,后来找到问题,是生命周期不一致造成的;
改进后js取值渲染:
var datas = JSON.parse(JSON.parse(JSON.stringify($v('P14_SKILLS'))));
console.log(datas);
console.log(datas[0]);
console.log(datas[0].skill_name);
layui.use(['rate'], function () {
for (var i = 0; i < datas.length; i++) {
// console.log(datas[i]);
// console.log(datas[i].score);
var score = datas[i].score
const div = `<div id="ja_skill_${i + 1}"></div>`;
$('#skill_div').append(div);
console.log(`#ja_skill_${i + 1}`);
console.log(i)
var rate = layui.rate;
rate.render({
elem: `#ja_skill_${i + 1}`
, value: score //初始值
})
}
})
效果
接下来是 将技能名和打分绑定
$('.ja_skill_class').remove();
if (!$v('P14_SKILLS')) {
return false;
} else {
var datas = JSON.parse(JSON.parse(JSON.stringify($v('P14_SKILLS'))));
console.log(datas);
console.log(datas[0].skill_name);
layui.use(['rate'], function () {
for (var i = 0; i < datas.length; i++) {
var score = datas[i].score
const div = `<fieldset class="ja_skill_class layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>${datas[i].skill_name}</legend>
</fieldset>
<div class="ja_skill_class" id="ja_skill_${i + 1}"></div>`;
$('#skill_div').append(div);
console.log(`#ja_skill_${i + 1}`);
console.log(datas[i].id,datas[i].skill_name, datas[i].score)
var rate = layui.rate;
rate.render({
elem: `#ja_skill_${i + 1}`
, value: score //初始值
})
}
})
}
要让技能id和技能评分建立关系,使用MAP的方式
var map=new Map();
$('.ja_skill_class').remove();
if (!$v('P14_SKILLS')) {
return false;
} else {
var datas = JSON.parse(JSON.parse(JSON.stringify($v('P14_SKILLS'))));
console.log(datas);
console.log(datas[0].skill_name);
layui.use(['rate'], function () {
for (var i = 0; i < datas.length; i++) {
var score = datas[i].score
const div = `<fieldset class="ja_skill_class layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>${datas[i].skill_name}</legend>
</fieldset>
<div class="ja_skill_class" id="ja_skill_${i + 1}"></div>`;
$('#skill_div').append(div);
console.log(`#ja_skill_${i + 1}`);
console.log(datas[i].id, datas[i].skill_name, datas[i].score)
var ids = datas[i].id
var scores = datas[i].score
$s('P14_SKILL_IDS', ids);
$s('P14_SKILL_SCORES', scores);
var rate = layui.rate;
rate.render({
elem: `#ja_skill_${i + 1}`
, value: score, //初始值
choose: function (value) {
alert(value)
}
})
// var skill_ids = [];
// var skill_scores = [];
// for (var i = 0; i < datas.length; i++) {
// skill_scores[i] = datas[i].score;
// skill_ids[i] = datas[i].id;
// }
// $s('P14_SKILL_IDS', skill_ids);
// $s('P14_SKILL_SCORES',skill_scores);
}
})
}
scoremap定义放到全局变量声明
动态获取id
var idmap = new Map();
var map = new Map();
$('.ja_skill_class').remove();
if (!$v('P14_SKILLS')) {
return false;
} else {
var datas = JSON.parse(JSON.parse(JSON.stringify($v('P14_SKILLS'))));
console.log(datas);
console.log(datas[0].skill_name);
layui.use(['rate'], function () {
for (var i = 0; i < datas.length; i++) {
var score = datas[i].score
const div = `<fieldset class="ja_skill_class layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>${datas[i].skill_name}</legend>
</fieldset>
<div class="ja_skill_class" skill_id="${datas[i].id}" id="ja_skill_${i + 1}"></div>`;
$('#skill_div').append(div);
// alert('页面上获取id:' + datas[i].id)
console.log(`#ja_skill_${i + 1}`);
console.log(datas[i].id, datas[i].skill_name, datas[i].score)
var ids = datas[i].id
var scores = datas[i].score
// $s('P14_SKILL_IDS', ids);
// $s('P14_SKILL_SCORES', scores);
// map.set(datas[i].id, datas[i].score); //存入评分
// scoremap.forEach((key, value) => {
// console.log('map: ' +'id '+ key, '评分'+value)
// })
var rate = layui.rate;
rate.render({
elem: `#ja_skill_${i + 1}`
, value: score, //初始值
choose: function (values) {
console.log(this);
console.log(this.elem[0]);
console.log($(this.elem[0]).attr('id'));
console.log($(this.elem[0]).attr('skill_id'));
// scoremap.set(ids, values); //存入评分,动态获取
}
})
// // alert('页面上获取id:' + ids);
// idmap.set('P14_SKILL_IDS', ids); //存入id
// // console.log($v('P14_SKILL_IDS')) //展示id
// idmap.forEach((key, value) => {
// console.log('idmap' + key, value)
// })
}
})
}
id和评分动态绑定
var idmap = new Map();
var map = new Map();
$('.ja_skill_class').remove();
if (!$v('P14_SKILLS')) {
return false;
} else {
var datas = JSON.parse(JSON.parse(JSON.stringify($v('P14_SKILLS'))));
console.log(datas);
console.log(datas[0].skill_name);
layui.use(['rate'], function () {
for (var i = 0; i < datas.length; i++) {
var score = datas[i].score
const div = `<fieldset class="ja_skill_class layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>${datas[i].skill_name}</legend>
</fieldset>
<div class="ja_skill_class" skill_id="${datas[i].id}" id="ja_skill_${i + 1}"></div>`;
$('#skill_div').append(div);
// alert('页面上获取id:' + datas[i].id)
console.log(`#ja_skill_${i + 1}`);
console.log(datas[i].id, datas[i].skill_name, datas[i].score)
var ids = datas[i].id
var scores = datas[i].score
// $s('P14_SKILL_IDS', ids);
// $s('P14_SKILL_SCORES', scores);
// map.set(datas[i].id, datas[i].score); //存入评分
// scoremap.forEach((key, value) => {
// console.log('map: ' +'id '+ key, '评分'+value)
// })
var rate = layui.rate;
rate.render({
elem: `#ja_skill_${i + 1}`
, value: score, //初始值
choose: function (values) {
// console.log(this);
// console.log(this.elem[0]);
// console.log($(this.elem[0]).attr('id'));
console.log($(this.elem[0]).attr('skill_id'));
scoremap.set($(this.elem[0]).attr('skill_id'), values); //存入评分,动态获取
scoremap.forEach((key, value) => { //动态展示
console.log('scoremap' + key, value)
})
}
})
// // alert('页面上获取id:' + ids);
// idmap.set('P14_SKILL_IDS', ids); //存入id
// // console.log($v('P14_SKILL_IDS')) //展示id
// idmap.forEach((key, value) => {
// console.log('idmap' + key, value)
// })
}
})
}
注意:key和value的顺序不能变,不然会出现一个id有多个对应的值!
js中获取的map如何存到数据库
// var total = [];
$('.ja_skill_class').remove();
if (!$v('P14_SKILLS')) {
return false;
} else {
var datas = JSON.parse(JSON.parse(JSON.stringify($v('P14_SKILLS'))));
console.log(datas);
console.log(datas[0].skill_name);
var i = 0;
layui.use(['rate'], function () {
for (var i = 0; i < datas.length; i++) {
var score = datas[i].score
const div = `<fieldset class="ja_skill_class layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>${datas[i].skill_name}</legend>
</fieldset>
<div class="ja_skill_class" skill_id="${datas[i].id}" id="ja_skill_${i + 1}"></div>`;
$('#skill_div').append(div);
// alert('页面上获取id:' + datas[i].id)
// console.log(`#ja_skill_${i + 1}`);
// console.log(datas[i].id, datas[i].skill_name, datas[i].score)
var ids = datas[i].id
var scores = datas[i].score
var rate = layui.rate;
rate.render({
elem: `#ja_skill_${i + 1}`
, value: score, //初始值
choose: function (values) {
// console.log(this);
// console.log(this.elem[0]);
// console.log($(this.elem[0]).attr('id'));
// console.log($(this.elem[0]).attr('skill_id'));
scoremap.set($(this.elem[0]).attr('skill_id'), values, values); //存入评分,动态获取
// scoremap.forEach((key, value) => { //动态展示
// console.log('scoremap: ' + key, value)
// })
var obj = {
id: $(this.elem[0]).attr('skill_id'),
score: values,
sort_number: i
}
// 新增的
scoreArr.push(obj);
$s('P14_REQ', scoreArr);
i++;
}
})
}
})
}
在存储动态评分和id上也花费了一些心力,写了人才自评评分保存函数TALENT_SELF_ASSESSMENT_FUN
create function TALENT_SELF_ASSESSMENT_FUN(p_data in clob,p_job_number in varchar2)
return varchar2 as
pragma autonomous_transaction;
v_count number(20) ;
v_flag nvarchar2(256);
v_err_msg nvarchar2(2000);
--DATE: 2024/5/14 18:36
--CREATOR: wxx
--DESCRIBE:APP104 人才自评评分保存函数 P14
cursor c_job is select *
from json_table(p_data,
'$[*]'
COLUMNS (
SKILL_ID NUMBER PATH '$.ID',
SCORE NUMBER PATH '$.SCORE'
)) p;
begin
v_flag := 'error';
-- 循环数组
for c in c_job
loop
WRITE_LOG(GET_FN_NAME(), 'debug', c.SKILL_ID || c.SCORE , 1, 0);
-- 查询是否有该数据
select count(1)
into v_count
from JAS_EMP_SKILL_SCORE_NEW
where EMP_CODE = p_job_number
and SKILL_ID = c.SKILL_ID
and SCORE = c.SCORE;
-- 数据库没有,执行新增
if v_count = 0 then
insert into JAS_EMP_SKILL_SCORE_NEW(EMP_CODE, SKILL_ID,SCORE)
values (p_job_number, c.SKILL_ID, c.SCORE);
--数据库有该技能分数 ,执行更新
else
update JAS_EMP_SKILL_SCORE_NEW
set
SCORE = c.SCORE
where EMP_CODE = p_job_number
and SKILL_ID = c.SKILL_ID;
end if;
end loop;
-- commit;
v_flag := 'success';
commit;
return v_flag;
exception
when others then
v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
WRITE_LOG(GET_FN_NAME(),
'error',
v_err_msg || chr(13) || v_flag,
1,
0);
v_flag := '数据错误';
if instr(sqlerrm, 'ORA-01400') > 0 then
v_flag := '数据不能为空';
end if;
rollback;
return v_flag;
end;
/
之前有几次就回显几次数据,但是我们只需要取相同技能id中最新一条即可
效果对比:
注意参数的传递,参数p_data传过来是object,无法被解析,需要使用JSON.stringify转化成json
最后js代码:
// var total = [];
$('.ja_skill_class').remove();
if (!$v('P14_SKILLS')) {
return false;
} else {
var datas = JSON.parse(JSON.parse(JSON.stringify($v('P14_SKILLS'))));
console.log(datas);
console.log(datas[0].skill_name);
var i = 0;
layui.use(['rate'], function () {
for (var i = 0; i < datas.length; i++) {
var score = datas[i].score
const div = `<fieldset class="ja_skill_class layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>${datas[i].skill_name}</legend>
</fieldset>
<div class="ja_skill_class" skill_id="${datas[i].id}" id="ja_skill_${i + 1}"></div>`;
$('#skill_div').append(div);
// alert('页面上获取id:' + datas[i].id)
// console.log(`#ja_skill_${i + 1}`);
// console.log(datas[i].id, datas[i].skill_name, datas[i].score)
var ids = datas[i].id
var scores = datas[i].score
var rate = layui.rate;
rate.render({
elem: `#ja_skill_${i + 1}`
, value: score, //初始值
choose: function (values) {
// console.log(this);
// console.log(this.elem[0]);
// console.log($(this.elem[0]).attr('id'));
// console.log($(this.elem[0]).attr('skill_id'));
scoremap.set($(this.elem[0]).attr('skill_id'), values, values); //存入评分,动态获取
// scoremap.forEach((key, value) => { //动态展示
// console.log('scoremap: ' + key, value)
// })
var obj = {
id: $(this.elem[0]).attr('skill_id'),
score: values,
sort_number: i
}
// 新增的,只需要取相同技能id中最新一条即可
scoreArr.push(obj);
scoreArr.forEach(item => {
var found = result.find(r => r.id === item.id);
if (!found || found.sort_number < item.sort_number) {
var index = result.findIndex(r => r.id === item.id);
if (index !== -1) {
result[index] = item;
} else {
result.push(item);
}
}
});
//debugger;
//
console.log(JSON.stringify(result));
$s('P14_REQ', JSON.stringify(result));
//$s('P14_REQ', result);
i++;
// 原版的
// scoreArr.push(obj);
// $s('P14_REQ', scoreArr);
// i++;
}
})
}
})
}
函数改进,json_table在将json转化为行列的时候注意区分大小写,存储的网页数据是小写,对应的转换也是小写
最后代码:
create function TALENT_SELF_ASSESSMENT_FUN(p_data in clob,p_job_number in varchar2)
return varchar2 as
pragma autonomous_transaction;
v_count number(20) ;
v_flag nvarchar2(256);
v_err_msg nvarchar2(2000);
--DATE: 2024/5/14 18:36
--CREATOR: wxx
--DESCRIBE:APP104 人才自评评分保存函数 P14
cursor c_job is select *
from json_table(p_data,
'$[*]'
COLUMNS (
SKILL_ID NUMBER PATH '$.id',
SCORE NUMBER PATH '$.score'
)) p;
begin
v_flag := 'error';
-- 循环数组
for c in c_job
loop
-- 查询是否有该id的技能
select count(1)
into v_count
from JAS_EMP_SKILL_SCORE_NEW
where EMP_CODE = p_job_number
and SKILL_ID = c.SKILL_ID;
-- and SCORE = c.SCORE;
DBMS_OUTPUT.PUT_LINE(v_count);
-- 数据库没有,执行新增
if v_count = 0 then
insert into JAS_EMP_SKILL_SCORE_NEW(EMP_CODE, SKILL_ID,SCORE)
values (p_job_number, c.SKILL_ID, c.SCORE);
--数据库有该技能分数 ,执行更新
else
update JAS_EMP_SKILL_SCORE_NEW
set SCORE = c.SCORE
where EMP_CODE = p_job_number
and SKILL_ID = c.SKILL_ID;
end if;
end loop;
-- commit;
v_flag := 'success';
commit;
return v_flag;
exception
when others then
v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
WRITE_LOG(GET_FN_NAME(),
'error',
v_err_msg || chr(13) || v_flag,
1,
0);
v_flag := '数据错误';
if instr(sqlerrm, 'ORA-01400') > 0 then
v_flag := '数据不能为空';
end if;
rollback;
return v_flag;
end;
/
至此前端页面交互和后端数据存储转换完毕!