def get_water_by_date_chart(request):
start_time = request.POST.get('start_time')
end_time = request.POST.get('end_time')
# print 'start_time: %s\nend_time: %s' % (start_time, end_time, )
if start_time is None or end_time is None:
result = dict()
result['message'] = u'时间日期为空, 请传入查询时间'.encode('utf-8')
result['code'] = -1
result['result'] = False
return HttpResponse(content=json.dumps(obj=result, ensure_ascii=False),
content_type='application/json',
charset='utf-8')
try:
start = DateUtils.convert_str_to_date(str_date=start_time)
end = DateUtils.convert_str_to_date(str_date=end_time)
water_list = Water.objects.filter(curTime__range=(start, end))
if water_list is None or len(water_list) == 0:
result = dict()
result['result'] = False
result['message'] = u'数据为空'
result['code'] = -1
return HttpResponse(content=json.dumps(obj=result,
ensure_ascii=False),
content_type='application/json',
charset='utf-8')
else:
data = dict()
x_time = list()
phs = list()
pressures = list()
temperatures = list()
electrolytics = list()
for info in water_list:
x_time.append(DateUtils.convert_date_to_str(info.curTime))
phs.append(info.ph)
pressures.append(info.pressure)
temperatures.append(info.temperature)
electrolytics.append(info.electrolytic)
data['x_time'] = x_time
data['phs'] = phs
data['pressures'] = pressures
data['temperatures'] = temperatures
data['electrolytics'] = electrolytics
result = dict()
result['result'] = True
result['message'] = 'success'
result['code'] = 0
result['data'] = data
return JsonResponse(data=result, safe=False, status=200)
except Exception, e:
result = dict()
result['message'] = e.message
result['result'] = False
result['code'] = -1
return JsonResponse(data=result)
def encodeJwt(id, role, remember):
'''
生成jwt
'''
payload = {
'iat': DateUtils.format(DateUtils.now()),
'from_user': id,
'role': role,
'remember': remember
}
return Signature.s.dumps(payload, Signature.salt)
def main():
mysql = "SELECT exp_no '标准单号',exp_date '计费日期',seller_no '商家编号', system_source '来源系统',order_type '单据类型',transaction_type '交易类型' FROM trans_standard_201807 ;"
# 生成数据
my_data = JdbcUtils.get_datas(mysql)
# 生成字段名称
my_fields = JdbcUtils.get_fields(mysql)
yesterdayStr = DateUtils.getYesterdayStr()
# 生成文件名称
my_file_name = 'file_' + yesterdayStr + '.xlsx'
# 生成文件路径
my_file_path = 'E:/toolsResult/' + my_file_name
# 生成excel
ExcelUtils.get_excel(my_data, my_fields, my_file_path)
my_email = mail.MyEmail(emailFrom='*****@*****.**',
emailTo='*****@*****.**',
emailSubject='【标准单数据】' + yesterdayStr,
emailText='Dear all：\n\t附件为昨天的标准单数据，请查收！',
annexPath=my_file_path,
annexName=my_file_name)
# 生成邮件
my_email_msg = EmailUtils.create_email(my_email)
my_receiver = ['*****@*****.**']
EmailUtils.send_email('*****@*****.**', 'Gqy1084', my_receiver,
my_email_msg)
def get_compare_info(request):
a_time = request.POST.get('a_time')
b_time = request.POST.get('b_time')
result = dict()
if a_time is None or b_time is None:
result['message'] = u'时间日期为空, 请传入查询时间'.encode('utf-8')
result['code'] = -1
result['result'] = False
return HttpResponse(content=json.dumps(obj=result, ensure_ascii=False),
content_type='application/json',
charset='utf-8')
try:
a = DateUtils.convert_str_to_date(str_date=a_time)
b = DateUtils.convert_str_to_date(str_date=b_time)
water_list = Water.objects.filter(curTime__in=[a, b])
if len(water_list) < 2:
result['message'] = '查询时间数据为空, 请先把指定日期数据添加到数据库中'
result['code'] = -1
result['result'] = False
return HttpResponse(content=json.dumps(obj=result,
ensure_ascii=False),
content_type='appllication/json',
charset='utf-8')
data_list = list()
for info in water_list:
data = dict()
data['pressure'] = info.pressure
data['temperature'] = info.temperature
data['ph'] = info.ph
data['electrolytic'] = info.electrolytic
data['curTime'] = DateUtils.convert_date_to_str(info.curTime)
data_list.append(data)
result['message'] = 'success'
result['code'] = 0
result['result'] = True
result['data'] = data_list
return JsonResponse(data=result, status=200, safe=False)
except Exception, e:
result['message'] = e.message
result['result'] = False
result['code'] = -1
return JsonResponse(data=result, status=501)
def get_market_cap_for_list_of_dates(ticker, list_of_dates):
date_marketcap_map = TickersPricesDAO.get_market_cap_for_list_of_dates(ticker, list_of_dates)
if len(date_marketcap_map) < len(list_of_dates):
# get last_update
last_updated = LastUpdatedDAO.get_last_updated("market_cap", ticker)
if DateUtils.is_after(list_of_dates[-1], last_updated):
print(f"market cap value for {ticker} is not up to date. retrieving from macrotrends")
MarketCapBuilderBL.populate_single_ticker(ticker=ticker)
market_cap = TickersPricesDAO.get_market_cap_for_list_of_dates(ticker, list_of_dates)
return date_marketcap_map
def get_market_cap(date_str, ticker):
market_cap = TickersPricesDAO.get_market_cap(date_str, ticker)
if market_cap is None:
# get last_update
last_updated = LastUpdatedDAO.get_last_updated("market_cap", ticker)
if DateUtils.is_after(date_str, last_updated):
print(f"market cap value for {ticker} and {date_str} is not up to date. retrieving from macrotrends")
MarketCapBuilderBL.populate_single_ticker(ticker=ticker)
market_cap = TickersPricesDAO.get_market_cap(date_str, ticker)
return market_cap
def fill_business_days(missing_dates):
connection, cursor = get_connection_cursor()
my_data = []
for date in missing_dates:
business_day = DateUtils.get_business_date(date)
t = (date, business_day)
my_data.append(t)
sql = f"insert into shares.business_days (date, business_day) VALUES (%s, %s)"
cursor.executemany(sql, my_data)
connection.commit()
def get_alert_water_info_chart(request):
start_time = request.POST.get('start_time')
end_time = request.POST.get('end_time')
result = dict()
if start_time is None or end_time is None:
result['message'] = u'时间日期为空, 请传入查询时间'
result['result'] = False
result['code'] = -1
return HttpResponse(content=json.dumps(obj=result, ensure_ascii=False),
content_type='application/json',
charset='utf-8')
try:
water_list = WaterDB.get_instance().query_specified_alert_by_date(
start_time=start_time,
end_time=end_time,
alert_info=AlertValue.ALERT_INFO)
if water_list is None or len(water_list) < 1:
result['message'] = u'指定时间内, 报警数据为空'
result['result'] = False
result['code'] = -1
return HttpResponse(content=json.dumps(obj=result,
ensure_ascii=False),
content_type='application/json',
charset='utf-8')
data = dict()
x_time = list()
phs = list()
pressures = list()
temperatures = list()
electrolytics = list()
for info in water_list:
x_time.append(DateUtils.convert_date_to_str(info.get('curTime')))
phs.append(info.get('ph'))
pressures.append(info.get('pressure'))
temperatures.append(info.get('temperature'))
electrolytics.append(True if info.get('electrolytic') ==
1 else False)
data['x_time'] = x_time
data['phs'] = phs
data['pressures'] = pressures
data['temperatures'] = temperatures
data['electrolytics'] = electrolytics
result['message'] = 'success'
result['result'] = True
result['code'] = 0
result['data'] = data
return JsonResponse(data=result, safe=False, status=200)
except Exception, e:
result['message'] = e.message
result['code'] = -1
result['result'] = False
return JsonResponse(data=result)
def get_market_cap(date, ticker):
connection, cursor = get_connection_cursor()
business_day = DateUtils.get_business_date(date)
sql = f"select t.market_cap from shares.tickers_prices t where t.ticker = '{ticker}' and t.date = '{business_day}';"
# print(f"sql is: {sql}")
cursor.execute(sql)
single_result = cursor.fetchone()
if single_result:
(market_cap, ) = single_result
return market_cap
else:
return None
def get_market_cap_for_list_of_dates(ticker,
list_of_dates,
connection=None,
cursor=None):
if not connection:
connection, cursor = get_connection_cursor()
# (select 1 id, '2019-3-31' date union all select 2 id, '2019-6-30' date)
original_dates_sql = create_dates_sql(list_of_dates)
list_of_business_dates = DateUtils.get_business_dates(list_of_dates)
business_dates_sql = create_dates_sql(list_of_business_dates)
sql = f"select dates.date original_date, t.date business_day, t.market_cap from {original_dates_sql} as dates, {business_dates_sql} as business_dates, shares.tickers_prices t where t.ticker = '{ticker}' AND dates.id=business_dates.id AND t.date = business_dates.date;"
cursor.execute(sql)
return cursor.fetchall()
def get_macrotrends_responses_method_b(companies_ids, date_str_list) -> object:
dateticker_to_capprice_map = {}
for date_str in tqdm(date_str_list, desc="looping over all given dates to get macrotrends responses", colour="CYAN"):
ticker_list = get_tickers(companies_ids)
business_day = get_business_date(date_str)
dateticker_to_capprice_map_for_business_day = TickersPricesDAO.get_market_cap_list(date_str, business_day, ticker_list)
for ticker in ticker_list:
market_cap = dateticker_to_capprice_map_for_business_day.get((date_str, ticker))
if market_cap is None:
# get last_update
last_updated = LastUpdatedDAO.get_last_updated("market_cap", ticker)
if DateUtils.is_after(date_str, last_updated):
print(
f"market cap value for {ticker} and {date_str} is not up to date. retrieving from macrotrends")
MarketCapBuilderBL.populate_single_ticker(ticker=ticker)
market_cap = TickersPricesDAO.get_market_cap(date_str, ticker)
dateticker_to_capprice_map[(date_str,ticker)] = market_cap
return dateticker_to_capprice_map