From c27cdf5554ce65c9685c3aba43421e2488ad4c1a Mon Sep 17 00:00:00 2001 From: Ludovic Pouzenc Date: Tue, 2 Jan 2018 21:39:22 +0100 Subject: approchement_gestion_compta: second "working" version. outputs ODS. Date math are wrong. --- api/rapprochement_gestion_compta.php | 319 ++++++++++++++++++++++++++++------- 1 file changed, 258 insertions(+), 61 deletions(-) diff --git a/api/rapprochement_gestion_compta.php b/api/rapprochement_gestion_compta.php index 5e1b44d..706275e 100644 --- a/api/rapprochement_gestion_compta.php +++ b/api/rapprochement_gestion_compta.php @@ -17,8 +17,11 @@ * 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'); -$YEAR=2017; +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'); @@ -52,7 +55,7 @@ function fix_csv_adt($csv_value) { '2698161' => 'CHD458', '80895435' => 'CHD377', '24015374' => 'CHD447', - // '60768027' => '???', // GOUDENNE ?? + '60768027' => 'CHD448', // GOUDENNE (était GOUDENE dans appli de gestion) '60549585' => 'CHD454', '6330693' => 'CHD439', '41697640' => 'CHD413', @@ -72,13 +75,14 @@ function fix_csv_adt($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']) ) { +if ( !isset($_FILES['upfile']['error']) || is_array($_FILES['upfile']['error']) || is_integer($_POST['year']) ) { ?> @@ -88,8 +92,9 @@ if ( !isset($_FILES['upfile']['error']) || is_array($_FILES['upfile']['error']) exit(); } -header('Content-Type: text/plain; charset=utf-8'); -header('Content-Disposition: attachment; filename=rapprochement' . $YEAR . '.csv'); +$year = $_POST['year']; +$errors = array(); +//header('Content-Type: text/plain; charset=utf-8'); include_once('inc/config.php'); $mysqli = new mysqli($db_config['host'], $db_config['username'], $db_config['password'], $db_config['database']); @@ -138,6 +143,7 @@ $headers = array_map('csv_encoding_to_utf8', $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', @@ -156,7 +162,7 @@ foreach ( $labels as $k => $l ) { } //print_r($cols); -$res = $mysqli->query("SELECT * FROM v_rapprochement_compta WHERE actif$YEAR='oui'"); +$res = $mysqli->query("SELECT * FROM v_rapprochement_compta WHERE actif$year='oui'"); if ( $res === FALSE ) { die("Wrong query"); } @@ -183,7 +189,7 @@ $row = 1; while (($line = fgetcsv($fhcsv, 1200, ";")) !== FALSE ) { $row++; if ( count($line) !== $numcol ) { - echo "CSV skipping line $row : bad col count\n"; + $errors[] = "CSV skipping line $row : bad col count\n"; continue; } $data = array(); @@ -263,98 +269,114 @@ foreach ( $data as $k => &$v ) { } //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']) ) { - $v['ana']['text'] = "service sans prélèvement"; + $errors[] = "service sans prélèvement : $k"; } if ( !isset($v['svc']) && isset($v['ech']) ) { - $v['ana']['text'] = "prélèvement sans service"; + $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']['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"); + $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 + $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']; + $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: - echo "Péridicité inconnue : $k / SER-" . $s['svc_id'] . "\n"; + $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': - $v['ana']['echbyyear'] += $e['montant']; + 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 - $v['ana']['echbymonth'][$m] +=$e['montant']; + 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: - echo "Péridicité inconnue : $k / echeance du " . $e['date_ech'] . "\n"; + $errors[] = "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 + [CHD2_abo] => Array ( [svc] => Array ( [0] => Array ( - [svc_id] => 3 - [svc_annee_premiere_saisie] => import-TTN - [actif2015] => oui + [svc_id] => 917 + [svc_annee_premiere_saisie] => 2015 + [actif2015] => non [actif2016] => oui [actif2017] => oui - [svc_derniere_modif] => jamais + [svc_derniere_modif] => 2015-12-31 12:15:58 [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 + [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] => @@ -368,39 +390,214 @@ foreach ( $data as $k => &$v ) { ( [0] => Array ( - [motif] => Cotisation CHD + [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] => 20 - [date_ech] => 10/10/2017 + [montant] => 28 + [date_ech] => 15/12/2016 [statut] => exécutée avec succès [raison] => - [date_op] => 10/10/2017 - [periodicite] => ANNUEL - [categorie] => + [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 + ) + ) */ -echo "adt;type;source;report;annuel;jan.;fév.;mar.;avr.;mai.;juin;juil;août;sept;oct.;nov.;déc.;encours;message\n"; + +$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 ) { - list($adt,$type) = explode('_',$k); + $adt = $v['ana']['adt']; if ( $adt !== $lastadt ) { - $pair = ! $pair; - $lastadt = $adt; + $pair=! $pair; + $l+=1; + $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"; + $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(); -- cgit v1.1