SQLize | PHPize | SQLtest

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_WH_ALERTA_PLACAS_MANAUS_SendMail] AS begin --If(OBJECT_ID('WH_PCBs_RECEBE_INSP_Email') Is Not Null) IF object_id('tempdb..#body') is not null Begin Drop Table #body End declare @recipients varchar(max); declare @subject varchar(100); declare @from_address varchar(100); declare @body varchar(max); DECLARE @year varchar(10) = (SELECT year(getdate())) --Carrega o Ano. create table #body(id varchar(200), NF varchar(50), ITEM varchar(50), DESCRICAO varchar(200) , DATA_HORA varchar(50) , ORDEMCOMPRA varchar(50) , QTD_NFE varchar(50) , QTD_CONF varchar(50)) --BaaN VNC insert into #body SELECT NEWID() New_ID, * FROM OPENQUERY ([BAAN], ' SELECT DISTINCT pur137.t$docn$o AS NF, J.t$item AS ITEM, J.T$DSCA AS DESCRICAO, TO_CHAR(pur137.t$dtpo$o,''YYYY/MM/DD'') ||'' ''||TO_CHAR(FROM_TZ(CAST(pur137.t$dtpo$o + NUMTODSINTERVAL(pur137.t$hrpo$o, ''second'') as TIMESTAMP), ''America/Los_Angeles'') AT TIME ZONE ''America/Sao_paulo'',''HH24:MI:SS'') AS DATA_HORA, NVL(d.t$norc$o, e.T$orno$o) AS ORDEMCOMPRA, NVL(d.t$qtnf$o, e.t$qtde$o) AS QTD_NFE, NVL(d.t$qtco$o, 0) AS QTD_CONF FROM baan.tgdpur128361 pur128, baan.tgdpur137361 pur137 LEFT JOIN baan.tgdprc001361 v ON v.t$cpor$o = pur137.T$rpor$o AND v.t$ndoc$o = pur137.t$docn$o AND v.t$seri$o = pur137.t$seri$o AND v.t$cgcc$o = pur137.t$cgcc$o AND v.t$cgce$o = pur137.t$cgce$o LEFT JOIN baan.tgdprc002361 d ON d.t$cpor$o = pur137.T$rpor$o AND d.t$ndoc$o = pur137.t$docn$o AND d.t$seri$o = pur137.t$seri$o AND d.t$cgcc$o = pur137.t$cgcc$o AND d.t$cgce$o = pur137.t$cgce$o LEFT JOIN baan.tgdpur142361 e ON e.T$rpor$o = pur137.T$rpor$o AND e.T$rpor$o = pur137.T$rpor$o AND e.T$nupi$o = pur137.T$nupi$o AND e.T$docn$o = pur137.T$docn$o LEFT JOIN baan.ttdpur041361 H ON (H.T$ORNO = e.T$orno$o AND h.t$pono = e.t$pono$o) OR (H.T$ORNO = d.t$norc$o AND h.T$pono = d.t$posi$o) LEFT JOIN baan.ttiitm001361 J ON TRIM(H.T$ITEM) = TRIM(J.T$ITEM) WHERE TO_CHAR(TO_DATE(pur128.t$dtpo$o, ''dd/mm/yyyy''), ''DD/MM/YYYY'') = TO_CHAR(SYSDATE -6, ''DD/MM/YYYY'') AND pur128.t$tpen$o IN (1,3) AND pur137.t$rpor$o = pur128.t$rpor$o AND pur137.t$nupi$o = 0 AND pur137.t$expe$o IN (0,2) AND pur137.t$suno$o = ''N93206'' AND J.t$item LIKE (''MOT-SB%'') ') DECLARE @count int select @count = count(*) from #body if (@count > 0 AND (SELECT DATEPART(weekday, GETDATE())) != '1' AND (SELECT CONVERT(VARCHAR(8),GETDATE(),108)) BETWEEN '00:00:01' AND '23:59:00') -- SE CONTER ITENS, NÃO FOR DOMINGO E FOR 06:15 begin declare @id varchar(200), @NF varchar(50), @ITEM varchar(50), @DESCRICAO varchar(50), @DATA_HORA varchar(50), @ORDEMCOMPRA varchar(50), @QTD_NFE varchar(50), @QTD_CONF varchar(50); declare @html varchar(max) set @html =''+ N'<div align="center">'+ N'<table class="MsoNormalTable" border="0" cellspacing="10" cellpadding="0" width="801" style="width:600.5pt;mso-cellspacing:7.5pt;background:black;mso-yfti-tbllook:'+ N' 1184;mso-padding-alt:7.5pt 7.5pt 7.5pt 7.5pt">'+ N' <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;mso-yfti-lastrow:yes">'+ N' <td width="150" style="width:112.5pt;padding:7.5pt 7.5pt 7.5pt 7.5pt">'+ N' <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:7.5pt;font-family:&quot;Helvetica&quot;,&quot;sans-serif&quot;;mso-fareast-font-family:'+ N' &quot;Times New Roman&quot;;color:black"><img id="_x0000_i1025" src="http://support.flex.com/cdnr/54/acton/attachment/39314/f-e636025b-9246-4c7c-9442-510ca479e953/1/-/-/-/-/logo_40x91.png" border="0"></span></p>'+ N' </td>'+ N' <td width="360" style="width:3.75in;padding:7.5pt 7.5pt 7.5pt 7.5pt">'+ N' <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:'+ N' auto;text-align:right"><span style="font-family:&quot;Arial&quot;,&quot;sans-serif&quot;;'+ N' mso-fareast-font-family:&quot;Times New Roman&quot;;color:white">Alerta de: PLACAS RECEBIDAS DE MANAUS</span></p>'+ N' </td>'+ N' </tr>'+ N'</tbody></table>'+ N'</div>'+ N'<div align="center">'+ N'<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="600" style="width:6.25in;mso-cellspacing:0in;mso-yfti-tbllook:1184;mso-padding-alt:'+ N' 0in 0in 0in 0in">'+ N' <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes">'+ N' <td style="background:white;padding:15.0pt 0in 15.0pt 0in">'+ N'<style type="text/css">'+ N' table.steelBlueCols {'+ N' border: 0px solid #0101DF;'+ N' text-align: center;'+ N' border-collapse: collapse;'+ N' color: #000000;'+ N' width: 95%;'+ N' border: none;'+ N' margin:auto;'+ N' }'+ N' table.steelBlueCols th {border: 1px solid #A8A8A8;padding: 5px 10px;}'+ N' table.steelBlueCols tr {border: 1px solid #A8A8A8;padding: 5px 10px;}'+ N' table.steelBlueCols td {border: 1px solid #A8A8A8;padding: 5px 10px;}'+ N' h2 {text-align: center;}'+ N'</style> '+ N'<div class="container_Report">'+ N' <div class="row">'+ N' <p> </p>'+ N'<table class="steelBlueCols">' + N'<tr bgcolor="#01A9DB"><th>NF</th>'+ N'<th>ITEM</th>'+ N'<th>DESCRICAO</th>'+ N'<th>DATA_HORA</th>'+ N'<th>ORDEMCOMPRA</th>'+ N'<th>QTD_NFE</th>'+ N'<th>QTD_CONF</th>'; WHILE @count > 0 BEGIN select top 1 @id = id, @NF = NF, @ITEM = ITEM, @DESCRICAO = DESCRICAO , @DATA_HORA = DATA_HORA , @ORDEMCOMPRA = ORDEMCOMPRA, @QTD_NFE = QTD_NFE , @QTD_CONF = QTD_CONF from #body set @html =@html+ N'<tr><th>'+ @NF +'</th>'+ N'<th>' +@ITEM+'</th>'+ N'<th>' +@DESCRICAO+'</th>'+ N'<th>' +@DATA_HORA+'</th>'+ N'<th>' +@ORDEMCOMPRA+'</th>'+ N'<th>' +@QTD_NFE+'</th>'+ N'<th>' +@QTD_CONF+'</th>'; DELETE FROM #body WHERE @id = id SELECT @count = count(*) FROM #body END -- set @html = @html+ N'</table>'+ --N' </div>'+ --N' <a href="--http://governance.jag.flextronics.com/PLN_Report/Generic/Sp_Select?query_name=sp_VarrocLeds">Link de Consulta das PCBs.</a>'+ --N' </div>' ; set @html = @html+ N'</table>'+ N' </td>'+ N' </tr>'+ N' <tr style="mso-yfti-irow:1;mso-yfti-lastrow:yes">'+ N' <td style="padding:0in 0in 0in 0in"></td>'+ N' </tr>'+ N' </tbody></table>'+ N' </div>'+ N' </td>'+ N' </tr>'+ N' <tr style="mso-yfti-irow:1;mso-yfti-lastrow:yes">'+ N' <td style="background:#E6E6E6;padding:15.0pt 0in 15.0pt 0in">'+ N' <div align="center">'+ N' <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="500" style="width:375.0pt;mso-cellspacing:0in;mso-yfti-tbllook:1184;mso-padding-alt:'+ N' 0in 0in 0in 0in">'+ N' <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;mso-yfti-lastrow:yes">'+ N' <td style="padding:0in 0in 0in 0in">'+ N' <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:'+ N' auto"><span style="font-size:7.5pt;font-family:&quot;Arial&quot;,&quot;sans-serif&quot;;'+ N' color:gray">Copyright © '+ @year +'. Flex Ltd. All rights reserved. No part of'+ N' this document may be reproduced in any form without the prior written'+ N' permission of Flex. The information contained in this document is intended'+ N' for use and viewing by Flex employees only and may contain confidential,'+ N' trade secret, insider and/or legally privileged information belonging to'+ N' Flex or its clients. Unauthorized disclosure of this content in any form is'+ N' strictly prohibited and may result in disciplinary and/or legal action.</span></p>'+ N' </td>'+ N' </tr>'+ N' </tbody></table>'+ N' </div>'+ N' </td>'+ N' </tr>'+ N'</tbody></table>'+ N'</div>'; select @html --recipients select @recipients = email from ( SELECT distinct COALESCE( (SELECT CAST(email AS VARCHAR(200)) + ';' AS [text()] FROM WH_CADASTRO_EMAIL_WAREHOUSE AS O FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), '') AS email FROM WH_CADASTRO_EMAIL_WAREHOUSE AS C) as t --body EXEC [dbo].[sp_send_mail] @_from_address = 'PLACAS RECEBIDAS DE MANAUS <Placas.Manaus-reply@flex.com>', --@_recipients = @recipients, --N'luciano.clemente@flex.com', @_recipients = N'andre.capeleto@flex.com', @_subject = N'Alerta | RECEBIMENTO PLACAS MANAUS ', @_body= @html end end --EXEC [sp_WH_ALERTA_PLACAS_MANAUS_SendMail] --http://governance.jag.flextronics.com/PLN_Report/Generic/Read?query_name=WH_CAD_SELF_LIFE_Email GO