def getDataASENL( sql, ente, permission ): try: rows = exec_steady(sql) if permission else [] except EmptySetError: rows = [] l = [] for row in rows: r = dict(row) sql = ''' select title from clasifs_internas_cytg as clas where {} = clas.sorting_val and {} = clas.direccion_id and {} = clas.org_fiscal_id limit 1; '''.format( r['clasif_final_cytg'], r['direccion_id'], ente, r['ires_id']) try: seg = exec_steady(sql) except EmptySetError: seg = [] if seg: segd = dict(seg[0]) r['clasif_id'] = r['clasif_final_cytg'] r['monto'] = r['monto_pendiente_solventar'] r['clasif_name'] = segd['title'] l.append(r) return l
def add_audit_data(ent): attributes = set([ 'id', 'title', 'org_fiscal_id', 'direccion_id', ]) mod_ent = {attr: ent[attr] for attr in attributes} mod_ent['dependency_ids'] = [] sql = ''' SELECT dependencia_id FROM auditoria_dependencias WHERE auditoria_id = {}; '''.format(mod_ent['id']) rows = exec_steady(sql) for row in rows: mod_ent['dependency_ids'].append(row[0]) mod_ent['years'] = [] sql = ''' SELECT anio_cuenta_pub FROM auditoria_anios_cuenta_pub WHERE auditoria_id = {}; '''.format(mod_ent['id']) rows = exec_steady(sql) for row in rows: mod_ent['years'].append(row[0]) return mod_ent
def getDataCYTG( sql, ente, permission ): try: rows = exec_steady(sql) if permission else [] except EmptySetError: rows = [] l = [] for row in rows: r = dict(row) sql = ''' select seg.monto_pendiente_solventar, clas.title from seguimientos_obs_cytg as seg join clasifs_internas_cytg as clas on {} = clas.sorting_val and {} = clas.direccion_id and {} = clas.org_fiscal_id where observacion_id = {} order by seguimiento_id desc limit 1; '''.format( r['clasif_final_cytg'], r['direccion_id'], ente, r['ires_id']) try: seg = exec_steady(sql) except EmptySetError: seg = [] if seg: segd = dict(seg[0]) r['clasif_id'] = r['clasif_final_cytg'] r['monto'] = segd['monto_pendiente_solventar'] r['clasif_name'] = segd['title'] r['monto_observado'] = 0 l.append(r) return l
def _count_entities(search_params, not_blocked_clause, count_by_field='id'): ''' Counts non-blocked entities ''' clause = ' AND NOT blocked' if not_blocked_clause else '' sql = ''' SELECT count({})::int as total FROM {} WHERE TRUE {} ''' count = 0 _search_params = search_params.copy() if search_params is not None else {} concerned_fiscals = get_org_fiscals(_search_params) try: del _search_params['org_fiscal_id'] except: pass if 'ASENL' in concerned_fiscals: table = 'acciones_asenl' query = sql.format(count_by_field, table, clause) if _search_params: query += ' AND ' + _setup_search_criteria(table, _search_params) rows = exec_steady(query) if len(rows) == 0: raise NoResultFound('Just expecting one total as a result') elif len(rows) > 1: raise MultipleResultsFound( 'Multiple results found, but only one expected') count += rows.pop()['total'] if 'ASF' in concerned_fiscals: table = 'acciones_asf' try: del _search_params['description'] except: pass query = sql.format(count_by_field, table, clause) if _search_params: query += ' AND ' + _setup_search_criteria(table, _search_params) rows = exec_steady(query) if len(rows) == 0: raise NoResultFound('Just expecting one total as a result') elif len(rows) > 1: raise MultipleResultsFound( 'Multiple results found, but only one expected') count += rows.pop()['total'] return count
def iresASF(ignored_audit_str, ej_ini, ej_fin, ente, str_filtro_direccion, permission): sql = ''' select ires.id as ires_id, dep_cat.title as dependencia, anio.anio_cuenta_pub as ejercicio, tipos.title as tipo_observacion, pre.direccion_id as direccion_id, pre.num_observacion as num_observacion, ires.observacion_ir as observacion, ires.monto_observado as monto from observaciones_ires_asf as ires join observaciones_pre_asf as pre on ires.id = pre.observacion_ires_id join auditoria_dependencias as dep on pre.auditoria_id = dep.auditoria_id join dependencies as dep_cat on dep.dependencia_id = dep_cat.id join auditoria_anios_cuenta_pub as anio on pre.auditoria_id = anio.auditoria_id join observation_types as tipos on ires.tipo_observacion_id = tipos.id where not pre.blocked and not ires.blocked {} and anio.anio_cuenta_pub >= {} and anio.anio_cuenta_pub <= {} {} order by dependencia, num_observacion, tipo_observacion, ires_id; '''.format(ignored_audit_str, ej_ini, ej_fin, str_filtro_direccion) try: rows = exec_steady(sql) if permission else [] except EmptySetError: rows = [] l = [] for row in rows: r = dict(row) sql = ''' select seg.estatus_id estatus_id, estatus_ires.title as estatus, seg.monto_solventado from seguimientos_obs_asf as seg join estatus_ires_asf as estatus_ires on seg.estatus_id = estatus_ires.id where observacion_id = {} order by seguimiento_id desc limit 1; '''.format(r['ires_id']) try: seg = exec_steady(sql) except EmptySetError: seg = [] r['estatus'] = '' r['num_observacion'] = '' #No lo tiene como campo r['cant_obs'] = 1 #No se agrupa -> 1 r['monto_solventado'] = 0 if seg: segd = dict(seg[0]) r['estatus'] = segd['estatus'] r['monto_solventado'] = segd['monto_solventado'] l.append(r) return l
def add_observacion_data(ent): attributes = set([ 'id', 'observacion_pre_id', 'num_oficio_of', 'fecha_recibido', 'fecha_vencimiento', 'observacion_ir', 'tipo_observacion_id', 'accion', 'clave_accion', 'monto_observado', 'monto_a_reintegrar', 'monto_reintegrado', 'fecha_reintegro', 'monto_por_reintegrar', 'tiene_pras' ]) mod_ent = {attr: ent[attr] for attr in attributes} sql = ''' SELECT * FROM seguimientos_obs_asf WHERE observacion_id = {} ORDER BY seguimiento_id ASC; '''.format(mod_ent['id']) rows = exec_steady(sql) mod_ent['seguimientos'] = [] for row in rows: mod_ent['seguimientos'].append(dict(row)) if mod_ent['tiene_pras']: sql = ''' SELECT * FROM pras_ires_asf WHERE pras_observacion_id = {}; '''.format(mod_ent['id']) rows = exec_steady(sql) mod_ent['pras'] = dict(rows[0]) else: mod_ent['pras'] = None # Add obs preliminar data add_preliminar_data(mod_ent) return mod_ent
def add_user_permissions(ent): attributes = set([ 'id', 'username', 'orgchart_role_id', 'division_id', 'disabled', 'first_name', 'last_name', ]) mod_ent = {attr: ent[attr] for attr in attributes} mod_ent['access_vector'] = [] sql = ''' SELECT authority_id FROM user_authority WHERE user_id = {} ORDER BY authority_id; '''.format(mod_ent['id']) try: rows = exec_steady(sql) except Exception as err: return mod_ent for row in rows: mod_ent['access_vector'].append(row[0]) return mod_ent
def create(**kwargs): ''' Crear una entidad Acción ''' org_fiscal = get_org_fiscal_name(kwargs['org_fiscal_id']) table = 'acciones_' + org_fiscal.lower() seq_name = table + '_seq' title = kwargs['title'].replace("'", "''") description = ", '{}'".format(kwargs['description'].replace( "'", "''")) if org_fiscal == "ASENL" else "" sql = """ INSERT INTO {} VALUES (nextval('{}'::regclass), '{}'{}) RETURNING * """.format(table, seq_name, title, description) try: rows = exec_steady(sql) except: raise Exception( 'No fue posible insertar la entrada. Verifique que el título no esté duplicado.' ) return dict(rows[0])
def getRowsASENL(ignored_audit_str, ej_ini, ej_fin, str_filtro_direccion, fiscal, aux_dict, permission): estatuses = getArrayEstatusASENL('estatus_pre_asenl') sql = ''' select dep_cat.title as dependencia, tipos.title as tipo_obs, pre.estatus_proceso_id as estatus, count(pre.id) as cant_obs, sum(pre.monto_observado) as monto_observado_pre from observaciones_pre_asenl as pre join auditoria_dependencias as dep on pre.auditoria_id = dep.auditoria_id join dependencies as dep_cat on dep.dependencia_id = dep_cat.id join auditoria_anios_cuenta_pub as anio on pre.auditoria_id = anio.auditoria_id join observation_types as tipos on pre.tipo_observacion_id = tipos.id where not pre.blocked {} and anio.anio_cuenta_pub >= {} and anio.anio_cuenta_pub <= {} {} group by dep_cat.title, tipos.title, pre.estatus_proceso_id order by dep_cat.title, tipos.title, pre.estatus_proceso_id; '''.format(ignored_audit_str, ej_ini, ej_fin, str_filtro_direccion) try: rows = exec_steady(sql) if ((fiscal == '' or fiscal == 'asenl') and permission) else [] except EmptySetError: rows = [] for row in rows: estatusStr = estatuses[row[2]] if isinstance(row[2], int) else '_none' if (row[0], row[1]) in aux_dict: aux_dict[(row[0], row[1])]['to' + estatusStr] = ('asenl', row[3], row[4]) else: aux_dict[(row[0], row[1])] = { 'to' + estatusStr: ('asenl', row[3], row[4]) } return aux_dict
def get_catalogs(table_name_list): ''' Fetches values and captions from a list of tables. These pairs can be used on input screens ''' fields_d = {} for table in table_name_list: values_l = [] if table == 'fiscals': sql = ''' SELECT * FROM {} WHERE title = 'ASF' OR title = 'ASENL' ORDER BY id; '''.format(table) else: sql = ''' SELECT * FROM {} ORDER BY id; '''.format(table) rows = exec_steady(sql) for row in rows: values_l.append(dict(row)) fields_d[table] = values_l return fields_d
def iresASENL(ignored_audit_str, ej_ini, ej_fin, ente, str_filtro_direccion, permission): sql = ''' select ires.id as ires_id, dep_cat.title as dependencia, anio.anio_cuenta_pub as ejercicio, tipos.title as tipo_observacion, ires.num_observacion as num_observacion, ires.observacion_final as observacion, count(pre.id) as cant_obs, sum(ires.monto_observado) as monto, ires.monto_solventado as monto_solventado from observaciones_ires_asenl as ires join observaciones_pre_asenl as pre on ires.observacion_pre_id = pre.id join auditoria_dependencias as dep on pre.auditoria_id = dep.auditoria_id join dependencies as dep_cat on dep.dependencia_id = dep_cat.id join auditoria_anios_cuenta_pub as anio on pre.auditoria_id = anio.auditoria_id join observation_types as tipos on ires.tipo_observacion_id = tipos.id where not pre.blocked and not ires.blocked {} and anio.anio_cuenta_pub >= {} and anio.anio_cuenta_pub <= {} {} group by dependencia, pre.num_observacion, observacion, tipo_observacion, ires_id, ejercicio, direccion_id order by dependencia, num_observacion, tipo_observacion; '''.format(ignored_audit_str, ej_ini, ej_fin, str_filtro_direccion) try: rows = exec_steady(sql) if permission else [] except EmptySetError: rows = [] l = [] for row in rows: r = dict(row) r['estatus'] = '' l.append(r) return l
def add_preliminar_data(ent): if ent['observacion_pre_id'] <= 0: ent['direccion_id'] = None ent['programa_social_id'] = None ent['auditoria_id'] = None ent['num_observacion'] = None else: sql = ''' SELECT direccion_id, programa_social_id, auditoria_id, num_observacion FROM observaciones_pre_asf WHERE id = {} AND NOT blocked; '''.format(ent['observacion_pre_id']) try: rows = exec_steady(sql) row = dict(rows[0]) ent['direccion_id'] = row['direccion_id'] ent['programa_social_id'] = row['programa_social_id'] ent['auditoria_id'] = row['auditoria_id'] ent['num_observacion'] = row['num_observacion'] except Exception as err: ent['direccion_id'] = None ent['programa_social_id'] = None ent['auditoria_id'] = None ent['num_observacion'] = None
def getRowsASENL(ignored_audit_str, ej_ini, ej_fin, str_filtro_direccion, aux_dict, permission): ignored_audit_str = ignored_audit_str.replace('ires.', 'pre.') sql = ''' select dep_cat.title, anio.anio_cuenta_pub, count(ires.id) as cant_obs, sum(ires.monto_observado) as monto_observado_ires from observaciones_ires_asenl as ires join observaciones_pre_asenl as pre on ires.id = pre.observacion_ires_id join auditoria_dependencias as dep on pre.auditoria_id = dep.auditoria_id join dependencies as dep_cat on dep.dependencia_id = dep_cat.id join auditoria_anios_cuenta_pub as anio on pre.auditoria_id = anio.auditoria_id where not pre.blocked {} and pre.observacion_ires_id > 0 and anio.anio_cuenta_pub >= {} and anio.anio_cuenta_pub <= {} {} group by dep_cat.title, anio.anio_cuenta_pub order by dep_cat.title, anio.anio_cuenta_pub; '''.format(ignored_audit_str, ej_ini, ej_fin, str_filtro_direccion) try: rows = exec_steady(sql) if permission else [] except EmptySetError: rows = [] for row in rows: if (row[0], row[1]) in aux_dict: aux_dict[(row[0], row[1])]['asenl'] = (row[2], row[3]) else: aux_dict[(row[0], row[1])] = {'asenl': (row[2], row[3])} return aux_dict
def delete(org_fiscal_id, pre_ires, id): ''' Eliminar una entidad Estatus ''' org_fiscal = get_org_fiscal_name(org_fiscal_id) pre_ires = pre_ires.lower() validate(org_fiscal, pre_ires) pre_ires = pre_ires + '_' if org_fiscal != 'SFP' else '' table = 'estatus_' + pre_ires + org_fiscal.lower() sql = """ DELETE FROM {} WHERE id = {} RETURNING * """.format(table, id) try: rows = exec_steady(sql) except EmptySetError: raise except: raise Exception('No fue posible eliminar el estatus (id = {}).'.format(id)) return dict(rows[0])
def create(**kwargs): ''' Crear una entidad Estatus ''' org_fiscal = get_org_fiscal_name(kwargs['org_fiscal_id']) if 'pre_ires' not in kwargs: raise Exception('Indique si se trata de pre o ires (preliminar o informe de resultados)') pre_ires = kwargs['pre_ires'].lower() validate(org_fiscal, pre_ires) pre_ires = pre_ires + '_' if org_fiscal != 'SFP' else '' title = kwargs['title'].replace("'", "''") table = 'estatus_' + pre_ires + org_fiscal.lower() sql = """ INSERT INTO {} VALUES (DEFAULT, '{}') RETURNING * """.format(table, title) try: rows = exec_steady(sql) except: raise Exception('No fue posible insertar la entrada. Verifique que el título no esté duplicado.') return dict(rows[0])
def preASF(ignored_audit_str, ej_ini, ej_fin, ente, str_filtro_direccion, permission): sql = ''' select dep_cat.title as dependencia, anio.anio_cuenta_pub as ejercicio, pre.direccion_id as direccion_id, pre.num_observacion as num_observacion, pre.observacion as observacion, estatus_pre_asf.title as estatus, count(pre.id) as cant_obs, sum(pre.monto_observado) as monto from observaciones_pre_asf as pre join auditoria_dependencias as dep on pre.auditoria_id = dep.auditoria_id join dependencies as dep_cat on dep.dependencia_id = dep_cat.id join auditoria_anios_cuenta_pub as anio on pre.auditoria_id = anio.auditoria_id join estatus_pre_asf as estatus_pre_asf on pre.estatus_criterio_int_id = estatus_pre_asf.id where not pre.blocked {} and anio.anio_cuenta_pub >= {} and anio.anio_cuenta_pub <= {} {} group by dependencia, num_observacion, observacion, estatus, ejercicio, direccion_id order by dependencia, ejercicio; '''.format(ignored_audit_str, ej_ini, ej_fin, str_filtro_direccion) try: rows = exec_steady(sql) if permission else [] except EmptySetError: rows = [] l = [] for row in rows: r = dict(row) r['tipo_observacion'] = '' #Pre no tiene este campo r['monto_solventado'] = 0 #Pre no tiene este campo l.append(r) return l
def getObraPublicaId(): sql = "select id from divisions where title = 'OBRAS';" try: rows = exec_steady(sql) except EmptySetError: rows = [] return rows[0][0]
def get_org_fiscal_name(org_fiscal_id): sql = """ SELECT title FROM fiscals WHERE id = {} """.format(org_fiscal_id) rows = exec_steady(sql) return rows.pop()[0]
def get_ids(org_fisc, pre_ires): sql = ''' SELECT id FROM fiscals WHERE title = '{}'; '''.format(org_fisc) of_id = exec_steady(sql) sql = ''' SELECT id FROM observation_stages WHERE title = '{}'; '''.format(pre_ires) pi_id = exec_steady(sql) return of_id[0][0], pi_id[0][0]
def read(id): ''' Recuperar una entidad Dependencia ''' sql = """ SELECT * FROM dependencies WHERE id = {} """.format(id) rows = exec_steady(sql) return dict(rows[0])
def read(id): ''' Fetches a social program entity ''' sql = """ SELECT * FROM social_programs WHERE id = {} """.format(id) rows = exec_steady(sql) return dict(rows[0])
def add_observation_amounts(ent): attributes = set([ 'id', 'observation_type_id', 'social_program_id', 'audit_id', 'title', 'fiscal_id', 'amount_observed', 'observation_code_id', 'observation_bis_code_id', 'reception_date', 'expiration_date', 'doc_a_date', 'doc_b_date', 'doc_c_date', 'doc_a', 'doc_b', 'doc_c', 'dep_response', 'dep_resp_comments', 'division_id', 'hdr_doc', 'hdr_reception_date', 'hdr_expiration1_date', 'hdr_expiration2_date', 'observation_stage_id', ]) mod_ent = {attr: ent[attr] for attr in attributes} mod_ent['reception_date'] = mod_ent['reception_date'].__str__() mod_ent['expiration_date'] = mod_ent['expiration_date'].__str__() mod_ent['doc_a_date'] = mod_ent['doc_a_date'].__str__() mod_ent['doc_b_date'] = mod_ent['doc_b_date'].__str__() mod_ent['doc_c_date'] = mod_ent['doc_c_date'].__str__() mod_ent['hdr_reception_date'] = mod_ent['hdr_reception_date'].__str__() mod_ent['hdr_expiration1_date'] = mod_ent['hdr_expiration1_date'].__str__() mod_ent['hdr_expiration2_date'] = mod_ent['hdr_expiration2_date'].__str__() sql = ''' SELECT * FROM amounts WHERE observation_id = {} ORDER BY id DESC; '''.format(mod_ent['id']) rows = exec_steady(sql) mod_ent['amounts'] = [] for row in rows: row_dict = dict(row) row_dict['inception_time'] = row_dict['inception_time'].__str__() mod_ent['amounts'].append(row_dict) return mod_ent
def create(**kwargs): ''' Crear una entidad Dependencia ''' sql = """ INSERT INTO dependencies VALUES (DEFAULT, '{}', '{}', {}) RETURNING * """.format(kwargs['title'].replace("'", "''"), kwargs['description'].replace("'", "''"), kwargs['clasif_id']) rows = exec_steady(sql) return dict(rows[0])
def setEntesIds(): entesSql = "select * from fiscals;" try: entesRows = exec_steady(entesSql) except EmptySetError: rows = [] entes = {} for e in entesRows: entes[e[1]] = e[0] return entes
def getDataASENL(ignored_audit_str, ej_ini, ej_fin, ente, only_obras, str_filtro_direccion, permission): data_rows = [] sql = ''' select ires.id as ires_id, dep_cat.title as dependencia, anio.anio_cuenta_pub as ejercicio, tipos.title as tipo_observacion, pre.direccion_id as direccion_id, ires.clasif_final_cytg as clasif_final_cytg, ires.monto_pendiente_solventar as monto_pendiente_solventar, ires.monto_observado as monto_observado from observaciones_ires_asenl as ires join observaciones_pre_asenl as pre on ires.observacion_pre_id = pre.id join auditoria_dependencias as dep on pre.auditoria_id = dep.auditoria_id join dependencies as dep_cat on dep.dependencia_id = dep_cat.id join auditoria_anios_cuenta_pub as anio on pre.auditoria_id = anio.auditoria_id join observation_types as tipos on ires.tipo_observacion_id = tipos.id where not pre.blocked {} and anio.anio_cuenta_pub >= {} and anio.anio_cuenta_pub <= {} {} order by dependencia, ejercicio, tipo_observacion, ires_id; '''.format(ignored_audit_str, ej_ini, ej_fin, str_filtro_direccion) try: rows = exec_steady(sql) if permission else [] except EmptySetError: rows = [] l = [] for row in rows: r = dict(row) r['clasif_id'] = r['clasif_final_cytg'] r['monto'] = r['monto_observado'] l.append(r) data_rowsl = {} for i in l: key = (i['dependencia'], i['tipo_observacion']) if key in data_rowsl: data_rowsl[key]['cant_obs'] += 1 data_rowsl[key]['monto'] += i['monto'] else: data_rowsl[key] = {'cant_obs': 1, 'monto': i['monto']} for item in data_rowsl: value = data_rowsl[item] o = {} o['dep'] = item[0] o['tipo'] = item[1] o['c_obs'] = value['cant_obs'] o['monto'] = value['monto'] data_rows.append(o) return data_rows
def update(id, **kwargs): ''' Actualizar una entidad Dependencia ''' sql = """ UPDATE dependencies SET title = '{}', description = '{}', clasif_id = {} WHERE id = {} RETURNING * """.format(kwargs['title'].replace("'", "''"), kwargs['description'].replace("'", "''"), kwargs['clasif_id'], id) rows = exec_steady(sql) return dict(rows[0])
def execute_count(sql, count_by_field, table, clause, _search_params): query = sql.format(count_by_field, table, clause) if _search_params: query += ' AND ' + _setup_search_criteria(table, _search_params) rows = exec_steady(query) if len(rows) == 0: raise NoResultFound('Just expecting one total as a result') elif len(rows) > 1: raise MultipleResultsFound('Multiple results found, but only one expected') return rows.pop()['total']
def get(self, id): sql = """ SELECT id, observation_type_id FROM observations WHERE id = {} """.format(id) try: rows = exec_steady(sql) except EmptySetError: ns.abort(404) return dict(rows[0])
def read(org_fiscal_id, direccion_id, id): ''' Recuperar una entidad Clasificación interna de CyTG ''' sql = """ SELECT * FROM clasifs_internas_cytg WHERE NOT blocked AND org_fiscal_id = {} AND direccion_id = {} AND sorting_val = {} """.format(org_fiscal_id, direccion_id, id) rows = exec_steady(sql) return dict(rows[0])
def add_observacion_data(ent): attributes = set([ 'id', 'direccion_id', 'fecha_captura', 'programa_social_id', 'auditoria_id', 'acta_cierre', 'fecha_firma_acta_cierre', 'fecha_compromiso', 'clave_observacion', 'observacion', 'acciones_correctivas', 'acciones_preventivas', 'tipo_observacion_id', 'monto_observado', 'monto_a_reintegrar', 'monto_reintegrado', 'fecha_reintegro', 'monto_por_reintegrar', 'num_oficio_of_vista_cytg', 'fecha_oficio_of_vista_cytg', 'num_oficio_cytg_aut_invest', 'fecha_oficio_cytg_aut_invest', 'num_carpeta_investigacion', 'num_oficio_vai_municipio', 'fecha_oficio_vai_municipio', 'autoridad_invest_id', 'num_oficio_pras_of', 'fecha_oficio_pras_of', 'num_oficio_pras_cytg_dependencia', 'num_oficio_resp_dependencia', 'fecha_oficio_resp_dependencia', ]) mod_ent = {attr: ent[attr] for attr in attributes} sql = ''' SELECT * FROM seguimientos_obs_sfp WHERE observacion_id = {} ORDER BY seguimiento_id ASC; '''.format(mod_ent['id']) rows = exec_steady(sql) mod_ent['seguimientos'] = [] for row in rows: mod_ent['seguimientos'].append(dict(row)) return mod_ent