def Analysis(self, dfTriage):
self.findErrorStrings(dfTriage)
self.Billing = self.Billing.fillna('')
py.saveExcel(self.listOfConfigFile[2] + "\\BillingTriage.xlsx",
"Sheet1", self.Billing)
return self.Billing
def FindAgeSubmitDate(self) :
i = 0
for each in self.dfMain['Order Number'] :
self.dfMain.at[i,'AgeSubmitDate'] = (datetime.datetime.now() -self.dfMain.at[i,'Order Submit Date']).days
self.dfMain.at[i,'AgeSubmitDateCategory'] = self.AssignAgeCategory(self.dfMain.at[i,'AgeSubmitDate'] )
i = i+1
cols = ['Order Number','Fl Commited Date','BB Commited Date','Edge Committed Date','SI committed date','edge committed date_M','SI Committed date_M','tempDate','AgeCCDCategory','AgeSubmitDate','AgeCCD','AgeSubmitDateCategory','Order Submit Date']
py.saveExcel(self.DefaultPath+"FormattedFLBBDasboard1.xlsx","Sheet1",self.dfMain)
self.dfMain = self.dfMain[cols]
py.saveExcel(self.DefaultPath+"FormattedFLBBDasboardcheckAge.xlsx","Sheet1",self.dfMain)
def __init__(self):
dfMain = pd.read_excel(
"C:\\Users\\prerna.prakash\\Desktop\\Input\\FL44\\FormattedFLBBDasboard.xlsx"
)
dfMain['Fl Commited Date'] = pd.to_datetime(dfMain['Fl Commited Date'],
dayfirst=True)
dfMain['BB Commited Date'] = pd.to_datetime(dfMain['BB Commited Date'],
dayfirst=True)
dfMain['Edge Committed Date'] = pd.to_datetime(
dfMain['Edge Committed Date'], dayfirst=True)
dfMain['SI committed date'] = pd.to_datetime(
dfMain['SI committed date'], dayfirst=True)
cols = [
'Order Number', 'Fl Commited Date', 'BB Commited Date',
'Edge Committed Date', 'SI committed date'
]
now = datetime.datetime.now()
dfMain = dfMain[cols]
print(now)
i = 0
templist = []
for each in dfMain['Order Number']:
if (dfMain.at[i, 'Fl Commited Date'] - now).days < 0:
templist.append('Backlog')
elif (dfMain.at[i, 'BB Commited Date'] - now).days < 0:
templist.append('Backlog')
elif (dfMain.at[i, 'Edge Committed Date'] - now).days < 0:
templist.append('Backlog')
elif (dfMain.at[i, 'SI committed date'] - now).days < 0:
templist.append('Backlog')
elif (dfMain.at[i, 'Fl Commited Date'] - now).days >= 0:
templist.append('Amber')
elif (dfMain.at[i, 'BB Commited Date'] - now).days >= 0:
templist.append('Amber')
elif (dfMain.at[i, 'Edge Committed Date'] - now).days >= 0:
templist.append('Amber')
elif (dfMain.at[i, 'SI committed date'] - now).days >= 0:
templist.append('Amber')
else:
templist.append('NOCCD')
i = i + 1
dfMain['Date'] = templist
py.saveExcel(
"C:\\Users\\prerna.prakash\\Desktop\\Input\\FL303\\FormattedFLBBDasboard1.xlsx",
'Sheet1', dfMain)
def saveCount(self):
cwd = os.getcwd()
TeamDict = rm.extractRelevantFromDf(self.mainreport, 'TEAM')
TeamDict['DateTime'] = self.reportTime
dfTeam = pd.DataFrame(data=TeamDict, index=[0])
dfsaveit = pd.read_excel(cwd + "\\HistoricData.xlsx", "Sheet1")
dfsaveit = pd.concat([pd.DataFrame(dfTeam), dfsaveit],
ignore_index=True)
py.saveExcel(cwd + "\\HistoricData.xlsx", "Sheet1", dfsaveit)
TeamDict = rm.extractRelevantFromDf(self.mainreport, 'LAST_TASK')
TeamDict['DateTime'] = self.reportTime
dfTeam = pd.DataFrame(data=TeamDict, index=[0])
dfsaveit = pd.read_excel(cwd + "\\HistoricDataTask.xlsx", "Sheet1")
dfsaveit = pd.concat([pd.DataFrame(dfTeam), dfsaveit],
ignore_index=True)
py.saveExcel(cwd + "\\HistoricDataTask.xlsx", "Sheet1", dfsaveit)
def makeTriage(self):
cols = [
'SIEBEL_NUM', 'CTN', 'ACCOUNT_NUMBER', 'LAST_TASK', 'CHANNEL',
'SHIPPING', 'ORDER_TYPE', 'ORDERTYPECODE', 'ORDER TYPECODE2',
'COLLECTED_DATE', 'ORDER_STATUS', 'SUBMIT_DATE', 'PAYMENT_TYPE',
'PORTDATE', 'SUBMIT_DATE_ONLY', 'ORDER_DATE_ONLY', 'DAYS_OPEN',
'AGE', 'REFERENCE_NUMBER', 'ORDER_SEQ_ID', 'CARTRIDGE_ID',
'NEXT_ACTION', 'CATEGORY', 'TASK_START_TIME', 'ERROR_MESSAGE1',
'ERROR_MESSAGE2', 'TASK_DESCRIPTION', 'TXN_REASON', 'PAC_CODE',
'DONOR_NO', 'DONOR_SP', 'RECIPIENT_NO', 'RECIPIENT_SP', 'DIVISION',
'PREVIOUSNUM', 'SHIPPINGDATE', 'PORTING_SLA', 'SHIPPING_SLA',
'ATTRIB_55', 'ATTRIB_60', 'IMSI', 'Iphone Order', 'OLD/NEW OSM',
'COLLECT_TO_STORE/DELIVER_TO_STORE', 'PRIORITY', 'NEW_SIM',
'OLD_SIM', 'Unnamed: 47', 'TEAM', 'Age', 'AgeTemp', 'AgeReq',
'Priority', 'ANALYSIS', 'ISSUE', 'LOCATION', 'Next Task'
]
files = os.listdir(self.listOfConfigFile[2])
dftemp1 = pd.read_excel(self.listOfConfigFile[4] + "\\Analysis.xlsx",
'Sheet1')
cwd = os.getcwd()
print("Files in '%s': %s" % (cwd, files))
files_xlsx = [f for f in files if f[-4:] == 'xlsx']
df = pd.DataFrame()
for f in files_xlsx:
dftemp = pd.read_excel(self.listOfConfigFile[2] + "\\" + f)
dftemp = dftemp[cols]
df = df.append(dftemp)
df = df.reset_index(drop=True)
i = 0
for each in df['ANALYSIS']:
j = 0
for feach in dftemp1['ANALYSIS']:
feach = str(feach)
each = str(each)
if feach in each:
df.at[i, 'DeeDee'] = str(dftemp1.at[j, 'DD'])
j = j + 1
i = i + 1
#df =py.DD(df,self.listOfConfigFile[0])
py.saveExcel(self.listOfConfigFile[4] + "\\ARTAnalysis.xlsx", "Sheet1",
df)
def __init__(self):
self.DateToday = datetime.date.today()
self.month = str(self.DateToday.month)
self.day = str(self.DateToday.day)
self.DefaultPath = "C:\\Users\\prerna.prakash\\Desktop\\Input\\FL" + self.day + self.month + "\\"
if os.path.exists(self.DefaultPath + "SI.csv") == 'False':
return False
else:
cols = [
'Case Reference', 'Created At', 'Current Order Status',
'Osf Order Reference Buyers Id', 'Committed Date',
'Sub Order Type'
]
dfSI = pd.read_csv(self.DefaultPath + "SI.csv",
usecols=cols,
low_memory=True,
error_bad_lines=True)
dfSI[['Order Number',
'AID']] = dfSI['Case Reference'].str.split('_',
n=1,
expand=True)
py.saveExcel(self.DefaultPath + "FormattedSI3.xlsx", 'Sheet1',
dfSI)
print("Columns in SI", len(dfSI.index))
dfSI['Created At'] = pd.to_datetime(dfSI['Created At'],
errors='ignore',
infer_datetime_format=True,
yearfirst=True,
exact=False)
py.saveExcel(self.DefaultPath + "FormattedSI2.xlsx", 'Sheet1',
dfSI)
print("Columns in SI", len(dfSI.index))
#dfSI = dfSI.sort_values(by=['Created At'],ascending = True)
dfSI = dfSI.sort_values(by=['Created At'], ascending=False)
py.saveExcel(self.DefaultPath + "FormattedSI1.xlsx", 'Sheet1',
dfSI)
dfSI.drop_duplicates(
'Order Number',
keep='first',
).reset_index(drop=True)
print("Columns in SI", len(dfSI.index))
#dfSI.drop_duplicates('Order Number', keep='last',).reset_index(drop=True)
print("Columns in SI", len(dfSI.index))
dfSI.rename(columns={
'Osf Order Reference Buyers Id': 'SI sales orderid_M',
'Current Order Status': 'SI status_M',
'Committed Date': 'SI Committed date_M',
'Sub Order Type': 'SI sub order type_M'
},
inplace=True)
py.saveExcel(self.DefaultPath + "FormattedSI.xlsx", 'Sheet1', dfSI)
def makeReport(self):
dfTeamMap = pd.read_excel(self.listOfConfigFile[0], 'Sheet1')
self.dfMainReport = self.dfMainReport.reset_index(drop=True)
p = pr.prep()
p.addLocation(self.dfMainReport)
p.NextTaskExpected(self.dfMainReport)
self.dfMainReport = p.change(self.dfMainReport)
# py.saveExcel(self.listOfConfigFile[2],"Sheet1",self.dfMainReport)
py.saveExcel(
"C:\\Users\\prerna.prakash\\Desktop\\Input\\checkthis.xlsx",
"Sheet1", self.dfMainReport)
self.dfMainReport = py.vlookupin(self.dfMainReport, dfTeamMap,
'LAST_TASK', 'TASK', ['TEAM'])
MA = ma.mobileAge(self.dfMainReport)
self.dfMainReport = MA.Main()
self.makeTriageSheet()
self.makeTriage()
def __init__(self):
self.DefaultPath = fm.getDefaultPath()
if os.path.exists(self.DefaultPath+"Consumer.xlsx") == 'False' :
return False
else :
dfCONSUMER = pd.read_excel(self.DefaultPath+"Consumer.xlsx",sheetname = 'All Orders')
dfCONSUMER[['Order Number','AID']] = dfCONSUMER['Customer Order Refer'].str.split('_',n=1,expand=True)
col = ['Order Number','ORRef',"PSTN Status","SalesOrderRef","Status"]
dfCONSUMER = dfCONSUMER[col]
dfCONSUMER.rename(columns={'ORRef': 'Consumer SI sales orderid_M','PSTN Status':'Consumer SI status_M','SalesOrderRef':'Consumer EDGE SalesOrderID_M','STATUS_CODE':'edge status code_m','STATUS_MESSAGE':'edge status message_m','STATUS_TYPE':'edge status_type_m','Status':'Consumer EDGE status_M'}, inplace=True)
py.saveExcel(self.DefaultPath+"FormattedConsumer.xlsx",'Sheet1',dfCONSUMER)
#check consumer columns
# -*- coding: utf-8 -*-
"""
Created on Mon May 7 19:07:30 2018
@author: prerna.prakash
"""
import pycel as py
import pandas as pd
import FLModule as FM
import FLnoCCD as CCD
import time
import CancellationAnalysis as ca
strtime = time.time()
df = pd.read_excel("C:\\Users\\prerna.prakash\\Desktop\\Input\\FL3010\\FormattedFLBBDasboardFinal.xlsx")
#cols = ['Order Number','Fl Commited Date','BB Commited Date','edge committed date_M','SI Committed date_M']
#df = df[cols]
#templist = FM.category(df)
#df['category'] = templist
cancellationAnalysis = ca.cancellationAnalysis(df)
df = cancellationAnalysis.analysis()
py.saveExcel("C:\\Users\\prerna.prakash\\Desktop\\Input\\FL3010\\FormattedFLBBDasboardcheck1.xlsx","Sheet1",df)
endtime = time.time()
print('time taken is', endtime-strtime)
def Analysis(self, dfTriage):
self.UIM = self.defineAnalysis(dfTriage)
py.saveExcel(self.listOfConfigFile[4] + "\\UIMTriage.xlsx", "Sheet1",
self.UIM)
return self.UIM
dfEDGE = ed.EDGE()
dfSI = si.SI()
#dfCONSUMER = cm.CONSUMER()
dfSR = sr.SR()
df = pd.read_excel(DefaultPath+"Siebel Report.xlsx")
df.columns = cols
dfEDGE = pd.read_excel(DefaultPath+"FormattedEdge.xlsx")
dfSI= pd.read_excel(DefaultPath+"FormattedSI.xlsx")
#dfCONSUMER= pd.read_excel(DefaultPath+"FormattedConsumer.xlsx")
dfSR= pd.read_excel(DefaultPath+"SRFormatted.xlsx")
df = df.merge(dfEDGE,how='left', on='Order Number')
df = df.merge(dfSI,how='left', on='Order Number')
#df = df.merge(dfCONSUMER,how='left', on='Order Number')
df = df.merge(dfSR,how='left', on='Order Number')
py.saveExcel(DefaultPath+"FormattedFLBBDasboard.xlsx",'Sheet1',df)
endtime = time.time()
print("FLBB Dashboard created with SI,EDGE and SR DATA",strtime-endtime)
templist = fm.CategoryforSiebelReport(df)
df['Category'] = templist
EnS = ES.EDGEnSI()
df =EnS.defineAnalysisEDGE(df)
FLAging = fa.FLAging(df)
endtime = time.time()
print("Age Added",endtime-strtime)
print("Time taken is:",endtime-strtime)
df = pd.read_excel(DefaultPath+"FormattedFLBBDasboard1.xlsx")
FLnoCCD = CCD.FLnoCCD()
dfEDGEID = pd.read_excel("C:\\Users\\prerna.prakash\\Downloads\\new\\CCD\\Main\\EDGEID.xlsx")
Failed= pd.read_excel("C:\\Users\\prerna.prakash\\Downloads\\new\\CCD\\Main\\Failed.xlsx")
def __init__(self):
self.DateToday = date.today()
yesterday = date.today() - timedelta(1)
print(yesterday)
yesterday = date.today() - timedelta(1)
print(type(yesterday))
print(yesterday.day)
print(yesterday.month)
self.DefaultPath = 'C:\\Users\\prerna.prakash\\Desktop\\Input'
self.month = str(self.DateToday.month)
self.day = str(self.DateToday.day)
while True:
if os.path.exists(self.DefaultPath + "\\FL" + str(yesterday.day) +
str(yesterday.month)) == True:
break
else:
yesterday = yesterday - timedelta(1)
print(self.DefaultPath + "\\FL" + str(yesterday.day) +
str(yesterday.month))
self.DefaultPath = "C:\\Users\\prerna.prakash\\Desktop\\Input\\FL" + self.day + self.month + "\\"
if os.path.exists(self.DefaultPath + "FLSR.csv") == 'False':
return False
elif os.path.exists(self.DefaultPath + "FLSR.xlsx"):
names = ['Order Number', 'Date', 'AID', 'AID2', 'STATUS', 'REASON']
#,'a','a','a','a','a','a','a','a','a','a','a','a','a','a','a']
#,'a','a','a','a','a','a'
dfSR = pd.read_excel(self.DefaultPath + "FLSR.xlsx",
header=None,
names=names,
parse_cols="A:F")
dfSR = dfSR.iloc[:, 0:6]
SRcol = ['Order Number', 'Date', "AID", "STATUS", "REASON"]
dfSR = dfSR[SRcol]
dfSR['Date'] = pd.to_datetime(dfSR['Date'],
format='%d-%m-%Y %H:%M:%S',
yearfirst=True)
dfSR = dfSR.sort_values(by=['Date'], ascending=False)
dfSR = dfSR.drop_duplicates(['Order Number'
]).reset_index(drop='True')
dfSR.rename(columns={
'AID': 'SR_NUM',
'STATUS': 'SR TITLE',
'REASON': 'SR DESC'
},
inplace=True)
py.saveExcel(self.DefaultPath + "SRFormatted.xlsx", 'Sheet1', dfSR)
else:
names = ['Order Number', 'Date', 'AID', 'AID2', 'STATUS', 'REASON']
dfSR = pd.read_csv(self.DefaultPath + "FLSR.csv",
header=None,
names=names,
low_memory=False,
error_bad_lines=True)
dfSR = dfSR.iloc[:, 0:6]
SRcol = ['Order Number', 'Date', "AID", "STATUS", "REASON"]
dfSR = dfSR[SRcol]
dfSR['Date'] = pd.to_datetime(dfSR['Date'], yearfirst=True)
dfSR = dfSR.sort_values(by=['Date'], ascending=False)
dfSR = dfSR.drop_duplicates(['Order Number'
]).reset_index(drop='True')
dfSR.rename(columns={
'AID': 'SR_NUM',
'STATUS': 'SR TITLE',
'REASON': 'SR DESC'
},
inplace=True)
py.saveExcel(self.DefaultPath + "SRFormatted.xlsx", 'Sheet1', dfSR)
# -*- coding: utf-8 -*-
"""
Created on Tue Aug 21 20:10:39 2018
@author: prerna.prakash
"""
import pandas as pd
import Tracker as tk
import pycel as py
df = pd.read_excel(
'C:\\Users\\prerna.prakash\\Desktop\\Input\\FL228\\FormattedFLBBDasboard.xlsx'
)
Tracker = tk.Tracker()
df = Tracker.Apply(df)
py.saveExcel(
'C:\\Users\\prerna.prakash\\Desktop\\Input\\FL218\\FormattedFLBBDasboarddd.xlsx',
'Sheet1', df)
def appendToMainFile(self, name):
if 'EDGEID' in name:
dfCCDEDGEID = pd.read_excel(
'C:\\Users\\prerna.prakash\\Desktop\\FL\\Input\\CCD\Main\\EDGEID.xlsx'
)
dfEDGEID = pd.read_csv(name,
low_memory=False,
error_bad_lines=False)
dfCCDEDGEID = dfCCDEDGEID.append(dfEDGEID)
print('Appending EDGEID')
dfCCDEDGEID = dfCCDEDGEID.drop_duplicates(
["OrderID"],
keep='last',
).reset_index(drop=True)
py.saveExcel(
'C:\\Users\\prerna.prakash\\Desktop\\FL\\Input\\CCD\Main\\EDGEID.xlsx',
'Sheet1', dfCCDEDGEID)
if 'SIID' in name:
dfCCDEDGEID = pd.read_excel(
'C:\\Users\\prerna.prakash\\Desktop\\FL\\Input\\CCD\\Main\\SIID.xlsx'
)
dfEDGEID = pd.read_csv(name,
low_memory=False,
error_bad_lines=False)
dfCCDEDGEID = dfCCDEDGEID.append(dfEDGEID)
print('Appending SIID')
dfCCDEDGEID = dfCCDEDGEID.drop_duplicates(
["OrderID"],
keep='last',
).reset_index(drop=True)
py.saveExcel(
'C:\\Users\\prerna.prakash\\Desktop\\FL\\Input\\CCD\\Main\\SIID.xlsx',
'Sheet1', dfCCDEDGEID)
if 'WithoutID' in name:
dfCCDEDGEID = pd.read_excel(
'C:\\Users\\prerna.prakash\\Desktop\\FL\\Input\\CCD\\Main\\WithoutID.xlsx'
)
dfEDGEID = pd.read_csv(name,
low_memory=False,
error_bad_lines=False)
cols = ['Success Order without ID']
dfEDGEID = dfEDGEID[cols]
dfCCDEDGEID = dfCCDEDGEID[cols]
dfCCDEDGEID = dfCCDEDGEID.append(dfEDGEID)
print('Appending WithoutID')
dfCCDEDGEID = dfCCDEDGEID.drop_duplicates(
["Success Order without ID"],
keep='first',
).reset_index(drop=True)
dfCCDEDGEID['temp'] = 'WithoutID'
py.saveExcel(
'C:\\Users\\prerna.prakash\\Desktop\\FL\\Input\\CCD\\Main\\WithoutID.xlsx',
'Sheet1', dfCCDEDGEID)
if 'Failed' in name:
dfCCDEDGEID = pd.read_excel(
'C:\\Users\\prerna.prakash\\Desktop\\FL\\Input\\CCD\Main\\Failed.xlsx'
)
dfEDGEID = pd.read_csv(name,
low_memory=False,
error_bad_lines=False)
dfCCDEDGEID = dfCCDEDGEID.append(dfEDGEID)
print('Appending Failed')
dfCCDEDGEID = dfCCDEDGEID.drop_duplicates(
["OrderID"],
keep='last',
).reset_index(drop=True)
py.saveExcel(
'C:\\Users\\prerna.prakash\\Desktop\\FL\\Input\\CCD\Main\\Failed.xlsx',
'Sheet1', dfCCDEDGEID)
