<?php
// dsurvey library
// a) names of directories and files
// b) functions
//   connect to database for sesssion aname (exit if errof)
//       connectDsurveyDatabase($aname)
//   does this session exist?
//       checkXmlExists($sessName,$sessions_dir,"- Error. No such session: $sessName (getUserList) ");
//   extract a request field (GET or POST).
//       extractRequestVar($varname,$default,$errorMess) {
//   check xml syntax of a string. Return 0 if no errors, otherwise, returns an HTML message pointing out the errors
//      checkSyntaxXML($xmlstr,$dafile) {
//   break string into words (collapses space strings
//      getWords(astring)

// =================================

 $svydir='';
 $rootdir=$_SERVER['DOCUMENT_ROOT'];
 $dsvydir=$rootdir.'dSurvey/';
 $tmpdir=$dsvydir.'temp/';
 $wwwdir=$dsvydir;
 $instruments_dir=$dsvydir.'instruments/';
 $sessions_dir=$dsvydir.'sessions/';
 $data_dir=$dsvydir.'data/';
 $lookup_dir=$dsvydir.'pinfo/';
  $backup_dir=$dsvydir.'sessions/backup/';

 $pinfo_dir=$lookup_dir;
 $debug=0;

 $hdrMysql='dsurvey_';
 $mysqlHdr='dSurvey_';
 $currentDatabse='';

 $break = Explode('/',$_SERVER['SCRIPT_NAME']);
 $myScriptName = $break[count($break) - 1];

$aa=0;
if (array_key_exists('showDirs',$_GET)) {
   $aa=$_GET['showDirs'];
}

if ($aa == 1 ) {
print '<h1>Directories </h1>';
print '<ul>';
print "<li>Rootdir= $rootdir   ";
print "<li>dsvyDir= $dsvydir ";
print "<li>wwwDir= $wwwdir ";
print "<li>instruments_dir= $instruments_dir ";
print "<li>sessions_dir= $sessions_dir     ";
print "<li>data_dir= $data_dir ";
print "<li>lookup_dir= $lookup_dir ";
print "<li>pinfo_dir= $pinfo_dir ";
print '</ul> ';
}


//=========================
// check xml syntax of a string. Return 0 if no errors
// otherwise, returns an HTML message pointing out the errors

function checkSyntaxXML($xmlstr,$dafile) {

libxml_use_internal_errors(true);

$doc = simplexml_load_string($xmlstr);
//header('Content-Type: text/plain; charset=iso-8859-1');

if (!$doc) {
   $xml = explode("\n", $xmlstr);
   $errors = libxml_get_errors();
   $stuff=" <h3>Errors in XML file  $dafile </h3>  <ul> ";
    foreach ($errors as $error) {
        $stuff=$stuff . ' <li> ' .  display_xml_error($error, $xml);
    }
    libxml_clear_errors();
    return $stuff .  ' </ul>' ;
}
return 0 ;
}

function display_xml_error($error, $xml) {
    $return  = '<div style="white-space:pre;font-family:monospace;stybackground-color:#ababab">' .htmlentities($xml[$error->line - 1]) . '<br>' ;
    $return .= str_repeat('-', $error->column)  ;
    $return .= '</div> ';

    switch ($error->level) {
        case LIBXML_ERR_WARNING:
            $return .= "Warning $error->code: ";
            break;
         case LIBXML_ERR_ERROR:
            $return .= "Error $error->code: ";
            break;
        case LIBXML_ERR_FATAL:
            $return .= "Fatal Error $error->code: ";
            break;
    }

    $return .= trim($error->message) .
               "<br/>  Line:  $error->line   , Column: $error->column";

    if ($error->file) {
        $return .= "<br/>  File: $error->file";
    }

    return $return;
}


//===-
// conect to a dsurvey session mysql database, using "i" (improved) funcitons
// 10 april 2011 : currently only used for restoreMysqlDatabase (which uses mysli_multi_query
function connectDsurveyDatabaseI($aname) {
global $svydir,$rootdir,$dsvydir,$wwwdir,$instruments_dir,$sessions_dir,$data_dir,$lookup_dir,$myScriptName;
global $pinfo_dir,$debug,$hdrMysql,$mysqlHdr,$currentDatabase,$tmpdir,$backup_dir;

$link=mysqli_connect('localhost','dsurveyAdmin','admin');
if (!$link) {
   header("Content-Type: text/plain");
   print ' - Error. Could not connect to mysqli (with username=`dsurveyAdmin` and password=`admin`): ' . mysqli_error();
   exit ;
}
$mdata=$hdrMysql . $aname ;
$qq=mysqli_select_db($link,$mdata);
if (!$qq) {
   header("Content-Type: text/plain");
   print '- Error. Unable to select mysqli database ' . $mdata . ': '  . mysqli_error($link);
   mysqli_close($link);
   exit ;
}

$currentDatabase=$mdata ;    // a global ;

return $link ;
}


//===-   mysql
// conect to a dsurvey session mysql database
function connectDsurveyDatabase($aname) {

global $svydir,$rootdir,$dsvydir,$wwwdir,$instruments_dir,$sessions_dir,$data_dir,$lookup_dir,$myScriptName;
global $pinfo_dir,$debug,$hdrMysql,$mysqlHdr,$currentDatabase,$tmpdir,$backup_dir;


$link=mysql_connect('localhost','dsurveyAdmin','admin');
if (!$link) {
   header("Content-Type: text/plain");
   print ' - Error. Could not connect to mysql (with username=`dsurveyAdmin` and password=`admin`): ' . mysql_error();
   exit ;
}

$mdata=$hdrMysql . $aname ;
$qq=mysql_select_db($mdata, $link);
if (!$qq) {
   header("Content-Type: text/plain");
   print '- Error. Unable to select mysql database ' . $mdata . ': '  . mysql_error();
   mysql_close($link);
   exit ;
}

$currentDatabase=$mdata ;    // a global ;

return $link ;
}

//===-
// extract a request field (GET or POST)
// varname is name of field to get. can be a space delimited list of names (first one found is returned)
// if $errormess='', return $default if no such field
//    if = '1',  display generic error  message and exit
//    else, display $errormess and exit
function extractRequestVar($varname,$default,$errorMess) {
$varname=trim(strtoupper($varname));
$vars =getWords($varname);
// preg_replace("/\s+/"," ", $varname) ;             // collapse spaces
//$varname=strtoupper(trim($varname));
//$vars=explode(' ',$varname);

foreach ($_REQUEST as $vname=>$oof) {
     $avar=trim(strtoupper($vname)) ;
     for ($ith=0;$ith<count($vars);$ith++) {
        if ($avar==$vars[$ith]) {
           return $oof ;
        }
     }
}
// if here, could not be found
$errorMess=trim($errorMess);
if ($errorMess!=='') {          // an error, quit
  if ($errorMess=='1') {
     $file = $_SERVER["SCRIPT_NAME"];
      $break = explode('/', $file);
      $pfile = $break[count($break) - 1];
      header("Content-Type: text/plain");
      print '- Error. Unable to find request variable (' . $varname . ') in ' . $pfile ;
  } else {
     header("Content-Type: text/plain");
     print $errorMess ;
  }
  exit ;
}
  return $default ;
}

//=======================
// see if session exists. If not, exit with error message
function checkXMLexists($fname,$adir,$errorMess) {

global $svydir,$rootdir,$dsvydir,$wwwdir,$instruments_dir,$sessions_dir,$data_dir,$lookup_dir,$myScriptName;
global $pinfo_dir,$debug,$hdrMysql,$mysqlHdr,$currentDatabase,$backup_dir;


  $dafile=$adir . $fname . '.XML' ;
  $isfile=file_exists($dafile);
  if ($isfile) return 1 ;

  if (trim($errorMess)!=='') {
      header("Content-Type: text/plain");
      print $errorMess;
      exit;
  }

 $file = $_SERVER["SCRIPT_NAME"];
 $break = explode('/', $file);
 $pfile = $break[count($break) - 1];

 header("Content-Type: text/plain");
 print '- Error. No such XML file ' . $dafile . ' (' . $pfile . ')';

 exit ;
}

//=============
// trim spaces, convert internal spaces to single space string, break into an array
function getWords($cval3) {
    $cval3=trim($cval3);
    $cval4=preg_replace("/\s+/"," ", $cval3) ;             // collapse spaces
    $bvars=explode(' ',$cval4) ;
    return $bvars ;

}

// copy one php table to a new table
// Requires that $link be the pointer to the mysql database (i.e.;  $link=mysql_connect('localhost','username','password');

//  from http://www.electrictoolbox.com/php-script-backup-copy-mysql-table/
// noowerwrite means "do NOT overwrite exisdting table "
function copy_table($from, $to,$link,$noOverwrite) {

   if ($noOverwrite!="1") {
       mysql_query("DROP TABLE IF EXISTS '$to' ",$link);
       mysql_query("CREATE TABLE  '$to'  SELECT * FROM  '$from' ",$link);
       return 1;
   }

    if(table_exists($to,$link)) {
        $success = false;
    }
    else {
        mysql_query("CREATE TABLE '$to' LIKE '$from'");
        mysql_query("INSERT INTO '$to' SELECT * FROM '$from'");
        $success = true;
    }

    return $success;

}

function table_exists($tablename, $database = false) {

    if(!$database) {
        $res = mysql_query("SELECT DATABASE()");
        $database = mysql_result($res, 0);
    }

    $res = mysql_query("
        SELECT COUNT(*) AS count
        FROM information_schema.tables
        WHERE table_schema = '$database'
        AND table_name = '$tablename'
    ");

    return mysql_result($res, 0) == 1;
}


//===========================
// write a mysql table as a CSV
// Database MUST be selected before calling this: i.e using mysql_select_db($dbname);
//
// $outfile is a string containing path to the output file
// $link is from a $link=mysql_connect($server, $login, $password);
// $escape =1, then escape the contents of the table (this allows crlfs, etc to be transfered, albeit in an escaped format)
// $table is table from selected database
// return codes:
//  -1 : could not open output file
//  -2 : could not select database
//  -3 : could not write output file
// >0  : number of lines copied

function mysqlToCSV($table,$outfile,$escape,$link) {


$fp = fopen($outfile, "w");
if (!$fp) return -1 ;

$res = mysql_query("SELECT * FROM '$table' ",$link);
if (!$res) return -2 ;

// fetch a row and write the column names out to the file
$row = mysql_fetch_assoc($res);
$line = "";
$comma = "";
foreach($row as $name => $value) {
    $line .= $comma . '"' . str_replace('"', '""', $name) . '"';
    $comma = ",";
}
$line .= "\n";
$qq=fputs($fp, $line);
if (!$qq) return -3 ;

// remove the result pointer back to the start
mysql_data_seek($res, 0,$link);

// and loop through the actual data
$nrows=0;
while($row = mysql_fetch_assoc($res)) {

    $line = "";
    $comma = "";                       // a fun hack
    foreach($row as $value) {
       if (is_numeric($value)) {                    // a number, than do NOT quote
             $line .= $comma . $value  ;
       } else {
          if ($escape==1) {                        // might be crlfs and other stuff -- then escape it (CSV-escaped file)
             $line .= $comma . '"' . rawurlencode($value) . '"';
          } else {                                                   // deal with "s
            $line .= $comma . '"' . str_replace('"', '""', $value) . '"';
          }
       }
       $comma = ",";              // hack for fun!

    }
    $line .= "\n";
    fputs($fp, $line);
    $nrows++ ;

}

fclose($fp);
return $nrows ;
}

//================================
// dump sql database to output file (entire database, not just one table)
// Note: restoring from this (using restoreMysqlDatabase will overwrite an existing version of the database (on the mysql server)
function saveMysqlDatabase($database,$outfile) {

//'dsurveyAdmin','admin'
$command="mysqldump  --add-drop-table --host=localhost --user=dsurveyAdmin --password=admin $database > $outfile ";
print "Command=  $command ";
$qq=system($command,$arf);
return $arf ;
}

// restore sql database from output file (entire database, not just one table)
function restoreMysqlDatabase($database,$infile) {

//'dsurveyAdmin','admin'
$command="mysql  --host=localhost --user=dsurveyAdmin --password=admin $database < $infile ";
$qq=system($command);
return $qq ;
}


//=============
// save & restore a mysql database table to $outfile
// this assumes
/// 1) $link has been set,
//     i.e.; $link=mysql_connect('localhost','dsurveyAdmin','admin');
//  2) that the database has been then selected,
//      ie; using:  $mdata=$hdrMysql . $aname ; $qq=mysql_select_db($mdata, $link);
// Note:  say using: $link=connectDsurveyDatabase($sessName); will do both of the above steps
// THIS WILL NOT SAVE TABLE STRUCTURE __ ONLY CONTENT!

function saveMysqlTable($tableName,$backupFile,$link) {

$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName ";
print " xyx= " . $query . " \n " ;
$result = mysql_query($query,$link);
return $result . ":: " .  mysql_error($link)  ;
}

// THIS WILL NOT  RESTORE TABLE STRUCTURE __ ONLY CONTENT!
function restoreMysqlTable($tableName,$backupFile,$link) {

$query="truncate TABLE $tableName ";
$result = mysql_query($query,$link);  // if tablename doesn't exist, no problem
print "check1 = $result ";
$query=  "LOAD DATA INFILE '$backupFile' INTO TABLE $tableName";
print " bxyx= " . $query . " \n " ;
$result = mysql_query($query,$link);
return "Status:  $result . " .  mysql_error($link)  ;
}


//===========================
// (adapted from:  http://www.hotscripts.com/forums/php/26985-need-php-script-export-mysql-data.html
// create a string that can be used to recreate current database.
// You could then save this string to a backup file
// this assumes
/// 1) $link has been set,
//     i.e.; $link=mysql_connect('localhost','dsurveyAdmin','admin');
//  2) that the database has been then selected,
//      ie; using:  $mdata=$hdrMysql . $aname ; $qq=mysql_select_db($mdata, $link);
// Note:  say using: $link=connectDsurveyDatabase($sessName); will do both of the above steps
// Files created with this can be used by myqli_multi_query to restore a database

function makeDbBackup($link,$sessName) {
  $backup='';
  $ntables=0;

$vudate=date(DATE_RFC822)  ;

$tab_status = mysql_query("SHOW TABLE STATUS",$link);
while($all = mysql_fetch_assoc($tab_status)):
    $tbl_stat[$all['Name']] = $all['Auto_increment'];
endwhile;

//$tables = mysql_list_tables($newname);
$tables=mysql_query("SHOW TABLES",$link);
while($tabs = mysql_fetch_row($tables)):
    $backup .= "-- \n--  For dSurvey session $sessName on $vudate \n";
    $backup .= "-- \n-- Table structure for `$tabs[0]`\n-- \n\nDROP TABLE IF EXISTS `$tabs[0]` ; \nCREATE TABLE IF NOT EXISTS `$tabs[0]` (";
    $res = mysql_query("SHOW CREATE TABLE $tabs[0]");
    while($all = mysql_fetch_assoc($res)):
        $str = str_replace("CREATE TABLE `$tabs[0]` (", "", $all['Create Table']);
        $str = str_replace(",", ",", $str);
        $str2 = str_replace("`) ) TYPE=MyISAM ", "`)\n ) TYPE=MyISAM ", $str);
       if ($tbl_stat[$tabs[0]]!='') {
            $backup .= $str2." AUTO_INCREMENT=".$tbl_stat[$tabs[0]].";\n\n";
       } else {
            $backup .= $str2.";\n\n";
       }
    endwhile;
    $backup .= "-- \n-- Data to be executed for table `$tabs[0]`\n-- \n\n";
    $data = mysql_query("SELECT * FROM $tabs[0]");
    while($dt = mysql_fetch_row($data)):
        $backup .= "INSERT INTO `$tabs[0]` VALUES('$dt[0]'";
        for($i=1; $i<sizeof($dt); $i++):
            $aval=mysql_real_escape_string($dt[$i],$link);
//            $aval=$dt[$i] ;

            $backup .= ", '$aval'";
//            $backup .= ", '$dt[$i]'";
        endfor;
        $backup .= ");\n";
    endwhile;
    $backup .= "\n-- --------------------------------------------------------\n\n";
endwhile;

return $backup ;
}


//===============================
// restore a mySQL database, using contents of $query.
// Note: this assumes the database exists (even if it is empty)
// Typically, $query is the contents of dbBakcup file, as created by mkDbBackup
// returns # of commands processed
// this assumes
/// 1) $link has been set,
//     i.e.; $link=mysqli_connect('localhost','dsurveyAdmin','admin');  (NOTE: Must be the "improved" connect function)
//  2) that the database has been then selected,
//      ie; using:  $mdata=$hdrMysql . $aname ; $qq=mysqli_select_db($mdata, $link);
// Note:  say using: $link=connectDsurveyDatabaseI($sessName); will do both of the above steps
// This uses mysqli_mutli_query, which expect ; delimited queries (that should be escaped)


function restoreDbBackup($link,$query) {
$vudate=date(DATE_RFC822)  ;
$ndo=0 ;

//$qq=mysqli_multi_query($link,$query);
//return $qq ;
// have to clear stuff in store_result to avoid problems
$qq =mysqli_multi_query($link, $query);

if ($qq) {
    do {
        $ndo++ ;
        $result = mysqli_store_result($link);
        if ($result) {

            while ($row = mysqli_fetch_row($result)) {
//                 print "\n" . $row[0] ;
            }
//            mysqli_free_result($result);
        if (mysqli_more_results($link)) {
//            printf("-----------------\n");
        }

        }
    } while (mysqli_next_result($link));
} else {
    print "-ERROR: " . mysqli_error($link). " \n ";
}

/* close connection */
mysqli_close($link);
return $ndo ;

}

?>