Дипломная работа: Автоматизация учета предоставленных услуг в комплексном центре социального обслуживания населения и анализа данных на примере СПБ ГБУСОН "КЦСОН Адмиралтейского района Санкт-Петербурга"

Скачать

Заказать новую работу

Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам
136
DELIMITER $$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`ufio_BEFORE_INSERT` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`ufio_BEFORE_INSERT` BEFORE INSERT ON `ufio` FOR EACH ROW
BEGIN
set new.ufio=trim(new.ufio);
SET NEW.cr_by = get_wID();
SET NEW.upd_by = get_wID();
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`ufio_AFTER_INSERT` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`ufio_AFTER_INSERT` AFTER INSERT ON `ufio` FOR EACH ROW
BEGIN
insert into contracts (ufio_id,dep_id,blocked,startdate,enddate)
values (new.id, GET_DEP(GET_wID()),1,date(now()),date(now()));
# stub contract to assign fio to department
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`ufio_BEFORE_UPDATE` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`ufio_BEFORE_UPDATE` BEFORE UPDATE ON `ufio` FOR EACH ROW
BEGIN
set new.ufio=trim(new.ufio);
SET NEW.upd_by = get_wID();
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`contracts_BEFORE_INSERT` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`contracts_BEFORE_INSERT` BEFORE INSERT ON `contracts` FOR EACH
ROW
BEGIN
SET NEW.cr_by = get_wID();
IF (NEW.startdate IS NULL) THEN
SET NEW.startdate = now();
END IF;
IF (NEW.enddate IS NULL) THEN
SET NEW.enddate = now();
END IF;
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`contracts_BEFORE_UPDATE` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`contracts_BEFORE_UPDATE` BEFORE UPDATE ON `contracts` FOR
EACH ROW
BEGIN
SET NEW.upd_by = get_wID();
IF (NEW.startdate IS NULL) THEN
SET NEW.startdate = now();
END IF;
IF (NEW.enddate IS NULL) THEN
SET NEW.enddate = now();
137
END IF;
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`main_BEFORE_INSERT` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`main_BEFORE_INSERT` BEFORE INSERT ON `main` FOR EACH ROW
BEGIN
declare ovdid boolean;
INSERT INTO contracts_has_serv
SET
filled = new.uslnum,
contracts_id = new.contracts_id,
serv_id = new.serv_id
ON DUPLICATE KEY UPDATE
filled = filled + new.uslnum,
contracts_id = new.contracts_id,
serv_id = new.serv_id;
if CHECK_OVERDID(new.contracts_id, new.serv_id) < 0 then
SET NEW.overdid = True;
end if;
SET NEW.cr_by = get_wID();
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`main_BEFORE_UPDATE` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`main_BEFORE_UPDATE` BEFORE UPDATE ON `main` FOR EACH ROW
BEGIN
UPDATE contracts_has_serv set filled=filled+new.uslnum-old.uslnum,
contracts_id=new.contracts_id, serv_id=new.serv_id;
if CHECK_OVERDID(new.contracts_id, new.serv_id) < 0 then
SET NEW.overdid = True;
end if;
SET NEW.upd_by=get_wID();
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`main_AFTER_DELETE` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`main_AFTER_DELETE` AFTER DELETE ON `main` FOR EACH ROW
BEGIN
UPDATE contracts_has_serv set fill=fill-old.uslnum, contracts_id=old.contracts_id, serv_id=old.serv_id;
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`add_info_BEFORE_INSERT` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`add_info_BEFORE_INSERT` BEFORE INSERT ON `add_info` FOR EACH
ROW
BEGIN
SET NEW.cr_by = get_wID();
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`add_info_AFTER_INSERT` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`add_info_AFTER_INSERT` AFTER INSERT ON `add_info` FOR EACH
ROW
BEGIN
138
declare ripso int default 0;
set ripso = (select ripso_id from contracts where id=new.contracts_id);
if ripso > 0 then
insert into contracts_has_serv (serv_id, contracts_id, planned, archive)
select serv_id, new.contracts_id, planned,0 from ripso_has_serv where ripso_id=ripso;
# By default ippsu had all services - worker can change it later
end if;
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`add_info_BEFORE_UPDATE` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`add_info_BEFORE_UPDATE` BEFORE UPDATE ON `add_info` FOR
EACH ROW
BEGIN
SET NEW.upd_by = get_wID();
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`invoice_BEFORE_INSERT` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`invoice_BEFORE_INSERT` BEFORE INSERT ON `invoice` FOR EACH
ROW
BEGIN
SET NEW.cr_by = get_wID(CURRENT_USER);
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`invoice_BEFORE_UPDATE` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`invoice_BEFORE_UPDATE` BEFORE UPDATE ON `invoice` FOR EACH
ROW
BEGIN
SET NEW.upd_by = get_wID(CURRENT_USER);
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`invoice_has_payment_BEFORE_INSERT` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`invoice_has_payment_BEFORE_INSERT` BEFORE INSERT ON
`invoice_has_payment` FOR EACH ROW
BEGIN
declare inv int;
declare pay int;
# check duplicates
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`ufio_has_category_BEFORE_INSERT` $$
SHOW WARNINGS$$
USE `kcson`$$
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`ufio_has_category_BEFORE_INSERT` BEFORE INSERT ON
`ufio_has_category` FOR EACH ROW
BEGIN
SET NEW.upd_by = get_wID();
SET NEW.cr_by = get_wID();
END$$
SHOW WARNINGS$$
USE `kcson`$$
DROP TRIGGER IF EXISTS `kcson`.`ufio_has_category_BEFORE_UPDATE` $$
SHOW WARNINGS$$
USE `kcson`$$
139
CREATE DEFINER = CURRENT_USER TRIGGER `kcson`.`ufio_has_category_BEFORE_UPDATE` BEFORE UPDATE ON
`ufio_has_category` FOR EACH ROW
BEGIN
SET NEW.upd_by = get_wID();
END$$
SHOW WARNINGS$$
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- -----------------------------------------------------
-- Data for table `kcson`.`dep`
-- -----------------------------------------------------
START TRANSACTION;
USE `kcson`;
INSERT INTO `kcson`.`dep` (`id`, `dep`, `depFullName`, `note`, `archive`) VALUES (1, ' 1', '  ',
NULL, NULL);
COMMIT;
-- -----------------------------------------------------
-- Data for table `kcson`.`ufio`
-- -----------------------------------------------------
START TRANSACTION;
USE `kcson`;
INSERT INTO `kcson`.`ufio` (`id`, `ufio`, `ufio_short`, `ufioDeath`, `ufiobirth`, `ESRN`, `prim`, `phone`, `snils`, `curator`,
`create`, `ts`, `cr_by`, `upd_by`) VALUES (DEFAULT, ' ', ' ', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL);
INSERT INTO `kcson`.`ufio` (`id`, `ufio`, `ufio_short`, `ufioDeath`, `ufiobirth`, `ESRN`, `prim`, `phone`, `snils`, `curator`,
`create`, `ts`, `cr_by`, `upd_by`) VALUES (DEFAULT, ' 2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL);
COMMIT;
-- -----------------------------------------------------
-- Data for table `kcson`.`servform`
-- -----------------------------------------------------
START TRANSACTION;
USE `kcson`;
INSERT INTO `kcson`.`servform` (`id`, `servform`, `prim`) VALUES (1, '   ', NULL);
INSERT INTO `kcson`.`servform` (`id`, `servform`, `prim`) VALUES (2, '  
      ', NULL);
INSERT INTO `kcson`.`servform` (`id`, `servform`, `prim`) VALUES (3, '  
      ', NULL);
INSERT INTO `kcson`.`servform` (`id`, `servform`, `prim`) VALUES (4, '   
  ', NULL);
COMMIT;
-- -----------------------------------------------------
-- Data for table `kcson`.`ripso`
-- -----------------------------------------------------
START TRANSACTION;
USE `kcson`;
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (1, '1', 2019, 1, 1, 12);
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (2, '2', 2019, 1, 2, 12);
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (3, '3', 2019, 1, 3, 2);
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (4, '4', 2019, 1, 4, 3);
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (5, '5', 2019, 1, 4, 12);
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (6, '6', 2019, 1, 1, 12);
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (7, '7', 2019, 1, 2, 12);
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (8, '8', 2019, 1, 3, 2);
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (9, '9', 2019, 1, 4, 3);
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (10, '14', 2019, 1, 1, 12);
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (11, '70', 2019, 1, 3, 12);
INSERT INTO `kcson`.`ripso` (`id`, `ripso`, `year`, `archive`, `servform_id`, `months`) VALUES (12, '71', 2019, 1, 3, 12);
COMMIT;
-- -----------------------------------------------------
-- Data for table `kcson`.`contracts`
-- -----------------------------------------------------
START TRANSACTION;
USE `kcson`;
140
INSERT INTO `kcson`.`contracts` (`id`, `contracts`, `ufio_id`, `dep_id`, `ripso_id`, `blocked`, `startdate`, `enddate`,
`ippsuNum`, `prim`, `create`, `ts`, `cr_by`, `upd_by`) VALUES (DEFAULT, '12312313', 1, 1, 1, 0, '2001-01-01', '2001-12-31',
NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `kcson`.`contracts` (`id`, `contracts`, `ufio_id`, `dep_id`, `ripso_id`, `blocked`, `startdate`, `enddate`,
`ippsuNum`, `prim`, `create`, `ts`, `cr_by`, `upd_by`) VALUES (DEFAULT, '43434', 2, 1, 1, 0, '2001-01-01', '2001-12-31', NULL,
NULL, NULL, NULL, NULL, NULL);
COMMIT;
-- -----------------------------------------------------
-- Data for table `kcson`.`roles`
-- -----------------------------------------------------
START TRANSACTION;
USE `kcson`;
INSERT INTO `kcson`.`roles` (`id`, `role`, `prim`) VALUES (1, '  ', NULL);
COMMIT;
-- -----------------------------------------------------
-- Data for table `kcson`.`worker`
-- -----------------------------------------------------
START TRANSACTION;
USE `kcson`;
INSERT INTO `kcson`.`worker` (`id`, `worker`, `user`, `prim`, `roles_id`, `dep_id`, `archive`) VALUES (1, ' ', NULL,
NULL, DEFAULT, DEFAULT, NULL);
COMMIT;
-- -----------------------------------------------------
-- Data for table `kcson`.`holiday`
-- -----------------------------------------------------
START TRANSACTION;
USE `kcson`;
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-01-01', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-01-02', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-01-03', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-01-04', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-01-05', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-01-06', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-01-07', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-01-08', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-03-08', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-05-01', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-05-02', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-05-03', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-05-09', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-05-10', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-06-12', '');
INSERT INTO `kcson`.`holiday` (`holiday`, `prim`) VALUES ('2019-11-04', '');
COMMIT;
-- -----------------------------------------------------
-- Data for table `kcson`.`category`
-- -----------------------------------------------------
START TRANSACTION;
USE `kcson`;
INSERT INTO `kcson`.`category` (`id`, `category`, `archive`, `prim`, `total`, `subof`) VALUES (15, '', 0, NULL, 0, NULL);
INSERT INTO `kcson`.`category` (`id`, `category`, `archive`, `prim`, `total`, `subof`) VALUES (16, '', NULL, NULL, 0, 13);
INSERT INTO `kcson`.`category` (`id`, `category`, `archive`, `prim`, `total`, `subof`) VALUES (17, '-. ', NULL,
NULL, NULL, 14);
INSERT INTO `kcson`.`category` (`id`, `category`, `archive`, `prim`, `total`, `subof`) VALUES (18, '-. ', NULL,
NULL, NULL, 14);
INSERT INTO `kcson`.`category` (`id`, `category`, `archive`, `prim`, `total`, `subof`) VALUES (5, '', NULL, ' ',
NULL, NULL);
INSERT INTO `kcson`.`category` (`id`, `category`, `archive`, `prim`, `total`, `subof`) VALUES (6, '', NULL, '
. ', NULL, NULL);
INSERT INTO `kcson`.`category` (`id`, `category`, `archive`, `prim`, `total`, `subof`) VALUES (7, '', NULL, ' ',
NULL, 2);
INSERT INTO `kcson`.`category` (`id`, `category`, `archive`, `prim`, `total`, `subof`) VALUES (8, '', NULL, NULL, NULL, 2);
INSERT INTO `kcson`.`category` (`id`, `category`, `archive`, `prim`, `total`, `subof`) VALUES (9, '', NULL, NULL, NULL, 2);
INSERT INTO `kcson`.`category` (`id`, `category`, `archive`, `prim`, `total`, `subof`) VALUES (10, '', NULL, NULL, NULL, 2);
INSERT INTO `kcson`.`category` (`id`, `category`, `archive`, `prim`, `total`, `subof`) VALUES (13, ' ..', NULL, NULL,
NULL, 1);
Источник: https://baza.diplomsite.ru/previewfile/2412

Смотрите также:

_1966(~1
_Антонян Ю.М., Сексуальные преступления. Чикатило и другие
_РГР № 1 (2)
- Габариты и масса 27
'Образ–Я' и его развитие средствами диалога
"Логистическая технология JIT"
"Особенности правовой системы Российской Федерации" по дисциплине "Теория государства и права"
«Анализ ликвидности и платежеспособности предприятия» (на материалах ОАО Московская кондитерская фабрика «Красный Октябрь»)