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 view_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('uraian'))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('disabled'))
query = DBSession.query(AsetRuang)
rowTable = DataTables(req, AsetRuang, query, columns)
return rowTable.output_result()
elif url_dict['act']=='headofnama':
term = 'term' in params and params['term'] or ''
q = DBSession.query(AsetRuang.id,AsetRuang.kode,AsetRuang.uraian).\
filter(#AsetRuang.unit_id == ses['unit_id'],
AsetRuang.uraian.ilike('%%%s%%' % term)).\
order_by(AsetRuang.uraian)
rows = q.all()
r = []
for k in rows:
d={}
d['id'] = k[0]
d['value'] = k[2]
d['kode'] = k[1]
d['uraian'] = k[2]
r.append(d)
return r
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 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 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 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 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 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 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 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(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 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 sp2d_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('no_sp2d'))
columns.append(ColumnDT('no_sp2d'))
columns.append(ColumnDT('tgl_sp2d', filter = self._DT_strftime))
columns.append(ColumnDT('uraian'))
columns.append(ColumnDT('sp2damt', filter = self._DT_number_format))
columns.append(ColumnDT('sp2dppn', filter = self._DT_number_format))
columns.append(ColumnDT('sp2dpph', filter = self._DT_number_format))
columns.append(ColumnDT('sp2dpot', filter = self._DT_number_format))
columns.append(ColumnDT('sp2dnet', filter = self._DT_number_format))
columns.append(ColumnDT('nm_penerima'))
columns.append(ColumnDT('bank_penerima'))
columns.append(ColumnDT('rek_penerima'))
columns.append(ColumnDT('npwp'))
query = get_query()
rowTable = DataTables(req, SimdaSp2d, query, columns)
return rowTable.output_result()
elif url_dict['act']=='csv':
return
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 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 ar_payment_item_act(self):
ses = self.request.session
req = self.request
params = req.params
url_dict = req.matchdict
kegiatan_sub_id = 'kegiatan_sub_id' in params and params['kegiatan_sub_id'] or 0
if url_dict['act']=='grid':
columns = []
columns.append(ColumnDT('id'))
#columns.append(ColumnDT('kegiatan_subs.kegiatans.kode'))
#columns.append(ColumnDT('kegiatan_subs.no_urut'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('ref_kode'))
columns.append(ColumnDT('ref_nama'))
columns.append(ColumnDT('tanggal', filter=self._DTstrftime))
columns.append(ColumnDT('amount', filter=self._number_format))
columns.append(ColumnDT('posted'))
query = DBSession.query(ARPaymentTransaksi).filter(
ARPaymentTransaksi.tahun == ses['tahun'],
ARPaymentTransaksi.unit_id == ses['unit_id'],
ARPaymentTransaksi.tanggal == ses['tanggal'],
)
rowTable = DataTables(req, ARPaymentTransaksi, 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 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 gaji_routes_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('path'))
columns.append(ColumnDT('factory'))
columns.append(ColumnDT('perm_name'))
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('%%%s%%' % term),
Route.perm_name != None).\
order_by(Route.path).all()
r = []
for k in rows:
d={}
d['id'] = k[0]
d['value'] = k[1]
r.append(d)
return r
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 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 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 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 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 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 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 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 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 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 service_grouped_json_route():
"""view grouped services, data endpoint"""
info_column = service_info_column(request.args.get('crop'))
columns = [
ColumnDT(info_column, mData='info'),
ColumnDT(func.count(Service.id),
mData='cnt_services',
global_search=False),
]
# join allows filter over host attrs
query = db.session.query().select_from(Service).join(Host).group_by(
info_column)
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 profile_webauthn_list_json_route():
"""get registered credentials list for current user"""
columns = [
ColumnDT(WebauthnCredential.id,
mData='id',
search_method='none',
global_search=False),
ColumnDT(WebauthnCredential.registered, mData='registered'),
ColumnDT(WebauthnCredential.name, mData='name'),
ColumnDT(literal_column('1'),
mData='_buttons',
search_method='none',
global_search=False)
]
query = db.session.query().select_from(WebauthnCredential) \
.filter(WebauthnCredential.user_id == current_user.id) \
.order_by(WebauthnCredential.registered.asc())
creds = DataTables(request.values.to_dict(), query,
columns).output_result()
return Response(json.dumps(creds, cls=SnerJSONEncoder),
mimetype='application/json')
def vuln_grouped_json_route():
"""view grouped vulns, data endpoint"""
columns = [
ColumnDT(Vuln.name, mData='name'),
ColumnDT(Vuln.severity, mData='severity'),
ColumnDT(Vuln.tags, mData='tags'),
ColumnDT(func.count(Vuln.id), mData='cnt_vulns', global_search=False),
]
# join allows filter over host attrs
query = db.session.query().select_from(Vuln).join(Host).group_by(
Vuln.name, Vuln.severity, Vuln.tags)
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')
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(func.concat_ws('/', Service.port, Service.proto),
mData='service'),
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')
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 excl_list_json_route():
"""list target exclusions, data endpoint"""
columns = [
ColumnDT(Excl.id, mData='id'),
ColumnDT(Excl.family, mData='family'),
ColumnDT(Excl.value, mData='value'),
ColumnDT(Excl.comment, mData='comment'),
ColumnDT(literal_column('1'),
mData='_buttons',
search_method='none',
global_search=False)
]
query = db.session.query().select_from(Excl)
if 'filter' in request.values:
query = apply_filters(query,
filter_parser.parse(
request.values.get('filter')),
do_auto_join=False)
excls = DataTables(request.values.to_dict(), query,
columns).output_result()
return Response(json.dumps(excls, cls=SnerJSONEncoder),
mimetype='application/json')
def usr_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'))
columns.append(ColumnDT('group_name'))
query = DBSession.query(User.id, User.user_name, User.email, User.status,
User.last_login_date, User.registered_date,
Group.group_name).filter(User.id==UserGroup.user_id,
UserGroup.group_id==Group.id)
rowTable = DataTables(req, User, 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'))
columns.append(ColumnDT('nama'))
query = DBSession.query(User.id, User.user_name, User.email, User.status,
User.last_login_date, User.registered_date,
UnitModel.nama).filter(
User.id==UserUnitModel.user_id,
UnitModel.id==UserUnitModel.unit_id,
UserGroup.user_id==User.id,
UserGroup.group_id==gid)
rowTable = DataTables(req, User, query, columns)
return rowTable.output_result()
def test_ordering(session):
"""Test if it returns a list with the correct order."""
columns = [ColumnDT(User.id, ), ColumnDT(User.name)]
query = session.query().select_from(User)
# Descending
params = create_dt_params(columns, order=[{"column": 1, "dir": "desc"}])
rowTable = DataTables(params, query, columns)
res = rowTable.output_result()
assert res["data"][0]["1"] == "zzz_User"
# Ascending
params = create_dt_params(columns, order=[{"column": 1, "dir": "asc"}])
rowTable = DataTables(params, query, columns)
res = rowTable.output_result()
assert res["data"][0]["1"] == "000_User"
def view_act(request):
req = request
params = req.params
url_dict = req.matchdict
if url_dict['act'] == 'sspd':
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('arinvoices.kode'))
columns.append(ColumnDT('arinvoices.wp_nama'))
columns.append(ColumnDT('arinvoices.op_kode'))
columns.append(ColumnDT('arinvoices.op_nama'))
columns.append(ColumnDT('arinvoices.rek_nama'))
columns.append(ColumnDT('bayar', filter=_DTnumberformat))
columns.append(ColumnDT('tgl_bayar', filter=_DTstrftime))
columns.append(ColumnDT('posted'))
query = DBSession.query(ARSspd).join(ARInvoice).\
filter(ARSspd.posted==0,
ARInvoice.unit_id==request.session['unit_id'])
rowTable = DataTables(req, ARSspd, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'grid':
#Nambahin param sts_id untuk percobaan sementara, karena session sts_id tidak jalan
sts_id = 'sts_id' in params and params['sts_id'] or 0
print '----------------------ID STS-------------------------', sts_id
columns = []
columns.append(ColumnDT('sspd_id'))
columns.append(ColumnDT('sts_id'))
columns.append(ColumnDT('rekening_id'))
columns.append(ColumnDT('rekenings.kode'))
columns.append(ColumnDT('rekenings.nama'))
columns.append(ColumnDT('jumlah', filter=_DTnumberformat))
query = DBSession.query(ARStsItem).join(Rekening).\
filter(ARStsItem.sts_id==sts_id)#request.session['sts_id'])
rowTable = DataTables(req, ARStsItem, query, columns)
return rowTable.output_result()
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 = [
ColumnDT(User.id, ),
ColumnDT(User.name),
ColumnDT(Address.description),
ColumnDT(User.created_at),
]
# DESC
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()
assert res['data'][0]['2'] == 'zzz_aaa'
# ASC
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()
assert res['data'][0]['2'] == '000_aaa'
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_request.kode'))
columns.append(ColumnDT('product_request.nama'))
columns.append(ColumnDT('approval_date', filter=_DTstrftime))
columns.append(ColumnDT('approval_level'))
query = DBSession.query(ProductReqAppr)
rowTable = DataTables(req, ProductReqAppr, 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('units.nama'))
columns.append(ColumnDT('product_request.kode'))
columns.append(ColumnDT('product_request.nama'))
columns.append(ColumnDT('approval_date', filter=_DTstrftime))
columns.append(ColumnDT('approval_level'))
query = DBSession.query(ProductReqAppr).filter(
ProductReqAppr.product_request_id == ProductRequest.id,
ProductReqAppr.unit_id == Unit.id,
or_(
ProductRequest.nama.ilike('%%%s%%' % cari),
ProductRequest.kode.ilike('%%%s%%' % cari),
Unit.nama.ilike('%%%s%%' % cari),
))
rowTable = DataTables(req, ProductReqAppr, query, columns)
return rowTable.output_result()
def test_ordering_nulls(session):
"""Test if it returns a list with the correct nulls order."""
columns = [
ColumnDT(User.id, ),
ColumnDT(User.name),
ColumnDT(Address.description, nulls_order="nullsfirst"),
ColumnDT(User.created_at),
]
query = session.query().select_from(User).join(Address)
# NULLS FIRST
params = create_dt_params(columns, order=[{"column": 2, "dir": "desc"}])
rowTable = DataTables(params, query, columns)
res = rowTable.output_result()
if "error" in res:
# sqlite3 doesn't support nulls ordering
assert 'sqlite3.OperationalError) near "NULLS"' in res["error"]
columns = [
ColumnDT(User.id, ),
ColumnDT(User.name),
ColumnDT(Address.description, nulls_order="nullslast"),
ColumnDT(User.created_at),
]
# NULLS LAST
params = create_dt_params(columns, order=[{"column": 2, "dir": "asc"}])
rowTable = DataTables(params, query, columns)
res = rowTable.output_result()
if "error" in res:
# sqlite3 doesn't support nulls ordering
assert 'sqlite3.OperationalError) near "NULLS"' in res["error"]
def view_grid(request):
req = request
ses = req.session
params = req.params
url_dict = req.matchdict
# if not 'logged' in ses or not ses['logged'] or ses['userid']!='sa':
# url = self.request.resource_url(self.context, '')
# self.d['msg'] = ""
# return self.d
date_from = 'date_from' in req.params and req.params['date_from']\
or datetime.now().strftime('%d-%m-%Y') #"07-09-2015"
date_to = 'date_to' in req.params and req.params['date_to'] or date_from
ddate_from = datetime.strptime(date_from, '%d-%m-%Y')
ddate_to = datetime.strptime(date_to, '%d-%m-%Y')
if url_dict['act'] == 'grid':
columns, query = get_columns()
qry = query.filter(PosSppt.tgl_cetak_sppt.between(
ddate_from, ddate_to))
rowTable = DataTables(req.GET, PosSppt, qry, columns)
return rowTable.output_result()
elif url_dict['act'] == 'rekon':
query = PosPbbDBSession.query(PosSppt.kd_propinsi,
PosSppt.kd_dati2,
PosSppt.kd_kecamatan,
PosSppt.kd_kelurahan,
PosSppt.kd_blok,
PosSppt.no_urut,
PosSppt.kd_jns_op,
PosSppt.thn_pajak_sppt).\
filter(PosSppt.tgl_cetak_sppt.between(ddate_from,ddate_to))
rows = query.all()
queryPbb = PbbDBSession.query(Sppt.kd_propinsi,
Sppt.kd_dati2,
Sppt.kd_kecamatan,
Sppt.kd_kelurahan,
Sppt.kd_blok,
Sppt.no_urut,
Sppt.kd_jns_op,
Sppt.thn_pajak_sppt).\
filter(Sppt.tgl_cetak_sppt.between(ddate_from,ddate_to))
rowPbbs = queryPbb.all()
rowNotFound = []
if len(rows) != len(rowPbbs):
rowNotFound = list(set(rows) - set(rowPbbs))
#print "**DEBUG**", len(rows), len(rowPbbs)
columns, query = get_columns()
qry = query.filter(
tuple_(PosSppt.kd_propinsi, PosSppt.kd_dati2, PosSppt.kd_kecamatan,
PosSppt.kd_kelurahan, PosSppt.kd_blok, PosSppt.no_urut,
PosSppt.kd_jns_op, PosSppt.thn_pajak_sppt).in_(rowNotFound))
rowTable = DataTables(req.GET, PosSppt, qry, columns)
return rowTable.output_result()
elif url_dict['act'] == 'update':
bayar.set_raw(req.params['id'])
query = PosPbbDBSession.query(PosSppt).\
filter_by(kd_propinsi = bayar['kd_propinsi'],
kd_dati2 = bayar['kd_dati2'],
kd_kecamatan = bayar['kd_kecamatan'],
kd_kelurahan = bayar['kd_kelurahan'],
kd_blok = bayar['kd_blok'],
no_urut = bayar['no_urut'],
kd_jns_op = bayar['kd_jns_op'],
thn_pajak_sppt = bayar['thn_pajak_sppt'])
row = query.first()
if row:
rowPbb = Sppt()
rowPbb.kd_propinsi = unicode(row.kd_propinsi)
rowPbb.kd_dati2 = unicode(row.kd_dati2)
rowPbb.kd_kecamatan = unicode(row.kd_kecamatan)
rowPbb.kd_kelurahan = unicode(row.kd_kelurahan)
rowPbb.kd_blok = unicode(row.kd_blok)
rowPbb.no_urut = unicode(row.no_urut)
rowPbb.kd_jns_op = unicode(row.kd_jns_op)
rowPbb.thn_pajak_sppt = unicode(row.thn_pajak_sppt)
rowPbb.kd_kantor = unicode(row.kd_kantor)
rowPbb.kd_kanwil = unicode(row.kd_kanwil)
rowPbb.kd_tp = unicode(row.kd_tp)
rowPbb.pbb_yg_harus_dibayar_sppt = row.pbb_yg_harus_dibayar_sppt
rowPbb.status_pembayaran_sppt = unicode(row.status_pembayaran_sppt)
rowPbb.tgl_cetak_sppt = row.tgl_cetak_sppt
rowPbb.nip_pencetak_sppt = unicode(row.nip_pencetak_sppt)
try:
PbbDBSession.add(rowPbb)
PbbDBSession.flush()
except:
return dict(status=0, message='Gagal %s' % bayar.get_raw())
return dict(status=1, message='Sukses')
def gaji_unit_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'))
groups = groupfinder(req.user, req)
ids = []
if req.user.id == 1 or 'group:admin' in groups:
query = UnitModel.query() #DBSession.query(UnitModel)
else:
units = DBSession.query(
UserUnitModel.unit_id, UserUnitModel.sub_unit,
UnitModel.kode).join(UnitModel).filter(
UnitModel.id == UserUnitModel.unit_id,
UserUnitModel.user_id == req.user.id).all()
for unit in units:
if unit.sub_unit:
rows = DBSession.query(UnitModel.id).filter(
UnitModel.kode.ilike('%s%%' % unit.kode)).all()
else:
rows = DBSession.query(UnitModel.id).filter(
UnitModel.kode == unit.kode).all()
for i in range(len(rows)):
ids.append(rows[i])
query = DBSession.query(UnitModel).filter(
(UnitModel.id).in_(ids))
rowTable = DataTables(req, UnitModel, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'changeid':
ids = UserUnitModel.unit_granted(req.user.id, params['unit_id'])
if req.user.id>1 and 'g:admin' not in groupfinder(req.user, req)\
and not ids:
return {
'success':
False,
'msg':
'Anda tidak boleh mengubah ke unit yang bukan hak akses anda'
}
row = UnitModel.get_by_id('unit_id' in params and params['unit_id']
or 0)
if row:
ses['unit_id'] = row.id
ses['unit_kd'] = row.kode
ses['unit_nm'] = row.nama
return {'success': True, 'msg': 'Sukses ubah SKPD'}
elif url_dict['act'] == 'headofnama':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(
UnitModel.id, UnitModel.kode, UnitModel.nama).filter(
UnitModel.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'] == 'import':
rows = DBSession.execute(
"""SELECT a.kode, a.nama, a.passwd, b.unit_id
FROM admin.users2 a
INNER JOIN admin.user_units2 b
ON a.id = b.id""").all()
for kode, nama, passwd, unit_id in rows:
user = Users()
user.user_name = nama
user.user_password = passwd
user.email = ''.join([nama, '@tangerangkab.org'])
user.status = 1
DBSession.add(user)
DBSession.flush()
if user.id:
user_unit = UserUnitModel()
user_unit.user_id = user.id
user_unit.unit_id = unit_id
user_unit.status = 1
DBSession.add(user_unit)
DBSession.flush()
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('tanggal', filter=_DTstrftime))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('approval_level'))
query = DBSession.query(ProductPlan)
rowTable = DataTables(req, ProductPlan, 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('tanggal', filter=_DTstrftime))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('approval_level'))
query = DBSession.query(ProductPlan).filter(
ProductPlan.unit_id == Unit.id,
or_(
ProductPlan.nama.ilike('%%%s%%' % cari),
ProductPlan.kode.ilike('%%%s%%' % cari),
Unit.nama.ilike('%%%s%%' % cari),
))
rowTable = DataTables(req, ProductPlan, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'hon_plan_approval':
term = 'term' in params and params['term'] or ''
unit = 'unit_id' in params and params['unit_id'] or ''
rows = DBSession.query(ProductPlan.id, ProductPlan.kode,
ProductPlan.nama).filter(
ProductPlan.nama.ilike('%%%s%%' % term),
ProductPlan.unit_id == unit,
ProductPlan.approval_level == 0,
ProductPlan.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'] == 'hok_plan_approval':
term = 'term' in params and params['term'] or ''
unit = 'unit_id' in params and params['unit_id'] or ''
rows = DBSession.query(ProductPlan.id, ProductPlan.kode,
ProductPlan.nama).filter(
ProductPlan.kode.ilike('%%%s%%' % term),
ProductPlan.unit_id == unit,
ProductPlan.approval_level == 0,
ProductPlan.disabled == 0).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['kode'] = k[1]
d['nama'] = k[2]
r.append(d)
return r
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')
#return awal, akhir
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('arinvoices.kode'))
columns.append(ColumnDT('arinvoices.wp_nama'))
columns.append(ColumnDT('arinvoices.op_kode'))
columns.append(ColumnDT('arinvoices.op_nama'))
columns.append(ColumnDT('arinvoices.rek_nama'))
columns.append(ColumnDT('bayar', filter=_DTnumberformat))
columns.append(ColumnDT('tgl_bayar', filter=_DTstrftime))
columns.append(ColumnDT('posted'))
query = DBSession.query(ARSspd).join(ARInvoice)\
.filter(ARSspd.tgl_bayar.between(awal,akhir))
rowTable = DataTables(req.GET, ARSspd, query, columns)
return rowTable.output_result()
u = request.user.id
a = DBSession.query(
UserGroup.group_id).filter(UserGroup.user_id == u).first()
b = '%s' % a
c = int(b)
x = DBSession.query(
UserUnit.unit_id).filter(UserUnit.user_id == u).first()
if x == 'None' or not x: #Untuk BUD
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('arinvoices.kode'))
columns.append(ColumnDT('arinvoices.wp_nama'))
columns.append(ColumnDT('arinvoices.op_kode'))
columns.append(ColumnDT('arinvoices.op_nama'))
columns.append(ColumnDT('arinvoices.rek_nama'))
columns.append(ColumnDT('bayar', filter=_DTnumberformat))
columns.append(ColumnDT('tgl_bayar', filter=_DTstrftime))
columns.append(ColumnDT('posted'))
query = DBSession.query(ARSspd).join(ARInvoice)
rowTable = DataTables(req, ARSspd, query, columns)
return rowTable.output_result()
y = '%s' % x
z = int(y)
if c == 2: #Untuk Bendahara
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('arinvoices.kode'))
columns.append(ColumnDT('arinvoices.wp_nama'))
columns.append(ColumnDT('arinvoices.op_kode'))
columns.append(ColumnDT('arinvoices.op_nama'))
columns.append(ColumnDT('arinvoices.rek_nama'))
columns.append(ColumnDT('bayar', filter=_DTnumberformat))
columns.append(ColumnDT('tgl_bayar', filter=_DTstrftime))
columns.append(ColumnDT('posted'))
query = DBSession.query(ARSspd).filter(
ARSspd.arinvoice_id == ARInvoice.id,
ARInvoice.unit_id == z).join(ARInvoice)
rowTable = DataTables(req, ARSspd, query, columns)
return rowTable.output_result()
else: #Untuk BUD
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('arinvoices.kode'))
columns.append(ColumnDT('arinvoices.wp_nama'))
columns.append(ColumnDT('arinvoices.op_kode'))
columns.append(ColumnDT('arinvoices.op_nama'))
columns.append(ColumnDT('arinvoices.rek_nama'))
columns.append(ColumnDT('bayar', filter=_DTnumberformat))
columns.append(ColumnDT('tgl_bayar', filter=_DTstrftime))
columns.append(ColumnDT('posted'))
query = DBSession.query(ARSspd).join(ARInvoice)
rowTable = DataTables(req, ARSspd, query, columns)
return rowTable.output_result()
def data(dset_id,
type,
subject,
session,
cohort,
ratings,
comments,
only_ratings=0):
"""Return server side data for datatable."""
all_raters = request.args.get('all_raters', 0, type=int)
columns = [
ColumnDT(Image.name),
ColumnDT(Rating.rating),
]
# If there are ratings insert rating info
if ratings:
columns.insert(2, ColumnDT(Rating.timestamp))
# TODO Figure out how to insert subratings here
if comments:
columns.insert(2, ColumnDT(Rating.comment))
if all_raters:
columns.insert(2, ColumnDT(Rater.username))
# Check if there are cohort labels
if cohort:
columns.insert(1, ColumnDT(Image.cohort))
# Check if there are sess labels
if session:
columns.insert(1, ColumnDT(Image.session))
# Check if there are sub labels
if subject:
columns.insert(1, ColumnDT(Image.subject))
# Check if there are type labels
if type:
columns.insert(1, ColumnDT(Image.imgtype))
# Ratings query for single user (only ratings)
if all_raters or current_user.is_anonymous:
query = db.session.query().\
select_from(Image).\
filter(Image.dataset_id == dset_id,
Rating.rating > 0).\
join(Rating).\
join(Rater) if only_ratings \
else db.session.query().\
select_from(Image).\
filter(Image.dataset_id == dset_id).\
join(Rating, isouter=True).\
join(Rater, isouter=True)
else:
if only_ratings:
query = db.session.query().\
select_from(Image).\
filter(Image.dataset_id == dset_id,
Rating.rater == current_user,
Rating.rating > 0).\
join(Rating).\
join(Rater)
else:
query = db.session.query().\
select_from(Image).\
filter(Image.dataset_id == dset_id,
or_(Rating.rater == current_user,
Rating.rater == None)).\
join(Rating, isouter=True).\
join(Rater, isouter=True)
params = request.args.to_dict()
rowTable = DataTables(params, query, columns)
return jsonify(rowTable.output_result())
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 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 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('wajibpajaks.nama'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('pajaks.kode'))
columns.append(ColumnDT('wilayahs.nama'))
columns.append(ColumnDT('status', filter=_DTactive))
query = DBSession.query(ObjekPajak).join(WajibPajak).outerjoin(Pajak).\
outerjoin(Wilayah)
if request.user.id != 1:
query = query.join(Unit).join(UserUnit).\
filter(UserUnit.user_id==request.user.id)
rowTable = DataTables(req.GET, ObjekPajak, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'hon':
term = 'term' in params and params['term'] or ''
query = DBSession.query(ObjekPajak).join(Pajak).\
filter(ObjekPajak.nama.ilike('%%%s%%' % term))
wp_id = 'wpid' in req.params and req.params['wpid'] or None
if wp_id:
query = query.filter(ObjekPajak.wajibpajak_id == wp_id)
rows = query.all()
r = []
for k in rows:
d = {}
d['id'] = k.id
d['value'] = k.nama
d['alamat_1'] = k.alamat_1
d['alamat_2'] = k.alamat_2
d['tarif'] = k.pajaks.tarif
r.append(d)
return r
elif url_dict['act'] == 'hon1':
x = request.user.id
term = 'term' in params and params['term'] or ''
d = DBSession.query(User.email).filter(User.id == x).first()
rows = DBSession.query(ObjekPajak).join(WajibPajak).join(Pajak).\
filter(ObjekPajak.nama.ilike('%%%s%%' % term),
ObjekPajak.wajibpajak_id==WajibPajak.id,
WajibPajak.email==d,
ObjekPajak.pajak_id==Pajak.id).all()
r = []
for k in rows:
print k
d = {}
d['id'] = k.id
d['value'] = k.nama
d['sp_id'] = k.wajibpajaks.id
d['sp_nm'] = k.wajibpajaks.nama
d['unit_id'] = k.units.id
d['unit_nm'] = k.units.nama
d['tarif'] = k.pajaks.tarif
r.append(d)
return r
def gaji_potongan_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('nip'))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('gaji_bersih', filter=self._number_format))
columns.append(ColumnDT('amount_01', filter=self._number_format))
columns.append(ColumnDT('amount_02', filter=self._number_format))
columns.append(ColumnDT('amount_03', filter=self._number_format))
columns.append(ColumnDT('amount_04', filter=self._number_format))
columns.append(ColumnDT('amount_05', filter=self._number_format))
columns.append(ColumnDT('amount_06', filter=self._number_format))
columns.append(ColumnDT('amount_07', filter=self._number_format))
columns.append(ColumnDT('amount_08', filter=self._number_format))
columns.append(ColumnDT('amount_09', filter=self._number_format))
columns.append(ColumnDT('amount_10', filter=self._number_format))
columns.append(ColumnDT('amount_11', filter=self._number_format))
columns.append(ColumnDT('amount_12', filter=self._number_format))
query = DBSession.query(GajiPegawai.id, GajiPegawai.nip, GajiPegawai.nama, GajiPegawai.gaji_bersih,
GajiPotongan.amount_01, GajiPotongan.amount_02, GajiPotongan.amount_03,
GajiPotongan.amount_04, GajiPotongan.amount_05, GajiPotongan.amount_06,
GajiPotongan.amount_07, GajiPotongan.amount_08, GajiPotongan.amount_09,
GajiPotongan.amount_10, GajiPotongan.amount_11, GajiPotongan.amount_12,
).join(GajiPotongan).filter(
GajiPegawai.tahun == ses['tahun'],
GajiPegawai.bulan == ses['bulan'],
)
rows = UserUnitModel.get_filtered(self.request)
if rows and rows.sub_unit:
query = query.filter(GajiPegawai.unitkd.like( '%s%%' % self.request.session['unit_kd']))
else:
query = query.filter(GajiPegawai.unitkd== self.request.session['unit_kd'])
rowTable = DataTables(req, GajiPegawai, query, columns)
return rowTable.output_result()
elif url_dict['act']=='upload':
filename = self.request.POST['files'].filename
input_file = self.request.POST['files'].file
name, ext = os.path.splitext(filename)
if ext not in ('.xls','.csv','.xlsx'):
return dict(success=False, notes='File extension not allowed.')
file_path = os.path.join('/tmp', '%s%s' % (uuid.uuid4(),ext))
temp_file_path = file_path + '~'
output_file = open(temp_file_path, 'wb')
input_file.seek(0)
while True:
data = input_file.read(2<<16)
if not data:
break
output_file.write(data)
output_file.close()
os.rename(temp_file_path, file_path)
if ext == '.xls':
import_data(xls_reader(file_path))
return dict(success=True, notes=file_path)
def gaji_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('nip'))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('gaji_kotor', filter=self._number_format))
columns.append(ColumnDT('potongan', filter=self._number_format))
columns.append(ColumnDT('gaji_bersih', filter=self._number_format))
query = DBSession.query(GajiPegawai).filter(
GajiPegawai.tahun == ses['tahun'],
GajiPegawai.bulan == ses['bulan'])
rows = UserUnitModel.get_filtered(self.request)
if rows and rows.sub_unit:
query = query.filter(
GajiPegawai.unitkd.like('%s%%' % ses['unit_kd']))
else:
query = query.filter(GajiPegawai.unitkd == ses['unit_kd'])
rowTable = DataTables(req, GajiPegawai, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'headofnip':
nip = 'term' in params and params['term'] or ''
rows = DBSession.query(
GajiPegawai.id, GajiPegawai.nip, GajiPegawai.nama,
GajiPegawai.gaji_bersih).filter(
GajiPegawai.tahun == ses['tahun'],
GajiPegawai.bulan == ses['bulan'],
GajiPegawai.unitkd == ses['unit_kd'],
GajiPegawai.nip.ilike('%s%%' % nip)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['nip'] = k[1]
d['nama'] = k[2]
d['gaji_bersih'] = k[3]
r.append(d)
return r
elif url_dict['act'] == 'import':
self.d['msg'] = 'Gagal Import Gagal'
engine_mssql = create_engine(
'mssql+pyodbc:///?odbc_connect={0}'.format(
urllib.quote_plus(EngineMssql[0])))
sqlselect = text("""SELECT *
FROM pegawai_gaji
WHERE tahun=:tahun AND bulan=:bulan AND jenis=:jenis
ORDER by nip""")
srcs = engine_mssql.execute(sqlselect,
tahun=self.session['tahun'],
bulan=self.session['bulan'],
jenis=0).all()
for src in srcs.fetchall():
gajiPg = GajiPegawai()
gajiPg.from_dict(src)
DBSession.add(gajiPg)
DBSession.flush()
DBSession.commit()
return self.d
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 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(Unit)
rowTable = DataTables(req.GET, Unit, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'hon':
term = 'term' in params and params['term'] or ''
print dir(req.user)
qry = DBSession.query(Unit.id, Unit.nama)
qry = qry.filter( #Unit.is_summary==0,
Unit.nama.ilike('%%%s%%' % term))
if req.user.user_name != 'admin':
qry = qry.join(UserUnit)
qry = qry.filter(UserUnit.user_id == req.user.id)
rows = qry.all()
# IF USER IS LOGGED IN AND IS SKPD=TRUE
# ADD FILTER ONLY HIS DATA
#
r = []
for k in rows:
if len(rows) == 1:
req.session['a_unit_id'] = k[0]
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['nama'] = k[1]
r.append(d)
return r
elif url_dict['act'] == 'hon_user':
term = 'term' in params and params['term'] or ''
qry = DBSession.query(Unit.id, Unit.nama)
rows = qry.filter( #Unit.is_summary==0,
Unit.nama.ilike('%%%s%%' % term)).all()
# IF USER IS LOGGED IN AND IS SKPD=TRUE
# ADD FILTER ONLY HIS DATA
#
r = []
for k in rows:
if len(rows) == 1:
req.session['a_unit_id'] = k[0]
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['nama'] = k[1]
r.append(d)
return r
elif url_dict['act'] == 'hon_reg':
term = 'term' in params and params['term'] or ''
unit_id = 'unit_id' in params and params['unit_id'] or 0
rows = DBSession.query(Unit.id, Unit.nama).filter(
Unit.id == unit_id, Unit.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['nama'] = k[1]
r.append(d)
return r
elif url_dict['act'] == 'hon_ob':
term = 'term' in params and params['term'] or ''
unit_id = 'unit_id' in params and params['unit_id'] or 0
rows = DBSession.query(Unit.id, Unit.nama).filter(
Unit.id == unit_id, Unit.nama.ilike('%%%s%%' % term)).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['nama'] = k[1]
r.append(d)
return r
elif url_dict['act'] == 'hon_fast':
term = 'term' in params and params['term'] or ''
rows = DBSession.query(Unit.id, Unit.nama).filter(
Unit.nama.ilike('%%%s%%' % term), Unit.level_id == 3).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['nama'] = k[1]
r.append(d)
return r
elif url_dict['act'] == 'hon_wp':
term = 'term' in params and params['term'] or ''
u = request.user.id
a = DBSession.query(
UserGroup.group_id).filter(UserGroup.user_id == u).first()
b = '%s' % a
c = int(b)
if c == 1: #Untuk login WP
x = DBSession.query(
UserUnit.unit_id).filter(UserUnit.user_id == u).first()
if x == 'None' or not x:
return {'success': False}
y = '%s' % x
z = int(y)
rows = DBSession.query(Unit.id, Unit.nama).filter(
Unit.id == z, Unit.nama.ilike('%%%s%%' % term),
Unit.level_id == 3).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['nama'] = k[1]
r.append(d)
return r
elif c == 2: #Untuk login Bendahara
x = DBSession.query(
UserUnit.unit_id).filter(UserUnit.user_id == u).first()
if x == 'None' or not x:
return {'success': False}
y = '%s' % x
z = int(y)
rows = DBSession.query(Unit.id, Unit.nama).filter(
Unit.id == z, Unit.nama.ilike('%%%s%%' % term),
Unit.level_id == 3).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['nama'] = k[1]
r.append(d)
return r
elif c == 3: #Untuk login Admin
rows = DBSession.query(Unit.id, Unit.nama).filter(
Unit.nama.ilike('%%%s%%' % term), Unit.level_id == 3).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['nama'] = k[1]
r.append(d)
return r
else: #Untuk login BUD
rows = DBSession.query(Unit.id, Unit.nama).filter(
Unit.nama.ilike('%%%s%%' % term), Unit.level_id == 3).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['nama'] = k[1]
r.append(d)
return r
def lumiera_qa_json():
columns = list()
columns.append(ColumnDT('question', filter=_default_value_view))
columns.append(ColumnDT('answer', filter=_default_value_view))
columns.append(ColumnDT('timestamp', filter=_timestamp_value_view))
return jsonify(**DataTables(request, Lumiera_sprashivai_qa, db.session.query(Lumiera_sprashivai_qa), columns).output_result())
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('accept_date', filter=_DTstrftime))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('product_delivers.nama'))
query = DBSession.query(ProductAccept)
rowTable = DataTables(req, ProductAccept, 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('accept_date', filter=_DTstrftime))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('product_delivers.nama'))
query = DBSession.query(ProductAccept).filter(
ProductAccept.product_deliver_id == ProductDeliver.id,
ProductAccept.unit_id == Unit.id,
or_(
ProductDeliver.nama.ilike('%%%s%%' % cari),
Unit.nama.ilike('%%%s%%' % cari),
))
rowTable = DataTables(req, ProductAccept, query, columns)
return rowTable.output_result()
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.product_accept_id
rows = DBSession.query(
Product.id, Product.kode, Product.nama, Product.qty,
ProductAcceptItem.qty, ProductDeliverItem.qty).filter(
ProductAccept.id == x,
ProductAccept.product_deliver_id ==
ProductDeliverItem.product_deliver_id,
ProductAcceptItem.product_accept_id == ProductAccept.id,
ProductAcceptItem.product_id == ProductDeliverItem.product_id,
Product.id == ProductAcceptItem.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]
d['a_qty'] = k[4]
d['d_qty'] = k[5]
r.append(d)
print '----------------------Hasil Headof----------------------', r
return r
elif url_dict['act'] == 'hon_adjust':
term = 'term' in params and params['term'] or ''
unit = 'unit_id' in params and params['unit_id'] or ''
rows = DBSession.query(ProductAccept.id, ProductAccept.kode,
ProductAccept.nama).filter(
ProductAccept.nama.ilike('%%%s%%' % term),
ProductAccept.unit_id == unit,
ProductAccept.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'] == 'hok_adjust':
term = 'term' in params and params['term'] or ''
unit = 'unit_id' in params and params['unit_id'] or ''
rows = DBSession.query(ProductAccept.id, ProductAccept.kode,
ProductAccept.nama).filter(
ProductAccept.kode.ilike('%%%s%%' % term),
ProductAccept.unit_id == unit,
ProductAccept.disabled == 0).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['kode'] = k[1]
d['nama'] = k[2]
r.append(d)
return r
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():
"""Return server side data."""
# Getting html datatables parameters
params = request.args.to_dict()
# Getting samples selected
lsams = session["dt_samples"]
#Get all the whole table queried as a subquery (So can be used in SELECT * FROM this_subquery) for example
all_info_query = db.session.query(Var).subquery()
# Build the initial query:
## 1. Query all variants first -> db.session.query(Var.variant_id)
## 2. Then pass to the builder the user-selected coords -> session["dt_coords"]
## 3. Build the query
filter_info_query_in = initial_query_builder(
db.session.query(Var.variant_id), session["dt_coords"], Var)
#Do the same but as a subquery (o can be used in SELECT * FROM this_subquery)
filter_info_subquery_in = initial_query_builder(
db.session.query(Var.variant_id), session["dt_coords"],
Var).subquery()
# Query the table with the genotypes (Alleles model)
## select_from(Alleles): which is the basic table
##
## filter(Alleles.variant_id.in_(filter_info_subquery_in): Filter the super
## long Alleles table to only include variants which are also in the above-queried info table.
##
## Alleles.sample.in_(lsams): Filter the alleles table to only include rows with samples that the user has selected.
##
## func.sum(Alleles.gt)>0: Only include samples which have at least one variant in the given coordinates
geno_query = db.session.query().select_from(Alleles).filter(
Alleles.variant_id.in_(filter_info_subquery_in),
Alleles.sample.in_(lsams)).group_by(
Alleles.variant_id).having(func.sum(Alleles.gt) > 0)
nondynamics_state = False
#If user has performed filter on the var_Table:
if session['fpane_clean_dict'] != '':
fpd = session['fpane_clean_dict']
nondynamics_state = (len([
y for y in ['dynamic_ac', 'dynamic_af', 'dynamic_an']
for x in list(fpd.keys()) if y not in x
]) > 0)
# True if AC,AN.AF should be calculated on the fly for the selected samples, FALSE is not. It's always TRUE
# Filter the initial query according to the user-defined filters:
filter_info_query_in_filtered = fpane_query_builder_var(
filter_info_query_in, session['fpane_clean_dict'], lsams, Var,
field_types)
filter_info_subquery_in = filter_info_query_in_filtered.subquery()
#Re-do the geno_query as above but now filter to have same variants with the filter_info_query_in filtered
geno_query = db.session.query().select_from(Alleles).filter(
Alleles.variant_id.in_(filter_info_subquery_in),
Alleles.sample.in_(lsams)).group_by(
Alleles.variant_id).having(func.sum(Alleles.gt) > 0)
#Filter sample columns (hom,het etc..) and dynamic_af,dynamic_an,dynamic_ac columns:
geno_query_filtered = fpane_query_builder_having(
geno_query, session['fpane_clean_dict'], lsams, Alleles,
field_types)
geno_query = geno_query_filtered
#Join varquery and geno query
query = geno_query.join(
all_info_query,
Alleles.variant_id == all_info_query.columns["variant_id"])
# Here we control which columns of the above-built queries will be returned in the final table.
#'# if queried samples < cohort_cutoff, PQuery will return all info columns and the individual genotypes per sample.
if len(lsams) <= cohort_cutoff:
cols = column_dt_builder(info_cols_ordered, lsams, Alleles,
all_info_query, "samples")
## If queried samples > cohort_cutoff, then PQuery will run in cohort mode without showing individual genotypes.
elif len(lsams) > cohort_cutoff:
cols = column_dt_builder(info_cols_ordered, lsams, Alleles,
all_info_query)
# Here we specify the number of columns returned and building the final object which will be returned
# on the var_table page:
if session["dt_coords"] != "all" or nondynamics_state == False:
# HERE IS A BUG:
# We just count the total rows before the filtering as otherwise it
# would be super slow:
rowcount = filter_info_query_in.count()
rowTable = DataTables(params, query, cols, rowcount)
return jsonify(rowTable.output_result())
# If the user selects to return whole exome we rebuild the queries to deal with this huge task:
else:
print("2exome")
geno_query = db.session.query(Alleles).filter(
Alleles.sample.in_(lsams)).subquery()
infoquery = db.session.query().select_from(Var).group_by(
geno_query.columns.variant_id).having(
func.sum(geno_query.columns.gt) > 0)
query = infoquery.join(geno_query,
geno_query.columns.variant_id == Var.variant_id)
if len(lsams) <= cohort_cutoff:
cols = column_dt_builder_all(info_cols_ordered,
lsams,
geno_query,
Var,
mode="samples")
elif len(lsams) > cohort_cutoff:
cols = column_dt_builder_all(info_cols_ordered, lsams, geno_query,
Var)
rowcount_query = db.session.query(Alleles.variant_id).filter(
Alleles.sample.in_(lsams)).group_by(
Alleles.variant_id).having(func.sum(Alleles.gt) > 0)
rowcount = rowcount_query.count()
rowTable = DataTables(params, query, cols, rowcount)
return jsonify(rowTable.output_result())
#PREVIOUS CHUNKS OF CODE, MIGHT BE USEFUL
# try:
# if session['fpane_clean_dict']!='':
# print(session['fpane_clean_dict'])
# fpd = session['fpane_clean_dict']
# dynamics_state = len([y for y in ['dynamic_ac', 'dynamic_af', 'dynamic_an'] for x in list(fpd.keys()) if y in x])>0
# nondynamics_state = len([y for y in ['dynamic_ac', 'dynamic_af', 'dynamic_an'] for x in list(fpd.keys()) if y not in x])>0
#
# def final_query_builder(db, Var, Alleles, coords, filt, insams, fpd, params, dynamics_state, nondynamics_state):
#
# general_genoquery = db.session.query(A)
# genoquery_pre = db.session.query(Alleles.variant_id, func.sum(Alleles.gt).label('dynamic_AC'),
# (func.count(Alleles.gt) * 2).label('dynamic_AN'),
# (func.sum(Alleles.gt) / (func.count(Alleles.gt) * 2)).label('dynamic_AF')).filter(
# Alleles.sample.in_(lsams)).group_by(
# Alleles.variant_id)
# if dynamics_state == False & nondynamics_state == False:
# dt_columns = dt_columns_builder()
# varquery = db.session.query().select_from(Var)
# genoquery_final = genoquery_pre.limit(params.get('length')).offset(params.get('start')).subquery()
# varquery_final = varquery
# varquery_final2 = initial_query_builder(varquery_final, session["dt_coords"], session["dt_filt"], Var)
# varquery_final3 = fpane_query_builder_var(varquery_final2, fpd, lsams, Var, field_types)
# final_query = varquery_final3.join(genoquery_final, genoquery_final.columns.variant_id == Var.variant_id)
# row_count = varquery_final3.add_columns(*[c.sqla_expr for c in [ColumnDT(Var.variant_id)]]).count()
# elif dynamics_state == False & nondynamics_state == True:
# dt_columns = dt_columns_builder()
# genoquery_final = genoquery_pre.subquery()
# varquery_sq1 = db.session.query(Var)
# varquery_sq1 = initial_query_builder(varquery_sq1, session["dt_coords"], session["dt_filt"], Var)
# varquery_sq1 = fpane_query_builder_var(varquery_sq1, fpd, lsams, Var, field_types)
# varquery_sq1 = varquery_sq1.limit(params.get('length')).offset(params.get('start')).subquery()
# varquery_sq2 = db.session.query().select_from(varquery_sq1).add_columns(*[c.sqla_expr for c in dt_columns])
#
# main_geno = db.session.query(Alleles)
#
# main_geno = db.session.query(mc.variant_id, func.sum(mc.gt).label('AC'),
# (func.count(mc.gt)*2).label('AN'),
# (func.sum(mc.gt)/(func.count(mc.gt)*2)).label('AF'),
# func.group_concat(case([(mc.sample == "S2201", mc.gt+":"+mc.dp+":"+mc.gq+":"+mc.pgt+":"+mc.pid+":"+mc.sample)])).label('S2201'),
# func.group_concat(case([(mc.sample == "S2202", mc.gt+":"+mc.dp+":"+mc.gq+":"+mc.pgt+":"+mc.pid+":"+mc.sample)])).label('S2202'),
# func.group_concat(case([(mc.sample == "S2203", mc.gt+":"+mc.dp+":"+mc.gq+":"+mc.pgt+":"+mc.pid+":"+mc.sample)])).label('S2203')).group_by(main_sub.columns.variant_id)
#
# final_query = varquery_sq2.join
#
#
#
# if dynamics_state == True:
# if nondynamics_state == True:
# print('case1')
# genoquery_fpane_filt_pre = fpane_query_builder_geno(genoquery_pre, fpd, lsams, Alleles, field_types)
# row_count = genoquery_fpane_filt_pre.count()
# genoquery_final = genoquery_fpane_filt_pre.subquery()
# varquery_final = varquery
# final_query = varquery_final.join(genoquery_final, genoquery_final.columns.variant_id == Var.variant_id)
# final_query_infilt = initial_query_builder(final_query, session["dt_coords"], session["dt_filt"], Var)
# final_query_infilt_fpanefilt = fpane_query_builder_var(final_query_infilt, fpd, lsams, Var, field_types).limit(params.get('length')).offset(params.get('start'))
# elif nondynamics_state == False:
# print('case2')
# genoquery_fpane_filt_pre = fpane_query_builder_geno(genoquery_pre, fpd, lsams, Alleles, field_types)
# row_count = genoquery_fpane_filt_pre.count()
# genoquery_final = genoquery_fpane_filt_pre.limit(params.get('length')).offset(params.get('start')).subquery()
# varquery_final = varquery
# final_query = varquery_final.join(genoquery_final,genoquery_final.columns.variant_id == Var.variant_id)
# final_query_infilt = initial_query_builder(final_query, session["dt_coords"], session["dt_filt"],Var)
# final_query_infilt_fpanefilt = final_query_infilt
# session['fpane_clean_dict'] = ''
# else:
# print('case3')
# varquery_final = varquery
# varquery_final2 = initial_query_builder(varquery_final, session["dt_coords"], session["dt_filt"], Var)
# varquery_final3 = fpane_query_builder_var(varquery_final2, fpd, lsams, Var, field_types)
# varquery_sq = varquery_final3.add_columns(*[c.sqla_expr for c in [ColumnDT(Var.variant_id)]]).subquery()
# genoquery_fpane_filt_pre = genoquery_pre
# genoquery_final = genoquery_fpane_filt_pre.join(varquery_sq, = ).limit(params.get('length')).offset(params.get('start')).subquery()
# final_query = varquery_final3.join(genoquery_final, genoquery_final.columns.variant_id == Var.variant_id)
# final_query_infilt_fpanefilt = fpane_query_builder_var(final_query, fpd, lsams, Var, field_types)
# row_count = varquery_final3.add_columns(*[c.sqla_expr for c in [ColumnDT(Var.variant_id)]]).count()
# session['fpane_clean_dict'] = ''
# else:
# print('case4')
# #print("fpane_query_else")
# #print(len(session['fpane_clean_dict']))
# #print(session['fpane_clean_dict'])
# dynamics_state = False
# genoquery_final = genoquery_pre.limit(params.get('length')).offset(params.get('start')).subquery()
# varquery_final = varquery
# final_query = varquery_final.join(genoquery_final, genoquery_final.columns.variant_id == Var.variant_id)
# final_query_infilt = initial_query_builder(final_query, session["dt_coords"], session["dt_filt"], Var)
# final_query_infilt_fpanefilt = final_query_infilt
# row_count = varquery_final.add_columns(*[c.sqla_expr for c in [ColumnDT(Var.variant_id)]]).count()
# session['fpane_clean_dict'] = ''
# except KeyError:
# print('case5')
# genoquery_final = genoquery_pre.limit(params.get('length')).offset(params.get('start')).subquery()
# varquery_final = varquery
# final_query = varquery_final.join(genoquery_final, genoquery_final.columns.variant_id == Var.variant_id)
# final_query_infilt = initial_query_builder(final_query, session["dt_coords"], session["dt_filt"], Var)
# final_query_infilt_fpanefilt = final_query_infilt
# row_count = final_query_infilt_fpanefilt.add_columns(*[c.sqla_expr for c in [ColumnDT(Var.variant_id)]]).count()
# dt_columns = []
# for x in info_cols_ordered+lsams:
# if x in lsams: dt_columns.append(ColumnDT(Var.__table__.c[x]))
# else:
# if field_types[x]['db_status'] == 'in_db': dt_columns.append(ColumnDT(Var.__table__.c[x]))
# elif field_types[x]['db_status'] == 'out_db':
# if '_ac' in x: dt_columns.append(ColumnDT(func.sum(Alleles.gt)))
# elif '_an' in x: dt_columns.append(ColumnDT(func.count(Alleles.gt)*2))
# elif '_af' in x: dt_columns.append(ColumnDT(func.sum(Alleles.gt)/(func.count(Alleles.gt)*2)))
# #print(fquery)
# rowTable = DataTables(params, final_query_infilt_fpanefilt, dt_columns, row_count=row_count)
# #print(rowTable)
# return jsonify(rowTable.output_result())
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('request_date', filter=_DTstrftime))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('approval_level'))
query = DBSession.query(ProductRequest)
rowTable = DataTables(req, ProductRequest, 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('request_date', filter=_DTstrftime))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('approval_level'))
query = DBSession.query(ProductRequest).filter(
ProductRequest.unit_id == Unit.id,
or_(
ProductRequest.nama.ilike('%%%s%%' % cari),
ProductRequest.kode.ilike('%%%s%%' % cari),
Unit.nama.ilike('%%%s%%' % cari),
))
rowTable = DataTables(req, ProductRequest, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'hon_request_approval':
term = 'term' in params and params['term'] or ''
unit = 'unit_id' in params and params['unit_id'] or ''
rows = DBSession.query(ProductRequest.id, ProductRequest.kode,
ProductRequest.nama).filter(
ProductRequest.nama.ilike('%%%s%%' % term),
ProductRequest.unit_id == unit,
ProductRequest.approval_level == 0,
ProductRequest.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'] == 'hok_request_approval':
term = 'term' in params and params['term'] or ''
unit = 'unit_id' in params and params['unit_id'] or ''
rows = DBSession.query(ProductRequest.id, ProductRequest.kode,
ProductRequest.nama).filter(
ProductRequest.kode.ilike('%%%s%%' % term),
ProductRequest.unit_id == unit,
ProductRequest.approval_level == 0,
ProductRequest.disabled == 0).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['kode'] = k[1]
d['nama'] = k[2]
r.append(d)
return r
elif url_dict['act'] == 'hon_request_deliver':
term = 'term' in params and params['term'] or ''
unit = 'unit_id' in params and params['unit_id'] or ''
rows = DBSession.query(ProductRequest.id, ProductRequest.kode,
ProductRequest.nama).filter(
ProductRequest.nama.ilike('%%%s%%' % term),
ProductRequest.unit_id == unit,
ProductRequest.approval_level == 2,
ProductRequest.disabled == 1,
ProductRequest.status_dlv == 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'] == 'hok_request_deliver':
term = 'term' in params and params['term'] or ''
unit = 'unit_id' in params and params['unit_id'] or ''
rows = DBSession.query(ProductRequest.id, ProductRequest.kode,
ProductRequest.nama).filter(
ProductRequest.kode.ilike('%%%s%%' % term),
ProductRequest.unit_id == unit,
ProductRequest.approval_level == 2,
ProductRequest.disabled == 1,
ProductRequest.status_dlv == 0).all()
r = []
for k in rows:
d = {}
d['id'] = k[0]
d['value'] = k[1]
d['kode'] = k[1]
d['nama'] = k[2]
r.append(d)
return r
elif url_dict['act'] == 'hon_deliver_item':
term = 'term' in params and params['term'] or ''
deliver = 'deliver' in params and params['deliver'] or ''
a = DBSession.query(ProductDeliver).filter(
ProductDeliver.id == deliver).first()
x = a.product_request_id
rows = DBSession.query(Product.id, Product.kode, Product.nama,
ProductRequestItem.qty).filter(
ProductRequestItem.product_request_id == x,
Product.id == ProductRequestItem.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_accept_item':
term = 'term' in params and params['term'] or ''
accept = 'accept' in params and params['accept'] or ''
a = DBSession.query(ProductDeliver).filter(
ProductAccept.id == accept,
ProductDeliver.id == ProductAccept.product_deliver_id).first()
x = a.product_request_id
rows = DBSession.query(Product.id, Product.kode, Product.nama,
ProductRequestItem.qty).filter(
ProductRequestItem.product_request_id == x,
Product.id == ProductRequestItem.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 view_act(request):
req = request
params = req.params
url_dict = req.matchdict
user = req.user
if url_dict['act']=='grid':
u = request.user.id
a = DBSession.query(UserGroup.group_id).filter(UserGroup.user_id==u).first()
b = '%s' % a
c = int(b)
x = DBSession.query(UserUnit.unit_id).filter(UserUnit.user_id==u).first()
if x=='None' or not x: #Untuk BUD
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('tgl_sts', filter=_DTstrftime))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('jumlah', filter=_DTnumberformat))
query = DBSession.query(ARSts).\
join(Unit)
rowTable = DataTables(req, ARSts, query, columns)
return rowTable.output_result()
y = '%s' % x
z = int(y)
if c == 2: #Untuk Bendahara
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('tgl_sts', filter=_DTstrftime))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('jumlah', filter=_DTnumberformat))
query = DBSession.query(ARSts
).join(Unit
).filter(ARSts.unit_id==x
)
rowTable = DataTables(req, ARSts, query, columns)
return rowTable.output_result()
else: #Untuk BUD
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('tgl_sts', filter=_DTstrftime))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('jumlah', filter=_DTnumberformat))
query = DBSession.query(ARSts).\
join(Unit)
rowTable = DataTables(req, ARSts, query, columns)
return rowTable.output_result()
