Statistics by Item Tutorial

This example consists of implementing a robot to visit the Trivago website and check the packages offered by the online travel agencies (OTA) for a specific hotel and date, and one-night stay. All the packages found will be written into an Excel file, which will be the robot's result or output artifact.

Besides, the prices of the packages will be classified by a specified price range. The classification will be as follows:

  • Price lower than the specified range: UNDER
  • Price higher than the specified range: ABOVE
  • Price within the specified range: IN_RANGE
  • No offers: EMPTY

This classification will allow you to visualize statistics per item from the Appian RPA Console, which will visually show the classification of those items.

All the necessary input information will be obtained from an Excel file which will be received as an argument in every execution, so our robot needs a mandatory input variable of type Text.

Source Code and Support Files

Description Link
Source Code robot-tutorial-statistics-by-item.zip
Chrome Driver v2.35 chromedriver.exe
Gecko Driver v0.23 win64 (Firefox) geckodriver.exe
IE Driver Server v3.8 win64 IEDriverServer.exe
Input file example trivago.xlsx

Review Browser module architecture page for other browser versions.

Configuration

Environment

  • Development:
    • IDE: Eclipse
    • JDK 1.8
    • Maven 3
    • Appian RPA modules:
      • Client
      • Browser
      • Data Provider
  • Resource:
    • SO: Windows
    • JRE 1.8
    • Applications:
      • Firefox, Chrome or IE11 browser

Eclipse Project

You must import the Eclipse project included in the zip file in the beginning of this tutorial. After importing the project, it is very important to modify the pom.xml file. You have to fill in the parent tag with the version of Appian RPA you are using, and change the profile tag with the configuration of your repository.

Workflow

The robot will receive the input file and will check the data to make sure they are correct. Then, it will launch the web browser and will visit the Trivago website. Then, it will perform a search for the hotel, and just in case it exists, it will look for the packages offered for a specific date.

Once all dates are shown, it will perform a process consisting of reading the packages information and writing it into the output file. In addition, the process will classify the package by its price, in order to obtain the statistics per item.

rpa-statistics-workflow.png

You can import this workflow using this file. To import the workflow, you have to click in the import button (2233684.png), and paste the data included in the file.

Support Files

This tutorial needs the driver of the browser to use as support file. Depending on the browser, these drivers can be found at the beginning of this tutorial. If the browser's version doesn't correspond with the one supported by the driver, you must download the right one.

2229515.png

Also, the robot will need two input variables:

  • A parameter to choose a browser. Valid values are: "chromeBrowser,ieBrowser,firefoxBrowser"
  • The maximum number of posts to read.

Implementation

We will analyze the class TrivagoRobot, to discuss the robot functioning. Before we begin, it is important to highlight that the robots should slow down their working pace because the applications are not designed for such a high working speed. Though the smart wait can help a lot to achieve this, sometimes you need to perform periodic short pauses to adapt the robot pace.

We now start analyzing the methods init and validateInputData. The method init initializes the different components that will be used during the execution, obtains the information about the input file and initialize certain data, which are necessary for the execution. It is also possible to include a file as a fallback in case no other file is specified as an input variable for the robot. To do so, we should add a support file called, "trivago.xlsx".

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
@override
public boolean startUp() throws Exception {

    server = (IJidokaServer<?>) JidokaFactory.getServer();
    client = IClient.getInstance(this);

    // Get Browser module
    browser = IWebBrowserSupport.getInstance(this, client);

    // Get IExcel component
    excel = IExcel.getExcelInstance(this);

    return IRobot.super.startUp();
}

/**
 * Action to initialize the robot.
 * 
 * @throws Exception if no browser was selected or in case of errors
 *                   initializing IExcel module
 */
public void init() throws Exception {

    try {
        setBrowser();

        client.defaultPause(PAUSE);
        client.typingPause(PAUSE);
        client.mousePause(PAUSE);
        client.characterPause(50);

        String fileName = StringUtils.isBlank(server.getWorkflowParameters().get(PARAM_INPUT_FILE)) ? "trivago.xlsx"
                : server.getWorkflowParameters().get(PARAM_INPUT_FILE);

        inputFilePath = Paths.get(server.getCurrentDir(), fileName).toString();

        excel.init(inputFilePath);

        loadProperties();
    } catch (Exception e) {
        throw new JidokaFatalException("Error initializing the robot");
    }
}

In the method validateInputData the robot checks that the input file information meets the requirements. For example, the name of the hotel must be informed, and the date must be properly formed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
/**
* Action to validate input file.
*/
public void validateInputFile() {

    try {
        // check hotel name
        String hotel = excel.getCellValueAsString(1, 0);
        if (StringUtils.isBlank(hotel)) {
            throw new JidokaFatalException("The name of hotel is empty.");
        }

        // Check input date
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
        dateFormat.setLenient(false);

        Date checkingDate = null;

        Cell dateCell = excel.getCell(new CellReference(1, 1));
        if (dateCell.getCellType() == CellType.FORMULA) {

            FormulaEvaluator evaluator = excel.getWorkbook().getCreationHelper().createFormulaEvaluator();

            CellValue dateCellValue = evaluator.evaluate(excel.getCell(new CellReference(1, 1)));

            checkingDate = DateUtil.getJavaDate(dateCellValue.getNumberValue());
        } else if (dateCell.getCellType() == CellType.NUMERIC) {
            checkingDate = dateCell.getDateCellValue();
        } else {
            checkingDate = dateFormat.parse(dateCell.getStringCellValue());
        }

        server.debug("Parsed checking date :" + checkingDate);
        server.debug("Current date: " + new Date());

        if (checkingDate.before(new Date())) {
            throw new JidokaFatalException("The input date cannot be in the past.");
        }

        this.inputData = new InputData(hotel, checkingDate);
    } catch (ParseException e) {
        throw new JidokaFatalException("Invalid format for input date.", e);
    } catch (Exception e) {
        throw new JidokaFatalException("Error validating input file format");
    }
}

Now we will analyze the methods openBrowser, searchHotel and checkIfHotelExist.

The method openBrowser will launch the browser to visit the Trivago website. Once there, it waits until the search field is loaded to type in the name of the hotel. Besides, it checks whether the banner for the cookie policy appears. If so, it closes it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
/**
* Action to open browser and navigate to Trivago web site.
*/
public void openBrowser() {

    try {
        // Open browser
        browser.initBrowser();

        // Open Trivago web site
        browser.navigate(TRIVAGO_URL);

        client.mouseMoveToCenter();

        // If a banner exists, close it
        if (browser.existsElement(By.className("accept"))) {
            browser.clickOnElement(By.className("accept"));
        }

        // Ensure focus on input search
        browser.clickSafe(browser.waitElement(By.cssSelector("input[type=search]")));

        // Press space to go to the advanced
        client.keyboard().space();
    } catch (Exception e) {
        throw new JidokaFatalException("Error opening the browser");
    }
}

The method searchHotel types the name of the hotel into the search field. This search process is automatically launched by the Trivago website, so we don't need to click on any button, neither perform any other action.

1
2
3
4
5
6
7
8
9
/**
 * Action to search hotel in Trivago.
 */
public void searchHotel() {

    server.info("Search hotel: " + inputData.getHotel());

    windows.typeText(inputData.getHotel());
}

The method checkIfHotelExist checks whether the Trivago website displays a message informing that the hotel does not exist. If such message is on screen, the method returns the literal "no", otherwise, it returns the literal "yes".

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
* Conditional action to check if hotel is found.
*
* @return "yes" if hotel exists and "no" if hotel doesn't exist.
*/
public String checkIfHotelExist() {

    try {
        // Set default timeout
        browser.setTimeOutSecondsWaitElement(IMPLICIT_WAIT_TIME_MS);

        WebElement warningMessage = browser.getElement(By.cssSelector(".ssg-feedback-no-results"));

        return warningMessage == null || !warningMessage.isDisplayed() ? "Yes" : "No";
    } catch (Exception e) {
        throw new JidokaFatalException("Error checking if the hotel exist");
    }
}

Next, we will analyze the method searchByDates, which will select the check-in date within the search calendar. To do so, it will browse the calendar looking for the right month, if necessary, and then it will select the following day in the calendar for the check-out date.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
/**
* Action to search by input dates.
*
* @throws JidokaUnsatisfiedConditionException if some conditions are not met
*/
public void searchByDates() {

    try {
        // Enter the term to search
        client.keyboard().enter();

        client.pause(2000);

        // Get date from input data and select it in the calendar
        Calendar cal = Calendar.getInstance();
        cal.setTime(inputData.getCheckingDate());
        selectMonthInCalendar(cal.get(Calendar.MONTH), cal.get(Calendar.YEAR));

        client.pause(1000);

        // Select the day of check-in
        int day = cal.get(Calendar.DAY_OF_MONTH);
        pickDay(day);

        client.pause(1000);

        cal.add(Calendar.DATE, 1);
        day = cal.get(Calendar.DAY_OF_MONTH);

        // Select the day of the checkout
        pickDay(day);

        client.pause(2000);

        // Show the room type selector
        browser.clickOnElement(By.xpath("(//button[@class='roomtype-btn'])[2]"));

        client.pause(1000);

        // Wait until the list of OTAs available for the hotel is loaded
        client.waitFor(this).wait(5, "Wait for hotel name to be clickable", true,
                () -> elementToBeClickable(By.xpath(HOTEL_NAME)));

        closeSignInBannerIfExist();
    } catch (JidokaUnsatisfiedConditionException e) {
        throw new JidokaFatalException("Error searching by dates");
    }
}

This code snippet uses different Browser module's methods to look for the necessary elements in the browser, and interact with them. Additionally, it uses the private method selectMonthInCalendar to perform the proper month selection.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
/**
* Search and select the month in calendar.
*
* @param month the month to search
* @param year the year to search
*/
private void selectMonthInCalendar(int month, int year) {

    Calendar cal = Calendar.getInstance();
    cal.set(Calendar.MONTH, month);
    cal.set(Calendar.YEAR, year);
    cal.set(Calendar.DAY_OF_MONTH, 1);

    // Go forward or backward depending on the input date
    Date heading = getCalendarHeading();
    String paginateButton;

    if (heading.compareTo(cal.getTime()) > 0) {
        paginateButton = ".cal-btn-prev";
    } else {
        paginateButton = ".cal-btn-next";
    }

    boolean monthFound = false;
    String monthStr = new SimpleDateFormat("MMMM").format(cal.getTime());
    do {
        server.info("Checking month " + monthStr + " in the calendar...");
        String calendarMonth = browser.waitElement(By.cssSelector(".cal-heading-month")).getText();

        calendarMonth = calendarMonth.split(" ")[0];

        monthFound = calendarMonth.equalsIgnoreCase(monthStr);

        if (!monthFound) {
            server.info("Go to the next month...");
            browser.clickOnElement(By.cssSelector(paginateButton));
        }

    } while (!monthFound);

    server.info("Calendar month changed to:" + monthStr);
}

The methods foundHotelDeals and loadHotelDeals will check whether there are offers, and if so, show them all and load them to process them.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
/**
* Conditional action to check if hotel deals were found.
*
* @return yes or not.
*/
public String foundHotelDeals() {

    try {
        // Load deals
        client.waitFor(this).wait(10, "Waiting for the deals to be loaded", true, 
                () -> elementToBeClickable(By.xpath(LOAD_OTHER_DEALS_BUTTON)));

        browser.clickOnElement(By.xpath(LOAD_OTHER_DEALS_BUTTON));

        server.info("Checking for hotel deals");

        client.waitFor(this).wait(10, "Waiting for the deals to be loaded", true, 
                () -> elementToBeClickable(By.cssSelector(".slideout-deal")));

        return browser.getElements(By.cssSelector(".slideout-deal, .slideout-deal__price")).isEmpty() ? "No"
                : "Yes";
    } catch (Exception e) {
        throw new JidokaFatalException("Error founding hotel deals");
    }
}

/**
 * Action to load all hotel deals.
 * 
 * @throws Exception
 */
public void loadHotelDeals() {

    try {
        server.info("Reading hotel deals");
        client.pause(5000);

        while (!elementToBeClickable(By.className("sl-box__arrow--expanded"))) {

            WebElement moreDealsButton = browser.getElement(By.className(MORE_HOTEL_DEALS_BUTTON));
            moveTo(moreDealsButton);
            moreDealsButton.click();
            server.info("Loading more data...");
            client.pause(2000);
        }
        server.info("Loaded all data...");

        otas = browser.getElements(By.xpath(OTA_CONTAINER));

        server.info("Read complete");
        server.setNumberOfItems(otas.size());
    } catch (Exception e) {
        throw new JidokaFatalException("Error loading hotel deals");
    }
}

Let's focus now on the methods hasMoreOtas and writeOtaToOutputFile. The method hasMoreOtas checks whether there is any package left to process. If so, the workflow continues to the method writeOtaToOutputFile, which processes the package and writes the information into the output file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
/**
* Conditional action to check if there are more OTAs left to process.
*
* @return the wire name
*/
public String hasMoreOtas() {

    return currentItem < otas.size() ? "Yes" : "No";
}

/**
 * Action to write information about the current OTA to the output file.
 * 
 * @throws JidokaException if IExcel cannot be initialized
 */
public void writeOtaToOutputFile() throws JidokaException {

    try {
        server.info("Write OTA in output file, OTA number: " + currentItem);

        createOutputHeader();

        Ota currentOta = readHotelDealInformation();
        currentItem++;
        server.setCurrentItem(currentItem, currentOta.getName());

        Row row = excel.getSheet().createRow(firstRow);
        row.createCell(0, CellType.STRING).setCellValue(currentOta.getName());
        row.createCell(1, CellType.STRING).setCellValue(currentOta.getRoom());
        row.createCell(2, CellType.STRING).setCellValue(currentOta.getComplements());
        row.createCell(3, CellType.STRING).setCellValue(currentOta.getPrice());

        server.setCurrentItemResultToOK();
        firstRow++;
    } catch (Exception e) {
        throw new JidokaItemException("Error writing the current OTA information on the output file");
    }
}

This method will be analyzed in more detail. Apart from writing the information into the output file by using the component IExcel, it notifies the server the current item key and classifies the packages by the price and the specified range in the input file. After doing the calculation, it creates an object of type Map<String, String>, which will contain the specific item properties. These properties are the ones which we will able to analyze from the Statistics per item section provided by the Appian RPA Console.

The method setCurrentItemResultToOK(String, Map<String, String>) is used to register this information. It exposes the interface IJidokaStatistics. In the same way that we have this method for the correct item processing, we have a similar method in case the item processing result should generate a warning.

Additionally, the private method readHotelDealInformation obtains the information related to the current item.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
/**
* Read information about current hotel deal.
*
* @return the {@link Ota} instance filled with the hotel information
*/
private Ota readHotelDealInformation() {

    // DOM
    WebElement otaContainer = otas.get(currentItem);

    String otaName = "";
    String roomDescription = "";
    String roomPrice = "";
    String roomComplements = "";

    // Set default timeout
    browser.setTimeOutSecondsWaitElement(IMPLICIT_WAIT_TIME_MS);

    otaName = otaContainer.findElement(By.className(CLASSNAME_FOR_OTA_LOGO)).getAttribute("title");

    String msg = "Unable to locate element with classname \"%s\"";

    try {
        // Try to get elements text
        roomDescription = otaContainer.findElement(By.className(CLASSNAME_FOR_OTA_ROOM_DESCRIPTION)).getText();
    } catch (NoSuchElementException e) {
        // Send warning and skip current item, so robot can continue with the next item
        server.warn(String.format(msg, CLASSNAME_FOR_OTA_ROOM_DESCRIPTION));
    }

    try {
        // Try to get elements text
        roomPrice = otaContainer.findElement(By.className(CLASSNAME_FOR_OTA_ROOM_PRICE)).getText()
                .replaceAll("\u200E", "");

    } catch (NoSuchElementException e) {
        // Send warning and skip current item, so robot can continue with the next item
        server.warn(String.format(msg, CLASSNAME_FOR_OTA_ROOM_PRICE));
    }

    try {
        // Try to get elements text
        if (roomPrice != null && !roomPrice.isEmpty()) {
            roomComplements = otaContainer.findElement(By.className(CLASSNAME_FOR_OTA_ROOM_COMPLEMENTS)).getText();
        }
    } catch (NoSuchElementException e) {
        // Send warning and skip current item, so robot can continue with the next item
        server.warn(String.format(msg, CLASSNAME_FOR_OTA_ROOM_COMPLEMENTS));
    }

    return new Ota(otaName, roomDescription, roomComplements, roomPrice);
}

Finally, we will analyze the methods end and cleanUp.

The method end will set the mark that identifies a correct robot execution to true, whereas the method cleanUp will close the browser and the component IExcel used to handle the output file. Additionally, it returns the path to that file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/**
 * Action to end robot.
 */
public void end() {

    robotEnd = true;
}
/**
 * @see com.novayre.jidoka.client.api.IRobot#cleanUp()
 */
@Override
public String[] cleanUp() throws Exception {

    closeBrowser();
    closeExcel();

    if (!robotEnd) {
        server.setCurrentItemResultToWarn("ERROR");
        currentItem++;

        while (currentItem < otas.size()) {
            server.setCurrentItem(currentItem, "Item Skipped");
            server.setCurrentItemResultToWarn("SKIPPED");
            currentItem++;
        }
    }

    return new String[];
}

The method also controls whether all the items have been properly processed, which will happen provided we have run the method end. In case not all the items have been processed, the last processed item is marked as an error, ignoring the rest of them.

Execution

Once we have implemented our robot, we will deploy it to the Appian RPA repository.

mvn clean deploy

When launching the robot, we should specify the input file, since it has been set as a mandatory input variable.

rpa-statistics-exec-options.png


This version of the Appian RPA documentation was written for Appian 21.2, and does not represent the interfaces or functionality of other Appian versions.
Open in Github Built: Fri, Nov 12, 2021 (02:44:55 PM)

On This Page

FEEDBACK