def alignJourneyPatternsSQL(operationdate, dataownercode, lineplanningnumber):
priorities = getJourneyPatternPriority(operationdate, dataownercode, lineplanningnumber)
if len(priorities) == 0:
return
data = {1: [], 2: []}
highest = priorities[0][0]
order = 0
for linedirection, localservicelevelcode, journeypatterncode, priority in priorities:
order += 1
data[linedirection].append((order, "(select distinct userstopcode, userstopordernumber from localservicegrouppasstime as p where dataownercode = '%s' and localservicelevelcode = '%s' and journeypatterncode = '%s') as q%d" % (dataownercode, localservicelevelcode, journeypatterncode, order)))
sql_one = alignOneDirectionJourneyPatterns(data[highest], dataownercode)
second = data[(set(data.keys()) - set([highest])).pop()]
if len(second) > 0:
sql_two = alignOneDirectionJourneyPatterns(second, dataownercode)
sql = "SELECT one.*, two.* FROM (%s) AS one FULL JOIN (%s) AS two ON one.stopareacode = two.stopareacode ORDER BY one.myorder, two.myorder desc;" % (sql_one, sql_two)
else:
sql = sql_one
cur = connect()
cur.execute(sql)
return cur.fetchall()
def alignJourneyPatternsSQL(operationdate, dataownercode, lineplanningnumber):
priorities = getJourneyPatternPriority(operationdate, dataownercode,
lineplanningnumber)
if len(priorities) == 0:
return
data = {1: [], 2: []}
highest = priorities[0][0]
order = 0
for linedirection, localservicelevelcode, journeypatterncode, priority in priorities:
order += 1
data[linedirection].append((
order,
"(select distinct userstopcode, userstopordernumber from localservicegrouppasstime as p where dataownercode = '%s' and localservicelevelcode = '%s' and journeypatterncode = '%s') as q%d"
%
(dataownercode, localservicelevelcode, journeypatterncode, order)))
sql_one = alignOneDirectionJourneyPatterns(data[highest], dataownercode)
second = data[(set(data.keys()) - set([highest])).pop()]
if len(second) > 0:
sql_two = alignOneDirectionJourneyPatterns(second, dataownercode)
sql = "SELECT one.*, two.* FROM (%s) AS one FULL JOIN (%s) AS two ON one.stopareacode = two.stopareacode ORDER BY one.myorder, two.myorder desc;" % (
sql_one, sql_two)
else:
sql = sql_one
cur = connect()
cur.execute(sql)
return cur.fetchall()
def getLines(dataownercode):
cur = connect()
output = {}
cur.execute(
"select distinct transporttype from line where dataownercode = %s",
(dataownercode, ))
transporttypes = cur.fetchall()
for transporttype in transporttypes:
transporttype = transporttype[0]
cur.execute(
"select distinct linepublicnumber, linename, lineplanningnumber from line where dataownercode = %s and transporttype = %s order by linepublicnumber",
(
dataownercode,
transporttype,
))
lines = cur.fetchall()
lines_final = []
for line in lines:
try:
lines_final.append((int(line[0]), line[1], line[2]))
except:
lines_final.append((line[0], line[1], line[2]))
output[transporttype] = sorted(lines_final, key=itemgetter(0))
return output
def getJourneyPatternPriority(operationdate, dataownercode,
lineplanningnumber):
cur = connect()
cur.execute(
"select p.linedirection, p.localservicelevelcode, p.journeypatterncode, max(userstopordernumber) as priority from localservicegroupvalidity as v, localservicegrouppasstime as p where v.dataownercode = p.dataownercode and v.localservicelevelcode = p.localservicelevelcode and v.operationdate = %s and v.dataownercode = %s and lineplanningnumber = %s group by p.localservicelevelcode, p.journeypatterncode, p.linedirection order by localservicelevelcode, priority desc, linedirection;",
(operationdate, dataownercode, lineplanningnumber))
return cur.fetchall()
def showLines(today, dataownercode):
cur = connect()
cur.execute("SELECT lineplanningnumber, transporttype, linepublicnumber, linename FROM line WHERE dataownercode = %s and lineplanningnumber = '6'", (dataownercode,))
lines = cur.fetchall()
for lineplanningnumber, transporttype, linepublicnumber, linename in lines:
print transporttype, linepublicnumber, '-', linename, lineplanningnumber
aligned = alignJourneyPatterns(today, dataownercode, lineplanningnumber)
if aligned is None:
print 'No pattern'
else:
print html(dataownercode, aligned)
def getLines(today, dataownercode):
cur = connect()
output = {}
cur.execute("SELECT lineplanningnumber, transporttype, linepublicnumber, linename FROM line WHERE dataownercode = %s", (dataownercode,))
lines = cur.fetchall()
for lineplanningnumber, transporttype, linepublicnumber, linename in lines:
aligned = alignJourneyPatterns(today, dataownercode, lineplanningnumber)
if aligned is not None:
output[lineplanningnumber] = {'transporttype': transporttype, 'lineplanningnumber': lineplanningnumber, 'linepublicnumber': linepublicnumber, 'linename': linename, 'aligned': html(dataownercode, aligned)}
return output
def showLines(today, dataownercode):
cur = connect()
cur.execute(
"SELECT lineplanningnumber, transporttype, linepublicnumber, linename FROM line WHERE dataownercode = %s and lineplanningnumber = '6'",
(dataownercode, ))
lines = cur.fetchall()
for lineplanningnumber, transporttype, linepublicnumber, linename in lines:
print transporttype, linepublicnumber, '-', linename, lineplanningnumber
aligned = alignJourneyPatterns(today, dataownercode,
lineplanningnumber)
if aligned is None:
print 'No pattern'
else:
print html(dataownercode, aligned)
def html(dataownercode, aligned):
cur = connect()
cur.execute(
"SELECT userstopcode, timingpointname FROM usertimingpoint AS u, timingpoint AS t WHERE u.dataownercode = %s AND u.timingpointdataownercode = t.dataownercode AND u.timingpointcode = t.timingpointcode;",
(dataownercode, ))
stops = {}
for x in cur.fetchall():
stops[x[0]] = x[1]
if len(aligned[0]) == 2:
output = '<table class="lijn"><tr><th class="left"><button class="btn btn-success btn-mini" onclick="selecteer(0);"><i class="icon-arrow-down icon-white"></i></th><th><button class="btn btn-success btn-mini" onclick="selecteer(2);"><i class="icon-resize-horizontal icon-white"></i></th><th class="right"><button class="btn btn-success btn-mini" onclick="selecteer(1);"><i class="icon-arrow-up icon-white"></i></th></tr>'
for x, y in aligned:
#if (x[2] is not None and x[2] == y[2]) or (x[0] == y[0]) or (stops[x[0]] == stops[y[0]]):
if x is not None and y is not None:
output += '<tr><td class="left"><button type="button" data-toggle="button" class-toggle="btn-success" class="btn btn-primary btn-mini" id="%(userstopcode1)s">%(timingpointname1)s</button></td><td><button class="btn btn-success btn-mini" onclick="selecteerHaltes(this);"><i class="icon-resize-horizontal icon-white"></i></td><td class="right"><button type="button" data-toggle="button" class-toggle="btn-success" class="btn btn-primary btn-mini" id="%(userstopcode2)s">%(timingpointname2)s</button></td></tr>\n' % {
'dataownercode': dataownercode,
'userstopcode1': x[0],
'timingpointname1': stops[x[0]],
'userstopcode2': y[0],
'timingpointname2': stops[y[0]]
}
elif x is not None:
output += '<tr><td class="left"><button type="button" data-toggle="button" class-toggle="btn-success" class="btn btn-primary btn-mini" id="%(userstopcode1)s">%(timingpointname1)s</button></td><td></td><td></td></tr>\n' % {
'dataownercode': dataownercode,
'userstopcode1': x[0],
'timingpointname1': stops[x[0]]
}
elif y is not None:
output += '<tr><td></td><td></td><td class="right"><button type="button" data-toggle="button" class-toggle="btn-success" class="btn btn-primary btn-mini" id="%(userstopcode2)s">%(timingpointname2)s</button></td></tr>\n' % {
'dataownercode': dataownercode,
'userstopcode2': y[0],
'timingpointname2': stops[y[0]]
}
output += '</table>'
return output
else:
return '<table class="lijn"><tr><th class="left"><button class="btn btn-success btn-mini" onclick="selecteer(0);"><i class="icon-arrow-down icon-white"></i></th></tr>' + ''.join(
[
'<tr><td class="left"><button type="button" data-toggle="button" class-toggle="btn-success" class="btn btn-primary btn-mini" id="%s_%s">%s</button></td></tr>\n'
% (dataownercode, x, stops[x]) for x in aligned
]) + '</table>'
def getLines(dataownercode):
cur = connect()
output = {}
cur.execute("select distinct transporttype from line where dataownercode = %s", (dataownercode,) )
transporttypes = cur.fetchall()
for transporttype in transporttypes:
transporttype = transporttype[0]
cur.execute("select distinct linepublicnumber, linename, lineplanningnumber from line where dataownercode = %s and transporttype = %s order by linepublicnumber", (dataownercode, transporttype,) )
lines = cur.fetchall()
lines_final = []
for line in lines:
try:
lines_final.append((int(line[0]),line[1],line[2]))
except:
lines_final.append((line[0],line[1],line[2]))
output[transporttype] = sorted(lines_final, key=itemgetter(0))
return output
def getLines(today, dataownercode):
cur = connect()
output = {}
cur.execute(
"SELECT lineplanningnumber, transporttype, linepublicnumber, linename FROM line WHERE dataownercode = %s",
(dataownercode, ))
lines = cur.fetchall()
for lineplanningnumber, transporttype, linepublicnumber, linename in lines:
aligned = alignJourneyPatterns(today, dataownercode,
lineplanningnumber)
if aligned is not None:
output[lineplanningnumber] = {
'transporttype': transporttype,
'lineplanningnumber': lineplanningnumber,
'linepublicnumber': linepublicnumber,
'linename': linename,
'aligned': html(dataownercode, aligned)
}
return output
def html(dataownercode, aligned):
cur = connect()
cur.execute("SELECT userstopcode, timingpointname FROM usertimingpoint AS u, timingpoint AS t WHERE u.dataownercode = %s AND u.timingpointdataownercode = t.dataownercode AND u.timingpointcode = t.timingpointcode;", (dataownercode,))
stops = {}
for x in cur.fetchall():
stops[x[0]] = x[1]
if len(aligned[0]) == 2:
output = '<table class="lijn"><tr><th class="left"><button class="btn btn-success btn-mini" onclick="selecteer(0);"><i class="icon-arrow-down icon-white"></i></th><th><button class="btn btn-success btn-mini" onclick="selecteer(2);"><i class="icon-resize-horizontal icon-white"></i></th><th class="right"><button class="btn btn-success btn-mini" onclick="selecteer(1);"><i class="icon-arrow-up icon-white"></i></th></tr>'
for x, y in aligned:
#if (x[2] is not None and x[2] == y[2]) or (x[0] == y[0]) or (stops[x[0]] == stops[y[0]]):
if x is not None and y is not None:
output += '<tr><td class="left"><button type="button" data-toggle="button" class-toggle="btn-success" class="btn btn-primary btn-mini" id="%(userstopcode1)s">%(timingpointname1)s</button></td><td><button class="btn btn-success btn-mini" onclick="selecteerHaltes(this);"><i class="icon-resize-horizontal icon-white"></i></td><td class="right"><button type="button" data-toggle="button" class-toggle="btn-success" class="btn btn-primary btn-mini" id="%(userstopcode2)s">%(timingpointname2)s</button></td></tr>\n'%{'dataownercode': dataownercode, 'userstopcode1': x[0], 'timingpointname1': stops[x[0]], 'userstopcode2': y[0], 'timingpointname2': stops[y[0]]}
elif x is not None:
output += '<tr><td class="left"><button type="button" data-toggle="button" class-toggle="btn-success" class="btn btn-primary btn-mini" id="%(userstopcode1)s">%(timingpointname1)s</button></td><td></td><td></td></tr>\n'%{'dataownercode': dataownercode, 'userstopcode1': x[0], 'timingpointname1': stops[x[0]]}
elif y is not None:
output += '<tr><td></td><td></td><td class="right"><button type="button" data-toggle="button" class-toggle="btn-success" class="btn btn-primary btn-mini" id="%(userstopcode2)s">%(timingpointname2)s</button></td></tr>\n'%{'dataownercode': dataownercode, 'userstopcode2': y[0], 'timingpointname2': stops[y[0]]}
output += '</table>'
return output
else:
return '<table class="lijn"><tr><th class="left"><button class="btn btn-success btn-mini" onclick="selecteer(0);"><i class="icon-arrow-down icon-white"></i></th></tr>'+''.join(['<tr><td class="left"><button type="button" data-toggle="button" class-toggle="btn-success" class="btn btn-primary btn-mini" id="%s_%s">%s</button></td></tr>\n'%(dataownercode, x, stops[x]) for x in aligned])+'</table>'
def getJourneyPatternPriority(operationdate, dataownercode, lineplanningnumber):
cur = connect()
cur.execute("select p.linedirection, p.localservicelevelcode, p.journeypatterncode, max(userstopordernumber) as priority from localservicegroupvalidity as v, localservicegrouppasstime as p where v.dataownercode = p.dataownercode and v.localservicelevelcode = p.localservicelevelcode and v.operationdate = %s and v.dataownercode = %s and lineplanningnumber = %s group by p.localservicelevelcode, p.journeypatterncode, p.linedirection order by localservicelevelcode, priority desc, linedirection;", (operationdate, dataownercode, lineplanningnumber) )
return cur.fetchall()