弁財天

ゴフマン「専門家を信じるのではなく、自分自身で考えて判断せよ」

(238037)[Mosfilmovskaya str. 70/627, Moscow, Russian Federation.] update3

カルロスゴーン逮捕の個人的な推察をここに書いておくw

20140123版のパナマ文書のスキーマには「中国語表記」のカラムがあるとこがw

三菱グループとロシアとシンガポールが関連に登場する。 MUFGが北朝鮮国境で中国人相手にマネロンしていた件。マレーシアの1MDBのマネロンでシンガポールとロスチャイルド銀行(スイス)、ゴールドマン・サックスが関与していた件。ナイジェリアではマネロンの別名のHSBC。そしてロシアとイランと北朝鮮のカネがダンスケ銀行エストニア支店でマネロンされていた話と噛み合うのだろうw。

perl 20140123_extract.pl 238037|perl id2link.pl
ADDRESS(238037)[Mosfilmovskaya str. 70/627, Moscow, Russian Federation.]…(2171行、1.2Mバイト)

perl 20140123_extract.pl 146999|perl id2link.pl
ENTITY(146999)[Mitsubishi Group Corporation]…(859行、475Kバイト)

$ cat 20140123_schema.sql
-- 20140123/countriesNW.csv
-- country_id;country_code;country_name

-- 0 country_id
-- 1 country_code
-- 2 country_name
-- MAX_COLS 3

create table offshore_20140123_countriesnw (
country_id varchar(3) not null,
country_code varchar(2) not null,
country_name varchar(48) not null
);
create index offshore_20140123_countries_nw_idx on offshore_20140123_countriesnw( country_id );

-- 20140123/edges_1DNW.csv
-- Unique_ID;Entity_ID1;Entity_ID2;description_;date_from;date_to;direction;chinesePos;linkType

-- 0 Unique_ID
-- 1 Entity_ID1
-- 2 Entity_ID2
-- 3 description_
-- 4 date_from
-- 5 date_to
-- 6 direction
-- 7 chinesePos
-- 8 linkType
-- MAX_COLS 9

create table offshore_20140123_edges_1dnw (
Unique_ID integer not null,
Entity_ID1 integer not null,
Entity_ID2 integer not null,
description_ varchar(29) not null,
date_from varchar(28),
date_to varchar(28),
direction integer not null,
chinesePos varchar(8) not null,
linkType varchar(2) not null
);

create index offshore_20140123_edges_1dnw_idx on offshore_20140123_edges_1dnw( Unique_ID,Entity_ID1,Entity_ID2 );
create index offshore_20140123_edges_1dnw_id1_idx on offshore_20140123_edges_1dnw( Entity_ID1 );
create index offshore_20140123_edges_1dnw_id2_idx on offshore_20140123_edges_1dnw( Entity_ID2 );

-- 20140123/node_countriesNW.csv
-- NODEID1;country_code;country_name;country_id

-- 0 NODEID1
-- 1 country_code
-- 2 country_name
-- 3 country_id
-- MAX_COLS 4

create table offshore_20140123_node_countriesnw (
NODEID1 integer not null,
country_code varchar(2) not null,
country_name varchar(33) not null,
country_id varchar(3)
);

create index offshore_20140123_node_countries_nw_idx on offshore_20140123_node_countriesnw( NODEID1 );

-- 20140123/nodesNW.csv
-- Unique_ID;subtypes_;Description_;searchField_;status;desc_status;type;desc_company_type;inc_dat;dorm_dat;juris;desc_jurisdiction;completeAddresses;agencyID;tax_stat;tax_stat_description

-- 0 Unique_ID
-- 1 subtypes_
-- 2 Description_
-- 3 searchField_
-- 4 status
-- 5 desc_status
-- 6 type
-- 7 desc_company_type
-- 8 inc_dat
-- 9 dorm_dat
-- 10 juris
-- 11 desc_jurisdiction
-- 12 completeAddresses
-- 13 agencyID
-- 14 tax_stat
-- 15 tax_stat_description
-- MAX_COLS 16

--drop table offshore_20140123_nodesnw;
create table offshore_20140123_nodesnw (
Unique_ID integer not null,
subtypes_ varchar(23) not null,
Description_ varchar(221),
searchField_ varchar(305),
status varchar(1),
desc_status varchar(37),
type varchar(5),
desc_company_type varchar(46),
inc_dat varchar(28),
dorm_dat varchar(28),
juris varchar(5),
desc_jurisdiction varchar(24),
completeAddresses varchar(269),
agencyID integer,
tax_stat varchar(5),
tax_stat_description varchar(53)
);

create index offshore_20140123_nodes_nw_idx on offshore_20140123_nodesnw( Unique_ID );

--
$ 
$ cat 20140123_extract.pl
#!/usr/bin/perl

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

my $dbh;
my $sth;

my $TARGET=$ARGV[0];
my $DATE=$ARGV[1];

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

$| = 1;

my $sql;
my %did;

&decode_edges(0, $TARGET, \%did) if ($TARGET ne "");
exit(0);

sub decode_edges() {
	my ($nest, $node_id, $did) = @_;
	my $sql = "select Unique_ID, Entity_ID1, Entity_ID2, description_, direction, chinesePos, linkType from offshore_20140123_edges_1dnw where Unique_ID = $node_id or Entity_ID1 = $node_id or Entity_ID2 = $node_id";

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

	if (defined($did->{$node_id})) {
        $did->{$node_id}++;
		#print $nest_s . "($node_id)x". $did->{$node_id}."\n";
		return;
	}

	$did->{$node_id}++;

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

    my $edge_s;
	my $e = $dbh->selectall_arrayref($sql);
	foreach my $r (@$e) {
        $edge_s = "[".$r->[3]."/".$r->[5]."/".$r->[6]."]";

		if ($r->[6] eq "OA"||$r->[6] eq "EA" ) {
			my $a;
			$a = &decode_nodes_nw($r->[0]);

			my $a2 = &decode_nodes_nw($r->[1]);
			$a .= $a2 if ($a2 ne "");

			my $a3 = &decode_nodes_nw($r->[2]);
			$a .= $a3 if ($a3 ne "");
			print encode("UTF8", "$nest_s$edge_s$a\n") if ($a ne "");

			&decode_edges($nest + 1,$r->[0], $did) if ($r->[0] != $node_id);
			&decode_edges($nest + 1,$r->[1], $did) if ($r->[1] != $node_id);
		}
		elsif ($r->[6] eq "EO"||$r->[6] eq "EI"||$r->[6] eq "EU"||$r->[6] eq "EE") { # Officer
			my $o;
			$o = &decode_nodes_nw($r->[0]);

			my $o2 = &decode_nodes_nw($r->[1]);
			$o .= $o2 if ($o2 ne "");

			my $o3 = &decode_nodes_nw($r->[2]);
			$o .= $o3 if ($o3 ne "");

			print encode("UTF8", "$nest_s$edge_s$o"."\n") if ($o ne "");
			&decode_edges($nest + 1,$r->[1], $did) if ($r->[4] == 0 && $r->[1] != $node_id);
			&decode_edges($nest + 1,$r->[2], $did) if ($r->[4] == 1 && $r->[2] != $node_id);
		}
		else {
			print encode("UTF8", $r->[6] . "/" . $r->[5] . "/" . $r->[3] . "/" . $r->[0] . "/" . $r->[1] . "/" . $r->[2]);
			print $nest_s . "N/A\n";
		}
	}
}

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

	#my $sql = "select node_id, address, countries, sourceid from panama_addresses where node_id = $node_id";
	my $sql = "select Unique_ID, subtypes_, Description_, searchField_, completeAddresses from offshore_20140123_nodesnw where Unique_ID = $node_id";

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

	my $address = "";
	foreach my $r (@$d) {
		$address = $r->[1]."(" . $r->[0] . ")[" . $r->[2];
        $address .= "/" . $r->[3] if ($r->[3] ne "" && $r->[3] ne $r->[2]);
        $address .= "/" . $r->[4] if ($r->[4] ne "" && $r->[4] ne $r->[2]);
        $address .= "]";
	}
	$address;
}

$dbh->disconnect;

投稿されたコメント:

コメント
コメントは無効になっています。