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