Views: 120
XBRL GLインスタンス文書のデータベース登録
期間別・科目別に文書をまとめて表示させるため、データベースを使用します。
ここでは、サーバーにアップロードしたインスタンス文書をPHPのDOMのXML機能を使って解析し、PostgreSQLに登録しました。
DOMで名前空間付きの要素を処理するために、
$glcor = ‘http://www.xbrl.org/taxonomy/int/gl/cor/2003-08-29/’;
$dom -> getElementsByTagNameNS($glcor, ‘accountingEntries’);
といった方法で、getElementsByTagNameNSを使用しました。
entryDetails要素までの階層の繰り返しは、(1/3)の投稿と同じイメージです。
PostgreSQLデータベースへは、PDOで接続していますので、
$stmt = $pdo -> prepare($sql);
のようなコーディングで、SQL文を実行します。
[php] DOMを使用したXML解析とPDOでのデータベース登録＜抜粋＞
$dom = new DOMDocument;
$dom->load($filename);
$glcor = 'http://www.xbrl.org/taxonomy/int/gl/cor/2003-08-29/';
$glbus = 'http://www.xbrl.org/taxonomy/int/gl/bus/2003-08-29/';
$glmuc = 'http://www.xbrl.org/taxonomy/int/gl/muc/2003-08-29/';
$entries = $dom -> getElementsByTagNameNS($glcor, 'accountingEntries');
if (count($entries)) :
foreach ($entries as $entry) :
/*
* entryHeader
*/
$entryHeader = $entry -> getElementsByTagNameNS($glcor, 'entryHeader');
if (count($entryHeader)) :
foreach ($entryHeader as $header) :
$enteredDate = $header
-> getElementsByTagNameNS($glcor, 'enteredDate')
-> item(0)
-> nodeValue;
$enteredDate = trim($enteredDate);
$sourceJournalDescription = $header
-> getElementsByTagNameNS($glbus, 'sourceJournalDescription')
-> item(0)
-> nodeValue;
$sourceJournalDescription = trim($sourceJournalDescription);
$entryNumber = $header
-> getElementsByTagNameNS($glcor, 'entryNumber')
-> item(0)
-> nodeValue;
$entryNumber = trim($entryNumber);
/*
* INSERT TABLE
*/
$sql = "INSERT INTO entryheaders(";
$sql .= "accountingEnrtiesID, enteredDate, sourceJournalDescription, entryNumber";
$sql .= ") ";
$sql .= "VALUES(";
$sql .= ":accountingEnrtiesID, :enteredDate,";
$sql .= ":sourceJournalDescription, :entryNumber";
$sql .= ") ";
$stmt = $pdo -> prepare($sql);
$stmt -> bindParam(':accountingEnrtiesID', $accountingEnrtiesID);
$stmt -> bindParam(':enteredDate', $enteredDate);
$stmt -> bindParam(':sourceJournalDescription', $sourceJournalDescription);
$stmt -> bindParam(':entryNumber', $entryNumber);
try {
$stmt -> execute();
} catch( PDOExecption $e ) {
print "Error!: ".$e->getMessage()."<br>";
}
// Get current sequence id value
$sql = "SELECT currval('entryheaders_id_seq') AS seq";
$stmt = $pdo -> query($sql);
while ($row = $stmt -> fetch(PDO::FETCH_ASSOC)) {
$entryHeadersID = $row['seq'];
}
endforeach;
endif;
endforeach;
endif;[sql]テーブル定義
-- accountingEnrties
CREATE TABLE accountingEnrties (
-- documentInfo
id SERIAL8 NOT NULL,
entriesType VARCHAR(32),
uniqueID VARCHAR(32),
language VARCHAR(32),
creationDate VARCHAR(10),
creator VARCHAR(64),
entriesComment VARCHAR(128),
periodCoveredStart VARCHAR(10),
periodCoveredEnd VARCHAR(10),
sourceApplication VARCHAR(32),
defaultCurrency VARCHAR(32),
-- entityInformation
-- entityPhoneNumber
phoneNumber VARCHAR(16),
-- entityFaxNumberStructure
entityFaxNumber VARCHAR(16),
-- entityEmailAddressStructure
entityEmailAddress VARCHAR(64),
-- organizationIdentifiers
organizationIdentifier VARCHAR(16),
organizationDescription VARCHAR(128),
-- organizationAddress
organizationAddressStreet VARCHAR(128),
organizationAddressCity VARCHAR(128),
organizationAddressStateOrProvince VARCHAR(128),
organizationAddressCountry VARCHAR(128),
organizationAddressZipOrPostalCode VARCHAR(16),
PRIMARY KEY (id)
);
-- entryHeader
CREATE TABLE entryHeaders (
id SERIAL8 NOT NULL,
accountingEnrtiesID INT8,
postedDate VARCHAR(10),
enteredBy VARCHAR(32),
enteredDate VARCHAR(10),
sourceJournalID VARCHAR(16),
sourceJournalDescription VARCHAR(128),
entryOrigin VARCHAR(64),
entryType VARCHAR(10),
entryNumber VARCHAR(16),
entryComment VARCHAR(16),
bookTaxDifference VARCHAR(32),
PRIMARY KEY (id),
FOREIGN KEY (accountingEnrtiesID) REFERENCES accountingEnrties ( id )
);
-- entryDetail
CREATE TABLE entryDetails (
id SERIAL8 NOT NULL,
entryHeadersID INT8,
lineNumber VARCHAR(16),
-- account
accountMainID VARCHAR(32),
accountMainDescription VARCHAR(128),
accountPurposeCode VARCHAR(16),
accountType VARCHAR(32),
-- accountSub
accountSubDescription VARCHAR(128),
accountSubID VARCHAR(32),
accountSubType VARCHAR(32),
amount VARCHAR(16),
amountDecimals VARCHAR(16),
amountUnitref VARCHAR(16),
amountMemo VARCHAR(128),
-- identifierReference
identifierCode VARCHAR(16),
-- identifierExternalReference
identifierAuthorityCode VARCHAR(16),
identifierAuthority VARCHAR(64),
identifierDescription VARCHAR(128),
identifierType VARCHAR(32),
-- identifierAddress
identifierStreet VARCHAR(128),
identifierCity VARCHAR(128),
identifierStateOrProvince VARCHAR(128),
identifierCountry VARCHAR(128),
identifierZipOrPostalCode VARCHAR(16),
documentType VARCHAR(16),
documentNumber VARCHAR(16),
documentReference VARCHAR(16),
documentDate VARCHAR(10),
documentLocation VARCHAR(64),
maturityDate VARCHAR(10),
terms VARCHAR(32),
-- measurable
measurableCode VARCHAR(16),
measurableID VARCHAR(64),
measurableDescription VARCHAR(128),
measurableQuantity VARCHAR(64),
measurableUnitOfMeasure VARCHAR(16),
measurableCostPerUnit VARCHAR(64),
measurableQualifier VARCHAR(16),
-- depreciationMortgage
dmJurisdiction VARCHAR(32),
-- taxes
taxAuthority VARCHAR(16),
taxAmount VARCHAR(64),
taxCode VARCHAR(16),
debitCreditCode VARCHAR(16),
postingDate VARCHAR(10),
postingStatus VARCHAR(16),
detailComment VARCHAR(128),
PRIMARY KEY (id),
FOREIGN KEY (entryHeadersID) REFERENCES entryHeaders ( id )
);
