弁財天

岐阜のパナマ文書から切り出し。

朝日◆新たな流出ファイル「バハマ・リークス」 https://t.co/ClJszV3k7k 「今年７月１日、「新たなリーク」と題するＩＣＩＪからのメッセージが、パナマ文書の取材・報道で提携する各国の記者たちに送られてきた」

— deepthroat (@gloomynews) 2016年9月22日

新たな流出ファイル「バハマ・リークス」 編集委員・奥山俊宏 2016年9月22日03時13分
　タックスヘイブン（租税回避地）の法人に関する大量の電子ファイルが新たに流出し、世界各国の記者たちの手に渡った。バハマの法人情報に関する報道プロジェクトをＩＣＩＪは「バハマ・リークス」と名付けた。
　今年７月１日、「新たなリーク」と題するＩＣＩＪからのメッセージが、パナマ文書の取材・報道で提携する各国の記者たちに送られてきた。南ドイツ新聞の記者が「漏洩（ろうえい）されたデータのセット」を受け取った、と伝える内容だった。
　８月上旬、朝日新聞などＩＣＩＪと提携する各国の報道機関の記者が１７万５千余の法人の情報にアクセスできるようになった。９月５日には、ファイルに名前のある関係者への直接取材を各国で一斉に開始した。
　タックスヘイブンに関する大量…

ＩＣＩＪはこれについて「新たなリーク」「バハマ・リークス」と呼ぶ。「バハマ文書」とはあえて呼ばないようだ。→租税回避地バハマで企業役員　欧州委前幹部、申告せず：朝日新聞デジタル https://t.co/BIwF2gEbEs

— 奥山俊宏 (@okuyamatoshi) 2016年9月21日

このヒトが土人の国担当w

パナマ文書(2016年5月10日版)を14017073で検索した結果。

$ perl panama_extract.pl 14017073|perl id2link.pl 
.ENTITY(10027092)[MEDSEARCH INVEST S.A./Switzerland/Panama Papers]
..INTERMEDIARIES(11000337)[VERTEX S.A./Switzerland/Panama Papers]ENTITY(10027092)[MEDSEARCH INVEST S.A./Switzerland/Panama Papers]
..OFFICER(12213348)[THE BEARER/Switzerland/Panama Papers]ENTITY(10027092)[MEDSEARCH INVEST S.A./Switzerland/Panama Papers]
..OFFICER(12213349)[VERTEX S.A./Switzerland/Panama Papers]ENTITY(10027092)[MEDSEARCH INVEST S.A./Switzerland/Panama Papers]
.ADDRESS(14017073)[4-7-7 NISHINOSHO GIFUSHI GIFUKEN JAPAN/Japan/Panama Papers]
.ENTITY(10027092)[MEDSEARCH INVEST S.A./Switzerland/Panama Papers]
.ADDRESS(14017073)[4-7-7 NISHINOSHO GIFUSHI GIFUKEN JAPAN/Japan/Panama Papers]
.12100665/similar/12213349
.= OFFICER(12100665)[VERTEX S.A./Switzerland/Panama Papers]
.12198103/similar/12213349
.= OFFICER(12198103)[VERTEX S.A./Switzerland/Panama Papers]
$ 

バハマリークスでall_edges表のスキーマが変わり5月の126万5691件から450万5673件に増加。
こりゃまた、おっそろしー関連データベース作ったなぁ。もはや税務署のレベルを遥かに超えてる。ここまでやってしまうとビッグブラザー登場みたいなw

パナマ文書で岐阜市西庄4-7-7からたどれたのはスイスのMEDSEARCH INVEST S.A.社だけだった。 バハマリークスでは岐阜市西庄4-7-7の住所から「人さらい」をたどれるよーになった。

関連が全く変わったのもあれば、変わらないものもある。 パナマ文書のall_edge.csv生成の関連付けプログラムに何かバグがあったみたいですなー。

バハマリークス(2016年9月21日版)を14017073で検索した結果。

$ perl bahama_extract.pl 14017073|perl id2link.pl 
.ENTITY(10002372)[MITSUBOSHI CORPORATION/Hong Kong/Panama Papers]
..INTERMEDIARIES(11001746)[ORION HOUSE SERVICES (HK) LIMITED/Hong Kong/Panama Papers]ENTITY(10002372)[MITSUBOSHI CORPORATION/Hong Kong/Panama Papers]
..OFFICER(12119000)[NAGATA SOICHIRO/Japan/Panama Papers]ENTITY(10002372)[MITSUBOSHI CORPORATION/Hong Kong/Panama Papers]
..OFFICER(12160432)[MOSSFON SUBSCRIBERS LTD./Samoa/Panama Papers]ENTITY(10002372)[MITSUBOSHI CORPORATION/Hong Kong/Panama Papers]
.ADDRESS(14017073)[4-7-7 NISHINOSHO GIFUSHI GIFUKEN JAPAN/Japan/Panama Papers]
$ 

icijdb=> select distinct(rel_type) from bahama_all_edges;
                   rel_type
----------------------------------------------
 register of director of
 vp / treas. asst sec
 same name as
 related entity
 vice president / gen coun /
 same company as
 vice president
 c.f.o.
 resigned
 clementi limited
 Nominee Secretary of
 treasurer
 corporate secretary
 pres / dir. / sec. / tres.
 director / shareholder of
 alt director
 ordinary director
 special director
 owner of
 dir / pres / vp / trea / sec
 chairman / director
 ind. non-exec. dir.
 permanent director
 director / secretary
 vice president / treasurer
 shareholder
 bruno a. roberts
 president
 attorney at law
 director/treasurer
 personal directorship of
 manager-corporate
 c.e.o. / pres. / dir.
 financial controller
 corporate director
 managing director
 director / treasurer / s
 dir / asst secretary
 vice president / c.f.o.
 pres. / dir. / sec.
 tax advisor of
 secretary
 dir. / sec. / treasure
 company
 assistant controller
 similar name and address as
 authorized
 director and shareholder of
 director / treasurer
 director / manager
 first vice president
 safekeeping of
 custodian of
 pres / ch.invest.of / di
 director / asst. sec
 executive director
 chairman of
 company secretary
 connected of
 sole signatory / beneficial owner of
 attorney in fact
 dir. / chrm. / vp / a.s.
 stockbroker of
 director - v / s
 pres / v.p.
 secretary / treasurer
 Nominee Protector of
 registered address
 ex vice president
 trust settlor of
 treasurer of
 director / sec
 director / beneficial owner of
 diretor
 officer
 vice president and
 dir / pres / secretary
 director / pres. / secr.
 gfs
 director / pres / treas
 asst. treasurer
 assistant treasurer
 president / dir / secret
 director / ass. sec.
 beneficiary of
 dep.chairman / tr. dir
 dir / pres / treas / sec
 t.c.
 president / secretary
 1st v.p. / director
 as. sec. / as. treas.
 director - vp / t
 non executive direct
 first director
 v.p / / senior trader
 asst. controller
 director / pres / secretary
 director / v.pres. / sec
 authorized signatory of
 class b director
 president / treasurer
 bank signatory of
 2nd vice president
 non
 assistant secretary of
 director / shareholder / beneficial owner of
 chief exec. officer
 vice president of
 company executive
 dir. / vp / sec
 director / c.e.o
 mr /
 sol director/president/secretary/treasurer
 director/president
 assistant director
 asst treas / director
 co-trustee of trust of
 alt. dir to ian fair
 power of attorney / shareholder of
 member of foundation council of
 dir / pres / sec / tre
 director of
 president of
 beneficiary, shareholder and director of
 pres. / chairman
 executive officer
 Nominee Director of
 director / officer
 entity similar to
 director / v.p.
 dir. / v. p. / a.s.
 Nominee Shareholder of
 exec. vice president
 director / c.o.o.
 power of attorney of
 pres. / secretary
 vice president / sec. / trea
 dir / vice president / cof
 vice president / director
 vp / treasurer / directo
 president / c.e.o
 v-president / director
 vice president/secretary
 trustee of trust of
 vice president / sec.
 sec
 vice president-finance
 same intermediary as
 corp. director
 Nominee Trust Settlor of
 secretary of
 member / shareholder of
 mng. director / china
 investment advisor of
 sole signatory of
 resident director of
 anderson
 v.p. / treas. / director
 vice president / sec. / dir.
 tur limited
 subscriber
 banker
 vice president / g.m.
 appointor of
 advisor to the board
 nominated person of
 deputy ch. / c.e.o
 presid / treas / dir
 shareholder (through julex foundation) of
 director / cob
 Nominee Beneficiary of
 president / treas.
 director / vp / secretar
 c.e.o. / secretary
 probably same officer as
 director (rami makhlouf) of
 general accountant of
 asst. secretary
 registered office
 businesswoman
 signatory of
 first beneficiary of
 unit trust register of
 chartered accountant
 same name and registration date as
 controller
 independent director
 Nominee Beneficial Owner of
 owner, director and shareholder of
 sole director / sec.
 general manager
 same address as
 director / treas / sec
 vice president / treas / sec
 authorized signatory
 second vice president
 general counsel
 records & registers of
 businessman
 chairman / board / c.e.o
 grantee of a mortgage of
 protector of
 register of shareholder of
 co.
 director / mgn. dir.
 ibc
 chief financial off
 sole director/president/secretary/treasurer
 chairman
 correspondent addr. of
 director / pres / 01
 c.e.o
 Nominee Investment Advisor of
 member
 successor protector of
 partner of
 assistant secretary
 sole director
 officer of
 registered agent
 reserve director of
 vp / treasurer
 president / diector
 director / assist sec
 pres / sec / director
 president and director of
 director / c.e.o / pres.
 to gary lane
 board representative of
 nominee name of
 beneficial owner of
 3rd vice president
 chief executive off
 accountant
 executive
 director / president
 dipl. ing.
 joint settlor of
 dir. and sec.
 sec / treas
 vice-chairman
 alternate director of
 vp / c.e.o.
 legal advisor of
 sole shareholder of
 pres. / ch.exec. / dir.
 chairman / president
 director / c.f.o.
 auth. representative of
 auditor of
 shareholder of
 dir / pres / treas
 director - p / t
 dir. v. pres. / tre
 company / director
 principal beneficiary of
 direc / secr. / treas.
 investment advisor
 corporation
 president / c.e.o / director
 intermediary of
 cramlington
 dir / asst treasurer
 director
 alternate director
 president - director of
 dir / vice president / treas
 dr. norbert marxer
 vice president / secretary
 chairman / pres / dir
 chief fin. officer
 treas. / director
 treasurer / asst. sec.
 authorised person / signatory of
(274 行)

icijdb=>

icijは関連付けに再び失敗してると思うぞw

以前のパナマ文書の関連付けは

icijdb=> select distinct(rel_type) from panama_all_edges;
      rel_type
--------------------
 intermediary_of
 registered_address
 similar
 underlying
 officer_of
(5 行)

icijdb=>

その他(else)の関連を以前の"officer_of"の関連と見なすロジックにしてしまうか。

                #elsif ($r->[1] eq "officer_of") {

バハマリークス解読ちうサンプル(where country_codes like '%JPN%')。
20161004_bahama_leaks_jp.zip
zip圧縮。10万行。右クリックでダウンロードしてね。

.csvファイルの解析と乱暴な表定義。
csv2table.pl
bahama_intermediaries表の
internal_id varchar(6),とならなければならないのに
--internal_id integer,と間違えるバグは修正済みw

#!/usr/bin/perl

use strict;

$| = 1;

use Text::CSV;

my $tn = `basename $ARGV[0] .csv`;
chomp $tn;
$tn = lc $tn;
$tn = "bahama_".$tn;

my $h = scalar(<>);
print $h . "\n";

chomp($h);

my @COL_LEN;
my @COL_TYPE;
my @COL_NULL;

my @colname = split(/,/, $h);
for (my $loop = 0; $loop <= $#colname; $loop++) {
        print STDERR "$loop $colname[$loop]\n";
        $COL_LEN[$loop] = 1;
        $COL_NULL[$loop] = "not null";
}

my $MAX_COLS=$#colname + 1;
print STDERR "MAX_COLS $MAX_COLS\n\n";

my $pp = Text::CSV->new({ allow_whitespace => 1, binary => 1 });

while(<>) {
        chomp;
        my $line = $_;
        $pp->parse($line);
        my @fs = $pp->fields($line);
        &column_type(@fs);
}

print "create table $tn (\n";

for (my $loop = 0; $loop <= $#colname; $loop++) {
        if ($COL_TYPE[$loop] eq "d" ) {
                print "$colname[$loop] integer";
                print " not null" if ($COL_NULL[$loop] ne "null");
        } else {
                print $colname[$loop] . " varchar(" . $COL_LEN[$loop] . ")";
                print " not null" if ($COL_NULL[$loop] ne "null");
        }
        print ",\n" if ($loop < $#colname);
}

print ");\n\n";

sub column_type {
    my (@fs) = @_;
    for (my $loop = 0; $loop <= $#colname; $loop++) {
        if ($COL_LEN[$loop] < length($fs[$loop])) {
            $COL_LEN[$loop] = length($fs[$loop]);
        }
        if ($fs[$loop] ne "") { # これね。
            if ($fs[$loop] =~ /^\d+$/) {
                $COL_TYPE[$loop] = "d" if ($COL_TYPE[$loop] ne "s");
            } else {
                $COL_TYPE[$loop] = "s";
            }
        }
        if ($fs[$loop] eq "") {
            $COL_NULL[$loop] = "null";
        }
    }
}

# End of FILE.

スキーマ bahama.sql

/*
==> Addresses.csv <==
address,icij_id,valid_until,country_codes,countries,node_id,sourceID
address,icij_id,valid_until,country_codes,countries,node_id,sourceID,note
*/

drop table bahama_addresses;
create table bahama_addresses (
address varchar(259),
icij_id varchar(53),
valid_until varchar(53),
country_codes varchar(3),
countries varchar(32),
node_id integer,
sourceID varchar(14),
note varchar(1));

create index bahama_addresses_node_id_idx on bahama_addresses(node_id);

/*
==> all_edges.csv <==
node_1,rel_type,node_2
node_1,rel_type,node_2,sourceID,valid_until,start_date,end_date
*/

drop table bahama_all_edges;

create table bahama_all_edges (
node_1 integer not null,
rel_type varchar(44) not null,
node_2 integer not null,
sourceID varchar(14),
valid_until varchar(53),
start_date varchar(11),
end_date varchar(11));

create index bahama_all_edges_node_1_idx on bahama_all_edges(node_1);
create index bahama_all_edges_node_2_idx on bahama_all_edges(node_2);

/*
==> Entities.csv <==
name,original_name,former_name,jurisdiction,jurisdiction_description,company_type,address,internal_id,incorporation_date,inactivation_date,struck_off_date,dorm_date,status,service_provider,ibcRUC,country_codes,countries,note,valid_until,node_id,sourceID
name,original_name,former_name,jurisdiction,jurisdiction_description,company_type,address,internal_id,incorporation_date,inactivation_date,struck_off_date,dorm_date,status,service_provider,ibcRUC,country_codes,countries,note,valid_until,node_id,sourceID
*/

drop table bahama_entities;

create table bahama_entities (
name varchar(128),
original_name varchar(196),
former_name varchar(115),
jurisdiction varchar(5),
jurisdiction_description varchar(34),
company_type varchar(46),
address varchar(269),
internal_id integer,
incorporation_date varchar(11),
inactivation_date varchar(11),
struck_off_date varchar(11),
dorm_date varchar(11),
status varchar(37),
service_provider varchar(26),
ibcRUC varchar(19),
country_codes varchar(15),
countries varchar(57),
note varchar(608),
valid_until varchar(53),
node_id integer,
sourceID varchar(14));

create index bahama_entities_node_id_idx on bahama_entities(node_id);

/*
==> Intermediaries.csv <==
name,internal_id,address,valid_until,country_codes,countries,status,node_id,sourceID
name,internal_id,address,valid_until,country_codes,countries,status,node_id,sourceID,note
*/

drop table bahama_intermediaries;
create table bahama_intermediaries (
name varchar(91),
internal_id varchar(10),
--internal_id integer,
address varchar(230),
valid_until varchar(53),
country_codes varchar(15),
countries varchar(51),
status varchar(34),
node_id integer,
sourceID varchar(14),
note varchar(1));

create index bahama_intermediaries_node_id_idx on bahama_intermediaries(node_id);

/*
==> Officers.csv <==
name,icij_id,valid_until,country_codes,countries,node_id,sourceID
name,icij_id,valid_until,country_codes,countries,node_id,sourceID,note
*/

drop table bahama_officers;
create table bahama_officers (
name varchar(200),
icij_id varchar(32),
valid_until varchar(53),
country_codes varchar(119),
countries varchar(303),
node_id integer,
sourceID varchar(14),
note varchar(1));
create index bahama_officers_node_id_idx on bahama_officers(node_id);

/* End of FILE. */

シェル bahama_insert.sh

#!/bin/sh

#cp 20160921/Addresses.csv 20160921/Addresses.csv.0
#strings 20160921/Addresses.csv.0 >20160921/Addresses.csv

#dos2unix 20160921/*.csv

#psql -h localhost -U icij -d icijdb -f sql/truncate.sql

perl bahama_insert.pl 20160921/Addresses.csv
perl bahama_insert.pl 20160921/Entities.csv
perl bahama_insert.pl 20160921/Intermediaries.csv
perl bahama_insert.pl 20160921/Officers.csv
perl bahama_insert.pl 20160921/all_edges.csv

bahama_insert.pl

#!/usr/bin/perl

use strict;
use utf8;
use strict;
use Encode;
use DBD::Pg;

my $dbh;
my $sth;

$dbh = DBI->connect("dbi:Pg:dbname=icijdb;host=localhost", "icij", "hogehoge");
$dbh->{pg_enable_utf8} = 1;

$| = 1;

use Text::CSV;

my $target;
$target = "bahama_addresses" if ($ARGV[0] =~ /addresses/i);
$target = "bahama_all_edges" if ($ARGV[0] =~ /all_edges/i);
$target = "bahama_entities" if ($ARGV[0] =~ /entities/i);
$target = "bahama_intermediaries" if ($ARGV[0] =~ /intermediaries/i);
$target = "bahama_officers" if ($ARGV[0] =~ /officers/i);

print "$target ...\n";
#exit(0);

my $h = scalar(<>);
print $h . "\n";

chomp($h);
my @colname = split(/,/, $h);
for (my $loop = 0; $loop <= $#colname; $loop++) {
        print STDERR "$loop $colname[$loop]\n";
}

my $MAX_COLS=$#colname + 1;
print "MAX_COLS $MAX_COLS\n";

my @COL_LEN;
my @COL_TYPE;
my @COL_NULL;

my $pp = Text::CSV->new({ allow_whitespace => 1, binary => 1 });


while(<>) {
        chomp;
        my $line = $_;
        $pp->parse($line);
        my @fs = $pp->fields($line);
        &insert_panama($target, @fs);
}

$dbh->disconnect;

sub insert_panama {
        my ($tn, @fs) = @_;
        my $sql = "INSERT INTO " . $tn . " (";
        for (my $loop = 0; $loop <= $#colname; $loop++) {
                $sql .= $colname[$loop];
                $sql .= ", " if ($loop < $#colname);
        }
        $sql .= ") values (";
        for (my $loop = 0; $loop <= $#colname; $loop++) {
            $fs[$loop] =~ s/\"//g;
            $fs[$loop] =~ s/\\//g;
            $fs[$loop] =~ s/\'/\'\'/g;
            $fs[$loop] =~ tr/ / /s;
            $fs[$loop] =~ s/ $//;
                if ($fs[$loop] eq "") {
                        $sql .= 'null';
                } else {
                        #$sql .= 'E\'' . decode('UTF-8',$fs[$loop]) . '\'';
                        $sql .= 'E\'' . $fs[$loop] . '\'';
                }
                $sql .= ", " if ($loop < $#colname);
        }
        $sql .= ")";
        #print $sql . "\n";
        $sth = $dbh->prepare($sql);
        $sth->execute() or print "[$sql]\n";
}

# End of FILE.

bahama_extract.pl

#!/usr/bin/perl

use utf8;
use strict;
use Encode;
use DBD::Pg;

my $dbh;
my $sth;

my $TARGET=$ARGV[0];

$dbh = DBI->connect("dbi:Pg:dbname=icijdb;host=localhost", "icij", "hogehoge");
$dbh->{pg_enable_utf8} = 1;

$| = 1;

my $sql;
#$sql = "select node_id, address, countries, sourceid from panama_addresses_20160921";
$sql = "select node_id, address, countries, sourceid from bahama_addresses \
where country_codes like '%JPN%'";

my $d = $dbh->selectall_arrayref($sql);

my @did;

if ($TARGET ne "") {
        &decode_edges(0, $TARGET, \@did);
        exit(0);
}

foreach my $r (@$d) {
        print "ENTITY NAME(" . $r->[1] . ")...\n";
        &decode_edges(0, $r->[0], \@did);
        print "ENTITY NAME(" . $r->[1] . ").\n";
        @did = ();
}
exit(0);

sub decode_edges() {
        my ($nest, $node_id, $did) = @_;
        my $sql = "select node_1, rel_type, node_2 from bahama_all_edges where node_1 = $node_id or node_2 = $node_id";

        #print STDERR "$sql\n";

        return if ($node_id eq ""); # not null 制約がなくなったのでw

        my $nest_s = "";
        for (my $loop = 0; $loop < $nest; $loop++) {
                #print ".";
                $nest_s .= ".";
        }

        my $e .= &decode_entity($node_id);
        print "$nest_s$e\n" if ($e ne "");

        for (my $loop = 0; $loop <= $#$did; $loop++) {
                if (@$did[$loop] == $node_id) {
                        #print $nest_s . " $node_id o\n";
                        return;
                }
        }

        push (@$did, $node_id);

        if ($nest > 10) {
                #print $nest_s . " $node_id x\n";
                return;
        }

        my $e = $dbh->selectall_arrayref($sql);
        foreach my $r (@$e) {
                #print $r->[0] . "/" . $r->[1] . "/" . $r->[2] . " ";
                #if ($r->[1] eq "registered_address") {
                if ($r->[1] eq "registered address") {
                        my $address2;
                        $address2 = &decode_address($r->[2]) if ($r->[0] == $node_id);
                        print "$nest_s$address2\n" if ($address2 ne "");
                        &decode_edges($nest + 1,$r->[0], $did) if ($r->[2] == $node_id);
                }
                #elsif ($r->[1] eq "intermediary_of") {
                elsif ($r->[1] eq "intermediary of") {
                        my $intermediary;
                        $intermediary = &decode_intermediaries($r->[0]) if ($r->[2] == $node_id);
                        $intermediary .= &decode_entity($r->[2]);
                        print encode('UTF-8',"$nest_s$intermediary\n") if ($intermediary ne "");
                        &decode_edges($nest + 1,$r->[2], $did) if ($r->[0] == $node_id);
                }
                elsif ($r->[1] eq "similar") {
                        print $nest_s . $r->[0] . "/" . $r->[1] . "/" . $r->[2] . "\n";
                        my $similar;
                        $similar = &decode_officer($r->[0]) if ($r->[2] == $node_id);
                        $similar = &decode_officer($r->[2]) if ($r->[0] == $node_id);
                        $similar .= &decode_entity($r->[2]);
                        print $nest_s . "= " . "$similar\n" if ($similar ne "");
                        &decode_edges($nest + 1,$r->[2], $did) if ($r->[0] == $node_id);
                }
                #elsif ($r->[1] eq "officer_of") {
                #elsif ($r->[1] eq "shareholder of") {
                else {
                        my $officer;
                        $officer = &decode_officer($r->[0], $r->[1]) if ($r->[2] == $node_id);
                        $officer .= &decode_entity($r->[2]);
                        print encode('UTF-8',"$nest_s$officer\n") if ($officer ne "");
                        &decode_edges($nest + 1,$r->[2], $did) if ($r->[0] == $node_id);
                }
                #else {
                #       print $r->[0] . "/" . $r->[1] . "/" . $r->[2];
                #       print $nest_s . " N/A\n";
                #}
        }
}

sub decode_address() {
        my ($node_id) = @_;
        #print  "ADDRESS(" . $node_id . ")"  . " -> ";

        my $sql = "select node_id, address, countries, sourceid \
from bahama_addresses where node_id = $node_id";

        my $d = $dbh->selectall_arrayref($sql);

        my $address = "";
        foreach my $r (@$d) {
                $address = "ADDRESS(" . $r->[0] . ")[" . $r->[1] . "/" . $r->[2] . "/" . $r->[3] . "]";
                #print encode('UTF-8', $address) . " -> ";
        }
        $address;
}

sub decode_officer() {
        my ($node_id, $rel_type) = @_;
        #print  "OFFICER(" . $node_id . ") -> ";

        my $sql = "select node_id, name, countries, sourceid \
from bahama_officers where node_id = $node_id";

        my $d = $dbh->selectall_arrayref($sql);

        my $officer = "";
        foreach my $r (@$d) {
                $officer = "OFFICER(" . $r->[0] . ")[" . $r->[1] . "/" . $r->[2] . "/" . $r->[3] . "] ($rel_type) ";
                #print  encode('UTF-8', $officer) . " -> ";
        }
        $officer;
}

sub decode_intermediaries() {
        my ($node_id) = @_;
        #print  "OFFICER(" . $node_id . ") -> ";

        my $sql = "select node_id, name, countries, sourceid \
from bahama_intermediaries where node_id = $node_id";

        my $d = $dbh->selectall_arrayref($sql);

        my $officer = "";
        foreach my $r (@$d) {
                $officer = "INTERMEDIARIES(" . $r->[0] . ")[" . $r->[1] . "/" . $r->[2] . "/" . $r->[3] . "]";
                #print  encode('UTF-8', $officer) . " -> ";
        }
        $officer;
}

sub decode_entity() {
        my ($node_id) = @_;

        #my $sql = "select node_id, name, countries, sourceid \
        my $sql = "select node_id, name, countries, sourceid, incorporation_date, inactivation_date, struck_off_date, dorm_date \
from bahama_entities where node_id = $node_id";

        my $d = $dbh->selectall_arrayref($sql);

        my $officer = "";
        foreach my $r (@$d) {
                #$officer = "ENTITY(" . $r->[0] . ")[" . $r->[1] . "/" . $r->[2] . "/" . $r->[3] . "]";
                $officer = "ENTITY(" . $r->[0] . ")[" . $r->[1] . "/" . $r->[2] . "/" . $r->[3] . "/" . $r->[4] . "," . $r->[5] . "," . $r->[6] . "," . $r->[7] . "]";
                #print  encode('UTF-8', $officer) . " -> ";
        }
        $officer;
}

$dbh->disconnect;

4月にパナマ文書で騒ぎになった会社をバハマ・リークスで 再検索
してみた。 ユニクロの山口県の本社住所が検索結果としてでてくる。

簡単に言えば検索結果のデータ構造で事件の真相を想像してみろという試みですな。マイニングロジックはひょっとしてTSAか？

バハマ・リークスで馬暁紅(Ma Xiaohong)を検索。

$ perl bahama_extract.pl 12190232|perl id2link.pl
ENTITY(10028363)[SKY BRIGHT DEVELOPMENT LTD./Hong Kong/Panama Papers/30-MAY-2011,,,]
.INTERMEDIARIES(11001746)[ORION HOUSE SERVICES (HK) LIMITED/Hong Kong/Panama Papers]ENTITY(10028363)[SKY BRIGHT DEVELOPMENT LTD./Hong Kong/Panama Papers/30-MAY-2011,,,]
.OFFICER(12190232)[MA XIAOHONG/China/Panama Papers] (shareholder of) ENTITY(10028363)[SKY BRIGHT DEVELOPMENT LTD./Hong Kong/Panama Papers/30-MAY-2011,,,]
ADDRESS(14069963)[ROOM 202; UNIT 1; 72#. BAWEI ROAD; ZHENXING DISTRICT; DANDONG; LIAONING; P.R. CHINA/China/Panama Papers]
$

おぉぉバハマ・リークスでは馬暁紅(Ma Xiaohong)の遼寧省丹東の住所まで確認できますたぁ。

おらー、まっとれよ。文科省の北朝鮮部隊をもうすぐ壊滅じゃぁぁ。
と思ってたらパナマ文書と同時に既に始末されてたみたいだ。

やっぱ、とっかかりは会社設立日(incorporation_date)か。
30-MAY-2011。うーむ、311の2カ月半後かぁ。
15年前じゃなかったのか？w