class ViewerRatingModel(db.Model): __table_args__ = {"schema": "sch_storyboard"} __tablename__ = "tbl_viewer_rating" viewer_id = db.Column(db.Integer) story_id = db.Column(db.Integer) latest_rating = db.Column(db.Float) viewer_rating_mapping_id = db.Column(db.Integer, primary_key=True) def __init__(self, i_viewer_id, i_story_id, i_latest_rating): self.viewer_id = i_viewer_id self.story_id = i_story_id self.latest_rating = i_latest_rating def json(self): return {"rating": self.latest_rating} @classmethod def get_viewer_rating(self, in_story_id, in_user_id): try: viewer_rating = self.query.filter_by( story_id=in_story_id ).filter_by(viewer_id=in_user_id).first( ) # flask sqlalchemy requires self.column name to evaluate "!="" condition return viewer_rating except: print("exception occured") @classmethod def upsert_viewer_rating_mapping( self, in_story_id, in_viewer_id, in_rating ): #if the writer is the viewer then ignore,logic has been implemented in parent method. try: viewer_rating = self.get_viewer_rating(in_story_id, in_viewer_id) if viewer_rating is None: new_viewer_rating_map = self(in_viewer_id, in_story_id, 0) db.session.add(new_viewer_rating_map) db.session.commit() else: if (in_rating != 0): previous_latest_rating = viewer_rating.latest_rating viewer_rating.latest_rating = in_rating db.session.commit() RatingModel.update_rating(in_story_id, in_rating, previous_latest_rating) except: print("exception occured") @classmethod def delete_viewer_rating_mapping( self, in_story_id, in_viewer_id ): #if the writer is the viewer then ignore,logic has been implemented in parent method. try: self.query.filter_by(story_id=in_story_id).filter_by( viewer_id=in_viewer_id).delete() except: print("exception occured") finally: db.session.commit()
class WriterModel(db.Model): __table_args__ = {"schema": "sch_storyboard"} __tablename__ = "tbl_writer" writer_id = db.Column(db.Integer, primary_key=True) writer_display_id = db.Column(db.String) writer_first_name = db.Column(db.String) writer_last_name = db.Column(db.String) user_id = db.Column(db.Integer) stories = db.relationship("StoryModel", backref="writer") def __init__(self, i_writer_id, i_writer_display_id, i_writer_first_name, i_writer_last_name, in_user_id): self.writer_id = i_writer_id self.writer_display_id = i_writer_display_id self.writer_first_name = i_writer_first_name self.writer_last_name = i_writer_last_name self.user_id = in_user_id def json(self): return { "userid": self.user_id, "writerid": self.writer_id, "first_name": self.writer_first_name, "last_name": self.writer_last_name } @classmethod def get_writer_by_id(self, in_writer_id): try: #print(in_writer_id) writer = self.query.filter(self.writer_id == in_writer_id).first() return writer except: print("exception occured") @classmethod def get_writer_by_userid(self, in_user_id): # try: print(in_user_id) writer = self.query.filter(self.user_id == in_user_id).first() return writer
class ViewModel(db.Model): __table_args__ = {"schema": "sch_storyboard"} __tablename__ = "tbl_view" view_id = db.Column(db.Integer, primary_key=True) view_number = db.Column(db.Integer) story_id = db.Column(db.Integer) writer_id = db.Column(db.Integer) def __init__(self, i_view_id, i_view_number, i_story_id, i_writer_id): self.view_id = i_view_id self.view_number = i_view_number self.story_id = i_story_id self.writer_id = i_writer_id def json(self): return { "view_id": self.view_id, "view": self.view_number, "story_id": self.story_id, "writer_id": self.writer_id } @classmethod def get_view_by_story(self, in_story_id): try: view = self.query.filter_by(story_id=in_story_id).first( ) # flask sqlalchemy requires self.column name to evaluate "!="" condition return view except: print("exception occured") @classmethod def get_viewlist_by_writer(self, in_writer_id): try: view = self.query.filter_by(writer_id=in_writer_id).first() return view except: print("exception occured") @classmethod def get_view_list_by_story(self): try: view = self.query.all() return view except: print("exception occured") @classmethod def create_view( self, in_story_id, in_writer_id ): #if the writer is the viewer then ignore,logic has been implemented in parent method. try: new_view = self(None, 1, in_story_id, in_writer_id) db.session.add(new_view) except: print("exception occured") finally: db.session.commit() @classmethod def update_view( self, in_story_id): #if user is writer then don't increament the view try: view = self.get_view_by_story( in_story_id ) # reusing the "get_view_by_story" function of this class view.view_number = view.view_number + 1 except: print("exception occured") finally: db.session.commit() @classmethod def delete_view_by_story(self, in_story_id): try: self.query.filter_by(story_id=in_story_id).delete() except: print("exception occured") finally: db.session.commit()
class StoryModel(db.Model): __table_args__ = {"schema": "sch_storyboard"} __tablename__ = "tbl_story" story_id = db.Column(db.Integer, primary_key=True) category_id = db.Column(db.Integer) story_name = db.Column(db.Text) story_description = db.Column(db.Text) story_text_id = db.Column( db.Integer, db.ForeignKey("sch_storyboard.tbl_storytext.story_text_id")) writer_id = db.Column(db.Integer, db.ForeignKey("sch_storyboard.tbl_writer.writer_id")) created_date = db.Column(db.DateTime) updated_date = db.Column(db.DateTime) created_by = db.Column(db.Integer) updated_by = db.Column(db.Integer) total_view = db.Column(db.Integer) average_rating = db.Column(db.Integer) def __init__(self, i_story_id, i_category_id, i_story_name, i_story_description, i_created_date, i_updated_date, i_created_by, i_updated_by, i_total_view, i_average_rating, i_obj_writer, i_obj_storytext): self.story_id = i_story_id self.category_id = i_category_id self.story_name = i_story_name self.story_description = i_story_description self.created_date = i_created_date self.updated_date = i_updated_date self.created_by = i_created_by self.updated_by = i_updated_by self.total_view = i_total_view self.average_rating = i_average_rating self.writer = i_obj_writer self.storytext = i_obj_storytext def json(self): return { "story_id": self.story_id, "category_id": self.category_id, "story_name": self.story_name, "story_details": self.story_description, "storytext_id": self.story_text_id, "writer_id": self.writer.user_id, "writer_name": self.writer.writer_first_name + " " + self.writer.writer_last_name, "created_date": self.created_date.strftime("%Y-%m-%d %H:%M:%S") if self.created_date is not None else None, "updated_date": self.updated_date.strftime("%Y-%m-%d %H:%M:%S") if self.updated_date is not None else None, "created_by": self.created_by, "updated_by": self.updated_by, "view": self.total_view, "rating": self.average_rating } @classmethod def get_story_list_by_writer(self, in_writer_id): try: print(in_writer_id) writer = WriterModel.query.filter_by( writer_id=in_writer_id).first() return writer.stories except: print("exception occured model") @classmethod def get_story_by_id(self, in_story_id): try: story = self.query.filter_by(story_id=in_story_id).first() return story except: print("exception occured") @classmethod def get_story_by_name(self, in_story_name, in_category_id): try: story = self.query.filter_by(story_name=in_story_name).filter_by( category_id=in_category_id).first() return story except: print("exception occured") @classmethod def get_story_list_by_category(self, in_category_id): try: stories = self.query.filter( self.category_id == in_category_id).all() return stories except: print("exception occured model") @classmethod def get_story_list_by_trend(self): try: stories = self.query.all() return stories except: print("exception occured") @classmethod def get_writer_list(self): try: writers = WriterModel.query.all() return writers except: print("exception occured") @classmethod def get_if_user_is_writer(self, in_story_id, in_user_id): try: iswriter = self.query.filter_by(story_id=in_story_id).filter_by( writer_id=in_user_id).first() if iswriter: return True else: return False except: print("exception occured") @classmethod def get_writer_name(self, in_story_id): try: story = self.query.filter_by(story_id=in_story_id).first() return story.writer except: print("exception occured") @classmethod def create_story(self, in_category_id, in_story_name, in_story_description, in_story, in_writer_userid, in_created_by): #need to inlude story text model try: obj_writer = WriterModel.get_writer_by_userid(in_writer_userid) obj_storytext = StoryTextModel.create_storytext(in_story) new_story = self(i_story_id=None, i_category_id=in_category_id, i_story_name=in_story_name, i_story_description=in_story_description, i_created_date=datetime.datetime.now(), i_created_by=in_created_by, i_total_view=0, i_average_rating=0, i_updated_date=None, i_updated_by=None, i_obj_writer=obj_writer, i_obj_storytext=obj_storytext) db.session.add(new_story) db.session.commit() except: print("exception occured story model") @classmethod def update_story(self, in_story_id, in_category_id, in_story_name, in_story_description, in_storytext, in_writer_userid, in_updated_by): try: existing_story = self.get_story_by_id( in_story_id ) # reusing the "get_storydetails_by_id" function of this class if in_category_id is not None: existing_story.category_id = in_category_id if in_story_description is not None: existing_story.story_description = in_story_description if in_story_name is not None: existing_story.story_name = in_story_name if in_writer_userid is not None: obj_writer = WriterModel.get_writer_by_userid(in_writer_userid) if obj_writer: existing_story.writer = obj_writer if in_storytext is not None: StoryTextModel.update_storytext(existing_story.story_text_id, in_storytext) if in_updated_by is not None: existing_story.updated_by = in_updated_by existing_story.updated_date = datetime.datetime.now() db.session.commit() except: print("exception occured") @classmethod def delete_story_by_id(self, in_story_id): try: story = self.get_story_by_id(in_story_id) self.query.filter_by(story_id=in_story_id).delete() StoryTextModel.delete_story_by_id(story.story_text_id) except: print("exception occured") finally: db.session.commit()
class RatingModel(db.Model): __table_args__ = {"schema": "sch_storyboard"} __tablename__ = "tbl_rating" rating_id = db.Column(db.Integer, primary_key=True) rating_number = db.Column(db.Integer) story_id = db.Column(db.Integer) writer_id = db.Column(db.Integer) rating = db.Column(db.Float) def __init__(self, i_rating_id, i_rating_number, i_story_id, i_writer_id, i_rating): self.rating_id = i_rating_id self.rating_number = i_rating_number self.story_id = i_story_id self.writer_id = i_writer_id self.rating = i_rating def json(self): return { "rating_id": self.rating_id, "rating_number": self.rating_number, "story_id": self.story_id, "writer_id": self.writer_id, "rating": self.rating } @classmethod def get_rating_by_story(self, in_story_id): try: rating = self.query.filter_by(story_id=in_story_id).first( ) # flask sqlalchemy requires self.column name to evaluate "!="" condition return rating except: print("exception occured") @classmethod def get_rating_by_writer(self, in_writer_id): try: rating = self.query.filter_by(writer_id=in_writer_id).all() return rating except: print("exception occured") @classmethod def get_rating_list_by_story(self): #need to be changed try: rating = self.query.all() return rating except: print("exception occured") @classmethod def create_rating( self, in_story_id, in_writer_id ): #if the writer is the viewer then ignore,logic has been implemented in parent method. try: new_rating = self( None, 1, in_story_id, in_writer_id, 0.001 ) #calculating average by division. So putting 0 on numerator is avoided db.session.add(new_rating) except: print("exception occured") finally: db.session.commit() @classmethod def update_rating(self, in_story_id, in_rating, in_user_rating=0 ): #if user is writer then don't increament the rating try: new_rating = self.get_rating_by_story( in_story_id ) # reusing the "get_ratingdetails_by_id" function of this class new_rating.rating_number = new_rating.rating_number + 1 new_rating.rating = ( (new_rating.rating * (new_rating.rating_number - 1) - in_user_rating) + in_rating) / new_rating.rating_number print(new_rating.rating) print(new_rating.rating_number) except: print("exception occured") finally: db.session.commit( ) ##the db session is getting commited in the calling method in ViewerRatingModel @classmethod def delete_rating_by_story(self, in_story_id): try: self.query.filter_by(story_id=in_story_id).delete( ) # flask sqlalchemy requires self.column name to evaluate "!="" condition except: print("exception occured") finally: db.session.commit()
class StoryTextModel(db.Model): __table_args__ = {"schema": "sch_storyboard"} __tablename__ = "tbl_storytext" story_text_id = db.Column(db.Integer, primary_key=True) story_text = db.Column(db.Text) story_table = db.relationship("StoryModel", backref='storytext') def __init__(self, i_story_text_id, i_story_text): self.story_text_id = i_story_text_id self.story_text = i_story_text def json(self): return { "storytextid": self.story_text_id, "storytext": self.story_text } @classmethod def get_storytext_by_id(self, in_storytext_id): try: storytext = self.query.filter_by( story_text_id=in_storytext_id).first() return storytext except: print("exception occured storytext model") @classmethod def create_storytext(self, in_storytext): #need to inlude story text model try: new_storytext = self(i_story_text_id=None, i_story_text=in_storytext) db.session.add(new_storytext) db.session.commit() storytext = self.query.order_by(self.story_text_id.desc()).first() return storytext except: print("exception occured") @classmethod def update_storytext(self, in_storytext_id, in_storytext): try: existing_storytext = self.get_storytext_by_id( in_storytext_id ) # reusing the "get_storydetails_by_id" function of this class if in_storytext is not None: existing_storytext.story_text = in_storytext except: print("exception occured") finally: db.session.commit() @classmethod def delete_story_by_id(self, in_storytext_id): try: self.query.filter_by(story_text_id=in_storytext_id).delete() except: print("exception occured") finally: db.session.commit()
class UserModel(db.Model): __table_args__ = {"schema": "sch_storyboard"} __tablename__ = "tbl_user" user_id = db.Column(db.Integer, primary_key=True) user_first_name = db.Column(db.String) user_last_name = db.Column(db.String) writer_alt_name = db.Column(db.String) user_phone_number_1 = db.Column(db.String) user_phone_number_2 = db.Column(db.String) email_id = db.Column(db.String) is_writer = db.Column(db.Boolean) password = db.Column(db.String) secret_question_1 = db.Column(db.String) secret_question_2 = db.Column(db.String) secret_question_3 = db.Column(db.String) def __init__(self, i_user_id, i_user_first_name, i_user_last_name, i_writer_alt_name, i_user_phone_number_1, i_user_phone_number_2, i_email_id, i_is_writer, i_password, i_secret_question_1, i_secret_question_2, i_secret_question_3): self.user_id = i_user_id self.user_first_name = i_user_first_name self.user_last_name = i_user_last_name self.writer_alt_name = i_writer_alt_name self.user_phone_number_1 = i_user_phone_number_1 self.user_phone_number_2 = i_user_phone_number_2 self.email_id = i_email_id self.is_writer = i_is_writer self.password = i_password self.secret_question_1 = i_secret_question_1 self.secret_question_2 = i_secret_question_2 self.secret_question_3 = i_secret_question_3 def json(self): return { "userid": self.user_id, "first_name": self.user_first_name, "last_name": self.user_last_name, "writer_altname": self.writer_alt_name, "user_phone_number": self.user_phone_number_1, "user_altphone_number": self.user_phone_number_2, "emailid": self.email_id, "writer": self.is_writer } @classmethod def authenticate_user( self, in_user_id=None, in_password=None): # or condition nto be added for mail id try: #print(in_password) user = self.query.filter_by(user_id=in_user_id).filter_by( password=in_password ).first( ) # flask sqlalchemy requires self.column name to evaluate "!="" condition return user except: print("exception occured") @classmethod def get_user_by_id(self, in_user_id): try: user = self.query.filter_by(user_id=in_user_id).first( ) # flask sqlalchemy requires self.column name to evaluate "!="" condition return user except: print("exception occured") @classmethod def get_user_by_email_phone(self, in_user_phone_number=None ): # or condition nto be added for mail id try: user = self.query.filter_by( user_phone_number_1=in_user_phone_number).first() return user except: print("exception occured") @classmethod def create_user( self, in_first_name, in_last_name, in_writer_alt_name, in_user_phone_number_1, in_user_phone_number_2, in_email_id, in_is_writer, in_password, i_secret_question_1, i_secret_question_2, i_secret_question_3 ): #if the writer is the userer then ignore,logic has been implemented in parent method. try: new_user = self(None, in_first_name, in_last_name, in_writer_alt_name, in_user_phone_number_1, in_user_phone_number_2, in_email_id, in_is_writer, in_password.strip(), i_secret_question_1, i_secret_question_2, i_secret_question_3) db.session.add(new_user) except: print("exception occured") finally: db.session.commit() @classmethod def update_user(self, in_user_id, in_first_name, in_last_name, in_writer_alt_name, in_user_phone_number_1, in_user_phone_number_2, in_email_id, in_is_writer, in_new_password, in_password, i_secret_question_1, i_secret_question_2, i_secret_question_3 ): #if user is writer then don't increament the user try: update_user = self.get_user_by_id( in_user_id ) # reusing the "get_user_by_story" function of this class if update_user: if in_first_name: update_user.user_first_name = in_first_name if in_last_name: update_user.user_last_name = in_last_name if in_writer_alt_name: update_user.writer_alt_name = in_writer_alt_name if in_user_phone_number_1: update_user.user_phone_number_1 = in_user_phone_number_1 if in_user_phone_number_2: update_user.user_phone_number_2 = in_user_phone_number_2 if in_email_id: update_user.email_id = in_email_id if in_is_writer: update_user.is_writer = in_is_writer if (in_new_password and update_user.secret_question_1 == i_secret_question_1 and update_user.secret_question_2 == i_secret_question_2 and update_user.secret_question_3 == i_secret_question_3): update_user.password = in_new_password.strip() if in_new_password: if (update_user.password.strip() == in_password.strip()): update_user.password = in_new_password.strip() except: print("exception ocuured") finally: db.session.commit() @classmethod def delete_user_by_id(self, in_user_id): try: self.query.filter_by(user_id=in_user_id).delete() except: print("exception occured") finally: db.session.commit()
class CategoryModel(db.Model): __table_args__ = {"schema":"sch_storyboard"} __tablename__ = "tbl_category" category_id = db.Column( db.Integer , primary_key = True ) category_name = db.Column( db.String(100) ) category_description = db.Column( db.String(100) ) created_date = db.Column( db.DateTime ) updated_date = db.Column( db.DateTime ) created_by = db.Column( db.Integer ) updated_by = db.Column( db.Integer ) def __init__(self,i_category_id,i_category_name,i_category_description,i_created_date,i_updated_date,i_created_by,i_updated_by): self.category_id = i_category_id self.category_name = i_category_name self.category_description = i_category_description self.created_date = i_created_date self.updated_date = i_updated_date self.created_by = i_created_by self.updated_by = i_updated_by def json(self): return { 'category_id' : self.category_id if self.category_id else None, 'category_name' : self.category_name if self.category_name else None, 'category_description' : self.category_description if self.category_description else None, 'created_date' : self.created_date.strftime("%Y-%m-%d %H:%M:%S") if self.created_date else None, 'updated_date' : self.updated_date.strftime("%Y-%m-%d %H:%M:%S") if self.updated_date else None, 'created_by' : self.created_by if self.created_by else None, 'updated_by' : self.updated_by if self.updated_by else None, 'selected' : False #this is used in front end. to reduce mobile app resource usage it is sent from api } @classmethod def get_categorydetails_by_name(self,in_category_name): try: category = self.query.filter_by(category_name = in_category_name).first() return category except: print("exception occured") @classmethod def get_categorydetails_by_id(self,in_category_id): try: category = self.query.filter_by(category_id=in_category_id).first() return category except: print("exception occured") @classmethod def get_category_list(self): try: categorylist = self.query.all() return categorylist except: print("exception occured") @classmethod def create_category(self,in_category_name,in_category_description,in_created_by): try: new_category = self(None,in_category_name,in_category_description,datetime.datetime.now(),None,in_created_by,None) db.session.add(new_category) except: print("exception occured") finally: db.session.commit() @classmethod def update_category_details(self,in_category_id,in_category_name,in_category_description,in_updated_by): try: existing_category = self.get_categorydetails_by_id(in_category_id) # reusing the "get_categorydetails_by_id" function of this class if in_category_name is not None: existing_category.category_name = in_category_name if in_category_description is not None: existing_category.category_description = in_category_description if in_updated_by is not None: existing_category.updated_by = in_updated_by existing_category.updated_date = datetime.datetime.now() except: print("exception occured") finally: db.session.commit() @classmethod def delete_category_by_id(self,in_category_id): try: self.query.filter_by(category_id=in_category_id).delete() db.session.commit() except: print("exception occured")