def getHistorySitesByImei(imei, fromTime, toTime): sites = {"manual": [], "auto": []} _run_sites = icfg.db.query("""SELECT *,Sites.id AS site_id FROM Sites,SiteSeatStatus WHERE SiteSeatStatus.imei ='{0}' AND SiteSeatStatus.site_id = Sites.id AND (SiteSeatStatus.from_time>='{1}' AND SiteSeatStatus.to_time<='{2}')""" .format(imei, fromTime, toTime)) for site in _run_sites: site = uTools.dbItem2Dict(site, format="string") site["seated_num"] = site["to_seated_num"] sites["auto"].append(site) busline = getBuslineByImei(imei) _manual_sites = icfg.db.query( """SELECT *,Sites.id AS site_id FROM LineSites,Sites WHERE LineSites.busline_id ={0} AND LineSites.site_id = Sites.id AND Sites.setting_type='manual' ORDER BY seq""".format( busline["id"])) for site in _manual_sites: site = uTools.dbItem2Dict(site, format="string") sites["manual"].append(site) return sites
def getCompany(company_id, format="orign"): _ret = icfg.db.query( "SELECT * FROM Company WHERE id={0}".format(company_id)) if len(_ret) == 0: return None company = uTools.dbItem2Dict(_ret[0], format) return company
def getBuslineByid(busline_id, format="orign"): _ret = icfg.db.query( "SELECT * FROM BusLine WHERE id={0}".format(busline_id)) if len(_ret) == 0: return None busline = uTools.dbItem2Dict(_ret[0], format) return busline
def getEmployeeByid(company_id, with_manager=False): rows = [] openids = [] _employee = icfg.db.query( """SELECT * FROM CompanyHasEmployee WHERE company_id={0} """.format(company_id)) receivers = [employee for employee in _employee] if with_manager == True: _mgrs = icfg.db.query( """SELECT * FROM Manager WHERE privilege<>'invisible' """.format(company_id)) for mgr in _mgrs: receivers.append(mgr) for employee in receivers: if employee.openid in openids: continue openids.append(employee.openid) _ret = icfg.db.query("""SELECT * FROM Customer WHERE openid ='{0}' """.format(employee.openid)) if len(_ret) > 0: nickname = _ret[0].nickname rows.append(uTools.dbItem2Dict(employee, format="string")) rows[-1]["nickname"] = nickname if rows[-1]["remark"] == "": rows[-1]["remark"] = nickname return rows
def getSeatStatusDithering(bustravel_id): _ret = icfg.db.query( "SELECT * FROM BusTravel WHERE id={0}".format(bustravel_id)) bustravel = _ret[0] #按座位号进行排序 seat2sensor = devdb.seat2sensor(bustravel.imei) rows = [] merges = [] no = 0 for seat_name in sorted(seat2sensor.keys()): _seatStatus = icfg.db.query("""SELECT * FROM SeatStatusDithering WHERE bustravel_id={0} AND seat_state='seated' AND sensor_no ={1} ORDER BY from_time""".format( bustravel_id, seat2sensor[seat_name])) if len(_seatStatus) == 0: rows.append({"seat_name": seat_name}) no += 1 continue if len(_seatStatus) > 1: merges.append({"index": no, "rowspan": len(_seatStatus)}) for seatstatus in _seatStatus: no += 1 rows.append(uTools.dbItem2Dict(seatstatus, format="string")) for _dir in ["from", "to"]: _ret = icfg.db.query("""SELECT * FROM TravelSites,Sites WHERE TravelSites.id={0} AND TravelSites.manual_site_id = Sites.id """.format(seatstatus[_dir + "_travel_site_id"])) rows[-1][_dir + "_name"] = _ret[0].name rows[-1]["seat_name"] = seat_name rows[-1]["seat_state_ch"] = seatState[rows[-1]["seat_state"]] seats = {"total": no, "rows": rows, "merges": merges} return seats
def getMonitorRegionResultByImei(imei, from_time, to_time): rows = [] _ret = icfg.db.query("""SELECT * FROM MonitorResult WHERE imei='{0}' AND from_time >='{1}' AND to_time<='{2}' """.format(imei, from_time.strftime("%Y-%m-%d %H:%M:%S"), to_time.strftime("%Y-%m-%d %H:%M:%S"))) region = {} for item in _ret: if region.has_key(item.monitor_region_id) == False: region[long(item.monitor_region_id)] = getMonitorRegionInfoById( item.monitor_region_id) rows.append(uTools.dbItem2Dict(item, format="string")) for row in rows: row["from_name"] = region[row["monitor_region_id"]]["from_name"] row["to_name"] = region[row["monitor_region_id"]]["to_name"] row["times_percent"] = "{0}%".format( region[row["monitor_region_id"]]["times_percent"]) row["mileage_percent"] = "{0}%".format( region[row["monitor_region_id"]]["mileage_percent"]) del row["seatStatus"] ret = {"region": region, "rows": rows} return ret
def getCompanyEmployeeByOpenid(openid, company_id, format="orign"): _ret = icfg.db.query( "SELECT * FROM CompanyHasEmployee WHERE openid='{0}' AND company_id={1}" .format(openid, company_id)) employee = None if len(_ret) > 0: employee = uTools.dbItem2Dict(_ret[0], format) return employee
def getCompanyByBusline(busline_id): _ret = icfg.db.query( "SELECT * FROM BusLine WHERE id={0}".format(busline_id)) if len(_ret) == 0: return None _ret = icfg.db.query("SELECT * FROM Company WHERE id={0}".format( _ret[0].company_id)) company = uTools.dbItem2Dict(_ret[0]) return company
def getBusesByImei4pc(imei): grps = [] _grps = icfg.db.query("""SELECT * FROM CurrentLocation WHERE imei = '{0}' """.format(imei)) for grp in _grps: grps.append(uTools.dbItem2Dict(grp, format="string")) buses = {"total": len(grps), "rows": grps} return buses
def getBuslineByImei(imei, format="orign"): _ret = icfg.db.query( "SELECT * FROM GroupHasDevice WHERE imei='{0}'".format(imei)) if len(_ret) == 0: return None _ret = icfg.db.query("SELECT * FROM BusLine WHERE busgroupid={0}".format( _ret[0].devicegroup_id)) busline = (None if len(_ret) == 0 else uTools.dbItem2Dict(_ret[0], format)) return busline
def getHistorySite(self,imei,startTime,endTime): _sites = db.query("""SELECT * FROM SiteSeatStatus,Sites WHERE imei='{0}' AND from_time>='{1}' AND from_time<='{2}' AND SiteSeatStatus.manual_site_id = Sites.id ORDER BY from_time """.format(imei,startTime,endTime)) sites = [] for site in _sites: sites.append(uTools.dbItem2Dict(site,format="string")) return sites
def statSites(_sites): sites = {"manual": [], "auto": []} stat_sites = [] #防止重复统计 for _site in _sites: if _site.site_id in stat_sites: continue setting_type = ("auto" if _site.setting_type is None else _site.setting_type) site = uTools.dbItem2Dict(_site, format="string") sites[setting_type].append(site) return sites
def getCompanyByImei(imei): _ret = icfg.db.query( "SELECT * FROM GroupHasDevice WHERE imei='{0}'".format(imei)) if len(_ret) == 0: return None _ret = icfg.db.query("SELECT * FROM BusLine WHERE busgroupid={0}".format( _ret[0].devicegroup_id)) if len(_ret) == 0: return None _ret = icfg.db.query("SELECT * FROM Company WHERE id={0}".format( _ret[0].company_id)) company = uTools.dbItem2Dict(_ret[0]) return company
def getBusesByBusline4pc(busline_id): grps = [] _ret = icfg.db.query( "SELECT * FROM BusLine WHERE id={0}".format(busline_id)) busline = _ret[0] _grps = icfg.db.query("""SELECT * FROM CurrentLocation,GroupHasDevice WHERE GroupHasDevice.devicegroup_id={0} AND CurrentLocation.imei = GroupHasDevice.imei""". format(busline.busgroupid)) for grp in _grps: grps.append(uTools.dbItem2Dict(grp, format="string")) buses = {"total": len(grps), "rows": grps} return buses
def getEmployeeByid4pc(company_id, with_manager=False): rows = [] openids = [] rows = {"saiwei": [], "customer": []} _employee = icfg.db.query( """SELECT * FROM CompanyHasEmployee WHERE company_id={0} """.format(company_id)) for employee in _employee: openids.append(employee.openid) _employee = uTools.dbItem2Dict(employee, format="string") rows["customer"].append(formatEmployeeInfo(_employee)) if with_manager == True: _mgrs = icfg.db.query( """SELECT * FROM Manager WHERE privilege<>'invisible' """.format(company_id)) for employee in _mgrs: if employee.openid in openids: continue openids.append(employee.openid) _employee = uTools.dbItem2Dict(employee, format="string") rows["saiwei"].append(formatEmployeeInfo(_employee)) return rows
def getHistoryTrackMsg(self, openid, data, mapType="QQ"): imei = data.imei fromTime = datetime.datetime.strptime(data.startTime, "%Y-%m-%d %H:%M:%S") toTime = datetime.datetime.strptime(data.endTime, "%Y-%m-%d %H:%M:%S") _pts = historyTrackMgr.getHistoryTrack(imei, fromTime, toTime) pts = [] start_gmileage = -1 for pt in _pts: if start_gmileage == -1: start_gmileage = pt.gmileage pts.append(uTools.dbItem2Dict(pt, format="string")) pts[-1]["dist"] = float("%0.2f" % (pt["gmileage"] - start_gmileage)) dev = devdb.getDevice(imei) sites = linedb.getHistorySitesByImei(imei, fromTime, toTime) cfgPara = { 'dev': dev, 'pts': pts, 'sites': sites, 'openid': openid, 'startTime': fromTime.strftime("%m/%d %H:%M"), 'endTime': toTime.strftime("%m/%d %H:%M"), 'url': "/m/bustrack?act=HISTORY-TRACK&strict=y&openid={0}&startTime={1}&endTime={1}&imei=" .format(openid, data.startTime, data.endTime), } if web.ctx.method == 'GET': if mapType == "QQ": ret = self.render.historytrackqq( json.dumps(cfgPara, ensure_ascii=False)) elif mapType == "BAIDU": #print cfgPara ret = self.render.historytrackbaidu( json.dumps(cfgPara, ensure_ascii=False)) else: ret = self._formatPostMsg(cfgPara) return ret
def getBusTravelByTimeSpan(imei, from_time, to_time): _travels = icfg.db.query("""SELECT * FROM BusTravel WHERE imei='{0}' AND from_time>='{1}' AND from_time<='{2}' """.format(imei, from_time, to_time)) travels = [] for travel in _travels: travel["duration_time"] = uTools.secondsFormat2( (travel.to_time - travel.from_time).total_seconds()) travels.append(uTools.dbItem2Dict(travel, format="string")) for _dir in ["from", "to"]: _ret = icfg.db.query("""SELECT * FROM SiteSeatStatus,Sites WHERE SiteSeatStatus.id={0} AND SiteSeatStatus.manual_site_id = Sites.id """.format(travel[_dir + "_site_id"])) travels[-1][_dir + "_name"] = _ret[0].name return travels
def getLineStatTaskByOpenid(openid, from_time): _tasks = icfg.db.query("""SELECT * FROM BackgroudTaskSequence WHERE owner='{0}' AND (create_at>'{1}' OR state<>'complete') AND name ='line-stat' ORDER BY create_at DESC """.format(openid, from_time)) tasks = [] for _task in _tasks: param = json.loads(_task.param) _dev = icfg.db.query("SELECT * FROM Device WHERE imei ='{0}'".format( param["imei"])) dev = _dev[0] task = uTools.dbItem2Dict(_task, format="short_datetime") task["bus_no"] = dev.name task["imei"] = dev.imei task.pop("param") tasks.append(task) return tasks
def getTravelSite(bustravel_id): rows = [] _sites = icfg.db.query( "SELECT * FROM TravelSites WHERE id={0} ORDER BY from_time".format( bustravel_id)) for site in _sites: _ret = icfg.db.query("""SELECT * FROM Sites WHERE id ={0}""".format( site.manual_site_id)) manual_site = _ret[0] rows.append(uTools.dbItem2Dict(site, format="string")) rows[-1]["site_name"] = manual_site.name rows[-1]["address"] = manual_site.address _ret = icfg.db.query( """SELECT * FROM SiteSeatStatus WHERE id ={0}""".format( site.site_seatstatus_id)) seatstatu_site = _ret[0] rows[-1]["duration_time"] = uTools.secondsFormat2( (site.to_time - site.from_time).total_seconds()) rows[-1]["from_seated_num"] = seatstatu_site.from_seated_num rows[-1]["to_seated_num"] = seatstatu_site.to_seated_num rows[-1]["num_change"] = seatstatu_site.num_change sites = {"total": len(rows), "rows": rows} return sites
def getMonitorRegionById(monitor_region_id, format="origion"): _ret = icfg.db.query( "SELECT * FROM MonitorRegion WHERE id={0}".format(monitor_region_id)) ret = uTools.dbItem2Dict(_ret[0], format) return ret
def getFanStateByArea(imeis): _imeis = json.dumps(imeis) _fans = icfg.db.query("SELECT * FROM FanState WHERE imei in ({0})".format( _imeis[1:-1])) fans = [uTools.dbItem2Dict(fan, format="string") for fan in _fans] return fans
def getCompanyRegion(country=None, province=None, city=None, district=None): where = [] group_region = "province" if country != None: where.append("country='{0}'".format(country)) group_region = "province" if province != None: where.append("province='{0}'".format(province)) group_region = "city" if city != None: where.append("city='{0}'".format(city)) group_region = "district" if district != None: where.append("district='{0}'".format(district)) group_region = None #查询下级信息 group_cond = ("" if group_region == None else "GROUP BY {0}".format(group_region)) where_sql = ("" if where == [] else "WHERE " + " AND ".join(where)) regions = [] if group_region != None: _ret = icfg.db.query("""SELECT {0},COUNT(id) AS sum FROM Company {1} {2} """.format(group_region, where_sql, group_cond)) for item in _ret: regions.append({ "id": -1, "text": item[group_region], "iconCls": "icon-tip", "state": "close", "region": group_region, "name": item[group_region], "sum": item["sum"] }) #查询企业信息 companys = icfg.db.query("""SELECT * FROM Company {0}""".format(where_sql)) items = [] locations = [] no = 0 for comp in companys: items.append(uTools.dbItem2Dict(comp, format="string")) _buslines = icfg.db.query( "SELECT id FROM BusLine WHERE company_id={0}".format(comp.id)) items[-1]["line_sum"] = len(_buslines) for busline in _buslines: _lineSites = icfg.db.query("""SELECT * FROM Sites WHERE id IN ( SELECT site_id FROM LineSites WHERE busline_id={0} AND is_end='yes' AND seq = 1) """.format(busline.id)) for site in _lineSites: locations.append(uTools.dbItem2Dict(site, format="string")) locations[-1]["company"] = items[-1] _ret = icfg.db.query("""SELECT COUNT(imei) AS sum FROM GroupHasDevice WHERE devicegroup_id IN (SELECT busgroupid FROM BusLine WHERE company_id={0}) """.format(comp.id)) items[-1]["bus_sum"] = _ret[0].sum no += 1 #查询公司所在位置信息 company = {"total": len(items), "rows": items} ret = {"regions": regions, "company": company, "locations": locations} return ret