{
$db = DB::getInstance();
- $res = $db->prepare('SELECT
- journal.id,
- strftime(\'%d/%m/%Y\', date) AS date,
- (CASE cat.type WHEN 1 THEN \'Recette\' WHEN -1 THEN \'Dépense\' ELSE \'Autre\' END) AS type,
- (CASE cat.intitule WHEN NULL THEN \'\' ELSE cat.intitule END) AS cat,
- journal.libelle,
- montant,
- compte_debit,
- debit.libelle AS libelle_debit,
- compte_credit,
- credit.libelle AS libelle_credit,
- (CASE moyen_paiement WHEN NULL THEN \'\' ELSE moyen.nom END) AS moyen,
- numero_cheque,
- numero_piece,
- remarques
+ $retro_comptatibility = 'SELECT count(montant) AS montants FROM compta_flux WHERE compta_flux.id_journal = journal.id';
+ $res = $db->prepare('
+ SELECT
+ journal.id,
+ strftime(\'%d/%m/%Y\', date) AS date,
+ (CASE cat.type WHEN 1 THEN \'Recette\' WHEN -1 THEN \'Dépense\' ELSE \'Autre\' END) AS type,
+ (CASE cat.intitule WHEN NULL THEN \'\' ELSE cat.intitule END),
+ journal.libelle,
+ abs(flux.montant) AS montant,
+ (CASE WHEN flux.montant >= 0 THEN flux.compte ELSE \'\' END),
+ (CASE WHEN flux.montant >= 0 THEN flux_compte.libelle ELSE \'\' END) AS compte_debit_libelle,
+ (CASE WHEN flux.montant < 0 THEN flux.compte ELSE \'\' END),
+ (CASE WHEN flux.montant < 0 THEN flux_compte.libelle ELSE \'\' END) AS compte_credit_libelle,
+ (CASE moyen_paiement WHEN NULL THEN \'\' ELSE moyen.nom END) AS moyen,
+ numero_cheque,
+ numero_piece,
+ remarques
FROM compta_journal AS journal
LEFT JOIN compta_categories AS cat ON cat.id = journal.id_categorie
- LEFT JOIN compta_comptes AS debit ON debit.id = journal.compte_debit
- LEFT JOIN compta_comptes AS credit ON credit.id = journal.compte_credit
LEFT JOIN compta_moyens_paiement AS moyen ON moyen.code = journal.moyen_paiement
+ LEFT JOIN compta_flux AS flux ON flux.id_journal = journal.id
+ LEFT JOIN compta_comptes AS flux_compte ON flux_compte.id = flux.compte
WHERE id_exercice = '.(int)$exercice.'
- ORDER BY journal.date;
+ ORDER BY journal.id;
')->execute();
$fp = fopen('php://output', 'w');
};
$line = 0;
+ $last_id = 0;
+ $id = 0;
$delim = utils::find_csv_delim($fp);
+ unset($data);
while (!feof($fp))
{
$row = fgetcsv($fp, 4096, $delim);
+ print_r(["row" => $row]);
$line++;
if (empty($row))
throw new UserException('Erreur sur la ligne ' . $line . ' : la première colonne doit être vide ou contenir le numéro unique d\'opération.');
}
+ $last_id = $id;
$id = $col('Numéro mouvement');
- $date = $col('Date');
-
- if (!preg_match('!^\d{2}/\d{2}/\d{4}$!', $date))
+ if ($id == $last_id)
{
- $db->exec('ROLLBACK;');
- throw new UserException('Erreur sur la ligne ' . $line . ' : la date n\'est pas au format jj/mm/aaaa.');
+ $montant = $col('Montant');
+ $debit = $col('Compte de débit - numéro');
+ $credit = $col('Compte de crédit - numéro');
+
+ if (trim($debit) == '' && trim($credit) != '')
+ {
+ $debit = null;
+ $fluxs[] =
+ [ 'compte' => $credit
+ , 'montant' => - $montant
+ ];
+ }
+ if (trim($debit) != '' && trim($credit) == '')
+ {
+ $credit = null;
+ $fluxs[] =
+ [ 'compte' => $debit
+ , 'montant' => $montant
+ ];
+ }
}
+ else
+ {
+ if (isset($data)) {
+ // NOTE: import previously collected $last_id
+ $data['fluxs'] = $fluxs;
+ $id_journal = $db->simpleQuerySingle('SELECT id FROM compta_journal WHERE id = ?;', false, $last_id);
+ if (empty($last_id) || empty($id_journal))
+ {
+ print_r(["data" => $data]);
+ $journal->add($data);
+ }
+ else
+ {
+ print_r(['id' => $last_id, "data" => $data]);
+ $journal->edit($last_id, $data);
+ }
+ unset($data);
+ }
+
+ // NOTE: start collecting $id
+ $fluxs = [];
+ $date = $col('Date');
+
+ if (!preg_match('!^\d{2}/\d{2}/\d{4}$!', $date))
+ {
+ $db->exec('ROLLBACK;');
+ throw new UserException('Erreur sur la ligne ' . $line . ' : la date n\'est pas au format jj/mm/aaaa.');
+ }
- $date = explode('/', $date);
- $date = $date[2] . '-' . $date[1] . '-' . $date[0];
+ $date = explode('/', $date);
+ $date = $date[2] . '-' . $date[1] . '-' . $date[0];
- // En dehors de l'exercice courant
- if ($db->simpleQuerySingle('SELECT 1 FROM compta_exercices
- WHERE (? < debut OR ? > fin) AND cloture = 0;', false, $date, $date))
- {
- continue;
- }
+ // En dehors de l'exercice courant
+ if ($db->simpleQuerySingle('SELECT 1 FROM compta_exercices
+ WHERE (? < debut OR ? > fin) AND cloture = 0;', false, $date, $date))
+ {
+ continue;
+ }
- $debit = $col('Compte de débit - numéro');
- $credit = $col('Compte de crédit - numéro');
+ $cat = $col('Catégorie');
+ $moyen = strtoupper(substr($col('Moyen de paiement'), 0, 2));
- if (trim($debit) == '' && trim($credit) != '')
- {
- $debit = null;
- }
- elseif (trim($debit) != '' && trim($credit) == '')
- {
- $credit = null;
- }
+ if (!$moyen || !array_key_exists($moyen, $liste_moyens))
+ {
+ $moyen = false;
+ $cat = false;
+ }
- $cat = $col('Catégorie');
- $moyen = strtoupper(substr($col('Moyen de paiement'), 0, 2));
+ if ($cat && !array_key_exists($cat, $liste_cats))
+ {
+ $cat = $moyen = false;
+ }
- if (!$moyen || !array_key_exists($moyen, $liste_moyens))
- {
- $moyen = false;
- $cat = false;
- }
+ $montant = $col('Montant');
+ $debit = $col('Compte de débit - numéro');
+ $credit = $col('Compte de crédit - numéro');
- if ($cat && !array_key_exists($cat, $liste_cats))
- {
- $cat = $moyen = false;
- }
+ if (trim($debit) == '' && trim($credit) != '')
+ {
+ $debit = null;
+ $fluxs[] =
+ [ 'compte' => $credit
+ , 'montant' => - $montant
+ ];
+ }
+ if (trim($debit) != '' && trim($credit) == '')
+ {
+ $credit = null;
+ $fluxs[] =
+ [ 'compte' => $debit
+ , 'montant' => $montant
+ ];
+ }
- $data = [
- 'libelle' => $col('Libellé'),
- 'montant' => (float) $col('Montant'),
- 'date' => $date,
- 'compte_credit' => $credit,
- 'compte_debit' => $debit,
- 'numero_piece' => $col('Numéro de pièce'),
- 'remarques' => $col('Remarques'),
- ];
+ $data = [
+ 'libelle' => $col('Libellé'),
+ 'date' => $date,
+ 'numero_piece' => $col('Numéro de pièce'),
+ 'remarques' => $col('Remarques'),
+ ];
- if ($cat)
- {
- $data['moyen_paiement'] = $moyen;
- $data['numero_cheque'] = $col('Numéro de chèque');
- $data['id_categorie'] = $liste_cats[$cat];
+ if ($cat)
+ {
+ $data['moyen_paiement'] = $moyen;
+ $data['numero_cheque'] = $col('Numéro de chèque');
+ $data['id_categorie'] = $liste_cats[$cat];
+ }
}
-
- if (empty($id))
+ }
+ if (isset($data)) {
+ // NOTE: import previously collected $id
+ $data['fluxs'] = $fluxs;
+ $id_journal = $db->simpleQuerySingle('SELECT id FROM compta_journal WHERE id = ?;', false, $id);
+ if (empty($id) || empty($id_journal))
{
+ print_r(["data" => $data]);
$journal->add($data);
}
else
{
+ print_r(['id (end)' => $id, "data" => $data]);
$journal->edit($id, $data);
}
- }
+ unset($data);
+ }
$db->exec('END;');
}
}
-?>
\ No newline at end of file
+?>
dépôts / garradin.git / blobdiff