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

if __name__ == '__main__' and __package__ is None:

    import sys

    import os

    sys.path.insert(0, os.getcwd())

categories={}

catm = CategoryManager()

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)

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

            inventory = iclient.getItemInventoryByItemId(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:

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

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