def schedule_edit(schedule, new=False):
print(schedule)
sqlConn = SqlHelper()
sqlStr = """UPDATE schedule SET
startdate={0},
enddate={1},
sunday={2},
monday={3},
tuesday={4},
wednesday={5},
thursday={6},
friday={7},
saturday={8},
starttime={9},
duration={10},
repeating={11},
station={12}
WHERE id={13}""".format(schedule['startdate'], schedule['enddate'],
schedule['sunday'], schedule['monday'],
schedule['tuesday'], schedule['wednesday'],
schedule['thursday'], schedule['friday'],
schedule['saturday'], schedule['starttime'],
schedule['duration'], schedule['repeat'],
schedule['station'], schedule['id'])
print(sqlStr)
sqlConn.execute(sqlStr)
def water_usage_stats():
sqlConn = SqlHelper()
sqlStr = """
SELECT DISTINCT dripnodes.sid,
SUM((duration / 60 )) as runmins,
(SELECT sum((gph * count)) as totalgph from dripnodes where dripnodes.sid=history.sid) as totalgph,
stations.notes
FROM history
INNER JOIN dripnodes ON dripnodes.sid=history.sid
INNER JOIN stations ON stations.id=history.sid
WHERE starttime >= (CURRENT_DATE - INTERVAL 30 DAY)
GROUP BY dripnodes.sid
ORDER BY dripnodes.sid ASC;
"""
results = {'water_usage': []}
for d in sqlConn.read(sqlStr):
results['water_usage'].append({
'sid':
int(d[0]),
'notes':
str(d[3]),
'run_mins':
int(d[1]),
'total_gph':
float(d[2]),
'usage_last_30':
float((d[1] / 60) * d[2])
})
return results
def get_schedule(station=None):
this_date = int(str(datetime.now().date()).replace('-', ''))
result = []
sqlConn = SqlHelper()
schedules = []
if station is None:
sqlStr = """SELECT * FROM schedule WHERE enddate > {0} AND startdate <= {1} ORDER BY station ASC""".format(
this_date, this_date)
schedules = sqlConn.read(sqlStr)
for sched in schedules:
data = {
"id": sched[0],
"startdate": sched[1],
"enddate": sched[2],
"sunday": sched[3] == 1,
"monday": sched[4] == 1,
"tuesday": sched[5] == 1,
"wednesday": sched[6] == 1,
"thursday": sched[7] == 1,
"friday": sched[8] == 1,
"saturday": sched[9] == 1,
"station": sched[10],
"starttime": sched[11],
"duration": sched[12],
"repeat": sched[13] == 1
}
result.append(data)
return result
def list(self):
schedules = []
sqlStr = ''' SELECT * FROM schedule where station={0}'''.format(
self.sid)
sqlConn = SqlHelper()
sched_data = sqlConn.read(sqlStr)
for sched in sched_data:
s = StationSchedule(self.sid)
s.sched_id = sched[0]
s.startdate = sched[1]
s.enddate = sched[2]
s.sunday = sched[3]
s.monday = sched[4]
s.tuesday = sched[5]
s.wednesday = sched[6]
s.thursday = sched[7]
s.friday = sched[8]
s.saturday = sched[9]
s.sid = self.sid # sched[10]
s.starttime = sched[11]
s.duration = sched[12]
s.repeat = sched[13]
schedules.append(s)
return schedules
def list_gpio():
sqlConn = SqlHelper()
gpio_pins = []
sqlStr = "SELECT * FROM gpio_pins ORDER BY gpio asc"
pins = sqlConn.read(sqlStr)
for i in pins:
gpio_pins.append({'gpio': i[0], 'notes': i[1]})
return gpio_pins
def get_chart_stats(cid, days=30):
sqlStr = ""
sqlConn = SqlHelper()
results = {"labels": [], "series": [], "data": []}
station_data = {}
stations = []
for s in sqlConn.read("SELECT DISTINCT id FROM stations WHERE common=0"):
stations.append(s[0])
for station in stations:
station_data[station] = {station: {}}
if cid == 1: # chart1 in js app
d1 = []
sqlStr = """SELECT DISTINCT sid, SUM(duration / 60)
FROM history
WHERE starttime >= (CURRENT_DATE - INTERVAL {0} DAY) AND schedule_id>0
GROUP BY sid
ORDER BY sid ASC""".format(days)
td = sqlConn.read(sqlStr)
for d in td:
try:
station_data[d[0]]['scheduled'] = int(d[1])
except Exception as e:
station_data[d[0]]['scheduled'] = 0
results['series'].append('Scheduled'.format(days))
d2 = []
sqlStr = """SELECT DISTINCT sid, SUM(duration / 60)
FROM history
WHERE starttime >= (CURRENT_DATE - INTERVAL {0} DAY) AND schedule_id=0
GROUP BY sid
ORDER BY sid ASC""".format(days)
td = sqlConn.read(sqlStr)
for d in td:
try:
station_data[d[0]]['unscheduled'] = int(d[1])
except Exception as e:
station_data[d[0]]['unscheduled'] = 0
results['series'].append('Unscheduled'.format(days))
results['labels'] = stations
sorted_data = sorted(station_data.items(), key=operator.itemgetter(0))
for i in sorted_data:
if 'scheduled' in i[1]:
d1.append(i[1]['scheduled'])
else:
d1.append(0)
if 'unscheduled' in i[1]:
d2.append(i[1]['unscheduled'])
else:
d2.append(0)
results['data'].append(d1)
results['data'].append(d2)
return results
def station_edit(station):
sqlConn = SqlHelper()
sqlStr = """UPDATE stations SET
gpio='{0}',
notes='{1}'
WHERE id={2}""".format(station['gpio_pin'], station['notes'],
station['sid'])
print(sqlStr)
sqlConn.execute(sqlStr)
def get_station_nodes():
sqlConn = SqlHelper()
sqlStr = """
SELECT * FROM dripnodes ORDER BY sid ASC
"""
results = {'dripnodes': []}
for dn in sqlConn.read(sqlStr):
node = {'id': dn[0], 'sid': dn[2], 'gph': dn[1], 'count': dn[3]}
results['dripnodes'].append(node)
return results
def add(self, gpio_pin, notes=''):
sqlConn = SqlHelper()
try:
self.gpio_pin = gpio_pin
self.notes = notes
sqlStr = '''INSERT INTO stations (ID, GPIO, NOTES) VALUES ({0}, {1}, \'{2}\')'''.format(
self.sid, self.gpio_pin, self.notes)
sqlConn.execute(sqlStr)
except Exception as e:
raise e
def load(self):
sqlConn = SqlHelper()
try:
sqlStr = ''' SELECT * FROM stations WHERE id={0}'''.format(
self.sid)
data = sqlConn.read(sqlStr)[0]
self.gpio_pin = data[1]
self.notes = data[2]
self.details = data[3]
except Exception as e:
raise e
def get_last_station_run():
sqlConn = SqlHelper()
results = {}
stations = list_stations()
for station in stations:
sqlStr = "SELECT (starttime + INTERVAL {0} HOUR) FROM history WHERE sid={1} ORDER BY starttime DESC LIMIT 1".format(
CONFIG.SETTINGS['LOCALOFFSET'], station['sid'])
try:
results[station['sid']] = sqlConn.read(sqlStr)[0][0]
except:
results[station['sid']] = None
return results
def add_or_edit_station_nodes(dripnode, new=False):
sqlConn = SqlHelper()
sqlStr = ""
if new:
sqlStr = """
INSERT INTO dripnodes (gph, sid, count) VALUES ({0},{1},{2})
""".format(dripnode['gph'], dripnode['sid'], dripnode['count'])
else:
sqlStr = """
UPDATE dripnodes SET gph={0}, sid={1}, count={2} WHERE id={3}
""".format(dripnode['gph'], dripnode['sid'], dripnode['count'],
dripnode['id'])
sqlConn.execute(sqlStr)
def get_current_tasks(self):
sqlConn = SqlHelper()
if str(self.today_cache['day']) + str(
self.today_cache['time']) != self.last_datetime:
sqlStr = """SELECT id, station, duration from schedule
WHERE (
startdate <= CAST(replace(date(NOW()), '-', '') AS UNSIGNED)
and enddate > CAST(replace(date(NOW()), '-', '') AS UNSIGNED)
)
and {0}=1
and starttime={1}
""".format(self.today_cache['day'], self.today_cache['time'])
return sqlConn.read(sqlStr)
else:
pass
def dripnodes_edit(nodes_data, new=False, delete=False):
sqlConn = SqlHelper()
sqlStr = ""
if new:
sqlStr = """
INSERT INTO dripnodes (gph, sid, count) VALUES ({0}, {1}, {2});
""".format(nodes_data['gph'], nodes_data['sid'], nodes_data['count'])
elif delete:
sqlStr = """
DELETE FROM dripnodes WHERE gph={0} AND sid={1}
""".format(nodes_data['gph'], nodes_data['sid'])
else:
sqlStr = """
UPDATE dripnodes SET gph={0}, sid={1}, count={2} WHERE sid={1} and gph={0};
""".format(nodes_data['gph'], nodes_data['sid'], nodes_data['count'])
sqlConn.execute(sqlStr)
class RelayController:
def __init__(self):
self.sqlConn = SqlHelper()
self.__setup_pins()
def __setup_pins(self):
pins = []
sqlStr = """SELECT gpio FROM gpio_pins"""
for pin in self.sqlConn.read(sqlStr):
pins.append(pin[0])
GPIO.setmode(GPIO.BCM)
for pin in pins:
GPIO.setup(pin,
GPIO.OUT,
initial=CONFIG.SETTINGS['GPIO_RELAY_OFFSTATE'])
def __reset(self):
GPIO.setmode(GPIO.BCM)
self.__setup_pins
def __log_relay_activity(self, sid, duration, schedule_id):
sqlStr = """ INSERT INTO history (sid, schedule_id, duration, starttime)
VALUES ({0},{1},{2},'{3}')""".format(sid, schedule_id, duration,
datetime.now())
self.sqlConn.execute(sqlStr)
def __get_gpio_from_sid(self, sid):
sqlStr = """ SELECT gpio FROM stations WHERE id={0}""".format(sid)
gpio = self.sqlConn.read(sqlStr)[0]
return gpio
def activate_relay(self, sid, duration, schedule_id=0):
pin = self.__get_gpio_from_sid(sid)
self.__log_relay_activity(sid, duration, schedule_id)
timer = 0
GPIO.output(pin, CONFIG.SETTINGS['GPIO_RELAY_ONSTATE'])
GPIO.output(CONFIG.SETTINGS['COMMON_WIRE_GPIO'],
CONFIG.SETTINGS['GPIO_RELAY_ONSTATE'])
while timer < duration:
sleep(1)
timer += 1
GPIO.output(pin, CONFIG.SETTINGS['GPIO_RELAY_OFFSTATE'])
GPIO.output(CONFIG.SETTINGS['COMMON_WIRE_GPIO'],
CONFIG.SETTINGS['GPIO_RELAY_OFFSTATE'])
def station_history(sid=None, days=7):
sqlConn = SqlHelper()
sqlStr = ""
if sid is None:
sqlStr = "SELECT * FROM history WHERE starttime >= (CURRENT_DATE - INTERVAL {0} DAY) ORDER BY id DESC".format(
days)
else:
pass
history_json = {'history': []}
for hist in sqlConn.read(sqlStr):
history_json['history'].append({
'id': hist[0],
'sid': hist[1],
'schedule_id': hist[2],
'duration': hist[3],
'starttime': hist[4]
})
return history_json
def chart_stats_chrono(days=7):
sqlConn = SqlHelper()
sqlStr = """SELECT DISTINCT DAYOFWEEK((starttime + INTERVAL {0} HOUR)) as day, SUM(duration / 60) as mins
FROM history
WHERE starttime >= (CURRENT_DATE - INTERVAL {1} DAY)
GROUP BY day
ORDER BY day ASC""".format(CONFIG.SETTINGS['LOCALOFFSET'], days)
sqlStr2 = """SELECT DISTINCT DAYOFWEEK((starttime + INTERVAL {0} HOUR)) as day, SUM(duration / 60) as mins
FROM history
WHERE starttime >= (CURRENT_DATE - INTERVAL {1} DAY) AND schedule_id>0
GROUP BY day
ORDER BY day ASC""".format(CONFIG.SETTINGS['LOCALOFFSET'], days)
sqlStr3 = """SELECT DISTINCT DAYOFWEEK((starttime + INTERVAL {0} HOUR)) as day, SUM(duration / 60) as mins
FROM history
WHERE starttime >= (CURRENT_DATE - INTERVAL {1} DAY) AND schedule_id=0
GROUP BY day
ORDER BY day ASC""".format(CONFIG.SETTINGS['LOCALOFFSET'], days)
results = {
"labels": [
'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
'Saturday'
],
"series": ['Total', 'Scheduled', 'Unscheduled'],
# durations (starttime + sec(duration)), per series
"data": [[0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0, 0],
[0, 0, 0, 0, 0, 0, 0]]
}
# parser.parse('January 11, 2010').strftime("%A")
sql_data = sqlConn.read(sqlStr)
sql_data2 = sqlConn.read(sqlStr2)
sql_data3 = sqlConn.read(sqlStr3)
if sql_data is not None and len(sql_data) > 0:
for d in sql_data:
results['data'][0][int(sunday_fix(d[0]))] = int(d[1])
if sql_data2 is not None and len(sql_data2) > 0:
for d in sql_data2:
results['data'][1][int(sunday_fix(d[0]))] = int(d[1])
if sql_data3 is not None and len(sql_data3) > 0:
for d in sql_data3:
results['data'][2][int(sunday_fix(d[0]))] = int(d[1])
return results
def list_stations():
sqlConn = SqlHelper()
stations = []
sqlStr = 'SELECT * FROM stations WHERE common=0 ORDER BY ID ASC LIMIT 500'
data = sqlConn.read(sqlStr)
for s in data:
sid = s[0]
station = Station(sid)
station.load()
schedules = station.list_schedules()
station_json = {
'gpio_pin': station.gpio_pin,
'sid': station.sid,
'notes': station.notes,
'details': station.details,
'schedule': []
}
for s in schedules:
station_json['schedule'].append(s.__dict__)
stations.append(station_json)
return stations
def schedule_add(schedule):
print(schedule)
sqlConn = SqlHelper()
sqlStr = """INSERT INTO schedule (
startdate,
enddate,
sunday,
monday,
tuesday,
wednesday,
thursday,
friday,
saturday,
starttime,
duration,
repeating,
station
) VALUES (
{0},
{1},
{2},
{3},
{4},
{5},
{6},
{7},
{8},
{9},
{10},
{11},
{12})""".format(schedule['startdate'], schedule['enddate'],
schedule['sunday'], schedule['monday'],
schedule['tuesday'], schedule['wednesday'],
schedule['thursday'], schedule['friday'],
schedule['saturday'], schedule['starttime'],
schedule['duration'], schedule['repeat'],
schedule['station'])
print(sqlStr)
sqlConn.execute(sqlStr)
def chart_minutes_by_station_per_dow(days=30):
sqlConn = SqlHelper()
results = {
"labels": [
'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
'Saturday'
],
"series": [],
"data": []
}
def fill_series():
stationsSql = "SELECT id FROM stations WHERE common=0 ORDER BY id ASC"
return [i[0] for i in sqlConn.read(stationsSql)]
results['series'] = fill_series()
def populate_data(sid):
dataSql = """SELECT
sid,
DAYOFWEEK((starttime + INTERVAL {0} HOUR)) as day,
SUM(duration / 60) as mins
FROM history
WHERE starttime >= (CURRENT_DATE - INTERVAL {1} DAY) AND sid = {2}
GROUP BY day
ORDER BY day ASC""".format(CONFIG.SETTINGS['LOCALOFFSET'],
days, sid)
return [i for i in sqlConn.read(dataSql)]
def parse_day_data(table):
result = {0: 0, 1: 0, 2: 0, 3: 0, 4: 0, 5: 0, 6: 0}
for i in table:
ind = sunday_fix(i[1])
result[ind] = int(i[2])
return result.values()
for sid in results['series']:
results['data'].append(list(parse_day_data(populate_data(sid))))
return results
def get_gpio(self):
sqlConn = SqlHelper()
sqlStr = """ SELECT gpio FROM gpio_pins """
for pin in sqlConn.read(sqlStr):
self.gpio_list.append(pin[0])
def get_schedule_cal():
base = datetime.today()
date_list = [base - timedelta(days=x) for x in range(-14, 14)]
sqlConn = SqlHelper()
events = []
stations = [x[0] for x in sqlConn.read("SELECT id FROM stations")]
station_colors = {}
for i in stations:
station_colors[i] = "#%06x" % random.randint(0, 0xFFFFFF)
sqlStr = """
SELECT * FROM schedule
WHERE (startdate <= CAST(replace(date(NOW()), '-', '') AS UNSIGNED)
AND enddate > CAST(replace(date(NOW()), '-', '') AS UNSIGNED)
) """.format()
schedules = []
for sched in sqlConn.read(sqlStr):
schedules.append({
"id": sched[0],
"startdate": sched[1],
"enddate": sched[2],
"sunday": sched[3] == 1,
"monday": sched[4] == 1,
"tuesday": sched[5] == 1,
"wednesday": sched[6] == 1,
"thursday": sched[7] == 1,
"friday": sched[8] == 1,
"saturday": sched[9] == 1,
"station": sched[10],
"starttime": sched[11],
"duration": sched[12],
"repeat": sched[13] == 1
})
for date in date_list:
for event in schedules:
# print(
# str("%04d" % event['starttime'])[:2],
# str("%04d" % event['starttime'])[-2:]
# )
wd = date.weekday()
if wd == 0 and event['sunday']:
events.append({
'id':
event['id'],
'title':
"SID #" + str(event['station']) + " for " +
str(event['duration'] / 60) + ' min',
'start':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) -
timedelta(days=1)),
'end':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) +
timedelta(seconds=event['duration']) -
timedelta(days=1)),
'backgroundColor':
station_colors[event['station']],
'textColor':
'#FFF'
})
if wd == 1 and event['monday']:
events.append({
'id':
event['id'],
'title':
"SID #" + str(event['station']) + " for " +
str(event['duration'] / 60) + ' min',
'start':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) -
timedelta(days=1)),
'end':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) +
timedelta(seconds=event['duration']) -
timedelta(days=1)),
'backgroundColor':
station_colors[event['station']],
'textColor':
'#FFF'
})
if wd == 2 and event['tuesday']:
events.append({
'id':
event['id'],
'title':
"SID #" + str(event['station']) + " for " +
str(event['duration'] / 60) + ' min',
'start':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) -
timedelta(days=1)),
'end':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) +
timedelta(seconds=event['duration']) -
timedelta(days=1)),
'backgroundColor':
station_colors[event['station']],
'textColor':
'#FFF'
})
if wd == 3 and event['wednesday']:
events.append({
'id':
event['id'],
'title':
"SID #" + str(event['station']) + " for " +
str(event['duration'] / 60) + ' min',
'start':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) -
timedelta(days=1)),
'end':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) +
timedelta(seconds=event['duration']) -
timedelta(days=1)),
'backgroundColor':
station_colors[event['station']],
'textColor':
'#FFF'
})
if wd == 4 and event['thursday']:
events.append({
'id':
event['id'],
'title':
"SID #" + str(event['station']) + " for " +
str(event['duration'] / 60) + ' min',
'start':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) -
timedelta(days=1)),
'end':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) +
timedelta(seconds=event['duration']) -
timedelta(days=1)),
'backgroundColor':
station_colors[event['station']],
'textColor':
'#FFF'
})
if wd == 5 and event['friday']:
events.append({
'id':
event['id'],
'title':
"SID #" + str(event['station']) + " for " +
str(event['duration'] / 60) + ' min',
'start':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) -
timedelta(days=1)),
'end':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) +
timedelta(seconds=event['duration']) -
timedelta(days=1)),
'backgroundColor':
station_colors[event['station']],
'textColor':
'#FFF'
})
if wd == 6 and event['saturday']:
events.append({
'id':
event['id'],
'title':
"SID #" + str(event['station']) + " for " +
str(event['duration'] / 60) + ' min',
'start':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) -
timedelta(days=1)),
'end':
str(
date.replace(
date.year, date.month, date.day,
int(str("%04d" % event['starttime'])[:2]),
int(str("%04d" % event['starttime'])[-2:]), 0) +
timedelta(seconds=event['duration']) -
timedelta(days=1)),
'backgroundColor':
station_colors[event['station']],
'textColor':
'#FFF'
})
return events
def __init__(self):
self.sqlConn = SqlHelper()
self.__setup_pins()
def get_next_station_run():
local = timezone("America/Los_Angeles")
today = int(datetime.now().strftime('%w'))
sqlConn = SqlHelper()
results = {}
stations = list_stations()
sqlStr = """SELECT * FROM schedule
WHERE (startdate <= replace(CAST(CURDATE() AS CHAR), '-', '')
AND enddate > replace(CAST(CURDATE() AS CHAR), '-', ''))
AND station={0}
ORDER BY starttime DESC
"""
for s in stations:
results[s['sid']] = {}
for d in sqlConn.read(sqlStr.format(s['sid'])):
if s['sid'] == d[10]:
counter = 0
counter -= today
daylist = [d[3], d[4], d[5], d[6], d[7], d[8], d[9]]
if 1 in daylist:
while counter <= 7:
counter += 1
if daylist[today] == 1 and d[11] > 0:
results[s['sid']]['next_date'] = datetime.now(
).date() + timedelta(days=counter)
t = list('{0:04d}'.format(d[11]))
t.insert(2, ':')
results[s['sid']]['duration'] = d[12]
results[s['sid']]['next_time'] = ''.join(t)
results[
s['sid']]['next_datetime'] = datetime.combine(
results[s['sid']]['next_date'],
datetime.min.time()) + timedelta(
hours=int(results[s['sid']]
['next_time'].split(':')[0]),
minutes=int(
results[s['sid']]
['next_time'].split(':')[1]))
naive = results[s['sid']]['next_datetime']
localdt = local.localize(naive, is_dst=True)
utc_dt = localdt.astimezone(UTC)
if utc_dt < datetime.now().replace(tzinfo=UTC):
utc_dt = utc_dt + timedelta(weeks=1)
results[s['sid']]['next_datetime'] = utc_dt
else:
if (today + counter
) < 6 and daylist[today + counter] == 1 > 0:
results[s['sid']]['next_date'] = datetime.now(
).date() + timedelta(days=counter)
t = list('{0:04d}'.format(d[11]))
t.insert(2, ':')
results[s['sid']]['duration'] = d[12]
results[s['sid']]['next_time'] = ''.join(t)
results[s['sid']][
'next_datetime'] = datetime.combine(
results[s['sid']]['next_date'],
datetime.min.time()) + timedelta(
hours=int(
results[s['sid']]
['next_time'].split(':')[0]),
minutes=int(
results[s['sid']]
['next_time'].split(':')[1]))
naive = results[s['sid']]['next_datetime']
localdt = local.localize(naive, is_dst=True)
utc_dt = localdt.astimezone(UTC)
if utc_dt < datetime.now().replace(tzinfo=UTC):
utc_dt = utc_dt + timedelta(weeks=1)
results[s['sid']]['next_datetime'] = utc_dt
# print(results)
return results
def station_activity_timechart(days=30):
sqlConn = SqlHelper()
results = {
"labels": [
'00:00', '01:00', '02:00', '03:00', '04:00', '05:00', '06:00',
'07:00', '08:00', '09:00', '10:00', '11:00', '12:00', '13:00',
'14:00', '15:00', '16:00', '17:00', '18:00', '19:00', '20:00',
'21:00', '22:00', '23:00'
],
"series": [],
"data": []
}
def extract_hours(table):
result = {
'00': 0,
'01': 0,
'02': 0,
'03': 0,
'04': 0,
'05': 0,
'06': 0,
'07': 0,
'08': 0,
'09': 0,
'10': 0,
'11': 0,
'12': 0,
'13': 0,
'14': 0,
'15': 0,
'16': 0,
'17': 0,
'18': 0,
'19': 0,
'20': 0,
'21': 0,
'22': 0,
'23': 0,
}
if len(table) > 0:
for row in table:
hour = str(str(row[1]).split(' ')[1].split(':')[0])
result[hour] += int(row[2])
sorted_vals = sorted(result.items(), key=lambda x: x[0])
return [v[1] for v in sorted_vals]
def fill_series():
stationsSql = "SELECT id FROM stations WHERE common=0 ORDER BY id ASC"
return [i[0] for i in sqlConn.read(stationsSql)]
results['series'] = fill_series()
def populate_data(sid):
dataSql = """SELECT sid, CAST(starttime AS CHAR), (duration / 60) as mins
FROM history
WHERE starttime >= (CURRENT_DATE - INTERVAL {0} DAY) AND sid = {1}
""".format(days, sid)
return [i for i in sqlConn.read(dataSql)]
for sid in results['series']:
results['data'].append(extract_hours(populate_data(sid)))
return results
def schedule_delete(schedule_id):
sqlConn = SqlHelper()
sqlStr = """DELETE FROM schedule WHERE id={0}""".format(schedule_id)
sqlConn.execute(sqlStr)
def delete_station_node(id):
sqlConn = SqlHelper()
sqlStr = """
DELETE FROM dripnodes WHERE id={0}
""".format(id)
sqlConn.execute(sqlStr)