prepare($query); $stmt->bindValue(':ticket_id', $ticket_id, PDO::PARAM_INT); $stmt->execute(); $ticket = $stmt->fetch(PDO::FETCH_ASSOC); if (empty($ticket)) { throw new Exception('Ticket not found. Ticket ID: ' . $ticket_id); } $message = 'Ticket found. Ticket ID: ' . $ticket['id']; createLog2($pathinfo . $message, 'success'); echo $pathinfo . $message . PHP_EOL; // sms customer if ($is_sms_customer) { smsNotificationCustomer($ticket); } // sms technician if ($is_sms_technician) { smsNotificationTechnician($ticket); } // email if ($is_email) { emailNotification($ticket); } exit; } catch (Exception $e) { $message = $e->getMessage(); echo $pathinfo . $message . PHP_EOL; createLog2($pathinfo . $message, 'error'); exit; } function smsNotificationCustomer($ticket) { global $pathinfo; try { $explode_duration = explode(':', $ticket['technician_eda_duration']); $technician_duration = ''; if (!empty($explode_duration) && is_array($explode_duration)) { if (count($explode_duration) == 3) { $array_duration = []; if ($explode_duration[0] != '00') { array_push($array_duration, "{$explode_duration[0]} Jam"); } if ($explode_duration[1] != '00') { array_push($array_duration, "{$explode_duration[1]} Menit"); } if ($explode_duration[2] != '00') { array_push($array_duration, "{$explode_duration[2]} Detik"); } $technician_duration = implode(" ", $array_duration); } } $content_sms = 'Yth.Bpk/Ibu ' . $ticket['contact_name'] . ', petugas kami Bpk. ' . $ticket['technician_name'] . '/' . $ticket['technician_phone'] . '/SERVICE CAR akan tiba dilokasi dengan perkiraan waktu ' . $technician_duration; $data_sms = [ 'direction_id' => 2, 'sms_status_id' => 3, 'call_type_id' => (int) $ticket['call_type_id'], 'user_id' => (int) $ticket['technician_user_id'], 'inbound_media_id' => 8, 'send_user_id' => (int) $ticket['technician_user_id'], 'queue_time' => date('Y-m-d H:i:s'), 'recipient' => str_replace(' ', '', $ticket['phone_number1']), 'customer_id' => (int) $ticket['customer_id'], 'ticket_id' => (int) $ticket['id'], 'ticket_no' => (int) $ticket['ticket_no'], 'message' => $content_sms ]; $conn = dbconnect(); $data_sms_query = array_map(function($key, $val) use ($conn) { if (empty($val) && $val !== 0) { return $key . ' = NULL'; } return $key . ' = ' . $conn->quote($val); }, array_keys($data_sms), $data_sms); $query = 'INSERT INTO sms_pending SET ' . implode(', ', $data_sms_query); $stmt = $conn->prepare($query); $stmt->execute(); return true; } catch (Exception $e) { $message = $e->getMessage(); echo $pathinfo . $message . PHP_EOL; createLog2($pathinfo . $message, 'error'); return false; } } function smsNotificationTechnician($ticket) { global $pathinfo; try { $content_sms = 'BPK/IBU ' . $ticket['contact_name'] . '-' . $ticket['phone_number1'] . '-' . $ticket['vehicle_type'] . '-' . $ticket['license_plate'] . '-' . $ticket['note_location']; $data_sms = [ 'direction_id' => 2, 'sms_status_id' => 3, 'call_type_id' => (int) $ticket['call_type_id'], 'user_id' => (int) $ticket['technician_user_id'], 'inbound_media_id' => 8, 'send_user_id' => (int) $ticket['technician_user_id'], 'queue_time' => date('Y-m-d H:i:s'), 'recipient' => str_replace(' ', '', $ticket['technician_phone']), 'customer_id' => (int) $ticket['customer_id'], 'ticket_id' => (int) $ticket['id'], 'ticket_no' => (int) $ticket['ticket_no'], 'message' => $content_sms ]; $conn = dbconnect(); $data_sms_query = array_map(function($key, $val) use ($conn) { if (empty($val) && $val !== 0) { return $key . ' = NULL'; } return $key . ' = ' . $conn->quote($val); }, array_keys($data_sms), $data_sms); $query = 'INSERT INTO sms_pending SET ' . implode(', ', $data_sms_query); $stmt = $conn->prepare($query); $stmt->execute(); return true; } catch (Exception $e) { $message = $e->getMessage(); echo $pathinfo . $message . PHP_EOL; createLog2($pathinfo . $message, 'error'); return false; } } function emailNotification($ticket) { global $pathinfo; global $config; $conn = dbconnect(); $conn->beginTransaction(); try { $query = 'SELECT * FROM ticket_email_queues WHERE is_complete = 0 AND ticket_id = :ticket_id ORDER BY id DESC LIMIT 1'; $stmt = $conn->prepare($query); $stmt->bindValue(':ticket_id', $ticket['id'], PDO::PARAM_INT); $stmt->execute(); $queue = $stmt->fetch(PDO::FETCH_ASSOC); if (!$queue || empty($queue)) { throw new Exception('Ticket email queue not found. Ticket ID: ' . $ticket['id']); } $message = 'Ticket email queue found. Ticket ID: ' . $ticket['id'] . '. Queue ID: ' . $queue['id']; createLog2($pathinfo . $message, 'success'); echo $pathinfo . $message . PHP_EOL; $technician_emails = array_filter([$ticket['email'], $ticket['email2'], $ticket['email3'], $ticket['email4']], function($e) { return filter_var($e, FILTER_VALIDATE_EMAIL); }); $technician_email = implode('; ', $technician_emails); $pic_email = $ticket['pic_email']; $spv_email = $ticket['spv_email']; $manager_email = $ticket['manager_email']; $director_email = $ticket['director_email']; $spk_date = date('Y-m-d', strtotime($ticket['email_date'])); $spk_expire_date = date('Y-m-d', strtotime($spk_date. ' + 14 days')); $ticket['spk_date'] = dateIndo($spk_date); $ticket['spk_expire_date'] = dateIndo($spk_expire_date); $ticket['regard_name'] = strtoupper($ticket['assign_fullname']); switch ($ticket['service_center_type_id']) { case 1: $content_path = __DIR__ . '/template/email_spk_dealer.txt'; $content_html = file_get_contents($content_path); break; case 2: $content_path = __DIR__ . '/template/email_spk_towing.txt'; $content_html = file_get_contents($content_path); break; default: $content_html = null; break; } if (empty($content_html)) { throw new Exception('Email template not found. Ticket ID: ' . $ticket['id'] . '. Queue ID: ' . $queue['id']); } if (preg_match_all("/{{(.*?)}}/", $content_html, $m)) { foreach ($m[1] as $i => $var) { $content_html = str_replace($m[0][$i], sprintf('%s', $ticket[$var]), $content_html); } } // $content_html = preg_replace('/([\s])\1+/', '', $content_html); $content = str_replace([' ', '•'], '', strip_tags($content_html)); // $content = html_entity_decode(strip_tags($content_html)); $data_email = [ 'email_from' => 'experience.honda@jsmservice.id', 'email_to' => str_replace(';;', ';', $technician_email), 'email_cc' => 'abigail@jsm.co.id; andreas_sp@hpm.co.id', 'email_bcc' => 'herfan@jsm.co.id', 'mail_date' => date('Y-m-d H:i:s'), 'subject' => 'SPK Service Order - ' . $ticket['ticket_no'], 'content' => $content, 'content_html' => $content_html, 'email_status_id' => 9, // email queue 'is_complete' => 0, // ready to send 'created' => date('Y-m-d H:i:s'), 'direction_id' => 2, // outbound 'user_id' => (int) $ticket['assign_user_id'], 'ticket_id' => $ticket['id'], 'ticket_no' => $ticket['ticket_no'], 'customer_id' => (int) $ticket['customer_id'], 'inbound_media_id' => 3, ]; $data_email_query = array_map(function($key, $val) use ($conn) { if (empty($val) && $val !== 0) { return $key . ' = NULL'; } return $key . ' = ' . $conn->quote($val); }, array_keys($data_email), $data_email); $query = 'INSERT INTO emails SET ' . implode(', ', $data_email_query); $stmt = $conn->prepare($query); $stmt->execute(); $inserted = $stmt->rowCount(); if ($inserted) { $email_id = $conn->lastInsertId(); $message = 'Email inserted. Ticket ID: ' . $ticket['id'] . '. Queue ID: ' . $queue['id'] . '. Email ID: ' . $email_id; createLog2($pathinfo . $message, 'success'); echo $pathinfo . $message . PHP_EOL; $path_attachment_email = $config['attachment_path'] . '/email/outbox/' . $email_id; $dir_attachment_email = $config['attachment_dir'] . '/email/outbox/' . $email_id; $dir_attachment_spk = $config['attachment_dir'] . '/spk'; $file_spk = 'spk_' . $ticket['ticket_no'] . '.pdf'; // check if exist directory email outbox by email id if (!is_dir($dir_attachment_email)) { // create directory email outbox by email id if not exist mkdir($dir_attachment_email, 0777, true); $message = 'Directory email SPK created: ' . $dir_attachment_email; createLog2($pathinfo . $message, 'success'); echo $pathinfo . $message . PHP_EOL; } // check if exist file spk if (file_exists($dir_attachment_spk . '/' . $file_spk)) { copy($dir_attachment_spk . '/' . $file_spk, $dir_attachment_email . '/' . $file_spk); $message = 'File SPK copied. From: ' . $dir_attachment_spk . '/' . $file_spk . '. To: ' . $dir_attachment_email . '/' . $file_spk; createLog2($pathinfo . $message, 'success'); echo $pathinfo . $message . PHP_EOL; $data_attachment = [ 'path' => $path_attachment_email, 'filename' => $file_spk, 'file_size' => filesize($dir_attachment_email . '/' . $file_spk), 'mime_type' => 'application/pdf', 'ticket_id' => $ticket['id'], 'user_id' => $ticket['technician_user_id'], ]; $data_attachment_query = array_map(function($key, $val) use ($conn) { if (empty($val) && $val !== 0) { return $key . ' = NULL'; } return $key . ' = ' . $conn->quote($val); }, array_keys($data_attachment), $data_attachment); $query = 'INSERT INTO ticket_attachments SET ' . implode(', ', $data_attachment_query); $stmt = $conn->prepare($query); $stmt->execute(); } else { throw new Exception('File SPK not found. Ticket ID: ' . $ticket['id'] . '. Queue ID: ' . $queue['id'] . '. Filename: ' . $file_spk); } $query = 'SELECT * FROM ticket_attachments WHERE ticket_id = :ticket_id'; $stmt = $conn->prepare($query); $stmt->bindValue(':ticket_id', $ticket['id'], PDO::PARAM_INT); $stmt->execute(); $attachments = []; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $attachments[] = $row; } if (empty($attachments)) { throw new Exception('Email attachment not found. Ticket ID: ' . $ticket['id'] . '. Queue ID: ' . $queue['id']); } $value_attachment_query = []; foreach ($attachments as $attachment) { $value_attachment_temp = [ $conn->quote($email_id), $conn->quote($attachment['path']), $conn->quote($attachment['filename']), $conn->quote($attachment['file_size']), $conn->quote($attachment['mime_type']) ]; $value_attachment_query[] = '(' . implode(', ', $value_attachment_temp) . ')'; } $query = 'INSERT INTO email_attachments (email_id, path, filename, file_size, mime_type) VALUES ' . implode(', ', $value_attachment_query); $stmt = $conn->prepare($query); $stmt->execute(); $inserted = $stmt->rowCount(); if ($stmt->rowCount() !== false) { $message = 'Email attachment inserted. Total: ' . $inserted; createLog2($pathinfo . $message, 'success'); echo $pathinfo . $message . PHP_EOL; } // update ticket email notification status $query = 'UPDATE ticket_email_queues SET is_complete = 1 WHERE ticket_id = :ticket_id AND is_complete = 0'; $stmt = $conn->prepare($query); $stmt->bindValue(':ticket_id', $ticket['id'], PDO::PARAM_INT); $updated = $stmt->execute(); if ($updated) { // update email status $query = 'UPDATE emails SET is_complete = 1 WHERE id = :email_id AND is_complete = 0'; $stmt = $conn->prepare($query); $stmt->bindValue(':email_id', $email_id, PDO::PARAM_INT); $updated = $stmt->execute(); if ($updated) { $message = 'Email updated. Email notification set successfully. Ticket ID: ' . $ticket['id'] . '. Queue ID: ' . $queue['id'] . '. Email ID: ' . $email_id; createLog2($pathinfo . $message, 'success'); echo $pathinfo . $message . PHP_EOL; $conn->commit(); socketEmit('email-sender'); return true; } throw new Exception('Failed to update email. Email notification set failed. Ticket ID: ' . $ticket['id'] . '. Queue ID: ' . $queue['id'] . '. Email ID: ' . $email_id); } throw new Exception('Failed to update ticket email queue. Ticket ID: ' . $ticket['id'] . '. Queue ID: ' . $queue['id']); } throw new Exception('Failed to insert email SPK. Ticket ID: ' . $ticket['id'] . '. Queue ID: ' . $queue['id']); } catch (Exception $e) { $conn->rollBack(); $message = $e->getMessage(); echo $pathinfo . $message . PHP_EOL; createLog2($pathinfo . $message, 'error'); return false; } } function dateIndo($value) { if (!empty($value)) { $date = date('Y-m-d', strtotime($value)); $split = explode('-', $date); switch ((int) $split[1]) { case 1: $month = 'Januari'; break; case 2: $month = 'Februari'; break; case 3: $month = 'Maret'; break; case 4: $month = 'April'; break; case 5: $month = 'Mei'; break; case 6: $month = 'Juni'; break; case 7: $month = 'Juli'; break; case 8: $month = 'Agustus'; break; case 9: $month = 'September'; break; case 10: $month = 'Oktober'; break; case 11: $month = 'November'; break; case 12: $month = 'Desember'; break; default: $month = $value; break; } return $split[2] . ' ' . $month . ' ' . $split[0]; } return $value; }