Subversion Repositories SmartDukaan

#!/usr/bin/python

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.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
        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])

        item.product_group = product_group
        item.brand = our_brand
        item.model_number = our_model_number
        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 item.startDate == None or item.startDate == '':
            item.startDate = datetime.datetime.now()
        else:
            item.startDate = datetime.datetime(*xlrd.xldate_as_tuple(start_date, workbook.datemode))

        item.sellingPrice = sp
        item.model_name = model_name

        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):
    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:
        for item in items:
            print item
            items_writer.writerow([str(value) for value in item])
    else:
        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()