1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP TABLE IF EXISTS `travail`
--------------
--------------
CREATE TABLE `travail`
( `mvt_date` date not null,
`reference` integer unsigned not null,
`motif` char(10) not null,
`recette` decimal(15,2) not null,
`depense` decimal(15,2) not null,
`cumul` decimal(15,2) not null
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP PROCEDURE IF EXISTS `cumul`
--------------
--------------
CREATE PROCEDURE `cumul`
( in_date date
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE _mvt_date date;
DECLARE _reference integer;
DECLARE _motif char(10);
DECLARE _recette decimal(15,2);
DECLARE _depense decimal(15,2);
DECLARE _cumul decimal(15,2);
DECLARE _fin INTEGER DEFAULT 1;
DECLARE _curs CURSOR FOR select mvt_date, reference, motif, recette, depense, cumul
from ( select mvt_date, reference, motif, recette, depense, @cum := @cum + recette - depense as cumul,
periode, mvt_id, rang
from ( (select M.mvt_id,
E.cr_id AS Rang,
date_format(M.mvt_date, "%Y-%m") AS Periode,
M.mvt_date,
R.cr_ref AS Reference,
R.cr_motif AS Motif,
R.cr_Montant AS Recette,
0 AS Depense
from carnetrecette as R
inner join entree as E
on E.cr_id = R.cr_id
inner join mouvement as M
on M.mvt_id = E.mvt_id
having periode = date_format(in_date, "%Y-%m")
or periode = date_format(in_date - interval 1 month, "%Y-%m"))
union
(select M.mvt_id,
S.cd_id AS Rang,
date_format(M.mvt_date, "%Y-%m") AS Periode,
M.mvt_date,
D.cd_ref AS Reference,
D.cd_motif AS Motif,
0 AS Recette,
D.cd_Montant AS Depense
from carnetdepense as D
inner join sortie as S
on S.cd_id = D.cd_id
inner join mouvement as M
on M.mvt_id = S.mvt_id
having periode = date_format(in_date, "%Y-%m")
or periode = date_format(in_date - interval 1 month, "%Y-%m"))
order by mvt_id, rang) as t
cross join (select @cum:=0) as x
) as y
where periode = date_format(in_date, "%Y-%m")
order by mvt_id, rang;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
OPEN _curs;
FETCH _curs INTO _mvt_date, _reference, _motif, _recette, _depense, _cumul;
WHILE (_fin)
DO
INSERT INTO `TRAVAIL` (`mvt_date`,`reference`,`motif`,`recette`,`depense`,`cumul`) values (_mvt_date, _reference, _motif, _recette, _depense, _cumul);
FETCH _curs INTO _mvt_date, _reference, _motif, _recette, _depense, _cumul;
END WHILE;
CLOSE _curs;
END
--------------
--------------
call `cumul` ('2017-04-29')
--------------
--------------
select * from travail
--------------
+------------+-----------+-------+-----------+-----------+------------+
| mvt_date | reference | motif | recette | depense | cumul |
+------------+-----------+-------+-----------+-----------+------------+
| 2017-04-18 | 125 | six | 500000.00 | 0.00 | 600000.00 |
| 2017-04-18 | 125 | six | 500000.00 | 0.00 | 1100000.00 |
| 2017-04-24 | 333 | sept | 0.00 | 300000.00 | 800000.00 |
| 2017-04-24 | 555 | huit | 100000.00 | 0.00 | 900000.00 |
| 2017-04-24 | 66 | neuf | 0.00 | 400000.00 | 500000.00 |
| 2017-04-24 | 999 | dix | 0.00 | 200000.00 | 300000.00 |
+------------+-----------+-------+-----------+-----------+------------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager