Issues in TicketHandler.php (master) - Issues in master - XoopsModules25x/xhelp - Measure and Improve Code Quality continuously with Scrutinizer

Issues (1844)

Security Analysis not enabled

File Inclusion

File Inclusion enables an attacker to inject custom files into PHP's file loading mechanism, either explicitly passed to include, or for example via PHP's auto-loading mechanism.

Regex Injection

Regex Injection enables an attacker to execute arbitrary code in your PHP process.

SQL Injection

SQL Injection enables an attacker to execute arbitrary SQL code on your database server gaining access to user data, or manipulating user data.

Response Splitting

Response Splitting can be used to send arbitrary responses.

File Manipulation

File Manipulation enables an attacker to write custom data to files. This potentially leads to injection of arbitrary code on the server.

Object Injection

Object Injection enables an attacker to inject an object into PHP code, and can lead to arbitrary code execution, file exposure, or file manipulation attacks.

File Exposure

File Exposure allows an attacker to gain access to local files that he should not be able to access. These files can for example include database credentials, or other configuration files.

XML Injection

XML Injection enables an attacker to read files on your local filesystem including configuration files, or can be abused to freeze your web-server process.

Code Injection

Code Injection enables an attacker to execute arbitrary code on the server.

Variable Injection

Variable Injection enables an attacker to overwrite program variables with custom data, and can lead to further vulnerabilities.

XPath Injection

XPath Injection enables an attacker to modify the parts of XML document that are read. If that XML document is for example used for authentication, this can lead to further vulnerabilities similar to SQL Injection.

Other Vulnerability

This category comprises other attack vectors such as manipulating the PHP runtime, loading custom extensions, freezing the runtime, or similar.

Command Injection

Command Injection enables an attacker to inject a shell command that is execute with the privileges of the web-server. This can be used to expose sensitive data, or gain access of your server.

LDAP Injection

LDAP Injection enables an attacker to inject LDAP statements potentially granting permission to run unauthorized queries, or modify content inside the LDAP tree.

Cross-Site Scripting

Cross-Site Scripting enables an attacker to inject code into the response of a web-request that is viewed by other users. It can for example be used to bypass access controls, or even to take over other users' accounts.

Header Injection

Unfortunately, the security analysis is currently not available for your project. If you are a non-commercial open-source project, please contact support to gain access.

class/TicketHandler.php (1 issue)

Labels

Bug 1

Severity

<?php declare(strict_types=1);

namespace XoopsModules\Xhelp;

/*
 * You may not change or alter any portion of this comment or credits
 * of supporting developers from this source code or any supporting source code
 * which is considered copyrighted (c) material of the original comment or credit authors.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 */

/**
 * @copyright    {@link https://xoops.org/ XOOPS Project}
 * @license      {@link https://www.gnu.org/licenses/gpl-2.0.html GNU GPL 2 or later}
 * @author       Eric Juden <[email protected]>
 * @author       XOOPS Development Team
 */

use Xmf\Request;
use XoopsModules\Xhelp\RequestsHandler;
filter:
    dependency_paths: ["lib/*"]

if (!\defined('XHELP_CLASS_PATH')) {
    exit();
}
// require_once XHELP_CLASS_PATH . '/BaseObjectHandler.php';
//require_once  \dirname(__DIR__, 3) . '/include/cp_header.php';

global $xoopsUser;

/**
 * class TicketHandler
 */
class TicketHandler extends BaseObjectHandler
{
    /**
     * Name of child class
     *
     * @var string
     */
    public $classname = Ticket::class;
    /**
     * DB Table Name
     *
     * @var string
     */
    public $dbtable = 'xhelp_tickets';

    private const TABLE = 'xhelp_tickets';
    private const ENTITY = Ticket::class;
    private const ENTITYNAME = 'Ticket';
    private const KEYNAME = 'id';
    private const IDENTIFIER = 'uid';

    /**
     * Constructor
     *
     * @param \XoopsMySQLDatabase|null $db reference to a xoopsDB object
     */
    public function __construct(\XoopsMySQLDatabase $db = null)
    {
        $this->init($db);
        $this->helper = Helper::getInstance();
        parent::__construct($db, static::TABLE, static::ENTITY, static::KEYNAME, static::IDENTIFIER);
    }

    /**
     * retrieve an object from the database, based on. use in child classes
     * @param int $id ID
     * @return mixed object if id exists, false if not
     */
    public function get($id = null, $fields = null)
    {
        $id = (int)$id;
        if ($id > 0) {
            $sql = $this->selectQuery(new \Criteria('id', $id, '=', 't'));
            if (!$result = $this->db->query($sql)) {
                return false;
            }
            $numrows = $this->db->getRowsNum($result);
            if (1 == $numrows) {
                $object = new $this->classname($this->db->fetchArray($result));

                return $object;
            }
        }

        return false;
    }

    /**
     * find a ticket based on a hash
     *
     * @param string $hash
     * @return Ticket object
     */
    public function getTicketByHash(string $hash): ?Ticket
    {
        $sql = $this->selectQuery(new \Criteria('emailHash', $hash, '=', 't'));
        if (!$result = $this->db->query($sql)) {
            return null;
        }
        $numrows = $this->db->getRowsNum($result);
        if (1 == $numrows) {
            $object = new $this->classname($this->db->fetchArray($result));

            return $object;
        }
    }

    /**
     * Retrieve the list of departments for the specified tickets
     * @param mixed $tickets can be a single value or array consisting of either ticketids or ticket objects
     * @return array array of integers representing the ids of each department
     */
    public function getTicketDepartments($tickets): array
    {
        $a_tickets = [];
        $a_depts   = [];
        if (\is_array($tickets)) {
            foreach ($tickets as $ticket) {
                if (\is_object($ticket)) {
                    $a_tickets[] = $ticket->getVar('id');
                } else {
                    $a_tickets[] = (int)$ticket;
                }
            }
        } else {
            if (\is_object($tickets)) {
                $a_tickets[] = $tickets->getVar('id');
            } else {
                $a_tickets[] = (int)$tickets;
            }
        }

        $sql = \sprintf('SELECT DISTINCT department FROM `%s` WHERE id IN (%s)', $this->db->prefix('xhelp_tickets'), \implode(',', $a_tickets));
        $ret = $this->db->query($sql);

        while (false !== ($temp = $this->db->fetchArray($ret))) {
            $a_depts[] = $temp['department'];
        }

        return $a_depts;
    }

    /**
     * @param \CriteriaElement|\CriteriaCompo $criteria
     * @param bool                            $id_as_key
     * @param bool                            $hasCustFields
     * @return array
     */
    public function &getObjectsByStaff($criteria, bool $id_as_key = false, bool $hasCustFields = false): array
    {
        $sql = $this->selectQuery($criteria, true, $hasCustFields);
        if (\is_object($criteria)) {
            $limit = $criteria->getLimit();
            $start = $criteria->getStart();
        }

        $ret = $this->db->query($sql, $limit, $start);
        $arr = [];
        while (false !== ($temp = $this->db->fetchArray($ret))) {
            $tickets = $this->create();
            $tickets->assignVars($temp);
            if ($id_as_key) {
                $arr[$tickets->getVar('id')] = $tickets;
            } else {
                $arr[] = $tickets;
            }
            unset($tickets);
        }

        return $arr;
    }

    /**
     * @param int  $uid
     * @param bool $id_as_key
     * @return array
     */
    public function &getMyUnresolvedTickets(int $uid, bool $id_as_key = false): array
    {
        $uid = $uid;

        // Get all ticketEmail objects where $uid is found
        $ticketEmailsHandler = $this->helper->getHandler('TicketEmails');
        $criteria            = new \Criteria('uid', $uid);
        $ticketEmails        = $ticketEmailsHandler->getObjectsSortedByTicket($criteria);

        // Get friendly array of all ticketids needed
        $aTicketEmails = [];
        foreach ($ticketEmails as $ticketEmail) {
            $aTicketEmails[$ticketEmail->getVar('ticketid')] = $ticketEmail->getVar('ticketid');
        }
        unset($ticketEmails);

        // Get unresolved statuses and filter out the resolved statuses
        $statusHandler = $this->helper->getHandler('Status');
        $criteria      = new \Criteria('state', '1');
        $statuses      = $statusHandler->getObjects($criteria, true);
        $aStatuses     = [];
        foreach ($statuses as $status) {
            $aStatuses[$status->getVar('id')] = $status->getVar('id');
        }
        unset($statuses);

        // Get array of tickets.
        // Only want tickets that are unresolved.
        $criteria = new \CriteriaCompo(new \Criteria('t.id', '(' . \implode(',', \array_keys($aTicketEmails)) . ')', 'IN'));
        $criteria->add(new \Criteria('t.status', '(' . \implode(',', \array_keys($aStatuses)) . ')', 'IN'));
        $tickets = $this->getObjects($criteria, $id_as_key);

        // Return all tickets
        return $tickets;
    }

    /**
     * @param int  $state
     * @param bool $id_as_key
     * @return array
     */
    public function getObjectsByState(int $state, bool $id_as_key = false): array
    {
        $criteria = new \Criteria('state', $state, '=', 's');
        $sql      = $this->selectQuery($criteria, true);
        if (\is_object($criteria)) {
            $limit = $criteria->getLimit();
            $start = $criteria->getStart();
        }

        $ret = $this->db->query($sql, $limit, $start);
        $arr = [];
        while (false !== ($temp = $this->db->fetchArray($ret))) {
            $tickets = $this->create();
            $tickets->assignVars($temp);
            if ($id_as_key) {
                $arr[$tickets->getVar('id')] = $tickets;
            } else {
                $arr[] = $tickets;
            }
            unset($tickets);
        }

        return $arr;
    }

    /**
     * @param \CriteriaElement|\CriteriaCompo $criteria
     * @param bool                            $hasCustFields
     * @return int
     */
    public function getCountByStaff($criteria, bool $hasCustFields = false): int
    {
        if ($hasCustFields) {
            $sql = \sprintf(
                'SELECT COUNT(*) AS TicketCount FROM `%s` t INNER JOIN %s j ON t.department = j.department INNER JOIN %s s ON t.status = s.id INNER JOIN %s f ON t.id = f.ticketid ',
                $this->db->prefix('xhelp_tickets'),
                $this->db->prefix('xhelp_jstaffdept'),
                $this->db->prefix('xhelp_status'),
                $this->db->prefix('xhelp_ticket_values')
            );
        } else {
            $sql = \sprintf('SELECT COUNT(*) AS TicketCount FROM `%s` t INNER JOIN %s j ON t.department = j.department INNER JOIN %s s ON t.status = s.id', $this->db->prefix('xhelp_tickets'), $this->db->prefix('xhelp_jstaffdept'), $this->db->prefix('xhelp_status'));
        }

        if (($criteria instanceof \CriteriaCompo) || ($criteria instanceof \Criteria)) {
            $sql .= ' ' . $criteria->renderWhere();
        }

        if (!$result = $this->db->query($sql)) {
            return 0;
        }
        [$count] = $this->db->fetchRow($result);

        return (int)$count;
    }

    /**
     * Get all tickets a staff member is in dept
     * @param int    $uid   staff user id
     * @param int    $mode  One of the '_QRY_STAFF_{X}' constants
     * @param int    $start first record to return
     * @param int    $limit number of records to return
     * @param string $sort  Sort Field
     * @param string $order Sort Order
     * @return array  array of {@link Ticket}> objects
     * @todo   Filter by Department, Status
     */
    public function getStaffTickets(int $uid, int $mode = -1, int $start = 0, int $limit = 0, string $sort = '', string $order = ''): array
    {
        $uid      = $uid;
        $arr      = [];
        $criteria = new \CriteriaCompo();
        $criteria->setLimit($limit);
        $criteria->setStart($start);
        switch ($mode) {
            case \XHELP_QRY_STAFF_HIGHPRIORITY:
                $criteria->add(new \Criteria('uid', $uid, '=', 'j'));
                $criteria->add(new \Criteria('state', 1, '=', 's'));
                $criteria->add(new \Criteria('ownership', 0, '=', 't'));
                $criteria->setSort('t.priority, t.posted');
                break;
            case \XHELP_QRY_STAFF_NEW:
                $criteria->add(new \Criteria('uid', $uid, '=', 'j'));
                $criteria->add(new \Criteria('ownership', 0, '=', 't'));
                $criteria->add(new \Criteria('state', 1, '=', 's'));
                $criteria->setSort('t.posted');
                $criteria->setOrder('DESC');
                break;
            case \XHELP_QRY_STAFF_MINE:
                $criteria->add(new \Criteria('uid', $uid, '=', 'j'));
                $criteria->add(new \Criteria('ownership', $uid, '=', 't'));
                $criteria->add(new \Criteria('state', 1, '=', 's'));
                $criteria->setSort('t.posted');
                break;
            case \XHELP_QRY_STAFF_ALL:
                $criteria->add(new \Criteria('uid', $uid, '=', 'j'));
                break;
            default:
                return $arr;
        }

        return $this->getObjectsByStaff($criteria);
    }

    /**
     * Get number of tickets based on staff membership
     * @param int $uid staff user id
     * @param int $mode
     * @return int Number of tickets
     * @todo   Filter by Department, Status
     */
    public function getStaffTicketCount(int $uid, int $mode = -1): int
    {
        $criteria = new \CriteriaCompo();
        switch ($mode) {
            case \XHELP_QRY_STAFF_HIGHPRIORITY:
                $criteria->add(new \Criteria('uid', $uid, '=', 'j'));
                $criteria->add(new \Criteria('status', 2, '<', 't'));
                $criteria->add(new \Criteria('ownership', 0, '=', 't'));
                //$criteria->add($crit2);
                $criteria->setSort('t.priority, t.posted');
                break;
            case \XHELP_QRY_STAFF_NEW:
                $criteria->add(new \Criteria('uid', $uid, '=', 'j'));
                $criteria->add(new \Criteria('ownership', 0, '=', 't'));
                $criteria->add(new \Criteria('status', 2, '<', 't'));
                $criteria->setSort('t.posted');
                $criteria->setOrder('DESC');
                break;
            case \XHELP_QRY_STAFF_MINE:
                $criteria->add(new \Criteria('uid', $uid, '=', 'j'));
                $criteria->add(new \Criteria('ownership', $uid, '=', 't'));
                $criteria->add(new \Criteria('status', 2, '<', 't'));
                $criteria->setSort('t.posted');
                break;
            case \XHELP_QRY_STAFF_ALL:
                $criteria->add(new \Criteria('uid', $uid, '=', 'j'));
                break;
            default:
                return 0;
        }

        return $this->getCountByStaff($criteria);
    }

    /**
     * @param \XoopsObject $object
     * @return string
     */
    public function insertQuery(\XoopsObject $object): string
    {
        //TODO mb replace with individual variables
        // Copy all object vars into local variables
        foreach ($object->cleanVars as $k => $v) {
            ${$k} = $v;
        }

        $totalTimeSpent = Request::getInt('timespent', 0, 'POST');
        $ownership      = Request::getInt('owner', 0, 'POST');

        $sql = \sprintf(
            'INSERT INTO `%s` (uid, SUBJECT, description, department, priority, STATUS, lastUpdated, ownership, closedBy, totalTimeSpent, posted, userIP, emailHash, email, serverid, overdueTime)
            VALUES (%u, %s, %s, %u, %u, %u, %u, %u, %u, %u, %u, %s, %s, %s, %u, %u)',
            $this->db->prefix($this->dbtable),
            //            $id,
            $uid,
            $this->db->quoteString($subject),
            $this->db->quoteString($description),
            $department,
            $priority,
            $status,
            \time(),
            $ownership,
            $closedBy,
            $totalTimeSpent,
            $posted,
            $this->db->quoteString($userIP),
            $this->db->quoteString($emailHash),
            $this->db->quoteString($email),
            $serverid,
            $overdueTime
        );

        return $sql;
    }

    /**
     * @param \XoopsObject $object
     * @return string
     */
    public function updateQuery(\XoopsObject $object): string
    {
        //TODO mb replace with individual variables
        // Copy all object vars into local variables
        foreach ($object->cleanVars as $k => $v) {
            ${$k} = $v;
        }

        $sql = \sprintf(
            'UPDATE `%s` SET SUBJECT = %s, description = %s, department = %u, priority = %u, STATUS = %u, lastUpdated = %u, ownership = %u,
            closedBy = %u, totalTimeSpent = %u, userIP = %s, emailHash = %s, email = %s, serverid = %u, overdueTime = %u WHERE id = %u',
            $this->db->prefix($this->dbtable),
            $this->db->quoteString($subject),
            $this->db->quoteString($description),
            $department,
            $priority,
            $status,
            \time(),
            $ownership,
            $closedBy,
            $totalTimeSpent,
            $this->db->quoteString($userIP),
            $this->db->quoteString($emailHash),
            $this->db->quoteString($email),
            $serverid,
            $overdueTime,
            $id
        );

        return $sql;
    }

    /**
     * @param \XoopsObject $object
     * @return string
     */
    public function deleteQuery(\XoopsObject $object): string
    {
        $sql = \sprintf('DELETE FROM `%s` WHERE id = %u', $this->db->prefix($this->dbtable), $object->getVar('id'));

        return $sql;
    }

    /**
     * Create a "select" SQL query
     * @param \CriteriaElement|null $criteria {@link CriteriaElement} to match
     * @param bool                  $join
     * @param bool                  $hasCustFields
     * @return string SQL query
     */
    public function selectQuery(\CriteriaElement $criteria = null, bool $join = false, bool $hasCustFields = false): string
    {
        global $xoopsUser;
        if ($join) {
            if ($hasCustFields) {
                $sql = \sprintf(
                    'SELECT t.*, (UNIX_TIMESTAMP() - t.posted) AS elapsed, (UNIX_TIMESTAMP() - t.lastUpdated)
                                AS lastUpdate FROM `%s` t INNER JOIN %s j ON t.department = j.department INNER JOIN %s s
                                ON t.status = s.id INNER JOIN %s f ON t.id = f.ticketid',
                    $this->db->prefix('xhelp_tickets'),
                    $this->db->prefix('xhelp_jstaffdept'),
                    $this->db->prefix('xhelp_status'),
                    $this->db->prefix('xhelp_ticket_values')
                );
            } else {
                $sql = \sprintf(
                    'SELECT t.*, (UNIX_TIMESTAMP() - t.posted) AS elapsed, (UNIX_TIMESTAMP() - t.lastUpdated)
                                AS lastUpdate FROM `%s` t INNER JOIN %s j ON t.department = j.department INNER JOIN %s s
                                ON t.status = s.id',
                    $this->db->prefix('xhelp_tickets'),
                    $this->db->prefix('xhelp_jstaffdept'),
                    $this->db->prefix('xhelp_status')
                );
            }
        } else {
            $sql = \sprintf(
                'SELECT t.*, (UNIX_TIMESTAMP() - t.posted) AS elapsed, (UNIX_TIMESTAMP() - t.lastUpdated)
                            AS lastUpdate  FROM `%s` t INNER JOIN %s s ON t.status = s.id',
                $this->db->prefix($this->dbtable),
                $this->db->prefix('xhelp_status')
            );
        }
        if (($criteria instanceof \CriteriaCompo) || ($criteria instanceof \Criteria)) {
            $sql .= ' ' . $criteria->renderWhere();
            if ('' != $criteria->getSort()) {
                $sql .= ' ORDER BY ' . $criteria->getSort() . ' ' . $criteria->getOrder();
            }
        }
        if (!empty($xoopsUser)) {
            $sql = \str_replace((string)\XHELP_GLOBAL_UID, (string)$xoopsUser->getVar('uid'), $sql);
        }

        return $sql;
    }

    /**
     * delete a ticket from the database
     *
     * @param \XoopsObject $object    reference to the {@link Ticket}
     *                                obj to delete
     * @param bool         $force
     * @return bool FALSE if failed.
     */
    public function delete(\XoopsObject $object, $force = false): bool
    {
        if (0 != \strcasecmp($this->classname, \get_class($object))) {
            return false;
        }

        // Remove all ticket responses first
        $responseHandler = $this->helper->getHandler('Response');
        if (!$responseHandler->deleteAll(new \Criteria('ticketid', $object->getVar('id')))) {
            return false;
        }

        // Remove all files associated with this ticket
        $fileHandler = $this->helper->getHandler('File');
        if (!$fileHandler->deleteAll(new \Criteria('ticketid', $object->getVar('id')))) {
            return false;
        }

        // Remove custom field values for this ticket
        $ticketValuesHandler = $this->helper->getHandler('TicketValues');
        if (!$ticketValuesHandler->deleteAll(new \Criteria('ticketid', $object->getVar('id')))) {
            return false;
        }

        $ret = parent::delete($object, $force);

        return $ret;
    }

    /**
     * increment a value to 1 field for tickets matching a set of conditions
     *
     * @param string                               $fieldname
     * @param mixed                                $fieldvalue
     * @param \CriteriaElement|\CriteriaCompo|null $criteria {@link CriteriaElement}
     * @return bool FALSE if deletion failed
     */
    public function incrementAll(string $fieldname, $fieldvalue, $criteria = null): bool
    {
        $set_clause = \is_numeric($fieldvalue) ? $fieldname . ' = ' . $fieldname . '+' . $fieldvalue : $fieldname . ' = ' . $fieldname . '+' . $this->db->quoteString($fieldvalue);
        $sql        = 'UPDATE ' . $this->db->prefix($this->dbtable) . ' SET ' . $set_clause;
        if (($criteria instanceof \CriteriaCompo) || ($criteria instanceof \Criteria)) {
            $sql .= ' ' . $criteria->renderWhere();
        }
        if (!$result = $this->db->query($sql)) {
            return false;
        }

        return true;
    }
}   // end of handler class


1			<?php declare(strict_types=1);
2
3			namespace XoopsModules\Xhelp;
4
5			/*
6			* You may not change or alter any portion of this comment or credits
7			* of supporting developers from this source code or any supporting source code
8			* which is considered copyrighted (c) material of the original comment or credit authors.
9			*
10			* This program is distributed in the hope that it will be useful,
11			* but WITHOUT ANY WARRANTY; without even the implied warranty of
12			* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
13			*/
14
15			/**
16			* @copyright {@link https://xoops.org/ XOOPS Project}
17			* @license {@link https://www.gnu.org/licenses/gpl-2.0.html GNU GPL 2 or later}
18			* @author Eric Juden <[email protected]>
19			* @author XOOPS Development Team
20			*/
21
22			use Xmf\Request;
23			use XoopsModules\Xhelp\RequestsHandler;
			0 ignored issues – show Bug introduced 2022-01-20 18:30 UTC by Report Bug Copy Issue Report Show Similar Issues like this The type `XoopsModules\Xhelp\RequestsHandler` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
24
25			if (!\defined('XHELP_CLASS_PATH')) {
26			exit();
27			}
28			// require_once XHELP_CLASS_PATH . '/BaseObjectHandler.php';
29			//require_once \dirname(__DIR__, 3) . '/include/cp_header.php';
30
31			global $xoopsUser;
32
33			/**
34			* class TicketHandler
35			*/
36			class TicketHandler extends BaseObjectHandler
37			{
38			/**
39			* Name of child class
40			*
41			* @var string
42			*/
43			public $classname = Ticket::class;
44			/**
45			* DB Table Name
46			*
47			* @var string
48			*/
49			public $dbtable = 'xhelp_tickets';
50
51			private const TABLE = 'xhelp_tickets';
52			private const ENTITY = Ticket::class;
53			private const ENTITYNAME = 'Ticket';
54			private const KEYNAME = 'id';
55			private const IDENTIFIER = 'uid';
56
57			/**
58			* Constructor
59			*
60			* @param \XoopsMySQLDatabase\|null $db reference to a xoopsDB object
61			*/
62			public function __construct(\XoopsMySQLDatabase $db = null)
63			{
64			$this->init($db);
65			$this->helper = Helper::getInstance();
66			parent::__construct($db, static::TABLE, static::ENTITY, static::KEYNAME, static::IDENTIFIER);
67			}
68
69			/**
70			* retrieve an object from the database, based on. use in child classes
71			* @param int $id ID
72			* @return mixed object if id exists, false if not
73			*/
74			public function get($id = null, $fields = null)
75			{
76			$id = (int)$id;
77			if ($id > 0) {
78			$sql = $this->selectQuery(new \Criteria('id', $id, '=', 't'));
79			if (!$result = $this->db->query($sql)) {
80			return false;
81			}
82			$numrows = $this->db->getRowsNum($result);
83			if (1 == $numrows) {
84			$object = new $this->classname($this->db->fetchArray($result));
85
86			return $object;
87			}
88			}
89
90			return false;
91			}
92
93			/**
94			* find a ticket based on a hash
95			*
96			* @param string $hash
97			* @return Ticket object
98			*/
99			public function getTicketByHash(string $hash): ?Ticket
100			{
101			$sql = $this->selectQuery(new \Criteria('emailHash', $hash, '=', 't'));
102			if (!$result = $this->db->query($sql)) {
103			return null;
104			}
105			$numrows = $this->db->getRowsNum($result);
106			if (1 == $numrows) {
107			$object = new $this->classname($this->db->fetchArray($result));
108
109			return $object;
110			}
111			}
112
113			/**
114			* Retrieve the list of departments for the specified tickets
115			* @param mixed $tickets can be a single value or array consisting of either ticketids or ticket objects
116			* @return array array of integers representing the ids of each department
117			*/
118			public function getTicketDepartments($tickets): array
119			{
120			$a_tickets = [];
121			$a_depts = [];
122			if (\is_array($tickets)) {
123			foreach ($tickets as $ticket) {
124			if (\is_object($ticket)) {
125			$a_tickets[] = $ticket->getVar('id');
126			} else {
127			$a_tickets[] = (int)$ticket;
128			}
129			}
130			} else {
131			if (\is_object($tickets)) {
132			$a_tickets[] = $tickets->getVar('id');
133			} else {
134			$a_tickets[] = (int)$tickets;
135			}
136			}
137
138			$sql = \sprintf('SELECT DISTINCT department FROM `%s` WHERE id IN (%s)', $this->db->prefix('xhelp_tickets'), \implode(',', $a_tickets));
139			$ret = $this->db->query($sql);
140
141			while (false !== ($temp = $this->db->fetchArray($ret))) {
142			$a_depts[] = $temp['department'];
143			}
144
145			return $a_depts;
146			}
147
148			/**
149			* @param \CriteriaElement\|\CriteriaCompo $criteria
150			* @param bool $id_as_key
151			* @param bool $hasCustFields
152			* @return array
153			*/
154			public function &getObjectsByStaff($criteria, bool $id_as_key = false, bool $hasCustFields = false): array
155			{
156			$sql = $this->selectQuery($criteria, true, $hasCustFields);
157			if (\is_object($criteria)) {
158			$limit = $criteria->getLimit();
159			$start = $criteria->getStart();
160			}
161
162			$ret = $this->db->query($sql, $limit, $start);
163			$arr = [];
164			while (false !== ($temp = $this->db->fetchArray($ret))) {
165			$tickets = $this->create();
166			$tickets->assignVars($temp);
167			if ($id_as_key) {
168			$arr[$tickets->getVar('id')] = $tickets;
169			} else {
170			$arr[] = $tickets;
171			}
172			unset($tickets);
173			}
174
175			return $arr;
176			}
177
178			/**
179			* @param int $uid
180			* @param bool $id_as_key
181			* @return array
182			*/
183			public function &getMyUnresolvedTickets(int $uid, bool $id_as_key = false): array
184			{
185			$uid = $uid;
186
187			// Get all ticketEmail objects where $uid is found
188			$ticketEmailsHandler = $this->helper->getHandler('TicketEmails');
189			$criteria = new \Criteria('uid', $uid);
190			$ticketEmails = $ticketEmailsHandler->getObjectsSortedByTicket($criteria);
191
192			// Get friendly array of all ticketids needed
193			$aTicketEmails = [];
194			foreach ($ticketEmails as $ticketEmail) {
195			$aTicketEmails[$ticketEmail->getVar('ticketid')] = $ticketEmail->getVar('ticketid');
196			}
197			unset($ticketEmails);
198
199			// Get unresolved statuses and filter out the resolved statuses
200			$statusHandler = $this->helper->getHandler('Status');
201			$criteria = new \Criteria('state', '1');
202			$statuses = $statusHandler->getObjects($criteria, true);
203			$aStatuses = [];
204			foreach ($statuses as $status) {
205			$aStatuses[$status->getVar('id')] = $status->getVar('id');
206			}
207			unset($statuses);
208
209			// Get array of tickets.
210			// Only want tickets that are unresolved.
211			$criteria = new \CriteriaCompo(new \Criteria('t.id', '(' . \implode(',', \array_keys($aTicketEmails)) . ')', 'IN'));
212			$criteria->add(new \Criteria('t.status', '(' . \implode(',', \array_keys($aStatuses)) . ')', 'IN'));
213			$tickets = $this->getObjects($criteria, $id_as_key);
214
215			// Return all tickets
216			return $tickets;
217			}
218
219			/**
220			* @param int $state
221			* @param bool $id_as_key
222			* @return array
223			*/
224			public function getObjectsByState(int $state, bool $id_as_key = false): array
225			{
226			$criteria = new \Criteria('state', $state, '=', 's');
227			$sql = $this->selectQuery($criteria, true);
228			if (\is_object($criteria)) {
229			$limit = $criteria->getLimit();
230			$start = $criteria->getStart();
231			}
232
233			$ret = $this->db->query($sql, $limit, $start);
234			$arr = [];
235			while (false !== ($temp = $this->db->fetchArray($ret))) {
236			$tickets = $this->create();
237			$tickets->assignVars($temp);
238			if ($id_as_key) {
239			$arr[$tickets->getVar('id')] = $tickets;
240			} else {
241			$arr[] = $tickets;
242			}
243			unset($tickets);
244			}
245
246			return $arr;
247			}
248
249			/**
250			* @param \CriteriaElement\|\CriteriaCompo $criteria
251			* @param bool $hasCustFields
252			* @return int
253			*/
254			public function getCountByStaff($criteria, bool $hasCustFields = false): int
255			{
256			if ($hasCustFields) {
257			$sql = \sprintf(
258			'SELECT COUNT(*) AS TicketCount FROM `%s` t INNER JOIN %s j ON t.department = j.department INNER JOIN %s s ON t.status = s.id INNER JOIN %s f ON t.id = f.ticketid ',
259			$this->db->prefix('xhelp_tickets'),
260			$this->db->prefix('xhelp_jstaffdept'),
261			$this->db->prefix('xhelp_status'),
262			$this->db->prefix('xhelp_ticket_values')
263			);
264			} else {
265			$sql = \sprintf('SELECT COUNT(*) AS TicketCount FROM `%s` t INNER JOIN %s j ON t.department = j.department INNER JOIN %s s ON t.status = s.id', $this->db->prefix('xhelp_tickets'), $this->db->prefix('xhelp_jstaffdept'), $this->db->prefix('xhelp_status'));
266			}
267
268			if (($criteria instanceof \CriteriaCompo) \|\| ($criteria instanceof \Criteria)) {
269			$sql .= ' ' . $criteria->renderWhere();
270			}
271
272			if (!$result = $this->db->query($sql)) {
273			return 0;
274			}
275			[$count] = $this->db->fetchRow($result);
276
277			return (int)$count;
278			}
279
280			/**
281			* Get all tickets a staff member is in dept
282			* @param int $uid staff user id
283			* @param int $mode One of the '_QRY_STAFF_{X}' constants
284			* @param int $start first record to return
285			* @param int $limit number of records to return
286			* @param string $sort Sort Field
287			* @param string $order Sort Order
288			* @return array array of {@link Ticket}> objects
289			* @todo Filter by Department, Status
290			*/
291			public function getStaffTickets(int $uid, int $mode = -1, int $start = 0, int $limit = 0, string $sort = '', string $order = ''): array
292			{
293			$uid = $uid;
294			$arr = [];
295			$criteria = new \CriteriaCompo();
296			$criteria->setLimit($limit);
297			$criteria->setStart($start);
298			switch ($mode) {
299			case \XHELP_QRY_STAFF_HIGHPRIORITY:
300			$criteria->add(new \Criteria('uid', $uid, '=', 'j'));
301			$criteria->add(new \Criteria('state', 1, '=', 's'));
302			$criteria->add(new \Criteria('ownership', 0, '=', 't'));
303			$criteria->setSort('t.priority, t.posted');
304			break;
305			case \XHELP_QRY_STAFF_NEW:
306			$criteria->add(new \Criteria('uid', $uid, '=', 'j'));
307			$criteria->add(new \Criteria('ownership', 0, '=', 't'));
308			$criteria->add(new \Criteria('state', 1, '=', 's'));
309			$criteria->setSort('t.posted');
310			$criteria->setOrder('DESC');
311			break;
312			case \XHELP_QRY_STAFF_MINE:
313			$criteria->add(new \Criteria('uid', $uid, '=', 'j'));
314			$criteria->add(new \Criteria('ownership', $uid, '=', 't'));
315			$criteria->add(new \Criteria('state', 1, '=', 's'));
316			$criteria->setSort('t.posted');
317			break;
318			case \XHELP_QRY_STAFF_ALL:
319			$criteria->add(new \Criteria('uid', $uid, '=', 'j'));
320			break;
321			default:
322			return $arr;
323			}
324
325			return $this->getObjectsByStaff($criteria);
326			}
327
328			/**
329			* Get number of tickets based on staff membership
330			* @param int $uid staff user id
331			* @param int $mode
332			* @return int Number of tickets
333			* @todo Filter by Department, Status
334			*/
335			public function getStaffTicketCount(int $uid, int $mode = -1): int
336			{
337			$criteria = new \CriteriaCompo();
338			switch ($mode) {
339			case \XHELP_QRY_STAFF_HIGHPRIORITY:
340			$criteria->add(new \Criteria('uid', $uid, '=', 'j'));
341			$criteria->add(new \Criteria('status', 2, '<', 't'));
342			$criteria->add(new \Criteria('ownership', 0, '=', 't'));
343			//$criteria->add($crit2);
344			$criteria->setSort('t.priority, t.posted');
345			break;
346			case \XHELP_QRY_STAFF_NEW:
347			$criteria->add(new \Criteria('uid', $uid, '=', 'j'));
348			$criteria->add(new \Criteria('ownership', 0, '=', 't'));
349			$criteria->add(new \Criteria('status', 2, '<', 't'));
350			$criteria->setSort('t.posted');
351			$criteria->setOrder('DESC');
352			break;
353			case \XHELP_QRY_STAFF_MINE:
354			$criteria->add(new \Criteria('uid', $uid, '=', 'j'));
355			$criteria->add(new \Criteria('ownership', $uid, '=', 't'));
356			$criteria->add(new \Criteria('status', 2, '<', 't'));
357			$criteria->setSort('t.posted');
358			break;
359			case \XHELP_QRY_STAFF_ALL:
360			$criteria->add(new \Criteria('uid', $uid, '=', 'j'));
361			break;
362			default:
363			return 0;
364			}
365
366			return $this->getCountByStaff($criteria);
367			}
368
369			/**
370			* @param \XoopsObject $object
371			* @return string
372			*/
373			public function insertQuery(\XoopsObject $object): string
374			{
375			//TODO mb replace with individual variables
376			// Copy all object vars into local variables
377			foreach ($object->cleanVars as $k => $v) {
378			${$k} = $v;
379			}
380
381			$totalTimeSpent = Request::getInt('timespent', 0, 'POST');
382			$ownership = Request::getInt('owner', 0, 'POST');
383
384			$sql = \sprintf(
385			'INSERT INTO `%s` (uid, SUBJECT, description, department, priority, STATUS, lastUpdated, ownership, closedBy, totalTimeSpent, posted, userIP, emailHash, email, serverid, overdueTime)
386			VALUES (%u, %s, %s, %u, %u, %u, %u, %u, %u, %u, %u, %s, %s, %s, %u, %u)',
387			$this->db->prefix($this->dbtable),
388			// $id,
389			$uid,
390			$this->db->quoteString($subject),
391			$this->db->quoteString($description),
392			$department,
393			$priority,
394			$status,
395			\time(),
396			$ownership,
397			$closedBy,
398			$totalTimeSpent,
399			$posted,
400			$this->db->quoteString($userIP),
401			$this->db->quoteString($emailHash),
402			$this->db->quoteString($email),
403			$serverid,
404			$overdueTime
405			);
406
407			return $sql;
408			}
409
410			/**
411			* @param \XoopsObject $object
412			* @return string
413			*/
414			public function updateQuery(\XoopsObject $object): string
415			{
416			//TODO mb replace with individual variables
417			// Copy all object vars into local variables
418			foreach ($object->cleanVars as $k => $v) {
419			${$k} = $v;
420			}
421
422			$sql = \sprintf(
423			'UPDATE `%s` SET SUBJECT = %s, description = %s, department = %u, priority = %u, STATUS = %u, lastUpdated = %u, ownership = %u,
424			closedBy = %u, totalTimeSpent = %u, userIP = %s, emailHash = %s, email = %s, serverid = %u, overdueTime = %u WHERE id = %u',
425			$this->db->prefix($this->dbtable),
426			$this->db->quoteString($subject),
427			$this->db->quoteString($description),
428			$department,
429			$priority,
430			$status,
431			\time(),
432			$ownership,
433			$closedBy,
434			$totalTimeSpent,
435			$this->db->quoteString($userIP),
436			$this->db->quoteString($emailHash),
437			$this->db->quoteString($email),
438			$serverid,
439			$overdueTime,
440			$id
441			);
442
443			return $sql;
444			}
445
446			/**
447			* @param \XoopsObject $object
448			* @return string
449			*/
450			public function deleteQuery(\XoopsObject $object): string
451			{
452			$sql = \sprintf('DELETE FROM `%s` WHERE id = %u', $this->db->prefix($this->dbtable), $object->getVar('id'));
453
454			return $sql;
455			}
456
457			/**
458			* Create a "select" SQL query
459			* @param \CriteriaElement\|null $criteria {@link CriteriaElement} to match
460			* @param bool $join
461			* @param bool $hasCustFields
462			* @return string SQL query
463			*/
464			public function selectQuery(\CriteriaElement $criteria = null, bool $join = false, bool $hasCustFields = false): string
465			{
466			global $xoopsUser;
467			if ($join) {
468			if ($hasCustFields) {
469			$sql = \sprintf(
470			'SELECT t.*, (UNIX_TIMESTAMP() - t.posted) AS elapsed, (UNIX_TIMESTAMP() - t.lastUpdated)
471			AS lastUpdate FROM `%s` t INNER JOIN %s j ON t.department = j.department INNER JOIN %s s
472			ON t.status = s.id INNER JOIN %s f ON t.id = f.ticketid',
473			$this->db->prefix('xhelp_tickets'),
474			$this->db->prefix('xhelp_jstaffdept'),
475			$this->db->prefix('xhelp_status'),
476			$this->db->prefix('xhelp_ticket_values')
477			);
478			} else {
479			$sql = \sprintf(
480			'SELECT t.*, (UNIX_TIMESTAMP() - t.posted) AS elapsed, (UNIX_TIMESTAMP() - t.lastUpdated)
481			AS lastUpdate FROM `%s` t INNER JOIN %s j ON t.department = j.department INNER JOIN %s s
482			ON t.status = s.id',
483			$this->db->prefix('xhelp_tickets'),
484			$this->db->prefix('xhelp_jstaffdept'),
485			$this->db->prefix('xhelp_status')
486			);
487			}
488			} else {
489			$sql = \sprintf(
490			'SELECT t.*, (UNIX_TIMESTAMP() - t.posted) AS elapsed, (UNIX_TIMESTAMP() - t.lastUpdated)
491			AS lastUpdate FROM `%s` t INNER JOIN %s s ON t.status = s.id',
492			$this->db->prefix($this->dbtable),
493			$this->db->prefix('xhelp_status')
494			);
495			}
496			if (($criteria instanceof \CriteriaCompo) \|\| ($criteria instanceof \Criteria)) {
497			$sql .= ' ' . $criteria->renderWhere();
498			if ('' != $criteria->getSort()) {
499			$sql .= ' ORDER BY ' . $criteria->getSort() . ' ' . $criteria->getOrder();
500			}
501			}
502			if (!empty($xoopsUser)) {
503			$sql = \str_replace((string)\XHELP_GLOBAL_UID, (string)$xoopsUser->getVar('uid'), $sql);
504			}
505
506			return $sql;
507			}
508
509			/**
510			* delete a ticket from the database
511			*
512			* @param \XoopsObject $object reference to the {@link Ticket}
513			* obj to delete
514			* @param bool $force
515			* @return bool FALSE if failed.
516			*/
517			public function delete(\XoopsObject $object, $force = false): bool
518			{
519			if (0 != \strcasecmp($this->classname, \get_class($object))) {
520			return false;
521			}
522
523			// Remove all ticket responses first
524			$responseHandler = $this->helper->getHandler('Response');
525			if (!$responseHandler->deleteAll(new \Criteria('ticketid', $object->getVar('id')))) {
526			return false;
527			}
528
529			// Remove all files associated with this ticket
530			$fileHandler = $this->helper->getHandler('File');
531			if (!$fileHandler->deleteAll(new \Criteria('ticketid', $object->getVar('id')))) {
532			return false;
533			}
534
535			// Remove custom field values for this ticket
536			$ticketValuesHandler = $this->helper->getHandler('TicketValues');
537			if (!$ticketValuesHandler->deleteAll(new \Criteria('ticketid', $object->getVar('id')))) {
538			return false;
539			}
540
541			$ret = parent::delete($object, $force);
542
543			return $ret;
544			}
545
546			/**
547			* increment a value to 1 field for tickets matching a set of conditions
548			*
549			* @param string $fieldname
550			* @param mixed $fieldvalue
551			* @param \CriteriaElement\|\CriteriaCompo\|null $criteria {@link CriteriaElement}
552			* @return bool FALSE if deletion failed
553			*/
554			public function incrementAll(string $fieldname, $fieldvalue, $criteria = null): bool
555			{
556			$set_clause = \is_numeric($fieldvalue) ? $fieldname . ' = ' . $fieldname . '+' . $fieldvalue : $fieldname . ' = ' . $fieldname . '+' . $this->db->quoteString($fieldvalue);
557			$sql = 'UPDATE ' . $this->db->prefix($this->dbtable) . ' SET ' . $set_clause;
558			if (($criteria instanceof \CriteriaCompo) \|\| ($criteria instanceof \Criteria)) {
559			$sql .= ' ' . $criteria->renderWhere();
560			}
561			if (!$result = $this->db->query($sql)) {
562			return false;
563			}
564
565			return true;
566			}
567			} // end of handler class
568

XoopsModules25x / xhelp

Issues (1844)

Security Analysis not enabled

class/TicketHandler.php (1 issue)

Labels

Severity

Introduced By

Duplication Side-by-Side

Filter issues like