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; |
Partager