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