def paris_df(res, units, date, distribution='Worldwide'):
engine = create_engine('sqlite:///../PI.sqlite')
plants = ('SP5 ', 'ISA1 ', 'ISA2 ', 'CISA1', 'CISA2', 'TISA2')
df = pd.DataFrame()
for indicator in ('CISA2', 'ISA2 ', 'CRE ', 'CY ', 'FLR ', 'GRLF ', 'SP1 ', 'SP2 ', 'SP5 ', 'UA7 ', 'UCF ',
'UCLF ', 'US7 '):
print('.',end='')
for u in units:
active = x.active_unit(date).LocId.tolist() # all active units and plants
centre = f.centre(x.cnt(u), date, False) # list of units in the same centre
reactor = pd.read_sql('select D.LocId from PI_NsssTypeLookup T inner join PI_UnitData D \
on T.NsssTypeId=D.NsssTypeId where T.NsssType="{}"'.format(x.reactor(u, False)), engine).LocId
ar = set(reactor) & set(active)
r = set([f.station(u, date) for u in ar])
r = set(reactor) | set(r)
# TODO: to be checked
RandC = set(centre) & set(r)
distr = active if distribution=='Worldwide' else r if distribution=='Same reactor type' \
else RandC if distribution=='Same reactor type and RC' else None
Id = f.station(u,date) if indicator in plants else u
if distr is not None:
df = df.append(quartile(res, Id, indicator, distr), ignore_index=False)
else:
sys.exit(3)
if Id != u:
break
return df
def analyseTime(qtr, centreCode):
"""
:param qtr: YYYYMM
:param centreCode: a letter of centre
:return: median and mean time were spending for analysis
"""
req = 'select julianday(ProductionDate)-julianday(SubmittalDate) from PI_DataSubmittal where LocId in ({}) and YrMn = {}'\
.format(f.centre(centreCode, qtr), qtr)
return pd.read_sql(req, engine).median(), pd.read_sql(req, engine).mean()
def DES(date, uList=None):
units = f.centre(uList, date) if uList is not None else 0
req = 'select SourceId, ElementCode, ElementValue from PI_IndValues where SourceId in ({}) and SourceCode = " " ' \
'and YrMn = {}'.format(units,date)
res = pd.read_sql(req, engine)
res.SourceId, res.ElementCode = res.SourceId.apply(
name), res.ElementCode.apply(eName)
res['Date'] = date
return res
def pi2(qtr, centreCode, plt=False):
req = 'select max(julianday(ProductionDate)-julianday("{}")) from PI_DataSubmittal where LocId in ({}) and YrMn = {}'\
.format(endOfQtr(qtr), f.centre(centreCode, qtr), qtr)
maxUnit = pd.read_sql(req, engine).values[0][0]
req = 'select avg(julianday(ProductionDate)-julianday("{}")) from PI_DataSubmittal where LocId in ({}) and YrMn = {}'\
.format(endOfQtr(qtr), f.centre(centreCode, qtr), qtr)
avg = pd.read_sql(req, engine).values[0][0]
req = 'select julianday(ProductionDate)-julianday("{}") from PI_DataSubmittal where LocId in ({}) and YrMn = {}'\
.format(endOfQtr(qtr), f.centre(centreCode, qtr), qtr)
d = pd.read_sql(req, engine)
med = d.median()[0]
req = 'select sum((julianday(ProductionDate)-julianday("{}"))>60) from PI_DataSubmittal where LocId in ({}) and YrMn = {}'\
.format(endOfQtr(qtr), f.centre(centreCode, qtr, True, True), qtr)
numOfOut = pd.read_sql(req, engine).values[0][0]
if plt:
return d.hist(label='PI-2 for ' + centreCode)
return maxUnit, avg, med, numOfOut
def SOER(date, uList='T'):
tqdm.pandas()
units = f.centre(uList, date) if uList is not None else 0
uid = pd.read_sql(
'select UnitID ,UnitName from CORE_Unit where INPORef in ({})'.format(
units), engine)
visit = pd.read_sql(
'select UnitID, VisitID, VisitUnitID from CORE_VisitUnit', engine)
visit = visit[visit.UnitID.isin(uid.UnitID)]
v = pd.read_sql(
'select VisitID, VisitTypeID, StartDate, EndDate, YearFilter from CORE_Visit',
engine)
visit = visit.merge(v, on='VisitID')
visit = visit[visit.YearFilter == int(str(date)[:4])].drop_duplicates(
subset='VisitID')
soer = pd.read_sql('select VisitSOERID, VisitID from SITS_VisitSOER',
engine)
visit = visit.merge(soer, on='VisitID')
visit['VisitSOERRecID'] = visit.VisitSOERID.progress_apply(
lambda recID: pd.read_sql(
'select VisitSOERRecID from SITS_VisitSOERRec where VisitSOERID={}'
.format(recID), engine).VisitSOERRecID.values[0])
#visit['SOERSubRecID'] = visit.VisitSOERID.progress_apply(lambda recID: pd.read_sql(
# 'select SOERSubRecID from SITS_VisitSOERSubRec where VisitSOERID={}'.format(recID),
# engine).SOERSubRecID.values[0])
#visit['SOERSubRecCode'] = visit.SOERSubRecID.progress_apply(lambda recID: pd.read_sql(
# 'select SOERSubRecCode from CORE_SOERSubRec where SOERRecID={}'.format(recID),
# engine).SOERSubRecCode.values[0])
#visit['HasClassification'] = visit.SOERSubRecID.progress_apply(lambda recID: pd.read_sql(
# 'select HasClassification from CORE_SOERSubRec where SOERRecID={}'.format(recID),
# engine).HasClassification.values[0])
#visit['SOERID'] = visit.SOERID.progress_apply(lambda recID: pd.read_sql(
# 'select SOERCode from CORE_SOER where SOERID={}'.format(recID),
# engine).SOERCode.values[0])
#visit['SOERCode'] = visit.SOERID.progress_apply(lambda recID: pd.read_sql(
# 'select SOERCode from CORE_SOER where SOERID={}'.format(recID),
# engine).SOERCode.values[0])
visit['Notes'] = visit.VisitSOERRecID.progress_apply(
lambda visitID: pd.read_sql(
'select Notes from SITS_VisitSOERSubRec where VisitSOERRecID={}'.
format(visitID), engine).Notes.values[0])
visit['OECTNotes'] = visit.VisitSOERRecID.progress_apply(
lambda visitID: pd.read_sql(
'select OECTNotes from SITS_VisitSOERSubRec where VisitSOERRecID={}'
.format(visitID), engine).OECTNotes.values[0])
# replace UID with name
visit.UnitID = visit.UnitID.progress_apply(
lambda id: uid[uid.UnitID == id].UnitName.values[0])
visit.VisitTypeID = visit.VisitTypeID.progress_apply(
lambda id: pd.read_sql(
'select VisitTypeCode from CORE_VisitType where VisitTypeID={}'.
format(id), engine).VisitTypeCode.values[0])
visit = visit.drop(columns=[
'VisitID', 'VisitUnitID', 'YearFilter', 'VisitSOERID', 'VisitSOERRecID'
])
print(list(visit))
'''
print(visit.tail(), '\nlen=', len(visit))
soer = pd.read_sql('select VisitSOERRecID, SOERRecID, CreateDate from SITS_VisitSOERRec', engine).drop_duplicates()
soer = soer[soer.SOERRecID.isin(visit.SOERRecID)].drop_duplicates()
visit = visit.merge(soer, on='SOERRecID').drop_duplicates()
print(visit.tail(), '\nlen=', len(visit))
soer = pd.read_sql('select VisitSOERRecID, Notes, OECTNotes from SITS_VisitSOERSubRec', engine).drop_duplicates()
visit = visit.merge(soer, on='VisitSOERRecID').drop_duplicates()
visit.UnitID = visit.UnitID.progress_apply(lambda id: uid[uid.UnitID == id].UnitName.values[0])
visit['VisitSOERID'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql(
'select VisitSOERID from SITS_VisitSOER where VisitID={} and VisitSOERID is not null'.format(visitID), engine).VisitSOERID.values)
visit['VisitSOERID'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql(
'select VisitSOERID from SITS_VisitSOER where VisitID={} and VisitSOERID is not null'.format(visitID),
engine).VisitSOERID.values)
visit['Notes'] = visit.VisitSOERRecID.progress_apply(lambda visitID: pd.read_sql(
'select Notes from SITS_VisitSOERSubRec where VisitSOERRecID={}'.format(visitID), engine).Notes.values[0])
visit['OECTNotes'] = visit.VisitSOERRecID.progress_apply(lambda visitID: pd.read_sql(
'select OECTNotes from SITS_VisitSOERSubRec where VisitSOERRecID={}'.format(visitID), engine).OECTNotes.values[0])
visit['VisitType'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql(
'select VisitTypeID from CORE_Visit where VisitID={}'.format(visitID), engine).VisitTypeID.values[0])
visit.VisitType = visit.VisitType.progress_apply(lambda visitTypeID: pd.read_sql(
'select VisitType from CORE_VisitType where VisitTypeID={}'.format(visitTypeID), engine).VisitType.values[0])
visit['YearFilter'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql(
'select YearFilter from CORE_Visit where VisitID={}'.format(visitID), engine).YearFilter.values[0])
year = int(str(date)[:4])
visit = visit[visit.YearFilter==year]
visit['StartDate'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql(
'select StartDate from CORE_Visit where VisitID={}'.format(visitID), engine).StartDate.values[0])
visit['EndDate'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql(
'select EndDate from CORE_Visit where VisitID={}'.format(visitID), engine).EndDate.values[0])
print(visit.tail())
'''
print(visit.head())
return visit