WebSVN – SmartDukaan – Diff – /trunk/PyProj/src/shop2020/model/v1/catalog/script/AmazonHistoryPopulator.py



    sheet.write(0, 33, "Decision", heading_xf)
    sheet.write(0, 34, "Reason", heading_xf)
    sheet.write(0, 35, "Updated Price", heading_xf)
    
    sheet_iterator = 1
    cantCompeteItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for cantCompeteItem in cantCompeteItems:
        amScraping =  cantCompeteItem[0]
        item = cantCompeteItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:

    sheet.write(0, 33, "Decision", heading_xf)
    sheet.write(0, 34, "Reason", heading_xf)
    sheet.write(0, 35, "Updated Price", heading_xf)
    
    sheet_iterator = 1
    competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for competitiveItem in competitiveItems:
        amScraping =  competitiveItem[0]
        item = competitiveItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:

    sheet.write(0, 33, "Decision", heading_xf)
    sheet.write(0, 34, "Reason", heading_xf)
    sheet.write(0, 35, "Updated Price", heading_xf)
    
    sheet_iterator = 1
    almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for almostCompetitiveItem in almostCompetitiveItems:
        amScraping =  almostCompetitiveItem[0]
        item = almostCompetitiveItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:

    sheet.write(0, 33, "Decision", heading_xf)
    sheet.write(0, 34, "Reason", heading_xf)
    sheet.write(0, 35, "Updated Price", heading_xf)
    
    sheet_iterator = 1
    amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for amongCheapestItem in amongCheapestItems:
        amScraping =  amongCheapestItem[0]
        item = amongCheapestItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:

    sheet.write(0, 33, "Decision", heading_xf)
    sheet.write(0, 34, "Reason", heading_xf)
    sheet.write(0, 35, "Updated Price", heading_xf)
    
    sheet_iterator = 1
    cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for cheapestItem in cheapestItems:
        amScraping =  cheapestItem[0]
        item = cheapestItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:

    sheet.write(0, 28, "Margin", heading_xf)
    sheet.write(0, 29, "Avg Sale", heading_xf)
    sheet.write(0, 30, "Sales History", heading_xf)
    
    sheet_iterator = 1
    amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for amongCheapestItem in amongCheapestItems:
        amScraping =  amongCheapestItem[0]
        item = amongCheapestItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:

    sheet.write(0, 4, "Brand", heading_xf)
    sheet.write(0, 5, "Product Name", heading_xf)
    sheet.write(0, 6, "Reason", heading_xf)
    
    sheet_iterator = 1
    amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()
    for amongCheapestItem in amongCheapestItems:
        amScraping =  amongCheapestItem[0]
        item = amongCheapestItem[1]
        sheet.write(sheet_iterator, 0, amScraping.item_id)
        if amScraping.warehouseLocation == 1:

            sheet.write(sheet_iterator, 1, it.brand)
            sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
            sheet.write(sheet_iterator, 3, "False")
            sheet_iterator+=1
    
    filename = "/tmp/flipkart-report-"+runType+" " + str(timestamp) + ".xls"
    wbk.save(filename)
    
def main():
    parser = optparse.OptionParser()
    parser.add_option("-t", "--type", dest="runType",

Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/catalog/script/AmazonHistoryPopulator.py – Rev 12475 → 12476

Rev 12475		Rev 12476
Line 999...		Line 999...
999	`sheet.write(0, 33, "Decision", heading_xf)`	999	`sheet.write(0, 33, "Decision", heading_xf)`
1000	`sheet.write(0, 34, "Reason", heading_xf)`	1000	`sheet.write(0, 34, "Reason", heading_xf)`
1001	`sheet.write(0, 35, "Updated Price", heading_xf)`	1001	`sheet.write(0, 35, "Updated Price", heading_xf)`
1002		1002
1003	`sheet_iterator = 1`	1003	`sheet_iterator = 1`
1004	`cantCompeteItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()`	1004	`cantCompeteItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()`
1005	`for cantCompeteItem in cantCompeteItems:`	1005	`for cantCompeteItem in cantCompeteItems:`
1006	`amScraping = cantCompeteItem[0]`	1006	`amScraping = cantCompeteItem[0]`
1007	`item = cantCompeteItem[1]`	1007	`item = cantCompeteItem[1]`
1008	`sheet.write(sheet_iterator, 0, amScraping.item_id)`	1008	`sheet.write(sheet_iterator, 0, amScraping.item_id)`
1009	`if amScraping.warehouseLocation == 1:`	1009	`if amScraping.warehouseLocation == 1:`
Line 1106...		Line 1106...
1106	`sheet.write(0, 33, "Decision", heading_xf)`	1106	`sheet.write(0, 33, "Decision", heading_xf)`
1107	`sheet.write(0, 34, "Reason", heading_xf)`	1107	`sheet.write(0, 34, "Reason", heading_xf)`
1108	`sheet.write(0, 35, "Updated Price", heading_xf)`	1108	`sheet.write(0, 35, "Updated Price", heading_xf)`
1109		1109
1110	`sheet_iterator = 1`	1110	`sheet_iterator = 1`
1111	`competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).all()`	1111	`competitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).filter(AmazonScrapingHistory.timestamp==timestamp).all()`
1112	`for competitiveItem in competitiveItems:`	1112	`for competitiveItem in competitiveItems:`
1113	`amScraping = competitiveItem[0]`	1113	`amScraping = competitiveItem[0]`
1114	`item = competitiveItem[1]`	1114	`item = competitiveItem[1]`
1115	`sheet.write(sheet_iterator, 0, amScraping.item_id)`	1115	`sheet.write(sheet_iterator, 0, amScraping.item_id)`
1116	`if amScraping.warehouseLocation == 1:`	1116	`if amScraping.warehouseLocation == 1:`
Line 1213...		Line 1213...
1213	`sheet.write(0, 33, "Decision", heading_xf)`	1213	`sheet.write(0, 33, "Decision", heading_xf)`
1214	`sheet.write(0, 34, "Reason", heading_xf)`	1214	`sheet.write(0, 34, "Reason", heading_xf)`
1215	`sheet.write(0, 35, "Updated Price", heading_xf)`	1215	`sheet.write(0, 35, "Updated Price", heading_xf)`
1216		1216
1217	`sheet_iterator = 1`	1217	`sheet_iterator = 1`
1218	`almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).all()`	1218	`almostCompetitiveItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.ALMOST_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()`
1219	`for almostCompetitiveItem in almostCompetitiveItems:`	1219	`for almostCompetitiveItem in almostCompetitiveItems:`
1220	`amScraping = almostCompetitiveItem[0]`	1220	`amScraping = almostCompetitiveItem[0]`
1221	`item = almostCompetitiveItem[1]`	1221	`item = almostCompetitiveItem[1]`
1222	`sheet.write(sheet_iterator, 0, amScraping.item_id)`	1222	`sheet.write(sheet_iterator, 0, amScraping.item_id)`
1223	`if amScraping.warehouseLocation == 1:`	1223	`if amScraping.warehouseLocation == 1:`
Line 1329...		Line 1329...
1329	`sheet.write(0, 33, "Decision", heading_xf)`	1329	`sheet.write(0, 33, "Decision", heading_xf)`
1330	`sheet.write(0, 34, "Reason", heading_xf)`	1330	`sheet.write(0, 34, "Reason", heading_xf)`
1331	`sheet.write(0, 35, "Updated Price", heading_xf)`	1331	`sheet.write(0, 35, "Updated Price", heading_xf)`
1332		1332
1333	`sheet_iterator = 1`	1333	`sheet_iterator = 1`
1334	`amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE).all()`	1334	`amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.AMONG_CHEAPEST_CAN_COMPETE).filter(AmazonScrapingHistory.timestamp==timestamp).all()`
1335	`for amongCheapestItem in amongCheapestItems:`	1335	`for amongCheapestItem in amongCheapestItems:`
1336	`amScraping = amongCheapestItem[0]`	1336	`amScraping = amongCheapestItem[0]`
1337	`item = amongCheapestItem[1]`	1337	`item = amongCheapestItem[1]`
1338	`sheet.write(sheet_iterator, 0, amScraping.item_id)`	1338	`sheet.write(sheet_iterator, 0, amScraping.item_id)`
1339	`if amScraping.warehouseLocation == 1:`	1339	`if amScraping.warehouseLocation == 1:`
Line 1436...		Line 1436...
1436	`sheet.write(0, 33, "Decision", heading_xf)`	1436	`sheet.write(0, 33, "Decision", heading_xf)`
1437	`sheet.write(0, 34, "Reason", heading_xf)`	1437	`sheet.write(0, 34, "Reason", heading_xf)`
1438	`sheet.write(0, 35, "Updated Price", heading_xf)`	1438	`sheet.write(0, 35, "Updated Price", heading_xf)`
1439		1439
1440	`sheet_iterator = 1`	1440	`sheet_iterator = 1`
1441	`cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()`	1441	`cheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.BUY_BOX).filter(AmazonScrapingHistory.timestamp==timestamp).all()`
1442	`for cheapestItem in cheapestItems:`	1442	`for cheapestItem in cheapestItems:`
1443	`amScraping = cheapestItem[0]`	1443	`amScraping = cheapestItem[0]`
1444	`item = cheapestItem[1]`	1444	`item = cheapestItem[1]`
1445	`sheet.write(sheet_iterator, 0, amScraping.item_id)`	1445	`sheet.write(sheet_iterator, 0, amScraping.item_id)`
1446	`if amScraping.warehouseLocation == 1:`	1446	`if amScraping.warehouseLocation == 1:`
Line 1538...		Line 1538...
1538	`sheet.write(0, 28, "Margin", heading_xf)`	1538	`sheet.write(0, 28, "Margin", heading_xf)`
1539	`sheet.write(0, 29, "Avg Sale", heading_xf)`	1539	`sheet.write(0, 29, "Avg Sale", heading_xf)`
1540	`sheet.write(0, 30, "Sales History", heading_xf)`	1540	`sheet.write(0, 30, "Sales History", heading_xf)`
1541		1541
1542	`sheet_iterator = 1`	1542	`sheet_iterator = 1`
1543	`amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).all()`	1543	`amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).filter(AmazonScrapingHistory.timestamp==timestamp).all()`
1544	`for amongCheapestItem in amongCheapestItems:`	1544	`for amongCheapestItem in amongCheapestItems:`
1545	`amScraping = amongCheapestItem[0]`	1545	`amScraping = amongCheapestItem[0]`
1546	`item = amongCheapestItem[1]`	1546	`item = amongCheapestItem[1]`
1547	`sheet.write(sheet_iterator, 0, amScraping.item_id)`	1547	`sheet.write(sheet_iterator, 0, amScraping.item_id)`
1548	`if amScraping.warehouseLocation == 1:`	1548	`if amScraping.warehouseLocation == 1:`
Line 1604...		Line 1604...
1604	`sheet.write(0, 4, "Brand", heading_xf)`	1604	`sheet.write(0, 4, "Brand", heading_xf)`
1605	`sheet.write(0, 5, "Product Name", heading_xf)`	1605	`sheet.write(0, 5, "Product Name", heading_xf)`
1606	`sheet.write(0, 6, "Reason", heading_xf)`	1606	`sheet.write(0, 6, "Reason", heading_xf)`
1607		1607
1608	`sheet_iterator = 1`	1608	`sheet_iterator = 1`
1609	`amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).all()`	1609	`amongCheapestItems = session.query(AmazonScrapingHistory,Item).join((Item,AmazonScrapingHistory.item_id==Item.id)).filter(AmazonScrapingHistory.competitiveCategory==CompetitionCategory.EXCEPTION).filter(AmazonScrapingHistory.timestamp==timestamp).all()`
1610	`for amongCheapestItem in amongCheapestItems:`	1610	`for amongCheapestItem in amongCheapestItems:`
1611	`amScraping = amongCheapestItem[0]`	1611	`amScraping = amongCheapestItem[0]`
1612	`item = amongCheapestItem[1]`	1612	`item = amongCheapestItem[1]`
1613	`sheet.write(sheet_iterator, 0, amScraping.item_id)`	1613	`sheet.write(sheet_iterator, 0, amScraping.item_id)`
1614	`if amScraping.warehouseLocation == 1:`	1614	`if amScraping.warehouseLocation == 1:`
Line 1659...		Line 1659...
1659	`sheet.write(sheet_iterator, 1, it.brand)`	1659	`sheet.write(sheet_iterator, 1, it.brand)`
1660	`sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))`	1660	`sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))`
1661	`sheet.write(sheet_iterator, 3, "False")`	1661	`sheet.write(sheet_iterator, 3, "False")`
1662	`sheet_iterator+=1`	1662	`sheet_iterator+=1`
1663		1663
1664	`filename = "/tmp/amazon-scraping.xls"`	1664	`filename = "/tmp/flipkart-report-"+runType+" " + str(timestamp) + ".xls"`
1665	`wbk.save(filename)`	1665	`wbk.save(filename)`
1666		1666
1667	`def main():`	1667	`def main():`
1668	`parser = optparse.OptionParser()`	1668	`parser = optparse.OptionParser()`
1669	`parser.add_option("-t", "--type", dest="runType",`	1669	`parser.add_option("-t", "--type", dest="runType",`