1130 lines
67 KiB
JavaScript
1130 lines
67 KiB
JavaScript
/////////////////////////////////////////////////
|
||
// LOGGING
|
||
/////////////////////////////////////////////////
|
||
const logger = require("../../public/js/app/logger_concert");
|
||
|
||
/////////////////////////////////////////////////
|
||
// APPELS DES MODULES
|
||
/////////////////////////////////////////////////
|
||
const path = require("path");
|
||
const fs = require("fs");
|
||
// CALCUL DE DATE
|
||
const moment = require('moment');
|
||
// ENVOIE MAIL GOOGLE MAIL
|
||
const nodemailer = require('nodemailer');
|
||
// CONNECTION BASE DE DONNÉES
|
||
const mysql = require('mysql2');
|
||
const mysql_promise = require('mysql2/promise');
|
||
|
||
// ADOBE PDF
|
||
const PDFServicesSdk = require('@adobe/pdfservices-node-sdk');
|
||
// FETCH (utile pour nodejs version < 18)
|
||
const fetch = require("node-fetch");
|
||
|
||
/////////////////////////////////////////////////
|
||
// GOOGLE SHEET PARAMETERS
|
||
/////////////////////////////////////////////////
|
||
const { GoogleSpreadsheet } = require('google-spreadsheet');
|
||
const GS_creds = require('../../inscriptionsafps-53b60452f63d.json');
|
||
const { error } = require("console");
|
||
// Fichier Google Sheet "afps_concert_annuel" (Mon Drive/inscriptions/base_donees)
|
||
// doc ID is the long id in the sheets URL
|
||
const GS_concert = new GoogleSpreadsheet(process.env.gsheet_concertannuel);
|
||
|
||
/////////////////////////////////////////////////
|
||
// APPELS DES FONCTIONS
|
||
/////////////////////////////////////////////////
|
||
const sleep = require("./../../modules/sleep");
|
||
const envoie_mail = require("./../../modules/envoie_mail");
|
||
const pdf = require("./../../modules/pdf");
|
||
|
||
////////////////////////////////////////////////////
|
||
// FILE NAME
|
||
////////////////////////////////////////////////////
|
||
const filename = path.parse(__filename).base;
|
||
|
||
|
||
////////////////////////////////////////////////////
|
||
// CONNECTION DB
|
||
////////////////////////////////////////////////////
|
||
async function connect_DB_promise() {
|
||
return db_connection_promise = await mysql_promise.createConnection({
|
||
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
|
||
});
|
||
}
|
||
|
||
/////////////////////////////////////////////////
|
||
// 5 - CLIC SUR VALIDER
|
||
/////////////////////////////////////////////////
|
||
|
||
const payer = async function (req, res) {
|
||
|
||
logger.info("======================================", { label: filename });
|
||
logger.info("======== FUNCTION payer (enregistrement BD + envoie vers Stripe) ==========", { label: filename });
|
||
logger.info("======================================", { label: filename });
|
||
|
||
logger.info("req.session.adherent_mail = " + req.session.adherent_mail , { numsession: req.session.id, label: filename});
|
||
logger.info("req.session.deja_inscrit_concert = " + req.session.deja_inscrit_concert , { numsession: req.session.id, label: filename});
|
||
|
||
let sql;
|
||
let table;
|
||
let champs;
|
||
let values;
|
||
let saison = "2025/2026";
|
||
moment.locale('fr');
|
||
let now = moment().format("YYYY-MM-DD HH:mm:ss");
|
||
var today = moment().format('L');
|
||
let commande_nbr_places_concertiste = 0;
|
||
let commande_nbr_places_encadrant_parent = 0;
|
||
let commande_nbr_places_encadrant_prof = 0;
|
||
let piano_commandes = 0;
|
||
let alto_commandes = 0;
|
||
let flute_commandes = 0;
|
||
let trompette_commandes = 0;
|
||
let harpe_commandes = 0;
|
||
let violon1_commandes = 0;
|
||
let violon2_commandes = 0;
|
||
let violoncelle1_commandes = 0;
|
||
let violoncelle2_commandes = 0;
|
||
var mail_objet;
|
||
var mail_html;
|
||
|
||
|
||
// FONCTION POUR AFFICHER LA PAGE EN CAS D'ERREUR
|
||
function affiche_page_admin() {
|
||
////////////////////////////////////////////////////
|
||
// AFFIHER LA PAGE /identifier
|
||
// AFFICHAGE DES ERREURS
|
||
// EN CAS DE RETOUR SUR LA PAGE AVEC ERREUR :
|
||
// - SI IDENTIFIANT NON ADHÉRENT
|
||
// - SI RIEN SAISIE
|
||
////////////////////////////////////////////////////
|
||
|
||
if (req.session.err_mail_form_adhesion_1) {
|
||
res.locals.err_mail_form_adhesion_1 = req.session.err_mail_form_adhesion_1;
|
||
res.locals.err_mail_form_adhesion_2 = req.session.err_mail_form_adhesion_2;
|
||
res.locals.err_mail_form_adhesion_3 = req.session.err_mail_form_adhesion_3;
|
||
// on supprime le contenu après affichage
|
||
req.session.err_mail_form_adhesion_1 = undefined;
|
||
req.session.err_mail_form_adhesion_2 = undefined;
|
||
req.session.err_mail_form_adhesion_3 = undefined;
|
||
}
|
||
res.render('concert/page_templates/admin');
|
||
};
|
||
|
||
/////////////////////////////////////////////////
|
||
// SI IDENTIFIANT PERDU
|
||
/////////////////////////////////////////////////
|
||
logger.info("req.session.identifiant : " + req.session.identifiant , { numsession: req.session.id, label: filename});
|
||
if (req.session.identifiant === undefined) {
|
||
logger.info("IDENTIFIANT PERDU", { numsession: req.session.id });
|
||
req.session.err_mail_form_adhesion_1 = "Session expirée, veuillez recommencer";
|
||
affiche_page_admin();
|
||
} else {
|
||
|
||
/////////////////////////////////////////////////
|
||
// AJOUT DANS LA BASE DE DONNÉES
|
||
/////////////////////////////////////////////////
|
||
|
||
if (req.session.deja_inscrit_concert == "non") {
|
||
logger.info("NOUVELLE INSCRIPTION AU CONCERT", { numsession: req.session.id, label: filename});
|
||
} else if (req.session.deja_inscrit_concert == "oui") {
|
||
logger.info("AJOUT DE BILLETS POUR UN ADHÉRENT DÉJÀ INSCRIT AU CONCERT", { numsession: req.session.id, label: filename});
|
||
}
|
||
|
||
|
||
// CONNECTION DB PROMISE
|
||
try {
|
||
//Connexion
|
||
let db_connection_promise = await connect_DB_promise();
|
||
|
||
//Queries
|
||
|
||
/////////////////////////////////////////////////
|
||
// AJOUT CONCERTISTES
|
||
for (let toto = 1; toto < 5; toto++) {
|
||
if (req.session["eleve_"+toto+"_nom"]) {
|
||
|
||
table = "ca_concertiste";
|
||
if (req.session.deja_inscrit_concert == "non") {
|
||
champs = "date_maj,saison,identifiant,concertiste_nom,concertiste_prenom,concertiste_age,concertiste_intrument,concertiste_cahier,concertiste_piece,concertiste_prof_nom,concertiste_prof_mail";
|
||
values = '"'+ now + '","' + saison + '"' + ',"' + req.session.identifiant + '","' + req.session["eleve_"+toto+"_nom"] + '","' + req.session["eleve_"+toto+"_prenom"] + '",' + req.session["eleve_"+toto+"_age"] + ',"' + req.session["eleve_"+toto+"_instrument"] + '","' + req.session["eleve_"+toto+"_cahier"] + '","' + req.session["eleve_"+toto+"_piece"] + '","' + req.session["eleve_"+toto+"_prof_nom"] + '","' + req.session["eleve_"+toto+"_prof_mail"] + '"';
|
||
} else if (req.session.deja_inscrit_concert == "oui") {
|
||
champs = "date_maj,saison,identifiant,concertiste_nom,concertiste_prenom,concertiste_age,concertiste_intrument,concertiste_cahier,concertiste_piece,concertiste_prof_nom,concertiste_prof_mail,ajout";
|
||
values = '"'+ now + '","' + saison + '"' + ',"' + req.session.identifiant + '","' + req.session["eleve_"+toto+"_nom"] + '","' + req.session["eleve_"+toto+"_prenom"] + '",' + req.session["eleve_"+toto+"_age"] + ',"' + req.session["eleve_"+toto+"_instrument"] + '","' + req.session["eleve_"+toto+"_cahier"] + '","' + req.session["eleve_"+toto+"_piece"] + '","' + req.session["eleve_"+toto+"_prof_nom"] + '","' + req.session["eleve_"+toto+"_prof_mail"] + '","' + now + '"';
|
||
}
|
||
|
||
sql = 'INSERT INTO ' + table + ' (' + champs + ') VALUES (' + values + ')';
|
||
logger.info("REQUETE SQL ca_concertiste : " + sql , { numsession: req.session.id, label: filename});
|
||
|
||
const [results, fields] = await db_connection_promise.execute(sql);
|
||
logger.info("CONCERTISTES Row ID added : " + results.insertId, { numsession: req.session.id, label: filename});
|
||
|
||
// COMPTEUR DE PLACES
|
||
commande_nbr_places_concertiste += 1;
|
||
if (req.session["eleve_"+toto+"_instrument"] == "Piano") {
|
||
piano_commandes += 1;
|
||
}
|
||
if (req.session["eleve_"+toto+"_instrument"] == "Alto") {
|
||
alto_commandes += 1;
|
||
}
|
||
if (req.session["eleve_"+toto+"_instrument"] == "Flute") {
|
||
flute_commandes += 1;
|
||
}
|
||
if (req.session["eleve_"+toto+"_instrument"] == "Trompette") {
|
||
trompette_commandes += 1;
|
||
}
|
||
if (req.session["eleve_"+toto+"_instrument"] == "Harpe") {
|
||
harpe_commandes += 1;
|
||
}
|
||
if (req.session["eleve_"+toto+"_instrument"] == "Violon") {
|
||
switch (req.session["eleve_"+toto+"_cahier"]) {
|
||
case 'Cahier 1':
|
||
violon1_commandes += 1;
|
||
break;
|
||
case 'Cahier 2':
|
||
violon1_commandes += 1;
|
||
break;
|
||
case 'Cahier 3':
|
||
violon1_commandes += 1;
|
||
break;
|
||
case 'Cahier 4':
|
||
violon1_commandes += 1;
|
||
break;
|
||
case 'Cahier 5':
|
||
violon1_commandes += 1;
|
||
break;
|
||
case 'Cahier 6':
|
||
violon2_commandes += 1;
|
||
break;
|
||
case 'Cahier 7':
|
||
violon2_commandes += 1;
|
||
break;
|
||
case 'Cahier 8':
|
||
violon2_commandes += 1;
|
||
break;
|
||
case 'Cahier 9':
|
||
violon2_commandes += 1;
|
||
break;
|
||
case 'Cahier 10':
|
||
violon2_commandes += 1;
|
||
break;
|
||
default:
|
||
logger.info("Erreur détecté dans le switch req.session[eleve_+toto+_instrument] == Violon", { numsession: req.session.id, label: filename});
|
||
}
|
||
}
|
||
if (req.session["eleve_"+toto+"_instrument"] == "Violoncelle") {
|
||
switch (req.session["eleve_"+toto+"_cahier"]) {
|
||
case 'Cahier 1':
|
||
violoncelle1_commandes += 1;
|
||
break;
|
||
case 'Cahier 2':
|
||
violoncelle1_commandes += 1;
|
||
break;
|
||
case 'Cahier 3':
|
||
violoncelle1_commandes += 1;
|
||
break;
|
||
case 'Cahier 4':
|
||
violoncelle1_commandes += 1;
|
||
break;
|
||
case 'Cahier 5':
|
||
violoncelle2_commandes += 1;
|
||
break;
|
||
case 'Cahier 6':
|
||
violoncelle2_commandes += 1;
|
||
break;
|
||
case 'Cahier 7':
|
||
violoncelle2_commandes += 1;
|
||
break;
|
||
case 'Cahier 8':
|
||
violoncelle2_commandes += 1;
|
||
break;
|
||
case 'Cahier 9':
|
||
violoncelle2_commandes += 1;
|
||
break;
|
||
case 'Cahier 10':
|
||
violoncelle2_commandes += 1;
|
||
break;
|
||
default:
|
||
logger.info("Erreur détecté dans le switch req.session[eleve_+toto+_instrument] == Violoncelle", { numsession: req.session.id, label: filename});
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
/////////////////////////////////////////////////
|
||
// AJOUT ENCADRANTS
|
||
let compteur_encadrants = 0;
|
||
for (let toto = 1; toto < 5; toto++) {
|
||
if (req.session["encadrant_"+toto+"_nom"]) {
|
||
|
||
|
||
// SI PARENT, ne rien mettre dans instrument (évite qu'il y ait "undefined")
|
||
if (!req.session["encadrant_"+toto+"_prof_instrument"]) {req.session["encadrant_"+toto+"_prof_instrument"] = ''};
|
||
|
||
table = "ca_encadrant";
|
||
|
||
|
||
if (req.session.deja_inscrit_concert == "non") {
|
||
champs = "date_maj,saison,identifiant,encadrant_type,encadrant_nom,encadrant_prenom,encadrant_telephone_mobile,encadrant_mail,encadrant__prof_instrument";
|
||
values = '"'+ now + '","' + saison + '"' + ',"' + req.session.identifiant + '","' + req.session["encadrant_"+toto+"_type"] + '","' + req.session["encadrant_"+toto+"_nom"] + '","' + req.session["encadrant_"+toto+"_prenom"] + '","' + req.session["encadrant_"+toto+"_telephone_mobile"] + '","' + req.session["encadrant_"+toto+"_mail"] + '","' + req.session["encadrant_"+toto+"_prof_instrument"] + '"';
|
||
} else if (req.session.deja_inscrit_concert == "oui") {
|
||
champs = "date_maj,saison,identifiant,encadrant_type,encadrant_nom,encadrant_prenom,encadrant_telephone_mobile,encadrant_mail,encadrant__prof_instrument, ajout";
|
||
values = '"'+ now + '","' + saison + '"' + ',"' + req.session.identifiant + '","' + req.session["encadrant_"+toto+"_type"] + '","' + req.session["encadrant_"+toto+"_nom"] + '","' + req.session["encadrant_"+toto+"_prenom"] + '","' + req.session["encadrant_"+toto+"_telephone_mobile"] + '","' + req.session["encadrant_"+toto+"_mail"] + '","' + req.session["encadrant_"+toto+"_prof_instrument"] + '","' + now + '"';
|
||
}
|
||
|
||
|
||
sql = 'INSERT INTO ' + table + ' (' + champs + ') VALUES (' + values + ')';
|
||
logger.info("REQUETE SQL ca_encadrant : " + sql , { numsession: req.session.id, label: filename});
|
||
const [results, fields] = await db_connection_promise.execute(sql);
|
||
|
||
// COMPTEUR DE PLACES
|
||
if (req.session["encadrant_"+toto+"_type"]=="Professeur") {
|
||
commande_nbr_places_encadrant_prof += 1;
|
||
};
|
||
if (req.session["encadrant_"+toto+"_type"]=="Parent") {
|
||
commande_nbr_places_encadrant_parent += 1;
|
||
};
|
||
compteur_encadrants++;
|
||
}
|
||
|
||
}
|
||
|
||
/////////////////////////////////////////////////
|
||
// AJOUT DB INFORMATIONS ADHÉRENTS
|
||
|
||
// SI C'EST SA PREMIÈRE INSCRIPTION
|
||
if (req.session.deja_inscrit_concert == "non") {
|
||
|
||
table = "ca_adherent";
|
||
|
||
sql = 'INSERT INTO ';
|
||
sql += table;
|
||
sql += ' (';
|
||
sql += 'date_inscription, ';
|
||
sql += 'date_maj, ';
|
||
sql += 'saison, ';
|
||
sql += 'identifiant, ';
|
||
sql += 'association_suzuki, ';
|
||
sql += 'adherent_nom, ';
|
||
sql += 'adherent_prenom, ';
|
||
sql += 'adherent_telephone_mobile, ';
|
||
sql += 'adherent_mail, ';
|
||
sql += 'hebergement_ville, ';
|
||
sql += 'hebergement_places, ';
|
||
sql += 'commande_nb_spectateur, ';
|
||
sql += 'comment, ';
|
||
sql += 'commande_nb_concertiste, ';
|
||
sql += 'commande_nb_encadr_prof, ';
|
||
sql += 'commande_nb_encadr_par, ';
|
||
sql += 'commande_cout_concertiste, ';
|
||
sql += 'commande_cout_spectateur, ';
|
||
sql += 'don, ';
|
||
sql += 'stripe_statut_paiement, ';
|
||
sql += 'montant_total';
|
||
sql += ') VALUES (';
|
||
sql += '"'+ now +'",';
|
||
sql += '"'+ now +'",';
|
||
sql += '"'+ saison +'",';
|
||
sql += '"'+ req.session.identifiant +'",';
|
||
sql += '"'+ req.session.association_suzuki +'",';
|
||
sql += '"'+ req.session.adherent_nom +'",';
|
||
sql += '"'+ req.session.adherent_prenom +'",';
|
||
sql += '"'+ req.session.adherent_telephone_mobile +'",';
|
||
sql += '"'+ req.session.adherent_mail +'",';
|
||
sql += '"'+ req.session.hebergement_ville +'",';
|
||
sql += ''+ req.session.hebergement_nb +',';
|
||
sql += ''+ req.session.spectateur_nb_place +',';
|
||
sql += '"'+ req.session.commentaire_adherent +'",';
|
||
sql += ''+ commande_nbr_places_concertiste +',';
|
||
sql += ''+ commande_nbr_places_encadrant_prof +',';
|
||
sql += ''+ commande_nbr_places_encadrant_parent +',';
|
||
sql += ''+ req.session.cout_concertiste +',';
|
||
sql += ''+ req.session.cout_spectateur +',';
|
||
sql += ''+ parseInt(req.session.don) +',';
|
||
if (req.session.montant_total > 0) {
|
||
sql += '"attente",';
|
||
} else {
|
||
sql += '"gratuit",';
|
||
}
|
||
|
||
sql += ''+ req.session.montant_total;
|
||
sql += ')';
|
||
|
||
logger.info("REQUETE SQL ca_adherent : " + sql , { numsession: req.session.id, label: filename});
|
||
const [results_insert_ca_adherent, fields_insert_ca_adherent] = await db_connection_promise.execute(sql);
|
||
logger.info("ADHÉRENTS Row ID added : " + results_insert_ca_adherent.insertId, { numsession: req.session.id, label: filename});
|
||
|
||
} else if (req.session.deja_inscrit_concert == "oui") {
|
||
|
||
// SI C'EST UN AJOUT DE BILLET
|
||
|
||
table = "ca_ajout";
|
||
|
||
sql = 'INSERT INTO ';
|
||
sql += table;
|
||
sql += ' (';
|
||
sql += 'date_maj, ';
|
||
sql += 'saison, ';
|
||
sql += 'identifiant, ';
|
||
sql += 'commande_nb_spectateur, ';
|
||
sql += 'commande_nb_concertiste, ';
|
||
sql += 'commande_nb_encadr_prof, ';
|
||
sql += 'commande_nb_encadr_par, ';
|
||
sql += 'commande_cout_concertiste, ';
|
||
sql += 'commande_cout_spectateur, ';
|
||
sql += 'don, ';
|
||
sql += 'stripe_statut_paiement, ';
|
||
sql += 'montant_total';
|
||
sql += ') VALUES (';
|
||
sql += '"'+ now +'",';
|
||
sql += '"'+ saison +'",';
|
||
sql += '"'+ req.session.identifiant +'",';
|
||
sql += ''+ req.session.spectateur_nb_place +',';
|
||
sql += ''+ commande_nbr_places_concertiste +',';
|
||
sql += ''+ commande_nbr_places_encadrant_prof +',';
|
||
sql += ''+ commande_nbr_places_encadrant_parent +',';
|
||
sql += ''+ req.session.cout_concertiste +',';
|
||
sql += ''+ req.session.cout_spectateur +',';
|
||
sql += ''+ parseInt(req.session.don) +',';
|
||
if (req.session.montant_total > 0) {
|
||
sql += '"attente",';
|
||
} else {
|
||
sql += '"gratuit",';
|
||
}
|
||
|
||
sql += ''+ req.session.montant_total;
|
||
sql += ')';
|
||
|
||
logger.info("REQUETE SQL ca_ajout : " + sql , { numsession: req.session.id, label: filename});
|
||
const [results_insert_ca_adherent, fields_insert_ca_adherent] = await db_connection_promise.execute(sql);
|
||
logger.info("ca_ajout Row ID added : " + results_insert_ca_adherent.insertId, { numsession: req.session.id, label: filename});
|
||
|
||
|
||
}
|
||
|
||
|
||
|
||
|
||
} 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.err_mail_form_adhesion_1 = '"Il y a un problème système connexion base de données, veuillez recommencer, "' + error;
|
||
//await db_connection_promise.end();
|
||
affiche_page_admin();
|
||
}
|
||
|
||
}
|
||
|
||
|
||
|
||
|
||
logger.info("======================================");
|
||
logger.info("======== payer ==========");
|
||
logger.info("======================================");
|
||
|
||
|
||
let sql11;
|
||
let sql12;
|
||
let sql13;
|
||
let sql14;
|
||
var mail_objet;
|
||
var mail_html;
|
||
moment.locale('fr');
|
||
|
||
var today = moment().format('L');
|
||
var identifiant;
|
||
let compteur_piano = 0
|
||
let compteur_harpe = 0
|
||
let compteur_trompette = 0
|
||
let compteur_flute = 0
|
||
let compteur_alto = 0
|
||
let compteur_violon1 = 0
|
||
let compteur_violon2 = 0
|
||
let compteur_violoncelle1 = 0
|
||
let compteur_violoncelle2 = 0
|
||
|
||
// FONCTION POUR AFFICHER LA PAGE EN CAS D'ERREUR
|
||
function affiche_page_admin() {
|
||
////////////////////////////////////////////////////
|
||
// AFFIHER LA PAGE /identifier
|
||
// AFFICHAGE DES ERREURS
|
||
// EN CAS DE RETOUR SUR LA PAGE AVEC ERREUR :
|
||
// - SI IDENTIFIANT NON ADHÉRENT
|
||
// - SI RIEN SAISIE
|
||
////////////////////////////////////////////////////
|
||
|
||
if (req.session.err_mail_form_adhesion_1) {
|
||
res.locals.err_mail_form_adhesion_1 = req.session.err_mail_form_adhesion_1;
|
||
res.locals.err_mail_form_adhesion_2 = req.session.err_mail_form_adhesion_2;
|
||
res.locals.err_mail_form_adhesion_3 = req.session.err_mail_form_adhesion_3;
|
||
// on supprime le contenu après affichage
|
||
req.session.err_mail_form_adhesion_1 = undefined;
|
||
req.session.err_mail_form_adhesion_2 = undefined;
|
||
req.session.err_mail_form_adhesion_3 = undefined;
|
||
}
|
||
res.render('concert/page_templates/admin');
|
||
};
|
||
|
||
|
||
|
||
|
||
|
||
var customer_email = req.session.adherent_mail;
|
||
var payment_status = "paye";
|
||
var amount_total = req.session.montant_total;
|
||
|
||
identifiant = req.session.identifiant;
|
||
|
||
|
||
try {
|
||
|
||
//Connexion
|
||
let db_connection_promise = await connect_DB_promise();
|
||
|
||
console.log("req.session.deja_inscrit_concert = ",req.session.deja_inscrit_concert);
|
||
|
||
// SI MODE INSCRIPTION INITIALE
|
||
if (req.session.deja_inscrit_concert == "non") {
|
||
|
||
//Query
|
||
sql = 'UPDATE ';
|
||
sql += 'ca_adherent';
|
||
sql += ' SET ';
|
||
sql += 'stripe_retour = "paiement manuel", ';
|
||
sql += 'stripe_date = "' + now + '", ';
|
||
|
||
sql += 'stripe_statut_paiement = "paye"';
|
||
sql += ' WHERE identifiant = "' + identifiant + '"';
|
||
|
||
console.log("SQL 123 = ", sql);
|
||
|
||
const [results, fields] = await db_connection_promise.execute(sql);
|
||
logger.info("Row ca_adherent UPDATE STRIPE: " + results.changedRows, { numsession: req.session.id, label: filename});
|
||
|
||
//ca_concertiste
|
||
sql11 = 'SELECT saison, concertiste_nom, concertiste_prenom, concertiste_age, concertiste_intrument, concertiste_cahier, concertiste_piece, concertiste_prof_nom, concertiste_prof_mail, ajout FROM ca_concertiste where identifiant = "' + identifiant + '"';
|
||
//ca_encadrant
|
||
sql12 = 'SELECT saison, encadrant_type, encadrant_nom, encadrant_prenom, encadrant_telephone_mobile, encadrant_mail, encadrant__prof_instrument, ajout FROM ca_encadrant where identifiant = "' + identifiant + '"';
|
||
//Nombre de place commandées
|
||
sql13 = 'SELECT commande_nb_spectateur, commande_nb_concertiste, commande_nb_encadr_prof, commande_nb_encadr_par, commande_cout_concertiste, commande_cout_spectateur, don, montant_total, stripe_retour FROM ca_adherent where identifiant = "' + identifiant + '"';
|
||
//ca_adherent
|
||
sql14 = 'SELECT saison, date_inscription, date_maj, association_suzuki, adherent_nom, adherent_prenom, adherent_telephone_mobile, adherent_mail, hebergement_ville, hebergement_places, comment FROM ca_adherent where identifiant = "' + identifiant + '"';
|
||
|
||
}
|
||
|
||
// SI MODE INSCRIPTION AJOUT DE BILLET
|
||
// ON SÉLECTIONNE LES CONCERTISTES ET LES ENCADRANTS QUI ONT QUELQUE CHOSE DANS LA CHAMP "AJOUT"
|
||
if (req.session.deja_inscrit_concert == "oui") {
|
||
//Query
|
||
sql = 'UPDATE ';
|
||
sql += 'ca_ajout';
|
||
sql += ' SET ';
|
||
sql += 'stripe_statut_paiement = "paye"';
|
||
sql += ' WHERE identifiant = "' + identifiant + '"';
|
||
|
||
const [results, fields] = await db_connection_promise.execute(sql);
|
||
logger.info("Row ca_adherent UPDATE STRIPE: " + results.changedRows, { numsession: req.session.id, label: filename});
|
||
|
||
//ca_concertiste
|
||
sql11 = 'SELECT saison, concertiste_nom, concertiste_prenom, concertiste_age, concertiste_intrument, concertiste_cahier, concertiste_piece, concertiste_prof_nom, concertiste_prof_mail, ajout FROM ca_concertiste where identifiant = "' + identifiant + '" AND ajout IS NOT NULL';
|
||
//ca_encadrant
|
||
sql12 = 'SELECT saison, encadrant_type, encadrant_nom, encadrant_prenom, encadrant_telephone_mobile, encadrant_mail, encadrant__prof_instrument, ajout FROM ca_encadrant where identifiant = "' + identifiant + '" AND ajout IS NOT NULL';
|
||
//Nombre de place commandées
|
||
sql13 = 'SELECT commande_nb_spectateur, commande_nb_concertiste, commande_nb_encadr_prof, commande_nb_encadr_par, commande_cout_concertiste, commande_cout_spectateur, don, montant_total, stripe_retour FROM ca_ajout where identifiant = "' + identifiant + '"';
|
||
//ca_adherent
|
||
sql14 = 'SELECT saison, date_inscription, date_maj, association_suzuki, adherent_nom, adherent_prenom, adherent_telephone_mobile, adherent_mail FROM ca_adherent where identifiant = "' + identifiant + '"';
|
||
}
|
||
|
||
//ca_concertiste
|
||
const [results_ca_concertiste, fields_ca_concertiste] = await db_connection_promise.execute(sql11);
|
||
logger.info("Row ca_concertiste RECUP INFOS : " + results_ca_concertiste.length, { numsession: req.session.id, label: filename});
|
||
|
||
//ca_encadrant
|
||
const [results_ca_encadrant, fields_ca_encadrant] = await db_connection_promise.execute(sql12);
|
||
logger.info("Row ca_encadrant RECUP INFOS : " + results_ca_encadrant.length, { numsession: req.session.id, label: filename});
|
||
|
||
//Nombre de place commandées
|
||
const [results_nb_commande, fields_nb_commande] = await db_connection_promise.execute(sql13);
|
||
logger.info("Row results_nb_commande RECUP INFOS : " + results_nb_commande.length, { numsession: req.session.id, label: filename});
|
||
|
||
// RÉCUPÉRATION DES INFORMATIONS PARTICIPANTS POUR ENVOIE MAIL AVEC RÉCAP
|
||
//ca_adherent
|
||
const [results_ca_adherent, fields_ca_adherent] = await db_connection_promise.execute(sql14);
|
||
logger.info("Row ca_adherent RECUP INFOS: " + results_ca_adherent.length, { numsession: req.session.id, label: filename});
|
||
|
||
// ADRESSE ADHÉRENT adherent
|
||
sql = 'SELECT adherent_adresse_rue, adherent_adresse_codepostal, adherent_adresse_ville FROM adherent where identifiant = "' + identifiant + '"';
|
||
const [results_adherent, fields_adherent] = await db_connection_promise.execute(sql);
|
||
logger.info("Row adherent RECUP INFOS: " + results_adherent.length, { numsession: req.session.id, label: filename});
|
||
|
||
|
||
|
||
// AJOUT DÉCOMPTE DES PLACES
|
||
// SI PAIEMENT REUSSI
|
||
// !!! Au démarrage de la campagne les champs de la table doivent être à 0 (zéro) et non vide ou null
|
||
|
||
|
||
|
||
sql = 'UPDATE ';
|
||
sql += 'ca_decompte_place';
|
||
sql += ' SET ';
|
||
sql += 'date_maj = "' + now + '", ';
|
||
if (results_nb_commande[0].commande_nb_spectateur > 0) { sql += 'spectateur_commandes = spectateur_commandes + ' + results_nb_commande[0].commande_nb_spectateur + ', '; }
|
||
if (results_nb_commande[0].commande_nb_concertiste > 0) { sql += 'concertiste_commandes = concertiste_commandes + ' + results_nb_commande[0].commande_nb_concertiste + ', '; }
|
||
if (results_nb_commande[0].commande_nb_encadr_prof > 0) { sql += 'encadr_prof_commandes = encadr_prof_commandes + ' + results_nb_commande[0].commande_nb_encadr_prof + ', '; }
|
||
if (results_nb_commande[0].commande_nb_encadr_par > 0) { sql += 'encadr_parent_commandes = encadr_parent_commandes + ' + results_nb_commande[0].commande_nb_encadr_par + ', '; }
|
||
logger.info("A3 VANT LE IF results_ca_concertiste.length: ", { numsession: req.session.id, label: filename});
|
||
logger.info("results_ca_concertiste.length: " + results_ca_concertiste.length, { numsession: req.session.id, label: filename});
|
||
if (results_ca_concertiste.length > 0) {
|
||
for (let i = 0; i<results_ca_concertiste.length; i++) {
|
||
if (results_ca_concertiste[i].concertiste_intrument == "Piano") { compteur_piano++ };
|
||
if (results_ca_concertiste[i].concertiste_intrument == "Harpe") { compteur_harpe++ };
|
||
if (results_ca_concertiste[i].concertiste_intrument == "Trompette") { compteur_trompette++ };
|
||
if (results_ca_concertiste[i].concertiste_intrument == "Flute") { compteur_flute++ };
|
||
if (results_ca_concertiste[i].concertiste_intrument == "Alto") { compteur_alto++ };
|
||
if (results_ca_concertiste[i].concertiste_intrument == "Violon" && (results_ca_concertiste[i].concertiste_cahier == "Cahier 1" || results_ca_concertiste[i].concertiste_cahier == "Cahier 2" || results_ca_concertiste[i].concertiste_cahier == "Cahier 3" || results_ca_concertiste[i].concertiste_cahier == "Cahier 4" || results_ca_concertiste[i].concertiste_cahier == "Cahier 5")) { compteur_violon1++ };
|
||
if (results_ca_concertiste[i].concertiste_intrument == "Violon" && (results_ca_concertiste[i].concertiste_cahier == "Cahier 6" || results_ca_concertiste[i].concertiste_cahier == "Cahier 7" || results_ca_concertiste[i].concertiste_cahier == "Cahier 8" || results_ca_concertiste[i].concertiste_cahier == "Cahier 9" || results_ca_concertiste[i].concertiste_cahier == "Cahier 10")) { compteur_violon2++ };
|
||
if (results_ca_concertiste[i].concertiste_intrument == "Violoncelle" && (results_ca_concertiste[i].concertiste_cahier == "Cahier 1" || results_ca_concertiste[i].concertiste_cahier == "Cahier 2" || results_ca_concertiste[i].concertiste_cahier == "Cahier 3" || results_ca_concertiste[i].concertiste_cahier == "Cahier 4")) { compteur_violoncelle1++ };
|
||
if (results_ca_concertiste[i].concertiste_intrument == "Violoncelle" && (results_ca_concertiste[i].concertiste_cahier == "Cahier 5" || results_ca_concertiste[i].concertiste_cahier == "Cahier 6" || results_ca_concertiste[i].concertiste_cahier == "Cahier 7" || results_ca_concertiste[i].concertiste_cahier == "Cahier 8" || results_ca_concertiste[i].concertiste_cahier == "Cahier 9" || results_ca_concertiste[i].concertiste_cahier == "Cahier 10")) { compteur_violoncelle2++ };
|
||
}
|
||
}
|
||
|
||
sql += 'piano_commandes = piano_commandes + ' + compteur_piano + ', ';
|
||
sql += 'alto_commandes = alto_commandes + ' + compteur_alto + ', ';
|
||
sql += 'flute_commandes = flute_commandes + ' + compteur_flute + ', ';
|
||
sql += 'trompette_commandes = trompette_commandes + ' + compteur_trompette + ', ';
|
||
sql += 'harpe_commandes = harpe_commandes + ' + compteur_harpe + ', ';
|
||
sql += 'violon1_commandes = violon1_commandes + ' + compteur_violon1 + ', ';
|
||
sql += 'violon2_commandes = violon2_commandes + ' + compteur_violon2 + ', ';
|
||
sql += 'violoncelle1_commandes = violoncelle1_commandes + ' + compteur_violoncelle1 + ', ';
|
||
sql += 'violoncelle2_commandes = violoncelle2_commandes + ' + compteur_violoncelle2;
|
||
|
||
logger.info("REQUETE SQL UPDATE ca_decompte_place : " + sql , { numsession: req.session.id, label: filename});
|
||
const [results_maj_compteur_places, fields_maj_compteur_places] = await db_connection_promise.execute(sql);
|
||
|
||
|
||
|
||
|
||
// ca_decompte_place
|
||
sql = 'SELECT spectateur_commandes, concertiste_commandes, encadr_prof_commandes, encadr_parent_commandes, piano_commandes, alto_commandes, flute_commandes, trompette_commandes, harpe_commandes, violon1_commandes, violon2_commandes, violoncelle1_commandes, violoncelle2_commandes FROM ca_decompte_place';
|
||
const [results_ca_decompte_place, fields_ca_decompte_place] = await db_connection_promise.execute(sql);
|
||
logger.info("Row decompte RECUP INFOS : " + results_ca_decompte_place.length, { numsession: req.session.id, label: filename});
|
||
|
||
//////////////////////////
|
||
// ENVOI MAIL À L'ADHÉRENT
|
||
|
||
|
||
mail_objet = 'AFPS - CONCERT UNESCO 2024 - INSCRIPTION - ' + identifiant;
|
||
|
||
|
||
mail_html = "<p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'>Cher(e) adhérent(e),<br></p><p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'>Félicitations ! Vous êtes inscrit au Concert national Unesco 2024 organisé par l'Association Française pour la Pédagogie Suzuki.";
|
||
mail_html += "<br>Vous allez recevoir vos billets par mail de la part de la plateforme de Billetterie WEEZEVENT.</p>";
|
||
|
||
|
||
logger.info("DON : " + results_nb_commande[0].don, { numsession: req.session.id, label: filename});
|
||
console.log("ligne 195");
|
||
if (results_nb_commande[0].don > 0) {
|
||
mail_html += "<p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'>Nous vous remercions chaleureusement pour votre don. Votre justificatif est joint à ce mail.</p>";
|
||
}
|
||
|
||
mail_html += "<br>";
|
||
mail_html += '<p style="color:#E73058;font-size: 13pt; font-family: Arial; text-decoration: none;"><strong>RÉCAPITULATIF</strong></p>';
|
||
mail_html += "<p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'><strong>Identifiant Suzuki : </strong>" + identifiant;
|
||
if (results_ca_adherent[0].hebergement_places > 0) {mail_html += "</p><br><p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'><strong>HÉBERGEMENT PROPOSÉ : </strong><br> Ville : " + results_ca_adherent[0].hebergement_ville + ", nombre de personnes : " + results_ca_adherent[0].hebergement_places + "</p><br>";}
|
||
if (results_ca_concertiste.length > 0) {
|
||
mail_html += "<p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'><strong><span style='color: #E73058'>" + results_ca_concertiste.length + "</span> CONCERTISTE(S) :</strong></p>";
|
||
mail_html += "<p style='font-size: 9pt; font-family: Arial; font-style: italic; color: #454343; text-decoration: none;'>Les concertistes s'engagent à être présents les 2 journées du 20 et 21 avril 2024.</p>";
|
||
for (let i = 0; i<results_ca_concertiste.length; i++) {
|
||
mail_html += "<p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'>Nom : " + results_ca_concertiste[i].concertiste_nom + " " + results_ca_concertiste[i].concertiste_prenom + " - " + results_ca_concertiste[i].concertiste_age + " ans,<br>";
|
||
mail_html += "instrument : " + results_ca_concertiste[i].concertiste_intrument + " - " + results_ca_concertiste[i].concertiste_cahier + " - " + results_ca_concertiste[i].concertiste_piece + "<br>";
|
||
if (results_ca_concertiste[i].concertiste_intrument == "Piano") {mail_html += "<span style='font-size: 9pt; font-family: Arial; font-style: italic; color: #E73058; text-decoration: none;'>Pour le piano l’inscription doit être validée par le comité.</span><br>"};
|
||
mail_html += "professeur : " + results_ca_concertiste[i].concertiste_prof_nom + " - " + results_ca_concertiste[i].concertiste_prof_mail + "</p>";
|
||
}
|
||
}
|
||
if (results_ca_encadrant.length > 0) {
|
||
mail_html += "<br><p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'><strong><span style='color: #E73058'>" + results_ca_encadrant.length + "</span> ENCADRANT(S) :</strong></p>";
|
||
mail_html += "<p style='font-size: 9pt; font-family: Arial; font-style: italic; color: #454343; text-decoration: none;'>Les encadrants s'engagent à être présents les 2 journées du 20 et 21 avril 2024.</p>";
|
||
for (let i = 0; i<results_ca_encadrant.length; i++) {
|
||
mail_html += "<p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'>Nom : " + results_ca_encadrant[i].encadrant_nom + " " + results_ca_encadrant[i].encadrant_prenom + " - " + results_ca_encadrant[i].encadrant_type + "<br>";
|
||
mail_html += "coordonnées : " + results_ca_encadrant[i].encadrant_telephone_mobile + " - " + results_ca_encadrant[i].encadrant_mail + "<br>";
|
||
if (results_ca_encadrant[i].encadrant__prof_instrument) {mail_html += "instrument : " + results_ca_encadrant[i].encadrant__prof_instrument};
|
||
mail_html += "</p>";
|
||
}
|
||
}
|
||
if (results_nb_commande[0].commande_nb_spectateur > 0) {
|
||
mail_html += "<br><p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'><strong><span style='color: #E73058'>" + results_nb_commande[0].commande_nb_spectateur + "</span> SPECTATEUR(S) RÉSERVÉ(S) :</strong><br>"
|
||
mail_html += "<span style='font-size: 9pt; font-family: Arial; font-style: italic; color: #454343; text-decoration: none;'>Les billets spectateurs sont valables pour la date du 21 avril 2024.</span></p>";
|
||
}
|
||
|
||
mail_html += "<br><p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'><strong>TARIFS :</strong><br> ";
|
||
if (results_ca_concertiste.length > 0) {mail_html += "- concertiste(s) : " + results_nb_commande[0].commande_cout_concertiste + "€<br>"};
|
||
if (results_nb_commande[0].commande_nb_spectateur > 0) {mail_html += "- spectateur(s) : " + results_nb_commande[0].commande_cout_spectateur + "€"};
|
||
if (results_nb_commande[0].don > 0) {mail_html += "<br><br>- Votre don : " + results_nb_commande[0].don + "€"};
|
||
mail_html += "<br><br>Montant total : " + results_nb_commande[0].montant_total + "€";
|
||
|
||
mail_html += '<br><br><p style="font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;">Association Française pour la pédagogie Suzuki<br>Marie Rossot - Pôle organisation du Concert national Unesco 2024</p><p><img src="cid:logo" alt="Logo" width="200" height="73"/></p>';
|
||
|
||
// ajout du logo
|
||
let dossier_image = path.resolve(__dirname, "../../public/images/logos/")+"/logosuzukigris.png";
|
||
|
||
if (results_nb_commande[0].don > 0) {
|
||
|
||
//////////////////////////////////////////////////////////////////////////////
|
||
// ADOBE PDF : JUSTIFICATIF DE DON
|
||
|
||
// VARIABLES POUR LE PDF
|
||
let OUTPUT;
|
||
let INPUT;
|
||
let now_pdf = moment().format("YYYY-MM-DDTHH-mm-ss");
|
||
var adresse = results_adherent[0].adherent_adresse_rue;
|
||
adresse += " ";
|
||
adresse += results_adherent[0].adherent_adresse_codepostal;
|
||
adresse += " ";
|
||
adresse += results_adherent[0].adherent_adresse_ville;
|
||
OUTPUT = path.resolve(__dirname, "../../pdf/concert_annuel/don/2425/")+"/"+identifiant+"_don_"+now_pdf+".pdf";
|
||
INPUT = path.resolve(__dirname, "../../pdf/template/don_justificatif.docx");
|
||
JSON_INPUT =
|
||
{
|
||
"date": today,
|
||
"identifiant": identifiant,
|
||
"mode_paiement": "carte bancaire",
|
||
"adherent_nom": results_ca_adherent[0].adherent_nom,
|
||
"adherent_prenom": results_ca_adherent[0].adherent_prenom,
|
||
"adresse": adresse,
|
||
"adherent_paiement_montant_don": results_nb_commande[0].don
|
||
};
|
||
|
||
await pdf.pdfgo(OUTPUT, INPUT, JSON_INPUT);
|
||
|
||
// VARIABLE POUR LE MAIL
|
||
var attachment_name = identifiant + "_don_" + now_pdf + ".pdf";
|
||
var mailOptions = {
|
||
from: 'afps.inscription@gmail.com',
|
||
to: customer_email,
|
||
subject: mail_objet,
|
||
html: mail_html,
|
||
attachments: [
|
||
{
|
||
filename: 'logosuzukigris.png',
|
||
path: dossier_image,
|
||
cid: 'logo'
|
||
},
|
||
{
|
||
filename: attachment_name,
|
||
path: OUTPUT
|
||
},
|
||
]
|
||
};
|
||
|
||
|
||
} else {
|
||
|
||
// SANS JUSTIFICATIF DE DON
|
||
// VARIABLE POUR LE MAIL
|
||
var mailOptions = {
|
||
from: 'afps.inscription@gmail.com',
|
||
to: customer_email,
|
||
subject: mail_objet,
|
||
html: mail_html,
|
||
attachments: [{
|
||
filename: 'logosuzukigris.png',
|
||
path: dossier_image,
|
||
cid: 'logo'
|
||
}]
|
||
};
|
||
}
|
||
|
||
envoie_mail.envoie_mail(mailOptions);
|
||
|
||
/////////////////////////////////////
|
||
// ENVOI MAIL AU PROF SI PAIEMENT REUSSI & SI CONCERTISTES
|
||
if(results_ca_concertiste.length > 0) {
|
||
logger.info("ENVOIE MAILS AU PROF", { numsession: req.session.id, label: filename});
|
||
for (let i = 0; i<results_ca_concertiste.length; i++) {
|
||
|
||
var mail_objet = 'AFPS - CONCERT UNESCO 2024 - VOTRE ÉLÈVE CONCERTISTE - ' + results_ca_concertiste[i].concertiste_nom + ' ' + results_ca_concertiste[i].concertiste_prenom;
|
||
var mail_html = "<p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'>Cher(e) professeur(e),<br></p><p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'>Votre élève est inscrit au Concert national Unesco 2024 de l’Association Française pour la Pédagogie Suzuki qui aura lieu en avril 2024 à l'UNESCO de Paris.</p>";
|
||
mail_html += '<p style="font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;">Identifiant adhérent de l\'élève : ' + identifiant + '<br>';
|
||
mail_html += 'Nom de l\'élève : ' + results_ca_concertiste[i].concertiste_nom + ' ' + results_ca_concertiste[i].concertiste_prenom + '<br>';
|
||
mail_html += 'Âge de l\'élève : ' + results_ca_concertiste[i].concertiste_age + '<br>';
|
||
mail_html += 'Instrument de l\'élève : ' + results_ca_concertiste[i].concertiste_intrument + '<br>';
|
||
mail_html += 'Niveau (cahier) : ' + results_ca_concertiste[i].concertiste_cahier + '<br>';
|
||
mail_html += 'Pièce : ' + results_ca_concertiste[i].concertiste_piece + '</p>';
|
||
mail_html += "<br><p style='font-size: 11pt; font-family: Arial; color: #454343; text-decoration: none;'>Association Française pour la Pédagogie Suzuki<br>Marie Rossot - Pôle organisation du Concert national Unesco 2024</p><img src='cid:logo' alt='Logo' width='200' height='73'/>";
|
||
|
||
var mailOptions = {
|
||
from: 'afps.inscription@gmail.com',
|
||
to: results_ca_concertiste[i].concertiste_prof_mail,
|
||
subject: mail_objet,
|
||
html: mail_html,
|
||
attachments: [{
|
||
filename: 'logosuzukigris.png',
|
||
path: dossier_image,
|
||
cid: 'logo'
|
||
}]
|
||
};
|
||
|
||
|
||
envoie_mail.envoie_mail(mailOptions);
|
||
}
|
||
}
|
||
|
||
/////////////////////////////////////
|
||
// ENVOI BILLETS VERS WEEZEVENT
|
||
/////////////////////////////////////
|
||
|
||
if((results_ca_concertiste.length > 0 || results_ca_encadrant.length > 0 || results_nb_commande[0].commande_nb_spectateur > 0)) {
|
||
|
||
logger.info("ENVOIE BILLET WEEZEVENT", { numsession: req.session.id, label: filename});
|
||
|
||
// WEEZEVENT test compte de Julie
|
||
// EVENT
|
||
// id: 1012995, name: 'ADUZUKI UNESCO Concertistes Encadrants',
|
||
// id: 1013004, name: 'ADUZUKI UNESCO Spectateur',
|
||
// id: 1042983, name: 'ADUZUKI UNESCO Encadrants',
|
||
// id billet: '3742964', name: 'Spectateur',
|
||
// id billet : '3863469', name: 'Concertiste'
|
||
// id billet: '3863496', name: 'Encadrant professeur',
|
||
// id billet: '3863495', name: 'Encadrant parent',
|
||
|
||
// WEEZEVENT Compte AFPS
|
||
// EVENT
|
||
// id: 1043198 ADUZUKI UNESCO Encadrants
|
||
// id: 1043199 ADUZUKI UNESCO Élèves musiciens
|
||
// id: 1043050 ADUZUKI UNESCO Spectateur
|
||
// id billet: '3874933', name: 'Spectateur',
|
||
// id billet : '3875316', name: 'Élève musicien'
|
||
// id billet: '3875315', name: 'Encadrant professeur',
|
||
// id billet: '3875313', name: 'Encadrant parent',
|
||
|
||
let weez_id_evenement_concertiste = process.env.weezevent_id_event_concertiste;
|
||
let weez_id_evenement_encadrant = process.env.weezevent_id_event_encacrant;
|
||
let weez_id_evenement_spectateur = process.env.weezevent_id_event_spect;
|
||
|
||
let weez_id_billet_concertiste = process.env.weez_id_billet_concertiste;
|
||
let weez_id_billet_spectateur = process.env.weez_id_billet_spectateur;
|
||
let weez_id_billet_encadrant_parent = process.env.weez_id_billet_encadrant_parent;
|
||
let weez_id_billet_encadrant_prof = process.env.weez_id_billet_encadrant_prof;
|
||
|
||
//let weez_id_evenement_concertiste = "1043199";
|
||
//let weez_id_evenement_encadrant = "1043198";
|
||
//let weez_id_evenement_spectateur = "1043050";
|
||
//
|
||
//
|
||
//let weez_id_billet_concertiste = "3875316";
|
||
//let weez_id_billet_spectateur = "3874933";
|
||
//let weez_id_billet_encadrant_parent = "3875313";
|
||
//let weez_id_billet_encadrant_prof = "3875315";
|
||
|
||
let weez_id_billet;
|
||
let weez_email = customer_email;
|
||
//let weez_nom = results_ca_adherent[0].adherent_nom;
|
||
let participants = {
|
||
"participants": [],
|
||
"return_ticket_url": true
|
||
};
|
||
|
||
|
||
let weez_prenom;
|
||
let TICKETING_API_BASE_URL = "https://api.weezevent.com/v3/participants?";
|
||
let url = TICKETING_API_BASE_URL;
|
||
body = '&api_key=' + process.env.weezevent_API_KEY;
|
||
body += '&access_token=' + process.env.weezevent_TOKEN;
|
||
|
||
|
||
async function envoi_weez_api(url,body) {
|
||
logger.info("url = " + url , { numsession: req.session.id, label: filename});
|
||
logger.info("body = " + body , { numsession: req.session.id, label: filename});
|
||
let headers = {'content-type':'application/x-www-form-urlencoded;charset=utf-8'};
|
||
let premise_post_token = await fetch(url, {
|
||
method: 'POST',
|
||
headers: headers,
|
||
body: body
|
||
});
|
||
let response_post_token = await premise_post_token.json();
|
||
console.log("response_post_token = ", response_post_token);
|
||
logger.info("response_post_token = " + response_post_token , { numsession: req.session.id, label: filename});
|
||
}
|
||
|
||
// LISTING DES PARTICIPANTS
|
||
|
||
// BILLETS SPECTATEUR
|
||
if (results_nb_commande[0].commande_nb_spectateur > 0) {
|
||
logger.info("results_nb_commande[0].commande_nb_spectateur = " + results_nb_commande[0].commande_nb_spectateur , { numsession: req.session.id, label: filename});
|
||
for (let i = 0; i<results_nb_commande[0].commande_nb_spectateur; i++) {
|
||
weez_prenom = "Spectateur "+(i+1);
|
||
participants.participants.push({ "id_evenement": weez_id_evenement_spectateur, "id_billet": weez_id_billet_spectateur, "email": weez_email, "nom": identifiant, "prenom": weez_prenom, "form": {"adresse": identifiant},"notify": true });
|
||
}
|
||
}
|
||
// BILLETS ENCADRANT
|
||
if (results_ca_encadrant.length > 0) {
|
||
logger.info("results_ca_encadrant.length = " + results_ca_encadrant.length , { numsession: req.session.id, label: filename});
|
||
for (let i = 0; i<results_ca_encadrant.length; i++) {
|
||
if (results_ca_encadrant[i].encadrant_type === 'Parent') {weez_id_billet = weez_id_billet_encadrant_parent}
|
||
if (results_ca_encadrant[i].encadrant_type === 'Professeur') {weez_id_billet = weez_id_billet_encadrant_prof}
|
||
participants.participants.push({ "id_evenement": weez_id_evenement_encadrant, "id_billet": weez_id_billet, "email": weez_email, "nom": results_ca_encadrant[i].encadrant_nom, "prenom": results_ca_encadrant[i].encadrant_prenom, "form": {"adresse": identifiant}, "notify": true });
|
||
}
|
||
}
|
||
// BILLETS CONCERTISTE
|
||
if (results_ca_concertiste.length > 0) {
|
||
logger.info("results_ca_concertiste.length = " + results_ca_concertiste.length , { numsession: req.session.id, label: filename});
|
||
for (let i = 0; i<results_ca_concertiste.length; i++) {
|
||
participants.participants.push({ "id_evenement": weez_id_evenement_concertiste, "id_billet": weez_id_billet_concertiste, "email": weez_email, "nom": results_ca_concertiste[i].concertiste_nom, "prenom": results_ca_concertiste[i].concertiste_prenom, "form": {"adresse": identifiant}, "notify": true });
|
||
}
|
||
}
|
||
|
||
// ENVOIE DE TOUS LES BILLETS PAR 1 REQUÈTE API
|
||
body += '&data=' + JSON.stringify(participants);
|
||
envoi_weez_api(url,body);
|
||
}
|
||
|
||
/////////////////////////////////////
|
||
// COPIE VERS GOOGLE SHEET
|
||
/////////////////////////////////////
|
||
|
||
try {
|
||
await GS_concert.useServiceAccountAuth(GS_creds);
|
||
await GS_concert.loadInfo();
|
||
const GS_concert_adherent = GS_concert.sheetsByTitle['adherents'];
|
||
const GS_concert_hebergements = GS_concert.sheetsByTitle['hebergements'];
|
||
const GS_concert_spectateurs = GS_concert.sheetsByTitle['spectateurs'];
|
||
const GS_concert_concertistes = GS_concert.sheetsByTitle['concertistes'];
|
||
const GS_concert_encadrants = GS_concert.sheetsByTitle['encadrants'];
|
||
const GS_concert_nombre_places = GS_concert.sheetsByTitle['nombre_places'];
|
||
const GS_concert_finance = GS_concert.sheetsByTitle['finance'];
|
||
const GS_concert_inscription_en_attente = GS_concert.sheetsByTitle['inscription_en_attente'];
|
||
|
||
montant_total_concert = results_nb_commande[0].commande_cout_concertiste + results_nb_commande[0].commande_cout_spectateur;
|
||
|
||
|
||
|
||
const ajout_gs_adherent = await GS_concert_adherent.addRows (
|
||
[
|
||
{
|
||
saison: results_ca_adherent[0].saison,
|
||
identifiant: identifiant,
|
||
association_suzuki: results_ca_adherent[0].association_suzuki,
|
||
date_inscription: moment(results_ca_adherent[0].date_inscription).format("YYYY-MM-DD HH:mm:ss"),
|
||
date_inscription_maj: moment(results_ca_adherent[0].date_maj).format("YYYY-MM-DD HH:mm:ss"),
|
||
paiement: "paye",
|
||
adherent_nom: results_ca_adherent[0].adherent_nom,
|
||
adherent_prenom: results_ca_adherent[0].adherent_prenom,
|
||
adherent_telephone_mobile: "'" + results_ca_adherent[0].adherent_telephone_mobile,
|
||
adherent_mail: results_ca_adherent[0].adherent_mail,
|
||
nb_spectateur: results_nb_commande[0].commande_nb_spectateur,
|
||
nb_concertiste: results_nb_commande[0].commande_nb_concertiste,
|
||
nb_encadr_prof: results_nb_commande[0].commande_nb_encadr_prof,
|
||
nb_encadr_par: results_nb_commande[0].commande_nb_encadr_par,
|
||
commentaire: results_ca_adherent[0].comment,
|
||
date_maj_gsheet: now
|
||
}
|
||
]
|
||
);
|
||
|
||
if (results_ca_adherent[0].hebergement_places > 0){
|
||
const ajout_gs_hebergements = await GS_concert_hebergements.addRows (
|
||
[
|
||
{
|
||
saison: results_ca_adherent[0].saison,
|
||
identifiant: identifiant,
|
||
hebergement_ville: results_ca_adherent[0].hebergement_ville,
|
||
hebergement_places: results_ca_adherent[0].hebergement_places,
|
||
date_maj_gsheet: now
|
||
}
|
||
]
|
||
);
|
||
}
|
||
|
||
if (results_nb_commande[0].commande_nb_spectateur > 0){
|
||
const ajout_gs_spectateurs = await GS_concert_spectateurs.addRows (
|
||
[
|
||
{
|
||
saison: results_ca_adherent[0].saison,
|
||
identifiant: identifiant,
|
||
commande_nb_spectateur: results_nb_commande[0].commande_nb_spectateur,
|
||
date_maj_gsheet: now
|
||
}
|
||
]
|
||
);
|
||
}
|
||
|
||
if (results_ca_concertiste.length > 0) {
|
||
let ajout_gs_concertistes = [];
|
||
for (let i = 0; i < results_ca_concertiste.length; i++) {
|
||
ajout_gs_concertistes.push({
|
||
saison: results_ca_concertiste[i].saison,
|
||
identifiant: identifiant,
|
||
concertiste_nom: results_ca_concertiste[i].concertiste_nom,
|
||
concertiste_prenom: results_ca_concertiste[i].concertiste_prenom,
|
||
adherent_telephone_mobile: "'" + results_ca_adherent[0].adherent_telephone_mobile,
|
||
adherent_mail: results_ca_adherent[0].adherent_mail,
|
||
concertiste_age: results_ca_concertiste[i].concertiste_age,
|
||
concertiste_intrument: results_ca_concertiste[i].concertiste_intrument,
|
||
concertiste_cahier: results_ca_concertiste[i].concertiste_cahier,
|
||
concertiste_piece: results_ca_concertiste[i].concertiste_piece,
|
||
concertiste_prof_nom: results_ca_concertiste[i].concertiste_prof_nom,
|
||
concertiste_prof_mail: results_ca_concertiste[i].concertiste_prof_mail,
|
||
date_maj_gsheet: now
|
||
});
|
||
}
|
||
await GS_concert_concertistes.addRows (ajout_gs_concertistes);
|
||
}
|
||
|
||
if (results_ca_encadrant.length > 0) {
|
||
let ajout_gs_encadrants = [];
|
||
for (let i = 0; i<results_ca_encadrant.length; i++) {
|
||
ajout_gs_encadrants.push({
|
||
saison: results_ca_encadrant[i].saison,
|
||
identifiant: identifiant,
|
||
encadrant_type: results_ca_encadrant[i].encadrant_type,
|
||
encadrant_nom: results_ca_encadrant[i].encadrant_nom,
|
||
encadrant_prenom: results_ca_encadrant[i].encadrant_prenom,
|
||
encadrant_telephone_mobile: "'" + results_ca_encadrant[i].encadrant_telephone_mobile,
|
||
encadrant_mail: results_ca_encadrant[i].encadrant_mail,
|
||
encadrant_prof_instrument: results_ca_encadrant[i].encadrant__prof_instrument,
|
||
date_maj_gsheet: now
|
||
})
|
||
}
|
||
await GS_concert_encadrants.addRows (ajout_gs_encadrants);
|
||
}
|
||
|
||
// ONGLET nombre_places
|
||
await GS_concert_nombre_places.loadCells('A1:O2');
|
||
|
||
GS_concert_nombre_places.getCellByA1('B2').value = results_ca_decompte_place[0].spectateur_commandes;
|
||
GS_concert_nombre_places.getCellByA1('C2').value = results_ca_decompte_place[0].encadr_prof_commandes;
|
||
GS_concert_nombre_places.getCellByA1('D2').value = results_ca_decompte_place[0].encadr_parent_commandes;
|
||
GS_concert_nombre_places.getCellByA1('E2').value = results_ca_decompte_place[0].concertiste_commandes;
|
||
GS_concert_nombre_places.getCellByA1('F2').value = results_ca_decompte_place[0].piano_commandes;
|
||
GS_concert_nombre_places.getCellByA1('G2').value = results_ca_decompte_place[0].alto_commandes;
|
||
GS_concert_nombre_places.getCellByA1('H2').value = results_ca_decompte_place[0].flute_commandes;
|
||
GS_concert_nombre_places.getCellByA1('I2').value = results_ca_decompte_place[0].trompette_commandes;
|
||
GS_concert_nombre_places.getCellByA1('J2').value = results_ca_decompte_place[0].harpe_commandes;
|
||
GS_concert_nombre_places.getCellByA1('K2').value = results_ca_decompte_place[0].violon1_commandes;
|
||
GS_concert_nombre_places.getCellByA1('L2').value = results_ca_decompte_place[0].violon2_commandes;
|
||
GS_concert_nombre_places.getCellByA1('M2').value = results_ca_decompte_place[0].violoncelle1_commandes;
|
||
GS_concert_nombre_places.getCellByA1('N2').value = results_ca_decompte_place[0].violoncelle2_commandes;
|
||
GS_concert_nombre_places.getCellByA1('O2').value = now;
|
||
|
||
await GS_concert_nombre_places.saveUpdatedCells();
|
||
|
||
const ajout_gs_finance = await GS_concert_finance.addRows (
|
||
[
|
||
{
|
||
saison: results_ca_adherent[0].saison,
|
||
identifiant: identifiant,
|
||
adherent_nom: results_ca_adherent[0].adherent_nom,
|
||
adherent_prenom: results_ca_adherent[0].adherent_prenom,
|
||
adherent_mail: results_ca_adherent[0].adherent_mail,
|
||
paiement: "paye",
|
||
stripe_retour: results_ca_adherent[0].stripe_retour,
|
||
montant_concertiste: results_nb_commande[0].commande_cout_concertiste,
|
||
montant_spectateur: results_nb_commande[0].commande_cout_spectateur,
|
||
montant_total_concert: montant_total_concert,
|
||
montant_don: results_nb_commande[0].don,
|
||
montant_total: results_nb_commande[0].montant_total,
|
||
date_maj_gsheet: now
|
||
}
|
||
]
|
||
);
|
||
|
||
|
||
|
||
|
||
|
||
} catch (error) {
|
||
// Echec de connection à la GOOGLE SHEET
|
||
logger.error("Il y a un problème avec la connection à Google sheet : " + error, { numsession: req.session.id });
|
||
req.session.err_mail_form_adhesion_1 = '"Il y a un problème système, "' + error;
|
||
};
|
||
|
||
|
||
|
||
/////////////////////////////////////
|
||
// REMISE À ZÉRO DE L'AJOUT
|
||
if (req.session.deja_inscrit_concert == "oui") {
|
||
//Query
|
||
sql = 'UPDATE ';
|
||
sql += 'ca_ajout';
|
||
sql += ' SET ';
|
||
sql += 'identifiant = concat(identifiant, ID)';
|
||
sql += ' WHERE identifiant = "' + identifiant + '"';
|
||
|
||
const [results_ca_ajout, fields_ca_ajout] = await db_connection_promise.execute(sql);
|
||
logger.info("Row ca_adherent UPDATE STRIPE: " + results_ca_ajout.changedRows, { numsession: req.session.id, label: filename});
|
||
|
||
//ca_concertiste
|
||
|
||
sql = 'UPDATE ';
|
||
sql += 'ca_concertiste';
|
||
sql += ' SET ';
|
||
sql += 'ajout = null';
|
||
sql += ' WHERE identifiant = "' + identifiant + '" AND ajout IS NOT NULL';
|
||
|
||
const [results_ca_concertiste, fields_ca_concertiste] = await db_connection_promise.execute(sql);
|
||
|
||
|
||
//ca_encadrant
|
||
|
||
sql = 'UPDATE ';
|
||
sql += 'ca_encadrant';
|
||
sql += ' SET ';
|
||
sql += 'ajout = null';
|
||
sql += ' WHERE identifiant = "' + identifiant + '" AND ajout IS NOT NULL';
|
||
const [results_ca_encadrant, fields_ca_encadrant] = await db_connection_promise.execute(sql);
|
||
|
||
// AJOUT DU NOMBRE DE PLACES SPECTATEUR ET CONCERTISTES COMMANDEES DANS LE COMPTEUR DE L'ADHERENT pour qu'ils ne puissent pas dépasser le quota
|
||
|
||
//Query
|
||
sql = 'UPDATE ';
|
||
sql += 'ca_adherent';
|
||
sql += ' SET ';
|
||
sql += 'commande_nb_spectateur = commande_nb_spectateur + ' + results_nb_commande[0].commande_nb_spectateur + ', ';
|
||
sql += 'commande_nb_concertiste = commande_nb_concertiste + ' + results_nb_commande[0].commande_nb_concertiste;
|
||
sql += ' WHERE identifiant = "' + identifiant + '"';
|
||
|
||
const [results, fields] = await db_connection_promise.execute(sql);
|
||
logger.info("Row ca_adherent UPDATE STRIPE: " + results.changedRows, { numsession: req.session.id, label: filename});
|
||
|
||
|
||
|
||
}
|
||
|
||
/////////////////////////////////////
|
||
// AFFICHAGE DE LA PAGE
|
||
res.locals.identifiant = identifiant;
|
||
res.locals.today = today;
|
||
res.locals.amount_total = amount_total;
|
||
|
||
|
||
res.render('concert/page_templates/paiement_reussi');
|
||
|
||
|
||
|
||
} catch (error) {
|
||
// Echec de connection à la BD
|
||
logger.error("Il y a un problème système : " + error, { numsession: req.session.id });
|
||
req.session.err_mail_form_adhesion_1 = '"Il y a un problème système, "' + error;
|
||
//await db_connection_promise.end();
|
||
affiche_page_admin();
|
||
}
|
||
|
||
|
||
|
||
|
||
|
||
}
|
||
|
||
|
||
/////////////////////////////////////////////////
|
||
// EXPORT DES MODULES POUR QU'ILS PUISSENT LUS PAR LES AUTRES FICHIERS JS
|
||
/////////////////////////////////////////////////
|
||
|
||
module.exports = {
|
||
payer
|
||
} |