def historyTask(uName):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute('SELECT req_id, req_judul, user_name, ktgri_nama, req_date, req_endDate, req_kodelaporan FROM t_request a LEFT JOIN m_user b ON a.user_id = b.user_id LEFT JOIN m_kategori c ON a.ktgri_id = c.ktgri_id WHERE req_status = "Finished" and req_PIC = "'+uName+'" ORDER BY req_id desc')
resultHist = cursor.fetchall()
histList = []
for row in resultHist:
histDict = {
'requestId' : row[0],
'requestJudul' : row[1],
'userName' : row[2],
'requestKategori' : row[3],
'requestTanggal' : row[4],
'requestEndDate' : row[5],
'requestKodeLaporan' : row[6]
}
histList.append(histDict)
historyTask = json.dumps(histList)
return historyTask
except Error as e :
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if(db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def listTask(uId):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
#listTask = cursor.execute(''.join(['SELECT req_id, req_judul, user_name, ktgri_nama, req_date, req_deadline, req_prioritas FROM t_request a LEFT JOIN m_user b ON a.user_id = b.user_id LEFT JOIN m_kategori c ON a.ktgri_id = c.ktgri_id WHERE req_PIC = "'+session['username']+'" ORDER BY req_id']))
cursor.execute('SELECT req_id, req_judul, user_name, ktgri_nama, req_date, req_deadline, req_prioritas, req_status, prog_id FROM t_request a LEFT JOIN m_user b ON a.user_id = b.user_id LEFT JOIN m_kategori c ON a.ktgri_id = c.ktgri_id WHERE req_status = "On Process" and prog_id = "'+uId+'" ORDER BY req_id desc')
resultTask = cursor.fetchall()
taskProg = []
for row in resultTask:
taskDict = {
'requestId' : row[0],
'requestJudul' : row[1],
'userName' : row[2],
'requestKategori' : row[3],
'requestTanggal' : str(row[4]),
'requestDeadline' : str(row[5]),
'requestPrioritas' : row[6],
'requestStatus' : row[7],
'progId' : row[8]
}
taskProg.append(taskDict)
listTask = json.dumps(taskProg)
return listTask
except Error as e :
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if(db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def prioritas(self, request_id):
self.update_prioritas=''
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute('UPDATE t_request SET req_prioritas = "0" WHERE req_id= "'+request_id+'" ')
db.commit()
update_prioritas = cursor.fetchone()
return update_prioritas
except Error as e :
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if(db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def getPosisi():
username = request.form['username']
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(''.join(['select user_posisi from m_user where user_id = "'+username+'"']))
record = cursor.fetchone()
position = str(record).replace("('",'').replace("',)",'')
return position
except Error as e:
print("Error while conencting file MySQL", e)
finally:
#Closing DB Connection
if(db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def changePass(self, user_password, new_password, confirm_password, username):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(' UPDATE m_user SET user_password = user_password WHERE user_id = "'+session['user_id']+'" ')
db.commit()
undo_prioritas = cursor.fetchone()
return undo_prioritas
except Error as e :
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if(db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def onProgressTask(self):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute('''SELECT user_name, req_id, req_judul, ktgri_nama, org_nama, req_date,
req_dateAccept, req_PIC, req_deadline
FROM m_user a
LEFT JOIN t_request b
ON a.user_id = b.user_id
LEFT JOIN m_kategori c
ON b.ktgri_id = c.ktgri_id
LEFT JOIN m_organisasi d
ON b.org_id = d.org_id
WHERE req_status = "On Process" ''')
onProgTask = cursor.fetchall()
for onProg in onProgTask:
onNama = onProg[0]
onId = onProg[1]
onJud = onProg[2]
onKat = onProg[3]
onOrg = onProg[4]
onDate = onProg[5]
onDateA = onProg[6]
onPIC = onProg[7]
onDead = onProg[8]
return onProgTask
except Error as e :
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if(db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def get_numberID(self):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(
'select count(req_id) from t_request where month(req_date) = month(now())'
)
record = cursor.fetchone()
clear = str(record).replace('(', '').replace(',)', '')
return int(clear)
except Error as e:
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def listTask(self):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
#listTask = cursor.execute(''.join(['SELECT req_id, req_judul, user_name, ktgri_nama, req_date, req_deadline, req_prioritas FROM t_request a LEFT JOIN m_user b ON a.user_id = b.user_id LEFT JOIN m_kategori c ON a.ktgri_id = c.ktgri_id WHERE req_PIC = "'+session['username']+'" ORDER BY req_id']))
listTask = cursor.execute(
'SELECT req_id, req_judul, user_name, ktgri_nama, req_date, req_deadline, req_prioritas, req_status, prog_id FROM t_request a LEFT JOIN m_user b ON a.user_id = b.user_id LEFT JOIN m_kategori c ON a.ktgri_id = c.ktgri_id WHERE req_status = "On Process" and req_PIC = "'
+ session['username'] + '" ORDER BY req_id desc')
listTask = cursor.fetchall()
return listTask
except Error as e:
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def availableTaskSPV(self):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
listAvailTaskSPV = cursor.execute('''SELECT user_name, user_posisi, req_id, req_judul, ktgri_nama, org_nama, req_date,
req_deadline, req_prioritas
FROM m_user a
LEFT JOIN t_request b
ON a.user_id = b.user_id
LEFT JOIN m_kategori c
ON b.ktgri_id = c.ktgri_id
LEFT JOIN m_organisasi d
ON b.org_id = d.org_id
WHERE req_status LIKE 'Waiting%' ORDER BY req_deadline asc ''')
listAvailTaskSPV = cursor.fetchall()
for taskSPV in listAvailTaskSPV:
uName = taskSPV[0]
posisi = taskSPV[1]
reqId = taskSPV[2]
reqJud = taskSPV[3]
ktgri = taskSPV[4]
org = taskSPV[5]
rDate = taskSPV[6]
rDead = taskSPV[7]
rPrio = taskSPV[8]
return listAvailTaskSPV
except Error as e :
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if(db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def finReq(kode):
loadKode = json.loads(kode)
endDate = datetime.datetime.now()
for i in loadKode:
request_id = loadKode['request_id']
kodLap = loadKode['kode_laporan']
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(''.join([
'UPDATE t_request SET req_status = "Finished" WHERE req_id = "'
+ request_id + '"'
]))
db.commit()
cursor.execute('INSERT INTO m_rating VALUES (%s,%s,%s)',
(request_id, '0', ''))
db.commit()
cursor.execute(''.join([
'UPDATE t_request SET req_endDate = "' + str(endDate) +
'", req_kodeLaporan = "' + kodLap + '" WHERE req_id = "' +
request_id + '"'
]))
db.commit()
print('Report Finished')
except Error as e:
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def rejectRequest(data):
loadData = json.loads(data)
uName = loadData['userName']
request_id = loadData['request_id']
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute('UPDATE t_request SET req_status = "Rejected by ' +
uName + '" WHERE req_id = "' + request_id + '" ')
db.commit()
except Error as e:
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def namaDept(self):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(
'select ktgri_id, ktgri_nama from m_kategori where ktgri_aktifYN = "Y" Order by ktgri_id'
)
listDept = cursor.fetchall()
return listDept
except Error as e:
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def namaPenerima(self):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(''.join([
'select user_id, user_name, user_email from m_user where user_flag = "User" '
]))
listPen = cursor.fetchall()
return listPen
except Error as e:
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def listNamaOrganisasi(self):
self.list_org = ''
# if request.method == 'POST':
try:
db = databaseCMS.db_request()
cursor = db.cursor()
listOrg = cursor.execute('SELECT org_id, org_nama FROM cms_request.m_organisasi ORDER BY org_nama')
#listOrg = cursor.execute('select org_id, org_nama from m_organisasi where org_aktifYN = "Y" order by org_id')
listOrg = cursor.fetchall()
return listOrg
except Error as e :
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if(db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def listFinished(uId):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(''.join([
'SELECT req_kodeLaporan, req_judul, req_date, req_PIC, req_endDate, b.req_id, rating FROM t_request a LEFT JOIN m_rating b ON a.req_id = b.req_id WHERE req_status = "Finished" and user_id="'
+ uId + '" ORDER BY req_date desc'
]))
resultFinished = cursor.fetchall()
finishedList = []
for row in resultFinished:
finishedDict = {
'RequestKodeLaporan': row[0],
'RequestJudul': row[1],
'RequestDate': str(row[2]),
'RequestPIC': row[3],
'RequestEndDate': str(row[4]),
'RequestId': row[5],
'RequestRating': row[6]
}
finishedList.append(finishedDict)
finishedResult = json.dumps(finishedList)
print(finishedResult)
return finishedResult
except Error as e:
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def listRequestUser(uId):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(''.join([
'SELECT req_id ,IFNULL(req_judul,""), IFNULL(req_date,""),IFNULL(req_deadline,""), IFNULL(req_status,""), IFNULL(req_PIC,""), IFNULL(req_kodelaporan, "") from t_request WHERE req_status IN ("On Process" , "Waiting") AND user_id="'
+ uId + '" ORDER BY req_id desc'
]))
resultReqUser = cursor.fetchall()
reqUserList = []
for row in resultReqUser:
reqUserDict = {
'RequestId': row[0],
'RequestJudul': row[1],
'RequestDate': row[2],
'RequestDeadline': row[3],
'RequestStatus': row[4],
'RequestPIC': row[5],
'RequestKodeLaporan': row[6]
}
reqUserList.append(reqUserDict)
reqUserResult = json.dumps(reqUserList)
print(reqUserResult)
return reqUserResult
except Error as e:
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def finishRating(data):
i = json.loads(data)
rating = i['rating']
keterangan = i['keterangan']
request_id = i['request_id']
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(' UPDATE m_rating SET rating = "'+rating+'" , keterangan ="'+keterangan+'" WHERE req_id = "'+request_id+'" ')
db.commit()
except Error as e :
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if(db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def confirmRequest(self, request_id):
self.confirm_request = ''
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(''.join([
'UPDATE t_request SET req_status = "Confirmed" WHERE req_id = "'
+ request_id + '"'
]))
db.commit()
confirm_request = cursor.fetchone()
return confirm_request
except Error as e:
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def getDetailTask(self, request_id):
self.detail_task = ''
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(''.join([
'SELECT a.req_id, req_judul, req_deskripsi, org_nama, ktgri_nama, req_tampilan, req_periode, req_deadline, req_file, reqSch_tanggal, reqSch_bulan, reqSch_hari, req_kodeLaporan, req_tujuan FROM t_request a LEFT JOIN m_organisasi b ON a.org_id = b.org_id LEFT JOIN m_kategori c ON a.ktgri_id = c.ktgri_id LEFT JOIN t_reqSchedule d ON a.req_id = d.req_id WHERE a.req_id = "'
+ request_id + '"'
]))
detail_task = cursor.fetchone()
print(detail_task)
return detail_task
except Error as e:
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def finishRequest(self, request_id):
self.finish_request =''
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(''.join(['UPDATE t_request SET req_status = "Finished" WHERE req_id = "'+request_id+'"']))
db.commit()
cursor.execute('INSERT INTO m_rating VALUES (%s,%s,%s)',(request_id,'0',''))
db.commit()
# finish_request = cursor.fetchone()
# return finish_request
except Error as e :
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if(db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def availableTask(self):
try:
db = databaseCMS.db_request()
cursor = db.cursor()
listAvailTask = cursor.execute(
'''SELECT req_id, req_judul, user_name, ktgri_nama,
req_date, req_deadline, req_prioritas
FROM t_request a
LEFT JOIN m_user b
ON a.user_id = b.user_id
LEFT JOIN m_kategori c
ON a.ktgri_id = c.ktgri_id
WHERE req_status LIKE 'Waiting%' ORDER BY req_deadline asc'''
)
listAvailTask = cursor.fetchall()
for row in listAvailTask:
requestId = row[0]
requestJudul = row[1]
requestNama = row[2]
requestKategori = row[3]
requestTanggal = row[4]
requestDeadline = row[5]
requstPrioritas = row[6]
return listAvailTask
except Error as e:
print("Error while connecting file MySQL", e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
from db import databaseCMS
from fpdf import FPDF
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute('SELECT user_name, user_id, user_password FROM m_user')
result = cursor.fetchall()
k = 0
for i in result:
pdf = FPDF()
pdf.set_font('Arial', size=8)
pdf.set_left_margin(0.5)
pdf.add_page()
pdf.cell(200, 10, txt="No Kwitansi : ", ln=2, align="L")
pdf.cell(200,
10,
txt="Telah Diterima Dari : " + i[0],
ln=4,
align="L")
pdf.cell(200,
10,
txt="Sejumlah : " + i[1],
ln=6,
align="L")
pdf.cell(200, 10, txt="Untuk Pembayaran : " + i[2], ln=10, align="L")
pdf.cell(50, 10, 'text', 1, 0, 'C')
pdf.set_line_width(1)
k = k + 1
pdf.output('Kwitansi' + str(k) + '.pdf')
def editRequest(editData):
if request.method == 'POST':
loadEditData = json.loads(editData)
r = requests.get('http://127.0.0.1:5001/generateRequestId')
r2 = json.dumps(r.json())
rId = json.loads(r2)
for x in rId:
req_id = x['requestId']
reqSch_aktifYN = 'Y'
reqSch_groupBy = 'Dr. Andre Lembong'
req_dateAccept = None
req_endDate = None
req_status = 'Waiting'
req_prioritas = '1'
reqSch_lastUpdate = datetime.datetime.now()
req_date = datetime.datetime.now()
reqSch_bulan = loadEditData['Bulan']
req_deadline = loadEditData['Deadline']
req_deskripsi = loadEditData['Deskripsi']
req_file = loadEditData['File']
reqSch_hari = loadEditData['Hari']
req_judul = loadEditData['Judul']
ktgri_id = loadEditData['KtgriId']
org_id = loadEditData['OrgId']
req_PIC = loadEditData['PIC']
req_periode = loadEditData['Periode']
prog_id = loadEditData['ProgId']
req_tampilan = loadEditData['Tampilan']
reqSch_tanggal = loadEditData['Tanggal']
req_tujuan = loadEditData['Tujuan']
user_id = loadEditData['UserId']
req_kodeLaporan = loadEditData['kodLap']
reqSch_ktgri = loadEditData['schKtgri']
reqSch_lastUpdate = loadEditData['schLastUpdate']
reqSch_org = loadEditData['schOrg']
reqSch_reportPIC = loadEditData['schPIC']
reqSch_penerima = loadEditData['schPenerima']
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(
'INSERT INTO t_request VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
(req_id, prog_id, user_id, org_id, ktgri_id, req_kodeLaporan,
req_judul, req_deskripsi, req_tujuan, req_tampilan,
req_periode, req_deadline, req_file, req_date, req_dateAccept,
req_endDate, req_status, req_PIC, req_prioritas))
db.commit()
cursor.execute(
'INSERT INTO t_reqSchedule VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
(req_id, reqSch_hari, reqSch_bulan, reqSch_tanggal,
reqSch_groupBy, reqSch_reportPIC, reqSch_org, reqSch_ktgri,
reqSch_lastUpdate, reqSch_aktifYN, reqSch_penerima))
db.commit()
print("Edit Request Sent")
except Error as e:
print("Error while connecting file MySQL", e)
flash('Error,', e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
def requestEditLap(self,
prog_id,
user_id,
req_report,
req_kodeLaporan,
req_deskripsi,
req_tampilan,
req_deadline,
req_file,
req_PIC,
reqSch_hari,
reqSch_bulan,
reqSch_tanggal,
reqSch_groupBy='Dr. Andre Lembong',
reqSch_reportPIC=None,
reqSch_ktgriNama=None,
reqSch_orgNama=None,
reqSch_aktifYN='Y',
req_dateAccept=None,
req_endDate=None,
req_status='Waiting',
req_prioritas='1',
reqSch_penerima=None):
self.req_id = self.generateRequestID()
self.prog_id = prog_id
self.user_id = user_id
self.org_id = ''
self.ktgri_id = ''
self.req_kodeLaporan = req_kodeLaporan
self.req_deskripsi = req_deskripsi
self.req_tampilan = req_tampilan
self.req_periode = ''
self.req_deadline = req_deadline
self.req_file = req_file
self.req_date = datetime.datetime.now()
self.req_dateAccept = req_dateAccept
self.req_endDate = req_endDate
self.req_status = req_status
self.req_PIC = req_PIC
self.req_prioritas = req_prioritas
self.last_report = TemplateLaporan().getDataReport(req_report)
self.req_judul = self.last_report[1]
self.req_tujuan = self.last_report[2]
self.reqSch_hari = reqSch_hari
self.reqSch_bulan = reqSch_bulan
self.reqSch_tanggal = reqSch_tanggal
self.reqSch_groupBy = reqSch_groupBy
self.reqSch_lastUpdate = datetime.datetime.now()
try:
db = databaseCMS.db_request()
cursor = db.cursor()
cursor.execute(
'INSERT INTO t_request VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
(self.req_id, prog_id, user_id, self.org_id, self.ktgri_id,
req_kodeLaporan, self.req_judul, req_deskripsi,
self.req_tujuan, req_tampilan, self.req_periode, req_deadline,
req_file, self.req_date, req_dateAccept, req_endDate,
self.req_status, req_PIC, req_prioritas))
db.commit()
record = cursor.fetchone()
cursor.execute(
'INSERT INTO t_reqSchedule VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
(self.req_id, reqSch_hari, reqSch_bulan, reqSch_tanggal,
reqSch_groupBy, reqSch_reportPIC, reqSch_orgNama,
reqSch_ktgriNama, self.reqSch_lastUpdate, reqSch_aktifYN,
reqSch_penerima))
db.commit()
record = cursor.fetchone()
print("Your connected...", record)
except Error as e:
print("Error while connecting file MySQL", e)
flash('Error,', e)
finally:
#Closing DB Connection.
if (db.is_connected()):
cursor.close()
db.close()
print("MySQL connection is closed")
