Subversion Repositories SmartDukaan

#!/usr/bin/python

'''

Creates a CSV report about availability and requirements of items.

Created on 11-Nov-2011

Updated on 02-Jan-2012

@author: Chandranshu

@author: Rajveer

'''

import optparse

import xlwt

from operator import itemgetter

import operator

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

    import sys

    import os

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

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

from shop2020.clients.CatalogClient import CatalogClient

from shop2020.clients.TransactionClient import TransactionClient

categories={}

catm = CategoryManager()

root_category = catm.getCategory(10000)

for child_cat_id in root_category.children_category_ids:

    category = catm.getCategory(child_cat_id)

    for cat_id in category.children_category_ids:

        categories[cat_id] = category.display_name

categories[10010] = 'Handsets' 

def main():

    parser = optparse.OptionParser()

    parser.add_option("-v", "--vendor", dest="vendorId",

                      type="int", default=1,

                      help="Create report for VENDOR",

                      metavar="VENDOR")

    parser.set_usage("%prog [options] <output_file_name>")

    (options, args) = parser.parse_args()

    if len(args) != 1:

        parser.error("Missing argument: output file name")

    inventory_client = CatalogClient().get_client()

    stocks_for_items_with_pending_orders = inventory_client.getPendingOrdersInventory(options.vendorId)

    txn_client = TransactionClient().get_client()

    item_wise_risky_orders = txn_client.getItemWiseRiskyOrdersCount()

    #[OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS]

    pending_orders = txn_client.getOrdersInBatch([3,5,35,36], 0, 0, 0)

    old_order_ids = []

    #f = open('/tmp/orderfile', 'r+')

    #for line in f:

    #    old_order_ids.append(int(line))

    #    print line

    item_quantity_list = {}

    for order in pending_orders:

        if order.warehouse_id not in [1,2,3,4,5]:

            continue

        if order.id in old_order_ids:

            continue

        #f.write(str(order.id)+"\n")    

        lineitem = order.lineitems[0]

        if item_quantity_list.has_key(lineitem.item_id):

            item_quantity_list[lineitem.item_id] = item_quantity_list[lineitem.item_id] + lineitem.quantity

        else:

            item_quantity_list[lineitem.item_id] = lineitem.quantity 

    print item_quantity_list

    wbk = xlwt.Workbook()

    sheet = wbk.add_sheet('main')

    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, "Category", heading_xf)

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

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

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

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

    sheet.write(0, 6, "Total Requirement", heading_xf)

    sheet.write(0, 7, "Actual Availability", heading_xf)

    sheet.write(0, 8, "Computed Availability", heading_xf)

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

    sheet.write(0, 10, "9D2", heading_xf)

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

    sheet.write(0, 12, "9D1", heading_xf)

    sheet.write(0, 13, "9C7", heading_xf)

    sheet.write(0, 14, "903", heading_xf)

    sheet.write(0, 15, "Pending Orders", heading_xf)

    sheet.write(0, 16, "Minimum Stock Level", heading_xf)

    sheet.write(0, 17, "Risky Orders", heading_xf)

    sheet.write(0, 18, "Deficit", heading_xf)

    xls_fields = []    

    i = 1

    for stock in stocks_for_items_with_pending_orders:

        itemId = stock.itemId

        if not item_quantity_list.has_key(itemId):

            continue

        item = inventory_client.getItem(itemId)

        category = categories[item.category]

        brand = item.brand

        model_name = item.modelName if item.modelName else ""

        model_number = item.modelNumber

        color = item.color if item.color else ""

        #pending_orders = stock.reserved

        pending_orders = item_quantity_list.get(itemId)

        min_stock_level = stock.minimumStock

        total_requirement = pending_orders + stock.minimumStock

        available = stock.available

        if total_requirement > available:

            deficit = total_requirement - available

        else:

            deficit = 0

        risky_orders = item_wise_risky_orders.get(itemId) if item_wise_risky_orders.has_key(itemId) else 0

        if risky_orders > available:

            bare_minimum_required = risky_orders - available

        else:

            bare_minimum_required = 0

        xls_field = {}

        xls_field['itemId'] = itemId

        xls_field['category'] = category

        xls_field['brand'] = brand

        xls_field['model_name'] = model_name

        xls_field['model_number'] = model_number

        xls_field['color'] = color

        xls_field['total_requirement'] = total_requirement

        xls_field['available'] = available

        xls_field['pending_orders'] = pending_orders

        xls_field['min_stock_level'] = min_stock_level

        xls_field['risky_orders'] = risky_orders

        xls_field['deficit'] = deficit

        xls_fields.append(xls_field)

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

    for xls_field in sorted_x:

        lineNo = str(i + 1)

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

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

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

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

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

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

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

        sheet.write(i, 7, xlwt.Formula('K' + lineNo + '+L' + lineNo + '+M' + lineNo + '+N' + lineNo + '+O' + lineNo))

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

        sheet.write(i, 9, "")

        sheet.write(i, 10, 0)

        sheet.write(i, 11, 0)

        sheet.write(i, 12, 0)

        sheet.write(i, 13, 0)

        sheet.write(i, 14, 0)

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

        sheet.write(i, 16, xls_field.get('min_stock_level'))

        sheet.write(i, 17, xls_field.get('risky_orders'))

        sheet.write(i, 18, xls_field.get('deficit'))

        i = i + 1

    wbk.save(args[0])

if __name__ == '__main__':

    main()