微信模板消息发送（修改版）
前几天写了个微信模板发送的功能，但后来测试的时候发现存在一些严重的错误，修复过后，客户又加了个上传Excel表格获取用户openid发送信息的功能，由于之前没接触过，还是挺迷茫的，好在经过加班加点之后，终于搞定了这个功能。以下只是简略版本，重点实现功能，各位看官将就看一下。
//使用此代码前需做以下准备工作：
//1、建立一个名为access_token.php的空php文件
//2、在微信公众平台使用管理员权限设置好ip白名单，并在功能设置中绑定业务域名（此文件所在的网络地址），记得根据提示下载MP_verify_TcfRJJNVAMluRK8C.txt文件放在同级目录下
//3、在数据库中建立一个名为send的表，表中包含id、openid、nickname、groupid字段
//4、建立一个名为log.php的空php文件
//5、下载PHPExcel 1.8版本
//6、建立一个名为next_openid.php的空PHP文件
//7、建立一个名为upload的空文件夹，内放一个名为office的空文件夹
//8、下载layui框架
//9、下载jquery 1.8或以上版本
//本页共有两个PHP文件，send.php为主要文件，upload.php为上传文件，可按需截取
send.php
<meta charset="utf-8"> <?php require("PHPExcel-1.8/Classes/PHPExcel.php"); //引入PHPExcel //数据库封装类（在网上找的，随便改动了一下） class MyPDO { protected static $_instance = null; protected $dbName = ''; protected $dsn; protected $dbh; /** * 构造 * * @return MyPDO */ private function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset) { try { $this->dsn = 'mysql:host=' . $dbHost . ';dbname=' . $dbName; $this->dbh = new PDO($this->dsn, $dbUser, $dbPasswd); $this->dbh->exec('SET character_set_connection=' . $dbCharset . ', character_set_results=' . $dbCharset . ', character_set_client=' . $dbCharset); } catch (PDOException $e) { $this->outputError($e->getMessage()); } } /** * 防止克隆 * */ private function __clone() { } /** * Singleton instance * * @return Object */ public static function getInstance($dbHost = '127.0.0.1', $dbUser = 'hx', $dbPasswd = 'zHedsDp6vwWFJD9m', $dbName = 'hx', $dbCharset = 'utf8mb4') { if (self::$_instance === null) { self::$_instance = new self($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset); } return self::$_instance; } /** * Query 查询 * * @param String $strSql SQL语句 * @param String $queryMode 查询方式(All or Row) * @param Boolean $debug * @return Array */ public function query($strSql, $queryMode = 'All', $debug = false) { if ($debug === true) $this->debug($strSql); $recordset = $this->dbh->query($strSql); $this->getPDOError(); if ($recordset) { $recordset->setFetchMode(PDO::FETCH_ASSOC); if ($queryMode == 'All') { $result = $recordset->fetchAll(); } elseif ($queryMode == 'Row') { $result = $recordset->fetch(); } } else { $result = null; } return $result; } /** * Update 更新 * * @param String $table 表名 * @param Array $arrayDataValue 字段与值 * @param String $where 条件 * @param Boolean $debug * @return Int */ public function update($table, $arrayDataValue, $where = '', $debug = false) { $this->checkFields($table, $arrayDataValue); if ($where) { $strSql = ''; foreach ($arrayDataValue as $key => $value) { $strSql .= ", `$key`='$value'"; } $strSql = substr($strSql, 1); $strSql = "UPDATE `$table` SET $strSql WHERE $where"; } else { $strSql = "REPLACE INTO `$table` (`" . implode('`,`', array_keys($arrayDataValue)) . "`) VALUES ('" . implode("','", $arrayDataValue) . "')"; } if ($debug === true) $this->debug($strSql); $result = $this->dbh->exec($strSql); $this->getPDOError(); return $result; } /** * Insert 插入 * * @param String $table 表名 * @param Array $arrayDataValue 字段与值 * @param Boolean $debug * @return Int */ public function insert($table, $arrayDataValue, $debug = false) { $this->checkFields($table, $arrayDataValue); $strSql = "INSERT INTO `$table` (`" . implode('`,`', array_keys($arrayDataValue)) . "`) VALUES ('" . implode("','", $arrayDataValue) . "')"; if ($debug === true) $this->debug($strSql); $result = $this->dbh->exec($strSql); // $this->getPDOError(); return $result; } /** * Replace 覆盖方式插入 * * @param String $table 表名 * @param Array $arrayDataValue 字段与值 * @param Boolean $debug * @return Int */ public function replace($table, $arrayDataValue, $debug = false) { $this->checkFields($table, $arrayDataValue); $strSql = "REPLACE INTO `$table`(`" . implode('`,`', array_keys($arrayDataValue)) . "`) VALUES ('" . implode("','", $arrayDataValue) . "')"; if ($debug === true) $this->debug($strSql); $result = $this->dbh->exec($strSql); $this->getPDOError(); return $result; } /** * Delete 删除 * * @param String $table 表名 * @param String $where 条件 * @param Boolean $debug * @return Int */ public function delete($table, $where = '', $debug = false) { if ($where == '') { $this->outputError("'WHERE' is Null"); } else { $strSql = "DELETE FROM `$table` WHERE $where"; if ($debug === true) $this->debug($strSql); $result = $this->dbh->exec($strSql); $this->getPDOError(); return $result; } } /** * execSql 执行SQL语句 * * @param String $strSql * @param Boolean $debug * @return Int */ public function execSql($strSql, $debug = false) { if ($debug === true) $this->debug($strSql); $result = $this->dbh->exec($strSql); $this->getPDOError(); return $result; } /** * 获取字段最大值 * * @param string $table 表名 * @param string $field_name 字段名 * @param string $where 条件 */ public function getMaxValue($table, $field_name, $where = '', $debug = false) { $strSql = "SELECT MAX(" . $field_name . ") AS MAX_VALUE FROM $table"; if ($where != '') $strSql .= " WHERE $where"; if ($debug === true) $this->debug($strSql); $arrTemp = $this->query($strSql, 'Row'); $maxValue = $arrTemp["MAX_VALUE"]; if ($maxValue == "" || $maxValue == null) { $maxValue = 0; } return $maxValue; } /** * 获取指定列的数量 * * @param string $table * @param string $field_name * @param string $where * @param bool $debug * @return int */ public function getCount($table, $field_name, $where = '', $debug = false) { $strSql = "SELECT COUNT($field_name) AS NUM FROM $table"; if ($where != '') $strSql .= " WHERE $where"; if ($debug === true) $this->debug($strSql); $arrTemp = $this->query($strSql, 'Row'); return $arrTemp['NUM']; } /** * 获取表引擎 * * @param String $dbName 库名 * @param String $tableName 表名 * @param Boolean $debug * @return String */ public function getTableEngine($dbName, $tableName) { $strSql = "SHOW TABLE STATUS FROM $dbName WHERE Name='" . $tableName . "'"; $arrayTableInfo = $this->query($strSql); $this->getPDOError(); return $arrayTableInfo[0]['Engine']; } /** * beginTransaction 事务开始 */ private function beginTransaction() { $this->dbh->beginTransaction(); } /** * commit 事务提交 */ private function commit() { $this->dbh->commit(); } /** * rollback 事务回滚 */ private function rollback() { $this->dbh->rollback(); } /** * transaction 通过事务处理多条SQL语句 * 调用前需通过getTableEngine判断表引擎是否支持事务 * * @param array $arraySql * @return Boolean */ public function execTransaction($arraySql) { $retval = 1; $this->beginTransaction(); foreach ($arraySql as $strSql) { if ($this->execSql($strSql) == 0) $retval = 0; } if ($retval == 0) { $this->rollback(); return false; } else { $this->commit(); return true; } } /** * checkFields 检查指定字段是否在指定数据表中存在 * * @param String $table * @param array $arrayField */ private function checkFields($table, $arrayFields) { $fields = $this->getFields($table); foreach ($arrayFields as $key => $value) { if (!in_array($key, $fields)) { $this->outputError("Unknown column `$key` in field list."); } } } /** * getFields 获取指定数据表中的全部字段名 * * @param String $table 表名 * @return array */ private function getFields($table) { $fields = array(); $recordset = $this->dbh->query("SHOW COLUMNS FROM $table"); $this->getPDOError(); $recordset->setFetchMode(PDO::FETCH_ASSOC); $result = $recordset->fetchAll(); foreach ($result as $rows) { $fields[] = $rows['Field']; } return $fields; } /** * getPDOError 捕获PDO错误信息 */ private function getPDOError() { if ($this->dbh->errorCode() != '00000') { $arrayError = $this->dbh->errorInfo(); $this->outputError($arrayError[2]); } } /** * debug * * @param mixed $debuginfo */ private function debug($debuginfo) { var_dump($debuginfo); exit(); } /** * 输出错误信息 * * @param String $strErrMsg */ private function outputError($strErrMsg) { throw new Exception('MySQL Error: ' . $strErrMsg); } /** * destruct 关闭数据库连接 */ public function destruct() { $this->dbh = null; } } //公众号模板消息群发 class WxMessage { /** * @return mixed */ //封装数据库连接方法 public function dataBase() { $dbms = 'mysql'; //数据库类型 $host = ''; //数据库主机名 $dbName = ''; //使用的数据库 $user = ''; //数据库连接用户名 $pass = ''; //对应的密码 $dsn = "$dbms:host=$host;dbname=$dbName"; try { $dbh = new PDO($dsn, $user, $pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES'utf8';")); //初始化一个PDO对象 //PDO是连接数据库的方法，其内依次放置 ，用户名，密码，展现格式 } catch (PDOException $e) { die ("Error!: " . $e->getMessage() . "<br/>"); } //处理抛出的异常 return $dbh; } var $appid = ''; //开发者appid var $appsecret = ''; //开发者密钥 //access_token需动态获取，存于access_token.php,access_token会过期，过期后需重新获取(清空线上access_token.php里的内容) //构造函数，获取token; public function __construct($appid = null, $appsecret = null) { if ($appid && $appsecret) { $this->appid = $appid; $this->appsecret = $appsecret; } $this->lasttime = 1406469747; $this->access_token = " ";//动态获取access_token $file = file_get_contents('access_token.php'); $file = json_decode($file, true); if (!$file || $file['expires_time'] < time()) { $url = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=" . $this->appid . "&secret=" . $this->appsecret . ""; $res = $this->https_request($url); $result = json_decode($res, true); $result['expires_time'] = time() + 60; file_put_contents('access_token.php', json_encode($result)); } else { $result = $file; // exit(); } $this->access_token = $result['access_token']; // print_r($this->access_token); } //发送模板消息 public function send_template_message($data) { $url = "https://api.weixin.qq.com/cgi-bin/message/template/send?access_token=" . $this->access_token; $res = $this->https_request($url, $data); $result = json_decode($res, true); echo "<script> alert('发送成功!');location.href='http://rs.xinhuang.net.cn/send.php'; </script>"; } //获取粉丝 public function getMemberList($openid = '') { ini_set('max_execution_time', '0'); $nextOpenid = file_get_contents('next_openid.php'); if (!$openid && $nextOpenid) { $openid = $nextOpenid; } $url = "https://api.weixin.qq.com/cgi-bin/user/get?access_token=" . $this->access_token . "&next_openid=" . $openid; $res = $this->https_request($url); $result = json_decode($res, true); if ($result['count'] == 0) { return true; } foreach ($result['data']['openid'] as $key => $value) { $isCreate = MyPDO::getInstance()->query('select openid from send where openid= "' . $value . '"', 'Row'); // print_r($isCreate); if ($isCreate) { continue; } $userInfo = 'https://api.weixin.qq.com/cgi-bin/user/info?access_token=' . $this->access_token . '&openid=' . $value . '&lang=zh_CN'; $res = $this->https_request($userInfo); $result1 = json_decode($res, true); file_put_contents('log.php', file_get_contents('log.php') . ' ' . $res); $openid = $result1['openid']; $nickname = ''; $groupid = $result1['groupid']; $sql = "insert into send (openid,nickname,groupid) values('$openid','$nickname','$groupid')"; MyPDO::getInstance()->insert('send', array('openid' => $openid, 'nickname' => $nickname, 'groupid' => $groupid)); file_put_contents('next_openid.php', $openid); sleep(0.3); } if ($result['total'] == $result['count']) { return true; } if ($openid != $result['next_openid']) { $this->getMemberList($result['next_openid']); } return true; // return $result; } //https请求(支持GET和POST) protected function https_request($url, $data = null) { $curl = curl_init(); // 初始化一个cURL会话 curl_setopt($curl, CURLOPT_URL, $url); curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, FALSE); curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, $url); if (!empty($data)) { curl_setopt($curl, CURLOPT_POST, 1); curl_setopt($curl, CURLOPT_POSTFIELDS, $data); } curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1); $output = curl_exec($curl); // 抓取 URL 并把它传递给浏览器 curl_close($curl); return $output; } } //执行操作 if (isset($_POST["submit"])) { //添加粉丝进数据库 if (isset($_POST["sen"])) { (new WxMessage())->getMemberList(); } //确认后发送模板消息 if (isset($_POST["sending"])) { $excel = $_POST["excel"]; date_default_timezone_set('PRC'); //Excel上传后读取内容 $PHPExcel = new PHPExcel(); $filePath = $excel; $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { echo 'no Excel'; return; } } $PHPExcel = $PHPReader->load($filePath); /**读取excel文件中的第一个工作表*/ $currentSheet = $PHPExcel->getSheet(0); /**取得最大的列号*/ $allColumn = $currentSheet->getHighestColumn(); /**取得最大的行号*/ $allRow = $currentSheet->getHighestRow(); /**从第二行开始输出，因为excel表中第一行为列名*/ for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { /**从第A列开始输出*/ $val = $currentSheet->getCellByColumnAndRow(3, $currentRow)->getValue(); $data = orderSuccess($val);//把从Excel表里面查出来的openid的值作为一个参数赋给$openid // (new WxMessage())->send_template_message(json_encode($data)); } // //测试时使用以下代码,并注释掉上面的发送 // $data=orderSuccess('');//括号内为用户openid,此处为了不误发信息骚扰用户而使用开发者自己的openid,发送消息成功后,只有开发者本人可以收到信息 // (new WxMessage())->send_template_message(json_encode($data)); } // exit(); } //下单成功通知模板 //* // openid:微信唯一标识 // orderSn:订单号 // goods_name:商品名称 // goods_num:商品数量 // allMoney:总价格 // function orderSuccess($openid = null, $orderSn = null, $remark = null) { $first = $_POST['first']; $activityName = $_POST['activityName']; $activityTime = $_POST['activityTime']; $remark = $_POST['modelarea']; $template = array( "touser" => "$openid", "template_id" => "8W1ofwzIntnvGoZB3kUX5nCf6aiLyL3XvLV8fKHAOdc", 'url' => '',//用户点击详情后的跳转地址 'topcolor' => '#ccc', 'data' => array('first' => array('value' => $first, 'color' => "#743A3A", ), 'keyword1' => array('value' => $activityName, //商品名称 'color' => '#FF0000' ), 'keyword2' => array('value' => "$activityTime", //参加时间 'color' => '#FF0000' ), 'remark' => array('value' => "$remark", //活动描述 'color' => '#FF0000' ), ) ); // echo $template; return $template; // return $remark; } ?> <script type="text/javascript" src="/jquery-1.8.0.min.js"></script> <link rel="stylesheet" href="/layui/css/layui.css"> <script src="/layui/layui.js"></script> <div style="text-align: center;padding: 20px;font-size: 24px;">微信模板消息发送</div> <div style="text-align: center;margin-bottom: 20px;"> <div style="display: inline-block;width: auto;color: white;"> <form action="" name="send" method="post"> <input name="sen" type="hidden" value="1"/> <input style="padding: 10px 20px;" type="submit" name="submit" value="同步粉丝" onclick="alert('加载需要一点时间，请稍等');return;"/> </form> </div> <div style="height: 15px;"></div> <form action="" name="group" method="post"> <div style="display: inline-block;width: auto;"> 开 头 语： <input name="first" type="text" value="" placeholder="请输入开头语" style="width:250px;height: 50px;margin-bottom: -20px;text-align: center;"/> </div> <div style="display: inline-block;width: auto;">  活动名称： <input name="activityName" type="text" value="" placeholder="请输入活动名称" style="width:250px;height: 50px;margin-bottom: -20px;text-align: center;"/> </div> <div style="height: 30px;"></div> <div style="display: inline-block;width: auto;"> 参加时间： <input name="activityTime" type="text" value="" placeholder="请输入活动时间" style="width:250px;height: 50px;margin-bottom: -20px;text-align: center;"/> </div> <div style="display: inline-block;width: auto;"> 通知内容： <textarea name="modelarea" placeholder="请输入通知内容" style="width:250px;height: 50px;margin-bottom: -20px;"></textarea> </div> <div style="height: 30px;"></div> <div style="display: inline-block;width: auto;color: white;margin: 0 40px;"> <div> <div style="display:inline-block;width:50px;color: black;">Excel：</div> <div style="display:inline-block;color: black;"> <input type="text" class="form-control form-boxed" name="excel" style="width:250px;height: 40px;text-align: center;"> <button type="button" class="btn btn-secondary-outline" id="test1" style="padding:10px;text-align: center;"> 上传表格 </button> </div> </div> <script> layui.use(['upload'], function () { var upload = layui.upload; var uploadInst = upload.render({ elem: '#test1' //绑定元素 , url: 'upload.php?ProType=uploadImg' //上传接口 , accept: 'file' , done: function (res) { console.log('yes') //上传完毕回调 var res = res.data; $('[name=excel]').val(res.path); } , error: function () { //请求异常回调 console.log('no') } }); }); </script> </div> <div style="display: inline-block;width: auto;margin: 0 60px;"> <input name="sending" type="hidden" value=" "/> <input id="submit" style="padding: 10px 20px;" type="submit" name="submit" value="发送消息"/> </div> </form> </div> <div style="text-align: center;font-size: 24px;">用户信息（由于用户过多，列表仅显示最后50条）</div> <table class="table table-bordered table-striped table-hover" style="width: 90%;border: 1px solid black;margin: 0 auto;margin-top: 10px;"> <thead style="border: 1px solid black;"> <tr style="border: 1px solid black;"> <th style="border: 1px solid black;">序号</th> <th style="border: 1px solid black;">用户openid</th> <th style="border: 1px solid black;">用户分组</th> </tr> </thead> <tbody> <?php $sql_r = "SELECT * FROM send ORDER BY id DESC limit 50"; foreach ((new WxMessage())->dataBase()->query($sql_r) as $key => $values) { ?> <tr class="cen"> <td style="border: 1px solid black;color: #b2b2b2;text-align: center;margin: 0;"><?php echo $values['id']; ?></td> <td style="border: 1px solid black;color: #b2b2b2;text-align: center;margin: 0;"><?php echo $values['openid']; ?></td> <td style="border: 1px solid black;color: #b2b2b2;text-align: center;margin: 0;"><?php echo $values['groupid']; ?></td> </tr> <?php } ?> </tbody> </table>
upload.php
<?php //本文件用于控制upload文件夹 define('__UPLOAD__','upload'); class Upload { protected $tmpName; protected $name; protected $size; protected $type; protected $file_type; protected $uploadDir; public $dir =__UPLOAD__; public function __construct() { $this->uploadDir = __UPLOAD__; $file= $_FILES['file']; $this->name = $file['name']; $this->tmpName = $file['tmp_name']; $this->size = $file['size']; $this->type = $file['type']; if (!is_dir($this->uploadDir)) { mkdir($this->uploadDir); chmod($this->uploadDir, 0777); } $this->typeDir(); } public function typeDir () { switch ($this->type) { case 'image/jpeg' : case 'image/png' : case 'image/bmp' : $this->file_type = 'img'; $this->makeDir('img'); break; case 'application/vnd.openxmlformats-officedocument.wordprocessingml.document': case 'application/vnd.ms-excel': case 'application/msword': case 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet': $this->file_type = 'office'; $this->makeDir('office'); break; default: } } public function makeDir($type) { $images_dir = $this->uploadDir . '/images'; $office_dir = $this->uploadDir . '/office'; if ($type=='img') { if (!is_dir($images_dir)) { mkdir($images_dir); chmod($images_dir, 0777); } $this->dir = $images_dir; } if ($type=='office') { if (!is_dir($office_dir)) { mkdir($office_dir); chmod($office_dir, 0777); } $this->dir = $office_dir; } } public function move () { $path = pathinfo($this->name); $name =md5($path['filename'].time()) . '.' . $path['extension']; $path_name = $this->dir . '/' . $name; $result= move_uploaded_file($this->tmpName,$path_name); if ($result) { return array('path'=>trim($path_name,'.'),'type'=>$this->file_type,'name'=>$this->name,'id'=>md5(substr(microtime(), 2, 5).rand(0,99))); } return false; } } $result= new Upload(); $result= $result->move(); if ($result) { print_r(json_encode(array('code'=>200,'data'=>$result))); } //print_r($result);
以下为最终效果：
