noalyss Version-9
acc_ledger_history_purchase.class.php
Go to the documentation of this file.
1<?php
2
3/*
4 * This file is part of NOALYSS.
5 *
6 * PhpCompta is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 2 of the License, or
9 * (at your option) any later version.
10 *
11 * NOALYSS is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
15 *
16 * You should have received a copy of the GNU General Public License
17 * along with PhpCompta; if not, write to the Free Software
18 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
19 */
20// Copyright (2018) Author Dany De Bontridder <dany@alchimerys.be>
21
22/**
23 * @file
24 * @brief class Acc_Ledger_History_Purchase , list of operations
25 * * display or export operations in HTML , PDF or CSV
26 */
27
28/**
29 * @brief Display the operations for Purchase
30 * @see acc_ledger_historyTest.php
31 */
33{
34
35 private $data;//!< Contains rows from SQL
36
37 /**
38 * @param mixed $data
39 */
40 public function set_data($data)
41 {
42 //!< Contains rows from SQL
43 $this->data = $data;
44 return $this;
45 }
46
47 public function __construct(\Database $cn, $pa_ledger, $p_from, $p_to,
48 $p_mode)
49 {
50 parent::__construct($cn, $pa_ledger, $p_from, $p_to, $p_mode);
51 $this->filter_operation='all';
52 $this->ledger_type='ACH';
53 }
54 public function get_filter_operation()
55 {
57 }
58
59
60 /**
61 * @brief display the accounting
62 */
63 public function export_accounting_html()
64 {
65 $ledger_history=new Acc_Ledger_History_Generic($this->db,
66 $this->ma_ledger, $this->m_from, $this->m_to, $this->m_mode);
67 $ledger_history->export_accounting_html();
68 }
69
70 public function export_detail_html()
71 {
73
74 $this->get_row();
75 $this->add_vat_info();
77 include NOALYSS_TEMPLATE."/acc_ledger_history_purchase_detail.php";
78 }
79
80 public function export_extended_html()
81 {
82 $this->get_row();
83 $this->add_vat_info();
85 $this->prepare_detail();
87 include NOALYSS_TEMPLATE."/acc_ledger_history_purchase_extended.php";
88 }
89
90 /**
91 * @brief display in HTML following the mode
92 */
93 function export_html()
94 {
95 switch ($this->m_mode)
96 {
97 case "E":
98 $this->export_extended_html();
99 break;
100 case "D":
101 $this->export_detail_html();
102 break;
103 case "L":
104 $this->export_oneline_html();
105 break;
106 case "A":
107 $this->export_accounting_html();
108 break;
109 default:
110 break;
111 }
112 }
113
114 /**
115 * display in HTML one operation by line
116 */
117 public function export_oneline_html()
118 {
119 $this->get_row();
120 $this->prepare_reconcile_date();
121 $nb_other_tax=$this->has_other_tax();
122 require_once NOALYSS_TEMPLATE.'/acc_ledger_history_purchase_oneline.php';
123 }
124
125 /**
126 * Get the rows from jrnx and quant* tables
127 * @param int $p_limit max of rows to returns
128 * @param int $p_offset the number of rows to skip
129 */
130 public function get_row($p_limit=-1, $p_offset="")
131 {
132 $periode=sql_filter_per($this->db, $this->m_from, $this->m_to, 'p_id',
133 'jr_tech_per');
134
135 $cond_limite=($p_limit!=-1)?" limit ".$p_limit." offset ".$p_offset:"";
136 $sql_filter=$this->build_filter_operation();
137 $ledger_list=join(",", $this->ma_ledger);
138 $sql="
139 with row_purchase as
140 (select qp_internal,
141 qp_supplier,sum(qp_price) as novat,
142 sum(qp_vat) as vat ,
143 sum(qp_vat_sided) as tva_sided ,
144 sum(qp_nd_amount) as noded_amount,
145 sum(qp_nd_tva) as noded_vat,
146 sum(qp_dep_priv) as private_amount
147 from
148 quant_purchase group by qp_supplier,qp_internal),
149 supplier_detail as (
150 select x.f_id as f_id,
151 (select ad_value from fiche_detail where ad_id=1 and f_id=x.f_id) as name,
152 (select ad_value from fiche_detail where ad_id=32 and f_id=x.f_id) as first_name,
153 (select ad_value from fiche_detail where ad_id=23 and f_id=x.f_id) as qcode
154 from
155 fiche as x) ,
156 row_currency as (
157 select sum(oc_amount) as sum_oc_amount,sum(oc_vat_amount) as sum_oc_vat_amount,jrnx.j_grpt
158 from
159 operation_currency
160 join jrnx using (j_id)
161 join quant_purchase qp using (j_id)
162 group by j_grpt
163 ),
164 other_tax as (select sum(case when j_debit is true
165 then j_montant else 0-j_montant end) other_tax_amount
166 ,j_grpt
167 from jrnx j1
168 join jrn_tax jt2 on (j1.j_id=jt2.j_id) group by j_grpt)
169 select
170 name,
171 first_name,
172 qcode,
173 jrn.jr_id,
174 jr_pj_number,
175 to_char(jr_date,'DD.MM.YYYY') as str_date,
176 to_char(jr_date,'DDMMYY') as str_date_short,
177 to_char(jr_date_paid,'DD.MM.YYYY') as str_date_paid,
178 jr_internal,
179 qp_supplier,
180 jrn.jr_comment,
181 jr_pj_name,
182 vat,
183 tva_sided,
184 novat,
185 noded_amount,
186 noded_vat,
187 private_amount,
188 novat+vat-tva_sided as tvac,
189 n_text,
190 jr_grpt_id,
191 jrn.currency_id,
192 jrn.currency_rate,
193 jrn.currency_rate_ref,
194 sum_oc_amount,
195 sum_oc_vat_amount,
196 cr_code_iso,
197 coalesce (other_tax_amount,0) other_tax_amount
198 from
199 jrn
200 join row_purchase on (qp_internal=jr_internal)
201 join supplier_detail on (qp_supplier=f_id)
202 left join jrn_note using (jr_id)
203 left join row_currency as rc on (rc.j_grpt = jrn.jr_grpt_id)
204 left join currency as c on (c.id=jrn.currency_id)
205 left join other_tax as ot on (ot.j_grpt=jrn.jr_grpt_id)
206 where
207 jr_def_id in ({$ledger_list})
208 {$sql_filter}
209 and {$periode}
210 {$cond_limite}
211 order by jrn.jr_date, substring(jr_pj_number,'[0-9]+$')::numeric ";
212 $this->data=$this->db->get_array($sql);
213 }
214
215 /**
216 * @brief preprare the query for fetching the detailed VAT of an operation
217 */
218 private function add_vat_info()
219 {
220 $prepare=$this->db->is_prepare("vat_infop");
221 if ($prepare==FALSE)
222 {
223 $this->db->prepare("vat_infop",
224 "
225 select
226 sum(qp_vat) vat_amount ,
227 qp_vat_code
228 from
229 quant_purchase
230 where
231 qp_internal = $1
232 group by qp_vat_code order by qp_vat_code");
233 }
234
235 $nb_row=count($this->data);
236 for ($i=0; $i<$nb_row; $i++)
237 {
238 $ret=$this->db->execute("vat_infop",
239 array($this->data[$i]["jr_internal"]));
241 $this->data[$i]["detail_vat"]=$array;
242 }
243 }
244
245 /**
246 * Prepare the query for fetching detail of an operation
247 */
248 private function prepare_detail()
249 {
250
251 if ($this->db->is_prepare("detail_purchase")==FALSE)
252 {
253 $this->db->prepare("detail_purchase",
254 "
255 with card_name as
256 (select f_id,ad_value as name
257 from fiche_detail where ad_id=1),
258 card_qcode as
259 (select f_id,ad_value as qcode
260 from fiche_detail where ad_id=23)
261 select qp_price,qp_quantite,qp_vat,qp_vat_code,qp_unit,qp_vat_sided,name,qcode,tva_label,
262 qp_price+qp_vat-qp_vat_sided as tvac
263 from
264 quant_purchase
265 join jrnx using (j_id)
266 join card_name on (card_name.f_id=qp_fiche)
267 join card_qcode on (card_qcode.f_id=qp_fiche)
268 left join tva_rate on ( qp_vat_code=tva_id)
269 where
270 qp_internal=$1
271
272 ");
273 }
274 }
275 /**
276 * To get data
277 * @return array of rows
278 */
279 function get_data()
280 {
281 return $this->data;
282 }
283
284 /**
285 * @brief export Purchase in CSV
286 */
287 function export_csv()
288 {
289 // Prepare the query for reconcile date
290 $prepared_query=new Prepared_Query($this->db);
291 $prepared_query->prepare_reconcile_date();
292 $nb_other_tax=$this->has_other_tax();
293
294 $export=new Noalyss_Csv(_('journal'));
295 $export->send_header();
296
297 $this->get_row();
298 $this->prepare_reconcile_date();
299 $this->add_vat_info();
300
301 $own=new Noalyss_Parameter_Folder($this->db);
302 $title=array();
303 $title[]=_('Date');
304 $title[]=_("Paiement");
305 $title[]=_("operation");
306 $title[]=_("Pièce");
307 $title[]=_("Fournisseur");
308 $title[]=_("Note");
309 $title[]=_("interne");
310 $title[]=_("HTVA");
311 $title[]=_("privé");
312 $title[]=_("DNA");
313 $title[]=_("tva non ded.");
314 $title[]=_("TVA NP");
315
316
317
318 if ( $own->MY_TVA_USE=='Y')
319 {
320 $a_Tva=$this->db->get_array("select tva_id,tva_label from tva_rate order by tva_rate,tva_label,tva_id");
321 foreach($a_Tva as $line_tva)
322 {
323 $title[]="Tva ".$line_tva['tva_label'];
324 }
325 }
326 if ($nb_other_tax>0) {
327 $title[]=_("Autre taxe");
328 }
329 $title[]=_("TVAC/TTC");
330 $title[]=_("Devise");
331 $title[]=_("Devise HTVA");
332 $title[]=_("Devise TVA");
333 $title[]=_("Taux ref");
334 $title[]=_("Taux utilisé");
335 $title[]=_("Date paiement");
336 $title[]=_("Code paiement");
337 $title[]=_("Méthode paiement");
338 $title[]=_("Montant paiement");
339 $title[]=_("n° opération");
340 $export->write_header($title);
341
342 foreach ($this->data as $line)
343 {
344 $export->add($line['str_date']);
345 $export->add($line['str_date_paid']);
346 $export->add($line['jr_id']);
347 $export->add($line['jr_pj_number']);
348 $export->add($line['name']." ".
349 $line["first_name"]." ".
350 $line["qcode"]); // qp_supplier
351 $export->add($line['jr_comment']);
352 $export->add($line['jr_internal']);
353 $export->add($line['novat'],"number");
354 $export->add($line['private_amount'],"number");
355 $export->add($line['noded_amount'],"number");
356 $export->add($line['noded_vat'],"number");
357 $export->add($line['tva_sided'],"number");
358
359 $a_tva_amount=array();
360 if ($own->MY_TVA_USE=='Y')
361 {
362 //- set all TVA to 0
363 foreach ($a_Tva as $l)
364 {
365 $t_id=$l["tva_id"];
366 $a_tva_amount[$t_id]=0;
367 }
368 foreach ($line['detail_vat'] as $lineTVA)
369 {
370 $idx_tva=$lineTVA['qp_vat_code'];
371 $a_tva_amount[$idx_tva]=$lineTVA['vat_amount'];
372 }
373
374 foreach ($a_Tva as $line_tva)
375 {
376 $a=$line_tva['tva_id'];
377 $export->add($a_tva_amount[$a], "number");
378 }
379 }
380 if ( $nb_other_tax > 0)
381 {
382 $export->add($line['other_tax_amount'],"number");
383 }
384 $export->add(bcadd($line['other_tax_amount'],$line['tvac'],2),"number");
385 /**
386 * Add currency info
387 */
388 $export->add($line['cr_code_iso']);
389 $export->add($line['sum_oc_amount'],'number');
390 $export->add($line['sum_oc_vat_amount'],'number');
391 $export->add($line['currency_rate'],'number');
392 $export->add($line['currency_rate_ref'],'number');
393
394 /**
395 * Retrieve payment if any
396 */
397 $ret_reconcile=$this->db->execute('reconcile_date',array($line['jr_id']));
399 if ($max > 0) {
400 for ($e=0;$e<$max;$e++) {
402 $export->add($row['jr_date']);
403 $export->add($row['qcode_bank']);
404 $export->add($row['qcode_name']);
405 $export->add($row['jr_montant'],"number");
406 $export->add($row['jr_internal']);
407 }
408 }
409 $export->write();
410
411 }
412
413 }
414}
sql_filter_per($p_cn, $p_from, $p_to, $p_form='p_id', $p_field='jr_tech_per')
Create the condition to filter on the j_tech_per thanks a from and to date.
Definition: ac_common.php:682
manage the list of operation when we need several ledger with a different type or from Misceleaneous ...
Display the operations for Purchase.
export_oneline_html()
display in HTML one operation by line
export_detail_html()
display detail of operations m_mode=D
add_vat_info()
preprare the query for fetching the detailed VAT of an operation
get_row($p_limit=-1, $p_offset="")
Get the rows from jrnx and quant* tables.
__construct(\Database $cn, $pa_ledger, $p_from, $p_to, $p_mode)
export_html()
display in HTML following the mode
export_extended_html()
display extended details of operation m_mode=E
prepare_detail()
Prepare the query for fetching detail of an operation.
Display history of operation.
has_other_tax()
count the number of addition tax for the ledger
$filter_operation
type of ledger VEN , ACH , ODS, FIN
prepare_reconcile_date()
Prepare the query for fetching the linked operation @staticvar int $prepare.
add_additional_tax_info()
add additional info about additional tax.
static fetch_all($ret)
wrapper for the function pg_fetch_all
static fetch_array($ret, $p_indice=0, $p_mode=PGSQL_ASSOC)
wrapper for the function pg_fetch_array
static num_row($ret)
wrapper for the function pg_num_rows
contains the class for connecting to Noalyss
Manage the CSV : manage files and write CSV record.
Class to manage the company parameter (address, name...)
contains prepared query used in different classes of the application
$SecUser db
$sql_filter
Definition: preod.inc.php:43