def listarMisPreguntas(self):
ln_opcn_mnu = request.form["id_mnu_ge"]
id_lgn_accso_ge = request.form["id_lgn_accso_ge"]
token = request.headers['Authorization']
validacionSeguridad = ValidacionSeguridad()
lb_val = validacionSeguridad.Principal(token, ln_opcn_mnu,
optns.OPCNS_MNU['PreguntaSg'])
a_prmtrs = {}
lc_prmtrs = ''
StrSql = " select"\
" a.id,"\
" c.dscrpcn,"\
" case when a.estdo is true then 'ACTIVO' else 'INACTIVO' end as estdo ,"\
" a.id_prgnt_sgrdd_ge"\
" from"\
" "+str(dbConf.DB_SHMA)+".tbrespuestas_preguntas_seguridad a"\
" inner join "+str(dbConf.DB_SHMA)+".tbpreguntas_seguridad_ge b on"\
" a.id_prgnt_sgrdd_ge = b.id"\
" inner join "+str(dbConf.DB_SHMA)+".tbpreguntas_seguridad c on"\
" b.id_prgnta_sgrdd = c.id"\
" where"\
" a.id_lgn_accso_ge ="+id_lgn_accso_ge
Cursor = Pconnection.queryFree(StrSql)
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
###return Utils.nice_json({labels.lbl_stts_success:"TEST!!"}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_RGSTRS}, 200)
def remover_favorito(self):
#return Utils.nice_json({labels.lbl_stts_success:labels.FVRTO_BRRDO}, 200)
key = request.headers['Authorization']
if key:
validacionSeguridad.ValidacionToken(key)
if validacionSeguridad:
token = Pconnection.querySelect(
dbConf.DB_SHMA + '.tbgestion_accesos', "token",
"key='" + key + "' and estdo is true")[0]
tmp_data = jwt.decode(token["token"],
conf.SS_TKN_SCRET_KEY + key, 'utf-8')
datosUsuario = validacionSeguridad.ObtenerDatosUsuario(
tmp_data['lgn'])[0]
id_lgn_prfl_scrsl = validacionSeguridad.validaUsuario(
tmp_data['lgn'])
ld_id_mnu_ge = request.form['id_mnu_ge']
#busco el favorito
lo_current = self.busca_favorito(
id_lgn_prfl_scrsl['id_prfl_scrsl'], ld_id_mnu_ge)
arrayValues = {}
arrayValues['estdo'] = 'false'
Pconnection.queryUpdate(dbConf.DB_SHMA + '.tbfavoritosmenu',
arrayValues,
'id=' + str(lo_current['id']))
return Utils.nice_json(
{labels.lbl_stts_success: labels.FVRTO_BRRDO}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_SN_PRMTRS}, 400)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_SN_PRMTRS}, 400)
def actualizarContrenaInterna(self):
lc_clve_actl = request.form['clve_tmprl']
lc_nva_cntrsna = request.form['nva_cntrsna']
lc_rnva_cntrsna = request.form['rnva_cntrsna']
ld_fcha_actl = time.ctime()
####lc_clve_actl = hashlib.md5(lc_clve_actl.encode('utf-8')).hexdigest()
lc_id_lgn_ge = request.form['id_lgn_ge']
'''
Validaa nueva contrasena y la clave temporal tiene que ser iguales
'''
if lc_nva_cntrsna != lc_rnva_cntrsna:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_CNCD_CNTSNA}, 400)
'''
Validar que la contrasena cumpla con el Patron de contraseas
'''
if not re.match(conf.EXPRESION_CLAVE_USUARIO, lc_nva_cntrsna):
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_PTRN_CLVE}, 400)
lc_nva_cntrsna = hashlib.md5(
lc_nva_cntrsna.encode('utf-8')).hexdigest()
lc_clve_actl = hashlib.md5(lc_clve_actl.encode('utf-8')).hexdigest()
'''
Actualizar el usuario si coincide con la contraseña actual que ingreso
'''
lc_query_usro = " select "\
" b.id as id_lgn_ge, "\
" a.id as id_lgn "\
" from "\
" "+dbConf.DB_SHMA+".tblogins a "\
" inner join "+dbConf.DB_SHMA+".tblogins_ge b on "\
" a.id = b.id_lgn "\
" where "\
" b.id = "+lc_id_lgn_ge+" "\
" and cntrsna = '"+lc_clve_actl+"' LIMIT 1 "
Cursor_clv_tmp2 = lc_cnctn.queryFree(lc_query_usro)
if Cursor_clv_tmp2:
data_usro = json.loads(json.dumps(Cursor_clv_tmp2[0], indent=2))
#Actualiza login
la_clmns_actlzr_lgn = {}
la_clmns_actlzr_lgn['id'] = str(data_usro['id_lgn'])
la_clmns_actlzr_lgn['cntrsna'] = str(lc_nva_cntrsna)
self.UsuarioActualizaRegistro(la_clmns_actlzr_lgn, 'tblogins')
la_clmns_actlzr_lgn_ge = {}
la_clmns_actlzr_lgn_ge['id'] = str(data_usro['id_lgn_ge'])
la_clmns_actlzr_lgn_ge['fcha_mdfccn'] = str(ld_fcha_actl)
la_clmns_actlzr_lgn_ge['cmbo_cntrsna'] = 'false'
self.UsuarioActualizaRegistro(la_clmns_actlzr_lgn_ge,
'tblogins_ge')
return Utils.nice_json({labels.lbl_stts_success: True}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_CNTRSNA_INVLDA}, 400)
def actualizar(self):
key = request.headers['Authorization']
ln_opcn_mnu = request.form["id_mnu_ge"]
validacionSeguridad = ValidacionSeguridad()
lb_val = validacionSeguridad.Principal(key, ln_opcn_mnu,
optns.OPCNS_MNU['PreguntaSg'])
u = ActualizarAcceso(request.form)
if not u.validate():
return Utils.nice_json({labels.lbl_stts_error: u.errors}, 400)
if lb_val:
token = validacionSeguridad.ValidacionToken(key)
lc_datosUsuario = validacionSeguridad.ObtenerDatosUsuario(
token['lgn'])[0]
lb_estdo = request.form["lb_estdo"]
a_prgnta_ge = {}
a_prgnta = {}
# Actualizo tabla ge
a_prgnta_ge['id'] = request.form['ln_id_prgnta_ge']
a_prgnta_ge['fcha_mdfccn'] = str(ld_fcha_actl)
a_prgnta_ge['id_lgn_mdfccn_ge'] = str(lc_datosUsuario['id_lgn_ge'])
a_prgnta['cdgo'] = request.form['lc_cdgo']
a_prgnta['dscrpcn'] = request.form['lc_dscrpcn']
a_prgnta['estdo'] = lb_estdo
a_prgnta['fcha_mdfccn'] = str(ld_fcha_actl)
a_prgnta['id_lgn_mdfccn_ge'] = str(lc_datosUsuario['id_lgn_ge'])
#validacion duplicados
lc_tbls_query = dbConf.DB_SHMA + ".tbpreguntas_seguridad_ge a INNER JOIN " + dbConf.DB_SHMA + ".tbpreguntas_seguridad b on a.id_prgnta_sgrdd=b.id "
CursorValidar1 = Pconnection.querySelect(
lc_tbls_query, ' b.id ', " a.id <>'" + str(a_prgnta_ge['id']) +
"' and b.cdgo ='" + str(a_prgnta['cdgo']) + "'")
CursorValidar2 = Pconnection.querySelect(
lc_tbls_query, ' b.id ', " a.id <>'" + str(a_prgnta_ge['id']) +
"' and b.dscrpcn= '" + str(a_prgnta['dscrpcn']) + "' ")
if CursorValidar1:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_RGSTRO_RPTDO}, 400)
if CursorValidar2:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_RGSTRO_RPTDO}, 400)
self.PreguntaActualizaRegistro(a_prgnta_ge,
'tbpreguntas_seguridad_ge')
# obtengo id_prgnta a partir del id
Cursor = Pconnection.querySelect(
dbConf.DB_SHMA + '.tbpreguntas_seguridad_ge',
'id_prgnta_sgrdd',
"id=" + str(request.form['ln_id_prgnta_ge']))
if Cursor:
data = json.loads(json.dumps(Cursor[0], indent=2))
ln_id_prgnta = data['id_prgnta_sgrdd']
# Actualizo tabla principal
a_prgnta['id'] = ln_id_prgnta
self.PreguntaActualizaRegistro(a_prgnta, 'tbpreguntas_seguridad')
return Utils.nice_json(
{labels.lbl_stts_success: labels.SCCSS_ACTLZCN_EXTSA}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_ATRZCN}, 400)
def listar(self):
ln_opcn_mnu = request.form["id_mnu_ge_opt"]
ln_id_grpo_emprsrl = request.form["id_grpo_emprsrl"]
key = request.headers['Authorization']
validacionSeguridad = ValidacionSeguridad()
lb_val = validacionSeguridad.Principal(key, ln_opcn_mnu,
optns.OPCNS_MNU['Optmenu'])
a_prmtrs = {}
lc_prmtrs = ''
try:
a_prmtrs['ordn'] = request.form['ordn']
lc_ordn = a_prmtrs['ordn']
lc_prmtrs += " and a.ordn like '%" + lc_ordn + "%'"
except:
pass
try:
a_prmtrs['dscrpcn'] = request.form['dscrpcn']
lc_dscrpcn = a_prmtrs['dscrpcn']
lc_prmtrs += " and a.dscrpcn like '%" + lc_dscrpcn + "%' "
except:
pass
try:
a_prmtrs['id_mnu_ge'] = request.form['id_mnu_ge']
ln_id_prgnta_ge = a_prmtrs['id_mnu_ge']
lc_prmtrs += " and b.id = '" + ln_id_prgnta_ge + "'"
except:
pass
if lb_val:
StrSql = " select "\
" a.id_mnu as parent,"\
" b.id,"\
" a.ordn,"\
" a.dscrpcn,"\
"a.lnk,"\
" case when a.estdo = true then 'ACTIVO' else 'INACTIVO' end as estdo"\
" from "\
" "+str(dbConf.DB_SHMA)+".tbmenu a inner join "+str(dbConf.DB_SHMA)+".tbmenu_ge b on "\
" a.id=b.id_mnu "\
" where "\
" b.estdo = true and id_grpo_emprsrl = "+ln_id_grpo_emprsrl+" "\
+ str(lc_prmtrs)
print(StrSql)
Cursor = Pconnection.queryFree(StrSql)
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_RGSTRS}, 400)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_ATRZCN}, 400)
def creaPreguntaSeguridad(self):
print('aquiuu')
key = request.headers['Authorization']
ln_opcn_mnu = request.form["id_mnu_ge"]
ln_id_prgnt_sgrdd_ge = request.form["ln_id_prgnt_sgrdd_ge"]
validacionSeguridad = ValidacionSeguridad()
lb_val = validacionSeguridad.Principal(
key, ln_opcn_mnu, optns.OPCNS_MNU['MisPreguntaSeguridad'])
u = ActualizarPreguntaSeguridad(request.form)
if not u.validate():
return Utils.nice_json({labels.lbl_stts_error: u.errors}, 400)
if (ln_id_prgnt_sgrdd_ge == '0'):
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_PRGNTA_SGRDD}, 400)
if lb_val:
token = validacionSeguridad.ValidacionToken(key)
lc_datosUsuario = validacionSeguridad.ObtenerDatosUsuario(
token['lgn'])[0]
lc_rspsta = request.form['lc_rspsta']
lc_rspsta = hashlib.md5(lc_rspsta.encode('utf-8')).hexdigest()
la_rspsta_prgnta_sgrdd = {}
if (request.form['lc_rspsta']):
la_rspsta_prgnta_sgrdd['rspsta'] = lc_rspsta
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_RSPSTA_PRGNTA_SGRDD},
400)
# Actualizo tabla ge
la_rspsta_prgnta_sgrdd['id_prgnt_sgrdd_ge'] = ln_id_prgnt_sgrdd_ge
la_rspsta_prgnta_sgrdd['id_lgn_accso_ge'] = str(
lc_datosUsuario['id_lgn_ge'])
la_rspsta_prgnta_sgrdd['fcha_mdfcn'] = str(ld_fcha_actl)
la_rspsta_prgnta_sgrdd['id_lgn_mdfccn_ge'] = str(
lc_datosUsuario['id_lgn_ge'])
la_rspsta_prgnta_sgrdd['fcha_crcn'] = str(ld_fcha_actl)
la_rspsta_prgnta_sgrdd['id_lgn_crcn_ge'] = str(
lc_datosUsuario['id_lgn_ge'])
#validacion duplicados
lc_tbls_query = dbConf.DB_SHMA + ".tbrespuestas_preguntas_seguridad "
CursorValidar1 = Pconnection.querySelect(
lc_tbls_query, ' id ',
" id_lgn_accso_ge = '" + str(lc_datosUsuario['id_lgn_ge']) +
"' and id_prgnt_sgrdd_ge ='" + str(ln_id_prgnt_sgrdd_ge) + "'")
if CursorValidar1:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_RGSTRO_RPTDO}, 400)
self.crearPregunta_seguridad(la_rspsta_prgnta_sgrdd,
'tbrespuestas_preguntas_seguridad')
return Utils.nice_json(
{labels.lbl_stts_success: labels.SCCSS_ACTLZCN_EXTSA}, 200)
def claveTemporal(self):
lc_crro_crprtvo = request.form['crro_crprtvo']
lc_query_clv_tmp = "select id from ( "\
" select "\
" case when emplds_une.id is not null then "\
" emplds.crro_elctrnco "\
" else "\
" prstdr.crro_elctrnco "\
" end as crro_elctrnco, "\
" lgn_ge.id "\
" from "+dbConf.DB_SHMA+".tblogins_ge lgn_ge "\
" left join "+dbConf.DB_SHMA+".tblogins lgn on lgn.id = lgn_ge.id_lgn "\
" left join "+dbConf.DB_SHMA+".tbempleados_une emplds_une on emplds_une.id_lgn_accso_ge = lgn_ge.id "\
" left join "+dbConf.DB_SHMA+".tbempleados emplds on emplds.id = emplds_une.id_empldo "\
" left join "+dbConf.DB_SHMA+".tbprestadores prstdr on prstdr.id_lgn_accso_ge = lgn_ge.id "\
" left join "+dbConf.DB_SHMA+".tbcargos_une crgo_une on crgo_une.id = emplds_une.id_crgo_une "\
" left join "+dbConf.DB_SHMA+".tbcargos crgo on crgo.id = crgo_une.id_crgo "\
" left join "+dbConf.DB_SHMA+".tbunidades_negocio undd_ngco on undd_ngco.id = emplds_une.id_undd_ngco "\
" inner join "+dbConf.DB_SHMA+".tblogins_perfiles_sucursales as prfl_scrsls on prfl_scrsls.id_lgn_ge = lgn_ge.id and prfl_scrsls.mrca_scrsl_dfcto is true "\
" inner join "+dbConf.DB_SHMA+".tbperfiles_une as prfl_une on prfl_une.id = prfl_scrsls.id_prfl_une "\
" where id_mtvo_rtro_une is null) as test "\
" where crro_elctrnco ='"+lc_crro_crprtvo+"'"
Cursor_clv_tmp = lc_cnctn.queryFree(lc_query_clv_tmp)
if Cursor_clv_tmp:
data = json.loads(json.dumps(Cursor_clv_tmp[0], indent=2))
#se inserta en la tabla para posterior validacion
arrayValues = {}
#Se genera la keyword
key = str(data['id']) + str(random.randint(1, 100000))
key = hashlib.md5(key.encode('utf-8')).hexdigest()
ld_fcha_actl = time.ctime()
clave_tmp = Utils.aleatoria_n_digitos(8)
arrayValues['cntrsna'] = str(clave_tmp)
IpUsuario = IP(socket.gethostbyname(socket.gethostname()))
arrayValues['ip'] = str(IpUsuario)
device = Utils.DetectarDispositivo(
request.headers.get('User-Agent'))
arrayValues['dspstvo_accso'] = str(device)
arrayValues['crreo_slctnte'] = str(lc_crro_crprtvo)
arrayValues['fcha_mdfccn '] = str(ld_fcha_actl)
arrayValues['id_lgn_ge '] = str(data['id'])
arrayValues['token'] = str(key)
arrayValues['estdo'] = str('true')
lc_cnctn.queryInsert(dbConf.DB_SHMA + ".tbclaves_tmp", arrayValues)
return Utils.nice_json(
{
labels.lbl_stts_success:
'Redireccionando Espera un Momento...',
"tkn": key
}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_CRRO_SSTMA}, 400)
def crear_perfiles_sucursales(self):
lob_rspsta = Datos_perfiles_sucursales(request.form)
if not lob_rspsta.validate():
return Utils.nice_json({labels.lbl_stts_error: lob_rspsta.errors},
400)
key = request.headers['Authorization']
validacionSeguridad = ValidacionSeguridad()
token = validacionSeguridad.ValidacionToken(key)
datosUsuario = validacionSeguridad.ObtenerDatosUsuario(token['lgn'])[0]
arrayValues = {}
ln_id = request.form["id"]
ln_id_prfl_une = request.form["id_prfl_une"]
lb_mrca_scrsl_dfcto = request.form["mrca_scrsl_dfcto"]
lb_estdo = request.form["estdo"]
lb_cntrl_cmprbnte = request.form["cntrl_cmprbnte"]
lb_cntrl_cja_mnr = request.form["cntrl_cja_mnr"]
lb_cntrl_atrzcn = request.form["cntrl_atrzcn"]
lb_gdgt_sgmnto_trsldo = request.form["gdgt_sgmnto_trsldo"]
ln_mnto_rmblso_pac = request.form["mnto_rmblso_pac"]
ln_id_frma_pgo_dfcto_une = request.form["id_frma_pgo_dfcto_une"]
if ln_id_frma_pgo_dfcto_une == 'null':
ln_id_frma_pgo_dfcto_une = Null
ln_id_cnl_rcdo_dfcto_une = request.form["id_cnl_rcdo_dfcto_une"]
if ln_id_cnl_rcdo_dfcto_une == 'null':
ln_id_cnl_rcdo_dfcto_une = Null
arrayValues['id_prfl_une'] = ln_id_prfl_une
arrayValues['mrca_scrsl_dfcto'] = lb_mrca_scrsl_dfcto
arrayValues['estdo'] = lb_estdo
arrayValues['cntrl_cmprbnte'] = lb_cntrl_cmprbnte
arrayValues['cntrl_cja_mnr'] = lb_cntrl_cja_mnr
arrayValues['id_frma_pgo_dfcto_une'] = ln_id_frma_pgo_dfcto_une
arrayValues['id_cnl_rcdo_dfcto_une'] = ln_id_cnl_rcdo_dfcto_une
arrayValues['cntrl_atrzcn'] = lb_cntrl_atrzcn
arrayValues['gdgt_sgmnto_trsldo'] = lb_gdgt_sgmnto_trsldo
arrayValues['mnto_rmblso_pac'] = ln_mnto_rmblso_pac
arrayValues['id_lgn_mdfccn_ge'] = str(datosUsuario['id_lgn_ge'])
arrayValues['fcha_mdfccn'] = str(fecha_act)
Cursor = lc_cnctn.queryUpdate(
dbConf.DB_SHMA + "." + str('tblogins_perfiles_sucursales'),
arrayValues, 'id=' + str(ln_id))
if Cursor:
return Utils.nice_json(
{labels.lbl_stts_success: labels.SCCSS_ACTLZCN_EXTSA}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_PRBLMS_GRDR}, 400)
def perfiles_sucursales(self):
ln_id_lgn_ge = request.form["id_lgn_ge"]
strSql ="select lgn_prfl_scrsl.id as lgn_prfl_scrsl,"\
"prfl.cdgo as cdgo_prfl,prfl.dscrpcn as dscrpcn_prfl,"\
"lgn_prfl_scrsl.id_prfl_une as id_prfl_une,"\
"case when lgn_prfl_scrsl.estdo = true then 'ACTIVO' else 'INACTIVO' end as estdo, "\
"case when lgn_prfl_scrsl.mrca_scrsl_dfcto = true then 'ACTIVO' else 'INACTIVO' end as mrca_scrsl_dfcto, "\
"scrsls.nmbre_scrsl as nmbre_scrsl, "\
"lgn_prfl_scrsl.id_scrsl as id_scrsl, "\
"lgn_prfl_scrsl.id_lgn_ge as id_lgn_ge, "\
"lgn_prfl_scrsl.id_lgn_crcn_ge as id_lgn_crcn_ge, "\
"lgn_prfl_scrsl.id_lgn_mdfccn_ge as id_lgn_mdfccn_ge, "\
"lgn_prfl_scrsl.fcha_crcn::text as fcha_crcn, "\
"lgn_prfl_scrsl.fcha_mdfccn::text as fcha_mdfccn, "\
"undds_ngcio.nmbre_rzn_scl as undds_ngcio, "\
"undds_ngcio.id as id_undds_ngcio, "\
"lgn_prfl_scrsl.id_frma_pgo_dfcto_une as id_frma_pgo_dfcto_une, "\
"lgn_prfl_scrsl.id_cnl_rcdo_dfcto_une as id_cnl_rcdo_dfcto_une, "\
"lgn_prfl_scrsl.gdgt_sgmnto_trsldo as gdgt_sgmnto_trsldo,lgns.nmbre_usro, "\
"case when lgn_prfl_scrsl.cntrl_cmprbnte = true then 'ACTIVO' else 'INACTIVO' end as cntrl_cmprbnte, "\
"case when lgn_prfl_scrsl.cntrl_cja_mnr = true then 'ACTIVO' else 'INACTIVO' end as cntrl_cja_mnr, "\
"case when lgn_prfl_scrsl.cntrl_atrzcn = true then 'ACTIVO' else 'INACTIVO' end as cntrl_atrzcn, "\
"lgn_prfl_scrsl.mnto_rmblso_pac::text as mnto_rmblso_pac, "\
"case when lgn_prfl_scrsl.gdgt_sgmnto_trsldo = true then 'ACTIVO' else 'INACTIVO' end as gdgt_sgmnto_trsldo "\
"from "\
" "+str(dbConf.DB_SHMA)+".tblogins_perfiles_sucursales as lgn_prfl_scrsl inner join "\
" "+str(dbConf.DB_SHMA)+".tbperfiles_une as prfl_une "\
"on lgn_prfl_scrsl.id_prfl_une = prfl_une.id inner join "\
" "+str(dbConf.DB_SHMA)+".tbperfiles as prfl "\
"on prfl.id = prfl_une.id_prfl inner join "\
" "+str(dbConf.DB_SHMA)+".tbsucursales as scrsls "\
"on scrsls.id = lgn_prfl_scrsl.id_scrsl inner join "\
" "+str(dbConf.DB_SHMA)+".tbunidades_negocio as undds_ngcio "\
"on undds_ngcio.id = scrsls.id_undd_ngco inner join "\
" "+str(dbConf.DB_SHMA)+".tblogins_ge as lgns_ge "\
"on lgns_ge.id = lgn_prfl_scrsl.id_lgn_ge inner join "\
" "+str(dbConf.DB_SHMA)+".tblogins as lgns "\
"on lgns_ge.id_lgn = lgns.id "\
"where lgn_prfl_scrsl.id_lgn_ge = "+str(ln_id_lgn_ge)+""\
" order by prfl.dscrpcn "
Cursor = lc_cnctn.queryFree(strSql)
print(type(Cursor))
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_success: labels.INFO_NO_DTS}, 200)
def listar(self):
ln_opcn_mnu = request.form["id_mnu_ge"]
token = request.headers['Authorization']
validacionSeguridad = ValidacionSeguridad()
lb_val = validacionSeguridad.Principal(token, ln_opcn_mnu,
optns.OPCNS_MNU['PreguntaSg'])
a_prmtrs = {}
lc_prmtrs = ''
try:
a_prmtrs['cdgo'] = request.form['cdgo']
lc_cdgo = a_prmtrs['cdgo']
lc_prmtrs += " and a.cdgo like '%" + lc_cdgo + "%'"
except:
pass
try:
a_prmtrs['dscrpcn'] = request.form['dscrpcn']
lc_dscrpcn = a_prmtrs['dscrpcn']
lc_prmtrs += " and a.dscrpcn like '%" + lc_dscrpcn + "%' "
except:
pass
try:
a_prmtrs['id_prgnta_ge'] = request.form['id_prgnta_ge']
ln_id_prgnta_ge = a_prmtrs['id_prgnta_ge']
lc_prmtrs += " and b.id = '" + ln_id_prgnta_ge + "'"
except:
pass
if lb_val:
StrSql = " select "\
" b.id,"\
" a.cdgo,"\
" a.dscrpcn "\
" ,case when a.estdo = true then 'ACTIVO' else 'INACTIVO' end as estdo "\
" from "\
" "+str(dbConf.DB_SHMA)+".tbpreguntas_seguridad a inner join "+str(dbConf.DB_SHMA)+".tbpreguntas_seguridad_ge b on "\
" a.id=b.id_prgnta_sgrdd "\
" where "\
" b.estdo = true "\
+ str(lc_prmtrs)
Cursor = Pconnection.queryFree(StrSql)
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_RGSTRS}, 400)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_ATRZCN}, 400)
def preguntasSeguridad(self):
lc_token = request.form['lc_token']
## Debe existir en la base de datos el token generado, me rotorna el id del login del usuario y si no existe, retorna error
## Consulto 3 preguntas aleatorias de las que se encuentran parametrizadas de acuerdo al id_lgns_ssn si tiene menos de 3
## si existen, retorno las preguntas con su id preguntas, devuelve un error
Cursor = lc_cnctn.queryFree("select "\
" id_lgn_ge "\
"from "\
""+dbConf.DB_SHMA+".tbclaves_tmp "\
"where "\
"token = '"+lc_token+"' "\
"and estdo = true LIMIT 1 ")
if Cursor:
data = json.loads(json.dumps(Cursor[0], indent=2))
##Se valida que existan 6 preguntas parametrizadas
lc_query_cnt = " select count(a.id) as ttl from "+dbConf.DB_SHMA+".tbrespuestas_preguntas_seguridad a "\
" inner join "+dbConf.DB_SHMA+".tbpreguntas_seguridad_ge b on a.id_prgnt_sgrdd_ge = b.id "\
" inner join "+dbConf.DB_SHMA+".tbpreguntas_seguridad c on b.id_prgnta_sgrdd = c.id "\
" where a.id_lgn_accso_ge = "+str(data['id_lgn_ge'])+" and a.estdo = true LIMIT 1 "
Cursor3 = lc_cnctn.queryFree(lc_query_cnt)
if Cursor3:
data3 = json.loads(json.dumps(Cursor3[0], indent=2))
ln_ttl = data3['ttl']
else:
ln_ttl = 0
if (ln_ttl < 6):
return Utils.nice_json(
{
labels.lbl_stts_error:
"No se han definido las 6 preguntas de seguridad, comuníquese con el administrador del sistema."
}, 400)
lc_query_rndm = " select a.id, c.dscrpcn from "+dbConf.DB_SHMA+".tbrespuestas_preguntas_seguridad a "\
" inner join "+dbConf.DB_SHMA+".tbpreguntas_seguridad_ge b on a.id_prgnt_sgrdd_ge = b.id "\
" inner join "+dbConf.DB_SHMA+".tbpreguntas_seguridad c on b.id_prgnta_sgrdd = c.id "\
" where a.id_lgn_accso_ge = "+str(data['id_lgn_ge'])+" and a.estdo = true "\
" order by random() limit 3 "
Cursor2 = lc_cnctn.queryFree(lc_query_rndm)
if Cursor2:
data2 = json.loads(json.dumps(Cursor2, indent=2))
return Utils.nice_json(data2, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: "La cadena Token no es valida."}, 400)
def Sucursales(self):
ln_id_undd_ngco = request.form["id_undd_ngco"]
strSql = " select id as id,nmbre_scrsl as dscrpcn "\
" from "\
" "+str(dbConf.DB_SHMA)+".tbsucursales "\
" where "\
" id_undd_ngco = "+str(ln_id_undd_ngco)+""\
" and estdo = true "\
" order by nmbre_scrsl"
Cursor = lc_cnctn.queryFree(strSql)
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_success: labels.INFO_NO_DTS}, 200)
def listar(self):
#key = request.headers['Authorization']
#validacionSeguridad = ValidacionSeguridad()
lc_rsltdo = requests.post('http://127.0.0.1:5001/api/contratos/listar',
data=request.form)
return Utils.nice_json(lc_rsltdo.json(), 200)
def UnidadesNegocio(self):
ln_id_grpo_emprsrl = request.form["id_grpo_emprsrl"]
strSql = " select id as id,nmbre_rzn_scl as dscrpcn"\
" from "\
" "+str(dbConf.DB_SHMA)+".tbunidades_negocio "\
" where "\
" id_grpo_emprsrl = "+str(ln_id_grpo_emprsrl)+""\
" and estdo = true "\
" order by nmbre_rzn_scl"
Cursor = lc_cnctn.queryFree(strSql)
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_success: labels.INFO_NO_DTS}, 200)
def ObtenerUsuarios(self):
lc_tkn = request.headers['Authorization']
ln_opcn_mnu = request.form["id_mnu_ge"]
validacionSeguridad = ValidacionSeguridad()
val = validacionSeguridad.Principal(lc_tkn, ln_opcn_mnu,
optns.OPCNS_MNU['Usuarios'])
lc_prmtrs = ''
try:
ln_id_lgn_ge = request.form['id_login_ge']
lc_prmtrs = lc_prmtrs + " and a.id = " + ln_id_lgn_ge
except Exception:
pass
try:
lc_lgn = request.form['login']
lc_prmtrs = lc_prmtrs + " and lgn like '%" + lc_lgn + "%' "
except Exception:
pass
try:
ln_id_grpo_emprsrl = request.form['id_grpo_emprsrl']
lc_prmtrs = lc_prmtrs + " and id_grpo_emprsrl = " + ln_id_grpo_emprsrl + " "
except Exception:
pass
if val:
Cursor = lc_cnctn.queryFree(" select "\
" a.id, b.lgn, b.nmbre_usro, b.fto_usro, case when b.estdo = true then 'ACTIVO' else 'INACTIVO' end as estdo "\
" from "\
" "+str(dbConf.DB_SHMA)+".tblogins_ge a inner join "+str(dbConf.DB_SHMA)+".tblogins b on "\
" a.id_lgn = b.id "\
" where "\
" a.id_grpo_emprsrl = "+ln_id_grpo_emprsrl+" "\
+ lc_prmtrs +
" order by "\
" b.lgn")
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_RGSTRS}, 400)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_ATRZCN}, 400)
def FormasPago(self):
ln_id_undd_ngco = request.form["id_undd_ngco"]
strSql = " select frms_pgo_une.id,trim(frms_pgo.dscrpcn) as dscrpcn "\
" from "\
" "+str(dbConf.DB_SHMA)+".tbformas_pago_une as frms_pgo_une "\
" Inner Join "+str(dbConf.DB_SHMA)+".tbformas_pago as frms_pgo "\
" on frms_pgo_une.id_frma_pgo = frms_pgo.id "\
" where frms_pgo_une.id_undd_ngco = "+str(ln_id_undd_ngco)+""\
" and frms_pgo_une.estdo = true and frms_pgo.estdo = true "\
" order by trim(frms_pgo.dscrpcn)"
Cursor = lc_cnctn.queryFree(strSql)
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_success: labels.INFO_NO_DTS}, 200)
def listar(self):
ln_opcn_mnu = request.form["id_mnu_ge"]
key = request.headers['Authorization']
validacionSeguridad = ValidacionSeguridad()
if validacionSeguridad.Principal(key, ln_opcn_mnu,
optns.OPCNS_MNU['Perfiles']):
lc_dta = ''
lc_cdgo = ''
try:
lc_cdgo = request.form["cdgo"]
lc_dta = lc_dta + " and a.cdgo = '" + lc_cdgo + "' "
except Exception:
pass
lc_dscrpcn = ''
try:
lc_dscrpcn = request.form["dscrpcn"]
lc_dta = lc_dta + " and a.dscrpcn like '%" + lc_dscrpcn + "%' "
except Exception:
pass
ln_id_undd_ngco = request.form["id_undd_ngco"]
strSql = " select b.id, "\
" a.cdgo ,a.dscrpcn "\
" ,case when b.estdo = true then 'ACTIVO' else 'INACTIVO' end as estdo "\
" from "\
" "+str(dbConf.DB_SHMA)+".tbperfiles a inner join "+" "+str(dbConf.DB_SHMA)+".tbperfiles_une b on "\
" a.id=b.id_prfl "\
" where "\
" b.id_undd_ngco = "+str(ln_id_undd_ngco) +" "+ lc_dta +""\
" order by a.dscrpcn"
Cursor = lc_cnctn.queryFree(strSql)
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_success: labels.INFO_NO_DTS}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_ATRZCN}, 400)
def CanalRecaudo(self):
ln_id_undd_ngco = request.form["id_undd_ngco"]
strSql = " select cnls_rcdo_une.id,trim(cnls_rcdo.dscrpcn) as dscrpcn "\
" from "\
" "+str(dbConf.DB_SHMA)+".tbcanales_recaudo_une as cnls_rcdo_une "\
" Inner Join "+str(dbConf.DB_SHMA)+".tbcanales_recaudo as cnls_rcdo"\
" on cnls_rcdo_une.id_cnl_rcdo = cnls_rcdo.id "\
" where cnls_rcdo_une.id_undd_ngco = "+str(ln_id_undd_ngco)+""\
" and cnls_rcdo.estdo = true and cnls_rcdo_une.estdo = true "\
"order by trim(cnls_rcdo.dscrpcn)"
Cursor = lc_cnctn.queryFree(strSql)
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_success: labels.INFO_NO_DTS}, 200)
def Perfiles(self):
ln_id_undd_ngco = request.form["id_undd_ngco"]
strSql = " select a.id as id, trim(b.dscrpcn) as dscrpcn "\
" from "\
" "+str(dbConf.DB_SHMA)+".tbperfiles_une as a "\
" Inner Join "+str(dbConf.DB_SHMA)+".tbperfiles as b"\
" on a.id_prfl=b.id "\
" where "\
" a.id_undd_ngco = "+str(ln_id_undd_ngco)+""\
" and a.estdo= true and b.estdo= true "\
" order by trim(b.dscrpcn)"
Cursor = lc_cnctn.queryFree(strSql)
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_success: labels.INFO_NO_DTS}, 200)
def Barrios(self):
ln_id_mncpo_ge = request.form["id_mncpo_ge"]
strSql = " select a.id,trim(b.dscrpcn) as dscrpcn "\
" from "\
" "+str(dbConf.DB_SHMA)+".tbbarrios_ge as a "\
" Inner Join "+str(dbConf.DB_SHMA)+".tbbarrios as b"\
" on a.id_brro = b.id "\
" where "\
" a.id_mncpo_ge = "+str(ln_id_mncpo_ge)+""\
" and a.estdo= true and b.estdo= true "\
" order by trim(b.dscrpcn)"
Cursor = lc_cnctn.queryFree(strSql)
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_success: labels.INFO_NO_DTS}, 200)
def validaclaveTemporal(self):
lc_clve_tmprl = request.form['clve_tmprl']
lc_query = "select "\
"estdo, "\
"crreo_slctnte "\
"from "\
""+dbConf.DB_SHMA+".tbclaves_tmp "\
"where "\
"current_timestamp - fcha_crcn < INTERVAL '30' minute "\
"and estdo = true "\
"and cntrsna ='"+lc_clve_tmprl+"'"
Cursor = lc_cnctn.queryFree(lc_query)
if Cursor:
data_vlda = json.loads(json.dumps(Cursor[0], indent=2))
if data_vlda['estdo']:
return Utils.nice_json({labels.lbl_stts_success: True}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_CLVE_TMP}, 400)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_CLVE_TMP}, 400)
def gestionPermisos(self):
ln_opcn_mnu = request.form["id_mnu_ge"]
ln_id_undd_ngco = request.form["id_undd_ngco"]
ln_id_prfl_une = request.form["id_perfil_une"]
ls_data = request.form["ls_data"]
ls_data_permisos = request.form["ls_data_permisos"]
key = request.headers['Authorization']
validacionSeguridad = ValidacionSeguridad()
if validacionSeguridad.Principal(key, ln_opcn_mnu,
optns.OPCNS_MNU['Perfiles']):
token = validacionSeguridad.ValidacionToken(key)
datosUsuario = validacionSeguridad.ObtenerDatosUsuario(
token['lgn'])[0]
#Obtengo el cursor con todos los elementos de la consulta
Cursor = self.datos_perfil(ln_id_prfl_une, ls_data, 2)
#Obtngo un array Json con las opciones de perfil
data = json.loads(json.dumps(Cursor, indent=2))
lc_query_actlzr = ""
lc_query_insrtr = ""
for obj in data:
if obj["existe"]:
if obj["stdo_envdo"] != obj["estdo"]:
objectValues = {}
objectValues["estdo"] = str(obj["stdo_envdo"])
objectValues["id_lgn_mdfccn_ge"] = str(
datosUsuario["id_lgn_ge"])
objectValues["fcha_mdfccn"] = str(
datetime.datetime.now()).split('.')[0]
clause = "id_prfl_une = " + str(
ln_id_prfl_une) + " AND id_mnu_ge=" + str(
obj["id"])
lc_cnctn.queryUpdate("ssi7x.tbperfiles_une_menu",
objectValues, clause)
else:
if obj["stdo_envdo"]:
objectValues = {}
objectValues["id_prfl_une"] = str(ln_id_prfl_une)
objectValues["id_mnu_ge"] = str(obj["id"])
objectValues["id_lgn_crcn_ge"] = str(
datosUsuario['id_lgn_ge'])
objectValues["id_lgn_mdfccn_ge"] = str(
datosUsuario['id_lgn_ge'])
objectValues["fcha_mdfccn"] = str(
datetime.datetime.now()).split('.')[0]
lc_cnctn.queryInsert("ssi7x.tbperfiles_une_menu",
objectValues)
#TODO:verificar que el persmiso este activo.
lo_data_permisos = json.loads(ls_data_permisos)
for obj in lo_data_permisos:
self.gestion_modos_acceso(obj, datosUsuario)
return Utils.nice_json({labels.lbl_stts_success: "OK"}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_success: errors.ERR_NO_ATRZCN}, 400)
def obtenerOpcionesPerfil(self):
ln_opcn_mnu = request.form["id_mnu_ge"]
key = request.headers['Authorization']
validacionSeguridad = ValidacionSeguridad()
if validacionSeguridad.Principal(key, ln_opcn_mnu,
optns.OPCNS_MNU['Perfiles']):
token = validacionSeguridad.ValidacionToken(key)
datosUsuario = validacionSeguridad.ObtenerDatosUsuario(
token['lgn'])[0]
ln_id_grpo_emprsrl = datosUsuario['id_grpo_emprsrl']
ln_id_prfl_une = request.form["id_prfl_une"]
ln_id_undd_ngco = request.form["id_undd_ngco"]
strSql ="SELECT * FROM( "\
"SELECT m.dscrpcn as text,p.lnk,m.id as id_mnu,m.ordn,p.id_prfl_une_mnu,(case when p.id is not null then true else false end)::boolean as seleccionado "\
",5 AS id_crar, "\
"(select (case when ppm.id is null then false else ppm.estdo end) AS activo from ssi7x.tbpermisos AS prmss "\
"left join ssi7x.tbpermisos_perfiles_menu AS ppm on ppm.id_prmso=prmss.id "\
"where (ppm.id_prfl_une_mnu = p.id_prfl_une_mnu or ppm.id_prfl_une_mnu is null) and prmss.cdgo='01' ) as crar, "\
"6 AS id_act, "\
"(select (case when ppm.id is null then false else ppm.estdo end) AS activo from ssi7x.tbpermisos AS prmss "\
"left join ssi7x.tbpermisos_perfiles_menu AS ppm on ppm.id_prmso=prmss.id "\
"where (ppm.id_prfl_une_mnu = p.id_prfl_une_mnu or ppm.id_prfl_une_mnu is null) and prmss.cdgo='02' ) as actlzr, "\
"7 AS id_anlr, "\
"(select (case when ppm.id is null then false else ppm.estdo end) AS activo from ssi7x.tbpermisos AS prmss "\
"left join ssi7x.tbpermisos_perfiles_menu AS ppm on ppm.id_prmso=prmss.id "\
"where (ppm.id_prfl_une_mnu = p.id_prfl_une_mnu or ppm.id_prfl_une_mnu is null) and prmss.cdgo='03' ) as anlr, "\
"8 AS id_imprmr, "\
"(select (case when ppm.id is null then false else ppm.estdo end) AS activo from ssi7x.tbpermisos AS prmss "\
"left join ssi7x.tbpermisos_perfiles_menu AS ppm on ppm.id_prmso=prmss.id "\
"where (ppm.id_prfl_une_mnu = p.id_prfl_une_mnu or ppm.id_prfl_une_mnu is null) and prmss.cdgo='04' ) as imprmr, "\
"9 AS id_exprtr, "\
"(select (case when ppm.id is null then false else ppm.estdo end) AS activo from ssi7x.tbpermisos AS prmss "\
"left join ssi7x.tbpermisos_perfiles_menu AS ppm on ppm.id_prmso=prmss.id "\
"where (ppm.id_prfl_une_mnu = p.id_prfl_une_mnu or ppm.id_prfl_une_mnu is null) and prmss.cdgo='05' ) as exprtr "\
"FROM (select m.dscrpcn,mg.id,m.ordn from ssi7x.tbmenu m "\
"inner join ssi7x.tbmenu_ge mg on m.id=mg.id_mnu "\
"where mg.id_grpo_emprsrl=2 and m.estdo=true and mg.estdo=true "\
") AS M "\
"left join "\
"(select "\
"m.dscrpcn,mng.id,pum.id AS id_prfl_une_mnu,m.lnk from ssi7x.tbperfiles as p "\
"inner join ssi7x.tbperfiles_une as pu on pu.id_prfl= p.id "\
"inner Join ssi7x.tbperfiles_une_menu as pum ON pu.id= pum.id_prfl_une "\
"inner join ssi7x.tbmenu_ge mng ON pum.id_mnu_ge = mng.id "\
"inner join ssi7x.tbmenu m ON m.id = mng.id_mnu "\
"where pu.id = "+ln_id_prfl_une+" and pu.id_undd_ngco= "+ln_id_undd_ngco+" and mng.id_grpo_emprsrl = " + str(ln_id_grpo_emprsrl) + " "\
"and mng.estdo=true and m.estdo=true and pum.estdo=true "\
")as P ON P.id=M.id "\
") AS H order by H.ordn"
Cursor = lc_cnctn.queryFree(strSql)
if Cursor:
data = json.loads(json.dumps(Cursor, indent=2))
return Utils.nice_json(data, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_success: labels.INFO_NO_DTS}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_ATRZCN}, 400)
def actualizar(self):
lob_rspsta = DatosUpdate(request.form)
if not lob_rspsta.validate():
return self.Utils.nice_json(
{labels.lbl_stts_error: lob_rspsta.errors}, 400)
ln_opcn_mnu = request.form["id_mnu_ge"]
key = request.headers['Authorization']
validacionSeguridad = ValidacionSeguridad()
if validacionSeguridad.Principal(key, ln_opcn_mnu,
optns.OPCNS_MNU['Perfiles']):
token = validacionSeguridad.ValidacionToken(key)
datosUsuario = validacionSeguridad.ObtenerDatosUsuario(
token['lgn'])[0]
lc_cdgo = request.form["cdgo"]
lc_dscrpcn = request.form["dscrpcn"]
ln_id_prfl_une = request.form["id_prfl_une"]
lb_estdo = request.form["estdo"]
ln_id_undd_ngco = request.form['id_undd_ngco']
lc_tbls_query = dbConf.DB_SHMA + ".tbperfiles_une a INNER JOIN " + dbConf.DB_SHMA + ".tbperfiles b on a.id_prfl=b.id "
CursorValidar1 = lc_cnctn.querySelect(
lc_tbls_query, ' b.id ', " a.id <>'" + str(ln_id_prfl_une) +
"' and b.cdgo ='" + str(lc_cdgo) + "' and a.id_undd_ngco ='" +
str(ln_id_undd_ngco) + "'")
CursorValidar2 = lc_cnctn.querySelect(
lc_tbls_query, ' b.id ', " a.id <>'" + str(ln_id_prfl_une) +
"' and b.dscrpcn= '" + str(lc_dscrpcn) +
"' and a.id_undd_ngco ='" + str(ln_id_undd_ngco) + "'")
if CursorValidar1:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_RGSTRO_RPTDO}, 400)
if CursorValidar2:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_RGSTRO_RPTDO}, 400)
arrayValues = {}
arrayValuesDetalle = {}
#Actualizo tabla une
arrayValuesDetalle['id_lgn_mdfccn_ge'] = str(
datosUsuario['id_lgn_ge'])
arrayValuesDetalle['estdo'] = lb_estdo
arrayValuesDetalle['fcha_mdfccn'] = str(fecha_act)
lc_cnctn.queryUpdate(dbConf.DB_SHMA + "." + str('tbperfiles_une'),
arrayValuesDetalle,
'id=' + str(ln_id_prfl_une))
#obtengo id_lgn a partir del id_lgn_ge
Cursor = lc_cnctn.querySelect(
str(dbConf.DB_SHMA) + '.tbperfiles_une', 'id_prfl',
"id=" + ln_id_prfl_une)
if Cursor:
data = json.loads(json.dumps(Cursor[0], indent=2))
ln_id_prfl = data['id_prfl']
#Actualizo tabla principal
arrayValues['cdgo'] = str(lc_cdgo)
arrayValues['dscrpcn'] = lc_dscrpcn
arrayValues['estdo'] = lb_estdo
lc_cnctn.queryUpdate(
str(dbConf.DB_SHMA) + "." + str('tbperfiles'), arrayValues,
'id =' + str(ln_id_prfl))
return Utils.nice_json(
{labels.lbl_stts_success: labels.SCCSS_ACTLZCN_EXTSA}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_PRBLMS_GRDR}, 400)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_ATRZCN}, 400)
from flask_restful import Resource
from pyasn1.type.univ import Null
from wtforms import Form
from wtforms import validators
from wtforms import StringField
from wtforms import IntegerField
from Static.ConnectDB import ConnectDB # @UnresolvedImport
from Static.Utils import Utils # @UnresolvedImport
import Static.config_DB as dbConf # @UnresolvedImport
import Static.errors as errors # @UnresolvedImport
import Static.labels as labels # @UnresolvedImport
import Static.opciones_higia as optns # @UnresolvedImport
from ValidacionSeguridad import ValidacionSeguridad # @UnresolvedImport
#Declaracion de variables globales
Utils = Utils()
lc_cnctn = ConnectDB()
fecha_act = time.ctime()
#clase de llamado para validar datos desde labels
class DatosPerfil(Form):
cdgo = StringField(labels.lbl_cdgo_prfl,
[validators.DataRequired(message=errors.ERR_NO_Cdgo)])
dscrpcn = StringField(
labels.lbl_dscrpcn_prfl,
[validators.DataRequired(message=errors.ERR_NO_Dscrpcn)])
class DatosUpdate(Form):
cdgo = StringField(labels.lbl_cdgo_prfl,
def actualizarContrasena(self):
lc_clve_tmprl = request.form['clve_tmprl']
lc_nva_cntrsna = request.form['nva_cntrsna']
lc_rnva_cntrsna = request.form['rnva_cntrsna']
responsed = self.validaclaveTemporal()
ld_fcha_actl = time.ctime()
lc_cntrsna = hashlib.md5(lc_nva_cntrsna.encode('utf-8')).hexdigest()
'''
Validaa nueva contrasena y la clave temporal tiene que ser iguales
'''
if lc_nva_cntrsna != lc_rnva_cntrsna:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_CNCD_CNTSNA}, 400)
'''
Validar que la cadena que se recibe de codigo, se encuentre en la base de datos y vigente por los 30 minutos
'''
if responsed.status_code != 200:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_CLVE_TMP}, 400)
'''
Validar que la contrasena cumpla con el Patron de contraseas
'''
if not re.match(conf.EXPRESION_CLAVE_USUARIO, lc_nva_cntrsna):
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_NO_PTRN_CLVE}, 400)
'''
Actualizar el usuario que coincida con el codigo registrado
'''
lc_query_usro = "select lgn, id_lgn, id_lgn_ge from ( "\
" select "\
" case when emplds_une.id is not null then "\
" emplds.crro_elctrnco "\
" else "\
" prstdr.crro_elctrnco "\
" end as crro_elctrnco,lgn.lgn, lgn.id id_lgn, lgn_ge.id id_lgn_ge "\
" from "+dbConf.DB_SHMA+".tblogins_ge lgn_ge "\
" left join "+dbConf.DB_SHMA+".tblogins lgn on lgn.id = lgn_ge.id_lgn "\
" left join "+dbConf.DB_SHMA+".tbempleados_une emplds_une on emplds_une.id_lgn_accso_ge = lgn_ge.id "\
" left join "+dbConf.DB_SHMA+".tbempleados emplds on emplds.id = emplds_une.id_empldo "\
" left join "+dbConf.DB_SHMA+".tbprestadores prstdr on prstdr.id_lgn_accso_ge = lgn_ge.id "\
" left join "+dbConf.DB_SHMA+".tbcargos_une crgo_une on crgo_une.id = emplds_une.id_crgo_une "\
" left join "+dbConf.DB_SHMA+".tbcargos crgo on crgo.id = crgo_une.id_crgo "\
" left join "+dbConf.DB_SHMA+".tbunidades_negocio undd_ngco on undd_ngco.id = emplds_une.id_undd_ngco "\
" inner join "+dbConf.DB_SHMA+".tblogins_perfiles_sucursales as prfl_scrsls on prfl_scrsls.id_lgn_ge = lgn_ge.id and prfl_scrsls.mrca_scrsl_dfcto is true "\
" inner join "+dbConf.DB_SHMA+".tbperfiles_une as prfl_une on prfl_une.id = prfl_scrsls.id_prfl_une "\
" where id_mtvo_rtro_une is null) as test "\
" where crro_elctrnco = (select "\
" crreo_slctnte "\
" from "\
" "+dbConf.DB_SHMA+".tbclaves_tmp "\
" where "\
" current_timestamp - fcha_crcn < INTERVAL '30' minute "\
" and estdo = true and cntrsna = '"+lc_clve_tmprl+"') "
Cursor_clv_tmp = lc_cnctn.queryFree(lc_query_usro)
if Cursor_clv_tmp:
data_usro = json.loads(json.dumps(Cursor_clv_tmp[0], indent=2))
'''
Validar el historial de contraseñas, si retorna un true, permite cambiar la contraseña
'''
if self.validaHistorialContrasena(data_usro['id_lgn_ge'],
str(lc_cntrsna)) == True:
return Utils.nice_json(
{
labels.lbl_stts_error:
"Esta contraseña ha sido usada anteriormente, intenta con una contraseña diferente."
}, 400)
else:
#Actualiza login
la_clmns_actlzr_lgn = {}
la_clmns_actlzr_lgn['id'] = str(data_usro['id_lgn'])
la_clmns_actlzr_lgn['cntrsna'] = str(lc_cntrsna)
self.UsuarioActualizaRegistro(la_clmns_actlzr_lgn, 'tblogins')
la_clmns_actlzr_lgn_ge = {}
la_clmns_actlzr_lgn_ge['id'] = str(data_usro['id_lgn_ge'])
la_clmns_actlzr_lgn_ge['fcha_mdfccn'] = str(ld_fcha_actl)
self.UsuarioActualizaRegistro(la_clmns_actlzr_lgn_ge,
'tblogins_ge')
'''
Insertar el registro de historial de contraseñas
'''
la_clmns_insrtr_hstrl_ge = {}
la_clmns_insrtr_hstrl_ge['fcha_crcn'] = str(ld_fcha_actl)
la_clmns_insrtr_hstrl_ge['fcha_mdfccn'] = str(ld_fcha_actl)
la_clmns_insrtr_hstrl_ge['id_lgn_crcn_ge'] = str(
data_usro['id_lgn_ge'])
la_clmns_insrtr_hstrl_ge['id_lgn_mdfccn_ge'] = str(
data_usro['id_lgn_ge'])
la_clmns_insrtr_hstrl_ge['id_lgn_ge'] = str(
data_usro['id_lgn_ge'])
la_clmns_insrtr_hstrl_ge['cntrsna'] = str(lc_cntrsna)
self.UsuarioInsertaRegistro(la_clmns_insrtr_hstrl_ge,
'tbhistorial_claves')
return Utils.nice_json({labels.lbl_stts_success: True}, 200)
'''
def crear(self):
ln_opcn_mnu = request.form["id_mnu_ge"]
key = request.headers['Authorization']
validacionSeguridad = ValidacionSeguridad()
lb_val = validacionSeguridad.Principal(key, ln_opcn_mnu,
optns.OPCNS_MNU['PreguntaSg'])
u = Acceso(request.form)
if not u.validate():
return Utils.nice_json({labels.lbl_stts_error: u.errors}, 400)
if lb_val:
token = validacionSeguridad.ValidacionToken(key)
lc_datosUsuario = validacionSeguridad.ObtenerDatosUsuario(
token['lgn'])[0]
a_prgnta = {}
a_prgnta_ge = {}
a_prgnta['cdgo'] = request.form['lc_cdgo']
a_prgnta['dscrpcn'] = request.form['lc_dscrpcn']
a_prgnta['fcha_crcn'] = str(ld_fcha_actl)
a_prgnta['fcha_mdfccn'] = str(ld_fcha_actl)
a_prgnta['id_lgn_crcn_ge'] = str(lc_datosUsuario['id_lgn_ge'])
a_prgnta['id_lgn_mdfccn_ge'] = str(lc_datosUsuario['id_lgn_ge'])
# validacion para evitar registros duplicados
Cursor1 = Pconnection.querySelect(
dbConf.DB_SHMA + '.tbpreguntas_seguridad', 'cdgo',
"cdgo='" + str(a_prgnta['cdgo']) + "'")
Cursor2 = Pconnection.querySelect(
dbConf.DB_SHMA + '.tbpreguntas_seguridad', 'dscrpcn',
"dscrpcn ='" + str(a_prgnta['dscrpcn']) + "'")
if Cursor1 and Cursor2:
return Utils.nice_json(
{
labels.lbl_stts_error:
labels.lbl_cdgo + " y " + labels.lbl_prgnta + " " +
errors.ERR_RGSTRO_RPTDO
}, 400)
elif Cursor2:
return Utils.nice_json(
{
labels.lbl_stts_error:
labels.lbl_prgnta + " " + errors.ERR_RGSTRO_RPTDO
}, 400)
elif Cursor1:
return Utils.nice_json(
{
labels.lbl_stts_error:
labels.lbl_cdgo + " " + errors.ERR_RGSTRO_RPTDO
}, 400)
ln_id_prgnta = self.crearPregunta_seguridad(
a_prgnta, 'tbpreguntas_seguridad')
a_prgnta_ge['id_prgnta_sgrdd'] = str(ln_id_prgnta)
a_prgnta_ge['id_lgn_crcn_ge'] = str(lc_datosUsuario['id_lgn_ge'])
a_prgnta_ge['id_lgn_mdfccn_ge'] = str(lc_datosUsuario['id_lgn_ge'])
a_prgnta_ge['fcha_crcn'] = str(ld_fcha_actl)
a_prgnta_ge['fcha_mdfccn'] = str(ld_fcha_actl)
a_prgnta_ge['id_lgn_ge'] = str(lc_datosUsuario['id_lgn_ge'])
ln_prgnts_sg_ge = self.crearPregunta_seguridad(
a_prgnta_ge, 'tbpreguntas_seguridad_ge')
return Utils.nice_json(
{
labels.lbl_stts_success: labels.SCCSS_RGSTRO_EXTSO,
'id': ln_prgnts_sg_ge
}, 200)
return Utils.nice_json({labels.lbl_stts_error: errors.ERR_NO_ATRZCN},
400)
def reponderPreguntasSeguridad(self):
lc_token = request.form['token']
ln_prgnta_0 = request.form['txt_idpr0']
ln_prgnta_1 = request.form['txt_idpr1']
ln_prgnta_2 = request.form['txt_idpr2']
lc_rspsta_0 = request.form['txt_pregunta0']
lc_rspsta_0 = hashlib.md5(lc_rspsta_0.encode('utf-8')).hexdigest()
lc_rspsta_1 = request.form['txt_pregunta1']
lc_rspsta_1 = hashlib.md5(lc_rspsta_1.encode('utf-8')).hexdigest()
lc_rspsta_2 = request.form['txt_pregunta2']
lc_rspsta_2 = hashlib.md5(lc_rspsta_2.encode('utf-8')).hexdigest()
## Obtengo el id delusuario de acuerdo al token
lc_query_clve_tmp = " select id, id_lgn_ge, crreo_slctnte, cntrsna from " + dbConf.DB_SHMA + ".tbclaves_tmp where token = '" + lc_token + "' and estdo = true limit 1;"
Cursor_clve_tmp = lc_cnctn.queryFree(lc_query_clve_tmp)
if Cursor_clve_tmp:
data_tkn = json.loads(json.dumps(Cursor_clve_tmp[0], indent=2))
id_lgn_ge = data_tkn['id_lgn_ge']
id_claves_tmp = data_tkn['id']
lc_crro_slctnte = data_tkn['crreo_slctnte']
lc_cntrsna = data_tkn['cntrsna']
else:
return Utils.nice_json(
{labels.lbl_stts_error: "La cadena Token no es valida."}, 400)
## Validar la cantidad de intentos
#validamos que no tenga una clave temporal ya generada al menos 3 intentos en 30 min
lc_query = "select "\
"count(estdo) as count "\
"from "\
""+dbConf.DB_SHMA+".tbclaves_tmp "\
"where "\
"current_timestamp - fcha_crcn < INTERVAL '30' minute "\
"and estdo = true "\
"and crreo_slctnte ='"+lc_crro_slctnte+"'"
Cursor = lc_cnctn.queryFree(lc_query)
if Cursor:
data_vlda = json.loads(json.dumps(Cursor[0], indent=2))
if data_vlda['count'] >= 3:
return Utils.nice_json(
{
labels.lbl_stts_error:
'Se han realizado 3 intentos en los ultimos minutos, intentalo más tarde'
}, 400)
## Valido que la respuesta sea la misma que tiene parametrizada en la tabla de respuestas
## Las 3 respuestas exitosas, retornan un True
if self.validaPreguntaSeguridad(
id_lgn_ge, ln_prgnta_0,
lc_rspsta_0) == True and self.validaPreguntaSeguridad(
id_lgn_ge, ln_prgnta_1,
lc_rspsta_1) == True and self.validaPreguntaSeguridad(
id_lgn_ge, ln_prgnta_2, lc_rspsta_2) == True:
## Activo el registro de la clave temporal de acuerdo al tocken si las 3 preguntas son exitosas
ld_fcha_actl = time.ctime()
la_clmns_actlzr_rps = {}
la_clmns_actlzr_rps['id'] = str(id_claves_tmp)
la_clmns_actlzr_rps['estdo'] = 'true'
la_clmns_actlzr_rps['fcha_mdfccn'] = str(ld_fcha_actl)
self.UsuarioActualizaRegistro(la_clmns_actlzr_rps, 'tbclaves_tmp')
##consultar login y numero de cel para los mensajes
lc_query_lgn = " select lgn, tlfno_cllr, id from ( "\
" select "\
" case when emplds_une.id is not null then "\
" trim(emplds.tlfno_cllr) "\
" else "\
" trim(prstdr.tlfno_cllr) "\
" end as tlfno_cllr, "\
" lgn.lgn, lgn_ge.id "\
" from "+dbConf.DB_SHMA+".tblogins_ge lgn_ge "\
" left join "+dbConf.DB_SHMA+".tblogins lgn on lgn.id = lgn_ge.id_lgn "\
" left join "+dbConf.DB_SHMA+".tbempleados_une emplds_une on emplds_une.id_lgn_accso_ge = lgn_ge.id "\
" left join "+dbConf.DB_SHMA+".tbempleados emplds on emplds.id = emplds_une.id_empldo "\
" left join "+dbConf.DB_SHMA+".tbprestadores prstdr on prstdr.id_lgn_accso_ge = lgn_ge.id "\
" left join "+dbConf.DB_SHMA+".tbcargos_une crgo_une on crgo_une.id = emplds_une.id_crgo_une "\
" left join "+dbConf.DB_SHMA+".tbcargos crgo on crgo.id = crgo_une.id_crgo "\
" left join "+dbConf.DB_SHMA+".tbunidades_negocio undd_ngco on undd_ngco.id = emplds_une.id_undd_ngco "\
" inner join "+dbConf.DB_SHMA+".tblogins_perfiles_sucursales as prfl_scrsls on prfl_scrsls.id_lgn_ge = lgn_ge.id and prfl_scrsls.mrca_scrsl_dfcto is true "\
" inner join "+dbConf.DB_SHMA+".tbperfiles_une as prfl_une on prfl_une.id = prfl_scrsls.id_prfl_une "\
" where id_mtvo_rtro_une is null) as test "\
" where id = "+str(id_lgn_ge)+" LIMIT 1 "
Cursor_lgn = lc_cnctn.queryFree(lc_query_lgn)
if Cursor_lgn:
data_lgn = json.loads(json.dumps(Cursor_lgn[0], indent=2))
lc_lgn = data_lgn['lgn']
ln_cllr = data_lgn['tlfno_cllr']
## Cuando retorne 200, entonces se genera el proceso que habia anteriormente, que envia por sms y correo electronico y redireccoina a ingresar codigo de seguridad
asunto = "Clave Temporal de Acceso Higia SSI"
mensaje = "Cordial saludo "+lc_lgn+","\
"<p>La clave temporal auto generada por el sistema estará vigente por 30 minutos</p>"\
"<br>"\
"<b>Clave Generada:</b>"+str(lc_cntrsna)
correo.enviarCorreo(lc_crro_slctnte, asunto, mensaje)
#ENVIO DEL CODIGO POR MENSAJE DE TEXTO
lc_sms = "La clave temporal generada por el sistema estará vigente por 30 minutos. Clave Generada:" + str(
lc_cntrsna)
#lc_sms = lc_sms.replace(" ", "+")
lc_stts_web_srvcs_SMS = Utils.webServiceSMS(
conf.URL_WS_SMS, ln_cllr, lc_sms, conf.LGN_USRO_WS_SMS,
conf.CNTRSNA_USRO_WS_SMS)
return Utils.nice_json(
{
labels.lbl_stts_success:
'Clave Temporal Generada con Exito! Redireccionando Espera un Momento...'
}, 200)
else:
return Utils.nice_json(
{
labels.lbl_stts_error:
"Error en una respuesta de las preguntas de seguridad"
}, 400)
def crear(self):
lob_rspsta = DatosPerfil(request.form)
if not lob_rspsta.validate():
return self.Utils.nice_json(
{labels.lbl_stts_error: lob_rspsta.errors}, 400)
ln_opcn_mnu = request.form["id_mnu_ge"]
ln_id_undd_ngco = request.form["id_undd_ngco"]
key = request.headers['Authorization']
validacionSeguridad = ValidacionSeguridad()
if validacionSeguridad.Principal(key, ln_opcn_mnu,
optns.OPCNS_MNU['Perfiles']):
token = validacionSeguridad.ValidacionToken(key)
datosUsuario = validacionSeguridad.ObtenerDatosUsuario(
token['lgn'])[0]
arrayValues = {}
arrayValues['cdgo'] = request.form["cdgo"]
arrayValues['dscrpcn'] = request.form["dscrpcn"]
# validacion para evitar registros duplicados, se verifica que el codigo y la descripcion no existan en otros registros
Cursor1 = lc_cnctn.querySelect(
dbConf.DB_SHMA + '.tbperfiles', 'cdgo',
"cdgo='" + str(arrayValues['cdgo']) + "'")
Cursor2 = lc_cnctn.querySelect(
dbConf.DB_SHMA + '.tbperfiles', 'dscrpcn',
"dscrpcn ='" + arrayValues['dscrpcn'] + "'")
if Cursor1 and Cursor2:
return Utils.nice_json(
{
labels.lbl_stts_error:
labels.lbl_cdgo_prfl + " " + labels.lbl_dscrpcn_prfl +
" " + errors.ERR_RGSTRO_RPTDO
}, 400)
if Cursor1:
return Utils.nice_json(
{
labels.lbl_stts_error:
labels.lbl_cdgo_prfl + " " + errors.ERR_RGSTRO_RPTDO
}, 400)
if Cursor2:
return Utils.nice_json(
{
labels.lbl_stts_error:
labels.lbl_dscrpcn_prfl + " " + errors.ERR_RGSTRO_RPTDO
}, 400)
ln_id_prfl = lc_cnctn.queryInsert(dbConf.DB_SHMA + ".tbperfiles",
arrayValues, 'id')
if ln_id_prfl:
arrayValuesDetalle = {}
arrayValuesDetalle['id_prfl'] = str(ln_id_prfl)
arrayValuesDetalle['id_undd_ngco'] = str(ln_id_undd_ngco)
arrayValuesDetalle['id_lgn_crcn_ge'] = str(
datosUsuario['id_lgn_ge'])
arrayValuesDetalle['id_lgn_mdfccn_ge'] = str(
datosUsuario['id_lgn_ge'])
arrayValuesDetalle['fcha_crcn'] = str(fecha_act)
arrayValuesDetalle['fcha_mdfccn'] = str(fecha_act)
ln_id_prfl_une = lc_cnctn.queryInsert(
dbConf.DB_SHMA + ".tbperfiles_une", arrayValuesDetalle,
'id')
return Utils.nice_json(
{
labels.lbl_stts_success: labels.SCCSS_RGSTRO_EXTSO,
"id": str(ln_id_prfl_une)
}, 200)
else:
return Utils.nice_json(
{labels.lbl_stts_error: errors.ERR_PRBLMS_GRDR}, 400)
else:
return Utils.nice_json(
{labels.lbl_stts_success: errors.ERR_NO_ATRZCN}, 400)
class ValidacionSeguridad(Resource):
Utils = Utils()
C = ConnectDB()
def Principal(self, key, id_mnu_ge, sttc_mnu):
if not key and id_mnu_ge:
return False
DatosUsuario = self.ValidacionToken(key)
if DatosUsuario:
if int(sttc_mnu) == int(id_mnu_ge):
lo_datos = self.validaUsuario(DatosUsuario['lgn'])
return self.ValidaOpcionMenu(lo_datos['id_prfl_scrsl'],
id_mnu_ge)
else:
return False
else:
False
def validaUsuario(self, usuario):
IdUsuarioGe = json.loads(
json.dumps(self.ObtenerDatosUsuario(usuario)[0], indent=2))
strQuery = "SELECT "\
" a.id as id_prfl_scrsl,"\
" b.nmbre_scrsl as nmbre_scrsl,"\
" c.estdo as estdo ,"\
" b.id as id_scrsl "\
" FROM "+confDB.DB_SHMA+".tblogins_perfiles_sucursales a"\
" left JOIN "+confDB.DB_SHMA+".tbsucursales b on a.id_scrsl=b.id"\
" left join "+confDB.DB_SHMA+".tblogins_ge c on c.id = a.id_lgn_ge"\
" WHERE a.id_lgn_ge = " + str(IdUsuarioGe['id_lgn_ge']) + " and a.mrca_scrsl_dfcto is true"
Cursor = self.C.queryFree(strQuery)
if Cursor:
data = json.loads(json.dumps(Cursor[0], indent=2))
if data['estdo']:
return data
else:
return errors.ERR_NO_USRO_INCTVO
else:
return errors.ERR_NO_TNE_PRFL
def ValidacionToken(self, key):
try:
token = self.C.querySelect(confDB.DB_SHMA + '.tbgestion_accesos',
"token", "key='" + key +
"' and estdo is true")[0]
decode = jwt.decode(token["token"], conf.SS_TKN_SCRET_KEY + key,
'utf-8')
return decode
except jwt.exceptions.ExpiredSignatureError:
return None
def ValidaOpcionMenu(self, id_lgn_prfl_scrsl, id_mnu_ge):
Cursor = self.C.queryFree(" select a.id "\
" from "+confDB.DB_SHMA+".tblogins_perfiles_menu a inner join "\
" "+confDB.DB_SHMA+".tblogins_perfiles_sucursales b "\
" on a.id_lgn_prfl_scrsl = b.id inner join "\
" "+confDB.DB_SHMA+".tblogins_ge c on c.id = b.id_lgn_ge inner join "\
" "+confDB.DB_SHMA+".tbmenu_ge d on d.id = a.id_mnu_ge inner join "\
" "+confDB.DB_SHMA+".tbmenu e on e.id = d.id_mnu "\
" where c.estdo=true "\
" and b.estdo=true "\
" and a.estdo=true "\
" and d.id = " + str(id_mnu_ge) + " and a.id_lgn_prfl_scrsl = " + str(id_lgn_prfl_scrsl))
if Cursor:
return True
else:
return False
def ObtenerDatosUsuario(self, usuario):
cursor = self.C.queryFree(" select "\
" case when emplds_une.id is not null then "\
" concat_ws("\
" ' ',"\
" emplds.prmr_nmbre,"\
" emplds.sgndo_nmbre,"\
" emplds.prmr_aplldo,"\
" emplds.sgndo_aplldo)"\
" else"\
" prstdr.nmbre_rzn_scl"\
" end as nmbre_cmplto,"\
" case when emplds_une.id is not null then"\
" emplds.crro_elctrnco"\
" else" \
" prstdr.crro_elctrnco"\
" end as crro_elctrnco,"\
" lgn_ge.id as id_lgn_ge, "\
" lgn.lgn as lgn, "\
" crgo.dscrpcn as crgo, "\
" lgn.fto_usro as fto_usro, "\
"(Case when emplds_une.id_undd_ngco is null THEN "\
"prfl_une.id_undd_ngco "\
" ELSE "\
" emplds_une.id_undd_ngco "\
" END "\
" ) as id_undd_ngco,"\
" lgn_ge.id_grpo_emprsrl as id_grpo_emprsrl, "
" lgn_ge.cmbo_cntrsna as cmbo_cntrsna "
" from "+confDB.DB_SHMA+".tblogins_ge lgn_ge " \
" left join "+confDB.DB_SHMA+".tblogins lgn on lgn.id = lgn_ge.id_lgn " \
" left join "+confDB.DB_SHMA+".tbempleados_une emplds_une on emplds_une.id_lgn_accso_ge = lgn_ge.id " \
" left join "+confDB.DB_SHMA+".tbempleados emplds on emplds.id = emplds_une.id_empldo " \
" left join "+confDB.DB_SHMA+".tbprestadores prstdr on prstdr.id_lgn_accso_ge = lgn_ge.id " \
" left join "+confDB.DB_SHMA+".tbcargos_une crgo_une on crgo_une.id = emplds_une.id_crgo_une " \
" left join "+confDB.DB_SHMA+".tbcargos crgo on crgo.id = crgo_une.id_crgo " \
" left join "+confDB.DB_SHMA+".tbunidades_negocio undd_ngco on undd_ngco.id = emplds_une.id_undd_ngco " \
" inner join "+confDB.DB_SHMA+".tblogins_perfiles_sucursales as prfl_scrsls on prfl_scrsls.id_lgn_ge = lgn_ge.id and prfl_scrsls.mrca_scrsl_dfcto is true "\
" inner join "+confDB.DB_SHMA+".tbperfiles_une as prfl_une on prfl_une.id = prfl_scrsls.id_prfl_une "\
" where lgn.lgn = '" + usuario + "' and id_mtvo_rtro_une is null")
return cursor