• Демо +38(067)217-0440
    Назад

    API Collaborator. Зачетная книжка сотрудника

    Дата публикации: 15.01.2019
    Просмотры: 1028

    В прошлой статье мы рассмотрели как с помощью API Collaborator получать список людей, которые пользовались учебным порталом компании в заданный период. Теперь предлагаю рассмотреть реальный кейс.

    Худенко Наталья, Руководитель Центра развития персонала производства, «Юрия-Фарм».
    У них достаточно часто возникала потребность печати «Зачетной книжки» сотрудника. Важно было получить выборку за указанный период всех назначенных пользователю учебных заданий и статус обучения по ним — что пройдено, с каким результатом, а что в процессе. Результат нужен в электронной таблице, приятно оформленным и подготовленным к печати. Вот что примерно требовалось:
    blank

    Как будем решать

    Сделаем копию электронной таблицы, которая получает выборку пользователей, и используем ее как основу. Этот список будет источником данных для выбора сотрудника для которого нужно сформировать Зачетную книжку.
    Преименуем лист «main» в «users» и добавим новый лист,  ему дадим главное имя — «main» — он будет содержать результирующую таблицу данных. Оформим его, заполним примерами данных. На нем же разместим элементы формы запроса — поля для указания дат и выбора  сотрудника плюс кнопки запуска обработки данных. У меня получилось вот так:
    blank

    В коде скрипта main.gs выполним соотвествующие замены:

    • добавим в main.gs переменную var SHEET_USERS = SPREADSHEET.getSheetByName("users");
    • заменим во всем коде SHEET_MAIN на SHEET_USERS.

    blank

    • заменим название функции function main() на function getUsersList()
    • заменим объявление переменных, которые хранят даты периода
    var date_period_begin = SHEET_USERS.getRange('D1').getValue();
var date_period_end = SHEET_USERS.getRange('D2').getValue();
// заменим на..
var date_period_begin = SHEET_MAIN.getRange('D3').getValue();
var date_period_end = SHEET_MAIN.getRange('E3').getValue();

    После этого желательно проверить — все ли работает? Запустим функцию getUsersList() и проверим, что список пользователей обновляется без ошибок.
    Планируемая последовательность действий пользователя по работе с «Зачетной книжкой»:
    1. Пользователь указывает период выборки данных в ячейках подписанных «Период обучения от — до».
    2. Нажимает кнопку «Обновить список пользователей». При этом запускается код, что был описан в предыдущей статье. Он обновляет список пользователей на листе «users». Этот список является источником данных для выпадающего списка «Сотрудник».

    Про то как..

    3. Выбирает из списка «Сотрудник» нужного человека  и нажимает кнопку «Заполнить книжку».
    4. «Заполнить книжку» выполнит запрос на данные про выбранного человека, очистит книжку и заполнит ее результатами сотрудника по заданиям, которые попадают в указанный временной период.
    Теперь заставим это все работать…

    Готовим REST запросы

    Нам потребуются 2 запроса:
    — на данные выбранного пользователя (Имя, Фамилия, Должность, Подразделение, Фото).  Все о пользователе отдает запрос к его профилю:

    /api/rest.php/auth/users/<user_id>

    — на выборку данных по заданиям из его «Истории обучения»:

    /api/rest.php/tasks?page=1&count=<records_limit>&filter[date_assign]=<date_period_begin>&filter[date_assign]=<date_period_end>&sorting[date_assign]=desc&user_id=<user_id>&action=get-study-history-all-tasks

    В этих запросах:

    • <records_limit> — количество записей на странице выдачи результата;
    • <date_period_begin> и <date_period_end> — даты периода в формате "YYYY-MM-DD";
    • <user_id> — идентификатор пользователя Collaborator.

    Получение информации о пользователе

    Получить данные указанного пользователя и разместить их на листе «Зачетной книжки» мы сможем этой функцией:

    function getUserProfile(user_id){
  checkJWT(); //check and udate JWT-key if nessesary
  // /api/rest.php/auth/users/<user_id>
  var requestURL = '/api/rest.php/auth/users/' + user_id;
  var res = _getResByCollaboratorAPI(keyJWT, urlHome + requestURL);
  if (JSON.stringify(res).substring(1, 6) == 'Error') {
      Logger.log(JSON.stringify(res));
      return 0;
  }
  // refill cells with user info
  SHEET_MAIN.getRange('A4').setValue(res.fullname);
  clearImages(); // clear photo
  if (res.photo_thumb){
    getImageFromURL(urlHome + res.photo_thumb); //get & insert photo_thumb
  }
  SHEET_MAIN.getRange('B8').setValue(res.position);
  SHEET_MAIN.getRange('B9').setValue(res.department);
  SHEET_MAIN.getRange('B10').setValue(res.city);
}

    Эта функция кроме данных про ФИО, должность, подразделение и город человека еще получает и всталяет его фото.
    Перед вставкой фото на лист выполняется очистка всех картинок на листе, кроме первой (это логотип компании — его не трогаем). Это делает функция:

    function clearImages(){
  var ReportSheet = SpreadsheetApp.getActive();
  // Deletes all images (except image[0]) in a ReportSheet
  var images = ReportSheet.getImages();
  for (var i = 1; i < images.length; i++) {
    images[i].remove();
  }
}

    Получение фото и вставка его на лист выполняется функцией:

    function getImageFromURL(urlRequest){
  var ReportSheet = SpreadsheetApp.getActive();
  var sheet = SHEET_MAIN;
  var img = UrlFetchApp.fetch(urlRequest).getBlob();
  sheet.insertImage(img, 1, 5)
  var images = sheet.getImages();
  var img = images[images.length - 1];
  var imgH = img.getHeight();
  var imgW = img.getWidth();
  var maxW = 150;
  var newImgH = Math.round(imgH*(maxW/imgW));
  img.setWidth(maxW)
     .setHeight(newImgH)
     .setAltTextTitle('Изображение')
     .setAnchorCellYOffset(0)
     .setAnchorCellXOffset(5);
}

    Получение данных из истории обучения сотрудника

    Из «Истории обучения» пользователя Collaborator нам нужно извлечь список заданий, у которых их дата назначения пользователю попадает в заданный период от  <date_period_begin> до <date_period_end>.
    Добавим переменную, которая будет хранить первую ячейку таблицы отчета.

    var rngStartReport = SpreadsheetApp.getActive().getRangeByName('start_report');

    Это именованная ячейка «start_report«. Относительно нее мы будем заполнять все остальные ячейки.
    Функция заполнения отчета имеет вид:

    function getUserLearningHistoryForPeriod(user_id, date_period_begin, date_period_end, records_limit) {
  // get data of learning tasks for preset user who was assigned to this tasks in portal from <date_period_begin> to <date_period_end>
  // example api request:
  // api/rest.php/tasks?page=1&count=10&filter[date_assign]=2018-11-01&filter[date_assign]=2018-12-09&sorting[date_assign]=desc&user_id=3906&action=get-study-history-all-tasks
  records_limit = records_limit || 1000;
  var requestURL = '/api/rest.php/tasks?page=1&count=' + records_limit +
    '&filter[date_assign]=' + dateToStr(date_period_begin) +
    '&filter[date_assign]=' + dateToStr(date_period_end) +
    '&sorting[date_assign]=desc&user_id=' + user_id +
    '&action=get-study-history-all-tasks';
  var res = _getResByCollaboratorAPI(keyJWT, urlHome + requestURL);
  if (JSON.stringify(res).substring(1, 6) == 'Error') {
      Logger.log(JSON.stringify(res));
      return 0;
  }
  // fill cells in sheet "users"
  SHEET_MAIN.getRange(rngStartReport.getRow(), 1, SHEET_MAIN.getLastRow(), 10).clearContent();
  var rng = rngStartReport;
  var data_tasks = res.data;
  var accessdates = [];
  data_tasks.forEach( function(item, i, data_tasks) {
    rng.offset(i, 0).setValue(item.title);
    rng.offset(i, 1).setValue(item.display_type);
    rng.offset(i, 2).setValue(item.status);
    rng.offset(i, 3).setValue(item.mark);
    rng.offset(i, 4).setValue(item.date_assign);
    //rng.offset(i, 5).setValue(item.date_start);
    rng.offset(i, 5).setValue(item.date_finish);
  });
}

    Теперь осталось только определить <user_id> для выбранного из списка пользователя и все будет готово.

    Определяем ID пользователя и получаем «Зачетную книжку»

    Для работы запросов необходимо знать <user_id>. Вручную его вводить неудобно. Более «человечно» выбирать пользователя по его ФИО. И мы сделали выпадающий список, который помогает найти человека и выбрать его ФИО. Но это не дает нам нужный идентификатор. По-этому ради удобства использования прийдется немного поиграться…
    В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP).  Она прекрасно решает задачу нахождения идентификатора. Подробно описано тут: https://www.planetaexcel.ru/techniques/2/106/index.php?page=user&id=11
    VLOOKUP ищет по диаппазону ячеек строку с заданным значением (поисковой фразой) и возвращает значение из другого столбца этого диаппазона, но в той же строке. Есть нюанс: эта функция производит поиск только по первому столбцу диапазона. Поэтому нам надо будет изменить функцию получения списка пользователей, чтобы поменятьстолбцы местами:
    blank
    Измененная функция заполнения списка пользователей:

    function getUsersList() {
  checkJWT();
  var records_limit = 100;
  var date_period_begin = SHEET_MAIN.getRange('D3').getValue();
  var date_period_end = SHEET_MAIN.getRange('E3').getValue();
  var requestURL = '/api/rest.php/auth/users?page=1&count=' + records_limit +
    '&filter[last_activity]=' + dateToStr(date_period_begin) +
    '&filter[last_activity]=' + dateToStr(date_period_end) +
    '&sorting[last_activity]=desc';
  var res = _getResByCollaboratorAPI(keyJWT, urlHome + requestURL);
  if (JSON.stringify(res).substring(1, 6) == 'Error') {
      Logger.log(JSON.stringify(res));
      return 0;
  }
  SHEET_USERS.getRange(1, 1, 1000, 2).clearContent();
  var rng = SHEET_USERS.getRange(1, 1);
  var data_users = res.data;
  data_users.forEach( function(item, i, data_users) {
    rng.offset(i, 1).setValue(item.id);
    rng.offset(i, 0).setValue(item.fullname);
  });
}

    В Google Spreadsheets есть полная аналогия функции ВПР (VLOOKUP). Подробно тут: https://support.google.com/docs/answer/3093318?hl=ru. Используем ее.
    После перестановки столбцов не забудьте перенастроить правила проверки данных в ячейке с выпадающим списком на другой столбец (было users!B:B — исправляем на users!A:A).
    blank
    На листе настроек добавим ячейку, которая будет содержать идентификатор пользователя Collaborator, которого мы выберем из выпадающего списка на «Зачетной книжке». Назначим ей имя «user_id«. В этой ячейке вставим формулу:
    =VLOOKUP(main!D5;users!A:B;2;FALSE)

    blank
    В коде скрипта значение из этой ячейки получаем так:
    var user_id = SpreadsheetApp.getActive().getRangeByName('user_id').getValue();
    Итоговая функция заполнения «Зачетной книжки» имеет вид:

    function main(){
  var user_id = SpreadsheetApp.getActive().getRangeByName('user_id').getValue();
  var date_period_begin = SHEET_MAIN.getRange('D3').getValue();
  var date_period_end = SHEET_MAIN.getRange('E3').getValue();
  checkJWT(); //check and udate JWT-key if nessesary
  getUserProfile(user_id);
  getUserLearningHistoryForPeriod(user_id, date_period_begin, date_period_end);
}

    И последний штрих. Надо назначить на нарисованные кнопки скрипты, которые им следует запускать:

    • Кнопка «Обновить список пользователей» — скрипт getUsersList ;
    • Кнопка «Заполнить книжку» — скрипт main .

    Теперь можно пользоваться и вот как это работает:

    Итог

    Вот и добрались до практических и полезных задач. Этот пример работы через API с Collaborator можно легко развить в формирование сертификатов за обучение, подсчет «хитрого» рейтинга и т.д.
    Использование MS Excel или Google Spreadsheets выгодно тем, что в руках у пользователя оказываются огромные возможности настройки внешнего вида отчетов, дополнительной обработки и визуализации данных. Кроме этого он можем распечатать или сохранить результат в удобном формате, самостоятельно настраивая все параметры и не ожидая помощи от программистов 🙂
    Как всегда — полный код скрипта main со всеми функциями можно найти тут:   https://github.com/mirgor/lmscollaborator_api_examples

    blank
    The LMS Collaborator Team
    Content Manager LMS Collaborator
    Всі записи автора
    Теги
    ОбщееКейсы (Примеры использования)
    Испытайте LMS Collaborator в действии
    Нужны рекомендации по выбору правильных функций для оцифровки и автоматизации корпоративных процессов обучения? Мы здесь, чтобы помочь.
    Заказать демо
    Или звоните нашему менеджеру
    +38(067)217-0440
    Другие статьи
    Все статьи