<?php
/**
 * This is used to scrape the establishment scores from the MCHD site
 * @author @samkeen
 *
 * The target table has this form (jsut showing one row here, in actuality it is multiple rows)
 *
 * <table cellspacing="2" cellpadding="2" rules="all" border="1" id="ResultsDataGrid" style="border-width:1px;border-style:solid;width:100%;">
 * <tr>
 *   <td align="left" style="width:30%;"><a href="ListSearch.aspx?id=265050" target="_self">1000 Sundries</a></td>
 *   <td align="left" style="width:30%;">1000 SW Broadway</td>
 *   <td align="left" style="width:15%;">Portland</td>
 *   <td align="left" style="width:10%;">97205</td>
 * </tr>
 */
require_once 'PHPUnit/Extensions/SeleniumTestCase.php';
class Example extends PHPUnit_Extensions_SeleniumTestCase {
    private $match_cell_text = "//table[@id='ResultsDataGrid']/tbody/tr[{row}]/td[{index}]";
    private $match_cell_href = "//table[@id='ResultsDataGrid']/tbody/tr[{row}]/td[{index}]/a@href";
    private $establishemnt_header_fields = array();
    private $collected_establishments = array();

    function setUp() {
    // form:  {TABLE CELL number} => '{db field name}::{seleniumRC method name}::{match rule}'
    // order important, should be same as column order of target table (columns start at 1 not 0)
        $this->establishemnt_header_fields = array(
            1 => array(
            "name::getText::{$this->match_cell_text}",
            "detail_link::getAttribute::{$this->match_cell_href}"
            ),
            2 => "address::getText::{$this->match_cell_text}",
            3 => "city::getText::{$this->match_cell_text}",
            4 => "postal_code::getText::{$this->match_cell_text}"
        );
        $this->setBrowser("*chrome");
        $this->setBrowserUrl("http://www2.co.multnomah.or.us");
        $this->db_connection = $this->db_connect();
    }
    protected function tearDown() {
        $this->db_connection->close();
    }

    function testMyTestCase() {
        $this->open("/Health/mchealthinspect/ListSearch.aspx");
        $this->click("Button1");
        $this->waitForPageToLoad("30000");
        $this->parse_table();
        $pages = 1;
        while($pages<1000) { // 1000 is arbitrary safty stop, try/catch should engage when we run out of rows
            try {
                $this->assertTrue($this->isElementPresent("Next"));
                $this->clickAndWait("Next");
                $this->waitForPageToLoad("30000");
                sleep(4);
                $this->parse_table();
            } catch (PHPUnit_Framework_AssertionFailedError $e) {
                array_push($this->verificationErrors, $e->toString());
                die;
                break;
            }
            // be nice to server
            sleep(1);
            $pages++;
        }
    }

    private function parse_table($max_rows=100) {
        $row = 2;
        // 100 is arbitrary safty stop, try/catch should engage when we run out of rows
        while($row<$max_rows) {
            try {
                $this->assertTrue($this->isElementPresent("//table[@id='ResultsDataGrid']/tbody/tr[{$row}]/td[1]"));
                $this_row = array();
                foreach ($this->establishemnt_header_fields as $index => $field) {
                    $field = is_array($field)?$field:array($field);
                    foreach ($field as $sub_field) {
                    // $sub_field is in form ex: "name::getText:://table[@id='ResultsDataGrid']/tbody/tr[{row}]/td[{index}]"
                        $field_parts = explode('::',$sub_field);
                        $sub_field = $field_parts[0];
                        $match_command = $field_parts[1];
                        $match_rule = $field_parts[2];
                        $match_rule = str_replace(array('{row}','{index}'),array($row,$index),$match_rule);
                        $field_value = $this->{$match_command}($match_rule);
                        $this_row[$sub_field] = $field_value;
                        /*
                         * special case for detail_link: we also grab the mchd_id out of it
                         */
                        $match = null;
                        if($sub_field=='detail_link' && preg_match('/\?.*$/',$field_value,$match)) {
                            $this_row['mchd_id'] = trim($match[0],' ?');
                        }
                    }
                }
                $this->collected_establishments[] = $this_row;
            } catch (PHPUnit_Framework_AssertionFailedError $e) {
                array_push($this->verificationErrors, $e->toString());
                break;
            }
            $row++;
        }
        //## INSERT Establishment ROW ##//
        // @todo convert to prepared statements

        foreach ($this->collected_establishments as $establishment) {
            $sql_insert_message = $this->build_insert_query($establishment);
            $this->db_connection->query($sql_insert_message);
        }
        $this->collected_establishments = array();
    }
    private function build_insert_query($inset_elements) {
        $query = "INSERT `establishments` (`".implode('`, `',array_keys($inset_elements))."` ) VALUES(";
        foreach ($inset_elements as &$element) {
            $element = mysqli_real_escape_string($this->db_connection, $element);
        }
        $query .= "'".implode("', '", $inset_elements)."')";
        return $query;
    }
    private function db_connect() {
        $mysqli = mysqli_connect( 'localhost', 'test', 'test', 'mchd' );
        /* check connection */
        if (mysqli_connect_errno()) {
            printf ( "Connect failed: %s\n", mysqli_connect_error () );
            exit ();
        }
        return $mysqli;
    }
}
?>