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