class/queue-database-construction.php

   1 <?php
   2
   3 class QueueDatabaseConstruction{
   4
   5         private $sql_data = array();
   6         private $connection = null;
   7
   8         public $die_state = array();
   9         public $comment_error = false;
  10         public $delete_status = false;
  11
  12         public $alphabet = ["a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p",
  13 "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"];
  14
  15         function __construct(){
  16                 $sql_ini = fopen("settings/sql.ini", "r");
  17                 while(!feof($sql_ini)){
  18                         $line = fgets($sql_ini);
  19                         $key = substr($line, 0, strpos($line, "="));
  20                         $value = trim(substr($line, strpos($line, "=")+1));
  21                         $this->sql_data[$key] = $value;
  22                 }
  23                 $this->connectToDatabase();
  24         }
  25
  26         function connectToDatabase(){
  27                 try {
  28             $this->connection = new PDO ("mysql:dbname=" . $this->sql_data["database"] . ";host=" . $this->sql_data["connection"],
  29                                                                                                 $this->sql_data["user"],        $this->sql_data["pass"]);
  30                         $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  31
  32         } catch (PDOException $e) {
  33             $this->logsys .= "Failed to get DB handle: " . $e->getMessage() . "\n";
  34         }
  35         }
  36
  37
  38         function getConnection(){
  39                 return $this->connection;
  40         }
  41         function addToTable($tablename, $paramaters){
  42                 $param_len = sizeof($paramaters);
  43                 $bind_string = "";
  44                 $table_string= "";
  45                 $first_comma = false;
  46                 foreach($paramaters as $key => $param){
  47                         if(!$first_comma){
  48                         $bind_string = ":$key";
  49                         $table_string = "`$key`";
  50                         $first_comma = true;
  51                         }
  52                         else{
  53                                 $bind_string .= ",:$key";
  54                                 $table_string .= ",`$key`";
  55                         }
  56                 }
  57                 $statement = $this->connection->prepare("INSERT INTO `".$this->sql_data["database"] ."`.`$tablename`($table_string) VALUES(" . $bind_string . ")");
  58
  59                 $index = 0;
  60                 foreach($paramaters as $key => $param){
  61                         $success =      $statement->bindParam(":" . $key , $paramaters[$key]);
  62                         $index++;
  63                 }
  64                 try{
  65                         $statement->execute();
  66                 }catch(Exception  $e){
  67                    echo "<strong>" . $e->getMessage() . "</strong><br/>";
  68                 }
  69         }
  70
  71         function buildThreadPosts($build_type, $display_type){
  72                 $threads = $this->getThreads();
  73                 $thread_counter = 0;
  74                 $row_size = 4;
  75                 $list_add = "";
  76                 if($display_type == "list") $list_add = "-list";
  77
  78                 if($build_type == "embeded"){
  79                         ob_start();
  80                         require_once("class/twitter-connection.php");
  81                         ob_clean();
  82
  83                         $twitter_connection = new TwitterConnection();
  84
  85                         foreach($threads as $thread){
  86                                 $post_id = $thread[0];
  87                                 if($thread_counter % $row_size == 0) echo"<ul class='row-container" . $list_add ."'>";
  88                                 echo "<li class='thread-container" . $list_add ."' PostNo='" . $post_id ."'>";
  89                                 echo "PostNo: " . $post_id;
  90
  91                                 echo $twitter_connection->getEmbededTweet($post_id)["html"];
  92
  93                                 echo "</li>";
  94                                                                 if($thread_counter % $row_size == $row_size - 1) echo"</ul>";
  95                                 $thread_counter++;
  96                         }
  97                 }
  98                 else{
  99                         foreach($threads as $thread){
 100                                 $post_id = $thread[0];
 101                                 if($thread_counter % $row_size == 0) echo"<ul class='row-container" . $list_add ."'>";
 102                                 echo "<li class='thread-container" . $list_add ."' PostNo='" . $post_id ."'>";
 103                                         echo "<div class='details" . $list_add ."'><ul>
 104                                         <li>PostNo: " . $post_id .
 105                                         "
 106                                         </li>
 107                                         <li  class='interaction-item" . $list_add ."'>
 108                                         <a href='/?thread=" . $post_id . "'>
 109                                                 Open
 110                                         </a>
 111                                         </li>
 112                                         <li  class='interaction-item" . $list_add ."'>
 113                                         <a href='https://twitter.com/Qazoku/status/". $post_id ."'>
 114                                                 Twitter
 115                                         </a>
 116                                         </li>
 117                                         </ul>
 118                                         </div>
 119                                         <div class='thread-contents" . $list_add ."'>
 120                                         <div class='thread-text'><blockquote>" . $thread["PostText"] ."</blockquote></div>
 121                                         <div class='thread-image" . $list_add ."'>";
 122                                         if($thread["ImageURL"] !== null)
 123                                                 $this->createMediaNodeFromRaw($thread["ImageURL"]);
 124                                         else echo "<img/>";
 125                                         echo "</div></div>";
 126                                 echo "</li>";
 127                                 if($thread_counter % $row_size == $row_size - 1) echo "</ul>";
 128                                 $thread_counter++;
 129                         }
 130                 }
 131         }
 132
 133         //Get the count of all items that are not unverified and not replies
 134         function getThreads(){
 135                 $statement = $this->connection->prepare("SELECT * FROM `Tweet`
 136                                                                                 LEFT OUTER JOIN `Response` ON `Response`.`PostID` = `Tweet`.`PostID`
 137                                                                                 LEFT OUTER JOIN `Unverified` ON `Unverified`.`PostID` = `Tweet`.`PostID`
 138                                                                                 WHERE `Unverified`.`PostID` IS NULL AND `Response`.`PostID` IS NULL
 139                                                                                 ORDER BY Tweet.PostID DESC");
 140                 try{
 141                         $statement->execute();
 142                         $threads = $statement->fetchAll();
 143                 }catch(Exception  $e){
 144                    echo "<strong>" . $e->getMessage() . "</strong><br/>";
 145                 }
 146                 return $threads;
 147         }
 148
 149         function deleteThread($table, $postID){
 150                 $postID_for_reference = $postID;
 151                 $statement = $this->connection->prepare("DELETE FROM `" . $this->sql_data["database"] ."`.`$table` WHERE `PostID` = :PostID");
 152                 $statement->bindParam(":PostID", $postID_for_reference);
 153                 //var_dump($statement);
 154                 echo "Delete: " . $postID_for_reference;
 155                 try{
 156                         $response = $statement->execute();
 157                 }catch(Exception  $e){
 158                    echo "<strong>" . $e->getMessage() . "</strong><br/>";
 159                 }
 160                 return $response;
 161         }
 162
 163         function deleteFromUnprocessedImageString($image_path_uprocessed){
 164                 if($image_path_uprocessed === null) return;
 165                 $image_path_uprocessed_arr = explode(",", $image_path_uprocessed);
 166                 foreach($image_path_uprocessed_arr as $unprocessed_path){
 167                         $path = rawurldecode($unprocessed_path);
 168                         unlink ($path);
 169                 }
 170         }
 171
 172         function recursiveDeleteResponses($post_id){
 173                 if($post_id == null){
 174                         return;
 175                 }
 176                 $statement = $this->connection->prepare("SELECT * FROM `Response` WHERE `Response`.`RepliesTo` = :replyID");
 177                 $statement->bindParam(":replyID", $post_id);
 178                 $statement->execute();
 179                 $response = $statement->fetchAll();
 180                 echo "A " . var_dump ($response[0][0]) . "<br/>";
 181                 $this->recursiveDeleteResponses($response[0][0]);
 182                 if($response[0] == null){
 183                         return;
 184                 }
 185                 else{
 186                         $statement = $this->connection->prepare("SELECT ImageURL FROM `Tweet` WHERE `Tweet`.`PostID` = :postid");
 187                         $statement->bindParam(":postid", $response[0][0]);
 188                         $statement->execute();
 189                         $image_response = $statement->fetchAll();
 190                         echo "B " .  $response[0][0] . " " . $image_response[0]["ImageURL"];
 191                         $this->deleteFromUnprocessedImageString($image_response[0]["ImageURL"]);
 192                         $this->deleteThread("Tweet", $response[0][0]);
 193                 }
 194         }
 195
 196         function buildQueueForm(){
 197                 echo'<div class="input-container">
 198                         <form action="add-to-queue.php" enctype="multipart/form-data" method="POST" target="_self">
 199                         <label>Comment:</label><br />
 200                         <textarea id="Comment" name="comment" rows="10" cols="60" placeholder = "Comment Text Here">';
 201                 echo '</textarea>
 202                         <p id="CharacterCount"></p>
 203
 204                         <input name="MAX_FILE_SIZE" type="hidden" value="5242880" />
 205                         <input name="file1" type="file" id="f1" /><input name="file2" type="file" id="f2" /><br/>
 206                         <input name="file3" type="file" id="f3" /><input name="file4" type="file" id="f4" /><br/>
 207                         </div>
 208                         <hr />
 209                         <p id="errorMsg">Input a comment and/or file</p>
 210                         <input id="submit" type="submit" /></form>
 211                 ';
 212         }
 213
 214         function buildPassForm(){
 215                 echo"<form action='' method='POST'>
 216                 <input name='name'><br/>
 217                 <input name='pass' type='password'><br/>
 218                 <input type='submit' id='authorization-input' value='Authorize'></form>";
 219         }
 220
 221         function checkCommentValid($tweet_comment){
 222                 $COMMENT_MAX = 500;
 223
 224                 if(mb_strlen($tweet_comment) > $COMMENT_MAX){
 225                         echo "Comment too long[Server]<br/>";
 226                         $this->comment_error = true;
 227                         return "";
 228                 }
 229                 $this->comment_error = false;
 230                 return $tweet_comment;
 231         }
 232
 233         function uploadAndVerify($files){
 234                 $FILE_MAX = 5242880;
 235                 $file_arr = array();
 236                 $file_string = "";
 237                 $first = true;
 238                 for($file = 1; $file <= 4; $file++){
 239                         $upload_location = "images/" . basename($files["file" . (string)$file]["name"]);
 240
 241                         if(!file_exists($upload_location) && $files["file" . (string)$file]["error"] == 0 && $upload_location !== "images/" && $files["file" . (string)$file]["size"] < $FILE_MAX){
 242                                 $file_arr[$file - 1] = $upload_location;
 243                                 if($first){
 244                                         $file_string .= rawurlencode($upload_location);
 245                                         $first = false;
 246                                 }
 247                                 else{
 248                                         $file_string .=  "," . rawurlencode($upload_location);
 249                                 }
 250                                 if (move_uploaded_file($files["file" . (string)$file]["tmp_name"], $upload_location )) {
 251                                         echo "File: $file  was valid.<br/>";
 252                                 }
 253                                 else {
 254                                         echo "File: $file_location " . " Detected an error <br/>";
 255                                         $file_arr[$file - 1] = "0";
 256                                         $die_state[$file - 1] = true;
 257                                         continue;
 258                                 }
 259                                 $die_state[$file - 1] = false;
 260                         }
 261                         else{
 262                                 $file_arr[$file - 1] = 0;
 263                                 if($files["file" . (string)$file]["size"] >= $FILE_MAX){
 264                                         echo "file" . (string)$file ." Over filesize limit-Server<br/>";
 265                                         $this->die_state[$file - 1] = true;
 266                                 }
 267                                 else if($files["file" . (string)$file]["error"] == 1){
 268                                         echo "file $file, PHP err " . $files["file" . (string)$file]["error"] . " <br/>";
 269                                         $this->die_state[$file - 1] = true;
 270                                 }
 271                                 else if($files["file" . (string)$file]["error"] == 2){
 272                                         echo "file $file, Over size limit-Client<br/>";
 273                                         $this->die_state[$file - 1] = true;
 274                                 }
 275                                 else if($files["file" . (string)$file]["error"] == 3){
 276                                         echo "file $file, The uploaded file was only partially uploaded. <br/>";
 277                                         $this->die_state[$file - 1] = true;
 278                                 }
 279                                 else if($files["file" . (string)$file]["error"] == 4){
 280                                         echo "file $file, Empty<br/>";
 281                                         $this->die_state[$file - 1] = false;
 282                                 }
 283                                 else if(file_exists($upload_location)) {
 284                                         echo "file " . (string)$file .", Duplicate<br/>";
 285                                         $this->die_state[$file - 1] = true;
 286                                 }
 287                                 else{
 288                                         echo "file $file, Unkown Upload Error " . $files["file" . (string)$file]["error"] . "<br/>";
 289                                         $this->die_state[$file - 1] = true;
 290                                 }
 291                         }
 292                 }
 293                 return $file_string;
 294         }
 295
 296         function displayTabularDatabase($table_name, $display_images = false){
 297                 echo "<br/>Displaying All entries(lower number means posted sooner): <br/>";
 298                 $statement = $this->connection->query("Select * from ". $table_name . " ORDER BY PostNo DESC;");
 299                 $statement->execute();
 300                 $result_arr = $statement->fetchAll();
 301
 302                 foreach($result_arr[0] as $key=>$head){
 303                         if(is_numeric ($key)) unset($result_arr[0][$key]);
 304                 }
 305
 306                 echo "<table border='1'><tr>";
 307                 foreach($result_arr[0] as $key=>$head_item)
 308                         echo "<th>$key</th>";
 309                 echo "</tr>";
 310
 311
 312
 313                 for($row = sizeof($result_arr) - 1; $row >= 0 ; $row--){
 314                         echo"<tr>";
 315                         $tupple = $result_arr[$row];
 316                         $column = 0;
 317                         foreach($tupple as $key=>$col){
 318                                 if(is_numeric ($key)) unset($result_arr[0][$key]);
 319                                 else {
 320                                         if($column == 2 && $display_images){
 321                                                 $this->createMediaNodeFromRaw($col);
 322                                         }
 323                                         else{
 324                                                 if($key == "PostNo") echo "<td>$col - $row</td>";
 325                                                 else echo "<td>$col</td>";
 326                                         }
 327                                         $column++;
 328                                 }
 329                         }
 330                         echo"</tr>";
 331                 }
 332                 echo "</table><hr/>";
 333         }
 334
 335         function createMediaNodeFromRaw($img_path_unprocessed){
 336                 $img_arr = explode(",", $img_path_unprocessed);
 337                 foreach($img_arr as $img){
 338                         $img = urldecode($img);
 339                         $img_ext = pathinfo($img, PATHINFO_EXTENSION);
 340                         if(strcmp($img_ext, "png") == 0 || strcmp($img_ext, "jpg")  == 0|| strcmp($img_ext, "gif") == 0)
 341                                 echo "<td>" . $this->createImageNode($img) . "</td>";
 342                         else
 343                                 echo "<td>" . $this->createVideoNode($img) . "</td>";
 344
 345                 }
 346         }
 347
 348         function createImageNode($img_path){
 349                 //return "<img src='$img_path' width='50%'/>";
 350                 return "<a href='$img_path'><img src='$img_path' class='media'/></a>";
 351         }
 352         function createVideoNode($vid_path){
 353                 //return "<video src='$vid_path' autoplay='true' loop='true' width='50%'/>"
 354                 return "<a href='$vid_path'><video src='$vid_path' autoplay='true' loop='true' class='media' /></a>";
 355         }
 356
 357         function retrieveOldestEntry(){
 358                 echo "<pre>";
 359                 $retrieval_query = $this->connection->prepare("SELECT * FROM TweetQueue ORDER BY PostNo ASC LIMIT 1");
 360
 361                 $most_recent = $retrieval_query->execute();
 362
 363                 $data_arr = $retrieval_query->fetchAll();
 364
 365                 print_r($data_arr);
 366
 367                 $file_arr  = explode(",", ($data_arr[0]["ImageLocation"] ));
 368                 return $data_arr;
 369         }
 370
 371         function deleteOldestEntry($oldest){
 372                 print_r($oldest);
 373
 374                 $this->deleteFromUnprocessedImageString($oldest[0]["ImageLocation"]);
 375
 376                 $delete_querry = $this->connection->prepare("DELETE FROM TweetQueue WHERE PostNo=:PostNo;");
 377                 $delete_querry->bindParam(":PostNo", $oldest[0]["PostNo"]);
 378                 $this->delete_status = $delete_querry->execute();
 379
 380                 if($this->delete_status !== 1){
 381                         echo "<pre><hr/>Delete Err" . $delete_query->error;
 382                 }
 383         }
 384 }
 385
 386 ?>