class UserInfo(db.Model):
"""ユーザー本人情報テーブル"""
__tablename__ = 'UserInfo'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
UserInfo_id = db.Column(db.Integer, primary_key=True)
User_id = db.Column(db.Integer, db.ForeignKey('User.User_id'), nullable=False)
last_name = db.Column(db.String(255), nullable=False)
first_name = db.Column(db.String(255), nullable=False)
last_name_kana = db.Column(db.String(255), nullable=False)
first_name_kana = db.Column(db.String(255), nullable=False)
birth = db.Column(db.Date, nullable=False)
create_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
update_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
def __init__(self, User_id, last_name, first_name, last_name_kana, first_name_kana, birth):
self.User_id = User_id
self.last_name = last_name
self.first_name = first_name
self.last_name_kana = last_name_kana
self.first_name_kana = first_name_kana
self.birth = birth
def create_new_userinfo(self):
db.session.add(self)
@classmethod
def select_userinfo_by_user_id(cls):
"""ユーザーIDによってユーザー本人情報テーブルのレコードを取得する"""
return cls.query.filter_by(User_id = current_user.get_id()).first()
class Address(db.Model):
"""住所情報テーブル"""
__tablename__ = 'Address'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
Address_id = db.Column(db.Integer, primary_key=True, nullable=False)
User_id = db.Column(db.Integer, db.ForeignKey('User.User_id'), nullable=False)
zip_code = db.Column(db.Integer, nullable=False)
prefecture = db.Column(db.String(64), nullable=False)
address1 = db.Column(db.String(255), nullable=False)
address2 = db.Column(db.String(255), nullable=False)
address3 = db.Column(db.String(255))
create_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
update_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
def __init__(self, User_id, zip_code, prefecture, address1, address2, address3):
self.User_id = User_id
self.zip_code = zip_code
self.prefecture = prefecture
self.address1 = address1
self.address2 = address2
self.address3 = address3
def create_new_useraddress(self):
db.session.add(self)
@classmethod
def select_address_by_user_id(cls):
"""ユーザーIDによって住所情報テーブルのレコードを取得する"""
return cls.query.filter_by(User_id = current_user.get_id()).first()
class BuyShippingAddress(db.Model):
"""購入配送先住所情報テーブル"""
__tablename__ = 'BuyShippingAddress'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
BuyShippingAddress_id = db.Column(db.Integer, primary_key=True)
last_name = db.Column(db.String(255), nullable=False)
first_name = db.Column(db.String(255), nullable=False)
last_name_kana = db.Column(db.String(255), nullable=False)
first_name_kana = db.Column(db.String(255), nullable=False)
zip_code = db.Column(db.Integer, nullable=False)
prefecture = db.Column(db.String(64), nullable=False)
address1 = db.Column(db.String(255), nullable=False)
address2 = db.Column(db.String(255), nullable=False)
address3 = db.Column(db.String(255))
create_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
update_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
def __init__(self, last_name, first_name, last_name_kana, first_name_kana, zip_code, prefecture, address1, address2, address3):
self.last_name = last_name
self.first_name = first_name
self.last_name_kana = last_name_kana
self.first_name_kana = first_name_kana
self.zip_code = zip_code
self.prefecture = prefecture
self.address1 = address1
self.address2 = address2
self.address3 = address3
def create_new_buyshippingaddress(self):
db.session.add(self)
class Credit(db.Model):
"""クレジット情報テーブル"""
__tablename__ = 'Credit'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
Credit_id = db.Column(db.Integer, primary_key=True)
User_id = db.Column(db.Integer, db.ForeignKey('User.User_id'), nullable=False)
credit_name = db.Column(db.String(255), nullable=False)
credit_num = db.Column(db.Integer, nullable=False)
expire = db.Column(db.Date, nullable=False)
security_code_hash = db.Column(db.String(255), nullable=False)
create_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
update_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
def __init__(self, User_id, credit_name, credit_num, expire):
self.User_id = User_id
self.credit_name = credit_name
self.credit_num = credit_num
self.expire = expire
def create_new_credit(self):
db.session.add(self)
# Custom property getter
@property
def security_code(self):
raise AttributeError('セキュリティコードは読み取り可能な属性ではありません。')
# Custom property setter
@security_code.setter
def security_code(self, security_code):
# generate_password_hash()：ハッシュ値が生成される
self.security_code_hash = generate_password_hash(security_code)
@classmethod
def search_credit(cls, Credit_id):
return cls.query.get(Credit_id)
@classmethod
def select_credits_by_user_id(cls):
return cls.query.filter_by(User_id = current_user.get_id()).all()
@classmethod
def delete_credit(cls, Credit_id):
"""支払い方法の削除"""
cls.query.filter_by(Credit_id=Credit_id).delete()
class BuyCredit(db.Model):
"""購入クレジット情報テーブル"""
__tablename__ = 'BuyCredit'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
BuyCredit_id = db.Column(db.Integer, primary_key=True)
credit_name = db.Column(db.String(255), nullable=False)
credit_num = db.Column(db.Integer, nullable=False)
expire = db.Column(db.Date, nullable=False)
security_code_hash = db.Column(db.String(255), nullable=False)
create_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
update_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
def __init__(self, credit_name, credit_num, expire, security_code_hash ):
self.credit_name = credit_name
self.credit_num = credit_num
self.expire = expire
self.security_code_hash = security_code_hash
def create_new_buycredit(self):
db.session.add(self)
class PasswordResetToken(db.Model):
"""パスワードリセットトークン情報テーブル"""
__tablename__ = 'PasswordResetToken'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
PasswordResetToken_id = db.Column(db.Integer, primary_key=True)
token = db.Column(
db.String(64),
unique=True,
index=True,
default=str(uuid4),
nullable=False
)
User_id = db.Column(db.Integer, db.ForeignKey('User.User_id'), nullable=False)
expire_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
create_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
update_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
def __init__(self, token, User_id, expire_at):
self.token = token
self.User_id = User_id
self.expire_at = expire_at
@classmethod
def publish_token(cls, user):
"""パスワードリセットトークン情報テーブルにレコードの挿入をする"""
token = str(uuid4())
new_token = cls(
token,
user.User_id,
# トークンの有効期限を1日に設定
datetime.now() + timedelta(days=1)
)
db.session.add(new_token)
return token
@classmethod
def get_user_id_by_token(cls, token):
"""トークンに紐づいたユーザーIDを返す"""
now = datetime.now()
record = cls.query.filter_by(token=str(token)).filter(cls.expire_at > now).first()
if record:
return record.User_id
else:
return None
@classmethod
def delete_token(cls, token):
"""トークンの削除"""
cls.query.filter_by(token=str(token)).delete()
class ShippingAddress(db.Model):
"""配送先住所情報テーブル"""
__tablename__ = 'ShippingAddress'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
ShippingAddress_id = db.Column(db.Integer, primary_key=True)
User_id = db.Column(db.Integer, db.ForeignKey('User.User_id'), nullable=False)
last_name = db.Column(db.String(255), nullable=False)
first_name = db.Column(db.String(255), nullable=False)
last_name_kana = db.Column(db.String(255), nullable=False)
first_name_kana = db.Column(db.String(255), nullable=False)
zip_code = db.Column(db.Integer, nullable=False)
prefecture = db.Column(db.String(64), nullable=False)
address1 = db.Column(db.String(255), nullable=False)
address2 = db.Column(db.String(255), nullable=False)
address3 = db.Column(db.String(255))
create_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
update_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
def __init__(self, User_id, last_name, first_name, last_name_kana, first_name_kana, zip_code, prefecture, address1, address2, address3):
self.User_id = User_id
self.last_name = last_name
self.first_name = first_name
self.last_name_kana = last_name_kana
self.first_name_kana = first_name_kana
self.zip_code = zip_code
self.prefecture = prefecture
self.address1 = address1
self.address2 = address2
self.address3 = address3
def create_new_shippingaddress(self):
db.session.add(self)
@classmethod
def search_shippingaddress(cls, ShippingAddress_id):
return cls.query.get(ShippingAddress_id)
@classmethod
def select_shippingaddresses_by_user_id(cls):
return cls.query.filter_by(User_id = current_user.get_id()).all()
@classmethod
def delete_shippingaddress(cls, shippingaddress_id):
"""配送先住所レコードの削除"""
cls.query.filter_by(ShippingAddress_id=shippingaddress_id).delete()
class User(UserMixin, db.Model):
"""ユーザー情報テーブル"""
__tablename__ = 'User'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
User_id = db.Column(db.Integer, primary_key=True)
user_code = db.Column(db.String(64), unique=True, index=True, nullable=False)
username = db.Column(db.String(64), index=True, nullable=False)
email = db.Column(db.String(64), unique=True, index=True, nullable=False)
password_hash = db.Column(db.String(128), nullable=False)
picture_path = db.Column(db.Text, default='default.jpeg', nullable=False)
prof_comment = db.Column(db.Text)
default_ShippingAddress_id = db.Column(db.Integer, db.ForeignKey('ShippingAddress.ShippingAddress_id'))
default_pay_way = db.Column(db.Integer, default=1, nullable=False)
default_Credit_id = db.Column(db.Integer, db.ForeignKey('Credit.Credit_id'))
is_active = db.Column(db.Boolean, default=True, nullable=True)
create_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
update_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
def __init__(self, user_code, username, email):
self.user_code = user_code
self.username = username
self.email = email
def create_new_user(self):
db.session.add(self)
def get_id(self):
"""load_userが受け取る引数"""
return (self.User_id)
# Custom property getter
@property
def password(self):
raise AttributeError('パスワードは読み取り可能な属性ではありません。')
# Custom property setter
@password.setter
def password(self, password):
# generate_password_hash()：ハッシュ値が生成される
self.password_hash = generate_password_hash(password)
def validate_password(self, password):
"""
ユーザーのパスワードと引数のパスワードが正しいか判定する。
一致していたらTrueを返す。
"""
# check_password_hash():ハッシュ値が指定した文字列のものと一致しているか判定
# 一致→True 不一致→False
return check_password_hash(self.password_hash, password)
@classmethod
def select_user_by_email(cls, email):
"""emailによってユーザーを得る"""
return cls.query.filter_by(email=email).first()
@classmethod
def select_user_by_id(cls, User_id):
"""ユーザーIDによってユーザーを得る"""
return cls.query.get(User_id)
@classmethod
def select_user_by_user_code(cls, user_code):
"""ユーザーコードによってユーザーを得る"""
return cls.query.filter_by(user_code=user_code).first()
@classmethod
def user_search_by_word(cls, word):
"""ユーザー情報の検索"""
return cls.query.filter(or_(
cls.username.like(f'%{word}%'),
cls.user_code.like(f'%{word}%')
)).all()
class Sell(db.Model):
"""出品情報テーブル"""
__tablename__ = 'Sell'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
Sell_id = db.Column(db.Integer, primary_key=True)
User_id = db.Column(db.Integer, db.ForeignKey('User.User_id'), nullable=False)
sell_title = db.Column(db.String(255), nullable=False)
key1 = db.Column(db.String(255), nullable=False)
key2 = db.Column(db.String(255), nullable=False)
key3 = db.Column(db.String(255), nullable=False)
sell_comment = db.Column(db.Text, nullable=False)
price = db.Column(db.Integer, nullable=False)
item_picture_path = db.Column(db.Text, default='default.png', nullable=False)
genre = db.Column(EnumType(enum_class=Genre), nullable=False)
item_state = db.Column(EnumType(enum_class=Item_state), nullable=False)
postage = db.Column(EnumType(enum_class=Postage), nullable=False)
send_way = db.Column(EnumType(enum_class=Send_way), nullable=False)
consignor = db.Column(db.String(64), nullable=False)
schedule = db.Column(EnumType(enum_class=Schedule), nullable=False)
remarks = db.Column(db.Text)
deal_status = db.Column(EnumType(enum_class=Deal_status),default=Deal_status(1), nullable=False)
sell_flg = db.Column(db.Boolean,default=True, nullable=False)
is_active = db.Column(db.Boolean,default=True, nullable=False)
has_sent = db.Column(db.Boolean,default=False, nullable=False)
has_got = db.Column(db.Boolean,default=False, nullable=False)
create_at = db.Column(db.DateTime,default=datetime.now, nullable=False)
update_at = db.Column(db.DateTime,default=datetime.now, nullable=False)
# Sellテーブルからデータを取得時にUserテーブルも取得
user = db.relationship('User', backref='sell', lazy='joined', uselist=False)
def __init__(self, User_id, sell_title, key1, key2, key3, sell_comment, price, item_picture_path, genre, item_state, \
postage, send_way, consignor, schedule, remarks):
self.User_id = User_id
self.sell_title = sell_title
self.key1 = key1
self.key2 = key2
self.key3 = key3
self.sell_comment = sell_comment
self.price = price
self.item_picture_path = item_picture_path
self.genre = Genre[genre]
self.item_state = Item_state[item_state]
self.postage = Postage[postage]
self.send_way = Send_way[send_way]
self.consignor = consignor
self.schedule = Schedule[schedule]
self.remarks = remarks
def create_new_sell(self):
db.session.add(self)
@classmethod
def select_sell_by_sell_id(cls, Sell_id):
"""Sell_id(item_id)によってSell(出品情報)レコードを得る"""
return cls.query.get(Sell_id)
@classmethod
def select_sell_by_user_id(cls, User_id):
"""User_idによってSell(商品)レコードを得る"""
return cls.query.filter(cls.User_id==User_id).all()
@classmethod
def select_sell_by_user_id_sort(cls, User_id):
"""User_idによってSell(商品)レコードを得る(新着順)"""
return cls.query.filter(
and_(
cls.User_id==User_id,
cls.sell_flg == True,
cls.is_active == True
)
).order_by(desc(cls.create_at)).all()
@classmethod
def select_sell_id_by_user_id(cls, User_id):
"""User_idによってSell(商品)レコードのSell_idを得る"""
return cls.query.filter(cls.User_id==User_id).with_entities(cls.Sell_id).all()
@classmethod
def select_all_sell_by_deal_status(cls, deal_status):
"""deal_statusによってSell(商品)レコードを得る"""
return cls.query.filter(
cls.deal_status==Deal_status(deal_status)
).with_entities(cls.Sell_id).all()
@classmethod
def select_not_user_sell_by_deal_status(cls, User_id, deal_status):
"""
SellのUser_idが引数のUser_idと一致しないかつ、
deal_statusによってSell(商品)レコードを得る
"""
return cls.query.filter(cls.User_id!=User_id, cls.deal_status==Deal_status(deal_status)).all()
@classmethod
def select_sell_by_deal_status(cls, User_id, deal_status):
"""User_idとdeal_statusによってSell(商品)レコードを得る"""
return cls.query.filter(cls.User_id==User_id, cls.deal_status==Deal_status(deal_status)
).all()
@classmethod
def select_sell_by_deal_status_page(cls, User_id, deal_status, page=1):
"""User_idとdeal_statusによってSell(商品)レコードを得る"""
buy = aliased(Buy)
if deal_status == 1:
return cls.query.filter(
cls.User_id==User_id, cls.deal_status==Deal_status(deal_status)
).order_by(desc(cls.create_at)).paginate(page, 1, False)
if deal_status == 2:
return cls.query.filter(
cls.User_id==User_id, cls.deal_status==Deal_status(deal_status)
).join(
buy,
cls.Sell_id == buy.Sell_id
).order_by(desc(buy.create_at)).paginate(page, 1, False)
if deal_status == 3:
return cls.query.filter(
cls.User_id==User_id, cls.deal_status==Deal_status(deal_status)
).order_by(desc(cls.update_at)).paginate(page, 1, False)
# paginate(表示するページ,1ページに表示する数,エラーを出力するかどうか)
@classmethod
def select_new_sell(cls):
"""
出品状態、有効フラグが有効の商品を新着順に取り出す
"""
return cls.query.filter_by(
sell_flg = True, is_active = True
).order_by(desc(cls.create_at)).all()
@classmethod
def delete_sell(cls, Sell_id):
"""出品情報の削除"""
cls.query.filter_by(Sell_id=Sell_id).delete()
@classmethod
def item_search(cls, word, sort, genre, value_min, value_max, status, postages, deal_statuses):
"""商品検索"""
filter_q = cls.query.filter(
or_(
cls.key1.like(f'%{word}%'),
cls.key2.like(f'%{word}%'),
cls.key3.like(f'%{word}%'),
cls.sell_comment.like(f'%{word}%'),
)
)
if genre is None or genre == "all":
all_genre = list(Genre)
filter_q = filter_q.filter(cls.genre.in_(all_genre))
else:
genre = Genre[genre]
filter_q = filter_q.filter(cls.genre == genre)
if value_min is not None:
filter_q = filter_q.filter(cls.price >= value_min)
if value_max is not None:
filter_q = filter_q.filter(cls.price <= value_max)
if status != [] and len(status) != 6:
item_state_filters = []
for state in status:
item_state = Item_state[state]
item_state_filters.append(cls.item_state == item_state)
filter_q = filter_q.filter(or_(*item_state_filters))
if postages != [] and len(postages) != 2:
for postage in postages:
postage = Postage[postage]
filter_q = filter_q.filter(cls.postage == postage)
if deal_statuses != [] and len(deal_statuses) != 2:
for deal_status in deal_statuses:
if deal_status == "販売中":
deal_status = Deal_status(1)
filter_q = filter_q.filter(cls.deal_status == deal_status)
if deal_status == "売り切れ":
deal_status_2 = Deal_status(2)
deal_status_3 = Deal_status(3)
filter_q = filter_q.filter(
or_(
cls.deal_status == deal_status_2,
cls.deal_status == deal_status_3
)
)
if sort == '価格の安い順':
items = filter_q.order_by(cls.price).all()
elif sort == '価格の高い順':
items = filter_q.order_by(desc(Sell.price)).all()
elif sort == '出品の新しい順':
items = filter_q.order_by(desc(Sell.create_at)).all()
elif sort == 'いいね！の多い順':
likes = aliased(Likes)
likes_q = likes.query.group_by(likes.Sell_id).with_entities(likes.Sell_id, func.count(likes.Sell_id).label("likecnt")).subquery()
items = filter_q.outerjoin(
likes_q,
cls.Sell_id == likes_q.c.Sell_id
).order_by(desc(likes_q.c.likecnt)).all()
else:
items = filter_q.all()
return items
@classmethod
def select_sales(cls, User_id):
"""売り上げ金を合計して返す"""
return cls.query.filter(
cls.User_id==current_user.User_id,
cls.deal_status==Deal_status(3)
).with_entities(func.sum(Sell.price).label("sumprice")).first()
@classmethod
def select_sall_status(cls, User_id):
"""取引中の出品状態"""
buy = aliased(Buy)
return cls.query.filter(
and_(
Sell.deal_status == Deal_status['取引中'],
or_(
Sell.User_id == User_id,
buy.User_id == User_id
)
)
).join(
buy,
cls.Sell_id == buy.Sell_id
).order_by(desc(Sell.update_at)).all()
class UserTempToken(db.Model):
"""ユーザー仮登録トークン情報テーブル"""
__tablename__ = 'UserTempToken'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
UserTempTokenToken_id = db.Column(db.Integer, primary_key=True)
token = db.Column(
db.String(64),
unique=True,
index=True,
default=str(uuid4),
nullable=False
)
email = db.Column(db.String(64), unique=True, nullable=False)
expire_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
create_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
update_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
def __init__(self, token, email, expire_at):
self.token = token
self.email = email
self.expire_at = expire_at
@classmethod
def publish_token(cls, email):
"""
ユーザー仮登録トークン情報テーブルにレコードの挿入し、
tokenを返す
"""
token = str(uuid4())
new_token = cls(
token,
email,
# トークンの有効期限を1日に設定
datetime.now() + timedelta(days=1)
)
db.session.add(new_token)
return token
@classmethod
def get_email_by_token(cls, token):
"""トークンより仮登録したemailを返す"""
now = datetime.now()
record = cls.query.filter_by(token=str(token)).filter(cls.expire_at > now).first()
if record:
return record.email
else:
return None
@classmethod
def email_exists(cls, email):
"""
引数のemailに紐づくレコードがあればTrue,
なければFalseを返す
"""
now = datetime.now()
record = cls.query.filter_by(email=email).filter(cls.expire_at > now).first()
if record:
return True
else:
return False
@classmethod
def delete_token(cls, token):
"""トークンの削除"""
cls.query.filter_by(token=str(token)).delete()