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}