import_csv.php 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602
  1. <?php
  2. if (PHP_SAPI !='cli') {
  3. die('Run this script through the command line or comment this line in the code');
  4. }
  5. require_once __DIR__.'/../inc/global.inc.php';
  6. require_once api_get_path(LIBRARY_PATH).'log.class.php';
  7. /**
  8. * Class ImportCsv
  9. */
  10. class ImportCsv
  11. {
  12. private $logger;
  13. private $dumpValues;
  14. public $test;
  15. public $defaultLanguage = 'dutch';
  16. public $extraFieldIdNameList = array(
  17. 'session' => 'external_session_id',
  18. 'course' => 'external_course_id',
  19. 'user' => 'external_user_id',
  20. );
  21. /**
  22. * @param Logger $logger
  23. */
  24. public function __construct($logger)
  25. {
  26. $this->logger = $logger;
  27. }
  28. /**
  29. * @param bool $dump
  30. */
  31. function setDumpValues($dump)
  32. {
  33. $this->dumpValues = $dump;
  34. }
  35. function getDumpValues()
  36. {
  37. return $this->dumpValues;
  38. }
  39. /**
  40. * Runs the import process
  41. */
  42. public function run()
  43. {
  44. $path = api_get_path(SYS_CODE_PATH).'cron/incoming/';
  45. if (!is_dir($path)) {
  46. echo "The folder! $path does not exits";
  47. exit;
  48. }
  49. if ($this->getDumpValues()) {
  50. $this->dumpDatabaseTables();
  51. }
  52. echo "Starting with reading the files: ".PHP_EOL.PHP_EOL;
  53. $files = scandir($path);
  54. $fileToProcess = array();
  55. if (!empty($files)) {
  56. foreach ($files as $file) {
  57. $fileInfo = pathinfo($file);
  58. if ($fileInfo['extension'] == 'csv') {
  59. // teachers_yyyymmdd.csv, courses_yyyymmdd.csv, students_yyyymmdd.csv and sessions_yyyymmdd.csv
  60. $parts = explode('_', $fileInfo['filename']);
  61. $method = 'import'.ucwords($parts[1]);
  62. if (method_exists($this, $method)) {
  63. $fileToProcess[$parts[1]][] = array(
  64. 'method' => $method,
  65. 'file' => $path.$fileInfo['basename']
  66. );
  67. //$this->$method($path.$fileInfo['basename']);
  68. } else {
  69. echo "Error - This file '$file' can't be processed.".PHP_EOL;
  70. echo "The file have to has this format:".PHP_EOL;
  71. echo "prefix_students_ddmmyyyy.csv, prefix_teachers_ddmmyyyy.csv, prefix_courses_ddmmyyyy.csv, prefix_sessions_ddmmyyyy.csv ".PHP_EOL;
  72. exit;
  73. }
  74. }
  75. }
  76. if (empty($fileToProcess)) {
  77. echo 'Error - no files to process.';
  78. exit;
  79. }
  80. $sections = array('students', 'teachers', 'courses', 'sessions');
  81. //$sections = array('sessions');
  82. $this->prepareImport();
  83. foreach ($sections as $section) {
  84. $this->logger->addInfo("-- Import $section --");
  85. if (isset($fileToProcess[$section]) && !empty($fileToProcess[$section])) {
  86. $files = $fileToProcess[$section];
  87. foreach ($files as $fileInfo) {
  88. $method = $fileInfo['method'];
  89. $file = $fileInfo['file'];
  90. echo 'Reading file: '.$file.PHP_EOL;
  91. $this->logger->addInfo("Reading file: $file");
  92. $this->$method($file);
  93. }
  94. }
  95. }
  96. }
  97. }
  98. private function prepareImport()
  99. {
  100. // Create user extra field: extra_external_user_id
  101. UserManager::create_extra_field($this->extraFieldIdNameList['user'], 1, 'External user id', null);
  102. // Create course extra field: extra_external_course_id
  103. CourseManager::create_course_extra_field($this->extraFieldIdNameList['course'], 1, 'External course id');
  104. // Create session extra field extra_external_session_id
  105. SessionManager::create_session_extra_field($this->extraFieldIdNameList['session'], 1, 'External session id');
  106. }
  107. /**
  108. * @param string $file
  109. */
  110. function moveFile($file)
  111. {
  112. $moved = str_replace('incoming', 'treated', $file);
  113. if ($this->test) {
  114. $result = 1;
  115. } else {
  116. $result = rename($file, $moved);
  117. }
  118. if ($result) {
  119. $this->logger->addInfo("Moving file to the treated folder: $file");
  120. } else {
  121. $this->logger->addError("Error - Cant move file to the treated folder: $file");
  122. }
  123. }
  124. /**
  125. * @param array $row
  126. *
  127. * @return array
  128. */
  129. private function cleanUserRow($row)
  130. {
  131. $row['lastname'] = $row['LastName'];
  132. $row['firstname'] = $row['FirstName'];
  133. $row['email'] = $row['Email'];
  134. $row['username'] = $row['UserName'];
  135. $row['password'] = $row['Password'];
  136. $row['auth_source'] = $row['AuthSource'];
  137. $row['official_code'] = $row['OfficialCode'];
  138. $row['phone'] = $row['PhoneNumber'];
  139. if (isset($row['StudentID'])) {
  140. $row['extra_'.$this->extraFieldIdNameList['user']] = $row['StudentID'];
  141. }
  142. if (isset($row['TeacherID'])) {
  143. $row['extra_'.$this->extraFieldIdNameList['user']] = $row['TeacherID'];
  144. }
  145. //$row['lastname'] = Status
  146. return $row;
  147. }
  148. /**
  149. * @param array $row
  150. * @return array
  151. */
  152. private function cleanCourseRow($row)
  153. {
  154. $row['title'] = $row['Title'];
  155. $row['course_code'] = $row['Code'];
  156. $row['course_category'] = $row['CourseCategory'];
  157. $row['email'] = $row['Teacher'];
  158. $row['language'] = $row['Language'];
  159. if (isset($row['CourseID'])) {
  160. $row['extra_'.$this->extraFieldIdNameList['course']] = $row['CourseID'];
  161. }
  162. return $row;
  163. }
  164. /**
  165. * File to import
  166. * @param string $file
  167. */
  168. private function importTeachers($file)
  169. {
  170. $data = Import::csv_to_array($file);
  171. /* Unique identifier: official-code username.
  172. Email address and password should never get updated. *ok
  173. The only fields that I can think of that should update if the data changes in the csv file are FirstName and LastName. *ok
  174. A slight edit of these fields should be taken into account. ???
  175. Adding teachers is no problem, but deleting them shouldn’t be automated, but we should get a log of “to delete teachers”.
  176. We’ll handle that manually if applicable.
  177. No delete!
  178. */
  179. $language = $this->defaultLanguage;
  180. if (!empty($data)) {
  181. $this->logger->addInfo(count($data)." records found.");
  182. foreach ($data as $row) {
  183. $row = $this->cleanUserRow($row);
  184. $user_id = UserManager::get_user_id_from_original_id($row['extra_'.$this->extraFieldIdNameList['user']], $this->extraFieldIdNameList['user']);
  185. $userInfo = api_get_user_info($user_id);
  186. //$userInfo = api_get_user_info_from_username($row['username']);
  187. $userInfoByOfficialCode = api_get_user_info_from_official_code($row['official_code']);
  188. if (empty($userInfo) && empty($userInfoByOfficialCode)) {
  189. // Create user
  190. $userId = UserManager::create_user(
  191. $row['firstname'],
  192. $row['lastname'],
  193. COURSEMANAGER,
  194. $row['email'],
  195. $row['username'],
  196. $row['password'],
  197. $row['official_code'],
  198. $language, //$row['language'],
  199. $row['phone'],
  200. null, //$row['picture'], //picture
  201. PLATFORM_AUTH_SOURCE, // ?
  202. '0000-00-00 00:00:00', //$row['expiration_date'], //$expiration_date = '0000-00-00 00:00:00',
  203. 1, //active
  204. 0,
  205. null, // extra
  206. null, //$encrypt_method = '',
  207. false //$send_mail = false
  208. );
  209. if ($userId) {
  210. foreach ($row as $key => $value) {
  211. if (substr($key, 0, 6) == 'extra_') { //an extra field
  212. UserManager::update_extra_field_value($userId, substr($key, 6), $value);
  213. }
  214. }
  215. $this->logger->addInfo("Teachers - User created: ".$row['username']);
  216. } else {
  217. $this->logger->addError("Teachers - User NOT created: ".$row['username']." ".$row['firstname']." ".$row['lastname']);
  218. }
  219. } else {
  220. if (empty($userInfo)) {
  221. $this->logger->addError("Teachers - Can't update user :".$row['username']);
  222. continue;
  223. }
  224. // Update user
  225. $result = UserManager::update_user(
  226. $userInfo['user_id'],
  227. $row['firstname'], // <<-- changed
  228. $row['lastname'], // <<-- changed
  229. $userInfo['username'],
  230. null, //$password = null,
  231. $auth_source = null,
  232. $userInfo['email'],
  233. COURSEMANAGER,
  234. $userInfo['official_code'],
  235. $userInfo['phone'],
  236. $userInfo['picture_uri'],
  237. $userInfo['expiration_date'],
  238. $userInfo['active'],
  239. null, //$creator_id = null,
  240. 0, //$hr_dept_id = 0,
  241. null, // $extra = null,
  242. null, //$language = 'english',
  243. null, //$encrypt_method = '',
  244. false, //$send_email = false,
  245. 0 //$reset_password = 0
  246. );
  247. if ($result) {
  248. foreach ($row as $key => $value) {
  249. if (substr($key, 0, 6) == 'extra_') { //an extra field
  250. UserManager::update_extra_field_value($userInfo['user_id'], substr($key, 6), $value);
  251. }
  252. }
  253. $this->logger->addInfo("Teachers - User updated: ".$row['username']);
  254. } else {
  255. $this->logger->addError("Teachers - User not updated: ".$row['username']);
  256. }
  257. }
  258. }
  259. }
  260. $this->moveFile($file);
  261. }
  262. /**
  263. * @param string $file
  264. */
  265. private function importStudents($file)
  266. {
  267. $data = Import::csv_to_array($file);
  268. /*
  269. * Another users import.
  270. Unique identifier: official code and username . ok
  271. Username and password should never get updated. ok
  272. If an update should need to occur (because it changed in the .csv), we’ll want that logged. We will handle this manually in that case.
  273. All other fields should be updateable, though passwords should of course not get updated. ok
  274. If a user gets deleted (not there anymore),
  275. He should be set inactive one year after the current date. So I presume you’ll just update the expiration date. We want to grant access to courses up to a year after deletion.
  276. */
  277. if (!empty($data)) {
  278. $language = $this->defaultLanguage;
  279. $this->logger->addInfo(count($data)." records found.");
  280. foreach ($data as $row) {
  281. $row = $this->cleanUserRow($row);
  282. //$userInfo = api_get_user_info_from_username($row['username']);
  283. $user_id = UserManager::get_user_id_from_original_id($row['extra_'.$this->extraFieldIdNameList['user']], $this->extraFieldIdNameList['user']);
  284. $userInfo = array();
  285. if (!empty($user_id)) {
  286. $userInfo = api_get_user_info($user_id);
  287. }
  288. $userInfoByOfficialCode = api_get_user_info_from_official_code($row['official_code']);
  289. if (empty($userInfo) && empty($userInfoByOfficialCode)) {
  290. // Create user
  291. $result = UserManager::create_user(
  292. $row['firstname'],
  293. $row['lastname'],
  294. STUDENT,
  295. $row['email'],
  296. $row['username'],
  297. $row['password'],
  298. $row['official_code'],
  299. $language, //$row['language'],
  300. $row['phone'],
  301. null, //$row['picture'], //picture
  302. PLATFORM_AUTH_SOURCE, // ?
  303. '0000-00-00 00:00:00', //$row['expiration_date'], //$expiration_date = '0000-00-00 00:00:00',
  304. 1, //active
  305. 0,
  306. null, // extra
  307. null, //$encrypt_method = '',
  308. false //$send_mail = false
  309. );
  310. if ($result) {
  311. foreach ($row as $key => $value) {
  312. if (substr($key, 0, 6) == 'extra_') { //an extra field
  313. UserManager::update_extra_field_value($result, substr($key, 6), $value);
  314. }
  315. }
  316. $this->logger->addInfo("Students - User created: ".$row['username']);
  317. } else {
  318. $this->logger->addError("Students - User NOT created: ".$row['username']." ".$row['firstname']." ".$row['lastname']);
  319. }
  320. } else {
  321. if (empty($userInfo)) {
  322. $this->logger->addError("Students - Can't update user :".$row['username']);
  323. continue;
  324. }
  325. if ($row['action'] == 'delete') {
  326. // INactive one year later
  327. $userInfo['expiration_date'] = api_get_utc_datetime(api_strtotime(time() + 365*24*60*60));
  328. }
  329. // Update user
  330. $result = UserManager::update_user(
  331. $userInfo['user_id'],
  332. $row['firstname'], // <<-- changed
  333. $row['lastname'], // <<-- changed
  334. $userInfo['username'],
  335. null, //$password = null,
  336. $auth_source = null,
  337. $userInfo['email'],
  338. STUDENT,
  339. $userInfo['official_code'],
  340. $userInfo['phone'],
  341. $userInfo['picture_uri'],
  342. $userInfo['expiration_date'],
  343. $userInfo['active'],
  344. null, //$creator_id = null,
  345. 0, //$hr_dept_id = 0,
  346. null, // $extra = null,
  347. null, //$language = 'english',
  348. null, //$encrypt_method = '',
  349. false, //$send_email = false,
  350. 0 //$reset_password = 0
  351. );
  352. if ($result) {
  353. foreach ($row as $key => $value) {
  354. if (substr($key, 0, 6) == 'extra_') { //an extra field
  355. UserManager::update_extra_field_value($userInfo['user_id'], substr($key, 6), $value);
  356. }
  357. }
  358. $this->logger->addInfo("Students - User updated: ".$row['username']);
  359. } else {
  360. $this->logger->addError("Students - User NOT updated: ".$row['username']." ".$row['firstname']." ".$row['lastname']);
  361. }
  362. }
  363. }
  364. }
  365. $this->moveFile($file);
  366. }
  367. /**
  368. * @param string $file
  369. */
  370. private function importCourses($file)
  371. {
  372. $data = Import::csv_to_array($file);
  373. //$language = $this->defaultLanguage;
  374. if (!empty($data)) {
  375. $this->logger->addInfo(count($data)." records found.");
  376. foreach ($data as $row) {
  377. $row = $this->cleanCourseRow($row);
  378. $courseCode = CourseManager::get_course_id_from_original_id($row['extra_'.$this->extraFieldIdNameList['course']], $this->extraFieldIdNameList['course']);
  379. //$courseInfo = api_get_course_info($row['course_code']);
  380. $courseInfo = api_get_course_info($courseCode);
  381. if (empty($courseInfo)) {
  382. // Create
  383. $params = array();
  384. $params['title'] = $row['title'];
  385. $params['exemplary_content'] = false;
  386. $params['wanted_code'] = $row['course_code'];
  387. $params['course_category'] = $row['course_category'];
  388. $params['course_language'] = $row['language'];
  389. $courseInfo = CourseManager::create_course($params);
  390. if (!empty($courseInfo)) {
  391. CourseManager::update_course_extra_field_value($courseInfo['code'], 'external_course_id', $row['extra_'.$this->extraFieldIdNameList['course']]);
  392. $this->logger->addInfo("Courses - Course created ".$courseInfo['code']);
  393. } else {
  394. $this->logger->addError("Courses - Can't create course:".$row['title']);
  395. }
  396. } else {
  397. // Update
  398. $params = array(
  399. 'title' => $row['title'],
  400. );
  401. $result = CourseManager::update_attributes($courseInfo['real_id'], $params);
  402. if ($result) {
  403. $this->logger->addInfo("Courses - Course updated ".$courseInfo['code']);
  404. } else {
  405. $this->logger->addError("Courses - Course NOT updated ".$courseInfo['code']);
  406. }
  407. }
  408. }
  409. }
  410. $this->moveFile($file);
  411. }
  412. /**
  413. * @param string $file
  414. */
  415. private function importSessions($file)
  416. {
  417. $result = SessionManager::importCSV(
  418. $file,
  419. true,
  420. 1,
  421. $this->logger,
  422. array('SessionID' => 'extra_'.$this->extraFieldIdNameList['session']),
  423. $this->extraFieldIdNameList['session']
  424. );
  425. if (!empty($result['error_message'])) {
  426. $this->logger->addError($result['error_message']);
  427. }
  428. $this->logger->addInfo("Sessions - Sessions parsed: ".$result['session_counter']);
  429. $this->moveFile($file);
  430. }
  431. private function dumpDatabaseTables()
  432. {
  433. echo 'Dumping tables'.PHP_EOL;
  434. // User
  435. $table = Database::get_main_table(TABLE_MAIN_USER);
  436. $tableAdmin = Database::get_main_table(TABLE_MAIN_ADMIN);
  437. //$sql = "DELETE FROM $table WHERE username NOT IN ('admin') AND lastname <> 'Anonymous' ";
  438. $sql = "DELETE FROM $table where user_id not in (select user_id from $tableAdmin) and status <> ".ANONYMOUS;
  439. Database::query($sql);
  440. echo $sql.PHP_EOL;
  441. // Course
  442. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  443. $sql = "DELETE FROM $table";
  444. Database::query($sql);
  445. echo $sql.PHP_EOL;
  446. $table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  447. $sql = "DELETE FROM $table";
  448. Database::query($sql);
  449. echo $sql.PHP_EOL;
  450. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  451. $sql = "DELETE FROM $table";
  452. Database::query($sql);
  453. echo $sql.PHP_EOL;
  454. // Sessions
  455. $table = Database::get_main_table(TABLE_MAIN_SESSION);
  456. $sql = "DELETE FROM $table";
  457. Database::query($sql);
  458. echo $sql.PHP_EOL;
  459. $table = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
  460. $sql = "DELETE FROM $table";
  461. Database::query($sql);
  462. echo $sql.PHP_EOL;
  463. $table = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  464. $sql = "DELETE FROM $table";
  465. Database::query($sql);
  466. echo $sql.PHP_EOL;
  467. $table = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  468. $sql = "DELETE FROM $table";
  469. Database::query($sql);
  470. echo $sql.PHP_EOL;
  471. // Extra fields
  472. $table = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
  473. $sql = "DELETE FROM $table";
  474. Database::query($sql);
  475. echo $sql.PHP_EOL;
  476. $table = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  477. $sql = "DELETE FROM $table";
  478. Database::query($sql);
  479. echo $sql.PHP_EOL;
  480. $table = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  481. $sql = "DELETE FROM $table";
  482. Database::query($sql);
  483. echo $sql.PHP_EOL;
  484. }
  485. }
  486. use Monolog\Logger;
  487. use Monolog\Handler\StreamHandler;
  488. use Monolog\Handler\NativeMailerHandler;
  489. use Monolog\Handler\RotatingFileHandler;
  490. use Monolog\Handler\BufferHandler;
  491. $logger = new Logger('cron');
  492. $emails = isset($_configuration['cron_notification_mails']) ? $_configuration['cron_notification_mails'] : null;
  493. $minLevel = Logger::DEBUG;
  494. if (!is_array($emails)) {
  495. $emails = array($emails);
  496. }
  497. $subject = "Cron main/cron/import_csv.php ".date('Y-m-d h:i:s');
  498. $from = api_get_setting('emailAdministrator');
  499. if (!empty($emails)) {
  500. foreach ($emails as $email) {
  501. $stream = new NativeMailerHandler($email, $subject, $from, $minLevel);
  502. $logger->pushHandler(new BufferHandler($stream, 0, $minLevel));
  503. }
  504. }
  505. $stream = new StreamHandler(api_get_path(SYS_ARCHIVE_PATH).'import_csv.log', $minLevel);
  506. $logger->pushHandler(new BufferHandler($stream, 0, $minLevel));
  507. $logger->pushHandler(new RotatingFileHandler('import_csv', 5, $minLevel));
  508. $import = new ImportCsv($logger);
  509. $dump = false;
  510. if (isset($argv[1]) && $argv[1] = '--dump') {
  511. $dump = true;
  512. }
  513. $import->setDumpValues($dump);
  514. // Do not moves the files to treated
  515. $import->test = true;
  516. $import->run();