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 = []
    
    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)
        catalog_item_id, category_id = [None, None]
        
        if supplied_product_group != None and supplied_product_group != '':
            our_brand, our_model_number, our_color,\
            brand, model_number, model_name, color,\
            dp, mrp, mop, sp,\
            comments, hotspot_category, unused_feature, xfer_price, weight, start_date, deal_text, deal_value = sheet.row_values(rownum)[0:19]
            product_group = supplied_product_group
        else:
            our_brand, our_model_number, our_color,\
            product_group, brand, model_number, model_name, color,\
            dp, mrp, mop, sp,\
            comments, hotspot_category, unused_feature, xfer_price, weight, start_date, deal_text, deal_value = sheet.row_values(rownum)[0:20]

        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_color == '':
            our_color = color
        
        if sp == '':
            sp = mop 
        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)
            item = VendorItemMapping.query.filter_by(vendor=vendor, item_key=key, vendor_category=category).one().item
        
        if item is None:
            #print "[ADDING:]{0} {1} {2} {3} to our catalogue.".format(product_group, brand, model_number, color)
            new_items.append(rownum)
            item = Item()
            item.product_group = product_group
            item.brand = our_brand
            item.model_number = our_model_number
            item.model_name = model_name
            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 catalog_item_id !=None and catalog_item_id != "":
                #Add category and entity id
                item.catalog_item_id = int(catalog_item_id)
                item.category = int(category_id)
                item.status = status.ACTIVE
                item.status_description = "This item is active"
            else:
                # Check if a similar item already exists in our database
                similar_item = Item.query.filter_by(product_group=product_group, brand = our_brand, model_number=our_model_number, hotspotCategory=category).first()
                print "[SIMILAR ITEM FOUND:] FOR {0} {1} {2} {3}".format(product_group, brand, model_number, color)
                             
                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
                    
        
                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"
            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])
        
        #In future we will provide the option to edit color in CMS
        item.color = our_color

        if category_id !=None and category_id != "":
            item.category = int(category_id)

        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(product_group, brand, model_number, 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(product_group, brand, model_number, 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(product_group, brand, model_number, 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(product_group, brand, model_number, color, item.mrp, mrp))
        
        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("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 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()