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

Скачать

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

Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам
131
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`servOFyear-3`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`servOFyear-3` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `servOFyear-3` AS SELECT * FROM kcson.main m where year(m.vdate) = (year(current_date())-
3);
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`servOFyear-1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`servOFyear-1`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`servOFyear-1` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `servOFyear-1` AS SELECT * FROM kcson.main m where year(m.vdate) = (year(current_date())-
1);
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`fioOFdepBYset`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`fioOFdepBYset`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`fioOFdepBYset` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `fioOFdepBYset` AS SELECT
ufio, ufio_short,ufiobirth,ESRN,ufioDeath
FROM ufio
where id in (
select ufio.id from main
INNER JOIN ufio
ON main.ufio_id = ufio.id
INNER JOIN dep
ON main.dep_id = dep.id
where main.cr_by = GET_wID( )
);
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`dep_has_ufio_by_ripso`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`dep_has_ufio_by_ripso`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`dep_has_ufio_by_ripso` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `dep_has_ufio_by_ripso` AS
select * from ufio
where id in (
select ufio_id from contracts c inner join dep_has_ripso rod on rod.ripso_id = c.ripso_id
where rod.dep_id=GET_DEP(GET_wID())
);
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`dep_has_ufio`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`dep_has_ufio`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`dep_has_ufio` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `dep_has_ufio` AS
select * from ufio
where id in (
132
select ufio_id from contracts c
where c.dep_id=GET_DEP(GET_wID())
);
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`worker_has_dep`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`worker_has_dep`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`worker_has_dep` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `worker_has_dep` AS
SELECT
dw.dep_id, d.dep
FROM
dep_has_worker dw
INNER JOIN
dep d ON d.id = dw.dep_id
INNER JOIN
worker w ON dw.worker_id = w.id
WHERE
dw.worker_id = GET_WID()
AND dw.archive = 0;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`dep_has_serv`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`dep_has_serv`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`dep_has_serv` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `dep_has_serv` AS select s.*
from
serv s inner join dep_has_ripso dhr
inner join ripso_has_serv rhs
on rhs.ripso_id = dhr.ripso_id
on rhs.serv_id=s.id
where dhr.dep_id = GET_DEP(GET_wID());
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`main_cprice`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`main_cprice`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`main_cprice` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `main_cprice` AS
SELECT
*, perc * uslnum * price as to_pay, perc * uslnum * price2 as to_pay2
FROM
(SELECT
m.*,
(SELECT
perc
FROM
add_info pd
WHERE
pddate <= m.vdate
AND contracts_id = m.contracts_id
ORDER BY pddate ASC
LIMIT 1) AS perc,
price,
price2,
133
price3
FROM
(main m
INNER JOIN serv ON m.serv_id = serv.id)
INNER JOIN contracts ON m.contracts_id = contracts.id) f;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`main_NZ`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`main_NZ`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`main_NZ` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `main_NZ` AS select * from main where uslnum >0;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`dep_total_serv`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`dep_total_serv`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`dep_total_serv` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `dep_total_serv` AS
SELECT
contracts_id,
dep_id,
serv_id,
worker_id,
SUM(uslnum),
MONTH(vdate),
YEAR(vdate)
FROM
main # main_cprice
GROUP BY contracts_id , dep_id , serv_id , worker_id;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`dep_total_supserv1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`dep_total_supserv1`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`dep_total_supserv1` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `dep_total_supserv1` AS
SELECT
contracts_id dep_id,
s.sub_serv worker_id,
SUM(uslnum),
mnth1,
year1
FROM
(SELECT
contracts_id,
dep_id,
serv_id,
worker_id,
SUM(uslnum) uslnum,
MONTH(vdate) mnth1,
YEAR(vdate) year1
FROM
main
GROUP BY contracts_id , dep_id , serv_id , worker_id) m
RIGHT JOIN
serv s ON m.serv_id = s.id;
134
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`exp_main`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`exp_main`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`exp_main` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `exp_main` AS
SELECT
s.id, u.ufio, s.tnum, w.worker, m.uslnum, m.vdate
FROM
main m
INNER JOIN
serv s ON m.serv_id = s.id
INNER JOIN
worker w ON m.worker_id = w.id
INNER JOIN
ufio u ON m.ufio_id = u.id
WHERE
m.dep_id = GET_DEP(GET_WID())
ORDER BY s.id;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`view1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`view1`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`view1` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `view1` AS
select ufio, worker
, MAX(CASE WHEN id = "C1" THEN uslnum END) "C1"
,
sum(uslnum)
from exp_main group by id, ufio, worker;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`max_pay_in_month_50`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`max_pay_in_month_50`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`max_pay_in_month_50` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `max_pay_in_month_50` AS
SELECT
*, (sdd - (lm * 1.5)) / 2 AS max_pay
FROM
(SELECT
a.contracts_id,
c.ripso_id,
a.pddate,
a.sdd,
(SELECT
IF(a.work_livemin = 0, live_min_p, live_min_w) AS live_min
FROM
live_min
WHERE
a.pddate > lmdate
ORDER BY pddate ASC
LIMIT 1) AS lm
FROM
add_info a
135
INNER JOIN contracts c ON c.id = a.contracts_id
INNER JOIN ripso r ON r.id = c.ripso_id
WHERE
r.servform_id IN (1 , 2, 3)) t;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`max_pay_in_month_75`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`max_pay_in_month_75`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`max_pay_in_month_75` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `max_pay_in_month_75` AS
SELECT
*, (sdd * 0.75) AS max_pay
FROM
(SELECT
a.contracts_id,
c.ripso_id,
a.pddate,
a.sdd,
(SELECT
IF(a.work_livemin = 0, live_min_p, live_min_w) AS live_min
FROM
live_min
WHERE
a.pddate > lmdate
ORDER BY pddate ASC
LIMIT 1) AS lm
FROM
add_info a
INNER JOIN contracts c ON c.id = a.contracts_id
INNER JOIN ripso r ON r.id = c.ripso_id
WHERE
r.servform_id IN (4)) t;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`max_pay_in_month`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`max_pay_in_month`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`max_pay_in_month` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `max_pay_in_month` AS
select * from max_pay_in_month_75
union
select * from max_pay_in_month_50;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`total_cprice_in_month`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`total_cprice_in_month`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`total_cprice_in_month` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `total_cprice_in_month` AS
SELECT
contracts_id, SUM(to_pay)
FROM
main_cprice
GROUP BY MONTH(vdate) , YEAR(vdate);
SHOW WARNINGS;
USE `kcson`;
Источник: https://baza.diplomsite.ru/previewfile/2412

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

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