| http://the.fork.pl/code/kb24/ |
the.fork.pl
Site Credits Contact Powered by The Story of Fork Words Sermon on the Hill Antybiografia Angel Devil Powrót z... Simon T. Favourite poems Roam Sounds The Lost Faith Atari Reloaded Players Code Saker Moon phase
KB24 Attic PLC 33doi DemoScene Atari Logrus Cryogen Crew History Stuph Pictures Ascii Art Set One Set Two Set Three
KB24 - automatyka
Skrypty archiwizujące opearcje z konta i karty kredytowej w systemi KB24 (Kredyt Bank).Wyjaśnienie
Poniższe skrypty działają u mnie, ale nie daję żadnych gwarancji, że będą działać u Ciebie. Mi akurat wystarcza sprawdzanie RORu i jednej karty VISA. Pamiętaj, że sam odpowiadasz za bezpieczeństwo swojego konta.
Jak to działa
Skrypt kb24_getcsv.pl pobiera wyciąg z konta (ROR+VISA), u mnie robi to co 30 minut (cron). Skrypt kb24_mysql.pl analizuje wyciągi i uzupełnia bazę, dodatkowo wysyła maile (i/lub smsy, ale też poprzez mail) o nowych transakcjach.
Dane do logowania i inne przechowywane są w credentials.ini (przykład poniżej).
Skrypty wymagają pakietów LWP, Digest::SHA, Text::CSV_XS i DBI/MySQL
Download
kb24_getcsv.pl 3.5K kb24_mysql.pl 5.4KPrzykładowa konfiguracja
id=12345678
pin=123456
email=user@mailbox
sms=user@idea.net.pl
db_user=aaa
db_pass=aaa
db_host=localhost
db_db=kb24
pin=123456
email=user@mailbox
sms=user@idea.net.pl
db_user=aaa
db_pass=aaa
db_host=localhost
db_db=kb24
Skrypt tworzący bazę
create.sql 961BCREATE TABLE `ror` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_op` date NOT NULL DEFAULT '0000-00-00',
`data_ks` date NOT NULL DEFAULT '0000-00-00',
`opis` text NOT NULL,
`identyfikator` text NOT NULL,
`tytul` text NOT NULL,
`kwota` decimal(10,2) NOT NULL DEFAULT '0.00',
`saldo` decimal(10,2) NOT NULL DEFAULT '0.00',
`ts_ksiegowania` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`hasz` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `visa` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_op` date NOT NULL DEFAULT '0000-00-00',
`data_ks` date NOT NULL DEFAULT '0000-00-00',
`tytul` text NOT NULL,
`kwota` decimal(10,2) NOT NULL DEFAULT '0.00',
`prowizja` decimal(10,2) NOT NULL DEFAULT '0.00',
`ts_ksiegowania` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`hasz` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_op` date NOT NULL DEFAULT '0000-00-00',
`data_ks` date NOT NULL DEFAULT '0000-00-00',
`opis` text NOT NULL,
`identyfikator` text NOT NULL,
`tytul` text NOT NULL,
`kwota` decimal(10,2) NOT NULL DEFAULT '0.00',
`saldo` decimal(10,2) NOT NULL DEFAULT '0.00',
`ts_ksiegowania` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`hasz` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `visa` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_op` date NOT NULL DEFAULT '0000-00-00',
`data_ks` date NOT NULL DEFAULT '0000-00-00',
`tytul` text NOT NULL,
`kwota` decimal(10,2) NOT NULL DEFAULT '0.00',
`prowizja` decimal(10,2) NOT NULL DEFAULT '0.00',
`ts_ksiegowania` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`hasz` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Przykładowy skrypt uruchamiający
Dodatkowo wykonuje backup bazy do katalogu backup (jeśli coś się zmieniło).#!/bin/sh
perl kb24_getcsv.pl
perl kb24_mysql.pl visa
perl kb24_mysql.pl ror
mysqldump -ukb24 -pxxxxxxxxxxxxxxxxxxxxx kb24 | grep -v 'dump completed' > kb24.sql
diff kb24.sql backup/kb24.sql > /dev/null
if [ $? -eq 1 ]; then
mv backup/kb24.sql backup/kb24-`date "+%s"`.sql
cp kb24.sql backup/kb24.sql
fi
perl kb24_getcsv.pl
perl kb24_mysql.pl visa
perl kb24_mysql.pl ror
mysqldump -ukb24 -pxxxxxxxxxxxxxxxxxxxxx kb24 | grep -v 'dump completed' > kb24.sql
diff kb24.sql backup/kb24.sql > /dev/null
if [ $? -eq 1 ]; then
mv backup/kb24.sql backup/kb24-`date "+%s"`.sql
cp kb24.sql backup/kb24.sql
fi
Przykładowe zapytania statystyczne
-- date range
SELECT 'ror', min(data_op), max(data_op) FROM ror union SELECT 'visa', min(data_op), max(data_op) FROM visa;
-- income
SELECT data_op,identyfikator,substr(tytul,1,60),kwota FROM ror WHERE kwota > 0 ORDER BY data_op DESC LIMIT 20;
-- monthly income + outcome
SELECT substring(data_op,1,7) AS ym, sum(IF(kwota>0,kwota,0)) AS income, sum(IF(kwota<0,kwota,0)) AS outcome FROM ror GROUP BY ym;
-- yearly income + outcome + monthly average
SELECT substring(data_op,1,4) AS r, sum(IF(kwota>0,kwota,0)) AS income, round(sum(IF(kwota>0,kwota,0))/12,2) AS average_in, sum(IF(kwota<0,kwota,0)) AS outcome, round(sum(IF(kwota<0,kwota,0))/12,2) AS average_out FROM ror GROUP BY r;
-- employers
SELECT identyfikator, sum(kwota) AS suma, count(kwota) AS num FROM ror WHERE kwota>0 GROUP BY substr(identyfikator,1,15) ORDER BY suma DESC LIMIT 20;
-- emploees
SELECT identyfikator, sum(kwota) AS suma, count(kwota) AS num FROM ror WHERE kwota<0 GROUP BY substr(identyfikator,1,15) ORDER BY suma LIMIT 20;
-- highest incomes
SELECT data_op, identyfikator, kwota,tytul FROM ror WHERE kwota>0 ORDER BY kwota DESC LIMIT 10;
-- highest outcomes
SELECT data_op, identyfikator, kwota,tytul FROM ror WHERE kwota<0 ORDER BY kwota ASC LIMIT 10;
-- biggest visa payments
SELECT data_op,tytul,kwota FROM visa ORDER BY kwota DESC LIMIT 10;
-- busy months
SELECT us.ym, sum(us.payment_number) AS payment_sum FROM (SELECT substring(data_op,1,7) AS ym, count(*) AS payment_number FROM ror WHERE kwota < 0 GROUP BY ym union SELECT substring(data_op,1,7) AS ym, count(*) AS payment_number FROM visa GROUP BY ym ORDER BY ym) AS us GROUP BY us.ym ORDER BY payment_sum DESC;
-- number of transfers
SELECT substring(data_op,1,7) AS ym, count(*) AS num FROM ror WHERE kwota<0 AND opis LIKE '%elekt.kanaly%' GROUP BY ym ORDER BY ym;
-- yearly number of transfers + monthly average
SELECT substring(data_op,1,4) AS r, count(*) AS num, round(count(*)/12,1) AS average_num FROM ror WHERE kwota<0 AND opis LIKE '%elekt.kanaly%' GROUP BY r;
-- monthly payments by pattern (eg. %BP%), aggregated ror+visa
SELECT ym, sum(kwota) FROM (SELECT substring(data_op,1,7) AS ym, tytul, -kwota AS kwota FROM ror WHERE tytul LIKE '%BP%' OR tytul LIKE '%statoil%' union ALL SELECT substring(data_op,1,7) AS ym, tytul, kwota FROM visa WHERE tytul LIKE '%BP%' OR tytul LIKE '%statoil%' ORDER BY ym) AS s1 GROUP BY ym;
SELECT 'ror', min(data_op), max(data_op) FROM ror union SELECT 'visa', min(data_op), max(data_op) FROM visa;
-- income
SELECT data_op,identyfikator,substr(tytul,1,60),kwota FROM ror WHERE kwota > 0 ORDER BY data_op DESC LIMIT 20;
-- monthly income + outcome
SELECT substring(data_op,1,7) AS ym, sum(IF(kwota>0,kwota,0)) AS income, sum(IF(kwota<0,kwota,0)) AS outcome FROM ror GROUP BY ym;
-- yearly income + outcome + monthly average
SELECT substring(data_op,1,4) AS r, sum(IF(kwota>0,kwota,0)) AS income, round(sum(IF(kwota>0,kwota,0))/12,2) AS average_in, sum(IF(kwota<0,kwota,0)) AS outcome, round(sum(IF(kwota<0,kwota,0))/12,2) AS average_out FROM ror GROUP BY r;
-- employers
SELECT identyfikator, sum(kwota) AS suma, count(kwota) AS num FROM ror WHERE kwota>0 GROUP BY substr(identyfikator,1,15) ORDER BY suma DESC LIMIT 20;
-- emploees
SELECT identyfikator, sum(kwota) AS suma, count(kwota) AS num FROM ror WHERE kwota<0 GROUP BY substr(identyfikator,1,15) ORDER BY suma LIMIT 20;
-- highest incomes
SELECT data_op, identyfikator, kwota,tytul FROM ror WHERE kwota>0 ORDER BY kwota DESC LIMIT 10;
-- highest outcomes
SELECT data_op, identyfikator, kwota,tytul FROM ror WHERE kwota<0 ORDER BY kwota ASC LIMIT 10;
-- biggest visa payments
SELECT data_op,tytul,kwota FROM visa ORDER BY kwota DESC LIMIT 10;
-- busy months
SELECT us.ym, sum(us.payment_number) AS payment_sum FROM (SELECT substring(data_op,1,7) AS ym, count(*) AS payment_number FROM ror WHERE kwota < 0 GROUP BY ym union SELECT substring(data_op,1,7) AS ym, count(*) AS payment_number FROM visa GROUP BY ym ORDER BY ym) AS us GROUP BY us.ym ORDER BY payment_sum DESC;
-- number of transfers
SELECT substring(data_op,1,7) AS ym, count(*) AS num FROM ror WHERE kwota<0 AND opis LIKE '%elekt.kanaly%' GROUP BY ym ORDER BY ym;
-- yearly number of transfers + monthly average
SELECT substring(data_op,1,4) AS r, count(*) AS num, round(count(*)/12,1) AS average_num FROM ror WHERE kwota<0 AND opis LIKE '%elekt.kanaly%' GROUP BY r;
-- monthly payments by pattern (eg. %BP%), aggregated ror+visa
SELECT ym, sum(kwota) FROM (SELECT substring(data_op,1,7) AS ym, tytul, -kwota AS kwota FROM ror WHERE tytul LIKE '%BP%' OR tytul LIKE '%statoil%' union ALL SELECT substring(data_op,1,7) AS ym, tytul, kwota FROM visa WHERE tytul LIKE '%BP%' OR tytul LIKE '%statoil%' ORDER BY ym) AS s1 GROUP BY ym;
