易飞ERP安全库存预警提示：以HTML表格发送至邮箱

if exists(select *from sysobjects where name='jf_SafetyStock')
drop procedure jf_SafetyStock
go
create procedure jf_SafetyStock
as
begin
if exists(
SELECT MB001,MB002,MB003,CAST((ISNULL(PURTB.TB009,0)+ISNULL(PURTD.TD008,0)+ISNULL(PURTD2.TD008,0)-ISNULL(MOCTA.TA015,0)-ISNULL(MOCTB.TB004,0)-ISNULL(COPTD.TD008,0)+ISNULL(INVMC.MC007,0)) AS INT) AS QTY1,CAST((INVMC2.MC004)AS INT) AS QTY2,((ISNULL(PURTB.TB009,0)+ISNULL(PURTD.TD008,0)+ISNULL(PURTD2.TD008,0)-ISNULL(MOCTA.TA015,0)-ISNULL(MOCTB.TB004,0)-ISNULL(COPTD.TD008,0)+ISNULL(INVMC.MC007,0))/INVMC2.MC004) AS RATE FROM INVMB
 LEFT JOIN (SELECT TB004,SUM(TB009) AS TB009 FROM PURTB WHERE TB025='Y' AND TB039='N' GROUP BY TB004) AS PURTB ON INVMB.MB001=PURTB.TB004
 LEFT JOIN (SELECT TD004,SUM(TD008) AS TD008 FROM PURTD WHERE TD018='N' GROUP BY TD004) AS PURTD ON INVMB.MB001=PURTD.TD004
 LEFT JOIN (SELECT TD004,SUM(TD008-TD015) AS TD008 FROM PURTD WHERE TD018='Y' AND TD016='N' GROUP BY TD004) AS PURTD2 ON INVMB.MB001=PURTD2.TD004
 LEFT JOIN (SELECT TA006,SUM(TA015-TA017-TA018-TA060) AS TA015 FROM MOCTA WHERE TA011 NOT IN ('Y','y') GROUP BY TA006) AS MOCTA ON INVMB.MB001=MOCTA.TA006
 LEFT JOIN (SELECT TB003,SUM(TB004-TB005) AS TB004 FROM MOCTB,MOCTA WHERE TA011 NOT IN ('Y','y') AND TA001=TB001 AND TA002=TB002 GROUP BY TB003) AS MOCTB ON INVMB.MB001=MOCTB.TB003
 LEFT JOIN (SELECT TD004,SUM(TD008+TD024-TD009-TD025) AS TD008 FROM COPTD WHERE TD016='N' GROUP BY TD004) AS COPTD ON INVMB.MB001=COPTD.TD004
 LEFT JOIN (SELECT MC001,SUM(MC007) AS MC007 FROM INVMC WHERE MC002<>'901' GROUP BY MC001) AS INVMC ON INVMB.MB001=INVMC.MC001
 LEFT JOIN (SELECT MC001,SUM(MC004) AS MC004 FROM INVMC WHERE MC002<>'901'GROUP BY MC001) AS INVMC2 ON INVMB.MB001=INVMC2.MC001
WHERE INVMC2.MC004<>0 AND INVMC2.MC004 IS NOT NULL
AND ((ISNULL(PURTB.TB009,0)+ISNULL(PURTD.TD008,0)+ISNULL(PURTD2.TD008,0)-ISNULL(MOCTA.TA015,0)-ISNULL(MOCTB.TB004,0)-
ISNULL(COPTD.TD008,0)+ISNULL(INVMC.MC007,0))-INVMC2.MC004)<0
)
begin
declare @tablehtml nvarchar(max);
declare @obj nvarchar(100);
set @obj =CONVERT(varchar(100), GETDATE(), 112) + '安全库存检视表'
set @tablehtml = N'<H1>库存可用量小于安全库存之品号预警</H1> '
+ N'<table border="1px" cellspacing="0px" style="border-collapse:collapse">' +
+ N'<tr><th>品号</th><th>品名</th><th>规格</th><th>库存可用</th><th>安全存量</th><th>低于数量</th></tr>'
+cast((
--库存可用量预计小于安全可用量（记得剔除中间仓库存）
SELECT 
td=ISNULL(rtrim(MB001),''),'',
td=ISNULL(rtrim(MB002),''),'',
td=ISNULL(rtrim(MB003),''),'',
td=CAST((ISNULL(PURTB.TB009,0)+ISNULL(PURTD.TD008,0)+ISNULL(PURTD2.TD008,0)-ISNULL(MOCTA.TA015,0)-ISNULL(MOCTB.TB004,0)-ISNULL(COPTD.TD008,0)+ISNULL(INVMC.MC007,0)) AS INT),'',
td=CAST((INVMC2.MC004)AS INT),'',
td=convert(decimal(18,3),((ISNULL(PURTB.TB009,0)+ISNULL(PURTD.TD008,0)+ISNULL(PURTD2.TD008,0)-ISNULL(MOCTA.TA015,0)-ISNULL(MOCTB.TB004,0)-ISNULL(COPTD.TD008,0)+ISNULL(INVMC.MC007,0))/INVMC2.MC004)),''
 FROM INVMB
 LEFT JOIN (SELECT TB004,SUM(TB009) AS TB009 FROM PURTB WHERE TB025='Y' AND TB039='N' GROUP BY TB004) AS PURTB ON INVMB.MB001=PURTB.TB004
 LEFT JOIN (SELECT TD004,SUM(TD008) AS TD008 FROM PURTD WHERE TD018='N' GROUP BY TD004) AS PURTD ON INVMB.MB001=PURTD.TD004
 LEFT JOIN (SELECT TD004,SUM(TD008-TD015) AS TD008 FROM PURTD WHERE TD018='Y' AND TD016='N' GROUP BY TD004) AS PURTD2 ON INVMB.MB001=PURTD2.TD004
 LEFT JOIN (SELECT TA006,SUM(TA015-TA017-TA018-TA060) AS TA015 FROM MOCTA WHERE TA011 NOT IN ('Y','y') GROUP BY TA006) AS MOCTA ON INVMB.MB001=MOCTA.TA006
 LEFT JOIN (SELECT TB003,SUM(TB004-TB005) AS TB004 FROM MOCTB,MOCTA WHERE TA011 NOT IN ('Y','y') AND TA001=TB001 AND TA002=TB002 GROUP BY TB003) AS MOCTB ON INVMB.MB001=MOCTB.TB003
 LEFT JOIN (SELECT TD004,SUM(TD008+TD024-TD009-TD025) AS TD008 FROM COPTD WHERE TD016='N' GROUP BY TD004) AS COPTD ON INVMB.MB001=COPTD.TD004
 LEFT JOIN (SELECT MC001,SUM(MC007) AS MC007 FROM INVMC WHERE MC002<>'901' GROUP BY MC001) AS INVMC ON INVMB.MB001=INVMC.MC001
 LEFT JOIN (SELECT MC001,SUM(MC004) AS MC004 FROM INVMC WHERE MC002<>'901'GROUP BY MC001) AS INVMC2 ON INVMB.MB001=INVMC2.MC001
WHERE INVMC2.MC004<>0 AND INVMC2.MC004 IS NOT NULL
AND ((ISNULL(PURTB.TB009,0)+ISNULL(PURTD.TD008,0)+ISNULL(PURTD2.TD008,0)-ISNULL(MOCTA.TA015,0)-ISNULL(MOCTB.TB004,0)-
ISNULL(COPTD.TD008,0)+ISNULL(INVMC.MC007,0))-INVMC2.MC004)<0
for xml path('tr'),type)AS NVARCHAR(MAX))
+N'</table>';
exec msdb.dbo.sp_send_dbmail
@profile_name='db_mail',
@recipients ='1137***@qq.com',
@blind_copy_recipients ='113***@qq.com',
@subject = @obj, 
@body =@tablehtml,
@body_format='HTML'

end

end

效果：