rapprochement_gestion_compta: first working version. outputs CSV for now

author: Ludovic Pouzenc <ludovic@pouzenc.fr> 2017-12-24 23:15:06 +0100
committer: Ludovic Pouzenc <ludovic@pouzenc.fr> 2017-12-24 23:15:06 +0100
commit: 19a576a64ffb5834240dbb83b1d6d88db85654c4 (patch)
tree: 44a26eee7290c0f0466d1c6fe77f1682eeeb74c1
parent: f4e2e322c979d8131736b52c507efee8d35e4bda (diff)
download: chd_gestion-19a576a64ffb5834240dbb83b1d6d88db85654c4.zip
chd_gestion-19a576a64ffb5834240dbb83b1d6d88db85654c4.tar.gz
chd_gestion-19a576a64ffb5834240dbb83b1d6d88db85654c4.tar.bz2
1 files changed, 311 insertions, 18 deletions
diff --git a/api/rapprochement_gestion_compta.php b/api/rapprochement_gestion_compta.php
index 9a99844..5e1b44d 100644
--- a/api/rapprochement_gestion_compta.php
+++ b/api/rapprochement_gestion_compta.php
@@ -18,6 +18,58 @@
  * along with CHD Gestion.  If not, see <http://www.gnu.org/licenses/>.
  **/
 
+$YEAR=2017;
+
+function csv_encoding_to_utf8($text) {
+  return mb_convert_encoding($text, 'UTF-8', 'WINDOWS-1252');
+}
+
+function fix_csv_categorie($data) {
+  return ($data['motif'][0]==='C')?'adh':'abo';
+}
+
+function fix_csv_periodicite($data) {
+  return ($data['typeech']==='PONCTUEL')?'PONCTUEL':$data['periodicite'];
+}
+
+function fix_csv_adt($csv_value) {
+  $csv_adt_fixes = array(
+    '27635373' => 'CHD429',
+    '39571010' => 'CHD430',
+    '92450244' => 'CHD414',
+    '61642823' => 'CHD444',
+    '49427005' => 'CHD457',
+    '20584291' => 'CHD432',
+    '4060826' => 'CHD426',
+    '43146809' => 'CHD461',
+    '19776933' => 'CHD455',
+    '86308363' => 'CHD420',
+    '67262793' => 'CHD416',
+    '13862724' => 'CHD381',
+    '17875035' => 'CHD418',
+    '5425085' => 'CHD436',
+    '24055458' => 'CHD453',
+    '2698161' => 'CHD458',
+    '80895435' => 'CHD377',
+    '24015374' => 'CHD447',
+   // '60768027' => '???', // GOUDENNE ??
+    '60549585' => 'CHD454',
+    '6330693' => 'CHD439',
+    '41697640' => 'CHD413',
+    '83848700' => 'CHD441',
+    '1029656' => 'CHD443',
+    '92566210' => 'CHD435',
+    '23242743' => 'CHD449',
+    '21935178' => 'CHD451',
+    '39053250' => 'CHD446',
+    '10162304' => 'CHD437',
+    '15336435' => 'CHD431',
+    'CHD 433' => 'CHD433',
+  );
+  return isset($csv_adt_fixes[$csv_value])?$csv_adt_fixes[$csv_value]:$csv_value;
+}
+
+
 // Undefined | Multiple Files | $_FILES Corruption Attack
 // If this request falls under any of them, treat it invalid.
 if ( !isset($_FILES['upfile']['error']) || is_array($_FILES['upfile']['error']) ) {
@@ -26,7 +78,7 @@ if ( !isset($_FILES['upfile']['error']) || is_array($_FILES['upfile']['error'])
 <html>
 <body>
 <form method="post" enctype="multipart/form-data">
-  Envoi de l'export CSV de l'échéancier Crédit Coop<br>
+  Envoi de l'export CSV des échéances Crédit Coop<br>
   <input type="file" name="upfile" id="upfile"><br>
   <input type="submit" name="submit">
 </form>
@@ -37,6 +89,8 @@ if ( !isset($_FILES['upfile']['error']) || is_array($_FILES['upfile']['error'])
 }
 
 header('Content-Type: text/plain; charset=utf-8');
+header('Content-Disposition: attachment; filename=rapprochement' . $YEAR . '.csv'); 
+
 include_once('inc/config.php');
 $mysqli = new mysqli($db_config['host'], $db_config['username'], $db_config['password'], $db_config['database']);
 if (mysqli_connect_errno()) {
@@ -53,14 +107,14 @@ switch ($_FILES['upfile']['error']) {
     die('No file sent.');
   case UPLOAD_ERR_INI_SIZE:
   case UPLOAD_ERR_FORM_SIZE:
-    die('Exceeded filesize limit.');
+    die('Exceeded php filesize limit.');
   default:
     die('Unknown errors.');
 }
 
 // You should also check filesize here.
-if ($_FILES['upfile']['size'] > 1000000) {
-  die('Exceeded filesize limit.');
+if ($_FILES['upfile']['size'] > 2000000) {
+  die('Exceeded soft filesize limit.');
 }
 
 // DO NOT TRUST $_FILES['upfile']['mime'] VALUE !!
@@ -77,37 +131,276 @@ if ( $headers === FALSE ) {
   die('Can\'t read CSV headers');
 }
 $numcol = count($headers);
-echo "CSV $numcol colonnes\n";
+//echo "CSV $numcol colonnes\n";
+
+$headers = array_map('csv_encoding_to_utf8', $headers);
+//print_r($headers);
 
+$labels = array(
+  'motif' => 'Motif de l\'échéancier',
+  'adt' => 'Numéro du débiteur',
+  'montant' => 'Montant de l\'échéance en Euro',
+  'date_ech' => 'Date d\'échéance',
+  'statut' => 'Statut échéance',
+  'raison' => 'Raison du Statut',
+  'date_op' => 'Date d\'opération',
+  'typeech' => 'Type d\'échéancier',
+  'periodicite' => 'Périodicité si prélèvement permanent',
+  'categorie' => 'Code catégorie débiteur',
+);
+$cols = array();
+foreach ( $labels as $k => $l ) {
+  $i = array_search($l, $headers, true) or die ("Pas de colonne '$l' sur la ligne d'entête du CSV");
+  //echo "\$k == $k; \$l == $l; \$i == $i\n";
+  $cols[$k]=$i;
+}
+//print_r($cols);
 
-$res = $mysqli->query("SELECT * FROM v_rapprochement_compta WHERE actif2016='oui'");
+$res = $mysqli->query("SELECT * FROM v_rapprochement_compta WHERE actif$YEAR='oui'");
 if ( $res === FALSE ) {
 	die("Wrong query");
 }
-$sqldata = array();
-$sqlidx_adt_svctype = array();
+$svcdata = array();
+$idx_adt_svctype = array();
 $row = 0;
 while ( ($data = $res->fetch_assoc()) !== NULL ) {
   $row++;
-  $sqldata[$row] = $data;
+  $svcdata[$row] = $data;
   $k = $data['adt'] . '_' . ($data['service_type']==='Adhésion'?'adh':'abo');
-  if ( !isset($sqlidx_adt_svctype[$k])) {
-    $sqlidx_adt_svctype[$k] = array();
+  if ( !isset($idx_adt_svctype[$k])) {
+    $idx_adt_svctype[$k] = array();
+  }
+  if ( !isset($idx_adt_svctype[$k]['svc'])) {
+    $idx_adt_svctype[$k]['svc'] = array();
   }
-  $sqlidx_adt_svctype[$k][] = $row;
+  $idx_adt_svctype[$k]['svc'][] = $row;
 }
-//print_r($sqlidx_adt_svctype);
-echo "SQL " . ($row) . " lignes\n";
+//print_r($svcidx_adt_svctype);
+//echo "Services : $row lignes\n";
 
+$echdata = array();
 $row = 1;
-while (($data = fgetcsv($fhcsv, 1200, ";")) !== FALSE ) {
+while (($line = fgetcsv($fhcsv, 1200, ";")) !== FALSE ) {
   $row++;
-  if ( count($data) !== $numcol ) {
+  if ( count($line) !== $numcol ) {
     echo "CSV skipping line $row : bad col count\n";
     continue;
   }
-  //
+  $data = array();
+  foreach ( $cols as $k => $i ) {
+    $data[$k] = $line[$i];
+  }
+  $data = array_map('csv_encoding_to_utf8', $data);
+  $data['adt'] = fix_csv_adt($data['adt']);
+  $data['categorie'] = fix_csv_categorie($data);
+  $data['periodicite'] = fix_csv_periodicite($data);
+  $echdata[$row] = $data;
+  $k = $data['adt'] . '_' . $data['categorie'];
+  if ( !isset($idx_adt_svctype[$k])) {
+    $idx_adt_svctype[$k] = array();
+  }
+  if ( !isset($idx_adt_svctype[$k]['ech'])) {
+    $idx_adt_svctype[$k]['ech'] = array();
+  }
+  $idx_adt_svctype[$k]['ech'][] = $row;
 }
 fclose($fhcsv);
-echo "CSV " . ($row-1) . " lignes\n";
+//echo "CSV " . ($row-1) . " lignes\n";
+
+//print_r($idx_adt_svctype);
+/*
+    [CHD340_adh] => Array
+        (
+            [svc] => Array
+                (
+                    [0] => 561
+                )
+
+            [ech] => Array
+                (
+                    [0] => 22
+                )
+
+        )
+
+    [CHD340_abo] => Array
+        (
+            [svc] => Array
+                (
+                    [0] => 562
+                )
+
+            [ech] => Array
+                (
+                    [0] => 23
+                    [1] => 24
+                    [2] => 25
+                    [3] => 26
+                    [4] => 27
+                    [5] => 28
+                    [6] => 29
+                    [7] => 30
+                    [8] => 31
+                    [9] => 32
+                    [10] => 33
+                    [11] => 34
+                )
+        )
+*/
+$data = $idx_adt_svctype;
+
+foreach ( $data as $k => &$v ) {
+  if ( isset($v['svc']) ) {
+    foreach ( array_keys($v['svc']) as $sk ) {
+      $v['svc'][$sk] = $svcdata[ $v['svc'][$sk] ];
+    }
+  }
+  if ( isset($v['ech']) ) {
+    foreach ( array_keys($v['ech']) as $sk ) {
+      $v['ech'][$sk] = $echdata[ $v['ech'][$sk] ];
+    }
+  }
+}
+
+//print_r($data);
+
+foreach ( $data as $k => &$v ) {
+  $v['ana'] = array();
+  if ( isset($v['svc']) && !isset($v['ech']) ) {
+    $v['ana']['text'] = "service sans prélèvement";
+  }
+  if ( !isset($v['svc']) && isset($v['ech']) ) {
+    $v['ana']['text'] = "prélèvement sans service";
+  }
+
+  $v['ana']['svcbyyear'] = 0;
+  $v['ana']['svcbymonth'] = array(0,0,0,0,0,0,0,0,0,0,0,0);
+  if ( isset($v['svc']) ) {
+    foreach ( array_keys($v['svc']) as $sk ) {
+      $s = $v['svc'][$sk];
+      switch ( $s['periodicite']) {
+      case 'Annuel':
+        $v['ana']['svcbyyear'] += $s['prix_ht'];
+        break;
+      case 'Mensuel':
+        $debut = new DateTime($s['date_debut']);
+        $fin = strlen($s['date_fin'])?new DateTime($s['date_fin']):new DateTime("$YEAR-12-31");
+        for ($m=0;$m<12;$m++) {
+          $d1 = new DateTime("$YEAR-$m-01");
+          $d2 = new DateTime("$YEAR-$m-28"); //XXX c'est pas parfait
+          if ( $d1 < $fin && $d2 > $debut ) {
+            $v['ana']['svcbymonth'][$m] +=$s['prix_ht'];
+          }
+        }
+        break;
+      default:
+        echo "Péridicité inconnue : $k / SER-" .  $s['svc_id'] . "\n";
+      }
+    }
+  }
+
+  $v['ana']['echbyyear'] = 0;
+  $v['ana']['echbymonth'] = array(0,0,0,0,0,0,0,0,0,0,0,0);
+  if ( isset($v['ech']) ) {
+    foreach ( array_keys($v['ech']) as $sk ) {
+      $e = $v['ech'][$sk];
+      switch ( $e['periodicite']) {
+      case 'ANNUEL':
+        $v['ana']['echbyyear'] += $e['montant'];
+        break;
+      case 'MENSUEL':
+      case 'PONCTUEL':
+        $m = explode('/', $e['date_ech'])[1]-1; //XXX Fragile
+        $v['ana']['echbymonth'][$m] +=$e['montant'];
+        break;
+      default:
+        echo "Péridicité inconnue : $k / echeance du " . $e['date_ech'] . "\n";
+      }
+    }
+  }
+  $v['ana']['report'] = 0;
+  $v['ana']['encours'] = 
+    $v['ana']['report'] + $v['ana']['svcbyyear'] - $v['ana']['echbyyear']
+    + array_sum($v['ana']['svcbymonth']) - array_sum($v['ana']['echbymonth']);
+  if ( !isset($v['ana']['text']) ) {
+    if ( $v['ana']['encours'] < 0 ) {
+      $v['ana']['text'] = "Trop perçu";
+    } else if ( $v['ana']['encours'] > 0 ) {
+      $v['ana']['text'] = "Non soldé";
+    }
+  }
+}
+//print_r($data);
+/*
+
+    [CHD2_adh] => Array
+        (
+            [svc] => Array
+                (
+                    [0] => Array
+                        (
+                            [svc_id] => 3
+                            [svc_annee_premiere_saisie] => import-TTN
+                            [actif2015] => oui
+                            [actif2016] => oui
+                            [actif2017] => oui
+                            [svc_derniere_modif] => jamais
+                            [adt] => CHD2
+                            [adt_type] => Perso
+                            [raison] => 
+                            [nom] => MAUBÉ
+                            [prenom] => Denis
+                            [service_type] => Adhésion
+                            [prix_base_ht] => 20.00
+                            [prix_ht] => 20.00
+                            [periodicite] => Annuel
+                            [date_debut] => 2012-01-01 00:00:00
+                            [date_fin] => 
+                            [statut] => Actif CHD
+                            [proprio] => 
+                            [description] => 
+                            [notes] => 
+                        )
+
+                )
+
+            [ech] => Array
+                (
+                    [0] => Array
+                        (
+                            [motif] => Cotisation CHD
+                            [adt] => CHD2
+                            [montant] => 20
+                            [date_ech] => 10/10/2017
+                            [statut] => exécutée avec succès
+                            [raison] => 
+                            [date_op] => 10/10/2017
+                            [periodicite] => ANNUEL
+                            [categorie] => 
+                        )
+
+                )
+
+        )
+*/
+
+echo "adt;type;source;report;annuel;jan.;fév.;mar.;avr.;mai.;juin;juil;août;sept;oct.;nov.;déc.;encours;message\n";
+
+foreach ( $data as $k => $v ) {
+  list($adt,$type) = explode('_',$k);
+  if ( $adt !== $lastadt ) {
+    $pair = ! $pair;
+    $lastadt = $adt;
+  }
+
+  echo "$adt;$type;gestion;" . $v['ana']['report'] . ";" . $v['ana']['svcbyyear'] . ";";
+  for ($m=0;$m<12;$m++) echo $v['ana']['svcbymonth'][$m]. ";";
+  echo $v['ana']['encours'] . ";" . $v['ana']['text'];
+  echo "\n";
+
+  echo "$adt;$type;prélv.;" . $v['ana']['report'] . ";" . $v['ana']['echbyyear'] . ";";
+  for ($m=0;$m<12;$m++) echo $v['ana']['echbymonth'][$m]. ";";
+  echo $v['ana']['encours'] . ";" . $v['ana']['text'];
+  echo "\n";
+}
author	Ludovic Pouzenc <ludovic@pouzenc.fr>	2017-12-24 23:15:06 +0100
committer	Ludovic Pouzenc <ludovic@pouzenc.fr>	2017-12-24 23:15:06 +0100
commit	19a576a64ffb5834240dbb83b1d6d88db85654c4 (patch)
tree	44a26eee7290c0f0466d1c6fe77f1682eeeb74c1
parent	f4e2e322c979d8131736b52c507efee8d35e4bda (diff)
download	chd_gestion-19a576a64ffb5834240dbb83b1d6d88db85654c4.zip chd_gestion-19a576a64ffb5834240dbb83b1d6d88db85654c4.tar.gz chd_gestion-19a576a64ffb5834240dbb83b1d6d88db85654c4.tar.bz2