function process()
{
set_time_limit(0);
$csv_file = isset($_GET['csv_file']) ? trim($_GET['csv_file']) : null;
if (is_null($csv_file)) {
echo 'ERROR - csv_file param missing!';
exit;
}
$ds = new CvsDataSourceReader(",");
$cur_path = Director::baseFolder();
$ds->Open($cur_path . "/assets/" . $csv_file);
$headers = $ds->getFieldsInfo();
try {
$exams = 0;
while (($row = $ds->getNextRow()) !== FALSE) {
$code = trim($row[$headers["exam_code"]]);
if ($code != 'COA-EVENT') {
continue;
}
$email = trim($row[$headers['open_stack_id']]);
$exam_ext_id = trim($row[$headers['candidate_exam_id']]);
$status = trim($row[$headers['exam_status']]);
$pass_date = trim($row[$headers['pass_fail_date_c']]);
$cert_nbr = trim($row[$headers['certification_number']]);
$modified_date = trim($row[$headers['candidate_exam_date_modified']]);
$exam_expiration_date = trim($row[$headers['exam_expiration_date']]);
$cert_exam_expiration_date = trim($row[$headers['certificate_expiration_date']]);
$cert_status = trim($row[$headers['certification_status']]);
$member = $this->member_repository->findByEmail($email);
if (is_null($member)) {
continue;
}
$exam = $member->getExamByExternalId($exam_ext_id);
if (is_null($exam)) {
//create exam
$exam = CertifiedOpenStackAdministratorExam::create();
$exam->OwnerID = $member->ID;
$exam->ExternalID = $exam_ext_id;
}
$exam->setState($status, $modified_date, $exam_expiration_date, $pass_date, $code, $cert_nbr, $cert_exam_expiration_date, $cert_status);
$exam->write();
$exams++;
}
} catch (Exception $e) {
$status = 0;
}
echo sprintf("created %s exams", $exams);
}
public function MarketPlaceReviews()
{
$output = '';
$reviews = $this->ProductReviews();
$old_reviews = array();
$ds = new CvsDataSourceReader("\t");
$cur_path = Director::baseFolder();
$ds->Open($cur_path . "/marketplace/code/migrations/data/reviews.csv");
$headers = $ds->getFieldsInfo();
while (($row = $ds->getNextRow()) !== FALSE) {
$review = explode(',', $row[0]);
$created = date('M jS Y', strtotime($review[1]));
$title = $review[5];
$comment = $review[3];
$rating = $review[0];
$user_name = $review[6];
$product_id = $review[2];
if ($this->company_service_ID == $product_id) {
$old_reviews[] = new ArrayData(array("IsImported" => 1, "Created" => $created, "Title" => $title, "Comment" => $comment, "Rating" => $rating * 20, "UserName" => $user_name));
}
}
$reviews = array_merge($old_reviews, $reviews->toArray());
usort($reviews, array($this, 'sort_reviews'));
foreach ($reviews as $review) {
if ($review->IsImported) {
$output .= $review->renderWith('MarketPlaceReviews_review_import');
} else {
$output .= $review->renderWith('MarketPlaceReviews_review');
}
}
return $output;
}
function up()
{
echo "Starting Migration Proc ...<BR>";
//check if migration already had ran ...
$migration = Migration::get()->filter('Name', $this->title)->First();
if (!$migration) {
//if not create migration and run it...
$migration = new Migration();
$migration->Name = $this->title;
$migration->Description = $this->description;
$migration->Write();
//run migration proc
//get migration data from cvs file,
//this cvs file contains all Orgs that should be splitted on several
//current affiliations
$ds = new CvsDataSourceReader("\t");
$cur_path = Director::baseFolder();
$ds->Open($cur_path . "/openstack/code/migrations/data/orgs.csv");
$headers = $ds->getFieldsInfo();
$split_organizations = array();
//and stored those ones on a hash
try {
while (($row = $ds->getNextRow()) !== FALSE) {
$id = $row[$headers["ID"]];
$split = $row[$headers["Split"]];
$name = $row[$headers["Name"]];
if ($split == 'True' || $split == 'SEMICOLON') {
$split_organizations[$id] = array("Split" => $split, "Name" => $name);
}
}
} catch (Exception $e) {
$status = 0;
}
echo sprintf("Multiple Organization Count %d </BR>", count($split_organizations));
echo 'Procesing Members.... </BR>';
$count = DB::query("SELECT COUNT(ID) From Member Where OrgID IS NOT NULL;")->value();
echo sprintf("%d Members to process </BR>", $count);
$page = 0;
$page_size = 1000;
$page_count = ceil($count / $page_size);
for ($page = 0; $page <= $page_count; $page++) {
echo sprintf("Procesing Member Page %d Of %d ...</BR>", $page, $page_count);
$start_from = $page * $page_size;
//get current org
$res = DB::query("SELECT Member.ID AS MemberID, Org.ID AS OrgID, Org.Name AS OrgName , Org.IsStandardizedOrg, Member.JobTitle, Member.Role From Member INNER JOIN Org ON Org.ID=Member.OrgID Where OrgID IS NOT NULL LIMIT {$start_from}, {$page_size};");
$multi_org_id = null;
foreach ($res as $record) {
$curOrgID = $record["OrgID"];
$curMemberID = $record["MemberID"];
$curRole = $record["Role"];
$curJobTitle = $record["JobTitle"];
//is multiple current affiliations?
if (isset($split_organizations[$curOrgID])) {
$multi_org_id = $curOrgID;
$org_aux = $split_organizations[$curOrgID];
$org_name = $org_aux["Name"];
$mode = $org_aux["Split"];
$affiliations = array();
switch ($mode) {
case "True":
$affiliations = explode(",", $org_name);
break;
case "SEMICOLON":
$affiliations = explode(";", $org_name);
break;
}
foreach ($affiliations as $a) {
$a = Convert::raw2sql(trim($a));
$org_id = DB::query("SELECT ID From Org WHERE Name = '{$a}' AND ID <> {$curOrgID} ORDER BY ID ASC LIMIT 1")->value();
if (is_null($org_id)) {
$org_id = DB::query("SELECT ID From Org WHERE Name Like '%{$a}%' AND ID <> {$curOrgID} ORDER BY ID ASC LIMIT 1")->value();
}
if (is_null($org_id)) {
//create Org
$org_id = $this->writeOrg($a);
}
//create affiliation
$this->writeAffiliation(null, null, null, null, true, $curMemberID, $org_id);
//check if we have it on history repeated to get the from date
$res2 = DB::query("SELECT NewAffiliation,OldAffiliation,Created,LastEdited FROM AffiliationUpdate WHERE MemberID={$curMemberID} AND NewAffiliation=OldAffiliation AND OldAffiliation='{$curOrgID}' ORDER BY Created ASC;");
if ($res2) {
foreach ($res2 as $record2) {
$this->updateAffiliationStartDate($record2['Created'], $curMemberID, $org_id);
break;
}
}
}
} else {
//single current affiliation
//create affiliation
$this->writeAffiliation(null, null, $curJobTitle, $curRole, true, $curMemberID, $curOrgID);
}
//now build affiliation history ...
if (!is_null($multi_org_id)) {
$res3 = DB::query("SELECT NewAffiliation,OldAffiliation,Created,LastEdited FROM AffiliationUpdate WHERE MemberID={$curMemberID} AND NewAffiliation<>'{$multi_org_id}' AND OldAffiliation<>'{$multi_org_id}' ORDER BY Created ASC;");
} else {
$res3 = DB::query("SELECT NewAffiliation,OldAffiliation,Created,LastEdited FROM AffiliationUpdate WHERE MemberID={$curMemberID} ORDER BY Created ASC;");
}
foreach ($res3 as $record3) {
$old_org = Convert::raw2sql(trim($record3["OldAffiliation"]));
$new_org = Convert::raw2sql(trim($record3["NewAffiliation"]));
$created = $record3["Created"];
$new_org_id = $this->getOrgId($new_org);
$old_org_id = $this->getOrgId($old_org);
if (is_null($new_org_id)) {
continue;
}
//create affiliation
if ($new_org_id == $old_org_id || is_null($old_org_id)) {
//check if current
$count = DB::query("SELECT COUNT(ID) FROM Affiliation WHERE MemberID={$curMemberID} AND OrganizationID={$new_org_id} AND Current=1;")->value();
if ($count > 0) {
$this->updateAffiliationStartDate($created, $curMemberID, $new_org_id);
continue;
}
//check if does not not exists already
$count = DB::query("SELECT COUNT(ID) FROM Affiliation WHERE MemberID={$curMemberID} AND OrganizationID={$new_org_id}")->value();
if ($count > 0) {
continue;
}
//new one
$this->writeAffiliation($created, null, null, null, false, $curMemberID, $new_org_id);
} else {
//try to update old affiliation
$this->updateAffiliationEndDate($created, $curMemberID, $old_org_id);
//check if current
$count = DB::query("SELECT COUNT(ID) FROM Affiliation WHERE MemberID={$curMemberID} AND OrganizationID={$new_org_id} AND Current=1;")->value();
if ($count > 0) {
$this->updateAffiliationStartDate($created, $curMemberID, $new_org_id);
continue;
}
$this->writeAffiliation($created, null, null, null, false, $curMemberID, $new_org_id);
}
}
}
}
} else {
echo "Migration Already Ran! <BR>";
}
echo "Migration Done <BR>";
}
