def test_with_default_params_ok(self):
"""Return column with given default params."""
col = ColumnDT('id')
assert col.column_name == 'id'
assert col.filter(col.column_name) == 'id'
def test_with_filter_ok(self):
"""Return column with a specific filter."""
col = ColumnDT('id', filter=lambda x: 'HANDLE %s' % x)
assert col.filter(col.column_name) == 'HANDLE id'
def test_with_invalid_search_method(self):
"""Return column with a specific filter."""
with self.assertRaises(ValueError):
ColumnDT(User.name, search_method='invalid')
def test_with_default_params_ok(self):
"""Return column with given default params."""
col = ColumnDT(User.id)
assert col.nulls_order is None
assert col.search_method == 'string_contains'
def view_act(request):
ses = request.session
req = 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('unit_kd'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('rekening_kd'))
columns.append(ColumnDT('rekening_nm'))
columns.append(ColumnDT('tgl_tetap', filter=_DTstrftime))
columns.append(ColumnDT('pokok', filter=_DTnumber_format))
columns.append(ColumnDT('denda', filter=_DTnumber_format))
columns.append(ColumnDT('bunga', filter=_DTnumber_format))
columns.append(ColumnDT('posted'))
query = DBSession.query(
ARInvoiceBphtb.id,
ARInvoiceBphtb.unit_kd,
ARInvoiceBphtb.kode,
ARInvoiceBphtb.nama,
ARInvoiceBphtb.rekening_kd,
ARInvoiceBphtb.rekening_nm,
ARInvoiceBphtb.pokok,
ARInvoiceBphtb.denda,
ARInvoiceBphtb.bunga,
ARInvoiceBphtb.posted,
ARInvoiceBphtb.tgl_tetap,
ARInvoiceBphtb.tgl_validasi,
).filter(
ARInvoiceBphtb.tahun == ses['tahun'],
between(ARInvoiceBphtb.tgl_tetap, ses['tanggal'],
ses['tanggal_to']),
ARInvoiceBphtb.posted == ses['posted'])
#).order_by(ARInvoiceBphtb.kode.asc()
#)
rowTable = DataTables(req, ARInvoiceBphtb, query, columns)
return rowTable.output_result()
def produk_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('measure_big.nama'))
columns.append(ColumnDT('measure_small.nama'))
columns.append(ColumnDT('measure_convert'))
columns.append(ColumnDT('qty'))
columns.append(ColumnDT('price'))
columns.append(ColumnDT('disabled'))
query = DBSession.query(Product)
rowTable = DataTables(req, Product, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'hon_plan':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Product.id, Product.kode, Product.nama,
Product.price).filter(
Product.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[2]
d['kode'] = k[1]
d['nama'] = k[2]
d['price'] = k[3]
r.append(d)
return r
elif url_dict['act'] == 'hon_receipt':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Product.id, Product.kode, Product.nama,
Product.qty).filter(
Product.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[2]
d['kode'] = k[1]
d['nama'] = k[2]
d['qty'] = k[3]
r.append(d)
return r
elif url_dict['act'] == 'hon_request':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Product.id, Product.kode, Product.nama,
Product.qty).filter(
Product.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[2]
d['kode'] = k[1]
d['nama'] = k[2]
d['qty'] = k[3]
r.append(d)
return r
elif url_dict['act'] == 'hon_adjust_item':
term = 'term' in params and params['term'] or ''
adjust = 'adjust' in params and params['adjust'] or ''
## Mencari unit_id ##
unit = DBSession.query(Unit.id).join(ProductAdjust).filter(
ProductAdjust.id == adjust,
Unit.id == ProductAdjust.unit_id).first()
## Mencari barang sesuai unit di Penerimaan Gudang ##
rows = DBSession.query(
Product.id, Product.kode, Product.nama, Product.qty
#).outerjoin(ProductReceipt,
).join(ProductReceiptItem).filter(
ProductReceipt.unit_id == unit,
ProductReceiptItem.product_receipt_id == ProductReceipt.id,
ProductReceiptItem.product_id == Product.id,
Product.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[2]
d['kode'] = k[1]
d['nama'] = k[2]
d['qty'] = k[3]
r.append(d)
return r
elif url_dict['act'] == 'hon_deliver':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Product.id, Product.kode, Product.nama).filter(
Product.nama.ilike('%%%s%%' % term)).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
elif url_dict['act'] == 'hon_accept':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Product.id, Product.kode, Product.nama).filter(
Product.nama.ilike('%%%s%%' % term)).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
elif url_dict['act'] == 'headofnama':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Product.id, Product.nama).filter(
Product.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
r.append(d)
return r
elif url_dict['act'] == 'headofkode':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Product.id, Product.kode).filter(
Product.kode.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(request):
req = request
params = req.params
url_dict = req.matchdict
user = req.user
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')
if url_dict['act'] == 'grid':
u = request.user.id
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('wp_nama'))
columns.append(ColumnDT('op_kode'))
columns.append(ColumnDT('op_nama'))
columns.append(ColumnDT('rek_nama'))
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('unit_nama'))
query = DBSession.query(ARInvoice)\
.filter(ARInvoice.tgl_tetap.between(awal,akhir))
if group_in(request, 'wp'):
query = query.join(WajibPajak).filter(
WajibPajak.email == request.user.email)
elif request.user.id > 1:
query = query.join(Unit).join(UserUnit).\
filter(UserUnit.user_id==request.user.id)
rowTable = DataTables(req.GET, ARInvoice, query, columns)
return rowTable.output_result()
def service_list_json_route():
"""list services, data endpoint"""
columns = [
ColumnDT(Service.id, mData='id'),
ColumnDT(Host.id, mData='host_id'),
ColumnDT(Host.address, mData='host_address'),
ColumnDT(Host.hostname, mData='host_hostname'),
ColumnDT(Service.proto, mData='proto'),
ColumnDT(Service.port, mData='port'),
ColumnDT(Service.name, mData='name'),
ColumnDT(Service.state, mData='state'),
ColumnDT(Service.info, mData='info'),
ColumnDT(Service.tags, mData='tags'),
ColumnDT(Service.comment, mData='comment'),
ColumnDT(literal_column('1'),
mData='_buttons',
search_method='none',
global_search=False)
]
query = db.session.query().select_from(Service).outerjoin(Host)
if 'filter' in request.values:
query = apply_filters(query,
FILTER_PARSER.parse(
request.values.get('filter')),
do_auto_join=False)
services = DataTables(request.values.to_dict(), query,
columns).output_result()
return jsonify(services)
def data():
"""Return server side data."""
# GET parameters
params = request.args.to_dict()
host_rid = None if 'host_rid' not in params.keys() else params['host_rid']
host_function_type = None if 'host_function_type' not in params.keys(
) else params['host_function_type']
host_function_param = None if 'host_function_param' not in params.keys(
) else params['host_function_param'].strip()
filter_tgid = None if 'tgid' not in params.keys() else int(
params['tgid'].strip())
filter_suid = None if 'suid' not in params.keys() else int(
params['suid'].strip())
start_time = None if 'sdate' not in params.keys(
) else datetime.datetime.utcfromtimestamp(float(params['sdate']))
end_time = None if 'edate' not in params.keys(
) else datetime.datetime.utcfromtimestamp(float(params['edate']))
print(params)
sysid = None if 'sysid' not in params.keys() else int(params['sysid'])
stime = int(params['sdate']) #used in the queries
etime = int(params['edate']) #used in the queries
DataStore = column_helper('data_store')
EventKeys = column_helper('event_keys')
SysIDTags = column_helper('sysid_tags')
UnitIDTags = column_helper('unit_id_tags')
TGIDTags = column_helper('tgid_tags')
LocRegResp = column_helper('loc_reg_resp_rv')
DataStore.time.type = MyDateType()
k = 'logs'
if host_function_type:
k = '%s_%s' % (k, host_function_type)
column_d = {
'logs_su': [
ColumnDT(TGIDTags.tag),
ColumnDT(DataStore.tgid),
ColumnDT(DataStore.tgid),
],
'logs_tgid': [
ColumnDT(DataStore.suid),
ColumnDT(UnitIDTags.tag),
ColumnDT(DataStore.suid),
ColumnDT(DataStore.time)
],
'logs_calls': [
ColumnDT(DataStore.time),
ColumnDT(SysIDTags.tag),
ColumnDT(DataStore.tgid),
ColumnDT(TGIDTags.tag),
ColumnDT(DataStore.frequency),
ColumnDT(DataStore.suid)
],
'logs_joins': [
ColumnDT(DataStore.time),
ColumnDT(DataStore.opcode),
ColumnDT(DataStore.sysid),
ColumnDT(SysIDTags.tag),
ColumnDT(LocRegResp.tag),
ColumnDT(DataStore.tgid),
ColumnDT(TGIDTags.tag),
ColumnDT(DataStore.suid),
ColumnDT(UnitIDTags.tag)
],
'logs_total_tgid': [
ColumnDT(DataStore.sysid),
ColumnDT(SysIDTags.tag),
ColumnDT(DataStore.tgid),
ColumnDT(TGIDTags.tag),
ColumnDT(DataStore.tgid)
],
'logs_call_detail': [
ColumnDT(DataStore.time),
ColumnDT(DataStore.opcode),
ColumnDT(SysIDTags.sysid),
ColumnDT(SysIDTags.tag),
ColumnDT(DataStore.tgid),
ColumnDT(TGIDTags.tag),
ColumnDT(DataStore.suid),
ColumnDT(UnitIDTags.tag),
ColumnDT(DataStore.frequency)
]
}
"""or_( EventKeys.tag == 'grp_v_ch_grant', EventKeys.tag == 'grp_v_ch_grant_exp'),"""
query_d = {
'logs_total_tgid': db.session.query(DataStore.sysid, \
SysIDTags.tag, \
DataStore.tgid, \
TGIDTags.tag, \
func.count(DataStore.tgid).label('count'))
.group_by(DataStore.tgid)
.outerjoin(SysIDTags.table_, DataStore.sysid == SysIDTags.sysid)
.outerjoin(TGIDTags.table_, DataStore.tgid == TGIDTags.rid)
.filter(and_(DataStore.tgid != 0), (DataStore.frequency != None) ),
'logs_call_detail': db.session.query(DataStore.time, \
DataStore.opcode, \
DataStore.sysid, \
SysIDTags.tag, \
DataStore.tgid, \
TGIDTags.tag, \
DataStore.suid, \
UnitIDTags.tag, \
DataStore.frequency )
.outerjoin(SysIDTags.table_, DataStore.sysid == SysIDTags.sysid)
.outerjoin(TGIDTags.table_, and_(DataStore.tgid == TGIDTags.rid, DataStore.sysid == TGIDTags.sysid))
.outerjoin(UnitIDTags.table_, and_(DataStore.suid == UnitIDTags.rid, DataStore.sysid == UnitIDTags.sysid))
.filter(and_(DataStore.tgid != 0), (DataStore.frequency != None) )
.filter(or_(DataStore.opcode == 0, and_(DataStore.opcode == 2, DataStore.mfrid == 144)) ),
'logs_tgid': db.session.query(DataStore.suid, \
UnitIDTags.tag, \
func.count(DataStore.suid).label('count'), func.max(DataStore.time).label('last') )
.outerjoin(UnitIDTags.table_, and_(DataStore.suid == UnitIDTags.rid, DataStore.sysid == UnitIDTags.sysid)),
'logs_su': db.session.query(TGIDTags.tag, \
DataStore.tgid, \
func.count(DataStore.tgid).label('count') )
.outerjoin(TGIDTags.table_, DataStore.tgid == TGIDTags.rid),
'logs_calls': db.session.query(DataStore.time, \
SysIDTags.tag, \
DataStore.tgid, \
TGIDTags.tag, \
DataStore.frequency, \
DataStore.suid )
.join(EventKeys.table_, and_(or_( EventKeys.tag == 'grp_v_ch_grant', EventKeys.tag == 'grp_v_ch_grant_mbt'),EventKeys.id == DataStore.cc_event))
.outerjoin(TGIDTags.table_, and_(TGIDTags.rid == DataStore.tgid, TGIDTags.sysid == DataStore.sysid))
.outerjoin(SysIDTags.table_, DataStore.sysid == SysIDTags.sysid),
'logs_joins': db.session.query(DataStore.time, \
DataStore.opcode, \
DataStore.sysid, \
SysIDTags.tag, \
LocRegResp.tag, \
DataStore.tgid, \
TGIDTags.tag, \
DataStore.suid, \
UnitIDTags.tag )
.join(LocRegResp.table_, DataStore.p == LocRegResp.rv)
.outerjoin(SysIDTags.table_, DataStore.sysid == SysIDTags.sysid)
.outerjoin(TGIDTags.table_, and_(DataStore.tgid == TGIDTags.rid, DataStore.sysid == TGIDTags.sysid))
.outerjoin(UnitIDTags.table_, and_(DataStore.suid == UnitIDTags.rid, DataStore.sysid == UnitIDTags.sysid))
.filter(or_(DataStore.opcode == 40, DataStore.opcode == 43)) # joins
} # end query_d
if host_function_type != 'cc_event':
q = query_d[k]
if host_function_type in 'su tgid'.split():
filter_col = {'su': DataStore.suid, 'tgid': DataStore.tgid}
group_col = {'su': DataStore.tgid, 'tgid': DataStore.suid}
if '?' in host_rid:
id_start = int(host_rid.replace('?', '0'))
id_end = int(host_rid.replace('?', '9'))
q = q.filter(filter_col[host_function_type] >= id_start,
filter_col[host_function_type] <= id_end)
elif '-' in host_rid:
id_start, id_end = host_rid.split('-')
id_start = int(id_start)
id_end = int(id_end)
q = q.filter(filter_col[host_function_type] >= id_start,
filter_col[host_function_type] <= id_end)
else:
q = q.filter(filter_col[host_function_type] == int(host_rid))
q = q.group_by(group_col[host_function_type])
q = q.filter(DataStore.suid != None)
dt_cols = {
'logs_tgid': [DataStore.suid, UnitIDTags.tag, 'count'],
'logs_su': [TGIDTags.tag, DataStore.tgid, 'count'],
'logs_calls': [
DataStore.time, SysIDTags.tag, DataStore.tgid, TGIDTags.tag,
DataStore.frequency, DataStore.suid
],
'logs_joins': [
DataStore.time, SysIDTags.tag, LocRegResp.tag, TGIDTags.tag,
DataStore.suid
],
'logs_total_tgid': [
DataStore.sysid, SysIDTags.tag, DataStore.tgid, TGIDTags.tag,
'count'
]
}
if host_function_type == 'cc_event':
mapl = oplog_map[host_function_param]
columns = []
for row in mapl:
col = getattr(DataStore, row[0])
if row[0] == 'sysid':
col = SysIDTags.tag
elif row[1] == 'Talkgroup':
col = TGIDTags.tag
elif row[1] == 'Source' or row[1] == 'Target':
col = UnitIDTags.tag
elif row[0] == 'cc_event':
continue
#col = EventKeys.tag
elif row[0] == 'opcode':
continue
elif host_function_param == 'loc_reg_resp' and row[0] == 'p':
col = LocRegResp.tag
columns.append(col)
column_dt = [ColumnDT(s) for s in columns]
q = db.session.query(*columns).join(
EventKeys.table_,
and_(EventKeys.tag == host_function_param,
EventKeys.id == DataStore.cc_event)).outerjoin(
SysIDTags.table_, DataStore.sysid == SysIDTags.sysid)
if host_function_param == 'grp_aff_resp':
q = q.outerjoin(
TGIDTags.table_,
and_(DataStore.tgid2 == TGIDTags.rid,
DataStore.sysid == TGIDTags.sysid)).outerjoin(
UnitIDTags.table_,
and_(DataStore.suid == UnitIDTags.rid,
DataStore.sysid == UnitIDTags.sysid))
elif host_function_param == 'ack_resp_fne' or host_function_param == 'grp_aff_q' or host_function_param == 'u_reg_cmd':
q = q.outerjoin(
TGIDTags.table_,
and_(DataStore.tgid2 == TGIDTags.rid,
DataStore.sysid == TGIDTags.sysid)).outerjoin(
UnitIDTags.table_,
and_(DataStore.suid2 == UnitIDTags.rid,
DataStore.sysid == UnitIDTags.sysid))
else:
q = q.outerjoin(
TGIDTags.table_,
and_(DataStore.tgid == TGIDTags.rid,
DataStore.sysid == TGIDTags.sysid)).outerjoin(
UnitIDTags.table_,
and_(DataStore.suid == UnitIDTags.rid,
DataStore.sysid == UnitIDTags.sysid))
if host_function_param == 'loc_reg_resp':
q = q.join(LocRegResp.table_, LocRegResp.rv == DataStore.p)
if host_function_type == 'cc_event':
cl = columns
elif k in dt_cols:
cl = dt_cols[k]
else:
cl = None
# apply tgid and suid filters if present
if host_function_type == 'cc_event':
if filter_tgid is not None and int(filter_tgid) != 0:
q = q.filter(DataStore.tgid == filter_tgid)
if filter_suid is not None and int(filter_suid) != 0:
q = q.filter(DataStore.suid == filter_suid)
if cl:
c = int(params['order[0][column]'])
d = params['order[0][dir]'] # asc or desc
if d == 'asc':
q = q.order_by(cl[c])
else:
q = q.order_by(desc(cl[c]))
q = q.filter(
and_(DataStore.time >= int(stime), DataStore.time <= int(etime)))
if sysid != 0:
q = q.filter(DataStore.sysid == sysid)
if host_function_type == 'cc_event':
rowTable = DataTables(params, q, column_dt)
else:
rowTable = DataTables(params, q, column_d[k])
js = jsonify(rowTable.output_result())
# j= 'skipped' # json.dumps(rowTable.output_result(), indent=4, separators=[',', ':'], sort_keys=True)
# with open('data-log', 'a') as logf:
# s = '\n\t'.join(['%s:%s' % (k, params[k]) for k in params.keys()])
# logf.write('keys: %s\n' % (' '.join(params.keys())))
# logf.write('params:\n\t%s\nrequest: %s\n' % (s, function_req))
# logf.write('%s\n' % j)
return js
def view_act(request):
req = request
params = req.params
url_dict = req.matchdict
if url_dict['act'] == 'grid':
u = request.user.id
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('alamat_1'))
columns.append(ColumnDT('alamat_2'))
columns.append(ColumnDT('status', filter=_DTactive))
columns.append(ColumnDT('units.nama'))
query = DBSession.query(WajibPajak).join(Unit)
if req.user.id != 1:
query = query.join(UserUnit).filter(
UserUnit.user_id == req.user.id)
#TODO : Add filter BY User SKPD
# .filter(WajibPajak.user_id==u)
rowTable = DataTables(req.GET, WajibPajak, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'hon':
term = 'term' in params and params['term'] or ''
query = DBSession.query(WajibPajak.id, WajibPajak.nama, WajibPajak.alamat_1, WajibPajak.alamat_2,WajibPajak.unit_id).\
filter(WajibPajak.nama.ilike('%%%s%%' % term) )
if req.user.id != 1:
query = query.filter(
WajibPajak.unit_id == UserUnit.unit_id,
UserUnit.user_id == req.user.id,
)
unit_id = 'unitid' in params and params['unitid'] or None
if unit_id:
query = query.filter(WajibPajak.unit_id == unit_id)
rows = query.all()
r = []
for k in rows:
d = {}
if len(rows) == 1:
req.session['a_wp_id'] = k[0]
d['id'] = k[0]
d['value'] = k[1]
d['alamat_1'] = k[2]
d['alamat_2'] = k[3]
d['unit_id'] = k[4]
r.append(d)
return r
## BUD ##
elif url_dict['act'] == 'hon1':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(WajibPajak.id, WajibPajak.nama,
WajibPajak.user_id, WajibPajak.unit_id).filter(
WajibPajak.nama.ilike('%%%s%%' %
term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['user'] = k[2]
d['unit'] = k[3]
r.append(d)
return r
## Bendahara ##
elif url_dict['act'] == 'hon2':
term = 'term' in params and params['term'] or ''
u = request.user.id
rows = DBSession.query(WajibPajak.id, WajibPajak.nama,
WajibPajak.user_id, WajibPajak.unit_id).filter(
WajibPajak.nama.ilike('%%%s%%' % term),
WajibPajak.user_id == u).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['user'] = k[2]
d['unit'] = k[3]
r.append(d)
return r
## WP ##
elif url_dict['act'] == 'hon3':
term = 'term' in params and params['term'] or ''
u = request.user.id
a = DBSession.query(User.email).filter(User.id == u).first()
rows = DBSession.query(WajibPajak.id, WajibPajak.nama,
WajibPajak.user_id, WajibPajak.unit_id).filter(
WajibPajak.nama.ilike('%%%s%%' % term),
WajibPajak.email == a).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['user'] = k[2]
d['unit'] = k[3]
r.append(d)
return r
elif url_dict['act'] == 'ho_objek':
term = 'term' in params and params['term'] or ''
u = request.user.id
print '----------------User_Login---------------', u
x = DBSession.query(
UserGroup.group_id).filter(UserGroup.user_id == u).first()
y = '%s' % x
z = int(y)
print '----------------Group_id-----------------', z
if z == 1:
a = DBSession.query(User.email).filter(User.id == u).first()
print '----------------Email---------------------', a
rows = DBSession.query(
WajibPajak.id, WajibPajak.nama, WajibPajak.user_id,
WajibPajak.unit_id).filter(
WajibPajak.email == a,
WajibPajak.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['user'] = k[2]
d['unit'] = k[3]
r.append(d)
print '----------------Penyetor------------------', r
return r
elif z == 2:
print '----------------User_id-------------------', u
rows = DBSession.query(
WajibPajak.id, WajibPajak.nama, WajibPajak.user_id,
WajibPajak.unit_id).filter(
WajibPajak.user_id == u,
WajibPajak.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['user'] = k[2]
d['unit'] = k[3]
r.append(d)
print '----------------Penyetor------------------', r
return r
else:
rows = DBSession.query(
WajibPajak.id, WajibPajak.nama, WajibPajak.user_id,
WajibPajak.unit_id).filter(
WajibPajak.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['user'] = k[2]
d['unit'] = k[3]
r.append(d)
print '----------------Penyetor------------------', r
return r
def data():
columns = [
ColumnDT(Player.name),
ColumnDT(Player.pos),
ColumnDT(Player.ta),
ColumnDT(Gamelogs.opp),
ColumnDT(Gamelogs.ha),
ColumnDT(Gamelogs.date),
ColumnDT(Gamelogs.fdp),
ColumnDT(Gamelogs.mins),
ColumnDT(Gamelogs.fgm),
ColumnDT(Gamelogs.fga),
ColumnDT(Gamelogs.fg3m),
ColumnDT(Gamelogs.fg3a),
ColumnDT(Gamelogs.ftm),
ColumnDT(Gamelogs.fta),
ColumnDT(Gamelogs.oreb),
ColumnDT(Gamelogs.dreb),
ColumnDT(Gamelogs.reb),
ColumnDT(Gamelogs.ast),
ColumnDT(Gamelogs.stl),
ColumnDT(Gamelogs.blk),
ColumnDT(Gamelogs.tov),
ColumnDT(Gamelogs.pf),
ColumnDT(Gamelogs.pts)
]
query = db.session.query().select_from(Gamelogs).join(Player)
params = request.args.to_dict()
rowTable = DataTables(params, query, columns)
return json.dumps(rowTable.output_result())
def annotation_data(genome):
"""Return server side data for loaded_annotations."""
# define columns in table:
columns = [
ColumnDT(Variants.genome), #0
ColumnDT(Annotations.user_name),
ColumnDT(Variants.chromosome),
ColumnDT(Variants.sv_start),
ColumnDT(Variants.sv_end),
ColumnDT(Variants.variant), #5
ColumnDT(Annotations.bionano_presence), #6
ColumnDT(Annotations.bionano_depth),
ColumnDT(Annotations.bionano_partial),
ColumnDT(Annotations.bionano_spanning), #9
ColumnDT(Annotations.illumina_presence), #10
ColumnDT(Annotations.illumina_depth),
ColumnDT(Annotations.illumina_paired_end),
ColumnDT(Annotations.illumina_soft_clipped),
ColumnDT(Annotations.illumina_split_read), #14
ColumnDT(Annotations.nanopore_presence), #15
ColumnDT(Annotations.nanopore_depth),
ColumnDT(Annotations.nanopore_soft_clipped),
ColumnDT(Annotations.nanopore_split_read), #18
ColumnDT(Annotations.pacbio_presence), #19
ColumnDT(Annotations.pacbio_depth),
ColumnDT(Annotations.pacbio_soft_clipped),
ColumnDT(Annotations.pacbio_split_read), #22
ColumnDT(Annotations.tenx_presence), #23
ColumnDT(Annotations.tenx_depth),
ColumnDT(Annotations.tenx_soft_clipped),
ColumnDT(Annotations.tenx_split_read),
ColumnDT(Annotations.tenx_linked_read), #27
ColumnDT(Annotations.comments), #28
# ColumnDT(StaticFiles.technology),
ColumnDT(Variants.id) #29
]
query = db.session.query(
Variants.genome,
Annotations.user_name,
Variants.chromosome,
Variants.sv_start,
Variants.sv_end,
Variants.variant,
Annotations.bionano_presence,
Annotations.bionano_depth,
Annotations.bionano_partial,
Annotations.bionano_spanning,
Annotations.illumina_presence,
Annotations.illumina_depth,
Annotations.illumina_paired_end,
Annotations.illumina_soft_clipped,
Annotations.illumina_split_read,
Annotations.nanopore_presence,
Annotations.nanopore_depth,
Annotations.nanopore_soft_clipped,
Annotations.nanopore_split_read,
Annotations.pacbio_presence,
Annotations.pacbio_depth,
Annotations.pacbio_soft_clipped,
Annotations.pacbio_split_read,
Annotations.tenx_presence,
Annotations.tenx_depth,
Annotations.tenx_soft_clipped,
Annotations.tenx_split_read,
Annotations.tenx_linked_read,
Annotations.comments,
Variants.id
).select_from(
Variants
).outerjoin(
Annotations, Annotations.variant_id == Variants.id
).filter(
Variants.genome == genome
)
params = request.args.to_dict()
rowTable = DataTables(params, query, columns)
data = rowTable.output_result()
return jsonify(data)
def note_list_json_route():
"""list notes, data endpoint"""
columns = [
ColumnDT(Note.id, mData='id'),
ColumnDT(Host.id, mData='host_id'),
ColumnDT(Host.address, mData='host_address'),
ColumnDT(Host.hostname, mData='host_hostname'),
# break pylint duplicate-code
ColumnDT(Service.proto, mData='service_proto'),
ColumnDT(Service.port, mData='service_port'),
ColumnDT(func.concat_ws('/', Service.port, Service.proto), mData='service'),
ColumnDT(Note.via_target, mData='via_target'),
ColumnDT(Note.xtype, mData='xtype'),
ColumnDT(Note.data, mData='data'),
ColumnDT(Note.tags, mData='tags'),
ColumnDT(Note.comment, mData='comment'),
ColumnDT(literal_column('1'), mData='_buttons', search_method='none', global_search=False)
]
query = db.session.query().select_from(Note).outerjoin(Host, Note.host_id == Host.id).outerjoin(Service, Note.service_id == Service.id)
if 'filter' in request.values:
query = apply_filters(query, FILTER_PARSER.parse(request.values.get('filter')), do_auto_join=False)
notes = DataTables(request.values.to_dict(), query, columns).output_result()
return jsonify(notes)
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('unit_id'))
columns.append(ColumnDT('rekening_id'))
columns.append(ColumnDT('units.kode'))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('rekenings.kode'))
columns.append(ColumnDT('rekenings.nama'))
query = DBSession.query(UnitRekening).join(Unit).join(Rekening)
rowTable = DataTables(req.GET, UnitRekening, 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(skpdrekening.user_id, skpdrekening.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, skpdrekening, query, columns)
return rowTable.output_result()
def test_with_filter_ok(self):
"""Return column with a specific filter."""
col = ColumnDT(User.name, search_method='like')
assert col.search_method == 'like'
def 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('kode'))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('level_id'))
columns.append(ColumnDT('urusan.nama'))
query = DBSession.query(Unit)
rowTable = DataTables(req, Unit, 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('level_id'))
columns.append(ColumnDT('urusan.nama'))
query = DBSession.query(Unit).filter(
Unit.urusan_id == Urusan.id,
or_(
Unit.nama.ilike('%%%s%%' % cari),
Unit.kode.ilike('%%%s%%' % cari),
Urusan.nama.ilike('%%%s%%' % cari),
))
rowTable = DataTables(req, Unit, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'headofnama':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Unit.id, Unit.kode, Unit.nama).filter(
Unit.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[2]
d['kode'] = k[1]
r.append(d)
return r
elif url_dict['act'] == 'headofkode':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Unit.id, Unit.kode).filter(
Unit.kode.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_with_valid_nulls_order(self):
"""Return column with a specific filter."""
col = ColumnDT(User.name, nulls_order='nullslast')
assert col.nulls_order == 'nullslast'
def get_data():
from app.helper import dt_to_str
columns = [
ColumnDT(Asin.id),
ColumnDT(Asin.site_url),
ColumnDT(Asin.asin),
ColumnDT(Asin.review_rating),
ColumnDT(Asin.quantity),
ColumnDT(Asin.unit),
ColumnDT(Asin.sell_price),
ColumnDT(Asin.link),
ColumnDT(Asin.created_at),
ColumnDT(Asin.status),
ColumnDT(Asin.description)
]
args = parser.parse(request.query_string)
order = args['order']
order_index = order[0]['column']
dir_asc = order[0]['dir']
if order_index == 1:
order = Asin.site_url
elif order_index == 2:
order = Asin.asin
elif order_index == 3:
order = Asin.review_rating
elif order_index == 4:
order = Asin.quantity
elif order_index == 5:
order = Asin.sell_price
elif order_index == 6:
order = Asin.link
elif order_index == 7:
order = Asin.link
elif order_index == 8:
order = Asin.created_at
elif order_index == 9:
order = Asin.status
elif order_index == 10:
order = Asin.description
else:
order = Asin.id
if dir_asc == 'desc':
order_by = desc(order)
else:
order_by = asc(order)
search_value = args['search']['value']
if search_value != '':
query = db.session.query().select_from(Asin)
else:
query = db.session.query().select_from(Asin).filter(
Asin.site_url.like('%' + search_value + '%')
| Asin.asin.like('%' + search_value + '%'))
query = query.order_by(order_by)
params = request.args.to_dict()
rowTable = DataTables(params, query, columns)
# returns what is needed by DataTable
return jsonify(rowTable.output_result())
def test_with_invalid_nulls_order(self):
"""Return column with a specific filter."""
with self.assertRaises(ValueError):
ColumnDT(User.name, nulls_order='invalid')
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('adjust_date', filter=_DTstrftime))
columns.append(ColumnDT('units.nama'))
#columns.append(ColumnDT('product_accepts.nama'))
query = DBSession.query(ProductAdjust)
rowTable = DataTables(req, ProductAdjust, 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('adjust_date', filter=_DTstrftime))
columns.append(ColumnDT('units.nama'))
#columns.append(ColumnDT('product_accepts.nama'))
query = DBSession.query(ProductAdjust
).filter(ProductAdjust.unit_id==Unit.id,
or_(Unit.nama.ilike('%%%s%%' % cari),
ProductAdjust.kode.ilike('%%%s%%' % cari),
ProductAdjust.nama.ilike('%%%s%%' % cari),))
rowTable = DataTables(req, ProductAdjust, query, columns)
return rowTable.output_result()
elif url_dict['act']=='hon_warehouse_adjust':
term = 'term' in params and params['term'] or ''
unit = 'unit_id' in params and params['unit_id'] or ''
rows = DBSession.query(ProductAdjust.id, ProductAdjust.kode, ProductAdjust.nama
).filter(ProductAdjust.nama.ilike('%%%s%%' % term),
ProductAdjust.unit_id==unit,
ProductAdjust.disabled==0).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
elif url_dict['act']=='hon_adjust_item':
term = 'term' in params and params['term'] or ''
adjust = 'adjust' in params and params['adjust'] or ''
a = DBSession.query(ProductAdjust).filter(ProductAdjust.id == adjust).first()
x = a.id
rows = DBSession.query(Product.id, Product.kode, Product.nama, ProductAdjustItem.qty
).filter(ProductAdjustItem.product_adjust_id==x,
Product.id==ProductAdjustItem.product_id,
Product.nama.ilike('%%%s%%' % term),).all()
r = []
for k in rows:
d={}
d['id'] = k[0]
d['value'] = k[2]
d['kode'] = k[1]
d['nama'] = k[2]
d['qty'] = k[3]
r.append(d)
return r
def vuln_list_json_route():
"""list vulns, data endpoint"""
columns = [
ColumnDT(literal_column('1'),
mData='_select',
search_method='none',
global_search=False),
ColumnDT(Vuln.id, mData='id'),
ColumnDT(Host.id, mData='host_id'),
ColumnDT(Host.address, mData='host_address'),
ColumnDT(Host.hostname, mData='host_hostname'),
ColumnDT(Service.proto, mData='service_proto'),
ColumnDT(Service.port, mData='service_port'),
ColumnDT(func.concat_ws('/', Service.port, Service.proto),
mData='service'),
ColumnDT(Vuln.via_target, mData='via_target'),
ColumnDT(Vuln.name, mData='name'),
ColumnDT(Vuln.xtype, mData='xtype'),
ColumnDT(Vuln.severity, mData='severity'),
ColumnDT(Vuln.refs, mData='refs'),
ColumnDT(Vuln.tags, mData='tags'),
ColumnDT(Vuln.comment, mData='comment'),
ColumnDT(literal_column('1'),
mData='_buttons',
search_method='none',
global_search=False)
]
query = db.session.query().select_from(Vuln).outerjoin(
Host, Vuln.host_id == Host.id).outerjoin(Service,
Vuln.service_id == Service.id)
if 'filter' in request.values:
query = apply_filters(query,
FILTER_PARSER.parse(
request.values.get('filter')),
do_auto_join=False)
vulns = DataTables(request.values.to_dict(), query,
columns).output_result()
return Response(json.dumps(vulns, cls=SnerJSONEncoder),
mimetype='application/json')