TicketManager.php 79 KB

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