[openmairie-framework-Commits] r4938 - in openmairie_exemple/branches/om5_rad: . app data/pgsql obj

[fray...@users.adullact.net]

Author: fraynaud
Date: 2024-04-24 22:30:18 +0200 (Wed, 24 Apr 2024)
New Revision: 4938

Modified:
openmairie_exemple/branches/om5_rad/HISTORY.txt
openmairie_exemple/branches/om5_rad/TODO.txt
openmairie_exemple/branches/om5_rad/app/om_gen_plus.class.php
openmairie_exemple/branches/om5_rad/data/pgsql/init_gen_plus.sql
openmairie_exemple/branches/om5_rad/data/pgsql/init_temp.sql
openmairie_exemple/branches/om5_rad/obj/om_forms.class.php
Log:

ajout du type sql - 4 operations simples sur champs de la table



Modified: openmairie_exemple/branches/om5_rad/HISTORY.txt
===================================================================
--- openmairie_exemple/branches/om5_rad/HISTORY.txt 2024-04-24 17:42:23 UTC (rev 4937)
+++ openmairie_exemple/branches/om5_rad/HISTORY.txt 2024-04-24 20:30:18 UTC (rev 4938)
@@ -125,6 +125,8 @@
* Evolution : om_gen_plus méthode table_sql_inc_gen -> ordre des champs om-forms 17-04-2024
* Evolution : om_gen_plus : liste : formatage présentation du champ double précision avec round
-> cast en numeric -> sur 2 caractères
+* Evolution : om_champs : ajout du type sql :-les 4 opérations sur champs de la table 24/04/24

+
4.10.0 (29/11/2022
-------------------

Modified: openmairie_exemple/branches/om5_rad/TODO.txt
===================================================================
--- openmairie_exemple/branches/om5_rad/TODO.txt 2024-04-24 17:42:23 UTC (rev 4937)
+++ openmairie_exemple/branches/om5_rad/TODO.txt 2024-04-24 20:30:18 UTC (rev 4938)
@@ -59,16 +59,32 @@

- cle secondaire ne doit pas avoir accès à calcul

+*** agregat avec les vues

-* Preciser les champs parametres si acces table om_forms et om_tables_parametre
-SELECT om_forms.column_name as "champ",om_forms.table_name as "table",om_champs.data_type as "type",om_champs.character_maximum_length as "max",
-om_contraintes.constraint_name as "clé secondaire",case om_champs.is_nullable when 'YES' then 'Non' else 'Oui' end as "obligatoire",
-om_champs.column_default as "défaut",om_forms.parametres->>'libelle' as libelle,om_forms.parametres->>'type' as type,om_forms.parametres->>'bloc' as bloc,
-om_forms.parametres->>'position' as position,om_forms.parametres->>'calcul' as calcul,om_forms.parametres->>'liste' as liste,
-om_tables_parametre.parametres->>'nombre_colonne' as lib, om_forms.table_name
-FROM om5.om_forms LEFT JOIN om5.om_tables ON om_forms.table_name=om_tables.table_name
-LEFT JOIN om5.om_contraintes ON om_forms.column_name=concat(om_contraintes.table_name,'.',om_contraintes.column_name)
-left join om5.om_champs on om_champs.column_name=om_forms.column_name
-left join om5.om_tables_parametre on om_tables_parametre.table_name='article'
-WHERE (om_champs.table_name = 'article') and concat(om_champs.table_name,'.',om_champs.table_name) not like om_champs.column_name
-ORDER BY om_forms.column_name ASC NULLS LAST LIMIT 15 OFFSET 0
+CREATE OR REPLACE VIEW AS cumul_champsousform_form
+ SELECT form.cle_primaire
+ sum(sousform.champ) AS cumul_champ
+ FROM sousform
+ JOIN ON sousform.cle_secondaire = form.cle_primaire
+ GROUP BY form.cleprimaire
+
+CREATE OR REPLACE VIEW om5.article_montant_ht AS
+ SELECT famille.famille,
+ sum(montant_ht) AS montant_ht
+ FROM om5.article
+ JOIN om5.famille ON article.famille=famille.famille
+ GROUP BY famille.famille;
+
+-> champs calcul + type sql
+
+sum article.montant_ht
+famille = pk + table
+
+* information_schema.view
+view_catalog "om5_rad";
+view_schema "om5";
+view_name "article_montant_ht";
+table_catalog "om5_rad";
+table_schema "om5";
+table_name"article"
+

Modified: openmairie_exemple/branches/om5_rad/app/om_gen_plus.class.php
===================================================================
--- openmairie_exemple/branches/om5_rad/app/om_gen_plus.class.php 2024-04-24 17:42:23 UTC (rev 4937)
+++ openmairie_exemple/branches/om5_rad/app/om_gen_plus.class.php 2024-04-24 20:30:18 UTC (rev 4938)
@@ -22,11 +22,11 @@
$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_champs ";
- $sql .= " left join ".DB_PREFIXE."om_forms on om_forms.column_name=om_champs.column_name";
+ $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>";
+ // $this->msg .= $sql."<br>";
// Exécution de la requête
$res = $this->f->db->query($sql);
// Logger
@@ -46,6 +46,7 @@
$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);
// parametres om_tables et om_tables_parametre (version 3)
@@ -355,10 +356,15 @@
// 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",',
- $elem['name']
+ $name
);
}
return sprintf(
@@ -527,7 +533,7 @@
// On sort du switch
break;
case "blob" :
- // cas type = hidden ***
+ // cas type = hidden *** sql
if($type=='hidden'){ // ***
if ($maj == 3) {
$tableobj .= sprintf($template_settype_0, $elem['name'], "datestatic");
@@ -643,7 +649,13 @@
}
}
} // switch
+
}
+ // sql
+ foreach($this->om_forms as $elem){
+ if($elem['type']== "sql")
+ $tableobj .= sprintf($template_settype_0, $elem['name'], "hidden");
+ }
//
return $tableobj;
}
@@ -764,7 +776,7 @@
$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 as $elem){
- //$this->msg .= $elem['data_type']."<br>";
+ //$this->msg .= $elem['data_type']." ".$elem['type']." ".$elem['calcul']."<br>";
// champ affiche
if($elem["liste"]==1 and $elem["data_type"]!='text' and $elem["type"]!='file'){ // sauf blob et upload
// initialisation
@@ -812,7 +824,16 @@
if($elem['data_type']== 'boolean'){ // format oui/non
$champaffiche.= "\"case ".$this->table.".".$elem["name"]." when 't' then 'Oui' else 'Non' end as \\\"\".__(\"".$libelle."\").\"\\\"\",";
}
- }else{
+ // 'round(montant_ht::numeric*1.2321, 2) as "'.__("montant ttc").'"',
+ if($elem['type']== 'sql'){ //
+ if($elem['calcul']!=''){
+ $champaffiche.= "'".$elem['calcul'];
+ $champaffiche.= '';
+ $champaffiche.= "as \"'.__(\"".$libelle."\").'\"',";
+ }else
+ $champaffiche.= "' \'\' as \"'.__(\"".$libelle."\").'\"',";
+ }
+ }else{
// champs non affiche
$champnonaffiche.="\n ";
$champnonaffiche.= "'".$this->table.".".$elem["name"]." as \"'.__(\"".$elem["name"]."\").'\"',";

Modified: openmairie_exemple/branches/om5_rad/data/pgsql/init_gen_plus.sql
===================================================================
--- openmairie_exemple/branches/om5_rad/data/pgsql/init_gen_plus.sql 2024-04-24 17:42:23 UTC (rev 4937)
+++ openmairie_exemple/branches/om5_rad/data/pgsql/init_gen_plus.sql 2024-04-24 20:30:18 UTC (rev 4938)
@@ -15,9 +15,6 @@
parametres jsonb
);

-ALTER TABLE ONLY om_tables_parametre
- ADD CONSTRAINT om_tables_parametre_pkey PRIMARY KEY (table_name);
-
CREATE TABLE om_forms
(
column_name character varying(120),
@@ -28,3 +25,5 @@
ALTER TABLE ONLY om_forms
ADD CONSTRAINT om_forms_pkey PRIMARY KEY (column_name);

+ALTER TABLE ONLY om_tables_parametre
+ ADD CONSTRAINT om_tables_parametre_pkey PRIMARY KEY (table_name);

Modified: openmairie_exemple/branches/om5_rad/data/pgsql/init_temp.sql
===================================================================
--- openmairie_exemple/branches/om5_rad/data/pgsql/init_temp.sql 2024-04-24 17:42:23 UTC (rev 4937)
+++ openmairie_exemple/branches/om5_rad/data/pgsql/init_temp.sql 2024-04-24 20:30:18 UTC (rev 4938)
@@ -62,6 +62,10 @@
);


+create table om_vues
+
+
+
-- pk

ALTER TABLE ONLY om_tables

Modified: openmairie_exemple/branches/om5_rad/obj/om_forms.class.php
===================================================================
--- openmairie_exemple/branches/om5_rad/obj/om_forms.class.php 2024-04-24 17:42:23 UTC (rev 4937)
+++ openmairie_exemple/branches/om5_rad/obj/om_forms.class.php 2024-04-24 20:30:18 UTC (rev 4938)
@@ -463,74 +463,82 @@
// 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']);
+ $val['libelle']=$this->verifier_libelle($val['libelle']);
+ // data_type -> type sql
+ if($this->valF['data_type']=='sql')
+ $this->valF['type']= 'sql';
if ($this->correct) {
- // 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);
+ // 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
+ // 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("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(), '');
+ }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);
- }else{
- $this->addToMessage("ajout des paramètres en commentaire du champs"." ".$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);
- }
+ }
}
+ //***
+ // 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)
@@ -897,8 +905,8 @@
// 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_champs ";
- $sql .= " left join ".DB_PREFIXE."om_forms on om_forms.column_name=om_champs.column_name";
+ $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 .= " order by parametres->>'bloc',parametres->>'position' ";
//echo $sql;