| Line 32... |
Line 32... |
| 32 |
sheet = workbook.sheet_by_index(0)
|
32 |
sheet = workbook.sheet_by_index(0)
|
| 33 |
num_rows = sheet.nrows
|
33 |
num_rows = sheet.nrows
|
| 34 |
updatedOn = datetime.datetime.now()
|
34 |
updatedOn = datetime.datetime.now()
|
| 35 |
new_items = []
|
35 |
new_items = []
|
| 36 |
updated_items = []
|
36 |
updated_items = []
|
| - |
|
37 |
not_created_items = []
|
| 37 |
|
38 |
|
| 38 |
existing_vendor_item_mappings = VendorItemMapping.query.filter_by(vendor=vendor, vendor_category=category).all()
|
39 |
existing_vendor_item_mappings = VendorItemMapping.query.filter_by(vendor=vendor, vendor_category=category).all()
|
| 39 |
existing_vendor_item_mappings_set = set([mapping.item_key for mapping in existing_vendor_item_mappings])
|
40 |
existing_vendor_item_mappings_set = set([mapping.item_key for mapping in existing_vendor_item_mappings])
|
| 40 |
|
41 |
|
| 41 |
for rownum in range(1, num_rows):
|
42 |
for rownum in range(1, num_rows):
|
| 42 |
print sheet.row_values(rownum)
|
43 |
print sheet.row_values(rownum)
|
| 43 |
catalog_item_id, category_id = [None, None]
|
- |
|
| 44 |
|
44 |
|
| 45 |
if supplied_product_group != None and supplied_product_group != '':
|
45 |
if supplied_product_group != None and supplied_product_group != '':
|
| 46 |
our_brand, our_model_number, our_color,\
|
46 |
our_brand, our_model_number, our_color,\
|
| 47 |
brand, model_number, model_name, color,\
|
47 |
brand, model_number, model_name, color,\
|
| 48 |
dp, mrp, mop, sp,\
|
48 |
dp, mrp, mop, sp,\
|
| 49 |
comments, hotspot_category, unused_feature, xfer_price, weight, start_date, deal_text, deal_value = sheet.row_values(rownum)[0:19]
|
49 |
comments, hotspot_category, unused_feature, xfer_price, weight, start_date, deal_text, deal_value,\
|
| - |
|
50 |
warranty_period, defaultWarehouse, preferredVendor = sheet.row_values(rownum)[0:22]
|
| 50 |
product_group = supplied_product_group
|
51 |
product_group = supplied_product_group
|
| 51 |
else:
|
52 |
else:
|
| 52 |
our_brand, our_model_number, our_color,\
|
53 |
our_brand, our_model_number, our_color,\
|
| 53 |
product_group, brand, model_number, model_name, color,\
|
54 |
product_group, brand, model_number, model_name, color,\
|
| 54 |
dp, mrp, mop, sp,\
|
55 |
dp, mrp, mop, sp,\
|
| 55 |
comments, hotspot_category, unused_feature, xfer_price, weight, start_date, deal_text, deal_value = sheet.row_values(rownum)[0:20]
|
56 |
comments, hotspot_category, unused_feature, xfer_price, weight, start_date, deal_text, deal_value,\
|
| - |
|
57 |
warranty_period, defaultWarehouse, preferredVendor = sheet.row_values(rownum)[0:23]
|
| 56 |
|
58 |
|
| 57 |
print product_group
|
59 |
print product_group
|
| 58 |
|
60 |
|
| 59 |
if isinstance(model_number, float):
|
61 |
if isinstance(model_number, float):
|
| 60 |
model_number = str(int(model_number))
|
62 |
model_number = str(int(model_number))
|
| Line 72... |
Line 74... |
| 72 |
vendor_item_pricing = None
|
74 |
vendor_item_pricing = None
|
| 73 |
|
75 |
|
| 74 |
key = product_group.strip().lower() + '|' + brand.strip().lower() + '|' + model_number.strip().lower() + '|' + color.strip().lower()
|
76 |
key = product_group.strip().lower() + '|' + brand.strip().lower() + '|' + model_number.strip().lower() + '|' + color.strip().lower()
|
| 75 |
if key in existing_vendor_item_mappings_set:
|
77 |
if key in existing_vendor_item_mappings_set:
|
| 76 |
existing_vendor_item_mappings_set.remove(key)
|
78 |
existing_vendor_item_mappings_set.remove(key)
|
| - |
|
79 |
|
| - |
|
80 |
# Check if a similar items already exists in our database
|
| 77 |
item = VendorItemMapping.query.filter_by(vendor=vendor, item_key=key, vendor_category=category).one().item
|
81 |
similar_items = Item.query.filter_by(brand=our_brand.strip(), model_number=our_model_number.strip(), model_name=model_name.strip()).all()
|
| - |
|
82 |
|
| - |
|
83 |
for old_item in similar_items:
|
| - |
|
84 |
if old_item.color == our_color.strip():
|
| - |
|
85 |
item = old_item
|
| - |
|
86 |
break
|
| 78 |
|
87 |
|
| 79 |
if item is None:
|
88 |
if item is None:
|
| - |
|
89 |
for old_item in similar_items:
|
| - |
|
90 |
if old_item.color == None or old_item.color.lower() == 'na' or old_item.color.lower() == 'blank' or old_item.color.lower() == '(blank)' or old_item.color == '':
|
| - |
|
91 |
item = old_item
|
| - |
|
92 |
break
|
| - |
|
93 |
|
| - |
|
94 |
i = 0
|
| - |
|
95 |
color_of_similar_item = None
|
| - |
|
96 |
for old_item in similar_items:
|
| - |
|
97 |
if old_item.color != None and old_item.color.lower() != 'na' and old_item.color.lower() != 'blank' and old_item.color.lower() != '(blank)' and old_item.color != '':
|
| - |
|
98 |
similar_item = old_item
|
| - |
|
99 |
color_of_similar_item = similar_item.color
|
| - |
|
100 |
break
|
| - |
|
101 |
i = i + 1
|
| - |
|
102 |
if i == len(similar_items):
|
| - |
|
103 |
similar_item = old_item
|
| - |
|
104 |
color_of_similar_item = similar_item.color
|
| - |
|
105 |
|
| - |
|
106 |
if color_of_similar_item != None and color_of_similar_item.lower() != 'na' and color_of_similar_item.lower() != 'blank' and color_of_similar_item.lower() != '(blank)' and color_of_similar_item != '':
|
| - |
|
107 |
if item is None and (our_color.strip().lower() == 'na' or our_color.strip().lower() == 'blank' or our_color.strip().lower() == '(blank)' or our_color.strip() == ''):
|
| - |
|
108 |
not_created_items.append(rownum)
|
| - |
|
109 |
continue
|
| - |
|
110 |
|
| - |
|
111 |
|
| - |
|
112 |
if item is None:
|
| 80 |
#print "[ADDING:]{0} {1} {2} {3} to our catalogue.".format(product_group, brand, model_number, color)
|
113 |
#print "[ADDING:]{0} {1} {2} {3} to our catalogue.".format(brand, model_number, model_name, color)
|
| 81 |
new_items.append(rownum)
|
114 |
new_items.append(rownum)
|
| 82 |
item = Item()
|
115 |
item = Item()
|
| 83 |
item.product_group = product_group
|
116 |
item.product_group = product_group.strip()
|
| 84 |
item.brand = our_brand
|
117 |
item.brand = our_brand.strip()
|
| 85 |
item.model_number = our_model_number
|
118 |
item.model_number = our_model_number.strip()
|
| 86 |
item.model_name = model_name
|
119 |
item.model_name = model_name.strip()
|
| - |
|
120 |
item.color = our_color.strip()
|
| 87 |
item.status = status.IN_PROCESS
|
121 |
item.status = status.IN_PROCESS
|
| 88 |
item.status_description = "This item is in process"
|
122 |
item.status_description = "This item is in process"
|
| 89 |
item.addedOn = updatedOn
|
123 |
item.addedOn = updatedOn
|
| 90 |
item.hotspotCategory = category
|
124 |
item.hotspotCategory = category
|
| 91 |
|
125 |
|
| Line 99... |
Line 133... |
| 99 |
|
133 |
|
| 100 |
session.add(item)
|
134 |
session.add(item)
|
| 101 |
session.add(vendor_item_mapping)
|
135 |
session.add(vendor_item_mapping)
|
| 102 |
session.add(vendor_item_pricing)
|
136 |
session.add(vendor_item_pricing)
|
| 103 |
|
137 |
|
| 104 |
if catalog_item_id !=None and catalog_item_id != "":
|
138 |
if similar_item is None or similar_item.catalog_item_id is None:
|
| - |
|
139 |
# If there is no similar item in the database from before,
|
| 105 |
#Add category and entity id
|
140 |
# use the entity_id_generator
|
| 106 |
item.catalog_item_id = int(catalog_item_id)
|
141 |
entity_id = EntityIDGenerator.query.first()
|
| 107 |
item.category = int(category_id)
|
142 |
item.catalog_item_id = entity_id.id + 1
|
| 108 |
item.status = status.ACTIVE
|
143 |
entity_id.id = entity_id.id + 1
|
| - |
|
144 |
if similar_item is not None and similar_item.catalog_item_id is None:
|
| 109 |
item.status_description = "This item is active"
|
145 |
similar_item.catalog_item_id = entity_id.id
|
| 110 |
else:
|
146 |
else:
|
| 111 |
# Check if a similar item already exists in our database
|
- |
|
| 112 |
similar_item = Item.query.filter_by(product_group=product_group, brand = our_brand, model_number=our_model_number, hotspotCategory=category).first()
|
- |
|
| 113 |
print "[SIMILAR ITEM FOUND:] FOR {0} {1} {2} {3}".format(product_group, brand, model_number, color)
|
- |
|
| 114 |
|
- |
|
| 115 |
if similar_item is None or similar_item.catalog_item_id is None:
|
- |
|
| 116 |
# If there is no similar item in the database from before,
|
- |
|
| 117 |
# use the entity_id_generator
|
- |
|
| 118 |
entity_id = EntityIDGenerator.query.first()
|
- |
|
| 119 |
item.catalog_item_id = entity_id.id + 1
|
- |
|
| 120 |
entity_id.id = entity_id.id + 1
|
- |
|
| 121 |
|
- |
|
| 122 |
|
- |
|
| 123 |
else:
|
- |
|
| 124 |
#If a similar item already exists for a product group, brand and model_number, set it as same.
|
147 |
#If a similar item already exists for a product group, brand and model_number, set it as same.
|
| 125 |
item.catalog_item_id = similar_item.catalog_item_id
|
148 |
item.catalog_item_id = similar_item.catalog_item_id
|
| 126 |
item.category = similar_item.category
|
149 |
item.category = similar_item.category
|
| 127 |
item.status = similar_item.status
|
150 |
item.status = similar_item.status
|
| 128 |
item.status_description = similar_item.status_description
|
151 |
item.status_description = similar_item.status_description
|
| 129 |
#Use the same brand, model name and model number as in similar item in database
|
152 |
#Use the same brand, model name and model number as in similar item in database
|
| 130 |
item.brand = similar_item.brand
|
153 |
item.brand = similar_item.brand
|
| 131 |
item.model_name = similar_item.model_name
|
154 |
item.model_name = similar_item.model_name
|
| 132 |
item.model_number = similar_item.model_number
|
155 |
item.model_number = similar_item.model_number
|
| 133 |
else:
|
156 |
else:
|
| 134 |
# If this item already existed and one of its price parameters has changed
|
157 |
# If this item already existed and one of its price parameters has changed
|
| 135 |
# in which case we add it to the list of updated items for reporting.
|
158 |
# in which case we add it to the list of updated items for reporting.
|
| 136 |
if item.status == status.PHASED_OUT:
|
159 |
if item.status == status.PHASED_OUT:
|
| 137 |
item.status = status.IN_PROCESS #Not the ideal choice but we don't know whether content has been generated for it beforehand.
|
160 |
item.status = status.IN_PROCESS #Not the ideal choice but we don't know whether content has been generated for it beforehand.
|
| 138 |
item.status_description = "This item is in process"
|
161 |
item.status_description = "This item is in process"
|
| - |
|
162 |
|
| - |
|
163 |
if item.color != our_color.strip() and our_color.strip().lower() != 'na' and our_color.strip().lower() != 'blank' and our_color.strip().lower() != '(blank)' and our_color.strip() != '':
|
| - |
|
164 |
item.color = our_color.strip()
|
| - |
|
165 |
|
| - |
|
166 |
vendor_item_mapping = VendorItemMapping.get_by(vendor=vendor, item=item)
|
| - |
|
167 |
if vendor_item_mapping is None:
|
| - |
|
168 |
vendor_item_mapping = VendorItemMapping(vendor=vendor, item=item, item_key=key, vendor_category=category)
|
| - |
|
169 |
session.add(vendor_item_mapping)
|
| 139 |
vendor_item_pricing = VendorItemPricing.get_by(vendor=vendor, item=item)
|
170 |
vendor_item_pricing = VendorItemPricing.get_by(vendor=vendor, item=item)
|
| 140 |
if vendor_item_pricing is None:
|
171 |
if vendor_item_pricing is None:
|
| 141 |
vendor_item_pricing = VendorItemPricing(vendor=vendor, item=item)
|
172 |
vendor_item_pricing = VendorItemPricing(vendor=vendor, item=item)
|
| 142 |
session.add(vendor_item_pricing)
|
173 |
session.add(vendor_item_pricing)
|
| 143 |
|
174 |
|
| 144 |
if item.mrp != mrp or vendor_item_pricing.dealerPrice != dp or vendor_item_pricing.transfer_price != xfer_price or item.sellingPrice != sp:
|
175 |
if item.mrp != mrp or vendor_item_pricing.dealerPrice != dp or vendor_item_pricing.transfer_price != xfer_price or item.sellingPrice != sp:
|
| 145 |
updated_items.append(sheet.row_values(rownum)[0:19] + [item.mrp, item.dealerPrice, item.transfer_price])
|
176 |
updated_items.append(sheet.row_values(rownum)[0:19] + [item.mrp, item.dealerPrice, item.transfer_price])
|
| 146 |
|
177 |
|
| 147 |
#In future we will provide the option to edit color in CMS
|
- |
|
| 148 |
item.color = our_color
|
- |
|
| 149 |
|
- |
|
| 150 |
if category_id !=None and category_id != "":
|
- |
|
| 151 |
item.category = int(category_id)
|
- |
|
| 152 |
|
- |
|
| 153 |
if dp != "":
|
178 |
if dp != "":
|
| 154 |
item.dealerPrice = dp
|
179 |
item.dealerPrice = dp
|
| 155 |
item.sellingPrice = dp
|
180 |
item.sellingPrice = dp
|
| 156 |
vendor_item_pricing.dealerPrice = dp
|
181 |
vendor_item_pricing.dealerPrice = dp
|
| 157 |
|
182 |
|
| 158 |
if mrp != "" and mop != "" and mrp < mop:
|
183 |
if mrp != "" and mop != "" and mrp < mop:
|
| 159 |
raise Exception("[BAD MRP and MOP:] for {0} {1} {2} {3}. MRP={4}. MOP={5}".format(product_group, brand, model_number, color, mrp, mop))
|
184 |
raise Exception("[BAD MRP and MOP:] for {0} {1} {2} {3}. MRP={4}. MOP={5}".format(brand, model_number, model_name, color, mrp, mop))
|
| 160 |
|
185 |
|
| 161 |
if mrp != "" and sp != "" and mrp < sp:
|
186 |
if mrp != "" and sp != "" and mrp < sp:
|
| 162 |
raise Exception("[BAD MRP and SP:] for {0} {1} {2} {3}. MRP={4}. SP={5}".format(product_group, brand, model_number, color, mrp, sp))
|
187 |
raise Exception("[BAD MRP and SP:] for {0} {1} {2} {3}. MRP={4}. SP={5}".format(brand, model_number, model_name, color, mrp, sp))
|
| 163 |
|
188 |
|
| 164 |
if mop != "" and xfer_price != "" and xfer_price > mop:
|
189 |
if mop != "" and xfer_price != "" and xfer_price > mop:
|
| 165 |
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))
|
190 |
raise Exception("[BAD MOP and TP:] for {0} {1} {2} {3}. TP={4}. MOP={5}".format(brand, model_number, model_name, color, xfer_price, mop))
|
| 166 |
# if mrp != "":
|
191 |
# if mrp != "":
|
| 167 |
# if item.mrp == None or item.mrp >= mrp:
|
192 |
# if item.mrp == None or item.mrp >= mrp:
|
| 168 |
# item.mrp = mrp
|
193 |
# item.mrp = mrp
|
| 169 |
# else:
|
194 |
# else:
|
| 170 |
# 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))
|
195 |
# raise Exception("[NEW MRP MORE THAN old MRP:] for {0} {1} {2} {3}. Old mrp={4}. New MRP={5}".format(brand, model_number, model_name, color, item.mrp, mrp))
|
| - |
|
196 |
|
| - |
|
197 |
if defaultWarehouse == None or defaultWarehouse == "":
|
| - |
|
198 |
raise Exception("[Default Warehouse required:] for {0} {1} {2} {3}".format(brand, model_number, model_name, color))
|
| - |
|
199 |
else:
|
| - |
|
200 |
try:
|
| - |
|
201 |
item.defaultWarehouse = int(defaultWarehouse)
|
| - |
|
202 |
except:
|
| - |
|
203 |
raise Exception("[Default Warehouse should be number:] for {0} {1} {2} {3}. defaultWarehouse={4}".format(brand, model_number, model_name, color, defaultWarehouse))
|
| - |
|
204 |
|
| - |
|
205 |
if preferredVendor == None or preferredVendor == "":
|
| - |
|
206 |
raise Exception("[Preferred Vendor required:] for {0} {1} {2} {3}".format(brand, model_number, model_name, color))
|
| - |
|
207 |
else:
|
| - |
|
208 |
try:
|
| - |
|
209 |
item.preferredVendor = int(preferredVendor)
|
| - |
|
210 |
except:
|
| - |
|
211 |
raise Exception("[Preferred Vendor should be number:] for {0} {1} {2} {3}. preferredVendor={4}".format(brand, model_number, model_name, color, preferredVendor))
|
| - |
|
212 |
|
| - |
|
213 |
if warranty_period != "":
|
| - |
|
214 |
try:
|
| - |
|
215 |
item.warranty_period = int(warranty_period)
|
| - |
|
216 |
except:
|
| - |
|
217 |
pass
|
| 171 |
|
218 |
|
| 172 |
if mrp != "":
|
219 |
if mrp != "":
|
| 173 |
item.mrp = mrp
|
220 |
item.mrp = mrp
|
| 174 |
|
221 |
|
| 175 |
if mop != "":
|
222 |
if mop != "":
|
| Line 205... |
Line 252... |
| 205 |
item_change_log.item = item
|
252 |
item_change_log.item = item
|
| 206 |
|
253 |
|
| 207 |
if not dry_run:
|
254 |
if not dry_run:
|
| 208 |
session.commit()
|
255 |
session.commit()
|
| 209 |
|
256 |
|
| - |
|
257 |
write_report("items_not_created_dueto_color.csv", not_created_items, sheet, False)
|
| 210 |
write_report("new_items.csv", new_items, sheet, False)
|
258 |
write_report("new_items.csv", new_items, sheet, False)
|
| 211 |
write_report("updated_items.csv", updated_items, sheet, True)
|
259 |
write_report("updated_items.csv", updated_items, sheet, True)
|
| 212 |
phased_out_items = [key.split('|') for key in list(existing_vendor_item_mappings_set)]
|
260 |
phased_out_items = [key.split('|') for key in list(existing_vendor_item_mappings_set)]
|
| 213 |
write_report("phased_out_items.csv", phased_out_items, sheet, True)
|
261 |
write_report("phased_out_items.csv", phased_out_items, sheet, True)
|
| 214 |
|
262 |
|