def process_errors():
clogged = False
df = pd.DataFrame()
for dirpath in settings['UpdatesDir']:
df = get_errors(os.path.basename(dirpath))
if not df.empty:
if not clogged:
writelog('Vacuum clogged with Errors. Cleaning vacuum...',
'error')
clogged = True
for serial in df['Comp_Serial'].unique():
writelog(
'{0} error(s) appended to {1}'
's workbook'.format(
len(df[df['Comp_Serial'] == serial].index), serial),
'warning')
if not os.path.exists(settings['ErrorsDir'] +
'//{}'.format(serial)):
os.makedirs(settings['ErrorsDir'] + '//{}'.format(serial))
myobj = XMLAppendClass(
settings['ErrorsDir'] + '//{0}//{1}_E{2}.xml'.format(
serial,
datetime.datetime.now().__format__("%Y%m%d"),
random.randint(10000000, 10000000000)))
myobj.write_xml(df[df['Comp_Serial'] == serial])
del df
def check_for_updates():
for dirpath in settings['UpdatesDir']:
if os.path.basename(dirpath) == '05_New-User':
files = list(pl.Path(dirpath).glob('*.sql'))
else:
files = list(pl.Path(dirpath).glob('*.xml'))
if files:
writelog("Vacuum found crumbs... Switching to max suction", 'info')
return files
def process(self):
if self.asql:
if self.action == 'Escalate':
self.escalate()
elif self.action == 'Close':
self.close()
elif self.action == 'Paid':
validatecol(self.asql, 'mydisputes', 'Amount_Or_Days')
validatecol(self.asql, 'mydisputes', 'Credit_Invoice_Date',
True)
self.paid()
elif self.action == 'Denied':
self.denied()
elif self.action == 'Approved':
self.approved()
elif self.action == 'Dispute Note':
self.disputenote()
else:
writelog("Processing {0} GRT Dispute Actions".format(len(self.df)),
'info')
self.asql = SQLConnect('alch')
self.asql.connect()
try:
self.asql.upload(self.df, 'mydisputes')
if self.action == 'Escalate':
self.escalate()
elif self.action == 'Close':
self.close()
elif self.action == 'Paid':
validatecol(self.asql, 'mydisputes', 'Amount_Or_Days')
validatecol(self.asql, 'mydisputes', 'Credit_Invoice_Date',
True)
self.paid()
elif self.action == 'Denied':
self.denied()
elif self.action == 'Approved':
self.approved()
elif self.action == 'Dispute Note':
self.disputenote()
processresults(self.folder_name, self.asql, 'mydisputes',
'New Seed Disputes')
finally:
self.asql.close()
def process_updates(files):
writeblank = False
for file in files:
folder_name = os.path.basename(os.path.dirname(file))
if writeblank:
writelog("", 'info')
writelog(
"Reading file ({0}/{1})".format(folder_name,
os.path.basename(file)), 'info')
writelog("", 'info')
if folder_name == '05_New-User':
newuser(file)
else:
xmlobj = XMLParseClass(file)
if xmlobj:
parsed = xmlobj.parsexml(
'./{urn:schemas-microsoft-com:rowset}data/')
if folder_name == '01_BMI-PCI':
for action in settings['BMIPCI-Action']:
df = parsed.loc[parsed['Action'] == action]
if not df.empty:
myobj = BMIPCI(action, df, folder_name)
myobj.process()
elif folder_name == '02_Seeds':
myobj = Seeds(folder_name, None, parsed)
myobj.dispute()
elif folder_name == '03_Non-Seeds':
myobj = NonSeeds(parsed, folder_name)
myobj.dispute()
elif folder_name == '04_Dispute-Actions':
for action in settings['Dispute_Actions-Action']:
df = parsed.loc[parsed['Action'] == action]
if not df.empty:
myobj = DisputeActions(action=action,
folder_name=folder_name,
df=df)
myobj.process()
del parsed
del xmlobj
del folder_name
writeblank = True
os.remove(file)
def myexithandler():
writelog('Exiting Vacuum...', 'warning')
gc.collect()
folder_name=folder_name,
df=df)
myobj.process()
del parsed
del xmlobj
del folder_name
writeblank = True
os.remove(file)
app = QtWidgets.QApplication(sys.argv).instance()
app.aboutToQuit.connect(myexithandler)
if __name__ == '__main__':
if os.path.isfile(log_filepath()):
writelog('', 'info')
writelog('Starting Vacuum...', 'info')
continue_flag = False
while 1 != 0:
Has_Updates = None
writelog('Vacuum sniffing floor for crumbs...', 'info')
while Has_Updates is None:
Has_Updates = check_for_updates()
sleep(1)
rand = random.randint(1, 10000000000)
if continue_flag:
writelog('', 'info')
continue_flag = False
def dispute(self):
if self.asql:
for cost_type in settings['Seed-Cost_Type'].split(', '):
if 'PC-' in cost_type:
self.grabseedinfo(
settings['PaperCost'], 'seed', cost_type, '''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.Billed_Amt=B.{0},
A.dispute_amt=isnull(A.dispute_amt,B.{0}),
A.USI=CASE
when isnull(A.USI,'') != '' then A.USI
WHEN isnull(B.WTN,'') != '' THEN B.WTN
WHEN isnull(B.Circuit_ID,'') != '' THEN B.Circuit_ID
WHEN isnull(B.BTN,'') != '' THEN B.BTN
END,
A.Source_TBL=isnull(A.Source_TBL, 'PCI'),
A.Source_ID=isnull(A.Source_ID, B.PCI_ID)
'''.format(cost_type.split('-')[1]))
elif 'MRC' == cost_type:
self.grabseedinfo(
settings[cost_type], 'bdt_{0}_id'.format(cost_type),
cost_type, '''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.USOC=case
when A.USOC is not null then A.USOC
else B.USOC
end,
A.USOC_Desc=case
when A.USOC_Desc is not null then A.USOC_Desc
else B.USOC_Description
end,
A.Billed_Amt=B.Amount,
A.dispute_amt=isnull(A.dispute_amt,B.Amount),
A.Source_TBL=isnull(A.Source_TBL, 'BMI'),
A.Source_ID=isnull(A.Source_ID, B.BMI_ID),
A.USI=CASE
when isnull(A.USI,'') != '' then A.USI
WHEN isnull(B.WTN,'') != '' THEN B.WTN
WHEN isnull(B.Circuit_ID,'') != '' THEN B.Circuit_ID
WHEN isnull(B.BTN,'') != '' THEN B.BTN
END,
A.CPID = C.CPID,
A.BANMaster_ID = C.BANMasterID
'''.format(cost_type), '''
left join {0} As C
on
B.BDT_MRC_ID = C.BDT_MRC_ID
and
B.Invoice_Date = C.Invoice_Date
'''.format(settings['MRC_CMP']))
elif 'OCC' == cost_type:
self.grabseedinfo(
settings[cost_type], 'bdt_{0}_id'.format(cost_type),
cost_type, '''
A.Record_Type=upper(B.Activity_Type),
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.USOC=case
when A.USOC is not null then A.USOC
else B.USOC
end,
A.USI=case
when isnull(A.USI,'') != '' then A.USI
WHEN isnull(B.BTN,'') != '' THEN B.BTN
WHEN isnull(B.Circuit_ID,'') != '' THEN B.Circuit_ID
END,
A.USOC_Desc=case
when A.USOC_Desc is not null then A.USOC_Desc
else B.USOC_Description
end,
A.Billed_Amt=B.Amount,
A.dispute_amt=isnull(A.dispute_amt, B.Amount),
A.Phrase_Code=case
when A.Phrase_Code is not null then A.Phrase_Code
else B.Phrase_Code
end,
A.Causing_SO=case
when A.Causing_SO is not null then A.Causing_SO
else B.SO
end,
A.PON=case
when A.PON is not null then A.PON
else B.PON
end
''')
elif 'TAS' == cost_type:
self.grabseedinfo(
settings[cost_type], 'bdt_{0}_id'.format(cost_type),
cost_type, '''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.Billed_Amt=B.Total_Amount,
A.dispute_amt=isnull(A.dispute_amt, B.Total_Amount),
A.Jurisdiction=case
when A.Jurisdiction is not null then A.Jurisdiction
else B.Jurisdiction_Phrase
end
'''.format(cost_type))
elif 'USAGE' == cost_type:
self.grabseedinfo(
settings[cost_type], 'bdt_{0}_id'.format(cost_type),
cost_type, '''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.Billed_Amt=B.Amount,
A.dispute_amt=isnull(A.dispute_amt,B.Amount),
A.USI=case when isnull(A.USI,'') != '' then A.USI else B.BTN end,
A.Usage_Rate=case
when A.Usage_Rate is not null then A.Usage_Rate
else B.Usage_Rate
end,
A.Jurisdiction=case
when A.Jurisdiction is not null then A.Jurisdiction
else B.Jurisdiction
end
'''.format(cost_type))
elif 'LPC' == cost_type:
self.grabseedinfo(
settings[cost_type], 'bdt_invoice_id', cost_type, '''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.Billed_Amt=B.Late_Payment_Charges,
A.dispute_amt=isnull(A.dispute_amt,B.Late_Payment_Charges)
'''.format(cost_type))
elif 'ADJ' == cost_type:
self.grabseedinfo(
settings[cost_type], 'bdt_pad_id', cost_type, '''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.Billed_Amt=B.Amount,
A.dispute_amt=isnull(A.dispute_amt,B.Amount),
A.Phrase_Code=case
when A.Phrase_Code is not null then A.Phrase_Code
else B.Phrase_Code
end
'''.format(cost_type))
self.appenddisputes()
else:
writelog("Processing {0} New Seed Disputes".format(len(self.df)),
'info')
self.asql = SQLConnect('alch')
self.asql.connect()
try:
self.asql.upload(self.df, 'myseeds')
validatecol(self.asql, 'myseeds', 'Dispute_Amt')
validatecol(self.asql, 'myseeds', 'Approved_Amt')
validatecol(self.asql, 'myseeds', 'Received_Amt')
validatecol(self.asql, 'myseeds', 'Received_Invoice_Date',
True)
for cost_type in settings['Seed-Cost_Type'].split(', '):
if 'PC-' in cost_type:
self.grabseedinfo(
settings['PaperCost'], 'seed', cost_type, '''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.Billed_Amt=B.{0},
A.dispute_amt=isnull(A.dispute_amt,B.{0}),
A.USI=CASE
when isnull(A.USI,'') != '' then A.USI
WHEN isnull(B.WTN,'') != '' THEN B.WTN
WHEN isnull(B.Circuit_ID,'') != '' THEN B.Circuit_ID
WHEN isnull(B.BTN,'') != '' THEN B.BTN
END,
A.Source_TBL=isnull(A.Source_TBL, 'PCI'),
A.Source_ID=isnull(A.Source_ID, B.PCI_ID)
'''.format(cost_type.split('-')[1]))
elif 'MRC' == cost_type:
self.grabseedinfo(
settings[cost_type],
'bdt_{0}_id'.format(cost_type), cost_type, '''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.USOC=case
when A.USOC is not null then A.USOC
else B.USOC
end,
A.USOC_Desc=case
when A.USOC_Desc is not null then A.USOC_Desc
else B.USOC_Description
end,
A.Billed_Amt=B.Amount,
A.dispute_amt=isnull(A.dispute_amt,B.Amount),
A.Source_TBL=isnull(A.Source_TBL, 'BMI'),
A.Source_ID=isnull(A.Source_ID, B.BMI_ID),
A.USI=CASE
when isnull(A.USI,'') != '' then A.USI
WHEN isnull(B.WTN,'') != '' THEN B.WTN
WHEN isnull(B.Circuit_ID,'') != '' THEN B.Circuit_ID
WHEN isnull(B.BTN,'') != '' THEN B.BTN
END,
A.CPID = C.CPID,
A.BANMaster_ID = C.BANMasterID
'''.format(cost_type), '''
left join {0} As C
on
B.BDT_MRC_ID = C.BDT_MRC_ID
and
B.Invoice_Date = C.Invoice_Date
'''.format(settings['MRC_CMP']))
elif 'OCC' == cost_type:
self.grabseedinfo(
settings[cost_type],
'bdt_{0}_id'.format(cost_type), cost_type, '''
A.Record_Type=upper(B.Activity_Type),
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.USOC=case
when A.USOC is not null then A.USOC
else B.USOC
end,
A.USI=case
when isnull(A.USI,'') != '' then A.USI
WHEN isnull(B.BTN,'') != '' THEN B.BTN
WHEN isnull(B.Circuit_ID,'') != '' THEN B.Circuit_ID
END,
A.USOC_Desc=case
when A.USOC_Desc is not null then A.USOC_Desc
else B.USOC_Description
end,
A.Billed_Amt=B.Amount,
A.dispute_amt=isnull(A.dispute_amt, B.Amount),
A.Phrase_Code=case
when A.Phrase_Code is not null then A.Phrase_Code
else B.Phrase_Code
end,
A.Causing_SO=case
when A.Causing_SO is not null then A.Causing_SO
else B.SO
end,
A.PON=case
when A.PON is not null then A.PON
else B.PON
end
''')
elif 'TAS' == cost_type:
self.grabseedinfo(
settings[cost_type],
'bdt_{0}_id'.format(cost_type), cost_type, '''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.Billed_Amt=B.Total_Amount,
A.dispute_amt=isnull(A.dispute_amt, B.Total_Amount),
A.Jurisdiction=case
when A.Jurisdiction is not null then A.Jurisdiction
else B.Jurisdiction_Phrase
end
'''.format(cost_type))
elif 'USAGE' == cost_type:
self.grabseedinfo(
settings[cost_type],
'bdt_{0}_id'.format(cost_type), cost_type, '''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.Billed_Amt=B.Amount,
A.dispute_amt=isnull(A.dispute_amt,B.Amount),
A.USI=case when isnull(A.USI,'') != '' then A.USI else B.BTN end,
A.Usage_Rate=case
when A.Usage_Rate is not null then A.Usage_Rate
else B.Usage_Rate
end,
A.Jurisdiction=case
when A.Jurisdiction is not null then A.Jurisdiction
else B.Jurisdiction
end
'''.format(cost_type))
elif 'LPC' == cost_type:
self.grabseedinfo(
settings[cost_type], 'bdt_invoice_id', cost_type,
'''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.Billed_Amt=B.Late_Payment_Charges,
A.dispute_amt=isnull(A.dispute_amt,B.Late_Payment_Charges)
'''.format(cost_type))
elif 'ADJ' == cost_type:
self.grabseedinfo(
settings[cost_type], 'bdt_pad_id', cost_type, '''
A.Record_Type='{0}',
A.Vendor=B.Vendor,
A.Platform=B.Platform,
A.BAN=B.BAN,
A.Bill_Date=B.Bill_Date,
A.State=B.State,
A.Billed_Amt=B.Amount,
A.dispute_amt=isnull(A.dispute_amt,B.Amount),
A.Phrase_Code=case
when A.Phrase_Code is not null then A.Phrase_Code
else B.Phrase_Code
end
'''.format(cost_type))
self.appenddisputes()
processresults(self.folder_name, self.asql, 'myseeds',
'New Seed Disputes')
finally:
self.asql.close()