БД_П. Программа 5 2 Объекты автоматизации, на которых используется программа 5
 Скачать 0.8 Mb.
|
Таблица A.2 – sick_leave_certificates (Больничные листы). Информация о больничных листах, выданных поликлиникой
Таблица A.3– holiday (Отпуска). Информация об отпусках
Таблица A.4– patients (Пациенты). Информация о пациентах поликлиники
Таблица A.5– patients_stat (Пациенты стационара). Информация о пациентах, лечащихся стационарно
Таблица A.6– s_diagnosis (Диагнозы). Справочник диагнозов
Таблица A.7– s_positions (Должности). Справочник должностей
Таблица A.8– s_units (Отделения). Справочник отделений
Таблица A.9– users (Пользователи). Пользователи приложения
Процедура A.10 – add_or_updt_examination– обновляет запись или добавляет новую в таблице examination_resoults (Результаты обследования). Входные данные (IN patient_ INT, IN date__ DATETIME, IN diagnosis_ INT, IN doctor_ INT, IN stat_ VARCHAR(255), IN id_ex INT). Выходные данные отсутствуют. Тело процедуры: BEGIN IF id_ex = -1 THEN INSERT INTO pclinic.examination_resoults (patient, date_, diagnosis, doctor, stat) VALUES (patient_, date__, diagnosis_, doctor_, stat_); ELSE UPDATE pclinic.examination_resoults SET patient = patient_, date_ = date__, diagnosis = diagnosis_, doctor = doctor_, stat = stat_ WHERE id_exr = id_ex; END IF; END Процедура A.11 – add_sick_leave – добавляет новую запись в таблицу sick_leave_certificates (Больничные листы) . Входные данные ( patient_name_ VARCHAR(255) , diagnosis_ INT, date_begin_ DATE , date_end_ DATE, doctor_name_ VARCHAR(255). Выходные данные отсутствуют. Тело процедуры: BEGIN insert INTO pclinic.sick_leave_certificates(patient_name,diagnosis,date_begin,date_end,doctor_name)VALUES ( patient_name_, diagnosis_, date_begin_, date_end_, doctor_name_); END Процедура A.12 – close_sick_leave_cert – Закрывает больничный лист путем обновления записи в соответствующей таблице. Входные данные (id_sick_leave_cert VARCHAR(255), date_end_ DATE) . Выходные данные отсутствуют. Тело процедуры: BEGIN UPDATE pclinic.sick_leave_certificates SET date_end = date_end_ WHERE id_slc = id_sick_leave_cert; END Процедура A.13– get_columns – возвращает список наименований и комментариев заданной таблицы. Входные данные (IN table_name_ VARCHAR(50)). Выходные данные отсутствуют. Тело процедуры: BEGIN SELECT COLUMN_NAME as col , COLUMN_COMMENT as com FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = 'pclinic' AND TABLE_NAME = table_name_; END Процедура A.14 – get_user_params – возвращает строку пользователя по его логину и паролю. Входные данные (pas VARCHAR(255), log VARCHAR(255)). Тело процедуры: BEGIN select concat(fam,' ',`name`,' ',lastname) as user_, admin from users where password=password(pas) and login=log; END Процедура A.15 – service_nurce – возвращает перечень данные о количество пациентов, обслуженных каждой медсестрой по месяцам. Входные данные отсутствуют. Тело процедуры: SELECT nurse_serv , count(patient) AS count_patient -- , month(date_off) AS month_ , -- DATE_FORMAT(date_off,'%M') AS nm monthname(date_off) AS nm FROM pclinic.patients_stat WHERE year(date_off) = 2014 GROUP BY nm , nurse_serv ORDER BY nurse_serv , nm ASC; END Процедура A.16 – select1 – возвращает число пациентов, выписанных из стационара по месяцам. Входные данные (IN year_ SMALLINT). Тело процедуры: SELECT get_monthname(month(date_off)) as mon , count(patient) as pat FROM pclinic.patients_stat WHERE year(date_off) = year(now()) - 1 GROUP BY (month(date_off)); END Процедура A.27 – select3 – возвращает число различных стационарных пациентов было на каждого врача за год. Входные данные (IN `year` YEAR). Тело процедуры: BEGIN SELECT get_init(dd.surname,dd.name,dd.lastname) as doctor , count(DISTINCT patient) as par_d FROM pclinic.patients_stat pt LEFT JOIN pclinic.doctors dd ON dd.id_doctor = pt.doctor WHERE year(date_off) = 2014 GROUP BY doctor; END Процедура A.17 – select6 – возвращает статистические величины (среднее, максимальное, минимальное значения, количество) по пациентам за год . Входные данные отсутствуют. Тело процедуры: BEGIN -- в среднем поступивших в стац. в месяц -- в среднем обслед. в месяц SELECT round(avg(dd.pat)) `avg` , max(dd.pat) `max` , min(dd.pat) `min` , count(dd.pat) `count` FROM ( SELECT count(patient) AS pat FROM pclinic.patients_stat WHERE year(date_on) = 2014 GROUP BY month(date_on)) dd; END Процедура A.18 – fhrt14 – возвращает данные по выработке лаборатории за указанный период для медицинского учреждения. Входные данные (zaved INT, date_ot DATE, IN date_do DATE). Тело процедуры: BEGIN IF zaved = -1 THEN SELECT count(*) FROM bolca.labs ll WHERE ll.`date` BETWEEN date_ot AND date_do; ELSE SELECT count(*) FROM bolca.labs ll WHERE ll.zaved = zaved AND ll.`date` BETWEEN date_ot AND date_do; END IF; END Процедура A.19 – update_cur_rep – обеспечивает обновление отчета о количестве пациентов, принятых врачами в текущем месяце (у каждого врача). Входные данные (cur_month INT) . Тело процедуры: BEGIN DECLARE done, flag BOOLEAN DEFAULT FALSE; DECLARE a, b INT; DECLARE cur CURSOR FOR SELECT d.id_doctor , ifnull(ff.pat, 0) ppp FROM ( SELECT p.doctor doc , p.date_on don , count(p.patient) pat FROM pclinic.patients_stat p WHERE year(p.date_on) = 2015 AND month(p.date_on) = cur_month GROUP BY p.doctor) ff RIGHT JOIN pclinic.doctors d ON d.id_doctor = ff.doc; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; DECLARE CONTINUE HANDLER FOR 1329 SET done = TRUE; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; END; SET a = NULL; SET b = NULL; SET done = FALSE; IF cur_month = 0 THEN SET cur_month = month(now()); END IF; OPEN cur; met: REPEAT FETCH cur INTO a, b; IF NOT done THEN UPDATE current_report SET cou_ = b, ost = 45 - b, date_ = curdate() WHERE id_d = a; SET flag = TRUE; END IF; UNTIL done END REPEAT; CLOSE cur; END Функция A.19 – get_age– возвращает число полных лет по дате рождения. Входные данные (date_of_birth DATE). Результат smallint. . Тело функции: BEGIN DECLARE res SMALLINT(6); SET @curd := curdate(); SET res := year(@curd) - year(date_of_birth)-1; -- if not ( MONTH(date_of_birth) <= month(@curd) and day(date_of_birth)<=day(@curd)) then -- SET res=res-1; -- end if; if MONTH(date_of_birth) <= month(@curd) then if day(date_of_birth)<=day(@curd) then SET res = res + 1; end if; end if; RETURN res; END Функция A.19 – get_age_format– возвращает форматированное число полных лет по дате рождения. Входные данные (date_of_birth DATE). Результат varchar(15). Тело функции: BEGIN declare res_str VARCHAR(15) DEFAULT ''; set @res:=(select get_age(date_of_birth)); if @res>10 then set @res_a:=@res % 10 ; else set @res_a:=@res; end if; if @res_a=1 then set res_str=' год'; else if @res_a>1 and @res_a<5 then SET res_str = ' года'; else SET res_str = ' лет'; end if;end if; RETURN (concat(@res,res_str)); END Функция A.19 – get_init– возвращает фамилию с инициалами по полному ФИО. Входные данные (fam VARCHAR(255), nam VARCHAR(255), othch VARCHAR(255)). Результат varchar(255). Тело функции: BEGIN SET @res := concat(fam, ' ', substr(nam, 1, 1), '.', substr(othch, 1, 1), '.'); RETURN @res; END Функция A.19 – get_monthname– возвращает наименование месяца по-русски. Входные данные (month_ TINYINT). Результат varchar(15). Тело функции: BEGIN CASE month_ WHEN 1 THEN RETURN 'Январь'; WHEN 2 THEN RETURN 'Февраль'; WHEN 3 THEN RETURN 'Март'; WHEN 4 THEN RETURN 'Апрель'; WHEN 5 THEN RETURN 'Май'; WHEN 6 THEN RETURN 'Июнь'; WHEN 7 THEN RETURN 'Июль'; WHEN 8 THEN RETURN 'Август'; WHEN 9 THEN RETURN 'Сентябрь'; WHEN 10 THEN RETURN 'Октябрь'; WHEN 11 THEN RETURN 'Ноябрь'; WHEN 12 THEN RETURN 'Декабрь'; ELSE RETURN 'Неизвестно'; END CASE; END Представление A.19 – v_doctors– Врачи. Описание представления: CREATE OR REPLACE VIEW v_doctors AS select `doc`.`id_doctor` AS `id_doctor`,`doc`.`surname` AS `surname`,`doc`.`name` AS `name`,`doc`.`lastname` AS `lastname`,`uu`.`unit` AS `unit`,`pp`.`position_name` AS `position_name`,`pp`.`salary` AS `salary`,`doc`.`date_on` AS `date_on` from ((`doctors` `doc` left join `s_positions` `pp` on((`pp`.`id_position` = `doc`.`position`))) left join `s_units` `uu` on((`uu`.`id_unit` = `doc`.`unit`))) order by `doc`.`surname`; Представление A.19 – v_examination_resoults– Результаты обследования. Описание представления: CREATE OR REPLACE VIEW v_examination_resoults AS select `er`.`id_exr` AS `id_exr`,`get_init`(`p`.`surname`,`p`.`name`,`p`.`lastname`) AS `patien`,year(`p`.`date_of_birth`) AS `year_of_birth`,`get_age`(`p`.`date_of_birth`) AS `age`,`er`.`date_` AS `date_`,`sd`.`diagnosis_name` AS `diagnosis_name`,`d`.`surname` AS `id_doctor`,`er`.`stat` AS `stat` from (((`examination_resoults` `er` left join `s_diagnosis` `sd` on((`sd`.`id_diag` = `er`.`diagnosis`))) left join `patients` `p` on((`p`.`id_patient` = `er`.`patient`))) left join `doctors` `d` on((`d`.`id_doctor` = `er`.`doctor`))); Представление A.19 – v_holidays – Отпуска. Описание представления: CREATE OR REPLACE VIEW v_holidays AS select `hh`.`id_holiday` AS `id_holiday`,concat(`dd`.`surname`,' ',`dd`.`name`,' ',`dd`.`lastname`) AS `doc`,`hh`.`date_begin` AS `date_begin`,`hh`.`date_end` AS `date_end` from (`holiday` `hh` left join `doctors` `dd` on((`dd`.`id_doctor` = `hh`.`doctor`))); Представление A.19 – v_patients_stat– Пациенты стационара. Описание представления: CREATE OR REPLACE VIEW v_patients_stat AS select `pst`.`id_pat_st` AS `id_pat_st`,concat(`pa`.`surname`,' ',`pa`.`name`,' ',`pa`.`lastname`) AS `patient`,year(`pa`.`date_of_birth`) AS `year_of_birth`,`get_init`(`doc`.`surname`,`doc`.`name`,`doc`.`lastname`) AS `doctor`,`di`.`diagnosis_name` AS `diagnos`,`pst`.`date_on` AS `date_on`,`pst`.`date_off` AS `date_off`,`pst`.`nurse_serv` AS `nurse_serv` from (((`patients_stat` `pst` left join `patients` `pa` on((`pa`.`id_patient` = `pst`.`patient`))) left join `doctors` `doc` on((`doc`.`id_doctor` = `pst`.`doctor`))) left join `s_diagnosis` `di` on((`di`.`id_diag` = `pst`.`diagnosis`))); Представление A.19 – v_sick_leave_certificates– Больничные листы. Описание представления: CREATE OR REPLACE VIEW v_sick_leave_certificates AS select `slc`.`id_slc` AS `id_slc`,`slc`.`patient_name` AS `patient_name`,`dd`.`diagnosis_name` AS `diagn`,`slc`.`date_begin` AS `date_begin`,`slc`.`date_end` AS `date_end`,((`slc`.`date_end` - `slc`.`date_begin`) + 1) AS `days`,`slc`.`doctor_name` AS `doctor_name` from (`sick_leave_certificates` `slc` left join `s_diagnosis` `dd` on((`dd`.`id_diag` = `slc`.`diagnosis`))); Триггер A.19 – update_patient_stat – Выставляет значение "число дней проведенных в стационаре" при выписке пациента из стационара. Описание триггера: CREATE TRIGGER update_patient_stat BEFORE UPDATE ON patients_stat FOR EACH ROW BEGIN SET @new_day = dayofyear(old.date_on); SET @new_day2 = dayofyear(new.date_off); IF @new_dayIS is NOT NULL and NEW.period_days IS NULL THEN SET NEW.period_days = (@new_day2 - @new_day+1); END IF; END Триггер A.19 – set_password – Шифрует пароль пользователя в БД(при добавлении нового пользователя). Описание триггера: CREATE TRIGGER pclinic.set_password BEFORE INSERT ON pclinic.users FOR EACH ROW BEGIN SET NEW.password = password(NEW.password); END ПРИЛОЖЕНИЕ БСХЕМА БД Поликлиники |