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, catalog_item_id, category_id = sheet.row_values(rownum)[0:17]

        #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:

            #Check 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.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:

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