<?php

/**
  * BLOG:CMS: PHP/MySQL Personal Content Management System (CMS)
  * http://blogcms.com/
  * ----------------------------------------------------------------
  *
  * Copyright (C) 2003-2005 Radek HULÁN
  * http://hulan.cz/contact/
  *
  * ----------------------------------------------------------------
  * This program is free software; you can redistribute it and/or
  * modify it under the terms of the GNU General Public License
  * as published by the Free Software Foundation; either version 2
  * of the License, or (at your option) any later version.
**/

  function printTable(){
    global $pluginstable;
    $query=sql_query('select tableid, title, description from '.sql_table('plug_table_head').' order by title asc');
    if (sql_num_rows($query)==0) {
      echo "<p>No tables defined yet</p>";
    } else {
      echo "<table><thead><tr>";
      echo "<th>Table</th><th>Description</th><th>Items</th><th colspan='4'>Templates</th><th colspan='2'>Structure</th></tr></thead><tbody>";
      while ($row=sql_fetch_object($query)){
        echo "<tr onmouseover='focusRow(this);' onmouseout='blurRow(this);'>";
        $tableid=strval($row->tableid);
        echo "<td>".sql_unescape($row->title)." <a href='$pluginstable/?action=edittemplate&amp;field=title&amp;id=$tableid&amp;text=0'>edit</a></td>";
        echo "<td>".sql_unescape($row->description)." <a href='$pluginstable/?action=edittemplate&amp;field=description&amp;id=$tableid&amp;text=0'>edit</a></td>";
        echo "<td><a href='$pluginstable/?action=edititems&amp;id=$tableid'>edit items</a></td>";
        echo "<td><a href='$pluginstable/?action=edittemplate&amp;field=theader&amp;id=$tableid'>header</a></td>";
        echo "<td><a href='$pluginstable/?action=edittemplate&amp;field=titem1&amp;id=$tableid'>row 1</a></td>";
        echo "<td><a href='$pluginstable/?action=edittemplate&amp;field=titem2&amp;id=$tableid'>row 2</a></td>";
        echo "<td><a href='$pluginstable/?action=edittemplate&amp;field=tfooter&amp;id=$tableid'>footer</a></td>";
        echo "<td><a href='$pluginstable/?action=showtable&amp;id=$tableid'>show</a></td>";
        echo "<td><a href='$pluginstable/?action=deletetable&amp;id=$tableid'>delete</a></td>";
        echo "</tr>";
      }
      echo "</tbody></table>";
    }
  }

  function printNew($columns){
    global $pluginstable;
    echo "<form method='post' action='$pluginstable/'><div>";
    echo "<table>";
    echo "<tr><td>Short table name (only a-z,0-9,A-Z)</td><td><input name='plugintabletitle' type='text' value='' size='20' /></td></tr>";
    echo "<tr><td>Table description</td><td><input type='text' name='plugintabledescription' value='' size='60' /></td></tr>";
    echo "</table>";
    echo "<table><thead><tr>";
    echo "<th>Column</th><th>Name (a-z,0-9)</th><th>Description</th><th>Type</th></tr></thead><tbody>";
    for ($i=1;$i<=$columns;$i++){
      echo "<tr onmouseover='focusRow(this);' onmouseout='blurRow(this);'>";
      echo "<td>$i</td>";
      echo "<td><input type='text' size='15' name='plugintableshortname$i' value='' /></td>";
      echo "<td><input type='text' size='30' name='plugintablelongname$i' value='' /></td>";
      echo "<td><input type='radio' id='pt001$i' name='plugintabletype$i' value='0' checked='checked' /><label for='pt001$i'>none</label>";
      echo "    <input type='radio' id='pt002$i' name='plugintabletype$i' value='1' /><label for='pt002$i'>text</label>";
      echo "    <input type='radio' id='pt003$i' name='plugintabletype$i' value='2' /><label for='pt003$i'>number</label></td>";
      echo "</tr>";
    }
    echo "</tbody></table>";
    echo "<input type='submit' name='plugintablesubmit1' value='Create this table!' />";
    echo "</div></form>";
  }

  function _back(){
    echo "<p><a href='javascript:history.go(-1);' title='Previous page'>&raquo; Go Back</a></p>";
  }

  function _doError($s){
    echo "<h2>Error!</h2><p>$s</p>";
    _back();
    global $oPluginAdmin;
    $oPluginAdmin->end();
    die;
  }

  function createNewTable($columns){
    // check submitted data
    if (empty($_POST['plugintabletitle'])) _doError('No table name specified');
    if (!ereg('^[0-9a-zA-Z]+$',$_POST['plugintabletitle'])) _doError('Bad table name specified.');
    $total=0;
    $arr=array();
    for ($i=1;$i<=$columns;$i++){
      $j=strval($i);
      if ($_POST["plugintabletype$j"] != '0') {
        if (!ereg('^[0-9a-zA-Z]+$',$_POST["plugintableshortname$j"])) _doError("Bad no.$j column name specified.");
        if (empty($_POST["plugintableshortname$j"])) _doError("No.$j short column name not specified.");
        if (empty($_POST["plugintablelongname$j"])) _doError("No.$j column description not specified.");
        $total++;
        $arr[]=$i;
      }
    }
    if ($total==0) _doError('No columns specified!');
    $query=sql_query("select count(*) as total from ".sql_table('plug_table_head')." where title='".$_POST['plugintabletitle']."'");
    $row=sql_fetch_object($query);
    if ($row->total>0) _doError('Table name already exists!');
    // data seems to be ok
    // table header
    $title=sql_escape($_POST['plugintabletitle']);
    $description=sql_escape($_POST['plugintabledescription']);
    sql_query("insert into ".sql_table('plug_table_head')." (title,description) values ('$title','$description')");
    $tableid=strval(sql_insert_id());
    // table items
    $total=1;
    $theader="<h4><%tabledescription%></h4>\n<table><thead><tr>\n";
    $titem="<tr>\n";
    $tfooter="</tbody></table>";
    foreach ($arr as $i){
      $j=strval($i);
      $rowid=strval($total++);
      $title=sql_escape($_POST["plugintableshortname$j"]);
      $description=sql_escape($_POST["plugintablelongname$j"]);
      $rowtype=$_POST["plugintabletype$j"];
      sql_query("insert into ".sql_table('plug_table_row').
                " (tableid,rowid,rowtitle,rowdescription,rowtype) ".
                " values ($tableid,$rowid,'$title','$description',$rowtype)");
      $theader.="\t<th>$description</th>\n";
      $titem  .="\t<td><%$title%></td>\n";
    }
    $theader.="</tr></thead><tbody>";
    $titem.="</tr>";
    sql_query("update ".sql_table('plug_table_head')." set ".
              "theader='$theader', ".
              "titem1='$titem', ".
              "titem2='$titem', ".
              "tfooter='$tfooter' ".
              "where tableid=$tableid" );
  }

  function showTable($tableid){
  	$tableid = intval($tableid);
    echo "<h2>Table structure</h2>";
    $query=sql_query("select title, description from ".sql_table('plug_table_head')." where tableid=$tableid");
    if ($row=sql_fetch_object($query)){
      echo "<p><b>Table name</b>: ".sql_unescape(strtoupper($row->title))."</p>";
      echo "<p><b>Description</b>: ".sql_unescape($row->description)."</p>";
    } else return;
    $query=sql_query("select rowtitle, rowdescription, rowtype from ".sql_table('plug_table_row')." where tableid=$tableid order by rowid asc");
    echo "<table><thead><tr><th>Column Name</th><th>Column description</th><th>Type</th></tr></thead><tbody>";
    while ($row=sql_fetch_object($query)) {
      echo "<tr onmouseover='focusRow(this);' onmouseout='blurRow(this);'>";
      echo "<td>".sql_unescape($row->rowtitle)."</td><td>".sql_unescape($row->rowdescription)."</td>";
      if (intval($row->rowtype)==1) echo "<td>text</td>"; else echo "<td>number</td>";
      echo "</tr>";
    }
    echo "</tbody></table>";
  }

  function deleteTable($tableid,$confirm=true){
    global $pluginstable;
    $query=sql_query("select title, description from ".sql_table('plug_table_head')." where tableid=$tableid");
    if (!$row=sql_fetch_object($query)) return;
    if ($confirm){
      echo "<h2>Table delete confirm</h2>";
      echo "<p><b>Table name</b>: ".sql_unescape(strtoupper($row->title))."</p>";
      echo "<p><b>Description</b>: ".sql_unescape($row->description)."</p>";
      echo "<form method='post' action='$pluginstable/'><div>";
      echo "<input type='submit' name='pluginsubmitfinaldelete' value='Delete this table and all items!' />";
      echo "<input type='hidden' name='plugintableid' value='$tableid' />";
      echo "</div></form>";
    } else {
      sql_query("delete from ".sql_table('plug_table_item')." where tableid=$tableid");
      sql_query("delete from ".sql_table('plug_table_row')." where tableid=$tableid");
      sql_query("delete from ".sql_table('plug_table_head')." where tableid=$tableid");
      echo "<h2>Table deleted!</h2>";
    }
  }

  function editTemplate($tableid,$field){
    global $pluginstable;
    $query=sql_query("select $field from ".sql_table('plug_table_head')." where tableid=$tableid");
    if (!$msg=sql_fetch_array($query)) return;
    echo "<h2>Edit template: $field</h2>";
    echo "<form method='post' action='$pluginstable/'><div>";
    if ($_GET['text']=='0')
      echo "<div><input name='plugintabletemplate' size='50' value='".sql_unescape($msg[$field])."' /></div>";
    else
      echo "<textarea name='plugintabletemplate' rows='10' cols='50' >".htmlspecialchars(sql_unescape($msg[$field]))."</textarea><br />";
    echo "<input type='hidden' name='plugintableid' value='$tableid' />";
    echo "<input type='hidden' name='plugintablefield' value='$field' />";
    echo "<input type='submit' name='pluginsubmittemplate' value='Commit this change!' />";
    echo "</div></form>";
  }

  function saveTemplate($tableid,$field){
  	$tableid = intval($tableid);
  	$field = sql_escape($field);
    echo "<h2>Saving template: $field</h2>";
    sql_query("update ".sql_table('plug_table_head')." set $field='".sql_escape($_POST['plugintabletemplate'])."' where tableid=$tableid");
    echo "<p>Template was saved...</p>";
  }

  function editItems($tableid){
    global $pluginstable;
    $tableid = intval($tableid);
    $query=sql_query("select title, description from ".sql_table('plug_table_head')." where tableid=$tableid");
    if (!$row=sql_fetch_object($query)) return;
    echo "<h2>Add and edit items for table: ".sql_unescape(strtoupper($row->title))."</h2>";
    echo "<p>Description: ".sql_unescape($row->description)."</p>";
    // new item
    echo "<h3>Add new item</h3>";
    echo "<form method='post' action='$pluginstable/'><div>";
    $query=sql_query("select rowid, rowdescription, rowtype from ".sql_table('plug_table_row')." where tableid=$tableid order by rowid asc");
    echo "<table><thead><tr><th>Column</th><th>Content</th><th>Type</th></tr></thead><tbody>";
    while ($row=sql_fetch_object($query)) {
      echo "<tr onmouseover='focusRow(this);' onmouseout='blurRow(this);'>";
      echo "<td>".sql_unescape($row->rowdescription)."</td>";
      echo "<td><textarea name='pluginadditem".strval($row->rowid)."' rows='2' cols='70'></textarea></td>";
      if (intval($row->rowtype)==1) echo "<td>text</td>"; else echo "<td>number</td>";
      echo "</tr>";
    }
    echo "</tbody></table>";
    echo "<input type='hidden' name='plugintableid' value='$tableid' />";
    echo "<input type='submit' name='pluginsubmitnewitem' value='Add new item!' />";
    echo "</div></form>";
    echo "<h3>Edit and delete items</h3>";
    // total rows
    $query=sql_query("select count(rowid) as total from ".sql_table('plug_table_row')." where tableid=$tableid");
    $row=sql_fetch_object($query);
    $numrows=$row->total;
    // all items
    echo "<table><thead><tr>";
    $query=sql_query("select rowdescription, rowid from ".sql_table('plug_table_row')." where tableid=$tableid order by rowid asc");
    while ($row=sql_fetch_object($query)){
      echo "<th>".sql_unescape($row->rowdescription)."</th>";
    }
    echo "<th colspan='2'>Action</th>";
    echo "</tr></thead><tbody>";
    $query=sql_query(
      "select i.itemid as xitemid, i.rowid as xrowid, i.itemtext as xitemtext, i.itemint as xitemint, r.rowtype as xrowtype ".
      " from ".sql_table('plug_table_row')." r, ".sql_table('plug_table_item')." i ".
      " where r.tableid=$tableid and r.tableid=i.tableid and r.rowid=i.rowid ".
      " order by xitemid,xrowid");
    while ($row=sql_fetch_object($query)){
      if ($row->xrowid==1) {
        echo "<tr onmouseover='focusRow(this);' onmouseout='blurRow(this);'>";
        $from=$row->xitemid;
      }
      if (intval($row->xrowtype)==1)echo "<td>".strip_tags(htmlspecialchars(sql_unescape($row->xitemtext)))."</td>"; else echo "<td>".strval($row->xitemint)."</td>";
      if ($row->xrowid==$numrows) {
        echo "<td><a href='$pluginstable/?action=itemedit&amp;id=$tableid&amp;itemfrom=$from&amp;itemto=".strval($row->xitemid)."'>edit</a></td>";
        echo "<td><a href='$pluginstable/?action=itemdelete&amp;id=$tableid&amp;itemfrom=$from&amp;itemto=".strval($row->xitemid)."'>delete</a></td>";
        echo "</tr>";
      }
    }
    echo "</tbody></table>";
  }

  function addItem($tableid){
    // total rows
    $tableid = intval($tableid);
    $query=sql_query("select count(rowid) as total from ".sql_table('plug_table_row')." where tableid=$tableid");
    $row=sql_fetch_object($query);
    $numrows=$row->total;
    $isok=false;
    for ($i=1;$i<=$numrows;$i++) if (!empty($_POST['pluginadditem'.strval($i)])) $isok=true;
    if (!$isok) _doError('Fields empty! Item NOT added.');
    echo "<h2>Adding item</h2>";
	$tableitem = intVal(quickQuery("select max(tableitem) as result from ".sql_table('plug_table_item')." where tableid=$tableid"))+1;
    for ($i=1;$i<=$numrows;$i++) {
      $j=strval($i);
      $itemtext=sql_escape($_POST['pluginadditem'.strval($i)]);
      if (is_numeric($_POST['pluginadditem'.strval($i)])) $itemint=$_POST['pluginadditem'.strval($i)]; else $itemint="0";
      sql_query(
        "insert into ".sql_table('plug_table_item').
        " (tableid,rowid,itemtext,itemint,tableitem) values ".
        " ($tableid,$j,'$itemtext',$itemint,$tableitem)"
        );
    }
    echo "<p>Done...</p>";
  }

  function deleteItem($itemfrom,$itemto){
    echo "<h2>Deleting item</h2>";
    $itemfrom = intval($itemfrom);
    $itemto = intval($itemto);
    sql_query("delete from ".sql_table('plug_table_item')." where itemid>=$itemfrom and itemid<=$itemto");
    echo "<p>Done...</p>";
  }

  function itemEdit($tableid,$itemfrom,$itemto){
    global $pluginstable;
    $tableid = intval($tableid);
    $itemfrom = intval($itemfrom);
    $itemto = intval($itemto);
    $query=sql_query("select title, description from ".sql_table('plug_table_head')." where tableid=$tableid");
    if (!$row=sql_fetch_object($query)) return;
    echo "<h2>Edit item</h2>";
    echo "<form method='post' action='$pluginstable/'><div>";
    $query=sql_query("select rowid, rowdescription, rowtype from ".sql_table('plug_table_row')." where tableid=$tableid order by rowid asc");
    echo "<table><thead><tr><th>Column</th><th>Content</th><th>Type</th></tr></thead><tbody>";
    $i=0;
    while ($row=sql_fetch_object($query)) {
      echo "<tr onmouseover='focusRow(this);' onmouseout='blurRow(this);'>";
      echo "<td>".sql_unescape($row->rowdescription)."</td>";
      $query_val=sql_query("select itemtext, itemint from ".sql_table('plug_table_item')." where itemid=".strval(intval($itemfrom+$i)));
      $value='';
      if ($row_val=sql_fetch_object($query_val))
        if (intval($row->rowtype)==1) $value=sql_unescape($row_val->itemtext); else $value=strval($row_val->itemint);
      echo "<td><textarea name='pluginedititem".strval(intval($itemfrom+$i))."' rows='4' cols='70'>$value</textarea></td>";
      if (intval($row->rowtype)==1) echo "<td>text</td>"; else echo "<td>number</td>";
      echo "</tr>";
      $i++;
    }
    echo "</tbody></table>";
    echo "<input type='hidden' name='plugintableid' value='$tableid' />";
    echo "<input type='hidden' name='plugintablefrom' value='$itemfrom' />";
    echo "<input type='hidden' name='plugintableto' value='$itemto' />";
    echo "<input type='submit' name='pluginsubmitedititem' value='Edit item!' />";
    echo "</div></form>";
  }

  function editItem($tableit,$itemfrom,$itemto){
    $tableid = intval($tableid);
    $itemfrom = intval($itemfrom);
    $itemto = intval($itemto);
    echo "<h2>Editing item</h2>";
    for ($i=intval($itemfrom);$i<=intval($itemto);$i++){
      $j=strval($i);
	$itemtext=sql_escape(undoMagic($_POST['pluginedititem'.strval($i)]));
      if (is_numeric($_POST['pluginedititem'.strval($i)])) $itemint=$_POST['pluginedititem'.strval($i)]; else $itemint="0";
      sql_query("update ".sql_table('plug_table_item')." set itemtext='$itemtext',itemint=$itemint where itemid=$j");
    }
    echo "<p>Done...</p>";
  }

	$strRel = '../../../';
	include($strRel . 'cfg.php');
	global $member;
	if (!$member->isAdmin())	
		doError('You\'re not logged in.');
	include($DIR_LIBS . 'PLUGINADMIN.php');

	// create the admin area page
	$oPluginAdmin = new PluginAdmin('Table');
	$oPluginAdmin->start();

	global $pluginstable, $CONF;
	$pluginstable=$CONF['PluginURL']."table";

	echo '<p><a href="index.php?action=overview">(',_BACKHOME,')</a></p>';

	// new table
	if (isset($_POST['plugintablesubmit1'])) {
    createNewTable($oPluginAdmin->plugin->_getColumns());
  }
	// delete table
	if (isset($_POST['pluginsubmitfinaldelete'])) {
    deleteTable(intval($_POST['plugintableid']),false);
    $oPluginAdmin->end();
    return;
  }
	// new item POST
	if (isset($_POST['pluginsubmitnewitem'])) {
    addItem($_POST['plugintableid']);
    editItems($_POST['plugintableid']);
    $oPluginAdmin->end();
    return;
  }
	// edit item POST
	if (isset($_POST['pluginsubmitedititem'])) {
    editItem($_POST['plugintableid'],$_POST['plugintablefrom'],$_POST['plugintableto']);
    editItems($_POST['plugintableid']);
    $oPluginAdmin->end();
    return;
  }
	// edit template POST
	if (isset($_POST['pluginsubmittemplate'])) {
	  saveTemplate($_POST['plugintableid'],$_POST['plugintablefield']);
	  editTemplate($_POST['plugintableid'],$_POST['plugintablefield']);
    $oPluginAdmin->end();
    return;
  }

  // actions GET
  if (isset($_GET['action'])){
    switch ($_GET['action']) {
      case 'showtable':
        showTable(intGetVar('id'));
        _back();
        $oPluginAdmin->end();
        return;
        break;
      case 'deletetable':
        deleteTable(intGetVar('id'),true);
        $oPluginAdmin->end();
        return;
        break;
      case 'edittemplate':
        editTemplate(intGetVar('id'),$_GET['field']);
        showTable(intGetVar('id'));
        _back();
        $oPluginAdmin->end();
        return;
        break;
      case 'edititems':
        editItems(intGetVar('id'));
        _back();
        $oPluginAdmin->end();
        return;
        break;
      case 'itemdelete':
        deleteItem($_GET['itemfrom'],$_GET['itemto']);
        editItems($_GET['id']);
        _back();
        $oPluginAdmin->end();
        return;
        break;
      case 'itemedit':
        itemEdit($_GET['id'],$_GET['itemfrom'],$_GET['itemto']);
        $oPluginAdmin->end();
        return;
        break;
    }
  }

	// plugin headline
	echo "<h2>Lists / Tables Creation</h2>";

  // print table
	echo "<h3>Existing Lists / Tables</h3>";
  printTable();

  // new table
  echo "<h3>Create New List / Table</h3>";
  printNew($oPluginAdmin->plugin->_getColumns());

	$oPluginAdmin->end();
?>