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
| CREATE PROCEDURE `maj_suivi_activite` (idElement varchar(100) )
BEGIN
DECLARE idGestionnaire varchar(100);
DECLARE moisAction varchar(4);
DECLARE moisAnneeAction varchar(10);
DECLARE nbActions varchar(100);
DECLARE colonneToUpdate varchar(100);
/*recherche gestionnaire, ann?e et mois de l'action*/
select id_gestionnaire, date_format(datedebut,'%m'), date_format(datedebut,'%Y-%m') from actions
where id_element=idElement
into idGestionnaire,moisAction,moisAnneeAction;
/*D?termination du nb d'actions*/
select count(act2.id_element) from actions act2, element elem
where act2.id_gestionnaire=idGestionnaire
and date_format(act2.datedebut,'%Y-%m')=moisAnneeAction
and act2.REALISE=1
and elem.id_element=act2.id_element
and elem.asupprimer=0
into nbActions;
/*D?termination de la colonne ? updater*/
case moisAction
when '01' then set colonneToUpdate:='C810JANVIER';
when '02' then set colonneToUpdate:='C811FVRIER';
when '03' then set colonneToUpdate:='C812MARS';
when '04' then set colonneToUpdate:='C814AVRIL';
when '05' then set colonneToUpdate:='C815MAI';
when '06' then set colonneToUpdate:='C816JUIN';
when '07' or '08' then set colonneToUpdate:='C818JUILLET';
when '09' then set colonneToUpdate:='C820SEPTEMBRE';
when '10' then set colonneToUpdate:='C822OCTOBRE';
when '11' then set colonneToUpdate:='C823NOVEMBRE';
when '12' then set colonneToUpdate:='C824DCEMBRE';
end case;
/*preparation de la requête d'update*/
PREPARE stmt FROM "update c800suiviactivit activite , actions act, c705objectifs obj,lienelement lien, c458equipecommercial eqCo
set ? =?
where
act.id_element=?
and eqCo.C467IDENTIFIANTUTILI=act.id_gestionnaire
and activite.id_elementdefaut=eqCo.id_element and activite.C807LIBELL like '2-Consolidation%'
and lien.id_element1=activite.id_element and lien.numrelation='800.705'
and obj.id_element=lien.id_element2 and obj.C714ANNE=date_format(act.datedebut,'%Y');";
SET @a=colonneToUpdate, @b=nbActions, @c=idElement;
EXECUTE stmt USING @a, @b, @c;
DEALLOCATE PREPARE stmt;
END; |
Partager