<?php
use_helper('LsNumber');
?>
<?php
$person = EntityTable::getPersonById($entity['id']);
$legalName = PersonTable::getLegalName($person);
$data = array('Types' => implode(', ', array_keys(EntityTable::getExtensionsForDisplay($entity, false))), 'Legal Name' => $entity['name'] == $legalName ? null : $legalName, 'Birthplace' => $person['birthplace'], 'Date of Birth' => Dateable::convertForDisplay($entity['start_date']), 'Date of Death' => Dateable::convertForDisplay($entity['end_date']), 'Gender' => PersonTable::getGenderName($person));
?>
<?php
include_partial('global/datatable', array('data' => $data, 'label_width' => isset($label_width) ? $label_width : '80px'));
protected function execute($arguments = array(), $options = array())
{
if (!$this->safeToRun()) {
print "Script already running!\n";
die;
}
$this->init($arguments, $options);
$session = $options['session'];
$start_id = $options['start_id'];
if ($session) {
$sql = 'SELECT * FROM scraper_meta WHERE scraper = ? and namespace = ? and predicate = ?';
$stmt = $this->db->execute($sql, array('OsUpdate', $session, 'last_scraped'));
$metas = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (count($metas)) {
$start_id = $metas[0]['value'];
} else {
$meta = new ScraperMeta();
$meta->scraper = 'OsUpdate';
$meta->namespace = $session;
$meta->predicate = 'last_scraped';
$meta->value = $start_id - 1;
$meta->save();
}
}
$entity_ids = $this->getEntities($options['limit'], $start_id);
foreach ($entity_ids as $entity_id) {
//$this->printDebug("*******************************");
//get person record
$sql = 'SELECT * FROM person WHERE entity_id = ?';
$stmt = $this->db->execute($sql, array($entity_id));
if (!($donorPerson = $stmt->fetch(PDO::FETCH_ASSOC))) {
if ($this->debugMode) {
print "* Can't find Person record for donor with entity_id " . $id . "; skipping...";
}
return;
}
$this->printDebug(PersonTable::getLegalName($donorPerson));
$trans = $this->getTransactions($entity_id);
$verified_donations = $this->getDonations($trans);
$out = array();
foreach ($verified_donations as $key => $subarr) {
foreach ($subarr as $subkey => $subvalue) {
$out[$subkey][$key] = $subvalue;
}
}
$verified_fields = array_map(array_unique, $out);
$trans = $this->getTransactions($entity_id, 0, 1);
$fields_to_check = array('donor_name', 'street', 'city', 'state', 'zip', 'employer_raw', 'org_raw', 'title_raw', 'gender', 'suffix');
foreach ($fields_to_check as $f) {
$this->printDebug($f . ": " . implode(",", $verified_fields[$f]));
}
$unverified_donations = $this->getDonations($trans);
$this->donationMatches = array();
$this->donationNonmatches = array();
foreach ($unverified_donations as $ud) {
if ($this->namesAreCompatible($ud, $donorPerson)) {
$mat = $this->checkForMatch($ud, $verified_donations, $fields_to_check, $verified_fields);
$ud['reason'] = $mat[1];
if ($mat[0] == 1) {
$this->donationMatches[] = $ud;
} else {
$this->donationNonmatches[] = $ud;
}
}
}
$fields_to_check[] = 'reason';
$this->printDebug("\nSUCCESSES");
foreach ($this->donationMatches as $dm) {
//mark donation matches as verified
$sql = 'UPDATE os_entity_transaction SET is_verified = 1, is_synced = (is_verified = is_processed), reviewed_at = ?, reviewed_by_user_id = ? WHERE entity_id = ? AND cycle = ? AND transaction_id = ?';
$stmt = $this->db->execute($sql, array(date('Y-m-d H:i:s'), 1, $entity_id, $dm['cycle'], $dm['row_id']));
$str = '';
foreach ($fields_to_check as $f) {
$str .= $f . ": " . $dm[$f] . "; ";
}
$this->printDebug("\t" . $str);
}
$this->printDebug("\nFAILURES");
foreach ($this->donationNonmatches as $dm) {
//mark donation non-matches as unverified
$sql = 'UPDATE os_entity_transaction SET is_verified = 0, is_synced = (is_verified = is_processed), reviewed_at = ?, reviewed_by_user_id = ? WHERE entity_id = ? AND cycle = ? AND transaction_id = ?';
$stmt = $this->db->execute($sql, array(date('Y-m-d H:i:s'), 1, $entity_id, $dm['cycle'], $dm['row_id']));
$str = '';
foreach ($fields_to_check as $f) {
$str .= $f . ": " . $dm[$f] . "; ";
}
$this->printDebug("\t" . $str);
}
$sql = 'UPDATE scraper_meta SET value = ? WHERE scraper = ? and namespace = ? and predicate = ?';
$stmt = $this->db->execute($sql, array($entity_id, 'OsUpdate', $session, 'last_scraped'));
$this->printDebug("*******************************");
}
}
public function processEntity($id, $newTrans, $oldTrans)
{
//get person names so we can make sure added donations are from the right person
$sql = 'SELECT * FROM person WHERE entity_id = ?';
$stmt = $this->db->execute($sql, array($id));
if (!($donorPerson = $stmt->fetch(PDO::FETCH_ASSOC))) {
if ($this->debugMode) {
print "* Can't find Person record for donor with entity_id " . $id . "; skipping...";
}
return;
}
if ($this->debugMode) {
print "\n=== Processing entity " . $id . " (" . PersonTable::getLegalName($donorPerson) . ") ===\n";
}
$recipients = array();
//get donations from all the newly matched transactions
$newDonations = $this->getDonations($newTrans);
foreach ($newDonations as $donation) {
if (!$this->namesAreCompatible($donorPerson, $donation)) {
if ($this->debugMode) {
print "* Skipping donation with incompatible donor name: " . $donation['donor_name'] . "\n";
}
continue;
}
$cycle = $donation['cycle'];
$recipientId = $donation['recipient_id'];
if (isset($recipients[$cycle][$recipientId]['new'])) {
$recipients[$cycle][$recipientId]['new'][] = $donation;
} else {
if (!isset($recipients[$cycle])) {
$recipients[$cycle] = array();
}
$recipients[$cycle][$recipientId] = array();
$recipients[$cycle][$recipientId]['new'] = array($donation);
$recipients[$cycle][$recipientId]['old'] = array();
}
}
//get donations from all the old transactions
$oldDonations = $this->getDonations($oldTrans);
foreach ($oldDonations as $donation) {
$cycle = $donation['cycle'];
$recipientId = $donation['recipient_id'];
if (isset($recipients[$cycle][$recipientId]['old'])) {
$recipients[$cycle][$recipientId]['old'][] = $donation;
} else {
if (!isset($recipients[$cycle])) {
$recipients[$cycle] = array();
}
$recipients[$cycle][$recipientId] = array();
$recipients[$cycle][$recipientId]['old'] = array($donation);
$recipients[$cycle][$recipientId]['new'] = array();
}
}
//if there are NO already-processed matches, and no matches to remove,
//ie, if we're going from no matches to any number of matches,
//we can delete existing donation relationships for this entity
$deleteRels = false;
if (!count($oldDonations)) {
$sql = 'SELECT COUNT(*) FROM os_entity_transaction WHERE entity_id = ? AND is_processed = 1';
$stmt = $this->db->execute($sql, array($id));
if (!$stmt->fetch(PDO::FETCH_COLUMN)) {
$deleteRels = true;
}
}
if ($deleteRels) {
if ($this->debugMode) {
print "- Removing old donation relationships...\n";
}
//first get ids
$sql = 'SELECT DISTINCT r.id FROM relationship r ' . 'LEFT JOIN fec_filing f ON (f.relationship_id = r.id) ' . 'WHERE r.entity1_id = ? AND r.category_id = ? AND r.is_deleted = 0 ' . 'AND f.id IS NOT NULL';
$stmt = $this->db->execute($sql, array($id, RelationshipTable::DONATION_CATEGORY));
$relIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
if (count($relIds)) {
//soft delete them
$sql = 'UPDATE relationship SET is_deleted = 1, updated_at = ? WHERE id IN (' . implode(',', $relIds) . ')';
$params = array(LsDate::getCurrentDateTime());
$this->db->execute($sql, $params);
//create modification records of the deletions
$sql = 'INSERT INTO modification (object_model, object_id, object_name, is_delete, created_at, updated_at) ' . 'VALUES ';
$params = array();
foreach ($relIds as $relId) {
$sql .= '(?, ?, ?, ?, ?, ?), ';
$now = LsDate::getCurrentDateTime();
$params = array_merge($params, array('Relationship', $relId, 'Relationship ' . $relId, true, $now, $now));
}
$sql = substr($sql, 0, strlen($sql) - 2);
$stmt = $this->db->execute($sql, $params);
}
}
//make sure the entity hasn't been deleted in the meantime!
$sql = 'SELECT id FROM entity WHERE id = ? AND is_deleted = 0';
$stmt = $this->db->execute($sql, array($id));
if (!$stmt->fetch(PDO::FETCH_COLUMN)) {
//skip to the end
$recipients = array();
}
//create filings/relationships for each cycle-recipient pair
foreach ($recipients as $cycle => $recipients) {
foreach ($recipients as $recipientId => $donations) {
//if it's a committee recipient, try to determine
//whether it belongs to a candidate
if (strpos($recipientId, 'C') === 0) {
$recipientId = $this->getFinalRecipientIdByCycleAndCommitteeId($cycle, $recipientId);
}
//find the entity with this recipient id, or generate a new one
if (!($recipientEntity = $this->getEntityByRecipientId($recipientId))) {
if ($this->debugMode) {
print "* Couldn't find or create entity for recipient " . $recipientId . "; skipping...\n";
}
continue;
}
//create committee entity and position relationship between it and the candidate, if necessary
//DISABLED, FOR NOW
//$this->createCampaignCommittee($recipientEntity['id'], $recipientId);
if ($this->debugMode) {
print "Updating donation relationship with " . $recipientEntity['name'] . "...\n";
}
//see if there's already a relationship
Doctrine_Manager::getInstance()->setCurrentConnection('main');
$q = LsDoctrineQuery::create()->from('Relationship r')->where('r.entity1_id = ? AND r.entity2_id = ? AND r.category_id = ?', array($id, $recipientEntity['id'], RelationshipTable::DONATION_CATEGORY));
$rel = $q->fetchOne();
//create relationship if there's not already one
if (!$rel) {
//but if there aren't any new donations, then we skip this recipient
//THIS SHOULD NOT TYPICALLY HAPPEN, BECAUSE NO NEW DONATIONS MEANS
//THERE ARE OLD DONATIONS TO REMOVE, WHICH MEANS THERE SHOULD BE
//EXISTING RELATIONSHIPS... they may have been deleted
if (!count($donations['new'])) {
if ($this->debugMode) {
print "* No relationships found, and no new donations to process, so skipping it...\n";
}
continue;
}
if ($this->debugMode) {
print "+ Creating new donation relationship\n";
}
$rel = new Relationship();
$rel->entity1_id = $id;
$rel->entity2_id = $recipientEntity['id'];
$rel->setCategory('Donation');
$rel->description1 = 'Campaign Contribution';
$rel->description2 = 'Campaign Contribution';
$rel->save();
}
//add new filings and references to the relationship
foreach ($donations['new'] as $donation) {
$filing = new FecFiling();
$filing->relationship_id = $rel->id;
$filing->amount = $donation['amount'];
$filing->fec_filing_id = $donation['fec_id'];
$filing->crp_cycle = $donation['cycle'];
$filing->crp_id = $donation['row_id'];
$filing->start_date = $donation['date'];
$filing->end_date = $donation['date'];
$filing->is_current = false;
$filing->save();
if ($this->debugMode) {
print "+ Added new FEC filing: " . $donation['fec_id'] . " (" . $donation['amount'] . ")\n";
}
//add reference if there's an fec_id
if ($donation['fec_id']) {
$ref = new Reference();
$ref->object_model = 'Relationship';
$ref->object_id = $rel->id;
$ref->source = $this->fecImageBaseUrl . $donation['fec_id'];
$ref->name = 'FEC Filing ' . $donation['fec_id'];
$ref->save();
}
}
//remove old filings from the relationship
foreach ($donations['old'] as $donation) {
if ($this->debugMode) {
print "- Deleting FEC filing: {$donation['fec_id']}, {$donation['cycle']}, {$donation['row_id']} ({$donation['amount']})\n";
}
$sql = 'DELETE FROM fec_filing WHERE relationship_id = ? AND crp_cycle = ? AND crp_id = ?';
$stmt = $this->db->execute($sql, array($rel->id, $donation['cycle'], $donation['row_id']));
}
//recompute fields based on filings
if (!$rel->updateFromFecFilings()) {
if ($this->debugMode) {
print "- Deleting donation relationship with no filings: " . $rel->id . "\n";
}
//no remaining filings for this relationship, so delete it!
$rel->delete();
} else {
if ($this->debugMode) {
print "Relationship " . $rel->id . " updated with " . $rel->filings . " filings totaling " . $rel->amount . "\n";
}
//add a reference to OS donation search for the relationship, if necessary
$sql = 'SELECT COUNT(*) FROM reference ' . 'WHERE object_model = ? AND object_id = ? AND name = ?';
$stmt = $this->db->execute($sql, array('Relationship', $rel->id, 'FEC contribution search'));
if (!$stmt->fetch(PDO::FETCH_COLUMN)) {
$ref = new Reference();
$ref->object_model = 'Relationship';
$ref->object_id = $rel->id;
$ref->source = sprintf($this->fecSearchUrlPattern, strtoupper($donorPerson['name_last']), strtoupper($donorPerson['name_first']));
$ref->name = 'FEC contribution search';
$ref->save();
if ($this->debugMode) {
print "+ Added reference to FEC contribution search\n";
}
}
}
//clear cache for recipient
LsCache::clearEntityCacheById($recipientEntity['id']);
}
}
//update os_entity_transaction
$sql = 'UPDATE os_entity_transaction SET is_processed = is_verified, is_synced = 1 WHERE entity_id = ?';
$stmt = $this->db->execute($sql, array($id));
//make sure that all removed matches result in deleted fec filings and updated relationships for this entity
$this->cleanupFecFilings($id, $oldDonations);
//update opensecrets categories based on matched donations
$this->printDebug("Updating industry categories based on matched donations...");
$newCategories = OsPerson::updateCategories($id);
foreach ($newCategories as $categoryId) {
$this->printDebug("+ Added industry category: " . $categoryId);
}
//clear cache for donor
LsCache::clearEntityCacheById($id);
}
