| [ Index ] |
|
Code source de eGroupWare 1.2.106-2 |
1 <?php 2 /**************************************************************************\ 3 * eGroupWare - KnowledgeBase * 4 * http://www.egroupware.org * 5 * Written by Alejandro Pedraza [alpeb AT users.sourceforge DOT net] * 6 * ------------------------------------------------------------------------ * 7 * Started off as a port of phpBrain - http://vrotvrot.com/phpBrain/ * 8 * but quickly became a full rewrite * 9 * ------------------------------------------------------------------------ * 10 * This program is free software; you can redistribute it and/or modify it * 11 * under the terms of the GNU General Public License as published by the * 12 * Free Software Foundation; either version 2 of the License, or (at your * 13 * option) any later version. * 14 \**************************************************************************/ 15 16 /* $Id: class.sokb.inc.php 20295 2006-02-15 12:31:25Z $ */ 17 18 /** 19 * Data manipulation layer of the Knowledge Base. Methods to be used only by methods in the bo class. 20 * 21 * Last Editor: $Author: $ 22 * @author Alejandro Pedraza 23 * @package phpbrain 24 * @version $Revision: 20295 $ 25 * @license GPL 26 **/ 27 class sokb 28 { 29 /** 30 * Database object 31 * 32 * @access private 33 * @var object db 34 */ 35 var $db; 36 37 /** 38 * Number of rows in result set 39 * 40 * @access public 41 * @var int 42 */ 43 var $num_rows; 44 45 /** 46 * Number of unanswered questions in result set 47 * 48 * @access public 49 * @var int 50 */ 51 var $num_questions; 52 53 /** 54 * Number of comments in result set 55 * 56 * @access public 57 * @var int 58 */ 59 var $num_comments; 60 61 /** 62 * Type of LIKE SQL operator to use 63 * 64 * @access private 65 * @var string 66 */ 67 var $like; 68 69 /** 70 * Class constructor 71 * 72 * @author Alejandro Pedraza 73 * @access public 74 **/ 75 function sokb() 76 { 77 $this->db = clone($GLOBALS['phpgw']->db); 78 79 // postgresql is case sensite by default, so make it case insensitive 80 if ($this->db->Type == 'pgsql') 81 { 82 $this->like = 'ILIKE'; 83 } 84 else 85 { 86 $this->like = 'LIKE'; 87 } 88 } 89 90 /** 91 * Returns array of articles 92 * 93 * @author Alejandro Pedraza 94 * @access public 95 * @param array $owners Users ids accessible by current user 96 * @param array $categories Categories ids 97 * @param int $start For pagination 98 * @param int $upper_limit For pagination 99 * @param srting $sort Sorting direction: ASC | DESC 100 * @param string $order Sorting field name 101 * @param mixed $publish_filter To filter pusblished or unpublished entries 102 * @param string $query Search string 103 * @return array Articles 104 */ 105 function search_articles($owners, $categories, $start, $upper_limit = '', $sort, $order, $publish_filter = False, $query) 106 { 107 $order = $this->db->db_addslashes($order); 108 if ($sort != 'DESC') $sort = 'ASC'; 109 110 // We use COALESCE (VALUE in case of maxdb) to turn NULLs into zeros, to avoid some databases (postgres and maxdb, don't know about mssql) 111 // to sort records with score NULL before records with a score > 0. 112 $score = (($this->db->Type == 'maxdb')? 'VALUE(SUM(phpgw_kb_search.score), 0)' : 'SUM(COALESCE(phpgw_kb_search.score))') . ' AS pertinence'; 113 // have to figure out later if maxdb is broken here... 114 $files_field = (($this->db->Type == 'maxdb')? 'VALUE(art_file)' : 'COUNT(COALESCE(art_file))') . ' AS files'; 115 116 $fields = array('phpgw_kb_articles.art_id', 'title', 'topic', 'views', 'cat_id', 'published', 'user_id', 'created', 'modified', 'votes_1', 'votes_2', 'votes_3', 'votes_4', 'votes_5', $score, $files_field); 117 $fields_str = implode(', ', $fields); 118 $owners = implode(', ', $owners); 119 120 $sql = "SELECT $fields_str FROM phpgw_kb_articles LEFT JOIN phpgw_kb_search ON phpgw_kb_articles.art_id=phpgw_kb_search.art_id "; 121 $sql .= "LEFT JOIN phpgw_kb_files ON phpgw_kb_articles.art_id=phpgw_kb_files.art_id "; 122 $sql .= "WHERE user_id IN ($owners)"; 123 if ($publish_filter && $publish_filter!='all') 124 { 125 ($publish_filter == 'published')? $publish_filter = 1 : $publish_filter = 0; 126 $sql .= " AND published=$publish_filter"; 127 } 128 if (!$categories) 129 { 130 $sql .= " AND cat_id = 0"; 131 } 132 else 133 { 134 $categories = implode(",", $categories); 135 $sql .= " AND cat_id IN(" . $categories . ")"; 136 } 137 138 if ($query) 139 { 140 $words_init = explode(' ', $query); 141 $words = array(); 142 foreach ($words_init as $word_init) 143 { 144 $words[] = $this->db->db_addslashes($word_init); 145 } 146 $likes = array(); 147 foreach ($words as $word) 148 { 149 if ((int)$word) 150 { 151 $likes[] = "phpgw_kb_articles.art_id='$word'"; 152 break; 153 } 154 $likes[] = "title {$this->like} '%$word%' OR topic {$this->like} '%$word%' OR text {$this->like} '%$word%'"; 155 } 156 $likes = implode(' OR ', $likes); 157 158 if ($likes) 159 { 160 // build query for results matching keywords (these are the most relevant results, and so appear first) 161 $sql_keywords = $sql . " AND (keyword='" . implode("' OR keyword='", $words) . "')"; 162 163 // build query for the rest of results (looking in title, topic and text only). These appear after the previous ones are shown. 164 // I must use the negation of the previous conditions to avoid shown repeated records 165 $sql_rest = $sql . " AND (keyword!='" . implode("' AND keyword!='", $words) . "' AND $likes)"; 166 } 167 } 168 169 // Group by on all fields to return unique records and calculate pertinence scores 170 $groupby = " GROUP BY phpgw_kb_articles.art_id, title, topic, views, cat_id, published, user_id, created, modified, votes_1, votes_2, votes_3, votes_4, votes_5"; 171 $order_sql = array(); 172 if ($order) 173 { 174 $order_sql[] = "$order $sort"; 175 } 176 if ($query) 177 { 178 $order_sql[] = "pertinence DESC"; 179 } 180 if (!$order && !$query) 181 { 182 $order_sql[] = "modified DESC"; 183 } 184 $order_sql = ' ORDER BY ' . implode(',', $order_sql); // only PHP lets me write crap like this 185 186 if ($query) 187 { 188 $sqls[0] = $sql_keywords.$groupby.$order_sql; 189 $sqls[1] = $sql_rest.$groupby.$order_sql; 190 } 191 else 192 { 193 $sqls[0] = $sql.$groupby.$order_sql; 194 } 195 196 //echo "sqls: "._debug_array($sqls); 197 $articles = array(); 198 $this->num_rows = 0; 199 foreach ($sqls as $sql) 200 { 201 $this->db->query($sql, __LINE__, __FILE__); 202 $this->num_rows += $this->db->num_rows(); 203 $this->db->limit_query($sql, $start, __LINE__, __FILE__, $upper_limit); 204 $start = $this->db->num_rows(); 205 $articles = array_merge($articles, $this->results_to_array($fields)); 206 } 207 208 return $articles; 209 } 210 211 /** 212 * Returns results of advanced search 213 * 214 * @author Alejandro Pedraza 215 * @access public 216 * @param array $owners User ids accessible by current user 217 * @param array $cats_ids Categories filtering 218 * @param string $ocurrences Field name where to search 219 * @param string $pub_date Articles from last 3 or 6 months, or last year 220 * @param int $start For pagination 221 * @param int $num_res For pagination 222 * @param string $all_words 'with all the words' filtering 223 * @param string $phrase 'exact phrase' filtering 224 * @param string $one_word 'with at least one of the words' filtering 225 * @param string $without_words 'without the words' filtering 226 * @param int $cat Don't know 227 * @param bool $include_subs Include subcategories when filtering by categories. Seems to not being working 228 * @return array Articles 229 * @todo use params $cat and $include_subs 230 */ 231 function adv_search_articles($owners, $cats_ids, $ocurrences, $pub_date, $start, $num_res, $all_words, $phrase, $one_word, $without_words, $cat, $include_subs) 232 { 233 $fields= array('phpgw_kb_articles.art_id', 'title', 'topic', 'views', 'cat_id', 'published', 'user_id', 'created', 'modified', 'votes_1', 'votes_2', 'votes_3', 'votes_4', 'votes_5'); 234 $fields_str = implode(' , ', $fields); 235 236 // permissions filtering 237 $owners = implode(', ', $owners); 238 $sql = "SELECT DISTINCT $fields_str FROM phpgw_kb_articles LEFT JOIN phpgw_kb_search ON phpgw_kb_articles.art_id=phpgw_kb_search.art_id WHERE user_id IN ($owners)"; 239 240 // categories filtering 241 $cats_ids = implode (',', $cats_ids); 242 if ($cats_ids) $sql .= " AND cat_id IN ($cats_ids)"; 243 244 // date filtering 245 switch ($pub_date) 246 { 247 case '3': 248 $sql .= " AND created>" . mktime(0, 0, 0, date('n')-3); 249 break; 250 case '6': 251 $sql .= " AND created>" . mktime(0, 0, 0, date('n')-6); 252 break; 253 case 'year': 254 $sql .= " AND created>" . mktime(0, 0, 0, date('n')-12); 255 break; 256 } 257 258 // ocurrences filtering 259 switch ($ocurrences) 260 { 261 case 'title': 262 $target_fields = array('title'); 263 break; 264 case 'topic': 265 $target_fields = array('topic'); 266 break; 267 case 'text': 268 $target_fields = array('text'); 269 break; 270 default: 271 $target_fields = array('title', 'topic', 'keyword', 'text'); 272 break; 273 } 274 275 // "with all the words" filtering 276 $all_words = $this->db->db_addslashes($all_words); 277 $all_words = strlen($all_words)? explode(' ', $all_words) : False; 278 $each_field = array(); 279 if ($all_words) 280 { 281 foreach ($all_words as $word) 282 { 283 $each_field[] = "(" . implode(" {$this->like} '%$word%' OR ", $target_fields) . " {$this->like} '%$word%')"; 284 } 285 if ($each_field) 286 { 287 $sql .= " AND " . implode(" AND ", $each_field); 288 } 289 } 290 291 // "with the exact phrase" filtering 292 $phrase = $this->db->db_addslashes($phrase); 293 if ($phrase) 294 { 295 $sql .= " AND (" . implode (" {$this->like} '%$phrase%' OR ", $target_fields) . " {$this->like} '%$phrase%')"; 296 } 297 298 // "With at least one of the words" filtering 299 $one_word = $this->db->db_addslashes($one_word); 300 $one_word = strlen($one_word)? explode(' ', $one_word) : False; 301 if ($one_word) 302 { 303 $each_field = array(); 304 foreach ($one_word as $word) 305 { 306 $each_field[] = "(" . implode(" {$this->like} '%$word' OR ", $target_fields) . " {$this->like} '%$word%')"; 307 } 308 $sql .= " AND (". implode (" OR ", $each_field) . ")"; 309 } 310 311 // "Without the words" filtering 312 $without_words = $this->db->db_addslashes($without_words); 313 $without_words = strlen($without_words)? explode(' ', $without_words) : False; 314 $each_field = array(); 315 if ($without_words) 316 { 317 foreach ($without_words as $word) 318 { 319 $each_field[] = "(" . implode(" NOT {$this->like} '%word' AND ", $target_fields) . " NOT {$this->like} '%$word%')"; 320 } 321 $sql .= " AND " . implode(" AND ", $each_field); 322 } 323 324 // do the query 325 //echo "query: $sql <br>"; 326 $this->db->query($sql, __LINE__, __FILE__); 327 $this->num_rows = $this->db->num_rows(); 328 $this->db->limit_query($sql, $start, __LINE__, __FILE__, $num_res); 329 return $this->results_to_array($fields); 330 } 331 332 /** 333 * Fetches results from database and returns array of articles 334 * 335 * @author Alejandro Pedraza 336 * @access private 337 * @param array $fields Which fields to fetch 338 * @return array Articles 339 */ 340 function results_to_array($fields) 341 { 342 $articles = array(); 343 for ($i=0; $this->db->next_record(); $i++) 344 { 345 foreach ($fields as $field) 346 { 347 if (preg_match('/.* AS (.*)/', $field, $matches)) { 348 $modified_field = $matches[1]; 349 } else { 350 $modified_field = $field; 351 } 352 $articles[$i][$modified_field] = $this->db->f($modified_field); 353 } 354 $articles[$i]['art_id'] = $this->db->f('art_id'); 355 $username = $GLOBALS['phpgw']->accounts->get_account_name($articles[$i]['user_id'], $lid, $fname, $lname); 356 $articles[$i]['username'] = $fname . ' ' . $lname; 357 //$articles[$i]['files'] = unserialize($articles[$i]['files']); 358 $articles[$i]['total_votes'] = $articles[$i]['votes_1'] + $articles[$i]['votes_2'] + $articles[$i]['votes_3'] + $articles[$i]['votes_4'] + $articles[$i]['votes_5']; 359 if ($articles[$i]['total_votes']) 360 { 361 $articles[$i]['average_votes'] = (1*$articles[$i]['votes_1'] + 2*$articles[$i]['votes_2'] + 3*$articles[$i]['votes_3'] + 4*$articles[$i]['votes_4'] + 5*$articles[$i]['votes_5']) / ($articles[$i]['total_votes']); 362 } 363 else 364 { 365 $articles[$i]['average_votes'] = 0; // avoid division by zero 366 } 367 } 368 return $articles; 369 } 370 371 /** 372 * Upgrades phpgw_kb_search table given user input 373 * 374 * @author Alejandro Pedraza 375 * @access public 376 * @param int $art_id Article ID 377 * @param string $word Keyword 378 * @param mixed $upgrade_key Whether to give more or less score to $word 379 * @return void 380 */ 381 function update_keywords($art_id, $word, $upgrade_key) 382 { 383 $word = $this->db->db_addslashes(substr($word, 0, 30)); 384 385 // retrieve current score 386 $sql = "SELECT score FROM phpgw_kb_search WHERE keyword='$word' AND art_id=$art_id"; 387 $this->db->query($sql, __LINE__, __FILE__); 388 $keyword_exists = $this->db->next_record(); 389 if ($keyword_exists && upgrade_key != 'same') 390 { 391 // upgrade score 392 $old_score = $this->db->f('score'); 393 $new_score = $upgrade_key? $old_score + 1 : $old_score - 1; 394 $sql = "UPDATE phpgw_kb_search SET score=$new_score WHERE keyword='$word' AND art_id=$art_id"; 395 $this->db->query($sql, __LINE__, __FILE__); 396 } 397 elseif (!$keyword_exists || $upgrade_key != 'same') 398 { 399 // create new entry for word 400 $sql = "INSERT INTO phpgw_kb_search (keyword, art_id, score) VALUES('$word', $art_id, 1)"; 401 $this->db->query($sql, __LINE__, __FILE__); 402 } 403 } 404 405 /** 406 * Returns unanswered questions 407 * 408 * @author Alejandro Pedraza 409 * @access public 410 * @param array $owners User ids accessible by current user 411 * @param array $categories Categories ids 412 * @param int $start For pagination 413 * @param int $upper_limit For pagination 414 * @param srting $sort Sorting direction: ASC | DESC 415 * @param string $order Sorting field name 416 * @param mixed $publish_filter To filter pusblished or unpublished entries 417 * @param string $query Search string 418 * @return array Questions 419 */ 420 function unanswered_questions($owners, $categories, $start, $upper_limit='', $sort, $order, $publish_filter=False, $query) 421 { 422 $fields = array('question_id', 'user_id', 'summary', 'details', 'cat_id', 'creation', 'published'); 423 $fields_str = implode(', ', $fields); 424 $owners = implode(', ', $owners); 425 $sql = "SELECT $fields_str FROM phpgw_kb_questions WHERE user_id IN ($owners)"; 426 if ($publish_filter && $publish_filter!='all') 427 { 428 ($publish_filter == 'published')? $publish_filter = 1 : $publish_filter = 0; 429 $sql .= " AND published=$publish_filter"; 430 } 431 if (!$categories) 432 { 433 $sql .= " AND cat_id = 0"; 434 } 435 else 436 { 437 $categories = implode(",", $categories); 438 $sql .= " AND cat_id IN(" . $categories . ")"; 439 } 440 if ($query) 441 { 442 $query = $this->db->db_addslashes($query); 443 $words = explode(' ', $query); 444 $sql .= " AND (summary {$this->like} '%" . implode("%' OR summary {$this->like} '%", $words) . "%' OR details {$this->like} '%" . implode("%' OR details {$this->like} '%", $words) . "%')"; 445 } 446 if ($order) 447 { 448 $sql .= " ORDER BY $order $sort"; 449 } 450 //echo "sql: $sql <br><br>"; 451 $this->db->query($sql, __LINE__, __FILE__); 452 $this->num_rows = $this->db->num_rows(); 453 $this->num_questions = $this->num_rows; 454 $this->db->limit_query($sql, $start, __LINE__, __FILE__, $upper_limit); 455 $questions = array(); 456 for ($i=0; $this->db->next_record(); $i++) 457 { 458 foreach ($fields as $field) 459 { 460 $questions[$i][$field] = $this->db->f($field); 461 } 462 $username = $GLOBALS['phpgw']->accounts->get_account_name($questions[$i]['user_id'], $lid, $fname, $lname); 463 $questions[$i]['username'] = $fname . ' ' . $lname; 464 } 465 return $questions; 466 } 467 468 /** 469 * Saves a new or edited article 470 * 471 * @author Alejandro Pedraza 472 * @access public 473 * @param array $contents article contents 474 * @param bool $is_new True if it's a new article, False if its an edition 475 * @param bool $publish True if the article is to be published without revision 476 * @return mixed article id or False if failure 477 **/ 478 function save_article($contents, $is_new, $publish = False) 479 { 480 $current_time = time(); 481 if ($is_new) 482 { 483 ($publish)? $publish = 1 : $publish = 0; 484 $q_id = $contents['answering_question']? $contents['answering_question'] : 0; 485 $sql = "INSERT INTO phpgw_kb_articles (q_id, title, topic, text, cat_id, published, user_id, created, modified, modified_user_id, votes_1, votes_2, votes_3, votes_4, votes_5) VALUES (" 486 . "$q_id, '" 487 . $this->db->db_addslashes($contents['title']) . "', '" 488 . $this->db->db_addslashes($contents['topic']) . "', '" 489 . $this->db->db_addslashes($contents['text']) . "', " 490 . (int) $contents['cat_id'] . ", " 491 . $publish . ", " 492 . $GLOBALS['phpgw_info']['user']['account_id'] . ", " 493 . $current_time . ", " . $current_time . ", " 494 . $GLOBALS['phpgw_info']['user']['account_id'] . ", " 495 . " 0, 0, 0, 0, 0)"; 496 $this->db->query($sql, __LINE__, __FILE__); 497 $article_id = $this->db->get_last_insert_id('phpgw_kb_articles', 'art_id'); 498 499 // update table phpgw_kb_search with keywords. Even if no keywords were introduced, generate an entry 500 $keywords = explode (' ', $contents['keywords']); 501 foreach ($keywords as $keyword) 502 { 503 $this->update_keywords($article_id, $keyword, 'same'); 504 } 505 506 // if publication is automatic and the article answers a question, delete the question 507 if ($publish && $contents['answering_question']) 508 { 509 $sql = "DELETE FROM phpgw_kb_questions WHERE question_id=$q_id"; 510 $this->db->query($sql, __LINE__, __FILE__); 511 } 512 513 return $article_id; 514 } 515 else 516 { 517 $sql = "UPDATE phpgw_kb_articles SET " 518 ." title='" . $this->db->db_addslashes($contents['title']) 519 ."', topic='" . $this->db->db_addslashes($contents['topic']) 520 ."', text='" . $this->db->db_addslashes($contents['text']) 521 ."', cat_id='" . (int)($contents['cat_id']) 522 ."', modified=" . $current_time 523 .", modified_user_id=" . $GLOBALS['phpgw_info']['user']['account_id'] 524 ." WHERE art_id=" . $contents['editing_article_id']; 525 $this->db->query($sql, __LINE__, __FILE__); 526 $queries_ok = false; 527 if ($this->db->affected_rows()) $queries_ok = true; 528 529 // update keywords 530 $keywords = explode (' ', $contents['keywords']); 531 foreach ($keywords as $keyword) 532 { 533 $this->update_keywords($contents['editing_article_id'], $keyword, True, False); 534 } 535 536 if ($queries_ok) 537 { 538 return $contents['editing_article_id']; 539 } 540 else 541 { 542 return False; 543 } 544 } 545 } 546 547 /** 548 * Changes article owner when user is deleted 549 * 550 * @author Alejandro Pedraza 551 * @access public 552 * @param int $owner actual owner 553 * @param int $new_owner new owner 554 * @return void 555 **/ 556 function change_articles_owner($owner, $new_owner) 557 { 558 $sql = "UPDATE phpgw_kb_articles SET user_id='$new_owner' WHERE user_id='$owner'"; 559 $this->db->query($sql, __LINE__, __FILE__); 560 } 561 562 /** 563 * Deletes article 564 * 565 * @author Alejandro Pedraza 566 * @access public 567 * @param int $art_id article id 568 * @return bool 1 on success, 0 on failure 569 **/ 570 function delete_article($art_id) 571 { 572 $sql = "DELETE FROM phpgw_kb_articles WHERE art_id=$art_id"; 573 if (!$this->db->query($sql, __LINE__, __FILE__)) return 0; 574 return 1; 575 } 576 577 /** 578 * Deletes question 579 * 580 * @author Alejandro Pedraza 581 * @access public 582 * @param int $q_id Question id 583 * @return bool 1 on success, 0 on failure 584 **/ 585 function delete_question($q_id) 586 { 587 $sql = "DELETE FROM phpgw_kb_questions WHERE question_id=$q_id"; 588 if (!$this->db->query($sql, __LINE__, __FILE__)) return 0; 589 return 1; 590 } 591 592 /** 593 * Returns latest articles entered 594 * 595 * @author Alejandro Pedraza 596 * @access public 597 * @param int $parent_cat Category id 598 * @return array Articles 599 */ 600 function get_latest_articles($parent_cat) 601 { 602 $sql = "SELECT art_id, title, topic, text, modified, votes_1, votes_2, votes_3, votes_4, votes_5 FROM phpgw_kb_articles"; 603 $this->db->query($sql, __LINE__, __FILE__); 604 605 $articles = array(); 606 while ($this->db->next_record()) 607 { 608 $rating = 1*$this->db->f('votes_1') + 2*$this->db->f('votes_2') + 3*$this->db->f('votes_3') + 4*$this->db->f('votes_4') + 5*$this->db->f('votes_5'); 609 $articles[$this->db->f('art_id')] = array( 610 'title' => $this->db->f('title'), 611 'topic' => $this->db->f('topic'), 612 'text' => $this->db->f('text'), 613 'modified' => $this->db->f('modified'), 614 'rating' => $rating 615 ); 616 } 617 618 return $articles; 619 } 620 621 /** 622 * Returns article 623 * 624 * @author Alejandro Pedraza 625 * @access public 626 * @param int $art_id article id 627 * @return array Article 628 **/ 629 function get_article($art_id) 630 { 631 $fields = array('art_id', 'title', 'topic', 'text', 'views', 'cat_id', 'published', 'user_id', 'created', 'modified', 'modified_user_id', 'votes_1', 'votes_2', 'votes_3', 'votes_4', 'votes_5'); 632 $fields_str = implode(", ", $fields); 633 634 $sql = "SELECT $fields_str FROM phpgw_kb_articles WHERE art_id=$art_id"; 635 //echo "sql: $sql <br>"; 636 $this->db->query($sql, __LINE__, __FILE__); 637 $article = array(); 638 if (!$this->db->next_record()) return 0; 639 foreach ($fields as $field) 640 { 641 $article[$field] = $this->db->f($field); 642 } 643 644 // get article's attached files names 645 $this->db->query("SELECT art_file, art_file_comments FROM phpgw_kb_files WHERE art_id=$art_id", __LINE__, __FILE__); 646 $article['files'] = array(); 647 $i = 0; 648 while ($this->db->next_record()) 649 { 650 $article['files'][$i]['file'] = $this->db->f('art_file'); 651 $article['files'][$i]['comment'] = $this->db->f('art_file_comments'); 652 $i++; 653 } 654 655 // get article's attached urls 656 $this->db->query("SELECT art_url, art_url_title FROM phpgw_kb_urls WHERE art_id=$art_id", __LINE__, __FILE__); 657 $article['urls'] = array(); 658 $i = 0; 659 while ($this->db->next_record()) 660 { 661 $article['urls'][$i]['link'] = $this->db->f('art_url'); 662 $article['urls'][$i]['title'] = $this->db->f('art_url_title'); 663 $i++; 664 } 665 666 // get article's keywords 667 $this->db->query("SELECT keyword FROM phpgw_kb_search WHERE art_id=$art_id", __LINE__, __FILE__); 668 $article['keywords'] = array(); 669 while ($this->db->next_record()) 670 { 671 $article['keywords'][] = $this->db->f('keyword'); 672 } 673 $article['keywords'] = implode(' ', $article['keywords']); 674 675 // normalize vote frequence to the range 0 - 40 676 $votes = array(); 677 $article['total_votes'] = $article['votes_1'] + $article['votes_2'] + $article['votes_3'] + $article['votes_4'] + $article['votes_5']; 678 if ($article['total_votes']) 679 { 680 $article['average_votes'] = ($article['votes_1'] + 2*$article['votes_2'] + 3*$article['votes_3'] + 4*$article['votes_4'] + 5*$article['votes_5']) / $article['total_votes']; 681 } 682 else 683 { 684 $article['average_votes'] = 0; 685 } 686 687 return $article; 688 } 689 690 /** 691 * Returns all articles ids from a given owner 692 * 693 * @author Alejandro Pedraza 694 * @access public 695 * @param int $owner owner id 696 * @return array Articles ids 697 **/ 698 function get_articles_ids($owner) 699 { 700 $sql = "SELECT art_id FROM phpgw_kb_articles WHERE user_id=$owner"; 701 $this->db->query($sql, __LINE__, __FILE__); 702 $articles_ids = array(); 703 while ($this->db->next_record()) 704 { 705 $articles_ids[] = $this->db->f('art_id'); 706 } 707 return $articles_ids; 708 } 709 710 /** 711 * Increments the view count of a published article 712 * 713 * @author Alejandro Pedraza 714 * @param int $art_id article id 715 * @param int $current_count current view count 716 * @return void 717 **/ 718 function register_view($art_id, $current_count) 719 { 720 $current_count ++; 721 $sql = "UPDATE phpgw_kb_articles SET views=$current_count WHERE art_id=$art_id"; 722 $this->db->query($sql, __LINE__, __FILE__); 723 } 724 725 /** 726 * Returns article's comments 727 * 728 * @author Alejandro Pedraza 729 * @param int $art_id article id 730 * @param int $limit Number of comments to return 731 * @return array Comments 732 */ 733 function get_comments($art_id, $limit) 734 { 735 $fields = array('comment_id', 'user_id', 'comment', 'entered', 'art_id', 'published'); 736 $fields_str = implode(", ", $fields); 737 $sql = "SELECT " . $fields_str . " FROM phpgw_kb_comment WHERE art_id=$art_id ORDER BY entered DESC"; 738 $this->db->query($sql, __LINE__, __FILE__); 739 $this->num_comments = $this->db->num_rows(); 740 if ($limit) 741 { 742 $this->db->limit_query($sql, 0, __LINE__, __FILE__, $limit); 743 } 744 $comments = array(); 745 for ($i=0; $this->db->next_record(); $i++) 746 { 747 foreach ($fields as $field) 748 { 749 $comments[$i][$field] = $this->db->f($field); 750 } 751 $GLOBALS['phpgw']->accounts->get_account_name($comments[$i]['user_id'], $lid, $fname, $lname); 752 $comments[$i]['username'] = $fname . ' ' . $lname; 753 } 754 return $comments; 755 } 756 757 /** 758 * Delete article's comments 759 * 760 * @author Alejandro Pedraza 761 * @access public 762 * @param int $art_id article id 763 * @return void 764 */ 765 function delete_comments($art_id) 766 { 767 $sql = "DELETE FROM phpgw_kb_comment WHERE art_id=$art_id"; 768 $this->db->query($sql, __LINE__, __FILE__); 769 } 770 771 /** 772 * Delete article's ratings 773 * 774 * @author Alejandro Pedraza 775 * @access public 776 * @param int $art_id article id 777 * @return void 778 */ 779 function delete_ratings($art_id) 780 { 781 $sql = "DELETE FROM phpgw_kb_ratings WHERE art_id=$art_id"; 782 $this->db->query($sql, __LINE__, __FILE__); 783 } 784 785 /** 786 * Delete article's file entries in phpgw_kb_files 787 * 788 * @author Alejandro Pedraza 789 * @access public 790 * @param int $art_id article id 791 * @param string $file_to_erase File name 792 * @return bool 1 on success, 0 on failure 793 */ 794 function delete_files($art_id, $file_to_erase = false) 795 { 796 $files = ''; 797 if ($file_to_erase) 798 { 799 $file_to_erase = $this->db->db_addslashes($file_to_erase); 800 $files = " AND art_file='$file_to_erase'"; 801 } 802 $sql = "DELETE FROM phpgw_kb_files WHERE art_id=$art_id$files"; 803 $this->db->query($sql, __LINE__, __FILE__); 804 if ($this->db->affected_rows()) return True; 805 return False; 806 } 807 808 /** 809 * Delete article's urls 810 * 811 * @author Alejandro Pedraza 812 * @access public 813 * @param int $art_id article id 814 * @return void 815 */ 816 function delete_urls($art_id) 817 { 818 $sql = "DELETE FROM phpgw_kb_urls WHERE art_id=$art_id"; 819 $this->db->query($sql, __LINE__, __FILE__); 820 } 821 822 /** 823 * Returns an article related comments 824 * 825 * @author Alejandro Pedraza 826 * @access public 827 * @param int $art_id Article id 828 * @param array $owners Accessible owners to current user 829 * @return array IDs and titles of articles 830 */ 831 function get_related_articles($art_id, $owners) 832 { 833 $owners = implode(', ', $owners); 834 $sql = "SELECT phpgw_kb_articles.art_id, phpgw_kb_articles.title FROM phpgw_kb_related_art, phpgw_kb_articles WHERE phpgw_kb_related_art.related_art_id=phpgw_kb_articles.art_id AND phpgw_kb_related_art.art_id=$art_id AND phpgw_kb_articles.user_id IN ($owners)"; 835 $this->db->query($sql, __LINE__, __FILE__); 836 $related = array(); 837 while ($this->db->next_record()) 838 { 839 $related[] = array('art_id' => $this->db->f('art_id'), 'title' => $this->db->f('title')); 840 } 841 return $related; 842 } 843 844 /** 845 * Tells if the current user has already rated the article 846 * 847 * @author Alejandro Pedraza 848 * @access public 849 * @param int $art_id article id 850 * @return bool 1 if he has, 0 if not 851 **/ 852 function user_has_voted($art_id) 853 { 854 $sql = "SELECT * FROM phpgw_kb_ratings WHERE user_id=" . $GLOBALS['phpgw_info']['user']['account_id'] . " AND art_id=$art_id"; 855 $this->db->query($sql, __LINE__, __FILE__); 856 if ($this->db->next_record()) return 1; 857 return 0; 858 } 859 860 /** 861 * Stores new comment 862 * 863 * @author Alejandro Pedraza 864 * @access public 865 * @param string $comment comment text 866 * @param int $art_id article id 867 * @param bool $publish True if comment is to be published, False if not 868 * @return bool 1 on success, 0 on failure 869 **/ 870 function add_comment($comment, $art_id, $publish) 871 { 872 $comment = $this->db->db_addslashes($comment); 873 ($publish)? $publish = 1 : $publish = 0; 874 $sql = "INSERT INTO phpgw_kb_comment (user_id, comment, entered, art_id, published) VALUES(" 875 . $GLOBALS['phpgw_info']['user']['account_id'] . ", '$comment', " . time() . ", $art_id, $publish)"; 876 $this->db->query($sql, __LINE__, __FILE__); 877 if (!$this->db->affected_rows()) return 0; 878 return 1; 879 } 880 881 /** 882 * Adds link to article 883 * 884 * @author Alejandro Pedraza 885 * @access public 886 * @param string $url Url 887 * @param string $title Url title 888 * @param int $art_id article id 889 * @return bool 1 on success, 0 on failure 890 */ 891 function add_link($url, $title, $art_id) 892 { 893 $sql = "INSERT INTO phpgw_kb_urls (art_id, art_url, art_url_title) VALUES ($art_id, '$url', '$title')"; 894 $this->db->query($sql, __LINE__, __FILE__); 895 if (!$this->db->affected_rows()) return 0; 896 return 1; 897 } 898 899 /** 900 * Publishes article, and resets creation and modification date 901 * 902 * @author Alejandro Pedraza 903 * @access public 904 * @param int $art_id article id 905 * @return int Numbers of lines affected (should be 1, if not there's an error) 906 **/ 907 function publish_article($art_id) 908 { 909 $sql = "UPDATE phpgw_kb_articles SET published=1, created=". time() . ", modified=" . time() . " WHERE art_id=$art_id"; 910 $this->db->query($sql, __LINE__, __FILE__); 911 912 // check if the article answers a question, and if so, delete it 913 $sql = "SELECT q_id FROM phpgw_kb_articles WHERE art_id=$art_id"; 914 $this->db->query($sql, __LINE__, __FILE__); 915 if ($this->db->next_record()) 916 { 917 $sql = "DELETE FROM phpgw_kb_questions WHERE question_id=".$this->db->f('q_id'); 918 $this->db->query($sql, __LINE__, __FILE__); 919 } 920 921 return True; 922 } 923 924 /** 925 * Publishes question 926 * 927 * @author Alejandro Pedraza 928 * @access public 929 * @param int $q_id Question id 930 * @return int Numbers of lines affected (should be 1, if not there's an error) 931 **/ 932 function publish_question($q_id) 933 { 934 $sql = "UPDATE phpgw_kb_questions SET published=1 WHERE question_id=$q_id"; 935 $this->db->query($sql, __LINE__, __FILE__); 936 return ($this->db->affected_rows()); 937 } 938 939 /** 940 * Publishes article comment 941 * 942 * @author Alejandro Pedraza 943 * @access public 944 * @param int $art_id Article ID 945 * @param int $comment_id Comment ID 946 * @return int Numbers of lines affected (should be 1, if not there's an error) 947 */ 948 function publish_comment($art_id, $comment_id) 949 { 950 $sql = "UPDATE phpgw_kb_comment SET published=1 WHERE art_id=$art_id AND comment_id=$comment_id"; 951 $this->db->query($sql, __LINE__, __FILE__); 952 return ($this->db->affected_rows()); 953 } 954 955 /** 956 * Deletes article comment 957 * 958 * @author Alejandro Pedraza 959 * @access public 960 * @param int $art_id Article ID 961 * @param int $comment_id Comment ID 962 * @return int Numbers of lines affected (should be 1, if not there's an error) 963 */ 964 function delete_comment($art_id, $comment_id) 965 { 966 $sql = "DELETE FROM phpgw_kb_comment WHERE art_id=$art_id AND comment_id=$comment_id"; 967 $this->db->query($sql, __LINE__, __FILE__); 968 return ($this->db->affected_rows()); 969 } 970 971 /** 972 * Deletes article comment 973 * 974 * @author Alejandro Pedraza 975 * @access public 976 * @param int $art_id Article ID 977 * @param int $delete_link Link ID 978 * @return bool 1 on success, 0 on failure 979 */ 980 function delete_link($art_id, $delete_link) 981 { 982 $delete_link = $this->db->db_addslashes($delete_link); 983 $sql = "DELETE FROM phpgw_kb_urls WHERE art_id=$art_id AND art_url='$delete_link'"; 984 $this->db->query($sql, __LINE__, __FILE__); 985 if (!$this->db->affected_rows()) return 0; 986 return 1; 987 } 988 989 /** 990 * Increments vote_x in table 991 * 992 * @author Alejandro Pedraza 993 * @access public 994 * @param int $art_id Article id 995 * @param int $rating Rating between 1 and 5 996 * @param int $current_rating Number of current votes in that rating 997 * @return bool 1 on success, 0 on failure 998 **/ 999 function add_vote($art_id, $rating, $current_rating) 1000 { 1001 $new_rating = $current_rating + 1; 1002 $sql = "UPDATE phpgw_kb_articles SET votes_" . $rating . "=$new_rating WHERE art_id=$art_id"; 1003 $this->db->query($sql, __LINE__, __FILE__); 1004 if (!$this->db->affected_rows()) return 0; 1005 return 1; 1006 } 1007 1008 /** 1009 * Registers that actual user has voted this article 1010 * 1011 * @author Alejandro Pedraza 1012 * @access public 1013 * @param int $art_id article id 1014 * @return bool 1 on success, 0 on failure 1015 **/ 1016 function add_rating_user($art_id) 1017 { 1018 $sql = "INSERT INTO phpgw_kb_ratings (user_id, art_id) VALUES(" . $GLOBALS['phpgw_info']['user']['account_id'] . ", $art_id)"; 1019 $this->db->query($sql, __LINE__, __FILE__); 1020 if (!$this->db->affected_rows()) return 0; 1021 return 1; 1022 } 1023 1024 /** 1025 * Register file upload in the article's database record 1026 * 1027 * @author Alejandro Pedraza 1028 * @access public 1029 * @param int $article_id Article id 1030 * @param string $file_name File name 1031 * @return bool 1 on success, 0 on failure 1032 */ 1033 function add_file($article_id, $file_name) 1034 { 1035 $file_name = $this->db->db_addslashes($file_name); 1036 $comment = $_POST['file_comment']? $_POST['file_comment'] : ''; 1037 $comment = $this->db->db_addslashes($comment); 1038 1039 $sql = "INSERT INTO phpgw_kb_files (art_id, art_file, art_file_comments) VALUES($article_id, '$file_name', '$comment')"; 1040 $this->db->query($sql, __LINE__, __FILE__); 1041 if (!$this->db->next_record()) return 0; 1042 return 1; 1043 } 1044 1045 /** 1046 * Checks if there is already an article in the db with the given ID 1047 * 1048 * @author Alejandro Pedraza 1049 * @access public 1050 * @param int $art_id article id 1051 * @return bool 1 if there is one, 0 if not 1052 **/ 1053 function exist_articleID($article_id) 1054 { 1055 $sql = "SELECT art_id FROM phpgw_kb_articles WHERE art_id=" . $article_id; 1056 $this->db->query($sql, __LINE__, __FILE__); 1057 return $this->db->next_record(); 1058 } 1059 1060 /** 1061 * Returns ids of owners of articles 1062 * 1063 * @author Alejandro Pedraza 1064 * @access public 1065 * @param array $articles Ids of articles 1066 * @return array Article ids and owners ids 1067 */ 1068 function owners_list($articles) 1069 { 1070 $articles = implode(', ', $articles); 1071 $sql = "SELECT art_id, user_id FROM phpgw_kb_articles WHERE art_id IN($articles)"; 1072 $this->db->query($sql, __LINE__, __FILE__); 1073 $owners = array(); 1074 while ($this->db->next_record()) 1075 { 1076 $owners[] = array('art_id' => $this->db->f('art_id'), 'user_id' => $this->db->f('user_id')); 1077 } 1078 return $owners; 1079 } 1080 1081 /** 1082 * Adds related article to article 1083 * 1084 * @author Alejandro Pedraza 1085 * @access public 1086 * @param int $art_id Article id 1087 * @param array $articles Articles id to relate to $art_id 1088 * @return bool 1 on success, 0 on failure 1089 */ 1090 function add_related($art_id, $articles) 1091 { 1092 $added = False; 1093 foreach ($articles as $article) 1094 { 1095 $sql = "INSERT INTO phpgw_kb_related_art (art_id, related_art_id) VALUES($art_id, $article)"; 1096 $this->db->query($sql, __LINE__, __FILE__); 1097 if ($this->db->affected_rows()) $added = True; 1098 } 1099 return $added; 1100 } 1101 1102 /** 1103 * Deletes related article to article 1104 * 1105 * @author Alejandro Pedraza 1106 * @access public 1107 * @param int $art_id Article id 1108 * @param int $related_id Article id to delete 1109 * @return void 1110 */ 1111 function delete_related($art_id, $related_id, $all = False) 1112 { 1113 $sql_operator = $all? 'OR' : 'AND'; 1114 $sql = "DELETE FROM phpgw_kb_related_art WHERE art_id=$art_id $sql_operator related_art_id=$related_id"; 1115 $this->db->query($sql, __LINE__, __FILE__); 1116 } 1117 1118 /** 1119 * Deletes entry in keywords table 1120 * 1121 * @author Alejandro Pedraza 1122 * @access public 1123 * @param int $art_id Article id 1124 * @return void 1125 */ 1126 function delete_search($art_id) 1127 { 1128 $sql = "DELETE FROM phpgw_kb_search WHERE art_id=$art_id"; 1129 $this->db->query($sql, __LINE__, __FILE__); 1130 } 1131 1132 /** 1133 * Adds question to database 1134 * 1135 * @author Alejandro Pedraza 1136 * @access public 1137 * @param array $data Question data 1138 * @param bool $publish Whether to publish the question or not 1139 * @return int Numbers of lines affected (should be 1, if not there's an error) 1140 */ 1141 function add_question($data, $publish) 1142 { 1143 ($publish)? $publish = 1 : $publish = 0; 1144 $sql = "INSERT INTO phpgw_kb_questions (user_id, summary, details, cat_id, creation, published) VALUES (" 1145 . $GLOBALS['phpgw_info']['user']['account_id'] . ", '" 1146 . $this->db->db_addslashes($data['summary']) . "', '" 1147 . $this->db->db_addslashes($data['details']) . "', " 1148 . (int)$data['cat_id'] . ", " 1149 . time() . ", " 1150 . $publish . ")"; 1151 $this->db->query($sql, __LINE__, __FILE__); 1152 return $this->db->affected_rows(); 1153 } 1154 1155 /** 1156 * Returns question 1157 * 1158 * @author Alejandro Pedraza 1159 * @access public 1160 * @param int $q_id Question id 1161 * @return array Question 1162 */ 1163 function get_question($q_id) 1164 { 1165 $fields = array('user_id', 'summary', 'details', 'cat_id', 'creation'); 1166 $fields_str = implode(", ", $fields); 1167 1168 $sql = "SELECT $fields_str FROM phpgw_kb_questions WHERE question_id=$q_id AND published=1"; 1169 $this->db->query($sql, __LINE__, __FILE__); 1170 $question = array(); 1171 while ($this->db->next_record()) 1172 { 1173 foreach ($fields as $field) 1174 { 1175 $question[$field] = $this->db->f($field); 1176 } 1177 } 1178 return $question; 1179 } 1180 } 1181 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Sun Feb 25 17:20:01 2007 | par Balluche grâce à PHPXref 0.7 |