gad spreadsheet

[jay-meister]

Get payments into specified format for GAD

Requirements:

Gift Aid Spreadsheet

Most of our members sign a ‘Gift Aid’ declaration – which means that we can claim an additional 22% from Inland Revenue on whatever subscription + donation they have made. We generally make the claim (for about £5,000) once a year – but could do it more or less frequently. We make the claim on the HMRC website and the claim has to have a spreadsheet attached listing all the payments for which we are making the claim. The attached Excel spreadsheet produced by our old IT system gives an example. For each individual donor the spreadsheet shows :  title, using a maximum of 4 characters per line (Note we have a few members whose title – such as Admiral – has to be arbitrarily curtated !)  first name (or first initial) and last name, using a maximum of 35 characters per line  house name or number  postcode, using capital letters and include a space, for example, S19 2BD  Blank column for aggregated donations (we just list the individual donations)  Blank column for sponsored events (we don’t organise the walks, runs, bike rides etc which some charities do)  donation date, using the format DD/MM/YY  donation amount, without using £ signs and showing amounts to 2 decimal places, eg 200.00 not £200 Notes The administrator needs to be able to setup the start and end dates for the period covered by the claim (it will normally be from 1 st April to 30 th March next year) The spreadsheet contains an entry for every subscription charged and/or donation charged by a member during the specified period, providing that member’s Gift Aid is set to TRUE and provided that the member status is ACTIVE. Charges for Events are not treated as eligible as a donation for Gift Aid We don’t make the claim until 6 months after the end of the financial year (which is 30 th March) so we can safely assume that the charges have been matched by payments – because any non-paying members will have been deactivated after 120 days. The entries in the spreadsheet should be in surname alphabetic sequence

[jay-meister]

  var fs = require('fs')
    var query = `
    select
    payments.date, payments.amount,
    members.title, members.first_name, members.last_name, members.initials,
    members.address1, members.address2, members.address3, members.address4,
    members.county, members.postcode
    from payments
    left outer join members
    on payments.member = members.id
    where members.activation_status = 'activated'
    and members.gift_aid_signed = true
    and payments.category in ('donation', 'subscription')
    and members.membership_type in ('life-single', 'life-double', 'annual-single', 'annual-double', 'annual-family')
    and payments.date between '2016-04-01' and '2017-03-31'
    order by members.last_name
    ;`
    Members
      .query(query, null, function (err, records) {

        var write_res = fs.writeFileSync(__dirname + '/members.json', JSON.stringify(records, null, 2))
        console.log(write_res);
        return res.json(records[0])
      })

[jay-meister]

And formatting functionality:

var members = require('./members.json')

const currencyFormatter = require('currency-formatter');

const get_address = (list) => {
  const without_nulls = list.filter(item => item)
  return without_nulls.join(', ')
}

const get_full_name = (first, initials, last) => {
  var first_name = first || initials
  var full = first_name ? first_name + ' ' + last : last
  return full.slice(0, 35)
}

var format = (o) => {
  return {
    "Title": o.title ? o.title.slice(0, 4) : '',
    "Full name": get_full_name(o.first_name, o.initials, o.last_name),
    "House name or number": get_address([o.address1, o.address2, o.address3, o.address4, o.county]),
    "Postcode": o.postcode.toUpperCase(),
    "Aggregtd donation": '',
    "Sponsered event":  '',
    "Date paid": o.date.split('T')[0].split('-').reverse().join('/'),
    "Total payment": currencyFormatter.format(o.amount/100, { code: 'GBP' }).slice(1)
  }
}

var fields =
  [ "Title"
  , "Full name"
  , "House name or number"
  , "Postcode"
  , "Aggregtd donation"
  , "Sponsered event"
  , "Date paid"
  , "Total payment"
  ]

var json2csv = require('json2csv');
var fs = require('fs');
var data = members.map(format)
var csv = json2csv({ data, fields });

fs.writeFile('./gad.csv', csv, function(err) {
  if (err) throw err
  console.log('file saved')
})