////////////////////////////////////////////////////////////////////////////
/////
///////////////////////////////////////////////////
// LAB.lab_tasks
CREATE TABLE LAB.scheduled_tasks
(
task_id INT NOT NULL DEFAULT SERIAL(1),
device_id INT NOT NULL,
sample_id VARCHAR(25) NOT NULL,
scheduling timestamp NOT NULL DEFAULT TIMESTAMP,
sequence INT NOT NULL DEFAULT 0,
sample_type VARCHAR(25) NOT NULL DEFAULT 'U',
patient_code VARCHAR(25) NULL,
patient_surname VARCHAR(25) NULL,
patient_sex VARCHAR(1) NOT NULL DEFAULT 'U',
patient_birthday timestamp NULL,
task_state INT NOT NULL DEFAULT 0,
created timestamp NOT NULL DEFAULT TIMESTAMP,
modification timestamp NOT NULL DEFAULT TIMESTAMP,
modifier VARCHAR(32) NOT NULL DEFAULT USER,
PRIMARY KEY(task_id),
FOREIGN KEY ref2device (device_id)
REFERENCES LAB.devices (device_id),
CONSTRAINT unique_key
UNIQUE (device_id, sample_id),
CONSTRAINT patient_sex IN ('M', 'F', 'U'),
CONSTRAINT sample_type IN ('U', 'SE', 'SF', 'UR', 'PL')
)
///////////////////////////////////////////////////
// LAB.lab_tests
CREATE TABLE LAB.scheduled_tests
(
test_id INT NOT NULL DEFAULT SERIAL(1),
task_id INT NOT NULL,
analit_device INT NULL,
reagent_device INT NULL,
test_type VARCHAR(25) NOT NULL DEFAULT 'SAMPLE',
priority VARCHAR(25) NOT NULL DEFAULT 'R',
dilution_factor FLOAT(5) NOT NULL DEFAULT 1.0,
completed timestamp NULL,
authorized timestamp NULL,
doctor VARCHAR(32) NULL,
test_state INT NOT NULL DEFAULT 0,
created timestamp NOT NULL DEFAULT TIMESTAMP,
modification timestamp NOT NULL DEFAULT TIMESTAMP,
modifier VARCHAR(32) NOT NULL DEFAULT USER,
PRIMARY KEY(test_id),
FOREIGN KEY ref2tasks (task_id)
REFERENCES LAB.scheduled_tasks (task_id),
FOREIGN KEY ref2analit4device (analit_device)
REFERENCES LAB.specify_analit4device (analit_device),
FOREIGN KEY ref2reagent4device (reagent_device)
REFERENCES LAB.specify_reagent4device (reagent_device),
CONSTRAINT priority IN ('U', 'R', 'S'),
CONSTRAINT test_type IN ('SAMPLE', 'CONTROL', 'U')
)
// test_status
///////////////////////////////////////////////////
// errors (5600 - 5700 eventlog)
5600 -
///////////////////////////////////////////////////
// CREATE DBPROC LAB.get_task
CREATE DBPROC LAB.get_task
( IN device_id INT
, IN sample_id VARCHAR(25)
, OUT task_id INT
, OUT task_state INT
)
AS
BEGIN
TRY
SELECT task_id, task_state
FROM LAB.scheduled_tasks
WHERE device_id = :device_id AND sample_id = :sample_id;
FETCH INTO :task_id, :task_state;
CATCH
IF $rc = 100 THEN STOP(5601, 'invalid argument: The task ''' || device_id
|| ' - ' || sample_id || ''' not exists')
ELSE STOP($rc, $errmsg);
END;
///////////////////////////////////////////////////
// CREATE DBPROC LAB.add_unplanned_task
CREATE DBPROC LAB.add_unplanned_task
( IN device_id INT
, IN sample_id VARCHAR(25)
, IN sample_type VARCHAR(25)
, OUT task_state INT
, OUT task_id INT
)
AS
VAR log_msg VARCHAR (255);
SET log_msg = '';
SET task_state = 300100;
BEGIN
TRY
SELECT task_id, task_state
FROM LAB.scheduled_tasks
WHERE device_id = :device_id AND sample_id = :sample_id;
FETCH INTO :task_id, :task_state;
return;
CATCH
IF $rc != 100 THEN STOP($rc, $errmsg);
IF sample_type is null THEN BEGIN
SET sample_type = 'U';
END;
TRY
INSERT LAB.scheduled_tasks
( device_id
, sample_id
, sample_type
, task_state
, sequence
)
values
( :device_id
, :sample_id
, :sample_type
, :task_state
, 0
);
SET task_id = LAB.scheduled_tasks.CURRVAL;
SET log_msg = 'The task for device_id ' || device_id || ' - ' || sample_id
|| ' has been added successfully, with task_id = ' || task_id;
call LAB.add_event('info', 'add_task', 'LAB.add_task', :log_msg);
CATCH
STOP($rc, $errmsg);
END;
///////////////////////////////////////////////////
// CREATE DBPROC LAB.schedule_task
CREATE DBPROC LAB.schedule_task
( IN device_id INT
, IN sample_id VARCHAR(25)
, INOUT scheduling timestamp
, INOUT sequence INT
, INOUT sample_type VARCHAR(25)
, INOUT patient_code VARCHAR(25)
, INOUT patient_surname VARCHAR(25)
, INOUT patient_sex VARCHAR(1)
, INOUT patient_birthday timestamp
, OUT task_state INT
, OUT task_id INT
, OUT flag INT
)
AS
VAR log_msg VARCHAR (255);
SET log_msg = '';
SET task_state = 100000;
SET flag = 0;
BEGIN
TRY
SELECT task_id, task_state, scheduling, sequence, sample_type,
patient_code, patient_surname, patient_sex, patient_birthday
FROM LAB.scheduled_tasks
WHERE device_id = :device_id AND sample_id = :sample_id;
FETCH INTO :task_id, :task_state, :scheduling, :sequence, :sample_type,
:patient_code, :patient_surname, :patient_sex, :patient_birthday;
SET flag = 1;
return;
CATCH
IF $rc != 100 THEN STOP($rc, $errmsg);
TRY
INSERT LAB.scheduled_tasks
( device_id
, sample_id
, task_state
, scheduling
, sequence
, sample_type
, patient_code
, patient_surname
, patient_sex
, patient_birthday
)
values
( :device_id
, :sample_id
, :task_state
, :scheduling
, :sequence
, :sample_type
, :patient_code
, :patient_surname
, :patient_sex
, :patient_birthday
);
SET task_id = LAB.scheduled_tasks.CURRVAL;
SET log_msg = 'The task for device_id ' || device_id || ' - ' || sample_id
|| ' has been scheduled successfully, with task_id = ' || task_id;
call LAB.add_event('info', 'add_task', 'LAB.add_task', :log_msg);
CATCH
STOP($rc, $errmsg);
END;
///////////////////////////////////////////////////
// CREATE DBPROC LAB.get_planned_test
CREATE DBPROC LAB.get_planned_test
( IN task_id INT
, IN test_code VARCHAR(25)
, OUT test_id INT
)
AS
BEGIN
TRY
select b.test_id
from LAB.scheduled_tasks a,
LAB.scheduled_tests b,
LAB.specify_analit4device c
where a.task_id = b.task_id
and b.analit_device = c.analit_device
and c.status = 0
and b.test_state = 100000
and a.task_id = :task_id
and c.test_code = :test_code;
FETCH INTO :test_id;
CATCH
IF $rc = 100 THEN STOP(5602, 'invalid argument: The test ''' || task_id ||
' - ' || test_code || ''' not exists')
ELSE STOP($rc, $errmsg);
END;
///////////////////////////////////////////////////
// CREATE DBPROC LAB.exist_test
CREATE DBPROC LAB.exist_test
( IN task_id INT
, IN test_code VARCHAR(25)
)
AS
VAR test_id INT;
SET test_id = 0;
BEGIN
TRY
select b.test_id
from LAB.scheduled_tasks a,
LAB.scheduled_tests b,
LAB.specify_analit4device c
where a.task_id = b.task_id
and b.analit_device = c.analit_device
and c.status = 0
and a.task_id = :task_id
and c.test_code = :test_code;
FETCH INTO :test_id;
CATCH
IF $rc = 100 THEN STOP(5602, 'invalid argument: The test ''' || task_id ||
' - ' || test_code || ''' not exists')
ELSE STOP($rc, $errmsg);
END;
///////////////////////////////////////////////////
// CREATE DBPROC LAB.complete_test
CREATE DBPROC LAB.complete_test
( IN test_id INT
)
AS
VAR log_msg VARCHAR (255);
test_state INT;
SET test_state = 300000;
SET log_msg = '';
BEGIN
TRY
UPDATE LAB.scheduled_tests
SET test_state = :test_state, completed = timestamp
WHERE test_id = :test_id;
SET log_msg = 'The test ' || test_id || 'has been completed successfully,
with status = ' || test_state;
call LAB.add_event('info', 'complete_test', 'LAB.complete_test',
:log_msg);
CATCH
STOP($rc, $errmsg);
END;
///////////////////////////////////////////////////
// CREATE DBPROC LAB.add_unplanned_test
CREATE DBPROC LAB.add_unplanned_test
( IN task_id INT
, IN test_code VARCHAR(25)
, IN test_type VARCHAR(25)
, IN priority VARCHAR(25)
, IN dilution FLOAT(5)
, OUT test_state INT
, OUT test_id INT
)
AS
VAR log_msg VARCHAR (255);
device_id INT;
analit_id INT;
analit_device INT;
reagent_device INT;
SET log_msg = '';
SET test_state = 300100;
SET analit_id = NULL;
SET analit_device = NULL;
SET reagent_device = NULL;
BEGIN
/*
task
*/
TRY
SELECT device_id
FROM LAB.scheduled_tasks
WHERE task_id = :task_id;
FETCH INTO :device_id;
CATCH BEGIN
IF $rc = 100 THEN STOP(5501, 'invalid argument: The task ''' || task_id ||
''' not exists')
ELSE STOP($rc, $errmsg);
END;
/*
analit code
*/
TRY
CALL LAB.get_analit4testcode(:device_id, :test_code, :analit_id,
:analit_device);
CATCH BEGIN
IF $rc != 5110 THEN BEGIN
STOP($rc, $errmsg);
END;
END;
/*
reagent code
*/
TRY
CALL LAB.get_reagent4device(:device_id, :analit_id, :reagent_device);
CATCH BEGIN
IF $rc != 5710 THEN BEGIN
STOP($rc, $errmsg);
END;
END;
/*
*/
if test_type is null then begin
SET test_type = 'U';
end;
if priority is null then begin
SET priority = 'R';
end;
if dilution is null then begin
SET dilution = 1.0;
end;
TRY
INSERT LAB.scheduled_tests
( task_id
, analit_device
, reagent_device
, test_type
, priority
, dilution_factor
, test_state
, completed
)
values
( :task_id
, :analit_device
, :reagent_device
, :test_type
, :priority
, :dilution
, :test_state
, timestamp
);
SET test_id = LAB.scheduled_tests.CURRVAL;
SET log_msg = 'The test for task ' || task_id || ' - ' || test_code || '
has been added successfully, with test_id = ' || test_id;
call LAB.add_event('info', 'add_test', 'LAB.add_unplanned_test',
:log_msg);
CATCH
STOP($rc, $errmsg);
END;
///////////////////////////////////////////////////
// CREATE DBPROC LAB.add_repeated_test
CREATE DBPROC LAB.add_repeated_test
( IN task_id INT
, IN test_code VARCHAR(25)
, IN test_type VARCHAR(25)
, IN priority VARCHAR(25)
, IN dilution FLOAT(5)
, OUT test_state INT
, OUT test_id INT
)
AS
VAR log_msg VARCHAR (255);
device_id INT;
analit_id INT;
analit_device INT;
reagent_device INT;
SET log_msg = '';
SET test_state = 200100;
SET analit_id = NULL;
SET analit_device = NULL;
SET reagent_device = NULL;
BEGIN
/*
task
*/
TRY
SELECT device_id
FROM LAB.scheduled_tasks
WHERE task_id = :task_id;
FETCH INTO :device_id;
CATCH BEGIN
IF $rc = 100 THEN STOP(5501, 'invalid argument: The task ''' || task_id ||
''' not exists')
ELSE STOP($rc, $errmsg);
END;
/*
analit code
*/
TRY
CALL LAB.get_analit4testcode(:device_id, :test_code, :analit_id,
:analit_device);
CATCH BEGIN
IF $rc != 5110 THEN BEGIN
STOP($rc, $errmsg);
END;
END;
/*
reagent code
*/
TRY
CALL LAB.get_reagent4device(:device_id, :analit_id, :reagent_device);
CATCH BEGIN
IF $rc != 5710 THEN BEGIN
STOP($rc, $errmsg);
END;
END;
/*
*/
if test_type is null then begin
SET test_type = 'U';
end;
if priority is null then begin
SET priority = 'R';
end;
if dilution is null then begin
SET dilution = 1.0;
end;
TRY
INSERT LAB.scheduled_tests
( task_id
, analit_device
, reagent_device
, test_type
, priority
, dilution_factor
, test_state
, completed
)
values
( :task_id
, :analit_device
, :reagent_device
, :test_type
, :priority
, :dilution
, :test_state
, timestamp
);
SET test_id = LAB.scheduled_tests.CURRVAL;
SET log_msg = 'The test for task ' || task_id || ' - ' || test_code || '
has been added successfully, with test_id = ' || test_id;
call LAB.add_event('info', 'add_test', 'LAB.add_repeated_test', :log_msg);
CATCH
STOP($rc, $errmsg);
END;
///////////////////////////////////////////////////
// CREATE DBPROC LAB.schedule_test
CREATE DBPROC LAB.schedule_test
( IN task_id INT
, IN analit_code VARCHAR(25)
, INOUT test_type VARCHAR(25)
, INOUT priority VARCHAR(25)
, INOUT dilution FLOAT(5)
, OUT test_state INT
, OUT test_id INT
, OUT flag INT
)
AS
VAR log_msg VARCHAR (255);
device_id INT;
analit_device INT;
test_code VARCHAR (25);
SET log_msg = '';
SET test_state = 100000;
SET analit_device = -1;
SET flag = 0;
BEGIN
/*
task
*/
TRY
SELECT device_id
FROM LAB.scheduled_tasks
WHERE task_id = :task_id;
FETCH INTO :device_id;
CATCH BEGIN
IF $rc = 100 THEN STOP(5501, 'invalid argument: The task ''' || task_id ||
''' not exists')
ELSE STOP($rc, $errmsg);
END;
/*
test_code
*/
TRY
SELECT d.analit_device, d.test_code
FROM
LAB.analits a,
LAB.test4devicetype b,
LAB.specify_analit4devicetype c,
LAB.specify_analit4device d
WHERE
a.analit_id = c.analit_id
AND a.status = 0
AND c.status = 0
AND d.status = 0
AND b.device_type = c.device_type
AND b.test_name = c.test_name
AND c.analit_devicetype = d.analit_devicetype
AND d.device_id = :device_id
AND a.analit_code = :analit_code;
FETCH INTO :analit_device, :test_code;
CATCH BEGIN
IF $rc = 100 THEN STOP(5501, 'invalid argument: The analit_code ''' ||
analit_code || ''' not register on device')
ELSE STOP($rc, $errmsg);
END;
/*
test
*/
TRY
SELECT test_id, test_type, priority, dilution_factor, test_state
FROM LAB.scheduled_tests
WHERE task_id = :task_id
AND analit_device = :analit_device AND test_state = 100000;
FETCH INTO :test_id, :test_type, :priority, :dilution, :test_state;
SET flag = 1;
return;
CATCH
IF $rc != 100 THEN STOP($rc, $errmsg);
TRY
INSERT LAB.scheduled_tests
( task_id
, analit_device
, test_type
, priority
, dilution_factor
, test_state
)
values
( :task_id
, :analit_device
, :test_type
, :priority
, :dilution
, :test_state
);
SET test_id = LAB.scheduled_tests.CURRVAL;
SET log_msg = 'The test for task ' || task_id || ' - ' || test_code || '
has been scheduled successfully, with test_id = ' || test_id;
call LAB.add_event('info', 'add_test', 'LAB.add_test', :log_msg);
CATCH
STOP($rc, $errmsg);
END;
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
