{"id":645,"date":"2013-10-13T12:25:55","date_gmt":"2013-10-13T03:25:55","guid":{"rendered":"http:\/\/www.sambuichi.jp\/?p=645"},"modified":"2014-07-25T10:23:41","modified_gmt":"2014-07-25T01:23:41","slug":"xbrl-gl%e9%96%b2%e8%a6%a7%e3%82%b5%e3%83%bc%e3%83%93%e3%82%b9%e8%a9%a6%e4%bd%9c23","status":"publish","type":"post","link":"https:\/\/www.sambuichi.jp\/?p=645&lang=en","title":{"rendered":"XBRL GL instance viewing service[Prototype](2\/3)"},"content":{"rendered":"

Views: 122<\/p>

Store XBRL GL instance documents in database<\/h1>\n

In order to calculate summary for period and\/or account numbers, we store contents of XBRL GL instance documents.<\/p>\n

In this case, we use PostgreSQL database after processed by PHP’s XML DOM functions.
\n
\nWe need getElementsByTagNameNS for processing elements having namespace definition.<\/p>\n

$glcor = ‘http:\/\/www.xbrl.org\/taxonomy\/int\/gl\/cor\/2003-08-29\/’;
\n$dom -> getElementsByTagNameNS($glcor, ‘accountingEntries’);<\/p>\n

The similar method with previous sample was used for handling structures of entryDetails element.<\/p>\n

We connect to PostgreSQL database with PDO. Following style of coding was used to execute a sql command.<\/p>\n

$stmt = $pdo -> prepare($sql);<\/p>\n

[php] XML parsing with DOM and database handling with PDO<\/p>\n

\n
\r\n    $dom = new DOMDocument;\r\n    $dom->load($filename);\r\n\r\n    $glcor = 'http:\/\/www.xbrl.org\/taxonomy\/int\/gl\/cor\/2003-08-29\/';\r\n    $glbus = 'http:\/\/www.xbrl.org\/taxonomy\/int\/gl\/bus\/2003-08-29\/';\r\n    $glmuc = 'http:\/\/www.xbrl.org\/taxonomy\/int\/gl\/muc\/2003-08-29\/';\r\n\r\n    $entries = $dom -> getElementsByTagNameNS($glcor, 'accountingEntries');\r\n    if (count($entries)) :\r\n        foreach ($entries as $entry) :\r\n            \/*\r\n             * entryHeader\r\n             *\/\r\n            $entryHeader = $entry -> getElementsByTagNameNS($glcor, 'entryHeader');\r\n            if (count($entryHeader)) :\r\n                foreach ($entryHeader as $header) :\r\n                    $enteredDate = $header\r\n                        -> getElementsByTagNameNS($glcor, 'enteredDate')\r\n                        -> item(0)\r\n                        -> nodeValue;\r\n                    $enteredDate = trim($enteredDate);\r\n                    $sourceJournalDescription = $header\r\n                        -> getElementsByTagNameNS($glbus, 'sourceJournalDescription')\r\n                        -> item(0)\r\n                        -> nodeValue;\r\n                    $sourceJournalDescription = trim($sourceJournalDescription);\r\n                    $entryNumber = $header\r\n                        -> getElementsByTagNameNS($glcor, 'entryNumber')\r\n                        -> item(0)\r\n                        -> nodeValue;\r\n                    $entryNumber = trim($entryNumber);\r\n                    \/*\r\n                     * INSERT TABLE\r\n                     *\/\r\n                    $sql = \"INSERT INTO entryheaders(\";\r\n                    $sql .= \"accountingEnrtiesID, enteredDate, sourceJournalDescription, entryNumber\";\r\n                    $sql .= \") \";\r\n                    $sql .= \"VALUES(\";\r\n                    $sql .= \":accountingEnrtiesID, :enteredDate,\";\r\n                    $sql .= \":sourceJournalDescription, :entryNumber\";\r\n                    $sql .= \") \";\r\n                    $stmt = $pdo -> prepare($sql);\r\n                    $stmt -> bindParam(':accountingEnrtiesID', $accountingEnrtiesID);\r\n                    $stmt -> bindParam(':enteredDate', $enteredDate);\r\n                    $stmt -> bindParam(':sourceJournalDescription', $sourceJournalDescription);\r\n                    $stmt -> bindParam(':entryNumber', $entryNumber);\r\n                    try {\r\n                        $stmt -> execute();\r\n                    } catch( PDOExecption $e ) {\r\n                        print \"Error!: \".$e->getMessage().\"<br>\";\r\n                    }\r\n                    \/\/ Get current sequence id value\r\n                    $sql = \"SELECT currval('entryheaders_id_seq') AS seq\";\r\n                    $stmt = $pdo -> query($sql);\r\n                    while ($row = $stmt -> fetch(PDO::FETCH_ASSOC)) {\r\n                        $entryHeadersID = $row['seq'];\r\n                    }\r\n                endforeach;\r\n            endif;\r\n        endforeach;\r\n    endif;<\/code><\/pre>\n<\/div>\n
[sql] table definition<\/span><\/p>\n
\n
\r\n-- accountingEnrties\r\nCREATE TABLE accountingEnrties (\r\n    -- documentInfo\r\n    id SERIAL8 NOT NULL,\r\n    entriesType VARCHAR(32),\r\n    uniqueID VARCHAR(32),\r\n    language VARCHAR(32),\r\n    creationDate  VARCHAR(10),\r\n    creator  VARCHAR(64),\r\n    entriesComment  VARCHAR(128),\r\n    periodCoveredStart VARCHAR(10),\r\n    periodCoveredEnd  VARCHAR(10),\r\n    sourceApplication  VARCHAR(32),\r\n    defaultCurrency  VARCHAR(32),\r\n    -- entityInformation\r\n    -- entityPhoneNumber \r\n    phoneNumber VARCHAR(16),\r\n    -- entityFaxNumberStructure\r\n    entityFaxNumber VARCHAR(16), \r\n    -- entityEmailAddressStructure \r\n    entityEmailAddress  VARCHAR(64),\r\n    -- organizationIdentifiers \r\n    organizationIdentifier VARCHAR(16),\r\n    organizationDescription VARCHAR(128),\r\n    -- organizationAddress \r\n    organizationAddressStreet VARCHAR(128),\r\n    organizationAddressCity VARCHAR(128), \r\n    organizationAddressStateOrProvince VARCHAR(128), \r\n    organizationAddressCountry VARCHAR(128),\r\n    organizationAddressZipOrPostalCode VARCHAR(16), \r\n    PRIMARY KEY (id)\r\n);\r\n\r\n-- entryHeader\r\nCREATE TABLE entryHeaders (\r\n    id SERIAL8 NOT NULL,\r\n    accountingEnrtiesID INT8,\r\n    postedDate VARCHAR(10),\r\n    enteredBy VARCHAR(32),\r\n    enteredDate VARCHAR(10),\r\n    sourceJournalID VARCHAR(16),\r\n    sourceJournalDescription VARCHAR(128),\r\n    entryOrigin VARCHAR(64),\r\n    entryType VARCHAR(10),\r\n    entryNumber VARCHAR(16),\r\n    entryComment VARCHAR(16),\r\n    bookTaxDifference VARCHAR(32),\r\n    PRIMARY KEY (id),\r\n    FOREIGN KEY (accountingEnrtiesID)  REFERENCES accountingEnrties ( id )\r\n);\r\n\r\n-- entryDetail\r\nCREATE TABLE entryDetails (\r\n    id SERIAL8 NOT NULL,\r\n    entryHeadersID INT8,\t\t\t\r\n    lineNumber VARCHAR(16),\r\n    -- account\r\n    accountMainID VARCHAR(32),\r\n    accountMainDescription VARCHAR(128),\r\n    accountPurposeCode VARCHAR(16),\r\n    accountType VARCHAR(32),\r\n    -- accountSub\r\n    accountSubDescription VARCHAR(128),\r\n    accountSubID VARCHAR(32),\r\n    accountSubType VARCHAR(32),\r\n    amount VARCHAR(16),\r\n    amountDecimals VARCHAR(16),\r\n    amountUnitref  VARCHAR(16),\r\n    amountMemo VARCHAR(128),\r\n    -- identifierReference\r\n    identifierCode VARCHAR(16),\r\n    -- identifierExternalReference\r\n    identifierAuthorityCode VARCHAR(16),\r\n    identifierAuthority VARCHAR(64),\r\n    identifierDescription VARCHAR(128),\r\n    identifierType VARCHAR(32),\r\n    -- identifierAddress\r\n    identifierStreet VARCHAR(128),\r\n    identifierCity VARCHAR(128),\r\n    identifierStateOrProvince VARCHAR(128),\r\n    identifierCountry VARCHAR(128),\r\n    identifierZipOrPostalCode VARCHAR(16),\r\n    documentType VARCHAR(16),\r\n    documentNumber VARCHAR(16),\r\n    documentReference VARCHAR(16),\r\n    documentDate VARCHAR(10),\r\n    documentLocation VARCHAR(64),\r\n    maturityDate VARCHAR(10),\r\n    terms VARCHAR(32),\r\n    -- measurable\r\n    measurableCode VARCHAR(16),\r\n    measurableID VARCHAR(64),\r\n    measurableDescription VARCHAR(128),\r\n    measurableQuantity  VARCHAR(64),\r\n    measurableUnitOfMeasure VARCHAR(16),\r\n    measurableCostPerUnit  VARCHAR(64),\r\n    measurableQualifier VARCHAR(16),\r\n    -- depreciationMortgage\r\n    dmJurisdiction VARCHAR(32),\r\n    -- taxes\r\n    taxAuthority  VARCHAR(16),\r\n    taxAmount  VARCHAR(64),\r\n    taxCode VARCHAR(16),\r\n    debitCreditCode VARCHAR(16),\r\n    postingDate VARCHAR(10),\r\n    postingStatus VARCHAR(16),\r\n    detailComment VARCHAR(128),\r\n    PRIMARY KEY (id),\r\n    FOREIGN KEY (entryHeadersID)  REFERENCES entryHeaders ( id )\r\n);\r\n<\/code><\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Views: 122Store XBRL GL instance documents in database In order to calculate summary for period and\/or account […]<\/p>\n","protected":false},"author":1,"featured_media":428,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[17],"tags":[],"_links":{"self":[{"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/posts\/645"}],"collection":[{"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=645"}],"version-history":[{"count":1,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/posts\/645\/revisions"}],"predecessor-version":[{"id":647,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/posts\/645\/revisions\/647"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/media\/428"}],"wp:attachment":[{"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=645"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=645"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=645"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}