TicketManager.php 82 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364
  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. Display::display_error_message(
  639. get_lang('UplUnableToSaveFileFilteredExtension')
  640. );
  641. } else {
  642. $new_file_name = uniqid('');
  643. $path_attachment = api_get_path(SYS_ARCHIVE_PATH);
  644. $path_message_attach = $path_attachment.'plugin_ticket_messageattch/';
  645. if (!file_exists($path_message_attach)) {
  646. @mkdir($path_message_attach, api_get_permissions_for_new_directories(), true);
  647. }
  648. $new_path = $path_message_attach.$new_file_name;
  649. if (is_uploaded_file($file_attach['tmp_name'])) {
  650. @copy($file_attach['tmp_name'], $new_path);
  651. }
  652. $safe_file_name = Database::escape_string($file_name);
  653. $safe_new_file_name = Database::escape_string($new_file_name);
  654. $sql = "INSERT INTO $table_support_message_attachments (
  655. filename,
  656. path,
  657. ticket_id,
  658. message_id,
  659. size,
  660. sys_insert_user_id,
  661. sys_insert_datetime,
  662. sys_lastedit_user_id,
  663. sys_lastedit_datetime
  664. ) VALUES (
  665. '$safe_file_name',
  666. '$safe_new_file_name',
  667. '$ticketId',
  668. '$message_id',
  669. '".$file_attach['size']."',
  670. '$userId',
  671. '$now',
  672. '$userId',
  673. '$now'
  674. )";
  675. Database::query($sql);
  676. return array(
  677. 'path' => $path_message_attach.$safe_new_file_name,
  678. 'filename' => $safe_file_name,
  679. );
  680. }
  681. }
  682. /**
  683. * Get tickets by userId
  684. * @param int $from
  685. * @param int $number_of_items
  686. * @param $column
  687. * @param $direction
  688. * @param int $userId
  689. * @return array
  690. */
  691. public static function get_tickets_by_user_id(
  692. $from,
  693. $number_of_items,
  694. $column,
  695. $direction,
  696. $userId = 0
  697. ) {
  698. $table_support_category = Database::get_main_table(TABLE_TICKET_CATEGORY);
  699. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  700. $table_support_priority = Database::get_main_table(TABLE_TICKET_PRIORITY);
  701. $table_support_status = Database::get_main_table(TABLE_TICKET_STATUS);
  702. $direction = !empty($direction) ? $direction : 'DESC';
  703. $userId = !empty($userId) ? $userId : api_get_user_id();
  704. $isAdmin = UserManager::is_admin($userId);
  705. switch ($column) {
  706. case 0:
  707. $column = 'ticket_id';
  708. break;
  709. case 1:
  710. $column = 'status_name';
  711. break;
  712. case 2:
  713. $column = 'start_date';
  714. break;
  715. case 3:
  716. $column = 'sys_lastedit_datetime';
  717. break;
  718. case 4:
  719. $column = 'category_name';
  720. break;
  721. case 5:
  722. $column = 'sys_insert_user_id';
  723. break;
  724. case 6:
  725. $column = 'assigned_last_user';
  726. break;
  727. case 7:
  728. $column = 'total_messages';
  729. break;
  730. case 8:
  731. $column = 'subject';
  732. break;
  733. default:
  734. $column = 'ticket_id';
  735. }
  736. $sql = "SELECT DISTINCT
  737. ticket.*,
  738. ticket.id ticket_id,
  739. status.name AS status_name,
  740. ticket.start_date,
  741. ticket.sys_lastedit_datetime,
  742. cat.name AS category_name,
  743. priority.name AS priority_name,
  744. ticket.total_messages AS total_messages,
  745. ticket.message AS message,
  746. ticket.subject AS subject,
  747. ticket.assigned_last_user
  748. FROM $table_support_tickets ticket
  749. INNER JOIN $table_support_category cat
  750. ON (cat.id = ticket.category_id)
  751. INNER JOIN $table_support_priority priority
  752. ON (ticket.priority_id = priority.id)
  753. INNER JOIN $table_support_status status
  754. ON (ticket.status_id = status.id)
  755. WHERE 1=1
  756. ";
  757. if (!$isAdmin) {
  758. $sql .= " AND (ticket.assigned_last_user = $userId OR ticket.sys_insert_user_id = $userId )";
  759. }
  760. // Search simple
  761. if (isset($_GET['submit_simple']) && $_GET['keyword'] != '') {
  762. $keyword = Database::escape_string(trim($_GET['keyword']));
  763. $sql .= " AND (
  764. ticket.id LIKE '%$keyword%' OR
  765. ticket.code LIKE '%$keyword%' OR
  766. ticket.subject LIKE '%$keyword%' OR
  767. ticket.message LIKE '%$keyword%' OR
  768. ticket.keyword LIKE '%$keyword%' OR
  769. ticket.source LIKE '%$keyword%' OR
  770. ticket.personal_email LIKE '%$keyword%'
  771. )";
  772. }
  773. // Search advanced
  774. if (isset($_GET['submit_advanced'])) {
  775. $keyword_category = Database::escape_string(
  776. trim($_GET['keyword_category'])
  777. );
  778. $keyword_admin = Database::escape_string(
  779. trim($_GET['keyword_admin'])
  780. );
  781. $keyword_start_date_start = Database::escape_string(
  782. trim($_GET['keyword_start_date_start'])
  783. );
  784. $keyword_start_date_end = Database::escape_string(
  785. trim($_GET['keyword_start_date_end'])
  786. );
  787. $keyword_status = Database::escape_string(
  788. trim($_GET['keyword_status'])
  789. );
  790. $keyword_source = isset($_GET['keyword_source']) ? Database::escape_string(trim($_GET['keyword_source'])) : '';
  791. $keyword_priority = Database::escape_string(
  792. trim($_GET['keyword_priority'])
  793. );
  794. $keyword_range = !empty($keyword_start_date_start) && !empty($keyword_start_date_end);
  795. $keyword_course = Database::escape_string(trim($_GET['keyword_course']));
  796. if ($keyword_category != '') {
  797. $sql .= " AND ticket.category_id = '$keyword_category' ";
  798. }
  799. if ($keyword_admin != '') {
  800. $sql .= " AND ticket.assigned_last_user = '$keyword_admin' ";
  801. }
  802. if ($keyword_status != '') {
  803. $sql .= " AND ticket.status_id = '$keyword_status' ";
  804. }
  805. if ($keyword_range == false && $keyword_start_date_start != '') {
  806. $sql .= " AND DATE_FORMAT(ticket.start_date,'%d/%m/%Y') >= '$keyword_start_date_start' ";
  807. }
  808. if ($keyword_range && $keyword_start_date_start != '' && $keyword_start_date_end != '') {
  809. $sql .= " AND DATE_FORMAT(ticket.start_date,'%d/%m/%Y') >= '$keyword_start_date_start'
  810. AND DATE_FORMAT(ticket.start_date,'%d/%m/%Y') <= '$keyword_start_date_end'";
  811. }
  812. if ($keyword_priority != '') {
  813. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  814. }
  815. if ($keyword_source != '') {
  816. $sql .= " AND ticket.source = '$keyword_source' ";
  817. }
  818. if ($keyword_course != '') {
  819. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  820. $sql .= " AND ticket.course_id IN (
  821. SELECT id FROM $course_table
  822. WHERE (
  823. title LIKE '%$keyword_course%' OR
  824. code LIKE '%$keyword_course%' OR
  825. visual_code LIKE '%$keyword_course%'
  826. )
  827. )";
  828. }
  829. }
  830. $sql .= " ORDER BY $column $direction";
  831. $sql .= " LIMIT $from, $number_of_items";
  832. $result = Database::query($sql);
  833. $tickets = array();
  834. $webPath = api_get_path(WEB_PATH);
  835. while ($row = Database::fetch_assoc($result)) {
  836. /*$sql_unread = "SELECT
  837. COUNT(DISTINCT message.message_id) AS unread
  838. FROM $table_support_tickets ticket,
  839. $table_support_messages message,
  840. $table_main_user user
  841. WHERE ticket.ticket_id = message.ticket_id
  842. AND ticket.ticket_id = '{$row['col0']}'
  843. AND message.status = 'NOL'
  844. AND message.sys_insert_user_id = user.user_id ";
  845. if ($isAdmin) {
  846. $sql_unread .= " AND user.user_id
  847. NOT IN (SELECT user_id FROM $table_main_admin)
  848. AND ticket.status_id != '".self::STATUS_FORWARDED."' ";
  849. } else {
  850. $sql_unread .= " AND user.user_id
  851. IN (SELECT user_id FROM $table_main_admin) ";
  852. }
  853. $result_unread = Database::query($sql_unread);
  854. $unread = Database::fetch_object($result_unread)->unread;*/
  855. $userInfo = api_get_user_info($row['sys_insert_user_id']);
  856. $hrefUser = $webPath.'main/admin/user_information.php?user_id='.$userInfo['user_id'];
  857. $name = "<a href='$hrefUser'> {$userInfo['complete_name_with_username']} </a>";
  858. $actions = '';
  859. if ($row['assigned_last_user'] != 0) {
  860. $assignedUserInfo = api_get_user_info($row['assigned_last_user']);
  861. if (!empty($assignedUserInfo)) {
  862. $hrefResp = $webPath.'main/admin/user_information.php?user_id='.$assignedUserInfo['user_id'];
  863. $row['assigned_last_user'] = "<a href='$hrefResp'> {$assignedUserInfo['complete_name_with_username']} </a>";
  864. } else {
  865. $row['assigned_last_user'] = get_lang('UnknownUser');
  866. }
  867. } else {
  868. if ($row['status_id'] !== self::STATUS_FORWARDED) {
  869. $row['assigned_last_user'] = '<span style="color:#ff0000;">'.get_lang('ToBeAssigned').'</span>';
  870. } else {
  871. $row['assigned_last_user'] = '<span style="color:#00ff00;">'.get_lang('MessageResent').'</span>';
  872. }
  873. }
  874. switch ($row['source']) {
  875. case self::SOURCE_PRESENTIAL:
  876. $img_source = 'icons/32/user.png';
  877. break;
  878. case self::SOURCE_EMAIL:
  879. $img_source = 'icons/32/mail.png';
  880. break;
  881. case self::SOURCE_PHONE:
  882. $img_source = 'icons/32/event.png';
  883. break;
  884. default:
  885. $img_source = 'icons/32/course_home.png';
  886. break;
  887. }
  888. $row['start_date'] = Display::dateToStringAgoAndLongDate($row['start_date']);
  889. $row['sys_lastedit_datetime'] = Display::dateToStringAgoAndLongDate($row['sys_lastedit_datetime']);
  890. $icon = Display::return_icon($img_source, get_lang('Info')).'<a href="ticket_details.php?ticket_id='.$row['id'].'">'.$row['code'].'</a>';
  891. if ($isAdmin) {
  892. $ticket = array(
  893. $icon.' '.$row['subject'],
  894. $row['status_name'],
  895. $row['start_date'],
  896. $row['sys_lastedit_datetime'],
  897. $row['category_name'],
  898. $name,
  899. $row['assigned_last_user'],
  900. $row['total_messages']
  901. );
  902. } else {
  903. $actions = '';
  904. /*
  905. $now = api_strtotime(api_get_utc_datetime());
  906. $last_edit_date = api_strtotime($row['sys_lastedit_datetime']);
  907. $dif = $now - $last_edit_date;
  908. if ($dif > 172800 && $row['priority_id'] === self::PRIORITY_NORMAL && $row['status_id'] != self::STATUS_CLOSE) {
  909. $actions .= '<a href="'.api_get_path(WEB_CODE_PATH).'ticket/tickets.php?ticket_id=' . $row['ticket_id'] . '&amp;action=alert">
  910. <img src="' . Display::returnIconPath('exclamation.png') . '" border="0" /></a>';
  911. }
  912. if ($row['priority_id'] === self::PRIORITY_HIGH) {
  913. $actions .= '<img src="' . Display::returnIconPath('admin_star.png') . '" border="0" />';
  914. }*/
  915. $ticket = array(
  916. $icon.' '.$row['subject'],
  917. $row['status_name'],
  918. $row['start_date'],
  919. $row['sys_lastedit_datetime'],
  920. $row['category_name']
  921. );
  922. }
  923. /*if ($unread > 0) {
  924. $ticket['0'] = $ticket['0'] . '&nbsp;&nbsp;(' . $unread . ')<a href="ticket_details.php?ticket_id=' . $row['ticket_id'] . '">
  925. <img src="' . Display::returnIconPath('message_new.png') . '" border="0" title="' . $unread . ' ' . get_lang('Messages') . '"/>
  926. </a>';
  927. }*/
  928. if ($isAdmin) {
  929. $ticket['0'] .= '&nbsp;&nbsp;<a href="javascript:void(0)" onclick="load_history_ticket(\'div_'.$row['ticket_id'].'\','.$row['ticket_id'].')">
  930. <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').'"/>
  931. <div class="blackboard_hide" id="div_' . $row['ticket_id'].'">&nbsp;&nbsp;</div>
  932. </a>&nbsp;&nbsp;';
  933. }
  934. $tickets[] = $ticket;
  935. }
  936. return $tickets;
  937. }
  938. /**
  939. * @param int $userId
  940. * @return mixed
  941. */
  942. public static function get_total_tickets_by_user_id($userId = 0)
  943. {
  944. $table_support_category = Database::get_main_table(TABLE_TICKET_CATEGORY);
  945. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  946. $table_support_priority = Database::get_main_table(TABLE_TICKET_PRIORITY);
  947. $table_support_status = Database::get_main_table(TABLE_TICKET_STATUS);
  948. $userId = api_get_user_id();
  949. $sql = "SELECT COUNT(ticket.id) AS total
  950. FROM $table_support_tickets ticket
  951. INNER JOIN $table_support_category cat
  952. ON (cat.id = ticket.category_id)
  953. INNER JOIN $table_support_priority priority
  954. ON (ticket.priority_id = priority.id)
  955. INNER JOIN $table_support_status status
  956. ON (ticket.status_id = status.id)
  957. WHERE 1 = 1";
  958. if (!api_is_platform_admin()) {
  959. $sql .= " AND (ticket.assigned_last_user = $userId OR ticket.sys_insert_user_id = $userId )";
  960. }
  961. // Search simple
  962. if (isset($_GET['submit_simple'])) {
  963. if ($_GET['keyword'] != '') {
  964. $keyword = Database::escape_string(trim($_GET['keyword']));
  965. $sql .= " AND (
  966. ticket.code LIKE '%$keyword%' OR
  967. ticket.subject LIKE '%$keyword%' OR
  968. ticket.message LIKE '%$keyword%' OR
  969. ticket.keyword LIKE '%$keyword%' OR
  970. ticket.personal_email LIKE '%$keyword%' OR
  971. ticket.source LIKE '%$keyword%'
  972. )";
  973. }
  974. }
  975. // Search advanced
  976. if (isset($_GET['submit_advanced'])) {
  977. $keyword_category = Database::escape_string(
  978. trim($_GET['keyword_category'])
  979. );
  980. $keyword_admin = Database::escape_string(
  981. trim($_GET['keyword_admin'])
  982. );
  983. $keyword_start_date_start = Database::escape_string(
  984. trim($_GET['keyword_start_date_start'])
  985. );
  986. $keyword_start_date_end = Database::escape_string(
  987. trim($_GET['keyword_start_date_end'])
  988. );
  989. $keyword_status = Database::escape_string(
  990. trim($_GET['keyword_status'])
  991. );
  992. $keyword_source = isset($_GET['keyword_source']) ? Database::escape_string(trim($_GET['keyword_source'])) : '';
  993. $keyword_priority = Database::escape_string(
  994. trim($_GET['keyword_priority'])
  995. );
  996. $keyword_range = isset($_GET['keyword_dates']) ? Database::escape_string(trim($_GET['keyword_dates'])) : '';
  997. $keyword_course = Database::escape_string(
  998. trim($_GET['keyword_course'])
  999. );
  1000. if ($keyword_category != '') {
  1001. $sql .= " AND ticket.category_id = '$keyword_category' ";
  1002. }
  1003. if ($keyword_admin != '') {
  1004. $sql .= " AND ticket.assigned_last_user = '$keyword_admin' ";
  1005. }
  1006. if ($keyword_status != '') {
  1007. $sql .= " AND ticket.status_id = '$keyword_status' ";
  1008. }
  1009. if ($keyword_range == false && $keyword_start_date_start != '') {
  1010. $sql .= " AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') = '$keyword_start_date_start' ";
  1011. }
  1012. if ($keyword_range && $keyword_start_date_start != '' && $keyword_start_date_end != '') {
  1013. $sql .= " AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') >= '$keyword_start_date_start'
  1014. AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') <= '$keyword_start_date_end'";
  1015. }
  1016. if ($keyword_priority != '') {
  1017. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  1018. }
  1019. if ($keyword_source != '') {
  1020. $sql .= " AND ticket.source = '$keyword_source' ";
  1021. }
  1022. if ($keyword_priority != '') {
  1023. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  1024. }
  1025. if ($keyword_course != '') {
  1026. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  1027. $sql .= " AND ticket.course_id IN ( ";
  1028. $sql .= "SELECT id
  1029. FROM $course_table
  1030. WHERE (title LIKE '%$keyword_course%'
  1031. OR code LIKE '%$keyword_course%'
  1032. OR visual_code LIKE '%$keyword_course%' )) ";
  1033. }
  1034. }
  1035. /*
  1036. if ($keyword_unread == 'yes') {
  1037. $sql .= " AND ticket.id IN ( ";
  1038. $sql .= "SELECT ticket.id
  1039. FROM $table_support_tickets ticket,
  1040. $table_support_messages message,
  1041. $table_main_user user
  1042. WHERE ticket.id = message.ticket_id
  1043. AND message.status = 'NOL'
  1044. AND message.sys_insert_user_id = user.user_id
  1045. AND user.user_id NOT IN (
  1046. SELECT user_id FROM $table_main_admin
  1047. ) AND ticket.status_id != '".self::STATUS_FORWARDED."'
  1048. GROUP BY ticket.id)";
  1049. } else {
  1050. if ($keyword_unread == 'no') {
  1051. $sql .= " AND ticket.id NOT IN ( ";
  1052. $sql .= " SELECT ticket.id
  1053. FROM $table_support_tickets ticket,
  1054. $table_support_messages message,
  1055. $table_main_user user
  1056. WHERE ticket.id = message.ticket_id
  1057. AND message.status = 'NOL'
  1058. AND message.sys_insert_user_id = user.user_id
  1059. AND user.user_id NOT IN (SELECT user_id FROM $table_main_admin)
  1060. AND ticket.status_id != '".self::STATUS_FORWARDED."'
  1061. GROUP BY ticket.id)";
  1062. }
  1063. }*/
  1064. $res = Database::query($sql);
  1065. $obj = Database::fetch_object($res);
  1066. return $obj->total;
  1067. }
  1068. /**
  1069. * @param int $ticketId
  1070. * @return array
  1071. */
  1072. public static function get_ticket_detail_by_id($ticketId)
  1073. {
  1074. $ticketId = intval($ticketId);
  1075. $table_support_category = Database::get_main_table(TABLE_TICKET_CATEGORY);
  1076. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1077. $table_support_priority = Database::get_main_table(TABLE_TICKET_PRIORITY);
  1078. $table_support_status = Database::get_main_table(TABLE_TICKET_STATUS);
  1079. $table_support_messages = Database::get_main_table(TABLE_TICKET_MESSAGE);
  1080. $table_support_message_attachments = Database::get_main_table(TABLE_TICKET_MESSAGE_ATTACHMENTS);
  1081. $table_main_user = Database::get_main_table(TABLE_MAIN_USER);
  1082. $sql = "SELECT
  1083. ticket.*,
  1084. cat.name,
  1085. status.name as status,
  1086. priority.name priority
  1087. FROM $table_support_tickets ticket
  1088. INNER JOIN $table_support_category cat
  1089. ON (cat.id = ticket.category_id)
  1090. INNER JOIN $table_support_priority priority
  1091. ON (priority.id = ticket.priority_id)
  1092. INNER JOIN $table_support_status status
  1093. ON (status.id = ticket.status_id)
  1094. WHERE
  1095. ticket.id = $ticketId ";
  1096. $result = Database::query($sql);
  1097. $ticket = array();
  1098. if (Database::num_rows($result) > 0) {
  1099. while ($row = Database::fetch_assoc($result)) {
  1100. $row['course'] = null;
  1101. $row['start_date_from_db'] = $row['start_date'];
  1102. $row['start_date'] = api_convert_and_format_date(api_get_local_time($row['start_date']), DATE_TIME_FORMAT_LONG, api_get_timezone());
  1103. $row['end_date_from_db'] = $row['end_date'];
  1104. $row['end_date'] = api_convert_and_format_date(api_get_local_time($row['end_date']), DATE_TIME_FORMAT_LONG, api_get_timezone());
  1105. $row['sys_lastedit_datetime_from_db'] = $row['sys_lastedit_datetime'];
  1106. $row['sys_lastedit_datetime'] = api_convert_and_format_date(api_get_local_time($row['sys_lastedit_datetime']), DATE_TIME_FORMAT_LONG, api_get_timezone());
  1107. $row['course_url'] = null;
  1108. if ($row['course_id'] != 0) {
  1109. $course = api_get_course_info_by_id($row['course_id']);
  1110. $sessionId = 0;
  1111. if ($row['session_id']) {
  1112. $sessionId = $row['session_id'];
  1113. }
  1114. if ($course) {
  1115. $row['course_url'] = '<a href="'.$course['course_public_url'].'?id_session='.$sessionId.'">'.$course['name'].'</a>';
  1116. }
  1117. }
  1118. $userInfo = api_get_user_info($row['sys_insert_user_id']);
  1119. $row['user_url'] = '<a href="'.api_get_path(WEB_PATH).'main/admin/user_information.php?user_id='.$userInfo['user_id'].'">
  1120. ' . $userInfo['complete_name'].'</a>';
  1121. $ticket['usuario'] = $userInfo;
  1122. $ticket['ticket'] = $row;
  1123. }
  1124. $sql = "SELECT *, message.id as message_id
  1125. FROM $table_support_messages message
  1126. INNER JOIN $table_main_user user
  1127. ON (message.sys_insert_user_id = user.user_id)
  1128. WHERE
  1129. message.ticket_id = '$ticketId' ";
  1130. $result = Database::query($sql);
  1131. $ticket['messages'] = array();
  1132. $attach_icon = Display::return_icon('attachment.gif', '');
  1133. $webPath = api_get_path(WEB_CODE_PATH);
  1134. while ($row = Database::fetch_assoc($result)) {
  1135. $message = $row;
  1136. $completeName = api_get_person_name($row['firstname'], $row['lastname']);
  1137. $href = $webPath.'main/admin/user_information.php?user_id='.$row['user_id'];
  1138. $message['admin'] = UserManager::is_admin($message['user_id']);
  1139. $message['user_created'] = "<a href='$href'> $completeName </a>";
  1140. $sql = "SELECT *
  1141. FROM $table_support_message_attachments
  1142. WHERE
  1143. message_id = ".$row['message_id']." AND
  1144. ticket_id = $ticketId";
  1145. $result_attach = Database::query($sql);
  1146. while ($row2 = Database::fetch_assoc($result_attach)) {
  1147. $archiveURL = $archiveURL = $webPath.'ticket/download.php?ticket_id='.$ticketId.'&file=';
  1148. $row2['attachment_link'] = $attach_icon.'&nbsp;<a href="'.$archiveURL.$row2['path'].'&title='.$row2['filename'].'">'.$row2['filename'].'</a>&nbsp;('.$row2['size'].')';
  1149. $message['attachments'][] = $row2;
  1150. }
  1151. $ticket['messages'][] = $message;
  1152. }
  1153. }
  1154. return $ticket;
  1155. }
  1156. /**
  1157. * @param int $ticketId
  1158. * @param int $userId
  1159. * @return bool
  1160. */
  1161. public static function update_message_status($ticketId, $userId)
  1162. {
  1163. $ticketId = intval($ticketId);
  1164. $userId = intval($userId);
  1165. $table_support_messages = Database::get_main_table(
  1166. TABLE_TICKET_MESSAGE
  1167. );
  1168. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1169. $now = api_get_utc_datetime();
  1170. $sql = "UPDATE $table_support_messages
  1171. SET
  1172. status = 'LEI',
  1173. sys_lastedit_user_id ='".api_get_user_id()."',
  1174. sys_lastedit_datetime ='" . $now."'
  1175. WHERE ticket_id ='$ticketId' ";
  1176. if (api_is_platform_admin()) {
  1177. $sql .= " AND sys_insert_user_id = '$userId'";
  1178. } else {
  1179. $sql .= " AND sys_insert_user_id != '$userId'";
  1180. }
  1181. $result = Database::query($sql);
  1182. if (Database::affected_rows($result) > 0) {
  1183. Database::query(
  1184. "UPDATE $table_support_tickets SET
  1185. status_id = '".self::STATUS_PENDING."'
  1186. WHERE id ='$ticketId' AND status_id = '".self::STATUS_NEW."'"
  1187. );
  1188. return true;
  1189. } else {
  1190. return false;
  1191. }
  1192. }
  1193. /**
  1194. * @param int $ticketId
  1195. * @param int $userId
  1196. * @param string $title
  1197. * @param string $message
  1198. * @param int $onlyToUserId
  1199. *
  1200. * @return bool
  1201. */
  1202. public static function sendNotification($ticketId, $title, $message, $onlyToUserId = 0)
  1203. {
  1204. $ticketInfo = self::get_ticket_detail_by_id($ticketId);
  1205. if (empty($ticketInfo)) {
  1206. return false;
  1207. }
  1208. $assignedUserInfo = api_get_user_info($ticketInfo['ticket']['assigned_last_user']);
  1209. $requestUserInfo = $ticketInfo['usuario'];
  1210. $ticketCode = $ticketInfo['ticket']['code'];
  1211. $status = $ticketInfo['ticket']['status'];
  1212. $priority = $ticketInfo['ticket']['priority'];
  1213. // Subject
  1214. $titleEmail = "[$ticketCode] $title";
  1215. // Content
  1216. $href = api_get_path(WEB_CODE_PATH).'/ticket/ticket_details.php?ticket_id='.$ticketId;
  1217. $ticketUrl = Display::url($ticketCode, $href);
  1218. $messageEmail = get_lang('TicketNum').": $ticketUrl <br />";
  1219. $messageEmail .= get_lang('Status').": $status <br />";
  1220. $messageEmail .= get_lang('Priority').": $priority <br />";
  1221. $messageEmail .= '<hr /><br />';
  1222. $messageEmail .= $message;
  1223. $currentUserId = api_get_user_id();
  1224. if (!empty($onlyToUserId)) {
  1225. // Send only to specific user
  1226. if ($currentUserId != $onlyToUserId) {
  1227. MessageManager::send_message_simple(
  1228. $onlyToUserId,
  1229. $titleEmail,
  1230. $messageEmail
  1231. );
  1232. }
  1233. } else {
  1234. // Send to assigned user and to author
  1235. if ($requestUserInfo && $currentUserId != $requestUserInfo['id']) {
  1236. MessageManager::send_message_simple(
  1237. $requestUserInfo['id'],
  1238. $titleEmail,
  1239. $messageEmail
  1240. );
  1241. }
  1242. if ($assignedUserInfo &&
  1243. $requestUserInfo['id'] != $assignedUserInfo['id'] &&
  1244. $currentUserId != $assignedUserInfo['id']
  1245. ) {
  1246. MessageManager::send_message_simple(
  1247. $assignedUserInfo['id'],
  1248. $titleEmail,
  1249. $messageEmail
  1250. );
  1251. }
  1252. }
  1253. }
  1254. /**
  1255. * @param array $params
  1256. * @param int $ticketId
  1257. * @param int $userId
  1258. *
  1259. * @return bool
  1260. */
  1261. public static function updateTicket(
  1262. $params,
  1263. $ticketId,
  1264. $userId
  1265. ) {
  1266. $now = api_get_utc_datetime();
  1267. $table = Database::get_main_table(TABLE_TICKET_TICKET);
  1268. $newParams = [
  1269. 'priority_id' => isset($params['priority_id']) ? $params['priority_id'] : '',
  1270. 'status_id' => isset($params['status_id']) ? $params['status_id'] : '',
  1271. 'sys_lastedit_user_id' => $userId,
  1272. 'sys_lastedit_datetime' => $now,
  1273. ];
  1274. Database::update($table, $newParams, ['id = ? ' => $ticketId]);
  1275. return true;
  1276. }
  1277. /**
  1278. * @param $status_id
  1279. * @param $ticketId
  1280. * @param $userId
  1281. * @return bool
  1282. */
  1283. public static function update_ticket_status(
  1284. $status_id,
  1285. $ticketId,
  1286. $userId
  1287. ) {
  1288. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1289. $ticketId = intval($ticketId);
  1290. $status_id = intval($status_id);
  1291. $userId = intval($userId);
  1292. $now = api_get_utc_datetime();
  1293. $sql = "UPDATE $table_support_tickets
  1294. SET
  1295. status_id = '$status_id',
  1296. sys_lastedit_user_id ='$userId',
  1297. sys_lastedit_datetime ='".$now."'
  1298. WHERE id ='$ticketId'";
  1299. $result = Database::query($sql);
  1300. if (Database::affected_rows($result) > 0) {
  1301. self::sendNotification(
  1302. $ticketId,
  1303. get_lang('TicketUpdated'),
  1304. get_lang('TicketUpdated')
  1305. );
  1306. return true;
  1307. } else {
  1308. return false;
  1309. }
  1310. }
  1311. /**
  1312. * @return mixed
  1313. */
  1314. public static function get_number_of_messages()
  1315. {
  1316. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1317. $table_support_messages = Database::get_main_table(
  1318. TABLE_TICKET_MESSAGE
  1319. );
  1320. $table_main_user = Database::get_main_table(TABLE_MAIN_USER);
  1321. $table_main_admin = Database::get_main_table(TABLE_MAIN_ADMIN);
  1322. $user_info = api_get_user_info();
  1323. $userId = $user_info['user_id'];
  1324. $sql = "SELECT COUNT(DISTINCT ticket.id) AS unread
  1325. FROM $table_support_tickets ticket,
  1326. $table_support_messages message ,
  1327. $table_main_user user
  1328. WHERE
  1329. ticket.id = message.ticket_id AND
  1330. message.status = 'NOL' AND
  1331. user.user_id = message.sys_insert_user_id ";
  1332. if (!api_is_platform_admin()) {
  1333. $sql .= " AND ticket.request_user = '$userId'
  1334. AND user_id IN (SELECT user_id FROM $table_main_admin) ";
  1335. } else {
  1336. $sql .= " AND user_id NOT IN (SELECT user_id FROM $table_main_admin)
  1337. AND ticket.status_id != '".self::STATUS_FORWARDED."'";
  1338. }
  1339. $sql .= " AND ticket.project_id != '' ";
  1340. $res = Database::query($sql);
  1341. $obj = Database::fetch_object($res);
  1342. return $obj->unread;
  1343. }
  1344. /**
  1345. * @param int $ticketId
  1346. * @param int $userId
  1347. */
  1348. public static function send_alert($ticketId, $userId)
  1349. {
  1350. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1351. $now = api_get_utc_datetime();
  1352. $ticketId = intval($ticketId);
  1353. $userId = intval($userId);
  1354. $sql = "UPDATE $table_support_tickets SET
  1355. priority_id = '".self::PRIORITY_HIGH."',
  1356. sys_lastedit_user_id ='$userId',
  1357. sys_lastedit_datetime ='$now'
  1358. WHERE id = '$ticketId'";
  1359. Database::query($sql);
  1360. }
  1361. /**
  1362. * @param int $ticketId
  1363. * @param int $userId
  1364. */
  1365. public static function close_ticket($ticketId, $userId)
  1366. {
  1367. $ticketId = intval($ticketId);
  1368. $userId = intval($userId);
  1369. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1370. $now = api_get_utc_datetime();
  1371. $sql = "UPDATE $table_support_tickets SET
  1372. status_id = '".self::STATUS_CLOSE."',
  1373. sys_lastedit_user_id ='$userId',
  1374. sys_lastedit_datetime ='".$now."',
  1375. end_date ='$now'
  1376. WHERE id ='$ticketId'";
  1377. Database::query($sql);
  1378. self::sendNotification(
  1379. $ticketId,
  1380. get_lang('TicketClosed'),
  1381. get_lang('TicketClosed')
  1382. );
  1383. }
  1384. /**
  1385. *
  1386. */
  1387. public static function close_old_tickets()
  1388. {
  1389. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1390. $now = api_get_utc_datetime();
  1391. $userId = api_get_user_id();
  1392. $sql = "UPDATE $table_support_tickets
  1393. SET
  1394. status_id = '".self::STATUS_CLOSE."',
  1395. sys_lastedit_user_id ='$userId',
  1396. sys_lastedit_datetime ='$now',
  1397. end_date = '$now'
  1398. WHERE
  1399. DATEDIFF('$now', sys_lastedit_datetime) > 7 AND
  1400. status_id != '".self::STATUS_CLOSE."' AND
  1401. status_id != '".self::STATUS_NEW."' AND
  1402. status_id != '".self::STATUS_FORWARDED."'";
  1403. Database::query($sql);
  1404. }
  1405. /**
  1406. * @param int $ticketId
  1407. * @return array
  1408. */
  1409. public static function get_assign_log($ticketId)
  1410. {
  1411. $table = Database::get_main_table(TABLE_TICKET_ASSIGNED_LOG);
  1412. $ticketId = intval($ticketId);
  1413. $sql = "SELECT * FROM $table
  1414. WHERE ticket_id = $ticketId
  1415. ORDER BY assigned_date DESC";
  1416. $result = Database::query($sql);
  1417. $history = [];
  1418. $webpath = api_get_path(WEB_PATH);
  1419. while ($row = Database::fetch_assoc($result)) {
  1420. if ($row['user_id'] != 0) {
  1421. $assignuser = api_get_user_info($row['user_id']);
  1422. $row['assignuser'] = '<a href="'.$webpath.'main/admin/user_information.php?user_id='.$row['user_id'].'" target="_blank">'.
  1423. $assignuser['username'].'</a>';
  1424. } else {
  1425. $row['assignuser'] = get_lang('Unassign');
  1426. }
  1427. $row['assigned_date'] = date_to_str_ago($row['assigned_date']);
  1428. $insertuser = api_get_user_info($row['sys_insert_user_id']);
  1429. $row['insertuser'] = '<a href="'.$webpath.'main/admin/user_information.php?user_id='.$row['sys_insert_user_id'].'" target="_blank">'.
  1430. $insertuser['username'].'</a>';
  1431. $history[] = $row;
  1432. }
  1433. return $history;
  1434. }
  1435. /**
  1436. * @param $from
  1437. * @param $number_of_items
  1438. * @param $column
  1439. * @param $direction
  1440. * @param null $userId
  1441. * @return array
  1442. */
  1443. public static function export_tickets_by_user_id(
  1444. $from,
  1445. $number_of_items,
  1446. $column,
  1447. $direction,
  1448. $userId = null
  1449. ) {
  1450. $from = intval($from);
  1451. $number_of_items = intval($number_of_items);
  1452. $table_support_category = Database::get_main_table(
  1453. TABLE_TICKET_CATEGORY
  1454. );
  1455. $table_support_tickets = Database::get_main_table(TABLE_TICKET_TICKET);
  1456. $table_support_priority = Database::get_main_table(
  1457. TABLE_TICKET_PRIORITY
  1458. );
  1459. $table_support_status = Database::get_main_table(TABLE_TICKET_STATUS);
  1460. $table_support_messages = Database::get_main_table(
  1461. TABLE_TICKET_MESSAGE
  1462. );
  1463. $table_main_user = Database::get_main_table(TABLE_MAIN_USER);
  1464. if (is_null($direction)) {
  1465. $direction = "DESC";
  1466. }
  1467. if (is_null($userId) || $userId == 0) {
  1468. $userId = api_get_user_id();
  1469. }
  1470. $sql = "SELECT
  1471. ticket.code,
  1472. ticket.sys_insert_datetime,
  1473. ticket.sys_lastedit_datetime,
  1474. cat.name as category,
  1475. CONCAT(user.lastname,' ', user.firstname) AS fullname,
  1476. status.name as status,
  1477. ticket.total_messages as messages,
  1478. ticket.assigned_last_user as responsable
  1479. FROM $table_support_tickets ticket,
  1480. $table_support_category cat ,
  1481. $table_support_priority priority,
  1482. $table_support_status status ,
  1483. $table_main_user user
  1484. WHERE
  1485. cat.id = ticket.category_id
  1486. AND ticket.priority_id = priority.id
  1487. AND ticket.status_id = status.id
  1488. AND user.user_id = ticket.request_user ";
  1489. // Search simple
  1490. if (isset($_GET['submit_simple'])) {
  1491. if ($_GET['keyword'] !== '') {
  1492. $keyword = Database::escape_string(trim($_GET['keyword']));
  1493. $sql .= " AND (ticket.code = '$keyword'
  1494. OR user.firstname LIKE '%$keyword%'
  1495. OR user.lastname LIKE '%$keyword%'
  1496. OR concat(user.firstname,' ',user.lastname) LIKE '%$keyword%'
  1497. OR concat(user.lastname,' ',user.firstname) LIKE '%$keyword%'
  1498. OR user.username LIKE '%$keyword%') ";
  1499. }
  1500. }
  1501. //Search advanced
  1502. if (isset($_GET['submit_advanced'])) {
  1503. $keyword_category = Database::escape_string(
  1504. trim($_GET['keyword_category'])
  1505. );
  1506. $keyword_request_user = Database::escape_string(
  1507. trim($_GET['keyword_request_user'])
  1508. );
  1509. $keyword_admin = Database::escape_string(
  1510. trim($_GET['keyword_admin'])
  1511. );
  1512. $keyword_start_date_start = Database::escape_string(
  1513. trim($_GET['keyword_start_date_start'])
  1514. );
  1515. $keyword_start_date_end = Database::escape_string(
  1516. trim($_GET['keyword_start_date_end'])
  1517. );
  1518. $keyword_status = Database::escape_string(
  1519. trim($_GET['keyword_status'])
  1520. );
  1521. $keyword_source = Database::escape_string(
  1522. trim($_GET['keyword_source'])
  1523. );
  1524. $keyword_priority = Database::escape_string(
  1525. trim($_GET['keyword_priority'])
  1526. );
  1527. $keyword_range = Database::escape_string(
  1528. trim($_GET['keyword_dates'])
  1529. );
  1530. $keyword_unread = Database::escape_string(
  1531. trim($_GET['keyword_unread'])
  1532. );
  1533. $keyword_course = Database::escape_string(
  1534. trim($_GET['keyword_course'])
  1535. );
  1536. if ($keyword_category != '') {
  1537. $sql .= " AND ticket.category_id = '$keyword_category' ";
  1538. }
  1539. if ($keyword_request_user != '') {
  1540. $sql .= " AND (ticket.request_user = '$keyword_request_user'
  1541. OR user.firstname LIKE '%$keyword_request_user%'
  1542. OR user.official_code LIKE '%$keyword_request_user%'
  1543. OR user.lastname LIKE '%$keyword_request_user%'
  1544. OR concat(user.firstname,' ',user.lastname) LIKE '%$keyword_request_user%'
  1545. OR concat(user.lastname,' ',user.firstname) LIKE '%$keyword_request_user%'
  1546. OR user.username LIKE '%$keyword_request_user%') ";
  1547. }
  1548. if ($keyword_admin != '') {
  1549. $sql .= " AND ticket.assigned_last_user = '$keyword_admin' ";
  1550. }
  1551. if ($keyword_status != '') {
  1552. $sql .= " AND ticket.status_id = '$keyword_status' ";
  1553. }
  1554. if ($keyword_range == '' && $keyword_start_date_start != '') {
  1555. $sql .= " AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') = '$keyword_start_date_start' ";
  1556. }
  1557. if ($keyword_range == '1' && $keyword_start_date_start != '' && $keyword_start_date_end != '') {
  1558. $sql .= " AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') >= '$keyword_start_date_start'
  1559. AND DATE_FORMAT( ticket.start_date,'%d/%m/%Y') <= '$keyword_start_date_end'";
  1560. }
  1561. if ($keyword_priority != '') {
  1562. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  1563. }
  1564. if ($keyword_source != '') {
  1565. $sql .= " AND ticket.source = '$keyword_source' ";
  1566. }
  1567. if ($keyword_priority != '') {
  1568. $sql .= " AND ticket.priority_id = '$keyword_priority' ";
  1569. }
  1570. if ($keyword_course != '') {
  1571. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  1572. $sql .= " AND ticket.course_id IN ( ";
  1573. $sql .= "SELECT id
  1574. FROM $course_table
  1575. WHERE (title LIKE '%$keyword_course%'
  1576. OR code LIKE '%$keyword_course%'
  1577. OR visual_code LIKE '%$keyword_course%' )) ";
  1578. }
  1579. if ($keyword_unread == 'yes') {
  1580. $sql .= " AND ticket.id IN (
  1581. SELECT ticket.id
  1582. FROM $table_support_tickets ticket,
  1583. $table_support_messages message,
  1584. $table_main_user user
  1585. WHERE ticket.id = message.ticket_id
  1586. AND message.status = 'NOL'
  1587. AND message.sys_insert_user_id = user.user_id
  1588. AND user.status != 1 AND ticket.status_id != '".self::STATUS_FORWARDED."'
  1589. GROUP BY ticket.id)";
  1590. } else {
  1591. if ($keyword_unread == 'no') {
  1592. $sql .= " AND ticket.id NOT IN (
  1593. SELECT ticket.id
  1594. FROM $table_support_tickets ticket,
  1595. $table_support_messages message,
  1596. $table_main_user user
  1597. WHERE ticket.id = message.ticket_id
  1598. AND message.status = 'NOL'
  1599. AND message.sys_insert_user_id = user.user_id
  1600. AND user.status != 1
  1601. AND ticket.status_id != '".self::STATUS_FORWARDED."'
  1602. GROUP BY ticket.id)";
  1603. }
  1604. }
  1605. }
  1606. $sql .= " LIMIT $from,$number_of_items";
  1607. $result = Database::query($sql);
  1608. $tickets[0] = array(
  1609. utf8_decode('Ticket#'),
  1610. utf8_decode('Fecha'),
  1611. utf8_decode('Fecha Edicion'),
  1612. utf8_decode('Categoria'),
  1613. utf8_decode('Usuario'),
  1614. utf8_decode('Estado'),
  1615. utf8_decode('Mensajes'),
  1616. utf8_decode('Responsable'),
  1617. utf8_decode('Programa'),
  1618. );
  1619. while ($row = Database::fetch_assoc($result)) {
  1620. if ($row['responsable'] != 0) {
  1621. $row['responsable'] = api_get_user_info($row['responsable']);
  1622. $row['responsable'] = $row['responsable']['firstname'].' '.$row['responsable']['lastname'];
  1623. }
  1624. $row['sys_insert_datetime'] = api_format_date(
  1625. $row['sys_insert_datetime'], '%d/%m/%y - %I:%M:%S %p'
  1626. );
  1627. $row['sys_lastedit_datetime'] = api_format_date(
  1628. $row['sys_lastedit_datetime'], '%d/%m/%y - %I:%M:%S %p'
  1629. );
  1630. $row['category'] = utf8_decode($row['category']);
  1631. $row['programa'] = utf8_decode($row['fullname']);
  1632. $row['fullname'] = utf8_decode($row['fullname']);
  1633. $row['responsable'] = utf8_decode($row['responsable']);
  1634. $tickets[] = $row;
  1635. }
  1636. return $tickets;
  1637. }
  1638. /**
  1639. * @param string $url
  1640. * @return FormValidator
  1641. */
  1642. public static function getCategoryForm($url, $projectId)
  1643. {
  1644. $form = new FormValidator('category', 'post', $url);
  1645. $form->addText('name', get_lang('Name'));
  1646. $form->addHtmlEditor('description', get_lang('Description'));
  1647. $form->addHidden('project_id', $projectId);
  1648. $form->addButtonUpdate(get_lang('Save'));
  1649. return $form;
  1650. }
  1651. /**
  1652. * @return array
  1653. */
  1654. public static function getStatusList()
  1655. {
  1656. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->findAll();
  1657. $list = [];
  1658. /** @var \Chamilo\TicketBundle\Entity\Status $row */
  1659. foreach ($items as $row) {
  1660. $list[$row->getId()] = $row->getName();
  1661. }
  1662. return $list;
  1663. }
  1664. /**
  1665. * @return array
  1666. */
  1667. public static function getTicketsFromCriteria($criteria)
  1668. {
  1669. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Ticket')->findBy($criteria);
  1670. $list = [];
  1671. /** @var Ticket $row */
  1672. foreach ($items as $row) {
  1673. $list[$row->getId()] = $row->getCode();
  1674. }
  1675. return $list;
  1676. }
  1677. /**
  1678. * @param string $code
  1679. * @return int
  1680. */
  1681. public static function getStatusIdFromCode($code)
  1682. {
  1683. $item = Database::getManager()
  1684. ->getRepository('ChamiloTicketBundle:Status')
  1685. ->findOneBy(['code' => $code])
  1686. ;
  1687. if ($item) {
  1688. return $item->getId();
  1689. }
  1690. return 0;
  1691. }
  1692. /**
  1693. * @return array
  1694. */
  1695. public static function getPriorityList()
  1696. {
  1697. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->findAll();
  1698. $list = [];
  1699. /** @var \Chamilo\TicketBundle\Entity\Priority $row */
  1700. foreach ($projects as $row) {
  1701. $list[$row->getId()] = $row->getName();
  1702. }
  1703. return $list;
  1704. }
  1705. /**
  1706. * @return array
  1707. */
  1708. public static function getProjects()
  1709. {
  1710. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Project')->findAll();
  1711. $list = [];
  1712. /** @var Project $row */
  1713. foreach ($projects as $row) {
  1714. $list[] = [
  1715. 'id' => $row->getId(),
  1716. '0' => $row->getId(),
  1717. '1' => $row->getName(),
  1718. '2' => $row->getDescription(),
  1719. '3' => $row->getId()
  1720. ];
  1721. }
  1722. return $list;
  1723. }
  1724. /**
  1725. * @return array
  1726. */
  1727. public static function getProjectsSimple()
  1728. {
  1729. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Project')->findAll();
  1730. $list = [];
  1731. /** @var Project $row */
  1732. foreach ($projects as $row) {
  1733. $list[] = [
  1734. 'id' => $row->getId(),
  1735. '0' => $row->getId(),
  1736. '1' => Display::url(
  1737. $row->getName(),
  1738. api_get_path(WEB_CODE_PATH).'ticket/tickets.php?project_id='.$row->getId()
  1739. ),
  1740. '2' => $row->getDescription()
  1741. ];
  1742. }
  1743. return $list;
  1744. }
  1745. /**
  1746. * @return int
  1747. */
  1748. public static function getProjectsCount()
  1749. {
  1750. $count = Database::getManager()->getRepository('ChamiloTicketBundle:Project')->createQueryBuilder('p')
  1751. ->select('COUNT(p.id)')
  1752. ->getQuery()
  1753. ->getSingleScalarResult();
  1754. return $count;
  1755. }
  1756. /**
  1757. * @param array $params
  1758. */
  1759. public static function addProject($params)
  1760. {
  1761. $project = new Project();
  1762. $project->setName($params['name']);
  1763. $project->setDescription($params['description']);
  1764. $project->setInsertUserId(api_get_user_id());
  1765. //$project->setEmail($params['email']);
  1766. Database::getManager()->persist($project);
  1767. Database::getManager()->flush();
  1768. }
  1769. /**
  1770. * @param $id
  1771. * @return Project
  1772. */
  1773. public static function getProject($id)
  1774. {
  1775. return Database::getManager()->getRepository('ChamiloTicketBundle:Project')->find($id);
  1776. }
  1777. /**
  1778. * @param int $id
  1779. * @param array $params
  1780. */
  1781. public static function updateProject($id, $params)
  1782. {
  1783. $project = self::getProject($id);
  1784. $project->setName($params['name']);
  1785. $project->setDescription($params['description']);
  1786. $project->setLastEditDateTime(new DateTime($params['sys_lastedit_datetime']));
  1787. $project->setLastEditUserId($params['sys_lastedit_user_id']);
  1788. Database::getManager()->merge($project);
  1789. Database::getManager()->flush();
  1790. }
  1791. /**
  1792. * @param int $id
  1793. */
  1794. public static function deleteProject($id)
  1795. {
  1796. $project = self::getProject($id);
  1797. if ($project) {
  1798. Database::getManager()->remove($project);
  1799. Database::getManager()->flush();
  1800. }
  1801. }
  1802. /**
  1803. * @param string $url
  1804. * @return FormValidator
  1805. */
  1806. public static function getProjectForm($url)
  1807. {
  1808. $form = new FormValidator('project', 'post', $url);
  1809. $form->addText('name', get_lang('Name'));
  1810. $form->addHtmlEditor('description', get_lang('Description'));
  1811. $form->addButtonUpdate(get_lang('Save'));
  1812. return $form;
  1813. }
  1814. /**
  1815. * @return array
  1816. */
  1817. public static function getStatusAdminList()
  1818. {
  1819. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->findAll();
  1820. $list = [];
  1821. /** @var Status $row */
  1822. foreach ($items as $row) {
  1823. $list[] = [
  1824. 'id' => $row->getId(),
  1825. 'code' => $row->getCode(),
  1826. '0' => $row->getId(),
  1827. '1' => $row->getName(),
  1828. '2' => $row->getDescription(),
  1829. '3' => $row->getId()
  1830. ];
  1831. }
  1832. return $list;
  1833. }
  1834. /**
  1835. * @return array
  1836. */
  1837. public static function getStatusSimple()
  1838. {
  1839. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->findAll();
  1840. $list = [];
  1841. /** @var Project $row */
  1842. foreach ($projects as $row) {
  1843. $list[] = [
  1844. 'id' => $row->getId(),
  1845. '0' => $row->getId(),
  1846. '1' => Display::url($row->getName()),
  1847. '2' => $row->getDescription()
  1848. ];
  1849. }
  1850. return $list;
  1851. }
  1852. /**
  1853. * @return int
  1854. */
  1855. public static function getStatusCount()
  1856. {
  1857. $count = Database::getManager()->getRepository('ChamiloTicketBundle:Status')->createQueryBuilder('p')
  1858. ->select('COUNT(p.id)')
  1859. ->getQuery()
  1860. ->getSingleScalarResult();
  1861. return $count;
  1862. }
  1863. /**
  1864. * @param array $params
  1865. */
  1866. public static function addStatus($params)
  1867. {
  1868. $item = new Status();
  1869. $item->setCode(URLify::filter($params['name']));
  1870. $item->setName($params['name']);
  1871. $item->setDescription($params['description']);
  1872. Database::getManager()->persist($item);
  1873. Database::getManager()->flush();
  1874. }
  1875. /**
  1876. * @param $id
  1877. * @return Project
  1878. */
  1879. public static function getStatus($id)
  1880. {
  1881. return Database::getManager()->getRepository('ChamiloTicketBundle:Status')->find($id);
  1882. }
  1883. /**
  1884. * @param int $id
  1885. * @param array $params
  1886. */
  1887. public static function updateStatus($id, $params)
  1888. {
  1889. $item = self::getStatus($id);
  1890. $item->setName($params['name']);
  1891. $item->setDescription($params['description']);
  1892. Database::getManager()->merge($item);
  1893. Database::getManager()->flush();
  1894. }
  1895. /**
  1896. * @param int $id
  1897. */
  1898. public static function deleteStatus($id)
  1899. {
  1900. $item = self::getStatus($id);
  1901. if ($item) {
  1902. Database::getManager()->remove($item);
  1903. Database::getManager()->flush();
  1904. }
  1905. }
  1906. /**
  1907. * @param string $url
  1908. * @return FormValidator
  1909. */
  1910. public static function getStatusForm($url)
  1911. {
  1912. $form = new FormValidator('status', 'post', $url);
  1913. $form->addText('name', get_lang('Name'));
  1914. $form->addHtmlEditor('description', get_lang('Description'));
  1915. $form->addButtonUpdate(get_lang('Save'));
  1916. return $form;
  1917. }
  1918. /**
  1919. * @return array
  1920. */
  1921. public static function getPriorityAdminList()
  1922. {
  1923. $items = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->findAll();
  1924. $list = [];
  1925. /** @var Status $row */
  1926. foreach ($items as $row) {
  1927. $list[] = [
  1928. 'id' => $row->getId(),
  1929. 'code' => $row->getCode(),
  1930. '0' => $row->getId(),
  1931. '1' => $row->getName(),
  1932. '2' => $row->getDescription(),
  1933. '3' => $row->getId()
  1934. ];
  1935. }
  1936. return $list;
  1937. }
  1938. /**
  1939. * @return array
  1940. */
  1941. public static function getPrioritySimple()
  1942. {
  1943. $projects = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->findAll();
  1944. $list = [];
  1945. /** @var Priority $row */
  1946. foreach ($projects as $row) {
  1947. $list[] = [
  1948. 'id' => $row->getId(),
  1949. '0' => $row->getId(),
  1950. '1' => Display::url($row->getName()),
  1951. '2' => $row->getDescription()
  1952. ];
  1953. }
  1954. return $list;
  1955. }
  1956. /**
  1957. * @return int
  1958. */
  1959. public static function getPriorityCount()
  1960. {
  1961. $count = Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->createQueryBuilder('p')
  1962. ->select('COUNT(p.id)')
  1963. ->getQuery()
  1964. ->getSingleScalarResult();
  1965. return $count;
  1966. }
  1967. /**
  1968. * @param array $params
  1969. */
  1970. public static function addPriority($params)
  1971. {
  1972. $item = new Priority();
  1973. $item
  1974. ->setCode(URLify::filter($params['name']))
  1975. ->setName($params['name'])
  1976. ->setDescription($params['description'])
  1977. ->setColor('')
  1978. ->setInsertUserId(api_get_user_id())
  1979. ->setUrgency('')
  1980. ;
  1981. Database::getManager()->persist($item);
  1982. Database::getManager()->flush();
  1983. }
  1984. /**
  1985. * @param $id
  1986. * @return Priority
  1987. */
  1988. public static function getPriority($id)
  1989. {
  1990. return Database::getManager()->getRepository('ChamiloTicketBundle:Priority')->find($id);
  1991. }
  1992. /**
  1993. * @param int $id
  1994. * @param array $params
  1995. */
  1996. public static function updatePriority($id, $params)
  1997. {
  1998. $item = self::getPriority($id);
  1999. $item->setName($params['name']);
  2000. $item->setDescription($params['description']);
  2001. Database::getManager()->merge($item);
  2002. Database::getManager()->flush();
  2003. }
  2004. /**
  2005. * @param int $id
  2006. */
  2007. public static function deletePriority($id)
  2008. {
  2009. $item = self::getPriority($id);
  2010. if ($item) {
  2011. Database::getManager()->remove($item);
  2012. Database::getManager()->flush();
  2013. }
  2014. }
  2015. /**
  2016. * @param string $url
  2017. * @return FormValidator
  2018. */
  2019. public static function getPriorityForm($url)
  2020. {
  2021. $form = new FormValidator('priority', 'post', $url);
  2022. $form->addText('name', get_lang('Name'));
  2023. $form->addHtmlEditor('description', get_lang('Description'));
  2024. $form->addButtonUpdate(get_lang('Save'));
  2025. return $form;
  2026. }
  2027. /**
  2028. * @return string
  2029. */
  2030. public static function getSettingsMenu()
  2031. {
  2032. $items = [
  2033. [
  2034. 'url' => 'projects.php',
  2035. 'content' => get_lang('Projects')
  2036. ],
  2037. [
  2038. 'url' => 'status.php',
  2039. 'content' => get_lang('Status')
  2040. ],
  2041. [
  2042. 'url' => 'priorities.php',
  2043. 'content' => get_lang('Priority')
  2044. ]
  2045. ];
  2046. echo Display::actions($items);
  2047. }
  2048. /**
  2049. * @return array
  2050. */
  2051. public static function getDefaultStatusList()
  2052. {
  2053. return [
  2054. self::STATUS_NEW,
  2055. self::STATUS_PENDING,
  2056. self::STATUS_UNCONFIRMED,
  2057. self::STATUS_CLOSE,
  2058. self::STATUS_FORWARDED
  2059. ];
  2060. }
  2061. /**
  2062. * @return array
  2063. */
  2064. public static function getDefaultPriorityList()
  2065. {
  2066. return [
  2067. self::PRIORITY_NORMAL,
  2068. self::PRIORITY_HIGH,
  2069. self::PRIORITY_LOW,
  2070. self::STATUS_CLOSE,
  2071. self::STATUS_FORWARDED
  2072. ];
  2073. }
  2074. /**
  2075. * Deletes the user from all the ticket system
  2076. * @param int $userId
  2077. */
  2078. public static function deleteUserFromTicketSystem($userId)
  2079. {
  2080. $userId = (int) $userId;
  2081. $schema = Database::getManager()->getConnection()->getSchemaManager();
  2082. if ($schema->tablesExist('ticket_assigned_log')) {
  2083. $sql = "UPDATE ticket_assigned_log SET user_id = NULL WHERE user_id = $userId";
  2084. Database::query($sql);
  2085. $sql = "UPDATE ticket_assigned_log SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2086. Database::query($sql);
  2087. }
  2088. if ($schema->tablesExist('ticket_ticket')) {
  2089. $sql = "UPDATE ticket_ticket SET assigned_last_user = NULL WHERE assigned_last_user = $userId";
  2090. Database::query($sql);
  2091. $sql = "UPDATE ticket_ticket SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2092. Database::query($sql);
  2093. $sql = "UPDATE ticket_ticket SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2094. Database::query($sql);
  2095. }
  2096. if ($schema->tablesExist('ticket_category')) {
  2097. $sql = "UPDATE ticket_category SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2098. Database::query($sql);
  2099. $sql = "UPDATE ticket_category SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2100. Database::query($sql);
  2101. }
  2102. if ($schema->tablesExist('ticket_category_rel_user')) {
  2103. $sql = "DELETE FROM ticket_category_rel_user WHERE user_id = $userId";
  2104. Database::query($sql);
  2105. }
  2106. if ($schema->tablesExist('ticket_message')) {
  2107. $sql = "UPDATE ticket_message SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2108. Database::query($sql);
  2109. $sql = "UPDATE ticket_message SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2110. Database::query($sql);
  2111. }
  2112. if ($schema->tablesExist('ticket_message_attachments')) {
  2113. $sql = "UPDATE ticket_message_attachments SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2114. Database::query($sql);
  2115. $sql = "UPDATE ticket_message_attachments SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2116. Database::query($sql);
  2117. }
  2118. if ($schema->tablesExist('ticket_priority')) {
  2119. $sql = "UPDATE ticket_priority SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2120. Database::query($sql);
  2121. $sql = "UPDATE ticket_priority SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2122. Database::query($sql);
  2123. }
  2124. if ($schema->tablesExist('ticket_project')) {
  2125. $sql = "UPDATE ticket_project SET sys_insert_user_id = NULL WHERE sys_insert_user_id = $userId";
  2126. Database::query($sql);
  2127. $sql = "UPDATE ticket_project SET sys_lastedit_user_id = NULL WHERE sys_lastedit_user_id = $userId";
  2128. Database::query($sql);
  2129. }
  2130. }
  2131. }