<?php

/*
LabStoRe
Version: 1.6.3
Date: 6 September 2014
Copyright: Santosh Patnaik, MD, PhD
License: GPL 3+
URL: www.bioinformatics.org/phplabware
*/

include(realpath(dirname(__FILE__) . '/start.php'));

$data = '';

// Only if export button pressed and mysql query and table name and type of export known
if(isset($_POST['export']) and isset($_POST['parameter']) and isset($_POST['table'])) {
  ///////////////////////////////////////////////////////////////////////////////////
  $table = $_POST['table'];
  $date = date("Ymd");
  $type = $_POST['parameter'] == 'Excel' ? 'Excel' : 'CSV';
  // for query
  foreach(array('sterm_1', 'sterm_2', 'sbool', 'smenu_1', 'smenu_2', 'sort_1', 'sort_2', 'order_1', 'order_2') as $x) {
    $y = '';
    if(isset($_GET[$x])) {
      $y = $_GET[$x];
    }
    if(isset($_POST[$x])) {
      $y = $_POST[$x];
    }
    $myget[$x] = $y;
    if(!isset($myget['sbool']) or strtolower($myget['sbool']) != 'and') {
      $sbool = "OR";
    }
    else {
      $sbool = 'AND';
    }
    if(!isset($myget['order_1']) or strtolower($myget['order_1']) != 'desc') {
      $myget['order_1'] = "ASC";
    }
    else {
      $myget['order_1'] = 'DESC';
    }
    if(!isset($myget['order_2']) or strtolower($myget['order_2']) != 'desc') {
      $myget['order_2'] = "ASC";
    }
    else {
      $myget['order_2'] = 'DESC';
    }
  }
  $where_condition = "";
  if(isset($myget['sterm_1']) and $myget['sterm_1'] != '') {
    if(isset($myget['sterm_2']) and $myget['sterm_2'] != '') {
      $where_condition = " WHERE `" . add_slashes($myget['smenu_1'], 1) . "` LIKE '%" . add_slashes($myget['sterm_1'], 2) . "%' " . $sbool . " `" . add_slashes($myget['smenu_2'], 1) . "` LIKE '%" . add_slashes($myget['sterm_2'], 2) . "%'";
    }
    else {
      $where_condition = " WHERE `" . add_slashes($myget['smenu_1'], 1) . "` LIKE '%" . add_slashes($myget['sterm_1'], 2) . "%'";
    }
  }
  else {
    if(isset($myget['sterm_2']) and $myget['sterm_2'] != '') {
      $where_condition = " WHERE `" . add_slashes($myget['smenu_2'], 1) . "` LIKE '%" . add_slashes($myget['sterm_2'], 2) . "%'";
    }
  }
  // End ------------- get where conditions for query
  
  // Start ----------- sort conditions for mysql query
  $order_condition = "`name` ASC";
  if($table == $users_table_name) {
    $order_condition = "`" . $users_table_username_field . "` ASC";
  }
  if(isset($myget['sort_1']) and $myget['sort_1'] != '') {
    if(isset($myget['order_1'])) {
      $order_condition = "`" . add_slashes($myget['sort_1'], 1) . "` " . $myget['order_1'];
    }
    else {
      $order_condition = "`" . add_slashes($myget['sort_1'], 1) . "` ASC";
    }
    // if the optional second sort option was chosen - conditional on 1st being chosen
    if(isset($myget['sort_2']) and $myget['sort_2'] != '' and $myget['sort_2'] !== $myget['sort_1']) {
      if(isset($myget['order_2'])) {
        $order_condition .= ", `" . add_slashes($myget['sort_2'], 1) . "` " . $myget['order_2'];
      }
      else {
        $order_condition .= ", `" . add_slashes($myget['sort_2'], 1) . "` ASC";
      }
    }
  }
  
  $query = "SELECT * FROM `" . add_slashes($table, 1) . "` " . $where_condition . ' ORDER BY ' . $order_condition;
  $result = execute_db($query, $conn, 'labstore/export.php');
  // No. of rows
  $count = database_num_rows($result);
  // No. of columns
  $count_cols = database_num_fields($result);
  // Set filename - tablename_date format
  $filename = $table . "_" . $date;
  
  // If Excel, header is column headings; data is rest, made of many lines each made of many values
  ///////////////////////////////////////////////////////////////////////////////////
  if($type == "Excel") {
    $header = "Downloaded " . $date . " from " . $db_name . " MySQL database. More up-to-date data is available on the website. Refer to 'help' on the site for understanding the column headings.\n";
    // the column headings -------------------------------------
    for($i = 0;$i < $count_cols;$i++) {
      $header .= database_field_name($result, $i) . "\t";
    }
    // The rows ------------------------------------------------
    while($row = database_fetch_row($result)) {
      $line = '';
      foreach($row as $value) {
        if(!isset($value) OR $value == "") {
          $value = "\t";
        }
        else {
          $value = str_replace('"', '""', $value);
          $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
      }
      $data .= trim($line) . "\n";
    }
    // Clean ---------------------------------------------------
    $data = str_replace("\r", " ", $data);
    if($data == "") {
      $data = "\nno matching records found\n";
    }
    // Start sending -------------------------------------------
    ob_end_clean();
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=" . $filename . ".xls");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
    header("Pragma: public");
    echo($header . "\n" . $data);
  }
  ///////////////////////////////////////////////////////////////////////////////////
  
  if($type == "CSV") {
    $csv = "Downloaded on date (YearMonthDay) " . $date . " from '" . $db_name . "' MySQL database. More up-to-date data is available on the LabStoRe website. Refer to 'help' on the site for understanding the column headings.\n";
    // Heading -------------------------------------------------
    for($i = 0;$i < $count_cols;$i++) {
      $csv .= database_field_name($result, $i) . ",";
    }
    // Delete the last "," and make it a line
    $csv = substr($csv, 0, - 1);
    $csv .= "\n";

    // Rest ----------------------------------------------------
    while($row = database_fetch_row($result)) {
      $line = '';
      foreach($row as $value) {
        // Value - empty - put comma separator
        if(!isset($value) OR $value == "") {
          $value = ",";
        }
        else {
          // Value - not empty - clean and put comma separator
          $value = str_replace('"', '""', $value);
          $value = str_replace('\r', '"\r"', $value);
          $value = str_replace('\n', '"\n"', $value);
          $value = '"' . $value . '"';
          $value = $value . ",";
        }
        // String values to make line
        $line .= $value;
      }
      // Delete the last "," and make it a line
      $line = substr($line, 0, - 1);
      $csv .= $line . "\n";
    }
    // Start sending -------------------------------------------
    ob_end_clean();
    header("Content-Type: text/x-csv");
    header("Content-Disposition: attachment; filename=" . $filename . ".csv");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
    header("Pragma: public");
    echo($csv);
  }
  ///////////////////////////////////////////////////////////////////////////////////
}