/TOOLS/PHP/quotes_to_db.php

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

?>