def saveMainteData(table, route, date):
# 获取每张表格一保二保数目
frist = 0
second = 0
for i in range(0, table.nrows):
for j in range(0, table.ncols):
string = str(table.cell(i, j).value)
if st.contains(string, "一保"):
result = re.findall(r"\d+\.?\d*", string)
frist = result[0] if result != [] else 0
if st.contains(string, "二保"):
result = re.findall(r"\d+\.?\d*", string)
second = result[0] if result != [] else 0
try:
rmc = RouteMonthlyDetail.objects.get(route=route, date=date)
rmc.num_fir_maintain += getIntValue(frist)
rmc.num_sec_maintain += getIntValue(second)
rmc.save()
except RouteMonthlyDetail.DoesNotExist:
nrmc = RouteMonthlyDetail(route=route,
date=date,
num_fir_maintain=frist,
num_sec_maintain=second)
nrmc.save()
return
def getRoute(tablename):
if st.contains(tablename, "专线"):
return "海峡专线"
if st.contains(tablename, "夜班"):
return "夜班一号线"
if st.contains(tablename.lower(), "k2"):
return "k2"
route = re.findall(r"\d+\.?\d*", tablename)
return route[0]
def load_xml_data(table, filepath):
# 获取当前表格的行数
nrows = table.nrows
# 获取当前表格的列数
ncols = table.ncols
item = {}
item["team"] = str.split(filepath, '\\')[-2]
item["route"] = os.path.split(filepath)[1][0:-6].replace("路", "")
totalCharge = 0
index = xt.getStartIndex(table, "交易时间")
for i in range(index, nrows):
if table.cell(i, 0).value is "":
continue
if contains(table.cell(i, 0).value, "合计"):
continue
charge_ = table.cell(i, 6).value
ocd = OilChargeDetail(car_id=str(table.cell(i, 3).value),
route=item["route"],
paytime=str(table.cell(i, 0).value),
charge=charge_,
printnum=str(table.cell(i, 2).value),
station=str(table.cell(i, 5).value),
team=item["team"])
ocd.save()
totalCharge += float(charge_)
item["charge"] = totalCharge
return item
def load_xml_data(table, routename):
# 获取当前表格的行数
nrows = table.nrows
# 获取当前表格的列数
ncols = table.ncols
list = []
index = xt.getStartIndex(table, "交易时间")
for i in range(index, nrows):
# carId = str(table.cell(i, 1).value)
if table.cell(i, 0).value is "":
continue
if contains(table.cell(i, 0).value, "合计"):
continue
paytime = table.cell(i, 0).value
carNum = table.cell(i, 1).value
printnum = table.cell(i, 2).value
carId = "闽AY" + str(table.cell(i, 3).value)[0:4]
oiltpye = table.cell(i, 4).value
station = table.cell(i, 5).value
charge = table.cell(i, 6).value
item = {}
item["route"] = routename
item["paytime"] = paytime
item["cardnum"] = carNum
item["printnum"] = printnum
item["carid"] = carId
item["oiltype"] = oiltpye
item["oilstation"] = station
item["charge"] = charge
list.append(item)
return list
def load_data(table,item):
nrows = table.nrows
list = []
indexR=item[0]
carNoC=item[1]
for i in range(indexR, nrows):
# carId = str(table.cell(i, 1).value)
#print(table.cell(i, carNoC).value)
if table.cell(i, carNoC).value is "":
continue
if st.contains(str(table.cell(i,carNoC).value),"小计"):
continue
item=[]
item.append(str(table.cell(i, carNoC).value)[0:4])#车辆id
item.append(table.cell(i,carNoC+1).value)#车公里
item.append(table.cell(i,carNoC+2).value)#指标总量
item.append(table.cell(i, carNoC + 3).value) # 百公里指标target
item.append(table.cell(i, carNoC + 4).value) # 车辆油耗
#item.append(table.cell(i, carNoC + 5).value) # 实际百公里
item.append(table.cell(i, carNoC + 6).value) # 实际节约
item.append(table.cell(i, carNoC + 7).value) # 实际超耗
item.append(table.cell(i, carNoC + 8).value) # 二保
item.append(table.cell(i, carNoC + 9).value) # 跟车
list.append(item)
return list
def load_detail_data(table, item):
nrows = table.nrows
list = []
indexR = item[0]
carNoC = item[1]
route = getRoute(table.name)
for i in range(indexR, nrows):
if table.cell(i, carNoC).value is "":
continue
if st.contains(str(table.cell(i, carNoC).value), "合计"):
continue
item = []
car_id = getCar_id(table.cell(i, carNoC).value, table.name)
item.append(car_id) # 车辆id
item.append(route) # 车辆线路
item.append(table.cell(i, carNoC + 4).value) # 停驶天数
item.append(table.cell(i, carNoC + 5).value) # 工作天数
item.append(table.cell(i, carNoC + 7).value) # 营业公里
item.append(table.cell(i, carNoC + 8).value) # 包车公里
item.append(table.cell(i, carNoC + 9).value) # 公用公里
item.append(table.cell(i, carNoC + 10).value) # 调车公里
item.append(table.cell(i, carNoC + 14).value) # 故障次数
item.append(table.cell(i, carNoC + 15).value) # 故障分钟
list.append(item)
return list
def getCar_id(id, tablename):
s = str(id)
if (st.contains(str(id), "路")):
s = str(id).split("路")[1].strip()
if (st.contains(str(id), "线")):
s = str(id).split("线")[1].strip()
if st.contains(s, ".") or len(s) == 3:
s = s.split(".")[0]
if len(s)==4:
return s
route = getRoute(tablename)
if route == "1" or route == "17" or route == "28" or route == "161":
s = "A" + s
elif route == "501":
s = "B" + s
return s
def load_sum_data(table, item):
print(table.name)
nrows = table.nrows
list = []
indexR=item[0]
carNoC=item[1]
route=fb.getRoute(table.name)
for i in range(indexR, nrows):
if table.cell(i, carNoC).value is "":
continue
if st.contains(str(table.cell(i,carNoC).value),"小计"):
continue
item=[]
car_id=fb.getCar_id(table.cell(i, carNoC).value,table.name)
item.append(car_id)#车辆id
item.append(route)#车辆线路
item.append(table.cell(i,carNoC+1).value)#车公里
item.append(table.cell(i,carNoC+2).value)#指标总量
item.append(table.cell(i, carNoC + 3).value) # 百公里指标target
item.append(table.cell(i, carNoC + 4).value) # 车辆油耗
item.append(table.cell(i, carNoC + 6).value) # 实际节约
item.append(table.cell(i, carNoC + 7).value) # 实际超耗
item.append(table.cell(i, carNoC + 8).value) # 二保
item.append(table.cell(i, carNoC + 9).value) # 跟车
list.append(item)
return list
def getStartIndex(table, targetstr):
rowindex = None
nrows = table.nrows
ncols = table.ncols
for i in range(0, nrows):
for j in range(0, ncols):
if st.contains(str(table.cell(i, j).value), targetstr):
rowindex = i + 1
break
return rowindex
def scanfiles():
list=[]
for i in range(0,len(mFilename)):
data=xlrd.open_workbook(mFilename[i])
for sheet in data.sheets():
if st.contains(sheet.name,"统计"):
table = data.sheet_by_name(sheet.name)
item=getStartIndex(table)
list.extend(load_data(table,item))
return list
def scanfiles():
list = []
for i in range(0, len(mFilename)):
data = xlrd.open_workbook(mFilename[i])
for sheet in data.sheets():
# if st.contains(sheet.name,"统计"):
# table = data.sheet_by_name(sheet.name)
# item=getStartIndex(table)
# list1.extend(load_sum_data(table, item))
if st.contains(sheet.name, "汇总"):
table = data.sheet_by_name(sheet.name)
item = getStartIndex(table)
if item is None:
continue
list.extend(logic.feedback.carDetail.load_detail_data(table, item))
return list
def searchforFile(path):
items = []
dirs = os.listdir(path)
for file in dirs:
filepath = path + "\\" + str(file)
if os.path.isdir(filepath):
items += searchforFile(filepath)
if os.path.isfile(filepath):
if os.path.splitext(filepath)[1] == ".xls" and contains(
filepath, "明细"):
print(filepath)
data = xlrd.open_workbook(filepath)
table = data.sheet_by_name("1")
item = load_xml_data(table, os.path.split(filepath)[1][0:-6])
items.append(item)
return items
def load_feedback_sum_data(table, item, date):
nrows = table.nrows
indexR = item[0]
carNoC = item[1]
route = getRoute(table.name)
for i in range(indexR, nrows):
if table.cell(i, carNoC).value is "" or 0:
continue
if st.contains(str(table.cell(i, carNoC).value), "计"):
continue
car_id = getCar_id(table.cell(i, carNoC).value, table.name)
bi_ = bil.search_for_sub_car_id(car_id, route)
if bi_ is None:
print(" no result !!!! car_id:", car_id, "route:", route, "date:",
date)
mileage = getFloatValue(table.cell(i, carNoC + 1).value) # 车公里
team_target = getFloatValue(table.cell(i, carNoC + 3).value) # 车队上报指标
elec, oilwear = 0, 0
if bi_.cartype.power_type == "电":
elec = getFloatValue(table.cell(i, carNoC + 4).value)
else:
oilwear = getFloatValue(table.cell(i, carNoC + 4).value) # 车辆油耗
maintain = getFloatValue(table.cell(i, carNoC + 8).value) # 二保
follow = getFloatValue(table.cell(i, carNoC + 9).value) # 跟车
inspection = 0
if table.ncols > carNoC + 10:
try:
inspection = getFloatValue(table.cell(i, carNoC + 10).value)
except:
traceback.print_exc()
inspection = 0
mf = MonthlyFeedback(fb_car_id=car_id,
date=date,
route=route,
carInfo=bi_ if bi_ != None else None,
mileage=mileage,
oilwear=oilwear,
maintain=maintain,
follow=follow,
team_target=team_target,
inspection=inspection,
electric_cost=elec)
mf.save()
def getRoute(tablename):
if contains(tablename, "专线"):
return "海峡专线"
if contains(tablename, "夜间"):
return "夜班一号线"
if contains(tablename, "夜班"):
return "夜班一号线"
if contains(tablename.lower(), "k2"):
return "k2"
if contains(tablename.lower(), "21支"):
return "142"
if contains(tablename.lower(), "30支"):
return "149"
if contains(tablename.lower(), "57路区间"):
return "57区间"
route = re.findall(r"\d+\.?\d*", tablename)
#print(route)
return route[0]
def load_xml_data(table, routename):
# 获取当前表格的行数
nrows = table.nrows
# 获取当前表格的列数
ncols = table.ncols
item = {}
item["route"] = routename.replace("路", "")
totalCharge = 0
index = xt.getStartIndex(table, "交易时间")
for i in range(index, nrows):
# carId = str(table.cell(i, 1).value)
if table.cell(i, 0).value is "":
continue
if contains(table.cell(i, 0).value, "合计"):
continue
charge = table.cell(i, 6).value
totalCharge += float(charge)
item["charge"] = totalCharge
return item
def load_feedb_detail_data(table, item, date):
nrows = table.nrows
indexR = item[0]
carNoC = item[1]
route = getRoute(table.name)
saveMainteData(table, route, date)
for i in range(indexR, nrows):
if table.cell(i, carNoC).value is "":
continue
if table.cell(i, carNoC).value is "0":
continue
if st.contains(str(table.cell(i, carNoC).value), "计"):
continue
if st.contains(str(table.cell(i, carNoC).value), "一保"):
continue
if st.contains(str(table.cell(i, carNoC).value), "备注"):
continue
car_id = getCar_id(table.cell(i, carNoC).value, table.name)
try:
mf = MonthlyFeedback.objects.get(fb_car_id=car_id,
route=route,
date=date)
except:
traceback.print_exc()
print("--------------------------car_id:", car_id, "route:", route,
date)
continue
#
if mf is not None:
try:
mf.work_days = getFloatValue(table.cell(i, carNoC +
5).value) # 工作天数
mf.fix_days = getFloatValue(table.cell(i, carNoC +
2).value) # 修理天数
mf.stop_days = getFloatValue(table.cell(i, carNoC +
4).value) # 停驶天数
mf.shunt_mileage = getFloatValue(
table.cell(i, carNoC + 10).value) # 调车公里
mf.engage_mileage = getFloatValue(
table.cell(i, carNoC + 8).value) # 包车公里
mf.public_mileage = getFloatValue(
table.cell(i, carNoC + 9).value) # 公用公里
mf.fault_times = getFloatValue(
table.cell(i, carNoC + 14).value) # 故障次数
mf.fault_minutes = getFloatValue(
table.cell(i, carNoC + 15).value) # 故障分钟
mf.target_in_compute = get_one_car_target(
route, date, mf.carInfo.cartype)
if date.month > 5 and date.month < 10:
mf.target_in_compute_2 = mf.carInfo.cartype.target_value4
else:
mf.target_in_compute_2 = mf.carInfo.cartype.target_value3
mf.save()
except:
traceback.print_exc()
print("--------------------------car_id", car_id, "route:",
route)
continue
#判断是否是文件
def checkfileexist(filename):
return os.path.isfile(filename)
def checkPathexist(dirpath):
return os.path.isdir(dirpath)
# 读取文件内容并打印
def readFile(filename):
fopen = open(filename, 'r') # r 代表read
for eachLine in fopen:
print
"读取到得内容如下：", eachLine
fopen.close()
if __name__ == '__main__':
filePath = "G:\\一公司车辆行驶证600部公务11部"
picitems=searchForFile(path=filePath,splix=".jpg")
pyd=PyDbHelper.GetInstance()
items=pyd.get_items("select car_id from bus_info")
for picitem in picitems:
for item in items:
car_id=item["car_id"][3:7]
path=os.path.split(picitem)[0]
print("car_id"+str(car_id))
print("path"+str(path))
if st.contains(str(path),str(car_id)):
print("car_id" + str(car_id))
print("path" + str(path))
newfilename=os.path.split(picitem)[1]
newfilepath="f:\\媒体材料\\行驶证\\"+car_id+"\\"+newfilename
copyFile(picitem,newfilepath)
