[Noalyss-commit] [noalyss] 03/16: Currency : create view + add currency name to table currency

[Dany De Bontridder]

sparkyx pushed a commit to branch r700-currency
in repository noalyss.

commit 9733fadf9121a947cdf96a24830338402168e64f
Author: Dany De Bontridder <address@hidden>
Date:   Fri Apr 20 19:12:44 2018 +0200

    Currency : create view + add currency name to table currency
---
 .../currency_mtable.class.php}                     | 45 +++++-----------------
 include/database/currency_history_sql.class.php    |  2 +-
 include/database/currency_sql.class.php            |  2 +
 sql/upgrade.sql                                    | 23 +++++++++++
 4 files changed, 36 insertions(+), 36 deletions(-)

diff --git a/include/database/currency_sql.class.php 
b/include/class/currency_mtable.class.php
similarity index 51%
copy from include/database/currency_sql.class.php
copy to include/class/currency_mtable.class.php
index 0d00e0b..acfb7ae 100644
--- a/include/database/currency_sql.class.php
+++ b/include/class/currency_mtable.class.php
@@ -19,43 +19,18 @@
  */
 // Copyright (2018) Author Dany De Bontridder <address@hidden>
 
-require_once NOALYSS_INCLUDE.'/lib/noalyss_sql.class.php';
-require_once NOALYSS_INCLUDE.'/lib/database.class.php';
-
 /**
- * class_currency_sql.php
- *
  * @file
- * @brief abstract of the table public.currency */
-class Currency_SQL extends Noalyss_SQL
-{
+ * @brief Currency thanks the view v_currency_last_value
+ */
 
-    function __construct(Database $p_cn, $p_id=-1)
+class Current_MTable extends Manage_Table_SQL
+{
+    function __construct(Data $p_table)
     {
-        $this->table="public.currency";
-        $this->primary_key="id";
-        /*
-         * List of columns
-         */
-        $this->name=array(
-            "id"=>"id"
-            , "cr_code_iso"=>"cr_code_iso"
-        );
-        /*
-         * Type of columns
-         */
-        $this->type=array(
-            "id"=>"numeric"
-            , "cr_code_iso"=>"text"
-        );
-
-
-        $this->default=array(
-            "id"=>"auto"
-        );
-
-        $this->date_format="DD.MM.YYYY";
-        parent::__construct($p_cn, $p_id);
+        $this->table=$p_table;
+        parent::__construct($p_table);
+        // 
+                
     }
-
-}
+}
\ No newline at end of file
diff --git a/include/database/currency_history_sql.class.php 
b/include/database/currency_history_sql.class.php
index e193532..05f35a4 100644
--- a/include/database/currency_history_sql.class.php
+++ b/include/database/currency_history_sql.class.php
@@ -58,7 +58,7 @@ class Currency_history_SQL extends Noalyss_SQL
             "id"=>"auto"
         );
 
-        $this->date_format="DD.MM.YYYY";
+        $this->date_format="YYYYMMDD";
         parent::__construct($p_cn, $p_id);
     }
 
diff --git a/include/database/currency_sql.class.php 
b/include/database/currency_sql.class.php
index 0d00e0b..ef67bcc 100644
--- a/include/database/currency_sql.class.php
+++ b/include/database/currency_sql.class.php
@@ -40,6 +40,7 @@ class Currency_SQL extends Noalyss_SQL
         $this->name=array(
             "id"=>"id"
             , "cr_code_iso"=>"cr_code_iso"
+            ,"cr_name"=>"cr_name"
         );
         /*
          * Type of columns
@@ -47,6 +48,7 @@ class Currency_SQL extends Noalyss_SQL
         $this->type=array(
             "id"=>"numeric"
             , "cr_code_iso"=>"text"
+            , "cr_name"=>"text"
         );
 
 
diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index 0aeb267..cdc851f 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -24,3 +24,26 @@ CREATE TABLE public.currency_history (
 ;
 
 -- Ajouter commentaire sur colonne
+
+ALTER TABLE public.currency ADD cr_name varchar(80) NULL;
+
+-- Create view to manage the table
+create view v_currency_last_value as 
+with recent_rate as 
+( select 
+       currency_id,max(ch_from) as rc_from
+       from 
+        currency_history 
+        group by currency_id
+        )
+select 
+       cr1.id as currency_id,
+       cr1.cr_name,
+       cr1.cr_code_iso,
+       ch1.id as currency_history_id,
+       ch1.ch_value as ch_value,
+       rc_from 
+from
+currency as cr1
+join recent_rate on (currency_id=cr1.id)
+join currency_history as ch1 on (recent_rate.currency_id=ch1.currency_id and 
rc_from=ch1.ch_from);
\ No newline at end of file