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")