. * * Comments & Questions @ joris.veenhuis@aegee.org */ /* before adding the .sql file from this database: * Make a Backup * UPDATE CIA SET agora_id_end = (agora_id_end - 1) WHERE agora_id_end = 10 maybe also create some indexes on the table? at least on agora_id_end and all the order columns */ require("./include/include.php"); require_once(FILESYSTEMROOTPATH."jc/include/classes/DefaultLayout.php"); require_once(FILESYSTEMROOTPATH."include/form/AbstractForm.php");//gebruik je deze hier wel? $layout = new DefaultLayout("JC_MANAGE", "./jc/"); $layout->init(); $JCParser = new JCParser(); if($JCParser->safetycheck1()) die("This tool can only be used after creating a new agora and before proposals are created for that Agora, please contact George if you accidently did so, but the Agora has not taken place yet and no real proposals have been created yet."); if($JCParser->safetycheck2() != $_SESSION['JC_MODULE']['AgoraId']) die("You cannot change the CIA for past Agorae anymore."); $_SESSION['JC_MODULE']['PreviousAgoraId'] = $_SESSION['JC_MODULE']['AgoraId'] - 1; if($_GET['action'] == 'delete_document' and $_GET['document_id'] and ctype_digit($_GET['document_id'])) { $JCParser->deleteDocument($_GET['document_id']); } if($_POST['unparsed_text']) { $title = $JCParser->parse($_POST['unparsed_text']); $JCParser->putTitletoDB($_POST['document'], $_POST['order'], $_POST['last_changed'], $_POST['title_title'], $title); } class JCParser { private $mysql; public function __construct() { $this->mysql = $GLOBALS['ClassFactory']->getMySQL(); }/*Unfortunately there are hardly any history_id's in the database, so would be a waste of time to wrote something to find them :( private function getpreviousSentenceID($dtitle, $ano, $pno, $sno, $text) { //chkprevSenID($dtitle, $ano, $pno, $sno, $text); if(chkprevSenID(0, 0, 0, 0, $text)) if(count($matches)==1) return $matches[0]; } private function chkprevSenID($sentence['dtitle'], $sentence['ano'], $sentence['pno'], $sentence['sno'], $sentence['text']) { $where = "agora_id_end='".$this->mysql->escape($_SESSION['JC_MODULE']['PreviousAgoraId'])."'"; if($sentence['dtitle']) $where[] = "`title` = '".$this->mysql->escape(trim($sentence['dtitle']))."'"; if($sentence['ano']) $where[] = "`article` = '".$this->mysql->escape(trim($sentence['ano']))."'"; if($sentence['pno']) $where[] = "`paragraph` = '".$this->mysql->escape(trim($sentence['pno']))."'"; if($sentence['sno']) $where[] = "`sentence` = '".$this->mysql->escape(trim($sentence['sno']))."'"; $where[] = "`sentence_text` = '".$this->mysql->escape(trim($sentence['text']))."'"; $query = "SELECT id FROM CIA WHERE ".implode(" AND ",$where); if( $this->mysql->query($query) ) { $ret = array(); while( $row = $this->mysql->fetchAssoc() ) { $ret[] = $row['id']; } return $ret; } else $layout->echoln("MySQL error
"); }*/ function deleteDocument($order) { global $layout; $query = "UPDATE CIA SET agora_id_end = agora_id_end - 1 WHERE agora_id_start < '".$this->mysql->escape($_SESSION['JC_MODULE']['PreviousAgoraId'])."' AND agora_id_end = '".$this->mysql->escape($_SESSION['JC_MODULE']['PreviousAgoraId'])."' AND `order` = '".$this->mysql->escape($order)."'"; if(!$this->mysql->query($query)) $layout->echoln("MySQL error
"); $query = "DELETE FROM CIA WHERE agora_id_start = '".$this->mysql->escape($_SESSION['JC_MODULE']['PreviousAgoraId'])."' AND agora_id_end = '".$this->mysql->escape($_SESSION['JC_MODULE']['PreviousAgoraId'])."' AND `order` = '".$this->mysql->escape($order)."'"; if(!$this->mysql->query($query)) $layout->echoln("MySQL error
"); } function safetycheck1() {//Does the current Agora have any associated proposals already? then you cannot use this script anymore $query = "SELECT count(id) as number FROM proposals WHERE agora_id = '".$this->mysql->escape($_SESSION['JC_MODULE']['AgoraId'])."'"; $this->mysql->query($query); $result = $this->mysql->fetchAssoc(); return $result['number']; } function safetycheck2() {//Is the current Agora the latest Agora? well, you can compare that in the normal flow, but this gives you the latest agora number $query = "SELECT id FROM agorae ORDER BY id DESC LIMIT 0,1"; $this->mysql->query($query); $result = $this->mysql->fetchAssoc(); return $result['id']; } function getDocuments() { global $layout; $query = "SELECT DISTINCT (`order`) AS id, title FROM CIA WHERE agora_id_end='".$this->mysql->escape($_SESSION['JC_MODULE']['PreviousAgoraId'])."' ORDER BY `order` ASC"; if( $this->mysql->query($query) ) { $ret = array(); while( $row = $this->mysql->fetchAssoc() ) { $ret[ $row['id'] ] = $row['title']; } return $ret; } else $layout->echoln("MySQL error
"); } function getCIA() { global $layout; $CIA = array(); $query = "SELECT * FROM CIA WHERE agora_id_end='".$this->mysql->escape($_SESSION['JC_MODULE']['PreviousAgoraId'])."' ORDER BY `order` ASC, order2 ASC, article ASC, paragraph ASC, sentence ASC"; if( $this->mysql->query($query) ) { while( $row = $this->mysql->fetchAssoc() ) { $CIA[$row['order']]['t'][$row['order2']]['a'][$row['article']]['p'][$row['paragraph']][$row['sentence']] = $row['sentence_text']; $CIA[$row['order']]['title'] = $row['title']; $CIA[$row['order']]['last_changed'] = $row['last_change']; $CIA[$row['order']]['t'][$row['order2']]['title'] = $row['title2']; $CIA[$row['order']]['t'][$row['order2']]['a'][$row['article']]['title'] = $row['article_title']; } return $CIA; } else $layout->echoln("MySQL error
"); } function getNextTitleNo($order) { global $layout; $query = "SELECT order2 FROM CIA WHERE agora_id_end = '".$this->mysql->escape($_SESSION['JC_MODULE']['PreviousAgoraId'])."' AND `order` = '".$this->mysql->escape($order)."' ORDER BY order2 DESC LIMIT 0,1"; if(!$this->mysql->query($query)) $layout->echoln("MySQL error
"); $result = $this->mysql->fetchAssoc(); return $result['order2'] + 1; } function putTitletoDB($document, $order, $last_changed, $title_title, $title) { global $layout; $query = "SELECT `order` FROM CIA WHERE agora_id_end = '".$this->mysql->escape($_SESSION['JC_MODULE']['PreviousAgoraId'])."' AND `title` = '".$this->mysql->escape(trim($document))."' ORDER BY `order` DESC LIMIT 0,1"; if(!$this->mysql->query($query)) $layout->echoln("MySQL error
"); $result = $this->mysql->fetchAssoc(); $existingorder = $result['order']; if($existingorder) $order = $existingorder; else { if(!ctype_digit($order) OR !$order) $order = 1; else $order = $order + 1;//eigenlijk hoeft dit alleen als dat ordernummer al bezet is //in case document doesn't exist yet, free some space so document can be added at the right place in the order $query = "UPDATE CIA SET `order` = `order` + 1 WHERE agora_id_end = '".$this->mysql->escape($_SESSION['JC_MODULE']['PreviousAgoraId'])."' AND `order` >= '".$this->mysql->escape($order)."'"; if(!$this->mysql->query($query)) $layout->echoln("MySQL error
"); } if(trim($document) and trim($order) and trim($last_changed)) { $order2 = $this->getNextTitleNo($order); foreach ($title as $articleno => $article) { foreach ($article['p'] as $paragraphno => $paragraph) { foreach ($paragraph as $sentenceno => $sentence) { if($sentenceno > 1) $result .= " "; $result .= "{$sentenceno}".nl2br($sentence)."."; $query ="INSERT INTO `CIA` SET ";//$title[articlenumber]['p'][paragraphnumber][sentencenumber] $query .="`abs_order` = 0, "; $query .="`category` = 'CIA', "; $query .="`title` = '".$this->mysql->escape(trim($document))."', "; $query .="`order` = '".$this->mysql->escape(trim($order))."', "; $query .="`last_change` = '".$this->mysql->escape(trim($last_changed))."', "; $query .="`title2` = '".$this->mysql->escape(trim($title_title))."', "; $query .="`order2` = '".$this->mysql->escape(trim($order2))."', ";//dit is de volgende na de laatste invoer, dus die moet je eerst ophalen $query .="`article` = '".$this->mysql->escape(trim($articleno))."', "; $query .="`article_title` = '".$this->mysql->escape(trim($article['title']))."', "; $query .="`paragraph` = '".$this->mysql->escape(trim($paragraphno))."', "; $query .="`sentence` = '".$this->mysql->escape(trim($sentenceno))."', "; $query .="`sentence_text` = '".$this->mysql->escape($sentence)."', ";//deze f niet meer trimmen, want soms willen we er wel f line-ending bij. $query .="`agora_id_start` = '".$this->mysql->escape(trim($_SESSION['JC_MODULE']['PreviousAgoraId']))."', "; $query .="`agora_id_end` = '".$this->mysql->escape(trim($_SESSION['JC_MODULE']['PreviousAgoraId']))."', "; $query .="`history_id` = 0";//last one, don't include ,space //echo($query."
\n"); if(!$this->mysql->query($query)) $layout->echoln("Problem inserting all sentences in database
"); } } } } else $layout->echoln("Please at least enter the document title and the last time it was changed
"); } function parse($unparsed) { global $layout; //Previous more or less working preg_match attempts: //preg_match_all("/(?:^|\R)Article [0-9]{1,2}(?::.*?)?\R/", $unparsed, $CIA);//:.*?\R //preg_match_all("/(?:^|\R)Article [0-9]{1,2}[.\R]*?(?=Article|$)/", $unparsed, $CIA);//:.*?\R //preg_match_all("@(^Article.*?)(?:^Article|$)@msD", $unparsed, $CIA); $has_articles = preg_match_all("@(?<=^|\n|\r)Article ([0-9]{1,2}):?(.*?)\R(.*?)(?=$|\RArticle)@s", $unparsed, $articles, PREG_SET_ORDER | PREG_OFFSET_CAPTURE); //what if no articles? like in the logo file? if($has_articles) $text_before_articles = trim(substr($unparsed,0,$articles[0][0][1])); else $text_before_articles = $unparsed;//maar wat als geen paragrafen? Gewoon de hele tekst als paragraaf 0 if(!$has_articles OR $text_before_articles) {//If there are no paragraphs or there is text before the first paragraph $articles = array_merge(array(0=>array(1=>array(0=>0),3=>array(0=>$text_before_articles))), $articles); } //printf("
%s
",print_r($articles, true)); foreach ($articles as $article) { $title[$article[1][0]]['title'] = trim($article[2][0]); //alle tekst voor eerste paragraaf pakken, trimmen, als die tekst er is opslaan onder nummer 0, daarna doorgaan met de rest //preg_match("@$(.*?)(?=$|\(([a-z0-9]{1,2})\))@s",$article[3],$paragraphs, PREG_SET_ORDER); //kunnen paragrafen met letters wel? nee dus! hier de oude functie inclusief paragrafen met letters, wat op zich beter is maar niet kan zonder DB wijzigingen //$has_paragraphs = preg_match_all("@(?<=^|\n|\r)\h*\(([a-z0-9]{1,2})\)(.+?)(?=$|\R\h*\((?:[a-z0-9]{1,2})\))@s",$article[3],$paragraphs, PREG_SET_ORDER | PREG_OFFSET_CAPTURE); //white space between line start and paragraph symbol //en de nieuwe met alleen nummerparagrafen $has_paragraphs = preg_match_all("@(?<=^|\n|\r)\h*\(([0-9]{1,2})\)(.+?)(?=$|\R\h*\((?:[0-9]{1,2})\))@s",$article[3][0],$paragraphs, PREG_SET_ORDER | PREG_OFFSET_CAPTURE); //white space between line start and paragraph symbol //maar wat als tekst voor eerste paragraaf? get text before first paragraph start if($has_paragraphs) $text_before_paragraphs = trim(substr($article[3][0],0,$paragraphs[0][0][1])); else $text_before_paragraphs = $article[3][0];//maar wat als geen paragrafen? Gewoon de hele tekst als paragraaf 0 if(!$has_paragraphs OR $text_before_paragraphs) {//If there are no paragraphs or there is text before the first paragraph $paragraphs = array_merge(array(0=>array(1=>array(0=>0),2=>array(0=>$text_before_paragraphs))), $paragraphs); } foreach ($paragraphs as $paragraph) { //$sentences = preg_split("@\.(\s|$)@",$paragraph[2][0]);//zowel .spatie als .newline als einde artikel //$has_sentences = preg_match_all("@.*?(?:[.:;]\s|$)@s",$paragraph[2][0],$sentences, PREG_SET_ORDER | PREG_OFFSET_CAPTURE); //white space between line start and paragraph symbol $has_sentences = preg_match_all("@.*?\S{3,}?.*?(?:[.:;]\s|$)@s",$paragraph[2][0],$sentences, PREG_SET_ORDER); //white space between line start and paragraph symbol //splitsen op .:; lijkt een goed idee, maar hoe weet je dan welke het was? preg_match_all met "@.*?([.:;]\s|$)\s@" maar dan weer probleem bij zinnen die beginnen met nummer na dubbele punt... en lost het ook niet op //maar wat als maar één zin? geen probleem! je krijgt gewoon array met maar één element terug $i = 1; foreach ($sentences as $sentence) { $sentence = trim($sentence[0]);//remove whitespace //(^\d{0,2}(?!\.)(?!\d)) preg_match("@^(\s*•\s*)?(?:\s*(\(?[a-zA-Z0-9]{1,2}[.)])\s*)?(?:[0-9]{0,2}(?=[^.\d)]))?(.*)@s", $sentence, $results);//remove sentence number (if present) $results[3] = trim($results[3]); if($results[3]) { if($results[2]) $results[3] = $results[2]." ".$results[3];//als zin met nietnumerieke nepparagraaf begint dan knipt ie hem er uit, en voegen we hem later weer netjes toe if($results[1]) $results[3] = "• ".$results[3];//als zin met bullit begint, dan standaardbullet toevoegen //dis nergens voor nodig, in online versie is elke regel toch op nieuwe zin if(mb_substr($results[2],-1)==";" OR mb_substr($results[2],-1)==":") $results[2] = $results[2]."\n";//toch weer f line-ending er bij. if(!$title[$article[1][0]]['p'][$paragraph[1][0]][$i]) $title[$article[1][0]]['p'][$paragraph[1][0]][$i] = $results[3]; else {//Mochten er meerdere paragrafen of artikelen zijn met zelfde nummer, zorgt dit ervoor dat er toch geen zinnen verloren gaan, dan voegt ie er namelijk gewoon eentje bij. $title[$article[1][0]]['p'][$paragraph[1][0]][] = $results[3]; $layout->echoln("Warning: Two articles or paragraphs with the same number detected!
"); } $i++; } } } } return $title; } function reproduce($CIA) { //$CIA[$documentno]['t'][$titleno]['a'][$articleno] foreach ($CIA as $documentno => $document) { $result .= "

{$document['title']}

{$document['last_changed']}\n"; foreach ($document['t'] as $titleno => $title) { $result .= "

{$title['title']}

\n"; foreach ($title['a'] as $articleno => $article) { $result .= "

"; if($articleno || $article['title']) { $result .= ""; if($articleno) $result .= "Article {$articleno}"; if($articleno && $article['title']) $result .= ": "; if($article['title']) $result .= $article['title']; $result .= "
\n"; } foreach ($article['p'] as $paragraphno => $paragraph) { if($paragraphno) $result .= "({$paragraphno}) "; foreach ($paragraph as $sentenceno => $sentence) { if($sentenceno > 1) $result .= " "; if(!$_GET['hidesentencenumbers']) $result .= "{$sentenceno}"; $result .= nl2br($sentence)."
\n"; } $result .= "
\n"; } $result .= "

\n"; } } } return $result; } } //regex101.com //print_r($title); $documents = $JCParser->getDocuments(); $i = 0; foreach($documents as $id => $document) { $option = "