[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpcompta-dev] r4093 - in phpcompta/trunk: doc include sql
|
From: |
phpcompta-dev |
|
Subject: |
[Phpcompta-dev] r4093 - in phpcompta/trunk: doc include sql |
|
Date: |
Sat, 14 May 2011 07:06:11 +0200 (CEST) |
Author: danydb
Date: 2011-05-14 07:06:10 +0200 (Sat, 14 May 2011)
New Revision: 4093
Added:
phpcompta/trunk/include/class_anc_table.php
Modified:
phpcompta/trunk/doc/manuel-fr.odt
phpcompta/trunk/include/anc_imp.inc.php
phpcompta/trunk/include/class_acc_ledger_purchase.php
phpcompta/trunk/include/class_acc_operation.php
phpcompta/trunk/include/class_acc_reconciliation.php
phpcompta/trunk/include/class_anc_balance_double.php
phpcompta/trunk/include/class_anc_operation.php
phpcompta/trunk/include/class_anc_print.php
phpcompta/trunk/sql/upgrade.sql
Log:
Fix bug in ANC print and add 0000251: Liaison compta analytique -> compta
g?\195?\169n?\195?\169rale
Modified: phpcompta/trunk/doc/manuel-fr.odt
===================================================================
(Binary files differ)
Modified: phpcompta/trunk/include/anc_imp.inc.php
===================================================================
--- phpcompta/trunk/include/anc_imp.inc.php 2011-05-14 05:05:07 UTC (rev
4092)
+++ phpcompta/trunk/include/anc_imp.inc.php 2011-05-14 05:06:10 UTC (rev
4093)
@@ -35,7 +35,10 @@
//-- the menu
$menu=array(array("?p_action=ca_imp&sub=listing&$str_dossier",_("Listing"),_("Listing
des opérations"),"listing"),
array("?p_action=ca_imp&sub=bs&$str_dossier",_("Balance
simple"),_("Balance simple d'un plan analytique"),"bs"),
- array("?p_action=ca_imp&sub=bc2&$str_dossier",_("Balance
croisée"),_("Balance croisée de 2 plans analytiques"),"bc2")
+ array("?p_action=ca_imp&sub=bc2&$str_dossier",_("Balance
croisée"),_("Balance croisée de 2 plans analytiques"),"bc2"),
+
array("?p_action=ca_imp&sub=tab&$str_dossier",_("Tableau"),_("Tableau lié à la
comptabilité"),'tab'),
+
array("?p_action=ca_imp&sub=groupe&$str_dossier",_("Groupe"),_("Balance par
groupe"),'gr'),
+
);
$sub=(isset($_GET['sub']))?$_GET['sub']:'no';
@@ -107,4 +110,22 @@
echo $bc->display_html();
}
}
+//----------------------------------------------------------------------
+// Table linked between accountancy and analytic
+//---------------------------------------------------------------------------
+if ( $sub == 'tab')
+ {
+ require_once('class_anc_table.php');
+ $tab=new Anc_Table($cn);
+ $tab->get_request();
+ echo '<form method="get">';
+ echo $tab->display_form($str_hidden);
+ echo '<p>'.HtmlInput::submit('Recherche','Recherche').'</p>';
+ echo '</form>';
+ if ( isset($_GET['result']))
+ {
+ echo $tab->show_button($str_hidden);
+ $tab->display_html();
+ }
+ }
\ No newline at end of file
Modified: phpcompta/trunk/include/class_acc_ledger_purchase.php
===================================================================
--- phpcompta/trunk/include/class_acc_ledger_purchase.php 2011-05-14
05:05:07 UTC (rev 4092)
+++ phpcompta/trunk/include/class_acc_ledger_purchase.php 2011-05-14
05:06:10 UTC (rev 4093)
@@ -490,7 +490,8 @@
$op->oa_group=$group;
$op->j_id=$j_id;
$op->oa_date=$e_date;
- $op->oa_debit=($amount < 0 )?'t':'f';
+ echo $amount;
+ $op->oa_debit=($amount > 0 )?'t':'f';
$op->oa_description=FormatString($e_comm);
$op->save_form_plan($_POST,$i,$j_id);
}
Modified: phpcompta/trunk/include/class_acc_operation.php
===================================================================
--- phpcompta/trunk/include/class_acc_operation.php 2011-05-14 05:05:07 UTC
(rev 4092)
+++ phpcompta/trunk/include/class_acc_operation.php 2011-05-14 05:06:10 UTC
(rev 4093)
@@ -87,7 +87,7 @@
function insert_jrnx()
{
- if ( $this->poste == "") return true;
+ if ( $this->poste == "") return false;
/* for negative amount the operation is reversed */
if ( $this->amount < 0 )
{
Modified: phpcompta/trunk/include/class_acc_reconciliation.php
===================================================================
--- phpcompta/trunk/include/class_acc_reconciliation.php 2011-05-14
05:05:07 UTC (rev 4092)
+++ phpcompta/trunk/include/class_acc_reconciliation.php 2011-05-14
05:06:10 UTC (rev 4093)
@@ -188,13 +188,7 @@
}
/**
address@hidden return array of not-reconciled operation
- address@hidden
- address@hidden
- address@hidden
- address@hidden
- @code
-
- @endcode
+
*/
function get_not_reconciled()
{
Modified: phpcompta/trunk/include/class_anc_balance_double.php
===================================================================
--- phpcompta/trunk/include/class_anc_balance_double.php 2011-05-14
05:05:07 UTC (rev 4092)
+++ phpcompta/trunk/include/class_anc_balance_double.php 2011-05-14
05:06:10 UTC (rev 4093)
@@ -127,18 +127,40 @@
$r.="</table>";
$r.=h2info('Résumé');
- $r.='<table>';
+ $r.='<table class="result">';
+ $r.='<tr>';
+ $r.=th('Po').
+ th('Nom').
+ th('Débit',' style="text-align:right"').
+ th('Crédit','style="text-align:right" ').
+ th('Solde',' style="text-align:right"');
+
$sum=$this->show_sum($array);
+ $tot_cred=0;$tot_deb=0;
foreach ($sum as $row)
{
$r.='<tr>';
$r.='<td>'.$row['poste'].'</td>';
$r.='<td>'.$row['desc'].'</td>';
- $r.='<td>'.nbm($row['debit']).'</td>';
- $r.='<td>'.nbm($row['credit']).'</td>';
+ $r.='<td class="num">'.nbm($row['debit']).'</td>';
+ $r.='<td class="num">'.nbm($row['credit']).'</td>';
+ $diff=bcsub($row['debit'],$row['credit']);
+ $tot_cred=bcadd($tot_cred,$row['credit']);
+ $tot_deb=bcadd($tot_deb,$row['debit']);
+
+ $r.=td(nbm($diff),' class="num" ');
+
$r.='<td>'.$row['dc'].'</td>';
$r.='</tr>';
}
+ $r.=td('');
+ $r.=td('total');
+ $r.=td(nbm($tot_deb),'class="num"');
+ $r.=td(nbm($tot_cred),'class="num"');
+ $solde=bcsub($tot_deb,$tot_cred);
+ $sign=($tot_cred<$tot_deb)?" - ":" + ";
+ $r.=td($sign.nbm($solde),'class="num" style="border:solid 1px
blue;font-weight:bold"');
+ $r.='</tr>';
$r.='</table>';
return $r;
@@ -465,29 +487,8 @@
}
- /*!
- * \brief Set the filter (account_date)
- *
- * \return return the string to add to load
- */
- function set_sql_filter()
- {
- $sql="";
- $and=" and ";
- if ( $this->from != "" )
- {
- $sql.="$and a.oa_date >= to_date('".$this->from."','DD.MM.YYYY')";
- }
- if ( $this->to != "" )
- {
- $sql.=" $and a.oa_date <= to_date('".$this->to."','DD.MM.YYYY')";
- }
- return $sql;
-
- }
-
/*!
* \brief add extra lines with sum of each account
* \param $p_array array returned by load()
@@ -533,8 +534,8 @@
$s=abs($tot_deb-$tot_cred);
$d=($tot_deb>$tot_cred)?'debit':'credit';
$array[]=array('poste'=>$old,'desc'=>$old_desc
-
,'debit'=>$tot_deb,'credit'=>$tot_cred,
-
+ ,'debit'=>$tot_deb,'credit'=>$tot_cred,
+
'solde'=>$s,'dc'=>$d);
Modified: phpcompta/trunk/include/class_anc_operation.php
===================================================================
--- phpcompta/trunk/include/class_anc_operation.php 2011-05-14 05:05:07 UTC
(rev 4092)
+++ phpcompta/trunk/include/class_anc_operation.php 2011-05-14 05:06:10 UTC
(rev 4093)
@@ -480,7 +480,7 @@
$value=new INum();
$value->name="val[".$p_seq."][]";
$value->size=6;
- $value->value=(isset($val[$p_seq][$i]))?$val[$p_seq][$i]:$p_amount;
+
$value->value=abs((isset($val[$p_seq][$i]))?$val[$p_seq][$i]:$p_amount);
$value->readonly=($p_mode==1)?false:true;
$result.='<td>'.$value->input().'</td>';
@@ -549,7 +549,7 @@
$op->po_id=$hplan[$p_item][$e];
$op->oa_group=$this->oa_group;
$op->j_id=$j_id;
- $op->oa_amount=$val[$p_item][$row];
+ $op->oa_amount=abs($val[$p_item][$row]);
$op->oa_debit=$this->oa_debit;
$op->oa_date=$this->oa_date;
Modified: phpcompta/trunk/include/class_anc_print.php
===================================================================
--- phpcompta/trunk/include/class_anc_print.php 2011-05-14 05:05:07 UTC (rev
4092)
+++ phpcompta/trunk/include/class_anc_print.php 2011-05-14 05:06:10 UTC (rev
4093)
@@ -118,7 +118,7 @@
$r.= '<span class="notice">'._('Les dates sont en format
DD.MM.YYYY').'</span>';
$r.=$p_hidden;
- $r.='<span style="padding:5px;margin:5px;border:2px double
blue;display:block;">';
+ $r.='<span style="padding:5px;margin:5px;display:block;">';
$plan=new Anc_Plan($this->db);
$plan_id=new ISelect("pa_id");
$plan_id->value=$this->db->make_array("select pa_id, pa_name from
plan_analytique order by pa_name");
@@ -142,4 +142,27 @@
$r.='</span>';
return $r;
}
+ /*!
+ * \brief Set the filter (account_date)
+ *
+ * \return return the string to add to load
+ */
+
+ function set_sql_filter()
+ {
+ $sql="";
+ $and=" and ";
+ if ( $this->from != "" )
+ {
+ $sql.="$and a.oa_date >= to_date('".$this->from."','DD.MM.YYYY')";
+ }
+ if ( $this->to != "" )
+ {
+ $sql.=" $and a.oa_date <= to_date('".$this->to."','DD.MM.YYYY')";
+ }
+
+ return $sql;
+
+ }
+
}
Added: phpcompta/trunk/include/class_anc_table.php
===================================================================
--- phpcompta/trunk/include/class_anc_table.php (rev 0)
+++ phpcompta/trunk/include/class_anc_table.php 2011-05-14 05:06:10 UTC (rev
4093)
@@ -0,0 +1,242 @@
+<?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
+*/
+/* $Revision$ */
+
+// Copyright Author Dany De Bontridder address@hidden
+
+/*!\file
+ * \brief object to show a table: link between accountancy and analytic
+ */
+require_once('class_anc_print.php');
+
+class Anc_Table extends Anc_Print
+{
+ function __contruct($p_cn)
+ {
+ $this->cn=$p_cn;
+ }
+ function get_request()
+ {
+ parent::get_request();
+ $this->card_poste=HtmlInput::default_value('card_poste',1,$_GET);
+ }
+ function display_form($p_hidden='')
+ {
+ $r=parent::display_form($p_hidden);
+ $icard=new ISelect('card_poste');
+ $icard->value=array(
+ array('value'=>1,'label'=>'Par fiche'),
+ array('value'=>2,'label'=>'Par poste comptable')
+ );
+
+ $icard->selected=$this->card_poste;
+ $r.=$icard->input();
+ return $r;
+ }
+ /**
+ * load the data
+ * does not return anything but give a value to this->aheader and this->arow
+ */
+ function load_card()
+ {
+ $date=$this->set_sql_filter();
+ $date=($date != '')?" $date":'';
+ $sql_from_poste=($this->from_poste!='')?" and po.po_name >=
upper('".Database::escape_string($this->from_poste)."')":'';
+ $sql_to_poste=($this->to_poste!='')?" and po.po_name <=
upper('".Database::escape_string($this->to_poste)."')":'';
+
+ $header="select distinct po_id,po_name from v_table_analytic_card where
+ pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste." order by
po_name";
+ $this->aheader=$this->db->get_array($header,array($this->pa_id));
+
+ $this->arow=$this->db->get_array("select distinct f_id,j_qcode,name from
v_table_analytic_card where
+ pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste." order by
name",array($this->pa_id));
+ $this->sql='select sum_amount from v_table_analytic_card where f_id=$1 and
po_id=$2 and pa_id='.$this->pa_id.' '.$date.$sql_from_poste.$sql_to_poste;
+ }
+ function set_sql_filter()
+ {
+ $sql="";
+ $and=" and ";
+ if ( $this->from != "" )
+ {
+ $sql.="$and oa_date >= to_date('".$this->from."','DD.MM.YYYY')";
+ }
+ if ( $this->to != "" )
+ {
+ $sql.=" $and oa_date <= to_date('".$this->to."','DD.MM.YYYY')";
+ }
+
+ return $sql;
+
+ }
+
+ /**
+ * load the data
+ * does not return anything but give a value to this->aheader and this->arow
+ */
+ function load_poste()
+ {
+ $date=$this->set_sql_filter();
+ $date=($date != '')?" $date":'';
+ $sql_from_poste=($this->from_poste!='')?" and po.po_name >=
upper('".Database::escape_string($this->from_poste)."')":'';
+ $sql_to_poste=($this->to_poste!='')?" and po.po_name <=
upper('".Database::escape_string($this->to_poste)."')":'';
+
+ $header="select distinct po_id,po_name from v_table_analytic_account where
+ pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste." order by
po_name";
+ $this->aheader=$this->db->get_array($header,array($this->pa_id));
+
+ $this->arow=$this->db->get_array("select distinct j_poste,name from
v_table_analytic_account where
+ pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste." order by
j_poste",array($this->pa_id));
+ $this->sql='select sum_amount from v_table_analytic_account where
j_poste=$1 and po_id=$2 and pa_id='.$this->pa_id.'
'.$date.$sql_from_poste.$sql_to_poste;
+ }
+ /**
+ address@hidden display the button export CSV
+ address@hidden $p_hidden is a string containing hidden items
+ address@hidden html string
+ */
+ function show_button($p_hidden)
+ {
+ $r="";
+ $r.= '<form method="GET" action="export.php" style="display:inline">';
+ $r.= HtmlInput::hidden("act","CSV/AncTable");
+ $r.= HtmlInput::hidden("to",$this->to);
+ $r.= HtmlInput::hidden("from",$this->from);
+ $r.= HtmlInput::hidden("pa_id",$this->pa_id);
+ $r.= HtmlInput::hidden("from_poste",$this->from_poste);
+ $r.= HtmlInput::hidden("to_poste",$this->to_poste);
+ $r.= $p_hidden;
+ $r.= dossier::hidden();
+ $r.=HtmlInput::submit('bt_csv',"Export en CSV");
+ $r.= '</form>';
+ return $r;
+ }
+ function display_html()
+ {
+ bcscale(2);
+
+ if ( $this->card_poste=='1')
+ {
+ $this->load_card();
+
+ echo '<table class="result">';
+ echo '<tr>';
+ echo th('Fiche');
+ foreach ($this->aheader as $h)
+ {
+ echo '<th style="text-align:right">'.h($h['po_name']).'</th>';
+ }
+ echo th('Total',' style="text-align:right"');
+ echo '</tr>';
+ /*
+ * Show all the result
+ */
+ $tot_global=0;
+ for ($i=0;$i<count($this->arow);$i++)
+ {
+ echo '<tr>';
+ echo
td(HtmlInput::history_card($this->arow[$i]['f_id'],$this->arow[$i]['j_qcode'].'
'.$this->arow[$i]['name']));
+ $tot_row=0;
+ for ($x=0;$x<count($this->aheader);$x++)
+ {
+
$amount=$this->db->get_value($this->sql,array($this->arow[$i]['f_id'],$this->aheader[$x]['po_id']));
+ if ($amount==null)$amount=0;
+ if ( isset($tot_col[$x]))
+ {
+ $tot_col[$x]=bcadd($tot_col[$x],$amount);
+ }
+ else
+ {
+ $tot_col[$x]=$amount;
+ }
+ echo td(nbm($amount),' class="num" ');
+ $tot_row=bcadd($tot_row,$amount);
+ }
+ echo td(nbm($tot_row),' class="num"');
+ $tot_global=bcadd($tot_global,$tot_row);
+ echo '</tr>';
+
+
+ }
+ echo '<tr>';
+ echo td('Totaux');
+ for ($i=0;$i<count($this->aheader);$i++)
+ {
+ echo td(nbm($tot_col[$i]),' class="num"');
+ }
+ echo td(nbm($tot_global),' class="num"
style="font-size:130%;text-weight:bold;border:solid 1px blue"');
+ echo '</tr>';
+ echo '</table>';
+ }
+ if ( $this->card_poste=='2')
+ {
+ $this->load_poste();
+
+ echo '<table class="result">';
+ echo '<tr>';
+ echo th('poste comptable ');
+ foreach ($this->aheader as $h)
+ {
+ echo '<th style="text-align:right">'.h($h['po_name']).'</th>';
+ }
+ echo th('Total',' style="text-align:right"');
+ echo '</tr>';
+ /*
+ * Show all the result
+ */
+ $tot_global=0;
+ for ($i=0;$i<count($this->arow);$i++)
+ {
+ echo '<tr>';
+ echo
td(HtmlInput::history_account($this->arow[$i]['j_poste'],$this->arow[$i]['j_poste'].'
'.$this->arow[$i]['name']));
+ $tot_row=0;
+ for ($x=0;$x<count($this->aheader);$x++)
+ {
+
$amount=$this->db->get_value($this->sql,array($this->arow[$i]['j_poste'],$this->aheader[$x]['po_id']));
+ if ($amount==null)$amount=0;
+ if ( isset($tot_col[$x]))
+ {
+ $tot_col[$x]=bcadd($tot_col[$x],$amount);
+ }
+ else
+ {
+ $tot_col[$x]=$amount;
+ }
+ echo td(nbm($amount),' class="num" ');
+ $tot_row=bcadd($tot_row,$amount);
+ }
+ echo td(nbm($tot_row),' class="num"');
+ $tot_global=bcadd($tot_global,$tot_row);
+ echo '</tr>';
+
+
+ }
+ echo '<tr>';
+
+ echo td('Totaux');
+ for ($i=0;$i<count($this->aheader);$i++)
+ {
+ echo td(nbm($tot_col[$i]),' class="num"');
+ }
+ echo td(nbm($tot_global),' class="num"
style="font-size:130%;text-weight:bold;border:solid 1px blue"');
+ echo '</tr>';
+ echo '</table>';
+
+ }
+
+ }
+}
\ No newline at end of file
Modified: phpcompta/trunk/sql/upgrade.sql
===================================================================
--- phpcompta/trunk/sql/upgrade.sql 2011-05-14 05:05:07 UTC (rev 4092)
+++ phpcompta/trunk/sql/upgrade.sql 2011-05-14 05:06:10 UTC (rev 4093)
@@ -2,4 +2,45 @@
create unique index qp_j_id on quant_purchase(j_id);
create unique index qf_jr_id on quant_fin(jr_id);
update jrn_def set
jrn_def_code=substr(jrn_def_code,1,1)||substr(jrn_def_code,length(jrn_def_code)-1,length(jrn_def_code));
+alter table operation_analytique drop column pa_id;
+ALTER TABLE operation_analytique ADD CONSTRAINT
operation_analytique_oa_amount_check CHECK (oa_amount >= 0::numeric);
+
+CREATE OR REPLACE VIEW v_table_analytic_card AS
+ SELECT po.po_id, po.pa_id, po.po_name, po.po_description, sum(
+ CASE
+ WHEN operation_analytique.oa_debit = true THEN
operation_analytique.oa_amount * (-1)::numeric
+ ELSE operation_analytique.oa_amount
+ END) AS sum_amount, jrnx.f_id, jrnx.j_qcode, ( SELECT
fiche_detail.ad_value
+ FROM fiche_detail
+ WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id) AS
name
+ FROM operation_analytique
+ JOIN poste_analytique po USING (po_id)
+ JOIN jrnx USING (j_id)
+ GROUP BY po.po_id, po.po_name, po.pa_id, jrnx.f_id, jrnx.j_qcode, ( SELECT
fiche_detail.ad_value
+ FROM fiche_detail
+ WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id),
po.po_description
+ HAVING sum(
+CASE
+ WHEN operation_analytique.oa_debit = true THEN
operation_analytique.oa_amount * (-1)::numeric
+ ELSE operation_analytique.oa_amount
+END) <> 0::numeric;
+
+
+CREATE OR REPLACE VIEW v_table_analytic_account AS
+ SELECT po.po_id, po.pa_id, po.po_name, po.po_description, sum(
+ CASE
+ WHEN operation_analytique.oa_debit = true THEN
operation_analytique.oa_amount * (-1)::numeric
+ ELSE operation_analytique.oa_amount
+ END) AS sum_amount, jrnx.j_poste, tmp_pcmn.pcm_lib AS name
+ FROM operation_analytique
+ JOIN poste_analytique po USING (po_id)
+ JOIN jrnx USING (j_id)
+ JOIN tmp_pcmn ON jrnx.j_poste::text = tmp_pcmn.pcm_val::text
+ GROUP BY po.po_id, po.po_name, po.pa_id, jrnx.j_poste, tmp_pcmn.pcm_lib,
po.po_description
+ HAVING sum(
+CASE
+ WHEN operation_analytique.oa_debit = true THEN
operation_analytique.oa_amount * (-1)::numeric
+ ELSE operation_analytique.oa_amount
+END) <> 0::numeric;
+
commit;
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Phpcompta-dev] r4093 - in phpcompta/trunk: doc include sql,
phpcompta-dev <=