时间戳

首页 > 解决方案 > 基于 Sql 表格格式电子邮件中的值的颜色代码

sql - 基于 Sql 表格格式电子邮件中的值的颜色代码

问题描述

我有发送 HTML 表格电子邮件的工作代码，如果它们低于 0，我只需要帮助对 HTML 代码中的特定结果进行颜色编码。

   set datefirst 1;
   set nocount on;

   if (object_id('tempdb..#temp') iS NOT NULL)
             drop table #temp

   declare @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX);
   USE PL00
   select w.WeekDayID,w.[WeekDay] + FORMAT(convert(date,convert(varchar(10),dateadd(dd,w.WeekDayID-DATEPART(dw,getdate()),GETDATE()),121)),' (MM/dd)') as [WeekDay]
             ,SUM(CASE o.[TYPE] WHEN 'SVC' THEN o.SUB_TOTAL ELSE 0.0 END) as TotalSales,COUNT(DISTINCT CASE o.[TYPE] WHEN 'SVC' THEN o.ORDER_NO ELSE NULL END) as TotalOrders
                   ,MAX(CASE o.[TYPE] WHEN 'SVC' THEN e.U_DLYSALES ELSE 0.0 END) as SalesGoal
   into #temp
   from (select 1 as WeekDayID,'Monday' as [WeekDay] union select 2,'Tuesday' union select 3,'Wednesday' union select 4,'Thursday' union select 5,'Friday' union select 6,'Saturday' union select 7,'Sunday') as w
             left join schedule s on w.WeekDayID = DATEPART(dw,s.[DATE]) inner join ordhdr o on s.ID_VAL = o.ORDER_NO left join employee e on o.leadtech = e.emp_id
   where s.[DATE] between convert(datetime,convert(varchar(10),dateadd(dd,1-DATEPART(dw,getdate()),GETDATE()),121)) and convert(datetime,convert(varchar(10), GETDATE(),121))
   --     and o.[TYPE] = 'SVC'
             and e.EMP_ID = '45-ANGORTI'
   group by w.WeekDayID,w.[WeekDay]
   union 
   select 8 as WeekDayID,'Weekly' as [WeekDay], SUM(o2.SUB_TOTAL) as TotalSales,COUNT(DISTINCT o2.ORDER_NO) as TotalOrders,MAX(ISNULL(e.U_WKSALES,0.0)) as SalesGoal
   from ordhdr o2 inner join schedule s2 on s2.ID_VAL = o2.ORDER_NO left join employee e on o2.leadtech = e.emp_id
                         where s2.[DATE]  between convert(datetime,convert(varchar(10),dateadd(dd,1-DATEPART(dw,getdate()),GETDATE()),121))  
                                      and convert(datetime,convert(varchar(10),GETDATE(),121))
                                     and o2.[TYPE] = 'SVC'
             and e.EMP_ID = '45-ANGORTI'
   union 
   select 9 as WeekDayID,'Monthly' as [WeekDay], SUM(o2.SUB_TOTAL) as TotalSales,COUNT(DISTINCT o2.ORDER_NO) as TotalOrders,MAX(ISNULL(e.U_MONSALES,0.0)) as SalesGoal
   from ordhdr o2 inner join schedule s2 on s2.ID_VAL = o2.ORDER_NO left join employee e on o2.leadtech = e.emp_id
                         where s2.[DATE]  between convert(datetime,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))  
                                      and convert(datetime,convert(varchar(10),GETDATE(),121))
                                     and o2.[TYPE] = 'SVC'
             and e.EMP_ID = '45-ANGORTI'

   insert #temp
   select 
             w.WeekDayID, w.[WeekDay] + FORMAT(convert(date,convert(varchar(10),dateadd(dd,w.WeekDayID-DATEPART(dw,getdate()),GETDATE()),121)),' (MM/dd)') as [WeekDay]
             ,0.0 as TotalSales,0 as TotalOrders,0.0 as SalesGoal
   from (select 1 as WeekDayID,'Monday' as [WeekDay] union select 2,'Tuesday' union select 3,'Wednesday' union select 4,'Thursday' union select 5,'Friday' union select 6,'Saturday' union select 7,'Sunday') as w
             left join #temp t on w.WeekDayID = t.WeekDayID
   where t.WeekDayID is null

   --delete #temp where WeekDayID >= DATEPART(dw,getdate())

   --select e.U_DLYSALES,e.U_WKSALES,e.U_MONSALES, * from employee e where e.U_DLYSALES > 0.0
   select * from #temp order by WeekDayID


DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT #temp.[WeekDay] AS 'td','',  #temp.TotalSales AS 'td','',#temp.TotalOrders AS 'td','',#temp.SalesGoal  AS 'td', '',
(#temp.TotalSales -  #temp.SalesGoal) AS 'td'


from #temp
order by WeekDayID
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3> TEST. </H3>
<table border = 1> 
<tr>
<th> Weekday </th> <th> TotalSales </th> <th> Total ORders </th> <th> SalesGoal </th>  <th> Difference </th>  </tr>'    

SET @body = @body + @xml +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name =  'mail.pacificlawnsprinklers.com',
@body = @body,
@body_format ='HTML',
@recipients = 'Mmahoney@pacificlawnsprinklers.com', -- replace with your email address
@subject = 'TEST' ;

测试。

+-------------------+-------------+---------------+------------+-------------+
|     Weekday       | TotalSales  | Total ORders  | SalesGoal  | Difference  |
+-------------------+-------------+---------------+------------+-------------+
| Monday (08/05)    |     2570.00 |             1 |    1000.00 |     1570.00 |
| Tuesday (08/06)   |     1072.00 |             6 |    1000.00 |       72.00 |
| Wednesday (08/07) |        0.00 |             0 |       0.00 |        0.00 |
| Thursday (08/08)  |        0.00 |             0 |       0.00 |        0.00 |
| Friday (08/09)    |        0.00 |             0 |       0.00 |        0.00 |
| Saturday (08/10)  |        0.00 |             0 |       0.00 |        0.00 |
| Sunday (08/11)    |        0.00 |             0 |       0.00 |        0.00 |
| Weekly            |     3642.00 |             7 |    6000.00 |    -2358.00 |
| Monthly           |     6244.00 |            13 |   25000.00 |   -18756.00 |
+-------------------+-------------+---------------+------------+-------------+

标签： sqlsql-servertsqlhtml-emailsp-send-dbmail

解决方案

您可以检查零值并将它们包含在一个跨度中并给它一个颜色。

例子：

<span style="font-color:#f00">0</span>

由于您是为电子邮件执行此操作，因此唯一的全局支持是在元素级别或数据级别添加颜色内联。

您可以添加一个类，但一些电子邮件客户端不会CSS在头脑中阅读，或者它们被剥离了。如果你想走上课的路线，那么下面是你可以做的。

.red{color:#f00;}
<span class="red">0</span>

推荐阅读