protected function convertRelationship($id, $oldEntity2Id, $newEntity2Id)
{
$now = LsDate::getCurrentDateTime();
//update relationship
$sql = 'UPDATE relationship r SET entity2_id = ?, last_user_id = ?, updated_at = ? WHERE id = ?';
if (!$this->testMode) {
$stmt = $this->db->execute($sql, array($newEntity2Id, 1, $now, $id));
if (!$stmt->rowCount()) {
throw new Exception("Couldn't update relationship " . $id);
}
}
//create modification record
$sql = 'INSERT INTO modification (object_model, object_id, object_name, created_at, updated_at) ' . 'VALUES (?, ?, ?, ?, ?)';
if (!$this->testMode) {
$stmt = $this->db->execute($sql, array('Relationship', $id, 'Relationship ' . $id, $now, $now));
if (!$stmt->rowCount()) {
throw new Exception("Couldn't insert modification record for relationship " . $id);
}
}
//create modification_field record
$modId = $this->db->lastInsertId('modification');
$sql = 'INSERT INTO modification_field (modification_id, field_name, old_value, new_value) VALUES (?, ?, ?, ?)';
if (!$this->testMode) {
$stmt = $this->db->execute($sql, array($modId, 'entity2_id', $oldEntity2Id, $newEntity2Id));
if (!$stmt->rowCount()) {
throw new Exception("Couldn't insert modification_field record for relationship " . $id);
}
}
if ($this->debugMode) {
print "Converted relationship " . $id . "\n";
}
}
public function save(Doctrine_Connection $conn = null, $updateList = true, $updateEntity = true)
{
if ($conn == null) {
$conn = $this->_table->getConnection();
}
try {
$conn->beginTransaction();
$ret = parent::save($conn);
if ($updateList) {
$this->LsList->last_user_id = LsVersionableListener::getUserId();
$this->LsList->updated_at = LsDate::getCurrentDateTime();
$this->LsList->save();
}
if ($updateEntity) {
$this->Entity->last_user_id = LsVersionableListener::getUserId();
$this->Entity->updated_at = LsDate::getCurrentDateTime();
$this->Entity->save();
}
$conn->commit();
} catch (Exception $e) {
$conn->rollback();
throw $e;
}
return $ret;
}
static function addCategory($id, $categoryId, $source = null)
{
$db = LsDb::getDbConnection();
$sql = "INSERT INTO os_entity_category (entity_id, category_id, source, created_at, updated_at) " . "VALUES (?, ?, ?, ?, ?) " . "ON DUPLICATE KEY UPDATE updated_at = ?";
$now = LsDate::getCurrentDateTime();
return $stmt = $db->execute($sql, array($id, $categoryId, $source, $now, $now, $now));
}
static function convertForDisplay($date, $separator = '-', $abbreviate = false)
{
if ($date === null) {
return null;
} else {
if (!LsDate::validate($date)) {
throw new Exception("Can't convert: invalid date");
}
}
list($year, $month, $day) = explode('-', $date);
if (intval($year) < 1930) {
$abbreviate = false;
}
if (intval($year) > 0 && intval($month) > 0 && intval($day) > 0) {
$ret = date('M j \'y', strtotime($date));
} elseif (intval($year) > 0 && intval($month) > 0) {
//the month needs to be incremented because strtotime interprets '2008-12-00' as '2008-11-31'
$year = $month > 10 ? $year + 1 : $year;
$month = ($month + 1) % 12;
$ret = date('M \'y', strtotime(implode('-', array($year, $month, $day))));
} elseif ($year) {
if ($abbreviate) {
$ret = '\'' . substr($year, 2, 2);
} else {
$ret = $year;
}
} else {
$ret = null;
}
return $ret;
}
public function executeMatch($request)
{
if ($request->isMethod('post')) {
$this->checkArticle($request);
//get verified entity ids
$oldEntities = LsDoctrineQuery::create()->select('ae.entity_id')->from('ArticleEntity ae')->leftJoin('ae.Entity e')->where('ae.article_id = ?', $this->article->id)->andWhere('ae.is_verified = 1')->andWhere('e.is_deleted = 0')->fetchAll(PDO::FETCH_COLUMN);
//get all submitted entity ids
$newEntities = array_unique($request->getParameter('entity_ids', array()));
//compute changes
$verifiedEntities = array_diff($newEntities, $oldEntities);
$unverifiedEntities = array_diff($oldEntities, $newEntities);
//get user and time
$userId = $this->getUser()->getGuardUser()->id;
$time = LsDate::getCurrentDateTime();
//save changes
LsDoctrineQuery::create()->update('ArticleEntity')->set('is_verified', '?', true)->set('reviewed_by_user_id', $userId)->set('reviewed_at', '?', array($time))->where('article_id = ?', $this->article->id)->andWhereIn('entity_id', $verifiedEntities)->execute();
LsDoctrineQuery::create()->update('ArticleEntity')->set('is_verified', '?', false)->set('reviewed_by_user_id', $userId)->set('reviewed_at', '?', array($time))->where('article_id = ?', $this->article->id)->andWhereIn('entity_id', $unverifiedEntities)->execute();
$this->article->description = $request->getParameter('description');
$this->article->reviewed_by_user_id = $userId;
$this->article->reviewed_at = $time;
$this->article->save();
$this->redirect('article/admin');
}
}
static function compare(LsDate $date1, LsDate $date2)
{
//if exactly one of the dates are blank
if ($date1->getYear() == '0000' xor $date2->getYear() == '0000') {
return self::COMPARE_UNKNOWN;
}
if ($date1->_year > $date2->_year) {
return self::COMPARE_AFTER;
} elseif ($date1->_year < $date2->_year) {
return self::COMPARE_BEFORE;
} else {
//years are equal, gotta look closer
if ($date1->_month > $date2->_month && $date2->_month) {
//first month is greater than nonzero second month
return self::COMPARE_AFTER;
} elseif ($date1->_month < $date2->_month && $date1->_month) {
//second month is greater than nonzero first month
return self::COMPARE_BEFORE;
} elseif ($date1->_month == $date2->_month) {
//months are equal, gotta look closer
if ($date1->_day > $date2->_day && $date2->_day) {
//first day is greater than nonzero second day
return self::COMPARE_AFTER;
} elseif ($date1->_day < $date2->_day && $date1->_day) {
//second day is greater than nonzero first day
return self::COMPARE_BEFORE;
} elseif ($date1->_day == $date2->_day) {
//same dates!
return self::COMPARE_SAME;
} else {
//days are unequal but one of them is zero
return self::COMPARE_UNKNOWN;
}
} else {
//months are unequal but one of them is zero
return self::COMPARE_UNKNOWN;
}
}
}
function import(Entity $person, $possible_persons)
{
//loop through the people we found. usually just one.
foreach ($possible_persons as $possible_person) {
$this->printDebug('Query returned ' . count($possible_person) . ' person named ' . $possible_person->name);
//this person does not provide education. we skip
if (count($possible_person->education)) {
$this->printDebug('Education found');
} else {
$this->printDebug('No education history found');
continue;
}
//get employement info for this possible match
$possible_person_bio = $possible_person->summary;
if (count($possible_person->employment_history)) {
foreach ($possible_person->employment_history as $employment) {
$possible_person_bio .= ' ' . $employment->company . " ";
}
$this->printDebug('Employment found');
} else {
$this->printDebug('No employment history found');
continue;
}
//get employment info for the person in our database
$relationship_orgs = $person->getRelatedEntitiesQuery('Org', RelationshipTable::POSITION_CATEGORY, null, null, null, false, 1)->execute();
$person_bio = $person->summary;
foreach ($relationship_orgs as $org) {
$person_bio .= ' ' . $org->name;
}
//lets see how many matches we get
$matches = LsLanguage::getCommonPronouns($person_bio, trim($possible_person_bio), LsLanguage::$business);
if (count($matches)) {
foreach ($possible_person->education as $school) {
$school->institution = mb_convert_encoding($school->institution, 'UTF-8');
$school->institution = preg_replace('//isu', ' ', $school->institution);
$this->printDebug('Looking for the school: ' . $school->institution);
$current_school = EntityTable::findByAlias($school->institution, $context = 'bw_school');
//find school
if ($current_school) {
$this->printDebug('Found school');
} else {
$current_school = EntityTable::getByExtensionQuery(array('Org', 'School'))->addWhere('LOWER(org.name) LIKE ?', '%' . strtolower($school->institution) . "%")->fetchOne();
if (!$current_school) {
$new_school = new Entity();
$new_school->addExtension('Org');
$new_school->addExtension('School');
$new_school->name = $school->institution;
$wikipedia = new LsWikipedia();
$wikipedia->request($school->institution);
if ($wikipedia->execute() && !$wikipedia->isDisambiguation()) {
$info_box = $wikipedia->getInfoBox();
if (isset($info_box['students']) && preg_match('/([\\d\\,]{2,})/isu', $info_box['students']['clean'], $match)) {
$new_school->students = LsNumber::clean($match[1]);
} else {
$student_types = array('undergrad', 'postgrad', 'grad', 'doctoral');
$num_students = 0;
foreach ($student_types as $st) {
if (isset($info_box[$st]) && preg_match('/([\\d\\,]{2,})/isu', $info_box[$st]['clean'], $match)) {
$num_students += LsNumber::clean($match[1]);
}
}
if ($num_students > 0) {
$new_school->students = $num_students;
}
}
if (isset($info_box['faculty']) && preg_match('/([\\d\\,]{2,})/isu', $info_box['faculty']['clean'], $match)) {
$new_school->faculty = LsNumber::clean($match[1]);
}
if (isset($info_box['type'])) {
if (stristr($info_box['type']['clean'], 'public')) {
$new_school->is_private = 0;
} else {
if (stristr($info_box['type']['clean'], 'private')) {
$new_school->is_private = 1;
}
}
}
if (isset($info_box['endowment'])) {
if (preg_match('/(\\$[\\d\\,\\.\\s]+)(million|billion)/isu', $info_box['endowment']['clean'], $match)) {
if (strtolower($match[2]) == 'billion') {
$factor = 1000000000;
} else {
$factor = 1000000;
}
$new_school->endowment = LsNumber::formatDollarAmountAsNumber($match[1], $factor);
}
}
if (isset($info_box['established'])) {
$year = null;
if ($date = LsDate::convertDate($info_box['established']['clean'])) {
$new_school->start_date = $date;
} else {
if (preg_match('/\\b(\\d\\d\\d\\d)\\b/isu', $info_box['established']['clean'], $match)) {
$new_school->start_date = $match[1];
}
}
}
$summary = trim($wikipedia->getIntroduction());
$summary = preg_replace('/\\n\\s*\\n/isu', '', $summary);
if (strlen($summary) > 10) {
$new_school->summary = $summary;
}
$new_school->save();
$new_school->addReference($source = $wikipedia->getUrl(), $excerpt = null, $fields = array('summary'), $name = 'Wikipedia');
} else {
$new_school->save();
}
$current_school = $new_school;
$this->printDebug('Adding new school');
}
$alias = new Alias();
$alias->name = $school->institution;
$alias->context = 'bw_school';
$alias->Entity = $current_school;
$alias->save();
}
//find degree
$degree = null;
if (!($degree = DegreeTable::getByText($school->degree))) {
$degree = DegreeTable::addDegree($school->degree);
$this->printDebug('Adding new degree');
}
//find relationship
$relationship = null;
$relationships = $person->getRelationshipsWithQuery($current_school, RelationshipTable::EDUCATION_CATEGORY)->execute();
foreach ($relationships as $existing_relationship) {
if ($existing_relationship->degree_id == $degree->id) {
$relationship = $existing_relationship;
break;
}
}
if ($relationship) {
$this->printDebug('Relationship between person and school exists');
} else {
$relationship = new Relationship();
$relationship->Entity1 = $person;
$relationship->Entity2 = $current_school;
$relationship->description1 = 'student';
$relationship->is_current = 0;
if ($school->year) {
$relationship->end_date = $school->year;
}
$relationship->setCategory('Education');
$this->printDebug('Creating new relationship between person and school');
}
//save
$relationship->save();
//add degree and reference
if ($relationship->degree_id == null) {
$reference_name = strstr($school->source, 'wikipedia') ? "Wikipedia" : "BusinessWeek";
$relationship->Degree = $degree;
$relationship->save();
$relationship->addReference($source = $school->source, $excerpt = null, $fields = array('degree_id'), $name = $reference_name, $detail = null, $date = null);
$this->printDebug('Adding degree and reference');
}
}
} else {
$this->printDebug('No organization matches');
return false;
}
}
return true;
}
static function mergeField($fieldName, $existingValue, $newValue)
{
if ($newValue === null) {
return $existingValue;
} elseif ($existingValue === null) {
return $newValue;
} elseif ($existingValue === $newValue) {
return $existingValue;
}
$appendWithNewlineFields = array('summary', 'notes');
$appendWithSpaceFields = array('name_suffix', 'name_prefix');
$dateFields = array('start_date', 'end_date');
$nameFields = array('name_first', 'name_middle');
$numberFields = array('employees', 'revenue', 'endowment');
if (in_array($fieldName, $appendWithNewlineFields)) {
return $existingValue . "\n\n" . $newValue;
} elseif (in_array($fieldName, $appendWithSpaceFields)) {
return $existingValue . ' ' . $newValue;
} elseif (in_array($fieldName, $dateFields)) {
$existingDate = new LsDate($existingValue);
$newDate = new LsDate($newValue);
if ($existingDate->howSpecific() > $newDate->howSpecific()) {
return $existingDate->format();
} elseif ($existingDate->howSpecific() < $newDate->howSpecific()) {
return $newDate->format();
} else {
if ($existingDate->howSpecific() == LsDate::YEAR_SPECIFIC) {
return LsDate::lessOrEqual($existingDate, $newDate) ? $existingDate : $newDate;
} else {
return $existingDate;
}
}
} elseif (in_array($fieldName, $nameFields)) {
return strlen($existingValue) >= strlen($newValue) || strlen($existingValue) !== 1 ? $existingValue : $newValue;
} elseif (in_array($fieldName, $numberFields)) {
return $existingValue == 0 ? $newValue : $existingValue;
} else {
return $existingValue;
}
}
static function addAlias($id, $alias)
{
$db = LsDb::getDbConnection();
$sql = "INSERT IGNORE INTO alias (entity_id, name, context, updated_at, created_at) VALUES (?, ?, ?, ?, ?)";
$now = LsDate::getCurrentDateTime();
return $stmt = $db->execute($sql, array($id, $alias, "opensecrets", $now, $now));
}
public function updateDateRange($date, $use_year = true)
{
$date = new LsDate($date);
if ($use_year) {
$date->setDay('00');
$date->setMonth('00');
}
if ($date->getYear() == '0000') {
return false;
}
//start date and end date both null, make start date the date
if (!$this->end_date && !$this->start_date) {
$this->start_date = (string) $date;
$this->end_date = (string) $date;
return true;
}
$end_date = new LsDate($this->end_date);
$start_date = new LsDate($this->start_date);
$start_date_comp = LsDate::compare($date, $start_date);
$end_date_comp = LsDate::compare($date, $end_date);
//if date is the same as either start date or end date, make no changes
if ($start_date_comp == LsDate::COMPARE_SAME || $end_date_comp === LsDate::COMPARE_SAME) {
return false;
} else {
if ($start_date_comp == LsDate::COMPARE_BEFORE) {
$this->start_date = (string) $date;
return true;
} else {
if ($end_date_comp == LsDate::COMPARE_AFTER) {
$this->end_date = (string) $date;
return true;
}
}
}
//no changes have been made
return false;
}
static function logUpdate($id)
{
$db = LsDb::getDbConnection();
$now = LsDate::getCurrentDateTime();
$sql = "INSERT INTO task_meta (task, namespace, predicate, value, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?) " . "ON DUPLICATE KEY UPDATE value = ?";
$params = array(__CLASS__, $id, self::$logKey, $now, $now, $now, $now);
return $stmt = $db->execute($sql, $params);
}
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);
}
private function importExecutiveRelationship($entityId, $person_arr, $title = null, $current = null)
{
if (!in_array($entityId, $this->old_exec_entity_ids)) {
$rel = new Relationship();
$rel->entity1_id = $entityId;
$rel->entity2_id = $this->entity->id;
$rel->setCategory('Position');
$rel->is_current = $current;
$rel->is_board = 0;
$rel->is_executive = 1;
if ($title != '') {
$rel->description1 = $title;
}
//Form 3s let us set a start date
if ($person_arr['formName'] == 'Form 3' && $person_arr['date']) {
//filing date could be innacurate, so only use month
$date = LsDate::formatFromText($person_arr['date']);
$rel->start_date = preg_replace('/-\\d\\d$/', '-00', $date);
}
if (!$this->testMode) {
$rel->save();
//save sources
$rel->addReference($person_arr['readableXmlUrl'], null, null, $this->entity->name . ' ' . $person_arr['formName'], null, $person_arr['date']);
if (isset($this->filing_url) && isset($this->filing_name)) {
$rel->addReference($this->filing_url, null, null, $this->entity->name . " " . $this->filing_name, null, $this->filing_date);
}
}
$this->printDebug("+ Relationship created: " . $rel->id . " (" . $rel->description1 . ")");
}
}
static function getDisambiguated(LsDatespan $span)
{
$ret = new LsDatespan();
$ret->setStart(LsDate::getDisambiguatedAsStart($span->getStart()));
$ret->setEnd(LsDate::getDisambiguatedAsEnd($span->getEnd()));
return $ret;
}
private function getTransactionDetails($org, $fedspending_id)
{
if (!$this->browser->get($this->fedSpendingSearchUrl, array('record_id' => $fedspending_id, 'datype' => 'X', 'detail' => '4'))->responseIsError()) {
$response_url = $this->browser->getUrlInfo();
$response_url_str = "http://" . $response_url['host'] . $response_url['path'] . "?" . $response_url['query'];
$this->printDebug("Transaction #{$fedspending_id} http://" . $response_url['host'] . $response_url['path'] . "?" . $response_url['query']);
$text = $this->browser->getResponseText();
$this->browser->setResponseText(iconv('ISO-8859-1', 'UTF-8', $this->browser->getResponseText()));
$xml = $this->browser->getResponseXml();
} else {
$this->printDebug("Couldn't get " . $this->fedSpendingSearchUrl);
return false;
}
$obligated_amount = $xml->xpath('/fedspendingSearchResults/data/record/amounts/obligatedAmount');
$maj_agency_cat = $xml->xpath('/fedspendingSearchResults/data/record/purchaser_information/maj_agency_cat');
$contracting_agency = $xml->xpath('/fedspendingSearchResults/data/record/purchaser_information/contractingOfficeAgencyID');
$psc_cat = $xml->xpath('/fedspendingSearchResults/data/record/product_or_service_information/psc_cat');
$signed_date = $xml->xpath('/fedspendingSearchResults/data/record/contract_information/signedDate');
$descriptionOfContractRequirement = $xml->xpath('/fedspendingSearchResults/data/record/contract_information/descriptionOfContractRequirement');
$current_completion_date = $xml->xpath('/fedspendingSearchResults/data/record/contract_information/currentCompletionDate');
$state_code = $xml->xpath('/fedspendingSearchResults/data/record/principal_place_of_performance/stateCode');
$place_of_performance_congressional_district = $xml->xpath('/fedspendingSearchResults/data/record/principal_place_of_performance/placeOfPerformanceCongressionalDistrict');
foreach ($obligated_amount as $key => $dont_use) {
$gov_name = $this->cleanGovtBodyName($maj_agency_cat[$key]);
$gov_agency = $this->getGovernmentBodyEntity($gov_name, $maj_agency_cat[$key]);
if ($gov_agency) {
//$this->printDebug("Found existing Government Agency" . $gov_agency->name);
} else {
$gov_name = $this->cleanGovtBodyName($maj_agency_cat[$key]);
$this->printDebug($gov_name);
$gov_agency = $this->addGovernmentBodyEntity($gov_name, $maj_agency_cat[$key]);
$this->printDebug("Creating new Government Agency: " . $gov_agency->name);
$gov_agency->addReference(self::$baseContractorUrl . $org->fedspending_id, null, array('name', 'parent_id'), 'FedSpending.org');
}
if (!$gov_agency) {
$this->printDebug("Error creating Government Agency");
return false;
}
$sub_agency_name = $this->cleanGovtBodyName($contracting_agency[$key]);
$sub_agency = $this->getGovernmentBodyEntity($sub_agency_name, $contracting_agency[$key]);
if ($sub_agency_name == $gov_agency->name) {
$sub_agency = $gov_agency;
}
if (!$sub_agency) {
$sub_agency = $this->addGovernmentBodyEntity($sub_agency_name, $contracting_agency[$key], $gov_agency->id);
$this->printDebug("Creating new sub-agency: " . $sub_agency->name);
$sub_agency->addReference(self::$baseContractorUrl . $org->fedspending_id, null, array('name', 'parent_id'), 'FedSpending.org');
}
if (!$sub_agency) {
$this->printDebug("Error creating Government Agency");
return false;
}
try {
$district = null;
$state = explode(': ', $state_code[$key]);
$federal_district = explode(': ', $place_of_performance_congressional_district[$key]);
$state = $state[0];
$federal_district = $federal_district[0];
$filing = new FedspendingFiling();
$filing->goods = $descriptionOfContractRequirement[$key];
$filing->amount = abs($obligated_amount[$key]);
if ($filing->amount < 1000) {
$this->printDebug('amount under $1000, rolling back');
return 'under_1000';
}
//$this->printDebug("state: " . $state . " and district: " . $federal_district);
if ($district = PoliticalDistrictTable::getFederalDistrict($state, $federal_district)) {
//$this->printDebug("found " . $district->id);
$filing->District = $district;
} elseif (trim($state) && trim($federal_district)) {
try {
$district = PoliticalDistrictTable::addFederalDistrict($state, $federal_district);
$this->printDebug("Adding District " . $state . " #" . $district->id);
$filing->District = $district;
} catch (Exception $e) {
throw $e;
}
}
$filing->fedspending_id = $fedspending_id;
$filing->start_date = LsDate::formatFromText($signed_date[$key]);
$filing->end_date = LsDate::formatFromText($current_completion_date[$key]);
$relationship = null;
if ($relationship = $org->getRelationshipsWithQuery($sub_agency, RelationshipTable::TRANSACTION_CATEGORY)->fetchOne()) {
$relationship->addFedspendingFiling($filing);
} else {
$relationship = new Relationship();
$relationship->Entity1 = $org;
$relationship->Entity2 = $sub_agency;
$relationship->setCategory('Transaction');
$relationship->description1 = 'Contractor';
$relationship->description2 = 'Client';
$relationship->save();
$relationship->addReference(self::$baseContractorUrl . $org->fedspending_id, null, array('start_date', 'end_date', 'amount', 'goods'), 'FedSpending.org');
$relationship->addFedspendingFiling($filing);
}
$filing->save();
return true;
} catch (Exception $e) {
throw $e;
}
}
}
static function logChanges(Doctrine_Record $oldObject, Doctrine_Record $newObject, $new = false)
{
$merged = false;
$modId = null;
//isMerge() is used to set the modification's is_merge field, which, for non-delete
//modifications, is only used to identify the modification as part of a larger merge
if (method_exists($newObject, 'isMerge')) {
$merged = $newObject->isMerge();
}
if (get_class($oldObject) != get_class($newObject)) {
throw new exception("Can't log modifications between objects of different classes");
}
$conn = Doctrine_Manager::connection();
try {
$conn->beginTransaction();
//get clean sets of old and new data
$oldData = method_exists($oldObject, 'getAllData') ? $oldObject->getAllData() : $oldObject->toArray(false);
$newData = method_exists($newObject, 'getAllData') ? $newObject->getAllData() : $newObject->toArray(false);
//if modification is create, set all old data to null
if ($new) {
$oldData = array_fill_keys(array_keys($oldData), null);
}
//remove null and blank keys
$oldData = array_filter($oldData, array('self', 'notNullOrBlankCallback'));
$newData = array_filter($newData, array('self', 'notNullOrBlankCallback'));
//change false values to 0
$oldData = array_map(array('self', 'falseToZeroCallback'), $oldData);
$newData = array_map(array('self', 'falseToZeroCallback'), $newData);
//create set of possible modified fields
$fields = array_unique(array_merge(array_keys($oldData), array_keys($newData)));
$fields = array_diff($fields, array('id', 'created_at', 'updated_at', 'is_deleted', 'last_user_id'));
$modifiedFields = array();
foreach ($fields as $field) {
//field is modified if field was or is no longer null, or if old and new values are different
if (!array_key_exists($field, $oldData) || !array_key_exists($field, $newData) || !self::areSameValues($oldData[$field], $newData[$field])) {
$modifiedFields[] = $field;
}
}
if ($merged || count($modifiedFields)) {
$db = Doctrine_Manager::connection();
$sql = 'INSERT INTO modification (object_model, object_id, object_name, is_create, is_merge, user_id, created_at, updated_at) ' . 'VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
if (!($name = $newObject->getName())) {
$name = get_class($newObject);
}
$params = array(get_class($oldObject), $newObject->id ? $newObject->id : '', $name, $new, $merged, self::getUserId(), LsDate::getCurrentDateTime(), LsDate::getCurrentDateTime());
$stmt = $db->execute($sql, $params);
$modId = $db->lastInsertId('modification');
if (count($modifiedFields)) {
//insert all modification_field records at once
$sql = 'INSERT INTO modification_field (modification_id, field_name, old_value, new_value) VALUES';
$params = array();
foreach ($modifiedFields as $field) {
$sql .= ' (?, ?, ?, ?),';
$params = array_merge($params, array($modId, $field, isset($oldData[$field]) ? $oldData[$field] : null, isset($newData[$field]) ? $newData[$field] : null));
}
$sql = substr($sql, 0, strlen($sql) - 1);
$stmt = $db->execute($sql, $params);
}
}
$conn->commit();
} catch (Exception $e) {
$conn->rollback();
throw $e;
}
unset($oldObject);
unset($newObject);
return $modId;
}
static function birthDatesAreCompatible(LsDate $d1, LsDate $d2, $maxYearDiff = 1)
{
if ($d1->isblank() || $d2->isBlank()) {
return true;
} elseif (abs($d1->getYear() - $d2->getYear()) <= $maxYearDiff && LsLogic::areCompatible($d1->getMonth(true), $d2->getMonth(true)) && LsLogic::areCompatible($d1->getDay(true), $d2->getDay(true))) {
return true;
} else {
return false;
}
}
public function executeChat($request)
{
$this->checkUser();
$this->room = $request->getParameter('room', 1);
if ($this->room < 1 || $this->room > 20) {
$this->room = 1;
}
//log chat user
$db = Doctrine_Manager::connection();
$sql = 'REPLACE INTO chat_user (user_id, room, updated_at) VALUES (?, ?, ?)';
//use REPLACE in order to update timestamp
$stmt = $db->execute($sql, array($this->getUser()->getGuardUser()->id, $this->room, LsDate::getCurrentDateTime()));
//get other chat users
$q = LsDoctrineQuery::create()->from('sfGuardUser u')->leftJoin('u.Profile p')->leftJoin('u.ChatUser cu')->where('cu.room = ?', $this->room)->andWhere('cu.updated_at > ?', date('Y-m-d H:i:s', strtotime('5 minutes ago')))->andWhere('u.id <> ?', $this->getUser()->getGuardUser()->id)->setHydrationMode(Doctrine::HYDRATE_ARRAY);
$this->users = $q->execute();
}
static function lockEntityById($id, $userId)
{
$db = Doctrine_Manager::connection();
$sql = 'UPDATE os_entity_transaction SET locked_at = ?, locked_by_user_id = ? WHERE entity_id = ?';
$stmt = $db->execute($sql, array(LsDate::getCurrentDateTime(), $userId, $id));
}
private function getRawFiles()
{
$url = 'http://www.senate.gov/legislative/Public_Disclosure/database_download.htm';
$this->browser->get($url);
$text = $this->browser->getResponseText();
$re = '/(http\\:\\/\\/soprweb\\.senate\\.gov\\/downloads\\/(((19|20)\\d\\d)_(\\d)\\.zip))">.*?((\\d+)\\/(\\d+)\\/(\\d+))/is';
//</td><td align="center">6/17/2008</td>
preg_match_all($re, $text, $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
$date = new LsDate($match[6]);
$file = sfConfig::get('sf_root_dir') . '/data/ldaFiles/' . $match[2];
if (file_exists($file)) {
$date2 = new LsDate(date('Y-m-d', filemtime($file)));
if (LsDate::compare($date, $date2) < 1) {
continue;
}
}
$this->browser->get($match[1]);
$saved = file_put_contents($file, $this->browser->getResponseText());
if ($saved !== FALSE) {
$this->printDebug('saved');
$zip = zip_open($file);
if (is_resource($zip)) {
$this->printDebug('unzipped');
while ($zip_entry = zip_read($zip)) {
$n = basename(zip_entry_name($zip_entry));
$li = Doctrine::getTable('LdaImport')->findOneByFilename($n);
if ($li && $li->filesize == zip_entry_filesize($zip_entry)) {
continue;
}
$fp = fopen($this->_dir . $n, 'w');
if (zip_entry_open($zip, $zip_entry, "r")) {
$buf = zip_entry_read($zip_entry, zip_entry_filesize($zip_entry));
zip_entry_close($zip_entry);
fwrite($fp, $buf);
fclose($fp);
$li = new LdaImport();
$li->year = $match[2];
$li->quarter = $match[5];
$li->filename = $n;
$li->filesize = filesize($this->_dir . $n);
$li->offset = 0;
$li->save();
}
}
zip_close($zip);
} else {
$this->printDebug('zip failure');
}
} else {
continue;
}
}
}
public function logCustomFieldChange($key, $oldValue, $newValue, $modificationId = null)
{
$object = $this->getInvoker();
if (!($id = $object->id)) {
throw Exception("Can't log custom field changes for object without id");
}
$model = get_class($object);
$db = Doctrine_Manager::connection();
try {
$db->beginTransaction();
if (!$modificationId) {
//insert modification
$userId = LsVersionableListener::getUserId();
$time = LsDate::getCurrentDateTime();
$sql = 'INSERT INTO modification (object_model, object_id, user_id, created_at, updated_at) ' . 'VALUES (?, ?, ?, ?, ?)';
$stmt = $db->execute($sql, array($model, $id, $userId, $time, $time));
$modificationId = $db->lastInsertId('modification');
}
//insert modification field
$sql = 'INSERT INTO modification_field (modification_id, field_name, old_value, new_value) VALUES (?, ?, ?, ?)';
$stmt = $db->execute($sql, array($modificationId, $key, $oldValue, $newValue));
$db->commit();
} catch (Exception $e) {
$db->rollback();
throw $e;
}
}
protected function scrapeArticles()
{
foreach ($this->articleList as $url => $ary) {
$article = new Article();
$article->url = $this->cleanArticleUrl($url);
$article->title = $this->cleanArticleTitle($ary['title']);
$article->source_id = $this->getArticleSourceId();
//get authors if listed
if (preg_match_all('#q=author%3A%22([^%]+)%22#i', $ary['subtitle'], $authorMatches, PREG_PATTERN_ORDER)) {
$article->authors = urldecode(implode(',', $authorMatches[1]));
}
try {
$article->body = $this->scrapeArticleBody($article->url);
} catch (Exception $e) {
$this->printDebug("*** Error scraping article: " . $e->getMessage() . " (" . $url . ")");
continue;
}
$article->published_at = $this->getArticleDateFromBody($article->body);
$article->reviewed_at = NULL;
$article->found_at = LsDate::getCurrentDateTime();
$this->articles[] = $article;
$this->printDebug("+ Scraped article '" . $article->title . "'");
}
}
