def get(self, id=None, onlyActive=None):
print(id, onlyActive)
"""
Mengambil barang dari database
id = None; mengambil semua data
id != None; Mengambil barang tertentu
"""
if id == None:
# Mengambil semua data yang diarsipkan
if onlyActive == 'inactive':
return connExecute(
"SELECT * FROM `{}` WHERE `barang_status` = 0".format(
self.tableName))
# Mengambil semua data yang tidak diarsipkan
elif onlyActive == 'active':
return connExecute(
"SELECT * FROM `{}` WHERE `barang_status` = 1".format(
self.tableName))
# Mengambil semua data
return connExecute("SELECT * FROM `{}`".format(self.tableName))
# Mengambil data berdasarkan ID
return connExecute("SELECT * FROM `{}`".format(self.tableName))[0]
def delete(self, id=None):
"""
Menghapus data barang spesifik dari database
"""
connExecute(
"UPDATE `{}` SET `barang_status` = 0 WHERE `barang_id` = '{}'".
format(self.tableName, id))
return {'valid': True, 'msg': "Data successfully archived"}
def post(self, id):
data = request.get_json()
supplier_id = data[0]['value']
supplier_nama = data[1]['value']
supplier_alamat = data[2]['value']
supplier_provinsi = data[3]['value']
supplier_kota = data[4]['value']
supplier_telepon = data[5]['value']
sql = "INSERT INTO `master_supplier` SET "
sql += "`supplier_id` = '{}',".format(supplier_id)
sql += "`supplier_nama` = '{}',".format(supplier_nama.upper())
sql += "`supplier_alamat` = '{}',".format(supplier_alamat)
sql += "`supplier_provinsi` = '{}',".format(supplier_provinsi)
sql += "`supplier_kota` = '{}',".format(supplier_kota)
sql += "`supplier_telepon` = '{}'".format(supplier_telepon)
sql += "ON DUPLICATE KEY UPDATE "
sql += "`supplier_nama` = '{}',".format(supplier_nama.upper())
sql += "`supplier_alamat` = '{}',".format(supplier_alamat)
sql += "`supplier_provinsi` = '{}',".format(supplier_provinsi)
sql += "`supplier_kota` = '{}',".format(supplier_kota)
sql += "`supplier_telepon` = '{}'".format(supplier_telepon)
execs = connExecute(sql)
return execs
def get(self, id):
supplier_id = "SUP" + str(random.randint(1000000, 9999999))
supplier_nama = ""
supplier_alamat = ""
supplier_provinsi = ""
supplier_kota = ""
supplier_telepon = ""
if id != "baru":
sql = "SELECT * FROM `master_supplier` WHERE `supplier_id` = '{}'".format(
id)
json_data = connExecute(sql)
if len(json_data) > 0:
json_data = json_data[0]
supplier_id = json_data['supplier_id']
supplier_nama = json_data['supplier_nama'].title()
supplier_alamat = json_data['supplier_alamat']
supplier_provinsi = json_data['supplier_provinsi']
supplier_kota = json_data['supplier_kota']
supplier_telepon = json_data['supplier_telepon']
supplier = form(supplier_id)
supplier.add_text("Nama Supplier", "Masukan Nama Supplier",
supplier_nama)
supplier.add_text("Alamat Supplier", "Masukan Alamat Supplier",
supplier_alamat)
supplier.add_select("Provinsi Supplier", "Silahkan pilih provinsi",
opt.provinsi(), supplier_provinsi)
supplier.add_select("Kota Supplier", "Silahkan pilih kota", opt.kota(),
supplier_kota)
supplier.add_text("No. Telepon", "Masukan Nomor Telepon",
supplier_telepon)
return supplier.get()
def get(self, id):
pengguna_id = "USR" + str(random.randint(10000, 99999))
pengguna_nama = ""
pengguna_posisi = ""
pengguna_status = ""
if id != "baru":
sql = "SELECT * FROM `pengguna` WHERE `pengguna_id` = '{}'".format(
id)
json_data = connExecute(sql)
if len(json_data) > 0:
json_data = json_data[0]
pengguna_id = json_data['pengguna_id']
pengguna_nama = json_data['pengguna_nama'].upper()
pengguna_posisi = json_data['pengguna_posisi']
pengguna_status = json_data['pengguna_status']
pengguna = form(pengguna_id)
pengguna.add_text("Nama Pengguna", "Masukan Nama Pengguna",
pengguna_nama)
pengguna.add_select("Pilih Posisi Pengguna", "Posisi Pengguna",
opt.posisi(), pengguna_posisi)
pengguna.add_select("Pilih Status Pengguna", "Pilih Status Pengguna",
opt.status(), pengguna_status)
return pengguna.get()
def objectDataPelanggan():
json_data = {}
temporary_data = connExecute("SELECT * FROM `master_pelanggan`")
for data in temporary_data:
json_data[data['pelanggan_id']] = "{}".format(
data['pelanggan_nama'])
return json_data
def objectDataSupplier():
json_data = {}
temporary_data = connExecute(
"SELECT * FROM `master_supplier` WHERE `supplier_nama` != 'UMUM'")
json_data['SUPGENERAL'] = "UMUM"
for data in temporary_data:
json_data[data['supplier_id']] = "{}".format(data['supplier_nama'])
return json_data
def objectDataPengguna(staffType=None):
json_data = {}
sql = "SELECT * FROM `pengguna` "
if staffType:
sql += "WHERE `pengguna_posisi` = '{}' ".format(staffType)
temporary_data = connExecute(sql)
for data in temporary_data:
json_data[data['pengguna_id']] = "{}".format(data['pengguna_nama'])
return json_data
def get(self):
sql = "SELECT * FROM `merek`"
table_list = connExecute(sql)
list_data = []
for data in table_list:
table_data = tbl(data['merek_id'])
table_data.add_field_text(data['merek_nama'].title())
list_data.append(table_data.get())
return list_data
def get(self, list_data=[]):
list_data = []
for data in connExecute(
"SELECT * FROM `pengguna` as a INNER JOIN `posisi` as b ON a.pengguna_posisi = b.posisi_id"
):
table_data = tbl(data['pengguna_id'])
table_data.add_field_text(data['pengguna_nama'].title())
table_data.add_field_text(data['posisi_nama'])
table_data.add_field_text(data['pengguna_status'])
list_data.append(table_data.get())
return list_data
def getOrder(self, id):
json_data = {}
json_data['data_supplier'] = Options.objectDataSupplier()
json_data['data_pelanggan'] = Options.objectDataPelanggan()
json_data['data_pengguna'] = Options.objectDataPengguna(3001)
json_data['data_product'] = Options.objectDataBarang()
json_data['data_satuan'] = Options.objectDataSatuan()
# Get Order Data if exist
if id[0:3] == "INV":
sql = "SELECT * FROM `order` WHERE `pembelian_id` = '{}' ".format(
id)
order_data = connExecute(sql)[0]
sql = "SELECT * FROM `order_item` WHERE `pembelian_id` = '{}'".format(
id)
order_item = connExecute(sql)
order_data['pembelian_item'] = order_item
json_data['data_order'] = order_data
return json_data
def get(self):
sql = "SELECT * FROM `master_supplier`"
table_list = connExecute(sql)
list_data = []
for data in table_list:
table_data = tbl(data['supplier_id'])
table_data.add_field_text(data['supplier_nama'].title())
table_data.add_field_text(data['supplier_alamat'].title())
table_data.add_field_text(data['supplier_provinsi'] + "/" +
data['supplier_kota'])
table_data.add_field_text(data['supplier_telepon'])
list_data.append(table_data.get())
return list_data
def post(self, id):
data = request.get_json()
merek_id = data[0]['value']
merek_nama = data[1]['value']
sql = "INSERT INTO `merek` SET "
sql += "`merek_id` = '{}',".format(merek_id)
sql += "`merek_nama` = '{}'".format(merek_nama.upper())
sql += "ON DUPLICATE KEY UPDATE "
sql += "`merek_nama` = '{}'".format(merek_nama.upper())
execs = connExecute(sql)
return execs
def objectDataBarang():
json_data = {}
data_product_sql = ""
data_product_sql += "SELECT `barang_id`,`barang_nama`,`merek_nama`,`kategori_nama`,`barang_varian` FROM `barang` as a "
data_product_sql += "INNER JOIN `merek` as b "
data_product_sql += "ON `barang_merek` = `merek_id` "
data_product_sql += "INNER JOIN `kategori` as c "
data_product_sql += "ON `barang_kategori` = `kategori_id`"
temporary_data = connExecute(data_product_sql)
for data in temporary_data:
json_data[data['barang_id']] = "[{}] {} - {} {}".format(
data['kategori_nama'], data['merek_nama'], data['barang_nama'],
data['barang_varian'])
return json_data
def get(self, id):
satuan_id = "S" + str(random.randint(10000, 99999))
satuan_nama = ""
if id != "baru":
sql = "SELECT * FROM `satuan` WHERE `satuan_id` = '{}'".format(id)
json_data = connExecute(sql)
if len(json_data) > 0:
json_data = json_data[0]
satuan_id = json_data['satuan_id']
satuan_nama = json_data['satuan_nama'].title()
satuan = form(satuan_id)
satuan.add_text("Nama Satuan", "Masukan Nama Satuan", satuan_nama)
return satuan.get()
def get(self, id):
merek_id = "M" + str(random.randint(10000, 99999))
merek_nama = ""
if id != "baru":
sql = "SELECT * FROM `merek` WHERE `merek_id` = '{}'".format(id)
json_data = connExecute(sql)
if len(json_data) > 0:
json_data = json_data[0]
merek_id = json_data['merek_id']
merek_nama = json_data['merek_nama'].title()
merek = form(merek_id)
merek.add_text("Nama Merek", "Masukan Nama Merek", merek_nama)
return merek.get()
def get(self, id):
kategori_id = "K" + str(random.randint(100000, 999999))
kategori_nama = ""
if id != "baru":
sql = "SELECT * FROM `kategori` WHERE `kategori_id` = '{}'".format(
id)
json_data = connExecute(sql)
if len(json_data) > 0:
json_data = json_data[0]
kategori_id = json_data['kategori_id']
kategori_nama = json_data['kategori_nama'].title()
kategori = form(kategori_id)
kategori.add_text("Nama Kategori", "Masukan Nama Kategori",
kategori_nama)
return kategori.get()
def post(self, id):
data = request.get_json()
pengguna_id = data[0]['value']
pengguna_nama = data[1]['value']
pengguna_posisi = data[2]['value']
pengguna_status = data[3]['value']
sql = "INSERT INTO `pengguna` SET "
sql += "`pengguna_id` = '{}',".format(pengguna_id)
sql += "`pengguna_nama` = '{}',".format(pengguna_nama.upper())
sql += "`pengguna_posisi` = '{}',".format(pengguna_posisi)
sql += "`pengguna_status` = '{}' ".format(pengguna_status)
sql += "ON DUPLICATE KEY UPDATE "
sql += "`pengguna_nama` = '{}',".format(pengguna_nama.upper())
sql += "`pengguna_posisi` = '{}',".format(pengguna_posisi)
sql += "`pengguna_status` = '{}' ".format(pengguna_status)
execs = connExecute(sql)
return execs
def get(self):
json_data = {}
data_product_sql = ""
data_product_sql += "SELECT * FROM `order` as a "
data_product_sql += "INNER JOIN `pembelian_status` as b ON a.pembelian_status = b.pembelian_status_id "
data_product_sql += "INNER JOIN `master_supplier` as c ON a.pembelian_supplier_id = c.supplier_id"
json_data['pembelian_data'] = connExecute(data_product_sql)
json_data['pembelian_belum_dibayar'] = 0
json_data['pembelian_jatuh_tempo'] = 0
json_data['pembelian_selesai'] = 0
list_data = []
for pembelian in json_data['pembelian_data']:
table_data = tbl(pembelian['pembelian_id'])
table_data.add_field_text(pembelian['supplier_nama'].title())
table_data.add_field_text(
pembelian['pembelian_tanggal'] + ' / ' +
pembelian['pembelian_tanggal_jatuh_tempo'])
# Pembelian yang belum dibayar
if pembelian['pembelian_status'] == 'ST202':
table_data.add_field_badge_danger(
pembelian['pembelian_status_nama'].title())
json_data['pembelian_belum_dibayar'] += pembelian[
'pembelian_total']
# Pembelian yang selesai
elif pembelian['pembelian_status'] == 'ST200':
table_data.add_field_badge(
pembelian['pembelian_status_nama'].title())
json_data['pembelian_selesai'] += pembelian['pembelian_total']
table_data.add_field_price(pembelian['pembelian_total'])
list_data.append(table_data.get())
json_data['pembelian_data'] = list_data
return json_data
def getWithTableFormat(self):
sql = "SELECT * FROM `barang` as a "
sql += "INNER JOIN `kategori` as b "
sql += "ON a.barang_kategori = b.kategori_id "
sql += "INNER JOIN `merek` as c "
sql += "ON a.barang_merek = c.merek_id "
sql += "WHERE `barang_status` = 1"
table_list = connExecute(sql)
list_data = []
for data in table_list:
table_data = Table(data['barang_id'])
table_data.add_field_badge(data['kategori_nama'].title())
table_data.add_field_text(data['barang_nama'].title() + " - " +
data['barang_varian'])
table_data.add_field_text(data['merek_nama'].title())
table_data.add_field_price(data['barang_harga_jual'])
table_data.add_field_text(
str(data['barang_stok_toko']) + " / " +
str(data['barang_stok_gudang']))
list_data.append(table_data.get())
return list_data
def get_value(table_name):
return connExecute("SELECT * FROM " + table_name)
def post(self, id):
data = request.get_json()
pembelian_id = data['pembelian_id']
if pembelian_id == "":
pembelian_id = generateId("INV")
pembelian_supplier_id = data['pembelian_supplier_id']
pembelian_tanggal = data['pembelian_tanggal']
pembelian_tanggal_jatuh_tempo = data['pembelian_tanggal_jatuh_tempo']
pembelian_item = data['pembelian_item']
pembelian_faktur = int(0)
pembelian_pajak = int(0)
pembelian_diskon = int(data['pembelian_diskon'])
pembelian_total = int(data['pembelian_total'])
pembelian_status = data['pembelian_status']
sql = "INSERT INTO `order` SET "
sql += "`pembelian_id` = '{}',".format(pembelian_id)
sql += "`order_type` = '{}',".format(100)
sql += "`pembelian_supplier_id` = '{}',".format(
pembelian_supplier_id)
sql += "`pembelian_tanggal` = '{}',".format(
pembelian_tanggal)
sql += "`pembelian_tanggal_jatuh_tempo` = '{}',".format(
pembelian_tanggal_jatuh_tempo)
sql += "`pembelian_faktur` = '{}',".format(
pembelian_faktur)
sql += "`pembelian_pajak` = '{}',".format(
pembelian_pajak)
sql += "`pembelian_diskon` = '{}',".format(
pembelian_diskon)
sql += "`pembelian_total` = '{}',".format(
pembelian_total)
sql += "`pembelian_status` = '{}'".format(
pembelian_status)
sql += "ON DUPLICATE KEY UPDATE "
sql += "`order_type` = '{}',".format(100)
sql += "`pembelian_supplier_id` = '{}',".format(
pembelian_supplier_id)
sql += "`pembelian_tanggal` = '{}',".format(
pembelian_tanggal)
sql += "`pembelian_tanggal_jatuh_tempo` = '{}',".format(
pembelian_tanggal_jatuh_tempo)
sql += "`pembelian_faktur` = '{}',".format(
pembelian_faktur)
sql += "`pembelian_pajak` = '{}',".format(
pembelian_pajak)
sql += "`pembelian_diskon` = '{}',".format(
pembelian_diskon)
sql += "`pembelian_total` = '{}',".format(
pembelian_total)
sql += "`pembelian_status` = '{}'".format(
pembelian_status)
execs = connExecute(sql)
# Register the transaction
if pembelian_status == "ST200":
transactionId = generateId("TRC")
sql = "INSERT INTO `transaksi` "
sql += "(`transaksi_id`, `order_id`, `transaksi_jumlah`) "
sql += "VALUES ('{}', '{}', '{}'); ".format(
transactionId, pembelian_id, pembelian_total)
connExecute(sql)
# Delete all previous item
sql = "DELETE FROM `order_item` WHERE `pembelian_id` = '{}'".format(
pembelian_id)
connExecute(sql)
# Registering all item
for item in pembelian_item:
sql = "INSERT INTO `order_item` "
sql += "(`pembelian_id`, `barang_id`, `barang_satuan`, "
sql += "`barang_jumlah`, `barang_harga`, `barang_total`) "
sql += "VALUES ('{}', '{}', '{}', '{}', '{}', '{}')".format(
pembelian_id, item['barang_id'], item['barang_satuan'],
int(item['barang_jumlah']), int(item['barang_harga']),
int(item['barang_total']))
connExecute(sql)
# Update the barang_total to the database
if pembelian_status == "ST200":
sql = "UPDATE `barang` "
sql += "SET `barang_stok_toko` = `barang_stok_toko` + '{}' ".format(
item['barang_jumlah'])
sql += "WHERE `barang`.`barang_id` = '{}' ".format(
item['barang_id'])
connExecute(sql)
return execs
def getOrders(self, type):
json_data = {}
data_product_sql = ""
data_product_sql += "SELECT * FROM `order` as a "
data_product_sql += "INNER JOIN `pembelian_status` as b ON a.pembelian_status = b.pembelian_status_id "
if type == 100:
data_product_sql += "INNER JOIN `master_supplier` as c ON a.pembelian_supplier_id = c.supplier_id "
elif type == 200:
data_product_sql += "INNER JOIN `master_pelanggan` as c ON a.order_customer_id = c.pelanggan_id "
data_product_sql += "INNER JOIN `pengguna` as d ON a.order_sales_id = d.pengguna_id "
data_product_sql += "WHERE `order_type` = '{}' ".format(type)
json_data['pembelian_data'] = connExecute(data_product_sql)
json_data['pembelian_belum_dibayar'] = 0
json_data['pembelian_jatuh_tempo'] = 0
json_data['pembelian_selesai'] = 0
list_data = []
for pembelian in json_data['pembelian_data']:
table_data = tbl(pembelian['pembelian_id'])
if type == 100:
table_data.add_field_text(pembelian['supplier_nama'].title())
elif type == 200:
table_data.add_field_text(pembelian['pengguna_nama'].title())
table_data.add_field_text(pembelian['pelanggan_nama'].title())
table_data.add_field_text(
pembelian['pembelian_tanggal'] + ' / ' +
pembelian['pembelian_tanggal_jatuh_tempo'])
# Jatuh Tempo
if pembelian['pembelian_tanggal_jatuh_tempo'] == str(
date.today()) and pembelian['pembelian_status'] != 'ST200':
table_data.add_field_badge_warning("JATUH TEMPO".title())
json_data['pembelian_jatuh_tempo'] += pembelian[
'pembelian_total']
# Pembelian yang belum dibayar
elif pembelian['pembelian_status'] == 'ST202':
table_data.add_field_badge_danger(
pembelian['pembelian_status_nama'].title())
json_data['pembelian_belum_dibayar'] += pembelian[
'pembelian_total']
# Pembelian yang selesai
elif pembelian['pembelian_status'] == 'ST200':
table_data.add_field_badge(
pembelian['pembelian_status_nama'].title())
json_data['pembelian_selesai'] += pembelian['pembelian_total']
table_data.add_field_price(pembelian['pembelian_total'])
list_data.append(table_data.get())
json_data['pembelian_data'] = list_data
return json_data
def createOrder(self, data):
pembelian_id = data['pembelian_id']
if pembelian_id == "":
pembelian_id = generateId("INV")
order_type = data['order_type']
order_sales_id = data['order_sales_id']
order_customer_id = data['order_customer_id']
pembelian_supplier_id = data['pembelian_supplier_id']
pembelian_tanggal = data['pembelian_tanggal']
pembelian_tanggal_jatuh_tempo = data['pembelian_tanggal_jatuh_tempo']
pembelian_item = data['pembelian_item']
pembelian_faktur = int(0)
pembelian_pajak = int(0)
pembelian_diskon = int(data['pembelian_diskon'])
pembelian_total = int(data['pembelian_total'])
pembelian_status = data['pembelian_status']
# Switch between [PEMBELIAN] and [PENJUALAN]
operator = '+' # Default operator for product processing is addition
if order_type == 200:
operator = '-'
sql = "INSERT INTO `order` SET "
sql += "`pembelian_id` = '{}',".format(pembelian_id)
sql += "`order_type` = '{}',".format(order_type)
sql += "`order_sales_id` = '{}',".format(
order_sales_id)
sql += "`order_customer_id` = '{}',".format(
order_customer_id)
sql += "`pembelian_supplier_id` = '{}',".format(
pembelian_supplier_id)
sql += "`pembelian_tanggal` = '{}',".format(
pembelian_tanggal)
sql += "`pembelian_tanggal_jatuh_tempo` = '{}',".format(
pembelian_tanggal_jatuh_tempo)
sql += "`pembelian_faktur` = '{}',".format(
pembelian_faktur)
sql += "`pembelian_pajak` = '{}',".format(
pembelian_pajak)
sql += "`pembelian_diskon` = '{}',".format(
pembelian_diskon)
sql += "`pembelian_total` = '{}',".format(
pembelian_total)
sql += "`pembelian_status` = '{}'".format(
pembelian_status)
sql += "ON DUPLICATE KEY UPDATE "
sql += "`order_type` = '{}',".format(order_type)
sql += "`order_sales_id` = '{}',".format(
order_sales_id)
sql += "`order_customer_id` = '{}',".format(
order_customer_id)
sql += "`pembelian_supplier_id` = '{}',".format(
pembelian_supplier_id)
sql += "`pembelian_tanggal` = '{}',".format(
pembelian_tanggal)
sql += "`pembelian_tanggal_jatuh_tempo` = '{}',".format(
pembelian_tanggal_jatuh_tempo)
sql += "`pembelian_faktur` = '{}',".format(
pembelian_faktur)
sql += "`pembelian_pajak` = '{}',".format(
pembelian_pajak)
sql += "`pembelian_diskon` = '{}',".format(
pembelian_diskon)
sql += "`pembelian_total` = '{}',".format(
pembelian_total)
sql += "`pembelian_status` = '{}'".format(
pembelian_status)
execs = connExecute(sql)
# Register the transaction
if pembelian_status == "ST200":
transactionId = generateId("TRC")
sql = "INSERT INTO `transaksi` "
sql += "(`transaksi_id`, `order_id`, `transaksi_jumlah`) "
sql += "VALUES ('{}', '{}', '{}'); ".format(
transactionId, pembelian_id, pembelian_total)
connExecute(sql)
# Delete all previous item
sql = "DELETE FROM `order_item` WHERE `pembelian_id` = '{}'".format(
pembelian_id)
connExecute(sql)
# Registering all item
for item in pembelian_item:
sql = "INSERT INTO `order_item` "
sql += "(`pembelian_id`, `barang_id`, `barang_satuan`, "
sql += "`barang_jumlah`, `barang_harga`, `barang_total`) "
sql += "VALUES ('{}', '{}', '{}', '{}', '{}', '{}')".format(
pembelian_id, item['barang_id'], item['barang_satuan'],
int(item['barang_jumlah']), int(item['barang_harga']),
int(item['barang_total']))
connExecute(sql)
# Update the barang_total to the database
if pembelian_status == "ST200":
sql = "UPDATE `barang` "
sql += "SET `barang_stok_toko` = `barang_stok_toko` {} '{}' ".format(
operator, item['barang_jumlah'])
sql += "WHERE `barang`.`barang_id` = '{}' ".format(
item['barang_id'])
connExecute(sql)
return execs
def objectDataSupplier():
json_data = {}
temporary_data = connExecute("SELECT * FROM `master_supplier`")
for data in temporary_data:
json_data[data['supplier_id']] = "{}".format(data['supplier_nama'])
return json_data
def objectDataSatuan():
json_data = {}
temporary_data = connExecute("SELECT * FROM `satuan`")
for data in temporary_data:
json_data[data['satuan_id']] = "{}".format(data['satuan_nama'])
return json_data
