*
* 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 .
**/
require_once('/usr/local/php/vendor/autoload.php');
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
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' => 'CHD448', // GOUDENNE (était GOUDENE dans appli de gestion)
'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']) || is_integer($_POST['year']) ) {
?>
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',
'refech' => 'Référence échéance',
'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 ) {
$errors[] = "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();
list($adt,$type) = explode('_',$k);
$v['ana']['adt'] = $adt;
$v['ana']['type'] = $type;
$adt_id = str_replace('CHD', '', $adt);
$v['ana']['adt_id'] = $adt_id;
if ( isset($v['svc']) && !isset($v['ech']) ) {
$errors[] = "service sans prélèvement : $k";
}
if ( !isset($v['svc']) && isset($v['ech']) ) {
$errors[] = "prélèvement sans service : $k";
}
$v['ana']['svcbyyear'] = 0;
$v['ana']['svcbyyearkeys'] = array();
$v['ana']['svcbymonth'] = array(0,0,0,0,0,0,0,0,0,0,0,0);
$v['ana']['svcbymonthkeys'] = array();
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'];
$v['ana']['svcbyyearkeys'][] = $sk;
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
/*
$errors[] = "DEBUG m==$m if ( " . $d1->format('Y-m-d H:i:s') . " < " . $fin->format('Y-m-d H:i:s') . " && " . $d2->format('Y-m-d H:i:s') . " > " . $debut->format('Y-m-d H:i:s') . " ) : "
. ( ($d1 < $fin && $d2 > $debut)?"vrai":"faux");
*/
if ( $d1 < $fin && $d2 > $debut ) {
$v['ana']['svcbymonth'][$m] -=$s['prix_ht'];
if ( !isset($v['ana']['svcbymonthkeys'][$m]) ) {
$v['ana']['svcbymonthkeys'][$m] = array();
}
$v['ana']['svcbymonthkeys'][$m][] = $sk;
}
}
break;
default:
$errors[] = "Péridicité inconnue : $k / SER-" . $s['svc_id'] . "\n";
}
}
}
$v['ana']['echbyyear'] = 0;
$v['ana']['echbyyearkeys'] = array();
$v['ana']['echbymonth'] = array(0,0,0,0,0,0,0,0,0,0,0,0);
$v['ana']['echbymonthkeys'] = array();
if ( isset($v['ech']) ) {
foreach ( array_keys($v['ech']) as $sk ) {
$e = $v['ech'][$sk];
switch ( $e['periodicite']) {
case 'ANNUEL':
if ( strpos($e['statut'], 'succès') !== false ) {
$v['ana']['echbyyear'] += $e['montant'];
}
$v['ana']['echbyyearkeys'][] = $sk;
break;
case 'MENSUEL':
case 'PONCTUEL':
$m = explode('/', $e['date_ech'])[1]-1; //XXX Fragile
if ( strpos($e['statut'], 'succès') !== false ) {
$v['ana']['echbymonth'][$m] +=$e['montant'];
}
if ( !isset($v['ana']['echbymonthkeys'][$m]) ) {
$v['ana']['echbymonthkeys'][$m] = array();
}
$v['ana']['echbymonthkeys'][$m][] = $sk;
break;
default:
$errors[] = "Péridicité inconnue : $k / echeance du " . $e['date_ech'] . "\n";
}
}
}
}
//print_r($data);
/*
[CHD2_abo] => Array
(
[svc] => Array
(
[0] => Array
(
[svc_id] => 917
[svc_annee_premiere_saisie] => 2015
[actif2015] => non
[actif2016] => oui
[actif2017] => oui
[svc_derniere_modif] => 2015-12-31 12:15:58
[adt] => CHD2
[adt_type] => Perso
[raison] =>
[nom] => MAUBÉ
[prenom] => Denis
[service_type] => Internet
[prix_base_ht] => 25.00
[prix_ht] => 25.00
[periodicite] => Mensuel
[date_debut] => 2015-12-31 11:15:58
[date_fin] =>
[statut] => Actif CHD
[proprio] =>
[description] =>
[notes] =>
)
)
[ech] => Array
(
[0] => Array
(
[motif] => ABONNEMENT CHD
[adt] => CHD2
[montant] => 15
[date_ech] => 06/01/2016
[statut] => exécutée avec succès
[raison] =>
[date_op] => 06/01/2016
[typeech] => REPETITIF
[periodicite] => MENSUEL
[categorie] => abo
)
[11] => Array
(
[motif] => ABONNEMENT CHD
[adt] => CHD2
[montant] => 28
[date_ech] => 15/12/2016
[statut] => exécutée avec succès
[raison] =>
[date_op] => 15/12/2016
[typeech] => REPETITIF
[periodicite] => MENSUEL
[categorie] => abo
)
)
[ana] => Array
(
[adt_id] =>
[adt] => CHD2
[type] => abo
[svcbyyear] => 0
[svcbymonth] => Array
(
[0] => 0
[1] => 25
[2] => 25
[3] => 25
[4] => 25
[5] => 25
[6] => 25
[7] => 25
[8] => 25
[9] => 25
[10] => 25
[11] => 25
)
[echbyyear] => 0
[echbymonth] => Array
(
[0] => 15
[1] => 28
[2] => 28
[3] => 28
[4] => 28
[5] => 28
[6] => 28
[7] => 28
[8] => 28
[9] => 28
[10] => 28
[11] => 28
)
[report] => 0
[encours] => -48
[text] => Trop perçu
)
)
*/
$spreadsheet = new Spreadsheet();
$spreadsheet->getProperties()->setCreator('(autogenerated)')
->setLastModifiedBy('(autogenerated)')
->setTitle('CHD - Rapprochement compta / gestion');
// Add some data
$spreadsheet->setActiveSheetIndex(0)->setTitle('Erreurs')
->setCellValue('A1', 'Erreurs d\'execution')
->fromArray(
array_chunk($errors, 1),
NULL,
'A1'
);
$s = $spreadsheet->createSheet()->setTitle('Rapprochement');
$s->fromArray(
array('adt','type','source','report','annuel',
'jan.','fév.','mar.','avr.','mai.','juin','juil','août','sept','oct.','nov.','déc.',
'somme','encours'),
NULL,
'A1'
);
$lastadt="";
$pair=true;
$l=2;
foreach ( $data as $k => $v ) {
$adt = $v['ana']['adt'];
if ( $adt !== $lastadt ) {
$pair=! $pair;
$l+=1;
$lastadt=$adt;
}
$s->setCellValue("A$l", $v['ana']['adt'])
->setCellValue("B$l", $v['ana']['type'])
->setCellValue("C$l", 'gestion')
//->setCellValue("D$l", $v['ana']['report'])
->setCellValue("E$l", $v['ana']['svcbyyear'])
->fromArray($v['ana']['svcbymonth'], NULL, "F$l")
->setCellValue("R$l", "=SUM(D$l:Q$l)");
foreach ( $v['ana']['svcbyyearkeys'] as $sk ) {
$s->getComment("E$l")->getText()->createTextRun(
' SER-'. $v['svc'][$sk]['svc_id']
. ' ' . $v['svc'][$sk]['date_debut']
. ' ' . $v['svc'][$sk]['date_fin']
);
}
for ($m=0;$m<12;$m++) {
if ( isset($v['ana']['svcbymonthkeys'][$m]) ) {
foreach ( $v['ana']['svcbymonthkeys'][$m] as $sk ) {
$c = chr(ord('F')+$m);
$s->getComment("$c$l")->getText()->createTextRun(
' SER-'. $v['svc'][$sk]['svc_id']
. ' ' . $v['svc'][$sk]['date_debut']
. ' ' . $v['svc'][$sk]['date_fin']
);
}
}
}
$l+=1;
$s->setCellValue("A$l", $v['ana']['adt'])
->setCellValue("B$l", $v['ana']['type'])
->setCellValue("C$l", 'prélév.')
//->setCellValue("D$l", $v['ana']['report'])
->setCellValue("E$l", $v['ana']['echbyyear'])
->fromArray($v['ana']['echbymonth'], NULL, "F$l")
->setCellValue("R$l", "=SUM(D$l:Q$l)");
foreach ( $v['ana']['echbyyearkeys'] as $sk ) {
$s->getComment("E$l")->getText()->createTextRun(
' ' . $v['ech'][$sk]['refech']
. ' ' . $v['ech'][$sk]['date_op']
. ' ' . $v['ech'][$sk]['statut']
);
}
for ($m=0;$m<12;$m++) {
if ( isset($v['ana']['echbymonthkeys'][$m]) ) {
foreach ( $v['ana']['echbymonthkeys'][$m] as $sk ) {
$c = chr(ord('F')+$m);
$s->getComment("$c$l")->getText()->createTextRun(
' ' . $v['ech'][$sk]['refech']
. ' ' . $v['ech'][$sk]['date_op']
. ' ' . $v['ech'][$sk]['statut']
);
}
}
}
$l+=1;
$s->setCellValue("A$l", $v['ana']['adt'])
->setCellValue("B$l", $v['ana']['type'])
->setCellValue("C$l", 'manuel');
$l+=1;
$s->setCellValue("A$l", $v['ana']['adt'])
->setCellValue("B$l", $v['ana']['type'])
->setCellValue("C$l", 'diff.')
//->setCellValue("D$l", $v['ana']['report'])
->setCellValue("E$l", "=SUM(E".($l-3).":E".($l-1).')')
->setCellValue("F$l", "=SUM(F".($l-3).":F".($l-1).')')
->setCellValue("G$l", "=SUM(G".($l-3).":G".($l-1).')')
->setCellValue("H$l", "=SUM(H".($l-3).":H".($l-1).')')
->setCellValue("I$l", "=SUM(I".($l-3).":I".($l-1).')')
->setCellValue("J$l", "=SUM(J".($l-3).":J".($l-1).')')
->setCellValue("K$l", "=SUM(K".($l-3).":K".($l-1).')')
->setCellValue("L$l", "=SUM(L".($l-3).":L".($l-1).')')
->setCellValue("M$l", "=SUM(M".($l-3).":M".($l-1).')')
->setCellValue("N$l", "=SUM(N".($l-3).":N".($l-1).')')
->setCellValue("O$l", "=SUM(O".($l-3).":O".($l-1).')')
->setCellValue("P$l", "=SUM(P".($l-3).":P".($l-1).')')
->setCellValue("Q$l", "=SUM(Q".($l-3).":Q".($l-1).')')
->setCellValue("S$l", "=SUM(D$l:Q$l)");
$l+=1;
}
// Redirect output to a client’s web browser (Ods)
header('Content-Type: application/vnd.oasis.opendocument.spreadsheet');
header('Content-Disposition: attachment;filename="rapprochement-compta-gestion.ods"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$writer = IOFactory::createWriter($spreadsheet, 'Ods');
$writer->save('php://output');
exit();