B EÍÃdÿ“ã@sÌddlZddlmZdZdZdZdZdZdZd Zd d „Z d d „Z dd„Z dd„Z dd„Z dd„ZdZdedZdZdZdedZdedZdedZd edZd!ed"Zd#Zd$Zd%Zd&Zd'ZdS)(éN)ÚdatetimeÚiXZgestionZ wsconsumerZ2019wsConsumer2019ztelefonica.destra.net.brz Web Servicec OsTg}xJ|D]B\}}y|||ƒs*| |¡Wq tk rJ| |¡Yq Xq W|S)N)ÚappendÚKeyError)Z validatorsÚvaluesÚinvalidÚ attributeZ validator©r ú.C:\Sistemas\Destra\wsDestra\resources\utils.pyÚ validate_argss r cCs$d}yt ||¡dSdSdS)Nz%Y-%m-%dTF)rÚstrptime)Zarg_dateZformator r r Ú validate_dates  r cCsy t|ƒdSdSdS)NTF)Úint)Zarg_intr r r Úvalidate_integer s rcCs| ¡dkp| ¡dkS)NÚTRUEÚFALSE)Úupper)Zarg_boolr r r Úvalidate_boolean'srcGs0x&|D]}y | ¡WqYqXqW||fS)N)Úclose)ÚoutputÚ status_codeÚ resourcesÚresourcer r r Úclose_resources*s    rcCs&t dtdtdtdt¡S)Nz dbname = z user = z password = z host = )Úpsycopg2ÚconnectÚdbname_gestionÚ user_gestionÚpassword_gestionÚ host_gestionr r r r Ú create_conn2sr a4Select wsc.id_cliente From usuario_ws_cliente wsc, cliente cli, cliente_grupo gru Where (cli.id_cliente = wsc.id_cliente) and (gru.id_grupo_cliente = cli.id_grupo_cliente) and (gru.integracao_ws = true) and (wsc.login = %s)aÒ Select clc.id_check_list, Trim(Cast(Coalesce(gru.codigo_sap, '' ) as VarChar(5) )) as COD_EMPRESA, Trim(Cast(to_char(Coalesce(cli.id_cliente, '0' ), '0000' ) as VarChar(5))) as COD_FILIAL, Cast(doc.cod_documento as VarChar(50)) as CONTRATO, Trim(to_char(Coalesce((fun.id_funcionario+91000000), '0' ), '00000000')) as COD_FUNCIONARIO, Cast( (Case When cl.id_empresa_contratante is not null Then contte.nome Else contda.nome End) as VarChar(150)) as FORNECEDOR, (Case When clc.data is not null Then Cast(to_char(clc.data, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DATA_CANCELAMENTO, Cast(clc.hora as varchar(8)) as HORA_CANCELAMENTO, (Case When lfe.dt_demissao is not null Then Cast(to_char(lfe.dt_demissao, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DT_DEMISSAO, Cast( (Case When lfe.dt_demissao is NOT null then 'D' Else 'I' end) as Char(1)) as status From check_list_cancelado clc, check_list cl left join empresa_terceirizada contte on (contte.id_empresa_terceirizada = cl.id_empresa_contratante), documento_contratacao doc, ligacao_func_emp_cliente lfec, cliente cli, cliente_grupo gru, ligacao_func_emp lfe, empresa_terceirizada contda, funcionario fun Where (clc.id_check_list in (Select id_check_list from check_list_cancelado where data between %s and %s)) and (cl.concluido is true) and (clc.enviar_notificacao is true) and (cl.id_check_list = clc.id_check_list) and (cl.dt_termino >= current_date) and (doc.id_documento_contratacao = cl.id_documento_contratacao) and (lfec.id_ligacao_func_emp_cliente = cl.id_ligacao_func_emp_cliente) and (cli.id_cliente = lfec.id_cliente) and (gru.id_grupo_cliente = cli.id_grupo_cliente) and (lfe.id_ligacao_func_emp = lfec.id_ligacao_func_emp) and (contda.id_empresa_terceirizada = lfe.id_empresa_terceirizada) and (fun.id_funcionario = lfe.id_funcionario) and (gru.integracao_ws = true) and (cli.id_cliente in (zb) ) and (clc.enviou_ws = %s) and (clc.data between %s and %s)a(SELECT cla.id_check_list, Cast(nor.descricao as VarChar(50) ) as NOME_REQUISITO, Trim(Cast(to_char(nor.id_norma_regulamentadora , '00000000') as VarChar(16))) as COD_REQUISITO, (Case When cla.dt_bloqueio is not null Then Cast(to_char(cla.dt_bloqueio, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DATA_VALIDADE, Cast( (Case When nor.bloqueia_acesso is true then '1' Else '0' end) as Char(1)) as BLOQUEIA_ACESSO FROM check_list_atividades cla, norma_regulamentadora nor WHERE (nor.id_norma_regulamentadora = cla.id_norma_regulamentadora) AND (cla.id_check_list in %s ) ) UNION (SELECT cl.id_check_list, tre.descricao NOME_REQUISITO, '99' || Trim(Cast(to_char(tre.id_treinamento , '000000') as VarChar(7))) as COD_REQUISITO, (Case When fte.dt_validade is not null Then Cast(to_char(fte.dt_validade, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DATA_VALIDADE, Cast('0' as Char(1)) as BLOQUEIA_ACESSO FROM check_list cl, check_list_detalhe det, ligacao_func_emp_cliente lfec, ligacao_func_emp lfe, funcionario_treinamento_especial fte, treinamento tre WHERE (cl.id_check_list in %s) AND (det.id_check_list = cl.id_check_list) AND (lfec.id_ligacao_func_emp_cliente = cl.id_ligacao_func_emp_cliente) AND (lfe.id_ligacao_func_emp = lfec.id_ligacao_func_emp) AND (fte.id_funcionario = lfe.id_funcionario) AND (fte.dt_entrega <= det.dt_conclusao) AND (tre.id_treinamento = fte.id_treinamento) ) ORDER BY NOME_REQUISITO a¹ Select det.id_check_list, cli.id_cliente as id_cliente_destra, Trim(Cast(to_char(Coalesce(cli.id_cliente, '0' ), '0000') as VarChar(5))) as COD_FILIAL, to_char(Coalesce((fun.id_funcionario+91000000) , '0' ), '00000000') as COD_FUNCIONARIO, Cast(Coalesce(fun.nome, '' ) as VarChar(40)) as NOME_FUNCIONARIO, Cast(Coalesce(fun.cpf_cnpj, '' ) as VarChar(15)) as CPF_FUNCIONARIO, Cast(to_char(cl.dt_inicio, 'YYYYMMDD') as VarChar(8)) as DATA_INI_CHECK_LIST, Cast(to_char(cl.dt_termino, 'YYYYMMDD') as VarChar(8)) as DATA_FIM_CHECK_LIST, Cast(to_char(det.dt_send_ws, 'YYYYMMDD') as VarChar(8)) as DATA_ENVIO, Cast(det.hr_send_ws as varchar(8)) as HORA_ENVIO, Cast(to_char(det.dt_check_ws, 'YYYYMMDD') as VarChar(8)) as DATA_MARCACAO, Cast(det.hr_check_ws as varchar(8)) as HORA_MARCACAO, Cast(to_char(det.dt_conclusao, 'YYYYMMDD') as VarChar(8)) as DATA_LIBERACAO, Cast(det.hr_conclusao as varchar(8)) as HORA_LIBERACAO, Cast(to_char( cl.dt_inclusao, 'YYYYMMDD') as VarChar(8)) as DATA_INCLUSAO, Cast(cl.hr_inclusao as varchar(8)) as HORA_INCLUSAO, Cast(to_char( cl.dt_alteracao, 'YYYYMMDD') as VarChar(8)) as DATA_ALTERACAO, Cast(cl.hr_alteracao as varchar(8)) as HORA_ALTERACAO From check_list_detalhe det, check_list cl, ligacao_func_emp_cliente lfec, ligacao_func_emp lfe, funcionario fun, cliente cli Where (det.dt_send_ws between %s and %s) and (cl.id_check_list = det.id_check_list) and (lfec.id_ligacao_func_emp_cliente = cl.id_ligacao_func_emp_cliente) and (cli.id_cliente = lfec.id_cliente) and (lfe.id_ligacao_func_emp = lfec.id_ligacao_func_emp) and (fun.id_funcionario = lfe.id_funcionario) a~' Select cl.id_check_list, cli.id_cliente as id_cliente_destra, Cast(Coalesce(gru.codigo_sap, '' ) as VarChar(4) ) as COD_EMPRESA, Trim(Cast(to_char(Coalesce(cli.id_cliente, '0' ), '0000') as VarChar(5))) as COD_FILIAL, Cast(Coalesce(dir.descricao, '' ) as VarChar(50)) as DIRETORIA, Cast(Coalesce(are.codigo, '') as VarChar(50)) as SECAO, Cast(Coalesce(loc.codigo_sap, '' ) as VarChar(14)) as AREA, Cast(Coalesce(ges.codigo_sap, '' ) as VarChar(14) ) as GESTOR, Cast(Coalesce(dctp.descricao, '' ) as VarChar(50)) as CONTRATO_TIPO_TRABALHO, Cast(Coalesce(doc.cod_documento, '' ) as VarChar(50)) as CONTRATO, Cast(Coalesce(replace(replace(tex.desc_servicos, chr(13),' '), chr(10),''), ' ' ) as VarChar(100)) as ESCOPO, (Case When doc.dt_inicio is not null Then Cast(to_char(doc.dt_inicio, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DATA_INI_CONTRATO, Cast(to_char(det.dt_conclusao, 'YYYYMMDD') as VarChar(8)) as DATA_LIBERACAO, Cast(det.hr_conclusao as varchar(8)) as HORA_LIBERACAO, (Case When doc.dt_termino is not null Then Cast(to_char(doc.dt_termino, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DATA_FIM_CONTRATO, Cast( Coalesce( (Case When doc.alimenta_subsidiada is true then 'S' Else 'N' end), '' ) as VarChar(1)) as ALIM_SUBSIDIADA, to_char(Coalesce((fun.id_funcionario+91000000) , '0' ), '00000000') as COD_FUNCIONARIO, Cast(Coalesce(fun.nome, '' ) as VarChar(40)) as FUNCIONARIO, Trim(Cast(to_char(Coalesce(func.id_funcao , '0' ), '00000000') as VarChar(9))) as COD_CARGO, Cast(Coalesce(func.descricao, '' ) as VarChar(150)) as DESC_CARGO, Cast(Coalesce(fun.cpf_cnpj, '' ) as VarChar(15)) as CPF, Cast(Coalesce(fun.rg_ie, '' ) as VarChar(16)) as RG, Cast(Coalesce(fun.uf_emissor_rg, '' ) as VarChar(2)) as UF_RG, Cast(Coalesce(fun.passaporte, '' ) as VarChar(15)) as PASSP_NR, (Case When fun.dt_validade_passaporte is not null Then Cast(to_char(fun.dt_validade_passaporte, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DAT_VAL_PASSAPORTE, Cast(Coalesce(fdt.numero_cnh, '' ) as VarChar(12)) as NUM_CNH, Cast(Coalesce(fdt.categoria_cnh, '' ) as VarChar(3)) as CATEGORIA_CNH, Cast(Coalesce(fdt.uf_emissor_cnh, '' ) as VarChar(2)) as UF_EMISS_CNH, (Case When fdt.validade_cnh is not null Then Cast(to_char(fdt.validade_cnh, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DATA_VAL_CNH, (Case When fdt.dt_nascimento is not null Then Cast(to_char(fdt.dt_nascimento, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DATA_NASCIMENTO, (Case When cl.dt_inicio is not null Then Cast(to_char(cl.dt_inicio, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DATA_INI_CHECK_LIST, (Case When cl.dt_termino is not null Then Cast(to_char(cl.dt_termino, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DATA_FIM_CHECK_LIST, (Case When cl.dt_inclusao is not null Then Cast(to_char(cl.dt_inclusao, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DATA_INCLUSAO, (Case When cl.hr_inclusao is not null Then Cast(cl.hr_inclusao as varchar(8)) Else Cast('' as varchar(8)) end) as HORA_INCLUSAO, (Case When cl.dt_alteracao is not null Then Cast(to_char(cl.dt_alteracao, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DATA_ALTERACAO, (Case When cl.hr_alteracao is not null Then Cast(cl.hr_alteracao as varchar(8)) Else Cast('' as varchar(8)) end) as HORA_ALTERACAO, (Case When det.dt_vencimento_aso is not null Then Cast(to_char(det.dt_vencimento_aso, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DAT_VAL_ASO, (Case When det.dt_vencimento_integracao_cl is not null Then Cast(to_char(det.dt_vencimento_integracao_cl, 'YYYYMMDD') as VarChar(8)) Else Cast('' as varchar(8)) end) as DAT_VAL_INTEGRACAO, Cast( Coalesce( (Case When contte.id_empresa_terceirizada is not null Then Trim(Cast(to_char(Coalesce(contte.id_empresa_terceirizada, '0' ), '00000000') as VarChar(9))) Else Trim(Cast(to_char(Coalesce(contda.id_empresa_terceirizada, '0' ), '00000000') as VarChar(9))) end), '' ) as VarChar(9)) as FORNECEDOR_TITULAR, Cast( Coalesce( (Case When contte.id_empresa_terceirizada is not null Then contte.nome Else contda.nome end), '' ) as VarChar(150)) as RAZAO_SOCIAL_TITULAR, Cast( Coalesce( (Case When contte.id_empresa_terceirizada is not null Then contte.cpf_cnpj Else contda.cpf_cnpj end) , '' ) as VarChar(15)) as CNPJ_TITULAR, Cast( Coalesce( (Case When contte.id_empresa_terceirizada is not null Then ge_contte.cnpj_base Else ge_contda.cnpj_base end), '' ) as VarChar(15)) as CNPJ_BASE_GRUPO_FORNECEDOR_TITULAR, Cast( Coalesce( (Case When contte.id_empresa_terceirizada is not null Then ge_contte.descricao Else ge_contda.descricao end), '' ) as VarChar(50)) as NOME_GRUPO_FORNECEDOR_TITULAR, Cast( Coalesce( (Case When cl.id_empresa_contratante is not null Then Trim(Cast(to_char(Coalesce(contda.id_empresa_terceirizada, '0' ), '00000000') as VarChar(9))) Else '' End), '' ) as VarChar(9)) as FORNECEDOR_SUB, Cast( Coalesce( (Case When cl.id_empresa_contratante is not null Then contda.nome Else '' End), '' ) as VarChar(150)) as RAZAO_SOCIAL_SUB, Cast( Coalesce( (Case When cl.id_empresa_contratante is not null Then contda.cpf_cnpj Else '' End), '' ) as VarChar(15)) as CNPJ_SUB, Cast( Coalesce( (Case When cl.id_empresa_contratante is not null Then ge_contda.cnpj_base Else '' End), '' ) as VarChar(8)) as CNPJ_BASE_GRUPO_FORNECEDOR_SUB, Cast( Coalesce( (Case When cl.id_empresa_contratante is not null Then ge_contda.descricao Else '' End), '' ) as VarChar(50)) as NOME_GRUPO_FORNECEDOR_SUB, Cast(Coalesce('A', '' ) as VarChar(1) ) as STATUS From check_list cl left join cliente_local_trabalho loc on (loc.id_cliente_local_trabalho = cl.id_cliente_local_trabalho) left join empresa_terceirizada contte on (contte.id_empresa_terceirizada = cl.id_empresa_contratante) left join grupo_empresa ge_contte on (ge_contte.id_grupo_empresa = contte.id_grupo_empresa), cliente cli, funcao func, funcionario fun, cliente_grupo gru, ligacao_func_emp lfe, check_list_detalhe det, funcionario_detalhe fdt, documento_contratacao doc left join diretoria dir on (dir.id_diretoria = doc.id_diretoria) left join area are on (are.id_area = doc.id_area) left join documento_contratacao_tipo_trabalho dctp on (dctp.id_documento_contratacao_tipo_trabalho = doc.id_documento_contratacao_tipo_trabalho) left join cliente_pessoa ges on (ges.id_cliente_pessoa = doc.id_gestor), empresa_terceirizada contda, grupo_empresa ge_contda, ligacao_func_emp_cliente lfec, documento_contratacao_texto tex Where (det.id_check_list = cl.id_check_list) and (lfec.id_ligacao_func_emp_cliente = cl.id_ligacao_func_emp_cliente) and (cli.id_cliente = lfec.id_cliente) and (gru.id_grupo_cliente = cli.id_grupo_cliente) and (lfe.id_ligacao_func_emp = lfec.id_ligacao_func_emp) and (contda.id_empresa_terceirizada = lfe.id_empresa_terceirizada) and (ge_contda.id_grupo_empresa = contda.id_grupo_empresa) and (fun.id_funcionario = lfe.id_funcionario) and (func.id_funcao = lfe.id_funcao) and (fdt.id_funcionario = fun.id_funcionario) and (doc.id_documento_contratacao = cl.id_documento_contratacao) and (tex.id_documento_contratacao = doc.id_documento_contratacao) and (cl.cancelado = false) and (gru.integracao_ws = true) and (cl.concluido = true) and (cli.id_cliente in (zÂ) ) and (det.enviou_ws = %s) and ( (cl.dt_inclusao between %s and %s) or (cl.dt_alteracao between %s and %s) ) a Update check_list_detalhe set enviou_ws = true, dt_check_ws = current_date, hr_check_ws = current_time Where id_check_list in (Select cl.id_check_list From check_list cl, ligacao_func_emp_cliente lfec Where (cl.id_check_list in %s) and (lfec.id_ligacao_func_emp_cliente = cl.id_ligacao_func_emp_cliente) and (lfec.id_cliente in (z) ) ) aŸ Update check_list_detalhe set enviou_ws = false, dt_reab_ws = current_date, hr_reab_ws = current_time, dt_check_ws = null, hr_check_ws = null, dt_send_ws = null, hr_send_ws = null Where id_check_list in (Select cl.id_check_list From check_list cl, ligacao_func_emp_cliente lfec Where (cl.id_check_list in %s) and (lfec.id_ligacao_func_emp_cliente = cl.id_ligacao_func_emp_cliente) and (lfec.id_cliente in (a– Update check_list_cancelado set enviou_ws = false, dt_reab_ws = current_date, hr_reab_ws = current_time, dt_check_ws = null, hr_check_ws = null, dt_send_ws = null, hr_send_ws = null Where id_check_list in (Select cl.id_check_list From check_list cl, ligacao_func_emp_cliente lfec Where (cl.id_check_list in %s) and (lfec.id_ligacao_func_emp_cliente = cl.id_ligacao_func_emp_cliente) and (lfec.id_cliente in (a˜ Update check_list_cancelado set enviou_ws = true, dt_check_ws = current_date, hr_check_ws = current_time Where id_check_list in (Select clc.id_check_list From check_list_cancelado clc, check_list cl, ligacao_func_emp_cliente lfec Where (clc.id_check_list in %s) and (cl.id_check_list = clc.id_check_list) and (lfec.id_ligacao_func_emp_cliente = cl.id_ligacao_func_emp_cliente) and (lfec.id_cliente in (z ) ) ) z– Update check_list_cancelado set dt_send_ws = current_date, hr_send_ws = current_time Where id_check_list in %s z² Update check_list_detalhe set dt_send_ws = current_date, hr_send_ws = current_time, ip_request = %s Where (id_check_list in %s) aPSelect Coalesce(wsc.id_cliente, '0') as ID_FILIAL, Cast(Coalesce(cli.codigo_sap, '' ) as VarChar(4) ) as COD_FILIAL, Cast(Coalesce(cli.fantasia , '' ) as VarChar(30)) as NOME_FILIAL From usuario_ws_cliente wsc, cliente cli, cliente_grupo gru Where (cli.id_cliente = wsc.id_cliente) and (gru.id_grupo_cliente = cli.id_grupo_cliente) and (gru.integracao_ws = true) and (wsc.login = %s) Group by wsc.id_cliente, cli.codigo_sap, cli.fantasia Order by wsc.id_cliente;zo Select senha, su From usuario_ws Where (login = %s) and (senha = %s) limit 1; aL Select det.id_check_list, cli.id_cliente as id_cliente_destra, Trim(Cast(to_char(Coalesce(cli.id_cliente, '0' ), '0000') as VarChar(5))) as COD_FILIAL, to_char(Coalesce((fun.id_funcionario+91000000) , '0' ), '00000000') as COD_FUNCIONARIO, Cast(Coalesce(fun.nome, '' ) as VarChar(40)) as NOME_FUNCIONARIO, Cast(Coalesce(fun.cpf_cnpj, '' ) as VarChar(15)) as CPF_FUNCIONARIO, Cast(to_char(cl.dt_inicio, 'YYYYMMDD') as VarChar(8)) as DATA_INI_CHECK_LIST, Cast(to_char(cl.dt_termino, 'YYYYMMDD') as VarChar(8)) as DATA_FIM_CHECK_LIST, Cast(to_char(det.dt_send_ws, 'YYYYMMDD') as VarChar(8)) as DATA_ENVIO, Cast(det.hr_send_ws as varchar(8)) as HORA_ENVIO, Cast(to_char(det.dt_check_ws, 'YYYYMMDD') as VarChar(8)) as DATA_MARCACAO, Cast(det.hr_check_ws as varchar(8)) as HORA_MARCACAO, Cast(to_char(det.dt_conclusao, 'YYYYMMDD') as VarChar(8)) as DATA_LIBERACAO, Cast(det.hr_conclusao as varchar(8)) as HORA_LIBERACAO, Cast(to_char( cl.dt_inclusao, 'YYYYMMDD') as VarChar(8)) as DATA_INCLUSAO, Cast(cl.hr_inclusao as varchar(8)) as HORA_INCLUSAO, Cast(to_char( cl.dt_alteracao, 'YYYYMMDD') as VarChar(8)) as DATA_ALTERACAO, Cast(cl.hr_alteracao as varchar(8)) as HORA_ALTERACAO From check_list_detalhe det, check_list cl, ligacao_func_emp_cliente lfec, ligacao_func_emp lfe, funcionario fun, cliente cli, cliente_grupo gru Where (cl.id_check_list = det.id_check_list) and (cl.cancelado = false) and (cl.concluido = true) and (lfec.id_ligacao_func_emp_cliente = cl.id_ligacao_func_emp_cliente) and (cli.id_cliente = lfec.id_cliente) and (gru.id_grupo_cliente = cli.id_grupo_cliente) and (gru.integracao_ws = true) and (lfe.id_ligacao_func_emp = lfec.id_ligacao_func_emp) and (fun.id_funcionario = lfe.id_funcionario) and (fun.id_ws in %s) )rrZ sql_statusÚsegundosrrrrZapplication_namer r rrrr Zsql_unidades_usuarioZsql_cancelamentosZsql_requisitosZsql_liberacoes_enviadasZsql_liberacoesZsql_check_liberacoesZsql_reabilitar_liberacoesZsql_reabilitar_cancelamentosZsql_check_cancelamentosZsql_sent_cancelamentosZsql_sent_liberacoesÚ sql_unidadesZ sql_loginZsql_liberacoes_matriculasr r r r ÚsH  E-,^c   1