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