from flask_sqlalchemy import SQLAlchemy from sqlalchemy.orm import relationship db = SQLAlchemy() # # # class Formable: """ Parent class allowing some html form facilities """ export_keys = [] def from_request(self, form_request): """ Get a form request structure and fill in our fields :param form_request: :return: """ for key in self.export_keys: setattr(self, key, form_request.form.get(key)) def to_struct(self): """ Export the orm object to a structure easily used in jinja :return: nothing """ _struct = {'id': self.id} for key in self.export_keys: _value = getattr(self, key) _struct[key] = '' if _value is None else _value return _struct # # Categorized projects # # There is one label list, # each label belongs to one or more categories. # # The projects are labelled by one or more label. # # Thus this is modeled with classes # Project, Label and Category # And many_to_many association are done through # ProjectLabel and CategoryLabel # class Project(db.Model, Formable): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String) labels = relationship("ProjectLabel", back_populates="project") # add keys to import/export export_keys = ['name'] def from_request(self, form_request): """ overide parent method to deal with category labels :param form_request: :return: """ struct = super(Project, self).from_request(form_request) self.labels.clear() form_labels = [] for _c in Category.query.all(): form_labels = form_labels + form_request.form.getlist(_c.name) for label_id in form_labels: n_l = Label.query.get(int(label_id)) n_pl = ProjectLabel(project=self, label=n_l) self.labels.append(n_pl) def to_struct(self): """ overide parent method to include one key: agent.fullname Mainly we add the 'labels' element containing list of label names and we add the 'category_labels' element containing a dict where category key contains labels for that project. :return: """ _struct = super(Project, self).to_struct() _struct['labels'] = [_l.label.name for _l in self.labels] _struct['category_labels'] = {} for _c in Category.query.all(): category_labels = [] for _l in self.labels: if _l.label in [_cl.label for _cl in _c.labels]: category_labels.append(_l.label.name) _struct['category_labels'][_c.name] = category_labels return _struct class ProjectLabel(db.Model): """ Labelling projects. On project can have many labels. And one label will be set to many projects """ id = db.Column(db.Integer, primary_key=True) project_id = db.Column(db.Integer, db.ForeignKey('project.id')) label_id = db.Column(db.Integer, db.ForeignKey('label.id')) project = relationship("Project", back_populates="labels") label = relationship("Label", back_populates="projects") class Label(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, unique=True) category_id = db.Column(db.Integer, db.ForeignKey('category.id')) projects = relationship("ProjectLabel", back_populates="label") categories = relationship("CategoryLabel", back_populates="label") class CategoryLabel(db.Model): """ Categorizing labels: one label can be added to many categories one category hosts many labels """ id = db.Column(db.Integer, primary_key=True) category_id = db.Column(db.Integer, db.ForeignKey('category.id')) label_id = db.Column(db.Integer, db.ForeignKey('label.id')) category = relationship("Category", back_populates="labels") label = relationship("Label", back_populates="categories") class Category(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String) labels = relationship("Label", back_populates="category") labels = relationship("CategoryLabel", back_populates="category") # # Agents # class AgentBap(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(16)) agents = relationship("Agent", back_populates="bap") class AgentGrade(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(16)) agents = relationship("Agent", back_populates="grade") class AgentStatus(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(16)) agents = relationship("Agent", back_populates="status") class Company(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(16)) agents = relationship("Agent", back_populates="company") class Agent(db.Model, Formable): id = db.Column(db.Integer, primary_key=True) firstname = db.Column(db.String(100)) secondname = db.Column(db.String(100)) virtual = db.Column(db.Integer) # integer boolean permanent = db.Column(db.Integer) # integer boolean company_id = db.Column(db.Integer, db.ForeignKey('company.id')) grade_id = db.Column(db.Integer, db.ForeignKey('agent_grade.id')) status_id = db.Column(db.Integer, db.ForeignKey('agent_status.id')) bap_id = db.Column(db.Integer, db.ForeignKey('agent_bap.id')) grade = relationship("AgentGrade", back_populates="agents") bap = relationship("AgentBap", back_populates="agents") status = relationship("AgentStatus", back_populates="agents") company = relationship("Company", back_populates="agents") @property def fullname(self): return f"{self.secondname} {self.firstname}" @property def namefull(self): return f"{self.firstname} {self.secondname}" # has to be set as we inherit Formable # export_keys = ['firstname', 'secondname', 'virtual', 'permanent', 'company_id', 'status_id', 'grade_id', 'bap_id'] def to_struct(self): """ overide parent method to include one key: agent.fullname :return: """ _struct = super(Agent, self).to_struct() _struct['fullname'] = self.fullname return _struct class Service(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), unique=True) abbr = db.Column(db.String(50), unique=True) class Capacity(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), unique=True) class Period(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), unique=True) num_months = db.Column(db.Integer) class Charge(db.Model, Formable): id = db.Column(db.Integer, primary_key=True) agent_id = db.Column(db.Integer, db.ForeignKey('agent.id')) project_id = db.Column(db.Integer, db.ForeignKey('project.id')) service_id = db.Column(db.Integer, db.ForeignKey('service.id')) capacity_id = db.Column(db.Integer, db.ForeignKey('capacity.id')) period_id = db.Column(db.Integer, db.ForeignKey('period.id')) charge_rate = db.Column(db.Integer) # Overwrite Formable default to fit our own members # export_keys = ['agent_id', 'project_id', 'service_id', 'capacity_id', 'period_id', 'charge_rate']