WebSVN – SmartDukaan – Diff – /trunk/PyProj/src/shop2020/model/v1/inventory/script/DailyStockReportGenerator.py

 import operator
 import xlwt
 from shop2020.thriftpy.model.v1.catalog.ttypes import status
 from shop2020.utils.EmailAttachmentSender import get_attachment_part, mail_html
 import datetime
+from shop2020.model.v1.inventory.impl import DataService
+from shop2020.model.v1.inventory.impl.DataService import Warehouse, \
+    ItemInventoryHistory, CurrentInventorySnapshot, VendorItemPricing, \
+    VendorItemMapping, Vendor, MissedInventoryUpdate, BadInventorySnapshot, \
+    VendorHolidays, ItemAvailabilityCache, \
+    CurrentReservationSnapshot, IgnoredInventoryUpdateItems, ItemStockPurchaseParams, \
+    OOSStatus, AmazonInventorySnapshot, StateMaster, HoldInventoryDetail, AmazonFbaInventorySnapshot, \
+    SnapdealInventorySnapshot, FlipkartInventorySnapshot, SnapdealStockAtEOD, FlipkartStockAtEOD
+from shop2020.thriftpy.model.v1.inventory.ttypes import \
+    InventoryServiceException, HolidayType, InventoryType, WarehouseType
+from shop2020.model.v1.inventory.impl.Convertors import to_t_item_inventory
 if __name__ == '__main__' and __package__ is None:
     import sys
     import os
     sys.path.insert(0, os.getcwd())
 categories={}
 catm = CategoryManager()
+def get_item_availability_for_location(item_id, source_id):
+    item_availability = ItemAvailabilityCache.get_by(itemId=item_id, sourceId = source_id)
+    if item_availability:
+        return [item_availability.warehouseId, item_availability.expectedDelay, item_availability.billingWarehouseId, item_availability.sellingPrice, item_availability.totalAvailability, item_availability.weight]
+    else:
+        __update_item_availability_cache(item_id, source_id)
+            ##Check risky status for the source
+        __check_risky_item(item_id, source_id)
+        return get_item_availability_for_location(item_id, source_id)
+def __get_item_from_source(item_id, source_id):
+    if source_id == 1:
+        client = CatalogClient("catalog_service_server_host_master", "catalog_service_server_port").get_client()
+        return client.getItem(item_id)
+    if source_id == 2:
+        client = CatalogClient("catalog_service_server_host_hotspot", "catalog_service_server_port").get_client()
+        return client.getItem(item_id)
+def get_ignored_warehouses(item_id):
+    Ignored_inventory_items = IgnoredInventoryUpdateItems.query.filter_by(item_id=item_id).all()
+    warehouses = []
+    for Ignored_inventory_item in Ignored_inventory_items:
+        warehouses.append(Ignored_inventory_item.warehouse_id)
+    return warehouses
+def __get_warehouse_with_min_transfer_price(warehouses, ignoredWhs, item_id, item_pricing, ignoreAvailability):
+    warehouse_retid = -1
+    minTransferPrice = None
+    total_availability = 0
+    availabilityForBillingWarehouses = {}
+    warehousesAvailability = {}
+    availability = 0
+    billing_warehouse_retid = None
+    if not ignoreAvailability:
+        for entry in CurrentInventorySnapshot.query.filter_by(item_id = item_id).all():
+            entry.reserved = max(entry.reserved, 0)
+            entry.held = max(entry.held, 0)
+            #if entry.availability > entry.reserved:
+            warehousesAvailability[entry.warehouse_id] = [entry.availability, entry.reserved, entry.held]
+    if len(ignoredWhs) > 0:
+        for whid in ignoredWhs:
+            if warehousesAvailability.has_key(whid):
+                warehousesAvailability[whid][0] = 0
+                warehousesAvailability[whid][1] = 0
+                warehousesAvailability[whid][2] = 0
+    for warehouse in warehouses.values():
+        if not ignoreAvailability:
+            #TODO Mistake no entry for this warehouse.id in warehouseswithAvailab
+            if warehouse.id not in warehousesAvailability:
+                continue
+            entry = warehousesAvailability[warehouse.id]
+            if warehouse.billingWarehouseId in availabilityForBillingWarehouses:
+                if warehouse.billingWarehouseId is not None or warehouse.billingWarehouseId != 0:
+                    availabilityForBillingWarehouses[warehouse.billingWarehouseId] = availabilityForBillingWarehouses[warehouse.billingWarehouseId] + entry[0] - entry[1] - entry[2]
+            else:
+                if warehouse.billingWarehouseId is not None or warehouse.billingWarehouseId != 0:
+                    availabilityForBillingWarehouses[warehouse.billingWarehouseId] = entry[0] - entry[1] - entry[2]
+            if entry[0] <= (entry[1] + entry[2]):
+                continue
+            total_availability += entry[0] - entry[1] - entry[2]
+        # Missing transfer price cases should not impact warehouse assignment
+        transferPrice = None
+        if item_pricing.has_key(warehouse.vendor_id):
+            transferPrice = item_pricing[warehouse.vendor_id].nlc
+        if minTransferPrice is None or (transferPrice and minTransferPrice > transferPrice):
+            warehouse_retid = warehouse.id
+            billing_warehouse_retid = warehouse.billingWarehouseId
+            minTransferPrice = transferPrice
+    if billing_warehouse_retid in availabilityForBillingWarehouses:
+        availability = availabilityForBillingWarehouses[billing_warehouse_retid]
+    else:
+        availability = total_availability
+    return [warehouse_retid, availability]
+def __get_warehouse_with_min_transfer_delay(warehouses, ignoredWhs, item_id, item_pricing):
+    minTransferDelay = None
+    minTransferDelayWarehouses = {}
+    total_availability = 0
+    for entry in CurrentInventorySnapshot.query.filter_by(item_id = item_id).all():
+        entry.reserved = max(entry.reserved, 0)
+        entry.held = max(entry.held, 0)
+        if warehouses.has_key(entry.warehouse_id):
+            warehouse = warehouses[entry.warehouse_id]
+            #if entry.availability > entry.reserved:
+            if entry.warehouse_id not in ignoredWhs:
+                total_availability += entry.availability - entry.reserved - entry.held
+            if entry.availability - entry.reserved - entry.held <= 0:
+                continue
+            transferDelay = warehouse.transferDelayInHours
+            if minTransferDelay is None or minTransferDelay >= transferDelay:
+                if minTransferDelay != transferDelay:
+                    minTransferDelayWarehouses = {}
+                minTransferDelayWarehouses[warehouse.id] = warehouse
+                minTransferDelay = transferDelay
+    return [__get_warehouse_with_min_transfer_price(minTransferDelayWarehouses, ignoredWhs, item_id, item_pricing, False)[0], total_availability]
+def __get_vendor_holiday_delay(vendor_id, expectedDelay):
+    ## If vendor is closed two days continuously
+    holidayDelay = 0
+    currentDate = datetime.date.today()
+    expectedDate = currentDate + datetime.timedelta(days = expectedDelay)
+    holidays = VendorHolidays.query.filter(VendorHolidays.vendor_id == vendor_id).filter(VendorHolidays.date.between(currentDate, expectedDate)).all()
+    if holidays:
+        holidayDelay = holidayDelay + len(holidays)
+    return holidayDelay
+def __update_item_availability_cache(item_id, source_id):
+    """
+    Determines the warehouse that should be used to fulfil an order for the given item.
+    Algorithm explained at https://sites.google.com/a/shop2020.in/virtual-w-h-and-inventory/technical-details
+    It will be ensured that every item has either a preferred vendor specified or at least for one vendor its transfer price should be defined.
+    This is needed to associate an item with at least one vendor so that in default case when its available no where, we know from where to procure it.
+    if item available at any OUR-GOOD warehouse
+        // OUR-GOOD warehouses have inventory risk; So, we empty them first!
+        // We can start with minimum transfer price criterion but down the line we can also bring in Inventory age
+        assign OUR-GOOD warehouse with minimum transfer price
+    else
+        if Preferred vendor is specified and marked Sticky
+            // Always purchase from Preferred if its marked sticky
+            assign preferred vendor's THIRDPARTY GOOD/VIRTUAL warehouse
+        else
+            if item available in a THIRDPARTY GOOD/VIRTUAL warehouse
+                assign THIRDPARTY GOOD/VIRTUAL warehouse where item is available with minimal transfer delay followed by minimum transfer price
+            else
+                // Item not available at any warehouse, OURS or THIRDPARTY
+                If Preferred vendor is specified
+                    assign preferred vendor's THIRDPARTY GOOD/VIRTUAL warehouse
+                else
+                    assign THIRDPARTY GOOD/VIRTUAL warehouse with minimum transfer price
+    Returns an ordered list of size 4 with following elements in the given order:
+. Logistics location of the warehouse which was finally picked up to ship the order.
+. Expected delay added by the category manager.
+. Id of the warehouse which was finally picked up.
+    Parameters:
+     - itemId
+    """
+    item = __get_item_from_source(item_id, source_id)
+    item_pricing = {}
+    for vendorItemPricing in VendorItemPricing.query.filter_by(item_id=item_id).all():
+        item_pricing[vendorItemPricing.vendor_id] = vendorItemPricing
+    ignoredWhs = get_ignored_warehouses(item_id)
+    warehouses = {}
+    ourGoodWarehouses = {}
+    thirdpartyWarehouses = {}
+    preferredThirdpartyWarehouses = {}
+    for warehouse in Warehouse.query.all():
+        if (warehouse.inventoryType == InventoryType._VALUES_TO_NAMES[InventoryType.BAD] or warehouse.warehouseType == WarehouseType._VALUES_TO_NAMES[WarehouseType.OURS_THIRDPARTY]):
+            continue
+        warehouses[warehouse.id] = warehouse
+        if warehouse.warehouseType == WarehouseType._VALUES_TO_NAMES[WarehouseType.OURS]:
+            if warehouse.inventoryType == InventoryType._VALUES_TO_NAMES[InventoryType.GOOD]:
+                ourGoodWarehouses[warehouse.id] = warehouse
+        else:
+            thirdpartyWarehouses[warehouse.id] = warehouse
+            if item.preferredVendor == warehouse.vendor_id and warehouse.inventoryType == InventoryType._VALUES_TO_NAMES[InventoryType.GOOD]:
+                preferredThirdpartyWarehouses[warehouse.id] = warehouse
+    warehouse_retid = -1
+    total_availability = 0
+    [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_price(ourGoodWarehouses, ignoredWhs, item_id, item_pricing, False)
+    if warehouse_retid == -1:
+        if item.preferredVendor and item.isWarehousePreferenceSticky:
+            [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_delay(preferredThirdpartyWarehouses, ignoredWhs, item_id, item_pricing)
+            if warehouse_retid == -1:
+                warehouse_retid = preferredThirdpartyWarehouses.keys()[0]
+        else:
+            [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_delay(thirdpartyWarehouses, ignoredWhs, item_id, item_pricing)
+            if warehouse_retid == -1:
+                if item.preferredVendor:
+                    warehouse_retid = preferredThirdpartyWarehouses.keys()[0]
+                else:
+                    [warehouse_retid, total_availability] = __get_warehouse_with_min_transfer_price(thirdpartyWarehouses, ignoredWhs, item_id, item_pricing, True)
+    warehouse = warehouses[warehouse_retid]
+    billingWarehouseId = warehouse.billingWarehouseId
+    # Fetching billing warehouse of a Good billable warehouse corresponding to the virtual one
+    if not warehouse.billingWarehouseId:
+        for w in Warehouse.query.filter_by(vendor_id = warehouse.vendor_id, inventoryType = InventoryType._VALUES_TO_NAMES[InventoryType.GOOD]).all():
+            if w.billingWarehouseId:
+                billingWarehouseId = w.billingWarehouseId
+                break
+    expectedDelay = item.expectedDelay
+    if expectedDelay is None:
+        print 'expectedDelay field for this item was Null. Resetting it to 0'
+        expectedDelay = 0
+    else:
+        expectedDelay = int(item.expectedDelay)
+    if total_availability <= 0:
+        if item.preferredVendor in [1, 5]:
+            expectedDelay = expectedDelay + 3
+        else:
+            expectedDelay = expectedDelay + 2
+    else:
+        if warehouse.transferDelayInHours:
+            expectedDelay = expectedDelay + warehouse.transferDelayInHours / 24
+    if warehouse.warehouseType == WarehouseType.THIRD_PARTY:
+        expectedDelay = expectedDelay + __get_vendor_holiday_delay(warehouse.vendor_id, expectedDelay)
+    total_availability = 0
+    for entry in CurrentInventorySnapshot.query.filter_by(item_id = item_id).all():
+        if entry.warehouse_id not in ignoredWhs:
+            total_availability += entry.availability - entry.reserved
+    item_availability_cache = ItemAvailabilityCache.get_by(itemId=item_id, sourceId=source_id)
+    if item_availability_cache is None:
+        item_availability_cache = ItemAvailabilityCache()
+        item_availability_cache.itemId = item_id
+        item_availability_cache.sourceId = source_id
+    item_availability_cache.warehouseId = int(warehouse_retid)
+    item_availability_cache.expectedDelay = expectedDelay
+    item_availability_cache.billingWarehouseId = billingWarehouseId
+    item_availability_cache.sellingPrice = item.sellingPrice
+    item_availability_cache.totalAvailability = total_availability
+    item_availability_cache.weight = 1000*item.weight if item.weight else 300
+    session.commit()
+def __check_risky_item(item_id, source_id):
+    ## We should get the list of strings which will identify to the catalog servers
+    if source_id == 1:
+        client = CatalogClient("catalog_service_server_host_master", "catalog_service_server_port").get_client()
+        client.validateRiskyStatus(item_id)
+    if source_id == 2:
+        client = CatalogClient("catalog_service_server_host_hotspot", "catalog_service_server_port").get_client()
+        client.validateRiskyStatus(item_id)
+def get_item_inventory_by_item_id(item_id):
+    inventory =  CurrentInventorySnapshot.query.filter_by(item_id=item_id).all()
+    return to_t_item_inventory(inventory, item_id)
 def main():
     wbk = xlwt.Workbook()
     sheet = wbk.add_sheet('main', cell_overwrite_ok=True)
     heading_xf = xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')
     sheet.write(0, 12, "Reserved", heading_xf)
     sheet.write(0, 13, "Status", heading_xf)
     sheet.write(0, 14, "Entity Id", heading_xf)
     sheet.write(0, 15, "Ships By", heading_xf)
+    DataService.initialize('inventory','192.168.190.114')
     iclient = InventoryClient().get_client()
     cclient = CatalogClient().get_client()
-    items = cclient.getAllItems(False)
+#    items = cclient.getAllItems(False)
     xls_fields = []
-    for item in items:
+#    for item in items:
-        if item.itemStatus == 3:
+#        if item.itemStatus == 3:
-            try:
+#            try:
-                fulfilmentWarehouseId, expected_delay, billingWarehouseId, sellingPrice, totalAvailability, weight = iclient.getItemAvailabilityAtLocation(item.id, 1)
+#                fulfilmentWarehouseId, expected_delay, billingWarehouseId, sellingPrice, totalAvailability, weight = iclient.getItemAvailabilityAtLocation(item.id, 1)
-            except:
+#           except:
-                print "Unable to fetch inventory for item " + str(item.id)
+#               print "Unable to fetch inventory for item " + str(item.id)
-                continue
+#               continue
     statsMap = {}
     items = cclient.getAllItems(False)
     for item in items:
         if item.itemStatus not in (2,3,6):
             continue
         try:
-            fulfilmentWarehouseId, expected_delay, billingWarehouseId, sellingPrice, totalAvailability, weight = iclient.getItemAvailabilityAtLocation(item.id, 1)
+            fulfilmentWarehouseId, expected_delay, billingWarehouseId, sellingPrice, totalAvailability, weight = get_item_availability_for_location(item.id, 1)
-            inventory = iclient.getItemInventoryByItemId(item.id)
+            inventory = get_item_inventory_by_item_id(item.id)
             availability = 0
             reserved = 0
             for wh, inv in inventory.availability.items():
                 if wh != 16:
                     availability = availability + inv
             if item.itemStatus == 3:
                 if expected_delay == 0:
                     shipsBy = 'NEXT DAY'
                 else:
                     shipsBy = 'NO NEXT DAY'
-        except:
+        except Exception as e:
             print "Unable to fetch inventory for item " + str(item.id)
+            print e
             continue
         mainCategory = catm.getCategory(catm.getCategory(item.category).parent_category_id).display_name
         if not statsMap.has_key(mainCategory):
             statsMap[mainCategory] = {'NEXT DAY' : [], 'NO NEXT DAY' : [], 'NOT AVAILABLE' : []}
     for catName in statsMap.keys():
         innerMap = statsMap.get(catName)
         body = body + "<tr><td>" + catName + "</td><td>" + str(len(innerMap.get('NEXT DAY')+innerMap.get('NO NEXT DAY'))) + "</td><td>" + str(len(innerMap.get('NEXT DAY'))) + "</td><td>" + str(len(innerMap.get('NOT AVAILABLE'))) + "</td><td>" + str(len(set(innerMap.get('NEXT DAY')+innerMap.get('NO NEXT DAY')))) + "</td><td>" + str(len(set(innerMap.get('NEXT DAY')))) + "</td><td>" + str(len(set(innerMap.get('NOT AVAILABLE')))) + "</td></tr>"
     body = body + "</table></body></html>"
     timestring = datetime.datetime.now().strftime("%Y-%m-%d")
     filename = "/tmp/stockreport" + timestring + ".xls"
     wbk.save(filename)
     filenames = [filename]
     mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["rajveer.singh@shop2020.in", "chaitnaya.vats@shop2020.in", "chandan.kumar@shop2020.in", "khushal.bhatia@shop2020.in", "rajneesh.arora@shop2020.in", "manoj.kumar@shop2020.in"], "Inventory Stock Report for " + timestring, body, [get_attachment_part(filename) for filename in filenames])

Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/inventory/script/DailyStockReportGenerator.py – Rev 10687 → 11879