| Line 22... |
Line 22... |
| 22 |
workbook = xlrd.open_workbook(filename)
|
22 |
workbook = xlrd.open_workbook(filename)
|
| 23 |
sheet = workbook.sheet_by_index(0)
|
23 |
sheet = workbook.sheet_by_index(0)
|
| 24 |
num_rows = sheet.nrows
|
24 |
num_rows = sheet.nrows
|
| 25 |
updatedOn = datetime.datetime.now()
|
25 |
updatedOn = datetime.datetime.now()
|
| 26 |
new_items = []
|
26 |
new_items = []
|
| 27 |
old_items = []
|
27 |
updated_items = []
|
| - |
|
28 |
existing_items = Item.query.filter_by(status=status.ACTIVE, hotspotCategory=category).all()
|
| - |
|
29 |
existing_items_set = set([item.brand+';'+item.model_number+';'+item.color for item in existing_items])
|
| 28 |
for rownum in range(1, num_rows):
|
30 |
for rownum in range(1, num_rows):
|
| 29 |
#print sheet.row_values(rownum)
|
31 |
#print sheet.row_values(rownum)
|
| 30 |
catalog_item_id, category_id, product_group = [None, None, None]
|
32 |
catalog_item_id, category_id, product_group = [None, None, None]
|
| - |
|
33 |
|
| 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]
|
34 |
#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]
|
| 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]
|
35 |
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]
|
36 |
#product_group, brand, model_number, color, xfer_price, mop, mrp, catalog_item_id, category_id = sheet.row_values(rownum)[0:9]
|
| 34 |
if isinstance(model_number, float):
|
37 |
if isinstance(model_number, float):
|
| 35 |
model_number = str(int(model_number))
|
38 |
model_number = str(int(model_number))
|
| - |
|
39 |
|
| - |
|
40 |
key = brand + ';' + model_number + ';' + color
|
| - |
|
41 |
if key in existing_items_set:
|
| - |
|
42 |
existing_items_set.remove(key)
|
| 36 |
item = Item.get_by(brand=brand, model_number=model_number, color=color, hotspotCategory=category)
|
43 |
item = Item.get_by(brand=brand, model_number=model_number, color=color, hotspotCategory=category)
|
| 37 |
#item = Item.get_by(product_group=product_group, brand=brand, model_number=model_number, color=color)
|
44 |
#item = Item.get_by(product_group=product_group, brand=brand, model_number=model_number, color=color)
|
| 38 |
|
45 |
|
| 39 |
if item is None:
|
46 |
if item is None:
|
| 40 |
#print "[ADDING:]{0} {1} {2} {3} to our catalogue.".format(product_group, brand, model_number, color)
|
47 |
#print "[ADDING:]{0} {1} {2} {3} to our catalogue.".format(product_group, brand, model_number, color)
|
| Line 75... |
Line 82... |
| 75 |
#If a similar item already exists for a product group, brand and model_number, set it as same.
|
82 |
#If a similar item already exists for a product group, brand and model_number, set it as same.
|
| 76 |
item.catalog_item_id = similar_item.catalog_item_id
|
83 |
item.catalog_item_id = similar_item.catalog_item_id
|
| 77 |
item.category = similar_item.category
|
84 |
item.category = similar_item.category
|
| 78 |
#item.status = status.ACTIVE
|
85 |
#item.status = status.ACTIVE
|
| 79 |
else:
|
86 |
else:
|
| 80 |
old_items.append(rownum)
|
87 |
if item.mrp != mrp or item.dealerPrice != dp or item.transfer_price != xfer_price:
|
| - |
|
88 |
updated_items.append(sheet.row_values(rownum)[0:15] + [item.mrp, item.dealerPrice, item.transfer_price])
|
| 81 |
|
89 |
|
| 82 |
if category_id !=None and category_id != "":
|
90 |
if category_id !=None and category_id != "":
|
| 83 |
item.category = int(category_id)
|
91 |
item.category = int(category_id)
|
| 84 |
|
92 |
|
| 85 |
if dp != "":
|
93 |
if dp != "":
|
| Line 108... |
Line 116... |
| 108 |
|
116 |
|
| 109 |
item.model_name = model_name
|
117 |
item.model_name = model_name
|
| 110 |
|
118 |
|
| 111 |
if weight != "":
|
119 |
if weight != "":
|
| 112 |
item.weight = weight
|
120 |
item.weight = weight
|
| 113 |
if start_date != "":
|
121 |
# if start_date != "":
|
| 114 |
item.startDate = datetime.datetime(*xlrd.xldate_as_tuple(start_date, workbook.datemode))
|
122 |
# item.startDate = datetime.datetime(*xlrd.xldate_as_tuple(start_date, workbook.datemode))
|
| 115 |
|
123 |
|
| 116 |
# item.bestDealText = deal_text
|
124 |
# item.bestDealText = deal_text
|
| 117 |
# if deal_value != "":
|
125 |
# if deal_value != "":
|
| 118 |
# item.bestDealValue = deal_value
|
126 |
# item.bestDealValue = deal_value
|
| 119 |
# item.comments = comments
|
127 |
# item.comments = comments
|
| Line 126... |
Line 134... |
| 126 |
item_change_log.item = item
|
134 |
item_change_log.item = item
|
| 127 |
|
135 |
|
| 128 |
if not dry_run:
|
136 |
if not dry_run:
|
| 129 |
session.commit()
|
137 |
session.commit()
|
| 130 |
|
138 |
|
| 131 |
write_report("new_items.csv", new_items, sheet)
|
139 |
write_report("new_items.csv", new_items, sheet, False)
|
| 132 |
write_report("updated_items.csv", old_items, sheet)
|
140 |
write_report("updated_items.csv", updated_items, sheet, True)
|
| - |
|
141 |
phased_out_items = [key.split(';') for key in list(existing_items_set)]
|
| - |
|
142 |
write_report("phased_out_items.csv", phased_out_items, sheet, True)
|
| 133 |
|
143 |
|
| 134 |
if (not dry_run) and full_update:
|
144 |
if (not dry_run) and full_update:
|
| 135 |
query_string = "UPDATE " + str(Item.table) + " SET status=" + str(status.PHASED_OUT) + ", updatedOn='"+ str(updatedOn) +"'"+\
|
145 |
query_string = "UPDATE " + str(Item.table) + " SET status=" + str(status.PHASED_OUT) + ", updatedOn='"+ str(updatedOn) +"'"+\
|
| 136 |
" WHERE updatedOn <> '" + str(updatedOn) + "' AND hotspotCategory='" + category +"'";
|
146 |
" WHERE updatedOn <> '" + str(updatedOn) + "' AND hotspotCategory='" + category +"'";
|
| 137 |
session.execute(query_string, mapper=Item)
|
147 |
session.execute(query_string, mapper=Item)
|
| 138 |
session.commit()
|
148 |
session.commit()
|
| 139 |
|
149 |
|
| 140 |
print "Successfully updated the item list information."
|
150 |
print "Successfully updated the item list information."
|
| 141 |
|
151 |
|
| 142 |
def write_report(filename, items, sheet):
|
152 |
def write_report(filename, items, sheet, is_item):
|
| 143 |
items_writer = csv.writer(open(filename, "wb"), delimiter=',', quoting=csv.QUOTE_ALL)
|
153 |
items_writer = csv.writer(open(filename, "wb"), delimiter=',', quoting=csv.QUOTE_ALL)
|
| - |
|
154 |
items_writer.writerow(sheet.row_values(0) + ['Old MRP', 'Old DP', 'Old TP'])
|
| - |
|
155 |
if is_item:
|
| - |
|
156 |
for item in items:
|
| - |
|
157 |
#print item
|
| 144 |
items_writer.writerow(sheet.row_values(0))
|
158 |
items_writer.writerow([str(value) for value in item])
|
| - |
|
159 |
else:
|
| 145 |
for i in items:
|
160 |
for i in items:
|
| 146 |
#print sheet.row_values(i)
|
161 |
#print sheet.row_values(i)
|
| 147 |
items_writer.writerow([str(value) for value in sheet.row_values(i)])
|
162 |
items_writer.writerow([str(value) for value in sheet.row_values(i)])
|
| 148 |
|
163 |
|
| 149 |
def main():
|
164 |
def main():
|
| 150 |
parser = optparse.OptionParser()
|
165 |
parser = optparse.OptionParser()
|
| 151 |
parser.add_option("-f", "--file", dest="filename",
|
166 |
parser.add_option("-f", "--file", dest="filename",
|
| 152 |
default="ItemList.xls", type="string",
|
167 |
default="ItemList.xls", type="string",
|