PhpLabware PHP Cross Reference Learning Management Systems

Source: /includes/db_inc.php - 1661 lines - 59278 bytes - Summary - Text - Print

Description: functions interfacing with the databaseauthor: Nico Stuurman <nicost@soureforge.net>

   1  <?php
   2    
   3  // db_inc.php -  functions interfacing with the database
   4  // db_inc.php - author: Nico Stuurman <nicost@soureforge.net>
   5    /***************************************************************************
   6    * This script contain functions interfacing with the database              *
   7    * Although they are geared towards phplabware, they might be more generally*
   8    *   useful.                                                                *
   9    *                                                                          *
  10    *                                                                          *
  11    * Copyright (c) 2001 by Nico Stuurman                                      *
  12    * ------------------------------------------------------------------------ *
  13    *  This program is free software; you can redistribute it and/or modify it *
  14    *  under the terms of the GNU General Public License as published by the   *
  15    *  Free Software Foundation; either version 2 of the License, or (at your  *
  16    *  option) any later version.                                              *
  17    \**************************************************************************/       
  18  
  19  class tableinfo {
  20     var $short;
  21     var $realname;
  22     var $label;
  23     var $desname;
  24     var $queryname;
  25     var $pagename;
  26     var $id;
  27  
  28     // hackers way of overloading
  29     function tableinfo ($db,$realname=false,$id=false) {
  30        global $_GET;
  31  
  32        if ($id)
  33           $r=$db->Execute("SELECT id,shortname,tablename,real_tablename,table_desc_name,label FROM tableoftables WHERE id='$id'");
  34        elseif ($realname)
  35           $r=$db->Execute("SELECT id,shortname,tablename,real_tablename,table_desc_name,label FROM tableoftables WHERE real_tablename='$realname'");
  36        else
  37           $r=$db->Execute("SELECT id,shortname,tablename,real_tablename,table_desc_name,label FROM tableoftables WHERE tablename='$_GET[tablename]'");
  38        $this->id=$r->fields['id'];
  39        $this->short=$r->fields['shortname'];
  40        $this->realname=$r->fields['real_tablename'];
  41        $this->name=$r->fields['tablename'];
  42        $this->htmlname=str_replace(' ','%20',htmlentities($r->fields['tablename']));
  43        $this->label=$r->fields['label'];
  44        $this->desname=$r->fields['table_desc_name'];
  45        $this->fields=comma_array_SQL($db,$this->desname,columnname);
  46     }
  47  }
  48  
  49  
  50  if (!function_exists('array_key_exists')) {
  51     function array_key_exists($find,$array){
  52        while ((list($key,$value)=each($array)) && !$test) {
  53           if ($key==$find)
  54              $test=true;
  55        }
  56        return $test;
  57     }
  58  }
  59  
  60  /**
  61   * *
  62   *  Analogue of adodb GetMenu2, but display referenced values (using getvalues)
  63   *
  64   */
  65  function GetValuesMenu ($db,$selectname,$selected,$tablename,$columnname,$whereclause=false,$jscript=false) {
  66     global $max_menu_length;
  67  
  68     $tableinfo=new tableinfo($db,$tablename);
  69     $r=$db->Execute("SELECT id FROM $tablename $whereclause");
  70     while ($r && !$r->EOF) {
  71        $values[]=getvalues($db,$tableinfo,$columnname,'id',$r->fields[0]);
  72        $r->MoveNext();
  73     }
  74     $text="<select name='$selectname' $jscript>\n";
  75     $text.="<option value=''></option>\n";
  76     if (is_array($values)) {
  77        foreach ($values as $value) {
  78           $v["{$value[0]['recordid']}"]=$value[0]['text'];
  79        }
  80        asort($v);
  81        while (list ($key,$val)=each($v)) {
  82           if ($key==$selected)
  83              $selecttext='selected';
  84           else
  85              $selecttext=false;
  86           $text.="<option $selecttext value='$key'>$val</option>\n";
  87        }
  88     }
  89     $text.="</select>\n";
  90     return $text;
  91  }
  92  
  93  
  94  /**
  95   * *
  96   *  SQL where search that returns a comma delimited string
  97   *
  98   */
  99  function comma_array_SQL_where($db,$tablein,$column,$searchfield,$searchval)
 100  {
 101     $rs = $db->Execute("select $column from $tablein where $searchfield='$searchval' order by sortkey");
 102  
 103     if ($rs) {
 104        while (!$rs->EOF) {
 105           $tempa[]=$rs->fields[0];
 106           $rs->MoveNext();
 107        }
 108     }
 109     return join(",",$tempa);
 110  }
 111  
 112  /**
 113   * *
 114   *  SQL search (entrire column) that returns a comma delimited string
 115   *
 116   */
 117  function comma_array_SQL($db,$tablein,$column,$where=false) 
 118  {
 119     $rs = $db->Execute("select $column from $tablein $where order by sortkey");
 120     if ($rs) {
 121        while (!$rs->EOF) {
 122           $tempa[]=$rs->fields[0];
 123           $rs->MoveNext();
 124        }
 125     }
 126     if ($tempa)
 127        return join(",",$tempa);
 128  }
 129  
 130  /**
 131   *  Helper function for sortstring
 132   *
 133   * returns a string with format tablename.columnname, reflecting the data source for the specified column
 134   */
 135  function origin_column ($db,$tableinfo,$key){
 136     $r=$db->Execute("SELECT associated_table,associated_column,datatype FROM $tableinfo->desname WHERE columnname='$key'");
 137     if (!$r->fields[0]) {
 138        return $tableinfo->realname.'.'.$key;
 139     } elseif ($r->fields[2]) {
 140        // (m)pulldowns need special care
 141        return $r->fields[0] . ".$key ";
 142     } else {
 143        $rtable=$db->Execute("SELECT real_tablename,table_desc_name,id FROM tableoftables WHERE id={$r->fields[0]}");
 144        $rtkey=$db->Execute("SELECT columnname,datatype,type FROM {$rtable->fields[1]} WHERE id='{$r->fields[1]}'");
 145         //  $tablecolumnvalues[$rtdesc->fields[0]]=$columnvalues[$column];
 146        $asstableinfo=new tableinfo($db,false,$rtable->fields[2]);
 147        return origin_column($db,$asstableinfo,$rtkey->fields[0]);
 148     }
 149  }
 150  
 151  
 152  /**
 153   *  Update sortdirarray and returns formatted sortdirstring
 154   *
 155   */
 156  function sortstring($db,$tableinfo,&$sortdirarray,$sortup,$sortdown) {
 157     if ($sortup && $sortup<>" ") {
 158        if (is_array($sortdirarray) && array_key_exists($sortup,$sortdirarray)) {
 159           if ($sortdirarray[$sortup]=='asc')
 160              unset($sortdirarray[$sortup]);
 161           else
 162              $sortdirarray[$sortup]='asc';
 163        }
 164        elseif (!is_array($sortdirarray) || !array_key_exists($sortup,$sortdirarray))
 165           $sortdirarray[$sortup]='asc';
 166     } 
 167     if ($sortdown && $sortdown <>" ") {
 168        if (is_array($sortdirarray) && array_key_exists($sortdown,$sortdirarray)) {
 169           if ($sortdirarray[$sortdown]=='desc')
 170              unset($sortdirarray[$sortdown]);
 171           else
 172              $sortdirarray[$sortdown]="desc";
 173        }
 174        elseif (!is_array($sortdirarray) || !array_key_exists($sortdown,$sortdirarray))
 175           $sortdirarray[$sortdown]="desc";
 176     }
 177  
 178     if ($sortdirarray) {
 179        foreach($sortdirarray as $key => $value) {
 180           if ($sortstring)
 181              $sortstring .= ", ";
 182           // if the column '$key' is of type table, we'llhave to dig deeper to find the table.column description of the underlying data
 183           $table_column=origin_column ($db,$tableinfo,$key);
 184           $sortstring .= "$table_column $value";
 185        }
 186     }
 187     return $sortstring;
 188  }
 189  
 190  /**
 191   *  Displays header of 'general' table
 192   *
 193   */
 194  function tableheader ($sortdirarray,$nowfield) 
 195  {
 196     $columnname=$nowfield['name'];
 197     $columnlabel=$nowfield['label'];
 198     echo "<th><table align='center' width='100%'><tr><td align='left'>";
 199     if ($sortdirarray[$columnname]=='asc') {
 200       $sortupicon='icons/sortup_active.png';
 201     } else {
 202        $sortupicon='icons/sortup.png';
 203     }
 204     // for mpulldowns and tables sort buttons do not make sense:
 205     if ($nowfield['datatype']!='mpulldown' && $nowfield['datatype']!='table') {
 206        echo "<input type='image' name='sortup_$columnname' value='$columnlabel' src='$sortupicon' alt='Sort Up'>";
 207     }
 208     echo "</td><th align='center'>$columnlabel</th><td align='right'>";
 209     if ($sortdirarray[$columnname]=='desc') {
 210        $sortdownicon='icons/sortdown_active.png';
 211     } else {
 212        $sortdownicon='icons/sortdown.png';
 213     }
 214     // for mpulldowns and tables sort buttons do not make sense:
 215     if ($nowfield['datatype']!='mpulldown' && $nowfield['datatype']!='table') {
 216        echo "<input type='image' name='sortdown_$columnname' value='$columnlabel' src='$sortdownicon' alt='Sort Down'>";
 217     }
 218     echo "</td></tr></table></th>\n";
 219  }
 220  
 221  /**
 222   *  Inserts $fields with $fieldvalues into $table
 223   *
 224   * Returns the id of inserted record on succes, false otherwise.
 225   * $fields is a comma separated list with all column names
 226   * Fieldvalues must be an associative array containing all the $fields to be added.
 227   * Fields named 'date' are automatically filled with a Unix timestamp
 228   */
 229  function add ($db,$table,$fields,$fieldvalues,$USER,$tableid) {
 230     if (!may_write($db,$tableid,false,$USER)) {
 231        echo "<h3>You are not allowed to do this.<br>";
 232        return false;
 233     }
 234     // test if upload already took place through variable magic
 235     if ($fieldvalues['magic'])
 236        if ($test=get_cell($db,$table,'id','magic',$fieldvalues['magic'])) {
 237           echo "<h3 align='center'>That record was already uploaded.</h3>\n";
 238           return -1;
 239        }
 240     include ('./includes/defines_inc.php');
 241     if (!($USER['permissions'] & $WRITE) )
 242        return false;
 243     // generate the new ID
 244     $id=$db->GenID($table.'_id_seq');
 245     if ($id) {
 246        $columns='id';
 247        $values="$id";
 248        $column=strtok($fields,',');
 249        while ($column) {
 250           if (!($column=='id')) {
 251              $columns.=",$column";
 252              // set userid
 253              if ($column=='ownerid') {
 254                 // a plugin can overwrite the ownerid, this can be handy when you want to assign all entries in a table to a particular user
 255                 if (function_exists('plugin_setowner'))
 256                    $fieldvalues['ownerid']=plugin_setowner($db);
 257                 else
 258                    $fieldvalues['ownerid']=$USER['id'];
 259              }
 260              // set default access rights, 
 261              elseif (in_array($column, array('gr','gw','er','ew'))) {
 262                 $fieldvalues[$column]=get_access($fieldvalues,$column);
 263              }
 264   
 265              // set timestamp
 266              if ($column=='date') {
 267                 $date=(time());
 268                 $values.=",$date";
 269              }
 270              else {
 271                 if (isset($fieldvalues[$column]) && 
 272                          !(is_array($fieldvalues[$column])) && 
 273                          strlen($fieldvalues[$column])>0)
 274                $values.=",'".addslashes($fieldvalues[$column])."'";
 275                 else
 276                    $values.=",NULL";
 277              }
 278           }
 279       $column=strtok(",");
 280        }
 281        // add trusted users entered on the form
 282        if (is_array($fieldvalues['trust_read']))
 283           foreach ($fieldvalues['trust_read'] as $userid)
 284              $db->Execute("INSERT INTO trust VALUES ('$tableid','$id','$userid','r')");
 285        if (is_array($fieldvalues['trust_write']))
 286           foreach ($fieldvalues['trust_write'] as $userid)
 287              $db->Execute("INSERT INTO trust VALUES ('$tableid','$id','$userid','w')");
 288        $query="INSERT INTO $table ($columns) VALUES ($values)";
 289        if ($db->Execute($query))
 290           return $id;
 291        else {
 292           echo "Error in query: $query.<br>";
 293           echo "<h3>Database error.  Contact your system administrator.</h3>\n";
 294        }
 295     }
 296  }
 297  
 298  /**
 299   *  For multiple choice pulldowns.  
 300   *
 301   * Deletes entries in key_table for a give record,and then re-inserts the ones present in the array
 302   */
 303  function update_mpulldown ($db,$key_table,$recordid,$valueArray) {
 304     $db->Execute ("DELETE FROM $key_table WHERE recordid=$recordid");
 305     if (is_array($valueArray)) {
 306        while (list($key,$value)=each($valueArray)) {
 307           $db->Execute ("INSERT INTO $key_table VALUES ($recordid,$value)");
 308        }
 309     }
 310  }
 311  
 312  
 313  /**
 314   *  Modifies $fields in $table with values $fieldvalues where id=$id
 315   *
 316   * Returns true on succes, false on failure
 317   * Fieldvalues must be an associative array containing all the $fields to be added.
 318   * If a field is not present in $fieldvalues, it will be set to NULL.  
 319   * The entry 'id' in $fields will be ignored.
 320   * Fields lastmodby and lastmoddate will be automatically set
 321   */
 322  function modify ($db,$table,$fields,$fieldvalues,$id,$USER,$tableid) {
 323     if (!may_write($db,$tableid,$id,$USER))
 324        return false;
 325     // delete all entries in trust related to this record first
 326     $db->Execute("DELETE FROM trust WHERE tableid='$tableid' and recordid='$id'");
 327     // then add back trusted users entered on the form
 328     if (is_array($fieldvalues['trust_read']))
 329        foreach ($fieldvalues['trust_read'] as $userid)
 330           $db->Execute("INSERT INTO trust VALUES ('$tableid','$id','$userid','r')");
 331     if (is_array($fieldvalues['trust_write']))
 332        foreach ($fieldvalues['trust_write'] as $userid)
 333           $db->Execute("INSERT INTO trust VALUES ('$tableid','$id','$userid','w')");
 334  
 335     $query="UPDATE $table SET ";
 336     $column=strtok($fields,',');
 337     while ($column) {
 338        if (! ($column=='id' || $column=='date' || $column=='ownerid' || is_array($fieldvalues[$column]) ) ) {
 339           $test=true;
 340           if (in_array($column, array('gr','gw','er','ew')))
 341              $fieldvalues[$column]=get_access($fieldvalues,$column);
 342           if ($column=='lastmodby')
 343              $fieldvalues['lastmodby']=$USER['id'];
 344           if ($column=='lastmoddate')
 345              $fieldvalues['lastmoddate']=time();
 346           if (isset($fieldvalues[$column]) && (strlen($fieldvalues[$column])>0))
 347              // escape all nasty stuff
 348              $query.="$column='".addslashes($fieldvalues[$column]) ."',";
 349           else
 350              $query.="$column=NULL,";
 351        }
 352        $column=strtok(',');
 353     }
 354     $query[strrpos($query,',')]=' ';
 355  
 356     if ($test) {
 357        $query.=" WHERE id='$id'";
 358        $result=$db->Execute($query);
 359        if ($result) {
 360           if (function_exists('plugin_modify')) {
 361              plugin_modify($db,$tableid,$id);
 362           }
 363           return true;
 364        } else {
 365           echo "<h4>Error making the requested modifications.  </h4>\n";
 366        }
 367     }
 368  }
 369  
 370  
 371  /**
 372   *  Deletes the entry with id=$id
 373   *
 374   * Returns true on succes, false on failure
 375   * Checks whether the delete is allowed
 376   * This is very generic, it is likely that you will need to do more cleanup
 377   */
 378  function delete ($db, $tableid, $id, $USER, $filesonly=false) {
 379  
 380     $table=get_cell($db,"tableoftables","real_tablename","id",$tableid);
 381     if (!may_write($db,$tableid,$id,$USER))
 382        return false;
 383  
 384     // check for associated files
 385     $r=$db->Execute("SELECT id FROM files 
 386                      WHERE tablesfk=$tableid AND ftableid=$id");
 387     while ($r && !$r->EOF) {
 388        delete_file ($db,$r->fields("id"),$USER); 
 389        $r->MoveNext();
 390     }
 391     // and now delete for real
 392     if (!$filesonly) {
 393        if ($db->Execute("DELETE FROM $table WHERE id=$id"))
 394           return true;
 395        else
 396           return false;
 397     }
 398     else
 399        return true;
 400  }
 401  
 402  /**
 403   *  Generates thumbnails and extracts information from 2-D image files
 404   *
 405   */
 406  function process_image($db,$fileid,$bigsize) 
 407  {
 408     global $USER, $system_settings;
 409     
 410     if (!$fileid)
 411        return false;
 412     $imagefile=file_path ($db,$fileid);
 413     if (!file_exists($imagefile)) {
 414        return false;
 415     }
 416     $bigthumb=$system_settings['thumbnaildir']."/big/$fileid.jpg";
 417     $smallthumb=$system_settings['thumbnaildir']."/small/$fileid.jpg";
 418     $smallsize=$system_settings['smallthumbsize'];
 419     $convert=$system_settings['convert'];
 420  
 421     // make big thumbnail and get image info
 422     $command = "$convert -verbose -sample ".$bigsize."x".$bigsize." $action \"$imagefile\" jpg:$bigthumb";
 423     exec($command, $result_str_arr, $status);
 424  
 425     // make small thumbnail
 426     $command = "$convert -sample ".$smallsize."x".$smallsize." $action \"$imagefile\" jpg:$smallthumb";
 427     `$command`;
 428  
 429     // get size, mime, and type from image file.  
 430     // Try exif function, if that fails use convert 
 431     $sizearray=getimagesize($imagefile);
 432     $width=$sizearray[0];
 433     if ($width) {
 434        $height=$sizearray[1];
 435        $mime=$sizearray['mime'];
 436        switch ($sizearray[2]) {
 437           case 1: $filename_extension='GIF'; break;
 438           case 2: $filename_extension='JPG'; break;
 439           case 3: $filename_extension='PNG'; break;
 440           case 4: $filename_extension='SWF'; break;
 441           case 5: $filename_extension='PSD'; break;
 442           case 6: $filename_extension='BMP'; break;
 443           case 7: $filename_extension='TIFF'; break;
 444           case 8: $filename_extension='TIFF'; break;
 445           case 9: $filename_extension='JPC'; break;
 446           case 10: $filename_extension='JP2'; break;
 447           case 11: $filename_extension='JPX'; break;
 448           case 12: $filename_extension='JB2'; break;
 449           case 13: $filename_extension='SWC'; break;
 450           case 14: $filename_extension='IFF'; break;
 451        }
 452     }
 453     else {
 454        // get filetype and size in pixels from convert. Take first token after filesize.  Don't know if it always works.
 455        // appparently convert yields:
 456        // original filename, dimensions, Class, (optional) colordepht, size (in kb), filetype, ???, ???
 457        $convertresult[0] = strtok ($result_str_arr[0]," ");
 458        $test = false;
 459        for ($i=1; $i<7; $i++) {
 460           $convertresult[$i] = strtok (" ");
 461           if ($i == 1) 
 462              $pixels = $convertresult[$i];
 463           if ($test) {
 464              $filename_extension = $convertresult[$i];
 465              $test = false; 
 466           }
 467           if (substr ($convertresult[$i], -2) == 'kb')
 468              $test = true;
 469        }
 470        // extract pixel dimensions, this fails when there are spaces in the filename
 471        $width = (int) strtok ($pixels, 'x+= >');
 472        $height = (int) strtok ('x+= >');
 473     }
 474  
 475     if($mime) 
 476        $db->Execute("UPDATE files SET mime='$mime' WHERE id=$fileid");
 477     $r=$db->Execute("SELECT id FROM images WHERE id=$fileid");
 478  
 479     if (!$r->fields["id"]) 
 480        $query="INSERT INTO images (id,x_size,y_size,xbt_size,ybt_size,xst_size,yst_size,type) VALUES ('$fileid', '$width', '$height', '$bigsize', '$bigsize', '$smallsize', '$smallsize', '$filename_extension')";
 481     else 
 482        $query="UPDATE images SET x_size='$width',y_size='$height',xbt_size='$bigsize',ybt_size='$bigsize',xst_size='$smallsize',yst_size='$smallsize',type='$filename_extension' WHERE id=$fileid";
 483     $db->Execute($query);
 484     
 485  }
 486  
 487  /**
 488   *  !Upload files and enters then into table files
 489   *  Filetitle in _POST will be inserted in the title field of table files
 490    * Returns id of last uploaded file upon succes, false otherwise
 491    */
 492  function upload_files ($db,$tableid,$id,$columnid,$columnname,$USER,$system_settings)
 493  {
 494     global $_FILES,$_POST,$system_settings;
 495  
 496     $table=get_cell($db,'tableoftables','tablename','id',$tableid);
 497     $real_tablename=get_cell($db,'tableoftables','real_tablename','id',$tableid);
 498  
 499     if (!($db && $table && $id)) {
 500        echo "Error in code: $db, $table, or $id is not defined.<br>";
 501        return false;
 502     }
 503     if (!may_write($db,$tableid,$id,$USER)) {
 504        echo "You do not have permission to write to table $table.<br>";
 505        return false;
 506     }
 507     if (isset($_FILES[$columnname]['name'][0]) && !$filedir=$system_settings['filedir']) {
 508        echo "<h3><i>Filedir</i> was not set.  The file was not uploaded. Please contact your system administrator</h3>";
 509        return false;
 510     }
 511     for ($i=0;$i<sizeof($_FILES[$columnname]['name']);$i++) {
 512        if (!$fileid=$db->GenID('files_id_seq'))
 513           return false;
 514        $originalname=$_FILES[$columnname]['name'][$i];
 515        $mime=$_FILES[$columnname]['type'][$i];
 516        // sometimes mime types are not set properly, let's try to fix those
 517        if (substr($originalname,-4,4)=='.pdf')
 518           $mime='application/pdf';
 519        if (substr($originalname,-4,4)=='.doc')
 520           $mime='application/msword';
 521        if (substr($originalname,-4,4)=='.htm')
 522           $mime='text/html';
 523        // work around php bug??  
 524        $mime=strtok ($mime,";");
 525        $filestype=substr(strrchr($mime,'/'),1);
 526        $size=$_FILES["$columnname"]['size'][$i];
 527        $title=$_POST['filetitle'][$i];
 528        if (!$title)
 529           $title='NULL'; 
 530        else
 531           $title="'$title'";
 532        $type=$_POST['filetype'][$i];
 533        // this works asof php 4.02
 534  if (file_exists($_FILES[$columnname]['tmp_name'][$i])) {
 535  }
 536        if (move_uploaded_file($_FILES["$columnname"]['tmp_name'][$i],"$filedir/$fileid"."_"."$originalname")) {
 537           $query="INSERT INTO files (id,filename,mime,size,title,tablesfk,ftableid,ftablecolumnid,type) VALUES ($fileid,'$originalname','$mime','$size',$title,'$tableid',$id,'$columnid','$filestype')";
 538       $db->Execute($query);
 539        } else {
 540           /** 
 541            * files that are uploaded by other phplabware code are OK
 542            * Move files that are in the phplabware tmpdir also
 543            */
 544          $dirname=dirname($_FILES[$columnname]['tmp_name'][$i]);
 545          $tmpdir=$system_settings['tmpdir'];
 546          if (dirname($_FILES[$columnname]['tmp_name'][$i]) == $system_settings['tmpdir']) {
 547             if (!@rename($_FILES[$columnname]['tmp_name'][$i],"$filedir/$fileid".'_'.$originalname)) {
 548                /**
 549                  * Try copying and deleting the old file instead
 550                  */
 551                if (!copy ($_FILES[$columnname]['tmp_name'][$i],"$filedir/$fileid".'_'.$originalname)) {
 552                   $fileid=false;
 553                }
 554                /**
 555                 * No matter if the copy failed, we can delete this file
 556                 */
 557                @unlink ($_FILES[$columnname]['tmp_name'][$i]);
 558                   
 559              } 
 560              if ($fileid) {
 561                 $query="INSERT INTO files (id,filename,mime,size,title,tablesfk,ftableid,ftablecolumnid,type) VALUES ($fileid,'$originalname','$mime','$size',$title,'$tableid',$id,'$columnid','$filestype')";
 562             $db->Execute($query);
 563              }
 564           }
 565        }
 566     }
 567     return $fileid;
 568  }
 569  
 570  
 571  /**
 572   *  returns an array with id,name,title,size, and hyperlink to all
 573   *
 574   * files associated with the given record
 575   */
 576  function get_files ($db,$table,$id,$columnid,$format=1,$thumbtype='small') {
 577     $tableid=get_cell($db,'tableoftables','id','tablename',$table);
 578     $r=$db->Execute("SELECT id,filename,title,mime,type,size FROM files WHERE tablesfk=$tableid AND ftableid=$id AND ftablecolumnid='$columnid'");
 579     if ($r && !$r->EOF) {
 580        $i=0;
 581        $sid=SID;
 582        while (!$r->EOF) {
 583           $filesid=$files[$i]['id']=$r->fields('id');
 584           $filesname=$files[$i]['name']=$r->fields('filename');
 585           $filestitle=$files[$i]['title']=$r->fields('title');
 586           $mime=$files[$i]['mime']=$r->fields('mime');
 587           $filestype=$files[$i]['type']=$r->fields('type');
 588           $filesize=$files[$i]['size']=nice_bytes($r->fields('size'));
 589           // if this is an image, we'll send the thumbnail
 590           $rb=$db->Execute("SELECT id FROM images WHERE id='$filesid'");
 591           if ($rb->fields(0)) {
 592              $text="<img src=showfile.php?id=$filesid&amp;type=$thumbtype&amp;$sid alt='Image'>";
 593           } 
 594       elseif ($format==1) {
 595              if (strlen($filestitle) > 0)
 596                 $text=$filestitle;
 597              else
 598                  $text=$filesname;
 599           }
 600       elseif ($format==2)
 601          $text="file_$i";
 602       else
 603          $text=$filesname;
 604           $icon="icons/$filestype.jpg";
 605           if (@is_readable($icon)) {
 606              $text="<img src='$icon' alt='$filestype File'>";
 607              // display the filename in tooltip only when there is an icon and javascript is enabled
 608              if ($_SESSION['javascript_enabled']) {
 609                 $tip_width=strlen($filesname);
 610                 $files[$i]['link']="<a href='showfile.php?id=$filesid&amp;$sid' onmouseover=\"this.T_WIDTH=$tip_width;return escape('$filesname')\">$text</a>\n";
 611              } else {
 612                 $files[$i]['link']="<a href='showfile.php?id=$filesid&amp;$sid'>$text</a>\n";
 613              }
 614           } else {
 615              $files[$i]['link']="<a href='showfile.php?id=$filesid&amp;$sid'>$text</a>\n";
 616           }
 617           $r->MoveNext();
 618           $i++;
 619        }
 620     return $files;
 621     }
 622  }
 623  
 624  
 625  /**
 626   *  Returns path to the file
 627   *
 628   */
 629  function file_path ($db,$fileid) {
 630     global $system_settings;
 631     $filename=get_cell($db,'files','filename','id',$fileid);
 632     return $system_settings['filedir']."/$fileid"."_$filename";
 633  }
 634  
 635  
 636  /**
 637   *  Deletes all file associated with this record,column and table
 638   *
 639   */
 640  function delete_column_file($db,$tableid,$columnid,$recordid,$USER) {
 641  
 642     $r=$db->Execute("SELECT id FROM files 
 643                      WHERE tablesfk=$tableid AND ftableid=$recordid AND ftablecolumnid=$columnid");
 644     while ($r && !$r->EOF) {
 645        delete_file ($db,$r->fields('id'),$USER); 
 646        $r->MoveNext();
 647     }
 648  }
 649  
 650  
 651  /**
 652   *  Deletes file identified with id.
 653   *
 654   * Checks 'mother table' whether this is allowed
 655   * Also deletes entries in index table for this file
 656   * Returns name of deleted file on succes
 657   */
 658  function delete_file ($db,$fileid,$USER) {
 659     global $system_settings;
 660  
 661     $tableid=get_cell($db,'files','tablesfk','id',$fileid);
 662     $tabledesc=get_cell($db,'tableoftables','table_desc_name','id',$tableid);
 663     $ftableid=get_cell($db,'files','ftableid','id',$fileid);
 664     // bail out when file was not found
 665     if (!$ftableid) {
 666        return false;
 667     }
 668     $columnid=get_cell($db,'files','ftablecolumnid','id',$fileid);
 669     $associated_table=get_cell($db,$tabledesc,'associated_table','id',$columnid);
 670     $filename=get_cell($db,'files','filename','id',$fileid);
 671     if (!may_write($db,$tableid,$ftableid,$USER))
 672        return false;
 673     @unlink($system_settings['filedir']."/$fileid"."_$filename");   
 674     // even if unlink fails we should really remove the entry from the database:
 675     $db->Execute("DELETE FROM files WHERE id=$fileid");
 676     // if this was an image:
 677     $db->Execute("DELETE FROM images WHERE id=$fileid");
 678     // remove indexing of file content
 679     $db->Execute ("DELETE FROM $associated_table WHERE fileid=$fileid");
 680     return $filename;
 681  }
 682  
 683  /**
 684   *  Returns a 2D array with id and full name of all users
 685   *
 686   * called by show_access
 687   */
 688  function user_array ($db) {
 689     $r=$db->Execute("SELECT id,firstname,lastname FROM users ORDER BY lastname");
 690     while (!$r->EOF){
 691        $ua[$i]["id"]=$r->fields['id'];
 692        if ($r->fields['firstname'])
 693           $ua[$i]['name']=$r->fields['firstname']." ".$r->fields['lastname'];
 694        else
 695           $ua[$i]['name']=$r->fields['lastname'];
 696        $i++;
 697        $r->MoveNext();
 698     }
 699     return $ua;
 700  }
 701   
 702  /**
 703   *  Prints a table with access rights
 704   *
 705   * input is string as 'rw-rw-rw-'
 706   * names are same as used in get_access
 707   */
 708  function show_access ($db,$tableid,$id,$USER,$global_settings) {
 709     global $client;
 710     $table=get_cell($db,'tableoftables','real_tablename','id',$tableid);
 711     if ($id) {
 712        $ra=$db->Execute("SELECT gr,gw,er,ew,ownerid FROM $table WHERE id='$id'");
 713        if ($ra) {
 714           $gr=$ra->fields[0];
 715           $gw=$ra->fields[1];
 716           $er=$ra->fields[2];
 717           $ew=$ra->fields[3];
 718           $ownerid=$ra->fields[4];
 719        }
 720        // $access=get_cell($db,$table,"access","id",$id);
 721        // $ownerid=get_cell($db,$table,"ownerid","id",$id);
 722        $groupid=get_cell($db,'users','groupid','id',$ownerid);
 723        $group=get_cell($db,'groups','name','id',$groupid);
 724        $rur=$db->Execute("SELECT trusteduserid FROM trust WHERE tableid='$tableid' AND recordid='$id' AND rw='r'");
 725        while (!$rur->EOF) {
 726           $ur[]=$rur->fields('trusteduserid');
 727           $rur->MoveNext();
 728        }
 729        $ruw=$db->Execute("SELECT trusteduserid FROM trust WHERE tableid='$tableid' AND recordid='$id' AND rw='w'");
 730        while (!$ruw->EOF) {
 731           $uw[]=$ruw->fields('trusteduserid');
 732           $ruw->MoveNext();
 733        }
 734     }
 735     else {
 736        $access=$global_settings['access'];
 737        // translate the $access string into our new format
 738        if ($access{3}=='r')
 739           $gr=1;
 740        if ($access{4}=='w')
 741           $gw=1;
 742        if ($access{6}=='r')
 743           $er=1;
 744        if ($access{7}=='w')
 745           $ew=1;
 746        $group=get_cell($db,'groups','name','id',$USER['groupid']);
 747     }
 748     $user_array=user_array($db);
 749     echo "<table border=0>\n";
 750     echo "<tr><th>Access:</th><th>$group</th><th>Everyone</th><th>and also</th></tr>\n";
 751     echo "<tr><th>Read</th>\n";
 752     if ($gr) $sel="checked"; else $sel=false;
 753     echo "<td><input type='checkbox' $sel name='grr' value='&nbsp;'></td>\n";
 754     if ($er) $sel="checked"; else $sel=false;
 755     echo "<td><input type='checkbox' $sel name='evr' value='&nbsp;'></td>\n";
 756     // multiple select box for trusted users.  Opera does not like 1 as size
 757     if ($client->browser=="Opera" || $client->browser=="Internet Explorer")
 758        $size=2;
 759     else
 760         $size=2;
 761     echo "<td>\n<select multiple size='$size' name='trust_read[]'>\n";
 762     echo "<option>nobody else</option>\n";
 763     foreach ($user_array as $user) {
 764       if (@in_array($user["id"],$ur))
 765           $selected="selected";
 766        else
 767           $selected=false;
 768       echo "<option $selected value=".$user["id"].">".$user["name"]."</option>\n";
 769     }
 770     echo "</select></td>\n";
 771     echo "</tr>\n";
 772     echo "<tr><th>Write</th>\n";
 773     if ($gw) $sel="checked"; else $sel=false;
 774     echo "<td><input type='checkbox' $sel name='grw' value='&nbsp;'></td>\n";
 775     if ($ew) $sel="checked"; else $sel=false;
 776     echo "<td><input type='checkbox' $sel name='evw' value='&nbsp;'></td>\n";
 777     echo "<td>\n<select multiple size='$size' name='trust_write[]'>\n";
 778     echo "<option>nobody else</option>\n";
 779     foreach ($user_array as $user) {
 780       if (@in_array($user["id"],$uw))
 781           $selected="selected";
 782        else
 783           $selected=false;
 784        echo "<option $selected value=".$user["id"].">".$user["name"]."</option>\n";
 785     }
 786     echo "</select></td>\n";
 787     echo "</tr>\n";
 788     echo "</table>\n";
 789  }
 790  
 791  
 792  /**
 793   *  Returns a formatted access strings given an associative array
 794   *
 795   * with 'grr','evr','grw','evw' as keys
 796   */
 797  function get_access ($fieldvalues,$column) {
 798     global $system_settings;
 799     $gr=0; $gw=0; $er=0; $ew=0;
 800  
 801     ${$column}=$fieldvalues[$column];
 802  
 803     if (!$fieldvalues) {
 804        $access=$system_settings['access'];
 805        // translate the $access string into our new format
 806        if ($access{3}=='r')
 807           $gr=1;
 808        if ($access{4}=='w')
 809           $gw=1;
 810        if ($access{6}=='r')
 811           $er=1;
 812        if ($access{7}=='w')
 813           $ew=1;
 814        return ${$column};
 815     }
 816     if ($fieldvalues['grr']) 
 817        $gr=1;
 818     if ($fieldvalues['evr']) 
 819        $er=1;
 820     if ($fieldvalues['grw']) 
 821        $gw=1;
 822     if ($fieldvalues['evw']) 
 823        $ew=1;
 824  
 825     return ${$column};
 826  }
 827  
 828  
 829  /**
 830   *  Returns an SQL SELECT statement with ids of records the user may see
 831   *
 832   * Since it uses subqueries it does not work with MySQL
 833   */
 834  function may_read_SQL_subselect ($db,$table,$tableid,$USER,$clause=false) {
 835     include_once  ('./includes/defines_inc.php');
 836     $query="SELECT id FROM $table ";
 837     // don't know why, but variables defined in defines_in.php are not know here
 838     // bug in my php version?
 839     $SUPER=64;
 840  
 841     if ($USER['permissions'] & $SUPER) {
 842        if ($clause)
 843           $query .= "WHERE $clause";
 844     }
 845     else {
 846        $grouplist=$USER['group_list'];
 847        $userid=$USER['id'];
 848        $query .= ' WHERE ';
 849        if ($clause) 
 850           $query .= " $clause AND ";
 851        // owner
 852        $query .= "( (ownerid=$userid) ";
 853        // group (quote gr='1', otherwise index willnot be used)
 854        $query .= "OR (CAST( (SELECT groupid FROM users WHERE users.id=$table.ownerid) AS int) IN ($grouplist) AND gr='1') ";
 855        // world
 856        $query .= "OR (er='1')";
 857        // and also
 858        $query .= "OR id IN (SELECT recordid FROM trust WHERE tableid='$tableid' AND trusteduserid='$userid' AND rw='r')";
 859        $query .=")";
 860     }
 861     return $query;
 862  }
 863  
 864  /**
 865   *  returns a comma-separated list of quoted values from a SQL search
 866   *
 867   * helper function for may_read_SQL
 868   */
 869  function make_SQL_ids ($r,$ids,$field='id') {
 870     if (!$r || $r->EOF)
 871        return substr ($ids,0,-1);
 872     $id=$r->fields[$field];
 873     $ids .="$id";
 874     $r->MoveNext();
 875     $column_count=1;
 876     while (!$r->EOF) {
 877        $id=$r->fields[$field];
 878        if ($id)
 879           $ids .=",$id";
 880        $r->MoveNext();
 881        $column_count+=1;
 882     }
 883     return ($ids);
 884  }
 885  
 886  
 887  /**
 888   *  Returns an array with ids of records the user may see in SQL format
 889   *
 890   * Works with MySQL but not with early postgres 7 versions (current ones should
 891   * work)
 892   */
 893  function may_read_SQL_JOIN ($db,$table,$USER) {
 894     include  ('./includes/defines_inc.php');
 895     if (!($USER['permissions'] & $SUPER)) {
 896        $query="SELECT id FROM $table ";
 897        $usergroup=$USER['groupid'];
 898        $group_list=$USER['group_list'];
 899        $userid=$USER['id'];
 900        $query .= " WHERE ";
 901        // owner and everyone
 902        $query .= "( (ownerid=$userid) ";
 903        $query .= "OR (er='1')";
 904        $query .=")";
 905        $r=$db->CacheExecute(2,$query);
 906        if ($r) {
 907           $ids=make_SQL_ids($r,$ids);
 908        }
 909        // group
 910        $query="SELECT $table.id FROM $table LEFT JOIN users ON $table.ownerid=users.id WHERE users.groupid IN ($group_list) AND ($table.gr='1')";
 911        $r=$db->CacheExecute(2,$query);
 912     }
 913     else {     // superuser
 914        $query="SELECT id FROM $table ";
 915        $r=$db->CacheExecute(2,$query);
 916     }
 917     if ($ids)
 918        $ids.=",";
 919     if ($r)
 920        return make_SQL_ids($r,$ids);
 921  }
 922  
 923  
 924  /**
 925   *  Generates an SQL query asking for the records that may be seen by this user
 926   *
 927   * Generates a left join for mysql, subselect for postgres
 928   */
 929  function may_read_SQL ($db,$tableinfo,$USER,$temptable='tempa') {
 930     global $db_type;
 931  
 932     if ($db_type=='mysql') {
 933        $list=may_read_SQL_JOIN ($db,$tableinfo->realname,$USER);
 934        if (!$list)
 935           $list='-1';
 936        $result['sql']= " {$tableinfo->realname}.id IN ($list) ";
 937        $result['numrows']=substr_count($list,',');
 938     }
 939     else {
 940        //return may_read_SQL_subselect ($db,$table,$tableid,$USER);
 941        $r=$db->Execute(may_read_SQL_subselect ($db,$tableinfo->realname,$tableinfo->id,$USER,false));
 942        $result['numrows']=$r->NumRows();
 943        make_temp_table($db,$temptable,$r); 
 944        $result['sql'] = " ($tableinfo->realname.id = $temptable.uniqueid) ";
 945     }
 946   
 947     return $result;
 948  }
 949  
 950  /**
 951   * Generates a temporary table from given recordset
 952   */
 953  function make_temp_table ($db,$temptable,$r) {
 954     global $system_settings;
 955     $rc=$db->Execute("CREATE TEMPORARY TABLE $temptable (
 956                       uniqueid int UNIQUE NOT NULL)");
 957     if ($rc) {
 958        $r->MoveFirst();
 959        while (!$r->EOF) {
 960           $string .= $r->fields["id"]."\n";
 961           $r->MoveNext();
 962        }
 963     }
 964     // INSERT is too slow.  COPY instead from a file.  postgres only!
 965     $tmpfile=tempnam($system_settings['tmppsql'],'tmptable');
 966     $fp=fopen($tmpfile,'w');
 967     fwrite($fp,$string);
 968     fflush($fp);
 969     chmod ($tmpfile,0644);
 970     $rd=$db->Execute ("COPY $temptable FROM '$tmpfile'"); 
 971     $rc=$db->Execute("ALTER TABLE $temptable ADD PRIMARY KEY (uniqueid)");
 972     fclose ($fp);
 973     unlink($tmpfile);
 974  }
 975  
 976  /**
 977   *  determines whether or not the user may read this record
 978   * When recordid is not set, report an error and return false, otherwise retrun true when the record with this id can be read, false otherwise
 979   * When the record does not exist return false
 980   *
 981   */
 982  function may_read ($db,$tableinfo,$id,$USER) {
 983     $list=may_read_SQL($db,$tableinfo,$USER);
 984     // we use the temptable only for non-mysql databases:
 985    if ($db->databaseType=='mysql') {
 986        $query="SELECT id FROM $tableinfo->realname WHERE ".$list['sql'];
 987     } else {
 988        $query="SELECT id FROM tempa,$tableinfo->realname WHERE ".$list['sql'];
 989     }
 990  
 991     if ($id) {
 992        $query .= ' AND id=' . $id;
 993     } else {
 994        echo "<h3>Internal error in db_inc.php, function may_read.  Please report to your System administrator</h3><br>\n";
 995        // when if is not set there is an error, return false to be safe
 996        return false;
 997     }
 998     $r=$db->Execute($query);
 999     if (!$r)
1000        return false;
1001     if ($r->EOF)
1002        return false;
1003     else
1004        return true;
1005  }
1006  
1007  /**
1008   *  checks if this user may write/modify/delete these data
1009   *
1010   */
1011  function may_write ($db,$tableid,$id,$USER) {
1012     include  ('./includes/defines_inc.php');
1013     
1014     $table=get_cell($db,'tableoftables','real_tablename','id',$tableid);
1015     if ($USER['permissions'] & $SUPER)
1016        return true;
1017     if ( ($USER['permissions'] & $WRITE) && (!$id))
1018        return true;
1019     $ownerid=get_cell($db,$table,'ownerid','id',$id);
1020     $ownergroup=get_cell($db,'users','groupid','id',$ownerid);
1021     if ($USER['permissions'] & $ADMIN) {
1022        if ($USER['groupid']==$ownergroup)
1023           return true;
1024     }
1025     if ( ($USER['permissions'] & $WRITE) && $id) {
1026        $userid=$USER['id'];
1027        // 'user' write access
1028        if ($r=$db->Execute("SELECT * FROM $table WHERE id=$id AND
1029              ownerid=$userid")) 
1030           if (!$r->EOF)
1031              return true;
1032        // 'group' write access
1033        if ($r=$db->Execute("SELECT * FROM $table WHERE id=$id AND gw='1'"))
1034           if (!$r->EOF && in_array($ownergroup, $USER['group_array']))
1035              return true;
1036        // 'world' write access
1037        if ($r=$db->Execute("SELECT * FROM $table WHERE id=$id AND ew='1'") )
1038           if (!$r->EOF) 
1039              return true;
1040        // 'and also' write access
1041        if ($r=$db->Execute("SELECT * FROM trust WHERE trusteduserid='$userid'
1042                AND tableid='$tableid' AND recordid='$id' AND rw='w'"))
1043           if (!$r->EOF) 
1044              return true;
1045     }
1046  }
1047  
1048  /**
1049   *  returns an comma-separated list of quoted values from a SQL search
1050   *
1051   * derived from make_SQL_ids but can be called from anywhere 
1052   */
1053  function make_SQL_csf ($r,$ids,$field="id",&$column_count) {
1054     if (!$r || $r->EOF)
1055        return false;
1056     $r->MoveFirst();
1057     while (!$id && !$r->EOF) {
1058        $id=$r->fields[$field];
1059        $ids .="$id";
1060        $r->MoveNext();
1061     }
1062     $column_count=1;
1063     unset ($id);
1064     while (!$r->EOF) {
1065        $id=$r->fields[$field];
1066        if ($id) {
1067           $ids .=",$id";
1068           $column_count+=1;
1069        }
1070        $r->MoveNext();
1071     }
1072     return ($ids);
1073  }
1074  /**
1075   *  helperfunction for numerictoSQL
1076   *
1077   */
1078  function typevalue ($value,$type) {
1079     if ($type=='int') {
1080        return (int)$value;
1081     }
1082     elseif ($type=='float') {
1083        return (float)$value;
1084     }
1085     return false; 
1086  }
1087  
1088  /**
1089   *  interprets numerical search terms into an SQL statement
1090   *
1091   * implements ranges (i.e. 1-6), and lists (1,2,3) and combinations thereof
1092   * < and > can also be used
1093   */
1094  function numerictoSQL (&$tableinfo,$searchterm,$column,$type,$and) {
1095     $commalist=explode(',',$searchterm);
1096     for ($i=0;$i<sizeof($commalist);$i++) {
1097        $rangelist=explode('-',$commalist[$i]);
1098        if (sizeof($rangelist)==2) {
1099           sort($rangelist);
1100           $value1=typevalue($rangelist[0],$type);
1101           $value2=typevalue($rangelist[1],$type);
1102           if ($i>0) {
1103              $sql.='OR ';
1104           }
1105           $sql.="({$tableinfo->realname}.$column>=$value1 AND {$tableinfo->realname}.$column<=$value2) ";
1106        }
1107        elseif (sizeof($rangelist)==1) {
1108           if ($commalist[$i]{0}=='<' || $commalist[$i]{0}=='>') {
1109              $token=$commalist[$i]{0};
1110              $commalist[$i]=substr($commalist[$i],1);
1111           }
1112           $value=typevalue ($commalist[$i],$type);
1113           if ($i>0) {
1114              $sql.='OR ';
1115           }
1116           if (!$token)
1117              $token='=';
1118           $sql.="({$tableinfo->realname}.$column$token$value) ";
1119        }
1120     }
1121     return "$and ($sql) ";
1122  }
1123  
1124  
1125  
1126  /**
1127   *  Converts a (textual) date range into a meaningfull SQL statement
1128   *
1129   * Start and end are separated with '-'
1130   * dates can be in current local (12/24/03), or textual (today, last month,etcc.)
1131   * Alternatively, a single date can be preceded with a '<' or '>'
1132   * I am not sure how strtotime deals with US versus European standards...
1133   */
1134  
1135  function datetoSQL ($searchterm,$column,$and) {
1136     $timerange=explode('-',$searchterm);
1137     if (sizeof($timerange) < 2) {
1138        // we have only one searchterm
1139        if ($timerange[0]{0} == '>') {
1140           $time=strtotime(substr($timerange[0],1));
1141           if ($time>0)
1142              $sql .= "($column>=$time) ";
1143        }
1144        elseif ($timerange[0]{0} == '<') {
1145           $time=strtotime(substr($timerange[0],1));
1146           if ($time>0)
1147              $sql .= "($column<=$time) ";
1148        }
1149        else { // we give a search range of one day
1150           $searchrange=86400;
1151           $starttime=strtotime($timerange[0]);
1152           $endtime=$starttime+$searchrange;
1153              $sql .= "($column>=$starttime AND $column<=$endtime) ";
1154        }
1155  
1156     }
1157     if (sizeof($timerange) == 2) {
1158        $starttime=strtotime($timerange[0]);
1159        $endtime=strtotime($timerange[1]);
1160        if ($starttime > 0 && $endtime > 0)
1161           $sql .= "($column>=$starttime AND $column<=$endtime) ";
1162     }
1163     return "$and ($sql) ";
1164  }
1165  
1166  
1167  /**
1168   *  Helper function for search
1169   *
1170   * Interprets fields the right way
1171   */
1172  function searchhelp ($db,$tableinfo,$column,&$columnvalues,$query,$wcappend,$and) {
1173     // first get the specs on this column
1174     $rc=$db->Execute("SELECT type,datatype,associated_table,key_table,associated_column,associated_local_key FROM ".$tableinfo->desname." WHERE columnname='$column'");
1175     // get the left joins in the sql statement right
1176     if ($rc->fields[1]=='table') {
1177        $rtableoftables=$db->Execute("SELECT real_tablename,table_desc_name,id FROM tableoftables WHERE id={$rc->fields['associated_table']}");
1178        $rtdesc=$db->Execute("SELECT columnname,datatype,type FROM {$rtableoftables->fields[1]} WHERE id='{$rc->fields['associated_column']}'");
1179        $tablecolumnvalues[$rtdesc->fields[0]]=$columnvalues[$column];
1180        $asstableinfo=new tableinfo($db,false,$rtableoftables->fields[2]);
1181        // find the key and the column it relates to
1182        if ($rc->fields['associated_local_key']) {
1183           $rasslk=$db->Execute("SELECT columnname,associated_column FROM {$tableinfo->desname} WHERE id={$rc->fields['associated_local_key']}");
1184           $associated_local_key=$rasslk->fields[0];
1185           $rtdesc2=$db->Execute("SELECT columnname,datatype,type FROM {$asstableinfo->desname} WHERE id={$rasslk->fields[1]}");
1186           $foreign_key=$rtdesc2->fields[0];
1187        }
1188        else {
1189           $associated_local_key=$column;
1190           $foreign_key=$rtdesc->fields[0];
1191        }
1192        // check whether we already have this table as a join:
1193        // postgres does not like the same join twice, this also dictates that we can not use multiple foreign keys in a table
1194        if (!strstr($query[1],$asstableinfo->realname))
1195           // although it looks like we link to another column, we really use the id of the associated table only
1196           $query[1].= "LEFT JOIN {$asstableinfo->realname} ON {$tableinfo->realname}.$associated_local_key={$asstableinfo->realname}.id ";
1197        // for nested structure: recursively call searchhelp, this will also yield the real sort or search statement we are inetersted in and will make all nested joints
1198        $table_where=searchhelp($db,$asstableinfo,$rtdesc->fields[0],$tablecolumnvalues,false,$wcappend,false);
1199        // this can again lead to joining to the same table twice: check!
1200        if (! strstr($query[1],$table_where[1])) {
1201           $query[1].=$table_where[1];
1202        }
1203        // we should always propagate the WHERE clause
1204        if ($table_where[2]) {
1205           $query[2].=$and.' '.$table_where[2];
1206        }
1207     }
1208     //consider other columns only when there is a search value.  These will only contribute to the WHERE part, not to the FROM part
1209     if ($columnvalues[$column]) {
1210        $query[5]=true;
1211        if ($column=='ownerid') {
1212           $query[2].= "$and {$tableinfo->realname}.ownerid={$columnvalues[$column]} ";
1213        }
1214        else {
1215           $query[5]=true;
1216           if ($rc->fields[1]=='file' && $rc->fields[2]) {
1217              // we append a wild-card here, not necessarily a cool idea 
1218              $rw=$db->Execute("SELECT id FROM words WHERE word LIKE '".strtolower($columnvalues[$column])."%'");
1219              if ($rw && $rw->fields[0]) {
1220                 $rid=$db->Execute("SELECT DISTINCT recordid AS id FROM {$rc->fields[2]} WHERE wordid='{$rw->fields[0]}'");
1221                 if ($rid && $rid->fields[0]) {
1222                    $list=make_SQL_ids($rid,$list);
1223                    $query[2].="$and {$tableinfo->realname}.id IN ($list) ";   
1224                    /* This resulst in very slow SQL execution
1225                    $query[1].="LEFT JOIN {$rc->fields[2]} ON {$tableinfo->realname}.id={$rc->fields[2]}.recordid ";
1226                    $query[2].="$and {$rc->fields[2]}.wordid='{$rw->fields[0]}' ";
1227                    */
1228                 }
1229             else $query[2].="$and id=0 ";
1230              }
1231          else $query[2].="$and id=0 ";
1232           }
1233           elseif ($rc->fields[1]=='table') {
1234           }
1235           // there are some (old) cases where pulldowns are of type text...
1236           elseif ($rc->fields[1]=='pulldown') {
1237              $columnvalues[$column]=(int)$columnvalues[$column];
1238              if ($columnvalues["$column"]==-1)
1239                 $query[2].="$and ({$tableinfo->realname}.$column='' OR {$tableinfo->realname}.$column IS NULL) ";
1240              else
1241                 $query[2].="$and {$tableinfo->realname}.$column='$columnvalues[$column]' ";
1242           }
1243           elseif ($rc->fields[1]=='mpulldown') {
1244              // emulate a logical AND between values selected in a mpulldown
1245              unset ($id_list);
1246              // keep the code to deal with single selects and multiple selects
1247              if (is_array($columnvalues)) {
1248                 unset($id_list);
1249                 $j=0;
1250                 // read in values from types tables and arrange in groups
1251                 foreach($columnvalues[$column] as $typeid) {
1252                    $rl=$db->Execute("SELECT recordid FROM {$rc->fields[3]} WHERE typeid=$typeid");
1253                    while ($rl && !$rl->EOF) {
1254                       $id_list[$j].=$rl->fields[0].',';
1255                       $rl->MoveNext();
1256                    }
1257                    $id_list[$j]=substr($id_list[$j],0,-1);
1258                    // if nothing is found we'll pass an impossible id value
1259                    if (strlen($id_list[$j]) <1)
1260                       $id_list[$j]='-1';
1261                    $j++;
1262                 }
1263              }
1264              else {  // for 'single' selects
1265                 $rmp=$db->Execute("SELECT recordid FROM {$rc->fields[3]} WHERE typeid='{$columnvalues[$column]}'");
1266                 if ($rmp) {
1267                    $id_list=$rmp->fields[0];
1268                    $rmp->MoveNext();
1269                    while (!$rmp->EOF) {
1270                       $id_list.=",{$rmp->fields[0]}";
1271                       $rmp->MoveNext();
1272                    }
1273                }
1274              }
1275              // pass the multiple lists to the main query
1276              if (is_array($id_list)) {
1277                 foreach ($id_list as $list) {
1278                    if (!$listfound) {
1279                       $query[2].="$and ( {$tableinfo->realname}.id IN ($list) ";
1280                       $listfound=true;
1281                    }
1282                    else
1283                       $query[2].="OR {$tableinfo->realname}.id IN ($list) ";
1284                 }
1285             $query[2] .= ')';
1286                 // we should not be able to get here:
1287                 if (!$listfound)
1288                    $query[2].="$and {$tableinfo->realname}.id IN (-1) ";
1289              }
1290              elseif ($id_list) // for 'single' selects
1291                 $query[2].="$and {$tableinfo->realname}.id IN ($id_list) ";
1292              else // nothing found, make sure we do not crash the search statement
1293                 $query[2].="$and {$tableinfo->realname}.id IN (-1) ";
1294                 
1295           }
1296           // since all tables now have desc. tables,we can check for int/floats
1297           // should probably do this more upstream for performance gain
1298           elseif ($rc->fields[1]=='date') {
1299              $query[2].= datetoSQL($columnvalues[$column],$column,$and);
1300           }
1301           elseif (substr($rc->fields[0],0,3)=='int') {
1302              $query[2].=numerictoSQL ($tableinfo,$columnvalues[$column],$column,'int',$and); 
1303           }
1304           elseif (substr($rc->fields[0],0,5)=='float') {
1305              $query[2].=numerictoSQL ($tableinfo,$columnvalues[$column],$column,'float',$and); 
1306           }
1307           else {
1308              $columnvalues[$column]=trim($columnvalues[$column]);
1309              $columnvalue=$columnvalues[$column];
1310              $columnvalue=str_replace('*','%',$columnvalue);
1311              // support logical AND and OR
1312              $token_array=explode(' ',$columnvalue);
1313              // this is ugly stuff, there must be a neater way of parsing
1314              foreach($token_array as $token) {
1315                 $count++;
1316                 if ($nextand)
1317                    $and=$nextand;
1318                 if (($token=='AND' || $token=='OR') && $count>1) {
1319                     $nextand=$token;
1320                     $booleanfound=true;
1321                 }
1322                 else {
1323                     $booleanfound=false;
1324                     $cvalue.=$token.' ';
1325                 }
1326                 if ($booleanfound || $count >= sizeof($token_array)) {
1327                    $cvalue=trim($cvalue);
1328                    if ($wcappend)
1329                       $cvalue="%$cvalue%";
1330                    $query[2].="$and UPPER({$tableinfo->realname}.$column) LIKE UPPER('$cvalue') ";
1331                 }
1332              }
1333           }
1334        }
1335     }
1336     return $query;
1337  }
1338  
1339  /**
1340   *  Returns an SQL search statement
1341   *
1342   * The whereclause should NOT start with WHERE
1343   * The whereclause should contain the output of may_read_SQL and
1344   * can also be used for sorting
1345   */
1346  function search ($db,$tableinfo,$fields,&$fieldvalues,$whereclause=false,$wcappend=true) 
1347  {
1348     global $db_type;
1349  
1350     $columnvalues=$fieldvalues;
1351  /* It seems we getter better performance by only asking for id
1352     keep this code here to revert to in case of side-effetcs
1353  
1354     // change fields into a SQL string that works with multiple tables
1355     $fieldsarray=explode(',',$fields);
1356     foreach ($fieldsarray as $field) 
1357        $fieldsSQLstring.=$tableinfo->realname.".$field AS $field, ";
1358     $fieldsSQLstring=substr($fieldsSQLstring,0,-2);
1359     // SELECT part
1360     //$query[0]="SELECT $fieldsSQLstring "; //FROM ".$tableinfo->realname." WHERE ";
1361  */
1362     // SELECT part
1363     $query[0]="SELECT {$tableinfo->realname}.id ";
1364     // FROM part
1365     if ($db_type=='mysql') {
1366        $query[1]='FROM '.$tableinfo->realname.' ';
1367     } else { // non sql databases use tempb table to determine whether user may read a record. Newer Postgres database need tempb in the SQL statement
1368        $query[1]='FROM tempb, '.$tableinfo->realname.' ';
1369     }
1370     // WHERE part
1371     $query[2]='WHERE ';
1372     // flag telling whether WHERE field already contains a statement
1373     $query[5]=false;
1374     $column=strtok($fields,',');
1375     while ($column) { 
1376        if ($query[5])
1377           $query=searchhelp ($db,$tableinfo,$column,$columnvalues,$query,$wcappend,"AND");
1378        else
1379           $query=searchhelp ($db,$tableinfo,$column,$columnvalues,$query,$wcappend,$and);
1380        $column=strtok (',');
1381     }
1382  
1383     /*
1384     * Ugly hack needed here.  Go through the sort string (in whereclause)
1385     * and if there is there an assist tabel in there, we need to make a left join
1386     * There must be a nicer way to figure this all out.
1387     */
1388     if ($whereclause) {
1389        // figure out if there assist tables in the ORDER By part
1390        $words=preg_split('/ /',$whereclause);
1391        $counter=0;
1392        foreach ($words as $word) {
1393           $parts=explode('_',$word);
1394           // Second part of the if is for the old style naming of assist tables...
1395           if ( (substr($parts[1],-3) == 'ass') || (substr($parts[0],0,3)=='ass') ) {
1396              // found assist table so add LEFT JOIN
1397              $dotparts=explode('.',$word);
1398              $query[1] .= 'LEFT JOIN '. $dotparts[0] . " ON {$dotparts[0]}.id={$tableinfo->realname}.$dotparts[1] ";
1399              // we'll also need to clean up the order by string
1400              $direction='asc';
1401              for ($j=1; $j<4; $j++) {
1402                 if (substr($words[$counter+$j],0,3)=='asc') {
1403                    $direction='asc';
1404                    break;
1405                 } elseif (substr($words[$counter+$j],0,4)=='desc') {
1406                    $direction='desc';
1407                    break;
1408                 }
1409              }
1410              $newwhereclause .= $dotparts[0] . '.sortkey '. $direction . ', ' . $dotparts[0] . '.type ';  // the last asc/desc will be added in the next loop (below)
1411           } else {
1412              $newwhereclause .= $word . ' ';
1413           }
1414           $counter++;
1415        }
1416        $whereclause = $newwhereclause;
1417  
1418        if ($query[5])
1419           $query[2] .= "AND $whereclause";
1420        else
1421           $query[2] .= $whereclause;
1422     }
1423     if (function_exists('plugin_search'))
1424        $query[0]=plugin_search($query[0],$columnvalues,$query[1]);
1425     $result=$query[0].$query[1].$query[2];
1426     //echo "$result.<br>";
1427     return $result;
1428  }
1429  
1430  
1431  /**
1432   *   sets AtFirstPage and AtLastPage
1433   *
1434   */
1435  function first_last_page (&$r,&$current_page,$r_p_p,$numrows) {
1436     // protect against pushing the reload button while at the last page
1437     if ( (($current_page-1) * $r_p_p) >= $numrows)
1438        $current_page -=1;
1439     // if we are still outof range, this must be a new search statement and we can go to page 1
1440     if ( (($current_page-1) * $r_p_p) >= $numrows)
1441        $current_page =1;
1442  
1443     if ($current_page < 2)
1444        $r->AtFirstPage=true;
1445     else
1446        $r->AtFirstPage=false;
1447     if ( ($current_page * $r_p_p) >= $numrows)
1448        $r->AtLastPage=true;
1449     else
1450        $r->AtLastPage=false;
1451  }
1452  
1453  /**
1454   *  Displays the next and previous buttons
1455   *
1456   * $r is the result of a $db->Execute query used to display the table with records
1457   * When $paging is true, the records per page field will also be displayed
1458   * $num_p_r holds the (global) records per page variable
1459   */
1460  function next_previous_buttons($r,$paging=false,$num_p_r=false,$numrows=false,$pagenr=false,$db=false,$tableinfo=false,$viewid=false) {
1461     echo "<table border=0 width='100%'>\n<tr>\n<td align='left'>";
1462     if (function_exists($r->AtFirstPage))
1463        $r->AtFirstPage=$r->AtFirstPage();
1464     if ($r && !$r->AtFirstPage)
1465        echo "<input type=\"submit\" name=\"previous\" value=\"Previous\"></td>\n";
1466     else
1467        if ($paging)
1468           echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n";
1469        else
1470           echo "&nbsp;</td>\n";
1471     if ($db && $tableinfo)
1472        show_reports($db,$tableinfo,false,$viewid);
1473     if ($paging) {
1474        if ($numrows>0) {
1475           echo "<td align='center'>$numrows Records found. ";
1476           if ($pagenr) {
1477              $start=($pagenr-1)*$num_p_r+1;
1478              $end=$pagenr*$num_p_r;
1479              if ($end > $numrows)
1480                 $end=$numrows;
1481              echo "Showing $start through $end. ";
1482           }
1483           echo "</td>\n";
1484        }
1485        else
1486           echo "<td align='center'>No records found. </td>\n";
1487        echo "<td align='center'>\n";
1488       echo "<input type='text' name='num_p_r'value='$num_p_r' size=3>&nbsp;";
1489        echo "Records per page</td>\n";
1490     }
1491     echo "<td align='right'>";
1492  
1493     // add direct links to pages
1494     if ($pagenr) {
1495        $startp=$pagenr-5;
1496        if ($startp<1)
1497           $startp=1;
1498        $endp=$startp+9;
1499        if ($numrows) {
1500           if ($numrows < ($endp*$num_p_r)) {
1501              $endp= ceil($numrows/$num_p_r);
1502           }
1503        }
1504  
1505        if ($endp > 1) {
1506           echo "Goto page: ";
1507           echo "<input type='hidden' name='{$tableinfo->pagename}' value='0'>\n";     
1508           for ($i=$startp; $i<=$endp; $i++) {
1509              if ($pagenr==$i)
1510                 echo "<b>$i </b>";
1511              else
1512              // try using links with javascript converting it into post variables
1513                 echo "<a href='javascript:document.g_form.{$tableinfo->pagename}.value=\"$i\"; document.g_form.searchj.value=\"Search\"; document.g_form.submit()'>$i </a>";
1514           }
1515        }
1516     }
1517       
1518     if (function_exists($r->AtLastPage))
1519        $r->AtLastPage=$r->AtLastPage();
1520     if ($r && !$r->AtLastPage)
1521        echo "<input type=\"submit\" name=\"next\" value=\"Next\">\n";
1522     else
1523        if ($paging)
1524           echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
1525        else
1526           echo "&nbsp;";
1527     echo "</td>\n</tr>\n";
1528     echo "</table>\n";
1529  }
1530  
1531  /**
1532   *  Returns the variable $num_p_r holding the # of records per page
1533   *
1534   * check user settings and GET_VARS
1535   * Write the value back to the user defaults
1536   * When no value is found, default to 10
1537   */
1538  function paging ($num_p_r,&$USER) {
1539     if (!$num_p_r)
1540        $num_p_r=$USER['settings']['num_p_r'];
1541     if (isset($_GET['num_p_r']))
1542        $num_p_r=$_GET['num_p_r'];
1543     if (!isset($num_p_r))
1544       $num_p_r=10;
1545     $USER['settings']['num_p_r']=$num_p_r;
1546     return $num_p_r;
1547  }
1548  
1549  /**
1550   *  Returns current page
1551   *
1552   * current page is table specific, therefore
1553   * The variable name is formed using the short name for the table
1554   */
1555  function current_page($curr_page, $sname, $num_p_r, $numrows) {
1556     // damage control: if settings are absent, $num_p_rt will be 0
1557     if (!$num_p_r) {
1558        $num_p_r=10;
1559     }
1560     $varname=$sname.'_curr_page';
1561     ${$varname}=$curr_page;
1562  
1563     if (!isset($$varname))
1564        ${$varname}=$_SESSION[$varname];
1565     // if the current page is out of bound, we'll reset it to 1
1566     if (${$varname} > ($numrows/$num_p_r))
1567        ${$varname}=1;
1568     // the page number can be set directly or by clicking the next/previous buttons (see function next_previous_buttons)
1569     if ($_GET[$varname]) {
1570        ${$varname}=$_GET[$varname];
1571     }
1572     if (isset($_GET['next'])) {
1573        ${$varname}+=1;
1574     }
1575     elseif (isset($_GET['previous'])) {
1576        $$varname-=1;
1577     }
1578     if ($$varname<1)
1579        $$varname=1;
1580     $_SESSION[$varname]=${$varname}; 
1581     //session_register($varname);
1582     return ${$varname};
1583  }
1584  
1585  /**
1586   *  Assembles the search SQL statement and remembers it in _SESSION
1587   *
1588   */
1589  function make_search_SQL($db,$tableinfo,$fields,$USER,$search,$searchsort,$whereclause=false) {
1590     global $db_type;
1591  
1592     // apparently searchsort can be passed as an empty string.  that is bad
1593     if (!$searchsort)
1594        $searchsort=$tableinfo->realname.'.date DESC';
1595     $fieldvarsname=$tableinfo->short.'_fieldvars';
1596     global ${$fieldvarsname};
1597     $queryname=$tableinfo->short.'_query';
1598     if (!$whereclause)
1599        $whereclause=may_read_SQL ($db,$tableinfo,$USER);
1600     if (!$whereclause)
1601        $whereclause=-1;
1602     if ($search=='Search') {
1603        ${$queryname}=search($db,$tableinfo,$fields,$_GET," $whereclause ORDER BY $searchsort");
1604        ${$fieldvarsname}=$_GET;
1605     }
1606     elseif (session_is_registered ($queryname) && isset($_SESSION[$queryname])) {
1607        ${$queryname}=$_SESSION[$queryname];
1608        ${$fieldvarsname}=$_SESSION[$fieldvarsname];
1609     } else {
1610        // This must be a 'Show All'
1611        // terrible: some postgres version need the temp table in the FROM clause:
1612        if ($db_type=='mysql') {
1613           ${$queryname} = "SELECT $fields FROM $tableinfo->realname WHERE $whereclause ORDER BY date DESC";
1614        } else {
1615           ${$queryname} = "SELECT $fields FROM tempb, $tableinfo->realname WHERE $whereclause ORDER BY date DESC";
1616        }
1617        ${$fieldvarsname}=$_GET;
1618     }
1619     $_SESSION[$queryname]=${$queryname};   
1620     //session_register($queryname);
1621     if (!${$fieldvarsname})
1622        ${$fieldvarsname}=$_GET;
1623     $_SESSION[$fieldvarsname]=${$fieldvarsname};   
1624     //session_register($fieldvarsname);
1625  
1626     if ($search !='Show All') {
1627        // globalize _GET 
1628        $column=strtok($fields,',');
1629        while ($column) {
1630           global ${$column};
1631           ${$column}=$_GET[$column];
1632           $column=strtok(',');
1633        }
1634        // extract variables from session
1635        globalize_vars ($fields, ${$fieldvarsname});
1636     }
1637     // do one last error control: replace double commas with singles
1638     ${$queryname}=preg_replace("/,,/",",",${$queryname});
1639     return ${$queryname};
1640  }
1641  
1642  
1643  /**
1644   *  Checks whether a user has access to a given table
1645   *
1646   *
1647   */
1648  function may_see_table($db,$USER,$tableid) {
1649     include  ('./includes/defines_inc.php');
1650     // Sysadmin may see it all
1651     if ($USER['permissions'] & $SUPER)
1652        return true;
1653     $group_list=$USER['group_list'];
1654     $r=$db->Execute ("SELECT tableid FROM groupxtable_display WHERE groupid IN ($group_list) AND tableid='$tableid'");
1655     if ($r && !$r->EOF)
1656        return true;
1657     else
1658        return false;
1659  }
1660  
1661  ?>

title

Description

title

Description

title

Description

title

title

Body