/** @OnlyCurrentDoc */ // Tip Fair for Google Sheets \u00b7 https://tipfair.facilo.es // Paste this whole file into Extensions > Apps Script (Code.gs), save, and reopen your spreadsheet. // It only reads and writes the spreadsheet it is installed in. Your tips data never leaves Google. // Tip Fair core: all pure logic (spec section 6). Runs in Apps Script, in the sidebar and in Node tests. // Amounts are integer pennies or cents. Hours and points are integer hundredths. Dates are 'yyyy-mm-dd' strings. var Core = (function () { 'use strict'; var MONTHS = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']; var TYPES = ['Card', 'Cash', 'Service charge', 'Voucher']; var METHODS = ['Equal', 'Hours', 'Points', 'Hours \u00d7 points']; var STAFF_TYPES = ['Worker', 'Agency worker', 'Owner or manager', 'Self-employed']; var LANGS = ['English', 'Spanish', 'Polish', 'Portuguese', 'Romanian', 'Italian']; var FREE_LIMIT = 5, GRACE_DAYS = 14; function pad(n) { return (n < 10 ? '0' : '') + n; } function err(text) { return { ok: false, error: text }; } /* ---------- 6.5 largest remainder ---------- */ // Splits integer n by integer weights. Exact share e_k = n*w_k/W, worked out with BigInt so nothing rounds early. // Ties: larger fraction, then larger exact share, then list order. // No BigInt literals (0n): the Apps Script editor's parser refuses them, so the constants are made with BigInt(). function largestRemainder(n, weights) { var ZERO = BigInt(0), ONE = BigInt(1); var W = ZERO, N = BigInt(n); weights.forEach(function (w) { W += BigInt(w); }); if (W <= ZERO) return weights.map(function () { return 0; }); var items = weights.map(function (w, k) { var num = N * BigInt(w), fl = num / W, rem = num % W; if (rem < ZERO) { fl -= ONE; rem += W; } // floor for negative n return { k: k, num: num, floor: fl, rem: rem }; }); var L = N; items.forEach(function (it) { L -= it.floor; }); var order = items.slice().sort(function (a, b) { if (a.rem !== b.rem) return a.rem > b.rem ? -1 : 1; if (a.num !== b.num) return a.num > b.num ? -1 : 1; return a.k - b.k; }); for (var i = 0; i < Number(L); i++) order[i].floor += ONE; return items.map(function (it) { return Number(it.floor); }); } /* ---------- numbers ---------- */ // Hours: a number or 'h:mm'. Returns hundredths of an hour, or null. function parseHours(v) { if (v === '' || v == null) return 0; if (typeof v === 'number') return isFinite(v) && v >= 0 ? Math.round(v * 100) : null; if (Object.prototype.toString.call(v) === '[object Date]') return Math.round((v.getHours() + v.getMinutes() / 60) * 100); var s = String(v).trim(), m = /^(\d+):([0-5]\d)$/.exec(s); if (m) return Number(m[1]) * 100 + Math.round(Number(m[2]) * 100 / 60); s = s.replace(',', '.'); if (!/^\d+(\.\d+)?$/.test(s)) return null; return Math.round(parseFloat(s) * 100); } // Points: blank is 1, any number >= 0 up to 2 decimals. Returns hundredths, or null. function parsePoints(v) { if (v === '' || v == null) return 100; var x = typeof v === 'number' ? v : parseFloat(String(v).trim().replace(',', '.')); if (!isFinite(x) || x < 0 || !/^\s*\d*([.,]\d*)?\s*$/.test(String(v))) return null; return Math.round(x * 100); } function hundredths(h) { var x = h / 100; return Math.round(x * 100) / 100; } // Money from a sheet cell or CSV text. comma: true when the decimal mark is a comma (1.234,56). function parseAmount(v, comma) { if (typeof v === 'number') return isFinite(v) ? Math.round(v * 100) : null; var s = String(v == null ? '' : v).trim().replace(/[\u00a3\u20ac$\s\u00a0]|GBP|EUR/gi, ''); if (!s) return null; var neg = false; if (/^\(.*\)$/.test(s)) { neg = true; s = s.slice(1, -1); } if (/^[-\u2212]/.test(s)) { neg = true; s = s.slice(1); } if (comma) s = s.replace(/\./g, '').replace(',', '.'); else s = s.replace(/,/g, ''); if (!/^\d*(\.\d{0,4})?$/.test(s) || s === '' || s === '.') return null; var p = Math.round(parseFloat(s) * 100); return neg ? -p : p; } /* ---------- dates ---------- */ var MON = { jan: 1, feb: 2, mar: 3, apr: 4, may: 5, jun: 6, jul: 7, aug: 8, sep: 9, sept: 9, oct: 10, nov: 11, dec: 12 }; function iso(y, m, d) { if (y < 100) y += 2000; var t = new Date(Date.UTC(y, m - 1, d)); if (t.getUTCFullYear() !== y || t.getUTCMonth() !== m - 1 || t.getUTCDate() !== d) return null; return y + '-' + pad(m) + '-' + pad(d); } // order: 'dmy' (default) or 'mdy' only when the venue says so for an ambiguous file. Never guesses mm/dd. function parseDate(v, order) { if (v == null || v === '') return null; if (Object.prototype.toString.call(v) === '[object Date]') return isNaN(v) ? null : iso(v.getFullYear(), v.getMonth() + 1, v.getDate()); var s = String(v).trim().replace(/[T ]\d{1,2}:\d\d(:\d\d)?(\.\d+)?\s*(Z|[ap]m|[+-]\d\d:?\d\d)?$/i, '').trim(), m; if ((m = /^(\d{4})-(\d{1,2})-(\d{1,2})$/.exec(s))) return iso(+m[1], +m[2], +m[3]); if ((m = /^(\d{1,2})[\/.\-](\d{1,2})[\/.\-](\d{2}|\d{4})$/.exec(s))) return order === 'mdy' ? iso(+m[3], +m[1], +m[2]) : iso(+m[3], +m[2], +m[1]); if ((m = /^(\d{1,2})[ \-]([A-Za-z]{3,9})\.?[ \-,]+(\d{4})$/.exec(s))) { var mo = MON[m[2].slice(0, 4).toLowerCase()] || MON[m[2].slice(0, 3).toLowerCase()]; return mo ? iso(+m[3], mo, +m[1]) : null; } return null; } // True when every slash date in the list has day and month both <= 12 and at least one differs (3/4 vs 4/3). function ambiguousDates(values) { var seen = false, any = false; for (var i = 0; i < values.length; i++) { var s = String(values[i] == null ? '' : values[i]).trim().replace(/[T ]\d{1,2}:\d\d.*$/, ''); var m = /^(\d{1,2})[\/.\-](\d{1,2})[\/.\-](\d{2}|\d{4})$/.exec(s); if (!m) continue; any = true; if (+m[1] > 12 || +m[2] > 12) return false; if (+m[1] !== +m[2]) seen = true; } return any && seen; } function addDays(d, n) { var t = new Date(d + 'T12:00:00Z'); t.setUTCDate(t.getUTCDate() + n); return t.toISOString().slice(0, 10); } function addYears(d, n) { var y = +d.slice(0, 4) + n, m = +d.slice(5, 7), day = +d.slice(8, 10); return iso(y, m, day) || iso(y, m, day - 1); } function lastDay(y, m) { return new Date(Date.UTC(y, m, 0)).getUTCDate(); } function monthEnd(d) { var y = +d.slice(0, 4), m = +d.slice(5, 7); return d.slice(0, 8) + pad(lastDay(y, m)); } function addMonths(ym, n) { var y = +ym.slice(0, 4), m = +ym.slice(5, 7) - 1 + n; y += Math.floor(m / 12); m = ((m % 12) + 12) % 12; return y + '-' + pad(m + 1); } function daysBetween(a, b) { return Math.round((Date.parse(b + 'T12:00:00Z') - Date.parse(a + 'T12:00:00Z')) / 864e5); } function d(s, style) { if (!s) return ''; var y = +s.slice(0, 4), m = +s.slice(5, 7), day = +s.slice(8, 10), M = MONTHS[m - 1]; switch (style) { case 'long': return day + ' ' + M + ' ' + y; case 'my': return M + ' ' + y; case 'mY': return M.slice(0, 3) + ' ' + y; case 'm': return M; case 'dM': return day + ' ' + M; case 'dm': return day + ' ' + M.slice(0, 3); default: return day + ' ' + M.slice(0, 3) + ' ' + y; } } /* ---------- 6.7 deadlines ---------- */ // Great Britain: the end of the month after the month of the earliest tip. function gbDeadline(earliest) { var ym = addMonths(earliest.slice(0, 7), 1); return monthEnd(ym + '-01'); } function ieStatementBy(payout) { return addDays(payout, 10); } /* ---------- money ---------- */ function money(p, cur) { var neg = p < 0, a = Math.abs(p), s = String(Math.floor(a / 100)).replace(/\B(?=(\d{3})+(?!\d))/g, ','); return (neg ? '\u2212' : '') + (cur || '\u00a3') + s + '.' + pad(a % 100); } function plain(p) { return (p < 0 ? '-' : '') + Math.floor(Math.abs(p) / 100) + '.' + pad(Math.abs(p) % 100); } /* ---------- 6.1 tips ---------- */ function inPeriod(t, from, to) { return t.date && t.date >= from && t.date <= to; } function tipsTotal(tips, from, to) { var total = 0, byType = { Card: 0, Cash: 0, 'Service charge': 0, Voucher: 0 }, n = 0, first = null; tips.forEach(function (t) { if (!inPeriod(t, from, to)) return; var v = t.amount - (t.vat || 0); total += v; n++; byType[TYPES.indexOf(t.type) > -1 ? t.type : 'Card'] += v; if (!first || t.date < first) first = t.date; }); return { total: total, byType: byType, count: n, first: first }; } /* ---------- 6.2 weights ---------- */ function weight(method, h, p, anyHours) { switch (method) { case 'Equal': return !anyHours || h > 0 ? 1 : 0; case 'Hours': return h; case 'Points': return h > 0 ? p : 0; default: return h * p; // Hours \u00d7 points } } /* ---------- hours check (5.4) ---------- */ function checkHours(staff, hours, from, to, monthName) { var names = {}, msgs = [], total = 0, people = {}; staff.forEach(function (s) { names[s.name] = 1; }); var unknown = {}; hours.forEach(function (r) { if (r.date && (r.date < from || r.date > to)) return; if (!names[r.name]) { unknown[r.name] = 1; return; } total += r.hours; if (r.hours > 0) people[r.name] = 1; }); Object.keys(unknown).forEach(function (n) { msgs.push({ k: 'unknown', name: n, text: '"' + n + '" has hours but isn\'t on the Staff sheet. Add them, or fix the name.' }); }); staff.forEach(function (s) { if (!people[s.name]) msgs.push({ k: 'none', name: s.name, text: s.name + ' has no hours in ' + monthName + ', so gets no share unless the rule is Equal.' }); }); return { total: total, people: Object.keys(people).length, unknown: Object.keys(unknown), messages: msgs }; } /* ---------- 6.3, 6.4 split ---------- */ // o: {tips, staff:[{name, full, group, points, type, lang}], hours:[{date, name, hours}], from, to, // groupsOn, groups:[{name, pct, method}], method, pool: 'period'|'day', tronc} function split(o) { var tt = tipsTotal(o.tips, o.from, o.to), T = tt.total, R = o.tronc || 0; if (R < 0 || R > Math.max(T, 0)) return err('The tronc amount can\'t be more than the tips.'); var N = T - R; if (N <= 0) return err('There are no tips to share in this period.'); var defs = o.groupsOn ? o.groups : [{ name: 'All staff', pct: 100, method: o.method }]; var pctSum = 0; defs.forEach(function (g) { pctSum += Math.round(Number(g.pct || 0) * 100); }); if (pctSum !== 10000) return err('Group shares add up to ' + pctSum / 100 + '%. Make them 100%.'); var known = {}; o.staff.forEach(function (s) { known[s.name] = 1; }); var hrs = o.hours.filter(function (r) { return known[r.name] && (!r.date || (r.date >= o.from && r.date <= o.to)); }); var groups = defs.map(function (g) { var people = o.staff.filter(function (s) { return !o.groupsOn || s.group === g.name; }).map(function (s) { return { name: s.name, full: s.full || s.name, group: g.name, type: s.type || 'Worker', lang: s.lang || 'English', p: s.points == null ? 100 : s.points, h: 0, w: 0, share: 0 }; }); return { name: g.name, pct: Number(g.pct), method: g.method, people: people, pot: 0 }; }); var days; if (o.pool === 'day') { if (hrs.some(function (r) { return !r.date; })) return err('Each-day sharing needs a date on every Hours row.'); var byDay = {}; o.tips.forEach(function (t) { if (inPeriod(t, o.from, o.to)) byDay[t.date] = (byDay[t.date] || 0) + t.amount - (t.vat || 0); }); days = Object.keys(byDay).sort().filter(function (k) { return byDay[k] !== 0; }).map(function (k) { return { date: k, T: byDay[k] }; }); var tr = largestRemainder(R, days.map(function (x) { return Math.max(x.T, 0); })); days.forEach(function (x, i) { x.N = x.T - tr[i]; }); } else days = [{ date: null, N: N }]; var pots = groups.map(function () { return 0; }); for (var di = 0; di < days.length; di++) { var day = days[di], hoursOf = {}, any = false; hrs.forEach(function (r) { if (!day.date || r.date === day.date) { hoursOf[r.name] = (hoursOf[r.name] || 0) + r.hours; if (r.hours > 0) any = true; } }); if (day.date && !any && day.N !== 0) return err(Core.d(day.date, 'dm') + ' has tips but no hours. Add hours for that day, or share by the whole period.'); var anyHours = hrs.length > 0; var ws = groups.map(function (g) { return g.people.map(function (p) { var h = hoursOf[p.name] || 0; return weight(g.method, h, p.p, anyHours); }); }); for (var gi = 0; gi < groups.length; gi++) { var sw = ws[gi].reduce(function (a, b) { return a + b; }, 0); if (sw === 0 && groups[gi].pct > 0) return err('Nobody in ' + groups[gi].name + ' has hours in this period. Set its share to 0%, or add hours.'); } var dpots = largestRemainder(day.N, groups.map(function (g) { return Math.round(g.pct * 100); })); groups.forEach(function (g, gi2) { pots[gi2] += dpots[gi2]; var s = largestRemainder(dpots[gi2], ws[gi2]); g.people.forEach(function (p, k) { p.share += s[k]; p.h += hoursOf[p.name] || 0; p.w += ws[gi2][k]; }); }); } var sharers = 0, check = 0, people = []; groups.forEach(function (g, gi) { g.pot = pots[gi]; g.people.forEach(function (p) { check += p.share; if (p.share > 0) sharers++; people.push(p); }); }); if (check !== N) return err('Shares don\'t add up. Please write to support@facilo.es.'); return { ok: true, T: T, R: R, Tsplit: N, byType: tt.byType, count: tt.count, first: tt.first, groups: groups, people: people, sharers: sharers, days: o.pool === 'day' ? days.length : 0 }; } // 6.10: individual shares need Pro when more than 5 people share. function locked(r, pro) { return r.ok && r.sharers > FREE_LIMIT && !pro; } function ruleText(o) { var defs = o.groupsOn ? o.groups : [{ name: 'All staff', pct: 100, method: o.method }]; return defs.map(function (g) { return g.name + ' ' + g.pct + '% ' + g.method.toLowerCase().replace(' \u00d7 ', '\u00d7'); }).join('; ') + '; ' + (o.pool === 'day' ? 'each day' : 'whole period'); } /* ---------- 6.8 record ---------- */ var RECORD_COLS = ['Period ID', 'From', 'To', 'Paid on', 'Tips total', 'Card', 'Cash', 'Service charge', 'Voucher', 'Through tronc', 'To workers', 'Rule', 'Name', 'Group', 'Hours', 'Points', 'Share', 'Finalised at', 'Fingerprint', 'Keep until', 'Note', 'Status']; var FP = RECORD_COLS.indexOf('Fingerprint'); // One row per person with a share. Amounts as decimal text so the sheet shows them and the fingerprint is stable. function recordRows(r, o, payout, at) { var id = o.from + '_' + o.to, rule = ruleText(o), keep = addYears(payout, 3); var rows = r.people.filter(function (p) { return p.share > 0; }).map(function (p) { return [id, o.from, o.to, payout, plain(r.T), plain(r.byType.Card), plain(r.byType.Cash), plain(r.byType['Service charge']), plain(r.byType.Voucher), plain(r.R), plain(r.Tsplit), rule, p.full, p.group, String(hundredths(p.h)), String(hundredths(p.p)), plain(p.share), at, '', keep, '', '']; }); var fp = fingerprint(rows); rows.forEach(function (row) { row[FP] = fp; }); return rows; } // Canonical JSON of the block, without Fingerprint, Note and Status. First 16 hex of SHA-256. function fingerprint(rows) { var canon = rows.map(function (row) { return row.slice(0, FP).map(function (v) { return String(v == null ? '' : v); }); }); return sha256(JSON.stringify(canon)).slice(0, 16); } // Groups the Record sheet rows (from row 2, as text) into blocks and checks each fingerprint. function readRecord(rows) { var blocks = [], cur = null; rows.forEach(function (row, i) { var r = row.map(function (v) { return v == null ? '' : String(v); }); if (!r[0]) return; var key = r[0] + '|' + r[17]; // One row per person in a block, so a repeated name starts a new one (two finalises in the same minute). if (!cur || cur.key !== key || cur.names[r[12]]) { cur = { key: key, id: r[0], from: r[1], to: r[2], paid: r[3], total: toP(r[4]), tronc: toP(r[9]), toWorkers: toP(r[10]), rule: r[11], at: r[17], fp: r[18], status: r[21], rows: [], names: {}, first: i }; blocks.push(cur); } cur.rows.push(r); cur.names[r[12]] = 1; if (r[21]) cur.status = r[21]; }); blocks.forEach(function (b) { b.changed = fingerprint(b.rows) !== b.fp; b.replaced = /^Replaced/.test(b.status); }); return blocks; } function toP(s) { var p = parseAmount(s, false); return p == null ? 0 : p; } /* ---------- 6.9 record request ---------- */ function requestRange(q, m1, m2) { var minM = addMonths(q.slice(0, 7), -36), maxM = addMonths(q.slice(0, 7), -1), errors = []; if (m1 < minM) errors.push({ k: 'min', m: minM, text: 'The months must start no more than 3 years before the request: ' + d(minM + '-01', 'my') + ' or later.' }); if (m2 > maxM) errors.push({ k: 'max', m: maxM, text: 'The months must have ended before the request date: ' + d(maxM + '-01', 'my') + ' or earlier.' }); if (m1 > m2) errors.push({ k: 'order', text: 'The first month must come before the last.' }); return { errors: errors, min: minM, max: maxM, replyBy: addDays(q, 27) }; } // Totals per payout month for one worker, from finalised blocks that weren't replaced. function letterRows(blocks, fullName, m1, m2) { var months = [], byM = {}; for (var m = m1; m <= m2; m = addMonths(m, 1)) { months.push(m); byM[m] = { month: m, total: 0, toWorkers: 0, tronc: 0, share: 0, worked: false }; } blocks.forEach(function (b) { if (b.replaced) return; var m = b.paid.slice(0, 7), x = byM[m]; if (!x) return; x.total += b.total; x.toWorkers += b.toWorkers; x.tronc += b.tronc; b.rows.forEach(function (r) { if (r[12] === fullName) { x.share += toP(r[16]); x.worked = true; } }); }); var rows = months.map(function (m) { return byM[m]; }), sum = { total: 0, toWorkers: 0, tronc: 0, share: 0 }; rows.forEach(function (x) { sum.total += x.total; sum.toWorkers += x.toWorkers; sum.tronc += x.tronc; sum.share += x.share; }); // Periods that end in the asked months but are paid after them, so the owner can say where they will show. var later = blocks.filter(function (b) { var e = b.to.slice(0, 7); return !b.replaced && e >= m1 && e <= m2 && b.paid.slice(0, 7) > m2; }) .map(function (b) { return { from: b.from, to: b.to, paid: b.paid }; }); return { rows: rows, sum: sum, later: later, noShare: rows.filter(function (x) { return !x.worked; }).map(function (x) { return x.month; }) }; } /* ---------- 6.12 CSV ---------- */ var MAX_CSV = 5 * 1024 * 1024; function parseCsv(text) { if (text.length > MAX_CSV) return err('That file is over 5 MB. Export a shorter period.'); if (text.charCodeAt(0) === 0xfeff) text = text.slice(1); var nl = text.search(/\r?\n/), head = nl < 0 ? text : text.slice(0, nl); var counts = { ',': 0, ';': 0, '\t': 0 }, q = false; for (var i = 0; i < head.length; i++) { var c = head[i]; if (c === '"') q = !q; else if (!q && counts[c] != null) counts[c]++; } var sep = counts['\t'] > counts[','] && counts['\t'] >= counts[';'] ? '\t' : counts[';'] > counts[','] ? ';' : ','; var rows = [], row = [], f = '', inq = false; for (var j = 0; j < text.length; j++) { var ch = text[j]; if (inq) { if (ch === '"') { if (text[j + 1] === '"') { f += '"'; j++; } else inq = false; } else f += ch; } else if (ch === '"') inq = true; else if (ch === sep) { row.push(f); f = ''; } else if (ch === '\n' || ch === '\r') { if (ch === '\r' && text[j + 1] === '\n') j++; row.push(f); rows.push(row); row = []; f = ''; } else f += ch; } if (f !== '' || row.length) { row.push(f); rows.push(row); } rows = rows.filter(function (r) { return r.some(function (x) { return x.trim() !== ''; }); }); if (!rows.length) return err('That file is empty.'); return { ok: true, sep: sep, headers: rows[0].map(function (h) { return h.trim(); }), rows: rows.slice(1) }; } var WORDS = { date: ['date', 'created', 'time', 'transaction date'], tip: ['tip', 'tips', 'gratuity', 'tip amount'], staff: ['staff', 'employee', 'team member', 'server', 'operator'], ref: ['reference', 'ref', 'transaction id', 'receipt number', 'receipt', 'payment id', 'id'] }; function findCol(headers, words) { var low = headers.map(function (h) { return h.toLowerCase().replace(/[_\-]+/g, ' ').replace(/\s*\(.*\)$/, '').trim(); }); for (var w = 0; w < words.length; w++) { var i = low.indexOf(words[w]); if (i > -1) return i; } for (var w2 = 0; w2 < words.length; w2++) { for (var k = 0; k < low.length; k++) if (new RegExp('(^|\\s)' + words[w2] + '(\\s|$)').test(low[k])) return k; } return -1; } function detectColumns(headers) { var m = { date: findCol(headers, WORDS.date), tip: findCol(headers, WORDS.tip), staff: findCol(headers, WORDS.staff), ref: findCol(headers, WORDS.ref) }; if (m.ref === m.date || m.ref === m.tip) m.ref = -1; m.found = m.date > -1 && m.tip > -1; return m; } function mappingKey(headers) { return 'map:' + headers.map(function (h) { return h.toLowerCase(); }).sort().join('|'); } // Named tills, matched by their own export headers (fixtures in tests/fixtures). var TILLS = [ { name: 'Square', has: ['date', 'time', 'tip', 'gross sales', 'transaction id'] }, { name: 'SumUp', has: ['transaction id', 'date', 'tip amount', 'total amount'] }, { name: 'Zettle', has: ['date', 'receipt number', 'tip', 'staff'] }, { name: 'Dojo', has: ['transaction date', 'gratuity', 'transaction id'] }, { name: 'Teya', has: ['transaction date', 'tip amount', 'terminal id'] }, { name: 'Lightspeed', has: ['date', 'receipt id', 'tips', 'employee'] }, { name: 'Clover', has: ['payment id', 'tip amount', 'employee', 'payment date'] } ]; function tillName(headers) { var low = headers.map(function (h) { return h.toLowerCase().trim(); }); for (var i = 0; i < TILLS.length; i++) if (TILLS[i].has.every(function (w) { return low.indexOf(w) > -1; })) return TILLS[i].name; return ''; } // Turns parsed CSV rows into tips with a mapping {date, tip, staff, ref}. order: 'dmy' | 'mdy'. function csvTips(p, map, source, order) { var comma = p.sep === ';', out = [], bad = 0; p.rows.forEach(function (r) { var a = parseAmount(r[map.tip], comma); if (a == null || a === 0) return; var dt = parseDate(r[map.date], order); if (!dt) { bad++; return; } out.push({ date: dt, amount: a, type: 'Card', vat: 0, source: source || '', ref: map.ref > -1 ? String(r[map.ref] || '').trim() : '', staff: map.staff > -1 ? String(r[map.staff] || '').trim() : '' }); }); var first = null, last = null, total = 0; out.forEach(function (t) { total += t.amount; if (!first || t.date < first) first = t.date; if (!last || t.date > last) last = t.date; }); return { tips: out, count: out.length, total: total, first: first, last: last, bad: bad, ambiguous: order ? false : ambiguousDates(p.rows.map(function (r) { return r[map.date]; })) }; } // Duplicates: same date, amount and reference as a tip already in the sheet (or earlier in the file). function dedupe(existing, incoming) { var seen = {}, keep = [], skipped = 0; function key(t) { return t.date + '|' + t.amount + '|' + (t.ref || ''); } existing.forEach(function (t) { seen[key(t)] = 1; }); incoming.forEach(function (t) { var k = key(t); if (seen[k] && (t.ref || existing.length)) { skipped++; return; } seen[k] = 1; keep.push(t); }); return { tips: keep, skipped: skipped }; } /* ---------- safety ---------- */ // Text that Sheets would read as a formula gets a leading apostrophe. function safeText(v) { var s = v == null ? '' : String(v); return /^[=+\-@]/.test(s) ? '\'' + s : s; } /* ---------- 6.11 licence ---------- */ // res: Freemius license object or null when offline. lastGood: 'yyyy-mm-dd' of the last good check. function licenceState(res, today, lastGood) { if (res === null) { if (!lastGood) return { pro: false, status: 'offline', left: 0 }; var left = GRACE_DAYS - daysBetween(lastGood, today); return { pro: left > 0, status: 'offline', left: Math.max(0, left) }; } if (!res || res.error) return { pro: false, status: 'wrong' }; if (res.is_cancelled) return { pro: false, status: 'cancelled' }; var exp = res.expiration ? String(res.expiration).slice(0, 10) : null; if (exp && exp < today) return { pro: false, status: 'expired', expires: exp }; return { pro: true, status: 'ok', expires: exp }; } /* ---------- SHA-256 (sync, so the same code runs everywhere) ---------- */ var K256 = [0x428a2f98, 0x71374491, 0xb5c0fbcf, 0xe9b5dba5, 0x3956c25b, 0x59f111f1, 0x923f82a4, 0xab1c5ed5, 0xd807aa98, 0x12835b01, 0x243185be, 0x550c7dc3, 0x72be5d74, 0x80deb1fe, 0x9bdc06a7, 0xc19bf174, 0xe49b69c1, 0xefbe4786, 0x0fc19dc6, 0x240ca1cc, 0x2de92c6f, 0x4a7484aa, 0x5cb0a9dc, 0x76f988da, 0x983e5152, 0xa831c66d, 0xb00327c8, 0xbf597fc7, 0xc6e00bf3, 0xd5a79147, 0x06ca6351, 0x14292967, 0x27b70a85, 0x2e1b2138, 0x4d2c6dfc, 0x53380d13, 0x650a7354, 0x766a0abb, 0x81c2c92e, 0x92722c85, 0xa2bfe8a1, 0xa81a664b, 0xc24b8b70, 0xc76c51a3, 0xd192e819, 0xd6990624, 0xf40e3585, 0x106aa070, 0x19a4c116, 0x1e376c08, 0x2748774c, 0x34b0bcb5, 0x391c0cb3, 0x4ed8aa4a, 0x5b9cca4f, 0x682e6ff3, 0x748f82ee, 0x78a5636f, 0x84c87814, 0x8cc70208, 0x90befffa, 0xa4506ceb, 0xbef9a3f7, 0xc67178f2]; function utf8(s) { var out = []; for (var i = 0; i < s.length; i++) { var c = s.charCodeAt(i); if (c >= 0xd800 && c < 0xdc00 && i + 1 < s.length) { c = 0x10000 + ((c - 0xd800) << 10) + (s.charCodeAt(++i) - 0xdc00); } if (c < 0x80) out.push(c); else if (c < 0x800) out.push(0xc0 | c >> 6, 0x80 | c & 63); else if (c < 0x10000) out.push(0xe0 | c >> 12, 0x80 | c >> 6 & 63, 0x80 | c & 63); else out.push(0xf0 | c >> 18, 0x80 | c >> 12 & 63, 0x80 | c >> 6 & 63, 0x80 | c & 63); } return out; } function sha256(s) { var b = utf8(s), l = b.length * 8, H = [0x6a09e667, 0xbb67ae85, 0x3c6ef372, 0xa54ff53a, 0x510e527f, 0x9b05688c, 0x1f83d9ab, 0x5be0cd19], w = new Array(64); b.push(0x80); while (b.length % 64 !== 56) b.push(0); for (var sh = 56; sh >= 0; sh -= 8) b.push(sh >= 32 ? Math.floor(l / Math.pow(2, sh)) & 255 : l >>> sh & 255); for (var o = 0; o < b.length; o += 64) { for (var t = 0; t < 16; t++) w[t] = b[o + 4 * t] << 24 | b[o + 4 * t + 1] << 16 | b[o + 4 * t + 2] << 8 | b[o + 4 * t + 3]; for (t = 16; t < 64; t++) { var x = w[t - 15], y = w[t - 2]; var s0 = (x >>> 7 | x << 25) ^ (x >>> 18 | x << 14) ^ x >>> 3, s1 = (y >>> 17 | y << 15) ^ (y >>> 19 | y << 13) ^ y >>> 10; w[t] = w[t - 16] + s0 + w[t - 7] + s1 | 0; } var a = H[0], bb = H[1], c = H[2], dd = H[3], e = H[4], f = H[5], g = H[6], h = H[7]; for (t = 0; t < 64; t++) { var S1 = (e >>> 6 | e << 26) ^ (e >>> 11 | e << 21) ^ (e >>> 25 | e << 7), ch = e & f ^ ~e & g; var t1 = h + S1 + ch + K256[t] + w[t] | 0; var S0 = (a >>> 2 | a << 30) ^ (a >>> 13 | a << 19) ^ (a >>> 22 | a << 10), mj = a & bb ^ a & c ^ bb & c; var t2 = S0 + mj | 0; h = g; g = f; f = e; e = dd + t1 | 0; dd = c; c = bb; bb = a; a = t1 + t2 | 0; } H[0] = H[0] + a | 0; H[1] = H[1] + bb | 0; H[2] = H[2] + c | 0; H[3] = H[3] + dd | 0; H[4] = H[4] + e | 0; H[5] = H[5] + f | 0; H[6] = H[6] + g | 0; H[7] = H[7] + h | 0; } return H.map(function (v) { return ('0000000' + (v >>> 0).toString(16)).slice(-8); }).join(''); } function uid(spreadsheetId) { return sha256('tipfair:' + spreadsheetId).slice(0, 32); } return { MONTHS: MONTHS, TYPES: TYPES, METHODS: METHODS, STAFF_TYPES: STAFF_TYPES, LANGS: LANGS, FREE_LIMIT: FREE_LIMIT, GRACE_DAYS: GRACE_DAYS, RECORD_COLS: RECORD_COLS, largestRemainder: largestRemainder, parseHours: parseHours, parsePoints: parsePoints, parseAmount: parseAmount, parseDate: parseDate, ambiguousDates: ambiguousDates, addDays: addDays, addYears: addYears, addMonths: addMonths, monthEnd: monthEnd, daysBetween: daysBetween, d: d, gbDeadline: gbDeadline, ieStatementBy: ieStatementBy, money: money, plain: plain, hundredths: hundredths, tipsTotal: tipsTotal, weight: weight, checkHours: checkHours, split: split, locked: locked, ruleText: ruleText, recordRows: recordRows, fingerprint: fingerprint, readRecord: readRecord, requestRange: requestRange, letterRows: letterRows, parseCsv: parseCsv, detectColumns: detectColumns, mappingKey: mappingKey, tillName: tillName, csvTips: csvTips, dedupe: dedupe, safeText: safeText, licenceState: licenceState, sha256: sha256, uid: uid }; })(); // Statement words in the 6 languages (spec 5.7). The chosen language is printed first, English under it. // {v} is the venue, {c} the contact. Kept short and plain. English "how" lines are built in docs.js; the others use // gs/all (group or everyone, then {m} the method and {d} day by day) and the y* lines, with the group in brackets. var I18N = { English: { title: 'Tips statement', name: 'Name', period: 'Period', to: 'to', paid: 'Paid on', total: 'Tips shared at {v} this period', tronc: 'Paid through a tronc operator', agency: 'Agency', share: 'Your share', how: 'How your share was worked out', taxUK: 'Your share is before any tax and National Insurance, which go through payroll.', ask: 'Questions? Ask {c}.', taxIE: 'This is your written statement of tips and gratuities for this period. Your share is before any tax deducted through payroll.', askNone: 'Questions? Ask your manager.' }, Spanish: { title: 'Resumen de propinas', name: 'Nombre', period: 'Periodo', to: 'a', paid: 'Pagado el', total: 'Propinas repartidas en {v} en este periodo', tronc: 'Pagado a trav\u00e9s de un operador de tronc', agency: 'Agencia', share: 'Tu parte', how: 'C\u00f3mo se calcul\u00f3 tu parte', taxUK: 'Tu parte es antes de impuestos y National Insurance, que se descuentan en la n\u00f3mina.', ask: '\u00bfDudas? Pregunta a {c}.', taxIE: 'Este es tu resumen escrito de propinas de este periodo. Tu parte es antes de los impuestos que se descuentan en la n\u00f3mina.', askNone: '\u00bfDudas? Pregunta a tu responsable.', howWords: {'gs': '{g} reparte el {pct}% de las propinas {m}{d}.', 'all': 'Las propinas se reparten {m}{d}.', 'team': 'equipo', 'day': ', d\u00eda a d\u00eda', 'Hours': 'por horas trabajadas', 'Points': 'por puntos', 'Equal': 'a partes iguales', 'HP': 'por horas trabajadas por puntos', 'yHours': 'Trabajaste {h} de las {H} horas ({g}).', 'yPoints': 'Tienes {pt} de los {P} puntos ({g}).', 'yEqual': 'Personas que reparten: {n}, cont\u00e1ndote a ti.', 'yHP': 'Horas \u00d7 puntos: {h} \u00d7 {pt} = {x} de {HP} ({g}).'} }, Polish: { title: 'Zestawienie napiwk\u00f3w', name: 'Imi\u0119 i nazwisko', period: 'Okres', to: 'do', paid: 'Wyp\u0142acono', total: 'Napiwki podzielone w {v} w tym okresie', tronc: 'Wyp\u0142acone przez operatora troncu', agency: 'Agencja', share: 'Tw\u00f3j udzia\u0142', how: 'Jak obliczono Tw\u00f3j udzia\u0142', taxUK: 'Tw\u00f3j udzia\u0142 jest przed podatkiem i National Insurance, kt\u00f3re s\u0105 potr\u0105cane w wyp\u0142acie.', ask: 'Pytania? Zapytaj: {c}.', taxIE: 'To jest Twoje pisemne zestawienie napiwk\u00f3w za ten okres. Tw\u00f3j udzia\u0142 jest przed podatkiem potr\u0105canym w wyp\u0142acie.', askNone: 'Pytania? Zwr\u00f3\u0107 si\u0119 do kierownictwa.', howWords: {'gs': '{g} dzieli {pct}% napiwk\u00f3w {m}{d}.', 'all': 'Napiwki s\u0105 dzielone {m}{d}.', 'team': 'zesp\u00f3\u0142', 'day': ', dzie\u0144 po dniu', 'Hours': 'wed\u0142ug przepracowanych godzin', 'Points': 'wed\u0142ug punkt\u00f3w', 'Equal': 'po r\u00f3wno', 'HP': 'wed\u0142ug godzin pomno\u017conych przez punkty', 'yHours': 'Twoje godziny: {h} z {H} ({g}).', 'yPoints': 'Twoje punkty: {pt} z {P} ({g}).', 'yEqual': 'Liczba os\u00f3b, kt\u00f3re dziel\u0105 napiwki: {n}, w tym Ty.', 'yHP': 'Godziny \u00d7 punkty: {h} \u00d7 {pt} = {x} z {HP} ({g}).'} }, Portuguese: { title: 'Resumo de gorjetas', name: 'Nome', period: 'Per\u00edodo', to: 'a', paid: 'Pago em', total: 'Gorjetas repartidas em {v} neste per\u00edodo', tronc: 'Pago atrav\u00e9s de um operador de tronc', agency: 'Ag\u00eancia', share: 'A sua parte', how: 'Como foi calculada a sua parte', taxUK: 'A sua parte \u00e9 antes de impostos e National Insurance, que s\u00e3o descontados no sal\u00e1rio.', ask: 'D\u00favidas? Pergunte a {c}.', taxIE: 'Este \u00e9 o seu resumo escrito de gorjetas deste per\u00edodo. A sua parte \u00e9 antes dos impostos descontados no sal\u00e1rio.', askNone: 'D\u00favidas? Pergunte \u00e0 ger\u00eancia.', howWords: {'gs': '{g} reparte {pct}% das gorjetas {m}{d}.', 'all': 'As gorjetas s\u00e3o repartidas {m}{d}.', 'team': 'equipa', 'day': ', dia a dia', 'Hours': 'por horas trabalhadas', 'Points': 'por pontos', 'Equal': 'em partes iguais', 'HP': 'por horas trabalhadas vezes pontos', 'yHours': 'As suas horas: {h} de {H} ({g}).', 'yPoints': 'Os seus pontos: {pt} de {P} ({g}).', 'yEqual': 'Pessoas que repartem: {n}, incluindo voc\u00ea.', 'yHP': 'Horas \u00d7 pontos: {h} \u00d7 {pt} = {x} de {HP} ({g}).'} }, Romanian: { title: 'Situa\u021bia bac\u0219i\u0219urilor', name: 'Nume', period: 'Perioada', to: 'p\u00e2n\u0103 la', paid: 'Pl\u0103tit pe', total: 'Bac\u0219i\u0219uri \u00eemp\u0103r\u021bite la {v} \u00een aceast\u0103 perioad\u0103', tronc: 'Pl\u0103tit printr-un operator tronc', agency: 'Agen\u021bie', share: 'Partea ta', how: 'Cum a fost calculat\u0103 partea ta', taxUK: 'Partea ta este \u00eenainte de impozit \u0219i National Insurance, care se re\u021bin din salariu.', ask: '\u00centreb\u0103ri? \u00centreab\u0103 pe {c}.', taxIE: 'Aceasta este situa\u021bia ta scris\u0103 a bac\u0219i\u0219urilor pentru aceast\u0103 perioad\u0103. Partea ta este \u00eenainte de impozitul re\u021binut din salariu.', askNone: '\u00centreb\u0103ri? \u00centreab\u0103 conducerea.', howWords: {'gs': '{g} \u00eemparte {pct}% din bac\u0219i\u0219uri {m}{d}.', 'all': 'Bac\u0219i\u0219urile se \u00eempart {m}{d}.', 'team': 'echipa', 'day': ', zi de zi', 'Hours': 'dup\u0103 orele lucrate', 'Points': 'dup\u0103 puncte', 'Equal': '\u00een mod egal', 'HP': 'dup\u0103 orele lucrate \u00eenmul\u021bite cu punctele', 'yHours': 'Orele tale: {h} din {H} ({g}).', 'yPoints': 'Punctele tale: {pt} din {P} ({g}).', 'yEqual': 'Persoane care \u00eempart bac\u0219i\u0219urile: {n}, inclusiv tu.', 'yHP': 'Ore \u00d7 puncte: {h} \u00d7 {pt} = {x} din {HP} ({g}).'} }, Italian: { title: 'Riepilogo mance', name: 'Nome', period: 'Periodo', to: 'al', paid: 'Pagato il', total: 'Mance divise da {v} in questo periodo', tronc: 'Pagato tramite un operatore tronc', agency: 'Agenzia', share: 'La tua quota', how: 'Come \u00e8 stata calcolata la tua quota', taxUK: 'La tua quota \u00e8 al lordo di tasse e National Insurance, trattenute in busta paga.', ask: 'Domande? Chiedi a {c}.', taxIE: 'Questo \u00e8 il tuo riepilogo scritto delle mance di questo periodo. La tua quota \u00e8 al lordo delle tasse trattenute in busta paga.', askNone: 'Domande? Chiedi al tuo responsabile.', howWords: {'gs': '{g} divide il {pct}% delle mance {m}{d}.', 'all': 'Le mance sono divise {m}{d}.', 'team': 'squadra', 'day': ', giorno per giorno', 'Hours': 'in base alle ore lavorate', 'Points': 'in base ai punti', 'Equal': 'in parti uguali', 'HP': 'in base alle ore lavorate per i punti', 'yHours': 'Le tue ore: {h} su {H} ({g}).', 'yPoints': 'I tuoi punti: {pt} su {P} ({g}).', 'yEqual': 'Persone che dividono le mance: {n}, te compreso o compresa.', 'yHP': 'Ore \u00d7 punti: {h} \u00d7 {pt} = {x} su {HP} ({g}).'} } }; // Tip Fair printouts and result checks (spec 5.6 to 5.10). Pure: data in, HTML strings out, so tests can scan them. // v = venue context: {country: 'GB'|'NI'|'IE', venue, contact, from, to, payout, groupsOn} var Docs = (function () { 'use strict'; var C = typeof Core !== 'undefined' ? Core : require('./core.js'); var W = typeof I18N !== 'undefined' ? I18N : require('./i18n.js'); function esc(s) { return String(s == null ? '' : s).replace(/[&<>"']/g, function (c) { return { '&': '&', '<': '<', '>': '>', '"': '"', "'": ''' }[c]; }); } function cur(v) { return v.country === 'IE' ? '\u20ac' : '\u00a3'; } function num(x) { return String(Math.round(x * 100) / 100); } function sub(s, v) { return s.replace('{v}', v.venue).replace('{c}', v.contact || 'your manager'); } /* ---------- result checks (5.6) ---------- */ // Returns [{kind: 'ok'|'warn', text}] in the order shown above the Finalise button. function checks(r, v, locked) { var out = [{ kind: 'ok', text: 'Shares add up to ' + C.money(r.Tsplit, cur(v)) + ' exactly.' }]; var month = C.d(v.from, 'm'); if (v.country === 'GB' && r.first) { var D1 = C.gbDeadline(r.first); out.push(v.payout > D1 ? { kind: 'warn', text: 'Tips paid in ' + month + ' must reach staff by ' + C.d(D1, 'long') + '. Your payout date is ' + C.d(v.payout, 'dM') + '.' } : { kind: 'ok', text: 'Payout ' + C.d(v.payout, 'dm') + ' is on time. ' + month + ' tips must reach staff by ' + C.d(D1) + '.' }); } if (v.country === 'IE') out.push({ kind: 'warn', text: 'Give each person their statement by ' + C.d(C.ieStatementBy(v.payout), 'long') + ', within 10 days of payout.' }); r.people.forEach(function (p) { if (p.share <= 0) return; if (v.country === 'IE' && p.type === 'Owner or manager') out.push({ kind: 'warn', text: p.name + ' is an owner or manager. In Ireland they can only share in card tips if they regularly do the same work as the others, and only a fair share.' }); if (v.country === 'GB' && p.type === 'Self-employed') out.push({ kind: 'warn', text: p.name + ' is self-employed. The UK tips law covers workers and agency workers, not the self-employed. You can still include them.' }); }); if (locked) out.push({ kind: 'warn', text: r.sharers + ' people share in this period. Free covers up to 5. Upgrade to see each share.' }); return out; } function savedLine(v) { return C.d(v.from, 'my') + ' is saved in the Record sheet.' + (v.country === 'NI' ? '' : ' Keep it until ' + C.d(C.addYears(v.payout, 3)) + '.'); } /* ---------- statements (5.7) ---------- */ function how(r, p, v) { var g = r.groups.filter(function (x) { return x.name === p.group; })[0], who = v.groupsOn ? g.name : 'Everyone', of = v.groupsOn ? 'the ' + g.name + '\'s' : 'the team\'s'; var on = g.people.filter(function (x) { return x.w > 0; }); var H = on.reduce(function (a, x) { return a + x.h; }, 0) / 100, P = on.reduce(function (a, x) { return a + x.p; }, 0) / 100; var HP = on.reduce(function (a, x) { return a + x.h * x.p; }, 0) / 10000, h = p.h / 100, pt = p.p / 100; var lead = v.groupsOn ? who + ' shares ' + num(g.pct) + '% of the tips, ' : 'Tips are shared '; if (v.groupsOn && g.method === 'Equal') lead = who + ' shares ' + num(g.pct) + '% of the tips '; if (r.days) lead = lead.replace(/, $/, ' ') + 'day by day, '; switch (g.method) { case 'Hours': return [lead + 'by hours worked.', 'You worked ' + num(h) + ' of ' + of + ' ' + num(H) + ' hours.']; case 'Points': return [lead + 'by points.', 'You have ' + num(pt) + ' of ' + of + ' ' + num(P) + ' points.']; case 'Equal': return [lead + 'equally.', 'You are one of ' + on.length + ' people sharing.']; default: return [lead + 'by hours worked times points.', 'You worked ' + num(h) + ' hours at ' + num(pt) + ' points: ' + num(h * pt) + ' of ' + of + ' ' + num(HP) + '.']; } } // The same two lines in the statement's language (i18n howWords). Returns null for English. function howIn(r, p, v, lang) { var t = lang !== 'English' && W[lang] && W[lang].howWords; if (!t) return null; var g = r.groups.filter(function (x) { return x.name === p.group; })[0], on = g.people.filter(function (x) { return x.w > 0; }); var H = on.reduce(function (a, x) { return a + x.h; }, 0) / 100, P = on.reduce(function (a, x) { return a + x.p; }, 0) / 100; var HP = on.reduce(function (a, x) { return a + x.h * x.p; }, 0) / 10000, h = p.h / 100, pt = p.p / 100; var k = g.method === 'Hours \u00d7 points' ? 'HP' : g.method; var f = { g: v.groupsOn ? g.name : t.team, pct: num(g.pct), m: t[k], d: r.days ? t.day : '', h: num(h), H: num(H), pt: num(pt), P: num(P), x: num(h * pt), HP: num(HP), n: on.length }; function fill(s) { return s.replace(/\{(\w+)\}/g, function (a, x) { return f[x]; }); } return [fill(v.groupsOn ? t.gs : t.all), fill(t['y' + k])]; } // One A4 statement. Only this person's share and the venue's total: never anyone else's amount. function statement(r, p, v, staff) { var lang = W[staff && staff.lang] ? staff.lang : 'English', w = W[lang], en = lang === 'English' ? null : W.English; function L(k) { return esc(sub(w[k], v)) + (en ? '' + esc(sub(en[k], v)) + '' : ''); } var c = cur(v), foot = v.country === 'IE' ? ['taxIE'] : ['taxUK', v.contact ? 'ask' : 'askNone']; var agency = staff && staff.type === 'Agency worker' && staff.agency ? '
' + L('agency') + '
' + esc(staff.agency) + '
' : ''; return '
' + '
' + esc(v.venue) + '' + L('title') + '
' + '
' + L('name') + '
' + esc(p.full) + '
' + agency + '
' + L('period') + '
' + C.d(v.from, 'long') + (lang === 'English' ? ' to ' : ' \u2013 ') + C.d(v.to, 'long') + '
' + '
' + L('paid') + '
' + C.d(v.payout, 'long') + '
' + '
' + L('total') + '
' + C.money(r.T, c) + '
' + '
' + L('tronc') + '
' + C.money(r.R, c) + '
' + '
' + L('share') + '' + C.money(p.share, c) + '
' + '

' + L('how') + '

' + howLines(r, p, v, lang) + '
' + '
' + foot.map(function (k) { return '

' + L(k) + '

'; }).join('') + '
'; } function howLines(r, p, v, lang) { var en = how(r, p, v), loc = howIn(r, p, v, lang); return en.map(function (x, i) { return '

' + (loc ? esc(loc[i]) + '' + esc(x) + '' : esc(x)) + '

'; }).join(''); } function statements(r, v, staffByName, only) { return r.people.filter(function (p) { return p.share > 0 && (!only || p.name === only); }) .map(function (p) { return statement(r, p, v, staffByName[p.name]); }).join(''); } /* ---------- reply letter (5.8) ---------- */ // L = Core.letterRows(...). req = {worker (full name), on, m1, m2} function letter(L, req, v) { var c = cur(v), ni = v.country === 'NI'; function mo(x) { return C.money(x, c); } var body = L.rows.map(function (x) { return '' + C.d(x.month + '-01', 'mY') + '' + mo(x.total) + '' + mo(x.toWorkers) + '' + mo(x.tronc) + '' + mo(x.share) + ''; }).join(''); var range = C.d(req.m1 + '-01', 'my') + ' to ' + C.d(req.m2 + '-01', 'my'); return '

' + esc(v.venue) + '

' + C.d(req.on, 'long') + '

' + '

Dear ' + esc(req.worker) + ',

' + (ni ? '

Tips summary

Here are the tips from ' + range + '.

' : '

Your request for tips records

You asked on ' + C.d(req.on, 'long') + ' for the records of tips from ' + range + '. Here they are.

') + '' + body + '
Month paidTips at ' + esc(v.venue) + 'To workersThrough troncYour share
Total' + mo(L.sum.total) + '' + mo(L.sum.toWorkers) + '' + mo(L.sum.tronc) + '' + mo(L.sum.share) + '
' + (L.sum.total ? '' : '

No tips were paid in these months.

') + (L.later || []).map(function (b) { var span = b.from.slice(0, 7) === b.to.slice(0, 7) ? C.d(b.to, 'my') : C.d(b.from, 'long') + ' to ' + C.d(b.to, 'long'); return '

Tips for ' + span + ' are paid on ' + C.d(b.paid, 'long') + ', so they will appear under ' + C.d(b.paid, 'my') + '.

'; }).join('') + '

These are the total tips received at ' + esc(v.venue) + ' and your own share. We don\'t share other people\'s amounts.

' + '

' + esc(v.contact) + '

'; } /* ---------- policy (5.9, s27I) ---------- */ var ASK = { no: 'No. Tips are up to the customer.', encourage: 'We encourage customers to tip, but it is always up to them.', service: 'We add a service charge to the bill. Customers can ask us to remove it.' }; var METHOD_WORDS = { 'Equal': 'equally', 'Hours': 'by hours worked', 'Points': 'by points', 'Hours \u00d7 points': 'by hours worked times points' }; function ruleWords(o, pointsMean) { var pts = ''; var s = o.groupsOn ? o.groups.map(function (g, i) { return (i ? g.name + ' staff share ' + num(g.pct) + '% ' : g.name + ' staff share ' + num(g.pct) + '% of tips ') + METHOD_WORDS[g.method] + '.'; }).join(' ') : 'Tips are shared between all staff ' + METHOD_WORDS[o.method] + '.'; var usesPoints = (o.groupsOn ? o.groups.map(function (g) { return g.method; }) : [o.method]).some(function (m) { return /Points|points/.test(m); }); if (usesPoints && pointsMean) pts = ' Points stand for ' + pointsMean.replace(/\.$/, '') + '.'; return s + pts + (o.pool === 'day' ? ' Tips are shared day by day, among the staff who worked that day.' : ''); } // a = form answers: {ask, ways:[...], who, when, consultOn, consultHow, said, contact, pointsMean, today} function policy(o, a, v) { var ways = (a.ways && a.ways.length ? a.ways : ['Card', 'Cash']).map(function (x) { return x.toLowerCase(); }); var waysTxt = ways.length > 1 ? ways.slice(0, -1).join(', ') + ' or ' + ways[ways.length - 1] : ways[0]; var S = [ ['What counts as a tip here', 'Any tip, gratuity or service charge a customer pays by ' + waysTxt + '.'], ['Do we ask customers to tip?', ASK[a.ask] || ASK.no], ['Who shares', a.who + '.'], ['How shares are worked out', ruleWords(o, a.pointsMean)], ['When tips are paid', a.when + '.'], ['Card fees', '100% of tips go to staff. We keep nothing for card fees.'] ]; if (o.tronc > 0 || a.tronc) S.push(['Tronc', 'Part of the tips is paid through a tronc operator, who decides how that part is shared.']); S.push(['Your records', 'You can ask ' + a.contact + ' for the records of your tips. We\'ll reply within 4 weeks.']); S.push(['How we asked staff', 'We asked staff for their views by ' + (a.consultHow || 'Meeting').toLowerCase() + ' on ' + C.d(a.consultOn, 'long') + '. What staff said: ' + (a.said || 'no comments.')]); S.push(['Dates', 'This policy: ' + C.d(a.today, 'long') + '. Review due by ' + C.d(C.addYears(a.today, 3), 'long') + '.']); S.push(['Who to ask', a.contact]); return '

How we share tips at ' + esc(v.venue) + '

' + S.map(function (x) { return '

' + esc(x[0]) + '

' + esc(x[1]) + '

'; }).join('') + '

Made with Tip Fair. This is a template to help you write your own policy, not legal advice.

'; } /* ---------- notice (5.10, Ireland) ---------- */ var LAW = 'The law requires that tips and gratuities paid by card or other electronic means are distributed to employees.'; // a = {card: true|false, how, pct, service: true|false, cash} function noticeLines(a) { var L = []; if (a.card) L.push('We share ' + (a.pct || '100%') + ' of card tips with our staff, ' + a.how.charAt(0).toLowerCase() + a.how.slice(1) + '.'); else L.push('We don\'t take tips by card.'); L.push(a.cash === 'Shared the same way' ? 'Cash tips are shared the same way.' : 'Cash tips are kept by the person who receives them.'); L.push(a.service ? 'Our service charge is shared with staff in the same way as card tips.' : 'We don\'t add a service charge.'); return L; } function notice(a, v) { var L = noticeLines(a); function page(cls) { return '

' + esc(v.venue) + '

Tips and gratuities

' + '

' + esc(L[0]) + '

' + L.slice(1).map(function (x) { return '

' + esc(x) + '

'; }).join('') + '

' + esc(LAW) + '

'; } return page('tf-a4') + page('tf-a5'); } function noticeText(a, v) { return v.venue + ': tips and gratuities\n\n' + noticeLines(a).join('\n') + '\n\n' + LAW; } return { esc: esc, checks: checks, savedLine: savedLine, how: how, statement: statement, statements: statements, letter: letter, ruleWords: ruleWords, policy: policy, notice: notice, noticeText: noticeText, LAW: LAW }; })(); /** * Tip Fair: shares tips fairly and keeps the records the tips laws ask for. * Sheets: Staff, Tips, Hours, Record (+ Shares when written). Settings, mappings and the licence in Document Properties. * The maths is in core.js, the documents in docs.js. Nothing here sends data out of the spreadsheet. */ function onOpen(e) { SpreadsheetApp.getUi().createAddonMenu().addItem('Open Tip Fair', 'showSidebar').addToUi(); } function onInstall(e) { onOpen(e); } function showSidebar() { SpreadsheetApp.getUi().showSidebar(HtmlService.createTemplate(HTML_FILES_['Sidebar']).evaluate().setTitle('Tip Fair')); } function include(name) { return HTML_FILES_[name]; } function today_() { return Utilities.formatDate(new Date(), ss_().getSpreadsheetTimeZone() || 'Europe/London', 'yyyy-MM-dd'); } function now_() { return Utilities.formatDate(new Date(), ss_().getSpreadsheetTimeZone() || 'Europe/London', "yyyy-MM-dd HH:mm"); } function pro_() { return licenceState_(today_()).pro; } /** 5.1 */ function setup(country, venue) { if (['GB', 'NI', 'IE'].indexOf(country) < 0) throw new Error('Choose where the venue is.'); saveSettings_({ country: country, venue: String(venue || '').trim().slice(0, 120) || 'My venue' }); setupSheets_(country); return getState(); } function saveSettings(patch) { var keep = {}; ['venue', 'country', 'contact'].forEach(function (k) { if (patch[k] != null) keep[k] = String(patch[k]).trim().slice(0, 120); }); if (keep.country && ['GB', 'NI', 'IE'].indexOf(keep.country) < 0) delete keep.country; var s = saveSettings_(keep); if (keep.country) setNotes_(s.country); return getState(); } function openSheet(name) { var sh = ss_().getSheetByName(name); if (sh) ss_().setActiveSheet(sh); } /** Default period: the month of the latest tip, or last month. Default payout: the 28th of the month after. */ function defaults_(tips, today) { var last = tips.reduce(function (a, t) { return t.date > a ? t.date : a; }, ''); var ym = last ? last.slice(0, 7) : Core.addMonths(today.slice(0, 7), -1); return { from: ym + '-01', to: Core.monthEnd(ym + '-01'), payout: Core.addMonths(ym, 1) + '-28' }; } function groupsFrom_(staff, saved) { var names = []; staff.forEach(function (s) { if (names.indexOf(s.group) < 0) names.push(s.group); }); var old = {}; (saved || []).forEach(function (g) { old[g.name] = g; }); if (names.every(function (n) { return old[n]; }) && (saved || []).length === names.length) return saved; var base = Math.floor(10000 / Math.max(names.length, 1)), extra = 10000 - base * names.length; return names.map(function (n, i) { return old[n] || { name: n, pct: (base + (i === 0 ? extra : 0)) / 100, method: 'Hours' }; }); } /** Everything the home screen (5.2) needs. Counts and totals only. */ function getState() { var s = settings_(), today = today_(), lic = licenceState_(today); if (!isSetUp_()) return { setUp: false, licence: lic, today: today }; var st = readStaff_(), tp = readTips_(), hr = readHours_(); var d = defaults_(tp.tips, today); var o = s.work && s.work.from ? s.work : {}; var from = o.from || d.from, to = o.to || d.to; var tt = Core.tipsTotal(tp.tips, from, to); var hrs = hr.hours.filter(function (r) { return !r.date || (r.date >= from && r.date <= to); }); var hTotal = hrs.reduce(function (a, r) { return a + r.hours; }, 0); var id = from + '_' + to, blocks = Core.readRecord(recordValues_()); var fin = blocks.filter(function (b) { return b.id === id && !b.replaced; })[0]; return { setUp: true, today: today, licence: lic, settings: { country: s.country, venue: s.venue, contact: s.contact || '' }, staff: st.staff.map(function (x) { return { name: x.name, group: x.group, points: x.points, type: x.type }; }), badPoints: st.badPoints, tips: { total: tt.total, count: tt.count, bad: tp.bad, latest: d.from }, hours: { total: hTotal, bad: hr.bad }, work: { from: from, to: to, payout: o.payout || d.payout, groupsOn: o.groupsOn != null ? o.groupsOn : groupsFrom_(st.staff, []).length > 1, groups: groupsFrom_(st.staff, o.groups), method: o.method || 'Hours', pool: o.pool || 'period', tronc: o.tronc || 0 }, finalised: fin ? { at: fin.at, changed: fin.changed } : null, records: blocks.filter(function (b) { return !b.replaced; }).length }; } /** 5.4 */ function checkHours(from, to) { var st = readStaff_(), hr = readHours_(); var r = Core.checkHours(st.staff, hr.hours, from, to, Core.d(from, 'm')); r.bad = hr.bad; return r; } /** 5.3: the sidebar parses the CSV and sends the tips it found. They are checked again here. */ function importTips(tips, mapping) { var clean = []; (tips || []).forEach(function (t) { var date = Core.parseDate(t.date), amount = typeof t.amount === 'number' ? Math.round(t.amount) : Core.parseAmount(t.amount, false); if (!date || amount == null) return; clean.push({ date: date, amount: amount, type: Core.TYPES.indexOf(t.type) > -1 ? t.type : 'Card', vat: Math.round(Number(t.vat) || 0), source: String(t.source || '').slice(0, 60), ref: String(t.ref || '').slice(0, 80) }); }); if (mapping && mapping.key && /^map:/.test(mapping.key)) { PropertiesService.getDocumentProperties().setProperty(mapping.key, JSON.stringify({ map: mapping.map, source: String(mapping.source || '').slice(0, 60), order: mapping.order || '' })); } return appendTips_(clean); } function getMapping(key) { if (!/^map:/.test(key)) return null; var raw = PropertiesService.getDocumentProperties().getProperty(key); return raw ? JSON.parse(raw) : null; } /** The split settings from the sidebar, kept so the next month starts from them. */ function cleanWork_(w) { var o = { from: Core.parseDate(w.from), to: Core.parseDate(w.to), payout: Core.parseDate(w.payout), groupsOn: !!w.groupsOn, method: Core.METHODS.indexOf(w.method) > -1 ? w.method : 'Hours', pool: w.pool === 'day' ? 'day' : 'period', tronc: Math.max(0, Math.round(Number(w.tronc) || 0)), groups: (w.groups || []).map(function (g) { return { name: String(g.name), pct: Number(g.pct) || 0, method: Core.METHODS.indexOf(g.method) > -1 ? g.method : 'Hours' }; }) }; if (!o.from || !o.to || o.from > o.to) throw new Error('Choose the period again.'); if (!o.payout) throw new Error('Choose the payout date.'); return o; } function compute_(w) { var o = cleanWork_(w), st = readStaff_(); saveSettings_({ work: o }); o.tips = readTips_().tips; o.staff = st.staff; o.hours = readHours_().hours; return { o: o, r: Core.split(o), staff: st.staff }; } function view_(o) { var s = settings_(); return { country: s.country, venue: s.venue, contact: s.contact || '', from: o.from, to: o.to, payout: o.payout, groupsOn: o.groupsOn }; } /** 5.5 \u2192 5.6. Over 5 people without Pro, only group totals come back (6.10). */ function work(w) { var c = compute_(w), r = c.r; if (!r.ok) return { ok: false, error: r.error }; var lock = Core.locked(r, pro_()), v = view_(c.o); var id = c.o.from + '_' + c.o.to, fin = Core.readRecord(recordValues_()).filter(function (b) { return b.id === id && !b.replaced; })[0]; return { ok: true, locked: lock, T: r.T, R: r.R, Tsplit: r.Tsplit, byType: r.byType, count: r.count, sharers: r.sharers, groups: r.groups.map(function (g) { return { name: g.name, pct: g.pct, method: g.method, pot: g.pot, n: g.people.filter(function (p) { return p.share > 0; }).length, people: lock ? [] : g.people.map(function (p) { return { name: p.full, h: p.h, p: p.p, share: p.share }; }) }; }), checks: Docs.checks(r, v, lock), saved: fin ? Docs.savedLine(v) : null, finalisedAt: fin ? fin.at : null }; } /** 6.8 */ function finalise(w, note) { var c = compute_(w), r = c.r; if (!r.ok) return { ok: false, error: r.error }; if (Core.locked(r, pro_())) return { ok: false, code: 'PRO' }; var rows = Core.recordRows(r, c.o, c.o.payout, now_()); if (note) rows[0][20] = String(note).slice(0, 200); writeRecord_(rows, c.o.from + '_' + c.o.to, today_()); return { ok: true, saved: Docs.savedLine(view_(c.o)) }; } function writeShares(w) { var c = compute_(w), r = c.r; if (!r.ok) return { ok: false, error: r.error }; if (Core.locked(r, pro_())) return { ok: false, code: 'PRO' }; writeSharesSheet_(r, settings_().venue + ': tip shares, ' + Core.d(c.o.from, 'long') + ' to ' + Core.d(c.o.to, 'long')); return { ok: true }; } /** 5.8: finalised periods and the names in them, for the Records screen. */ function getRecords() { var blocks = Core.readRecord(recordValues_()), names = []; blocks.forEach(function (b) { if (!b.replaced) b.rows.forEach(function (r) { if (names.indexOf(r[12]) < 0) names.push(r[12]); }); }); return { blocks: blocks.map(function (b) { return { id: b.id, from: b.from, to: b.to, paid: b.paid, total: b.total, at: b.at, people: b.rows.length, changed: b.changed, replaced: b.replaced, status: b.status }; }).reverse(), names: names.sort() }; } function checkRequest(on, m1, m2) { return Core.requestRange(on, m1, m2); } /** * Documents open in a print dialog (7). 'one' is free while the period isn't locked; the rest need Pro (6.10). * kind: 'one' {w, name} | 'all' {w} | 'letter' {worker, on, m1, m2} | 'policy' {w, a} | 'notice' {a} */ function print(kind, x) { var s = settings_(), html, title; if (kind !== 'one' && !pro_()) return { ok: false, code: 'PRO' }; if (kind === 'one' || kind === 'all') { var c = compute_(x.w), r = c.r; if (!r.ok) return { ok: false, error: r.error }; if (Core.locked(r, pro_())) return { ok: false, code: 'PRO' }; var by = {}; c.staff.forEach(function (p) { by[p.name] = p; }); var only = kind === 'one' ? (r.people.filter(function (p) { return p.full === x.name; })[0] || {}).name : null; if (kind === 'one' && !only) return { ok: false, error: 'Choose a name from the list.' }; html = Docs.statements(r, view_(c.o), by, only); title = kind === 'one' ? 'Statement' : 'Statements'; } else if (kind === 'letter') { var q = Core.requestRange(x.on, x.m1, x.m2); if (q.errors.length) return { ok: false, error: q.errors[0].text }; var L = Core.letterRows(Core.readRecord(recordValues_()), x.worker, x.m1, x.m2); html = Docs.letter(L, x, { country: s.country, venue: s.venue, contact: s.contact || '' }); title = 'Reply letter'; } else if (kind === 'policy') { var o = cleanWork_(x.w); x.a.today = today_(); html = Docs.policy(o, x.a, { venue: s.venue }); title = 'Tipping policy'; } else if (kind === 'notice') { html = Docs.notice(x.a, { venue: s.venue }); title = 'Tips notice'; } else throw new Error('Unknown document'); var t = HtmlService.createTemplate(HTML_FILES_['Print']); t.data = html; SpreadsheetApp.getUi().showModalDialog(t.evaluate().setWidth(860).setHeight(640), title); return { ok: true }; } /** 5.10: the notice as text to copy to a website. */ function noticeText(a) { if (!pro_()) return { ok: false, code: 'PRO' }; return { ok: true, text: Docs.noticeText(a, { venue: settings_().venue }) }; } /** * Tip Fair: reading and writing the sheets. One getValues() and one setValues() per operation. * Every text value is written through Core.safeText, so a name can't become a formula. * Logs never hold row data. */ var SHEETS = { staff: 'Staff', tips: 'Tips', hours: 'Hours', record: 'Record', shares: 'Shares' }; var HEADERS = { Staff: ['Name', 'Group', 'Points', 'Type', 'Agency', 'Statement language', 'Notes'], Tips: ['Date', 'Amount', 'Type', 'VAT part', 'Source', 'Reference'], Hours: ['Date', 'Name', 'Hours'], Record: Core.RECORD_COLS, Shares: ['Name', 'Group', 'Hours', 'Points', 'Share'] }; function ss_() { return SpreadsheetApp.getActiveSpreadsheet(); } /** Settings live in Document Properties, so they travel with the spreadsheet. */ function settings_() { var raw = PropertiesService.getDocumentProperties().getProperty('tf'); return raw ? JSON.parse(raw) : {}; } function saveSettings_(patch) { var s = settings_(); Object.keys(patch).forEach(function (k) { s[k] = patch[k]; }); PropertiesService.getDocumentProperties().setProperty('tf', JSON.stringify(s)); return s; } function isSetUp_() { var ss = ss_(); return !!settings_().country && ['Staff', 'Tips', 'Hours', 'Record'].every(function (n) { return !!ss.getSheetByName(n); }); } /** 5.1: adds the four sheets. Safe to run again: sheets that exist keep their data. */ function setupSheets_(country) { var ss = ss_(), first = null; ['Staff', 'Tips', 'Hours', 'Record'].forEach(function (name) { var sh = ss.getSheetByName(name); if (!sh) { sh = ss.insertSheet(name); sh.getRange(1, 1, 1, HEADERS[name].length).setValues([HEADERS[name]]).setFontWeight('bold'); sh.setFrozenRows(1); if (name === 'Record') { // Plain text, so amounts and dates read back exactly as written and the fingerprint holds. sh.getRange(1, 1, sh.getMaxRows(), HEADERS.Record.length).setNumberFormat('@'); sh.protect().setWarningOnly(true).setDescription('Written by Tip Fair'); } if (name === 'Staff') { sh.getRange(2, 4, 500, 1).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(Core.STAFF_TYPES, true).setAllowInvalid(true).build()); sh.getRange(2, 6, 500, 1).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(Core.LANGS, true).setAllowInvalid(true).build()); } if (name === 'Tips') sh.getRange(2, 3, 5000, 1).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(Core.TYPES, true).setAllowInvalid(true).build()); if (name === 'Hours') sh.getRange(2, 3, 5000, 1).setNumberFormat('0.00'); if (!first) first = sh; } }); setNotes_(country); if (first) ss.setActiveSheet(ss.getSheetByName('Staff')); } /** Header notes (4). They follow the country, so changing it in Settings updates them. */ function setNotes_(country) { var ss = ss_(), tips = ss.getSheetByName('Tips'), rec = ss.getSheetByName('Record'); if (tips) { tips.getRange(1, 2).setNote('What the customer paid as a tip, before any card fees. Don\'t take card fees off.'); tips.getRange(1, 4).setNote('Only if this amount includes VAT, such as some service charges. Leave blank otherwise.'); tips.getRange(1, 3).setNote(country === 'IE' ? 'In Ireland a service charge counts as an electronic tip and is shared like one.' : ''); } if (rec) rec.getRange(1, 1).setNote(country === 'NI' ? 'Written by Tip Fair.' : 'Written by Tip Fair. Keep for 3 years from each payout.'); } function rows_(name) { var sh = ss_().getSheetByName(name); if (!sh) return []; var n = sh.getLastRow() - 1, c = HEADERS[name].length; return n > 0 ? sh.getRange(2, 1, n, c).getValues() : []; } function str_(v) { return v == null ? '' : String(v).trim(); } /** Staff, from the Staff sheet. Name is used everywhere, so Hours must match it. */ function readStaff_() { var out = [], bad = [], seen = {}; rows_('Staff').forEach(function (r) { var name = str_(r[0]); if (!name || seen[name]) return; seen[name] = 1; var p = Core.parsePoints(r[2]); if (p == null) { bad.push(name); p = 100; } var type = str_(r[3]), lang = str_(r[5]); out.push({ name: name, full: name, group: str_(r[1]) || 'Staff', points: p, type: Core.STAFF_TYPES.indexOf(type) > -1 ? type : 'Worker', agency: str_(r[4]), lang: Core.LANGS.indexOf(lang) > -1 ? lang : 'English' }); }); return { staff: out, badPoints: bad }; } function readTips_() { var out = [], bad = 0; rows_('Tips').forEach(function (r) { if (r[0] === '' && r[1] === '') return; var date = Core.parseDate(r[0]), amount = Core.parseAmount(r[1], false), vat = r[3] === '' ? 0 : Core.parseAmount(r[3], false); if (!date || amount == null || vat == null) { bad++; return; } var type = str_(r[2]); out.push({ date: date, amount: amount, type: Core.TYPES.indexOf(type) > -1 ? type : 'Card', vat: vat, source: str_(r[4]), ref: str_(r[5]) }); }); return { tips: out, bad: bad }; } function readHours_() { var out = [], bad = 0; rows_('Hours').forEach(function (r) { var name = str_(r[1]); if (!name) return; var h = Core.parseHours(r[2]), date = r[0] === '' ? null : Core.parseDate(r[0]); if (h == null || (r[0] !== '' && !date)) { bad++; return; } out.push({ date: date, name: name, hours: h }); }); return { hours: out, bad: bad }; } /** 5.3: appends imported tips under the last row, skipping ones already there. */ function appendTips_(incoming) { var have = readTips_().tips, d = Core.dedupe(have, incoming); if (d.tips.length) { var sh = ss_().getSheetByName('Tips'); var vals = d.tips.map(function (t) { return [t.date, Core.plain(t.amount), t.type || 'Card', t.vat ? Core.plain(t.vat) : '', Core.safeText(t.source), Core.safeText(t.ref)]; }); var range = sh.getRange(sh.getLastRow() + 1, 1, vals.length, 6); range.setValues(vals.map(function (r) { return [toDate_(r[0]), Number(r[1]), r[2], r[3] === '' ? '' : Number(r[3]), r[4], r[5]]; })); sh.getRange(range.getRow(), 1, vals.length, 1).setNumberFormat('d mmm yyyy'); sh.getRange(range.getRow(), 2, vals.length, 1).setNumberFormat('0.00'); } return { added: d.tips.length, skipped: d.skipped }; } function toDate_(iso) { return new Date(+iso.slice(0, 4), +iso.slice(5, 7) - 1, +iso.slice(8, 10)); } /** The Record sheet as text, from row 2. */ function recordValues_() { var sh = ss_().getSheetByName('Record'); if (!sh) return []; var n = sh.getLastRow() - 1; return n > 0 ? sh.getRange(2, 1, n, HEADERS.Record.length).getDisplayValues() : []; } /** 6.8: adds a finalised block. An earlier block for the same period is marked Replaced, never deleted. */ function writeRecord_(rows, id, today) { var sh = ss_().getSheetByName('Record'), old = recordValues_(), stamp = 'Replaced on ' + Core.d(today); var all = old.map(function (r) { var x = r.slice(); if (x[0] === id && !/^Replaced/.test(x[21])) x[21] = stamp; return x; }).concat(rows); all = all.map(function (r) { return r.map(Core.safeText); }); sh.getRange(2, 1, all.length, HEADERS.Record.length).setNumberFormat('@').setValues(all); } /** "Write shares to sheet": a Shares sheet for payroll, replaced each time. */ function writeSharesSheet_(r, title) { var ss = ss_(), sh = ss.getSheetByName('Shares') || ss.insertSheet('Shares'); sh.clearContents(); var vals = [[Core.safeText(title), '', '', '', ''], HEADERS.Shares]; r.people.forEach(function (p) { vals.push([Core.safeText(p.full), Core.safeText(p.group), Core.hundredths(p.h), Core.hundredths(p.p), p.share / 100]); }); sh.getRange(1, 1, vals.length, 5).setValues(vals); sh.getRange(1, 1, 2, 5).setFontWeight('bold'); sh.getRange(3, 5, Math.max(r.people.length, 1), 1).setNumberFormat('0.00'); sh.setFrozenRows(2); ss.setActiveSheet(sh); } /** * Tip Fair licence (6.11). The sidebar talks to Freemius itself, so the add-on needs no * external_request scope. This file keeps the result in Document Properties and decides Pro from it. * Only the uid, the key and the title "Tip Fair" ever leave the sheet. */ /** Freemius product, made at launch in the Facilo store (21741). */ var LICENCE = { productId: '44992', apiBase: 'https://api.freemius.com/v1', title: 'Tip Fair' }; function lic_() { var raw = PropertiesService.getDocumentProperties().getProperty('tfLicence'); return raw ? JSON.parse(raw) : {}; } function saveLic_(l) { PropertiesService.getDocumentProperties().setProperty('tfLicence', JSON.stringify(l)); } /** What the rest of the add-on asks: is Pro on today? */ function licenceState_(today) { var l = lic_(), st; if (!l.key) return { pro: false, status: 'none', hasKey: false, productId: LICENCE.productId }; if (l.status === 'ok') { var expired = l.expires && l.expires < today, left = Core.GRACE_DAYS - Core.daysBetween(l.lastGood || today, today); st = { pro: !expired && (l.lastFail ? left > 0 : true), status: expired ? 'expired' : 'ok', expires: l.expires || null, left: Math.max(0, left) }; } else st = { pro: false, status: l.status || 'wrong', expires: l.expires || null }; st.hasKey = true; st.keyEnd = l.key.slice(-4); st.needsCheck = l.status === 'ok' && (!l.lastCheck || l.lastCheck < today); st.productId = LICENCE.productId; return st; } /** What the sidebar sends to Freemius. */ function licenceRequest(key) { var l = lic_(); return { ok: !!LICENCE.productId, uid: Core.uid(ss_().getId()), key: String(key || l.key || '').trim(), installId: l.installId || '', productId: LICENCE.productId, apiBase: LICENCE.apiBase, title: LICENCE.title }; } /** * Stores what Freemius said. result.status: 'ok' | 'wrong' | 'used' | 'expired' | 'cancelled' | 'offline'. * Offline keeps Pro for 14 days from the last good check (Core.GRACE_DAYS). */ function recordLicence(result) { var l = lic_(), today = today_(); if (result.status === 'ok') { l = { key: result.key || l.key, installId: String(result.installId || l.installId || ''), status: 'ok', expires: result.expires ? String(result.expires).slice(0, 10) : null, lastGood: today, lastCheck: today }; } else if (result.status === 'offline') { if (l.status === 'ok') { l.lastFail = today; l.lastCheck = today; } } else if (result.status === 'expired' || result.status === 'cancelled') { l.status = result.status; l.lastCheck = today; if (result.expires) l.expires = String(result.expires).slice(0, 10); } else { // A wrong key typed over a working one doesn't switch Pro off. if (l.key && l.status === 'ok' && result.key && result.key !== l.key) return { pro: licenceState_(today).pro, status: result.status, hasKey: true }; l = { key: result.key || l.key, installId: l.installId || '', status: result.status || 'wrong', expires: l.expires || null, lastCheck: today }; } saveLic_(l); return licenceState_(today); } var HTML_FILES_ = {"Sidebar":"\n\n\n
\n\n\n\n","Print":"\n\n\n\n
Check it, then print or save as PDF.
\n
\n\n","styles":"\n","core":""};