[Noalyss-commit] [noalyss] 14/34: Task 0002128: Clôture comptabilité française Réécriture op. ouverture et cloture

[dwm]

sparkyx pushed a commit to branch devel
in repository noalyss.

commit 590856dcf7f6534fa6e0852bc4e0d32aff417122
Author: sparkyx <danydb@noalyss.eu>
AuthorDate: Sat Jan 13 16:04:52 2024 +0100

    Task 0002128: Clôture comptabilité française
    Réécriture op. ouverture et cloture
---
 include/class/operation_exercice.class.php         |  28 ++-
 include/class/operation_opening.class.php          |   6 +-
 include/constant.php                               |   2 +-
 include/sql/patch/upgrade190.sql                   |  99 ++++++++++
 unit-test/include/class/operation_exerciceTest.php | 209 +++++++++++++++++++++
 5 files changed, 335 insertions(+), 9 deletions(-)

diff --git a/include/class/operation_exercice.class.php 
b/include/class/operation_exercice.class.php
index d76e4bd05..2c9f895db 100644
--- a/include/class/operation_exercice.class.php
+++ b/include/class/operation_exercice.class.php
@@ -231,13 +231,6 @@ select sum(signed_amount) delta,sum(debit) 
debit,sum(credit) credit from saldo_d
             $cn->commit();
             return true;
         } catch (\Exception $e) {
-//            $a_error=array(2=>_("la date est obligatoire",),1=>_('Crédit et 
débit non équilibré'),3=>_("Montant invalide"),
-//                4=>_("Fiche non permise dans ce journal"),6=>_("Période 
fermée ou inexistante"),EXC_DUPLICATE=>_("Opération déja transférée"));
-//            $exc_code=$e->getCode();
-//
-//            if ( isset($a_error[$exc_code] ) )
-//                $oe_result=$a_error[$e->getCode()];
-//            else
             $oe_result=$e->getMessage();
 
             $oe_status='NOK';
@@ -247,6 +240,14 @@ select sum(signed_amount) delta,sum(debit) 
debit,sum(credit) credit from saldo_d
 
     }
 
+    /**
+     * @brief Transform the data in table OPERATION_EXERCICE and 
OPERATION_EXERCICE_DETAIL into an array usable
+     *  by Acc_Ledger, the result will be stored into the global variable 
$oe_data
+     * @globals $oe_data array with the data transformed
+     * @param $ledger_id
+     * @return void
+     * @throws Exception
+     */
     function transform($ledger_id)
     {
         global $oe_data; // transform data to array used by 
Acc_Ledger::verify_operation
@@ -294,4 +295,17 @@ select sum(signed_amount) delta,sum(debit) 
debit,sum(credit) credit from saldo_d
         }
     }
 
+    public function get_operation_exercice_sql(): Operation_Exercice_SQL
+    {
+        return $this->operation_exercice_sql;
+    }
+
+    public function set_operation_exercice_sql(Operation_Exercice_SQL 
$operation_exercice_sql): Operation_Exercice_SQL
+    {
+        $this->operation_exercice_sql = $operation_exercice_sql;
+        return $this;
+
+    }
+
+
 }
diff --git a/include/class/operation_opening.class.php 
b/include/class/operation_opening.class.php
index 4252cb972..f727ec852 100644
--- a/include/class/operation_opening.class.php
+++ b/include/class/operation_opening.class.php
@@ -19,9 +19,13 @@
 // Copyright Author Dany De Bontridder danydb@aevalys.eu 6/01/24
 /*! 
  * \file
- * \brief 
+ * \brief Operation OUVERTURE
  */
 
+/**
+ * @class
+ * @brief Operation ouverture
+ */
 class Operation_Opening extends Operation_Exercice
 {
     private $signature;
diff --git a/include/constant.php b/include/constant.php
index adcc63388..5ac2b51c3 100644
--- a/include/constant.php
+++ b/include/constant.php
@@ -123,7 +123,7 @@ if (!defined("NOALYSS_PACKAGE_REPOSITORY")) {
 if (!defined("SYSINFO_DISPLAY")) {
     define("SYSINFO_DISPLAY", TRUE);
 }
-define("DBVERSION", 190);
+define("DBVERSION", 191);
 define("MONO_DATABASE", 25);
 define("DBVERSIONREPO", 20);
 define('NOTFOUND', '--not found--');
diff --git a/include/sql/patch/upgrade190.sql b/include/sql/patch/upgrade190.sql
new file mode 100644
index 000000000..e58122b77
--- /dev/null
+++ b/include/sql/patch/upgrade190.sql
@@ -0,0 +1,99 @@
+begin;
+
+-- protect against wrong card in fiche_detail
+
+CREATE OR REPLACE FUNCTION comptaproc.fiche_detail_check_qcode()
+    RETURNS trigger
+    LANGUAGE plpgsql
+AS $function$
+declare
+    i record;
+begin
+    if NEW.ad_id=23 and NEW.ad_value != OLD.ad_value then
+        update jrnx set j_qcode=NEW.ad_value where j_qcode = OLD.ad_value;
+        update op_predef_detail set opd_poste=NEW.ad_value where 
opd_poste=OLD.ad_value;
+        for i in select ad_id from attr_def where ad_type = 'card' or ad_id=25 
loop
+                update fiche_detail set ad_value=NEW.ad_value where 
ad_value=OLD.ad_value and ad_id=i.ad_id;
+                if i.ad_id=19 then
+                    update stock_goods set sg_code=NEW.ad_value where 
sg_code=OLD.ad_value;
+                end if;
+
+            end loop;
+    end if;
+    return NEW;
+end;
+$function$;
+
+drop trigger if exists fiche_detail_check_qcode_trg on public.fiche_detail ;
+drop function   if exists  comptaproc.fiche_detail_qcode_upd() cascade;
+
+create trigger fiche_detail_check_qcode_trg before insert
+    or update on
+    public.fiche_detail for each row execute function 
comptaproc.fiche_detail_check_qcode();
+
+
+
+update fiche_detail set ad_value=ad_value where ad_id in (select ad_id from 
attr_def where ad_type='card');
+
+insert into parameter values ('MY_REPORT','N') ON CONFLICT DO NOTHING;
+
+update menu_ref set me_file='payment_method.inc.php' where me_code='CFGPAY';
+
+update menu_ref set me_menu='Principal' where me_code='COMPANY';
+update menu_ref set me_menu='Financier' where me_code='MENUFIN';
+
+CREATE OR REPLACE FUNCTION comptaproc.set_tech_user()
+    RETURNS trigger
+AS $function$
+declare
+    /* variable */
+    noalyss_user text;
+begin
+    new.tech_user := current_setting('noalyss.user_login');
+    new.tech_date := now();
+    return NEW;
+
+exception when others then
+    new.tech_date := now();
+    new.tech_user := current_user;
+    return NEW;
+end ;
+$function$
+    LANGUAGE plpgsql;
+
+drop table if exists operation_exercice_detail;
+drop table if exists operation_exercice;
+
+create table operation_exercice
+(
+    oe_id   bigint generated by default as identity  primary key,
+    oe_date date null,
+    oe_type text not null check  (oe_type = 'opening' or oe_type = 'closing'),
+    oe_text text ,
+    oe_dossier_id int not null,
+    oe_exercice int not null,
+    jr_internal text,
+    oe_transfer_date timestamp,
+    tech_user text,
+    tech_date timestamp default  now()
+);
+
+create table operation_exercice_detail
+(
+    oed_id  bigint generated by default as identity  primary key,
+    oe_id bigint references operation_exercice (oe_id) on update cascade on 
delete cascade not null,
+    oed_poste account_type ,
+    oed_qcode text ,
+    oed_label text,
+    oed_amount numeric (20,4),
+    oed_debit bool
+);
+
+create trigger trg_set_tech_user  before insert or update on 
operation_exercice  for each row execute function comptaproc.set_tech_user();
+
+update menu_ref set me_code='OPCL' , 
me_menu='Ouvert./Fermeture',me_file='operation_exercice.inc.php',me_description='Opération
 de cloture ou d''ouverture d''exercice'
+                  ,me_description_etendue ='Ecriture d''ouverture ou de 
fermeture , , reporte les soldes des comptes de l''année passé du poste 
comptable 0xxx à 5xxxx sur l''année courante ou ferme les comptes de 6 à 7 de 
l''année '
+where me_code='OPEN';
+
+insert into version (val,v_description) values (191,'cloture-ouverture 
exercice-version 9112');
+commit;
\ No newline at end of file
diff --git a/unit-test/include/class/operation_exerciceTest.php 
b/unit-test/include/class/operation_exerciceTest.php
new file mode 100644
index 000000000..fae0233cc
--- /dev/null
+++ b/unit-test/include/class/operation_exerciceTest.php
@@ -0,0 +1,209 @@
+<?php
+
+/*
+ * * Copyright (C) 2022 Dany De Bontridder <dany@alchimerys.be>
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License
+ * as published by the Free Software Foundation; either version 2
+ * of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
+ *
+ * 
+ * Author : Dany De Bontridder danydb@noalyss.eu $(DATE)
+ */
+
+/**
+ * @file
+ * @brief noalyss
+ */
+
+use PHPUnit\Framework\TestCase;
+
+require DIRTEST . '/global.php';
+
+/**
+ * @testdox Test Operation_Exercice, Operation_Opening and Operation_Closing
+ * @coversDefaultClass
+ */
+class Operation_ExerciceTest extends TestCase
+{
+
+    /**
+     * @var Fiche
+     */
+    protected $object;
+    protected $connection;
+
+    /**
+     * Sets up the fixture, for example, opens a network connection.
+     * This method is called before a test method is executed.
+     */
+    protected function setUp(): void
+    {
+
+
+    }
+
+    /**
+     * Tears down the fixture, for example, closes a network connection.
+     * This method is called after a test method is executed.
+     */
+    protected function tearDown(): void
+    {
+        /**
+         * example
+         * if ( ! is_object($this->object->fiche_def)) return;
+         * include_once DIRTEST.'/global.php';
+         * $g_connection=Dossier::connect();
+         * $sql=new ArrayObject();
+         * $sql->append("delete from fiche_detail where f_id in (select f_id 
from fiche where fd_id =\$1 )");
+         * $sql->append("delete from fiche where f_id not in (select f_id from 
fiche_detail where \$1=\$1)");
+         * $sql->append("delete from jnt_fic_attr where fd_id  = \$1 ");
+         * $sql->append("delete from fiche_def where fd_id = \$1");
+         * foreach ($sql as $s) {
+         * $g_connection->exec_sql($s,[$this->object->fiche_def->id]);
+         * }
+         */
+    }
+
+    /**
+     * the setUpBeforeClass() template methods is called before the first test 
of the test case
+     *  class is run
+     */
+    public static function setUpBeforeClass(): void
+    {
+        //        include 'global.php';
+        global $aOperation;
+        $aOperation=array();
+
+
+    }
+
+    /**
+     *  tearDownAfterClass() template methods is calleafter the last test of 
the test case class is run,
+     *
+     */
+    static function tearDownAfterClass(): void
+    {
+        global $g_connection;
+        global $aOperation;
+
+        // clean operation
+        if (!empty($aOperation)) {
+            foreach ($aOperation as $item) {
+                $g_connection->exec_sql("delete from operation_exercice where 
oe_id=$1",[$item]);
+
+            }
+        }
+    }
+//
+//    public function dataExample()
+//    {
+//        return array([1], [2], [3]);
+//    }
+
+    /**
+     * @testdox insert row for opening
+     * @covers       Operation_Opening::insert
+     */
+    function testInsertOpening()
+    {
+        global $g_connection;
+        global $aOperation;
+        $operation=new \Operation_Opening(-1);
+        $operation->set_exercice(2020);
+        $operation->set_from_folder(DOSSIER);
+        $operation->insert();
+        $operation_id=$operation->get_operation_exercice_sql()->getp("oe_id");
+        $this->assertFalse(empty($operation_id),'operation not saved');
+        $aOperation[]=$operation_id;
+        $sum_cred=$g_connection->get_value("select sum(oed_amount) 
+                from operation_exercice_detail where oed_debit='f' and 
oe_id=$1",[$operation_id]);
+        $this->assertEquals(1796.89,$sum_cred,'Total credit incorrect for 
operation $operation_id');
+
+        $sum_deb=$g_connection->get_value("select sum(oed_amount) 
+                from operation_exercice_detail where oed_debit='t' and 
oe_id=$1",[$operation_id]);
+        $this->assertEquals(1117.08,$sum_deb,'Total debit incorrect for 
operation $operation_id');
+
+        $sum=$g_connection->get_value("select sum(oed_amount) 
+                from operation_exercice_detail where 
oe_id=$1",[$operation_id]);
+        $this->assertEquals(2913.97,$sum,'Total incorrect for operation 
$operation_id');
+
+        $g_connection->exec_sql("insert into 
operation_exercice_detail(oe_id,oed_poste,oed_amount,oed_debit)
+values ($1,$2,$3,$4)",[$operation_id,'4111',$sum_cred-$sum_deb,'t']);
+
+    }
+    /**
+     * @testdox insert row for closing
+     * @covers       Operation_Closing::insert
+     */
+
+    function testInsertClosing()
+    {
+        global $g_connection;
+        global $aOperation;
+
+        $operation=new \Operation_Closing(-1);
+        $operation->set_exercice(2020);
+        $operation->insert();
+        $operation_id=$operation->get_operation_exercice_sql()->getp("oe_id");
+        $this->assertFalse(empty($operation_id),'operation not saved');
+        $aOperation[]=$operation_id;
+        $sum_cred=$g_connection->get_value("select sum(oed_amount) 
+                from operation_exercice_detail where oed_debit='f' and 
oe_id=$1",[$operation_id]);
+        $this->assertEquals(813.08,$sum_cred,'Total credit incorrect for 
operation $operation_id');
+
+        $sum_deb=$g_connection->get_value("select sum(oed_amount) 
+                from operation_exercice_detail where oed_debit='t' and 
oe_id=$1",[$operation_id]);
+        $this->assertEquals(1492.89,$sum_deb,'Total debit incorrect for 
operation $operation_id');
+
+        $sum=$g_connection->get_value("select sum(oed_amount) 
+                from operation_exercice_detail where 
oe_id=$1",[$operation_id]);
+        $this->assertEquals(2305.97,$sum,'Total incorrect for operation 
$operation_id');
+        $g_connection->exec_sql("insert into 
operation_exercice_detail(oe_id,oed_poste,oed_amount,oed_debit)
+values ($1,$2,$3,$4)",[$operation_id,'140',$sum_cred-$sum_deb,'t']);
+
+
+    }
+
+    /**
+     * @testdox transform into an array for Acc_ledger
+     * @depends testInsertClosing
+     * @depends testInsertOpening
+     * @return void
+     */
+    function testTransformOpening()
+    {
+        global $g_connection;
+        global $aOperation;
+        $ledger_id=4;
+
+        // clean operation
+        if (!empty($aOperation)) {
+            foreach ($aOperation as $item) {
+                $operation_sql=new 
\Operation_Exercice_SQL($g_connection,$item);
+                if ( $operation_sql->getp('oe_type')=='opening') {
+                    global $oe_data;
+                    $operation=new \Operation_Opening($item);
+                    $operation->transform($ledger_id);
+                    $this->assertEquals(12,$oe_data['nb_item'],"Number of 
elements not correct");
+                }
+                if ( $operation_sql->getp('oe_type')=='closing') {
+                    global $oe_data;
+                    $operation=new \Operation_Closing($item);
+                    $operation->transform($ledger_id);
+                    $this->assertEquals(5,$oe_data['nb_item'],"Number of 
elements not correct");
+                }
+            }
+        }
+    }
+}
\ No newline at end of file