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 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 PostMessage(db.Model):
"""投稿メッセージテーブル"""
__tablename__ = 'PostMessage'
__table_args__ = (CheckConstraint('update_at >= create_at'), )
PostMessage_id = db.Column(db.Integer, primary_key=True)
Sell_id = db.Column(db.Integer,
db.ForeignKey('Sell.Sell_id'),
nullable=False)
from_user_id = db.Column(db.Integer,
db.ForeignKey('User.User_id'),
nullable=False)
message = db.Column(db.Text, nullable=False)
is_read = db.Column(db.Boolean, default=False, nullable=False)
is_active = db.Column(db.Boolean, default=True, 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, Sell_id, from_user_id, message):
self.Sell_id = Sell_id
self.from_user_id = from_user_id
self.message = message
def create_new_postmessage(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 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 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 UserConnect(db.Model):
"""フォロー情報テーブル"""
__tablename__ = 'UserConnect'
__table_args__ = (CheckConstraint('update_at >= create_at'), )
UserConnect_id = db.Column(db.Integer, primary_key=True)
to_user_id = db.Column(db.Integer,
db.ForeignKey('User.User_id'),
nullable=False)
from_user_id = db.Column(db.Integer,
db.ForeignKey('User.User_id'),
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, to_user_id, from_user_id):
self.to_user_id = to_user_id
self.from_user_id = from_user_id
def create_new_userconnect(self):
db.session.add(self)
@classmethod
def followed_exists(cls, User_id):
"""
引数のUser_idのユーザーをログイン中のユーザーが
フォローしていればTrue、
フォローしていなければFalseを返す
"""
record = cls.query.filter(
and_(cls.to_user_id == User_id,
cls.from_user_id == current_user.get_id())).first()
if record:
return True
else:
return False
@classmethod
def select_followers_by_user_id(cls, User_id):
"""to_user_idがUser_idと一致する複数レコードを抽出"""
return cls.query.filter_by(to_user_id=User_id).all()
@classmethod
def select_follows_by_user_id(cls, User_id):
"""from_user_idがUser_idと一致する複数レコードを抽出"""
return cls.query.filter_by(from_user_id=User_id).all()
@classmethod
def select_follows_user_id_by_user_id(cls, User_id):
"""from_user_idがUser_idと一致するto_user_idのみを抽出"""
return cls.query.filter_by(from_user_id=User_id).with_entities(
cls.to_user_id).all()
@classmethod
def delete_follow(cls, User_id):
"""フォローレコードの削除"""
return cls.query.filter(
and_(cls.to_user_id == User_id,
cls.from_user_id == current_user.get_id())).delete()
@classmethod
def select_timeline_sell(cls, Sell):
"""
SellとUserConnectを結合し
ログインしているユーザー以外かつ
フォローしているユーザーが出品しているかつ
出品状態、有効フラグが有効の商品を新着順に取り出す
"""
sell = aliased(Sell)
return cls.query.filter(
and_(cls.from_user_id == current_user.User_id,
sell.sell_flg == True, sell.is_active == True,
sell.User_id != current_user.User_id)).outerjoin(
sell, sell.User_id == cls.to_user_id).with_entities(
sell).order_by(desc(sell.create_at)).all()
class Likes(db.Model):
"""いいねログテーブル"""
__tablename__ = 'Likes'
__table_args__ = (CheckConstraint('update_at >= create_at'), )
Sell_id = db.Column(
db.Integer,
db.ForeignKey('Sell.Sell_id'),
primary_key=True,
)
User_id = db.Column(db.Integer,
db.ForeignKey('User.User_id'),
primary_key=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, Sell_id, User_id):
self.Sell_id = Sell_id
self.User_id = User_id
def create_new_likes(self):
db.session.add(self)
@classmethod
def liked_exists(cls, Sell_id):
"""
Sell_idとログイン中のユーザーIDが一致するいいねレコードを抽出し、
レコードが存在すればTrue、
存在しなければFalseを返す
"""
record = cls.query.filter(
and_(cls.Sell_id == Sell_id,
cls.User_id == current_user.get_id())).first()
if record:
return True
else:
return False
@classmethod
def liked_exists_user_id(cls, User_id, Sell_id):
"""
Sell_idとユーザーIDが一致するいいねレコードを抽出し、
レコードが存在すればTrue、
存在しなければFalseを返す
"""
record = cls.query.filter(
and_(cls.Sell_id == Sell_id, cls.User_id == User_id)).first()
if record:
return True
else:
return False
@classmethod
def select_likes_by_sell_id(cls, Sell_id):
"""Sell_idと一致する複数いいねレコードを抽出"""
return cls.query.filter_by(Sell_id=Sell_id).all()
@classmethod
def delete_like(cls, Sell_id):
"""いいねレコードの削除"""
return cls.query.filter(
and_(cls.Sell_id == Sell_id,
cls.User_id == current_user.get_id())).delete()
@classmethod
def delete_all_like(cls, Sell_id):
"""商品を削除した時に一緒にする閲覧履歴の削除"""
cls.query.filter_by(Sell_id=Sell_id).delete()
@classmethod
def likes_join_sell(cls, Sell, User_id):
"""
LikesとSellを結合し,
いいねしたUser_idと引数のUser_id
が一致したSellレコードを新着順に取り出す
"""
sell = aliased(Sell)
return cls.query.filter(cls.User_id == User_id).outerjoin(
sell).with_entities(sell).order_by(desc(cls.create_at)).all()
class BrowsingHistory(db.Model):
"""閲覧履歴テーブル"""
__tablename__ = 'BrowsingHistory'
__table_args__ = (CheckConstraint('update_at >= create_at'), )
BrowsingHistory_id = db.Column(db.Integer, primary_key=True)
Sell_id = db.Column(db.Integer,
db.ForeignKey('Sell.Sell_id'),
nullable=False)
User_id = db.Column(db.Integer,
db.ForeignKey('User.User_id'),
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, Sell_id, User_id):
self.Sell_id = Sell_id
self.User_id = User_id
def create_new_browsinghistory(self):
db.session.add(self)
@classmethod
def b_history_join_sell(cls, Sell, User_id):
"""
BrowsingHistoryとSellを結合し,
閲覧したUser_idと引数のUser_id
が一致したSellレコードを新着順に3件取り出す
"""
sell = aliased(Sell)
return cls.query.filter(
and_(
cls.User_id == User_id, sell.Sell_id == cls.Sell_id,
sell.User_id != User_id, sell.sell_flg == True,
sell.is_active == True,
cls.create_at.in_(
cls.query.with_entities(
func.max(cls.create_at).label("max_c")).group_by(
cls.Sell_id)))).outerjoin(
sell, sell.Sell_id ==
cls.Sell_id).with_entities(sell).order_by(
desc(cls.create_at)).limit(4).all()
@classmethod
def select_hit_sell(cls, Sell):
"""
SellとBrowsingHistoryを結合し
出品状態、有効フラグが有効の商品を
今日の日付で閲覧数が多い順に取り出す
"""
sell = aliased(Sell)
now = datetime.now()
return cls.query.filter(
and_(cls.create_at > now - timedelta(days=1),
sell.Sell_id == cls.Sell_id, sell.sell_flg == True,
sell.is_active == True)).outerjoin(
sell,
sell.Sell_id == cls.Sell_id).with_entities(sell).order_by(
desc(func.count())).group_by(cls.Sell_id).all()
@classmethod
def b_history_exists(cls, User_id, Sell_id):
"""
User_idとSell_idが一致するレコードが存在していれば
(ユーザーが商品を閲覧していれば)
True,
存在していなければFalseを返す
"""
record = cls.query.filter(
and_(cls.User_id == User_id, cls.Sell_id == Sell_id)).first()
if record:
return True
else:
return False
@classmethod
def delete_b_history(cls, Sell_id):
"""商品を削除した時に一緒にする閲覧履歴の削除"""
cls.query.filter_by(Sell_id=Sell_id).delete()
class Buy(db.Model):
"""購入情報テーブル"""
__tablename__ = 'Buy'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
Buy_id = db.Column(db.Integer, primary_key=True)
User_id = db.Column(db.Integer, db.ForeignKey('User.User_id'), nullable=False)
Sell_id = db.Column(db.Integer, db.ForeignKey('Sell.Sell_id'), nullable=False)
pay_way = db.Column(db.Integer, nullable=False)
Credit_id = db.Column(db.Integer, db.ForeignKey('BuyCredit.BuyCredit_id'), nullable=False)
ShippingAddress_id = db.Column(db.Integer, db.ForeignKey('BuyShippingAddress.BuyShippingAddress_id'), nullable=False)
create_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
update_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
# Buyテーブルからデータを取得時にBuyShippingAddressテーブルとBuyCreditも取得
s_address = db.relationship('BuyShippingAddress', backref='buy', lazy='joined', uselist=False)
credit = db.relationship('BuyCredit', backref='buy', lazy='joined', uselist=False)
def __init__(self, User_id, Sell_id, pay_way, Credit_id, ShippingAddress_id):
self.User_id = User_id
self.Sell_id = Sell_id
self.pay_way = pay_way
self.Credit_id = Credit_id
self.ShippingAddress_id = ShippingAddress_id
def create_new_buy(self):
db.session.add(self)
@classmethod
def select_buy_by_sell_id(cls, Sell_id):
"""Sell_id(item_id)によってBuy(購入情報)レコードを得る"""
return cls.query.filter_by(Sell_id=Sell_id).first()
@classmethod
def buy_join_sell_deal_status(cls, User_id, deal_status):
"""
Buy(購入)とSell(商品)を結合し、
BuyのUser_idとSellのdeal_statusが引数と一致するものを
絞り込む
"""
sell = aliased(Sell)
return cls.query.filter(
cls.User_id==User_id
).outerjoin(sell, sell.deal_status==Deal_status(deal_status)
).with_entities(sell).all()
@classmethod
def buy_join_sell_deal_status_page(cls, User_id, deal_status, page=1):
"""
Buy(購入)とSell(商品)を結合し、
BuyのUser_idとSellのdeal_statusが引数と一致するものを
絞り込む
"""
sell = aliased(Sell)
if deal_status == 2:
return cls.query.filter(
cls.User_id==User_id,
sell.deal_status==Deal_status(deal_status)
).outerjoin(
sell,
cls.Sell_id==sell.Sell_id
).order_by(desc(cls.create_at)).with_entities(sell).paginate(page, 1, False)
if deal_status == 3:
return cls.query.filter(
cls.User_id==User_id,
sell.deal_status==Deal_status(deal_status)
).outerjoin(
sell,
cls.Sell_id==sell.Sell_id
).order_by(desc(sell.update_at)).with_entities(sell).paginate(page, 1, False)
# paginate(表示するページ,1ページに表示する数,エラーを出力するかどうか)
@classmethod
def buy_exists_user_id(cls, User_id, Sell_id):
"""
Sell_idとユーザーIDが一致する購入情報レコードを抽出し、
レコードが存在すればTrue、
存在しなければFalseを返す
"""
record = cls.query.filter(
and_(
cls.Sell_id == Sell_id,
cls.User_id == User_id
)
).first()
if record:
return True
else:
return False
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 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 Rating(db.Model):
"""相互評価情報テーブル"""
__tablename__ = 'Rating'
__table_args__ = (CheckConstraint('update_at >= create_at'),)
Rating_id = db.Column(db.Integer, primary_key=True)
Sell_id = db.Column(db.Integer, db.ForeignKey('Sell.Sell_id'), nullable=False)
to_user_id = db.Column(db.Integer, db.ForeignKey('User.User_id'), nullable=False)
from_user_id = db.Column(db.Integer, db.ForeignKey('User.User_id'), nullable=False)
rating = db.Column(EnumType(enum_class=Rating_enum), nullable=False)
rating_message = db.Column(db.Text)
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, Sell_id, to_user_id, from_user_id, rating, rating_message):
self.Sell_id = Sell_id
self.to_user_id = to_user_id
self.from_user_id = from_user_id
self.rating = Rating_enum(rating)
self.rating_message = rating_message
def create_new_rating(self):
db.session.add(self)
@classmethod
def select_rate_by_user_id(cls, User_id):
"""User_idのユーザーが評価されたレコードを抽出し、良いと悪いをカウントした値を返す"""
good_ratings = cls.query.filter(
and_(
cls.to_user_id == User_id,
cls.rating == Rating_enum(1)
)
).all()
bad_ratings = cls.query.filter(
and_(
cls.to_user_id == User_id,
cls.rating == Rating_enum(2)
)
).all()
return len(good_ratings),len(bad_ratings)
@classmethod
def select_sell_id_to_user_id(cls, Sell_id, User_id):
"""Sell_id(item_id)とto_user_idによってRating(相互評価情報)レコードを返す"""
return cls.query.filter(
and_(
cls.Sell_id == Sell_id,
cls.to_user_id == User_id
)
).all()
@classmethod
def select_count_sell_id_to_user_id(cls, Sell_id, User_id):
"""Sell_id(item_id)とto_user_idによってRating(相互評価情報)レコードの有無を返す"""
return cls.query.filter(
and_(
cls.Sell_id == Sell_id,
cls.to_user_id == User_id
)
).count()
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()
class DealMessage(db.Model):
"""取引メッセージテーブル"""
__tablename__ = 'DealMessage'
__table_args__ = (CheckConstraint('update_at >= create_at'), )
DealMessage_id = db.Column(db.Integer, primary_key=True)
Sell_id = db.Column(db.Integer,
db.ForeignKey('Sell.Sell_id'),
nullable=False)
to_user_id = db.Column(db.Integer,
db.ForeignKey('User.User_id'),
nullable=False)
from_user_id = db.Column(db.Integer,
db.ForeignKey('User.User_id'),
nullable=False)
message = db.Column(db.Text, nullable=False)
is_read = db.Column(db.Boolean, default=False, nullable=False)
is_checked = db.Column(db.Boolean, default=False, nullable=False)
is_active = db.Column(db.Boolean, default=True, 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, Sell_id, to_user_id, from_user_id, message):
self.Sell_id = Sell_id
self.to_user_id = to_user_id
self.from_user_id = from_user_id
self.message = message
def create_new_dealmessage(self):
db.session.add(self)
@classmethod
def get_messages_by_sell_id(cls, Sell_id, offset_value=0, limit_value=5):
"""Sell_id(item_id)によって取引メッセージレコードを得る"""
return cls.query.filter_by(Sell_id=Sell_id).order_by(
desc(cls.DealMessage_id)).offset(offset_value).limit(
limit_value).all()
# 最新の5件が取り出される
@classmethod
def update_is_read_by_ids(cls, ids):
"""DealMessage_idが一致するレコードのis_readをTrueに更新する"""
cls.query.filter(cls.DealMessage_id.in_(ids)).update(
{'is_read': 1},
# レコードを更新する前にSELECTを実行して更新対象のレコードを取得する。
# デフォルト値(設定しないと)IN句には対応していないためエラーになる。
synchronize_session='fetch')
@classmethod
def update_is_checked_by_ids(cls, ids):
"""DealMessage_idが一致するレコードのis_checkedをTrueに更新する"""
cls.query.filter(cls.DealMessage_id.in_(ids)).update(
{'is_checked': 1}, synchronize_session='fetch')
@classmethod
def select_not_read_messages(cls, dest_user_id, self_user_id, sell_id):
"""相手から自分に対するメッセージでまだ読まれていないメッセージを取得"""
return cls.query.filter(
and_(cls.from_user_id == dest_user_id,
cls.to_user_id == self_user_id, cls.Sell_id == sell_id,
cls.is_read == 0)).order_by(cls.DealMessage_id).all()
@classmethod
def select_not_checked_messages(cls, self_user_id, dest_user_id, sell_id):
"""自分から相手に対するメッセージで相手に読まれているが、自分がチェックしていないメッセージを取得"""
return cls.query.filter(
and_(cls.from_user_id == self_user_id,
cls.to_user_id == dest_user_id, cls.Sell_id == sell_id,
cls.is_read == 1,
cls.is_checked == 0)).order_by(cls.DealMessage_id).all()
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)
