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.DataAcessors import get_item_availability_for_location
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_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')
    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 not in (16, 1771):
                    availability = availability + inv
            for wh, inv in inventory.reserved.items():
                if wh not in (16, 1771):
                    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", ["anikendra.das@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()