Created
March 30, 2016 11:13
-
-
Save ronnywang/642a8881e73644b79e68dcc3052dbed9 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| $files = glob("outputs/*_10-1_*"); | |
| usort($files, function($a, $b) { | |
| preg_match('#outputs/([0-9]*)-([0-9]*)_10-1_([0-9]*).csv#', $a, $matches); | |
| $a = $matches[1] * 10000 + $matches[2] * 100 + $matches[3]; | |
| preg_match('#outputs/([0-9]*)-([0-9]*)_10-1_([0-9]*).csv#', $b, $matches); | |
| $b = $matches[1] * 10000 + $matches[2] * 100 + $matches[3]; | |
| return $a - $b; | |
| }); | |
| $result = array(); | |
| foreach ($files as $f) { | |
| error_log($f); | |
| preg_match('#outputs/([0-9]*)-([0-9]*)_10-1_([0-9]*).csv#', $f, $matches); | |
| $year = $matches[1]; | |
| $month = $matches[2]; | |
| $fp = fopen($f, 'r'); | |
| $group = null; | |
| while ($row = fgetcsv($fp)) { | |
| $line = trim(implode('', $row)); | |
| if (false) { | |
| } elseif ($line == "資產別資產總額現金及\n約當現金存放央行及拆借銀行同業透過損益按公允價值衡量之金融資產應收款項透支、貼現備供出售持有至到期不動產其他資產") { | |
| fgetcsv($fp); // by Asset 及放款金融資產日金融資產及設備 | |
| fgetcsv($fp); // 銀行別Total AssetsCash and Alike Equivalent CashDeposits with CBC & Claims on Other BanksFinancial Assets at Fair Value through Profit or LossAmounts ReceivableOverdrafts, Discount and LoansAvailable-for-Sale Financial AssetsHeld-to-maturity Financial assetsFixed AssetsOther Assets | |
| fgetcsv($fp); // by Bank | |
| $columns = explode(',', '資產總額,現金及約當現金,存放央行及拆借銀行同業,透過損益按公允價值衡量之金融資產,應收款項,透支、貼現及放款,備供出售金融資產,持有至到期日金融資產,不動產及設備,其他資產'); | |
| $columns = array_map(function($v) { return '資產別:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == "資產別資產總額現金及\n約當現金存放央行及\n拆借銀行同業透過損益按公允價值衡量之金融資產附賣回票券及債券投資應收\n款項貼現及\n放款備供出售\n金融資產持有至到期日金融資產不動產其他\n資產") { | |
| fgetcsv($fp); // by Asset, ,,,,,,,,,及設備, | |
| fgetcsv($fp); // "Available-for-Sale Financial AssetsHeld-to-maturity Financial assets" | |
| fgetcsv($fp); // "信合社別 \nby Credit CooperativeTotal\nAssetsCash and Alike Equivalent CashDeposits with CBC & Claims on Other BanksFinancial Assets at Fair Value through Profit or LossSecurities Purchased under R/SAmounts ReceivableDiscount and LoansProperty\nand EquipmentOther\nAssets" | |
| $columns = explode(',', '資產總額,現金及約當現金,存放央行及拆借銀行同業,透過損益按公允價值衡量之金融資產,附賣回票券及債券投資,應收款項,貼現及放款,備供出售金融資產,持有至到期日金融資產,不動產及設備,其他資產'); | |
| $columns = array_map(function($v) { return '資產別:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == "負債及股東權益負債及\n權益央行、銀行同業存款及融資附買回票券\n及債券負債應付款項存款及匯款其他\n負債權益 Equities") { | |
| fgetcsv($fp); // Liabilities & Equities,,,, , ,,, ,股 金,資本公積,保留盈餘 | |
| fgetcsv($fp); // "信合社別 \nby Credit CooperativeLiabilities & EquitiesDue to the CBC & Other BanksSecurities Sold under R/PAmounts\nPayableDeposits and\nRemittancesOther Liabilities Capital\nStockReserve\nFundRetained Earnings" | |
| $columns = explode(',', '負債及權益,央行、銀行同業存款及融資,附買回票券及債券負債,應付款項,存款及匯款,,其他負債,權益,權益股金,權益資本公積,權益保留盈餘'); | |
| $columns = array_map(function($v) { return '負債及權益:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == "負債及權益負債及\n權益銀行暨同業透支及拆借透過損益按公允價值衡量之金融負債附買回票券及債券負債應付款項公司債其他負債權益 Equities") { | |
| fgetcsv($fp); // Liabilities& Equities,,,,,,,, ,股 本,資本公積 | |
| fgetcsv($fp); // 銀行別 by Bank,Liabilities & Equities,Due to the Other Banks,Financial Liabilities at Fair Value through Profit or Loss,Securities Sold under R/P,Amounts Payable ,Coporate Bonds,Other Liabilities, ,Capital Stock,Reserve Fund | |
| $columns = explode(',', '負債及權益,銀行暨同業透支及拆借,透過損益按公允價值衡量之金融負債,附買回票券及債券負債,應付款項,公司債,其他負債,權益,權益股本,權益資本公積'); | |
| $columns = array_map(function($v) { return '負債及權益:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == "資產別資產總額現金及\n約當現金存放央行及拆借銀行同業公平價值變動列入損益之金融資產應收款項透支、貼現備供出售持有至到期固定資產其他資產") { | |
| fgetcsv($fp); // by Asset, ,,,, ,及放款,金融資產,日金融資產, , | |
| fgetcsv($fp); // '銀行別Total AssetsCash and Alike Equivalent CashDeposits with CBC & Claims on Other BanksFinancial Assets at Fair Value through Profit or LossAmounts ReceivableOverdrafts, Discount and LoansAvailable-for-Sale Financial AssetsHeld-to-maturity Financial assetsFixed AssetsOther Assets' | |
| fgetcsv($fp); // 'by Bank' | |
| $columns = explode(',', '資產總額,現金及約當現金,存放央行及拆借銀行同業,公平價值變動列入損益之金融資產,應收款項,透支、貼現及放款,備供出售金融資產,持有至到期日金融資產,固定資產,其他資產'); | |
| $columns = array_map(function($v) { return '資產別:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == "負債及權益負債及\n權益央行、銀行同業存款及融資郵匯轉存款存款及匯款金融債券其他負債權益 Equities") { | |
| fgetcsv($fp); // Capital StockReserve FundRetained Earnings | |
| fgetcsv($fp); // 銀行別 \nby BankLiabilities & EquitiesDue to the CBC & Other BanksDeposit Replaced by the Taiwan Post Co.Deposits and RemittancesBank DebenturesOther Liabilities 股 本資本公積保留盈餘 | |
| fgetcsv($fp); // Capital StockReserve FundRetained Earnings | |
| $columns = explode(',', '負債及權益,央行、銀行同業存款及融資,郵匯轉存款,存款及匯款,金融債券,其他負債,權益,權益股本,權益資本公積,權益保留盈餘'); | |
| $columns = array_map(function($v) { return '負債及權益:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == '負債及股東權益負債及股東權益央行、銀行同業存款及融資郵匯轉存款存款及匯款金融債券其他負債股東權益 Equities') { | |
| fgetcsv($fp); // 'Capital StockReserve FundRetained Earnings' | |
| fgetcsv($fp); // 銀行別 by Bank,Liabilities & Equities,Due to the CBC & Other Banks,Deposit Replaced by the Taiwan Post Co.,Deposits and Remittances,Bank Debentures,Other Liabilities, ,股 本,資本公積,保留盈餘 | |
| fgetcsv($fp); // ,,,,,,, ,Capital Stock,Reserve Fund,Retained Earnings | |
| $columns = explode(',', '負債及權益,央行、銀行同業存款及融資,郵匯轉存款,存款及匯款,金融債券,其他負債,權益,權益股本,權益資本公積,權益保留盈餘'); | |
| $columns = array_map(function($v) { return '負債及權益:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == "資產別 by Asset資產總額現金及\n約當現金存放央行及拆借銀行同業公平價值變動列入損益之金融資產應收款項透支、貼現備供出售持有至到期固定資產其他資產") { | |
| fgetcsv($fp); // , ,,,, ,及放款,金融資產,日金融資產, , | |
| fgetcsv($fp); // 銀行別,Total Assets,Cash and Alike Equivalent Cash,Deposits with CBC & Claims on Other Banks,Financial Assets at Fair Value through Profit or Loss,Amounts Receivable,Overdrafts, Discount and Loans,Available-for-Sale Financial Assets,Held-to-maturity Financial assets,Fixed Assets,Other Assets | |
| fgetcsv($fp); // 'by Bank' | |
| $columns = explode(',', '資產總額,現金及約當現金,存放央行及拆借銀行同業,公平價值變動列入損益之金融資產,應收款項,透支、貼現及放款,備供出售金融資產,持有至到期日金融資產,固定資產,其他資產'); | |
| $columns = array_map(function($v) { return '資產別:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == "負債及股東權益負債及股東權益央行、同業存款\n(含郵匯轉存)及融資應付款項存款及匯款總行及國外\n聯行拆放其他負債股東權益 Equities") { | |
| fgetcsv($fp); // Liabilities & Equities,,,, ,, , ,專撥營業資金,保留盈餘,權益調整數 | |
| fgetcsv($fp); // 銀行別 by Bank,Liabilities & Equities,Due to the CBC & Other Banks,Amounts Payable ,Deposits and Remittances,Due to Head Office & other Overseas Branches ,Other Liabilities, ,,, | |
| fgetcsv($fp); // 'Working CapitalRetained EarningsDue Adjustment' | |
| $columns = explode(',', '負債及權益,央行、同業存款(含郵匯轉存)及融資,應付款項,存款及匯款,總行及國外聯行拆放,其他負債,權益,權益專撥營業資金,權益保留盈餘,權益調整數'); | |
| $columns = array_map(function($v) { return '負債及權益:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == '資產別資產總額現金及存拆放銀行同業公平價值變動列入損益之金融資產附賣回票券及債券投資應收款項備供出售金融資產持有至到期日金融資產採權益法之股權投資固定資產其他資產') { | |
| fgetcsv($fp); // by Asset,,,,,,,,,, | |
| fgetcsv($fp); // '銀行別 \nby BankTotal AssetsCash & Claims on Other BanksFinancial Assets at Fair Value through Profit or LossSecurities Purchased under R/SAmounts ReceivableAvailable-for-Sale Financial AssetsHeld-to-maturity Financial assetsEquity Investment, Equity MethodFixed AssetsOther Assets' | |
| $columns = explode(',', '資產總額,現金及存拆放銀行同業,公平價值變動列入損益之金融資產,附賣回票券及債券投資,應收款項,備供出售金融資產,持有至到期日金融資產,採權益法之股權投資,固定資產,其他資產'); | |
| $columns = array_map(function($v) { return '資產別:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == '負債及股東權益負債及股東權益銀行暨同業透支及拆借公平價值變動列入損益之金融負債附買回票券及債券負債應付款項公司債其他負債股東權益 Equities') { | |
| fgetcsv($fp); // Liabilities& Equities,,,,,,,, ,股 本,資本公積 | |
| fgetcsv($fp); // '銀行別 \nby BankLiabilities & EquitiesDue to the Other BanksFinancial Liabilities at Fair Value through Profit or LossSecurities Sold under R/PAmounts Payable Coporate BondsOther Liabilities Capital StockReserve Fund' | |
| $columns = explode(',', '負債及權益,銀行暨同業透支及拆借,公平價值變動列入損益之金融負債,附買回票券及債券負債,應付款項,公司債,其他負債,權益,權益股本,權益資本公積'); | |
| $columns = array_map(function($v) { return '負債及權益:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == "資產別 by Asset資產總額現金及\n約當現金存放央行及拆借銀行同業透過損益按公允價值衡量之金融資產應收款項透支、貼現備供出售持有至到期不動產其他資產") { | |
| fgetcsv($fp); // 及放款金融資產日金融資產及設備 | |
| fgetcsv($fp); // 銀行別Total AssetsCash and Alike Equivalent CashDeposits with CBC & Claims on Other BanksFinancial Assets at Fair Value through Profit or LossAmounts ReceivableOverdrafts, Discount and LoansAvailable-for-Sale Financial AssetsHeld-to-maturity Financial assetsFixed AssetsOther Assets | |
| fgetcsv($fp); // by Bank | |
| $columns = explode(',', '資產總額,現金及約當現金,存放央行及拆借銀行同業,透過損益按公允價值衡量之金融資產,應收款項,透支、貼現及放款,備供出售金融資產,持有至到期日金融資產,不動產及設備,其他資產'); | |
| $columns = array_map(function($v) { return '資產別:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == "負債及權益負債及\n權益央行、同業存款\n(含郵匯轉存)及融資應付款項存款及匯款總行及國外\n聯行拆放其他負債權益 Equities") { | |
| fgetcsv($fp); // Liabilities & Equities 專撥營業\n資金保留盈餘權益\n調整數 | |
| fgetcsv($fp); // 銀行別 \nby BankLiabilities & EquitiesDue to the CBC & Other BanksAmounts Payable Deposits and RemittancesDue to Head Office & other Overseas Branches Other Liabilities | |
| fgetcsv($fp); // Working CapitalRetained EarningsDue Adjustment | |
| $columns = explode(',', '負債及權益,央行、同業存款(含郵匯轉存)及融資,應付款項,存款及匯款,總行及國外聯行拆放,其他負債,權益,權益專撥營業資金,權益保留盈餘,權益調整數'); | |
| $columns = array_map(function($v) { return '負債及權益:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == '資產別資產總額現金及存拆放銀行同業透過損益按公允價值衡量之金融資產附賣回票券及債券投資應收款項備供出售金融資產持有至到期日金融資產採權益法之股權投資不動產其他資產') { | |
| fgetcsv($fp); // ' by Asset及設備' | |
| fgetcsv($fp); // '銀行別 \nby BankTotal AssetsCash & Claims on Other BanksFinancial Assets at Fair Value through Profit or LossSecurities Purchased under R/SAmounts ReceivableAvailable-for-Sale Financial AssetsHeld-to-maturity Financial assetsEquity Investment, Equity MethodFixed AssetsOther Assets' | |
| $columns = explode(',', '資產總額,現金及存拆放銀行同業,透過損益按公允價值衡量之金融資產,附賣回票券及債券投資,應收款項,備供出售金融資產,持有至到期日金融資產,採權益法之股權投資,不動產及設備,其他資產'); | |
| $columns = array_map(function($v) { return '資產別:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == '負債及權益負債及權益銀行暨同業透支及拆借透過損益按公允價值衡量之金融負債附買回票券及債券負債應付款項公司債其他負債權益 Equities') { | |
| fgetcsv($fp); // 'Liabilities& Equities 股 本資本公積' | |
| fgetcsv($fp); // '銀行別 \nby BankLiabilities & EquitiesDue to the Other BanksFinancial Liabilities at Fair Value through Profit or LossSecurities Sold under R/PAmounts Payable Coporate BondsOther Liabilities Capital StockReserve Fund' | |
| $columns = explode(',', '負債及權益,銀行暨同業透支及拆借,透過損益按公允價值衡量之金融負債,附買回票券及債券負債,應付款項,公司債,其他負債,權益,權益股本,權益資本公積'); | |
| $columns = array_map(function($v) { return '負債及權益:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == '資產別資產總額現金及存放公平價值變動列入損益之金融資產附賣回票券及債券投資貼現及放款備供出售金融資產持有至到期日金融資產固定資產其他資產') { | |
| fgetcsv($fp); //' by Asset 央行(合庫)及' | |
| fgetcsv($fp); // '拆借銀行同業' | |
| fgetcsv($fp); // '銀行別 \nby BankTotal AssetsCash and Alike Equivalent CashFinancial Assets at Fair Value through Profit or LossSecurities Purchased under R/SDiscount and LoansAvailable-for-Sale Financial AssetsHeld-to-maturity Financial assetsFixed AssetsOther Assets' | |
| $columns = explode(',', '資產總額,現金及存放央行(合庫)及拆借銀行同業,公平價值變動列入損益之金融資產,附賣回票券及債券投資,貼現及放款,備供出售金融資產,持有至到期日金融資產,固定資產,其他資產'); | |
| $columns = array_map(function($v) { return '資產別:' . $v; }, $columns); | |
| continue; | |
| } elseif ($line == "負債及股東權益負債及\n股東權益央行、銀行同業存款及融資附買回票券\n及債券負債應付款項存款及匯款其他負債社員權益 Equities of Members") { | |
| fgetcsv($fp); // 'Liabilities & Equities 股 金公積及盈餘' | |
| fgetcsv($fp); // '銀行別 \nby BankLiabilities & EquitiesDue to the CBC & Other BanksSecurities Sold under R/SAmounts PayableDeposits and RemittancesOther Liabilities Capital StockReserve Fund & Retained Earnings' | |
| $columns = explode(',', '負債及權益,央行、銀行同業存款及融資,附買回票券及債券負債,應付款項,存款及匯款,其他負債,權益,權益股金,權益公積及盈餘'); | |
| $columns = array_map(function($v) { return '負債及權益:' . $v; }, $columns); | |
| continue; | |
| } | |
| if (in_array($line, array( | |
| '資料來源：金融機構申報資料。', | |
| 'Source：Provided by individual institution.', | |
| '說 明：#係金融控股公司之子公司。', | |
| 'Note：# refer to the subsidary of financial holding companies.', | |
| ' 2.『對中小企業放款餘額』占放款％係指占各銀行國內總分行之放款(含催收款)之百分比。', | |
| '3.自101年1月起，本表所稱中小企業，除符合「中小企業認定標準」之中小企業外，另增列經中小企業信用保證基金保證符合「商業登記法」', | |
| '第五條之小規模商業及認定視同中小企業者，爰本統計資料亦配合更新，並追溯至101年1月。', | |
| '說 明：本表自102年1月起包含大陸地區銀行在臺分行資料。', | |
| '', | |
| ))) { | |
| continue; | |
| } | |
| foreach (array( | |
| '10-1 金融機構資產負債簡表', | |
| '10-1 Abridged Balance Sheet', | |
| 'Notes：', | |
| ) as $check_line) { | |
| if (strpos($line, $check_line) === 0) { | |
| continue 2; | |
| } | |
| } | |
| if ($line == '（１）本國銀行（全行）Domestic Banks (All Branches)' or $line == '（1）本國銀行（全行）Domestic Banks (All Branches)' | |
| ) { | |
| $group = '本國銀行全行'; | |
| continue; | |
| } elseif ($line == '（２）外國銀行在臺分行（含OBU）Local Branches of Foreign Banks (Include OBU)' or $line == '（2）外國銀行在臺分行（含OBU）Local Branches of Foreign Banks (Include OBU)') { | |
| $group = '外國銀行在臺分行'; | |
| continue; | |
| } elseif ($line == '（3）大陸地區銀行在臺分行 Local Branches of Mainland Chinese Banks') { | |
| $group = '大陸地區銀行在臺分行'; | |
| continue; | |
| } elseif ($line == '（３）票券金融公司 Bills Finance Companies' or $line == '（4）票券金融公司 Bills Finance Companies') { | |
| $group = '票券金融公司'; | |
| continue; | |
| } elseif ($line == '（４）信用合作社 Credit Cooperatives' or $line == '（5）信用合作社 Credit Cooperatives') { | |
| $group = '信用合作社'; | |
| continue; | |
| } | |
| if ($row[count($row) - 1] == '單位：新臺幣百萬元 NT$ Million') { | |
| continue; | |
| } | |
| if (preg_match('#^[0-9.－ -]+$#u', implode('', array_slice($row, 1)))) { | |
| $en_row = fgetcsv($fp); | |
| $bank_name = str_replace(' ', '', array_shift($row)); | |
| if (strpos($bank_name, '#')) { | |
| $bank_name = trim($bank_name, '#'); | |
| $is_child = 'Y'; | |
| } else { | |
| $is_child = 'N'; | |
| } | |
| $row = array_map(function($v) { | |
| $v = str_replace(' ', '', $v); | |
| $v = str_replace('－', '', $v); | |
| return $v; | |
| }, $row); | |
| $key = $year . $month . $group . $bank_name; | |
| if (array_key_exists($key, $result)) { | |
| $result[$key][6] = array_merge($result[$key][6], array_combine($columns, $row)); | |
| } else { | |
| $result[$key] = array($year, $month, $group, $bank_name, $en_row[0], $is_child, array_combine($columns, $row)); | |
| } | |
| continue; | |
| } | |
| print_r($row); | |
| echo "\"" . str_replace("\n", "\\n", $line) . "\"\n"; | |
| echo trim(str_replace("\n", '', implode(',', $row))) . "\n"; | |
| exit; | |
| } | |
| } | |
| $columns = array(); | |
| foreach ($result as $record) { | |
| foreach ($record[6] as $k => $v) { | |
| $columns[$k][$record[2]] ++; | |
| } | |
| } | |
| $output = fopen('php://output', 'w'); | |
| $groups = array( | |
| '本國銀行全行', | |
| '外國銀行在臺分行', | |
| '票券金融公司', | |
| '信用合作社', | |
| '大陸地區銀行在臺分行', | |
| ); | |
| //fputcsv($output, array_merge(array('種類'), $groups)); | |
| unset($columns['負債及權益:']); | |
| foreach ($columns as $k => $counts) { | |
| //fputcsv($output, array_merge(array($k), array_map(function($g) use ($counts) { return array_key_exists($g, $counts) ? 'Y' : ''; }, $groups))); | |
| } | |
| fputcsv($output, array_merge(array( | |
| '年', | |
| '月', | |
| '分類', | |
| '銀行別', | |
| '銀行英文', | |
| '金融控股公司之子公司', | |
| ), array_keys($columns))); | |
| foreach ($result as $record) { | |
| fputcsv($output, array_merge( | |
| array_slice($record, 0, 6), | |
| array_map(function($c) use ($record) { return array_key_exists($c, $record[6]) ? $record[6][$c]: ''; }, array_keys($columns)) | |
| )); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment