Created
May 30, 2019 18:08
-
-
Save instplanet/d90e9de6b647b194d2e338d9d356577e to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# -*- coding: utf-8 -*- | |
import scrapy | |
import MySQLdb | |
from scrapy.utils.response import open_in_browser | |
from datetime import datetime, timedelta | |
class LoginiclassSpider(scrapy.Spider): | |
name = 'loginIclass' | |
start_urls = ['http://fs4.iclass.com.br/iclassfs/login/login.seam'] | |
def parse(self, response): | |
# token = response.css('input[id *= token]').attrib['value'] | |
token = response.css('#login\:token').attrib['value'] | |
view_state = response.css('#javax\.faces\.ViewState').attrib['value'] | |
yield scrapy.FormRequest( | |
url='http://fs4.iclass.com.br/iclassfs/login/login.seam', | |
formdata={ | |
"login": "login", | |
"login:token": token, | |
"login:username": "VTELMARCOS", | |
"login:password": "123", | |
"login:rememberMe": "on", | |
"login:id_login": "Entrar", | |
"javax.faces.ViewState": view_state, | |
}, | |
callback=self.request_fill_report_params, | |
method='POST' | |
) | |
def request_fill_report_params(self, response): | |
today = datetime.now() | |
yesterday = datetime.now() - timedelta(days=1) | |
# start_date = datetime(yesterday.year, yesterday.month, yesterday.day, 0, 0, 0).timestamp()).replace('.', ') | |
# end_date = datetime(today.year, today.month, today.day, 0, 0, 0).timestamp()).replace('.', ') | |
start_date = '1559012400000' | |
end_date = '1559098800000' | |
url = """http://fs4.iclass.com.br/iclassfs/relatorio?tipo=html&rel=DETALHEOSCONCLUIDA&visao=Armazem&timeZoneCliente=America/Recife&locale=pt_BR&operadoraId=2121&credenciadaId=755&dataInicio={start_date}&dataFim={end_date}&statusSlaId=&userId=2607&dataSolicitacaoInicio=&dataSolicitacaoFim=&dataCriacaoInicio=&dataCriacaoFim=&gstClienteId=1485&mostraAtivoNoAmbiente=false""".format(start_date=start_date, end_date=end_date) | |
yield scrapy.Request(url=url, callback=self.get_report_date) | |
def get_report_date(self, response): | |
db = MySQLdb.connect(host="127.0.0.1", | |
user="root", | |
passwd="123", | |
db="iclass") | |
cursor = db.cursor() | |
# body > table: nth - child(2) | |
tables = response.css('table') | |
for table in tables: | |
rows = table.css('tr') | |
for row in rows[4:]: | |
if len(row.css('td').getall()) == 1 or len(row.css('td').getall()) == 3: | |
continue | |
else: | |
os, cliente, segmentacao_cliente, cluster, credenciada, cidade, bairro, microarea, comentario, dt_solicitacao, tecnico, tipo_os, motivo, zona, dt_agendamento, dt_max_atend, dt_encerramento, inicio_desloc, fim_desloc, km_percorrido, tempo_em_visita, entrada, saida, obs_eqp, criado_por, coordenadas = row.css('td') | |
sql = """ | |
INSERT INTO `iclass`.`iclassOs`( | |
`cliente`, | |
`segmentacao_cliente`, | |
`cluster`, | |
`credenciada`, | |
`cidade`, | |
`bairro`, | |
`microarea`, | |
`comentario`, | |
`data_solicitacao`, | |
`tecnico`, | |
`tipo_os`, | |
`motivo`, | |
`zona`, | |
`data_agendamento`, | |
`data_maxima_atendimento`, | |
`data_encerramento`, | |
`inicio_deslocamento`, | |
`fim_deslocamento`, | |
`km_percorrido`, | |
`tempo_em_visita`, | |
`entrada`, | |
`saida`, | |
`obs_eqp`, | |
`criado_por`, | |
`coordenada` | |
) VALUES ( | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s, | |
%s | |
); | |
""" | |
# self.log(sql) | |
result = cursor.execute(sql, | |
( | |
cliente.css('span::text').get(), | |
segmentacao_cliente.css('span::text').get(), | |
cluster.css('span::text').get(), | |
credenciada.css('span::text').get(), | |
cidade.css('span::text').get(), | |
bairro.css('span::text').get(), | |
microarea.css('span::text').get(), | |
comentario.css('span::text').get(), | |
dt_solicitacao.css('span::text').get(), | |
tecnico.css('span::text').get(), | |
tipo_os.css('span::text').get(), | |
motivo.css('span::text').get(), | |
zona.css('span::text').get(), | |
dt_agendamento.css('span::text').get(), | |
dt_max_atend.css('span::text').get(), | |
dt_encerramento.css('span::text').get(), | |
inicio_desloc.css('span::text').get(), | |
fim_desloc.css('span::text').get(), | |
km_percorrido.css('span::text').get(), | |
tempo_em_visita.css('span::text').get(), | |
entrada.css('span::text').get(), | |
saida.css('span::text').get(), | |
obs_eqp.css('span::text').get(), | |
criado_por.css('span::text').get(), | |
coordenadas.css('span::text').get(), | |
) | |
) | |
self.log(result) | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment