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