Search Posts

XBRL GL閲覧サービス[試作](2/3)

Posted on | By webmaster

Visits: 676

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 )
);
Category: xBRL