class JobPost(db.Model):
__tablename__ = 'jobpost'
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(120), db.ForeignKey('user.email'))
timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
title = db.Column(db.String(64))
organization = db.Column(db.String(32))
description = db.Column(db.String(1000))
pay = db.Column(db.FLOAT(precision=10, scale=2))
pay_frequency = db.Column(db.String(32))
impressions = db.Column(db.Text(), default='')
keywords = db.Column(db.Text(), nullable=True)
# start_date = db.Column(db.Date,nullable=True)
# end_date = db.Column(db.Date,nullable=True)
def __repr__(self):
return '<Job {}>'.format(self.title, self.email)
def serialize(self):
return {
'id': self.id,
'email': self.email,
'title': self.title,
'timestamp': self.timestamp,
'organization': self.organization,
'description': self.description,
'pay': self.pay,
'pay_frequency': self.pay_frequency,
'impressions': self.impressions,
'keywords': self.keywords
#'applications': [a.serialize() for a in self.applications]}
}
class BuyCar(db.Model):
__tablename__ = 'buycar'
id = db.Column(db.Integer, primary_key=True)
add_time = db.Column(db.DATETIME, default=datetime.now()) # 加入购物车时间
num = db.Column(db.Integer, default=1) # 商品数量 默认只购买一件 应该禁止修改
price = db.Column(db.FLOAT(16))
course_id = db.Column(db.Integer,
db.ForeignKey('course.course_id')) # 购物车商品名称
user_car = db.Column(db.Integer, db.ForeignKey('user.id')) # 哪个用户购物车里的商品
class Stations(db.Model):
ID = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.VARCHAR(255))
address = db.Column(db.VARCHAR(255))
p_latitude = db.Column(db.FLOAT())
p_longitude = db.Column(db.FLOAT())
banking = db.Column(db.VARCHAR(255))
bonus = db.Column(db.VARCHAR(255))
bike_stands = db.Column(db.Integer())
def __init__(self, ID, name, address, bonus, latitude, longitude,
bike_stands, banking):
self.ID = ID
self.name = name
self.address = address
self.bonus = bonus
self.p_latitude = latitude
self.p_longitude = longitude
self.bike_stands = bike_stands
self.banking = banking
class Course(db.Model):
__tablename__ = 'course'
id = db.Column(db.Integer, primary_key=True) # 序号
course_id = db.Column(db.Integer, unique=True) # 商品id
course_name = db.Column(db.String(512), unique=True) # 商品名称
introduce = db.Column(db.String(512)) # 课程简介
image = db.Column(db.String(256), unique=True) # 商品图片 文件名唯一
good_tag = db.Column(db.String(128), db.ForeignKey('tag.name')) # 商品所属分类
long_time = db.Column(db.String(128)) # 课程时长
study_num = db.Column(db.Integer) # 学习人数 购买人数
teacher_name = db.Column(db.String(512),
db.ForeignKey('teacher.teacher_name')) # 该课程对应讲师
school_id = db.Column(db.Integer,
db.ForeignKey('school.id')) # 该课程对应的机构/学校
difficulty = db.Column(db.Integer) # 难度
# 1 入门 2 初级 3 中级 4 高级
chap_num = db.Column(db.Integer) # 该课程章节数
price = db.Column(db.FLOAT(16)) # 现价
old_price = db.Column(db.FLOAT(16)) # 原价
start = db.Column(db.Integer) # 星级>>>1-5星
discount = db.Column(db.FLOAT(32)) # 折扣
add_time = db.Column(db.DATETIME, default=datetime.now()) # 上架时间
skull_num = db.Column(db.Integer, default=0) # 库存
sales = db.Column(db.Integer, default=0) # 销量
view_num = db.Column(db.Integer, default=1) # 浏览次数
comment_num = db.Column(db.Integer, default=0) # 评论数量
course_info = db.Column(db.Text) # 商品介绍
share_link = db.Column(db.String(256)) # 分享链接
get_secure = db.Column(db.String(256)) # 提取密码
target = db.Column(db.Integer, default=1) # 商品是否上架 0表示未上架 1表示已经上架 默认直接上架商品
detail = db.Column(db.Text) # 课程详情介绍
# 外键关联第二步
comment_good = db.relationship('Comment', backref='course')
course_ids = db.relationship('Detail', backref='course')
# course_orders = db.relationship('Orders', backref='course')
car_id = db.relationship('BuyCar', backref='course')
good_course = db.relationship('SchoolCourse', backref='course')
course_chapter = db.relationship('CourseChapters')
class Weather(db.Model):
ID = db.Column(db.Integer(), primary_key=True)
temp = db.Column(db.FLOAT())
weather = db.Column(db.VARCHAR(255))
w_description = db.Column(db.VARCHAR(255))
visibility = db.Column(db.Integer())
wind_speed = db.Column(db.Integer())
datetime = db.Column(db.BigInteger())
day = db.Column(db.TIMESTAMP())
week = db.Column(db.Integer())
def __init__(self, ID, temp, weather, w_description, visibility,
wind_speed, datetime, day, week):
self.ID = ID
self.temp = temp
self.weather = weather
self.w_description = w_description
self.visibility = visibility
self.wind_speed = wind_speed
self.datetime = datetime
self.day = day
self.week = week
class Questionnaire(db.Model):
__tablename__ = 'Questionnaire'
Tube_ID = db.Column(db.VARCHAR(30), primary_key=True, nullable=False)
Sample_ID = db.Column(db.VARCHAR(30))
Living_city = db.Column(db.VARCHAR(20))
Gender = db.Column(db.SMALLINT())
Age = db.Column(db.FLOAT())
Height = db.Column(db.FLOAT())
Weight = db.Column(db.FLOAT())
Sampling_date = db.Column(db.DATETIME())
Questionnaire_date = db.Column(db.DATETIME())
Questionnaire_status = db.Column(db.SMALLINT(), default=0)
Stool_shape = db.Column(db.SMALLINT())
Defecation_pattern = db.Column(db.SMALLINT())
Defecation_regular = db.Column(db.SMALLINT())
Defecation_period = db.Column(db.SMALLINT())
Alcohol = db.Column(db.SMALLINT())
Smoking = db.Column(db.SMALLINT())
Meat_product = db.Column(db.SMALLINT())
Fresh_vegetables = db.Column(db.SMALLINT())
Dietary_preference = db.Column(db.SMALLINT())
Dietary_preference_text = db.Column(db.VARCHAR(30))
Past_medical_history = db.Column(db.VARCHAR(60))
Family_history = db.Column(db.VARCHAR(40))
Subhealth_state = db.Column(db.SMALLINT())
Subhealth_state_text = db.Column(db.VARCHAR(200))
Breakfasts_weekly = db.Column(db.SMALLINT())
Supper_weekly = db.Column(db.SMALLINT())
Water_daily = db.Column(db.SMALLINT())
Drinks_preference = db.Column(db.VARCHAR(20))
Sleeping_time = db.Column(db.SMALLINT())
Insomnia_pattern = db.Column(db.SMALLINT())
Nap_pattern = db.Column(db.SMALLINT())
Fasting_blood_glucose = db.Column(db.FLOAT())
Medicine = db.Column(db.VARCHAR(40))
Health_product = db.Column(db.VARCHAR(40))
Triglyceride = db.Column(db.FLOAT())
Total_cholesterol = db.Column(db.FLOAT())
High_density_lipoprotein = db.Column(db.FLOAT())
Low_density_lipoprotein = db.Column(db.FLOAT())
Way_of_birth = db.Column(db.SMALLINT())
Breastfeeding = db.Column(db.SMALLINT())
Medical_examination = db.Column(db.SMALLINT())
Spend_on_MH = db.Column(db.SMALLINT())
Remarks = db.Column(db.VARCHAR(30))
BMI = db.Column(db.FLOAT())
Status = db.Column(db.SMALLINT(), default=2)
def Questionnaire2dict(self):
data = {
'Sample_ID': self.Sample_ID,
'Tube_ID': self.Tube_ID,
'Living_city': self.Living_city,
'Gender': self.Gender,
'Age': self.Age,
'Height': self.Height,
'Weight': self.Weight,
'Sampling_date': self.Sampling_date,
'Questionnaire_date': self.Questionnaire_date,
'Questionnaire_status': self.Questionnaire_status,
'Stool_shape': self.Stool_shape,
'Defecation_pattern': self.Defecation_pattern,
'Defecation_regular': self.Defecation_regular,
'Defecation_period': self.Defecation_period,
'Alcohol': self.Alcohol,
'Smoking': self.Smoking,
'Meat_product': self.Meat_product,
'Fresh_vegetables': self.Fresh_vegetables,
'Dietary_preference': self.Dietary_preference,
'Dietary_preference_text': self.Dietary_preference_text,
'Past_medical_history': self.Past_medical_history,
'Family_history': self.Family_history,
'Subhealth_state_text': self.Subhealth_state_text,
'Breakfasts_weekly': self.Breakfasts_weekly,
'Supper_weekly': self.Supper_weekly,
'Water_daily': self.Water_daily,
'Drinks_preference': self.Drinks_preference,
'Sleeping_time': self.Sleeping_time,
'Insomnia_pattern': self.Insomnia_pattern,
'Nap_pattern': self.Nap_pattern,
'Fasting_blood_glucose': self.Fasting_blood_glucose,
'Medicine': self.Medicine,
'Health_product': self.Health_product,
'Triglyceride': self.Triglyceride,
'Total_cholesterol': self.Total_cholesterol,
'High_density_lipoprotein': self.High_density_lipoprotein,
'Low_density_lipoprotein': self.Low_density_lipoprotein,
'Way_of_birth': self.Way_of_birth,
'Breastfeeding': self.Breastfeeding,
'Medical_examination': self.Medical_examination,
'Spend_on_MH': self.Spend_on_MH,
'Remarks': self.Remarks,
'BMI': self.BMI,
'Status': self.Status
}
if None != data['Sampling_date']:
data['Sampling_date'] = data['Sampling_date'].strftime(
"%Y-%m-%d %H:%M:%S")
if None != data['Questionnaire_date']:
data['Questionnaire_date'] = data['Questionnaire_date'].strftime(
"%Y-%m-%d %H:%M:%S")
return data
def Questionnaire2insert(self, data):
if 'Tube_ID' in data:
self.Tube_ID = data['Tube_ID']
if 'Sample_ID' in data:
self.Sample_ID = data['Sample_ID']
def Questionnaire2update(self, data):
if 'Sample_ID' in data:
self.Sample_ID = data['Sample_ID']
if 'Living_city' in data:
self.Living_city = data['Living_city']
if 'Gender' in data:
self.Gender = data['Gender']
if 'Age' in data:
self.Age = data['Age']
if 'Height' in data:
self.Height = data['Height']
if 'Weight' in data:
self.Weight = data['Weight']
if 'Sampling_date' in data:
self.Sampling_date = data['Sampling_date']
if 'Questionnaire_date' in data:
self.Questionnaire_date = data['Questionnaire_date']
if 'Stool_shape' in data:
self.Stool_shape = data['Stool_shape']
if 'Defecation_pattern' in data:
self.Defecation_pattern = data['Defecation_pattern']
if 'Defecation_regular' in data:
self.Defecation_regular = data['Defecation_regular']
if 'Defecation_period' in data and data['Defecation_period'] != '':
self.Defecation_period = data['Defecation_period']
if 'Alcohol' in data:
self.Alcohol = data['Alcohol']
if 'Smoking' in data:
self.Smoking = data['Smoking']
if 'Meat_product' in data:
self.Meat_product = data['Meat_product']
if 'Fresh_vegetables' in data:
self.Fresh_vegetables = data['Fresh_vegetables']
if 'Dietary_preference' in data:
self.Dietary_preference = data['Dietary_preference']
if 'Dietary_preference_text' in data:
self.Dietary_preference_text = data['Dietary_preference_text']
if 'Past_medical_history' in data:
self.Past_medical_history = data['Past_medical_history']
if 'Family_history' in data:
self.Family_history = data['Family_history']
if 'Subhealth_state_text' in data:
self.Subhealth_state_text = data['Subhealth_state_text']
if 'Breakfasts_weekly' in data:
self.Breakfasts_weekly = data['Breakfasts_weekly']
if 'Supper_weekly' in data:
self.Supper_weekly = data['Supper_weekly']
if 'Water_daily' in data:
self.Water_daily = data['Water_daily']
if 'Drinks_preference' in data:
self.Drinks_preference = data['Drinks_preference']
if 'Sleeping_time' in data:
self.Sleeping_time = data['Sleeping_time']
if 'Insomnia_pattern' in data:
self.Insomnia_pattern = data['Insomnia_pattern']
if 'Nap_pattern' in data:
self.Nap_pattern = data['Nap_pattern']
if 'Fasting_blood_glucose' in data and data[
'Fasting_blood_glucose'] != '':
self.Fasting_blood_glucose = data['Fasting_blood_glucose']
if 'Medicine' in data:
self.Medicine = data['Medicine']
if 'Health_product' in data:
self.Health_product = data['Health_product']
if 'Triglyceride' in data and data['Triglyceride'] != '':
self.Triglyceride = data['Triglyceride']
if 'Total_cholesterol' in data and data['Total_cholesterol'] != '':
self.Total_cholesterol = data['Total_cholesterol']
if 'High_density_lipoprotein' in data and data[
'High_density_lipoprotein'] != '':
self.High_density_lipoprotein = data['High_density_lipoprotein']
if 'Low_density_lipoprotein' in data and data[
'Low_density_lipoprotein'] != '':
self.Low_density_lipoprotein = data['Low_density_lipoprotein']
if 'Way_of_birth' in data:
self.Way_of_birth = data['Way_of_birth']
if 'Breastfeeding' in data:
self.Breastfeeding = data['Breastfeeding']
if 'Medical_examination' in data:
self.Medical_examination = data['Medical_examination']
if 'Spend_on_MH' in data:
self.Spend_on_MH = data['Spend_on_MH']
if 'Remarks' in data:
self.Remarks = data['Remarks']
if 'BMI' in data:
self.BMI = data['BMI']
if 'Status' in data:
# self.Status = data['Status']
pass
else:
self.Status = 2
if 0 == self.Questionnaire_status:
self.Questionnaire_status = 1
def __repr__(self):
return "<Questionnaire %r>" % self.__tablename__
class Route(db.Model):
id = db.Column(db.Integer, primary_key=True)
# creator = db.Column(db.String(64))
departure_time = db.Column(db.DateTime,
index=True,
default=datetime.utcnow)
departure_location_lat = db.Column(db.Float(precision=53))
departure_location_long = db.Column(db.Float(precision=53))
departure_location_string = db.Column(db.String(256))
arrival_location_lat = db.Column(db.Float(precision=53))
arrival_location_long = db.Column(db.Float(precision=53))
arrival_location_string = db.Column(db.String(256))
driver_id = db.Column(db.Integer,
db.ForeignKey('user.id', ondelete='CASCADE'))
passenger_places = db.Column(db.Integer)
maximum_deviation = db.Column(db.FLOAT(precision=3))
playlist = db.Column(db.String(32)) # Should be the spotify playlist id
def __repr__(self):
return '<Route from {}, {} to {}, {}>'.format(
self.departure_location_lat, self.departure_location_long,
self.arrival_location_lat, self.arrival_location_long)
@hybrid_method
def arrival_distance(self, location, allowed):
return distance(
(self.arrival_location_lat, self.arrival_location_long),
location).km <= allowed
@arrival_distance.expression
def arrival_distance(cls, location, allowed):
return distance((cls.arrival_location_lat, cls.arrival_location_long),
location).km <= allowed
def to_dict(self):
data = {
"id": self.id,
"driver-id": self.driver_id,
"passenger-ids":
self.passengers(), # TODO: test whether this works
"passenger-places": self.passenger_places,
"from":
[self.departure_location_lat, self.departure_location_long],
"to": [self.arrival_location_lat, self.arrival_location_long],
"arrive-by":
self.departure_time.isoformat() + ".00" # include milliseconds
}
return data
def from_dict(self, data):
if "from" in data:
self.departure_location_lat, self.departure_location_long = data[
"from"]
self.departure_location_string = addr(self.departure_location_lat,
self.departure_location_long)
if "to" in data:
self.arrival_location_lat, self.arrival_location_long = data["to"]
self.arrival_location_string = addr(self.arrival_location_lat,
self.arrival_location_long)
if "passenger-places" in data:
self.passenger_places = data["passenger-places"]
if "arrive-by" in data:
# src: https://stackoverflow.com/questions/969285/how-do-i-translate-an-iso-8601-datetime-string-into-a-python-datetime-object
self.departure_time = dateutil.parser.parse(data["arrive-by"])
self.maximum_deviation = 15
def text_from(self):
if self.departure_location_string:
return self.departure_location_string
else:
self.departure_location_string = addr(self.departure_location_lat,
self.departure_location_long)
return self.departure_location_string
# return addr(self.departure_location_lat, self.departure_location_long)
def text_to(self):
if self.arrival_location_string:
return self.arrival_location_string
else:
self.arrival_location_string = addr(self.arrival_location_lat,
self.arrival_location_long)
return self.arrival_location_string
# return addr(self.arrival_location_lat, self.arrival_location_long)
def driver(self):
return User.query.get(self.driver_id)
def places_left(self):
try:
current_passengers = self.passengers()
return self.passenger_places - len(current_passengers)
except:
return 0
# Returns a list of the passenger id's
def passengers(self):
# return RouteRequest.query(RouteRequest.user_id).filter_by(route_id=self.id, status=RequestStatus.accepted).all()
passengers = RouteRequest.query.filter_by(
route_id=self.id, status=RequestStatus.accepted).all()
passenger_ids = []
for passenger in passengers:
passenger_ids.append(passenger.user_id)
return passenger_ids
def stops(self, sorted=False, passengerNames=False):
route = RouteRequest.query.filter_by(
route_id=self.id, status=RequestStatus.accepted).all()
stops = []
for stop in route:
newstop = (stop.departure_location_lat,
stop.departure_location_long, stop.pickup_text())
if passengerNames:
newstop += (stop.user().name(), )
stops.append(newstop)
if sorted:
stops.sort(key=lambda x: distance(x[0], x[1]), reverse=True)
return stops
def google_calendar_link(self):
start_time = self.departure_time.isoformat() # "20180512T230000Z"
end_time = (self.departure_time + timedelta(hours=1)).isoformat()
# Remove the '-' and ':' from the time
translation_table = dict.fromkeys(
map(ord, '-:'),
None) # We want to replace the '-' and ':' with Nothing)
start_time = start_time.translate(translation_table)
end_time = end_time.translate(translation_table)
base_url = "https://calendar.google.com/calendar/r/eventedit"
description = "Your trip from {from_} to {to_}. For details, link here: {route_url}" \
.format(from_=self.text_from(),
to_=self.text_to(),
route_url=url_for('routes_drive.drive', drive_id=self.id, _external=True))
parameters = "?text={event_name}&dates={start_time}/{end_time}&details={description}&location={location}"
parameters = parameters.format(event_name="[PlaceHolder] Trip",
start_time=start_time,
end_time=end_time,
description=quote(description),
location=quote(self.text_from()))
# quote encodes the special characters in a string to e.g. %20
return base_url + parameters