Subversion Repositories SmartDukaan

#!/usr/bin/python
'''
This script is used to match our inventory report with that of a
vendor. Run the script with -h option to learn more.

@author: Chandranshu
'''
import optparse
import sys
import csv
import xlrd

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

def read_vendor_item_mapping(filename):
    '''
    Returns a dictionary containing the item id corresponding to the vendor key of a product.
    This is used to read the inventory data we get from the warehouse.
    '''
    reader = csv.reader(open(filename, "rb"), dialect=csv.excel_tab)
    vendor_item_mapping = {}
    
    try:
        for row in reader:
            if reader.line_num == 1:
                continue
            key = row[0].strip()
            val = int(row[1])
            vendor_item_mapping[key]=val
    except csv.Error, e:
        sys.exit('file %s, line %d: %s' % (filename, reader.line_num, e))

    return vendor_item_mapping     

def read_inventory_data(filename, vendor_mapping_filename, working_product_group):
    '''
    Returns a dictionary containing the availability of items in warehosue keyed by
    the id of the item in our catalog. 
    
    Parameters:
     - filename: The XLS file to read the warehouse inventory from.
     - vendor_mapping_filename: The CSV file to read the item mapping from.
     - working_product_group: The product group to use overriding the one specified in the inventory report.
    '''
    vendor_item_mapping = read_vendor_item_mapping(vendor_mapping_filename);
    workbook = xlrd.open_workbook(filename)
    sheet = workbook.sheet_by_index(0)
    num_rows = sheet.nrows
    availability = {}
    for rownum in range(1, num_rows):
        unused_company, unused_division, unused_store_name, unused_store_code, unused_product_catalog, product_group, brand, model_number, color, unused_item_number, quantity = sheet.row_values(rownum)[0:11]
        if isinstance(model_number, float):
            model_number = str(int(model_number))
        itemid = product_group.strip() + "|" + brand.strip() + "|" + model_number.strip() + "|" + color.strip()
        itemid = itemid.lower()
        
        key = str(itemid)
        if vendor_item_mapping.has_key(itemid):
            key = vendor_item_mapping[itemid]
 
        if availability.has_key(key):
            quantity = quantity + availability.get(key)
       
        availability[key] = quantity
    
    return availability

def read_local_inventory(filename):
    '''
    Returns the following objects:
     1. A dictionary keying the availability of items in our catalog by
      the id of the item in our catalog.
     2. A list of items whose inventory is zero.
     3. A dictionary mapping the item id to the availability in our catalog. 

    Parameters:
     - filename : File to read the local inventory data from
    '''
    reader = csv.reader(open(filename, "rb"))
    availability = {}
    zero_inventory_items = [['Id', 'Product Group', 'Brand', 'Model', 'Color']]
    items = {}
    try:
        for row in reader:
            if reader.line_num == 1:
                continue
            key = int(row[0])
            val = int(row[5])
            availability[key]=val
            items[key] = [row[1], row[2], row[3], row[4]]
            if val == 0:
                zero_inventory_items.append([row[0], row[1], row[2], row[3], row[4]])
    except csv.Error, e:
        sys.exit('file %s, line %d: %s' % (filename, reader.line_num, e))
    return availability, zero_inventory_items, items

def write_report(filename, item_list):
    '''
    Writes the elements of the given list in the specified file. It uses the
    default CSV writer and hence will fail if the elements of the list use 
    Unicode characters.
    
    Parameters:
     - filename: File to write report to. Existing file, if any, is truncated.
     - item_list: List whose elements have to be written.
    '''
    print "Writing report to: " + filename
    writer = csv.writer(open(filename, "wb"), delimiter=',', quoting=csv.QUOTE_MINIMAL)
    for item in item_list:
        writer.writerow(item)

def compare_stocks(inventory_file, closing_stock_file, vendor_mapping_file, date_string, product_group):
    '''
    Does not return anything. Creates three reports by matching the data in
    the two given reports:
    1. Mismatch Report: Lists the differences in stock situation between our
                        inventory and warehouse.
    2. Missing Report: Lists the items which are present in the warehouse report
                       but absent in our catalog.
    3. Zero Inventory Report: Lists the items for which we have no inventory.
    
    Parameters:
     - inventory_file: The file containing our inventory details. The file
           should be in CSV format. It should have 5 columns for Product 
           Group, Brand, Model, Color and availability.
     - closing_stock_file: The file containing the inventory situation in
           one of the warehouses.
     - date_string: The date these reports are for. Is used to create reports. 
    '''
    closing_stock = read_inventory_data(closing_stock_file, vendor_mapping_file, product_group)
    our_inventory, zero_inventory_items, items = read_local_inventory(inventory_file)
    
    print closing_stock
    print our_inventory
    
    items_with_inventory_mismatch = [['Id', 'Product Group', 'Brand', 'Model', 'Color', 'Online Retail Stock', 'Hotspot Stock']]
    items_missing_in_catalog = [['Product Group', 'Brand', 'Model', 'Color']]
    for key,value in closing_stock.iteritems():
        if our_inventory.has_key(key):
            if our_inventory[key]!= value:
                mismatch_list=[key] + items[key]
                mismatch_list.append(our_inventory[key])
                mismatch_list.append(int(value))
                items_with_inventory_mismatch.append(mismatch_list)
        else:
            items_missing_in_catalog.append(str(key).split('|'))
    
    write_report("mismatch-" + date_string + ".csv", items_with_inventory_mismatch)
    write_report("missing-" + date_string + ".csv", items_missing_in_catalog)
    write_report("zero-inventory-" + date_string + ".csv", zero_inventory_items)

def main():
    parser = optparse.OptionParser()
    parser.add_option("-i", "--inventory", dest="inventory_file",
                   default="inventory-report.csv", type="string",
                   help="Read the item inventory from INVENTORY_FILE",
                   metavar="INVENTORY_FILE")
    parser.add_option("-c", "--closingstock", dest="closing_stock_file",
                   default="closing_stock.xls", type="string",
                   help="Read the closing stock from CLOSING_STOCK_FILE",
                   metavar="CLOSING_STOCK_FILE")
    parser.add_option("-v", "--vendor-mapping", dest="vendor_mapping_file",
                      default="vendor_mapping_1.csv", type="string",
                      help="Read the vendor item mapping from VENDOR_MAPPING_FILE",
                      metavar="VENDOR_MAPPING_FILE")
    parser.add_option("-d", "--date", dest="date_string",
                   default="xxxxxxxx", type="string",
                   help="Append DATE string to the names of the generated files",
                   metavar="DATE")
    parser.add_option("-p", "--product-group", dest="product_group",
                      default=None, type="string",
                      help="Assume PRODUCT_GROUP to be the product group of all items",
                      metavar="PRODUCT_GROUP")
    (options, args) = parser.parse_args()
    if len(args) != 0:
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
    compare_stocks(options.inventory_file, options.closing_stock_file, options.vendor_mapping_file, options.date_string, options.product_group)

if __name__ == '__main__':
    main()