//
CREATE DBPROC LAB.auto_authorize
( IN test_id INT
)
AS
VAR log_msg VARCHAR (255);
result_id INT;
result FLOAT(5);
normal_range_flag VARCHAR (10);
units VARCHAR (10);
SET log_msg = '';
BEGIN
TRY
select result_id, result, normal_range_flag, units
from LAB.raw_results
where test_id = :test_id;
FETCH INTO :result_id, :result, :normal_range_flag, :units;
CATCH
IF $rc = 100 THEN STOP(5602, 'invalid argument: The test ''' || test_id ||
''' not exists')
ELSE STOP($rc, $errmsg);
TRY
TRY
update LAB.raw_results
set authorized_result = :result,
authorized_units = :units,
authorized_range_flag = :normal_range_flag,
authorized_time = timestamp
where result_id = :result_id;
CATCH
STOP($rc, $errmsg);
SET log_msg = 'The test ' || test_id || 'has been auto authorized
successfully';
call LAB.add_event('info', 'add_task', 'LAB.add_task', :log_msg);
CATCH
STOP($rc, $errmsg);
END;
///////////////////////////////////////////////////
// TABLE LAB.raw_results
CREATE TABLE LAB.raw_results
(
result_id INT NOT NULL DEFAULT SERIAL(1),
device_id INT NOT NULL,
test_type VARCHAR(35) NOT NULL,
order_group VARCHAR(25) NULL,
result_group VARCHAR(25) NULL,
sample_id VARCHAR(25) NULL,
test_code VARCHAR(25) NULL,
sample_type VARCHAR(10) NULL,
priority VARCHAR(10) NULL,
result FLOAT(5) NULL,
normal_range_flag VARCHAR(10) NULL,
units VARCHAR(10) NULL,
result_status VARCHAR(10) NULL,
sequence_number VARCHAR(10) NULL,
carrier VARCHAR(10) NULL,
position VARCHAR(10) NULL,
test_started timestamp NULL,
test_completed timestamp NULL,
dilution_factor FLOAT(5) NULL,
container_type VARCHAR(10) NULL,
comment VARCHAR(255) NULL,
histogram INT NOT NULL DEFAULT 0,
operator VARCHAR(32) NULL,
reagent_serial VARCHAR(10) NULL,
reagent_lot VARCHAR(10) NULL,
receive_time timestamp NOT NULL DEFAULT TIMESTAMP,
authorized_result FLOAT(5) NULL,
authorized_units VARCHAR(10) NULL,
authorized_range_flag VARCHAR(10) NULL,
authorized_time timestamp NULL,
task_id INT NOT NULL,
test_id INT NOT NULL,
PRIMARY KEY(result_id),
FOREIGN KEY ref2device (device_id)
REFERENCES LAB.devices (device_id),
FOREIGN KEY ref2task (task_id)
REFERENCES LAB.scheduled_tasks (task_id),
FOREIGN KEY ref2test (test_id)
REFERENCES LAB.scheduled_tests (test_id)
)
// status
// 0 - unauthorized
// 1 - authorized
///////////////////////////////////////////////////
// TABLE LAB.raw_histograms
CREATE TABLE LAB.raw_histograms
(
result_id INT NOT NULL,
type VARCHAR(5) NOT NULL DEFAULT 'SVG',
histogram LONG NOT NULL,
CONSTRAINT type IN ('SVG','RAW'),
FOREIGN KEY ref2raw_result (result_id)
REFERENCES LAB.raw_results (result_id)
)
///////////////////////////////////////////////////
// CREATE TRIGGER histogram_insert
CREATE TRIGGER histogram_insert FOR LAB.raw_histograms AFTER INSERT EXECUTE
(
TRY
UPDATE LAB.raw_results SET histogram = 1 WHERE result_id = :NEW.result_id;
CATCH
STOP ($rc);
)
///////////////////////////////////////////////////
// errors (5500 - 5600 raw results)
5500
5600
///////////////////////////////////////////////////
// CREATE DBPROC LAB.add_result
CREATE DBPROC LAB.add_result
( IN device_code VARCHAR(25)
, IN sample_id VARCHAR(25)
, IN order_group VARCHAR(25)
, IN result_group VARCHAR(25)
, IN test_type VARCHAR(35)
, IN test_code VARCHAR(25)
, IN sample_type VARCHAR(10)
, IN priority VARCHAR(10)
, IN result FLOAT(5)
, IN dilution_factor FLOAT(5)
, IN normal_range_flag VARCHAR(10)
, IN container_type VARCHAR(10)
, IN units VARCHAR(10)
, IN result_status VARCHAR(10)
, IN reagent_serial VARCHAR(10)
, IN reagent_lot VARCHAR(10)
, IN sequence_number VARCHAR(10)
, IN carrier VARCHAR(10)
, IN position VARCHAR(10)
, IN test_started timestamp
, IN test_completed timestamp
, IN comment VARCHAR(255)
, OUT record_id INT
)
AS
VAR device_id INT;
device_type VARCHAR(25);
log_msg VARCHAR (255);
task_id INT;
test_id INT;
task_state INT;
test_state INT;
SET device_id = 0;
SET task_id = 0;
SET test_id = 0;
SET task_state = 0;
SET test_state = 0;
SET log_msg = '';
BEGIN
/*
device
*/
TRY
SELECT device_id, device_type
FROM LAB.devices
WHERE device_code = :device_code;
FETCH INTO :device_id, :device_type;
CATCH BEGIN
IF $rc = 100 THEN STOP(5501, 'invalid argument: The device ''' ||
device_code || ''' not register in system')
ELSE STOP($rc, $errmsg);
END;
/*
task
*/
TRY
CALL LAB.get_task(:device_id, :sample_id, :task_id, :task_state);
CATCH BEGIN
TRY
CALL LAB.add_unplanned_task(:device_id, :sample_id, :sample_type,
:task_state, :task_id);
CATCH BEGIN
IF $rc != 5601 THEN
STOP($rc, $errmsg);
END;
END;
/*
test
*/
TRY
CALL LAB.get_planned_test(:task_id, :test_code, :test_id);
TRY
CALL LAB.complete_test(:test_id);
CATCH
STOP($rc, $errmsg);
CATCH BEGIN
/*
planned test not found
*/
IF $rc = 5602 THEN BEGIN
TRY
CALL LAB.exist_test(:task_id, :test_code);
TRY
CALL LAB.add_repeated_test(:task_id, :test_code, :test_type, :priority,
:dilution_factor, :test_state, :test_id);
CATCH
STOP($rc, $errmsg);
CATCH BEGIN
IF $rc = 5602 THEN BEGIN
TRY
CALL LAB.add_unplanned_test(:task_id, :test_code, :test_type,
:priority, :dilution_factor, :test_state, :test_id);
CATCH
STOP($rc, $errmsg);
END;
END;
END
ELSE BEGIN
STOP($rc, $errmsg);
END;
END;
TRY
INSERT LAB.raw_results
( device_id
, sample_id
, order_group
, result_group
, test_type
, test_code
, sample_type
, priority
, result
, normal_range_flag
, units
, result_status
, sequence_number
, carrier
, position
, test_started
, test_completed
, comment
, dilution_factor
, container_type
, reagent_serial
, reagent_lot
, receive_time
, task_id
, test_id
)
values
( :device_id
, :sample_id
, :order_group
, :result_group
, :test_type
, :test_code
, :sample_type
, :priority
, :result
, :normal_range_flag
, :units
, :result_status
, :sequence_number
, :carrier
, :position
, :test_started
, :test_completed
, :comment
, :dilution_factor
, :container_type
, :reagent_serial
, :reagent_lot
, timestamp
, :task_id
, :test_id
);
SET record_id = LAB.raw_results.CURRVAL;
SET log_msg = 'The raw result has been added successfully.';
call LAB.add_event('info', 'add_rawresult', :device_code, :log_msg);
/*
auto authorize
*/
TRY
call LAB.auto_authorize(:test_id);
CATCH
STOP($rc, $errmsg);
CATCH
STOP($rc, $errmsg);
END;
////////////////////////////////////////////////////////////////////////////
///
///////////////////////////////////////////////////
// LAB.reagents
CREATE TABLE LAB.reagents
(
reagent_id INT NOT NULL DEFAULT SERIAL(1),
reagent_type INT NOT NULL DEFAULT 0,
producer_id VARCHAR(25) NOT NULL,
reagent_name VARCHAR(25) NOT NULL,
reagent_serial VARCHAR(25) NOT NULL,
reagent_lot VARCHAR(25) NOT NULL,
issue_date timestamp NOT NULL,
delivery_date timestamp NOT NULL,
expire_date timestamp NOT NULL,
status INT NOT NULL DEFAULT 0,
created timestamp NOT NULL DEFAULT TIMESTAMP,
modified timestamp NOT NULL DEFAULT TIMESTAMP,
deleted timestamp NULL,
modifier VARCHAR(32) NOT NULL DEFAULT USER,
PRIMARY KEY(reagent_id),
FOREIGN KEY ref2producers (producer_id)
REFERENCES LAB.producers (producer_id)
)
///////////////////////////////////////////////////
// LAB.specify_reagent4devicetype
CREATE TABLE LAB.specify_reagent4devicetype
(
device_type VARCHAR(25) NOT NULL,
reagent_id INT NOT NULL,
CONSTRAINT unique_key
UNIQUE (device_type, reagent_id),
FOREIGN KEY ref2devicetypes (device_type)
REFERENCES LAB.devicetypes (device_type),
FOREIGN KEY ref2reagents (reagent_id)
REFERENCES LAB.reagents (reagent_id)
)
///////////////////////////////////////////////////
// LAB.specify_reagent4analit
CREATE TABLE LAB.specify_reagent4analit
(
device_type VARCHAR(25) NOT NULL,
reagent_id INT NOT NULL,
analit_id INT NOT NULL,
CONSTRAINT unique_key
UNIQUE (device_type, reagent_id, analit_id),
FOREIGN KEY ref2reagent4devicetype (device_type, reagent_id)
REFERENCES LAB.specify_reagent4devicetype (device_type, reagent_id),
FOREIGN KEY ref2analit4devicetype (device_type, analit_id)
REFERENCES LAB.specify_analit4devicetype (device_type, analit_id)
)
///////////////////////////////////////////////////
// LAB.specify_reagent4device
CREATE TABLE LAB.specify_reagent4device
(
reagent_device INT NOT NULL DEFAULT SERIAL(1),
device_type VARCHAR(25) NOT NULL,
reagent_id INT NOT NULL,
device_id INT NOT NULL,
status INT NOT NULL DEFAULT 0,
created timestamp NOT NULL DEFAULT TIMESTAMP,
deleted timestamp NULL,
modifier VARCHAR(32) NOT NULL DEFAULT USER,
PRIMARY KEY(reagent_device),
FOREIGN KEY ref2reagent4devicetype (device_type, reagent_id)
REFERENCES LAB.specify_reagent4devicetype (device_type, reagent_id),
FOREIGN KEY ref2devices (device_id)
REFERENCES LAB.devices (device_id)
)
///////////////////////////////////////////////////
// errors (5700 - 5800 eventlog)
5700 -
///////////////////////////////////////////////////
// CREATE DBPROC LAB.get_reagent4device
CREATE DBPROC LAB.get_reagent4device
( IN device_id INT
, IN analit_id INT
, OUT reagent_device INT
)
AS
BEGIN
TRY
select b.reagent_device
from
LAB.specify_reagent4analit a,
LAB.specify_reagent4device b
where
a.device_type = b.device_type
and a.reagent_id = b.reagent_id
and b.device_id = :device_id
and a.analit_id = :analit_id;
FETCH INTO :reagent_device;
CATCH
IF $rc = 100 THEN STOP(5710, 'invalid argument: The analit_id ''' ||
analit_id || ''' not specified for the device_id ' || device_id)
ELSE STOP($rc, $errmsg);
END;
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
