TicketManager.php 81 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. use Chamilo\TicketBundle\Entity\Project;
  4. use Chamilo\TicketBundle\Entity\Status;
  5. use Chamilo\TicketBundle\Entity\Priority;
  6. use Chamilo\TicketBundle\Entity\Ticket;
  7. /**
  8. * Class TicketManager
  9. * @package chamilo.plugin.ticket
  10. */
  11. class TicketManager
  12. {
  13. const PRIORITY_NORMAL = 'NRM';
  14. const PRIORITY_HIGH = 'HGH';
  15. const PRIORITY_LOW = 'LOW';
  16. const SOURCE_EMAIL = 'MAI';
  17. const SOURCE_PHONE = 'TEL';
  18. const SOURCE_PLATFORM = 'PLA';
  19. const SOURCE_PRESENTIAL = 'PRE';
  20. const STATUS_NEW = 'NAT';
  21. const STATUS_PENDING = 'PND';
  22. const STATUS_UNCONFIRMED = 'XCF';
  23. const STATUS_CLOSE = 'CLS';
  24. const STATUS_FORWARDED = 'REE';
  25. /**
  26. * Constructor
  27. */
  28. public function __construct()
  29. {
  30. }
  31. /**
  32. * Get categories of tickets
  33. *
  34. * @param int $projectId
  35. * @param string $order
  36. *
  37. * @return array
  38. */
  39. public static function get_all_tickets_categories($projectId, $order = '')
  40. {
  41. $table_support_category = Database::get_main_table(TABLE_TICKET_CATEGORY);
  42. $table_support_project = Database::get_main_table(TABLE_TICKET_PROJECT);
  43. $order = empty($order) ? 'category.total_tickets DESC' : $order;
  44. $projectId = (int) $projectId;
  45. $sql = "SELECT
  46. category.*,
  47. category.id category_id,
  48. project.other_area,
  49. project.email
  50. FROM
  51. $table_support_category category
  52. INNER JOIN $table_support_project project
  53. ON project.id = category.project_id
  54. WHERE project.id = $projectId
  55. ORDER BY $order";
  56. $result = Database::query($sql);
  57. $types = array();
  58. while ($row = Database::fetch_assoc($result)) {
  59. $types[] = $row;
  60. }
  61. return $types;
  62. }
  63. /**
  64. * @param $from
  65. * @param $numberItems
  66. * @param $column
  67. * @param $direction
  68. * @return array
  69. */
  70. public static function getCategories($from, $numberItems, $column, $direction)
  71. {
  72. $table = Database::get_main_table(TABLE_TICKET_CATEGORY);
  73. $sql = "SELECT id, name, description, total_tickets
  74. FROM $table";
  75. if (!in_array($direction, array('ASC','DESC'))) {
  76. $direction = 'ASC';
  77. }
  78. $column = intval($column);
  79. $from = intval($from);
  80. $numberItems = intval($numberItems);
  81. //$sql .= " ORDER BY col$column $direction ";
  82. $sql .= " LIMIT $from,$numberItems";
  83. $result = Database::query($sql);
  84. $types = array();
  85. while ($row = Database::fetch_array($result)) {
  86. $types[] = $row;
  87. }
  88. return $types;
  89. }
  90. /**
  91. * @param int $id
  92. * @return array|mixed
  93. */
  94. public static function getCategory($id)
  95. {
  96. $table = Database::get_main_table(TABLE_TICKET_CATEGORY);
  97. $id = intval($id);
  98. $sql = "SELECT id, name, description, total_tickets
  99. FROM $table WHERE id = $id";
  100. $result = Database::query($sql);
  101. $category = Database::fetch_array($result);
  102. return $category;
  103. }
  104. /**
  105. * @return int
  106. */
  107. public static function getCategoriesCount()
  108. {
  109. $table = Database::get_main_table(TABLE_TICKET_CATEGORY);
  110. $sql = "SELECT count(id) count
  111. FROM $table ";
  112. $result = Database::query($sql);
  113. $category = Database::fetch_array($result);
  114. return $category['count'];
  115. }
  116. /**
  117. * @param int $id
  118. * @param array $params
  119. */
  120. public static function updateCategory($id, $params)
  121. {
  122. $table = Database::get_main_table(TABLE_TICKET_CATEGORY);
  123. $id = intval($id);
  124. Database::update($table, $params, ['id = ?' => $id]);
  125. }
  126. /**
  127. * @param array $params
  128. */
  129. public static function addCategory($params)
  130. {
  131. $table = Database::get_main_table(TABLE_TICKET_CATEGORY);
  132. Database::insert($table, $params);
  133. }
  134. /**
  135. * @param int $id
  136. *
  137. * @return bool
  138. */
  139. public static function deleteCategory($id)
  140. {
  141. $id = intval($id);
  142. if (empty($id)) {
  143. return false;
  144. }
  145. $table = Database::get_main_table(TABLE_TICKET_TICKET);
  146. $sql = "UPDATE $table SET category_id = NULL WHERE category_id = $id";
  147. Database::query($sql);
  148. $table = Database::get_main_table(TABLE_TICKET_CATEGORY);
  149. $sql = "DELETE FROM $table WHERE id = $id";
  150. Database::query($sql);
  151. return true;
  152. }
  153. /**
  154. * @param int $categoryId
  155. * @param array $users
  156. *
  157. * @return bool
  158. */
  159. public static function addUsersToCategory($categoryId, $users)
  160. {
  161. $table = Database::get_main_table(TABLE_TICKET_CATEGORY_REL_USER);
  162. if (empty($users) || empty($categoryId)) {
  163. return false;
  164. }
  165. foreach ($users as $userId) {
  166. if (self::userIsAssignedToCategory($userId, $categoryId) == false) {
  167. $params = [
  168. 'category_id' => $categoryId,
  169. 'user_id' => $userId
  170. ];
  171. Database::insert($table, $params);
  172. }
  173. }
  174. return true;
  175. }
  176. /**
  177. * @param int $userId
  178. * @param int $categoryId
  179. *
  180. * @return bool
  181. */
  182. public static function userIsAssignedToCategory($userId, $categoryId)
  183. {
  184. $table = Database::get_main_table(TABLE_TICKET_CATEGORY_REL_USER);
  185. $userId = intval($userId);
  186. $categoryId = intval($categoryId);
  187. $sql = "SELECT * FROM $table
  188. WHERE category_id = $categoryId AND user_id = $userId";
  189. $result = Database::query($sql);
  190. return Database::num_rows($result) > 0;
  191. }
  192. /**
  193. * @param int $categoryId
  194. *
  195. * @return array
  196. */
  197. public static function getUsersInCategory($categoryId)
  198. {
  199. $table = Database::get_main_table(TABLE_TICKET_CATEGORY_REL_USER);
  200. $categoryId = intval($categoryId);
  201. $sql = "SELECT * FROM $table WHERE category_id = $categoryId";
  202. $result = Database::query($sql);
  203. return Database::store_result($result);
  204. }
  205. /**
  206. * @param int $categoryId
  207. */
  208. public static function deleteAllUserInCategory($categoryId)
  209. {
  210. $table = Database::get_main_table(TABLE_TICKET_CATEGORY_REL_USER);
  211. $categoryId = intval($categoryId);
  212. $sql = "DELETE FROM $table WHERE category_id = $categoryId";
  213. Database::query($sql);
  214. }
  215. /**
  216. * Get all possible tickets statuses
  217. * @return array
  218. */
  219. public static function get_all_tickets_status()
  220. {
  221. $table = Database::get_main_table(TABLE_TICKET_STATUS);
  222. $sql = "SELECT * FROM " . $table;
  223. $result = Database::query($sql);
  224. $types = array();
  225. while ($row = Database::fetch_assoc($result)) {
  226. $types[] = $row;
  227. }
  228. return $types;
  229. }
  230. /**
  231. * Inserts a new ticket in the corresponding tables
  232. * @param int $category_id
  233. * @param int $course_id
  234. * @param int $sessionId
  235. * @param int $project_id
  236. * @param string $other_area
  237. * @param string $subject
  238. * @param string $content
  239. * @param string $personalEmail
  240. * @param $file_attachments
  241. * @param string $source
  242. * @param string $priority
  243. * @param string $status
  244. * @param int $assignedUserId
  245. *
  246. * @return bool
  247. */
  248. public static function add(
  249. $category_id,
  250. $course_id,
  251. $sessionId,
  252. $project_id,
  253. $other_area,
  254. $subject,
  255. $content,
  256. $personalEmail = '',
  257. $file_attachments = [],
  258. $source = '',
  259. $priority = '',
  260. $status = '',
  261. $assignedUserId = 0
  262. ) {
  263. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  264. $table_support_category = Database::get_main_table(TABLE_TICKET_CATEGORY);
  265. if (empty($category_id)) {
  266. return false;
  267. }
  268. $currentUserId = api_get_user_id();
  269. $currentUserInfo = api_get_user_info();
  270. $now = api_get_utc_datetime();
  271. $course_id = intval($course_id);
  272. $category_id = intval($category_id);
  273. $project_id = intval($project_id);
  274. $priority = empty($priority) ? self::PRIORITY_NORMAL : $priority;
  275. if ($status === '') {
  276. $status = self::STATUS_NEW;
  277. if ($other_area > 0) {
  278. $status = self::STATUS_FORWARDED;
  279. }
  280. }
  281. if (!empty($category_id)) {
  282. if (empty($assignedUserId)) {
  283. $usersInCategory = TicketManager::getUsersInCategory($category_id);
  284. if (!empty($usersInCategory) && count($usersInCategory) > 0) {
  285. $userCategoryInfo = $usersInCategory[0];
  286. if (isset($userCategoryInfo['user_id'])) {
  287. $assignedUserId = $userCategoryInfo['user_id'];
  288. }
  289. }
  290. }
  291. }
  292. $assignedUserInfo = [];
  293. if (!empty($assignedUserId)) {
  294. $assignedUserInfo = api_get_user_info($assignedUserId);
  295. if (empty($assignedUserInfo)) {
  296. return false;
  297. }
  298. }
  299. // insert_ticket
  300. $params = [
  301. 'project_id' => $project_id,
  302. 'category_id' => $category_id,
  303. 'priority_id' => $priority,
  304. 'personal_email' => $personalEmail,
  305. 'status_id' => $status,
  306. 'start_date' => $now,
  307. 'sys_insert_user_id' => $currentUserId,
  308. 'sys_insert_datetime' => $now,
  309. 'sys_lastedit_user_id' => $currentUserId,
  310. 'sys_lastedit_datetime' => $now,
  311. 'source' => $source,
  312. 'assigned_last_user' => $assignedUserId,
  313. 'subject' => $subject,
  314. 'message' => $content
  315. ];
  316. if (!empty($course_id)) {
  317. $params['course_id'] = $course_id;
  318. }
  319. if (!empty($sessionId)) {
  320. $params['session_id'] = $sessionId;
  321. }
  322. $ticketId = Database::insert($table_support_tickets, $params);
  323. if ($ticketId) {
  324. $ticket_code = "A" . str_pad($ticketId, 11, '0', STR_PAD_LEFT);
  325. $titleCreated = sprintf(
  326. get_lang('TicketXCreated'),
  327. $ticket_code
  328. );
  329. Display::addFlash(Display::return_message(
  330. $titleCreated,
  331. 'normal',
  332. false
  333. ));
  334. if ($assignedUserId != 0) {
  335. self::assignTicketToUser(
  336. $ticketId,
  337. $assignedUserId
  338. );
  339. Display::addFlash(Display::return_message(
  340. sprintf(
  341. get_lang('TicketXAssignedToUserX'),
  342. $ticket_code,
  343. $assignedUserInfo['complete_name']
  344. ),
  345. 'normal',
  346. false
  347. ));
  348. }
  349. if (!empty($file_attachments)) {
  350. $attachmentCount = 0;
  351. foreach ($file_attachments as $attach) {
  352. if (!empty($attach['tmp_name'])) {
  353. $attachmentCount++;
  354. }
  355. }
  356. if ($attachmentCount > 0) {
  357. self::insertMessage(
  358. $ticketId,
  359. '',
  360. '',
  361. $file_attachments,
  362. $currentUserId
  363. );
  364. }
  365. }
  366. // Update code
  367. $sql = "UPDATE $table_support_tickets
  368. SET code = '$ticket_code'
  369. WHERE id = '$ticketId'";
  370. Database::query($sql);
  371. // Update total
  372. $sql = "UPDATE $table_support_category
  373. SET total_tickets = total_tickets + 1
  374. WHERE id = $category_id";
  375. Database::query($sql);
  376. $helpDeskMessage =
  377. '<table>
  378. <tr>
  379. <td width="100px"><b>' . get_lang('User') . '</b></td>
  380. <td width="400px">' . $currentUserInfo['complete_name']. '</td>
  381. </tr>
  382. <tr>
  383. <td width="100px"><b>' . get_lang('Username') . '</b></td>
  384. <td width="400px">' . $currentUserInfo['username'] . '</td>
  385. </tr>
  386. <tr>
  387. <td width="100px"><b>' . get_lang('Email') . '</b></td>
  388. <td width="400px">' . $currentUserInfo['email'] . '</td>
  389. </tr>
  390. <tr>
  391. <td width="100px"><b>' . get_lang('Phone') . '</b></td>
  392. <td width="400px">' . $currentUserInfo['phone'] . '</td>
  393. </tr>
  394. <tr>
  395. <td width="100px"><b>' . get_lang('Date') . '</b></td>
  396. <td width="400px">' . api_convert_and_format_date($now, DATE_TIME_FORMAT_LONG) . '</td>
  397. </tr>
  398. <tr>
  399. <td width="100px"><b>' . get_lang('Title') . '</b></td>
  400. <td width="400px">' . $subject . '</td>
  401. </tr>
  402. <tr>
  403. <td width="100px"><b>' . get_lang('Description') . '</b></td>
  404. <td width="400px">' . $content . '</td>
  405. </tr>
  406. </table>';
  407. if ($assignedUserId != 0) {
  408. $href = api_get_path(WEB_CODE_PATH).'/ticket/ticket_details.php?ticket_id='.$ticketId;
  409. $helpDeskMessage .= sprintf(
  410. get_lang('TicketAssignedToXCheckZAtLinkY'),
  411. $assignedUserInfo['complete_name'],
  412. $href,
  413. $ticketId
  414. );
  415. }
  416. if (empty($category_id)) {
  417. if (api_get_setting('ticket_send_warning_to_all_admins') === 'true') {
  418. $warningSubject = sprintf(
  419. get_lang('TicketXCreatedWithNoCategory'),
  420. $ticket_code
  421. );
  422. Display::addFlash(Display::return_message($warningSubject));
  423. $admins = UserManager::get_all_administrators();
  424. foreach ($admins as $userId => $data) {
  425. if ($data['active']) {
  426. TicketManager::send_message_simple(
  427. $userId,
  428. $warningSubject,
  429. $helpDeskMessage
  430. );
  431. }
  432. }
  433. }
  434. } else {
  435. $categoryInfo = TicketManager::getCategory($category_id);
  436. $usersInCategory = TicketManager::getUsersInCategory($category_id);
  437. $message = '<h2>'.get_lang('TicketInformation').'</h2><br />'.$helpDeskMessage;
  438. if (api_get_setting('ticket_warn_admin_no_user_in_category') === 'true') {
  439. $usersInCategory = TicketManager::getUsersInCategory($category_id);
  440. if (empty($usersInCategory)) {
  441. $subject = sprintf(
  442. get_lang('WarningCategoryXDoesntHaveUsers'),
  443. $categoryInfo['name']
  444. );
  445. if (api_get_setting('ticket_send_warning_to_all_admins') === 'true') {
  446. Display::addFlash(Display::return_message(
  447. sprintf(
  448. get_lang('CategoryWithNoUserNotificationSentToAdmins'),
  449. $categoryInfo['name']
  450. ),
  451. null,
  452. false
  453. ));
  454. $admins = UserManager::get_all_administrators();
  455. foreach ($admins as $userId => $data) {
  456. if ($data['active']) {
  457. TicketManager::sendNotification(
  458. $ticketId,
  459. $subject,
  460. $message,
  461. $userId
  462. );
  463. }
  464. }
  465. } else {
  466. Display::addFlash(Display::return_message($subject));
  467. }
  468. }
  469. }
  470. // Send notification to all users
  471. if (!empty($usersInCategory)) {
  472. foreach ($usersInCategory as $data) {
  473. if ($data['user_id']) {
  474. TicketManager::sendNotification(
  475. $ticketId,
  476. $subject,
  477. $message,
  478. $data['user_id']
  479. );
  480. }
  481. }
  482. }
  483. }
  484. if (!empty($personalEmail)) {
  485. api_mail_html(
  486. get_lang('VirtualSupport'),
  487. $personalEmail,
  488. get_lang('IncidentResentToVirtualSupport'),
  489. $helpDeskMessage
  490. );
  491. }
  492. TicketManager::sendNotification(
  493. $ticketId,
  494. $titleCreated,
  495. $helpDeskMessage
  496. );
  497. return true;
  498. } else {
  499. return false;
  500. }
  501. }
  502. /**
  503. * Assign ticket to admin
  504. *
  505. * @param int $ticketId
  506. * @param int $userId
  507. *
  508. * @return bool
  509. */
  510. public static function assignTicketToUser(
  511. $ticketId,
  512. $userId
  513. ) {
  514. $ticketId = intval($ticketId);
  515. $userId = intval($userId);
  516. if (empty($ticketId)) {
  517. return false;
  518. }
  519. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  520. $ticket = self::get_ticket_detail_by_id($ticketId);
  521. if ($ticket) {
  522. $sql = "UPDATE $table_support_tickets
  523. SET assigned_last_user = $userId
  524. WHERE id = $ticketId";
  525. Database::query($sql);
  526. return true;
  527. } else {
  528. return false;
  529. }
  530. }
  531. /**
  532. * Insert message between Users and Admins
  533. * @param int $ticketId
  534. * @param string $subject
  535. * @param string $content
  536. * @param array $file_attachments
  537. * @param int $userId
  538. * @param string $status
  539. * @param bool $sendConfirmation
  540. *
  541. * @return bool
  542. */
  543. public static function insertMessage(
  544. $ticketId,
  545. $subject,
  546. $content,
  547. $file_attachments,
  548. $userId,
  549. $status = 'NOL',
  550. $sendConfirmation = false
  551. ) {
  552. $ticketId = intval($ticketId);
  553. $userId = intval($userId);
  554. $table_support_messages = Database::get_main_table(TABLE_TICKET_MESSAGE);
  555. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  556. if ($sendConfirmation) {
  557. $form = '<form action="ticket_details.php?ticket_id=' . $ticketId . '" id="confirmticket" method="POST" >
  558. <p>' . get_lang('TicketWasThisAnswerSatisfying') . '</p>
  559. <button class="btn btn-primary responseyes" name="response" id="responseyes" value="1">' . get_lang('Yes') . '</button>
  560. <button class="btn btn-danger responseno" name="response" id="responseno" value="0">' . get_lang('No') . '</button>
  561. </form>';
  562. $content .= $form;
  563. }
  564. $now = api_get_utc_datetime();
  565. $params = [
  566. 'ticket_id' => $ticketId,
  567. 'subject' => $subject,
  568. 'message' => $content,
  569. 'ip_address' => $_SERVER['REMOTE_ADDR'],
  570. 'sys_insert_user_id' => $userId,
  571. 'sys_insert_datetime' => $now,
  572. 'sys_lastedit_user_id' => $userId,
  573. 'sys_lastedit_datetime' => $now,
  574. 'status' => $status
  575. ];
  576. $messageId = Database::insert($table_support_messages, $params);
  577. if ($messageId) {
  578. // update_total_message
  579. $sql = "UPDATE $table_support_tickets
  580. SET
  581. sys_lastedit_user_id ='$userId',
  582. sys_lastedit_datetime ='$now',
  583. total_messages = (
  584. SELECT COUNT(*) as total_messages
  585. FROM $table_support_messages
  586. WHERE ticket_id ='$ticketId'
  587. )
  588. WHERE id = $ticketId ";
  589. Database::query($sql);
  590. if (is_array($file_attachments)) {
  591. foreach ($file_attachments as $file_attach) {
  592. if ($file_attach['error'] == 0) {
  593. self::save_message_attachment_file(
  594. $file_attach,
  595. $ticketId,
  596. $messageId
  597. );
  598. } else {
  599. if ($file_attach['error'] != UPLOAD_ERR_NO_FILE) {
  600. return false;
  601. }
  602. }
  603. }
  604. }
  605. }
  606. return true;
  607. }
  608. /**
  609. * Attachment files when a message is sent
  610. * @param $file_attach
  611. * @param $ticketId
  612. * @param $message_id
  613. * @return array
  614. */
  615. public static function save_message_attachment_file(
  616. $file_attach,
  617. $ticketId,
  618. $message_id
  619. ) {
  620. $now = api_get_utc_datetime();
  621. $userId = api_get_user_id();
  622. $ticketId = intval($ticketId);
  623. $new_file_name = add_ext_on_mime(
  624. stripslashes($file_attach['name']),
  625. $file_attach['type']
  626. );
  627. $file_name = $file_attach['name'];
  628. $table_support_message_attachments = Database::get_main_table(TABLE_TICKET_MESSAGE_ATTACHMENTS);
  629. if (!filter_extension($new_file_name)) {
  630. Display :: display_error_message(
  631. get_lang('UplUnableToSaveFileFilteredExtension')
  632. );
  633. } else {
  634. $new_file_name = uniqid('');
  635. $path_attachment = api_get_path(SYS_ARCHIVE_PATH);
  636. $path_message_attach = $path_attachment . 'plugin_ticket_messageattch/';
  637. if (!file_exists($path_message_attach)) {
  638. @mkdir($path_message_attach, api_get_permissions_for_new_directories(), true);
  639. }
  640. $new_path = $path_message_attach . $new_file_name;
  641. if (is_uploaded_file($file_attach['tmp_name'])) {
  642. @copy($file_attach['tmp_name'], $new_path);
  643. }
  644. $safe_file_name = Database::escape_string($file_name);
  645. $safe_new_file_name = Database::escape_string($new_file_name);
  646. $sql = "INSERT INTO $table_support_message_attachments (
  647. filename,
  648. path,
  649. ticket_id,
  650. message_id,
  651. size,
  652. sys_insert_user_id,
  653. sys_insert_datetime,
  654. sys_lastedit_user_id,
  655. sys_lastedit_datetime
  656. ) VALUES (
  657. '$safe_file_name',
  658. '$safe_new_file_name',
  659. '$ticketId',
  660. '$message_id',
  661. '" . $file_attach['size'] . "',
  662. '$userId',
  663. '$now',
  664. '$userId',
  665. '$now'
  666. )";
  667. Database::query($sql);
  668. return array(
  669. 'path' => $path_message_attach . $safe_new_file_name,
  670. 'filename' => $safe_file_name,
  671. );
  672. }
  673. }
  674. /**
  675. * Get tickets by userId
  676. * @param $from
  677. * @param $number_of_items
  678. * @param $column
  679. * @param $direction
  680. * @param int $userId
  681. * @return array
  682. */
  683. public static function get_tickets_by_user_id(
  684. $from,
  685. $number_of_items,
  686. $column,
  687. $direction,
  688. $userId = 0
  689. ) {
  690. $table_support_category = Database::get_main_table(TABLE_TICKET_CATEGORY);
  691. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  692. $table_support_priority = Database::get_main_table(TABLE_TICKET_PRIORITY);
  693. $table_support_status = Database::get_main_table(TABLE_TICKET_STATUS);
  694. $direction = !empty($direction) ? $direction : 'DESC';
  695. $userId = !empty($userId) ? $userId : api_get_user_id();
  696. $isAdmin = UserManager::is_admin($userId);
  697. $sql = "SELECT DISTINCT
  698. ticket.*,
  699. ticket.id ticket_id,
  700. ticket.id AS col0,
  701. ticket.start_date AS col1,
  702. ticket.sys_lastedit_datetime AS col2,
  703. cat.name AS col3,
  704. priority.name AS col5,
  705. priority.name AS col6,
  706. status.name AS col7,
  707. ticket.total_messages AS col8,
  708. ticket.message AS col9,
  709. ticket.subject AS subject,
  710. ticket.assigned_last_user
  711. FROM $table_support_tickets ticket,
  712. $table_support_category cat,
  713. $table_support_priority priority,
  714. $table_support_status status
  715. WHERE
  716. cat.id = ticket.category_id AND
  717. ticket.priority_id = priority.id AND
  718. ticket.status_id = status.id
  719. ";
  720. if (!$isAdmin) {
  721. $sql .= " AND (ticket.assigned_last_user = $userId OR ticket.sys_insert_user_id = $userId )";
  722. }
  723. $keyword_unread = '';
  724. if (isset($_GET['keyword_unread'])) {
  725. $keyword_unread = Database::escape_string(
  726. trim($_GET['keyword_unread'])
  727. );
  728. }
  729. // Search simple
  730. if (isset($_GET['submit_simple'])) {
  731. if ($_GET['keyword'] != '') {
  732. $keyword = Database::escape_string(trim($_GET['keyword']));
  733. $sql .= " AND (
  734. ticket.code = '$keyword' OR
  735. ticket.id = '$keyword'
  736. )";
  737. }
  738. }
  739. // Search advanced
  740. if (isset($_GET['submit_advanced'])) {
  741. $keyword_category = Database::escape_string(
  742. trim($_GET['keyword_category'])
  743. );
  744. $keyword_admin = Database::escape_string(
  745. trim($_GET['keyword_admin'])
  746. );
  747. $keyword_start_date_start = Database::escape_string(
  748. trim($_GET['keyword_start_date_start'])
  749. );
  750. $keyword_start_date_end = Database::escape_string(
  751. trim($_GET['keyword_start_date_end'])
  752. );
  753. $keyword_status = Database::escape_string(
  754. trim($_GET['keyword_status'])
  755. );
  756. $keyword_source = isset($_GET['keyword_source']) ? Database::escape_string(trim($_GET['keyword_source'])) : '';
  757. $keyword_priority = Database::escape_string(
  758. trim($_GET['keyword_priority'])
  759. );
  760. $keyword_range = isset($_GET['keyword_dates']) ? Database::escape_string(trim($_GET['keyword_dates'])) : '';
  761. $keyword_course = Database::escape_string(
  762. trim($_GET['keyword_course'])
  763. );
  764. if ($keyword_category != '') {
  765. $sql .= " AND ticket.category_id = '$keyword_category' ";
  766. }
  767. /*if ($keyword_request_user != '') {
  768. $sql .= " AND (ticket.request_user = '$keyword_request_user'
  769. OR concat(user.firstname,' ',user.lastname) LIKE '%$keyword_request_user%'
  770. OR concat(user.lastname,' ',user.firstname) LIKE '%$keyword_request_user%'
  771. OR user.username LIKE '%$keyword_request_user%') ";
  772. }*/
  773. if ($keyword_admin != '') {
  774. $sql .= " AND ticket.assigned_last_user = '$keyword_admin' ";
  775. }
  776. if ($keyword_status != '') {
  777. $sql .= " AND ticket.status_id = '$keyword_status' ";
  778. }
  779. if ($keyword_range == '' && $keyword_start_date_start != '') {
  780. $sql .= " AND DATE_FORMAT(ticket.start_date,'%d/%m/%Y') = '$keyword_start_date_start' ";
  781. }
  782. if ($keyword_range == '1' && $keyword_start_date_start != '' && $keyword_start_date_end != '') {
  783. $sql .= " AND DATE_FORMAT(ticket.start_date,'%d/%m/%Y') >= '$keyword_start_date_start'
  784. AND DATE_FORMAT(ticket.start_date,'%d/%m/%Y') <= '$keyword_start_date_end'";
  785. }
  786. if ($keyword_priority != '') {
  787. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  788. }
  789. if ($keyword_source != '') {
  790. $sql .= " AND ticket.source = '$keyword_source' ";
  791. }
  792. if ($keyword_course != '') {
  793. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  794. $sql .= " AND ticket.course_id IN (
  795. SELECT id FROM $course_table
  796. WHERE (
  797. title LIKE '%$keyword_course%' OR
  798. code LIKE '%$keyword_course%' OR
  799. visual_code LIKE '%$keyword_course%'
  800. )
  801. )";
  802. }
  803. }
  804. $sql .= " ORDER BY col$column $direction";
  805. $sql .= " LIMIT $from, $number_of_items";
  806. $result = Database::query($sql);
  807. $tickets = array();
  808. $webPath = api_get_path(WEB_PATH);
  809. $webCodePath = api_get_path(WEB_CODE_PATH);
  810. while ($row = Database::fetch_assoc($result)) {
  811. /*$sql_unread = "SELECT
  812. COUNT(DISTINCT message.message_id) AS unread
  813. FROM $table_support_tickets ticket,
  814. $table_support_messages message,
  815. $table_main_user user
  816. WHERE ticket.ticket_id = message.ticket_id
  817. AND ticket.ticket_id = '{$row['col0']}'
  818. AND message.status = 'NOL'
  819. AND message.sys_insert_user_id = user.user_id ";
  820. if ($isAdmin) {
  821. $sql_unread .= " AND user.user_id
  822. NOT IN (SELECT user_id FROM $table_main_admin)
  823. AND ticket.status_id != '".self::STATUS_FORWARDED."' ";
  824. } else {
  825. $sql_unread .= " AND user.user_id
  826. IN (SELECT user_id FROM $table_main_admin) ";
  827. }
  828. $result_unread = Database::query($sql_unread);
  829. $unread = Database::fetch_object($result_unread)->unread;*/
  830. $userInfo = api_get_user_info($row['sys_insert_user_id']);
  831. $hrefUser = $webPath . 'main/admin/user_information.php?user_id=' . $userInfo['user_id'];
  832. $name = "<a href='$hrefUser'> {$userInfo['username']} </a>";
  833. $actions = '';
  834. if ($row['assigned_last_user'] != 0) {
  835. $assignedUserInfo = api_get_user_info($row['assigned_last_user']);
  836. if (!empty($assignedUserInfo)) {
  837. $hrefResp = $webPath . 'main/admin/user_information.php?user_id=' . $assignedUserInfo['user_id'];
  838. $row['assigned_last_user'] = "<a href='$hrefResp'> {$assignedUserInfo['username']} </a>";
  839. } else {
  840. $row['assigned_last_user'] = get_lang('UnknownUser');
  841. }
  842. } else {
  843. if ($row['status_id'] !== self::STATUS_FORWARDED) {
  844. $row['assigned_last_user'] = '<span style="color:#ff0000;">' . get_lang('ToBeAssigned') . '</span>';
  845. } else {
  846. $row['assigned_last_user'] = '<span style="color:#00ff00;">' . get_lang('MessageResent') . '</span>';
  847. }
  848. }
  849. switch ($row['source']) {
  850. case self::SOURCE_PRESENTIAL:
  851. $img_source = 'icons/32/user.png';
  852. break;
  853. case self::SOURCE_EMAIL:
  854. $img_source = 'icons/32/mail.png';
  855. break;
  856. case self::SOURCE_PHONE:
  857. $img_source = 'icons/32/event.png';
  858. break;
  859. default:
  860. $img_source = 'icons/32/course_home.png';
  861. break;
  862. }
  863. $row['col1'] = Display::tip(
  864. date_to_str_ago($row['col1']),
  865. api_get_local_time($row['col1'])
  866. );
  867. $row['col2'] = Display::tip(
  868. date_to_str_ago($row['col2']),
  869. api_get_local_time($row['col2'])
  870. );
  871. if ($isAdmin) {
  872. if ($row['priority_id'] === self::PRIORITY_HIGH && $row['status_id'] != self::STATUS_CLOSE) {
  873. $actions .= '<img src="' . $webCodePath . 'img/exclamation.png" border="0" />';
  874. }
  875. $row['col0'] = Display::return_icon(
  876. $img_source,
  877. get_lang('Info')
  878. ).
  879. '<a href="ticket_details.php?ticket_id=' . $row['col0'] . '">' . $row['code'] . '</a>';
  880. // @todo fix
  881. /*if ($row['col7'] == 'PENDIENTE') {
  882. $row['col7'] = '<span style="color: #f00; font-weight:bold;">' . $row['col7'] . '</span>';
  883. }*/
  884. $ticket = array(
  885. $row['col0'].' '.$row['subject'],
  886. $row['col7'],
  887. $row['col1'],
  888. $row['col2'],
  889. $row['col3'],
  890. $name,
  891. $row['assigned_last_user'],
  892. $row['col8']
  893. );
  894. } else {
  895. $actions = '';
  896. $row['col0'] = Display::return_icon(
  897. $img_source,
  898. get_lang('Info')
  899. ) . '<a href="ticket_details.php?ticket_id=' . $row['col0'] . '">' . $row['code'] . '</a>';
  900. $now = api_strtotime(api_get_utc_datetime());
  901. $last_edit_date = api_strtotime($row['sys_lastedit_datetime']);
  902. $dif = $now - $last_edit_date;
  903. if ($dif > 172800 && $row['priority_id'] === self::PRIORITY_NORMAL && $row['status_id'] != self::STATUS_CLOSE) {
  904. $actions .= '<a href="'.api_get_path(WEB_CODE_PATH).'ticket/tickets.php?ticket_id=' . $row['ticket_id'] . '&amp;action=alert">
  905. <img src="' . Display::returnIconPath('exclamation.png') . '" border="0" /></a>';
  906. }
  907. if ($row['priority_id'] === self::PRIORITY_HIGH) {
  908. $actions .= '<img src="' . Display::returnIconPath('admin_star.png') . '" border="0" />';
  909. }
  910. $ticket = array(
  911. $row['col0'],
  912. $row['col7'],
  913. $row['col1'],
  914. $row['col2'],
  915. $row['col3']
  916. );
  917. }
  918. /*if ($unread > 0) {
  919. $ticket['0'] = $ticket['0'] . '&nbsp;&nbsp;(' . $unread . ')<a href="ticket_details.php?ticket_id=' . $row['ticket_id'] . '">
  920. <img src="' . Display::returnIconPath('message_new.png') . '" border="0" title="' . $unread . ' ' . get_lang('Messages') . '"/>
  921. </a>';
  922. }*/
  923. if ($isAdmin) {
  924. $ticket['0'] .= '&nbsp;&nbsp;<a href="javascript:void(0)" onclick="load_history_ticket(\'div_' . $row['ticket_id'] . '\',' . $row['ticket_id'] . ')">
  925. <img onclick="load_course_list(\'div_' . $row['ticket_id'] . '\',' . $row['ticket_id'] . ')" onmouseover="clear_course_list (\'div_' . $row['ticket_id'] . '\')" src="' . Display::returnIconPath('history.gif') . '" title="' . get_lang('Historial') . '" alt="' . get_lang('Historial') . '"/>
  926. <div class="blackboard_hide" id="div_' . $row['ticket_id'] . '">&nbsp;&nbsp;</div>
  927. </a>&nbsp;&nbsp;';
  928. }
  929. $tickets[] = $ticket;
  930. }
  931. return $tickets;
  932. }
  933. /**
  934. * @param int $userId
  935. * @return mixed
  936. */
  937. public static function get_total_tickets_by_user_id($userId = 0)
  938. {
  939. $table_support_category = Database::get_main_table(
  940. TABLE_TICKET_CATEGORY
  941. );
  942. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  943. $table_support_priority = Database::get_main_table(
  944. TABLE_TICKET_PRIORITY
  945. );
  946. $table_support_status = Database::get_main_table(TABLE_TICKET_STATUS);
  947. $table_support_messages = Database::get_main_table(
  948. TABLE_TICKET_MESSAGE
  949. );
  950. $table_main_user = Database::get_main_table(TABLE_MAIN_USER);
  951. $table_main_admin = Database::get_main_table(TABLE_MAIN_ADMIN);
  952. $sql = "SELECT COUNT(ticket.id) AS total
  953. FROM $table_support_tickets ticket ,
  954. $table_support_category cat ,
  955. $table_support_priority priority,
  956. $table_support_status status
  957. WHERE
  958. cat.id = ticket.category_id AND
  959. ticket.priority_id = priority.id AND
  960. ticket.status_id = status.id";
  961. // Search simple
  962. if (isset($_GET['submit_simple'])) {
  963. if ($_GET['keyword'] != '') {
  964. $keyword = Database::escape_string(trim($_GET['keyword']));
  965. $sql .= " AND (ticket.code = '$keyword'
  966. OR user.firstname LIKE '%$keyword%'
  967. OR user.lastname LIKE '%$keyword%'
  968. OR concat(user.firstname,' ',user.lastname) LIKE '%$keyword%'
  969. OR concat(user.lastname,' ',user.firstname) LIKE '%$keyword%'
  970. OR user.username LIKE '%$keyword%') ";
  971. }
  972. }
  973. $keyword_unread = '';
  974. if (isset($_GET['keyword_unread'])) {
  975. $keyword_unread = Database::escape_string(
  976. trim($_GET['keyword_unread'])
  977. );
  978. }
  979. // Search advanced
  980. if (isset($_GET['submit_advanced'])) {
  981. $keyword_category = Database::escape_string(
  982. trim($_GET['keyword_category'])
  983. );
  984. $keyword_request_user = '';
  985. /*$keyword_request_user = Database::escape_string(
  986. trim($_GET['keyword_request_user'])
  987. );*/
  988. $keyword_admin = Database::escape_string(
  989. trim($_GET['keyword_admin'])
  990. );
  991. $keyword_start_date_start = Database::escape_string(
  992. trim($_GET['keyword_start_date_start'])
  993. );
  994. $keyword_start_date_end = Database::escape_string(
  995. trim($_GET['keyword_start_date_end'])
  996. );
  997. $keyword_status = Database::escape_string(
  998. trim($_GET['keyword_status'])
  999. );
  1000. $keyword_source = isset($_GET['keyword_source']) ? Database::escape_string(trim($_GET['keyword_source'])) : '';
  1001. $keyword_priority = Database::escape_string(
  1002. trim($_GET['keyword_priority'])
  1003. );
  1004. $keyword_range = isset($_GET['keyword_dates']) ? Database::escape_string(trim($_GET['keyword_dates'])) : '';
  1005. $keyword_course = Database::escape_string(
  1006. trim($_GET['keyword_course'])
  1007. );
  1008. if ($keyword_category != '') {
  1009. $sql .= " AND ticket.category_id = '$keyword_category' ";
  1010. }
  1011. if ($keyword_request_user != '') {
  1012. $sql .= " AND (ticket.request_user = '$keyword_request_user'
  1013. OR user.firstname LIKE '%$keyword_request_user%'
  1014. OR user.official_code LIKE '%$keyword_request_user%'
  1015. OR user.lastname LIKE '%$keyword_request_user%'
  1016. OR concat(user.firstname,' ',user.lastname) LIKE '%$keyword_request_user%'
  1017. OR concat(user.lastname,' ',user.firstname) LIKE '%$keyword_request_user%'
  1018. OR user.username LIKE '%$keyword_request_user%') ";
  1019. }
  1020. if ($keyword_admin != '') {
  1021. $sql .= " AND ticket.assigned_last_user = '$keyword_admin' ";
  1022. }
  1023. if ($keyword_status != '') {
  1024. $sql .= " AND ticket.status_id = '$keyword_status' ";
  1025. }
  1026. if ($keyword_range == '' && $keyword_start_date_start != '') {
  1027. $sql .= " AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') = '$keyword_start_date_start' ";
  1028. }
  1029. if ($keyword_range == '1' && $keyword_start_date_start != '' && $keyword_start_date_end != '') {
  1030. $sql .= " AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') >= '$keyword_start_date_start'
  1031. AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') <= '$keyword_start_date_end'";
  1032. }
  1033. if ($keyword_priority != '') {
  1034. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  1035. }
  1036. if ($keyword_source != '') {
  1037. $sql .= " AND ticket.source = '$keyword_source' ";
  1038. }
  1039. if ($keyword_priority != '') {
  1040. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  1041. }
  1042. if ($keyword_course != '') {
  1043. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  1044. $sql .= " AND ticket.course_id IN ( ";
  1045. $sql .= "SELECT id
  1046. FROM $course_table
  1047. WHERE (title LIKE '%$keyword_course%'
  1048. OR code LIKE '%$keyword_course%'
  1049. OR visual_code LIKE '%$keyword_course%' )) ";
  1050. }
  1051. }
  1052. if ($keyword_unread == 'yes') {
  1053. $sql .= " AND ticket.id IN ( ";
  1054. $sql .= "SELECT ticket.id
  1055. FROM $table_support_tickets ticket,
  1056. $table_support_messages message,
  1057. $table_main_user user
  1058. WHERE ticket.id = message.ticket_id
  1059. AND message.status = 'NOL'
  1060. AND message.sys_insert_user_id = user.user_id
  1061. AND user.user_id NOT IN (
  1062. SELECT user_id FROM $table_main_admin
  1063. ) AND ticket.status_id != '".self::STATUS_FORWARDED."'
  1064. GROUP BY ticket.id)";
  1065. } else {
  1066. if ($keyword_unread == 'no') {
  1067. $sql .= " AND ticket.id NOT IN ( ";
  1068. $sql .= " SELECT ticket.id
  1069. FROM $table_support_tickets ticket,
  1070. $table_support_messages message,
  1071. $table_main_user user
  1072. WHERE ticket.id = message.ticket_id
  1073. AND message.status = 'NOL'
  1074. AND message.sys_insert_user_id = user.user_id
  1075. AND user.user_id NOT IN (SELECT user_id FROM $table_main_admin)
  1076. AND ticket.status_id != '".self::STATUS_FORWARDED."'
  1077. GROUP BY ticket.id)";
  1078. }
  1079. }
  1080. $res = Database::query($sql);
  1081. $obj = Database::fetch_object($res);
  1082. return $obj->total;
  1083. }
  1084. /**
  1085. * @param int $ticketId
  1086. * @return array
  1087. */
  1088. public static function get_ticket_detail_by_id($ticketId)
  1089. {
  1090. $ticketId = intval($ticketId);
  1091. $table_support_category = Database::get_main_table(TABLE_TICKET_CATEGORY);
  1092. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1093. $table_support_priority = Database::get_main_table(TABLE_TICKET_PRIORITY);
  1094. $table_support_status = Database::get_main_table(TABLE_TICKET_STATUS);
  1095. $table_support_messages = Database::get_main_table(TABLE_TICKET_MESSAGE);
  1096. $table_support_message_attachments = Database::get_main_table(TABLE_TICKET_MESSAGE_ATTACHMENTS);
  1097. $table_main_user = Database::get_main_table(TABLE_MAIN_USER);
  1098. $sql = "SELECT
  1099. ticket.*,
  1100. cat.name,
  1101. status.name as status,
  1102. priority.name priority
  1103. FROM $table_support_tickets ticket
  1104. INNER JOIN $table_support_category cat
  1105. ON (cat.id = ticket.category_id)
  1106. INNER JOIN $table_support_priority priority
  1107. ON (priority.id = ticket.priority_id)
  1108. INNER JOIN $table_support_status status
  1109. ON (status.id = ticket.status_id)
  1110. WHERE
  1111. ticket.id = $ticketId ";
  1112. $result = Database::query($sql);
  1113. $ticket = array();
  1114. if (Database::num_rows($result) > 0) {
  1115. while ($row = Database::fetch_assoc($result)) {
  1116. $row['course'] = null;
  1117. $row['start_date_from_db'] = $row['start_date'];
  1118. $row['start_date'] = api_convert_and_format_date(
  1119. api_get_local_time($row['start_date']), DATE_TIME_FORMAT_LONG, api_get_timezone()
  1120. );
  1121. $row['end_date_from_db'] = $row['end_date'];
  1122. $row['end_date'] = api_convert_and_format_date(
  1123. api_get_local_time($row['end_date']), DATE_TIME_FORMAT_LONG, api_get_timezone()
  1124. );
  1125. $row['sys_lastedit_datetime_from_db'] = $row['sys_lastedit_datetime'];
  1126. $row['sys_lastedit_datetime'] = api_convert_and_format_date(
  1127. api_get_local_time($row['sys_lastedit_datetime']), DATE_TIME_FORMAT_LONG, api_get_timezone()
  1128. );
  1129. $row['course_url'] = null;
  1130. if ($row['course_id'] != 0) {
  1131. $course = api_get_course_info_by_id($row['course_id']);
  1132. $sessionId = 0;
  1133. if ($row['session_id']) {
  1134. $sessionId = $row['session_id'];
  1135. }
  1136. if ($course) {
  1137. $row['course_url'] = '<a href="'.$course['course_public_url'].'?id_session='.$sessionId.'">'.$course['name'].'</a>';
  1138. }
  1139. }
  1140. $userInfo = api_get_user_info($row['sys_insert_user_id']);
  1141. $row['user_url'] = '<a href="' . api_get_path(WEB_PATH) . 'main/admin/user_information.php?user_id=' . $userInfo['user_id'] . '">
  1142. ' . $userInfo['complete_name']. '</a>';
  1143. $ticket['usuario'] = $userInfo;
  1144. $ticket['ticket'] = $row;
  1145. }
  1146. $sql = "SELECT *, message.id as message_id
  1147. FROM $table_support_messages message
  1148. INNER JOIN $table_main_user user
  1149. ON (message.sys_insert_user_id = user.user_id)
  1150. WHERE
  1151. message.ticket_id = '$ticketId' ";
  1152. $result = Database::query($sql);
  1153. $ticket['messages'] = array();
  1154. $attach_icon = Display::return_icon('attachment.gif', '');
  1155. $admin_table = Database::get_main_table(TABLE_MAIN_ADMIN);
  1156. $webPath = api_get_path(WEB_CODE_PATH);
  1157. while ($row = Database::fetch_assoc($result)) {
  1158. $message = $row;
  1159. $completeName = api_get_person_name($row['firstname'], $row['lastname']);
  1160. $href = $webPath . 'main/admin/user_information.php?user_id=' . $row['user_id'];
  1161. // Check if user is an admin
  1162. $sql_admin = "SELECT user_id FROM $admin_table
  1163. WHERE user_id = '" . intval($message['user_id']) . "'
  1164. LIMIT 1";
  1165. $result_admin = Database::query($sql_admin);
  1166. $message['admin'] = false;
  1167. if (Database::num_rows($result_admin) > 0) {
  1168. $message['admin'] = true;
  1169. }
  1170. $message['user_created'] = "<a href='$href'> $completeName </a>";
  1171. $sql = "SELECT *
  1172. FROM $table_support_message_attachments
  1173. WHERE
  1174. message_id = " . $row['message_id'] . " AND
  1175. ticket_id = $ticketId";
  1176. $result_attach = Database::query($sql);
  1177. while ($row2 = Database::fetch_assoc($result_attach)) {
  1178. $archiveURL = $archiveURL = $webPath . 'ticket/download.php?ticket_id=' . $ticketId . '&file=';
  1179. $row2['attachment_link'] = $attach_icon . '&nbsp;<a href="' . $archiveURL . $row2['path'] . '&title=' . $row2['filename'] . '">' . $row2['filename'] . '</a>&nbsp;(' . $row2['size'] . ')';
  1180. $message['attachments'][] = $row2;
  1181. }
  1182. $ticket['messages'][] = $message;
  1183. }
  1184. }
  1185. return $ticket;
  1186. }
  1187. /**
  1188. * @param int $ticketId
  1189. * @param int $userId
  1190. * @return bool
  1191. */
  1192. public static function update_message_status($ticketId, $userId)
  1193. {
  1194. $ticketId = intval($ticketId);
  1195. $userId = intval($userId);
  1196. $table_support_messages = Database::get_main_table(
  1197. TABLE_TICKET_MESSAGE
  1198. );
  1199. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1200. $now = api_get_utc_datetime();
  1201. $sql = "UPDATE $table_support_messages
  1202. SET
  1203. status = 'LEI',
  1204. sys_lastedit_user_id ='" . api_get_user_id() . "',
  1205. sys_lastedit_datetime ='" . $now . "'
  1206. WHERE ticket_id ='$ticketId' ";
  1207. if (api_is_platform_admin()) {
  1208. $sql .= " AND sys_insert_user_id = '$userId'";
  1209. } else {
  1210. $sql .= " AND sys_insert_user_id != '$userId'";
  1211. }
  1212. $result = Database::query($sql);
  1213. if (Database::affected_rows($result) > 0) {
  1214. Database::query(
  1215. "UPDATE $table_support_tickets SET
  1216. status_id = '".self::STATUS_PENDING."'
  1217. WHERE id ='$ticketId' AND status_id = '".self::STATUS_NEW."'"
  1218. );
  1219. return true;
  1220. } else {
  1221. return false;
  1222. }
  1223. }
  1224. /**
  1225. * @param int $ticketId
  1226. * @param int $userId
  1227. * @param string $title
  1228. * @param string $message
  1229. * @param int $onlyToUserId
  1230. *
  1231. * @return bool
  1232. */
  1233. public static function sendNotification($ticketId, $title, $message, $onlyToUserId = 0)
  1234. {
  1235. $ticketInfo = self::get_ticket_detail_by_id($ticketId);
  1236. if (empty($ticketInfo)) {
  1237. return false;
  1238. }
  1239. $assignedUserInfo = api_get_user_info($ticketInfo['ticket']['assigned_last_user']);
  1240. $requestUserInfo = $ticketInfo['usuario'];
  1241. $ticketCode = $ticketInfo['ticket']['code'];
  1242. $status = $ticketInfo['ticket']['status'];
  1243. $priority = $ticketInfo['ticket']['priority'];
  1244. // Subject
  1245. $titleEmail = "[$ticketCode] $title";
  1246. // Content
  1247. $href = api_get_path(WEB_CODE_PATH).'/ticket/ticket_details.php?ticket_id='.$ticketId;
  1248. $ticketUrl = Display::url($ticketCode, $href);
  1249. $messageEmail = get_lang('TicketNum').": $ticketUrl <br />";
  1250. $messageEmail .= get_lang('Status').": $status <br />";
  1251. $messageEmail .= get_lang('Priority').": $priority <br />";
  1252. $messageEmail .= '<hr /><br />';
  1253. $messageEmail .= $message;
  1254. $currentUserId = api_get_user_id();
  1255. if (!empty($onlyToUserId)) {
  1256. // Send only to specific user
  1257. if ($currentUserId != $onlyToUserId) {
  1258. MessageManager::send_message_simple(
  1259. $onlyToUserId,
  1260. $titleEmail,
  1261. $messageEmail
  1262. );
  1263. }
  1264. } else {
  1265. // Send to assigned user and to author
  1266. if ($requestUserInfo && $currentUserId != $requestUserInfo['id']) {
  1267. MessageManager::send_message_simple(
  1268. $requestUserInfo['id'],
  1269. $titleEmail,
  1270. $messageEmail
  1271. );
  1272. }
  1273. if ($assignedUserInfo &&
  1274. $requestUserInfo['id'] != $assignedUserInfo['id'] &&
  1275. $currentUserId != $assignedUserInfo['id']
  1276. ) {
  1277. MessageManager::send_message_simple(
  1278. $assignedUserInfo['id'],
  1279. $titleEmail,
  1280. $messageEmail
  1281. );
  1282. }
  1283. }
  1284. }
  1285. /**
  1286. * @param array $params
  1287. * @param int $ticketId
  1288. * @param int $userId
  1289. *
  1290. * @return bool
  1291. */
  1292. public static function updateTicket(
  1293. $params,
  1294. $ticketId,
  1295. $userId
  1296. ) {
  1297. $now = api_get_utc_datetime();
  1298. $table = Database::get_main_table(TABLE_TICKET_TICKET);
  1299. $newParams = [
  1300. 'priority_id' => isset($params['priority_id']) ? $params['priority_id'] : '',
  1301. 'status_id' => isset($params['status_id']) ? $params['status_id'] : '',
  1302. 'sys_lastedit_user_id' => $userId,
  1303. 'sys_lastedit_datetime' => $now,
  1304. ];
  1305. Database::update($table, $newParams, ['id = ? ' => $ticketId]);
  1306. return true;
  1307. }
  1308. /**
  1309. * @param $status_id
  1310. * @param $ticketId
  1311. * @param $userId
  1312. * @return bool
  1313. */
  1314. public static function update_ticket_status(
  1315. $status_id,
  1316. $ticketId,
  1317. $userId
  1318. ) {
  1319. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1320. $ticketId = intval($ticketId);
  1321. $status_id = intval($status_id);
  1322. $userId = intval($userId);
  1323. $now = api_get_utc_datetime();
  1324. $sql = "UPDATE $table_support_tickets
  1325. SET
  1326. status_id = '$status_id',
  1327. sys_lastedit_user_id ='$userId',
  1328. sys_lastedit_datetime ='" . $now . "'
  1329. WHERE id ='$ticketId'";
  1330. $result = Database::query($sql);
  1331. if (Database::affected_rows($result) > 0) {
  1332. self::sendNotification(
  1333. $ticketId,
  1334. get_lang('TicketUpdated'),
  1335. get_lang('TicketUpdated')
  1336. );
  1337. return true;
  1338. } else {
  1339. return false;
  1340. }
  1341. }
  1342. /**
  1343. * @return mixed
  1344. */
  1345. public static function get_number_of_messages()
  1346. {
  1347. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1348. $table_support_messages = Database::get_main_table(
  1349. TABLE_TICKET_MESSAGE
  1350. );
  1351. $table_main_user = Database::get_main_table(TABLE_MAIN_USER);
  1352. $table_main_admin = Database::get_main_table(TABLE_MAIN_ADMIN);
  1353. $user_info = api_get_user_info();
  1354. $userId = $user_info['user_id'];
  1355. $sql = "SELECT COUNT(DISTINCT ticket.id) AS unread
  1356. FROM $table_support_tickets ticket,
  1357. $table_support_messages message ,
  1358. $table_main_user user
  1359. WHERE
  1360. ticket.id = message.ticket_id AND
  1361. message.status = 'NOL' AND
  1362. user.user_id = message.sys_insert_user_id ";
  1363. if (!api_is_platform_admin()) {
  1364. $sql .= " AND ticket.request_user = '$userId'
  1365. AND user_id IN (SELECT user_id FROM $table_main_admin) ";
  1366. } else {
  1367. $sql .= " AND user_id NOT IN (SELECT user_id FROM $table_main_admin)
  1368. AND ticket.status_id != '".self::STATUS_FORWARDED."'";
  1369. }
  1370. $sql .= " AND ticket.project_id != '' ";
  1371. $res = Database::query($sql);
  1372. $obj = Database::fetch_object($res);
  1373. return $obj->unread;
  1374. }
  1375. /**
  1376. * @param int $ticketId
  1377. * @param int $userId
  1378. */
  1379. public static function send_alert($ticketId, $userId)
  1380. {
  1381. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1382. $now = api_get_utc_datetime();
  1383. $ticketId = intval($ticketId);
  1384. $userId = intval($userId);
  1385. $sql = "UPDATE $table_support_tickets SET
  1386. priority_id = '".self::PRIORITY_HIGH."',
  1387. sys_lastedit_user_id ='$userId',
  1388. sys_lastedit_datetime ='$now'
  1389. WHERE id = '$ticketId'";
  1390. Database::query($sql);
  1391. }
  1392. /**
  1393. * @param int $ticketId
  1394. * @param int $userId
  1395. */
  1396. public static function close_ticket($ticketId, $userId)
  1397. {
  1398. $ticketId = intval($ticketId);
  1399. $userId = intval($userId);
  1400. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1401. $now = api_get_utc_datetime();
  1402. $sql = "UPDATE $table_support_tickets SET
  1403. status_id = '".self::STATUS_CLOSE."',
  1404. sys_lastedit_user_id ='$userId',
  1405. sys_lastedit_datetime ='" . $now . "',
  1406. end_date ='$now'
  1407. WHERE id ='$ticketId'";
  1408. Database::query($sql);
  1409. self::sendNotification(
  1410. $ticketId,
  1411. get_lang('TicketClosed'),
  1412. get_lang('TicketClosed')
  1413. );
  1414. }
  1415. /**
  1416. *
  1417. */
  1418. public static function close_old_tickets()
  1419. {
  1420. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1421. $now = api_get_utc_datetime();
  1422. $userId = api_get_user_id();
  1423. $sql = "UPDATE $table_support_tickets
  1424. SET
  1425. status_id = '".self::STATUS_CLOSE."',
  1426. sys_lastedit_user_id ='$userId',
  1427. sys_lastedit_datetime ='$now',
  1428. end_date = '$now'
  1429. WHERE
  1430. DATEDIFF('$now', sys_lastedit_datetime) > 7 AND
  1431. status_id != '".self::STATUS_CLOSE."' AND
  1432. status_id != '".self::STATUS_NEW."' AND
  1433. status_id != '".self::STATUS_FORWARDED."'";
  1434. Database::query($sql);
  1435. }
  1436. /**
  1437. * @param int $ticketId
  1438. * @return array
  1439. */
  1440. public static function get_assign_log($ticketId)
  1441. {
  1442. $table_support_assigned_log = Database::get_main_table(TABLE_TICKET_ASSIGNED_LOG);
  1443. $ticketId = intval($ticketId);
  1444. $sql = "SELECT * FROM $table_support_assigned_log
  1445. WHERE ticket_id = '$ticketId'
  1446. ORDER BY assigned_date DESC";
  1447. $result = Database::query($sql);
  1448. $history = [];
  1449. $webpath = api_get_path(WEB_PATH);
  1450. while ($row = Database::fetch_assoc($result)) {
  1451. if ($row['user_id'] != 0) {
  1452. $assignuser = api_get_user_info($row['user_id']);
  1453. $row['assignuser'] = '<a href="' . $webpath . 'main/admin/user_information.php?user_id=' . $row['user_id'] . '" target="_blank">' .
  1454. $assignuser['username'] . '</a>';
  1455. } else {
  1456. $row['assignuser'] = get_lang('Unassign');
  1457. }
  1458. $row['assigned_date'] = date_to_str_ago($row['assigned_date']);
  1459. $insertuser = api_get_user_info($row['sys_insert_user_id']);
  1460. $row['insertuser'] = '<a href="' . $webpath . 'main/admin/user_information.php?user_id=' . $row['sys_insert_user_id'] . '" target="_blank">' .
  1461. $insertuser['username'] . '</a>';
  1462. $history[] = $row;
  1463. }
  1464. return $history;
  1465. }
  1466. /**
  1467. * @param $from
  1468. * @param $number_of_items
  1469. * @param $column
  1470. * @param $direction
  1471. * @param null $userId
  1472. * @return array
  1473. */
  1474. public static function export_tickets_by_user_id(
  1475. $from,
  1476. $number_of_items,
  1477. $column,
  1478. $direction,
  1479. $userId = null
  1480. ) {
  1481. $from = intval($from);
  1482. $number_of_items = intval($number_of_items);
  1483. $table_support_category = Database::get_main_table(
  1484. TABLE_TICKET_CATEGORY
  1485. );
  1486. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1487. $table_support_priority = Database::get_main_table(
  1488. TABLE_TICKET_PRIORITY
  1489. );
  1490. $table_support_status = Database::get_main_table(TABLE_TICKET_STATUS);
  1491. $table_support_messages = Database::get_main_table(
  1492. TABLE_TICKET_MESSAGE
  1493. );
  1494. $table_main_user = Database::get_main_table(TABLE_MAIN_USER);
  1495. if (is_null($direction)) {
  1496. $direction = "DESC";
  1497. }
  1498. if (is_null($userId) || $userId == 0) {
  1499. $userId = api_get_user_id();
  1500. }
  1501. $sql = "SELECT
  1502. ticket.code,
  1503. ticket.sys_insert_datetime,
  1504. ticket.sys_lastedit_datetime,
  1505. cat.name as category,
  1506. CONCAT(user.lastname,' ', user.firstname) AS fullname,
  1507. status.name as status,
  1508. ticket.total_messages as messages,
  1509. ticket.assigned_last_user as responsable
  1510. FROM $table_support_tickets ticket,
  1511. $table_support_category cat ,
  1512. $table_support_priority priority,
  1513. $table_support_status status ,
  1514. $table_main_user user
  1515. WHERE
  1516. cat.id = ticket.category_id
  1517. AND ticket.priority_id = priority.id
  1518. AND ticket.status_id = status.id
  1519. AND user.user_id = ticket.request_user ";
  1520. // Search simple
  1521. if (isset($_GET['submit_simple'])) {
  1522. if ($_GET['keyword'] !== '') {
  1523. $keyword = Database::escape_string(trim($_GET['keyword']));
  1524. $sql .= " AND (ticket.code = '$keyword'
  1525. OR user.firstname LIKE '%$keyword%'
  1526. OR user.lastname LIKE '%$keyword%'
  1527. OR concat(user.firstname,' ',user.lastname) LIKE '%$keyword%'
  1528. OR concat(user.lastname,' ',user.firstname) LIKE '%$keyword%'
  1529. OR user.username LIKE '%$keyword%') ";
  1530. }
  1531. }
  1532. //Search advanced
  1533. if (isset($_GET['submit_advanced'])) {
  1534. $keyword_category = Database::escape_string(
  1535. trim($_GET['keyword_category'])
  1536. );
  1537. $keyword_request_user = Database::escape_string(
  1538. trim($_GET['keyword_request_user'])
  1539. );
  1540. $keyword_admin = Database::escape_string(
  1541. trim($_GET['keyword_admin'])
  1542. );
  1543. $keyword_start_date_start = Database::escape_string(
  1544. trim($_GET['keyword_start_date_start'])
  1545. );
  1546. $keyword_start_date_end = Database::escape_string(
  1547. trim($_GET['keyword_start_date_end'])
  1548. );
  1549. $keyword_status = Database::escape_string(
  1550. trim($_GET['keyword_status'])
  1551. );
  1552. $keyword_source = Database::escape_string(
  1553. trim($_GET['keyword_source'])
  1554. );
  1555. $keyword_priority = Database::escape_string(
  1556. trim($_GET['keyword_priority'])
  1557. );
  1558. $keyword_range = Database::escape_string(
  1559. trim($_GET['keyword_dates'])
  1560. );
  1561. $keyword_unread = Database::escape_string(
  1562. trim($_GET['keyword_unread'])
  1563. );
  1564. $keyword_course = Database::escape_string(
  1565. trim($_GET['keyword_course'])
  1566. );
  1567. if ($keyword_category != '') {
  1568. $sql .= " AND ticket.category_id = '$keyword_category' ";
  1569. }
  1570. if ($keyword_request_user != '') {
  1571. $sql .= " AND (ticket.request_user = '$keyword_request_user'
  1572. OR user.firstname LIKE '%$keyword_request_user%'
  1573. OR user.official_code LIKE '%$keyword_request_user%'
  1574. OR user.lastname LIKE '%$keyword_request_user%'
  1575. OR concat(user.firstname,' ',user.lastname) LIKE '%$keyword_request_user%'
  1576. OR concat(user.lastname,' ',user.firstname) LIKE '%$keyword_request_user%'
  1577. OR user.username LIKE '%$keyword_request_user%') ";
  1578. }
  1579. if ($keyword_admin != '') {
  1580. $sql .= " AND ticket.assigned_last_user = '$keyword_admin' ";
  1581. }
  1582. if ($keyword_status != '') {
  1583. $sql .= " AND ticket.status_id = '$keyword_status' ";
  1584. }
  1585. if ($keyword_range == '' && $keyword_start_date_start != '') {
  1586. $sql .= " AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') = '$keyword_start_date_start' ";
  1587. }
  1588. if ($keyword_range == '1' && $keyword_start_date_start != '' && $keyword_start_date_end != '') {
  1589. $sql .= " AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') >= '$keyword_start_date_start'
  1590. AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') <= '$keyword_start_date_end'";
  1591. }
  1592. if ($keyword_priority != '') {
  1593. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  1594. }
  1595. if ($keyword_source != '') {
  1596. $sql .= " AND ticket.source = '$keyword_source' ";
  1597. }
  1598. if ($keyword_priority != '') {
  1599. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  1600. }
  1601. if ($keyword_course != '') {
  1602. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  1603. $sql .= " AND ticket.course_id IN ( ";
  1604. $sql .= "SELECT id
  1605. FROM $course_table
  1606. WHERE (title LIKE '%$keyword_course%'
  1607. OR code LIKE '%$keyword_course%'
  1608. OR visual_code LIKE '%$keyword_course%' )) ";
  1609. }
  1610. if ($keyword_unread == 'yes') {
  1611. $sql .= " AND ticket.id IN (
  1612. SELECT ticket.id
  1613. FROM $table_support_tickets ticket,
  1614. $table_support_messages message,
  1615. $table_main_user user
  1616. WHERE ticket.id = message.ticket_id
  1617. AND message.status = 'NOL'
  1618. AND message.sys_insert_user_id = user.user_id
  1619. AND user.status != 1 AND ticket.status_id != '".self::STATUS_FORWARDED."'
  1620. GROUP BY ticket.id)";
  1621. } else {
  1622. if ($keyword_unread == 'no') {
  1623. $sql .= " AND ticket.id NOT IN (
  1624. SELECT ticket.id
  1625. FROM $table_support_tickets ticket,
  1626. $table_support_messages message,
  1627. $table_main_user user
  1628. WHERE ticket.id = message.ticket_id
  1629. AND message.status = 'NOL'
  1630. AND message.sys_insert_user_id = user.user_id
  1631. AND user.status != 1
  1632. AND ticket.status_id != '".self::STATUS_FORWARDED."'
  1633. GROUP BY ticket.id)";
  1634. }
  1635. }
  1636. }
  1637. $sql .= " LIMIT $from,$number_of_items";
  1638. $result = Database::query($sql);
  1639. $tickets[0] = array(
  1640. utf8_decode('Ticket#'),
  1641. utf8_decode('Fecha'),
  1642. utf8_decode('Fecha Edicion'),
  1643. utf8_decode('Categoria'),
  1644. utf8_decode('Usuario'),
  1645. utf8_decode('Estado'),
  1646. utf8_decode('Mensajes'),
  1647. utf8_decode('Responsable'),
  1648. utf8_decode('Programa'),
  1649. );
  1650. while ($row = Database::fetch_assoc($result)) {
  1651. if ($row['responsable'] != 0) {
  1652. $row['responsable'] = api_get_user_info($row['responsable']);
  1653. $row['responsable'] = $row['responsable']['firstname'] . ' ' . $row['responsable']['lastname'];
  1654. }
  1655. $row['sys_insert_datetime'] = api_format_date(
  1656. $row['sys_insert_datetime'], '%d/%m/%y - %I:%M:%S %p'
  1657. );
  1658. $row['sys_lastedit_datetime'] = api_format_date(
  1659. $row['sys_lastedit_datetime'], '%d/%m/%y - %I:%M:%S %p'
  1660. );
  1661. $row['category'] = utf8_decode($row['category']);
  1662. $row['programa'] = utf8_decode($row['fullname']);
  1663. $row['fullname'] = utf8_decode($row['fullname']);
  1664. $row['responsable'] = utf8_decode($row['responsable']);
  1665. $tickets[] = $row;
  1666. }
  1667. return $tickets;
  1668. }
  1669. /**
  1670. * @param string $url
  1671. * @return FormValidator
  1672. */
  1673. public static function getCategoryForm($url, $projectId)
  1674. {
  1675. $form = new FormValidator('category', 'post', $url);
  1676. $form->addText('name', get_lang('Name'));
  1677. $form->addHtmlEditor('description', get_lang('Description'));
  1678. $form->addHidden('project_id', $projectId);
  1679. $form->addButtonUpdate(get_lang('Save'));
  1680. return $form;
  1681. }
  1682. /**
  1683. * @return array
  1684. */
  1685. public static function getStatusList()
  1686. {
  1687. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->findAll();
  1688. $list = [];
  1689. /** @var \Chamilo\TicketBundle\Entity\Status $row */
  1690. foreach ($items as $row) {
  1691. $list[$row->getId()] = $row->getName();
  1692. }
  1693. return $list;
  1694. }
  1695. /**
  1696. * @return array
  1697. */
  1698. public static function getTicketsFromCriteria($criteria)
  1699. {
  1700. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Ticket')->findBy($criteria);
  1701. $list = [];
  1702. /** @var Ticket $row */
  1703. foreach ($items as $row) {
  1704. $list[$row->getId()] = $row->getCode();
  1705. }
  1706. return $list;
  1707. }
  1708. /**
  1709. * @param string $code
  1710. * @return int
  1711. */
  1712. public static function getStatusIdFromCode($code)
  1713. {
  1714. $item = Database::getManager()
  1715. ->getRepository('ChamiloTicketBundle:Status')
  1716. ->findOneBy(['code' => $code])
  1717. ;
  1718. if ($item) {
  1719. return $item->getId();
  1720. }
  1721. return 0;
  1722. }
  1723. /**
  1724. * @return array
  1725. */
  1726. public static function getPriorityList()
  1727. {
  1728. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->findAll();
  1729. $list = [];
  1730. /** @var \Chamilo\TicketBundle\Entity\Priority $row */
  1731. foreach ($projects as $row) {
  1732. $list[$row->getId()] = $row->getName();
  1733. }
  1734. return $list;
  1735. }
  1736. /**
  1737. * @return array
  1738. */
  1739. public static function getProjects()
  1740. {
  1741. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Project')->findAll();
  1742. $list = [];
  1743. /** @var Project $row */
  1744. foreach ($projects as $row) {
  1745. $list[] = [
  1746. 'id' => $row->getId(),
  1747. '0' => $row->getId(),
  1748. '1' => $row->getName(),
  1749. '2' => $row->getDescription(),
  1750. '3' => $row->getId()
  1751. ];
  1752. }
  1753. return $list;
  1754. }
  1755. /**
  1756. * @return array
  1757. */
  1758. public static function getProjectsSimple()
  1759. {
  1760. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Project')->findAll();
  1761. $list = [];
  1762. /** @var Project $row */
  1763. foreach ($projects as $row) {
  1764. $list[] = [
  1765. 'id' => $row->getId(),
  1766. '0' => $row->getId(),
  1767. '1' => Display::url(
  1768. $row->getName(),
  1769. api_get_path(WEB_CODE_PATH).'ticket/tickets.php?project_id='.$row->getId()
  1770. ),
  1771. '2' => $row->getDescription()
  1772. ];
  1773. }
  1774. return $list;
  1775. }
  1776. /**
  1777. * @return int
  1778. */
  1779. public static function getProjectsCount()
  1780. {
  1781. $count = Database::getManager()->getRepository('ChamiloTicketBundle:Project')->createQueryBuilder('p')
  1782. ->select('COUNT(p.id)')
  1783. ->getQuery()
  1784. ->getSingleScalarResult();
  1785. return $count;
  1786. }
  1787. /**
  1788. * @param array $params
  1789. */
  1790. public static function addProject($params)
  1791. {
  1792. $project = new Project();
  1793. $project->setName($params['name']);
  1794. $project->setDescription($params['description']);
  1795. $project->setInsertUserId(api_get_user_id());
  1796. //$project->setEmail($params['email']);
  1797. Database::getManager()->persist($project);
  1798. Database::getManager()->flush();
  1799. }
  1800. /**
  1801. * @param $id
  1802. * @return Project
  1803. */
  1804. public static function getProject($id)
  1805. {
  1806. return Database::getManager()->getRepository('ChamiloTicketBundle:Project')->find($id);
  1807. }
  1808. /**
  1809. * @param int $id
  1810. * @param array $params
  1811. */
  1812. public static function updateProject($id, $params)
  1813. {
  1814. $project = self::getProject($id);
  1815. $project->setName($params['name']);
  1816. $project->setDescription($params['description']);
  1817. $project->setLastEditDateTime(new DateTime($params['sys_lastedit_datetime']));
  1818. $project->setLastEditUserId($params['sys_lastedit_user_id']);
  1819. Database::getManager()->merge($project);
  1820. Database::getManager()->flush();
  1821. }
  1822. /**
  1823. * @param int $id
  1824. */
  1825. public static function deleteProject($id)
  1826. {
  1827. $project = self::getProject($id);
  1828. Database::getManager()->remove($project);
  1829. Database::getManager()->flush();
  1830. }
  1831. /**
  1832. * @param string $url
  1833. * @return FormValidator
  1834. */
  1835. public static function getProjectForm($url)
  1836. {
  1837. $form = new FormValidator('project', 'post', $url);
  1838. $form->addText('name', get_lang('Name'));
  1839. $form->addHtmlEditor('description', get_lang('Description'));
  1840. $form->addButtonUpdate(get_lang('Save'));
  1841. return $form;
  1842. }
  1843. /**
  1844. * @return array
  1845. */
  1846. public static function getStatusAdminList()
  1847. {
  1848. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->findAll();
  1849. $list = [];
  1850. /** @var Status $row */
  1851. foreach ($items as $row) {
  1852. $list[] = [
  1853. 'id' => $row->getId(),
  1854. 'code' => $row->getCode(),
  1855. '0' => $row->getId(),
  1856. '1' => $row->getName(),
  1857. '2' => $row->getDescription(),
  1858. '3' => $row->getId()
  1859. ];
  1860. }
  1861. return $list;
  1862. }
  1863. /**
  1864. * @return array
  1865. */
  1866. public static function getStatusSimple()
  1867. {
  1868. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->findAll();
  1869. $list = [];
  1870. /** @var Project $row */
  1871. foreach ($projects as $row) {
  1872. $list[] = [
  1873. 'id' => $row->getId(),
  1874. '0' => $row->getId(),
  1875. '1' => Display::url($row->getName()),
  1876. '2' => $row->getDescription()
  1877. ];
  1878. }
  1879. return $list;
  1880. }
  1881. /**
  1882. * @return int
  1883. */
  1884. public static function getStatusCount()
  1885. {
  1886. $count = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->createQueryBuilder('p')
  1887. ->select('COUNT(p.id)')
  1888. ->getQuery()
  1889. ->getSingleScalarResult();
  1890. return $count;
  1891. }
  1892. /**
  1893. * @param array $params
  1894. */
  1895. public static function addStatus($params)
  1896. {
  1897. $item = new Status();
  1898. $item->setCode(URLify::filter($params['name']));
  1899. $item->setName($params['name']);
  1900. $item->setDescription($params['description']);
  1901. Database::getManager()->persist($item);
  1902. Database::getManager()->flush();
  1903. }
  1904. /**
  1905. * @param $id
  1906. * @return Project
  1907. */
  1908. public static function getStatus($id)
  1909. {
  1910. return Database::getManager()->getRepository('ChamiloTicketBundle:Status')->find($id);
  1911. }
  1912. /**
  1913. * @param int $id
  1914. * @param array $params
  1915. */
  1916. public static function updateStatus($id, $params)
  1917. {
  1918. $item = self::getStatus($id);
  1919. $item->setName($params['name']);
  1920. $item->setDescription($params['description']);
  1921. Database::getManager()->merge($item);
  1922. Database::getManager()->flush();
  1923. }
  1924. /**
  1925. * @param int $id
  1926. */
  1927. public static function deleteStatus($id)
  1928. {
  1929. $item = self::getStatus($id);
  1930. Database::getManager()->remove($item);
  1931. Database::getManager()->flush();
  1932. }
  1933. /**
  1934. * @param string $url
  1935. * @return FormValidator
  1936. */
  1937. public static function getStatusForm($url)
  1938. {
  1939. $form = new FormValidator('status', 'post', $url);
  1940. $form->addText('name', get_lang('Name'));
  1941. $form->addHtmlEditor('description', get_lang('Description'));
  1942. $form->addButtonUpdate(get_lang('Save'));
  1943. return $form;
  1944. }
  1945. /**
  1946. * @return array
  1947. */
  1948. public static function getPriorityAdminList()
  1949. {
  1950. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->findAll();
  1951. $list = [];
  1952. /** @var Status $row */
  1953. foreach ($items as $row) {
  1954. $list[] = [
  1955. 'id' => $row->getId(),
  1956. 'code' => $row->getCode(),
  1957. '0' => $row->getId(),
  1958. '1' => $row->getName(),
  1959. '2' => $row->getDescription(),
  1960. '3' => $row->getId()
  1961. ];
  1962. }
  1963. return $list;
  1964. }
  1965. /**
  1966. * @return array
  1967. */
  1968. public static function getPrioritySimple()
  1969. {
  1970. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->findAll();
  1971. $list = [];
  1972. /** @var Priority $row */
  1973. foreach ($projects as $row) {
  1974. $list[] = [
  1975. 'id' => $row->getId(),
  1976. '0' => $row->getId(),
  1977. '1' => Display::url($row->getName()),
  1978. '2' => $row->getDescription()
  1979. ];
  1980. }
  1981. return $list;
  1982. }
  1983. /**
  1984. * @return int
  1985. */
  1986. public static function getPriorityCount()
  1987. {
  1988. $count = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->createQueryBuilder('p')
  1989. ->select('COUNT(p.id)')
  1990. ->getQuery()
  1991. ->getSingleScalarResult();
  1992. return $count;
  1993. }
  1994. /**
  1995. * @param array $params
  1996. */
  1997. public static function addPriority($params)
  1998. {
  1999. $item = new Priority();
  2000. $item
  2001. ->setCode(URLify::filter($params['name']))
  2002. ->setName($params['name'])
  2003. ->setDescription($params['description'])
  2004. ->setColor('')
  2005. ->setInsertUserId(api_get_user_id())
  2006. ->setUrgency('')
  2007. ;
  2008. Database::getManager()->persist($item);
  2009. Database::getManager()->flush();
  2010. }
  2011. /**
  2012. * @param $id
  2013. * @return Priority
  2014. */
  2015. public static function getPriority($id)
  2016. {
  2017. return Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->find($id);
  2018. }
  2019. /**
  2020. * @param int $id
  2021. * @param array $params
  2022. */
  2023. public static function updatePriority($id, $params)
  2024. {
  2025. $item = self::getPriority($id);
  2026. $item->setName($params['name']);
  2027. $item->setDescription($params['description']);
  2028. Database::getManager()->merge($item);
  2029. Database::getManager()->flush();
  2030. }
  2031. /**
  2032. * @param int $id
  2033. */
  2034. public static function deletePriority($id)
  2035. {
  2036. $item = self::getStatus($id);
  2037. Database::getManager()->remove($item);
  2038. Database::getManager()->flush();
  2039. }
  2040. /**
  2041. * @param string $url
  2042. * @return FormValidator
  2043. */
  2044. public static function getPriorityForm($url)
  2045. {
  2046. $form = new FormValidator('priority', 'post', $url);
  2047. $form->addText('name', get_lang('Name'));
  2048. $form->addHtmlEditor('description', get_lang('Description'));
  2049. $form->addButtonUpdate(get_lang('Save'));
  2050. return $form;
  2051. }
  2052. /**
  2053. * @return string
  2054. */
  2055. public static function getSettingsMenu()
  2056. {
  2057. $items = [
  2058. [
  2059. 'url' => 'projects.php',
  2060. 'content' => get_lang('Projects')
  2061. ],
  2062. [
  2063. 'url' => 'status.php',
  2064. 'content' => get_lang('Status')
  2065. ],
  2066. [
  2067. 'url' => 'priorities.php',
  2068. 'content' => get_lang('Priority')
  2069. ]
  2070. ];
  2071. echo Display::actions($items);
  2072. }
  2073. /**
  2074. * @return array
  2075. */
  2076. public static function getDefaultStatusList() {
  2077. return [
  2078. self::STATUS_NEW,
  2079. self::STATUS_PENDING,
  2080. self::STATUS_UNCONFIRMED,
  2081. self::STATUS_CLOSE,
  2082. self::STATUS_FORWARDED
  2083. ];
  2084. }
  2085. /**
  2086. * @return array
  2087. */
  2088. public static function getDefaultPriorityList() {
  2089. return [
  2090. self::PRIORITY_NORMAL,
  2091. self::PRIORITY_HIGH,
  2092. self::PRIORITY_LOW,
  2093. self::STATUS_CLOSE,
  2094. self::STATUS_FORWARDED
  2095. ];
  2096. }
  2097. }