def exportNC(time):
cpOp = mc.get_col_op("priceManage", "customerPrice")
list = cpOp.find({"recordTime": {"$in": timeToArrTimeNC(time)}})
dataList = []
for x in list:
data = {}
data["客户名称"] = x.get("name")
recordTime = x.get("recordTime")
data["日期"] = recordTime[:-2]
data["开单总金额"] = x.get("feeValue")
data["标准价格开单总金额"] = x.get("standFeeValue")
data["标准价格执行率"] = x.get("executeRate")
dataList.append(data)
headModle = ["客户名称", "日期", "开单总金额", "标准价格开单总金额", "标准价格执行率"]
df = pd.DataFrame(dataList)
# df["开单总金额"] = df["开单总金额"].astype(decimal)
# df["标准价格开单总金额"] = df["标准价格开单总金额"].astype(decimal)
# df["标准价格执行率"] = df["标准价格执行率"].astype(decimal)
df.to_excel(time + "标准客户价格执行率（不包含套餐）.xlsx", index=False, columns=headModle)
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")})
import config
import pandas as pd
import mongo_client
mysqlClient = config.get_mysql_client()
cursor = mysqlClient.cursor()
cursor.execute(
"SELECT ew.waybill_id_ from ewaybill_ ew, ewaybill_node_ ewn where ewn.waybill_id_ = ew.id_ and ewn.disabled_ = 0 and ewn.node_type_ = 'TMS_SIGN' and ewn.node_time_ >= '2019-01-01 00:00:00';"
)
col_op_waybillLogisticsLog = mongo_client.get_col_op("order",
"waybillLogisticsLog")
col_op_waybillMain = mongo_client.get_col_op("order", "waybillMain")
waybillIds = []
for x in cursor:
waybillIds.append(x[0])
print(len(waybillIds))
list = col_op_waybillMain.find({'waybillId': {
'$in': waybillIds
}}, {
"company": 1,
"goods": 1,
"receiver": 1,
"serviceFee": 1,
"product": 1,
'billingTime': 1,
'service.serviceTypeName': 1,
"shipper.clientName": 1,
'waybillId': 1
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)