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()