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)