def Orgs_domains(self):
if len(self.orgs_table) == 0:
print("No se encontraron registros nuevos de organizaciones")
else:
tabla = self.orgs_table
tabla = tabla[["domain_names","id"]]
final_table = pd.DataFrame()
for index, row in tabla.iterrows():
if len(row['domain_names']) == 0:
pass
else:
id = row['id']
tags = row['domain_names']
inter_table = pd.DataFrame(tags, columns= ['domain_names'])
inter_table['id'] = [id for i in range(len(inter_table)) ]
final_table = final_table.append(inter_table)
try:
final_table.reset_index(inplace= True,drop= True )
except:
pass
if len(final_table) == 0:
self.orgs_table = final_table
else:
if self.tipo == "complete":
# Borra la tabla anterior e inicializa una nueva con solo un ID, posteriormente comprueba las nuevas columnas
# para insertarlas.
Initialize("orgs_domains", self.engine)
New_columns(final_table, "orgs_domains", self.engine)
Upload_Redshift(final_table,"orgs_domains", "zendesk_support","zendesk-runahr",self.engine)
if self.tipo == "partial":
New_columns(final_table, "orgs_domains", self.engine)
Upload_Redshift(final_table,"orgs_domains", "zendesk_support","zendesk-runahr",self.engine)
self.orgs_domains =final_table
return self
def Users_tags(self):
tabla = self.users_table
print(tabla["tags"])
column_list = clean.column_list(self.users_table)
tabla = tabla[column_list + ["id"]]
tabla = pd.concat([pd.DataFrame({"tags":tabla['tags']}),pd.DataFrame({"id":tabla['id']})], axis = 1)
final_table = pd.DataFrame()
for index, row in tabla.iterrows():
if len(row['tags']) == 0:
pass
else:
id = row['id']
tags = row['tags']
inter_table = pd.DataFrame(tags, columns= ['tags'])
inter_table['id'] = [id for i in range(len(inter_table)) ]
final_table = final_table.append(inter_table)
try:
final_table.reset_index(inplace= True,drop= True )
except:
pass
if len(final_table) == 0:
self.users_tags = final_table
else:
if self.tipo == "complete":
# Borra la tabla anterior e inicializa una nueva con solo un ID, posteriormente comprueba las nuevas columnas
# para insertarlas.
Initialize("user_tags", self.engine)
New_columns(final_table, "user_tags", self.engine)
Upload_Redshift(final_table,"user_tags", "zendesk_support","zendesk-runahr",self.engine)
if self.tipo == "partial":
New_columns(final_table, "user_tags", self.engine)
Upload_Redshift(final_table,"user_tags", "zendesk_support","zendesk-runahr",self.engine)
self.user_tags =final_table
def tag_history(self):
if self.tipo == "complete":
# Borra la tabla anterior e inicializa una nueva con solo un ID, posteriormente comprueba las nuevas columnas
# para insertarlas.
Initialize("tag_history", self.engine)
New_columns(self.tabla_tag_history, "tag_history", self.engine)
Upload_Redshift(self.tabla_tag_history,"tag_history", "zendesk_support","zendesk-runahr",self.engine)
if self.tipo == "partial":
New_columns(self.tabla_tag_history, "tag_history", self.engine)
Upload_Redshift(self.tabla_tag_history,"tag_history", "zendesk_support","zendesk-runahr",self.engine)
def Tickets(self):
tabla = self.tickets_table
column_list = clean.column_list(self.tickets_table)
tabla = tabla.drop([str(i) for i in column_list], axis = 1)
if self.tipo == "complete":
# Borra la tabla anterior e inicializa una nueva con solo un ID, posteriormente comprueba las nuevas columnas
# para insertarlas.
Initialize("tickets", self.engine)
New_columns(tabla, "tickets", self.engine)
Upload_Redshift(tabla,"tickets", "zendesk_support","zendesk-runahr",self.engine)
if self.tipo == "partial":
New_columns(tabla, "tickets", self.engine)
Upload_Redshift(tabla,"tickets", "zendesk_support","zendesk-runahr",self.engine)
def field_history(self):
"""
La tabla de field_history no incluye a los tickets que esten archivados.
"""
if self.tipo == "complete":
# Borra la tabla anterior e inicializa una nueva con solo un ID, posteriormente comprueba las nuevas columnas
# para insertarlas.
Initialize("field_history", self.engine)
New_columns(self.tabla_field_history, "field_history", self.engine)
Upload_Redshift(self.tabla_field_history,"field_history", "zendesk_support","zendesk-runahr",self.engine)
if self.tipo == "partial":
New_columns(self.tabla_field_history, "field_history", self.engine)
Upload_Redshift(self.tabla_field_history,"field_history", "zendesk_support","zendesk-runahr",self.engine)
return self
def Orgs(self):
if len(self.orgs_table) == 0:
print("No se encontraron registros nuevos de organizaciones")
else:
tabla = self.orgs_table
column_list = clean.column_list(self.orgs_table)
tabla = tabla.drop([str(i) for i in column_list], axis = 1)
if self.tipo == "complete":
# Borra la tabla anterior e inicializa una nueva con solo un ID, posteriormente comprueba las nuevas columnas
# para insertarlas.
Initialize("orgs", self.engine)
New_columns(tabla, "orgs", self.engine)
Upload_Redshift(tabla,"orgs", "zendesk_support","zendesk-runahr",self.engine)
if self.tipo == "partial":
New_columns(tabla, "orgs", self.engine)
Upload_Redshift(tabla,"orgs", "zendesk_support","zendesk-runahr",self.engine)
def extract_groups(self):
self.groups_url = "https://runahr.zendesk.com/api/v2/groups.json"
response = requests.get(self.groups_url, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
tabla = []
for i in response.json()["groups"]:
tabla.append(i)
tabla = clean.fix_columns(pd.DataFrame(tabla))
self.table_groups = tabla
Initialize("groups", self.engine)
New_columns(self.table_groups, "groups", self.engine)
Upload_Redshift(self.table_groups,"groups", "zendesk_support","zendesk-runahr",self.engine)
return self
def __tickets_extract(self):
"""
Extraccion completa de Tickets a traves del endpoint incremental.
Retoma los valores pasados como argumentos en la instancia.
Fecha: Valor en timestamp desde la cual se hara la extraccion.
tipo :
- complete: Extraccion de la totalidad de los tickets desde el primero de enero de 2018.
- partial: se tomara el valor de la fecha de entrada para hacer la extraccion.
"""
self.incremental = "https://runahr.zendesk.com/api/v2/incremental/tickets.json"
def extract_custom_fields():
self.custom_fields_url = "https://runahr.zendesk.com/api/v2/ticket_fields.json"
respuesta = requests.get(self.custom_fields_url, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
data = respuesta.json()
fields = data["ticket_fields"]
dic = {}
for i in fields:
dic.update({i["id"]:
{
"Name":i["raw_title_in_portal"],
"Description":i["description"],
"Raw Description": i["raw_description"],
"Created_at": i["created_at"],
"removable": i["removable"] # if removable == False entonces es uncampo de sistema.
}
})
return dic
def add_field(tickets_table):
dic = {}
for ticket, fields in zip(tickets_table.id, tickets_table.custom_fields):
for field in fields:
if ticket not in dic:
dic.update(
{ticket:
{
field["id"]:
{
"value": field["value"],
"name":self.dic_fields[field["id"]]["Name"]
}
}
})
else:
dic[ticket].update(
{
field["id"]:
{
"value": field["value"],
"name":self.dic_fields[field["id"]]["Name"]
}
}
)
tabla = pd.DataFrame.from_dict(dic).T
for column in tabla.columns:
nombre = "Custom_" + str(tabla[column].iloc[0]["name"])
tabla = tabla.rename(columns = {column: nombre})
aux = []
for record in tabla[nombre]:
aux.append(record["value"])
tabla[nombre] = aux
tabla.reset_index(inplace= True)
tabla = tabla.rename(columns = {"index": "ticket_id"})
tabla = tabla.merge(tickets_table, left_on = "ticket_id", right_on= "id")
return tabla
# Creacion de tabla catalogo de los custom fields siempre se actualizara por completo
tickets = []
if self.tipo == "complete":
fecha = int(datetime.datetime.strptime("2018-01-01","%Y-%m-%d").timestamp())
response = requests.get(self.incremental + str("?start_time=")+ str(fecha), auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
if response.status_code != 200:
print("Error en la extraccion. CodeError: "+ str(response.status_code))
data = response.json()
tickets.extend(data['tickets'])
url = data['next_page']
if self.tipo == "partial":
fecha = int(datetime.datetime.strptime(self.fecha, "%Y-%m-%d").timestamp())
url = self.incremental + str("?start_time=")+ str(fecha)
response = requests.get(url, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
if response.status_code != 200:
print("Error en la extraccion. CodeError: "+ str(response.status_code))
data = response.json()
tickets.extend(data['tickets'])
url = data['next_page']
while url:
response = requests.get(url, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
time.sleep(4)
if response.status_code != 200:
print("Error en la extraccion. CodeError: "+ str(response.status_code))
data = response.json()
tickets.extend(data['tickets'])
if url == data['next_page']:
break
print("Numero de tickets extraidos: {}".format(len(tickets)))
url = data["next_page"]
tabla = pd.io.json.json_normalize(tickets)
self.dic_fields = extract_custom_fields()
tabla = add_field(tabla)
tabla = clean.fix_columns(tabla)
self.tickets_table = tabla
print("Comienza Extraccion de Custom Fields")
tabla = []
for i in self.dic_fields.keys():
aux = self.dic_fields[i]
tabla.append(aux)
tabla = pd.DataFrame(tabla)
tabla["id"] = [i for i in self.dic_fields.keys()]
tabla = clean.fix_columns(tabla)
self.fields_table = tabla
if self.tipo == "complete":
Initialize("custom_fields", self.engine)
New_columns(tabla, "custom_fields", self.engine)
Upload_Redshift(tabla,"custom_fields", "zendesk_support","zendesk-runahr",self.engine)
if self.tipo == "partial":
New_columns(tabla, "custom_fields", self.engine)
Upload_Redshift(tabla,"custom_fields", "zendesk_support","zendesk-runahr",self.engine)
print("Termino extraccion de custom fields")