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`;
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`