156 lines
5.0 KiB
SQL
156 lines
5.0 KiB
SQL
create database if not exists FPDAM2023 character set utf8mb4 collate utf8mb4_general_ci;
|
|
use FPDAM2023;
|
|
|
|
delimiter ;^
|
|
|
|
drop function if exists enrollments_add_validate;^
|
|
create function enrollments_add_validate(
|
|
`$year` smallint,
|
|
`$type` varchar(32),
|
|
$partner char(10),
|
|
$course_code char(7),
|
|
$course_name varchar(256),
|
|
$center_code integer,
|
|
$center_name varchar(256),
|
|
$score tinyint
|
|
) returns integer begin
|
|
return (
|
|
((case
|
|
when `$year` is null then 1 << 0
|
|
when `$year` < 1985 then 1 << 1
|
|
when `$year` > year(now()) then 1 << 1
|
|
else 0 end) << 0) |
|
|
((case
|
|
when `$type` is null then 1 << 0
|
|
when `$type` = '' then 1 << 1
|
|
else 0 end) << 2) |
|
|
((case
|
|
when $partner is null then 1 << 0
|
|
when $partner = '' then 1 << 1
|
|
else 0 end) << 4) |
|
|
((case
|
|
when $course_code is null then 1 << 0
|
|
when $course_code = '' then 1 << 1
|
|
else 0 end) << 6) |
|
|
((case
|
|
when $course_name is null then 1 << 0
|
|
when $course_name = '' then 1 << 1
|
|
else 0 end) << 8) |
|
|
((case
|
|
when $center_code is null then 1 << 0
|
|
when $center_code < 0 then 1 << 1
|
|
else 0 end) << 10) |
|
|
((case
|
|
when $center_name is null then 1 << 0
|
|
when $center_name = '' then 1 << 1
|
|
else 0 end) << 12) |
|
|
((case
|
|
when $score is null then 1 << 0
|
|
when $score < 0 then 1 << 1
|
|
when $score > 55 then 1 << 2
|
|
else 0 end) << 14) |
|
|
0);
|
|
end;^
|
|
|
|
drop procedure if exists enrollments_add;^
|
|
create procedure enrollments_add(
|
|
in `$session` text,
|
|
in `$year` smallint,
|
|
in `$type` varchar(32),
|
|
in $partner char(10),
|
|
in $course_code char(7),
|
|
in $course_name varchar(256),
|
|
in $center_code integer,
|
|
in $center_name varchar(256),
|
|
in $score tinyint,
|
|
out $error varchar(512),
|
|
out $id integer
|
|
) begin
|
|
|
|
declare `$database` varchar(64) default AnP.settings_get('database_fpdam2023', false);
|
|
declare `$procedure` varchar(64) default 'enrollments_add';
|
|
declare $procedure_id integer;
|
|
declare $parameters text default json_set('{}',
|
|
'$.year', `$year`,
|
|
'$.type', `$type`,
|
|
'$.partner', $partner,
|
|
'$.course_code', $course_code,
|
|
'$.course_name', $course_name,
|
|
'$.center_code', $center_code,
|
|
'$.center_name', $center_name,
|
|
'$.score', $score,
|
|
'$.id', 0
|
|
);
|
|
declare $session_id integer;
|
|
declare $ip_id integer;
|
|
declare $user_agent_id integer;
|
|
declare exit handler for sqlexception begin
|
|
get diagnostics condition 1
|
|
@_status = returned_sqlstate,
|
|
@_code = mysql_errno,
|
|
@message = message_text;
|
|
rollback;
|
|
set $error := AnP.errors_set($error, 1 << 0, 0, 0);
|
|
call AnP.exceptions_set($session_id, `$database`, `$procedure`, $ip_id, $user_agent_id, $error, $parameters, @message, @_status, @_code);
|
|
end;
|
|
|
|
start transaction;
|
|
|
|
set $id := 0;
|
|
if ifnull(`$session`, '') = '' then
|
|
set $session_id := AnP.sessions_get_machine();
|
|
else
|
|
call sessions_validate_full(`$session`, '["administrator"]', $error, $session_id, $ip_id, $user_agent_id);
|
|
end if;
|
|
set $error := AnP.errors_set($error, enrollments_add_validate(
|
|
`$year`, `$type`, $partner, $course_code, $course_name, $center_code, $center_name, $score
|
|
), 30, 0);
|
|
|
|
if ifnull($error, '') = '' then begin
|
|
|
|
declare $type_id integer default (select id from EnrollmentsTypes where date_out is null && name = `$type` limit 1);
|
|
declare $partner_id integer default (select id from Partners where date_out is null && code = $partner limit 1);
|
|
declare $course_id integer default (select id from Courses where date_out is null && code = $course_code limit 1);
|
|
declare $center_id integer default (select id from Centers where date_out is null && code = $center_code limit 1);
|
|
|
|
call AnP.procedures_get(`$database`, `$procedure`, $procedure_id);
|
|
|
|
if $type_id is null then begin
|
|
insert into EnrollmentsTypes(`session`, `procedure`, name) values
|
|
($session_id, $procedure_id, `$type`);
|
|
set $type_id := last_insert_id();
|
|
end;end if;
|
|
|
|
if $partner_id is null then begin
|
|
insert into Partners(`session`, `procedure`, code) values
|
|
($session_id, $procedure_id, $partner);
|
|
set $partner_id := last_insert_id();
|
|
end;end if;
|
|
|
|
if $course_id is null then begin
|
|
insert into Courses(`session`, `procedure`, code, name) values
|
|
($session_id, $procedure_id, $course_code, $course_name);
|
|
set $course_id := last_insert_id();
|
|
end;end if;
|
|
|
|
if $center_id is null then begin
|
|
insert into Centers(`session`, `procedure`, code, name) values
|
|
($session_id, $procedure_id, $center_code, $center_name);
|
|
set $center_id := last_insert_id();
|
|
end;end if;
|
|
|
|
insert into Enrollments(`session`, `procedure`, `year`, `type`, partner, course, center, score) values
|
|
($session_id, $procedure_id, `$year`, $type_id, $partner_id, $course_id, $center_id, $score);
|
|
set $id := last_insert_id();
|
|
|
|
set $parameters := json_set($parameters, '$.id', $id);
|
|
|
|
end;end if;
|
|
|
|
commit;
|
|
|
|
call AnP.logs_set($session_id, `$database`, `$procedure`, $ip_id, $user_agent_id, $error, $parameters, 1 << 1);
|
|
|
|
end;^
|
|
|
|
delimiter ; |