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_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_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 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 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_add_data(self): table = str(self.request.matchdict['table']) table_class = Import('courtrecords.models', table) has_interface(table_class,'__scaffold__') if 'form.submit' in self.request.params or 'form.submit.another' in self.request.params: object = table_class() 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, object.__scaffold__): # safety check setattr(object, name, self.request.params.get(p)) DBSession.add(object) DBSession.flush() transaction.commit() if 'form.submit' in self.request.params: return HTTPFound(location=route_url('manage_list_data', self.request, table=table)) html = self._element_generator(table_class.__scaffold__, table_class()) 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(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 main(global_config, **settings): """ This function returns a Pyramid WSGI application. """ engine = engine_from_config(settings, "sqlalchemy.") DBSession.configure(bind=engine) warnings.filterwarnings("ignore", ".*use_labels*.", category=exc.SAWarning) session_factory = UnencryptedCookieSessionFactoryConfig("changelater") authorization_policy = ACLAuthorizationPolicy() authentication_policy = AuthTktAuthenticationPolicy("894983832", callback=groupfinder) config = Configurator( settings=settings, authentication_policy=authentication_policy, authorization_policy=authorization_policy, request_factory=RequestExtension, root_factory=RootACL, session_factory=session_factory, ) config.add_static_view("themes", "courtrecords:themes", cache_max_age=3600) # Public Stuff config.add_route("home", "/") config.add_route("about", "/about") config.add_route("contact", "/contact") config.add_route("login", "/login") config.add_route("logout", "/logout") config.add_route("register", "/register") config.add_route("search", "/search") config.add_route("get_results", "/search/get") config.add_route("basket", "/basket") config.add_route("invoice", "/invoice/{hash}") config.add_route("record", "/record/{id}") # Management Stuff config.add_route("manage", "/manage") config.add_route("manage_users", "/manage/users") config.add_route("manage_orders", "/manage/orders") config.add_route("manage_order", "/manage/orders/{id}") config.add_route("manage_list_data", "/manage/data/{table}") config.add_route("manage_getlist_data", "/manage/data/{table}/list") config.add_route("manage_add_data", "/manage/data/{table}/add") config.add_route("manage_edit_data", "/manage/data/{table}/edit/{id}") config.add_route("manage_delete_data", "/manage/data/{table}/delete/{id}") config.add_route("manage_entities", "/manage/entities") config.add_route("manage_entities_get", "/manage/entities/get") config.add_route("manage_cases", "/manage/cases") config.add_route("manage_cases_get", "/manage/cases/get") config.add_route("manage_records_next", "/manage/records/next") config.add_route("manage_records_previous", "/manage/records/previous") config.add_route("manage_records", "/manage/records/{case_id}") config.add_route("manage_importer", "/manage/importer") config.add_route("manage_search", "/manage/search") config.add_route("manage_search_quick", "/manage/search/quick") config.add_route("manage_utilities", "/manage/utilities") # System Stuff config.add_route("cronjob_anonymize", "/cronjob/anonymize") # Basic Authentication Tests config.add_route("test_anon", "/test/anonymous") config.add_route("test_auth", "/test/authenticated") config.add_route("test_edit", "/test/editor") config.add_route("test_admin", "/test/administrator") config.include("pyramid_mailer") config.scan() return config.make_wsgi_app()
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
def add_user(self): if self.__class__.__name__ == 'Users': DBSession.add(self) transaction.commit()