class UserProfile(Common):
"""Class for """
# INITIALIZE
def __init__(self):
"""The Constructor for UserProfile class"""
self.postgres = PostgreSQL()
self.couch_query = Queries()
super(UserProfile, self).__init__()
# LOGIN FUNCTION
def user_profile(self):
"""
This API is for Getting User Information
---
tags:
- User
produces:
- application/json
parameters:
- name: token
in: header
description: Token
required: true
type: string
- name: userid
in: header
description: User ID
required: true
type: string
responses:
500:
description: Error
200:
description: User Information
"""
data = {}
# GET DATA
token = request.headers.get('token')
userid = request.headers.get('userid')
# CHECK TOKEN
token_validation = self.validate_token(token, userid)
if not token_validation:
data["alert"] = "Invalid Token"
data['status'] = 'Failed'
# RETURN ALERT
return self.return_data(data)
# OPEN CONNECTION
self.postgres.connection()
datas = self.get_users(userid)
# CLOSE CONNECTION
self.postgres.close_connection()
datas['status'] = 'ok'
return self.return_data(datas)
def get_users(self, userid):
"""Return Users"""
# DATA
sql_str = """
SELECT a.id, a.username, a.email, a.status,
a.first_name, a.middle_name, a.last_name, (
SELECT array_to_json(array_agg(role_perm)) FROM (
SELECT r.role_id, r.role_name, r.role_details, (
SELECT array_to_json(array_agg(p)) FROM permission p
INNER JOIN role_permission rp ON
rp.permission_id = p.permission_id
WHERE rp.role_id = r.role_id) AS permissions
FROM role r INNER JOIN account_role ar
ON ar.role_id = r.role_id
WHERE ar.account_id = a.id) AS role_perm) AS roles, (
SELECT array_to_json(array_agg(c)) FROM company c
INNER JOIN account_company ac
ON ac.company_id = c.company_id
WHERE ac.account_id = a.id) AS companies FROM account a
WHERE a.id=
"""
sql_str += userid
res = self.postgres.query_fetch_one(sql_str)
if res:
if "no_username" in res['username']:
res['username'] = ""
res["vessels"] = self.get_vessels(userid)
data = {}
data['data'] = res
return data
def get_vessels(self, account_id):
"""Return Companies"""
# DATA
sql_str = "SELECT * FROM account_company WHERE account_id = " + str(
account_id)
res = self.postgres.query_fetch_all(sql_str)
vessels = []
# COMPANY'S VESSELS-ID
for company in res:
company_id = company['company_id']
vessel_res = self.get_company_vessels(company_id)
for vessel in vessel_res['rows']:
temp = {}
temp['vessel_id'] = vessel['vessel_id']
temp['allow_access'] = self.get_allow_access(
account_id, vessel['vessel_id'])
vessels.append(temp)
return vessels
# GET VESSELS OF COMPANY
def get_company_vessels(self, company_id): #, user=None):
"""Return Company Vessels"""
assert company_id, "CompanyID is required."
# DATA
vessels = []
sql_str = "SELECT * FROM company_vessels WHERE company_id={0}".format(
company_id)
vessels = self.postgres.query_fetch_all(sql_str)
data = {}
data['rows'] = vessels
return data
def get_allow_access(self, account_id, vessel_id):
"""Return Allow access"""
# DATA
sql_str = "SELECT * FROM account_vessel"
sql_str += " WHERE account_id={0} AND vessel_id='{1}'".format(
account_id, vessel_id)
vessel = self.postgres.query_fetch_one(sql_str)
if vessel:
return vessel['allow_access']
return False
class Setup():
def __init__(self):
self.sha_security = ShaSecurity()
self.postgres = PostgreSQL()
# INIT CONFIG
self.config = ConfigParser()
# CONFIG FILE
self.config.read("config/config.cfg")
def main(self):
self.create_database()
self.create_tables()
self.create_default_entries()
def create_database(self):
self.dbname = configSectionParser(self.config,"POSTGRES")['db_name']
self.postgres.connection(True)
self.postgres.create_database(self.dbname)
self.postgres.close_connection()
def create_tables(self):
# OPEN CONNECTION
self.postgres.connection()
# VPN-ACCESS - CONFIG-FILE
query_str = """
CREATE TABLE public.vpn_access_conf_file (
config_file_name varchar(200) NOT null unique
);
"""
print("Create table: vpn_access_conf_file")
if self.postgres.exec_query(query_str):
print("Account VPN Access table successfully created!")
# VPN-ACCESS
query_str = """
CREATE TABLE public.vpn_access(
vpn_type varchar(16) NOT NULL,
prefix varchar(4) NOT null,
config_file_name varchar(200) NOT null,
octet_1 int4 NOT NULL,
octet_2_min int4 NOT NULL,
octet_2_max int4 NOT NULL,
octet_3_min int4 NOT NULL,
octet_3_max int4 NOT NULL,
octet_4_min int4 NOT NULL,
octet_4_max int4 NOT NULL,
current_ip_1 varchar(16) NOT NULL DEFAULT '0.0.0.0',
current_ip_2 varchar(16) NOT NULL DEFAULT '0.0.0.0',
current_reused_ip varchar(16) NOT NULL DEFAULT '0.0.0.0',
update_on timestamp NULL,
CONSTRAINT vpn_access_type_pkey PRIMARY KEY (vpn_type),
CONSTRAINT vpn_access_vpn_type_key UNIQUE (vpn_type),
CONSTRAINT account_vpn_access_conf_file_fkey FOREIGN KEY (config_file_name) REFERENCES vpn_access_conf_file(config_file_name) ON UPDATE CASCADE
);
"""
print("Create table: vpn_access")
if self.postgres.exec_query(query_str):
print("VPN Access table successfully created!")
# VPN-ACCESS-REQUESTS-STATUS
query_str = """
CREATE TABLE public.vpn_access_requests_status (
id serial NOT NULL,
status varchar(16) NOT NULL,
description varchar(200) NULL,
CONSTRAINT vpn_access_requests_status_status_key UNIQUE (status)
);
"""
print("Create table: vpn_access_requests_status")
if self.postgres.exec_query(query_str):
print("VPN Access Requests Status table successfully created!")
# VPN-ACCESS-REQUESTS
query_str = """
CREATE TABLE public.vpn_access_requests (
id serial NOT NULL,
job_id int8 NULL UNIQUE,
vpn_type varchar(16) NULL, -- NULL BECAUSE OF MANUAL INSERT
vpn_request_type varchar(16) NULL, -- NULL BECAUSE OF MANUAL INSERT
account_id int8 NOT NULL, -- NULL BECAUSE OF MANUAL INSERT
account_name varchar(80) NULL,
account_os varchar(40) NULL,
request_start_date timestamp DEFAULT CURRENT_TIMESTAMP,
request_finish_date timestamp NULL,
status varchar(20) NOT NULL DEFAULT 'INITIALIZED'::character varying,
status_details varchar(400) NULL,
callback_status int NULL,
CONSTRAINT vpn_access_requests_id_key UNIQUE (id),
CONSTRAINT vpn_access_requests_status FOREIGN KEY (status) REFERENCES vpn_access_requests_status(status) ON UPDATE cascade
);
"""
print("Create table: vpn_access_requests")
if self.postgres.exec_query(query_str):
print("VPN Access Requests table successfully created!")
# ACCOUNT-VPN-ACCESS
query_str = """
CREATE TABLE public.account_vpn_access(
id_vpn_access_requests int8,
ip_addr_1 varchar(16) NOT NULL,
ip_addr_2 varchar(16) NOT NULL,
vpn_type varchar(16) NOT NULL,
account_id int8 NULL, -- NULL BECAUSE OF MANUAL INSERT
zip_file_path varchar(200) NULL,
is_active int2 NULL,
last_update_date timestamp NULL,
account_filename varchar(200) NULL,
manually_added int2 NULL DEFAULT 0,
date_created timestamp default CURRENT_TIMESTAMP,
--CONSTRAINT account_vpn_access_pkey PRIMARY KEY (vpn_type, account_id, id_vpn_access_requests),
CONSTRAINT account_vpn_access_request_id_fkey FOREIGN KEY (id_vpn_access_requests) REFERENCES vpn_access_requests(id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT account_vpn_access_vpn_type_fkey FOREIGN KEY (vpn_type) REFERENCES vpn_access(vpn_type) ON UPDATE CASCADE ON DELETE CASCADE
);
"""
print("Create table: account_vpn_access")
if self.postgres.exec_query(query_str):
print("Account VPN Access table successfully created!")
# ACCOUNT-VPN-ACCESS WEBPROD-APIPROD
query_str = """
CREATE TABLE public.account_vpn_access_webprod_apiprod (
id serial NOT NULL,
id_vpn_access_requests int8 NULL,
request_type varchar(20) NOT NULL,
account_id int8 NOT NULL,
account_vpn_ip varchar(15) NOT NULL,
is_active int4 NOT NULL,
status varchar(20) NULL,
status_details varchar(400) NULL,
request_finish_date timestamp NOT NULL,
CONSTRAINT account_vpn_access_webprod_apiprod_request_id_fkey FOREIGN KEY (id_vpn_access_requests) REFERENCES vpn_access_requests(id) ON UPDATE CASCADE ON DELETE CASCADE
);
"""
print("Create table: account_vpn_access_webprod_apiprod")
if self.postgres.exec_query(query_str):
print("Account VPN Access(WEBPROD-APIPROD) table successfully created!")
#VPN_ACCESS_REAL_VESSEL_REQUESTS
query_str = """
CREATE TABLE public.vpn_access_real_vessel_requests (
id serial NOT NULL,
request_type varchar(40) NULL,
job_id int8 UNIQUE NOT NULL,
vessel_ip_address varchar(15) NOT NULL,
vessel_port varchar(80) NOT NULL,
vessel_username varchar(80) NOT NULL,
vessel_password varchar(80) NOT NULL,
token varchar(400) NOT NULL,
callback_url varchar(400) NULL,
account_id int8 NOT NULL,
account_vpn_ip varchar(15) NOT NULL,
request_start_date timestamp NOT NULL,
request_finish_date timestamp NULL,
status varchar(20) NULL,
status_details varchar(400) NULL,
callback_status int4 NULL,
is_ssh_connection_error int2 DEFAULT 0,
request_tries int2 DEFAULT 0,
CONSTRAINT vpn_access_real_vessel_requests_id_key UNIQUE (id)
--CONSTRAINT vpn_access_real_vessel_requests_job_id_key UNIQUE (job_id)
);
"""
print("Create table: vpn_access_real_vessel_requests")
if self.postgres.exec_query(query_str):
print("Account VPN Access table successfully created!")
#ACCOUNT_VPN_ACCESS_REAL_VESSEL
query_str = """
CREATE TABLE public.account_vpn_access_real_vessel (
id serial NOT NULL,
add_job_id int8,
remove_job_id int8,
account_ip_address varchar(16) NOT NULL,
account_id int8 NULL,
is_active int2 NULL,
date_created timestamp NULL DEFAULT CURRENT_TIMESTAMP,
last_update_date timestamp NULL,
CONSTRAINT account_vpn_access_real_vessel_job_id_fkey FOREIGN KEY (add_job_id) REFERENCES vpn_access_real_vessel_requests(job_id) ON DELETE cascade--,
);
"""
print("Create table: account_vpn_access_real_vessel")
if self.postgres.exec_query(query_str):
print("Account ACCOUNT_VPN_ACCESS_REAL_VESSEL table successfully created!")
# CLOSE CONNECTION
self.postgres.close_connection()
def create_default_entries(self):
# VPN-ACCESS - CONFIG-FILE (WEB_API)
data = {}
data['config_file_name'] = 'WEB_API_VPN_DEFAULT_CONFIGURATION_FILE'
print("Create default vpn-access-conf-file: ", data['config_file_name'])
self.postgres.insert('vpn_access_conf_file', data)
# VPN-ACCESS - CONFIG-FILE (VESSEL)
data = {}
data['config_file_name'] = 'VESSEL_VPN_DEFAULT_CONFIGURATION_FILE'
print("Create default vpn-access-conf-file: ", data['config_file_name'])
self.postgres.insert('vpn_access_conf_file', data)
#1 CLIENT 172.16.0.0/24 - 172.23.0.0/24
# VPN-ACCESS CLIENT
data = {}
data['vpn_type'] = 'CLIENT'
data['prefix'] = 'CLI'
data['config_file_name'] = 'WEB_API_VPN_DEFAULT_CONFIGURATION_FILE'
data['octet_1'] = '172'
data['octet_2_min'] = '16'
data['octet_2_max'] = '23'
data['octet_3_min'] = '1'
data['octet_3_max'] = '254'
data['octet_4_min'] = '1'
data['octet_4_max'] = '254'
data['current_ip_1'] = '0.0.0.0'
data['current_ip_2'] = '0.0.0.0'
print("Create default vpn-access: ", data['vpn_type'])
self.postgres.insert('vpn_access', data)
#4 UNKNOWN 10.8.80.0 - 10.8.84.0
# VPN-ACCESS GBF
data = {}
data['vpn_type'] = 'UNKNOWN'
data['prefix'] = 'UNKN'
data['config_file_name'] = 'WEB_API_VPN_DEFAULT_CONFIGURATION_FILE'
data['octet_1'] = '10'
data['octet_2_min'] = '8'
data['octet_2_max'] = '8'
data['octet_3_min'] = '80'
data['octet_3_max'] = '84'
data['octet_4_min'] = '1'
data['octet_4_max'] = '254'
data['current_ip_1'] = '0.0.0.0'
data['current_ip_2'] = '0.0.0.0'
print("Create default vpn-access: ", data['vpn_type'])
self.postgres.insert('vpn_access', data)
#1 VGBF GLOBAL BRAINFORCE INC. 10.10.10.0 - 10.10.13.0 (10.10.10.0/24)
# VPN-ACCESS-VESSEL CLIENT
data = {}
data['vpn_type'] = 'VGBF'
data['prefix'] = 'VGBF'
data['config_file_name'] = 'VESSEL_VPN_DEFAULT_CONFIGURATION_FILE'
data['octet_1'] = '10'
data['octet_2_min'] = '10'
data['octet_2_max'] = '10'
data['octet_3_min'] = '10'
data['octet_3_max'] = '13'
data['octet_4_min'] = '1'
data['octet_4_max'] = '254'
data['current_ip_1'] = '0.0.0.0'
data['current_ip_2'] = '0.0.0.0'
print("Create default vpn-access: ", data['vpn_type'])
self.postgres.insert('vpn_access', data)
#2 VRH RADIO HOLLAND 10.10.20.0 - 10.10.23.0 (10.10.20.0/24)
# VPN-ACCESS-VESSEL CLIENT
data = {}
data['vpn_type'] = 'VRH'
data['prefix'] = 'VRH'
data['config_file_name'] = 'VESSEL_VPN_DEFAULT_CONFIGURATION_FILE'
data['octet_1'] = '10'
data['octet_2_min'] = '10'
data['octet_2_max'] = '10'
data['octet_3_min'] = '20'
data['octet_3_max'] = '23'
data['octet_4_min'] = '1'
data['octet_4_max'] = '254'
data['current_ip_1'] = '0.0.0.0'
data['current_ip_2'] = '0.0.0.0'
print("Create default vpn-access: ", data['vpn_type'])
self.postgres.insert('vpn_access', data)
#3 VCLIENT CLIENT 172.24.0.0/24 - 172.31.0.0/24
# VPN-ACCESS-VESSEL CLIENT
data = {}
data['vpn_type'] = 'VCLIENT'
data['prefix'] = 'VCLI'
data['config_file_name'] = 'VESSEL_VPN_DEFAULT_CONFIGURATION_FILE'
data['octet_1'] = '172'
data['octet_2_min'] = '24'
data['octet_2_max'] = '31'
data['octet_3_min'] = '1'
data['octet_3_max'] = '254'
data['octet_4_min'] = '1'
data['octet_4_max'] = '254'
data['current_ip_1'] = '0.0.0.0'
data['current_ip_2'] = '0.0.0.0'
print("Create default vpn-access: ", data['vpn_type'])
self.postgres.insert('vpn_access', data)
#4 VESSEL REAL-VESSEL 10.20.0.0 - 10.80.0.0 (10.20.0.0/24)
# VPN-ACCESS-VESSEL CLIENT
data = {}
data['vpn_type'] = 'VESSEL'
data['prefix'] = 'VSL'
data['config_file_name'] = 'VESSEL_VPN_DEFAULT_CONFIGURATION_FILE'
data['octet_1'] = '10'
data['octet_2_min'] = '20'
data['octet_2_max'] = '80'
data['octet_3_min'] = '1'
data['octet_3_max'] = '254'
data['octet_4_min'] = '1'
data['octet_4_max'] = '254'
data['current_ip_1'] = '0.0.0.0'
data['current_ip_2'] = '0.0.0.0'
print("Create default vpn-access: ", data['vpn_type'])
self.postgres.insert('vpn_access', data)
#5 VUNKNOWN VESSEL UNKNOWN 10.10.80.0 - 10.10.84.0
# VPN-ACCESS-VESSEL CLIENT
data = {}
data['vpn_type'] = 'VUNKNOWN'
data['prefix'] = 'VUNK'
data['config_file_name'] = 'VESSEL_VPN_DEFAULT_CONFIGURATION_FILE'
data['octet_1'] = '10'
data['octet_2_min'] = '10'
data['octet_2_max'] = '10'
data['octet_3_min'] = '80'
data['octet_3_max'] = '84'
data['octet_4_min'] = '1'
data['octet_4_max'] = '254'
data['current_ip_1'] = '0.0.0.0'
data['current_ip_2'] = '0.0.0.0'
print("Create default vpn-access: ", data['vpn_type'])
self.postgres.insert('vpn_access', data)
# VPN_ACCESS_REQUESTS_STATUS - ON-GOING
data = {}
data['status'] = 'ON-GOING'
data['description'] = 'Creation of VPN is Ongoing.'
print("Create default VPN_ACCESS_REQUESTS_STATUS: ", data['status'])
self.postgres.insert('vpn_access_requests_status', data)
# VPN_ACCESS_REQUESTS_STATUS - DENIED
data = {}
data['status'] = 'DENIED'
data['description'] = 'Problem Occured during creation of VPN'
print("Create default VPN_ACCESS_REQUESTS_STATUS: ", data['status'])
self.postgres.insert('vpn_access_requests_status', data)
# VPN_ACCESS_REQUESTS_STATUS - INITIALIZED
data = {}
data['status'] = 'INITIALIZED'
data['description'] = 'Request has been initialized.'
print("Create default VPN_ACCESS_REQUESTS_STATUS: ", data['status'])
self.postgres.insert('vpn_access_requests_status', data)
# VPN_ACCESS_REQUESTS_STATUS - FINISHED-VS
data = {}
data['status'] = 'FINISHED-VS'
data['description'] = 'Process in Vessel Server are done.'
print("Create default VPN_ACCESS_REQUESTS_STATUS: ", data['status'])
self.postgres.insert('vpn_access_requests_status', data)
# VPN_ACCESS_REQUESTS_STATUS - DONE
data = {}
data['status'] = 'DONE'
data['description'] = 'All of the vpn creation process is complete.'
print("Create default VPN_ACCESS_REQUESTS_STATUS: ", data['status'])
self.postgres.insert('vpn_access_requests_status', data)
# VPN_ACCESS_REQUESTS_STATUS - CONN-ERR
data = {}
data['status'] = 'CONN-ERR'
data['description'] = 'Cannot Connect using ssh.'
print("Create default VPN_ACCESS_REQUESTS_STATUS: ", data['status'])
self.postgres.insert('vpn_access_requests_status', data)
# VPN_ACCESS_REQUESTS - WEB-API VPN
data = {}
data['job_id'] = -1
data['account_id'] = -1
data['status_details'] = 'For manually added EXISTING WEB-API USER.'
print("Create default VPN_ACCESS_REQUESTS - For manually added EXISTING WEB-API USER: "******"Create default VPN_ACCESS_REQUESTS - For manually added EXISTING REAL-VESSEL-ACCOUNT: ", data['job_id'])
self.postgres.insert('vpn_access_requests', data)
# VPN_ACCESS_REQUESTS - REAL-VESSEL VPN
data = {}
data['job_id'] = -3
data['account_id'] = -3
data['status_details'] = 'For manually creation of REAL-VESSEL VPN.'
print("Create default VPN_ACCESS_REQUESTS - For manually creation of REAL-VESSEL-SERVER VPN: ", data['job_id'])
self.postgres.insert('vpn_access_requests', data)
class CalculateAlarmTrigger(Common):
"""Class for CalculateAlarmTrigger"""
# INITIALIZE
def __init__(self):
"""The Constructor for CalculateAlarmTrigger class"""
self.postgres = PostgreSQL()
self.calc_condition = CalculateAlarmCondition()
self.alarm_type = AlarmType()
super(CalculateAlarmTrigger, self).__init__()
def calculate_trigger(self, trigger_ids, start, end, vessel_id=None, device_id=None):
"""Calculate Alarm Trigger"""
results = []
if vessel_id:
check_vessel = self.validate_vessel_device(vessel_id, "vessel")
if not check_vessel:
return "Invalid Vessel."
if device_id:
check_device = self.validate_vessel_device(device_id, "device")
if not check_device:
# CHECK BY DEVICE NAME
device_id = self.validate_device_name(device_id)
if not device_id:
return "Invalid Device."
for trigger_id in trigger_ids:
alarm_trigger = self.get_alarm_trigger(trigger_id)
# print("*"*50)
# print("alarm_trigger: ", alarm_trigger)
# print("*"*50)
if alarm_trigger:
alarm_type = self.alarm_type.alarm_type(alarm_trigger['alarm_type_id'])
# CALCULATE CONDITION
result = self.calc_condition.calculate_condition(
alarm_trigger['alarm_condition_id'],
start, end, vessel_id, device_id)
# print("+"*50)
# print("result: ", result)
# print("+"*50)
if type(result) == list:
#REMOVE COREVALUES TIMESTAMP
for res in result:
del res['core']
res['alarm_trigger_id'] = alarm_trigger['alarm_trigger_id']
results.append({
"results":result,
"err_message": "",
"alarm_trigger_id": alarm_trigger['alarm_trigger_id'],
"alarm_type_id": alarm_trigger['alarm_type_id'],
"alarm_type": alarm_type['alarm_type'],
"alarm_description": alarm_trigger['description']
})
else:
results.append({
"results":[],
"err_message": result,
"alarm_trigger_id": alarm_trigger['alarm_trigger_id'],
"alarm_type_id": alarm_trigger['alarm_type_id'],
"alarm_type": alarm_type['alarm_type'],
"alarm_description": alarm_trigger['description']
})
# Pylint Error -> Redefinition of results type from list to str
# (redefined-variable-type)
# else:
# results = "No Alarm Trigger found."
return results
# GET ALARM TRIGGER
def get_alarm_trigger(self, alarm_trigger_id):
"""Get Alarm Trigger"""
assert alarm_trigger_id, "Alarm Trigger ID is required."
# OPEN CONNECTION
self.postgres.connection()
# DATA
sql_str = "SELECT * FROM alarm_trigger"
sql_str += " WHERE alarm_trigger_id={0}".format(alarm_trigger_id)
alarm_trigger = self.postgres.query_fetch_one(sql_str)
# CLOSE CONNECTION
self.postgres.close_connection()
if alarm_trigger:
return alarm_trigger
return 0
def validate_vessel_device(self, _id, key):
"""Validate Vessel and Device"""
assert _id, "{0} ID is required.".format(key.upper())
# OPEN CONNECTION
self.postgres.connection()
# DATA
sql_str = "SELECT * FROM {0}".format(key)
sql_str += " WHERE {0}_id= '{1}'".format(key, _id)
data = self.postgres.query_fetch_one(sql_str)
# CLOSE CONNECTION
self.postgres.close_connection()
if data:
return 1
return 0
def validate_device_name(self, device):
"""Validate Device Name"""
assert device, "{0} Device name is required."
sql_str = "SELECT device_id FROM device"
sql_str += " WHERE device = '{0}'".format(device.upper())
data = self.postgres.query_fetch_one(sql_str)
if data:
return data['device_id']
return 0
class DeleteAlarmValue(Common):
"""Class for DeleteAlarmValue"""
# INITIALIZE
def __init__(self):
"""The Constructor for DeleteAlarmValue class"""
self.postgres = PostgreSQL()
super(DeleteAlarmValue, self).__init__()
def delete_alarm_value(self):
"""
This API is for Deleting Alarm Value
---
tags:
- Alarm Values
produces:
- application/json
parameters:
- name: token
in: header
description: Token
required: true
type: string
- name: userid
in: header
description: User ID
required: true
type: string
- name: query
in: body
description: Alarm Value IDs
required: true
schema:
id: Delete Alarm Values
properties:
alarm_value_ids:
types: array
example: []
responses:
500:
description: Error
200:
description: Delete Alarm Value
"""
data = {}
# GET JSON REQUEST
query_json = request.get_json(force=True)
# GET HEADER
token = request.headers.get('token')
userid = request.headers.get('userid')
# GET QUERY
alarm_value_ids = query_json["alarm_value_ids"]
# CHECK TOKEN
token_validation = self.validate_token(token, userid)
if not token_validation:
data['alert'] = "Invalid Token"
data['status'] = 'Failed'
# RETURN ALERT
return self.return_data(data)
if not self.delete_alarm_values(alarm_value_ids):
data["alert"] = "Please check your query!"
data['status'] = 'Failed'
self.postgres.close_connection()
# RETURN ALERT
return self.return_data(data)
data['message'] = "Alarm value successfully Deleted!"
data['status'] = "ok"
return self.return_data(data)
def delete_alarm_values(self, alarm_value_ids):
"""Delete Alarm Values"""
conditions = []
conditions.append({
"col": "alarm_value_id",
"con": "in",
"val": alarm_value_ids
})
if self.postgres.delete('alarm_value', conditions):
return 1
return 0
def tester():
print('\n ** VPN TESTER\n')
OS = [
'WINDOWS',
'LINUX'
]
VPN_TYPES = []
######################## USER SELECTION
postgresql_query = PostgreSQL()
postgresql_query.connection()
sql_str = "SELECT t1.vpn_type, t2.id, t2.first_name FROM account_vpn_access t1 "\
"RIGHT JOIN account t2 ON t2.id=t1.account_id"
account_vpns = postgresql_query.query_fetch_all(sql_str)
postgresql_query.close_connection()
account_vpns_data = { d['first_name']:{'account_id': d['id']} for d in account_vpns}
for _k, _v in account_vpns_data.items():
account_vpns_data[_k]['vpn_list'] = [ d['vpn_type'] for d in account_vpns if d['first_name']==_k and d['vpn_type']]
account_id_list = []
for d in account_vpns_data.items():
account_id_list.append(d)
account_vpns_data_text = []
for index, d in enumerate(account_id_list):
_str = "{}.) {} -> {}".format(index+1, d[0], ', '.join(d[1]['vpn_list']))
account_vpns_data_text.append(_str)
account_vpns_data_text = '\n'.join(account_vpns_data_text)
print('\nUser List:')
print(account_vpns_data_text)
_selected_accound_id = int((input('select user:> ') or 0))-1
selected_accound_id = account_id_list[_selected_accound_id][1]['account_id']
name = account_id_list[_selected_accound_id][0]
######################## USER SELECTION
######################## VPN-ACCESS SELECTION
postgresql_query = PostgreSQL()
postgresql_query.connection()
sql_str = "SELECT vpn_type FROM vpn_access"
data = postgresql_query.query_fetch_all(sql_str)
postgresql_query.close_connection()
VPN_TYPES = [ d['vpn_type'] for d in data ]
vpn_str_option = ''.join([ "{}.) {}\n".format(index+1, val) for index, val\
in enumerate(VPN_TYPES)])
print('\nVPN Types:\n{}'.format(vpn_str_option[:-1]))
selected_vpn_type = int((input('select vpn-type:> ') or 0))-1
######################## VPN-ACCESS SELECTION
print('\nOperating System: \n1.) {} \n2.) {} '.format(OS[0], OS[1]))
selected_os = int(input('select os:> ') or 0)-1
print()
print('------------------')
print('selected_account_id:', selected_accound_id)
print('name:', name)
print('selected_vpn_type:', VPN_TYPES[selected_vpn_type])
print('selected_os:', OS[selected_os])
print('------------------')
input('enter to continue:>')
if selected_accound_id>=0 and name and selected_vpn_type>=0 and selected_os>=0:
try:
vpn_access = Vpn_Access_Main(1111, selected_accound_id,
name, vpn_type=VPN_TYPES[selected_vpn_type], account_os=OS[selected_os])
vpn_access.generate()
except IndexError:
print("INDEX-ERROR.")
except:
print(sys.exc_info())
else:
print("\nInvalid selection.")
class Report(Common):
"""Class for Report"""
# INITIALIZE
def __init__(self):
"""The Constructor for Report class"""
self.postgresql_query = PostgreSQL()
self._couch_db = CouchDatabase()
self.couch_query = Queries()
super(Report, self).__init__()
def report(self):
"""
This API is for Getting Report Temp
---
tags:
- Vessel
produces:
- application/json
parameters:
- name: token
in: header
description: Token
required: true
type: string
- name: userid
in: header
description: User ID
required: true
type: string
- name: vessel_id
in: query
description: Vessel ID
required: true
type: string
responses:
500:
description: Error
200:
description: Report Temp
"""
data = {}
# GET DATA
token = request.headers.get('token')
userid = request.headers.get('userid')
vessel_id = request.args.get('vessel_id')
# CHECK TOKEN
token_validation = self.validate_token(token, userid)
if not token_validation:
data["alert"] = "Invalid Token"
data['status'] = 'Failed'
# RETURN ALERT
return self.return_data(data)
# OPEN CONNECTION
self.postgresql_query.connection()
# GET REPORT TEMP
report_data = self.get_report(vessel_id)
# CLOSE CONNECTION
self.postgresql_query.close_connection()
datas = {}
datas['report_data'] = report_data
datas['status'] = 'ok'
return self.return_data(datas)
def get_report(self, vessel_id):
"""Returns the Report"""
sql_str = "SELECT * FROM report_temp WHERE vessel_id = '" + vessel_id + "'"
res = self.postgresql_query.query_fetch_one(sql_str)
if res:
return res['report_data']
default_report_data = "General Info: \n"
default_report_data += """VESSEL: *["PARAMETERS"]["INFO"]["VESSELNAME"]*\n"""
default_report_data += "TIME: *TIMESTAMP*\n"
return default_report_data
class VPNUpdate(Common):
"""Class for VPNUpdate"""
# INITIALIZE
def __init__(self):
"""The Constructor for VPNUpdate class"""
self._couch_db = CouchDatabase()
self.couch_query = Queries()
self.postgres = PostgreSQL()
# INIT CONFIG
self.config = ConfigParser()
# CONFIG FILE
self.config.read("config/config.cfg")
self.vpn_db_build = config_section_parser(self.config,
"VPNDB")['build']
super(VPNUpdate, self).__init__()
if self.vpn_db_build.upper() == 'TRUE':
self.my_ip = config_section_parser(self.config, "IPS")['my']
self.my_protocol = config_section_parser(self.config,
"IPS")['my_protocol']
self.user_vpn = config_section_parser(self.config,
"IPS")['user_vpn']
self.user_protocol = config_section_parser(self.config,
"IPS")['user_protocol']
self.vessel_vpn = config_section_parser(self.config,
"IPS")['vessel_vpn']
self.vessel_protocol = config_section_parser(
self.config, "IPS")['vessel_protocol']
self.vpn_token = '269c2c3706886d94aeefd6e7f7130ab08346590533d4c5b24ccaea9baa5211ec'
def vpn_update(self):
"""
This API is for Getting Data for VPN
---
tags:
- VPN
produces:
- application/json
parameters:
- name: token
in: header
description: Token
required: true
type: string
- name: jobid
in: header
description: Job ID
required: true
type: string
- name: query
in: body
description: Updating VNP
required: true
schema:
id: Updating VNP
properties:
status:
type: string
message:
type: string
directory:
type: string
action:
type: string
ip:
type: string
vpn_type:
type: string
responses:
500:
description: Error
200:
description: Role
"""
data = {}
# GET JSON REQUEST
query_json = request.get_json(force=True)
# GET DATA
job_id = request.headers.get('jobid')
token = request.headers.get('token')
# print("="*50, " vpn_update ", "="*50)
# print("job_id: ", job_id)
# print("token: ", token)
# print("query_json: ", query_json)
# print("="*50, " vpn_update ", "="*50)
ip_address = query_json['ip']
vpn_type = query_json['vpn_type']
action = query_json['action']
datas = self.update_job(job_id, token, query_json)
# CHECK TOKEN
if not datas:
data["alert"] = "Invalid Data!"
data['status'] = 'Failed'
# RETURN ALERT
return self.return_data(data)
# OPEN CONNECTION
self.postgres.connection()
if query_json['status'] == 'ok' and action == 'CREATE' and vpn_type in [
'CLIENT', 'RHADMIN', 'VCLIENT', 'VRH'
]:
if ip_address:
sql_str = "SELECT a.id FROM account a INNER JOIN job j"
sql_str += " ON a.id=j.account_id WHERE j.job_id={0}".format(
job_id)
account = self.postgres.query_fetch_one(sql_str)
account_id = account['id']
account_vpn_data = {}
account_vpn_data['account_id'] = account_id
account_vpn_data['vpn_ip'] = ip_address
account_vpn_data['status'] = True
account_vpn_data['vpn_type'] = vpn_type
account_vpn_data['job_id'] = job_id
account_vpn_data['created_on'] = time.time()
self.postgres.insert('account_vpn', account_vpn_data)
if query_json['status'] == 'ok' and action == 'CREATE' and vpn_type in [
'VCLIENT', 'VRH'
]:
if ip_address:
self.postgres.connection()
sql_str = "SELECT * FROM account_vessel WHERE account_id={0}".format(
account_id)
vessels = self.postgres.query_fetch_all(sql_str)
self.postgres.close_connection()
add_ip = False
remove_ip = False
for vessel in vessels:
add_ip = bool(vessel['allow_access'])
if add_ip is True:
remove_ip = True
# if vessel['allow_access']:
# add_ip = True
# else:
# remove_ip = True
callback_url = self.my_protocol + "://" + self.my_ip + "/vpn/update"
data_url = self.my_protocol + "://" + self.my_ip + "/vpn/data"
# OPEN CONNECTION
self.postgres.connection()
sql_str = "SELECT * FROM account_role where account_id='{0}' ".format(
account_id)
sql_str += "AND role_id in (SELECT role_id FROM role "
sql_str += "WHERE role_name='super admin')"
super_admin = self.postgres.query_fetch_one(sql_str)
vpn_type = 'VCLIENT'
if super_admin:
vpn_type = 'VRH'
# CLOSE CONNECTION
self.postgres.close_connection()
sa_role = self.get_sa_role(account_id)
if add_ip and not sa_role:
# INSERT JOB
j_id = self.insert_job(callback_url, data_url,
self.vpn_token, account_id,
self.vessel_vpn, 'ADD', vpn_type)
# INIT PARAMS FOR CREATE VPN
vpn_params = {}
vpn_params['callback_url'] = callback_url
vpn_params['data_url'] = data_url
vpn_params['job_id'] = j_id
self.add_vpn_ip(vpn_params, self.vpn_token, True)
if remove_ip and not sa_role:
# INSERT JOB
j_id = self.insert_job(callback_url, data_url,
self.vpn_token, account_id,
self.vessel_vpn, 'REMOVE', vpn_type)
# INIT PARAMS FOR CREATE VPN
vpn_params = {}
vpn_params['callback_url'] = callback_url
vpn_params['data_url'] = data_url
vpn_params['job_id'] = j_id
self.add_vpn_ip(vpn_params, self.vpn_token, True)
elif query_json['status'] == 'ok' and action in ['ADD', 'REMOVE']:
self.postgres.connection()
sql_str = "SELECT a.id, a.status FROM account a INNER JOIN job j"
sql_str += " ON a.id=j.account_id WHERE j.job_id={0}".format(
job_id)
account = self.postgres.query_fetch_one(sql_str)
self.postgres.close_connection()
account_id = account['id']
account_status = account['status']
conditions = []
conditions.append({
"col": "vessel_vpn_ip",
"con": "=",
"val": ip_address
})
conditions.append({
"col": "account_id",
"con": "=",
"val": account_id
})
account_vessel_data = {}
account_vessel_data['vessel_vpn_state'] = 'ok'
if account_status:
self.postgres.update('account_vessel', account_vessel_data,
conditions)
else:
self.postgres.update('account_offline_vessel',
account_vessel_data, conditions)
elif query_json['status'] == 'DONE' and action in ['DELETE']:
sql_str = "SELECT a.id FROM account a INNER JOIN job j"
sql_str += " ON a.id=j.account_id WHERE j.job_id={0}".format(
job_id)
account = self.postgres.query_fetch_one(sql_str)
account_id = account['id']
conditions = []
conditions.append({"col": "vpn_type", "con": "=", "val": vpn_type})
conditions.append({
"col": "account_id",
"con": "=",
"val": account_id
})
account_vpn_data = {}
account_vpn_data['status'] = False
self.postgres.update('account_vpn', account_vpn_data, conditions)
self.postgres.connection()
self.delete_user(job_id, token)
if query_json['status'] == 'ok' and action == 'CREATE':
self.send_email_vpn(job_id, token)
# CLOSE CONNECTION
self.postgres.close_connection()
data['message'] = "Job successfully updated!"
data['status'] = "ok"
return self.return_data(data)
def update_job(self, job_id, token, data):
"""Update Job"""
conditions = []
data = self.remove_key(data, "ip")
data['message'] = self.remove_special_char(data['message'])
if data['status'] == 'failed':
data['status'] = 'pending'
conditions.append({"col": "token", "con": "=", "val": str(token)})
conditions.append({"col": "job_id", "con": "=", "val": job_id})
if self.postgres.update('job', data, conditions):
return 1
return 0
def send_email_vpn(self, job_id, token):
"""Send Email VPN"""
sql_str = "SELECT * FROM job WHERE job_id={0} AND token='{1}'".format(
job_id, token)
job = self.postgres.query_fetch_one(sql_str)
if job:
sql_str = "SELECT email FROM account WHERE id={0}".format(
job['account_id'])
email = self.postgres.query_fetch_one(sql_str)
if email:
email = email['email']
filename = job['directory'].split("/")[-1]
url = 'http://' + job['vnp_server_ip'] + '/zip_vpn/' + filename
vpn_dir = '/home/admin/all_vpn/' + filename
urllib.request.urlretrieve(url, vpn_dir)
# emailer = Email()
# e_temp = Message()
# msg = "Attached is your new VPN for new VPN configuration."
# message = e_temp.message_temp(msg)
# subject = "Web VPN"
# if job['vpn_type'] in ['VCLIENT', 'VRH']:
# subject = "Vessel VPN"
# instruction = './Instructions_OPENVPN.pdf'
# emailer.send_email(email,
# message,
# subject,
# [vpn_dir, instruction])
# SEND AUTO INSTALL VPN
self.send_auto_install(job, email)
return 1
return 0
def add_vpn_ip(self, data, vpn_token, flag=False):
"""Add VPN IP"""
api_endpoint = self.user_protocol + "://" + self.user_vpn + "/ovpn"
if flag:
api_endpoint = self.vessel_protocol + "://" + self.vessel_vpn + "/ovpn"
headers = {'content-type': 'application/json', 'token': vpn_token}
req = requests.post(api_endpoint,
data=json.dumps(data),
headers=headers)
res = req.json()
return res
def insert_job(self, callback_url, data_url, vpn_token, account_id,
user_vpn, action, vpn_type):
"""Insert Job"""
update_on = time.time()
# INIT NEW JOB
temp = {}
temp['callback_url'] = callback_url
temp['vnp_server_ip'] = user_vpn
temp['data_url'] = data_url
temp['token'] = vpn_token
temp['status'] = 'pending'
temp['account_id'] = account_id
temp['vpn_type'] = vpn_type
temp['action'] = action
temp['update_on'] = update_on
temp['created_on'] = update_on
# INSERT NEW JOB
job_id = self.postgres.insert('job', temp, 'job_id')
return job_id
def get_sa_role(self, account_id):
"""Return Account Role"""
# OPEN CONNECTION
self.postgres.connection()
sql_str = "SELECT * FROM account_role where account_id='{0}' ".format(
account_id)
sql_str += "AND role_id in (SELECT role_id FROM role "
sql_str += "WHERE role_name='super admin')"
account = self.postgres.query_fetch_one(sql_str)
# CLOSE CONNECTION
self.postgres.close_connection()
return account
def send_auto_install(self, job, email):
""" Send email Auto install """
account_id = job['account_id']
sa_role = self.get_sa_role(account_id)
# OPEN CONNECTION
self.postgres.connection()
sql_str = "SELECT * FROM account_vessel WHERE account_id={0} ".format(
account_id)
sql_str += "AND allow_access=true"
acc_vessel = self.postgres.query_fetch_all(sql_str)
# CLOSE CONNECTION
self.postgres.close_connection()
filenames = []
if acc_vessel or sa_role:
# MUST HAVE TWO VPN
sql_str = "SELECT * FROM job WHERE job_id in ("
sql_str += "SELECT job_id FROM account_vpn WHERE "
sql_str += "account_id={0} and status=true)".format(account_id)
jobs = self.postgres.query_fetch_all(sql_str)
if len(jobs) == 2:
# SEND EMAIL
for j in jobs:
filename = j['directory'].split("/")[-1]
filenames.append('/home/admin/all_vpn/' + filename)
win_url, mac_url = self.installer_setup(account_id, filenames)
self.send_installer_email(win_url, mac_url, email)
else:
# ONE VPN
# SEND EMAIL
filename = job['directory'].split("/")[-1]
filenames.append('/home/admin/all_vpn/' + filename)
win_url, mac_url = self.installer_setup(account_id, filenames)
self.send_installer_email(win_url, mac_url, email)
return 1
def installer_setup(self, account_id, filenames):
""" Installer Setup """
installer_dir = '/home/admin/all_vpn/Installer/VPN_' + str(
account_id) + '_Installer'
# REMOVE DIRECTORY IF EXIST
remove_command = 'rm -rf ' + installer_dir
os.system(remove_command)
# CREATE DIRECTORY
create_dir = 'mkdir ' + installer_dir
os.system(create_dir)
# ------------------------- WINDOWS ------------------------- #
# CREATE WINDOWS INSTALLER DIRECTORY
iwindir = installer_dir + "/RHBox\ VPN\ Installer-WindowsOS"
new_windir = 'mkdir ' + iwindir
os.system(new_windir)
# COPY WINDOWS INSTALL DIRECTORY
original_iwinfiles = "/app/rhboxautoinstall/RHBox\ VPN\ Installer-WindowsOS/"
iwinfiles = "cp -r " + original_iwinfiles + "* " + iwindir + "/"
os.system(iwinfiles)
# COPY WINDOWS VPN CREDENTIALS
for fname in filenames:
cp_command = 'cp ' + fname + ' ' + iwindir + '/' + 'VPN\ Credentials'
os.system(cp_command)
# ZIP WINDOWS INSTALLER
oiwindir = installer_dir + "/RHBox VPN Installer-WindowsOS"
shutil.make_archive(oiwindir, 'zip', oiwindir)
# --------------------------- MAC --------------------------- #
# CREATE MAC INSTALLER DIRECTORY
imacdir = installer_dir + "/RHBox\ VPN\ Installer-MacOS"
new_macdir = 'mkdir ' + imacdir
os.system(new_macdir)
# COPY MAC INSTALL DIRECTORY
original_imacfiles = "/app/rhboxautoinstall/RHBox\ VPN\ Installer-MacOS/"
imacfiles = "cp -r " + original_imacfiles + "* " + imacdir + "/"
os.system(imacfiles)
# COPY MAC VPN CREDENTIALS
for fname in filenames:
cp_command = 'cp ' + fname + ' ' + imacdir + '/' + 'VPNCredentials'
os.system(cp_command)
# ZIP MAC INSTALLER
oimacdir = installer_dir + "/RHBox VPN Installer-MacOS"
shutil.make_archive(oimacdir, 'zip', oimacdir)
win_zip = oiwindir + '.zip'
mac_zip = oimacdir + '.zip'
return self.upload_vpn(win_zip, mac_zip, account_id)
def upload_vpn(self, win_zip, mac_zip, account_id):
""" VPN ACCESS """
# AWS ACCESS
aws_access_key_id = config_section_parser(self.config,
"AWS")['aws_access_key_id']
aws_secret_access_key = config_section_parser(
self.config, "AWS")['aws_secret_access_key']
region_name = config_section_parser(self.config, "AWS")['region_name']
# CONNECT TO S3
s3_resource = boto3.resource(
's3',
aws_access_key_id=aws_access_key_id,
aws_secret_access_key=aws_secret_access_key,
region_name=region_name)
s3_client = boto3.client('s3',
aws_access_key_id=aws_access_key_id,
aws_secret_access_key=aws_secret_access_key,
region_name=region_name)
win_url = self.s3_uploader('WINDOWS', win_zip, s3_resource, s3_client,
account_id)
mac_url = self.s3_uploader('MAC', mac_zip, s3_resource, s3_client,
account_id)
return [win_url, mac_url]
def s3_uploader(self, ops, zipdir, s3_resource, s3_client, account_id):
""" VPN uploader """
# Bucket
bucket = config_section_parser(self.config, "AWS")['bucket']
# Filename
filename = 'VPN/VPN_' + ops + '_Installer_' + str(account_id) + '.zip'
# SAVE TO S3
s3_resource.meta.client.upload_file(zipdir, bucket, filename)
s3_params = {'Key': filename, 'Bucket': bucket}
# GET PRESIGNED URL
pres_url = s3_client.generate_presigned_url('get_object',
Params=s3_params,
ExpiresIn=86400,
HttpMethod='GET')
return pres_url
def send_installer_email(self, win_url, mac_url, email):
""" SEND INSTALLER EMAIL """
emailer = Email()
e_temp = VPNInstallTemp()
# MESSAGE
msg = "Your VPN credentials installer has created or modified. "
msg += "Please select a URL that matches your Operating system."
# NOTES
note = "Note: URL to download the installer will expire in 24 hours. "
note += "If you miss to download and the URL already expired, "
note += "please contact the administrator."
message = e_temp.vpn_mgs_temp(msg, note, win_url, mac_url)
subject = "Radio Holland VPN Installer"
emailer.send_email(email, message, subject)
return 1
def delete_user(self, job_id, token):
""" DELETE USER """
sql_str = "SELECT * FROM job WHERE job_id={0} AND token='{1}'".format(
job_id, token)
job = self.postgres.query_fetch_one(sql_str)
if job:
account_id = job['account_id']
sa_role = self.get_sa_role(account_id)
# OPEN CONNECTION
self.postgres.connection()
sql_str = "SELECT * FROM account_vessel WHERE account_id={0} ".format(
account_id)
sql_str += "AND allow_access=true"
acc_vessel = self.postgres.query_fetch_all(sql_str)
# CLOSE CONNECTION
self.postgres.close_connection()
conditions = []
conditions.append({"col": "id", "con": "=", "val": account_id})
sql_str = "SELECT is_active FROM account WHERE id={0}".format(
account_id)
active = self.postgres.query_fetch_one(sql_str)
if acc_vessel or sa_role:
self.postgres.connection()
# MUST HAVE TWO VPN
sql_str = "SELECT * FROM job WHERE job_id in ("
sql_str += "SELECT job_id FROM account_vpn WHERE "
sql_str += "account_id={0} and status=true)".format(account_id)
jobs = self.postgres.query_fetch_all(sql_str)
if len(jobs) == 2 and not active['is_active']:
# DELETE USER
self.postgres.delete('account', conditions)
else:
if not active['is_active']:
# DELETE USER
self.postgres.delete('account', conditions)
return 1
class AuthenticationKey(Common):
"""Class for AuthenticationKey"""
# INITIALIZE
def __init__(self):
"""The Constructor for AunthenticationKey class"""
# INIT CONFIG
self.config = ConfigParser()
# CONFIG FILE
self.config.read("config/config.cfg")
self.postgres = PostgreSQL()
super(AuthenticationKey, self).__init__()
def authentication_key(self):
"""
This API is for To send authentication key on user email
---
tags:
- User
produces:
- application/json
parameters:
- name: query
in: body
description: User authentication key
required: true
schema:
id: User authentication key
properties:
email:
type: string
responses:
500:
description: Error
200:
description: User authentication key
"""
data = {}
# GET JSON REQUEST
query_json = request.get_json(force=True)
if not self.insert_authentication_key(query_json):
data["alert"] = "Email does not exist!"
data['status'] = 'Failed'
# RETURN ALERT
return self.return_data(data)
data['message'] = "Authentication key has been send in your email!"
data['status'] = "ok"
return self.return_data(data)
def insert_authentication_key(self, query_json):
"""Insert Authentication Key"""
# GET VALUES ROLE AND PERMISSION
email = query_json['email']
reset_token = str(uuid4())[:8]
updates = {}
updates['reset_token_date'] = time.time()
updates['reset_token'] = reset_token
sql_str = "SELECT email FROM account WHERE"
sql_str += " email='{0}'".format(email)
if not self.postgres.query_fetch_one(sql_str):
self.postgres.close_connection()
return 0
# INIT CONDITION
conditions = []
# CONDITION FOR QUERY
conditions.append({"col": "email", "con": "=", "val": email})
# UPDATE RESET TOKEN
if self.postgres.update('account', updates, conditions):
# SEND EMAIL FOR RESET PASSWORD
self.send_authentication_key(email, reset_token)
# RETURN
return 1
# RETURN
return 0
def send_authentication_key(self, email, reset_token):
"""Send Authentication Key"""
email_temp = ResetToken()
emailer = Email()
message = email_temp.reset_token_temp(reset_token)
subject = "Authentication key for password reset"
emailer.send_email(email, message, subject)
return 1
data = {}
data['manually_added'] = 1
data['account_id'] = -1
data['id_vpn_access_requests'] = 1
data['vpn_type'] = d['vpn_type']
data['ip_addr_1'] = ip_addr_1
data['ip_addr_2'] = ip_addr_2
data['is_active'] = 1
created_id = postgresql_query.insert(
'account_vpn_access',
data,
table_id='id_vpn_access_requests')
table_id = created_id
# print('table_id:', table_id)
conditions = []
conditions.append({
"col": "vpn_type",
"con": "=",
"val": d['vpn_type']
})
data = {}
data['current_ip_1'] = ip_addr_1
data['current_ip_2'] = ip_addr_2
data['update_on'] = datetime.fromtimestamp(time.time())
postgresql_query.update('vpn_access', data, conditions)
finally:
postgresql_query.close_connection()
class Vpn_Access:
ccd_path = '/etc/openvpn/ccd'
easy_rsa_path = '/etc/openvpn/easy-rsa/'
easy_rsa_key_path = '/etc/openvpn/easy-rsa/keys/'
zipfiles_main_path = '/home/'
def __init__(self, id_vpn_access_requests, account_id, account_name,
vpn_type, account_os):
self.id_vpn_access_requests = id_vpn_access_requests
self.account_id = account_id
self.account_name = account_name
self.vpn_type = vpn_type
self.account_os = account_os
self.postgresql_query = PostgreSQL()
self.conf_default_root_path = os.getcwd(
) #os.path.join(os.getcwd(), 'WEB_API_VPN_DEFAULT_CONFIGURATION_FILE')
self.account_filename = Vpn_Access.set_account_filename(
vpn_type, account_id, account_name)
self.current_ip_1 = None
self.current_ip_2 = None
self.self_check_status = True
self.self_check()
#DEBUG:
# time.sleep(5)
@property
def vpn_default_conf_path(self):
'''Configuration files and vpn_access.py should be in the same folder.'''
self.postgresql_query.connection()
sql_str = "SELECT config_file_name FROM vpn_access WHERE vpn_type = '{}' ".format(
self.vpn_type)
config_file_name = self.postgresql_query.query_fetch_one(sql_str)
self.postgresql_query.close_connection()
config_file_name = config_file_name['config_file_name']
if config_file_name:
return os.path.join(self.conf_default_root_path, config_file_name)
else:
raise ValueError('[ERROR] Unknown Account Type.')
@staticmethod
def set_account_filename(vpn_type, account_id, account_name):
account_filename = '{}_{:02d}_{}'.format(vpn_type, account_id,
account_name)
print('account_filename:', account_filename)
return account_filename
def self_check(self):
'''CHECKING ALL CAUSES OF POSSIBLE ERRORS.'''
#VERIFY IF THE USER DOESN'T HAVE EXISTING VPN THAT IS ACTIVE
self.postgresql_query.connection()
sql_str = "SELECT COUNT(1) FROM account_vpn_access WHERE account_id={} AND vpn_type='{}'".\
format(self.account_id, self.vpn_type)
count = self.postgresql_query.query_fetch_one(sql_str)
is_exists = count['count']
self.postgresql_query.close_connection()
if is_exists:
status_details = '[ERROR] this account has existing vpn-access for {} VPN.'.format(
self.vpn_type)
print(status_details)
conditions = []
conditions.append({
"col": "id",
"con": "=",
"val": self.id_vpn_access_requests #"CLIENT",
})
data = {}
data['status'] = 'DENIED'
data['request_finish_date'] = datetime.fromtimestamp(time.time())
data['status_details'] = status_details
self.postgresql_query.update('vpn_access_requests', data,
conditions)
self.self_check_status = False
#CHECK ALL EXISTENCE OF ALL DIRS
try:
assert self.vpn_default_conf_path
assert os.path.exists(self.ccd_path)
assert os.path.exists(self.easy_rsa_path)
assert os.path.exists(self.easy_rsa_key_path)
assert os.path.exists(self.zipfiles_main_path)
assert not os.path.exists(
os.path.join(self.ccd_path,
self.account_filename)), 'Existing file in ccd.'
except:
status_details = 'ERROR: In VPN_ACCESS.self_check. | '
status_details += str(sys.exc_info())[:400].replace('\'', '\"')
print(status_details)
conditions = []
conditions.append({
"col": "id",
"con": "=",
"val": self.id_vpn_access_requests #"CLIENT",
})
data = {}
data['status'] = 'DENIED'
data['request_finish_date'] = datetime.fromtimestamp(time.time())
data['status_details'] = status_details
self.postgresql_query.update('vpn_access_requests', data,
conditions)
self.self_check_status = False
def create_static_ip(self):
print("RUNNING - create_static_ip.")
status_failed = 'failed'
status_ok = 'ok'
if not self.self_check_status:
# status_failed = 'failed'
return self.id_vpn_access_requests
self.postgresql_query.connection()
sql_str = "SELECT COUNT(1) FROM vpn_access where vpn_type ='{}'".format(
self.vpn_type)
count = self.postgresql_query.query_fetch_one(sql_str)
is_exists = count['count']
self.postgresql_query.close_connection()
if not is_exists:
print("[ERROR] Account type {} doesn't exists.".format(
self.vpn_type))
# return status_failed
return self.id_vpn_access_requests
filename = os.path.join(self.ccd_path, self.account_filename)
if os.path.exists(filename):
print('[ERROR] File Already Exists: {}'.format(filename))
# return status_failed
return self.id_vpn_access_requests
else:
try:
next_ip_1, next_ip_2 = self.get_ip_addr()
except ValueError:
print(sys.exc_info())
if '[FATAL] Vpn_Access' in str(sys.exc_info()):
return 'failed'
# return status_failed
return self.id_vpn_access_requests
if not self.add_file_to_ccd(self.account_filename, next_ip_1,
next_ip_2):
# return status_failed
return self.id_vpn_access_requests
#RUN PKI-TOOL
vpn_files = self.run_pkitool() or []
if not vpn_files:
# return status_failed
return self.id_vpn_access_requests
#RUN GENERERATE_CONF_FILE
conf_file = self.genererate_conf_file()
if not conf_file:
# return status_failed
return self.id_vpn_access_requests
#ZIP FILES
zip_file_path = os.path.join(self.zipfiles_main_path, "{}.zip".\
format(self.account_filename))
vpn_files = vpn_files + [conf_file] # append doesn't work?
if not self.zip_file(vpn_files, zip_file_path):
print("[ERROR] zip file error.")
# return status_failed
return self.id_vpn_access_requests
#ADD DATA IN ACCOUNT_VPN_ACCESS
conditions = []
data = {}
print('id_vpn_access_requests:', self.id_vpn_access_requests)
data['id_vpn_access_requests'] = self.id_vpn_access_requests
data['ip_addr_1'] = next_ip_1
data['ip_addr_2'] = next_ip_2
data['vpn_type'] = self.vpn_type
data['account_id'] = self.account_id
data['zip_file_path'] = zip_file_path
if not self.postgresql_query.insert('account_vpn_access', data,
'account_id'):
print("[ERROR] Unable to update account_vpn_access table.")
# return status_failed
return self.id_vpn_access_requests
# UPDATE CURRENT IP IN VPN_ACCESS TABLE
conditions = []
conditions.append({
"col": "vpn_type",
"con": "=",
"val": self.vpn_type #"CLIENT",
})
data = {}
data['current_ip_1'] = next_ip_1
data['current_ip_2'] = next_ip_2
data['update_on'] = datetime.fromtimestamp(time.time())
if self.postgresql_query.update('vpn_access', data, conditions):
if not self.delete_conf_file(conf_file):
print("[WARNING]: wasn't able to delete .conf file.")
print('DONE: create_static_ip')
# UPDATE CURRENT IP IN VPN_ACCESS TABLE
conditions = []
conditions.append({
"col": "id",
"con": "=",
"val": self.id_vpn_access_requests #"CLIENT",
})
data = {}
data['status'] = 'FINISHED-VS'
data['request_finish_date'] = datetime.fromtimestamp(
time.time())
#update request table:
self.postgresql_query.update('vpn_access_requests', data,
conditions)
# return status_ok
return self.id_vpn_access_requests
else:
print("[ERROR]: Unable to update vpn_access table.")
# return status_failed
return self.id_vpn_access_requests
def add_file_to_ccd(self, filename, next_ip_1, next_ip_2):
print("RUNNING - add_file_to_ccd.")
os.chdir(self.ccd_path)
content = 'ifconfig-push {} {}'.format(next_ip_1, next_ip_2)
cmd = "echo '{}' > {filename}".format(content, filename=filename)
os.system(cmd)
#VALIDATE FILE
if os.path.exists(filename):
return True
else:
print('ERROR: {} in ccd/ is not created.'.format(filename))
return False
def run_pkitool(self):
print("RUNNING - run_pkitool.")
#CHANGING DIR IS REQUIRED TO RUN THE PROGRAM PROPERLY.
os.chdir(self.easy_rsa_path)
# RUN COMMAND
cmd = '. ./vars && ./pkitool {}'.format(self.account_filename)
os.system(cmd)
#CHANGE DIR
os.chdir(self.easy_rsa_key_path)
filename = os.path.join(self.easy_rsa_key_path, self.account_filename)
extensions = ['key', 'crt']
files_w_ext = ['{}.{}'.format(filename, ext) for ext in extensions]
files_w_ext = files_w_ext + ['ca.crt']
for file in files_w_ext:
if not os.path.exists(file):
print('ERROR: {} in keys/ is not created.'.format(file))
return False
return files_w_ext
def genererate_conf_file(self):
print('RUN: genererate_conf_file')
try:
with open(self.vpn_default_conf_path, 'r') as f:
ext = ACCOUNT_OS[self.account_os]['conf_extension']
file = os.path.join(self.easy_rsa_key_path,
"{}.{}".format(self.account_filename, ext))
with open(file, 'w') as f1:
content = str(
f.read().format(ACCOUNT_NAME=self.account_filename))
f1.write(content)
if os.path.exists(file):
return file
return 0
except:
print(str(sys.exc_info()))
return 0
def delete_conf_file(self, conf_file_path):
print("conf_file_path:", conf_file_path)
os.system('rm {}'.format(conf_file_path))
if not os.path.exists(conf_file_path):
return 1
return 0
def get_ip_addr(self):
print("RUNNING - get_ip_addr.")
self.postgresql_query.connection()
next_ip_1 = None
next_ip_2 = None
# sql_str = "SELECT * FROM vpn_access WHERE vpn_type='CLIENT'"
sql_str = "SELECT * FROM vpn_access WHERE vpn_type='{}'".format(
self.vpn_type)
res = self.postgresql_query.query_fetch_one(sql_str)
assert res, 'VPN TYPE ({}) does not exists is not initialized in vpn_access.'.format(
self.vpn_type)
print('CURRENT IP:', res['current_ip_1'])
print('CURRENT IP:', res['current_ip_2'])
print('\n')
current_ip_1 = res['current_ip_1']
current_ip_2 = res['current_ip_2']
#COMPUTE FOR NEXT-IP
next_ip_1, next_ip_2 = self.compute_for_next_ip(res)
print('NEXT IP:', next_ip_1)
print('NEXT IP:', next_ip_2)
print('\n')
# CLOSE CONNECTION
self.postgresql_query.close_connection()
return next_ip_1, next_ip_2
def compute_for_next_ip(self, res):
'''Computation for next ip for vpn.'''
octet_1 = res.get('octet_1')
octet_2_min = res.get('octet_2_min')
octet_3_min = res.get('octet_3_min')
octet_4_min = res.get('octet_4_min')
octet_2_max = res.get('octet_2_max')
octet_3_max = res.get('octet_3_max')
octet_4_max = res.get('octet_4_max')
ip = res.get('current_ip_1').split('.')
current_octet_2 = int(ip[1])
current_octet_3 = int(ip[2])
current_octet_4 = int(ip[3])
next_octet_2 = current_octet_2
next_octet_3 = current_octet_3
next_octet_4 = current_octet_4
next_ip_1 = None
next_ip_2 = None
valid_numbers = {
'1': 2,
'5': 6,
'9': 10,
'13': 14,
'17': 18,
'21': 22,
'25': 26,
'29': 30,
'33': 34,
'37': 38,
'41': 42,
'45': 46,
'49': 50,
'53': 54,
'57': 58,
'61': 62,
'65': 66,
'69': 70,
'73': 74,
'77': 78,
'81': 82,
'85': 86,
'89': 90,
'93': 94,
'97': 98,
'101': 102,
'105': 106,
'109': 110,
'113': 114,
'117': 118,
'121': 122,
'125': 126,
'129': 130,
'133': 134,
'137': 138,
'141': 142,
'145': 146,
'149': 150,
'153': 154,
'157': 158,
'161': 162,
'165': 166,
'169': 170,
'173': 174,
'177': 178,
'181': 182,
'185': 186,
'189': 190,
'193': 194,
'197': 198,
'201': 202,
'205': 206,
'209': 210,
'213': 214,
'217': 218,
'221': 222,
'225': 226,
'229': 230,
'233': 234,
'237': 238,
'241': 242,
'245': 246,
'249': 250,
'253': 254
}
next_octet_4_1 = None
next_octet_4_2 = None
next_valid_num = str(current_octet_4 + 4)
# GET FIRST IP
if res.get('current_ip_1') == '0.0.0.0':
next_octet_4_2 = valid_numbers.get('1')
next_octet_4_1 = next_octet_4_2 - 1
next_octet_2 = octet_2_min
next_octet_3 = octet_3_min
# GET NEXT-OCTET 4
elif valid_numbers.get(next_valid_num, False):
next_octet_4_2 = valid_numbers[str(next_valid_num)]
next_octet_4_1 = next_octet_4_2 - 1
# IF OCTET 4 EXCEEDS
else:
print('yyyyy')
next_octet_4_1 = octet_4_min
next_octet_4_2 = next_octet_4_1 + 1
if (current_octet_3 + 1) <= octet_3_max:
next_octet_3 = current_octet_3 + 1
# IF OCTET 3 EXCEEDS
else:
next_octet_3 = octet_3_min
# IF OCTET 2 EXCEEDS
if current_octet_2 + 1 <= octet_2_max:
next_octet_2 = current_octet_2 + 1
# REACHED MAXIMUM VPN_ACCESS
else:
raise ValueError("[FATAL] Vpn_Access For Account-Type: {} Has"\
" Reached It's Limit".format(self.vpn_type))
next_ip_1 = "{}.{}.{}.{}".format(octet_1, next_octet_2,\
next_octet_3, next_octet_4_1)
next_ip_2 = "{}.{}.{}.{}".format(octet_1, next_octet_2,\
next_octet_3, next_octet_4_2)
return next_ip_1, next_ip_2
def zip_file(self, file_paths, zip_file_path):
'''file paths should be absolute'''
print('RUN: zip_file')
for file in file_paths:
if not os.path.exists(file):
print("[ERROR] File {} doesn't exist".format(file))
return False
files = ' '.join(file_paths)
cmd = 'zip -rj {} {}'.format(zip_file_path, files)
os.system(cmd)
if os.path.exists(zip_file_path):
return True
return False
class Setup():
"""Class for Setup"""
def __init__(self):
"""The constructor for Setup class"""
self.sha_security = ShaSecurity()
self.postgres = PostgreSQL()
# INIT CONFIG
self.config = ConfigParser()
# CONFIG FILE
self.config.read("config/config.cfg")
def main(self):
"""Main"""
time.sleep(30) # Don't Delete this is for Docker
self.create_database()
self.create_tables()
self.create_default_entries()
def create_database(self):
"""Create Database"""
self.dbname = config_section_parser(self.config, "POSTGRES")['db_name']
self.postgres.connection(True)
self.postgres.create_database(self.dbname)
self.postgres.close_connection()
self.vpn_db_build = config_section_parser(self.config,
"VPNDB")['build']
if self.vpn_db_build.upper() == 'TRUE':
self.my_ip = config_section_parser(self.config, "IPS")['my']
self.user_vpn = config_section_parser(self.config,
"IPS")['user_vpn']
self.vessel_vpn = config_section_parser(self.config,
"IPS")['vessel_vpn']
def create_tables(self):
"""Create Tables"""
# OPEN CONNECTION
self.postgres.connection()
# ACCOUNT TABLE
query_str = "CREATE TABLE account (id serial PRIMARY KEY,"
query_str += " username VARCHAR (50) UNIQUE NOT NULL, password VARCHAR (355) NOT NULL,"
query_str += " email VARCHAR (355) UNIQUE NOT NULL, token VARCHAR (1000) NOT NULL,"
query_str += " first_name VARCHAR (1000) , last_name VARCHAR (1000),"
query_str += " vessel_vpn_state VARCHAR (355),"
query_str += " middle_name VARCHAR (1000), default_value BOOLEAN,"
query_str += " url VARCHAR (1000), reset_token VARCHAR (355), status BOOLEAN NOT NULL,"
query_str += " state BOOLEAN NOT NULL, reset_token_date BIGINT, created_on BIGINT NOT NULL,"
query_str += " update_on BIGINT, last_login BIGINT)"
print("Create table: account")
if self.postgres.exec_query(query_str):
print("Account table successfully created!")
# VESSEL TABLE
query_str = "CREATE TABLE vessel (vessel_id VARCHAR (500) PRIMARY KEY,"
query_str += " number VARCHAR (355),"
query_str += " state VARCHAR (355),"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: vessel")
if self.postgres.exec_query(query_str):
print("Vessel table successfully created!")
# ALTER VESSEL TABLE
# DON'T MOVE ALTER
query_str = "ALTER TABLE vessel ADD CONSTRAINT vessel_pk PRIMARY KEY (vessel_id)"
print("Alter table: vessel")
if self.postgres.exec_query(query_str):
print("Vessel table successfully Alter!")
# DEVICE TABLE
query_str = "CREATE TABLE device (device_id VARCHAR (500),"
query_str += " device VARCHAR (355),"
query_str += " device_type VARCHAR (355),"
query_str += " vessel_id VARCHAR (355) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: device")
if self.postgres.exec_query(query_str):
print("Devidce table successfully created!")
# MODULE TABLE
query_str = "CREATE TABLE module (module_id VARCHAR (500),"
query_str += " module VARCHAR (355),"
query_str += " vessel_id VARCHAR (355) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: module")
if self.postgres.exec_query(query_str):
print("Module table successfully created!")
# OPTION TABLE
query_str = "CREATE TABLE option (option_id VARCHAR (500),"
query_str += " option VARCHAR (355),"
query_str += " vessel_id VARCHAR (355) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: option")
if self.postgres.exec_query(query_str):
print("Option table successfully created!")
# COMPANY TABLE
query_str = "CREATE TABLE company (company_id serial PRIMARY KEY,"
query_str += " company_name VARCHAR (355) UNIQUE NOT NULL,"
query_str += " default_value BOOLEAN,"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: company")
if self.postgres.exec_query(query_str):
print("Company table successfully created!")
# SELECTED LABEL TABLE
query_str = "CREATE TABLE selected_label (selected_label_id serial PRIMARY KEY,"
query_str += " device_type VARCHAR (355), select_type VARCHAR (355), label VARCHAR (355),"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Selected label table: Selected label")
if self.postgres.exec_query(query_str):
print("Selected label table successfully created!")
# SOURCE: https://stackoverflow.com/questions/9789736/how-to-implement-a-many-to-many-relationship-in-postgresql
# ROLE TABLE
query_str = "CREATE TABLE role (role_id serial PRIMARY KEY,"
query_str += " role_name VARCHAR (355) UNIQUE NOT NULL,"
query_str += " default_value BOOLEAN,"
query_str += " role_details VARCHAR (1000),"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: role")
if self.postgres.exec_query(query_str):
print("Role table successfully created!")
# PERMISSION TABLE
query_str = "CREATE TABLE permission (permission_id serial PRIMARY KEY,"
query_str += " permission_name VARCHAR (355) UNIQUE NOT NULL,"
query_str += " permission_details VARCHAR (1000),"
query_str += " default_value BOOLEAN,"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: permission")
if self.postgres.exec_query(query_str):
print("Permission table successfully created!")
# REPORT TEMP TABLE
query_str = "CREATE TABLE report_temp (report_temp_id serial PRIMARY KEY,"
query_str += " vessel_id VARCHAR (355) UNIQUE NOT NULL REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " report_data VARCHAR (5000),"
query_str += " state BOOLEAN,"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: report_temp")
if self.postgres.exec_query(query_str):
print("Report temp table successfully created!")
if self.vpn_db_build.upper() == 'TRUE':
# JOB TABLE
query_str = "CREATE TABLE job (job_id serial PRIMARY KEY,"
query_str += " account_id int REFERENCES account (id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " callback_url VARCHAR (355), data_url VARCHAR (355),"
query_str += " token VARCHAR (355), status VARCHAR (355),"
query_str += " vnp_server_ip VARCHAR (355), vpn_type VARCHAR (355),"
query_str += " account_os VARCHAR (355),"
query_str += " remark VARCHAR (500), message VARCHAR (500),"
query_str += " directory VARCHAR (500), action VARCHAR (355),"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: job")
if self.postgres.exec_query(query_str):
print("Job table successfully created!")
# VESSEL VPN JOB TABLE
query_str = "CREATE TABLE vessel_vpn_job (vessel_vpn_job_id serial PRIMARY KEY,"
query_str += " callback_url VARCHAR (355), token VARCHAR (355), status VARCHAR (355),"
query_str += " vnp_server_ip VARCHAR (355), vpn_type VARCHAR (355),"
query_str += " vessel_name VARCHAR (500), imo VARCHAR (500),"
query_str += " remark VARCHAR (500), message VARCHAR (500),"
query_str += " directory VARCHAR (500), action VARCHAR (355),"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: vessel_vpn_job")
if self.postgres.exec_query(query_str):
print("Vessel VPN Job table successfully created!")
# EMAIL LOG TABLE
query_str = "CREATE TABLE email_log (mail_log_id serial PRIMARY KEY,"
query_str += " email_schedule_id BIGINT, email_vessel_id BIGINT,"
query_str += " date_today BIGINT, message jsonb, data_date VARCHAR (355),"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: email_log")
if self.postgres.exec_query(query_str):
print("Vessel Email table successfully created!")
# EMAIL VESSEL TABLE
query_str = "CREATE TABLE email_vessel (email_vessel_id serial PRIMARY KEY,"
query_str += " vessel_id VARCHAR (355) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " email VARCHAR (355),"
query_str += " mail_enable BOOLEAN, reporting_enable BOOLEAN,"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: email_vessel")
if self.postgres.exec_query(query_str):
print("Vessel Email table successfully created!")
# EMAIL SCHEDULE TABLE
query_str = "CREATE TABLE email_schedule (email_schedule_id serial PRIMARY KEY,"
query_str += " email_vessel_id int REFERENCES email_vessel "
query_str += "(email_vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " schedule VARCHAR (355), utc_time BIGINT,"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: email_schedule")
if self.postgres.exec_query(query_str):
print("MAIL SCHEDULE table successfully created!")
# ROLE PERMISSION TABLE
query_str = "CREATE TABLE role_permission ("
query_str += " role_id int REFERENCES role (role_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " permission_id int REFERENCES permission (permission_id) ON UPDATE CASCADE,"
query_str += " CONSTRAINT role_permission_pkey PRIMARY KEY (role_id, permission_id))"
print("Create table: role_permission")
if self.postgres.exec_query(query_str):
print("Role permission table successfully created!")
# ACCOUNT VESSEL TABLE
query_str = "CREATE TABLE account_vessel ("
query_str += " account_id int REFERENCES account (id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " vessel_vpn_state VARCHAR (355), vessel_vpn_ip VARCHAR (355),"
query_str += " vessel_id VARCHAR (1000) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " allow_access BOOLEAN)"
print("Create table: account_vessel")
if self.postgres.exec_query(query_str):
print("Account vessel table successfully created!")
# ACCOUNT OFF LINE VESSEL TABLE
query_str = "CREATE TABLE account_offline_vessel ("
query_str += " account_id int REFERENCES account (id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " vessel_vpn_state VARCHAR (355), vessel_vpn_ip VARCHAR (355),"
query_str += " vessel_id VARCHAR (1000) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " allow_access BOOLEAN)"
print("Create table: account_offline_vessel")
if self.postgres.exec_query(query_str):
print("Account vessel table successfully created!")
# ACCOUNT VPN TABLE
query_str = "CREATE TABLE account_vpn ("
query_str += " account_id int REFERENCES account (id) ON DELETE CASCADE,"
query_str += " vpn_type VARCHAR (350), vpn_ip VARCHAR (350), status BOOLEAN,"
query_str += " created_on BIGINT NOT NULL)"
print("Create table: account_vpn")
if self.postgres.exec_query(query_str):
print("Account VPN table successfully created!")
# ACCOUNT ROLE TABLE
query_str = "CREATE TABLE account_role ("
query_str += " account_id int REFERENCES account (id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " role_id int REFERENCES role (role_id) ON UPDATE CASCADE,"
query_str += " CONSTRAINT account_role_pkey PRIMARY KEY (account_id, role_id))"
print("Create table: account_role")
if self.postgres.exec_query(query_str):
print("Account role table successfully created!")
# ACCOUNT COMPANY TABLE
query_str = "CREATE TABLE account_company ("
query_str += " account_id int REFERENCES account (id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " company_id int REFERENCES company (company_id) ON UPDATE CASCADE,"
query_str += " CONSTRAINT account_company_pkey PRIMARY KEY (account_id, company_id))"
print("Create table: account_company")
if self.postgres.exec_query(query_str):
print("Account company table successfully created!")
# INI FILES TABLE
query_str = "CREATE TABLE ini_files (ini_files_id serial PRIMARY KEY,"
query_str += " vessel_id VARCHAR (1000) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " dir VARCHAR (355), content jsonb,"
query_str += " lastupdate BIGINT, vessel_lastupdate BIGINT,"
query_str += " update_on BIGINT, created_on BIGINT NOT NULL)"
print("Create table: ini_files")
if self.postgres.exec_query(query_str):
print("INI files table successfully created!")
# VERSION TABLE
query_str = "CREATE TABLE version (version_id serial PRIMARY KEY,"
query_str += " version_name VARCHAR (100) UNIQUE NOT NULL, web VARCHAR (100),"
query_str += " api VARCHAR (100), backend VARCHAR (100), status BOOLEAN NOT NULL,"
query_str += " description VARCHAR (1000), created_on BIGINT NOT NULL)"
print("Create table: version")
if self.postgres.exec_query(query_str):
print("Version table successfully created!")
# LATEST VERSION TABLE
query_str = "CREATE TABLE latest_version (latest_version_id bool PRIMARY KEY DEFAULT TRUE,"
query_str += " version_id serial REFERENCES version (version_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " CONSTRAINT latest_version_uni CHECK (latest_version_id))"
print("Create table: latest_version")
if self.postgres.exec_query(query_str):
print("Latest Version table successfully created!")
# VESSEL VERSION TABLE
query_str = "CREATE TABLE vessel_version"
query_str += " (vessel_id VARCHAR (1000) UNIQUE NOT NULL REFERENCES vessel (vessel_id)"
query_str += " ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " version_id serial REFERENCES version (version_id) ON UPDATE CASCADE ON DELETE CASCADE)"
print("Create table: vessel_version")
if self.postgres.exec_query(query_str):
print("Vessel Version table successfully created!")
# VESSEL VERSION LOG TABLE
query_str = "CREATE TABLE vessel_version_log"
query_str += " (vessel_id VARCHAR (1000) REFERENCES vessel (vessel_id)"
query_str += " ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " version_id serial REFERENCES version (version_id) ON UPDATE CASCADE ON DELETE CASCADE,"
query_str += " created_on BIGINT NOT NULL)"
print("Create table: vessel_version_log")
if self.postgres.exec_query(query_str):
print("Vessel Version Log table successfully created!")
# COMPANY VESSEL REF TABLE
query_str = """ CREATE TABLE public.company_vessels (
company_id int4 NOT NULL,
vessel_id VARCHAR (500) NOT NULL REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT company_vessels_pkey PRIMARY KEY (company_id, vessel_id),
CONSTRAINT company_vessels_company_id_fkey FOREIGN KEY (company_id)
REFERENCES company(company_id) ON UPDATE CASCADE ON DELETE CASCADE
);"""
print("Create table: company_vessels")
if self.postgres.exec_query(query_str):
print("Account Vessels Ref table successfully created!")
#ALARM VALUE TABLE
query_str = """CREATE TABLE alarm_value (alarm_value_id serial PRIMARY KEY,
name VARCHAR (255) NOT NULL,
vessel VARCHAR (255),
device VARCHAR (255),
device_type VARCHAR (255),
module VARCHAR (255),
option VARCHAR(255),
value VARCHAR (255),
created_on BIGINT NOT NULL,
update_on BIGINT
);"""
print("Create table: alarm_value")
if self.postgres.exec_query(query_str):
print("Alarm value table successfully created!")
#ALARM CONDITION OPERATOR TABLE
query_str = """CREATE TABLE alarm_coperator
(alarm_coperator_id serial PRIMARY KEY,
operator VARCHAR (255) UNIQUE NOT NULL,
param_num VARCHAR (255) NOT NULL,
label VARCHAR (255) NOT NULL,
opgroup VARCHAR (255) NOT NULL
);"""
print("Create table: alarm_coperator")
if self.postgres.exec_query(query_str):
print("Alarm Condition Operator table successfully created!")
#ALARM CONDITION TABLE
query_str = """CREATE TABLE alarm_condition (alarm_condition_id serial PRIMARY KEY,
comment VARCHAR (255) NOT NULL,
operator_id INT REFERENCES alarm_coperator (alarm_coperator_id)
ON UPDATE CASCADE ON DELETE CASCADE,
parameters jsonb NOT NULL,
created_on BIGINT NOT NULL,
update_on BIGINT
);"""
print("Create table: alarm_condition")
if self.postgres.exec_query(query_str):
print("Alarm Condition table successfully created!")
#ALARM TRIGGER TABLE
query_str = """CREATE TABLE alarm_type (alarm_type_id serial PRIMARY KEY,
alarm_type VARCHAR(25) UNIQUE NOT NULL,
alarm_value INT
);"""
print("Create table: alarm_type")
if self.postgres.exec_query(query_str):
print("Alarm Type table successfully created!")
#ALARM TRIGGER TABLE
query_str = """CREATE TABLE alarm_trigger (alarm_trigger_id serial PRIMARY KEY,
alarm_type_id int REFERENCES alarm_type (alarm_type_id)
ON UPDATE CASCADE ON DELETE CASCADE,
alarm_condition_id int REFERENCES alarm_condition (alarm_condition_id)
ON UPDATE CASCADE ON DELETE CASCADE,
alarm_enabled BOOLEAN DEFAULT 'f' NOT NULL,
label VARCHAR(255) NOT NULL,
description VARCHAR(255),
is_acknowledged BOOLEAN DEFAULT 'f' NOT NULL,
created_on BIGINT NOT NULL,
update_on BIGINT
);"""
print("Create table: alarm_trigger")
if self.postgres.exec_query(query_str):
print("Alarm Trigger table successfully created!")
#ALARM DATA TABLE
query_str = """CREATE TABLE alarm_data (alarm_data_id serial PRIMARY KEY,
alarm_trigger_id int REFERENCES alarm_trigger (alarm_trigger_id)
ON UPDATE CASCADE ON DELETE CASCADE,
average jsonb, device VARCHAR(555),
module VARCHAR(555), option VARCHAR(555),
vessel_id VARCHAR(555) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,
vessel_name VARCHAR(555),
alarm_description VARCHAR(555), err_message VARCHAR(555),
device_id VARCHAR(555), message VARCHAR(555),
alarm_type VARCHAR(555),
vessel_number VARCHAR(555), alarm_type_id int
REFERENCES alarm_type (alarm_type_id)
ON UPDATE CASCADE ON DELETE CASCADE,
epoch_date BIGINT NOT NULL,
created_on BIGINT NOT NULL,
update_on BIGINT
);"""
print("Create table: alarm_data")
if self.postgres.exec_query(query_str):
print("Alarm Data table successfully created!")
#ALARM STATE TABLE
query_str = """CREATE TABLE alarm_state (alarm_state_id serial PRIMARY KEY,
alarm_trigger_id int REFERENCES alarm_trigger (alarm_trigger_id)
ON UPDATE CASCADE ON DELETE CASCADE,
category VARCHAR(50),
results jsonb, device VARCHAR(555),
module VARCHAR(555), option VARCHAR(555),
vessel_id VARCHAR(555) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,
vessel_name VARCHAR(555),
alarm_description VARCHAR(555), err_message VARCHAR(555),
device_id VARCHAR(555), message VARCHAR(555),
alarm_type VARCHAR(555),
vessel_number VARCHAR(555), alarm_type_id int
REFERENCES alarm_type (alarm_type_id)
ON UPDATE CASCADE ON DELETE CASCADE,
epoch_date BIGINT NOT NULL,
created_on BIGINT NOT NULL,
update_on BIGINT
);"""
print("Create table: alarm_state")
if self.postgres.exec_query(query_str):
print("Alarm Data table successfully created!")
# VESSEL IMAGE TABLE
query_str = """CREATE TABLE vessel_image (vessel_image_id serial PRIMARY KEY,
vessel_id VARCHAR(555) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,
vessel_imo VARCHAR(555),
image_name VARCHAR(555),
status VARCHAR(10) NOT NULL,
created_on BIGINT NOT NULL,
update_on BIGINT
);"""
print("Create table: vessel_image")
if self.postgres.exec_query(query_str):
print("Vessel image table successfully created!")
# VESSEL FILE TABLE
query_str = """CREATE TABLE vessel_file (vessel_file_id serial PRIMARY KEY,
vessel_id VARCHAR(555) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,
vessel_imo VARCHAR(555),
file_name VARCHAR(555),
status VARCHAR(10) NOT NULL,
created_on BIGINT NOT NULL,
update_on BIGINT
);"""
print("Create table: vessel_file")
if self.postgres.exec_query(query_str):
print("Vessel file table successfully created!")
# BLOCKAGE DATA TABLE
query_str = """CREATE TABLE blockage_data (blockage_data_id serial PRIMARY KEY,
vessel_id VARCHAR(555) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,
device_id VARCHAR(555),
antenna_status jsonb NOT NULL,
coordinates jsonb NOT NULL,
blockzones jsonb NOT NULL,
epoch_date BIGINT NOT NULL,
created_on BIGINT NOT NULL,
update_on BIGINT
);"""
print("Create table: blockage_data")
if self.postgres.exec_query(query_str):
print("Blockage Data table successfully created!")
# SUBCATEGORY TABLE
query_str = """CREATE TABLE subcategory (subcategory_id serial PRIMARY KEY,
subcategory_name VARCHAR(255) UNIQUE NOT NULL,
created_on BIGINT NOT NULL,
update_on BIGINT
);"""
print("Create table: subcategory")
if self.postgres.exec_query(query_str):
print("Sub Category table successfully created!")
# SUBCATEGORY OPTION TABLE
query_str = """CREATE TABLE subcategory_options (subcategory_id INT REFERENCES subcategory (subcategory_id) ON UPDATE CASCADE ON DELETE CASCADE,
option VARCHAR(255) UNIQUE NOT NULL
);"""
print("Create table: subcategory_options")
if self.postgres.exec_query(query_str):
print("Sub Category Options table successfully created!")
# DEVICE IMAGE TABLE
query_str = """CREATE TABLE device_image (device_image_id serial PRIMARY KEY,
vessel_id VARCHAR(555) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,
device_id VARCHAR(255),
vessel_imo VARCHAR(555),
image_name VARCHAR(555),
status VARCHAR(10) NOT NULL,
created_on BIGINT NOT NULL,
update_on BIGINT
);"""
print("Create table: device_image")
if self.postgres.exec_query(query_str):
print("Device image table successfully created!")
#ALARM EMAIL TABLE
query_str = """CREATE TABLE alarm_email (alarm_email_id serial PRIMARY KEY,
alarm_trigger_id int REFERENCES alarm_trigger (alarm_trigger_id)
ON UPDATE CASCADE ON DELETE CASCADE,
vessel_id VARCHAR(555) REFERENCES vessel (vessel_id) ON UPDATE CASCADE ON DELETE CASCADE,
device_id VARCHAR(555), alarm_status VARCHAR(100), message jsonb,
epoch_date BIGINT NOT NULL,
created_on BIGINT NOT NULL,
update_on BIGINT
);"""
print("Create table: alarm_email")
if self.postgres.exec_query(query_str):
print("Alarm Email table successfully created!")
# ALTER TABLE ACCOUNT VPN
query_str = "ALTER TABLE account_vpn ADD COLUMN job_id BIGINT"
print("ALTER TABLE account vpn")
if self.postgres.exec_query(query_str):
print("ALTER TABLE account vpn successfully!")
# ALTER TABLE ALARM VALUE
query_str = "ALTER TABLE alarm_value ADD CONSTRAINT unique_name UNIQUE(name)"
print("ALTER TABLE alarm_value")
if self.postgres.exec_query(query_str):
print("ALTER TABLE alarm_value successfully!")
else:
print("Failed to ALTER ALARM VALUE")
# ALTER TABLE ALARM CONDITION
query_str = "ALTER TABLE alarm_condition ADD CONSTRAINT unique_comment UNIQUE(comment)"
print("ALTER TABLE alarm_condition")
if self.postgres.exec_query(query_str):
print("ALTER TABLE alarm_condition successfully!")
else:
print("Failed to ALTER ALARM CONDITION")
# ALTER TABLE ALARM TRIGGER
query_str = "ALTER TABLE alarm_trigger ADD CONSTRAINT unique_label UNIQUE(label)"
print("ALTER TABLE alarm_trigger")
if self.postgres.exec_query(query_str):
print("ALTER TABLE alarm_trigger successfully!")
else:
print("Failed to ALTER ALARM TRIGGER")
# ALTER TABLE ACCOUNT
query_str = "ALTER TABLE account ADD COLUMN is_active BOOLEAN"
print("ALTER TABLE account")
if self.postgres.exec_query(query_str):
print("ALTER TABLE account successfully!")
else:
print("Failed to ALTER account")
# ALTER TABLE VESSEL
query_str = "ALTER TABLE vessel ADD COLUMN vessel_name VARCHAR(555)"
print("ALTER TABLE vessel")
if self.postgres.exec_query(query_str):
print("ALTER TABLE vessel successfully!")
else:
print("Failed to ALTER vessel")
# ALTER TABLE ALARM TRIGGER
query_str = "ALTER TABLE alarm_trigger"
query_str += " ADD COLUMN alarm_email BOOLEAN DEFAULT 'f' NOT NULL,"
query_str += " ADD COLUMN email VARCHAR (255)"
print("ALTER TABLE alarm_trigger")
if self.postgres.exec_query(query_str):
print("ALTER TABLE alarm_trigger successfully!")
else:
print("Failed to ALTER alarm_trigger")
# UPDATE TABLE ACCOUNT
query_str = "UPDATE account SET is_active=true WHERE is_active IS NULL"
print("UPDATE TABLE account")
if self.postgres.exec_query(query_str):
print("UPDATE TABLE account successfully!")
else:
print("Failed to UPDATE account table")
# CLOSE CONNECTION
self.postgres.close_connection()
def create_default_entries(self):
"""Create Default Entries"""
vpn_db_build = config_section_parser(self.config, "VPNDB")['build']
# PERMISSION
data = {}
data['permission_name'] = config_section_parser(
self.config, "PERMISSION")['permission_name']
data['permission_details'] = config_section_parser(
self.config, "PERMISSION")['permission_details']
data['default_value'] = True
data['created_on'] = time.time()
print("Create default permission: ", data['permission_name'])
permission_id = self.postgres.insert('permission', data,
'permission_id')
count = 9
if vpn_db_build.upper() == 'TRUE':
count = 10
for dta in range(1, count):
data1 = {}
data1['permission_name'] = config_section_parser(
self.config, "PERMISSION")['permission_name' + str(dta)]
data1['permission_details'] = config_section_parser(
self.config, "PERMISSION")['permission_details' + str(dta)]
data1['default_value'] = True
data1['created_on'] = time.time()
print("Create default permission: ", data1['permission_name'])
self.postgres.insert('permission', data1, 'permission_id')
if permission_id:
print("Default Permission successfully created!")
else:
self.postgres.connection()
sql_str = "SELECT * FROM permission WHERE permission_name='" + data[
'permission_name'] + "'"
res = self.postgres.query_fetch_one(sql_str)
permission_id = res['permission_id']
self.postgres.close_connection()
# ROLE
data = {}
data['role_name'] = config_section_parser(self.config,
"ROLE")['role_name']
data['role_details'] = config_section_parser(self.config,
"ROLE")['role_details']
data['default_value'] = True
data['created_on'] = time.time()
# ROLE1
data1 = {}
data1['role_name'] = config_section_parser(self.config,
"ROLE")['role_name1']
data1['role_details'] = config_section_parser(self.config,
"ROLE")['role_details1']
data1['default_value'] = True
data1['created_on'] = time.time()
# ROLE2
data2 = {}
data2['role_name'] = config_section_parser(self.config,
"ROLE")['role_name2']
data2['role_details'] = config_section_parser(self.config,
"ROLE")['role_details2']
data2['default_value'] = True
data2['created_on'] = time.time()
if vpn_db_build.upper() == 'TRUE':
# ROLE3
data3 = {}
data3['role_name'] = config_section_parser(self.config,
"ROLE")['role_name3']
data3['role_details'] = config_section_parser(
self.config, "ROLE")['role_details3']
data3['default_value'] = True
data3['created_on'] = time.time()
print("Create default role: ", data['role_name'])
print("Create default role: ", data1['role_name'])
print("Create default role: ", data2['role_name'])
role_id = self.postgres.insert('role', data, 'role_id')
self.postgres.insert('role', data1, 'role_id')
self.postgres.insert('role', data2, 'role_id')
if vpn_db_build.upper() == 'TRUE':
print("Create default role: ", data3['role_name'])
self.postgres.insert('role', data3, 'role_id')
sa_role_id = self.postgres.insert('role', data3, 'role_id')
if role_id:
print("Default Role successfully created!")
else:
self.postgres.connection()
sql_str = "SELECT * FROM role WHERE role_name='" + data[
'role_name'] + "'"
res = self.postgres.query_fetch_one(sql_str)
role_id = res['role_id']
self.postgres.close_connection()
if vpn_db_build.upper() == 'TRUE':
if sa_role_id:
print("Default Role successfully created!")
else:
self.postgres.connection()
sql_str = "SELECT * FROM role WHERE role_name='" + data3[
'role_name'] + "'"
res = self.postgres.query_fetch_one(sql_str)
sa_role_id = res['role_id']
self.postgres.close_connection()
# ROLE PERMISSION
temp = {}
temp['role_id'] = role_id
temp['permission_id'] = permission_id
self.postgres.insert('role_permission', temp)
# SUPER ADMIN
# ROLE PERMISSION
self.postgres.connection()
sql_str = "SELECT permission_id FROM permission WHERE permission_name ='all and with admin vessel VPN.'"
permission = self.postgres.query_fetch_one(sql_str)
self.postgres.close_connection()
if vpn_db_build.upper() == 'TRUE':
temp = {}
temp['role_id'] = sa_role_id
temp['permission_id'] = permission['permission_id']
self.postgres.insert('role_permission', temp)
# ACCOUNT
data = {}
data['username'] = config_section_parser(self.config,
"ADMIN")['username']
data['password'] = config_section_parser(self.config,
"ADMIN")['password']
data['email'] = config_section_parser(self.config, "ADMIN")['email']
data['status'] = bool(
config_section_parser(self.config, "ADMIN")['status'])
data['state'] = bool(
config_section_parser(self.config, "ADMIN")['state'])
data['url'] = "default"
data['token'] = self.sha_security.generate_token()
data['default_value'] = True
data['created_on'] = time.time()
data['update_on'] = time.time()
print("Create default user: "******"Default user successfully created!")
else:
self.postgres.connection()
sql_str = "SELECT id FROM account WHERE username='******'username'] + "'"
res = self.postgres.query_fetch_one(sql_str)
account_id = res['id']
self.postgres.close_connection()
# ACCOUNT ROLE
temp = {}
temp['account_id'] = account_id
temp['role_id'] = role_id
self.postgres.insert('account_role', temp)
# COMPANY
data = {}
data['company_name'] = config_section_parser(self.config,
"COMPANY")['company_name']
data['default_value'] = True
data['created_on'] = time.time()
print("Create default company: ", data['company_name'])
company_id = self.postgres.insert('company', data, 'company_id')
if company_id:
print("Default Role successfully created!")
else:
self.postgres.connection()
sql_str = "SELECT * FROM company WHERE company_name='" + data[
'company_name'] + "'"
res = self.postgres.query_fetch_one(sql_str)
company_id = res['company_id']
self.postgres.close_connection()
# ACCOUNT COMPANY
temp = {}
temp['account_id'] = account_id
temp['company_id'] = role_id
self.postgres.insert('account_company', temp)
#ALARM TYPE
temp = {}
alarm_types = ({
"alarm_type": "Critical",
"alarm_value": 10
}, {
"alarm_type": "Warning",
"alarm_value": 20
}, {
"alarm_type": "Alert",
"alarm_value": 30
}, {
"alarm_type": "Info",
"alarm_value": 40
}, {
"alarm_type": "Debug",
"alarm_value": 50
})
for data in alarm_types:
alarm_type_id = self.postgres.insert('alarm_type', data)
if alarm_type_id:
print("Alarm types successfully added!")
else:
print("Failed to add Alarm Type")
temp = {}
alarm_operators = ({
"operator": "TRUE",
"param_num": 0,
"label": "Always True",
"opgroup": "Boolean"
}, {
"operator": "FALSE",
"param_num": 0,
"label": "Always False",
"opgroup": "Boolean"
}, {
"operator": "!",
"param_num": 1,
"label": "Invert Boolean-result",
"opgroup": "Boolean"
}, {
"operator": "=",
"param_num": 2,
"label": "True if equal",
"opgroup": "Boolean"
}, {
"operator": "!=",
"param_num": 2,
"label": "True if Param1 not equal to Param2",
"opgroup": "Boolean"
}, {
"operator": "LIKE",
"param_num": 2,
"label": "True if Param1 LIKE Param2 case insensitive",
"opgroup": "String"
}, {
"operator": "!LIKE",
"param_num": 2,
"label": "True if Param1 not LIKE Param2 case insensitive",
"opgroup": "String"
}, {
"operator": ">",
"param_num": 2,
"label": "True if Param1 > Param2",
"opgroup": "Boolean"
}, {
"operator": "<",
"param_num": 2,
"label": "True if Param1 < Param2",
"opgroup": "Boolean"
}, {
"operator": "AND",
"param_num": 2,
"label": "True if Param1 AND Param2 are true",
"opgroup": "Boolean"
}, {
"operator": "OR",
"param_num": 2,
"label": "True if Param1 OR Param2 is true",
"opgroup": "Boolean"
}, {
"operator": "+",
"param_num": 2,
"label": "Returns the sum of Param1 and Param2",
"opgroup": "Double"
}, {
"operator": "-",
"param_num": 2,
"label": "Returns the difference of Param1 and Param2",
"opgroup": "Double"
}, {
"operator": "*",
"param_num": 2,
"label": "Returns the Param1 times Param2",
"opgroup": "Double"
}, {
"operator": "/",
"param_num": 2,
"label": "Returns the Param1 divided by Param2",
"opgroup": "Double"
}, {
"operator": "BETWEEN",
"param_num": 3,
"label": "True if Param1 > Param2 and Param1 < Param3",
"opgroup": "Boolean"
}, {
"operator": "BETWEENEQ",
"param_num": 3,
"label": "True if Param1 >= Param2 and Param1 =< Param3",
"opgroup": "Boolean"
}, {
"operator": "!BETWEEN",
"param_num": 3,
"label": "True if Param1 < Param2 and Param1 > Param3",
"opgroup": "Boolean"
}, {
"operator": "!BETWEENEQ",
"param_num": 3,
"label": "True if Param1 <= Param2 and Param1 >= Param3",
"opgroup": "Boolean"
})
for data in alarm_operators:
alarm_operator_id = self.postgres.insert('alarm_coperator', data)
if alarm_operator_id:
print("Alarm Conditions successfully added!")
else:
print("Failed to add Alarm Conditions")
class VPNIPUpdate(Common):
"""Class for VPNIPUpdate"""
# INITIALIZE
def __init__(self):
"""The Constructor for VPNIPUpdate class"""
# INIT CONFIG
self.config = ConfigParser()
# CONFIG FILE
self.config.read("config/config.cfg")
self.couch_query = Queries()
self.postgres = PostgreSQL()
self.epoch_default = 26763
self.vpn_db_build = config_section_parser(self.config,
"VPNDB")['build']
super(VPNIPUpdate, self).__init__()
if self.vpn_db_build.upper() == 'TRUE':
self.my_ip = config_section_parser(self.config, "IPS")['my']
self.my_protocol = config_section_parser(self.config,
"IPS")['my_protocol']
self.user_vpn = config_section_parser(self.config,
"IPS")['user_vpn']
self.user_protocol = config_section_parser(self.config,
"IPS")['user_protocol']
self.vessel_vpn = config_section_parser(self.config,
"IPS")['vessel_vpn']
self.vessel_protocol = config_section_parser(
self.config, "IPS")['vessel_protocol']
# self.generate_token()
self.vpn_token = '269c2c3706886d94aeefd6e7f7130ab08346590533d4c5b24ccaea9baa5211ec'
def vpn_ip_update(self):
"""VPN IP Update"""
if self.vpn_db_build.upper() == 'TRUE':
account_vpn_state = self.get_account_vpn_state()
if account_vpn_state:
self.update_account_vessel(account_vpn_state)
account_vessels = self.get_account_vessel(True)
for account_vessel in account_vessels or []:
self.check_job(account_vessel, 'ADD')
account_vessels = self.get_account_vessel(False)
disabled_account = self.get_account_vessel(
False, 'account_offline_vessel')
account_vessels += disabled_account
for account_vessel in account_vessels or []:
self.check_job(account_vessel, 'REMOVE')
return 1
def get_account_vpn_state(self):
"""Return Account VPN State"""
# INIT SQL QUERY
sql_str = "SELECT * FROM account WHERE vessel_vpn_state='pending'"
# FETCH ALL
res = self.postgres.query_fetch_all(sql_str)
# RETURN
return res
def get_account_vessel(self, allow_access, table="account_vessel"):
"""Return Account Vessel"""
# INIT SQL QUERY
sql_str = "SELECT DISTINCT (account_id), allow_access FROM {0}".format(
table)
sql_str += " WHERE vessel_vpn_state='pending' AND allow_access={0}".format(
allow_access)
# FETCH ALL
res = self.postgres.query_fetch_all(sql_str)
# RETURN
return res
def update_account_vessel(self, account_vpn_state):
"""Update Account Vessel"""
for avs in account_vpn_state:
account_id = avs['id']
vessel_allow_access = self.get_vessel_allow_access(account_id)
self.remove_account_vessel(account_id, vessel_allow_access)
# INIT CONDITION
conditions = []
# CONDITION FOR QUERY
conditions.append({"col": "id", "con": "=", "val": account_id})
updates = {}
updates['vessel_vpn_state'] = 'ok'
# UPDATE VESSEL VPN STATE
self.postgres.update('account', updates, conditions)
def check_job(self, account, action):
"""Check Job"""
account_id = account['account_id']
# INIT SQL QUERY
sql_str = "SELECT * FROM job"
sql_str += " WHERE account_id='{0}' AND status='pending'".format(
account_id)
sql_str += " AND action='{0}'".format(action)
res = self.postgres.query_fetch_one(sql_str)
# JOB DATAS
callback_url = self.my_protocol + "://" + self.my_ip + "/vpn/update"
data_url = self.my_protocol + "://" + self.my_ip + "/vpn/data"
if not res:
user_vpn = self.get_user_vpn(account_id)
if user_vpn:
sql_str = "SELECT * FROM account_role where account_id='{0}' ".format(
account_id)
sql_str += "AND role_id in (SELECT role_id FROM role "
sql_str += "WHERE role_name='super admin')"
super_admin = self.postgres.query_fetch_one(sql_str)
vpn_type = 'VCLIENT'
if not super_admin:
# vpn_type = 'VRH'
update_on = time.time()
# INIT NEW JOB
temp = {}
temp['callback_url'] = callback_url
temp['vnp_server_ip'] = user_vpn
temp['data_url'] = data_url
temp['token'] = self.vpn_token
temp['status'] = 'pending'
temp['account_id'] = account_id
temp['vpn_type'] = vpn_type
temp['action'] = action
temp['update_on'] = update_on
temp['created_on'] = update_on
# INSERT NEW JOB
job_id = self.postgres.insert('job', temp, 'job_id')
# INIT PARAMS FOR CREATE VPN
vpn_params = {}
vpn_params['callback_url'] = callback_url
vpn_params['data_url'] = data_url
vpn_params['job_id'] = job_id
self.add_vpn_ip(vpn_params, self.vpn_token, True)
return job_id
return 0
else:
job_id = res['job_id']
# INIT PARAMS FOR CREATE VPN
vpn_params = {}
vpn_params['callback_url'] = callback_url
vpn_params['data_url'] = data_url
vpn_params['job_id'] = job_id
self.add_vpn_ip(vpn_params, self.vpn_token, True)
return 0
def get_user_vpn(self, account_id):
"""Return User VPN"""
sql_str = "SELECT * FROM account_role where account_id='{0}' ".format(
account_id)
sql_str += "AND role_id in (SELECT role_id FROM role "
sql_str += "WHERE role_name='super admin')"
super_admin = self.postgres.query_fetch_one(sql_str)
vpn_type = 'VCLIENT'
if super_admin:
vpn_type = 'VRH'
# INIT SQL QUERY
sql_str = "SELECT * FROM account_vpn"
sql_str += " WHERE account_id='{0}' AND vpn_type='{1}'".format(
account_id, vpn_type)
sql_str += " ORDER BY created_on DESC LIMIT 1"
res = self.postgres.query_fetch_one(sql_str)
if res:
return res['vpn_ip']
return 0
def add_vpn_ip(self, data, vpn_token, flag=False):
"""Add VPN IP"""
api_endpoint = self.user_protocol + "://" + self.user_vpn + "/ovpn"
if flag:
api_endpoint = self.vessel_protocol + "://" + self.vessel_vpn + "/ovpn"
headers = {'content-type': 'application/json', 'token': vpn_token}
req = requests.post(api_endpoint,
data=json.dumps(data),
headers=headers)
res = req.json()
return res
def get_vessel_allow_access(self, account_id):
"""Return Vessel Allow Access"""
# DATA
sql_str = "SELECT * FROM account_company WHERE account_id = " + str(
account_id)
res = self.postgres.query_fetch_all(sql_str)
rows = []
# COMPANY'S VESSELS-ID
for company in res:
company_id = company['company_id']
vessel_res = self.get_company_vessels(company_id)
vessels = []
for vessel in vessel_res['rows']:
temp = {}
temp['vessel_id'] = vessel['vessel_id']
temp['allow_access'] = self.get_allow_access(
account_id, vessel['vessel_id'])
vessels.append(temp)
company['vessels'] = vessels
rows.append(company)
temp_vessels = []
list_vessels = []
for row in rows:
for row_vessel in row['vessels'] or []:
if row_vessel['vessel_id'] not in list_vessels:
list_vessels.append(row_vessel['vessel_id'])
temp_vessels.append(row_vessel)
# CLOSE CONNECTION
self.postgres.close_connection()
return temp_vessels
# GET VESSELS OF COMPANY
def get_company_vessels(self, company_id):
"""Return Company Vessels"""
assert company_id, "CompanyID is required."
# DATA
vessels = []
sql_str = "SELECT * FROM company_vessels WHERE company_id={0}".format(
company_id)
vessels = self.postgres.query_fetch_all(sql_str)
data = {}
data['rows'] = vessels
return data
def get_allow_access(self, account_id, vessel_id):
"""Return Allow Access"""
# DATA
sql_str = "SELECT * FROM account_vessel"
sql_str += " WHERE account_id={0} AND vessel_id='{1}'".format(
account_id, vessel_id)
vessel = self.postgres.query_fetch_one(sql_str)
if vessel:
return vessel['allow_access']
ntwconf = self.couch_query.get_complete_values(vessel_id, "NTWCONF")
# INSERT ACCOUNT VESSEL
temp = {}
temp['vessel_vpn_ip'] = ntwconf['NTWCONF']['tun1']['IP']
temp['account_id'] = account_id
temp['vessel_id'] = vessel_id
temp['allow_access'] = False
temp['vessel_vpn_state'] = 'ok'
self.postgres.insert('account_vessel', temp)
return False
def remove_account_vessel(self, account_id, vessel_allow_access):
"""Remove Account Vessel"""
vessel_ids = [x['vessel_id'] for x in vessel_allow_access]
if vessel_ids:
# INIT SQL QUERY
sql_str = "SELECT allow_access, vessel_id FROM account_vessel"
if len(vessel_ids) == 1:
sql_str += " WHERE account_id={0}".format(account_id)
sql_str += " AND vessel_id NOT IN ('{0}')".format(
vessel_ids[0])
else:
sql_str += " WHERE account_id={0}".format(account_id)
sql_str += " AND vessel_id NOT IN {0}".format(
tuple(vessel_ids))
# FETCH ALL
rows = self.postgres.query_fetch_all(sql_str)
for row in rows or []:
conditions = []
conditions.append({
"col": "vessel_id",
"con": "=",
"val": row['vessel_id']
})
conditions.append({
"col": "account_id",
"con": "=",
"val": account_id
})
account_vessel_data = {}
account_vessel_data['vessel_vpn_state'] = 'pending'
account_vessel_data['allow_access'] = False
self.postgres.update('account_vessel', account_vessel_data,
conditions)
# RETURN
return 1
return 0
def vpn_terminal_receiver():
'''
usage:
python script_vpn_real_vessel_manual_creation.py -vessel_number <vessel-number> -vessel_name <vessel_name> -account_os <vessel-os>
eg. [terminal command]:
python script_vpn_real_vessel_manual_creation.py -vessel_number 1 -vessel_name LABO1 -vessel_os LINUX
help:
python script_vpn_real_vessel_manual_creation.py -h
'''
print('\n***[START] SCRIPT_VPN_REAL_VESSEL_MANUAL_CREATION. \n\n')
parser = argparse.ArgumentParser()
parser.add_argument('-vessel_number',
action='store',
dest='vessel_number',
help='Vessel Number - must be unique')
parser.add_argument('-vessel_name',
action='store',
dest='vessel_name',
help="Vessel Name")
parser.add_argument('-vessel_os',
action='store',
dest="vessel_os",
help='Account OS - LINUX/WINDOWS')
results = parser.parse_args()
vessel_number = int(results.vessel_number)
vessel_name = results.vessel_name
vessel_os = results.vessel_os
assert vessel_number
assert vessel_name
assert vessel_os
# GET ID OF DEFAULT TABLE-ID OF MANUAL-INSERTED REAL-VESSEL BASED ON JOB-ID'S -3
postgresql_query = PostgreSQL()
manual_realvessel_tbl_id = None
postgresql_query.connection()
sql_str = "select * from vpn_access_requests where job_id = -3"
result = postgresql_query.query_fetch_one(sql_str)
postgresql_query.close_connection()
if hasattr(result, 'get'):
manual_realvessel_tbl_id = result['id']
##### FINAL DATA
id_vpn_access_requests = manual_realvessel_tbl_id # DEFAULT FOR MANUAL CREATION - [DO NOT CHANGE THIS]
vpn_type = 'VESSEL' # DEFAULT - [DO NOT CHANGE THIS]
account_id = vessel_number
account_name = vessel_name
account_os = vessel_os
vpn_access_create = Vpn_Access_Create(id_vpn_access_requests, account_id,
account_name, vpn_type, account_os)
vpn_access_create.create_static_ip()
if vpn_access_create.current_ip_1:
print('\n\n')
print('-' * 50)
print('ZIP FILE PATH: {}'.format(vpn_access_create.zip_file_path))
print('-' * 50)
print('\n\n***[DONE] SCRIPT_VPN_REAL_VESSEL_MANUAL_CREATION.\n')
class Common():
# RETURN DATA
def return_data(self, data):
# RETURN
return jsonify(data)
# SQL QUERY RETURN CONVERT TO JSON
def convert_to_json(self, data):
# INITIALIZE
json_data = []
# LOOP DATA
for result in data:
# APPEND JSON DATA
json_data.append(dict(zip(row_headers, result)))
# RETURN
return json.dumps(json_data)
# REMOVE KEY
def remove_key(self, data, item):
# CHECK DATA
if item in data:
# REMOVE DATA
del data[item]
# RETURN
return data
# GET INFO
def get_info(self, columns, table):
# CHECK IF COLUMN EXIST,RETURN 0 IF NOT
if not columns: return 0
# INITIALIZE
cols = ''
count = 1
# LOOP COLUMNS
for data in columns:
# CHECK IF COUNT EQUAL COLUMN LENGHT
if len(columns) == count:
# ADD DATA
cols += data
else:
# ADD DATA
cols += data + ", "
# INCREASE COUNT
count += 1
# CREATE SQL QUERY
sql_str = "SELECT " + cols + " FROM " + table
# INITIALIZE DATABASE INFO
self.my_db = MySQL_DATABASE()
# CONNECT TO DATABASE
self.my_db.connection_to_db(self.my_db.database)
# CALL FUNCTION QUERY ONE
ret = self.my_db.query_fetch_one(sql_str)
# CLOSE CONNECTION
self.my_db.close_connection()
# RETURN
return ret
# GET INFOS
def get_infos(self, columns, table):
# CHECK IF COLUMN EXIST,RETURN 0 IF NOT
if not columns: return 0
# INITIALIZE
cols = ''
count = 1
# LOOP COLUMNS
for data in columns:
# CHECK IF COUNT EQUAL COLUMN LENGHT
if len(columns) == count:
# ADD DATA
cols += data
else:
# ADD DATA
cols += data + ", "
# INCREASE COUNT
count += 1
# CREATE SQL QUERY
sql_str = "SELECT " + cols + " FROM " + table
# INITIALIZE DATABASE INFO
self.my_db = MySQL_DATABASE()
# CONNECT TO DATABASE
self.my_db.connection_to_db(self.my_db.database)
# CALL FUNCTION QUERY ONE
ret = self.my_db.query_fetch_all(sql_str)
# CLOSE CONNECTION
self.my_db.close_connection()
# RETURN
return ret
# GET USER INFO
def get_user_info(self, columns, table, user_id, token):
# CHECK IF COLUMN EXIST,RETURN 0 IF NOT
if not columns: return 0
# INITIALIZE
cols = ''
count = 1
# LOOP COLUMNS
for data in columns:
# CHECK IF COUNT EQUAL COLUMN LENGHT
if len(columns) == count:
# ADD DATA
cols += data
else:
# ADD DATA
cols += data + ", "
# INCREASE COUNT
count += 1
# CREATE SQL QUERY
sql_str = "SELECT " + cols + " FROM " + table + " WHERE "
sql_str += " token = '" + token + "'"
sql_str += " AND id = '" + user_id + "'"
# INITIALIZE DATABASE INFO
# self.my_db = MySQL_DATABASE()
self.postgres = PostgreSQL()
# CONNECT TO DATABASE
self.postgres.connection()
# CALL FUNCTION QUERY ONE
ret = self.postgres.query_fetch_one(sql_str)
# CLOSE CONNECTION
self.postgres.close_connection()
# RETURN
return ret
# VALIDATE TOKEN
def validate_token(self, token, user_id):
import datetime
import dateutil.relativedelta
# CHECK IF COLUMN EXIST,RETURN 0 IF NOT
if not token: return 0
# SET COLUMN FOR RETURN
columns = ['username', 'update_on']
# CHECK IF TOKEN EXISTS
user_data = self.get_user_info(columns, "account", user_id, token)
data = {}
data['update_on'] = time.time() #datetime.fromtimestamp(time.time())
condition = []
temp_con = {}
temp_con['col'] = 'id'
temp_con['val'] = user_id
temp_con['con'] = "="
condition.append(temp_con)
self.postgres = PostgreSQL()
self.postgres.update('account', data, condition)
# CHECK IF COLUMN EXIST,RETURN 0 IF NOT
if user_data:
dt1 = datetime.datetime.fromtimestamp(user_data['update_on'])
dt2 = datetime.datetime.fromtimestamp(time.time())
rd = dateutil.relativedelta.relativedelta(dt2, dt1)
# print(rd.years, rd.months, rd.days, rd.hours, rd.minutes, rd.seconds)
if rd.years or rd.months or rd.days or rd.hours: return 0
if rd.minutes > 30: return 0
else: return 0
# RETURN
return 1
def device_complete_name(self, name, number=''):
# SET READABLE DEVICE NAMES
humanize_array = {}
humanize_array['NTWCONF'] = 'Network Configuration'
humanize_array['NTWPERF'] = 'Network Performance ' + str(number)
humanize_array['COREVALUES'] = 'Core Values'
humanize_array['IOP'] = 'Irridium OpenPort ' + str(number)
humanize_array['VDR'] = 'VDR ' + str(number)
humanize_array['VSAT'] = 'V-SAT ' + str(number)
humanize_array['MODEM'] = 'MODEM ' + str(number)
humanize_array['FBB'] = 'FleetBroadBand ' + str(number)
humanize_array['VHF'] = 'VHF ' + str(number)
humanize_array['SATC'] = 'SAT-C ' + str(number)
# RETURN
return humanize_array[name]
# COUNT DATA
def count_data(self, datas, column, item):
# INITIALIZE
count = 0
# LOOP DATAS
for data in datas:
# CHECK OF DATA
if data[column] == item:
# INCREASE COUNT
count += 1
# RETURN
return count
# REMOVE KEY
def remove_data(self, datas, remove):
ret_data = []
# CHECK DATA
for data in datas:
if not data['device'] in remove: ret_data.append(data)
# RETURN
return ret_data
def set_return(self, datas):
ret_data = {}
ret_data['data'] = []
for data in datas:
ret_data['data'].append(data['value'])
return ret_data
def check_time_lapse(self, current, timestamp):
from datetime import datetime
struct_now = time.localtime(current)
new_time = time.strftime("%m/%d/%Y %H:%M:%S %Z", struct_now)
vessel_time = time.localtime(timestamp)
vessel_time = time.strftime("%m/%d/%Y %H:%M:%S %Z", vessel_time)
vessel_time = vessel_time.split(' ')
v_time = vessel_time[1]
v_date = vessel_time[0]
new_time = new_time.split(' ')
n_time = new_time[1]
n_date = new_time[0]
start_date = datetime.strptime(v_date, "%m/%d/%Y")
end_date = datetime.strptime(n_date, "%m/%d/%Y")
# if not abs((start_date-start_date).days):
if not abs((start_date - end_date).days):
FMT = '%H:%M:%S'
tdelta = datetime.strptime(str(n_time), FMT) - datetime.strptime(
str(v_time), FMT)
tdelta = str(tdelta).split(":")
try:
if int(tdelta[0]):
return 'red'
if int(tdelta[1]) < 10:
return 'green'
if int(tdelta[1]) < 20:
return 'orange'
except:
return 'red'
return 'red'
def get_ids(self, key, datas):
module_ids = []
for data in datas or []:
module_ids.append(data['module'])
return module_ids
def check_request_json(self, query_json, important_keys):
query_json = simplejson.loads(simplejson.dumps(query_json))
for imp_key in important_keys.keys():
if type(query_json.get(imp_key)):
if not type(query_json[imp_key]) == type(
important_keys[imp_key]):
return 0
else:
return 0
return 1
def milli_to_sec(self, millis):
# SET TO INT
millis = int(millis)
# CONVERT
seconds = (millis / 1000)
# RETURN
return int(seconds)
class Login(Common, ShaSecurity):
"""Class for Login"""
def __init__(self):
"""The Constructor for Login class"""
self.postgres = PostgreSQL()
super(Login, self).__init__()
# LOGIN FUNCTION
def login(self):
"""
This API is use for login
---
tags:
- User
consumes:
- application/json
produces:
- application/json
parameters:
- name: query
in: body
description: Login
required: true
schema:
id: Login
properties:
email:
type: string
password:
type: string
responses:
500:
description: Error
200:
description: User Information
"""
# GET JSON REQUEST
query_json = request.get_json(force=True)
# INSTANTIATE VARIABLES
json_return = {}
# account_table = "account"
# GET REQUEST PARAMS
email = query_json["email"]
password = query_json["password"]
# CHECK IF USER EXIST
user_data = self.get_user_data(email)
# CHECK USERNAME
if user_data:
if "no_username" in user_data['username']:
user_data['username'] = ""
if not user_data:
json_return["alert"] = "Invalid Username or Password!"
json_return['status'] = 'Failed'
# logging.warning("Email does not exists, email: %s " % email)
# RETURN
return self.return_data(json_return)
# CHECK IF USER STATUS IS ACTIVE
if not self.check_user_status(user_data):
json_return["alert"] = "User is deactivated!"
json_return['status'] = 'Failed'
# logging.warning("User is deactivated, email: %s " % email)
# RETURN
return self.return_data(json_return)
# CHECK PASSWORD
if not self.check_password(password, user_data):
json_return["alert"] = "Invalid Username or Password!"
json_return['status'] = 'Failed'
# logging.warning("Invalid Username or Password")
# CLOSE DB CONNECTION
self.postgres.close_connection()
# RETURN
return self.return_data(json_return)
# UPDATE TOKEN
user_data = self.update_token(user_data)
user_data = self.remove_key(user_data, "password")
user_data = self.remove_key(user_data, "created_on")
user_data = self.remove_key(user_data, "last_login")
# CLOSE DB CONNECTION
self.postgres.close_connection()
# RETURN
return self.return_data(user_data)
# GET USER INFO
def get_user_data(self, email):
"""Return User Information"""
# CREATE SQL QUERY
# sql_str = "SELECT * FROM " + table + " WHERE email=\'"+email+"\'"
sql_str = """
SELECT a.id, a.username, a.email, a.status, a.password, (
SELECT array_to_json(array_agg(role_perm)) FROM (
SELECT r.role_id, r.role_name, r.role_details, (
SELECT array_to_json(array_agg(p)) FROM permission p
INNER JOIN role_permission rp ON
rp.permission_id = p.permission_id
WHERE rp.role_id = r.role_id) AS permissions
FROM role r INNER JOIN account_role ar
ON ar.role_id = r.role_id
WHERE ar.account_id = a.id) AS role_perm) AS roles, (
SELECT array_to_json(array_agg(c)) FROM company c
INNER JOIN account_company ac
ON ac.company_id = c.company_id
WHERE ac.account_id = a.id) AS companies FROM account a
"""
sql_str += " WHERE email='{0}' OR username='******'".format(email)
# GET USER INFO
user_data = self.postgres.query_fetch_one(sql_str)
# CHECK IF USER EXIST
if not user_data:
# RETURN
return 0
# RETURN
return user_data
# CHECK USER STATUS
def check_user_status(self, user_data):
"""Check User Status"""
# CHECK IF STATUS IS TRUE OR 1
if user_data["status"]:
# RETURN
return 1
# RETURN
return 0
# CHECK PASSWORD
def check_password(self, password, user_data):
"""Check User Password"""
# CHECK PASSWORD
if password == user_data["password"]:
# RETURN
return 1
# RETURN
return 0
def update_token(self, user_data):
"""Update Token"""
new_token = self.generate_token()
data = {}
data['token'] = new_token
data['update_on'] = time.time()
data['last_login'] = time.time()
condition = []
temp_con = {}
temp_con['col'] = 'id'
temp_con['val'] = user_data["id"]
temp_con['con'] = "="
condition.append(temp_con)
if self.postgres.update('account', data, condition):
user_data["token"] = new_token
return user_data
