def on_callback_query(self, msg):
query_id, from_id, query_data = telepot.glance(msg, flavor='callback_query')
#self.bot.sendMessage(from_id, str(msg))
#log('callback chat id {} from {}: {}'.format(self.chat_id, from_id, str(msg)))
if self.callback and self.sender_id != from_id:
# once in callback mode, ignore everyone else
return
if self.message is None:
# probably bot restarted
self.message = (self.chat_id, msg['message']['message_id'])
log_callback(msg, self.callback)
if self.callback:
callback = self.callback
self.callback = None
callback.kwargs[callback.argname] = query_data
self.dbc = db.open_connection()
callback.callback(self, **callback.kwargs)
db.close_connection(self.dbc)
return
if query_data in commands.all_commands:
self.command = query_data
self.dbc = db.open_connection()
commands.all_commands[query_data](self)
db.close_connection(self.dbc)
return
#bot.answerCallbackQuery(query_id) #, text='Got it')
# Destroy message if we can't understand what it is. Probably an old message.
self.burn_message(self.message)
def login_user(request):
returnValue = None
statusCode = None
setCookie = False
# open connection
connection = db.get_connection()
cursor = db.get_cursor(connection)
try:
# process request
jsonUser = request.get_json(force=True)
user = db.try_login_user(cursor, jsonUser["username-input-login"],
jsonUser["password-input-login"])
if user:
tokens.set_user_session(user)
setCookie = True
returnValue = jsonify(user)
statusCode = 200
else:
returnValue = jsonify({'error': 'UNKNOWN_CREDENTIALS'})
statusCode = 202
except Exception as e:
print('Caught this error: ' + repr(e))
returnValue = jsonify({'error': str(e)})
statusCode = 500
finally:
# close connection
db.close_cursor(cursor)
db.close_connection(connection)
response = make_response(returnValue, statusCode)
if setCookie:
tokens.set_user_cookie(response)
return response
def post(self, body):
emp_number = self.emp_number
eattach_id = str(int(time()))
eattach_desc = body['comment']
eattach_filename = body['file_name']
eattach_attachment = b64decode(body["select_file"]).hex()
eattach_size = int((len(body["select_file"]) * 3 / 4) -
body["select_file"].count("=", -2))
eattach_size = str(eattach_size)
eattach_type = guess_type(eattach_filename)[0]
screen = self.screen
attached_by = self.emp_number
field = "(`emp_number`,`eattach_id`,`eattach_desc`, `eattach_filename`, `eattach_size`, `eattach_attachment`, `eattach_type`,`screen`,`attached_by`)"
values = "('%s', '%s', '%s', '%s', '%s', x'%s', '%s', '%s', '%s')" % (
emp_number, eattach_id, eattach_desc, eattach_filename,
eattach_size, eattach_attachment, eattach_type, screen,
attached_by)
table = "`hs_hr_emp_attachment`"
statement = "INSERT INTO %s %s VALUES %s" % (table, field, values)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
connection.commit()
db.close_connection(connection, cursor)
result = {
"file_id": eattach_id,
"comment": eattach_desc,
"file_name": eattach_filename,
"size": eattach_size,
"type": eattach_type,
"date_added": self.get_meta(eattach_id)[9]
}
return result
def get_summary(mobile_number):
connection = db.open_connection()
cursor = connection.cursor()
sql_select_query = "SELECT USER_ID FROM USER_INFO where MOBILE_NUMBER = {0}".format(mobile_number)
cursor.execute(sql_select_query)
result_set = cursor.fetchall()
db.close_connection()
if len(result_set) > 0:
user_id = [i[0] for i in result_set][0]
connection = db.open_connection()
cursor = connection.cursor()
sql_select_query = "SELECT * FROM ANSWER_HISTORY LEFT JOIN FIXED_QUESTIONS ON ANSWER_HISTORY.QUESTION_ID = \
FIXED_QUESTIONS.QUESTION_ID WHERE TRACKER_ID = {0}".format(user_id)
cursor.execute(sql_select_query)
result_set = cursor.fetchall()
details = []
for row in result_set:
temp = {}
temp['ANSWER'] = row[4]
temp['QUESTION'] = row[7]
temp['DAY'] = row[2]
details.append(temp)
return details
else:
return "Number not found"
def get(self):
field = "A.`emp_firstname`, A.`emp_middle_name`, A.`emp_lastname`, A.`employee_id`, A.`emp_other_id`, A.`emp_dri_lice_num`, A.`emp_dri_lice_exp_date`, A.`emp_bpjs_no`, A.`emp_npwp_no`, A.`emp_bpjs_ket_no`, D.`id` AS `work_shift_id`, A.`emp_gender`, A.`emp_marital_status`, E.`id` AS `nation_code`, A.`emp_birthday`, B.`id` AS `emp_religion`, A.`emp_birth_place`"
table = "(((`hs_hr_employee` AS A JOIN `ohrm_religion` AS B ON A.`emp_religion`=B.`id`) JOIN `ohrm_employee_work_shift` AS C ON A.`emp_number`=C.`emp_number`) JOIN `ohrm_work_shift` AS D ON C.`work_shift_id`=D.`id`) JOIN `ohrm_nationality` AS E ON A.`nation_code`=E.`id`"
sql_filter = "A.`emp_number` LIKE %s" % self.emp_number
statement = "SELECT %s FROM %s WHERE %s LIMIT 0,1" % (field, table,
sql_filter)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
result = cursor.fetchone()
db.close_connection(connection, cursor)
if isinstance(result[6], datetime.datetime):
license_expiry_date = result[6].isoformat()
else:
license_expiry_date = ""
result = {
"first_name": result[0],
"middle_name": result[1],
"last_name": result[2],
"employee_id": result[3],
"no_ktp": result[4],
"drivers_license_number": result[5],
# "license_expiry_date": result[6].isoformat(),
"license_expiry_date": license_expiry_date,
"no_bpjs_kesehatan": result[7],
"no_npwp": result[8],
"no_bpjs_ketenagakerjaan": result[9],
"work_shift": str(result[10]),
"gender": str(result[11]),
"marital_status": result[12],
"nationality": str(result[13]),
"date_of_birth": result[14].isoformat(),
"religion": str(result[15]),
"place_of_birth": result[16]
}
return result
def run():
db.init()
try:
logging.info('\nPress Ctrl+C to stop')
ros = get_ros()
logging.info('\nRead sensors every %s seconds...' %
REPEAT_DELAY_SECONDS)
while True:
start = time.time()
sys.stdout.write('\r\033[K')
for pin_num, (sensor_type, ro) in enumerate(zip(SENSOR_TYPES,
ros)):
val = adc.read(pin_num)
dt = datetime.now(timezone.utc).astimezone()
sys.stdout.write('%s=%g ' % (sensor_type, val))
sys.stdout.flush()
temp, hum = openweather.get_temperature_and_rel_humidity()
db.store_measurement(dt, sensor_type, val, ro, temp, hum)
upload_recorded()
sleep = REPEAT_DELAY_SECONDS + start - time.time()
if sleep > 0:
time.sleep(sleep)
except KeyboardInterrupt:
logging.info('Stopped by user')
db.close_connection()
def get_brewery():
b = True
if 'brewery' in request.args:
bry = '.*' + request.args['brewery'] + '.*'
elif 'sid' in request.args:
bry = request.args['sid']
b = False
else:
return "No brewery provided. You fool."
conn = db.establish_connection()
cur = db.create_cursor(conn)
out = ""
try:
if b:
cur.execute('SELECT * FROM Breweries WHERE name ~* %s', (bry, ))
else:
cur.execute('SELECT * FROM Breweries WHERE StateID=%s', (bry, ))
for brew_data in cur:
out += "<h1>Here's a brewery</h1><p>" + str(
brew_data) + "</p><a href='/beer?id=" + str(
brew_data[4]) + "'>BEERS</a>"
# cur.execute('SELECT * FROM Beers WHERE BreweryId=%s',(brew_data[4],))
# for item in cur:
# out+="<p>"+str(item)+"</p>"
except Exception as e:
out = "<h1>Here's an error</h1><p>" + str(e) + "</p>"
finally:
# conn.commit()
db.close_connection(conn, cur)
return out
def run():
db.init()
try:
ros = get_ros()
delay = DEFAULT_SECONDS_BETWEEN_READINGS
while True:
start = time.time()
sys.stdout.write('\r\033[K')
for pin_num, (sensor_type, ro) in enumerate(zip(SENSOR_TYPES,
ros)):
val = adc.read(pin_num)
dt = datetime.now(timezone.utc).astimezone()
logging.info('%s=%g ' % (sensor_type, val))
temp, hum = openweather.get_temperature_and_rel_humidity()
db.store_reading(dt, sensor_type, val, ro, temp, hum)
delay = upload_recorded()
try:
delay = float(delay)
except (TypeError, ValueError) as e:
delay = DEFAULT_SECONDS_BETWEEN_READINGS
logging.error('Cannot parse delay: %s' % e)
logging.info('Wait %s seconds...' % delay)
sleep = delay - time.time() + start
if sleep > 0:
time.sleep(sleep)
except KeyboardInterrupt:
db.close_connection()
def main_menu():
done = False
while not done:
print('Battleship Main Menu')
print('----------------------------------------')
print('(C) Display class list')
print('(S) Display ship list')
print('(AC) Add a new class')
print('(AS) Add a new ship')
print('(DC) Delete a class')
print('(DS) Delete a ship')
print('(X) Exit')
choice = input('Please make your selection: ').upper()
if choice == 'C':
list_classes()
elif choice == 'S':
list_ships()
elif choice == 'AC':
add_new_class()
elif choice == 'AS':
add_new_ship()
elif choice == 'DC':
delete_class()
elif choice == 'DS':
delete_ship()
elif choice == 'X':
db.close_connection()
done = True
else:
print('¯\_(ツ)_/¯')
def login_user(mobile_number):
connection = db.open_connection()
cursor = connection.cursor()
sql_select_query = "SELECT * FROM USER_INFO WHERE MOBILE_NUMBER = '{0}'".format(mobile_number)
cursor.execute(sql_select_query)
result_set = cursor.fetchall()
db.close_connection()
return len(result_set)
def get_all(self):
field = "`kdsaldo`, `tahun`,`bulan`, `masuk`, `keluar`, `saldoakhir`"
table = "`saldokas`"
statement = "SELECT %s FROM %s LIMIT 0,1000" % (field, table)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
result = cursor.fetchall()
db.close_connection(connection, cursor)
return result
def api_get_beer():
name = None
abbr = None
abv = None
brew = None
style = None
results = 20
vals = []
if 'name' in request.args:
name = ".*" + request.args['name'] + ".*"
vals.append(name)
if 'brew' in request.args:
brew = ".*" + request.args['brew'] + ".*"
vals.append(brew)
if 'style' in request.args:
style = ".*" + request.args['style'] + ".*"
vals.append(style)
if 'abv' in request.args:
abv = request.args['abv']
vals.append(abv)
if 'abbr' in request.args:
abbr = request.args['abbr']
vals.append(abbr)
if 'results' in request.args:
try:
results = int(request.args['abbr'])
except Exception as e:
return str(e)
if len(vals) == 0:
return jsonify(
'Usage: supply a beername, brewery, state abbreviation, style, or abv'
)
conn = db.establish_connection()
cur = db.create_cursor(conn)
vals.append(results)
try:
qbase = 'SELECT * FROM Beers WHERE TRUE'
abbrq = '' if abbr == None else ' AND stateid=(SELECT stateid FROM States WHERE abbr=%s)'
brewq = '' if brew == None else ' AND breweryid= ANY (SELECT breweryid FROM Breweries WHERE name ~* %s)'
beerq = '' if name == None else ' AND name ~* %s'
abvq = '' if abv == None else ' AND abv=%s'
styleq = '' if style == None else ' AND style ~* %s'
qend = ' ORDER BY ratings DESC LIMIT %s'
cur.execute(qbase + beerq + brewq + styleq + abvq + abbrq + qend, vals)
# if name != None:
# cur.execute(qbase+brewq+abbrq,vals)
# elif beer != None:
# cur.execute(qbase+beerq+abbrq,vals)
# else:
# cur.execute(qbase+'TRUE'+abbrq,vals)
results = cur.fetchall()
except Exception as e:
results = str(e)
finally:
db.close_connection(conn, cur)
return jsonify(results)
def delete(self, body):
table = "`saldokas`"
sql_filter = "`kdsaldo` = %s" % (body["kdsaldo"])
statement = "DELETE FROM %s WHERE %s" % (table, sql_filter)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
connection.commit()
db.close_connection(connection, cursor)
return cursor.rowcount
def get_all(self):
field = "`id`, `name`"
table = "`ohrm_religion`"
statement = "SELECT %s FROM %s LIMIT 0,1000" % (field, table)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
result = cursor.fetchall()
db.close_connection(connection, cursor)
return result
def get_all(self):
field = "`cou_code`, `cou_name`"
table = "`hs_hr_country`"
statement = "SELECT %s FROM %s LIMIT 0,1000" % (field, table)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
result = cursor.fetchall()
db.close_connection(connection, cursor)
return result
def get_all(self):
field = "`kdpemasukan`, `tanggal`, `norumah`, `kdrw`, `kdrt`, `nokk`, `jumlah`, `keterangan`, `dokumen_bayar`, `terverifikasi`"
table = "`tr_pemasukan`"
statement = "SELECT %s FROM %s LIMIT 0,1000" % (field, table)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
result = cursor.fetchall()
db.close_connection(connection, cursor)
return result
def get_all(self):
field = "`kdpengeluaran`, `tanggal`, `jumlah`, `keterangan`"
table = "`tr_pengeluaran`"
statement = "SELECT %s FROM %s LIMIT 0,1000" % (field, table)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
result = cursor.fetchall()
db.close_connection(connection, cursor)
return result
def get_all(self):
field = "`kdrw`, `kdrt`, `norumah`, `nokk`, `nmkk`, `statustinggal`, `pengurus`"
table = "`tbl_warga`"
statement = "SELECT %s FROM %s LIMIT 0,1000" % (field, table)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
result = cursor.fetchall()
db.close_connection(connection, cursor)
return result
def get_school_info(school_id):
conn = db.connect(settings.DB_HOST, settings.DB_USER,
settings.DB_PASSWD, settings.DB_NAME)
# school uf
school_uf = db.get_school_uf(conn, school_id)
if school_uf is not None:
uf_score_avg = db.get_uf_score_avg(conn, school_uf)
school_city = db.get_school_city(conn, school_id)
if school_city is not None:
city_score_avg = db.get_city_score_avg(conn, school_city)
# num students
total_students = db.get_total_lines(conn, school_id)
total_absents = db.get_total_absents(conn, school_id)
# avg
avg_score1, avg_score2, avg_score3, avg_score4, avg_score5, avg_total = db.get_score_avg(conn, school_id)
# sex
score_avg_sex = db.get_score_avg_sex(conn, school_id)
# score interval
score_count = [
db.get_count_per_score_interval(conn, 0, 0, school_id),
db.get_count_per_score_interval(conn, 1, 99, school_id),
db.get_count_per_score_interval(conn, 100, 199, school_id),
db.get_count_per_score_interval(conn, 200, 299, school_id),
db.get_count_per_score_interval(conn, 300, 399, school_id),
db.get_count_per_score_interval(conn, 400, 499, school_id),
db.get_count_per_score_interval(conn, 500, 599, school_id),
db.get_count_per_score_interval(conn, 600, 699, school_id),
db.get_count_per_score_interval(conn, 700, 799, school_id),
db.get_count_per_score_interval(conn, 800, 899, school_id),
db.get_count_per_score_interval(conn, 900, 999, school_id),
db.get_count_per_score_interval(conn, 1000, 1000, school_id)
]
db.close_connection(conn)
score_interval_data = [
['notas', 'alunos', { 'role': 'annotation' }],
["0", int(score_count[0]), int(score_count[0])],
["1-99", int(score_count[1]), int(score_count[1])],
["100-199", int(score_count[2]), int(score_count[2])],
["200-299", int(score_count[3]), int(score_count[3])],
["300-399", int(score_count[4]), int(score_count[4])],
["400-499", int(score_count[5]), int(score_count[5])],
["500-599", int(score_count[6]), int(score_count[6])],
["600-699", int(score_count[7]), int(score_count[7])],
["700-799", int(score_count[8]), int(score_count[8])],
["800-899", int(score_count[9]), int(score_count[9])],
["900-999", int(score_count[10]), int(score_count[10])],
["1000", int(score_count[11]), int(score_count[11])]
]
return render_template('school_info.html', **locals())
def get_all(self):
field = "`tahun`, `kdrw`, `kdrt`, `norumah`, `jan`, `feb`, `mar`, `apr`, `may`, `jun`, `jul`, `aug`, `sep`, `oct`, `nop`, `des`"
table = "`tr_iuran`"
statement = "SELECT %s FROM %s LIMIT 0,1000" % (field, table)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
result = cursor.fetchall()
db.close_connection(connection, cursor)
return result
def delete(self, file_id):
table = "`hs_hr_emp_attachment`"
sql_filter = "`emp_number` = %s AND `eattach_id` = %s" % (
self.emp_number, file_id)
statement = "DELETE FROM %s WHERE %s" % (table, sql_filter)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
connection.commit()
db.close_connection(connection, cursor)
return cursor.rowcount
def put(self, body):
field = "`terverifikasi`='%s'" % (body['terverifikasi'])
table = "`tr_pemasukan`"
sql_filter = "`kdpemasukan`='%s'" % (body['kdpemasukan'])
statement = "UPDATE %s SET %s WHERE %s" % (table, field, sql_filter)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
connection.commit()
db.close_connection(connection, cursor)
return cursor.rowcount
def delete(self, dependent_id):
table = "`hs_hr_emp_dependents`"
sql_filter = "`emp_number`='%s' AND `ed_seqno` = '%s'" % (
self.emp_number, dependent_id)
statement = "DELETE FROM %s WHERE %s" % (table, sql_filter)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
connection.commit()
db.close_connection(connection, cursor)
return cursor.rowcount
def delete(self, body):
table = "`tbl_warga`"
sql_filter = "`kdrw` = '%s' AND `kdrt` = '%s' AND `norumah` = '%s'" % (
body['kdrw'], body['kdrt'], body['norumah'])
statement = "DELETE FROM %s WHERE %s" % (table, sql_filter)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
connection.commit()
db.close_connection(connection, cursor)
return cursor.rowcount
def delete(self, body):
table = "`tr_iuran`"
sql_filter = "`tahun` = '%s' AND `kdrw` = '%s' AND `kdrt` = '%s' AND `norumah` = '%s'" % (
body["tahun"], body["kdrw"], body["kdrt"], body["norumah"])
statement = "DELETE FROM %s WHERE %s" % (table, sql_filter)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
connection.commit()
db.close_connection(connection, cursor)
return cursor.rowcount
def get_all(self):
field = "`emp_number`,`eec_seqno`,`eec_name`,`eec_relationship`,`eec_home_no`,`eec_mobile_no`,`eec_office_no`,`eec_address`"
table = "`hs_hr_emp_emergency_contacts`"
sql_filter = "`emp_number`='%s'" % (self.emp_number)
statement = "SELECT %s FROM %s WHERE %s LIMIT 0,1000" % (field, table,
sql_filter)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
result = cursor.fetchall()
db.close_connection(connection, cursor)
return result
def get(self):
field = "`emp_number`,`emp_street1`,`emp_street2`,`city_code`,`provin_code`,`emp_zipcode`,`coun_code`,`emp_hm_telephone`,`emp_mobile`,`emp_work_telephone`,`emp_work_email`,`emp_oth_email`"
table = "`hs_hr_employee`"
sql_filter = "`emp_number` = %s" % self.emp_number
statement = "SELECT %s FROM %s WHERE %s LIMIT 0,1" % (field, table,
sql_filter)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
result = cursor.fetchone()
db.close_connection(connection, cursor)
return result
def put_comment(self, body):
field = "`eattach_desc` = '%s'" % body['comment']
table = "`hs_hr_emp_attachment`"
sql_filter = "`emp_number`='%s' AND `screen`='%s' AND `eattach_id` = '%s'" % (
self.emp_number, self.screen, body['file_id'])
statement = "UPDATE %s SET %s WHERE %s" % (table, field, sql_filter)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
connection.commit()
db.close_connection(connection, cursor)
return cursor.rowcount
def get_all(self):
field = "`emp_number`, `ed_seqno`, `ed_name`, `ed_relationship_type`, `ed_relationship`, `ed_date_of_birth`, `ed_gender`"
table = "`hs_hr_emp_dependents`"
sql_filter = "`emp_number`='%s'" % (self.emp_number)
statement = "SELECT %s FROM %s WHERE %s LIMIT 0,1000" % (field, table,
sql_filter)
connection = db.open_connection()
cursor = db.sql_cursor(connection, statement)
result = cursor.fetchall()
db.close_connection(connection, cursor)
return result
def _start_pennyworth_db_connection(request):
""" Starts a connection to the pennyworth db. """
if request.config.getoption('use_database') or request.config.getoption(
'use_auth'):
db.start_connection(request.config.getoption('stage'))
yield
db.close_connection()
# Since the yield above is in a conditional, it doesn't always execute, and when
# it doesn't, pytest raises a ValueError because it "did not yield a value"
else:
yield None
def main():
logging.info("Starting dataset evaluator...")
while True:
db.init_db_connection(config.PG_CONNECT)
pending_job = db.dataset_eval.get_next_pending_job()
if pending_job:
logging.info("Processing job %s..." % pending_job["id"])
evaluate_dataset(pending_job)
else:
logging.info("No pending datasets. Sleeping %s seconds." % SLEEP_DURATION)
db.close_connection()
time.sleep(SLEEP_DURATION)
import db
import re
import HTMLParser
working_file = open('working-file.csv', 'a+')
error_file = open('error_file.txt', 'a+')
h = HTMLParser.HTMLParser()
count = 0
db.open_connection()
for (text, id) in db.query('SELECT text, id FROM quotes2'):
try:
processed_text = re.sub(r'<.*> ', '', text)
processed_text = h.unescape(processed_text)
processed_text = processed_text.lower()
processed_text = re.sub(r'[^0-9a-zA-Z\s]', '', processed_text)
processed_text = processed_text.replace('\n', ' ')
processed_text = processed_text.replace('\r', ' ')
processed_text = re.sub(r'\s+', ' ', processed_text)
working_file.write(str(id) + ',' + processed_text + '\n')
except:
error_file.write('Error => id:' + str(id))
count += 1
error_file.write('Total: ' + str(count))
db.close_connection()
working_file.close()
error_file.close()
