}
//retrieves data about voting members of assembly from https://scraperwiki.com/scrapers/cz_praha_voting_records_retrieval/
//2010-2014
require 'scraperwiki/simple_html_dom.php';
scraperwiki::attach("cz_praha_voting_records_retrieval", "src");
$rows = scraperwiki::select("distinct(mp_id) from src.mp_vote");
foreach ($rows as $row) {
    $url = "http://www.praha.eu/jnp/cz/home/volene_organy/zastupitelstvo_hmp/slozeni_zastupitelstva/index.html?memberId=" . $row['mp_id'];
    $html = scraperwiki::scrape($url);
    $dom = new simple_html_dom();
    $dom->load($html);
    $part = get_first_string($html, '</h2>', '<div>');
    $name = trim($dom->find('h2', 0)->plaintext);
    $email = get_first_string($part, 'mailto:', '"');
    $party = trim(get_first_string($part, 'Strana:</span>', '<br'));
    $club = trim(get_first_string(get_first_string($part, 'Klub:</span>', '</a') . '::', '">', '::'));
    $data[] = array('id' => $row['mp_id'], 'name' => $name, 'party' => $party, 'club' => $club);
}
scraperwiki::save_sqlite(array('id'), $data, 'info');
/**
 * finds substrings between opening and closing markers
 * @return result array of the substrings
 */
function returnSubstrings($text, $openingMarker, $closingMarker)
{
    $openingMarkerLength = strlen($openingMarker);
    $closingMarkerLength = strlen($closingMarker);
    $result = array();
    $position = 0;
    while (($position = strpos($text, $openingMarker, $position)) !== false) {
        $position += $openingMarkerLength;
    $html = '<html><body>' . $r['html'] . '</body></html>';
    $dom = new simple_html_dom();
    $dom->load($html);
    $info = array('id' => $r['id'], 'decision_number' => $r['decision_number'], 'date' => $r['date'], 'document_number' => $r['document_number'], 'name' => $r['name'], 'passed' => $r['passed'], 'link' => $r['link']);
    $part = get_first_string($html, '</h2>', '<div>');
    $info['for'] = trim(get_first_string($part, 'pro:</span>', '<br')) == '' ? 0 : trim(get_first_string($part, 'pro:</span>', '<br'));
    $info['against'] = trim(get_first_string($part, 'proti:</span>', '<br')) == '' ? 0 : trim(get_first_string($part, 'proti:</span>', '<br'));
    $info['abstain'] = trim(get_first_string($part, 'zdržel se:</span>', '<br')) == '' ? 0 : trim(get_first_string($part, 'zdržel se:</span>', '<br'));
    $info['number_representatives'] = trim(get_first_string($part, 'Počet zastupitelů:</span>', '<br')) == '' ? 0 : trim(get_first_string($part, 'Počet zastupitelů:</span>', '<br'));
    $info['present'] = trim(get_first_string($part, 'přítomno:</span>', '<br')) == '' ? 0 : trim(get_first_string($part, 'přítomno:</span>', '<br'));
    $trs = $dom->find("table[class=data-grid]", 0)->find("tr");
    array_shift($trs);
    $data = array();
    foreach ($trs as $tr) {
        $tds = $tr->find("td");
        $mp_id = get_first_string($tds[0]->find("a", 0)->href . "&", "memberId=", "&");
        $data[] = array('division_id' => $info['id'], 'mp_id' => $mp_id, 'vote' => trim($tds[1]->plaintext), 'mp_name' => $tds[0]->plaintext);
    }
    //one division done
    scraperwiki::save_sqlite(array('id'), $info, 'division');
    scraperwiki::save_sqlite(array('division_id', 'mp_id'), $data, 'mp_vote');
    scraperwiki::save_var('last_id', $info['id']);
}
/**
 * finds substrings between opening and closing markers
 * @return result array of the substrings
 */
function returnSubstrings($text, $openingMarker, $closingMarker)
{
    $openingMarkerLength = strlen($openingMarker);
    $closingMarkerLength = strlen($closingMarker);
     //preg_match('/id=([0-9]{1,})/',$as[0]->href,$matches);  //simple_html_dom.php
     preg_match('/id=([0-9]{1,})/', $as[0], $matches);
     $data_mp['mp_unique_id'] = $matches[1];
     //$tmp = explode ('-',$as[0]->plaintext);  //simple_html_dom.php
     $tmp = explode('-', get_first_string($as[0], '">', '<'));
     $tmp2 = explode('/', trim(end($tmp)));
     $data_mp['state'] = $tmp2[1];
     $data_mp['party'] = $tmp2[0];
     array_pop($tmp);
     $data_mp['name'] = trim(implode('-', $tmp));
 }
 scraperwiki::save_sqlite(array('term', 'mp_id'), $data_mp, 'mp');
 //votes
 $data = array();
 //$trs0 = $dom->find('table[class=tabela-1]',0);  //simple_html_dom.php
 $trs0 = get_first_string($html[0]['html'], '<table class="tabela-1"', '<!--Fim Código-->');
 $trs = returnSubstrings($trs0, '<tr', '</tr>');
 if (count($trs) > 0) {
     array_shift($trs);
     //first row is the header
     foreach ($trs as $tr) {
         //$tds = $tr->find('td');  //simple_html_dom.php;
         $tds = returnSubstrings($tr, '<td', '</td>');
         //if ($tr->class == 'even') { //session  //simple_html_dom.php
         if (strpos($tr, 'even') > 0) {
             //session
             //$da = explode('/',trim($tds[0]->plaintext));  //simple_html_dom.php
             $da = explode('/', trim(strip_tags('<td' . $tds[0])));
             $date = $da[2] . '-' . $da[1] . '-' . $da[0];
             //$session = trim($tds[1]->plaintext);  //simple_html_dom.php
             $session = trim(strip_tags('<td' . $tds[1]));
        if ($number > 500) {
            echo $url1 . "** has more than 500 divisions -> problem -> needs to solve pagination";
            die;
        }
        $trs = $dom1->find("table[class=data-grid]", 0)->find("tr");
        array_shift($trs);
        $data = array();
        foreach ($trs as $tr) {
            $tds = $tr->find('td');
            $datum = array('decision_number' => $tds[0]->plaintext, 'date' => convert_date($tds[1]->plaintext), 'document_number' => $tds[2]->plaintext, 'name' => $tds[3]->plaintext, 'passed' => $tds[4]->plaintext, 'link' => htmlspecialchars_decode($tds[4]->find('a', 0)->href));
            $datum['id'] = get_first_string($datum['link'] . "&", "votingId=", "&");
            $url = "http://www.praha.eu" . $datum['link'];
            $html = scraperwiki::scrape($url);
            $dom = new simple_html_dom();
            $dom->load($html);
            $datum['html'] = '<h1>' . get_first_string($dom->innertext, '<h1>', '</table>') . '</table>';
            $data[] = $datum;
        }
        //one session done:
        scraperwiki::save_sqlite(array('id'), $data, 'division');
        scraperwiki::save_sqlite(array('id'), $s, 'session');
    }
}
/**
 * converts dates formats between Central European and ISO (ISO 8601)
 * @return converted date
 * examples:
 * convert_date('2010-02-15','to euro')
 *    returns '15.2.2010;
 * convert_date('15.2.2010')
 *    returns '2010-02-15'
function cl_camara_division2array($division_id)
{
    include "./db.inc.php";
    $db = new MySQL();
    //if(!$db->init()) die("¡¡¡ERROR!!!<BR>\n");
    $url = "http://www.camara.cl/trabajamos/sala_votacion_detalle.aspx?prmID=";
    //3274
    $out = array();
    $out['division_id'] = $division_id;
    $html = Grabber($url . $division_id);
    $out['original_html'] = $html;
    $a_favor_sub = get_first_string($html, 'A favor</h2>', 'En contra</h2>');
    $en_contra_sub = get_first_string($html, 'En contra</h2>', 'Abstención</h2>');
    $abstencion_sub = get_first_string($html, 'Abstención</h2>', 'Dispensados Art. 5°</h2>');
    $dispensados_sub = get_first_string($html, 'Dispensados Art. 5°</h2>', 'Pareos</h2>');
    $pareos_sub = get_first_string($html, 'Pareos</h2>', '</div>');
    $table_sub = get_first_string($html, '<table class="tabla resumenvotacion">', '</table>');
    $table_control_number = returnSubstrings($table_sub, '<td>', '</td>');
    $fecha = trim(get_first_string($html, 'Fecha:</strong>', '</p>'));
    $materia = str_replace("'", "\\'", trim(get_first_string($html, 'Materia:</strong>', '</p>')));
    $out['info']['topic'] = $materia;
    if ($materia == "") {
        $materia = str_replace("'", "\\'", trim(get_first_string($html, 'Observaciones:</strong>', '</p>')));
        $out['info']['topic'] = $materia;
    }
    $articulo = str_replace("'", "\\'", trim(get_first_string($html, 'Artículo:</strong>', '</p>')));
    $out['info']['article'] = $articulo;
    $sesion = str_replace("'", "\\'", trim(get_first_string($html, 'Sesión:</strong>', '</p>')));
    $out['info']['session'] = $sesion;
    $tramite = str_replace("'", "\\'", trim(get_first_string($html, 'Trámite:</strong>', '</p>')));
    $out['info']['step'] = $tramite;
    $tipo_de_votacion = str_replace("'", "\\'", strtolower(trim(get_first_string($html, 'Tipo de votación:</strong>', '</p>'))));
    $out['info']['division_type'] = $tipo_de_votacion;
    $quorum = str_replace("'", "\\'", trim(get_first_string($html, 'Quorum:</strong>', '</p>')));
    $out['info']['quorum'] = $quorum;
    $resultado = str_replace("'", "\\'", trim(get_first_string($html, 'Resultado:</strong>', '</p>')));
    $out['info']['result'] = $resultado;
    $name_sub = trim(get_first_string($html, '<div id ="detail">', '<p>'));
    $name = trim(get_first_string($name_sub, '<h2>', '</h2>'));
    $out['info']['name'] = $name;
    $fecha_db_ar = explode(' ', $fecha);
    global $mes;
    $fecha_db = $fecha_db_ar[4] . '-' . $mes[trim($fecha_db_ar[2], '.')] . '-' . $fecha_db_ar[0] . ' ' . $fecha_db_ar[5];
    $fecha_db_date = $fecha_db_ar[4] . '-' . $mes[trim($fecha_db_ar[2], '.')] . '-' . $fecha_db_ar[0];
    $fecha_db_time = $fecha_db_ar[5];
    $out['info']['date'] = $fecha_db_date;
    $out['info']['time'] = $fecha_db_time;
    /*$query = "
    		INSERT INTO 
    			division (division_id,divided_on,name,materia,session,article,tramite,type,quorum,result)
    		VALUES 
    			($row, '$fecha_db', '$name', '$materia', '$sesion', '$articulo', '$tramite', '$tipo_de_votacion', '$quorum', '$resultado')
    	";*/
    $camara = 'C.Diputados';
    $en_sala = '1';
    $out['info']['enSala'] = 'true';
    if (strpos($name, 'Bolet') == 0) {
        $nro_boletin = substr($name, 12);
    }
    if ($nro_boletin != null) {
        $id_proyecto_ley = $db->getIdProyectoLey($nro_boletin);
    } else {
        $id_proyecto_ley = 0;
    }
    $id_sesion = $db->getIdSesion($sesion);
    $name = utf8_decode($name);
    $tipo_de_votacion = utf8_decode($tipo_de_votacion);
    $articulo = utf8_decode($articulo);
    $materia = utf8_decode($materia);
    $quorum = utf8_decode($quorum);
    $query = "INSERT INTO Votacion (name,camara,en_sala,tipo,articulo,materia,fecha,hora,voto_si,voto_no,voto_abs,voto_disp,voto_pareos,voto_aus,resultado,quorum,id_proyecto_ley,id_sesion,id_parlamento,created_at,updated_at) VALUES ('{$name}', '{$camara}', {$en_sala}, '{$tipo_de_votacion}', '{$articulo}', '{$materia}', '{$fecha_db_date}', '{$fecha_db_time}', {$table_control_number['0']}, {$table_control_number['1']}, {$table_control_number['2']}, {$table_control_number['3']}, 0, 0, '{$resultado}', '{$quorum}', {$id_proyecto_ley}, {$id_sesion}, {$division_id}, '" . date('Y-m-d H:m:s') . "', '" . date('Y-m-d H:m:s') . "')";
    //echo $query;
    $id_votacion = $db->insert($query);
    //echo $id_votacion;
    $a_favor_ar = returnSubstrings($a_favor_sub, 'ID=', '">');
    $en_contra_ar = returnSubstrings($en_contra_sub, 'ID=', '">');
    $abstencion_ar = returnSubstrings($abstencion_sub, 'ID=', '">');
    $dispensados_ar = returnSubstrings($dispensados_sub, 'ID=', '">');
    $pareos_ar = returnSubstrings($pareos_sub, 'ID=', '">');
    $out['total'] = array('yes' => 0, 'no' => 0, 'abstain' => 0, 'dispensed' => 0, 'paired' => 0);
    foreach ($a_favor_ar as $mp_row) {
        $db->insertVoto($id_votacion, $mp_row, 'y');
        $name_pom = str_replace("'", "\\'", trim(get_first_string($a_favor_sub, 'prmID=' . $mp_row . '">', '</a>')));
        $names[$name_pom][$mp_row] = $mp_row;
        $out['mp']['mp_' . $mp_row]['mp_id'] = $mp_row;
        $out['mp']['mp_' . $mp_row]['vote'] = 'y';
        $name_pom2 = explode('.', $name_pom);
        $out['mp']['mp_' . $mp_row]['name'] = trim($name_pom2[1]) . '.' . $name_pom2[2];
        if ($name_pom2[0] == 'Sra') {
            $out['mp']['mp_' . $mp_row]['sex'] = 'f';
        } else {
            $out['mp']['mp_' . $mp_row]['sex'] = 'm';
        }
        $out['total']['yes']++;
    }
    foreach ($en_contra_ar as $mp_row) {
        $db->insertVoto($id_votacion, $mp_row, 'n');
        $name_pom = str_replace("'", "\\'", trim(get_first_string($en_contra_sub, 'prmID=' . $mp_row . '">', '</a>')));
        $names[$name_pom][$mp_row] = $mp_row;
        $out['mp']['mp_' . $mp_row]['mp_id'] = $mp_row;
        $out['mp']['mp_' . $mp_row]['vote'] = 'n';
        $name_pom2 = explode('.', $name_pom);
        $out['mp']['mp_' . $mp_row]['name'] = trim($name_pom2[1]) . '.' . $name_pom2[2];
        if ($name_pom2[0] == 'Sra') {
            $out['mp']['mp_' . $mp_row]['sex'] = 'f';
        } else {
            $out['mp']['mp_' . $mp_row]['sex'] = 'm';
        }
        $out['total']['no']++;
    }
    foreach ($abstencion_ar as $mp_row) {
        $db->insertVoto($id_votacion, $mp_row, 'a');
        $name_pom = str_replace("'", "\\'", trim(get_first_string($abstencion_sub, 'prmID=' . $mp_row . '">', '</a>')));
        $names[$name_pom][$mp_row] = $mp_row;
        $out['mp']['mp_' . $mp_row]['mp_id'] = $mp_row;
        $out['mp']['mp_' . $mp_row]['vote'] = 'a';
        $name_pom2 = explode('.', $name_pom);
        $out['mp']['mp_' . $mp_row]['name'] = trim($name_pom2[1]) . '.' . $name_pom2[2];
        if ($name_pom2[0] == 'Sra') {
            $out['mp']['mp_' . $mp_row]['sex'] = 'f';
        } else {
            $out['mp']['mp_' . $mp_row]['sex'] = 'm';
        }
        $out['total']['abstain']++;
    }
    foreach ($dispensados_ar as $mp_row) {
        $db->insertVoto($id_votacion, $mp_row, 'd');
        $name_pom = str_replace("'", "\\'", trim(get_first_string($dispensados_sub, 'prmID=' . $mp_row . '">', '</a>')));
        $names[$name_pom][$mp_row] = $mp_row;
        $out['mp']['mp_' . $mp_row]['mp_id'] = $mp_row;
        $out['mp']['mp_' . $mp_row]['vote'] = 'd';
        $name_pom2 = explode('.', $name_pom);
        $out['mp']['mp_' . $mp_row]['name'] = trim($name_pom2[1]) . '.' . $name_pom2[2];
        if ($name_pom2[0] == 'Sra') {
            $out['mp']['mp_' . $mp_row]['sex'] = 'f';
        } else {
            $out['mp']['mp_' . $mp_row]['sex'] = 'm';
        }
        $out['total']['dispensed']++;
    }
    foreach ($pareos_ar as $mp_row) {
        $db->insertVoto($id_votacion, $mp_row, 'p');
        $name_pom = str_replace("'", "\\'", trim(get_first_string($pareos_sub, 'prmID=' . $mp_row . '">', '</a>')));
        $names[$name_pom][$mp_row] = $mp_row;
        $out['mp']['mp_' . $mp_row]['mp_id'] = $mp_row;
        $out['mp']['mp_' . $mp_row]['vote'] = 'p';
        $name_pom2 = explode('.', $name_pom);
        $out['mp']['mp_' . $mp_row]['name'] = trim($name_pom2[1]) . '.' . $name_pom2[2];
        if ($name_pom2[0] == 'Sra') {
            $out['mp']['mp_' . $mp_row]['sex'] = 'f';
        } else {
            $out['mp']['mp_' . $mp_row]['sex'] = 'm';
        }
        $out['total']['paired']++;
        //check
        if ($table_control_number[0] == $out['total']['yes'] and $table_control_number[1] == $out['total']['no'] and $table_control_number[2] == $out['total']['abstain'] and $table_control_number[3] == $out['total']['dispensed']) {
        } else {
            $out['error'] = 'wrong sums: yes:' . $table_control_number[0] . ' vs. ' . $out['total']['yes'] . ', no:' . $table_control_number[1] . ' vs. ' . $out['total']['no'] . ', abstain:' . $table_control_number[2] . ' vs. ' . $out['total']['abstain'] . ', dispensed:' . $table_control_number[3] . ' vs. ' . $out['total']['dispensed'];
        }
    }
    //updated Votacion con pareos y ausentes
    $ausentes = 120 - $table_control_number[0] - $table_control_number[1] - $table_control_number[2] - $out['total']['paired'];
    $db->updatePareosAusentes($id_votacion, $out['total']['paired'], $ausentes);
    if (strlen($html) < 8300) {
        $out['error'] = 'small file; might have not been downloaded correctly or wrong id';
    }
    return $out;
}
        }
        foreach ($htmls as $key => $html) {
            $dom->load($html);
            $divs = $dom->find('div[class=votacionesResultado]');
            foreach ($divs as $div) {
                $as = $div->find('a');
                if (count($as) > 1) {
                    $link = $div->find('a', 1)->href;
                    $td_ar = explode("<br>", $div->find('td', 0));
                    $si_ar = explode(':', $td_ar[0]);
                    $si = trim($si_ar[1]);
                    $no_ar = explode(':', $td_ar[1]);
                    $no = trim($no_ar[1]);
                    $abst_ar = explode(':', $td_ar[2]);
                    $abst = trim($abst_ar[1]);
                    $number = get_first_string($div->innertext, 'votacion=', '&');
                    $url = "http://www.congreso.es" . $link;
                    $xml = str_replace("ISO-8859-1", "UTF-8", iconv("ISO-8859-1", "UTF-8", scraperwiki::scrape($url)));
                    $data = array('date' => $right_date->format('Y-m-d'), 'number' => $number, 'yes' => $si, 'no' => $no, 'abstain' => $abst, 'link' => $link, 'xml' => $xml);
                    scraperwiki::save_sqlite(array('date', 'number'), $data, 'division');
                }
            }
        }
        // /foreach $htmls
    }
    $date = $right_date->modify('+1 day');
    scraperwiki::save_var('last_date', $date->format('Y-m-d'));
}
/*print_r($data);

if (isset($data)) {
$dom = new simple_html_dom();
$dom->load($html);
//selects
$selects = $dom->find("select");
//options from 2nd select
$regs = $selects[1]->find("option");
foreach ((array) $regs as $reg) {
    $regions[] = $reg->value;
}
//foreach region
foreach ((array) $regions as $key => $region) {
    if ($key >= $last_region) {
        //get number of records
        $url = "http://wwwinfo.mfcr.cz/cgi-bin/ufisreg/vyber1.pl?Viewico=1&zkokraj={$region}&uzemcelek=2&Viewnao=0&useZko=0&typ=1&pocet=0";
        $html = iconv("cp1250", "UTF-8//TRANSLIT", scraperwiki::scrape($url));
        $total = trim(get_first_string($html, 'Celkem nalezeno', 'záznamů'));
        //up to number of records
        for ($i = $last_i; $i < $total; $i = $i + 20) {
            //get the html
            $url = "http://wwwinfo.mfcr.cz/cgi-bin/ufisreg/vyber1.pl?Viewico=1&zkokraj={$region}&uzemcelek=2&Viewnao=0&useZko=0&typ=1&pocet={$i}";
            $html = iconv("cp1250", "UTF-8//TRANSLIT", scraperwiki::scrape($url));
            //get dom
            $dom = new simple_html_dom();
            $dom->load($html);
            //extract the table with data
            $tables = $dom->find("table");
            $out = array('region' => $region, 'i' => $i, 'html' => $tables[2]->innertext);
            //save it
            scraperwiki::save_sqlite(array('region', 'i'), $out);
            scraperwiki::save_var('last_i', $i);
            scraperwiki::save_var('last_region', $key);
     $item['interpelation_oral'] = 0;
 }
 //interpelations written
 $url = "http://www.psp.cz/sqw/tisky.sqw?o={$term}&pi=" . $row['id'];
 $html = iconv("cp1250", "UTF-8//TRANSLIT", scraperwiki::scrape($url));
 if (strpos($html, 'Celkem nalezen')) {
     $ar = explode(' ', trim(get_first_string($html, 'Celkem nalezen', 'tisk')));
     $item['interpelation_written'] = $ar[count($ar) - 1];
 } else {
     $item['interpelation_written'] = 0;
 }
 //law proposals / návrhy zákonů
 $url = "http://www.psp.cz/sqw/tisky.sqw?o={$term}&nz=" . $row['id'];
 $html = iconv("cp1250", "UTF-8//TRANSLIT", scraperwiki::scrape($url));
 if (strpos($html, 'Celkem nalezen')) {
     $ar = explode(' ', trim(get_first_string($html, 'Celkem nalezen', 'tisk')));
     $item['proposal'] = end($ar);
 } else {
     $item['proposal'] = 0;
 }
 //speeches (number of sessions)
 $url = "http://www.psp.cz/eknih/2010ps/rejstrik/jmenny/{$row['id']}.html";
 //**********
 $html = iconv("cp1250", "UTF-8//TRANSLIT", scraperwiki::scrape($url));
 preg_match_all('/#sx/', $html, $matches);
 $item['speech_session'] = count($matches[0]);
 /*print_r($item);
   if ($i > 3)
     die();*/
 scraperwiki::save_var('last_id', $row['id']);
 scraperwiki::save_sqlite(array('id'), $item);