396 lines
29 KiB
JavaScript
396 lines
29 KiB
JavaScript
/////////////////////////////////////////////////
|
||
// LOGGING
|
||
/////////////////////////////////////////////////
|
||
const logger = require("../public/js/app/logger_adhesion");
|
||
|
||
/////////////////////////////////////////////////
|
||
// APPELS DES MODULES
|
||
/////////////////////////////////////////////////
|
||
const moment = require('moment');
|
||
const ejs = require('ejs');
|
||
const nodemailer = require('nodemailer');
|
||
const path = require("path");
|
||
const stripe = require('stripe')(process.env.STRIPE_PRIVATE_KEY);
|
||
const pdf = require("pdf-creator-node");
|
||
const fs = require("fs");
|
||
const mysql = require('mysql2');
|
||
const mysql_promise = require('mysql2/promise');
|
||
// ADOBE PDF
|
||
const PDFServicesSdk = require('@adobe/pdfservices-node-sdk');
|
||
|
||
|
||
/////////////////////////////////////////////////
|
||
// APPELS DES FONCTIONS
|
||
/////////////////////////////////////////////////
|
||
// Déterminer les profils de l'adhésion
|
||
const carte_adherent = require("./../modules/adhesion/carte_adherent");
|
||
// FAIRE UNE PAUSE
|
||
const sleep = require("./../modules/sleep");
|
||
|
||
////////////////////////////////////////////////////
|
||
// FILE NAME
|
||
////////////////////////////////////////////////////
|
||
const filename = path.parse(__filename).base;
|
||
|
||
/////////////////////////////////////////////////
|
||
// GOOGLE SHEET PARAMETERS
|
||
/////////////////////////////////////////////////
|
||
const { GoogleSpreadsheet } = require('google-spreadsheet');
|
||
const GS_creds = require('../inscriptionsafps-53b60452f63d.json');
|
||
const { error } = require("console");
|
||
// Initialize the sheet - doc ID is the long id in the sheets URL
|
||
const GS_adhesion = new GoogleSpreadsheet(process.env.gsheet_adhesion);
|
||
|
||
|
||
/////////////////////////////////////////////////
|
||
// VARIABLES GLOBALES
|
||
/////////////////////////////////////////////////
|
||
let adherent_type, profil1, profil2, profil3;
|
||
|
||
|
||
|
||
/////////////////////////////////////////////////
|
||
// ASSOCIATIONS LOCACLES
|
||
// IMPORTS ADHÉRENTS DANS DB
|
||
// CLIC SUR LE BOUTON "Valider" de la page /maj_googlesheet
|
||
/////////////////////////////////////////////////
|
||
const import_ass_locales_v2 = async function(req, res) {
|
||
|
||
logger.info("======================================", { label: filename });
|
||
logger.info("======== FUNCTION import_ass_locales_v2 (adhesion maj_googlesheet) ==========", { label: filename });
|
||
logger.info("======================================", { label: filename });
|
||
|
||
|
||
/////////////////////////////////////////////////
|
||
// DATE
|
||
let now = moment().format("YYYY-MM-DD HH:mm:ss");
|
||
let now_pdf = moment().format("YYYY-MM-DDTHH-mm-ss");
|
||
|
||
/////////////////////////////////////////////////
|
||
// VARIABLE
|
||
let nb_import = 0;
|
||
|
||
/////////////////////////////////////////////////
|
||
// CONNEXION GSHEET
|
||
try {
|
||
await GS_adhesion.useServiceAccountAuth(GS_creds);
|
||
} catch (error) {
|
||
// Echec de connection à GS
|
||
logger.error("Il y a un problème avec la connection à Google Sheet. : " + error, { numsession: req.session.id });
|
||
req.session.message4 = "Il y a un problème système, veuillez recommencer.";
|
||
res.redirect('/adhesion/maj_googlesheet');
|
||
};
|
||
|
||
await GS_adhesion.loadInfo();
|
||
const GS_asso_locales = GS_adhesion.sheetsByTitle['adhesion_ass_locales'];
|
||
const GS_asso_locales_rows = await GS_asso_locales.getRows();
|
||
console.log("GS_asso_locales.rowCount = ", GS_asso_locales.rowCount);
|
||
//console.log("GS_asso_locales_rows[0].identifiant = ", GS_asso_locales_rows[0].identifiant);
|
||
//console.log("GS_asso_locales_rows[1].identifiant = ", GS_asso_locales_rows[1].identifiant);
|
||
|
||
// NOMBRE DE LIGNE DANS L'ONGLET adhesion_ass_locales
|
||
let compteur = GS_asso_locales.rowCount - 1; // POUR DÉCOMPTER LA LIGNE D'EN-TÊTE
|
||
console.log("compteur = ", compteur);
|
||
|
||
////////////////////////////////////////////////////
|
||
// CONNECTION ADOBE API
|
||
////////////////////////////////////////////////////
|
||
const credentials = PDFServicesSdk.Credentials
|
||
.servicePrincipalCredentialsBuilder()
|
||
.withClientId(process.env.PDF_SERVICES_CLIENT_ID)
|
||
.withClientSecret(process.env.PDF_SERVICES_CLIENT_SECRET)
|
||
.build();
|
||
//timeout Default: 10000
|
||
const clientConfig = PDFServicesSdk.ClientConfig
|
||
.clientConfigBuilder()
|
||
.withConnectTimeout(40000)
|
||
.withReadTimeout(80000)
|
||
.build();
|
||
// Create an ExecutionContext using credentials
|
||
const executionContext = PDFServicesSdk.ExecutionContext.create(credentials);
|
||
|
||
/////////////////////////////////////////////////
|
||
// CONNECTION DB
|
||
try {
|
||
const pool = mysql.createPool({
|
||
host: process.env.db_connection_host,
|
||
port: process.env.db_connection_port,
|
||
user: process.env.db_connection_user,
|
||
password: process.env.db_connection_password,
|
||
database: process.env.db_connection_database,
|
||
rowsAsArray: true
|
||
});
|
||
const promisePool = pool.promise();
|
||
|
||
/////////////////////////////////////////////////
|
||
// ON LIT TOUTES LES LIGNES DU GOOGLE SHEET LES UNES APRÈS LES AUTRES
|
||
for (i=0; i<compteur; i++) {
|
||
console.log("i = ", i);
|
||
console.log("GS_asso_locales_rows[i].date_export = ", GS_asso_locales_rows[i].date_export);
|
||
|
||
/////////////////////////////////////////////////
|
||
// SI LA LIGNE N'A PAS DÉJÀ ÉTÉ IMPORTÉE ON L'AJOUTE DANS LA BD
|
||
// SI DATE EXPORT EST VIDE
|
||
if (!GS_asso_locales_rows[i].date_export) {
|
||
console.log("GS_asso_locales_rows[i].identifiant NON IMPORTÉ = ", GS_asso_locales_rows[i].identifiant);
|
||
|
||
// AJOUT DANS LA TABLE adherent
|
||
let sql = 'INSERT INTO adherent (association_suzuki, saison, identifiant, date_maj, adherent_type, adherent_nom, adherent_prenom, adherent_adresse_rue, adherent_adresse_complement, adherent_adresse_codepostal, adherent_adresse_ville, adherent_adresse_pays, adherent_telephone_fixe, adherent_telephone_mobile, adherent_mail) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
|
||
let sql_values = [GS_asso_locales_rows[i].association, GS_asso_locales_rows[i].saison, GS_asso_locales_rows[i].identifiant, now, GS_asso_locales_rows[i].adherent_type, GS_asso_locales_rows[i].adherent_nom, GS_asso_locales_rows[i].adherent_prenom, GS_asso_locales_rows[i].adherent_adresse_rue, GS_asso_locales_rows[i].adherent_adresse_complement, GS_asso_locales_rows[i].adherent_adresse_codepostal, GS_asso_locales_rows[i].adherent_adresse_ville, GS_asso_locales_rows[i].adherent_adresse_pays, GS_asso_locales_rows[i].adherent_telephone_fixe, GS_asso_locales_rows[i].adherent_telephone_mobile, GS_asso_locales_rows[i].adherent_mail];
|
||
|
||
const [results, fields] = await promisePool.query(sql, sql_values); // Execution SQL
|
||
logger.info("INSERT - adherent - Row ID added : " + results.insertId, { numsession: req.session.id, label: filename});
|
||
|
||
// AJOUT DANS LA TABLE adherent_finance
|
||
console.log("GS_asso_locales_rows[i].adherent_paiement_montant_don = ", GS_asso_locales_rows[i].adherent_paiement_montant_don);
|
||
if (!GS_asso_locales_rows[i].adherent_paiement_montant_don){GS_asso_locales_rows[i].adherent_paiement_montant_don = 0;}
|
||
|
||
let sql2 = 'INSERT INTO adherent_finance (date_maj, saison, identifiant, association_suzuki, adherent_type, adherent_nom, adherent_prenom, adherent_telephone_mobile, adherent_mail, statut_paiement, paiement_mode, date_paiement, montant_adhesion, montant_don, montant_total) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
|
||
let sql_values2 = [now, GS_asso_locales_rows[i].saison, GS_asso_locales_rows[i].identifiant, GS_asso_locales_rows[i].association, GS_asso_locales_rows[i].adherent_type, GS_asso_locales_rows[i].adherent_nom, GS_asso_locales_rows[i].adherent_prenom, GS_asso_locales_rows[i].adherent_telephone_mobile, GS_asso_locales_rows[i].adherent_mail, GS_asso_locales_rows[i].adhesion_paye, GS_asso_locales_rows[i].adherent_paiement_mode, now, GS_asso_locales_rows[i].adherent_paiement_montant_adhesion, GS_asso_locales_rows[i].adherent_paiement_montant_don, GS_asso_locales_rows[i].adherent_paiement_montant_total];
|
||
|
||
const [results2, fields2] = await promisePool.query(sql2, sql_values2); // Execution SQL
|
||
logger.info("INSERT - adherent_finance - Row ID added : " + results2.insertId, { numsession: req.session.id, label: filename});
|
||
|
||
// AJOUT DANS LA TABLE adherent_professeur
|
||
if (GS_asso_locales_rows[i].adherent_prof_instrument1) {
|
||
let sql3 = 'INSERT INTO adherent_professeur (date_maj, saison, identifiant, association_suzuki, adherent_nom, adherent_prenom, adherent_telephone_mobile, adherent_mail, adherent_prof_instrument1, adherent_prof_instrument1_esa, adherent_prof_instrument2, adherent_prof_instrument2_esa, adherent_prof_sece, adherent_prof_ecole1, adherent_prof_ecole1_adresse, adherent_prof_ecole1_cp, adherent_prof_ecole1_ville, adherent_prof_ecole1_mail, adherent_prof_ecole1_web, adherent_prof_ecole2, adherent_prof_ecole2_adresse, adherent_prof_ecole2_cp, adherent_prof_ecole2_ville, adherent_prof_ecole2_mail, adherent_prof_ecole2_web, adherent_prof_ecole3, adherent_prof_ecole3_adresse, adherent_prof_ecole3_cp, adherent_prof_ecole3_ville, adherent_prof_ecole3_mail, adherent_prof_ecole3_web) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
|
||
let sql_values3 = [now, GS_asso_locales_rows[i].saison, GS_asso_locales_rows[i].identifiant, GS_asso_locales_rows[i].association, GS_asso_locales_rows[i].adherent_nom, GS_asso_locales_rows[i].adherent_prenom, GS_asso_locales_rows[i].adherent_telephone_mobile, GS_asso_locales_rows[i].adherent_mail, GS_asso_locales_rows[i].adherent_prof_instrument1, GS_asso_locales_rows[i].adherent_prof_instrument1_esa, GS_asso_locales_rows[i].adherent_prof_instrument2, GS_asso_locales_rows[i].adherent_prof_instrument2_esa, GS_asso_locales_rows[i].adherent_prof_sece, GS_asso_locales_rows[i].adherent_prof_ecole1, GS_asso_locales_rows[i].adherent_prof_ecole1_adresse, GS_asso_locales_rows[i].adherent_prof_ecole1_cp, GS_asso_locales_rows[i].adherent_prof_ecole1_ville, GS_asso_locales_rows[i].adherent_prof_ecole1_mail, GS_asso_locales_rows[i].adherent_prof_ecole1_web, GS_asso_locales_rows[i].adherent_prof_ecole2, GS_asso_locales_rows[i].adherent_prof_ecole2_adresse, GS_asso_locales_rows[i].adherent_prof_ecole2_cp, GS_asso_locales_rows[i].adherent_prof_ecole2_ville, GS_asso_locales_rows[i].adherent_prof_ecole2_mail, GS_asso_locales_rows[i].adherent_prof_ecole2_web, GS_asso_locales_rows[i].adherent_prof_ecole3, GS_asso_locales_rows[i].adherent_prof_ecole3_adresse, GS_asso_locales_rows[i].adherent_prof_ecole3_cp, GS_asso_locales_rows[i].adherent_prof_ecole3_ville, GS_asso_locales_rows[i].adherent_prof_ecole3_mail, GS_asso_locales_rows[i].adherent_prof_ecole3_web];
|
||
|
||
const [results3, fields3] = await promisePool.query(sql3, sql_values3); // Execution SQL
|
||
logger.info("INSERT - adherent_professeur - Row ID added : " + results3.insertId, { numsession: req.session.id, label: filename});
|
||
}
|
||
// AJOUT DANS LA TABLE adherent_formation_prof
|
||
if (GS_asso_locales_rows[i].adherent_prof_stagiaire_instrument1) {
|
||
let sql4 = 'INSERT INTO adherent_formation_prof (date_maj, saison, identifiant, association_suzuki, adherent_nom, adherent_prenom, adherent_telephone_mobile, adherent_mail, adherent_prof_stagiaire_instrument1, adherent_prof_stagiaire_instrument1_prof, adherent_prof_stagiaire_instrument1_esa, adherent_prof_stagiaire_instrument2, adherent_prof_stagiaire_instrument2_prof, adherent_prof_stagiaire_instrument2_esa) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
|
||
let sql_values4 = [now, GS_asso_locales_rows[i].saison, GS_asso_locales_rows[i].identifiant, GS_asso_locales_rows[i].association, GS_asso_locales_rows[i].adherent_nom, GS_asso_locales_rows[i].adherent_prenom, GS_asso_locales_rows[i].adherent_telephone_mobile, GS_asso_locales_rows[i].adherent_mail, GS_asso_locales_rows[i].adherent_prof_stagiaire_instrument1, GS_asso_locales_rows[i].adherent_prof_stagiaire_instrument1_prof, GS_asso_locales_rows[i].adherent_prof_stagiaire_instrument1_esa, GS_asso_locales_rows[i].adherent_prof_stagiaire_instrument2, GS_asso_locales_rows[i].adherent_prof_stagiaire_instrument2_prof, GS_asso_locales_rows[i].adherent_prof_stagiaire_instrument2_esa];
|
||
|
||
const [results4, fields4] = await promisePool.query(sql4, sql_values4); // Execution SQL
|
||
logger.info("INSERT - adherent_formation_prof - Row ID added : " + results4.insertId, { numsession: req.session.id, label: filename});
|
||
}
|
||
|
||
// AJOUT DANS LA TABLE adherent_eleve 1
|
||
if (GS_asso_locales_rows[i].adherent_eleve1_nom) {
|
||
let sql5 = 'INSERT INTO adherent_eleve (date_maj, saison, identifiant, association_suzuki, adherent_telephone_mobile, adherent_mail, eleve_nom, eleve_prenom, eleve_datenaissance, eleve_contactsup_nom, eleve_contactsup_prenom, eleve_contactsup_mail, eleve_instrumentA, eleve_instrumentA_prof, eleve_instrumentA_cahier, eleve_instrumentB, eleve_instrumentB_prof, eleve_instrumentB_cahier, eleve_droitimage) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
|
||
let sql_values5 = [now, GS_asso_locales_rows[i].saison, GS_asso_locales_rows[i].identifiant, GS_asso_locales_rows[i].association, GS_asso_locales_rows[i].adherent_telephone_mobile, GS_asso_locales_rows[i].adherent_mail, GS_asso_locales_rows[i].adherent_eleve1_nom, GS_asso_locales_rows[i].adherent_eleve1_prenom, GS_asso_locales_rows[i].adherent_eleve1_datenaissance, GS_asso_locales_rows[i].adherent_eleve1_contactsup_nom, GS_asso_locales_rows[i].adherent_eleve1_contactsup_prenom, GS_asso_locales_rows[i].adherent_eleve1_contactsup_mail, GS_asso_locales_rows[i].adherent_eleve1_InstrumentA, GS_asso_locales_rows[i].adherent_eleve1_InstrumentA_prof, GS_asso_locales_rows[i].adherent_eleve1_InstrumentA_cahier, GS_asso_locales_rows[i].adherent_eleve1_InstrumentB, GS_asso_locales_rows[i].adherent_eleve1_InstrumentB_prof, GS_asso_locales_rows[i].adherent_eleve1_InstrumentB_cahier, GS_asso_locales_rows[i].adherent_eleve1_droitimage];
|
||
|
||
const [results5, fields5] = await promisePool.query(sql5, sql_values5); // Execution SQL
|
||
logger.info("INSERT - adherent_eleve 1 - Row ID added : " + results5.insertId, { numsession: req.session.id, label: filename});
|
||
}
|
||
|
||
// AJOUT DANS LA TABLE adherent_eleve 2
|
||
if (GS_asso_locales_rows[i].adherent_eleve2_nom) {
|
||
let sql6 = 'INSERT INTO adherent_eleve (date_maj, saison, identifiant, association_suzuki, adherent_telephone_mobile, adherent_mail, eleve_nom, eleve_prenom, eleve_datenaissance, eleve_contactsup_nom, eleve_contactsup_prenom, eleve_contactsup_mail, eleve_instrumentA, eleve_instrumentA_prof, eleve_instrumentA_cahier, eleve_instrumentB, eleve_instrumentB_prof, eleve_instrumentB_cahier, eleve_droitimage) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
|
||
let sql_values6 = [now, GS_asso_locales_rows[i].saison, GS_asso_locales_rows[i].identifiant, GS_asso_locales_rows[i].association, GS_asso_locales_rows[i].adherent_telephone_mobile, GS_asso_locales_rows[i].adherent_mail, GS_asso_locales_rows[i].adherent_eleve2_nom, GS_asso_locales_rows[i].adherent_eleve2_prenom, GS_asso_locales_rows[i].adherent_eleve2_datenaissance, GS_asso_locales_rows[i].adherent_eleve2_contactsup_nom, GS_asso_locales_rows[i].adherent_eleve2_contactsup_prenom, GS_asso_locales_rows[i].adherent_eleve2_contactsup_mail, GS_asso_locales_rows[i].adherent_eleve2_InstrumentA, GS_asso_locales_rows[i].adherent_eleve2_InstrumentA_prof, GS_asso_locales_rows[i].adherent_eleve2_InstrumentA_cahier, GS_asso_locales_rows[i].adherent_eleve2_InstrumentB, GS_asso_locales_rows[i].adherent_eleve2_InstrumentB_prof, GS_asso_locales_rows[i].adherent_eleve2_InstrumentB_cahier, GS_asso_locales_rows[i].adherent_eleve2_droitimage];
|
||
|
||
const [results6, fields6] = await promisePool.query(sql6, sql_values6); // Execution SQL
|
||
logger.info("INSERT - adherent_eleve 2 - Row ID added : " + results6.insertId, { numsession: req.session.id, label: filename});
|
||
}
|
||
|
||
// AJOUT DANS LA TABLE adherent_eleve 3
|
||
if (GS_asso_locales_rows[i].adherent_eleve3_nom) {
|
||
let sql7 = 'INSERT INTO adherent_eleve (date_maj, saison, identifiant, association_suzuki, adherent_telephone_mobile, adherent_mail, eleve_nom, eleve_prenom, eleve_datenaissance, eleve_contactsup_nom, eleve_contactsup_prenom, eleve_contactsup_mail, eleve_instrumentA, eleve_instrumentA_prof, eleve_instrumentA_cahier, eleve_instrumentB, eleve_instrumentB_prof, eleve_instrumentB_cahier, eleve_droitimage) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
|
||
let sql_values7 = [now, GS_asso_locales_rows[i].saison, GS_asso_locales_rows[i].identifiant, GS_asso_locales_rows[i].association, GS_asso_locales_rows[i].adherent_telephone_mobile, GS_asso_locales_rows[i].adherent_mail, GS_asso_locales_rows[i].adherent_eleve3_nom, GS_asso_locales_rows[i].adherent_eleve3_prenom, GS_asso_locales_rows[i].adherent_eleve3_datenaissance, GS_asso_locales_rows[i].adherent_eleve3_contactsup_nom, GS_asso_locales_rows[i].adherent_eleve3_contactsup_prenom, GS_asso_locales_rows[i].adherent_eleve3_contactsup_mail, GS_asso_locales_rows[i].adherent_eleve3_InstrumentA, GS_asso_locales_rows[i].adherent_eleve3_InstrumentA_prof, GS_asso_locales_rows[i].adherent_eleve3_InstrumentA_cahier, GS_asso_locales_rows[i].adherent_eleve3_InstrumentB, GS_asso_locales_rows[i].adherent_eleve3_InstrumentB_prof, GS_asso_locales_rows[i].adherent_eleve3_InstrumentB_cahier, GS_asso_locales_rows[i].adherent_eleve3_droitimage];
|
||
|
||
const [results7, fields7] = await promisePool.query(sql7, sql_values7); // Execution SQL
|
||
logger.info("INSERT - adherent_eleve 3 - Row ID added : " + results7.insertId, { numsession: req.session.id, label: filename});
|
||
}
|
||
|
||
// AJOUT DANS LA TABLE adherent_eleve 4
|
||
if (GS_asso_locales_rows[i].adherent_eleve4_nom) {
|
||
let sql8 = 'INSERT INTO adherent_eleve (date_maj, saison, identifiant, association_suzuki, adherent_telephone_mobile, adherent_mail, eleve_nom, eleve_prenom, eleve_datenaissance, eleve_contactsup_nom, eleve_contactsup_prenom, eleve_contactsup_mail, eleve_instrumentA, eleve_instrumentA_prof, eleve_instrumentA_cahier, eleve_instrumentB, eleve_instrumentB_prof, eleve_instrumentB_cahier, eleve_droitimage) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
|
||
let sql_values8 = [now, GS_asso_locales_rows[i].saison, GS_asso_locales_rows[i].identifiant, GS_asso_locales_rows[i].association, GS_asso_locales_rows[i].adherent_telephone_mobile, GS_asso_locales_rows[i].adherent_mail, GS_asso_locales_rows[i].adherent_eleve4_nom, GS_asso_locales_rows[i].adherent_eleve4_prenom, GS_asso_locales_rows[i].adherent_eleve4_datenaissance, GS_asso_locales_rows[i].adherent_eleve4_contactsup_nom, GS_asso_locales_rows[i].adherent_eleve4_contactsup_prenom, GS_asso_locales_rows[i].adherent_eleve4_contactsup_mail, GS_asso_locales_rows[i].adherent_eleve4_InstrumentA, GS_asso_locales_rows[i].adherent_eleve4_InstrumentA_prof, GS_asso_locales_rows[i].adherent_eleve4_InstrumentA_cahier, GS_asso_locales_rows[i].adherent_eleve4_InstrumentB, GS_asso_locales_rows[i].adherent_eleve4_InstrumentB_prof, GS_asso_locales_rows[i].adherent_eleve4_InstrumentB_cahier, GS_asso_locales_rows[i].adherent_eleve4_droitimage];
|
||
|
||
const [results8, fields8] = await promisePool.query(sql8, sql_values8); // Execution SQL
|
||
logger.info("INSERT - adherent_eleve 4 - Row ID added : " + results8.insertId, { numsession: req.session.id, label: filename});
|
||
}
|
||
|
||
// AJOUT DANS LA TABLE adherent_eleve 5
|
||
if (GS_asso_locales_rows[i].adherent_eleve5_nom) {
|
||
let sql9 = 'INSERT INTO adherent_eleve (date_maj, saison, identifiant, association_suzuki, adherent_telephone_mobile, adherent_mail, eleve_nom, eleve_prenom, eleve_datenaissance, eleve_contactsup_nom, eleve_contactsup_prenom, eleve_contactsup_mail, eleve_instrumentA, eleve_instrumentA_prof, eleve_instrumentA_cahier, eleve_instrumentB, eleve_instrumentB_prof, eleve_instrumentB_cahier, eleve_droitimage) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
|
||
let sql_values9 = [now, GS_asso_locales_rows[i].saison, GS_asso_locales_rows[i].identifiant, GS_asso_locales_rows[i].association, GS_asso_locales_rows[i].adherent_telephone_mobile, GS_asso_locales_rows[i].adherent_mail, GS_asso_locales_rows[i].adherent_eleve5_nom, GS_asso_locales_rows[i].adherent_eleve5_prenom, GS_asso_locales_rows[i].adherent_eleve5_datenaissance, GS_asso_locales_rows[i].adherent_eleve5_contactsup_nom, GS_asso_locales_rows[i].adherent_eleve5_contactsup_prenom, GS_asso_locales_rows[i].adherent_eleve5_contactsup_mail, GS_asso_locales_rows[i].adherent_eleve5_InstrumentA, GS_asso_locales_rows[i].adherent_eleve5_InstrumentA_prof, GS_asso_locales_rows[i].adherent_eleve5_InstrumentA_cahier, GS_asso_locales_rows[i].adherent_eleve5_InstrumentB, GS_asso_locales_rows[i].adherent_eleve5_InstrumentB_prof, GS_asso_locales_rows[i].adherent_eleve5_InstrumentB_cahier, GS_asso_locales_rows[i].adherent_eleve5_droitimage];
|
||
|
||
const [results9, fields9] = await promisePool.query(sql9, sql_values9); // Execution SQL
|
||
logger.info("INSERT - adherent_eleve 5 - Row ID added : " + results9.insertId, { numsession: req.session.id, label: filename});
|
||
}
|
||
|
||
|
||
/////////////////////////////////////////////////
|
||
// ENVOIE CARTE ADHÉRENT
|
||
//////////////////////////////////////////////////////////////////////////////
|
||
// ADOBE PDF : CRÉATION DE LA CARTE D'ADHÉSION EN PDF
|
||
// + JUSTIFICATIF DE DON
|
||
|
||
//////////////////////////////////////////////////////////////////////////////
|
||
// DÉFINIR LE PROFIL À AJOUTER DANS LA CARTE ADHÉRENT
|
||
// La fonction est dans le fichier /modules/adherent_type.js
|
||
profils = carte_adherent.profil(GS_asso_locales_rows[i].adherent_type);
|
||
profil1 = profils[0];
|
||
profil2 = profils[1];
|
||
profil3 = profils[2];
|
||
|
||
let OUTPUT;
|
||
let INPUT
|
||
|
||
// S'il y a un don
|
||
if (GS_asso_locales_rows[i].adherent_paiement_montant_don > 0) {
|
||
|
||
OUTPUT = path.resolve(__dirname, "../pdf/adhesion/2526/paye/")+"/"+GS_asso_locales_rows[i].identifiant+"_adhesion_2526_paye_don_"+now_pdf+".pdf";
|
||
INPUT = path.resolve(__dirname, "../pdf/template/carte_adherent_don.docx");
|
||
|
||
} else {
|
||
|
||
// S'il n'y a pas de don
|
||
OUTPUT = path.resolve(__dirname, "../pdf/adhesion/2526/paye/")+"/"+GS_asso_locales_rows[i].identifiant+"_adhesion_2526_paye_"+now_pdf+".pdf";
|
||
INPUT = path.resolve(__dirname, "../pdf/template/carte_adherent.docx");
|
||
|
||
}
|
||
|
||
moment.locale('fr');
|
||
var date_formattee = moment().format('L')
|
||
var today = date_formattee;
|
||
|
||
var adresse = GS_asso_locales_rows[i].adherent_adresse_rue;
|
||
adresse += " ";
|
||
adresse += GS_asso_locales_rows[i].adherent_adresse_codepostal;
|
||
adresse += " ";
|
||
adresse += GS_asso_locales_rows[i].adherent_adresse_ville;
|
||
|
||
if (profil2 == undefined) {profil2 = " "};
|
||
if (profil3 == undefined) {profil3 = ""};
|
||
|
||
|
||
JSON_INPUT =
|
||
{
|
||
"date": today,
|
||
"identifiant": GS_asso_locales_rows[i].identifiant,
|
||
"profil1": profil1,
|
||
"profil2": profil2,
|
||
"profil3": profil3,
|
||
"mode_paiement": GS_asso_locales_rows[i].adherent_paiement_mode,
|
||
"nom": GS_asso_locales_rows[i].adherent_nom,
|
||
"prenom": GS_asso_locales_rows[i].adherent_prenom,
|
||
"adresse": adresse,
|
||
"montant_don": GS_asso_locales_rows[i].adherent_paiement_montant_don
|
||
};
|
||
|
||
const documentMerge = PDFServicesSdk.DocumentMerge,
|
||
documentMergeOptions = documentMerge.options,
|
||
adobe_options = new documentMergeOptions.DocumentMergeOptions(JSON_INPUT, documentMergeOptions.OutputFormat.PDF);
|
||
|
||
// Create a new operation instance using the options instance.
|
||
const documentMergeOperation = documentMerge.Operation.createNew(adobe_options);
|
||
|
||
// Set operation input document template from a source file.
|
||
const input = PDFServicesSdk.FileRef.createFromLocalFile(INPUT);
|
||
documentMergeOperation.setInput(input);
|
||
|
||
// Execute the operation and Save the result to the specified location.
|
||
await documentMergeOperation
|
||
.execute(executionContext)
|
||
.then(result => result.saveAsFile(OUTPUT).then(
|
||
)
|
||
)
|
||
.catch(err => {
|
||
if(err instanceof PDFServicesSdk.Error.ServiceApiError
|
||
|| err instanceof PDFServicesSdk.Error.ServiceUsageError) {
|
||
console.log('Exception encountered while executing ADOBE PDF', err);
|
||
} else {
|
||
console.log('Exception encountered while executing ADOBE PDF', err);
|
||
}
|
||
});
|
||
|
||
|
||
//////////////////////////////////////////////////////////////////////////////
|
||
// ENVOIE CARTE ADHERENT + JUSTIFICATIF DE DON PAR MAIL
|
||
|
||
console.log("MAIL ENVOI AVANT");
|
||
var transporter = nodemailer.createTransport({
|
||
service: 'gmail',
|
||
auth: {
|
||
user: process.env.nodemailer_user,
|
||
pass: process.env.nodemailer_pass
|
||
}
|
||
});
|
||
|
||
var attachment_name = GS_asso_locales_rows[i].identifiant;
|
||
attachment_name += "_adhesion_2526_carteadherent.pdf";
|
||
var attachment_don_name = GS_asso_locales_rows[i].identifiant;
|
||
attachment_don_name += "_don.pdf";
|
||
|
||
var mail_objet = "ASSOCIATION FRANÇAISE POUR LA PÉDAGOGIE SUZUKI - ADHÉSION 2025/2026 - ";
|
||
mail_objet += GS_asso_locales_rows[i].identifiant;
|
||
mail_html = "<p>Cher adhérent,</p><p>Félicitations ! Vous êtes désormais membre de l’Association Française pour la Pédagogie Suzuki (http://www.afpedagogiesuzuki.fr) et de l’European Suzuki Association (https://europeansuzuki.org) pour l'année 2025/2026. Vous pouvez à ce titre participer à tous les événements Suzuki en France et dans le monde.</p><p>Vous trouverez en pièce jointe votre carte d'adhésion annuelle (valable du 1er septembre 2025 au 31 août 2026).</p><p>Votre identifiant (valable pour toute la famille) est ";
|
||
mail_html += GS_asso_locales_rows[i].identifiant;
|
||
mail_html += ".</p>";
|
||
if (GS_asso_locales_rows[i].adherent_paiement_montant_don > 0) {
|
||
mail_html += "<br><p>Nous vous remercions chaleureusement pour votre don. Votre justificatif est également joint à ce mail</p>";
|
||
}
|
||
mail_html += "<p><br>Nous vous remercions pour votre intérêt et vous souhaitons une très belle année en musique !</p><p>Association Française pour la pédagogie Suzuki<br>Chez Anne PAGES<br>30 rue des Tattes<br>74500 PUBLIER</p>";
|
||
|
||
|
||
var mailOptions = {
|
||
from: 'afps.inscription@gmail.com',
|
||
to: GS_asso_locales_rows[i].adherent_mail,
|
||
subject: mail_objet,
|
||
html: mail_html,
|
||
attachments: [{
|
||
filename: attachment_name,
|
||
path: OUTPUT
|
||
}
|
||
]
|
||
};
|
||
|
||
transporter.sendMail(mailOptions, function(error, info){
|
||
if (error) {
|
||
logger.info("error : " + error, { numsession: req.session.id });
|
||
} else {
|
||
console.log('Email sent: ' + info.response);
|
||
}
|
||
});
|
||
console.log("MAIL ENVOI APRES");
|
||
|
||
|
||
// METTRE À JOUR LE STATUS D'ENVOI DE LA CARTE
|
||
let sql10 = 'UPDATE ';
|
||
sql10 += 'adherent';
|
||
sql10 += ' SET ';
|
||
sql10 += 'carte_envoi = "definitive"';
|
||
sql10 += ' WHERE identifiant = "' + GS_asso_locales_rows[i].identifiant + '"';
|
||
|
||
const [results10, fields10] = await promisePool.query(sql10); // Execution SQL
|
||
logger.info("adherent carte envoi Row ID changed : " + results10.changedRows, { numsession: req.session.id, label: filename});
|
||
|
||
|
||
|
||
/////////////////////////////////////////////////
|
||
// GS - Taguer l'adhérent comme importé dans le tableau Google Sheet
|
||
GS_asso_locales_rows[i].date_export = now;
|
||
await GS_asso_locales_rows[i].save();
|
||
await sleep.wait(2000);
|
||
|
||
nb_import++;
|
||
}
|
||
}
|
||
|
||
/////////////////////////////////////////////////
|
||
// ON AFFICHE LE NOMBRE DE LIGNES IMPORTÉES
|
||
req.session.message4 = 'Adhérents associations locales importées : ' + nb_import;
|
||
res.redirect('/adhesion/maj_googlesheet');
|
||
|
||
} catch (error) {
|
||
// Echec de connection à la BD
|
||
logger.error("Il y a un problème avec la connection à la base de données : " + error, { numsession: req.session.id });
|
||
req.session.message4 = "Il y a un problème système, veuillez recommencer. " + error;
|
||
//pool.end();
|
||
res.redirect('/adhesion/maj_googlesheet');
|
||
};
|
||
|
||
}
|
||
|
||
|
||
/////////////////////////////////////////////////
|
||
// EXPORT DE MODULE POUR QU'IL SOIT LU DANS LES AUTRES FICHIERS JS
|
||
/////////////////////////////////////////////////
|
||
module.exports = {
|
||
import_ass_locales_v2
|
||
} |