class LancamentoHorasModel(db.Model): __tablename__ = "lancamento_horas" lancamento_horas_id = db.Column(db.Integer, primary_key=True) projeto_id = db.Column(db.Integer, db.ForeignKey(ProjetoModel.projeto_id)) cliente_id = db.Column(db.Integer, db.ForeignKey(ClienteModel.cliente_id)) usuario_id = db.Column(db.Integer, db.ForeignKey(UsuarioModel.usuario_id)) categoria_id = db.Column(db.Integer, db.ForeignKey(CategoriaModel.categoria_id)) data_inicio = db.Column(db.Date()) data_fim = db.Column(db.Date()) descricao = db.Column(db.String(5000)) criado_por = db.Column(db.String(100)) data_criacao = db.Column(db.Date()) atualizado_por = db.Column(db.String(100)) data_ultima_atualizacao = db.Column(db.Date()) ativo = db.Column(db.Integer) def __init__(self, lancamento_horas_id, projeto_id, cliente_id, usuario_id, categoria_id, data_inicio, data_fim, descricao, criado_por, data_criacao, atualizado_por, data_ultima_atualizacao, ativo): self.lancamento_horas_id = lancamento_horas_id self.projeto_id = projeto_id self.cliente_id = cliente_id self.usuario_id = usuario_id self.categoria_id = categoria_id self.data_inicio = data_inicio self.data_fim = data_fim self.descricao = descricao self.criado_por = criado_por self.data_criacao = data_criacao self.atualizado_por = atualizado_por self.data_ultima_atualizacao = data_ultima_atualizacao self.ativo = ativo def json(self): data_inicio = self.data_inicio.strftime('%Y-%m-%d %H:%M:%S') if self.data_inicio else "" data_fim = self.data_fim.strftime('%Y-%m-%d %H:%M:%S') if self.data_fim else "" return { "lancamento_horas_id": self.lancamento_horas_id, "projeto_id": self.projeto_id, "cliente_id": self.cliente_id, "usuario_id": self.usuario_id, "categoria_id": self.categoria_id, "data_inicio": data_inicio, "data_fim": data_fim, "descricao": self.descricao, "criado_por": self.criado_por, "data_criacao": self.data_criacao.strftime('%Y-%m-%d %H:%M:%S'), "atualizado_por": self.atualizado_por, "data_ultima_atualizacao": self.data_ultima_atualizacao.strftime('%Y-%m-%d %H:%M:%S'), } @classmethod def find_by_id(cls, lancamento_horas_id): return LancamentoHorasModel.query.filter_by(lancamento_horas_id=lancamento_horas_id,ativo=1).first() def save_to_db(self): db.session.add(self) db.session.commit()
class Payslip(db.Model): """ Payslip info """ __tablename__ = 'payslips' # Payslip details id = db.Column(db.Integer, primary_key=True) payment_period_start = db.Column(db.Date()) payment_period_end = db.Column(db.Date()) gross_income = db.Column(db.Numeric()) income_tax = db.Column(db.Numeric()) net_income = db.Column(db.Numeric()) super_amount = db.Column(db.Numeric()) # Foreign keys employee_id = db.Column(db.Integer, db.ForeignKey('employees.id')) def __init__(self, name, price, store_id): self.name = name self.price = price self.store_id = store_id def to_json(self): """ Convert Employee instance to JSON format """ return { 'firstName': self.user.first_name, 'lastName': self.user.last_name, 'payment_period_start': self.payment_period_start, 'payment_period_end': self.payment_period_end, 'gross_income': self.gross_income, 'income_tax': self.income_tax, 'net_income': self.net_income, 'super_amount': self.super_amount } def to_csv(self): """ Convert Employee instance to CSV format """ pass def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class PerfilModel(db.Model): __tablename__ = "perfil" perfil_id = db.Column(db.Integer, primary_key=True) nome = db.Column(db.String(100)) criado_por = db.Column(db.String(100)) data_criacao = db.Column(db.Date()) atualizado_por = db.Column(db.String(100)) data_ultima_atualizacao = db.Column(db.Date()) ativo = db.Column(db.Integer) def __init__(self, perfil_id, nome, criado_por, data_criacao, atualizado_por, data_ultima_atualizacao, ativo): self.perfil_id = perfil_id self.nome = nome self.criado_por = criado_por self.data_criacao = data_criacao self.atualizado_por = atualizado_por self.data_ultima_atualizacao = data_ultima_atualizacao self.ativo = ativo def json(self): return { "perfil_id": self.perfil_id, "nome": self.nome, "criado_por": self.criado_por, "data_criacao": self.data_criacao.strftime('%Y-%m-%d %H:%M:%S'), "atualizado_por": self.atualizado_por, "data_ultima_atualizacao": self.data_ultima_atualizacao.strftime('%Y-%m-%d %H:%M:%S'), } @classmethod def find_by_id(cls, perfil_id): return PerfilModel.query.filter_by(perfil_id=perfil_id, ativo=1).first() @classmethod def find_by_name(cls, nome): return PerfilModel.query.filter_by(nome=nome, ativo=1).first() def save_to_db(self): db.session.add(self) db.session.commit()
class MembrosSquadModel(db.Model): __tablename__ = "membros_squad" membros_squad_id = db.Column(db.Integer, primary_key=True) squad_id = db.Column(db.Integer, db.ForeignKey(SquadModel.squad_id)) usuario_id = db.Column(db.Integer, db.ForeignKey(UsuarioModel.usuario_id)) criado_por = db.Column(db.String(100)) data_criacao = db.Column(db.Date()) atualizado_por = db.Column(db.String(100)) data_ultima_atualizacao = db.Column(db.Date()) ativo = db.Column(db.Integer) def __init__(self, membros_squad_id, squad_id, usuario_id, criado_por, data_criacao, atualizado_por, data_ultima_atualizacao, ativo): self.membros_squad_id = membros_squad_id self.squad_id = squad_id self.usuario_id = usuario_id self.criado_por = criado_por self.data_criacao = data_criacao self.atualizado_por = atualizado_por self.data_ultima_atualizacao = data_ultima_atualizacao self.ativo = ativo def json(self): return { "membros_squad_id": self.membros_squad_id, "squad_id": self.squad_id, "usuario_id": self.usuario_id, "criado_por": self.criado_por, "data_criacao": self.data_criacao.strftime('%Y-%m-%d %H:%M:%S'), "atualizado_por": self.atualizado_por, "data_ultima_atualizacao": self.data_ultima_atualizacao.strftime('%Y-%m-%d %H:%M:%S'), } @classmethod def find_by_id(cls, membros_squad_id): return MembrosSquadModel.query.filter_by( membros_squad_id=membros_squad_id, ativo=1).first() def save_to_db(self): db.session.add(self) db.session.commit()
class UserDetails(db.Model): __tablename__ = 'user_details' id = db.Column(db.Integer, primary_key=True) adress = db.Column(db.String(50)) phone_number = db.Column(db.String(50)) postal_code = db.Column(db.String(50)) birth_date = db.Date() gender = db.Column(db.String(1)) # samo m/f ? avatar = db.Column(db.String(25)) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) updated_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) def __init__(self, adress, phone_number, postal_code, birth_date, gender, avatar, created_at, updated_at): self.adress = adress self.phone_number = phone_number self.postal_code = postal_code self.birth_date = birth_date self.gender = gender self.avatar = avatar self.created_at = created_at self.updated_at = updated_at def save_to_db(self): db.session.add(self) db.session.commit()
class NotificationModel(db.Model): __tablename__ = 'notifications' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) message = db.Column(db.String(160)) date = db.Column(db.Date()) def __repr__(self): return f"Notification ({self.message}, {self.date})" def __init__(self, message, date): self.message = message self.date = date @classmethod def find_by_message(cls, message): return cls.query.filter_by(name=message).all() @classmethod def find_by_date(cls, date): return cls.query.filter_by(date=date).all() @classmethod def find_by_user_id(cls, user_id): return cls.query.filter_by(user=user_id).all()
class Clown(db.Model): __tablename__ = "clowns" guild_id = db.Column(db.Numeric, primary_key=True) clown_id = db.Column(db.Numeric) previous_clown_id = db.Column(db.Numeric) nomination_date = db.Column(db.Date(), server_default=db.func.now()) join_time = db.Column(db.DateTime)
class Link(db.Model): id = db.Column(db.Integer, primary_key=True, autoincrement=True) entered_link = db.Column(db.String(2048)) generated_hash = db.Column(db.String(50)) generated_day = db.Column(db.Date(), default=current_date(), nullable=False) hash_lifetime = db.Column(db.Integer(), default=90, nullable=False)
class ProjetoModel(db.Model): __tablename__ = "projeto" projeto_id = db.Column(db.Integer, primary_key=True) nome = db.Column(db.String(100)) cliente_id = db.Column(db.Integer, db.ForeignKey(ClienteModel.cliente_id)) squad_id = db.Column(db.Integer, db.ForeignKey(SquadModel.squad_id)) criado_por = db.Column(db.String(100)) data_criacao = db.Column(db.Date()) atualizado_por = db.Column(db.String(100)) data_ultima_atualizacao = db.Column(db.Date()) ativo = db.Column(db.Integer) def __init__(self, projeto_id, nome, cliente_id, squad_id, criado_por, data_criacao, atualizado_por, data_ultima_atualizacao, ativo): self.projeto_id = projeto_id self.nome = nome self.cliente_id = cliente_id self.squad_id = squad_id self.criado_por = criado_por self.data_criacao = data_criacao self.atualizado_por = atualizado_por self.data_ultima_atualizacao = data_ultima_atualizacao self.ativo = ativo def json(self): return { "projeto_id": self.projeto_id, "nome": self.nome, "cliente_id": self.cliente_id, "squad_id": self.squad_id, "criado_por": self.criado_por, "data_criacao": self.data_criacao.strftime('%Y-%m-%d %H:%M:%S'), "atualizado_por": self.atualizado_por, "data_ultima_atualizacao": self.data_ultima_atualizacao.strftime('%Y-%m-%d %H:%M:%S'), } @classmethod def find_by_id(cls, projeto_id): return ProjetoModel.query.filter_by(projeto_id=projeto_id,ativo=1).first() def save_to_db(self): db.session.add(self) db.session.commit()
class VenteModel(db.Model): __tablename__ = 'ventes' id = db.Column(db.Integer, primary_key=True) date_de_vente = db.Column(db.Date(), nullable=False) produit_id = db.Column(db.Integer, db.ForeignKey('produits.id')) produit = db.relationship('ProduitModel') commercial_id = db.Column(db.Integer, db.ForeignKey('commercials.id')) commercial = db.relationship('CommercialModel') client_id = db.Column(db.Integer, db.ForeignKey('clients.id')) client = db.relationship('ClientModel') user_id = db.Column(db.Integer, db.ForeignKey('users.id')) user = db.relationship('UserModel') def __init__(self, date_de_vente, produit_id, commercial_id, client_id, user_id): self.date_de_vente = date_de_vente self.produit_id = produit_id self.commercial_id = commercial_id self.client_id = client_id self.user_id = user_id def json(self): return { 'id': self.id, 'date_de_vente': self.date_de_vente.strftime('%Y-%m-%d'), 'produit_id': self.produit_id, 'commercial_id': self.commercial_id, 'client_id': self.client_id } @classmethod def find_by_id(cls, _id): return cls.query.filter_by(id=_id).first() @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id).first() def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class Users(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True, autoincrement=True) username = db.Column(db.String(16)) role_id = db.Column(db.Integer) password = db.Column(db.String(16)) first_name = db.Column(db.String(16)) last_name = db.Column(db.String(16)) father_name = db.Column(db.String(20)) roll_no = db.Column(db.Integer) class_no = db.Column(db.String(11)) address = db.Column(db.String(40)) section = db.Column(db.String(100)) admission_date = db.Column(db.Date()) is_active = db.Column(db.Boolean()) is_delete = db.Column(db.Boolean()) modified_datetime = db.Column(db.DateTime(), nullable=False)
class PatientModel(BaseModel): __tablename__ = 'patient' id = db.Column(db.Integer, primary_key=True) first_name = db.Column(db.String(250), nullable=False) last_name = db.Column(db.String(250), nullable=False) date_of_birth = db.Column(db.Date(), nullable=False) passport = db.Column(db.String(80), nullable=False, unique=True) insurance_number = db.Column(db.String(80)) age = db.Column(db.Integer()) sex = db.Column(db.String(20), nullable=False) medical_condition = db.Column(db.String(512)) email = db.Column(db.String(250)) address_1 = db.Column(db.String(250)) address_2 = db.Column(db.String(250)) city = db.Column(db.String(80)) state = db.Column(db.String(80)) zip_code = db.Column(db.String(20)) country = db.Column(db.String(80)) phone = db.Column(db.String(80)) created_at_utc = db.Column(db.DateTime, nullable=False, default=datetime.utcnow()) clinic_id = db.Column(db.Integer, db.ForeignKey('clinic.id'), nullable=False) clinic = db.relationship('ClinicModel', backref='PatientModel', lazy=True) @staticmethod def parent_id_exists(path_id): """Check if clinic with id=path_id exists in the DB""" if not ClinicModel.find_by_id(path_id): raise ValidationError( 'Clinic with id={} does not exist.'.format(path_id)) def set_parent_id(self, parent_id): self.clinic_id = parent_id @classmethod def get_from_db(cls, parent_field=None, parent_id=None, self_id=None): return super().get_from_db(cls.clinic_id, parent_id, self_id)
class UsuarioModel(db.Model): __tablename__ = "usuario" usuario_id = db.Column(db.Integer, primary_key=True) nome = db.Column(db.String(100)) telefone = db.Column(db.String(12)) email = db.Column(db.String(100)) password = db.Column(db.String(100)) perfil_id = db.Column(db.Integer, db.ForeignKey(PerfilModel.perfil_id)) valor_hora = db.Column(db.Float(precision=2)) timezone = db.Column(db.String(5)) ultimo_login = db.Column(db.Date()) confirmado = db.Column(db.Integer) criado_por = db.Column(db.String(100)) data_criacao = db.Column(db.Date()) atualizado_por = db.Column(db.String(100)) data_ultima_atualizacao = db.Column(db.Date()) ativo = db.Column(db.Integer) def __init__(self, usuario_id, nome, telefone, email, password, perfil_id, valor_hora, timezone, ultimo_login, confirmado, criado_por, data_criacao, atualizado_por, data_ultima_atualizacao, ativo): self.usuario_id = usuario_id self.nome = nome self.telefone = telefone self.email = email self.password = password self.perfil_id = perfil_id self.valor_hora = valor_hora self.timezone = timezone self.ultimo_login = ultimo_login self.confirmado = confirmado self.criado_por = criado_por self.data_criacao = data_criacao self.atualizado_por = atualizado_por self.data_ultima_atualizacao = data_ultima_atualizacao self.ativo = ativo def json(self): return { "usuario_id": self.usuario_id, "nome": self.nome, "telefone": self.telefone, "email": self.email, "password": self.password, "perfil_id": self.perfil_id, "valor_hora": self.valor_hora, "timezone": self.timezone, "ultimo_login": self.ultimo_login.strftime('%Y-%m-%d %H:%M:%S'), "criado_por": self.criado_por, "data_criacao": self.data_criacao.strftime('%Y-%m-%d %H:%M:%S'), "atualizado_por": self.atualizado_por, "data_ultima_atualizacao": self.data_ultima_atualizacao.strftime('%Y-%m-%d %H:%M:%S'), } @classmethod def find_by_email(cls, email): return UsuarioModel.query.filter_by(email=email, ativo=1).first() @classmethod def find_by_id(cls, usuario_id): return UsuarioModel.query.filter_by(usuario_id=usuario_id, ativo=1).first() @classmethod def update_last_login(cls, usuario_id): user = UsuarioModel.find_by_id(usuario_id) if user: user.ultimo_login = datetime.datetime.utcnow().strftime( '%Y-%m-%d %H:%M:%S') user.save_to_db() def save_to_db(self): db.session.add(self) db.session.commit()
class OcorrenciaModel(db.Model): __tablename__ = 'ocorrencia' numeroOcorrencia = db.Column(db.String(50), unique=True) localOcorrencia = db.Column(db.String(450)) # tipo = db.Column(db.String(50)) observacoes = db.Column(db.String(450)) situacao = db.Column(db.String(50)) data = db.Column(db.Date()) id = db.Column(db.Integer, primary_key=True) dp_id = db.Column(db.Integer, db.ForeignKey('dp.id')) dp = db.relationship('DpModel', uselist=False, backref=db.backref('ocorrencia'), lazy=True) veiculo_id = db.Column(db.Integer, db.ForeignKey('veiculo.id')) veiculo = db.relationship('VeiculoModel', uselist=False, backref=db.backref('ocorrencia'), lazy=True) tipo_id = db.Column(db.Integer, db.ForeignKey('tipo_ocorrencia.id')) tipo = db.relationship('TipoOcorrenciaModel', uselist=False, backref=db.backref('ocorrencia'), lazy=True) # def __init__(self, numeroOcorrencia, localOcorrencia, tipo, observacoes, situacao, data, _id): # def __init__(self, numeroOcorrencia, localOcorrencia, tipo, observacoes, situacao, data, veiculo_id, _id): def __init__(self, numeroOcorrencia, localOcorrencia, tipo_id, observacoes, situacao, data, dp_id, veiculo_id, _id): self.id = _id self.numeroOcorrencia = numeroOcorrencia self.tipo_id = tipo_id self.observacoes = observacoes self.situacao = situacao self.localOcorrencia = localOcorrencia self.data = datetime.strptime(data, '%Y-%m-%d').date() self.dp_id = dp_id self.veiculo_id = veiculo_id self.id = _id def json(self): return { 'numeroOcorrencia': self.numeroOcorrencia, 'tipo': self.tipo, 'observacoes': self.observacoes, 'situacao': self.situacao, 'data': self.data.strftime('%Y-%m-%d'), 'dp': self.dp.json(), 'veiculo': self.veiculo.json(), 'id': self.id, 'localOcorrencia': self.localOcorrencia, 'tipo': self.tipo.json() } @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id).first() def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit() @classmethod def search_by_params(cls, local, placa, chassis, numeroMotor, nomeProp, numeroOcorrencia, dp, tipo, dataInicial, dataFinal, situacao): ocorrencias = [] # Filtro completo if (dp and dataInicial and dataFinal and tipo): print('Pesquisa contém DP, Tipo e Data Inicial e Data Final!') ocorrencias = db.session.query(OcorrenciaModel).join(VeiculoModel).join(ProprietarioModel).join(DpModel).join(TipoOcorrenciaModel).\ filter(OcorrenciaModel.localOcorrencia.like("%"+local+"%")).\ filter(VeiculoModel.placa.like("%"+placa+"%")).\ filter(VeiculoModel.chassis.like("%"+chassis+"%")).\ filter(VeiculoModel.numeroMotor.like("%"+numeroMotor+"%")).\ filter(ProprietarioModel.nome.like("%"+nomeProp+"%")).\ filter(OcorrenciaModel.numeroOcorrencia.like("%"+numeroOcorrencia+"%")).\ filter(TipoOcorrenciaModel.id == tipo).\ filter(OcorrenciaModel.situacao.like("%"+situacao+"%")).\ filter(OcorrenciaModel.data.between(dataInicial, dataFinal)).\ filter(DpModel.id == dp) elif (dp and dataInicial and dataFinal): print('Pesquisa contém DP, Data Inicial e Data Final!') ocorrencias = db.session.query(OcorrenciaModel).join(VeiculoModel).join(ProprietarioModel).join(DpModel).join(TipoOcorrenciaModel).\ filter(OcorrenciaModel.localOcorrencia.like("%"+local+"%")).\ filter(VeiculoModel.placa.like("%"+placa+"%")).\ filter(VeiculoModel.chassis.like("%"+chassis+"%")).\ filter(VeiculoModel.numeroMotor.like("%"+numeroMotor+"%")).\ filter(ProprietarioModel.nome.like("%"+nomeProp+"%")).\ filter(OcorrenciaModel.numeroOcorrencia.like("%"+numeroOcorrencia+"%")).\ filter(OcorrenciaModel.situacao.like("%"+situacao+"%")).\ filter(OcorrenciaModel.data.between(dataInicial, dataFinal)).\ filter(DpModel.id == dp) elif (dp and tipo): print('Pesquisa contém DP e Tipo!') ocorrencias = db.session.query(OcorrenciaModel).join(VeiculoModel).join(ProprietarioModel).join(DpModel).join(TipoOcorrenciaModel).\ filter(OcorrenciaModel.localOcorrencia.like("%"+local+"%")).\ filter(VeiculoModel.placa.like("%"+placa+"%")).\ filter(VeiculoModel.chassis.like("%"+chassis+"%")).\ filter(VeiculoModel.numeroMotor.like("%"+numeroMotor+"%")).\ filter(ProprietarioModel.nome.like("%"+nomeProp+"%")).\ filter(OcorrenciaModel.numeroOcorrencia.like("%"+numeroOcorrencia+"%")).\ filter(TipoOcorrenciaModel.id == tipo).\ filter(OcorrenciaModel.situacao.like("%"+situacao+"%")).\ filter(DpModel.id == dp) elif (dataInicial and dataFinal): print('Pesquisa contém Data!') ocorrencias = db.session.query(OcorrenciaModel).join(VeiculoModel).join(ProprietarioModel).join(DpModel).join(TipoOcorrenciaModel).\ filter(OcorrenciaModel.localOcorrencia.like("%"+local+"%")).\ filter(VeiculoModel.placa.like("%"+placa+"%")).\ filter(VeiculoModel.chassis.like("%"+chassis+"%")).\ filter(VeiculoModel.numeroMotor.like("%"+numeroMotor+"%")).\ filter(ProprietarioModel.nome.like("%"+nomeProp+"%")).\ filter(OcorrenciaModel.numeroOcorrencia.like("%"+numeroOcorrencia+"%")).\ filter(TipoOcorrenciaModel.id == tipo).\ filter(OcorrenciaModel.situacao.like("%"+situacao+"%")).\ filter(OcorrenciaModel.data.between(dataInicial, dataFinal)) elif dp: print('Pesquisa contém DP!') ocorrencias = db.session.query(OcorrenciaModel).join(VeiculoModel).join(ProprietarioModel).join(DpModel).join(TipoOcorrenciaModel).\ filter(OcorrenciaModel.localOcorrencia.like("%"+local+"%")).\ filter(VeiculoModel.placa.like("%"+placa+"%")).\ filter(VeiculoModel.chassis.like("%"+chassis+"%")).\ filter(VeiculoModel.numeroMotor.like("%"+numeroMotor+"%")).\ filter(ProprietarioModel.nome.like("%"+nomeProp+"%")).\ filter(OcorrenciaModel.numeroOcorrencia.like("%"+numeroOcorrencia+"%")).\ filter(TipoOcorrenciaModel.id == tipo).\ filter(OcorrenciaModel.situacao.like("%"+situacao+"%")).\ filter(DpModel.id == dp) elif tipo: print('Pesquisa contém Tipo!') ocorrencias = db.session.query(OcorrenciaModel).join(VeiculoModel).join(ProprietarioModel).join(DpModel).join(TipoOcorrenciaModel).\ filter(OcorrenciaModel.localOcorrencia.like("%"+local+"%")).\ filter(VeiculoModel.placa.like("%"+placa+"%")).\ filter(VeiculoModel.chassis.like("%"+chassis+"%")).\ filter(VeiculoModel.numeroMotor.like("%"+numeroMotor+"%")).\ filter(ProprietarioModel.nome.like("%"+nomeProp+"%")).\ filter(OcorrenciaModel.numeroOcorrencia.like("%"+numeroOcorrencia+"%")).\ filter(TipoOcorrenciaModel.id == tipo).\ filter(OcorrenciaModel.situacao == situacao) elif local or placa or chassis or numeroMotor or nomeProp or numeroOcorrencia or tipo or situacao: print('Nenhum!') ocorrencias = db.session.query(OcorrenciaModel).join(VeiculoModel).join(ProprietarioModel).join(DpModel).join(TipoOcorrenciaModel).\ filter(OcorrenciaModel.localOcorrencia.like("%"+local+"%")).\ filter(VeiculoModel.placa.like("%"+placa+"%")).\ filter(VeiculoModel.chassis.like("%"+chassis+"%")).\ filter(VeiculoModel.numeroMotor.like("%"+numeroMotor+"%")).\ filter(ProprietarioModel.nome.like("%"+nomeProp+"%")).\ filter(OcorrenciaModel.numeroOcorrencia.like("%"+numeroOcorrencia+"%")).\ filter(OcorrenciaModel.situacao == situacao) # filter(TipoOcorrenciaModel.id == tipo).\ else: ocorrencias = db.session.query(OcorrenciaModel).all() return ocorrencias
class SensorModel(db.Model): """Sensor data structure""" __tablename__ = 'sensor_table' __table_args__ = {'extend_existing': True} id = db.Column(db.Integer, primary_key=True) date_created = db.Column(db.Date()) employee = db.Column(db.String()) item = db.Column(db.String()) serial = db.Column(db.String()) ins_type = db.Column(db.String()) unit_type = db.Column(db.String()) rec_date = db.Column(db.String()) rec_pass = db.Column(db.String()) start_date = db.Column(db.String()) appearance = db.Column(db.String()) functions = db.Column(db.String()) complete = db.Column(db.String()) notes = db.Column(db.String(1000)) complaint = db.Column(db.String()) admin_notes = db.Column(db.String(1000)) rec_to_start = db.Column(db.String()) start_to_comp = db.Column(db.String()) def __init__(self, date_created, employee, item, serial, ins_type, unit_type, rec_date, rec_pass, start_date, appearance, func_check, notes, complete): """sensor object constructor""" self.date_created = date_created self.employee = employee self.item = item self.serial = serial self.ins_type = ins_type self.unit_type = unit_type self.rec_date = rec_date self.rec_pass = rec_pass self.start_date = start_date self.appearance = appearance self.functions = func_check self.complete = complete self.notes = notes @classmethod def get_all_data(cls): """Get all sensor data""" obj = cls.query.filter(SensorModel.complete == '' and SensorModel.rec_date == '' and SensorModel.start_date == '') return obj @classmethod def get_single_data(cls, id): """Get single line data from a given id""" obj = cls.query.filter_by(id=id).first() return obj @classmethod def update_db(cls, id, date_created, employee, item, serial_num, ins_type, unit_type, rec_date, rec_pass, start_date, appearance, functions, notes, complete): """Updates all items in sensor data table""" obj = cls.query.filter_by(id=id).first() obj.date_created = date_created obj.employee = employee obj.item = item obj.serial_num = serial_num obj.ins_type = ins_type obj.unit_type = unit_type obj.rec_date = rec_date obj.rec_pass = rec_pass obj.start_date = start_date obj.appearance = appearance obj.functions = functions obj.complete = complete obj.notes = notes db.session.commit() @classmethod def update_rec_date(cls, id, rec_date): """Updates the receive date for an entry""" obj = cls.query.filter_by(id=id).first() obj.rec_date = rec_date db.session.commit() return None @classmethod def update_rec_pass(cls, id, rec_pass): """Updates the receive pass/fail for an entry""" obj = cls.query.filter_by(id=id).first() obj.rec_pass = rec_pass db.session.commit() @classmethod def update_start_date(cls, id, start_date): """Updates the start date for an entry""" obj = cls.query.filter_by(id=id).first() obj.start_date = start_date obj.rec_to_start = SensorModel.lt_check(obj.rec_date, start_date) db.session.commit() return None @classmethod def update_appearance(cls, id, appearance): """Updates the appearance for an entry""" obj = cls.query.filter_by(id=id).first() obj.appearance = appearance db.session.commit() return None @classmethod def update_functions(cls, id, functions): """Updates the functions for an entry""" obj = cls.query.filter_by(id=id).first() obj.functions = functions db.session.commit() return None @classmethod def update_complete(cls, id, complete): """Updates the complete for an entry""" obj = cls.query.filter_by(id=id).first() obj.complete = complete obj.start_to_comp = SensorModel.lt_check(obj.start_date, complete) db.session.commit() return None @classmethod def update_notes(cls, id, notes): """Updates the notes for an entry""" obj = cls.query.filter_by(id=id).first() obj.notes = notes db.session.commit() return None @classmethod def update_transfer(cls, id, complete): """Updates the transfer for an entry""" obj = cls.query.filter_by(id=id).first() obj.complete = complete obj.start_to_comp = SensorModel.lt_check(obj.rec_date, complete) db.session.commit() return None @classmethod def lt_check(cls, time1, time2): """Calculates time between 2 dates""" tm1 = datetime(int(time1.split()[0].split('-')[0]), int(time1.split()[0].split('-')[1]), int(time1.split()[0].split('-')[2]), int(time1.split()[1].split(':')[0]), int(time1.split()[1].split(':')[1]), int(time1.split()[1].split(':')[2].split('.')[0])) tm2 = datetime(int(time2.split()[0].split('-')[0]), int(time2.split()[0].split('-')[1]), int(time2.split()[0].split('-')[2]), int(time2.split()[1].split(':')[0]), int(time2.split()[1].split(':')[1]), int(time2.split()[1].split(':')[2].split('.')[0])) return str(tm2 - tm1) @classmethod def count_completed(cls, name, month, year): """Function which counts how many entries have been completed""" count = 0 obj = cls.query.filter_by(employee=name).filter(SensorModel.complete != '').all() for o in obj: if o.complete.split('-')[1] == month and o.complete.split('-')[0] == year: count += 1 return count @classmethod def count_failed(cls, month, year): """Function which counts how many entries have failed""" count = 0 obj = cls.query.filter(SensorModel.complete != '').all() for o in obj: if o.complete.split('-')[1] == month and o.complete.split('-')[0] == year: if o.rec_pass == 'Fail' or o.functions == 'Fail' or o.appearance == 'Fail': count += 1 return count @classmethod def process_fail(cls, month, year): """Counts the failed process""" app = 0 func = 0 obj = cls.query.filter(SensorModel.complete != '').all() for o in obj: if o.complete.split('-')[1] == month and o.complete.split('-')[0] == year: if o.appearance == 'Fail': app += 1 if o.functions == 'Fail': func += 1 return [app, func] def save_to_db(self): """Method to save data base""" db.session.add(self) db.session.commit()
class NewsArticleModel(db.Model): __tablename__ = 'articles' id = db.Column(db.Integer, primary_key=True) date = db.Column(db.Date()) title = db.Column(db.String()) description = db.Column(db.String()) text = db.Column(db.Text()) def __init__(self, date, title, description, text): self.date = date self.title = title self.description = description self.text = text def json(self): return { 'id': self.id, 'date': self.date.strftime("%d-%m-%Y"), 'title': self.title, 'description': self.description, 'text': self.text, } @classmethod def find_by_title(cls, title): return cls.query.filter_by(title=title).first() @classmethod def find_by_id(cls, _id): return cls.query.filter_by(id=_id).first() @classmethod def find_by_date(cls, date): return cls.query.filter_by(date=date).all() @classmethod def find_by_date_and_title(cls, date, title): return cls.query.filter_by(date=date, title=title).first() @classmethod def get_all_sorted_by_date(cls): return cls.query.order_by(cls.date.desc()).all() @classmethod def get_all_sorted_by_title(cls): return cls.query.order_by(cls.title).all() @classmethod def get_all_sorted_by_date_and_title(cls): return cls.query.order_by(cls.date.desc(), cls.title).all() @classmethod def delete_from_db_by_date(cls, date): bukl_delete_q = cls.__table__.delete().where(cls.date == date) db.session.execute(bukl_delete_q) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit() def save_to_db(self): db.session.add(self) db.session.commit()
class callbackModel(db.Model): __tablename__ = 'items' keys = db.Column(db.String(), primary_key=True) batch_number = db.Column(db.String()) price = db.Column(db.Integer()) location = db.Column(db.String()) feature = db.Column(db.String()) # data = db.Column(JsonEncodedDict) time = db.Column(db.Date(), default=datetime.now().date()) state = db.Column(db.String(), default="ACTIVE") quantity = db.Column(db.Integer, default=-999) id = db.Column(db.Integer, primary_key=True) recheck = db.Column(db.Boolean, default=False) broken = db.Column(db.Boolean, default=False) missing = db.Column(db.Boolean, default=False) category = db.Column(db.String()) update_date = db.Column(db.Date(), default=datetime.now().date()) extra = db.Column(db.String()) def __init__(self, keys, feature, location, price, batch_number, id, time, state, quantity, category): self.keys = keys self.feature = feature self.location = location self.batch_number = batch_number self.price = price self.time = time self.state = state self.id = id self.quantity = quantity self.category = category self.update_date = time self.extra = "extra" def json(self): return { 'NAME': self.keys, 'FEATURE': self.feature, 'LOCATION': self.location, 'BATCH NUMBER': self.batch_number, 'CREATION_DATE': str(self.time), 'PRICE': self.price, 'BOX NO': self.id, 'STATE': self.state, 'QUANTITY': self.quantity, "CATEGORY": self.category, 'RECHECK': self.recheck, 'MISSING': self.missing, 'BROKEN': self.broken, 'UPDATED_DATE': str(self.update_date), 'EXTRA': self.extra } @classmethod def find_by_key(cls, keys, state): return cls.query.filter_by(keys=keys).filter_by(state=state).all() @classmethod def find_by_only_key(cls, keys): return cls.query.filter_by(keys=keys).all() @classmethod def find_and_update_name(cls, keys, updated_name): items = cls.query.filter_by(keys=keys).all() for each in items: each.keys = updated_name db.session.commit() return items @classmethod def find_by_key_and_id(cls, keys, id): return cls.query.filter_by(id=id).filter_by(keys=keys).all() @classmethod def find_all(cls): item_name = set() item_name_with_category = set() item_largest_box_size = {} item_stock = {} item = cls.query.all() for each in item: item_name.add(each.keys) item_name_with_category.add(each.keys + ':' + each.category) if each.keys in item_largest_box_size: if each.id > item_largest_box_size[each.keys]: item_largest_box_size[each.keys] = each.id else: item_largest_box_size[each.keys] = each.id if each.state == "ACTIVE": if each.keys in item_stock: item_stock[each.keys] = item_stock[each.keys] + 1 else: item_stock[each.keys] = 1 return item_name, item_largest_box_size, item_stock, item_name_with_category @classmethod def find_all_for_delete(cls): item_name = set() item = cls.query.all() return item @classmethod def find_all_data(cls): item = cls.query.all() return item @classmethod def find_and_update_state(cls, keys, id, quantity, time): item = cls.query.filter_by(id=id).filter_by(keys=keys).first() if item.state == "INACTIVE": return "Item already deleted" if item.quantity != -999: if quantity < 0: return "please enter positive quantity to be reduced" + str( item) item.quantity = item.quantity - quantity if item.quantity == 0: item.state = "INACTIVE" item.update_date = time db.session.commit() if item.quantity < 0: return "please enter value based on the box value not the total order value" + str( item) item.feature = item.feature + ",SALE#" + str(time) + ":" + str( quantity) item.update_date = time db.session.commit() return "item quantity reduced from the box" + str(item) if item.quantity == -999 and quantity != -999: return "PLEASE ENTER 0 IN QUANTITY" item.feature = item.feature + ",SALE#" + str(time) + ":" + str( quantity) item.state = "INACTIVE" item.update_date = time db.session.commit() return item @classmethod def find_all_changes_done_today(cls): item = cls.query.filter(cls.update_date > (datetime.now().date() - timedelta(days=2))).all() return item @classmethod def find_by_only_key_and_update_godown(cls, keys, location): items = cls.query.filter_by(keys=keys).all() for each in items: each.location = location db.session.commit() return items @classmethod def find_and_reactivate_record(cls, keys, id, time, state, quantity): item = cls.query.filter_by(id=id).filter_by(keys=keys).first() item.state = state item.feature = item.feature + ",REPURCHASED#" + str(time) + ":" + str( quantity) item.update_date = time if item.quantity != -999: item.quantity = item.quantity + quantity db.session.commit() return item @classmethod def find_and_update_record(cls, keys, feature, location, price, batch_number, id, time, state, quantity, category, updated_name, updated_box): item = cls.query.filter_by(id=id).filter_by(keys=keys).first() item.keys = updated_name item.state = state item.feature = feature item.location = location item.batch_number = batch_number item.price = price item.update_date = time item.quantity = quantity item.category = category item.id = updated_box value = db.session.commit() return value @classmethod def set_recheck(cls, datepassed): item = cls.query.filter(cls.update_date < datepassed).filter_by( state="ACTIVE").all() item_name = set() for each in item: item_name.add(each.keys + ':' + each.category) if not each.recheck: each.recheck = True item = cls.query.filter(cls.update_date > datepassed).all() for each in item: if each.recheck: each.recheck = False db.session.commit() return item_name @classmethod def reportItem(cls, keys, id, operation): item = cls.query.filter_by(id=id).filter_by(keys=keys).first() if item != None: if operation.upper() == "BROKEN": item.broken = True item.update_date = datetime.now().date() elif operation.upper() == "MISSING": item.missing = True item.update_date = datetime.now().date() elif operation.upper() == "FOUND": item.missing = False item.update_date = datetime.now().date() elif operation.upper() == "RECHECKED": item.recheck = False item.update_date = datetime.now().date() else: return "operation not supported yet on item " + str(item) db.session.commit() return item.keys return "item not found" def save_to_db(self): item = db.session.add(self) db.session.commit() return def delete_from_db(self): db.session.delete(self) with db.engine.begin() as conn: conn.execute("VACUUM") db.session.commit()
class MoodModel(db.Model): __tablename__ = 'mood' mood_id = db.Column(db.Integer,primary_key=True) email = db.Column(db.String(101)) day_status = db.Column(db.String(101)) sleep_status = db.Column(db.String(101)) time_spending = db.Column(db.String(101)) eat_status = db.Column(db.String(101)) activities = db.Column(db.String(101)) entry_date = db.Column(db.Date()) def __init__(self,email,day_status,sleep_status,time_spending,eat_status,activities,entry_date): self.email = email self.day_status = day_status self.sleep_status = sleep_status self.time_spending = time_spending self.eat_status = eat_status self.activities = activities self.entry_date = entry_date def save(self): db.session.add(self) db.session.commit() def delete(self): db.session.delete(self) db.session.commit() def json(self): string = self.entry_date.strftime("%Y/%m/%d") return {'email':self.email,'day_status':self.day_status,'sleep_status':self.sleep_status,'time_spending':self.time_spending,'eat_status':self.eat_status,'activites':self.activities,'date':string} @classmethod def find_by_email_and_date(cls,email,entry_date): return cls.query.filter_by(email = email,entry_date = entry_date).all() @classmethod def check_by_email_and_date(cls,email,entry_date): return cls.query.filter_by(email = email,entry_date = entry_date).first() @classmethod def analyze_weekly_status(cls,moodlist): day_stat = [] sleep_stat = [] time_stat = [] eat_stat = [] activity = [] for mood in moodlist: day_stat.append(mood.day_status) sleep_stat.append(mood.sleep_status) time_stat.append(mood.time_spending) eat_stat.append(mood.eat_status) activity.append(mood.activities) return {'great':day_stat.count('great'),'normal':day_stat.count('normal'),'sad':day_stat.count('sad'), 'sleep early':sleep_stat.count('sleep early'),'sleep good':sleep_stat.count('sleep good'),'sleep medium':sleep_stat.count('sleep medium'),'sleep bad':sleep_stat.count('sleep bad'), 'family':time_stat.count('family'),'friend':time_stat.count('friend'), 'eat healty':eat_stat.count('eat healthy'),'eat_homemade':eat_stat.count('eat homemade'),'eat_fastfood':eat_stat.count('eat fastfood'),'eat soda':eat_stat.count('eat soda'),'eat sweets':eat_stat.count('eat sweets'), 'read':activity.count('read'),'gaming':activity.count('gaming'),'movie':activity.count('movie'),'party':activity.count('party'),'workout':activity.count('workout') } @classmethod def analyze_user_statistics(cls,moodlist): day_stat = [] sleep_stat = [] time_stat = [] eat_stat = [] activity = [] min_values = [] #iterating over moodlist to fetch each mood data or row for mood in moodlist: #day_stat.append(mood.day_status) sleep_stat.append(mood.sleep_status) time_stat.append(mood.time_spending) eat_stat.append(mood.eat_status) activity.append(mood.activities) #craeting dictionary of each stat in order to store their individual count sleep_dict = {'sleep early':sleep_stat.count('sleep early'), 'sleep good':sleep_stat.count('sleep good'), 'sleep medium':sleep_stat.count('sleep medium'), 'sleep bad':sleep_stat.count('sleep bad') } time_dict = { 'family':time_stat.count('family'), 'friend':time_stat.count('friend') } eat_dict = { 'eat healty':eat_stat.count('eat healthy'), 'eat homemade':eat_stat.count('eat homemade'), 'eat fastfood':eat_stat.count('eat fastfood'), 'eat soda':eat_stat.count('eat soda'), 'eat sweets':eat_stat.count('eat sweets') } activity_dict = { 'read':activity.count('read'), 'gaming':activity.count('gaming'), 'movie':activity.count('movie'), 'party':activity.count('party'), 'workout':activity.count('workout') } #appending the min values of each dictionary in min_values list min_values.append(min(sleep_dict,key=sleep_dict.get)) min_values.append(min(time_dict,key=time_dict.get)) min_values.append(min(eat_dict,key=eat_dict.get)) min_values.append(min(activity_dict,key=activity_dict.get)) return {'min':min_values}