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
| if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[action_ensemble]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[action_ensemble]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE action_ensemble
AS
DECLARE @machine char(5)
DECLARE @tache char(100)
DECLARE @thisdate datetime
DECLARE @date_dep datetime
DECLARE @date_fin datetime
DECLARE @temp char(10)
DECLARE @secteur char(25)
DECLARE @period int
DECLARE @sql char(255)
DECLARE C_tache_machine SCROLL CURSOR FOR
SELECT nom_machine,nom_tache FROM t_tache_machine WHERE (t_tache_machine.periode_tache <> 9999 AND t_tache_machine.periode_tache <> 999 ) ORDER by nom_machine
OPEN C_tache_machine
FETCH NEXT FROM C_tache_machine INTO @machine,@tache
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE c_date SCROLL CURSOR FOR
SELECT Date_prev FROM T_data WHERE nom_machine =RTRIM(@machine) AND nom_tache= RTRIM(@tache) AND date_prev=(select max(date_prev) FROM T_data WHERE nom_machine = + RTRIM(@machine) AND nom_tache= RTRIM(@tache) )
DECLARE c_donne SCROLL CURSOR FOR
SELECT nom_machine,secteur_machine,nom_tache,periode_tache FROM T_tache_machine WHERE nom_machine =@machine AND nom_tache=@tache AND (t_tache_machine.periode_tache <> 9999 AND t_tache_machine.periode_tache <> 999 )
OPEN C_date
OPEN C_donne
FETCH FIRST FROM C_date INTO @thisdate
FETCH FIRST FROM C_donne INTO @machine,@secteur,@tache,@period
SELECT @thisdate
IF DATEDIFF(day,@thisdate,GETDATE()) > 31 OR @@FETCH_STATUS <> 0
BEGIN
SELECT @date_dep = DATEADD(day,- DATEPART(weekday,GETDATE()) + 8,GETDATE())
SELECT @date_dep = CONVERT(datetime,CONVERT(Varchar(2),DATEPART(day,@date_dep)) + '/' + CONVERT(Varchar(2),DATEPART(mm,@date_dep)) + '/' + CONVERT(Varchar(4),DATEPART(yy,@date_dep)))
SELECT @date_dep
END
ELSE
BEGIN
SELECT @date_dep = ISNULL(@thisdate,CONVERT(datetime,CONVERT(Varchar(2),DATEPART(day,DATEADD(day,- DATEPART(weekday,GETDATE()) + 8,GETDATE()))) + '/' + CONVERT(Varchar(2),DATEPART(mm,DATEADD(day,- DATEPART(weekday,GETDATE()) + 8,GETDATE()))) + '/' + CONVERT(Varchar(4),DATEPART(yy,DATEADD(day,- DATEPART(weekday,GETDATE()) + 8,GETDATE())))))
END
SELECT @date_fin=DATEADD(day,- DATEPART(weekday,GETDATE()) + 14,GETDATE())
WHILE (SELECT @date_dep )< @Date_fin
BEGIN
IF (SELECT DATEPART(dw,@date_dep))<6
BEGIN
INSERT T_data (nom_machine,secteur_machine,nom_tache,date_prev,periodicite_tache)
VALUES (@machine,@secteur,@tache,@date_dep,@period)
END
SELECT @date_dep = DATEADD(hh,@period * 4 ,@date_dep)
END
DEALLOCATE C_donne
DEALLOCATE C_date
FETCH NEXT FROM C_tache_machine INTO @machine,@tache
END
DEALLOCATE C_tache_machine
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO |
Partager