def __users_extract_partial(self):
"""
No se puede usar el incremental export porque todos los usuarios estan siendo
updated conforme a sus valores en Salesforce.
Para utilizar el enpoint incremental sera necesario modificar el flujo de trabajo de
la actualizacion del user type para que solo actualice aquellos usuarios que
tengan algun cambio en sus valores.
"""
users =[]
# fecha = int(datetime.datetime.strptime(self.fecha, "%Y-%m-%d").timestamp())
# url = self.incremental_users + str("?start_time=")+ str(fecha)
# response = requests.get(url, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
query = "type:user created>={}".format(self.fecha)
response = requests.get(self.search_url + query, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
data = response.json()
users.extend(data["results"])
url = data["next_page"]
print(url)
while url:
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()
users.extend(data['results'])
if url == data['next_page']:
break
print("Numero de usuarios extraidos: {}".format(len(users)))
url = data["next_page"]
print(url)
tabla = pd.io.json.json_normalize(users)
tabla = clean.fix_columns(tabla)
self.users_table = tabla
return self
def __extract_tag_history(self):
self.tag_history_url = "https://runahr.zendesk.com/api/v2/ticket_audits.json"
response = requests.get(self.tag_history_url, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
data = response.json()
next_url = data["before_url"]
tags = []
tags.append(data["audits"])
counter = 0
while next_url != None and counter < 1:
response = requests.get(next_url, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
data = response.json()
tags.append(data["audits"])
next_url = data["before_url"]
print(len(tags)) #deprecate
counter += 1
tabla = pd.DataFrame()
for request in tags:
for audit in request:
id = audit["ticket_id"]
created_at = audit["created_at"]
for evento in audit["events"]:
if evento["type"] == "Change" and evento["field_name"] == "tags":
aux = pd.DataFrame(evento["value"])
aux["id"] = id
aux["updated"] = created_at
tabla = tabla.append(aux)
tabla.rename(columns = {0: "tag"}, inplace = True)
tabla = tabla.reset_index(drop = True)
tabla = clean.fix_columns(tabla)
self.tabla_tag_history = tabla
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.
"""
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) + str(
"&include=comment_events")
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"]))
if response.status_code == 429:
print(url)
print(response.json())
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)
tabla = clean.fix_columns(tabla)
self.tickets_table = tabla
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 __extract_field_options(self):
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"]
tabla = []
for field in fields:
if "custom_field_options" in field.keys():
data = field["custom_field_options"]
for entry in data:
tabla.append(entry)
tabla = pd.DataFrame(tabla)
tabla = clean.fix_columns(tabla)
self.table_fields_options = tabla
def __users_extract_complete(self):
users = []
self.users_url = "https://runahr.zendesk.com/api/v2/users.json"
response = requests.get(self.users_url, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
data = response.json()
users.extend(data['users'])
url = data['next_page']
while url:
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()
users.extend(data['users'])
if url == data['next_page']:
break
print("Numero de usuarios extraidos: {}".format(len(users)))
url = data["next_page"]
tabla = pd.io.json.json_normalize(users)
tabla = clean.fix_columns(tabla)
self.users_table = tabla
def __extract_tag_history_partial(self, fecha, ids):
tabla = pd.DataFrame()
for ticket in ids:
url = "https://runahr.zendesk.com/api/v2/tickets/{}/audits.json".format(str(ticket))
response = requests.get(url, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
data = response.json()
for audit in data["audits"]:
id = audit["ticket_id"]
created_at = audit["created_at"]
for evento in audit["events"]:
if evento["type"] == "Change" and evento["field_name"] == "tags":
aux = pd.DataFrame(evento["value"])
aux["id"] = id
aux["updated"] = created_at
tabla = tabla.append(aux)
print("Audists del ticket: ", str(ticket))
tabla.rename(columns = {0: "tag"}, inplace = True)
tabla = tabla.reset_index(drop = True)
tabla = clean.fix_columns(tabla)
self.tabla_tag_history = tabla
def __orgs_extract_partial(self):
query = "type:organization created>={}".format(self.fecha)
orgs = []
response = requests.get(self.search_url + query, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
data = response.json()
orgs.extend(data["results"])
url = data["next_page"]
while url:
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()
orgs.extend(data['results'])
if url == data['next_page']:
break
print("Numero de organizaciones extraidas: {}".format(len(orgs)))
url = data["next_page"]
tabla = pd.io.json.json_normalize(orgs)
tabla = clean.fix_columns(tabla)
self.orgs_table = tabla
return self
def __orgs_extract(self):
"""
Se incluye la opcion de count
"""
self.orgs_url = "https://runahr.zendesk.com/api/v2/organizations.json"
response = requests.get(self.orgs_url, auth = (os.environ["ZENDESK_USER"], os.environ["ZENDESK_PASSWORD"]))
count = response.json()['count']
data = response.json()
orgs = []
orgs.extend(data['organizations'])
url = data['next_page']
while url:
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()
orgs.extend(data['organizations'])
if url == data['next_page']:
break
print("Numero de organizaciones extraidas: {}".format(len(orgs)))
url = data["next_page"]
tabla = pd.io.json.json_normalize(orgs)
tabla = clean.fix_columns(tabla)
self.orgs_table = tabla
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")