定时根据物流发送活动邮件,如何做到最省最优?

需求

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

  

技术栈

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

  

数据表设计

    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物流签收并发邮件跟踪表';

  

配置

    #mandrill config
define('SMTP_FROM_EMAIL_MANDRILL', 'NAIPO');
define('SMTP_FROM_PASSWORD_MANDRILL', 'FXHufSPO5K7pw***1GhA');
define('SMTP_FROM_PORT_MANDRILL', '587');
define('SMTP_FROM_HOSTNAME_MANDRILL', 'smtp.mandrillapp.com');

  

代码

    <?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');

//        $email= 't17666108223@gmail.com';
//        $email= 'chan.punlap@gmail.com';
//        $email= 'rebekah_nan@hotmail.com';
//        $email= 'yebinbin@aukeys.com';
//        $email= 'test-62ufhke7l@srv1.mail-tester.com';
//        $email= 'chan.punlap@gmail.com';
//        $params = [
//            'sku_info'=> 'oCuddle-C20',
//            'email'=> $email,
//            'order_number'=> '20969391718840',
//        ];
        $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);
            }
        }
    }


/**
     * 阿里云实时快递返回物流结果
     * @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 = "ba36866b469c45ebbe3c2ec38f13922c";//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.
      熟悉官方文档