/TOOLS/PHP/quotes_to_db.php
PHP | 196 lines | 166 code | 14 blank | 16 comment | 21 complexity | b0b5e18504ddade294ff8093d90a1087 MD5 | raw file
- <?php
- require_once 'mysql.class.php';
- $QUOTES_DIR ="/home/voellenk/QUOTES/";
- $dbhost = 'localhost:3036';
- $dbuser = 'va';
- $dbpass = 'dummy';
- $dbname = 'va_stratlab';
- // parse command line parameters
- $args= parseArgs($argv);
- if (array_key_exists("exchange", $args)) {
- $exchange=$args['exchange'];
- } else {
- echo "Usage: ".$_SERVER['PHP_SELF']." --exchange=<EXCHANGENAME>\n";
- die;
- }
- // verify if directory with zip files exists
- if (!is_dir($QUOTES_DIR))
- die ("Directory $QUOTES_DIR does not exist.\n");
- // establish connection to db
- $db = new mysql($dbhost, $dbuser, $dbpass);
- if (!$db->connect($dbname))
- die;
- $sql = "SELECT id FROM exchange WHERE name='$exchange'";
- $rows= $db->select($sql);
- if (!$rows) {
- $sql = "INSERT INTO exchange (name) VALUES ('$exchange')";
- $result = $db->insert($sql);
- if ($result) {
- $sql = "SELECT id FROM exchange WHERE name='$exchange'";
- $rows= $db->select($sql);
- $exchange_id = $rows[0];
- } else {
- die($db->error);
- }
- } else {
- // use exchange_id for coming INSERTS
- $exchange_id = $rows[0];
- }
- $zipfilearr=array();
- $dh = opendir($QUOTES_DIR);
- if ($dh==TRUE) {
- while (($file = readdir($dh)) !== false) {
- //echo "filename: $file : filetype: " . filetype($QUOTES_DIR . $file) . "\n";
- if ($file == $exchange & filetype($QUOTES_DIR . $file)=="dir" ) {
- // found exchange directory
- $dh1 = opendir($QUOTES_DIR.$file);
- while (($zfile = readdir($dh1)) !== false) {
- if (substr(strtoupper($zfile),-4) == ".ZIP") {
- $zipfilearr[]=$zfile;
- }
- }
- closedir($dh1);
- }
- }
- closedir($dh);
- }
- sort($zipfilearr); //alphabetically sort filenames
- foreach ($zipfilearr as $zfile) {
- echo "processing $zfile...";
- readZipFile($QUOTES_DIR.$exchange."/".$zfile);
- }
- $db->disconnect();
- // ---- end of script ----
- function readZipFile($zfile) {
- $namecounter=0;
- $pricecounter=0;
- $txtfilearr=array();
- $za = new ZipArchive();
- $res = $za->open($zfile);
- if ($res===true) {
- //loop through all text file in zip archive
- $j=0;
- for ($i=0; $i<$za->numFiles; $i++) {
- $arr = $za->statIndex($i);
- if (substr(strtoupper($arr['name']),-4) == ".TXT") {
- $txtfilearr[$j]['name']=$arr['name'];
- $txtfilearr[$j++]['index']=$i;
- }
- }
- sort($txtfilearr);
- echo "found ".sizeof($txtfilearr)." files. ";
-
- // now uncompress each file and read the contents
- foreach($txtfilearr as $txtfile) {
- $za->extractTo(".", $txtfile['name']);
- $fcontents= file($txtfile['name']); //read file contents into array
- insertIntoDb($fcontents, &$namecounter, &$pricecounter); //insert into va_tradedb
- unlink($txtfile['name']); //remove txt file after processing
- }
-
- $za->close();
- } else {
- echo "opening of zip file $zfile failed.\n";
- }
- echo "inserted $namecounter names and $pricecounter prices.\n";
- return true;
- }
- function insertIntoDb($fcontents, $namecounter, $pricecounter) {
- global $exchange_id;
- global $db;
- // loop through every line of text file
- foreach ($fcontents as $line) {
- $cells= explode(",",$line);
- // $cells[0] : name
- // $cells[1] : code
- // $cells[2] : date
- // $cells[3:6] : OHLC
- // $cells[7] : volume
- // $cells[8] : oi
- if (sizeof ($cells) <8 | sizeof ($cells) >9)
- die ("line $line invalid.\n");
- $name= str_replace("'","\'",$cells[0]);
- if (is_numeric($cells[2])) { // omit first line
- $sql="SELECT stockname.id,exchange.id FROM exchange JOIN stockname ON (exchange.id=stockname.exchange_id)
- WHERE stockname.code='".$cells[1]."' AND exchange.id='".$exchange_id."'";
- $rows=$db->select($sql);
- // $rows[0] = stockname.id
- // $rows[1] = exchange.id
- if (!$rows) {
- // if no result found insert row into db
- $sql="INSERT INTO stockname (name, code, exchange_id) VALUES ('".$name."','".$cells[1]."','".$exchange_id."')";
- if ($db->insert($sql)) {
- $namecounter++;
- } else {
- die("Insert Error: ".mysql_error()."\n");
- }
- }
- $date = substr($cells[2],0,4)."-".substr($cells[2],4,2)."-".substr($cells[2],6,2);
- $sql="SELECT id FROM stockname WHERE code='".$cells[1]."' AND exchange_id=".$exchange_id;
- $id=$db->select($sql);
- // $id[0] includes id
- //$sql="SELECT volume FROM stockprice WHERE stock_id='".$id[0]."' AND date='$date'";
- //$rows=$db->select($sql);
- if (sizeof($cells)==8) { //without open interest
- $sql="INSERT INTO stockprice (stock_id, date, open, high, low, close, volume)
- VALUES('".$id[0]."','".$date."','".$cells[3]."','".$cells[4]."','".$cells[5]."','".$cells[6]."','".$cells[7]."')";
- } elseif (sizeof($cells)==9) { //include oi
- $sql="INSERT INTO stockprice (stock_id, date, open, high, low, close, volume, oi)
- VALUES('".$id[0]."','".$date."','".$cells[3]."','".$cells[4]."','".$cells[5]."','".$cells[6]."','".$cells[7]."','".$cells[8]."')";
- }
- //echo $sql."\n";
- if (@$db->insert($sql)) {
- $pricecounter++;
- } else {
- //die("Insert Error: ".mysql_error()."\n");
- }
- }
- }
- }
- function parseArgs($argv){
- array_shift($argv);
- $out = array();
- foreach ($argv as $arg){
- if (substr($arg,0,2) == '--'){
- $eqPos = strpos($arg,'=');
- if ($eqPos === false){
- $key = substr($arg,2);
- $out[$key] = isset($out[$key]) ? $out[$key] : true;
- } else {
- $key = substr($arg,2,$eqPos-2);
- $out[$key] = substr($arg,$eqPos+1);
- }
- } else if (substr($arg,0,1) == '-'){
- if (substr($arg,2,1) == '='){
- $key = substr($arg,1,1);
- $out[$key] = substr($arg,3);
- } else {
- $chars = str_split(substr($arg,1));
- foreach ($chars as $char){
- $key = $char;
- $out[$key] = isset($out[$key]) ? $out[$key] : true;
- }
- }
- } else {
- $out[] = $arg;
- }
- }
- return $out;
- }
- ?>