127 lines
2.7 KiB
JavaScript
127 lines
2.7 KiB
JavaScript
const express = require('express');
|
|
const Database = require('better-sqlite3');
|
|
const { decrypt } = require('../utils/crypto');
|
|
const auth = require('../middleware/authMiddleware');
|
|
|
|
const router = express.Router();
|
|
const db = new Database('plusfit.db');
|
|
|
|
router.get('/export', auth, (req, res) => {
|
|
|
|
const users = db.prepare(`
|
|
SELECT
|
|
u.*,
|
|
v.betrag,
|
|
v.name AS vertragsname
|
|
FROM users u
|
|
JOIN vertragsarten v
|
|
ON u.vertragsvariante = v.id
|
|
WHERE
|
|
u.status = 'aktiv' -- 🔐 WICHTIGSTER FILTER
|
|
AND u.gesperrt = 0 -- optional, zusätzlich
|
|
AND v.aktiv = 1
|
|
AND u.iban IS NOT NULL
|
|
AND u.mandatsreferenz IS NOT NULL
|
|
`).all();
|
|
|
|
if (users.length === 0) {
|
|
return res.send('Keine aktiven SEPA-Lastschriften vorhanden');
|
|
}
|
|
|
|
const now = new Date();
|
|
const msgId = `PLUSFIT-${now.getTime()}`;
|
|
const date = now.toISOString().slice(0, 10);
|
|
|
|
const totalSum = users.reduce(
|
|
(sum, u) => sum + Number(u.betrag),
|
|
0
|
|
);
|
|
|
|
let xml = `<?xml version="1.0" encoding="UTF-8"?>
|
|
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02">
|
|
<CstmrDrctDbtInitn>
|
|
|
|
<GrpHdr>
|
|
<MsgId>${msgId}</MsgId>
|
|
<CreDtTm>${now.toISOString()}</CreDtTm>
|
|
<NbOfTxs>${users.length}</NbOfTxs>
|
|
<CtrlSum>${totalSum.toFixed(2)}</CtrlSum>
|
|
<InitgPty>
|
|
<Nm>Plusfit</Nm>
|
|
</InitgPty>
|
|
</GrpHdr>
|
|
|
|
<PmtInf>
|
|
<PmtInfId>PMT-${date}</PmtInfId>
|
|
<PmtMtd>DD</PmtMtd>
|
|
<NbOfTxs>${users.length}</NbOfTxs>
|
|
<CtrlSum>${totalSum.toFixed(2)}</CtrlSum>
|
|
|
|
<PmtTpInf>
|
|
<SvcLvl><Cd>SEPA</Cd></SvcLvl>
|
|
<LclInstrm><Cd>CORE</Cd></LclInstrm>
|
|
<SeqTp>RCUR</SeqTp>
|
|
</PmtTpInf>
|
|
|
|
<ReqdColltnDt>${date}</ReqdColltnDt>
|
|
|
|
<Cdtr>
|
|
<Nm>Plusfit</Nm>
|
|
</Cdtr>
|
|
|
|
<CdtrAcct>
|
|
<Id><IBAN>DE12345678901234567890</IBAN></Id>
|
|
</CdtrAcct>
|
|
|
|
<CdtrAgt>
|
|
<FinInstnId><BIC>GENODEF1XXX</BIC></FinInstnId>
|
|
</CdtrAgt>
|
|
|
|
<ChrgBr>SLEV</ChrgBr>
|
|
`;
|
|
|
|
users.forEach(u => {
|
|
const iban = decrypt(u.iban);
|
|
|
|
xml += `
|
|
<DrctDbtTxInf>
|
|
<PmtId>
|
|
<EndToEndId>${u.vertragsnummer}</EndToEndId>
|
|
</PmtId>
|
|
|
|
<InstdAmt Ccy="EUR">${Number(u.betrag).toFixed(2)}</InstdAmt>
|
|
|
|
<DrctDbtTx>
|
|
<MndtRltdInf>
|
|
<MndtId>${u.mandatsreferenz}</MndtId>
|
|
<DtOfSgntr>${date}</DtOfSgntr>
|
|
</MndtRltdInf>
|
|
</DrctDbtTx>
|
|
|
|
<Dbtr>
|
|
<Nm>${u.kontoinhaber || `${u.vorname} ${u.nachname}`}</Nm>
|
|
</Dbtr>
|
|
|
|
<DbtrAcct>
|
|
<Id><IBAN>${iban}</IBAN></Id>
|
|
</DbtrAcct>
|
|
|
|
<RmtInf>
|
|
<Ustrd>Mitgliedsbeitrag ${u.vertragsname}</Ustrd>
|
|
</RmtInf>
|
|
</DrctDbtTxInf>
|
|
`;
|
|
});
|
|
|
|
xml += `
|
|
</PmtInf>
|
|
</CstmrDrctDbtInitn>
|
|
</Document>`;
|
|
|
|
res.setHeader('Content-Type', 'application/xml');
|
|
res.setHeader('Content-Disposition', 'attachment; filename=plusfit_sepa.xml');
|
|
res.send(xml);
|
|
});
|
|
|
|
module.exports = router;
|