TicketManager.php 80 KB

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