<?php
/* DrasticTools version 0.6.18
* DrasticTools is released under the GPL license:
* Copyright (C) 2007 email: info@drasticdata.nl
*
* 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.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License along
* with this program; if not, write to the Free Software Foundation, Inc.,
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
*
* =========================================================================================
* If you find this sofware useful, we appreciate your donation on http://www.drasticdata.nl
* Suggestions for improvement can be sent to: info@drasticdata.nl
* =========================================================================================
*/
class drasticSrcMySQL {
// these options should be set via the options argument on the constructor!
public $add_allowed = true; // may the user add records? Default is true.
public $delete_allowed = true; // may the user delete records? Default is true.
public $editablecols; // array of columnnames to be editable. Defaults to all columns except the id column
public $defaultcols; // array of columnnames and values; only records that satisfy these conditions will be selected;
// added records will have these values as default
public $sortcol; // name of column to sort on initially. Defaults to the id column.
public $sort; // sort ascending (a) or descending (d)? Default is a.
public $SQLCharset = "utf8"; // character set of the strings in the table
public $HTMLCharset = "UTF-8"; // character set for xhttprequest
// General variables
public $orderbystr, $wherestr, $addstr;
public $idname;
public $idcolnr;
public $result;
public $num_rows;
public $num_fields;
public $cols;
public $cols_numeric;
private $max;
function __construct($server, $user, $pw, $db, $table, $options = null) {
if (!isset($_REQUEST["op"])) return;
if ($options) {
if (isset($options["add_allowed"])) $this->add_allowed = $options["add_allowed"];
if (isset($options["delete_allowed"])) $this->delete_allowed = $options["delete_allowed"];
if (isset($options["editablecols"])) $this->editablecols = $options["editablecols"];
if (isset($options["defaultcols"])) $this->defaultcols= $options["defaultcols"];
if (isset($options["sortcol"])) $this->sortcol= $options["sortcol"];
if (isset($options["sort"])) $this->sort= $options["sort"];
if (isset($options["SQLCharset"])) $this->SQLCharset= $options["SQLCharset"];
if (isset($options["HTMLCharset"])) $this->HTMLCharset= $options["HTMLCharset"];
}
/* Optionally retrieve parameters from the addparams parameter.
* Uncomment the line below and change "myparameter" to the name of your parameter
* If you pass multiple parameters copy the line multiple times
*
* $myparameter = $_REQUEST["myparameter"];
*/
$this->conn = mysql_connect($server, $user, $pw) or die(mysql_error());
mysql_select_db($db) or die (mysql_error());
$this->table = $table;
$res = mysql_query("SET NAMES '" . $this->SQLCharset . "'", $this->conn);
// Initialize the name of the id field, column names and numeric columns:
$idresult = $this->metadata();
$primary_found = false;
for($i=0; $i < mysql_num_fields($idresult); $i++) {
$fld = mysql_fetch_field($idresult, $i);
if ($primary_found == false) {
if ($fld->primary_key == 1) {
$this->idname = $fld->name;
$this->idcolnr = $i;
$primary_found = true;
}
elseif ($fld->unique_key == 1) {
$this->idname = $fld->name;
$this->idcolnr = $i;
}
}
$this->cols[] = $fld->name;
if ($fld->numeric == 1) $this->cols_numeric[] = $fld->name;
}
if (!isset($this->idname)) die("Could not find primary or unique key");
// Initialize editablecols if not done yet:
if (!isset($this->editablecols)) {
mysql_field_seek($idresult, 0);
for($i=0; $i < mysql_num_fields($idresult); $i++) {
$fldname = mysql_fetch_field($idresult)->name;
if ($fldname != $this->idname) $this->editablecols[] = $fldname;
}
}
mysql_free_result($idresult);
// Initialize Field types:
$this->fldtypes = array();
$colresult = mysql_query("SHOW COLUMNS FROM " . $this->table, $this->conn) or die(mysql_error());
for($i=0; $i < mysql_num_rows($colresult); $i++) {
list($fldname, $fldtype, $fldnull, $fldkey, $flddefault, $fldextra) = mysql_fetch_row($colresult);
$this->fldtypes[$fldname] = $fldtype;
}
mysql_free_result($colresult);
// Calculate the WHERE string and the string for the ADD operation, if the defaultcols option is set.
$this->wherestr = "";
$this->addstr = " () VALUES () ";
if ($this->defaultcols){
foreach ($this->defaultcols as $key => $value) $assignment[] = $key . " = '" . $value . "'";
$wherestr1 = implode(" AND ", $assignment);
$this->wherestr = sprintf(" WHERE %s ", $wherestr1);
$addstr1 = implode(", ", array_keys($this->defaultcols));
$addstr2 = implode(", ", array_map(array ($this, "addquotes"), array_values($this->defaultcols)));
$this->addstr = sprintf(" (%s) VALUES (%s) ", $addstr1, $addstr2);
}
// Do the sorting:
if (isset($_REQUEST["sortcol"])) $this->sortcol = mysql_real_escape_string($_REQUEST["sortcol"]);
if (isset($_REQUEST["sort"])) $this->sort = mysql_real_escape_string($_REQUEST["sort"]);
if (!$this->sortcol) $this->sortcol = $this->idname;
if (!$this->sort) $this->sort = "a";
$this->orderbystr = " ORDER BY " . $this->sortcol . ($this->sort == "d"?" DESC":"");
header( "Expires: Mon, 26 Jul 1997 05:00:00 GMT" ); // disable IE caching
header( "Last-Modified: " . gmdate( "D, d M Y H:i:s" ) . " GMT" );
header( "Cache-Control: no-cache, must-revalidate" );
header( "Pragma: no-cache" );
if (isset($_REQUEST["op"])) $op = $_REQUEST["op"]; else $op = "";
if (isset($_REQUEST["id"])) $id = $_REQUEST["id"];
if (isset($_REQUEST["col"])) $col = $_REQUEST["col"];
if (isset($_REQUEST["value"])) $value = $_REQUEST["value"];
if ($op != "vb" && $op != "vc") header("Content-Type: text/html; charset=".$this->HTMLCharset);
switch ($op) {
case ("a") : if ($this->add_allowed) exit($this->add());
case ("d") : if ($this->delete_allowed) exit($this->delete($id));
case ("u") : exit($this->update($id, $col, rawurldecode($value)));
}
// Get the table in memory
$this->result = $this->select();
$this->num_rows = mysql_num_rows($this->result);
$this->num_fields = mysql_num_fields($this->result);
if ($op == "v") exit($this->view());
if ($op == "vm") exit($this->view_meta());
if ($op == "vb") exit($this->view_bar());
if ($op == "vc") exit($this->view_circle());
if ($op == "vl") exit($this->view_label());
if ($op == "vrn") exit($this->view_rownr());
}
function __destruct() {
if ($this->result) mysql_free_result($this->result);
if ($this->conn) mysql_close($this->conn);
}
private function view_meta(){
$result[0] = $this->num_rows;
$result[1] = $this->num_fields;
$result[2] = $this->idname;
$result[3] = $this->idcolnr;
$result[4] = $this->cols;
$result[5] = $this->cols_numeric;
$result[6] = $this->add_allowed;
$result[7] = $this->delete_allowed;
$result[8] = $this->editablecols;
$result[9] = $this->defaultcols;
$result[10] = $this->sortcol;
$result[11] = $this->sort;
$result[12] = $this->fldtypes;
return(json_encode($result));
}
//
// These protected functions can be overruled if you want to redefine your datasource
//
protected function select(){
$res = mysql_query("SELECT * FROM $this->table" . $this->wherestr . $this->orderbystr, $this->conn) or die(mysql_error());
return ($res);
}
protected function add(){
mysql_query("INSERT INTO $this->table" . $this->addstr, $this->conn) or die(mysql_error());
if (mysql_affected_rows($this->conn) == 1) return(true); else return(false);
}
// Override this function if you want to use (join) query on multiple tables:
protected function metadata(){
$res = mysql_query("SELECT * FROM $this->table LIMIT 1", $this->conn);
return ($res);
}
//
// Private functions only visible within the class
//
private function exists($id, $fld = "") {
$res = $this -> select();
// check field
if ($fld != "") {
$found = false;
while (($field = mysql_fetch_field($res)) != null) {
if ($field->name == $fld) {
$found = true;
break;
}
}
if (!$found) return(false);
}
// check id
for ($i=0; $i < mysql_num_rows($res); $i++) {
$row = mysql_fetch_array($res);
if ($row[$this->idcolnr] == $id) return(true);
}
return(false);
}
private function delete($id){
if (!$this->exists($id)) return(false);
mysql_query("DELETE FROM $this->table WHERE $this->idname='$id'", $this->conn) or die(mysql_error());
if (mysql_affected_rows($this->conn) == 1) return(json_encode(true)); else return(json_encode(false));
}
private function update($id, $fld, $value){
if ((in_array($fld, $this->editablecols)) && $this->exists($id, $fld)) {
mysql_query("UPDATE $this->table SET $fld='$value' WHERE $this->idname='$id'", $this->conn) or die(mysql_error());
if (mysql_affected_rows($this->conn) == 1) {
return("1");
} else {
$res = mysql_query("SELECT $fld FROM $this->table WHERE $this->idname='$id'", $this->conn) or die(mysql_error());
$row = mysql_fetch_array($res);
if ($row[0] == $value)
return("1");
else
return("0");
}
}
return("0");
}
private function view_rownr(){
if (isset($_REQUEST["id"])){
mysql_data_seek($this->result, 0);
for ($i = 0; $i < $this->num_rows; $i++) {
$value = mysql_fetch_array($this->result);
if ($value[$this->idcolnr] == $_REQUEST["id"]) {
return(json_encode($i));
}
}
}
return(json_encode(-1));
}
private function view(){
if ($this->num_rows == 0) return(json_encode(array(null, null)));
if (isset($_REQUEST["cols"])) $cols = explode(",", $_REQUEST["cols"], $this->num_fields);
if (isset($_REQUEST["id"])){
$res = mysql_query("SELECT * FROM $this->table WHERE ".$this->idname." = '".$_REQUEST["id"]."'", $this->conn);
$value = mysql_fetch_array($res);
for ($j = 0; $j < ((isset($cols))?(count($cols)):($this->num_fields)); $j++) {
$row[$j] = $value[((isset($cols))?($cols[$j]):($j))];
}
$arr[0] = $row;
$sqlidarr[0] = $value[$this->idname];
mysql_free_result($res);
}
else {
if (isset($_REQUEST["start"])) $start = $_REQUEST["start"]; else $start = 0;
if (isset($_REQUEST["end"])) $end = $_REQUEST["end"]; else $end= $this->num_rows;
if ($start < 0) $start=0;
if ($end > $this->num_rows) $end = $this->num_rows;
if ($start < $this->num_rows) {
mysql_data_seek($this->result, $start);
for ($i = 0; $i < ($end-$start); $i++) {
$value = mysql_fetch_array($this->result);
for ($j = 0; $j < ((isset($cols))?(count($cols)):($this->num_fields)); $j++) {
$row[$j] = $value[((isset($cols))?($cols[$j]):($j))];
//echo $row[$j];
}
$arr[$i] = $row;
$sqlidarr[$i] = $value[$this->idname];
}
}
}
$result[0] = $sqlidarr;
$result[1] = $arr;
return(json_encode($result));
}
private function view_bar(){
$id = $_REQUEST["id"];
$colname = $_REQUEST["colname"];
$w = $_REQUEST["w"];
$h = $_REQUEST["h"];
if (!isset($this->max)) $this->max = $this->MaxNumber($colname);
$res = mysql_query("SELECT $colname FROM $this->table WHERE $this->idname='$id'", $this->conn) or die(mysql_error());
$data = mysql_fetch_array($res);
$value = $data[0];
mysql_free_result($res);
$im = imagecreatetruecolor ($w, $h) or die("Cannot Initialize new GD image stream");
imagealphablending($im, FALSE);
imagesavealpha($im, TRUE);
$bg = imagecolorallocatealpha($im, 255, 255, 255, 127);
imagefill($im, 0, 0, $bg);
$clr = imagecolorallocatealpha($im, 0, 0, 255, 60);
$height = (int) ceil($h * ($value / $this->max));
imagefilledrectangle ($im, 0, $h-$height, $w, $h, $clr);
Header("Content-type: image/png");
Imagepng($im);
ImageDestroy($im);
}
private function view_circle(){
$id = $_REQUEST["id"];
$colname = $_REQUEST["colname"];
$w = $_REQUEST["w"];
if (!isset($this->max)) $this->max = $this->MaxNumber($colname);
$res = mysql_query("SELECT $colname FROM $this->table WHERE $this->idname='$id'", $this->conn) or die(mysql_error());
$data = mysql_fetch_array($res);
$value = $data[0];
mysql_free_result($res);
$maxr = $w / 2;
$maxopp = pi() * pow($maxr, 2);
$opp = $maxopp * ($value / $this->max);
$r = sqrt($opp / pi());
$width = $r*2;
$im = imagecreatetruecolor ($w, $w) or die("Cannot Initialize new GD image stream");
imagealphablending($im, FALSE);
imagesavealpha($im, TRUE);
$bg = imagecolorallocatealpha($im, 255, 255, 255, 127);
imagefill($im, 0, 0, $bg);
$clr = imagecolorallocatealpha($im, 0, 0, 255, 60);
imagefilledarc ($im, $maxr, $maxr, $width, $width, 0, 360, $clr, IMG_ARC_PIE);
Header("Content-type: image/png");
Imagepng($im);
ImageDestroy($im);
}
private function MaxNumber($colname) {
$max = 0;
mysql_data_seek($this->result, 0);
for ($i=0; $i < $this->num_rows; $i++) {
$row = mysql_fetch_array($this->result);
$max = max($max, $row[$colname]);
}
return($max);
}
function addquotes($str) {
return ("'".$str."'");
}
}
?>
|
Download
