def draw_delivery_histogram(state1, city1, state2, city2):
db = DB()
sql = '''
SELECT ROUND(AVG(ORDERR.DELIVERING_TIME
- ORDERR.PURCHASE_TIME), 2) AS PREP_TIME,
ROUND(AVG(ORDERR.RECEIVING_TIME
- ORDERR.DELIVERING_TIME), 2) AS DELI_TIME,
TO_CHAR(ORDERR.PURCHASE_TIME, 'YYYY-MM') AS TIME1
FROM ORDERR,
CUSTOMER,
ORDER_ITEM,
SELLER
WHERE CUSTOMER.CUSTOMER_ID = ORDERR.CUSTOMER_ID
AND CUSTOMER.ZIPCODE IN
(SELECT ZIPCODE
FROM GEOLOC
WHERE GEOLOC.STATE = '%s'
AND GEOLOC.CITY = '%s'
)
AND SELLER.ZIPCODE IN
(SELECT ZIPCODE
FROM GEOLOC
WHERE GEOLOC.STATE = '%s'
AND GEOLOC.CITY = '%s' )
AND ORDERR.STATUS = 'delivered'
AND ORDER_ITEM.SELLER_ID = SELLER.SELLER_ID
AND ORDERR.ORDER_ID = ORDER_ITEM.ORDER_ID
GROUP BY TO_CHAR(ORDERR.PURCHASE_TIME, 'YYYY-MM')
ORDER BY TO_DATE(TIME1, 'YYYY-MM') ASC''' % (state1, city1, state2,
city2)
results = db.executeMultiResult(sql)
db.close()
return results
def achieve_Memory_page_info(need_state='tianshi'):
if need_state == 'tianshi':
return 'no'
else:
tmp_1 = DB().get_list('Memory_info', ['ip_addr_id'])
# 对数据进行去重处理
run_function = lambda x, y: x if y in x else x + [y]
tmp_2 = reduce(run_function, [
[],
] + tmp_1)
tmp_3 = []
for i in tmp_2:
fields = ['disk_state_value']
tmp_5 = OrderedDict()
tmp_5['ip_addr'] = use_id_return_racadm_ip(i['ip_addr_id'])
for x in need_state.split(','):
where = {'ip_addr_id': i['ip_addr_id'], 'disk_state_key': x}
tmp_6 = DB().get_one('Memory_info', fields, where)
if tmp_6:
tmp_5[x] = tmp_6['disk_state_value']
else:
tmp_5[x] = ''
tmp_3.append(tmp_5)
if tmp_3:
return tmp_3
else:
return 'no'
def achieve_Memory_status_page_info(need_state='tianshi'):
if need_state == 'tianshi':
return 'no'
else:
tmp_1 = DB().get_list('Memory_status_info', ['ip_addr_id'])
# 对数据进行去重处理
run_function = lambda x, y: x if y in x else x + [y]
tmp_2 = reduce(run_function, [
[],
] + tmp_1)
tmp_3 = {}
for i in tmp_2:
tmp_4 = 'tmp_%s_tmp' % (i['ip_addr_id'])
tmp_3[tmp_4] = use_id_return_racadm_ip(i['ip_addr_id'])
fields = ['ip_addr_id']
for tmp_1 in need_state.split(','):
fields.append(tmp_1)
tmp_1 = DB().get_list('Memory_status_info', fields)
tmp_2 = []
for i in tmp_1:
tmp_9 = OrderedDict()
tmp_4 = 'tmp_%s_tmp' % (i['ip_addr_id'])
tmp_9['ip_addr'] = tmp_3[tmp_4]
for tmp_7 in need_state.split(','):
tmp_9[tmp_7] = i[tmp_7]
tmp_2.append(tmp_9)
if tmp_2:
return tmp_2
else:
return 'no'
def get_selector_schema():
db = DB()
sql = "SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='PRODUCT_SELECTOR'"
result = db.executeMultiResult(sql)
db.close()
return result
return results
def achieve_Cpu_page_info(need_state='tianshi', need_state_1='tianshi'):
if need_state == 'tianshi' or need_state_1 == 'tianshi':
return 'no'
else:
tmp_1 = DB().get_list('CPU_info', ['ip_addr_id'])
# 对数据进行去重处理
run_function = lambda x, y: x if y in x else x + [y]
tmp_2 = reduce(run_function, [
[],
] + tmp_1)
tmp_3 = []
for i in tmp_2:
fields = ['disk_state_value']
tmp_5 = OrderedDict()
tmp_5['ip_addr'] = use_id_return_racadm_ip(i['ip_addr_id'])
for x in need_state.split(','):
if x == 'cpu_info':
tmp_8 = 0
tmp_status = 'True'
tmp_13 = []
while tmp_status == 'True':
try:
tmp_8 = int(tmp_8) + 1
tmp_12 = []
for y in need_state_1.split(','):
tmp_9 = y.replace('xxxxx=xxxxx',
'%s' % (tmp_8))
where = {
'ip_addr_id': i['ip_addr_id'],
'disk_state_key': tmp_9
}
tmp_11 = DB().get_one(
'CPU_info', fields,
where)['disk_state_value']
tmp_14 = y.split('xxxxx=xxxxx', 2)[1]
tmp_15 = achieve_Memory_info_state_value(
tmp_14)
if tmp_15 == 'no':
tmp_15 = tmp_14
tmp_12.append('%s : %s' % (tmp_15, tmp_11))
tmp_13.append(" | ".join(tmp_12))
except:
tmp_status = 'false'
tmp_5[x] = tmp_13
continue
where = {'ip_addr_id': i['ip_addr_id'], 'disk_state_key': x}
tmp_6 = DB().get_one('CPU_info', fields, where)
if tmp_6:
tmp_5[x] = tmp_6['disk_state_value']
else:
tmp_5[x] = ''
tmp_3.append(tmp_5)
if tmp_3:
return tmp_3
else:
return 'no'
def check_dell_racadm_Sensor_BATTERY_save(ip_addr_id, Nic_data='tianshi'):
if Nic_data == 'tianshi':
return 'no'
else:
info_data_where = {'ip_addr_id': ip_addr_id}
DB().delete('Battery_status_info', info_data_where)
for i in Nic_data:
DB().create('Battery_status_info', i)
return 'ok'
def check_dell_racadm_CPU_info_save(ip_addr_id, Nic_data='tianshi'):
if Nic_data == 'tianshi':
return 'no'
else:
info_data_where = {'ip_addr_id': ip_addr_id}
DB().delete('CPU_info', info_data_where)
for i in Nic_data:
DB().create('CPU_info', i)
return 'ok'
def racadm_disk_change_old():
# 默认认为时间相差为一个小时 3600秒
difference_time = 3600
if request.method == "GET":
client_ip = request.remote_addr
if check_client_ip(client_ip) == 'no':
# IP地址没在白名单 禁止访问
return '01'
# 当前时间
now_time_tmp = datetime.datetime.now()
tmp_fields = [
'disk_name', 'ip_addr_id', 'disk_state_key', 'disk_state_value'
]
tmp_where = {'disk_state_key': 'check_time'}
tmp_1 = DB().get_list_where('disk_status_info', tmp_fields, tmp_where)
for tmp_2 in tmp_1:
if tmp_2['disk_state_value'] != '====':
tmp_3 = datetime.datetime.strptime(tmp_2['disk_state_value'],
'%Y-%m-%d %H:%M:%S')
else:
continue
tmp_4 = now_time_tmp - tmp_3
if tmp_4.seconds > difference_time:
need_state = achieve_disk_reportsclass_table_value(
'save_status_data_need')
if need_state == 'no':
info_data_where = {
'ip_addr_id': tmp_2['ip_addr_id'],
'disk_name': tmp_2['disk_name']
}
DB().delete('disk_status_info', info_data_where)
else:
status_insert_data = {}
for tmp_5 in need_state.split(','):
if tmp_5 == 'check_time':
continue
if tmp_2['disk_state_value'] == '====':
continue
status_insert_data['disk_state_value'] = '===='
tmp_where_1 = {
'disk_name': tmp_2['disk_name'],
'ip_addr_id': tmp_2['ip_addr_id']
}
DB().update_where('disk_status_info', status_insert_data,
tmp_where_1)
status_insert_data = {
'disk_state_value': tmp_2['disk_state_value']
}
tmp_where_1 = {
'disk_name': tmp_2['disk_name'],
'ip_addr_id': tmp_2['ip_addr_id'],
'disk_state_key': tmp_2['disk_state_key']
}
DB().update_where('disk_status_info', status_insert_data,
tmp_where_1)
return 'ok'
def update():
fields = ["id", "name", "name_cn", "mobile", "email", "role", "status"]
if request.method == "GET":
where = {'id': request.args.get("id")}
userinfo = DB().get_one('users', fields, where)
return json.dumps(userinfo)
else:
userinfo = dict((k, v[0]) for k, v in dict(request.form).items())
DB().update('users', userinfo)
return json.dumps({"code": 0})
def del_user():
if not session.get('username', None):
return redirect("/login")
id = request.args.get("id")
fields = ["id"]
where = {'id': id}
if DB().check('users', fields, where):
DB().delete('users', where)
return json.dumps({"code": 0, "result": "delete user success"})
else:
return json.dumps({"code": 1, "errmsg": "删除错误,可能用户已不存在"})
def get_cities_by_state(state):
db = DB()
sql = '''
SELECT DISTINCT (UTL_RAW.CAST_TO_VARCHAR2(NLSSORT(CITY, 'nls_sort=binary_ai'))) city FROM GEOLOC
WHERE STATE = '%s'
ORDER BY city
''' % (state)
results = db.executeMultiResult(sql)
db.close()
return results
def user_list():
if not session.get('username', None):
return redirect("/login")
fields = ["id", "name", "name_cn", "mobile", "email", "role", "status"]
#data = userlist(fields)
name = request.form.get('id')
if name == '' or name == "None" or name is None:
data = DB().get_list('users', fields)
else:
tianshi = "users where name='%s'%(name)"
data = DB().get_list('users', fields)
return render_template('userlist.html', users=data, info=session)
def draw_line_all_product():
db = DB()
sql = '''
SELECT *
FROM (SELECT COUNT(*) cnt, TO_CHAR(PURCHASE_TIME, 'YYYY-MM') timee
FROM ORDERR
GROUP BY TO_CHAR(PURCHASE_TIME, 'YYYY-MM'))
ORDER BY timee'''
print(sql)
result = db.executeMultiResult(sql)
db.close()
return result
def add_user():
if not session.get('username', None):
return redirect("/login")
if request.method == "GET":
return render_template("add.html", info=session)
if request.method == "POST":
data = dict((k, v[0]) for k, v in dict(request.form).items())
data['password'] = hashlib.md5(data['password'] + salt).hexdigest()
fields = ['name']
where = {"name": data["name"]}
if DB().check('users', fields, where):
return json.dumps({"code": 1, "errmsg": "username is exist"})
DB().create('users', data)
return json.dumps({"code": 0, "result": "add user success"})
def cabinet():
if not session.get('username', None):
return redirect("/login")
idcs = DB().get_list('idc', idc_fields)
# print idcs # [{'id': 7, 'name': 'hp'}, {'id': 1, 'name': 'syq'}]
idcs = dict((idc['id'], idc['name']) for idc in idcs)
# print idcs # {1: 'syq', 7: 'hp'}
data = DB().get_list('cabinet', fields)
# print data # [{'idc_id': 2, 'u_num': '32U', 'id': 5, 'power': '20A', 'name': '005'}]
# 重点！！！循环机柜表数据，并查询出idc_id是对比机房表信息，匹配就把idc_id 换位name,然后传到前端
for x in data:
if x['idc_id'] in idcs:
x['idc_id'] = idcs[x['idc_id']]
return render_template('cabinet/cabinet.html', cabinets=data, info=session)
def idc_update():
if not session.get('username'):
return redirect('/login')
if request.method == 'POST':
data = dict((k, v[0]) for k, v in dict(request.form).items())
DB().update('idc', data)
return json.dumps({'code': 0, 'result': 'update completed!'})
else:
id = request.args.get('id')
where = {'id': id}
idc_info = DB().get_one('idc', fields, where)
return render_template('idc/idcupdate.html',
idc=idc_info,
info=session)
def idc_add():
if not session.get('username', None):
return redirect("/login")
if request.method == 'POST':
data = dict((k, v[0]) for k, v in dict(request.form).items())
where = {'name': data['name']}
result = DB().check('idc', fields, where)
if result:
return json.dumps({'code': 1, 'errmsg': 'idc name is exist'})
else:
data = DB().create('idc', data)
return json.dumps({'code': 0, 'result': 'add idc success'})
else:
return render_template('idc/idcadd.html', info=session)
def check_dell_racadm_System_Nic_info_save(ip_addr_id,
Nic_data='tianshi',
System_data='tianshi'):
if Nic_data == 'tianshi' or System_data == 'tianshi':
return 'no'
else:
info_data_where = {'ip_addr_id': ip_addr_id}
DB().delete('System_info', info_data_where)
DB().delete('NIC_info', info_data_where)
for i in Nic_data:
i['ip_addr_id'] = ip_addr_id
DB().create('NIC_info', i)
System_data['ip_addr_id'] = ip_addr_id
DB().create('System_info', System_data)
return 'ok'
def draw_customer_histogram(start_date, end_date):
db = DB()
sql = '''
SELECT COUNT(DISTINCT CUSTOMER.CUSTOMER_ID) AS CNT,
TO_CHAR(ORDERR.PURCHASE_TIME, 'YYYY-MM') AS TIME1
FROM ORDERR,
CUSTOMER
WHERE CUSTOMER.CUSTOMER_ID = ORDERR.CUSTOMER_ID
AND ORDERR.PURCHASE_TIME < TO_DATE('%s', 'YYYY-MM')
AND ORDERR.PURCHASE_TIME > TO_DATE('%s', 'YYYY-MM')
GROUP BY TO_CHAR(ORDERR.PURCHASE_TIME, 'YYYY-MM')
ORDER BY TO_DATE(TIME1, 'YYYY-MM') ASC''' % (end_date, start_date)
results = db.executeMultiResult(sql)
db.close()
return results
def cabinet_delete():
if not session.get('username', None):
return redirect("/login")
id = request.form.get('id')
where = {'id': id}
DB().delete('cabinet', where)
return json.dumps({'code': 0, 'result': 'delete success!'})
def UpdateDatabase(stats):
query = '''
UPDATE markets_india_calculated_ratios
SET roe = %s, roa = %s, cmp = %s,
price_to_book = %s, price_to_earnings = %s,
price_to_sales = %s, market_cap = %s,
net_margin = %s, profit_growth = %s,
revenue_growth = %s
WHERE instrument_id = %s
'''
for stat in stats:
DB.execute_query(query,
(stat['roe'], stat['roa'], stat['cmp'], stat['pb'],
stat['pe'], stat['ps'], stat['market_cap'],
stat['net_margin'], stat['profit_growth'],
stat['revenue_growth'], stat['instrument_id']))
def disk_achieve_disk_warning_return():
fields = ['warning_field_key', 'warning_value']
tmp_1 = DB().get_list('disk_warning_table', fields)
if tmp_1:
return tmp_1
else:
return 'no'
def index():
if not session.get('username', None):
return redirect("/login")
fields = ["id", "name", "name_cn", "mobile", "email", "role", "status"]
where = {'name': session['username']}
result = DB().get_one('users', fields, where)
return render_template('index.html', info=session, user=result)
def return_racadm_ip():
check_ip_fields = ["ip_addr", 'id']
tmp_1 = DB().get_list('monitored_ipaddr', check_ip_fields)
if tmp_1:
return tmp_1
else:
return 'no'
def del_user():
if not session.get('username', None):
return redirect("/login")
id = request.args.get("id")
where = {'id': id}
DB().delete('users', where)
return json.dumps({"code": 0, "result": "delete user success"})
def user_list():
if not session.get('username', None):
return redirect("/login")
fields = ["id", "name", "name_cn", "mobile", "email", "role", "status"]
#data = userlist(fields)
data = DB().get_list('users', fields)
return render_template('userlist.html', users=data, info=session)
def achieve_Sensor_status_info_state_value(reports_submit_name):
fields = ["disk_state_name"]
where = {'disk_state_key': reports_submit_name}
tmp_1 = DB().get_one('sensor_status_state', fields, where)
if tmp_1:
return tmp_1['disk_state_name']
else:
return 'no'
def see_if_fake_price():
db = DB()
sql = '''
SELECT digit,
ROUND(COUNT(digit) / (
SELECT COUNT(*)
FROM ORDER_ITEM
) * 100, 2) cnt
FROM (
SELECT PRODUCT_ID, substr(PRICE, 0, 1) digit
FROM ORDER_ITEM
)
GROUP BY digit
ORDER BY digit ASC'''
results = db.executeMultiResult(sql)
db.close()
return results
def draw_satisfactory_histogram(product_id):
db = DB()
sql = '''
SELECT AVG(SCORE), TO_CHAR(REVIEW.CREATION_DATE, 'YYYY-MM') AS TIMEE
FROM ORDERR,
ORDER_ITEM,
PRODUCT,
REVIEW
WHERE ORDERR.ORDER_ID = ORDER_ITEM.ORDER_ID
AND ORDER_ITEM.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND PRODUCT.PRODUCT_ID = '%s'
AND ORDERR.ORDER_ID = REVIEW.ORDER_ID
GROUP BY TO_CHAR(REVIEW.CREATION_DATE, 'YYYY-MM')
ORDER BY TO_DATE(TIMEE, 'YYYY-MM') ASC ''' % (product_id)
results = db.executeMultiResult(sql)
db.close()
return results
def see_if_fake_weight():
db = DB()
sql = '''
SELECT digit,
ROUND(COUNT(digit) / (
SELECT COUNT(*)
FROM PRODUCT
) * 100, 2) cnt
FROM (
SELECT PRODUCT_ID, substr(WEIGHT, 0, 1) digit
FROM PRODUCT
)
GROUP BY digit
ORDER BY digit ASC'''
results = db.executeMultiResult(sql)
db.close()
return results