| <?php | <?php |
| define('__ROOT__', dirname(dirname(__FILE__))); | define('__ROOT__', dirname(dirname(__FILE__))); |
| include(__ROOT__ . "/config.php"); | include(__ROOT__ . "/config.php"); |
| /************************************** | /************************************** |
| * Drop tables * | * Drop tables * |
| **************************************/ | **************************************/ |
| // Drop table messages from file db | // Drop table messages from file db |
| $db->exec("DROP TABLE IF EXISTS dcaas_services"); | $db->exec("DROP TABLE IF EXISTS dcaas_services"); |
| $row = 0; | $row = 0; |
| $headers = Array(); | $headers = Array(); |
| $csvfile = '../data/DCaaS+Catalogue+September+2013 PublicDCaaS_AFI_Part_3_Sec_5_Service_.csv'; | $path = "../data/"; |
| foreach (glob($path."*.csv") as $filename) { | |
| $csvfile = $path.$filename; | |
| } | |
| if (($handle = fopen($csvfile, "r")) !== FALSE) { | if (($handle = fopen($csvfile, "r")) !== FALSE) { |
| while (($data = fgetcsv($handle, 4096, ",")) !== FALSE) { | while (($data = fgetcsv($handle, 7000, ",")) !== FALSE) { |
| if ($row == 0) { | if ($row == 0) { |
| /************************************** | /************************************** |
| * Create tables * | * Create tables * |
| **************************************/ | **************************************/ |
| // Create table messages | // Create table messages |
| $create = "CREATE TABLE IF NOT EXISTS dcaas_services ("; | $create = "CREATE TABLE IF NOT EXISTS dcaas_services ("; |
| // id INTEGER PRIMARY KEY,"; | // id INTEGER PRIMARY KEY,"; |
| foreach ($data as $column) { | foreach ($data as $column) { |
| $column = str_replace(" ", "_", strtolower($column)); | $column = str_replace(" ", "_", strtolower($column)); |
| $create .= $column . " text " . (($column == 'acnabn' || $column == 'service_name') ? " not null " : "") . ","; | $create .= $column . " text " . (($column == 'acnabn' || $column == 'service_name') ? " not null " : "") . ","; |
| $headers[] = $column; | $headers[] = $column; |
| } | } |
| //$create .= " PRIMARY KEY ( acnabn,service_name) )"; | //$create .= " PRIMARY KEY ( acnabn,service_name) )"; |
| $create .= "id INTEGER PRIMARY KEY )"; | $create .= "id INTEGER PRIMARY KEY )"; |
| echo $create; | echo $create; |
| $db->exec($create); | $db->exec($create); |
| // Prepare INSERT statement to SQLite3 file db | // Prepare INSERT statement to SQLite3 file db |
| /* Create a string for the parameter placeholders filled to the number of params */ | /* Create a string for the parameter placeholders filled to the number of params */ |
| $placeholders = implode(',', array_fill(0, count($headers), '?')); | $placeholders = implode(',', array_fill(0, count($headers), '?')); |
| $insert = "INSERT INTO dcaas_services (" . implode($headers, ", ") . ") | $insert = "INSERT INTO dcaas_services (" . implode($headers, ", ") . ") |
| VALUES (" . $placeholders . ")"; | VALUES (" . $placeholders . ")"; |
| echo $insert; | echo $insert; |
| $stmt = $db->prepare($insert); | $stmt = $db->prepare($insert); |
| $db->beginTransaction(); | $db->beginTransaction(); |
| } else { | } else { |
| if (count($data) < count($headers)) { | if (count($data) < count($headers)) { |
| $values = $data + array_fill(count($data), count($headers) - count($data), NULL); | $values = $data + array_fill(count($data), count($headers) - count($data), NULL); |
| } else { | } else { |
| $values = $data; | $values = $data; |
| } | } |
| print_r($values); | print_r($values); |
| $stmt->execute($data); | $stmt->execute($data); |
| } | } |
| $row++; | $row++; |
| } | } |
| // end transaction | // end transaction |
| $db->commit(); | $db->commit(); |
| fclose($handle); | fclose($handle); |
| echo "$row rows imported"; | echo "<br/>\n $row rows imported <br/>\n"; |
| } | } |
| ?> | ?> |