<?php
/**
 * Ce fichier surcharge om_gen.class.php
 * @package framework_openmairie om5_rad
 * François RAYNAUD 21/02/2024
 *
 */

require_once PATH_OPENMAIRIE."om_gen.class.php";


class gen_plus extends gen {

	var $om_forms;
	var $om_forms_liste;
	var $om_tables_parametre;
	
	// initialisation
	// définit le tableau om_forms issu de la table om_forms
	function init_generation_for_table($table) {
		parent::init_generation_for_table($table);
		// **** ajout du tableau om_forms ***
        $sql = " SELECT om_forms.column_name, SPLIT_PART(om_forms.column_name, '.', 1) as table_name,";
		$sql .= "  data_type, parametres->>'type' as type, parametres->>'bloc' as bloc, parametres->>'position' as position, ";
		$sql .= "  parametres->>'calcul' as calcul, parametres->>'libelle' as libelle, parametres->>'liste' as liste ";		
		$sql .= " FROM ".DB_PREFIXE."om_forms ";
		$sql .= " left join ".DB_PREFIXE."om_champs on om_forms.column_name=om_champs.column_name";		
		$sql .= " where SPLIT_PART(om_forms.column_name, '.', 1) = '".$this->table."'";
		$sql .= " order by parametres->>'bloc',parametres->>'position' ";
        // $this->msg .= $sql."<br>"; 
		// Exécution de la requête
		$res = $this->f->db->query($sql);
		// Logger
        $this->f->addToLog(__METHOD__."(): db->query(\"".$sql."\");", VERBOSE_MODE);
        // Vérification d'une éventuelle erreur de base de données
        $this->f->isDatabaseError($res);        
		while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
			$temp = explode(".", $row['column_name']);
			$this->om_forms[$temp[1]]["position"]=$row["position"];
			$this->om_forms[$temp[1]]["bloc"]=$row["bloc"];
			$this->om_forms[$temp[1]]["type"]=$row["type"];
			$this->om_forms[$temp[1]]["libelle"]=$row["libelle"];
			$this->om_forms[$temp[1]]["column_name"]=$row["column_name"];
			$this->om_forms[$temp[1]]["calcul"]=$row["calcul"];
			$this->om_forms[$temp[1]]["data_type"]=$row["data_type"];
			$this->om_forms[$temp[1]]['name']=$temp[1];
			$this->om_forms[$temp[1]]["liste"]=$row["liste"];
			//$this->msg .= $temp[1]."<br>";
			//$this->msg .= $this->om_forms[$temp[1]]["column_name"]." ".$this->om_forms[$temp[1]]["calcul"]=$row["calcul"]."<br>"; 
	    }
		$this->f->addToLog(__METHOD__."(): om_forms(\"".print_r($this->om_forms)."\");", DEBUG_MODE);
		
		// **** ajout du tableau om_forms_liste ***
        $sql = " SELECT om_forms.column_name, SPLIT_PART(om_forms.column_name, '.', 1) as table_name,";
		$sql .= "  data_type, parametres->>'type' as type, parametres->>'position_liste' as position_liste, ";
		$sql .= "  parametres->>'calcul' as calcul, parametres->>'libelle_court' as libelle_court, ";
		$sql .= "  parametres->>'libelle' as libelle,parametres->>'liste' as liste ";		
		$sql .= " FROM ".DB_PREFIXE."om_forms ";
		$sql .= " left join ".DB_PREFIXE."om_champs on om_forms.column_name=om_champs.column_name";		
		$sql .= " where SPLIT_PART(om_forms.column_name, '.', 1) = '".$this->table."'";
		// $sql .= " and parametres->>'liste' = '1' "; 
		$sql .= " order by parametres->>'position_liste' ";
        // $this->msg .= $sql."<br>"; 
		// Exécution de la requête	
		$res = $this->f->db->query($sql);
		// Logger
        $this->f->addToLog(__METHOD__."(): db->query(\"".$sql."\");", VERBOSE_MODE);
        // Vérification d'une éventuelle erreur de base de données
        $this->f->isDatabaseError($res);        
		while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
			$temp = explode(".", $row['column_name']);
			$this->om_forms_liste[$temp[1]]["position_liste"]=$row["position_liste"];
			$this->om_forms_liste[$temp[1]]["type"]=$row["type"];
			$this->om_forms_liste[$temp[1]]["libelle_court"]=$row["libelle_court"];
			$this->om_forms_liste[$temp[1]]["libelle"]=$row["libelle"];
			$this->om_forms_liste[$temp[1]]["column_name"]=$row["column_name"];
			$this->om_forms_liste[$temp[1]]["calcul"]=$row["calcul"];
			$this->om_forms_liste[$temp[1]]["data_type"]=$row["data_type"];
			$this->om_forms_liste[$temp[1]]['name']=$temp[1];
			$this->om_forms_liste[$temp[1]]["liste"]=$row["liste"];
			//$this->msg .= $temp[1]."<br>";
			//$this->msg .= $this->om_forms[$temp[1]]["column_name"]." ".$this->om_forms_liste[$temp[1]]["calcul"]=$row["calcul"]."<br>"; 
	    }
		$this->f->addToLog(__METHOD__."(): om_forms(\"".print_r($this->om_forms)."\");", DEBUG_MODE);

        // parametres om_tables et om_tables_parametre (version 3)
		$sql = " select parametres ";
		$sql .=" FROM ".DB_PREFIXE."om_tables_parametre ";
		$sql .= " where table_name =  '".$this->table."'";		
		$res = $this->f->db->getOne($sql);
		// Logger
        $this->f->addToLog(__METHOD__."(): db->query(\"".$sql."\");", VERBOSE_MODE);
        // Vérification d'une éventuelle erreur de base de données
        $this->f->isDatabaseError($res);
        $temp=json_decode($res);
        $this->om_tables_parametre['libelle']=$temp->libelle;
        $this->om_tables_parametre['col1']=$temp->col1;
        $this->om_tables_parametre['col2']=$temp->col2;
        $this->om_tables_parametre['col3']=$temp->col3;
        $this->om_tables_parametre['affichage']=$temp->affichage;
        $this->om_tables_parametre['nombre_colonne']=$temp->nombre_colonne; 
        $this->om_tables_parametre['recherche']=$temp->recherche;       
        // modification des tailles et longueur pour varchar(255)
        // len = -5 pour champs sans longueur
        // affichage mini = 20
        for ($t = 0; $t < count($this->info); $t++) {
			 if ($this->info[$t]["len"] == -5) {
				 $this->info[$t]["taille_affichage"] = 20;
				 $this->info[$t]["len"] = 255;
				 //$this->info[$t]["max_saisie"] = 255;
			 }
	    }	
		
	}
	
	// construction de gen/obj/table.class.php
	// ajout de la methode setLayout
    function table_obj_class_gen($dyn = null) {
        //
        $om_dbform_class = "dbform";
        $om_dbform_require = "require_once PATH_OPENMAIRIE.\"om_dbform.class.php\";";
        if ($this->_om_dbform_class_override !== null) {
            $om_dbform_class = $this->_om_dbform_class_override;
        }
        if ($this->_om_dbform_path_override !== null) {
            $om_dbform_require = "require_once \"".$this->_om_dbform_path_override."\";";
        }
        //
        $template = "%s
%s

class %s_gen extends %s {

    protected \$_absolute_class_name = \"%s\";

    %s
    %s
    %s
    %s
    %s
    %s%s
    %s%s%s

%s

    //==================================
    // sous Formulaire
    //==================================
    %s

    //==================================
    // cle secondaire
    //==================================
    %s

}
";

        //
        $tableobj = "";
        //
        $tableobj .= $this->def_obj_meth_setvalf();
        //
        if($this->typecle=="N"){ // cle automatique si numerique
            $tableobj.="\n\n    //=================================================";
            $tableobj.="\n    //cle primaire automatique [automatic primary key]";
            $tableobj.="\n    //==================================================";
            $tableobj.=$this->def_obj_meth_setid();
            $tableobj.=$this->def_obj_meth_setvalfajout();
            $tableobj.=$this->def_obj_meth_verifierajout();
        }
        //
        $tableobj.=$this->def_obj_meth_verifier();
        //
        $tableobj.="\n\n    //==========================";
        $tableobj.="\n    // Formulaire  [form]";
        $tableobj.="\n    //==========================";
        $tableobj.=$this->def_obj_meth_settype();
        $tableobj.=$this->def_obj_meth_setonchange();
        $tableobj.=$this->def_obj_meth_settaille();
        $tableobj.=$this->def_obj_meth_setmax();
        $tableobj.=$this->def_obj_meth_setlib();
        $tableobj.=$this->def_obj_meth_setselect();
        $tableobj.=$this->def_obj_meth_setval();
        $tableobj.=$this->def_obj_meth_setlayout(); //***
        $tableobj.=$this->def_obj_meth_triggerajouter(); //***
        $tableobj.=$this->def_obj_meth_triggermodifier(); //***
        //
        return sprintf(
            $template,
            $this->def_php_script_header(),
            $om_dbform_require,
            $this->table,
            $om_dbform_class,
            $this->table,
            //
            $this->def_obj_attr_table(),
            $this->def_obj_attr_cleprimaire(),
            $this->def_obj_attr_typecle(),
            $this->def_obj_attr_required_field(),
            $this->def_obj_attr_unique_key(),
            $this->def_obj_attr_foreign_keys_extended(),
            $this->def_obj_attr_abstract_type(),
            //
            $this->def_obj_meth_get_default_libelle(),
            $this->def_obj_meth_get_var_sql_forminc__champs(),
            $this->def_obj_meth_get_var_sql_forminc__sql(),
            //
            $tableobj,
            //
            $this->def_obj_meth_setvalsousformulaire(),
            $this->def_obj_meth_clesecondaire()
        );
    }



    /**
     * Construit la définition de la méthode setLib() pour table_obj.
     * ajout d om_forms
     * @return string
     */
    function def_obj_meth_setlib() {
        $tableobj="\n\n    function setLib(&\$form, \$maj) {";
        $tableobj.="\n    //libelle des champs";
        foreach($this->info as $elem){
			if($this->om_forms[$elem['name']]['libelle']== "")
				$tableobj.="\n        \$form->setLib('".$elem['name']."',''";
            else
				$tableobj.="\n        \$form->setLib('".$elem['name']."', __('".$this->om_forms[$elem['name']]['libelle']."')";
            $tableobj.=');';
        }
        $tableobj.="\n    }";
        return $tableobj;
    }

    /**
     * Construit la définition de la méthode setLayout() pour table_obj.
     * ajout d om_forms bloc et position
     * le nombre de colonne est à determiner dans la table
     * @return string
     */

    function def_obj_meth_setlayout() {
		// nombre de colonnes		
		$nb_column=  $this->om_tables_parametre['nombre_colonne'];
        if(!is_numeric($nb_column))
			$nb_column=2;        
        $taille=12/$nb_column;
		// affichage
        $tableobj="\n\n    function setLayout(&\$form, \$maj) {";
        $tableobj.="\n    //regroupement des champs";
        // limite a 3 colonne / traitement de la guillemet
        $this->om_tables_parametre['col1']=str_replace("'","\'",$this->om_tables_parametre['col1']);
        $this->om_tables_parametre['col2']=str_replace("'","\'",$this->om_tables_parametre['col2']);
        $this->om_tables_parametre['col3']=str_replace("'","\'",$this->om_tables_parametre['col3']);
        foreach($this->om_forms as $elem){		
			if($elem['bloc']=='C1'and $elem['position']==1){
				$tableobj.= "\n			\$form->setBloc('".$elem['name']."','D',__('".$this->om_tables_parametre['col1']."'), 'col_".$taille."');";					
			}			
			if($elem['bloc']=='C2'and $elem['position']==1){
				$tableobj.= "\n			\$form->setBloc('".$archive."','F');";	
				$tableobj.= "\n			\$form->setBloc('".$elem['name']."','D',__('".$this->om_tables_parametre['col2']."'), 'col_".$taille."');";						
			}
			if($elem['bloc']=='C3'and $elem['position']==1){
				$tableobj.= "\n			\$form->setBloc('".$archive."','F');";	
				$tableobj.= "\n			\$form->setBloc('".$elem['name']."','D',__('".$this->om_tables_parametre['col3']."'), 'col_".$taille."');";						
			}
			$archive=$elem['name'];				
        }
        // reprise du dernier hors boucle
        $tableobj.= "\n			\$form->setBloc('".$archive."','F');";       
        $tableobj.="\n    }";
        
        return $tableobj;
    }

    /**
     * Construit la définition de la méthode triggermodifier() pour table_obj.
     * ajout d om_forms
     * $id, &$dnu1 = null, $val = array(), $dnu2 = null
     * @return string
     */
    function def_obj_meth_triggermodifier() {
        $tableobj="\n\n    function triggermodifier(\$id, &\$dnu1 = null, \$val = array(), \$dnu2 = null) {";
        $tableobj.="\n    //trigger avant modification";  
        foreach($this->om_forms as $elem){ 
			if($elem['calcul'] != "")   
				$tableobj.=$this->trigger_calcul($elem['name'],$elem['calcul'],$elem['data_type']);        
        }
        $tableobj.="\n    }";
        return $tableobj;         
    }

    /**
     * Construit la définition de la méthode triggerajouter() pour table_obj.
     * ajout d om_forms
     * 
     * @return string
     */

	function def_obj_meth_triggerajouter() {		
        $tableobj="\n\n    function triggerajouter(\$id, &\$dnu1 = null, \$val = array(), \$dnu2 = null) {";
        $tableobj.="\n    //trigger avant ajout";
        foreach($this->om_forms as $elem){
			if($elem['calcul'] != "")
				$tableobj.=$this->trigger_calcul($elem['name'],$elem['calcul'],$elem['data_type']);  
        }
        $tableobj.="\n    }";
        return $tableobj;
         
    }

    /**
     * Construit la formule de calcul pour triggers.
     * ajout d om_forms
     * 
     * @return string
     */
	function trigger_calcul($name, $calcul,$data_type) {
		$temp="";
		if($data_type == "numeric" or $data_type == "integer"){		
			$tp=str_replace("*", "|*|",$calcul);
			$tp=str_replace("+", "|+|",$tp);
			$tp=str_replace("-", "|-|",$tp);
			$tp=str_replace("/", "|/|",$tp);
			$tp=str_replace("(", "|(|",$tp);
			$tp=str_replace(")", "|)|",$tp);			    
			$tp=explode("|", $tp);
			$ch='';
			$i=0;
			foreach($tp as $el){			
				if($el=='*' or $el=='+' or $el=='-' or $el=='/' or $el==')' or $el=='(')
					$ch .= $el;
				else
					if($el!='') // cas ou il a une opérande et une parenthèse de suite			    
						if(is_numeric($el)) // cas ou il y a une constante forcemment numérique
							$ch .= $el;
						else
							$ch .= "\$this->valF['".$el."']";
			}
			$temp ="\n        \$this->valF['".$name."']=".$ch;
			$temp.=';';			
		}
		if($data_type == "date"){		
			$ch=$calcul;
			$temp ="\n        \$this->valF['".$name."']=".$ch;
			$temp.=';';
		}				          
        return $temp;	
	}
	


    /**
     * Renvoi l'entête des scripts PHP générés pour gen_plus
     * @return string
     */
    function def_php_script_header() {
        // Si l'attribut n'est pas défini alors on le définit
        if ($this->_php_script_header == null) {
            $this->_php_script_header = "<?php\n//\$Id\$ \n//gen_plus openMairie le ".date('d/m/Y H:i')."\n";
        }
        // On renvoi l'attribut
        return $this->_php_script_header;
    }


	// regeneration à chaque demande et non seulement quand la base est modifiée
    function stream_slightly_equals_file($stream, $path_to_file) {
        return false;
    }


	// changement de l'ordre d'affichage des champs suivant bloc/position d'om_forms : 
    function def_obj_meth_get_var_sql_forminc__champs() {
        $template = '
    /**
     *
     * @return array
     */
    function get_var_sql_forminc__champs() {
        return array(%s
        );
    }
';
        // clé primaire
        $array_content = $this->primary_key.",";
        // champs om_form
        // cas ou tableau vide ***
        
        foreach ($this->om_forms as $elem) {
			if($elem['type']=="sql")
				$name="'' as ".$elem['name'];
			else
				$name = $elem['name'];
			
            $array_content .= sprintf(
                '
            "%s",',
                $name
            );
        }
        return sprintf(
            $template,
            $array_content
        );
    }


	// ajout des types om_forms
	
    function def_obj_meth_settype_by_maj($maj) {
        // Niveau d'arborescence 0
        $template_settype_0 = '
            $form->setType("%s", "%s");';
        // Niveau d'arborescence 1
        $template_settype_1 = '
                $form->setType("%s", "%s");';
        // Niveau d'arborescence 2
        $template_settype_2 = '
                    $form->setType("%s", "%s");';
        //
        $template_settype_widgets_specifiques = '
            if ($this->retourformulaire == "") {%s
            } else {%s
            }';
        //
        $template_settype_multi = '
            if ($_SESSION["niveau"] == 2) {%s
            } else {%s
            }';
        $template_settype_date_om_validite = '
            if ($this->f->isAccredited(array($this->table."_modifier_validite", $this->table, ))) {%s
            } else {%s
            }';
        //
        $template_settype_retourformulaire = '
            if ($this->is_in_context_of_foreign_key("%s", $this->retourformulaire)) {%s
            } else {%s
            }';
        //
        $template_settype_retourformulaire_multi = '
            if ($this->is_in_context_of_foreign_key("om_collectivite", $this->retourformulaire)) {
                if($_SESSION["niveau"] == 2) {%s
                } else {%s
                }
            } else {
                if($_SESSION["niveau"] == 2) {%s
                } else {%s
                }
            }';
        //
        $tableobj = "";
        //
        foreach($this->info as $elem) {
			
			if($this->om_forms[$elem['name']]['type'] 
				or $this->om_forms[$elem['name']]['type'] !='' )
				$type=$this->om_forms[$elem['name']]['type'];
			else
				$type='';
			
			          
            // Gestion de la clé primaire
            if ($elem['name'] == $this->primary_key) {
                if ($maj == 0) {
                    $tableobj .= sprintf($template_settype_0, $elem['name'], ($this->typecle == "N" ? "hidden" : "text"));
                } elseif ($maj == 1) {
                    $tableobj .= sprintf($template_settype_0, $elem['name'], "hiddenstatic");
                } elseif ($maj == 2) {
                    $tableobj .= sprintf($template_settype_0, $elem['name'], "hiddenstatic");
                } elseif ($maj == 3) {
                    $tableobj .= sprintf($template_settype_0, $elem['name'], "static");
                }
                // On passe à l'itération suivante de la boucle
                continue;
            }

            // Gestion des clés secondaires
            // XXX Ce ne sont pas les bonnes clés secondaires voir foreign_keys
            if (!empty($this->clesecondaire)) {
                //
                if (in_array($elem['name'], $this->clesecondaire)) {
                    //
                    $elem1 = $elem['name'];
                    //
                    if ($elem['name'] == "om_collectivite") {
                        //
                        if ($maj == 0 || $maj == 1) {
                            $tableobj .= sprintf(
                                $template_settype_retourformulaire_multi,
                                sprintf($template_settype_2, $elem['name'], "selecthiddenstatic"),
                                sprintf($template_settype_2, $elem['name'], "hidden"),
                                sprintf($template_settype_2, $elem['name'], "select"),
                                sprintf($template_settype_2, $elem['name'], "hidden")
                            );
                        } elseif ($maj == 2) {
                            $tableobj .= sprintf(
                                $template_settype_multi,
                                sprintf($template_settype_1, $elem['name'], "selectstatic"),
                                sprintf($template_settype_1, $elem['name'], "hidden")
                            );
                        } elseif ($maj == 3) {
                            $tableobj .= sprintf(
                                $template_settype_retourformulaire_multi,
                                sprintf($template_settype_2, $elem['name'], "selectstatic"),
                                sprintf($template_settype_2, $elem['name'], "hidden"),
                                sprintf($template_settype_2, $elem['name'], "selectstatic"),
                                sprintf($template_settype_2, $elem['name'], "hidden")
                            );
                        }
                    } else {
                        if ($maj == 0 || $maj == 1) {
                            $tableobj .= sprintf(
                                $template_settype_retourformulaire,
                                $this->foreign_tables[$elem1]['foreign_table_name'],
                                sprintf($template_settype_1, $elem['name'], "selecthiddenstatic"),
                                sprintf($template_settype_1, $elem['name'], "select")
                            );
                        } elseif ($maj == 2) {
                            $tableobj .= sprintf($template_settype_0, $elem['name'], "selectstatic");
                        } elseif ($maj == 3) {
                            $tableobj .= sprintf($template_settype_0, $elem['name'], "selectstatic");
                        }
                    }
                    // On passe à l'itération suivante de la boucle
                    continue;
                }
            }

            //
            switch ($elem['type']) {
                case "date" :
					// cas type = hidden ***
					if($type=='hidden'){ // ***
						if ($maj == 3) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "datestatic");
						} elseif ($maj == 0 || $maj == 1) {	
							$tableobj .= sprintf($template_settype_0, $elem['name'], "hidden");	
						}				
					}else{ // ***
						if ($maj == 3) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "datestatic");
						} elseif ($maj == 0 || $maj == 1) {
							//
							if ($elem['name'] == 'om_validite_debut' || $elem['name'] == 'om_validite_fin') {
								$tableobj .= sprintf(
									$template_settype_date_om_validite,
									sprintf($template_settype_1, $elem['name'], "date"),
									sprintf($template_settype_1, $elem['name'], "hiddenstaticdate")
								);
							} else {
								//$tableobj .= sprintf($template_settype_0, $elem['name'], "date");
								if($this->om_forms[$elem['name']]['calcul']=='')//***
									$tableobj .= sprintf($template_settype_0, $elem['name'], "date");
								else
									$tableobj .= sprintf($template_settype_0, $elem['name'], "hidden");
							}
						} elseif ($maj == 2) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "hiddenstatic");
						}
					} // ***
                    
                    
                      
                    // On sort du switch
                    break;
                case "blob" :
					// cas type = hidden *** sql
					if($type=='hidden'){ // ***
						if ($maj == 3) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "datestatic");
						} elseif ($maj == 0 || $maj == 1) {	
							$tableobj .= sprintf($template_settype_0, $elem['name'], "hidden");	
						}				
					}else{ // ***
						if ($maj == 0 || $maj == 1) {
							if (strpos($elem['name'], '_om_htmletatex') !== false) {
								$tableobj .= sprintf($template_settype_0, $elem['name'], "htmlEtatEx");
							} elseif (strpos($elem['name'], '_om_htmletat') !== false) {
								$tableobj .= sprintf($template_settype_0, $elem['name'], "htmlEtat");
							} elseif (strpos($elem['name'], '_om_html') !== false) {
								$tableobj .= sprintf($template_settype_0, $elem['name'], "html");
							} elseif (strpos($type, 'html') !== false) {							//*** type
								$tableobj .= sprintf($template_settype_0, $elem['name'], "html");
							} else {
								$tableobj .= sprintf($template_settype_0, $elem['name'], "textarea");
							}
						} elseif ($maj == 3) {
							if (strpos($elem['name'], '_html') !== false) {
								$tableobj .= sprintf($template_settype_0, $elem['name'], "htmlstatic");
							} else {
								$tableobj .= sprintf($template_settype_0, $elem['name'], "textareastatic");
							}
						} elseif ($maj == 2) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "hiddenstatic");
						}
					}
                    // On sort du switch
                    break;
                case "geom" :
					// cas type = hidden ***
					if($type=='hidden'){ // ***
						if ($maj == 3) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "datestatic");
						} elseif ($maj == 0 || $maj == 1) {	
							$tableobj .= sprintf($template_settype_0, $elem['name'], "hidden");	
						}				
					}else{ // ***
						if ($maj == 0 || $maj == 1 || $maj == 2 || $maj == 3) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "geom");
						}
					}
                    // On sort du switch
                    break;
                case "bool" :
					// cas type = hidden ***
					if($type=='hidden'){ // ***
						if ($maj == 3) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "datestatic");
						} elseif ($maj == 0 || $maj == 1) {	
							$tableobj .= sprintf($template_settype_0, $elem['name'], "hidden");	
						}				
					}else{ // ***
						if ($maj == 0 || $maj == 1) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "checkbox");
						} elseif ($maj == 3) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "checkboxstatic");
						} elseif ($maj == 2) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "hiddenstatic");
						}
					}
                    // On sort du switch
                    break;
                default :
					// cas type = hidden ***
					if($type=='hidden'){ // ***
						if ($maj == 3) {
							$tableobj .= sprintf($template_settype_0, $elem['name'], "datestatic");
						} elseif ($maj == 0 || $maj == 1) {	
							$tableobj .= sprintf($template_settype_0, $elem['name'], "hidden");	
						}				
					}else{ // ***
						// Gestion des fichiers
						if (in_array($elem["name"], $this->_om_file_fields) === true
							or $type=="file") {  // *** $type
							if ($maj == 0) {
								$tableobj .= sprintf(
									$template_settype_widgets_specifiques,
									sprintf($template_settype_1, $elem['name'], "upload"),
									sprintf($template_settype_1, $elem['name'], "upload2")
								);
							} elseif ($maj == 1) {
								$tableobj .= sprintf(
									$template_settype_widgets_specifiques,
									sprintf($template_settype_1, $elem['name'], "upload"),
									sprintf($template_settype_1, $elem['name'], "upload2")
								);
							} elseif ($maj == 2) {
								$tableobj .= sprintf($template_settype_0, $elem['name'], "filestatic");
							} elseif ($maj == 3) {
								$tableobj .= sprintf($template_settype_0, $elem['name'], "file");
							}
						} else {
							// Cas standards
							if ($maj == 0) {
								if($this->om_forms[$elem['name']]['calcul']=='')//***
									$tableobj .= sprintf($template_settype_0, $elem['name'], "text");
								else
									$tableobj .= sprintf($template_settype_0, $elem['name'], "hidden");
							} elseif ($maj == 1) {
								if($this->om_forms[$elem['name']]['calcul']=='')//***
									$tableobj .= sprintf($template_settype_0, $elem['name'], "text");
								else
									$tableobj .= sprintf($template_settype_0, $elem['name'], "hidden");
							} elseif ($maj == 2) {
								$tableobj .= sprintf($template_settype_0, $elem['name'], "hiddenstatic");
							} elseif ($maj == 3) {								
								$tableobj .= sprintf($template_settype_0, $elem['name'], "static");
							}
							
						}
					}	
            } // switch

        }
        // sql
        foreach($this->om_forms as $elem){
			if($elem['type']== "sql")
				$tableobj .= sprintf($template_settype_0, $elem['name'], "hidden");
		}
        //
        return $tableobj;
    }

	// paramétrage du moteur de recherche
	// retour code moteur de recherche pour table_sql_inc_gen
    function def_moteur_recherche_inc() {
		// initialisation de variable
		$moteur = "";
		// initialisation du tableau options
		$moteur .= "\nif (!isset(\$options)) {";
		$moteur .= "\n\t\$options = array();";
		$moteur .= "\n}";
		// initialisation champs -> suivant ordre des champs dans le formulaire		
		foreach($this->om_forms as $elem){
			if($elem['liste']==1){ // uniquement dans la liste affichée
				if($elem['data_type']=="character varying"){ // text
					$moteur .= "\n\n\$champs['".$elem['name']."'] = array("; 
					$moteur .= "\n\t'table' => '".$this->table."',";
					$moteur .= "\n\t'colonne' => array('".$elem['name']."'),";
					$moteur .= "\n\t'type' => 'text',";
					$moteur .= "\n\t'taille' => 15,";
					$moteur .= "\n\t'libelle' => _('".$elem['libelle']."'));";
				}
				if($elem['data_type']=="date"){ // date
					// cas date
					$moteur .= "\n\n\$champs['".$elem['name']."'] = array("; 
					$moteur .= "\n\t'table' => '".$this->table."',";
					$moteur .= "\n\t'colonne' => '".$elem['name']."',";
					//$moteur .= "\n\t'libelle' => _('".$elem['libelle']."'),";
					$moteur .= "\n\t'type' => 'date',";
					$moteur .= "\n\t'taille' => 8,";
					$moteur .= "\n\t'lib1' => _('".$elem['libelle']." de'),";
					$moteur .= "\n\t'lib2' => _('à'),";
					$moteur .= "\n\t'where' => 'intervaldate',);";
				}
				if (!empty($this->clesecondaire) && in_array($elem["name"], $this->clesecondaire)) {
					// cas clé secondaire -> cas ou nom de champs = nom de la table
					$moteur .= "\n\n\$champs['".$elem['name']."'] = array("; 
					$moteur .= "\n\t'table' => '".$elem['name']."',";
					$moteur .= "\n\t'colonne' => '".$elem['name']."',";
					$moteur .= "\n\t'libelle' => _('".$elem['libelle']."'),";
					$moteur .= "\n\t'type' => 'select',);";					
				}
				if($elem['data_type']=="boolean"){ // boolean
					$moteur .= "\n\n\$args=array();";
					$moteur .= "\n\$args[0]=array(true,false);";
					$moteur .= "\n\$args[1]=array(_('Oui'), _('Tous'));";
					$moteur .= "\n\$champs['".$elem['name']."'] = array(";
					$moteur .= "\n\t'table' => '".$this->table."',";
					$moteur .= "\n\t'colonne' => '".$elem['name']."',";
					$moteur .= "\n\t'libelle' => _('".$elem['libelle']."'),";
					$moteur .= "\n\t'type' => 'select',";
					$moteur .= "\n\t'subtype' => 'manualselect',";
					$moteur .= "\n\t'args' => \$args,);";
				}
			}	
		}
		// parametrage du tableau options
		$moteur .= "\n\n\$options[]=array(";
		$moteur .= "\n\t'type' => 'search',";
		$moteur .= "\n\t'display' => true,";
		$moteur .= "\n\t'advanced' => \$champs,";
		$moteur .= "\n\t'absolute_object' => '".$this->table."',";
		$moteur .= "\n\t'export' => array(\"csv\")";
		$moteur .= "\n);";
		// retour code
		return $moteur;
	}
	
	// construit le contenu du script [gen/sql/<OM_DB_PHPTYPE>/<TABLE>.inc.php]
	// reprise compléte de la méthode suivant om_forms
	// return string.
	
    function table_sql_inc_gen($dyn = null) {
        // Initialisation des variables de travail
        $champaffiche = "";    // tableau des noms des champs affiches dans table.inc
        $champnonaffiche = ""; // tableau des noms des champs non affiches dans table.inc
        $champrecherche = "";  // tableau des noms des champs de recherche dans table.inc
        $tri = "";
        $libelle_t = '';
        $libelle_c = '';
        $alias = '';
        //Tableau avec nom de table comme cle et nombre d'occurence comme valeurs
        $fkLinkedTable=array();
        foreach ($this->foreign_tables as $value) {
            $fkLinkedTable[]=$value['foreign_table_name'];
        }
        $countLinkedTable=array_count_values($fkLinkedTable);
        // recuperation du libelle
        $libelle = $this->get_libelle_of($this->table);
        // si la colonne du libelle est une cle etrangere, on utilise le libelle de la table etrangere pour le trier
        if (in_array($libelle, $this->clesecondaire)) {
            if ($countLinkedTable[$this->foreign_tables[$libelle]['foreign_table_name']] > 1) {
                $alias = $this->foreign_tables[$libelle]['foreign_table_name'].
                            array_search($libelle, $this->clesecondaire);
            } else {
                $alias = $this->foreign_tables[$libelle]['foreign_table_name'];
            }
            $libelle_t = $this->foreign_tables[$libelle]['foreign_table_name'];
            $libelle_c = $this->get_libelle_of($libelle_t);
        } else {
            // sinon on affiche la colonne telle quelle
            $alias = $this->table;
            $libelle_t = $this->table;
            $libelle_c = $libelle;
        }
        // creation de la clause ORDER BY
        $tri = 'ORDER BY';
        $tri .= ' ';
        // ordre croissant explicite
        $tri .= $alias.'.'.$libelle_c.' ASC';
        $tri .= ' ';
        $tri .= 'NULLS LAST';
        $serie = 15; // nombre d'enregistrement par page
        $edition=$this->table;
        // On ajoute la clé primaire dans le champaffiche et le champrecherche en première position
        $champaffiche.= "\n    '".$this->table.".".$this->primary_key." as \"'.__(\"".$this->primary_key."\").'\"',";
        $champrecherche.= "\n    '".$this->table.".".$this->primary_key." as \"'.__(\"".$this->primary_key."\").'\"',";
        foreach($this->om_forms_liste as $elem){
			// champ affiche
			if($elem["liste"]==1 and  $elem["data_type"]!='text' and $elem["type"]!='file'){ // sauf blob et upload		
				// initialisation
				$temp ='def_champaffichedate'.OM_DB_PHPTYPE;
				$champaffiche.="\n    ";
				// substitution du nom pgsql du champs par le libelle du champ si non vide ***
				if($elem["libelle_court"]!="")
					$libelle=$elem["libelle_court"];
				else
					$libelle=$elem["name"];
				if($elem["libelle"]!="")
					$libelle_recherche=$elem["libelle"];
				else
					$libelle_recherche=$elem["name"];
				// suivant data type  
				if($elem['data_type']== 'integer'){
					// gen plus -> clé secondaire numérique
					if (!empty($this->clesecondaire) && in_array($elem["name"], $this->clesecondaire)) {
						$ftable = $this->foreign_tables[$elem["name"]]['foreign_table_name'];
						// recuperation du libelle
						$flibelle = $this->get_libelle_of($ftable);
						if ($countLinkedTable[$this->foreign_tables[$elem["name"]]['foreign_table_name']] > 1) {
							$champaffiche.= "'$ftable".
								array_search($elem["name"], $this->clesecondaire).
								'.'.$flibelle;
						} else {
							$champaffiche.= "'".$ftable.".".$flibelle;
						}
					}else
						$champaffiche.= "'".$this->table.".".$elem["name"];
					$champaffiche.= ' ';
					$champaffiche.= "as \"'.__(\"".$libelle."\").'\"',";
				}
				if ($elem["data_type"] == "date") { // format date francaise
					$champaffiche .= $this->$temp($this->table.".".$elem["name"], $libelle).",";
				} 
				if($elem['data_type']== 'character varying'){
					$champaffiche.= "'".$this->table.".".$elem["name"];
					$champaffiche.= ' ';
					$champaffiche.= "as \"'.__(\"".$libelle."\").'\"',";
				}
				if($elem['data_type']== 'numeric'){ // voir formatage
					// formatage -> cast en numeric -> en 2 caractères : reste à droite en colonne et pas to_char
					// arrondi la somme au centiéme si millième supérieur à 5 : 0,055 = 0,06
					$champaffiche.= "'round(".$this->table.".".$elem["name"];
					$champaffiche.= ' ,2) '; // ::numeric
					$champaffiche.= " as \"'.__(\"".$libelle."\").'\"',";
				}
				if($elem['data_type']== 'boolean'){ // format oui/non
					$champaffiche.= "\"case ".$this->table.".".$elem["name"]." when 't' then 'Oui' else 'Non' end as \\\"\".__(\"".$libelle."\").\"\\\"\",";
				}
				// 'round(montant_ht::numeric*1.2321, 2) as "'.__("montant ttc").'"',
				if($elem['type']== 'sql'){ // 
					if($elem['calcul']!=''){
						$champaffiche.= "'round(".$elem['calcul'];
						$champaffiche.= ' ,2) ';
						$champaffiche.= "as \"'.__(\"".$libelle."\").'\"',";
					}else
						$champaffiche.= "' \'\' as \"'.__(\"".$libelle."\").'\"',";
				}
			}else{  
				// champs non affiche
                $champnonaffiche.="\n    ";
                $champnonaffiche.= "'".$this->table.".".$elem["name"]." as \"'.__(\"".$elem["name"]."\").'\"',";				
			}
            // les champs recherche ne prennent pas les dates seulement pour les champs affichés
            if (($elem["data_type"] == "character varying" || $elem["data_type"] == "integer" 
				|| $elem["data_type"] == "numeric") 
				&& $elem["liste"]==1) {
				$champrecherche.="\n    ";
				// Si le champ que nous sommes en train d'afficher est une cle secondaire
				if (!empty($this->clesecondaire) && in_array($elem["name"], $this->clesecondaire)) {
					$ftable = $this->foreign_tables[$elem["name"]]['foreign_table_name'];
					// recuperation du libelle
					$flibelle = $this->get_libelle_of($ftable);
					if ($countLinkedTable[$this->foreign_tables[$elem["name"]]['foreign_table_name']] > 1) {
						$champrecherche.= "'".$ftable.array_search($elem["name"], $this->clesecondaire).".".$flibelle." ";
					} else {
						$champrecherche.= "'".$ftable.".".$flibelle." ";
					}
				} else {
					 $champrecherche.="'".$this->table.".".$elem["name"]." ";
				}
				$champrecherche.= "as \"'.__(\"".$libelle_recherche."\").'\"',";
            }
		}		
        // creation de table.inc.php
        $tableinc= $this->def_php_script_header();
        $tableinc.="\n\$DEBUG=0;";
        $tableinc.="\n\$serie=".$serie.";";
        $tableinc.=$this->def_ent();
        $tableinc.="\nif(!isset(\$premier)) \$premier='';";
        $tableinc.="\nif(!isset(\$tricolsf)) \$tricolsf='';";
        $tableinc.="\nif(!isset(\$premiersf)) \$premiersf='';";
        $tableinc.="\nif(!isset(\$selection)) \$selection='';";
        $tableinc.="\nif(!isset(\$retourformulaire)) \$retourformulaire='';";
        $tableinc.="\nif (!isset(\$idxformulaire)) {\n    \$idxformulaire = '';\n}";
        $tableinc.="\nif (!isset(\$tricol)) {\n    \$tricol = '';\n}";
        $tableinc.="\nif (!isset(\$valide)) {\n    \$valide = '';\n}";
        // TABLE
        $tableinc .= "\n// FROM ";
        $tableinc .= "\n\$table = DB_PREFIXE.\"".$this->table;
        // cle secondaires -> construction de $table
        if (!empty($this->clesecondaire)) {
            foreach ($this->clesecondaire as $key => $elem) {
                if (isset($this->foreign_tables[$elem])) {
                    $tableinc .= "\n    LEFT JOIN \".DB_PREFIXE.\"".$this->foreign_tables[$elem]["foreign_table_name"]." ";
                    $ftable=$this->foreign_tables[$elem]['foreign_table_name'];
                    if ($countLinkedTable[$ftable] > 1) {
                        $tableinc .= "as $ftable".$key." ";
                    }
                    $tableinc .= "\n        ON ".$this->table.".".$elem."=";
                    if ($countLinkedTable[$ftable] > 1) {
                        $tableinc .= "$ftable".$key;
                    } else {
                        $tableinc .=  $ftable;
                    }
                    $tableinc .= ".".$this->foreign_tables[$elem]["foreign_column_name"]." ";
                } else {
                    //
                    $tableinc .= "\n    LEFT JOIN \".DB_PREFIXE.\"".$elem." as a".$key." ";
                    $tableinc .= "\n        ON ".$this->table.".".$elem."=a".$key.".".$elem." ";
                }
            }
		}                
        $tableinc.= "\";";
        // CHAMP AFFICHE RECHERCHE Tri Edition 
        $tableinc .= "\n// SELECT ";
        $tableinc.="\n\$champAffiche = array(".$champaffiche."\n    );";
        $tableinc.="\n//\n\$champNonAffiche = array(".$champnonaffiche."\n    );";
        $tableinc.="\n//\n\$champRecherche = array(".$champrecherche."\n    );";
        $tableinc.="\n\$tri=\"".$tri."\";";
        $tableinc.="\n\$edition=\"".$edition."\";";
        // selection, sous formulaires et moteur
        $tableinc.=$this->def_selection_inc();
        $tableinc.=$this->def_sousformulaire_inc();
        if($this->om_tables_parametre['recherche']==1)
			$tableinc.=$this->def_moteur_recherche_inc();
        $tableinc.="\n";
        return $tableinc;
    }


}
