def count(startTime, endTime):
billOp = mc.get_col_op_prod("order", "waybillMain")
queryParam = {
"$and": [{
"service.serviceType": {
"$in": [
"distributionInstallation", "install",
"cityDistributionInstallation"
]
}
}, {
"shipper.clientCode": {
"$in": ["KH2017106444", "KH20170824585", "KH18122500000002"]
}
}, {
"lastStatus": {
'$in': ["signed"]
}
}, {
'billingTime': {
'$gt':
utils.dateTo8(
datetime.datetime.strptime(startTime,
"%Y-%m-%d %H:%M:%S")),
'$lt':
utils.dateTo8(
datetime.datetime.strptime(endTime, "%Y-%m-%d %H:%M:%S"))
}
}]
}
list = billOp.find(queryParam, no_cursor_timeout=True)
return list.count()
def export(startTime, endTime):
billOp_localhost = mc.get_col_op("order", "waybillMains")
list = billOp_localhost.find({
'opDate': {
'$gt':
utils.dateTo8(
datetime.datetime.strptime(startTime, "%Y-%m-%d %H:%M:%S")),
'$lt':
utils.dateTo8(
datetime.datetime.strptime(endTime, "%Y-%m-%d %H:%M:%S"))
}
})
dataList = []
i = 0
for x in list:
i = i + 1
print(i)
data = {}
data["一智通单号"] = x.get("waybillId")
data["服务类型"] = x.get("serviceType")
data["签收时间"] = x.get("opDate")
data["安维单号"] = x.get("customerId")
data["发货人"] = x.get("clientName")
data["发货商家"] = x.get("contacts")
data["单品安装单价"] = x.get("installFee")
data["单品安装件数"] = x.get("installPackages")
data["单品安装总价"] = x.get("totalInstallFeeNew")
data["开单总安装费"] = x.get("totalInstallFeeOld")
data["品名"] = x.get("standName")
data["商家编号"] = x.get("customerProductCode")
data["商家品名"] = x.get("customerProductName")
dataList.append(data)
headModle = [
"一智通单号", "服务类型", "签收时间", "安维单号", "发货人", "发货商家", "单品安装单价", "单品安装件数",
"单品安装总价", "开单总安装费", "品名", "商家编号", "商家品名"
]
df = pd.DataFrame(dataList)
df.to_excel("aa1.xlsx", index=False, columns=headModle)
def transport_sc_localhost(time):
billOp = mc.get_col_op_prod("order", "waybillMain")
billOp_localhost = mc.get_col_op("order", "waybillMain")
list = billOp.find({
'billingTime': {
'$gt':
utils.dateTo8(
datetime.datetime.strptime(time + " 00:00:00",
"%Y-%m-%d %H:%M:%S")),
'$lt':
utils.dateTo8(
datetime.datetime.strptime(time + " 23:59:59",
"%Y-%m-%d %H:%M:%S"))
}
})
i = 0
for one in list:
i = i + 1
print(i)
localOne = billOp_localhost.find_one({"_id": one.get("_id")})
if not localOne:
billOp_localhost.insert_one(one)
print(not localOne)
def delete(startTime,endTime):
billOp = mc.get_col_op_prod("order", "waybillMain")
billOp_localhost = mc.get_col_op("order", "waybillMains")
queryParam = {
"$and": [
{"service.serviceType": {"$in": ["distributionInstallation", "install", "cityDistributionInstallation"]}},
{"shipper.clientCode": {"$in": ["KH2017106444", "KH20170824585", "KH18122500000002"]}},
{"lastStatus": {'$in': ["signed"]}},
{'billingTime':
{'$gt': utils.dateTo8(datetime.datetime.strptime(startTime, "%Y-%m-%d %H:%M:%S")),
'$lt': utils.dateTo8(datetime.datetime.strptime(endTime, "%Y-%m-%d %H:%M:%S"))}},
{
"product.standGoodsId": ""
}
]
}
list = billOp.find(queryParam, no_cursor_timeout=True);
i = 0
for x in list:
i = i+1;
print(i)
billOp_localhost.delete_many({"waybillId":x.get("waybillId")})
def load(startTime, endTime):
billOp = mc.get_col_op_prod("order", "waybillMain")
orderOp = mc.get_col_op_prod("order", "orderMain")
col_op_waybillLogisticsLog = mc.get_col_op_prod("order",
"waybillLogisticsLog")
billOp_localhost = mc.get_col_op("order", "waybillMains")
queryParam = {
"$and": [{
"service.serviceType": {
"$in": [
"distributionInstallation", "install",
"cityDistributionInstallation"
]
}
}, {
"shipper.clientCode": {
"$in": ["KH2017106444", "KH20170824585", "KH18122500000002"]
}
}, {
"lastStatus": {
'$in': ["signed"]
}
}, {
'billingTime': {
'$gt':
utils.dateTo8(
datetime.datetime.strptime(startTime,
"%Y-%m-%d %H:%M:%S")),
'$lt':
utils.dateTo8(
datetime.datetime.strptime(endTime, "%Y-%m-%d %H:%M:%S"))
}
}, {
"product.standGoodsId": ""
}]
}
list = billOp.find(queryParam, no_cursor_timeout=True)
count = 0
for bill in list:
count = count + 1
print(count)
waybillId = bill.get("waybillId")
# 重复检验
findOne = billOp_localhost.find_one({"waybillId": waybillId})
if findOne is not None:
continue
totalInstallFeeOld = 0
serviceFee = bill.get("serviceFee")
for serviceF in serviceFee:
if serviceF.get("feeType") == "installFee":
totalInstallFeeOld = serviceF.get("amount")
addOneMain = {}
# 一智通单号
addOneMain["waybillId"] = waybillId
# 服务类型
addOneMain["serviceType"] = bill.get("service").get("serviceTypeName")
# 签收时间
logs = col_op_waybillLogisticsLog.find(
{
'waybillId': waybillId,
'nodeType': {
"$in": ['normal', 'abnormal']
}
}, {
'opDate': 1
}).sort([('opDate', -1)])
if not (logs.count() == 0):
log = logs[0]
addOneMain['opDate'] = log.get('opDate')
# 安维单号
addOneMain["customerId"] = bill.get("service").get("customerId")
# 发货人
addOneMain["clientName"] = bill.get("shipper").get("clientName")
# 发货商家
addOneMain["contacts"] = bill.get("shipper").get("contacts")
addMany = []
sproduct = []
orders = orderOp.find({"waybillId": waybillId})
if orders is not None:
for order in orders:
op = order["product"]
sproduct = sproduct + op
product = bill["product"]
forProduct = []
if len(sproduct) > len(product):
for i, sp in enumerate(sproduct):
appendOne = {}
appendOne["customerProductCode"] = sp.get("busGoodsId")
appendOne["customerProductName"] = sp.get("busName")
if i < len(product):
p = product[i]
appendOne["standGoodsId"] = p.get("standGoodsId")
appendOne["installPackages"] = p.get("installPackages")
appendOne["standName"] = p.get("standName")
appendOne["standGoodsId"] = p.get("standGoodsId")
appendOne["busName"] = p.get("busName")
else:
appendOne["standGoodsId"] = ""
appendOne["installPackages"] = ""
appendOne["standName"] = ""
appendOne["standGoodsId"] = ""
appendOne["busName"] = ""
forProduct.append(appendOne)
else:
for i, p in enumerate(product):
appendOne = {}
appendOne["standGoodsId"] = p.get("standGoodsId")
appendOne["installPackages"] = p.get("installPackages")
appendOne["standName"] = p.get("standName")
appendOne["standGoodsId"] = p.get("standGoodsId")
appendOne["busName"] = p.get("busName")
if i < len(sproduct):
sp = sproduct[i]
appendOne["customerProductCode"] = sp.get("busGoodsId")
appendOne["customerProductName"] = sp.get("busName")
else:
appendOne["customerProductCode"] = ""
appendOne["customerProductName"] = ""
forProduct.append(appendOne)
for p in forProduct:
addOne = copy.copy(addOneMain)
standGoodsId = p.get("standGoodsId")
customerProductName = p.get("customerProductName")
if standGoodsId != "":
# 单品安装单价
sf = getStandFee(standGoodsId)
installFee = sf.get("installFee") if sf else None
addOne["installFee"] = installFee
# 单品安装件数
installPackages = p.get("installPackages")
addOne["installPackages"] = installPackages
# 单品安装总价
if not installFee and not installPackages:
totalInstallFeeNew = installPackages * installFee
addOne["totalInstallFeeNew"] = totalInstallFeeNew
# 开单总安装费
addOne["totalInstallFeeOld"] = totalInstallFeeOld
# 品名
addOne["standName"] = p.get("standName")
else:
# 品名
addOne["standName"] = p.get("busName")
if customerProductName != "":
# 商家编号
addOne["customerProductCode"] = p.get("customerProductCode")
# 商家品名
addOne["customerProductName"] = p.get("customerProductName")
addMany.append(addOne)
if len(addMany) == 0:
print(bill.get("waybillId"))
break
billOp_localhost.insert_many(addMany)
def execute(startTime, endTime, fileName):
billOp = mc.get_col_op_prod("order", "waybillMain")
queryParam = {
"$and": [
# {"service.serviceType": {"$in": ["distributionInstallation", "install", "cityDistributionInstallation"]}},
# {"shipper.clientCode": {"$in": ["KH2017106444", "KH20170824585", "KH18122500000002"]}},
{
"lastStatus": {
'$ne': ["cancel"]
}
},
{
'billingTime': {
'$gt':
utils.dateTo8(
datetime.datetime.strptime(startTime,
"%Y-%m-%d %H:%M:%S")),
'$lt':
utils.dateTo8(
datetime.datetime.strptime(endTime,
"%Y-%m-%d %H:%M:%S"))
}
}
]
}
list = billOp.find(queryParam, no_cursor_timeout=True)
resultList = []
# resultListF = {}
for bill in list:
product = bill.get("product")
hasOn = []
for p in product:
goodsId = ""
name = ""
busGoodsId = p.get("busGoodsId")
if busGoodsId:
goodsId = busGoodsId
name = p.get("busName")
else:
goodsId = p.get("standGoodsId")
name = p.get("standName")
if goodsId and goodsId not in hasOn:
idxs = [
i for i, v in enumerate(resultList)
if v['goodsId'] == goodsId
]
installPackages = p.get("installPackages")
if isinstance(installPackages, Decimal128):
installPackages = float(installPackages.to_decimal())
if isinstance(installPackages, str):
installPackages = float(installPackages)
installFee = p.get("installFee")
if isinstance(installFee, Decimal128):
installFee = float(installFee.to_decimal())
if isinstance(installFee, str):
installFee = float(installFee)
print(type(installFee))
if idxs:
idx = idxs[0]
resultList[idx][
"ip"] = resultList[idx]["ip"] + installPackages
resultList[idx][
"ify"] = resultList[idx]["ify"] + installFee
else:
resultList.append({
"goodsId": goodsId,
"name": name,
"ip": installPackages,
"ify": installFee
})
else:
continue
hasOn.append(goodsId)
df = pd.DataFrame(resultList)
df.to_csv(fileName)
if not (logs.count() == 0):
log = logs[0]
data['opDate'] = log.get('opDate')
dataList.append(data)
book = xlwt.Workbook(encoding="utf-8", style_compression=0)
sheet = book.add_sheet("test", cell_overwrite_ok=True)
# d = bson.decimal128.Decimal128("0.5");
# print(d.to_decimal())
row = 1
for val in dataList:
sheet.write(row, 0, val.get('customerId'))
sheet.write(row, 1, val.get('waybillId'))
sheet.write(row, 2, val.get('orderNo'))
sheet.write(row, 3, val.get('stateName'))
dt = datetime.datetime.strftime(utils.dateTo8(val.get('billingTime')),
"%Y-%m-%d %H:%M:%S") if (utils.dateTo8(
val.get('billingTime'))) else ""
sheet.write(row, 4, dt)
sheet.write(row, 5, val.get('serviceTypeName'))
sheet.write(row, 6, val.get('deptName'))
sheet.write(row, 7, val.get('installPackages'))
pa = 0
if ((isinstance(val.get('packages'), float)
or isinstance(val.get('packages'), int))):
pa = val.get('packages')
else:
pa = val.get('packages').to_decimal() if (val.get('packages')) else 0
sheet.write(row, 8, pa)
va = 0
if (isinstance(val.get('volumes'), float)
and ew.waybill_id_ in (%s);
''', waybillMain['waybillId'])
inmysqlWaybill = cursor.fetchall()
print("len:-----%s", len(inmysqlWaybill))
if (len(inmysqlWaybill) > 0):
flag1 = 1
i += 1
print(i)
print(waybillMain['waybillId'])
data = {}
waybillId = waybillMain.get('waybillId')
data['waybillId'] = waybillId
data['deptName'] = waybillMain.get('company').get('deptName')
billingTime = waybillMain.get('billingTime')
dt = datetime.datetime.strftime(
utils.dateTo8(billingTime),
"%Y-%m-%d %H:%M:%S") if (utils.dateTo8(billingTime)) else ""
data['billingTime'] = dt
data['serviceTypeName'] = waybillMain.get('service').get(
'serviceTypeName') if (waybillMain.get('service')) else ""
data['packages'] = waybillMain.get('goods').get('packages')
data['volumes'] = waybillMain.get('goods').get('volumes')
data['endAreaName'] = waybillMain.get('receiver').get('endAreaName')
data['receiveAddress'] = waybillMain.get('receiver').get('receiveAddress')
data['clientName'] = waybillMain.get('shipper').get('clientName')
serviceFee = waybillMain.get('serviceFee')
for fee in serviceFee:
feeType = fee.get('feeType')
num = fee.get('amount')
flag = isinstance(num, int) or isinstance(num, float)
if (flag):
