def filtrar(self):
elementos = [('CAD', self.cad), ('NOME', self.nome),
('RAMAL', self.ramal), ('PAUSAS', self.pausa),
('CELULAR', self.celular), ('MONITORIA', self.monitoria),
('CC', self.cc), ('SETOR', self.setor),
('CARGO', self.cargo), ('FUNCAO', self.funcao),
('NVL', self.nvl), ('GESTOR', self.gestor),
('JORNADA', self.jornada), ('SAB', self.sab),
('MES_ANO', self.mes_ano),
('DATA_DESLIGAMENTO', self.data_desligamento),
('STATUS', self.status)]
lista_somente_elemento_preenchido = []
for e in elementos:
if e[1] != '':
lista_somente_elemento_preenchido.append(e)
filtro = ""
for campo in lista_somente_elemento_preenchido:
filtro = filtro + campo[0] + " = '" + campo[1] + "' and "
filtro = filtro[:len(filtro) - 4]
SQL = 'SELECT * FROM NC_MOP WHERE ' + filtro
if (len(lista_somente_elemento_preenchido) > 0):
return ado.buscar(cmd_sql=SQL)
else:
return mop()
def mop():
SQL = """
SELECT
A.*,B.HRS,B.TURNO
FROM NC_MOP AS A
LEFT JOIN
NC_JORNADA AS B
ON
A.JORNADA = B.JORNADA
"""
retorno = ado.buscar(cmd_sql=SQL)
return retorno
def copiar_mes_sql(mes_antigo, mes_novo):
CABECALHO = list(ado.buscar(cmd_sql="SELECT TOP 1 * FROM NC_MOP")[0].keys())
SQL = "SELECT "
campos = ""
for campo in CABECALHO:
if campo != "ID" and campo != "MES_ANO":
campos = campos + campo+","
campos = campos[:len(campos)-1]
SQL = SQL + campos + ", '{}' MES_ANO FROM NC_MOP WHERE MES_ANO = '{}' AND STATUS != 'DESLIGADO'".format(mes_novo,mes_antigo)
SQL_INSERT = "INSERT INTO NC_MOP ("+campos+", MES_ANO) " + SQL
ado.executar(SQL_INSERT)
return True
def buscar_por_id(ID):
SQL = "SELECT * FROM NC_MOP WHERE ID = " + ID
print(SQL)
return ado.buscar(cmd_sql=SQL)[0]
def buscar_importados():
SQL = "SELECT * FROM NC_USER WHERE STATUS = 'i'"
return ado.buscar(cmd_sql=SQL)
def logar(usuario, senha):
SQL = "SELECT * FROM NC_USER WHERE MATRICULA = {} AND SENHA = '{}'".format(
usuario, senha)
return ado.buscar(cmd_sql=SQL)[0]
def status():
SQL = "SELECT * FROM NC_STATUS"
return ado.buscar(cmd_sql=SQL)
def mes():
SQL = "SELECT * FROM NC_MES ORDER BY ORDEM DESC"
return ado.buscar(cmd_sql=SQL)
def funcao(setor):
SQL = "SELECT DISTINCT FUNCAO FROM NC_SETOR WHERE SETOR = '{}'".format(
setor)
print(SQL)
return ado.buscar(cmd_sql=SQL)
def funcao_():
SQL = "SELECT DISTINCT FUNCAO FROM NC_SETOR"
return ado.buscar(cmd_sql=SQL)
def setor():
SQL = 'SELECT DISTINCT SETOR FROM NC_SETOR'
return ado.buscar(cmd_sql=SQL)
def cc():
return ado.buscar(table="NC_CC")
def gestor():
return ado.buscar(table="NC_GESTOR")
def jornada():
return ado.buscar(cmd_sql="SELECT DISTINCT JORNADA FROM NC_JORNADA")
def movimentacoes():
movimentacoes = ado.buscar(table="NC_MOVIMENTACOES_DESLIGAMENTO")
if len(movimentacoes) == 0:
movimentacoes = [{"SEM RESULTADO": "SEM RESULTADO"}]
return movimentacoes
def jornadas():
jornadas = ado.buscar(table='NC_JORNADA')
if len(jornadas) == 0:
jornadas = [{"SEM RESULTADO": "SEM RESULTADO"}]
return jornadas
def cargo(setor, funcao):
SQL = "SELECT DISTINCT CARGO FROM NC_SETOR WHERE SETOR = '{}' and FUNCAO = '{}'".format(
setor, funcao)
return ado.buscar(cmd_sql=SQL)
def users():
return ado.buscar(cmd_sql="SELECT * FROM NC_USER")
def cargo_():
SQL = """SELECT DISTINCT CARGO FROM(
SELECT DISTINCT CARGO FROM NC_SETOR
UNION
SELECT DISTINCT CARGO FROM NC_MOP) AS A """
return ado.buscar(cmd_sql=SQL)