[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpcompta-dev] r606 - in trunk/rapport_avance: doc include sql
From: |
phpcompta-dev |
Subject: |
[Phpcompta-dev] r606 - in trunk/rapport_avance: doc include sql |
Date: |
Tue, 10 Dec 2013 17:48:39 +0100 (CET) |
Author: danydb
Date: 2013-12-10 17:48:39 +0100 (Tue, 10 Dec 2013)
New Revision: 606
Modified:
trunk/rapport_avance/doc/rapav-listing-code.odt
trunk/rapport_avance/include/class_rapav.php
trunk/rapport_avance/include/class_rapav_listing_compute.php
trunk/rapport_avance/include/class_rapav_listing_formula.php
trunk/rapport_avance/include/class_rapport_avance_sql.php
trunk/rapport_avance/sql/2-listing_compute_detail.sql
trunk/rapport_avance/sql/2-listing_compute_historique.sql
Log:
task #932 : Listing Ajout Historique
Create table
Task #932 - Listing : ajout op?\195?\169ration Historique
Modified: trunk/rapport_avance/doc/rapav-listing-code.odt
===================================================================
(Binary files differ)
Modified: trunk/rapport_avance/include/class_rapav.php
===================================================================
--- trunk/rapport_avance/include/class_rapav.php 2013-12-10 14:39:08 UTC
(rev 605)
+++ trunk/rapport_avance/include/class_rapav.php 2013-12-10 16:48:39 UTC
(rev 606)
@@ -97,15 +97,15 @@
* @return string
* @throws Exception if $p_date not valid
*/
- static function get_sql_date($p_date)
+ static function get_sql_date($p_date,$p_prefix="")
{
switch ($p_date)
{
case 0:
- $sql_date = "and (jrn1.j_date >= to_date($2,'DD.MM.YYYY') and
jrn1.j_date <= to_date($3,'DD.MM.YYYY'))";
+ $sql_date = "and ($p_prefix.j_date >= to_date($2,'DD.MM.YYYY')
and jrn1.j_date <= to_date($3,'DD.MM.YYYY'))";
break;
case 1:
- $sql_date = " and j_id in
+ $sql_date = " and $p_prefix.j_id in
(select j_id from jrnx join jrn on (j_grpt = jr_grpt_id)
where
coalesce(jr_date_paid,to_date('01.01.1900','DD.MM.YYYY'))
>= to_date($2,'DD.MM.YYYY')
@@ -114,7 +114,7 @@
";
break;
case 2:
- $sql_date = " and j_id in
+ $sql_date = " and $p_prefix.j_id in
(select j_id from jrnx join jrn on (j_grpt = jr_grpt_id)
where
coalesce(jr_ech,to_date('01.01.1900','DD.MM.YYYY')) >=
to_date($2,'DD.MM.YYYY')
Modified: trunk/rapport_avance/include/class_rapav_listing_compute.php
===================================================================
--- trunk/rapport_avance/include/class_rapav_listing_compute.php
2013-12-10 14:39:08 UTC (rev 605)
+++ trunk/rapport_avance/include/class_rapav_listing_compute.php
2013-12-10 16:48:39 UTC (rev 606)
@@ -179,7 +179,8 @@
echo '</tr>';
}
/** for each detail */
- echo '<tr>';
+ $class=($i%2==0)?' class="even" ':'class="odd" ';
+ echo '<tr '.$class.'>';
for ($e=0;$e <$nb_detail;$e++)
{
$detail=$odetail->next($r_detail,$e);
Modified: trunk/rapport_avance/include/class_rapav_listing_formula.php
===================================================================
--- trunk/rapport_avance/include/class_rapav_listing_formula.php
2013-12-10 14:39:08 UTC (rev 605)
+++ trunk/rapport_avance/include/class_rapav_listing_formula.php
2013-12-10 16:48:39 UTC (rev 606)
@@ -31,6 +31,7 @@
$this->detail->lc_code = $this->data->lp_code;
$this->detail->lc_comment = $this->data->lp_comment;
$this->detail->lc_order = $this->data->l_order;
+ $this->detail->lc_histo = $this->data->lp_histo;
$this->detail->save();
$this->fiche->save();
}
@@ -72,7 +73,9 @@
case 'COMP':
$ret = new Rapav_Formula_Compute($obj);
break;
-
+ case 'COMP':
+ $ret = new Rapav_Formula_Histo($obj);
+ break;
default:
throw new Exception('Object ' . var_export($obj, true) . '
invalide ');
break;
@@ -565,14 +568,15 @@
function compute($p_start, $p_end)
{
global $cn;
+ $this->histo=array();
$filter_ledger = "";
if ($this->data->jrn_def_id != "")
{
$filter_ledger = " and jrn1.j_jrn_def = " .
sql_string($this->data->jrn_def_id);
}
- $sql_date = RAPAV::get_sql_date($this->data->date_paid);
$card_saldo = ($this->data->lp_card_saldo == 0) ? "jrn1" : "jrn2";
+ $sql_date = RAPAV::get_sql_date($this->data->date_paid,$card_saldo);
switch ($this->type_operation)
{
case 0:
@@ -600,9 +604,9 @@
case 2:
// Compute D-C
$sql = "
- select sum(jrnx_amount)
- from (
- select distinct $card_saldo.j_id,case when
$card_saldo.j_debit = 't' then $card_saldo.j_montant else
$card_saldo.j_montant*(-1) end as jrnx_amount
+
+ (
+ select distinct
$card_saldo.j_id,$card_saldo.j_grpt,case when $card_saldo.j_debit = 't' then
$card_saldo.j_montant else $card_saldo.j_montant*(-1) end as jrnx_amount
from jrnx as jrn1
join jrnx as jrn2 on (jrn1.j_grpt=jrn2.j_grpt)
where
@@ -614,12 +618,22 @@
$sql_filter_operation
) as tv_amount
";
- $amount = $cn->get_value($sql, array(
+ $amount = $cn->get_value("select sum(jrnx_amount) from ".$sql,
array(
$this->data->fp_formula,
$p_start,
$p_end,
$this->fiche->f_id
));
+ if ($this->data->lp_histo == 1 )
+ {
+ $this->histo=$cn->get_array("select distinct jr_id from
+ jrn join $sql on (j_grpt=jr_grpt_id) ",array (
+ $this->data->fp_formula,
+ $p_start,
+ $p_end,
+ $this->fiche->f_id));
+
+ }
// if C-D is asked then reverse the result
if ($this->data->type_sum_account == 2)
$amount = bcmul($amount, -1);
@@ -627,9 +641,8 @@
// Only DEBIT
case 3:
$sql = "
- select sum(jrnx_amount)
- from (
- select distinct
$card_saldo.j_id,$card_saldo.j_montant as jrnx_amount
+ (
+ select distinct
$card_saldo.j_id,$card_saldo.j_grpt,$card_saldo.j_montant as jrnx_amount
from jrnx as jrn1
join jrnx as jrn2 on (jrn1.j_grpt=jrn2.j_grpt)
where
@@ -643,19 +656,29 @@
$sql_filter_operation
) as tv_amount
";
- $amount = $cn->get_value($sql, array(
+ $amount = $cn->get_value("select sum(jrnx_amount) from".$sql,
array(
$this->data->fp_formula,
$p_start,
$p_end,
$this->fiche->f_id
));
+ if ($this->data->lp_histo == 1 )
+ {
+ $this->histo=$cn->get_array("select distinct jr_id from
+ jrn join $sql on (j_grpt=jr_grpt_id) ",array (
+ $this->data->fp_formula,
+ $p_start,
+ $p_end,
+ $this->fiche->f_id));
+
+ }
+
break;
// Only CREDIT
case 4:
$sql = "
- select sum(jrnx_amount)
- from (
- select distinct
$card_saldo.j_id,jrn1.j_montant as jrnx_amount
+ (
+ select distinct
$card_saldo.j_id,$card_saldo.j_grpt,$card_saldo.j_montant as jrnx_amount
from jrnx as jrn1
join jrnx as jrn2 on (jrn1.j_grpt=jrn2.j_grpt)
where
@@ -669,12 +692,23 @@
$sql_filter_operation
) as tv_amount
";
- $amount = $cn->get_value($sql, array(
+ $amount = $cn->get_value("select sum(jrnx_amount) from ".$sql,
array(
$this->data->fp_formula,
$p_start,
$p_end,
$this->fiche->f_id
));
+ if ($this->data->lp_histo == 1 )
+ {
+ $this->histo=$cn->get_array("select distinct jr_id from
+ jrn join $sql on (j_grpt=jr_grpt_id) ",array (
+ $this->data->fp_formula,
+ $p_start,
+ $p_end,
+ $this->fiche->f_id));
+
+ }
+
break;
default:
@@ -686,12 +720,32 @@
/*
* 4 possibilities with type_sum_account
*/
+ if ($amount=="") $amount=0;
$this->detail->ld_value_numeric = $amount;
}
+ function save_computed()
+ {
+ parent::save_computed();
+ /*
+ * Save history now
+ */
+ if ($this->data->lp_histo == 1 )
+ {
+ for ($e=0;$e< count($this->histo);$e++)
+ {
+ $histo=new RAPAV_Listing_Compute_Historique_SQL();
+ $histo->jr_id=$this->histo[$e]['jr_id'];
+ $histo->ld_id=$this->detail->ld_id;
+ $histo->save();
+ unset($histo);
+ }
+ }
+ }
function input()
{
global $cn;
+ $histo_operation=new ICheckBox('histo');
$account = new IPoste("p_formula", "", "formula_acc_input_id");
$account->label = _("Recherche poste");
$account->set_attribute('gDossier', dossier::id());
@@ -702,6 +756,10 @@
. ' rapport_advanced.total_type_account order by 2');
echo '<p>';
+ echo "Reprendre historique opération: " . $histo_operation->input();
+ echo '</p>';
+
+ echo '<p>';
echo "type de total : " . $sel_total_type_row->input();
echo '</p>';
@@ -731,6 +789,7 @@
$this->data->setp('formula_type', 'ACCOUNT');
$this->data->setp('sum_signed', $p_array['tt_id']);
$this->data->lp_card_saldo = (isset($p_array['card_saldo'])) ? 1 : 0;
+ $this->data->lp_histo = (isset($p_array['histo'])) ? 1 : 0;
$this->data->save();
}
Modified: trunk/rapport_avance/include/class_rapport_avance_sql.php
===================================================================
--- trunk/rapport_avance/include/class_rapport_avance_sql.php 2013-12-10
14:39:08 UTC (rev 605)
+++ trunk/rapport_avance/include/class_rapport_avance_sql.php 2013-12-10
16:48:39 UTC (rev 606)
@@ -362,6 +362,7 @@
"attribut_card" => "ad_id",
"card_saldo" => "lp_card_saldo",
"with_card" => "lp_with_card",
+ "lp_histo"=>"lp_histo"
);
$this->type = array(
@@ -385,6 +386,7 @@
"ad_id" => "numeric",
"lp_card_saldo" => "text",
"lp_with_card" => "text",
+ "lp_histo" => "numeric",
);
$this->default = array(
@@ -547,6 +549,7 @@
var $lc_code;
var $lc_comment;
var $lc_order;
+ var $lc_histo;
/* example private
$variable=array("easy_name"=>column_name,"email"=>"column_name_email","val3"=>0);
*/
@@ -567,6 +570,7 @@
,'code'=>'lc_code'
,'comment'=>'lc_comment'
,'order'=>'lc_order'
+ ,'history'=>'lc_histo'
);
$this->type = array(
@@ -580,6 +584,7 @@
, 'lc_code'=>"text"
, 'lc_comment'=>"text"
, 'lc_order'=>"numeric"
+ , 'lc_histo'=>"numeric"
);
$this->default = array(
@@ -599,5 +604,53 @@
}
}
+/**
+ * @brief Manage the table rapport_advanced.listing_compute_historique
+ */
+class RAPAV_Listing_Compute_Historique_SQL extends Phpcompta_SQL
+{
+ //------ Attributes-----
+ var $lh_id;
+ var $ld_id;
+ var $jr_id;
+
+
+ function __construct($p_id = -1)
+ {
+
+
+ $this->table = "rapport_advanced.listing_compute_historique";
+ $this->primary_key = "lh_id";
+
+ $this->name = array(
+ "lh_id" => "lh_id", "lh_id" => "lh_id"
+ , "ld_id" => "ld_id"
+ , "jr_id" => "jr_id"
+ );
+
+ $this->type = array(
+ "lh_id" => "numeric"
+ , "ld_id" => "numeric"
+ , "jr_id" => "numeric"
+ );
+
+ $this->default = array(
+ "lh_id" => "auto"
+ );
+ global $cn;
+ $this->date_format = "DD.MM.YYYY";
+ parent::__construct($cn, $p_id);
+ }
+
+ /**
+ * @brief Add here your own code: verify is always call BEFORE insert or
update
+ */
+ public function verify()
+ {
+ parent::verify();
+ }
+
+}
+
?>
Modified: trunk/rapport_avance/sql/2-listing_compute_detail.sql
===================================================================
--- trunk/rapport_avance/sql/2-listing_compute_detail.sql 2013-12-10
14:39:08 UTC (rev 605)
+++ trunk/rapport_avance/sql/2-listing_compute_detail.sql 2013-12-10
16:48:39 UTC (rev 606)
@@ -1,3 +1,6 @@
+ALTER TABLE rapport_advanced.listing_param ADD COLUMN lp_histo integer;
+ALTER TABLE rapport_advanced.listing_param ALTER COLUMN lp_histo SET DEFAULT 0;
+COMMENT ON COLUMN rapport_advanced.listing_param.lp_histo IS '0 : no history 1
: with history';
-- Table: rapport_advanced.listing_compute_detail
-- DROP TABLE rapport_advanced.listing_compute_detail;
@@ -14,6 +17,7 @@
lc_code text,
lc_comment text,
lc_order bigint,
+ lc_histo integer DEFAULT 0,
CONSTRAINT listing_compute_detail_pkey PRIMARY KEY (ld_id ),
CONSTRAINT listing_compute_detail_lc_id_fkey FOREIGN KEY (lc_id)
REFERENCES rapport_advanced.listing_compute (lc_id) MATCH SIMPLE
@@ -39,6 +43,7 @@
COMMENT ON COLUMN rapport_advanced.listing_compute_detail.lc_code IS 'code
from listing_param';
COMMENT ON COLUMN rapport_advanced.listing_compute_detail.lc_comment IS
'comment from listing_param';
COMMENT ON COLUMN rapport_advanced.listing_compute_detail.lc_order IS 'order
from listing_param';
+COMMENT ON COLUMN rapport_advanced.listing_compute.lc_histo IS '0 : no history
1 : with history';
-- Index: rapport_advanced.fki_listing_compute_fiche_lf_id2_fk
Modified: trunk/rapport_avance/sql/2-listing_compute_historique.sql
===================================================================
--- trunk/rapport_avance/sql/2-listing_compute_historique.sql 2013-12-10
14:39:08 UTC (rev 605)
+++ trunk/rapport_avance/sql/2-listing_compute_historique.sql 2013-12-10
16:48:39 UTC (rev 606)
@@ -2,29 +2,27 @@
ALTER TABLE rapport_advanced.listing_param ADD COLUMN lp_paid text;
COMMENT ON COLUMN rapport_advanced.listing_param.lp_paid IS 'used by
Listing_Compute_Historique';
--- Table: rapport_advanced.listing_compute_historique
-
--- DROP TABLE rapport_advanced.listing_compute_historique;
-
CREATE TABLE rapport_advanced.listing_compute_historique
(
lh_id bigserial NOT NULL, -- PK
- ld_id bigint, -- FK to listing_compute_detail
+ lc_id bigint, -- FK to listing_compute_detail
jr_id bigint, -- FK to jrn
CONSTRAINT listing_compute_historique_pkey PRIMARY KEY (lh_id ),
CONSTRAINT listing_compute_historique_jr_id_fkey FOREIGN KEY (jr_id)
REFERENCES jrn (jr_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
- CONSTRAINT listing_compute_historique_ld_id_fkey FOREIGN KEY (ld_id)
+ CONSTRAINT listing_compute_historique_ld_id_fkey FOREIGN KEY (lc_id)
REFERENCES rapport_advanced.listing_compute_detail (ld_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)
WITH (
OIDS=FALSE
);
+ALTER TABLE rapport_advanced.listing_compute_historique
+ OWNER TO phpcompta;
COMMENT ON TABLE rapport_advanced.listing_compute_historique
IS 'Content : history of operation for a detail';
COMMENT ON COLUMN rapport_advanced.listing_compute_historique.lh_id IS 'PK';
-COMMENT ON COLUMN rapport_advanced.listing_compute_historique.ld_id IS 'FK to
listing_compute_detail';
+COMMENT ON COLUMN rapport_advanced.listing_compute_historique.lc_id IS 'FK to
listing_compute_detail';
COMMENT ON COLUMN rapport_advanced.listing_compute_historique.jr_id IS 'FK to
jrn';
---
PhpCompta est un logiciel de comptabilité libre en ligne (full web)
Projet opensource http://www.phpcompta.eu
[Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Phpcompta-dev] r606 - in trunk/rapport_avance: doc include sql,
phpcompta-dev <=