TicketManager.php 82 KB

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