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
|
CREATE PROCEDURE Gen_Relever
(
@Gerer int
)
AS
DECLARE @CodeUsager varchar(10)
DECLARE @VolumePrelever Float
DECLARE @PrevIndex Float
DECLARE @Coeff Float
DECLARE @UnitPrice Money
DECLARE @PresenceCompteur INTEGER
DECLARE @EtatCompteur INTEGER
DECLARE @Id_Trimestre INTEGER
DECLARE @TypeReleve char(1)
DECLARE @NumCompteur varchar(20)
DECLARE @CodeTypeUsager varchar(5)
SELECT @Id_Trimestre=id_trimestre FROM table_periode_rel where current_trim=1
BEGIN TRAN
DECLARE curGenReleve CURSOR FOR
SELECT table_usager.code_usager,
table_usager.code_type_usager,
table_usager.volume_prel_forf,table_usager.presence_compteur,
table_type_usager.pu_ht FROM table_usager LEFT OUTER JOIN table_type_usager
ON (table_usager.code_type_usager=table_type_usager.code_type_usager)
WHERE (table_usager.usager_inactif=@Gerer)
OPEN curGenReleve
FETCH NEXT FROM curGenReleve INTO @CodeUsager,@VolumePrelever,@PresenceCompteur,@UnitPrice,@CodeTypeUsager
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @EtatCompteur=compteur_activer,@PrevIndex=dernier_index,@Coeff=coefficient,
@NumCompteur=num_compteur,@CodeTypeUsager=code_type_usager
FROM table_compteur WHERE code_usager=@CodeUsager
IF (@EtatCompteur)=1
SET @TypeReleve='C'
ELSE
BEGIN
SET @TypeReleve='F'
SET @VolumePrelever =(@VolumePrelever/4)
END
INSERT INTO dbo.table_releve
(code_usager,
num_compteur,
prev_index,
nouvel_index,
volume_prelev,
type_prelev,
date_releve,
pu_ht,
code_type_usager,
id_fact,
num_fact,
coefficient,
calculer,
facturer,
id_trimestre)
VALUES
(@CodeUsager,
@NumCompteur,
@PrevIndex,
null,
@VolumePrelever,
@TypeReleve,
null,
@UnitPrice,
@CodeUsager,
null,
null,
@Coeff,
null,
null,
@Id_Trimestre
)
FETCH NEXT FROM curGenReleve INTO @CodeUsager,@VolumePrelever,@PresenceCompteur,@UnitPrice,@CodeTypeUsager
END
CLOSE curGenReleve
DEALLOCATE curGenReleve
COMMIT TRAN |
Partager