合同邮件原生发送+按照10分钟，20分钟，30分钟邮件发送+并记录日记 + 数据库连接封装
//合同邮件原生发送
<?php require_once __DIR__ . '/../../../api/v1/vendor/autoload.php'; require_once __DIR__ . '/../../../api/src/autoload.php'; use Shared\DB; use Monolog\Logger; use Monolog\Handler\StreamHandler; $log = new Logger('contract'); $log->pushHandler(new StreamHandler(__DIR__ . '/logs/_push_email_send.' . date('Y-m') . '.log')); require_once __DIR__ . '/../../../config/_db_connect_admin_read.php'; require_once __DIR__ . '/../../../functions/_email_send.php'; $PARAM_DB_port = '3306'; $DB_admin = $DB_connection_admin; $micSiteQuery = $DB_admin->prepare(" SELECT mic_site.*, host_address AS site_host FROM mic_site LEFT JOIN mic_host ON mic_host.host_id = mic_site.site_host_id WHERE site_status = 1 AND site_hourly_release = 1 ORDER BY site_id"); if (!$micSiteQuery->execute()) { $log->error('Database error ' . implode(' ', array_slice($micSiteQuery->errorInfo(), 2))); exit(); } $pdo = new DB(); while ($dbc = $micSiteQuery->fetch(PDO::FETCH_OBJ)) { $DB = $pdo->connect([ 'host' => $dbc->site_host, 'port' => $PARAM_DB_port, 'dbname' => 'mic' . $dbc->site_id, 'charset' => $dbc->charset, 'username' => 'mic' . $dbc->site_id, 'password' => $dbc->site_password, ]); $log->info('site_id: ' . $dbc->site_id); $DB->beginTransaction(); try { $setUser = $DB->prepare("SET @eamic_user = 'send email';"); $setUser->execute(); //获取到点推送的合同数据 $micContractQuery = $DB->prepare("SELECT contract_id, contract_code, contract_name, location_name, contract_start_date, contract_end_date, contract_description, contract_creator, contract_ff_1, // 当前时间 contract_ff_2 //下一次发送的时间 FROM pur_contract LEFT JOIN asset_location on pur_contract.contract_location_id = asset_location.location_id LEFT JOIN pur_supplier on contractor_id = pur_supplier.supplier_id WHERE contract_ff_2 = date_format(date_add(NOW(), INTERVAL 7 DAY),'%Y-%m-%d') AND contract_ff_7 <> '已结束';"); if (!$micContractQuery->execute()) { $log->error('获取合同推送数据异常：' . implode(' ', array_slice($micContractQuery->errorInfo(), 2))); exit(); } if ($micSiteQuery->rowCount() == 0) { $log->error('未获取到合同数据'); exit(); } $contractLists = $micContractQuery->fetchAll(PDO::FETCH_OBJ); // 找见推送人的邮箱 $employeeQuery = $DB->prepare( 'SELECT employee_email FROM admin_employee WHERE employee_code = :employee_code;' ); $insertContract = $DB->prepare( 'INSERT INTO pur_contract_freetable_1(contract_id,contract_ft1_field_1)VALUES (:contract_id, :now);' ); foreach ($contractLists as $contractList) { // 获取员工code $employeeCode = explode(' ', $contractList->contract_creator); // 进行绑定 $employeeQuery->bindValue(':employee_code', $employeeCode[0]); // 进行查询 if (!$employeeQuery->execute()) { $log->error(implode(' ', array_slice($employeeQuery->errorInfo(), 2))); exit(); } // 获取员工信息 $employeeInfo = $employeeQuery->fetch(PDO::FETCH_OBJ); // 进行判断员工邮箱是否为空的 $email_receiver = $employeeInfo->employee_email ?? ''; // 如果不是空的，进行推送邮件 if (!empty($email_receiver)) { // 下次执行时间（当前时间+几分钟活执行的秒） $nextDate = date('Y-m-d', strtotime($contractList->contract_ff_2) + $contractList->contract_ff_1 * 24 * 3600); $email_subject = "关于{$contractList->contract_code}合同待催函"; // Keep that title so that it will generate some logs $email_message = "您的合同 {$contractList->contract_code}--{$contractList->contract_name} 下次开票日期是{$nextDate} 开票间隔天数{$contractList->contract_ff_1},请尽快安排开票事宜!"; $email_message_alt = '合同推送email: ' . date('Y-m-d'); send_email($email_receiver, $email_subject, $email_message, $email_message_alt); } //发送邮件成功并且更新合同的下一个提醒日期 $updateQuery = $DB->prepare( 'UPDATE pur_contract SET contract_ff_2 = date_add(:contract_ff_2,INTERVAL :contract_ff_1 DAY) WHERE contract_id = :contract_id;' ); $updateQuery->bindValue(':contract_ff_2', $contractList->contract_ff_2); $updateQuery->bindValue(':contract_ff_1', $contractList->contract_ff_1); $updateQuery->bindValue( ':contract_id', $contractList->contract_id, PDO::PARAM_INT ); // 执行 if (!$updateQuery->execute()) { $log->error('更新下次推送时间失败：' . implode(' ', array_slice($updateQuery->errorInfo(), 2))); exit(); } // 绑定参数 $insertContract->bindValue(':contract_id', $contractList->contract_id, PDO::PARAM_INT); $insertContract->bindValue(':now', date('Y-m-d')); //提醒开票日 if (!$insertContract->execute()) { $log->error('记录开票信息失败：' . implode(' ', array_slice($insertContract->errorInfo(), 2))); exit(); } // 记录日记 $log->info("合同{$contractList->contract_code}同步成功"); } $DB->commit(); } catch (Exception $ex) { $DB->rollback(); $log->error('失败信息为：' . $ex->getMessage()); echo '失败,原因为：' . $ex->getFile() . $ex->getLine() . $ex->getMessage(); die(); } }
2:隔时发送邮件：
<?php use Monolog\Logger; use Monolog\Handler\StreamHandler; require __DIR__ . "/../../../../api/v1/vendor/autoload.php"; require __DIR__ . "/../../../../api/src/autoload.php"; require __DIR__ . '/YuanHan_DB_connect.php'; require __DIR__ . '/../../../../functions/_email_send.php'; $log = new Logger('log'); $month = date('Y-m'); $log->pushHandler(new StreamHandler(__DIR__ . '/log/21478log' . $month . '.log', Logger::DEBUG)); $setUser = $DB->prepare("SET @eamic_user = 'email';"); // 找出当前时间减去发送邮件的时间大于10分钟的数据。 $select_query = $DB->prepare(" SELECT wo_time_id, TIMESTAMPDIFF(MINUTE,send_time,NOW()) AS time FROM wo_send "); if (!$select_query->execute()) { throw new Exception(errorInfo($select_query)); } // 删除wo_send表中当前时间减去发送邮件的时间超过10分钟的数据 $delete_query = $DB->prepare(" DELETE FROM wo_send WHERE send_time <= DATE_SUB( NOW(), INTERVAL 10 MINUTE) AND wo_time_id = :wo_time_id "); while ($delete_result = $select_query->fetch(PDO::FETCH_OBJ)) { if ($delete_result->time > 10) { $log->info('大于10分钟的wo_id为' . $delete_result->wo_time_id); $delete_query->bindValue(':wo_time_id', $delete_result->wo_time_id); if (!$delete_query->execute()) { throw new Exception(errorInfo($role_three_query)); } } $log->info('wo_time_id为：' . $delete_result->wo_time_id . '没到要删除的时间'); } // 资产类型 $type_query = $DB->prepare(" SELECT type_id FROM mic_type WHERE type_name = '消防报警' "); if (!$type_query->execute()) { throw new Exception(errorInfo($type_query)); } $type_result = $type_query->fetch(PDO::FETCH_OBJ); $minutes_query = $DB->prepare(" SELECT wo_id , wo_name, wo_creation_time, asset_location.location_name AS location_name, wo_status, TIMESTAMPDIFF(MINUTE,wo_creation_time,NOW()) AS time FROM wo_list INNER JOIN asset_list ON wo_list.wo_asset_id = asset_list.asset_id INNER JOIN asset_location ON asset_list.location_id = asset_location.location_id WHERE wo_status < 6 AND wo_type_id =:wo_type_id "); $minutes_query->bindvalue(':wo_type_id', $type_result->type_id, PDO::PARAM_INT); if (!$minutes_query->execute()) { throw new Exception(errorInfo($twenty_minutes_query)); } //获取 角色是3 的邮箱，手机号，进行推送 $role_three_query = $DB->prepare(" SELECT employee_name, employee_phone, employee_email FROM admin_employee LEFT JOIN asset_location a on a.location_id=admin_employee.employee_location_id INNER JOIN (select asset_location.location_id,location_lft,location_rgt FROM asset_location LEFT JOIN asset_list on asset_list.location_id=asset_location.location_id LEFT JOIN wo_list on wo_list.wo_asset_id=asset_list.asset_id WHERE wo_id=:wo_id) b on b.location_lft between a.location_lft and a.location_rgt AND employee_role = 3 "); // 获取角色2 的邮箱，手机号，进行推送 $role_two_query = $DB->prepare(" SELECT employee_name, employee_phone, employee_email FROM admin_employee LEFT JOIN asset_location a on a.location_id=admin_employee.employee_location_id INNER JOIN (select asset_location.location_id,location_lft,location_rgt FROM asset_location LEFT JOIN asset_list on asset_list.location_id=asset_location.location_id LEFT JOIN wo_list on wo_list.wo_asset_id=asset_list.asset_id WHERE wo_id=:wo_id) b on b.location_lft between a.location_lft and a.location_rgt AND employee_role = 2 "); // 获取角色1 的邮箱，手机号，进行推送 $role_one_query = $DB->prepare(" SELECT employee_name, employee_phone, employee_email FROM admin_employee LEFT JOIN asset_location a on a.location_id=admin_employee.employee_location_id INNER JOIN (select asset_location.location_id,location_lft,location_rgt FROM asset_location LEFT JOIN asset_list on asset_list.location_id=asset_location.location_id LEFT JOIN wo_list on wo_list.wo_asset_id=asset_list.asset_id WHERE wo_id=:wo_id) b on b.location_lft between a.location_lft and a.location_rgt AND employee_role = 1 "); // 添加员工和工单ID和发送时间 $insertContract = $DB->prepare(" INSERT INTO wo_send( wo_id, employee_email, send_time ) VALUES ( :wo_id, :employee_email, :now); "); // 查询一下是否发送过邮件了 $is_send = $DB->prepare(" SELECT employee_email FROM wo_send WHERE employee_email=:employee_email AND wo_id=:wo_id "); try { while ($result = $minutes_query->fetch(PDO::FETCH_OBJ)) { // 如果当前时间大于10分钟小于20分钟 if ($result->time >= 10 && $result->time < 20) { $role_three_query->bindValue(':wo_id', $result->wo_id); if (!$role_three_query->execute()) { throw new Exception(errorInfo($role_three_query)); } // 进行给角色3的人发送邮件 while ($role_three_result = $role_three_query->fetch(PDO::FETCH_OBJ)) { // 将邮箱和工单号添加wo_send $is_send->bindValue(':employee_email', $role_three_result->employee_email); $is_send->bindValue(':wo_id', $result->wo_id); if (!$is_send->execute()) { $log->info('10分钟邮件employee_email查询失败' . implode(' ', array_slice($is_send->errorInfo(), 2))); exit(); } elseif ($is_send->rowCount() == 0) { // send subject $mail_subject = 'EAMic工单提醒: ' . $result->wo_name . '消防报警'; // send content $content = " 您好：<br><br> 园区 $result->location_name 消防报警超过10分钟未处理，工单号: {$result->wo_id}， 请尽快处理,请登录物业管理系统®查看。<br> 点击 <a href='http://rms.sinooceanlas.com:8080/eamic/index.php'>物业管理系统®系统</a> 查看详情。 <br><br> 祝您开心每一天！<br> "; // 给角色为3的发送邮件 send_email($role_three_result->employee_email, $mail_subject, $content, $content); // 记录时间以及发送的id $log->info('10分钟邮件发送的人以及邮箱' . $role_three_result->employee_email); $insertContract->bindValue(':wo_id', $result->wo_id, PDO::PARAM_INT); $insertContract->bindValue(':employee_email', $role_three_result->employee_email); $insertContract->bindValue(':now', date('Y-m-d H:i:s')); //发送邮件的时间 if (!$insertContract->execute()) { $log->info('10分钟邮件添加失败' . implode(' ', array_slice($insertContract->errorInfo(), 2))); exit(); } } else { $log->info($role_three_result->employee_email . '已经发送过了，不能重复发送'); } } } elseif ($result->time >= 20 && $result->time < 30) { $role_two_query->bindValue(':wo_id', $result->wo_id); if (!$role_two_query->execute()) { throw new Exception(errorInfo($role_two_query)); } while ($role_two_result = $role_two_query->fetch(PDO::FETCH_OBJ)) { $is_send->bindValue(':employee_email', $role_two_result->employee_email); $is_send->bindValue(':wo_id', $result->wo_id); if (!$is_send->execute()) { $log->info('20分钟邮件employee_email查询失败' . implode(' ', array_slice($is_send->errorInfo(), 2))); exit(); } elseif ($is_send->rowCount() == 0) { // send subject $mail_subject = 'EAMic工单提醒: ' . $result->wo_name . '消防报警'; // send content $content = " 您好：<br><br> 园区 $result->location_name 消防报警超过20分钟未处理，请尽快处理,工单号: {$result->wo_id} ， 请登录物业管理系统®查看。<br> 点击 <a href='http://rms.sinooceanlas.com:8080/eamic/index.php'>物业管理系统®系统</a> 查看详情。 <br><br> 祝您开心每一天！<br> "; // 给角色为2的发送邮件 send_email($role_two_result->employee_email, $mail_subject, $content, $content); // 记录时间以及发送的id $log->info('20分钟邮件发送的人以及邮箱' . $role_two_result->employee_email); $insertContract->bindValue(':wo_id', $result->wo_id, PDO::PARAM_INT); $insertContract->bindValue(':employee_email', $role_two_result->employee_email); $insertContract->bindValue(':now', date('Y-m-d H:i:s')); //发送邮件的时间 if (!$insertContract->execute()) { $log->info('20分钟' . implode(' ', array_slice($insertContract->errorInfo(), 2))); exit(); } } else { $log->info($role_two_result->employee_email . '已经发送过了，不能重复发送'); } } } elseif ($result->time >= 30 && $result->time < 40) { $role_one_query->bindValue(':wo_id', $result->wo_id); if (!$role_one_query->execute()) { throw new Exception(errorInfo($role_one_query)); } while ($role_one_result = $role_one_query->fetch(PDO::FETCH_OBJ)) { $is_send->bindValue(':employee_email', $role_one_result->employee_email); $is_send->bindValue(':wo_id', $result->wo_id); if (!$is_send->execute()) { $log->info('10分钟邮件employee_email查询失败' . implode(' ', array_slice($is_send->errorInfo(), 2))); exit(); } elseif ($is_send->rowCount() == 0) { // send subject $mail_subject = 'EAMic工单提醒: ' . $result->wo_name . '消防报警'; // send content $content = " 您好：<br><br> 园区 $result->location_name 消防报警超过30分钟未处理，工单号: $result->wo_id ，请尽快处理,请登录物业管理系统®查看。<br> 点击 <a href='http://rms.sinooceanlas.com:8080/eamic/index.php'>物业管理系统®系统</a> 查看详情。 <br><br> 祝您开心每一天！<br> "; // 给角色为1的发送邮件 send_email($role_one_result->employee_email, $mail_subject, $content, $content); $log->info('30分钟邮件发送的人以及邮箱' . $role_one_result->employee_email); $insertContract->bindValue(':wo_id', $result->wo_id, PDO::PARAM_INT); $insertContract->bindValue(':employee_email', $role_one_result->employee_email); $insertContract->bindValue(':now', date('Y-m-d H:i:s')); //发送邮件的时间 if (!$insertContract->execute()) { $log->info('30分钟' . implode(' ', array_slice($insertContract->errorInfo(), 2))); exit(); } } else { $log->info($role_one_result->employee_email . '已经发送过了，不能重复发送'); } } } else { $log->info("报警消防工单未处理工单ID：" . $result->wo_id); } } } catch (Exception $e) { $log->info("具体错误消息为：" . $e->getMessage()); echo $e->getMessage(); die(); }
数据库连接封装：
1:新建DB_config.php文件
/* Connection to the EAMic database */ $db_host = 'localhost'; $db_port = 3306; // $db_name = 'mic00001'; // $db_user = 'root'; $db_name = 'mic12345'; $db_user = 'admin_read'; $db_pwd = 'matrix11'; /**********************************/
2:新建DB_connect.php 文件
// 设置默认时间 date_default_timezone_set('Asia/Shanghai'); // 进行引入配置数据库参数 require __DIR__ . '/YuanHan_DB_config.php'; // 进行配置 try { $DB = new PDO( 'mysql:host=' . $db_host . ';port=' . $db_port . ';dbname=' . $db_name . ';charset=utf8', $db_user, $db_pwd ); } catch (Exception $e) { echo ' EAMic Error : ' . $e->getMessage() . ' ' . $e->getCode(); die(); }
3: 使用：只需要将DB_connect.php 文件引入到使用的文件就可以使用$DB了。
