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, 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).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, preferredWarehouse, preferredVendor = sheet.row_values(rownum)[0:22]
            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, preferredWarehouse, preferredVendor = sheet.row_values(rownum)[0:23]

        print product_group

        if isinstance(model_number, float):
            model_number = str(int(model_number))
        
        if isinstance(our_model_number, float):
            our_model_number = str(int(our_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.brand = our_brand.strip()
            item.product_group = product_group.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
            
            vendor_item_mapping = VendorItemMapping(vendor=vendor, item=item, item_key=key)
            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.product_group = similar_item.product_group
                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)
                session.add(vendor_item_mapping)
            else:
                vendor_item_mapping.item_key = key
            
            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, vendor_item_pricing.dealerPrice, vendor_item_pricing.transfer_price])
        
        if 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 preferredWarehouse != "":
            try:
                item.preferredWarehouse = int(preferredWarehouse)
            except:
                pass
        
        if mrp != "":
            item.mrp = mrp
        
        if mop != "":
            vendor_item_pricing.mop = mop

        if 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) + "'";
        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("-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, 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.full_update, options.dry_run, options.product_group)

if __name__ == '__main__':
    main()