class/board-level-database-connection.php

   1 <?php
   2
   3         include_once("class/database-connection.php");
   4         include_once("../class/database-connection.php");
   5
   6 class BoardLevelDatabaseConnection extends DatabaseConnection{
   7         function __construct($path_prefix = ""){
   8                 echo $path_prefix;
   9                 parent::__construct($path_prefix);
  10         }
  11
  12         function buildThread($build_type, $display_type, $thread_id){
  13                 //echo "<hr/>";
  14                 $replies = $this->getReplies($thread_id);
  15                 //var_dump ($replies);
  16                 $list_add = "";
  17                 $reply_counter = 0;
  18                 $row_size = 4;
  19                 if($display_type == "list") $list_add = "-list";
  20
  21                 if($build_type == "native"){
  22                         foreach($replies as $reply){
  23                                 //$this->testBlock( $reply);
  24                                 //echo"<hr/>";
  25                                 $post_id = $reply[0];
  26                                 if($reply_counter % $row_size == 0) echo"<ul class='row-reply" . $list_add ."'>";
  27                                 echo "<li class='reply-container" . $list_add ."' PostNo='" . $post_id ."'>";
  28                                 //details
  29                                         echo "<div class='details" . $list_add ."'><ul class='embed-details'>
  30                                         <li>PostNo: " . $post_id .
  31                                         "
  32                                         </li>
  33                                         <li  class='interaction-item" . $list_add ."'>
  34                                         <a href='/?thread=" . $post_id . "'>
  35                                                 Open
  36                                         </a>
  37                                         </li>
  38                                         <li  class='interaction-item" . $list_add ."'>
  39                                         <a href='https://twitter.com/Qazoku/status/". $post_id ."'>
  40                                                 Twitter
  41                                         </a>
  42                                         </li>
  43                                         </ul>
  44                                         </div>";
  45                                         //contents;
  46                                         echo "
  47                                         <div class='thread-contents" . $list_add ."'>
  48                                         <div class='thread-text" . $list_add ."'><blockquote>" . $reply["PostText"] ."</blockquote></div>
  49                                         <div class='thread-image" . $list_add ."'>";
  50                                         if($reply["ImageURL"] !== null)
  51                                                 $this->createMediaNodeFromRaw($reply["ImageURL"]);
  52                                         else echo "<img/>";
  53                                         echo "</div></div>";
  54                                 echo "</li>";
  55                                 if($reply_counter % $row_size == $row_size - 1) echo "</ul>";
  56                                 $reply_counter++;
  57                         }
  58                 }
  59                 else{
  60                         ob_start();
  61                         require_once("class/twitter-connection.php");
  62                         ob_clean();
  63
  64                         $twitter_connection = new TwitterConnection($this->path_prefix);
  65
  66                         foreach($replies as $reply){
  67                                 $post_id = $reply[0];
  68                                 if($reply_counter % $row_size == 0) echo"<ul class='row-reply" . $list_add ."'>";
  69
  70                                 echo "<li class='embeded reply-container" . ($display_type == "list" ? "-list max":"") . "' PostNo='" . $post_id ."'>";
  71                                 echo "<div class=''><ul class='embed-details'>
  72                                         <li class=''>PostNo: " . $post_id .
  73                                         "
  74                                         </li>
  75                                         <li class='embed-detail-item'>
  76                                         <a href='/?thread=" . $post_id . "'>
  77                                                 Open
  78                                         </a>
  79                                         </li>
  80                                         <li class='embed-detail-item'>
  81                                         <a href='https://twitter.com/Qazoku/status/". $post_id ."'>
  82                                                 Twitter
  83                                         </a>
  84                                         </li>
  85                                         </ul>
  86                                         </div>";
  87
  88                                 echo $twitter_connection->getEmbededTweet($post_id)["html"];
  89
  90                                 echo "</li>";
  91                                                                 if($reply_counter % $row_size == $row_size - 1) echo"</ul>";
  92                                 $reply_counter++;
  93                         }
  94                 }
  95         }
  96
  97         //Get the count of all items that are not Unsubmitted and not replies
  98         function getThreads(){
  99                 $statement = $this->connection->prepare("SELECT * FROM `Tweet`
 100                                                                                 LEFT OUTER JOIN `Response` ON `Response`.`PostID` = `Tweet`.`PostID`
 101                                                                                 LEFT OUTER JOIN `Unsubmitted` ON `Unsubmitted`.`PostID` = `Tweet`.`PostID`
 102                                                                                 WHERE `Unsubmitted`.`PostID` IS NULL AND `Response`.`PostID` IS NULL
 103                                                                                 ORDER BY Tweet.PostID DESC");
 104                 try{
 105                         $statement->execute();
 106                         $threads = $statement->fetchAll();
 107                 }catch(Exception  $e){
 108                    echo "<strong>" . $e->getMessage() . "</strong><br/>";
 109                 }
 110                 return $threads;
 111         }
 112
 113         function getAllUnposted(){
 114                 $statement = $this->connection->prepare("SELECT * FROM Tweet JOIN Unsubmitted ON `Tweet`.`PostID` = `Unsubmitted`.`PostID` WHERE `Unsubmitted`.`Unverified` = 0");
 115                 try{
 116                         $statement->execute();
 117                         return  $statement->fetchAll();
 118                 }
 119                 catch(Exception $e){
 120                         echo "<strong>" . $e->getMessage() . "</strong><br/>";
 121                 }
 122         }
 123
 124         function getReplies($post_id){
 125                 $thread_replies= [];
 126                 $this->getRepliesRecursive($post_id, $thread_replies);
 127                 $thread_replies = array_reverse($thread_replies);
 128                 $head = $this->getPostDetails("Tweet", "PostID", $post_id);
 129                 array_unshift($thread_replies, $head[0]);
 130                 return $thread_replies;
 131         }
 132
 133         function getRepliesRecursive($post_id, &$thread_replies_store){
 134                 //echo "grr_";
 135                 $statement = $this->connection->prepare('
 136                                                                 SELECT * FROM `Tweet`
 137                                                                 LEFT JOIN `Response` ON `Response`.`PostID` = `Tweet`.`PostID`
 138                                                                 WHERE `Response`.`RepliesTo` = :postID
 139                                                                 ORDER BY Tweet.PostID DESC');
 140                 $statement->bindParam(":postID", $post_id);
 141
 142                 try{
 143                         $statement->execute();
 144                         $threads = $statement->fetchAll();
 145                 }catch(Exception  $e){
 146                    echo "<strong>" . $e->getMessage() . "</strong><br/>";
 147                    return;
 148                 }
 149                 //$this->testBlock($threads);
 150                 foreach($threads as $thread){
 151                         $this->getRepliesRecursive($thread[0], $thread_replies_store);//0=postid
 152                         array_push($thread_replies_store, $thread);
 153                 }
 154         }
 155
 156         function buildDetailList($array){
 157                 $built_arr = array();
 158                 foreach($array as $name=>$post){
 159                         if(preg_match("/chk\d*/", $name)){
 160                                 $post_id = substr($name, 3);
 161                                 $built_arr[$post_id] = array("unv"=>$array["unv$post_id"],
 162                                                                                         "ipd"=>$array["ipd$post_id"],
 163                                                                                         "ban"=>$array["ban$post_id"]
 164                                                                                         );
 165                         }
 166                 }
 167                 return $built_arr;
 168         }
 169
 170         function sendVerifiedPostID($id){
 171                 echo " $id ";
 172                 $data_arr = $this->getPostDetails("Tweet", "PostID", $id)[0];
 173                 var_dump($data_arr);
 174                 require_once("class/twitter-connection.php");
 175                 $twitter_connection = new TwitterConnection();
 176                 $twitter_connection->makeTweet($data_arr["PostText"], explode(",", $data_arr["ImageURL"]));
 177         }
 178
 179         function toggleBanIPList($post_arr){
 180                 $post_no_list = $this->buildDetailList($post_arr);
 181                 foreach($post_no_list as $key=>$item){
 182                         $propper_entry = $this->getPostDetails("Banned", "IPAddress", $item["ipd"]);
 183                         if(sizeof($propper_entry) > 0){
 184                                 $this->deleteFromTable("Banned", "IPAddress", $item["ipd"]);
 185                         }
 186                         else{
 187                                 $this->addToTable("Banned", array("IPAddress"=>$item["ipd"], "BanComment" => $item["ban"]));
 188                         }
 189                 }
 190         }
 191         function verifyPosts($post_arr){
 192                 $post_no_list = $this->buildDetailList($post_arr);
 193                 foreach($post_no_list as $key=>$item){
 194                         $propper_entry = $this->getPostDetails("Unsubmitted", "PostID", $key);
 195                         if(sizeof($propper_entry) > 0){
 196                                 $this->updatePost("Unsubmitted", "PostID", $key, array("Unverified" => $item["unv"]));
 197                         }
 198                         else throw "Post doesn't exist??";
 199                 }
 200         }
 201
 202
 203
 204         function deleteExpiredEntries(){
 205                 $threads = $this->getThreads();
 206                 $thread_count = 0;
 207                 echo "A " . $this->path_prefix;
 208                 $user_properties = new TwitterConnection($this->path_prefix);
 209                 $delete_threshold = ($user_properties->getPostProperties()["Catalog-Size"]);
 210                 //echo(var_dump($user_properties->getPostProperties()));
 211                 foreach($threads as $thread){
 212                         $thread_count++;
 213                         echo $thread_count . " > $delete_threshold<br/>";
 214                         if($thread_count > $delete_threshold){
 215                                 echo "DELETE-- ";
 216                                 var_dump ($thread);
 217                                 $this->deleteFromUnprocessedImageString($thread["ImageURL"]);
 218                                 $this->deleteChain($thread[0]);//0 is the most relevant PostID
 219                         }
 220                 }
 221                 $database_connection = null;
 222         }
 223
 224         function deleteChain($post_id){
 225                 $delete_arr = array_reverse($this->getReplies($post_id));
 226                 foreach($delete_arr as $delete_item){
 227                         $this->deleteAllOfPost($delete_item["PostID"]);
 228                 }
 229         }
 230
 231         function deleteAllOfPost($post_id){
 232                 $this->deleteFromTable("Response", "PostID", $post_id);//0=postid
 233                 $this->deleteFromTable("Unsubmitted", "PostID", $post_id);//0=postid
 234                 $this->deleteFromTable("Tweet", "PostID", $post_id);//0=postid
 235         }
 236
 237         function deleteFromUnprocessedImageString($image_path_uprocessed){
 238                 if($image_path_uprocessed === null) return;
 239                 $image_path_uprocessed_arr = explode(",", $image_path_uprocessed);
 240                 foreach($image_path_uprocessed_arr as $unprocessed_path){
 241                         $path = rawurldecode($unprocessed_path);
 242                         unlink ($path);
 243                 }
 244         }
 245
 246
 247                 function retrieveOldestEntry(){
 248                 echo "<pre>";
 249                 $retrieval_query = $this->connection->prepare("SELECT * FROM TweetQueue ORDER BY PostNo ASC LIMIT 1");
 250
 251                 $most_recent = $retrieval_query->execute();
 252
 253                 $data_arr = $retrieval_query->fetchAll();
 254
 255                 print_r($data_arr);
 256
 257                 $file_arr  = explode(",", ($data_arr[0]["ImageLocation"] ));
 258                 return $data_arr;
 259         }
 260
 261         function retrieveNewestEntry(){
 262                 echo "<pre>";
 263                 $retrieval_query = $this->connection->prepare("SELECT * FROM TweetQueue ORDER BY PostNo DESC LIMIT 1");
 264
 265                 $most_recent = $retrieval_query->execute();
 266
 267                 $data_arr = $retrieval_query->fetchAll();
 268
 269                 print_r($data_arr);
 270
 271                 $file_arr  = explode(",", ($data_arr[0]["ImageLocation"] ));
 272                 return $data_arr;
 273         }
 274
 275         function deleteOldestEntry($oldest){
 276                 print_r($oldest);
 277
 278                 $this->deleteFromUnprocessedImageString($oldest[0]["ImageLocation"]);
 279
 280                 $delete_querry = $this->connection->prepare("DELETE FROM TweetQueue WHERE PostNo=:PostNo;");
 281                 $delete_querry->bindParam(":PostNo", $oldest[0]["PostNo"]);
 282                 $this->delete_status = $delete_querry->execute();
 283
 284                 if($this->delete_status !== 1){
 285                         echo "<pre><hr/>Delete Err" . $delete_query->error;
 286                 }
 287         }
 288
 289         function addTimelineTweetsToDatabase($combined_database_arr){
 290                 foreach($combined_database_arr as $key => $timeline_item){
 291                         $timeline_item[1] = str_replace("<","&lt;",$timeline_item[1]);
 292                         $timeline_item[1] = str_replace(">","&gt;",$timeline_item[1]);
 293                         $timeline_item[1] = str_replace("\"","&quot;",$timeline_item[1]);
 294
 295                         $this->addToTable("Tweet",  array("PostID"=>$timeline_item[0],
 296                                                 "PostText"=> $timeline_item[1], "ImageURL"=> $timeline_item[2]));
 297                 }
 298                 foreach($combined_database_arr as $key => $timeline_item){
 299                         if($timeline_item[3] !== null)
 300                                 $this->addToTable("Response",  array("PostID"=>$timeline_item[0], "RepliesTo"=>$timeline_item[3]));
 301                 }
 302         }
 303
 304         function getAllSubmissionDetails($table_name, $join_with, $ordering_param,$ordering){
 305                 $statement = $this->connection->prepare("SELECT * FROM $table_name JOIN $join_with ON `$table_name`.`PostID` = `$join_with`.`PostID` ORDER BY `$table_name` . `$ordering_param` $ordering");
 306                 //$statement->bindParam(":ordering_param", $ordering_param);
 307                 $statement->execute();
 308                 return $statement->fetchAll();
 309         }
 310
 311         function getVerificationDetails($ordering){
 312                 $statement = $this->connection->prepare("
 313                         SELECT DISTINCT * FROM `Tweet`
 314                                 LEFT JOIN `Response` ON `Tweet`.`PostID` = `Response`.`PostID`
 315                                 LEFT JOIN `Unsubmitted` ON `Tweet`.`PostID` = `Unsubmitted`.`PostID`
 316                                 LEFT JOIN `Banned` ON `Banned`.`IPAddress` = `Unsubmitted`.`IPAddress`
 317                                 ORDER BY `Tweet`.`PostID` $ordering
 318                 ");
 319
 320                 $statement->execute();
 321                 return $statement->fetchAll();
 322         }
 323
 324 }
 325
 326 ?>