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 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 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 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
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 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
for prefix in accepted_prefix_list: if d['name'].startswith(prefix): d['vpn_type'] = vpn_type new_data_list.append(d) break print('new_data_list:', new_data_list) for d in new_data_list: print(d['num'], d['name'], d['ip'], d['vpn_type']) if True: try: postgresql_query = PostgreSQL() postgresql_query.connection() for d in new_data_list: name = d['name'] ip = d['ip'] ip_addr_1 = ip last_num = int(re.search(r'\.(\d+)$', ip_addr_1).group(1)) ip_addr_2 = re.sub(r'(\d+)$', str(last_num + 1), ip_addr_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
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")
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)