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         //Get the count of all items that are not Unsubmitted and not replies
  13         function getThreads(){
  14                 $statement = $this->connection->prepare("SELECT * FROM `Tweet`
  15                                                                                 LEFT OUTER JOIN `Response` ON `Response`.`PostID` = `Tweet`.`PostID`
  16                                                                                 LEFT OUTER JOIN `Unsubmitted` ON `Unsubmitted`.`PostID` = `Tweet`.`PostID`
  17                                                                                 WHERE `Unsubmitted`.`PostID` IS NULL AND `Response`.`PostID` IS NULL
  18                                                                                 ORDER BY Tweet.PostID DESC");
  19                 try{
  20                         $statement->execute();
  21                         $threads = $statement->fetchAll();
  22                 }catch(Exception  $e){
  23                    echo "<strong>" . $e->getMessage() . "</strong><br/>";
  24                 }
  25                 return $threads;
  26         }
  27
  28         function getAllUnposted(){
  29                 $statement = $this->connection->prepare("SELECT * FROM Tweet JOIN Unsubmitted ON `Tweet`.`PostID` = `Unsubmitted`.`PostID` WHERE `Unsubmitted`.`Unverified` = 0");
  30                 try{
  31                         $statement->execute();
  32                         return  $statement->fetchAll();
  33                 }
  34                 catch(Exception $e){
  35                         echo "<strong>" . $e->getMessage() . "</strong><br/>";
  36                 }
  37         }
  38
  39         function getReplies($post_id){
  40                 $thread_replies= [];
  41                 $this->getRepliesRecursive($post_id, $thread_replies);
  42                 $thread_replies = array_reverse($thread_replies);
  43                 $head = $this->getPostDetails("Tweet", "PostID", $post_id);
  44                 array_unshift($thread_replies, $head[0]);
  45                 return $thread_replies;
  46         }
  47
  48         function getRepliesRecursive($post_id, &$thread_replies_store){
  49                 //echo "grr_";
  50                 $statement = $this->connection->prepare('
  51                                                                 SELECT * FROM `Tweet`
  52                                                                 LEFT JOIN `Response` ON `Response`.`PostID` = `Tweet`.`PostID`
  53                                                                 WHERE `Response`.`RepliesTo` = :postID
  54                                                                 ORDER BY Tweet.PostID DESC');
  55                 $statement->bindParam(":postID", $post_id);
  56
  57                 try{
  58                         $statement->execute();
  59                         $threads = $statement->fetchAll();
  60                 }catch(Exception  $e){
  61                    echo "<strong>" . $e->getMessage() . "</strong><br/>";
  62                    return;
  63                 }
  64                 //$this->testBlock($threads);
  65                 foreach($threads as $thread){
  66                         $this->getRepliesRecursive($thread[0], $thread_replies_store);//0=postid
  67                         array_push($thread_replies_store, $thread);
  68                 }
  69         }
  70
  71         function buildDetailList($array){
  72                 $built_arr = array();
  73                 foreach($array as $name=>$post){
  74                         if(preg_match("/chk\d*/", $name)){
  75                                 $post_id = substr($name, 3);
  76                                 $built_arr[$post_id] = array("unv"=>$array["unv$post_id"],
  77                                                                                         "ipd"=>$array["ipd$post_id"],
  78                                                                                         "ban"=>$array["ban$post_id"]
  79                                                                                         );
  80                         }
  81                 }
  82                 return $built_arr;
  83         }
  84
  85         function sendVerifiedPostID($id){
  86                 echo " $id ";
  87                 $data_arr = $this->getPostDetails("Tweet", "PostID", $id)[0];
  88                 var_dump($data_arr);
  89                 require_once("class/twitter-connection.php");
  90                 $twitter_connection = new TwitterConnection();
  91                 $twitter_connection->makeTweet($data_arr["PostText"], explode(",", $data_arr["ImageURL"]));
  92         }
  93
  94         function toggleBanIPList($post_arr){
  95                 $post_no_list = $this->buildDetailList($post_arr);
  96                 foreach($post_no_list as $key=>$item){
  97                         $propper_entry = $this->getPostDetails("Banned", "IPAddress", $item["ipd"]);
  98                         if(sizeof($propper_entry) > 0){
  99                                 $this->deleteFromTable("Banned", "IPAddress", $item["ipd"]);
 100                         }
 101                         else{
 102                                 $this->addToTable("Banned", array("IPAddress"=>$item["ipd"], "BanComment" => $item["ban"]));
 103                         }
 104                 }
 105         }
 106         function verifyPosts($post_arr){
 107                 $post_no_list = $this->buildDetailList($post_arr);
 108                 foreach($post_no_list as $key=>$item){
 109                         $propper_entry = $this->getPostDetails("Unsubmitted", "PostID", $key);
 110                         if(sizeof($propper_entry) > 0){
 111                                 $this->updatePost("Unsubmitted", "PostID", $key, array("Unverified" => $item["unv"]));
 112                         }
 113                         else throw "Post doesn't exist??";
 114                 }
 115         }
 116
 117
 118
 119         function deleteExpiredEntries(){
 120                 $threads = $this->getThreads();
 121                 $thread_count = 0;
 122                 echo "A " . $this->path_prefix;
 123                 $user_properties = new TwitterConnection($this->path_prefix);
 124                 $delete_threshold = ($user_properties->getPostProperties()["Catalog-Size"]);
 125                 //echo(var_dump($user_properties->getPostProperties()));
 126                 foreach($threads as $thread){
 127                         $thread_count++;
 128                         echo $thread_count . " > $delete_threshold<br/>";
 129                         if($thread_count > $delete_threshold){
 130                                 echo "DELETE-- ";
 131                                 var_dump ($thread);
 132                                 $this->deleteFromUnprocessedImageString($thread["ImageURL"]);
 133                                 $this->deleteChain($thread[0]);//0 is the most relevant PostID
 134                         }
 135                 }
 136                 $database_connection = null;
 137         }
 138
 139         function deleteChain($post_id){
 140                 $delete_arr = array_reverse($this->getReplies($post_id));
 141                 foreach($delete_arr as $delete_item){
 142                         $this->deleteAllOfPost($delete_item["PostID"]);
 143                 }
 144         }
 145
 146         function deleteAllOfPost($post_id){
 147                 $this->deleteFromTable("Response", "PostID", $post_id);//0=postid
 148                 $this->deleteFromTable("Unsubmitted", "PostID", $post_id);//0=postid
 149                 $this->deleteFromTable("Tweet", "PostID", $post_id);//0=postid
 150         }
 151
 152         function deleteFromUnprocessedImageString($image_path_uprocessed){
 153                 if($image_path_uprocessed === null) return;
 154                 $image_path_uprocessed_arr = explode(",", $image_path_uprocessed);
 155                 foreach($image_path_uprocessed_arr as $unprocessed_path){
 156                         $path = rawurldecode($unprocessed_path);
 157                         unlink ($path);
 158                 }
 159         }
 160
 161
 162                 function retrieveOldestEntry(){
 163                 echo "<pre>";
 164                 $retrieval_query = $this->connection->prepare("SELECT * FROM TweetQueue ORDER BY PostNo ASC LIMIT 1");
 165
 166                 $most_recent = $retrieval_query->execute();
 167
 168                 $data_arr = $retrieval_query->fetchAll();
 169
 170                 print_r($data_arr);
 171
 172                 $file_arr  = explode(",", ($data_arr[0]["ImageLocation"] ));
 173                 return $data_arr;
 174         }
 175
 176         function retrieveNewestEntry(){
 177                 echo "<pre>";
 178                 $retrieval_query = $this->connection->prepare("SELECT * FROM TweetQueue ORDER BY PostNo DESC LIMIT 1");
 179
 180                 $most_recent = $retrieval_query->execute();
 181
 182                 $data_arr = $retrieval_query->fetchAll();
 183
 184                 print_r($data_arr);
 185
 186                 $file_arr  = explode(",", ($data_arr[0]["ImageLocation"] ));
 187                 return $data_arr;
 188         }
 189
 190         function deleteOldestEntry($oldest){
 191                 print_r($oldest);
 192
 193                 $this->deleteFromUnprocessedImageString($oldest[0]["ImageLocation"]);
 194
 195                 $delete_querry = $this->connection->prepare("DELETE FROM TweetQueue WHERE PostNo=:PostNo;");
 196                 $delete_querry->bindParam(":PostNo", $oldest[0]["PostNo"]);
 197                 $this->delete_status = $delete_querry->execute();
 198
 199                 if($this->delete_status !== 1){
 200                         echo "<pre><hr/>Delete Err" . $delete_query->error;
 201                 }
 202         }
 203
 204         function addTimelineTweetsToDatabase($combined_database_arr){
 205                 foreach($combined_database_arr as $key => $timeline_item){
 206                         $timeline_item[1] = str_replace("<","&lt;",$timeline_item[1]);
 207                         $timeline_item[1] = str_replace(">","&gt;",$timeline_item[1]);
 208                         $timeline_item[1] = str_replace("\"","&quot;",$timeline_item[1]);
 209
 210                         $this->addToTable("Tweet",  array("PostID"=>$timeline_item[0],
 211                                                 "PostText"=> $timeline_item[1], "ImageURL"=> $timeline_item[2]));
 212                 }
 213                 foreach($combined_database_arr as $key => $timeline_item){
 214                         if($timeline_item[3] !== null)
 215                                 $this->addToTable("Response",  array("PostID"=>$timeline_item[0], "RepliesTo"=>$timeline_item[3]));
 216                 }
 217         }
 218
 219         function getAllSubmissionDetails($table_name, $join_with, $ordering_param,$ordering){
 220                 $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");
 221                 //$statement->bindParam(":ordering_param", $ordering_param);
 222                 $statement->execute();
 223                 return $statement->fetchAll();
 224         }
 225
 226         function getVerificationDetails($ordering){
 227                 $statement = $this->connection->prepare("
 228                         SELECT DISTINCT * FROM `Tweet`
 229                                 LEFT JOIN `Response` ON `Tweet`.`PostID` = `Response`.`PostID`
 230                                 LEFT JOIN `Unsubmitted` ON `Tweet`.`PostID` = `Unsubmitted`.`PostID`
 231                                 LEFT JOIN `Banned` ON `Banned`.`IPAddress` = `Unsubmitted`.`IPAddress`
 232                                 ORDER BY `Tweet`.`PostID` $ordering
 233                 ");
 234
 235                 $statement->execute();
 236                 return $statement->fetchAll();
 237         }
 238
 239 }
 240
 241 ?>