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):