<?
//---------------------------------------------------------------------------------
// Unit to extract data from a MYSQL database and send resulting dataset to the
// Nozicle dataset
//
// (c)2003 Nozicle Ltd
//
// Change Log:
// 04/04/2003 JR Initial Implementation
//
//---------------------------------------------------------------------------------

include('user_vars.inc');
include('funcs.php');

// initialise variables
$outstring = '';

// constants
$nftString = 0;
$nftInteger = 1;
$nftBlob = 2;
$nftDateTime = 3;
$nftNumber = 4;

$string_term = pack('C',0);
$integer_header = pack('llll',1,0,0,0); // type;size;offset;precision
$blob_header = pack('llll',2,0,0,0);
$datetime_header = pack('llll',3,0,0,0);
$number_header = pack('llll',4,0,0,0);

// open the database
$db = mysql_connect($db_host,$db_login,$db_password);
if (mysql_error() != ''):
  exit(mysql_error());
endif;

mysql_select_db($db_name, $db);
if (mysql_error() != ''):
  exit(mysql_error());
endif;

//Don't bother validating the user - uncomment the line below for validation

//read the post variables sent from the client
//$user_login = $_POST['user_login'];

//$user_password = get_password($db, $user_login);

//if ($user_password == ""):
//  exit('01Unknown user and/or password...');
//endif;

//if (validate_user($db, $user_login, $user_password) < 1):
//  mysql_close($db);
//  exit('01Unknown user and/or password...');
//endif;

$full_key = $secret_key.$user_password;

// read the sql statement from the uploaded file
$tmp_file = $_FILES['sql_file']['tmp_name'];

$zd = gzopen($tmp_file, "rb");
$encrypted_sql_statement = gzread($zd, 999999);
gzclose ($zd);

// read the params from the uploaded file
$tmp_file = $_FILES['params']['tmp_name'];

$zd = gzopen($tmp_file, "rb");
$encrypted_param_str = gzread($zd, 999999);
gzclose ($zd);

$sql_string = DecryptString($encrypted_sql_statement, $full_key);
$param_str = DecryptString($encrypted_param_str, $full_key);

parse_str($param_str, $params);

$table_name = $params['table_name'];
$filter = $params['filter'];
$sql_order = $params['sql_order'];
$table_fields = $params['table_fields'];
$do_compress = $params['do_compress'];

// create the SQL statement
if ($sql_string == 'none') {
  $sql_statement = "SELECT $table_fields FROM $table_name ";
  if ($filter != 'none') {
    $sql_statement = $sql_statement.' WHERE '.$filter;
  }
  if ($sql_order != 'none') {
    $sql_statement = $sql_statement.' ORDER BY '.$sql_order;
  }
} else {
  $sql_statement = $sql_string;
}

// check that the first word is 'select' (only select queries can be run on database)
$select_str = strtolower(substr($sql_statement, 0, 6)); // returns "select"

if ($select_str != 'select'):
  exit('Only select statements permitted...'.$sql_statement);
endif;

// run the SQL

// some systems may need to uncomment the following line if
// slashes have been mysteriously inserted
//$sql_statement = stripslashes($sql_statement);
$sql_result = mysql_query($sql_statement, $db);

if (mysql_error() != ''):
  exit(mysql_error().' '.strlen($encrypted_sql_statement).' '.strlen($sql_statement).' '.$sql_statement);
endif;

// get the data parameters
$num_rows = mysql_num_rows($sql_result);
$num_fields = mysql_num_fields($sql_result);


// header
$no_of_fields = 0;
$no_of_records = $num_rows;
$outstring = '';

// field headers
for ($i=0 ; $i<$num_fields ; $i++) {
  $field_header = '';
  $field_type = mysql_field_type($sql_result, $i);
  $field_name = mysql_field_name($sql_result, $i);
  $field_len = mysql_field_len($sql_result, $i);
  $field_flags = mysql_field_flags($sql_result, $i);

  if ($field_type=='string') {
    $field_header = $field_name.$string_term;
        $temp = pack('llll',0,$field_len,0,0);
        $field_header = $field_header.$temp;
        $no_of_fields = $no_of_fields + 1;
  }

  if ($field_type=='int') {
    $field_header = $field_name.$string_term.$integer_header;
        $no_of_fields = $no_of_fields + 1;
  }

  if ($field_type=='datetime') {
    $field_header = $field_name.$string_term.$datetime_header;
        $no_of_fields = $no_of_fields + 1;
  }

  if ($field_type=='date') {
    $field_header = $field_name.$string_term.$datetime_header;
        $no_of_fields = $no_of_fields + 1;
  }

  if ($field_type=='time') {
    $field_header = $field_name.$string_term.$datetime_header;
        $no_of_fields = $no_of_fields + 1;
  }

  if ($field_type=='blob') {
    $field_header = $field_name.$string_term.$blob_header;
        $no_of_fields = $no_of_fields + 1;
  }

  if ($field_type=='real') {
    $field_header = $field_name.$string_term.$number_header;
        $no_of_fields = $no_of_fields + 1;
  }

  $outstring = $outstring.$field_header;
}

$header = pack('ll',$no_of_fields,$no_of_records);
$outstring = $header.$outstring;

$data = '';
//$blob_offset = '';
//$blob_data = '';

//if ($get_fields_only!='true'):
while ($row = mysql_fetch_array($sql_result)) {

  $row_data = '';
  for ($i=0 ; $i<$num_fields ; $i++) {
    $field_type = mysql_field_type($sql_result, $i);
    $field_name = mysql_field_name($sql_result, $i);
    $field_len = mysql_field_len($sql_result, $i);
    $field_flags = mysql_field_flags($sql_result, $i);

        if ($field_type=='string') {
          $row_data = $row_data.$row[$field_name].$string_term;
          //print("<br>$field_name : $row_data<br>");
        }

        if ($field_type=='int') {
          $temp = pack('l',$row[$field_name]);
      $row_data = $row_data.$temp;
          //print("<br>$field_name : $row_data<br>");
    }

    if ($field_type=='datetime') {
          // send datetime in format YYYY-MM-DD HH:MM:SS
          $row_datetime = $row[$field_name];
          if (strlen($row_datetime) < 18):
            $row_datetime = '0000-00-00 00:00:00';
          endif;
      $row_data = $row_data.$row_datetime;
          //print("<br>$field_name : $row_data<br>");
    }

    if ($field_type=='date') {
      // send datetime in format YYYY-MM-DD HH:MM:SS
          $row_datetime = $row[$field_name];
          $row_datetime = $row_datetime.' 00:00:00';
      $row_data = $row_data.$row_datetime;
          //print("<br>$field_name : $row_data<br>");
    }

    if ($field_type=='time') {
      // send datetime in format YYYY-MM-DD HH:MM:SS
          $row_datetime = $row[$field_name];
          $row_datetime = '0000-00-00 '.$row_datetime;
      $row_data = $row_data.$row_datetime;
          //print("<br>$field_name : $row_data<br>");
    }

    if ($field_type=='blob') {
          $field_data = $row[$field_name];
          //$field_data = 'XXXXthis is a test';
          //if ($field_data == ''):
          //  $field_data = 'NULL';
          //endif;
          $field_length = pack('l',strlen($field_data));
      $row_data = $row_data.$field_length.$field_data;
          //$row_data = $row_data.$field_data;
    }

    if ($field_type=='real') {
      $temp = pack('d',$row[$field_name]);
      $row_data = $row_data.$temp;
          //print("<br>$field_name : $row_data<br>");
    }
  } // for i...
  $data = $data.$row_data;
} // while
//endif; // ($get_fields_only=='true')

$outstring = $outstring.$data;

// add the blob string offset to the beginning of the string
$outstring = pack('l', strlen($outstring)+4).$outstring;//.$blob_data;

// free the result set and close database
mysql_free_result($sql_result);
mysql_close($db);

// compress the result
if ($do_compress=='true') {
  $outstring = gzencode($outstring);
}

$outstring = 'noz_dataset'.$outstring;

// send the result to the client
Header("Content-Length: " . strlen($outstring));

echo $outstring;

?>