Plugins  LAST
 All Data Structures Files Functions Variables Pages
class_impdol_operation.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 /* $Revision$ */
20 
21 // Copyright (c) 2002 Author Dany De Bontridder dany@alchimerys.be
22 
23 /**
24  * @file
25  * @brief
26  *
27  */
28 require_once 'class_sql_impdol.php';
29 
31 {
32 
33  function save_file()
34  {
35  if (trim($_FILES['csv_operation']['name']) == '')
36  {
37  alert('Pas de fichier donné');
38  return -1;
39  }
40  $this->filename = tempnam($_ENV['TMP'], 'upload_');
41  move_uploaded_file($_FILES["csv_operation"]["tmp_name"], $this->filename);
42 
43  $imp = new Impdol_Import_sql();
44  $imp->setp('temp_file', $this->filename);
45  $imp->setp('send_file', $_FILES['csv_operation']['name']);
46  $imp->insert();
47  $this->impid = $imp->getp("id");
48  }
49 
50  function record()
51  {
52  $foperation = fopen($this->filename, 'r');
53  $this->row_count = 0;
54  $max = 0;
55  while (($row = fgetcsv($foperation, 0, ";", '"')) !== false)
56  {
57  if (count($row) != 11)
58  {
59  $str_row = implode($row, ";");
60  echo "Attention " . h($str_row) . " ne contient pas 11 colonnes";
61  continue;
62  }
63  $r = new impdol_Operation_tmp_Sql();
64  $r->setp('dolibarr', $row[0]);
65  $r->setp('date', $row[1]);
66  $r->setp('type', $row[2]);
67  $r->setp('qcode', $row[3]);
68  $r->setp('desc', $row[4]);
69  $r->setp('pj', $row[5]);
70  $r->setp('amount_unit', $row[6]);
71  $r->setp('amount_vat', $row[7]);
72  $r->setp('number_unit', $row[8]);
73  $r->setp('rate', $row[9]);
74  $r->setp('amount_total', $row[10]);
75  $r->setp("import_id", $this->impid);
76  $r->insert();
77  $this->row_count++;
78  }
79  echo "Nombre de lignes enregistrées : " . $this->row_count;
80  $import = new impdol_import_sql($this->impid);
81  $import->setp("nbrow", $this->row_count);
82  $import->update();
83  }
84 
85  /**
86  * Check data contained into impdol.operation_tmp. Update the column o_result (T = ok N no ok)
87  * Put in o_message the reason of the problem
88  */
89  function check()
90  {
91  global $cn;
92  try
93  {
94  $cn->start();
95  $array = $cn->get_array("select o_id from impdol.operation_tmp where i_id=$1 order by o_id", array($this->impid));
96  $nb_row = count($array);
97  for ($i = 0; $i < $nb_row; $i++)
98  {
99  $msg = "";
100  $operation = new impdol_operation_tmp_sql();
101  $operation->setp("id", $array[$i]['o_id']);
102  $operation->load();
103  $operation->setp("code", 'T');
104  $dol = $operation->getp("dolibarr");
105  /*
106  * detect duplicate
107  */
108  $db = $cn->get_value("select count(*) from impdol.operation_tmp where o_doli=$1 and o_id in (select o_id from impdol.operation_transfer)", array($dol));
109  if ($db > 0)
110  {
111  $operation->setp("code", "N");
112  $operation->setp("message", " Opération déjà transférée : doublon ");
113  $operation->update();
114  continue;
115  }
116  if (trim($dol) == "" || isNumber($dol) == 0)
117  {
118  $operation->setp("code", 'N');
119  $msg.=" le numéro de ligne pour dolibarr est invalide";
120  }
121  if (isDate($operation->getp("date")) == null)
122  {
123  $operation->setp("code", 'N');
124  $msg.=" La date est invalide, format n'est pas JJ.MM.AAAA";
125  }
126  $fiche = new Fiche($cn);
127  $fiche->get_by_qcode(trim($operation->getp("qcode")));
128 
129  if ($fiche->id == 0)
130  {
131  $operation->setp("code", 'N');
132  $msg.=" Cette fiche n'existe pas";
133  }
134  else
135  {
136  $operation->setp("fiche", $fiche->id);
137  }
138  /**
139  * check if card as a valid accounting
140  */
141  $poste = $fiche->strAttribut(ATTR_DEF_ACCOUNT);
142  if (trim($poste) == '' || $cn->get_value("select count(*) from tmp_pcmn where pcm_val=$1", array($poste)) == 0)
143  {
144  $operation->setp("code", 'N');
145  $msg.=" Cette fiche n'a pas de poste comptable valide";
146  }
147  $operation->setp("poste", $poste);
148  $a = array("rate" => " Taux de TVA", "amount_total" => "Montant total", "number_unit" => 'Nombre d\'unité', "amount_vat" => "Montant TVA");
149 
150  foreach ($a as $key => $value)
151  {
152  $v = $operation->getp($key);
153  $v = str_replace(",", ".", $v);
154 
155  if (trim($v) != "" && isNumber($v) == 0)
156  {
157  $operation->setp("code", 'N');
158  $msg.=" $value n'est pas un nombre";
159  continue;
160  }
161 
162  $operation->setp($key, $v);
163  }
164 
165  if ($operation->getp("type") != "T")
166  {
167  $tva_id = $cn->get_array("select tva_id from impdol.parameter_tva where pt_rate/100=$1", array($operation->getp("rate")));
168  if (count($tva_id) > 1)
169  {
170  $operation->setp("code", 'N');
171  $msg.=" Plusieurs code TVA correspondent à ce taux";
172  }
173  elseif (empty($tva_id))
174  {
175  $operation->setp("code", 'N');
176  $msg.=" Aucun code TVA ne correspond à ce taux";
177  }
178  else
179  {
180  $operation->setp("tva_id", $tva_id[0]['tva_id']);
181  }
182  }
183  // a supplier and one service at least is needed
184  $code_op = $operation->getp("dolibarr");
185  $nb_customer = $cn->get_value("select count(*) from impdol.operation_tmp where o_type='T' and o_doli=$1 and i_id=$2", array($code_op, $this->impid));
186  $nb_good = $cn->get_value("select count(*) from impdol.operation_tmp where o_type='S' and o_doli=$1 and i_id=$2", array($code_op, $this->impid));
187  if ($nb_customer == 0)
188  {
189  $operation->setp("code", 'N');
190  $msg.=" Aucun client ou fournisseur";
191  }
192  if ($nb_good == 0)
193  {
194  $operation->setp("code", 'N');
195  $msg.=" Aucune marchandise ou service";
196  }
197 
198  // check if in a opened period
199  $op_date = $operation->getp('date');
200  $periode = new Periode($cn);
201  try
202  {
203  $periode->find_periode($op_date);
204  }
205  catch (Exception $e)
206  {
207  $msg.=$e->getMessage();
208  $operation->setp('code', 'N');
209  }
210 
211  $operation->setp("message", $msg);
212  $operation->update();
213  }
214  /*
215  * If a part is N then the whole operation is N
216  */
217  $sql = "update impdol.operation_tmp set o_result='N' where i_id=$1 and
218  o_doli in (select o_doli from impdol.operation_tmp where o_result='N' and i_id=$1)";
219  $cn->exec_sql($sql, array($this->impid));
220  $cn->commit();
221  }
222  catch (Exception $e)
223  {
224  print_r($e->getTraceAsString());
225  $cn->rollback;
226  }
227  }
228 
229  /**
230  * Show the result in a table
231  */
232  function result()
233  {
234  require_once NOALYSS_INCLUDE.'/lib/class_html_table.php';
235  global $cn, $g_failed, $g_succeed;
236  $sql = " select o_doli,o_date,o_qcode,o_label,o_pj,amount_unit,
237  amount_vat,
238  number_unit,
239  vat_rate,
240  amount_total,
241  case when o_result='T' then '" . $g_succeed . "' else '" . $g_failed . "' end as result,
242  o_message
243  from impdol.operation_tmp where i_id=" . $this->impid . " order by o_id";
244  echo Html_Table::sql2table($cn, array(
245  array('name' => 'n° ligne',
246  'style' => 'style="text-align:right"'),
247  array('name' => 'Date',
248  'style' => 'text-align:right'),
249  array('name' => 'QuickCode'),
250  array('name' => 'Libellé'),
251  array('name' => 'n° pj'),
252  array('name' => 'Montant / unité', 'style' => 'style="text-align:right"'),
253  array('name' => 'Montant Total TVA', 'style' => 'style="text-align:right"'),
254  array('name' => 'Nbre unités', 'style' => 'style="text-align:right"'),
255  array('name' => 'taux TVA', 'style' => 'style="text-align:right"'),
256  array('name' => 'Montant total TVAC', 'style' => 'style="text-align:right"'),
257  array('name' => 'Transfert', 'style' => 'style="text-align:right"', 'raw' => 1),
258  array('name' => 'Message')
259  )
260  , $sql, 'style="width:100%" class="result"'
261  );
262  }
263 
264  function transfer()
265  {
266  global $cn;
267  $jrn = $_POST['p_jrn'];
268  $ledger = new Acc_Ledger($cn, $jrn);
269  $type = $ledger->get_type();
270  switch ($type)
271  {
272  case 'ACH':
273  $ledger = new Acc_Ledger_Purchase($cn, $jrn);
274  $tiers_side = 'c';
275  $oth_side = 'd';
276  break;
277  case 'VEN':
278  $ledger = new Acc_Ledger_Sold($cn, $jrn);
279  $tiers_side = 'd';
280  $oth_side = 'c';
281  break;
282  default:
283  die('Erreur ce type journal n\' est pas encore supporté');
284  }
285  /**
286  * Loop in table operation_tmp, get all the record to transfer
287  */
288  $array = $cn->get_array("select
289  distinct o_doli
290  from impdol.operation_tmp
291  where i_id=$1 and o_result='T'
292  order by o_doli asc", array($this->impid));
293  $nb_row = count($array);
294  bcscale(2);
295  try
296  {
297  $cn->start();
298  for ($i = 0; $i < $nb_row; $i++)
299  {
300  /*
301  * For each operation (same o_doli code)
302  */
303  $adetail = $cn->get_array("select o_id from impdol.operation_tmp where o_doli=$1 and i_id=$2 and o_type='S'", array($array[$i]['o_doli'], $this->impid));
304  $atiers = $cn->get_array("select o_id from impdol.operation_tmp where o_doli=$1 and i_id=$2 and o_type='T'", array($array[$i]['o_doli'], $this->impid));
305  if (count($atiers) > 1)
306  {
307  echo "Plusieurs clients pour l' opération, code " . $array[$i]['o_doli'];
308  continue;
309  }
310  if (count($atiers) == 0)
311  {
312  echo "Pas de client pour une opération, code " . $array[$i]['o_doli'];
313  continue;
314  }
315 
316  $oper_tiers = new Impdol_Operation_Tmp_Sql($atiers[0]['o_id']);
317  $nb_detail = count($adetail);
318  $sum = 0; $sum_side=0;
319  $grpt = $cn->get_value("select nextval('s_grpt');");
320  $internal = $ledger->compute_internal_code($grpt);
321 
322  $tva = array();
323  /* record all S record */
324  for ($e = 0; $e < $nb_detail; $e++)
325  {
326  /* Record service */
327  $oper = new Impdol_Operation_Tmp_Sql($adetail[$e]['o_id']);
328  $oper->from_array($array[$i]);
329  $date = format_date($oper->getp("date"), "YYYY-MM-DD", "DD.MM.YYYY");
330  $oper->setp("date", $date);
331  $jrnx = new Acc_Operation($cn);
332  $jrnx->date = $date;
333  $amount_tva = $oper->getp("amount_vat");
334  $amount_tvac = $oper->getp("amount_total");
335  $jrnx->amount = bcsub($amount_tvac, $amount_tva);
336  $save_amount=$jrnx->amount;
337  $jrnx->poste = $oper->getp('poste');
338  $jrnx->grpt = $grpt;
339  $jrnx->type = $oth_side;
340  $jrnx->jrn = $jrn;
341  $jrnx->user = $_SESSION['g_user'];
342  $jrnx->periode = 0;
343  $jrnx->qcode = $oper->getp("qcode");
344  $jrnx->desc = mb_substr($oper->getp("desc"),0,80,'UTF8');
345  $id = $jrnx->insert_jrnx();
346 
347  $transfer = new impdol_operation_transfer_sql();
348  $transfer->setp("j_id", $id);
349  $transfer->setp("o_id", $oper->getp("id"));
350  $transfer->insert();
351 
352  $tva_id = $oper->getp("tva_id");
353 
354  /*
355  * Save into quant_purchase or quant_sale
356  */
357  switch ($type)
358  {
359  case 'ACH':
360  $sql = "insert into quant_purchase(qp_internal,j_id,qp_fiche,qp_quantite,qp_price,qp_vat,qp_vat_code,qp_supplier)
361  values($1,$2,$3,$4,$5,$6,$7,$8)";
362  $cn->exec_sql($sql, array(null, $id, $oper->getp("fiche"), $oper->getp("number_unit"), $save_amount, $amount_tva, $tva_id, $oper_tiers->getp("fiche")));
363  $sum_side = ($save_amount > 0) ? bcadd($sum_side, $amount_tvac):$sum_side;
364  break;
365  case 'VEN':
366  $cn->exec_sql("insert into quant_sold
367  (qs_internal,qs_fiche,qs_quantite,qs_price,qs_vat,qs_vat_code,qs_client,j_id,qs_vat_sided,qs_valid)
368  values
369  ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)", array(null, /* 1 qs_internal */
370  $oper->getp("fiche"), /* 2 qs_fiche */
371  $oper->getp("number_unit"), /* 3 qs_quantite */
372  $save_amount, /* 4 qs_price */
373  $amount_tva, /* 5 qs_vat */
374  $tva_id, /* 6 qs_vat_code */
375  $oper_tiers->getp('fiche'), /* 7 qs_client */
376  $id, /* 8 j_id */
377  0, /* 9 qs_vat_sided */
378  'Y' /* 10 qs_valid */
379  ));
380 
381  $sum_side = ($save_amount > 0) ? bcadd($sum_side, $amount_tvac):$sum_side;
382  break;
383  }
384  /* save VAT into an array */
385  $side=($amount_tva > 0)?1:0;
386  if (isset($tva[$tva_id][$side]))
387  {
388  $tva[$tva_id][$side] = bcadd($tva[$tva_id][$side], $amount_tva);
389  }
390  else
391  {
392  $tva[$tva_id][$side] = $amount_tva;
393  }
394  $sum = bcadd($sum, $amount_tvac);
395 
396  } // loop e
397  // Record the tiers
398 
399  $jtiers = new Acc_Operation($cn);
400  $jtiers->date = $date;
401  $jtiers->amount = $sum;
402  $jtiers->poste = $oper_tiers->getp('poste');
403  $jtiers->grpt = $grpt;
404  $jtiers->type = $tiers_side;
405  $jtiers->jrn = $jrn;
406  $jtiers->user = $_SESSION['g_user'];
407  $jtiers->periode = 0;
408  $jtiers->qcode = $oper_tiers->getp("qcode");
409  $jtiers->desc = mb_substr($oper_tiers->getp("desc"),0,80,'UTF8');
410  $jtiers->insert_jrnx();
411  /* Record the vat 1 */
412  foreach ($tva as $key => $atva)
413  {
414  foreach ($atva as $tvaid=>$tva_value)
415  {
416  $tva = new Acc_TVA($cn, $key);
417  if ( $tva->load() == -1 ) die ("Code TVA inconnu $tvaid");
418  $poste = $tva->get_side($oth_side);
419  $op_tva = new Acc_Operation($cn);
420  $op_tva->date = $date;
421  $op_tva->amount = $tva_value;
422  $op_tva->poste = $poste;
423  $op_tva->grpt = $grpt;
424  $op_tva->type = $oth_side;
425  $op_tva->jrn = $jrn;
426  $op_tva->user = $_SESSION['g_user'];
427  $op_tva->periode = 0;
428  $op_tva->qcode = null;
429  $op_tva->desc = $tva->tva_label;
430  $op_tva->insert_jrnx();
431  }
432  }
433  /* record into jrn */
434  $acc_jrn = new Acc_Operation($cn);
435  $acc_jrn->jrn = $jrn;
436  $acc_jrn->amount =abs ($sum_side);
437  $acc_jrn->desc = mb_substr($oper_tiers->getp("desc"),0,80,'UTF8');
438  $acc_jrn->date = $date;
439  $acc_jrn->grpt = $grpt;
440  $acc_jrn->periode = 0;
441  $acc_jrn->insert_jrn();
442  $cn->exec_sql('update jrn set jr_pj_number=$1 where jr_id=$2',array($oper->getp('pj'),$acc_jrn->jr_id));
443 
444  /* Update info */
445  $ledger->grpt_id = $grpt;
446  $ledger->update_internal_code($internal);
447  /*
448  * Update internal code in quant_*
449  */
450  switch ($type)
451  {
452  case 'ACH':
453  $cn->exec_sql('update quant_purchase set qp_internal = $1 where j_id in (select j_id from jrnx where j_grpt=$2)', array($internal, $grpt));
454  break;
455  case 'VEN':
456  $cn->exec_sql('update quant_sold set qs_internal = $1 where j_id in (select j_id from jrnx where j_grpt=$2)', array($internal, $grpt));
457  break;
458  }
459  }// loop i
460  $cn->commit();
461  }
462  catch (Exception $e)
463  {
464  print_r($e->getTraceAsString());
465  $cn->rollback();
466  }
467  }
468 
469  function result_transfer()
470  {
471  require_once NOALYSS_INCLUDE.'/lib/class_html_table.php';
472  global $cn, $g_failed, $g_succeed;
473  $sql = " select distinct jr_id, jr_pj,jr_date, jr_comment,jr_internal
474 
475  from jrn
476  where jr_grpt_id in (
477  select j_grpt
478  from impdol.operation_tmp as otmp join impdol.operation_transfer as ot on (ot.o_id = otmp.o_id) join jrnx on (jrnx.j_id = ot.j_id)
479  and i_id=$1 ) order by jr_date ";
480  $arow=$cn->get_array($sql,array($this->impid));
481  echo h2("Opérations sauvées",'info');
482  echo '<table class="result">';
483  echo '<tr>';
484  echo th("Date");
485  echo th("Libellé");
486  echo th("Pièce");
487  echo th("N° opération");
488  echo '</tr>';
489  for ($i=0;$i<count($arow);$i++)
490  {
491  echo '<tr>';
492  echo td($arow[$i]['jr_date']);
493  echo td($arow[$i]['jr_comment']);
494  echo td($arow[$i]['jr_pj']);
495  echo '<td>'.HtmlInput::detail_op($arow[$i]['jr_id'],$arow[$i]['jr_internal']).'</td>';
496  echo '</tr>';
497  }
498  echo '</table>';
499  }
500 
501 }
502 
503 ?>
global $g_failed
$msg
if(isset($_POST['ftvaadd'])) if(isset($_POST['mod'])) $atva
get data from database
check()
Check data contained into impdol.operation_tmp.
$periode
for($i=0;$i< Database::num_row($ret);$i++) $row
$r
$type
Retrouve le type de row si == 3.
$nb_row
Definition: search_view.php:6
if(isset($_POST['remove'])) $array
$key
if(isset($_POST['save'])) $tva
result()
Show the result in a table.
global $g_succeed
$sql
$row_count
global $cn