TicketManager.php 80 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367
  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 = array();
  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, array('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 = array();
  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 = array();
  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 = array();
  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/course_home.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. );
  858. $icon .= '<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. SELECT id
  972. FROM $course_table
  973. WHERE (
  974. title LIKE '%$keyword_course%' OR
  975. code LIKE '%$keyword_course%' OR
  976. visual_code LIKE '%$keyword_course%'
  977. )
  978. ) ";
  979. }
  980. $res = Database::query($sql);
  981. $obj = Database::fetch_object($res);
  982. return (int)$obj->total;
  983. }
  984. /**
  985. * @param int $id
  986. * @return MessageAttachment
  987. */
  988. public static function getTicketMessageAttachment($id)
  989. {
  990. $id = (int) $id;
  991. $em = Database::getManager();
  992. $item = $em->getRepository('ChamiloTicketBundle:MessageAttachment')->find($id);
  993. if ($item) {
  994. return $item;
  995. }
  996. return false;
  997. }
  998. /**
  999. * @param int $ticketId
  1000. * @return array
  1001. */
  1002. public static function get_ticket_detail_by_id($ticketId)
  1003. {
  1004. $ticketId = intval($ticketId);
  1005. $table_support_category = Database::get_main_table(TABLE_TICKET_CATEGORY);
  1006. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1007. $table_support_priority = Database::get_main_table(TABLE_TICKET_PRIORITY);
  1008. $table_support_status = Database::get_main_table(TABLE_TICKET_STATUS);
  1009. $table_support_messages = Database::get_main_table(TABLE_TICKET_MESSAGE);
  1010. $table_support_message_attachments = Database::get_main_table(TABLE_TICKET_MESSAGE_ATTACHMENTS);
  1011. $table_main_user = Database::get_main_table(TABLE_MAIN_USER);
  1012. $sql = "SELECT
  1013. ticket.*,
  1014. cat.name,
  1015. status.name as status,
  1016. priority.name priority
  1017. FROM $table_support_tickets ticket
  1018. INNER JOIN $table_support_category cat
  1019. ON (cat.id = ticket.category_id)
  1020. INNER JOIN $table_support_priority priority
  1021. ON (priority.id = ticket.priority_id)
  1022. INNER JOIN $table_support_status status
  1023. ON (status.id = ticket.status_id)
  1024. WHERE
  1025. ticket.id = $ticketId ";
  1026. $result = Database::query($sql);
  1027. $ticket = array();
  1028. if (Database::num_rows($result) > 0) {
  1029. while ($row = Database::fetch_assoc($result)) {
  1030. $row['course'] = null;
  1031. $row['start_date_from_db'] = $row['start_date'];
  1032. $row['start_date'] = api_convert_and_format_date(
  1033. api_get_local_time($row['start_date']),
  1034. DATE_TIME_FORMAT_LONG,
  1035. api_get_timezone()
  1036. );
  1037. $row['end_date_from_db'] = $row['end_date'];
  1038. $row['end_date'] = api_convert_and_format_date(
  1039. api_get_local_time($row['end_date']),
  1040. DATE_TIME_FORMAT_LONG,
  1041. api_get_timezone()
  1042. );
  1043. $row['sys_lastedit_datetime_from_db'] = $row['sys_lastedit_datetime'];
  1044. $row['sys_lastedit_datetime'] = api_convert_and_format_date(
  1045. api_get_local_time($row['sys_lastedit_datetime']),
  1046. DATE_TIME_FORMAT_LONG,
  1047. api_get_timezone()
  1048. );
  1049. $row['course_url'] = null;
  1050. if ($row['course_id'] != 0) {
  1051. $course = api_get_course_info_by_id($row['course_id']);
  1052. $sessionId = 0;
  1053. if ($row['session_id']) {
  1054. $sessionId = $row['session_id'];
  1055. }
  1056. if ($course) {
  1057. $row['course_url'] = '<a href="'.$course['course_public_url'].'?id_session='.$sessionId.'">'.$course['name'].'</a>';
  1058. }
  1059. }
  1060. $userInfo = api_get_user_info($row['sys_insert_user_id']);
  1061. $row['user_url'] = '<a href="'.api_get_path(WEB_PATH).'main/admin/user_information.php?user_id='.$userInfo['user_id'].'">
  1062. ' . $userInfo['complete_name'].'</a>';
  1063. $ticket['usuario'] = $userInfo;
  1064. $ticket['ticket'] = $row;
  1065. }
  1066. $sql = "SELECT *, message.id as message_id
  1067. FROM $table_support_messages message
  1068. INNER JOIN $table_main_user user
  1069. ON (message.sys_insert_user_id = user.user_id)
  1070. WHERE
  1071. message.ticket_id = '$ticketId' ";
  1072. $result = Database::query($sql);
  1073. $ticket['messages'] = array();
  1074. $attach_icon = Display::return_icon('attachment.gif', '');
  1075. $webPath = api_get_path(WEB_CODE_PATH);
  1076. while ($row = Database::fetch_assoc($result)) {
  1077. $message = $row;
  1078. $message['admin'] = UserManager::is_admin($message['user_id']);
  1079. $message['user_info'] = api_get_user_info($message['user_id']);
  1080. $sql = "SELECT *
  1081. FROM $table_support_message_attachments
  1082. WHERE
  1083. message_id = ".$row['message_id']." AND
  1084. ticket_id = $ticketId";
  1085. $result_attach = Database::query($sql);
  1086. while ($row2 = Database::fetch_assoc($result_attach)) {
  1087. $archiveURL = $webPath.'ticket/download.php?ticket_id='.$ticketId.'&id='.$row2['id'];
  1088. $row2['attachment_link'] = $attach_icon.'&nbsp;<a href="'.$archiveURL.'">'.$row2['filename'].'</a>&nbsp;('.$row2['size'].')';
  1089. $message['attachments'][] = $row2;
  1090. }
  1091. $ticket['messages'][] = $message;
  1092. }
  1093. }
  1094. return $ticket;
  1095. }
  1096. /**
  1097. * @param int $ticketId
  1098. * @param int $userId
  1099. * @return bool
  1100. */
  1101. public static function update_message_status($ticketId, $userId)
  1102. {
  1103. $ticketId = intval($ticketId);
  1104. $userId = intval($userId);
  1105. $table_support_messages = Database::get_main_table(
  1106. TABLE_TICKET_MESSAGE
  1107. );
  1108. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1109. $now = api_get_utc_datetime();
  1110. $sql = "UPDATE $table_support_messages
  1111. SET
  1112. status = 'LEI',
  1113. sys_lastedit_user_id ='".api_get_user_id()."',
  1114. sys_lastedit_datetime ='" . $now."'
  1115. WHERE ticket_id ='$ticketId' ";
  1116. if (api_is_platform_admin()) {
  1117. $sql .= " AND sys_insert_user_id = '$userId'";
  1118. } else {
  1119. $sql .= " AND sys_insert_user_id != '$userId'";
  1120. }
  1121. $result = Database::query($sql);
  1122. if (Database::affected_rows($result) > 0) {
  1123. Database::query(
  1124. "UPDATE $table_support_tickets SET
  1125. status_id = '".self::STATUS_PENDING."'
  1126. WHERE id ='$ticketId' AND status_id = '".self::STATUS_NEW."'"
  1127. );
  1128. return true;
  1129. } else {
  1130. return false;
  1131. }
  1132. }
  1133. /**
  1134. * Send notification to a user through the internal messaging system
  1135. * @param int $ticketId
  1136. * @param string $title
  1137. * @param string $message
  1138. * @param int $onlyToUserId
  1139. *
  1140. * @return bool
  1141. */
  1142. public static function sendNotification($ticketId, $title, $message, $onlyToUserId = 0)
  1143. {
  1144. $ticketInfo = self::get_ticket_detail_by_id($ticketId);
  1145. if (empty($ticketInfo)) {
  1146. return false;
  1147. }
  1148. $assignedUserInfo = api_get_user_info($ticketInfo['ticket']['assigned_last_user']);
  1149. $requestUserInfo = $ticketInfo['usuario'];
  1150. $ticketCode = $ticketInfo['ticket']['code'];
  1151. $status = $ticketInfo['ticket']['status'];
  1152. $priority = $ticketInfo['ticket']['priority'];
  1153. // Subject
  1154. $titleEmail = "[$ticketCode] $title";
  1155. // Content
  1156. $href = api_get_path(WEB_CODE_PATH).'/ticket/ticket_details.php?ticket_id='.$ticketId;
  1157. $ticketUrl = Display::url($ticketCode, $href);
  1158. $messageEmail = get_lang('TicketNum').": $ticketUrl <br />";
  1159. $messageEmail .= get_lang('Status').": $status <br />";
  1160. $messageEmail .= get_lang('Priority').": $priority <br />";
  1161. $messageEmail .= '<hr /><br />';
  1162. $messageEmail .= $message;
  1163. $currentUserId = api_get_user_id();
  1164. if (!empty($onlyToUserId)) {
  1165. // Send only to specific user
  1166. if ($currentUserId != $onlyToUserId) {
  1167. MessageManager::send_message_simple(
  1168. $onlyToUserId,
  1169. $titleEmail,
  1170. $messageEmail
  1171. );
  1172. }
  1173. } else {
  1174. // Send to assigned user and to author
  1175. if ($requestUserInfo && $currentUserId != $requestUserInfo['id']) {
  1176. MessageManager::send_message_simple(
  1177. $requestUserInfo['id'],
  1178. $titleEmail,
  1179. $messageEmail
  1180. );
  1181. }
  1182. if ($assignedUserInfo &&
  1183. $requestUserInfo['id'] != $assignedUserInfo['id'] &&
  1184. $currentUserId != $assignedUserInfo['id']
  1185. ) {
  1186. MessageManager::send_message_simple(
  1187. $assignedUserInfo['id'],
  1188. $titleEmail,
  1189. $messageEmail
  1190. );
  1191. }
  1192. }
  1193. }
  1194. /**
  1195. * @param array $params
  1196. * @param int $ticketId
  1197. * @param int $userId
  1198. *
  1199. * @return bool
  1200. */
  1201. public static function updateTicket(
  1202. $params,
  1203. $ticketId,
  1204. $userId
  1205. ) {
  1206. $now = api_get_utc_datetime();
  1207. $table = Database::get_main_table(TABLE_TICKET_TICKET);
  1208. $newParams = [
  1209. 'priority_id' => isset($params['priority_id']) ? $params['priority_id'] : '',
  1210. 'status_id' => isset($params['status_id']) ? $params['status_id'] : '',
  1211. 'sys_lastedit_user_id' => $userId,
  1212. 'sys_lastedit_datetime' => $now,
  1213. ];
  1214. Database::update($table, $newParams, ['id = ? ' => $ticketId]);
  1215. return true;
  1216. }
  1217. /**
  1218. * @param int $status_id
  1219. * @param int $ticketId
  1220. * @param int $userId
  1221. * @return bool
  1222. */
  1223. public static function update_ticket_status(
  1224. $status_id,
  1225. $ticketId,
  1226. $userId
  1227. ) {
  1228. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1229. $ticketId = intval($ticketId);
  1230. $status_id = intval($status_id);
  1231. $userId = intval($userId);
  1232. $now = api_get_utc_datetime();
  1233. $sql = "UPDATE $table_support_tickets
  1234. SET
  1235. status_id = '$status_id',
  1236. sys_lastedit_user_id ='$userId',
  1237. sys_lastedit_datetime ='".$now."'
  1238. WHERE id ='$ticketId'";
  1239. $result = Database::query($sql);
  1240. if (Database::affected_rows($result) > 0) {
  1241. self::sendNotification(
  1242. $ticketId,
  1243. get_lang('TicketUpdated'),
  1244. get_lang('TicketUpdated')
  1245. );
  1246. return true;
  1247. } else {
  1248. return false;
  1249. }
  1250. }
  1251. /**
  1252. * @return mixed
  1253. */
  1254. public static function getNumberOfMessages()
  1255. {
  1256. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1257. $table_support_messages = Database::get_main_table(
  1258. TABLE_TICKET_MESSAGE
  1259. );
  1260. $table_main_user = Database::get_main_table(TABLE_MAIN_USER);
  1261. $table_main_admin = Database::get_main_table(TABLE_MAIN_ADMIN);
  1262. $user_info = api_get_user_info();
  1263. $userId = $user_info['user_id'];
  1264. $sql = "SELECT COUNT(DISTINCT ticket.id) AS unread
  1265. FROM $table_support_tickets ticket,
  1266. $table_support_messages message ,
  1267. $table_main_user user
  1268. WHERE
  1269. ticket.id = message.ticket_id AND
  1270. message.status = 'NOL' AND
  1271. user.user_id = message.sys_insert_user_id ";
  1272. if (!api_is_platform_admin()) {
  1273. $sql .= " AND ticket.request_user = '$userId'
  1274. AND user_id IN (SELECT user_id FROM $table_main_admin) ";
  1275. } else {
  1276. $sql .= " AND user_id NOT IN (SELECT user_id FROM $table_main_admin)
  1277. AND ticket.status_id != '".self::STATUS_FORWARDED."'";
  1278. }
  1279. $sql .= " AND ticket.project_id != '' ";
  1280. $res = Database::query($sql);
  1281. $obj = Database::fetch_object($res);
  1282. return $obj->unread;
  1283. }
  1284. /**
  1285. * @param int $ticketId
  1286. * @param int $userId
  1287. */
  1288. public static function send_alert($ticketId, $userId)
  1289. {
  1290. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1291. $now = api_get_utc_datetime();
  1292. $ticketId = intval($ticketId);
  1293. $userId = intval($userId);
  1294. $sql = "UPDATE $table_support_tickets SET
  1295. priority_id = '".self::PRIORITY_HIGH."',
  1296. sys_lastedit_user_id ='$userId',
  1297. sys_lastedit_datetime ='$now'
  1298. WHERE id = '$ticketId'";
  1299. Database::query($sql);
  1300. }
  1301. /**
  1302. * @param int $ticketId
  1303. * @param int $userId
  1304. */
  1305. public static function close_ticket($ticketId, $userId)
  1306. {
  1307. $ticketId = intval($ticketId);
  1308. $userId = intval($userId);
  1309. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1310. $now = api_get_utc_datetime();
  1311. $sql = "UPDATE $table_support_tickets SET
  1312. status_id = '".self::STATUS_CLOSE."',
  1313. sys_lastedit_user_id ='$userId',
  1314. sys_lastedit_datetime ='".$now."',
  1315. end_date ='$now'
  1316. WHERE id ='$ticketId'";
  1317. Database::query($sql);
  1318. self::sendNotification(
  1319. $ticketId,
  1320. get_lang('TicketClosed'),
  1321. get_lang('TicketClosed')
  1322. );
  1323. }
  1324. /**
  1325. * @throws \Doctrine\DBAL\DBALException
  1326. */
  1327. public static function close_old_tickets()
  1328. {
  1329. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1330. $now = api_get_utc_datetime();
  1331. $userId = api_get_user_id();
  1332. $sql = "UPDATE $table_support_tickets
  1333. SET
  1334. status_id = '".self::STATUS_CLOSE."',
  1335. sys_lastedit_user_id ='$userId',
  1336. sys_lastedit_datetime ='$now',
  1337. end_date = '$now'
  1338. WHERE
  1339. DATEDIFF('$now', sys_lastedit_datetime) > 7 AND
  1340. status_id != '".self::STATUS_CLOSE."' AND
  1341. status_id != '".self::STATUS_NEW."' AND
  1342. status_id != '".self::STATUS_FORWARDED."'";
  1343. Database::query($sql);
  1344. }
  1345. /**
  1346. * @param int $ticketId
  1347. * @return array
  1348. */
  1349. public static function get_assign_log($ticketId)
  1350. {
  1351. $table = Database::get_main_table(TABLE_TICKET_ASSIGNED_LOG);
  1352. $ticketId = intval($ticketId);
  1353. $sql = "SELECT * FROM $table
  1354. WHERE ticket_id = $ticketId
  1355. ORDER BY assigned_date DESC";
  1356. $result = Database::query($sql);
  1357. $history = [];
  1358. $webpath = api_get_path(WEB_PATH);
  1359. while ($row = Database::fetch_assoc($result)) {
  1360. if ($row['user_id'] != 0) {
  1361. $assignuser = api_get_user_info($row['user_id']);
  1362. $row['assignuser'] = '<a href="'.$webpath.'main/admin/user_information.php?user_id='.$row['user_id'].'" target="_blank">'.
  1363. $assignuser['username'].'</a>';
  1364. } else {
  1365. $row['assignuser'] = get_lang('Unassign');
  1366. }
  1367. $row['assigned_date'] = Display::dateToStringAgoAndLongDate($row['assigned_date']);
  1368. $insertuser = api_get_user_info($row['sys_insert_user_id']);
  1369. $row['insertuser'] = '<a href="'.$webpath.'main/admin/user_information.php?user_id='.$row['sys_insert_user_id'].'" target="_blank">'.
  1370. $insertuser['username'].'</a>';
  1371. $history[] = $row;
  1372. }
  1373. return $history;
  1374. }
  1375. /**
  1376. * @param $from
  1377. * @param $number_of_items
  1378. * @param $column
  1379. * @param $direction
  1380. * @param null $userId
  1381. * @return array
  1382. */
  1383. public static function export_tickets_by_user_id(
  1384. $from,
  1385. $number_of_items,
  1386. $column,
  1387. $direction,
  1388. $userId = null
  1389. ) {
  1390. $from = intval($from);
  1391. $number_of_items = intval($number_of_items);
  1392. $table_support_category = Database::get_main_table(
  1393. TABLE_TICKET_CATEGORY
  1394. );
  1395. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1396. $table_support_priority = Database::get_main_table(
  1397. TABLE_TICKET_PRIORITY
  1398. );
  1399. $table_support_status = Database::get_main_table(TABLE_TICKET_STATUS);
  1400. $table_support_messages = Database::get_main_table(
  1401. TABLE_TICKET_MESSAGE
  1402. );
  1403. $table_main_user = Database::get_main_table(TABLE_MAIN_USER);
  1404. if (is_null($direction)) {
  1405. $direction = "DESC";
  1406. }
  1407. if (is_null($userId) || $userId == 0) {
  1408. $userId = api_get_user_id();
  1409. }
  1410. $sql = "SELECT
  1411. ticket.code,
  1412. ticket.sys_insert_datetime,
  1413. ticket.sys_lastedit_datetime,
  1414. cat.name as category,
  1415. CONCAT(user.lastname,' ', user.firstname) AS fullname,
  1416. status.name as status,
  1417. ticket.total_messages as messages,
  1418. ticket.assigned_last_user as responsable
  1419. FROM $table_support_tickets ticket,
  1420. $table_support_category cat ,
  1421. $table_support_priority priority,
  1422. $table_support_status status ,
  1423. $table_main_user user
  1424. WHERE
  1425. cat.id = ticket.category_id
  1426. AND ticket.priority_id = priority.id
  1427. AND ticket.status_id = status.id
  1428. AND user.user_id = ticket.request_user ";
  1429. // Search simple
  1430. if (isset($_GET['submit_simple'])) {
  1431. if ($_GET['keyword'] !== '') {
  1432. $keyword = Database::escape_string(trim($_GET['keyword']));
  1433. $sql .= " AND (ticket.code = '$keyword'
  1434. OR user.firstname LIKE '%$keyword%'
  1435. OR user.lastname LIKE '%$keyword%'
  1436. OR concat(user.firstname,' ',user.lastname) LIKE '%$keyword%'
  1437. OR concat(user.lastname,' ',user.firstname) LIKE '%$keyword%'
  1438. OR user.username LIKE '%$keyword%') ";
  1439. }
  1440. }
  1441. //Search advanced
  1442. if (isset($_GET['submit_advanced'])) {
  1443. $keyword_category = Database::escape_string(
  1444. trim($_GET['keyword_category'])
  1445. );
  1446. $keyword_request_user = Database::escape_string(
  1447. trim($_GET['keyword_request_user'])
  1448. );
  1449. $keywordAssignedTo = (int) $_GET['keyword_assigned_to'];
  1450. $keyword_start_date_start = Database::escape_string(
  1451. trim($_GET['keyword_start_date_start'])
  1452. );
  1453. $keyword_start_date_end = Database::escape_string(
  1454. trim($_GET['keyword_start_date_end'])
  1455. );
  1456. $keyword_status = Database::escape_string(
  1457. trim($_GET['keyword_status'])
  1458. );
  1459. $keyword_source = Database::escape_string(
  1460. trim($_GET['keyword_source'])
  1461. );
  1462. $keyword_priority = Database::escape_string(
  1463. trim($_GET['keyword_priority'])
  1464. );
  1465. $keyword_range = Database::escape_string(
  1466. trim($_GET['keyword_dates'])
  1467. );
  1468. $keyword_unread = Database::escape_string(
  1469. trim($_GET['keyword_unread'])
  1470. );
  1471. $keyword_course = Database::escape_string(
  1472. trim($_GET['keyword_course'])
  1473. );
  1474. if ($keyword_category != '') {
  1475. $sql .= " AND ticket.category_id = '$keyword_category' ";
  1476. }
  1477. if ($keyword_request_user != '') {
  1478. $sql .= " AND (ticket.request_user = '$keyword_request_user'
  1479. OR user.firstname LIKE '%$keyword_request_user%'
  1480. OR user.official_code LIKE '%$keyword_request_user%'
  1481. OR user.lastname LIKE '%$keyword_request_user%'
  1482. OR concat(user.firstname,' ',user.lastname) LIKE '%$keyword_request_user%'
  1483. OR concat(user.lastname,' ',user.firstname) LIKE '%$keyword_request_user%'
  1484. OR user.username LIKE '%$keyword_request_user%') ";
  1485. }
  1486. if (!empty($keywordAssignedTo)) {
  1487. $sql .= " AND ticket.assigned_last_user = $keywordAssignedTo ";
  1488. }
  1489. if ($keyword_status != '') {
  1490. $sql .= " AND ticket.status_id = '$keyword_status' ";
  1491. }
  1492. if ($keyword_range == '' && $keyword_start_date_start != '') {
  1493. $sql .= " AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') = '$keyword_start_date_start' ";
  1494. }
  1495. if ($keyword_range == '1' && $keyword_start_date_start != '' && $keyword_start_date_end != '') {
  1496. $sql .= " AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') >= '$keyword_start_date_start'
  1497. AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') <= '$keyword_start_date_end'";
  1498. }
  1499. if ($keyword_priority != '') {
  1500. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  1501. }
  1502. if ($keyword_source != '') {
  1503. $sql .= " AND ticket.source = '$keyword_source' ";
  1504. }
  1505. if ($keyword_priority != '') {
  1506. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  1507. }
  1508. if ($keyword_course != '') {
  1509. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  1510. $sql .= " AND ticket.course_id IN ( ";
  1511. $sql .= "SELECT id
  1512. FROM $course_table
  1513. WHERE (title LIKE '%$keyword_course%'
  1514. OR code LIKE '%$keyword_course%'
  1515. OR visual_code LIKE '%$keyword_course%' )) ";
  1516. }
  1517. if ($keyword_unread == 'yes') {
  1518. $sql .= " AND ticket.id IN (
  1519. SELECT ticket.id
  1520. FROM $table_support_tickets ticket,
  1521. $table_support_messages message,
  1522. $table_main_user user
  1523. WHERE ticket.id = message.ticket_id
  1524. AND message.status = 'NOL'
  1525. AND message.sys_insert_user_id = user.user_id
  1526. AND user.status != 1 AND ticket.status_id != '".self::STATUS_FORWARDED."'
  1527. GROUP BY ticket.id)";
  1528. } else {
  1529. if ($keyword_unread == 'no') {
  1530. $sql .= " AND ticket.id NOT IN (
  1531. SELECT ticket.id
  1532. FROM $table_support_tickets ticket,
  1533. $table_support_messages message,
  1534. $table_main_user user
  1535. WHERE ticket.id = message.ticket_id
  1536. AND message.status = 'NOL'
  1537. AND message.sys_insert_user_id = user.user_id
  1538. AND user.status != 1
  1539. AND ticket.status_id != '".self::STATUS_FORWARDED."'
  1540. GROUP BY ticket.id)";
  1541. }
  1542. }
  1543. }
  1544. $sql .= " LIMIT $from,$number_of_items";
  1545. $result = Database::query($sql);
  1546. $tickets[0] = array(
  1547. utf8_decode('Ticket#'),
  1548. utf8_decode('Fecha'),
  1549. utf8_decode('Fecha Edicion'),
  1550. utf8_decode('Categoria'),
  1551. utf8_decode('Usuario'),
  1552. utf8_decode('Estado'),
  1553. utf8_decode('Mensajes'),
  1554. utf8_decode('Responsable'),
  1555. utf8_decode('Programa'),
  1556. );
  1557. while ($row = Database::fetch_assoc($result)) {
  1558. if ($row['responsable'] != 0) {
  1559. $row['responsable'] = api_get_user_info($row['responsable']);
  1560. $row['responsable'] = $row['responsable']['firstname'].' '.$row['responsable']['lastname'];
  1561. }
  1562. $row['sys_insert_datetime'] = api_format_date(
  1563. $row['sys_insert_datetime'],
  1564. '%d/%m/%y - %I:%M:%S %p'
  1565. );
  1566. $row['sys_lastedit_datetime'] = api_format_date(
  1567. $row['sys_lastedit_datetime'],
  1568. '%d/%m/%y - %I:%M:%S %p'
  1569. );
  1570. $row['category'] = utf8_decode($row['category']);
  1571. $row['programa'] = utf8_decode($row['fullname']);
  1572. $row['fullname'] = utf8_decode($row['fullname']);
  1573. $row['responsable'] = utf8_decode($row['responsable']);
  1574. $tickets[] = $row;
  1575. }
  1576. return $tickets;
  1577. }
  1578. /**
  1579. * @param string $url
  1580. * @param int $projectId
  1581. *
  1582. * @return FormValidator
  1583. */
  1584. public static function getCategoryForm($url, $projectId)
  1585. {
  1586. $form = new FormValidator('category', 'post', $url);
  1587. $form->addText('name', get_lang('Name'));
  1588. $form->addHtmlEditor('description', get_lang('Description'));
  1589. $form->addHidden('project_id', $projectId);
  1590. $form->addButtonUpdate(get_lang('Save'));
  1591. return $form;
  1592. }
  1593. /**
  1594. * @return array
  1595. */
  1596. public static function getStatusList()
  1597. {
  1598. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->findAll();
  1599. $list = [];
  1600. /** @var Status $row */
  1601. foreach ($items as $row) {
  1602. $list[$row->getId()] = $row->getName();
  1603. }
  1604. return $list;
  1605. }
  1606. /**
  1607. * @return array
  1608. */
  1609. public static function getTicketsFromCriteria($criteria)
  1610. {
  1611. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Ticket')->findBy($criteria);
  1612. $list = [];
  1613. /** @var Ticket $row */
  1614. foreach ($items as $row) {
  1615. $list[$row->getId()] = $row->getCode();
  1616. }
  1617. return $list;
  1618. }
  1619. /**
  1620. * @param string $code
  1621. * @return int
  1622. */
  1623. public static function getStatusIdFromCode($code)
  1624. {
  1625. $item = Database::getManager()
  1626. ->getRepository('ChamiloTicketBundle:Status')
  1627. ->findOneBy(['code' => $code])
  1628. ;
  1629. if ($item) {
  1630. return $item->getId();
  1631. }
  1632. return 0;
  1633. }
  1634. /**
  1635. * @return array
  1636. */
  1637. public static function getPriorityList()
  1638. {
  1639. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->findAll();
  1640. $list = [];
  1641. /** @var Priority $row */
  1642. foreach ($projects as $row) {
  1643. $list[$row->getId()] = $row->getName();
  1644. }
  1645. return $list;
  1646. }
  1647. /**
  1648. * @return array
  1649. */
  1650. public static function getProjects()
  1651. {
  1652. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Project')->findAll();
  1653. $list = [];
  1654. /** @var Project $row */
  1655. foreach ($projects as $row) {
  1656. $list[] = [
  1657. 'id' => $row->getId(),
  1658. '0' => $row->getId(),
  1659. '1' => $row->getName(),
  1660. '2' => $row->getDescription(),
  1661. '3' => $row->getId()
  1662. ];
  1663. }
  1664. return $list;
  1665. }
  1666. /**
  1667. * @return array
  1668. */
  1669. public static function getProjectsSimple()
  1670. {
  1671. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Project')->findAll();
  1672. $list = [];
  1673. /** @var Project $row */
  1674. foreach ($projects as $row) {
  1675. $list[] = [
  1676. 'id' => $row->getId(),
  1677. '0' => $row->getId(),
  1678. '1' => Display::url(
  1679. $row->getName(),
  1680. api_get_path(WEB_CODE_PATH).'ticket/tickets.php?project_id='.$row->getId()
  1681. ),
  1682. '2' => $row->getDescription()
  1683. ];
  1684. }
  1685. return $list;
  1686. }
  1687. /**
  1688. * @return int
  1689. */
  1690. public static function getProjectsCount()
  1691. {
  1692. $count = Database::getManager()->getRepository('ChamiloTicketBundle:Project')->createQueryBuilder('p')
  1693. ->select('COUNT(p.id)')
  1694. ->getQuery()
  1695. ->getSingleScalarResult();
  1696. return $count;
  1697. }
  1698. /**
  1699. * @param array $params
  1700. */
  1701. public static function addProject($params)
  1702. {
  1703. $project = new Project();
  1704. $project->setName($params['name']);
  1705. $project->setDescription($params['description']);
  1706. $project->setInsertUserId(api_get_user_id());
  1707. Database::getManager()->persist($project);
  1708. Database::getManager()->flush();
  1709. }
  1710. /**
  1711. * @param $id
  1712. * @return Project
  1713. */
  1714. public static function getProject($id)
  1715. {
  1716. return Database::getManager()->getRepository('ChamiloTicketBundle:Project')->find($id);
  1717. }
  1718. /**
  1719. * @param int $id
  1720. * @param array $params
  1721. */
  1722. public static function updateProject($id, $params)
  1723. {
  1724. $project = self::getProject($id);
  1725. $project->setName($params['name']);
  1726. $project->setDescription($params['description']);
  1727. $project->setLastEditDateTime(new DateTime($params['sys_lastedit_datetime']));
  1728. $project->setLastEditUserId($params['sys_lastedit_user_id']);
  1729. Database::getManager()->merge($project);
  1730. Database::getManager()->flush();
  1731. }
  1732. /**
  1733. * @param int $id
  1734. */
  1735. public static function deleteProject($id)
  1736. {
  1737. $project = self::getProject($id);
  1738. if ($project) {
  1739. Database::getManager()->remove($project);
  1740. Database::getManager()->flush();
  1741. }
  1742. }
  1743. /**
  1744. * @param string $url
  1745. * @return FormValidator
  1746. */
  1747. public static function getProjectForm($url)
  1748. {
  1749. $form = new FormValidator('project', 'post', $url);
  1750. $form->addText('name', get_lang('Name'));
  1751. $form->addHtmlEditor('description', get_lang('Description'));
  1752. $form->addButtonUpdate(get_lang('Save'));
  1753. return $form;
  1754. }
  1755. /**
  1756. * @return array
  1757. */
  1758. public static function getStatusAdminList()
  1759. {
  1760. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->findAll();
  1761. $list = [];
  1762. /** @var Status $row */
  1763. foreach ($items as $row) {
  1764. $list[] = [
  1765. 'id' => $row->getId(),
  1766. 'code' => $row->getCode(),
  1767. '0' => $row->getId(),
  1768. '1' => $row->getName(),
  1769. '2' => $row->getDescription(),
  1770. '3' => $row->getId()
  1771. ];
  1772. }
  1773. return $list;
  1774. }
  1775. /**
  1776. * @return array
  1777. */
  1778. public static function getStatusSimple()
  1779. {
  1780. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->findAll();
  1781. $list = [];
  1782. /** @var Project $row */
  1783. foreach ($projects as $row) {
  1784. $list[] = [
  1785. 'id' => $row->getId(),
  1786. '0' => $row->getId(),
  1787. '1' => Display::url($row->getName()),
  1788. '2' => $row->getDescription()
  1789. ];
  1790. }
  1791. return $list;
  1792. }
  1793. /**
  1794. * @return int
  1795. */
  1796. public static function getStatusCount()
  1797. {
  1798. $count = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->createQueryBuilder('p')
  1799. ->select('COUNT(p.id)')
  1800. ->getQuery()
  1801. ->getSingleScalarResult();
  1802. return $count;
  1803. }
  1804. /**
  1805. * @param array $params
  1806. */
  1807. public static function addStatus($params)
  1808. {
  1809. $item = new Status();
  1810. $item->setCode(URLify::filter($params['name']));
  1811. $item->setName($params['name']);
  1812. $item->setDescription($params['description']);
  1813. Database::getManager()->persist($item);
  1814. Database::getManager()->flush();
  1815. }
  1816. /**
  1817. * @param $id
  1818. * @return Project
  1819. */
  1820. public static function getStatus($id)
  1821. {
  1822. return Database::getManager()->getRepository('ChamiloTicketBundle:Status')->find($id);
  1823. }
  1824. /**
  1825. * @param int $id
  1826. * @param array $params
  1827. */
  1828. public static function updateStatus($id, $params)
  1829. {
  1830. $item = self::getStatus($id);
  1831. $item->setName($params['name']);
  1832. $item->setDescription($params['description']);
  1833. Database::getManager()->merge($item);
  1834. Database::getManager()->flush();
  1835. }
  1836. /**
  1837. * @param int $id
  1838. */
  1839. public static function deleteStatus($id)
  1840. {
  1841. $item = self::getStatus($id);
  1842. if ($item) {
  1843. Database::getManager()->remove($item);
  1844. Database::getManager()->flush();
  1845. }
  1846. }
  1847. /**
  1848. * @param string $url
  1849. * @return FormValidator
  1850. */
  1851. public static function getStatusForm($url)
  1852. {
  1853. $form = new FormValidator('status', 'post', $url);
  1854. $form->addText('name', get_lang('Name'));
  1855. $form->addHtmlEditor('description', get_lang('Description'));
  1856. $form->addButtonUpdate(get_lang('Save'));
  1857. return $form;
  1858. }
  1859. /**
  1860. * @return array
  1861. */
  1862. public static function getPriorityAdminList()
  1863. {
  1864. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->findAll();
  1865. $list = [];
  1866. /** @var Status $row */
  1867. foreach ($items as $row) {
  1868. $list[] = [
  1869. 'id' => $row->getId(),
  1870. 'code' => $row->getCode(),
  1871. '0' => $row->getId(),
  1872. '1' => $row->getName(),
  1873. '2' => $row->getDescription(),
  1874. '3' => $row->getId()
  1875. ];
  1876. }
  1877. return $list;
  1878. }
  1879. /**
  1880. * @return array
  1881. */
  1882. public static function getPrioritySimple()
  1883. {
  1884. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->findAll();
  1885. $list = [];
  1886. /** @var Priority $row */
  1887. foreach ($projects as $row) {
  1888. $list[] = [
  1889. 'id' => $row->getId(),
  1890. '0' => $row->getId(),
  1891. '1' => Display::url($row->getName()),
  1892. '2' => $row->getDescription()
  1893. ];
  1894. }
  1895. return $list;
  1896. }
  1897. /**
  1898. * @return int
  1899. */
  1900. public static function getPriorityCount()
  1901. {
  1902. $count = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->createQueryBuilder('p')
  1903. ->select('COUNT(p.id)')
  1904. ->getQuery()
  1905. ->getSingleScalarResult();
  1906. return $count;
  1907. }
  1908. /**
  1909. * @param array $params
  1910. */
  1911. public static function addPriority($params)
  1912. {
  1913. $item = new Priority();
  1914. $item
  1915. ->setCode(URLify::filter($params['name']))
  1916. ->setName($params['name'])
  1917. ->setDescription($params['description'])
  1918. ->setColor('')
  1919. ->setInsertUserId(api_get_user_id())
  1920. ->setUrgency('')
  1921. ;
  1922. Database::getManager()->persist($item);
  1923. Database::getManager()->flush();
  1924. }
  1925. /**
  1926. * @param $id
  1927. * @return Priority
  1928. */
  1929. public static function getPriority($id)
  1930. {
  1931. return Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->find($id);
  1932. }
  1933. /**
  1934. * @param int $id
  1935. * @param array $params
  1936. */
  1937. public static function updatePriority($id, $params)
  1938. {
  1939. $item = self::getPriority($id);
  1940. $item->setName($params['name']);
  1941. $item->setDescription($params['description']);
  1942. Database::getManager()->merge($item);
  1943. Database::getManager()->flush();
  1944. }
  1945. /**
  1946. * @param int $id
  1947. */
  1948. public static function deletePriority($id)
  1949. {
  1950. $item = self::getPriority($id);
  1951. if ($item) {
  1952. Database::getManager()->remove($item);
  1953. Database::getManager()->flush();
  1954. }
  1955. }
  1956. /**
  1957. * @param string $url
  1958. * @return FormValidator
  1959. */
  1960. public static function getPriorityForm($url)
  1961. {
  1962. $form = new FormValidator('priority', 'post', $url);
  1963. $form->addText('name', get_lang('Name'));
  1964. $form->addHtmlEditor('description', get_lang('Description'));
  1965. $form->addButtonUpdate(get_lang('Save'));
  1966. return $form;
  1967. }
  1968. /**
  1969. * Returns a list of menu elements for the tickets system's configuration
  1970. * @param string $exclude The element to exclude from the list
  1971. * @return array
  1972. */
  1973. public static function getSettingsMenuItems($exclude = null)
  1974. {
  1975. $items = [];
  1976. $project = [
  1977. 'icon' => 'career.png',
  1978. 'url' => 'projects.php',
  1979. 'content' => get_lang('Projects')
  1980. ];
  1981. $status = [
  1982. 'icon' => 'check-circle.png',
  1983. 'url' => 'status.php',
  1984. 'content' => get_lang('Status')
  1985. ];
  1986. $priority = [
  1987. 'icon' => 'order-course.png',
  1988. 'url' => 'priorities.php',
  1989. 'content' => get_lang('Priority')
  1990. ];
  1991. switch ($exclude) {
  1992. case 'project':
  1993. $items = [$status, $priority];
  1994. break;
  1995. case 'status':
  1996. $items = [$project, $priority];
  1997. break;
  1998. case 'priority':
  1999. $items = [$project, $status];
  2000. break;
  2001. default:
  2002. $items = [$project, $status, $priority];
  2003. break;
  2004. }
  2005. return $items;
  2006. }
  2007. /**
  2008. * Returns a list of strings representing the default statuses
  2009. * @return array
  2010. */
  2011. public static function getDefaultStatusList()
  2012. {
  2013. return [
  2014. self::STATUS_NEW,
  2015. self::STATUS_PENDING,
  2016. self::STATUS_UNCONFIRMED,
  2017. self::STATUS_CLOSE,
  2018. self::STATUS_FORWARDED
  2019. ];
  2020. }
  2021. /**
  2022. * @return array
  2023. */
  2024. public static function getDefaultPriorityList()
  2025. {
  2026. return [
  2027. self::PRIORITY_NORMAL,
  2028. self::PRIORITY_HIGH,
  2029. self::PRIORITY_LOW,
  2030. self::STATUS_CLOSE,
  2031. self::STATUS_FORWARDED
  2032. ];
  2033. }
  2034. /**
  2035. * Deletes the user from all the ticket system
  2036. * @param int $userId
  2037. */
  2038. public static function deleteUserFromTicketSystem($userId)
  2039. {
  2040. $userId = (int) $userId;
  2041. $schema = Database::getManager()->getConnection()->getSchemaManager();
  2042. if ($schema->tablesExist('ticket_assigned_log')) {
  2043. $sql = "UPDATE ticket_assigned_log SET user_id = NULL WHERE user_id = $userId";
  2044. Database::query($sql);
  2045. $sql = "UPDATE ticket_assigned_log SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2046. Database::query($sql);
  2047. }
  2048. if ($schema->tablesExist('ticket_ticket')) {
  2049. $sql = "UPDATE ticket_ticket SET assigned_last_user = NULL WHERE assigned_last_user = $userId";
  2050. Database::query($sql);
  2051. $sql = "UPDATE ticket_ticket SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2052. Database::query($sql);
  2053. $sql = "UPDATE ticket_ticket SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2054. Database::query($sql);
  2055. }
  2056. if ($schema->tablesExist('ticket_category')) {
  2057. $sql = "UPDATE ticket_category SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2058. Database::query($sql);
  2059. $sql = "UPDATE ticket_category SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2060. Database::query($sql);
  2061. }
  2062. if ($schema->tablesExist('ticket_category_rel_user')) {
  2063. $sql = "DELETE FROM ticket_category_rel_user WHERE user_id = $userId";
  2064. Database::query($sql);
  2065. }
  2066. if ($schema->tablesExist('ticket_message')) {
  2067. $sql = "UPDATE ticket_message SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2068. Database::query($sql);
  2069. $sql = "UPDATE ticket_message SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2070. Database::query($sql);
  2071. }
  2072. if ($schema->tablesExist('ticket_message_attachments')) {
  2073. $sql = "UPDATE ticket_message_attachments SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2074. Database::query($sql);
  2075. $sql = "UPDATE ticket_message_attachments SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2076. Database::query($sql);
  2077. }
  2078. if ($schema->tablesExist('ticket_priority')) {
  2079. $sql = "UPDATE ticket_priority SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2080. Database::query($sql);
  2081. $sql = "UPDATE ticket_priority SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2082. Database::query($sql);
  2083. }
  2084. if ($schema->tablesExist('ticket_project')) {
  2085. $sql = "UPDATE ticket_project SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2086. Database::query($sql);
  2087. $sql = "UPDATE ticket_project SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2088. Database::query($sql);
  2089. }
  2090. }
  2091. /**
  2092. * @param array $userInfo
  2093. * @param int $projectId
  2094. *
  2095. * @return bool
  2096. */
  2097. public static function userIsAllowInProject($userInfo, $projectId)
  2098. {
  2099. if (api_is_platform_admin()) {
  2100. return true;
  2101. }
  2102. $allowRoleList = self::getAllowedRolesFromProject($projectId);
  2103. // Check if a role was set to the project
  2104. if (!empty($allowRoleList) && is_array($allowRoleList)) {
  2105. if (in_array($userInfo['status'], $allowRoleList)) {
  2106. return true;
  2107. }
  2108. }
  2109. return false;
  2110. }
  2111. /**
  2112. * @param int $projectId
  2113. * @todo load from database instead of configuration.php setting
  2114. * @return array
  2115. */
  2116. public static function getAllowedRolesFromProject($projectId)
  2117. {
  2118. $options = api_get_configuration_value('ticket_project_user_roles');
  2119. if ($options) {
  2120. if (isset($options['permissions'][$projectId])) {
  2121. return $options['permissions'][$projectId];
  2122. }
  2123. }
  2124. return [];
  2125. }
  2126. }