def record(self):
id = int(self.request.matchdict['id'])
self.set('mode', self.request.params.get('mode',False))
case = DBSession.query(Cases,Counties,Courts,ActionTypes,Archives,Containers,CallNumbers).filter(Cases.id==id) \
.filter(Cases.county==Counties.id) \
.filter(Cases.court==Courts.id) \
.filter(Cases.actiontype==ActionTypes.id) \
.filter(Cases.container==Containers.id) \
.filter(Cases.call_number==CallNumbers.id) \
.filter(Cases.archive==Archives.id).first()
entities = DBSession.query(Entities,Roles,Suffixs,Prefixs).filter(Entities.case_id==case.Cases.id) \
.filter(Entities.role==Roles.id) \
.filter(Entities.suffix==Suffixs.id) \
.filter(Entities.prefix==Prefixs.id).order_by('entity asc').all()
record = {
'case': case,
'verses' : self.determine_verses(entities, case.ActionTypes.actiontype),
'entities': entities,
'price': '${:,.2f}'.format( float(entities[0].Roles.price) ),
}
self.set('record', record)
return self.response
def search(self):
#names
self.set('entity', self.request.params.get('entity',''))
self.set('escaped_entity', self.request.params.get('entity','').replace('"','\\"').replace("'","\\'"))
self.set('firstname', self.request.params.get('firstname',''))
self.set('escaped_firstname', self.request.params.get('firstname','').replace('"','\\"').replace("'","\\'"))
self.set('keywords', self.request.params.get('keywords',''))
self.set('escaped_keywords', self.request.params.get('keywords','').replace('"','\\"').replace("'","\\'"))
self.set('mode', self.request.params.get('mode',False))
#dates
self.set('start_date', int(self.request.params.get('start_date', 0)))
self.set('end_date', int(self.request.params.get('end_date', datetime.datetime.today().year)))
sd = DBSession.query(Cases).filter(Cases.year > 1800).order_by('year asc').first()
if sd:
self.set('earliest_recorded_date', sd.year)
else:
self.set('earliest_recorded_date', 1800)
ed = DBSession.query(Cases).filter(Cases.year < datetime.datetime.today().year).order_by('year desc').first()
if ed:
self.set('last_recorded_date', ed.year)
else:
self.set('last_recorded_date', datetime.datetime.today().year)
#types
actiontypes = [ int(at) for at in self.request.params.getall('actiontypes') ]
self.set('actiontypes', actiontypes)
self.set('actiontypes_list', ActionTypes.loadAll(order='actiontype asc'))
#counties
counties = [ int(at) for at in self.request.params.getall('counties') ]
self.set('counties', counties)
self.set('counties_list', Counties.loadAll(order='county asc'))
#roles
roles = [ int(r) for r in self.request.params.getall('roles') ]
self.set('roles', roles)
self.set('roles_list', Roles.loadAll(order='role asc'))
#archives
archives = [ int(l) for l in self.request.params.getall('archives') ]
self.set('archives', archives)
self.set('archives_list', Archives.loadAll(order='name asc'))
#courts
courts = [ int(l) for l in self.request.params.getall('courts') ]
self.set('courts', courts)
self.set('courts_list', Courts.loadAll(order='court asc'))
return self.response
def basket(self):
self.set('records', [])
if 'submit' in self.request.params:
return self.create_invoice()
else:
items = self.request.cookies.get('basket',False)
if items:
items = json.loads(urllib2.unquote(items))
results = []
price, total_price = 0.0, 0.0
for k,v in items.items():
entity = DBSession.query(Entities,Roles).filter(Entities.case_id==int(v)).filter(Entities.role==Roles.id).first()
price = float(entity.Roles.price)
total_price += price
results.append({
'id': v,
'price': '${:,.2f}'.format(price),
})
self.set('records', results)
self.set('total_price', '${:,.2f}'.format(total_price))
self.set('message_before_ordering', Config.get('message_before_ordering'))
return self.response
def manage_cases_get(self):
order_headings = ['cases.id',
'cases.actiontype',
'cases.case_number',
'cases.year',
'cases.container_number',
'cases.subset',
'cases.county',
'cases.notes']
# Determine Order
order_index = int(self.request.params.get('order[0][column]', 0))
order = order_headings[order_index] + ' ' + self.request.params.get('order[0][dir]', 'asc')
limit = int(self.request.params.get('length', 15))
offset = int(self.request.params.get('start',0))
query = DBSession.query(Cases,Counties,ActionTypes,Archives,Courts,CallNumbers,Containers) \
.filter(Cases.actiontype == ActionTypes.id) \
.filter(Cases.county == Counties.id) \
.filter(Cases.archive == Archives.id) \
.filter(Cases.court == Courts.id) \
.filter(Cases.call_number == CallNumbers.id) \
.filter(Cases.container == Containers.id)
count = query.count() # get total number of records
objs = query.order_by(order).offset(offset).limit(limit).all()
results = {"recordsTotal": count,
"recordsFiltered": count,
"data": []
}
for obj in objs:
results['data'].append([
obj.Cases.id,
obj.ActionTypes.actiontype,
obj.Cases.case_number,
obj.Cases.year,
obj.Containers.container,
obj.Cases.container_number,
obj.Cases.subset,
obj.Counties.county,
obj.Courts.court,
obj.CallNumbers.call_number,
obj.Archives.name,
obj.Cases.notes
])
return results
def problem_finder(self):
return {
'entities_no_case' : DBSession.query(Entities).filter(Entities.case_id == 0).all(),
'entities_no_prefix' : DBSession.query(Entities).filter(Entities.prefix == 0).all(),
'entities_no_suffix' : DBSession.query(Entities).filter(Entities.suffix == 0).all(),
'entities_no_role' : DBSession.query(Entities).filter(Entities.role == 0).all(),
'case_year_issues' : DBSession.query(Cases).filter(Cases.year != 0).filter(or_( Cases.year < 1800 , Cases.year > date.today().year)).all(),
'case_no_county' : DBSession.query(Cases).filter(Cases.county == 0).all(),
'case_no_actiontype' : DBSession.query(Cases).filter(Cases.actiontype == 0).all(),
#'case_no_case_number' : DBSession.query(Cases).filter(Cases.case_number == '').all(),
}
def manage_records_previous(self):
case_id = int(self.request.params.get('case',''))
call_number = int(self.request.params.get('call_number',''))
cases = DBSession.query(Cases).filter(Cases.call_number == call_number).order_by('id asc').all()
previous = 0
for i, case in enumerate(cases):
if case.id == case_id:
previous = i-1
break
if previous == -1:
previous = len(cases)-1
return HTTPFound(location=route_url('manage_records', self.request, case_id=cases[previous].id))
def manage_edit_data(self):
table = str(self.request.matchdict['table'])
table_class = Import('courtrecords.models', table)
id = self.request.matchdict['id']
has_interface(table_class,'__scaffold__')
if 'form.submit' in self.request.params or 'form.submit.list' in self.request.params:
object = DBSession.query(table_class).filter(getattr(table_class,'id')==id).first()
for p in self.request.params:
name = p.replace('form.', '') # all incoming form params are padding with this key
if hasattr(object, name) and filter(lambda d: name in d, table_class.__scaffold__): # safety check
setattr(object, name, self.request.params.get(p))
DBSession.flush()
transaction.commit()
if 'form.submit.list' in self.request.params:
return HTTPFound(location=route_url('manage_list_data', self.request, table=table))
object = DBSession.query(table_class).filter(getattr(table_class,'id')==id).first()
html = self._element_generator(table_class.__scaffold__, object)
self.set('form',html)
self.set('table', table)
if table.endswith('ies'):
self.set('table_singular', table[:-3] + 'y')
elif table.endswith('es'):
self.set('table_singular', table[:-2])
elif table.endswith("'s"):
self.set('table_singular', table[:-2])
elif table.endswith('s'):
self.set('table_singular', table[:-1])
else:
self.set('table_singular', table)
return self.response
def manage_records_next(self):
case_id = int(self.request.params.get('case',''))
call_number = int(self.request.params.get('call_number',''))
cases = DBSession.query(Cases).filter(Cases.call_number == call_number).order_by('id asc').all()
next = 0
for i, case in enumerate(cases):
if case.id == case_id:
next = i+1
break
if next == len(cases):
next = 0
return HTTPFound(location=route_url('manage_records', self.request, case_id=cases[next].id))
def manage_entities_get(self):
order_headings = ['entities.id',
'entities.case_id',
'entities.entity',
'entities.prefix',
'entities.lastname',
'entities.middlename',
'entities.firstname',
'entities.suffix',
'entities.alternatename',
'entities.role'
]
# Determine Order
order_index = int(self.request.params.get('order[0][column]', 0))
order = order_headings[order_index] + ' ' + self.request.params.get('order[0][dir]', 'asc')
limit = int(self.request.params.get('length', 15))
offset = int(self.request.params.get('start',0))
query = DBSession.query(Entities,Roles,Prefixs,Suffixs).filter(Entities.role == Roles.id) \
.filter(Entities.suffix == Suffixs.id) \
.filter(Entities.prefix == Prefixs.id)
count = query.count() # get total number of records
objs = query.order_by(order).offset(offset).limit(limit).all()
results = {"recordsTotal": count,
"recordsFiltered": count,
"data": []
}
for obj in objs:
results['data'].append([
obj.Entities.id,
obj.Entities.case_id,
obj.Entities.entity,
obj.Prefixs.prefix,
obj.Entities.lastname,
obj.Entities.middlename,
obj.Entities.firstname,
obj.Suffixs.suffix,
obj.Entities.alternatename,
obj.Roles.role,
'<a class="button red warning" href="' + self.request.application_url + '/manage/data/Entities/delete/' + str(obj.Entities.id) + '?back=' + self.request.application_url + '/manage/entities">Delete</a>'
])
return results
def manage_delete_data(self):
table = str(self.request.matchdict['table'])
table_class = Import('courtrecords.models', table)
back = self.request.params.get('back','')
id = int(self.request.matchdict['id'])
#has_interface(table_class,'__scaffold__')
obj = DBSession.query(table_class).filter(getattr(table_class,'id')==id).first()
DBSession.delete(obj)
transaction.commit()
if back:
return HTTPFound(location=back)
return HTTPFound(location=route_url('manage_list_data', self.request, table=table))
def manage_utilities(self):
if self.request.params.get('edit.Form.reindex.year',''):
cases = DBSession.query(Cases).all()
for case in cases:
print str(case.id)
year = str(case.year_text)
if not year:
year = str(case.year) # treat as string like input
year_only, full_date = self._determine_year(year)
case.year = int(year_only)
case.year_text = str(full_date)
DBSession.flush()
transaction.commit()
return self.response
def manage(self):
self.set('counties_count', self.fast_count(DBSession.query(Counties)))
self.set('actiontypes_count', self.fast_count(DBSession.query(ActionTypes)))
self.set('roles_count', self.fast_count(DBSession.query(Roles)))
self.set('cases_count', self.fast_count(DBSession.query(Cases)))
self.set('entities_count', self.fast_count(DBSession.query(Entities)))
complete_status = Statuses.load(order='priority desc')
self.set('orders_open', self.fast_count(DBSession.query(Invoices).filter(Invoices.status != complete_status.id)))
self.set('orders_closed', self.fast_count(DBSession.query(Invoices).filter(Invoices.status == complete_status.id)))
self.set('problems', self.problem_finder())
return self.response
def manage_getlist_data(self):
table = str(self.request.matchdict['table'])
table_class = Import('courtrecords.models', table)
# Generate table header
header_unique = []
for s in table_class.__scaffold__:
for k,v in s.items():
header_unique.append(k)
# Determine Order
order_index = int(self.request.params.get('order[0][column]', 0))
order = header_unique[order_index] + ' ' + self.request.params.get('order[0][dir]', 'asc')
limit = int(self.request.params.get('length', 15))
offset = int(self.request.params.get('start',0))
query = DBSession.query(table_class)
count = query.count() # get total number of records
objs = query.order_by(order).offset(offset).limit(limit).all()
results = {"recordsTotal": count,
"recordsFiltered": count,
"data": []
}
for obj in objs:
tmp = []
for h in header_unique:
tmp.append(getattr(obj, h))
tmp.append('') # empty for "option" column
results['data'].append(tmp)
return results
def manage_search_quick(self):
firstname = self.request.params.get('firstname','')
entity = self.request.params.get('entity','')
query = DBSession.query(Cases,Entities,Counties,ActionTypes) \
.filter(Cases.county==Counties.id) \
.filter(Cases.actiontype==ActionTypes.id) \
.filter(Entities.case_id==Cases.id)
if entity:
query = query.filter(Entities.entity.like('%'+entity+'%')) #only do % right side, otherwise will ignore sql indexing
if firstname:
query = query.filter(Entities.firstname.like(firstname+'%')) #only do % right side, otherwise will ignore sql indexing
query = query.order_by('entity asc')
results = query.all()
entities = []
for result in results:
entities.append({'entity': Result2Dict(result.Entities),
'case': Result2Dict(result.Cases),
'actiontype': Result2Dict(result.ActionTypes),
'county': Result2Dict(result.Counties) })
return entities
def manage_records(self):
case_id = self.request.matchdict['case_id']
if case_id != 'new':
case_id = int(case_id)
# Add last entry for new entries
self.set('lastentry',case_id)
lastcase = Cases.load(order='id desc')
if lastcase:
self.set('lastentry',lastcase.id)
# Handle Saving
if 'edit.Form.submit.save' in self.request.params or 'edit.Form.submit.savenext' in self.request.params:
self._save(case_id)
if 'edit.Form.submit.savenext' in self.request.params:
return HTTPFound(location=route_url('manage_records', self.request, case_id='new'))
return HTTPFound(location=route_url('manage_records', self.request, case_id=case_id))
# Handle Loading Information on screen
if case_id == 'new' or case_id == 0:
case = None
else:
case = DBSession.query(Cases,Counties,ActionTypes,Archives).filter(Cases.id == case_id) \
.filter(Cases.actiontype == ActionTypes.id) \
.filter(Cases.county == Counties.id) \
.filter(Cases.archive == Archives.id) \
.first()
entities = []
if case_id != 'new':
entities = DBSession.query(Entities,Roles).filter(Entities.case_id == case_id) \
.filter(Entities.role == Roles.id) \
.order_by('entity asc').all()
self.set('case',case)
self.set('year',self._get_year)
self.set('case_id',case_id)
self.set('entities',entities)
self.set('actiontypes',ActionTypes.loadAll(order="actiontype asc"))
self.set('containers',Containers.loadAll(order="container asc"))
self.set('courts',Courts.loadAll(order="court asc"))
self.set('call_numbers',CallNumbers.loadAll(order="call_number asc"))
self.set('archives',Archives.loadAll(order="name asc"))
self.set('roles',Roles.loadAll(order="role asc"))
self.set('counties',Counties.loadAll(order="county asc"))
self.set('prefixs',Prefixs.loadAll(order="prefix asc"))
self.set('suffixs',Suffixs.loadAll(order="suffix asc"))
#${request.application_url}/manage/data/Entities/delete/${entity.Entities.id}?back=${request.url}
delete_url = ''
if case:
entity_base_url = self.request.application_url + '/manage/data/Entities/delete/'
case_base_url = self.request.application_url + '/manage/data/Cases/delete/'
delete_url = case_base_url + str(case.Cases.id) + '?back='
for e in entities:
delete_url += entity_base_url + str(e.Entities.id) + '?back='
delete_url += self.request.application_url + '/manage/cases' # Back to base
self.set('delete_url', delete_url)
return self.response
def get_results(self):
order_headings = ['entities.id','entities.entity','entities.middlename','entities.firstname','entities.role','cases.year','cases.county']
order_index = int(self.request.params.get('order[0][column]',0))
order = order_headings[order_index] + ' ' + self.request.params.get('order[0][dir]', 'asc')
entity = HTMLParser.HTMLParser().unescape(self.request.params.get('entity',''))
firstname = HTMLParser.HTMLParser().unescape(self.request.params.get('firstname',''))
keywords = HTMLParser.HTMLParser().unescape(self.request.params.get('keywords',''))
start_date = int(self.request.params.get('start_date', 0))
end_date = int(self.request.params.get('end_date', datetime.datetime.today().year))
case_types = [ int(ct) for ct in self.request.params.getall('case_types[]') if ct ]
counties = [ int(c) for c in self.request.params.getall('counties[]') if c ]
roles = [ int(r) for r in self.request.params.getall('roles[]') if r ]
archives = [ int(l) for l in self.request.params.getall('archives[]') if l ]
courts = [ int(c) for c in self.request.params.getall('courts[]') if c ]
offset = int(self.request.params.get('start',0))
limit = int(self.request.params.get('length',15))
# Search Query Logic: Must be done outside basic .load because of join case
query = DBSession.query(Cases,Entities,Roles,Counties,ActionTypes,Archives,Courts,Containers) \
.filter(Cases.court==Courts.id) \
.filter(Cases.actiontype==ActionTypes.id) \
.filter(Cases.archive==Archives.id) \
.filter(Cases.county==Counties.id) \
.filter(Cases.container==Containers.id) \
.filter(Entities.case_id==Cases.id) \
.filter(Entities.role==Roles.id)
if entity:
query = query.filter(Entities.entity.like('%'+entity+'%')) #only do % right side, otherwise will ignore sql indexing
if firstname:
query = query.filter(Entities.firstname.like(firstname+'%')) #only do % right side, otherwise will ignore sql indexing
if keywords:
words = keywords.replace(',',' ').split(' ')
for word in words:
query = query.filter(or_(Cases.notes.like('%' + word.strip() + '%'), Entities.alternatename.like('%' + word.strip() + '%'))) # will be slow
if start_date >= 0 and end_date:
query = query.filter(and_(Cases.year <= end_date, Cases.year >= start_date))
if case_types:
clauses = []
for case_type in case_types:
if case_type != 0: # 0 is reserved for All
clauses.append( (Cases.actiontype == case_type) )
query = query.filter(or_(*clauses))
if counties:
clauses = []
for county in counties:
if county != 0: # 0 is reserved for All
clauses.append( (Cases.county == county) )
query = query.filter(or_(*clauses))
if roles:
clauses = []
for role in roles:
if role != 0: # 0 is reserved for All
clauses.append( (Entities.role == role) )
query = query.filter(or_(*clauses))
if archives:
clauses = []
for archive in archives:
if archive != 0: # 0 is reserved for All
clauses.append( (Cases.archive == archive) )
query = query.filter(or_(*clauses))
if courts:
clauses = []
for court in courts:
if court != 0: # 0 is reserved for All
clauses.append( (Cases.court == court) )
query = query.filter(or_(*clauses))
# Do not count with query.count() because it is slow. It avgs 3s a lookup
count = self.fast_count(query.with_entities(Cases.id)) #worst case is 2s
facets = []
if offset == 0: # only rebuild facets on start of new search
facets = self.make_facets(query.with_entities(Cases.id)) # must be done before offset and limit
# order, limit, offset
query = query.order_by(order) # set the order
objs = query.offset(offset).limit(limit).all() # offset and limit records for better lookup speed
results = {"recordsTotal": count,
"recordsFiltered": count,
"facets": facets,
"data": []
}
for obj in objs:
results['data'].append(
[obj.Entities.id,
obj.Entities.entity,
obj.Entities.firstname,
obj.Entities.middlename,
obj.Roles.role,
obj.Cases.year,
obj.Counties.county,
'', # image row, just ignore
{'_case': Result2Dict(obj.Cases),
'_entity':Result2Dict(obj.Entities),
'_county': Result2Dict(obj.Counties),
'_role': Result2Dict(obj.Roles),
'_court': Result2Dict(obj.Courts),
'_actiontype': Result2Dict(obj.ActionTypes),
'_container': Result2Dict(obj.Containers),
'_archive': Result2Dict(obj.Archives),
'_other_entities':self.get_other_entities(obj.Entities),
}
]
)
return results
def get(cls, name,purify=False):
text = DBSession.query(cls).filter(cls.name == name).first().value
if purify:
text = re.sub('<[^<]+?>', '', text)
return text