def __get_journey__(self, kid_id, bus_id):
cur = conn.execute(
'select journey.id,journey.gps,journey.last_update from journey inner join driver on '
'journey.id=driver.active_ride_j_id '
' where driver.b_id=? and j_type=0 ', [bus_id])
res = cur.fetchone()
if res is None:
# no journey exist
return None
else:
cur = conn.execute(
'select id from attendance where k_id=? and j_id=? ',
[kid_id, res[0]])
res_2 = cur.fetchone()
# if exist NO record, it means kid is still waiting for bus
if res_2 is None:
return {
"id": res[0],
"current_gps": res[1],
"last_update": res[2],
"bus_id": bus_id,
"kid_id": kid_id
}
else:
# kids journey is already started
return None
def get_kid_ids(journey_id):
cur = conn.execute(
'select kid.id from kid where kid.id not in( \
select kid.id from attendance inner join kid on attendance.k_id=kid.id \
where j_id=?) and kid.b_id=(select journey.b_id from journey where journey.id=?)',
[journey_id, journey_id])
return Kid.id_to_list(cur.fetchall())
def __sql__(self, kid_id):
cur = conn.execute(
'select journey.id,journey.j_type ,journey.gps,attendance.pick_time,\
attendance.pick_gps from attendance inner join journey on attendance.j_id=journey.id \
where date(attendance.pick_time)=date(?) and attendance.k_id=? \
and attendance.drop_present=0 limit 1 ', [self.date, kid_id])
return cur.fetchone()
def is_delta_time_passed(journey_id, prev_time):
# if location have has no record
cur = conn.execute(' select max(id) from location where j_id=?',
[journey_id])
res = cur.fetchone()
if res[0] is None:
return True # since first record
else:
cur = conn.execute(
'select id from location where id=? and time(time)<time(?)',
[res[0], prev_time])
res = cur.fetchone()
if res is None:
return False
else:
return True
def get_latest_location(self):
cur = conn.execute(
'select journey.gps,journey.last_update from journey where journey.id= ( '
'select driver.active_ride_j_id from driver inner join kid on kid.b_id=driver.b_id '
'inner join parent on parent.id=kid.p_id where parent.token=? and kid.id=? ) and '
'journey.end=0', [self.token, self.kid_id])
return cur.fetchone()
def add(self):
cur = conn.execute(
'insert into feedback (name,email,title,message,date,p_id,d_id) values (?,?,?,?,?,?,?)',
(self.name, self.email, self.title, self.message, self.date,
self.parent_id, self.driver_id))
conn.commit()
return cur.lastrowid
def add(self):
cur = conn.execute(
'insert into driver (name,contact,password,b_id,userid) values (?,?,?,?,?)',
[self.name, self.contact, self.password, self.b_id, self.userid])
print([self.name, self.contact, self.password, self.b_id, self.userid])
conn.commit()
return cur.lastrowid
def get_active_ride(token):
cur = conn.execute('select active_ride_j_id from driver where token=?',
[token])
j_id = cur.fetchone()
if j_id is None:
return None
else:
return j_id[0]
def add(self):
try:
cur = conn.execute(
'insert into kid (name,section,photo,p_id,b_id) values(?,?,?,?,?)',
(self.name, self.section, self.photo, self.p_id, self.b_id))
conn.commit()
except:
return None
return cur.lastrowid
def is_kidOf(kid_id, token):
cur = conn.execute(
'select kid.id from kid inner join parent on kid.p_id=parent.id '
'where kid.id=? and parent.token=? ', [kid_id, token])
res = cur.fetchone()
if res is None:
return False
else:
return True
def get_user(userid): # check password
cur = conn.execute(
'select driver.name,driver.password,bus.b_no,driver.active_ride_j_id, bus.id from driver inner join Bus '
'on driver.b_id=bus.id where driver.userid=? ', [userid])
user = cur.fetchone()
if user is None:
return False
else:
return user # {1:"pass","3":"active ride","4":"bus_id"}
def get_image_for_parent(token, kid_id):
cur = conn.execute(
'select kid.photo from kid inner join parent on kid.p_id=parent.id \
where parent.token=? and kid.id=? ', [token, kid_id])
r = cur.fetchone()
if r is None:
return r
else:
return r[0]
def get_image_for_driver(token, kid_id):
cur = conn.execute(
'select kid.photo from kid where kid.b_id=(select driver.b_id from driver where token=? \
) and kid.id=?', [token, kid_id])
r = cur.fetchone()
if r is None:
return r
else:
return r[0]
def is_valid_token(token):
date = utils.get_date_full()
cur = conn.execute(
'select email from parent where token=? and DATETIME(expires) > DATETIME(?)',
(token, date))
user = cur.fetchone()
if user is None:
return False
else:
return True
def get_all_locations(self):
cur = conn.execute(
'select location.id,location.gps,location.time from location where location.j_id= ( '
' select driver.active_ride_j_id from driver inner join kid on kid.b_id=driver.b_id '
'inner join parent on parent.id=kid.p_id where parent.token=? and kid.id=? ) order by '
'location.id desc limit 100', [self.token, self.kid_id])
res = cur.fetchall()
if res is None:
return []
else:
return [{"id": i[0], "gps": i[1], "time": i[2]} for i in res]
def is_ride_already_completed(userid, j_type, date=utils.get_date_only()):
cur = conn.execute(
'select * from journey inner join bus on bus.id=journey.b_id inner join driver'
' on driver.b_id=bus.id where driver.userid=? '
' and date(journey.date)=date(?) and journey.j_type=? and journey.end is not 0 ',
[userid, date, j_type])
res = cur.fetchone()
if res is None:
return False # no ride yet
else:
return True
def add_or_get(self):
# refer link:admin.kid
cur = conn.execute('select id from parent where email=? ',
[self.email])
res = cur.fetchone()
print(res)
if res is None:
# create new record
res = self.add()
return res
else:
return res[0]
def get_recent_rides(self, kid_id):
cur = conn.execute(
'select journey.id,journey.j_type ,attendance.pick_time,'
'attendance.pick_gps ,attendance.drop_time,attendance.drop_gps,attendance.drop_present , journey.gps '
'from attendance inner join journey on attendance.j_id=journey.id '
'where attendance.k_id=? order by attendance.id desc limit 50 ',
[kid_id])
res = cur.fetchall()
if res is None:
return []
else:
l = []
for r in res:
l.append({
"journey_id": r[0],
"journey_type": r[1],
"start_time": r[2],
"start_gps": r[3],
"end_time": r[4],
"end_gps": r[5],
"completed": r[6],
"current_gps": r[7]
})
return l
def get_all_parent_kid_with_bus():
# get data from parent_kid_bus view
cur = conn.execute('select * from parent_kid_bus')
print(cur.description)
return cur.fetchall()
def get_user(email):
cur = conn.execute('select * from parent where email=?', [email])
if cur.rowcount == 0:
return False
else:
return cur.fetchone()
def add(self):
cur = conn.execute(
'insert into parent (name,password,email,phone,home_gps) values(?,?,?,?,?)',
(self.name, self.password, self.email, self.phone, self.home_gps))
conn.commit()
return cur.lastrowid
def get_all(self):
cur = conn.execute('select * from parent')
return cur.fetchall()
def get_all(self):
cur = conn.execute(
'select id,name,email,title,message, date(date) as date, time(date) as time from feedback order by id desc limit 40'
)
return cur.fetchall()
def get_location(journey_id):
cur = conn.execute('select * from location where j_id=? ',
[journey_id])
def get_kid_drop_not_present(journey_id):
cur = conn.execute(
'select attendance.k_id from attendance where j_id=? and pick_present=1 \
and drop_present=0 ', [journey_id])
return Kid.id_to_list(cur.fetchall())
def get_parent_id(token):
cur = conn.execute('select * from parent where token=? ', [token])
return cur.fetchone()
def update_token(email, token, expire):
cur = conn.execute('update parent set token=?,expires=? where email=?',
[token, expire, email])
conn.commit()
def get_kids(token):
cur = conn.execute(
'select kid.id,name,section,photo,bus.id,bus.b_no from kid inner join bus on kid.b_id=bus.id where kid.p_id = \
(select parent.id from parent where parent.token=? )', [token])
return Parent.kids_to_obj_list(cur.fetchall())
def add(self):
cur = conn.execute(
'insert into location (gps,time,j_id) values(?,?,?) ',
(self.gps, self.time, self.j_id))
conn.commit()
return cur.lastrowid
def get_kids(parent_id):
cur = conn.execute('select * from kid where p_id=?', (parent_id))
return cur.fetchall()
