def update_tokens(self):
"""
We need to record these updates to token states and provide a
way to view this in the Gateway Interface.
"""
session = DBSession()
data = simplejson.loads(self.request.body)
if not 'device_id' in data:
return json_response('You must provide an device_id')
device = session.query(Device)\
.filter_by(device_id=data['device_id']).first()
if device:
for i in data['tokens']:
token = session.query(Token)\
.filter_by(token=i['token_id']).first()
if token:
circuit = session.query(Circuit)\
.filter_by(pin=i['account_id']).first()
if circuit:
job = AddCredit(token.value, circuit, token)
session.add(job)
token.state = 'used'
session.merge(token)
session.flush()
return json_response('ok')
else:
return json_response('You must provide a valid device_id')
class SMSHandler(object):
"""
Handler for most SMS operations
"""
def __init__(self, request):
self.request = request
self.breadcrumbs = breadcrumbs[:]
self.session = DBSession()
@action(renderer="sms/index.mako", permission="view")
def index(self):
breadcrumbs = self.breadcrumbs[:]
breadcrumbs.append({"text": "SMS Message"})
limit = self.request.params.get('limit', 1000)
count = self.session.query(Message).count()
messages = self.session.\
query(Message).order_by(desc(Message.id)).limit(limit)
return {
"limit": limit,
"count": count,
"messages": messages,
"table_headers": make_table_header(OutgoingMessage),
"breadcrumbs": breadcrumbs}
@action(renderer='sms/meter_messages.mako', permission='view')
def meter_messages(self):
from sqlalchemy import create_engine
db_string = self.request.registry.settings.get('db_string')
engine = create_engine(db_string, echo=False)
conn = engine.connect()
results = conn.execute(
"""
SELECT meter.name, meter.id, incoming_message.text, message.date
FROM meter, message, incoming_message
WHERE meter.phone = message.number
AND message.id = incoming_message.id ORDER BY message.date DESC LIMIT 1000;
""")
return {'results': results}
@action(permission="view")
def remove_all(self):
[self.session.delete(msg) for msg in self.session.query(Message).all()]
return HTTPFound(
location="%s/sms/index" % self.request.application_url)
@action()
def ping(self):
return Response('ok')
@action()
def received(self):
msgs = [msg.toDict() for msg in self.session.query(Message).\
filter_by(sent=False).filter(or_(Message.type == "job_message",
Message.type == "outgoing_message")).all()
if msg.number != '']
return Response(
content_type="application/json",
body=simplejson.dumps(msgs))
def login(self):
session = DBSession()
came_from = self.request.params.get('came_from', '/')
message = ''
login = ''
password = ''
if 'form.submitted' in self.request.params:
name = self.request.params['name']
hash = hashlib.md5(self.request.params['password']).hexdigest()
user = session.query(Users)\
.filter_by(name=name)\
.filter_by(password=unicode(hash)).first()
if user:
print(name, hash)
headers = remember(self.request, user.name)
return HTTPFound(
location="%s" % came_from,
headers=headers)
message = 'Failed login'
return {
'message': message,
'url': self.request.application_url + '/login',
'came_from': came_from,
'login': login,
'password': password}
def make_tokens(self):
session = DBSession()
batch = TokenBatch()
session.add(batch)
session.flush()
data = simplejson.loads(self.request.body)
if not 'device_id' in data:
return Response('You must provide an device_id')
else:
device = session.query(Device)\
.filter_by(device_id=data['device_id']).first()
if device:
if not 'tokens' in data:
return Response('You must provide an amount of tokens')
for group in data['tokens']:
for i in range(0, group['count']):
token = Token(Token.get_random(),
batch=batch,
value=group['denomination'])
session.add(token)
session.flush()
return json_response(
[{'token_id': int(token.token),
'denomination':
float(token.value)} for token in batch.getTokens()])
else:
return json_response('Not a valid device')
def send(self):
session = DBSession()
twilio = session.query(TwilioInterface).first()
msg = save_and_parse_message(twilio,
self.request.params['From'],
self.request.params['Body'],
id=self.request.params['SmsMessageSid'])
return Response(msg.uuid)
def groupfinder(userid, request):
"""
Rough cut of user admin system.
"""
session = DBSession()
user = session.query(Users).filter_by(name=userid).first()
if user:
if user.group:
return [user.group.name]
def findMeter(message):
"""
Takes a message object and returns either a meter or none.
Looks up the meter based on the message's number.
"""
session = DBSession()
meter = session.query(Meter).filter_by(phone=str(message.number)).first()
if meter:
return meter
class SMSHandler(object):
"""
Handler for most SMS operations
"""
def __init__(self, request):
self.request = request
self.breadcrumbs = breadcrumbs[:]
self.session = DBSession()
@action(renderer="sms/index.mako", permission="admin")
def index(self):
breadcrumbs = self.breadcrumbs[:]
breadcrumbs.append({"text": "SMS Message"})
limit = self.request.params.get('limit', 1000)
count = self.session.query(Message).count()
messages = self.session.\
query(Message).order_by(desc(Message.id)).limit(limit)
return {
"logged_in": authenticated_userid(self.request),
"limit": limit,
"count": count,
"messages": messages,
"table_headers": make_table_header(OutgoingMessage),
"breadcrumbs": breadcrumbs }
@action(permission="admin")
def remove_all(self):
[self.session.delete(msg) for msg in self.session.query(Message).all()]
return HTTPFound(
location="%s/sms/index" % self.request.application_url)
@action()
def ping(self):
return Response('ok')
@action()
def received(self):
msgs = [msg.toDict() for msg in self.session.query(Message).\
filter_by(sent=False).filter(or_(Message.type == "job_message",
Message.type == "outgoing_message")).all()
if msg.number != '']
return Response(
content_type="application/json",
body=simplejson.dumps(msgs))
def add_meter(self):
session = DBSession()
if self.request.method == 'GET':
comms = session.query(CommunicationInterface).all()
return {'comms': comms,
'breadcrumbs': self.breadcrumbs
}
elif self.request.method == 'POST':
comm = session.query(CommunicationInterface)\
.get(int(self.request.params.get('communication-interface')))
meter_name = self.request.params.get('meter-name')
meter_phone = self.request.params.get('meter-phone')
meter_location = self.request.params.get('meter-location')
batter_capacity = self.request.params.get('battery-capacity')
panel_capacity = self.request.params.get('panel-capacity')
meter = Meter(name=meter_name,
phone=meter_phone,
location=meter_location,
geometry='POINT(1 1)',
battery=batter_capacity,
status=True,
panel_capacity=panel_capacity,
communication_interface_id=comm.id)
# save the meter
session.add(meter)
session.flush()
# start at mains as every meter needs a mains
start_ip_address = 200
for x in range(0, int(self.request.params.get('number-of-circuits'))):
ip_address = '192.168.1.%s' % (start_ip_address + x)
# create an account for each circuit
account = Account(lang=self.request.params.get('default-language'))
session.add(account)
session.flush()
# create the circuit
circuit = Circuit(
meter=meter,
account=account,
ip_address=ip_address,
power_max=self.request.params.get('power-emax'),
energy_max=self.request.params.get('default-emax'))
session.add(circuit)
session.flush()
return HTTPFound(location='/manage/show_meters')
def job(self):
session = DBSession()
job = session.query(Job).get(self.request.matchdict["id"])
if self.request.method == "DELETE":
job.state = False
job.end = datetime.now()
session.merge(job)
return Response(str(job))
else:
return Response(simplejson.dumps(job.toDict()))
class FunctionalTests(unittest.TestCase):
""" Unit tests for the Gateway UI """
def setUp(self):
self.config = testing.setUp()
import ConfigParser
from gateway.models import DBSession
from gateway.models import initialize_sql
config = ConfigParser.ConfigParser()
config.readfp(open('testing.ini'))
db_string = config.get('app:gateway', 'db_string')
initialize_sql(db_string)
from gateway import main
from webtest import TestApp
app = main(None, **{'db_string': db_string,
'mako.directories': config.get('app:gateway', 'mako.directories')})
self.testapp = TestApp(app)
self.session = DBSession()
def tearDown(self):
testing.tearDown()
def test_number_of_meter(self):
from gateway.models import Meter
meters = self.session.query(Meter).all()
self.assertEquals(len(meters), 1)
def test_index(self):
res = self.testapp.get('/', status=200)
self.assertEquals(res.status, '200 ok')
def test_number_of_circuits(self):
from gateway.models import Circuit
circuits = self.session.query(Circuit).all()
self.assertEquals(len(circuits), 11)
def test_number_of_comm_interfaces(self):
from gateway.models import CommunicationInterface
interfaces = self.session.query(CommunicationInterface).all()
self.assertEquals(len(interfaces), 1)
def test_balance_english(self):
from gateway.models import CommunicationInterface
from gateway.models import Circuit
def findCircuit(message, meter):
"""Looks up circuit from meter and message.
"""
session = DBSession()
try:
circuit = session.query(Circuit).\
filter_by(ip_address=message["cid"]).\
filter_by(meter=meter).first()
if circuit:
return circuit
except Exception as e:
print e, message
def get_token(message):
"""Tries to match message to token."""
session = DBSession()
tokenNumber = message.text.split(delimiter)[2]
token = session.query(Token).filter_by(state="new").filter_by(token=tokenNumber).first()
if token:
return token
else:
interface = message.communication_interface
interface.sendMessage(
message.number, make_message_body("no-token.txt", lang=message.langauge), incoming=message.uuid
)
return False
class InterfaceHandler(object):
"""
A handler for managing the interfaces.
"""
def __init__(self, request):
self.breadcrumbs = breadcrumbs
self.request = request
self.session = DBSession()
self.interface = self.session.query(
CommunicationInterface).get(self.request.matchdict.get('id'))
@action(renderer='interface/index.mako', permission='admin')
def index(self):
breadcrumbs = self.breadcrumbs[:]
breadcrumbs.append({'text': 'Interface overview'})
messages = self.session.query(IncomingMessage)\
.filter_by(communication_interface=self.interface)\
.order_by(desc(IncomingMessage.id))\
.limit(200)
return {'interface': self.interface,
'breadcrumbs': breadcrumbs,
'messages': messages,
'fields': get_fields(self.interface)}
@action()
def send(self):
msg = save_and_parse_message(
self.interface,
self.request.params['number'],
self.request.params['message'])
return Response(msg.uuid)
@action(permission='admin')
def remove(self):
self.session.delete(self.interface)
self.session.flush()
return HTTPFound(location="%s/" % self.request.application_url)
def show_primary_logs(self):
session = DBSession()
logs = session.query(PrimaryLog)\
.filter_by(circuit=self.circuit)\
.order_by(desc(PrimaryLog.created))\
.limit(200)
return json_response([{'id': l.id,
'status': l.status,
'use_time': l.use_time,
'gateway_date': l.created.strftime('%Y-%m-%d %H:%M:%S'),
'meter_date': l.date.strftime('%Y-%m-%d %H:%M:%S'),
'time_difference': find_time_different(l),
'watthours': "{0:.1f}".format(l.watthours),
'credit': int(l.credit)} for l in logs])
def show_pculogs(self):
session = DBSession()
value = self.request.params.get('pcu-value', 'battery_volts')
start = datetime.strptime(
self.request.params.get('start', '05/01/2011'), '%m/%d/%Y')
end = datetime.strptime(
self.request.params.get('end', '07/20/2011'), '%m/%d/%Y')
pculogs = session.query(PCULog)\
.filter(PCULog.meter == self.meter)\
.filter(PCULog.timestamp >= start)\
.filter(PCULog.timestamp <= end)
return json_response(
{'dates': map(lambda x: time.mktime(x.timestamp.timetuple()), pculogs),
'values': map(lambda x: getattr(x, value), pculogs)})
def metersAsGeoJson(self):
session = DBSession()
meters = session.query(Meter).filter(Meter.geometry != None)
return Response(
content_type='application/json',
body=simplejson.dumps(
{'type': 'FeatureCollection',
'features':
[{'type': 'Feature',
'properties': {'name': x.name},
'geometry': {'type': 'Point',
'coordinates': list(loads(
x.geometry).coords)[0]}}
for x in meters]
}))
def get_circuit(message):
"""Queris the database to find circuit
Returns the circuit or false
"""
session = DBSession()
pin = message.text.split(delimiter)[1].lower()
circuit = session.query(Circuit).filter_by(pin=pin).first()
if circuit:
return circuit
else:
interface = message.communication_interface
interface.sendMessage(
message.number,
make_message_body("no-circuit.txt", lang=interface.lang, pin=pin),
incoming=message.uuid)
def get_payment_logs(self):
"""
A view to render a circuit's payment history as a json object.
"""
session = DBSession()
payments = session.query(AddCredit)\
.filter_by(circuit=self.circuit).order_by(desc(AddCredit.start))
return json_response(
{'payments': [{'id': p.id,
'status': p.state,
'token': str(p.token),
'start': str(p.start),
'credit': p.credit,
'end': str(p.end),
'state': p.state} for p in payments]})
def show_meters_json(self):
session = DBSession()
return json_response(
[
{'name': m.name,
'id': m.id,
'number_of_circuits': len(m.get_circuits()),
'uptime': find_meter_uptime(m),
'pv': m.panel_capacity,
'last_message': find_last_message_by_meter(m),
'phone': m.phone,
'battery': m.battery,
'location': m.location
} for m in session.query(Meter).all()
]
)
def find_meter_uptime(meter):
"""
cs = # of circuits
actual = number messages it did receive
possible = Number of messages it should have receive in the last week
-> 48 * cs * 7
(* (/ actual possible) 100)
"""
now = datetime.now()
last_week = now - timedelta(days=7)
session = DBSession()
log_count = session.query(PrimaryLog)\
.filter_by(circuit=meter.getMainCircuit())\
.filter(PrimaryLog.date < now)\
.filter(PrimaryLog.date > last_week).count()
return int((log_count / (24 * 7.0)) * 100)
class InterfaceHandler(object):
"""
A handler for managing the interfaces.
"""
def __init__(self, request):
self.breadcrumbs = breadcrumbs
self.request = request
self.session = DBSession()
self.interface = self.session.query(
CommunicationInterface).get(self.request.matchdict.get('id'))
@action(renderer='interface/index.mako', permission='admin')
def index(self):
breadcrumbs = self.breadcrumbs[:]
breadcrumbs.append({'text': 'Interface overview'})
return {'interface': self.interface,
'breadcrumbs': breadcrumbs,
'fields': get_fields(self.interface),
'logged_in': authenticated_userid(self.request)}
def save_and_parse_message(self, origin, text, id=None):
"""
Function to save incoming message based on relay type. Takes the
message class, the numner, the body of the message and a
session. Optional argument is the messages id. Parses the message
and return the message object.
"""
if id is None:
id = str(uuid.uuid4())
message = IncomingMessage(origin, text, id, self.interface)
self.session.add(message)
self.session.flush()
dispatcher.matchMessage(message)
return message
@action()
def send(self):
msg = self.save_and_parse_message(self.request.params['number'],
self.request.params['message'])
return Response(msg.uuid)
@action()
def remove(self):
self.session.delete(self.interface)
self.session.flush()
return HTTPFound(location="%s/" % self.request.application_url)
def index(self):
"""
Main view for meter overview. Also includes circuit gird and
some graphs
"""
session = DBSession()
breadcrumbs = self.breadcrumbs[:]
breadcrumbs.append({'text': 'Meters',
'url': '/manage/show_meters'})
breadcrumbs.append({"text": "Meter Overview"})
return {
'meter': self.meter,
'last_message': find_last_message_by_meter(self.meter),
'changesets': session\
.query(MeterChangeSet).filter_by(meter=self.meter),
'meter_config_keys': session.query(MeterConfigKey).all(),
'breadcrumbs': breadcrumbs}
def make_columns(cls):
"""
Function to look up and convert a SQL types to a colander type
Requires a Class the inherents from Base
"""
session = DBSession()
data = cls.__mapper__.columns._data
columns = {}
for key in data.keys():
column = data[key]
if len(column.foreign_keys) == 0:
columns[key] = matching.get(
str(column.type),
String)
elif len(column.foreign_keys) == 1:
foregin = column.foreign_keys[0]
table = foregin.column.table
choices = session.query(table).all()
columns[key] = [table, choices]
return columns
def show_graphing_logs(self):
session = DBSession()
value = self.request.params.get('value', 'watthours')
start = datetime.strptime(self.request.params.get('start', '05/01/2011'), '%m/%d/%Y')
end = datetime.strptime(self.request.params.get('end', '06/01/2011'), '%m/%d/%Y')
logs = session.query(PrimaryLog)\
.filter(PrimaryLog.circuit == self.circuit)\
.filter(PrimaryLog.date > start)\
.filter(PrimaryLog.date <= end)\
.order_by(PrimaryLog.created)
if value == 'use_time':
values = map(lambda x: (getattr(x, value) / 3600), logs)
else:
values = map(lambda x: getattr(x, value), logs)
return json_response(
{'dates': map(lambda x: time.mktime(x.date.timetuple()), logs),
'values': values }
)
def add(self):
errors = None
session = DBSession()
groups = session.query(Groups).all()
breadcrumbs = self.breadcrumbs[:]
breadcrumbs.append({'text': 'Add a new user'})
if self.request.method == 'GET':
return {'breadcrumbs': breadcrumbs,
'groups': groups,
'errors': errors}
elif self.request.method == 'POST':
name = self.request.params['name']
email = self.request.params['email']
password = self.request.params['password']
group = self.request.params['group']
user = Users(name=name, email=email,
group_id=group,
password=password)
session.add(user)
session.flush()
return HTTPFound(location='/')
def getDataListForCircuit(self,
circuit_id,
dateStart=datetime(2011, 5, 12),
dateEnd=datetime(2011, 5, 13),
quantity='watthours',
verbose=0):
session = DBSession()
# get query based on circuit and date
logs = session.query(PrimaryLog)\
.filter(PrimaryLog.circuit_id == circuit_id)\
.filter(PrimaryLog.date > dateStart)\
.filter(PrimaryLog.date <= dateEnd)\
.order_by(PrimaryLog.date)
# turn query into a sorted list of unique dates and watthour readings
data = [(l.date, getattr(l, quantity)) for l in logs]
# remove duplicate entries and sort by date
data = list(set(data))
data.sort()
dates = [d[0] for d in data]
watthours = [d[1] for d in data]
return dates, watthours
class AlertHandler(object):
"""
This handler allows admin to send test messages.
Will be expaned to include more types of alerts.
"""
def __init__(self, request):
self.request = request
self.session = DBSession()
self.breadcrumbs = breadcrumbs[:]
@action()
def all(self):
session = DBSession()
return json_response([a.toJSON() for a in session.query(Alert).order_by(Alert.id.desc()).all()])
@action(renderer='alerts/make.mako', permission='admin')
def make(self):
breadcrumbs = self.breadcrumbs[:]
breadcrumbs.append({'text': 'Send Alerts'})
return {
'breadcrumbs': breadcrumbs,
'interfaces': self.session.query(CommunicationInterface).all()}
@action(permission='admin')
def send_test(self):
"""
Method to send a test message via a comm interface
"""
interface = self.session\
.query(CommunicationInterface)\
.get(self.request.params['interface'])
number = self.request.params['number']
text = self.request.params['text']
msg = interface.sendMessage(number, text)
return HTTPFound(location="%s/message/index/%s" %
(self.request.application_url, msg.uuid))
class Dashboard(object):
"""
Home page for the gateway
"""
def __init__(self, request):
self.request = request
self.breadcrumbs = breadcrumbs[:]
self.session = DBSession()
@action(renderer='index.mako', permission='view')
def index(self):
logs = Grid(SystemLog,
self.session.query(SystemLog)\
.order_by(desc(SystemLog.created)).limit(10))
logs.configure(readonly=True)
return {
'logs': logs,
'breadcrumbs': self.breadcrumbs}
@action(renderer="dashboard.mako", permission='view')
def dashboard(self):
return {
"logged_in": authenticated_userid(self.request)}
import transaction
from shapely.geometry import Point
from gateway.models import DBSession
from gateway.models import Meter
from gateway.models import SystemLog
from gateway.models import initialize_sql
import csv
db_string = "postgresql://*****:*****@localhost:5432/gateway"
initialize_sql(db_string)
session = DBSession()
if __name__ == '__main__':
query = session.query(Meter)
try:
locations = csv.reader(open("locations.csv"))
for line in locations:
with transaction:
point = Point(float(line[2]), float(line[1]))
print point.wkt
meter = query.filter_by(name= line[0]).first()
meter.geometry = point.wkt
session.flush()
except Exception as error:
print error
