Plugins  LAST
 All Data Structures Files Functions Variables Pages
class_impacc_export.php
Go to the documentation of this file.
1 <?php
2 
3 /*
4  * This file is part of PhpCompta.
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  * PhpCompta 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 (2014) Author Dany De Bontridder <dany@alchimerys.be>
21 
22 require_once NOALYSS_INCLUDE."/lib/class_noalyss_csv.php";
23 
24 /**
25  * @file
26  * @brief Export
27  */
29 {
30 
31  var $ledger; //!< Ledger id (jrn_def.jrn_def_id
32  var $date_start; //!< date start
33  var $date_end; //!< date_end
34 
35  function __construct()
36  {
37  global $g_user;
38  $cn=Dossier::connect();
39  $this->ledger=0;
40  $exercice=$g_user->get_exercice();
41  $per=new Periode($cn);
42  $lim=$per->get_limit($exercice);
43  $this->date_start=$lim[0]->first_day();
44  $this->date_end=$lim[1]->last_day();
45  }
46 
47  //--------------------------------------------------------------------
48  /// Show a form to input the parameter , ledger , stard and end date
49  //--------------------------------------------------------------------
50  function input_param()
51  {
52  $cn=Dossier::connect();
53  $l=new Acc_Ledger($cn, $this->ledger);
54  $select_ledger=$l->select_ledger();
55 
56  $date_start=new IDate("date_start", $this->date_start);
57  $date_end=new IDate("date_end", $this->date_end);
58 
59  require DIR_IMPORT_ACCOUNT."/template/export_param.php";
60  }
61 
62  //--------------------------------------------------------------------
63  //--------------------------------------------------------------------
64  function get_param()
65  {
66  $this->ledger=HtmlInput::default_value_request("p_jrn", 0);
67  $this->date_start=HtmlInput::default_value_request("date_start", 0);
68  $this->date_end=HtmlInput::default_value_request("date_end", 0);
69  }
70 
71  //--------------------------------------------------------------------
72  //--------------------------------------------------------------------
73  function export_csv()
74  {
75  $cn=Dossier::connect();
76  $ledger=new Acc_Ledger($cn, $this->ledger);
77  $cvs=new Noalyss_Csv("ledger".$ledger->get_name());
78  $type=$ledger->get_type();
79  if ($type=='ACH')
80  {
81  $cvs->send_header();
82  $this->export_purchase($cvs);
83  }
84  else
85  if ($type=='VEN')
86  {
87  $cvs->send_header();
88  $this->export_sale($cvs);
89  }
90  else
91  if ($type=="ODS")
92  {
93  $cvs->send_header();
94  $this->export_misc($cvs);
95  }
96  else
97  if ($type=="FIN")
98  {
99  $cvs->send_header();
100  $this->export_fin($cvs);
101  }
102  else
103  {
104  throw new Exception(_("Journal invalide"));
105  }
106  }
107 
108  //--------------------------------------------------------------------
109  /// Export a ledger of Sale
110  //--------------------------------------------------------------------
111  function export_sale(Noalyss_Csv $p_csv)
112  {
113  $cn=Dossier::connect();
114  $sql="
115  select
116  to_char(jr_date,'DD.MM.YYYY') as sdate,
117  jr_id,
118  (select ad_value from fiche_detail where f_id=qs_client and ad_id=23) as qcode,
119  jr_pj,
120  jr_comment,
121  (select ad_value from fiche_detail where f_id=qs_fiche and ad_id=23) as qcode_serv,
122  qs_unit,
123  qs_price,
124  qs_vat_code,
125  qs_price+qs_vat as price_tax,
126  to_char(jr_ech,'DD.MM.YYYY') slimit,
127  to_char(jr_date_paid,'DD.MM.YYYY') sdatepaid
128  from jrn
129  join jrnx on (j_grpt=jr_grpt_id)
130  join public.quant_sold using (j_id)
131  where
132  jr_date <= to_date($1,'DD.MM.YYYY') and
133  jr_date >= to_date($2,'DD.MM.YYYY') and
134  jr_def_id=$3
135  order by jr_date,j_id
136  ";
137  $ret=$cn->exec_sql($sql,
138  array($this->date_end, $this->date_start, $this->ledger));
139  $nb=Database::num_row($ret);
140  for ($i=0; $i<$nb; $i++)
141  {
142  $row=Database::fetch_array($ret, $i);
143  $p_csv->add($row["sdate"]);
144  $p_csv->add($row["jr_id"]);
145  $p_csv->add($row["qcode"]);
146  $p_csv->add($row["jr_pj"]);
147  $p_csv->add($row["jr_comment"]);
148  $p_csv->add($row["qcode_serv"]);
149  $p_csv->add($row["qs_unit"], "number");
150  $p_csv->add($row["qs_price"], "number");
151  $p_csv->add($row["qs_vat_code"]);
152  $p_csv->add($row["price_tax"], "number");
153  $p_csv->add($row["slimit"]);
154  $p_csv->add($row["sdatepaid"]);
155  $p_csv->write();
156  }
157  }
158 
159  //--------------------------------------------------------------------
160  /// Export a ledger of Purchase
161  //--------------------------------------------------------------------
162  function export_purchase(Noalyss_Csv $p_csv)
163  {
164  $cn=Dossier::connect();
165 
166  $sql="
167  select
168  to_char(jr_date,'DD.MM.YYYY') as sdate,
169  jr_id,
170  (select ad_value from fiche_detail where f_id=qp_supplier and ad_id=23) as qcode,
171  jr_pj,
172  jr_comment,
173  (select ad_value from fiche_detail where f_id=qp_fiche and ad_id=23) as qcode_serv,
174  qp_unit,
175  qp_price,
176  qp_vat_code,
177  qp_price+qp_vat as price_tax,
178  to_char(jr_ech,'DD.MM.YYYY') slimit,
179  to_char(jr_date_paid,'DD.MM.YYYY') sdatepaid
180  from jrn
181  join jrnx on (j_grpt=jr_grpt_id)
182  join public.quant_purchase using (j_id)
183  where
184  jr_date <= to_date($1,'DD.MM.YYYY') and
185  jr_date >= to_date($2,'DD.MM.YYYY') and
186  jr_def_id=$3
187  order by jr_date,j_id
188  ";
189  $ret=$cn->exec_sql($sql,
190  array($this->date_end, $this->date_start, $this->ledger));
191  $nb=Database::num_row($ret);
192  for ($i=0; $i<$nb; $i++)
193  {
194  $row=Database::fetch_array($ret, $i);
195  $p_csv->add($row["sdate"]);
196  $p_csv->add($row["jr_id"]);
197  $p_csv->add($row["qcode"]);
198  $p_csv->add($row["jr_pj"]);
199  $p_csv->add($row["jr_comment"]);
200  $p_csv->add($row["qcode_serv"]);
201  $p_csv->add($row["qp_unit"], "number");
202  $p_csv->add($row["qp_price"], "number");
203  $p_csv->add($row["qp_vat_code"]);
204  $p_csv->add($row["price_tax"], "number");
205  $p_csv->add($row["slimit"]);
206  $p_csv->add($row["sdatepaid"]);
207  $p_csv->write();
208  }
209  }
210 
211  //--------------------------------------------------------------------
212  /// Export a financial ledger
213  //--------------------------------------------------------------------
214  function export_fin(Noalyss_Csv $p_csv)
215  {
216  $cn=Dossier::connect();
217  $sql="
218  select
219  jr_id,
220  to_char(jr_date,'DD.MM.YYYY') as sdate,
221  (select ad_value from fiche_detail where f_id=qf_other and ad_id=23) as qcode,
222  jr_pj_number,
223  jr_comment,
224  qf_amount
225  from
226  quant_fin
227  join jrn using (jr_id)
228  where
229  jr_date <= to_date($1,'DD.MM.YYYY') and
230  jr_date >= to_date($2,'DD.MM.YYYY') and
231  jr_def_id=$3
232  order by jr_date
233 ";
234  $ret=$cn->exec_sql($sql,
235  array($this->date_end, $this->date_start, $this->ledger));
236  $nb=Database::num_row($ret);
237  for ($i=0; $i<$nb; $i++)
238  {
239  $row=Database::fetch_array($ret, $i);
240  $p_csv->add($row["sdate"]);
241  $p_csv->add($row["jr_id"]);
242  $p_csv->add($row["qcode"]);
243  $p_csv->add($row["jr_pj_number"]);
244  $p_csv->add($row["jr_comment"]);
245  $p_csv->add($row["qf_amount"]);
246  $p_csv->write();
247  }
248  }
249 
250  //--------------------------------------------------------------------
251  /// Export ODS ledger
252  //--------------------------------------------------------------------
253  function export_misc(Noalyss_Csv $p_csv)
254  {
255  $cn=Dossier::connect();
256  $sql="
257  select
258  to_char(jr_date,'DD.MM.YYYY') as sdate,
259  jr_id,
260  coalesce(j_qcode,j_poste) as qcode,
261  jr_pj_number,
262  jr_comment,
263  j_montant,
264  case when j_debit = false then 'C'
265  else 'D'
266  end as deb
267  from
268  jrn
269  join jrnx on (jr_grpt_id=j_grpt)
270  where
271  jr_date <= to_date($1,'DD.MM.YYYY') and
272  jr_date >= to_date($2,'DD.MM.YYYY') and
273  jr_def_id=$3
274 ";
275  $ret=$cn->exec_sql($sql,
276  array($this->date_end, $this->date_start, $this->ledger));
277  $nb=Database::num_row($ret);
278  for ($i=0; $i<$nb; $i++)
279  {
280  $row=Database::fetch_array($ret, $i);
281  $p_csv->add($row["sdate"]);
282  $p_csv->add($row["jr_id"]);
283  $p_csv->add($row["qcode"]);
284  $p_csv->add($row["jr_pj_number"]);
285  $p_csv->add($row["jr_comment"]);
286  $p_csv->add($row["j_montant"],"number");
287  $p_csv->add($row["deb"]);
288  $p_csv->write();
289  }
290  }
291 
292 }
293 
294 ?>
$ledger
Ledger id (jrn_def.jrn_def_id.
$ret
export_misc(Noalyss_Csv $p_csv)
Export ODS ledger.
if(isset($_POST['save'])) $exercice
export_sale(Noalyss_Csv $p_csv)
Export a ledger of Sale.
for($i=0;$i< Database::num_row($ret);$i++) $row
$type
Retrouve le type de row si == 3.
input_param()
Show a form to input the parameter , ledger , stard and end date.
export_fin(Noalyss_Csv $p_csv)
Export a financial ledger.
export_purchase(Noalyss_Csv $p_csv)
Export a ledger of Purchase.
$sql
global $cn