From 19a576a64ffb5834240dbb83b1d6d88db85654c4 Mon Sep 17 00:00:00 2001 From: Ludovic Pouzenc Date: Sun, 24 Dec 2017 23:15:06 +0100 Subject: rapprochement_gestion_compta: first working version. outputs CSV for now --- api/rapprochement_gestion_compta.php | 329 +++++++++++++++++++++++++++++++++-- 1 file 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 . **/ +$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']) @@ -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"; +} -- cgit v1.1