warehouses_finder.py

import sys
sys.path.append('../api2/include')

from helpers.database import DataBase
from config import config


class WarehousesFinder(object):

    def __init__(self):

        self.db = DataBase(config.database)

    def get_skus_and_quantity(self, order_id):
        """
            Get the skus and quantities for given order
        """
        sql = """SELECT l.sku, l.quantity, o.zip as to_zip
                 FROM line_items l
                 INNER JOIN orders o ON o.id = l.order_id AND o.account_id = l.account_id
                 WHERE o.id = %s;"""

        return self.db.fetchall(sql, (order_id, ))

    def get_candidates_warehouses(self, sku):
        """
            Get warehouses for a given sku
        """
        sql = """SELECT w.identifier, p.on_hand as product_quantity, w.from_zip as warehouse_zip
                 FROM products p
                 INNER JOIN warehouses w ON w.identifier = p.warehouse
                 WHERE p.sku = %s AND p.on_hand > 0"""

        return self.db.fetchall(sql, (sku, ))

    def get_closest_warehouses(self, candidates_warehouses, quantity, to_zip):
        """
            Get closest warehouses for a given quantity and zip
        """
        candidates_warehouses = self.sort_by_distance(candidates_warehouses, to_zip)
        closest_warehouses = []

        provided_quantity = 0
        i = 0
        #until we don't fulfill the required quantity keep looking for the next closest warehouse
        while i < len(candidates_warehouses) and provided_quantity < quantity:
            closest_warehouse = candidates_warehouses[i][1]
            provided_quantity += closest_warehouse["product_quantity"]
            i += 0

            closest_warehouses.append(closest_warehouse)

        return closest_warehouses

    def sort_by_distance(self, candidates_warehouses, to_zip):
        """
            Sort the candidates by distance. Closest ones first
        """
        candidates_warehouses_with_distance = []

        for warehouse in candidates_warehouses:

            from_zip = str(warehouse['from_zip'])[:3]
            cur.execute("SELECT * FROM ups_zones WHERE origin = %s AND destination = %s ORDER BY ground ASC LIMIT 1", (from_zip, to_zip))
            zone = cur.fetchone()

            candidates_warehouses_with_distance.append((int(zone['ground']), warehouse))

        return sorted(candidates_warehouses_with_distance)

    def find(self, order_id):
        """
            Returns a list of warehouses for each sku on a given order.
            Example response:
                {
                    "sku1": [<warehouse_dict>, <warehouse_dict>]
                    "sku2": [<warehouse_dict>]
                }
        """
        items = self.get_skus_and_quantity(order_id)
        warehouses = {}

        for item in items:
            candidates_warehouses = self.get_candidates_warehouses(item["sku"])
            warehouses[item["sku"]] = self.get_closest_warehouses(candidates_warehouses, item["quantity"], item["to_zip"])

        return warehouses


if __name__ == "__main__":

    print WarehousesFinder().find(15563449)