def handle_product(params, json_data): if 'product_id' not in params: return [] result = [] try: q_prod = Query("""SELECT prd_id, prd_naam, prd_type, prd_btw, prd_kantineprijs_leden, prd_kantineprijs_extern, prd_borrelmarge, prd_leverancier_id, prd_emballageprijs FROM tblproduct WHERE prd_verwijderd = 0 AND prd_id = %s ORDER BY prd_id""") q_rel = Query("""SELECT prdrel_orig_prd_id, prdrel_rel_prd_id, prdrel_aantal FROM tblproductrelation WHERE prdrel_orig_prd_id = %s ORDER BY prdrel_orig_prd_id""") except DatabaseError: raise InternalServerError for prd_id in params['product_id']: try: q_prod.run((prd_id,)) rows_prod = q_prod.rows() q_rel.run((prd_id,)) rows_rel = q_rel.rows() except DatabaseError: raise InternalServerError result += _convert_product_rows(rows_prod, rows_rel) return result
def handle_budget(params, json_data): if 'budget_id' not in params: return [] budget_data = [] try: q = Query( 'SELECT bdgt_id, bdgt_naam, bdgt_minimum, bdgt_current, bdgt_ver_id FROM tblbudget WHERE bdgt_id = %s' ) except DatabaseError: raise InternalServerError for budget_id in params['budget_id']: try: q.run((budget_id, )) cur_result = q.rows() except DatabaseError: raise InternalServerError for row in cur_result: if not hasPermission(params, 'budget', row[4]): continue budget_data.append({ 'id': row[0], 'vereniging_id': row[4], 'naam': row[1], 'current': row[3], 'minimum': row[2] }) return budget_data
def handle_login(params, json_data): if (not _check_flood_protect(params['ip'][0])): return (False, '') if 'username' not in params or 'password' not in params: return (False, '') try: q = Query( 'SELECT gebr_id, gebr_wachtwoord FROM tblgebruiker WHERE gebr_naam = %s' ) q.run((params['username'][0], )) results = q.rows() except DatabaseError: raise InternalServerError if (len(results) != 1): _add_flood_protect(params['ip'][0]) return (False, '') if not sha512_crypt.verify(params['password'][0], results[0][1]): _add_flood_protect(params['ip'][0]) return (False, '') #Generate session key session_key = _create_session(params['ip'][0], results[0][0]) return (True, session_key)
def handle_factuur(params, json_data): if 'factuur_id' not in params: return None try: q = Query("""SELECT fac_id, fac_cor_op_id, fac_type, fac_ver_id, fac_leverancier, fac_volgnummer, fac_factuurdatum, fac_leverdatum, fac_verantwoordelijke, fac_saldo_speciaal, fac_saldo_basis, fac_saldo_speciaal_na, fac_saldo_basis_na, frgl_type, prd_naam, frgl_omschrijving, frgl_aantal, frgl_stukprijs, frgl_totprijs, frgl_btw, vrd_prd_id FROM tblfactuur LEFT JOIN tblfactuurregel ON fac_id = frgl_fac_id LEFT JOIN tblvoorraad on frgl_vrd_id = vrd_id LEFT JOIN tblproduct on vrd_prd_id = prd_id WHERE fac_id = %s ORDER BY fac_id""") except DatabaseError: raise InternalServerError result = [] for fac_id in params['factuur_id']: try: q.run((fac_id,)) regels = q.rows() except DatabaseError: raise InternalServerError result.extend(convert_factuur_rows(params, regels)) return result
def handle_budget(params, json_data): if 'budget_id' not in params: return [] budget_data = [] try: q = Query('SELECT bdgt_id, bdgt_naam, bdgt_minimum, bdgt_current, bdgt_ver_id FROM tblbudget WHERE bdgt_id = %s') except DatabaseError: raise InternalServerError for budget_id in params['budget_id']: try: q.run((budget_id,)) cur_result = q.rows() except DatabaseError: raise InternalServerError for row in cur_result: if not hasPermission(params, 'budget', row[4]): continue budget_data.append({ 'id': row[0], 'vereniging_id': row[4], 'naam': row[1], 'current': row[3], 'minimum': row[2]}) return budget_data
def handle_factuur(params, json_data): if 'factuur_id' not in params: return None try: q = Query("""SELECT fac_id, fac_cor_op_id, fac_type, fac_ver_id, fac_leverancier, fac_volgnummer, fac_factuurdatum, fac_leverdatum, fac_verantwoordelijke, fac_saldo_speciaal, fac_saldo_basis, fac_saldo_speciaal_na, fac_saldo_basis_na, frgl_type, prd_naam, frgl_omschrijving, frgl_aantal, frgl_stukprijs, frgl_totprijs, frgl_btw, vrd_prd_id FROM tblfactuur LEFT JOIN tblfactuurregel ON fac_id = frgl_fac_id LEFT JOIN tblvoorraad on frgl_vrd_id = vrd_id LEFT JOIN tblproduct on vrd_prd_id = prd_id WHERE fac_id = %s ORDER BY fac_id""") except DatabaseError: raise InternalServerError result = [] for fac_id in params['factuur_id']: try: q.run((fac_id, )) regels = q.rows() except DatabaseError: raise InternalServerError result.extend(convert_factuur_rows(params, regels)) return result
def handle_barcodes(params, json_data): try: log.debug("Fetching barcode table.") q = Query("SELECT bar_ean, bar_prd_id FROM tblbarcode") q.run() log.debug("Fetching rows.") rows = q.rows() log.debug("Rows: %s", rows) return rows except DatabaseError: raise InternalServerError
def query_product(prd_id): log.debug("Query for product %s", prd_id) try: q_prod = Query("""SELECT prd_id, prd_naam, prd_type, prd_btw, prd_kantineprijs_leden, prd_kantineprijs_extern, prd_borrelmarge, prd_leverancier_id, prd_emballageprijs FROM tblproduct WHERE prd_verwijderd = 0 AND prd_id = %s""") q_prod.run((prd_id,)) rows_prod = q_prod.rows() q_rel = Query("""SELECT prdrel_orig_prd_id, prdrel_rel_prd_id prdrel_aantal FROM tblproductrelation WHERE prdrel_orig_prd_id = %s""") q_rel.run((prd_id,)) rows_rel = q_rel.rows() except DatabaseError: raise InternalServerError return _convert_product_rows(rows_prod, rows_rel)
def handle_product_all(params, json_data): try: q_prod = Query("""SELECT prd_id, prd_naam, prd_type, prd_btw, prd_kantineprijs_leden, prd_kantineprijs_extern, prd_borrelmarge, prd_leverancier_id, prd_emballageprijs FROM tblproduct WHERE prd_verwijderd = 0 ORDER BY prd_id""") q_prod.run() rows_prod = q_prod.rows() q_rel = Query("""SELECT prdrel_orig_prd_id, prdrel_rel_prd_id, prdrel_aantal FROM tblproductrelation ORDER BY prdrel_orig_prd_id""") q_rel.run() rows_rel = q_rel.rows() except DatabaseError: raise InternalServerError return _convert_product_rows(rows_prod, rows_rel)
def budget_vereniging_query(vereniging_id): try: q = Query('SELECT bdgt_id FROM tblbudget WHERE bdgt_ver_id = %s') q.run((vereniging_id, )) result = q.rows() except DatabaseError: raise InternalServerError budget_ids = [] for row in result: budget_ids.append(result[0][0]) return budget_ids
def _check_flood_protect(ip): try: q = Query('SELECT fc_ammount FROM tblfloodcontrol WHERE fc_ip = %s AND fc_timelim > NOW()') q.run((ip,)) rows = q.rows() if len(rows) == 0: return True elif rows[0][0] < config.flood_maxAmmount: return True qu = Query('UPDATE tblfloodcontrol set fc_timelim = NOW() + INTERVAL %s MINUTE') qu.run((ip,config.flood_timeout)) return False except DatabaseError: raise InternalServerError
def budget_vereniging_query(vereniging_id): try: q = Query('SELECT bdgt_id FROM tblbudget WHERE bdgt_ver_id = %s') q.run((vereniging_id,)) result = q.rows() except DatabaseError: raise InternalServerError budget_ids = [] for row in result: budget_ids.append(result[0][0]) return budget_ids
def _add_flood_protect(ip): try: qr = Query('DELETE FROM tblfloodcontrol WHERE fc_timelim < NOW()') qr.run() q = Query('SELECT fc_ip FROM tblfloodcontrol WHERE fc_ip = %s') q.run((ip,)) rows = q.rows() if (len(rows) != 0): qu = Query('UPDATE tblfloodcontrol SET fc_ammount = fc_ammount+1, fc_timelim = NOW() + INTERVAL %s MINUTE WHERE fc_ip = %s') qu.run((config.flood_timeout, ip)) else: qu = Query('INSERT into tblfloodcontrol (fc_ip, fc_ammount, fc_timelim) VALUES (%s, 1, NOW() + INTERVAL %s MINUTE)') qu.run((ip, config.flood_timeout)) except DatabaseError: raise InternalServerError
def handle_users(params, json_data): if not hasPermission(params, "user_list", None): return [] try: q = Query('SELECT gebr_id, gebr_naam FROM tblgebruiker') q.run() results = q.rows() except DatabaseError: raise InternalServerError resList = [] for row in results: resList.append({'id': row[0], 'naam': row[1]}) return resList
def handle_kantine(params, json_data): try: q = Query("""SELECT prd_id, prd_naam, prd_kantineprijs_leden, prd_kantineprijs_extern FROM tblproduct WHERE prd_verwijderd = 0 AND prd_type = %s""") q.run((policy.product_type_kantine,)) rows = q.rows() except DatabaseError: raise InternalServerError result = [] for product in rows: result.append({'id':product[0], 'naam':product[1], 'prijs_leden':product[2], 'prijs_extern':product[3]}) return result
def _check_flood_protect(ip): try: q = Query( 'SELECT fc_ammount FROM tblfloodcontrol WHERE fc_ip = %s AND fc_timelim > NOW()' ) q.run((ip, )) rows = q.rows() if len(rows) == 0: return True elif rows[0][0] < config.flood_maxAmmount: return True qu = Query( 'UPDATE tblfloodcontrol set fc_timelim = NOW() + INTERVAL %s MINUTE' ) qu.run((ip, config.flood_timeout)) return False except DatabaseError: raise InternalServerError
def _verify_session(ip, session_key): if (not _check_flood_protect(ip)): return False try: q = Query('SELECT ses_gebr_id FROM tblsession WHERE ses_id = %s AND ses_ip = %s AND ses_timelim > NOW()') q.run((session_key, ip)) rows = q.rows() qu = Query('UPDATE tblsession SET ses_timelim = NOW() + INTERVAL %s MINUTE WHERE ses_timelim > NOW() AND ses_id = %s AND ses_ip = %s') qu.run((config.timeout, session_key, ip)) except DatabaseError: raise InternalServerError if len(rows) == 0: _add_flood_protect(ip) return False return rows[0][0]
def _add_flood_protect(ip): try: qr = Query('DELETE FROM tblfloodcontrol WHERE fc_timelim < NOW()') qr.run() q = Query('SELECT fc_ip FROM tblfloodcontrol WHERE fc_ip = %s') q.run((ip, )) rows = q.rows() if (len(rows) != 0): qu = Query( 'UPDATE tblfloodcontrol SET fc_ammount = fc_ammount+1, fc_timelim = NOW() + INTERVAL %s MINUTE WHERE fc_ip = %s' ) qu.run((config.flood_timeout, ip)) else: qu = Query( 'INSERT into tblfloodcontrol (fc_ip, fc_ammount, fc_timelim) VALUES (%s, 1, NOW() + INTERVAL %s MINUTE)' ) qu.run((ip, config.flood_timeout)) except DatabaseError: raise InternalServerError
def budget_query(budget_id): try: q = Query("""SELECT bdgt_id, bdgt_current, bdgt_minimum, bdgt_ver_id, bdgt_naam FROM tblbudget WHERE bdgt_id = %s""") q.run((budget_id,)) rows = q.rows() except DatabaseError: raise InternalServerError result = [] for row in rows: result.append({ 'budget_id': row[0], 'vereniging_id': row[3], 'minimum': row[2], 'current': row[1], 'naam': row[4] }) return result
def _verify_session(ip, session_key): if (not _check_flood_protect(ip)): return False try: q = Query( 'SELECT ses_gebr_id FROM tblsession WHERE ses_id = %s AND ses_ip = %s AND ses_timelim > NOW()' ) q.run((session_key, ip)) rows = q.rows() qu = Query( 'UPDATE tblsession SET ses_timelim = NOW() + INTERVAL %s MINUTE WHERE ses_timelim > NOW() AND ses_id = %s AND ses_ip = %s' ) qu.run((config.timeout, session_key, ip)) except DatabaseError: raise InternalServerError if len(rows) == 0: _add_flood_protect(ip) return False return rows[0][0]
def query_vereniging(ver_id): try: q = Query("""SELECT ver_id, ver_naam, ver_email, ver_basis_budget_id FROM tblvereniging WHERE ver_id=%s""") q.run((ver_id,)) result = q.rows() except DatabaseError: raise InternalServerError ver_overview = [] for row in result: ver_overview.append({ 'id': row[0], 'naam': row[1], 'email': row[2], 'basis_budget': row[3] }) return ver_overview
def query_voorraad(prd_id): try: q = Query("""SELECT vrd_id, vrd_datum, vrd_resterend, vrd_stukprijs, vrd_btw FROM tblvoorraad WHERE vrd_resterend <> 0 AND vrd_prd_id = %s""") q.run((prd_id,)) rows = q.rows() except DatabaseError: raise InternalServerError result = [] for row in rows: result.append({ 'id': row[0], 'datum': row[1], 'resterend': row[2], 'stukprijs': row[3], 'btw': row[4] }) return policy.voorraad_order(result)
def query_vereniging(ver_id): try: q = Query("""SELECT ver_id, ver_naam, ver_email, ver_basis_budget_id FROM tblvereniging WHERE ver_id=%s""") q.run((ver_id, )) result = q.rows() except DatabaseError: raise InternalServerError ver_overview = [] for row in result: ver_overview.append({ 'id': row[0], 'naam': row[1], 'email': row[2], 'basis_budget': row[3] }) return ver_overview
def budget_query(budget_id): try: q = Query( """SELECT bdgt_id, bdgt_current, bdgt_minimum, bdgt_ver_id, bdgt_naam FROM tblbudget WHERE bdgt_id = %s""") q.run((budget_id, )) rows = q.rows() except DatabaseError: raise InternalServerError result = [] for row in rows: result.append({ 'budget_id': row[0], 'vereniging_id': row[3], 'minimum': row[2], 'current': row[1], 'naam': row[4] }) return result
def handle_verenigingen(params, json_data): try: q = Query("""SELECT ver_id, ver_naam, ver_email, ver_basis_budget_id FROM tblvereniging""") q.run() rows = q.rows() except DatabaseError: raise InternalServerError ver_overview = [] for row in rows: if not hasPermission(params, 'vereniging', row[0]): continue ver_overview.append({ 'id': row[0], 'naam': row[1], 'email': row[2], 'basis_budget': row[3] }) return ver_overview
def handle_verenigingen(params, json_data): try: q = Query("""SELECT ver_id, ver_naam, ver_email, ver_basis_budget_id FROM tblvereniging""") q.run(); rows = q.rows() except DatabaseError: raise InternalServerError ver_overview = [] for row in rows: if not hasPermission(params, 'vereniging', row[0]): continue ver_overview.append({ 'id': row[0], 'naam': row[1], 'email': row[2], 'basis_budget': row[3] }) return ver_overview
def handle_login(params, json_data): if (not _check_flood_protect(params['ip'][0])): return (False, '') if 'username' not in params or 'password' not in params: return (False,'') try: q = Query('SELECT gebr_id, gebr_wachtwoord FROM tblgebruiker WHERE gebr_naam = %s') q.run((params['username'][0],)) results = q.rows() except DatabaseError: raise InternalServerError if (len(results) != 1): _add_flood_protect(params['ip'][0]) return (False,'') if not sha512_crypt.verify(params['password'][0], results[0][1]): _add_flood_protect(params['ip'][0]) return (False,'') #Generate session key session_key = _create_session(params['ip'][0], results[0][0]) return (True, session_key)
def process_factuur(factuur): # Gen. nones for possible non-existent values leverancier = None if 'leverancier' in factuur: leverancier = factuur['leverancier'] vereniging = None saldo_basis = None if 'vereniging' in factuur: vereniging = factuur['vereniging'] saldo_basis=query_vereniging(vereniging)[0]['basis_budget'] saldo_speciaal = None if 'saldo_speciaal' in factuur: saldo_speciaal = factuur['saldo_speciaal'] verantwoordelijke = None if 'verantwoordelijke' in factuur: verantwoordelijke = factuur['verantwoordelijke'] # Generate next number for factuur try: q = Query("""SELECT MAX(fac_volgnummer) FROM tblfactuur, tblboekjaar WHERE fac_ver_id <=> %s AND fac_leverancier <=> %s AND fac_bkjr_id=bkjr_id AND bkjr_is_huidig = -1""") q.run((vereniging, leverancier)) rows = q.rows() except DatabaseError: raise InternalServerError if rows[0][0] is None: number = 1 else: number = rows[0][0] + 1 factuur['volgnummer'] = number # Create factuur entry try: q = Query("""INSERT INTO tblfactuur (fac_ver_id, fac_leverancier, fac_type, fac_volgnummer, fac_factuurdatum, fac_leverdatum, fac_verantwoordelijke, fac_saldo_speciaal, fac_saldo_basis, fac_bkjr_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, (SELECT bkjr_id FROM tblboekjaar WHERE bkjr_is_huidig = -1))""") q.run((vereniging, leverancier, factuur['type'], number, factuur['factuurdatum'], factuur['leverdatum'], verantwoordelijke, saldo_speciaal, saldo_basis)); fac_id = q.lastrowid() except DatabaseError: raise InternalServerError log.debug('factuur created with id: %d', fac_id) # Pocess lines (first inkoop then verkoop) factuur_bedrag = 0 regels_processed = [] for regel in factuur['regels']: if regel['aantal'] < 0: factuur_bedrag += process_factuur_regel(regel, fac_id, regels_processed) for regel in factuur['regels']: if regel['aantal'] >= 0: factuur_bedrag += process_factuur_regel(regel, fac_id, regels_processed) factuur['regels'] = regels_processed # Finish up if saldo_speciaal is not None: budget_speciaal = budget_query(saldo_speciaal)[0] mutatie_speciaal = max(factuur_bedrag, budget_speciaal['minimum'] - budget_speciaal['current']) mutatie_basis = factuur_bedrag - mutatie_speciaal budget_update(saldo_speciaal, mutatie_speciaal) budget_update(saldo_basis, mutatie_basis) budget_speciaal = budget_query(saldo_speciaal)[0] budget_basis = budget_query(saldo_basis)[0] factuur['saldo_basis_na'] = budget_basis['current'] factuur['saldo_speciaal_na'] = budget_speciaal['current'] try: q = Query("""UPDATE tblfactuur SET saldo_speciaal_na = %s saldo_basis_na = %s WHERE fac_id = %s""") q.run((budget_speciaal['current'], budget_basis['current'], fac_id)) except DatabaseError: raise InternalServerError elif saldo_basis is not None: budget_update(saldo_basis, factuur_bedrag) budget_basis = budget_query(saldo_basis)[0] factuur['saldo_basis_na'] = budget_basis['current'] try: q = Query("""UPDATE tblfactuur SET fac_saldo_basis_na = %s WHERE fac_id = %s""") q.run((budget_basis['current'], fac_id)) except DatabaseError: raise InternalServerError return fac_id
def process_factuur(factuur): # Gen. nones for possible non-existent values leverancier = None if 'leverancier' in factuur: leverancier = factuur['leverancier'] vereniging = None saldo_basis = None if 'vereniging' in factuur: vereniging = factuur['vereniging'] saldo_basis = query_vereniging(vereniging)[0]['basis_budget'] saldo_speciaal = None if 'saldo_speciaal' in factuur: saldo_speciaal = factuur['saldo_speciaal'] verantwoordelijke = None if 'verantwoordelijke' in factuur: verantwoordelijke = factuur['verantwoordelijke'] # Generate next number for factuur try: q = Query("""SELECT MAX(fac_volgnummer) FROM tblfactuur, tblboekjaar WHERE fac_ver_id <=> %s AND fac_leverancier <=> %s AND fac_bkjr_id=bkjr_id AND bkjr_is_huidig = -1""") q.run((vereniging, leverancier)) rows = q.rows() except DatabaseError: raise InternalServerError if rows[0][0] is None: number = 1 else: number = rows[0][0] + 1 factuur['volgnummer'] = number # Create factuur entry try: q = Query("""INSERT INTO tblfactuur (fac_ver_id, fac_leverancier, fac_type, fac_volgnummer, fac_factuurdatum, fac_leverdatum, fac_verantwoordelijke, fac_saldo_speciaal, fac_saldo_basis, fac_bkjr_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, (SELECT bkjr_id FROM tblboekjaar WHERE bkjr_is_huidig = -1))""") q.run((vereniging, leverancier, factuur['type'], number, factuur['factuurdatum'], factuur['leverdatum'], verantwoordelijke, saldo_speciaal, saldo_basis)) fac_id = q.lastrowid() except DatabaseError: raise InternalServerError log.debug('factuur created with id: %d', fac_id) # Pocess lines (first inkoop then verkoop) factuur_bedrag = 0 regels_processed = [] for regel in factuur['regels']: if regel['aantal'] < 0: factuur_bedrag += process_factuur_regel(regel, fac_id, regels_processed) for regel in factuur['regels']: if regel['aantal'] >= 0: factuur_bedrag += process_factuur_regel(regel, fac_id, regels_processed) factuur['regels'] = regels_processed # Finish up if saldo_speciaal is not None: budget_speciaal = budget_query(saldo_speciaal)[0] mutatie_speciaal = max( factuur_bedrag, budget_speciaal['minimum'] - budget_speciaal['current']) mutatie_basis = factuur_bedrag - mutatie_speciaal budget_update(saldo_speciaal, mutatie_speciaal) budget_update(saldo_basis, mutatie_basis) budget_speciaal = budget_query(saldo_speciaal)[0] budget_basis = budget_query(saldo_basis)[0] factuur['saldo_basis_na'] = budget_basis['current'] factuur['saldo_speciaal_na'] = budget_speciaal['current'] try: q = Query("""UPDATE tblfactuur SET saldo_speciaal_na = %s saldo_basis_na = %s WHERE fac_id = %s""") q.run( (budget_speciaal['current'], budget_basis['current'], fac_id)) except DatabaseError: raise InternalServerError elif saldo_basis is not None: budget_update(saldo_basis, factuur_bedrag) budget_basis = budget_query(saldo_basis)[0] factuur['saldo_basis_na'] = budget_basis['current'] try: q = Query("""UPDATE tblfactuur SET fac_saldo_basis_na = %s WHERE fac_id = %s""") q.run((budget_basis['current'], fac_id)) except DatabaseError: raise InternalServerError return fac_id