<?php
//$Id$ 
//gen openMairie le 29/12/2023 18:02

require_once "../gen/obj/om_forms.class.php";

class om_forms extends om_forms_gen {


	var $tab; // tableau à insérer en json
	var $cle_secondaire; // variable globale clé secondaire
	var $type_sql; // variable globale type_sql



   function init_class_actions() {
        parent::init_class_actions();
        
        // ACTION - 004 - composer form
        //
        $this->class_actions[4] = array(
            "identifier" => "composer",
            "view" => "view_composer",
            "permission_suffix" => "ajouter",
        );
        
        // ACTION - 005 - composer form
        //
        $this->class_actions[5] = array(
            "identifier" => "composer-widget-ctl",
            "view" => "view_composer_widget_ctl",
            "permission_suffix" => "ajouter",
        );
		/*
        // ACTION - 006 - composer liste
        //
        $this->class_actions[6] = array(
            "identifier" => "composer_liste",
            "view" => "view_composer_liste",
            "permission_suffix" => "ajouter",
        );
        
        // ACTION - 007 - composer liste
        //
        $this->class_actions[7] = array(
            "identifier" => "composer-widget-ctl_liste",
            "view" => "view_composer_widget_ctl_liste",
            "permission_suffix" => "ajouter",
        );
		*/


	}

	// =======================
	// Ergonomie du formulaire
	// =======================

    function afterSousFormSpecificContent() {
		// initialiser les affichages des champs avec change_form_om_forms de script.js
		// pb de cle secondaire qui est integer		
		echo '<script type="text/javascript">change_form_om_forms($("select#data_type").val());</script>';
	} 


	
    function get_var_sql_forminc__champs() {
        return array(           
            "'' as data_type",
            "column_name", // clé ne peut pas être mis dans une autre position sauf si exists return true
            "'' as tables", // pour clé secondaire
            "table_name",
            "'' as table_schema",
            "'' as character_maximum_length",
            "'' as is_nullable",
            "'' as column_default",
            "'' as comment",
            "parametres",
            "parametres->>'libelle' as libelle",
            "parametres->>'libelle_court' as libelle_court",
            "parametres->>'type' as type",
            "parametres->>'bloc' as bloc",
            "parametres->>'position' as position",
            "parametres->>'liste' as liste",
            "parametres->>'position_liste' as position_liste",
            "parametres->>'calcul' as calcul",           
        );
    }

    function exists() {
        //if (!isset($this->val[0]) || $this->val[0] == "") {
        //    return false;
        //}
        return true;
    }



    function get_default_libelle() {
        return $this->getVal($this->clePrimaire);
    }



    function set_form_default_values(&$form, $maj, $validation) {
		parent::set_form_default_values($form, $maj, $validation);
		if($maj==0){
			$form->setVal('liste', 1);
		}
		If($maj>0){			
			// clé secondaire
			$temp = explode(".", $this->getParameter("idx"));
			$temp = $temp[0]."_".$temp[1]."_fkey";
			$sql  = "SELECT count(constraint_name) FROM information_schema.table_constraints ";
			$sql .= " where constraint_name = '".$temp."'";			
		    $res = $this->f->db->getOne($sql);
			$this->f->addToLog("obj/om_champs_class.php: db->getOne(\"".$sql."\");", DEBUG_MODE);
			$this->f->isDatabaseError($res);
			if($res==1){		
				$form->setVal('data_type',"cle_secondaire");
				$form->setVal('tables',$temp[0]);
				$this->cle_secondaire='oui';
			}else{
				$this->cle_secondaire='non';
				$form->setType("type", "select");
			}
			// sql
			$sql  = "SELECT parametres->>'type' as type FROM ".DB_PREFIXE."om_forms ";
			$sql .= " where column_name = '".$this->getParameter("idx")."'";
		    $res = $this->f->db->getOne($sql);
			$this->f->addToLog("obj/om_champs_class.php: db->getOne(\"".$sql."\");", DEBUG_MODE);
			$this->f->isDatabaseError($res);
			if($res=='sql')	{
				$form->setVal('data_type', $res);
				$this->type_sql='oui';
			}else
				$this->type_sql='non';
			// initialisation  des champs om_champs
			$sql  = "SELECT  table_schema, data_type, character_maximum_length  ,";
			$sql .= " is_nullable, column_default, comment";
			$sql .= " from ".DB_PREFIXE."om_champs ";
			$sql .= " where column_name = '".$this->getParameter("idx")."'";
			$this->f->addToLog("obj/om_champs_class.php: db->query(\"".$sql."\");", DEBUG_MODE);
			$res = $this->f->db->query($sql);
			$this->f->isDatabaseError($res);
			while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
				$form->setVal('table_schema', $row['table_schema']);
				$form->setVal('data_type', $row['data_type']);
				$form->setVal('character_maximum_length', $row['character_maximum_length']);
				$form->setVal('is_nullable', $row['is_nullable']);
				$form->setVal('column_default', $row['column_default']);
				$form->setVal('comment', $row['comment']);
			}	
		}
	}

    function setType(&$form,$maj) {
        parent::setType($form, $maj);       
        if($maj==0){
            $form->setType("table_schema", "hidden"); 
			$form->setType("data_type", "select"); 
            $form->setType("is_nullable", "select"); 
            $form->setType("bloc", "select");
            $form->setType("type", "select"); 
            $form->setType("libelle", "hidden"); // libelle = champs original 
            $form->setType("libelle_court", "hidden"); // libelle = champs original 
            $form->setType("tables", "select");  // cle secondaire 
            $form->setType("liste", "select");
        } 
        if($maj==1){
			$form->setType("data_type", "select"); 
            $form->setType("is_nullable", "select"); 			  
			$form->setType("table_schema", "hidden"); 
			$form->setType("bloc", "select");
			$form->setType("type", "select");
			$form->setType("liste", "select");
			if ($this->cle_secondaire == 'oui'){
				$form->setType("data_type", "hiddenstatic");
			}
			if($this->type_sql == 'oui'){
				$form->setType("data_type", "hiddenstatic");
				$form->setType("type", "hiddenstatic");
			}
			$form->setType("tables", "hidden");
			//$form->setType("tables", "select");  // cle secondaire 
		}	
		if ($maj == 2) {
            $form->setType('bloc', 'selectstatic');
            $form->setType('type', 'selectstatic');
            $form->setType('is_nullable', 'selectstatic');
            $form->setType('data_type', 'selectstatic');
            $form->setType("tables", "hidden");
        }   
        
        $form->setType("comment", "hidden"); // documentation enligne
        $form->setType("parametres", "hidden"); // documentation enligne
        $form->setType("bloc", "hidden"); // utiliser composition
        $form->setType("position", "hidden");// utiliser composition  
        $form->setType("character_maximum_length", "hidden"); // ne sert pas  
        //$form->setType("table_name", "hidden");  // clé secondaire -> formulaire
        $form->setType("column_default", "hidden"); // ne sert pas, si champs hidden renvoie un carctère vide et non un null ?
		$form->setType("position_liste", "hidden");// utiliser composition  
    }
	
    function setLib(&$form,$maj) {
        parent :: setLib($form,$maj);
		$form->setLib('table_name',_("Nom de la table"));
		$form->setLib('column_name',_("Nom du champ"));
		$form->setLib('default_value',_("Valeur par défaut"));
		$form->setLib('data_type',_("Type de champs"));
		$form->setLib('is_nullable',_("Obligatoire"));
		$form->setLib('type',_("Type de champs OM"));
		$form->setLib('position',_("Position dans le bloc"));
		$form->setLib('tables',_("Clé secondaire de la table"));
		$form->setLib('liste',_("Affichage en liste"));
		$form->setLib('position_liste',_("position dans la liste"));
		$form->setLib('libelle_court',_("Affichage libellé liste"));
	}

    function setMax(&$form, $maj) {
		parent::setMax($form, $maj);
		if($maj==0)
			$form->setMax("column_name", 60); // sans la table
		else
			$form->setMax("column_name", 120); // 40 de table + 60 de champs + point 
    }


    function setSelect(&$form, $maj, &$dnu1 = NULL, $dnu2 = NULL) {
        parent :: setSelect($form, $maj, $dnu1, $dnu2);
        if($maj<2){
            // accepte les nuls
            $contenu=array();
            $contenu[0]=array('YES','NO');
            $contenu[1]=array('Non obligatoire','Obligatoire');
            $form->setSelect("is_nullable",$contenu);
            // data type 
            $contenu=array();
            $contenu[0]=array('character varying','integer','numeric','boolean',
								'cle_secondaire','text','date', 'sql');
            $contenu[1]=array(_('character varying'),_('entier'),_('décimal'),
								_('booléen'),_('clé secondaire'),_('texte'),_('Date'),_('virtuel sql'));
            $form->setSelect("data_type",$contenu);
            // bloc paramétré en om_tables_parametre -> ne fonctionne qu en sous form ...
            
			$sql = " select  ";
			$sql .=" parametres ";
			$sql .=" FROM ".DB_PREFIXE."om_tables_parametre ";
			$sql .=" where table_name = '".$this->getParameter("idxformulaire")."' ";
			$res = $this->f->db->getOne($sql);
			$this->f->addToLog("app/om_tables_class.php: db->query(\"".$sql."\");", VERBOSE_MODE);
			$this->f->isDatabaseError($res);
			$col=json_decode($res);
			// bloc
            $contenu=array();
            $contenu[0]=array('C1','C2','C3');
            if($col->col1=="")
				$contenu[1]=array('colonne 1','colonne 2','colonne 3');
            else
				$contenu[1]=array($col->col1,$col->col2,$col->col3);
            $form->setSelect("bloc",$contenu);
            // type
            $contenu=array();
			$contenu[0]=array('','hidden','file','html','sql');
			$contenu[1]=array('visible','non visible','fichier','editeur html','virtuel sql');			
            $form->setSelect("type",$contenu); 
            
           	// liste tables pour clé secondaire						
			$sql = " SELECT  om_tables.table_name, om_tables.table_name as lib FROM ";
			$sql .= DB_PREFIXE."om_tables ";
			$sql .= " where substring(table_name,1,3) not like 'om_' ";
			$sql .= " and table_name not like '".$this->getParameter("idxformulaire")."' ";
			$sql .= " ORDER BY om_tables.table_name";
			$res = $this->f->db->query($sql);
			$this->addToLog(__METHOD__."(): db->query(\"".$sql."\");", VERBOSE_MODE);
			$this->f->isDatabaseError($res);
			//
			$contenu = array(array(""), array(__("choisir une table")));
			while ($row =& $res->fetchrow()) {
				$contenu[0][] = $row[0];
				$contenu[1][] = $row[1];
			}
			$form->setSelect("tables", $contenu);	
			// liste
            $contenu=array();
            $contenu[0]=array(1,0);
            $contenu[1]=array('Oui','Non');
            $form->setSelect("liste",$contenu);		        
		}
	}

    function setOnchange(&$form, $maj) {
		//javascript controle client
        $form->setOnchange('position','VerifNum(this)');
        $form->setOnchange('data_type','change_form_om_forms(this.value)'); // a voir ***
    }

	// ====================
	// vérifier les saisies
	// ====================

    function verifier($val = array(), &$dnu1 = null, $dnu2 = null) {
		parent::verifier($val, $dnu1, $dnu2);
		if($this->getParameter('maj')==0){ //vérification du nom de champ
			// la clé ne doit pas être vide 	
			if ($this->valF['column_name'] == $this->valF['table_name'].".") {
				$this->correct = false;
				$this->addToMessage( __("Le nom du champs est obligatoire")."&nbsp;"."[&nbsp;".__($this->clePrimaire)."&nbsp;]");
			}		 
			// le nom du champs ne doit pas exieter dans la table
			$sql = "select count(*) from ".DB_PREFIXE."om_forms ";
			$sql .= "where column_name ='".$this->valF[$this->clePrimaire]."' ";
			// Exécution de la requête
			$nb = $this->f->db->getone($sql);
			// Logger
			$this->addToLog(__METHOD__."(): db->getone(\"".$sql."\");", VERBOSE_MODE);
			// Vérification d'une éventuelle erreur de base de données
			$this->f->isDatabaseError($nb);
			if ($nb > 0) {
				$this->correct = false;
				$this->addToMessage( $this->valF[$this->clePrimaire]." ". __("est déjà utilisé dans la table")." ".$this->valF['table_name']."");
			}

			// le nom du champs ne doit pas être le nom de la table (la clé primaire n'est pas dans om_forms)
			if ($this->valF['column_name'] ==$this->valF['table_name'].".".$this->valF['table_name']) {
				$this->correct = false;
				$this->addToMessage( $this->valF[$this->clePrimaire]." ". __("est la clé primaire de la table")." ".$this->valF['table_name']."");
			}
		}
		
		// =============================
		// Contrôle des types openMairie
		// =============================
		if($this->getParameter('maj')<2){
			// si data_type=text alors html est possible
			if($val['type']=="html" and $val['data_type']!="text"){
				$this->correct = false;
				$this->addToMessage( __("type html impossible pour champ non texte"));
			}
			// si data_type = character varying alors upload est possible
			if($val['type']=="file" and $val['data_type']!="character varying"){
				$this->correct = false;
				$this->addToMessage( __("type upload impossible pour champ non texte"));
			}		
			// si un champ hidden ne doit pas être obligatoire 
			if($val['type']=="hidden" and $val['is_nullable']=="NO"){
				$this->correct = false;
				$this->addToMessage( __("type non visible impossible pour champ obligatoire"));
			}
			
		}	
	}

	// possibilité de modifier calcul
	function verifier_calcul($calcul) {
		// pb de la guillemet pour date('Y-m-d') en champs date
		if($calcul!=""){
			$calcul=str_replace("'","''",$calcul);
			$this->f->addToLog("obj/om_champs_class.php: db->getOne(\"".$calcul."\");", DEBUG_MODE);
		}		
		// verification que les champs existent dans la table en cours
		if($this->valF['data_type'] == "numeric" or $this->valF['data_type'] == "integer"
			or  $this->valF['data_type']=='sql'){		
			$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;
			$sql = " SELECT column_name";
			$sql .= " FROM ".DB_PREFIXE."om_champs ";
			$sql .= " where om_champs.table_name = '".$this->valF["table_name"]."' "; 
			$res = $this->f->db->query($sql);
			$this->f->addToLog("app/om_champs_class.php: db->query(\"".$sql." ".$widget_id."\");", DEBUG_MODE);
			$this->f->isDatabaseError($res);
			$champs=array();
			while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
					array_push($champs,$row['column_name']);
			}
			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{
							// vérification de l'existence des champs dans la table en cours	
							$ok=0;
							foreach($champs as $champ){
								$temp=explode(".",$champ); 
								if($el==$temp[1] and $ok==0){									
									$ok=1;							
								}	
							}	
							if($ok==1)
								$ch .= $el;
							else{
								$ch .= $el;
								$this->addToMessage($el." "._("est un champs inexistant en table")." ".$this->valF["table_name"]);
								$this->correct=false;
							}
						}
			}
			$calcul=$ch;		
		}		
		return $calcul;	
	}

	// possibilité de modifier libelle
	function verifier_libelle($libelle) {
		if($libelle!=""){
			$libelle=str_replace("'","''",$libelle);
			$this->f->addToLog("obj/om_champs_class.php: db->getOne(\"".$calcul."\");", DEBUG_MODE);
		}
		return $libelle;	
	}


	function setvalF($val = array()) {
        //affectation valeur formulaire
        parent::setvalF($val);
        if($this->getParameter('maj')==0){
			// data_type : cle_secondaire -> integer
			if ($val['data_type'] == 'cle_secondaire'){
				$val['column_name'] = $val['tables'];
				$this->valF['column_name'] = $val['tables'];
				$this->addToMessage("nom de champs = ".$val['column_name']);
			}					
			// traitement du nom de champs (column_name)
			// le libellé prend la valeur du nom de champs origine
			// la sequence \ (echappement) n est pas traitée
			// caractères spéciaux
			$temp = str_replace( array( '%', '@', '\'', ';', '<', '>','@','[',']',' '), '_', $val['column_name']);
			$temp = str_replace( array( '#', '{', '}','&', '$' , '£', 'µ','*','|','?',':'), '_', $temp);
			// accents
			$temp = str_replace(array( 'é', 'è', 'ê'), 'e',$temp);
			$temp = str_replace(array( 'ô'), 'o',$temp);  
			$temp = str_replace(array( 'ù'), 'u',$temp);
			$temp = str_replace(array( 'à'), 'a',$temp);
			$temp = str_replace(array( 'ç'), 'c',$temp);   
			$temp = strtolower($temp);
			// 1er caractère ne doit pas être un chiffre -> remplacer par "_"
			if(is_numeric(substr($temp,0,1))){
				$temp="_".(substr($temp,1,strlen($temp)-1));				
			}
			// On remet la clé primaire en forme table.champs
			// en retour "insert" mettre en forme la clé primaire car sinon erreur suivante :
			// class om_champs - dbform::is_action_condition_satisfied(): return false;
			// en effet la clé primaire doit exister pour satisfaire la condition exist()			
			$this->valF['column_name'] = $val['table_name'].".".$temp;
			$this->tab->libelle=$val['column_name'];
			$this->tab->libelle_court=$val['column_name'];
		   // recherche du max position pour bloc = C1 en ajout
			$sql = " SELECT ";
			$sql .= " max(parametres->>'position') ";
			$sql .= " FROM ".DB_PREFIXE."om_forms ";
			$sql .= " where SPLIT_PART(om_forms.column_name, '.', 1) = '".$this->valF["table_name"]."' "; // table_name ***
			$sql .= " and  parametres->>'bloc' = 'C1'";
			$res = $this->f->db->getOne($sql);
			$this->f->addToLog("obj/om_forms_class.php - setvalF: db->getOne(\"".$sql."\");", DEBUG_MODE);
			$this->f->isDatabaseError($res);
			$position=intval($res);				
			$position++;
			$this->tab->bloc = 'C1';
			$this->tab->position = $position;
			// recherche position max pour liste
			$sql = " SELECT ";
			$sql .= " max(parametres->>'position') ";
			$sql .= " FROM ".DB_PREFIXE."om_forms ";
			$sql .= " where SPLIT_PART(om_forms.column_name, '.', 1) = '".$this->valF["table_name"]."' "; // table_name ***
			$res = $this->f->db->getOne($sql);
			$this->f->addToLog("obj/om_forms_class.php - setvalF: db->getOne(\"".$sql."\");", DEBUG_MODE);
			$this->f->isDatabaseError($res);
			$position_liste=intval($res);				
			$position_liste++;
			$this->tab->position_liste = $position_liste;							        
		}
		// update
		// data_type : cle_secondaire -> integer
		// position et position_liste vide
		if($this->getParameter('maj')==1){
			if ($this->cle_secondaire == 'oui'){
				//echo "************************";
				$val['data_type'] = "integer";
				$this->addToMessage("data_type = integer");
			 }
			 if($val["liste"]==1 and $val['position_liste']==""){	
				$position_liste=0; 		
				// recherche position max pour liste si affichage liste remis à 1
				$sql = " SELECT ";
				$sql .= " max(parametres->>'position_liste') ";
				$sql .= " FROM ".DB_PREFIXE."om_forms ";
				$sql .= " where SPLIT_PART(om_forms.column_name, '.', 1) = '".$this->valF["table_name"]."' "; // table_name ***
				$sql .= " and column_name != '".$this->valF["column_name"]."' ";
				$res = $this->f->db->getOne($sql);
				$this->f->addToLog("obj/om_forms_class.php - setvalF: db->getOne(\"".$sql."\");", DEBUG_MODE);
				$this->f->isDatabaseError($res);
				$position_liste=intval($res);				
				$position_liste++;
				$this->tab->position_liste = $position_liste;	
				$this->addToMessage(_("affichage en liste position")." ".$this->tab->position_liste." ".$position_liste);			 
			 }else
				$this->tab->position_liste = $val['position_liste'];
			if($val['position']==""){										
			   // recherche du max position pour bloc = C1 en ajout
			    $position=0;
				$sql = " SELECT ";
				$sql .= " max(parametres->>'position') ";
				$sql .= " FROM ".DB_PREFIXE."om_forms ";
				$sql .= " where SPLIT_PART(om_forms.column_name, '.', 1) = '".$this->valF["table_name"]."' "; // table_name ***
				$sql .= " and  parametres->>'bloc' = 'C1'";
				$sql .= " and column_name != '".$this->valF["column_name"]."' ";
				$res1 = $this->f->db->getOne($sql);
				$this->f->addToLog("obj/om_forms_class.php - setvalF: db->getOne(\"".$sql."\");", DEBUG_MODE);
				$this->f->isDatabaseError($res1);
				$position=intval($res1);				
				$position++;
				$this->tab->bloc = 'C1';
				$this->tab->position = $position;
				$this->addToMessage(_("affichage en bloc C1 position")." ".$this->tab->position);
			}else{
				$this->tab->bloc = $val['bloc'];
				$this->tab->position = $val['position'];
			}
		}
	}

	// ajout = alter nom_table add nom_colonne type + om_forms
    function ajouter($val = array(), &$dnu1 = null, $dnu2 = null) {		
       // Begin
        $this->begin_treatment(__METHOD__);
        // Mutateur de valF
        $this->setValF($val);
        // verifier si existe des champs 
        $temp = explode(".", $this->valF['column_name']); // valeur du champ 
        // vérification ($this->setValFAjout($val); -> dans  verifier())
        $this->verifier($val, $this->f->db, null);
        $val['calcul']=$this->verifier_calcul($val['calcul']);
        $val['libelle']=$this->verifier_libelle($val['libelle']);
        // data_type -> type sql
        if($val['data_type']=='sql')
			$val['type']= 'sql';        
        if ($this->correct) {
			// type : sql 
			if($this->valF['type']!= 'sql'){				
				// cas cle secondaire
				if($this->valF['data_type']== 'cle_secondaire')
					$data_type = 'integer';
				else
					$data_type=$this->valF['data_type'];
				//$this->addToMessage($temp[1]);
				$sql  = "alter table ".DB_PREFIXE.$this->valF['table_name']." add column ";
				//$sql .=  $this->valF['column_name']." ".$data_type;
				$sql .=  $temp[1]." ".$data_type;
				if($this->valF['is_nullable']== 'NO')
					$sql .= " not null ";;
				// default a peu d'intérêt -> utiliser setval  car la mise a jour
				// se fait sur les enregistrements existants
				// et n'est pas pris en compte même si champs hidden
				if($this->valF['column_default'] != '')
					$sql .= " DEFAULT ".$this->valF['column_default'].""; // guillement à mettre ans le champs
				$res = $this->f->db->query($sql);   
				if ($this->f->isDatabaseError($res, true)) {
					// Appel de la methode de recuperation des erreurs
					$this->addToMessage($res->getDebugInfo());
					$this->erreur_db($res->getDebugInfo(), $res->getMessage(), '');
					$this->correct = false;
					//				
					// Return
					return $this->end_treatment(__METHOD__, false);
				}	
					$this->addToMessage("création du champ ".$this->valF['column_name']);
				// comment
				//$sql  = "comment on COLUMN ".DB_PREFIXE.$this->valF['table_name'].".".$temp[1]." IS ";				
			}		
			// om_forms/json
			$this->tab->type = $val['type'];
			$this->tab->calcul = $val['calcul']; 
			$this->tab->liste = $val['liste']; 
			$insert=json_encode($this->tab);
			$sql = "insert into ".DB_PREFIXE."om_forms ";
			$sql .= " VALUES ( '".$this->valF['column_name']."',";
			$sql .= "'".$insert."','".$this->valF['table_name']."')"; // ajout de table name
			$res = $this->f->db->query($sql);    
			if ($this->f->isDatabaseError($res, true)) {
				// Appel de la methode de recuperation des erreurs[1
				$this->addToMessage($res->getDebugInfo());
				$this->erreur_db($res->getDebugInfo(), $res->getMessage(), '');
				$this->correct = false;
				// Return
				return $this->end_treatment(__METHOD__, false);
			}else{
				$this->addToMessage("ajout des paramètres dans om_forms"." ".$this->valF['column_name']);
				if($this->triggermodifierapres($id, $this->f->db, $val, null) === false) {
					$this->correct = false;
					$this->addToLog(__METHOD__."(): ERROR", DEBUG_MODE);
					// Return
					return $this->end_treatment(__METHOD__, false);
				} 	
			} 
			//***			
			// Le premier parametre est vide car en MODE 'insert'
			// l'enregistrement n'existe pas encore donc il n'a pas
			// d'identifiant
			
			if($this->triggerajouterapres($this->valF[$this->clePrimaire], $this->f->db, $val, null) === false) {
				$this->correct = false;
				$this->addToLog(__METHOD__."(): ERROR", DEBUG_MODE);
				// Return
				return $this->end_treatment(__METHOD__, false);
			}                 
			 
		}else {
            // Message d'echec (saut d'une ligne supplementaire avant le
            // message pour qu'il soit mis en evidence)
            $this->addToMessage("<br/>".__("ajout non effectués")."<br/>");
            $message = __("champ non ajouté");
            $this->addToLog(__METHOD__."(): ".$message, VERBOSE_MODE);
            // Return
            return $this->end_treatment(__METHOD__, false);
        }	
       return $this->end_treatment(__METHOD__, true);    
    }

	// en modifier : alter table + om_forms
	function modifier($val = array(), &$dnu1 = null, $dnu2 = null) {
       // Begin
        $this->begin_treatment(__METHOD__);
        // Mutateur de valF
        $this->setValF($val);
        // verifier si existe des champs 
        $temp = explode(".", $this->valF['column_name']); // valeur du champ 
        // methode de verification champ lib
        $this->verifier($val, $this->f->db, null);
        $val['calcul']=$this->verifier_calcul($val['calcul']);
        $val['libelle']=$this->verifier_libelle($val['libelle']);
        // data_type -> type sql
        if($this->valF['data_type']=='sql')
			$this->valF['type']= 'sql';
        if ($this->correct) {
			// type : sql 
			if($this->valF['type']!= 'sql'){	
				// cas cle secondaire
				if($this->valF['data_type']== 'cle_secondaire')
					$data_type = 'integer';
				else
					$data_type=$this->valF['data_type'];
				// modifier
				$sql  = "alter table ".DB_PREFIXE.$this->valF['table_name']." alter column ";
				$sql .=  $temp[1]." type ".$data_type;
				$res = $this->f->db->query($sql);    
				if ($this->f->isDatabaseError($res, true)) {
					// Appel de la methode de recuperation des erreurs
					$this->addToMessage($res->getDebugInfo());
					$this->erreur_db($res->getDebugInfo(), $res->getMessage(), '');
					$this->correct = false;
					// Return
					return $this->end_treatment(__METHOD__, false);
				}
				$this->addToMessage("modification du champ ".$this->valF['column_name']);
				// champs null
				if($this->valF['is_nullable']== 'NO'){
					$sql  = "alter table ".DB_PREFIXE.$this->valF['table_name']." alter column ";
					$sql .=  $temp[1]." set not null ";
					$res = $this->f->db->query($sql);    
					if ($this->f->isDatabaseError($res, true)) {
						// Appel de la methode de recuperation des erreurs
						$this->addToMessage($res->getDebugInfo());
						$this->erreur_db($res->getDebugInfo(), $res->getMessage(), '');
						$this->correct = false;
						// Return
						return $this->end_treatment(__METHOD__, false);
					}else
						$this->addToMessage("modification du champ not null ".$this->valF['column_name']);
				}else{
					$sql  = "alter table ".DB_PREFIXE.$this->valF['table_name']." alter column ";
					$sql .=  $temp[1]." drop not null ";
					$res = $this->f->db->query($sql);    
					if ($this->f->isDatabaseError($res, true)) {
						// Appel de la methode de recuperation des erreurs
						$this->addToMessage($res->getDebugInfo());
						$this->erreur_db($res->getDebugInfo(), $res->getMessage(), '');
						$this->correct = false;
						// Return
						return $this->end_treatment(__METHOD__, false);
					}else
						$this->addToMessage("modification du champ null ".$this->valF['column_name']);
				}	
				 // column default	a peu d'intérêt -> utiliser setval  car la mise a jour
				 // se fait sur les enregistrements existants et n'est pas pris en compte même si champs hidden			 
				if($this->valF['column_default'] == ''){
					$sql  = "alter table ".DB_PREFIXE.$this->valF['table_name']." alter column ";
					$sql .=  $temp[1]." drop DEFAULT ";
					$res = $this->f->db->query($sql);    
					if ($this->f->isDatabaseError($res, true)) {
						// Appel de la methode de recuperation des erreurs
						$this->addToMessage($res->getDebugInfo());
						$this->erreur_db($res->getDebugInfo(), $res->getMessage(), '');
						$this->correct = false;
						// Return
						return $this->end_treatment(__METHOD__, false);
					}else
						$this->addToMessage("suppression de la valeur par default ".$this->valF['column_name']);						 
				}else{
					$sql  = "alter table ".DB_PREFIXE.$this->valF['table_name']." alter column ";
					$sql .=  $temp[1]." set DEFAULT ".$this->valF['column_default'].""; // guillement à mettre ans le champs";
					$res = $this->f->db->query($sql);    
					if ($this->f->isDatabaseError($res, true)) {
						// Appel de la methode de recuperation des erreurs
						$this->addToMessage($res->getDebugInfo());
						$this->erreur_db($res->getDebugInfo(), $res->getMessage(), '');
						$this->correct = false;
						// Return
						return $this->end_treatment(__METHOD__, false);
					}else
						$this->addToMessage("ajout de la valeur par default ".$this->valF['column_name']);				
				}
			}else{
			// sql => destruction de champs
			// destruction de la table -> CASCADE ?
			$sql  = "alter table ".DB_PREFIXE.$temp[0]." drop IF EXISTS ";
			$sql .=  $temp[1];
			$res = $this->f->db->query($sql);    
				if ($this->f->isDatabaseError($res, true)) {
					$this->addToMessage($res->getDebugInfo());
					// Appel de la methode de recuperation des erreurs
					$this->erreur_db($res->getDebugInfo(), $res->getMessage(), '');
					$this->correct = false;
					// Return
					return $this->end_treatment(__METHOD__, false);
				}				
			}
			// om_forms
			$this->tab->libelle = $val['libelle'];
			$this->tab->libelle_court = $val['libelle_court'];
			$this->tab->type = $val['type'];
			$this->tab->calcul = $val['calcul'];
			$this->tab->liste = $val['liste'];
			//
			$insert=json_encode($this->tab);
			//$sql  = "comment on COLUMN ".DB_PREFIXE.$this->valF['table_name'].".".$temp[1]." IS ";
			$sql = "update ".DB_PREFIXE."om_forms set parametres = ";
			$sql .= "'".$insert."'";
			$sql .= " where column_name = '".$this->valF['column_name']."'";
			$res = $this->f->db->query($sql);    
			if ($this->f->isDatabaseError($res, true)) {
				// Appel de la methode de recuperation des erreurs[1
				$this->addToMessage($res->getDebugInfo());
				$this->erreur_db($res->getDebugInfo(), $res->getMessage(), '');
				$this->correct = false;
				// Return
				return $this->end_treatment(__METHOD__, false);
			}else{
				$this->addToMessage("modification des paramètres en om_forms pour"." ".$this->valF['column_name']);
				if($this->triggermodifierapres($id, $this->f->db, $val, null) === false) {
					$this->correct = false;
					$this->addToLog(__METHOD__."(): ERROR", DEBUG_MODE);
					// Return
					return $this->end_treatment(__METHOD__, false);
				} 	
			}  			 				
		}else {
            // Message d'echec (saut d'une ligne supplementaire avant le
            // message pour qu'il soit mis en evidence)
            $this->addToMessage("<br/>".__("modification non effectués")."<br/>");
            $message = __("champ non modifié");
            $this->addToLog(__METHOD__."(): ".$message, VERBOSE_MODE);
            // Return
            return $this->end_treatment(__METHOD__, false);
        }	
        return $this->end_treatment(__METHOD__, true);      
    }

	// supprimer drop columns + om_forms
	function supprimer($val = array(), &$dnu1 = null, $dnu2 = null) {
        // Begin
        $this->begin_treatment(__METHOD__);
        // Mutateur de valF
        $this->setValF($val);
        // verifier si existe des champs 
        $temp = explode(".", $this->valF['column_name']); // valeur du champ 
        $this->verifier($val, $this->f->db, null);
        if ($this->correct) {
			// destruction de la table -> CASCADE ?
			$sql  = "alter table ".DB_PREFIXE.$temp[0]." drop IF EXISTS "; // IF EXISTS = cas sql
			$sql .=  $temp[1];
			$res = $this->f->db->query($sql);    
			if ($this->f->isDatabaseError($res, true)) {
				$this->addToMessage($res->getDebugInfo());
				// Appel de la methode de recuperation des erreurs
				$this->erreur_db($res->getDebugInfo(), $res->getMessage(), '');
				$this->correct = false;
				// Return
				return $this->end_treatment(__METHOD__, false);
			}else{

				$this->addToMessage("destruction du champ ".$this->valF['column_name']);
                if($this->triggersupprimerapres($this->valF[$this->clePrimaire], $this->f->db, $val, null) === false) {
                    $this->correct = false;
                    $this->addToLog(__METHOD__."(): ERROR", DEBUG_MODE);
                    // Return
                    return $this->end_treatment(__METHOD__, false);
                }
			} 
		}else {
            // Message d'echec (saut d'une ligne supplementaire avant le
            // message pour qu'il soit mis en evidence)
            $this->addToMessage("<br/>".__("destruction non effectués")."<br/>");
            $message = __("champ non détruit");
            $this->addToLog(__METHOD__."(): ".$message, VERBOSE_MODE);
            // Return
            return $this->end_treatment(__METHOD__, false);
        }	
        return $this->end_treatment(__METHOD__, true);
    }

	// traitement du data_type cle_secondaire
    function triggerajouterapres($id, &$dnu1 = null, $val = array(), $dnu2 = null) {	
		if($this->valF['data_type']== 'cle_secondaire'){
			$this->cle_secondaire_contrainte($id, $dnu1, $val, $dnu2); 
		}	
    }

    function triggermodifierapres($id, &$dnu1 = null, $val = array(), $dnu2 = null) {
		if($this->valF['data_type']== 'cle_secondaire' and $this->cle_secondaire=='non'){
			$this->cle_secondaire_contrainte($id, $dnu1, $val, $dnu2); 
		}
    }
    
    function cle_secondaire_contrainte($id, &$dnu1 = null, $val = array(), $dnu2 = null) {
			// recherche si le nom de champ correspond à une table
			$temp = explode(".", $this->valF['column_name']); // valeur du champ 
			$schema = substr(DB_PREFIXE, 0, -1);
			$sql  = "select table_name from information_schema.tables ";
			$sql .= " where table_type = 'BASE TABLE' and table_schema = '".$schema."'";
			$sql .= " and table_name = '".$temp[0]."'"; 		
			$table=  $this->f->db->getOne($sql);
			$this->addToLog("triggerajouterapres() : db->getOne(\"".$sql."\")", VERBOSE_MODE);;
			// test existence $table
			if($table){
				$sql  = " ALTER TABLE ONLY ".DB_PREFIXE.$this->valF['table_name'];
				$sql .= " ADD CONSTRAINT  ".$this->valF['table_name']."_".$temp[1]."_fkey FOREIGN KEY (";
				$sql .= $temp[1].") REFERENCES ";
				$sql .= DB_PREFIXE.$temp[1]."(".$temp[1].")";
				//$this->addToMessage($sql);
				$res  =  $this->f->db->query($sql);
				if ($this->f->isDatabaseError($res, true)) {
					// Appel de la methode de recuperation des erreurs
					$this->addToMessage($res->getDebugInfo());
					$this->addToMessage(_("pas de creation cle secondaire sur table ")." ".$temp[1]);
					$this->erreur_db($res->getDebugInfo(), $res->getMessage(), '');
					$this->addToLog("triggerajouterapres() : db->query(\"".$sql."\")", VERBOSE_MODE);
				}else
					$this->addToMessage(_("creation cle secondaire sur table ")." ".$temp[1]);
				$this->db->commit();
			}else
				$this->addToMessage(_("pas de creation cle secondaire sur table inexixtante")." ".$temp[1]);		
	}
	
    function triggersupprimerapres($id, &$dnu1 = null, $val = array(), $dnu2 = null) {
		// destruction d'om_forms
		$sql  = "delete from ".DB_PREFIXE."om_forms";
		$sql  .= " where column_name = '".$this->valF['column_name']."'";
		$res  =  $this->f->db->query($sql);
		$this->addToLog("triggersupprimerapres() : db->query(\"".$sql."\")", VERBOSE_MODE);
		if ($this->f->isDatabaseError($res, true)) {
			$this->addToMessage($res->getDebugInfo());
		}else 		
			$this->addToMessage(_("suppression enregistrement om_forms pour")." ".DB_PREFIXE.$this->valF['table_name']."");		
	}	
	
// ============================================
// action 4 et 5 pour présenter les formulaires
// ============================================

	// affichage en select des om_tables
	// recherche des champs dans les colonnes
    function view_composer() {
        // Verification de l'accessibilité sur l'élément
        $this->checkAccessibility();
        
        // Affichage du formulaire de sélection de tables
         
        // Ouverture du formulaire
        $this->f->layout->display__form_container__begin(array(
            "action" => $this->getDataSubmit(),
            "id" => "dashboard_composer_form",
        ));        
        // ===============================================
        // Paramétrage des champs du formulaire table_name
        // ===============================================
        $champs = array("table_name");
        // Création d'un nouvel objet de type formulaire
        $form = $this->f->get_inst__om_formulaire(array(
            "validation" => 0,
            "maj" => 0,
            "champs" => $champs,
        ));
        // Paramétrage du champs table_name 
        $form->setLib("table_name", __("Formulaire pour la table"));
        $form->setType("table_name", "select");
        $form->setTaille("table_name", 25);
        $form->setOnChange("table_name", "submit()");
        $form->setMax("table_name", 25);
        $form->setVal("table_name", (isset($_POST["table_name"]) ? $_POST["table_name"] : ""));
        $sql = " SELECT  om_tables.table_name, om_tables.table_name as lib ";
        $sql .= " FROM ".DB_PREFIXE."om_tables ";
        $sql .= " where substring(table_name,1,3) not like 'om_' ORDER BY om_tables.table_name ";
        $res = $this->f->db->query($sql);
        $this->addToLog(__METHOD__."(): db->query(\"".$sql."\");", VERBOSE_MODE);
        $this->f->isDatabaseError($res);
        //
        $contenu = array(array(""), array(__("choisir une table")));
        while ($row =& $res->fetchrow()) {
            $contenu[0][] = $row[0];
            $contenu[1][] = $row[1];
        }
        $form->setSelect("table_name", $contenu);
        // Affichage du formulaire
        $form->entete();
        $form->afficher($champs, 0, false, false);
        $form->enpied();
        // Fermeture du fomulaire
        $this->f->layout->display__form_container__end();
        // *** fin formulaire table
		// retour du nom de table choisi
        if (!isset($_POST["table_name"]) || $_POST["table_name"] == "") {
            return;
        }
        // Initialisation des paramètres
        $params = array(
            "edition" => array(
                "default_value" => 1,
            ),
        );
        foreach ($this->f->get_initialized_parameters($params) as $key => $value) {
            ${$key} = $value;
        }
        // affichage des widgets-champs
        echo "<div id=\"dashboard-composer\">\n";
        // Ouverture du conteneur #dashboard
        echo "<div id=\"dashboard\">\n";
        // Conteneur permettant de recevoir d'eventuels messages d'erreur des requetes
        // Ajax
        echo "<div id=\"info\">";
        echo "</div>\n";
        // Si le mode edition est active alors on affiche l'action pour ajouter un
        // nouveau widget
        // pas d affichage des champs
        // Si le mode edition est activé alors on affiche un lien contenant
        // le lien vers les actions de contrôle des widgets  
        // le mode edition est toujours actif     
        if ($edition == 1) {
            // creer un lien -> *** enleve display:none sinon pas d'affichage 
            // lancement action 5 : view_composer_widget_ctl qui met à jour bloc et position en modification
            // et type=hidden en suppression
            printf(
                '<a id="widgetctl-href-base" href="%s"  >&nbsp;</a>',
                OM_ROUTE_FORM."&obj=om_forms&idx=0&action=5"
            );
        }       
        // Ouverture du conteneur de colonnes
        // nombre de colonne à afficher -> json d'om_tables
		$sql = " select  parametres ";
		$sql .= " from ".DB_PREFIXE."om_tables_parametre ";
		$sql .=" where table_name = '".$_POST['table_name']."' ";
		$res = $this->f->db->getOne($sql);
		$this->f->addToLog("app/om_forms_ class.php: db->query(\"".$sql."\");", VERBOSE_MODE);
		$this->f->isDatabaseError($res);
		$tab=json_decode($res);
		$nb_column=$tab->nombre_colonne;
		$this->addToLog("view_composer() : db->getOne(\"".$sql."\")", VERBOSE_MODE);;
		if(!is_numeric($nb_column))
			$nb_column=2;
        echo "<div class=\"col".$nb_column."\">\n";                
        // ajout des champs par colonne
        // requête globale
		$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 .= " 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) = '".$_POST["table_name"]."'";
		$sql .= " and parametres->>'type' != 'sql'"; // seul les sql ne sont pas dans le formulaire
		$sql .= " order by parametres->>'bloc',parametres->>'position' ";
		//echo $sql;
		$res = $this->f->db->query($sql);
		$this->f->addToLog("app/om_champs_class.php: db->query(\"".$sql."\");", VERBOSE_MODE);
		$this->f->isDatabaseError($res);
		while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
			//$temp=json_decode($row["comment"]);			
			$c[$row["column_name"]]["position"]=$row['position'];
			$c[$row["column_name"]]["bloc"]=$row['bloc'];
			$c[$row["column_name"]]["type"]=$row['type'];
			$c[$row["column_name"]]["table_name"]=$row["table_name"];
			$c[$row["column_name"]]["column_name"]=$row["column_name"];
			$c[$row["column_name"]]["data_type"]=$row["data_type"];
	    }	
	    // On boucle sur chacune des colonnes
	    for ($i = 1; $i <= $nb_column; $i++) {
			// Ouverture du conteneur .column
            echo "<div class=\"column\" id=\"column_".$i."\">\n";
			foreach($c as $key => $elem){
				if($elem["bloc"]=="C".intval($i)){
					//echo "->".$elem['column_name']." ".$elem['position']." ";
					//echo $elem['bloc']."*<br>";
					/*
					if($elem['type'] == 'hidden'  or $elem['type'] == 'sql' ){
						 echo '<br>'.$elem['column_name']." [".$elem['type']." ]";
					}else{
					*/ 
					// Affichage des colonnes               
					$this->display_dashboard_widget(
						$elem['column_name'], // pour update et delete
						$elem['column_name'], // titre fenetre champ
						$elem['data_type']." - ". $elem['type'], // contenu 
						$elem['data_type'],
						$elem['position'],
						$edition
					);
					//}
				}
			}
            // Fermeture du conteneur .column
            echo "</div>\n";
		}
        // On affiche un conteneur vide pour avec la propriete clear a both pour
        // reinitialiser le positionnement des blocs
        echo "<div class=\"both\"><!-- --></div>\n";
        // Fermeture du conteneur de colonnes
        echo "</div>\n";
        // Fermeture du conteneur #dashboard
        echo "</div>\n";
        // Fermeture du conteneur #dashboard-composer
        echo "</div>\n";
    }

	// fonction appellée par  view_composer()
    // affichage des colonnes
    function display_dashboard_widget($id = NULL, $title = NULL, $content = NULL, $footer = NULL, $type = NULL, $mode_edit = false) {
		// Ouverture du conteneur du widget
        echo "<div";
        echo " class=\"widget ui-widget ui-widget-content ui-helper-clearfix ui-corner-all ".$class_sup."\"";
        echo " id=\"widget_".$id."\"";
        echo ">\n";
        // Titre du widget
        echo "<div class=\"widget-header ";
        if ($mode_edit == true) {
            echo "widget-header-edit widget-header-move ";
        }
        echo "ui-widget-header ui-corner-all\">";
        echo "<h3>";
        echo $title;
        echo "</h3>";
        echo "</div>\n";
        // Ouverture du wrapper : Contenu + Footer
        echo "<div class=\"widget-content-wrapper\">\n";
        // Contenu du widget
        echo "<!-- Start Widget Content -->\n";
        echo "<div class=\"widget-content\">\n\n";
        //
        echo $content;
        //
        echo "\n\n</div>\n";
        echo "<!-- End Widget Content -->\n";
        // Fermeture du wrapper : Contenu + Footer
        echo "</div>\n";
        // Fermeture du conteneur du widget
        echo "</div>\n";
    }

	// mise à jour de comment de om_champs: bloc, position, type
    function view_composer_widget_ctl() {	
        // Initialisation des paramètres
        $params = array(
            "mode" => array(
                "default_value" => null,
            ),
            "widget" => array(
                "default_value" => null,
            ),
            "profil" => array(
                "default_value" => 0,
            ),
        );
        foreach ($this->f->get_initialized_parameters($params) as $key => $value) {
            ${$key} = $value;
        }     
        //UPDATE
        //index.php?module=form&obj=om_champs&idx=0&action=5
        //&mode=update
        //&column_1=widget_livre.auteurxwidget_livre.titre
        //&column_2=widget_livre.fichier
        //&column_3=widget_livre.resume& 
        if ($mode == "update") {
            $c=array();
            $nb=array();
            foreach($this->f->get_submitted_get_value() as $key => $values) {
                // On souhaite récupérer uniquement les paramètres column_*
                if (!$this->f->starts_with($key, "column_")) {
                    continue;
                }
                //$this->f->addToLog(__METHOD__."(): mode update (".$key.");", DEBUG_MODE);
                $bloc = "C".str_replace("column_", "", $key);
                $widgets = explode("xwidget_", $this->f->get_submitted_get_value($key));
                foreach($widgets as $i => $widget_id) {
                    $position = $i+1;
                    $widget_id = str_replace("widget_", "", $widget_id);
                    // Lorsqu'une colonne est vide, il y a une valeur vide dans le
                    // tableau widget, donc si c'est le cas on passe a l'iteration
                    // suivante
                    if ($widget_id == "") {
						//$this->f->addToLog(__METHOD__."(): mode update vide(".$table.");", DEBUG_MODE);
                        continue;                     
                    }else{
						// initialisation du nom de la table pour requête sql
						$temp1=explode(".", $widget_id);
						$table=$temp1[0];
						//$this->f->addToLog(__METHOD__."(): mode update existant(".$table.");", DEBUG_MODE);
					}
                   $c[$widget_id]["position"]=$position;
                   $c[$widget_id]["bloc"]=$bloc;
                   // comptage par bloc                
                }
                // max par colonne pour position des champs hidden
                $nb[$bloc]=$position;
            }
            //$this->f->addToLog(__METHOD__."(): mode update(".print_r($c, true).");", DEBUG_MODE);
            //$this->f->addToLog(__METHOD__."(): mode update(".$table.");", DEBUG_MODE);           
			$sql = " select om_forms.column_name, om_forms.table_name, data_type, ";
			$sql .= " parametres->>'bloc' as bloc, parametres->>'position' as position, "; 
			$sql .= " parametres->>'type' as type ";
			$sql .= " FROM ".DB_PREFIXE."om_forms left join ".DB_PREFIXE."om_champs ";
			$sql .= " on om_champs.column_name=om_forms.column_name ";
			$sql .= " where om_forms.table_name = '".$table."' ";  
			$res = $this->f->db->query($sql);
			$this->f->addToLog("app/om_champs_class.php: db->query(\"".$sql." / ".$widget_id."\");", DEBUG_MODE);
			$this->f->isDatabaseError($res);
			while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
				$temp1=explode(".", $row['column_name']);
				//$this->f->addToLog(__METHOD__."(): mode update type (".$temp->type.");", DEBUG_MODE);			
				if($row['type']=='hidden'){ // cas des champs cachés non affichés -> hidden
					 $nb['C1']++;
					 $bloc= 'C1';
					 $position=$nb['C1'];
					 $this->f->addToLog(__METHOD__."(): mode update *** (".$row['column_name']." | ".$row['type']." | ".$row['position']." [ ".$row['bloc']."*);", DEBUG_MODE);
				}else{
					$bloc=$c[$row['column_name']]["bloc"];
					$position=$c[$row['column_name']]["position"];
					$this->f->addToLog(__METHOD__."(): mode update ->  (".$row['column_name']." | ".$row['type']." | ".$row['position']." [ ".$row['bloc']."*);", DEBUG_MODE);

				}
				// update bloc 
				$sql = " update ".DB_PREFIXE."om_forms set parametres = jsonb_set(parametres, '{bloc}', '\"".$bloc."\"')";
				$sql .= " WHERE column_name = '".$row['column_name']."'" ; 
				$res3 = $this->f->db->query($sql);    
				$this->f->addToLog(__METHOD__."(): mode update(\"".$sql."\", ".print_r($temp, true).");", DEBUG_MODE);
				$this->f->isDatabaseError($res3);
				// update position
				$sql = " update ".DB_PREFIXE."om_forms set parametres = jsonb_set(parametres, '{position}', '\"".$position."\"')";
				$sql .= " WHERE column_name = '".$row['column_name']."'" ; 
				$res2 = $this->f->db->query($sql);    
				$this->f->addToLog(__METHOD__."(): mode update(\"".$sql."\", ".print_r($temp, true).");", DEBUG_MODE);
				$this->f->isDatabaseError($res2);
				 
			}	           
            return;
        }
        // DELETE
        // module=form&obj=om_forms&idx=0&action=5&mode=delete&widget=widget_livre.auteur
        if ($mode == "delete") {			
			// widget est le column_name concerné
            $widget = str_replace("widget_", "", $widget);
            // en mode delete le champs est mis en hidden 
			$sql = " update ".DB_PREFIXE."om_forms set parametres = jsonb_set(parametres, '{type}', '\"hidden\"')";
			$sql .= " WHERE column_name = '".$widget."'" ; 
			$res = $this->f->db->query($sql);    
			$this->f->addToLog(__METHOD__."(): mode delete ".$sql." ;", DEBUG_MODE);
			$this->f->isDatabaseError($res); 	
            
            return;
        }         
    }
/*
// =======================================
// action 5 et 6 pour présenter les listes
// =======================================

	// affichage en select des om_tables
	// recherche des champs dans les colonnes
    function view_composer_liste() {
        // Verification de l'accessibilité sur l'élément
        $this->checkAccessibility();
        
        // Affichage du formulaire de sélection de tables
         
        // Ouverture du formulaire
        $this->f->layout->display__form_container__begin(array(
            "action" => $this->getDataSubmit(),
            "id" => "dashboard_composer_form",
        ));        
        // ===============================================
        // Paramétrage des champs du formulaire table_name
        // ===============================================
        $champs = array("table_name");
        // Création d'un nouvel objet de type formulaire
        $form = $this->f->get_inst__om_formulaire(array(
            "validation" => 0,
            "maj" => 0,
            "champs" => $champs,
        ));
        // Paramétrage du champs table_name 
        $form->setLib("table_name", __("Formulaire pour la table"));
        $form->setType("table_name", "select");
        $form->setTaille("table_name", 25);
        $form->setOnChange("table_name", "submit()");
        $form->setMax("table_name", 25);
        $form->setVal("table_name", (isset($_POST["table_name"]) ? $_POST["table_name"] : ""));
        $sql = " SELECT  om_tables.table_name, om_tables.table_name as lib ";
        $sql .= " FROM ".DB_PREFIXE."om_tables ";
        $sql .= " where substring(table_name,1,3) not like 'om_' ORDER BY om_tables.table_name ";
        $res = $this->f->db->query($sql);
        $this->addToLog(__METHOD__."(): db->query(\"".$sql."\");", VERBOSE_MODE);
        $this->f->isDatabaseError($res);
        //
        $contenu = array(array(""), array(__("choisir une table")));
        while ($row =& $res->fetchrow()) {
            $contenu[0][] = $row[0];
            $contenu[1][] = $row[1];
        }
        $form->setSelect("table_name", $contenu);
        // Affichage du formulaire
        $form->entete();
        $form->afficher($champs, 0, false, false);
        $form->enpied();
        // Fermeture du fomulaire
        $this->f->layout->display__form_container__end();
        // *** fin formulaire table
		// retour du nom de table choisi
        if (!isset($_POST["table_name"]) || $_POST["table_name"] == "") {
            return;
        }
        // Initialisation des paramètres
        $params = array(
            "edition" => array(
                "default_value" => 1,
            ),
        );
        foreach ($this->f->get_initialized_parameters($params) as $key => $value) {
            ${$key} = $value;
        }
        // affichage des widgets-champs
        echo "<div id=\"dashboard-composer\">\n";
        // Ouverture du conteneur #dashboard
        echo "<div id=\"dashboard\">\n";
        // Conteneur permettant de recevoir d'eventuels messages d'erreur des requetes
        // Ajax
        echo "<div id=\"info\">";
        echo "</div>\n";
        // Si le mode edition est active alors on affiche l'action pour ajouter un
        // nouveau widget
        // pas d affichage des champs
        // Si le mode edition est activé alors on affiche un lien contenant
        // le lien vers les actions de contrôle des widgets  
        // le mode edition est toujours actif     
        if ($edition == 1) {
            // creer un lien -> *** enleve display:none sinon pas d'affichage 
            // lancement action 5 : view_composer_widget_ctl qui met à jour bloc et position en modification
            // et type=hidden en suppression
            printf(
                '<a id="widgetctl-href-base" href="%s"  >&nbsp;</a>',
                OM_ROUTE_FORM."&obj=om_forms&idx=0&action=7"
            );
        }       
        // Ouverture du conteneur de colonnes
        // 1 colonne à afficher pour ordre de liste
         echo "<div class=\"col1\">\n";                 
        // ajout des champs par colonne
        // requête globale
		$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_liste' as position ";
		$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) = '".$_POST["table_name"]."'";
		$sql .= " and parametres->>'liste' = '1' ";
		$sql .= " order by parametres->>'position_liste' ";
		//echo $sql;
		$res = $this->f->db->query($sql);
		$this->f->addToLog("app/om_champs_class.php: db->query(\"".$sql."\");", VERBOSE_MODE);
		$this->f->isDatabaseError($res);
		while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
			//$temp=json_decode($row["comment"]);			
			$c[$row["column_name"]]["position"]=$row['position_liste'];
			$c[$row["column_name"]]["bloc"]=$row['bloc'];
			$c[$row["column_name"]]["type"]=$row['type'];
			$c[$row["column_name"]]["table_name"]=$row["table_name"];
			$c[$row["column_name"]]["column_name"]=$row["column_name"];
			$c[$row["column_name"]]["data_type"]=$row["data_type"];
	    }	
	    // On boucle sur une colonnes
		// Ouverture du conteneur .column
		echo "<div class=\"column\" id=\"column_1\">\n";
		foreach($c as $key => $elem){             
			$this->display_dashboard_widget(
				$elem['column_name'], // pour update et delete
				$elem['column_name'], // titre fenetre champ
				$elem['data_type']." - ". $elem['type'], // contenu 
				$elem['data_type'],
				$elem['position'],
				$edition
			);
		}
		// Fermeture du conteneur .column
		echo "</div>\n";
        // On affiche un conteneur vide pour avec la propriete clear a both pour
        // reinitialiser le positionnement des blocs
        echo "<div class=\"both\"><!-- --></div>\n";
        // Fermeture du conteneur de colonnes
        echo "</div>\n";
        // Fermeture du conteneur #dashboard
        echo "</div>\n";
        // Fermeture du conteneur #dashboard-composer
        echo "</div>\n";
    }

	// mise à jour de comment de om_champs: bloc, position, type
    function view_composer_widget_ctl_liste() {	
        // Initialisation des paramètres
        $params = array(
            "mode" => array(
                "default_value" => null,
            ),
            "widget" => array(
                "default_value" => null,
            ),
            "profil" => array(
                "default_value" => 0,
            ),
        );
        foreach ($this->f->get_initialized_parameters($params) as $key => $value) {
            ${$key} = $value;
        }     
        //UPDATE
        //index.php?module=form&obj=om_champs&idx=0&action=5
        //&mode=update
        //&column_1=widget_livre.auteurxwidget_livre.titre
        //&column_2=widget_livre.fichier
        //&column_3=widget_livre.resume& 
        if ($mode == "update") {
            $c=array();
            $nb=array();
            foreach($this->f->get_submitted_get_value() as $key => $values) {
                // On souhaite récupérer uniquement les paramètres column_*
                if (!$this->f->starts_with($key, "column_")) {
                    continue;
                }
                //$this->f->addToLog(__METHOD__."(): mode update (".$key.");", DEBUG_MODE);
                $bloc = "C".str_replace("column_", "", $key);
                $widgets = explode("xwidget_", $this->f->get_submitted_get_value($key));
                foreach($widgets as $i => $widget_id) {
                    $position = $i+1;
                    $widget_id = str_replace("widget_", "", $widget_id);
                    // Lorsqu'une colonne est vide, il y a une valeur vide dans le
                    // tableau widget, donc si c'est le cas on passe a l'iteration
                    // suivante
                    if ($widget_id == "") {
						//$this->f->addToLog(__METHOD__."(): mode update vide(".$table.");", DEBUG_MODE);
                        continue;                     
                    }else{
						// initialisation du nom de la table pour requête sql
						$temp1=explode(".", $widget_id);
						$table=$temp1[0];
						//$this->f->addToLog(__METHOD__."(): mode update existant(".$table.");", DEBUG_MODE);
					}
                   $c[$widget_id]["position"]=$position;         
                }
                // max par colonne pour position des champs hidden
                $nb[$bloc]=$position;
            }
            //$this->f->addToLog(__METHOD__."(): mode update(".print_r($c, true).");", DEBUG_MODE);
            //$this->f->addToLog(__METHOD__."(): mode update(".$table.");", DEBUG_MODE);           
			$sql = " select om_forms.column_name, om_forms.table_name, data_type, ";
			$sql .= " parametres->>'bloc' as bloc, parametres->>'position_liste' as position, "; 
			$sql .= " parametres->>'type' as type, parametres->>'liste' as liste ";
			$sql .= " FROM ".DB_PREFIXE."om_forms left join ".DB_PREFIXE."om_champs ";
			$sql .= " on om_champs.column_name=om_forms.column_name ";
			$sql .= " where om_forms.table_name = '".$table."' ";  
			$res = $this->f->db->query($sql);
			$this->f->addToLog("app/om_champs_class.php: db->query(\"".$sql." / ".$widget_id."\");", DEBUG_MODE);
			$this->f->isDatabaseError($res);
			while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
				$temp1=explode(".", $row['column_name']);
				$position=$c[$row['column_name']]["position"];
				$this->f->addToLog(__METHOD__."(): mode update ->  (".$row['column_name']." | ".$row['type']." | ".$row['position']." [ ".$row['bloc']."*);", DEBUG_MODE);
				// update position
				$sql = " update ".DB_PREFIXE."om_forms set parametres = jsonb_set(parametres, '{position_liste}', '\"".$position."\"')";
				$sql .= " WHERE column_name = '".$row['column_name']."'" ; 
				$res2 = $this->f->db->query($sql);    
				$this->f->addToLog(__METHOD__."(): mode update(\"".$sql."\", ".print_r($temp, true).");", DEBUG_MODE);
				$this->f->isDatabaseError($res2);
				 
			}	           
            return;
        }
        // DELETE
        // module=form&obj=om_forms&idx=0&action=5&mode=delete&widget=widget_livre.auteur
        if ($mode == "delete") {			
			// widget est le column_name concerné
            $widget = str_replace("widget_", "", $widget);
            // en mode delete le champs est mis en hidden 
			$sql = " update ".DB_PREFIXE."om_forms set parametres = jsonb_set(parametres, '{liste}', '\"0\"')";
			$sql .= " WHERE column_name = '".$widget."'" ; 
			$res = $this->f->db->query($sql);    
			$this->f->addToLog(__METHOD__."(): mode delete ".$sql." ;", DEBUG_MODE);
			$this->f->isDatabaseError($res); 	            
            return;
        }         
    }
*/


}
