def recycle_journeyids(conn,data):
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute("""
create temporary table NewJourney(
id bigserial primary key NOT NULL,
privatecode varchar(255) NOT NULL,
operator_id varchar(255) NOT NULL,
availabilityconditionRef integer NOT NULL,
journeypatternref integer NOT NULL,
timedemandgroupref integer NOT NULL,
productCategoryRef integer,
noticeassignmentRef integer,
departuretime integer,
blockref varchar(255),
name varchar(255),
lowfloor boolean,
hasLiftOrRamp boolean,
haswifi boolean,
bicycleAllowed boolean,
onDemand boolean,
isvirtual boolean default(false)
);
""")
for key,journey in data['JOURNEY'].items():
journey = deepcopy(journey)
setRefsDict(journey,data['AVAILABILITYCONDITION'],'availabilityconditionref')
setRefsDict(journey,data['JOURNEYPATTERN'],'journeypatternref')
setRefsDict(journey,data['TIMEDEMANDGROUP'],'timedemandgroupref')
setRefsDict(journey,data['NOTICEASSIGNMENT'],'noticeassignmentref',ignore_null=True)
setRefsDict(journey,data['PRODUCTCATEGORY'],'productcategoryref')
exists,id = simple_dict_insert(conn,'NEWJOURNEY',journey,check_existing=False,return_id=True)
cur.execute("""
SELECT jn.operator_id,jo.id,jn.id as tmp_id
FROM
journey as jo,newjourney as jn
WHERE
jo.departuretime = jn.departuretime AND
jo.privatecode = jn.privatecode AND
jo.availabilityconditionref NOT IN (SELECT DISTINCT availabilityconditionref FROM availabilityconditionday WHERE isavailable = true)
""")
for row in cur.fetchall():
data['JOURNEY'][row['operator_id']]['id'] = row['id']
cur.execute("delete from newjourney where id = %s",[row['tmp_id']])
cur.execute("delete from journeytransfers where journeyref = %s or onwardjourneyref = %s",[row['id']]*2)
def recycle_journeyids(conn, data):
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute("""
create temporary table NewJourney(
id bigserial primary key NOT NULL,
privatecode varchar(255) NOT NULL,
operator_id varchar(255) NOT NULL,
availabilityconditionRef integer,
journeypatternref integer,
timedemandgroupref integer,
productCategoryRef integer,
noticeassignmentRef integer,
departuretime integer,
blockref varchar(255),
name varchar(255),
lowfloor boolean,
hasLiftOrRamp boolean,
haswifi boolean,
bicycleAllowed boolean,
onDemand boolean,
isvirtual boolean default(false)
);
""")
for key, journey in data['JOURNEY'].items():
journey = deepcopy(journey)
setRefsDict(journey, data['AVAILABILITYCONDITION'],
'availabilityconditionref')
setRefsDict(journey, data['JOURNEYPATTERN'], 'journeypatternref')
setRefsDict(journey, data['TIMEDEMANDGROUP'], 'timedemandgroupref')
setRefsDict(journey,
data['NOTICEASSIGNMENT'],
'noticeassignmentref',
ignore_null=True)
setRefsDict(journey, data['PRODUCTCATEGORY'], 'productcategoryref')
exists, id = simple_dict_insert(conn,
'NEWJOURNEY',
journey,
check_existing=False,
return_id=True)
cur.execute(
"""
SELECT array_agg(distinct newjourney.availabilityconditionref) as availabilityconditions
FROM
journey JOIN (SELECT availabilityconditionref,array_agg(validdate ORDER BY validdate) as days
FROM availabilityconditionday GROUP BY availabilityconditionref) as jac USING (availabilityconditionref)
JOIN timedemandgroup as oj ON (oj.id = journey.timedemandgroupref)
JOIN journeypattern as ojp ON (ojp.id = journey.journeypatternref)
JOIN route AS orr ON (orr.id = ojp.routeref)
JOIN line AS ol ON (ol.id = orr.lineref)
JOIN (SELECT journeypatternref,array_agg(pointref ORDER BY pointorder) as points
FROM pointinjourneypattern GROUP BY journeypatternref) as jjp USING (journeypatternref)
,newjourney JOIN (SELECT availabilityconditionref,array_agg(validdate ORDER BY validdate) as days
FROM availabilityconditionday WHERE isavailable = true GROUP BY availabilityconditionref) as nac USING
(availabilityconditionref)
JOIN timedemandgroup as nt ON (nt.id = newjourney.timedemandgroupref)
JOIN journeypattern as njpp ON (njpp.id = newjourney.journeypatternref)
JOIN route AS nr ON (nr.id = njpp.routeref)
JOIN line AS nl ON (nl.id = nr.lineref)
JOIN (SELECT journeypatternref,array_agg(pointref ORDER BY pointorder) as points
FROM pointinjourneypattern GROUP BY journeypatternref) as njp USING (journeypatternref)
WHERE
journey.operator_id = newjourney.operator_id AND
--nac.days = jac.days AND
(jjp.points != njp.points OR oj.operator_id != nt.operator_id OR nl.operatorref != ol.operatorref);
""", [data['_validfrom']])
availabilityConditionrefs = cur.fetchone()['availabilityconditions']
print availabilityConditionrefs
if availabilityConditionrefs is None:
availabilityConditionrefs = []
print str(len(availabilityConditionrefs)) + ' calendars dirty'
cur.execute(
"""
UPDATE availabilityconditionday SET isavailable = false
WHERE availabilityConditionref = any(%s) AND validdate < %s AND availabilityconditionref in (SELECT id FROM availabilitycondition
WHERE versionref = %s)
;
""", [availabilityConditionrefs, data['_validfrom'], data['VERSION']['1']])
cur.execute("CREATE INDEX ON newjourney(operator_id)")
cur.execute(
"""
SELECT journey.operator_id,journey.id,newjourney.id as tmp_id
FROM
journey JOIN availabilitycondition as oac ON (oac.id = journey.availabilityconditionref)
,newjourney JOIN availabilitycondition as nac ON (nac.id = newjourney.availabilityconditionref)
WHERE
journey.operator_id = newjourney.operator_id AND
(%s = 0 or newjourney.availabilityconditionref != any(%s))
""", [len(availabilityConditionrefs), availabilityConditionrefs])
count = 0
for row in cur.fetchall():
count += 1
data['JOURNEY'][row['operator_id']]['id'] = row['id']
cur.execute("delete from newjourney where id = %s", [row['tmp_id']])
print str(count) + ' journeys recycled'
def recycle_journeyids(conn,data):
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute("""
create temporary table NewJourney(
id bigserial primary key NOT NULL,
privatecode varchar(255) NOT NULL,
operator_id varchar(255) NOT NULL,
availabilityconditionRef integer,
journeypatternref integer,
timedemandgroupref integer,
productCategoryRef integer,
noticeassignmentRef integer,
departuretime integer,
blockref varchar(255),
name varchar(255),
lowfloor boolean,
hasLiftOrRamp boolean,
haswifi boolean,
bicycleAllowed boolean,
onDemand boolean,
isvirtual boolean default(false)
);
""")
for key,journey in data['JOURNEY'].items():
journey = deepcopy(journey)
setRefsDict(journey,data['AVAILABILITYCONDITION'],'availabilityconditionref')
setRefsDict(journey,data['JOURNEYPATTERN'],'journeypatternref')
setRefsDict(journey,data['TIMEDEMANDGROUP'],'timedemandgroupref')
setRefsDict(journey,data['NOTICEASSIGNMENT'],'noticeassignmentref',ignore_null=True)
setRefsDict(journey,data['PRODUCTCATEGORY'],'productcategoryref')
exists,id = simple_dict_insert(conn,'NEWJOURNEY',journey,check_existing=False,return_id=True)
cur.execute("""
SELECT array_agg(distinct newjourney.availabilityconditionref) as availabilityconditions
FROM
journey JOIN (SELECT availabilityconditionref,array_agg(validdate ORDER BY validdate) as days
FROM availabilityconditionday GROUP BY availabilityconditionref) as jac USING (availabilityconditionref)
JOIN timedemandgroup as oj ON (oj.id = journey.timedemandgroupref)
JOIN journeypattern as ojp ON (ojp.id = journey.journeypatternref)
JOIN route AS orr ON (orr.id = ojp.routeref)
JOIN line AS ol ON (ol.id = orr.lineref)
JOIN (SELECT journeypatternref,array_agg(pointref ORDER BY pointorder) as points
FROM pointinjourneypattern GROUP BY journeypatternref) as jjp USING (journeypatternref)
,newjourney JOIN (SELECT availabilityconditionref,array_agg(validdate ORDER BY validdate) as days
FROM availabilityconditionday WHERE isavailable = true GROUP BY availabilityconditionref) as nac USING
(availabilityconditionref)
JOIN timedemandgroup as nt ON (nt.id = newjourney.timedemandgroupref)
JOIN journeypattern as njpp ON (njpp.id = newjourney.journeypatternref)
JOIN route AS nr ON (nr.id = njpp.routeref)
JOIN line AS nl ON (nl.id = nr.lineref)
JOIN (SELECT journeypatternref,array_agg(pointref ORDER BY pointorder) as points
FROM pointinjourneypattern GROUP BY journeypatternref) as njp USING (journeypatternref)
WHERE
journey.operator_id = newjourney.operator_id AND
--nac.days = jac.days AND
(jjp.points != njp.points OR oj.operator_id != nt.operator_id OR nl.operatorref != ol.operatorref);
""",[data['_validfrom']])
availabilityConditionrefs = cur.fetchone()['availabilityconditions']
print availabilityConditionrefs
if availabilityConditionrefs is None:
availabilityConditionrefs = []
print str(len(availabilityConditionrefs)) + ' calendars dirty'
cur.execute("""
UPDATE availabilityconditionday SET isavailable = false
WHERE availabilityConditionref = any(%s) AND validdate < %s AND availabilityconditionref in (SELECT id FROM availabilitycondition
WHERE versionref = %s)
;
""",[availabilityConditionrefs,data['_validfrom'],data['VERSION']['1']])
cur.execute("CREATE INDEX ON newjourney(operator_id)")
cur.execute("""
SELECT journey.operator_id,journey.id,newjourney.id as tmp_id
FROM
journey JOIN availabilitycondition as oac ON (oac.id = journey.availabilityconditionref)
,newjourney JOIN availabilitycondition as nac ON (nac.id = newjourney.availabilityconditionref)
WHERE
journey.operator_id = newjourney.operator_id AND
(%s = 0 or newjourney.availabilityconditionref != any(%s))
""",[len(availabilityConditionrefs),availabilityConditionrefs])
count = 0
for row in cur.fetchall():
count += 1
data['JOURNEY'][row['operator_id']]['id'] = row['id']
cur.execute("delete from newjourney where id = %s",[row['tmp_id']])
print str(count) + ' journeys recycled'
