28 require_once
'class_sql_impdol.php';
35 if (trim($_FILES[
'csv_operation'][
'name']) ==
'')
37 alert(
'Pas de fichier donné');
40 $this->filename = tempnam($_ENV[
'TMP'],
'upload_');
41 move_uploaded_file($_FILES[
"csv_operation"][
"tmp_name"], $this->filename);
43 $imp =
new Impdol_Import_sql();
44 $imp->setp(
'temp_file', $this->filename);
45 $imp->setp(
'send_file', $_FILES[
'csv_operation'][
'name']);
47 $this->impid = $imp->getp(
"id");
52 $foperation = fopen($this->filename,
'r');
55 while ((
$row = fgetcsv($foperation, 0,
";",
'"')) !==
false)
57 if (count(
$row) != 11)
59 $str_row = implode(
$row,
";");
60 echo
"Attention " . h($str_row) .
" ne contient pas 11 colonnes";
63 $r =
new impdol_Operation_tmp_Sql();
64 $r->setp(
'dolibarr',
$row[0]);
67 $r->setp(
'qcode',
$row[3]);
70 $r->setp(
'amount_unit',
$row[6]);
71 $r->setp(
'amount_vat',
$row[7]);
72 $r->setp(
'number_unit',
$row[8]);
74 $r->setp(
'amount_total',
$row[10]);
75 $r->setp(
"import_id", $this->impid);
81 $import->setp(
"nbrow", $this->row_count);
95 $array = $cn->get_array(
"select o_id from impdol.operation_tmp where i_id=$1 order by o_id", array($this->impid));
97 for ($i = 0; $i <
$nb_row; $i++)
101 $operation->setp(
"id",
$array[$i][
'o_id']);
103 $operation->setp(
"code",
'T');
104 $dol = $operation->getp(
"dolibarr");
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));
111 $operation->setp(
"code",
"N");
112 $operation->setp(
"message",
" Opération déjà transférée : doublon ");
113 $operation->update();
116 if (trim($dol) ==
"" || isNumber($dol) == 0)
118 $operation->setp(
"code",
'N');
119 $msg.=
" le numéro de ligne pour dolibarr est invalide";
121 if (isDate($operation->getp(
"date")) == null)
123 $operation->setp(
"code",
'N');
124 $msg.=
" La date est invalide, format n'est pas JJ.MM.AAAA";
127 $fiche->get_by_qcode(trim($operation->getp(
"qcode")));
131 $operation->setp(
"code",
'N');
132 $msg.=
" Cette fiche n'existe pas";
136 $operation->setp(
"fiche",
$fiche->id);
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)
144 $operation->setp(
"code",
'N');
145 $msg.=
" Cette fiche n'a pas de poste comptable valide";
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");
150 foreach (
$a as
$key => $value)
152 $v = $operation->getp(
$key);
153 $v = str_replace(
",",
".", $v);
155 if (trim($v) !=
"" && isNumber($v) == 0)
157 $operation->setp(
"code",
'N');
158 $msg.=
" $value n'est pas un nombre";
162 $operation->setp(
$key, $v);
165 if ($operation->getp(
"type") !=
"T")
167 $tva_id = $cn->get_array(
"select tva_id from impdol.parameter_tva where pt_rate/100=$1", array($operation->getp(
"rate")));
170 $operation->setp(
"code",
'N');
171 $msg.=
" Plusieurs code TVA correspondent à ce taux";
175 $operation->setp(
"code",
'N');
176 $msg.=
" Aucun code TVA ne correspond à ce taux";
180 $operation->setp(
"tva_id",
$tva_id[0][
'tva_id']);
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)
189 $operation->setp(
"code",
'N');
190 $msg.=
" Aucun client ou fournisseur";
194 $operation->setp(
"code",
'N');
195 $msg.=
" Aucune marchandise ou service";
199 $op_date = $operation->getp(
'date');
207 $msg.=$e->getMessage();
208 $operation->setp(
'code',
'N');
211 $operation->setp(
"message",
$msg);
212 $operation->update();
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));
224 print_r($e->getTraceAsString());
234 require_once NOALYSS_INCLUDE.
'/lib/class_html_table.php';
236 $sql =
" select o_doli,o_date,o_qcode,o_label,o_pj,amount_unit,
241 case when o_result='T' then '" . $g_succeed .
"' else '" . $g_failed .
"' end as result,
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')
260 ,
$sql,
'style="width:100%" class="result"'
267 $jrn = $_POST[
'p_jrn'];
268 $ledger =
new Acc_Ledger($cn, $jrn);
273 $ledger =
new Acc_Ledger_Purchase($cn, $jrn);
278 $ledger =
new Acc_Ledger_Sold($cn, $jrn);
283 die(
'Erreur ce type journal n\' est pas encore supporté');
288 $array = $cn->get_array(
"select
290 from impdol.operation_tmp
291 where i_id=$1 and o_result='T'
292 order by o_doli asc", array($this->impid));
298 for ($i = 0; $i <
$nb_row; $i++)
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)
307 echo
"Plusieurs clients pour l' opération, code " .
$array[$i][
'o_doli'];
310 if (count($atiers) == 0)
312 echo
"Pas de client pour une opération, code " .
$array[$i][
'o_doli'];
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);
324 for ($e = 0; $e < $nb_detail; $e++)
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);
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');
339 $jrnx->type = $oth_side;
341 $jrnx->user = $_SESSION[
'g_user'];
343 $jrnx->qcode = $oper->getp(
"qcode");
344 $jrnx->desc = mb_substr($oper->getp(
"desc"),0,80,
'UTF8');
345 $id = $jrnx->insert_jrnx();
348 $transfer->setp(
"j_id", $id);
349 $transfer->setp(
"o_id", $oper->getp(
"id"));
352 $tva_id = $oper->getp(
"tva_id");
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;
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)
369 ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)", array(null,
370 $oper->getp(
"fiche"),
371 $oper->getp(
"number_unit"),
375 $oper_tiers->getp(
'fiche'),
381 $sum_side = ($save_amount > 0) ? bcadd($sum_side, $amount_tvac):$sum_side;
385 $side=($amount_tva > 0)?1:0;
394 $sum = bcadd($sum, $amount_tvac);
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;
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();
414 foreach (
$atva as $tvaid=>$tva_value)
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;
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();
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));
446 $ledger->update_internal_code($internal);
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));
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));
464 print_r($e->getTraceAsString());
471 require_once NOALYSS_INCLUDE.
'/lib/class_html_table.php';
473 $sql =
" select distinct jr_id, jr_pj,jr_date, jr_comment,jr_internal
476 where jr_grpt_id in (
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">';
487 echo th(
"N° opération");
489 for ($i=0;$i<count($arow);$i++)
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>';
if(isset($_POST['ftvaadd'])) if(isset($_POST['mod'])) $atva
get data from database
check()
Check data contained into impdol.operation_tmp.
for($i=0;$i< Database::num_row($ret);$i++) $row
$type
Retrouve le type de row si == 3.
if(isset($_POST['remove'])) $array
if(isset($_POST['save'])) $tva
result()
Show the result in a table.