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
|
USE LIVRET
declare
@codeHTML varchar(max),
@Q_Med varchar(max),
@commande varchar(8000),
@Prod varchar(50), @catc varchar(50), @dci varchar(50),
@Id integer, @Idol integer, @rang integer, @nb integer;
set @codeHTML = '<html><body><table border=1>';
set @codeHTML = concat(@codeHTML, '<tr><td COLSPAN="4" align="center"><b> LIVRET DU MEDICAMENT DANS EASILY AU : ' + format(getdate(), 'dd/MM/yyyy') + '</b></td></tr><tr> </tr>');
set @codeHTML = concat(@codeHTML, '<tr><td>Produit</td><td>Code catc</td><td>Dénominations Communes Internationales</td><td>Rang</td></tr>');
declare Cur_Li cursor for
select distinct p.pdt_id, p.pdt_libelle, m.catc_code, d2.dc_libelle, d1.dcm_rang_dc
from livret.Livret.PRODUIT p
inner join livret.livret.MEDICAMENT m on m.mdc_id = p.pdt_id
inner join livret.livret.DENOMINATION_COMMUNE_MEDICAMENT d1 on d1.mdc_id = m.mdc_id
inner join livret.livret.DENOMINATION_COMMUNE d2 on d2.dc_id = d1.dc_id
inner join livret.livret.ACT_PDT_UF u on u.pdt_id = p.pdt_id
inner join livret.livret.livret l on l.pdt_id = p.pdt_id
where u.uf_code like 'CHA%'
and l.liv_dte_retrait is null
and p.pdt_synchroTheriaque = 1
order by p.pdt_id, p.pdt_libelle, d1.dcm_rang_dc
open Cur_Li
fetch Cur_Li into
@ID,
@Prod,
@catc,
@dci,
@rang
set @Idol = '0'
While @@FETCH_STATUS = 0
Begin
if @Id <> @Idol
BEGIN
select @nb = count(*)
from livret.livret.DENOMINATION_COMMUNE_MEDICAMENT d
where d.mdc_id = @Id;
set @codeHTML = concat(@codeHTML, '<tr><td rowspan="' + cast(@nb as varchar(2)) + '">' + @prod + '</td><td rowspan="' + cast(@nb as varchar(2)) + '">' + @catc + '</td><td>' + @dci + '</td><td>' + cast(@rang as varchar(2)) + '</td></tr>');
END
else
set @codeHTML = concat(@codeHTML, '<tr><td>' + @dci + '</td><td>' + cast(@rang as varchar(2)) + '</td></tr>');
set @Idol = @Id
FETCH NEXT FROM Cur_Li INTO
@ID,
@Prod,
@catc,
@dci,
@rang
End
CLOSE Cur_Li
deallocate Cur_Li
set @codeHTML = concat(@codeHTML, '</table></body></html>');
set @commande = 'echo ' + @codeHTML + ' > D:\NLS\LivretCHA\livret.txt'
exec xp_cmdshell @commande; |
