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
'''
from operator import itemgetter
from shop2020.clients.CatalogClient import CatalogClient
from shop2020.clients.InventoryClient import InventoryClient
from shop2020.clients.TransactionClient import TransactionClient
from shop2020.model.v1.catalog.impl.CategoryManager import CategoryManager
import operator
import optparse
import xlwt


if __name__ == '__main__' and __package__ is None:
    import sys
    import os
    sys.path.insert(0, os.getcwd())

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 = InventoryClient().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, 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,6,9]:
            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, "9I5", 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
        
        catalog_client = CatalogClient().get_client()
        item = catalog_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()