Subversion Repositories SmartDukaan

Rev

Rev 873 | Rev 1089 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 873 Rev 1080
Line 1... Line 1...
1 
#!/usr/bin/python
 1 
#!/usr/bin/python
- 
 
 2 
 
2 
import optparse
 3 
import optparse
- 
 
 4 
import csv
3 
import xlrd
 5 
import xlrd
4 
import datetime
 6 
import datetime
5 
 
 7 
 
6 
if __name__ == '__main__' and __package__ is None:
 8 
if __name__ == '__main__' and __package__ is None:
7 
    import sys
 9 
    import sys
Line 12... Line 14...
12 
from shop2020.model.v1.catalog.impl import DataService
 14 
from shop2020.model.v1.catalog.impl import DataService
13 
from shop2020.model.v1.catalog.impl.DataService import Item, EntityIDGenerator,\
 15 
from shop2020.model.v1.catalog.impl.DataService import Item, EntityIDGenerator,\
14 
    ItemChangeLog
 16 
    ItemChangeLog
15 
from elixir import *
 17 
from elixir import *
16 
 
 18 
 
17 
def load_item_data(filename):
 19 
def load_item_data(filename, category, full_update, dry_run):
18 
    DataService.initialize()
 20 
    DataService.initialize()
19 
 
 21 
 
20 
    workbook = xlrd.open_workbook(filename)
 22 
    workbook = xlrd.open_workbook(filename)
21 
    sheet = workbook.sheet_by_index(0)
 23 
    sheet = workbook.sheet_by_index(0)
22 
    num_rows = sheet.nrows
 24 
    num_rows = sheet.nrows
23 
    updatedOn = datetime.datetime.now()
 25 
    updatedOn = datetime.datetime.now()
- 
 
 26 
    new_items = []
- 
 
 27 
    old_items = []
24 
    for rownum in range(1, num_rows):
 28 
    for rownum in range(1, num_rows):
25 
        print sheet.row_values(rownum)
 29 
        #print sheet.row_values(rownum)
- 
 
 30 
        catalog_item_id, category_id, product_group = [None, None, None]
26 
        #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]
 31 
        #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]
27 
        brand, model_number, model_name, color, dp, mrp, unused_fbp, 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]
 32 
        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]
- 
 
 33 
        #product_group, brand, model_number, color, xfer_price, mop, mrp, catalog_item_id, category_id = sheet.row_values(rownum)[0:9]
28 
        if isinstance(model_number, float):
 34 
        if isinstance(model_number, float):
29 
            model_number = str(int(model_number))
 35 
            model_number = str(int(model_number))
- 
 
 36 
        item = Item.get_by(brand=brand, model_number=model_number, color=color, hotspotCategory=category)
30 
        item = Item.get_by(manufacturer_name=brand, model_number=model_number, color=color)
 37 
        #item = Item.get_by(product_group=product_group, brand=brand, model_number=model_number, color=color)
- 
 
 38
31 
        if item is None:
 39 
        if item is None:
32 
            print "[ADDING:]{0} {1} {2} to our catalogue.".format(brand, model_number, color)
 40 
            #print "[ADDING:]{0} {1} {2} {3} to our catalogue.".format(product_group, brand, model_number, color)
- 
 
 41 
            new_items.append(rownum)
33 
            item = Item()
 42 
            item = Item()
- 
 
 43 
            #item.product_group = product_group
34 
            item.manufacturer_name = brand
 44 
            item.brand = brand
35 
            item.model_number = model_number
 45 
            item.model_number = model_number
36 
            item.color = color
 46 
            item.color = color
37 
            item.status = status.IN_PROCESS
 47 
            item.status = status.IN_PROCESS
38 
            item.addedOn = updatedOn
 48 
            item.addedOn = updatedOn
39 
            session.add(item)
 49 
            session.add(item)
40 
        '''
50
- 
 
 51 
            if catalog_item_id !=None and catalog_item_id != "":
- 
 
 52 
                #Add category and entity id
- 
 
 53 
                item.catalog_item_id = int(catalog_item_id)
41 
        #Set the catalog_item_id.
 54 
                item.category = int(category_id)
- 
 
 55 
                item.status = status.ACTIVE
- 
 
 56 
            else:
- 
 
 57 
                '''
- 
 
 58 
                This is to be used for accessories.
- 
 
 59 
                '''
- 
 
 60 
                # Check if a similar item already exists in our database
42 
        similar_item = Item.query.filter_by(manufacturer_name = brand, model_number=model_number).first()
 61 
                #similar_item = Item.query.filter_by(product_group=product_group, brand = brand, model_number=model_number).first()
- 
 
 62 
                #print "[SIMILAR ITEM FOUND:] FOR {0} {1} {2} {3}".format(product_group, brand, model_number, color)
- 
 
 63
- 
 
 64 
                similar_item = Item.query.filter_by(brand = brand, model_number=model_number, hotspotCategory=category).first()
- 
 
 65 
                print "[SIMILAR ITEM FOUND:] FOR {0} {1} {2} {3}".format(category, brand, model_number, color)
43 
        print similar_item
 66
44 
        if similar_item is None or similar_item.catalog_item_id is None:
 67 
                if similar_item is None or similar_item.catalog_item_id is None:
- 
 
 68 
                    # If there is no similar item in the database from before,
45 
            #Use the entity_id_generator
 69 
                    # use the entity_id_generator
46 
            entity_id = EntityIDGenerator.query.first()
 70 
                    entity_id = EntityIDGenerator.query.first()
47 
            item.catalog_item_id = entity_id.id + 1
 71 
                    item.catalog_item_id = entity_id.id + 1
48 
            entity_id.id = entity_id.id  + 1
 72 
                    entity_id.id = entity_id.id  + 1
- 
 
 73 
                else:
- 
 
 74 
                    #If a similar item already exists for a product group, brand and model_number, set it as same.
- 
 
 75 
                    item.catalog_item_id = similar_item.catalog_item_id
- 
 
 76 
                    item.category = similar_item.category
- 
 
 77 
                    #item.status = status.ACTIVE
49 
        else:
 78 
        else:
50 
            #If it already exists for a brand and model_number, set it as same.
 - 
 
51 
            item.catalog_item_id = similar_item.catalog_item_id
 79 
            old_items.append(rownum)
52 
        '''
 - 
 
53
80 
 
54 
        #Add category and entity id
 - 
 
55 
        item.catalog_item_id = int(catalog_item_id)
 81 
        if category_id !=None and category_id != "":
56 
        item.category = int(category_id)
 82 
            item.category = int(category_id)
- 
 
 83 
 
57 
        item.status = status.ACTIVE
 84 
        if dp != "":
58
85 
            item.dealerPrice = dp
59 
        item.hotspotCategory = hotspot_category
 86 
            item.sellingPrice = dp
60
87
61 
        item.model_name = model_name
 88 
        if mrp != "" and mop != "" and mrp <  mop:
- 
 
 89 
            print "[BAD MRP and MOP:] for {0} {1} {2} {3}. MRP={4}. MOP={5}".format(product_group, brand, model_number, color, mrp, mop)
- 
 
 90 
 
- 
 
 91 
        if mop != "" and xfer_price != "" and xfer_price > mop:
- 
 
 92 
            print "[BAD MOP and TP:] for {0} {1} {2} {3}. TP={4}. MOP={5}".format(product_group, brand, model_number, color, xfer_price, mop)
62 
        if mrp != "":
 93 
        if mrp != "":
- 
 
 94 
            if item.mrp == None or item.mrp >= mrp:
63 
            item.mrp = mrp
 95 
                item.mrp = mrp
64 
        #else:
 96 
            else:
65 
    	#    item.mrp = dp + dp/5
 97 
                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)
66 
        '''
 98
67 
        if mop != "":
 99 
        if mop != "":
68 
            item.mop = mop
 100 
            item.mop = mop
69 
        '''
 - 
 
70 
        if dp != "":
- 
 
71 
            item.dealerPrice = dp
 - 
 
72 
            item.sellingPrice = dp
101 
            item.sellingPrice = mop
73 
        #else:
 102 
 
74 
    	#    item.dealerPrice = mrp - mrp/5
 - 
 
75 
    	#    item.sellingPrice= mrp - mrp/5
 - 
 
76 
        if xfer_price !="":
 103 
        if xfer_price !="":
77 
            item.transfer_price = xfer_price
 104 
            item.transfer_price = xfer_price
- 
 
 105
- 
 
 106 
        item.startDate = datetime.datetime.now()
- 
 
 107 
 
- 
 
 108 
        item.model_name = model_name
- 
 
 109 
 
78 
        if weight != "":
110 
        if weight != "":
79 
            item.weight = weight
 111 
            item.weight = weight
80 
        if start_date != "":
 112 
        if start_date != "":
81 
            item.startDate = datetime.datetime(*xlrd.xldate_as_tuple(start_date, workbook.datemode))
 113 
            item.startDate = datetime.datetime(*xlrd.xldate_as_tuple(start_date, workbook.datemode))
- 
 
 114
82 
        item.bestDealText = deal_text
 115 
#        item.bestDealText = deal_text
83 
        if deal_value != "":
 116 
#        if deal_value != "":
84 
            item.bestDealValue = deal_value
 117 
#            item.bestDealValue = deal_value
85 
        item.comments = comments
 118 
#        item.comments = comments
- 
 
 119 
 
86 
        item.updatedOn = updatedOn
 120 
        item.updatedOn = updatedOn
87
121
88 
        item_change_log = ItemChangeLog()
 122 
        item_change_log = ItemChangeLog()
89 
        item_change_log.new_status = item.status
 123 
        item_change_log.new_status = item.status
90 
        item_change_log.timestamp = updatedOn
 124 
        item_change_log.timestamp = updatedOn
91 
        item_change_log.item = item
 125 
        item_change_log.item = item
- 
 
 126
- 
 
 127 
        if not dry_run:
- 
 
 128 
            session.commit()
- 
 
 129
- 
 
 130 
    write_report("new_items.csv", new_items, sheet)
- 
 
 131 
    write_report("updated_items.csv", old_items, sheet)
- 
 
 132
- 
 
 133 
    if (not dry_run) and full_update:
- 
 
 134 
        query_string =  "UPDATE " + str(Item.table) + " SET status=" + str(status.PHASED_OUT) + ", updatedOn='"+ str(updatedOn) +"'"+\
- 
 
 135 
                        " WHERE updatedOn <> '" + str(updatedOn) + "' AND hotspotCategory='" + category +"'";
- 
 
 136 
        session.execute(query_string, mapper=Item)
92 
        session.commit()
 137 
        session.commit()
93
138
94 
    session.execute("UPDATE " + str(Item.table) + " SET status=" + str(status.PHASED_OUT) + ", updatedOn='"+ str(updatedOn) +"' WHERE updatedOn <> '" + str(updatedOn) + "'", mapper=Item)
 - 
 
95 
    session.commit()
 - 
 
96 
    print "Successfully updated the item list information."
 139 
    print "Successfully updated the item list information."
97 
 
 140 
 
- 
 
 141 
def write_report(filename, items, sheet):
- 
 
 142 
    items_writer = csv.writer(open(filename, "wb"), delimiter=',', quoting=csv.QUOTE_ALL)
- 
 
 143 
    items_writer.writerow(sheet.row_values(0))
- 
 
 144 
    for i in items:
- 
 
 145 
        #print sheet.row_values(i)
- 
 
 146 
        items_writer.writerow([str(value) for value in sheet.row_values(i)])
- 
 
 147 
 
98 
def main():
 148 
def main():
99 
    parser = optparse.OptionParser()
 149 
    parser = optparse.OptionParser()
100 
    parser.add_option("-f", "--file", dest="filename",
 150 
    parser.add_option("-f", "--file", dest="filename",
101 
                   default="ItemList.xls", type="string",
 151 
                   default="ItemList.xls", type="string",
102 
                   help="Read the item list from FILE",
 152 
                   help="Read the item list from FILE",
103 
                   metavar="FILE")
 153 
                   metavar="FILE")
- 
 
 154 
    parser.add_option("-c", "--category", dest="category",
- 
 
 155 
                      type="string",
- 
 
 156 
                      help="Update the list only for the products belonging to CATEGORY",
- 
 
 157 
                      metavar="CATEGORY")
- 
 
 158 
    parser.add_option("-d", "--dry-run", dest="dry_run",
- 
 
 159 
                      action="store_true",
- 
 
 160 
                      help="Dry run only reporting on pending changes.Please note that some of the items can be reported twice.")
- 
 
 161 
    parser.add_option("-a", "--full", dest="full_update",
- 
 
 162 
                      action="store_true",
- 
 
 163 
                      help="In a full update, all older items are marked as PHASED_OUT. Also see -pm.")
- 
 
 164 
    parser.add_option("-p", "--partial", dest="full_update",
- 
 
 165 
                      action="store_false",
- 
 
 166 
                      help="In a partial update, older items are left as is. Also see -fm.")
- 
 
 167 
    parser.set_defaults(full_update=True, dry_run=False, category="Handsets")
104 
    (options, args) = parser.parse_args()
 168 
    (options, args) = parser.parse_args()
105 
    if len(args) != 0:
 169 
    if len(args) != 0:
106 
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
 170 
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
107 
    filename = options.filename
 171 
    filename = options.filename
108 
    load_item_data(filename)
 172 
    load_item_data(filename, options.category, options.full_update, options.dry_run)
109 
 
 173 
 
110 
if __name__ == '__main__':
 174 
if __name__ == '__main__':
111 
    main()
 175 
    main()