summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorLudovic Pouzenc <ludovic@pouzenc.fr>2018-01-02 21:39:22 +0100
committerLudovic Pouzenc <ludovic@pouzenc.fr>2018-01-02 21:39:22 +0100
commitc27cdf5554ce65c9685c3aba43421e2488ad4c1a (patch)
tree0d18a0bc966c8206f48d20c724676d92d5a9ea27
parent19a576a64ffb5834240dbb83b1d6d88db85654c4 (diff)
downloadchd_gestion-c27cdf5554ce65c9685c3aba43421e2488ad4c1a.zip
chd_gestion-c27cdf5554ce65c9685c3aba43421e2488ad4c1a.tar.gz
chd_gestion-c27cdf5554ce65c9685c3aba43421e2488ad4c1a.tar.bz2
approchement_gestion_compta: second "working" version. outputs ODS. Date math are wrong.
Diffstat
-rw-r--r--api/rapprochement_gestion_compta.php319
1 files 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 <http://www.gnu.org/licenses/>.
**/
+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']) ) {
?>
<!DOCTYPE html>
<html>
<body>
<form method="post" enctype="multipart/form-data">
Envoi de l'export CSV des échéances Crédit Coop<br>
+ <select name="year"><option>2016</option><option>2017</option><option>2017</option><option>2018</option></select>
<input type="file" name="upfile" id="upfile"><br>
<input type="submit" name="submit">
</form>
@@ -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();
generated by cgit v1.1 at 2024-04-24 11:08:29 +0000