Hack to import SEPA mandates from a CSV file to the Crédit Agricole bank. Fun times...

import-sepa-mandates-to-ca.rb

#encoding: utf-8

# This HACK imports SEPA Mandates from a CSV file to
# Crédit Agricole SEPA System via their HTML API. :)
#
# Good luck with this!
#

require 'mechanize'
require 'virtus'
require 'csv'
require 'ruby-progressbar'

RUN = -> do
  ImportSepaMandatesCa::Runner.new.call
end

module ImportSepaMandatesCa
  class Runner
    def pb
      @pb ||= ProgressBar.create(total: accounts.size, format: '%w%i %c/%C %e')
    end

    def call
      accounts.each do |account|
        if exists?(account.mandate_reference)
          pb.log "Account #{account.mandate_reference} exists... Skipping"
        else
          pb.log "Creating #{account.mandate_reference}"
          create(account)
          system "git commit -a -m 'import #{account.mandate_reference}' > /dev/null"
        end
        pb.increment
      end
    end

    def accounts
      @accounts ||= begin
        csv = CSV.read(ARGV[0])
        header = csv.shift
        csv.map do |line|
          a = Account.new(
            Hash[header.zip(line)]
          )

          exit 1 unless a.valid?

          a
        end
      end
    end

    LOGIN_URL = 'https://www.solution-pack-sepa.credit-agricole.fr/PortailMandateWayCA/login.jsp'
    CREATE_MANDATE_URL = 'https://www.solution-pack-sepa.credit-agricole.fr/PortailMandateWayCA/work1/creatMandate.html'

    def agent
      @agent ||= begin
        agent = Mechanize.new
        agent.default_encoding = 'UTF-8'

        puts "Connecting..."
        page = agent.get(LOGIN_URL)
        form = page.form('f')
        form.j_username = ENV.fetch( 'CA_USERNAME' )
        form.j_password = ENV.fetch( 'CA_PASSWORD' )
        page = agent.submit(form)
        unless page.body.include? "Bienvenue  #{ENV.fetch('CA_NAME', 'Philippe Creux')}" # yes, 2 spaces!
          raise "Failed to login: #{page.body}"
        end
        puts "Connected!"

        agent
      end
    end

    def exists?(mandate_reference)
      File.read('created.csv').include? mandate_reference
    end

    def www_exists?(mandate_reference)
      page = agent.get "https://www.solution-pack-sepa.credit-agricole.fr/PortailMandateWayCA/work1/searchMandate.html?mandate_reference=#{CGI.escape(mandate_reference)}"
      if page.body.include? "Aucun mandat ne correspond"
        false
      elsif page.body.include? mandate_reference
        true
      else
        raise "Unexpected response: #{page.body}"
      end
    end

    def create(account)
      page = agent.get "https://www.solution-pack-sepa.credit-agricole.fr/PortailMandateWayCA/work1/creatMandate.html"
      form = page.form_with(id: 'createMan')

      form.dbtrNm               = account.last_name
      form.dbtrAdrLine5         = account.first_name # weird mapping eh?
      form.dbtrAdrLine1         = account.address
      form.dbtrAdrTwnNm         = account.city
      form.dbtrAdrPstCd         = account.postal_code
      form.dbtrBic              = account.bic
      form.ibanCountryCode      = account.iban_country_code
      form.ibanKeyControl       = account.iban_key
      form.ibanAccountNumber    = account.iban_account_number
      form.underlyingContractId = account.contract_ref
      form.phoneNb              = account.phone_number
      form.mobileNb             = account.mobile_phone_number
      form.emailAdr             = account.email
      form.signatureDate        = account.signature_date
      form.signatureLocation    = account.signature_city
      form.mandateRef           = account.mandate_reference

      page = agent.submit(form)

      errors = page.body.scan(/<span.*error.*span>/).to_a
      unless errors.empty?
        p account
        p errors
        puts "Import - Error - #{account.mandate_reference}"
        if errors.join('').include? 'Incoh&eacute;rence entre BIC et IBAN'
          File.open('failed.csv', 'a') do |f|
            f.puts [account.id, account.mandate_reference, [account.iban_country_code, account.iban_key, account.iban_account_number].join, account.bic].join ','
          end
          puts "Import - Error - invalid BIC / IBAN... skipping"
          return nil
        end
        raise "error while creating account..."
      end

      page = page.links.find { |l| l.href.include? 'creer' }.click
      unless page.body.include? 'printPDFMandat'
        puts page.body
        puts "Import - Error - #{account.mandate_reference}"
        raise "error while confirming account..."
      else
        puts "Import - Success - #{account.mandate_reference}"
        File.open('created.csv', 'a') do |f|
          f.puts account.id + ',' + account.mandate_reference
        end
      end
    end
  end

  class Account
    include Virtus.model
    MANDATORY = %w(
      last_name
      first_name
      address
      city
      postal_code
      bic
      iban_country_code
      iban_key
      iban_account_number
      signature_date
      signature_city
      mandate_reference)

    OPTIONAL = %w(
      id
      contract_ref
      phone_number
      mobile_phone_number
      email)

    (MANDATORY + OPTIONAL).each do |a|
      attribute a
    end

    def valid?
      self.mandate_reference = ' '
      MANDATORY.each do |a|
        if send(a).nil? || send(a).empty?
          puts "missing #{a} - #{attributes.inspect}"
          return false
        end
      end

      (MANDATORY + OPTIONAL).each do |attribute|
        self.send("#{attribute}=", sanitize(self.send(attribute)))
      end

      self.email.downcase! if self.email
      self.iban_account_number = self.iban_account_number.gsub(' ', '')
      self.signature_city = self.signature_city.gsub(/\d/, '').strip
      self.city = self.city.gsub(/\d/, '').strip
      self.signature_date = sanitize_signature_date
      self.mandate_reference = generate_mandate_ref
      self.postal_code = postal_code.rjust(5, '0')
      self.iban_key = self.iban_key.rjust(2, '0')
      # Deleting the BIC suffix works sometimes...
      # self.bic = bic.gsub(/...$/, '')

      true
    end

    def sanitize_signature_date
      d, m, yyyy = signature_date.split('/')
      dd = d.rjust(2, '0')
      mm = m.rjust(2, '0')
      [dd, mm, yyyy].join('/')
    end

    def generate_mandate_ref
      dd, mm, y = signature_date.split('/')
      yy = y[-2..-1]
      "++" + yy + mm + dd + first_name.gsub(/[^A-Z]/, '') + last_name.gsub(/[^A-Z]/, '')
    end


    def sanitize(name)
      return nil if name.nil?
      name = name.tr( "ÀÁÂÃÄÅàáâãäåĀāĂ㥹ÇçĆćĈĉĊċČčÐðĎďĐđÈÉÊËèéêëĒēĔĕĖėĘęĚěĜĝĞğĠġĢģĤĥĦħÌÍÎÏìíîïĨĩĪīĬĭĮįİıĴĵĶķĸĹĺĻļĽľĿŀŁłÑñŃńŅņŇňʼnŊŋÒÓÔÕÖØòóôõöøŌōŎŏŐőŔŕŖŗŘřŚśŜŝŞşŠšſŢţŤťŦŧÙÚÛÜùúûüŨũŪūŬŭŮůŰűŲųŴŵÝýÿŶŷŸŹźŻżŽž", "AAAAAAaaaaaaAaAaAaCcCcCcCcCcDdDdDdEEEEeeeeEeEeEeEeEeGgGgGgGgHhHhIIIIiiiiIiIiIiIiIiJjKkkLlLlLlLlLlNnNnNnNnnNnOOOOOOooooooOoOoOoRrRrRrSsSsSsSssTtTtTtUUUUuuuuUuUuUuUuUuUuWwYyyYyYZzZzZz")

      name.
        gsub("'", ' ').
        gsub(";", ' ').
        upcase.
        strip
    end

  end
end

RUN.call