def et(config, all_clusters: List[Cluster]) -> None: email = config['email']['username'] objects_to_sheet = ObjectsToSheet() existing_totals = objects_to_sheet.download_from_sheet(from_row, SHEET_ID, TAB_TITLE) existing_totals = [t for t in existing_totals if t.email != email] existing_totals.append(Total(email, compute_total(all_clusters))) objects_to_sheet.upload_to_sheet(existing_totals, SHEET_ID, TAB_TITLE)
def upload_unknown_trackings(sheet_id: str, known_trackings: Set[str],
trackings_from_groups: TrackingInfoDict) -> None:
print("Uploading list of unknown trackings")
unknown_trackings = []
for tracking_tuple in trackings_from_groups.keys():
for tracking in tracking_tuple:
if tracking not in known_trackings:
group, amount, date = trackings_from_groups[tracking_tuple]
unknown_trackings.append(
UnknownTracking(tracking, date, group, amount, False))
unknown_trackings_by_num = {
u.tracking_number: u
for u in unknown_trackings
}
for previous_unknown_tracking in _get_unknown_trackings_from_sheet(
sheet_id):
if previous_unknown_tracking.manually_verified and previous_unknown_tracking.tracking_number in unknown_trackings_by_num:
unknown_trackings_by_num[previous_unknown_tracking.
tracking_number].manually_verified = True
unknown_trackings.sort(key=cmp_to_key(compare))
ObjectsToSheet().upload_to_sheet(unknown_trackings, sheet_id,
'Unknown Trackings')
def main():
parser = argparse.ArgumentParser(
description="Ouptut a set of orders for which we don't have shipments")
parser.add_argument("--days")
args, _ = parser.parse_known_args()
order_ids_to_orders = get_order_ids_to_orders(args)
config = open_config()
sheet_id = config['reconciliation']['baseSpreadsheetId']
orders_from_sheet = get_orders_from_sheet(sheet_id)
for previous_order in orders_from_sheet:
if previous_order.manually_verified and previous_order.order_id in order_ids_to_orders:
order_ids_to_orders[previous_order.order_id].manually_verified = True
orders_list = list(order_ids_to_orders.values())
orders_list = filter_orders(orders_list, config)
orders_list.sort(key=cmp_to_key(compare))
objects_to_sheet = ObjectsToSheet()
objects_to_sheet.upload_to_sheet(orders_list, sheet_id, 'Non-Shipped Orders')
class TrackingUploader:
def __init__(self, config) -> None:
self.config = config
self.objects_to_sheet = ObjectsToSheet()
self.base_spreadsheet_id = config['reconciliation']['baseSpreadsheetId']
def upload_trackings(self, trackings) -> None:
existing_trackings = self.objects_to_sheet.download_from_sheet(tracking.from_row,
self.base_spreadsheet_id,
"Trackings")
existing_tracking_numbers = set(
[existing_tracking.tracking_number for existing_tracking in existing_trackings])
new_trackings = [t for t in trackings if t.tracking_number not in existing_tracking_numbers]
all_trackings = existing_trackings + new_trackings
self.upload_all_trackings(all_trackings)
def upload_all_trackings(self, trackings: List[tracking.Tracking]) -> None:
if self.config.get("onlyLastSixMonths", False):
six_months_ago = (date.today() - timedelta(weeks=26)).strftime("%Y-%m-%d")
trackings = [t for t in trackings if t.ship_date > six_months_ago]
self.objects_to_sheet.upload_to_sheet(trackings, self.base_spreadsheet_id, "Trackings")
def __init__(self, config) -> None:
self.config = config
self.objects_to_sheet = ObjectsToSheet()
class ReconciliationUploader:
def __init__(self, config) -> None:
self.config = config
self.objects_to_sheet = ObjectsToSheet()
def override_pos_and_costs(self, all_clusters):
print("Filling manual PO adjustments")
base_sheet_id = self.config['reconciliation']['baseSpreadsheetId']
downloaded_clusters = self.objects_to_sheet.download_from_sheet(
clusters.from_row, base_sheet_id, "Reconciliation v2")
downloaded_tracking_to_cluster = compute_tracking_to_cluster(
downloaded_clusters)
for cluster in all_clusters:
candidate_downloads = find_candidate_downloads(
cluster, downloaded_tracking_to_cluster)
pos = set()
non_reimbursed_trackings = set()
total_tracked_cost = 0.0
for candidate in candidate_downloads:
pos.update(candidate.purchase_orders)
non_reimbursed_trackings.update(
candidate.non_reimbursed_trackings)
total_tracked_cost += candidate.tracked_cost
cluster.purchase_orders = pos
cluster.non_reimbursed_trackings = non_reimbursed_trackings
cluster.tracked_cost = total_tracked_cost
def download_upload_clusters_new(self, all_clusters) -> None:
base_sheet_id = self.config['reconciliation']['baseSpreadsheetId']
self.fill_adjustments(all_clusters, base_sheet_id, "Reconciliation v2")
all_clusters.sort(key=cmp_to_key(compare))
print("Uploading new reconciliation to sheet")
self.objects_to_sheet.upload_to_sheet(all_clusters, base_sheet_id,
"Reconciliation v2",
get_conditional_formatting_body)
def fill_adjustments(self, all_clusters, base_sheet_id, tab_title) -> None:
print("Filling in cost adjustments if applicable")
downloaded_clusters = self.objects_to_sheet.download_from_sheet(
clusters.from_row, base_sheet_id, tab_title)
downloaded_tracking_to_cluster = compute_tracking_to_cluster(
downloaded_clusters)
for cluster in all_clusters:
candidate_downloads = find_candidate_downloads(
cluster, downloaded_tracking_to_cluster)
cluster.adjustment = sum(
[candidate.adjustment for candidate in candidate_downloads])
cluster.notes = "; ".join([
candidate.notes for candidate in candidate_downloads
if candidate.notes.strip()
])
# Import the manual override boolean from the sheet's checkbox ONLY if:
# (a) no clusters have been merged in since the last sheet export and
# (b) there haven't been any new order IDs or tracking #s added to the
# cluster since the last export.
if len(candidate_downloads) == 1:
sheet_cluster = candidate_downloads[0]
if (sheet_cluster.trackings == cluster.trackings
and sheet_cluster.orders == cluster.orders):
cluster.manual_override = sheet_cluster.manual_override
def _get_unknown_trackings_from_sheet(sheet_id) -> List[UnknownTracking]:
objects_to_sheet = ObjectsToSheet()
return objects_to_sheet.download_from_sheet(_unknown_tracking_from_row,
sheet_id, 'Unknown Trackings')
def get_orders_from_sheet(sheet_id): objects_to_sheet = ObjectsToSheet() return objects_to_sheet.download_from_sheet(order_from_row, sheet_id, 'Non-Shipped Orders')
def main():
parser = argparse.ArgumentParser(
description='Importing Amazon reports from CSV or Drive')
parser.add_argument("--personal",
"-p",
action="store_true",
help="Use the personal CSV format")
parser.add_argument("globs", nargs="*")
args, _ = parser.parse_known_args()
from_row_function = from_personal_row if args.personal else from_amazon_row
all_trackings = []
if args.globs:
for glob_input in args.globs:
files = glob.glob(glob_input)
for file in files:
all_trackings.extend(
read_trackings_from_file(file, from_row_function))
else:
sheet_id = get_required("Enter Google Sheet ID: ")
tab_name = get_required("Enter the name of the tab within the sheet: ")
objects_to_sheet = ObjectsToSheet()
all_trackings.extend(
objects_to_sheet.download_from_sheet(from_amazon_row, sheet_id,
tab_name))
if len(all_trackings) == 0:
print("Nothing to import; terminating.")
return
num_n_a_trackings = len([
ignored for ignored in all_trackings
if ignored and ignored.tracking_number == 'N/A'
])
num_empty_trackings = len([
ignored for ignored in all_trackings
if ignored and ignored.tracking_number == ''
])
print(f'Skipping {num_n_a_trackings} for N/A tracking column and '
f'{num_empty_trackings} for empty tracking column.')
all_trackings = [
tracking for tracking in all_trackings
if tracking and tracking.tracking_number != 'N/A'
and tracking.tracking_number != ''
]
len_non_reconcilable_trackings = len(
[t for t in all_trackings if not t.reconcile])
print(
f'Skipping {len_non_reconcilable_trackings} non-reconcilable trackings.'
)
all_trackings = [t for t in all_trackings if t.reconcile]
base_len_trackings = len(all_trackings)
all_trackings = dedupe_trackings(all_trackings)
print(
f'Filtered {base_len_trackings - len(all_trackings)} duplicate trackings from the sheet.'
)
print('Uploading trackings to Sheets...')
tracking_uploader = TrackingUploader(config)
tracking_uploader.upload_trackings(all_trackings)
tracking_output = TrackingOutput(config)
trackings_before_save = {
t.tracking_number
for t in tracking_output.get_existing_trackings()
}
print(f"Number of trackings before: {len(trackings_before_save)}.")
print(f"Number imported from report(s): {len(all_trackings)}.")
tracking_output.save_trackings(all_trackings)
trackings_after_save = {
t.tracking_number: t
for t in tracking_output.get_existing_trackings()
}
print(f"Number of trackings after: {len(trackings_after_save)}.")
new_trackings = set(
trackings_after_save.keys()).difference(trackings_before_save)
print(f"Number of new-to-us trackings: {len(new_trackings)}")
new_tracking_objects = [trackings_after_save[t] for t in new_trackings]
email_config = config['email']
email_sender = EmailSender(email_config)
email_sender.send_email(new_tracking_objects)
print("Uploading new trackings to the group(s)' site(s)...")
group_site_manager = GroupSiteManager(config, DriverCreator())
group_site_manager.upload(new_tracking_objects)
def __init__(self, config) -> None: self.config = config self.objects_to_sheet = ObjectsToSheet() self.base_spreadsheet_id = config['reconciliation']['baseSpreadsheetId']
def main():
parser = argparse.ArgumentParser(
description='Importing Amazon reports from CSV or Drive')
parser.add_argument("globs", nargs="*")
args, _ = parser.parse_known_args()
all_trackings = []
if args.globs:
for glob_input in args.globs:
files = glob.glob(glob_input)
for file in files:
all_trackings.extend(read_trackings_from_file(file))
else:
sheet_id = get_required("Enter Google Sheet ID: ")
tab_name = get_required("Enter the name of the tab within the sheet: ")
objects_to_sheet = ObjectsToSheet()
all_trackings.extend(
objects_to_sheet.download_from_sheet(from_amazon_row, sheet_id,
tab_name))
num_n_a_trackings = len([
ignored for ignored in all_trackings
if ignored and ignored.tracking_number == 'N/A'
])
num_empty_trackings = len([
ignored for ignored in all_trackings
if ignored and ignored.tracking_number == ''
])
print(
f'Skipping {num_n_a_trackings} for n/a tracking column and {num_empty_trackings} for empty tracking column'
)
all_trackings = [
tracking for tracking in all_trackings
if tracking and tracking.tracking_number != 'N/A'
and tracking.tracking_number != ''
]
len_non_reconcilable_trackings = len(
[t for t in all_trackings if not t.reconcile])
print(
f'Skipping {len_non_reconcilable_trackings} non-reconcilable trackings'
)
all_trackings = [t for t in all_trackings if t.reconcile]
base_len_trackings = len(all_trackings)
all_trackings = dedupe_trackings(all_trackings)
print(
f'Filtered {base_len_trackings - len(all_trackings)} duplicate trackings from the sheet'
)
print('Uploading trackings to Sheets...')
tracking_uploader = TrackingUploader(config)
tracking_uploader.upload_trackings(all_trackings)
tracking_output = TrackingOutput(config)
print("Number of trackings beforehand: %d" %
len(tracking_output.get_existing_trackings()))
print("Number from sheet: %d" % len(all_trackings))
tracking_output.save_trackings(all_trackings)
print("Number of trackings after: %d" %
len(tracking_output.get_existing_trackings()))
print("Uploading to the group(s)' site(s)...")
group_site_manager = GroupSiteManager(config, DriverCreator())
group_site_manager.upload(all_trackings)