| Server IP : 210.245.233.93 / Your IP : 216.73.216.226 Web Server : Apache/2.2.15 (CentOS) System : Linux webserver2.onesolution.com.hk 2.6.32-754.35.1.el6.x86_64 #1 SMP Sat Nov 7 12:42:14 UTC 2020 x86_64 User : apache ( 48) PHP Version : 5.3.3 Disable Function : exec, shell_exec, system, passthru, popen, proc_open, pcntl_exec MySQL : ON | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /var/www/onesupportsys.onesolution.hk/cron_job/ |
Upload File : |
<?php
require_once('../inc/configure.php');
//require_once(__DIR__ . '/../checkuser.php');
// Send job closed mail
$nowdate = date("Y-m-d");
//$nowdate = date("2015-01-01");
$email_num = 0;
$email_to_list = array();
$job_info_admin = array(array());
$sql5 = "select * from sup_task_category where deleted = 0 order by sort ASC";
if (!($sth5 = $dbh->prepare($sql5))) {
throw new Exception("sql prepare statement failure: $sql5");
}
if (!$sth5->execute()) {
throw new Exception("sql execute statement failure: $sql5");
}
$task_categories = $sth5->fetchAll();
$sql = "SELECT job.*, staff.* FROM sup_job job, sys_login staff WHERE job.staff_id = staff.id and job.`date` = ? and job.actived = ? and job.deleted = ? group by job.staff_id order by staff.username ASC";
$parameters = array($nowdate, "1", "0");
if (!($sth = $dbh->prepare($sql))) {
throw new Exception("sql prepare statement failure: $sql");
}
$sth->setFetchMode(PDO::FETCH_ASSOC);
if (!$sth->execute($parameters)) {
throw new Exception("sql execute statement failure: $sql");
}
$staff_job_list = $sth->fetchAll();
//generate the email content and send email to each staff who has jobs today
foreach($staff_job_list as $key => $staff_job){
ob_start();
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>One Solution: Support</title>
<meta name="author" content="One Solution Limited" />
<style type="text/css">
th { text-align: left; }
.green { color: green; }
.red { color: red; }
.yellow { color: gold; }
</style>
</head>
<body>
<?php //foreach($staff_job_list as $key => $staff_job){ ?>
<div>
<p>This is a auto system e-mail for Job Date: <span style="font-size: 18px;font-weight: bold;"><?=date("Y-m-d H:i:s")?></span></p>
<hr />
<p>The following information is the job:</p>
<table>
<tbody>
<?php
$sql2 = "select job.*,customer.* from sup_job job, v_cm_customer_support customer where job.customer_id = customer.cust_id and job.staff_id = ? and job.`date` = ? and job.actived = ? and job.deleted = ? order by job.start_time ASC";
$parameters2 = array($staff_job["staff_id"],$nowdate, "1", "0");
if (!($sth2 = $dbh->prepare($sql2))) {
throw new Exception("sql prepare statement failure: $sql2");
}
$sth2->setFetchMode(PDO::FETCH_ASSOC);
if (!$sth2->execute($parameters2)) {
throw new Exception("sql execute statement failure: $sql2");
}
$job_list = $sth2->fetchAll();
foreach($job_list as $key2 => $this_job){
$job_info_admin[$this_job["id"]]["username"] = $staff_job['username'];
$job_info_admin[$this_job["id"]]["company_name"] = $this_job["company_name"];
$job_info_admin[$this_job["id"]]["start_time"] = $this_job["start_time"];
$job_info_admin[$this_job["id"]]["end_time"] = $this_job["end_time"];
//looping the job of this staff in today
/*if(($key2+1)%2 == 0)
$row_color = "style='background-color: #E0E0E0;'";
else{*/
$row_color = "style='background-color: white;'";
//}
?>
<tr valign="top" <?=$row_color?>>
<td style="padding-right: 20px;border: 1px solid #000000;min-width: 400px;max-width: 600px;">
<table>
<tr>
<th>Jod ID</th>
<td>:</td>
<td><a href="http://www.onesolution.com.hk/onesupport/job/modifyform.php?id=<?=$this_job["id"]?>" target="_blank">#<?=$this_job["id"]?></a></td>
</tr>
<tr>
<th>Staff</th>
<td>:</td>
<td><?=h($staff_job['username'])?></td>
</tr>
<tr>
<th>Job Date</th>
<td>:</td>
<td><?php echo h($staff_job['date']);?></td>
</tr>
<?php if(empty($this_job["contract_id"]) && empty($this_job["project_id"])){ ?>
<tr>
<th>Job Type</th>
<td>:</td>
<td>Normal Job</td>
</tr>
<?php } ?>
<?php if(!empty($this_job["contract_id"])){ ?>
<tr>
<th>Job Type</th>
<td>:</td>
<td>Contract Job (Monthly Job)</td>
</tr>
<?php } ?>
<?php if(!empty($this_job["project_id"])){ ?>
<tr>
<th>Job Type</th>
<td>:</td>
<td>Project Job</td>
</tr>
<?php } ?>
<tr>
<th valign="top" style="width: 110px;">Customer Name</th>
<td valign="top">:</td>
<td><?php echo $this_job["company_name"];?></td>
</tr>
<tr>
<th valign="top">Location</th>
<td valign="top">:</td>
<td style="word-wrap: break-word;width: 330px;">
<?php
if(empty($this_job["location"]))
echo $this_job["billaddress"];
else
echo $this_job["location"];
?>
</td>
</tr>
<?php
if(!empty($this_job["project_id"])){
$sql3 = "select * from sup_project where id = ?";
if (!($sth3 = $dbh->prepare($sql3))) {
throw new Exception("sql prepare statement failure: $sql3");
}
if (!$sth3->execute(array($this_job["project_id"]))) {
throw new Exception("sql execute statement failure: $sql3");
}
$project = $sth3->fetch(PDO::FETCH_ASSOC);
?>
<tr>
<th>Project Name</th>
<td>:</td>
<td><?=$project["project_name"]?></td>
</tr>
<tr>
<th>Project On-Site</th>
<td>:</td>
<td>
<?php
if($this_job["on_site"] == 1)
echo "Yes";
else
echo "No";
?>
</td>
</tr>
<?php } ?>
<tr>
<th>Start Time</th>
<td>:</td>
<td><?=$this_job["start_time"]?></td>
</tr>
<tr>
<th>End Time</th>
<td>:</td>
<td><?=$this_job["end_time"]?></td>
</tr>
<?php if(empty($this_job["project_id"])){ ?>
<tr>
<th>Call Type</th>
<td>:</td>
<td><?=Job::typeOptions2($this_job["type"]);?></td>
</tr>
<?php } ?>
<tr>
<th>Remarks</th>
<td>:</td>
<td><?=$this_job["remarks"];?></td>
</tr>
</table>
</td>
<?php
//select the tasks which are normal job or project job
//1 to 1 connect
$sql4 = "select *, task.remarks as task_remarks, task.status as task_status from sup_job_detail task, sup_job job, sup_task_category task_category where task.task_category_id = task_category.task_category_id and task.job_id = job.id and job.id=?";
if (!($sth4 = $dbh->prepare($sql4))) {
throw new Exception("sql prepare statement failure: $sql4");
}
if (!$sth4->execute(array($this_job["id"]))) {
throw new Exception("sql execute statement failure: $sql4");
}
$tasks = $sth4->fetchAll();
foreach($tasks as $key3 => $task){
$key3++;
//looping each task
?>
<td style="border: 1px solid #000000;min-width: 300px;max-width: 400px;">
<table>
<tr>
<th>#<?=$key3?></th>
<th style="width: 105px;">Task Category</th>
<td>:</td>
<td><?=$task["category_name"]?></td>
</tr>
<tr>
<td></td>
<th valign="top">Task Title</th>
<td valign="top">:</td>
<td style="width: 170px;"><?=$task["title"]?></td>
</tr>
<tr>
<td></td>
<th>Task Remarks</th>
<td colspan="2">:</td>
<td></td>
</tr>
<tr>
<td></td>
<td colspan="3" style="width:270px;"><?=$task["task_remarks"]?></td>
</tr>
</table>
</td>
<?php }
//for contract job monthly task
$sql4 = "select * from sup_period_task where customer_id = ? and deleted =? and status != ? and ? >= start_date and ? <= end_date order by start_date";
$parameters = array($this_job["customer_id"], "0", "3", date("Y-m-d"), date("Y-m-d"));
if (!($sth4 = $dbh->prepare($sql4))) {
throw new Exception("sql prepare statement failure: $sql4");
}
if (!$sth4->execute($parameters)) {
throw new Exception("sql execute statement failure: $sql4");
}
$period_tasks = $sth4->fetchAll();
foreach($period_tasks as $key3 => $ptask){
$key3++;
?>
<td style="border: 1px solid #000000;min-width: 300px;">
<table>
<tr>
<th>#<?=$key3?></th>
<th style="width: 105px;">Task Category</th>
<td>:</td>
<td>
<?php
foreach ($task_categories as $key4 => $task_category) {
if($ptask["task_category_id"] == $task_category["task_category_id"])
echo $task_category["category_name"];
}
?>
</td>
</tr>
<tr>
<td></td>
<th valign="top">Task Title</th>
<td valign="top">:</td>
<td style="width: 170px;"><?=$ptask["task_title"]?></td>
</tr>
<tr>
<td></td>
<th>Task Remarks</th>
<td colspan="2">:</td>
<td></td>
</tr>
<tr>
<td></td>
<td colspan="3" style="width:270px;"><?=$ptask["task_remarks"]?></td>
</tr>
</table>
</td>
<?php
}
?>
</tr>
<?php } ?>
</table>
</div>
<?php //} ?>
</body>
</html>
<?php //}
$body = ob_get_contents();
ob_end_clean();
$mailer = createPHPMailer();
$mailer->WordWrap = 50;
$mailer->From = 'noreply@onesolution.com.hk';
$mailer->FromName = 'One Solution Limited';
$email_addresses = array(
//'kelvinchan@onesolution.com.hk',
//send email to staff
$staff_job["email"],
);
foreach ($email_addresses as $email_address) {
if (!empty($email_address)) {
$mailer->AddAddress($email_address);
}
}
$mailer->Subject = 'One Support - '.$nowdate." - Job Announcement (".$staff_job["username"].")";
$mailer->Body = $body;
if (!$mailer->Send()) {
throw new Exception(sprintf('Send mail failure: %s', $mailer->ErrorInfo));
$sql5 = "insert into email_log (email_type, email_to, email_sent, sent_time) values (?, ?, ?, ?)";
if (!($sth5 = $dbh->prepare($sql5))) {
throw new Exception('[' . $sth5->errorCode() . ']: ' . print_r($sth5->errorInfo()));
}
$parameters5 = array("Daily Job Email", $staff_job["username"], "0", date("Y-m-d H:i:s"));
if (!$sth5->execute($parameters5)) {
throw new Exception('[' . $sth5->errorCode() . ']: ' . print_r($sth5->errorInfo()));
}
}else{
//send email successfully
$email_num++;
$email_to_list[] = $staff_job["username"];
$sql5 = "insert into email_log (email_type, email_to, email_sent, sent_time) values (?, ?, ?, ?)";
if (!($sth5 = $dbh->prepare($sql5))) {
throw new Exception('[' . $sth5->errorCode() . ']: ' . print_r($sth5->errorInfo()));
}
$parameters5 = array("Daily Job Email", $staff_job["username"], "1", date("Y-m-d H:i:s"));
if (!$sth5->execute($parameters5)) {
throw new Exception('[' . $sth5->errorCode() . ']: ' . print_r($sth5->errorInfo()));
}
}
}
//email simple summary for admin
$mailer2 = createPHPMailer();
$mailer2->WordWrap = 50;
$mailer2->From = 'noreply@onesolution.com.hk';
$mailer2->FromName = 'One Solution Limited';
$email_addresses2 = array(
'samuel@onesolution.com.hk',
'jonathan@onesolution.com.hk',
'skycheng@onesolution.com.hk',
//'kelvinchan@onesolution.com.hk',
);
foreach ($email_addresses2 as $email_address) {
if (!empty($email_address)) {
$mailer2->AddAddress($email_address);
}
}
$mailer2->AddBCC('kelvinchan@onesolution.com.hk');
$mailer2->Subject = 'One Support - '.$nowdate." - Send Job Email";
/*$body2 = "<html><body>
This is a auto system e-mail for Job Date: <b>".date("Y-m-d H:i:s")."</b><br><br>
The system has been sent ".$email_num." email to staffs.<br><br>
They are ".implode(", ",$email_to_list)."
</body>
</html>
";*/
$body2 = "<html><body>
This is a auto system e-mail for Job Date: <b>".date("Y-m-d H:i:s")."</b><br><br>
The system has been sent ".$email_num." email to staffs.<br><br>
They are ".implode(", ",$email_to_list).".<br><br>";
$body2 .= "<table border='1' cellpadding='8' style='border-collapse: collapse;'><tr>
<th style='border: 1px solid black;'>Job ID</th>
<th style='border: 1px solid black;'>Staff Name</th>
<th style='border: 1px solid black;'>Start Time</th>
<th style='border: 1px solid black;'>End Time</th>
<th style='border: 1px solid black;'>Company Name</th>
</tr>";
foreach($job_info_admin as $key => $job_info){
if(!empty($job_info)){
$body2 .= "<tr>
<td style='border: 1px solid black;'><a href='http://www.onesolution.com.hk/onesupport/job/modifyform.php?id=".$key."' target='_blank'>#".$key."</a></td>
<td style='border: 1px solid black;'>".$job_info["username"]."</td>
<td style='border: 1px solid black;'>".$job_info["start_time"]."</td>
<td style='border: 1px solid black;'>".$job_info["end_time"]."</td>
<td style='border: 1px solid black;' >".$job_info["company_name"]."</td>
</tr>";
}
}
$body2 .= "</table></body></html>";
$mailer2->Body = $body2;
if (!$mailer2->Send()) {
throw new Exception(sprintf('Send mail failure: %s', $mailer2->ErrorInfo));
$sql5 = "insert into email_log (email_type, email_to, email_sent, sent_time) values (?, ?, ?, ?)";
if (!($sth5 = $dbh->prepare($sql5))) {
throw new Exception('[' . $sth5->errorCode() . ']: ' . print_r($sth5->errorInfo()));
}
$parameters5 = array("Daily Job Email", "Samuel, Jonathan, Sky", "0", date("Y-m-d H:i:s"));
if (!$sth5->execute($parameters5)) {
throw new Exception('[' . $sth5->errorCode() . ']: ' . print_r($sth5->errorInfo()));
}
}else{
echo "Job Date: ".date("Y-m-d H:i:s")." cron job email sent successfully!";
$sql5 = "insert into email_log (email_type, email_to, email_sent, sent_time) values (?, ?, ?, ?)";
if (!($sth5 = $dbh->prepare($sql5))) {
throw new Exception('[' . $sth5->errorCode() . ']: ' . print_r($sth5->errorInfo()));
}
$parameters5 = array("Daily Job Email", "Samuel, Jonathan, Sky", "1", date("Y-m-d H:i:s"));
if (!$sth5->execute($parameters5)) {
throw new Exception('[' . $sth5->errorCode() . ']: ' . print_r($sth5->errorInfo()));
}
}
?>