def saveBbtTbmKpis(self,sDBPath):
bbttbmkpis =[]
for key in self.kpis:
_kpi = self.kpis[key]
bbttbmkpi = BbtTbmKpi(self.tunnelName, self.tbmName,self.iterationNo,key, _kpi.definition, _kpi.minImpact, _kpi.maxImpact, _kpi.avgImpact, _kpi.appliedLength, _kpi.percentOfApplication, _kpi.probabilityScore, _kpi.totalImpact)
bbttbmkpis.append(bbttbmkpi)
conn = getDBConnection(sDBPath)
c = conn.cursor()
c.execute("DELETE FROM BbtTbmKpi WHERE tunnelName=? AND tbmName=? AND iterationNo=?" , (self.tunnelName,self.tbmName, self.iterationNo))
conn.commit()
c.executemany("INSERT INTO BbtTbmKpi (tunnelName,tbmName,iterationNo,kpiKey,kpiDescr,minImpact,maxImpact,avgImpact,appliedLength,percentOfApplication,probabilityScore,totalImpact) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", bbttbmkpis)
conn.commit()
conn.close()
return bbttbmkpis
def main(argv):
sParm = "p,parameter in \n"
sParameterToShow = ""
segmentsToShow = []
sTbmCode = ""
sTypeToGroup = ""
bPrintHist = False
bShowProfile = False
bShowRadar = False
bShowKPI = False
bShowAllKpi = False
bShowDetailKPI = False
bGroupTypes = False
bShowAdvance = False
for k in parmDict:
sParm += "\t%s - %s\r\n" % (k,parmDict[k][0])
sParm += "\n t,tbmcode in \n"
for k in tbms:
sParm += "\t%s - Produttore %s di tipo %s per tunnel %s\r\n" % (k,tbms[k].manifacturer, tbms[k].type, tbms[k].alignmentCode)
sParm += "\n\t-r => generazione diagramma Radar per tutte le TBM\n"
sParm += "\n\t-k => generazione diagrammi KPI G, P e V\n"
sParm += "\n\t-a => generazione diagrammi KPI G + P + V\n"
sParm += "\n\t-d => generazione diagrammi KPI di Dettaglio\n"
sParm += "\n\t-i => generazione delle distribuzioni per ogni tipo di KPI selezionato\n"
sParm += "\n\t-c => raggruppamento per tipologia di TBM\n"
sParm += "\n\t-m => per tipologia di TBM indicata viene eseguito raggruppamento per Produttore\n"
sParm += "\n\t-s => per segmento progressivo indicata con km+m\n"
try:
opts, args = getopt.getopt(argv,"hp:t:rkadicm:s:",["parameter=","tbmcode=","radar","kpi","allkpi","detailkpi","histograms","compact_types","bytype","segment"])
except getopt.GetoptError:
print "readparameters.py -p <parameter> [-t <tbmcode>] [-rkai]\r\n where\r\n %s" % sParm
sys.exit(2)
if len(opts) < 1:
print "readparameters.py -p <parameter> [-t <tbmcode>] [-rkai]\r\n where\r\n %s" % sParm
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print "readparameters.py -p <parameter> [-t <tbmcode>] [-rkai]\r\n where\r\n %s" % sParm
sys.exit()
elif opt in ("-p", "--iparameter"):
sParameterToShow = arg
bShowProfile = True
if sParameterToShow =='adv':
bShowAdvance = True
elif opt in ("-s", "--segment"):
sSegmentToShow = arg
splitted = sSegmentToShow.split(",")
for s in splitted:
sf= s.split("+")
fSegmentToShow = float(sf[0])*1000+float(sf[1])
segmentsToShow.append((str(fSegmentToShow),"+".join(sf)))
elif opt in ("-t", "--tbmcode"):
sTbmCode = arg
elif opt in ("-r", "--radar"):
bShowRadar = True
elif opt in ("-k", "--kpi"):
bShowKPI = True
elif opt in ("-a", "--allkpi"):
bShowAllKpi = True
elif opt in ("-d", "--detailkpi"):
bShowDetailKPI = True
elif opt in ("-i", "--histograms"):
bPrintHist = True
elif opt in ("-c", "--compact_types"):
bGroupTypes = True
elif opt in ("-m", "--bytype"):
sTypeToGroup = arg
#bGroupTypes = True
if len(sTypeToGroup) >0 and sTypeToGroup not in ('DS','S','O'):
print "Wrong TBM Type -m=%s!\nreadparameters.py -p <parameter> [-t <tbmcode>] [-rkai]\r\n where\r\n %s" % (sTypeToGroup,sParm)
sys.exit(2)
if len(sParameterToShow) >0 and sParameterToShow not in parmDict:
print "Wrong parameter -p=%s!\nreadparameters.py -p <parameter> [-t <tbmcode>] [-rkai]\r\n where\r\n %s" % (sParameterToShow,sParm)
sys.exit(2)
if len(sTbmCode) >0 and sTbmCode not in tbms:
print "Wrong TBM Code -t=%s!\nreadparameters.py -p <parameter> -t <tbmcode> [-rkai]\r\n where\r\n %s" % (sTbmCode,sParm)
sys.exit(2)
# mi metto nella directory corrente
path = os.path.dirname(os.path.realpath(__file__))
os.chdir(path)
########## File vari: DB
sDBName = bbtConfig.get('Database','dbname')
sDBPath = os.path.join(os.path.abspath('..'), bbtConfig.get('Database','dbfolder'), sDBName)
if not os.path.isfile(sDBPath):
print "Errore! File %s inesistente!" % sDBPath
exit(1)
#load_tbm_table(sDBPath, tbms)
########### Outupt Folder
sDiagramsFolder = bbtConfig.get('Diagrams','folder')
sDiagramsFolderPath = os.path.join(os.path.abspath('..'), sDiagramsFolder)
# mi connetto al database
conn = getDBConnection(sDBPath)
# definisco il tipo di riga che vado a leggere, bbtparametereval_factory viene definita in bbtnamedtuples
conn.row_factory = sqlite3.Row
cur = conn.cursor()
# quante iterazioni?
sSql = "select max(BbtParameterEval.iteration_no) as max_iter from BbtParameterEval"
cur.execute(sSql)
bbtresult = cur.fetchone()
M_Max = float(bbtresult[0]) + 1.0
print "Numero massimo di iterazioni presenti %d" % M_Max
# Legge tutti i Tunnell
sSql = """SELECT distinct
bbtTbmKpi.tunnelName
FROM
bbtTbmKpi
ORDER BY bbtTbmKpi.tunnelName"""
cur.execute(sSql)
bbtresults = cur.fetchall()
print "Sono presenti %d diverse Gallerie" % len(bbtresults)
tunnelArray = []
for bbtr in bbtresults:
tunnelArray.append(bbtr[0])
# Legge tutte le TBM solo per associare i colori in maniera univoca
sSql = """SELECT bbtTbmKpi.tbmName, BbtTbm.type, BbtTbm.manufacturer, count(*) as cnt
FROM
bbtTbmKpi
JOIN BbtTbm on BbtTbm.name = bbtTbmKpi.tbmName
GROUP BY bbtTbmKpi.tbmName, BbtTbm.type, BbtTbm.manufacturer
ORDER BY bbtTbmKpi.tbmName"""
if bGroupTypes:
sSql = """SELECT BbtTbm.type, count(*) as cnt
FROM
bbtTbmKpi
JOIN BbtTbm on BbtTbm.name = bbtTbmKpi.tbmName
GROUP BY BbtTbm.type
ORDER BY BbtTbm.type"""
cur.execute(sSql)
bbtresults = cur.fetchall()
# associare un colore diverso ad ogni TBM
tbmColors = {}
for bbtr in bbtresults:
tbmColors[bbtr[0]] = main_colors.pop(0)
bShowlTunnel = False
for tun in tunnelArray:
allTbmData = []
print "\r\n%s" % tun
sSql = """SELECT bbtTbmKpi.tbmName, count(*) as cnt, BbtTbm.type, BbtTbm.manufacturer
FROM
bbtTbmKpi
JOIN BbtTbm on BbtTbm.name = bbtTbmKpi.tbmName
WHERE bbtTbmKpi.tunnelName = '"""+tun+"""'
GROUP BY bbtTbmKpi.tbmName, BbtTbm.type, BbtTbm.manufacturer
ORDER BY bbtTbmKpi.tbmName"""
# Filtro sulla eventuale TBM passata come parametro
if len(sTbmCode) > 0:
sSql = """SELECT bbtTbmKpi.tbmName, count(*) as cnt, BbtTbm.type, BbtTbm.manufacturer
FROM
bbtTbmKpi
JOIN BbtTbm on BbtTbm.name = bbtTbmKpi.tbmName
WHERE bbtTbmKpi.tunnelName = '"""+tun+"""' AND BbtTbm.name = '"""+sTbmCode+"""'
GROUP BY bbtTbmKpi.tbmName, BbtTbm.type, BbtTbm.manufacturer
ORDER BY bbtTbmKpi.tbmName"""
# Filtro sulla eventuale Tipologia passata come parametro
elif len(sTypeToGroup) > 0:
sSql = """SELECT bbtTbmKpi.tbmName, count(*) as cnt, BbtTbm.type, BbtTbm.manufacturer
FROM
bbtTbmKpi
JOIN BbtTbm on BbtTbm.name = bbtTbmKpi.tbmName
WHERE bbtTbmKpi.tunnelName = '"""+tun+"""' AND BbtTbm.type = '"""+sTypeToGroup+"""'
GROUP BY bbtTbmKpi.tbmName, BbtTbm.type, BbtTbm.manufacturer
ORDER BY bbtTbmKpi.tbmName"""
# Raggruppamento per Tipo TBM
elif bGroupTypes:
sSql = """SELECT BbtTbm.type, count(*) as cnt_tbmtype
FROM
BbtTbm
WHERE
BbtTbm.name IN (
SELECT DISTINCT BbtTbmKpi.tbmName
FROM bbtTbmKpi
WHERE
bbtTbmKpi.tunnelName = '"""+tun+"""')
GROUP BY BbtTbm.type
ORDER BY BbtTbm.type"""
cur.execute(sSql)
bbtresults = cur.fetchall()
print "Sono presenti %d diverse TBM" % len(bbtresults)
for tb in bbtresults:
tbmKey = tb[0]
tbmCount = float(tb[1])
if len(segmentsToShow) > 0:
for sCriteria, sProg in segmentsToShow:
# danzi.tn@20151123 calcolo iterazioni per la TBM corrente (non e' detto che siano tutte uguali)
sSql = "SELECT BBtParameterEval.*, BBtParameterEval.t1 +BBtParameterEval.t3 +BBtParameterEval.t4 +BBtParameterEval.t5 as tsum, 1 as adv FROM BbtParameter, BBtParameterEval WHERE BbtParameter.profilo_id = BBtParameterEval.profilo_id AND BbtParameter.fine = "+sCriteria+" AND BBtParameterEval.tunnelNAme = '"+tun+"' AND tbmNAme='"+tbmKey+"'"
if bGroupTypes:
sSql = "SELECT BBtParameterEval.*, BBtParameterEval.t1 +BBtParameterEval.t3 +BBtParameterEval.t4 +BBtParameterEval.t5 as tsum, 1 as adv FROM BbtParameter, BBtParameterEval, BbtTbm WHERE BbtParameter.profilo_id = BBtParameterEval.profilo_id AND BbtParameter.fine = "+sCriteria+" AND BbtTbm.name = BBtParameterEval.tbmName AND BbtParameter.profilo_id = BBtParameterEval.profilo_id AND BBtParameterEval.tunnelNAme = '"+tun+"' AND BbtTbm.type='"+tbmKey+"'"
cur.execute(sSql)
bbtresults = cur.fetchall()
pValues = []
for bbt_parametereval in bbtresults:
pVal = bbt_parametereval[sParameterToShow]
if pVal == None:
pVal = 0
pVal = float(pVal)
pValues.append(pVal)
if len(pValues) > 0:
num_bins = 50
fig = plt.figure(figsize=(32, 20), dpi=100)
ax1 = fig.add_subplot(111)
title("%s - %s" % (tun,replaceTBMName(tbmKey)))
weights = np.ones_like(pValues)/float(len(pValues))
n, bins, patches = ax1.hist(pValues,num_bins , normed=1, histtype ='stepfilled', weights=weights , color=tbmColors[tbmKey], alpha=0.3)
tbmMean = np.mean(pValues)
tbmSigma = np.std(pValues)
y = mlab.normpdf(bins, tbmMean, tbmSigma)
ax1.plot(bins, y, '--', color=tbmColors[tbmKey])
ax1.set_xlabel("%s (%f)" % (parmDict[sParameterToShow][0],tbmMean), color='r')
ax1.set_ylabel("Probabilita'(%)")
ax1.axvline(tbmMean, color='r', linewidth=2)
ax1.yaxis.grid(True)
sFileNAme = "bbt_%s_%s_%s_%s_hist.svg" % ( tun.replace (" ", "_"), replaceTBMName(tbmKey),sParameterToShow,sProg)
outputFigure(sDiagramsFolderPath, sFileNAme, format="svg")
print "Output su %s disponibile" % sFileNAme
plt.close(fig)
elif bShowProfile:
# danzi.tn@20151118 calcolo iterazioni per la TBM corrente (non e' detto che siano tutte uguali)
sSql = "select max(BbtParameterEval.iteration_no) as max_iter from BbtParameterEval WHERE BbtParameterEval.tbmName ='%s'" % tbmKey
if bGroupTypes:
sSql = "select max(BbtParameterEval.iteration_no) as max_iter from BbtParameterEval JOIN BbtTbm on BbtTbm.name = BbtParameterEval.tbmName WHERE BbtTbm.type ='%s'" % tbmKey
cur.execute(sSql)
bbtresult = cur.fetchone()
M = float(bbtresult[0]) + 1.0
if M_Max > M:
print "Numero massimo di iterazioni per %s sono %d" % (tbmKey, M)
sSql = "SELECT BBtParameterEval.*, BBtParameterEval.t1 +BBtParameterEval.t3 +BBtParameterEval.t4 +BBtParameterEval.t5 as tsum, 1 as adv FROM BBtParameterEval WHERE BBtParameterEval.tunnelNAme = '"+tun+"' AND tbmNAme='"+tbmKey+"' order by BBtParameterEval.iteration_no, BBtParameterEval.fine"
if bGroupTypes:
sSql = "SELECT BBtParameterEval.*, BBtParameterEval.t1 +BBtParameterEval.t3 +BBtParameterEval.t4 +BBtParameterEval.t5 as tsum, 1 as adv FROM BBtParameterEval JOIN BbtTbm on BbtTbm.name = BBtParameterEval.tbmName WHERE BBtParameterEval.tunnelNAme = '"+tun+"' AND BbtTbm.type='"+tbmKey+"' order by BBtParameterEval.iteration_no, BBtParameterEval.fine, BbtTbm.type"
cur.execute(sSql)
bbtresults = cur.fetchall()
# recupero tutti i parametri e li metto in una lista
N = len(bbtresults)/M # No di segmenti
pi = zeros(shape=(N,), dtype=float)
he = zeros(shape=(N,), dtype=float)
hp = zeros(shape=(N,), dtype=float)
ti = zeros(shape=(N,), dtype=float)
parm2show = zeros(shape=(N,M), dtype=float)
mean2Show = zeros(shape=(N,3), dtype=float)
tti = zeros(shape=(N,M), dtype=float)
xti = zeros(shape=(N,M), dtype=float)
i = 0
pj = 0
prev = 0.0
outValues =[]
if tun not in ('Galleria di linea direzione Sud'):
bbtresults.reverse()
for bbt_parametereval in bbtresults:
j = int(bbt_parametereval['iteration_no'])
if pj != j:
pj = j
prev = i = 0
pi[i] = bbt_parametereval['fine']
xti[i][j] = float(bbt_parametereval['dailyAdvanceRate'])
tti[i][j] = prev + 10.0/xti[i][j]
prev = tti[i][j]
he[i] = bbt_parametereval['he']
hp[i] = bbt_parametereval['hp']
pVal = bbt_parametereval[sParameterToShow]
if pVal == None:
pVal = 0
pVal = float(pVal)
if bShowAdvance:
pVal = tti[i][j]
if bGroupTypes:
pVal = pVal/tbmCount
outValues.append([int(bbt_parametereval['iteration_no']),float(bbt_parametereval['fine']), float(bbt_parametereval['he']),float(bbt_parametereval['hp']),pVal])
parm2show[i][j] = pVal
i += 1
for i in range(int(N)):
pki_mean = np.nanmean(parm2show[i,:])
# gabriele@20151124 rimosso 95o percentile sui grafi
#pki_std = np.nanstd(parm2show[i,:])
#mean2Show[i][0] = pki_mean - 2*pki_std
mean2Show[i][1] = pki_mean
#mean2Show[i][2] = pki_mean + 2*pki_std
i=0
for outVal in outValues:
outValues[i].append(mean2Show[i%N][1])
#outValues[i].append(mean2Show[i%N][0])
#outValues[i].append(mean2Show[i%N][2])
i += 1
if N==0:
print "\tPer TBM %s non ci sono dati in %s" % (tbmKey, tun)
else:
ylimInf = parmDict[sParameterToShow][2]
ylimSup = parmDict[sParameterToShow][3]
ymainInf = min(he)
fig = plt.figure(figsize=(32, 20), dpi=100)
ax1 = fig.add_subplot(111)
ax1.set_ylim(0,max(he)+100)
title("%s - %s" % (tun,replaceTBMName(tbmKey)))
ax1.plot(pi,he,'b-', linewidth=1)
if bShowlTunnel:
ax1.plot(pi,hp,'k-', linewidth=1)
ax1.set_xlabel('Progressiva (m)')
ax1.set_ylabel('Quota (m)', color='b')
for tl in ax1.get_yticklabels():
tl.set_color('b')
##########
ax2 = ax1.twinx()
ax2.yaxis.grid(True)
if ylimSup > 0:
ax2.set_ylim(ylimInf,ylimSup)
ax2.plot(pi,parm2show,'r.',markersize=1.0)
#ax2.plot(pi,mean2Show[:,0],'m-',linewidth=0.5, alpha=0.4)
ax2.plot(pi,mean2Show[:,1],'g-',linewidth=2, alpha=0.6)
#ax2.plot(pi,mean2Show[:,2],'c-',linewidth=0.5, alpha=0.4)
ax2.set_ylabel("%s (%s)" % (parmDict[sParameterToShow][0],parmDict[sParameterToShow][1]), color='r')
for tl in ax2.get_yticklabels():
tl.set_color('r')
outputFigure(sDiagramsFolderPath,"bbt_%s_%s_%s.svg" % ( tun.replace (" ", "_") , replaceTBMName(tbmKey),sParameterToShow), format="svg")
plt.close(fig)
# esposrto in csv i valori di confronto
csvfname=os.path.join(sDiagramsFolderPath,"bbt_%s_%s_%s.csv" % ( tun.replace (" ", "_") , replaceTBMName(tbmKey),sParameterToShow))
with open(csvfname, 'wb') as f:
writer = csv.writer(f,delimiter=";")
writer.writerow(('iterazione','fine','he','hp',sParameterToShow,'media','min95' ,'max95' ))
writer.writerows(outValues)
if bShowKPI:
print "%s %s" % (tun, tbmKey)
allTbmData += plotKPIS(cur,sDiagramsFolderPath,tun,tbmKey,tbmColors,bGroupTypes, sTypeToGroup, bPrintHist)
if bShowAllKpi:
allTbmData += plotTotalsKPIS(cur,sDiagramsFolderPath,tun,tbmKey,tbmColors,bGroupTypes, sTypeToGroup, bPrintHist)
if bShowDetailKPI:
allTbmData += plotDetailKPIS(cur,sDiagramsFolderPath,tun,tbmKey,tbmColors,bGroupTypes, sTypeToGroup, bPrintHist)
if len(allTbmData) > 0:
dictKPI = defaultdict(list)
dictDescr = {}
listToExport = []
for item in allTbmData:
key = item[0]
dictDescr[key] = item[-1]
dictKPI[key].append( item[1:-1] )
listToExport.append(item[:4])
# esposrto in csv i valori di confronto
csvfname=os.path.join(sDiagramsFolderPath,"bbt_%s_all_data.csv" % tun.replace (" ", "_") )
with open(csvfname, 'wb') as f:
writer = csv.writer(f,delimiter=";")
writer.writerow(('kpi','tbm','medie','sigma' ))
writer.writerows(listToExport)
for key in dictKPI:
keyDescr = dictDescr[key]
allTbmData = dictKPI[key]
fig = plt.figure(figsize=(22, 10), dpi=75)
ax = fig.add_subplot(111)
ax.yaxis.grid(True)
tbmNames = map(lambda y:y[0],allTbmData)
tbmMeans = map(lambda y:y[1],allTbmData)
tbmSigmas = map(lambda y:y[2],allTbmData)
tbmDatas = map(lambda y:y[3],allTbmData)
ax.set_xticks([y+1 for y in range(len(tbmDatas)) ])
ax.set_xlabel('TBMs')
ax.set_ylabel("%s - %s" % (key,keyDescr))
ax.set_title("%s, comparazione %s " % (tun,keyDescr))
xind = np.arange(len(tbmDatas))
plotColors =[]
tbmHiddenNames = []
for tk in tbmNames:
plotColors.append(tbmColors[tk])
tbmHiddenNames.append(replaceTBMName(tk))
if len(tbmDatas[0]) < 3:
#Stampa per quando len(tbmDatas) < 3
width = 0.35
plt.bar(xind, tbmMeans, width,color=plotColors, yerr=tbmSigmas)
plt.xticks(xind + width/2., tbmHiddenNames)
else:
try:
violin_parts = violinplot(tbmDatas,showmeans = True, points=50)
idx = 0
indMin = np.argmin(tbmMeans)
for vp in violin_parts['bodies']:
vp.set_facecolor(tbmColors[tbmNames[idx]])
vp.set_edgecolor(tbmColors[tbmNames[idx]])
vp.set_alpha(0.4)
if idx==indMin:
vp.set_edgecolor('y')
vp.set_linewidth(2)
idx +=1
plt.setp(ax, xticks=[y+1 for y in range(len(tbmDatas))],xticklabels=tbmHiddenNames)
except Exception as e:
print "Impossibile generare violin di %s per: %s" % ( key ,e)
width = 0.35
plt.bar(xind, tbmMeans, width,color=plotColors, yerr=tbmSigmas)
plt.xticks(xind + width/2., tbmHiddenNames)
outputFigure(sDiagramsFolderPath,"bbt_%s_%s_comp.svg" % (tun.replace (" ", "_") , key), format="svg")
plt.close(fig)
if bShowRadar:
plotRadarKPIS(cur,tunnelArray,sDiagramsFolderPath,tbmColors,bGroupTypes, sTypeToGroup)
conn.close()
def main(argv):
# mi metto nella directory corrente
path = os.path.dirname(os.path.realpath(__file__))
os.chdir(path)
########## File vari: DB
sDBName = bbtConfig.get("Database", "dbname")
sDBPath = os.path.join(os.path.abspath(".."), bbtConfig.get("Database", "dbfolder"), sDBName)
if not os.path.isfile(sDBPath):
print "Errore! File %s inesistente!" % sDBPath
exit(1)
load_tbm_table(sDBPath, tbms)
########### Outupt Folder
# sDiagramsFolder = bbtConfig.get('Diagrams','folder')
# sDiagramsFolderPath = os.path.join(os.path.abspath('..'), sDiagramsFolder)
# mi connetto al database
conn = getDBConnection(sDBPath)
# definisco il tipo di riga che vado a leggere, bbtparametereval_factory viene definita in bbtnamedtuples
conn.row_factory = sqlite3.Row
cur = conn.cursor()
# Legge tutti i Tunnell
sSql = """SELECT distinct
bbtTbmKpi.tunnelName
FROM
bbtTbmKpi
ORDER BY bbtTbmKpi.tunnelName"""
cur.execute(sSql)
bbtresults = cur.fetchall()
print "Sono presenti %d diverse Gallerie" % len(bbtresults)
tunnelArray = []
for bbtr in bbtresults:
tunnelArray.append(bbtr[0])
# definisco numero di iterazioni e numero di pk per ogni tunnel
sSql = "select max(BbtParameterEval.iteration_no) as max_iter from BbtParameterEval"
cur.execute(sSql)
bbtresult = cur.fetchone()
M = float(bbtresult[0]) + 1.0
pkCountDict = {}
for tun in tunnelArray:
sSql = (
"select count(distinct BbtParameterEval.fine) as pk_no from BbtParameterEval where BbtParameterEval.tunnelName='%s'"
% tun
)
cur.execute(sSql)
bbtresult = cur.fetchone()
pkNo = float(bbtresult[0])
pkCountDict[tun] = pkNo
# Legge tutte le TBM disponibili in tbmConfig per creare i dizionari di thrustLim e torqueLim
thrustLim = {}
torqueLim = {}
for tbmName in tbms:
tbm = tbms[tbmName]
thrustLim[tbm.name] = tbm.auxiliaryThrustForce
torqueLim[tbm.name] = tbm.breakawayTorque
# carico tutti i segmenti ordinati dove posso avere blocco scudo o testa
sSql = """SELECT BbtParameterEval.tunnelName, BbtParameterEval.tbmName, BbtParameterEval.fine, count(*) as cnt
FROM BbtParameterEval
JOIN BbtTbm on BbtTbm.name = BbtParameterEval.tbmName
WHERE BbtParameterEval.tbmName !='XXX'
AND (
(BbtParameterEval.tbmName LIKE 'CE%' and BbtParameterEval.availableThrust< 3000) OR (BbtParameterEval.tbmName LIKE 'GL%' and BbtParameterEval.availableThrust< 5000)
OR
(BbtParameterEval.torque> BbtTbm.breakawayTorque)
)
GROUP BY BbtParameterEval.tunnelName, BbtParameterEval.tbmName, BbtParameterEval.fine
ORDER BY BbtParameterEval.tunnelName, BbtParameterEval.tbmName ASC, cnt DESC"""
cur.execute(sSql)
bbtresults = cur.fetchall()
# definisco l'ordine di priorita' delle pk consolidabili per ogni cunicolo e tbm (ove posso applicare i consolidamenti)
tunnelRef = ""
tbmRef = ""
pkOrder = {}
pos = 0
overallBlockArray = []
overallBlockArray.append(
(
"Tunnel",
"TBM",
"PK",
"sim x segm",
"tot segmenti",
"sim tot tunnel",
"no blocchi",
"no blocchi/sim tot tunnel",
"no blocchi/sim segmento",
"no blocchi dc",
"no blocchi dc/sim tot tunnel",
"no blocchi dc/sim segmento",
)
)
for res in bbtresults:
tunnel = res[0]
tbm = res[1]
pk = str(res[2])
if tunnel == tunnelRef and tbm == tbmRef:
pos += 1
else:
pos = 0
tunnelRef = tunnel
tbmRef = tbm
pkOrder[tunnel + tbm + pk] = pos
impRid = res[3]
if res[0] == "Cunicolo esplorativo direzione Nord" and pos < 48:
impRid = 0
elif res[0] == "Galleria di linea direzione Nord" and pos < 38:
impRid = 0
overallBlockArray.append(
(
res[0],
res[1],
res[2],
M,
pkCountDict[res[0]],
M * pkCountDict[res[0]],
res[3],
res[3] / M / pkCountDict[res[0]],
res[3] / M,
impRid,
impRid / M / pkCountDict[res[0]],
impRid / M,
)
)
# carico in memoria tutti i record di BbtParmetersEval dove posso avere blocco scudo
sSql = """SELECT BbtParameterEval.tunnelName, BbtParameterEval.tbmName, BbtParameterEval.fine, count(*) as cnt
FROM BbtParameterEval
JOIN BbtTbm on BbtTbm.name = BbtParameterEval.tbmName
WHERE BbtParameterEval.tbmName !='XXX'
AND (
(BbtParameterEval.tbmName LIKE 'CE%' and BbtParameterEval.availableThrust< 3000) OR (BbtParameterEval.tbmName LIKE 'GL%' and BbtParameterEval.availableThrust< 5000)
)
GROUP BY BbtParameterEval.tunnelName, BbtParameterEval.tbmName, BbtParameterEval.fine
ORDER BY BbtParameterEval.tunnelName, BbtParameterEval.tbmName ASC, cnt DESC"""
cur.execute(sSql)
bbtresults = cur.fetchall()
shieldBlockArray = []
shieldBlockArray.append(
(
"Tunnel",
"TBM",
"PK",
"sim x segm",
"tot segmenti",
"sim tot tunnel",
"no blocchi",
"no blocchi/sim tot tunnel",
"no blocchi/sim segmento",
"no blocchi dc",
"no blocchi dc/sim tot tunnel",
"no blocchi dc/sim segmento",
)
)
for res in bbtresults:
pos = pkOrder[res[0] + res[1] + str(res[2])]
impRid = res[3]
if res[0] == "Cunicolo esplorativo direzione Nord" and pos < 48:
impRid = 0
elif res[0] == "Galleria di linea direzione Nord" and pos < 38:
impRid = 0
shieldBlockArray.append(
(
res[0],
res[1],
res[2],
M,
pkCountDict[res[0]],
M * pkCountDict[res[0]],
res[3],
res[3] / M / pkCountDict[res[0]],
res[3] / M,
impRid,
impRid / M / pkCountDict[res[0]],
impRid / M,
)
)
# carico in memoria tutti i record di BbtParmetersEval dove posso avere blocco testa
sSql = """SELECT BbtParameterEval.tunnelName, BbtParameterEval.tbmName, BbtParameterEval.fine, count(*) as cnt
FROM BbtParameterEval
JOIN BbtTbm on BbtTbm.name = BbtParameterEval.tbmName
WHERE BbtParameterEval.tbmName !='XXX'
AND (
(BbtParameterEval.torque> BbtTbm.breakawayTorque)
)
GROUP BY BbtParameterEval.tunnelName, BbtParameterEval.tbmName, BbtParameterEval.fine
ORDER BY BbtParameterEval.tunnelName, BbtParameterEval.tbmName ASC, cnt DESC"""
cur.execute(sSql)
bbtresults = cur.fetchall()
frontBlockArray = []
frontBlockArray.append(
(
"Tunnel",
"TBM",
"PK",
"sim x segm",
"tot segmenti",
"sim tot tunnel",
"no blocchi",
"no blocchi/sim tot tunnel",
"no blocchi/sim segmento",
"no blocchi dc",
"no blocchi dc/sim tot tunnel",
"no blocchi dc/sim segmento",
)
)
for res in bbtresults:
pos = pkOrder[res[0] + res[1] + str(res[2])]
impRid = res[3]
if res[0] == "Cunicolo esplorativo direzione Nord" and pos < 48:
impRid = 0
elif res[0] == "Galleria di linea direzione Nord" and pos < 38:
impRid = 0
frontBlockArray.append(
(
res[0],
res[1],
res[2],
M,
pkCountDict[res[0]],
M * pkCountDict[res[0]],
res[3],
res[3] / M / pkCountDict[res[0]],
res[3] / M,
impRid,
impRid / M / pkCountDict[res[0]],
impRid / M,
)
)
## # interrogo DB per vedere dove il torque e' maggiore di quello di base richiesto
## sSql = """SELECT BbtParameterEval.tunnelName, BbtParameterEval.tbmName, BbtParameterEval.fine, BbtParameterEval.torque
## FROM BbtParameterEval
## WHERE BbtParameterEval.tbmName !='XXX'
## AND ((BbtParameterEval.tbmName LIKE 'CE%' and BbtParameterEval.torque> 7360) OR (BbtParameterEval.tbmName LIKE 'GL%' and BbtParameterEval.torque> 15900))
## ORDER BY BbtParameterEval.tunnelName, BbtParameterEval.tbmName ASC, BbtParameterEval.fine"""
## cur.execute(sSql)
## bbtresults = cur.fetchall()
## frontBlockArray = []
## frontBlockArray.append(('Tunnel', 'TBM', 'PK', 'no blocchi', 'sim x segm', 'tot segmenti', 'sim tot tunnel', 'no blocchi/sim tot tunnel', 'no blocchi/sim segmento'))
## tunnelRef=''
## tbmRef=''
## pkRef=0
## cntOut = 0
## resCnt = len(bbtresults)
## for i in range(0, resCnt-1):
## res=bbtresults[i]
## tunnel = res[0]
## tbm = res[1]
## pk = res[2]
## toAdd = res[3]>torqueLim[tbm]
## if tunnel == tunnelRef and tbm == tbmRef and pk == pkRef:
## # e' un altro punto da aggiungere
## # aggiurno la somma se toAdd
## if toAdd:
## cntOut+=1
## else:
## # ho iniziato un nuovo record
## # se non e' il primo appendo il risultato ottenuto
## if i>0 and cntOut>0:
## frontBlockArray.append((tunnel, tbm, pk, cntOut, M, pkCountDict[res[0]], M*pkCountDict[res[0]], cntOut/M/pkCountDict[res[0]], cntOut/M))
## # azzero i conteggi e i riferimenti
## tunnelRef=tunnel
## tbmRef=tbm
## pkRef=pk
## cntOut=0
## # aggiurno la somma se toAdd
## if toAdd:
## cntOut+=1
## # se e' l'ultima iterazione aggiungo il risultato
## if i == resCnt-1 and cntOut>0:
## frontBlockArray.append((tunnel, tbm, pk, cntOut, M, pkCountDict[res[0]], M*pkCountDict[res[0]], cntOut/M/pkCountDict[res[0]], cntOut/M))
conn.close()
# esposrto in csv
with open("bloccoFronte.csv", "wb") as f:
writer = csv.writer(f, delimiter=",")
writer.writerows(frontBlockArray)
with open("bloccoScudo.csv", "wb") as f:
writer = csv.writer(f, delimiter=",")
writer.writerows(shieldBlockArray)
with open("blocco.csv", "wb") as f:
writer = csv.writer(f, delimiter=",")
writer.writerows(overallBlockArray)