def transform():
feeOp = mc.get_col_op_prod("priceManage", "fee")
list = feeOp.find({
"feeType": "transportFee",
"groupName": "协议",
"keys.startWebsites.websiteId": "00003085",
"name": {
"$ne": "喜梦宝协议运费"
},
"keys.customers": {
"$elemMatch": {
"customerId": {
"$ne": "KH20170824585"
}
}
}
})
# one = feeOp.find_one({"_id" : "5bc1988214776700073735d0"})
i = 0
for one in list:
i = i + 1
print(i)
newOne = one
_id = str(objectid.ObjectId())
print(_id)
newOne["_id"] = _id
newOne["keys"]["startWebsites"] = [{
"websiteId": "201710002",
"websiteName": "智通一号"
}]
feeOp.insert_one(newOne)
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 getStandFee(code):
feeOp = mc.get_col_op_prod("priceManage", "fee")
feeOne = feeOp.find_one({"feeType":"installFee","groupName":"标准","keys.products.productId":code})
if feeOne is None:
return None;
installFee = 0
minFee = 0;
vars = feeOne.get("vars")
for s in vars:
varName = s.get("varName")
if varName == "$安装单价":
installFee = s.get("varValue")
if varName == "$最低一票":
minFee = s.get("varValue")
return {"installFee":installFee,"minFee":minFee}
def importStandComboDeliverFee():
fee_op = mongo_client.get_col_op_test("priceManage","purchaseFee");
area_op = mongo_client.get_col_op_prod("baseConfig", "area")
df = pd.read_excel("C:/Users/zhu/Desktop/导入导出/采购价/送货费-标准套餐-模板.xlsx")
dataList = np.array(df).tolist()
addList = []
errorList = []
for data in dataList:
print(data)
one = {}
one["_id"] = str(objectid.ObjectId())
one["groupId"] = "5ce3c643eeeb414fbc908323"
one["groupName"] = "标准套餐"
one["priority"] = 2
one["feeType"] = "basicDeliveryFee"
one["name"] = data[0]
one["template"] = "费用 = $基础送货费;返回 费用；"
area = area_op.find_one({"mergerName":data[3]+data[4]+data[5]})
if area is None:
errorList.append(data);
continue
keys = {
"combos": [{"comboId": data[6], "comboName": data[7]}],
"destinations":[{"destinationId":area.get("code"),"destinationName":area.get("mergerName")}]}
one["keys"] = keys
one["vars"] = [{"varName":"$基础送货费","varValue":data[8]}]
one["createTime"] = Timestamp(1559098387, 253)
one["lastUpdateTime"] = Timestamp(1559785785, 272)
one["version"] = 1
one["creator"] = "15000000000"
one["creatorName"] = "测试九江管理员"
one["modifier"] = "15000000000"
one["modifierName"] = "测试九江管理员"
addList.append(one)
fee_op.insert_many(addList)
el = pd.DataFrame(errorList);
el.to_excel("error_scd.xlsx", index=False)
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)
