def newtilt(tilt=None): logger.debug(f"tilt {tilt}") if not tilt: return return tilt + DELTA_TILT if tilt + DELTA_TILT < MAX_TILT else tilt
def rets_data(time_=None): logger.debug(f'ENV {ENV}') if not time_: logger.info(f'time_ {time_}') return when_ = time_ period = when_.strftime("%Y-%m-%d") query_ = f''' select x.datetimeid, x.node, x.devicename, x.deviceno, x.tilt, x.subname, x.subunitno, y.localcellid, y.eci, y.cellname from (select ret.dateid as datetimeid, ret.node as node, ret.devicename as devicename, ret.deviceno as deviceno, sub.tilt as tilt, sub.subname as subname, sub.subunitno as subunitno from ret inner join retsubunit sub on date(ret.dateid) = date(sub.dateid) and ret.node = sub.node and ret.deviceno = sub.deviceno where date(ret.dateid) = current_date) as x inner join lcellreference as y on (x.node = y.node and (x.deviceno = y.localcellid or x.deviceno = y.localcellid + 10) and STR_TO_DATE(y.dateid, '%Y-%m-%d') = '{period}'); ''' return pd_sql(time_=time_, query_=query_)
def processor(time_=None): ''' Esta función detecta las transacciones en la tabla transactions. y las ejecuta sobre el nbi. Por ahora no se reintentan transacciones fallidas. ''' logger.debug(f"time_ {time_}") if not time_: return engine = get_engine() session = get_session(engine=engine) # detectar las transacciones a procesar # trxs = session.query(Transaction).filter(Transaction.sent.is_(null())) # trxs = session.query(Transaction).filter( # or_(Transaction.sent.is_(null()), # Transaction.oldtilt != Transaction.newtilt)).first() trxs = session.query(Transaction).filter( or_(Transaction.sent.is_(null()), Transaction.oldtilt != Transaction.newtilt)) if ENV == 'sim': for trx in trxs: # logger.info(f"trx \n{trx}") nbi_simulator(time_=time_, session_=session, trx_=trx) if ENV == 'prod': nbi_processor(time_=time_, session_=session, trxs_=trxs) session.commit() session.close()
def pd_sql(time_=None, query_=None): logger.debug(f'ENV {ENV}') if not time_: logger.info(f'time_ {time_}') return try: cnx = mysql.connector.connect( user=user_, password=password_, host=host_, database=database_, use_pure=True, ) query = query_ df = pd.read_sql(query,cnx) logger.info(f'df.shape {df.shape}') cnx.close() return df except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: logger.error("Something is wrong with your user name or password") elif err.errno == errorcode.ER_BAD_DB_ERROR: logger.error("Database does not exist") else: logger.error(err) else: cnx.close()
def scheduler(time_=None): logger.debug(f"time_ {time_}") if not time_: return engine = get_engine() db_connection = engine.connect() # set más reciente de 'High' overshooters en terreno plano query_ = ''' select o.cellname from overshooters o, terrains t where o.cellname = t.cellname and o.overshooter and t.is_plain and o.intensity = 'High' and o.datetimeid = (select max(datetimeid) from overshooters) and t.datetimeid = (select max(datetimeid) from terrains); ''' candidates_df = pd.read_sql(query_, db_connection) db_connection.close() # entregar los candidatos a mid_term_evaluator() mid_term_evaluator(time_=time_, candidates_df=candidates_df)
def delta_percentaje(reference=None, value=None): logger.debug(f"reference {reference} value {value}") if not reference or not value: return delta = reference - value return delta * 100 / reference
def neighborhood(time_=None): logger.debug(f'ENV {ENV}') if not time_: logger.info(f'time_ {time_}') return cells_df = get_cells_df(time_=time_) l = ['SITE', 'LAT', 'LON'] sites_df = cells_df[l].drop_duplicates() sites_df['key'] = 1 merged_df = pd.merge(sites_df, sites_df, on='key').drop("key", 1) merged_df = merged_df[merged_df['SITE_x'] != merged_df['SITE_y']] merged_df['distance_'] = haversine_distance(merged_df['LAT_x'].values, merged_df['LON_x'].values, merged_df['LAT_y'].values, merged_df['LON_y'].values) merged_df['bearing_'] = bearing(merged_df['LAT_x'].values, merged_df['LON_x'].values, merged_df['LAT_y'].values, merged_df['LON_y'].values) # logger.info(f'neighborhood: merged_df.columns {merged_df.columns}') l = ['SITE_x', 'SITE_y', 'distance_', 'bearing_'] merged_df = merged_df[l] merged_df = merged_df[merged_df['distance_'] <= KM] l = ['SITE', 'CELLNAME', 'AZIMUTH'] merged_df = pd.merge(cells_df[l], merged_df, how="inner", left_on='SITE', right_on='SITE_x') merged_df = merged_df[(merged_df['bearing_'] > merged_df['AZIMUTH'] - D) & (merged_df['bearing_'] < merged_df['AZIMUTH'] + D)] l = ['CELLNAME', 'distance_'] merged_df.sort_values(by=l, inplace=True) l = ['CELLNAME'] neighborhood_df = merged_df.groupby(l).head(N_DISTANCE) l = ['CELLNAME', 'AZIMUTH', 'SITE_x', 'SITE_y', 'distance_', 'bearing_'] logger.info(f'neighborhood_df[l].shape {neighborhood_df[l].shape}') return neighborhood_df[l], cells_df
def main(): # time_=datetime.datetime(2021, 2, 25, 10, 30, 0, 0) # day_before = time_ - datetime.timedelta(days=1) now_ = datetime.datetime.now() time_ = now_ period = time_.strftime("%Y-%m-%d") query_ = f''' select distinct * from lcellreference as l where STR_TO_DATE(l.dateid, '%Y-%m-%d') = '{period}'; ''' df = pd_sql(time_=time_, query_=query_) logger.debug(f'df.shape {df.shape}')
def enabler(cellnames=None): logger.debug(f'ENV {ENV}') engine = get_engine() session = get_session(engine=engine) for cellname in cellnames: # logger.debug(f'cellname {cellname}') antennas = session.query(Ret).filter(Ret.cellname == cellname, ) for antenna in antennas: antenna.enabled = True # logger.info(f'node {antenna.node} deviceno {antenna.deviceno}') # session.commit() session.commit() session.close()
def average_kpis(time_=None): ''' Esta función recibe time_ de modo de poder inferir el periodo. Para el periodo anterior, esta función devuelve para todas las celdas (cellname) : user_avg, user_thrp_dl, traffic_dl. Los datos anteriores corresponden al promedio de los valores de esas muestras para la data del periodo por celda. Esta función devuelve un dataframe con la data anterior. ''' logger.debug(f"ENV {ENV} time_ {time_}") if not time_: return # period = time_.strftime("%Y%m%d") # logger.debug(f"period {period}") data_df = pd.DataFrame() # empty df if ENV == 'sim': dict_ = { 'eNodeB_Name': [ 'MBTS-AIS_3G_003', 'MBTS-ARA_3G_013', ], # 'cellname': ['AIS_4G_003_3', 'ARA_4G_013_3',], 'Cell_Name': [ 'AIS_4G_003_3', 'ARA_4G_013_3', ], 'user_avg': [ 81.0, 200.0, ], 'user_thrp_dl': [ 25.4, 23.2, ], 'traffic_dl': [8285.170, 7660.760], } data_df = pd.DataFrame.from_dict(dict_) if ENV == 'prod': data_df = get_ta_df(time_=time_) return data_df
def all_enabler(time_=None): ''' La finalidad de esta función es habilitar a todas las celdas. Con excepción de aquellas cuyo nombre haga match con el pattern _MM_ ''' logger.debug(f'ENV {ENV} time_ {time_}') if not time_: logger.info(f'time_ {time_}') return df = cells_data(time_=time_) cellnames = df[~df['CELLNAME'].str.contains("_MM_")]['CELLNAME'].drop_duplicates().tolist() logger.debug(f'len(cellnames) {len(cellnames)}') enabler(cellnames=cellnames)
def cells_data(time_=None): logger.debug(f'ENV {ENV} time_ {time_}') if not time_: logger.info(f'time_ {time_}') return when_ = time_ period = when_.strftime("%Y-%m-%d") logger.debug(f'period {period}') query_ = f''' select distinct * from lcellreference as l where STR_TO_DATE(l.dateid, '%Y-%m-%d') = '{period}'; ''' return pd_sql(time_=time_, query_=query_)
def trx_updater(commands=None, sent_=None): ''' Esta función recibe una lista de diccionarios, con las respuestas a los comandos de cambio de tilt ejecutados en el NBI. Si el resultado es exitoso se actualizan las tablas rets y transactions. ''' logger.debug(f"ENV {ENV}") if not commands: return engine = get_engine() session = get_session(engine=engine) for command in commands: result = command['data']['result'] logger.debug(f"result {result}") executed_time_stamp_str = command['data']['executed_time_stamp'] executed_time_stamp = datetime.datetime.strptime( executed_time_stamp_str, '%Y-%m-%d %H:%M:%S') object_id = command['object_id'] trx = session.query(Transaction).filter( Transaction.id == object_id).first() if not trx: session.commit() session.close() return trx.sent = sent_ if result: trx.oldtilt = trx.newtilt trx.success = executed_time_stamp ret_updater(node=trx.node, deviceno=trx.deviceno, tilt=trx.newtilt, session=session) else: logger.info(f"result {result}") trx.failure = executed_time_stamp session.commit() session.close()
def transactions(time_=None): ''' Esta función detecta las transacciones en la tabla transactions. y las ejecuta sobre el nbi. Por ahora no se reintentan transacciones fallidas. ''' logger.debug(f"time_ {time_}") if not time_: return engine = get_engine() session = get_session(engine=engine) # detectar las transacciones a procesar trxs = session.query(Transaction).filter(Transaction.sent.is_(null())) for trx in trxs: # logger.info(f"trx \n{trx}") processor(time_=time_,session_=session,trx_=trx) session.commit() session.close()
def ret_updater(node=None, deviceno=None, tilt=None, session=None): logger.debug(f"ENV {ENV}") if not node or not deviceno or not tilt or not session: return ret = session.query(func.max(Ret.datetimeid)).first() logger.debug(f"ret {ret} type {type(ret)}") for datetimeid_ in ret: pass if not datetimeid_: return trx = session.query(Ret).filter( and_(Ret.node == node, Ret.deviceno == deviceno, Ret.datetimeid == datetimeid_)).first() if not trx: return trx.tilt = tilt session.commit()
def giver_of_times(): ''' Esta funcion es un generador de tiempos. Estos tiempos se asocian a los ciclos. Es el reloj del simulador. ''' logger.debug(f"") time_list = [ datetime.datetime(2021, 1, 10, 10, 30, 0, 0), datetime.datetime(2021, 1, 11, 10, 30, 0, 0), datetime.datetime(2021, 1, 12, 10, 30, 0, 0), datetime.datetime(2021, 1, 13, 10, 30, 0, 0), ] seconds = 1 if ENV == 'sim': for time_ in time_list: time.sleep(seconds) yield time_ if ENV == 'prod': yield datetime.datetime.now()
def nbi_simulator(time_=None, session_=None, trx_=None): logger.debug(f"time_ {time_}") if not time_ or not session_ or not trx_: pass logger.info(f"trx_ \n{trx_}") # logger.info(f"ENV {ENV}") # if ENV == 'sim': logger.info(f"ENV {ENV}") trx_.sent = datetime.now() nbi_response = failure_percentage() logger.info(f"nbi_response {nbi_response}") if nbi_response: trx_.success = datetime.now() else: trx_.failure = datetime.now() session_.commit()
def ta_data(time_=None): logger.debug(f'ENV {ENV}') if not time_: logger.info(f'time_ {time_}') return when_ = time_ period = when_.strftime("%Y-%m-%d") query_ = f''' select distinct * from prs_lte_hour p where STR_TO_DATE(p.dateid_date, '%Y-%m-%d') between '{period}' and '{period}'; ''' query_ = f''' select distinct * from prs_lte_hour p where STR_TO_DATE(p.dateid_date, '%Y-%m-%d') between '{period}' and '{period}' and p.dateid_hour = '20'; ''' return pd_sql(time_=time_, query_=query_)
def get_prod_engine(): logger.debug(f'get_engine:') return create_engine(PROD_DB_STR_CONNECTION, echo=ECHO)
def evaluator(time_=None, candidates_kpis_df=pd.DataFrame()): ''' Esta función recibe todas las celdas candidatas y sus kpis promedio, para el instante actual. Dependiendo de si la celda existe en la tabla transactions, hay comparaciones con kpis promedio iniciales. En base a reglas pueden entrar transacciones a la tabla transactions. ''' logger.debug(f"time_ {time_}") if not time_: return if candidates_kpis_df.empty: return logger.debug(f"candidates_kpis_df \n{candidates_kpis_df}") engine = get_engine() session = get_session(engine=engine) for idx in candidates_kpis_df.index: # overshooters plain terrain node = candidates_kpis_df['eNodeB_Name'][idx] user_avg = candidates_kpis_df['user_avg'][idx] user_thrp_dl = candidates_kpis_df['user_thrp_dl'][idx] traffic_dl = candidates_kpis_df['traffic_dl'][idx] antennas = session.query(Ret).filter(Ret.node == node, ) for antenna in antennas: if not antenna.enabled: continue logger.debug(f"node {antenna.node} deviceno {antenna.deviceno}") trx = session.query(Transaction).filter( and_(Transaction.node == antenna.node, Transaction.deviceno == antenna.deviceno)).first() if trx: # si trx anterior no fue exitosa if not trx.success: logger.debug(f"continue: success {trx.success}") continue cond_ = delta_percentaje( trx.user_thrp_dl_initial, user_thrp_dl) > MAX_DELTA_USER_THRP_DL_PERCENTAJE cond_ = cond_ or delta_percentaje( trx.traffic_dl_initial, traffic_dl) > MAX_DELTA_TRAFFIC_DL_PERCENTAJE if cond_: # rollback logger.debug(f"rollback") newtilt_ = trx.oldtilt else: newtilt_ = newtilt(trx.newtilt) if trx.newtilt == newtilt_: logger.debug(f"continue: newtilt_ {newtilt_}") continue # si nuevo tilt es distinto al último trx.newtilt = newtilt_ trx.generated = datetime.now() else: if not (user_avg >= MIN_USER_AVG and user_avg <= MAX_USER_AVG): logger.debug(f"continue: user_avg {user_avg}") continue if antenna.tilt == newtilt(antenna.tilt): logger.debug( f"continue: antenna.tilt == newtilt(antenna.tilt)") continue # se crea entrada en tabla transactions trx = Transaction( node=antenna.node, cellname=antenna.cellname, deviceno=antenna.deviceno, subunitno=antenna.subunitno, tilt_initial=antenna.tilt, # oldtilt = tilt_initial, oldtilt=antenna.tilt, # originalmente # user_thrp_dl_initial = user_thrp_dl, # traffic_dl_initial = traffic_dl, # para ver si pasa user_thrp_dl_initial=float(user_thrp_dl), traffic_dl_initial=float(traffic_dl), newtilt=newtilt(antenna.tilt), datetimeid=time_, generated=datetime.now(), ) logger.debug(f"trx \n{trx}") session.add(trx) session.commit() session.commit() session.close()
def create_tables(): logger.debug(f'create_tables:') engine = get_engine() BASE.metadata.create_all(engine) return True
def nbi_processor(time_=None, session_=None, trxs_=None): ''' Esta función recibe el query (trxs_) con todas las transacciones a ejecutar. Construye un mensaje al NBI con todas ellas. Espera el mensaje de respuesta y de acuerdo con lo recibido actualiza las transacciones en la BD (transactions y rets) ''' logger.debug(f"time_ {time_} ENV {ENV}") # logger.debug(f"trxs_ \n{trxs_} ENV {ENV}") if not time_ or not session_ or not trxs_: return # logger.debug(f"hello !!! ..") ''' { 'object_id': 14144, 'data': { 'command': 'MOD CELLDLSCHALGO:LOCALCELLID=0,DLEPFCAPACITYFACTOR=EPF_CAPC_FACTOR_1;', 'network_element': 'MBTS-VAL_3G_138' } }, ''' # RET Command # MOD RETSUBUNIT:DEVICENO=0,SUBUNITNO=1,TILT=60;{MBT-RM2023} command_list = [] for trx in trxs_: logger.debug(f"trx \n{trx}") object_id = 14144 command = ( f'MOD RETSUBUNIT:DEVICENO={trx.deviceno},' f'SUBUNITNO={trx.subunitno},' f'TILT={trx.newtilt};' # '{' # f'{trx.node}' # '}' ) network_element = f'{trx.node}' dict_ = { # 'object_id': object_id, # original 'object_id': trx.id, # para probar si puedo hilar con la trx 'data': { 'command': command, 'network_element': network_element } } command_list.append(dict_) logger.debug(f"command_list \n{command_list}") if not command_list: return random_script_id = str(random.randint(0, 1e6)).zfill(6)+\ ' '+datetime.datetime.now().strftime('%Y%m%d%H%M%S') data = { 'client_id': CLIENT_ID, 'script_id': random_script_id, 'command_type': ['MOD'], 'timestamp': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), 'timeout': -1, 'priority': 0, 'corre_id': 20, 'command_list': command_list } event_ = Event(type_='mml_type', data=data) id_ = event_.id_ logger.debug(f"id_ {id_} ENV {ENV}") sent_ = datetime.datetime.now() producer.send(MML_TOPIC, value=event_.as_dictionary()) logger.debug(f"after producer.send(MML_TOPIC ..)") logger.debug(f"MML_TOPIC {MML_TOPIC}") for m in consumer: logger.debug(f"after for m in consumer:") if id_ == m.value['id_']: logger.debug("*** match") # logger.info(f"m.value \n{m.value}") pprint.pprint(m.value, indent=0, width=120) ''' - para cada comando ejecutado, estudiar respuesta y actualizar trxs y rets, si corresponde ''' trx_updater(commands=m.value['data']['command_list'], sent_=sent_) break else: print("continue", flush=True) continue
def main(): for time_ in giver_of_times(): logger.debug(f"time_ {time_}")
def overshooters(time_=None, neighborhood_df=pd.DataFrame(), cells_df=pd.DataFrame()): logger.debug(f'ENV {ENV}') if not time_: logger.info(f'time_ {time_}') return # ------------------------------------------ # fue necesario setear tiempos para avanzar con las pruebas .. # now_ = datetime.datetime.now() if neighborhood_df.empty: neighborhood_df, cells_df = neighborhood(time_) day_before = time_ - datetime.timedelta(days=1) ta_df = get_ta_df(time_=day_before) # neighborhood_df.reset_index(inplace=True) # cells_df.reset_index(inplace=True) # ta_df.reset_index(inplace=True) # ------------------------------------------ neighborhood_df, overshooters_df = overshooting( neighborhood_df=neighborhood_df, ta_df=ta_df) # neighborhood_df.to_excel(r'data/neighborhood_df.xlsx', index = False) # overshooters_df.to_excel(r'data/overshooters_df.xlsx', index = False) # neighborhood_df.reset_index(inplace=True) # overshooters_df.reset_index(inplace=True) neighborhood_df, overshooters_intensity_df = overshooting_intensity( neighborhood_df=neighborhood_df, ta_df=ta_df) # neighborhood_df.to_excel(r'data/neighborhood_df.xlsx', index = False) overshooters_intensity_df.to_excel(r'data/overshooters_intensity_df.xlsx', index=False) # neighborhood_df.reset_index(inplace=True) # overshooters_intensity_df.reset_index(inplace=True) intensity_df = overshooters_intensity_df.drop(['distance_'], axis=1) # intensity_df.reset_index(inplace=True) merged_df = pd.merge(overshooters_df, intensity_df, how="inner", left_on='CELLNAME', right_on='CELLNAME').drop_duplicates() # merged_df.to_excel(r'data/merged_df.xlsx', index = False) # merged_df.reset_index(inplace=True) # ------------------------------------------------ l = ['CELLNAME', 'ta_', 'distance_', 'overshooter', 'overs_intensity'] overshooters_df = merged_df[l].drop_duplicates() l = [ 'cellname', 'ta_calculated', 'average_distance', 'overshooter', 'intensity' ] overshooters_df.columns = l overshooters_df['datetimeid'] = cells_df.iloc[0]['Dateid'] # ------------------------------------------------ # return overshooters_df, intensity_df, merged_df return overshooters_df
def get_engine(): logger.debug(f'get_engine:') return create_engine(LOCAL_DB_STR_CONNECTION, echo=ECHO)
from ret.config.settings import ( ENV, CLIENT_ID, KAFKA_BROKER_URL, MML_TOPIC, RST_TOPIC, ) from ret.utilities.trx_updater import trx_updater producer = KafkaProducer( bootstrap_servers=[KAFKA_BROKER_URL], value_serializer=lambda msg: json.dumps(msg).encode( 'utf-8'), # we serialize our data to json for efficent transfer ) logger.debug(f"MML_TOPIC {MML_TOPIC} RST_TOPIC {RST_TOPIC}") consumer = KafkaConsumer( RST_TOPIC, bootstrap_servers=[KAFKA_BROKER_URL], auto_offset_reset='latest', # where to start reading the messages at enable_auto_commit=True, #group_id='event-collector-group-2', # consumer group id value_deserializer=lambda m: json.loads(m.decode( 'utf-8')) # we deserialize our data from json ) def nbi_processor(time_=None, session_=None, trxs_=None): ''' Esta función recibe el query (trxs_) con todas las transacciones a ejecutar.
def main(): for time_ in giver_of_times(): dict_ = average_kpis(time_) logger.debug(f"dict_ \n{dict_}")
def read_yaml(file_path): logger.debug(f'read_yaml:') with open(file_path, "r") as f: return yaml.safe_load(f)