· 5 years ago · Nov 19, 2020, 07:14 PM
1SET FOREIGN_KEY_CHECKS=0;
2
3CREATE TABLE `tfape`.`alerta_email` (
4 `id` int(11) NOT NULL AUTO_INCREMENT,
5 `usuarioid` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
6 PRIMARY KEY (`id`) USING BTREE
7) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
8
9CREATE TABLE `tfape`.`contribuinte_historico` (
10 `id` int(11) NOT NULL AUTO_INCREMENT,
11 `codigo_contribuinte` int(11) NOT NULL COMMENT 'FK para a tabela de contribuinte',
12 `ano` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
13 `trimestre` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
14 `renda_bruta` double(17, 2) NOT NULL,
15 `codigo_atividade` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'FK para a tabela de atividade',
16 `comprovante` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'caminho do arquivo',
17 `data_de_atualizacao` datetime NOT NULL,
18 `responsavel_pela_atualizacao` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'nome completo de quem fez a alteração.',
19 `origem_da_alteracao` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '1' COMMENT '1 = contribuinte; 2 = servidor CPRH',
20 PRIMARY KEY (`id`) USING BTREE
21) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
22
23CREATE TABLE `tfape`.`contribuinte_liberacao_rba` (
24 `id` int(11) NOT NULL AUTO_INCREMENT,
25 `codigo_contribuinte` int(11) NOT NULL DEFAULT 0,
26 `ano` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
27 `responsavel_pela_atualizacao` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
28 `liberar` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '1' COMMENT '0 = não; 1 = sim.',
29 PRIMARY KEY (`id`) USING BTREE
30) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
31
32CREATE TABLE `tfape`.`sec_apps` (
33 `app_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
34 `app_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
35 `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
36 PRIMARY KEY (`app_name`) USING BTREE
37) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
38
39CREATE TABLE `tfape`.`sec_groups` (
40 `group_id` int(11) NOT NULL AUTO_INCREMENT,
41 `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
42 PRIMARY KEY (`group_id`) USING BTREE,
43 UNIQUE INDEX `description`(`description`) USING BTREE
44) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
45
46CREATE TABLE `tfape`.`sec_groups_apps` (
47 `group_id` int(11) NOT NULL,
48 `app_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `priv_access` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
50 `priv_insert` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
51 `priv_delete` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
52 `priv_update` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
53 `priv_export` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
54 `priv_print` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
55 PRIMARY KEY (`group_id`, `app_name`) USING BTREE,
56 INDEX `sec_groups_apps_ibfk_2`(`app_name`) USING BTREE,
57 CONSTRAINT `sec_groups_apps_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `tfape`.`sec_groups` (`group_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
58 CONSTRAINT `sec_groups_apps_ibfk_2` FOREIGN KEY (`app_name`) REFERENCES `tfape`.`sec_apps` (`app_name`) ON DELETE CASCADE ON UPDATE RESTRICT
59) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
60
61CREATE TABLE `tfape`.`sec_logged` (
62 `login` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
63 `date_login` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
64 `sc_session` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
65 `ip` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
66) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
67
68CREATE TABLE `tfape`.`sec_users` (
69 `login` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
70 `pswd` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
71 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
72 `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
73 `active` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
74 `activation_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
75 `priv_admin` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
76 `primeiro_acesso` int(11) NULL DEFAULT 0,
77 PRIMARY KEY (`login`) USING BTREE
78) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
79
80CREATE TABLE `tfape`.`sec_users_groups` (
81 `login` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
82 `group_id` int(11) NOT NULL,
83 PRIMARY KEY (`login`, `group_id`) USING BTREE,
84 INDEX `sec_users_groups_ibfk_2`(`group_id`) USING BTREE,
85 CONSTRAINT `sec_users_groups_ibfk_1` FOREIGN KEY (`login`) REFERENCES `tfape`.`sec_users` (`login`) ON DELETE CASCADE ON UPDATE RESTRICT,
86 CONSTRAINT `sec_users_groups_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `tfape`.`sec_groups` (`group_id`) ON DELETE CASCADE ON UPDATE RESTRICT
87) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
88
89ALTER TABLE `tfape`.`seg_usuarios` MODIFY COLUMN `usuarioid` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL FIRST;
90
91ALTER TABLE `tfape`.`temp_anos` MODIFY COLUMN `id` int(11) NOT NULL FIRST;
92
93ALTER TABLE `tfape`.`temp_anos` MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT FIRST;
94
95ALTER TABLE `tfape`.`tfape_conciliacao_falha` MODIFY COLUMN `data_vencimento` datetime NOT NULL AFTER `valor_pago`;
96
97ALTER TABLE `tfape`.`tfape_conciliacao_falha` MODIFY COLUMN `data_pagamento` datetime NOT NULL AFTER `data_vencimento`;
98
99CREATE TABLE `tfape`.`tfape_configuracao` (
100 `id` int(11) NOT NULL AUTO_INCREMENT,
101 `banco` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
102 `agencia` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
103 `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
104 `url_externo` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
105 PRIMARY KEY (`id`) USING BTREE
106) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
107
108CREATE TABLE `tfape`.`webservice_log` (
109 `id_webservice_log` int(11) NOT NULL,
110 `id_webservice_sistema` smallint(6) NOT NULL,
111 `endereco_ip_requisitante` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
112 `CodigoMunicipio` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
113 `mensagem` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
114 `datahora` datetime NOT NULL,
115 `usuario` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
116 PRIMARY KEY (`id_webservice_log`) USING BTREE,
117 INDEX `FK_webservicelog_webservicesistema_idx`(`id_webservice_sistema`) USING BTREE,
118 INDEX `FK_webservicelog_segusuarios_idx`(`usuario`) USING BTREE,
119 CONSTRAINT `FK_webservicelog_segusuarios` FOREIGN KEY (`usuario`) REFERENCES `tfape`.`seg_usuarios` (`usuarioid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
120 CONSTRAINT `FK_webservicelog_webservicesistema` FOREIGN KEY (`id_webservice_sistema`) REFERENCES `tfape`.`webservice_sistema` (`id_webservice_sistema`) ON DELETE NO ACTION ON UPDATE NO ACTION
121) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci COMMENT = 'Registro todos as consultas solicitadas ao webservice do TFA' ROW_FORMAT = Compact;
122
123CREATE TABLE `tfape`.`webservice_sistema` (
124 `id_webservice_sistema` smallint(6) NOT NULL,
125 `sistema` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
126 PRIMARY KEY (`id_webservice_sistema`) USING BTREE
127) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
128
129CREATE DEFINER = `root`@`%` PROCEDURE `tfape`.`cTempAnoDiaUtil`(IN `diaUtil` int)
130BEGIN
131
132set @yearNow = year(now());
133set @y = 2008;
134drop TEMPORARY table if exists _tempAnoDiaUtil;
135create temporary table _tempAnoDiaUtil (ano int, dia date);
136while (@y <= @yearNow) do
137 insert into _tempAnoDiaUtil values (@y, diaUtil(concat(@y, '01-01'),diaUtil));
138 set @y = @y + 1;
139end while;
140
141END;
142
143CREATE DEFINER = `root`@`%` FUNCTION `tfape`.`quintoDiaUtil`(ano int)
144 RETURNS date
145BEGIN
146 set @dataQ :=
147 (SELECT
148 dia
149FROM
150 (
151 SELECT
152 concat(ano, '-01-02') + INTERVAL @a DAY dia,
153
154 IF (
155 date_format(
156 (concat(ano,'-01-02') + INTERVAL @a DAY),
157 '%w'
158 ) BETWEEN 1
159 AND 5,
160 @c :=@c + 1,
161 NULL
162 ) dia_util,
163 @a := @a + 1
164 FROM
165 (SELECT @a := 0) b,
166 (SELECT @c := 0) c,
167 information_schema.character_sets
168 ) z
169WHERE
170 dia_util = 5);
171
172 RETURN @dataQ;
173END;
174
175SET FOREIGN_KEY_CHECKS=1;