Subversion Repositories SmartDukaan

Rev

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

Rev 5971 Rev 9431
Line 5... Line 5...
5 
It's now mostly used for Accessories since they come in huge numbers.
 5 
It's now mostly used for Accessories since they come in huge numbers.
6 
 
 6 
 
7 
@author: Chandranshu
 7 
@author: Chandranshu
8 
'''
 8 
'''
9 
from elixir import *
 9 
from elixir import *
10 
from shop2020.model.v1.catalog.impl import DataService
 - 
 
11 
from shop2020.model.v1.inventory.impl import DataService as IDataService
 - 
 
12 
from shop2020.model.v1.catalog.impl.DataService import Item, EntityIDGenerator, \
 - 
 
13 
    ItemChangeLog
 - 
 
14 
from shop2020.model.v1.inventory.impl.DataService import Vendor, \
 - 
 
15 
    VendorItemMapping, VendorItemPricing
 - 
 
16 
from shop2020.thriftpy.model.v1.catalog.ttypes import status, ItemType
 10 
from shop2020.thriftpy.model.v1.catalog.ttypes import Item
17 
import csv
 11 
import csv
18 
import datetime
 12 
import datetime
- 
 
 13 
from datetime import timedelta
19 
import optparse
 14 
import optparse
20 
import xlrd
 15 
import xlrd
- 
 
 16 
from shop2020.clients.CatalogClient import CatalogClient
- 
 
 17 
from shop2020.clients.InventoryClient import InventoryClient
- 
 
 18 
from shop2020.thriftpy.model.v1.inventory.ttypes import VendorItemPricing
- 
 
 19 
from shop2020.utils.Utils import to_java_date
- 
 
 20 
from shop2020.utils.EmailAttachmentSender import mail
21 
 
 21 
 
22 
if __name__ == '__main__' and __package__ is None:
 22 
if __name__ == '__main__' and __package__ is None:
23 
    import sys
 23 
    import sys
24 
    import os
 24 
    import os
25 
    sys.path.insert(0, os.getcwd())
 25 
    sys.path.insert(0, os.getcwd())
26 
 
 26 
 
27 
 
 27 
 
28 
def load_item_data(filename, vendorId, full_update, dry_run):
 28 
def load_item_data(filename):
29 
    DataService.initialize('catalog')
 - 
 
30 
    IDataService.initialize('inventory')
 - 
 
31 
 
 - 
 
32 
    vendor = Vendor.get_by(id=vendorId)
 - 
 
33 
    if vendor is None:
 - 
 
34 
        raise Exception("No vendor found for the id: " + str(vendorId))
 - 
 
35
- 
 
36 
    workbook = xlrd.open_workbook(filename)
 29 
    workbook = xlrd.open_workbook(filename)
37 
    sheet = workbook.sheet_by_index(0)
 30 
    sheet = workbook.sheet_by_index(0)
38 
    num_rows = sheet.nrows
 31 
    num_rows = sheet.nrows
39 
    updatedOn = datetime.datetime.now()
 32 
    updatedOn = to_java_date(datetime.datetime.now())
40 
    new_items = []
 - 
 
41 
    updated_items = []
 33 
    cclient = CatalogClient().get_client()
42 
    not_created_items = []
 34 
    iclient = InventoryClient().get_client()
43
35
44 
    for rownum in range(1, num_rows):
 36 
    for rownum in range(1, num_rows):
45 
        print sheet.row_values(rownum)
 37 
        print sheet.row_values(rownum)
46
- 
 
47 
        our_brand, our_model_number, our_model_name, our_color,\
 - 
 
48 
        product_group, brand, model_number, model_name, color,\
 - 
 
49 
        dp, mrp, mop, sp, xfer_price,\
 - 
 
50 
        comments, weight, start_date, deal_text, deal_value,\
 - 
 
51 
        warranty_period, serialized, hasItemNo, preferredVendor = sheet.row_values(rownum)[0:23]
 - 
 
52
- 
 
53 
        print product_group
 - 
 
54
- 
 
55 
        if isinstance(model_number, float):
 - 
 
56 
            model_number = str(int(model_number))
 - 
 
57
- 
 
58 
        if isinstance(our_model_number, float):
 - 
 
59 
            our_model_number = str(int(our_model_number))
 - 
 
60
- 
 
61 
        if our_brand == '':
 - 
 
62 
            our_brand = brand
 - 
 
63 
        if our_model_number == '':
 - 
 
64 
            our_model_number = model_number
 - 
 
65 
        if our_model_name == '':
 - 
 
66 
            our_model_name = model_name
 - 
 
67 
        if our_color == '':
 - 
 
68 
            our_color = color
 - 
 
69
- 
 
70 
        if sp == '':
 38 
        message = ""
71 
            sp = mop
- 
 
72 
        item = None
 - 
 
73 
        similar_item = None
 - 
 
74 
        vendor_item_pricing = None
 - 
 
75
- 
 
76 
        key = product_group.strip().lower() + '|' + brand.strip().lower() + '|' + model_number.strip().lower() + '|' + color.strip().lower()
 - 
 
77
- 
 
78 
        # Check if a similar items already exists in our database
 - 
 
79 
        similar_items = Item.query.filter_by(brand=our_brand.strip(), model_number=our_model_number.strip(), model_name=our_model_name.strip()).all()
 - 
 
80
- 
 
81 
        # Check if a similar item already exists in our database
 - 
 
82 
        for old_item in similar_items:
 - 
 
83 
            if old_item.color != None and old_item.color.strip().lower() == our_color.strip().lower():
 - 
 
84 
                item = old_item
 - 
 
85 
                break
 - 
 
86
- 
 
87 
        # Check if a similar item already exists in our database with out valid color if similar item with same color is not found
 - 
 
88 
        if item is None:
 - 
 
89 
            for old_item in similar_items:
 - 
 
90 
                if not check_color_valid(old_item.color):
 - 
 
91 
                    item = old_item
 - 
 
92 
                    break
 - 
 
93
- 
 
94 
        i = 0
 - 
 
95 
        color_of_similar_item = None
 - 
 
96 
        # Check if a similar item already exists in our database to be used to get catalog_item_id
 - 
 
97 
        for old_item in similar_items:
 - 
 
98 
            # get a similar item already existing in our database with valid color
 - 
 
99 
            if check_color_valid(old_item.color):
 - 
 
100 
                similar_item = old_item
 - 
 
101 
                color_of_similar_item = similar_item.color
 - 
 
102 
                break
 - 
 
103 
            i = i + 1
 - 
 
104 
            # get a similar item already existing in our database if similar item with valid color is not found
 - 
 
105 
            if i == len(similar_items):
 - 
 
106 
                similar_item = old_item
 - 
 
107 
                color_of_similar_item = similar_item.color
 - 
 
108
39 
        try:
109 
        # Check if a similar item that is obtained above is having a valid color
 - 
 
110 
        if check_color_valid(color_of_similar_item):
 - 
 
111 
            # if a similar item that is obtained above is having a valid color and new item is about to be created with out valid color it is not done.
 - 
 
112 
            # since for example if their is a item with red color in our database and we are creating a new item with no color for the same product which is wrong.
 40 
            product_group, brand, model_number, model_name, color, mrp, sp, mop, dp, tp, nlc, start_date, preferred_vendor, risky, weight, item_type = sheet.row_values(rownum)[0:16]
113 
            if item is None and not check_color_valid(our_color):
 - 
 
114 
                not_created_items.append(rownum)
 - 
 
115 
                continue
 - 
 
116
- 
 
117 
        if item is None:
 - 
 
118 
            #print "[ADDING:]{0} {1} {2} {3} to our catalogue.".format(brand, model_number, model_name, color)
 - 
 
119 
            new_items.append(rownum)
 - 
 
120 
            item = Item()
 - 
 
121 
            item.brand = our_brand.strip()
 - 
 
122 
            item.product_group = product_group.strip()
 - 
 
123 
            item.model_number = our_model_number.strip()
 - 
 
124 
            item.model_name = our_model_name.strip()
 - 
 
125 
            item.color = our_color.strip()
 - 
 
126 
            item.status = status.IN_PROCESS
 - 
 
127 
            item.status_description = "This item is in process"
 - 
 
128 
            item.addedOn = updatedOn
 - 
 
129
- 
 
130 
            vendor_item_mapping = VendorItemMapping(vendor=vendor, item=item, item_key=key)
 - 
 
131 
            vendor_item_pricing = VendorItemPricing(vendor=vendor, item=item)
 - 
 
132
- 
 
133 
            session.add(item)
 - 
 
134 
            session.add(vendor_item_mapping)
 - 
 
135 
            session.add(vendor_item_pricing)
 - 
 
136
- 
 
137 
            if similar_item is None or similar_item.catalog_item_id is None:
 - 
 
138 
                # If there is no similar item in the database from before,
 - 
 
139 
                # use the entity_id_generator
 - 
 
140 
                entity_id = EntityIDGenerator.query.first()
 - 
 
141 
                item.catalog_item_id = entity_id.id + 1
 - 
 
142 
                entity_id.id = entity_id.id  + 1
 - 
 
143 
                if similar_item is not None and similar_item.catalog_item_id is None:
 - 
 
144 
                    similar_item.catalog_item_id = entity_id.id
 - 
 
145 
            else:
 - 
 
146 
                #If a similar item already exists for a product group, brand and model_number, set it as same.
 - 
 
147 
                item.catalog_item_id = similar_item.catalog_item_id
 - 
 
148 
                item.category = similar_item.category
 - 
 
149 
                item.product_group = similar_item.product_group
 - 
 
150 
                item.status = similar_item.status
 - 
 
151 
                item.status_description = similar_item.status_description
 - 
 
152 
                #Use the same brand, model name and model number as in similar item in database
 - 
 
153 
                item.brand = similar_item.brand
 - 
 
154 
                item.model_name = similar_item.model_name
 - 
 
155 
                item.model_number = similar_item.model_number
 - 
 
156 
        else:
 - 
 
157 
            # If this item already existed and one of its price parameters has changed
 - 
 
158 
            # in which case we add it to the list of updated items for reporting.
 - 
 
159 
            if item.status == status.PHASED_OUT:
 - 
 
160 
                item.status = status.IN_PROCESS #Not the ideal choice but we don't know whether content has been generated for it beforehand.
 - 
 
161 
                item.status_description = "This item is in process"
 - 
 
162
- 
 
163 
            if check_color_valid(our_color) and (item.color == None or item.color.strip().lower() != our_color.strip().lower()):
 - 
 
164 
                item.color = our_color.strip()
 - 
 
165
41
166 
            vendor_item_mapping = VendorItemMapping.get_by(vendor=vendor, item=item)
 - 
 
167 
            if vendor_item_mapping is None:
 42 
            if isinstance(model_number, float):
168 
                vendor_item_mapping = VendorItemMapping(vendor=vendor, item=item, item_key=key)
 - 
 
169 
                session.add(vendor_item_mapping)
 43 
                model_number = str(int(model_number))
170 
            else:
 - 
 
171 
                vendor_item_mapping.item_key = key
 - 
 
172
44
173 
            vendor_item_pricing = VendorItemPricing.get_by(vendor=vendor, item=item)
 - 
 
174 
            if vendor_item_pricing is None:
 - 
 
175 
                vendor_item_pricing = VendorItemPricing(vendor=vendor, item=item)
 - 
 
176 
                session.add(vendor_item_pricing)
 - 
 
177
- 
 
178 
            if item.mrp != mrp or vendor_item_pricing.dealerPrice != dp or vendor_item_pricing.transfer_price != xfer_price or item.sellingPrice != sp:
 - 
 
179 
                updated_items.append(sheet.row_values(rownum)[0:23] + [item.mrp, vendor_item_pricing.dealerPrice, vendor_item_pricing.transfer_price, item.sellingPrice])
 - 
 
180
- 
 
181 
        if dp != "":
 45 
            item = Item()
182 
            item.sellingPrice = dp
 46 
            item.productGroup = product_group
183 
            vendor_item_pricing.dealerPrice = dp
 - 
 
184
- 
 
185 
        if mrp != "" and mop != "" and mrp <  mop:
 - 
 
186 
            raise Exception("[BAD MRP and MOP:] for {0} {1} {2} {3}. MRP={4}. MOP={5}".format(brand, model_number, model_name, color, mrp, mop))
 - 
 
187
- 
 
188 
        if mrp != "" and sp != "" and mrp < sp:
 - 
 
189 
            raise Exception("[BAD MRP and SP:] for {0} {1} {2} {3}. MRP={4}. SP={5}".format(brand, model_number, model_name, color, mrp, sp))
 - 
 
190
- 
 
191 
        if mop != "" and xfer_price != "" and xfer_price > mop:
 - 
 
192 
            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))
 - 
 
193
- 
 
194 
        if preferredVendor == None or preferredVendor == "":
 - 
 
195 
            raise Exception("[Preferred Vendor required:] for {0} {1} {2} {3}".format(brand, model_number, model_name, color))
 - 
 
196 
        else:
 - 
 
197 
            try:
 - 
 
198 
                item.preferredVendor = int(preferredVendor)
 - 
 
199 
            except:
 - 
 
200 
                raise Exception("[Preferred Vendor should be number:] for {0} {1} {2} {3}. preferredVendor={4}".format(brand, model_number, model_name, color, preferredVendor))
 - 
 
201
- 
 
202 
        if serialized == None or serialized == "":
 - 
 
203 
            raise Exception("[Serialized required:] for {0} {1} {2} {3}".format(brand, model_number, model_name, color))
 - 
 
204 
        else:
 - 
 
205 
            try:
 - 
 
206 
                if serialized:
 47 
            item.brand = brand
207 
                    item.type = ItemType.SERIALIZED
 48 
            item.modelNumber = model_number
208 
                else:
 - 
 
209 
                    item.type = ItemType.NON_SERIALIZED
 - 
 
210 
            except:
 - 
 
211 
                raise Exception("[Serialized should be either TRUE(SERIALIZED) or FALSE(NON_SERIALIZED):] for {0} {1} {2} {3}. serialized={4}".format(brand, model_number, model_name, color, serialized))
 - 
 
212
- 
 
213 
        if hasItemNo == None or hasItemNo == "":
 49 
            item.modelName = model_name
214 
            raise Exception("[hasItemNo required:] for {0} {1} {2} {3}".format(brand, model_number, model_name, color))
 - 
 
215 
        else:
 - 
 
216 
            try:
 - 
 
217 
                if hasItemNo:
 50 
            item.color = color
218 
                    item.hasItemNo = 1
 - 
 
219 
                else:
 - 
 
220 
                    item.hasItemNo = 0
 - 
 
221 
            except:
 - 
 
222 
                raise Exception("[hasItemNo should be either TRUE or FALSE:] for {0} {1} {2} {3}. hasItemNo={4}".format(brand, model_number, model_name, color, hasItemNo))
 - 
 
223
- 
 
224 
        if warranty_period != "":
 - 
 
225 
            try:
 - 
 
226 
                item.warranty_period = int(warranty_period)
 - 
 
227 
            except:
 - 
 
228 
                pass
 - 
 
229
- 
 
230 
        if mrp != "":
 - 
 
231 
            item.mrp = mrp
 51 
            item.mrp = round(mrp)
232
- 
 
233 
        if mop != "":
 - 
 
234 
            vendor_item_pricing.mop = mop
 52 
            item.sellingPrice = round(sp)
235
- 
 
236 
        if xfer_price !="":
 - 
 
237 
            vendor_item_pricing.transfer_price = xfer_price
 - 
 
238
- 
 
239 
        if start_date is not None and start_date != '':
 - 
 
240 
            #If a start date has been specified, it takes precedence.
 - 
 
241 
            item.startDate = datetime.datetime(*xlrd.xldate_as_tuple(start_date, workbook.datemode))
 53 
            item.startDate = long(to_java_date(datetime.datetime(1899, 12, 30) + timedelta(days=int(start_date))))
242 
        elif item.startDate == None or item.startDate == '':
- 
 
243 
            #If start date is not specified and item's start date is not set, set it to current time
 - 
 
244 
            item.startDate = datetime.datetime.now()
 54 
            item.preferredInsurer = preferred_vendor
245
- 
 
246 
        item.sellingPrice = sp
 55 
            item.risky = risky
247
- 
 
248 
        if weight != "":
- 
 
249 
            item.weight = weight
 56 
            item.weight = weight
250
- 
 
251 
        item.updatedOn = updatedOn
 57 
            item.updatedOn = updatedOn
- 
 
 58 
            item.type = int(item_type)
- 
 
 59 
            item_id = cclient.addItem(item)
252
60
253 
        item_change_log = ItemChangeLog()
 61 
            vip = VendorItemPricing()
254 
        item_change_log.new_status = item.status
 62 
            vip.itemId = item_id
255 
        item_change_log.timestamp = updatedOn
 63 
            vip.dealerPrice = round(dp)
256 
        item_change_log.item = item
 64 
            vip.mop = round(mop)
257
- 
 
258 
        if not dry_run:
 65 
            vip.nlc = round(nlc)
259 
            session.commit()
 66 
            vip.transferPrice = round(tp)
260
- 
 
261 
    write_report("/tmp/items_not_created_dueto_color.csv", not_created_items, sheet, False)
 - 
 
262 
    write_report("/tmp/new_items.csv", new_items, sheet, False)
 67 
            vip.vendorId = preferred_vendor
263 
    write_report("/tmp/updated_items.csv", updated_items, sheet, True)
 - 
 
264
68
265 
    if (not dry_run) and full_update:
 - 
 
266 
        query_string =  "UPDATE " + str(Item.table) + " SET status=" + str(status.PHASED_OUT) + ", status_description='This item has been phased out'" + ", updatedOn='"+ str(updatedOn) +"'"+\
 - 
 
267 
                        " WHERE updatedOn <> '" + str(updatedOn) + "'";
 - 
 
268 
        session.execute(query_string, mapper=Item)
 69 
            iclient.addVendorItemPricing(vip)
269 
        session.commit()
 70 
        except:
- 
 
 71 
            message = message + "\t" + str(brand) + "\t" + str(model_name) + "\t" + str(model_number) + "\n"
270
72
- 
 
 73 
    mail("build@shop2020.in", "cafe@nes", ["rajveer.singh@shop2020.in", "chandan.kumar@shop2020.in"], "Problem while adding items", message, [], [], [])
271 
    print "Successfully updated the item list information."
 74 
    print "Successfully updated the item list information."
272 
 
 75 
 
273 
def check_color_valid(color):
 - 
 
274 
    if color is not None:
 - 
 
275 
        color = color.strip().lower()
 - 
 
276 
        if color != '' and color != 'na' and color != 'blank' and color != '(blank)':
 - 
 
277 
            return True
 - 
 
278 
    return False
 - 
 
279 
 
 - 
 
280 
def write_report(filename, items, sheet, is_item):
 - 
 
281 
    '''
 - 
 
282 
    Iterates through the items list and writes all the values to the specified
 - 
 
283 
    filename in the CSV format. 'is_item' indicates whether the list consists
 - 
 
284 
    of row numbers or complete row data.
 - 
 
285 
    '''
 - 
 
286 
    items_writer = csv.writer(open(filename, "wb"), delimiter=',', quoting=csv.QUOTE_ALL)
 - 
 
287 
    items_writer.writerow(sheet.row_values(0)[0:23] + ['Old MRP', 'Old DP', 'Old TP', 'Old SP'])
 - 
 
288 
    if is_item:
 - 
 
289 
        #The list contains the complete rows.
 - 
 
290 
        for item in items:
 - 
 
291 
            print item
 - 
 
292 
            items_writer.writerow([str(value) for value in item])
 - 
 
293 
    else:
 - 
 
294 
        #The list only has row numbers. We've to fetch the data ourselves.
 - 
 
295 
        for i in items:
 - 
 
296 
            print sheet.row_values(i)
 - 
 
297 
            items_writer.writerow([str(value) for value in sheet.row_values(i)[0:23]])
 - 
 
298 
 
 76 
 
299 
def main():
 77 
def main():
300 
    parser = optparse.OptionParser()
 78 
    parser = optparse.OptionParser()
301 
    parser.add_option("-f", "--file", dest="filename",
 79 
    parser.add_option("-f", "--file", dest="filename",
302 
                   default="ItemList.xls", type="string",
 80 
                   default="ItemList.xls", type="string",
303 
                   help="Read the item list from FILE",
 81 
                   help="Read the item list from FILE",
304 
                   metavar="FILE")
 82 
                   metavar="FILE")
305 
    parser.add_option("-v", "--vendor", dest="vendor",
 - 
 
306 
                      type="int",
 - 
 
307 
                      help="Update the pricing information for VENDOR",
 - 
 
308 
                      metavar="VENDOR")
 - 
 
309 
    parser.add_option("-d", "--dry-run", dest="dry_run",
 - 
 
310 
                      action="store_true",
 - 
 
311 
                      help="Dry run only reporting on pending changes.Please note that some of the items can be reported twice.")
 - 
 
312 
    parser.add_option("-a", "--full", dest="full_update",
 - 
 
313 
                      action="store_true",
 - 
 
314 
                      help="In a full update, all older items are marked as PHASED_OUT. Also see -pm.")
 - 
 
315 
    parser.add_option("-p", "--partial", dest="full_update",
 - 
 
316 
                      action="store_false",
 - 
 
317 
                      help="In a partial update, older items are left as is. Also see -fm.")
 - 
 
318 
    parser.set_defaults(full_update=False, dry_run=False, vendor=1)
 - 
 
319 
    (options, args) = parser.parse_args()
 83 
    (options, args) = parser.parse_args()
320 
    if len(args) != 0:
 84 
    if len(args) != 0:
321 
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
 85 
        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")
322 
    filename = options.filename
 86 
    filename = options.filename
323 
    load_item_data(filename, options.vendor, options.full_update, options.dry_run)
 87 
    load_item_data(filename)
324 
 
 88 
 
325 
if __name__ == '__main__':
 89 
if __name__ == '__main__':
326 
    main()
 90 
    main()
327 

            91