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

Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам

121

CREATE TABLE IF NOT EXISTS `kcson`.`dep_has_main` (`id` INT, `contracts_id` INT, `dep_id` INT, `ufio_id` INT, `serv_id` INT,

`worker_id` INT, `vdate` INT, `uslnum` INT, `note` INT, `create` INT, `ts` INT, `cr_by` INT, `upd_by` INT, `reported` INT,

`wdate` INT, `overdid` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`last_used_workers`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`last_used_workers` (`id` INT, `worker` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`serv_activ`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`serv_activ` (`id` INT, `tnum` INT, `serv` INT, `year` INT, `sub_serv` INT, `sub_serv_str`

INT, `price` INT, `price2` INT, `price3` INT, `archive` INT, `total` INT, `acronym` INT, `workload` INT, `content` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`servOFripso`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`servOFripso` (`id` INT, `tnum` INT, `serv` INT, `year` INT, `sub_serv` INT, `sub_serv_str`

INT, `price` INT, `price2` INT, `price3` INT, `archive` INT, `total` INT, `acronym` INT, `workload` INT, `content` INT, `r_id` INT,

`ripso` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`fioOFdepBYserv`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`fioOFdepBYserv` (`ufio` INT, `ufio_short` INT, `ufiobirth` INT, `ESRN` INT, `ufioDeath`

INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`servOFyear`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`servOFyear` (`id` INT, `contracts_id` INT, `dep_id` INT, `ufio_id` INT, `serv_id` INT,

`worker_id` INT, `vdate` INT, `uslnum` INT, `note` INT, `create` INT, `ts` INT, `cr_by` INT, `upd_by` INT, `reported` INT,

`wdate` INT, `overdid` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`servOFyear-2`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`servOFyear-2` (`id` INT, `contracts_id` INT, `dep_id` INT, `ufio_id` INT, `serv_id` INT,

`worker_id` INT, `vdate` INT, `uslnum` INT, `note` INT, `create` INT, `ts` INT, `cr_by` INT, `upd_by` INT, `reported` INT,

`wdate` INT, `overdid` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`servOFyear-3`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`servOFyear-3` (`id` INT, `contracts_id` INT, `dep_id` INT, `ufio_id` INT, `serv_id` INT,

`worker_id` INT, `vdate` INT, `uslnum` INT, `note` INT, `create` INT, `ts` INT, `cr_by` INT, `upd_by` INT, `reported` INT,

`wdate` INT, `overdid` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`servOFyear-1`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`servOFyear-1` (`id` INT, `contracts_id` INT, `dep_id` INT, `ufio_id` INT, `serv_id` INT,

`worker_id` INT, `vdate` INT, `uslnum` INT, `note` INT, `create` INT, `ts` INT, `cr_by` INT, `upd_by` INT, `reported` INT,

`wdate` INT, `overdid` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`fioOFdepBYset`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`fioOFdepBYset` (`ufio` INT, `ufio_short` INT, `ufiobirth` INT, `ESRN` INT, `ufioDeath`

INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`dep_has_ufio_by_ripso`

-- -----------------------------------------------------

122

CREATE TABLE IF NOT EXISTS `kcson`.`dep_has_ufio_by_ripso` (`id` INT, `ufio` INT, `ufio_short` INT, `ufioDeath` INT,

`ufiobirth` INT, `ESRN` INT, `prim` INT, `phone` INT, `snils` INT, `curator` INT, `create` INT, `ts` INT, `cr_by` INT, `upd_by` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`dep_has_ufio`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`dep_has_ufio` (`id` INT, `ufio` INT, `ufio_short` INT, `ufioDeath` INT, `ufiobirth` INT,

`ESRN` INT, `prim` INT, `phone` INT, `snils` INT, `curator` INT, `create` INT, `ts` INT, `cr_by` INT, `upd_by` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`worker_has_dep`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`worker_has_dep` (`dep_id` INT, `dep` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`dep_has_serv`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`dep_has_serv` (`id` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`main_cprice`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`main_cprice` (`*` INT, `to_pay` INT, `to_pay2` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`main_NZ`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`main_NZ` (`id` INT, `contracts_id` INT, `dep_id` INT, `ufio_id` INT, `serv_id` INT,

`worker_id` INT, `vdate` INT, `uslnum` INT, `note` INT, `create` INT, `ts` INT, `cr_by` INT, `upd_by` INT, `reported` INT,

`wdate` INT, `overdid` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`dep_total_serv`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`dep_total_serv` (`contracts_id` INT, `dep_id` INT, `serv_id` INT, `worker_id` INT,

`SUM(uslnum)` INT, `MONTH(vdate)` INT, `YEAR(vdate)` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`dep_total_supserv1`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`dep_total_supserv1` (`dep_id` INT, `worker_id` INT, `SUM(uslnum)` INT, `mnth1` INT,

`year1` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`exp_main`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`exp_main` (`id` INT, `ufio` INT, `tnum` INT, `worker` INT, `uslnum` INT, `vdate` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`view1`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`view1` (`ufio` INT, `worker` INT, `"C1"` INT, `sum(uslnum)` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`max_pay_in_month_50`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`max_pay_in_month_50` (`*` INT, `max_pay` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`max_pay_in_month_75`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`max_pay_in_month_75` (`*` INT, `max_pay` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`max_pay_in_month`

-- -----------------------------------------------------

123

CREATE TABLE IF NOT EXISTS `kcson`.`max_pay_in_month` (`id` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- Placeholder table for view `kcson`.`total_cprice_in_month`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `kcson`.`total_cprice_in_month` (`contracts_id` INT, `SUM(to_pay)` INT);

SHOW WARNINGS;

-- -----------------------------------------------------

-- function SET_DEP

-- -----------------------------------------------------

USE `kcson`;

DROP function IF EXISTS `kcson`.`SET_DEP`;

SHOW WARNINGS;

DELIMITER $$

USE `kcson`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `SET_DEP`(

`depId` INT, `wrkID` INT

) RETURNS int(11)

MODIFIES SQL DATA

NOT DETERMINISTIC

COMMENT 'set default department (with checking is it possible)'

BEGIN

#declare wrkID int;

declare res int default 0;

#set wrkID= (select id from worker w where w.user=current_user());

set res = (select dep_id from workerOFdep where worker_id=wrkID and dep_id=depId);

#select @res := dep_id from workerOFdep where worker_id=@wrkID and dep_id=depId;

if res > 0 then

update workerOFdep set active=0 where(worker_id=wrkID);

update workerOFdep set active=1 where(worker_id=wrkID and dep_id=depId);

return 1;

else

return 0;

end if;

END$$

DELIMITER ;

SHOW WARNINGS;

-- -----------------------------------------------------

-- function GET_DEP

-- -----------------------------------------------------

USE `kcson`;

DROP function IF EXISTS `kcson`.`GET_DEP`;

SHOW WARNINGS;

DELIMITER $$

USE `kcson`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `GET_DEP`(

`wrkID` INT

) RETURNS int(11)

LANGUAGE SQL

NOT DETERMINISTIC

READS SQL DATA

SQL SECURITY INVOKER

COMMENT 'get default department '

BEGIN

#declare wrkID int (0);

declare dep_id int (0);

#set @wrkID = (select id from worker w where w.user=current_user());

set dep_id = (select dep_id from dep_has_worker where worker_id=wrkID and archive=0);

if dep_id > 0 then

return dep_id;

else

return 1;

124

# 1 - undefined dep

end if;

END$$

DELIMITER ;

SHOW WARNINGS;

-- -----------------------------------------------------

-- function GET_wID

-- -----------------------------------------------------

USE `kcson`;

DROP function IF EXISTS `kcson`.`GET_wID`;

SHOW WARNINGS;

DELIMITER $$

USE `kcson`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `GET_wID`(

) RETURNS int(11)

LANGUAGE SQL

NOT DETERMINISTIC

READS SQL DATA

SQL SECURITY INVOKER

COMMENT 'get default department '

BEGIN

return (select id from worker w where w.user=current_user());

END$$

DELIMITER ;

SHOW WARNINGS;

-- -----------------------------------------------------

-- procedure import_ripso

-- -----------------------------------------------------

USE `kcson`;

DROP procedure IF EXISTS `kcson`.`import_ripso`;

SHOW WARNINGS;

DELIMITER $$

USE `kcson`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `import_ripso`()

BEGIN

START TRANSACTION;

INSERT INTO `kcson`.`ripso` (`id`, `ripsoNum`, `year`, `active`, `servform`, `months`)

values

(default, '2', '2019', '1', 

12),

(default, '3', '2019', '1', '', 2),

(default, '4', '2019', '1',  3),

(default, '5', '2019', '1', '', 12),

(default, '6', '2019', '1',  12),

(default, '7', '2019', '1', 

12),

(default, '8', '2019', '1', '', 2),

(default, '9', '2019', '1',  3),

(default, '10', '2019', '0', '', 0),

(default, '11', '2019', '0', '', 0),

(default, '12', '2019', '0', '', 0),

(default, '13', '2019', '0', '', 0),

(default, '14', '2019', '1',  12),

(default, '15', '2019', '1', '', 12),

(default, '16', '2019', '1', '', 12);

COMMIT;

End$$

DELIMITER ;

SHOW WARNINGS;

-- -----------------------------------------------------

-- function GET_CONTR

-- -----------------------------------------------------

USE `kcson`;

DROP function IF EXISTS `kcson`.`GET_CONTR`;

SHOW WARNINGS;

125

DELIMITER $$

USE `kcson`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `GET_CONTR`(

`idfio` INT, `vdate` date, `idep` int

) RETURNS int(11)

READS SQL DATA

DETERMINISTIC

COMMENT 'get idcontract '

BEGIN

declare cc int (0);

declare rr varchar (255);

set cc = (select count(id) from contracts where ripso_id in

(select ripso_id from dep_has_ripso where dep_id=idep)

and startdate <= vdate and enddate >= vdate and blocked=0 and ufio_id=idfio);

if cc = 1 then

return (select id from contracts where ripso_id in

(select ripso_id from dep_has_ripso where dep_id=idep)

and startdate <= vdate and enddate >= vdate and blocked=0 and ufio_id=idfio);

elseif cc = 0 then



idep);

SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = cc;

return 0;

else



vdate, idep );

SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = cc;

return -1;

end if;

END$$

DELIMITER ;

SHOW WARNINGS;

-- -----------------------------------------------------

-- function CHECK_OVERDID

-- -----------------------------------------------------

USE `kcson`;

DROP function IF EXISTS `kcson`.`CHECK_OVERDID`;

SHOW WARNINGS;

DELIMITER $$

USE `kcson`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `CHECK_OVERDID`( CONTR int, SERV int)

RETURNS int(11)

LANGUAGE SQL

DETERMINISTIC

READS SQL DATA

SQL SECURITY DEFINER

COMMENT 'check overdid return serv left

no existence check!!!'

BEGIN

DECLARE pln int;

DECLARE fll int;

select planned, filled into pln, fll

from contracts_has_serv where

contracts_id = CONTR and

serv_id = SERV;

return (pln - fll);

END$$

DELIMITER ;

SHOW WARNINGS;

-- -----------------------------------------------------

-- procedure delete_setup_tools

-- -----------------------------------------------------

USE `kcson`;

DROP procedure IF EXISTS `kcson`.`delete_setup_tools`;

Источник: https://baza.diplomsite.ru/previewfile/2412

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



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