[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpcompta-dev] r322 - in trunk/import_doli: . doc include
From: |
phpcompta-dev |
Subject: |
[Phpcompta-dev] r322 - in trunk/import_doli: . doc include |
Date: |
Thu, 29 Mar 2012 23:14:13 +0200 (CEST) |
Author: danydb
Date: 2012-03-29 23:14:13 +0200 (Thu, 29 Mar 2012)
New Revision: 322
Modified:
trunk/import_doli/doc/import-donnee-dolibarr.ods
trunk/import_doli/include/class_impdol_operation.php
trunk/import_doli/include/class_install_impdol.php
trunk/import_doli/include/class_sql_impdol.php
trunk/import_doli/include/imd_operation.inc.php
trunk/import_doli/index.php
Log:
impdoli transfer to Ledger purchase
Modified: trunk/import_doli/doc/import-donnee-dolibarr.ods
===================================================================
(Binary files differ)
Modified: trunk/import_doli/include/class_impdol_operation.php
===================================================================
--- trunk/import_doli/include/class_impdol_operation.php 2012-03-28
23:57:15 UTC (rev 321)
+++ trunk/import_doli/include/class_impdol_operation.php 2012-03-29
21:14:13 UTC (rev 322)
@@ -27,6 +27,7 @@
*
*/
require_once 'class_sql_impdol.php';
+
class Impdol_Operation
{
@@ -40,46 +41,336 @@
$this->filename = tempnam($_ENV['TMP'], 'upload_');
move_uploaded_file($_FILES["csv_operation"]["tmp_name"],
$this->filename);
- $imp=new Impdol_Import_sql();
- $imp->setp('temp_file',$this->filename);
- $imp->setp('send_file',$_FILES['csv_operation']['name']);
+ $imp = new Impdol_Import_sql();
+ $imp->setp('temp_file', $this->filename);
+ $imp->setp('send_file', $_FILES['csv_operation']['name']);
$imp->insert();
- $this->impid=$imp->getp("id");
+ $this->impid = $imp->getp("id");
}
function record()
{
- $foperation= fopen($this->filename, 'r');
+ $foperation = fopen($this->filename, 'r');
$this->row_count = 0;
$max = 0;
while (($row = fgetcsv($foperation, 0, ";", '"')) !== false)
{
- if ( count($row) != 10 )
+ if (count($row) != 11)
{
echo "Attention $row ne contient pas 10
colonnes";
continue;
}
- $r=new impdol_Operation_tmp_Sql();
- $r->setp('dolibarr',$row[0]);
- $r->setp('date',$row[1]);
- $r->setp('qcode',$row[2]);
- $r->setp('desc',$row[3]);
- $r->setp('pj',$row[4]);
- $r->setp('amount_unit',$row[5]);
- $r->setp('amount_vat',$row[6]);
- $r->setp('rate',$row[8]);
- $r->setp('amount_total',$row[9]);
- $r->setp("import_id",$this->impid);
+ $r = new impdol_Operation_tmp_Sql();
+ $r->setp('dolibarr', $row[0]);
+ $r->setp('date', $row[1]);
+ $r->setp('type', $row[2]);
+ $r->setp('qcode', $row[3]);
+ $r->setp('desc', $row[4]);
+ $r->setp('pj', $row[5]);
+ $r->setp('amount_unit', $row[6]);
+ $r->setp('amount_vat', $row[7]);
+ $r->setp('number_unit', $row[8]);
+ $r->setp('rate', $row[9]);
+ $r->setp('amount_total', $row[10]);
+ $r->setp("import_id", $this->impid);
$r->insert();
$this->row_count++;
}
- echo "Nombre de lignes enregistrées : ".$this->row_count;
- $import=new impdol_import_sql($this->impid);
- $import->setp("nbrow",$this->row_count);
+ echo "Nombre de lignes enregistrées : " . $this->row_count;
+ $import = new impdol_import_sql($this->impid);
+ $import->setp("nbrow", $this->row_count);
$import->update();
}
+ /**
+ * Check data contained into impdol.operation_tmp. Update the column
o_result (T = ok N no ok)
+ * Put in o_message the reason of the problem
+ */
+ function check()
+ {
+ global $cn;
+ $array = $cn->get_array("select o_id from impdol.operation_tmp
where i_id=$1 order by o_id", array($this->impid));
+ $nb_row = count($array);
+ for ($i = 0; $i < $nb_row; $i++)
+ {
+ $msg = "";
+ $operation = new impdol_operation_tmp_sql();
+ $operation->setp("id", $array[$i]['o_id']);
+ $operation->load();
+ $operation->setp("code", 'T');
+ $dol = $operation->getp("dolibarr");
+ if (trim($dol) == "" || isNumber($dol) == 0)
+ {
+ $operation->setp("code", 'N');
+ $msg.=" le numéro de ligne pour dolibarr est
invalide";
+ }
+ if (isDate($operation->getp("date")) == null)
+ {
+ $operation->setp("code", 'N');
+ $msg.=" La date est invalide, format n'est pas
JJ.MM.AAAA";
+ }
+ $fiche = new Fiche($cn);
+ $fiche->get_by_qcode(trim($operation->getp("qcode")));
+ if ($fiche->id == 0)
+ {
+ $operation->setp("code", 'N');
+ $msg.=" Cette fiche n'existe pas";
+ }
+ else
+ {
+ $operation->setp("fiche", $fiche->id);
+ }
+ $poste = $fiche->strAttribut(ATTR_DEF_ACCOUNT);
+ if ( trim($poste) == '' || $cn->get_value("select
count(*) from tmp_pcmn where pcm_val=$1",array($poste))==0) {
+ $operation->setp("code", 'N');
+ $msg.=" Cette fiche n'a pas de poste comptable
valide";
+ }
+ $operation->setp("poste", $poste);
+ $a = array("rate" => " Taux de TVA", "amount_total" =>
"Montant total", "number_unit" => 'Nombre d\'unité', "amount_vat" => "Montant
TVA");
+
+ foreach ($a as $key => $value)
+ {
+ $v = $operation->getp($key);
+ $v = str_replace(",", ".", $v);
+
+ if (trim($v) != "" && isNumber($v) == 0)
+ {
+ $operation->setp("code", 'N');
+ $msg.=" $value n'est pas un nombre";
+ continue;
+ }
+
+ $operation->setp($key, $v);
+ }
+ /**
+ * @todo
+ * check if card as a valid accounting
+ */
+ if ($operation->getp("type") != "T")
+ {
+ $tva_id = $cn->get_array("select tva_id from
impdol.parameter_tva where pt_rate/100=$1", array($operation->getp("rate")));
+ if (count($tva_id) > 1)
+ {
+ $operation->setp("code", 'N');
+ $msg.=" Plusieurs code TVA
correspondent à ce taux";
+ }
+ elseif (empty($tva_id))
+ {
+ $operation->setp("code", 'N');
+ $msg.=" Aucun code TVA ne correspond à
ce taux";
+ }
+ else
+ {
+ $operation->setp("tva_id",
$tva_id[0]['tva_id']);
+ }
+ }
+
+ $operation->setp("message", $msg);
+ $operation->update();
+ }
+ /*
+ * If a part is N then the whole operation is N
+ */
+ $sql = "update impdol.operation_tmp set o_result='N' where
i_id=$1 and
+ o_doli in (select o_doli from
impdol.operation_tmp where o_result='N' and i_id=$1)";
+ $cn->exec_sql($sql, array($this->impid));
+ }
+
+ /**
+ * Show the result in a table
+ */
+ function result()
+ {
+ require_once 'class_html_table.php';
+ global $cn, $g_failed, $g_succeed;
+ $sql = " select o_doli,o_date,o_qcode,o_label,o_pj,amount_unit,
+ amount_vat,
+ number_unit,
+ vat_rate,
+ amount_total,
+ case when o_result='T' then '" . $g_succeed . "' else
'" . $g_failed . "' end as result,
+ o_message
+ from impdol.operation_tmp where i_id=" . $this->impid .
" order by o_id";
+ echo Html_Table::sql2table($cn, array(
+ array('name' => 'n° ligne',
+ 'style' => 'style="text-align:right"'),
+ array('name' => 'Date',
+ 'style' => 'text-align:right'),
+ array('name' => 'QuickCode'),
+ array('name' => 'Libellé'),
+ array('name' => 'n° pj'),
+ array('name' => 'Montant / unité', 'style' =>
'style="text-align:right"'),
+ array('name' => 'Montant Total TVA', 'style' =>
'style="text-align:right"'),
+ array('name' => 'Nbre unités', 'style' =>
'style="text-align:right"'),
+ array('name' => 'taux TVA', 'style' =>
'style="text-align:right"'),
+ array('name' => 'Montant total TVAC', 'style' =>
'style="text-align:right"'),
+ array('name' => 'Transfert', 'style' =>
'style="text-align:right"', 'raw' => 1),
+ array('name' => 'Message')
+ )
+ , $sql, 'style="width:80%" class="result"'
+ );
+ }
+
+ function transfer()
+ {
+ global $cn;
+ $jrn = $_POST['p_jrn'];
+ $ledger = new Acc_Ledger($cn, $jrn);
+ $type = $ledger->get_type();
+ switch ($type)
+ {
+ case 'ACH':
+ $ledger = new Acc_Ledger_Purchase($cn, $jrn);
+ break;
+ case 'VEN':
+ $ledger = new Acc_Ledger_Sale($cn, $jrn);
+ break;
+ default:
+ die('Erreur ce type journal n\' est pas encore
supporté');
+ }
+ /**
+ * Loop in table operation_tmp, get all the record to transfer
+ */
+ $array = $cn->get_array("select
+ distinct o_doli
+ from impdol.operation_tmp
+ where i_id=$1 and o_result='T'
+ order by o_doli asc",array($this->impid));
+ $nb_row = count($array);
+ bcscale(2);
+ for ($i = 0; $i < $nb_row; $i++)
+ {
+ /*
+ * For each operation (same o_doli code)
+ */
+ $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));
+ $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));
+ if (count($atiers) > 1)
+ {
+ echo "Plusieurs clients pour une opération";
+ var_dump($atiers);
+ continue;
+ }
+ if (count($atiers) == 0)
+ {
+ echo "Pas de client pour une opération";
+ continue;
+ }
+
+ $oper_tiers = new
Impdol_Operation_Tmp_Sql($atiers[0]['o_id']);
+ $nb_detail = count($adetail);
+ $sum = 0;
+ $grpt = $cn->get_value("select nextval('s_grpt');");
+ $internal = $ledger->compute_internal_code($grpt);
+
+ $tva = array();
+ /* record all S record */
+ for ($e = 0; $e < $nb_detail; $e++)
+ {
+ /* Record service */
+ $oper = new
Impdol_Operation_Tmp_Sql($adetail[$e]['o_id']);
+ $oper->from_array($array[$i]);
+ $date = format_date($oper->getp("date"),
"YYYY-MM-DD", "DD.MM.YYYY");
+ $oper->setp("date", $date);
+ $jrnx = new Acc_Operation($cn);
+ $jrnx->date = $date;
+ $amount_tva = $oper->getp("amount_vat");
+ $amount_tvac = $oper->getp("amount_total");
+ $jrnx->amount = bcsub($amount_tvac,
$amount_tva);
+ $jrnx->poste = $oper->getp('poste');
+ $jrnx->grpt = $grpt;
+ $jrnx->type = 'd';
+ $jrnx->jrn = $jrn;
+ $jrnx->user = $_SESSION['g_user'];
+ $jrnx->periode = 0;
+ $jrnx->qcode = $oper->getp("qcode");
+ $jrnx->desc = $oper->getp("desc");
+ $id = $jrnx->insert_jrnx();
+
+ $transfer = new impdol_operation_transfer_sql();
+ $transfer->setp("j_id", $id);
+ $transfer->setp("o_id", $oper->getp("id"));
+ $transfer->insert();
+
+ $tva_id = $oper->getp("tva_id");
+
+ /*
+ * Save into quant_purchase or quant_sale
+ */
+ switch ($type)
+ {
+ case 'ACH':
+ print_r($oper->get_info());
+ $sql = "insert into
quant_purchase(qp_internal,j_id,qp_fiche,qp_quantite,qp_price,qp_vat,qp_vat_code,qp_supplier)
+
values($1,$2,$3,$4,$5,$6,$7,$8)";
+ $cn->exec_sql($sql, array(null,
$id, $oper->getp("fiche"), $oper->getp("number_unit"),$jrnx->amount ,
$amount_tva,$tva_id, $oper_tiers->getp("fiche")));
+ break;
+ case 'VEN':
+ break;
+ }
+ /* save VAT into an array */
+ if (isset($tva[$tva_id]))
+ {
+ $tva[$tva_id] = bcadd($tva[$tva_id],
$amount_tva);
+ }
+ else
+ {
+ $tva[$tva_id] = $amount_tva;
+ }
+ $sum = bcadd($sum, $amount_tvac);
+ } // loop e
+ // Record the tiers
+
+ $jtiers = new Acc_Operation($cn);
+ $jtiers->date = $date;
+ $jtiers->amount = $sum;
+ $jtiers->poste = $oper_tiers->getp('poste');
+ $jtiers->grpt = $grpt;
+ $jtiers->type = 'c';
+ $jtiers->jrn = $jrn;
+ $jtiers->user = $_SESSION['g_user'];
+ $jtiers->periode = 0;
+ $jtiers->qcode = $oper_tiers->getp("qcode");
+ $jtiers->desc = $oper_tiers->getp("desc");
+ $jtiers->insert_jrnx();
+
+ /* Record the vat 1 */
+ foreach ($tva as $key => $value)
+ {
+ $tva = new Acc_TVA($cn, $key);
+ $tva->load();
+ $poste = $tva->get_side("d");
+ $op_tva = new Acc_Operation($cn);
+ $op_tva->date = $date;
+ $op_tva->amount = $value;
+ $op_tva->poste = $poste;
+ $op_tva->grpt = $grpt;
+ $op_tva->type = 'd';
+ $op_tva->jrn = $jrn;
+ $op_tva->user = $_SESSION['g_user'];
+ $op_tva->periode = 0;
+ $op_tva->qcode = null;
+ $op_tva->desc = $tva->tva_label;
+ $op_tva->insert_jrnx();
+ }
+
+ /* record into jrn */
+ $acc_jrn = new Acc_Operation($cn);
+ $acc_jrn->jrn = $jrn;
+ $acc_jrn->amount = $sum;
+ $acc_jrn->desc = $oper_tiers->getp("desc");
+ $acc_jrn->date = $date;
+ $acc_jrn->grpt = $grpt;
+ $acc_jrn->periode = 0;
+ $acc_jrn->insert_jrn();
+
+ /* Update info */
+ $ledger->grpt_id = $grpt;
+ $ledger->update_internal_code($internal);
+ }// loop i
+ }
+
}
?>
Modified: trunk/import_doli/include/class_install_impdol.php
===================================================================
--- trunk/import_doli/include/class_install_impdol.php 2012-03-28 23:57:15 UTC
(rev 321)
+++ trunk/import_doli/include/class_install_impdol.php 2012-03-29 21:14:13 UTC
(rev 322)
@@ -41,6 +41,7 @@
)
");
$cn->exec_sql("insert into impdol.version(v_id,v_text",
array(1, "Installation"));
+
$cn->exec_sql('
CREATE TABLE impdol.parameter_tva
(
@@ -50,6 +51,59 @@
CONSTRAINT parameter_tva_pkey PRIMARY
KEY (pt_id )
)
');
+
+ $cn->exec_sql("
+ CREATE TABLE impdol.operation_transfer
+ (
+ ot_id serial NOT NULL,
+ j_id bigint,
+ o_id bigint,
+ CONSTRAINT operation_transfer_pkey PRIMARY KEY
(ot_id ),
+ CONSTRAINT operation_transfer_j_id_fkey FOREIGN
KEY (j_id)
+ REFERENCES jrnx (j_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT operation_transfer_o_id_fkey FOREIGN
KEY (o_id)
+ REFERENCES impdol.operation_tmp (o_id)
MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+ )");
+
+
+ $cn->exec_sql("
+ CREATE TABLE impdol.operation_tmp
+ (
+ o_id bigserial NOT NULL,
+ o_doli text,
+ o_date text,
+ o_qcode text,
+ f_id text,
+ o_label text,
+ o_pj text,
+ amount_unit text,
+ amount_vat text,
+ number_unit text,
+ vat_rate text,
+ amount_total text,
+ jrn_def_id text,
+ o_message text,
+ i_id bigint,
+ o_result char,
+ tva_id bigint,
+ o_type char,
+ o_poste text,
+ CONSTRAINT operation_tmp_pkey PRIMARY
KEY (o_id ),
+ CONSTRAINT operation_tmp_i_id_fkey
FOREIGN KEY (i_id)
+ REFERENCES impdol.import (i_id)
MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE
CASCADE
+ )
+ ");
+ $cn->exec_sql("
+ COMMENT ON COLUMN impdol.operation_tmp.o_result IS
'result is T can be transferrable, N cannot be transferrable';
+ ");
+
+ $cn->exec_sql("
+ COMMENT ON COLUMN impdol.operation_tmp.o_type IS 'S =
marchandise, serviceT = tiers (fournisseurs, client)
+ ");
+
}
}
Modified: trunk/import_doli/include/class_sql_impdol.php
===================================================================
--- trunk/import_doli/include/class_sql_impdol.php 2012-03-28 23:57:15 UTC
(rev 321)
+++ trunk/import_doli/include/class_sql_impdol.php 2012-03-29 21:14:13 UTC
(rev 322)
@@ -258,9 +258,12 @@
"rate"=>"vat_rate",
"amount_total"=>"amount_total",
"jrn_def_id"=>"jrn_def_id",
- "o_message"=>"o_message",
+ "message"=>"o_message",
"import_id"=>"i_id",
- "code"=>"o_result"
+ "code"=>"o_result",
+ "tva_id"=>"tva_id",
+ "type"=>"o_type",
+ "poste"=>"o_poste"
);
@@ -280,7 +283,10 @@
"jrn_def_id"=>"text",
"o_message"=>"text",
"i_id"=>"numeric",
- "o_result"=>'text'
+ "o_result"=>'text',
+ "tva_id"=>'numeric',
+ "o_type"=>'text',
+ "o_poste"=>"text"
);
$this->default = array(
@@ -327,4 +333,34 @@
parent::__construct($cn,$p_id);
}
}
+
+
+class impdol_operation_transfer_sql extends sql_impdol
+{
+ function __construct($p_id=-1)
+ {
+ $this->table = "impdol.operation_transfer";
+ $this->primary_key = "ot_id";
+
+ $this->name=array(
+ "id"=>"ot_id",
+ "j_id"=>"j_id",
+ "o_id"=>"o_id"
+ );
+
+ $this->type = array(
+ "ot_id"=>'numeric',
+ "j_id"=>'numeric',
+ "o_id"=>'numeric',
+ );
+
+ $this->default = array(
+ "i_id" => "auto"
+ );
+ $this->date_format = "DD.MM.YYYY";
+ global $cn;
+
+ parent::__construct($cn,$p_id);
+ }
+}
?>
\ No newline at end of file
Modified: trunk/import_doli/include/imd_operation.inc.php
===================================================================
--- trunk/import_doli/include/imd_operation.inc.php 2012-03-28 23:57:15 UTC
(rev 321)
+++ trunk/import_doli/include/imd_operation.inc.php 2012-03-29 21:14:13 UTC
(rev 322)
@@ -45,15 +45,27 @@
// record the file into the table operation
$io->record();
- // show the result + error
+ // show the data
$io->check();
+
+ // show the result
$io->result();
+ echo '<form method="POST">';
+ echo "<p class=\"notice\">Les opérations qui ne sont pas marquées comme
correctes ne seront pas transfèrées </p>";
+ echo HtmlInput::hidden("impid",$io->impid);
+ $l=new Acc_Ledger($cn,0);
+ echo "Vers le journal ".$l->select_ledger("ALL", 3)->input();
+ echo HtmlInput::submit("transfer","Transfert des opérations");
+ echo "</FORM>";
}
// step 3, insert data into the target ledger
if ( isset ($_POST['transfer']))
{
-
+ $io=new Impdol_Operation();
+ $io->impid=$_POST['impid'];
+ $io->transfer();
+ var_dump($_POST);
}
?>
Modified: trunk/import_doli/index.php
===================================================================
--- trunk/import_doli/index.php 2012-03-28 23:57:15 UTC (rev 321)
+++ trunk/import_doli/index.php 2012-03-29 21:14:13 UTC (rev 322)
@@ -28,6 +28,10 @@
* load javascript
*/
require_once('include/class_import_card.php');
+require_once('class_acc_ledger.php');
+require_once('class_acc_ledger_purchase.php');
+require_once('class_acc_ledger_sold.php');
+require_once('class_acc_operation.php');
global $cn;
echo '<div style="float:right"><a class="mtitle" style="font-size:140%"
href="http://wiki.phpcompta.eu/doku.php?id=importation_dolibarr"
target="_blank">Aide</a>'.
'<span style="font-size:0.8em;color:red;display:inline">vers:SVNINFO</span>'.
---
PhpCompta est un logiciel de comptabilité libre en ligne (full web)
Projet opensource http://www.phpcompta.eu
[Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Phpcompta-dev] r322 - in trunk/import_doli: . doc include,
phpcompta-dev <=