*
* This file is part of CHD Gestion.
*
* CHD Gestion is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* CHD Gestion is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* 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']) ) {
?>
set_charset("utf8") or die($mysqli->error);
// Check $_FILES['upfile']['error'] value.
switch ($_FILES['upfile']['error']) {
case UPLOAD_ERR_OK:
break;
case UPLOAD_ERR_NO_FILE:
die('No file sent.');
case UPLOAD_ERR_INI_SIZE:
case UPLOAD_ERR_FORM_SIZE:
die('Exceeded php filesize limit.');
default:
die('Unknown errors.');
}
// You should also check filesize here.
if ($_FILES['upfile']['size'] > 2000000) {
die('Exceeded soft filesize limit.');
}
// DO NOT TRUST $_FILES['upfile']['mime'] VALUE !!
// Check MIME Type by yourself.
$finfo = finfo_open(FILEINFO_MIME_TYPE);
$mtype = finfo_file($finfo, $_FILES['upfile']['tmp_name']);
if ( $mtype !== 'text/plain' ) {
die('Invalid file format.'.$mtype);
}
$fhcsv = fopen($_FILES['upfile']['tmp_name'], "r") or die('Can\'t read CSV');
$headers = fgetcsv($fhcsv, 1200, ";");
if ( $headers === FALSE ) {
die('Can\'t read CSV headers');
}
$numcol = count($headers);
//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 actif$YEAR='oui'");
if ( $res === FALSE ) {
die("Wrong query");
}
$svcdata = array();
$idx_adt_svctype = array();
$row = 0;
while ( ($data = $res->fetch_assoc()) !== NULL ) {
$row++;
$svcdata[$row] = $data;
$k = $data['adt'] . '_' . ($data['service_type']==='Adhésion'?'adh':'abo');
if ( !isset($idx_adt_svctype[$k])) {
$idx_adt_svctype[$k] = array();
}
if ( !isset($idx_adt_svctype[$k]['svc'])) {
$idx_adt_svctype[$k]['svc'] = array();
}
$idx_adt_svctype[$k]['svc'][] = $row;
}
//print_r($svcidx_adt_svctype);
//echo "Services : $row lignes\n";
$echdata = array();
$row = 1;
while (($line = fgetcsv($fhcsv, 1200, ";")) !== FALSE ) {
$row++;
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";
//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";
}