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
| ALTER PROCEDURE [dbo].[BILAN_IGP_SEND_MAIL]
-- Add the parameters for the stored procedure here
@mail varchar(250)
AS
BEGIN
-- Insert statements for procedure here
DECLARE @MAIL_BODY NVARCHAR(max),
@BI_COD_ART nvarchar(25),
@BI_ART_LIB nvarchar(255),
@BI_LOT int,
@BI_PDS_ARR float,
@BI_NBU_ARR int,
@BI_PDS_DEM float,
@BI_NBU_DEM int,
@BI_ECART_PDS float,
@BI_ECART_NBU int
DECLARE c1 CURSOR FOR
select BI_COD_ART,BI_ART_LIB,BI_LOT,BI_PDS_ARR,BI_NBU_ARR,BI_PDS_DEM,BI_NBU_DEM,
BI_ECART_PDS,BI_ECART_NBU
from GCBILAN_IGP;
OPEN c1
FETCH NEXT FROM c1
INTO @BI_COD_ART,@BI_ART_LIB,@BI_LOT,@BI_PDS_ARR,@BI_NBU_ARR,@BI_PDS_DEM,@BI_NBU_DEM,@BI_ECART_PDS,@BI_ECART_NBU
WHILE @@FETCH_STATUS = 0
BEGIN
/* HEADER */
--SET @MAIL_BODY = 'sfqfff';
SET @MAIL_BODY = '<table border="1" align="center" cellpadding="2" cellspacing="0" style="color:black;font-family:consolas;text-align:center;">' +
'<tr>
<th>CODE ARTICLE</th>
<th>LIBELLE</th>
<th>LOT</th>
<th>POIDS ARRIVAGES</th>
<th>UNITES ARRIVAGES</th>
<th>POIDS DEMOULE</th>
<th>UNITES DEMOULE</th>
<th>ECART POIDS</th>
<th>ECART UNITES</th>
</tr>'
/* ROWS */
SELECT
@MAIL_BODY = @MAIL_BODY +
'<tr>' +
'<td> ' + BI_COD_ART + ' </td>' +
'<td> ' + BI_ART_LIB + ' </td>' +
'<td> ' + CONVERT(varchar,BI_LOT) + ' </td>' +
'<td> ' + CONVERT(varchar,BI_PDS_ARR) + ' </td>' +
'<td> ' + CONVERT(varchar,BI_NBU_ARR) + ' </td>' +
'<td> ' + CONVERT(varchar,BI_PDS_DEM) + ' </td>' +
'<td> ' + CONVERT(varchar,BI_NBU_DEM) + ' </td>' +
'<td> ' + CONVERT(varchar,BI_ECART_PDS) + ' </td>' +
'<td> ' + CONVERT(varchar,BI_ECART_NBU) + ' </td>' +
'</tr>'
FROM GCBILAN_IGP
order by BI_ART_LIB;
SELECT @MAIL_BODY = @MAIL_BODY + '</table>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients=@mail,
@subject = '[BILAN IGP] Démoulage IGP',
@body = @MAIL_BODY,
@body_format='HTML';
FETCH NEXT FROM c1
INTO @BI_COD_ART,@BI_ART_LIB,@BI_LOT,@BI_PDS_ARR,@BI_NBU_ARR,@BI_PDS_DEM,@BI_NBU_DEM,@BI_ECART_PDS,@BI_ECART_NBU
END
CLOSE c1
DEALLOCATE c1
END |
