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