noalyss Version-9
verif_bilan.inc.php
Go to the documentation of this file.
1<?php
2/*
3 * This file is part of NOALYSS.
4 *
5 * NOALYSS is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2 of the License, or
8 * (at your option) any later version.
9 *
10 * NOALYSS is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with NOALYSS; if not, write to the Free Software
17 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18*/
19
20// Copyright Author Dany De Bontridder danydb@aevalys.eu
21
22/*!\file
23 * \brief Verify the saldo of ledger: independant file
24 */
25if ( ! defined ('ALLOWED') ) die('Appel direct ne sont pas permis');
26
27
30$exercice=$g_user->get_exercice();
31echo '<div class="content">';
32$sql_year=" j_tech_per in (select p_id from parm_periode where p_exercice='".$g_user->get_exercice()."')";
33echo '<div class="myfieldset"><h1 class="legend">'._('Vérification des journaux').'</h1>';
34
36 with jdebit as (
37 select sum (j_montant) as amount,j_debit , jr_def_id
38 from jrnx join jrn on (j_grpt=jr_grpt_id)
39 where
40 $sql_year
41 and
42 j_debit='t'
43 group by jr_def_id,j_debit
44 ),
45jcredit as (
46 select sum (j_montant) as amount,j_debit , jr_def_id
47 from jrnx join jrn on (j_grpt=jr_grpt_id)
48 where
49 $sql_year
50 and
51 j_debit='f'
52 group by jr_def_id,j_debit
53 )
54select jrn_def_id,
55 jrn_def_name,
56 jdebit.amount as deb,
57 jcredit.amount as cred
58 from jrn_def
59join jdebit on (Jdebit.jr_def_id=jrn_def.jrn_def_id)
60join jcredit on (jcredit.jr_def_id=jrn_def.jrn_def_id)
61where jcredit.amount<>jdebit.amount
62order by jrn_def_name
63 ";
64$res=$cn->exec_sql($sql);
66
67$nb_jrn= ($jrn == FALSE) ?0 :count($jrn);
68if ( $jrn ===false ) {
69 echo $g_succeed." "._("Aucune anomalie dans les montants des journaux");
70}
71echo '<table class="result">';
72echo tr(th(_('Journal')).th(_('Débit'),' style="display:right"').th(_("Crédit"),' style="display:right"').th(_("Différence"),' style="display:right"').th(''));
73
75for ($i=0;$i<$nb_jrn;$i++)
76{
77 $l=$jrn[$i];
78 $id=$l['jrn_def_id'];
79 $name=$l['jrn_def_name'];
80 $deb=$l['deb'];
81 $cred=$l['cred'];
83 $class=($ix%2==0)?'odd':"even";
84 print tr(td($name).td(nbm($deb),'class="num"').td(nbm($cred),'class="num"').td(nbm($result),'class="num"').td($result),"class=\"$class\"");
85 $ix++;
86
87}
88
89$deb=$cn->get_value("select sum (j_montant) from jrnx where j_debit='t' and $sql_year ");
90$cred=$cn->get_value("select sum (j_montant) from jrnx where j_debit='f' and $sql_year ");
91
92if ( $cred == $deb )
93{
95}
96else
97{
99}
100$class=($ix%2==0)?'odd':"even";
101print tr(td(_('Grand livre')).td(nbm($deb),' class="num"').td(nbm($cred),' class="num"').td(nbm($result),' class="num"')
102 .td($result),"class=\"$class\"");
103
104echo '</table>';
105echo '</div>';
106echo '<hr>';
107echo '<div class="myfieldset"><h1 class="legend">'._('Vérification des comptes').'</h1>';
112if ( $g_parameter->MY_REPORT=="Y") {
113 $bilan->from=$start_periode->p_id;
114 $bilan->to=$end_periode->p_id;
115
116} else {
117 $first_periode=$cn->get_value("select to_char(p_start,'YYYY-MM-DD') from parm_periode order by p_start limit 1");
118 $bilan->from=format_date($first_periode,'YYYY-MM-DD','DD.MM.YYYY');
119 $bilan->to=$end_periode->last_day();
120}
121$bilan->verify();
122echo '</div>';
123?>
124<hr>
125<div class="myfieldset">
126 <h1 class="legend">
127 <?php echo _("Vérification des fiches").'</legend>';?>
128 </h1>
129 <h2>
130 <?php echo _('Fiches ayant changé de poste comptable');?>
131 </h2>
132 <?php
133 $sql_year_target=" j_tech_per in (select p_id from parm_periode where p_exercice='".$g_user->get_exercice()."')";
134
136 select count(*),f_id from (
137 select distinct
138 f_id,j_poste
139 from jrnx
140 where
141 $sql_year_target
142) as m
143group by f_id
144having count(*) > 1
145 ";
146
148
149 $sql_poste="select distinct j_poste,pcm_lib from jrnx join tmp_pcmn on (pcm_val=j_poste) where f_id =$1 and $sql_year";
150 $sql_qcode="select ad_value as qcode from fiche_detail where f_id=$1 and ad_id=".ATTR_DEF_QUICKCODE;
151 $res=$cn->prepare('get_poste',$sql_poste);
152 $resQcode=$cn->prepare('get_qcode',$sql_qcode);
153 if ( $res == false || $resQcode == false ) {
154 echo "ERREUR ".__FILE__.":".__LINE__."prepare failed";
155 }
156 echo _("Résultat");
157 if (count($a_fiche_id) == 0) { echo " OK $g_succeed";} else { echo " "._('Attention ').$g_failed;}
158 ?>
159 <ol>
160 <?php
161 for ($i=0;$i<count($a_fiche_id);$i++):
162 $poste=$cn->execute('get_poste',array($a_fiche_id[$i]['f_id']));
163 $tmp_qcode=$cn->execute('get_qcode',array($a_fiche_id[$i]['f_id']));
165
166 if ( $qcode == FALSE || $qcode[0]['qcode']=="") {
167 continue;
168 }
169 ?>
170 <li><?php
171 echo HtmlInput::card_detail($qcode[0]['qcode'],$qcode[0]['qcode'],' style="display:inline"') ;
172 echo " ";
173 echo HtmlInput::history_card($a_fiche_id[$i]["f_id"],_("Hist."),' display:inline');
174 ?>
175
176 </li>
177 <ul>
178 <?php
180 $nb_dep = ($all_dep == FALSE ) ? 0 :count($all_dep);
181 for ($e=0;$e<$nb_dep;$e++):
182 ?>
183 <li>
184 <?php echo HtmlInput::history_account($all_dep[$e]['j_poste'],$all_dep[$e]['j_poste'],' display:inline ')?>
185 <?php echo h($all_dep[$e]['pcm_lib'])?>
186 </li>
187 <?php
188 endfor;
189 ?>
190 </ul>
191 <?php
193 ?>
194 </ol>
195
196 <h2><?php echo _('Poste comptable utilisé sans la fiche correspondante') ?></h2>
197<p class="notice">
198 <?php echo _('Cela pourrait causer des différences entre les balances par fiches et celle par postes comptables, utilisez le plugin
199 "OUTIL COMPTABLE" pour corriger');
200 ?>
201</p>
202
203<?php
205 select distinct vw.f_id,j_poste ,vw_name,quick_code
206 from vw_poste_qcode as vw
207 join jrnx using (j_poste)
208 join vw_fiche_attr as v_attr on (vw.f_id=v_attr.f_id)
209 join jrn on (jrnx.j_grpt=jrn.jr_grpt_id)
210 where
211 jrnx.f_id is null and $sql_year order by 1
212";
213
214$sql_concerned_operation="select vw.f_id,jrnx.j_date,jrnx.j_id,jrn.jr_id,jrnx.j_poste,jr_internal ,jr_comment
215 from vw_poste_qcode as vw
216 join jrnx using (j_poste)
217 join jrn on (jrnx.j_grpt=jrn.jr_grpt_id)
218 where
219 jrnx.f_id is null and vw.f_id= $1 and $sql_year";
222 if ( $nb_account_used == 0 )
223 {
224 echo _('Résultat')." ".$g_succeed;
225 }
226 $ret=$cn->prepare('get_operation',$sql_concerned_operation);
227
228?>
229
230 <?php for ($i=0;$i<$nb_account_used;$i++): ?>
231 <h3>
232 <?php
233 echo _('Poste comptable ').$a_account_used[$i]['j_poste']._(' pour la fiche ').$a_account_used[$i]['quick_code']." ".$a_account_used[$i]['vw_name'];
234 ?>
235 </h3>
236 <?php
237 $ret_operation=$cn->execute('get_operation',array($a_account_used[$i]['f_id']));
239 $nb_operation=($a_operation == FALSE) ? 0 : count($a_operation);
240 ?>
241 <table class="result">
242 <?php for ($x=0;$x<$nb_operation;$x++):?>
243 <tr>
244 <td>
245 <?php echo $a_operation[$x]['j_date']; ?>
246 </td>
247 <td>
248 <?php echo h($a_operation[$x]['jr_comment']); ?>
249 </td>
250 <td>
251 <?php echo HtmlInput::detail_op($a_operation[$x]['jr_id'],$a_operation[$x]['jr_internal']); ?>
252 </td>
253 <td>
254
255 </td>
256 </tr>
257 <?php endfor;?>
258 </table>
259 <?php endfor;?>
260</div>
format_date($p_date, $p_from_format='YYYY-MM-DD', $p_to_format='DD.MM.YYYY')
format the date, when taken from the database the format is MM-DD-YYYY
Definition: ac_common.php:852
h2($p_string, $p_class="", $raw="")
Definition: ac_common.php:68
th($p_string, $p_extra='', $raw='')
Definition: ac_common.php:58
h1($p_string, $p_class="")
Definition: ac_common.php:72
tr($p_string, $p_extra='')
Definition: ac_common.php:88
p($p_string)
Definition: ac_common.php:39
td($p_string='', $p_extra='')
surround the string with td
Definition: ac_common.php:83
nbm($p_number, $p_dec=2)
format the number with a sep.
Definition: ac_common.php:137
global $g_user
if no group available , then stop
h( $row[ 'oa_description'])
this class handle the different bilan, from the table bilan, parse the form and replace in the templa...
static fetch_all($ret)
wrapper for the function pg_fetch_all
static connect()
static history_account($p_account, $p_mesg, $p_style="", $p_exercice="")
display a div with the history of the account
static detail_op($p_jr_id, $p_mesg)
return a string containing the html code for calling the modifyOperation
static card_detail($p_qcode, $pname='', $p_style="", $p_nohistory=false, $nofollowup=false)
show the detail of a card
static history_card($f_id, $p_mesg, $p_style="", $p_exercice="")
display a div with the history of the card
For the periode tables parm_periode and jrn_periode.
$all table
print
Type of printing.
$start_periode
$end_periode
$sql_concerned_operation
for($i=0;$i< $nb_jrn;$i++) $deb
$a_fiche_id
global $g_failed
if( $nb_account_used==0) $ret
$ret_operation
$nb_operation
$sql_account_used
$tmp_qcode
$nb_account_used
$sql_fiche_id
global $g_succeed
$sql_poste
$sql_year
$a_operation
global $g_parameter
$sql_year_target
$exercice
$a_account_used
$sql_qcode