<?php
/**
 * Database.php
 *
 * The Database class is meant to simplify the task of accessing
 * information from the website's database.
 *
 */
include_once("constants.php");
include_once("NewDB.php");

class MySQLDB {
//   var $connection;         //The MySQL database connection
//   var $num_active_users;   //Number of active users viewing site
//   var $num_active_guests;  //Number of active guests viewing site
//   var $num_members;        //Number of signed-up users

   /* New constuctor */
   function __construct() {
      // assume something went wrong by default
      $return = FALSE;

      // Keep around the old pg connection for testing
      $conn_string = "dbname=".DB_NAME." host=".DB_SERVER." user=".DB_USER." password=".DB_PASS;
      $this->connection = pg_connect($conn_string);

      if ($this->connection == false) {
         echo "error connecting to database<br />";
      }

        // Create handle for SSDB collection
      $dbh = new NewDB(DB_SERVER, DB_NAME, DB_USER, DB_PASS);

      // Check for errors, return object
      if ($dbh !== FALSE && $dbh_users !== FALSE) {
         $this->PDO = $dbh->PDO;
         $return = $this;
      }

      return $return;
   }
//#######################


   /**
    * addUploadFile single upload -- adds a file/username entry to the upload_files table in the SSDB database
   **/
   function addUploadFileSingle($username, $filename, $localpath, $proposal, $type, $status, $accesscontrol, $filesize, $sitenames)
   {
      $date = date("Y-m-d");
      $destination_path = "../upload/$proposal/";
      //$filesize = filesize("$destination_path$filename");

      $filename = addslashes($filename);
      $localpath = addslashes($localpath);

      $sql = "INSERT INTO ".TBL_UPLOAD_FILES."(id, username, filename, localpath, proposal, type, status, accesscontrol, filesize, sitenames) VALUES (DEFAULT, :username, :filename, :localpath, :proposal, :type, :status, :accesscontrol, :filesize, :sitenames)";

      $stmt = $this->PDO->prepare($sql);
      $stmt->bindParam(':username', $username);
      $stmt->bindParam(':filename', $filename);
      $stmt->bindParam(':localpath', $localpath);
      $stmt->bindParam(':proposal', $proposal);
      $stmt->bindParam(':type', $type);
      $stmt->bindParam(':status', $status);
      $stmt->bindParam(':accesscontrol', $accesscontrol);
      $stmt->bindParam(':filesize', $filesize);
      $stmt->bindParam(':sitenames', $sitenames);

      $stmt->execute();

      $sql = 'SELECT last_value from public.upload_files_id_seq';
      $stmt = $this->PDO->prepare($sql);

      try {
         $stmt->execute();
         $result = $stmt->fetchAll(PDO::FETCH_ASSOC)[0];
         $insert_id = $result['last_value'];
         $return = $insert_id;
      } catch (PDOException $e) {
         $return = $e;
      }

      return $return;

   }


   /**
    * addUploadFile single upload -- adds a file/username entry to the upload_files table in the SSDB database
   **/
   function editUploadFileSingle($id, $accesscontrol, $sitenames)
   {
      # make a copy in history
      $sql = "INSERT INTO upload_files_history(id, username, filename, localpath, proposal, type, status, accesscontrol, filesize, upload_date, submit_date,qc_id, sitenames) SELECT id, username, filename, localpath, proposal, type, status, accesscontrol::accesscontrol, filesize, upload_date, submit_date,qc_id, sitenames from upload_files WHERE id = :id";

      $stmt = $this->PDO->prepare($sql);
      $stmt->bindParam(':id', $id);
      $stmt->execute();

      $date = date("Y-m-d");
      $destination_path = "../upload/$proposal/";
      //$filesize = filesize("$destination_path$filename");

      $filename = addslashes($filename);
      $localpath = addslashes($localpath);

      $sql = "UPDATE ".TBL_UPLOAD_FILES." SET accesscontrol = :accesscontrol, sitenames = :sitenames WHERE id = :id";

      $stmt = $this->PDO->prepare($sql);
      $stmt->bindParam(':accesscontrol', $accesscontrol);
      $stmt->bindParam(':sitenames', $sitenames);
      $stmt->bindParam(':id', $id);

      try {
         $stmt->execute();
      } catch (PDOException $e) {
         $return = $e;
      }

      return $return;
   }


   /**
    * getAllDataPackagesArray - Return all of the data packages in SSDB, so
    * they can be sorted/filtered by the caller
    *
    * @param (none)
    *
    * @returns allDataPackagesArray

   **/
   function getAllDataPackagesArray( )
   {
      $dataPackageArray = [];

      // set up the SQL query
      $sql = <<<SQL
      SELECT * FROM package
      ORDER BY package.date DESC;
SQL;
      $stmt = $this->PDO->prepare($sql);

      try {
         $ex_status = $stmt->execute();
         if ( $ex_status === TRUE ) {
            $dataPackageArray = $stmt->fetchAll();
         }
      } catch (PDOException $e) {
         error_log($e);
         return FALSE;
      }

      // return either the empty array (if there were no data packages), or
      // whatever data packages are in the database
      return $dataPackageArray;
   }


   /**
    * getAllUploadedFiles - returns an array of files from the
    * 'upload_files' table for a given proposal ID
    *
    * @param proposal_id
    * @param file_status (optional)
    * @param order_by (optional)
    *
    * @returns array uploadedFiles
    *
   **/
   function getAllUploadedFiles(
      $proposal_id = P918,
      $file_type   = Documents
   )
   {
      // assume failure
      $return = FALSE;

      // no proposal ID, return
      if ( $proposal_id === NULL )
         return $return;

      $sql = "SELECT * from " . TBL_UPLOAD_FILES
         . " WHERE proposal = :proposal_id ";

      if ( $file_type !== NULL )
         $sql .= "AND type = :file_type ";

      # "prepare" the SQL query
      $stmt = $this->PDO->prepare($sql);
      $stmt->bindParam(':proposal_id', $proposal_id);
      if ( $file_type !== NULL )
         $stmt->bindParam(':file_type', $file_type);

      try {
         $ex_status = $stmt->execute();
         if ( $ex_status === TRUE ) {
            $fetchedArray = $stmt->fetchAll(PDO::FETCH_ASSOC);
            // return the array if there is one, otherwise, return an empty
            // array
            if ( count($fetchedArray) > 0 ) {
               return $fetchedArray;
            } else {
               return [];
            }
         }
      } catch (PDOException $e) {
         error_log($e);
         return FALSE;
      }

      // we should only get here if the database query failed for whatever
      // reason, or there are no package records in the database
      return $return;
   }


   function runMyQuery()
   {
      $dataPackageArray = [];

      // set up the SQL query
      $sql = <<<SQL
      SELECT COUNT(proposal) FROM upload_files
      WHERE proposal = 'P918' AND type = 'SeismicMultiChannel';
SQL;
      $stmt = $this->PDO->prepare($sql);

      try {
         $ex_status = $stmt->execute();
         if ( $ex_status === TRUE ) {
            $dataPackageArray = $stmt->fetchAll();
         }
      } catch (PDOException $e) {
         error_log($e);
         return FALSE;
      }

      // return either the empty array (if there were no data packages), or
      // whatever data packages are in the database
      return $dataPackageArray;
   }


// Helen's workspace

   function getProposalList()
   {
      $dataPackageArray = [];
      $sql1 = <<<SQL
      SELECT DISTINCT(proposal) FROM upload_files
      WHERE proposal LIKE 'P___' ORDER BY proposal
SQL;
      $stmt = $this->PDO->prepare($sql1);
      try {
         $ex_status = $stmt->execute();
         if ( $ex_status === TRUE ) {
            $dataPackageArray = $stmt->fetchAll();
         }
      } catch (PDOException $e) {
         error_log($e);
         return FALSE;
      }
      return $dataPackageArray;
   }


   function getTypeList()
   {
      $dataPackageArray = [];
      $sql2 = <<<SQL
      SELECT DISTINCT(type) FROM upload_files
SQL;
      $stmt = $this->PDO->prepare($sql2);
      try {
         $ex_status = $stmt->execute();
         if ( $ex_status === TRUE ) {
            $dataPackageArray = $stmt->fetchAll();
         }
      } catch (PDOException $e) {
         error_log($e);
         return FALSE;
      }
      return $dataPackageArray;
   }

   function countsEachType() {
      $typeCounts = [];

      $sql2 = <<<SQL
      SELECT type, count(type) from upload_files where type<>'' group by type
SQL;
      $stmt = $this->PDO->prepare($sql2);
      try {
         $ex_status = $stmt->execute();
         if ( $ex_status === TRUE ) {
            $typeCounts = $stmt->fetchAll();
         }
      } catch (PDOException $e) {
         error_log($e);
         return FALSE;
      }
      return $typeCounts;
   }

   function typeCountByProposal() {
      $counts = [];

      $sql2 = <<<SQL
      SELECT proposal, type, COUNT(type)
      FROM (
         SELECT proposal, type
         FROM upload_files
         WHERE proposal<>'' AND proposal LIKE 'P___'
         ORDER BY proposal, type
      ) as results
      GROUP BY proposal, type
SQL;
      $stmt = $this->PDO->prepare($sql2);
      try {
         $ex_status = $stmt->execute();
         if ( $ex_status === TRUE ) {
            $counts = $stmt->fetchAll();
         }
      } catch (PDOException $e) {
         error_log($e);
         return FALSE;
      }
      return $counts;
   }


   function runQueryTest(
      $proposal_id = array("P913", "P915", "P918"),
      $type_id     = LocationMaps
   )
   {
      $dataPackageArray = [];
      $sql = "SELECT COUNT(*) FROM " . TBL_UPLOAD_FILES
         . " WHERE proposal = :proposal_id "
         . " AND type = :type_id ";

      // prepare the SQL query

         $stmt = $this->PDO->prepare($sql);

      $i = 0;
      while ($i < count($proposal_id)) {
         $stmt->bindParam(':proposal_id', $proposal_id[$i]);
         $i += 1;
      }
         $stmt->bindParam(':type_id', $type_id);
         $stmt->execute();
         $dataPackageArray = $stmt->fetchAll();

         return $dataPackageArray;

   }



};

/* Create database connection */
$database = new MySQLDB;
?>