compte.ezt

{use $compte, $tableau}
<table>
  <thead>
    <tr><th>Débit</th><th colspan="4">{$compte}</th><th>Crédit</th></tr>
  </thead>
  <tbody>
    <tr>
      <th>Dates</th><th>Libellés</th><th>Sommes</th>
      <th>Dates</th><th>Libellés</th><th>Sommes</th>
    </tr>
    {foreach $tableau as $ligne}
      <tr>
        {if $ligne['credits']}<td>{$ligne['credits']['date']}</td><td>{$ligne['credits']['libelle']}</td><td>{$ligne['credits']['somme']}</td>{else}<td /><td /><td />{/if}
        {if $ligne['debits']}<td>{$ligne['debits']['date']}</td><td>{$ligne['debits']['libelle']}</td><td>{$ligne['debits']['somme']}</td>{else}<td /><td /><td />{/if}
      </tr>
    {/foreach}
  </tbody>
</table>

comptes.php

<?php
include_once 'ezc/Base/ezc_bootstrap.php';

$db = ezcDbFactory::create('sqlite:///home/charly/github.com/charlycoste/compta/db.sqlite');

$q = $db->createSelectQuery();
$e = $q->expr;
$q->select('*')
  ->from('comptes');

$stmt = $q->prepare();
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $flux1 = array('date'=>'30-12', 'libelle'=>'test1', 'somme'=>'10000');
    $flux2 = array('date'=>'31-12', 'libelle'=>'test2', 'somme'=>'15000');

    $lignes[] = array('credits'=>$flux1,'debits'=>$flux2);

$c="";

foreach ($rows as $row) {
    $code    = $row['code'];
    $parent  = $row['parent'];
    $libelle = $row['libelle'];

    $tpl = new ezcTemplate();
    $tpl->send->compte  = $libelle;
    $tpl->send->tableau = $lignes;

    $c .= $tpl->process( "compte.ezt" );
}

$tpl->send->content = $c;
echo $tpl->process("pagelayout.tpl");

flux.csv

29/11/13;Banque;350000;Capital
29/11/13;Immobilisations (terrains);400000;Capital
29/11/13;Immobilisations (constructions);1500000;Capital
29/11/13;Stocks (fournitures);5000;Capital
30/11/13;Banque;500000;Emprunts
01/12/13;Immobilisations (matériel);50000;Banque
02/12/13;Immobilisations (matériel);700000;Fournisseur Saviem
02/12/13;Caisse;10000;Banque
03/12/13;Charges (achats de bien);900;Caisse
05/12/13;Banque;8500;Produits (prestations de services)
10/12/13;Client Lambert;18400;Produits (prestations de services)
13/12/13;Charges (services reçus des entreprises);4000;Fournisseur Saviem
20/12/13;Charges (achats de bien);1500;Caisse
23/12/13;Client Michaud;25000;Produits (prestations de services)
24/12/13;Banque;18400;Client Lambert
28/12/13;Fournisseur Saviem;700000;Banque
31/12/13;Charges (services reçus du personnel);18000;Banque
31/12/13;Capital;10000;Banque
31/12/13;Prêts;10000;Banque

init_comptes.php

<?php
$dsn = '';
$csv = '';


include_once 'ezc/Base/ezc_bootstrap.php';

$db = ezcDbFactory::create($dsn);

if (($handle = fopen($csv, "r")) !== FALSE) {

    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {

        list($code, $parent, $libelle) = $data;

        $q = $db->createInsertQuery();
        $q->insertInto( 'comptes' )
          ->set('code', $q->bindValue($code))
          ->set('parent', $q->bindValue($parent))
          ->set('libelle', $q->bindValue($libelle));
        $stmt = $q->prepare();
        $stmt->execute();
    }

    fclose($handle);
}

init_db.php

<?php

$dsn = '';
$xml = 'schema.xml';

include_once 'ezc/Base/ezc_bootstrap.php';

$db = ezcDbFactory::create($dsn);

$xmlSchema = ezcDbSchema::createFromFile('xml', $xml);

$dbSchema = ezcDbSchema::createFromDb($db);

$diffSchema = ezcDbSchemaComparator::compareSchemas( $dbSchema, $xmlSchema );

$diffSchema->applyToDB( $db );

init_flux.php

<?php

$dsn = '';
$csv = 'flux.csv';

include_once 'ezc/Base/ezc_bootstrap.php';

$db  = ezcDbFactory::create($dsn);

$q = $db->createSelectQuery();
$q->select('*')
  ->from('comptes');
$stmt = $q->prepare();
$stmt->execute();
$rows = $stmt->fetchAll();

$keys   = array_map(function($n){return $n["libelle"];}, $rows);
$values = array_map(function($n){return $n["code"];}, $rows);
$map    = array_combine($keys, $values);

if (($handle = fopen($csv, "r")) !== FALSE) {

    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {

        list($date, $crediteur, $valeur, $debiteur) = $data;

        if(!in_array($crediteur, $keys)) {
            throw new Exception("crediteur $crediteur non connu");
        }

        if(!in_array($debiteur, $keys)) {
            throw new Exception("debiteur $debiteur non connu");
        }

        $q = $db->createInsertQuery();
        $q->insertInto( 'flux' )
          ->set('date', $q->bindValue($date))
          ->set('crediteur', $q->bindValue($map[$crediteur]))
          ->set('valeur', $q->bindValue($valeur))
          ->set('debiteur', $q->bindValue($map[$debiteur]));
        $stmt = $q->prepare();
        $stmt->execute();
    }
    fclose($handle);
}

pagelayout.ezt

{use $content}
<html>
<head>
  <title></title>
  <meta http-equiv="content-type" content="text/html; charset=utf-8" />
  <style type="text/css">
    {literal}
    body {
        font-family: mono;
    }
    table {
      border-collapse:collapse;
      margin: 1em;
      float: left;
    }
    thead {
      background: #ccc;
    }
    td {
      padding: 0 1em;
    }
    {/literal}
  </style>
</head>
<body>
{raw $content}
</body>
</html>

pcg.csv

1;;Comptes de capitaux
10;1;Capital et réserves
101;10;Capital
512;;Banque
211;;Immobilisations (terrains)
213;;Immobilisations (constructions)
215;;Immobilisations (matériel)
16;;Emprunts
401.001;;Fournisseur Saviem
513;;Caisse
706;;Produits (prestations de services)
401.002;;Fournisseur Saviem
411.001;;Client Lambert
274;;Prêts
641;;Charges (services reçus du personnel)
607;;Charges (achats de bien)
604;;Charges (services reçus des entreprises)
31;;Stocks (fournitures)

schema.xml

<?xml version="1.0" encoding="UTF-8"?>
<database>
 <table>
  <name>comptes</name>
  <declaration>
   <field>
    <name>code</name>
    <type>integer</type>
    <autoincrement>true</autoincrement>
    <notnull>true</notnull>
   </field>
   <field>
    <name>parent</name>
    <type>integer</type>
   </field>
   <field>
    <name>libelle</name>
    <type>text</type>
   </field>
   <index>
    <name>primary1</name>
    <primary>true</primary>
    <unique>true</unique>
    <field>
     <name>code</name>
    </field>
   </index>
  </declaration>
 </table>
 <table>
  <name>flux</name>
  <declaration>
   <field>
    <name>id</name>
    <type>integer</type>
    <autoincrement>true</autoincrement>
    <notnull>true</notnull>
   </field>
   <field>
    <name>date</name>
    <type>date</type>
   </field>
   <field>
    <name>crediteur</name>
    <type>text</type>
   </field>
   <field>
    <name>debiteur</name>
    <type>text</type>
   </field>
   <field>
    <name>valeur</name>
    <type>float</type>
   </field>
   <index>
    <name>primary2</name>
    <primary>true</primary>
    <unique>true</unique>
    <field>
     <name>id</name>
    </field>
   </index>
  </declaration>
 </table>
</database>