def zone_insert_mx(tags): # Get Zone fields = tags['id_record'] record = list() column_record = model.get_columns("v_record") query = "select * from v_record where id_record='"+fields+"' AND nm_type='MX'" db.execute(query) rows = db.fetchall() for row in rows: record.append(dict(zip(column_record, row))) column_ttl = model.get_columns("v_ttldata") query = "select * from v_ttldata where id_record='"+fields+"' AND nm_type='MX'" db.execute(query) rows = db.fetchall() ttldata = list() for row in rows: ttldata.append(dict(zip(column_ttl, row))) content_data = list() column_cdata= model.get_columns("v_contentdata") query = "select * from v_contentdata where id_record='"+fields+"' AND nm_type='MX'" db.execute(query) rows = db.fetchall() for row in rows: content_data.append(dict(zip(column_cdata, row))) content_serial = list() column_cserial= model.get_columns("v_content_serial") query = "select * from v_content_serial where id_record='"+fields+"' AND nm_type='MX'" db.execute(query) rows = db.fetchall() for row in rows: content_serial.append(dict(zip(column_cserial, row))) serial_data = "" data = "" for ns in content_data: data = data+" "+ns['nm_content'] for serial in content_serial: serial_data = serial_data+" "+serial['nm_content_serial'] data_ns_soa = data data_ns_serial = serial_data json_command = { "srv-set": { "sendblock": { "cmd": "zone-set", "zone": record[0]['nm_zone'], "owner": record[0]['nm_record'], "rtype": record[0]['nm_type'], "ttl": ttldata[0]['nm_ttl'], "data": data_ns_soa+""+data_ns_serial }, "receive": { "type": "command" } } } return json_command
def user_get(): id_userdata = get_jwt_identity() obj_userdata = list() column = db.get_columns('tb_userdata') try: results = list() query = "select * from tb_userdata where id_userdata='"+id_userdata+"' " data = db.query(query) rows = dbq.fetchall() for row in rows: print(row) results.append(dict(zip(column, row))) except Exception as e: return response(200, message=str(e)) else: for i in results : data = { "id_userdata": str(i['id_userdata']), "email" : i['email'], "first_name" : i['first_name'], "last_name" : i['last_name'], "location" : i['location'] } obj_userdata.append(data) return response(200, data=obj_userdata)
def get_cleanup_id(domainname): obj_userdata= list() column = model.get_columns("v_all_content") query = "SELECT * FROM v_all_content WHERE nm_zone = \'"+domainname+"""\'""" try: result = list() db.execute(query) rows = db.fetchall() for row in rows: result.append(dict(zip(column,row))) except Exception as e: respons = { "status" : False, "messages":str(e) } else: for i in result: data = { "id_zone": str(i['id_zone']), "id_content": str(i['id_content']), "id_content_serial": str(i['id_content_serial']), "id_ttldata": str(i['id_ttldata']), "id_record": str(i['id_record']) } obj_userdata.append(data) finally: return obj_userdata
def zone_ns_insert(tags): fields = str(list(tags.keys())[0]) record = list() column_record = model.get_columns("v_record") query = "select * from v_record where "+fields+"='"+tags[fields]+"' AND nm_type='NS'" db.execute(query) rows = db.fetchall() for row in rows: record.append(dict(zip(column_record, row))) ttldata = list() column_ttldata = model.get_columns("v_ttldata") query = "select * from v_ttldata where id_record='"+str(record[0]['id_record'])+"'" db.execute(query) rows = db.fetchall() for row in rows: ttldata.append(dict(zip(column_ttldata, row))) ctdata = list() column_ctdata = model.get_columns("v_contentdata") query = "select * from v_contentdata where id_record='"+str(record[0]['id_record'])+"'" db.execute(query) rows = db.fetchall() for row in rows: ctdata.append(dict(zip(column_ctdata, row))) command_ns = list() for ctn in ctdata: json_command={ "zone-set": { "sendblock": { "cmd": "zone-set", "zone": record[0]['nm_zone'], "owner": "@", "rtype": record[0]['nm_type'], "ttl": ttldata[0]['nm_ttl'], "data": ctn['nm_content'] }, "receive": { "type": "block" } } } command_ns.append({ "id_record": record[0]['id_record'], "command": json_command }) return command_ns
def get_columns(table): column = None try: db.execute("SHOW columns FROM " + table) column = [row[0] for row in db.fetchall()] except (Exception, psycopg2.DatabaseError) as e: column = str(e) return column
def get_all(table): results = None try: db.execute("SELECT * FROM "+table) results = db.fetchall() except Exception as e: return str(e) else: return results
def get_by_id(table, field= None, value= None): results = list() try: db.execute("SELECT * FROM "+table+" WHERE "+field+"=%s",(value,)) results = db.fetchall() except Exception as e: raise e else: return results
def get_columns(table): column = None try: # db.execute("SHOW columns FROM "+table) db.execute("SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name='"+table+"'") column = [row[0] for row in db.fetchall()] except (Exception, psycopg2.DatabaseError) as e: column = str(e) return column
def zone_insert(tags): # Get Record Data id_record = tags['id_record'] record = list() column_record = model.get_columns("v_record") query = "select * from v_record where id_record='"+id_record+"'" db.execute(query) rows = db.fetchall() for row in rows: record.append(dict(zip(column_record, row))) ttldata = list() column_ttldata = model.get_columns("v_ttldata") query = "select * from v_ttldata where id_record='"+str(record[0]['id_record'])+"'" db.execute(query) rows = db.fetchall() for row in rows: ttldata.append(dict(zip(column_ttldata, row))) ctdata = list() column_ctdata = model.get_columns("v_contentdata") query = "select * from v_contentdata where id_record='"+str(record[0]['id_record'])+"'" db.execute(query) rows = db.fetchall() for row in rows: ctdata.append(dict(zip(column_ctdata, row))) json_command={ "zone-set": { "sendblock": { "cmd": "zone-set", "zone": record[0]['nm_zone'], "owner": record[0]['nm_record'], "rtype": record[0]['nm_type'], "ttl": ttldata[0]['nm_ttl'], "data": ctdata[0]['nm_content'] }, "receive": { "type": "block" } } } return json_command
def get_all(table): column = get_columns(table) results = list() try: db.execute("SELECT * FROM " + table) rows = db.fetchall() for row in rows: results.append(dict(zip(column, row))) return results except (Exception, psycopg2.DatabaseError) as e: return column + str(e)
def get_by_id(table, field= None, value= None): column = get_columns(table) results = list() try: db.execute("SELECT * FROM "+table+" WHERE "+field+"=%s",(value,)) rows = db.fetchall() for row in rows: results.append(dict(zip(column, row))) except (Exception, psycopg2.DatabaseError) as e: raise e else: return results
def table(): # Renders table.html. conn = pymysql.connect(user=user, passwd=passwd, host=host, port=port) db = conn.cursor() db.execute('USE news_graph') # get list of all domains sql_q = ''' SELECT * from nodes ORDER BY alexa ASC''' db.execute(sql_q) out = db.fetchall() table = [] for line in out: table.append( [line[1], line[3], line[2], "%.4f" % line[10], line[7], line[6], line[9]]) return render_template('table.html', table=table)
def zone_unset(tags): id_record = tags['id_record'] record = list() column_record = model.get_columns("v_record") query = "select * from v_record where id_record='"+id_record+"'" db.execute(query) rows = db.fetchall() for row in rows: record.append(dict(zip(column_record, row))) json_command={ "zone-unset": { "sendblock": { "cmd": "zone-unset", "zone": record[0]['nm_zone'], "owner": record[0]['nm_record'], "rtype": record[0]['nm_type'] }, "receive": { "type": "block" } } } return json_command
def conf_unset(tags): id_zone = tags['id_zone'] record = list() column_record = model.get_columns("zn_zone") query = "select * from zn_zone where id_zone='"+id_zone+"'" db.execute(query) rows = db.fetchall() for row in rows: record.append(dict(zip(column_record, row))) json_command={ "conf-unset": { "sendblock": { "cmd": "conf-unset", "section": "zone", "item": "domain", "data":record[0]['nm_zone'] }, "receive": { "type": "block" } } } return json_command
def query_db(db, pos_domains, neg_domains): pos_domains_str = '"' + '", "'.join(pos_domains + ["1"]) + '"' neg_domains_str = '"' + '", "'.join(neg_domains + ["1"]) + '"' joined_domains_str = '"' + \ '", "'.join(pos_domains + neg_domains + ["1"]) + '"' # get all the relevant arcs sql_q = '''SELECT DISTINCT arc_id, source, target, weight_n1 from arcs join (SELECT node_id FROM nodes WHERE label IN (''' + \ joined_domains_str + \ ''')) as A ON arcs.source = A.node_id OR arcs.target = A.node_id''' db.execute(sql_q) arcs = db.fetchall() # get all the relevant pos nodes sql_q = '''SELECT * from nodes WHERE label IN (''' + pos_domains_str + ''') ORDER BY alexa ASC''' db.execute(sql_q) sel_pos_nodes = db.fetchall() # get all the relevant neg nodes sql_q = '''SELECT * from nodes WHERE label IN (''' + neg_domains_str + ''') ORDER BY alexa ASC''' db.execute(sql_q) sel_neg_nodes = db.fetchall() # get recommendation sql_q = '''SELECT node_id,label, alexa,location,n_out,n_in,w_out,w_in,w_diff,w_self,p_rank, SUM(amount) as am FROM ((SELECT * from ( SELECT * from nodes JOIN (SELECT col, SUM(weight_n2)/2.0 as amount FROM ( (SELECT source as col, weight_n2 from arcs JOIN (SELECT node_id FROM nodes WHERE label IN ( ''' + pos_domains_str + ''')) as A ON arcs.target = A.node_id ) UNION (SELECT target as col, weight_n2 from arcs JOIN (SELECT node_id FROM nodes WHERE label IN ( ''' + pos_domains_str + ''')) as B ON arcs.source = B.node_id )) as T GROUP BY col ) as E on nodes.node_id = E.col ORDER BY amount DESC LIMIT 100) as G WHERE label NOT IN (''' + joined_domains_str + ''')) UNION (SELECT * from ( SELECT * from nodes JOIN (SELECT col, -SUM(weight_n2) as amount FROM ( (SELECT source as col, weight_n2 from arcs JOIN (SELECT node_id FROM nodes WHERE label IN ( ''' + neg_domains_str + ''')) as A ON arcs.target = A.node_id ) UNION (SELECT target as col, weight_n2 from arcs JOIN (SELECT node_id FROM nodes WHERE label IN ( ''' + neg_domains_str + ''')) as B ON arcs.source = B.node_id )) as T GROUP BY col ) as E on nodes.node_id = E.col ORDER BY amount ASC LIMIT 10000) as G WHERE label NOT IN (''' + joined_domains_str + ''')) ) as j GROUP BY node_id,label,alexa,location,n_out,n_in,w_out,w_in,w_diff,w_self,p_rank ORDER BY am DESC ''' db.execute(sql_q) out = db.fetchall() rec_nodes = np.array(out) return arcs, sel_pos_nodes, sel_neg_nodes, rec_nodes
def zone_soa_insert_default(tags): # Get Zone fields = str(list(tags.keys())[0]) record = list() column_record = model.get_columns("v_record") query = "select * from v_record where "+fields+"='"+tags[fields]+"' AND nm_type='SOA'" db.execute(query) rows = db.fetchall() for row in rows: record.append(dict(zip(column_record, row))) column_ttl = model.get_columns("v_ttldata") query = "select * from v_ttldata where "+fields+"='"+tags[fields]+"' AND nm_type='SOA'" db.execute(query) rows = db.fetchall() ttldata = list() for row in rows: ttldata.append(dict(zip(column_ttl, row))) content_data = list() column_cdata= model.get_columns("v_contentdata") query = "select * from v_contentdata where "+fields+"='"+tags[fields]+"' AND nm_type='SOA'" db.execute(query) rows = db.fetchall() for row in rows: content_data.append(dict(zip(column_cdata, row))) content_serial = list() column_cserial= model.get_columns("v_content_serial") query = "select * from v_content_serial where "+fields+"='"+tags[fields]+"' AND nm_type='SOA'" db.execute(query) rows = db.fetchall() for row in rows: content_serial.append(dict(zip(column_cserial, row))) serial_data = "" data = "" date_t = record[0]['date_record'] for ns in content_data: data = data+" "+ns['nm_content'] data_ns_soa = data for serial in content_serial: serial_data = serial_data+" "+serial['nm_content_serial'] data_ns_serial = serial_data json_command={ "soa-set": { "sendblock": { "cmd": "zone-set", "zone": record[0]['nm_zone'], "owner": record[0]['nm_record'], "rtype": "SOA", "ttl": ttldata[0]['nm_ttl'], "data": data_ns_soa+" "+date_t+" "+data_ns_serial }, "receive": { "type": "command" } } } # print(json_command) return record[0]['id_record'], json_command
def z_prep_var_command_mx(): token = headers['access_token'] query = "select * from v_record where id_zone ='"+zone_id+"' AND nm_type = 'MX'" db.execute(query) rows = db.fetchall() if not rows: type_id = getId('MX','type',token) data = { "insert": { "fields": { "nm_record":"ThisIsaTest", "date_record":"2018070410", "id_zone":zone_id, "id_type":type_id } } } res = requests.post(url='http://127.0.0.1:6968/api/record', data = json.dumps(data), headers = token) temp = res.json() print(temp) record_id = temp['message']['id'] print(record_id) ttl_id = getId('14400','ttl',token) data_ttldata = { "insert": { "fields": { "id_record": record_id, "id_ttl": ttl_id } } } res_ttldata = requests.post(url='http://127.0.0.1:6968/api/ttldata', data = json.dumps(data_ttldata), headers = token) temp = res_ttldata.json() ttldata_id = temp['message']['id'] data_content = { "insert": { "fields": { "id_ttldata": ttldata_id, "nm_content": 'This is a test' } } } res_content = requests.post(url='http://127.0.0.1:6968/api/content', data = json.dumps(data_content), headers = token) data_cs = { "insert": { "fields": { "nm_content_serial": '38400', "id_record": record_id } } } res_cs = requests.post(url='http://127.0.0.1:6968/api/content_serial', data = json.dumps(data_cs), headers = token) return zone_id
def post(self): json_req = request.get_json(force=True) command = utils.get_command(request.path) command = 'dt_' + command init_data = cmd.parser(json_req, command) respons = dict() if init_data['action'] == 'insert': table = init_data['data'][0]['table'] fields = init_data['data'][0]['fields'] try: result = model.insert(table, fields) except Exception as e: respons = {"status": False, "error": str(e)} else: respons = {"status": True, "messages": "Success", "id": result} finally: return response(200, data=fields, message=respons) if init_data['action'] == 'remove': table = "" tags = dict() fields = "" for i in init_data['data']: table = i['table'] tags = i['tags'] fields = str(list(tags.keys())[0]) try: result = model.delete(table, fields, tags[fields]) except Exception as e: respons = {"status": False, "messages": str(e)} else: respons = {"status": result, "messages": "Success!"} finally: return response(200, data=tags, message=respons) if init_data['action'] == 'where': obj_userdata = list() table = "" fields = "" tags = dict() for i in init_data['data']: table = i['table'] tags = i['tags'] for a in tags: if tags[a] is not None: fields = a try: result = model.get_by_id(table, fields, tags[fields]) except Exception as e: respons = {"status": False, "messages": str(e)} else: for i in result: data = { "id_hosting": str(i['id_hosting']), "id_company_product": str(i['id_company_product']), 'spec_price': i['spec_price'], "spec_storage": i['spec_storage'], 'spec_database': i['spec_database'], "spec_free_domain": i['spec_free_domain'], "spec_hosting_domain": i['spec_hosting_domain'], 'spec_subdomain': i['spec_subdomain'], "spec_ftp_user": i['spec_ftp_user'], "spec_control_panel": i['spec_control_panel'], 'spec_email_account': i['spec_email_account'], "spec_spam_filter": i['spec_spam_filter'], "date_time": i['date_time'] } obj_userdata.append(data) respons = {"status": True, "messages": "Fine!"} finally: return response(200, data=obj_userdata, message=respons) if init_data['action'] == 'view': obj_userdata = list() table = "" fields = None tags = dict() for i in init_data['data']: table = i['table'] tags = i['tags'] for a in tags: if tags[a] is not None: fields = a column = model.get_columns("v_product_hosting") try: result = list() if fields is None: query = """select * from v_product_hosting""" db.execute(query) rows = db.fetchall() for row in rows: result.append(dict(zip(column, row))) else: query = """ select * from v_product_hosting where """ + fields + """='""" + tags[ fields] + """'""" db.execute(query) rows = db.fetchall() for row in rows: result.append(dict(zip(column, row))) except Exception as e: respons = {"status": False, "messages": str(e)} else: for i in result: data = { "id_company": str(i["id_company"]), "id_product": str(i["id_product"]), "id_hosting": str(i['id_hosting']), "id_company_product": str(i['id_company_product']), "id_additional_features": str(i["id_additional_features"]), "nm_company": i['nm_company'], "url_company": i['url_company'], "nm_product": i['nm_product'], "nm_company_product": i['nm_company_product'], "spec_storage": i['spec_storage'], 'spec_price': i['spec_price'], "spec_storage": i['spec_storage'], 'spec_database': i['spec_database'], "spec_free_domain": i['spec_free_domain'], "spec_hosting_domain": i['spec_hosting_domain'], 'spec_subdomain': i['spec_subdomain'], "spec_ftp_user": i['spec_ftp_user'], "spec_control_panel": i['spec_control_panel'], 'spec_email_account': i['spec_email_account'], "spec_spam_filter": i['spec_spam_filter'], "date_time": i['date_time'], "spec_features": i['spec_features'], "spec_features_value": i['spec_features_value'], "spec_features_price": i['spec_features_price'] } obj_userdata.append(data) respons = {"status": True, "messages": "Fine!"} finally: return response(200, data=obj_userdata, message=respons)
def search(): # Renders index.html. # connect db conn = pymysql.connect(user=user, passwd=passwd, host=host, port=port) db = conn.cursor() db.execute('USE news_graph') # get list of all domains sql_q = ''' SELECT label, alexa from nodes ORDER BY alexa ASC''' db.execute(sql_q) tuples = db.fetchall() all_domains = [{'name': v[0]} for v in tuples] all_domains_list = [x['name'] for x in all_domains] # generate forms pos_form = PosForm(csrf_enabled=False) neg_form = NegForm(csrf_enabled=False) sliders_form = SlidersForm(csrf_enabled=False) # dafault values for sliders if "config" in session: config = session["config"] else: config = { 'neg_domains': [], 'pos_domains': [], 'links_slider': '40', 'alexa_slider': '40', 'p_rank_slider': '40', 'in_slider': '0', 'out_slider': '0', 'self_slider': '0'} rank_sel = [int( config[ 'links_slider']), int(config['alexa_slider']), int(config['p_rank_slider']), int(config['in_slider']), int(config['out_slider']), int(config['self_slider'])] # add pos domain if pos_form.data["add_pos_domain"]: domain = pos_form.data["add_pos_domain"] if domain not in config["pos_domains"] + config["neg_domains"] and domain in all_domains_list: config['pos_domains'].append(domain) # add neg domain if neg_form.data["add_neg_domain"]: domain = neg_form.data["add_neg_domain"] if domain not in config["pos_domains"] + config["neg_domains"] and domain in all_domains_list: config['neg_domains'].append(domain) # delete pos domain if pos_form.data["del_pos_domain"]: domain = pos_form.data["del_pos_domain"] config['pos_domains'].remove(domain) # delete neg domain if neg_form.data["del_neg_domain"]: domain = neg_form.data["del_neg_domain"] config['neg_domains'].remove(domain) # sliders change if sliders_form.data['links_slider']: rank_sel = [int( sliders_form.data[ 'links_slider']), int(sliders_form.data['alexa_slider']), int(sliders_form.data['p_rank_slider']), int( sliders_form.data['in_slider']), int(sliders_form.data['out_slider']), int(sliders_form.data['self_slider'])] config['links_slider'] = str(rank_sel[0]) config['alexa_slider'] = str(rank_sel[1]) config['p_rank_slider'] = str(rank_sel[2]) config['in_slider'] = str(rank_sel[3]) config['out_slider'] = str(rank_sel[4]) config['self_slider'] = str(rank_sel[5]) session["config"] = config if len(config['neg_domains']) == 0 and len(config['pos_domains']) == 0: return render_template('search.html', pos_form=pos_form, neg_form=neg_form, sliders_form=sliders_form, all_domains=all_domains, config=config) arcs, sel_pos_nodes, sel_neg_nodes, rec_nodes = query_db( db, config["pos_domains"], config["neg_domains"]) data, graph = process(arcs, sel_pos_nodes, sel_neg_nodes, rec_nodes, config[ "pos_domains"], config["neg_domains"], np.array(rank_sel) / float(sum(rank_sel))) return render_template('search.html', pos_form=pos_form, neg_form=neg_form, sliders_form=sliders_form, all_domains=all_domains, config=config, data=data, graph=graph)
def post(self): json_req = request.get_json(force=True) command = utils.get_command(request.path) command = "zn_" + command init_data = cmd.parser(json_req, command) respons = dict() if init_data['action'] == 'insert': table = init_data['data'][0]['table'] fields = init_data['data'][0]['fields'] try: result = model.insert(table, fields) except Exception as e: respons = {"status": False, "error": str(e)} else: respons = {"status": True, "messages": "Fine!", "id": result} finally: return response(200, data=fields, message=respons) if init_data['action'] == 'where': obj_userdata = list() table = "" fields = "" tags = dict() for i in init_data['data']: table = i['table'] tags = i['tags'] for a in tags: if tags[a] is not None: fields = a try: result = model.get_by_id(table, fields, tags[fields]) except Exception as e: respons = {"status": False, "messages": str(e)} else: for i in result: data = { "id_content": str(i['id_content']), "id_ttldata": str(i['id_ttldata']), "nm_content": str(i['nm_content']) } obj_userdata.append(data) respons = {"status": True, "messages": "Fine!"} finally: return response(200, data=obj_userdata, message=respons) if init_data['action'] == 'remove': table = "" tags = dict() fields = "" for i in init_data['data']: table = i['table'] tags = i['tags'] for a in tags: if tags[a] is not None: fields = a try: result = model.delete(table, fields, tags[fields]) except Exception as e: respons = {"status": False, "messages": str(e)} else: respons = {"status": result, "messages": "Fine Deleted!"} finally: return response(200, data=tags, message=respons) if init_data['action'] == 'view': obj_userdata = list() table = "" fields = None tags = dict() for i in init_data['data']: table = i['table'] tags = i['tags'] for a in tags: if tags[a] is not None: fields = a column = model.get_columns("v_contentdata") try: result = list() if fields is None: query = """select * from v_contentdata""" db.execute(query) rows = db.fetchall() for row in rows: result.append(dict(zip(column, row))) else: query = """ select * from v_contentdata where """ + fields + """='""" + tags[ fields] + """'""" db.execute(query) rows = db.fetchall() for row in rows: result.append(dict(zip(column, row))) except Exception as e: respons = {"status": False, "messages": str(e)} else: for i in result: data = { "id_content": str(i['id_content']), "nm_zone": str(i['nm_zone']), "nm_record": str(i['nm_record']), "nm_type": str(i['nm_type']), "nm_ttl": i['nm_ttl'], "id_record": str(i['id_record']), "nm_content": str(i['nm_content']), } obj_userdata.append(data) respons = {"status": True, "messages": "Fine!"} finally: return response(200, data=obj_userdata, message=respons)
def post(self): json_req = request.get_json(force=True) command = utils.get_command(request.path) command = 'dt_' + command init_data = cmd.parser(json_req, command) respons = {} if init_data['action'] == 'insert': table = init_data['data'][0]['table'] fields = init_data['data'][0]['fields'] try: result = model.insert(table, fields) except Exception as e: respons = {"status": False, "error": str(e)} else: respons = {"status": True, "messages": "Success", "id": result} finally: return response(200, data=fields, message=respons) if init_data['action'] == 'remove': table = "" tags = dict() fields = "" for i in init_data['data']: table = i['table'] tags = i['tags'] fields = str(list(tags.keys())[0]) try: result = model.delete(table, fields, tags[fields]) except Exception as e: respons = {"status": False, "messages": str(e)} else: respons = {"status": result, "messages": "Success!"} finally: return response(200, data=tags, message=respons) if init_data['action'] == 'view': obj_userdata = list() table = "" fields = None tags = dict() for i in init_data['data']: table = i['table'] tags = i['tags'] for a in tags: if tags[a] is not None: fields = a column = model.get_columns("v_product_vm") try: result = list() if fields is None: query = """select * from v_product_vm""" db.execute(query) rows = db.fetchall() for row in rows: result.append(dict(zip(column, row))) else: query = """ select * from v_product_vm_test where """ + fields + """='""" + tags[ fields] + """'""" db.execute(query) rows = db.fetchall() for row in rows: result.append(dict(zip(column, row))) except Exception as e: respons = {"status": False, "messages": str(e)} else: for i in result: data = { "id_company": str(i['id_company']), "id_company_product": str(i['id_company_product']), "id_product": str(i['id_product']), 'id_vm': str(i['id_vm']), "id_additional_features": str(i['id_additional_features']), "nm_company": i['nm_company'], "url_company": i['url_company'], 'nm_company_product': i['nm_company_product'], "nm_product": i['nm_product'], "currency_used": i['currency_used'], 'spec_clock': i['spec_clock'], "spec_ram": i['spec_ram'], "spec_os": i['spec_os'], 'spec_storage_volume': i['spec_storage_volume'], "spec_ssd_volume": i['spec_ssd_volume'], "spec_snapshot_volume": i['spec_snapshot_volume'], "spec_template_volume": i['spec_template_volume'], 'spec_iso_volume': i['spec_iso_volume'], "spec_public_ip": i['spec_public_ip'], "spec_backup_storage": i['spec_backup_storage'], 'spec_features': i['spec_features'], "spec_features_value": i['spec_features_value'], "spec_features_price": i['spec_features_price'], 'spec_price': i['spec_price'], "date_time": i['date_time'] } obj_userdata.append(data) respons = {"status": True, "messages": "Fine!"} finally: return response(200, data=obj_userdata, message=respons) if init_data['action'] == 'where': obj_userdata = list() table = "" fields = "" tags = dict() for i in init_data['data']: table = i['table'] tags = i['tags'] for a in tags: if tags[a] is not None: fields = a try: result = model.get_by_id(table, fields, tags[fields]) except Exception as e: respons = {"status": False, "messages": str(e)} else: for i in result: data = { "id_vm": str(i['id_vm']), "id_company_product": str(i['id_company_product']), "spec_vcpu": i['spec_vcpu'], 'spec_clock': i['spec_clock'], "spec_ram": i['spec_ram'], "spec_os": i['spec_os'], "spec_storage_volume": i['spec_storage_volume'], 'spec_ssd_volume': i['spec_ssd_volume'], "spec_snapshot_volume": i['spec_snapshot_volume'], "spec_template_volume": i['spec_template_volume'], 'spec_iso_volume': i['spec_iso_volume'], "spec_public_ip": i['spec_public_ip'], "spec_backup_storage": i['spec_backup_storage'], 'spec_price': i['spec_price'], "spec_notes": i['spec_notes'], "date_time": i['date_time'] } obj_userdata.append(data) respons = {"status": True, "messages": "Fine!"} finally: return response(200, data=obj_userdata, message=respons)
def scheduledTask(): conn = mysql.connector.connect(**config) db = conn.cursor() db.execute( '''SELECT max(last_run_date) FROM scheduler_log where status = "Success"''' ) last_success_date = db.fetchall()[0][0] # last_success_date = datetime.datetime.strptime(last_success_date,"%Y-%m-%d").date() today = datetime.datetime.now().date() db.execute( ''' SELECT user.id, plan_cycle, rate_plan, next_invoice_date, balance_info.due_amount AS due_amount, discount_amount, pending_intervals, invoices.id FROM User_data user JOIN invoices ON user.id = invoices.user_id JOIN balance_info ON balance_info.user_id = user.id WHERE status = "Active" AND invoices.next_invoice_date between %s and %s AND processed = 0 ORDER BY next_invoice_date ASC ''', [last_success_date, today]) info = db.fetchall() for i in range(len(info)): due_amount = int(info[i][4]) amount_calc = 1 plan_cycle = int(info[i][1]) if plan_cycle == 1: amount_calc = 1 if plan_cycle == 2: amount_calc = 3 if plan_cycle == 3: amount_calc = 6 if plan_cycle == 4: amount_calc = 12 db.execute('''UPDATE invoices SET processed = 1 where id = %s''', [int(info[i][7])]) current_amount = int(info[i][5]) * amount_calc invoice_date = info[i][3] # datetime.datetime.strptime(info[i][3],"%Y-%m-%d").date() next_invoice_date = next_due_date(plan_cycle, invoice_date, 1) db.execute( '''INSERT INTO invoices(user_id,due_amount,current_cycle_amount,user_status,invoice_date,next_invoice_date,processed) VALUES(%s,%s,%s,%s,%s,%s,0) ''', [ int(info[i][0]), due_amount, current_amount, "Active", invoice_date, next_invoice_date ]) due_amount = int(info[i][4]) + current_amount intevals = int(info[i][6]) + 1 db.execute( '''UPDATE balance_info SET due_amount = due_amount + %s, one_interval_amount = %s, pending_intervals = %s WHERE user_id = %s ''', [current_amount, current_amount, intevals, int(info[i][0])]) conn.commit() db.execute( '''INSERT INTO scheduler_log(last_run_date,status) VALUES(%s,"Success")''', [datetime.date.today()]) conn.commit()