def test_column_ordering_relation(self): """Test if a foreign key column is orderable.""" self.populate(5) user6, addr6 = self.create_user('000_Whatever', '000_aaa') user7, addr7 = self.create_user('zzz_Whatif', 'zzz_aaa') self.session.add(user6) self.session.add(user7) self.session.commit() columns = self.create_columns( ['id', 'name', 'address.description', 'created_at']) # DESC req = self.create_dt_params(order=[{"column": 2, "dir": "desc"}]) rowTable = DataTables(req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert res['data'][0]['2'] == 'zzz_aaa' # ASC req = self.create_dt_params(order=[{"column": 2, "dir": "asc"}]) rowTable = DataTables(req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert res['data'][0]['2'] == '000_aaa'
def test_list_users(self): """Test if it returns a simple users list.""" self.populate(5) columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('name')) columns.append(ColumnDT('address.description')) columns.append(ColumnDT('created_at')) # LATEST req = self.create_dt_params() rowTable = DataTables( req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert len(res['data']) == 5 assert res['recordsTotal'] == '5' assert res['recordsFiltered'] == '5' # LEGACY req = self.create_dt_legacy_params() rowTable = DataTables( req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert len(res['aaData']) == 5 assert res['iTotalRecords'] == '5' assert res['iTotalDisplayRecords'] == '5'
def test_ordering_relation(self): """Test if it returns a list when ordering a foreign key.""" columns = [ ColumnDT(User.id,), ColumnDT(User.name), ColumnDT(Address.description), ColumnDT(User.created_at)] query = self.session.query().select_from(User).join(Address) # Descending params = self.create_dt_params(columns, order=[{"column": 2, "dir": "desc"}]) rowTable = DataTables(params, query, columns) res = rowTable.output_result() assert res['data'][0]['1'] == 'UserLastAddress' assert res['data'][0]['2'] == 'zzz_Address' columns = [ ColumnDT(User.id,), ColumnDT(User.name), ColumnDT(Address.description), ColumnDT(User.created_at)] # Ascending params = self.create_dt_params(columns, order=[{"column": 2, "dir": "asc"}]) rowTable = DataTables(params, query, columns) res = rowTable.output_result() assert res['data'][0]['1'] == 'UserFirstAddress' assert res['data'][0]['2'] == '000_Address'
def usr_group_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': gid = 'gid' in req.GET and req.GET['gid'] or 0 columns = [ ColumnDT(User.id, mData='id'), ColumnDT(User.email, mData='email'), ColumnDT(User.user_name, mData='name'), ColumnDT(User.status, mData='status'), #, filter=_DTstatus ColumnDT(func.to_char(User.last_login_date, 'DD-MM-YYYY'), mData='last_login'), ColumnDT(func.to_char(User.registered_date, 'DD-MM-YYYY'), mData='registered'), ] query = DBSession.query().select_from(User).join(UserGroup).\ filter(User.id==UserGroup.user_id, UserGroup.group_id==gid) rowTable = DataTables(req.GET, query, columns) return rowTable.output_result() elif url_dict['act']=='member': columns = [] gid = 'gid' in params and params['gid'] or 0 columns.append(ColumnDT('id')) columns.append(ColumnDT('email')) columns.append(ColumnDT('user_name')) columns.append(ColumnDT('status')) query = DBSession.query(User.id, User.user_name, User.email, User.status, User.last_login_date, User.registered_date).\ join(UserGroup).filter(UserGroup.group_id==gid) rowTable = DataTables(req, User, query, columns) return rowTable.output_result()
def test_ordering_relation(session): """Test if it returns a list when ordering a foreign key.""" columns = [ ColumnDT(User.id, ), ColumnDT(User.name), ColumnDT(Address.description), ColumnDT(User.created_at), ] query = session.query().select_from(User).join(Address) # Descending params = create_dt_params(columns, order=[{"column": 2, "dir": "desc"}]) rowTable = DataTables(params, query, columns) res = rowTable.output_result() assert res["data"][0]["1"] == "UserLastAddress" assert res["data"][0]["2"] == "zzz_Address" columns = [ ColumnDT(User.id, ), ColumnDT(User.name), ColumnDT(Address.description), ColumnDT(User.created_at), ] # Ascending params = create_dt_params(columns, order=[{"column": 2, "dir": "asc"}]) rowTable = DataTables(params, query, columns) res = rowTable.output_result() assert res["data"][0]["1"] == "UserFirstAddress" assert res["data"][0]["2"] == "000_Address"
def test_column_ordering_relation(self): """Test if a foreign key column is orderable.""" self.populate(5) user6, addr6 = self.create_user('000_Whatever', '000_aaa') user7, addr7 = self.create_user('zzz_Whatif', 'zzz_aaa') self.session.add(user6) self.session.add(user7) self.session.commit() columns = self.create_columns(['id', 'name', 'address.description', 'created_at']) # DESC req = self.create_dt_params( order=[{"column": 2, "dir": "desc"}]) rowTable = DataTables( req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert res['aaData'][0]['2'] == 'zzz_aaa' # ASC req = self.create_dt_params(order=[{"column": 2, "dir": "asc"}]) rowTable = DataTables( req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert res['aaData'][0]['2'] == '000_aaa'
def view_act(request): req = request params = req.params url_dict = req.matchdict if url_dict['act'] == 'grid': columns = [] columns.append(ColumnDT('user_id')) columns.append(ColumnDT('unit_id')) columns.append(ColumnDT('user_name')) columns.append(ColumnDT('nama')) query = DBSession.query(UserUnit.user_id, UserUnit.unit_id, User.user_name, Unit.nama).\ join(User).join(Unit) rowTable = DataTables(req.GET, UserUnit, query, columns) return rowTable.output_result() if url_dict['act'] == 'grid1': cari = 'cari' in params and params['cari'] or '' columns = [] columns.append(ColumnDT('user_id')) columns.append(ColumnDT('unit_id')) columns.append(ColumnDT('user_name')) columns.append(ColumnDT('nama')) query = DBSession.query(UserUnit.user_id, UserUnit.unit_id, User.user_name, Unit.nama).\ join(User).join(Unit).\ filter(or_(User.user_name.ilike('%%%s%%' % cari),Unit.nama.ilike('%%%s%%' % cari))) rowTable = DataTables(req, UserUnit, query, columns) return rowTable.output_result()
def group_routes_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('group_id')) columns.append(ColumnDT('route_id')) columns.append(ColumnDT('groups.group_name')) columns.append(ColumnDT('routes.nama')) columns.append(ColumnDT('routes.path')) query = DBSession.query(GroupRoutePermission).join(Group).join(Route) rowTable = DataTables(req, GroupRoutePermission, query, columns) return rowTable.output_result() elif url_dict['act']=='akses': columns = [] gid = 'gid' in params and params['gid'] or 0 columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('nama')) columns.append(ColumnDT('checked')) subq1 = DBSession.query(Route.id.label('id'), Route.kode.label('kode'), Route.nama.label('nama'), sqlalchemy.sql.literal_column("0").label('checked')) subq2 = DBSession.query(Route.id.label('id'), Route.kode.label('kode'), Route.nama.label('nama'), case([(GroupRoutePermission.group_id==gid,1)], else_=0).label('checked') ).join(GroupRoutePermission ).filter(Route.id == GroupRoutePermission.route_id, GroupRoutePermission.group_id == gid ) q = subq1.union(subq2).subquery() query = DBSession.query(Route.id.label('id'), Route.kode.label('kode'), Route.nama.label('nama'), func.max(q.c.checked).label('checked') ).join(q, q.c.id == Route.id ).group_by(Route.id, Route.kode, Route.nama ) rowTable = DataTables(req, Route, query, columns) return rowTable.output_result()
def gaji_fungsi_urusan_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('fnama')) columns.append(ColumnDT('unama')) columns.append(ColumnDT('nama')) query = DBSession.query(FungsiUrusan.id, Fungsi.nama.label('fnama'), Urusan.nama.label('unama'), FungsiUrusan.nama, ).filter(FungsiUrusan.fungsi_id==Fungsi.id, FungsiUrusan.urusan_id==Urusan.id, ) rowTable = DataTables(req, FungsiUrusan, query, columns) return rowTable.output_result() elif url_dict['act']=='grid1': cari = 'cari' in params and params['cari'] or '' columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('fnama')) columns.append(ColumnDT('unama')) columns.append(ColumnDT('nama')) query = DBSession.query(FungsiUrusan.id, Fungsi.nama.label('fnama'), Urusan.nama.label('unama'), FungsiUrusan.nama, ).filter(FungsiUrusan.fungsi_id==Fungsi.id, FungsiUrusan.urusan_id==Urusan.id, or_(Fungsi.nama.ilike('%%%s%%' % cari), Urusan.nama.ilike('%%%s%%' % cari), FungsiUrusan.nama.ilike('%%%s%%' % cari)) ) rowTable = DataTables(req, FungsiUrusan, query, columns) return rowTable.output_result() elif url_dict['act']=='changeid': row = FungsiUrusan.get_by_id('fungsi_urusan_id' in params and params['fungsi_urusan_id'] or 0) if row: ses['fungsi_urusan_id']=row.id ses['fungsi_urusan_kd']=row.kode ses['fungsi_urusan_nm']=row.nama return {'success':True}
def plan_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('nama')) columns.append(ColumnDT('receipt_date', filter=_DTstrftime)) columns.append(ColumnDT('units.nama')) columns.append(ColumnDT('vendors.nama')) query = DBSession.query(ProductReceipt) rowTable = DataTables(req, ProductReceipt, query, columns) return rowTable.output_result() elif url_dict['act']=='grid1': cari = 'cari' in params and params['cari'] or '' columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('nama')) columns.append(ColumnDT('receipt_date', filter=_DTstrftime)) columns.append(ColumnDT('units.nama')) columns.append(ColumnDT('vendors.nama')) query = DBSession.query(ProductReceipt ).filter(ProductReceipt.vendor_id==Vendor.id, ProductReceipt.unit_id==Unit.id, or_(Vendor.nama.ilike('%%%s%%' % cari), Unit.nama.ilike('%%%s%%' % cari),)) rowTable = DataTables(req, ProductReceipt, query, columns) return rowTable.output_result() elif url_dict['act']=='hon_warehouse_receipt': term = 'term' in params and params['term'] or '' unit = 'unit_id' in params and params['unit_id'] or '' rows = DBSession.query(ProductReceipt.id, ProductReceipt.kode, ProductReceipt.nama ).filter(ProductReceipt.nama.ilike('%%%s%%' % term), ProductReceipt.unit_id==unit).all() r = [] for k in rows: d={} d['id'] = k[0] d['value'] = k[2] d['kode'] = k[1] d['nama'] = k[2] r.append(d) return r
def usr_unit_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('email')) columns.append(ColumnDT('user_name')) columns.append(ColumnDT('status')) columns.append(ColumnDT('last_login_date')) columns.append(ColumnDT('registered_date')) columns.append(ColumnDT('nama')) query = DBSession.query(User.id, User.user_name, User.email, User.status, User.last_login_date, User.registered_date, Unit.nama ).outerjoin(UserUnit ).outerjoin(Unit ).filter(UserUnit.user_id!='1',UserUnit.user_id!='2',) rowTable = DataTables(req, User, query, columns) return rowTable.output_result() elif url_dict['act']=='grid1': cari = 'cari' in params and params['cari'] or '' columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('email')) columns.append(ColumnDT('user_name')) columns.append(ColumnDT('status')) columns.append(ColumnDT('last_login_date')) columns.append(ColumnDT('registered_date')) columns.append(ColumnDT('nama')) query = DBSession.query(User.id, User.user_name, User.email, User.status, User.last_login_date, User.registered_date, Unit.nama ).outerjoin(UserUnit ).outerjoin(Unit ).filter(UserUnit.user_id!='1', UserUnit.user_id!='2', UserUnit.user_id==User.id, UserUnit.unit_id==Unit.id, or_(Unit.nama.ilike('%%%s%%' % cari), User.user_name.ilike('%%%s%%' % cari), User.email.ilike('%%%s%%' % cari),)) rowTable = DataTables(req, User, query, columns) return rowTable.output_result()
def view_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': # defining columns disposisi_id = url_dict['disposisi_id'].isdigit() and url_dict['disposisi_id'] or 0 columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('disposisi_id')) columns.append(ColumnDT('na')) columns.append(ColumnDT('komentar')) user1db = aliased(User) surat_id = DBSession.query(Disposisi.surat_id).\ filter(Disposisi.id==disposisi_id, ) query = DBSession.query(DisposisiComment.id, DisposisiComment.disposisi_id, user1db.user_name.label('na'), DisposisiComment.komentar).\ join(Disposisi).\ outerjoin(user1db, DisposisiComment.user_id == user1db.id).\ filter(DisposisiComment.disposisi_id==Disposisi.id, Disposisi.surat_id==surat_id, ) rowTable = DataTables(req, DisposisiComment, query, columns) return rowTable.output_result() elif url_dict['act']=='file': disposisi_id = url_dict['disposisi_id'].isdigit() and url_dict['disposisi_id'] or 0 columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('surat_id')) columns.append(ColumnDT('name')) #, lambda i: "SuratDetail: {}".format(i.name))) columns.append(ColumnDT('size',filter=_number_format)) columns.append(ColumnDT('path')) si = DBSession.query(Disposisi.surat_id).\ filter(Disposisi.id==disposisi_id, ) query = DBSession.query(SuratDetail).\ join(Surat).\ filter(SuratDetail.surat_id==si ) rowTable = DataTables(req, SuratDetail, query, columns) return rowTable.output_result()
def test_column_ordering(self): """Test if a column is orderable.""" self.populate(5) user6, addr6 = self.create_user('000_aaa', 'Whatever') user7, addr7 = self.create_user('zzz_aaa', 'Whatif') self.session.add(user6) self.session.add(user7) self.session.commit() columns = [ ColumnDT(User.id, ), ColumnDT(User.name), ColumnDT(Address.description), ColumnDT(User.created_at), ] # DESC req = self.create_dt_params(order=[{"column": 1, "dir": "desc"}]) rowTable = DataTables( req, self.session.query().select_from(User).join(Address), columns) res = rowTable.output_result() assert res['data'][0]['1'] == 'zzz_aaa' # ASC req = self.create_dt_params(order=[{"column": 1, "dir": "asc"}]) rowTable = DataTables( req, self.session.query().select_from(User).join(Address), columns) res = rowTable.output_result() assert res['data'][0]['1'] == '000_aaa' # DESC first column req = self.create_dt_params(order=[{"column": 0, "dir": "desc"}]) rowTable = DataTables( req, self.session.query().select_from(User).join(Address), columns) res = rowTable.output_result() assert res['data'][0]['1'] == 'zzz_aaa'
def test_column_ordering_nulls(self): """Test if a foreign key column is orderable.""" self.populate(5) user6, addr6 = self.create_user('000_Whatever', '000_aaa') user7, addr7 = self.create_user('zzz_Whatif', 'zzz_aaa') self.session.add(user6) self.session.add(user7) self.session.commit() columns = [ ColumnDT(User.id, ), ColumnDT(User.name), ColumnDT(Address.description, 'nulls_first'), ColumnDT(User.created_at), ] # NULLS FIRST req = self.create_dt_params(order=[{"column": 2, "dir": "desc"}]) rowTable = DataTables( req, self.session.query().select_from(User).join(Address), columns) res = rowTable.output_result() if 'error' in res: # sqlite3 doesn't support nulls ordering assert res['error'] == '(sqlite3.OperationalError) near "NULLS"' columns = [ ColumnDT(User.id, ), ColumnDT(User.name), ColumnDT(Address.description, 'nulls_last'), ColumnDT(User.created_at), ] # NULLS LAST req = self.create_dt_params(order=[{"column": 2, "dir": "asc"}]) rowTable = DataTables( req, self.session.query().select_from(User).join(Address), columns) res = rowTable.output_result() if 'error' in res: # sqlite3 doesn't support nulls ordering assert res['error'] == '(sqlite3.OperationalError) near "NULLS"'
def plan_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act'] == 'grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('units.nama')) columns.append(ColumnDT('product_plans.kode')) columns.append(ColumnDT('product_plans.nama')) columns.append(ColumnDT('approval_date', filter=_DTstrftime)) columns.append(ColumnDT('approval_level')) columns.append(ColumnDT('notes')) query = DBSession.query(ProductPlanAppr) rowTable = DataTables(req, ProductPlanAppr, query, columns) return rowTable.output_result() elif url_dict['act'] == 'grid1': cari = 'cari' in params and params['cari'] or '' columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('un')) columns.append(ColumnDT('pk')) columns.append(ColumnDT('pn')) columns.append(ColumnDT('approval_date', filter=_DTstrftime)) columns.append(ColumnDT('approval_level')) columns.append(ColumnDT('notes')) query = DBSession.query( ProductPlanAppr.id, Unit.nama.label('un'), ProductPlan.kode.label('pk'), ProductPlan.nama.label('pn'), ProductPlanAppr.approval_date, ProductPlanAppr.approval_level, ProductPlanAppr.notes, ).filter( ProductPlanAppr.product_plan_id == ProductPlan.id, ProductPlanAppr.unit_id == Unit.id, or_( ProductPlan.nama.ilike('%%%s%%' % cari), ProductPlan.kode.ilike('%%%s%%' % cari), Unit.nama.ilike('%%%s%%' % cari), )) rowTable = DataTables(req, ProductPlanAppr, query, columns) return rowTable.output_result()
def test_global_search_filtering(self): """Test if result's are filtered from global search field.""" self.populate(5) user6, addr6 = self.create_user('Run To', 'The Hills') user7, addr7 = self.create_user('Fear Of', 'The Dark') self.session.add(user6) self.session.add(user7) self.session.commit() columns = self.create_columns(['id', 'name', 'address.description', 'created_at']) req = self.create_dt_params(search='Fear') rowTable = DataTables( req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert len(res['aaData']) == 1 assert res['iTotalRecords'] == '7' assert res['iTotalDisplayRecords'] == '1' assert res['aaData'][0]['1'] == 'Fear Of' assert res['aaData'][0]['2'] == 'The Dark'
def routes_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('nama')) columns.append(ColumnDT('path')) columns.append(ColumnDT('disabled')) query = DBSession.query(Route) rowTable = DataTables(req, Route, query, columns) return rowTable.output_result() elif url_dict['act']=='headof': term = 'term' in params and params['term'] or '' rows = DBSession.query(Route.id, Route.nama ).filter( Route.nama.ilike('%{term}%'.format(term=term))).\ order_by(Route.nama).all() print rows r = [] for k in rows: d={} d['id'] = k[0] d['value'] = k[1] r.append(d) return r
def view_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act'] == 'grid': # defining columns product_accept_id = url_dict['product_accept_id'].isdigit( ) and url_dict['product_accept_id'] or 0 columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('products.kode')) columns.append(ColumnDT('products.nama')) columns.append(ColumnDT('qty')) columns.append(ColumnDT('product_id')) columns.append(ColumnDT('product_accept_id')) query = DBSession.query(ProductAcceptItem).\ join(ProductAccept).\ filter(ProductAcceptItem.product_accept_id==product_accept_id ) rowTable = DataTables(req, ProductAcceptItem, query, columns) return rowTable.output_result()
def aset_kibe_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict pk_id = 'id' in params and int(params['id']) or 0 if url_dict['act']=='grid': # defining columns columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('units.kode')) columns.append(ColumnDT('kats.kode')) columns.append(ColumnDT('no_register')) columns.append(ColumnDT('uraian')) columns.append(ColumnDT('tahun')) columns.append(ColumnDT('th_beli')) columns.append(ColumnDT('harga')) columns.append(ColumnDT('kondisi')) query = DBSession.query(AsetKib).\ join(AsetKategori).\ filter(AsetKib.unit_id == ses['unit_id'], AsetKib.kategori_id==AsetKategori.id, AsetKib.kib=='E') rowTable = DataTables(req, AsetKib, query, columns) return rowTable.output_result()
def data(request): """Return server side data.""" # defining columns # - explicitly cast date to string, so string searching the date # will search a date formatted equal to how it is presented # in the table columns = [ ColumnDT(User.id), ColumnDT(User.name, sort_method=func.public.naturalsort(User.name)), ColumnDT(Address.description), ColumnDT(func.strftime('%d-%m-%Y', User.birthday)), ColumnDT(User.age) ] # defining the initial query depending on your purpose # - don't include any columns # - if you need a join, also include a 'select_from' query = DBSession.query().\ select_from(User).\ join(Address).\ filter(Address.id > 4) # instantiating a DataTable for the query and table needed rowTable = DataTables(request.GET, query, columns) # returns what is needed by DataTable return rowTable.output_result()
def gaji_app_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('nama')) columns.append(ColumnDT('tahun')) columns.append(ColumnDT('disabled')) query = DBSession.query(App) rowTable = DataTables(req, App, query, columns) return rowTable.output_result() elif url_dict['act']=='changeid': row = App.get_by_id('app_id' in params and params['app_id'] or 0) if row: ses['app_id']=row.id ses['app_kd']=row.kode ses['app_nm']=row.nama return {'success':True}
def test_outerjoin(self): """Test if outerjoin works.""" self.populate(5) f = faker.Faker() a = Address(description=f.address()) self.session.add(a) columns = [ ColumnDT(Address.id, ), ColumnDT(Address.description), ColumnDT(User.name), ] req = self.create_dt_params() rowTable = DataTables( req, self.session.query().select_from(Address).outerjoin(User), columns) res = rowTable.output_result() assert len(res['data']) == 6 assert res['recordsTotal'] == '6' assert res['recordsFiltered'] == '6' assert res['data'][5]['1'] == a.description assert res['data'][5]['2'] is None
def test_null_field_filtering(self): """Test if a None field is not filtered.""" self.populate(5) user6, addr6 = self.create_user('Empty', None) self.session.add(user6) self.session.commit() columns = [ ColumnDT(User.id, mData='ID'), ColumnDT(User.name, mData='Username'), ColumnDT(Address.description, mData='Address'), ColumnDT(User.created_at, mData='Created at'), ] req = self.create_dt_params() rowTable = DataTables( req, self.session.query().select_from(User).join(Address), columns) res = rowTable.output_result() assert len(res['data']) == 6 assert res['recordsTotal'] == '6' assert res['recordsFiltered'] == '6'
def test_list_users_with_hybrid_attribute(self): """Test if it returns a users list with a Hybrid column.""" self.populate(5) user6, addr6 = self.create_user('Homer', 'Simpson') self.session.add(user6) self.session.commit() columns = [ ColumnDT(User.id), ColumnDT(User.dummy), ColumnDT(User.name), ColumnDT(User.created_at), ] self.session.query(*[User.id, User.dummy]).all() req = self.create_dt_params() rowTable = DataTables(req, self.session.query(), columns) res = rowTable.output_result() assert len(res['data']) == 6 assert res['data'][5]['1'] == 'Ho' assert res['data'][5]['2'] == 'Homer'
def view_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict pk_id = 'id' in params and params['id'] and int(params['id']) or 0 if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('tanggal', filter=self._DTstrftime)) columns.append(ColumnDT('uraian')) columns.append(ColumnDT('alasan')) query = DBSession.query(AsetDel.id, AsetDel.kode, AsetDel.tanggal, AsetDel.uraian, AsetDel.alasan, ).filter(AsetDel.unit_id==ses['unit_id'] ) rowTable = DataTables(req, AsetDel, query, columns) return rowTable.output_result()
def view_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('field01', filter=_DTstrftime, search_like='%s%%')) #tgl columns.append(ColumnDT('field00')) #receiver columns.append(ColumnDT('field02')) #sender columns.append(ColumnDT('field03')) #cmd columns.append(ColumnDT('field04')) columns.append(ColumnDT('field05')) columns.append(ColumnDT('field06')) columns.append(ColumnDT('field07')) columns.append(ColumnDT('field11')) query = DBSession.query(SmsParsed) rowTable = DataTables(req, SmsParsed, query, columns) return rowTable.output_result() elif url_dict['act']=='csv': pass return
def aset_kibd_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict pk_id = 'id' in params and int(params['id']) or 0 if url_dict['act']=='grid': # defining columns columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('units.kode')) columns.append(ColumnDT('units.nama')) columns.append(ColumnDT('kats.kode')) columns.append(ColumnDT('no_register')) #columns.append(ColumnDT('uraian')) columns.append(ColumnDT('kats.uraian')) #columns.append(ColumnDT('tahun')) columns.append(ColumnDT('tgl_perolehan', filter=self._DTstrftime)) columns.append(ColumnDT('th_beli')) columns.append(ColumnDT('harga')) columns.append(ColumnDT('kondisi')) query = DBSession.query(AsetKib).\ join(AsetKategori, Unit).\ filter(AsetKib.unit_id == Unit.id, #AsetKib.unit_id == ses['unit_id'], AsetKib.kategori_id==AsetKategori.id, AsetKib.kib=='D', func.substr(Unit.kode,1,func.length(ses['unit_kd']))==ses['unit_kd'], or_(AsetKib.disabled=='0',AsetKib.disabled==None)) rowTable = DataTables(req, AsetKib, query, columns) return rowTable.output_result()
def json_disks(request): columns = list() if request.user.is_focus: columns.append(ColumnDT('company_name', filter=_unknown_column_value)) columns.append(ColumnDT('disk_rfid', filter=_add_href_in_disk__rf_id)) columns.append(ColumnDT('disk_upc', filter=_add_href_in_upc)) columns.append(ColumnDT('movie_name', filter=_unknown_column_value)) columns.append(ColumnDT('disk_format', filter=_unknown_column_value)) columns.append(ColumnDT('movie_release_date', filter=_date_column)) columns.append(ColumnDT('movie_dvd_release_date', filter=_date_column)) columns.append(ColumnDT('disk_state', filter=_unknown_column_value)) columns.append(ColumnDT('kiosk_alias', filter=_unknown_column_value)) columns.append(ColumnDT('kiosk_slot_number', filter=_unknown_column_value)) columns.append(ColumnDT('rent_days', filter=_unknown_column_value)) columns.append(ColumnDT('deal_first_night_rent_charge', filter=_unknown_cash_value)) # columns.append(ColumnDT('deal_next_night_rent_charge', filter=_unknown_cash_value)) columns.append(ColumnDT('deal_sale_charge', filter=_unknown_cash_value)) columns.append(ColumnDT('deal_total_amount', filter=_unknown_cash_value)) query = request.db_session.query(DisksView) if request.user.is_company: query = query.filter_by(company_id=request.user.company.id) row_table = DataTables(request, DisksView, query, columns) response = row_table.output_result() return JsonResponse(response)
def group_routes_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act'] == 'grid': columns = [] columns.append(ColumnDT('group_id')) columns.append(ColumnDT('route_id')) columns.append(ColumnDT('groups.group_name')) columns.append(ColumnDT('routes.nama')) columns.append(ColumnDT('routes.path')) query = DBSession.query(GroupRoutePermission).join(Group).join(Route) rowTable = DataTables(req, GroupRoutePermission, query, columns) return rowTable.output_result() elif url_dict['act'] == 'changeid': row = GroupRoutePermission.get_by_id( 'routes_id' in params and params['routes_id'] or 0) if row: ses['routes_id'] = row.id ses['routes_kd'] = row.kode ses['routes_nm'] = row.nama return {'success': True}
def data(employee_id=None): from db_models import EmployeeSalaryModel """Return server side data.""" # defining columns columns = [ ColumnDT(EmployeeSalaryModel.id), ColumnDT(EmployeeSalaryModel.employee_id), ColumnDT(EmployeeSalaryModel.date_filter), ColumnDT(EmployeeSalaryModel.amount), ColumnDT(EmployeeSalaryModel.is_deleted), ColumnDT(EmployeeSalaryModel.created_at), ] # defining the initial query depending on your purpose query = db.session.query().select_from(EmployeeSalaryModel).order_by(EmployeeSalaryModel.id) if employee_id is not None: query = query.filter_by(employee_id=employee_id) # GET parameters params = request.args.to_dict() # print(params) # instantiating a DataTable for the query and table needed rowTable = DataTables(params, query, columns) # returns what is needed by DataTable return jsonify(rowTable.output_result())
def ak_jurnal_skpd_item_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict pk_id = 'id' in params and params['id'] and int(params['id']) or 0 if url_dict['act']=='grid': ak_jurnal_id = url_dict['ak_jurnal_id'].isdigit() and url_dict['ak_jurnal_id'] or 0 columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('sapkd')) columns.append(ColumnDT('sapnm')) columns.append(ColumnDT('amount', filter=self._number_format)) columns.append(ColumnDT('notes')) columns.append(ColumnDT('rekkd')) columns.append(ColumnDT('reknm')) columns.append(ColumnDT('kegiatan_sub_id')) columns.append(ColumnDT('rekening_id')) columns.append(ColumnDT('ak_jurnal_id')) columns.append(ColumnDT('subkd')) columns.append(ColumnDT('subnm')) rek = aliased(Rekening) sap = aliased(Sap) sub = aliased(KegiatanSub) query = DBSession.query(AkJurnalItem.id, sap.kode.label('sapkd'), sap.nama.label('sapnm'), AkJurnalItem.amount, AkJurnalItem.notes, rek.kode.label('rekkd'), rek.nama.label('reknm'), AkJurnalItem.kegiatan_sub_id, AkJurnalItem.rekening_id, AkJurnalItem.ak_jurnal_id, sub.kode.label('subkd'), sub.nama.label('subnm'), ).join(AkJurnal, ).outerjoin(rek, AkJurnalItem.rekening_id == rek.id ).outerjoin(sap, AkJurnalItem.sap_id == sap.id ).outerjoin(sub, AkJurnalItem.kegiatan_sub_id == sub.id ).filter(AkJurnalItem.ak_jurnal_id==ak_jurnal_id, AkJurnalItem.ak_jurnal_id==AkJurnal.id, ).group_by(AkJurnalItem.id, sap.kode.label('sapkd'), sap.nama.label('sapnm'), AkJurnalItem.amount, AkJurnalItem.notes, rek.kode.label('rekkd'), rek.nama.label('reknm'), AkJurnalItem.kegiatan_sub_id, AkJurnalItem.rekening_id, AkJurnalItem.ak_jurnal_id, sub.kode.label('subkd'), sub.nama.label('subnm'), ) rowTable = DataTables(req, AkJurnalItem, query, columns) return rowTable.output_result()
def data(): """Return server side data.""" # defining columns columns = [ ColumnDT(User.id, mData='id'), ColumnDT(User.first_name, mData='first_name'), ColumnDT(User.last_name, mData='last_name'), ColumnDT(User.email, mData='email'), ColumnDT(User.ip_address, mData='ip_address'), ColumnDT(User.country, mData='country'), ColumnDT(Address.description, mData='Address_description'), ColumnDT(User.date_joined, mData='date_joined'), ColumnDT(User.city_array, mData='city_array'), ] # defining the initial query depending on your purpose query = db.session.query().select_from(User).join(Address, isouter=True) # GET parameters params = request.args.to_dict() # instantiating a DataTable for the query and table needed rowTable = DataTables(params, query, columns) # returns what is needed by DataTable return jsonify(rowTable.output_result())
def get_result(session, column, search_method, search_value): columns = [ColumnDT(column, search_method=search_method)] query = session.query() params = create_dt_params(columns) params['columns[0][search][value]'] = search_value rowTable = DataTables(params, query, columns) return rowTable.output_result()
def view_act(request): req = request params = req.params url_dict = req.matchdict if url_dict['act'] == 'grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('nama')) columns.append(ColumnDT('level_id')) columns.append(ColumnDT('is_summary')) columns.append(ColumnDT('status', filter=_DTactive)) query = DBSession.query(Rekening) rowTable = DataTables(req.GET, Rekening, query, columns) return rowTable.output_result() ## Headofnama Rekening elif url_dict['act'] == 'hon': term = 'term' in params and params['term'] or '' rows = DBSession.query(Rekening.id, Rekening.nama).filter( Rekening.nama.ilike('%%%s%%' % term)).all() r = [] for k in rows: d = {} d['id'] = k[0] d['value'] = k[1] r.append(d) return r
def ag_indikator_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': ag_step_id = ses['ag_step_id'] kegiatan_sub_id = 'kegiatan_sub_id' in params and params['kegiatan_sub_id'] or 0 columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('tipe')) columns.append(ColumnDT('no_urut')) columns.append(ColumnDT('tolok_ukur_%s' %ag_step_id)) columns.append(ColumnDT('volume_%s' %ag_step_id)) columns.append(ColumnDT('satuan_%s' %ag_step_id)) #columns.append(ColumnDT("".join(['tolok_ukur_',str(self.status_apbd)]))) #columns.append(ColumnDT("".join(['volume_',str(self.status_apbd)]))) #columns.append(ColumnDT("".join(['satuan_',str(self.status_apbd)]))) query = DBSession.query(KegiatanIndikator)\ .join(KegiatanSub)\ .filter(KegiatanSub.id==kegiatan_sub_id, KegiatanSub.unit_id==ses['unit_id']) rowTable = DataTables(req, KegiatanIndikator, query, columns) return rowTable.output_result()
def gaji_group_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict if url_dict['act'] == 'grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('group_name')) columns.append(ColumnDT('description')) columns.append(ColumnDT('member_count')) query = DBSession.query(Group) rowTable = DataTables(req, Group, query, columns) return rowTable.output_result() elif url_dict['act'] == 'headofnama': term = 'term' in params and params['term'] or '' rows = DBSession.query(Group.id, Group.group_name).filter( Group.group_name.ilike('%%%s%%' % term)).all() r = [] for k in rows: d = {} d['id'] = k[0] d['value'] = k[1] r.append(d) return r
def api_documents(): columns = [ ColumnDT('id'), ColumnDT('author_id'), ColumnDT('title'), ColumnDT('cite_count', filter=none_to_empty_str), ColumnDT('year', filter=none_to_empty_str), ColumnDT('created_at', filter=str), ColumnDT('updated_at', filter=str), ] # Currently we filter out documents that have no publication date or no citations. # This has two reasons: 1) documents without publication dates are not really interesting and # often times not real publications # 2) there exists no option to set SQL order by null behavior in the currently used libraries # (sqlalchemy-datatables and datatables.js). # Therefore I opened an issue with one of the libraries developers: # https://github.com/Pegase745/sqlalchemy-datatables/issues/35 # Joins do not work with DataTable # query = session.query(Document, Author).filter(Document.year != None).filter(Document.cite_count != None)\ # .join(Author, Author.id == Document.author_id) query = session.query(Document).filter(Document.year != None).filter( Document.cite_count != None) row_table = DataTables(request.args, Document, query, columns) return jsonify(**row_table.output_result())
def gaji_group_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('group_name')) columns.append(ColumnDT('description')) columns.append(ColumnDT('member_count')) query = DBSession.query(Group) rowTable = DataTables(req, Group, query, columns) return rowTable.output_result() elif url_dict['act']=='headofnama': term = 'term' in params and params['term'] or '' rows = DBSession.query(Group.id, Group.group_name ).filter( Group.group_name.ilike('%%%s%%' % term) ).all() r = [] for k in rows: d={} d['id'] = k[0] d['value'] = k[1] r.append(d) return r
def test_list_page_x(self): """Test if it lists users that are not on page 1.""" self.populate(10) user11, addr11 = self.create_user('The Number of', 'The Beast') user12, addr12 = self.create_user('Hallowed Be', 'Thy Name') self.session.add(user11) self.session.add(user12) self.session.commit() columns = self.create_columns(['id', 'name', 'address.description', 'created_at']) req = self.create_dt_params(start=10, length=10) rowTable = DataTables( req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert len(res['aaData']) == 2 assert res['iTotalRecords'] == '12' assert res['iTotalDisplayRecords'] == '12' assert res['aaData'][0]['1'] == 'The Number of' assert res['aaData'][1]['1'] == 'Hallowed Be'
def test_list_page_x(self): """Test if it lists users that are not on page 1.""" self.populate(10) user11, addr11 = self.create_user('The Number of', 'The Beast') user12, addr12 = self.create_user('Hallowed Be', 'Thy Name') self.session.add(user11) self.session.add(user12) self.session.commit() columns = self.create_columns( ['id', 'name', 'address.description', 'created_at']) req = self.create_dt_params(start=10, length=10) rowTable = DataTables(req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert len(res['data']) == 2 assert res['recordsTotal'] == '12' assert res['recordsFiltered'] == '12' assert res['data'][0]['1'] == 'The Number of' assert res['data'][1]['1'] == 'Hallowed Be'
def test_column_not_searchable(self): """Test if a column is not searchable.""" self.populate(5) user6, addr6 = self.create_user('Run To', 'The Hills') user7, addr7 = self.create_user('Fear Of', 'The Dark') self.session.add(user6) self.session.add(user7) self.session.commit() columns = [] columns.append(ColumnDT('id', mData='ID')) columns.append(ColumnDT('name', mData='Username', searchable=False)) columns.append(ColumnDT('address.description', mData='Address')) columns.append(ColumnDT('created_at', mData='Created at')) req = self.create_dt_params(search='Fear') rowTable = DataTables( req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert len(res['aaData']) == 0 assert res['iTotalRecords'] == '7' assert res['iTotalDisplayRecords'] == '0'
def test_column_not_searchable(self): """Test if a column is not searchable.""" self.populate(5) user6, addr6 = self.create_user('Run To', 'The Hills') user7, addr7 = self.create_user('Fear Of', 'The Dark') self.session.add(user6) self.session.add(user7) self.session.commit() columns = [] columns.append(ColumnDT('id', mData='ID')) columns.append(ColumnDT('name', mData='Username', searchable=False)) columns.append(ColumnDT('address.description', mData='Address')) columns.append(ColumnDT('created_at', mData='Created at')) req = self.create_dt_params(search='Fear') rowTable = DataTables(req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert len(res['data']) == 0 assert res['recordsTotal'] == '7' assert res['recordsFiltered'] == '0'
def routes_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('nama')) columns.append(ColumnDT('path')) columns.append(ColumnDT('status')) query = DBSession.query(Route) rowTable = DataTables(req, Route, query, columns) return rowTable.output_result() elif url_dict['act']=='headof': term = 'term' in params and params['term'] or '' rows = DBSession.query(Route.id, Route.nama ).filter( Route.nama.ilike('%{term}%'.format(term=term))).\ order_by(Route.nama).all() print rows r = [] for k in rows: d={} d['id'] = k[0] d['value'] = k[1] r.append(d) return r
def view_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': # defining columns product_plan_id = url_dict['product_plan_id'].isdigit() and url_dict['product_plan_id'] or 0 columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('product.kode')) columns.append(ColumnDT('product.nama')) columns.append(ColumnDT('product.price',filter=_number_format)) columns.append(ColumnDT('qty')) columns.append(ColumnDT('price',filter=_number_format)) columns.append(ColumnDT('product_id')) columns.append(ColumnDT('product_plan_id')) query = DBSession.query(ProductPlanItem).\ join(ProductPlan).\ filter(ProductPlanItem.product_plan_id==product_plan_id ) rowTable = DataTables(req, ProductPlanItem, query, columns) return rowTable.output_result()
def gaji_group_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('email')) columns.append(ColumnDT('user_name')) columns.append(ColumnDT('status')) columns.append(ColumnDT('last_login_date')) columns.append(ColumnDT('registered_date')) query = DBSession.query(User) rowTable = DataTables(req, User, query, columns) return rowTable.output_result() elif url_dict['act']=='headofnama': term = 'term' in params and params['term'] or '' rows = DBSession.query(User.id, User.user_name ).filter( User.user_name.ilike('%%%s%%' % term) ).all() r = [] for k in rows: d={} d['id'] = k[0] d['value'] = k[1] r.append(d) return r
def gaji_urusan_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict if url_dict['act'] == 'grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('nama')) columns.append(ColumnDT('disabled')) query = DBSession.query(UrusanModel) rowTable = DataTables(req, UrusanModel, query, columns) return rowTable.output_result() elif url_dict['act'] == 'changeid': row = UrusanModel.get_by_id( 'urusan_id' in params and params['urusan_id'] or 0) if row: ses['urusan_id'] = row.id ses['urusan_kd'] = row.kode ses['urusan_nm'] = row.nama return {'success': True}
def gaji_group_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict if url_dict["act"] == "grid": columns = [] columns.append(ColumnDT("id")) columns.append(ColumnDT("group_name")) columns.append(ColumnDT("description")) columns.append(ColumnDT("member_count")) query = DBSession.query(Group) rowTable = DataTables(req, Group, query, columns) return rowTable.output_result() elif url_dict["act"] == "headofnama": term = "term" in params and params["term"] or "" rows = DBSession.query(Group.id, Group.group_name).filter(Group.group_name.ilike("%%%s%%" % term)).all() r = [] for k in rows: d = {} d["id"] = k[0] d["value"] = k[1] r.append(d) return r
def view_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': pk_id = 'id' in params and params['id'] and int(params['id']) or 0 if url_dict['act']=='grid': # defining columns columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('tgl_terima', filter=self._DTstrftime)) columns.append(ColumnDT('tgl_validasi', filter=self._DTstrftime)) columns.append(ColumnDT('nama')) columns.append(ColumnDT('nilai')) columns.append(ColumnDT('posted')) query = DBSession.query(ARInvoice.id, ARInvoice.kode, ARInvoice.tgl_terima, ARInvoice.tgl_validasi, ARInvoice.nama, ARInvoice.nilai, ARInvoice.posted, ).filter(ARInvoice.tahun_id==ses['tahun'], ARInvoice.unit_id==ses['unit_id'], ).order_by(ARInvoice.id.asc() ) rowTable = DataTables(req, ARInvoice, query, columns) return rowTable.output_result()
def ak_jurnal_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('tanggal', filter=self._DTstrftime)) columns.append(ColumnDT('kode')) columns.append(ColumnDT('jv_type')) columns.append(ColumnDT('nama')) columns.append(ColumnDT('source')) columns.append(ColumnDT('source_no')) columns.append(ColumnDT('is_skpd')) columns.append(ColumnDT('posted')) query = DBSession.query(Jurnal.id, Jurnal.tanggal, Jurnal.kode, Jurnal.jv_type, Jurnal.nama, Jurnal.source, Jurnal.source_no, Jurnal.is_skpd, Jurnal.posted).\ outerjoin(JurnalItem).\ group_by(Jurnal.id, Jurnal.tanggal, Jurnal.kode, Jurnal.jv_type, Jurnal.nama, Jurnal.source, Jurnal.source_no, Jurnal.is_skpd, Jurnal.posted).\ filter(Jurnal.tahun_id == ses['tahun'], Jurnal.unit_id == ses['unit_id'],) rowTable = DataTables(req, Jurnal, query, columns) return rowTable.output_result()
def tables_record(group, zone): # print(group, zone) is_inner = 0 if group == 'inner': is_inner = 1 elif group == 'intercepted': is_inner = 2 zone = Zone.query.filter_by(name=zone.replace('_', '.'), is_inner=is_inner).first() if not zone: abort(404) # print(zone.id) if request.method == 'GET': columns = [] columns.append(ColumnDT(Record.id)) columns.append(ColumnDT(Record.host)) columns.append(ColumnDT(Record.record_type)) columns.append(ColumnDT(Record.value)) columns.append(ColumnDT(Record.TTL)) columns.append(ColumnDT(Record.line_type)) columns.append(ColumnDT( Record.comment)) # where address is an SQLAlchemy Relation # columns.append(ColumnDT(model.update_time)) columns.append(ColumnDT(Record.alive)) columns.append(ColumnDT(Record.update_time)) query = db.session.query().select_from(Record).filter( Record.zone_id == zone.id) params = request.args.to_dict() rowTable = DataTables(params, query, columns) res = rowTable.output_result() # print('asdfasfasdfasdfsdfas') # print(res) return jsonify(res)
def test_global_search_filtering(self): """Test if result's are filtered from global search field.""" self.populate(5) user6, addr6 = self.create_user('Run To', 'The Hills') user7, addr7 = self.create_user('Fear Of', 'The Dark') self.session.add(user6) self.session.add(user7) self.session.commit() columns = self.create_columns( ['id', 'name', 'address.description', 'created_at']) req = self.create_dt_params(search='Fear') rowTable = DataTables(req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert len(res['data']) == 1 assert res['recordsTotal'] == '7' assert res['recordsFiltered'] == '1' assert res['data'][0]['1'] == 'Fear Of' assert res['data'][0]['2'] == 'The Dark'
def get_result(self, column, search_method, search_value): columns = [ColumnDT(column, search_method=search_method)] query = self.session.query() params = self.create_dt_params(columns) params['columns[0][search][value]'] = search_value rowTable = DataTables(params, query, columns) return rowTable.output_result()
def view_act(self): ses = self.request.session req = self.request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': pk_id = 'id' in params and params['id'] and int(params['id']) or 0 if url_dict['act']=='grid': # defining columns columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('tgl_sts', filter=self._DTstrftime)) columns.append(ColumnDT('tgl_validasi', filter=self._DTstrftime)) columns.append(ColumnDT('jenis')) columns.append(ColumnDT('nama')) columns.append(ColumnDT('nominal')) columns.append(ColumnDT('posted')) columns.append(ColumnDT('posted1')) query = DBSession.query(Sts).filter( Sts.tahun_id == ses['tahun'], Sts.unit_id == ses['unit_id'] ) rowTable = DataTables(req, Sts, query, columns) return rowTable.output_result()
def api_fields(): columns = [ColumnDT('field_name'), ColumnDT('created_at', filter=str), ColumnDT('updated_at', filter=str), ] query = session.query(Label) row_table = DataTables(request.args, Label, query, columns) return jsonify(**row_table.output_result())
def group_routes_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act']=='grid': columns = [] columns.append(ColumnDT('group_id')) columns.append(ColumnDT('route_id')) columns.append(ColumnDT('groups.group_name')) columns.append(ColumnDT('routes.nama')) columns.append(ColumnDT('routes.path')) query = DBSession.query(GroupRoutePermission).join(Group).join(Route).\ order_by(Route.nama, Group.group_name) rowTable = DataTables(req, GroupRoutePermission, query, columns) return rowTable.output_result() elif url_dict['act']=='changeid': row = GroupRoutePermission.get_by_id('routes_id' in params and params['routes_id'] or 0) if row: ses['routes_id']=row.id ses['routes_kd']=row.kode ses['routes_nm']=row.nama return {'success':True}
def data(request): """Return server side data.""" # defining columns # - explicitly cast date to string, so string searching the date # will search a date formatted equal to how it is presented # in the table columns = [ ColumnDT(User.id), ColumnDT(User.name), ColumnDT(Address.description), ColumnDT(func.strftime('%d-%m-%Y', User.birthday)), ColumnDT(User.age) ] # defining the initial query depending on your purpose # - don't include any columns # - if you need a join, also include a 'select_from' query = DBSession.query().\ select_from(User).\ join(Address).\ filter(Address.id > 4) # instantiating a DataTable for the query and table needed rowTable = DataTables(request.GET, query, columns) # returns what is needed by DataTable return rowTable.output_result()
def get_dt_data(mo_pk): """Get managed objects values in jQuery datatables format""" # Get the vendor and technology pk's managedobject = ManagedObject.query.filter_by(pk=mo_pk).first() vendor_pk = managedobject.vendor_pk tech_pk = managedobject.tech_pk mo_name = managedobject.name # Get the schema managedobject_schema = ManagedObjectSchema.query.filter_by( vendor_pk=vendor_pk, tech_pk=tech_pk).first() schema_name = managedobject_schema.name.lower() metadata = MetaData() mo_data_table = Table(mo_name.lower(), metadata, autoload=True, autoload_with=db.engine, schema=schema_name) columns = [] for c in mo_data_table.columns: columns.append(ColumnDT(c, column_name=c.name, mData=c.name)) query = db.session.query(mo_data_table) # GET request parameters params = request.args.to_dict() row_table = DataTables(params, query, columns) return jsonify(row_table.output_result())
def view_act(request): req = request params = req.params url_dict = req.matchdict user = req.user if url_dict['act'] == 'grid': awal = 'awal' in request.GET and request.GET['awal'] or datetime.now( ).strftime('%Y-%m-%d') akhir = 'akhir' in request.GET and request.GET[ 'akhir'] or datetime.now().strftime('%Y-%m-%d') columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('wp_nama')) columns.append(ColumnDT('op_nama')) columns.append(ColumnDT('rek_nama')) columns.append(ColumnDT('tgl_terima', filter=_DTstrftime)) columns.append(ColumnDT('terutang', filter=_DTnumberformat)) columns.append(ColumnDT('denda', filter=_DTnumberformat)) columns.append(ColumnDT('bunga', filter=_DTnumberformat)) columns.append(ColumnDT('jumlah', filter=_DTnumberformat)) columns.append(ColumnDT('status_invoice')) query = DBSession.query(ARTbp)\ .filter(ARTbp.tgl_terima.between(awal,akhir)) #TODO Filter BY SKPD rowTable = DataTables(req.GET, ARTbp, query, columns) return rowTable.output_result()