定时发送邮件（如何最优）

需求

搞活期期间，定时给已经签收的客户，三天后发邮件

技术栈

1.调用第三方快递100或者阿里云快递接口
2.第三方接口收费，需考虑节省成本。
    -a.快递一般3天，5天，7天才签收，可以隔3，2，2查询一次快递接口
    -b.邮件签收后3天发送，可以根据时间和状态节省邮件数量
    -c.国外邮件投入了垃圾箱问题，使用mandrill邮件发送，减少邮件进入垃圾箱频率，同时，可以改用模板格式为html的
3.使用shopify

数据表设计

CREATE TABLE `nc_survery_email_record` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `email` varchar(32) NOT NULL COMMENT '邮箱',
  `shopify_order_id` varchar(32) NOT NULL COMMENT 'shopify订单ID',
  `sku_info` varchar(255) NOT NULL COMMENT 'sku信息，多个逗号隔开，如1,2',
  `submission_format` datetime DEFAULT NULL COMMENT '物流签收时间（当地时间）',
  `submission_cn_at` int(10) unsigned NOT NULL COMMENT '物流签收时间(北京时间)',
  `track_info` varchar(255) NOT NULL COMMENT '追踪信息(追踪单号和追踪公司编号)',
  `track_info_at` int(10) unsigned NOT NULL COMMENT '物流追踪号的更新时间(北京时间)',
  `interval_day` tinyint(1) unsigned NOT NULL DEFAULT '3' COMMENT '间隔天数，默认3,',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '状态，1待发邮件，2已发邮件，3跟踪号已过期',
  `created_at` int(10) unsigned NOT NULL COMMENT '创建时间',
  `updated_at` int(10) unsigned NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`,`track_info_at`) USING BTREE,
  UNIQUE KEY `shopify_order_id` (`shopify_order_id`),
  KEY `submission_at` (`submission_cn_at`),
  KEY `track_info_at` (`track_info_at`)
) ENGINE=InnoDB AUTO_INCREMENT=937 DEFAULT CHARSET=utf8 COMMENT='shopify物流签收并发邮件跟踪表';

代码

<?php
class ControllerCommandsNaipoShopify extends Controller
{

    /*
     * 完成后，3天自动（定时）发送邮件 --第一封邮件survey_edm.html
     */
    public function transactionSuccessSendMail()
    {
        set_time_limit(0);
        ini_set("memory_limit", "-1");


        $this->load->model('com/common');
        $this->load->model('catalog/information');
        $res = $this->db->query("SELECT `created_at` FROM `" . DB_PREFIX . "survery_email_record` ORDER BY `created_at` DESC")->row;
        $updatedAt = strtotime("-10 day");
        if (!empty($res)) $updatedAt = strtotime("-10 minute", $res['created_at']);
        $updatedAtMin = sysLocalToLocal('America/New_York', $updatedAt, 'Y-m-d\TH:i:s');
        //获取操作已付款，已完成的订单
        $pageApiUrl = "/admin/api/2020-04/orders.json?limit=250&status=any&fulfillment_status=fulfilled&financial_status=paid,partially_refunded&updated_at_min=" . $updatedAtMin;
        var_dump($pageApiUrl);
        $resJson = shopifyCurl($pageApiUrl);
        $orderData = json_decode($resJson, true);
        unset($resJson);
//        var_dump($orderData);
        if (isset($orderData['orders']) && !empty($orderData['orders']))
        {
            foreach ($orderData['orders'] as $k => $ov)
            {
//                if($ov['id']!='2107980120108'){
//                    continue;
//                }
                if (!empty($ov['fulfillments']))
                {
                    $skuInfo = [];
                    if (!empty($ov['line_items']))
                    {
                        foreach ($ov['line_items'] as $lk)
                        {
                            $skuInfo[] = $lk['sku'];
                        }
                    }

                    $submissionAt = [];
                    $trackInfo = [];
                    // 判定当前订单是否已签收或部分签收，如果签收，则保存签收时间
                    foreach ($ov['fulfillments'] as $fulfillment)
                    {
                        if(!empty($fulfillment['tracking_company']) && !empty($fulfillment['tracking_number']))
                        {
                            $trackInfo[] = [
                                'track_number' => $fulfillment['tracking_number'], //物流号
                                'com' => $fulfillment['tracking_company'], //物流公司编码
                            ];
                            // 已签收
                            if ($fulfillment['shipment_status'] == 'delivered')
                            {
                                $submissionAt[] = strtotime($fulfillment['updated_at']);
                            }else
                            {
                                //已发货-预计到达时间
                                $pageApiUrl = "/admin/api/2020-04/orders/".$ov['id']."/fulfillments/".$fulfillment['id'] ."/events.json";
                                $resJson = shopifyCurl($pageApiUrl);
                                $fulfillmentData = json_decode($resJson, true);
                                if(!empty($fulfillmentData)){
                                    foreach ($fulfillmentData['fulfillment_events'] as $fulfillmentDatum){
                                        if(!empty($fulfillmentDatum['estimated_delivery_at'])){
                                            $submissionAt[] = strtotime($fulfillmentDatum['estimated_delivery_at']);
                                        }
                                    }
                                }

                            }
                        }
                    }

                    if(!empty($trackInfo))
                    {
                        $createdAt = time();
                        $insertOrders = [];
                        $trackInfoDate = date('Y-m-d', strtotime($ov['updated_at']));
                        $insertOrders['track_info_at'] = strtotime($trackInfoDate);
                        $insertOrders['track_info'] = json_encode($trackInfo);
                        $insertOrders['sku_info'] = implode(',', $skuInfo);
                        $insertOrders['created_at'] = $createdAt;
                        $insertOrders['updated_at'] = $createdAt;
                        $insertOrders['shopify_order_id'] = $ov['id'];
                        $insertOrders['email'] = $ov['email'];
                        if (!empty($submissionAt) && max($submissionAt) > 0)
                        {
                            $insertOrders['submission_cn_at'] = max($submissionAt);
                            $insertOrders['submission_format'] = sysLocalToLocal('America/New_York', max($submissionAt));
                        }
                        if (! $this->db->query("SELECT id FROM `" . DB_PREFIX . "survery_email_record` WHERE `shopify_order_id`='" . $ov['id'] . "'")->row)
                        {
                            //插入需要发送邮件的表
                            $this->model_com_common->insert(DB_PREFIX . 'survery_email_record', $insertOrders);
                        }else
                        {
                            // 更新
                            unset($insertOrders['track_info_at']);
                            unset($insertOrders['created_at']);
                            $updateSet = '';
                            foreach ($insertOrders as $key => $val)
                            {
                                $updateSet .= $key . "='" . $val . "', ";
                            }
                            $updateSet = trim($updateSet, ', ');
                            var_dump($insertOrders);
                            $this->db->query("UPDATE " . DB_PREFIX . "survery_email_record SET " . $updateSet . " WHERE shopify_order_id = '" . $ov['id'] . "' and submission_cn_at = 0");
                        }
                    }
                }
            }
        }
        unset($orderData);

        //从获取需要发邮件的表中，定时发送邮件,3天前的
        $sendEmailsql = "SELECT * FROM `" . DB_PREFIX . "survery_email_record` WHERE `status` = 1 AND `submission_cn_at` > 0 AND `submission_cn_at` <='" . strtotime("-3 day") . "' ORDER BY `submission_cn_at` ASC LIMIT 4";
        var_dump($sendEmailsql);
        $sendEmailData = $this->db->query($sendEmailsql)->rows;
        foreach ($sendEmailData as $row)
        {
            try {
                $params = [
                    'sku_info'=> $row['sku_info'],
                    'email'=> $row['email'],
                    'order_number'=> $row['shopify_order_id'],
                ];
                //发送邮件
                $this->sendEmail($params, $row['email']);
                //发送成功更新状态为已发送
                $updateArr = ['status' => 2, 'updated_at' => time()];
                $updateSet = '';
                foreach ($updateArr as $key => $val)
                {
                    $updateSet .= $key . "='" . $val . "', ";
                }
                $updateSet = trim($updateSet, ', ');
                $this->db->query("UPDATE " . DB_PREFIX . "survery_email_record SET " . $updateSet . " WHERE id = '" . $row['id'] . "'");
                echo $row['email'] . ' send mail success<br/>';
                sleep(5);
            } catch (Exception $e) {
                var_dump($e->getMessage());
                echo $row['email'] . ' send mail error<br/>';
            }
        }
    }

    /**
     * @desc 邮件发送
     * @param string $sku_info
     * @param string $email
     * @return bool
     */
    public function sendEmail($params='',$email='')
    {
        $this->load->model('catalog/information');
        $private_key = base64_encode(json_encode($params));//加密数据(邮箱号，产品型号，订单号)

        $title = 'Take 3 minutes to help us improve and get $5';//标题
        $type = 'Customer-Experience-Survey';//退订活动主题

        $temp = $this->getNewTemp($private_key, $email, $type);//模板
//        $res = $this->model_catalog_information->sendMail($email, $title, $temp, 'NAIPO', SMTP_FROM_EMAIL_INFO, SMTP_FROM_PASSWORD_INFO, SMTP_FROM_EMAIL_INFO);//阿里邮箱发送
        $res = $this->model_catalog_information->sendMail($email, $title, $temp, 'NAIPO', SMTP_FROM_EMAIL_MANDRILL, SMTP_FROM_PASSWORD_MANDRILL, SMTP_FROM_EMAIL_INFO, 'html', SMTP_FROM_PORT_MANDRILL, SMTP_FROM_HOSTNAME_MANDRILL);//mandrill邮件发送

        var_dump($res);
        return true;
    }

    /**
     * survey_edm模板
     */
    public function getNewTemp($private_key,$email,$type)
    {
        return '<!DOCTYPE>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>NAIPO</title>
</head>
    <body style="background-color:#FAFAFA; margin:0; padding:0; font-family:Arial;">
        <table align="center" border="0" cellpadding="0" cellspacing="0" style="width: 100%; max-width: 600px; margin: 0 auto; font-size: 14px; vertical-align: middle; background: #fff;">
        <tr>
            <td style="text-align: center; background:#f2f2f4"><img src="https://cdn.shopify.com/s/files/1/0032/9370/8323/files/edm-logo-140.png?v=1587193232" alt=""></td>
        </tr>
        <tr>
            <td style="width: 100%; max-width: 600px; height: 151px; text-align:center;">
                <img style="width: 100%;" src="https://cdn.shopify.com/s/files/1/0032/9370/8323/files/web-banner-mobile.gif?v=1587021000" alt="" />
            </td>
        </tr>
        <tr style="background: #f9f9f9;">
            <td style="width: 100%; max-width: 600px; text-align:center; color: #333; font-size: 14px;">
                <p style="font-size: 14px; line-height: 24px; text-align: left; margin: 0; padding: 5% 10%;">
                <span style="font-size: 16px; display: block;">Hello,</span>
                <br>
                Thank you for choosing a NAIPO massager. We greatly appreciate your trust, and we’d love to know how we can make our products even better. 
                
                <br><br>
                This survey only takes <b style="font-size: 16px;">3 minutes</b>. We promise to take your feedback into consideration so we can deliver an even more amazing experience next time.
                <br><br>
                Take this survey and get a <b style="color: #46cbd8;">$5 cash</b> coupon as a token of our appreciation. 
                </p>
                
                <a style="width: 190px; line-height: 40px; color: #fff; font-size: 14px; margin-bottom: 20px;
                cursor: pointer; display: inline-block; text-decoration: none; border-radius: 20px;
                box-shadow: rgba(60,172,218,0.2) 0 10px 15px; background: #46c9d8;" href="https://www.naipocare.com/pages/customer-experience-survey?key='. $private_key .'&utm_source=survey&utm_medium=email&utm_campaign=5_coupon">
                TAKE ME THERE >></a>
                <p style="font-size: 14px; line-height: 14px; margin: 0 0 20px; padding: 0; color: #bbb;">*This survey will be valid for one month. So, fill it out soon!</p>
            </td>
        </tr>
        
        <tr style="background:#a2a2a2">
            <td style="width: 100%; max-width: 600px; padding: 0 5% 10px; margin: 0; text-align:center; color:#ffffff; line-height: 22px; font-size: 12px;">
                <p style="padding: 20px 0 0; margin: 0; text-align:center">
                    <a href="https://www.facebook.com/Naipocare" target="_blank"><img src="http://opencart.naipocare.com/catalog/view/theme/default/image/edm/201801-3600/fb.png" style="width: 32px;" alt=""></a>
                    <a href="https://www.youtube.com/channel/UCDz04SR5zZMQyX_JJE3vR_w" target="_blank" style="margin-left:10px;"><img src="http://opencart.naipocare.com/catalog/view/theme/default/image/edm/201801-3600/yt.png" style="width: 32px;" alt=""></a>
                    <a href="https://twitter.com/naipocare" target="_blank" style="margin-left:10px;"><img src="http://opencart.naipocare.com/catalog/view/theme/default/image/edm/201801-3600/tw.png" style="width: 32px;" alt=""></a>
                </p>
                To ensure your naipocare emails are always received sucessfully <br> add
                <a href="mailto:info@naipocare.com" style="color:#ffffff;text-decoration: underline;">info@naipocare.com</a> to your Safe Senders list.<br><span> This email was intended for ' . $email . ' </span>
                <a href="https://opencart.naipocare.com/unsubscribe.html?token=' . $email . '&campaign=' . $type . '" style="color:#ffffff; text-decoration:underline;">Unsubscribe.</a>
                <br>
                <a href="https://www.naipocare.com/pages/privacy-policy" target="_blank" style="color:#ffffff;text-decoration: underline;">Privacy Policy</a> |
                <a href="https://www.naipocare.com/pages/terms-conditions" target="_blank" style="color:#ffffff;text-decoration: underline;">Terms of Use</a> |
                <a href="https://www.naipocare.com/pages/contact-us" target="_blank" style="color:#ffffff;text-decoration: underline;">Contact Us</a>				
            </td>
        </tr>
    </table>
</body>
</html>';
    }

    /**
     * 每天更新物流签收状态和时间
     */
    public function getAllEveryDayTrackInfo()
    {
        set_time_limit(0);
        ini_set("memory_limit", "-1");

        $this->load->model('com/common');
        //获取需要更新的数据
        $trackInfoSql = "SELECT * FROM `" . DB_PREFIX . "survery_email_record` WHERE `status` = 1 AND `submission_cn_at` = 0 AND track_info_at + interval_day * 3600 * 24 <= '" . time() . "' ORDER BY `track_info_at` ASC LIMIT 4";
        $trackInfoData = $this->db->query($trackInfoSql)->rows;
        var_dump($trackInfoData);
        foreach ($trackInfoData as $row)
        {
            if(!empty($row['track_info']))
            {
                $trackInfoArr = json_decode($row['track_info'],true);
                $maxTime = [];
                $expiredFlag = false;
                foreach ($trackInfoArr as $value)
                {
                    // 阿里云快递查询
                    $trackInfo = $this->getAliyunTrackInfo(strtolower($value['com']), $value['track_number']);
                    var_dump($trackInfo);
                    if(isset($trackInfo['showapi_res_body']) && $trackInfo['showapi_res_code'] == 0)
                    {
                        $expiredFlag = false;
                        if ($trackInfo['showapi_res_body']['status'] == 4 && !empty($trackInfo['showapi_res_body']['data'])) $maxTime[] = $trackInfo['showapi_res_body']['data'][0]['time'];
                        // 判定跟踪号已过期
                        if($trackInfo['showapi_res_body']['status'] == 1) $expiredFlag = true;
                    }
                }
                //签收-更新签收时间
                if(!empty($maxTime))
                {
                    $lastSubmission  = max($maxTime);
                    $updateArr = [
                        'submission_format' => $lastSubmission,
                        'submission_cn_at' => strtotime($lastSubmission),
                    ];
                }else
                {
                    $updateArr = [
                        'track_info_at' => time(),
                        'interval_day' => 2,
                    ];
                    // 判定跟踪号已过期
                    if ($expiredFlag) $updateArr['status'] = 3;
                }

                // 更新到数据表
                $updateSet = '';
                foreach ($updateArr as $key => $val)
                {
                    $updateSet .= $key . "='" . $val . "', ";
                }
                $updateSet = trim($updateSet, ', ');
                $this->db->query("UPDATE " . DB_PREFIX . "survery_email_record SET " . $updateSet . " WHERE id = '" . (int)$row['id'] . "'");
                sleep(5);
            }
        }
    }

    /**
     * @desc 快递物流信息查询
     * @param $com 快递公司编码
     * @param $num 快递单号
     * @return array
     * @desc 暂时用金蝶快递100接口，等待确认使用哪种方法，方案确定后再更换第三方接口
     */
    private function getJindieTrackInfo($com,$num)
    {
        //参数设置
        $key = 'ISKqeium5530';	//客户授权key
        $customer = '0F5FD75C187CFB3C946******47B0E';	//查询公司编号
        $param = array (
//            'com' => 'ups',			//快递公司编码
//            'num' => '1Z81F81Y0315339616',	//快递单号
            'com' => $com,			//快递公司编码
            'num' => $num,	//快递单号
            'phone' => '',				//手机号
            'from' => '',				//出发地城市
            'to' => '',					//目的地城市
            'resultv2' => '1'			//开启行政区域解析
        );

        //请求参数
        $post_data = array();
        $post_data["customer"] = $customer;
        $post_data["param"] = json_encode($param);
        $sign = md5($post_data["param"].$key.$post_data["customer"]);
        $post_data["sign"] = strtoupper($sign);

        $url = 'http://poll.kuaidi100.com/poll/query.do';	//实时查询请求地址

        $params = "";
        foreach ($post_data as $k=>$v) {
            $params .= "$k=".urlencode($v)."&";		//默认UTF-8编码格式
        }
        $post_data = substr($params, 0, -1);
//echo '请求参数<br/>'.$post_data;

        //发送post请求
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $post_data);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        $result = curl_exec($ch);
        $data = str_replace("\"", '"', $result );
        $data = json_decode($data,true);

        return $data;
    }

    /**
     * 阿里云实时快递返回物流结果
     * @param string $com 快递公司编码
     * @param string $num 快递单号
     * @desc "status": 4,-1 待查询 0 查询异常 1 暂无记录 2 在途中 3 派送中 4 已签收 5 用户拒签 6 疑难件 7 无效单
    8 超时单 9 签收失败 10 退回
     *"ret_code": 0,//接口调用是否成功,0为成功,其他为失败
     * "showapi_res_code": 0,//showapi平台返回码,0为成功,其他为失败
     * @return array
     */
    private function getAliyunTrackInfo($com,$num)
    {
//        $com = 'ups';
//        $nu = '1ZE15W320363745308';
        $host = "https://kuaidi123.market.alicloudapi.com";
        $path = "/showapi_expInfo";
        $method = "GET";
        $appcode = "ba36866b***922c";//appcode
        $headers = array();
        array_push($headers, "Authorization:APPCODE " . $appcode);
        $querys = "com=" .$com. "&nu=" . $num;
        $bodys = "";
        $url = $host . $path . "?" . $querys;

        $curl = curl_init();
        curl_setopt($curl, CURLOPT_CUSTOMREQUEST, $method);
        curl_setopt($curl, CURLOPT_URL, $url);
        curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
        curl_setopt($curl, CURLOPT_FAILONERROR, false);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($curl, CURLOPT_HEADER, false);//不需要头部信息，直接返回数据设置为false
        if (1 == strpos("$".$host, "https://"))
        {
            curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
            curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
        }
        $result = curl_exec($curl);
        curl_close($curl);
        $data = json_decode($result,true);

        return $data;
    }



}

坑的解决

1.熟悉官方文档