remind_course_expiration.php 5.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * Course expiration reminder.
  5. * @package chamilo.cron
  6. * @author Imanol Losada <imanol.losada@beeznest.com>
  7. */
  8. require_once __DIR__ . '/../inc/global.inc.php';
  9. /**
  10. * Initialization
  11. */
  12. if (php_sapi_name() != 'cli') {
  13. exit; //do not run from browser
  14. }
  15. $isActive = api_get_setting('cron_remind_course_expiration_activate') === 'true';
  16. if (!$isActive) {
  17. exit;
  18. }
  19. $frequency = api_get_setting('cron_remind_course_expiration_frequency');
  20. // Days before expiration date to send reminders
  21. $today = gmdate("Y-m-d");
  22. $expirationDate = gmdate("Y-m-d", strtotime("$today + $frequency day"));
  23. $gradebookTable = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY);
  24. $certificateTable = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
  25. $sessionTable = Database::get_main_table(TABLE_MAIN_SESSION);
  26. $sessionUserTable = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  27. $query = "
  28. SELECT DISTINCT category.session_id, certificate.user_id
  29. FROM $gradebookTable AS category
  30. LEFT JOIN $certificateTable AS certificate
  31. ON category.id = certificate.cat_id
  32. INNER JOIN $sessionTable AS session
  33. ON category.session_id = session.id
  34. WHERE
  35. session.access_end_date BETWEEN '$today' AND
  36. '$expirationDate' AND
  37. category.session_id IS NOT NULL";
  38. $sessionId = 0;
  39. $userIds = array();
  40. $sessions = array();
  41. $result = Database::query($query);
  42. $urlSessionTable = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  43. $urlTable = Database::get_main_table(TABLE_MAIN_ACCESS_URL);
  44. while ($row = Database::fetch_array($result)) {
  45. if ($sessionId != $row['session_id']) {
  46. $sessionId = $row['session_id'];
  47. $userIds = array();
  48. }
  49. if (!is_null($row['user_id'])) {
  50. array_push($userIds, $row['user_id']);
  51. }
  52. $sessions[$sessionId] = $userIds;
  53. }
  54. $usersToBeReminded = array();
  55. foreach ($sessions as $sessionId => $userIds) {
  56. $userId = 0;
  57. $userIds = $userIds ? " AND sessionUser.user_id NOT IN (" . implode(", ", $userIds) . ")" : null;
  58. $query = "
  59. SELECT sessionUser.session_id, sessionUser.user_id, session.name, session.access_end_date
  60. FROM $sessionUserTable AS sessionUser
  61. INNER JOIN $sessionTable AS session
  62. ON sessionUser.session_id = session.id
  63. WHERE
  64. session_id = $sessionId$userIds";
  65. $result = Database::query($query);
  66. while ($row = Database::fetch_array($result)) {
  67. $usersToBeReminded[$row['user_id']][$row['session_id']] = array(
  68. 'name' => $row['name'],
  69. 'access_end_date' => $row['access_end_date']
  70. );
  71. }
  72. }
  73. if ($usersToBeReminded) {
  74. $today = date_create($today);
  75. $administrator = array(
  76. 'completeName' => api_get_person_name(
  77. api_get_setting("administratorName"),
  78. api_get_setting("administratorSurname"),
  79. null,
  80. PERSON_NAME_EMAIL_ADDRESS
  81. ),
  82. 'email' => api_get_setting("emailAdministrator")
  83. );
  84. echo "\n======================================================================\n\n";
  85. foreach ($usersToBeReminded as $userId => $sessions) {
  86. $user = api_get_user_info($userId);
  87. $userCompleteName = api_get_person_name(
  88. $user['firstname'],
  89. $user['lastname'],
  90. null,
  91. PERSON_NAME_EMAIL_ADDRESS
  92. );
  93. foreach ($sessions as $sessionId => $session) {
  94. $daysRemaining = date_diff($today, date_create($session['access_end_date']));
  95. $join = " INNER JOIN $urlSessionTable ON id = access_url_id";
  96. $result = Database::select(
  97. 'url',
  98. "$urlTable $join",
  99. array(
  100. 'where' => array(
  101. 'session_id = ?' => array(
  102. $sessionId
  103. )
  104. ),
  105. 'limit' => '1'
  106. )
  107. );
  108. $subjectTemplate = new Template(null, false, false, false, false, false);
  109. $subjectTemplate->assign('session_name', $session['name']);
  110. $subjectTemplate->assign(
  111. 'session_access_end_date',
  112. $session['access_end_date']
  113. );
  114. $subjectTemplate->assign(
  115. 'remaining_days',
  116. $daysRemaining->format("%d")
  117. );
  118. $subjectLayout = $subjectTemplate->get_template(
  119. 'mail/cron_remind_course_expiration_subject.tpl'
  120. );
  121. $bodyTemplate = new Template(null, false, false, false, false, false);
  122. $bodyTemplate->assign('complete_user_name', $userCompleteName);
  123. $bodyTemplate->assign('session_name', $session['name']);
  124. $bodyTemplate->assign(
  125. 'session_access_end_date',
  126. $session['access_end_date']
  127. );
  128. $bodyTemplate->assign(
  129. 'remaining_days',
  130. $daysRemaining->format("%d")
  131. );
  132. $bodyLayout = $bodyTemplate->get_template(
  133. 'mail/cron_remind_course_expiration_body.tpl'
  134. );
  135. api_mail_html(
  136. $userCompleteName,
  137. $user['email'],
  138. $subjectTemplate->fetch($subjectLayout),
  139. $bodyTemplate->fetch($bodyLayout),
  140. $administrator['completeName'],
  141. $administrator['email']
  142. );
  143. echo "Email sent to $userCompleteName (" . $user['email'] . ")\n";
  144. echo "Session: " . $session['name'] . "\n";
  145. echo "Date end: " . $session['access_end_date'] . "\n";
  146. echo "Days remaining: " . $daysRemaining->format("%d") . "\n\n";
  147. }
  148. echo "======================================================================\n\n";
  149. }
  150. } else {
  151. echo "No users to be reminded\n";
  152. }