webadmin.php

/var/www/onesupportdemo.onesolution.hk/inc/configure_bk.php

<?php
//-----------------------------------------------------------------------------
// Define
//-----------------------------------------------------------------------------
define('PROJECT_FOLDER', '');
define('PROJECT_PATH', '/');
define('PROJECT_ROOT', $_SERVER['DOCUMENT_ROOT'] . PROJECT_PATH);

//-----------------------------------------------------------------------------
// Error reporting level
//-----------------------------------------------------------------------------
    error_reporting(E_ALL ^ E_NOTICE);
    //error_reporting(E_ALL);
    ini_set('display_errors', 1);
//-----------------------------------------------------------------------------
// Timezone
//-----------------------------------------------------------------------------
    date_default_timezone_set('Asia/Hong_Kong');
//date_default_timezone_set('HKT/8.0/no DST');

//-----------------------------------------------------------------------------
// Session
//-----------------------------------------------------------------------------
    if ($without_session_start !== true) {
        session_start();
    }

//-----------------------------------------------------------------------------
// Send default header
//-----------------------------------------------------------------------------
    if ($without_send_header !== true) {
        // IE use last version
        if ($without_use_last_ie_version !== true) {
            header('X-UA-Compatible: IE=edge,chrome=1');
        }
        // Powered by
        if ($without_x_powered_by !== true) {
            header('X-Powered-By: OneSolution');
        }
        // No cache
        if ($without_no_cache_header !== true) {
            // Expires in the past
            header('Expires: Mon, 26 Jul 1990 05:00:00 GMT');
            // Always modified
            header('Last-Modified: ' . gmdate("D, d M Y H:i:s") . ' GMT');
            // HTTP/1.1
            header('Cache-Control: no-store, no-cache, must-revalidate');
            header('Cache-control: private', false); // IE 6 FIX
            header('Cache-Control: post-check=0, pre-check=0', false);
            // HTTP/1.0
            header('Pragma: no-cache');
        }
    }

//-----------------------------------------------------------------------------
// Connections
//-----------------------------------------------------------------------------
    if ($without_connection !== true) {
        $PDO_OPTIONS = array(
            PDO::ATTR_CASE              => PDO::CASE_LOWER,
            PDO::ATTR_ERRMODE           => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
            PDO::ATTR_STRINGIFY_FETCHES => false,
        );

        //-----------------------------------------------------------------------------
        // MySQL
        //-----------------------------------------------------------------------------

        // production
        $dbh = new PDO("mysql:host=192.168.171.21;dbname=onesupport_demo", 'onesupportdemo', 'RE34Gh#m', $PDO_OPTIONS);

        $sql        = "SET NAMES ?";
        $parameters = array('utf8');
        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");
        }

        //-----------------------------------------------------------------------------
        // SQL Server
        //-----------------------------------------------------------------------------
//    $sqlsrv_dbh = new PDO("sqlsrv:Server=IP;Database=oneDB", 'USER', 'PW', $PDO_OPTIONS);
    }

//-----------------------------------------------------------------------------
// Global functions
//-----------------------------------------------------------------------------
    require_once(__DIR__ . '/class.phpmailer.php');
    function createPHPMailer()
    {
        $mailer = new PHPMailer();
        $mailer->IsSMTP();
        $mailer->Host     = "localhost";
        $mailer->CharSet  = "UTF-8";
        $mailer->WordWrap = 50;
        $mailer->IsHTML(true);
        return $mailer;
    }

    function redirectAndExit($url)
    {
        header('Location: ' . $url);
        exit;
    }

    function isPost()
    {
        return strtoupper($_SERVER['REQUEST_METHOD']) == 'POST';
    }

    function isGet()
    {
        return strtoupper($_SERVER['REQUEST_METHOD']) == 'GET';
    }

    /**
     * Wrapper of htmlspecialchars()
     *
     * @param string $text
     * @param string $charset
     *
     * @return string
     */
    function h($text, $charset = null)
    {
//    return htmlspecialchars($text, ENT_QUOTES | ENT_HTML401, isset($charset) ? $charset : 'UTF-8');
        return htmlspecialchars($text, ENT_QUOTES, isset($charset) ? $charset : 'UTF-8');
    }

    /**
     * Helper class - Util
     */
    class Util
    {

        public static function isAdmin()
        {
            return $_SESSION['webadmin']['role'] == 1;
        }

        public static function isGuest()
        {
            return $_SESSION['webadmin']['role'] == 4;
        }

        public static function date_to_string(DateTime $datetime, $format = 'Y-m-d')
        {
            return $datetime->format($format);
        }

        public static function time_to_string(DateTime $datetime, $format = 'H:i:s')
        {
            return $datetime->format($format);
        }

        public static function value_to_date_string($value, $format = 'Y-m-d')
        {
            $output = '';
            if (!empty($value)) {
                $date   = DateTime::createFromFormat('Y-m-d', $value);
                $output = $date->format($format);
            }
            return $output;
        }

        public static function value_to_time_string($value, $format = 'H:i')
        {
            $output = '';
            if (!empty($value)) {
                $date   = DateTime::createFromFormat('H:i:s', $value);
                $output = $date->format($format);
            }
            return $output;
        }

        /**
         * This will give
         *
         * $a="/home/a.php";
         * $b="/home/root/b/b.php";
         * echo getRelativePath($a,$b), PHP_EOL;  // ./root/b/b.php
         * and
         *
         * $a="/home/apache/a/a.php";
         * $b="/home/root/b/b.php";
         * echo getRelativePath($a,$b), PHP_EOL; // ../../root/b/b.php
         * and
         *
         * $a="/home/root/a/a.php";
         * $b="/home/apache/htdocs/b/en/b.php";
         * echo getRelativePath($a,$b), PHP_EOL; // ../../apache/htdocs/b/en/b.php
         * and
         *
         * $a="/home/apache/htdocs/b/en/b.php";
         * $b="/home/root/a/a.php";
         * echo getRelativePath($a,$b), PHP_EOL; // ../../../../root/a/a.php
         *
         * @param type $from
         * @param type $to
         *
         * @return type
         */
        public static function getRelativePath($from, $to)
        {
            $from    = explode('/', $from);
            $to      = explode('/', $to);
            $relPath = $to;

            foreach ($from as $depth => $dir) {
                // find first non-matching dir
                if ($dir === $to[$depth]) {
                    // ignore this directory
                    array_shift($relPath);
                } else {
                    // get number of remaining dirs to $from
                    $remaining = count($from) - $depth;
                    if ($remaining > 1) {
                        // add traversals up to first matching dir
                        $padLength = (count($relPath) + $remaining - 1) * -1;
                        $relPath   = array_pad($relPath, $padLength, '..');
                        break;
                    } else {
                        $relPath[0] = './' . $relPath[0];
                    }
                }
            }
            return implode('/', $relPath);
        }

        public static function link($filepath)
        {
            $from = $_SERVER['SCRIPT_FILENAME'];
            $to   = realpath($filepath);

            $from = str_replace('\\', '/', $from);
            $to   = str_replace('\\', '/', $to);

            $link = $from == $to ? '' : Util::getRelativePath($from, $to);
            if (empty($link) && $from == $to) {
                $parts = explode('/', $from);
                $count = count($parts);
                $link  = $parts[$count - 1];
            }

            return $link;
        }

    }

//-----------------------------------------------------------------------------
// Pagination
//-----------------------------------------------------------------------------
    abstract class Pagination
    {

        private $number_per_page = 15;
        private $show_near_pages = 6;

        public function setNumberPerPage($number_per_page)
        {
            $this->number_per_page = $number_per_page;
        }

        public function getNumberPerPage()
        {
            return $this->number_per_page;
        }

        abstract protected function count();

        public function getOffset()
        {
            return ($this->getCurrentPage() - 1) * $this->number_per_page;
        }

        public function getLimit()
        {
            return $this->number_per_page;
        }

        public function getCurrentPage()
        {
            return max(intval($_GET['page']), 1);
        }

        private function getQueryString($cleanPage = true)
        {
            $get = $_GET;
            unset($get['page']);
            return $get;
        }

        protected function getPrevLink()
        {
            $page = $this->getCurrentPage() - 1;
            return $this->getPageLink($page);
        }

        protected function getNextLink()
        {
            $page = $this->getCurrentPage() + 1;
            return $this->getPageLink($page);
        }

        protected function getPageLink($page)
        {
            $get         = $this->getQueryString();
            $get['page'] = max($page, 1);
            return '?' . http_build_query($get);
        }

        public function pageCount()
        {
            $count = $this->count();
            $page  = $count / floatval($this->number_per_page);
            if (floor($page) < $page) {
                $page = floor($page) + 1;
            }
            return $page;
        }

        private $_cache_to_string;

        public function toString()
        {
            if (!empty($this->_cache_to_string)) {
                return $this->_cache_to_string;
            }

            $pageCount   = $this->pageCount();
            $currentPage = $this->getCurrentPage();
            ob_start();
            ?>
            <div class="pagination">
                <ul>
                    <li<?= $currentPage == 1 ? ' class="disabled"' : '' ?>>
                        <a href="<?= $this->getPrevLink() ?>">&laquo;</a></li>
                    <?php
                        $is_dot = false;
                        for ($i = 1; $i <= max($pageCount, 1); $i++):
                            $is_show = $i == 1 || $i == $pageCount || abs($currentPage - $i) < $this->show_near_pages;
                            if ($is_show):
                                $is_dot = true;
                                ?>
                                <li<?= $currentPage == $i ? ' class="active"' : '' ?>>
                                    <a href="<?= $this->getPageLink($i) ?>"><?= $i ?></a></li>
                            <?php
                            elseif ($is_dot):
                                $is_dot = false;
                                ?>
                                <li class="disabled"><a href="javascript:void(0)">...</a></li>
                            <?php
                            endif;
                        endfor;
                    ?>
                    <li<?= $currentPage >= $pageCount ? ' class="disabled"' : '' ?>>
                        <a href="<?= $this->getNextLink() ?>">&raquo;</a></li>
                </ul>
            </div>
            <?php
            $this->_cache_to_string = ob_get_contents();
            ob_end_clean();

            return $this->_cache_to_string;
        }

    }

    class MySqlPagination extends Pagination
    {
        private $sql;
        private $parameters;
        private $dbh;

        function __construct($sql, $parameters)
        {
            global $dbh;

            $this->sql        = $sql;
            $this->parameters = $parameters;
            $this->dbh        = $dbh;
        }

        protected function count()
        {
            $sql = $this->sql;
            $sql = "SELECT COUNT(*) AS count FROM ( $sql ) t";
            if (!($sth = $this->dbh->prepare($sql))) {
                throw new Exception("sql prepare statement failure: $sql");
            }
            $sth->setFetchMode(PDO::FETCH_ASSOC);
            if (!$sth->execute($this->parameters)) {
                throw new Exception("sql execute statement failure: $sql");
            }
            $record = $sth->fetch(PDO::FETCH_ASSOC);

            return $record['count'];
        }

        public function getSqlLimitAndOffset()
        {
            $limit  = $this->getLimit();
            $offset = $this->getOffset();
            return " LIMIT $limit OFFSET $offset ";
        }
    }

    class SqlsrvPagination extends Pagination
    {
        private $sql;
        private $parameters;
        private $dbh;

        function __construct($sql, $parameters)
        {
            global $sqlsrv_dbh;

            $this->sql        = $sql;
            $this->parameters = $parameters;
            $this->dbh        = $sqlsrv_dbh;
        }

        protected function count()
        {
            $sql = $this->sql;
            $pos = strpos($sql, "ORDER BY");
            if ($pos !== false) {
                $sql = substr($sql, 0, $pos);
            }
            $sql = "SELECT COUNT(*) AS count FROM ( $sql ) t";
            if (!($sth = $this->dbh->prepare($sql))) {
                throw new Exception("sql prepare statement failure: $sql");
            }
            $sth->setFetchMode(PDO::FETCH_ASSOC);
            if (!$sth->execute($this->parameters)) {
                throw new Exception("sql execute statement failure: $sql");
            }
            $record = $sth->fetch(PDO::FETCH_ASSOC);

            return $record['count'];
        }

        public function getSql()
        {
            $sql    = $this->sql;
            $limit  = $this->getLimit();
            $offset = $this->getOffset();

            $limit  = intval($limit);
            $offset = intval($offset);

            if ($offset == 0)
                return preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $limit . ' ', $sql);

            $orderby = stristr($sql, 'ORDER BY');
            $over    = $orderby ? preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby) : 'ORDER BY (SELECT 0)';

            // Remove ORDER BY clause from $sql
            $sql = preg_replace('/\s+ORDER BY(.*)/', '', $sql);

            // Add ORDER BY clause as an argument for ROW_NUMBER()
            $sql = "SELECT ROW_NUMBER() OVER ($over) AS \"AR_ROWNUM\", * FROM ($sql) AS inner_tbl";

            $start = $offset + 1;
            $end   = $offset + $limit;
            return "WITH outer_tbl AS ($sql) SELECT * FROM outer_tbl WHERE \"AR_ROWNUM\" BETWEEN $start AND $end";
        }
    }

//-----------------------------------------------------------------------------
// Sign in
//-----------------------------------------------------------------------------
    class SigninController
    {

        public function isLogin()
        {
            return isset($_SESSION['webadmin']['id']) && strlen($_SESSION['webadmin']['id']);
        }

        protected function redirectMain()
        {
            if ($_SESSION['webadmin']["role"] == 1) { //admin
                redirectAndExit(Util::link(__DIR__ . '/../contract/contract_alert.php'));
            } else {
                redirectAndExit(Util::link(__DIR__ . '/../main.php') . '?' . http_build_query($_GET));
            }

        }

        protected function redirectLogin()
        {
            redirectAndExit(Util::link(__DIR__ . '/../login.php') . '?' . http_build_query($_GET));
        }

        public function checkLogin()
        {
            if (!$this->isLogin()) {
                $this->redirectLogin();
            }
        }

        public function login()
        {
            global $dbh;

            if ($this->isLogin()) {
                $this->redirectMain();
            }

            $message = null;
            if (!empty($_GET['message'])) {
                $message = $_GET['message'];
            }
            $message_heading = 'Error!';
            if (!empty($_GET['message_heading'])) {
                $message_heading = $_GET['message_heading'];
            }
            $message_css_class = 'alert-error';
            if (!empty($_GET['message_css_class'])) {
                $message_css_class = $_GET['message_css_class'];
            }

            if (isPost()) {
                $name     = $_POST['name'];
                $password = $_POST['password'];
                $sql      = "SELECT * FROM sys_login WHERE loginname = ? AND loginpw = ? AND actived = ? AND deleted = ?";
                if (!($sth = $dbh->prepare($sql))) {
                    throw new Exception("sql prepare statement failure: $sql");
                }
                $sth->setFetchMode(PDO::FETCH_ASSOC);
                if (!$sth->execute(array($name, md5($password), 1, 0))) {
                    throw new Exception("sql execute statement failure: $sql");
                }
                $staff = $sth->fetch(PDO::FETCH_ASSOC);
                if (!empty($staff)) {
                    // Assign webadmin session
                    $_SESSION['webadmin'] = $staff;

                    // Redirect to job
                    $this->redirectMain();
                }
                $message           = 'User name or password not correct.';
                $message_heading   = 'Error!';
                $message_css_class = 'alert-error';
            }

            return array(
                'message'           => $message,
                'message_heading'   => $message_heading,
                'message_css_class' => $message_css_class,
            );
        }

        public function logout()
        {
            // Clear webadmin session
            unset($_SESSION['webadmin']);

            // Redirect to login
            $this->redirectLogin();
        }

    }

//-----------------------------------------------------------------------------
// Static values
//-----------------------------------------------------------------------------
    class Staff
    {

        public static function roleOptions()
        {
            return array(
                1 => 'Admin',
                2 => 'Engine',
                3 => 'Support',
                4 => 'Guest',
            );
        }

        public static function staff_name($staff_id)
        {
            global $dbh;

            $sql = "SELECT * from sys_login where id = ?";

            if (!($sth = $dbh->prepare($sql))) {
                throw new Exception("sql prepare statement failure: $sql");
            }
            $sth->setFetchMode(PDO::FETCH_ASSOC);
            $parameters = array((int)$staff_id);
            if (!$sth->execute($parameters)) {
                throw new Exception("sql execute statement failure: $sql");
            }
            $staff_detail = $sth->fetchAll();

            return $staff_detail;
        }

    }

    class JobDetail
    {
        public static function statusOptions()
        {
            return array(
                1 => 'Open',
                2 => 'Completed',
                3 => 'Follow up',
                4 => 'Cancel',
            );
        }

        public static function statusOptions2($statusnum)
        {
            if ($statusnum == 1)
                return 'Open';
            if ($statusnum == 2)
                return 'Completed';
            if ($statusnum == 3)
                return 'Follow up';
            if ($statusnum == 4)
                return 'Cancel';
        }
    }

    class JobPending
    {
        public static function convert_to_job_detail($job_pending, array $job_detail_attributes)
        {
            $attributes = array_merge($job_detail_attributes, array(
                'from_job_pending_id' => $job_pending['id'],
            ));
            // copy fields
            $copy_fields = array('title', 'remarks');
            foreach ($copy_fields as $field) {
                $attributes[$field] = $job_pending[$field];
            }
            return $attributes;
        }
    }

    class Job
    {

        public static function week()
        {
            return array(
                1 => 'Mon',
                2 => 'Tue',
                3 => 'Wed',
                4 => 'Thu',
                5 => 'Fri',
                6 => 'Sat',
                7 => 'Sun',
            );
        }

        public static function statusOptions()
        {
            return array(
                1 => 'Open',
                2 => 'Close',
                3 => 'Void',
            );
        }

        public static function activeperiod()
        {
            return array(
                1 => 'Daily',
                2 => 'Weekly',
                3 => 'Monthly',
            );
        }

        public static function whichweek()
        {
            return array(
                1 => 'The First Week of Month (1-7)',
                2 => 'The Second Week of Month (8-14)',
                3 => 'The Third Week of Month (15-21)',
                4 => 'The Fourth Week of Month (22-28)',
                5 => 'The Fifth Week of Month (29-31)',
            );
        }

        public static function monthly_task_option($customer_id)
        {
            global $dbh;

            $sql        = "SELECT * from sup_monthly_task where customer_id = '" . (int)$customer_id . "'";
            $parameters = array();
            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");
            }
            $monthly_tasks = $sth->fetchAll();

            return $monthly_tasks;
        }

        public static function typeOptions()
        {
            return array(
                1 => 'Schedule Visit',
                2 => 'Service Call',
                3 => 'Urgent Call',
                4 => 'Remote Service Call',
            );
        }

        public static function typeOptions2($typenum)
        {
            if ($typenum == 1)
                return 'Schedule Visit';
            if ($typenum == 2)
                return 'Service Call';
            if ($typenum == 3)
                return 'Urgent Call';
            if ($typenum == 4)
                return 'Remote Service Call';

        }

        public static function createDetails($keyValues)
        {
            global $dbh;

            if (empty($keyValues)) {
                $keyValues = array();
            }

            $sql        = "SELECT column_name FROM information_schema.columns WHERE table_schema = (SELECT DATABASE()) AND table_name = ?";
            $parameters = array('sup_job_detail');
            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_detail_columns = $sth->fetchAll();

            $job_pending_ids    = array();
            $job_pending_values = array();
            //var_dump($keyValues);
            foreach ($keyValues as $id => $attributes) {
                if (isset($_SESSION["all_job_ids"]) && !empty($_SESSION["all_job_ids"])) {
                    foreach ($_SESSION["all_job_ids"] as $job_id) {
                        $job_detail = array(
                            'job_id' => $job_id,
                        );

                        foreach ($job_detail_columns as $column) {
                            $column = $column['column_name'];
                            if (isset($attributes[$column])) {
                                $job_detail[$column] = $attributes[$column];
                            }
                        }

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

                        $columns    = array();
                        $values     = array();
                        $parameters = array();
                        foreach ($job_detail as $column => $value) {
                            $columns[]    = $column;
                            $parameters[] = !strlen($value) ? null : $value;
                            $values[]     = '?';
                        }
                        $sql = "INSERT sup_job_detail (" . 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");
                        }

                        if (!empty($job_detail['from_job_pending_id'])) {
                            $job_pending_ids[]    = $job_detail['from_job_pending_id'];
                            $job_pending_values[] = '?';
                        }
                    }
                } else {
                    /*foreach($_SESSION["all_job_ids"] as $job_id){
                    $job_detail = array(
                        'job_id' => $job_id,
                        'actived' => 1,
                        'deleted' => 0,
                        'status' => 1, // Open
                    );*/
                    foreach ($job_detail_columns as $column) {
                        $column = $column['column_name'];
                        if (isset($attributes[$column])) {
                            $job_detail[$column] = $attributes[$column];
                        }
                    }

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

                    $columns    = array();
                    $values     = array();
                    $parameters = array();
                    foreach ($job_detail as $column => $value) {
                        $columns[]    = $column;
                        $parameters[] = !strlen($value) ? null : $value;
                        $values[]     = '?';
                    }
                    $sql = "INSERT sup_job_detail (" . 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");
                    }

                    if (!empty($job_detail['from_job_pending_id'])) {
                        $job_pending_ids[]    = $job_detail['from_job_pending_id'];
                        $job_pending_values[] = '?';
                    }
                }


            }
            /*if (!empty($job_pending_ids)) {
                $job_pending = array(
                    'actived' => 0,
                );

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

                // Update job detail
                $values = array();
                $parameters = array();
                foreach ($job_pending as $column => $value) {
                    if ($column != 'id') {
                        $parameters[] = !strlen($value) ? null : $value;
                        $values[] = "`$column` = ?";
                    }
                }

                $sql = "UPDATE sup_job_pending SET " . implode(', ', $values) . " WHERE id IN (" . implode(', ', $job_pending_values) . ")";
                $parameters = array_merge($parameters, $job_pending_ids);
                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");
                }
            }*/
        }

        public static function updateDetails($keyValues, array $job_details)
        {
            global $dbh;

            if (empty($keyValues)) {
                $keyValues = array();
            }

            $sql        = "SELECT column_name FROM information_schema.columns WHERE table_schema = (SELECT DATABASE()) AND table_name = ?";
            $parameters = array('sup_job_detail');
            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_detail_columns = $sth->fetchAll();

            $job_detail_map = array();
            foreach ($job_details as $job_detail) {
                $job_detail_id                  = $job_detail['id'];
                $job_detail_map[$job_detail_id] = $job_detail;
            }

            foreach ($keyValues as $id => $attributes) {
                $job_detail = $job_detail_map[$id];
                if (empty($job_detail)) {
                    throw new Exception('Job detail not found!');
                }

                foreach ($job_detail_columns as $column) {
                    $column = $column['column_name'];
                    if (isset($attributes[$column])) {
                        $job_detail[$column] = $attributes[$column];
                    }
                }

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

                $values     = array();
                $parameters = array();
                foreach ($job_detail as $column => $value) {
                    if ($column != 'id') {
                        $parameters[] = !strlen($value) ? null : $value;
                        $values[]     = "`$column` = ?";
                    }
                }
                $sql          = "UPDATE sup_job_detail SET " . implode(', ', $values) . " WHERE id = ?";
                $parameters[] = $job_detail['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");
                }
            }
        }

        public static function allow_change_status_to_confirm($id)
        {
            global $dbh;

            $is_allow = true;

            $sql        = "SELECT * FROM sup_job_detail WHERE job_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_details = $sth->fetchAll();

            // check any detail status is open
            foreach ($job_details as $job_detail) {
                if ($job_detail['status'] == 1) {
                    $is_allow = false;
                    break;
                }
            }

            return $is_allow;
        }

    }


//contract
    class Contract
    {
        public static function statusOptions($data = NULL)
        {
            if (empty($data)) {
                return array(
                    1 => 'Open',
                    2 => 'Close',
                    3 => 'Void',
                    4 => 'Expired'
                );
            } else {
                if ($data == 1)
                    return 'Open';
                if ($data == 2)
                    return 'Close';
                if ($data == 3)
                    return 'Void';
                if ($data == 4)
                    return 'Expired';
            }
        }

        public static function typeOptions($data = NULL)
        {
            if (empty($data)) {
                return array(
                    1 => 'Schedule Visit',
                    2 => 'Service Call',
                    3 => 'Urgent Call',
                    4 => 'Remote Service Call',
                );
            } else {
                if ($data == 1)
                    return 'Schedule Visit';
                if ($data == 2)
                    return 'Service Call';
                if ($data == 3)
                    return 'Urgent Call';
                if ($data == 4)
                    return 'Remote Service Call';
            }
        }
    }

//Project
    class Project
    {
        public static function statusOptions()
        {
            return array(
                1 => 'Open',
                2 => 'Close',
                //3 => 'Void',
            );
        }

        public static function statusOptions2($status)
        {
            if ($status == 1)
                return 'Open';
            if ($status == 2)
                return 'Close';
            //if($status == 3)
            //return 'Void';
        }


    }

//Project
    class Customer
    {

        public static function company_name($customer_id)
        {
            global $dbh;

            $sql = "SELECT * from v_cm_customer_support where cust_id = ?";

            if (!($sth = $dbh->prepare($sql))) {
                throw new Exception("sql prepare statement failure: $sql");
            }
            $sth->setFetchMode(PDO::FETCH_ASSOC);
            $parameters = array($customer_id);
            if (!$sth->execute($parameters)) {
                throw new Exception("sql execute statement failure: $sql");
            }
            $customer_detail = $sth->fetchAll();

            return $customer_detail;
        }

    }


    class monthly_task_statusOption
    {
        public static function statusOptions()
        {
            return array(
                1 => 'Open',
                2 => 'Completed',
                3 => 'Cancel',
            );
        }
    }

    class period_task_statusOption
    {
        public static function statusOptions()
        {
            return array(
                1 => 'Open',
                2 => 'Completed',
                3 => 'Cancel',
            );
        }
    }


    if (!function_exists('contract_type')) {
        function contract_type($value)
        {
            if ($value == "1")
                return "Monthly";
            else if ($value == "2")
                return "Package";
            else
                return "Error";
        }
    }

    function get_month_name($month)
    {
        $months = array(
            1  => 'Jan',
            2  => 'Feb',
            3  => 'Mar',
            4  => 'Apr',
            5  => 'May',
            6  => 'Jun',
            7  => 'Jul',
            8  => 'Aug',
            9  => 'Sep',
            10 => 'Oct',
            11 => 'Nov',
            12 => 'Dec'
        );

        return $months[$month];
    }


    function dateRange($first, $last, $step = '+1 month', $format = 'Y-m')
    {
        $dates   = array();
        $current = strtotime($first);
        $last    = strtotime($last);

        while ($current <= $last) {
            $dates[] = date($format, $current);
            $current = strtotime($step, $current);
        }
        return $dates;
    }