1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
|
<?
//require_once("../configure.php");
$dbh = mysql_connect("202.155.243.166", "xtremesa", "xtdbone1q"); // 192.168.11.90
if(!$dbh){
die('Could not connect: '.mysql_errno()." - ".mysql_error());
}
$db_selected = mysql_select_db("xtreme_db");
if(!$db_selected){
die('Can\'t select DB: '.mysql_error());
}
mysql_query("SET NAMES UTF8");
//require($INCLUDE_DIR . "class.phpmailer.php");
//require("class.phpmailer.php");
require("../mail/class.phpmailer.php");
error_reporting(E_ALL);
//-----------------------email alert for oil department -----------------------------------------
$todayDate = date("Y-m-d");// current date
//echo "Today: ".$todayDate."<br>";
//Add one day to today
$date = strtotime(date("Y-m-d", strtotime($todayDate)) . " +1 day");
//echo "After adding one day: ".date('l dS \o\f F Y', $date)."<br>";
//echo date('Y-m-d', $date)."<br>";
$sql = "SELECT
oil_alert.cust_id,
oil_alert.staff_id,
oil_alert.alert_date,
oil_alert_item.name,
oil_alert.detail,
oil_alert.remark,
`user`.email,
oil_alert.id,
oil_cust.co_name
FROM
oil_alert
Inner Join `user` ON oil_alert.staff_id = `user`.id
Inner Join oil_alert_item ON oil_alert.alert_item = oil_alert_item.id
Inner Join oil_cust ON oil_alert.cust_id = oil_cust.id
where oil_alert.status = 1
and oil_alert.alert_date <= '".date('Y-m-d', $date)."'";
//echo $sql;
if(!$result = mysql_query($sql)){
echo mysql_errno()." - ".mysql_error();
exit();
}
while($row = mysql_fetch_assoc($result)){
$id = $row['id'];
$alertdate = $row['alert_date'];
$co_name = $row['co_name'];
$itemname = $row['name'];
$detail = $row['detail'];
$remark = $row['remark'];
$email = $row['email'];
$body = '<HTML>';
$body .= '<HEAD>';
$body .= '<TITLE></TITLE>';
$body .= '<META http-equiv=Content-Type content=text/html; charset=big5>';
$body .= '</HEAD>';
$body .= '<body LINK=#0000ff VLINK=#800080 bgcolor=#FFFFFF leftMargin=10 topMargin=20 marginheight=0 marginwidth=0>';
$body .= 'Alert Date: '.$alertdate;
$body .= '<br>Company: '.$co_name;
$body .= '<br>Detail: '.$detail;
$body .= '<br>Remark: '.$remark;
$body .= '</BODY>';
$body .= '</HTML>';
$x_mail = new PHPMailer();
$x_mail->IsSMTP();
$x_mail->Host = "localhost";
$x_mail->CharSet = "UTF-8";
$x_mail->From = "info@kawadapc.com";
$x_mail->FromName = "Kawadapc";
$x_mail->AddAddress($email);
$x_mail->AddAddress("ray.lau@kawadapc.com");
$x_mail->AddAddress("manfok@onesolution.com.hk");
$x_mail->WordWrap = 50;
$x_mail->IsHTML(true);
$x_mail->Subject = "Alert from CRM System for ". $co_name;
$x_mail->Body = $body;
$x_mail->Send();
echo "<br>Done";
$sql2 = "update oil_alert set status = 2 where id = '".$id."' ";
mysql_query($sql2);
}
mysql_free_result($result);
//-----------------------email alert for garage department -----------------------------------------
$sql = "
SELECT
garage_alert.id,
garage_alert.alert_date,
`user`.email,
garage_car.model_no,
garage_cust.`name` AS customer_name,
garage_cust.gender,
garage_cust.mobile,
garage_car_info.license_plate,
garage_initial_diagnosis_master_itemcat.`name` AS itemcat_name,
garage_initial_diagnosis.report_date
FROM
garage_alert
INNER JOIN garage_initial_diagnosis ON garage_initial_diagnosis.id = garage_alert.diagnosis_id
INNER JOIN `user` ON `user`.id = garage_initial_diagnosis.staff_id
INNER JOIN garage_car_info ON garage_car_info.id = garage_initial_diagnosis.car_info_id
INNER JOIN garage_car ON garage_car_info.car_id = garage_car.id
INNER JOIN garage_cust ON garage_initial_diagnosis.cust_id = garage_cust.id
INNER JOIN garage_initial_diagnosis_master_itemcat ON garage_initial_diagnosis_master_itemcat.id = garage_alert.alertitemcat_id
WHERE
garage_alert.`status` = 1 AND
garage_alert.alert_date <= NOW()
";
//echo $sql;
if(!$result = mysql_query($sql)){
echo mysql_errno()." - ".mysql_error();
exit();
}
while($row = mysql_fetch_assoc($result)){
$body = '<HTML>';
$body .= '<HEAD>';
$body .= '<TITLE></TITLE>';
$body .= '<META http-equiv=Content-Type content=text/html; charset=big5>';
$body .= '</HEAD>';
$body .= '<body LINK=#0000ff VLINK=#800080 bgcolor=#FFFFFF leftMargin=10 topMargin=20 marginheight=0 marginwidth=0>';
$body .= 'Alert Date: '.$row['alert_date'];
$body .= '<br>Customer: '.$row['customer_name'];
$body .= '<br>Tel: '.$row['mobile'];
$body .= '<br>Model: '.$row['model_no'];
$body .= '<br>License Plate: '.$row['license_plate'];
$body .= '<hr>Report Date: '.$row['report_date'];
$body .= '<br>Alert Item Type: '.$row['itemcat_name'];
$body .= '</BODY>';
$body .= '</HTML>';
//var_dump($body); exit;
$x_mail = new PHPMailer();
$x_mail->IsSMTP();
$x_mail->Host = "localhost";
$x_mail->CharSet = "UTF-8";
$x_mail->From = "info@kawadapc.com";
$x_mail->FromName = "Kawadapc";
$x_mail->AddAddress($row['email']);
//$x_mail->AddAddress("ray.lau@kawadapc.com");
$x_mail->AddAddress("garricklam@onesolution.com.hk");
$x_mail->WordWrap = 50;
$x_mail->IsHTML(true);
$x_mail->Subject = "Alert from CRM System for ". $row['customer_name'];
$x_mail->Body = $body;
$x_mail->Send();
$sql2 = "update garage_alert set status = 2 where id = '".$row['id']."' ";
mysql_query($sql2);
}
mysql_free_result($result);
echo 'Done';
?>
|