一、demo脚本
DROP PROCEDURE IF EXISTS `hongjin_email_xiangmuribao`;
DELIMITER ;;
CREATE PROCEDURE `hongjin_email_xiangmuribao`(
)
BEGIN
-- 24点执行，发送昨天的数据
IF HOUR(NOW())>=9 THEN
SET @my_date=CURDATE();
ELSE
SET @my_date=DATE_SUB(CURDATE(),INTERVAL 1 DAY);
END IF;SET @my_emailto=(
SELECT
GROUP_CONCAT(DISTINCT a.email1)
FROM ld_users a
LEFT JOIN ld_usersscf b ON b.id=a.id
WHERE b.cf_7956=1
);IF IFNULL(@my_emailto,'')<>'' AND EXISTS (SELECT 1 FROM ld_customform100 WHERE deleted=0 AND DATE_FORMAT(createdtime,'%Y-%m-%d')=@my_date) THEN
SET @i=0;
SET @my_emailtitle=CONCAT(
DATE_FORMAT(NOW(),'%Y年%c月%e日'),
'（星期',
(CASE DAYOFWEEK(NOW())
WHEN 1 THEN '日'
WHEN 2 THEN '一'
WHEN 3 THEN '二'
WHEN 4 THEN '三'
WHEN 5 THEN '四'
WHEN 6 THEN '五'
WHEN 7 THEN '六'
END),
'）客户项目日报表：');SET @my_emailcontent=(
SELECT
CONCAT(
'<div><span style="padding-left:2mm; font-weight: bold">',
CONCAT(DATE_FORMAT(NOW(),'%Y年%c月%e日'),'（星期',
(CASE DAYOFWEEK(NOW())
WHEN 1 THEN '日'
WHEN 2 THEN '一'
WHEN 3 THEN '二'
WHEN 4 THEN '三'
WHEN 5 THEN '四'
WHEN 6 THEN '五'
WHEN 7 THEN '六'
END),
'）客户项目日报表：</span>'),
'</div>'
'<table border="1" cellpadding="0" cellspacing="0" style="width:95%">',CHAR(13),
SPACE(3),'<tr style="background-color: #F4F8FE">',CHAR(13),
SPACE(4),'<td align="center" style="width:2%"><span style="font-weight: bold">序号</span></td>',CHAR(13),
SPACE(4),'<td align="center" style="width:5%"><span style="font-weight: bold">业务负责人</span></td>',CHAR(13),
SPACE(4),'<td align="center" style="width:15%"><span style="font-weight: bold">客户全称</span></td>',CHAR(13),
SPACE(4),'<td align="center" style="width:8%"><span style="font-weight: bold">芯片型号</span></td>',CHAR(13),
SPACE(4),'<td align="center" style="width:10%"><span style="font-weight: bold">方案名称</span></td>',CHAR(13),SPACE(4),'<td align="center" style="width:8%"><span style="font-weight: bold">主控平台型号</span></td>',CHAR(13),
SPACE(4),'<td align="center" style="width:20%"><span style="font-weight: bold">终端应用产品</span></td>',CHAR(13),
SPACE(4),'<td align="center" style="width:8%"><span style="font-weight: bold">项目阶段</span></td>',CHAR(13),
SPACE(4),'<td align="center" style="width:8%"><span style="font-weight: bold">项目状态</span></td>',CHAR(13),
SPACE(4),'<td align="center" style="width:8%"><span style="font-weight: bold">商务进度</span></td>',CHAR(13),
SPACE(4),'<td align="center" style="width:8%"><span style="font-weight: bold">技术进度</span></td>',CHAR(13),
SPACE(3),'</tr>',CHAR(13),
GROUP_CONCAT(
CONCAT(
SPACE(3),'<tr>',CHAR(13),
SPACE(4),'<td align="center"><span>',@i:=@i+1,'</span></td>',CHAR(13),
SPACE(4),'<td align="center"><span>',a.fuzeren,'</span></td>',CHAR(13),
SPACE(4),'<td align="center"><span>',a.accountname,'</span></td>',CHAR(13),
SPACE(4),'<td align="center"><span>',a.xinpianxinghao,'</span></td>',CHAR(13),
SPACE(4),'<td align="center"><span>',a.fanganmingchun,'</span></td>',CHAR(13),
SPACE(4),'<td align="center"><span>',a.zhukongxinghao,'</span></td>',CHAR(13),SPACE(4),'<td align="center"><span>',a.zhongduanyingyin,'</span></td>',CHAR(13),
SPACE(4),'<td align="center"><span>',a.xiangmujieduan,'</span></td>',CHAR(13),
SPACE(4),'<td align="center"><span>',a.xiangmuzhuangtai,'</span></td>',CHAR(13),
SPACE(4),'<td align="center"><span>',a.shangwujindu,'</span></td>',CHAR(13),
SPACE(4),'<td align="center"><span>',a.jishujindu,'</span></td>',CHAR(13),
SPACE(3),'</tr>',
CHAR(13)
)
SEPARATOR '\r\n'
)
) AS html
FROM (
SELECT
u.last_name AS fuzeren,
c.accountname,
IFNULL(b.cf_7580,'') AS xinpianxinghao,
IFNULL(b.cf_7581,'') AS fanganmingchun,
IFNULL(b.cf_7582,'') AS zhongduanyingyin,
IFNULL(b.cf_7583,'') AS xiangmujieduan,
IFNULL(b.cf_7584,'') AS xiangmuzhuangtai,
IFNULL(b.cf_7585,'') AS shangwujindu,
IFNULL(b.cf_7586,'') AS jishujindu,
IFNULL(b.cf_7955,'') AS zhukongxinghao
FROM ld_customform100 a
LEFT JOIN ld_customform100scf b ON b.customform100id=a.customform100id
LEFT JOIN ld_account c ON c.accountid=a.accountid
LEFT JOIN ld_users u ON u.id=a.smownerid
WHERE a.deleted=0
AND DATE_FORMAT(a.createdtime,'%Y-%m-%d')=@my_date
ORDER BY CONVERT(u.last_name USING GBK)
) a
);CALL ldcrm_add_email(@my_emailtitle,@my_emailcontent,@my_emailto,NOW(),1);
END IF;end ;;
DELIMITER ;
-- 测试发送邮件
-- CALL hongjin_email_xiangmuribao();
-- 添加事件每天晚上 24点执行
DROP EVENT IF EXISTS `hongjin_email_xiangmuribao_event`;
DELIMITER ;;
CREATE EVENT `hongjin_email_xiangmuribao_event` ON SCHEDULE EVERY 1 DAY STARTS '2024-04-11 00:00:01' ON COMPLETION NOT PRESERVE ENABLE DO
CALL hongjin_email_xiangmuribao();
;;
DELIMITER ;
/*
-- 查询邮件是否发送成功
SELECT
a.`subject` AS emai_subject,
a.date_start AS email_addtime,
b.email_sendtime,
description AS email_content,
a.to_email,
a.cc_email,
CASE WHEN reminder_sent=1 THEN '已发送' ELSE '未发送' END AS send_tag
FROM ld_activity a
LEFT JOIN ld_activity_reminder b ON b.activity_id=a.activityid
WHERE a.deleted=0
AND a.activitytype='Emails'
*/
-- crontab 添加计划任务
*/5 * * * * sudo /usr/bin/php /opt/home/www/crm/modules/Reminds/sendRemindsEmail.php >/dev/null