def update(self, values, wherekey, keyword):
connection = DBConnect()
query = "UPDATE " + self.table + " SET " + self.column + " = " + str(
values)
query += " WHERE " + str(wherekey)
query += " = " + str(keyword)
connection.execute(query)
print("Data berhasil di update")
def setPassword(self, passwordBaru):
self.__password = passwordBaru
connection = DBConnect()
query = "UPDATE "+self.table + \
" SET password= '******' WHERE email = '%s'" % (
self.__password, self.__email)
connection.execute(query)
print("===Password Berhasil Dirubah===")
def order(self, table, col, urutan):
if urutan == 1:
x = 'ASC'
else:
x = 'DESC'
connection = DBConnect()
query = "SELECT * from " + table + " ORDER BY " + col + ' ' + x
# print(query)
result = connection.executeRead(query)
print(result)
def login(self, nama, email, password):
connection = DBConnect()
query = "SELECT * from "+self.table + \
" WHERE nama= '%s' and email= '%s' and password = '******'" % (
nama, email, password)
result = connection.executeRead(query)
if not result:
return True
else:
return False
def insert(self, values):
connection = DBConnect()
query = """INSERT INTO """ + self.table + " ("
for column in self.column:
query += column + ","
query = query[:-1]
query += ") VALUES ("
for value in values:
query += "'" + value + "',"
query = query[:-1]
query += ")"
print(query)
result = connection.executeInsert(query)
def insert(self, values):
connection = DBConnect()
query = """INSERT INTO """ + self.table + "("
for column in self.column:
query += column + ","
query = query[:-1]
query += ") VALUES ("
for value in values:
query += "'" + str(value) + "',"
query = query[:-1]
query += ")"
connection.execute(query)
print("Data berhasil dientrikan")
def __init__(self, inputEmail=None):
super().__init__("santri", [
"nama", "email", "password", "alamat", "no_hp", "perguruan_tinggi", "prodi", "kamar_id"])
# variable privet
if inputEmail == None:
pass
else:
self.__email = inputEmail
connection = DBConnect()
query = "SELECT password FROM " + self.table + \
" WHERE email='%s'" % (self.__email)
result = connection.executeRead(query)
self.__password = result[0][0]
def update(self, values, idInput):
connection = DBConnect()
query = """UPDATE """ + self.table + " SET "
for i in range(len(self.column)):
query += self.column[i] + "="
if values[i] == None:
query += "NULL,"
else:
query += "'" + values[i] + "',"
query = query[:-1]
query += " WHERE id ='%d'" % (idInput)
# print(query)
connection.execute(query)
print("===Berhasil DiUpdate===")
def create(self, values):
connection = DBConnect()
query = """INSERT INTO """ + self.table + " ("
for column in self.column:
query += column + ","
query = query[:-1]
query += ") VALUES ("
for value in values:
if value == None:
query += "NULL,"
else:
query += "'" + value + "',"
query = query[:-1]
query += ")"
# print(query)
result = connection.execute(query)
print("===Berhasil DiTambakan===")
def jadwalDewe(self, ustad_id):
connection = DBConnect()
query = "SELECT * from " + "jadwal" + \
" WHERE ustad_id= '%s'" % (ustad_id)
result = connection.executeRead(query)
# print(result)
p = PrettyTable()
x = []
x.append('id')
for column in self.column:
x.append(column)
p.field_names = x
for row in result:
listRow = list(row)
listRow.pop(len(listRow)-1)
listRow.pop(len(listRow)-1)
p.add_row(listRow)
print(p)
def read(self, role=None):
connection = DBConnect()
query = "SELECT * from " + self.table
result = connection.executeRead(query)
p = PrettyTable()
x = []
if role == None:
x.append('id')
for column in self.column:
x.append(column)
p.field_names = x
for row in result:
listRow = list(row)
listRow.pop(len(listRow) - 1)
listRow.pop(len(listRow) - 1)
if role != None:
listRow.pop(0)
p.add_row(listRow)
print(p)
def getDataTransaksi(self):
connection = DBConnect()
query = "SELECT transaksi.tanggal, data_barang.namaBarang , data_barang.hargaBeli , data_barang.hargaJual , transaksi.jumlahBarang ,(data_barang.hargaJual*transaksi.jumlahBarang) AS harga_total FROM transaksi JOIN data_barang ON transaksi.id_Barang = data_barang.id_Barang ORDER BY transaksi.tanggal"
result = connection.executeSelect(query)
for colomn in result:
print("tanggal : ", colomn[0])
print("nama barang : ", colomn[1])
print("harga beli : ", colomn[2])
print("harga jual : ", colomn[3])
print("jumlah yang dibeli : ", colomn[4])
print("harga total : ", colomn[3])
print("*"*10)
def search(self, value):
connection = DBConnect()
query = "SELECT * from " + self.table + " WHERE "
for i in range(len(self.column)):
query += self.column[
i] + " LIKE " + "'" + "%" + value + "%" + "'" + " OR "
query = query[:-3]
# print(query)
result = connection.executeRead(query)
p = PrettyTable()
x = []
x.append('id')
for column in self.column:
x.append(column)
p.field_names = x
for row in result:
listRow = list(row)
listRow.pop(len(listRow) - 1)
listRow.pop(len(listRow) - 1)
p.add_row(listRow)
print(p)
def getID(self):
connection = DBConnect()
query = "SELECT * from "+self.table + \
" WHERE email= '%s'" % (self.__email)
hasil = connection.executeRead(query)
return hasil[0][0]
def view(self):
connection = DBConnect()
query = "SELECT * FROM " + self.table
result = connection.select(query)
return result
def bayarSPP(self, nominal, email):
connection = DBConnect()
query = "INSERT INTO transaksi (tgl_pembayaran, nominal, jenis_transaksi, santri_id) VALUES ('%s',%s,'spp', %s)" % (
Santri.tgl, nominal, self.getID())
connection.execute(query)
print("===Bayar SPP Berhasil===")
def insert_single(self, values):
connection = DBConnect()
query = "INSERT INTO " + self.table + "(" + self.column + ")" + " VALUES (\'%s\');"
query = query % (values)
connection.execute(query)
def dataStok(self, id):
connection = DBConnect()
query = "SELECT stok FROM data_barang WHERE id_Barang= " + str(id)
result = connection.executeSelect(query)
return result[0]
def deleteBarang(self, id):
connection = DBConnect()
query = "DELETE FROM data_barang WHERE id_Barang= " + str(id)
print(query)
result = connection.executeDelete(query)
def showLaporanKeuangan():
connection = DBConnect()
query = "SELECT transaksi.tanggal, SUM(transaksi.jumlahBarang * data_barang.hargaJual) AS pendapatan_kotor,SUM((transaksi.jumlahBarang * data_barang.hargaJual) - (transaksi.jumlahBarang * data_barang.hargaBeli)) AS pendapatan_bersih FROM transaksi JOIN data_barang ON transaksi.id_Barang=data_barang.id_Barang GROUP BY transaksi.tanggal"
result = connection.executeSelect(query)
return result
def updateHargaBeli(self, harbel, id):
connection = DBConnect()
query = "UPDATE data_barang SET hargaBeli='" + str(
harbel) + "' WHERE id_Barang= " + str(id)
print(query)
result = connection.executeUpdate(query)
def updateNamaBarang(self, namaBaru, id):
connection = DBConnect()
query = "UPDATE data_barang SET namaBarang='" + str(
namaBaru) + "' WHERE id_Barang= " + str(id)
print(query)
result = connection.executeUpdate(query)
def __call__(self, email):
connection = DBConnect()
query = "SELECT * from santri WHERE email= '%s'" % (email)
result = connection.executeRead(query)
print("Kamar dengan ID", str(result[0][8]),
"Telah Melakukan Absen Jaga Pos")
def getID(self, email):
connection = DBConnect()
query = "SELECT * from " + self.table + " WHERE email= '%s'" % (email)
result = connection.executeRead(query)
return result[0][0]
def deleteTransaksi(self,id):
connection = DBConnect()
query = "DELETE FROM transaksi WHERE id= "+str(id)
print(query)
result = connection.executeDelete(query)
def updateStokBarang(self, stok, id):
connection = DBConnect()
query = "UPDATE data_barang SET stok='" + str(
stok) + "' WHERE id_Barang= " + str(id)
print(query)
result = connection.executeUpdate(query)
def dataPembelian(self,id):
connection = DBConnect()
query = "SELECT jumlahBarang FROM transaksi WHERE id_Barang= "+str(id)
result = connection.executeSelect(query)
return result[0]
def getDataTotalBayar():
connection = DBConnect()
query = "SELECT (hargaJual * 2) FROM data_barang WHERE id_Barang = 3"
result = connection.executeSelect(query)
print(result)
def getAllData(self):
connection = DBConnect()
query = "SELECT * from " + self.table
result = connection.executeSelect(query)
# print(result)
return result
def getID(cls, email):
connection = DBConnect()
query = "SELECT * from santri WHERE email= '%s'" % (email)
result = connection.executeRead(query)
# print(result)
return str(result[0][8])