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):
ses = request.session
req = request
params = req.params
url_dict = req.matchdict
if url_dict['act'] == 'grid':
pk_id = 'id' in params and params['id'] and int(params['id']) or 0
if url_dict['act'] == 'grid':
# defining columns
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('unit_kd'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('rekening_kd'))
columns.append(ColumnDT('rekening_nm'))
columns.append(ColumnDT('tgl_trans', filter=_DTstrftime))
columns.append(ColumnDT('pokok', filter=_DTnumber_format))
columns.append(ColumnDT('denda', filter=_DTnumber_format))
columns.append(ColumnDT('bunga', filter=_DTnumber_format))
columns.append(ColumnDT('posted'))
query = DBSession.query(
ARPaymentPbb.id,
ARPaymentPbb.unit_kd,
ARPaymentPbb.kode,
ARPaymentPbb.nama,
ARPaymentPbb.rekening_kd,
ARPaymentPbb.rekening_nm,
ARPaymentPbb.pokok,
ARPaymentPbb.denda,
ARPaymentPbb.bunga,
ARPaymentPbb.posted,
ARPaymentPbb.tgl_trans,
ARPaymentPbb.tgl_validasi,
).filter(
ARPaymentPbb.tahun == ses['tahun'],
between(ARPaymentPbb.tgl_trans, ses['tanggal'],
ses['tanggal_to']),
ARPaymentPbb.posted == ses['posted'])
#).order_by(ARPaymentPbb.kode.asc()
#)
rowTable = DataTables(req, ARPaymentPbb, query, columns)
return rowTable.output_result()
def test_with_filter_ok(self):
"""Return column with a specific filter."""
col = ColumnDT('id', filter=lambda x: 'HANDLE %s' % x)
assert col.filter(col.column_name) == 'HANDLE id'
def 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()
def vuln_list_json_route():
"""list vulns, data endpoint"""
columns = [
ColumnDT(literal_column('1'),
mData='_select',
search_method='none',
global_search=False),
ColumnDT(Vuln.id, mData='id'),
ColumnDT(Host.id, mData='host_id'),
ColumnDT(Host.address, mData='host_address'),
ColumnDT(Host.hostname, mData='host_hostname'),
ColumnDT(Service.proto, mData='service_proto'),
ColumnDT(Service.port, mData='service_port'),
ColumnDT(func.concat_ws('/', Service.port, Service.proto),
mData='service'),
ColumnDT(Vuln.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 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 view_act(request):
req = request
params = req.params
url_dict = req.matchdict
user = req.user
if url_dict['act'] == 'grid':
awal = 'awal' in request.GET and request.GET['awal'] or datetime.now(
).strftime('%Y-%m-%d')
akhir = 'akhir' in request.GET and request.GET[
'akhir'] or datetime.now().strftime('%Y-%m-%d')
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('wp_nama'))
columns.append(ColumnDT('op_nama'))
columns.append(ColumnDT('rek_nama'))
columns.append(ColumnDT('tgl_terima', filter=_DTstrftime))
columns.append(ColumnDT('terutang', filter=_DTnumberformat))
columns.append(ColumnDT('denda', filter=_DTnumberformat))
columns.append(ColumnDT('bunga', filter=_DTnumberformat))
columns.append(ColumnDT('jumlah', filter=_DTnumberformat))
columns.append(ColumnDT('status_invoice'))
query = DBSession.query(ARTbp)\
.filter(ARTbp.tgl_terima.between(awal,akhir))
#TODO Filter BY SKPD
rowTable = DataTables(req.GET, ARTbp, query, columns)
return rowTable.output_result()
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 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 plan_act(request):
ses = request.session
req = request
params = req.params
url_dict = req.matchdict
if url_dict['act']=='grid':
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('receipt_date', filter=_DTstrftime))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('vendors.nama'))
query = DBSession.query(ProductReceipt)
rowTable = DataTables(req, ProductReceipt, query, columns)
return rowTable.output_result()
elif url_dict['act']=='grid1':
cari = 'cari' in params and params['cari'] or ''
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('kode'))
columns.append(ColumnDT('nama'))
columns.append(ColumnDT('receipt_date', filter=_DTstrftime))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('vendors.nama'))
query = DBSession.query(ProductReceipt
).filter(ProductReceipt.vendor_id==Vendor.id,
ProductReceipt.unit_id==Unit.id,
or_(Vendor.nama.ilike('%%%s%%' % cari),
Unit.nama.ilike('%%%s%%' % cari),))
rowTable = DataTables(req, ProductReceipt, query, columns)
return rowTable.output_result()
elif url_dict['act']=='hon_warehouse_receipt':
term = 'term' in params and params['term'] or ''
unit = 'unit_id' in params and params['unit_id'] or ''
rows = DBSession.query(ProductReceipt.id, ProductReceipt.kode, ProductReceipt.nama
).filter(ProductReceipt.nama.ilike('%%%s%%' % term),
ProductReceipt.unit_id==unit).all()
r = []
for k in rows:
d={}
d['id'] = k[0]
d['value'] = k[2]
d['kode'] = k[1]
d['nama'] = k[2]
r.append(d)
return r
def 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 plan_act(request):
ses = request.session
req = request
params = req.params
url_dict = req.matchdict
if url_dict['act'] == 'grid':
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('product_plans.kode'))
columns.append(ColumnDT('product_plans.nama'))
columns.append(ColumnDT('approval_date', filter=_DTstrftime))
columns.append(ColumnDT('approval_level'))
columns.append(ColumnDT('notes'))
query = DBSession.query(ProductPlanAppr)
rowTable = DataTables(req, ProductPlanAppr, query, columns)
return rowTable.output_result()
elif url_dict['act'] == 'grid1':
cari = 'cari' in params and params['cari'] or ''
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('un'))
columns.append(ColumnDT('pk'))
columns.append(ColumnDT('pn'))
columns.append(ColumnDT('approval_date', filter=_DTstrftime))
columns.append(ColumnDT('approval_level'))
columns.append(ColumnDT('notes'))
query = DBSession.query(
ProductPlanAppr.id,
Unit.nama.label('un'),
ProductPlan.kode.label('pk'),
ProductPlan.nama.label('pn'),
ProductPlanAppr.approval_date,
ProductPlanAppr.approval_level,
ProductPlanAppr.notes,
).filter(
ProductPlanAppr.product_plan_id == ProductPlan.id,
ProductPlanAppr.unit_id == Unit.id,
or_(
ProductPlan.nama.ilike('%%%s%%' % cari),
ProductPlan.kode.ilike('%%%s%%' % cari),
Unit.nama.ilike('%%%s%%' % cari),
))
rowTable = DataTables(req, ProductPlanAppr, query, columns)
return rowTable.output_result()
def 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 get_dt_columns(self):
return [ColumnDT(m) for m in self.get_columns()]
def test_with_yadcf_data_param():
"""Return Column with yadcf_data filter as false."""
col = ColumnDT(User.name, yadcf_data=False)
if col.yadcf_data != False:
raise AssertionError()
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 test_init_with_default_params():
"""Return column with given default params."""
col = ColumnDT(User.id)
if not (col.nulls_order is None or col.search_method == "string_contains"):
raise AssertionError()
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'))
query = DBSession.query(User.id, User.user_name, User.email, User.status,
User.last_login_date, User.registered_date).\
join(UserGroup).filter(UserGroup.group_id==gid)
rowTable = DataTables(req, User, query, columns)
return rowTable.output_result()
def 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 usr_unit_act(request):
ses = request.session
req = request
params = req.params
url_dict = req.matchdict
if url_dict['act']=='grid':
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('email'))
columns.append(ColumnDT('user_name'))
columns.append(ColumnDT('status'))
columns.append(ColumnDT('last_login_date'))
columns.append(ColumnDT('registered_date'))
columns.append(ColumnDT('nama'))
query = DBSession.query(User.id, User.user_name, User.email, User.status,
User.last_login_date, User.registered_date,
Unit.nama
).outerjoin(UserUnit
).outerjoin(Unit
).filter(UserUnit.user_id!='1',UserUnit.user_id!='2',)
rowTable = DataTables(req, User, query, columns)
return rowTable.output_result()
elif url_dict['act']=='grid1':
cari = 'cari' in params and params['cari'] or ''
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('email'))
columns.append(ColumnDT('user_name'))
columns.append(ColumnDT('status'))
columns.append(ColumnDT('last_login_date'))
columns.append(ColumnDT('registered_date'))
columns.append(ColumnDT('nama'))
query = DBSession.query(User.id, User.user_name, User.email, User.status,
User.last_login_date, User.registered_date,
Unit.nama
).outerjoin(UserUnit
).outerjoin(Unit
).filter(UserUnit.user_id!='1',
UserUnit.user_id!='2',
UserUnit.user_id==User.id,
UserUnit.unit_id==Unit.id,
or_(Unit.nama.ilike('%%%s%%' % cari),
User.user_name.ilike('%%%s%%' % cari),
User.email.ilike('%%%s%%' % cari),))
rowTable = DataTables(req, User, query, columns)
return rowTable.output_result()
def note_list_json_route():
"""list notes, data endpoint"""
columns = [
ColumnDT(Note.id, mData='id'),
ColumnDT(Host.id, mData='host_id'),
ColumnDT(Host.address, mData='host_address'),
ColumnDT(Host.hostname, mData='host_hostname'),
# break pylint duplicate-code
ColumnDT(Service.proto, mData='service_proto'),
ColumnDT(Service.port, mData='service_port'),
ColumnDT(func.concat_ws('/', Service.port, Service.proto),
mData='service'),
ColumnDT(Note.xtype, mData='xtype'),
ColumnDT(Note.data, mData='data'),
ColumnDT(Note.tags, mData='tags'),
ColumnDT(Note.comment, mData='comment'),
ColumnDT(literal_column('1'),
mData='_buttons',
search_method='none',
global_search=False)
]
query = db.session.query().select_from(Note).outerjoin(
Host, Note.host_id == Host.id).outerjoin(Service,
Note.service_id == Service.id)
if 'filter' in request.values:
query = apply_filters(query,
filter_parser.parse(
request.values.get('filter')),
do_auto_join=False)
notes = DataTables(request.values.to_dict(), query,
columns).output_result()
return jsonify(notes)
def test_with_filter_ok():
"""Return column with a specific filter."""
col = ColumnDT(User.name, search_method="like")
if col.search_method != "like":
raise AssertionError()
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 test_with_valid_nulls_order():
"""Return column with a specific filter."""
col = ColumnDT(User.name, nulls_order="nullslast")
if col.nulls_order != "nullslast":
raise AssertionError()
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_with_invalid_nulls_order():
"""Return column with a specific filter."""
with pytest.raises(ValueError):
ColumnDT(User.name, nulls_order="invalid")
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 test_with_invalid_search_method():
"""Return column with a specific filter."""
with pytest.raises(ValueError):
ColumnDT(User.name, search_method="invalid")
def test_with_default_params_ok(self):
"""Return column with given default params."""
col = ColumnDT('id')
assert col.column_name == 'id'
assert col.filter(col.column_name) == 'id'
def 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())
