File Source for DBMS_FIELD_DBLOOKUPSELECTLIST.phpclass

<?php
/**
* Class file DBMS_FIELD_DBLOOKUPSELECTLIST.phpclass
*
* @project Open CSP-Management
* @package dbms_field
* @category any
*
* @author Peter Krebs <pitlinz@users.sourceforge.net>
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
* @version $Id: DBMS_FIELD_DBLOOKUPSELECTLIST.phpclass,v 1.18 2008/12/03 21:32:40 pitlinz Exp $
*
*/
// ---------------------------------------------------------
// requirements
// ---------------------------------------------------------
pcf_require_class('DBMS_FIELD_DBSELECTLIST',dirname(__FILE__) . "/");
/**
* Class DBMS_FIELD_DBLOOKUPSELECTLIST
*
* handels select fields (<SELECT>) where the values are selected form the database
*
* @project Open CSP-Management
* @package dbms_field
* @category any
*
* @author Peter Krebs <pitlinz@users.sourceforge.net>
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
* @version pk-06-02-06
* @version pk-06-02-10
* @version pk-07-08-31
*
* @version $Id: DBMS_FIELD_DBLOOKUPSELECTLIST.phpclass,v 1.18 2008/12/03 21:32:40 pitlinz Exp $
*
*/
class DBMS_FIELD_DBLOOKUPSELECTLIST extends DBMS_FIELD_DBSELECTLIST
{
// ---------------------------------------------------------------------------
// constants
// ---------------------------------------------------------------------------
/**
* @constant string CLASS_SRC_FILE
*/
const CLASS_SRC_FILE = __FILE__;
// ---------------------------------------------------------------------------
// class (static)
// ---------------------------------------------------------------------------
/*** class vars ------------------------------------------------------ */
/*** class methods --------------------------------------------------- */
// ---------------------------------------------------------------------------
// object vars
// ---------------------------------------------------------------------------
/*** compostion --------------------------------------------------- */
/*** attributes -------------------------------------------------- */
/**
* @deprecated
*/
protected $classSrcFile=__FILE__;
/**
* @var string $lookupTable
* @access protected
***/
protected $lookupTable;
/**
* @var string $lookupShowField
* @since pk-05-11-20
* @access protected
***/
protected $lookupShowField;
/**
* @var string $lookupShowLstField
* @since pk-06-04-26
* @access protected
***/
protected $lookupShowLstField;
/**
* @var string $lookupField
* @access protected
*/
protected $lookupField;
/**
* @var string $lookupWhere
* @access protected
***/
protected $lookupWhere="";
/**
* @var string $lookupSort command string after 'ORDER BY' if empty
* @since pk-06-04-29
* @access protected
*/
protected $lookupSort="";
/**
* @var boolean $allowAddValues
* the class DOES NOT CARE if the added values are in the lookup table lookupShowField is used
* @access protected
***/
protected $allowAddValues=False;
/**
* @var boolean $allowEditValues
* @since pk-06-02-06
* @access protected
***/
protected $allowEditValues=False;
// ---------------------------------------------------------------------------
// factory / construct
// ---------------------------------------------------------------------------
// ---------------------------------------------------------------------------
// getter / setter
// ---------------------------------------------------------------------------
protected function getClassCacheKey()
{
$str_key = $this->lookupTable . $this->lookupField . $this->lookupShowField . $this->lookupShowLstField . $this->lookupSort . $this->lookupWhere;
return md5($str_key);
}
// ---------------------------------------------------------------------------
// 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
*
* @global $OCSP_CONF
*
* @returns array
*
* @since pk-06-04-26
* @version pk-06-04-29
*
***/
function editTbl_getFieldEditArr($debug=False)
{
global $OCSP_CONF;
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBSELECTLIST::editTbl_getFieldEditArr","",0);
$a_ret=parent::editTbl_getFieldEditArr($debug);
$a_ret['SELECT']['FLD_VALQUERY'] =NULL; // unset parent field
//$a_ret['SELECT']['FLD_RADCHKBOX'] =NULL;
$a_ret['DBLOOKUP']=array(
'TITLE' =>"DB Lookup Definition",
'FLD_LUTBL'=>array(
'FLDLAB'=>"Werte Tabelle"
),
'FLD_VALFLD'=>array(
'FLDLAB'=>"Wert Feld",
'FLDVAL'=>"<input name=\"lookupField\" value=\"".htmlspecialchars($this->lookupField)."\" size=\"30\">"
),
'FLD_SHOWFRMFLD'=>array(
'FLDLAB'=>"Anzeige Feld (Formular Auswahl)",
'FLDVAL'=>"<input name=\"lookupShowField\" value=\"".htmlspecialchars($this->lookupShowField)."\" size=\"30\">"
),
'FLD_SHOWLSTFLD'=>array(
'FLDLAB'=>"Anzeige Feld (Liste)",
'FLDVAL'=>"<input name=\"lookupShowLstField\" value=\"".htmlspecialchars($this->lookupShowLstField)."\" size=\"30\">"
),
'FLD_WHEREFLD'=>array(
'FLDLAB'=>"Where Template <a href=\"".OCSP_CONF::getInstance()->getValue('SYSTEMURL')."help/templatevars.html\" target=\"_blank\">?</a>",
'FLDVAL'=>"<textarea name=\"lookupWhere\" rows=\"2\" cols=\"30\">".htmlspecialchars($this->lookupWhere)."</textarea>"
),
'FLD_SHOWLSTSORT'=>array(
'FLDLAB'=>"Sortier Feld (Liste)",
'FLDVAL'=>"ORDER BY <input name=\"lookupSort\" value=\"".htmlspecialchars($this->lookupSort)."\" size=\"30\">"
),
'FLD_LSTADDVALUES'=>array(
'FLDLAB'=>"Werte hinzufügen",
'FLDVAL'=>"
<input name=\"allowAddValues\" type=\"checkbox\"".($this->allowAddValues ? "checked" : "")." value=\"1\">ja<br />
<b>ACHTUNG</b> neue Werte werden nicht automatisch in die Lookup Tabelle eingetragen.
"
),
'FLD_LSTEDITVALUES'=>NULL // placeholder if subclasses need this
);
$a_ret['DBLOOKUP']['FLD_LUTBL']['FLDVAL']="<select name=\"lookupTable\"
onchange=\"jQuery.getScript('" . OCSP_CONF::getInstance()->getValue('ADMINURL') . "/dbms/form_rpc.php?cmd=fldLookup_changeTable&FRM_ID=" . $this->getFrmId() . "&fld=" . $this->getKey() . "&table=' + this.value);\">";
$found=False;
if ($a_tblLst=$GLOBALS['USRDB']->getDBTblList()) {
foreach($a_tblLst as $s_tbl) {
if ($s_tbl == $this->lookupTable) {
$a_ret['DBLOOKUP']['FLD_LUTBL']['FLDVAL'].="\t<option value=\"$s_tbl\" selected>$s_tbl</option>\n";
$found=TRUE;
} else {
$a_ret['DBLOOKUP']['FLD_LUTBL']['FLDVAL'].="\t<option value=\"$s_tbl\">$s_tbl</option>\n";
}
}
}
if (!$found) {
$a_ret['DBLOOKUP']['FLD_LUTBL']['FLDVAL'].="\t<option value=\"".$this->lookupTable."\" selected>NOT IN DB[".$this->lookupTable."]</option>\n";
}
$a_ret['DBLOOKUP']['FLD_LUTBL']['FLDVAL'].="</select>";
$a_ret['DBLOOKUP']['FLD_LUTBL']['FLDVAL'].="<a onClick=\"
var tblName=getSelectedOptionByName(document.forms['frmEdit'],'lookupTable');
if (tblName=='') {
alert('Wählen Sie zuerst eine Tabelle aus');
} else {
var link='".OCSP_CONF::getInstance()->getValue('ADMINURL')."phpmyadmin/tbl_properties_structure.php?db=".$GLOBALS['USRDB']->getMyDBName()."';
link=link+'&table='+tblName;
openLinkWnd(link);
}
\" title=\"Tabelle in phpMyAdmin zeigen\">
<img src=\"".OCSP_CONF::getInstance()->getValue('SYSIMGURL')."icons-16x16/b_browse.png\" border=\"0\" class=\"button\">
</a>";
return $a_ret;
}
/**
* ensures that no ; is in $str used in a sql statements
* @param string
* @return string
*/
function DBcheckEditInput($str) {
$ret="";
if (!empty($str)) {
if ($arr=explode(';',$str)) {
$ret=$arr[0];
}
}
return $ret;
}
/**
* save the filed definition form
*
* @returns boolean
*
* @version pk-04-12-31
* @version pk-06-02-06 popUp_frmId
* @version pk-06-04-29 lookupSort
*
***/
function save()
{
$ret=parent::save();
$this->lookupTable = (isset($_POST['lookupTable']) ? $this->DBcheckEditInput($_POST['lookupTable']) : $this->lookupTable);
$this->lookupField = (isset($_POST['lookupField']) ? $this->DBcheckEditInput($_POST['lookupField']) : $this->lookupField);
$this->lookupShowField= (isset($_POST['lookupShowField']) ? $this->DBcheckEditInput($_POST['lookupShowField']) : $this->lookupShowField);
$this->lookupShowLstField= (isset($_POST['lookupShowLstField'])? $this->DBcheckEditInput($_POST['lookupShowLstField']) : $this->lookupShowLstField);
$this->lookupWhere = (isset($_POST['lookupWhere']) ? $this->DBcheckEditInput($_POST['lookupWhere']) : $this->lookupWhere);
$this->lookupSort = (isset($_POST['lookupSort']) ? $this->DBcheckEditInput($_POST['lookupSort']) : $this->lookupSort);
$this->allowAddValues = ((isset($_POST['allowAddValues']) && intval($_POST['allowAddValues'])) ? TRUE : False );
$this->allowEditValues = ((isset($_POST['allowEditValues']) && intval($_POST['allowEditValues'])) ? TRUE : False );
return $ret;
}
// -----------------------------------------------------------
// DB value methods
// -----------------------------------------------------------
/**
* returns the database query
*
* @param boolean $debug
*
* @global ${$this->myDataArrName}
*
* @return string
*
* @requires pcf_templates.phpinc
*
* @since pk-07-09-03
* @version pk-08-04-15
*
* @todo DBMS_FIELD_DBLOOKUPSELECTLIST::getMyValueQuery() remove inline SQL
*/
function getMyValueQuery($debug=False)
{
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBLOOKUPSELECTLIST::getMyValueQuery()");
if (empty($this->lookupShowField)) $this->lookupShowField=$this->lookupField;
$str_query ="SELECT ".$this->lookupField. " AS KEYCOL,".$this->lookupShowField. " as VALUECOL FROM ".$this->lookupTable;
$str_query.=$this->_getMyValueWhere($debug);
if (empty($this->lookupSort))
{
$str_query.=" ORDER BY ".$this->lookupField;
} else {
$str_query.=" ORDER BY ".$this->lookupSort;
}
if ($debug) echoDebugLine(__FILE__,__LINE__,"value query: <br />$str_query");
if (empty($this->progValueCacheId))
{
$this->progValueCacheId = md5($str_query);
}
return $str_query;
}
/**
* returns the where condition for $this->getMyValueQuery
*
* @param boolean $debug
*
* @return string
*
* @since pk-08-12-03
*/
protected function _getMyValueWhere($debug)
{
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBLOOKUPSELECTLIST::getMyValueWhere();");
if (!empty($this->lookupWhere)) // <pk-05-08-10>
{
pcf_require_class('OCSP_TEMPLATE',"common/");
global ${$this->myDataArrName};
return " WHERE " . OCSP_TEMPLATE::parse($this->lookupWhere,${$this->myDataArrName},$debug);
}
return "";
}
/**
* sets populates $this->myValues array from the database
*
* @param string $arrName
* @param boolean $debug
*
* @version pk-04-10-11
* @version pk-05-08-10
* @version pk-08-06-13 use OCSP_OBJ::defaultReadDBObj()
*/
function setMyValues($arrName=NULL,$debug=False)
{
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBLOOKUPSELECTLIST::setMyValues();");
$this->query = $this->getMyValueQuery($debug);
return parent::setMyValues($arrName,$debug);
}
// ###########################################################
// OBJECT HTML FORM METHODS
// ###########################################################
/**
* returns the html code for the new button
*
* @param boolean $debug
*
* @returns string
*
* @since pk-06-04-26
*/
function getNewBtn($debug=False) {
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBLOOKUPSELECTLIST::getNewBtn","arrName: $arrName",0);
$s_ret ="<!--\n\t\t _______ ADD VALUES BUTTON _______ \n";
$s_ret.="\t\t--> <a name=\"".$this->getMyFormName()."_".$this->myName."\" href=\"#".$this->getMyFormName()."_".$this->myName."\" onClick=\"\n"; // <pk-05-12-01>
$s_ret.="\t\t if (document.forms['".$this->getMyFormName()."']) frmObj=document.forms['".$this->getMyFormName()."'];\n";
$s_ret.="\t\t else frmObj=document.forms[".$this->myForm->getHTMLName()."];\n";
$s_ret.="\t\t if (selObj=findElement(frmObj,'".$this->myDataArrName."[".$this->myName."]"."')) { \n";
$s_ret.="\t\t if ((newVal=prompt('".$this->label."','')) && (newVal != '')) { \n";
$s_ret.="\t\t id=selObj.length;\n";
$s_ret.="\t\t opt = new Option(newVal,newVal,false,true);\n";
$s_ret.="\t\t selObj.options[id]=opt;\n";
$s_ret.="\t\t }\n";
$s_ret.="\t\t }\n";
$s_ret.="\t\t \"><img src=\"" . OCSP_CONF::getInstance()->getValue('SYSIMGURL') . "icons-16x16/filenew.png\" border=\"0\" alt=\"" . t_admin("neuer Eintrag") . "\" align=\"middle\">";
$s_ret.="</a><!--\n";
$s_ret.="\t\t _______ ADD VALUES BUTTON _______ \n-->";
return $s_ret;
}
/**
* returns the html code for the select
*
* @param string $aValue
* @param array $arrName
* @param boolean $debug
*
* @returns string
*
* @version pk-05-12-01
* @version pk-06-04-26 getNewBtn()
*
**/
function getInputTag($aValue,$arrName="DBVAL",$debug=False) {
$debug=($debug || $this->fldDebug);
if (!empty($arrName)) $this->myDataArrName=$arrName;
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBLOOKUPSELECTLIST::getInputTag(\$aValue,$arrName)","aValue=$aValue");
$str_ret=parent::getInputTag($aValue,$arrName,$debug);
if (!$this->showAsCheckRadio)
{
if ($this->allowAddValues)
{
$str_ret .= $this->getNewBtn($debug);
}
if (method_exists($this,"getZoomBtn"))
{
$str_ret .= $this->getZoomBtn($debug);
}
}
return $str_ret;
}
/**
* returns the db value for a value
*
* @param string $aValue
* @param boolean $debug
*
* @global $OCSP_VAL
*
* @return string
*
* @since pk-06-01-24
*
**/
function getDBValue($aValue,$mode=FRM_MODE_READONLY,$debug=False) {
$debug=($debug || $this->fldDebug);
if ($debug) echoDebugMethod(__FILE__,get_class($this),"DBMS_FIELD_DBLOOKUPSELECTLIST::getDBValue()",print_r($aValue,TRUE));
if ($this->lookupShowField == $this->lookupField) {
// no need to lookup in the database
return $aValue;
}
if (!empty($aValue) || strlen(strval($aValue)) > 0)
{
global $OCSP_VAL;
if (($mode==FRM_MODE_LIST) && (!empty($this->lookupShowLstField)))
{
$str_showCol=$this->lookupShowLstField;
} else {
$str_showCol=$this->lookupShowField;
}
$str_cacheKey=preg_replace('/[^a-z0-9A-Z_\-\.]/i','_',$str_showCol);
if (isset($OCSP_VAL['DBMS_FIELD_DBLOOKUP_CACHE'][$this->getKey()][$str_cacheKey][$aValue]))
{
return $OCSP_VAL['DBMS_FIELD_DBLOOKUP_CACHE'][$this->getKey()][$str_cacheKey][$aValue];
}
$obj_myDB=$this->myForm->getDBObj();
$s_query ="SELECT ".$str_showCol." FROM ".$this->lookupTable;
$s_query.=" WHERE ".$this->lookupField."=".$obj_myDB->qs_getSlashedValue($aValue);
if ($debug) echoDebugLine(__FILE__,__LINE__,"<p>$s_query</p>",1);
if ($s_ret=$obj_myDB->quickQuery($s_query,0)) {
// value found in the database
$OCSP_VAL['DBMS_FIELD_DBLOOKUP_CACHE'][$this->getKey()][$str_cacheKey][$aValue]=$s_ret;
return $s_ret;
} else {
// value not found return the value
return $aValue;
}
} else {
return $aValue;
}
}
/**
*
* returns the html code for the readonly field
*
* if is_array($aValue) a list (<ul>) is returned
*
* @param mixed $aValue
* @param string $arrName
* @param boolean $debug
*
* @returns string
*
* @version pk-06-01-24
* @version pk-06-02-10
* @version pk-06-04-26
* @version pk-06-04-14
*
**/
function getScreenValue($aValue,$arrName="DBVAL",$debug=False) {
$debug=($debug || $this->fldDebug || $this->fldDbgGetValue);
if ($debug) echoDebugMethod(__FILE__,__LINE__,"DBMS_FIELD_DBLOOKUPSELECTLIST::getScreenValue($aValue,$arrName,...)",0);
if ($this->lookupField == $this->lookupShowField) {
// no need to do db lookups
return parent::getScreenValue($aValue,$arrName,$debug);
}
if (empty($this->mySeperator)) $this->mySeperator="\n"; // <pk-06-09-14 />
if (!is_array($aValue) && (strstr($aValue,$this->mySeperator))) { // <pk-06-02-10>
$aValue=explode($this->mySeperator,$aValue);
}
if (is_array($aValue)) {
$ret= "<ul>";
foreach($aValue as $val) {
$ret.="<li>".htmlspecialchars($this->getDBValue($val,FRM_MODE_READONLY,$debug))."</li>";
}
$ret.="</ul>";
return $ret;
} else {
return htmlspecialchars($this->getDBValue($aValue,FRM_MODE_READONLY,$debug));
}
}
/**
*
* returns the html code for the readonly field in a list (table col)
*
* if is_array($aValue) a list (<ul>) is returned
*
* @param mixed $aValue
* @param string $arrName
* @param boolean $debug
*
* @returns string
*
* @since pk-06-04-26
* @version pk-06-05-29 (lstValueIsHtml added)
*
**/
function getListValue($aValue,$arrName="DBVAL",$debug=False) {
$debug=($debug || $this->fldDebug);
if ($debug) echoDebug(__FILE__,"<hr /><p><b>DBMS_FIELD_DBLOOKUPSELECTLIST::getListValue($aValue,$arrName,...)</b></p>",0);
if (empty($aValue) && ($this->nullToZero)) { // <pk-06-10-09>
return $this->nullLabel;
}
if ($this->lookupField == $this->lookupShowLstField) {
// no need to do db lookups
return parent::getListValue($aValue,$arrName,$debug);
}
if (empty($this->mySeperator)) $this->mySeperator="\n";
if (!empty($aValue) && !is_array($aValue) && (strstr($aValue,$this->mySeperator))) {
$aValue=explode($this->mySeperator,$aValue);
}
if (is_array($aValue)) {
$ret= "<ul>";
foreach($aValue as $val) {
$ret.="<li>";
if ($this->lstValueIsHtml) $ret.=$this->getDBValue($val,FRM_MODE_LIST,$debug); // <pk-06-05-29 />
else $ret.=htmlspecialchars($this->getDBValue($val,FRM_MODE_LIST,$debug));
$ret.="</li>";
}
$ret.="</ul>";
return $ret;
} else {
if ($this->lstValueIsHtml) return $this->getDBValue($aValue,FRM_MODE_LIST,$debug); // <pk-06-05-29 />
else return htmlspecialchars($this->getDBValue($aValue,FRM_MODE_LIST,$debug));
}
}
// ###########################################################
// SEARCH FORM METHODS
// ###########################################################
// ###########################################################
// OBJECT HTML FORM METHODS
// ###########################################################
// ###########################################################
// OBJECT VALUE CHECK METHODS
// ###########################################################
} // end class DBMS_FIELD_DBLOOKUPSELECTLIST
?>

Source for file DBMS_FIELD_DBLOOKUPSELECTLIST.phpclass