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