rendered paste body<?php
class CSV {
private $fileName;
private $fileType;
private $fileData; // create array of comma separated data by line
private $title;
private $view;
private $startDate;
private $endDate;
private $table;
public function CSV($file, $type) {
$this->fileName = $file;
$this->fileType = $type;
//step one
//$this->fileData = file($this->fileName);
$this->fileData = file($file);
}
public function processData() {
$this->getTableHeaders();
$this->createTables();
$this->importBody();
$this->replaceData();
$this->addDates();
$this->removeRows();
}
//step two
private function getTableHeaders() {
// explode lines of header by comma
$line1 = explode(',', $this->fileData[0]);
$line3 = explode(',', $this->fileData[2]);
$line4 = explode(',', $this->fileData[3]);
$line7 = explode(',', $this->fileData[6]);
// establish specific values
$this->title = $line1[0];
$this->view = $line3[0];
$this->startDate= $line4[0];
$this->endDate = $line4[1];
$this->table = $line7[0];
}
//step two
private function getBody() {
for ($i = 7; $i <= count($this->fileData); $i++):
$bodyLine[] = $this->fileData[$i];
endfor;
return $bodyLine;
}
//step four
private function importBody() {
$bodyLine = $this->getBody();
for ($i = 0; $i <= count($bodyLine); $i++):
$lineValue = $this->csv_string_to_array($bodyLine[$i]);
$insertData = "
insert into " . $this->fileType . " values ('" .
$lineValue[0] . "', '" .
$lineValue[1] . "', '" .
$lineValue[2] . "', '" .
$lineValue[3] . "', '" .
$lineValue[4] . "', '" .
$lineValue[5] . "', '" .
$lineValue[6] . "')
";
mysql_query($insertData);
endfor;
}
//step three
private function createTables() {
$dropTable = '
DROP TABLE IF EXISTS ' . $this->fileType . '
';
$createTable = '
CREATE TABLE ' . $this->fileType . ' (
Type varchar(4) NOT NULL,
Organization varchar(120),
Domain_Name varchar(30),
HRef varchar(75),
Visits int,
Percent_Visits float,
Hits int
)
';
mysql_query($dropTable);
mysql_query($createTable) or die(mysql_error());
}
// step 5a
private function replaceData() {
$updateTable = "
UPDATE " . $this->fileType . "
SET Type = '" . $this->fileType . "'
";
$changeName = "
ALTER TABLE " . $this->fileType . " CHANGE Type
Classification varchar(4) not null
";
mysql_query($updateTable);
mysql_query($changeName);
}
//step 5b
private function addDates() {
$beginningDates = "
ALTER TABLE " . $this->fileType . " ADD COLUMN
Beginning_Date varchar(10) DEFAULT '5-Jun-07'
AFTER Hits
";
$endingDates = "
ALTER TABLE " . $this->fileType . " ADD COLUMN
Ending_Date varchar(10) DEFAULT '11-Jun-07'
AFTER Beginning_Date
";
mysql_query($beginningDates);
mysql_query($endingDates);
}
//step 5c
private function removeRows() {
$delete = "
DELETE FROM " . $this->fileType . "
WHERE Organization = 'Total'
OR Domain_Name = 'Total'
";
mysql_query($delete) or die(mysql_error);
}
//step eight
public function sortRecordsAndOutputToFile() {
$file = fopen('output.csv', 'w');
$sort = mysql_query("
SELECT *
FROM Customer
ORDER BY Organization ASC, HRef ASC, Classification DESC
");
$csv = array();
$res = mysql_query("SHOW COLUMNS FROM Customer") or die(mysql_error());
array_push($csv, $this->title, "", "", "", "", "", "", "", "");
array_push($csv, "", "", "", "", "", "", "", "", "");
array_push($csv, $this->view, "", "", "", "", "", "", "", "");
array_push($csv, $this->startDate, $this->endDate, "", "", "", "", "", "", "");
array_push($csv, "", "", "", "", "", "", "", "", "");
array_push($csv, "", "", "", "", "", "", "", "", "");
array_push($csv, $this->table, "", "", "", "", "", "", "", "");
for ($i = 0; $i < mysql_num_rows($res); $i++):
array_push($csv, mysql_result($res, $i));
endfor;
while($row = mysql_fetch_array($sort)):
for ($i = 0; $i <= 8; $i++):
array_push($csv, $row[$i]);
endfor;
endwhile;
$this->fputcsv($file, $csv, ',', '"');
}
//static function to write array data to file as CSV, include support for quotation enclosed values
private function fputcsv($filePointer, $dataArray, $delimiter, $enclosure) {
// Build the string
$string = "";
$fieldCounter = 1;
// No leading delimiter
$writeDelimiter = FALSE;
foreach($dataArray as $dataElement):
// Replaces a double quote with two double quotes
if(!isset($dataElement)):
$dataElement="";
endif;
$dataElement=str_replace("\"", "\"\"", $dataElement);
// Adds a delimiter before each field (except the first)
if($writeDelimiter == TRUE):
$string .= $delimiter;
endif;
// Encloses each field with $enclosure and adds it to the string
$string .= $enclosure . $dataElement . $enclosure;
// Delimiters are used every time except the first.
$writeDelimiter = TRUE;
if ($fieldCounter % 9 == 0):
$string .= "\n";
$writeDelimiter = FALSE;
endif;
$fieldCounter++;
endforeach;
// Write the string to the file
fwrite($filePointer,$string);
}
//step six
public function insertToMasterTable() {
$insertData = "
INSERT INTO Customer
SELECT *
FROM " . $this->fileType . "
";
mysql_query($insertData);
}
public function createMasterTable() {
$dropTable = 'DROP TABLE IF EXISTS Customer';
$createCustomerTable = '
CREATE TABLE Customer (
Classification varchar(4) not null,
Organization varchar(120),
Domain_Name varchar(30),
HRef varchar(75),
Visits int,
Percent_Visits float,
Hits int,
Beginning_Date varchar(10),
Ending_Date varchar(10)
)
';
mysql_query($dropTable);
mysql_query($createCustomerTable) or die(mysql_error());
}
private function csv_string_to_array($str) {
$expr = "/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
$results = preg_split($expr,trim($str));
return preg_replace("/^\"(.*)\"$/","$1", $results);
}
//getters
public function getTitle() {
return $this->title;
}
public function getView() {
return $this->view;
}
public function getStartDate() {
return $this->startDate;
}
public function getEndDate() {
return $this->endDate;
}
public function getTable() {
return $this->table;
}
public function getFileType() {
return $this->fileType;
}
}
?>