SQL Server给一批用户分别单独发送销售数据清单
销售：我们想要知道自己今年以来的成交订单明细，但是并没有ERP的账号，怎么办？
我：ERP的数据都是客服人员录入的，你们自己卖了多少自己心里没点AC数吗？
销售：没。。
我：系统里有销售表，但是，里面是所有销售员的销售数据，我不能整个表群发，否则别人的秘密你都知道了。
销售：我们部门内部都相互沟通的，没有秘密。
我：你这话，你们领导知道吗，我还是分开单独发吧。
1. 首先获取销售清单的YTD（本年）数据，并只拿出一些他们可以看的列，销售员编号作为参数。
1 CREATE PROC [dbo].[uf_GetYTDInvoicesSp] 2 @slpcode int --销售人员的编号作为参数 3 as 4 SELECT 5 6 [Invoice Date] 7 ,[DocNum] 8 ,[LineNum] 9 ,[Piece No] 10 ,[Product Type] 11 ,[Customer No] 12 ,replace([Customer Name],',','') --因为csv是逗号分隔的，所以把字符串里的逗号去掉 13 ,[Country] 14 ,[SalesArea] 15 ,[Sales Order No] 16 ,[Delivery Note NO.] 17 ,[Delivery Date] 18 ,[Material No] 19 ,[Machine Number] 20 ,[Position Number] 21 ,[Internal Ordered Desgin] 22 ,[Order Length] 23 ,[Order Width] 24 ,[Order GSM/SQM] 25 ,[Order Unit] 26 ,[Sales Order Quantity] 27 ,[Sales Invoiced Amount(Incl.Vat)] 28 ,[Sales Invoiced Amount in USD without vat] 29 ,[DocCurrency] 30 ,[LineCurrency] 31 ,[invoiceno] 32 ,[Customer ref No] 33 ,[Source] 34 ,[ItmsGrpCod] 35 ,[ItmsGrpNam] 36 ,[slpcode] 37 ,[slpname] 38 --,[email] 39 ,[cardcode] 40 ,[PymntGroup] 41 ,[PaperGrade L1] 42 ,[PaperGrade L2] 43 ,[PaperGrade L3] 44 ,[Order Release Date] 45 ,[ID Piece No.] 46 FROM [xxxxdb].[dbo].[uf_SalesInvoiceReport] 47 where [invoice date] >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) 48 and email is not null 49 and [slpcode] = @slpcode 50 51 GO
2. 将销售员的数据导出到CSV文件中，并写好邮件模板。
1 CREATE PROCEDURE [dbo].[uf_GetInvoicesBySales] 2 @slpcode int null 3 4 AS 5 BEGIN 6 DECLARE @xml NVARCHAR(MAX) 7 DECLARE @body NVARCHAR(MAX) 8 DECLARE @myname VARCHAR(1000) 9 DECLARE @mycmd VARCHAR(1000) 10 DECLARE @myattachment VARCHAR(1000) 11 DECLARE @slpemail VARCHAR(100) = NULL 12 DECLARE @CopyMail VARCHAR(200) = NULL 13 DECLARE @slpname VARCHAR(100) 14 DECLARE @mysubject VARCHAR(100) 15 --DECLARE @slpcode int 16 --set @slpcode = 69 17 DROP TABLE IF EXISTS xeriumnewlive..MyTempTable_sub 18 DROP TABLE IF EXISTS xeriumnewlive..MyTempTable 19 20 CREATE TABLE MyTempTable_sub ( 21 [Invoice Date] [varchar](100) NULL, 22 [DocNum] [varchar](100) NULL, 23 [LineNum] [varchar](100) NULL, 24 [Piece No] [nvarchar](100) NULL, 25 [Product Type] [nvarchar](100) NULL, 26 [Customer No] [nvarchar](100) NULL, 27 [Customer Name] [nvarchar](100) NULL, 28 --[Customer Foreign Name] [nvarchar](100) NULL, 29 [Country] [nvarchar](100) NULL, 30 [SalesArea] [nvarchar](100) NULL, 31 [Sales Order No] [varchar](100) NULL, 32 [Delivery Note NO.] [varchar](100) NULL, 33 [Delivery Date] [varchar](100) NULL, 34 [Material No] [nvarchar](100) NULL, 35 [Machine Number] [nvarchar](100) NULL, 36 [Position Number] [nvarchar](100) NULL, 37 [Internal Ordered Desgin] [nvarchar](100) NULL, 38 [Order Length] [nvarchar](100) NULL, 39 [Order Width] [nvarchar](100) NULL, 40 [Order GSM/SQM] [nvarchar](100) NULL, 41 [Order Unit] [nvarchar](100) NULL, 42 [Sales Order Quantity] [nvarchar](100) NULL, 43 [Sales Invoiced Amount(Incl.Vat)] [nvarchar](100) NULL, 44 [Sales Invoiced Amount in USD without vat] [nvarchar](100) NULL, 45 [DocCurrency] [nvarchar](100) NULL, 46 [LineCurrency] [nvarchar](100) NULL, 47 [invoiceno] [nvarchar](100) NULL, 48 [Customer ref No] [nvarchar](100) NULL, 49 [Source] [nvarchar](100) NULL, 50 [ItmsGrpCod] [nvarchar](100) NULL, 51 [ItmsGrpNam] [nvarchar](100) NULL, 52 [slpcode] [nvarchar](100) NULL, 53 [slpname] [nvarchar](155) NULL, 54 --[email] [nvarchar](100) NULL, 55 [cardcode] [nvarchar](15) NULL, 56 [PymntGroup] [nvarchar](100) NULL, 57 [PaperGrade L1] [nvarchar](100) NULL, 58 [PaperGrade L2] [nvarchar](100) NULL, 59 [PaperGrade L3] [nvarchar](100) NULL, 60 [Order Release Date] [nvarchar](100) NULL, 61 [ID Piece No.] [nvarchar](100) NULL 62 ) 63 64 CREATE TABLE MyTempTable( 65 [Invoice Date] [varchar](100) NULL, 66 [DocNum] [varchar](100) NULL, 67 [LineNum] [varchar](100) NULL, 68 [Piece No] [nvarchar](100) NULL, 69 [Product Type] [nvarchar](100) NULL, 70 [Customer No] [nvarchar](100) NULL, 71 [Customer Name] [nvarchar](100) NULL, 72 --[Customer Foreign Name] [nvarchar](100) NULL, 73 [Country] [nvarchar](100) NULL, 74 [SalesArea] [nvarchar](100) NULL, 75 [Sales Order No] [varchar](100) NULL, 76 [Delivery Note NO.] [varchar](100) NULL, 77 [Delivery Date] [varchar](100) NULL, 78 [Material No] [nvarchar](100) NULL, 79 [Machine Number] [nvarchar](100) NULL, 80 [Position Number] [nvarchar](100) NULL, 81 [Internal Ordered Desgin] [nvarchar](100) NULL, 82 [Order Length] [nvarchar](100) NULL, 83 [Order Width] [nvarchar](100) NULL, 84 [Order GSM/SQM] [nvarchar](100) NULL, 85 [Order Unit] [nvarchar](100) NULL, 86 [Sales Order Quantity] [nvarchar](100) NULL, 87 [Sales Invoiced Amount(Incl.Vat)] [nvarchar](100) NULL, 88 [Sales Invoiced Amount in USD without vat] [nvarchar](100) NULL, 89 [DocCurrency] [nvarchar](100) NULL, 90 [LineCurrency] [nvarchar](100) NULL, 91 [invoiceno] [nvarchar](100) NULL, 92 [Customer ref No] [nvarchar](100) NULL, 93 [Source] [nvarchar](100) NULL, 94 [ItmsGrpCod] [nvarchar](100) NULL, 95 [ItmsGrpNam] [nvarchar](100) NULL, 96 [slpcode] [nvarchar](100) NULL, 97 [slpname] [nvarchar](155) NULL, 98 --[email] [nvarchar](100) NULL, 99 [cardcode] [nvarchar](15) NULL, 100 [PymntGroup] [nvarchar](100) NULL, 101 [PaperGrade L1] [nvarchar](100) NULL, 102 [PaperGrade L2] [nvarchar](100) NULL, 103 [PaperGrade L3] [nvarchar](100) NULL, 104 [Order Release Date] [nvarchar](100) NULL, 105 [ID Piece No.] [nvarchar](100) NULL 106 ) 107 108 109 --根据销售人员的代码，向MyTempTable_sub表中添加数据集 110 INSERT INTO MyTempTable_sub 111 EXEC dbo.uf_GetYTDInvoicesSp @slpcode 112 113 INSERT INTO MyTempTable 114 --这里做一个虚拟的表头，放在CSV文件中 115 SELECT 'Invoice Date' 116 ,'DocNum' 117 ,'LineNum' 118 ,'Piece No' 119 ,'Product Type' 120 ,'Customer No' 121 ,'Customer Name' 122 --,'Customer Foreign Name' 123 ,'Country' 124 ,'SalesArea' 125 ,'Sales Order No' 126 ,'Delivery Note NO.' 127 ,'Delivery Date' 128 ,'Material No' 129 ,'Machine Number' 130 ,'Position Number' 131 ,'Internal Ordered Desgin' 132 ,'Order Length' 133 ,'Order Width' 134 ,'Order GSM/SQM' 135 ,'Order Unit' 136 ,'Sales Order Quantity' 137 ,'Sales Invoiced Amount(Incl.Vat)' 138 ,'Sales Invoiced Amount in USD without vat' 139 ,'DocCurrency' 140 ,'LineCurrency' 141 ,'invoiceno' 142 ,'Customer ref No' 143 ,'Source' 144 ,'ItmsGrpCod' 145 ,'ItmsGrpNam' 146 ,'slpcode' 147 ,'slpname' 148 --,'email' 149 ,'cardcode' 150 ,'PymntGroup' 151 ,'PaperGrade L1' 152 ,'PaperGrade L2' 153 ,'PaperGrade L3' 154 ,'Order Release Date' 155 ,'ID Piece No.' 156 union all 157 ---销售数据明细 158 SELECT ISNULL([Invoice Date], '') 159 ,ISNULL([DocNum], '') 160 ,ISNULL([LineNum], '') 161 ,ISNULL([Piece No], '') 162 ,ISNULL([Product Type], '') 163 ,ISNULL([Customer No], '') 164 ,ISNULL([Customer Name], '') 165 --,ISNULL([Customer Foreign Name], '') 166 ,ISNULL([Country], '') 167 ,ISNULL([SalesArea], '') 168 ,ISNULL([Sales Order No], '') 169 ,ISNULL([Delivery Note NO.], '') 170 ,ISNULL([Delivery Date], '') 171 ,ISNULL([Material No], '') 172 ,ISNULL([Machine Number], '') 173 ,ISNULL([Position Number], '') 174 ,ISNULL([Internal Ordered Desgin], '') 175 ,ISNULL([Order Length], '') 176 ,ISNULL([Order Width], '') 177 ,ISNULL([Order GSM/SQM], '') 178 ,ISNULL([Order Unit], '') 179 ,ISNULL([Sales Order Quantity], '') 180 ,ISNULL([Sales Invoiced Amount(Incl.Vat)], '') 181 ,ISNULL([Sales Invoiced Amount in USD without vat], '') 182 ,ISNULL([DocCurrency], '') 183 ,ISNULL([LineCurrency], '') 184 ,ISNULL([invoiceno], '') 185 ,ISNULL([Customer ref No], '') 186 ,ISNULL([Source], '') 187 ,ISNULL([ItmsGrpCod], '') 188 ,ISNULL([ItmsGrpNam], '') 189 ,ISNULL([slpcode], '') 190 ,ISNULL([slpname], '') 191 --,ISNULL([email], '') 192 ,ISNULL([cardcode], '') 193 ,ISNULL([PymntGroup], '') 194 ,ISNULL([PaperGrade L1], '') 195 ,ISNULL([PaperGrade L2], '') 196 ,ISNULL([PaperGrade L3], '') 197 ,ISNULL([Order Release Date], '') 198 ,ISNULL([ID Piece No.], '') 199 FROM MyTempTable_sub 200 201 select @slpemail = email from oslp where slpcode = @slpcode 202 select @slpname = slpname from oslp where slpcode = @slpcode 203 204 205 SET @body =' 206 207208 Dear ' + @slpname + ', ' + 209 ' 210
211 Please check the attachment to review your invoiced orders for this year.
212 The system automatically sends this report to you every Tuesday at 8:30 a.m 213 214215 * Please don''t reply to this email directly!
216 * At the beginning of each year, the records in this table may be small or empty because it''s YTD data! 217 218219 Best Regards,
220 221 公司名 222 223 224 225 226 ' 227 228 SET @myname = 'Invoice_List'+ ' YTD_' + convert(varchar(10),year(GetDate()))+' '+replace(@slpname,' ','_')+' ' +CONVERT(varchar(12) , getdate(), 23)+'.csv' 229 --导出附件 230 SET @mycmd = 'bcp "SELECT * FROM xxxxdb..MyTempTable order by [invoice date] desc" queryout "D:\temp\' + @myname + '" -T -c -t","' 231 SET @myattachment = 'D:\temp\' + @myname 232 SET @mysubject = 'Invoice_List'+ ' YTD_' + convert(varchar(10),year(GetDate()))+' '+replace(@slpname,' ','_') 233 234 ----打开高级设置 235 --EXEC sp_configure 'show advanced options', 1 236 --RECONFIGURE 237 ----打开xp_cmdshell扩展存储过程 238 --EXEC sp_configure 'xp_cmdshell', 1 239 --RECONFIGURE 240 241 242 EXEC xp_cmdshell @mycmd 243 244 EXEC msdb.dbo.sp_send_dbmail 245 @profile_name = 'xxx.noreply' 246 ,@recipients = @slpemail 248 ,@body = @body 249 ,@body_format = 'HTML' 250 ,@subject = @mysubject 251 ,@file_attachments = @myattachment --添加附件 252 ; 253 254 255 ----关闭xp_cmdshell扩展存储过程、高级设置 256 --EXEC sp_configure 'xp_cmdshell', 0 257 --RECONFIGURE 258 --EXEC sp_configure 'show advanced options', 0 259 --RECONFIGURE 260 261 DROP TABLE IF EXISTS MyTempTable_sub 262 DROP TABLE IF EXISTS MyTempTable 263 264 END 265 GO
3. 利用游标，逐个抓取销售人员的编号，并将此编号作为参数传递给上一步的存储过程。
1 CREATE PROC [dbo].[uf_Split_SendInvoices2Sales] 2 3 AS 4 5 declare @slpcode int 6 declare cusinfo cursor scroll --定义游标 --scroll为滚动游标 7 for 8 --select slpcode from oslp where email is not null 9 select slpcode FROM [xxxxdb].[dbo].[uf_SalesInvoiceReport] where email is not null and slpcode not in (65) and [invoice date] >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) group by slpcode --确保没有销售记录的人员以及某些特人员不需要收到邮件 10 open cusinfo -- 打开游标 11 fetch next from cusinfo INTO @slpcode --抓取下一行游标数据 12 while @@fetch_status=0 --0 FETCH 语句成功; -1 FETCH 语句失败或此行不在结果集中; -2 被提取的行不存在 13 begin 14 exec uf_GetInvoicesBySales @slpcode 15 fetch next from cusinfo into @slpcode --抓取下一行游标数据 16 end 17 close cusinfo --关闭游标 18 deallocate cusinfo --释放游标 19 GO
4. 创建Job，定时发送。我用的是图形化界面创建的。
OK，大功告成。