from flask_sqlalchemy import SQLAlchemy from sqlalchemy.orm import relationship, column_property 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 ProjectStatus(db.Model, Formable): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100)) # add keys to import/export export_keys = ['name'] class Project(db.Model, Formable): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, unique=True) project_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: """ super(Project, self).from_request(form_request) self.project_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.project_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.project_labels] _struct['category_labels'] = {} for _c in Category.query.all(): category_labels = [] for _l in self.project_labels: if _l.label in [_cl.label for _cl in _c.category_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="project_labels") label = relationship("Label", back_populates="project_labels") class Label(db.Model, Formable): 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')) project_labels = relationship("ProjectLabel", back_populates="label") category_labels = relationship("CategoryLabel", back_populates="label") export_keys = ['name'] def from_request(self, form_request): """ overide parent method to deal with category labels :param form_request: :return: """ super(Label, self).from_request(form_request) self.category_labels.clear() for _c_id in form_request.form.getlist("categories"): n_c = Category.query.get(int(_c_id)) _cl = CategoryLabel(category=n_c, label=self) self.category_labels.append(_cl) def to_struct(self): """ overide parent method to include one key: categories Mainly we add the 'categories' element containing list of categories names :return: """ _struct = super(Label, self).to_struct() _struct['categories'] = [_c.name for _c in self.categories] return _struct @property def categories(self): """ :return: list of categories linked to that label """ return [_cl.category for _cl in self.category_labels] 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="category_labels") label = relationship("Label", back_populates="category_labels") class Category(db.Model, Formable): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, unique=True) category_labels = relationship("CategoryLabel", back_populates="category") export_keys = ['name'] def from_request(self, form_request): """ overide parent method to deal with category labels :param form_request: :return: """ super(Category, self).from_request(form_request) self.category_labels.clear() for _l_id in form_request.form.getlist("labels"): n_c = Label.query.get(int(_l_id)) _cl = CategoryLabel(label=n_c, category=self) self.category_labels.append(_cl) def to_struct(self): """ overide parent method to include one key: labels Mainly we add the 'labels' element containing list of labels names :return: """ _struct = super(Category, self).to_struct() _struct['labels'] = [_cl.label.name for _cl in self.category_labels] return _struct @property def labels(self): """ :return: list of labels linked to that category """ return [_cl.label for _cl in self.category_labels] # # History # class AgentHistory(db.Model): id = db.Column(db.Integer, primary_key=True) period_id = db.Column(db.Integer, db.ForeignKey('period.id')) agent_id = db.Column(db.Integer, db.ForeignKey('agent.id')) status_id = db.Column(db.Integer, db.ForeignKey('agent_status.id')) # # Agents # class EmploymentType(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100)) bap_code = db.Column(db.String(1)) bap_name = db.Column(db.String(100)) pro_family = db.Column(db.String(100)) pro_type_code = db.Column(db.String(5)) status = db.Column(db.String(50)) old_type_code = db.Column(db.String(5)) old_name = db.Column(db.String(100)) class AgentSkill(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 AgentResponsability(db.Model, Formable): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), unique=True) abbr = db.Column(db.String(50), unique=True) export_keys = ['name'] class AgentBap(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(16), unique=True) agents = relationship("Agent", back_populates="bap") class AgentGrade(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(16), unique=True) agents = relationship("Agent", back_populates="grade") class AgentStatus(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(16), unique=True) agents = relationship("Agent", back_populates="status") class Company(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(16), unique=True) 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") name = column_property(firstname + " " + secondname) @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, Formable): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), unique=True) export_keys = ['name'] 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'] def from_request(self, form_request): """ overide parent method to deal with category labels :param form_request: :return: """ super(Charge, self).from_request(form_request) # convert the charge_rate from etp float to integer percent self.charge_rate = 100 * float(self.charge_rate) print(self.charge_rate)