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()
