· 6 years ago · Jun 23, 2019, 08:32 PM
1CREATE TABLE IF NOT EXISTS `lc_movimento` (
2 `id` int(11) NOT NULL AUTO_INCREMENT,
3 `pago` int(11) DEFAULT NULL,
4 `dia` int(11) DEFAULT NULL,
5 `mes` int(11) DEFAULT NULL,
6 `ano` int(11) DEFAULT NULL,
7 `cat` int(11) DEFAULT NULL,
8 `idemp` int(11) DEFAULT NULL,
9 `usu_login` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
10 `descricao` longtext COLLATE latin1_general_ci,
11 `obs` longtext COLLATE latin1_general_ci,
12 `debito` double DEFAULT NULL,
13 `credito` double NOT NULL,
14 `dc` varchar(2) COLLATE latin1_general_ci NOT NULL,
15 PRIMARY KEY (`id`),
16 KEY `cat` (`cat`)
17) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3906 ;
18
19INSERT INTO `lc_movimento` (`id`, `pago`, `dia`, `mes`, `ano`, `cat`, `idemp`, `usu_login`, `descricao`, `obs`, `debito`, `credito`, `dc`) VALUES
20(41, 1, 11, 8, 2014, 149, 22, 'Rafael', 'SILVA MATTOS 23340-4/4', '', 10, 0, 'D'),
21(39, 1, 11, 6, 2014, 149, 22, 'Rafael', 'SILVA MATTOS 23340-2/4', '', 20, 0, 'D'),
22(40, 1, 10, 7, 2014, 149, 22, 'Adriano', 'SILVA MATTOS 23340-3/4', '', 30, 0, 'D'),
23(33, 1, 16, 5, 2014, 149, 16, 'Adriano', 'WSUL 146511-1/5', '', 0, 1000, 'C'),
24(34, 1, 16, 6, 2014, 149, 16, 'Adriano', 'WSUL 146511-2/5', '', 500, 0, 'D'),
25(35, 1, 14, 7, 2014, 149, 16, 'Adriano', 'WSUL 146511-3/5', '', 500, 0, 'D'),
26(36, 1, 13, 8, 2014, 149, 16, 'Adriano', 'WSUL 146511-4/5', '', 0, 200, 'C'),
27(37, 1, 12, 9, 2014, 149, 16, 'Adriano', 'WSUL 146511-5/5', '', 300, 0, 'D'),
28(38, 1, 12, 5, 2014, 149, 22, 'Adriano', 'SILVA MATTOS 23340-1/4', '', 0, 800, 'C'),
29(28, 1, 2, 5, 2014, 149, 22, 'Adriano', 'MGNETRON 21629-3/3', '', 0, 700, 'C'),
30(29, 1, 2, 5, 2014, 149, 22, 'Adriano', 'DELTACAPAS 15092-2/2', '', 100, 0, 'D'),
31(30, 1, 14, 5, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-1/3', '', 600, 0, 'D'),
32(31, 1, 29, 5, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-2/3', '', 50, 0, 'D'),
33(32, 1, 13, 6, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-3/3', '', 0, 60, 'C'),
34(42, 1, 2, 5, 2014, 149, 22, 'Adriano', 'RTO 3268-3/3', '', 20, 0, 'D'),
35(43, 1, 2, 5, 2014, 149, 22, 'Adriano', 'ROUTE - ENERBRAX 21410-2/2', '', 100, 0, 'D'),
36(44, 1, 2, 5, 2015, 149, 22, 'Adriano', 'SILVA MATTOS 23180-1/4', '', 0, 150, 'C'),
37(45, 1, 2, 5, 2015, 149, 22, 'Adriano', 'WGK 16339-1/3', '', 100, 0, 'D'),
38(46, 1, 2, 5, 2015, 149, 16, 'Adriano', 'ROYALCICLO 35755-1', '', 200, 0, 'D'),
39(47, 1, 2, 5, 2015, 149, 22, 'Adriano', 'CAR CENTRAL 42755-1/1', '', 0, 500, 'C');
40
41CREATE VIEW vw_extrato
42AS SELECT idemp, concat(lc_movimento.ano,'/',lc_movimento.mes,'/',lc_movimento.dia) as data,
43lc_movimento.debito+lc_movimento.credito as valor,
44lc_movimento.dc AS tipo
45FROM lc_movimento;
46
47SELECT idemp, DATE_FORMAT(data,'%d/%m/%Y') AS data,
48 SUM(IF(tipo = 'D', valor, 0)) AS debito,
49 SUM(IF(tipo = 'C', valor, 0)) AS credito,
50 (SELECT SUM(IF(tipo = 'C', valor, -valor)) FROM vw_extrato AS L2
51 WHERE DATE_FORMAT(vw_extrato.data,'%Y%m') >= DATE_FORMAT(L2.data,'%Y%m')
52 ) AS saldo
53FROM vw_extrato
54GROUP BY MONTH(data), YEAR(data) ORDER BY YEAR(data), MONTH(data)
55
56CREATE TABLE IF NOT EXISTS `lc_movimento` (
57 `id` int(11) NOT NULL AUTO_INCREMENT,
58 `pago` int(11) DEFAULT NULL,
59 `dia` int(11) DEFAULT NULL,
60 `mes` int(11) DEFAULT NULL,
61 `ano` int(11) DEFAULT NULL,
62 `cat` int(11) DEFAULT NULL,
63 `idemp` int(11) DEFAULT NULL,
64 `usu_login` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
65 `descricao` longtext COLLATE latin1_general_ci,
66 `obs` longtext COLLATE latin1_general_ci,
67 `debito` double DEFAULT NULL,
68 `credito` double NOT NULL,
69 `dc` varchar(2) COLLATE latin1_general_ci NOT NULL,
70 PRIMARY KEY (`id`),
71 KEY `cat` (`cat`)
72) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3906 ;
73
74INSERT INTO `lc_movimento` (`id`, `pago`, `dia`, `mes`, `ano`, `cat`, `idemp`, `usu_login`, `descricao`, `obs`, `debito`, `credito`, `dc`) VALUES
75(41, 1, 11, 8, 2014, 149, 22, 'Rafael', 'SILVA MATTOS 23340-4/4', '', 10, 0, 'D'),
76(39, 1, 11, 6, 2014, 149, 22, 'Rafael', 'SILVA MATTOS 23340-2/4', '', 20, 0, 'D'),
77(40, 1, 10, 7, 2014, 149, 22, 'Adriano', 'SILVA MATTOS 23340-3/4', '', 30, 0, 'D'),
78(33, 1, 16, 5, 2014, 149, 16, 'Adriano', 'WSUL 146511-1/5', '', 0, 1000, 'C'),
79(34, 1, 16, 6, 2014, 149, 16, 'Adriano', 'WSUL 146511-2/5', '', 500, 0, 'D'),
80(35, 1, 14, 7, 2014, 149, 16, 'Adriano', 'WSUL 146511-3/5', '', 500, 0, 'D'),
81(36, 1, 13, 8, 2014, 149, 16, 'Adriano', 'WSUL 146511-4/5', '', 0, 200, 'C'),
82(37, 1, 12, 9, 2014, 149, 16, 'Adriano', 'WSUL 146511-5/5', '', 300, 0, 'D'),
83(38, 1, 12, 5, 2014, 149, 22, 'Adriano', 'SILVA MATTOS 23340-1/4', '', 0, 800, 'C'),
84(28, 1, 2, 5, 2014, 149, 22, 'Adriano', 'MGNETRON 21629-3/3', '', 0, 700, 'C'),
85(29, 1, 2, 5, 2014, 149, 22, 'Adriano', 'DELTACAPAS 15092-2/2', '', 100, 0, 'D'),
86(30, 1, 14, 5, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-1/3', '', 600, 0, 'D'),
87(31, 1, 29, 5, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-2/3', '', 50, 0, 'D'),
88(32, 1, 13, 6, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-3/3', '', 0, 60, 'C'),
89(42, 1, 2, 5, 2014, 149, 22, 'Adriano', 'RTO 3268-3/3', '', 20, 0, 'D'),
90(43, 1, 2, 5, 2014, 149, 22, 'Adriano', 'ROUTE - ENERBRAX 21410-2/2', '', 100, 0, 'D'),
91(44, 1, 2, 5, 2015, 149, 22, 'Adriano', 'SILVA MATTOS 23180-1/4', '', 0, 150, 'C'),
92(45, 1, 2, 5, 2015, 149, 22, 'Adriano', 'WGK 16339-1/3', '', 100, 0, 'D'),
93(46, 1, 2, 5, 2015, 149, 16, 'Adriano', 'ROYALCICLO 35755-1', '', 200, 0, 'D'),
94(47, 1, 2, 5, 2015, 149, 22, 'Adriano', 'CAR CENTRAL 42755-1/1', '', 0, 500, 'C');
95
96CREATE VIEW vw_extrato
97AS SELECT idemp, concat(lc_movimento.ano,'/',lc_movimento.mes,'/',lc_movimento.dia) as data,
98lc_movimento.debito+lc_movimento.credito as valor,
99lc_movimento.dc AS tipo
100FROM lc_movimento;
101
102
103SELECT idemp, DATE_FORMAT(data,'%d/%m/%Y') AS data,
104 SUM(IF(tipo = 'D', valor, 0)) AS debito,
105 SUM(IF(tipo = 'C', valor, 0)) AS credito,
106 (SELECT SUM(IF(tipo = 'C', valor, -valor)) FROM vw_extrato AS L2
107 WHERE DATE_FORMAT(vw_extrato.data,'%Y%m') >= DATE_FORMAT(L2.data,'%Y%m')
108 ) AS saldo
109FROM vw_extrato WHERE idemp = '16'
110GROUP BY idemp, MONTH(data), YEAR(data) ORDER BY YEAR(data), MONTH(data)
111
112SELECT idemp, DATE_FORMAT(data,'%d/%m/%Y') AS data,
113SUM(IF(tipo = 'D', valor, 0)) AS debito,
114SUM(IF(tipo = 'C', valor, 0)) AS credito,
115(SELECT SUM(IF(tipo = 'C', valor, -valor)) FROM vw_extrato AS L2
116 WHERE DATE_FORMAT(vw_extrato.data,'%Y%m') >=
117 DATE_FORMAT(L2.data,'%Y%m') and
118 idemp = vw_extrato.idemp) AS saldo
119FROM vw_extrato WHERE idemp = '16'
120GROUP BY idemp, MONTH(data), YEAR(data) ORDER BY YEAR(data), MONTH(data);
121
122+-------+------------+--------+---------+-------+
123| idemp | data | debito | credito | saldo |
124+-------+------------+--------+---------+-------+
125| 16 | 16/05/2014 | 0 | 1000 | 1000 |
126| 16 | 16/06/2014 | 500 | 0 | 500 |
127| 16 | 14/07/2014 | 500 | 0 | 0 |
128| 16 | 13/08/2014 | 0 | 200 | 200 |
129| 16 | 12/09/2014 | 300 | 0 | -100 |
130| 16 | 02/05/2015 | 200 | 0 | -300 |
131+-------+------------+--------+---------+-------+
1326 rows in set (0.00 sec)
133
134SELECT idemp, DATE_FORMAT(data,'%d/%m/%Y') AS data,
135SUM(IF(tipo = 'D', valor, 0)) AS debito,
136SUM(IF(tipo = 'C', valor, 0)) AS credito,
137(SELECT SUM(IF(tipo = 'C', valor, -valor)) FROM vw_extrato AS L2
138 WHERE DATE_FORMAT(vw_extrato.data,'%Y%m') >=
139 DATE_FORMAT(L2.data,'%Y%m') and
140 idemp = vw_extrato.idemp) AS saldo
141FROM vw_extrato WHERE idemp = '16'
142GROUP BY idemp, MONTH(data), YEAR(data) ORDER BY YEAR(data), MONTH(data);
143
144+-------+------------+--------+---------+-------+
145| idemp | data | debito | credito | saldo |
146+-------+------------+--------+---------+-------+
147| 16 | 16/05/2014 | 0 | 1000 | 1000 |
148| 16 | 16/06/2014 | 500 | 0 | 500 |
149| 16 | 14/07/2014 | 500 | 0 | 0 |
150| 16 | 13/08/2014 | 0 | 200 | 200 |
151| 16 | 12/09/2014 | 300 | 0 | -100 |
152| 16 | 02/05/2015 | 200 | 0 | -300 |
153+-------+------------+--------+---------+-------+
1546 rows in set (0.00 sec)