def getDailySituationMailn():
sqlConn = jan_sqlite.create_connection(currPath + "/data.db")
with sqlConn:
# data = jan_sqlite.get_data_all(sqlConn,'data')
data = jan_sqlite.run_query(
sqlConn, "SELECT * FROM data WHERE is_complete = '0'")
dataList = []
for a in data:
d = {
"id": a[0],
"day": a[1],
"is_complete": a[2],
"filling": a[3],
"sex": a[4],
"a_init": a[5],
"red_day": a[6],
"bs": a[7],
"additional": a[8]
}
dataList.append(d)
returnObj = {"data": dataList}
return returnObj
def notifyByEmail(startDate):
html = '<html><head></head><body><h2>Nove ponudbe</h2></br>'
html += '<table border="1"><thead><tr><td>Naziv</td><td>Cena</td><td>Link</td></tr></thead><tbody>'
sqlConn = jan_sqlite.create_connection(currPath + "/avtoNet.db")
with sqlConn:
results = jan_sqlite.run_query(
sqlConn,
"SELECT * FROM cars WHERE created_on > '" + startDate + "'")
for result in results:
avtoNetId = result[2]
avtoNetId = str(avtoNetId)
title = result[3]
latestPrice = getLatestPrice(avtoNetId)
avtoNet = jan_avtoNet.AvtoNet()
url = avtoNet.createLink(avtoNetId)
html += '<tr><td>' + title + '</td><td>' + str(
latestPrice) + '</td><td>' + url + '</td></tr>'
html += '</tbody></table><h2>Spremembe cen</h2>'
html += '<table border="1"><thead><tr><td>Naziv</td><td>Nova cena</td><td>Cena</td><td>Link</td></tr></thead><tbody>'
with sqlConn:
results = jan_sqlite.run_query(
sqlConn,
"SELECT * FROM cars WHERE price_change_on > '" + startDate + "'")
for result in results:
avtoNetId = result[2]
avtoNetId = str(avtoNetId)
title = result[3]
latestPrice = getLatestPrice(avtoNetId)
results = jan_sqlite.run_query(
sqlConn, "SELECT * FROM prices WHERE avto_net_id = '" + avtoNetId +
"' ORDER BY created_on")
beforeLatestPrice = results[1][3]
avtoNet = jan_avtoNet.AvtoNet()
url = avtoNet.createLink(avtoNetId)
html += '<tr><td>' + title + '</td><td>' + str(
latestPrice) + '</td><td>' + str(
beforeLatestPrice) + '</td><td>' + url + '</td></tr>'
html += '</body></html>'
email = jan_email.Email()
email.setEmailAsHtml('*****@*****.**', 'Avto.Net API - NOVE PONUDBE',
html)
def getLatestPrice(id):
sqlConn = jan_sqlite.create_connection(currPath + "/avtoNet.db")
with sqlConn:
results = jan_sqlite.run_query(
sqlConn, "SELECT * FROM prices WHERE avto_net_id = '" + id +
"' ORDER BY created_on")
return results[0][3]
def updateDailySituation(id, values):
# date format -> 2019-12-30 08:30:40
sqlConn = jan_sqlite.create_connection(currPath + "/data.db")
with sqlConn:
res = jan_sqlite.update_data_daily_situation(sqlConn, 'data', id,
values)
return res
def checkForNotify():
sqlConn = jan_sqlite.create_connection(currPath + "/data.db")
with sqlConn:
data = jan_sqlite.run_query(
sqlConn, "SELECT * FROM data WHERE is_complete = '0'")
if len(data) > 3:
# send email
email = jan_email.Email()
email.sentEmail("*****@*****.**", "Daily Situation API",
"Vnesi podatke za zadnje dni! www.cvek.eu:7777")
def addNewDailySituationToday():
today = date.today()
dt_string = today.strftime("%Y-%m-%d")
sqlConn = jan_sqlite.create_connection(currPath + "/data.db")
# preveri ali za ta dan že obstaja zapis '2020-03-05'
with sqlConn:
data = jan_sqlite.run_query(
sqlConn, "SELECT * FROM data WHERE data.day = '" + dt_string + "'")
if len(data) > 0:
print("zapis za ta dan že obstaja. Zapis ni bil dodan")
return
params = "day,is_complete,filling,sex,a_init,red_day,bs,additional"
values = (dt_string, '0', '0', '0', '0', '0', '0', '')
res = jan_sqlite.insert_data(sqlConn, 'data', params, values)
def do_GET(self):
print(self.path)
try:
# if self.path.endswith('.html'):
if self.path.endswith('/'):
print("v defaultnem pathu")
self.path = "index.html"
print(curdir + sep +self.path)
f = open(curdir + sep +self.path) #open requested file
# print(os.getcwd())
# f = open("index.html")
#send code 200 response
self.send_response(200)
#send header first
self.send_header('Content-type','text-html')
self.end_headers()
#send file content to client
fileData = f.read()
self.wfile.write(fileData.encode('utf-8'))
f.close()
return
elif self.path.find('getData') > -1:
#send code 200 response
self.send_response(200)
#send header first
self.send_header('Content-type', 'application/json')
self.end_headers()
resDate = daily_situation.getDailySituationMailn()
jsonData = json.dumps(resDate)
mBin = jsonData.encode('utf-8')
self.wfile.write(mBin)
# elif self.path.endswith('/saveData'):
elif self.path.find('saveData') > -1:
print("v saveData pathu")
print(self.path.find('saveData'))
print(self.path)
self.send_response(200)
self.send_header('Content-type', 'application/json')
self.end_headers()
query_parameters = parse_qs(urlparse(self.path).query)
id = None
filling = ''
sex = ''
a_init = ''
red_day = ''
bs = ''
additional = ''
if 'id' in query_parameters:
id = query_parameters["id"][0]
else:
print("parameter id je nujen!")
mStr = json.dumps({"error": "NI ID PODATKA"})
mBin = mStr.encode('utf-8')
self.wfile.write(mBin)
return
if 'filling' in query_parameters:
filling = query_parameters["filling"][0] #return -> ['21'] so ve need to add [0]
if 'sex' in query_parameters:
sex = query_parameters["sex"][0]
if 'a_init' in query_parameters:
a_init = query_parameters["a_init"][0]
if 'red_day' in query_parameters:
red_day = query_parameters["red_day"][0]
if 'bs' in query_parameters:
bs = query_parameters["bs"][0]
if 'additional' in query_parameters:
additional = query_parameters["additional"][0]
sqlConn = jan_sqlite.create_connection(currPath+"/data.db")
# values = ('1','MASA',str(cobissMasa.status.name),cobissMasa.minDays,cobissMasa.error)
values = (int(filling), int(sex), int(a_init), int(red_day), int(bs), additional)
res = daily_situation.updateDailySituation(id, values)
mStr = json.dumps({"status": "OK"})
mBin = mStr.encode('utf-8')
self.wfile.write(mBin)
return
elif self.path.endswith(".js"):
f = open(currPath + sep +self.path, 'rb')
#send code 200 response
self.send_response(200)
#send header first
self.send_header('Content-type','application/javascript')
self.end_headers()
fileData = f.read()
self.wfile.write(fileData)
f.close()
return
except IOError:
self.send_error(404, 'file not found')
def searchNewCars():
searches = (
Url(
'Volkswagen', 'Passat',
'https://www.avto.net/Ads/results.asp?znamka=Volkswagen&model=Passat&modelID=&tip=&znamka2=&model2=&tip2=&znamka3=&model3=&tip3=&cenaMin=9000&cenaMax=14000&letnikMin=2015&letnikMax=2090&bencin=202&starost2=999&oblika=13&ccmMin=1800&ccmMax=99999&mocMin=&mocMax=&kmMin=0&kmMax=170000&kwMin=0&kwMax=999&motortakt=&motorvalji=&lokacija=0&sirina=&dolzina=&dolzinaMIN=&dolzinaMAX=&nosilnostMIN=&nosilnostMAX=&lezisc=&presek=&premer=&col=&vijakov=&EToznaka=&vozilo=&airbag=&barva=&barvaint=&EQ1=1000000000&EQ2=1000000000&EQ3=1002000000&EQ4=100000000&EQ5=1000000000&EQ6=1000000000&EQ7=1000100020&EQ8=1010000001&EQ9=100000000&KAT=1010000000&PIA=&PIAzero=&PSLO=&akcija=&paketgarancije=0&broker=&prikazkategorije=&kategorija=&zaloga=10&arhiv=&presort=&tipsort=&stran='
),
Url(
'Skoda', 'Octavia',
'https://www.avto.net/Ads/results.asp?znamka=%8Akoda&model=Octavia&modelID=&tip=&znamka2=&model2=&tip2=&znamka3=&model3=&tip3=&cenaMin=9000&cenaMax=14000&letnikMin=2015&letnikMax=2090&bencin=202&starost2=999&oblika=13&ccmMin=1800&ccmMax=99999&mocMin=&mocMax=&kmMin=0&kmMax=170000&kwMin=0&kwMax=999&motortakt=&motorvalji=&lokacija=0&sirina=&dolzina=&dolzinaMIN=&dolzinaMAX=&nosilnostMIN=&nosilnostMAX=&lezisc=&presek=&premer=&col=&vijakov=&EToznaka=&vozilo=&airbag=&barva=&barvaint=&EQ1=1000000000&EQ2=1000000000&EQ3=1002000000&EQ4=100000000&EQ5=1000000000&EQ6=1000000000&EQ7=1000100020&EQ8=1010000001&EQ9=100000000&KAT=1010000000&PIA=&PIAzero=&PSLO=&akcija=&paketgarancije=0&broker=&prikazkategorije=&kategorija=&zaloga=10&arhiv=&presort=&tipsort=&stran='
),
Url(
'Ford', 'Mondeo',
'https://www.avto.net/Ads/results.asp?znamka=Ford&model=Mondeo&modelID=&tip=&znamka2=&model2=&tip2=&znamka3=&model3=&tip3=&cenaMin=9000&cenaMax=14000&letnikMin=2015&letnikMax=2090&bencin=202&starost2=999&oblika=13&ccmMin=1800&ccmMax=99999&mocMin=&mocMax=&kmMin=0&kmMax=170000&kwMin=0&kwMax=999&motortakt=&motorvalji=&lokacija=0&sirina=&dolzina=&dolzinaMIN=&dolzinaMAX=&nosilnostMIN=&nosilnostMAX=&lezisc=&presek=&premer=&col=&vijakov=&EToznaka=&vozilo=&airbag=&barva=&barvaint=&EQ1=1000000000&EQ2=1000000000&EQ3=1002000000&EQ4=100000000&EQ5=1000000000&EQ6=1000000000&EQ7=1000100020&EQ8=1010000001&EQ9=100000000&KAT=1010000000&PIA=&PIAzero=&PSLO=&akcija=&paketgarancije=0&broker=&prikazkategorije=&kategorija=&zaloga=10&arhiv=&presort=&tipsort=&stran='
),
Url(
'Peugeot', '508',
'https://www.avto.net/Ads/results.asp?znamka=Peugeot&model=508&modelID=&tip=&znamka2=&model2=&tip2=&znamka3=&model3=&tip3=&cenaMin=9000&cenaMax=14000&letnikMin=2015&letnikMax=2090&bencin=202&starost2=999&oblika=13&ccmMin=1800&ccmMax=99999&mocMin=&mocMax=&kmMin=0&kmMax=170000&kwMin=0&kwMax=999&motortakt=&motorvalji=&lokacija=0&sirina=&dolzina=&dolzinaMIN=&dolzinaMAX=&nosilnostMIN=&nosilnostMAX=&lezisc=&presek=&premer=&col=&vijakov=&EToznaka=&vozilo=&airbag=&barva=&barvaint=&EQ1=1000000000&EQ2=1000000000&EQ3=1002000000&EQ4=100000000&EQ5=1000000000&EQ6=1000000000&EQ7=1000100020&EQ8=1010000001&EQ9=100000000&KAT=1010000000&PIA=&PIAzero=&PSLO=&akcija=&paketgarancije=0&broker=&prikazkategorije=&kategorija=&zaloga=10&arhiv=&presort=&tipsort=&stran='
))
for search in searches:
avtoNet = jan_avtoNet.AvtoNet()
avtoNet.runSearchByUrl(search.url)
time.sleep(3) # Delays for 10 seconds. You can also use a float value.
avtoNet.getOffers()
offers = avtoNet._offers
for offer in offers:
id = offer.id
price = offer.cena
sqlConn = jan_sqlite.create_connection(currPath + "/avtoNet.db")
with sqlConn:
existing = jan_sqlite.run_query(
sqlConn,
"SELECT * FROM cars WHERE avto_net_id = '" + id + "'")
if len(existing) > 0:
# AVTO ŽE OBSTAJA
carPrices = jan_sqlite.run_query(
sqlConn, "SELECT * FROM prices WHERE avto_net_id = '" +
id + "' ORDER BY created_on")
if not isSamePrice(price, carPrices):
# CENA SE JE SPREMENILA
# za spremljanje sprememb cen imamo na cars tabeli parameter price_change_on
today = datetime.now()
dt_string = today.strftime("%Y-%m-%d %H:%M:%S")
jan_sqlite.run_query(
sqlConn, "UPDATE cars SET price_change_on = '" +
dt_string + "' WHERE avto_net_id='" + id + "'")
pramas2 = 'avto_net_id,price'
values2 = (str(id), int(price.replace('.', '')))
jan_sqlite.insert_data(sqlConn, 'prices', pramas2,
values2)
else:
# NOV AVTO
# pri novem avto imamo parameter created on, s katerim potem lahko dobimi nove avte
params = 'avto_net_id,title,first_registration,km,engine,transmition,dealer'
values = (id, offer.title, int(offer.prvaReq), offer.km,
offer.motor, offer.menjalnik, offer.prodajalec)
jan_sqlite.insert_data(sqlConn, 'cars', params, values)
pramas2 = 'avto_net_id,price'
values2 = (str(id), int(price.replace('.', '')))
jan_sqlite.insert_data(sqlConn, 'prices', pramas2, values2)