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 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 on_chat_message(self, msg):
"""
Process received messages.
msg -- The received message
"""
if 'text' not in msg:
# probably a sticker or something
return
if self.callback and self.sender_id != msg['from']['id']:
# once in callback mode, ignore everyone else
return
text = msg['text']
#self.chat_id = msg['chat']['id']
self.sender_id = msg['from']['id']
self.username = get_value_from(msg['from'], ['username', 'first_name', 'id'], 'unknown-user-id')
self.is_group = msg['chat']['type'] == 'group'
self.groupname = msg['chat']['title'] if 'title' in msg['chat'] else None
#log('chat id {} from {}: {}'.format(self.chat_id, self.sender_id, str(msg)))
if self.callback:
log_msg(msg)
callback = self.callback
self.callback = None
callback.kwargs[callback.argname] = text
self.dbc = db.open_connection()
callback.callback(self, **callback.kwargs)
db.close_connection(self.dbc)
return
# avoid logging and processing every single message.
if text[0] != '/':
return
# get command, ignore bot username
self.args=text.split()
command = self.args[0]
if '@' in command:
more_split = command.split('@', maxsplit=1)
command = more_split[0]
# skip '/'
self.command = command[1:]
log_msg(msg)
#is_admin = False
#if sender_id in config.admins:
# is_admin = True
if self.command in commands.all_commands:
self.dbc = db.open_connection()
commands.all_commands[self.command](self)
db.close_connection(self.dbc)
def get_description_information(self):
print str(strftime("%H:%M:%S", gmtime())) + ": Checking Description!"
conn = open_connection()
user_handle_list = get_twitter_urls(conn)
error_sites = []
try:
for user_handle in user_handle_list:
#print "Checking " + user_handle
user = self.api.get_user(user_handle)
twitter_general_obj = {}
twitter_general_obj["join_date"] = user.created_at
twitter_general_obj["description"] = user.description
twitter_general_obj["handle"] = user.screen_name
twitter_general_obj["name"] = user.name
twitter_general_obj["tweetcount"] = user.statuses_count
twitter_general_obj["followercount"] = user.followers_count
twitter_general_obj["location"] = user.location
twitter_general_obj["desc_link"] = user.url
twitter_general_obj["time_lookup"] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
insert_in_handle_info(conn,twitter_general_obj)
Statistics.inc_twitter_descriptions()
except StandardError as e:
Statistics.add_twitter_error_sites(user_handle + "\t" + str(e))
#error_sites.append(user_handle)
except tweepy.TweepError as e:
#message = str(e.message[0]['code']) + "\n" + str(e.args[0][0]['code'])
Statistics.add_twitter_error_sites(user_handle + "\t" + str(e.message))
conn.close()
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(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 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 = "`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 = "`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 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 = "`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 = "`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 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 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_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 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 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 test_01_first(self):
"""
Establishes a connection against the DB (test version) and
empties it for the upcoming tests. Make sure seresponseer is up by,
for instance, running
docker run -p 3306:3306 --name db -e MYSQL_ROOT_PASSWORD=1234567 \
-d enyamada/steps-db:1.0
"""
steps.config = steps.read_config("tests-config.yml")
steps.empty_db(db.open_connection(steps.config["db"]))
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, 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 get(self, body):
field = "`kdsaldo`, `tahun`, `bulan`, `masuk`, `keluar`, `saldoakhir`"
table = "`saldokas`"
sql_filter = "`kdsaldo` = '%s'" % body["kdsaldo"]
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 logout_user(user_id):
try:
connection = db.open_connection()
cursor = connection.cursor()
sql_query = "DELETE FROM USER_SESSION WHERE USER_ID = '{0}'".format(user_id)
cursor.execute(sql_query)
connection.commit()
return True
except mysql.connector.Error as error:
print("Failed in logout_user {}".format(error))
return False
def get(self, body):
field = "`kdpemasukan`, `tanggal`, `norumah`, `kdrw`, `kdrt`, `nokk`, `jumlah`, `keterangan`, `dokumen_bayar`, `terverifikasi`"
table = "`tr_pemasukan`"
sql_filter = "`kdpemasukan` = '%s'" % body["kdpemasukan"]
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 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 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 get(self, body):
field = "`kdpengeluaran`, `tanggal`, `jumlah`, `keterangan`"
table = "`tr_pengeluaran`"
sql_filter = "`kdpengeluaran` = '%s'" % body["kdpengeluaran"]
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(self):
field = "`emp_number`, `user_name`, `user_password`"
table = "`ohrm_user`"
sql_filter = "`user_name` = '%s'" % self.username
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 post(self, body):
field = "(`jumlah`, `keterangan`)"
values = "('%s', '%s')" % (body["jumlah"], body["keterangan"])
table = "`tr_pengeluaran`"
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 = cursor.rowcount
return result
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()
if __name__ == '__main__':
STATUS_SCHEDULED = "scheduled"
STATUS_RE_SCHEDULED = "re-scheduled"
STATUS_DONE = "done"
CONFIG_FILE = "scheduler.yaml"
config = read_config(CONFIG_FILE)
logging.debug("Config read: %s", config)
setup_logging(config["log"])
db_conn = db.open_connection(config["db"])
spot_sg_id = aws.create_spot_security_group(config["aws"]["sg-name"])
# pylint: disable=no-value-for-parameter
scheduler = BackgroundScheduler()
scheduler.add_job(check_jobs, 'interval',
seconds=config["app"]["polling-interval"])
scheduler.start()
app.run(host='0.0.0.0', port=80)