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
from elixir import *

def load_item_data(filename, category, full_update, dry_run):
    DataService.initialize('catalog')

    workbook = xlrd.open_workbook(filename)
    sheet = workbook.sheet_by_index(0)
    num_rows = sheet.nrows
    updatedOn = datetime.datetime.now()
    new_items = []
    updated_items = []
    existing_items = Item.query.filter_by(status=status.ACTIVE, hotspotCategory=category).all()
    existing_items_set = set([item.brand+';'+item.model_number+';'+item.color for item in existing_items])
    for rownum in range(1, num_rows):
        #print sheet.row_values(rownum)
        catalog_item_id, category_id, product_group = [None, None, None]
        
        #hotspot_category, brand, model_number, color, model_name, mrp, mop, dp, xfer_price, weight, start_date, deal_text, deal_value, comments, catalog_item_id, category_id = sheet.row_values(rownum)[0:17]
        brand, model_number, model_name, color, dp, mrp, mop, comments, hotspot_category, unused_feature, xfer_price, weight, start_date, deal_text, deal_value = sheet.row_values(rownum)[0:15]
        #product_group, brand, model_number, color, xfer_price, mop, mrp, catalog_item_id, category_id = sheet.row_values(rownum)[0:9]
        if isinstance(model_number, float):
            model_number = str(int(model_number))
        
        key = brand + ';' + model_number + ';' + color
        if key in existing_items_set:
            existing_items_set.remove(key)
        item = Item.get_by(brand=brand, model_number=model_number, color=color, hotspotCategory=category)
        #item = Item.get_by(product_group=product_group, brand=brand, model_number=model_number, color=color)
        
        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 = brand
            item.model_number = model_number
            item.color = color
            item.status = status.IN_PROCESS
            item.addedOn = updatedOn
            item.hotspotCategory = category
            session.add(item)
            
            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
            else:
                '''
                This is to be used for accessories.
                '''
                # Check if a similar item already exists in our database
                #similar_item = Item.query.filter_by(product_group=product_group, brand = brand, model_number=model_number).first()
                #print "[SIMILAR ITEM FOUND:] FOR {0} {1} {2} {3}".format(product_group, brand, model_number, color)
                
                similar_item = Item.query.filter_by(brand = brand, model_number=model_number, hotspotCategory=category).first()
                print "[SIMILAR ITEM FOUND:] FOR {0} {1} {2} {3}".format(category, 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 = status.ACTIVE
        else:
            if item.mrp != mrp or item.dealerPrice != dp or item.transfer_price != xfer_price:
                updated_items.append(sheet.row_values(rownum)[0:15] + [item.mrp, item.dealerPrice, item.transfer_price])

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

        if dp != "":
            item.dealerPrice = dp
            item.sellingPrice = dp
        
        if mrp != "" and mop != "" and mrp <  mop:
            print "[BAD MRP and MOP:] for {0} {1} {2} {3}. MRP={4}. MOP={5}".format(product_group, brand, model_number, color, mrp, mop)

        if mop != "" and xfer_price != "" and xfer_price > mop:
            print "[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:
                print "[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 mop != "":
            item.mop = mop
            item.sellingPrice = mop

        if xfer_price !="":
            item.transfer_price = xfer_price
        
        item.startDate = datetime.datetime.now()

        item.model_name = model_name

        if weight != "":    
            item.weight = weight
#        if start_date != "":
#            item.startDate = datetime.datetime(*xlrd.xldate_as_tuple(start_date, workbook.datemode))
        
#        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_items_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) + ", 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("-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.set_defaults(full_update=True, dry_run=False, category="Handsets")
    (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.category, options.full_update, options.dry_run)

if __name__ == '__main__':
    main()