This is an old revision of the document!


Current Solution to this project is discussed in BrmLib

Administrativa Automation
founder: pasky
depends on:
interested: joe,
software license: Apache+PHP+Postgres
hardware license: N/A
status: active

brmlab existence involves some mundane adminsitrativa tasks, mainly tracking of membership fees and donations. So far, this is done manually and there result is a lot of laborous work and a lot of chaos.

Therefore, some simple software tool would do us a lot of good. It should:

  • Track members database - including personal, payment and access information
    • Can also provide LDAP interface for UNIX auth, automatically flash brmdoor, etc.
  • Track payments made to our bank accounts and tie them to the members database
  • Project our current and expected financial balance so that we know how much we can our incomes expect to be (and compare them to our expenses)
  • Keep the “paid until” attribute up-to-date and automatically send notifications and reminders in case payment is due
  • Produce reports and donation certificates for tax purposes

Status and Roadmap

  • Design and deploy dedicated members database instead of in-wiki membership roster
    • [TODO] Where do we deploy it? brmko? Or somewhere “safe” outside of brmlab?
    • [TODO] SQL schema design; for starters, the information currently in membership roster
    • [TODO] Simple web interface for querying and modifying the database
  • Include accounting information in the database
    • [TODO] Extend SQL schema to track incomes from members (or otherwise)
    • [TODO] Gateway Fio e-mail notifications to the database
    • [TODO] Automatic “payment due” reminders
    • [TODO] Automatic donation tax receipt generator
    • [TODO] Simple “project financial balance” tool
    • [not finished] LDAP interface for UNIX authentication
      • slapd partialy configured on data.brm. scheme improvements needed
    • [TODO] Possibly also some expense tracking and some-such

General Architectural Overview

(i) Notwithstanding the provisions of subsection 3 of Section A of Clause 214 of the Administrative Procedures (Scotland) Act 1978, it has been agreed that, insofar as the implementation of the statutory provisions is concerned, the resolution of anomalies and uncertainties between responsible departments shall fall within the purview of the Minister for Administrative Affairs.



Evidence plateb v bankovnictvi

# Copyright 2012 TMA
# License: GNU GPL version 2
use strict;
use warnings;
use utf8;
my $status = 'endmail';
my ($fio,$konto,$castka,$VS,$SS,$zprava,$protiucet,$date);
my ($header, $body);
my $mul;
sub vreset() {
	($header,$body) = ('','');
	($fio,$konto,$castka,$VS,$SS,$zprava,$protiucet,$date) = undef;
sub process($) {
	$status = $_[0];
	no warnings;
	$date =~ s/ +\d+:\d+:\d+ +[+-]\d{4}$//o;
	$date =~ s/^..., *(\d+) (...|\d+) (\d\d\d\d).*/$3-$2-$1/o;
	$date =~ s/Jan/01/o;
	$date =~ s/Feb/02/o;
	$date =~ s/Mar/03/o;
	$date =~ s/Apr/04/o;
	$date =~ s/May/05/o;
	$date =~ s/Jun/06/o;
	$date =~ s/Jul/07/o;
	$date =~ s/Aug/08/o;
	$date =~ s/Sep/09/o;
	$date =~ s/Oct/10/o;
	$date =~ s/Nov/11/o;
	$date =~ s/Dec/12/o;
	$date =~ s/ /0/o;
	$date =~ s/-(\d)-/-0$1-/o;
	$date =~ s/-(\d)$/-0$1/o;
	print "-- $ARGV $.\n";
	print "insert into ucetni_data (konto, castka, datum, VS, SS, protiucet, zprava, comment) values
	($konto, $castka", commanullq($date),
	commanull($VS), commanull($SS), commanullq($protiucet),
sub def_or_empty($) { defined$_[0]?$_[0]:'' }
sub quot($) {
	my ($x) = @_;
	# ' needs to be doubled
	$x =~ s/'/''/go;
	# replace suspicious characters by .
	$x =~ s{[^][\na-zA-Z0-9 !@#$%^&*()_=+;:"|<>/?,.~`{}'-]}{.}go; # '
sub commanull_($&) {
	my ($x, $q) = (@_);
	#print STDERR "[[$x]]\n";
	no warnings;
	(length $x) ? ", ". $q->($x) : ", NULL"
sub commanull($) { commanull_($_[0],sub {$_[0]}) }
sub commanullq($) { commanull_($_[0],\&quot) }
sub decimal($) {
	my ($x) = @_;
	$x =~ y/,/./;
	$x =~ s/ //g;
my %action = (
	endmail => sub {
		#print "$status: $_\n";
		/^From / and do {{ $status = 'header'; vreset }}
	header => sub {
		$header .= $_."\n";
		#print "$status: $_\n";
		/^From: automat(?:\@| at )fio\.cz/ and $fio = 1;
		/^$/ and $status = ($fio ? 'body' : 'endmail'); 
		/^Date: (.*)/ and $date = $1;
		#print "$status(",def_or_empty($fio),"): $_\n";
	body => sub {
		/^From / or $body .= $_."\n";
		#print "$status: $_\n";
		/^P..jem na kont.: (\d+)/ and ($konto, $mul) = ($1,1);
		/^V.daj na kont.: (\d+)/ and ($konto, $mul) = ($1,-1);
		/^..stka: ([0-9 ]+[,.]\d+)?/ and $castka = $mul * decimal($1);
		/^VS: (\d+)/ and $VS = $1;
		/^SS: (\d*)/ and $SS = $1;
		/^Zpr.va p..jemci: (.*)/ and $zprava = $1;
		/^Proti..et: ([0-9\/-]+)/ and $protiucet = $1;
		/_____/ and process('endmail');
		/^From / and (process('header'),vreset);
		#/^P[?ř][?í]jem na kont[?ě]: (\d+)/ and 	$konto = $1;
		#/^[?Č][?á]stka: (\d+)[,.](\d+)?/ and $castka = $1 + $2/100;
		#/^VS: (\d+)/ and $VS = $1;
		#/^SS: (\d*)/ and $SS = $1;
		#/^Zpr[?á]va p[?ř][?í]jemci: (.*)/ and $zprava = $1;
		#/^Proti..et: ([0-9\/-]+)/ and $protiucet = $1;
		#/_____/ and process;
my ($drop,$create) = (1,1);
my $SEP = ';';
print <<EOF if $drop;
drop table if exists ucetni_data;
print <<EOF if $create;
  `konto` decimal(30,0) NOT NULL,
  `castka` decimal(30,2) NOT NULL,
  `datum` date NOT NULL,
  `vs` int(11) DEFAULT NULL,
  `ss` int(11) DEFAULT NULL,
  `protiucet` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zprava` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `comment` varchar(2000) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
while (<>) {
process('') if $status eq 'body';


Short version history:

  • v0.0 was a large wall-piece of paper with prices and piece of paper with credit tracking
  • v1.0 was/is DOS-based tablet
  • v2.0 was an attempt on a LPC revamp that was ultimately unsuccessful
  • v3.0-tlapka is an ongoing(?) effort by tlapka for Arduino-based, ethernet-connected brmbar
  • v3.0 is pasky's Perl hack to replace v1.0 at least before v3.0-tlapka is finished (or for longer, if we like it); this page describes v3.0

Draft database schema and process classification (inspiration, but implemented with modifications):



Current database schema

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki