File Source for DBMS_FIELD_DBOPTGRPSELLIST.phpclass

<?php
/**
* Class file DBMS_FIELD_DBOPTGRPSELLIST.phpclass
*
* @project Open CSP-Management
* @package dbms_field
* @category mixed
*
* @author Peter Krebs <p.krebs@lvus.at>
* @copyright © 2002-2005 by Peter Krebs and Landesverlag Unternehmensservice
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
***/
if (empty($GLOBALS['OCSP']['PHPFORMPATH'])) $GLOBALS['OCSP']['PHPFORMPATH']=dirname(__FILE__)."/";
if (!class_exists("DBMS_FIELD_DBSELECTLIST")) {
require_once dirname(__FILE__)."/DBMS_FIELD_DBSELECTLIST.phpclass";
}
/**
* Class DBMS_FIELD_DBOPTGRPSELLIST
*
* @project Open CSP-Management
* @package dbms_field
* @category mixed
*
* @author Peter Krebs <p.krebs@wpus.at>
* @author amicas project <opencsp@amicas.at>
* @copyright © 2002-2005 by WURM & Partner Unternehmensservice
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
* @since pk-05-05-31
*
* @version ap-06-05-02
*
***/
class DBMS_FIELD_DBOPTGRPSELLIST extends DBMS_FIELD_DBSELECTLIST {
// ###########################################################
// OBJECT (CONST) VAR DECLARATION
// ###########################################################
var $className = "SelectDbOptGrpList";
/**
* source file of the class
*
* @var string $classSrcFile
***/
var $classSrcFile=__FILE__;
/**
* database columns
*
* @var string $dbColumns
***/
var $dbColumns="";
/**
* tables
*
* @var string $dbTables
***/
var $dbTables = "";
/**
* group by field
*
* @var string $dbGroupBy
***/
var $dbGroupBy = "";
/**
* order by field
*
* @var string $dbOrderBy
***/
var $dbOrderBy = "";
/**
* @var boolean $navigateGrps use a select for each grp and call reload on Change
* @since ap-06-02-05
***/
var $navigateGrps=FALSE;
// ###########################################################
// OBJECT SETTINGS METHODS
// ###########################################################
/**
* returns an array of field to bie shown in the edit form
* by using editTbl_echoForm
*
* the array is in the form:
*
* <code>
* [_sectionName_]
* [TITLE] -> string
* [_rowName_]
* [FLDLAB] (complete html code with <td></td>)
* [FLDVAL] (complete html code with <td></td>)
* </code>
*
* @param boolean $debug
*
* @returns array
*
* @since ap-06-05-02
*
***/
function editTbl_getFieldEditArr($debug=FALSE) {
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBSELECTLIST::editTbl_getFieldEditArr","",0);
$a_ret=parent::editTbl_getFieldEditArr($debug);
$a_ret['LAYOUT']['FLD_NAVIGATEGRPS']=array(
'FLDLAB'=>"Gruppen Navigation",
'FLDVAL'=>"<input type=\"checkbox\" name=\"navigateGrps\" value=\"1\" ".($this->navigateGrps ? "checked" : "")."> Ja, jede Gruppe in eigenem Select darstellen"
);
$a_ret['SELECT']['FLD_VALQUERY'] =NULL; // unset parent field
$a_ret['SELECT']['FLD_RADCHKBOX'] =NULL; // unset parent field
$a_ret['DBLOOKUP']=array(
'TITLE' =>"DB Lookup Definition",
'FLD_SELFLD'=>array(
'FLDLAB'=>"key,value)",
'FLDVAL'=>"SELECT <input name=\"dbFields\" value=\"".htmlspecialchars($this->dbFields)."\" size=\"30\">"
),
'FLD_FROMFRMFLD'=>array(
'FLDLAB'=>"Table[s]",
'FLDVAL'=>"FROM <input name=\"dbTables\" value=\"".htmlspecialchars($this->dbTables)."\" size=\"30\">"
),
'FLD_WHEREFLD'=>array(
'FLDLAB'=>"Where Template <a href=\"".$GLOBALS['OCSP']['SYSTEMURL']."help/templatevars.html\" target=\"_blank\">?</a>",
'FLDVAL'=>"WHERE <textarea name=\"dbWhere\" rows=\"2\" cols=\"30\">".htmlspecialchars($this->dbWhere)."</textarea>"
),
'FLD_GRPBYFLD'=>array(
'FLDLAB'=>"Gruppierungs Feld(er)",
'FLDVAL'=>"GROUP BY <input name=\"dbGroupBy\" value=\"".htmlspecialchars($this->dbGroupBy)."\" size=\"30\">"
),
'FLD_SHOWLSTSORT'=>array(
'FLDLAB'=>"Soritier Feld (Liste)",
'FLDVAL'=>"ORDER BY <input name=\"dbOrderBy\" value=\"".htmlspecialchars($this->dbOrderBy)."\" size=\"30\">"
),
'FLD_LSTADDVALUES' =>NULL, // placeholder if subclasses need this
'FLD_LSTEDITVALUES'=>NULL // placeholder if subclasses need this
);
return $a_ret;
}
/**
* echos the filed definition form
*
* @param bool $showSize
*
* @version pk-04-10-11
* @version ap-06-05-02
*
***/
function editTblForm($showSize=FALSE) {
$a_FieldEditArr=$this->editTbl_getFieldEditArr($debug);
$this->editTbl_echoForm($a_FieldEditArr,$debug);
}
/**
* save the filed definition form
*
* @return bool
*
* @version ap-06-05-02
*
***/
function save() {
$ret=parent::save();
$this->navigateGrps=(intval($_POST['navigateGrps']) ? TRUE : FALSE);
$this->dbFields=$_POST['dbFields'];
$this->dbTables=$_POST['dbTables'];
$this->dbWhere =$_POST['dbWhere'];
$this->dbGroupBy=$_POST['dbGroupBy'];
$this->dbOrderBy=$_POST['dbOrderBy'];
// set database query for parent methods which use this
// to lookup values
$query="SELECT ".$this->dbFields." FROM ".$this->dbTables;
if (!empty($this->dbWhere)) $query.=" WHERE ".$this->dbWhere;
$query.=" ORDER BY ".$this->dbGroupBy;
if (!empty($this->dbOrderBy)) $query.=",".$this->dbOrderBy;
$this->query=$GLOBALS[$this->getGlobalDBObjIdx()]->checkQuery($query,PQT_SELECT);
return $ret;
}
// ###########################################################
// OBJECT DATA METHODS
// ###########################################################
/**
* returns an array of all group fields or an empty array
* if explode(",",$this->dbGroupBy) fails
*
* @param boolean $debug
*
* @returns array
*
* @since ap-06-05-02
*
* @todo check if we have a complex dbGroupBy with for example CONCAT(xxx,yyy)
*
***/
function getSelectGroups($debug=FALSE) {
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBOPTGRPSELLIST::getSelectGroups","",0);
if ($a_fields=explode(",",$this->dbGroupBy)) {
return $a_fields;
}
return array();
}
/**
* returns the number of group fields used
*
* @param boolean $debug
*
* @returns int
*
* @since ap-06-05-02
*
***/
function nofGrpFlds($debug=FALSE) {
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBOPTGRPSELLIST::nofGrpFlds","",0);
$a_fields=$this>getSelectGroups($debug);
return sizeof($a_fields);
}
/**
* returns an array with key and value column name
* or NULL in case of an error
*
* @param boolean $debug
*
* @returns array
*
* @since ap-06-05-02
*
***/
function getKeyValColumns($debug=FALSE) {
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBOPTGRPSELLIST::getKeyValColumns","",0);
$a_ret=array();
if ($a_fields=explode(",",$this->dbFields,2)) {
$a_ret['MYKEY']=$a_fields[0];
if (empty($a_fields[1])) {
// key == value
$a_ret['MYVAL']=$a_fields[0];
} else {
$a_ret['MYVAL']=$a_fields[1];
}
return $a_ret;
} else {
if ($debug) echoDebug(__FILE__,"<p>Could not explode ".$this->dbFields."</p>");
return NULL;
}
}
/**
* returns the SQL statement for key,values,groups
*
* @param boolean $withOrder omit 'ORDER BY ...'
* @param boolean $debug
*
* @returns string
*
* @since ap-06-05-02
*
***/
function getSQLStmt($withOrder=TRUE,$debug=FALSE) {
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBOPTGRPSELLIST::getSQLStmt","",0);
if ($a_keyval=$this->getKeyValColumns($debug)) {
$s_query="SELECT ".$a_keyval['MYKEY']." AS MYKEY,".$a_keyval['MYVAL']." AS MYVAL";
$a_grps=$this->getSelectGroups($debug);
$i_nofGrps=sizeof($a_grps);
for ($i=0;$i<$i_nofGrps;$i++) {
$s_query.=",".$a_grps[$i]." AS MYGRP".$i;
}
$s_query.=" FROM ".$this->dbTables;
if (!empty($this->dbWhere)) $s_query.=" WHERE ".$this->dbWhere;
if ($withOrder) {
$s_query.=" ORDER BY ";
if (!empty($this->dbGroupBy)) $s_query.=$this->dbGroupBy.",";
if (!empty($this->dbOrderBy)) $s_query.=$this->dbOrderBy.",";
$s_query.=$a_keyval['MYVAL'];
}
return $s_query;
} else {
return NULL;
}
}
// ###########################################################
// OBJECT HTML FORM METHODS
// ###########################################################
/**
* returns a string with the options
*
*
* @param array $aValArr array of selected values
* @param bool $addNULL add the null option (for search)
* @param bool $debug
*
* @return string
*
* @version ap-06-05-02
*
***/
function getOptionList($aValArr,$addNULL=FALSE,$debug=FALSE) {
$debug=($debug || $this->fldDebug); // <pk-04-08-31>
if ($debug) {
echo "<p><b>DBMS_FIELD_DBOPTGRPSELLIST::getOptionList(\$aValArr,$addNULL,...)</b> (".get_class($this)."/".$this->myName.")</p>";
echo "<blockquote><p>\$aValArr</p><pre>".print_r($aValArr,TRUE)."</pre></blockquote>";
}
$s_ret="\n";
if ($addNULL) {
$s_ret.="\t<option value=\"\">".$this->nullLabel."</option>\n";
}
if (!($s_query=$this->getSQLStmt(TRUE,$debug))) {
// ERROR no SQL statement
if ($debug) echoDebug(__FILE__,"<p>NO SQL STATEMENT</p>");
return $s_ret;
}
if (!is_object($GLOBALS[$this->getGlobalDBObjIdx()])) {
$objcmd = "\$DBOBJ = new DB_".$GLOBALS['PROJECT']['DBTYPE'].";";
eval ($objcmd);
$DBOBJ->publicConnect($GLOBALS['PROJECT']['DBCONF']);
$toDisconnect=TRUE;
} else {
$DBOBJ=&$GLOBALS[$this->getGlobalDBObjIdx()];
$toDisconnect=FALSE;
}
if (($DBOBJ->isConnected()) && ($s_query=$DBOBJ->checkQuery($s_query,PQT_SELECT))) {
if ($cursor=$DBOBJ->query($s_query)) {
$a_aktGrp=array();
for ($i=0;$i<$i_nofGrps;$i++) {
$a_aktGrp[$i]['VAL'] =NULL;
$a_aktGrp[$i]['OPEN'] =FALSE;
}
while($a_row=$cursor->fetchArrayFld()) {
$i=0;
while($i<$i_nofGrps) {
if ($a_aktGrp[$i]['VAL'] != $a_row['MYGRP'.$i]) {
$j=$i;
while($j<$i_nofGrps) {
if ($a_aktGrp[$j]['OPEN']) $s_ret.="</optgroup>\n";
$a_aktGrp[$j]=NULL;
$j++;
}
$s_ret.="<optgroup label=\"".$a_row['MYGRP'.$i]."\">\n";
$a_aktGrp[$i]['VAL'] = $a_row['MYGRP'.$i];
$a_aktGrp[$i]['OPEN'] = TRUE;
}
$i++;
}
$s_ret.="\t<option value=\"".$a_row['MYKEY']."\"";
if ((!empty($a_row['MYKEY'])) && in_array($a_row['MYKEY'],$aValArr)) $s_ret .= " selected"; /* <pk-05-02-17 changed if condition to increase performance />*/
$s_ret.=">".$a_row['MYVAL']."</option>\n";
}
for ($i=0;$i<$i_nofGrps;$i++) {
if ($a_aktGrp[$i]['OPEN']) $s_ret.="</optgroup>\n";
}
}
}
if ($toDisconnect) $DBOBJ->disconnect();
return $s_ret;
}
/**
* returns the html code for a grouped select
*
* @param int $nofvals number of values in group
* @param string $grpCol
* @param array $selValues
* @param boolean $debug
*
* @returns string
*
* @since ap-06-05-02
*
* @todo change $GLOBALS['USRDB'] to the propriate DB by using self::getGlobalDBObjIdx()
*
***/
function getGrpSelect(&$nofvals,$grpCol,&$selValues,$debug=FALSE) {
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBOPTGRPSELLIST::getGrpSelect","grpCol=".$grpCol."\nselValues=".print_r($selValues,TRUE),0);
if (!is_array($selValues)) {
$selValues=array();
}
$s_query="SELECT COUNT(*) AS NOFENTRIES,".$grpCol;
$s_query.=" FROM ".$this->dbTables;
if (!empty($this->dbWhere)) $s_query.=" WHERE ".$this->dbWhere;
$s_query.=" GROUP BY $grpCol";
if ($a_keyval=$this->getKeyValColumns($debug)) {
$s_query.=" ORDER BY ";
if (!empty($this->dbGroupBy)) $s_query.=$this->dbGroupBy.",";
if (!empty($this->dbOrderBy)) $s_query.=$this->dbOrderBy.",";
$s_query.=$a_keyval['MYVAL'];
}
if ($debug) echoDebug(__FILE__,"<p>".$s_query."</p>");
$s_tag ="<select name='".$this->myDataArrName."[__".$this->myName."_GRP][".$grpCol."]";
$s_tag.=(($this->multiselect) ? "[]' multiple size='3'" : "' ");
$s_tag.=" ".$this->getOnChangeRelaod($this->myDataArrName).">\n";
$s_firstVal="";$b_valFound=FALSE;
$nofvals=0;
if ($o_cursor=$GLOBALS['USRDB']->query($s_query)) {
while($a_row=$o_cursor->fetchArrayNum()) {
$s_tag.="\t<option value=\"".$a_row[1]."\"";
if (in_array($a_row[1],$selValues)) {
$s_tag.=" selected";
$b_valFound=TRUE;
}
$s_tag.=">".$a_row[1]."</option>\n";
if (!intval($nofvals)) {
$s_firstVal=$a_row[1]; // cache first value in case we only have 1
}
$nofvals++;
}
}
if (!$b_valFound) $selValues[]=$s_firstVal;
if ($nofvals>1) {
$s_tag.=$s_optTag;
} else if ($nofvals==1) {
return "<input type=\"hidden\" name=\"".$this->myDataArrName."[__".$this->myName."_GRP][".$grpCol."]\" value=\"$s_firstVal\">$s_firstVal \n";
}
$s_tag.="</select>\n";
return $s_tag;
}
/**
* returns the html code for the select
*
* @param string $aValue
* @param array $arrName
* @param boolean $debug
*
* @returns string
*
* @since ap-06-05-02
*
* @todo change $GLOBALS['USRDB'] to the propriate DB by using self::getGlobalDBObjIdx()
*
***/
function getSelectTag($aValue,$arrName="DBVAL",$debug=FALSE) {
$debug=($debug || $this->fldDebug); // <pk-04-08-31>
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBOPTGRPSELLIST::getSelectTag","aValue:".print_r($aValue,TRUE),0);
if ((!$this->navigateGrps) || (empty($this->dbGroupBy))) {
// no need to do special handling
return parent::getSelectTag($aValue,$arrName,$debug);
}
$a_grps=$this->getSelectGroups($debug);
if (!($i_nofGrps=sizeof($a_grps))) {
// empty array -> no need for special handling
return parent::getSelectTag($aValue,$arrName,$debug);
}
$a_grpSelValues=array();
if (is_array($_POST[$this->myDataArrName]["__".$this->myName."_GRP"])) {
// reload form
foreach($a_grps as $i_idx => $s_grpCol) {
$a_values=$_POST[$this->myDataArrName]["__".$this->myName."_GRP"][$s_grpCol];
if (is_array($a_values)) {
$a_grpSelValues[$i_idx]=$a_values;
} else if (!empty($a_values)){
$a_grpSelValues[$i_idx][]=$a_values;
}
}
if ($debug) echoDebug(__FILE__,"<pre>".print_r($a_grpSelValues,TRUE)."</pre>");
} else if (!empty($aValue)) {
// select value path
if (!($valArr=explode($this->mySeperator,$aValue))) { // <pk-06-02-10>
$valArr=array();
$valArr[]=$aValue;
}
if (is_array($valArr)) {
$a_keyval=$this->getKeyValColumns($debug);
if ($s_query=$this->getSQLStmt(FALSE,$debug)) {
if (!strstr($s_query,"WHERE")) $s_query.=" WHERE ";
else $s_query.=" AND ";
$s_query.=$a_keyval['MYKEY']." IN (";
$c_sep="";
foreach($valArr as $s_val) {
$s_query.=$c_sep.$GLOBALS['USRDB']->qs_getSlashedValue($s_val);
$c_sep=",";
}
$s_query.=")";
}
if ($debug) echoDebug(__FILE__,"<p>value select: ".$s_query."</p>");
if ($o_cursor=$GLOBALS['USRDB']->query($s_query)) { // todo change fixed DB connection
while($a_row=$o_cursor->fetchArrayFld()) {
for ($i=0;$i<$i_nofGrps;$i++) {
if ((!is_array($a_grpSelValues[$i])) || (!in_array($a_row['MYGRP'.$i],$a_grpSelValues[$i]))) {
$a_grpSelValues[$i][]=$a_row['MYGRP'.$i];
}
}
}
}
}
}
$s_ret="";
if ($debug) echoDebug(__FILE__,"<pre>".print_r($a_grps,TRUE)."</pre>");
for ($i=0;$i<$i_nofGrps;$i++) {
$i_nofGrpsVals=0;
$s_ret.=$this->getGrpSelect($i_nofGrpsVals,$a_grps[$i],$a_grpSelValues[$i],$debug);
}
if ($debug) echoDebug(__FILE__,"<pre>".print_r($a_grpSelValues,TRUE)."</pre>");
$i_nofRows=0;
if ($a_keyval=$this->getKeyValColumns($debug)) {
$s_query="SELECT ".$a_keyval['MYKEY']." AS MYKEY,".$a_keyval['MYVAL']." AS MYVAL";
$s_query.=" FROM ".$this->dbTables;
if (!empty($this->dbWhere)) {
$s_query.=" WHERE ".$this->dbWhere;
} else {
$s_query.=" WHERE 1=1 "; // dummy to use AND
}
for ($i=0;$i<$i_nofGrps;$i++) {
$s_query.=" AND ".$a_grps[$i]." IN (";
$c_sep="";
foreach($a_grpSelValues[$i] as $s_key) {
$s_query.=$c_sep.$GLOBALS['USRDB']->qs_getSlashedValue($s_key);
$c_sep=",";
}
$s_query.=")";
}
if ($a_keyval=$this->getKeyValColumns($debug)) {
$s_query.=" ORDER BY ";
if (!empty($this->dbGroupBy)) $s_query.=$this->dbGroupBy.",";
if (!empty($this->dbOrderBy)) $s_query.=$this->dbOrderBy.",";
$s_query.=$a_keyval['MYVAL'];
}
if ($o_cursor=$GLOBALS['USRDB']->query($s_query)) {
$s_detsel = "<select name='".$arrName."[".$this->myName."]";
$s_detsel .=(($this->multiselect) ? "[]' multiple " : "' ");
$s_detsel .= $this->selectAddTag;
if (intval($this->selectsize)) {
$s_detsel.=" size=\"".intval($this->selectsize)."\" ";
}
if ($this->reloadFormOnChange) {
$s_detsel.=" ".$this->getOnChangeRelaod($arrName);
}
if (intval($this->tabIndex)) { // <pk-05-08-11>
$s_detsel.=" tabindex=".intval($this->tabIndex);
} // </pk-05-08-11>
$s_detsel .= ">\n";
while($a_row=$o_cursor->fetchArrayFld()) {
$s_detsel.="\t<option value=\"".$a_row['MYKEY']."\">".$a_row['MYVAL']."</option>\n";
$i_nofRows++;
}
$s_detsel.="</select>\n";
}
}
if ($i_nofRows>0) {
$s_ret.=$s_detsel;
}
$s_ret.=$this->getReloadButton($arrName);
return $s_ret;
}
// ###########################################################
// SEARCH FORM METHODS
// ###########################################################
// ###########################################################
// OBJECT HTML FORM METHODS
// ###########################################################
// ###########################################################
// OBJECT VALUE CHECK METHODS
// ###########################################################
} // end class DBMS_FIELD_DBOPTGRPSELLIST
?>

Source for file DBMS_FIELD_DBOPTGRPSELLIST.phpclass