133 lines
5.1 KiB
Python
133 lines
5.1 KiB
Python
import pandas as pd
|
|
import os
|
|
import glob
|
|
import cx_Oracle
|
|
from datetime import date
|
|
import os
|
|
|
|
|
|
def init():
|
|
global df
|
|
|
|
#list_of_files = glob.glob('*.xlsx') # * means all if need specific format then *.csv
|
|
latest_file = max(glob.iglob('*.xlsx'),key=os.path.getctime)
|
|
|
|
if latest_file.startswith("~$"):
|
|
latest_file=latest_file.replace("~$", "")
|
|
|
|
print(latest_file)
|
|
df = pd.read_excel(latest_file, engine="openpyxl", sheet_name="daily")
|
|
|
|
if df.size > 0:
|
|
createHtmlSummary(df)
|
|
write_oracle_db(df)
|
|
|
|
|
|
def write_oracle_db(df):
|
|
dsnStr = cx_Oracle.makedsn("plapexgma01.bmwgroup.net", "1534", "apex5p")
|
|
#print(dsnStr)
|
|
db_connection_string='username/passwort@hostname.de.oracle.com:1521/servicename'
|
|
con = cx_Oracle.connect(user="b2vadmin",password="B2V@aDmIn7", dsn=dsnStr)
|
|
|
|
try:
|
|
with con.cursor() as cursor:
|
|
|
|
|
|
sql = 'INSERT INTO RELIABILITY_DAILY VALUES (:1, :2,:3,:4, :5, :6, :7)'
|
|
|
|
#daily_value = (str(datetime.now().replace(microsecond=0)), 'emea', '3434','mobile', 97.43,'warning')
|
|
daily_value = ('emea')
|
|
df_list = df.values.tolist()
|
|
# print(str(s1[0]))
|
|
#cursor.execute('''INSERT INTO RELIABILITY_DAILY_TEST values ('emea')''')
|
|
cursor.execute(sql, ( today, df_list[0][0], "efef" , "efef", "efef", 2, "efef"))
|
|
# commit work
|
|
con.commit()
|
|
|
|
|
|
|
|
except cx_Oracle.Error as error:
|
|
print('Error occurred:')
|
|
print(error)
|
|
|
|
|
|
print("Database version:", con.version)
|
|
con.close()
|
|
|
|
|
|
|
|
def createHtmlSummary(df):
|
|
|
|
#df = self.totalall
|
|
|
|
df=df.sort_values(by=['HUB'])
|
|
unique_hub=df["HUB"].unique()
|
|
|
|
SummaryString='<html><body><style> table, th, td { border: 1px solid black; border-collapse: collapse; padding: 10px; text-align: center; }</style><br><p><b>Processed SLO count: '+ str(len(df)) +'</b></p><table><tr><th></th>'
|
|
|
|
|
|
#Adding colum headers for ever hub
|
|
for hub in unique_hub:
|
|
SummaryString+="<th>"+hub+"</th>"
|
|
|
|
SummaryString+="<th>total</th>"
|
|
|
|
|
|
####################################
|
|
####### Between 99 and 100 ##########
|
|
####################################
|
|
SummaryString+='</tr><tr><td style="background-color:Green;">>= 99</td>'
|
|
|
|
tmp99 = df[(df.evaluatedPercentage >=99) & (df.evaluatedPercentage <= 100)].groupby("HUB")["evaluatedPercentage"].count().reset_index()
|
|
|
|
for hub in unique_hub:
|
|
#for index, row in tmp99.iterrows():
|
|
SummaryString+='<td>{0}</td>'.format(tmp99[(tmp99.HUB == hub)]["evaluatedPercentage"].values[0] if tmp99[(tmp99.HUB == hub)].size != 0 else 0)
|
|
|
|
SummaryString+='<td><b>{0}</b></td>'.format(len(df[(df.evaluatedPercentage >=99) & (df.evaluatedPercentage <= 100)]))
|
|
####################################
|
|
####### Between 98 and 99 ##########
|
|
####################################
|
|
SummaryString+='</tr><tr><td style="background-color:Orange;">>= 98</td>'
|
|
|
|
#if tmp98.size != 0:
|
|
tmp98 = df[(df.evaluatedPercentage >=98) & (df.evaluatedPercentage < 99)].groupby("HUB")["evaluatedPercentage"].count().reset_index()
|
|
|
|
for hub in unique_hub:
|
|
SummaryString+='<td>{0}</td>'.format(tmp98[(tmp98.HUB == hub)]["evaluatedPercentage"].values[0] if tmp98[(tmp98.HUB == hub)].size != 0 else 0)
|
|
|
|
SummaryString+='<td><b>{0}</b></td>'.format(len(df[(df.evaluatedPercentage >=98) & (df.evaluatedPercentage < 99)]))
|
|
|
|
####################################
|
|
####### Between 0 and 98 ###########
|
|
####################################
|
|
SummaryString+='</tr><tr><td style="background-color:Red;">< 98</td>'
|
|
|
|
tmp0 = df[(df.evaluatedPercentage >=0) & (df.evaluatedPercentage < 98)].groupby("HUB")["evaluatedPercentage"].count().reset_index()
|
|
|
|
for hub in unique_hub:
|
|
SummaryString+='<td>{0}</td>'.format(tmp0[(tmp0.HUB == hub)]["evaluatedPercentage"].values[0] if tmp0[(tmp0.HUB == hub)].size != 0 else 0)
|
|
|
|
SummaryString+='<td><b>{0}</b></td>'.format(len(df[(df.evaluatedPercentage >=0) & (df.evaluatedPercentage < 98)]))
|
|
|
|
####################################
|
|
####### -1 --> N/A ###########
|
|
####################################
|
|
SummaryString+='</tr><tr><td style="background-color:DodgerBlue;">-1</td>'
|
|
|
|
tmpNA = df[(df.evaluatedPercentage == -1)].groupby("HUB")["evaluatedPercentage"].count().reset_index()
|
|
|
|
for hub in unique_hub:
|
|
SummaryString+='<td>{0}</td>'.format(tmpNA[(tmpNA.HUB == hub)]["evaluatedPercentage"].values[0] if tmpNA[(tmpNA.HUB == hub)].size != 0 else 0)
|
|
|
|
SummaryString+='<td><b>{0}</b></td>'.format(len(df[(df.evaluatedPercentage == -1)]))
|
|
|
|
SummaryString+="</tr></table></body></html>"
|
|
|
|
|
|
with open('summary.txt', 'w') as f:
|
|
f.write(SummaryString)
|
|
|
|
if __name__ == "__main__":
|
|
init()
|