<?php
// +-------------------------------------------------+
// 2002-2005 PMB Services / www.sigb.net pmb@sigb.net et contributeurs (voir www.sigb.net)
// +-------------------------------------------------+
// $Id: requester.class.php,v 1.10 2015-04-03 11:16:19 jpermanne Exp $
if (stristr($_SERVER['REQUEST_URI'], ".class.php")) die("no access");
require_once ($include_path.'/templates/requests.tpl.php');
class requester {
var $t_type = array(); //Table de definition des types de requetes
var $t_cont = array(); //Table de definition des contenus de requetes
var $t_univ = array(); //Table de definition des univers et relations
var $t_schema = array(); //Table schema de base
var $t_fct = array(); //Table de definition des fonctions
var $t_fct_grp = array(); //Table de definition des groupes de fonctions
var $t_join = array(); //Table de stockage des jointures
//Constructeur.
function requester() {
$rp = new reqParser();
$this->t_schema = $rp->run('req_schema');
$this->t_type = $rp->run('req_types');
$this->t_cont = $rp->run('req_contents');
$this->t_univ = $rp->run('req_universes');
$tmp = $rp->run('req_functions');
$this->t_fct = $tmp['REQ_FUNCTION'];
$this->t_fct_grp = $tmp['REQ_FUNCTION_GROUP'];
}
//Retourne un selecteur pour choix des univers
function getUnivSelector($selected=0, $change='') {
global $charset;
$form = "<select id='req_univ' name='req_univ' onChange=\"".$change."\">";
foreach($this->t_univ as $id=>$value) {
$form.= "<option value='$id'";
if($id==$selected) $form.= " selected='selected'";
$form.= " >".htmlentities($value['name'], ENT_QUOTES, $charset)."</option>";
}
$form.= "</select>";
return $form;
}
//Retourne un selecteur pour choix du type de requete
function getTypeSelector($selected=0, $change='') {
global $charset;
$form = "<select id='req_type' name='req_type' onChange=\"".$change."\" >";
foreach($this->t_type as $id=>$value) {
$form.="<option value='$id'";
if($id==$selected) $form.= " selected='selected'";
$form.=" >".htmlentities($value['name'], ENT_QUOTES, $charset)."</option>";
}
$form.= "</select>";
return $form;
}
//Retourne le nom de la table de reference d'un univers
function getReferenceTableName($univ_id=0){
if (!$univ_id) return '';
$itr=$this->t_univ[$univ_id]['ref'];
return $itr;
}
//Retourne l'id de la table de reference d'un univers
function getReferenceTableID($univ_id=0){
if (!$univ_id) return 0;
$tr=$this->getReferenceTableName($univ_id);
$itr=$this->t_schema[$tr];
return $itr;
}
//Retourne l'id d'une table a partir de son nom
function getTableID($table_name=''){
if (!$table_name) return 0;
$table_id=$this->t_schema[$table_name];
return $table_id;
}
//Retourne les informations sur une table a partir de son id
function getTableInfo($table_id=0){
if (!$table_id) return false;
return $this->t_schema[$table_id];
}
//Retourne la cle primaire d'une table
function getPrimaryKeyID($table_id=0){
if (!$table_id) return false;
$pkid=$this->t_schema[$table_id]['pkid'];
return $pkid;
}
//Retourne les cles etrangeres d'une table
function getForeignKeys($table_id=0){
if (!$table_id) return false;
$t=array();
foreach($this->t_schema[$table_id]['fields'] as $k=>$v){
if ($this->isForeignKey($k)) $t[]=$k;
}
return $t;
}
//Retourne l'Id de la table accessible depuis une cle etrangere
function getForeignTableID($foreign_key=0) {
if(!$foreign_key) return false;
$pk=$this->t_schema['cp_links'][$foreign_key];
$tmp=explode('-',$pk);
return $tmp[0];
}
//Retourne true si le champ fait partie d'une relation enfant-parent
function isForeignKey($field_id=0){
if(!$field_id) return false;
if(array_key_exists($field_id,$this->t_schema['cp_links'])) return true;
else return false;
}
//Retourne la liste des id des champs d'une table
function getTableFieldIDList($table_id=0, $with_FK='true'){
if(!$table_id) return false;
$t = array();
foreach($this->t_schema[$table_id]['fields'] as $k=>$v) {
if ($with_FK){
$t[]=$k;
} else {
if (!$this->isForeignKey($k)) $t[]=$k;
}
}
return $t;
}
//Retourne la liste des informations pour les champs d'une table
function getTableFieldInfo($table_id=0, $with_FK='true'){
if(!$table_id) return false;
$t = array();
foreach($this->t_schema[$table_id]['fields'] as $k=>$v) {
if ($with_FK){
$t[$k]=$v;
} else {
if (!$this->isForeignKey($k)) $t[$k]=$v;
}
}
return $t;
}
//Construit un selecteur avec la liste des tables accessibles a partir d'un Univers
function getTableSelector($univ_id=0 ,$selected=0){
global $charset;
if (!$univ_id) return false;
$u_info=$this->t_univ[$univ_id];
$sel = "<select>\n";
//Creation racine liens depuis les champs de la table de reference
//$dtree.="_dt_fiel_.add('U".$univ_id."',-1,' ".addslashes($msg['req_fiel_lbl'])."');\n";
//Creation noeud table de base
$bt_id=$this->getReferenceTableID($univ_id);
$t_info=$this->getTableInfo($bt_id);
$t_desc=$t_info['desc'];
$node_id='T'.$bt_id;
$sel.="<option value='".$node_id."' ";
if(!$selected || $bt_id==$selected) $sel.="selected='selected'" ;
$sel.=">".htmlentities($t_desc,ENT_QUOTES,$charset)."</option>";
//$link=$node_id;
//Creation noeuds de l'arbre
//$dtree.=$this->getNodesTree($univ_id, $bt_id, $node_id);
//$dtree.= "_dt_fiel_.icon.root='../../images/req_fiel.gif';";
//$dtree.= "document.getElementById('req_fiel_tree').innerHTML = _dt_fiel_;\n";
$sel.= "</select>\n";
return $sel;
}
//Construit l'arbre des champs accessibles a partir d'un Univers
function getFielTree($univ_id=0 ){
global $msg;
if (!$univ_id) return false;
$dtree = "<script type='text/javascript'>\n";
$dtree.= "_dt_fiel_ = new dTree('_dt_fiel_');\n";
//Creation racine liens depuis les champs de la table de reference
$dtree.="_dt_fiel_.add('U".$univ_id."',-1,' ".addslashes($msg['req_fiel_lbl'])."');\n";
//Creation noeud table de base
$bt_id=$this->getReferenceTableID($univ_id);
$t_info=$this->getTableInfo($bt_id);
$t_desc=$t_info['desc'];
$node_id='T'.$bt_id;
$dtree.="_dt_fiel_.add('".$node_id."','U".$univ_id."','".addslashes($t_desc)."');\n";
//$link=$node_id;
//Creation noeuds de l'arbre
$dtree.=$this->getNodesTree($univ_id, $bt_id, $node_id);
$dtree.= "_dt_fiel_.icon.root='../../images/req_fiel.gif';";
$dtree.= "document.getElementById('req_fiel_tree').innerHTML = _dt_fiel_;\n";
$dtree.= "</script>\n";
return $dtree;
}
//Construit les noeuds de l'arbre des champs accessibles a partir d'un univers
function getNodesTree($univ_id, $table_id, $prev_node_id=0, $prev_rel_id=0, $prev_desc=''){
global $msg, $charset;
//Info univers
$u_info=$this->t_univ[$univ_id];
//Info table
$t_info=$this->getTableInfo($table_id);
$t_name=$t_info['name'];
$t_desc=$t_info['desc'];
//Info champs
$f_info=$t_info['fields'];
foreach($f_info as $f_id=>$f_tab) {
$f_name=$f_tab['name'];
$exc_fields=explode(',',$u_info[$prev_rel_id]['except']);
if (!in_array($t_name.'.'.$f_name,$exc_fields)) {
//Affichage noeud champ
$drag_text='';
if(!$prev_rel_id) {
$drag_text.=$f_tab['desc'];
$node_id='F'.$f_id;
} else {
$drag_text=$prev_desc.'.'.$f_tab['desc'];
$node_id=$prev_node_id.'_F'.$f_id;
}
$dtree.="_dt_fiel_.add('".$node_id."','".$prev_node_id."','".addslashes($f_tab['desc'])."', \"draggable='yes' dragtype='cell' dragtext='".htmlentities($drag_text, ENT_QUOTES, $charset)."' dragged_type='FI' dragged_id='".$node_id."' \", '#');\n";
//Est-ce un champ lie?
if (is_array($u_info['relations']['from'][$t_name.'.'.$f_name])) {
foreach ($u_info['relations']['from'][$t_name.'.'.$f_name] as $k=>$rel_id) {
$rel_to=explode('.',$u_info[$rel_id]['to']);
$rel_type=$u_info[$rel_id]['type'];
$rel_desc=$u_info[$rel_id]['desc'];
$rel_prev=$u_info[$rel_id]['prev'];
$rel_through=$u_info[$rel_id]['through'];
if ($prev_rel_id==$rel_prev) {
switch($rel_type) {
case 'S1' :
break;
default :
//Calcul noeud relation
$r_node_id=$node_id.'_R'.$rel_id;
//Affichage noeud relation
$dtree.="_dt_fiel_.add('".$r_node_id."','".$prev_node_id."','".addslashes($rel_desc)."');\n";
$r_link=$r_node_id;
//On descend dans l'arbre
$s_t_id=$this->t_schema[$rel_to[0]];
$dtree.=$this->getNodesTree($univ_id, $s_t_id, $r_link, $rel_id, $rel_desc);
//Mise a jour table relations
$this->addJoin($univ_id, $rel_id);
//Est-ce une relation multivaluee?
if ($rel_through) {
$tmp=explode('.',$rel_through);
$tmp2=explode(',',$tmp[1]);
$lt_id=$this->getTableID($tmp[0]);
$lt_info=$this->getTableInfo($lt_id);
foreach($lt_info['fields'] as $lf_id=>$lf_tab){
if (!in_array($lf_tab['name'], $tmp2)) {
$drag_text=$rel_desc.'.'.$lf_tab['desc'];
$lr_node_id=$r_node_id.'_F'.$lf_id;
$dtree.="_dt_fiel_.add('".$lr_node_id."','".$r_node_id."','".addslashes($lf_tab['desc'])."', \"draggable='yes' dragtype='cell' dragtext='".htmlentities($drag_text, ENT_QUOTES, $charset)."' dragged_type='FI' dragged_id='".$lr_node_id."' \", '#');\n";
}
}
}
break;
}
}
}
}
}
}
return $dtree;
}
//Ajout relations a la table des jointures
function addJoin($univ_id,$rel_id,$n=0) {
if (!($univ_id && $rel_id)) return;
$t_rel = $this->t_univ[$univ_id][$rel_id];
$tg_desc='';
if (!$t_rel['prev']) {
$tg_desc=$this->t_univ[$univ_id]['name'];
} else {
$tg_desc=$this->t_univ[$univ_id][$t_rel['prev']]['desc'];
}
$this->t_join[$rel_id][$n]['tg_desc']=$tg_desc;
$this->t_join[$rel_id][$n]['td_desc']=$t_rel['desc'];
switch($t_rel['type']) {
case '11':
$this->t_join[$rel_id][$n]['join']='S';
break;
case 'N0':
case 'N1':
case 'NN':
$this->t_join[$rel_id][$n]['join']='R';
break;
default:
case '0N':
case '1N':
$this->t_join[$rel_id][$n]['join']='L';
break;
}
return;
}
//Retourne un formulaire pour la table des jointures
function getJoinTab() {
global $charset,$joi_tab_line_select;
$tpl='';
foreach($this->t_join as $rel_id=>$rels_tab) {
foreach($rels_tab as $rel_tab){
$tpl.=$joi_tab_line_select;
$tpl = str_replace('!!tg_desc!!', htmlentities($rel_tab['tg_desc'],ENT_QUOTES,$charset), $tpl);
$tpl = str_replace('!!td_desc!!', htmlentities($rel_tab['td_desc'],ENT_QUOTES,$charset), $tpl);
$tpl = str_replace('!!R_rel!!','R'.$rel_id,$tpl);
$tpl = str_replace('!!N_rel!!','R'.$rel_id,$tpl);
/*
* Ancienne version, avec definition des jointures en fonction de la relation
switch($rel_tab['join']) {
case 'S': //jointure stricte
$tpl=str_replace('!!S_sel!!',"checked='checked'",$tpl);
break;
case 'R': //jointure a droite
$tpl=str_replace('!!R_sel!!',"checked='checked'",$tpl);
break;
case 'L': //jointure a gauche
$tpl=str_replace('!!L_sel!!',"checked='checked'",$tpl);
break;
case 'N': //pas de jointure
$tpl=str_replace('!!N_sel!!',"checked='checked'",$tpl);
break;
default:
break;
}
$tpl=str_replace('!!S_sel!!','',$tpl);
*/
$tpl=str_replace('!!S_sel!!',"checked='checked'",$tpl);
$tpl=str_replace('!!R_sel!!','',$tpl);
$tpl=str_replace('!!L_sel!!','',$tpl);
$tpl=str_replace('!!N_sel!!','',$tpl);
}
}
return $tpl;
}
//Construit l'arbre des fonctions SQL
function getFuncTree(){
global $msg, $charset;
$dtree = "<script type='text/javascript'>\n";
$dtree.= "_dt_func_ = new dTree('_dt_func_');\n";
$dtree.="_dt_func_.add(0,-1,' ".addslashes($msg['req_func_lbl'])."');\n";
foreach($this->t_fct_grp as $k=>$v) {
$g_name=$v['name'];
$dtree.="_dt_func_.add(".($k).",0,'".addslashes($g_name)."');\n";
}
foreach($this->t_fct as $k=>$v) {
$f_name=$v['name'];
//TODO Traduction des messages
//$f_desc=$msg['req_fct_'.$f_name];
$f_desc=$f_name;
$grp_id=1*$v['group'];
$dtree.="_dt_func_.add(".$k.",".$grp_id.",'".addslashes($f_desc)."', \" draggable='yes' dragtype='cell' dragtext='".htmlentities($f_desc, ENT_QUOTES, $charset)."' dragged_type='FU' dragged_id='".$k."'\",'#' );\n";
}
$dtree.= "_dt_func_.icon.root='../../images/req_func.gif';";
$dtree.= "document.getElementById('req_func_tree').innerHTML = _dt_func_;\n";
$dtree.= "</script>\n";
return $dtree;
}
//Construit l'arbre des sous-requetes SQL
function getSubrTree(){
global $msg,$charset,$dbh;
$dtree='';
$q = "select idproc, name, comment, libproc_classement, num_classement from procs left join procs_classements on idproc_classement=num_classement ";
$q.= "where trim(requete) like('select %') ";
$q.= "order by libproc_classement,name ";
$r = pmb_mysql_query($q, $dbh);
if(pmb_mysql_num_rows($r)) {
$dtree = "<script type='text/javascript'>\n";
$dtree.= "_dt_subr_ = new dTree('_dt_subr_');\n";
$dtree.= "_dt_subr_.add(0,-1,' ".addslashes($msg['req_subr_lbl'])."');\n";
$t_num_classement=array();
while(($row=pmb_mysql_fetch_object($r))) {
if(in_array($row->num_classement,$t_num_classement)===false) {
if(!$row->num_classement) {
$libproc_classement=$msg['proc_clas_aucun'];
} else {
$libproc_classement=$row->libproc_classement;
}
$t_num_classement[]=$row->num_classement;
$dtree.= "_dt_subr_.add('c_".$row->num_classement."',0,'".addslashes($libproc_classement)."');\n";
}
$dtree.="_dt_subr_.add(".$row->idproc.",'c_".$row->num_classement."','".addslashes($row->name)."', \" draggable='yes' dragtype='cell' dragtext='".htmlentities($row->name, ENT_QUOTES, $charset)."' dragged_type='SU' dragged_id='".$row->idproc."'\",'#' );\n";
}
$dtree.= "_dt_subr_.icon.root='../../images/req_subr.gif';";
$dtree.= "document.getElementById('req_subr_tree').innerHTML = _dt_subr_;\n";
$dtree.= "</script>\n";
}
return $dtree;
}
//Construction requete a partir du formulaire poste
function buildRequest($req_type,$req_univ,$req_nb_lines,$req_datas) {
global $msg;
if (!$req_type || !$req_univ || !$req_nb_lines || !is_array($req_datas)) return;
//TODO a mettre a jour au fur et a mesure de l'evolution du soft
if ($req_type!='2') return ;
//donnees de la requete
$t_da=$req_datas['DA']; //donnees
$t_fi=$req_datas['FI']; //filtres
$t_va=$req_datas['VA']; //valeurs
$t_al=$req_datas['AL']; //alias
$t_vi=$req_datas['VI']; //visibilites
$t_gr=$req_datas['GR']; //regroupements
$t_so=$req_datas['SO']; //ordres
$t_jo=$req_datas['JO']; //jointures
$t_li=$req_datas['LI']; //limites
$t_sql= array(); //Table de stockage des elements de la requete SQL
//Type de la requete
$t_sql['type']=$this->t_type[$req_type]['type'];
$t_rel=array();
//Pour chacune des lignes de la requete
for($i=1;$i<$req_nb_lines+1;$i++) {
//recuperation donnees
if(is_array($t_da[$i]) && count($t_da[$i])) {
$t=$this->buildDataContent($t_da[$i]);
if(count($t['R'])) $t_rel+=$t['R'];
if(count($t['D'])) $t_sql['data'][$i]=$t['D'];
unset($t);
}
//recuperation valeurs
if (is_array($t_va[$lig])) {
}
//recuperation filtres
if(is_array($t_fi[$i]) && count($t_fi[$i])) {
$t=$this->buildFilterContent($t_fi[$i]);
if(count($t['R'])) $t_rel+=$t['R'];
if(count($t['F'])) $t_sql['filter'][$i]=$t['F'];
unset($t);
}
//recuperation alias
if (trim($t_al[$i][0])) $t_sql['alias'][$i]=$t_al[$i][0];
//recuperation visibilites
if($t_vi[$i][0]) $t_sql['visibility'][$i]=$t_vi[$i][0];
//recuperation regroupements
if($t_gr[$i][0]) $t_sql['group'][$i]=$t_gr[$i][0];
//recuperation tris
if($t_so[$i][0]) {
switch ($t_so[$i][0]){
case '1':
$t_sql['sort'][$i]='asc';
break;
case '2':
$t_sql['sort'][$i]='desc';
break;
case '0':
default:
break;
}
}
}
//Creation jointures et tables
//Suppression des jointures redondantes
$t_rel2=array_unique($t_rel);
//Tri par nb de relations
$t_rel3=array();
foreach($t_rel2 as $v2) {
$t_rel3[substr_count($v2,'_')][] =$v2;
}
ksort($t_rel3);
//Liste des jointures a prendre en consideration
$s_rel='';
while(count($t_rel3)!==0) {
$t_rel4=array_pop($t_rel3);
foreach($t_rel4 as $v4) {
if(strpos($s_rel,$v4)===false) $s_rel.=$v4;
}
}
$t_rel5 = explode('_',$s_rel);
//Liste des tables a prendre en compte dans la requete
$t_sql['from']=array();
//La table de reference
$t_sql['from'][0][0]['prev']=0;
$t_sql['from'][0][0]['t_from']=$this->t_schema[$this->getReferenceTableID($req_univ)]['name'];
//Pour chaque relation
foreach($t_rel5 as $v5) {
if($v5) {
$t_rel6 = $this->getFullJoin($req_univ, $v5, $t_jo);
$t_sql['from']=array_merge($t_sql['from'],$t_rel6);
}
}
//*********************************************************************
//Creation de la requete !!!
$request = '';
//Le type
$request.= $t_sql['type'].' ';
//Les donnees
if (is_array($t_sql['data'])) {
$t_data=array();
foreach ($t_sql['data'] as $k6=>$v6) {
if ($t_sql['visibility'][$k6]) {
$t_data[$k6]=$v6;
if($t_sql['alias'][$k6]) $t_data[$k6].= " as \"".$t_sql['alias'][$k6]."\"";
}
}
$s_data=implode(", ",$t_data);
$request.=$s_data.' ';
} else {
$request.="* ";
}
//les tables et jointures
$t_from=array();
foreach($t_sql['from'] as $k7=>$v7) {
if (!$k7) {
$t_from[]=$v7[0]['t_from'].' ';
} else {
foreach($v7 as $k8=>$v8) {
$prefix = '';
if ($v8['prev']) {
$prefix=$v8['prev'].'_';
}
switch($v8['join']) {
case 'L' : //jointure a gauche
$t_from[]='left join ';
$t_from[]=$v8['t_to'].' as '.$k7.'_'.$v8['t_to'];
$t_from[]=' on ';
$t_from[]=$k7.'_'.$v8['t_to'].'.'.$v8['f_to'].'=';
$t_from[]=$prefix.$v8['t_from'].'.'.$v8['f_from'].' ';
break;
case 'R': //jointure a droite
$t_from[]='right join ';
$t_from[]=$v8['t_to'].' as '.$k7.'_'.$v8['t_to'];
$t_from[]=' on ';
$t_from[]=$k7.'_'.$v8['t_to'].'.'.$v8['f_to'].'=';
$t_from[]=$prefix.$v8['t_from'].'.'.$v8['f_from'].' ';
break;
case 'S': //jointure stricte
$t_from[]='join ';
$t_from[]=$v8['t_to'].' as '.$k7.'_'.$v8['t_to'];
$t_from[]=' on ';
$t_from[]=$k7.'_'.$v8['t_to'].'.'.$v8['f_to'].'=';
$t_from[]=$prefix.$v8['t_from'].'.'.$v8['f_from'].' ';
break;
case 'N': //Pas de jointure
default :
$t_from[]=', ';
$t_from[]=$v8['t_to'].' as '.$k7.'_'.$v8['t_to'];
break;
}
}
}
}
$s_from= 'from '.implode('',$t_from).' ';
$request.=$s_from;
//Les clauses where
$s_filter='';
$last_data=$msg['req_error'];
if (is_array($t_sql['filter'])) {
$t_filter=array();
foreach ($t_sql['filter'] as $k6=>$v6) {
for($i=$k6;$i>=0;$i--){
if($t_sql['data'][$i]) {
$last_data=$t_sql['data'][$i];
break;
}
}
foreach($v6 as $v7) {
if(count($t_filter[$k6])) {
$t_filter[$k6].=' OR ';
}
$t_filter[$k6].=$last_data.$v7;
}
}
$s_filter.='('.implode(") and (",$t_filter).')';
}
if($s_filter) {
$request.= " where ".$s_filter." ";
}
//Les regroupements
if (is_array($t_sql['group'])) {
$t_group=array();
foreach ($t_sql['group'] as $k8=>$v8) {
if ($v8==1 && $t_sql['data'][$k8]) {
$t_group[]=$t_sql['data'][$k8];
}
}
if (count($t_group!=0)) {
$s_group=implode(", ",$t_group);
$request.= "group by $s_group ";
}
}
//Les tris
if (is_array($t_sql['sort'])) {
$t_sort=array();
foreach ($t_sql['sort'] as $k9=>$v9) {
$t_sort[]=$t_sql['data'][$k9].' '.$v9;
}
if (count($t_sort!=0)) {
$s_sort=implode(", ",$t_sort);
$request.='order by '.$s_sort.' ';
}
}
//Les limites
$s_limit='';
if(is_array($t_li)) {
if ($t_li['B']!=='') {
$s_limit.= $t_li['B'].', ';
}
if ($t_li['Q']!=='') {
$s_limit.= $t_li['Q'];
}
}
if ($s_limit!=='') {
$request.='limit '.$s_limit;
}
return $request;
}
//Extraction des parametres d'une fonction
//$f_id = id de fonction
function getFunction($f_id) {
$data=array();
if($this->t_fct[$f_id]['name']) {
$data['id']=$this->t_fct[$f_id]['id'];
$data['name']=$this->t_fct[$f_id]['name'];
$data['parenthesis']=$this->t_fct[$f_id]['parenthesis'];
$data['param']=$this->t_fct[$f_id]['param'];
$data['filter']=$this->t_fct[$f_id]['filter'];
}
return $data;
}
//Extraction d'une sous-requete
//$r_id = id de requete
function getSubRequest($r_id) {
global $dbh;
$sub='';
$q = "select requete from procs where idproc='".$r_id."'";
$r = pmb_mysql_query($q,$dbh);
if(pmb_mysql_num_rows($r)) {
$sub = '('.pmb_mysql_result($r,0,0).')';
}
return $sub;
}
//Extraction des infos de champ et de relation a partir de l'identifiant poste
//$f_id = id de champ
function getField($f_id) {
$data=array();
$data['D']=''; //Stockage donnees calculees
$data['R']=''; //Stockage relations trouvees
$tmp=explode('_',$f_id);
$tmp2=explode('-',end($tmp)); //Id dernier champ
$t_id=substr($tmp2[0],1);
$f_id=substr(end($tmp),1);
$t_name=$this->t_schema[$t_id]['name'];
$f_name=$this->t_schema[$t_id]['fields'][$f_id]['name'];
if (count($tmp)>1) {
$rel_id=$tmp[count($tmp)-2];
foreach($tmp as $v1) {
if (substr($v1,0,1)=='R') {
$data['R'].=$v1.'_';
}
}
$data['D']=$rel_id.'_';
} else {
$rel_id=0;
$data['R']=0;
}
$data['D'].=$t_name.'.'.$f_name;
return $data;
}
//Extraction des jointures multiples
function getFullJoin($univ_id, $rel, $t_jo) {
$rel_id=substr($rel,1);
$join=array();
$tf_from=array();
$tf_to=array();
$r_prev=$this->t_univ[$univ_id][$rel_id]['prev'];
$tf_from=explode('.',$this->t_univ[$univ_id][$rel_id]['from']);
$t_from=$tf_from[0];
$f_from=$tf_from[1];
$tf_to=explode('.',$this->t_univ[$univ_id][$rel_id]['to']);
$t_to=$tf_to[0];
$f_to=$tf_to[1];
$r_through=$this->t_univ[$univ_id][$rel_id]['through'];
if ($r_through) {
$tmp=explode('.',$r_through);
$t_int=$tmp[0];
$tmp2=explode(',',$tmp[1]);
$f_int_from=$tmp2[0];
$f_int_to=$tmp2[1];
if ($r_prev) {
$join[$rel][0]['prev']='R'.$r_prev;
} else {
$join[$rel][0]['prev']=0;
}
$join[$rel][0]['t_from']=$t_from;
$join[$rel][0]['f_from']=$f_from;
$join[$rel][0]['t_to']=$t_int;
$join[$rel][0]['f_to']=$f_int_from;
$join[$rel][0]['join']=$t_jo[$rel][0];
$join[$rel][1]['prev']=$rel;
$join[$rel][1]['t_from']=$t_int;
$join[$rel][1]['f_from']=$f_int_to;
$join[$rel][1]['t_to']=$t_to;
$join[$rel][1]['f_to']=$f_to;
$join[$rel][1]['join']=$t_jo[$rel][0];
} else {
if ($r_prev) {
$join[$rel][0]['prev']='R'.$r_prev;
} else {
$join[$rel][0]['prev']=0;
}
$join[$rel][0]['t_from']=$t_from;
$join[$rel][0]['f_from']=$f_from;
$join[$rel][0]['t_to']=$t_to;
$join[$rel][0]['f_to']=$f_to;
$join[$rel][0]['join']=$t_jo[$rel][0];
}
return $join;
}
//Recuperation des attributs de fonction a partir de l'identifiant
function getAttributes($fct_id=0,$c_type='') {
global $charset;
if (!$fct_id) die('No function id');
$tp_fct=$this->t_fct[$fct_id]['param'];
$xml="<params parenthesis=\"".$this->t_fct[$fct_id]['parenthesis']."\" remove=\"".$this->t_fct[$fct_id]['remove']."\" >\n";
if(count($tp_fct)) {
foreach($tp_fct as $v) {
if($c_type !="FI" || ($v['order'] >= $this->t_fct[$fct_id]['filter']) ) {
$xml.= "<param order=\"".$v['order']."\" content=\"".$v['content']."\" optional=\"".$v['optional']."\" repeat_from=\"".$v['repeat_from']."\" value=\"".trim(htmlspecialchars($v['value'],ENT_QUOTES,$charset))."\" />\n";
}
}
}
$xml.="</params>\n";
return $xml;
}
//construit la partie data d'une ligne de requete
function buildDataContent($t_da) {
$t_ret=array();
$t_fct=array(); //table de stockage de l'expression sql pour une fonction
$tab_fu=array(); //table de stockage des fonctions en cours de traitement
$n_fu=0;
foreach($t_da as $e_da) {
$da_type=key($e_da);
$da_cont=$e_da[$da_type];
switch ($da_type) {
case 'FU' : //Debut Fonction
//si on est dans une fonction, c'est un argument
if ($n_fu) {
$tab_fu[$n_fu]['params'][]=array('sub',($n_fu+1));
}
//stockage id fonction courante
$n_fu++;
$tab_fu[$n_fu]['id']=$da_cont;
break;
case 'order' : //Nouvel element fonction
$tab_fu[$n_fu]['params'][]=array('order',$da_cont);
break;
case 'FU_E' : //Fin Fonction
//Traitement de la fonction ici
$f_format=$this->getFunction($tab_fu[$n_fu]['id']);
//print '<pre>';print_r($tab_fu);print '</pre>';
$f_content=array();
$order=0;
foreach($tab_fu[$n_fu]['params'] as $v) {
switch($v[0]) {
case 'order' :
if ($f_format['param'][$v[1]]['optional']!='yes' && $f_format['param'][$v[1]]['content']=='keyword') {
if($f_format['param'][$v[1]]['before_sep']) {
$f_content[]=$f_format['param'][$v[1]]['before_sep'];
}
$f_content[]=$f_format['param'][$v[1]]['value'];
}
if($f_format['parenthesis']==$v[1]) $f_content[]='(';
$order=$v[1];
break;
case 'arg' :
if($f_format['param'][$order]['before_sep']) {
$f_content[]=$f_format['param'][$order]['before_sep'];
}
$f_content[]=$v[1];
break;
case 'sub' :
if($f_format['param'][$order]['before_sep']) {
$f_content[]=$f_format['param'][$order]['before_sep'];
}
$f_content[]=array_pop($t_fct);
default :
break;
}
}
//parenthese fermante
if($f_format['parenthesis']) $f_content[]=')';
//print '<pre>';print_r($f_content);print '</pre>';
//ajout de la fonction a la table de stockage de l'expression sql pour une fonction
$t_fct[]=implode('',$f_content);
array_pop($tab_fu);
$n_fu--;
if($n_fu==0) {
$t_ret['D']=implode(' ',$t_fct);
}
break;
case 'FI' : //Champ
$fi_data= array();
$fi_data=$this->getField($da_cont);
if($fi_data['R']) {
$t_ret['R'][]=$fi_data['R'];
}
//si on est dans une fonction, c'est un argument
if($n_fu) {
$tab_fu[$n_fu]['params'][]=array('arg',$fi_data['D']);
} else {
$t_ret['D']=$fi_data['D'];
}
break;
case 'TE' : //Saisie libre
//si on est dans une fonction, c'est un argument
if($n_fu) {
$tab_fu[$n_fu]['params'][]=array('arg',stripslashes($da_cont));
} else {
$t_ret['D']=stripslashes($da_cont);
}
break;
case 'SU' : //Sous requete
//si on est dans une fonction, c'est un argument
if($n_fu) {
$tab_fu[$n_fu]['params'][]=array('arg',$this->getSubRequest($da_cont));
} else {
$t_ret['D']=$this->getSubRequest($da_cont);
}
break;
default:
break;
}
}
return $t_ret;
}
//construit la partie filtre d'une ligne de requete (where)
function buildFilterContent($t_fi) {
$t_ret=array();
$t_fct=array(); //table de stockage de l'expression sql pour une fonction
$tab_fu=array(); //table de stockage des fonctions en cours de traitement
$n_fu=0;
foreach($t_fi as $e_fi) {
$fi_type=key($e_fi);
$fi_cont=$e_fi[$fi_type];
switch ($fi_type) {
case 'FU' : //Debut Fonction
//si on est dans une fonction, c'est un argument
if ($n_fu) {
$tab_fu[$n_fu]['params'][]=array('sub',($n_fu+1));
}
//stockage id fonction courante
$n_fu++;
$tab_fu[$n_fu]['id']=$fi_cont;
break;
case 'order' : //Nouvel element fonction
$tab_fu[$n_fu]['params'][]=array('order',$fi_cont);
break;
case 'FU_E' : //Fin Fonction
//Traitement de la fonction ici
$f_format=$this->getFunction($tab_fu[$n_fu]['id']);
$f_content=array();
$order=0;
foreach($tab_fu[$n_fu]['params'] as $v) {
switch($v[0]) {
case 'order' :
if ($f_format['param'][$v[1]]['optional']!='yes' && $f_format['param'][$v[1]]['content']=='keyword') {
$f_content[]=$f_format['param'][$v[1]]['value'];
}
if($f_format['parenthesis']==$v[1]) $f_content[]='(';
$order=$v[1];
break;
case 'arg' :
if($f_format['param'][$order]['before_sep']) {
$f_content[]=$f_format['param'][$order]['before_sep'];
}
$f_content[]=$v[1];
break;
case 'sub' :
if($f_format['param'][$order]['before_sep']) {
$f_content[]=$f_format['param'][$order]['before_sep'];
}
$f_content[]=array_pop($t_fct);
default :
break;
}
}
//parenthese fermante
if($f_format['parenthesis']) $f_content[]=')';
//ajout de la fonction a la table de stockage de l'expression sql pour une fonction
$t_fct[]=implode('',$f_content);
array_pop($tab_fu);
$n_fu--;
if($n_fu==0) {
$eq="";
if(!$f_format['filter']) $eq="=";
$t_ret['F'][]=$eq.implode(' ',$t_fct);
unset($t_fct);
}
break;
case 'FI' : //Champ
$fi_filter= array();
$fi_filter=$this->getField($fi_cont);
if($fi_filter['R']) {
$t_ret['R'][]=$fi_filter['R'];
}
//si on est dans une fonction, c'est un argument
if($n_fu) {
$tab_fu[$n_fu]['params'][]=array('arg',$fi_filter['D']);
} else {
$t_ret['F'][]="=".$fi_filter['D'];
}
break;
case 'TE' : //Saisie libre
//si on est dans une fonction, c'est un argument
if($n_fu) {
$tab_fu[$n_fu]['params'][]=array('arg',stripslashes($fi_cont));
} else {
$t_ret['F'][]="=".stripslashes($fi_cont);
}
break;
case 'SU' : //Sous requete
//si on est dans une fonction, c'est un argument
if($n_fu) {
$tab_fu[$n_fu]['params'][]=array('arg',$this->getSubRequest($fi_cont));
} else {
$t_ret['F'][]="=".$this->getSubRequest($fi_cont);
}
break;
default:
break;
}
}
return $t_ret;
}
}
class reqParser {
var $parser;
var $t=array();
var $cur_id=0;
var $cur_rel=0;
var $cur_fct=0;
function reqParser() {
}
function run($file) {
global $include_path;
global $charset;
//Recherche du fichier XML de description
$file = $include_path.'/requests/'.$file.'.xml';
$xml=file_get_contents($file,"r") or die("Can't find XML file $file");
unset($this->t);
$this->cur_id=0;
$rx = "/<?xml.*encoding=[\'\"](.*?)[\'\"].*?>/m";
if (preg_match($rx, $xml, $m)) $encoding = strtoupper($m[1]);
else $encoding = "ISO-8859-1";
$this->parser = xml_parser_create($encoding);
xml_set_object($this->parser, $this);
xml_parser_set_option($this->parser, XML_OPTION_TARGET_ENCODING, $charset);
xml_parser_set_option($this->parser, XML_OPTION_CASE_FOLDING, FALSE);
xml_parser_set_option($this->parser, XML_OPTION_SKIP_WHITE, TRUE);
xml_set_element_handler($this->parser, "tagStart", "tagEnd");
if (!xml_parse($this->parser, $xml, TRUE)) {
die( sprintf( "erreur XML %s la ligne: %d",
xml_error_string(xml_get_error_code($this->parser ) ),
xml_get_current_line_number($this->parser) ) );
}
xml_parser_free($this->parser);
return ($this->t);
}
function tagStart($parser, $tag, $att) {
global $msg;
switch ($tag) {
case 'table':
$this->t[$att['id']]['name']= $att['name'];
$this->t[$att['id']]['desc']= $att['desc'];
$this->t[$att['id']]['pkid']= $att['pkid'];
$this->t[$att['name']]=$att['id'];
$this->cur_id=$att['id'];
break;
case 'field':
$this->t[$this->cur_id]['fields'][$att['id']]['name']= $att['name'];
$this->t[$this->cur_id]['fields'][$att['id']]['desc']= $att['desc'];
$this->t[$this->cur_id]['fields'][$att['id']]['type']= $att['type'];
$this->t[$this->cur_id]['fields'][$att['id']]['name']= $att['name'];
$this->t[$this->cur_id]['fields'][$att['id']]['length']= $att['length'];
$this->t[$this->cur_id]['fields'][$att['id']]['precision']= $att['precision'];
$this->t[$this->cur_id]['fields'][$att['id']]['autoincrement']= $att['autoincrement'];
$this->t[$this->cur_id]['fields'][$att['id']]['enum']= $att['enum'];
$this->t[$this->cur_id]['fields'][$att['id']]['defval']= $att['defval'];
break;
case 'lien':
$this->t['cp_links'][$att['child']]=$att['parent'];
$this->t['pc_links'][$att['parent']][]=$att['child'];
break;
case 'REQ_TYPE':
$this->t[$att['id']]['type']= $att['type'];
$this->t[$att['id']]['name']= $msg[$att['name']];
break;
case 'REQ_CONTENT':
$this->t['REQ_CONTENT'][$att['id']]['type']= $att['type'];
$this->t['REQ_CONTENT'][$att['id']]['name']= $msg[$att['name']];
break;
case 'REQ_CONTAINER':
$this->t['REQ_CONTAINER'][$att['id']]['type']= $att['type'];
$this->t['REQ_CONTAINER'][$att['id']]['name']= $msg[$att['name']];
break;
case 'REQ_UNIVERSE':
$this->t[$att['id']]['name']= $msg[$att['name']];
$this->t[$att['id']]['ref']= $att['ref'];
$this->cur_id=$att['id'];
break;
case 'REQ_RELATION':
$this->t[$this->cur_id]['relations']['from'][$att['from']][]=$att['id'];
$this->t[$this->cur_id][$att['id']]['from']=$att['from'];
$this->t[$this->cur_id][$att['id']]['prev']=$att['prev'];
$this->t[$this->cur_id][$att['id']]['type']=$att['type'];
$this->t[$this->cur_id][$att['id']]['to']=$att['to'];
$this->t[$this->cur_id][$att['id']]['desc']=$att['desc'];
$this->t[$this->cur_id][$att['id']]['except']=$att['except'];
$this->cur_rel=$att['id'];
break;
case 'REQ_THROUGH':
$this->t[$this->cur_id][$this->cur_rel]['through']= $att['through'];
break;
case 'REQ_FUNCTION_GROUP' :
$this->t['REQ_FUNCTION_GROUP'][$att['id']]['name']=$att['name'];
break;
case 'REQ_FUNCTION':
$this->t['REQ_FUNCTION'][$att['id']]['name']=$att['name'];
$this->t['REQ_FUNCTION'][$att['id']]['group']=$att['group'];
$this->t['REQ_FUNCTION'][$att['id']]['parenthesis']= $att['parenthesis'];
$this->t['REQ_FUNCTION'][$att['id']]['remove']= $att['remove'];
$this->t['REQ_FUNCTION'][$att['id']]['filter']= $att['filter'];
$this->cur_fct=$att['id'];
break;
case 'FCT_PARAM':
$this->t['REQ_FUNCTION'][$this->cur_fct]['param'][$att['order']]['order']= $att['order'];
$this->t['REQ_FUNCTION'][$this->cur_fct]['param'][$att['order']]['content']= $att['content'];
$this->t['REQ_FUNCTION'][$this->cur_fct]['param'][$att['order']]['optional']= $att['optional'];
$this->t['REQ_FUNCTION'][$this->cur_fct]['param'][$att['order']]['value']= $att['value'];
$this->t['REQ_FUNCTION'][$this->cur_fct]['param'][$att['order']]['before_sep']= $att['before_sep'];
$this->t['REQ_FUNCTION'][$this->cur_fct]['param'][$att['order']]['repeat_from']= $att['repeat_from'];
break;
default :
break;
}
return;
}
function tagEnd($parser, $tag) {
return;
}
}
?>