--- /dev/null
+<?php\r
+//**********************************\r
+// Rico: GENERIC TABLE/VIEW EDITOR\r
+// By Matt Brown\r
+//**********************************\r
+\r
+class TableEditTable {\r
+ var $TblName;\r
+ var $alias;\r
+ var $arFields;\r
+ var $arData;\r
+ var $arColInfo;\r
+}\r
+\r
+class TableEditClass {\r
+\r
+ // public properties\r
+ var $action;\r
+ var $TableFilter;\r
+ var $options;\r
+ var $AutoInit;\r
+ var $CurrentField;\r
+ var $LookupField;\r
+ var $SvrOnly;\r
+ var $gridID;\r
+ var $formVar;\r
+ var $gridVar;\r
+ var $bufferVar;\r
+ var $optionsVar;\r
+ var $DefaultSort;\r
+ var $convertCharSet; // set to true if database is ISO-8859-1 encoded, false if UTF-8\r
+ var $sessions;\r
+\r
+ // private properties\r
+ var $Panels=array();\r
+ var $objDB;\r
+ var $CurrentPanel;\r
+ var $xhtmlcloser;\r
+ var $ErrorFlag;\r
+ var $ErrorMsg;\r
+ var $MainTbl;\r
+ var $Tables=array();\r
+ var $TableCnt;\r
+ var $Fields=array();\r
+ var $FieldCnt;\r
+ var $oParseMain;\r
+\r
+ //*************************************************************************************\r
+ // Class Constructor\r
+ //*************************************************************************************\r
+ function TableEditClass() {\r
+ if (is_object($GLOBALS['oDB'])) {\r
+ $this->objDB=&$GLOBALS['oDB']; // use oDB global as database connection, if it exists\r
+ }\r
+ $this->options=array();\r
+ $this->options["TableSelectNew"]="___new___";\r
+ $this->options["TableSelectNone"]="";\r
+ $this->options["canAdd"]=true;\r
+ $this->options["canEdit"]=true;\r
+ $this->options["canDelete"]=true;\r
+ $this->options["ConfirmDelete"]=true;\r
+ $this->options["ConfirmDeleteCol"]=-1;\r
+ $this->options["DebugFlag"]=isset($_GET["debug"]);\r
+ $this->options["prefetchBuffer"]=true;\r
+ $this->options["PanelNamesOnTabHdr"]=true;\r
+ $this->options["highlightElem"]="menuRow";\r
+\r
+ $this->SvrOnly=array();\r
+ $this->SvrOnly["DropDownSelect"]=1;\r
+ $this->SvrOnly["SelectSql"]=1;\r
+ $this->SvrOnly["SelectFilter"]=1;\r
+ $this->SvrOnly["Formula"]=1;\r
+ $this->SvrOnly["TableIdx"]=1;\r
+ $this->SvrOnly["AddQuotes"]=1;\r
+ $this->SvrOnly["FilterFlag"]=1;\r
+ $this->SvrOnly["XMLprovider"]=1;\r
+\r
+ $this->xhtmlcloser=">";\r
+ $this->FieldCnt=-1;\r
+ $this->CurrentPanel=-1;\r
+ $this->TableCnt=-1;\r
+ $this->AutoInit=true;\r
+ $this->formView=true;\r
+ $this->sessions=isset($_SESSION);\r
+ $this->ErrorFlag=false;\r
+ $this->ErrorMsg="";\r
+ $this->convertCharSet=false;\r
+ $this->oParseMain= new sqlParse();\r
+ $this->oParseMain->Init();\r
+ }\r
+\r
+ // -------------------------------------------------------------\r
+ // Class Destructor (only called if php5)\r
+ // -------------------------------------------------------------\r
+ function __destruct() {\r
+ for ($i=0; $i<count($this->Fields); $i++) {\r
+ $this->Fields[$i]=NULL;\r
+ }\r
+ $this->options=NULL;\r
+ $this->SvrOnly=NULL;\r
+ }\r
+\r
+ // returns field number if successful, false if error\r
+ function AddEntryField($ColumnName, $Heading, $EntryTypeCode, $DefaultValue) {\r
+ if (!in_array($EntryTypeCode, array("S", "N", "R", "H", "D", "DT", "I", "F", "B", "T", "TA", "SL", "RL", "CL", "tinyMCE"))) {\r
+ $this->TableEditError("invalid EntryTypeCode in TableEditClass");\r
+ return false;\r
+ }\r
+ $this->IncrCurrentField();\r
+ $this->CurrentField["ColName"]=$ColumnName;\r
+ $this->CurrentField["Hdg"]=$Heading;\r
+ $this->CurrentField["EntryType"]=$EntryTypeCode;\r
+ $this->CurrentField["ColData"]=$DefaultValue;\r
+ switch ($EntryTypeCode) {\r
+\r
+ case "D":\r
+ $this->CurrentField["type"]="date";\r
+ break;\r
+\r
+ case "DT":\r
+ $this->CurrentField["type"]="datetime";\r
+ break;\r
+\r
+ case "TA":\r
+ case "tinyMCE":\r
+ $this->CurrentField["TxtAreaRows"]=4;\r
+ $this->CurrentField["TxtAreaCols"]=80;\r
+ break;\r
+\r
+ case "R":\r
+ case "RL":\r
+ $this->CurrentField["RadioBreak"]="<br".$this->xhtmlcloser;\r
+ break;\r
+\r
+ case "H":\r
+ $this->CurrentField["visible"]=false;\r
+ break;\r
+ }\r
+ $s=$this->Tables[$this->MainTbl]->alias.".".$ColumnName;\r
+ if (in_array($EntryTypeCode, array("B", "T", "TA", "tinyMCE"))) $s="rtrim(" . $s . ")";\r
+ $this->oParseMain->AddColumn($s, "rico_col".$this->FieldCnt);\r
+ return $this->FieldCnt;\r
+ }\r
+\r
+ // returns field number if successful, false if error\r
+ function AddEntryFieldW($ColumnName, $Heading, $EntryTypeCode, $DefaultValue, $Width) {\r
+ $retval=$this->AddEntryField($ColumnName, $Heading, $EntryTypeCode, $DefaultValue);\r
+ if ($retval!==false) $this->CurrentField["width"]=$Width;\r
+ return $retval;\r
+ }\r
+ \r
+ // $DescColName is optional - pass empty if not used\r
+ function AddLookupField($CodeColName,$DescColName,$CodeHdg,$DisplayHdg,$EntryTypeCode,$DefaultValue,$sql) {\r
+ $retval=$this->AddEntryField($CodeColName,$CodeHdg,$EntryTypeCode,$DefaultValue);\r
+ $this->CurrentField["visible"]=false;\r
+ $this->CurrentField["SelectSql"]=$sql;\r
+ if (!empty($DescColName)) $this->CurrentField["DescriptionField"]=$this->ExtFieldId($this->FieldCnt+1);\r
+ $this->LookupField=&$this->Fields[$this->FieldCnt];\r
+ $oParseLookup= new sqlParse();\r
+ $alias="t" . $this->FieldCnt;\r
+ $oParseLookup->ParseSelect($sql);\r
+ if (count($oParseLookup->arSelList) == 2) {\r
+ $codeField=$oParseLookup->arSelList[0];\r
+ $descField=$oParseLookup->arSelList[1];\r
+ $s="left join ".$oParseLookup->FromClause." ".$alias." on t.".$CodeColName."=".$alias.".".str_replace("%alias%","",str_replace("%aliasmain%","",$codeField));\r
+ if (!empty($oParseLookup->WhereClause)) $s.=" and " . str_replace("%alias%",$alias.".",$oParseLookup->WhereClause);\r
+ $this->oParseMain->AddJoin($s);\r
+ $this->IncrCurrentField();\r
+ $this->CurrentField["ColName"]="Lookup_".$this->FieldCnt;\r
+ $this->CurrentField["Hdg"]=$DisplayHdg;\r
+ if (!empty($DescColName)) {\r
+ $descField=$this->Tables[$this->MainTbl]->alias & "." & $DescColName;\r
+ $this->CurrentField["ColName"]=$DescColName;\r
+ $this->CurrentField["FormView"]="hidden";\r
+ $this->CurrentField["EntryType"]="T";\r
+ } else if (preg_match("/^\\w+$/",$descField)) {\r
+ $descField=$alias.".".$descField;\r
+ } else {\r
+ $descField=str_replace("%alias%",$alias . ".",str_replace("%aliasmain%","t.",$descField));\r
+ }\r
+ $this->oParseMain->AddColumn($descField, "rico_col".$this->FieldCnt);\r
+ }\r
+ else {\r
+ $this->TableEditError("Invalid lookup query (".$sql.")");\r
+ }\r
+ }\r
+\r
+ // returns field number if successful, false if error\r
+ function AddCalculatedField($ColumnFormula, $Heading) {\r
+ $this->IncrCurrentField();\r
+ if (substr($ColumnFormula,0,1) != "(") {\r
+ $ColumnFormula="(".$ColumnFormula.")";\r
+ }\r
+ $this->CurrentField["ColName"]="Calc_".$this->FieldCnt;\r
+ $this->CurrentField["Hdg"]=$Heading;\r
+ $this->oParseMain->AddColumn($ColumnFormula, "rico_col".$this->FieldCnt);\r
+ return $this->FieldCnt;\r
+ }\r
+\r
+ function AddPanel($PanelHeading) {\r
+ $this->CurrentPanel++;\r
+ $this->Panels[$this->CurrentPanel]=$PanelHeading;\r
+ }\r
+\r
+ function DefineAltTable($AltTabName, $arFieldList, $arFieldData) {\r
+ $this->TableCnt++;\r
+ $this->Tables[$this->TableCnt]= new TableEditTable();\r
+ $_withval=$this->Tables[$this->TableCnt];\r
+ $_withval->TblName=$AltTabName;\r
+ $_withval->alias="a" . $this->TableCnt;\r
+ $_withval->arFields=$arFieldList;\r
+ $_withval->arData=$arFieldData;\r
+ if (count($_withval->arFields) != count($_withval->arData)) {\r
+ $this->TableEditError("# of fields does not match # of data entries supplied for table ".$AltTabName);\r
+ return false;\r
+ }\r
+ return $this->TableCnt;\r
+ }\r
+\r
+ function IncrCurrentField() {\r
+ $this->FieldCnt++;\r
+ $this->Fields[$this->FieldCnt]= array();\r
+ $this->CurrentField= &$this->Fields[$this->FieldCnt];\r
+ $this->CurrentField["panelIdx"]=($this->CurrentPanel >= 0) ? $this->CurrentPanel : 0;\r
+ $this->CurrentField["AddQuotes"]=true;\r
+ $this->CurrentField["ReadOnly"]=false;\r
+ $this->CurrentField["TableIdx"]=$this->MainTbl;\r
+ }\r
+\r
+ function SetTableName($s) {\r
+ $this->TableCnt++;\r
+ $this->MainTbl=$this->TableCnt;\r
+ $this->Tables[$this->TableCnt]= new TableEditTable();\r
+ $this->Tables[$this->MainTbl]->TblName=$s;\r
+ $this->Tables[$this->MainTbl]->alias="t";\r
+ $this->oParseMain->FromClause=$s." t";\r
+ $this->gridID=strtolower(str_replace(" ","_",str_replace(".","_",$s)));\r
+ $this->formVar=$this->gridID . "['edit']";\r
+ $this->gridVar=$this->gridID . "['grid']";\r
+ $this->bufferVar=$this->gridID . "['buffer']";\r
+ $this->optionsVar=$this->gridID . "['options']";\r
+ $actionparm="_action_".$this->gridID;\r
+ $this->action=isset($_REQUEST[$actionparm]) ? trim($_REQUEST[$actionparm]) : "";\r
+ $this->action=($this->action == "") ? "table" : strtolower($this->action);\r
+ }\r
+\r
+ function AddSort($field,$direction) {\r
+ if (!empty($this->DefaultSort)) $this->DefaultSort.=",";\r
+ $this->DefaultSort.=$field . " " . $direction;\r
+ }\r
+\r
+ function SortCurrent($direction) {\r
+ if (array_key_exists("Formula",$this->CurrentField))\r
+ $this->AddSort($this->CurrentField["Formula"],$direction);\r
+ elseif (array_key_exists("ColName",$this->CurrentField))\r
+ $this->AddSort($this->Tables[$this->CurrentField["TableIdx"]]->alias . "." . $this->CurrentField["ColName"],$direction);\r
+ $this->options["sortCol"]=$this->FieldCnt;\r
+ $this->options["sortDir"]=$direction;\r
+ }\r
+\r
+ function SortAsc() {\r
+ $this->SortCurrent("ASC");\r
+ }\r
+\r
+ function SortDesc() {\r
+ $this->SortCurrent("DESC");\r
+ }\r
+\r
+ function ConfirmDeleteColumn() {\r
+ $this->options["ConfirmDeleteCol"]=$this->FieldCnt;\r
+ }\r
+\r
+ function genXHTML() {\r
+ $this->xhtmlcloser=" />";\r
+ }\r
+\r
+ function SetDbConn(&$dbcls) {\r
+ $this->objDB=&$dbcls;\r
+ }\r
+\r
+ //*************************************************************************************\r
+ // Take appropriate action\r
+ //*************************************************************************************\r
+ function DisplayPage() {\r
+ if (count($this->Fields) == 0) {\r
+ return;\r
+ }\r
+ if (!$this->ErrorFlag) {\r
+ $this->GetColumnInfo();\r
+ }\r
+ if (!$this->ErrorFlag) {\r
+ switch ($this->action) {\r
+\r
+ case "del":\r
+ if ($this->options["canDelete"]) {\r
+ $this->TableDeleteRecord();\r
+ }\r
+ break;\r
+\r
+ case "ins":\r
+ if ($this->options["canAdd"]) {\r
+ $this->TableInsertRecord();\r
+ }\r
+ break;\r
+\r
+ case "upd":\r
+ if ($this->options["canEdit"]) {\r
+ $this->TableUpdateRecord();\r
+ }\r
+ break;\r
+\r
+ default:\r
+ if ($this->sessions) $_SESSION[$this->gridID]=$this->SqlSelectData();\r
+ $this->TableDisplay();\r
+ break;\r
+ }\r
+ }\r
+ if ($this->ErrorFlag) {\r
+ echo "\n<p style='color:red;'><span style='text-decoration:underline;'>ERROR ENCOUNTERED</span><br".$this->xhtmlcloser.$this->ErrorMsg;\r
+ }\r
+ }\r
+\r
+ // if AltTable has a multi-column key, then add those additional constraints\r
+ function AltTableKeyWhereClause($AltTabIdx) {\r
+ for ($i=0; $i<count($this->Tables[$AltTabIdx]->arFields); $i++) {\r
+ if ($this->Tables[$AltTabIdx]->arColInfo[$i]->IsPKey) {\r
+ $w.=" and ".$this->Tables[$AltTabIdx]->arFields[$i]."=".$this->Tables[$AltTabIdx]->arData[$i];\r
+ }\r
+ }\r
+ return $w;\r
+ }\r
+\r
+ function AltTableJoinClause($alias) {\r
+ for ($i=0; $i<count($this->Fields); $i++) {\r
+ if ($this->Fields[$i]["TableIdx"] == $this->MainTbl && !$this->IsCalculatedField($i)) {\r
+ if ($this->Fields[$i]["ColInfo"]->IsPKey) {\r
+ $this->objDB->AddCondition($w, $this->Fields[$i]["ColName"]."=".$alias.".".$this->Fields[$i]["ColName"]);\r
+ }\r
+ }\r
+ }\r
+ return $w;\r
+ }\r
+\r
+ // form where clause based on table's primary key\r
+ function TableKeyWhereClause() {\r
+ for ($i=0; $i<count($this->Fields); $i++) {\r
+ if ($this->Fields[$i]["TableIdx"] == $this->MainTbl && !$this->IsCalculatedField($i)) {\r
+ if ($this->Fields[$i]["ColInfo"]->IsPKey) {\r
+ $this->objDB->AddCondition($w, $this->Fields[$i]["ColName"]."=".$this->FormatValue($_POST["_k".$i],$i));\r
+ }\r
+ }\r
+ }\r
+ if (empty($w)) {\r
+ $this->TableEditError("no key value");\r
+ }\r
+ else {\r
+ return " WHERE ".$w;\r
+ }\r
+ }\r
+\r
+ // name used external to this script\r
+ function ExtFieldId($i) {\r
+ return $this->gridID."_".$i;\r
+ }\r
+\r
+ function IsCalculatedField($i) {\r
+ return array_key_exists("Formula",$this->Fields[$i]);\r
+ }\r
+\r
+ //*************************************************************************************\r
+ // Retrieves column info from database for main table and any alternate tables\r
+ //*************************************************************************************\r
+ function GetColumnInfo() {\r
+ $Columns=array();\r
+ $dicColIdx=array();\r
+ for ($FieldNum=0; $FieldNum<count($this->Fields); $FieldNum++) {\r
+ $dicColIdx[$this->Fields[$FieldNum]["TableIdx"].".".strtoupper($this->Fields[$FieldNum]["ColName"])]= $FieldNum;\r
+ if ($this->options["canEdit"] == false && $this->options["canAdd"] == false) {\r
+ $this->Fields[$FieldNum]["ReadOnly"]=true;\r
+ }\r
+ }\r
+ //print_r($dicColIdx);\r
+ for ($i=0; $i<=$this->TableCnt; $i++) {\r
+ $Columns=$this->objDB->GetColumnInfo($this->Tables[$i]->TblName);\r
+ if (!is_array($Columns)) {\r
+ $this->TableEditError("unable to retrieve column info for ".$this->Tables[$i]->TblName."<br>".$this->objDB->LastErrorMsg);\r
+ return;\r
+ }\r
+ //print_r($Columns);\r
+ for ($c=0; $c < count($Columns); $c++) {\r
+ $colname=strtoupper($Columns[$c]->ColName);\r
+ if (array_key_exists($i.".".$colname,$dicColIdx)) {\r
+ $FieldNum=$dicColIdx[$i.".".$colname];\r
+ $this->Fields[$FieldNum]["ColInfo"]=$Columns[$c];\r
+ }\r
+ elseif ($i != $this->MainTbl) {\r
+ for ($j=0; $j < count($this->Tables[$i]->arFields); $j++) {\r
+ if ($colname == $this->Tables[$i]->arFields[$j]) {\r
+ $this->Tables[$i]->arColInfo[$j]=$Columns[$c];\r
+ }\r
+ }\r
+ }\r
+ elseif ($Columns[$c]->IsPKey) {\r
+ $this->TableEditError("primary key field is not defined (".$this->Tables[$i]->TblName.".".$colname.")");\r
+ $dicColIdx=NULL;\r
+ return;\r
+ }\r
+ }\r
+ }\r
+ $dicColIdx=NULL;\r
+ }\r
+\r
+ function TableUpdateDatabase($sqltext, $actiontxt) {\r
+ if ($this->ErrorFlag) {\r
+ return;\r
+ }\r
+ $cnt=$this->objDB->RunActionQueryReturnMsg($sqltext, $errmsg);\r
+ if ($this->options["DebugFlag"])\r
+ echo "<p class='debug'>".$sqltext."<br".$this->xhtmlcloser."Records affected: ".$cnt;\r
+ if (!empty($errmsg))\r
+ $this->TableEditError("unable to update database!<br".$this->xhtmlcloser.$errmsg);\r
+ else if ($cnt == 1)\r
+ echo "<p class='ricoFormResponse ".$actiontxt."Successfully'></p>";\r
+ else\r
+ $this->TableEditError("no data changed - update skipped");\r
+ }\r
+\r
+ function FormatValue($v, $idx) {\r
+ $fld=$this->Fields[$idx];\r
+ $addquotes=$fld["AddQuotes"];\r
+ if (substr($fld["EntryType"],0,1) == "D") {\r
+ if ($v == "") {\r
+ $addquotes=false;\r
+ $v="NULL";\r
+ }\r
+ }\r
+ elseif ($fld["EntryType"] == "I" || $fld["EntryType"] == "F") {\r
+ $addquotes=false;\r
+ if ($v == "" || !is_numeric($v)) {\r
+ $v="NULL";\r
+ }\r
+ }\r
+ elseif ($fld["EntryType"] == "N" && $v == $this->options["TableSelectNew"]) {\r
+ $v=trim($_POST["textnew__".$this->ExtFieldId($idx)]);\r
+ }\r
+ elseif (strpos("SNR",substr($fld["EntryType"],0,1)) !== false && $v == $this->options["TableSelectNone"]) {\r
+ $addquotes=false;\r
+ $v="NULL";\r
+ }\r
+ if ($addquotes) $v=$this->objDB->addQuotes($v);\r
+ return $v;\r
+ }\r
+\r
+ function FormatFormValue($idx) {\r
+ if (!array_key_exists("EntryType",$this->Fields[$idx])) return "";\r
+ $fldname=$this->ExtFieldId($idx);\r
+ if ($this->Fields[$idx]["EntryType"] == "H" || (array_key_exists("FormView",$this->Fields[$idx]) && $this->Fields[$idx]["FormView"] == "exclude"))\r
+ $v=$this->Fields[$idx]["ColData"];\r
+ elseif (isset($_POST[$fldname])) {\r
+ $v=$_POST[$fldname];\r
+ if (get_magic_quotes_gpc()) $v=stripslashes($v);\r
+ $v=($this->convertCharSet) ? utf8_decode($v) : urldecode($v);\r
+ $v=trim($v);\r
+ }\r
+ return $this->FormatValue($v, $idx);\r
+ }\r
+\r
+ //*************************************************************************************\r
+ // Deletes the specified record\r
+ //*************************************************************************************\r
+ function TableDeleteRecord() {\r
+ $this->TableUpdateDatabase("DELETE FROM ".$this->Tables[$this->MainTbl]->TblName.$this->TableKeyWhereClause(), "deleted");\r
+ }\r
+\r
+ function UpdateRecord($sqltext) {\r
+ $this->objDB->RunActionQueryReturnMsg($sqltext, $errmsg);\r
+ if (!empty($errmsg)) {\r
+ $errmsg="unable to update database!<br".$this->xhtmlcloser.$errmsg;\r
+ if ($this->options["DebugFlag"]) {\r
+ $errmsg.="<p>SQL: ".$sqltext;\r
+ }\r
+ $this->TableEditError($errmsg);\r
+ }\r
+ elseif ($this->options["DebugFlag"]) {\r
+ echo "<BR class='debug'>".$sqltext;\r
+ }\r
+ }\r
+\r
+ function UpdateAltTableRecords($i) {\r
+ if ($this->ErrorFlag) {\r
+ return;\r
+ }\r
+ // delete existing record\r
+ $sqltext="delete from ".$this->Tables[$i]->TblName;\r
+ $sqltext.=$this->TableKeyWhereClause();\r
+ $sqltext.=$this->AltTableKeyWhereClause($i);\r
+ $this->UpdateRecord($sqltext);\r
+ // insert new record\r
+ $colnames="";\r
+ $coldata="";\r
+ for ($j=0; $j<count($this->Fields); $j++) {\r
+ if (!array_key_exists("ColInfo",$this->Fields[$j])) continue;\r
+ if ($this->Fields[$j]["TableIdx"] == $i || $this->Fields[$j]["ColInfo"]->IsPKey) {\r
+ $colnames.=",".$this->Fields[$j]["ColName"];\r
+ $coldata.=",".$this->FormatValue(trim($_POST[$this->ExtFieldId($j)]), $j);\r
+ }\r
+ }\r
+ for ($j=0; $j<count($this->Tables[$i]->arFields); $j++) {\r
+ $c=$this->Tables[$i]->arFields[$j];\r
+ $colnames.=",".$c;\r
+ $coldata.=",".$this->Tables[$i]->arData[$j];\r
+ }\r
+ $sqltext="insert into ".$this->Tables[$i]->TblName." (".substr($colnames,1).") values (".substr($coldata,1).")";\r
+ $this->UpdateRecord($sqltext);\r
+ }\r
+\r
+ //*************************************************************************************\r
+ // Updates an existing record in the db\r
+ //*************************************************************************************\r
+ function TableUpdateRecord() {\r
+ for ($i=0; $i<=$this->TableCnt; $i++) {\r
+ if ($i != $this->MainTbl) {\r
+ $this->UpdateAltTableRecords($i);\r
+ }\r
+ }\r
+ for ($i=0,$sqltext=''; $i<count($this->Fields); $i++) {\r
+ if (!$this->IsCalculatedField($i)) {\r
+ if ($this->Fields[$i]["TableIdx"] == $this->MainTbl && $this->Fields[$i]["ColInfo"]->Writeable && !array_key_exists("InsertOnly",$this->Fields[$i])) {\r
+ $sqltext.=",".$this->Fields[$i]["ColName"]."=".$this->FormatFormValue($i);\r
+ }\r
+ }\r
+ }\r
+ $sqltext="UPDATE ".$this->Tables[$this->MainTbl]->TblName." SET ".substr($sqltext,1);\r
+ $sqltext.=$this->TableKeyWhereClause();\r
+ $this->TableUpdateDatabase($sqltext, "updated");\r
+ }\r
+ //*************************************************************************************\r
+ // Inserts a new record into the db\r
+ //*************************************************************************************\r
+\r
+ function TableInsertRecord() {\r
+ $keyCnt=0;\r
+ $sqlcol="";\r
+ $sqlval="";\r
+ for ($i=0; $i<count($this->Fields); $i++) {\r
+ if (!$this->IsCalculatedField($i) && $this->Fields[$i]["TableIdx"] == $this->MainTbl && !array_key_exists("UpdateOnly",$this->Fields[$i])) {\r
+ if ($this->Fields[$i]["ColInfo"]->IsPKey) {\r
+ $keyCnt++;\r
+ $keyIdx=$i;\r
+ }\r
+ if ($this->Fields[$i]["ColInfo"]->Writeable) {\r
+ $sqlcol.=",".$this->Fields[$i]["ColName"];\r
+ $sqlval.=",".$this->FormatFormValue($i);\r
+ }\r
+ }\r
+ }\r
+ $sqltext="insert into ".$this->Tables[$this->MainTbl]->TblName." (".substr($sqlcol,1).") values (".substr($sqlval,1).")";\r
+ $this->TableUpdateDatabase($sqltext, "added");\r
+ }\r
+\r
+ function TableEditError($msg) {\r
+ $this->ErrorFlag=true;\r
+ $this->ErrorMsg=$msg;\r
+ }\r
+ \r
+ //*************************************************************************************\r
+ // Do post-processing on sql query\r
+ //*************************************************************************************\r
+ function FinishQuery() {\r
+ $oParseLookup= new sqlParse();\r
+ $this->oParseMain->AddWhereCondition($this->TableFilter);\r
+ for ($i=0; $i<count($this->Fields); $i++) {\r
+\r
+ if (array_key_exists("FilterFlag",$this->Fields[$i])) {\r
+ // add any column filters to where clause\r
+ $this->oParseMain->AddWhereCondition($this->Tables[$this->Fields[$i]["TableIdx"]]->alias.".".$this->Fields[$i]["ColName"]."='".$this->Fields[$i]["ColData"]."'");\r
+ }\r
+\r
+ if (array_key_exists("EntryType",$this->Fields[$i])) {\r
+ if (strpos("CSNR",substr($this->Fields[$i]["EntryType"],0,1)) !== false) {\r
+ if (array_key_exists("SelectSql",$this->Fields[$i])) {\r
+ $s=$this->Fields[$i]["SelectSql"];\r
+ if (array_key_exists("SelectFilter",$this->Fields[$i])) {\r
+ $oParseLookup->ParseSelect($s);\r
+ $oParseLookup->AddWhereCondition($this->Fields[$i]["SelectFilter"]);\r
+ $s=$oParseLookup->UnparseSelect();\r
+ }\r
+ $this->Fields[$i]["DropDownSelect"]=str_replace("%alias%","",str_replace("%aliasmain%","",$s));\r
+ }\r
+ else {\r
+ $this->Fields[$i]["DropDownSelect"]="select distinct ".$this->Fields[$i]["ColName"]." from ".$this->Tables[$this->Fields[$i]["TableIdx"]]->TblName." where ".$this->Fields[$i]["ColName"]." is not null";\r
+ }\r
+ }\r
+ }\r
+\r
+ if ($this->Fields[$i]["TableIdx"] != $this->MainTbl) {\r
+\r
+ // column from alt table - no avoiding subqueries here\r
+\r
+ $s="(select " . $this->Fields[$i]["ColName"] . " from " . $this->Tables[$this->Fields[$i]["TableIdx"]]->TblName . " a" . $i . " where " . $this->AltTableJoinClause("t") . $this->AltTableKeyWhereClause($this->Fields[$i]["TableIdx"]) . ")";\r
+ if (substr($this->Fields[$i]["EntryType"],1) == "L" && array_key_exists("SelectSql",$this->Fields[$i])) {\r
+ $oParseLookup->ParseSelect($this->Fields[$i]["SelectSql"]);\r
+ if (count($oParseLookup->arSelList) == 2) {\r
+ $codeField=$oParseLookup->arSelList[0];\r
+ $descField=$oParseLookup->arSelList[1];\r
+ $descQuery="select " . $descField . " from " . $oParseLookup->FromClause . " where " . $codeField . "=" . $s;\r
+ if (!empty($oParseLookup->WhereClause)) $descQuery.=" and " . $oParseLookup->WhereClause;\r
+ $this->oParseMain->arSelList[$i]="(" . $this->objDB->concat(array("(" . $descQuery . ")", "'<span class=\"ricoLookup\">'", $this->objDB->Convert2Char($s), "'</span>'"), false) . ") as rico_col" . $i;\r
+ } else {\r
+ $this->TableEditError("Invalid lookup query (".$this->Fields[$i]["SelectSql"].")");\r
+ return;\r
+ }\r
+ } else {\r
+ $this->oParseMain->arSelList[$i]=$s . " as rico_col" . $i;\r
+ }\r
+ }\r
+ }\r
+ if (empty($this->DefaultSort)) {\r
+ $this->DefaultSort=$this->objDB->PrimaryKey($this->Tables[$this->MainTbl]->TblName);\r
+ }\r
+ $this->oParseMain->AddSort($this->DefaultSort);\r
+ }\r
+ \r
+ //*************************************************************************************\r
+ // returns details of sql query as an array\r
+ //*************************************************************************************\r
+ function SqlSelectData() {\r
+ $this->FinishQuery();\r
+ $arr=$this->oParseMain->ToArray();\r
+ $SelectIdx=count($arr);\r
+ array_push($arr, array());\r
+ $HdgIdx=count($arr);\r
+ array_push($arr, array());\r
+ for ($i=0; $i<count($this->Fields); $i++) {\r
+ if (array_key_exists("DropDownSelect",$this->Fields[$i])) {\r
+ $arr[$SelectIdx][$i]=$this->Fields[$i]["DropDownSelect"];\r
+ }\r
+ if (array_key_exists("Hdg",$this->Fields[$i])) {\r
+ $arr[$HdgIdx][$i]=$this->Fields[$i]["Hdg"];\r
+ }\r
+ }\r
+ return $arr;\r
+ }\r
+ \r
+ //*************************************************************************************\r
+ // Displays a table\r
+ //*************************************************************************************\r
+ function TableDisplay() {\r
+ echo "\n<p class='ricoBookmark'>";\r
+ echo "<span id='".$this->gridID."_timer' class='ricoSessionTimer'></span>";\r
+ echo "<span id='".$this->gridID."_bookmark' class='ricoBookmark'> </span>";\r
+ echo "<span id='".$this->gridID."_savemsg' class='ricoSaveMsg'></span>";\r
+ echo "</p>";\r
+ echo "\n<div id='".$this->gridID."'></div>";\r
+ echo "\n<script type='text/javascript'>";\r
+ echo "\nRico.acceptLanguage('" . $_SERVER["HTTP_ACCEPT_LANGUAGE"] . "');";\r
+ echo "\nvar ".$this->gridID." = {};";\r
+ echo "\n".$this->optionsVar." = {";\r
+ foreach ($this->options as $o => $value) {\r
+ if (!is_object($value) && !array_key_exists($o,$this->SvrOnly)) {\r
+ echo "\n ".$o.": ".$this->FormatOption($value).",";\r
+ }\r
+ }\r
+ if ($this->CurrentPanel >= 0) {\r
+ echo "\n panels: [";\r
+ for ($i=0; $i<=$this->CurrentPanel; $i++) {\r
+ if ($i > 0) {\r
+ echo ",";\r
+ }\r
+ echo "'".$this->Panels[$i]."'";\r
+ }\r
+ echo "],";\r
+ }\r
+ echo "\n columnSpecs : [";\r
+ for ($i=0; $i<count($this->Fields); $i++) {\r
+ if ($this->Fields[$i]["TableIdx"] != $this->MainTbl) $this->Fields[$i]["UpdateOnly"]=true;\r
+ if ($i > 0) {\r
+ echo ",";\r
+ }\r
+ echo "\n {";\r
+ echo " FieldName:'".$this->ExtFieldId($i)."'";\r
+ foreach ($this->Fields[$i] as $o => $value) {\r
+ if (!is_object($value) && !array_key_exists($o,$this->SvrOnly)) {\r
+ echo ",\n ".$o.": ".$this->FormatOption($value);\r
+ }\r
+ }\r
+ if (array_key_exists("ColInfo",$this->Fields[$i])) {\r
+ echo ",\n isNullable:".$this->FormatOption($this->Fields[$i]["ColInfo"]->Nullable);\r
+ echo ",\n Writeable:".$this->FormatOption($this->Fields[$i]["ColInfo"]->Writeable);\r
+ echo ",\n isKey:".$this->FormatOption($this->Fields[$i]["ColInfo"]->IsPKey);\r
+ if ($this->Fields[$i]["ColInfo"]->ColLength) {\r
+ echo ",\n Length:".$this->Fields[$i]["ColInfo"]->ColLength;\r
+ }\r
+ }\r
+ echo " }";\r
+ }\r
+ echo "\n ]";\r
+ echo "\n};";\r
+ if ($this->AutoInit) {\r
+ echo "\nRico.onLoad(function() {";\r
+ //echo "\n try {";\r
+ echo "\n if(typeof Rico.LiveGrid=='undefined') throw('LiveGridForms requires the Rico.LiveGrid Library');";\r
+ echo "\n if(typeof Rico.GridMenu=='undefined') throw('LiveGridForms requires the Rico.GridMenu Library');";\r
+ echo "\n if(typeof Rico.Buffer=='undefined') throw('LiveGridForms requires the Rico.Buffer Library');";\r
+ echo "\n if(typeof Rico.Buffer.AjaxSQL=='undefined') throw('LiveGridForms requires the Rico.Buffer.AjaxSQL Library');";\r
+ echo $this->InitScript();\r
+ //echo "\n } catch(e) { alert(e.message); };";\r
+ echo "\n});";\r
+ }\r
+ echo "\n</script>";\r
+ }\r
+\r
+ function FormatOption($s) {\r
+ if (is_array($s)) return "{" . implode(",",$s) . "}";\r
+ switch (gettype($s)) {\r
+ case 'string': return "\"".addslashes($s)."\"";\r
+ case 'boolean': return $s ? 'true' : 'false';\r
+ case 'double': return str_replace(",",".",strval($s)); // make sure period is used as a decimal point\r
+ default: return $s;\r
+ }\r
+ }\r
+\r
+ function InitScript() {\r
+ $s="\n".$this->bufferVar."=new Rico.Buffer.AjaxSQL('" . $this->options["XMLprovider"] . "', {TimeOut:" . (array_shift(session_get_cookie_params())/60) . "});";\r
+ $s.="\nif(typeof ".$this->gridID."_GridInit=='function') ".$this->gridID."_GridInit();";\r
+ $s.="\n".$this->gridVar."=new Rico.LiveGrid('".$this->gridID."',".$this->bufferVar.",".$this->optionsVar.");";\r
+ $s.="\n".$this->gridVar.".menu=new Rico.GridMenu();";\r
+ if ($this->formView) {\r
+ $s.="\nif(typeof ".$this->gridID."_FormInit=='function') ".$this->gridID."_FormInit();";\r
+ $s.="\n".$this->formVar."=new Rico.TableEdit(".$this->gridVar.");";\r
+ }\r
+ $s.="\nif(typeof ".$this->gridID."_InitComplete=='function') ".$this->gridID."_InitComplete();";\r
+ return $s;\r
+ }\r
+}\r
+\r
+?>\r
projects / infodrom / rico3 / blobdiff