PHP ParlDB类代码示例

 private function video_sidebar($row, $heading_hpos, $count)
 {
     $db = new \ParlDB();
     $gid_type = $this->video_gid_type();
     $vq = $db->query("select id,adate,atime from video_timestamps where gid='uk.org.publicwhip/{$gid_type}/{$row['gid']}' and (user_id!=-1 or user_id is null) and deleted=0 order by (user_id is null) limit 1");
     $adate = $vq->field(0, 'adate');
     $time = $vq->field(0, 'atime');
     $videodb = \MySociety\TheyWorkForYou\Utility\Video::dbConnect();
     if (!$videodb) {
         return '';
     }
     $video = \MySociety\TheyWorkForYou\Utility\Video::fromTimestamp($videodb, $adate, $time);
     $start = $video['offset'];
     $out = '';
     if ($count > 1) {
         $out .= '<div class="debate__video" id="video_wrap"><div>';
         if ($row['gid'] != $this->first_gid) {
             $out .= '<p class="video-instructions">This video starts around ' . ($row['hpos'] - $heading_hpos) . ' speeches in (<a href="#g' . gid_to_anchor($row['gid']) . '">move there in text</a>)</p>';
         }
     }
     $out .= \MySociety\TheyWorkForYou\Utility\Video::object($video['id'], $start, "{$gid_type}/{$row['gid']}");
     $flashvars = 'gid=' . "{$gid_type}/{$row['gid']}" . '&amp;file=' . $video['id'] . '&amp;start=' . $start;
     $out .= "<strong>Embed this video</strong><p class='video-instructions'>Copy and paste this code on your website</p><input readonly onclick='this.focus();this.select();' type='text' name='embed' size='40' value=\"<embed src='http://www.theyworkforyou.com/video/parlvid.swf' width='320' height='230' allowfullscreen='true' allowscriptaccess='always' flashvars='{$flashvars}'></embed>\">";
     if ($count > 1) {
         $out .= '<p class="hide-video"><a href="" onclick="return showVideo();">Hide</a></p>';
         $out .= '</div></div>';
         $out .= '<div id="video_show" class="show-video" style="display:none;">
 <p style="margin:0"><a href="" onclick="return hideVideo();">Show video</a></p></div>';
     }
     return $out;
 }

function api_getMPsInfo_id($ids)
{
    $fields = preg_split('#\\s*,\\s*#', get_http_var('fields'), -1, PREG_SPLIT_NO_EMPTY);
    $ids = preg_split('#\\s*,\\s*#', $ids, -1, PREG_SPLIT_NO_EMPTY);
    $safe_ids = array(0);
    foreach ($ids as $id) {
        if (ctype_digit($id)) {
            $safe_ids[] = $id;
        }
    }
    $ids = join(',', $safe_ids);
    $db = new ParlDB();
    $last_mod = 0;
    $q = $db->query("select person_id, data_key, data_value, lastupdate from personinfo\n\t\twhere person_id in (" . $ids . ")");
    if ($q->rows()) {
        $output = array();
        for ($i = 0; $i < $q->rows(); $i++) {
            $data_key = $q->field($i, 'data_key');
            if (count($fields) && !in_array($data_key, $fields)) {
                continue;
            }
            $pid = $q->field($i, 'person_id');
            $output[$pid][$data_key] = $q->field($i, 'data_value');
            $time = strtotime($q->field($i, 'lastupdate'));
            if ($time > $last_mod) {
                $last_mod = $time;
            }
        }
        $q = $db->query("select memberinfo.*, person_id from memberinfo, member\n\t\t\twhere memberinfo.member_id=member.member_id and person_id in (" . $ids . ")\n\t\t\torder by person_id,member_id");
        if ($q->rows()) {
            $oldmid = 0;
            $count = -1;
            for ($i = 0; $i < $q->rows(); $i++) {
                $data_key = $q->field($i, 'data_key');
                if (count($fields) && !in_array($data_key, $fields)) {
                    continue;
                }
                $mid = $q->field($i, 'member_id');
                $pid = $q->field($i, 'person_id');
                if (!isset($output[$pid]['by_member_id'])) {
                    $output[$pid]['by_member_id'] = array();
                }
                if ($oldmid != $mid) {
                    $count++;
                    $oldmid = $mid;
                    $output[$pid]['by_member_id'][$count]['member_id'] = $mid;
                }
                $output[$pid]['by_member_id'][$count][$data_key] = $q->field($i, 'data_value');
                $time = strtotime($q->field($i, 'lastupdate'));
                if ($time > $last_mod) {
                    $last_mod = $time;
                }
            }
        }
        ksort($output);
        api_output($output, $last_mod);
    } else {
        api_error('Unknown person ID');
    }
}

function suggest_alerts($email,$criteria,$maxresults)
{
    $db = new ParlDB;
    if(stripos($criteria,"speaker:")==0)  //speaker only
    {
        // find emails who follow this speaker
        // find the speakers followed by those email
        // return their most frequently followed speakers, not followed by the searcher
        $sql ="SELECT count(*) AS c, criteria FROM alerts ";  // select and count criteria
        $sql.="WHERE email = any (SELECT email FROM alerts WHERE criteria like '%$criteria%') ";  // from emails which have the provided criteria/pid
        $sql.="AND LENGTH(criteria)=13.AND LEFT(criteria,8)='speaker:' ";  // filter in simple speaker alerts 'speaker:nnnnn'
        $sql.="AND NOT(criteria=ANY(SELECT criteria FROM alerts WHERE email='$email')) "; // disregard any alert of this emailer (already following)
        //$sql.="AND email like '%foo.test%' "; // filter in my test alerts  // REMOVE ME
        $sql.="GROUP BY criteria ORDER BY c DESC";  // most commo first
        $q=$db->query($sql);
        $resultcount=$q->rows();
        if($resultcount>0)    // if something was returned
	print "<p>You may also be interested in being alerted when these people speak too.</p>";
        {
            if($resultcount>$maxresults) $resultcount=$maxresults;  // cap results

            for($i=0; $i<$resultcount; $i++)  // iterate through results
            {
                if($q->field($i,'c')>1) // ignore suggestion where only one other has an alert for
                {
                    $pid=substr($q->field($i,'criteria'),-5); // extract members PID
                    $member=new MEMBER(array('person_id'=>$pid)); 
                    print '<p><a href="' . WEBPATH . 'alert/?r=1&only=1&amp;pid='.$member->person_id().'"><strong>Email me whenever '. $member->full_name() . ' speaks</strong></a></p>';
                }
            }
        }
    }
}

function _api_getMembers_output($sql, $params)
{
    global $parties;
    $db = new ParlDB();
    $q = $db->query($sql, $params);
    $output = array();
    $last_mod = 0;
    for ($i = 0; $i < $q->rows(); $i++) {
        $pid = $q->field($i, 'person_id');
        $row = array('member_id' => $q->field($i, 'member_id'), 'person_id' => $pid, 'name' => html_entity_decode(member_full_name($q->field($i, 'house'), $q->field($i, 'title'), $q->field($i, 'given_name'), $q->field($i, 'family_name'), $q->field($i, 'lordofname'))), 'party' => isset($parties[$q->field($i, 'party')]) ? $parties[$q->field($i, 'party')] : $q->field($i, 'party'));
        if ($q->field($i, 'house') != 2) {
            $row['constituency'] = $q->field($i, 'constituency');
        }
        $output[$pid] = $row;
        $time = strtotime($q->field($i, 'lastupdate'));
        if ($time > $last_mod) {
            $last_mod = $time;
        }
    }
    $pids = array_keys($output);
    if (count($pids)) {
        $q = $db->query('SELECT person, dept, position, from_date, to_date FROM moffice
            WHERE to_date="9999-12-31" AND person IN (' . join(',', $pids) . ')');
        for ($i = 0; $i < $q->rows(); $i++) {
            $row = $q->row($i);
            $pid = $row['person'];
            unset($row['person']);
            $output[$pid]['office'][] = $row;
        }
    }
    $output = array_values($output);
    api_output($output, $last_mod);
}

function wikipedize($source)
{
    $was_array = false;
    if (is_array($source)) {
        $source = join('|||', $source);
        $was_array = true;
    }
    # Set up various variables
    $capsword = "[A-Z][a-zA-Z'0-9]*";
    # not starting with number, as catches too much
    $fillerwords = "of|and|in|on|under|the";
    $middlewordre = "(?:{$capsword}|{$fillerwords})\\s*";
    $endwordre = "(?:{$capsword})\\s*";
    # and, of etc. can't appear at ends
    # Match either "Two Endwords" or "Endword and Some Middle Words"
    $greedyproperre = "/\\b{$endwordre}(?:{$middlewordre})*{$endwordre}\\b/ms";
    # Match without filler words (so if you have a phrase like
    # "Amnesty International and Human Rights Watch" you also get both parts
    # separately "Amnesty International" and "Human Rights Watch")
    $frugalproperre = "/\\b(?:{$endwordre}){2,}\\b/ms";
    # And do a greedy without the first word of a sentence
    $greedynotfirst = "/\\.\\s+\\S+\\s+({$endwordre}(?:{$middlewordre})*{$endwordre})\\b/ms";
    preg_match_all($greedyproperre, $source, $propernounphrases1);
    preg_match_all($frugalproperre, $source, $propernounphrases2);
    preg_match_all($greedynotfirst, $source, $propernounphrases3);
    # Three Letter Acronyms
    preg_match_all("/\\b[A-Z]{2,}/ms", $source, $acronyms);
    # We don't want no steenking duplicates
    $phrases = array_unique(array_merge($propernounphrases1[0], $propernounphrases2[0], $propernounphrases3[1], $acronyms[0]));
    # Sort into order, largest first
    usort($phrases, "lensort");
    foreach ($phrases as $i => $phrase) {
        $phrases[$i] = mysql_real_escape_string(str_replace(' ', '_', trim($phrase)));
    }
    # Open up a db connection, and whittle our list down even further, against
    # the real titles.
    $matched = array();
    $db = new ParlDB();
    $source = explode('|||', $source);
    $q = $db->query("SELECT title FROM titles WHERE title IN ('" . join("','", $phrases) . "')");
    for ($i = 0; $i < $q->rows(); $i++) {
        $wikistring = $q->field($i, 'title');
        $phrase = str_replace('_', ' ', $wikistring);
        # See if already matched a string this one is contained within
        foreach ($matched as $got) {
            if (strstr($got, $phrase)) {
                continue 2;
            }
        }
        # Go ahead
        twfy_debug("WIKIPEDIA", "Matched '{$phrase}'");
        # 1 means only replace one match for phrase per paragraph
        $source = preg_replace("/{$phrase}/", "<a href=\"http://en.wikipedia.org/wiki/{$wikistring}\">{$phrase}</a>", $source, 1);
        array_push($matched, $phrase);
    }
    if (!$was_array) {
        $source = join('|||', $source);
    }
    return $source;
}

function create_key($commercial, $reason)
{
    global $THEUSER;
    $key = auth_ab64_encode(random_bytes(16));
    $db = new ParlDB();
    $db->query('INSERT INTO api_key (user_id, api_key, commercial, created, reason) VALUES
		(' . $THEUSER->user_id() . ', "' . $key . '", ' . mysql_escape_string($commercial) . ', NOW(), "' . mysql_escape_string($reason) . '")');
}

function api_check_key($key)
{
    $db = new ParlDB();
    $q = $db->query('SELECT user_id FROM api_key WHERE api_key="' . mysql_real_escape_string($key) . '"');
    if (!$q->rows()) {
        return false;
    }
    return true;
}

function api_getPerson_id($id)
{
    $db = new ParlDB();
    $q = $db->query("select * from member\n        where person_id = '" . mysql_real_escape_string($id) . "'\n        order by left_house desc");
    if ($q->rows()) {
        _api_getPerson_output($q);
    } else {
        api_error('Unknown person ID');
    }
}

function api_getConstituencies($date = 'now()')
{
    $db = new ParlDB();
    $q = $db->query('select cons_id, name from constituency
		where main_name and from_date <= date(' . $date . ') and date(' . $date . ') <= to_date');
    $output = array();
    for ($i = 0; $i < $q->rows(); $i++) {
        $output[] = array('name' => html_entity_decode($q->field($i, 'name')));
    }
    api_output($output);
}

function create_key($commercial, $reason, $estimated_usage)
{
    global $THEUSER;
    $key = auth_ab64_encode(urandom_bytes(16));
    $db = new ParlDB();
    if ($commercial == '') {
        $commercial = 0;
    }
    $db->query('INSERT INTO api_key (user_id, api_key, commercial, created, reason, estimated_usage) VALUES
        (:user_id, :key, :commercial, NOW(), :reason, :estimated_usage)', array(':user_id' => $THEUSER->user_id(), ':key' => $key, ':commercial' => $commercial, ':reason' => $reason, ':estimated_usage' => $estimated_usage));
}

function recordEvent($bucket, $event, $data = null, $timer = null)
{
    // Hook up to the DB
    $db = new \ParlDB();
    $db->query('
        INSERT INTO `research_qual2_log`
        (`time`, `page`, `bucket`, `event`, `data`, `timer`)
        VALUES
        (:timestamp, :page, :bucket, :event, :data, :timer)
    ', array('timestamp' => time(), 'page' => $_POST['page'], 'bucket' => (int) $bucket, 'event' => $event, 'data' => $data, 'timer' => (int) $timer));
}

function get_person_id($c) {
	$db = new ParlDB;
	if ($c == '') return false;
	if ($c == 'Orkney ') $c = 'Orkney & Shetland';
	$n = normalise_constituency_name($c); if ($n) $c = $n;
	$q = $db->query("SELECT person_id FROM member
		WHERE constituency = '" . mysql_real_escape_string($c) . "'
		AND left_reason = 'still_in_office' AND house=1");
	if ($q->rows > 0)
		return $q->field(0, 'person_id');
	return false;
}

function day_speeches($search, $type, $date)
{
    $search = "{$search} date:{$date} section:{$type} groupby:speech";
    global $SEARCHENGINE, $want;
    $SEARCHENGINE = new SEARCHENGINE($search);
    $db = new ParlDB();
    $q = $db->query("select hpos from hansard where gid = :gid", array(':gid' => 'uk.org.publicwhip/' . $want['gid']));
    $want['hpos'] = $q->field(0, 'hpos');
    $LIST = new DEBATELIST();
    $args = array('s' => $search, 'p' => 1, 'num' => 200, 'pop' => 1, 'o' => 'd');
    $LIST->display('search_video', $args, 'html');
}

function api_check_key($key)
{
    $db = new ParlDB();
    $q = $db->query('SELECT user_id, disabled FROM api_key WHERE api_key = :key', array(':key' => $key));
    if (!$q->rows()) {
        return false;
    }
    if ($q->field(0, 'disabled')) {
        return 'disabled';
    }
    return true;
}

function api_check_key($key)
{
    $db = new ParlDB();
    $q = $db->query('SELECT user_id, disabled FROM api_key WHERE api_key="' . mysql_escape_string($key) . '"');
    if (!$q->rows()) {
        return false;
    }
    if ($q->field(0, 'disabled')) {
        return 'disabled';
    }
    return true;
}