def get_report_date(self, table_name):
session = reports.DBSession()
report_excute = eval('reports.{}'.format(table_name))
snap_report = session.query(report_excute,
report_excute.SnapDate).all()
snap_date = set([''.join(str(d[1]).split('-')) for d in snap_report])
return list(snap_date)
def get_report_country_for_date(self, table_name, report_date):
session = reports.DBSession()
report_excute = eval('reports.{}'.format(table_name))
mkps = session.query(report_excute, report_excute.Country)\
.filter_by(SnapDate=report_date).all()
mkps = set([(m[1]) for m in mkps])
return list(mkps)
def del_reports_for_date(self, table_name, snap_date, country):
session = reports.DBSession()
report_excute = eval('reports.{}'.format(table_name))
records = session.query(report_excute).filter_by(
SnapDate=snap_date, Country=country).all()
for record in records:
session.delete(record)
session.commit()
def update_feedback_rating(self, order_id):
session = reports.DBSession()
try:
session.query(reports.AprNegativeFeedback).filter(reports.AprNegativeFeedback.AmazonOrderId == order_id) \
.update({'RatingEnd': 0})
session.commit()
except Exception as e:
log.error('UpdateFeedbackError: %s' % e)
finally:
session.close()
def add_report_to_sql(self, json_b, table_name, country, params):
report_json = json.loads(json_b.decode())
snap_date = params.get('reportDate')
report_excute = 'reports.{}'.format(table_name)
session = reports.DBSession()
for report in report_json:
report_to_sql = eval(report_excute)(snap_date, country, report)
session.add(report_to_sql)
session.commit()
def select_feedback_orderid(self):
session = reports.DBSession()
try:
order_ids = session.query(
reports.AprNegativeFeedback,
reports.AprNegativeFeedback.FeedbackDate,
reports.AprNegativeFeedback.AmazonOrderId).all()
return order_ids
except Exception as e:
log.error('SelectFeedbackError: %s' % e)
finally:
session.close()
def add_report_to_sql(self, tb_name, country, report_json, snap_date=None):
execute_sql = 'reports.{}'.format(tb_name)
session = reports.DBSession()
for report in report_json:
if tb_name == 'AprNegativeFeedback': # feedback添加订单信息
od_id = report.get('Order ID')
fd_orderids = [
fod[2] for fod in self.select_feedback_orderid()
]
if od_id in fd_orderids:
continue
od_info = self.select_order_for_id(od_id)
if od_info:
report['BuyerName'] = od_info[1]
report['BuyerEmail'] = od_info[2]
report['PurchaseDate'] = od_info[3]
if snap_date:
report_to_sql = eval(execute_sql)(snap_date, country, report)
else:
report_to_sql = eval(execute_sql)(country, report)
session.add(report_to_sql)
session.commit()