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()
