The Fork
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
Kredyt Bank - KB24

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

download kb24_getcsv.pl 3.5K
download kb24_mysql.pl 5.4K

Przykł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

 

Skrypt tworzący bazę

download create.sql 961B
CREATE 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;

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
 

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;
 

Last modified: 2006-11-04 02:26:40 (v427)   Valid XHTML 1.0

(c) 2009 Marcin Gryszkalis