def get_type_name(typeid):
cursor, conn = dbcon.connect()
cursor.execute("SELECT typename FROM types WHERE typeid=%d" % (typeid))
name = cursor.fetchone()
if name is None:
return "No Name Found: TypeID " + str(typeid)
return name[0]
def get_eq_onewire(self):
db = dbcon.connect()
cursor = db.cursor(cursor_factory=extras.DictCursor)
onewire = {}
cursor.execute("SELECT room, location, addr, location, pin, power FROM"
" equipment WHERE id=%s and type=%s",
(self.unit['id'], 'OneWire'))
for index, record in enumerate(cursor):
onewire[index] = {
'room': record['room'],
'location': record['location'],
'addr': record['addr'],
'pin': record['pin'],
'power': record['power'],
'temp': None,
'lastupdate': None,
}
cursor.close()
db.close()
return onewire
def get_avg_station_quantity(typeid, regionid):
#return None
cursor, conn = dbcon.connect()
cursor.execute(
"SELECT AVG(quantity) FROM history WHERE typeid=%d AND regionid=%d" %
(typeid, regionid))
return cursor.fetchone()[0]
def get_type_name(typeid):
cursor, conn = dbcon.connect()
cursor.execute("SELECT typename FROM types WHERE typeid=%d" % (typeid))
name = cursor.fetchone()
if name is None:
return "No Name Found: TypeID " + str(typeid)
return name[0]
def get_type_name_no_blueprint(typeid):
cursor, conn = dbcon.connect()
cursor.execute("SELECT typename FROM types WHERE typeid=%d AND typename NOT LIKE '%%Blueprint'" % (typeid))
name = cursor.fetchone()
if name is None:
return None
return name[0]
def get_eq_lcd(self):
db = dbcon.connect()
cursor = db.cursor(cursor_factory=extras.DictCursor)
lcd = {}
cursor.execute("SELECT room, location, addr, pin FROM equipment"
" WHERE id=%s and type=%s", (self.unit['id'], 'Lcd'))
for index, record in enumerate(cursor):
lcd[index] = {
'room': record['room'],
'location': record['location'],
'addr': record['addr'],
'backlightPin': record['pin'],
'backlightState': False,
'pwmState': 0,
}
cursor.close()
db.close()
return lcd
def get_station_trades(stationid, min_margin=0.15):
start = datetime.now()
trades = {}
stationid = long(stationid)
cursor, conn = dbcon.connect()
#cursor.execute("SELECT DISTINCT typeid FROM orders WHERE stationid=%d AND bid = 0" % (stationid))
cursor.execute(
"SELECT typeid, price FROM (SELECT * FROM orders WHERE stationid=%d\
AND bid=0 ORDER BY typeid, price asc) price GROUP BY typeid" %
(stationid))
types_sell = dict(cursor.fetchall())
#cursor.execute("SELECT DISTINCT typeid FROM orders WHERE stationid=%d AND bid = 1" % (stationid))
cursor.execute(
"SELECT typeid, price FROM (SELECT * FROM orders WHERE stationid=%d\
AND bid=1 ORDER BY typeid, price desc) price GROUP BY typeid" %
(stationid))
types_buy = dict(cursor.fetchall())
typeids = [key for key in types_sell if key in types_buy]
#print [key for key in types_sell]
#types = dict((key, value) for key, value in types_sell if key in types_buy)
for typeid in typeids:
#types = [typeid[0] for typeid in types]
#for typeid in types:
name = get_type_name_no_blueprint(typeid)
margin = get_margin_values([types_sell[typeid], types_buy[typeid]])
quantity = get_avg_station_quantity(typeid, get_region(stationid))
if margin is None or margin < min_margin or name is None:
continue
else:
trades[name] = [typeid, format_percentage(margin), quantity]
#trades[typeid]=[format_percentage(margin)]
return trades
def get_station_trades(stationid, min_margin=0.15):
start = datetime.now()
trades = {}
stationid = long(stationid)
cursor, conn = dbcon.connect()
#cursor.execute("SELECT DISTINCT typeid FROM orders WHERE stationid=%d AND bid = 0" % (stationid))
cursor.execute("SELECT typeid, price FROM (SELECT * FROM orders WHERE stationid=%d\
AND bid=0 ORDER BY typeid, price asc) price GROUP BY typeid" % (stationid))
types_sell = dict(cursor.fetchall())
#cursor.execute("SELECT DISTINCT typeid FROM orders WHERE stationid=%d AND bid = 1" % (stationid))
cursor.execute("SELECT typeid, price FROM (SELECT * FROM orders WHERE stationid=%d\
AND bid=1 ORDER BY typeid, price desc) price GROUP BY typeid" % (stationid))
types_buy = dict(cursor.fetchall())
typeids = [key for key in types_sell if key in types_buy]
#print [key for key in types_sell]
#types = dict((key, value) for key, value in types_sell if key in types_buy)
for typeid in typeids:
#types = [typeid[0] for typeid in types]
#for typeid in types:
name = get_type_name_no_blueprint(typeid)
margin = get_margin_values([types_sell[typeid], types_buy[typeid]])
quantity = get_avg_station_quantity(typeid, get_region(stationid))
if margin is None or margin < min_margin or name is None:
continue
else:
trades[name]=[typeid, format_percentage(margin), quantity]
#trades[typeid]=[format_percentage(margin)]
return trades
def get_type_name_no_blueprint(typeid):
cursor, conn = dbcon.connect()
cursor.execute(
"SELECT typename FROM types WHERE typeid=%d AND typename NOT LIKE '%%Blueprint'"
% (typeid))
name = cursor.fetchone()
if name is None:
return None
return name[0]
def get_price_station(typeid, stationid):
prices = []
cursor, conn = dbcon.connect()
cursor.execute("SELECT MIN(price) FROM orders WHERE typeid=%d AND\
stationid=%d AND bid=0" % (int(typeid), int(stationid)))
prices.append(cursor.fetchone()[0])
cursor.execute("SELECT MAX(price) FROM orders WHERE typeid=%d AND\
stationid=%d AND bid=1" % (int(typeid), int(stationid)))
prices.append(cursor.fetchone()[0])
conn.close()
return prices
def get_price_station(typeid, stationid):
prices = []
cursor, conn = dbcon.connect()
cursor.execute("SELECT MIN(price) FROM orders WHERE typeid=%d AND\
stationid=%d AND bid=0" % (int(typeid), int(stationid)))
prices.append(cursor.fetchone()[0])
cursor.execute("SELECT MAX(price) FROM orders WHERE typeid=%d AND\
stationid=%d AND bid=1" % (int(typeid), int(stationid)))
prices.append(cursor.fetchone()[0])
conn.close()
return prices
def allid(cls):
result = []
db = dbcon.connect()
cursor = db.cursor()
cursor.execute("SELECT id FROM units")
for record in cursor:
result.append(cls.fromid(record[0]))
cursor.close()
db.close()
return result
def get_eq_servo(self):
db = dbcon.connect()
cursor = db.cursor(cursor_factory=extras.DictCursor)
servo = {}
cursor.execute("SELECT room, location, pin FROM equipment WHERE"
" id=%s and type=%s ", (self.unit['id'], 'Servo', ))
for index, record in enumerate(cursor):
servo[index] = {
'room': record['room'],
'location': record['location'],
'pin': record['pin'],
}
cursor.close()
db.close()
return servo
def get_eq_arduino_id(self, device):
a = {}
db = dbcon.connect()
cursor = db.cursor(cursor_factory=extras.DictCursor)
cursor.execute("SELECT * FROM units WHERE device =%s",
(device,))
for record in cursor:
a = {
'id': record['id'],
'location': record['location'],
'device': device,
}
cursor.close()
db.close()
return a
def get_eq_pir(self):
db = dbcon.connect()
cursor = db.cursor(cursor_factory=extras.DictCursor)
pir = {}
cursor.execute("SELECT room, location, addr, pin FROM equipment WHERE"
" id=%s and type=%s ", (self.unit['id'], 'Pir', ))
for index, record in enumerate(cursor):
pir[index] = {
'room': record['room'],
'location': record['location'],
'addr': record['addr'],
'pin': record['pin'],
'state': None
}
cursor.close()
db.close()
return pir
def get_region_name(regionid):
cursor, conn = dbcon.connect()
cursor.execute("SELECT regionname FROM regions WHERE regionid=%d" %
(regionid))
name = cursor.fetchone()
return name[0]
def get_region(stationid):
cursor, conn = dbcon.connect()
cursor.execute("SELECT regionid FROM systems WHERE systemid=(SELECT systemid FROM stations WHERE stationid=%d)" % (stationid))
return cursor.fetchone()[0]
def get_region_name(regionid):
cursor, conn = dbcon.connect()
cursor.execute("SELECT regionname FROM regions WHERE regionid=%d" % (regionid))
name = cursor.fetchone()
return name[0]
def get_station_name(stationid):
cursor, conn = dbcon.connect()
cursor.execute("SELECT stationname FROM stations WHERE stationid=%d" % (systemid))
name = cursor.fetchone()
return name[0]
def worker(job_json):
"""
For every incoming message, this worker function is called. Be extremely
careful not to do anything CPU-intensive here, or you will see blocking.
Sockets are async under gevent, so those are fair game.
"""
market_json = zlib.decompress(job_json)
market_data = simplejson.loads(market_json)
try:
resultType = market_data['resultType']
except KeyError as e:
log("Error getting result type")
return
if market_data['generator']['name']=='EveData.Org':
log("Ignoring evedata.org")
return
cursor, conn = dbcon.connect()
if resultType == 'orders':
insert_sql = "INSERT INTO orders\
(typeid, regionid, price, volremaining, orderrange, orderid, volentered,\
minvolume, bid, issuedate, duration, stationid, solarsystemid) VALUES"
values = "('%d', '%d', '%f', '%d', '%d', '%d', '%d', '%d', '%d', '%s', '%d', '%d', '%d'),"
delete_sql = "DELETE FROM orders WHERE regionid = %d AND typeid = %d"
try:
for rowset in market_data['rowsets']:
if len(rowset['rows']) <= 0:
continue
try:
cursor.execute(delete_sql % (rowset['regionID'], rowset['typeID']))
conn.commit()
except Error as e:
log("orders : " + delete_sql % (rowset['regionID'], rowset['typeID']))
log("MySQL Error [%d]: %s" % (e.args[0], e.args[1]))
conn.rollback()
for row in rowset['rows']:
insert_sql += values % (rowset['typeID'], rowset['regionID'], row[0], row[1], row[2], row[3], row[4], row[5], row[6], arrow.get(row[7]).datetime.strftime('%Y-%m-%d %H:%M:%S'), row[8], row[9], row[10])
try:
cursor.execute(insert_sql[:-1])
conn.commit()
except:
log("Error inserting orders, rolling back")
log(simplejson.dumps(market_data, indent=4*' '))
conn.rollback()
except KeyError as e:
log("Error parsing order rowsets")
conn.close()
return
elif resultType == 'history': #disable quickly. this is stupid
insert_sql = "INSERT INTO history (typeid, regionid, issuedate, orders, low, high, average, quantity) VALUES"
values = "('%d', '%d', '%s', '%d', '%d', '%d', '%d', '%d'),"
delete_sql = "DELETE FROM history WHERE regionid = %d AND typeid = %d"
try:
for rowset in market_data['rowsets']:
try:
cursor.execute(delete_sql % (rowset['regionID'], rowset['typeID']))
conn.commit()
except Error as e:
log("history : " + delete_sql % (rowset['regionID'], rowset['typeID']))
log("MySQL Error [%d]: %s" % (e.args[0], e.args[1]))
conn.rollback()
for row in rowset['rows']:
print row
insert_sql += values % (rowset['typeID'], rowset['regionID'], arrow.get(row[0]).datetime.strftime('%Y-%m-%d %H:%M:%S'), row[1], row[2], row[3], row[4], row[5])
try:
cursor.execute(insert_sql[:-1])
conn.commit()
except:
log("Error inserting history, rolling back")
log(simplejson.dumps(market_data, indent=4*' '))
conn.rollback()
except KeyError as e:
log("Error parsing history rowsets")
conn.close()
return
conn.close()
######packages#####
from django.shortcuts import render
from dbcon import connect, get_db
from django.http import HttpResponse
from django.views.decorators.csrf import csrf_exempt
import json
from bson.json_util import dumps
import bson
from bson.objectid import ObjectId
######end-packages#####
#this establishes connection with database
database = connect()
# this method is linked with the front index page which simply displayes the welcome page
def index(request):
return render(request, 'dashboard/index.html')
# this method is linked with the registration page which gets the data from input field
# and then stores it in a database
@csrf_exempt
def register(request):
try:
if request.method == 'POST':
action = request.POST.get("action", None)
def get_avg_station_quantity(typeid, regionid):
#return None
cursor, conn = dbcon.connect()
cursor.execute("SELECT AVG(quantity) FROM history WHERE typeid=%d AND regionid=%d" % (typeid, regionid))
return cursor.fetchone()[0]
def get_station_name(stationid):
cursor, conn = dbcon.connect()
cursor.execute("SELECT stationname FROM stations WHERE stationid=%d" %
(systemid))
name = cursor.fetchone()
return name[0]
def get_region(stationid):
cursor, conn = dbcon.connect()
cursor.execute(
"SELECT regionid FROM systems WHERE systemid=(SELECT systemid FROM stations WHERE stationid=%d)"
% (stationid))
return cursor.fetchone()[0]
# coding=utf-8
from flask import Flask, render_template, session, redirect, url_for, request
from flask_bootstrap import Bootstrap
# form
from flask_wtf import Form
from wtforms import TextAreaField, SubmitField, PasswordField
from wtforms.validators import Required
import sqlite3
import json
import dbcon
dbcon.connect('mboard.db')
file = r'mboard.db'
app = Flask(__name__)
bootstrap = Bootstrap(app)
app.config['SECRET_KEY'] = 'GUOKAOHE'
class newform(Form):
content = TextAreaField('please enter you message:',
validators=[Required()],
render_kw={
'placeholder': 'Message board',
'style':
'background: url("arrow.ico");height:500;'
})
form_submit = SubmitField('Submit')
class loginform(Form):
