finance-entrypoint

#!/bin/bash
#
# (C) 2012-2018 Stefan Schallenberg
#
# Execute automatic tasks for finance

##### trim ###################################################################
function trim {
	if [ $# -ne 1 ] ; then
		# Empty String results in empty string
		return
	fi

	# remove leading blanks
	local v="$1"
	v="${v#"${v%%[![:space:]]*}"}"
	v="${v%"${v##*[![:space:]]}"}"

	printf "%s\n" "$v"
	}

#### DB Setup privileges #####################################################
function dbsetup_privileges () {
	printf "Granting DB privileges to %s.\n" "$MYSQL_USER"
	$MYSQL_ROOT_CMD <<-EOF
		GRANT all privileges
			ON $MYSQL_DATABASE.*
			TO '$MYSQL_USER'@'%'
			WITH GRANT OPTION;
		EOF
	if [ $? != "0" ] ; then
		printf "Error granting DB privileges.\n"
		return 1
	fi
}

#### DB Setup Users ##########################################################
function dbsetup_users () {
	printf "(Re-)Creating DB User mariadb.sys\n"
	mysql \
		--host=$MYSQL_HOST \
		--user=root \
		--password=$MYSQL_ROOT_PASSWORD \
		mysql \
		<<-EOF
			CREATE USER IF NOT EXISTS 'mariadb.sys'@'localhost' ACCOUNT LOCK;
			GRANT SELECT,UPDATE,DELETE on mysql.global_priv to 'mariadb.sys'@'localhost';
			EOF
	if [ $? != "0" ] ; then return  1 ; fi

	where_users=""
	for u in ${DB_USERS-} ; do
		where_users="${where_users}, '$u'"
	done
	$MYSQL_ROOT_CMD -N --batch <<-EOF |
		SELECT user, host
			FROM mysql.user
			WHERE user NOT IN ('root', 'mariadb.sys', 'healthcheck',
				'$MYSQL_USER' $where_users )
			AND
				is_role="N";
		EOF
	while IFS=$'\t' read db_user db_host ; do
		printf "Deleting DB User %s @ %s\n" "$db_user" "$db_host"
		$MYSQL_ROOT_CMD <<-EOF
			DROP USER '$db_user'@'$db_host';
			EOF
		if [ $? != "0" ] ; then return  1 ; fi
	done
	if [ $? != "0" ] ; then return 1 ; fi

	$MYSQL_ROOT_CMD -N --batch <<-EOF |
		SELECT user
			FROM mysql.user
			WHERE user NOT IN ('fin_user') AND
				is_role="Y";
		EOF
	while read db_user ; do
			printf "Deleting DB Role %s\n" "$db_user"
			$MYSQL_ROOT_CMD <<-EOF
				DROP ROLE '$db_user' ;
				EOF
			if [ $? != "0" ] ; then return  1 ; fi
	done
	if [ $? != "0" ] ; then return 1 ; fi

	printf "Creating DB Role fin_user.\n"
	$MYSQL_ROOT_CMD <<-EOF
		CREATE ROLE IF NOT EXISTS fin_user;
		EOF
	if [ $? != "0" ] ; then return  1 ; fi

	for u in ${DB_USERS-} ; do
		eval p=\${DB_${u}_PASSWORD-}
		if [ -z "$p" ] ; then
			printf "Creating DB User %s without password\n" "$u"
			$MYSQL_ROOT_CMD <<-EOF
				CREATE USER IF NOT EXISTS '$u'@'%'
				EOF
			if [ $? != "0" ] ; then return  1 ; fi
		else
			printf "Creating DB User %s with password\n" "$u"
			$MYSQL_ROOT_CMD <<-EOF
				CREATE USER IF NOT EXISTS '$u'@'%' IDENTIFIED BY '$p';
				EOF
			if [ $? != "0" ] ; then return  1 ; fi
		fi
		$MYSQL_ROOT_CMD <<-EOF
			GRANT fin_user TO '$u'@'%';
			SET DEFAULT ROLE fin_user FOR '$u'@'%';
			EOF
		if [ $? != "0" ] ; then return 1 ; fi
	done
}

##### dbsetup_tables #########################################################
dbsetup_tables () {
	printf "Creating/Updating database %s.\n" "$MYSQL_DATABASE"
	fntxt2sql -cre -mysql \
		-mysql_host $MYSQL_HOST \
		-mysql_database $MYSQL_DATABASE \
		-mysql_user $MYSQL_USER \
		-mysql_password $MYSQL_PASSWORD
	if [ "$?" != "0" ] ; then
		printf "Error creating/updating database.\n"
		exit 1
	fi
}

##### aqsetup ################################################################
function aqsetup () {
	local line pin u blz parm
	local account_nr=0
	local accounts_blz=( )
	local accounts_user=( )

	# Read .hbci-pinfile
	while read line ; do
		if [ "${#line}" == "0" ] ; then
			continue;	# ignore blank lines
		elif [ ${line:0:1} == "#" ] ; then
			continue;	# ignore comment lines
		fi
		case $line in
			PIN_* )
				# HBCI with german BLZ and account number
#				pin=$(trim "${line#*=}")
#				pin=${pin%\"}
#				pin=${pin#\"}
				line=$(trim "${line%%=*}")
				line=${line##PIN_}
				u=${line#*_}
				blz=${line%_*}
				#DEBUG printf "DEBUG: PIN blz=%s u=%s\n" "$blz" "$u"

				accounts_blz[$account_nr]="$blz"
				accounts_user[$account_nr]="$u"
				(( account_nr ++ ))
				;;

			URL_* )
				parm=$(trim "${line#*=}")
				parm=${parm%\"}
				parm=${parm#\"}
				line=$(trim "${line%%=*}")
				line=${line##URL_}
				u=${line#*_}
				blz=${line%_*}
				#DEBUG printf "DEBUG: URL blz=%s u=%s parm=\"%s\"\n" "$blz" "$u" "$parm"

				eval "URL_${blz}_${u}=\"\$parm\""
				;;
			TANMODE_* )
				parm=$(trim "${line#*=}")
				parm=${parm%\"}
				parm=${parm#\"}
				line=$(trim "${line%%=*}")
				line=${line##TANMODE_}
				u=${line#*_}
				blz=${line%_*}
				#DEBUG printf "DEBUG: TANMODE blz=%s u=%s parm=\"%s\"\n" "$blz" "$u" "$parm"

				eval "TANMODE_${blz}_${u}=\"\$parm\""
				;;

			SETUP_* )
				parm=$(trim "${line#*=}")
				parm=${parm%\"}
				parm=${parm#\"}
				line=$(trim "${line%%=*}")
				line=${line##SETUP_}
				u=${line#*_}
				blz=${line%_*}
				#DEBUG printf "DEBUG: SETUP blz=%s u=%s parm=\"%s\"\n" "$blz" "$u" "$parm"

				eval "SETUP_${blz}_${u}=\"\$parm\""
				;;
			* )
				printf "Warning: Ignoring unknown HBCI config Line %s in %s\n" \
					"$line" "$datadir/.hbci-pinfile"
				;;
		esac
	done <$datadir/.hbci-pinfile
	(( account_nr -- ))

	# read configuration of aqhbci
	users=$(aqhbci_wrapper listusers )
	rc=$?
	# if list is empty rc=4 is set, so we ignore it.
	if [ $rc == 4 ] && [ -z "$users" ] ; then rc=0; fi
	[ $rc != 0 ] && return 1
	while read line ; do
		# line looks like
		# User 1: Bank: de/50010517 User Id: 1234567890 Customer Id: 12345678901234
		#DEBUG printf "DEBUG: Line \"%s\"\n" "$line"
		if [ -z "$line" ] ; then
			continue
		fi
		blz=${line##*Bank: }
		blz=${blz%% User Id:*}
		blz=${blz##de/}
		u=${line##*User Id: }
		u=${u%% Customer Id: *}
		#DEBUG printf "DEBUG: SETUP blz=%s u=%s\n" "$blz" "$u"
		eval "AQCONF_${blz}_${u}=1"
	done <<<$users

	# Now configure each account in pinfile but not yet in aqhbci
	for i in $(seq 0 $account_nr) ; do
		blz=${accounts_blz[$i]}
		u=${accounts_user[$i]}
		eval url=\"\$URL_${blz}_${u}\"
		eval tanmode=\"\$\{TANMODE_${blz}_${u}-\}\"
		eval setup=\"\$\{SETUP_${blz}_${u}-\}\"
		eval aqconf=\"\$\{AQCONF_${blz}_${u}-\}\"
		if [ -z "$aqconf" ] ; then
			aqsetup_acc "$blz" "$u" "$url" "$tanmode" "$setup" || return 1
		else
			printf "Reusing existing HBCI configuration for BLZ %s User %s\n" \
				"$blz" "$u"
		fi
	done

	return 0
}

function aqbanking_wrapper () {
	#DEBUG
	printf " ---------- aqbanking-cli %s\n" "$*" >&2
	[ "$1" == "--noninteractive" ] && shift
	aqbanking-cli \
		$taninteractive \
		--acceptvalidcerts \
		--pinfile=$datadir/.hbci-pinfile \
		"$@"
	rc=$?; if [ "$rc" != 0 ] ; then
		printf "Error %s in aqbanking-clie %s\n" "$rc" "$*" >&2
		return $rc
	fi
}

function aqhbci_wrapper () {
	#DEBUG
	printf " ---------- aqhbci-tool4 %s\n" "$*" >&2
	[ "$1" == "--noninteractive" ] && shift
	aqhbci-tool4 \
		$taninteractive \
		--acceptvalidcerts \
		--pinfile=$datadir/.hbci-pinfile \
		"$@"
	rc=$?; if [ "$rc" != 0 ] ; then
		printf "Error %s in aqhbci-tool4 %s\n" "$rc" "$*" >&2
		return $rc
	fi
}

##### aqsetup_acc ############################################################
function aqsetup_acc () {
	local blz="$1"
	local u="$2"
	local url="$3"
	local tanmode="$4"
	local parm="$5"

	local disable_getbankinfo="" disable_getsysid="" disable_setitanmode=""
	printf "Configuring HBCI for BLZ %s User %s tanmode=%s url=%s setup=%s\n" \
		"$blz" "$u" "$tanmode" "$url" "$parm"

	if [ "$blz" == "50010517" ] ; then
		# Special handline of ING-Diba Germany
		# They are not PSD2 compliant, one can only read balances and
		# transactions, but its enough for us.
		# Unidentified getbankinfo is not working, so we need to disable it
		disable_getbankinfo="1"
		disable_getsysid="1"
		if [ -z "$tanmode" ] ; then
			disable_setitanmode="1"
		fi
		printf "\tApplying special handling for ING-Diba\n"
	fi

	aqhbci_wrapper adduser \
		-b $blz \
		-N $u \
		-u $u \
		-t pintan \
		--context=1 \
		-s $url \
		--hbciversion=300 \
		$parm \
		|| return 1

	# find unique userid
	sed_regex='s#User [^:]*: '
	sed_regex+="Bank: de/$blz "
	sed_regex+="User Id: $u "
	sed_regex+="Customer Id: $u "
	sed_regex+='Unique Id: '
	uu=$(
		set -o pipefail
		aqhbci_wrapper --noninteractive listusers \
		| sed -n "${sed_regex}\\(.*\\)#\\1#p"
		) || return 1

	if [ -z "$disable_getbankinfo" ] ; then
		aqhbci_wrapper getbankinfo -u "$uu" || return 1
	fi

	if [ -z "$disable_getsysid" ] ; then
		aqhbci_wrapper getsysid -u "$uu" || return 1
	fi

	if [ -z "$disable_setitanmode" ] ; then
		aqhbci_wrapper setitanmode -u "$uu" -m "$tanmode"
		if [ $? != 0 ] ; then
			printf "Following itan modes are available:\n" &&
			aqhbci_wrapper \
				--noninteractive \
				listitanmodes \
				-u "$uu"
			return 1
		fi
	fi

	aqhbci_wrapper getaccounts -u "$uu"
	rc=$? ; if [ "$rc" != 0 ] ; then
		printf "Error %s in aqhbci-tool4 getaccounts. aqhbci-config dump follows.\n" "$rc"
		aqhbci_wrapper \
			listusers
		aqhbci_wrapper \
			listaccounts
		return 1
	fi

	# read accounts just added
	# Since aqhbci does not allow to select by user we selct by bank.
	# If accounts was already added with a different user results are unknown.
	accs=$(aqhbci_wrapper listaccounts -v ) || return 1
	while read line ; do
		# line looks like
		# Account 0: Bank: 70020270 Account Number: 4840199334  SubAccountId: EUR \
		# Account Type: bank LocalUniqueId: 5

		#DEBUG printf "DEBUG: Line \"%s\"\n" "$line"
		if [ -z "$line" ] ; then
			continue
		fi
		acc_blz=${line##*Bank: }
		acc_blz=${acc_blz%% Account Number:*}
		acc_nr=${line##*Account Number: }
		acc_nr=${acc_nr%%  SubAccountId:*}
		acc_id=${line##*LocalUniqueId: }
		if [ "$acc_blz" == "$blz" ] ; then
			printf "Adding new account %s id=%s for bank %s\n" \
				"$acc_nr" "$acc_id" "$acc_blz"
			aqhbci_wrapper getaccsepa -a "$acc_id" || return 1
			if [ -z "$disable_getbankinfo" ] ; then
				aqhbci_wrapper getbankinfo -u "$uu" || return 1
				aqhbci_wrapper getaccsepa -a "$acc_id" || return 1
			fi
		#DEBUG else
		# printf "ignoring account %s id=%s for bank %s\n" \
		#	"$acc_nr" "$acc_id" "$acc_blz"
		fi
	done <<<$accs

	# unclear why needed another time, but without it it does not work.
	aqhbci_wrapper getaccounts -u "$uu" || return 1

	return 0
}

##### Import ################################################################
function import () {
	for f in `ls -d $datadir/inbound.* 2>/dev/null`; do
		if [ -d $f ]; then
			printf "Importing directory %s\n" "$f"
			import_handleDir "$f" "$datadir/store"

		else
			printf "Not Importing Non-Directory %s\n" "$f"
		fi
	done

	return 0
}

##### Import_rescan ##########################################################
function import_rescan () {
	for f in `ls -d $datadir/store.* 2>/dev/null`; do
		if [ -d $f ]; then
			printf "Importing directory %s\n" "$f"
			import_handleDir "$f"
		else
			printf "Not Importing Non-Directory %s\n" "$f"
		fi
	done

	return 0
}

##### Handle one inbound or store directory ##################################
# Parameter 1: Directory to be scanned
# Parameter 2: where to move files after successful importing
function import_handleDir
{
	scandir="$1"
	moveTo="${2-}"

	# get type from Extension
	ftype=${scandir##*.}

	# Create target directory if it does not exist
	if [ ! -z $moveTo ] && [ ! -d $moveTo.$ftype ] ; then
		mkdir $moveTo.$ftype || return 1
	fi

	for f in `ls -d $scandir/* 2>/dev/null`; do
		case $ftype in
			aqb6 | aqb-tran | aqb-bal | aqm | btx)
				if [ -f $f ]; then
					printf "Importing file %s\n" "$f"
					fntxt2sql \
						-$ftype \
						-f $f \
						-mysql \
						-mysql_host $MYSQL_HOST \
						-mysql_database $MYSQL_DATABASE \
						-mysql_user $MYSQL_USER \
						-mysql_password $MYSQL_PASSWORD
					rc=$?
				else
					printf "Not importing Directory %s %s\n" "$ftype" "$f"
					rc=-1
				fi
				;;
			# for HBCI only directories are allowed
			hbci )
				if [ -d $f ]; then
					printf "Importing HBCI directory %s\n" "$f"
					$fntxt2sql \
						-$ftype \
						-f $f \
						-mysql \
						-mysql_host $MYSQL_HOST \
						-mysql_database $MYSQL_DATABASE \
						-mysql_user $MYSQL_USER \
						-mysql_password $MYSQL_PASSWORD
					rc=$?
				else
					printf "Not importing Non-Directory %s %s\n" "$ftype" "$f"
					rc=-1
				fi
				;;
			manual )
				printf "Not importing %s %s. Run manually!\n" "$ftype" "$f"
				rc=-1
				;;
			*)
				printf "Not importing unknown type %s in %s\n" "$ftype" "$f"
				rc=-1
				return 1
		esac

		if [ ! -z $moveTo ]; then
			if [ "$rc" == 0 ]; then
				mv -n $f $moveTo.$ftype/ || return 1
				printf "Moved imported file %s to %s\n" "$f" "$moveTo.$ftype"
			else
				printf "Import of $f failed (RC=%s).\n" "$rc"
			fi
		fi
	done

	return 0
}

##### Alert ##################################################################
# send balance and transactions via EMail
function alert () {
	export LC_ALL=C
	export MAILRC=/dev/null

	for a in ${MAIL_ACCOUNTS-} ; do

		Saldo=$(
		$MYSQL_CMD -s <<-EOF
			SELECT datum, betrag
			FROM fn_entry
			WHERE buchart = 'E' AND
				orig_ktonr="$a" AND
				datum = ( SELECT MAX(DATUM) from fn_entry where orig_ktonr='$a');
			EOF
		)
		if [ "$?" != "0" ] ; then return 1 ; fi


		( $MYSQL_CMD <<-EOF
			SELECT datum, betrag, part_name1, vzweck1, vzweck2
			FROM fn_entry
			WHERE buchart='B' AND
				orig_ktonr='$a' AND
				datum >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
			ORDER BY datum DESC, betrag;
		EOF
		) | mail \
			-n -Sv15-compat -Ssendwait -Snosave -Sexpandaddr=fail,-all,+addr \
			-S 'smtp-auth=none' \
			-S "mta=$MAIL_URL" \
			-S 'sendcharsets=C' \
			-S 'ttycharset=C' \
			-S "hostname=$MAIL_HOSTNAME" \
			-s "Account-info $a: $Saldo" \
			-r "$MAIL_FROM" $MAIL_TO \
			|| return 1
			# -SNosave is included in -d and generates error messages - so dont include it
			# -n -d -vv -Sv15-compat -Ssendwait -Sexpandaddr=fail,-all,+addr

	done

	return 0
}

##### Main ###################################################################
set -euo pipefail
[ "${DEBUG-}" == "1" ] && set -x

datadir=/finance

if [ -z "${MYSQL_HOST-}" ] ; then
	printf "Variable MYSQL_HOST needs to be set.\n"
	exit 1
elif [ -z "${MYSQL_DATABASE-}" ] ; then
	printf "Variable MYSQL_DATABASE needs to be set.\n"
	exit 1
elif [ -z "${MYSQL_USER-}" ] ; then
	printf "Variable MYSQL_USER needs to be set.\n"
	exit 1
elif [ -z "${MYSQL_PASSWORD-}" ] ; then
	printf "Variable MYSQL_PASSWORD needs to be set.\n"
	exit 1
elif [ ! -r $datadir/.hbci-pinfile ] ; then
	printf "File %s/.hbci-pinfile must exist and be readable.\n" \
		"$datadir"
	exit 1
fi

if [ ! -z "${MAIL_TO-}" ] ; then
	if [ -z "${MAIL_FROM-}" ] ; then
		printf "Variable MAIL_FROM needs to be set if using Alerting indicated by MAIL_TO\n"
		exit 1
	elif [ -z "${MAIL_URL-}" ] ; then
		printf "Variable MAIL_URL needs to be set if using Alerting indicated by MAIL_TO\n"
		exit 1
	elif [ -z "${MAIL_ACCOUNTS-}" ] ; then
		printf "Variable MAIL_ACCOUNTS needs to be set if using Alerting indicated by MAIL_TO\n"
		exit 1
	fi
	if [ -z "${MAIL_HOSTNAME-}" ] ; then
		MAIL_HOSTNAME="$(hostname)"
	fi
fi

fromdate=$(date +%Y%m%d -d @$(( $(date +%s) - ( 90 * 24 * 3600 ) )))
taninteractive="--noninteractive"
while [ "$#" -gt 0 ] ; do
	if [ "${1:0:10}" == "--fromdate" ] ; then
		fromdate="${1:11}"
	elif [ "$1" == "--taninteractive" ] ; then
		taninteractive=""
	elif [ "$1" == "--debug" ] ; then
		DEBUG=1
		set -x
	else
		printf "Error: Unknown parameter %s\n" "$1"
		exit 1
	fi
	shift
done

if ! ping  -c 1 $MYSQL_HOST ; then
	printf "Warning: MYSQL_HOST %s is not pingable.\n" "$MYSQL_HOST"
	[ "${DEBUG-}" != "1" ] && set -x
	cat /etc/resolv.conf
	dig +search $MYSQL_HOST || true # dont stop on error
	ping -c 1 $MYSQL_HOST || true # dont stop on error
	[ "${DEBUG-}" != "1" ] && set +x
fi

MYSQL_CMD="mysql"
MYSQL_CMD="$MYSQL_CMD --host=$MYSQL_HOST"
MYSQL_CMD="$MYSQL_CMD --user=$MYSQL_USER"
MYSQL_CMD="$MYSQL_CMD --password=$MYSQL_PASSWORD"
MYSQL_CMD="$MYSQL_CMD $MYSQL_DATABASE"

if [ ! -z "${MYSQL_ROOT_PASSWORD-}" ] ; then
	printf "Got DBADM privilege because \$MYSQL_ROOT_PASSWORD is set.\n"

	MYSQL_ROOT_CMD="mysql"
	MYSQL_ROOT_CMD="$MYSQL_ROOT_CMD --host=$MYSQL_HOST"
	MYSQL_ROOT_CMD="$MYSQL_ROOT_CMD --user=root"
	MYSQL_ROOT_CMD="$MYSQL_ROOT_CMD --password=$MYSQL_ROOT_PASSWORD"
	MYSQL_ROOT_CMD="$MYSQL_ROOT_CMD $MYSQL_DATABASE"
else
	printf "No DBADM privileges because \$MYSQL_ROOT_PASSWORD is not set.\ Will not check users.n"
	MYSQL_ROOT_CMD=""
fi

if [ ! -z "$MYSQL_ROOT_CMD" ] ; then
	dbsetup_privileges || exit 1
fi

if [ ! -z "$MYSQL_ROOT_CMD" ] && [ ! -z "$DB_USERS" ] ; then
	dbsetup_users || exit 1
fi

dbsetup_tables || exit 1

aqsetup || exit 1

test -d $datadir/inbound.aqb6      || mkdir $datadir/inbound.aqb6
test -d $datadir/inbound.aqb6-tran || mkdir $datadir/inbound.aqb6-tran
test -d $datadir/inbound.aqb6-bal  || mkdir $datadir/inbound.aqb6-bal
test -d $datadir/store.aqb6        || mkdir $datadir/store.aqb6

printf "Loading transactions and balances \n"
aqbanking_wrapper \
	request \
	--balance \
	--transactions \
	--ignoreUnsupported \
	-c $datadir/inbound.aqb6/$(date +\%Y\%m\%d-\%H\%M\%S.\%N) \
	--fromdate=$fromdate \
	|| exit 1

# @TODO change to aqb6. Check for formatstring (new option inaqb6) and
# also modify fntxt2sql to the new format
printf "Processing transactions and balances \n"
for f in $(ls $datadir/inbound.aqb/* 2>/dev/null) ; do
	fnam=$(basename $f)
	aqbanking_wrapper listtrans -c $f >$datadir/inbound.aqb-tran/$fnam && \
	aqbanking_wrapper listbal   -c $f >$datadir/inbound.aqb-bal/$fnam  && \
	mv $f $datadir/store.aqb
done

printf "Importing transactions and balances into database\n"
import || exit 1

if [ ! -z "${MAIL_TO:-}" ] ; then
	printf "Sending alert EMail\n"
	alert || exit 1
fi

exit 0