Mr_Cxy

导航

结合Mysql和kettle邮件发送日常报表_20161001

十一假期 参加婚礼 稍晚点发博 

整体流程步骤是：

写SQL-导出到excel设定excel模板调整格式-设置kettle转换--设置kettle邮件作业--完成

第一、写SQL 保持最近12个周的数据 

SELECT city AS 城市,DATE_FORMAT(order_date,"%x%v") AS 年周,
    CASE 
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -12 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -11 WEEK)  THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -12 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -12 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -12 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -11 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -10 WEEK)  THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -11 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -11 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -11 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -10 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -9 WEEK)  THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -10 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -10 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -10 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -9 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -8 WEEK)  THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -9 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -9 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -9 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -8 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -7 WEEK)  THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -8 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -8 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -8 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -7 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -6 WEEK)  THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -7 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -7 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -7 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -6 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -5 WEEK)  THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -6 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -6 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -6 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -5 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -4 WEEK)  THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -5 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -5 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -5 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -4 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -3 WEEK)  THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -4 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -4 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -4 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -3 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -2 WEEK)  THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -3 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -3 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -3 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -2 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -1 WEEK)  THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -2 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -2 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -2 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -1 WEEK) AND order_date<SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1) THEN CONCAT(CONCAT("第",WEEK(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -1 WEEK)),"周"),CONCAT(DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -1 WEEK),"%m%d"),"-",DATE_FORMAT(DATE_ADD(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -1 WEEK),INTERVAL 6 DAY),"%m%d")))
    WHEN order_date>=SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1) AND order_date<CURRENT_DATE THEN CONCAT(CONCAT("第",WEEK(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1)),"周"),CONCAT(DATE_FORMAT(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),"%m%d"),"-",DATE_FORMAT(DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL 6 DAY),"%m%d")))
    ELSE NULL END AS 年周日期,
    CASE 
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -12 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -11 WEEK) THEN "12"
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -11 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -10 WEEK) THEN "11"
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -10 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -9 WEEK) THEN "10"
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -9 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -8 WEEK) THEN "9"
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -8 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -7 WEEK) THEN "8"
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -7 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -6 WEEK) THEN "7"
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -6 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -5 WEEK) THEN "6"
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -5 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -4 WEEK) THEN "5"
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -4 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -3 WEEK) THEN "4"
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -3 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -2 WEEK) THEN "3"
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -2 WEEK) AND order_date<DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -1 WEEK) THEN "2"
    WHEN order_date>=DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1),INTERVAL -1 WEEK) AND order_date<SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1) THEN "1"
    WHEN order_date>=SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1) AND order_date<CURRENT_DATE THEN "0"
    ELSE NULL END AS 与当前周间隔几周,SUM(pay_money) AS 订单额
FROM `test_a03order` AS a
WHERE order_date>= DATE_ADD(SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,"%w")-1),INTERVAL -12 WEEK) AND order_date<CURRENT_DATE AND pay_money>0
GROUP BY city ,DATE_FORMAT(order_date,"%x%v")
12周数据

 

第二步设定excel模板调整格式 

主要设置上面sql语句要展示的表的表头 excel模板文件名 sheet表名

表头是执行kettle时候excel表输出时候的内容区域 

excel模板文件名 是kettle调用此模板 按照模板设置输出表 

sheet表名是kettle向excel表输出时候选择哪个sheet表（顾名思义 kettle是支持向多个sheet表输入数据的)

第三步 设置kettle转换 设置数据库连接 表输入步骤 excel表输出 步骤

 将SQL代码输入  表输入（kettle-核心对象-输入-表输入）

设置表输出：核心对象-输出-microsoft excel表输出

 

 ###

绝对路径 相对路径 是在不同终端上使用kettle文件时可以正常使用  用相对路径比较好 主要用于在输出的文件名 和调用excel模板文件时候用到

使用相对路径快捷键：ctrl+alt+space 组合键

下面图 主要和上个图对比

 

原来的C:\Users\cxy\Desktop\ 绝对路径

转换为了 ${Internal.Transformation.Filename.Directory}相对路径 指向的是test文件所在的目录

 

调用excel模板同样道理

下一步 设置数据输出的起始位置

kettle 保存执行 这时候会在桌面（我设置的路径是桌面）生成test的excel文件

 执行完毕 excel文件已生成

 

第四步 把生成文件设置成自动发送邮件

新建一个作业保存命名  调用上面的kettle转换文件 添加发送邮件控件  设置发件人信息 完成

 

 

 

 

 

 

 结束

 

posted on 2016-09-30 12:26  Mr_Cxy  阅读(934)  评论(0)  编辑  收藏  举报