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

Скачать

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

Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам
126
SHOW WARNINGS;
DELIMITER $$
USE `kcson`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_setup_tools`()
BEGIN
#drop PROCEDURE if exists import_serv_all;
#drop PROCEDURE if exists import_ripso;
#drop PROCEDURE if exists import_dep_all;
end$$
DELIMITER ;
SHOW WARNINGS;
-- -----------------------------------------------------
-- procedure gaver_Rdata
-- -----------------------------------------------------
USE `kcson`;
DROP procedure IF EXISTS `kcson`.`gaver_Rdata`;
SHOW WARNINGS;
DELIMITER $$
USE `kcson`$$
CREATE DEFINER=`root`@`localhost` procedure `gaver_Rdata`(
`rnameID` INT, `rperiodID` INT
)
MODIFIES SQL DATA
NOT DETERMINISTIC
COMMENT ''
BEGIN
#declare wrkID int;
declare res int default 0;
#set wrkID= (select id from worker w where w.user=current_user());
insert into Rdata (`year`, uslnum, money, `2ndmoney`, ufio_id, dep_id, serv_id, worker_id )
select year(vdate), uslnum, uslnum*1, uslnum*1, ufio_id, dep_id, serv_id, worker_id from
main m inner join contracts c on m.cotracts_id=c.id
;
END$$
DELIMITER ;
SHOW WARNINGS;
-- -----------------------------------------------------
-- procedure SHOW_COL
-- -----------------------------------------------------
USE `kcson`;
DROP procedure IF EXISTS `kcson`.`SHOW_COL`;
SHOW WARNINGS;
DELIMITER $$
USE `kcson`$$
CREATE PROCEDURE SHOW_COL(IN sqlToShow TEXT)
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DROP TEMPORARY TABLE IF EXISTS tempTable;
SET @sqlLimit0 = CONCAT('CREATE TEMPORARY TABLE tempTable AS (SELECT * FROM (',
sqlToShow, ') subq LIMIT 0)');
PREPARE stmt FROM @sqlLimit0;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SHOW COLUMNS FROM tempTable;
END$$
DELIMITER ;
SHOW WARNINGS;
-- -----------------------------------------------------
-- procedure Pivot
-- -----------------------------------------------------
127
USE `kcson`;
DROP procedure IF EXISTS `kcson`.`Pivot`;
SHOW WARNINGS;
DELIMITER $$
USE `kcson`$$
CREATE PROCEDURE Pivot(
IN tbl_name VARCHAR(99), -- table name (or db.tbl)
IN base_cols VARCHAR(99), -- column(s) on the left, separated by commas
IN pivot_col VARCHAR(64), -- name of column to put across the top
IN tally_col VARCHAR(64), -- name of column to SUM up
IN where_clause VARCHAR(99), -- empty string or "WHERE ..."
IN order_by VARCHAR(99) -- empty string or "ORDER BY ..."; usually the base_cols
)
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
-- GET the SUM()s
SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ',
' FROM ', tbl_name, ' ', where_clause, ' ORDER BY 1');
SET @cc1 = "CONCAT('SUM(IF(&p = ', &v, ', &t, 0)) AS ', &v)";
SET @cc2 = REPLACE(@cc1, '&p', pivot_col);
SET @cc3 = REPLACE(@cc2, '&t', tally_col);
-- select @cc2, @cc3;
SET @qval = CONCAT("'\"', val, '\"'");
-- select @qval;
SET @cc4 = REPLACE(@cc3, '&v', @qval);
-- select @cc4;
SET SESSION group_concat_max_len = 10000; -- just in case
SET @stmt = CONCAT(
'SELECT GROUP_CONCAT(', @cc4, ' SEPARATOR ",\n") INTO @sums',
' FROM ( ', @subq, ' ) AS top');
select @stmt;
PREPARE _sql FROM @stmt;
EXECUTE _sql; -- 2nd step: build SQL for columns
DEALLOCATE PREPARE _sql;
-- 3rd: Construct the query and perform it
SET @stmt2 = CONCAT(
'SELECT ',
base_cols, ',\n',
@sums,
',\n SUM(', tally_col, ') AS Total'
'\n FROM ', tbl_name, ' ',
where_clause,
' GROUP BY ', base_cols,
'\n WITH ROLLUP',
'\n', order_by
);
select @stmt2; -- The statement that generates the result
PREPARE _sql FROM @stmt2;
EXECUTE _sql; -- The resulting pivot table ouput
DEALLOCATE PREPARE _sql;
-- For debugging / tweaking, SELECT the various @variables after CALLing.
END;$$
DELIMITER ;
SHOW WARNINGS;
-- -----------------------------------------------------
-- procedure CREATE_PIVOT
-- -----------------------------------------------------
USE `kcson`;
DROP procedure IF EXISTS `kcson`.`CREATE_PIVOT`;
SHOW WARNINGS;
DELIMITER $$
USE `kcson`$$
CREATE PROCEDURE CREATE_PIVOT(
IN tbl_qry VARCHAR(2000), -- table name (or db.tbl)
IN base_cols VARCHAR(99), -- column(s) on the left, separated by commas
128
IN pivot_col VARCHAR(64), -- name of column to put across the top
IN tally_col VARCHAR(64), -- name of column to SUM up
IN where_clause VARCHAR(1000), -- empty string or "WHERE ..."
IN order_by VARCHAR(99) -- empty string or "ORDER BY ..."; usually the base_cols
)
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
-- GET the SUM()s
SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ',
' FROM ', tbl_qry, ' ', where_clause, ' ORDER BY 1');
SET @cc1 = "CONCAT('SUM(IF(&p = ', &v, ', &t, 0)) AS ', &v)";
SET @cc2 = REPLACE(@cc1, '&p', pivot_col);
SET @cc3 = REPLACE(@cc2, '&t', tally_col);
-- select @cc2, @cc3;
SET @qval = CONCAT("'\"', val, '\"'");
-- select @qval;
SET @cc4 = REPLACE(@cc3, '&v', @qval);
-- select @cc4;
SET SESSION group_concat_max_len = 10000; -- just in case
SET @stmt = CONCAT(
'SELECT GROUP_CONCAT(', @cc4, ' SEPARATOR ",\n") INTO @sums',
' FROM ( ', @subq, ' ) AS top');
SELECT @stmt;
PREPARE _sql FROM @stmt;
EXECUTE _sql; -- 2nd step: build SQL for columns
DEALLOCATE PREPARE _sql;
-- 3rd: Construct the query and perform it
SET @stmt2 = CONCAT(
'SELECT ',
base_cols, ',\n',
@sums,
',\n SUM(', tally_col, ') AS Total'
'\n FROM ', tbl_qry, ' ',
where_clause,
' GROUP BY ', base_cols,
'\n WITH ROLLUP',
'\n', order_by
);
SELECT @stmt2; -- The statement that generates the result
PREPARE _sql FROM @stmt2;
EXECUTE _sql; -- The resulting pivot table ouput
DEALLOCATE PREPARE _sql;
-- For debugging / tweaking, SELECT the various @variables after CALLing.
END;$$
DELIMITER ;
SHOW WARNINGS;
-- -----------------------------------------------------
-- procedure serv_dep_total
-- -----------------------------------------------------
USE `kcson`;
DROP procedure IF EXISTS `kcson`.`serv_dep_total`;
SHOW WARNINGS;
DELIMITER $$
USE `kcson`$$
CREATE DEFINER=`root`@`localhost` procedure `serv_dep_total`()
DETERMINISTIC
COMMENT ''
BEGIN
call CREATE_PIVOT("
(select serv_tnum, s.id, ufio_id, serv_id , uslnum
from main_NZ m inner join serv s on m.serv_id=s.id
where dep_id = GET_DEP(GET_wID())
) main1",
"ufio_id", "serv_tnum", "uslnum" , "" , "");
129
END;$$
DELIMITER ;
SHOW WARNINGS;
-- -----------------------------------------------------
-- procedure dep_serv_total
-- -----------------------------------------------------
USE `kcson`;
DROP procedure IF EXISTS `kcson`.`dep_serv_total`;
SHOW WARNINGS;
DELIMITER $$
USE `kcson`$$
CREATE DEFINER=`root`@`localhost` procedure `dep_serv_total`()
DETERMINISTIC
COMMENT ''
BEGIN
call CREATE_PIVOT("
(select tnum, s.id, ufio_id, serv_id , uslnum
from main_NZ m inner join serv s on m.serv_id=s.id
where dep_id = GET_DEP(GET_wID())
) main1",
"ufio_id", "tnum", "uslnum" , "" , "");
END;$$
DELIMITER ;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`user_has_serv`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`user_has_serv`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`user_has_serv` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `user_has_serv` AS SELECT * FROM main where (main.cr_by = GET_wID());
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`dep_has_main`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`dep_has_main`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`dep_has_main` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `dep_has_main` AS SELECT * FROM main where (main.dep_id = GET_DEP( GET_wID() ));
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`last_used_workers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`last_used_workers`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`last_used_workers` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `last_used_workers` As
SELECT worker.id, worker.worker
FROM worker inner join main ON worker.id=main.worker_id
where (main.cr_by = GET_wID())
GROUP BY worker.id, worker.worker
ORDER BY worker.worker
LIMIT 30;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`serv_activ`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`serv_activ`;
SHOW WARNINGS;
130
DROP VIEW IF EXISTS `kcson`.`serv_activ` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `serv_activ` AS select * from serv where archive=0 and total=0;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`servOFripso`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`servOFripso`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`servOFripso` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `servOFripso` AS SELECT
s.*, r.id as r_id, r.ripso
FROM ripso r
INNER JOIN serv s
INNER JOIN ripso_has_serv fr
ON s.id = fr.ripso_id
AND fr.ripso_id = r.id
WHERE s.archive = 0
AND r.archive = 0;
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`fioOFdepBYserv`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`fioOFdepBYserv`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`fioOFdepBYserv` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `fioOFdepBYserv` 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`.`servOFyear`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`servOFyear`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`servOFyear` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `servOFyear` AS SELECT * FROM kcson.main m where year(m.vdate) = year(current_date());
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`servOFyear-2`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kcson`.`servOFyear-2`;
SHOW WARNINGS;
DROP VIEW IF EXISTS `kcson`.`servOFyear-2` ;
SHOW WARNINGS;
USE `kcson`;
CREATE OR REPLACE VIEW `servOFyear-2` AS SELECT * FROM kcson.main m where year(m.vdate) = (year(current_date())-
2);
SHOW WARNINGS;
-- -----------------------------------------------------
-- View `kcson`.`servOFyear-3`
Источник: https://baza.diplomsite.ru/previewfile/2412

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

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