[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Noalyss-commit] [noalyss] 13/13: Task #1092 - Ageing report Balance ag
|
From: |
Dany De Bontridder |
|
Subject: |
[Noalyss-commit] [noalyss] 13/13: Task #1092 - Ageing report Balance agée : Possibilité d'entrer une date pour les opérations entrées après cette date, Dans les fiches, pour un seul client ou fournisseur Dans les catégories de fiches : pour une catégorie de clients ou de fournisseurs ou toutes les fiches Général pour toutes les fiches clients ou fournisseurs. export CSV |
|
Date: |
Sun, 07 Jun 2015 17:57:34 +0000 |
sparkyx pushed a commit to branch master
in repository noalyss.
commit 71a7a723e5f83982449538142c7e7e72a7a3a063
Author: Dany De Bontridder <address@hidden>
Date: Sun Jun 7 19:53:13 2015 +0200
Task #1092 - Ageing report
Balance agée :
Possibilité d'entrer une date pour les opérations entrées après cette date,
Dans les fiches, pour un seul client ou fournisseur
Dans les catégories de fiches : pour une catégorie de clients ou de
fournisseurs ou toutes les fiches
Général pour toutes les fiches clients ou fournisseurs.
export CSV
---
include/balance_age.inc.php | 79 ++++++++++
include/balance_card_ageing.inc.php | 58 +++++++
include/category_card.inc.php | 19 ++-
include/class_balance_age.php | 240 ++++++++++++++++++++++++++++++
include/class_lettering.php | 50 ++++++-
include/export_balance_age_csv.php | 88 +++++++++++
include/fiche.inc.php | 94 ++++++++++--
include/template/balance_aged_result.php | 174 +++++++++++++++++++++
sql/upgrade.sql | 91 +++++++++++-
9 files changed, 873 insertions(+), 20 deletions(-)
diff --git a/include/balance_age.inc.php b/include/balance_age.inc.php
new file mode 100644
index 0000000..ba5a4da
--- /dev/null
+++ b/include/balance_age.inc.php
@@ -0,0 +1,79 @@
+<?php
+/*
+ * This file is part of PhpCompta.
+ *
+ * PhpCompta 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.
+ *
+ * PhpCompta 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 PhpCompta; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+*/
+// Copyright (2014) Author Dany De Bontridder <address@hidden>
+
+if ( ! defined ('ALLOWED') ) die('Appel direct ne sont pas permis');
+
+/**
+ * @file
+ * @brief
+ * @param type $name Descriptionara
+ */
+$date_start=HtmlInput::default_value_get('p_date_start',
'01.01.'.$g_user->get_exercice());
+$w_date_start=new IDate('p_date_start',$date_start);
+$w_select=new ISelect('p_type');
+$w_select->value=array(
+ array('value'=>'C','label'=>_('Client')),
+ array('value'=>'F','label'=>_('Fournisseur'))
+);
+$w_select->selected=HtmlInput::default_value_get('p_type','C');
+
+$w_lettre=new ISelect('p_let');
+$w_lettre->value=array(
+ array('value'=>'let','label'=>_('lettrées et non lettrées')),
+ array('value'=>'unlet','label'=>_('non lettrées'))
+);
+$w_lettre->selected=HtmlInput::default_value_get('p_let','unlet');
+
+?>
+<form method="GET">
+ <?php
+ echo HtmlInput::request_to_hidden(array('gDossier','ac'));
+ ?>
+ <?php printf (_(' Opérations après la date %s qui sont %s
'),$w_date_start->input(),$w_lettre->input())?>
+ <?php echo _("Type de tiers")." ".$w_select->input()?>
+ <?php echo HtmlInput::submit("view", _('Valider'))?>
+</form>
+
+<?php
+ if ( ! isset($_GET['view']) ):
+ html_page_stop();
+ return;
+ endif;
+?>
+<form method="get" action="export.php">
+ <?php
+ echo
HtmlInput::request_to_hidden(array('gDossier','ac','p_type','p_let','p_date_start'));
+ echo HtmlInput::hidden('act','CSV:balance_age');
+ echo HtmlInput::submit('csv',_('export CSV'));
+?>
+</form>
+<?php
+ require_once 'class_balance_age.php';
+ $balance=new Balance_Age($cn);
+ $type=HtmlInput::default_value_get('p_type', 'C');
+ $let=HtmlInput::default_value_get('p_let', 'unlet');
+ $date=HtmlInput::default_value_get('p_date_start', date('d.m.Y'));
+ if ( $type == "C") :
+ $balance->display_sale($date,$let);
+ else:
+ $balance->display_purchase($date,$let);
+ endif;
+
+?>
\ No newline at end of file
diff --git a/include/balance_card_ageing.inc.php
b/include/balance_card_ageing.inc.php
new file mode 100644
index 0000000..9fc6984
--- /dev/null
+++ b/include/balance_card_ageing.inc.php
@@ -0,0 +1,58 @@
+<?php
+/*
+ * This file is part of PhpCompta.
+ *
+ * PhpCompta 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.
+ *
+ * PhpCompta 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 PhpCompta; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+*/
+// Copyright (2014) Author Dany De Bontridder <address@hidden>
+
+if ( ! defined ('ALLOWED') ) die('Appel direct ne sont pas permis');
+
+/**
+ * @file
+ * @brief
+ * @param type $name Descriptionara
+ */
+require_once('class_exercice.php');
+require_once 'class_balance_age.php';
+$let=( isset ($_GET['p_let']))?'let':'unlet';
+
+$export_csv = '<FORM METHOD="get" ACTION="export.php" style="display:inline">';
+$export_csv .=HtmlInput::request_to_hidden(array('gDossier','ac',));
+$export_csv.=HtmlInput::hidden('p_date_start', '01.01.2000');
+$export_csv .= HtmlInput::hidden('act','CSV:balance_age');
+$export_csv .= HtmlInput::hidden('p_let',$let);
+$export_csv .= HtmlInput::hidden('p_type','U');
+$export_csv .= HtmlInput::hidden('fiche',$_GET['f_id']);
+$export_csv .= HtmlInput::submit('csv',_('Export CSV'));
+$export_csv.='</FORM>';
+?>
+<form method="get">
+ <?php echo "Tout" ?><input type="checkbox" name="p_let" value="1">
+ <?php echo
HtmlInput::request_to_hidden(array('ac','gDossier','sb','sc','f_id'));?>
+ <input type="submit" class="smallbutton" value="<?php echo _('Valider')?>">
+</form>
+<?php
+
+echo '<div class="content" style="width:98%;margin-left:1%">';
+echo $export_csv;
+$fiche=new Fiche($cn,$_GET['f_id']);
+$bal=new Balance_Age($cn);
+$bal->display_card('01.01.2000', $fiche->id, $let);
+echo $export_csv;
+
+echo '</div>';
+
+?>
diff --git a/include/category_card.inc.php b/include/category_card.inc.php
index 52c97cc..322cc28 100644
--- a/include/category_card.inc.php
+++ b/include/category_card.inc.php
@@ -48,11 +48,14 @@ case 'op':
$def=4;
break;
case 'let':
- $def=6;
+ $def=7;
break;
case 'bal':
$def=5;
break;
+case 'balag':
+ $def=6;
+ break;
default:
$def=1;
$ss_action='dc';
@@ -66,14 +69,15 @@ $menu = array(
array('href'=>$root.'&sc=sv','label'=>_('Suivi'),'alt'=>_('Suivi Fournisseur,
client, banque, devis, bon de commande, courrier')),
array('href'=>$root.'&sc=cn','label'=>_('Contact'),'alt'=>_('Liste de
contacts')),
array('href'=>$root.'&sc=op','label'=>_('Opérations'),'alt'=>_('Toutes les
opérations')),
-
array('href'=>$root.'&sc=bal','label'=>_('Balance'),'alt'=>_('Balance du
fournisseur')),
+
array('href'=>$root.'&sc=bal','label'=>_('Balance'),'alt'=>_('Balance du
tiers')),
+ array('href'=>$root.'&sc=balag','label'=>_('Balance
âgée'),'alt'=>_('Balance âgée du tiers')),
array('href'=>$root.'&sc=let','label'=>_('Lettrage'),'alt'=>_('Opérations &
Lettrages'))
);
echo '<ul class="tabs">';
for ($i=0;$i<count($menu);$i++) {
$style=($def==($i+1))?"tabs_selected":"tabs";
echo '<li class="'.$style.'">';
- echo '<a href="'.$menu[$i]['href'].'" alt="'.$menu[$i]['alt'].'">';
+ echo '<a href="'.$menu[$i]['href'].'" title="'.$menu[$i]['alt'].'">';
echo h($menu[$i]['label']);
echo '</a>';
echo '</li>';
@@ -112,6 +116,13 @@ if ( $ss_action=='bal')
{
require_once('balance_card.inc.php');
}
+/*-------------------------------------------------------------------------
+ * Ageing Balance of the card
+ *-------------------------------------------------------------------------*/
+if ( $ss_action=='balag')
+ {
+ require_once('balance_card_ageing.inc.php');
+ }
/*----------------------------------------------------------------------
* All the contact
*
@@ -148,7 +159,7 @@ if ( $ss_action == 'cn')
/*----------------------------------------------------------------------------
* Lettering
*----------------------------------------------------------------------------*/
-if ( $def==6 )
+if ( $def==7 )
{
require_once('lettering.gestion.inc.php');
}
diff --git a/include/class_balance_age.php b/include/class_balance_age.php
new file mode 100644
index 0000000..13eddec
--- /dev/null
+++ b/include/class_balance_age.php
@@ -0,0 +1,240 @@
+<?php
+
+/*
+ * Copyright (C) 2015 Dany De Bontridder <address@hidden>
+ *
+ * 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.
+ */
+require_once 'class_lettering.php';
+
+/* * *
+ * @file
+ * @brief compute the ageing balance, currently this code is not used
+ *
+ */
+
+class Balance_Age
+{
+
+ private $cn;
+
+ function __construct($p_cn)
+ {
+ $this->cn=$p_cn;
+ $this->afiche=null;
+ }
+
+ function get_array_card($p_type, $p_extra="")
+ {
+ switch ($p_type)
+ {
+ case 'X':
+ $this->afiche=$this->cn->get_array("
+ with m as (select distinct qp_supplier as f_id from quant_purchase
union select qs_client from quant_sold)
+ select distinct fiche.f_id as f_id ,f1.ad_value as name,
f3.ad_value as first_name,f2.ad_value as quick_code
+ from fiche
+ join m on (fiche.f_id=m.f_id)
+ join fiche_detail as f1 on (fiche.f_id=f1.f_id and
f1.ad_id=1)
+ join fiche_detail as f2 on (fiche.f_id=f2.f_id and
f2.ad_id=23)
+ left join fiche_detail as f3 on (fiche.f_id=f3.f_id and
f3.ad_id=32)
+ where
+ fiche.fd_id=$1
+ order by f1.ad_value
+ ", array($p_extra));
+ break;
+ case 'U':
+ $fiche=new Fiche($this->cn, $p_extra);
+ $this->afiche[0]['f_id']=$fiche->id;
+ $this->afiche[0]['quick_code']=$fiche->get_quick_code();
+ $this->afiche[0]['name']=$fiche->strAttribut(ATTR_DEF_NAME, 0);
+
$this->afiche[0]['first_name']=$fiche->strAttribut(ATTR_DEF_FIRST_NAME, 0);
+ break;
+ case 'F':
+ $this->afiche=$this->cn->get_array("
+ select distinct qp_supplier as f_id ,f1.ad_value as name,
f3.ad_value as first_name,f2.ad_value as quick_code
+ from quant_purchase join
+ fiche_detail as f1 on (qp_supplier=f1.f_id and f1.ad_id=1)
+ join fiche_detail as f2 on (qp_supplier=f2.f_id and
f2.ad_id=23)
+ left join fiche_detail as f3 on (qp_supplier=f3.f_id and
f3.ad_id=32)
+ order by f1.ad_value
+ ");
+ break;
+ case 'C':
+ $this->afiche=$this->cn->get_array("
+ select distinct qs_client as f_id ,f1.ad_value as name,
f3.ad_value as first_name,f2.ad_value as quick_code
+ from quant_sold join
+ fiche_detail as f1 on (qs_client=f1.f_id and f1.ad_id=1)
+ join fiche_detail as f2 on (qs_client=f2.f_id and
f2.ad_id=23)
+ left join fiche_detail as f3 on (qs_client=f3.f_id and
f3.ad_id=32)
+ order by f1.ad_value
+ ");
+ break;
+ default:
+ throw new Exception('Type invalide');
+ }
+ }
+
+ function display_card($p_date_start, $p_fiche, $p_let)
+ {
+ $this->get_array_card('U', $p_fiche);
+ $a_fiche=$this->afiche;
+ $nb_fiche=count($a_fiche);
+ require 'template/balance_aged_result.php';
+ }
+
+ function display_category($p_date_start, $p_cat, $p_let)
+ {
+ // Get all fiche from Purchase
+
+ $this->get_array_card('X', $p_cat);
+ $a_fiche=$this->afiche;
+ $nb_fiche=count($a_fiche);
+ require 'template/balance_aged_result.php';
+ }
+
+ /**
+ * Display all the operation for the customer
+ * @param type $p_date_start min date of the operatin
+ * @param type $p_let 'unlet' only unlettered or 'let' for all
+ */
+ function display_purchase($p_date_start, $p_let)
+ {
+ // Get all fiche from Purchase
+ $this->get_array_card('F');
+ $a_fiche=$this->afiche;
+ $nb_fiche=count($a_fiche);
+ require 'template/balance_aged_result.php';
+ }
+
+ /**
+ * Display all the operation for the supplier
+ * @param type $p_date_start min date of the operatin
+ * @param type $p_let 'unlet' only unlettered or 'let' for all
+ */
+ function display_sale($p_date_start, $p_let)
+ {
+ // Get all fiche from Purchase
+ $this->get_array_card('C');
+ $a_fiche=$this->afiche;
+ $nb_fiche=count($a_fiche);
+ require 'template/balance_aged_result.php';
+ }
+
+ function export_csv($p_date_start, $p_let)
+ {
+ $nb_fiche=count($this->afiche);
+ $title=sprintf('"%s";', _('QuickCode'));
+ $title.=sprintf('"%s";', _('Nom'));
+ $title.=sprintf('"%s";', _('Prénom'));
+ $title.=sprintf('"%s";', _('Date'));
+ $title.=sprintf('"%s";', _('N° pièce'));
+ $title.=sprintf('"%s";', _('Interne'));
+ $title.=sprintf('"%s";', _('Fin'));
+ $title.=sprintf('"%s";', _('<30 jours'));
+ $title.=sprintf('"%s";', _('entre 30 et 60 jours'));
+ $title.=sprintf('"%s";', _('entre 60 et 90 jours'));
+ $title.=sprintf('"%s";', _('> 90 jours'));
+ $title.=sprintf("\n\r");
+ $flag_title=false;
+ for ($i=0; $i<$nb_fiche; $i++)
+ {
+ $card=new Lettering_Card($this->cn,
$this->afiche[$i]['quick_code']);
+ $card->set_parameter('start', $p_date_start);
+ $card->get_balance_ageing($p_let);
+ if (empty($card->content))
+ continue;
+ if ( ! $flag_title ) { echo $title;$flag_title=true;}
+ $nb_row=count($card->content);
+ $sum_lt_30=0;
+ $sum_gt_30_lt_60=0;
+ $sum_gt_60_lt_90=0;
+ $sum_gt_90=0;
+ $sum_fin=0;
+ for ($j=0; $j<$nb_row; $j++)
+ {
+ $show=true;
+ printf('"%s";', str_replace('"', '',
$this->afiche[$i]['quick_code']));
+ printf('"%s";', str_replace('"', '',
$this->afiche[$i]['name']));
+ printf('"%s";', str_replace('"', '',
$this->afiche[$i]['first_name']));
+ printf('"%s";', $card->content[$j]['j_date_fmt']);
+ printf('"%s";', $card->content[$j]['jr_pj_number']);
+ printf('"%s";', $card->content[$j]['jr_internal']);
+ if
($card->content[$j]['jrn_def_type']=='FIN'||$card->content[$j]['jrn_def_type']=='ODS')
+ {
+ printf("%s;", nb($card->content[$j]['j_montant']));
+ $sum_fin=bcadd($sum_fin, $card->content[$j]['j_montant']);
+ $show=false;
+ }
+ else
+ {
+ printf('0;');
+ }
+ if ($show&&$card->content[$j]['day_paid']<=30)
+ {
+ printf("%s;", nb($card->content[$j]['j_montant']));
+ $sum_lt_30=bcadd($sum_lt_30,
$card->content[$j]['j_montant']);
+ $show=false;
+ }
+ else
+ {
+ printf('0;');
+ }
+
+ if
($show&&$card->content[$j]['day_paid']>30&&$card->content[$j]['day_paid']<=60)
+ {
+ printf("%s;", nb($card->content[$j]['j_montant']));
+ $sum_gt_30_lt_60=bcadd($sum_gt_30_lt_60,
$card->content[$j]['j_montant']);
+ }
+ else
+ {
+ printf('0;');
+ }
+
+ if
($show&&$card->content[$j]['day_paid']>60&&$card->content[$j]['day_paid']<=90)
+ {
+ printf("%s;", nb($card->content[$j]['j_montant']));
+ $sum_gt_60_lt_90=bcadd($sum_gt_60_lt_90,
$card->content[$j]['j_montant']);
+ }
+ else
+ {
+ printf('0;');
+ }
+ if ($show&&$card->content[$j]['day_paid']>90)
+ {
+ printf("%s", nb($card->content[$j]['j_montant']));
+ $sum_gt_90=bcadd($sum_gt_90,
$card->content[$j]['j_montant']);
+ }
+ else
+ {
+ printf('0;');
+ }
+ printf("\n\r");
+ }
+ printf('"%s";', _('Totaux'));
+ printf('"";');
+ printf('"";');
+ printf('"";');
+ printf('"";');
+ printf('"";');
+ printf('%s;', nb($sum_fin));
+ printf('%s;', nb($sum_lt_30));
+ printf('%s;', nb($sum_gt_30_lt_60));
+ printf('%s;', nb($sum_gt_60_lt_90));
+ printf('%s', nb($sum_gt_90));
+ printf("\n\r");
+ }
+ }
+
+}
diff --git a/include/class_lettering.php b/include/class_lettering.php
index 131f138..073ab8d 100644
--- a/include/class_lettering.php
+++ b/include/class_lettering.php
@@ -708,5 +708,53 @@ class Lettering_Card extends Lettering
order by j_date,j_id";
$this->content=$this->db->get_array($sql,array($this->quick_code,$this->start,$this->end));
}
-
+ /**
+ * fill $this->content with the rows from this query
+ * Columns are
+ * - j_id, id of jrnx
+ * - j_date, date opeation (yyyy.mm.dd)
+ * - to_char(j_date,'DD.MM.YYYY') as j_date_fmt,
+ * - jr_pj_number, receipt number
+ * - j_montant, amount of the rows
+ * - j_debit, Debit or credit
+ * - jr_comment, label of the operation
+ * - jr_internal, internal number
+ * - jr_id, id of jrn
+ * - jr_def_id, id of the ledger (jrn_def.jrn_def_id)
+ * - coalesce(let_diff.jl_id,-1) as letter, id of the lettering , -1
means unlettered
+ * - diff_letter1 as letter_diff, delta between lettered operation
+ * - extract ('days' from
coalesce(jr_date_paid,now())-coalesce(jr_ech,jr_date)) as day_paid, days
between operation and payment
+ * - jd1.jrn_def_type type of the ledger (FIN, ODS,VEN or ACH)
+ *
+ *
+ * @param type $p_type value is unlet for unlettered operation or let for
everything
+ */
+ public function get_balance_ageing($p_type)
+ {
+ $sql_let = ($p_type =='unlet')?' let_diff.jl_id is null and':'';
+ $sql =
+ " with let_diff as (select jl_id,deb_amount-cred_amount as
diff_letter1
+ from
+ ( select jl_id,coalesce(sum(j_montant),0) as
cred_amount from letter_cred join jrnx using (j_id) group by jl_id) as CRED
+ left join (select jl_id,coalesce(sum(j_montant),0) as
deb_amount from letter_deb join jrnx using (j_id) group by jl_id) as DEB using
(jl_id)) ,
+ letter_jl as (select jl_id,j_id from letter_cred union
all select jl_id,j_id from letter_deb)
+ select DISTINCT j_id,j_date,to_char(j_date,'DD.MM.YYYY') as
j_date_fmt,jr_pj_number,
+
j_montant,j_debit,jr_comment,jr_internal,jr_id,jr_def_id,
+
coalesce(let_diff.jl_id,-1) as letter,
+ diff_letter1
as letter_diff,
+ extract
('days' from coalesce(jr_date_paid,now())-coalesce(jr_ech,jr_date)) as day_paid,
+
jd1.jrn_def_type
+ from jrnx join
jrn on (j_grpt = jr_grpt_id)
+ join jrn_def
as jd1 on (jrn.jr_def_id=jd1.jrn_def_id)
+ left join
letter_jl using (j_id)
+ left join
let_diff using (jl_id)
+ where
+ {$sql_let}
+ j_qcode = upper($1)
+ and j_date >= to_date($2,'DD.MM.YYYY')
+ and {$this->sql_ledger}
+ order by j_date,j_id";
+
$this->content=$this->db->get_array($sql,array($this->quick_code,$this->start));
+
+ }
}
diff --git a/include/export_balance_age_csv.php
b/include/export_balance_age_csv.php
new file mode 100644
index 0000000..cac21b3
--- /dev/null
+++ b/include/export_balance_age_csv.php
@@ -0,0 +1,88 @@
+<?php
+
+/*
+ * This file is part of PhpCompta.
+ *
+ * PhpCompta 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.
+ *
+ * PhpCompta 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 PhpCompta; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+ */
+// Copyright (2014) Author Dany De Bontridder <address@hidden>
+
+if (!defined('ALLOWED'))
+ die('Appel direct ne sont pas permis');
+require 'class_balance_age.php';
+header('Pragma: public');
+header('Content-type: application/csv');
+header('Content-Disposition: attachment;filename="balance_age.csv"',FALSE);
+/**
+ * @file
+ * @brief Export an ageing balance
+ * @param p_date_start considered writing after this date
+ * @param p_let lettered or not value (all): let or only unlettered
value:unlet
+ * @param p_type
+ * - C customer
+ * - F supplier
+ * - U only a card
+ * - X a category
+ * @param cat only if p_type = X it that case contains the category id
(fiche_def.fd_id)
+ * @param fiche only if p_type = U in that case contains the card id
(fiche.f_id)
+ * @param all Ony with p_type = X all the customer / supplier cards
+ */
+/*
+ * Retrieve card
+ */
+$bal=new Balance_Age($cn);
+$p_type = HtmlInput::default_value_get('p_type', "-");
+$p_date= HtmlInput::default_value_get('p_date_start', "-");
+$p_let= HtmlInput::default_value_get('p_let', "let");
+$cat= HtmlInput::default_value_get('cat', "");
+$fiche= HtmlInput::default_value_get('fiche', "0");
+$all= HtmlInput::default_value_get('all', "0");
+switch ($p_type)
+{
+ case 'C':
+ $bal->get_array_card('C');
+ $bal->export_csv($p_date, $p_let);
+ break;
+ case 'F':
+ $bal->get_array_card('F');
+ $bal->export_csv($p_date, $p_let);
+ break;
+ case 'U':
+ $bal->get_array_card('U', $fiche);
+ $bal->export_csv($p_date, $p_let);
+ break;
+ case 'X':
+ $all=HtmlInput::default_value_get('all', 0);
+ if ($all==0)
+ {
+ $bal->get_array_card('X', $_GET['cat']);
+ $bal->export_csv($p_date, $p_let);
+ }
+ else
+ {
+ $a_cat=$cn->get_array("select fd_id from vw_fiche_def where
ad_id=".ATTR_DEF_ACCOUNT." order by fd_label asc");
+ $nb_cat=count($a_cat);
+ for ($i=0; $i<$nb_cat; $i++)
+ {
+ $bal->get_array_card('X', $a_cat[$i]['fd_id']);
+ $bal->export_csv($p_date, $p_let);
+ }
+ }
+ break;
+
+ default:
+ break;
+}
+?>
diff --git a/include/fiche.inc.php b/include/fiche.inc.php
index 541e16e..058294d 100644
--- a/include/fiche.inc.php
+++ b/include/fiche.inc.php
@@ -67,6 +67,8 @@ $histo->value = array(
array('value' => 2, 'label' => _('Historique non Lettré')),
array('value' => 3, 'label' => _('Résumé')),
array('value' => 4, 'label' => _('Balance')),
+ array('value' => 6, 'label' => _('Balance âgée')),
+ array('value' => 7, 'label' => _('Balance âgée en-cours')),
array('value' => 5, 'label' => _('Balance non soldée'))
);
$histo->javascript = 'onchange="if (this.value==3 || this.value==-1) {
@@ -266,19 +268,90 @@
$export_csv.=HtmlInput::request_to_hidden(array('allcard'));
$export_csv.=dossier::hidden();
$export_csv.=HtmlInput::submit('CSV', 'Export en CSV');
$export_csv.='</FORM>';
-
+if (isDate($_REQUEST['start']) == null || isDate($_REQUEST['end']) == null)
+{
+ echo h2('Date invalide !', 'class="error"');
+ alert('Date invalide !');
+ return;
+}
+/*************************************************************************************************************************
+ * Balance agée tous
+/*************************************************************************************************************************/
+if ( $_GET['histo'] == 6)
+{
+ require_once 'class_balance_age.php';
+ $bal=new Balance_Age($cn);
+ $export_csv = '<FORM METHOD="get" ACTION="export.php"
style="display:inline">';
+ $export_csv
.=HtmlInput::request_to_hidden(array('gDossier','ac','p_let','p_date_start'));
+ $export_csv.=HtmlInput::hidden('p_date_start', $_GET['start']);
+ $export_csv .= HtmlInput::hidden('act','CSV:balance_age');
+ $export_csv .= HtmlInput::hidden('p_let','let');
+ $export_csv .= HtmlInput::hidden('p_type','X');
+ $export_csv .= HtmlInput::hidden('cat',$_GET['cat']);
+ $export_csv .= HtmlInput::hidden('all',$allcard);
+ $export_csv .= HtmlInput::submit('csv',_('Export CSV'));
+ $export_csv.='</FORM><p></p>';
+ if ( $allcard == 0 )
+ {
+ echo $export_csv;
+ $bal->display_category($_GET['start'],$_GET['cat'],'let');
+ echo $export_csv;
+ }
+ else
+ {
+ echo $export_csv;
+ $a_cat = $cn->get_array("select fd_id from vw_fiche_def where ad_id="
. ATTR_DEF_ACCOUNT . " order by fd_label asc");
+ $nb_cat=count($a_cat);
+ for ($i=0;$i < $nb_cat;$i++)
+ {
+ $bal->display_category($_GET['start'],$a_cat[$i]['fd_id'],'let');
+ }
+ echo $export_csv;
+ }
+ return;
+}
+/*************************************************************************************************************************
+ * Balance en-cours
+/*************************************************************************************************************************/
+if ( $_GET['histo'] == 7)
+{
+ require_once 'class_balance_age.php';
+ $bal=new Balance_Age($cn);
+ $export_csv = '<FORM METHOD="get" ACTION="export.php"
style="display:inline">';
+ $export_csv
.=HtmlInput::request_to_hidden(array('gDossier','ac','p_let','p_date_start'));
+ $export_csv.=HtmlInput::hidden('p_date_start', $_GET['start']);
+ $export_csv .= HtmlInput::hidden('act','CSV:balance_age');
+ $export_csv .= HtmlInput::hidden('p_let','unlet');
+ $export_csv .= HtmlInput::hidden('p_type','X');
+ $export_csv .= HtmlInput::hidden('cat',$_GET['cat']);
+ $export_csv .= HtmlInput::hidden('all',$allcard);
+ $export_csv .= HtmlInput::submit('csv',_('Export CSV'));
+ $export_csv.='</FORM><p></p>';
+ if ( $allcard == 0 )
+ {
+ echo $export_csv;
+ $bal->display_category($_GET['start'],$_GET['cat'],'unlet');
+ echo $export_csv;
+ }
+ else
+ {
+ echo $export_csv;
+ $a_cat = $cn->get_array("select fd_id from vw_fiche_def where ad_id="
. ATTR_DEF_ACCOUNT . " order by fd_label asc");
+ $nb_cat=count($a_cat);
+ for ($i=0;$i < $nb_cat;$i++)
+ {
+
$bal->display_category($_GET['start'],$a_cat[$i]['fd_id'],'unlet');
+ }
+ echo $export_csv;
+ }
+ return;
+}
/********************************************************************************************************************************
* Balance
*
**********************************************************************************************************************************/
if ($_GET['histo'] == 4 || $_GET['histo'] == 5)
{
- if (isDate($_REQUEST['start']) == null || isDate($_REQUEST['end']) ==
null)
- {
- echo h2(_('Date invalide !'), 'class="error"');
- alert(_('Date invalide !'));
- return;
- }
if ( $allcard == 0 ) echo $str_add_card;
echo $export_pdf;
echo $export_csv;
@@ -375,12 +448,7 @@ if ($_GET['histo'] == 4 || $_GET['histo'] == 5)
return;
}
-if (isDate($_REQUEST['start']) == null || isDate($_REQUEST['end']) == null)
-{
- echo h2('Date invalide !', 'class="error"');
- alert('Date invalide !');
- return;
-}
+
/***********************************************************************************************************************************
* Lettering
*
diff --git a/include/template/balance_aged_result.php
b/include/template/balance_aged_result.php
new file mode 100644
index 0000000..bc024ef
--- /dev/null
+++ b/include/template/balance_aged_result.php
@@ -0,0 +1,174 @@
+<?php
+/*
+ * * Copyright (C) 2015 Dany De Bontridder <address@hidden>
+*
+* 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.
+
+ *
+ */
+
+
+/**
+ * @file
+ * @brief show the result of balance ageing, included from
Balance_Age::display_purchase
+ * @see Balance_Age
+ */
+bcscale(2);
+?>
+
+<?php
+ $nb_fiche=count($a_fiche);
+ for ($i=0;$i<$nb_fiche;$i++):
+ $card = new Lettering_Card($this->cn,$a_fiche[$i]['quick_code']);
+ $card->set_parameter('start', $p_date_start);
+ $card->get_balance_ageing($p_let);
+ if ( empty ($card->content)) continue;
+?>
+<?php echo
HtmlInput::card_detail($a_fiche[$i]['quick_code'],h($a_fiche[$i]['name']).' '.
h($a_fiche[$i]['first_name']));
+?>
+<table class="result">
+ <tr>
+ <th>
+ <?php echo _('Date opération');?>
+ </th>
+ <th>
+ <?php echo _('Pièce');?>
+ </th>
+ <th>
+ <?php echo _('Libellé');?>
+ </th>
+ <th>
+ <?php echo _('Interne');?>
+ </th>
+ <th>
+ <?php echo _('Fin/ OD');?>
+ </th>
+ <th>
+ <?php echo _('< 30 jours');?>
+ </th>
+ <th>
+ <?php echo _('entre 31 et 60 jours');?>
+ </th>
+ <th>
+ <?php echo _('entre 61 et 90 jours');?>
+ </th>
+ <th>
+ <?php echo _('> 90 jours');?>
+ </th>
+ </tr>
+ <?php
+ $nb_row=count($card->content);
+ $sum_lt_30=0;
+ $sum_gt_30_lt_60=0;
+ $sum_gt_60_lt_90=0;
+ $sum_gt_90=0;
+ $sum_fin=0;
+ for ($j=0;$j < $nb_row;$j++):
+
+ $class=($j%2 == 0)?'even':'odd';
+ $show=true;
+ ?>
+ <tr class="<?php echo $class;?>">
+ <td>
+ <?php echo $card->content[$j]['j_date_fmt'] ?>
+ </td>
+ <td>
+ <?php echo HtmlInput::detail_op($card->content[$j]['jr_id'],
$card->content[$j]['jr_pj_number']) ?>
+ </td>
+ <td>
+ <?php echo $card->content[$j]['jr_comment'] ?>
+ </td>
+ <td>
+ <?php echo
HtmlInput::detail_op($card->content[$j]['jr_id'],$card->content[$j]['jr_internal'])
?>
+ </td>
+ <td style="text-align: right">
+ <?php
+ if ( $card->content[$j]['jrn_def_type'] == 'FIN' ||
$card->content[$j]['jrn_def_type'] == 'ODS') :
+ echo nbm($card->content[$j]['j_montant']);
+ $sum_fin=bcadd($sum_fin,$card->content[$j]['j_montant']);
+ $show=false;
+ endif;
+ ?>
+ </td>
+ <td style="text-align: right">
+ <?php
+ if ($show && $card->content[$j]['day_paid'] <= 30) :
+ echo nbm($card->content[$j]['j_montant']);
+
$sum_lt_30=bcadd($sum_lt_30,$card->content[$j]['j_montant']);
+ endif;
+ ?>
+ </td>
+ <td style="text-align: right">
+ <?php
+ if ( $show &&$card->content[$j]['day_paid'] > 30 &&
$card->content[$j]['day_paid'] <= 60) :
+ echo nbm($card->content[$j]['j_montant']);
+
$sum_gt_30_lt_60=bcadd($sum_gt_30_lt_60,$card->content[$j]['j_montant']);
+ endif;
+ ?>
+ </td>
+ <td style="text-align: right">
+ <?php
+ if ( $show && $card->content[$j]['day_paid'] > 60 &&
$card->content[$j]['day_paid'] <= 90) :
+ echo nbm($card->content[$j]['j_montant']);
+
$sum_gt_60_lt_90=bcadd($sum_gt_60_lt_90,$card->content[$j]['j_montant']);
+ endif;
+ ?>
+ </td>
+ <td style="text-align: right">
+ <?php
+ if ($show && $card->content[$j]['day_paid'] > 90) :
+ echo nbm($card->content[$j]['j_montant']);
+
$sum_gt_90=bcadd($sum_gt_90,$card->content[$j]['j_montant']);
+
+ endif;
+ ?>
+ </td>
+ </tr>
+ <?php
+ endfor;
+ ?>
+ <tr class="highlight">
+ <td>
+
+ </td>
+ <td>
+
+ </td>
+ <td>
+
+ </td>
+ <td>
+
+ </td>
+ <td style="text-align: right">
+ <?php echo nbm($sum_fin);?>
+ </td>
+ <td style="text-align: right">
+ <?php echo nbm($sum_lt_30);?>
+ </td>
+ <td style="text-align: right">
+ <?php echo nbm($sum_gt_30_lt_60);?>
+ </td>
+ <td style="text-align: right">
+ <?php echo nbm($sum_gt_60_lt_90);?>
+ </td>
+ <td style="text-align: right">
+ <?php echo nbm($sum_gt_90);?>
+ </td>
+ </tr>
+</table>
+<?php
+ endfor;
+?>
diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index e9e1326..c7117ac 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -7,7 +7,7 @@ INSERT INTO action(ac_id, ac_description, ac_module, ac_code)
INSERT INTO action(ac_id, ac_description, ac_module, ac_code)
VALUES (1210, 'Partager une note', 'note', 'SHARENOTE');
INSERT INTO action(ac_id, ac_description, ac_module, ac_code)
- VALUES (1220, 'Partager une note avec tout le monde ', 'note',
'SHARENOTEPUBLIC');
+ VALUES (1220, 'Créer une note publique', 'note', 'SHARENOTEPUBLIC');
INSERT INTO action(ac_id, ac_description, ac_module, ac_code)
VALUES (1230, 'Effacer une note publique', 'note', 'SHARENOTEREMOVE');
@@ -46,4 +46,91 @@ Arbre dépendance
) select * from t;
*/
-update menu_ref set me_menu = me_menu||' <span
id="menu_'||lower(me_code)||'"><img src="image/empty.gif"></span>' where
me_type='ME';
+-- update menu_ref set me_menu = me_menu||' <span
id="menu_'||lower(me_code)||'"><img src="image/empty.gif"></span>' where
me_type='ME';
+update menu_ref set me_menu = 'Favori ★ ' where me_code='BOOKMARK';
+update menu_ref set me_menu = 'Sortie ⎆' where me_code='LOGOUT';
+
+insert into menu_ref(me_code,me_menu,me_file,
me_url,me_description,me_parameter,me_javascript,me_type,me_description_etendue)
+values
+('BALAGE','Balance agée','balance_age.inc.php',null,'Balance
agée',null,null,'ME','Balance agée pour les clients et fournisseurs') ,
+('CSV:balance_age','Export Balance
agée','export_balance_age_csv.php',null,'Balance agée',null,null,'PR','Balance
agée pour les clients et fournisseurs')
+
+;
+
+insert into profile_menu (me_code,me_code_dep,p_id,p_order,
p_type_display,pm_default)
+values
+('BALAGE','PRINT',1,550,'M',0),('BALAGE','PRINT',2,550,'M',0),
+('CSV:balance_age',null,1,null,'P',0),('CSV:balance_age',null,2,null,'P',0)
+;
+
+
+/*
+with m as (
+ select jr_id,jr_grpt_id,
+ coalesce(jr_ech,jr_date) as op_date ,
+ jr_date_paid from jrn
+ where jr_date_paid is not null
+),n as (
+ select jr_id ,jr_date_paid - op_date as
delta,jr_grpt_id,jr_date_paid,op_date
+ from m
+ where
+ jr_date_paid - op_date < 30
+ ),solde as (
+select sum(qp_price+qp_vat+qp_nd_amount+qp_nd_tva+qp_nd_tva_recup -
qp_vat_sided),
+ qp_supplier
+from quant_purchase
+ join jrnx using (j_id)
+ join n on (j_grpt=n.jr_grpt_id)
+group by qp_supplier)
+select * ,
+ (select vw_name from vw_fiche_attr where f_id=qp_supplier) ,
+ (select vw_first_name from vw_fiche_attr where f_id=qp_supplier) ,
+(select quick_code from vw_fiche_attr where f_id=qp_supplier)
+from solde
+;
+*/
+/*
+CREATE TABLE tmp_bal_aged (
+ id SERIAL NOT NULL,
+ create_on timestamp default now(),
+ PRIMARY KEY (id));
+COMMENT ON TABLE tmp_bal_aged IS 'Table temporaire pour le calcul des balances
agées';
+
+CREATE TABLE tmp_bal_aged_child (
+ tmp_bal_agedid bigint NOT NULL,
+ id SERIAL NOT NULL,
+ f_id bigint NOT NULL,
+ amount numeric(20,4) NOT NULL,
+ amount30 numeric(20,4) NOT NULL,
+ amount60 numeric(20,4) NOT NULL,
+ amount90 numeric(20,4) NOT NULL,
+ PRIMARY KEY (id));
+COMMENT ON TABLE tmp_bal_aged_child IS 'Table temporaire pour le calcul des
balances agées';
+*/
+CREATE TABLE tmp_bal_aged (
+ id SERIAL NOT NULL,
+ create_on timestamp default now(),
+ PRIMARY KEY (id));
+COMMENT ON TABLE tmp_bal_aged IS 'Table temporaire pour le calcul des balances
agées';
+
+CREATE TABLE tmp_bal_aged_child
+(
+ id serial primary key,
+ j_id bigint,
+ j_date date,
+ j_date_fmt text,
+ jr_pj_number text,
+ j_montant numeric(20,4),
+ j_debit boolean,
+ jr_comment text,
+ jr_internal text,
+ jr_id integer,
+ jr_def_id integer,
+ letter bigint,
+ letter_diff numeric,
+ date_part double precision,
+ tmp_bal_agedid bigint NOT NULL,
+
+);
+COMMENT ON TABLE tmp_bal_aged_child IS 'Table temporaire pour le calcul des
balances agées';
+
- [Noalyss-commit] [noalyss] 03/13: Cosmetic, (continued)
- [Noalyss-commit] [noalyss] 03/13: Cosmetic, Dany De Bontridder, 2015/06/07
- [Noalyss-commit] [noalyss] 04/13: comment, Dany De Bontridder, 2015/06/07
- [Noalyss-commit] [noalyss] 05/13: Fix bug in todo list, add an empty table if nothing, Dany De Bontridder, 2015/06/07
- [Noalyss-commit] [noalyss] 07/13: Calendar : zoom and list mode, fix height, Dany De Bontridder, 2015/06/07
- [Noalyss-commit] [noalyss] 06/13: Calendar : zoom and list mode, Dany De Bontridder, 2015/06/07
- [Noalyss-commit] [noalyss] 08/13: Calendar : zoom and list mode, fix height, Dany De Bontridder, 2015/06/07
- [Noalyss-commit] [noalyss] 09/13: Calendar : highlight late action, Dany De Bontridder, 2015/06/07
- [Noalyss-commit] [noalyss] 11/13: Documentation, Dany De Bontridder, 2015/06/07
- [Noalyss-commit] [noalyss] 12/13: Esthétique, Dany De Bontridder, 2015/06/07
- [Noalyss-commit] [noalyss] 10/13: remove style for icon in menu, Dany De Bontridder, 2015/06/07
- [Noalyss-commit] [noalyss] 13/13: Task #1092 - Ageing report Balance agée : Possibilité d'entrer une date pour les opérations entrées après cette date, Dans les fiches, pour un seul client ou fournisseur Dans les catégories de fiches : pour une catégorie de clients ou de fournisseurs ou toutes les fiches Général pour toutes les fiches clients ou fournisseurs. export CSV,
Dany De Bontridder <=