| Line 19... |
Line 19... |
| 19 |
from email.mime.text import MIMEText
|
19 |
from email.mime.text import MIMEText
|
| 20 |
from pymongo.mongo_client import MongoClient
|
20 |
from pymongo.mongo_client import MongoClient
|
| 21 |
from xlrd import open_workbook
|
21 |
from xlrd import open_workbook
|
| 22 |
from xlutils.copy import copy
|
22 |
from xlutils.copy import copy
|
| 23 |
from xlwt.Workbook import Workbook
|
23 |
from xlwt.Workbook import Workbook
|
| - |
|
24 |
import _mysql
|
| 24 |
import MySQLdb
|
25 |
import MySQLdb
|
| 25 |
import smtplib
|
26 |
import smtplib
|
| 26 |
import time
|
27 |
import time
|
| 27 |
import xlwt
|
28 |
import xlwt
|
| 28 |
from dtr.utils import utils
|
29 |
from dtr.utils import utils
|
| 29 |
from dtr.utils.utils import toTimeStamp
|
30 |
from dtr.utils.utils import toTimeStamp
|
| - |
|
31 |
from dtr.reports import getdata
|
| 30 |
#from xlwt import
|
32 |
#from xlwt import
|
| 31 |
|
33 |
|
| 32 |
DataService.initialize()
|
34 |
DataService.initialize()
|
| 33 |
client = MongoClient('mongodb://localhost:27017/')
|
35 |
client = MongoClient('mongodb://localhost:27017/')
|
| 34 |
|
36 |
|
| Line 948... |
Line 950... |
| 948 |
workbook = xlwt.Workbook("utf8")
|
950 |
workbook = xlwt.Workbook("utf8")
|
| 949 |
sh1 = workbook.add_sheet("All Orders")
|
951 |
sh1 = workbook.add_sheet("All Orders")
|
| 950 |
sh2 = workbook.add_sheet("Flipkart Orders")
|
952 |
sh2 = workbook.add_sheet("Flipkart Orders")
|
| 951 |
|
953 |
|
| 952 |
db=client.Dtr
|
954 |
db=client.Dtr
|
| 953 |
results = Mysql.fetchResult('''
|
955 |
# results = Mysql.fetchResult('''
|
| 954 |
select ow.*, u.username, crm1.order_count, u.referrer, aua.city,aua.state,aua.pincode from order_view ow
|
956 |
# select ow.*, u.username, crm1.order_count, u.referrer, aua.city,aua.state,aua.pincode from order_view ow
|
| 955 |
left join users u on u.id = ow.user_id
|
957 |
# left join users u on u.id = ow.user_id
|
| 956 |
left join (select user_id, count(*) as order_count from order_view where status in ('ORDER_CREATED','DETAIL_CREATED') group by user_id)as crm1 on ow.user_id = crm1.user_id
|
958 |
# left join (select user_id, count(*) as order_count from order_view where status in ('ORDER_CREATED','DETAIL_CREATED') group by user_id)as crm1 on ow.user_id = crm1.user_id
|
| 957 |
left join (select * from (select * from all_user_addresses order by source) s group by user_id)aua on aua.user_id=u.id
|
959 |
# left join (select * from (select * from all_user_addresses order by source) s group by user_id)aua on aua.user_id=u.id
|
| 958 |
where (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) and u.activated = 1 and ow.status in ('ORDER_CREATED','DETAIL_CREATED');
|
960 |
# where (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) and u.activated = 1 and ow.status in ('ORDER_CREATED','DETAIL_CREATED');
|
| - |
|
961 |
#
|
| 959 |
''',)
|
962 |
# ''',)
|
| - |
|
963 |
con = MySQLdb.connect('localhost','root','shop2020','dtr')
|
| - |
|
964 |
cur = con.cursor()
|
| - |
|
965 |
cur.execute("select * from allorder")
|
| - |
|
966 |
results = cur.fetchall()
|
| 960 |
row = 0
|
967 |
row = 0
|
| 961 |
i=-1
|
968 |
i=-1
|
| 962 |
worksheet.write(row, inc(), 'User Id', boldStyle)
|
969 |
worksheet.write(row, inc(), 'User Id', boldStyle)
|
| 963 |
#worksheet1.write(row, i, 'User Id', boldStyle)
|
970 |
#worksheet1.write(row, i, 'User Id', boldStyle)
|
| 964 |
worksheet.write(row, inc(), 'User name', boldStyle)
|
971 |
worksheet.write(row, inc(), 'User name', boldStyle)
|
| Line 1031... |
Line 1038... |
| 1031 |
row=0
|
1038 |
row=0
|
| 1032 |
row2=0
|
1039 |
row2=0
|
| 1033 |
row3=0
|
1040 |
row3=0
|
| 1034 |
db1 = client.Catalog
|
1041 |
db1 = client.Catalog
|
| 1035 |
for result in results:
|
1042 |
for result in results:
|
| 1036 |
morder = db.merchantOrder.find_one({"orderId":result[0]})
|
1043 |
morder = db.merchantOrder.find_one({"orderId":result[2]})
|
| 1037 |
if morder is not None:
|
1044 |
if morder is not None:
|
| 1038 |
subOrders = morder.get("subOrders")
|
1045 |
subOrders = morder.get("subOrders")
|
| 1039 |
if subOrders is not None:
|
1046 |
if subOrders is not None:
|
| 1040 |
for subOrder in subOrders:
|
1047 |
for subOrder in subOrders:
|
| 1041 |
row3+=1
|
1048 |
row3+=1
|
| 1042 |
i=-1
|
1049 |
i=-1
|
| - |
|
1050 |
# sh1.write(row3, inc(), result[1])
|
| - |
|
1051 |
# sh1.write(row3, inc(), result[7])
|
| - |
|
1052 |
# sh1.write(row3, inc(), result[0])
|
| - |
|
1053 |
# sh1.write(row3, inc(), result[6], datetime_format)
|
| - |
|
1054 |
# sh1.write(row3, inc(), sourceMap.get(result[2]))
|
| - |
|
1055 |
# sh1.write(row3, inc(), morder.get("merchantOrderId"))
|
| - |
|
1056 |
# sh1.write(row3, inc(), subOrder.get("status"))
|
| - |
|
1057 |
# sh1.write(row3, inc(), subOrder.get("detailedStatus"))
|
| - |
|
1058 |
# sh1.write(row3, inc(), subOrder.get("productTitle"))
|
| - |
|
1059 |
# sh1.write(row3, inc(), result[9])
|
| - |
|
1060 |
sh1.write(row3, inc(), result[0])
|
| 1043 |
sh1.write(row3, inc(), result[1])
|
1061 |
sh1.write(row3, inc(), result[1])
|
| - |
|
1062 |
sh1.write(row3, inc(), result[2])
|
| - |
|
1063 |
sh1.write(row3, inc(), result[3])
|
| - |
|
1064 |
sh1.write(row3, inc(), result[4])
|
| - |
|
1065 |
sh1.write(row3, inc(), result[5])
|
| - |
|
1066 |
sh1.write(row3, inc(), result[6])
|
| 1044 |
sh1.write(row3, inc(), result[7])
|
1067 |
sh1.write(row3, inc(), result[7])
|
| 1045 |
sh1.write(row3, inc(), result[0])
|
1068 |
sh1.write(row3, inc(), result[8])
|
| 1046 |
sh1.write(row3, inc(), result[6], datetime_format)
|
- |
|
| 1047 |
sh1.write(row3, inc(), sourceMap.get(result[2]))
|
- |
|
| 1048 |
sh1.write(row3, inc(), morder.get("merchantOrderId"))
|
- |
|
| 1049 |
sh1.write(row3, inc(), subOrder.get("status"))
|
- |
|
| 1050 |
sh1.write(row3, inc(), subOrder.get("detailedStatus"))
|
- |
|
| 1051 |
sh1.write(row3, inc(), subOrder.get("productTitle"))
|
- |
|
| 1052 |
sh1.write(row3, inc(), result[9])
|
1069 |
sh1.write(row3, inc(), result[9])
|
| - |
|
1070 |
sh1.write(row3, inc(), result[10])
|
| 1053 |
inc()
|
1071 |
inc()
|
| 1054 |
if int(subOrder.get("quantity")) > 0:
|
1072 |
# if int(subOrder.get("quantity")) > 0:
|
| 1055 |
sh1.write(row3, i, int(subOrder.get("amountPaid"))/int(subOrder.get("quantity")))
|
1073 |
# sh1.write(row3, i, int(subOrder.get("amountPaid"))/int(subOrder.get("quantity")))
|
| 1056 |
skuData = getSkuData(morder.get("storeId"), subOrder.get("productCode"))
|
1074 |
# skuData = getSkuData(morder.get("storeId"), subOrder.get("productCode"))
|
| 1057 |
if morder.get("storeId") in (1,2,4,5,6):
|
1075 |
# if morder.get("storeId") in (1,2,4,5,6):
|
| 1058 |
skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
|
1076 |
# skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
|
| 1059 |
elif morder.get("storeId") == 3:
|
1077 |
# elif morder.get("storeId") == 3:
|
| 1060 |
skuData = db1.MasterData.find_one({'secondaryIdentifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
|
1078 |
# skuData = db1.MasterData.find_one({'secondaryIdentifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
|
| 1061 |
if skuData is not None:
|
1079 |
# if skuData is not None:
|
| 1062 |
sh1.write(row3, inc(), skuData.get("skuBundleId"))
|
1080 |
# sh1.write(row3, inc(), skuData.get("skuBundleId"))
|
| 1063 |
sh1.write(row3, inc(), skuData.get("brand"))
|
1081 |
# sh1.write(row3, inc(), skuData.get("brand"))
|
| 1064 |
sh1.write(row3, inc(), skuData.get("model_name"))
|
1082 |
# sh1.write(row3, inc(), skuData.get("model_name"))
|
| 1065 |
sh1.write(row3, inc(), skuData.get("category"))
|
1083 |
# sh1.write(row3, inc(), skuData.get("category"))
|
| 1066 |
sh1.write(row3, inc(), subOrder.get("dealRank"))
|
1084 |
# sh1.write(row3, inc(), subOrder.get("dealRank"))
|
| 1067 |
sh1.write(row3, inc(), subOrder.get("maxNlc"))
|
1085 |
# sh1.write(row3, inc(), subOrder.get("maxNlc"))
|
| 1068 |
sh1.write(row3, inc(), subOrder.get("minNlc"))
|
1086 |
# sh1.write(row3, inc(), subOrder.get("minNlc"))
|
| 1069 |
sh1.write(row3, inc(), utils.statusMap.get(subOrder.get("db")))
|
1087 |
# sh1.write(row3, inc(), utils.statusMap.get(subOrder.get("db")))
|
| 1070 |
|
1088 |
#
|
| 1071 |
sh1.write(row3, inc(), subOrder.get("itemStatus"))
|
1089 |
# sh1.write(row3, inc(), subOrder.get("itemStatus"))
|
| 1072 |
else:
|
1090 |
# else:
|
| 1073 |
sh1.write(row3, inc(), None)
|
1091 |
# sh1.write(row3, inc(), None)
|
| 1074 |
sh1.write(row3, inc(), None)
|
1092 |
# sh1.write(row3, inc(), None)
|
| 1075 |
sh1.write(row3, inc(), None)
|
1093 |
# sh1.write(row3, inc(), None)
|
| 1076 |
sh1.write(row3, inc(), None)
|
1094 |
# sh1.write(row3, inc(), None)
|
| 1077 |
sh1.write(row3, inc(), None)
|
1095 |
# sh1.write(row3, inc(), None)
|
| 1078 |
sh1.write(row3, inc(), None)
|
1096 |
# sh1.write(row3, inc(), None)
|
| 1079 |
sh1.write(row3, inc(), None)
|
1097 |
# sh1.write(row3, inc(), None)
|
| 1080 |
sh1.write(row3, inc(), None)
|
1098 |
# sh1.write(row3, inc(), None)
|
| 1081 |
sh1.write(row3, inc(), None)
|
1099 |
# sh1.write(row3, inc(), None)
|
| 1082 |
print result[10]
|
- |
|
| 1083 |
sh1.write(row3, inc(), unicode(result[10], errors='ignore') if result[10] else None)
|
- |
|
| 1084 |
sh1.write(row3, inc(), result[11])
|
1100 |
sh1.write(row3, inc(), result[11])
|
| 1085 |
sh1.write(row3, inc(), result[12])
|
1101 |
sh1.write(row3, inc(), result[12])
|
| - |
|
1102 |
sh1.write(row3, inc(), result[13])
|
| - |
|
1103 |
sh1.write(row3, inc(), result[14])
|
| - |
|
1104 |
sh1.write(row3, inc(), result[15])
|
| - |
|
1105 |
sh1.write(row3, inc(), result[16])
|
| - |
|
1106 |
sh1.write(row3, inc(), result[17])
|
| - |
|
1107 |
sh1.write(row3, inc(), result[18])
|
| - |
|
1108 |
sh1.write(row3, inc(), result[19])
|
| - |
|
1109 |
# print result[10]
|
| - |
|
1110 |
# sh1.write(row3, inc(), unicode(result[10], errors='ignore') if result[10] else None)
|
| - |
|
1111 |
# sh1.write(row3, inc(), result[11])
|
| - |
|
1112 |
# sh1.write(row3, inc(), result[12])
|
| - |
|
1113 |
sh1.write(row3, inc(), result[20])
|
| - |
|
1114 |
sh1.write(row3, inc(), result[21])
|
| - |
|
1115 |
sh1.write(row3, inc(), result[22])
|
| 1086 |
|
1116 |
|
| 1087 |
#removed filter as requested by Rajneesh
|
1117 |
#removed filter as requested by Rajneesh
|
| 1088 |
#if subOrder.get("cashBackStatus") == Store.CB_APPROVED or subOrder.get("cashBackStatus") == Store.CB_PENDING:
|
1118 |
#if subOrder.get("cashBackStatus") == Store.CB_APPROVED or subOrder.get("cashBackStatus") == Store.CB_PENDING:
|
| 1089 |
row +=1
|
1119 |
row +=1
|
| 1090 |
i=-1
|
1120 |
i=-1
|
| - |
|
1121 |
# worksheet.write(row, inc(), result[0])
|
| - |
|
1122 |
# worksheet.write(row, inc(), result[7])
|
| - |
|
1123 |
# worksheet.write(row, inc(), result[1])
|
| - |
|
1124 |
# worksheet.write(row, inc(), result[6], datetime_format)
|
| - |
|
1125 |
# worksheet.write(row, inc(), result[2])
|
| - |
|
1126 |
# worksheet.write(row, inc(), morder.get("merchantOrderId"))
|
| - |
|
1127 |
# worksheet.write(row, inc(), subOrder.get("merchantSubOrderId"))
|
| - |
|
1128 |
# worksheet.write(row, inc(), subOrder.get("productTitle"))
|
| - |
|
1129 |
# worksheet.write(row, inc(), subOrder.get("amountPaid"))
|
| - |
|
1130 |
# worksheet.write(row, inc(), subOrder.get("cashBackAmount"))
|
| - |
|
1131 |
# worksheet.write(row, inc(), subOrder.get("status"))
|
| - |
|
1132 |
# worksheet.write(row, inc(), subOrder.get("detailedStatus"))
|
| - |
|
1133 |
# worksheet.write(row, inc(), subOrder.get("cashBackStatus"))
|
| - |
|
1134 |
|
| - |
|
1135 |
worksheet.write(row, inc(), result[2])
|
| - |
|
1136 |
worksheet.write(row, inc(), result[1])
|
| 1091 |
worksheet.write(row, inc(), result[0])
|
1137 |
worksheet.write(row, inc(), result[0])
|
| - |
|
1138 |
worksheet.write(row, inc(), result[3])
|
| - |
|
1139 |
worksheet.write(row, inc(), result[4])
|
| - |
|
1140 |
worksheet.write(row, inc(), result[5])
|
| - |
|
1141 |
worksheet.write(row, inc(), result[23])
|
| - |
|
1142 |
worksheet.write(row, inc(), result[8])
|
| - |
|
1143 |
worksheet.write(row, inc(), result[10])
|
| - |
|
1144 |
worksheet.write(row, inc(), result[25])
|
| - |
|
1145 |
worksheet.write(row, inc(), result[6])
|
| 1092 |
worksheet.write(row, inc(), result[7])
|
1146 |
worksheet.write(row, inc(), result[7])
|
| 1093 |
worksheet.write(row, inc(), result[1])
|
1147 |
worksheet.write(row, inc(), result[24])
|
| 1094 |
worksheet.write(row, inc(), result[6], datetime_format)
|
- |
|
| 1095 |
worksheet.write(row, inc(), result[2])
|
1148 |
worksheet.write(row, inc(), result[8])
|
| 1096 |
worksheet.write(row, inc(), morder.get("merchantOrderId"))
|
- |
|
| 1097 |
worksheet.write(row, inc(), subOrder.get("merchantSubOrderId"))
|
- |
|
| 1098 |
worksheet.write(row, inc(), subOrder.get("productTitle"))
|
- |
|
| 1099 |
worksheet.write(row, inc(), subOrder.get("amountPaid"))
|
- |
|
| 1100 |
worksheet.write(row, inc(), subOrder.get("cashBackAmount"))
|
- |
|
| 1101 |
worksheet.write(row, inc(), subOrder.get("status"))
|
- |
|
| 1102 |
worksheet.write(row, inc(), subOrder.get("detailedStatus"))
|
- |
|
| 1103 |
worksheet.write(row, inc(), subOrder.get("cashBackStatus"))
|
- |
|
| - |
|
1149 |
|
| 1104 |
worksheet.write(row, inc(), False if morder.get("reconciled") is None else True)
|
1150 |
#worksheet.write(row, inc(), False if morder.get("reconciled") is None else True)
|
| 1105 |
if morder.get("reconciled") and morder.get("storeId")==3:
|
1151 |
#if morder.get("reconciled") and morder.get("storeId")==3:
|
| 1106 |
try:
|
1152 |
# try:
|
| 1107 |
snapdealafinfo = db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True})
|
1153 |
# snapdealafinfo = db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True})
|
| 1108 |
worksheet.write(row, inc(), snapdealafinfo.get("ip"))
|
1154 |
# worksheet.write(row, inc(), snapdealafinfo.get("ip"))
|
| 1109 |
worksheet.write(row, inc(), snapdealafinfo.get("conversionStatus"))
|
1155 |
# worksheet.write(row, inc(), snapdealafinfo.get("conversionStatus"))
|
| 1110 |
except:
|
1156 |
# except:
|
| 1111 |
print "Could not find", morder.get("adId"), "that is reconciled for order", morder.get("orderId")
|
1157 |
# print "Could not find", morder.get("adId"), "that is reconciled for order", morder.get("orderId")
|
| 1112 |
#if result[8] >= 10:
|
1158 |
#if result[8] >= 10:
|
| 1113 |
# row2 +=1
|
1159 |
# row2 +=1
|
| 1114 |
# i=-1
|
1160 |
# i=-1
|
| 1115 |
#worksheet1.write(row2, inc(), result[0])
|
1161 |
#worksheet1.write(row2, inc(), result[0])
|
| 1116 |
#worksheet1.write(row2, inc(), result[7])
|
1162 |
#worksheet1.write(row2, inc(), result[7])
|
| Line 1219... |
Line 1265... |
| 1219 |
i = 0
|
1265 |
i = 0
|
| 1220 |
sheet.write(0, inc(), )
|
1266 |
sheet.write(0, inc(), )
|
| 1221 |
def main():
|
1267 |
def main():
|
| 1222 |
|
1268 |
|
| 1223 |
message = generateSnapDealReco()
|
1269 |
message = generateSnapDealReco()
|
| - |
|
1270 |
# message ="Kuch nahi"
|
| 1224 |
generateFlipkartReco()
|
1271 |
generateFlipkartReco()
|
| 1225 |
generateShopcluesReco()
|
1272 |
generateShopcluesReco()
|
| 1226 |
generateAmazonReco(XLS_A_FILENAME)
|
1273 |
generateAmazonReco(XLS_A_FILENAME)
|
| - |
|
1274 |
message1 = getdata.summaryByBrandAndStore()
|
| 1227 |
getUserOrders()
|
1275 |
getUserOrders()
|
| - |
|
1276 |
#getdata.sendmail(["naman.kumar@shop2020.in"], message1, "Summary by brand and store")
|
| - |
|
1277 |
#sendmail(["naman.kumar@shop2020.in"], message, "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
|
| - |
|
1278 |
#sendmail(["naman.kumar@shop2020.in"], message1, "All DTR Orders", XLS_O_FILENAME)
|
| - |
|
1279 |
sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com", "manish.sharma@shop2020.in"], message1, "All DTR Orders", XLS_O_FILENAME)
|
| 1228 |
sendmail(["amit.gupta@shop2020.in","rajneesh.arora@saholic.com","manish.sharma@shop2020.in"], message, "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
|
1280 |
sendmail(["amit.gupta@shop2020.in","rajneesh.arora@saholic.com","manish.sharma@shop2020.in"], message, "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
|
| 1229 |
#sendmail(["amit.gupta@shop2020.in"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
|
1281 |
#sendmail(["amit.gupta@shop2020.in"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
|
| 1230 |
sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com", "manish.sharma@shop2020.in"], "", "All DTR Orders", XLS_O_FILENAME)
|
- |
|
| 1231 |
|
1282 |
|
| 1232 |
def main3():
|
1283 |
def main3():
|
| 1233 |
db = client.Dtr
|
1284 |
db = client.Dtr
|
| 1234 |
#main()
|
1285 |
#main()
|
| 1235 |
mailBodyTemplate="""
|
1286 |
mailBodyTemplate="""
|
| Line 1307... |
Line 1358... |
| 1307 |
for key,valmap in datemap.iteritems():
|
1358 |
for key,valmap in datemap.iteritems():
|
| 1308 |
valmap['_id'] = key
|
1359 |
valmap['_id'] = key
|
| 1309 |
db.flSaleSnapshot.save(valmap)
|
1360 |
db.flSaleSnapshot.save(valmap)
|
| 1310 |
|
1361 |
|
| 1311 |
if __name__ == '__main__':
|
1362 |
if __name__ == '__main__':
|
| - |
|
1363 |
# getdata.addToAllOrders(date.today()-timedelta(days=30))
|
| 1312 |
main2()
|
1364 |
# main2()
|
| 1313 |
main()
|
1365 |
main()
|