def get_stock_performance(symbol):
sql = "select distinct TERM, SALES / 1000000 as SALES, OPERATING_INCOME / 1000000 as OPERATING_INCOME, " \
" ORDINARY_INCOME / 1000000 as ORDINARY_INCOME, NET_INCOME / 1000000 as NET_INCOME" \
" From MST_PERFORMANCE where Stock_code = '{0}-T' and term not like '%Q%' and " \
"(term like '%会社実績%' or term like '%会社予想%') order by term, announce_date".format(symbol)
result = db.engine.execute(sql)
return common_dao.convert_json_list(result)
def search_company(condition):
base_sql = "select "
base_sql += " substr(a.STOCK_CODE,1,4) as STOCK_CODE "
base_sql += " ,a.STOCK_NAME "
base_sql += " ,a.LISTED_PLACE "
base_sql += " ,c.BUSINESS_TYPE_NAME "
base_sql += " ,a.LIESTED_DATE "
base_sql += " ,b.CHARACTERISTIC "
base_sql += " from MST_STOCK a "
base_sql += " inner join ( "
base_sql += " select * from MST_SHIKIHO s "
base_sql += " where (s.STOCK_CODE, s.GET_DATE) in ( "
base_sql += " select STOCK_CODE, max(GET_DATE) from MST_SHIKIHO group by STOCK_CODE) "
base_sql += " ) b on a.STOCK_CODE = b.STOCK_CODE "
base_sql += " inner join MST_BUSINESS_TYPE c "
base_sql += " on b.BUSINESS_TYPE = c.BUSINESS_TYPE "
base_sql += " where a.IS_DELETE = 0 "
sql = base_sql
if condition["symbol"] != '':
sql = sql + "and a.STOCK_CODE = '{0}-T' ".format(condition["symbol"])
if condition["symbol_name"] != '':
sql = sql + "and a.STOCK_NAME like '%{0}%' ".format(
condition["symbol_name"])
if condition["keyword"] != '':
sql = sql + "and (a.COMPANY_PROFILE like '%{0}%' or b.CHARACTERISTIC like '%{0}%' or " \
"b.COMMENTARY1 like '%{0}%' or b.COMMENTARY2 like '%{0}%') ".format(condition["keyword"])
result = db.engine.execute(sql)
return common_dao.convert_json_list(result)
def get_financial_result(from_date, to_date, search_word):
sql = " select"
sql += " substr(a.ANNOUNCED_DATE,1, 10) as ANNOUNCED_DATE"
sql += " ,a.STOCK_CODE"
sql += " ,trim(a.stock_name) as STOCK_NAME "
sql += " ,a.TITLE "
sql += " ,a.LINK "
sql += " ,a.NET_SALES "
sql += " ,a.OPERATING_INCOME "
sql += " ,a.ORDINARY_INCOME "
sql += " ,a.NET_INCOME "
sql += " ,a.CHANGE_SALES "
sql += " ,a.CHANGE_OPERATING "
sql += " ,a.CHANGE_ORDINARY "
sql += " ,a.CHANGE_NET_INCOME "
sql += " ,a.PROGRESS_SALES "
sql += " ,a.PROGRESS_OPERATING "
sql += " ,a.PROGRESS_ORDINARY "
sql += " ,a.PROGRESS_NET_INCOME "
sql += " ,a.CHANGE_REASON "
sql += " From DATA_TDnet a "
sql += " Where '{0}' <= a.announced_date and a.announced_date <='{1}'"
sql += " And title like '%{2}%' and title not like '%補足%'"
sql += " Order by substr(a.ANNOUNCED_DATE,1, 10) desc, a.stock_code "
result = db.engine.execute(sql.format(from_date, to_date, search_word))
return common_dao.convert_json_list(result)
def get_company_info(symbol):
sql = "SELECT "
sql += " substr(a.STOCK_CODE, 1, 4) as STOCK_CODE "
sql += " , a.STOCK_NAME "
sql += " , c.BUSINESS_TYPE_NAME "
sql += " , replace(b2.ESTABLISHED, '.', '/') as ESTABLISHED "
sql += " , a.LIESTED_DATE "
sql += " , a.LISTED_PLACE "
sql += " , a.FISCAL_PERIOD "
sql += " , ROUND(cast(a.SHARED_OUTSTANDING * e.end_price as decimal) / 100000000) as TOTAL_MARKET_VALUE "
sql += " , ROUND(cast(e.end_price as float) / cast(g.net_income / a.SHARED_OUTSTANDING as float), 3) as PER "
sql += " , ROUND(cast(e.end_price as float) / cast(a.BPS as float), 3) as PBR"
sql += " , b2.CHARACTERISTIC "
sql += " , a.COMPANY_PROFILE "
sql += " , b2.CONSOLIDATED_BUSINESS "
sql += " , b2.COMMENTARY1 "
sql += " , b2.COMMENTARY2 "
sql += " , b2.EMPLOYEE "
sql += " , b2.STOCK_FOREIGN_RATIO "
sql += " , b2.STOCK_INVESTMENT_RATIO "
sql += " , b2.FREE_FLOATWEIGHT "
sql += " , b2.SPECIFIC_SHAREHOLDER_RATIO "
sql += " , b2.PRINCIPAL_SHAREHOLDER "
sql += " , b2.OFFICER "
sql += " , a.DIVIDEND_RATE "
sql += " , d.REMARK "
sql += " , d.DIVIDENDS_DATE "
sql += " , d.DIVIDENDS "
sql += " , ROUND(d.DIVIDENDS / cast(e.END_PRICE * d.ROUND_LOT as float) * 100, 2) as YUTAI_RATIO "
sql += " , e.END_PRICE "
sql += " , b2.ASSETS "
sql += " , b2.EQUITY "
sql += " , ROUND(cast(b2.EQUITY_RATIO as float) * 100, 2) as EQUITY_RATIO "
sql += " , b2.CAPITAL_STOCK "
sql += " , b2.RETURNED_EARNINGS "
sql += " , b2.LIABILITIES_WITH_INTEREST "
sql += " , b2.COMPETITORS as LAST_COMPETITORS "
sql += " , f.COMPETITORS as COMPETITORS "
sql += " , b2.URL "
sql += "FROM MST_STOCK a "
sql += " left outer join ( select * from MST_SHIKIHO a where (a.stock_code, a.get_date) in ( "
sql += " select b.stock_code, max(b.get_date) from MST_SHIKIHO b group by b.stock_code "
sql += " )) b2 on a.stock_code = b2.stock_code "
sql += " left outer join ( select * from MST_SHIKIHO a where (a.stock_code, a.get_date) in ( "
sql += " select b.stock_code, min(b.get_date) from MST_SHIKIHO b group by b.stock_code "
sql += " )) f on a.stock_code = f.stock_code "
sql += " left outer join MST_BUSINESS_TYPE c on b2.BUSINESS_TYPE = c.BUSINESS_TYPE "
sql += " left outer join MST_YUTAI d on a.stock_code = d.stock_code "
sql += " left outer join DATA_STOCKS_TODAY e on a.stock_code = e.stock_code "
sql += " left outer join ( select * From MST_PERFORMANCE a where (a.stock_code, a.term, a.announce_date) in ( "
sql += " select b.stock_code, max(b.term), max(b.announce_date) from MST_PERFORMANCE b "
sql += " where b.term like '%会社予想' group by b.stock_code "
sql += " )) g on a.stock_code = g.stock_code "
sql += "WHERE a.STOCK_CODE = '{0}-T'".format(symbol)
result = db.engine.execute(sql)
result_list = common_dao.convert_json_list(result)
if result_list and len(result_list) > 0:
return result_list[0]
return None
def get_yutai(month):
sql = " select "
sql += " substr(a.stock_code,1,4) as STOCK_CODE, "
sql += " replace(replace(b.stock_name,'ＨＬＤＧ', ''), 'ＧＲＯＵＰ', '') as STOCK_NAME, "
sql += " a.DIVIDENDS_DATE,"
sql += " a.CONTENTS, "
sql += " a.round_lot * c.end_price as MIN_VALUE_PRICE, "
sql += " a.DIVIDENDS, "
sql += " replace(b.DIVIDEND_RATE, '%', '') as DIVIDEND_RATE, "
sql += " round(cast(a.dividends as float) / (c.end_price * a.round_lot) * 100, 2) as YUTAI_RATE, "
sql += " round(cast(REPLACE(B.DIVIDEND_RATE, '%', '') as decimal) + ROUND(CAST(A.DIVIDENDS AS FLOAT) / "
sql += " (C.END_PRICE * A.ROUND_LOT) * 100, 2), 2) as SUM_RATE, "
sql += " b.LISTED_PLACE "
sql += " From MST_YUTAI a "
sql += " inner join MST_STOCK b on a.stock_code = b.stock_code "
sql += " left outer join DATA_STOCKS_TODAY c on a.stock_code = c.stock_code "
sql += " where a.dividends_date like '%{0}月%' "
sql += " order by SUM_RATE desc, a.stock_code "
result = db.engine.execute(sql.format(month))
return common_dao.convert_json_list(result)
def get_announce_financial(from_date, to_date):
sql = "select "
sql += " substr(a.STOCK_CODE,1,4) as STOCK_CODE, "
sql += " replace(replace(b.STOCK_NAME,'ＨＬＤＧ', ''), 'ＧＲＯＵＰ', '') as STOCK_NAME, "
sql += " a.ANNOUNCE_DATE, "
sql += " a.FINANCIAL_TYPE, "
sql += " b.LISTED_PLACE, "
sql += " cast(cast(e.SALES as float) / f.SALES * 100 as int) as PROGRESS_SALES_RATIO, "
sql += " cast(cast(e.OPERATING_INCOME as float) / f.OPERATING_INCOME * 100 as int) as PROGRESS_OPE_RATIO, "
sql += " cast(cast(e.ORDINARY_INCOME as float) / f.ORDINARY_INCOME * 100 as int) as PROGRESS_ORD_RATIO, "
sql += " cast(cast(e.NET_INCOME as float) / f.NET_INCOME * 100 as int) as PROGRESS_INCOME_RATIO, "
sql += " round((cast(g.OPERATING_INCOME - f.OPERATING_INCOME as float) / f.OPERATING_INCOME) * 100, 1) as SHIKIHO_DIFF_RATIO "
sql += "from MST_ANNOUNCE_FINANCIAL a"
sql += " left outer join MST_STOCK b on a.stock_code = b.stock_code"
sql += " left outer join ("
sql += " select * from MST_PERFORMANCE c"
sql += " where (c.stock_code, c.term) in "
sql += " (select d.stock_code, max(d.term) from MST_PERFORMANCE d "
sql += " where d.term like '%会社実績%' group by d.stock_code)"
sql += " ) e on e.stock_code = a.stock_code"
sql += " left outer join ("
sql += " select * from MST_PERFORMANCE c"
sql += " where (c.stock_code, c.term, c.announce_date) in (select d.stock_code, max(d.term), max(d.announce_date)"
sql += " from MST_PERFORMANCE d where d.term like '%会社予想%' group by d.stock_code)"
sql += " ) f on f.stock_code = e.stock_code and substr(f.term, 1, 6) > substr(e.term, 1, 6) "
sql += " left outer join ( "
sql += " select b.stock_code, b.term, b.sales, b.operating_income, b.ordinary_income, b.net_income, b.eps "
sql += " from MST_PERFORMANCE b "
sql += " where b.term like '%四季報予想%' "
sql += " and (b.stock_code, b.announce_date) in ( "
sql += " select a.stock_code, max(a.announce_date) From MST_PERFORMANCE a "
sql += " where a.term like '%四季報予想%' group by a.stock_code "
sql += " ) "
sql += " ) g on g.stock_code = f.stock_code and substr(g.term,1, 6) = substr(f.term,1, 6) "
sql += " where trim(a.ANNOUNCE_DATE) between '{0}' and '{1}'"
sql += " order by a.ANNOUNCE_DATE, a.FINANCIAL_TYPE, STOCK_CODE"
result = db.engine.execute(sql.format(from_date, to_date))
return common_dao.convert_json_list(result)
def get_ohlc_data(symbol):
sql = "SELECT * FROM (SELECT STOCK_DATE, START_PRICE, HIGH_PRICE, LOW_PRICE, END_PRICE, VOLUME " \
"FROM DATA_STOCKS WHERE STOCK_CODE = '{0}-T' ORDER BY STOCK_DATE desc limit 60) " \
"ORDER BY STOCK_DATE desc".format(symbol)
result = db.engine.execute(sql)
return common_dao.convert_json_list(result)