def invoice_send_email(entity, receiver, attachment):
# Send email
scrapymail = Email()
if (attachment is None) or attachment.empty:
subject = '[PAM Tax Checking] - {0} 发票无异常 {1}'.format(
TIME['TODAY'], entity)
content = 'Hi All,\r\n\r\n{}的发票无异常记录。\r\n\r\nThanks.'.format(entity)
scrapymail.send(subject=subject,
content=content,
receivers=receiver,
attachment=None)
else:
subject = '[PAM Tax Checking] - {0} 发票异常清单 {1}'.format(
TIME['TODAY'], entity)
content = 'Hi All,\r\n\r\n请查看附件关于{}的发票异常记录。\r\n\r\nThanks.'.format(
entity)
entity_path = df_to_excel(df=attachment,
path=PATH['FILE_DIR'],
file_name=PATH['ATTACHMENT_FILE'].format(
TIME['TODAY'], entity),
sheet_name=entity)
scrapymail.send(subject=subject,
content=content,
receivers=receiver,
attachment=entity_path)
logger.info('Delete attachment file.')
os.remove(entity_path)
scrapymail.close()
condition_done = '[Timestamp] >= {0} AND [Source] = {1}'\
.format(get_sql_list(TIME['TODAY']), get_sql_list(PATH['SITE']))
entities_run = exist_db.get_to_runs(table_name=DB['LOG_TABLE_NAME'],
condition=condition_done,
source=PATH['SITE'])
for entity in entities_run:
entity_object = Haozu(entity)
entity_object.run()
with ODBC(config=keys.dbconfig_mkt) as entity_db:
entity_db.upload(df=entity_object.df['df'],
table_name=DB['DETAIL_TABLE'],
new_id=PATH['SITE'])
entity_db.upload(df=entity_object.df['info'],
table_name=DB['INFO_TABLE'],
new_id=PATH['SITE'],
dedupe_col='Source_ID')
entity_db.log(Entity=entity,
Timestamp=TIME['TODAY'],
Source=PATH['SITE'],
start=1,
end=len(entity_object.df['info']))
scrapyemail = Email()
scrapyemail.send(subject='[Scrapy] ' + DB['DETAIL_TABLE'],
content='Done',
attachment=PATH['LOG_PATH'])
scrapyemail.close()
exit(0)
# Upload to database
entity_db = ODBC(keys.dbconfig)
entity_db.upload(df=tax_df, table_name=DB['TAX_INFO_TABLE'])
entity_db.upload(df=tax_detail_df, table_name=DB['TAX_DETAIL_TABLE'])
entity_db.log(start=TIME['PRE3MONTH'], end=TIME['TODAY'], Timestamp=TIME['TIMESTAMP'], Source=PATH['SITE'],
Entity=row['Entity_Name'])
entity_db.close()
# Ensure failure of scraping process do not interrupt email and sp execution
with ODBC(keys.dbconfig) as execute_db:
# Update Irregular_Ind by executing stored procedure
execute_db.call_sp(sp='CHN.Irregular_Tax_Refresh', table_name=DB['TAX_DETAIL_TABLE'],
table_name2=DB['TAX_DETAIL_TABLE'])
for index, row in access.iterrows():
# Get irregular record
att = execute_db.call_sp(sp='CHN.Irregular_Tax_ETL', output=True, table_name=DB['TAX_DETAIL_TABLE'],
entity_name=row['Entity_Name'])
numeric_col = ['金额', '单价', '税率', '税额']
if att is not None:
att[numeric_col] = att[numeric_col].apply(pd.to_numeric)
invoice_send_email(entity=row['Entity_Name'], receiver=row['Email_List'], attachment=att)
# Send email summary
scrapyemail_summary = Email()
scrapyemail_summary.send('[Scrapy]' + PATH['SITE'], 'Done', PATH['LOG_PATH'],
receivers='[email protected];[email protected]')
scrapyemail_summary.close()
exit(0)
