def myTables():
with orcl('system', 'qzLmDtCHb3', 'PC90TSTB') as db:
# Truncate the table
sql = 'TRUNCATE TABLE SYSTEM.MY_TABLES'
db.dbExecuteCommand(sql)
# Populate with new tables
for tab in tables:
sql = 'INSERT INTO SYSTEM.MY_TABLES (tab) VALUES (\'' + tab + '\')'
db.dbExecuteCommand(sql)
db.dbCommit()
def alterGrantDestTables(tab):
with orcl('system', 'RzgMEuUd8S', 'OBINPD') as db:
sql = 'ALTER TABLE '
sql = sql + sdsUser + '.' + tab
sql = sql + ' add (CDC$_SRC_LAST_UPDATE_DATE TIMESTAMP default CURRENT_TIMESTAMP,CDC$_RPL_LAST_UPDATE_DATE TIMESTAMP default CURRENT_TIMESTAMP, CDC$_DML_CODE VARCHAR2(1 CHAR) default \'I\') '
print(sql)
db.dbExecuteCommand(sql)
# Grant SELECT to DW user
sql = 'GRANT SELECT ON ' + sdsUser + '.' + tab + ' TO ' + dstDWUser
db.dbExecuteCommand(sql)
def getCurrentSCN():
with orcl('system', 'qzLmDtCHb3', 'PC90TSTB') as db:
sql = 'SELECT APPLIED_SCN FROM DBA_CAPTURE WHERE CAPTURE_NAME = \'OGG$CAP_' + extractName + '\''
data = db.dbExecuteFetchOne(sql)
value = (data[0])
return value
from dbConn import orcl
from flask import jsonify
with orcl() as db:
def getOpenCRs(username):
sql = "SELECT COUNT(1) FROM PS_PHI_CR_TBL"
sql = sql + " WHERE (lower(PHI_ASSIGN_TO) = :usrname"
sql = sql + " OR lower(PHI_ASSIGN_TO) IN (SELECT LOWER(ROLENAME) FROM PS_ROLEUSER_VW WHERE lower(OPRID) = :usrname ))"
sql = sql + " AND PHI_CR_STATUS NOT IN (\'CLSD\',\'CNCL\')"
params = {'usrname': username}
data = db.dbExecuteFetchOne(sql, params)
if data[0] > 0:
sql = "SELECT \'<a href=\"https://phire.uc.edu/\' || PHI_CR_NUM || \'\" target=\"_blank\">\' || PHI_CR_NUM || \'</a>\', PHI_CR_STATUS, PHI_TITLE"
sql = sql + " FROM PS_PHI_CR_TBL"
sql = sql + " WHERE (lower(PHI_ASSIGN_TO) = :usrname"
sql = sql + " OR lower(PHI_ASSIGN_TO) IN (SELECT LOWER(ROLENAME) FROM PS_ROLEUSER_VW WHERE lower(OPRID) = :usrname ))"
sql = sql + " AND PHI_CR_STATUS NOT IN (\'CLSD\',\'CNCL\',\'CLNA\')"
params = {'usrname': username}
issues = db.dbExecuteFetchAll(sql, params)
return jsonify(issues)
else:
noCRs = [['0', '0', 'No CRs Assigned']]
return jsonify(noCRs)
def getOpenIssues(username):
sql = "SELECT \'<a href=\"https://phire.uc.edu/\' || PHI_ISSUE_NUM || \'\" target=\"_blank\">\' || PHI_ISSUE_NUM || \'</a>\', PHI_ISSUE_STATUS, PHI_ASSIGN_TO, to_char(cast(PHI_OPEN_DTTM as date), \'MM/DD/YYYY HH24:MI:SS\'), PHI_TITLE"
sql = sql + " FROM PS_PHI_ISSUE I"
from dbConn import orcl
from flask import jsonify
with orcl() as db:
def getProjects(environment):
sql = "SELECT \'<a vlink=\"#551A8B\" href=\"https://phire.uc.edu/\' || PHI_CR_NUM || \'\" target=\"_blank\">\' || PHI_CR_NUM || \'</a>\', PHI_CR_TYPE, PHI_CR_STATUS, PHI_TITLE"
sql = sql + " FROM PS_PHI_CR_TBL"
sql = sql + " WHERE PHI_CR_TYPE IN (\'QRY\',\'PROJ\')"
## Conditional logic based on environment... This is not the best way but its lazy and should work
if environment == 'TESTC':
sql = sql + " AND PHI_CR_STATUS IN (\'MIGC\',\'MIGB\',\'MIGU\', \'MIGZ\', \'MIGP\',\'TSTC\', \'TSTB\', \'TSTP\', \'TSTU\', \'APVP\')"
elif environment == 'TESTB':
sql = sql + " AND PHI_CR_STATUS IN (\'MIGB\',\'MIGU\', \'MIGZ\', \'MIGP\',\'TSTB\', \'TSTP\', \'TSTU\', \'APVP\')"
else:
sql = sql + " AND PHI_CR_STATUS IN (\'MIGU\', \'MIGZ\', \'MIGP\', \'TSTP\', \'TSTU\', \'APVP\')"
issues = db.dbExecuteFetchAll(sql)
return jsonify(issues)