/includes/ -> tablemanage_inc.php
1 <?php
2
3 // tablemanage_inc.php - support functions dealing with defining tables
4 // tablemanage_inc.php - author: Ethan Garner, Nico Stuurman <nicost@sf.net>
5 /***************************************************************************
6 * Copyright (c) 2001 by Ethan Garner, Nico Stuurman *
7 * ------------------------------------------------------------------------ *
8 * Part of phplabware, a web-driven groupware suite for research labs *
9 * This file contains classes and functions needed in tablemanage.php. *
10 * *
11 * This program is free software; you can redistribute it and/or modify it *
12 * under the terms of the GNU General Public License as published by the *
13 * Free Software Foundation; either version 2 of the License, or (at your *
14 * option) any later version. *
15 \**************************************************************************/
16
17
18 /////////////////////////////////////////////////////////////////////
19 ////
20 // !Prints form with access to table management functions
21 function create_new_table($db){
22 global $HTTP_POST_VARS,$PHP_SELF;
23 echo "<form method='post' id='tablemanage' enctype='multipart/form-data' ";
24 $dbstring=$PHP_SELF;
25 echo "action='$dbstring".SID."'>\n";
26 echo "<table align='center'>\n";
27 echo "<caption><h3>Edit tables</h3></caption>\n";
28 echo "<tr>\n";
29 echo "<th>Name</th>";
30 echo "<th>Display</th>";
31 echo "<th>Sort key</th>\n";
32 echo "<th>Custom</th>\n";
33 echo "<th>Action</th>\n";
34 echo "</tr>\n";
35 echo "<tr><td><input type='text' name='table_name' value=''></td>\n";
36 echo "<td><input type='text' name='table_display' value=''></td>\n";
37 echo "<td><input type='text' name='table_sortkey' value=''></td>\n";
38 echo "<td><input type='text' name='table_custom' value=''></td>\n";
39 echo "<td align='center'><input type='submit' name='add_table' value='Add'></td></tr>\n";
40
41 $query = "SELECT id,tablename,display,sortkey,custom FROM tableoftables ORDER BY sortkey";
42 $r=$db->Execute($query);
43 $rownr=0;
44 // print all entries
45 while (!($r->EOF) && $r) {
46 // get results of each row
47 $id = $r->fields["id"];
48 $name = $r->fields["type"];
49 $Display = $r->fields["display"];
50 $sortkey = $r->fields["sortkey"];
51 $Custom = $r->fields["custom"];
52
53 // print start of row of selected group
54 if ($rownr % 2)
55 echo "<tr class='row_odd' align='center'>\n";
56 else
57 echo "<tr class='row_even' align='center'>\n";
58 echo "<input type='hidden' name='type_id[]' value='$id'>\n";
59 echo "<td><input type='text' name='table_name[]' value='$name'></td>\n";
60 echo "<td><input type='text' name='table_display[]' value='$Display'></td>\n";
61 echo "<td><input type='text' name='type_sortkey[]' value='$sortkey'></td>\n";
62 echo "<td><input type='text' name='table_custom[]' value='$Custom'></td>\n";
63
64 $modstring = "<input type='submit' name='tamod"."_$rownr' value='Modify'>";
65 $delstring = "<input type='submit' name='tadel"."_$rownr' value='Remove' ";
66 $delstring .= "Onclick=\"if(confirm('Are you sure the $name \'$type\' ";
67 $delstring .= "should be removed?')){return true;}return false;\">";
68 echo "<td align='center'>$modstring $delstring</td>\n";
69 echo "</tr>\n";
70 $r->MoveNext();
71 $rownr+=1;
72 }
73
74 // Dismiss button
75 echo "<tr><td colspan=4 align='center'>\n";
76 echo "<input type='submit' name='submit' value='Dismiss'>\n";
77 echo "</td></tr>\n";
78
79 echo "</table>\n";
80 echo "</form>\n";
81
82 }
83
84 /////////////////////////////////////////////////////////////////////////
85 ////
86 // !deletes a user-generated table, including associated tables
87 function del_table($db,$tablename,$id,$USER) {
88 global $HTTP_POST_VARS, $string;
89
90 $real_tablename=get_cell($db,"tableoftables","real_tablename","id",$id);
91 $desc=$real_tablename."_desc";
92 // delete files owned by this table
93 $r=$db->Execute("SELECT id FROM files WHERE tablesfk='$id'");
94 while (!$r->EOF) {
95 delete_file ($db,$r->fields["id"],$USER);
96 $r->MoveNext();
97 }
98 $r=$db->Execute("select associated_table from $desc");
99 $tempTAB=array();
100 if ($r) {
101 while (!$r->EOF) {
102 if ($r->fields["associated_table"]) {
103 $db->Execute("DROP TABLE ".$r->fields["associated_table"]);
104 $db->Execute("DROP TABLE ".$r->fields["associated_table"]."_id_seq");
105 $db->Execute("DROP SEQUENCE ".$r->fields["associated_table"]."_id_seq");
106 }
107 $r->MoveNext();
108 }
109 }
110 $r=$db->Execute("DROP TABLE $real_tablename");
111 $r=$db->Execute("DROP TABLE $real_tablename"."ass");
112 $r=$db->Execute("DROP SEQUENCE $real_tablename"."ass");
113 $r=$db->Execute("DROP TABLE $real_tablename"."_id_seq");
114 $r=$db->Execute("DROP SEQUENCE $real_tablename"."_id_seq");
115 $r=$db->Execute("DROP TABLE $desc");
116 $r=$db->Execute("DROP TABLE $desc"."_id");
117 $r=$db->Execute("DROP SEQUENCE $desc"."_id");
118 $r=$db->Execute("DELETE FROM groupxtabledisplay WHERE tableid=$id");
119 $r=$db->Execute("Delete from tableoftables WHERE id=$id");
120 if ($r)
121 $string="Table $tablename has been deleted";
122 return $string;
123 }
124
125 /////////////////////////////////////////////////////////////////////////
126 ////
127 // !creates a general table
128 // also adds the tabledescription table and the entry in tableoftables
129 function add_table ($db,$tablename,$tablelabel,$sortkey,$plugincode) {
130 global $string;
131 $shortname=substr($tablename,0,3);
132
133 //check to ensure that duplicate table or database does not exist
134 $r=$db->Execute("SELECT tablename FROM tableoftables");
135 while ($r && !$r->EOF) {
136 if ($tablename==$r->fields["tablename"])
137 $isbad=true;
138 $r->MoveNext();
139 }
140 if ($tablename=="")
141 $string="Please enter a title for the table!";
142 if ($isbad)
143 $string="A table with the name $tablename already exists!";
144 if (preg_match("/\W/",$tablename)) {
145 $string="Please use only letters (no numbers, spaces and the like) in the tablename.";
146 $isbad=true;
147 }
148 if (preg_match("/^[0-9]/",$tablename)) {
149 $string="Tablenames should not start with a number. Sorry ;(";
150 $isbad=true;
151 }
152 if (!$isbad && $tablename) {
153 // ids > 10000 are available to users
154 $id=$db->GenID("tableoftables"."_gen_id_seq",10000);
155 $real_tablename=$tablename."_".$id;
156 $desc=$real_tablename . "_desc";
157 $r=$db->Execute("CREATE TABLE $real_tablename (
158 id int PRIMARY KEY,
159 title text,
160 gr smallint,
161 gw smallint,
162 er smallint,
163 ew smallint,
164 ownerid int,
165 magic int,
166 lastmodby int,
167 lastmoddate int,
168 date int)");
169 if ($r) {
170 $string= "Succesfully Added Table $tablename";
171 $db->Execute("CREATE INDEX $real_tablename"."_id_index ON $real_tablename (id)");
172 $db->Execute("CREATE INDEX $real_tablename"."_title_index ON $real_tablename (title)");
173 $db->Execute("CREATE INDEX $real_tablename"."_title_index ON $real_tablename (title(10))");
174 $db->Execute("CREATE INDEX $real_tablename"."_gr_index ON $real_tablename (gr)");
175 $db->Execute("CREATE INDEX $real_tablename"."_gw_index ON $real_tablename (gw)");
176 $db->Execute("CREATE INDEX $real_tablename"."_er_index ON $real_tablename (er)");
177 $db->Execute("CREATE INDEX $real_tablename"."_ew_index ON $real_tablename (ew)");
178 $db->Execute("CREATE INDEX $real_tablename"."_ownerid_index ON $real_tablename (ownerid)");
179 $db->Execute("CREATE INDEX $real_tablename"."_date_index ON $real_tablename (date)");
180 // check if shortname has been taken, if so, add id
181 $r=$db->Execute("SELECT id FROM tableoftables WHERE shortname='$shortname'");
182 if ($r->fields["id"])
183 $shortname.="$id";
184 if ($plugincode) $plugincode="'".$plugincode."'";
185 else $plugincode="NULL";
186 $sortkey=(int)$sortkey;
187
188 $r=$db->Execute("INSERT INTO tableoftables (id,sortkey,tablename,label,real_tablename,shortname,display,permission,table_desc_name,plugin_code) Values($id,'$sortkey','$tablename','$tablelabel','$real_tablename','$shortname','Y','Users','$desc',$plugincode)");
189 // let all groups see the table by default
190 $rg=$db->Execute("SELECT id FROM groups");
191 while ($rg && !$rg->EOF) {
192 $db->Execute("INSERT INTO groupxtable_display VALUES ('".$rg->fields["id"]."','$id')");
193 $rg->MoveNext();
194 }
195 $label=strtr($label,",'"," ");
196 $r=$db->Execute("CREATE TABLE $desc (
197 id int PRIMARY KEY,
198 sortkey int,
199 label text,
200 columnname text,
201 display_table char(1),
202 display_record char(1),
203 required char(1),
204 type text,
205 datatype text,
206 associated_table text,
207 associated_column text,
208 associated_local_key text,
209 key_table text,
210 thumb_x_size int,
211 thumb_y_size int,
212 link_first text,
213 link_last text,
214 modifiable char(1))");
215
216 $fieldstring="id,label,columnname,sortkey,display_table,display_record, required, type, datatype, associated_table, associated_column";
217 $descid=$db->GenId("$desc"."_id");
218 $db->Execute("INSERT INTO $desc ($fieldstring) Values($descid,'id','id','100','N','N','N','int(11)','text',NULL,NULL)");
219 $descid=$db->GenId("$desc"."_id");
220 $db->Execute("INSERT INTO $desc ($fieldstring) Values($descid,'group read','gr','111','N','N','N','smallint','int',NULL,NULL)");
221 $descid=$db->GenId("$desc"."_id");
222 $db->Execute("INSERT INTO $desc ($fieldstring) Values($descid,'group write','gw','112','N','N','N','smallint','int',NULL,NULL)");
223 $descid=$db->GenId("$desc"."_id");
224 $db->Execute("INSERT INTO $desc ($fieldstring) Values($descid,'everyone read','er','113','N','N','N','smallint','int',NULL,NULL)");
225 $descid=$db->GenId("$desc"."_id");
226 $db->Execute("INSERT INTO $desc ($fieldstring) Values($descid,'everyone write','ew','114','N','N','N','smallint','int',NULL,NULL)");
227 $descid=$db->GenId("$desc"."_id");
228 $db->Execute("INSERT INTO $desc ($fieldstring) Values($descid,'owner','ownerid','120','N','N','N','int(11)','user',NULL,NULL)");
229 $descid=$db->GenId("$desc"."_id");
230 $db->Execute("INSERT INTO $desc ($fieldstring) Values($descid,'magic','magic','130','N','N','N','int(11)','text',NULL,NULL)");
231 $descid=$db->GenId("$desc"."_id");
232 $db->Execute("INSERT INTO $desc ($fieldstring,modifiable) Values($descid,'title','title','140','Y','Y','Y','text','text',NULL,NULL,'Y')");
233 $descid=$db->GenId("$desc"."_id");
234 $db->Execute("INSERT INTO $desc ($fieldstring) Values($descid,'lastmoddate','lastmoddate','150','N','N','N','int(11)','date',NULL,NULL)");
235 $descid=$db->GenId("$desc"."_id");
236 $db->Execute("INSERT INTO $desc ($fieldstring) Values($descid,'lastmodby','lastmodby','160','N','N','N','int(11)','user',NULL,NULL)");
237 $descid=$db->GenId("$desc"."_id");
238 $db->Execute("INSERT INTO $desc ($fieldstring) Values($descid,'date','date','170','N','N','N','int(11)','date',NULL,NULL)");
239 }
240 else {
241 $string="Poblems adding this table. Sorry ;(";
242 }
243 return false;
244 }
245 }
246
247 /////////////////////////////////////////////////////////////////////////
248 ////
249 // !modifies the display properites of a table within navbar
250 function mod_table($db,$id,$offset) {
251 global $HTTP_POST_VARS,$string;
252
253 // prepare variable to feed into SQL statement
254 if ($HTTP_POST_VARS["table_name"][$offset])
255 $tablename="'".$HTTP_POST_VARS["table_name"][$offset]."'";
256 else
257 $tablename="NULL";
258 if ($HTTP_POST_VARS["table_label"][$offset])
259 $label="'".strtr($HTTP_POST_VARS["table_label"][$offset],",'"," ")."'";
260 else
261 $label="NULL";
262 $tablesort=(int) $HTTP_POST_VARS["table_sortkey"][$offset];
263 $tabledisplay= $HTTP_POST_VARS["table_display"][$offset];
264 $tablegroups= $HTTP_POST_VARS["tablexgroups"][$id];
265 if ($HTTP_POST_VARS["table_plugincode"][$offset])
266 $plugincode="'".$HTTP_POST_VARS["table_plugincode"][$offset]."'";
267 else
268 $plugincode="NULL";
269
270 // do the SQL update
271 $r=$db->Execute("UPDATE tableoftables SET sortkey='$tablesort',display='$tabledisplay',label=$label,plugin_code=$plugincode where id='$id'");
272 if ($r) {
273 // Set permissions for groups to see these tables
274 $db->Execute("DELETE FROM groupxtable_display WHERE tableid='$id'");
275 if ($tablegroups) {
276 foreach ($tablegroups AS $groupid)
277 if ($groupid)
278 $db->Execute("INSERT into groupxtable_display VALUES ('$groupid','$id')");
279 }
280 $string="Succesfully Changed Record $tablename";
281 }
282 else
283 $string="Please enter all fields";
284 return false;
285 }
286
287 /////////////////////////////////////////////////////////////////////////
288 ////
289 // !adds a general column entry
290 function add_columnecg($db,$tablename2,$colname2,$label,$datatype,$Rdis,$Tdis,$req,$modifiable,$sort) {
291 global $string;
292
293 $SQL_reserved=",absolute,action,add,allocate,alter,are,assertion,at,between,bit,bit_length,both,cascade,cascaded,case,cast,catalog,char_length,charachter_length,cluster,coalsce,collate,collation,column,connect,connection,constraint,constraints,convert,corresponding,cross,current_date,current_time,current_timestamp,current_user,date,day,deallocate,deferrrable,deferred,describe,descriptor,diagnostics,disconnect,domain,drop,else,end-exec,except,exception,execute,external,extract,false,first,full,get,global,hour,identity,immediate,initially,inner,input,insensitive,intersect,interval,isolation,join,last,leading,left,level,local,lower,match,minute,month,names,national,natural,nchar,next,no,nullif,octet_length,only,outer,output,overlaps,pad,partial,position,prepare,preserve,prior,read,relative,restrict,revoke,right,rows,scroll,second,session,session_user,size,space,sqlstate,substring,system_user,temporary,then,time,timepstamp,timezone_hour,timezone_minute,trailing,transaction,translate,translation,trim,true,unknown,uppper,usage,using,value,varchar,varying,when,write,year,zone,";
294
295 // find the id of the table and therewith the tablename
296 $r=$db->Execute("SELECT id,real_tablename,table_desc_name,label,shortname FROM tableoftables WHERE tablename='$tablename2'");
297 $id=$r->fields['id'];
298 $real_tablename=$r->fields['real_tablename'];
299 $desc=$r->fields['table_desc_name'];
300 $tablelabel=$r->fields['label'];
301 $shortname=$r->fields['label'];
302 $search=array("' '","','","';'","'\"'","'_'","'-'");
303 $replace=array('');
304 $colname=preg_replace ($search,$replace, $colname2);
305 if (!$sort)
306 $sort='0';
307
308 // for adodb's dml
309 $dict=NewDataDictionary($db);
310 $taboptArray=array('mysql'=>'TYPE=ISAM');
311
312 $fieldstring="id,columnname,label,sortkey,display_table,display_record,required,modifiable,type,datatype,associated_table,associated_column,key_table";
313 $fieldid=$db->GenId($desc."_id");
314 $label=strtr($label,",'"," ");
315 $colname=strtolower($colname);
316
317 // check whether this name exists, the query should fail
318 $rb=$db->Execute("SELECT $colname FROM $real_tablename");
319 if ($rb)
320 $string=('This columnname is in use. Please choose something else.');
321 elseif ($colname=="")
322 $string='Please enter a columnname';
323 elseif ($label=='')
324 $string='Please enter a Label';
325 elseif (strpos($SQL_reserved,",$colname,"))
326 $string="Column name <i>$colname</i> is a reserved SQL word. Please pick another column name";
327 else {
328 if ($datatype=='pulldown' || $datatype=='mpulldown') {
329 // create an associated table, not overwriting old ones, using a max number
330 $tablestr=$real_tablename;
331 $tablestr.='ass';
332 // simple and robust way to get UID. Start at 20 to avoid clashes
333 $assid=$db->GenID($tablestr,20);
334 $tablestr.="_$assid";
335 if ($datatype=='mpulldown') {
336 $keystr=$real_tablename;
337 $keystr.='ask';
338 $keystr.="_$assid";
339 }
340
341 $r=$db->Execute("INSERT INTO $desc ($fieldstring) Values($fieldid,'$colname','$label','$sort','$Tdis','$Rdis','$req','$modifiable','int','$datatype','$tablestr','','$keystr')");
342 $rs=$db->Execute("CREATE TABLE $tablestr (id int PRIMARY KEY, sortkey int, type text, typeshort text)");
343
344 if ($datatype=='mpulldown') {
345 $nflds="
346 recordid I8 CONSTRAINTS 'FOREIGN KEY REFERENCES $real_tablename (id)',
347 typeid I8 CONSTRAINTS 'FOREIGN KEY REFERENCES $tablestr(id)'
348 ";
349 $sqlArray=$dict->CreateTableSQL($keystr,$nflds,$taboptArray);
350 $dict->ExecuteSQLArray($sqlArray);
351 //$rss=$db->Execute("CREATE TABLE $keystr (recordid int, typeid int)");
352 // create indexes
353 $sqlArray=$dict->CreateIndexSQL($shortname."_ask_$assid".'_rid_index',$keystr,'recordid');
354 $dict->ExecuteSQLArray($sqlArray);
355 $sqlArray=$dict->CreateIndexSQL($shortname."_ask_$assid".'_tid_index',$keystr,'typeid');
356 $dict->ExecuteSQLArray($sqlArray);
357 }
358
359 $rsss=$db->Execute("ALTER table $real_tablename add column $colname int");
360 if ($r && $rs && $rsss && (!($colname=="")))
361 $string="Added column <i>$colname</i> into table <i>$tablelabel</i>";
362 else
363 $string="Problems creating this column.";
364 }
365 elseif ($datatype=="file") {
366 // this table links words found in files to specific records
367 $tablestr=$real_tablename."_wi"."_$fieldid";
368 $rs=$db->Execute("CREATE TABLE $tablestr (wordid int, fileid int, pagenr int, recordid int,UNIQUE (wordid,fileid,pagenr,recordid))");
369 $db->Execute("CREATE INDEX $tablestr"."_wi ON $tablestr (wordid)");
370 $db->Execute("CREATE INDEX $tablestr"."_fi ON $tablestr (fileid)");
371 $db->Execute("CREATE INDEX $tablestr"."_ri ON $tablestr (recordid)");
372
373 $r=$db->Execute("INSERT INTO $desc ($fieldstring) Values($fieldid,'$colname','$label','$sort','$Tdis','$Rdis','$req','$modifiable','int','$datatype','$tablestr',NULL,NULL)");
374 // we do not need this column, but not having it might break something
375 $rsss=$db->Execute("ALTER table $real_tablename add column $colname text");
376 if (($r)&&($rs)&&(!($colname=="")))
377 $string="Added column <i>$colname</i> into table <i>$tablelabel</i>";
378 else
379 $string='Problems creating this column.';
380 }
381 else {
382 if ($datatype=='int' || $datatype=='sequence' || $datatype=='date' || $datatype=='table')
383 $sqltype='int';
384 elseif ($datatype=='float')
385 $sqltype='float';
386 else
387 $sqltype='text';
388 $rsss=$db->Execute("ALTER table $real_tablename add column $colname $sqltype");
389 if ($rsss)
390 $r=$db->Execute("INSERT INTO $desc ($fieldstring) Values($fieldid,'$colname','$label','$sort','$Tdis','$Rdis','$req','$modifiable','$sqltype','$datatype','','',NULL)");
391 if (($r)&&$rsss&&(!($colname==""))) {
392 $string="Added column <i>$colname</i> into table: <i>$tablelabel</i>";
393 return $fieldid;
394 }
395 else {
396 $string='Please enter all values';
397 return false;
398 }
399 }
400 }
401 }
402
403 /////////////////////////////////////////////////////////////////////////
404 ////
405 // !modifies a general column entry
406 function mod_columnECG($db,$sort,$offset) {
407 global $string,$HTTP_POST_VARS;
408
409 $id=$HTTP_POST_VARS["column_id"][$offset];
410 $colname=$HTTP_POST_VARS["column_name"][$offset];
411 $label=$HTTP_POST_VARS["column_label"][$offset];
412 $datatype=$HTTP_POST_VARS["column_datatype"][$offset];
413 $thumbsize=$HTTP_POST_VARS["thumbsize"."_$offset"];
414 if (!$thumbsize)
415 $thumbsize="NULL";
416 $Rdis=$HTTP_POST_VARS["column_drecord"][$offset];
417 $Tdis=$HTTP_POST_VARS["column_dtable"][$offset];
418 $sort=$HTTP_POST_VARS["column_sort"][$offset];
419 $req=$HTTP_POST_VARS["column_required"][$offset];
420 $modifiable=$HTTP_POST_VARS["column_modifiable"][$offset];
421
422 // find the id of the table and therewith the tablename
423 $tablename=$HTTP_POST_VARS["table_name"];
424 $r=$db->Execute("SELECT id FROM tableoftables WHERE tablename='$tablename'");
425 $tableid=$r->fields["id"];
426 $real_tablename=get_cell($db,"tableoftables","real_tablename","id",$tableid);
427 $desc=$real_tablename."_desc";
428
429 // escape bad stuffin label
430 $label=strtr($label,",'"," ");
431 $r=$db->Execute("UPDATE $desc SET sortkey='$sort',display_table='$Tdis', display_record='$Rdis',required='$req',label='$label',modifiable='$modifiable',thumb_x_size=$thumbsize where id='$id'");
432 if ($r) {
433 $string="Succesfully Changed Column $colname in $tablename";
434 return true;
435 }
436 else
437 $string="Failed to modify column $colname.";
438 return false;
439 }
440
441 /////////////////////////////////////////////////////////////////////////
442 ////
443 // !Modifies an entry for a report
444 function mod_report($db,$offset) {
445 global $HTTP_POST_VARS,$HTTP_GET_VARS,$HTTP_POST_FILES,$system_settings;
446
447 $id=$HTTP_POST_VARS["report_id"][$offset];
448 $label=$HTTP_POST_VARS["report_label"][$offset];
449 $sortkey=$HTTP_POST_VARS["report_sortkey"][$offset];
450 $sortkey=(int)$sortkey;
451 if (!$sortkey)
452 $sortkey="NULL";
453 $templatedir=$system_settings["templatedir"];
454 if (isset($HTTP_POST_FILES["report_template"][$offset][0]) && !$templatedir) {
455 echo "<h3 align='center'>Templatedir is not set. Please correct this first.</h3>";
456 exit;
457 }
458 // Upload file, if any
459 $fileuploaded=move_uploaded_file($HTTP_POST_FILES["report_template"]["tmp_name"][$offset],"$templatedir/$id.tpl");
460 if ($fileuploaded) {
461 $filesize=$HTTP_POST_FILES["report_template"]["size"][$offset];
462 if (!$filesize)
463 $filesize="NULL";
464 }
465 // Write changes to database
466 if ($filesize)
467 $r=$db->Execute("UPDATE reports SET label='$label', sortkey=$sortkey, filesize=$filesize WHERE id='$id'");
468 else
469 $r=$db->Execute("UPDATE reports SET label='$label', sortkey=$sortkey WHERE id='$id'");
470 }
471
472 /////////////////////////////////////////////////////////////////////////
473 ////
474 // !Deletes an entry for a report
475 function rm_report($db,$offset) {
476 global $HTTP_POST_VARS,$system_settings;
477
478 $id=$HTTP_POST_VARS["report_id"][$offset];
479 $r=$db->Execute("DELETE FROM reports WHERE id=$id");
480 @unlink ($system_settings["templatedir"]."/$id.tpl");
481 }
482
483 /////////////////////////////////////////////////////////////////////////
484 ////
485 // !Deletes an entry for a report
486 function test_report($db,$offset,$tablename) {
487 global $HTTP_POST_VARS,$HTTP_GET_VARS,$system_settings;
488 $HTTP_GET_VARS["tablename"]=$tablename;
489
490 $tableinfo=new tableinfo($db);
491 $real_tablename=get_cell($db,"tableoftables","real_tablename","tablename",$tablename);
492 $reportid=$HTTP_POST_VARS["report_id"][$offset];
493 $r=$db->Execute("SELECT * FROM $real_tablename");
494
495 $fields=comma_array_SQL($db,$tableinfo->desname,"columnname");
496 $Allfields=getvalues($db,$tableinfo,$fields,"id",$r->fields["id"]);
497
498 $tp=@fopen($system_settings["templatedir"]."/$reportid.tpl","r");
499 if ($tp) {
500 while (!feof($tp))
501 $template.=fgets($tp,64000);
502 fclose($tp);
503 }
504 require("includes/report_inc.php");
505 $report=make_report($db,$template,$Allfields,$tableinfo,1);
506 return $report;
507
508 }
509 /////////////////////////////////////////////////////////////////////////
510 ////
511 // ! Streams a template back to the user
512 function export_report($db,$offset) {
513 global $HTTP_POST_VARS,$system_settings;
514
515 $templatedir=$system_settings["templatedir"];
516 $id=$HTTP_POST_VARS["report_id"][$offset];
517 if (is_readable("$templatedir/$id.tpl")) {
518 header("Accept-Ranges: bytes");
519 header("Connection: close");
520 header("Content-Type: text/txt");
521 // header("Content-Length: $filesize");
522 header("Content-Disposition-type: attachment");
523 header("Content-Disposition: attachment; filename=$filename");
524 readfile("$templatedir/$id.tpl");
525 }
526 }
527
528 /////////////////////////////////////////////////////////////////////////
529 ////
530 // ! Adds a new entry for a report
531 function add_report($db) {
532 global $HTTP_POST_VARS,$HTTP_POST_FILES,$HTTP_GET_VARS,$system_settings;
533
534 $id=$db->GenID("reports"."_gen_id_seq");
535 $tablename=$HTTP_GET_VARS["editreport"];
536 $r=$db->Execute("SELECT id FROM tableoftables WHERE tablename='$tablename'");
537 $tableid=$r->fields["id"];
538 $label=$HTTP_POST_VARS["addrep_label"];
539 $templatedir=$system_settings["templatedir"];
540 $sortkey=$HTTP_POST_VARS["addrep_sortkey"];
541 $sortkey=(int)$sortkey;
542 if (!$sortkey)
543 $sortkey="NULL";
544
545 // checks on input
546 if (!$label) {
547 return "<h3 align='center'>Please provide a template name!</h3>\n";
548 }
549 $fileuploaded=move_uploaded_file($HTTP_POST_FILES["addrep_template"]["tmp_name"],"$templatedir/$id.tpl");
550 if ($fileuploaded)
551 $filesize=$HTTP_POST_FILES["addrep_template"]["size"];
552 if (!$filesize)
553 $filesize="NULL";
554 $db->Execute("INSERT INTO reports (id,label,tableid,sortkey,filesize) VALUES ($id,'$label',$tableid,$sortkey,$filesize)");
555
556 }
557
558
559 /////////////////////////////////////////////////////////////////////////
560 ////
561 // !deletes a general column entry
562 function rm_columnecg($db,$tablename,$id,$colname,$datatype) {
563 global $string,$USER;
564
565 // find the id of the table and therewith the tablename
566 $r=$db->Execute("SELECT id FROM tableoftables WHERE tablename='$tablename'");
567 $tableid=$r->fields["id"];
568 $real_tablename=get_cell($db,"tableoftables","real_tablename","id",$tableid);
569 $tablelabel=get_cell($db,"tableoftables","label","id",$tableid);
570 $desc=get_cell($db,"tableoftables","table_desc_name","id",$tableid);
571 // if there are files associated, these have to be deleted as well
572 $r=$db->Execute ("SELECT datatype FROM $desc WHERE id='$id'");
573 if ($r->fields["datatype"]=="file") {
574 $r=$db->Execute("SELECT id FROM files WHERE tablesfk='$tableid'");
575 while (!$r->EOF)
576 delete_file($db,$r->fields["id"],$USER);
577 }
578 if ($r->fields["datatype"]=="pulldown" || $r->fields["datatype"=="file"]) {
579 $rv=$db->Execute("select associated_table from $desc where id ='$id'");
580 // $tempTAB=array();
581 if ($rv) {
582 while (!$rv->EOF) {
583 if ($rv->fields[0])
584 $db->Execute("DROP TABLE ".$rv->fields[0]);
585 $rv->MoveNext();
586 }
587 }
588 }
589 $r=$db->Execute("ALTER TABLE $real_tablename DROP COLUMN $colname");
590 $rrr=$db->Execute("DELETE FROM $desc WHERE id='$id'");
591 // Postgres does know how to drop a column, so only check the second query
592 if ($rrr)
593 $string="Deleted Column <i>$colname</i> from Table <i>$tablelabel</i>.";
594 }
595
596 ////
597 // !helper function for show_table_column_page
598 function make_column_js_array($db,$r) {
599 $result="new Array(\n";
600 $rb=$db->Execute("SELECT label,id FROM ".$r->fields["table_desc_name"]." WHERE label NOT IN ('id','access','date','ownerid','magic','lastmoddate','lastmodby')");
601 $result.="new Array(\"".$rb->fields["label"]."\", ".$rb->fields["id"].")";
602 $rb->MoveNext();
603 while (!$rb->EOF) {
604 $result.=",\nnew Array(\"".$rb->fields["label"]."\", ".$rb->fields["id"].")";
605 $rb->MoveNext();
606 }
607 $result.=")";
608 return $result;
609 }
610
611
612 ////
613 // ! show active link page
614 function show_active_link_page ($db,$table_name,$addcol_name,$addcol_label,$link_part_a=false,$link_part_b=false) {
615 echo "<form method='post' id='active_link'>\n";
616 echo "<input type='hidden' name='table_name' value='$table_name'></input>\n";
617 echo "<input type='hidden' name='addcol_name' value='$addcol_name'></input>\n";
618 echo "<input type='hidden' name='addcol_label' value='$addcol_label'></input>\n";
619 echo "<table align='center' cellpadding='2' cellspacing='0'>\n";
620 echo "<tr><td>Enter the link (including http://) here. \"Cell content\" will be extracted from the database</td></tr>\n";
621 echo "<tr><td><input type='text' name='link_part_a' value='$link_part_a'>\n";
622 echo "cell content<input type='text'name='link_part_b' value='$link_part_b'></td></tr>\n";
623
624 echo "<tr><td align='center'><input type='submit' name='submit' value='Submit'></input></td>\n";
625 echo "</tr>\n</table>\n</form>\n";
626 }
627
628
629 ////
630 // !Stores active link data
631 function add_active_link ($db,$table,$column,$link_a,$link_b) {
632 $r=$db->Execute("SELECT table_desc_name FROM tableoftables WHERE tablename='$table'");
633 $table_desc=$r->fields["table_desc_name"];
634 if ($r) {
635 $r=$db->Execute("UPDATE $table_desc SET link_first='$link_a',link_last='$link_b' WHERE columnname='$column'");
636 }
637 }
638
639
640 ////
641 // ! show page with choice of tables, dynamically generate list with columns
642 function show_table_column_page ($db,$table_name,$addcol_name,$addcol_label) {
643 global $HTTP_GET_VARS;
644
645 echo "<form method='post' id='table_type'>\n";
646 echo "<input type='hidden' name='table_name' value='$table_name'></input>\n";
647 echo "<input type='hidden' name='addcol_name' value='$addcol_name'></input>\n";
648 echo "<input type='hidden' name='addcol_label' value='$addcol_label'></input>\n";
649 // box 1 with tablenames
650 $r=$db->Execute("SELECT tablename,id,table_desc_name FROM tableoftables WHERE permission='Users' AND tablename <> 'settings' AND tablename <> '$table_name' AND table_desc_name IS NOT NULL ORDER BY sortkey");
651 // box 2, dynamically filled with column names
652 $the_array="modelinfo = new Array (\n";
653 $the_array.=make_column_js_array($db,$r);
654 $r->MoveNext();
655 while (!$r->EOF) {
656 $the_array.=",\n ".make_column_js_array($db,$r);
657 $r->MoveNext();
658 }
659 $the_array.="\n)\n";
660 echo add_js ($the_array);
661 $jscript="onChange=\"fillSelectFromArray(this.form.table_column_select,((this.selectedIndex == -1) ? null : modelinfo[this.selectedIndex-1]));\"";
662
663 echo "<h3 align='center'>Choose Table and column to be associated with column <i>$addcol_label</i> in table <i>$table_name</i>.</h3>\n";
664 echo "<table align='center' cellpadding='2' cellspacing='0'>\n";
665 echo "<tr><th>Table</th>\n<th>Column</th><th> </th></tr>\n";
666 $r->MoveFirst();
667 echo "<tr><td>".$r->GetMenu2("table_select","",true,false,0,$jscript)."</td>\n";
668 echo "<td><select name='table_column_select'></select></td>\n";
669 echo "</tr>\n";
670 $HTTP_GET_VARS['tablename']=$table_name;
671 $tableinfo=new tableinfo($db);
672 $rs=$db->Execute("SELECT id,associated_table,associated_column,associated_local_key,label FROM {$tableinfo->desname} WHERE datatype='table'");
673 if ($rs && !$rs->EOF) {
674 echo "<tr><td colspan=3>Grouping:</td></tr>\n";
675 echo "<tr><td colspan=3><input type='radio' name='ass_to'> Make this a primary key</input></td></tr>\n";
676 while (!$rs->EOF) {
677 if ($rs->fields['associated_table'] && !$rs->fields['associated_local_key']) {
678 $ass_tableinfo=new tableinfo($db,false,$rs->fields['associated_table']);
679 $ass_column=get_cell($db,$ass_tableinfo->desname,'label','id',$rs->fields['associated_column']);
680 echo "<tr><td colspan=3><input type='radio' name='ass_to' value='{$rs->fields[0]}'> Associate with: Local column: <i>{$rs->fields['label']}</i> (Foreign table: <i>{$ass_tableinfo->name}</i>, column: <i>$ass_column</i>),</input></td></tr>\n";
681 }
682 $rs->MoveNext();
683 }
684 }
685
686 echo "<tr><td colspan=3 align='center'><input type='submit' name='submit' value='Submit'></input></td></tr>\n";
687 echo "</table>\n</form>\n";
688 }
689
690 ////
691 // !Associates given column with a column in another table
692 // if there is already an association with the other table, that association
693 // will be used as a key
694 function add_associated_table($db,$table,$column,$table_ass,$column_ass) {
695 global $HTTP_POST_VARS;
696
697 $r=$db->Execute("SELECT table_desc_name FROM tableoftables WHERE tablename='$table'");
698 $table_desc=$r->fields["table_desc_name"];
699 $r=$db->Execute("UPDATE $table_desc SET associated_table='$table_ass', associated_column='$column_ass' WHERE columnname='$column'");
700 $ass_to=(int)$HTTP_POST_VARS['ass_to'];
701 if ($ass_to) {
702 $r=$db->Execute("UPDATE $table_desc SET associated_local_key='$ass_to' WHERE columnname='$column'");
703 }
704 }
705
706 ?>