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