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