CREATE TEMPORARY TABLE ExpectedRoe
(
stock_code text NOT NULL,
expected_roe double precision
);
DO $$DECLARE r record;
BEGIN
FOR r IN SELECT distinct(stock_code) as stock_code FROM BalanceSheet
LOOP
insert into ExpectedRoe(stock_code, expected_roe)
select
r.stock_code, expected_roe
from ...; -- 用 r.stock_code 當參數
END LOOP;
END$$;
SELECT * from ExpectedRoe where expected_roe >= 0.1
order by expected_roe desc;
某個例子:
DROP TABLE IF EXISTS AllRoe;
CREATE TEMPORARY TABLE AllRoe
(
stock_code text NOT NULL,
roe double precision
);
DO $$DECLARE r record;
BEGIN
FOR r IN SELECT distinct(stock_code) as stock_code FROM BalanceSheet
LOOP
insert into AllRoe(stock_code, roe)
select
r.stock_code, T.roe
from
(
with IndividualRoe as
(
select activity_date, roe from roe(r.stock_code)
)
select roe from IndividualRoe
where activity_date in
(
select max(activity_date) from IndividualRoe
)
) as T;
END LOOP;
END$$;
select * from AllRoe;
《熱門族群》大立光明年獲利可望年增50%，帶動玉晶光大漲 2012/11/30 12:51 時報資訊 【時報記者張漢綺台北報導】光學鏡頭廠－大立光 (3008) 業績可望一路旺到明年第1季，獲外資法人及投信一致看好，外資法人普遍預估，大立光明年獲利可望較今年成長50%，每股盈餘自50元起跳，在法人買盤力挺下，大立光今天股價再度強攻，盤中一度大漲近4%，逼近800元關卡，再創2011年9月22日以來新高價，也帶動玉晶光 (3406) 股價同步走高。 智慧型手機愈來愈強調照相功能，且手機薄型化，尤其是蘋果iPhone 5後鏡頭薄型化、前鏡頭改為HD，使得平均售價均提升，亦讓大立光在蘋果單隻手機鏡頭產值增加60%，法人預估，大立光供應給蘋果iPhone 5的後鏡頭供貨比率將自4S的50%增加至70%，前鏡頭比率則提升至40%，預估蘋果對大立光明年營收產值可望達1倍；除蘋果iPhone 5手機外，宏達電 (2498) HTC、Nokia、RIM在今年第4季到明年第1季力推多款新機，尤其是明年第1季旗鑑機陸續上市，而大陸智慧型手機的相機鏡頭亦自5M轉至8M，受惠高階鏡頭訂單集中，且新產能自10月底至明年第1季陸續開出，讓大立光成為最大受惠者，業績可望一路旺到明年。 大立光10月合併營收為24.16億元，較9月營收成長34%、較去年同月成長72%，創下單月歷史新高，累計前10月合併營收139.04億元，較去年同期成長4%；由於11月合併營收可望再創新高，以10月及11月合併營收來預估，外資普遍認為，大立光第4季營收可望較第3季成長約50%，獲利在經濟規模及良率提升下，可望季增達70%，全年每股盈餘可望上看35元，明年獲利可望較今年成長50%，每股盈餘自50元起跳。
select
Y.roe / X.pbr as expected_roe
from
(
select
pbr
from
ListedCoStatistics
where
stock_code = '3008'
and report_date in
(
select max(report_date) from ListedCoStatistics where stock_code = '3008'
)
) as X,
(
with W as
(
with T as
(
select
A.activity_date,
A.report_date,
A.number as shareholder_equity,
B.number as net_income
from
BalanceSheet as A,
IncomeStmt as B
where
A.stock_code = B.stock_code
and A.activity_date = B.activity_date
and A.report_date = B.report_date
and A.item = '股東權益總計'
and B.item = '合併總損益'
and A.report_type = 'C'
and B.report_type = 'C'
and A.stock_code = '3008'
and A.number != 0
and B.number != 0
)
select
V.activity_date,
V.annual_adjusted_net_income/V.shareholder_equity as roe
from
(
select
T.activity_date,
T.shareholder_equity,
case
when date_part('month', T.activity_date) = 3 then T.net_income * 4/1
when date_part('month', T.activity_date) = 6 then T.net_income * 4/2
when date_part('month', T.activity_date) = 9 then T.net_income * 4/3
else T.net_income
end as annual_adjusted_net_income
from
T,
(
select activity_date, max(report_date) as report_date from T
group by activity_date
) as U
where
T.activity_date = U.activity_date
and T.report_date = U.report_date
order by T.activity_date
) as V
)
select roe from W where activity_date in (select max(activity_date) from W)
) as Y
二代健保費率4.91% 40100薪以下保費增 作者： 記者楊湘鈞、黃文彥╱台北報導 | 聯合新聞網 – 2012年11月30日 上午2:38 二代健保費率底定，行政院長陳冲拍板調降一般保費費率，由百分之五點一七降為百分之四點九一。政院表示，明年元旦新制上路後，百分之八十七的民眾負擔不增或減少，另有百分之十三的民眾（約三百萬人）因補充保費增加，整體保費負擔增加。 衛生署樂觀估計，二代健保可維持財務至二○一六總統大選年。 二代健保明年一月一日上路，保費除了現行由薪資計算的「一般保費」之外，將再加上「補充保費」，計費項目包括高於四個月的獎金、兼職所得、租金收入、執行業務收入、股利所得及利息等六項。 雖然衛生署估計，調降一般保險費率至百分之四點九一後，百分之八十七的民眾負擔不增或減少，但隨著二代健保上路，政府將一併取消現行對收入較低民眾的「健保費率調漲差額專案補貼」，月薪四萬零一百元以下收入的民眾，繳交的一般保費將提高，每人每月保費將增加廿一元至四十四元不等。 行政院發言人鄭麗文說，二代健保已建立收支連動機制，未來將由監理、費協整併後的健保會，整體考量保險收入與醫療給付；如預期發生財務缺口，可運用收支連動機制，由健保會評估後提出最適當的費率，報衛生署轉行政院核定，以確保財務平衡。 健保局前總經理朱澤民表示，補助財源最終還是來自納稅人的錢，健保局應該專心進行健保改革，讓救濟弱勢族群回歸社會救助制度。 民間監督健保聯盟發言人滕西華表示，健保支出每年以百分之五的速度成長，收入成長率卻僅百分之一點七，加上景氣變動，很難永續經營。
import platform
os = platform.system()
if os == 'Windows':
from . import wget_win as wget_private
elif os == 'Darwin':
from . import wget_mac as wget_private
else:
raise Exception('Please add support for your platform')
def wget(cmdline):
wget_private.wget(cmdline)
就是一個 wget method，舉例來說：url --waitretry=3 -O dest_file抓哪個網址，waitretry 設為 3 ((這是很重要的設定，畢竟網路環境不穩，無法無限制的等待，也不能不等待，我們得選中庸之道))，存在哪個目的檔。
import os
def wget(cmdline):
wget = os.path.abspath('./core/thirdparty/wget/wget.exe')
assert os.path.isfile(wget)
wget_cmdline = '''{wget} {cmdline}'''.format(wget=wget, cmdline=cmdline)
os.system(wget_cmdline)
import os
def wget(cmdline):
wget_cmdline = '''wget {cmdline}'''.format(cmdline=cmdline)
os.system(wget_cmdline)
src_fd = open(src_file, 'rb')
src_content = src_fd.read()
src_fd.close()
content = None
try:
content = html.fromstring(src_content.decode('big5-hkscs'))
except UnicodeDecodeError as e:
self.LOGGER.debug(e)
content = html.fromstring(src_content.decode('gb18030'))
for table in content.xpath('//html/body/div/table/tr/td[@width="99%"]/table/tr/td/table/tr/td/table'):
for yearly_dataset in table.xpath('./tr'):
yearly_data = yearly_dataset.xpath('./td/text()')
if len(yearly_data) is 7:
activity_date = self.get_date(yearly_data[0])
if not activity_date:
continue
record = [
self.STOCK_CODE,
activity_date,
self.get_double(yearly_data[1]),
self.get_double(yearly_data[2]),
self.get_double(yearly_data[3]),
self.get_double(yearly_data[4]),
self.get_double(yearly_data[5]),
self.get_double(yearly_data[6]),
]
create table if not exists StockDividend
(
creation_dt timestamp default current_timestamp,
stock_code text not null,
activity_date date not null,
cash_dividend double precision,
stock_dividend_from_retained_earnings double precision,
stock_dividend_from_capital_reserve double precision,
stock_dividend double precision,
total_dividend double precision,
profit_sharing_percentage double precision,
unique (stock_code, activity_date)
);
DB_TYPE = 'postgres'如果將來想換 database，直接在這邊設定，加入適當的 implementation 就可以了。
from .. import db_config
db_type = db_config.DB_TYPE
if db_type == 'sqlite':
from .sqlite import insertion_factory as factory_private
elif db_type == 'postgres':
from .postgres import insertion_factory as factory_private
class InsertionFactory():
@staticmethod
def insertion():
return factory_private.InsertionFactory().insertion()
from . import insertion
class InsertionFactory():
@staticmethod
def insertion():
return insertion.Insertion()
import postgresql
class Insertion():
def __init__(self):
self.CONN_STRING = 'pq://stocktotal:stocktotal@localhost:5432/stocktotal'
self.DB_CONN = None
def open(self):
self.DB_CONN = postgresql.open(self.CONN_STRING)
def close(self):
self.DB_CONN.close()
self.DB_CONN = None
def insert(self, sql_cmd, record):
try:
fixed_record = [None if _.strip() == '' else _ for _ in record]
prepared_stmt = self.DB_CONN.prepare(sql_cmd)
prepared_stmt(*fixed_record)
except postgresql.exceptions.UniqueError:
pass
def insert_stock_dividend(self, record):
sql_cmd = \
'''
insert into StockDividend(
stock_code,
activity_date,
cash_dividend,
stock_dividend_from_retained_earnings,
stock_dividend_from_capital_reserve,
stock_dividend,
total_dividend,
profit_sharing_percentage
) values(
$1,
$2::text::date,
$3::text::float8,
$4::text::float8,
$5::text::float8,
$6::text::float8,
$7::text::float8,
$8::text::float8
)
'''
self.insert(sql_cmd, record)
import csv
import logging
import os
from datetime import date
class StandardcharteredSource():
def __init__(self):
from ..db.insertion import insertion_factory
self.LOGGER = logging.getLogger()
self.URL_TEMPLATE = ''
self.STOCK_CODE = None
self.SOURCE_TYPE = None
self.HTML_DIR = ''
self.CSV_DIR = ''
self.DB_INSERTION = insertion_factory.InsertionFactory().insertion()
def source(self, stock_code):
self.STOCK_CODE = stock_code
self.source_url_to_html(self.HTML_DIR)
self.source_html_to_csv(self.HTML_DIR, self.CSV_DIR)
self.source_csv_to_db(self.SOURCE_TYPE, self.CSV_DIR, self.DB_INSERTION)
def source_url_to_html(self, dest_dir):
if not os.path.exists(dest_dir):
os.makedirs(dest_dir)
url = self.__get_url()
dest_file = self.get_filename(dest_dir, 'html')
self.__wget(url, dest_file)
def source_html_to_csv(self, src_dir, dest_dir):
pass
def source_csv_to_db(self, source_type, src_dir, db_insertion):
src_file = self.get_filename(src_dir, 'csv')
if not os.path.isfile(src_file):
return
self.LOGGER.debug('''{src_file} => db'''.format(src_file=src_file))
fd = open(src_file, 'r')
csv_reader = csv.reader(fd)
INSERT_SOURCE_TYPE_MAP = {
'capital_structure_summary': db_insertion.insert_capital_structure_summary,
'stock_dividend': db_insertion.insert_stock_dividend,
}
db_insertion.open()
for r in csv_reader:
INSERT_SOURCE_TYPE_MAP[source_type](r)
self.LOGGER.debug(r)
db_insertion.close()
fd.close()
def get_filename(self, src_dir, ext):
return os.path.join(src_dir, self.STOCK_CODE + '.' + ext)
# Get date from ROC year to date (Python data type)
def get_date(self, literal):
try:
return date(int(literal) + 1911, 1, 1)
except ValueError:
return None
def get_double(self, literal):
literal = literal.replace(',','')
try:
return float(literal)
except ValueError:
return None
def __get_url(self):
return self.URL_TEMPLATE % self.STOCK_CODE
def __wget(self, url, dest_file):
from ..base import wget
cmdline = '''\"{url}\" --waitretry=3 -O \"{dest_file}\"'''.format(url=url, dest_file=dest_file)
wget.wget(cmdline)
import csv
import os
from lxml import html
from . import standardchartered_source
class StockDividendSource(standardchartered_source.StandardcharteredSource):
def __init__(self):
standardchartered_source.StandardcharteredSource.__init__(self)
self.URL_TEMPLATE = '''http://estockweb.standardchartered.com.tw/z/zc/zcc/zcc_%s.djhtm'''
self.SOURCE_TYPE = 'stock_dividend'
self.HTML_DIR = '../dataset/stock_dividend/html/'
self.CSV_DIR = '../dataset/stock_dividend/csv/'
def source_html_to_csv(self, src_dir, dest_dir):
assert os.path.isdir(src_dir)
if not os.path.exists(dest_dir):
os.makedirs(dest_dir)
src_file = self.get_filename(src_dir, 'html')
dest_file = self.get_filename(dest_dir, 'csv')
self.LOGGER.debug('''{src_file} => {dest_file}'''.format(src_file=src_file, dest_file=dest_file))
assert os.path.isfile(src_file)
dest_fd = open(dest_file, 'w', newline='')
csv_writer = csv.writer(dest_fd)
src_fd = open(src_file, 'rb')
src_content = src_fd.read()
src_fd.close()
content = None
try:
content = html.fromstring(src_content.decode('big5-hkscs').replace(' ', ' ').replace('<BR>', ''))
except UnicodeDecodeError as e:
self.LOGGER.debug(e)
content = html.fromstring(src_content.decode('gb18030').replace(' ', ' ').replace('<BR>', ''))
for table in content.xpath('//html/body/div/table/tr/td[@width="99%"]/table/tr/td/table/tr/td/table'):
for yearly_dataset in table.xpath('./tr'):
yearly_data = yearly_dataset.xpath('./td/text()')
if len(yearly_data) is 7:
activity_date = self.get_date(yearly_data[0])
if not activity_date:
continue
record = [
self.STOCK_CODE,
activity_date,
self.get_double(yearly_data[1]),
self.get_double(yearly_data[2]),
self.get_double(yearly_data[3]),
self.get_double(yearly_data[4]),
self.get_double(yearly_data[5]),
self.get_double(yearly_data[6]),
]
csv_writer.writerow(record)
dest_fd.close()
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);This will succeed only if each existing entry in the column can be converted to the new type by an implicit cast. If a more complex conversion is needed, you can add a USING clause that specifies how to compute the new values from the old.
insert into OperatingIncome(
report_date,
stock_code,
activity_date,
income
) values($1::text::date, $2, $3::text::date, $4::text::real)
insert into OperatingIncome(
report_date,
stock_code,
activity_date,
income
) values($1::text::date, $2, $3::text::date, $4::text::float8)
<?xml version="1.0" encoding="UTF-8"?>
<project name="StocktotalReport" basedir="." default="jar">
<property name="src.dir" value="src" />
<property name="lib.dir" value="lib" />
<property name="lib.jars" value="commons-beanutils-1.8.0.jar commons-cli-1.2.jar commons-collections-2.1.1.jar commons-configuration-1.9.jar commons-digester-2.1.jar commons-lang-2.6.jar commons-logging-1.1.1.jar groovy-all-2.0.1.jar jasperreports-4.8.0.jar jcommon-1.0.15.jar jfreechart-1.0.12.jar log4j-1.2.17.jar postgresql-9.2-1002.jdbc3.jar" />
<property name="config.dir" value="config"/>
<property name="build.dir" value="build" />
<property name="classes.dir" value="${build.dir}/classes" />
<property name="dest.dir" value="${build.dir}/release" />
<property name="dest.jar" value="${dest.dir}/stocktotal-report.jar" />
<path id="classpath">
<fileset dir="${lib.dir}" >
<include name="**/*.jar" />
</fileset>
</path>
<target name="clean">
<delete dir="${build.dir}" />
</target>
<target name="compile" depends="clean">
<mkdir dir="${classes.dir}" />
<javac srcdir="${src.dir}" encoding="UTF-8" destdir="${classes.dir}" includeantruntime="false" debug="true">
<classpath refid="classpath" />
<include name="**/*.java" />
</javac>
<!-- Copy all config files to ${classes.dir} -->
<copy todir="${classes.dir}">
<fileset dir="${src.dir}">
<exclude name="**/*.java" />
</fileset>
</copy>
</target>
<target name="jar" depends="compile">
<jar destfile="${dest.jar}">
<fileset dir="${classes.dir}" />
<manifest>
<attribute name="Main-Class" value="stocktotal.report.Program"/>
<attribute name="Class-Path" value="${lib.jars}"/>
</manifest>
</jar>
<copy todir="${dest.dir}">
<fileset dir="${lib.dir}" />
</copy>
<copy todir="${dest.dir}">
<fileset dir="${config.dir}" />
</copy>
</target>
<target name="run">
<java jar="${dest.jar}" fork="true" failonerror="true" />
</target>
</project>
SQL query <=> dataset
<subDataset name="ROE Dataset" uuid="4233d94c-76a5-4422-97c8-3c347be0be3b">
<parameter name="STOCK_CODE" class="java.lang.String" isForPrompting="false">
<defaultValueExpression><![CDATA["1301"]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[with T as
(
select
A.activity_date,
A.report_date,
A.number as shareholder_equity,
B.number as net_income,
C.number as operating_income,
D.number as total_assets
from
BalanceSheet as A,
IncomeStmt as B,
IncomeStmt as C,
BalanceSheet as D
where
A.stock_code = B.stock_code
and B.stock_code = C.stock_code
and C.stock_code = D.stock_code
and A.activity_date = B.activity_date
and B.activity_date = C.activity_date
and C.activity_date = D.activity_date
and A.report_date = B.report_date
and B.report_date = C.report_date
and C.report_date = D.report_date
and A.item = '股東權益總計'
and B.item = '合併總損益'
and C.item = '營業收入合計'
and D.item = '資產總計'
and A.report_type = 'C'
and B.report_type = 'C'
and C.report_type = 'C'
and D.report_type = 'C'
and A.stock_code = $P{STOCK_CODE}
and A.number != 0
and B.number != 0
and C.number != 0
and D.number != 0
)
select
V.activity_date,
V.annual_adjusted_net_income/V.shareholder_equity as roe,
V.annual_adjusted_net_income/V.annual_adjusted_operating_income as
net_profit_margin,
V.annual_adjusted_operating_income/V.total_assets as total_assets_turnover,
V.total_assets/V.shareholder_equity as equity_multiplier
from
(
select
T.activity_date,
T.shareholder_equity,
case
when date_part('month', T.activity_date) = 3 then T.net_income * 4/1
when date_part('month', T.activity_date) = 6 then T.net_income * 4/2
when date_part('month', T.activity_date) = 9 then T.net_income * 4/3
else T.net_income
end as annual_adjusted_net_income,
case
when date_part('month', T.activity_date) = 3 then
T.operating_income * 4/1
when date_part('month', T.activity_date) = 6 then
T.operating_income * 4/2
when date_part('month', T.activity_date) = 9 then
T.operating_income * 4/3
else T.operating_income
end as annual_adjusted_operating_income,
T.total_assets
from
T,
(
select activity_date, max(report_date) as report_date from T
group by activity_date
) as U
where
T.activity_date = U.activity_date
and T.report_date = U.report_date
order by T.activity_date
) as V]]>
</queryString>
<field name="activity_date" class="java.sql.Date"/>
<field name="roe" class="java.lang.Double"/>
<field name="net_profit_margin" class="java.lang.Double"/>
<field name="total_assets_turnover" class="java.lang.Double"/>
<field name="equity_multiplier" class="java.lang.Float"/>
</subDataset>
<style name="red current_ratio">
<conditionalStyle>
<conditionExpression><![CDATA[$F{current_ratio} < 2]]></conditionExpression>
<style forecolor="#FF0000"/>
</conditionalStyle>
</style>
JasperDesign jasperDesign = JRXmlLoader.load(designFile);
Map<String, JRDataset> datasetMap = jasperDesign.getDatasetMap();
Object[] names = datasetMap.keySet().toArray();
for (Object name : names) {
JRDesignDataset dataset = (JRDesignDataset)datasetMap.get((String)name);
jasperDesign.removeDataset(dataset);
Map<String, JRParameter> parametersMap = dataset.getParametersMap();
if (!parametersMap.containsKey(STOCK_CODE)) {
continue;
}
JRDesignParameter stockCodeParameter = (JRDesignParameter)parametersMap.get(STOCK_CODE);
JRDesignExpression expression = new JRDesignExpression(stockCode);
stockCodeParameter.setDefaultValueExpression(expression);
jasperDesign.addDataset(dataset);
}
HashMap<String, Object> parameters = new HashMap<String, Object>();
parameters.put(STOCK_CODE, stockCode);
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, conn);
private Connection getConn() throws Exception {
Class.forName(config.getString("database.driver"));
String url = config.getString("database.url");
String username = config.getString("database.username");
String password = config.getString("database.password");
return DriverManager.getConnection(url, username, password);
}
<?xml version="1.0" encoding="UTF-8"?>
<config>
<database>
<driver>org.postgresql.Driver</driver>
<url>jdbc:postgresql://localhost:5432/database</url>
<username>username</username>
<password>password</password>
</database>
<report>
<designFile>designFile.jrxml</designFile>
<destFilePathTemplate>report\designFile-%s.html</destFilePathTemplate>
</report>
</config>
JasperDesign jasperDesign = JRXmlLoader.load(designFile);
JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);
Connection conn = getConn();
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, conn);
JasperExportManager.exportReportToHtmlFile(jasperPrint, reportFile);
conn.close();
package stocktotal.report;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.HashMap;
import java.util.Map;
import net.sf.jasperreports.engine.JRDataset;
import net.sf.jasperreports.engine.JRParameter;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.design.JRDesignDataset;
import net.sf.jasperreports.engine.design.JRDesignExpression;
import net.sf.jasperreports.engine.design.JRDesignParameter;
import net.sf.jasperreports.engine.design.JasperDesign;
import net.sf.jasperreports.engine.xml.JRXmlLoader;
import org.apache.commons.configuration.XMLConfiguration;
import org.apache.log4j.Logger;
public class ReportGenerator {
public void generate(String stockCode, String configFile) throws Exception {
this.config = new XMLConfiguration(configFile);
logger.info("Prepare jrxml for JasperDesign");
String designFile = config.getString("report.designFile");
this.jasperDesign = JRXmlLoader.load(designFile);
logger.info("Set parameters for sub dataset");
setDatasetStockCode(stockCode);
logger.info("Compile to JasperReport file");
jasperReport = JasperCompileManager.compileReport(jasperDesign);
logger.info("Prepare parameters for main report");
HashMap<String, Object> parameters = new HashMap<String, Object>();
parameters.put(STOCK_CODE, stockCode);
logger.info("Prepare datasource for main report");
Connection conn = getConn();
logger.info("Set prepared parameters and datasource for JasperReport");
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, conn);
logger.info("Export to HTML format report");
makeReportDirectory(stockCode);
JasperExportManager.exportReportToHtmlFile(jasperPrint, getReportFilePath(stockCode));
logger.info("Release datasource connection");
conn.close();
}
private void setDatasetStockCode(String stockCode) throws Exception {
Map<String, JRDataset> datasetMap = jasperDesign.getDatasetMap();
Object[] names = datasetMap.keySet().toArray();
for (Object name : names) {
JRDesignDataset dataset = (JRDesignDataset)datasetMap.get((String)name);
jasperDesign.removeDataset(dataset);
Map<String, JRParameter> parametersMap = dataset.getParametersMap();
if (!parametersMap.containsKey(STOCK_CODE)) {
continue;
}
JRDesignParameter stockCodeParameter = (JRDesignParameter)parametersMap.get(STOCK_CODE);
JRDesignExpression expression = new JRDesignExpression(stockCode);
stockCodeParameter.setDefaultValueExpression(expression);
jasperDesign.addDataset(dataset);
}
}
private Connection getConn() throws Exception {
Class.forName(config.getString("database.driver"));
String url = config.getString("database.url");
String username = config.getString("database.username");
String password = config.getString("database.password");
return DriverManager.getConnection(url, username, password);
}
private void makeReportDirectory(String stockCode) {
String reportFilePath = config.getString("report.destFilePathTemplate");
String reportDir = (new File(reportFilePath)).getParent();
(new File(reportDir)).mkdir();
}
private String getReportFilePath(String stockCode) {
return String.format(config.getString("report.destFilePathTemplate"), stockCode);
}
final static Logger logger = Logger.getLogger(ReportGenerator.class);
private String STOCK_CODE = "STOCK_CODE";
private XMLConfiguration config;
private JasperDesign jasperDesign;
private JasperReport jasperReport;
}
package stocktotal.report;
import org.apache.log4j.Logger;
public class Program {
public static void main(String[] args) {
try {
Argparser parser = new Argparser();
parser.parse(args);
String stockCode = parser.getStockCode();
String configFile = parser.getConfigFile();
logger.info(String.format("Stock Code: %s", stockCode));
logger.info(String.format("Config File: %s", configFile));
ReportGenerator generator = new ReportGenerator();
generator.generate(stockCode, configFile);
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
}
final static Logger logger = Logger.getLogger(Program.class);
}
package stocktotal.report;
import org.apache.commons.cli.CommandLine;
import org.apache.commons.cli.CommandLineParser;
import org.apache.commons.cli.GnuParser;
import org.apache.commons.cli.Option;
import org.apache.commons.cli.OptionBuilder;
import org.apache.commons.cli.Options;
import org.apache.commons.cli.ParseException;
public class Argparser {
@SuppressWarnings("static-access")
public Argparser() {
Option help = new Option("help", "print this message");
Option stockCode = OptionBuilder.withArgName("stockCode")
.hasArg()
.withDescription("set stock code")
.create("stockcode");
Option configFile = OptionBuilder.withArgName("file")
.hasArg()
.withDescription("use given file for config")
.create("configfile");
this.options = new Options();
options.addOption(help);
options.addOption(stockCode);
options.addOption(configFile);
this.parser = new GnuParser();
}
public void parse(String[] args) throws ParseException {
this.cmdline = this.parser.parse(this.options, args);
}
public String getStockCode() {
assert this.cmdline != null;
return cmdline.hasOption("stockcode") ? cmdline.getOptionValue("stockcode") : null;
}
public String getConfigFile() {
assert this.cmdline != null;
return cmdline.hasOption("configfile") ? cmdline.getOptionValue("configfile") : null;
}
private Options options;
private CommandLineParser parser;
private CommandLine cmdline;
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/" debug="false">
<appender name="console" class="org.apache.log4j.ConsoleAppender">
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss} [%t] %-5p %c{2} %x - %m%n"/>
</layout>
</appender>
<appender name="logfile" class="org.apache.log4j.DailyRollingFileAppender">
<param name="file" value="logfile/logfile.log" />
<param name="append" value="true" />
<param name="datePattern" value="'.'yyyy-MM-dd" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss} [%t] %-5p %c{2} %x - %m%n"/>
</layout>
</appender>
<root>
<level value="INFO"/>
<appender-ref ref="console"/>
<appender-ref ref="logfile"/>
</root>
</log4j:configuration>
>>> import postgresql
>>> db = postgresql.open('pq://postgres:***@localhost:5432/postgres')
>>> db.close()
>>> print(db)
<postgresql.driver.pq3.Connection[pq://postgres:***@localhost:5432/postgres] closed>
>>>