/** @OnlyCurrentDoc */ // Remesa Fácil para Google Sheets · https://remesa.facilo.es // Pega este archivo entero en Extensiones > Apps Script, guarda y vuelve a abrir tu hoja. // Solo puede leer y escribir la hoja donde lo instalas. Tus datos no salen de ella. /** Remesa Fácil: all Spanish text. Row and file codes follow spec section 6. */ var Strings = { codes: { E01: 'Falta el titular de la cuenta.', E02: 'Falta el IBAN.', E03: 'Este IBAN no es válido. Revisa si falta o sobra algún dígito.', E04: 'Remesa Fácil aún no admite cuentas de {country}. Pon No en Cobrar y gestiona este recibo con tu banco.', E05: 'Revisa el importe: debe ser mayor que 0 y con dos decimales como máximo.', E06: 'Falta la referencia de mandato.', E07: "La referencia solo admite letras sin acento, números y / - ? : ( ) . , ' + hasta 35 caracteres.", E08: 'Este mandato aparece con dos cuentas distintas.', E09: 'Revisa la fecha de firma.', E10: 'Tipo debe ser Recurrente, Único, Último o Primero.', E11: 'Este mandato se cobró antes en otra cuenta. Si el socio cambió de cuenta, pide una orden nueva con otra referencia.', E12: 'Este mandato era para un solo cobro y ya se usó.', E13: 'Ya enviaste el último cobro de este mandato.', E14: 'Han pasado más de 36 meses desde el último cobro. El mandato ya no vale: pide una orden nueva.', E15: 'Esta celda tiene un error de fórmula.', W01: 'Este cobro aparece dos veces.', W02: 'Este mandato tiene dos cobros en esta remesa. ¿Es correcto?', W03: 'Este importe es muy distinto del habitual.', W04: 'El IBAN cuadra, pero sus dígitos de control españoles no. Confírmalo con el socio.', W05: 'Hemos acortado este texto para que quepa.', W06: 'Firmado hace más de 36 meses y sin cobros en el historial. Si llevas 36 meses sin cobrarle, pide una orden nueva.', W07: 'La fecha de cobro por fila es de Pro. Usamos la fecha del panel.', D00: 'Elige una fecha de cobro.', D01: 'Esa fecha ya ha pasado. El primer día posible es el {suggest}.', D02: 'Los bancos no cobran ese día. El siguiente día posible es el {suggest}.', D03: 'No da tiempo: el banco necesita al menos 3 días hábiles. El primer día posible es el {suggest}.', D04: 'Algunos bancos piden más margen. Comprueba la hora límite del tuyo.', D05: 'Tu banco podría no aceptar un fichero con tanta antelación.', X01: 'Completa los datos de tu entidad.', X03: 'No hay recibos que cobrar.', X05: 'Ya creaste una remesa igual el {date}. Si la subes dos veces, cobrarás dos veces.', X06: 'Un socio tiene el mismo IBAN que tu entidad.', X07: '{n} filas ocultas también se cobrarán.', N01: 'Escribe el nombre de la entidad.', N02: 'Escribe el NIF de la entidad.', N03: 'Este NIF no es válido. Revisa la letra y los números.', N04: 'El sufijo son 3 números, por ejemplo 000.', N05: 'Escribe el IBAN donde cobras.', N06: 'Este IBAN no es válido. Revisa si falta o sobra algún dígito.', N07: 'Este identificador no es válido. Cópialo de nuevo de tu banco.', N08: 'Este identificador es de otro NIF.' }, fields: { socio: 'Socio', holder: 'Titular de la cuenta', iban: 'IBAN', amount: 'Importe', mandate: 'Referencia de mandato', signed: 'Fecha de firma', concept: 'Concepto', type: 'Tipo', charge: 'Cobrar', due: 'Fecha de cobro', status: 'Estado' }, status: { ok: 'Correcto', warning: 'Aviso: ', error: 'Error: ', skipped: '' }, menu: { open: 'Abrir Remesa Fácil', template: 'Crear mi hoja de socios', help: 'Ayuda' }, ui: { title: 'Remesa Fácil', firstTitle: 'Cobra las cuotas sin errores en el banco', firstText: 'Prepara tu hoja en un minuto. Tus datos se quedan en esta hoja: nosotros no los vemos.', firstPrimary: 'Crear mi hoja de socios', firstLink: 'Ya tengo mi hoja', firstBox: 'Antes de empezar: tu banco debe tener activado el servicio de adeudos directos SEPA (domiciliaciones) para tu entidad, y cada socio debe haber firmado su orden de domiciliación.', steps: ['Tu entidad', 'Revisar', 'Crear fichero'], entityTitle: 'Tu entidad', entityName: 'Nombre de la entidad', entityNif: 'NIF', entityIban: 'IBAN donde cobras', entitySuffix: 'Sufijo', entityTrade: 'Nombre comercial (opcional)', suffixHelp: 'Usa el que te dio tu banco. Si no te dio ninguno, deja 000.', tradeHelp: 'Solo si tus socios te conocen por otro nombre. Lo verán en su recibo.', creditorLine: 'Tu identificador de acreedor: {id}', creditorHelp: 'Lo calculamos con tu NIF y tu sufijo. Si tu banco te dio otro, escríbelo aquí.', creditorField: 'Identificador de tu banco (opcional)', save: 'Guardar', edit: 'Cambiar', sheetTitle: 'Tu hoja de socios', sheetPick: 'Hoja', headerRow: 'Mi cabecera está en la fila', mapAsk: 'No encontramos estas columnas. Dinos cuál es cada una:', mapConflict: 'Hay varias columnas que podrían ser «{campo}». Elige una:', mapNone: 'Elige una columna', mapSaved: 'Columnas: {n} reconocidas.', reviewTitle: 'Revisar', dueField: 'Fecha de cobro', dueHelp: 'El día en que el dinero sale de las cuentas de tus socios.', dueLine: 'Envía el fichero a tu banco, como muy tarde, el {fecha}.', useDate: 'Usar el {fecha}', reviewPrimary: 'Revisar la hoja', running: 'Revisando {n} filas…', result: '{ok} recibos listos · {e} errores · {w} avisos', resultOne: { ok: '1 recibo listo', e: '1 error', w: '1 aviso' }, allGood: 'Todo correcto. Ya puedes crear el fichero.', rowCol: 'Fila {n} · {columna}', fileLevel: 'Toda la remesa', skipped: '{n} filas con No o Ejemplo en Cobrar no se cobran.', skippedOne: '1 fila con No o Ejemplo en Cobrar no se cobra.', more: 'Y {n} más. Corrige estos y vuelve a revisar.', buildTitle: 'Crear fichero', summary: 'Vas a cobrar {total} a {n} socios el {fecha}.', summaryMulti: 'Vas a cobrar {total} a {n} socios en {d} fechas.', changes: 'Tu banco no admite algunos caracteres. Así quedarán estos nombres:', tick: 'He revisado los importes. Los socios han firmado su orden y están avisados del cobro.', buildPrimary: 'Crear fichero para el banco', doneTitle: 'Fichero listo', doneText: 'Comprobación final superada: {n} recibos, {total}.', doneNext: 'Ahora súbelo en la web de tu banco, en el apartado de adeudos o remesas, antes del {fecha}.', firstTip: '¿Es tu primera remesa con este banco? Prueba antes con uno o dos recibos.', redownload: 'Volver a descargar', history: 'Lo hemos anotado en la hoja Historial. Si al final no envías el fichero, borra esas filas.', advanced: 'Formato antiguo (pain.008.001.02). Úsalo solo si tu banco te lo pide.', selfCheckFail: 'No hemos podido verificar el fichero. No lo envíes y escríbenos.', selfCheckCode: 'Código para soporte: {code}', limitText: 'La versión gratuita crea ficheros de hasta 25 recibos. Esta remesa tiene {n}.', limitPrimary: 'Ver Pro: 19 € al año', limitLink: 'Ya tengo una licencia', licenceTitle: 'Licencia', licenceField: 'Clave de licencia', licencePrimary: 'Activar', licenceDone: 'Licencia activa hasta el {fecha}.', licenceWrong: 'Esa clave no es válida. Cópiala de nuevo del correo de compra.', licenceUsed: 'Esa clave ya se usa con otra entidad. Escríbenos a support@facilo.es.', licenceOffline: 'No hemos podido comprobar tu licencia. Revisa tu conexión y vuelve a intentarlo.', licenceLapsed: 'Tu licencia caducó el {fecha}. Sigues teniendo todas las comprobaciones y ficheros de hasta 25 recibos.', licenceNeedsEntity: 'Guarda antes los datos de tu entidad.', planFree: 'Versión gratuita', planPro: 'Pro', planAgency: 'Asesorías', tools: 'Más', printBlank: 'Imprimir orden en blanco', printFilled: 'Imprimir órdenes rellenas', printFilledHelp: 'Selecciona en la hoja las filas de los socios y pulsa el botón.', retry: 'Volver a cobrar devueltos', retryHelp: 'Marca Devuelto en la hoja Historial y pulsa el botón.', retryWarn: 'Si el socio pidió la devolución a su banco, habla con él antes de volver a cobrarle.', retryDone: 'Hemos creado la hoja {hoja} con {n} recibos. Revísala y crea su fichero con su propia fecha.', retryNone: 'No hay recibos marcados como devueltos en Historial.', retryRefused: '{n} recibos eran Único o Último y no se pueden volver a cobrar.', proOnly: 'Esto es de Pro.', help: 'Ayuda', helpUrl: 'https://remesa.facilo.es/soporte/', proUrl: 'https://remesa.facilo.es/#precios', back: 'Volver', templateDone: 'Hemos creado la hoja {hoja}. Cambia la fila de ejemplo por tus socios.', templateExample: ['Lucía (benjamín)', 'María Ejemplo Pérez', 'ES79 2100 0813 6101 2345 6789', 30, 'EJEMPLO-0001', '', 'Cuota', 'Recurrente', 'Ejemplo', '', ''], error: 'Algo ha fallado. Vuelve a intentarlo y, si se repite, escríbenos a support@facilo.es.', rules: 'Reglas: EPC 2025 v1.2 y guía AEB oct. 2025' }, mandate: { title: 'Orden de domiciliación de adeudo directo SEPA', titleEn: 'SEPA Direct Debit Mandate', textEs: 'Mediante la firma de esta orden de domiciliación, el deudor autoriza (A) al acreedor a enviar instrucciones a la entidad del deudor para adeudar su cuenta y (B) a la entidad para efectuar los adeudos en su cuenta siguiendo las instrucciones del acreedor. Como parte de sus derechos, el deudor está legitimado al reembolso por su entidad en los términos y condiciones del contrato suscrito con la misma. La solicitud de reembolso deberá efectuarse dentro de las ocho semanas que siguen a la fecha de adeudo en cuenta. Puede obtener información adicional sobre sus derechos en su entidad financiera.', textEn: 'By signing this mandate form, you authorise (A) the Creditor to send instructions to your bank to debit your account and (B) your bank to debit your account in accordance with the instructions from the Creditor. As part of your rights, you are entitled to a refund from your bank under the terms and conditions of your agreement with your bank. A refund must be claimed within eight weeks starting from the date on which your account was debited. Your rights are explained in a statement that you can obtain from your bank.', ref: 'Referencia de la orden de domiciliación / Mandate reference', creditorId: 'Identificador del acreedor / Creditor Identifier', creditorName: 'Nombre del acreedor / Creditor’s name', address: 'Dirección / Address', postcode: 'Código postal - Población - Provincia / Postal Code - City - Town', country: 'País / Country', debtorName: 'Nombre del deudor / Debtor’s name', debtorAddress: 'Dirección del deudor / Address of the debtor', bic: 'BIC (opcional para cuentas del Espacio Económico Europeo)', iban: 'Número de cuenta - IBAN / Account number - IBAN', payType: 'Tipo de pago / Type of payment', recurrent: 'Pago recurrente / Recurrent payment', oneOff: 'Pago único / One-off payment', placeDate: 'Fecha - Localidad / Date - location in which you are signing', signature: 'Firma del deudor / Signature of the debtor', creditorBlock: 'A cumplimentar por el acreedor / To be completed by the creditor', debtorBlock: 'A cumplimentar por el deudor / To be completed by the debtor', keep: 'Todos los campos han de ser cumplimentados obligatoriamente. Una vez firmada esta orden de domiciliación debe ser enviada al acreedor para su custodia. / ALL GAPS ARE MANDATORY. ONCE THIS MANDATE HAS BEEN SIGNED MUST BE SENT TO CREDITOR FOR STORAGE.', print: 'Imprimir o guardar en PDF', spain: 'España' } }; /** Fills {key} placeholders. */ function fmt(s, args) { return String(s).replace(/\{([^}]+)\}/g, function (m, k) { return args && args[k] != null ? args[k] : m; }); } if (typeof module !== 'undefined' && module.exports) module.exports = { Strings: Strings, fmt: fmt }; /** * Remesa Fácil core: pure logic, no Google calls. * Runs in Apps Script (V8) and in Node tests. All money is integer cents. * Rules: EPC SDD Core Rulebook 2025 v1.2, EPC130-08 2025, AEB guide Oct 2025. */ var Core = (function () { 'use strict'; var RULES_VERSION = 'Reglas: EPC 2025 v1.2 y guía AEB oct. 2025'; var FREE_LIMIT = 25; // ---------- IBAN ---------- // EEA countries and IBAN lengths (SWIFT IBAN Registry). v1 accepts only these. var EEA = { AT: 20, BE: 16, BG: 22, CY: 28, CZ: 24, DE: 22, DK: 18, EE: 20, ES: 24, FI: 18, FR: 27, GR: 27, HR: 21, HU: 28, IE: 22, IS: 26, IT: 27, LI: 21, LT: 20, LU: 20, LV: 21, MT: 31, NL: 18, NO: 15, PL: 28, PT: 25, RO: 24, SE: 24, SI: 19, SK: 24 }; // SEPA scheme countries outside the EEA (EPC409-09). Refused with E04. var NON_EEA_SEPA = { AD: 'Andorra', CH: 'Suiza', GB: 'Reino Unido', GI: 'Gibraltar', GG: 'Guernsey', IM: 'Isla de Man', JE: 'Jersey', MC: 'Mónaco', SM: 'San Marino', VA: 'Ciudad del Vaticano', AL: 'Albania', MD: 'Moldavia', ME: 'Montenegro', MK: 'Macedonia del Norte', RS: 'Serbia' }; function compactIban(s) { return String(s == null ? '' : s).replace(/[\s\- ]/g, '').toUpperCase(); } function mod97(str) { var r = 0; for (var i = 0; i < str.length; i++) { var c = str.charCodeAt(i); var v; if (c >= 48 && c <= 57) v = c - 48; else if (c >= 65 && c <= 90) v = c - 55; else return -1; r = v > 9 ? (r * 100 + v) % 97 : (r * 10 + v) % 97; } return r; } function spanishInnerOk(iban) { var bank = iban.slice(4, 12), dc = iban.slice(12, 14), acct = iban.slice(14, 24); if (!/^\d{20}$/.test(iban.slice(4))) return false; var w = [1, 2, 4, 8, 5, 10, 9, 7, 3, 6]; function digit(n) { var s = 0; for (var i = 0; i < 10; i++) s += Number(n.charAt(i)) * w[i]; var v = 11 - (s % 11); return v === 11 ? 0 : v === 10 ? 1 : v; } return String(digit('00' + bank)) + String(digit(acct)) === dc; } /** Returns {iban, error: null|'E02'|'E03'|'E04', country, warning: null|'W04'}. */ function checkIban(raw) { var iban = compactIban(raw); var out = { iban: iban, error: null, warning: null, country: iban.slice(0, 2) }; if (!iban) { out.error = 'E02'; return out; } var cc = out.country; if (!/^[A-Z]{2}\d{2}[A-Z0-9]+$/.test(iban)) { out.error = 'E03'; return out; } if (NON_EEA_SEPA[cc]) { out.error = 'E04'; return out; } if (!EEA[cc] || iban.length !== EEA[cc]) { out.error = 'E03'; return out; } if (mod97(iban.slice(4) + iban.slice(0, 4)) !== 1) { out.error = 'E03'; return out; } if (cc === 'ES' && !spanishInnerOk(iban)) out.warning = 'W04'; return out; } // ---------- NIF and creditor identifier ---------- var DNI_LETTERS = 'TRWAGMYFPDXBNJZSQVHLCKE'; function compactNif(s) { return String(s == null ? '' : s).replace(/[\s\-.]/g, '').toUpperCase(); } function checkNif(raw) { var nif = compactNif(raw); var m; if ((m = /^(\d{8})([A-Z])$/.exec(nif))) { return DNI_LETTERS.charAt(Number(m[1]) % 23) === m[2]; } if ((m = /^([XYZ])(\d{7})([A-Z])$/.exec(nif))) { var n = Number('XYZ'.indexOf(m[1]) + m[2]); return DNI_LETTERS.charAt(n % 23) === m[3]; } if ((m = /^([ABCDEFGHJKLMNPQRSUVW])(\d{7})([0-9A-J])$/.exec(nif))) { var d = m[2], sum = 0; for (var i = 0; i < 7; i++) { var x = Number(d.charAt(i)); if (i % 2 === 0) { x *= 2; sum += Math.floor(x / 10) + (x % 10); } else sum += x; } var ctrl = (10 - (sum % 10)) % 10; var letter = 'JABCDEFGHI'.charAt(ctrl); var c = m[3], L = m[1]; if ('KLMNPQRSW'.indexOf(L) >= 0) return c === letter; if ('ABEH'.indexOf(L) >= 0) return c === String(ctrl); return c === String(ctrl) || c === letter; } return false; } function digitsOf(s) { var out = ''; for (var i = 0; i < s.length; i++) out += String(parseInt(s.charAt(i), 36)); return out; } function mod97Big(digits) { var r = 0; for (var i = 0; i < digits.length; i++) r = (r * 10 + Number(digits.charAt(i))) % 97; return r; } /** ES + check + suffix + NIF. Check ignores the suffix (EPC262-08 8.1.22). */ function creditorId(nif, suffix) { nif = compactNif(nif).replace(/[^A-Z0-9]/g, ''); var check = 98 - mod97Big(digitsOf(nif + 'ES') + '00'); return 'ES' + (check < 10 ? '0' : '') + check + suffix + nif; } function creditorIdValid(id) { id = String(id || '').toUpperCase(); var m = /^([A-Z]{2})(\d{2})([A-Z0-9]{3})([A-Z0-9]{1,28})$/.exec(id); if (!m) return false; return mod97Big(digitsOf(m[4] + m[1] + m[2])) === 1; } /** * Entity: {name, nif, iban, suffix, tradeName, creditorId (pasted, optional)}. * Returns {ok, errors: {field: code}, creditorId, iban}. */ function checkEntity(e) { e = e || {}; var errors = {}; var name = String(e.name || '').trim(); if (!name) errors.name = 'N01'; var nif = compactNif(e.nif); if (!nif) errors.nif = 'N02'; else if (!checkNif(nif)) errors.nif = 'N03'; var suffix = String(e.suffix == null || e.suffix === '' ? '000' : e.suffix).trim(); if (!/^\d{3}$/.test(suffix)) errors.suffix = 'N04'; var ib = checkIban(e.iban); if (ib.error) errors.iban = ib.error === 'E02' ? 'N05' : 'N06'; var calc = (!errors.nif && !errors.suffix) ? creditorId(nif, suffix) : ''; var finalId = calc; var pasted = String(e.creditorId || '').replace(/\s/g, '').toUpperCase(); if (pasted && pasted !== calc) { if (!creditorIdValid(pasted) || pasted.slice(0, 2) !== 'ES') errors.creditorId = 'N07'; else if (pasted.slice(7) !== nif) errors.creditorId = 'N08'; else finalId = pasted; } return { ok: Object.keys(errors).length === 0, errors: errors, creditorId: finalId, calculatedId: calc, iban: ib.iban, suffix: suffix, nif: nif, name: name, tradeName: String(e.tradeName || '').trim() }; } // ---------- Amounts ---------- var MAX_CENTS = 99999999999; /** Returns {cents} or {error: 'E05'}. */ function parseAmount(v) { var cents; if (typeof v === 'number') { if (!isFinite(v)) return { error: 'E05' }; cents = Math.round(v * 100); if (Math.abs(v * 100 - cents) > 1e-6) return { error: 'E05' }; } else { var s = String(v == null ? '' : v).replace(/€/g, '').replace(/[\s ]/g, ''); var m; if (/^\d+$/.test(s)) cents = Number(s) * 100; else if ((m = /^(\d+)[,.](\d{1,2})$/.exec(s))) cents = Number(m[1]) * 100 + Number((m[2] + '0').slice(0, 2)); else if ((m = /^(\d{1,3}(?:\.\d{3})+),(\d{1,2})$/.exec(s))) cents = Number(m[1].replace(/\./g, '')) * 100 + Number((m[2] + '0').slice(0, 2)); else return { error: 'E05' }; } if (!(cents > 0) || cents > MAX_CENTS) return { error: 'E05' }; return { cents: cents }; } function centsToXml(c) { var s = String(c); while (s.length < 3) s = '0' + s; return s.slice(0, -2) + '.' + s.slice(-2); } /** Spanish format: 4.537,50 € */ function euros(c) { var x = centsToXml(c).split('.'); var ints = x[0].replace(/\B(?=(\d{3})+(?!\d))/g, '.'); return ints + ',' + x[1] + ' €'; } // ---------- Text ---------- var ALLOWED = /[^A-Za-z0-9\/\-?:().,' +]/g; /** Returns {text, cut, changed}. */ function cleanText(raw, limit) { var src = String(raw == null ? '' : raw); var t = src.replace(/&/g, ' y ').replace(/€/g, ' EUR '); t = t.normalize('NFKD').replace(/[̀-ͯ]/g, ''); t = t.replace(ALLOWED, ' ').replace(/\s+/g, ' ').trim(); var cut = false; if (t.length > limit) { t = t.slice(0, limit).replace(/\s+$/, ''); cut = true; } return { text: t, cut: cut, changed: t !== src.trim() }; } function mandateOk(ref) { ref = String(ref); if (ref.length > 35 || ref.length === 0) return false; if (/[^A-Za-z0-9\/\-?:().,' +]/.test(ref)) return false; if (ref.charAt(0) === '/' || ref.charAt(ref.length - 1) === '/' || ref.indexOf('//') >= 0) return false; if (ref !== ref.trim()) return false; return true; } function norm(s) { return String(s == null ? '' : s).normalize('NFKD').replace(/[̀-ͯ]/g, '') .toLowerCase().replace(/[^a-z0-9]+/g, ' ').trim(); } // ---------- Dates (calendar dates as 'YYYY-MM-DD', no time zones) ---------- function pad(n) { return (n < 10 ? '0' : '') + n; } function ymd(y, m, d) { return y + '-' + pad(m) + '-' + pad(d); } function toUtc(s) { var p = s.split('-'); return Date.UTC(+p[0], +p[1] - 1, +p[2]); } function fromUtc(t) { var d = new Date(t); return ymd(d.getUTCFullYear(), d.getUTCMonth() + 1, d.getUTCDate()); } function addDays(s, n) { return fromUtc(toUtc(s) + n * 86400000); } function weekday(s) { return new Date(toUtc(s)).getUTCDay(); } function validYmd(s) { var m = /^(\d{4})-(\d{2})-(\d{2})$/.exec(s); if (!m) return false; return fromUtc(toUtc(s)) === s; } /** * Accepts a 'YYYY-MM-DD' string, 'D/M/YYYY' text, or {y,m,d} from the sheet layer. * Returns 'YYYY-MM-DD' or null. */ function parseDate(v) { if (v == null || v === '') return null; if (typeof v === 'object' && v.y) { var s0 = ymd(v.y, v.m, v.d); return validYmd(s0) ? s0 : null; } var s = String(v).trim(), m; if ((m = /^(\d{4})-(\d{1,2})-(\d{1,2})$/.exec(s))) s = ymd(+m[1], +m[2], +m[3]); else if ((m = /^(\d{1,2})[\/.\-](\d{1,2})[\/.\-](\d{4})$/.exec(s))) s = ymd(+m[3], +m[2], +m[1]); else return null; return validYmd(s) ? s : null; } function easter(y) { var a = y % 19, b = Math.floor(y / 100), c = y % 100, d = Math.floor(b / 4), e = b % 4; var f = Math.floor((b + 8) / 25), g = Math.floor((b - f + 1) / 3); var h = (19 * a + b - d - g + 15) % 30, i = Math.floor(c / 4), k = c % 4; var l = (32 + 2 * e + 2 * i - h - k) % 7, m = Math.floor((a + 11 * h + 22 * l) / 451); var month = Math.floor((h + l - 7 * m + 114) / 31), day = ((h + l - 7 * m + 114) % 31) + 1; return ymd(y, month, day); } /** TARGET2 closing days: weekends, 1 Jan, Good Friday, Easter Monday, 1 May, 25 and 26 Dec. */ function isTarget(s) { var wd = weekday(s); if (wd === 0 || wd === 6) return false; var md = s.slice(5); if (md === '01-01' || md === '05-01' || md === '12-25' || md === '12-26') return false; var e = easter(+s.slice(0, 4)); if (s === addDays(e, -2) || s === addDays(e, 1)) return false; return true; } /** TARGET days after `from`, up to and including `to`. */ function targetDays(from, to) { var n = 0, d = addDays(from, 1); while (d <= to) { if (isTarget(d)) n++; d = addDays(d, 1); } return n; } function nextTarget(s) { var d = s; while (!isTarget(d)) d = addDays(d, 1); return d; } /** First TARGET day with at least `min` TARGET days after today. */ function firstValid(today, min) { var d = addDays(today, 1); while (!(isTarget(d) && targetDays(today, d) >= min)) d = addDays(d, 1); return d; } function defaultDue(today) { return firstValid(today, 5); } /** Latest TARGET day that still leaves 3 TARGET days before `due`. */ function lastSendDay(due) { var d = addDays(due, -1); while (!(isTarget(d) && targetDays(d, due) >= 3)) d = addDays(d, -1); return d; } /** Returns {error: null|code, warnings: [], suggest}. */ function checkDue(due, today) { var out = { error: null, warnings: [], suggest: null }; if (!due || !validYmd(due)) { out.error = 'D00'; out.suggest = firstValid(today, 3); return out; } if (due <= today) { out.error = 'D01'; out.suggest = firstValid(today, 3); return out; } if (!isTarget(due)) { out.error = 'D02'; var n = nextTarget(due); out.suggest = targetDays(today, n) >= 3 ? n : firstValid(today, 3); return out; } var td = targetDays(today, due); if (td < 3) { out.error = 'D03'; out.suggest = firstValid(today, 3); return out; } if (td < 5) out.warnings.push('D04'); if ((toUtc(due) - toUtc(today)) / 86400000 > 14) out.warnings.push('D05'); return out; } function addMonths(s, n) { var y = +s.slice(0, 4), m = +s.slice(5, 7) - 1 + n, d = +s.slice(8, 10); y += Math.floor(m / 12); m = ((m % 12) + 12) % 12; var last = new Date(Date.UTC(y, m + 1, 0)).getUTCDate(); return ymd(y, m + 1, Math.min(d, last)); } var MONTHS = ['enero', 'febrero', 'marzo', 'abril', 'mayo', 'junio', 'julio', 'agosto', 'septiembre', 'octubre', 'noviembre', 'diciembre']; var DAYS = ['domingo', 'lunes', 'martes', 'miércoles', 'jueves', 'viernes', 'sábado']; /** 'martes 10 de noviembre de 2026'; withYear false drops the year. */ function dateWords(s, withYear) { return DAYS[weekday(s)] + ' ' + (+s.slice(8, 10)) + ' de ' + MONTHS[+s.slice(5, 7) - 1] + (withYear === false ? '' : ' de ' + s.slice(0, 4)); } function dateShort(s) { return s.slice(8, 10) + '/' + s.slice(5, 7) + '/' + s.slice(0, 4); } // ---------- Columns ---------- var FIELDS = [ { key: 'socio', label: 'Socio', required: false, syn: ['socio', 'alumno', 'vecino', 'miembro'] }, { key: 'holder', label: 'Titular de la cuenta', required: true, syn: ['titular de la cuenta', 'titular', 'nombre', 'deudor', 'pagador'] }, { key: 'iban', label: 'IBAN', required: true, syn: ['iban', 'cuenta', 'numero de cuenta'] }, { key: 'amount', label: 'Importe', required: true, syn: ['importe', 'cuota', 'cantidad', 'euros'] }, { key: 'mandate', label: 'Referencia de mandato', required: true, syn: ['referencia de mandato', 'mandato', 'referencia', 'ref'] }, { key: 'signed', label: 'Fecha de firma', required: true, syn: ['fecha de firma', 'firma', 'f firma', 'fecha mandato'] }, { key: 'concept', label: 'Concepto', required: false, syn: ['concepto', 'descripcion'] }, { key: 'type', label: 'Tipo', required: false, syn: ['tipo', 'secuencia'] }, { key: 'charge', label: 'Cobrar', required: false, syn: ['cobrar', 'activo'] }, { key: 'due', label: 'Fecha de cobro', required: false, syn: ['fecha de cobro'] }, { key: 'status', label: 'Estado', required: false, syn: ['estado'] } ]; var TEMPLATE_HEADERS = FIELDS.map(function (f) { return f.label; }); /** * Matches header cells to fields. Returns {map: {key: colIndex}, missing: [keys], conflicts: {key: [cols]}}. * `saved` (optional) overrides with the user's own choices. */ function mapHeaders(headers, saved) { var map = {}, conflicts = {}, i, j; var normed = headers.map(norm); FIELDS.forEach(function (f) { var hits = []; for (i = 0; i < normed.length; i++) { if (!normed[i]) continue; for (j = 0; j < f.syn.length; j++) if (normed[i] === f.syn[j]) { hits.push(i); break; } } if (hits.length === 1) map[f.key] = hits[0]; else if (hits.length > 1) conflicts[f.key] = hits; }); if (saved) Object.keys(saved).forEach(function (k) { if (saved[k] != null && saved[k] >= 0 && saved[k] < headers.length) { map[k] = saved[k]; delete conflicts[k]; } }); var missing = FIELDS.filter(function (f) { return f.required && map[f.key] == null; }) .map(function (f) { return f.key; }); return { map: map, missing: missing, conflicts: conflicts }; } // ---------- Rows ---------- var SEQ = { '': 'RCUR', recurrente: 'RCUR', primero: 'FRST', ultimo: 'FNAL', unico: 'OOFF' }; var SEQ_ORDER = { FRST: 0, RCUR: 1, OOFF: 2, FNAL: 3 }; var SEQ_WORDS = { RCUR: 'Recurrente', FRST: 'Primero', FNAL: 'Último', OOFF: 'Único' }; var FORMULA_ERR = /^#(REF!|N\/A|VALUE!|DIV\/0!|NAME\?|NUM!|NULL!|ERROR!|SPILL!|CALC!)$/; function isFormulaError(v) { return typeof v === 'string' && FORMULA_ERR.test(v.trim()); } function blank(v) { return v == null || String(v).trim() === ''; } function median(list) { if (!list.length) return 0; var a = list.slice().sort(function (x, y) { return x - y; }); var h = Math.floor(a.length / 2); return a.length % 2 ? a[h] : (a[h - 1] + a[h]) / 2; } /** * Checks the members sheet. * opts: {rows: [[cells]], firstRow: sheet row number of rows[0], map, entity (checked), due, * today, pro, history: [{created, due, msgId, mandate, holder, iban, cents, seq}], hidden: [bool]} * Returns {items: [{row, field, code, level, args}], txs: [...], skipped, hiddenCount, file: [{code, level, args}]}. */ function checkSheet(opts) { var map = opts.map, rows = opts.rows, today = opts.today, due = opts.due; var items = [], txs = [], file = [], skipped = 0, hiddenCount = 0; var entity = opts.entity; var hist = indexHistory(opts.history || []); var dueCheck = checkDue(due, today); if (!entity || !entity.ok) file.push({ code: 'X01', level: 'error' }); if (dueCheck.error) file.push({ code: dueCheck.error, level: 'error', args: { suggest: dueCheck.suggest } }); dueCheck.warnings.forEach(function (w) { file.push({ code: w, level: 'warning' }); }); function cell(r, key) { return map[key] == null ? '' : r[map[key]]; } function add(rowNo, key, code, level, args) { items.push({ row: rowNo, field: key, col: map[key] == null ? null : map[key], code: code, level: level, args: args || null }); } var proDateWarned = false; for (var i = 0; i < rows.length; i++) { var r = rows[i], rowNo = opts.firstRow + i; var used = Object.keys(map).some(function (k) { return k !== 'status' && !blank(r[map[k]]); }); if (!used) continue; var charge = norm(cell(r, 'charge')); if (charge === 'no' || charge === 'ejemplo') { skipped++; continue; } if (opts.hidden && opts.hidden[i]) hiddenCount++; var before = items.length; var fe = false; ['holder', 'iban', 'amount', 'mandate', 'signed', 'concept', 'type', 'due', 'socio'].forEach(function (k) { if (isFormulaError(cell(r, k))) { add(rowNo, k, 'E15', 'error'); fe = true; } }); var tx = { row: rowNo }; // Holder var holderRaw = cell(r, 'holder'); if (!isFormulaError(holderRaw)) { if (blank(holderRaw)) add(rowNo, 'holder', 'E01', 'error'); else { var h = cleanText(holderRaw, 70); if (!h.text) add(rowNo, 'holder', 'E01', 'error'); tx.holder = h.text; tx.holderRaw = String(holderRaw).trim(); if (h.cut) add(rowNo, 'holder', 'W05', 'warning'); } } // IBAN var ibRaw = cell(r, 'iban'); if (!isFormulaError(ibRaw)) { var ib = checkIban(ibRaw); tx.iban = ib.iban; if (ib.error) add(rowNo, 'iban', ib.error, 'error', ib.error === 'E04' ? { country: NON_EEA_SEPA[ib.country] } : null); else if (ib.warning) add(rowNo, 'iban', ib.warning, 'warning'); } // Amount var amRaw = cell(r, 'amount'); if (!isFormulaError(amRaw)) { var am = blank(amRaw) ? { error: 'E05' } : parseAmount(amRaw); if (am.error) add(rowNo, 'amount', 'E05', 'error'); else tx.cents = am.cents; } // Mandate var mRaw = cell(r, 'mandate'); if (!isFormulaError(mRaw)) { var mref = blank(mRaw) ? '' : String(mRaw); if (typeof mRaw === 'number') mref = String(mRaw); if (!mref.trim()) add(rowNo, 'mandate', 'E06', 'error'); else if (!mandateOk(mref)) add(rowNo, 'mandate', 'E07', 'error'); else tx.mandate = mref; } // Row due date (Pro) tx.due = due; var dRaw = cell(r, 'due'); if (!blank(dRaw) && !isFormulaError(dRaw)) { if (!opts.pro) { if (!proDateWarned) { add(rowNo, 'due', 'W07', 'warning'); proDateWarned = true; } } else { var rd = parseDate(dRaw); var rc = checkDue(rd, today); if (rc.error) add(rowNo, 'due', rc.error, 'error', { suggest: rc.suggest }); else { tx.due = rd; rc.warnings.forEach(function (w) { add(rowNo, 'due', w, 'warning'); }); } } } // Signature date var sRaw = cell(r, 'signed'); if (!isFormulaError(sRaw)) { var sd = parseDate(sRaw); if (!sd || sd > today || (tx.due && sd > tx.due)) add(rowNo, 'signed', 'E09', 'error'); else tx.signed = sd; } // Type var tRaw = cell(r, 'type'); if (!isFormulaError(tRaw)) { var t = norm(tRaw); if (SEQ[t] == null) add(rowNo, 'type', 'E10', 'error'); else tx.seq = SEQ[t]; } // Concept var cRaw = cell(r, 'concept'); if (!isFormulaError(cRaw)) { var base; if (blank(cRaw)) { var dd = tx.due && validYmd(tx.due) ? tx.due : today; base = 'Cuota ' + MONTHS[+dd.slice(5, 7) - 1] + ' ' + dd.slice(0, 4); var soc = cell(r, 'socio'); if (!blank(soc) && !isFormulaError(soc)) base += ' - ' + String(soc).trim(); } else base = String(cRaw).trim(); if (entity && entity.tradeName) base = entity.tradeName + ' - ' + base; var c = cleanText(base, 140); tx.concept = c.text; if (c.cut) add(rowNo, 'concept', 'W05', 'warning'); } var rowHasError = fe || items.slice(before).some(function (x) { return x.level === 'error'; }); tx.ok = !rowHasError; txs.push(tx); } // Cross-row rules var byMandate = {}; txs.forEach(function (tx) { if (!tx.mandate) return; var k = tx.mandate.toUpperCase(); (byMandate[k] = byMandate[k] || []).push(tx); }); Object.keys(byMandate).forEach(function (k) { var list = byMandate[k]; var ibans = {}; list.forEach(function (tx) { if (tx.iban) ibans[tx.iban] = 1; }); if (Object.keys(ibans).length > 1) list.forEach(function (tx) { add(tx.row, 'mandate', 'E08', 'error'); tx.ok = false; }); else for (var a = 0; a < list.length; a++) for (var b = a + 1; b < list.length; b++) { if (list[a].due !== list[b].due) continue; var code = list[a].cents === list[b].cents ? 'W01' : 'W02'; add(list[b].row, 'mandate', code, 'warning', { other: list[a].row }); } // History var h = hist.byMandate[k]; if (!h) { list.forEach(function (tx) { if (tx.signed && addMonths(tx.signed, 36) < (tx.due || due)) add(tx.row, 'signed', 'W06', 'warning'); }); return; } list.forEach(function (tx) { var d = tx.due || due; if (h.seqs.OOFF) { add(tx.row, 'mandate', 'E12', 'error'); tx.ok = false; } if (h.seqs.FNAL) { add(tx.row, 'mandate', 'E13', 'error'); tx.ok = false; } if (tx.iban && h.ibans.some(function (x) { return x !== tx.iban; }) && h.lastIban !== tx.iban) { add(tx.row, 'mandate', 'E11', 'error'); tx.ok = false; } if (h.lastDue && addMonths(h.lastDue, 36) < d) { add(tx.row, 'mandate', 'E14', 'error'); tx.ok = false; } if (tx.cents && h.lastCents && tx.cents > 3 * h.lastCents) add(tx.row, 'amount', 'W03', 'warning'); }); }); // Median rule var cents = txs.filter(function (t) { return t.cents; }).map(function (t) { return t.cents; }); var med = median(cents); txs.forEach(function (tx) { if (tx.cents && med && tx.cents > 3 * med && !items.some(function (x) { return x.row === tx.row && x.code === 'W03'; })) add(tx.row, 'amount', 'W03', 'warning'); if (entity && entity.iban && tx.iban === entity.iban) add(tx.row, 'iban', 'X06', 'warning'); }); var good = txs.filter(function (t) { return t.ok; }); if (!txs.length) file.push({ code: 'X03', level: 'error' }); if (hiddenCount) file.push({ code: 'X07', level: 'warning', args: { n: hiddenCount } }); // Repeated remesa if (good.length) { var key = remesaKey(good); var same = hist.remesas.filter(function (x) { return x.key === key; }); if (same.length) file.push({ code: 'X05', level: 'warning', args: { date: same[same.length - 1].created } }); } var errors = items.filter(function (x) { return x.level === 'error'; }).length + file.filter(function (x) { return x.level === 'error'; }).length; var warnings = items.filter(function (x) { return x.level === 'warning'; }).length + file.filter(function (x) { return x.level === 'warning'; }).length; items.sort(function (a, b) { return (a.level === b.level ? 0 : a.level === 'error' ? -1 : 1) || a.row - b.row || (a.col || 0) - (b.col || 0); }); return { items: items, file: file, txs: txs, ready: good.length, skipped: skipped, hiddenCount: hiddenCount, errors: errors, warnings: warnings, totalCents: sum(good) }; } function sum(txs) { var s = 0; txs.forEach(function (t) { s += t.cents; }); return s; } function remesaKey(txs) { var dates = {}; txs.forEach(function (t) { dates[t.due] = 1; }); return Object.keys(dates).sort().join(',') + '|' + txs.length + '|' + sum(txs); } function indexHistory(rows) { var byMandate = {}, byMsg = {}, order = []; rows.forEach(function (h) { if (!h.mandate) return; var k = String(h.mandate).toUpperCase(); var e = byMandate[k] = byMandate[k] || { ibans: [], seqs: {}, lastDue: null, lastCents: 0, lastIban: null }; if (h.iban && e.ibans.indexOf(h.iban) < 0) e.ibans.push(h.iban); if (h.seq) e.seqs[h.seq] = 1; if (h.due && (!e.lastDue || h.due >= e.lastDue)) { e.lastDue = h.due; e.lastCents = h.cents; e.lastIban = h.iban; } if (h.msgId) { if (!byMsg[h.msgId]) { byMsg[h.msgId] = { created: h.created, list: [] }; order.push(h.msgId); } byMsg[h.msgId].list.push(h); } }); var remesas = order.map(function (id) { return { msgId: id, created: byMsg[id].created, key: remesaKey(byMsg[id].list) }; }); return { byMandate: byMandate, remesas: remesas }; } // ---------- XML ---------- function esc(s) { return String(s).replace(/&/g, '&').replace(//g, '>') .replace(/"/g, '"').replace(/'/g, '''); } /** * opts: {entity (checked), txs: [{holder, iban, cents, mandate, signed, concept, seq, due}], * created: 'YYYY-MM-DDTHH:MM:SS', version: '08'|'02'} * Returns {xml, msgId, count, totalCents, blocks: [{due, seq, count, cents}]}. */ function buildXml(opts) { var e = opts.entity, version = opts.version === '02' ? '02' : '08'; var stamp = opts.created.replace(/[-:T]/g, ''); var msgId = 'REMESA-' + stamp; var groups = {}, keys = []; opts.txs.forEach(function (t) { var k = t.due + '|' + t.seq; if (!groups[k]) { groups[k] = []; keys.push(k); } groups[k].push(t); }); keys.sort(function (a, b) { var pa = a.split('|'), pb = b.split('|'); return pa[0] < pb[0] ? -1 : pa[0] > pb[0] ? 1 : SEQ_ORDER[pa[1]] - SEQ_ORDER[pb[1]]; }); var count = opts.txs.length, total = sum(opts.txs); var name = esc(cleanText(e.name, 70).text); var other = '' + esc(e.creditorId) + ''; var out = [ '', '', '', '', '' + msgId + '', '' + opts.created + '', '' + count + '', '' + centsToXml(total) + '', '' + name + '', '' + other + '', '' ]; var pos = 0, blocks = []; keys.forEach(function (k, bi) { var list = groups[k], p = k.split('|'); var bc = sum(list); blocks.push({ due: p[0], seq: p[1], count: list.length, cents: bc }); out.push( '', '' + msgId + '-' + (bi + 1) + '', 'DD', 'true', '' + list.length + '', '' + centsToXml(bc) + '', 'SEPA', 'CORE', '' + p[1] + '', '' + p[0] + '', '' + name + '', '' + esc(e.iban) + '', 'NOTPROVIDED', 'SLEV', '', '' + esc(e.creditorId) + '', 'SEPA', '' ); list.forEach(function (t) { pos++; var n = String(pos); while (n.length < 4) n = '0' + n; t.endToEndId = stamp + '-' + n; out.push( '', '' + t.endToEndId + '', '' + centsToXml(t.cents) + '', '', '' + esc(t.mandate) + '', '' + t.signed + '', '', 'NOTPROVIDED', '' + esc(t.holder) + '', '' + esc(t.iban) + '', '' + esc(t.concept) + '', '' ); }); out.push(''); }); out.push('', ''); return { xml: out.join('\n') + '\n', msgId: msgId, count: count, totalCents: total, blocks: blocks }; } /** Names whose bank text differs from what's in the sheet, for the summary. */ function changedNames(txs) { var seen = {}, out = []; txs.forEach(function (t) { if (t.holderRaw && t.holder !== t.holderRaw && !seen[t.holderRaw]) { seen[t.holderRaw] = 1; out.push({ from: t.holderRaw, to: t.holder }); } }); return out; } /** History rows to append after a download. */ function historyRows(built, txs, createdLocal) { return txs.map(function (t) { return [createdLocal, t.due, built.msgId, t.mandate, t.holder, t.iban, centsToXml(t.cents).replace('.', ','), SEQ_WORDS[t.seq], t.concept, '']; }); } function parseHistory(rows) { var back = { recurrente: 'RCUR', primero: 'FRST', ultimo: 'FNAL', unico: 'OOFF' }; return rows.filter(function (r) { return r && !blank(r[3]); }).map(function (r) { var am = parseAmount(r[6]); return { created: String(r[0]), due: parseDate(r[1]), msgId: String(r[2]), mandate: String(r[3]), holder: String(r[4]), iban: compactIban(r[5]), cents: am.cents || 0, seq: back[norm(r[7])] || 'RCUR', concept: String(r[8] || ''), returned: !blank(r[9]) && norm(r[9]) !== 'no' && r[9] !== false }; }); } return { RULES_VERSION: RULES_VERSION, FREE_LIMIT: FREE_LIMIT, EEA: EEA, NON_EEA_SEPA: NON_EEA_SEPA, FIELDS: FIELDS, TEMPLATE_HEADERS: TEMPLATE_HEADERS, SEQ_WORDS: SEQ_WORDS, MONTHS: MONTHS, compactIban: compactIban, checkIban: checkIban, spanishInnerOk: spanishInnerOk, checkNif: checkNif, creditorId: creditorId, creditorIdValid: creditorIdValid, checkEntity: checkEntity, parseAmount: parseAmount, centsToXml: centsToXml, euros: euros, cleanText: cleanText, mandateOk: mandateOk, norm: norm, parseDate: parseDate, isTarget: isTarget, targetDays: targetDays, nextTarget: nextTarget, firstValid: firstValid, defaultDue: defaultDue, lastSendDay: lastSendDay, checkDue: checkDue, addMonths: addMonths, addDays: addDays, easter: easter, dateWords: dateWords, dateShort: dateShort, mapHeaders: mapHeaders, checkSheet: checkSheet, buildXml: buildXml, changedNames: changedNames, historyRows: historyRows, parseHistory: parseHistory, esc: esc, isFormulaError: isFormulaError }; })(); if (typeof module !== 'undefined' && module.exports) module.exports = Core; /** * Remesa Fácil rule checker (self-check F8). * Reads a finished pain.008 file with its own parser and its own maths. * Shares no code with core.js on purpose, so a writer bug can't hide here. */ var Checker = (function () { 'use strict'; var NS = { 'urn:iso:std:iso:20022:tech:xsd:pain.008.001.08': '08', 'urn:iso:std:iso:20022:tech:xsd:pain.008.001.02': '02' }; var IBAN_LEN = { AT: 20, BE: 16, BG: 22, CY: 28, CZ: 24, DE: 22, DK: 18, EE: 20, ES: 24, FI: 18, FR: 27, GR: 27, HR: 21, HU: 28, IE: 22, IS: 26, IT: 27, LI: 21, LT: 20, LU: 20, LV: 21, MT: 31, NL: 18, NO: 15, PL: 28, PT: 25, RO: 24, SE: 24, SI: 19, SK: 24 }; var TEXT_OK = /^[A-Za-z0-9\/\-?:().,' +]+$/; // ---- Tiny XML parser: elements, attributes, text. No DTD, no CDATA, no comments. ---- function unescape(s, fail) { return s.replace(/&(#x[0-9a-fA-F]+|#\d+|amp|lt|gt|quot|apos);|&/g, function (m, ent) { if (!ent) { fail('Carácter & sin escapar.'); return m; } if (ent === 'amp') return '&'; if (ent === 'lt') return '<'; if (ent === 'gt') return '>'; if (ent === 'quot') return '"'; if (ent === 'apos') return "'"; return String.fromCharCode(ent.charAt(1) === 'x' ? parseInt(ent.slice(2), 16) : parseInt(ent.slice(1), 10)); }); } function parse(xml, fail) { if (xml.charCodeAt(0) === 0xFEFF) fail('El fichero empieza con BOM.'); var i = 0, root = null, stack = []; var decl = /^<\?xml version="1\.0" encoding="UTF-8"\?>\s*/.exec(xml); if (!decl) fail('Falta la declaración XML UTF-8.'); else i = decl[0].length; var tagRe = /<(\/?)([A-Za-z][A-Za-z0-9]*)((?:\s+[A-Za-z:]+="[^"<]*")*)\s*(\/?)>/g; while (i < xml.length) { var lt = xml.indexOf('<', i); var text = xml.slice(i, lt < 0 ? xml.length : lt); if (stack.length) stack[stack.length - 1].text += text; else if (/\S/.test(text)) fail('Texto fuera del documento.'); if (lt < 0) break; tagRe.lastIndex = lt; var m = tagRe.exec(xml); if (!m || m.index !== lt) { fail('Etiqueta mal formada cerca de la posición ' + lt + '.'); return null; } var close = m[1] === '/', name = m[2], self = m[4] === '/'; if (close) { var top = stack.pop(); if (!top || top.name !== name) { fail('Etiqueta sin abrir.'); return null; } top.text = unescape(top.text, fail); if (!top.children.length && !top.text.trim()) fail('Elemento vacío: ' + name + '.'); } else { var node = { name: name, attrs: {}, children: [], text: '' }; m[3].replace(/([A-Za-z:]+)="([^"]*)"/g, function (_, k, v) { node.attrs[k] = unescape(v, fail); }); if (stack.length) stack[stack.length - 1].children.push(node); else if (root) fail('Más de un elemento raíz.'); else root = node; if (self) fail('Elemento vacío: ' + name + '.'); else stack.push(node); } i = tagRe.lastIndex; } if (stack.length) fail('El fichero termina sin cerrar ' + stack[stack.length - 1].name + '.'); return root; } function kids(n, name) { return n ? n.children.filter(function (c) { return c.name === name; }) : []; } function one(n, path) { var parts = path.split('/'), cur = n; for (var i = 0; i < parts.length && cur; i++) { var k = kids(cur, parts[i]); cur = k.length === 1 ? k[0] : null; } return cur; } function val(n, path) { var x = one(n, path); return x ? x.text : null; } // ---- Own maths ---- function rem97(s) { var r = 0; for (var i = 0; i < s.length; i++) { var ch = s.charAt(i), v = /[0-9]/.test(ch) ? String(+ch) : String(ch.charCodeAt(0) - 55); for (var j = 0; j < v.length; j++) r = (r * 10 + (+v.charAt(j))) % 97; } return r; } function ibanOk(s) { if (!/^[A-Z]{2}\d{2}[A-Z0-9]+$/.test(s || '')) return false; if (IBAN_LEN[s.slice(0, 2)] !== s.length) return false; return rem97(s.slice(4) + s.slice(0, 4)) === 1; } function creditorOk(s) { var m = /^([A-Z]{2})(\d{2})[A-Z0-9]{3}([A-Z0-9]+)$/.exec(s || ''); return !!m && rem97(m[3] + m[1] + m[2]) === 1; } /** '30.00' -> 3000 cents, or NaN when not 1-2 decimals. */ function cents(s) { var m = /^(\d{1,11})(?:\.(\d{1,2}))?$/.exec(s || ''); if (!m) return NaN; return (+m[1]) * 100 + (m[2] ? +(m[2] + '0').slice(0, 2) : 0); } function dateOk(s) { if (!/^\d{4}-\d{2}-\d{2}$/.test(s || '')) return false; var d = new Date(s + 'T00:00:00Z'); return !isNaN(d) && d.toISOString().slice(0, 10) === s; } /** * Returns {ok, problems: [string], count, total (cents), version}. * today: 'YYYY-MM-DD'. Problems are short Spanish lines for the support log. */ function check(xml, today) { var problems = []; function fail(msg) { if (problems.indexOf(msg) < 0) problems.push(msg); } var doc = parse(String(xml), fail); if (!doc) return { ok: false, problems: problems }; var version = NS[doc.attrs.xmlns]; if (doc.name !== 'Document' || !version) fail('Espacio de nombres desconocido.'); var init = one(doc, 'CstmrDrctDbtInitn'); if (!init) { fail('Falta CstmrDrctDbtInitn.'); return { ok: false, problems: problems }; } var gh = one(init, 'GrpHdr'); var msgId = val(gh, 'MsgId') || ''; if (!/^REMESA-\d{14}$/.test(msgId)) fail('MsgId con formato inesperado.'); if (/^FSDD/i.test(msgId)) fail('MsgId no puede empezar por FSDD.'); if (!/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}$/.test(val(gh, 'CreDtTm') || '')) fail('CreDtTm no válido.'); var credId = val(gh, 'InitgPty/Id/OrgId/Othr/Id'); if (!creditorOk(credId)) fail('Identificador de acreedor no válido en la cabecera.'); checkText(val(gh, 'InitgPty/Nm'), 70, 'Nombre del presentador', fail); var blocks = kids(init, 'PmtInf'); if (!blocks.length) fail('No hay bloques de cobro.'); var allCount = 0, allCents = 0, e2e = {}, pmtIds = {}; blocks.forEach(function (b, bi) { var where = 'Bloque ' + (bi + 1) + ': '; var pid = val(b, 'PmtInfId'); if (pid !== msgId + '-' + (bi + 1)) fail(where + 'PmtInfId inesperado.'); if (pmtIds[pid]) fail(where + 'PmtInfId repetido.'); pmtIds[pid] = 1; if (val(b, 'PmtMtd') !== 'DD') fail(where + 'PmtMtd debe ser DD.'); if (val(b, 'BtchBookg') !== 'true') fail(where + 'BtchBookg debe ser true.'); if (val(b, 'PmtTpInf/SvcLvl/Cd') !== 'SEPA') fail(where + 'SvcLvl debe ser SEPA.'); if (val(b, 'PmtTpInf/LclInstrm/Cd') !== 'CORE') fail(where + 'LclInstrm debe ser CORE.'); var seq = val(b, 'PmtTpInf/SeqTp'); if (['FRST', 'RCUR', 'OOFF', 'FNAL'].indexOf(seq) < 0) fail(where + 'Falta SeqTp o no es válido.'); var due = val(b, 'ReqdColltnDt'); if (!dateOk(due)) fail(where + 'Fecha de cobro no válida.'); else { if (today && due <= today) fail(where + 'La fecha de cobro ya pasó.'); var wd = new Date(due + 'T00:00:00Z').getUTCDay(); if (wd === 0 || wd === 6) fail(where + 'La fecha de cobro cae en fin de semana.'); } checkText(val(b, 'Cdtr/Nm'), 70, where + 'Nombre del acreedor', fail); if (!ibanOk(val(b, 'CdtrAcct/Id/IBAN'))) fail(where + 'IBAN del acreedor no válido.'); if (val(b, 'CdtrAgt/FinInstnId/Othr/Id') !== 'NOTPROVIDED') fail(where + 'CdtrAgt inesperado.'); if (val(b, 'ChrgBr') !== 'SLEV') fail(where + 'ChrgBr debe ser SLEV.'); var sid = val(b, 'CdtrSchmeId/Id/PrvtId/Othr/Id'); if (!creditorOk(sid)) fail(where + 'Identificador de acreedor no válido.'); if (sid !== credId) fail(where + 'El identificador de acreedor no coincide con la cabecera.'); if (val(b, 'CdtrSchmeId/Id/PrvtId/Othr/SchmeNm/Prtry') !== 'SEPA') fail(where + 'SchmeNm debe ser SEPA.'); var txs = kids(b, 'DrctDbtTxInf'), bCents = 0; if (!txs.length) fail(where + 'Bloque sin recibos.'); txs.forEach(function (t, ti) { var w = where + 'recibo ' + (ti + 1) + ': '; var id = val(t, 'PmtId/EndToEndId'); if (!id || id.length > 35) fail(w + 'EndToEndId no válido.'); else if (e2e[id]) fail(w + 'EndToEndId repetido.'); e2e[id] = 1; var amt = one(t, 'InstdAmt'); var c = amt ? cents(amt.text) : NaN; if (!amt || amt.attrs.Ccy !== 'EUR') fail(w + 'Importe sin EUR.'); if (!(c >= 1 && c <= 99999999999)) fail(w + 'Importe no válido.'); else bCents += c; var mnd = val(t, 'DrctDbtTx/MndtRltdInf/MndtId'); if (!mnd || mnd.length > 35 || !TEXT_OK.test(mnd) || /^\/|\/$|\/\//.test(mnd)) fail(w + 'Referencia de mandato no válida.'); var sig = val(t, 'DrctDbtTx/MndtRltdInf/DtOfSgntr'); if (!dateOk(sig) || (dateOk(due) && sig > due)) fail(w + 'Fecha de firma no válida.'); if (val(t, 'DbtrAgt/FinInstnId/Othr/Id') !== 'NOTPROVIDED') fail(w + 'DbtrAgt inesperado.'); checkText(val(t, 'Dbtr/Nm'), 70, w + 'Titular', fail); if (!ibanOk(val(t, 'DbtrAcct/Id/IBAN'))) fail(w + 'IBAN no válido.'); checkText(val(t, 'RmtInf/Ustrd'), 140, w + 'Concepto', fail); }); if (String(txs.length) !== val(b, 'NbOfTxs')) fail(where + 'NbOfTxs no cuadra.'); if (cents(val(b, 'CtrlSum')) !== bCents) fail(where + 'CtrlSum no cuadra.'); allCount += txs.length; allCents += bCents; }); if (String(allCount) !== val(gh, 'NbOfTxs')) fail('Cabecera: NbOfTxs no cuadra.'); if (cents(val(gh, 'CtrlSum')) !== allCents) fail('Cabecera: CtrlSum no cuadra.'); return { ok: problems.length === 0, problems: problems, count: allCount, total: allCents, version: version }; } function checkText(s, max, label, fail) { if (s == null) { fail(label + ': falta.'); return; } if (s.length > max) fail(label + ': demasiado largo.'); if (!TEXT_OK.test(s)) fail(label + ': caracteres no admitidos.'); } return { check: check, parse: parse }; })(); if (typeof module !== 'undefined' && module.exports) module.exports = Checker; /** * Remesa Fácil flow: build, then self-check (F8) before anything leaves the sheet. * Apps Script loads every file into one global scope; Node needs the requires. */ if (typeof require !== 'undefined' && typeof Core === 'undefined') { var Core = require('./core.js'); var Checker = require('./checker.js'); } var Flow = (function () { 'use strict'; /** * Builds the file and reads it back with the independent checker. * Returns {ok: true, built, xml} or {ok: false, problems}. */ function prepare(opts, today) { var built = Core.buildXml(opts); var res = Checker.check(built.xml, today); var problems = res.problems.slice(); if (res.ok && res.count !== built.count) problems.push('El número de recibos no cuadra con la hoja.'); if (res.ok && res.total !== built.totalCents) problems.push('El total no cuadra con la hoja.'); if (problems.length) return { ok: false, problems: problems }; return { ok: true, built: built, xml: built.xml }; } return { prepare: prepare }; })(); if (typeof module !== 'undefined' && module.exports) module.exports = Flow; /** * Remesa Fácil: the thin Google layer. Everything that can be wrong about a file lives in core.js, * checker.js and flow.js, which the Node tests cover. This file only reads and writes the sheet. * Logs never hold row data: codes and row numbers only. */ var SETTINGS = 'Ajustes'; var HISTORY = 'Historial'; var HISTORY_HEADERS = ['Creado', 'Fecha de cobro', 'Remesa', 'Referencia de mandato', 'Titular', 'IBAN', 'Importe', 'Tipo', 'Concepto', 'Devuelto']; var MAX_ITEMS = 60; var GRACE_DAYS = 14; /** Freemius product 44820. Plan 78988 is Asesorías, 78987 is Pro. */ var LICENCE = { productId: '44820', agencyPlanId: '78988', apiBase: 'https://api.freemius.com/v1', title: 'Remesa Fácil' }; // Keys in Ajustes, column A, with the value in column B. var K = { name: 'Nombre de la entidad', nif: 'NIF', iban: 'IBAN donde cobras', suffix: 'Sufijo', tradeName: 'Nombre comercial', creditorId: 'Identificador de tu banco', address: 'Dirección', town: 'Código postal y población', province: 'Provincia', sheet: 'Hoja de socios', headerRow: 'Fila de cabecera', columns: 'Columnas', version: 'Formato', licence: 'Clave de licencia', plan: 'Plan', installId: 'Instalación', expires: 'Licencia hasta', lastCheck: 'Última comprobación', uid: 'Identificador de instalación' }; // ---------- Menu ---------- function onOpen(e) { SpreadsheetApp.getUi().createAddonMenu() .addItem(Strings.menu.open, 'showSidebar') .addItem(Strings.menu.template, 'menuTemplate') .addItem(Strings.menu.help, 'showHelp') .addToUi(); } function onInstall(e) { onOpen(e); } function showSidebar() { var html = HtmlService.createTemplate(HTML_FILES_['Sidebar']).evaluate().setTitle(Strings.ui.title); SpreadsheetApp.getUi().showSidebar(html); } function menuTemplate() { createTemplate(); showSidebar(); } function showHelp() { var t = HtmlService.createHtmlOutput( '

Guía, preguntas y contacto: ' + 'remesa.facilo.es/soporte

') .setWidth(320).setHeight(90); SpreadsheetApp.getUi().showModalDialog(t, Strings.ui.help); } /** Lets the HTML templates pull in shared files. */ function include(name) { return HtmlService.createHtmlOutput(HTML_FILES_[name]).getContent(); } // ---------- Ajustes ---------- function settingsSheet_(create) { var ss = SpreadsheetApp.getActive(); var sh = ss.getSheetByName(SETTINGS); if (!sh && create) { var was = ss.getActiveSheet(); sh = ss.insertSheet(SETTINGS); if (was) was.activate(); sh.getRange(1, 1, 1, 2).setValues([['Ajuste', 'Valor']]).setFontWeight('bold'); sh.setFrozenRows(1); sh.setColumnWidth(1, 220); sh.setColumnWidth(2, 320); } return sh; } function readSettings_() { var sh = settingsSheet_(false), out = {}; if (!sh || sh.getLastRow() < 2) return out; sh.getRange(2, 1, sh.getLastRow() - 1, 2).getDisplayValues().forEach(function (r) { if (r[0]) out[r[0]] = r[1]; }); return out; } /** Writes key/value pairs into Ajustes in one pass. Values are stored as plain text. */ function writeSettings_(pairs) { var sh = settingsSheet_(true); var last = sh.getLastRow(); var rows = last >= 2 ? sh.getRange(2, 1, last - 1, 2).getValues() : []; Object.keys(pairs).forEach(function (k) { var v = safe_(pairs[k]); var found = false; for (var i = 0; i < rows.length; i++) if (rows[i][0] === k) { rows[i][1] = v; found = true; break; } if (!found) rows.push([k, v]); }); if (!rows.length) return; var range = sh.getRange(2, 1, rows.length, 2); range.setNumberFormat('@'); range.setValues(rows); } function entityFromSettings_(s) { return { name: s[K.name] || '', nif: s[K.nif] || '', iban: s[K.iban] || '', suffix: s[K.suffix] || '000', tradeName: s[K.tradeName] || '', creditorId: s[K.creditorId] || '', address: s[K.address] || '', town: s[K.town] || '', province: s[K.province] || '' }; } /** Text that Sheets would read as a formula gets a leading apostrophe, which keeps it as text. */ function safe_(v) { var t = v == null ? '' : String(v); return /^[=+\-@]/.test(t) ? "'" + t : t; } function today_() { return Utilities.formatDate(new Date(), 'Europe/Madrid', 'yyyy-MM-dd'); } function nowLocal_() { return Utilities.formatDate(new Date(), 'Europe/Madrid', "yyyy-MM-dd'T'HH:mm:ss"); } // ---------- State for the sidebar ---------- /** Everything the sidebar needs to draw itself. No row data. */ function getState() { var s = readSettings_(); var ss = SpreadsheetApp.getActive(); var raw = entityFromSettings_(s); var checked = Core.checkEntity(raw); var hasEntity = !!(raw.name || raw.nif || raw.iban); var sheets = ss.getSheets().map(function (x) { return x.getName(); }) .filter(function (n) { return n !== SETTINGS && n !== HISTORY; }); var sheetName = s[K.sheet] && sheets.indexOf(s[K.sheet]) >= 0 ? s[K.sheet] : guessSheet_(sheets); var today = today_(); return { today: today, defaultDue: Core.defaultDue(today), entity: raw, entityCheck: { ok: checked.ok, errors: checked.errors, creditorId: checked.creditorId, calculatedId: checked.calculatedId }, hasEntity: hasEntity, sheets: sheets, sheetName: sheetName, headerRow: +(s[K.headerRow] || 1), columns: sheetName ? columnsFor_(sheetName, +(s[K.headerRow] || 1), s) : null, version: s[K.version] === '02' ? '02' : '08', licence: licenceState_(s, today), rules: Core.RULES_VERSION, freeLimit: Core.FREE_LIMIT }; } function guessSheet_(names) { if (names.indexOf('Socios') >= 0) return 'Socios'; return names[0] || ''; } /** The members sheet: the one saved in Ajustes, or our best guess. */ function sheetName_(s) { return s[K.sheet] || guessSheet_(SpreadsheetApp.getActive().getSheets().map(function (x) { return x.getName(); }) .filter(function (n) { return n !== SETTINGS && n !== HISTORY; })); } /** Header names and the column map for one sheet. */ function columnsFor_(sheetName, headerRow, s) { var sh = SpreadsheetApp.getActive().getSheetByName(sheetName); if (!sh || sh.getLastColumn() < 1 || sh.getLastRow() < headerRow) return { headers: [], map: {}, missing: requiredKeys_(), conflicts: {} }; var headers = sh.getRange(headerRow, 1, 1, sh.getLastColumn()).getDisplayValues()[0]; var saved = null; if (s && s[K.columns] && s[K.sheet] === sheetName) { try { saved = JSON.parse(s[K.columns]); } catch (e) { saved = null; } } var m = Core.mapHeaders(headers, saved); return { headers: headers, map: m.map, missing: m.missing, conflicts: m.conflicts }; } function requiredKeys_() { return Core.FIELDS.filter(function (f) { return f.required; }).map(function (f) { return f.key; }); } // ---------- Saving ---------- function saveEntity(e) { var checked = Core.checkEntity(e); if (!checked.ok) return { ok: false, errors: checked.errors, calculatedId: checked.calculatedId }; var pairs = {}; pairs[K.name] = checked.name; pairs[K.nif] = checked.nif; pairs[K.iban] = checked.iban; pairs[K.suffix] = checked.suffix; pairs[K.tradeName] = checked.tradeName; pairs[K.creditorId] = checked.creditorId !== checked.calculatedId ? checked.creditorId : ''; ['address', 'town', 'province'].forEach(function (k) { pairs[K[k]] = String(e[k] || '').trim(); }); writeSettings_(pairs); return { ok: true, creditorId: checked.creditorId }; } /** Saves the sheet, header row and any column choices the user made. */ function saveSheet(sheetName, headerRow, choices) { var s = readSettings_(); var saved = {}; if (s[K.columns] && s[K.sheet] === sheetName) { try { saved = JSON.parse(s[K.columns]) || {}; } catch (e) { saved = {}; } } Object.keys(choices || {}).forEach(function (k) { var v = choices[k]; if (v === '' || v == null) delete saved[k]; else saved[k] = +v; }); var pairs = {}; pairs[K.sheet] = sheetName; pairs[K.headerRow] = Math.max(1, Math.floor(+headerRow || 1)); pairs[K.columns] = JSON.stringify(saved); writeSettings_(pairs); return getState(); } function saveVersion(v) { var pairs = {}; pairs[K.version] = v === '02' ? '02' : '08'; writeSettings_(pairs); } // ---------- Review ---------- /** Reads the sheet once and runs every rule. */ function load_(due, s) { var ss = SpreadsheetApp.getActive(); var sheetName = sheetName_(s); var sh = ss.getSheetByName(sheetName); if (!sh) throw new Error('NOSHEET'); var headerRow = +(s[K.headerRow] || 1); var cols = columnsFor_(sheetName, headerRow, s); var lastRow = sh.getLastRow(); var n = Math.max(0, lastRow - headerRow); var width = Math.max(1, sh.getLastColumn()); var rows = n ? plainValues_(sh.getRange(headerRow + 1, 1, n, width).getValues()) : []; // Formula errors only show in display values; getValues returns them as strings already in V8. var hidden = []; var filter = sh.getFilter(); for (var i = 0; i < n; i++) { var r = headerRow + 1 + i; hidden.push(sh.isRowHiddenByUser(r) || (filter ? sh.isRowHiddenByFilter(r) : false)); } var today = today_(); var lic = licenceState_(s, today); var res = Core.checkSheet({ rows: rows, firstRow: headerRow + 1, map: cols.map, entity: Core.checkEntity(entityFromSettings_(s)), due: due, today: today, pro: lic.pro, history: Core.parseHistory(historyValues_()), hidden: hidden }); return { sh: sh, sheetName: sheetName, headerRow: headerRow, cols: cols, rows: rows, res: res, lic: lic, today: today }; } function historyValues_() { var sh = SpreadsheetApp.getActive().getSheetByName(HISTORY); if (!sh || sh.getLastRow() < 2) return []; return plainValues_(sh.getRange(2, 1, sh.getLastRow() - 1, HISTORY_HEADERS.length).getValues()); } /** Sheets hands date cells over as Date objects. The rules want the calendar day the cell shows. */ function plainValues_(rows) { var tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone(); return rows.map(function (r) { return r.map(function (v) { return Object.prototype.toString.call(v) === '[object Date]' ? (isNaN(v.getTime()) ? '' : Utilities.formatDate(v, tz, 'yyyy-MM-dd')) : v; }); }); } /** Checks the sheet, writes Estado, and returns what the sidebar lists. */ function review(due) { var s = readSettings_(); var L = load_(due, s); if (L.cols.missing.length) return { needColumns: true, columns: L.cols }; writeStatus_(L); return summary_(L); } function summary_(L) { var res = L.res, headers = L.cols.headers; var items = res.items.slice(0, MAX_ITEMS).map(function (it) { return { row: it.row, col: it.col, column: it.col == null ? '' : (headers[it.col] || ''), code: it.code, level: it.level, args: it.args }; }); var dates = {}; res.txs.forEach(function (t) { dates[t.due] = 1; }); return { items: items, more: Math.max(0, res.items.length - MAX_ITEMS), file: res.file, ready: res.ready, errors: res.errors, warnings: res.warnings, skipped: res.skipped, totalCents: res.totalCents, total: Core.euros(res.totalCents), dates: Object.keys(dates).sort(), names: Core.changedNames(res.txs).slice(0, 20), licence: L.lic, freeLimit: Core.FREE_LIMIT, sheetName: L.sheetName }; } /** One write for the whole Estado column. Adds the column after the last used one if it's missing. */ function writeStatus_(L) { var sh = L.sh, map = L.cols.map, n = L.rows.length; if (!n) return; var col = map.status; if (col == null) { col = sh.getLastColumn(); sh.getRange(L.headerRow, col + 1).setValue(Strings.fields.status).setFontWeight('bold'); map.status = col; } var byRow = {}; L.res.items.forEach(function (it) { var cur = byRow[it.row]; if (!cur || (cur.level === 'warning' && it.level === 'error')) byRow[it.row] = it; }); var charged = {}; L.res.txs.forEach(function (t) { charged[t.row] = 1; }); var out = []; for (var i = 0; i < n; i++) { var rowNo = L.headerRow + 1 + i, it = byRow[rowNo], v = ''; if (it) v = (it.level === 'error' ? Strings.status.error : Strings.status.warning) + message_(it.code, it.args); else if (charged[rowNo]) v = Strings.status.ok; out.push([safe_(v)]); } var range = sh.getRange(L.headerRow + 1, col + 1, n, 1); range.setNumberFormat('@'); range.setValues(out); } function message_(code, args) { var a = {}; Object.keys(args || {}).forEach(function (k) { var v = args[k]; a[k] = (k === 'suggest' || k === 'date') && /^\d{4}-\d{2}-\d{2}/.test(String(v)) ? Core.dateWords(String(v).slice(0, 10)) : v; }); return fmt(Strings.codes[code] || code, a); } /** Puts the cursor on the cell with the problem. */ function goTo(row, col) { var s = readSettings_(); var sh = SpreadsheetApp.getActive().getSheetByName(sheetName_(s)); if (!sh) return; sh.activate(); sh.getRange(row, (col == null ? 0 : col) + 1).activate(); } // ---------- Build ---------- /** * Builds the file. The sidebar calls this only after the tick box. The XML goes back to the * sidebar, which saves it as a download; nothing is stored anywhere else. */ function build(due, version, ack) { if (!ack) return { ok: false, code: 'ACK' }; var s = readSettings_(); var L = load_(due, s); var res = L.res; if (L.cols.missing.length) return { ok: false, code: 'COLUMNS' }; writeStatus_(L); if (res.errors) return { ok: false, code: 'ERRORS', summary: summary_(L) }; if (res.ready > Core.FREE_LIMIT && !L.lic.pro) return { ok: false, code: 'LIMIT', n: res.ready }; var entity = Core.checkEntity(entityFromSettings_(s)); var created = nowLocal_(); var prep = Flow.prepare({ entity: entity, txs: res.txs, created: created, version: version === '02' ? '02' : '08' }, L.today); if (!prep.ok) { console.log('selfcheck failed: ' + prep.problems.length + ' problems'); return { ok: false, code: 'SELFCHECK', support: 'X-' + prep.problems.length + '-' + res.ready }; } appendHistory_(Core.historyRows(prep.built, res.txs, created.replace('T', ' '))); var dates = prep.built.blocks.map(function (b) { return b.due; }).sort(); return { ok: true, xml: prep.xml, filename: 'remesa-' + dates[0] + '.xml', count: prep.built.count, total: Core.euros(prep.built.totalCents), lastSend: Core.dateWords(Core.lastSendDay(dates[0])) }; } /** History cells go in as text, so a name that starts with = never becomes a formula. */ function appendHistory_(rows) { if (!rows.length) return; var ss = SpreadsheetApp.getActive(); var sh = ss.getSheetByName(HISTORY); if (!sh) { var was = ss.getActiveSheet(); sh = ss.insertSheet(HISTORY); if (was) was.activate(); sh.getRange(1, 1, 1, HISTORY_HEADERS.length).setValues([HISTORY_HEADERS]).setFontWeight('bold'); sh.setFrozenRows(1); } var start = sh.getLastRow() + 1; var data = rows.map(function (r) { return r.map(safe_); }); var range = sh.getRange(start, 1, data.length, HISTORY_HEADERS.length); range.setNumberFormat('@'); range.setValues(data); sh.getRange(start, HISTORY_HEADERS.length, data.length, 1).insertCheckboxes(); } // ---------- Template ---------- /** Creates the members sheet with one example row. Never overwrites: an existing Socios gets Socios 2. */ function createTemplate() { var ss = SpreadsheetApp.getActive(); var name = 'Socios', i = 2; while (ss.getSheetByName(name)) name = 'Socios ' + i++; var sh = ss.insertSheet(name); var h = Core.TEMPLATE_HEADERS; sh.getRange(1, 1, 1, h.length).setValues([h]).setFontWeight('bold').setBackground('#f1f3f4'); sh.setFrozenRows(1); var ex = Strings.ui.templateExample.slice(0, h.length); ex[5] = today_(); sh.getRange(2, 1, 1, h.length).setValues([ex.map(String)]); var col = function (k) { return h.indexOf(Strings.fields[k]) + 1; }; sh.getRange(2, col('amount'), 999, 1).setNumberFormat('0.00'); sh.getRange(2, col('signed'), 999, 1).setNumberFormat('dd/mm/yyyy'); sh.getRange(2, col('due'), 999, 1).setNumberFormat('dd/mm/yyyy'); sh.getRange(2, col('signed')).setValue(new Date()); sh.getRange(2, col('type'), 999, 1).setDataValidation(SpreadsheetApp.newDataValidation() .requireValueInList(['Recurrente', 'Primero', 'Último', 'Único'], true).setAllowInvalid(true).build()); sh.getRange(2, col('charge'), 999, 1).setDataValidation(SpreadsheetApp.newDataValidation() .requireValueInList(['Sí', 'No', 'Ejemplo'], true).setAllowInvalid(true).build()); sh.autoResizeColumns(1, h.length); sh.activate(); var pairs = {}; pairs[K.sheet] = name; pairs[K.headerRow] = 1; pairs[K.columns] = '{}'; writeSettings_(pairs); return { sheetName: name, message: fmt(Strings.ui.templateDone, { hoja: name }) }; } // ---------- Mandates ---------- function printBlank() { showMandates_([{}]); } /** Pro: one filled order per selected row. */ function printFilled() { var s = readSettings_(); if (!licenceState_(s, today_()).pro) return { ok: false, code: 'PRO' }; var ss = SpreadsheetApp.getActive(); var sh = ss.getSheetByName(sheetName_(s)); var sel = ss.getActiveRangeList(); if (!sh || !sel || ss.getActiveSheet().getName() !== sh.getName()) return { ok: false, code: 'SELECT' }; var headerRow = +(s[K.headerRow] || 1); var cols = columnsFor_(sh.getName(), headerRow, s); var width = sh.getLastColumn(), people = [], seen = {}; sel.getRanges().forEach(function (r) { var vals = sh.getRange(r.getRow(), 1, r.getNumRows(), width).getValues(); vals.forEach(function (v, i) { var rowNo = r.getRow() + i; if (rowNo <= headerRow || seen[rowNo]) return; seen[rowNo] = 1; var get = function (k) { return cols.map[k] == null ? '' : v[cols.map[k]]; }; var ib = Core.checkIban(get('iban')); var type = Core.norm(get('type')); people.push({ name: String(get('holder') || ''), ref: String(get('mandate') || ''), iban: ib.error ? '' : ib.iban, oneOff: type === 'unico' }); }); }); if (!people.length) return { ok: false, code: 'SELECT' }; showMandates_(people.slice(0, 200)); return { ok: true, n: people.length }; } function showMandates_(people) { var s = readSettings_(); var e = Core.checkEntity(entityFromSettings_(s)); var t = HtmlService.createTemplate(HTML_FILES_['Mandate']); t.data = JSON.stringify({ creditor: { name: e.tradeName || e.name, id: e.ok ? e.creditorId : '', address: s[K.address] || '', town: s[K.town] || '', province: s[K.province] || '' }, people: people }).replace(/= today; var lapsed = !!plan && !!expires && expires < today; return { plan: active ? plan : 'free', pro: active, expires: expires, lapsed: lapsed, hasKey: !!s[K.licence], lastCheck: last, installId: s[K.installId] || '', needsCheck: active && (!last || Core.addDays(last.slice(0, 10), 1) <= today), graceLeft: last ? Math.max(0, GRACE_DAYS - daysBetween_(last.slice(0, 10), today)) : 0, productId: LICENCE.productId }; } function daysBetween_(a, b) { return Math.round((Date.UTC(+b.slice(0, 4), +b.slice(5, 7) - 1, +b.slice(8, 10)) - Date.UTC(+a.slice(0, 4), +a.slice(5, 7) - 1, +a.slice(8, 10))) / 86400000); } /** What the sidebar sends to Freemius: uid and key, never sheet data. */ function licenceRequest(key) { var s = readSettings_(); var plan = s[K.plan] || ''; var uid = s[K.uid]; if (!uid || plan !== 'agency') { var e = Core.checkEntity(entityFromSettings_(s)); if (!e.ok) return { ok: false, code: 'ENTITY' }; uid = hex32_('remesa-facil:' + e.creditorId); } return { ok: true, uid: uid, key: String(key || s[K.licence] || '').trim(), installId: s[K.installId] || '', productId: LICENCE.productId, agencyPlanId: LICENCE.agencyPlanId, apiBase: LICENCE.apiBase, title: LICENCE.title }; } /** Asesorías: a random uid per sheet, as it isn't tied to one entity. */ function agencyUid() { var uid = hex32_(Utilities.getUuid() + ':' + Date.now()); var pairs = {}; pairs[K.uid] = uid; writeSettings_(pairs); return uid; } function hex32_(text) { var bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, text, Utilities.Charset.UTF_8); return bytes.map(function (b) { return ('0' + (b & 255).toString(16)).slice(-2); }).join('').slice(0, 32); } /** * Stores what Freemius said. result: {status: 'ok'|'wrong'|'used'|'offline'|'expired', * key, plan ('pro'|'agency'), expires (YYYY-MM-DD), installId}. */ function recordLicence(result) { var s = readSettings_(); var today = today_(); var pairs = {}; if (result.status === 'ok') { pairs[K.licence] = result.key || s[K.licence] || ''; pairs[K.plan] = result.plan === 'agency' ? 'agency' : 'pro'; pairs[K.expires] = String(result.expires || '').slice(0, 10); if (result.installId) pairs[K.installId] = result.installId; pairs[K.lastCheck] = nowLocal_().replace('T', ' '); writeSettings_(pairs); } else if (result.status === 'expired') { pairs[K.expires] = String(result.expires || today).slice(0, 10); pairs[K.lastCheck] = nowLocal_().replace('T', ' '); writeSettings_(pairs); } else if (result.status === 'offline') { // A key that was valid in the last 14 days keeps working; after that, back to Free. var st = licenceState_(s, today); if (st.pro && st.graceLeft <= 0) { pairs[K.expires] = Core.addDays(today, -1); writeSettings_(pairs); } } return licenceState_(readSettings_(), today); } // ---------- Small helpers for the sidebar ---------- /** Date check and the last day to send, for the date field as the user types. */ function dueInfo(due) { var today = today_(); var c = Core.checkDue(due, today); var info = { error: c.error || null, warnings: c.warnings, suggest: c.suggest || null }; if (c.suggest) info.suggestWords = Core.dateWords(c.suggest); if (!c.error) { info.words = Core.dateWords(due); info.lastSend = Core.dateWords(Core.lastSendDay(due)); } return info; } /** Strings for the sidebar, as JSON safe to put inside a script tag. */ function stringsJson_() { return JSON.stringify(Strings).replace(/\n\n\n\n\n\n\n\n
\n
\n
\n
\n\n\n\n\n\n","Mandate":"\n\n\n\n\n\n\n\n

\n
\n\n\n\n\n\n"};