Subversion Repositories SmartDukaan

#!/usr/bin/python

from operator import itemgetter

from shop2020.clients.CatalogClient import CatalogClient

from shop2020.clients.InventoryClient import InventoryClient

from shop2020.model.v1.catalog.impl.CategoryManager import CategoryManager

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:

    1. Logistics location of the warehouse which was finally picked up to ship the order.

    2. Expected delay added by the category manager.

    3. 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.set_panes_frozen(True)

    sheet.set_horz_split_pos(1)

    sheet.set_remove_splits(True)

    excel_integer_format = '0'

    integer_style = xlwt.XFStyle()

    integer_style.num_format_str = excel_integer_format

    sheet.write(0, 0, "Item ID", heading_xf)

    sheet.write(0, 1, "Brand", heading_xf)

    sheet.write(0, 2, "Model Name", heading_xf)

    sheet.write(0, 3, "Model Number", heading_xf)

    sheet.write(0, 4, "Color", heading_xf)

    sheet.write(0, 5, "Shipping Days", heading_xf)

    sheet.write(0, 6, "Main Category", heading_xf)

    sheet.write(0, 7, "Sub Category", heading_xf)

    sheet.write(0, 8, "Risky", heading_xf)

    sheet.write(0, 9, "Sticky", heading_xf)

    sheet.write(0, 10, "Expected Delay", heading_xf)

    sheet.write(0, 11, "Availability", heading_xf)

    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)

    xls_fields = []    

#    for item in items:

#        if item.itemStatus == 3:

#            try:

#                fulfilmentWarehouseId, expected_delay, billingWarehouseId, sellingPrice, totalAvailability, weight = iclient.getItemAvailabilityAtLocation(item.id, 1)

#           except:

#               print "Unable to fetch inventory for item " + str(item.id)

#               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 = get_item_availability_for_location(item.id, 1)

            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

            for wh, inv in inventory.reserved.items():

                if wh != 16:

                    reserved = reserved + inv

            shipsBy = 'NOT AVAILABLE'

            if item.itemStatus == 3:

                if expected_delay == 0:

                    shipsBy = 'NEXT DAY'

                else:

                    shipsBy = 'NO NEXT DAY'

        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' : []}

        innerMap = statsMap.get(mainCategory)

        innerMap[shipsBy].append(item.catalogItemId)

        xls_field = {}

        xls_field['itemId'] = item.id

        xls_field['brand'] = removeNonAscii(item.brand)

        xls_field['modelName'] = removeNonAscii(item.modelName if item.modelName else "")

        xls_field['modelNumber'] = removeNonAscii(item.modelNumber if item.modelNumber else "")

        xls_field['color'] = removeNonAscii(item.color)

        xls_field['shippingDays'] = expected_delay

        xls_field['mainCategory'] = mainCategory

        xls_field['subCategory'] = catm.getCategory(item.category).display_name

        xls_field['risky'] = item.risky

        xls_field['sticky'] = item.isWarehousePreferenceSticky

        xls_field['expectedDelay'] = item.expectedDelay

        xls_field['availability'] = availability

        xls_field['reserved'] = reserved

        xls_field['status'] = status._VALUES_TO_NAMES[item.itemStatus]

        xls_field['cmsId'] = item.catalogItemId

        xls_field['shipsBy'] = shipsBy

        xls_fields.append(xls_field)

    sorted_x = sorted(xls_fields, key=operator.itemgetter('shipsBy'))

    i = 1

    for xls_field in sorted_x:

        sheet.write(i, 0, xls_field.get('itemId'))

        sheet.write(i, 1, xls_field.get('brand'))

        sheet.write(i, 2, xls_field.get('modelName'))

        sheet.write(i, 3, xls_field.get('modelNumber'))

        sheet.write(i, 4, xls_field.get('color'))

        sheet.write(i, 5, xls_field.get('shippingDays'))

        sheet.write(i, 6, xls_field.get('mainCategory'))

        sheet.write(i, 7, xls_field.get('subCategory'))

        sheet.write(i, 8, xls_field.get('risky'))

        sheet.write(i, 9, xls_field.get('sticky'))

        sheet.write(i, 10, xls_field.get('expectedDelay'))

        sheet.write(i, 11, xls_field.get('availability'))

        sheet.write(i, 12, xls_field.get('reserved'))

        sheet.write(i, 13, xls_field.get('status'))

        sheet.write(i, 14, xls_field.get('cmsId'))

        sheet.write(i, 15, xls_field.get('shipsBy'))

        i = i + 1

    body = "<html><body><table border='1'><tr><th>Category</th><th>TOTAL AVAILABLE - Skus</th><th>NEXT DAY - Skus</th><th>NOT AVAILABLE - Skus</th><th>TOTAL AVAILABLE - Products</th><th>NEXT DAY - Products</th><th>NOT AVAILABLE - Products</th></tr>"

    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])

def removeNonAscii(s): return "".join(i for i in s if ord(i)<128)

if __name__ == '__main__':

    main()