Subversion Repositories SmartDukaan

#!/usr/bin/python

'''

This script is used to load item details in the catalog database.

It's now mostly used for Accessories since they come in huge numbers.

@author: Chandranshu

'''

import optparse

import csv

import xlrd

import datetime

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

    import sys

    import os

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

from shop2020.thriftpy.model.v1.catalog.ttypes import status

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

from shop2020.model.v1.catalog.impl.DataService import Item, EntityIDGenerator,\

    ItemChangeLog, Vendor, VendorItemPricing, VendorItemMapping

from elixir import *

def load_item_data(filename, vendorId, category, full_update, dry_run, supplied_product_group):

    DataService.initialize('catalog')

    vendor = Vendor.get_by(id=vendorId)

    if vendor is None:

        raise Exception("No vendor found for the id: " + str(vendorId))

    workbook = xlrd.open_workbook(filename)

    sheet = workbook.sheet_by_index(0)

    num_rows = sheet.nrows

    updatedOn = datetime.datetime.now()

    new_items = []

    updated_items = []

    not_created_items = []

    existing_vendor_item_mappings = VendorItemMapping.query.filter_by(vendor=vendor, vendor_category=category).all()

    existing_vendor_item_mappings_set = set([mapping.item_key for mapping in existing_vendor_item_mappings])

    for rownum in range(1, num_rows):

        print sheet.row_values(rownum)

        if supplied_product_group != None and supplied_product_group != '':

            our_brand, our_model_number, our_model_name, our_color,\

            brand, model_number, model_name, color,\

            dp, mrp, mop, sp, xfer_price,\

            comments, weight, start_date, deal_text, deal_value,\

            warranty_period, defaultWarehouse, preferredVendor = sheet.row_values(rownum)[0:21]

            product_group = supplied_product_group

        else:

            our_brand, our_model_number, our_model_name, our_color,\

            product_group, brand, model_number, model_name, color,\

            dp, mrp, mop, sp, xfer_price,\

            comments, weight, start_date, deal_text, deal_value,\

            warranty_period, defaultWarehouse, preferredVendor = sheet.row_values(rownum)[0:22]

        print product_group

        if isinstance(model_number, float):

            model_number = str(int(model_number))

        if our_brand == '':

            our_brand = brand

        if our_model_number == '':

            our_model_number = model_number

        if our_model_name == '':

            our_model_name = model_name

        if our_color == '':

            our_color = color

        if sp == '':

            sp = mop 

        item = None

        similar_item = None

        vendor_item_pricing = None

        key = product_group.strip().lower() + '|' + brand.strip().lower() + '|' + model_number.strip().lower() + '|' + color.strip().lower()

        if key in existing_vendor_item_mappings_set:

            existing_vendor_item_mappings_set.remove(key)

        # Check if a similar items already exists in our database

        similar_items = Item.query.filter_by(brand=our_brand.strip(), model_number=our_model_number.strip(), model_name=our_model_name.strip()).all()

        # Check if a similar item already exists in our database

        for old_item in similar_items:

            if old_item.color != None and old_item.color.strip().lower() == our_color.strip().lower():

                item = old_item

                break

        # Check if a similar item already exists in our database with out valid color if similar item with same color is not found

        if item is None:

            for old_item in similar_items:

                if not check_color_valid(old_item.color):

                    item = old_item

                    break

        i = 0

        color_of_similar_item = None

        # Check if a similar item already exists in our database to be used to get catalog_item_id

        for old_item in similar_items:

            # get a similar item already existing in our database with valid color

            if check_color_valid(old_item.color):

                similar_item = old_item

                color_of_similar_item = similar_item.color

                break

            i = i + 1

            # get a similar item already existing in our database if similar item with valid color is not found

            if i == len(similar_items):

                similar_item = old_item

                color_of_similar_item = similar_item.color

        # Check if a similar item that is obtained above is having a valid color

        if check_color_valid(color_of_similar_item):

            # if a similar item that is obtained above is having a valid color and new item is about to be created with out valid color it is not done.

            # since for example if their is a item with red color in our database and we are creating a new item with no color for the same product which is wrong.

            if item is None and not check_color_valid(our_color):

                not_created_items.append(rownum)

                continue

        if item is None:

            #print "[ADDING:]{0} {1} {2} {3} to our catalogue.".format(brand, model_number, model_name, color)

            new_items.append(rownum)

            item = Item()

            item.product_group = product_group.strip()

            item.brand = our_brand.strip()

            item.model_number = our_model_number.strip()

            item.model_name = our_model_name.strip()

            item.color = our_color.strip()

            item.status = status.IN_PROCESS

            item.status_description = "This item is in process"

            item.addedOn = updatedOn

            item.hotspotCategory = category

            if category == 'Handsets':

                item.preferredWarehouse = 1

            else:

                item.preferredWarehouse = 2

            vendor_item_mapping = VendorItemMapping(vendor=vendor, item=item, item_key=key, vendor_category=category)

            vendor_item_pricing = VendorItemPricing(vendor=vendor, item=item)

            session.add(item)

            session.add(vendor_item_mapping)

            session.add(vendor_item_pricing)

            if similar_item is None or similar_item.catalog_item_id is None:

                # If there is no similar item in the database from before,

                # use the entity_id_generator

                entity_id = EntityIDGenerator.query.first()

                item.catalog_item_id = entity_id.id + 1

                entity_id.id = entity_id.id  + 1

                if similar_item is not None and similar_item.catalog_item_id is None:

                    similar_item.catalog_item_id = entity_id.id

            else:

                #If a similar item already exists for a product group, brand and model_number, set it as same.

                item.catalog_item_id = similar_item.catalog_item_id

                item.category = similar_item.category

                item.status = similar_item.status

                item.status_description = similar_item.status_description

                #Use the same brand, model name and model number as in similar item in database

                item.brand = similar_item.brand

                item.model_name = similar_item.model_name

                item.model_number = similar_item.model_number

        else:

            # If this item already existed and one of its price parameters has changed

            # in which case we add it to the list of updated items for reporting.

            if item.status == status.PHASED_OUT:

                item.status = status.IN_PROCESS #Not the ideal choice but we don't know whether content has been generated for it beforehand.

                item.status_description = "This item is in process"

            if check_color_valid(our_color) and (item.color == None or item.color.strip().lower() != our_color.strip().lower()):

                item.color = our_color.strip()

            vendor_item_mapping = VendorItemMapping.get_by(vendor=vendor, item=item)

            if vendor_item_mapping is None:

                vendor_item_mapping = VendorItemMapping(vendor=vendor, item=item, item_key=key, vendor_category=category)

                session.add(vendor_item_mapping)

            else:

                vendor_item_mapping.item_key = key

                vendor_item_mapping.vendor_category = category

            vendor_item_pricing = VendorItemPricing.get_by(vendor=vendor, item=item)

            if vendor_item_pricing is None:

                vendor_item_pricing = VendorItemPricing(vendor=vendor, item=item)

                session.add(vendor_item_pricing)

            if item.mrp != mrp or vendor_item_pricing.dealerPrice != dp or vendor_item_pricing.transfer_price != xfer_price or item.sellingPrice != sp:

                updated_items.append(sheet.row_values(rownum)[0:19] + [item.mrp, item.dealerPrice, item.transfer_price])

        if dp != "":

            item.dealerPrice = dp

            item.sellingPrice = dp

            vendor_item_pricing.dealerPrice = dp

        if mrp != "" and mop != "" and mrp <  mop:

            raise Exception("[BAD MRP and MOP:] for {0} {1} {2} {3}. MRP={4}. MOP={5}".format(brand, model_number, model_name, color, mrp, mop))

        if mrp != "" and sp != "" and mrp < sp:

            raise Exception("[BAD MRP and SP:] for {0} {1} {2} {3}. MRP={4}. SP={5}".format(brand, model_number, model_name, color, mrp, sp))

        if mop != "" and xfer_price != "" and xfer_price > mop:

            raise Exception("[BAD MOP and TP:] for {0} {1} {2} {3}. TP={4}. MOP={5}".format(brand, model_number, model_name, color, xfer_price, mop))

#        if mrp != "":

#            if item.mrp == None or item.mrp >= mrp:

#                item.mrp = mrp

#            else:

#                raise Exception("[NEW MRP MORE THAN old MRP:] for {0} {1} {2} {3}. Old mrp={4}. New MRP={5}".format(brand, model_number, model_name, color, item.mrp, mrp))

        if defaultWarehouse == None or defaultWarehouse == "":

            raise Exception("[Default Warehouse required:] for {0} {1} {2} {3}".format(brand, model_number, model_name, color))

        else:

            try:

                item.defaultWarehouse = int(defaultWarehouse)

            except:

                raise Exception("[Default Warehouse should be number:] for {0} {1} {2} {3}. defaultWarehouse={4}".format(brand, model_number, model_name, color, defaultWarehouse))

        if preferredVendor == None or preferredVendor == "":

            raise Exception("[Preferred Vendor required:] for {0} {1} {2} {3}".format(brand, model_number, model_name, color))

        else:

            try:

                item.preferredVendor = int(preferredVendor)

            except:

                raise Exception("[Preferred Vendor should be number:] for {0} {1} {2} {3}. preferredVendor={4}".format(brand, model_number, model_name, color, preferredVendor))

        if warranty_period != "":

            try:

                item.warranty_period = int(warranty_period)

            except:

                pass               

        if mrp != "":

            item.mrp = mrp

        if mop != "":

            item.mop = mop

            vendor_item_pricing.mop = mop

        if xfer_price !="":

            item.transfer_price = xfer_price

            vendor_item_pricing.transfer_price = xfer_price

        if start_date is not None and start_date != '':

            #If a start date has been specified, it takes precedence.

            item.startDate = datetime.datetime(*xlrd.xldate_as_tuple(start_date, workbook.datemode))

        elif item.startDate == None or item.startDate == '': 

            #If start date is not specified and item's start date is not set, set it to current time

            item.startDate = datetime.datetime.now()

        item.sellingPrice = sp

        if weight != "":    

            item.weight = weight

#        item.bestDealText = deal_text

#        if deal_value != "":

#            item.bestDealValue = deal_value

#        item.comments = comments

        item.updatedOn = updatedOn

        item_change_log = ItemChangeLog()

        item_change_log.new_status = item.status

        item_change_log.timestamp = updatedOn

        item_change_log.item = item

        if not dry_run:

            session.commit()

    write_report("items_not_created_dueto_color.csv", not_created_items, sheet, False)

    write_report("new_items.csv", new_items, sheet, False)

    write_report("updated_items.csv", updated_items, sheet, True)

    phased_out_items = [key.split('|') for key in list(existing_vendor_item_mappings_set)]

    write_report("phased_out_items.csv", phased_out_items, sheet, True)

    if (not dry_run) and full_update:

        query_string =  "UPDATE " + str(Item.table) + " SET status=" + str(status.PHASED_OUT) + ", status_description='This item has been phased out'" + ", updatedOn='"+ str(updatedOn) +"'"+\

                        " WHERE updatedOn <> '" + str(updatedOn) + "' AND hotspotCategory='" + category +"'";

        session.execute(query_string, mapper=Item)

        session.commit()

    print "Successfully updated the item list information."

def check_color_valid(color):

    if color is not None:

        color = color.strip().lower()

        if color != '' and color != 'na' and color != 'blank' and color != '(blank)':

            return True

    return False

def write_report(filename, items, sheet, is_item):

    '''

    Iterates through the items list and writes all the values to the specified

    filename in the CSV format. 'is_item' indicates whether the list consists

    of row numbers or complete row data.

    '''

    items_writer = csv.writer(open(filename, "wb"), delimiter=',', quoting=csv.QUOTE_ALL)

    items_writer.writerow(sheet.row_values(0) + ['Old MRP', 'Old DP', 'Old TP'])

    if is_item:

        #The list contains the complete rows.

        for item in items:

            print item

            items_writer.writerow([str(value) for value in item])

    else:

        #The list only has row numbers. We've to fetch the data ourselves.

        for i in items:

            print sheet.row_values(i)

            items_writer.writerow([str(value) for value in sheet.row_values(i)])

def main():

    parser = optparse.OptionParser()

    parser.add_option("-f", "--file", dest="filename",

                   default="ItemList.xls", type="string",

                   help="Read the item list from FILE",

                   metavar="FILE")

    parser.add_option("-c", "--category", dest="category",

                      type="string",

                      help="Update the list only for the products belonging to CATEGORY",

                      metavar="CATEGORY")

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

                      type="int",

                      help="Update the pricing information for VENDOR",

                      metavar="VENDOR")

    parser.add_option("-d", "--dry-run", dest="dry_run",

                      action="store_true",

                      help="Dry run only reporting on pending changes.Please note that some of the items can be reported twice.")

    parser.add_option("-a", "--full", dest="full_update",

                      action="store_true",

                      help="In a full update, all older items are marked as PHASED_OUT. Also see -pm.")

    parser.add_option("-p", "--partial", dest="full_update",

                      action="store_false",

                      help="In a partial update, older items are left as is. Also see -fm.")

    parser.add_option("-g", "--product-group", dest="product_group",

                      type="string",

                      help="Set GROUP as the product group for all items added/updated during this run.",

                      metavar="GROUP")

    parser.set_defaults(full_update=True, dry_run=False, category="Handsets", vendor=1)

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

    filename = options.filename

    load_item_data(filename, options.vendor, options.category, options.full_update, options.dry_run, options.product_group)

if __name__ == '__main__':

    main()