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 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_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 = "`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 = "`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 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 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 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 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, 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 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 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 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 = "`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 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
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 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_total_pengeluaran(self, body):
field = "SUM(`jumlah`) AS total_pengeluaran"
table = "`tr_pengeluaran`"
sql_filter = "`tanggal`>='%s-01' AND `tanggal`<='%s-31'" % (
body["tahunbulan"], body["tahunbulan"])
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, file_id):
field = "`emp_number`,`eattach_id`,`eattach_desc`, `eattach_filename`, `eattach_size`, `eattach_attachment`, `eattach_type`,`screen`,`attached_by`,`attached_by_name`, DATE_FORMAT(`attached_time`, '%Y-%m-%dT%T') AS `attached_time`"
table = "`hs_hr_emp_attachment`"
sql_filter = "`emp_number`='%s' AND `screen`='%s' AND `eattach_id` = '%s'" % (
self.emp_number, self.screen, file_id)
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 = "`kdrw`, `kdrt`, `norumah`, `nokk`, `nmkk`, `statustinggal`, `pengurus`"
table = "`tbl_warga`"
sql_filter = "`kdrw` = '%s' AND `kdrt` = '%s' AND `norumah` = '%s'" % (
body["kdrw"], body["kdrt"], body["norumah"])
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 = "`tahun`, `kdrw`, `kdrt`, `norumah`, `jan`, `feb`, `mar`, `apr`, `may`, `jun`, `jul`, `aug`, `sep`, `oct`, `nop`, `des`"
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 = "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(self, body):
field = "`ed_name` = '%s', `ed_relationship_type` = '%s', `ed_gender` = '%s', `ed_date_of_birth` = '%s'" % (
body["name"], body["relationship"], body["gender"],
body["date_of_birth"])
table = "`hs_hr_emp_dependents`"
sql_filter = "`emp_number`='%s' AND `ed_seqno` = '%s'" % (
self.emp_number, body["dependent_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 put(self, body):
field = "`eec_name` = '%s', `eec_relationship` = '%s', `eec_home_no` = '%s', `eec_mobile_no` = '%s', `eec_office_no` = '%s', `eec_address` = '%s'" % (
body["name"], body["relationship"], body["home_telephone"],
body["mobile"], body["work_telephone"], body["address"])
table = "`hs_hr_emp_emergency_contacts`"
sql_filter = "`emp_number`='%s' AND `eec_seqno` = '%s'" % (
self.emp_number, body["emergencycontact_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
