webadmin.php

/var/www/onesupportdemo.onesolution.hk/job/status.php

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
session_start();

require_once(__DIR__ . '/../checkuser.php');
if (isPost() && isset($_POST['id'], $_POST['status'])) {
extract($_POST);

$sql = "SELECT * FROM sup_job WHERE id = ?";
$parameters = array($id);
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");
}
$job = $sth->fetch(PDO::FETCH_ASSOC);
if (empty($job)) {
    throw new Exception('Job not found.');
}

$sql = "SELECT * FROM sup_job_detail WHERE job_id = ? ORDER BY id";
$parameters = array($job['id']);
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");
}
$job['details'] = $sth->fetchAll();

$sql = "SELECT * FROM sys_login WHERE id = ?";
$parameters = array($job['staff_id']);
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 = $sth->fetch(PDO::FETCH_ASSOC);

//20130815 send email to engine
$sql = "SELECT * FROM sys_login WHERE id = ?";
$parameters = array($staff['parentid']);
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");
}
$engine = $sth->fetch(PDO::FETCH_ASSOC);

$sql = "SELECT * FROM v_cm_customer_support WHERE cust_id = ?";
$parameters = array($job['customer_id']);
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");
}
$customer = $sth->fetch(PDO::FETCH_ASSOC);

$job_type_options = Job::typeOptions();
$job_detail_status_options = JobDetail::statusOptions();

if ($job['status'] != $status) {
// Validation
$errors = array();
if ($status == 2) {
    if (!Job::allow_change_status_to_confirm($job['id'])) {
        $errors['status'] = 'Not allow change status to confirm. Because there are some details status still is OPEN.';
    }
}

$send_job_closed_mail = false;
if (!empty($errors)) {
    print_r($errors);
    exit;
} else {
if (!$dbh->beginTransaction()) {
    throw new Exception('mysql begin transaction failure.');
}
try {
    $sql = "UPDATE sup_job SET status = ?, lastupdate = ?, lastupby = ? WHERE id = ?";
    $parameters = array($status, date('Y-m-d H:i:s'), $_SESSION['webadmin']['id'], $job['id']);
    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");
    }

    switch ($status) {
        case 1:{

        } // Open
            break;
        case 3: // Void
        {
            // Delete pending job detail
            $sql = "UPDATE sup_job_pending SET deleted = ?, lastupdate = ?, lastupby = ? WHERE job_id = ? AND actived = ? AND deleted = ?";
            $parameters = array(1, date('Y-m-d H:i:s'), $_SESSION['webadmin']['id'], $job['id'], 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");
            }

            $send_job_closed_mail = true;

        }
            break;

        case 2: // Close
        {
            $sql = "SELECT * FROM sup_job_detail WHERE job_id = ? ORDER BY id";
            $parameters = array($job['id']);
            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");
            }
            $job['details'] = $sth->fetchAll();

            // Create pending job detail
            foreach ($job['details'] as $detail) {
                // Filter status: follow up
                if ($detail['status'] != 3) {
                    continue;
                }

                $attributes = $detail;

                // remove genernal fields
                $genernal_fields = array('id', 'createby', 'createdate', 'lastupby', 'lastupdate', 'actived', 'deleted');
                foreach ($genernal_fields as $field) {
                    unset($attributes[$field]);
                }
                // remove special fields
                $special_fields = array('status', 'from_job_pending_id', 'task_category_id', 'follow_up_flag');
                foreach ($special_fields as $field) {
                    unset($attributes[$field]);
                }

                // Append record time
                $now = date("Y-m-d H:i:s");
                $attributes = array_merge($attributes, array(
                    'createdate' => $now,
                    'createby' => $_SESSION['webadmin']['id'],
                    'lastupdate' => $now,
                    'lastupby' => $_SESSION['webadmin']['id'],
                ));

                // Append values
                $attributes = array_merge($attributes, array(
                    'actived' => 1,
                    'deleted' => 0,
                    'job_detail_id' => $detail['id'],
                    'jpend_customer_id' => $job['customer_id'],

                ));

                // create pending job detail
                $columns = array();
                $values = array();
                $parameters = array();
                foreach ($attributes as $column => $value) {
                    $columns[] = $column;
                    $parameters[] = !strlen($value) ? null : $value;
                    $values[] = '?';
                }
                $sql = "INSERT sup_job_pending (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $values) . ")";
                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");
                }
            }
            $send_job_closed_mail = true;
        }
            break;
    }

    if (!$dbh->commit()) {
        throw new Exception('mysql commit transaction failure.');
    }
} catch (Exception $exception) {
    if (!$dbh->rollBack()) {
        throw new Exception('mysql roll back transaction failure.');
    }
    throw $exception;
}

if ($send_job_closed_mail) {
// Send job closed mail
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>
<div>
    <p>This is a prototype e-mail, it will not send to client currently. If any comments, welcome to contact program team!</p>
    <hr />
    <p>This is a system e-mail, please do not reply.</p>
    <p>The following information is the job,</p>
    <table>
        <tbody>
        <tr>
            <th>Job</th>
            <td>#<?=h($job['id'])?></td>
        </tr>
        <tr>
            <th>Customer</th>
            <td><?=h($customer['company_name'])?></td>
        </tr>
        <tr>
            <th>Staff</th>
            <td><?=h($staff['username'])?></td>
        </tr>
        <tr>
            <th>Date</th>
            <td><?=h($job['date'])?></td>
        </tr>
        <tr>
            <th>Actual Start Time</th>
            <td><?=h(Util::value_to_time_string($job['actual_start_time']))?></td>
        </tr>
        <tr>
            <th>Actual End Time</th>
            <td><?=h(Util::value_to_time_string($job['actual_end_time']))?></td>
        </tr>
        <tr>
            <th>Type</th>
            <td><?=h($job_type_options[$job['type']])?></td>
        </tr>
        <tr>
            <th>Status</th>
            <td>
                <?php
                    //get new status
                    $sql2 = "SELECT * FROM sup_job WHERE id = ?";
                    $parameters2 = array($id);
                    if (!($sth = $dbh->prepare($sql2))) {
                        throw new Exception("sql prepare statement failure: $sql2");
                    }
                    $sth->setFetchMode(PDO::FETCH_ASSOC);
                    if (!$sth->execute($parameters2)) {
                        throw new Exception("sql execute statement failure: $sql2");
                    }
                    $job2 = $sth->fetch(PDO::FETCH_ASSOC);


                    $statusOptions = Job::statusOptions();
                    echo $statusOptions[$job2['status']];
                ?>
            </td>
        </tr>
        <tr>
            <th>Remarks</th>
            <td><?=nl2br(h($job['remarks']))?></td>
        </tr>
        </tbody>
    </table>
    <hr />
    <p>The following information are the job's tasks,</p>
    <table>
        <thead>
        <tr>
            <th>Status</th>
            <th>Title</th>
            <th>Remarks</th>
        </tr>
        </thead>
        <tbody>
        <?php foreach ($job['details'] ?: array() as $job_detail): ?>
            <tr>
                <td>
                    <?php
                        $color = null;
                        switch ($job_detail['status']) {
                            case 1: { $color = 'background-color:#F4FA58;'; break; } // Open
                            case 2: { $color = 'background-color:#CEECF5;'; break; } // Completed
                            case 3: { $color = 'background-color:#F4FA58;'; break; } // Follow up
                            case 4: { $color = 'background-color:#CEECF5;'; break; } // Cancel
                        }
                    ?>
                    <span<?=empty($color) ? '' : sprintf(' style="%s"', $color)?>><?=h($job_detail_status_options[$job_detail['status']])?></span>
                </td>
                <td><?=h($job_detail['title'])?></td>
                <td><?=nl2br(h($job_detail['remarks']))?></td>
            </tr>
        <?php endforeach; ?>
        </tbody>
    </table>
    <p>Thank you for your attention!</p>
    <p>
        Regards,<br />
        One Solution Limited
    </p>
</div>
</body>
</html>
<?php
        $body = ob_get_contents();
        ob_end_clean();

        $mailer = createPHPMailer();
        $mailer->WordWrap = 0;
        $mailer->From = 'noreply@onesolution.com.hk';
        $mailer->FromName = 'noreply';
        $email_addresses = array(
            'samuel@onesolution.com.hk',
            'jonathan@onesolution.com.hk',
            //'kelvinchan@onesolution.com.hk',
            //sprintf('%s@onesolution.com.hk', $staff['loginname'])
            //$engine['email'],
            //$staff['email'],

//                    $job['email1'],
//                    $job['email2'],
        );
        foreach ($email_addresses as $email_address) {
            if (!empty($email_address)) {
                $mailer->AddAddress($email_address);
            }
        }

    //get new status
    $sql2 = "SELECT * FROM sup_job WHERE id = ?";
    $parameters2 = array($id);
    if (!($sth = $dbh->prepare($sql2))) {
        throw new Exception("sql prepare statement failure: $sql2");
    }
    $sth->setFetchMode(PDO::FETCH_ASSOC);
    if (!$sth->execute($parameters2)) {
        throw new Exception("sql execute statement failure: $sql2");
    }
    $job2 = $sth->fetch(PDO::FETCH_ASSOC);

    if($job2['status'] == 2){
        $mailer->Subject = 'One Support - Job Closed Notification';
    }

    if($job2['status'] == 3){
        $mailer->Subject = 'One Support - Job Void Notification';
    }


        $mailer->Body = $body;
        if (!$mailer->Send()) {
            throw new Exception(sprintf('Send mail failure: %s', $mailer->ErrorInfo));
        }


    }

    }

    $sql = "SELECT * FROM sup_job WHERE id = ?";
    $parameters = array($id);
    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");
    }
    $job = $sth->fetch(PDO::FETCH_ASSOC);
    }

    $statusOptions = Job::statusOptions();

    $_SESSION["remark_job_id"] = $job['id'];

    $startdate = date("Y-m-d");
    $enddate = Date('Y-m-d', strtotime($startdate ."+1 month"));

    $data = array(
        'start_date' => $startdate,
        'end_date' => $enddate,
        'id' => $job['id'],
        'message' => 'Changed status to ' . $statusOptions[$job['status']] . '.',
    );
    //redirectAndExit('modifyform.php?'.http_build_query($data));
    redirectAndExit('index.php?'.http_build_query($data));


    }