diff options
| author | Ludovic Pouzenc <ludovic@pouzenc.fr> | 2017-12-17 18:54:22 +0100 |
|---|---|---|
| committer | Ludovic Pouzenc <ludovic@pouzenc.fr> | 2017-12-17 18:54:22 +0100 |
| commit | f4e2e322c979d8131736b52c507efee8d35e4bda (patch) | |
| tree | 30b4b01a157f4e364e11aaa52cef06f8b90a1bcb | |
| parent | f2567b22b5d81e1d8bc14164ddd7d6098afd0e9c (diff) | |
| download | chd_gestion-f4e2e322c979d8131736b52c507efee8d35e4bda.zip chd_gestion-f4e2e322c979d8131736b52c507efee8d35e4bda.tar.gz chd_gestion-f4e2e322c979d8131736b52c507efee8d35e4bda.tar.bz2 | |
Ajout début de script pour rapporcher les prélèvements effectués et les
services de l'appli de gestion.
| -rw-r--r-- | api/rapprochement_gestion_compta.php | 113 | ||||
| -rw-r--r-- | schema/v_rapprochement_compta.sql | 24 |
2 files changed, 137 insertions, 0 deletions
diff --git a/api/rapprochement_gestion_compta.php b/api/rapprochement_gestion_compta.php new file mode 100644 index 0000000..9a99844 --- /dev/null +++ b/api/rapprochement_gestion_compta.php @@ -0,0 +1,113 @@ +<?php +/** + * Copyright 2017 Ludovic Pouzenc <ludovic@pouzenc.fr> + * + * 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 <http://www.gnu.org/licenses/>. + **/ + +// 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']) ) { +?> +<!DOCTYPE html> +<html> +<body> +<form method="post" enctype="multipart/form-data"> + Envoi de l'export CSV de l'échéancier Crédit Coop<br> + <input type="file" name="upfile" id="upfile"><br> + <input type="submit" name="submit"> +</form> +</body> +</html> +<?php + exit(); +} + +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']); +if (mysqli_connect_errno()) { + die(mysqli_connect_error()); +} +unset($db_config); +$mysqli->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 filesize limit.'); + default: + die('Unknown errors.'); +} + +// You should also check filesize here. +if ($_FILES['upfile']['size'] > 1000000) { + die('Exceeded 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"; + + +$res = $mysqli->query("SELECT * FROM v_rapprochement_compta WHERE actif2016='oui'"); +if ( $res === FALSE ) { + die("Wrong query"); +} +$sqldata = array(); +$sqlidx_adt_svctype = array(); +$row = 0; +while ( ($data = $res->fetch_assoc()) !== NULL ) { + $row++; + $sqldata[$row] = $data; + $k = $data['adt'] . '_' . ($data['service_type']==='Adhésion'?'adh':'abo'); + if ( !isset($sqlidx_adt_svctype[$k])) { + $sqlidx_adt_svctype[$k] = array(); + } + $sqlidx_adt_svctype[$k][] = $row; +} +//print_r($sqlidx_adt_svctype); +echo "SQL " . ($row) . " lignes\n"; + +$row = 1; +while (($data = fgetcsv($fhcsv, 1200, ";")) !== FALSE ) { + $row++; + if ( count($data) !== $numcol ) { + echo "CSV skipping line $row : bad col count\n"; + continue; + } + // +} +fclose($fhcsv); +echo "CSV " . ($row-1) . " lignes\n"; + diff --git a/schema/v_rapprochement_compta.sql b/schema/v_rapprochement_compta.sql new file mode 100644 index 0000000..ab9358d --- /dev/null +++ b/schema/v_rapprochement_compta.sql @@ -0,0 +1,24 @@ +SELECT s.id as "svc_id", +CASE YEAR(s.created) WHEN 0 THEN 'import-TTN' ELSE YEAR(s.created) END as "svc_annee_premiere_saisie", +IF( ( '2015-01-01' < s.date_fin OR ISNULL(s.date_fin) ) AND '2015-12-31' > s.date_debut, 'oui', 'non' ) as "actif2015", +IF( ( '2016-01-01' < s.date_fin OR ISNULL(s.date_fin) ) AND '2016-12-31' > s.date_debut, 'oui', 'non' ) as "actif2016", +IF( ( '2017-01-01' < s.date_fin OR ISNULL(s.date_fin) ) AND '2017-12-31' > s.date_debut, 'oui', 'non' ) as "actif2017", + +IF( (UNIX_TIMESTAMP(s.modified) < UNIX_TIMESTAMP('2015-01-01 00:00:00')), 'jamais', s.modified ) as "svc_derniere_modif", +CONCAT('CHD',a.id) as "adt", at.title as "adt_type", +a.raison, a.nom, a.prenom, +st.title as "service_type", st.prix_base_ht, s.prix_ht, +p.title as "periodicite", s.date_debut, s.date_fin, sst.title as "statut", a.proprio, s.description, a.notes + +FROM services s +LEFT JOIN adherents a ON ( a.id = s.adherent_id ) +LEFT JOIN adherent_types at ON ( at.id = a.adherent_type_id ) +LEFT JOIN service_types st ON (st.id = s.service_type_id) +LEFT JOIN service_statuts sst ON (sst.id = s.service_statut_id) +LEFT JOIN periodicites p ON (p.id = st.periodicite_id) + +WHERE s.service_statut_id <> 1 + +ORDER BY a.id, s.id + + |