| Line 1023... |
Line 1023... |
| 1023 |
sheet.write(0, 32, "Target TP", heading_xf)
|
1023 |
sheet.write(0, 32, "Target TP", heading_xf)
|
| 1024 |
sheet.write(0, 33, "Target NLC", heading_xf)
|
1024 |
sheet.write(0, 33, "Target NLC", heading_xf)
|
| 1025 |
sheet.write(0, 34, "Sales Potential", heading_xf)
|
1025 |
sheet.write(0, 34, "Sales Potential", heading_xf)
|
| 1026 |
sheet.write(0, 35, "Total Seller", heading_xf)
|
1026 |
sheet.write(0, 35, "Total Seller", heading_xf)
|
| 1027 |
sheet_iterator = 1
|
1027 |
sheet_iterator = 1
|
| 1028 |
canCompeteItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
1028 |
canCompeteItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
|
| - |
|
1029 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
| 1029 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
1030 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
| 1030 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
1031 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id))\
|
| 1031 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
1032 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| 1032 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
|
1033 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CANT_COMPETE).all()
|
| 1033 |
for item in canCompeteItems:
|
1034 |
for item in canCompeteItems:
|
| 1034 |
mpHistory = item[0]
|
1035 |
mpHistory = item[0]
|
| 1035 |
flipkartItem = item[1]
|
1036 |
flipkartItem = item[1]
|
| Line 1138... |
Line 1139... |
| 1138 |
sheet.write(0, 32, "Target TP", heading_xf)
|
1139 |
sheet.write(0, 32, "Target TP", heading_xf)
|
| 1139 |
sheet.write(0, 33, "Margin Increased Potential", heading_xf)
|
1140 |
sheet.write(0, 33, "Margin Increased Potential", heading_xf)
|
| 1140 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
1141 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
| 1141 |
sheet_iterator = 1
|
1142 |
sheet_iterator = 1
|
| 1142 |
|
1143 |
|
| 1143 |
buyBoxItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
1144 |
buyBoxItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join((MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
| 1144 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
1145 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
| 1145 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
1146 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id))\
|
| 1146 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
1147 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| 1147 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
|
1148 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.BUY_BOX).all()
|
| 1148 |
|
1149 |
|
| 1149 |
|
1150 |
|
| 1150 |
for item in buyBoxItems:
|
1151 |
for item in buyBoxItems:
|
| Line 1252... |
Line 1253... |
| 1252 |
sheet.write(0, 31, "Target SP", heading_xf)
|
1253 |
sheet.write(0, 31, "Target SP", heading_xf)
|
| 1253 |
sheet.write(0, 32, "Target TP", heading_xf)
|
1254 |
sheet.write(0, 32, "Target TP", heading_xf)
|
| 1254 |
sheet.write(0, 33, "Total Seller", heading_xf)
|
1255 |
sheet.write(0, 33, "Total Seller", heading_xf)
|
| 1255 |
sheet_iterator = 1
|
1256 |
sheet_iterator = 1
|
| 1256 |
|
1257 |
|
| 1257 |
prefNotCheapItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
1258 |
prefNotCheapItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join((MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
| 1258 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
1259 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
| 1259 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
1260 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id))\
|
| 1260 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
1261 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| 1261 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP).all()
|
1262 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.PREF_BUT_NOT_CHEAP).all()
|
| 1262 |
|
1263 |
|
| 1263 |
|
1264 |
|
| 1264 |
for item in prefNotCheapItems:
|
1265 |
for item in prefNotCheapItems:
|
| Line 1359... |
Line 1360... |
| 1359 |
sheet.write(0, 25, "Lowest Possible SP", heading_xf)
|
1360 |
sheet.write(0, 25, "Lowest Possible SP", heading_xf)
|
| 1360 |
sheet.write(0, 26, "Lowest Possible TP", heading_xf)
|
1361 |
sheet.write(0, 26, "Lowest Possible TP", heading_xf)
|
| 1361 |
sheet.write(0, 27, "Total Seller", heading_xf)
|
1362 |
sheet.write(0, 27, "Total Seller", heading_xf)
|
| 1362 |
sheet_iterator = 1
|
1363 |
sheet_iterator = 1
|
| 1363 |
|
1364 |
|
| 1364 |
cheapNotPrefferedItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
1365 |
cheapNotPrefferedItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join((MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
| 1365 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
1366 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
| 1366 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
1367 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id))\
|
| 1367 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
1368 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| 1368 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CHEAP_BUT_NOT_PREF).all()
|
1369 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.CHEAP_BUT_NOT_PREF).all()
|
| 1369 |
|
1370 |
|
| 1370 |
for item in cheapNotPrefferedItems:
|
1371 |
for item in cheapNotPrefferedItems:
|
| 1371 |
mpHistory = item[0]
|
1372 |
mpHistory = item[0]
|
| Line 1458... |
Line 1459... |
| 1458 |
sheet.write(0, 33, "Target NLC", heading_xf)
|
1459 |
sheet.write(0, 33, "Target NLC", heading_xf)
|
| 1459 |
sheet.write(0, 34, "Sales Potential", heading_xf)
|
1460 |
sheet.write(0, 34, "Sales Potential", heading_xf)
|
| 1460 |
sheet.write(0, 35, "Total Seller", heading_xf)
|
1461 |
sheet.write(0, 35, "Total Seller", heading_xf)
|
| 1461 |
sheet_iterator = 1
|
1462 |
sheet_iterator = 1
|
| 1462 |
|
1463 |
|
| 1463 |
competitiveItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
1464 |
competitiveItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join((MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
| 1464 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
1465 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
| 1465 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
1466 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id))\
|
| 1466 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
1467 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| 1467 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).all()
|
1468 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE).all()
|
| 1468 |
|
1469 |
|
| 1469 |
for item in competitiveItems:
|
1470 |
for item in competitiveItems:
|
| 1470 |
mpHistory = item[0]
|
1471 |
mpHistory = item[0]
|
| Line 1564... |
Line 1565... |
| 1564 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
1565 |
sheet.write(0, 30, "Lowest Possible TP", heading_xf)
|
| 1565 |
sheet.write(0, 31, "Margin", heading_xf)
|
1566 |
sheet.write(0, 31, "Margin", heading_xf)
|
| 1566 |
sheet.write(0, 32, "Total Seller", heading_xf)
|
1567 |
sheet.write(0, 32, "Total Seller", heading_xf)
|
| 1567 |
sheet_iterator = 1
|
1568 |
sheet_iterator = 1
|
| 1568 |
|
1569 |
|
| 1569 |
negativeMargin = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
1570 |
negativeMargin = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join((MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
| 1570 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
1571 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
| 1571 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
1572 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id))\
|
| 1572 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
1573 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| 1573 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).all()
|
1574 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).all()
|
| 1574 |
|
1575 |
|
| 1575 |
for item in negativeMargin:
|
1576 |
for item in negativeMargin:
|
| 1576 |
mpHistory = item[0]
|
1577 |
mpHistory = item[0]
|
| Line 1672... |
Line 1673... |
| 1672 |
sheet.write(0, 2, "Brand", heading_xf)
|
1673 |
sheet.write(0, 2, "Brand", heading_xf)
|
| 1673 |
sheet.write(0, 3, "Product Name", heading_xf)
|
1674 |
sheet.write(0, 3, "Product Name", heading_xf)
|
| 1674 |
sheet.write(0, 4, "Reason", heading_xf)
|
1675 |
sheet.write(0, 4, "Reason", heading_xf)
|
| 1675 |
sheet_iterator=1
|
1676 |
sheet_iterator=1
|
| 1676 |
|
1677 |
|
| 1677 |
exeptionItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
1678 |
exeptionItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join((MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
| 1678 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
1679 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
| 1679 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
1680 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id))\
|
| 1680 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
1681 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| 1681 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.EXCEPTION).all()
|
1682 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.EXCEPTION).all()
|
| 1682 |
|
1683 |
|
| 1683 |
for item in exeptionItems:
|
1684 |
for item in exeptionItems:
|
| 1684 |
mpHistory = item[0]
|
1685 |
mpHistory = item[0]
|
| Line 1744... |
Line 1745... |
| 1744 |
sheet.write(0, 32, "Target TP", heading_xf)
|
1745 |
sheet.write(0, 32, "Target TP", heading_xf)
|
| 1745 |
sheet.write(0, 33, "Sales Potential", heading_xf)
|
1746 |
sheet.write(0, 33, "Sales Potential", heading_xf)
|
| 1746 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
1747 |
sheet.write(0, 34, "Total Seller", heading_xf)
|
| 1747 |
sheet_iterator = 1
|
1748 |
sheet_iterator = 1
|
| 1748 |
|
1749 |
|
| 1749 |
competitiveNoInventory = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join(MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id)\
|
1750 |
competitiveNoInventory = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item).join((MarketPlaceHistory,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
|
| 1750 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId)\
|
1751 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
|
| 1751 |
.join(MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id)\
|
1752 |
.join((MarketPlaceHistory,MarketPlaceHistory.item_id==Item.id))\
|
| 1752 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
1753 |
.filter(MarketplaceItems.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.source==OrderSource.FLIPKART)\
|
| 1753 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE_NO_INVENTORY).all()
|
1754 |
.filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.COMPETITIVE_NO_INVENTORY).all()
|
| 1754 |
|
1755 |
|
| 1755 |
for item in competitiveNoInventory:
|
1756 |
for item in competitiveNoInventory:
|
| 1756 |
mpHistory = item[0]
|
1757 |
mpHistory = item[0]
|