Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?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;
- ?>
Add Comment
Please, Sign In to add comment