<?
$msql_user = 'auth';
$msql_pass = 'authpass1';
$msql_db = 'note2self';

$msql_user2 = 'n2s';
$msql_pass2 = 'KnowIt!';
$msql_db2 = 'note2self2';

@ $db = new mysqli('localhost', $msql_user2, $msql_pass2, $msql_db2);

if (mysqli_connect_errno()) {
   echo "Error: Could not connect to database. Please try again later.";
} else {
   $submit_date = date('Y-m-d H:i:s');
   $action = $_REQUEST['a'];
   if($action == 'submitok') {
      
      $rawtok = @$_REQUEST['rawtok'];
      $ref = @$_REQUEST['ref'];
      $fname = @$_REQUEST['fname'];
      $pub = @$_REQUEST['pub'];
      $place = @$_REQUEST['place'];
      $author = @$_REQUEST['author'];
      $used = @$_REQUEST['used'];
      $overwrite = @$_REQUEST['overwrite'];
      $archive = @$_REQUEST['archive'];

      $refid = $fnameid = $pubid = $placeid = 0;
      $alert = '';
      
      if($ref) {
         $query = "select id,ref from ref where ref = '$ref'";
         if(!mysqli_num_rows($result = $db->query($query))) {
            $query = "insert into ref values (NULL, '$ref')";
            $result = $db->query($query);
            if(!$db->affected_rows) { $alert .= 'Cannot insert Source into database. '; } else { $refid = $db->insert_id; }
         } else {
            $row = $result->fetch_object();
            $refid = $row->id;
         }
      }
      if($fname) {
         $query = "select id,fname from fname where fname = '$fname'";
         if(!mysqli_num_rows($result = $db->query($query))) {
            $query = "insert into fname values (NULL, '$fname')";
            $result = $db->query($query);
            if(!$db->affected_rows) { $alert .= 'Cannot insert Filename into database. '; } else { $fnameid = $db->insert_id; }
         } else {
            $row = $result->fetch_object();
            $fnameid = $row->id;
         }

      }
      if($pub) {
         $query = "select id,pub from pub where pub = '$pub'";
         if(!mysqli_num_rows($result = $db->query($query))) {
            $query = "insert into pub values (NULL, '$pub')";
            $result = $db->query($query);
            if(!$db->affected_rows) { $alert .= 'Cannot insert Publication into database. '; } else { $pubid = $db->insert_id; }
         } else {
            $row = $result->fetch_object();
            $pubid = $row->id;
         }

      }
      if($place) {
         $query = "select id,place from place where place = '$place'";
         if(!mysqli_num_rows($result = $db->query($query))) {
            $query = "insert into place values (NULL, '$place')";
            $result = $db->query($query);
            if(!$db->affected_rows) { $alert .= 'Cannot insert Place into database. '; } else { $placeid = $db->insert_id; }
         } else {
            $row = $result->fetch_object();
            $placeid = $row->id;
         }
      }
      if($overwrite == 'y') {
         $query = "delete from data where ref = $refid and fname = $fnameid and pub = $pubid and place = $placeid and author = '$author'";
         $result = $db->query($query);
         $alert .= $db->affected_rows.' rows deleted. ';
      }

      $para = $line = 1;
      $sentn = '';
      for($i=0;$i<=strlen($rawtok)-1;$i++) {
         $char=substr($rawtok,$i,1);
         if($char == "'" || $char == '"') { $char = '\\'.$char; $sentn = $sentn.$char; if(preg_match('/[!?.]/',substr($rawtok,$i-1,1))) { $char = substr($rawtok,$i-1,1); } } else
         if(preg_match('/[!?.]/',$char) && substr($rawtok,$i+1,1) == ')') { $sentn = $sentn.$char.')'; $char = ')'; $i++; } else
         if($char != "\n" && $char != ')') { $sentn = $sentn.$char; }
         if($char == ')' && preg_match('/[!?.\n]/',substr($rawtok,$i+1,1))) { $char = substr($rawtok,$i+1,1); $sentn = $sentn.')'.$char; $i++; }
         if($char == ')' && !preg_match('/[!?.]/',substr($rawtok,$i-1,1))) { $char = substr($rawtok,$i+1,1); $sentn = $sentn.')'.$char; $i++; }
         $trip = 0;
         if(substr_count($sentn,'"') == 1) { $trip = 1; }
         if(substr($rawtok,$i-3,3) == 'i.e') { $trip = 1; }
         if(substr($rawtok,$i-3,3) == ' vs') { $trip = 1; }
         if(substr($rawtok,$i-3,3) == 'e.g') { $trip = 1; }
         if((preg_match('/[!?.)]/',$char) && $i<strlen($rawtok)-1 && preg_match('/[\n ]/',substr($rawtok,$i+1,1)) && !$trip) || ($sentn && $i==strlen($rawtok)-1)) {
            $query = "insert into data (sentn,ref,fname,pub,place,author,para,line,procdate,used,ego)
            values ('".ltrim($sentn)."',$refid,$fnameid,$pubid,$placeid,'$author',$para,$line,'$submit_date','$used','$archive')";
            $result = $db->query($query);
            if(!$db->affected_rows) {
               $alert .= 'Error submitting line #'.$line.'. ';
            }
            $sentn = '';
            $line++;
         }
         if($char == "\n" && substr($rawtok,$i+1,1) == "\n" && $sentn) {
            $query = "insert into data (sentn,ref,fname,pub,place,author,para,line,procdate,used)
            values ('".ltrim($sentn)."',$refid,$fnameid,$pubid,$placeid,'$author',$para,$line,'$submit_date','$used')";
            $result = $db->query($query);
            if(!$db->affected_rows) {
               $alert .= 'Error submitting line #'.$line.'. ';
            }
            $sentn = '';
            $line++;
            $para++;
            $i++;
         } else if($char == "\n" && substr($rawtok,$i+1,1) == "\n" && !$sentn) {
            $para++;
            $i++;
         } else if($char == "\n") {
            $sentn = $sentn."\n";
         }
      }
      $alert .= ' '.($line-1).' lines added. ('.$para.' paragraphs.)';
      echo $alert;
   }
   if($action == 'search') {
      $srcselectmult = array();
      $pubselectmult = array();
      $placeselectmult = array();
      $searchterm = @$_REQUEST['searchterm'];
      $showused = @$_REQUEST['showused'];
      $usedonly = @$_REQUEST['usedonly'];
      $allsources = @$_REQUEST['allsources'];
      $allpublications = @$_REQUEST['allpublications'];
      $allplaces = @$_REQUEST['allplaces'];
      $srcselectmult = @$_REQUEST['srcselectmult'];
      $pubselectmult = @$_REQUEST['pubselectmult'];
      $placeselectmult = @$_REQUEST['placeselectmult'];
      $fileselect = @$_REQUEST['fileselect'];
      $searchresults = array();
      $substr = '';
      if(substr_count($searchterm,' ')) {
         foreach(explode(' ',$searchterm) as $val) {
            $substr .= "sentn like '%$val%' or ";
         }
         $substr = '('.substr($substr,0,-4).')';
      } else {
         $substr = "sentn like '%$searchterm%'";
      }
      if(!empty($srcselectmult)) {
         $substr2 = ' and ref in ('.implode(',',$srcselectmult).')';
         $substr = $substr.$substr2;
      }
      if(!empty($pubselectmult)) {
         $substr2 = ' and pub in ('.implode(',',$pubselectmult).')';
         $substr = $substr.$substr2;
      }
      if(!empty($placeselectmult)) {
         $substr2 = ' and place in ('.implode(',',$placeselectmult).')';
         $substr = $substr.$substr2;
      }
      if(!empty($fileselect)) {
         $substr2 = ' and fname = '.$fileselect;
         $substr = $substr.$substr2;
      }
      $query = "select id,sentn from data where $substr order by id asc";
      $result = $db->query($query);
      if(mysqli_num_rows($result)) {
         $searchresults[0] = '<p>'.mysqli_num_rows($result).' sentences found.</p>';
      } else {
         $searchresults[0] = '<p>No sentences found.</p>';
      }
      while($row = $result->fetch_assoc()) {
         $searchresults[$row['id']] = addslashes($row['sentn']);
      }
      echo json_encode($searchresults);
   }
   if($action == 'parafill') {
      $id = @$_REQUEST['id'];
      $di = @$_REQUEST['dir'];
      $query = "select ref,fname,pub,place,para,author from data where id = $id";
      $result = $db->query($query);
      $row0 = $result->fetch_assoc();
      $ref = $row0['ref'];
      $fname = $row0['fname'];
      $pub = $row0['pub'];
      $place = $row0['place'];
      $author = $row0['author'];
      $para = $row0['para'];
      if($di == 'p') {--$para;}
      if($di == 'n') {++$para;}
      $query = "select id,sentn,suf from data where ref = $ref and fname = $fname and pub = $pub and place = $place and author = '$author' and para = $para order by id asc";
      $result = $db->query($query);
      while($row = $result->fetch_assoc()) {
         $parafillresults[0][$row['id']] = $row['sentn'];
         $parafillresults[1][$row['id']] = $row['suf'];
      }
      if($di == 'p' && !$result->num_rows) { $parafillresults['err'] = 'First paragraph in origin.'; }
      if($di == 'n' && !$result->num_rows) { $parafillresults['err'] = 'Last paragraph in origin.'; }
      echo json_encode($parafillresults);
   }
   if($action == 'joinsentn') {
      $selectedsentn = @$_REQUEST['selectedsentn'];
      $query = "select id,sentn from data where id in (".substr($selectedsentn,0,-1).") order by id asc";
      $newsentn = '';
      $idtarget = array();
      $idtarget = explode(',',substr($selectedsentn,0,-1));
      $result = $db->query($query);
      while($row = $result->fetch_assoc()) {
         if($row['sentn']=='.' || (substr($row['sentn'],0,1)=='.' && substr($row['sentn'],0,3)!='...')) {
            $newsentn = $newsentn.$row['sentn'];
         } else {
            $newsentn = $newsentn.' '.$row['sentn'];
         }
      }
      $newsentn = trim($newsentn);
      $query = "update data set sentn = '".addslashes($newsentn)."' where id = $idtarget[0]";
      $result = $db->query($query);
      $msg['result'] = $db->affected_rows.' row(s) changed. ';
      $query = "delete from data where id = $idtarget[1]";
      $result = $db->query($query);
      $msg['result'] = $msg['result'].$db->affected_rows.' row(s) deleted.';
      $msg['id1'] = $idtarget[0];
      echo json_encode($msg);
   }
   if($action == 'breaksentn') {
      $selectedsentn = trim(@$_REQUEST['selectedsentn'],',');
      $query = "update data set suf = 'b' where id = $selectedsentn";
      $result = $db->query($query);
      $msg['result'] = $db->affected_rows.' row changed.';
      $msg['id'] = $selectedsentn;
      echo json_encode($msg);
   }
   if($action == 'buildsrc') {
      $query = "select id,ref from ref order by ref asc";
      $result = $db->query($query);
      $choices = '<option value="">Select...</option>'."\n";
      while($row = $result->fetch_assoc()) {
         $choices = $choices.'<option value="'.$row['id'].'">'.$row['ref']."</option>\n";
      }
      echo $choices;
   }
   if($action == 'buildfile') {
      $query = "select id,fname from fname order by fname asc";
      $result = $db->query($query);
      $choices = '<option value="">Select...</option>'."\n";
      while($row = $result->fetch_assoc()) {
         $choices = $choices.'<option value="'.$row['id'].'">'.$row['fname']."</option>\n";
      }
      echo $choices;
   }
   if($action == 'buildpub') {
      $query = "select id,pub from pub order by pub asc";
      $result = $db->query($query);
      $choices = '<option value="">Select...</option>'."\n";
      while($row = $result->fetch_assoc()) {
         $choices = $choices.'<option value="'.$row['id'].'">'.$row['pub']."</option>\n";
      }
      echo $choices;
   }
   if($action == 'buildplace') {
      $query = "select id,place from place order by place asc";
      $result = $db->query($query);
      $choices = '<option value="">Select...</option>'."\n";
      while($row = $result->fetch_assoc()) {
         $choices = $choices.'<option value="'.$row['id'].'">'.$row['place']."</option>\n";
      }
      echo $choices;
   }
   if($action == 'buildauthor') {
      $query = "select unique author from data order by author asc";
      $result = $db->query($query);
      $choices = '<option value="">Select...</option>'."\n";
      while($row = $result->fetch_assoc()) {
         $choices = $choices.'<option value="'.$row['author'].'">'.$row['author']."</option>\n";
      }
      echo $choices;
   }
   if($action == 'buildsentndata') {
      $selectedsentn = trim(@$_REQUEST['selectedsentn'],',');
      $query = "select ref,fname,pub,place,author,para,line,ego from data where id = $selectedsentn";
      $result = $db->query($query);
      $row = $result->fetch_assoc();
      $data['ref'] = $row['ref'];
      $data['fname'] = $row['fname'];
      $data['pub'] = $row['pub'];
      $data['place'] = $row['place'];
      $data['author'] = $row['author'];
      $data['para'] = $row['para'];
      $data['line'] = $row['line'];
      if(substr_count($row['ego'],'a')) {
         $query = "select fname as archive from fname where id = ".$row['fname'];
         $result = $db->query($query);
         $row = $result->fetch_assoc();
         $data['archive'] = substr($row['archive'],0,strpos($row['archive'],'.')).'.zip';
      } else { $data['archive'] = ''; }
      echo json_encode($data);
   }
   if($action == 'extract') {
      $response = '';
      $ids = @$_REQUEST['ids'];
      $idstr = '';
      foreach(explode(',',$ids) as $val){
         $idstr .= "'".$val."',";
      }
      $idstr = trim($idstr,',');
      $query = "select sentn from data where id in ($idstr)";
      $result = $db->query($query);
      while($row = $result->fetch_assoc()) {
         $response .= $row['sentn'].' ';
      }
      $response = trim($response);
      echo $response;
   }
}
?>