private function addServices()
{
$doctrine = $this->getContainer()->get('doctrine');
$manager = $doctrine->getManager();
$cmd = $manager->getClassMetadata('AppBundle\\Entity\\Service');
$connection = $manager->getConnection();
$connection->beginTransaction();
try {
$connection->query('SET FOREIGN_KEY_CHECKS=0');
$connection->query('DELETE FROM ' . $cmd->getTableName());
$connection->query('DELETE FROM service_stage');
$connection->query('DELETE FROM service_issue');
$connection->query('DELETE FROM service_provider');
$connection->query('DELETE FROM service_category');
$connection->query('DELETE FROM service_service_user');
$connection->query('ALTER TABLE ' . $cmd->getTableName() . ' AUTO_INCREMENT = 1');
// Beware of ALTER TABLE here--it's another DDL statement and will cause
// an implicit commit.
$connection->query('SET FOREIGN_KEY_CHECKS=1');
$connection->commit();
} catch (\Exception $e) {
$connection->rollback();
}
$service = new Service("My Service", "This is a service", "me", new \DateTimeImmutable("2018-01-01T00:00:00+0000"));
$service->addCategory($manager->getRepository('AppBundle\\Entity\\Category')->find(1));
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(1));
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(2));
$service->addProvider($manager->getRepository('AppBundle\\Entity\\Provider')->find(1));
$service->addProvider($manager->getRepository('AppBundle\\Entity\\Provider')->find(2));
$service->addServiceUser($manager->getRepository('AppBundle\\Entity\\ServiceUser')->find(2));
$service->addServiceUser($manager->getRepository('AppBundle\\Entity\\ServiceUser')->find(3));
$service->addIssue($manager->getRepository('AppBundle\\Entity\\Issue')->find(2));
$manager->persist($service);
$service2 = new Service("Another Service", "This is a another service", "someone else", new \DateTimeImmutable("2018-01-01T00:00:00+0000"));
$service2->addCategory($manager->getRepository('AppBundle\\Entity\\Category')->find(6));
$service2->addCategory($manager->getRepository('AppBundle\\Entity\\Category')->find(7));
$service2->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(3));
$service2->addProvider($manager->getRepository('AppBundle\\Entity\\Provider')->find(2));
$service2->addServiceUser($manager->getRepository('AppBundle\\Entity\\ServiceUser')->find(1));
$service2->addIssue($manager->getRepository('AppBundle\\Entity\\Issue')->find(3));
$manager->persist($service2);
$manager->flush();
}
protected function execute(InputInterface $input, OutputInterface $output)
{
$doctrine = $this->getContainer()->get('doctrine');
$manager = $doctrine->getManager();
$cmd = $manager->getClassMetadata('AppBundle\\Entity\\Service');
$connection = $manager->getConnection();
$connection->beginTransaction();
try {
$connection->query('SET FOREIGN_KEY_CHECKS=0');
$connection->query('DELETE FROM ' . $cmd->getTableName());
$connection->query('DELETE FROM provider');
$connection->query('DELETE FROM service_stage');
$connection->query('DELETE FROM service_issue');
$connection->query('DELETE FROM service_provider');
$connection->query('DELETE FROM service_category');
$connection->query('DELETE FROM service_service_user');
$connection->query('ALTER TABLE ' . $cmd->getTableName() . ' AUTO_INCREMENT = 1');
$connection->query('ALTER TABLE provider AUTO_INCREMENT = 1');
// Beware of ALTER TABLE here--it's another DDL statement and will cause
// an implicit commit.
$connection->query('SET FOREIGN_KEY_CHECKS=1');
$connection->commit();
} catch (\Exception $e) {
$connection->rollback();
}
$file = new \SplFileObject(__DIR__ . '/../../../trello.csv');
$file->setFlags(\SplFileObject::READ_CSV);
foreach ($file as $row) {
// Skip header row or empty row
if ($row[0] == "Card ID" || $row[0] == null) {
continue;
}
$matches = [];
$name = $row[1];
if (preg_match('/(.*)\\((.*)\\)/', $row[1], $matches)) {
$name = trim($matches[1]);
$providerName = $matches[2];
if (!isset($this->providers[$providerName])) {
$provider = new Provider($providerName);
$manager->persist($provider);
$this->providers[$providerName] = $provider;
}
}
$service = new Service($name, $row[3], null, null);
if (isset($providerName)) {
$service->addProvider($this->providers[$providerName]);
}
$output->writeln("---");
$output->writeln("Processing " . $name);
switch ($row[15]) {
case "Arrival":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(1));
break;
case "Awaiting Decision":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(2));
break;
case "Positive Decision":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(3));
break;
case "Positive decision - no recourse to public funds":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(5));
break;
case "Negative Decision - with state support (e.g. section 4)":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(5));
break;
case "Negative Decision - Destitute":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(6));
break;
case "Gateway Protection programme (Refugee Council)":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(7));
break;
default:
$output->writeln("Skipping because list is " . $row[15]);
continue 2;
}
foreach (explode(',', $row[4]) as $label) {
switch (trim($label)) {
case "Children":
$service->addServiceUser($manager->getRepository('AppBundle\\Entity\\ServiceUser')->find(3));
break;
case "Family":
$service->addServiceUser($manager->getRepository('AppBundle\\Entity\\ServiceUser')->find(2));
break;
case "Vulnerable adults":
$service->addServiceUser($manager->getRepository('AppBundle\\Entity\\ServiceUser')->find(4));
break;
case "Single Male":
$service->addServiceUser($manager->getRepository('AppBundle\\Entity\\ServiceUser')->find(5));
break;
case "Single Female":
$service->addServiceUser($manager->getRepository('AppBundle\\Entity\\ServiceUser')->find(6));
break;
case "Needing further support/Under-resourced (blue)":
$service->addIssue($manager->getRepository('AppBundle\\Entity\\Issue')->find(1));
break;
case "Gap in provision (pink)":
$service->addIssue($manager->getRepository('AppBundle\\Entity\\Issue')->find(2));
break;
case "Needing Immediate Action (purple)":
$service->addIssue($manager->getRepository('AppBundle\\Entity\\Issue')->find(3));
break;
case "Right to Remain/Positive Decision (green)":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(3));
break;
case "Arrival (red)":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(1));
break;
case "Awaiting Decision (yellow)":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(2));
break;
case "Positive no recourse (black)":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(4));
break;
case "Negative Decision with state support e.g section 4 (sky)":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(5));
break;
case "Negative Decision/Destitute (orange)":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(6));
break;
case "Gateway":
$service->addStage($manager->getRepository('AppBundle\\Entity\\Stage')->find(7));
break;
case "Education":
$service->addCategory($manager->getRepository('AppBundle\\Entity\\Category')->find(1));
break;
case "Accommodation":
$service->addCategory($manager->getRepository('AppBundle\\Entity\\Category')->find(3));
break;
case "Health & Social Care":
$service->addCategory($manager->getRepository('AppBundle\\Entity\\Category')->find(2));
break;
case "Finance":
$service->addCategory($manager->getRepository('AppBundle\\Entity\\Category')->find(4));
break;
case "Social & Community":
$service->addCategory($manager->getRepository('AppBundle\\Entity\\Category')->find(6));
break;
case "Asylum Process Advice & Legal":
$service->addCategory($manager->getRepository('AppBundle\\Entity\\Category')->find(5));
break;
case "Resources e.g. clothes and food":
$service->addCategory($manager->getRepository('AppBundle\\Entity\\Category')->find(7));
break;
default:
$output->writeln('Unhandled label - ' . $label);
}
}
$manager->persist($service);
$output->writeln("OK");
}
$manager->flush();
$output->writeln("CSV Imported");
}