<?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;
	}
}
?>