public function postUp(\Doctrine\DBAL\Schema\Schema $schema)
{
parent::postUp($schema);
$sql = 'UPDATE displays set type="user" WHERE type=""';
$results = $this->connection->executeQuery($sql);
$this->write("<info>Updated {$results->rowCount()} displays from type '' to type 'user'.</info>");
}
public function postUp(\Doctrine\DBAL\Schema\Schema $schema)
{
parent::postUp($schema);
$this->migrateElementAnswers();
$this->migrateAttachments();
foreach (scandir($this->_tmpDir) as $item) {
if ($item == '.' || $item == '..') {
continue;
}
unlink($this->_tmpDir . DIRECTORY_SEPARATOR . $item);
}
rmdir($this->_tmpDir);
}
/**
* Migrate schools from the old education apge format to the new global school list
*
* @param \Doctrine\DBAL\Schema\Schema $schema
*/
public function postUp(\Doctrine\DBAL\Schema\Schema $schema)
{
parent::postUp($schema);
$sql = 'SELECT li.* FROM element_list_items AS li ' . 'WHERE li.element_id IN ' . '(SELECT id FROM elements WHERE page_id IN (SELECT id from pages where type_id=(SELECT id from page_types where class="\\\\Jazzee\\\\Page\\\\Education")))';
$checkSchool = $this->connection->prepare('SELECT id FROM schools WHERE name=?');
$insertSchool = $this->connection->prepare('INSERT INTO schools (name, searchTerms, code) VALUES (?,?,?)');
$rows = $this->connection->executeQuery($sql)->fetchAll();
$fetchCode = $this->connection->prepare('SELECT value FROM element_list_item_variables WHERE item_id=? AND name="code"');
$fetchSearchTerms = $this->connection->prepare('SELECT value FROM element_list_item_variables WHERE item_id=? AND name="searchTerms"');
$total = count($rows);
$newSchools = 0;
foreach ($rows as $row) {
$fetchCode->execute(array($row['id']));
$code = $fetchCode->fetchColumn();
$fetchSearchTerms->execute(array($row['id']));
$searchTerms = $fetchSearchTerms->fetchColumn();
$checkSchool->execute(array($row['value']));
if ($checkSchool->rowCount() == 0) {
$insertSchool->execute(array($row['value'], $searchTerms, $code));
$newSchools++;
}
}
$this->write("<info>Added {$newSchools} schools from {$total} old elements.</info>");
$sql = 'UPDATE answers SET school_id = ' . '(SELECT id from schools WHERE name = ' . '(SELECT value FROM element_list_items WHERE id = ' . '(SELECT eInteger FROM element_answers WHERE answer_id = answers.id AND position = 0))) ' . 'WHERE page_id IN (SELECT id from pages where type_id=(SELECT id from page_types where class="\\\\Jazzee\\\\Page\\\\Education"))';
$rows = $this->connection->executeUpdate($sql);
$this->write("<info>Added School to answer for existing applicants. Modified {$rows} answers</info>");
$sql = 'DELETE FROM elements WHERE page_id IN ' . '(SELECT id from pages where type_id= ' . '(SELECT id from page_types where class="\\\\Jazzee\\\\Page\\\\Education")) ' . 'AND elements.fixedId = ' . 2;
$rows = $this->connection->executeUpdate($sql);
$this->write("<info>Removed the School List Element from Education pages. Modified {$rows} rows</info>");
$sql = 'UPDATE elements set page_id = (SELECT parent_id FROM pages WHERE id = page_id) WHERE page_id IN ' . '(SELECT id from pages WHERE parent_id IN ' . '(SELECT id from pages where type_id= ' . '(SELECT id from page_types where class="\\\\Jazzee\\\\Page\\\\Education")) ' . 'AND pages.fixedId = 2)';
$rows = $this->connection->executeUpdate($sql);
$this->write("<info>Moved all the Known School page elements to the Education page's new single page form. Modified {$rows} rows</info>");
$sql = 'UPDATE element_answers set answer_id = (SELECT parent_id from answers where id = element_answers.answer_id) ' . 'WHERE answer_id IN ' . '(SELECT id from answers WHERE page_id IN ' . '(SELECT id from pages WHERE parent_id IN ' . '(SELECT id from pages where type_id= ' . '(SELECT id from page_types where class="\\\\Jazzee\\\\Page\\\\Education")) AND pages.fixedId = 2))';
$rows = $this->connection->executeUpdate($sql);
$this->write("<info>Migrated Applicant Known school Answers to the Education page. Modified {$rows} rows</info>");
$sql = 'SELECT parent_id from answers WHERE page_id IN ' . '(SELECT id from pages WHERE parent_id IN ' . '(SELECT id from pages where type_id= ' . '(SELECT id from page_types where class="\\\\Jazzee\\\\Page\\\\Education")) AND pages.fixedId = 4)';
$rows = $this->connection->executeQuery($sql)->fetchAll();
$fixAnswer = $this->connection->prepare('UPDATE answers set page_id=null WHERE id = ?');
$fixChildAnswer = $this->connection->prepare('UPDATE answers set page_id=null WHERE parent_id = ?');
foreach ($rows as $row) {
$fixAnswer->execute(array($row['parent_id']));
$fixChildAnswer->execute(array($row['parent_id']));
}
$this->write("<info>Dis-associated all the applicant New School Answers from the Education Page. This will result in some applicant data loss and can be manually corrected in the DB. Modified " . count($rows) . " answers.</info>");
$sql = 'SELECT id from pages where type_id=(SELECT id from page_types where class="\\\\Jazzee\\\\Page\\\\Education")';
$rows = $this->connection->executeQuery($sql)->fetchAll();
$insertPageVars = $this->connection->prepare('INSERT INTO page_variables (page_id, name, value) VALUES (?,?,?)');
$insertNewSchoolPage = $this->connection->prepare('INSERT INTO pages (parent_id, title, fixedId, type_id) VALUES (?,?,?,(SELECT id from page_types WHERE class=?))');
$deleteChildPages = $this->connection->prepare('DELETE FROM pages WHERE parent_id=?');
$schoolListType = base64_encode('full');
$partialSchoolList = base64_encode('');
foreach ($rows as $row) {
$insertPageVars->execute(array($row['id'], 'schoolListType', $schoolListType));
$insertPageVars->execute(array($row['id'], 'partialSchoolList', $partialSchoolList));
$deleteChildPages->execute(array($row['id']));
$insertNewSchoolPage->execute(array($row['id'], 'New School', \Jazzee\Page\Education::PAGE_FID_NEWSCHOOL, '\\Jazzee\\Page\\Standard'));
}
$this->write("<info>Removed existing child pages, created new school page, set page elements for existing educaiton pages. Modified " . count($rows) . " pages.</info>");
$sql = 'SELECT id from pages WHERE fixedId = 4 AND parent_id IN (SELECT id from pages where type_id=(SELECT id from page_types where class="\\\\Jazzee\\\\Page\\\\Education"))';
$rows = $this->connection->executeQuery($sql)->fetchAll();
$insertPageElement = $this->connection->prepare('INSERT INTO elements (page_id, weight, fixedId, title, max, required, type_id) VALUES (?,?,?,?,?,?,(SELECT id from element_types WHERE class=?))');
foreach ($rows as $row) {
$tie = '\\Jazzee\\Element\\TextInput';
$count = 1;
$insertPageElement->execute(array($row['id'], $count++, \Jazzee\Page\Education::ELEMENT_FID_NAME, 'School Name', 255, 1, $tie));
$insertPageElement->execute(array($row['id'], $count++, \Jazzee\Page\Education::ELEMENT_FID_CITY, 'City', 64, 1, $tie));
$insertPageElement->execute(array($row['id'], $count++, \Jazzee\Page\Education::ELEMENT_FID_STATE, 'State or Province', 64, 1, $tie));
$insertPageElement->execute(array($row['id'], $count++, \Jazzee\Page\Education::ELEMENT_FID_COUNTRY, 'Country', 64, 1, $tie));
$insertPageElement->execute(array($row['id'], $count++, \Jazzee\Page\Education::ELEMENT_FID_POSTALCODE, 'Postal Code', 10, 1, $tie));
}
$this->write("<info>Added elements to all new school pages. For " . count($rows) . " pages.</info>");
}
