private function getTableInfo($DBtable, $arrExcluidos = array())
{
$arrTypes2Tags = array("varchar" => array("property" => "type", "tag" => "input", "tagType" => "text", "filterDefinition" => array("filter" => "FILTER_SANITIZE_SPECIAL_CHARS", "flags" => "", "options" => array())), "text" => array("property" => "type", "tag" => "textarea", "tagType" => "textarea", "filterDefinition" => array("filter" => "FILTER_SANITIZE_SPECIAL_CHARS", "flags" => "", "options" => array())), "int" => array("property" => "type", "tag" => "input", "tagType" => "text", "filterDefinition" => array("filter" => "FILTER_VALIDATE_INT", "flags" => "", "options" => array("default" => 1, "min_range" => 1, "max_range" => 10))), "tinyint(1)" => array("property" => "type", "tag" => "input", "tagType" => "checkbox", "filterDefinition" => array("filter" => "FILTER_VALIDATE_INT", "flags" => "", "options" => array("min_range" => 0, "max_range" => 1))), "enum" => array("property" => "type", "tag" => "select", "tagType" => "enum", "filterDefinition" => array()), "date" => array("property" => "type", "tag" => "input", "tagType" => "date", "filterDefinition" => array("filter" => "FILTER_VALIDATE_REGEXP", "flags" => "", "options" => array("regexp" => "#([0-9]{1,2})[/|-]([0-9]{1,2})[/|-]([0-9]{2,4})(?: ([0-9]{0,2}):([0-9]{0,2}):([0-9]{0,2}))*#"))), "datetime" => array("property" => "type", "tag" => "input", "tagType" => "datetime", "filterDefinition" => array("filter" => "FILTER_VALIDATE_REGEXP", "flags" => "", "options" => array("regexp" => "#([0-9]{1,2})[/|-]([0-9]{1,2})[/|-]([0-9]{2,4})(?: ([0-9]{0,2}):([0-9]{0,2}):([0-9]{0,2}))*#"))), "timestamp" => array("property" => "type", "tag" => "input", "tagType" => "datetime", "filterDefinition" => array("filter" => "FILTER_VALIDATE_REGEXP", "flags" => "", "options" => array("regexp" => "#([0-9]{1,2})[/|-]([0-9]{1,2})[/|-]([0-9]{2,4})(?: ([0-9]{0,2}):([0-9]{0,2}):([0-9]{0,2}))*#"))), "float" => array("property" => "type", "tag" => "input", "tagType" => "text", "filterDefinition" => array("filter" => "FILTER_VALIDATE_FLOAT", "flags" => "FILTER_FLAG_ALLOW_THOUSAND", "options" => array("decimal" => ","))), "email" => array("property" => "field", "tag" => "input", "tagType" => "email", "filterDefinition" => array("filter" => "FILTER_VALIDATE_EMAIL", "flags" => "", "options" => array())), "PRI" => array("property" => "key", "tag" => "input", "tagType" => "hidden", "filterDefinition" => array("filter" => "FILTER_VALIDATE_INT", "flags" => "", "options" => array("default" => NULL, "min_range" => 1, "max_range" => PHP_INT_MAX))), "MUL" => array("property" => "key", "tag" => "select", "tagType" => "dbSelect", "filterDefinition" => array("filter" => "FILTER_VALIDATE_INT", "flags" => "", "options" => array("default" => NULL, "min_range" => 1, "max_range" => PHP_INT_MAX))));
$mysqli = \cDb::getInstance();
$stdObjTableInfo = new \stdClass();
$stdObjTableInfo->tableName = $DBtable;
$rslCreate = $mysqli->query("SHOW CREATE TABLE " . $stdObjTableInfo->tableName);
$stdObjTableInfo->rslColumns = $mysqli->query("SHOW COLUMNS FROM " . $stdObjTableInfo->tableName);
$stdObjTableInfo->rslIdx = $mysqli->query("show index from " . $stdObjTableInfo->tableName);
$stdObjTableInfo->rslFksFrom = $mysqli->query("\n\t\t\t\t\tSELECT * FROM information_schema.KEY_COLUMN_USAGE\n\t\t\t\t\tWHERE\n\t\t\t\t\tTABLE_NAME = '" . $stdObjTableInfo->tableName . "' AND\n\t\t\t\t\tREFERENCED_TABLE_NAME IS NOT NULL\n\t\t\t\t\tAND TABLE_SCHEMA = '" . _DB_NAME_ . "';\n\t\t\t\t");
$stdObjTableInfo->rslFksTo = $mysqli->query("\n\t\t\t\t\tSELECT * FROM information_schema.KEY_COLUMN_USAGE\n\t\t\t\t\tWHERE REFERENCED_TABLE_NAME = '" . $stdObjTableInfo->tableName . "'\n\t\t\t\t\tAND TABLE_SCHEMA = '" . _DB_NAME_ . "';\n\t\t\t\t");
$stdObjTableInfo->arrCreateInfo = $rslCreate->fetch_array(MYSQLI_ASSOC);
/**/
$stdObjTableInfo->arrFksFrom = array();
while ($fkInfo = $stdObjTableInfo->rslFksFrom->fetch_array(MYSQLI_ASSOC)) {
$stdObjFkInfo = new \stdClass();
//$stdObjFkInfo->TABLE_NAME=$fkInfo['REFERENCED_TABLE_NAME'];
//$stdObjFkInfo->COLUMN_NAME=$fkInfo['COLUMN_NAME'];
$stdObjFkInfo->TABLE_NAME = $fkInfo['TABLE_NAME'];
$stdObjFkInfo->COLUMN_NAME = $fkInfo['COLUMN_NAME'];
$stdObjFkInfo->REFERENCED_TABLE_NAME = $fkInfo['REFERENCED_TABLE_NAME'];
$stdObjFkInfo->REFERENCED_COLUMN_NAME = $fkInfo['REFERENCED_COLUMN_NAME'];
array_push($stdObjTableInfo->arrFksFrom, $stdObjFkInfo);
unset($stdObjFkInfo);
}
$stdObjTableInfo->rslFksFrom->data_seek(0);
$stdObjTableInfo->arrFksTo = array();
while ($fkInfo = $stdObjTableInfo->rslFksTo->fetch_array(MYSQLI_ASSOC)) {
$stdObjFkInfo = new \stdClass();
//$stdObjFkInfo->TABLE_NAME=$fkInfo['TABLE_NAME'];
//$stdObjFkInfo->COLUMN_NAME=$fkInfo['COLUMN_NAME'];
$stdObjFkInfo->TABLE_NAME = $fkInfo['TABLE_NAME'];
$stdObjFkInfo->COLUMN_NAME = $fkInfo['COLUMN_NAME'];
$stdObjFkInfo->REFERENCED_TABLE_NAME = $fkInfo['REFERENCED_TABLE_NAME'];
$stdObjFkInfo->REFERENCED_COLUMN_NAME = $fkInfo['REFERENCED_COLUMN_NAME'];
//Comprobación si la FkTo corresponde a una relaccion manyToMany
//En la tabla FkTo tiene que haber una FkFrom que forme parte de la Pk
//Buscamos todas las FkFrom en la tabla de la FkTo actual (Todas salvo la FkTo que actual, que nos ha llevado a la tabla)
//Comprobamos si la FkFrom está incluida en la Pk
$rslFksFromFkTo = $mysqli->query("\n\t\t\t\t\t\tSELECT * FROM information_schema.KEY_COLUMN_USAGE\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\tTABLE_NAME = '" . $fkInfo['TABLE_NAME'] . "' AND\n\t\t\t\t\t\t(REFERENCED_TABLE_NAME IS NULL OR\n\t\t\t\t\t\tREFERENCED_TABLE_NAME <> '" . $stdObjTableInfo->tableName . "') AND\n\t\t\t\t\t\tTABLE_SCHEMA = '" . _DB_NAME_ . "';\n\t\t\t\t\t");
$arrPkColumns = array();
$arrFkColumns = array();
while ($fkFromFkToInfo = $rslFksFromFkTo->fetch_array(MYSQLI_ASSOC)) {
switch ($fkFromFkToInfo['CONSTRAINT_NAME']) {
//TODO: Mejora: no usar CONSTRAINT_NAME, la Pk podría llamarse de otro modo. Buscar un modo de llegar a TABLE_CONSTRAINT.CONSTRAINT_TYPE
case 'PRIMARY':
$arrPkColumns[] = $fkFromFkToInfo['TABLE_NAME'] . '.' . $fkFromFkToInfo['COLUMN_NAME'];
break;
default:
$arrFkColumns[] = $fkFromFkToInfo['TABLE_NAME'] . '.' . $fkFromFkToInfo['COLUMN_NAME'];
}
}
$arrFkManyToMany = array_intersect($arrPkColumns, $arrFkColumns);
$rslFksFromFkTo->data_seek(0);
while ($fkFromFkToInfo = $rslFksFromFkTo->fetch_array(MYSQLI_ASSOC)) {
if (in_array($fkFromFkToInfo['TABLE_NAME'] . '.' . $fkFromFkToInfo['COLUMN_NAME'], $arrFkManyToMany)) {
$fkFromFkToREFERENCED_TABLE_NAME = $fkFromFkToInfo['REFERENCED_TABLE_NAME'];
$fkFromFkToCOLUMN_NAME = $fkFromFkToInfo['COLUMN_NAME'];
}
}
$stdObjFkInfo->manyToMany = false;
$stdObjFkInfo->ffTable = null;
$stdObjFkInfo->ffField = null;
if (count($arrFkManyToMany) > 0) {
$stdObjFkInfo->manyToMany = true;
$stdObjFkInfo->ffTable = $fkFromFkToREFERENCED_TABLE_NAME;
$stdObjFkInfo->ffField = $fkFromFkToCOLUMN_NAME;
}
array_push($stdObjTableInfo->arrFksTo, $stdObjFkInfo);
unset($stdObjFkInfo);
}
$stdObjTableInfo->rslFksTo->data_seek(0);
/**/
$stdObjTableInfo->arrStdObjColumnInfo = array();
$stdObjTableInfo->arrAttrs = array();
while ($columnInfo = $stdObjTableInfo->rslColumns->fetch_array(MYSQLI_ASSOC)) {
if (in_array($columnInfo['Field'], $arrExcluidos)) {
continue;
}
$stdObjColumnInfo = new \stdClass();
$stdObjColumnInfo->field = $columnInfo['Field'];
$stdObjColumnInfo->type = $columnInfo['Type'];
$stdObjColumnInfo->null = $columnInfo['Null'] == 'NO' ? false : true;
$stdObjColumnInfo->key = $columnInfo['Key'];
$stdObjColumnInfo->default = $columnInfo['Default'];
$stdObjColumnInfo->extra = $columnInfo['Extra'];
$tag = 'input';
$tagType = "text";
$filterDefinition = array();
foreach ($arrTypes2Tags as $strSearchFor => $options) {
$property = $options["property"];
if (strpos($stdObjColumnInfo->{$property}, $strSearchFor) !== false) {
$tag = $options["tag"];
$tagType = $options["tagType"];
$filterDefinition = $options["filterDefinition"];
}
if ($tag == "select") {
$arrSelectValues = array();
switch ($strSearchFor) {
case "enum":
preg_match('/enum\\((.*)\\)$/', $stdObjColumnInfo->type, $matches);
$arrSelectValues = explode(',', $matches[1]);
$strRegEx = "";
foreach ($arrSelectValues as $value) {
$value = trim($value, "'");
$strRegEx .= $value . "|";
}
$strRegEx = substr($strRegEx, 0, -1);
$filterDefinition = array("filter" => "FILTER_VALIDATE_REGEXP", "flags" => "", "options" => array("regexp" => "~" . $strRegEx . "~"));
break;
//Select con consulta a BD
//Select con consulta a BD
case "MUL":
foreach ($stdObjTableInfo->arrFksFrom as $stdObjFkInfo) {
if ($stdObjFkInfo->COLUMN_NAME == $stdObjColumnInfo->field) {
$campoSelect = '';
$rslFk = $mysqli->query('SHOW COLUMNS FROM ' . $stdObjFkInfo->REFERENCED_TABLE_NAME);
while ($rtColumnInfo = $rslFk->fetch_array(MYSQLI_ASSOC)) {
$fieldName = strtolower($rtColumnInfo['Field']);
switch ($fieldName) {
case 'nombre':
case 'descripcion':
$campoSelect = $fieldName;
break 2;
}
}
if ($campoSelect == "") {
$campoSelect = $stdObjFkInfo->REFERENCED_COLUMN_NAME;
}
$sql = 'SELECT ' . $stdObjFkInfo->REFERENCED_COLUMN_NAME . ' as value, ' . $campoSelect . ' as content ' . 'FROM ' . $stdObjFkInfo->REFERENCED_TABLE_NAME . ' ORDER BY ' . $stdObjFkInfo->REFERENCED_COLUMN_NAME;
$arrSelectValues[] = $sql;
}
}
break;
}
}
}
$stdObjColumnInfo->tag = $tag;
$stdObjColumnInfo->tagType = $tagType;
$stdObjColumnInfo->filterDefinition = $filterDefinition;
if (isset($arrSelectValues)) {
$stdObjColumnInfo->arrSelectValues = $arrSelectValues;
unset($arrSelectValues);
}
//array_push($stdObjTableInfo->arrStdObjColumnInfo,$stdObjColumnInfo);
$stdObjTableInfo->arrStdObjColumnInfo[$stdObjColumnInfo->field] = $stdObjColumnInfo;
$stdObjTableInfo->arrAttrs[$stdObjColumnInfo->field] = $stdObjColumnInfo->type;
unset($stdObjColumnInfo);
}
$stdObjTableInfo->rslColumns->data_seek(0);
return $stdObjTableInfo;
}
$ancho = isset($_GET["ancho"]) ? $_GET["ancho"] : 640;
$alto = isset($_GET["alto"]) ? $_GET["alto"] : 480;
$categoria = isset($_GET["fichero"]) ? "/" . $_GET["fichero"] : "";
$url = "http://lorempixel.com/" . $ancho . "/" . $alto . $categoria;
//$firephp->info($url,"URL: ");
$objImg = Imagen::fromString(file_get_contents($url));
} catch (Exception $e) {
error_log($e->getMessage());
$file = BASE_IMGS_DIR . 'imgErr.png';
$objImg = Imagen::fromFile($file);
}
break;
case "DB":
try {
\cDb::conf(_DB_HOST_, _DB_USER_, _DB_PASSWD_, _DB_NAME_);
$db = cDb::getInstance();
list($tabla, $campoId, $valorId, $campoData) = explode('.', $_GET["fichero"]);
$sql = "SELECT " . $campoId . ", " . $campoData . " FROM " . $tabla . " WHERE id='" . $db->real_Escape_String($valorId) . "'";
//$GLOBALS['firephp']->info($sql);
$rslSet = $db->query($sql);
if ($rslSet->num_rows > 0) {
$data = $rslSet->fetch_object();
$data = $data->{$campoData};
}
$objImg = Imagen::fromString($data);
//$objImg->marcaAgua("");
//$objImg->marcaAgua("",1,1,"center");
} catch (Exception $e) {
error_log(print_r($e, true));
$file = BASE_IMGS_DIR . 'imgErr.png';
$objImg = Imagen::fromFile($file);