import re, json, string, random, time from time import sleep from generic.gn_mysql import gn_mysql from generic.gn_request import Request from generic.JsonByPath import JsonByPath from datetime import datetime from pathlib import Path import traceback class gn_api (object) : def __init__(self) : self.createLog("000","inicio da exportacao") """Construtor Arguments: api {str} -- api que esta usando essa clase generica, esse nome serve para encontrar os arquivos, mapa e classe """ self.root_dir = str(Path(__file__).parent.parent)#'/var/www/html/webservice/gn_integracao'#Path(__file__).parent.parent; self.jsonWs = {} self.config = {} self.sql = "" self.db = self.db or False self.tableNames = {} # prepara as variaveis de classe que vem da classe filho self.prepare_params() # Faz o processamento self.bootstrap() def prepare_params(self): """ Parametros """ # tempo de espera entre chamadas # Para envar item a item if "sendOneByOne" not in dir(self) : self.sendOneByOne = False # base da url, concatenado com as posicoes do mapa if "request_urlBase" not in dir(self) : self.request_urlBase = False # headers de chamada if "request_headers" not in dir(self) : self.request_headers = False self.application_id = self.apiName + "_id" if '#' in self.apiName: dados = self.apiName.split('#') self.application_id = dados[0] + "_id" if dados[0] + "_request" in dir(self) : self.api_request = getattr(self,dados[0]+"_request") return else : print("Nao definido a variavel de request") exit() if self.apiName + "_request" in dir(self) : self.api_request = getattr(self,self.apiName+"_request") else : print("Nao definido a variavel de request") exit() def rote_handler(self): query_str_to_json = lambda query, sep="&": {} if not query else { self.get_index(x.split('='),0):str(self.get_index(x.split('='),1)) for x in query.split(sep) } # Tratamento de multiplos dados vindo no argumento de rota rote_args = self.rote.split('?') self.rote_data = None rote_data_str = self.get_index(rote_args, 1) if(rote_data_str): self.rote = self.get_index(rote_args, 0) rote_data_list = rote_data_str.split(';') self.rote_data = {} for rote_data in rote_data_list: if '->' in rote_data: rote_map = rote_data.split('->') self.rote_data[rote_map[0]] = query_str_to_json(rote_map[1]) else : self.rote_data['query'] = query_str_to_json(rote_data) def setup(self, argv): self.apiName = argv[1] # Nome da api self.method = argv[2] # metodo chamado (GET POST PUT DELETE) self.rote = argv[3] # Rota chamada, se ALL, fazer todos self.nmbanco = argv[4] # Banco de Dados self.nmlkgrupo = argv[5] # posicionamento de grupo self.cdempresa = argv[6] # posicionamento de empresa self.cdfilial = argv[7] # posicionamento de filial self.saida = argv[8] # Arquivo de saida api self.saida_cobol = argv[9] # Arquivo de saida cobol self.debug = True if argv[-1] == 'debug' else False suffix = '_app_pedidos3' if not self.hasAttr('suffix', self) else self.suffix self.database_db = f"{self.nmbanco.lower()}{suffix}"#.format(self.nmbanco.lower(), suffix) #print(sql) setup = self.select( f"""SELECT * FROM TBEMPRESA WHERE 1=1 AND NMLKGRUPO = "{self.nmlkgrupo }" AND CDEMPRESA = "{self.cdempresa }" AND CDFILIAL = "{self.cdfilial }"; """ ) """ .format( NMLKGRUPO = self.nmlkgrupo , CDEMPRESA = self.cdempresa , CDFILIAL = self.cdfilial ) """ if not setup : self.createLog("404","Base de dados sem dados, checar empresa e filial") exit() self.get_environment_api() self.get_tokens_api() return setup def get_environment_api(self): try: environments = self.select( f""" SELECT * FROM TBTOKEN WHERE lkgrupo = "{self.nmlkgrupo.upper()}" AND empresa = "{self.cdempresa}" AND filial = "{self.cdfilial}" AND nome_api = "{self.apiName}" AND tipo_token = "A"; """ ) if environments and len(environments) > 0: for token in environments: if token.get('nome_token') == 'ambiente_api': self.ambiente = token.get('valor_token', None) #, "P") elif token.get('nome_token') == 'servidor_api': self.softdib_ip = token.get('valor_token', None) #, "localhost") elif token.get('nome_token') == 'porta_api': self.softdib_port = token.get('valor_token', None)#, "3000") # if not hasattr("ambiente", self) and not hasattr("softdib_ip", self) and not hasattr("softdib_port", self): # self.createLog(671, "Não foi encontrado dados de registro de ambiente") # exit() else: self.createLog(671, "Nao existem tokens cadastrados") exit() except: self.createLog(671, "Nao foi possivel carregar os dados de registro de ambiente") exit() def get_tokens_api(self): try: tokens = self.select( f""" SELECT * FROM TBTOKEN WHERE 1 = 1 AND lkgrupo = "{self.nmlkgrupo.upper()}" AND empresa = "{self.cdempresa}" AND filial = "{self.cdfilial}" AND nome_api = "{self.apiName}" AND tipo_token = "{self.ambiente}"; """ ) self.environment_config = { 'url' : tokens[0].get('url_token'), 'ambiente': tokens[0].get('tipo_token'), 'tokens' : { token.get('nome_token'):token.get('valor_token') for token in tokens} } except: self.createLog(671, "Nao foram encontrados dados de registro de tokens") exit() # raise Exception('Nao foi encontrado dados de registro de tokens') pass #self.environment_config = {} def get_token(self, key)-> dict: value = self.environment_config.get('tokens',{}).get(key) if value: return { key:value } return {} def get_token_data(self, key)-> dict: value = self.environment_config.get('tokens',{}).get(key) if value: return value return False """ ..######..########....###....########..######## .##....##....##......##.##...##.....##....##... .##..........##.....##...##..##.....##....##... ..######.....##....##.....##.########.....##... .......##....##....#########.##...##......##... .##....##....##....##.....##.##....##.....##... ..######.....##....##.....##.##.....##....##... """ def bootstrap(self): """ Gatilho de entrada """ self.load_api_map() self.handle_api_map() def load_api_map(self): """Busca e abre o arquivo de definicoes da api""" api_path = f"{self.root_dir}/{self.apiName}" emp_fil_path = f"{self.root_dir}/{self.apiName}/companies/{self.nmlkgrupo.lower()}" if '#' in self.apiName: dados = self.apiName.split('#') api_path = f"{self.root_dir}/{dados[0]}" emp_fil_path = f"{self.root_dir}/{dados[0]}/companies/{self.nmlkgrupo.lower()}" api_map_path = f"{api_path}/map.json" emp_fil_map_path = f"{emp_fil_path}/map.json" default_map = self.read_file(path = emp_fil_map_path) api_map = self.read_file(path = api_map_path) base = json.loads(api_map or '{}') head = json.loads(default_map or '{}') if base and head: from jsonmerge import Merger default_schema = { "properties": { "rotes": { "type": "array", "mergeStrategy": "arrayMergeById", "items": { "properties": { "fields":{ "mergeStrategy": "arrayMergeById", "mergeOptions": {"idRef": "para"}, "items": { "type": "array", 'mergeStrategy': 'overwrite' } }, "pedido_fields":{ "mergeStrategy": "arrayMergeById", "mergeOptions": {"idRef": "para"}, "items": { "type": "array", 'mergeStrategy': 'overwrite' } }, "itenscarrinho_fields":{ "mergeStrategy": "arrayMergeById", "mergeOptions": {"idRef": "para"}, "items": { "type": "array", 'mergeStrategy': 'overwrite' } } } } } } } if self.hasAttr("merger_schema", self): default_schema = self.merger_schema merger = Merger(default_schema) merged = merger.merge(base,head) rotes = merged.get('rotes',{}) merged['rotes'] = sorted(rotes, key=lambda rote: rote.get('id',len(rotes))) self.jsonWs = merged else: self.jsonWs = base #self.jsonWs = json.loads( default_map or api_map ) self.config = self.jsonWs.get('config') def handle_api_map(self) : #""" # Manipula os dados de acordo com as configuracoes do map.json #""" #Carrega dos dodos do webservice atual rotes = self.jsonWs['rotes'] # O metodo delete sempre deve executar de traz para frente. if self.method.upper() == "DELETE" : rotes = list(reversed(rotes)) #Percorre todos os dados das rotas for dados in rotes : self.atualWs = dados """ Verifica se essa rota pertence a um lkgrupo exclusivo e se existe restricao por grupo """ available_for = self.atualWs.get('available_for') if available_for and self.nmlkgrupo.lower() not in available_for: continue # Controle para enviar rota especifica passada por paramentros if not self.rote == 'ALL' and \ self.rote == self.atualWs['rote'] or \ re.match(r'&?[^=&]*=[^=&]*', self.rote) or \ self.rote == 'ALL' or \ self.isFile(self.rote) : pass else: continue if self.rote == 'ALL' and ('active' in self.atualWs and not self.atualWs['active']) : continue #if self.rote == 'ALL' : # if ('active' in self.atualWs and not self.atualWs['active']) : # continue #else: # if (self.rote != self.atualWs['rote']) : # continue if self.method.upper() == "SOAP" : self.rote = self.atualWs['rote'] isValid = self.load_input() if not isValid: continue self.soap_handler() continue # Metodo que controla os dados que recuperamos da api if 'GET' in self.atualWs['methods'] and \ self.method.upper() == "GET" : #sql = getattr(self, dados["custom_get"])() self.get_handler() continue if 'FILE' in self.atualWs['methods'] and \ self.method.upper() == "FILE" : self.fileHandler() # Por enquanto cada api tem a sua - depois separar por metodo parametro ou via put if 'DELETE' in self.atualWs['methods'] and \ self.method.upper() == "DELETE" : self.delete_handler() continue if 'POST' in self.atualWs['methods'] and self.method.upper() == "POST": route = self.fixed_rote() self.call(f"post_route_handler_{route}", is_required=True) continue if 'PUT' in self.atualWs['methods'] and self.method.upper() == "PUT": route = self.fixed_rote() self.call(f"put_route_handler_{route}", is_required=True) continue if 'PATCH' in self.atualWs['methods'] and self.method.upper() == "PATCH": route = self.fixed_rote() self.call(f"patch_route_handler_{route}", is_required=True) continue #if self.method.upper() in ['SEND','POST','PUT','PATCH'] and \ if self.method.upper() in ['SEND'] and \ ('POST' in dados['methods'] or 'PUT' in dados['methods']): self.post_put_handler() def fixed_rote(self) : return self.atualWs['rote'] \ .replace("/","_") \ .replace(".","_") \ .replace("-","_") \ .replace("{","") \ .replace("}","") def getTemplate(self) : rote = self.fixed_rote() #rote = rote #if self.debug is True: # path_root = "." #else : # path_root = self.root_dir #return self.read_file(f"{self.root_dir}/{self.apiName}/templates/{rote}_template.txt") #.format(path_root = path_root,rote=rote,api_name=self.apiName)) api_path_template = f"{self.root_dir}/{self.apiName}/templates" emp_fil_path_template = f"{self.root_dir}/{self.apiName}/companies/{self.nmlkgrupo.lower()}/templates" if '#' in self.apiName: dados = self.apiName.split('#') api_path_template = f"{self.root_dir}/{dados[0]}/templates" emp_fil_path_template = f"{self.root_dir}/{dados[0]}/companies/{self.nmlkgrupo.lower()}/templates" api_map_path_template = f"{api_path_template}/{rote}_template.txt" emp_fil_map_path_template = f"{emp_fil_path_template}/{rote}_template.txt" default_map_template = self.read_file(path = emp_fil_map_path_template) api_map_template = self.read_file(path = api_map_path_template) #head_template = json.loads(default_map_template or '{}') if default_map_template : return default_map_template else : return api_map_template """ .########..########.##.......########.########.######## .##.....##.##.......##.......##..........##....##...... .##.....##.##.......##.......##..........##....##...... .##.....##.######...##.......######......##....######.. .##.....##.##.......##.......##..........##....##...... .##.....##.##.......##.......##..........##....##...... .########..########.########.########....##....######## """ def delete_handler(self) : if not self.hasAttr("deleteFromApi",self) : return tabela = self.atualWs['table'] if not 'alias' in self.atualWs else self.atualWs['alias'] """ Verifica se existem dados na tabela, se nao existir nao deve ser executado o delete """ tabela_ref_total = f""" SELECT COUNT(*) as TOTAL FROM {tabela} """ tabela_ref_resultado, error = self.getDb().executesql(tabela_ref_total) tabela_ref_total = tabela_ref_resultado._rows[0]['TOTAL'] if tabela_ref_resultado and len(tabela_ref_resultado._rows) > 0 else 0 if tabela_ref_total == 0 : self.createLog("500", "{}:{} -> {} - Rota de delete sem dados para excluir, verifique o motivo de estar vazia".format(tabela, self.rote, self.database_db)) #exit() return sql = self.dePara() #custom_where = self.call("custom_delete_" + self.fixed_rote()) #if not custom_where : # custom_where = '' # Se esta no relaciona ID e nao esta no SQL do dePara - que sao o que sao enviado, entao deve ser excluido select = f""" SELECT REL.NMTABELA AS tabela , REL.IDAPI AS {self.application_id} , REL.IDSOFTDIB AS softdib_id FROM TBRELACIONAID REL WHERE NMTABELA = '{tabela}' AND NMNOMEAPI = '{self.apiName}' AND IDSOFTDIB NOT IN ( SELECT softdib_id FROM ({ sql }) AS TEMPTB ); """ to_delete_list = self.select(select) for item in to_delete_list: self.current_data = item is_deleted = self.deleteFromApi(**item) if is_deleted : self.getDb().delete( "TBRELACIONAID" , [ f"IDAPI='{ item[self.application_id]}'"#.format( applicationId = ) , f"NMTABELA='{tabela}'"#.format( tabela=tabela ) , f"NMNOMEAPI='{self.apiName}'"#.format( tabela=tabela ) ] ) self.current_data = {} """ .########..##.....##.########....########...#######...######..######## .##.....##.##.....##....##.......##.....##.##.....##.##....##....##... .##.....##.##.....##....##.......##.....##.##.....##.##..........##... .########..##.....##....##.......########..##.....##..######.....##... .##........##.....##....##.......##........##.....##.......##....##... .##........##.....##....##.......##........##.....##.##....##....##... .##.........#######.....##.......##.........#######...######.....##... """ # chave : putpost def post_put_handler(self): self.template = self.getTemplate() self.doSets() sql = self.dePara() print(f"Iniciando busca no banco - rota: {self.atualWs['table']}")#.format()) self.doProcedure() where = 'WHERE request_method IS NOT NULL' if self.atualWs['MD5'] else '' select = f""" SELECT * FROM ({sql}) AS TEMPTB {where} """ ini = time.time() db_data = self.select(select) #db_data = list(filter(lambda data: data['softdib_id'] =='PFT08-08', db_data) ) if 'args' in dir(db_data) : self.createLog('mysqlError',"""{mysql_error} : {mysql_error_desc} - rote : {rote}""".format( mysql_error = db_data.args[0] , mysql_error_desc = db_data.args[1] , rote = self.atualWs['rote'] ) ) return print(f"tempo de execucao: {time.time() - ini}; total: {len(db_data)}") rote = self.fixed_rote() before_handle_db_data = self.call(f'db_data_{rote}', db_data) db_data = before_handle_db_data if before_handle_db_data != None else db_data if db_data : self.mount_ws_map_props(db_data[0]) ###for each in filter(lambda item: item['softdib_id'] == 'PF20-12' , db_data) : for each in db_data : self.current_data = each self.handle_db_data(each) else: pass def mount_ws_map_props(self, data): self.ws_map_props = {} for key in data : self.ws_map_props[key], = list(filter(lambda item: item['para'] == key , self.atualWs['fields'])) or [None] def handle_db_data(self, each) : """ Metodo que manipula os dados vindo do banco """ replacement = self.replacement( each, self.template ) try: json_data = json.loads(replacement) except ValueError as error: print(f"""####ERROR-JSON : {error}""") print(replacement) return self.send_handler( json_data ) def replacement(self, data, json_str:str) : """ Metodo que substitue valores de um json de acordo com suas dados - Exemplo: { "attr": "voar" } - quero {{attr}} - resultado: quero voar - Returna o json_str de entrada com a mudanca """ #json_str = json_str.format(**data) for key,value in data.items() : props = self.ws_map_props[key] value = self.process_value_replacements(value, props) regex = r'{{\s*'+re.escape(key)+ r'\s*}}' json_str = re.sub(regex, value, json_str or '{}') #FIXME: tentar remover pelo banco #Limpar retornos falhos dentro do template json_str = re.sub(r'\s*None\s*','""',json_str) #json_str = re.sub(r'','',json_str) json_str = self.multi_replace(json_str,[ ["###LINEBREAK###","\\n"], ['""""','""'] ]) return json_str def process_value_replacements(self, value, props) -> str : if props: if('custom_replacement' in props) : value = self.call(props.get('custom_replacement',''), value, is_required = True) return str(value) def prepare_send(self, data) : """ Metodo que prepara os dados de envio """ # valor default onde respeita que post é para cadastro e put para alteracao # se uma rota tem um comportamento diferente ao incluir um dados passar o metodo no map #assumePOST = "post_bahavior" in self.atualWs """ if "post_bahavior" in self.atualWs : assumePOST = True """ request_method = self.current_data.get('request_method', self.method.upper()) methods = self.atualWs['methods'] #self.current_method = self.current_data['request_method'] if not assumePOST else self.atualWs['post_bahavior'] if request_method in methods : self.current_method = request_method elif request_method not in methods : _filter = next(filter(lambda item: item not in ['DELETE'], methods)) self.current_method = _filter else : self.current_method = request_method #del each['method'] self.current_rote = self.atualWs['rote'] # se uma rota tem um caminho diferente criar no map qual dos dados sera alterado # ex: pedidos/numero_pedido/status - definir no map "custom_path" : {"de":"qualquer alias difinido no map" ,"para" : "numero_pedido"} if 'custom_path' in self.atualWs : for change in self.atualWs['custom_path']: de = change['de'] para = str(self.current_data[change['para']]) #(?=\/?)(token)(?=\/) #? este regex considera que toda interseccao comecara com / regex = r'\/(' + re.escape(de) + r')(?=\/?)' self.current_rote = re.sub(regex, "/"+para, self.current_rote ) #self.current_rote = self.current_rote.replace(change['para'], self.current_data[change['de']]) has_backslash = self.config.get('backslash') backslash = "/" if has_backslash == None or has_backslash else "" """ and 'put_type' in self.atualWs and self.atualWs['put_type'] == "pathname" """ if 'request_method' in self.current_data and \ self.current_data['request_method'] == 'PUT' and \ 'put_data' not in self.atualWs : item_id = "/" + self.current_data[self.application_id] self.current_rote = f"{self.current_rote}{item_id}" """ .format( rote = self.current_rote , item_id = item_id ) """ backslash = "" if self.atualWs.get('put_data') and self.atualWs['put_data'] != 'body' : data[self.atualWs['put_data']] = self.current_data[self.application_id] or "" self.current_rote = self.current_rote + backslash def send_handler(self, data) : """ Metodo para manipular o envio """ # o data pode ser alterado aqui dentro self.prepare_send(data) # Faz um tratamento no dados antes de enviar func_rote = self.fixed_rote() func = f"beforeSend_{func_rote}"#.format(func_rote) # Tratamento de dados antes de enviar beforeSend = self.call(func, data, is_required= self.atualWs.get('need_beforeSend',False)) #if self.hasAttr(func, self) : # data = getattr(self, func)( data ) #print("stop") if hasattr(self, 'ignore_request') and self.ignore_request == True: self.ignore_request = False return self.send( data ) # Executa apoas enviar - cada rota tem a sua especifica - deve-se criar dentro da arquivo da api if not self.atualWs['send'] or self.send_response.status_code in range(200,300) : func = f"afterSend_{func_rote}"#format(func_rote) # Tratamento de dados depois de enviar #if self.hasAttr(func, self) : # getattr(self, func)( data ) self.call(func, data ) self.current_data = {} def send(self ,data) : """ Envia dados para uma api - via POST ou PUT """ if 'send' in self.atualWs and self.atualWs['send'] == False: return raw_type_rote = self.atualWs.get('raw_type') raw_type_default = self.config.get('raw_type') #raw_type_rote or "json" # Por padrao o envio de dados é via json raw_type = raw_type_rote or raw_type_default or "json" # mudar em configuracoes o raw_type se for diferente - ver na documentacao de cada api if raw_type == "text" : data = json.dumps(data) raw_type = "data" if raw_type == 'urlencoded': raw_type = 'data' params = { "rote" : self.current_rote , "method" : self.current_method , raw_type : data } print(f":params |> {params}\n:data |> {self.current_data}") # ! SENDREQUEST self.send_response = self.throttling( **params ) # retrn status_code = self.send_response.status_code or 0 response_func = f"response_code_{status_code}"#.format(status_code) # Chama uma funcao especifica para cada resposta # pode ser generica ou cricada em cada arquivo da api call = self.call(response_func, self.send_response) if call == None: #self.response_log(self.send_response) print(f":fail |> Funcao status code {status_code} nao criado ainda \n data:{data}")#.format(status_code)) def throttling(self, **params) : """ Metodo que controla throttling - para ativar o throttling é preciso definir no map em config - Por padrao se nao existir o throttling definido ele nao é aplicado """ #if 'throttling' not in self.config or ('throttling' in self.config and not self.config['throttling']): # return self.api_request.doRequest(**params) #else :z while True: req = self.api_request.doRequest(**params) try: error = json.loads(req.text) if error['error'] == "Bad Request: Rate limit exceeded, retry in 1 second": print(req) except: pass throttling_time = self.call("throttling_rules",req) if not throttling_time : #sleep(5) break print(throttling_time) sleep(throttling_time) return req def mountSelectputPost(self, selectFields:list=[]): """[summary] Keyword Arguments: fields {list} -- [description] (default: {[]}) Returns: [type] -- [description] """ gn_md5 = self.controleMD5() if gn_md5 != False: selectFields.append(f""" /** * GERA UM HASH MD5 DOS CAMPOS, * USADO PARA APOS O ENVIO GRAVAR NA TABELA DEPARA * CRIADO EM MERCOS.PY */ {gn_md5} as hash_md5 """) #.format(gn_md5)) if gn_md5 != False and 'methods' in self.atualWs : _post = "" _put = "" _get = "" # gn_md5 = gn_md5 , primaryKey = self.processItem_dbfield({'dbfield' : self.atualWs['primaryKey']}) table = self.atualWs['table'] if not 'alias' in self.atualWs else self.atualWs['alias'] self.atualWs['joins'] = self.atualWs.get('joins',[]) self.atualWs['joins'].append( { "tbName":"TBRELACIONAID TBRELINNER", "merge":"LEFT JOIN", "on" : [ f"TBRELINNER.IDSOFTDIB = {primaryKey}", f"TBRELINNER.NMTABELA = '{table}'", f"TBRELINNER.NMNOMEAPI = '{self.apiName}'", ] }, ) #selectFields.append( f"""( # SELECT TBREL.IDAPI # FROM TBRELACIONAID TBREL # WHERE # TBREL.NMTABELA = '{table}' # AND # TBREL.IDSOFTDIB = {primaryKey} # AND # TBREL.NMNOMEAPI = '{self.apiName}' # ) AS {self.application_id}""" # #) selectFields.append( f""" TBRELINNER.IDAPI AS {self.application_id}""" ) """ .format( table=table , primaryKey=primaryKey , apiName=self.apiName , identifier=self.application_id ) """ selectFields.append( f"{primaryKey} AS softdib_id")#.format(primaryKey=primaryKey) ) func = "custom_case_" + self.atualWs['rote'] _custom_case = "" if 'custom_case' in self.atualWs : if not self.hasAttr(func, self) : self.createLog("999",f"Funcao deve ser criada: {func}")#.format(func)) else : _custom_case = getattr(self, "custom_case_" + self.atualWs['rote'])() if 'PUT' in self.atualWs['methods'] or 'POST' in self.atualWs['methods']: _put = f""" /** * SE O PRODUTO JAH ESTIVER NA MERCOS, DEVEMOS ATUALIZAR COM UM PUT * CRIADO EM MERCOS.PY */ WHEN ( SELECT TBRELINNER.HASHMD5 IS NOT NULL AND UPPER(TBRELINNER.HASHMD5) <> UPPER({gn_md5}) ) THEN 'PUT' """ #.format( # table = table # , gn_md5 = gn_md5 # , apiName = self.apiName # , primaryKey = primaryKey #) #if 'POST' in self.atualWs['methods'] : _post = f""" /** * SE O ITEM NAO ESTIVER NA TABELA DE RELACIONAMENTO * CRIADO EM MERCOS.PY */ WHEN ( SELECT TBRELINNER.HASHMD5 IS NULL ) THEN 'POST' """ #.format( # table = table # , apiName = self.apiName # , primaryKey = primaryKey # #) if _put != "" or _post != "": case = f"""( /** * CASE PARA A MONTAGEM DA COLUNA MERCOSHASH, * ELA O METHODO DE ENNVIO, * POST OU PUT * CRIADO EM MERCOS.PY */ CASE {_post} {_custom_case} {_put} /** * SE O VALOR JA EXISTIR E O HASH FOR O MESMO, NAO PRECISA ENVIAR * CRIADO EM MERCOS.PY */ ELSE NULL END ) AS 'request_method' """ #.format( # POST=_post # , PUT=_put # , CUSTOM_CASE = _custom_case #) selectFields.append(case) return self.mountSelect(selectFields) """ .########..########..######..########...#######..##....##..######..######## .##.....##.##.......##....##.##.....##.##.....##.###...##.##....##.##...... .##.....##.##.......##.......##.....##.##.....##.####..##.##.......##...... .########..######....######..########..##.....##.##.##.##..######..######.. .##...##...##.............##.##........##.....##.##..####.......##.##...... .##....##..##.......##....##.##........##.....##.##...###.##....##.##...... .##.....##.########..######..##.........#######..##....##..######..######## """ def tryJson(self, req ) : #""" Tenta retornar um json de uma requisicao http """ try: json = req.json() except : json = {} return json def response_code_0(self, req) : print("response_code_0") def response_code_201(self, response) : if "relacionaId" not in self.atualWs or ( "relacionaId" in self.atualWs and self.atualWs['relacionaId'] == False ): return False identifier = self.application_id if not self.current_data[identifier] and 'control-id' in self.atualWs: control = self.atualWs.get('control-id') if control == 'custom' : # Criar em cada arquivo da integracao o custom especifico #self.current_data[identifier] = getattr(self, "custom_control_id")(response) self.current_data[identifier] = self.call("custom_control_id", response, is_required = True) elif control == 'by_rote': fn = f"custom_control_id_{self.fixed_rote()}" self.current_data[identifier] = self.call(fn, response, is_required = True) else : json_response = self.tryJson(response) self.current_data[identifier] = self.jsonValueByPath(control, json_response) or self.current_data[control] #if control in json_response : #self.current_data[identifier] = self.jsonValueByPath(control, json_response) # self.current_data[identifier] = json_response[control] #elif "/" in control: #else: #self.current_data[identifier] = self.current_data[control] IDAPI = self.current_data.get(identifier) SOFTDIB_ID = self.current_data.get('softdib_id') HASHMD5 = self.current_data.get('hash_md5') NMTABELA = self.atualWs.get('alias', self.atualWs.get('table')) #self.atualWs.get('table') if not \ #'alias' in self.atualWs else self.atualWs['alias'] if not IDAPI and not SOFTDIB_ID and NMTABELA: return data_relaciona_id = { "NMNOMEAPI" : self.apiName , "NMTABELA" : NMTABELA , "IDSOFTDIB" : SOFTDIB_ID , "DTULTIMAALTERACAO" : "now()" , "HASHMD5" : HASHMD5 , "IDAPI" : IDAPI } self.relatement(data_relaciona_id) print(f":ok |> [{NMTABELA}] {SOFTDIB_ID} - {IDAPI}") return True def response_code_400(self, response) : self.response_log(response) return False def response_code_404(self, response) : self.response_log(response) notAllowRote = self.call('not_allow_rote') or [] if self.atualWs['rote'] not in notAllowRote: self.getDb().delete( "TBRELACIONAID" , [ f"IDSOFTDIB ='{self.current_data['softdib_id']}'" #.format(self.current_data['softdib_id']) , f"IDAPI ='{self.current_data[self.application_id]}'" #.format(self.current_data[self.application_id]) , f"NMTABELA ='{self.atualWs ['table' ]}'" #.format(self.atualWs ['table' ]) ] ) def response_code_200(self, response) : print("response_code_200") def response_code_422(self, response) : self.response_log(response) def response_code_412(self, response) : self.response_log(response) def relatement(self, data): cond_params = [ f"NMTABELA = '{data.get('NMTABELA' )}'" # .format(table = data['NMTABELA' ]) #, f"IDAPI = '{data.get('IDAPI' )}'" #.format(id_api = data['IDAPI' ]) , f"IDSOFTDIB = '{data.get('IDSOFTDIB' )}'" # .format(id_sd = data['IDSOFTDIB' ]) , f"NMNOMEAPI = '{data.get('NMNOMEAPI' )}'" ] exists = self.exists("IDAPI", "TBRELACIONAID", cond_params) #table = self.atualWs['table'] if not 'alias' in self.atualWs else self.atualWs['alias'] if not exists : #dataFields['IDAPI'] = idapi self.getDb().insert( "TBRELACIONAID", data) else: query = self.getDb().update( "TBRELACIONAID", data, cond_params) #print(f"{query[0]._result.message}") #self.createLog(200, f"{query[0]._result.message}") def response_log(self, response): if 'softdib_id' not in self.current_data: self.current_data['softdib_id'] = 'relaciona_cli_pedido' text = '' try: text = response.json() except : text = response.text #print(text) self.createLog(response.status_code,"{text}| id Softdib: {sdid} | rote: {rote} | metodo {method}".format( text = text , sdid = self.current_data['softdib_id'] , rote = self.atualWs['rote'] , method = self.current_method )) """ .########.####.##.......######## .##........##..##.......##...... .##........##..##.......##...... .######....##..##.......######.. .##........##..##.......##...... .##........##..##.......##...... .##.......####.########.######## """ def fileHandler(self) : print("Ainda sera implementado") """ ..######...########.######## .##....##..##..........##... .##........##..........##... .##...####.######......##... .##....##..##..........##... .##....##..##..........##... ..######...########....##... """ def get_handler(self) : #""" # Manipulador de todos as rotas com o metodo GET # - Cada rota pode ter parametros na busca # - Deve-se tratar esses parametros numa funcao especifica criar uma funcao # rote_filter_ + "nome_da_rota" no arquio de sua propria integracao #""" """ rote = self.atualWs['rote'] func_filtro_rote = "rote_filter_" + self.atualWs['rote'] if self.hasAttr(func_filtro_rote, self) : rote = self.atualWs['rote'] + "/?" + getattr(self, func_filtro_rote)() """ """ func_before_get = "beforeGet_" + self.atualWs['rote'] if self.hasAttr(func_before_get, self) : self.atualWs['rote'] + "/?" + getattr(self, func_filtro_rote)() """ rote_slug = self.fixed_rote() params = self.call("rote_filter_" + rote_slug) rote = self.atualWs['rote'] + "?" + params if params else self.atualWs['rote'] self.createLog("444",rote) rote = self.call("format_rote_" + rote_slug, rote) or rote data = self.pagination(rote) use_softdib_api = self.atualWs.get('use_softdib_api') if use_softdib_api == None or use_softdib_api: # or self.atualWs['useSoftdibApi'] == 'true' : self.define_sd_request() # Acessa uma funcao generica ou nao para manipular os dados do resultado do GET # Deve ser definido no map.json #getattr(self, self.atualWs['rote'] + "_handler")(data) self.call(self.atualWs['handler'] + "_handler", data) def pagination(self, rote) : self.call("beforeGet_" + self.fixed_rote()) self.response = response = self.throttling( rote=rote ) if not response.ok : self.createLog(response.status_code,f"Erro nos dados: {rote} - {response.content.decode('utf-8')} ") return [] data = self.tryJson(response) if not data : return [] afterGet_data = self.call("afterGet_" + self.fixed_rote(), data) if afterGet_data != None: data = afterGet_data next_page = self.call("pagination_rules", response, data) if next_page: data = data + self.pagination(rote=next_page) return data """ .########.##....##.##.....##.####..#######.....########...#######.....########..########.########..####.########...#######. .##.......###...##.##.....##..##..##.....##....##.....##.##.....##....##.....##.##.......##.....##..##..##.....##.##.....## .##.......####..##.##.....##..##..##.....##....##.....##.##.....##....##.....##.##.......##.....##..##..##.....##.##.....## .######...##.##.##.##.....##..##..##.....##....##.....##.##.....##....########..######...##.....##..##..##.....##.##.....## .##.......##..####..##...##...##..##.....##....##.....##.##.....##....##........##.......##.....##..##..##.....##.##.....## .##.......##...###...##.##....##..##.....##....##.....##.##.....##....##........##.......##.....##..##..##.....##.##.....## .########.##....##....###....####..#######.....########...#######.....##........########.########..####.########...#######. """ def check_client_exists(self, code): return self.exists("IDAPI","TBRELACIONAID", [f"IDSOFTDIB='{code}'", "NMTABELA ='TBCLIENTE'"]) def pedidos_handler(self, data) : """Funcao para manipular os dados vim do GET de pedidos de todas as integraçoes - Ela grava os dados dos pedidos no sistema Softdib Args: data (dict): [] """ # for order in filter(lambda order: order['id'] in [150700699], data): # - mercos #for order in filter(lambda order: order['numero'] in [35728,35737], data): # - mercos - pelo numero #for order in filter(lambda order: order['numero'] in [34364], data): # - lojaintegrada #for order in filter(lambda order: order['numero'] in [42038], data): # - lojaintegrada #for order in filter(lambda order: order['numero'] in [39000, 39400], data): # - lojaintegrada #for order in filter(lambda order: order['order_id'] in [ '825'], data): # - spirit #for order in filter(lambda order: order['Order']['id'] in ["1645"], data): #- tray #for order in filter(lambda order: order['code'] in [203], data): #- sellent axion #for order in filter(lambda order: order['code'] in [27], data): #- sellent axion #for order in filter(lambda order: order['code'] in [1505], data): #- sellent rotalux # for order in filter(lambda order: order['id'] in [103468,103502,103544,103206,103033,103139,103149,103162,102986,102867,102936,102753], data): # - woocommerce # for order in filter(lambda order: order['id'] in [734], data): # - woocommerce #for order in filter(lambda order: order['id'] in [1272], data): # - ideris #for order in filter(lambda order: order['sequence'] in [623], data): # - mobilesales #for order in filter(lambda order: order['numero'] in [137249], data): # - bling # for order in filter(lambda order: order['Order']['id'] in ["45"], data): #- tray for order in data: code = self.jsonValueByPath(self.atualWs.get('identifier','id'),order) order_already_exists = self.exists( "IDTBPEDIDOSRETORNO" , "TBPEDIDOSRETORNO" , [ f"CDPEDIDOEXTERNO='{code}'"#.format(order[self.atualWs['identifier']]) , f"NMAPI='{self.apiName}'"#.format(self.apiName) ] ) if order_already_exists: continue order_to_send = dict() try: self.current_order = order # Ira extrair dodos os dados referente a pedidos e aos items # Ele extrai os valores relacionado ao que foi definido no map order_to_send['pedido' ] = self.call("get_order_data" , self.current_order, is_required = True) #[ self.extractValues(order, 'pedido_fields') ] # Existe um tratamento no Ideris, que se o pedido for Mercado Livre e o tipo for igual a FULL ele não irá importar o pedido if order_to_send['pedido' ][0]['CDFRETE'] == 0: continue order_to_send['itenscarrinho'] = self.call("get_order_items", self.current_order, is_required = True) order_to_send['volumes'] = self.call("get_order_volumes", self.current_order, is_required = False) order_to_send['etiquetas'] = self.call("get_order_etiquetas", self.current_order, is_required = False) #continue # funcao para verificar se o codigo do cliente existe no sistema softdib if len( order_to_send['pedido'] ) and ( not order_to_send['pedido'][0]['CDCLIENTE'] or self.atualWs.get('alwaysUpdateClient')): cli_data, = order_to_send['pedido'] #cli_data['IDCLIENTEEXTERNO'] = '' #if self.hasAttr("customer_data", self) : # Funcao para recuperar os dados do cliente caso nao venha os dados completo para cadastro e recuperar id do cliente #data = getattr(self, "customer_data")( data ) #else : customer_data = self.call('customer_data', cli_data) order_to_send['pedido'][0]['CDCLIENTE'] = self.salvar_cliente_softdib(customer_data or cli_data) self.salvar_pedido_softdib(order_to_send) except ZeroDivisionError: print(traceback.format_exc()) # except Exception as error: # self.createLog("999", str(error)) # continue #print(error) # Limpa a variavel self.current_order = {} def salvar_pedido_softdib(self, data) : """ Metodo para salvar pedidos na softdib """ pedido, = data['pedido'] self.createLog('001',f"Inicio da gravacao do pedido: {pedido.get('IDTBPEDIDO','')}") response = self.softdib_request.doRequest( rote = "/integracao/pedido/pedido" , data = json.dumps(data) , method = "POST" ) retorno_cobol = self.tryJson(response) if response.status_code == 200 and retorno_cobol : self.createLog(response.status_code,f"Pedido cadastrado com sucesso : { retorno_cobol[-1]['CDPEDIDO']} - {retorno_cobol[-1]['CDPEDIDOEXTERNO']}") if "save_status" in self.atualWs and len(retorno_cobol) : saved = self.save_status(retorno_cobol) else : self.createLog( response.status_code, retorno_cobol ) return False # Mesmo salvo o cobol pode retornar vazio # Sempre verificar o save_status, criar o deletar status if retorno_cobol : # Geralmente ocorra o UPDATE pois o Cobol ja salva esses dados dtulitmaalteracao = self.call("dateLastChanges") or pedido.get('DTULTIMAALTERACAO') or 'now()' #if self.hasattr("dateLastChanges", self) : # retorno_cobol[-1]['DTULITMAALTERACAO'] = self.dateLastChanges() #dtulitmaalteracao = retorno_cobol[-1].get('DTULITMAALTERACAO') dataFields = { "NMNOMEAPI" : self.apiName , "NMTABELA" : "TBPEDIDOSRETORNO" , "IDAPI" : retorno_cobol [-1]['CDPEDIDOEXTERNO' ] , "IDSOFTDIB" : retorno_cobol [-1]['CDPEDIDO' ] , "DTULTIMAALTERACAO" : dtulitmaalteracao , "HASHMD5" : retorno_cobol [-1]['NMCHAVEUNICAPEDIDO'] } # Exibir ID Pedido | ID Softdib # print("{}=>{}".format(dataFields['IDAPI'], dataFields['IDSOFTDIB'])) self.relatement(dataFields) return True def save_status(self, retorno_cobol) : """ Por enquano cada api tem seu tratamento criar funcao dentro do arquivo da api """ return self.getDb().insert( "TBPEDIDOSSTATUS", { 'NMLKGRUPO' : self.nmlkgrupo.upper() , 'CDEMPRESA' : self.cdempresa , 'CDFILIAL' : self.cdfilial , 'NMAPI' : self.apiName , 'CDPEDIDOEXTERNO' : retorno_cobol[-1]['CDPEDIDOEXTERNO'] , 'CDPEDIDO' : retorno_cobol[-1]['CDPEDIDO'] , 'DTSTATUS' : "now()" , 'CDSTATUS' : 1 , 'NMOBSERVACAO' : f"O pedido foi cadastrado com sucesso em {self.timeNow()} : Cod. { retorno_cobol[-1]['CDPEDIDO']} " }) #insert #getattr(self, "save_status_" + self.atualWs['rote'])() #print(response_orders_status) def salvar_cliente_softdib(self, data): """ Cadastro de cliente quando nao existe no sistema softdib """ response = self.softdib_request.doRequest( rote = "/integracao/pedido/cliente" , params = data , method = "POST" ) if response.status_code == 200 : retorno_cobol = response.json() codigo_cliente = retorno_cobol['cliente'][0]['Codigo'] data_relaciona_id = { "NMNOMEAPI" : self.apiName , "NMTABELA" : "TBCLIENTE" , "IDSOFTDIB" : codigo_cliente , "DTULTIMAALTERACAO" : data.get('DTULTIMAALTERACAO', "now()") , "IDAPI" : data.get('IDCLIENTEEXTERNO', '') , "HASHMD5" : "NextUpdateWillGenerateNewHash" } self.relatement(data_relaciona_id) self.createLog(response.status_code,f"cliente cadastrado com sucesso : {codigo_cliente}:{data.get('IDCLIENTEEXTERNO', '')}") return codigo_cliente def salvar_transportadora_softdib(self, data): """ Cadastro de cliente quando nao existe no sistema softdib """ data['FGTRANSPORTADOR'] = "S" response = self.softdib_request.doRequest( rote = "/integracao/pedido/cliente" , params = data , method = "POST" ) if response.status_code == 200 : retorno_cobol = response.json() codigo_cliente = retorno_cobol['cliente'][0]['Codigo'] data_relaciona_id = { "NMNOMEAPI" : self.apiName , "NMTABELA" : "TBTRANSPORTADOR" , "IDSOFTDIB" : codigo_cliente , "DTULTIMAALTERACAO" : data.get('DTULTIMAALTERACAO', "now()") , "IDAPI" : data.get('IDCLIENTEEXTERNO', '') , "HASHMD5" : "NextUpdateWillGenerateNewHash" } self.relatement(data_relaciona_id) self.createLog(response.status_code,f"cliente cadastrado com sucesso : {codigo_cliente}:{data.get('IDCLIENTEEXTERNO', '')}") return codigo_cliente def define_sd_request(self,rota='/integracao/pedido/authentication'): """ Monta a estrutura base de headers e paramentros para consumir ou enivar dados para api softdib """ prot = "http://" base = "192.168.1.3" if self.debug else self.softdib_ip port = f':{self.softdib_port or 3000}'#.format() base_url = f"{prot}{base}{port}" #""" .format( # prot=prot # , base=base # , port=port #) """ auth = Request( base_url = base_url ) auth_res = auth.doRequest( rote = rota, method = "post", json = { "user" : "SAC" , "lkgp" : self.nmlkgrupo , "banco" : self.nmbanco , "apiname" : self.apiName } ) if auth_res.status_code != 200 : print('Falha ao autenticar na Softdib') print(f"Falhou: {auth_res.status_code} - {auth_res.text} - {auth_res.reason}")#.format(auth_res.status_code)) exit() self.softdib_request = Request( base_url = base_url, headers = { "Content-Type": "application/json" ,"Accept": "application/json" ,"x-access-token": auth_res.text.replace('"','') } ) print(self.softdib_request) #!gn def extractValues(self, data, target) : fields = self.atualWs[target] values = self.generate_values(fields, data) values['CDEMPRESA'] = self.cdempresa values['FILIAL' ] = self.cdfilial return values #!gn def generate_values(self, fields, data): values = dict() for field in fields: new_value = self.handleField(field, data) if isinstance(new_value, bool) and not new_value: new_value = 0 # Verifica se o campo 'para' contém índices de array para_path = field['para'] if '/' in para_path and any(part.isdigit() for part in para_path.split('/')): self.set_nested_value(values, para_path, new_value) else: values[para_path] = new_value return values def set_nested_value(self, obj, path, value): """ Define um valor em uma estrutura aninhada baseada no caminho Exemplo: path = "telefones/0/numero" Resultado: obj["telefones"][0]["numero"] = value """ parts = path.split('/') current = obj for i, part in enumerate(parts[:-1]): # Todos exceto o último if part.isdigit(): # É um índice de array index = int(part) # Garante que current seja uma lista if not isinstance(current, list): raise ValueError(f"Esperado lista no caminho {'/'.join(parts[:i])}, mas encontrou {type(current)}") # Expande a lista se necessário while len(current) <= index: current.append({}) current = current[index] else: # É uma chave de dicionário if part not in current: # Verifica se o próximo elemento é um índice next_part = parts[i + 1] if i + 1 < len(parts) else None if next_part and next_part.isdigit(): current[part] = [] # Cria uma lista else: current[part] = {} # Cria um dicionário current = current[part] # Define o valor final last_part = parts[-1] if last_part.isdigit(): index = int(last_part) while len(current) <= index: current.append(None) current[index] = value else: current[last_part] = value def handleField(self, item, data): value = False self.item_data = item if "integration" in item : value = self.jsonValueByPath(item['integration'], data) #if item['integration'] in data : if item['integration'] == 'root': value = data if "list" in item: field_list = item.get("list",{}) if isinstance(value, dict) : return [self.generate_values(field_list, value)] elif isinstance(value, list) : return [self.generate_values(field_list, v) for v in value] if "fields" in item: field_child = item.get("fields",{}) return self.generate_values(field_child, value) if "default" in item and not value: value = item['default'] if "custom_translate" in item: #Se o campo tem um tratamento unico chama a funcao dele #value = getattr(self, item['custom_translate'])( value ) value = self.call(item['custom_translate'], value) if "translate_by_item" in item : value = self.call(item['translate_by_item'], value, item) if "translate_by_branch" in item : currentValue = value func_name = f"{item['translate_by_branch']}_{self.nmlkgrupo.lower()}" value = self.call(func_name, value, item) if value == None: value = currentValue if "translate" in item : value = self.translate(id=value, table=item['translate'], reverse=True) if 'translate_by_type' in item: value = self.translateByType(value, item['translate_by_type']) if 'globalThis' in item: globalThis = item['globalThis'] attr = globalThis if isinstance(globalThis, str) else item['integration'] setattr(self, attr.replace("/","_"), value ) return value """ .########.########.....###....##....##..######..##..........###....########.######## ....##....##.....##...##.##...###...##.##....##.##.........##.##......##....##...... ....##....##.....##..##...##..####..##.##.......##........##...##.....##....##...... ....##....########..##.....##.##.##.##..######..##.......##.....##....##....######.. ....##....##...##...#########.##..####.......##.##.......#########....##....##...... ....##....##....##..##.....##.##...###.##....##.##.......##.....##....##....##...... ....##....##.....##.##.....##.##....##..######..########.##.....##....##....######## """ #!gn def translate(self, **args ) : """ A Tradução eh a relacao do codigo da api com a codigo da softdib ou vice versa - Por padrao a traducao ocorre de um codigo da softdib para um codigo da api. - se quiser ao contrario mduar reverse para true na chamada da funcao - sql = True retonar apenas o sql gerado - lembrando que o codigo ja deve estar cadastrado na tabela de relacionamento - @params - reverse = False, id = id da softdib ou da integracao, sql = False """ relaciona_field = 'IDAPI' relaciona_key = 'IDSOFTDIB' if 'reverse' in args and args['reverse'] == True: relaciona_field = 'IDSOFTDIB' relaciona_key = 'IDAPI' retorno_sql = args.get('sql') random_name = self.randName("TBRELACIONAID", False) sql = f""" SELECT {random_name}.{relaciona_field} FROM TBRELACIONAID {random_name} WHERE {random_name}.NMTABELA = '{args['table']}' AND {random_name}.NMNOMEAPI = '{self.apiName}' AND {random_name}.{relaciona_key} = {args['id'] if retorno_sql else "'{}'".format(args['id'])} """ #""" .format( # table = args['table'] # , id = args['id'] # , relaciona_field = relaciona_field # , relaciona_key = relaciona_key # #, random_name = random_name #) """ if retorno_sql: return f"({sql})"#.format(sql) resultado = self.getDb().select(sql) return self.fetchFirst(resultado, relaciona_field) #!gn def translateByType(self, value, _type) : if isinstance(value, dict) and isinstance(value, list) : return 'ERROR' if _type == 'numero' : telefone = re.sub(r'(^([0]|[(])+|[^\d])', '', value) return telefone[2:] elif _type == 'ddd': telefone = re.sub(r'(^([0]|[(])+|[^\d])', '', value) return telefone[0:2] elif _type == 'date': return value.split(' ')[0] elif _type == 'hour': return value.split(' ')[1] else : return '' pass #!gn def order_id(self, value): return self.current_order.get(self.atualWs['identifier']) """ ..######...#######..##...... .##....##.##.....##.##...... .##.......##.....##.##...... ..######..##.....##.##...... .......##.##..##.##.##...... .##....##.##....##..##...... ..######...#####.##.######## """ def dePara(self): # fields=[] # self.table = "" # self.atualDados = dados # where = ['1=1'] # TODO montar esquema para where sql = f""" {self.mountSelectputPost ( [] ) } {self.mountJoins ( ) } {self.mountWhere ( [] ) } {self.mountGroupBy ( ) } {self.mountOrderBy ( ) } """ #""" .format( # # _select = self.mountSelectputPost ( [] ) # , _joins = self.mountJoins ( ) # , _where = self.mountWhere ( [] ) # , _groupby = self.mountGroupBy ( ) # , _orderBy = self.mountOrderBy ( ) #) """ #func = "processSQL_"+ self.atualWs['rote'] #if self.hasAttr(func, self) : # sql = getattr(self, func)(sql) sql_processed = self.call("processSQL_"+ self.fixed_rote(), sql) self.println(sql_processed or sql) return sql_processed or sql #!nunca esquecer do ";" nesses casos def doProcedure(self) : if "procedure" in self.atualWs : procedure = "sp_" + self.atualWs['rote'] if type(self.atualWs['procedure']) == bool else self.atualWs['procedure'] if not isinstance(self.atualWs['procedure'], list) : self.getDb().executesql(f"CALL {procedure}();")#.format(procedure=procedure)) else : for each_procedure in self.atualWs['procedure'] : self.getDb().executesql(f"CALL {each_procedure}();")#.format(procedure= each_procedure)) else: return '' def doSets(self) : if "group_concat" in self.atualWs: self.getDb().executesql('SET SESSION group_concat_max_len = 18446744073709551615;') if "set" in self.atualWs : self.getDb().executesql(self.atualWs.get('set')) else : return '' def mountSelect(self, fields:list=[]) -> str : """Monta o SELECT para o sql Keyword Arguments: fields {list} -- [Lista de campos, passado por paramentro para poder extender mais facilmente] (default: {[]}) Returns: str -- [Retorna o script assim: "SELECT CAMPO1, CAMPO2, CAMPO3"] """ # Percorre os campos declarados no mapa for dado in self.atualWs['fields']: # processa cada item baseado no tipo, dbfield, default, _custom ... fields.append( self.processItem(dado) ) # junta tudo com virgula fields = (","+" "*18).join(fields) # return f"SELECT {fields} FROM {self.atualWs.get('table','')}"#.format(fields, self.atualWs['table']) def controleMD5(self): """??? """ # se tiver controle de md5 if "MD5" in self.atualWs and self.atualWs['MD5']: # campos a serem hacheados md5Fields = [] if 'md5Fields' in self.atualWs: # percorre os capos da tabela for index,field in enumerate(self.atualWs['md5Fields']): # pega apenas os campos do tipo banco de dados #if 'dbfield' in field: # faz uma lista com todos dbfield = field.get('dbfield') customDbfield = False if 'custom_dbfield' in field : customDbfield = self.call(field['custom_dbfield'], dbfield) self.atualWs['fields'][index]['dbfield'] = customDbfield # field = customDbfield or dbfield field and md5Fields.append(field) else: # percorre os capos da tabela for index,field in enumerate(self.atualWs['fields']): # pega apenas os campos do tipo banco de dados #if 'dbfield' in field: # faz uma lista com todos dbfield = field.get('dbfield') customDbfield = False if 'custom_dbfield' in field : customDbfield = self.call(field['custom_dbfield'], dbfield) self.atualWs['fields'][index]['dbfield'] = customDbfield # field = customDbfield or dbfield field and md5Fields.append(field) # ordena a lista md5Fields = sorted(md5Fields) # manda para o metodo converter os tipos e fazer o join md5Fields = self.processItem_dbfield({'dbfield' : ' + '.join(md5Fields)}) # adiciona a funcao do banco #gn_md5 = f'CAST( MD5({md5Fields}) AS CHAR)'#.format(md5Fields) gn_md5 = f'MD5({md5Fields})'#.format(md5Fields) # adiciona o MD5 ao select '''''' return gn_md5 # Se nao tiver controle de md5 else: return False def mountJoins(self) -> str : #!arrumar aqui esta com erro, quando nao encontra dados no DExPARA joins_query = "" joins = self.atualWs.get('joins',[]) if joins : str_join = [] for join in self.atualWs["joins"] : tbName = join['tbName'] #alias = self.randName(tbName) on = '' if "on" in join : on = " AND ".join(join['on']) on = f"ON ({on})"#.format(on=on) merge = join['merge'] str_join.append(f"{merge} {tbName} {on}") """ .format( tbName = tbName , merge = merge #, alias = alias , on = on )) """ """ joins.append(join['script'].replace( ' {0} '.format(join['tbName']) , ' {0} AS {1} ON {3}'.format( join['tbName'], self.randName(join['tbName']), join['on'] ) )) """ joins_query = "\n".join(str_join) return joins_query def mountWhere(self, whereFields:list=[]) -> str : where = [ "1=1" ] if 'where' in self.atualWs: where += self.atualWs['where'] # where += whereFields # where = ' AND ' .join(where) if '@empresa' in where: where = where.replace('@empresa', str(int(self.cdempresa))) if '@filial' in where: where = where.replace('@filial', str(int(self.cdfilial))) if hasattr(self, 'rote_data') and self.rote_data and isinstance(self.rote_data, dict) and 'where' in self.rote_data: where_conditions = self.rote_data['where'] # Se where_conditions for um dicionário, percorrer e montar as condições if isinstance(where_conditions, dict): for key, value in where_conditions.items(): where += f" AND {key} = '{value}'" # return f" WHERE {where} "#.format(where) def mountOrderBy(self) -> str : """Monta a condicao orderby Returns: str -- OrderBy """ orderBy = "" if 'orderBy' in self.atualWs: orderBy = ' '.join(self.atualWs['orderBy']) orderBy = f" ORDER BY {orderBy}"#.format(orderBy) return orderBy def mountGroupBy(self) -> str : """Monta a condicao groupBy Returns: str -- groupBy """ groupBy = "" if 'groupBy' in self.atualWs and len(self.atualWs['groupBy']): groupBy = ' '.join(self.atualWs['groupBy']) groupBy = f" GROUP BY {groupBy} HAVING COUNT(*) > 0"#.format(groupBy, self.atualWs['primaryKey']) return groupBy #!deprecated def randName(self, table:str, save=True) -> str: """Generate a random string of fixed length Arguments: table {str} -- [description] Returns: str -- [description] """ stringLength = 10 if table in self.tableNames: return self.tableNames[table] # gera um nome aleatorio aleatorios = [random.choice(string.ascii_uppercase) for i in range(stringLength)] randNm = ''.join(aleatorios) randNm = f'/*{table}*/{randNm}'#.format(table=table, randNm=randNm) if not save: return randNm if randNm in self.tableNames.values() : randNm = self.randName(table) else: #self.tableNames.append(randNm) self.tableNames[table] = randNm return randNm def processItem(self, item:dict ) -> str: """ Monta o formado do select """ value = "" if 'dbfield' in item: value = self.processItem_dbfield(item) if '_custom' in item: value = self.processItem__custom(item) if 'translate' in item: value = self.translate(id=value, table=item['translate'],sql=True) if 'default' in item and not value: value = self.processItem_default(item) if 'prepend' in item : value = self.concat_sql(f"'{item['prepend']}'", value) if 'append' in item : value = self.concat_sql(value, f"'{item['append']}'")#.format()) if "_type" in item : value = self.process_type(value , item["_type"]) value = f"{value} AS '{item['para']}' "#.format(value, item["para"]) value = f" {value}\n"#.format(value) return value def process_type(self, value_in , _type) : value_out = value_in if _type == "integer" : value_out = f"(SELECT IF ({value_in}='', 0, FLOOR({value_in})))"#.format(value_in) if _type == "text" : value_out = f"(SELECT REPLACE({value_in}, CHAR(10), '###LINEBREAK###'))"#.format(value_in) if "cast" in _type.lower(): to_cast = _type.split(":")[-1] value_out = f'(SELECT CAST({value_in} AS {to_cast}))' return value_out # '''''' def processItem_default(self, field:dict) -> str: """Faz o processamento de valores do tipo defaults Arguments: field {object} -- Objeto com os dados da coluna {'dbfield':str, 'para':str, 'type':str ,'desc': str} Returns: str -- string do canpo tratado """ # lista com as variacoes de null nulsVariations = ['null', '\'null\'', 'NULL', '\'NULL\''] # pega o valor a ser trabalhado value = field["default"] # se vier null, troca por NULL maiusculo para que o banco entenda if value in nulsVariations: retorno = "NULL" # se nao for nul, faz outro tratamento else: # se for booleano if 'type' in field and field['type'] == 'Boolean': retorno = f"{value}" #.format(value) # se for qualquer outro tipo de dado, coloca entre aspas e eh sucesso else: retorno = f"'{value}'"#.format(value) # volta para ser coloccno no script return retorno def processItem_dbfield(self, field): field = field['dbfield'] self.table = field.split('.')[0].strip() # se nao tiver mais, soh retorna if '+' not in field : return field # se tiver +, faz o processamento #partes = field.split('+') partes = re.split('''\+(?=(?:[^']*\'[^']*\')*[^']*$)''',field) return self.concat_sql(*partes) def processItem__custom(self, field): return getattr(self, field["_custom"])(field) def select(self, sql): #exit() resultado = self.getDb().select(sql) return resultado def getDb(self): if self.db == False : self.db = gn_mysql( self.nmlkgrupo , self.database_db, self.debug ) return self.db def concat_sql(self, *values:list) : """ Funcacao para criar concat de retono para o mysql Ele insere o valor puro, se quiser concatenar uma string use "'{}'".format(value) """ cast = lambda text : f'CAST(COALESCE({text.strip()},"") AS CHAR)'#.format(text.strip()) concat = lambda text : f'CONCAT({text})'#.format(text) casts = ", ".join([cast(value) for value in values]) return concat(casts) def exists(self, field ,table, where = []): """ Verifica se um registro ja existe na tabela. ------------- Params: ``field`` - Campo de referencia. ``table`` - Tabela que deseja consultar. ``where`` - Condicao exclusiva . """ if len(where) > 0: where = f'WHERE 1=1 AND {" AND ".join(where)}'#.format(where=) sql = f""" SELECT COUNT({field}) AS qtd FROM {table} {where}"""#.format(field=field,table=table,where=where) exists = self.getDb().select(sql) return True if exists[0]['qtd'] > 0 else False """ .##........#######...######....######. .##.......##.....##.##....##..##....## .##.......##.....##.##........##...... .##.......##.....##.##...####..######. .##.......##.....##.##....##........## .##.......##.....##.##....##..##....## .########..#######...######....######. """ def println(self, _print) : if self.debug : print(_print) else : self.writeLog(str(_print), self.config["path_log"]) def timeNow(self): return datetime.strftime(datetime.now(),'%d/%m/%Y %H:%M:%S') def createLog(self, status_code, mensagem): flag = "w+" if status_code == '000' else "a+" conteudo = f"{self.timeNow()};{status_code};{mensagem}\n" """ .format( timeNow = self.timeNow() , status_code = status_code , mensagem = mensagem ) """ #file_name = self.apiName + "_log.txt" #path = "./logs/" if self.debug else "/var/tmp/" self.writeLog(conteudo,self.saida, flag) def writeLog(self, content="", file_name="", flag = "a+") : self.write_file(data=content,path=file_name,flag=flag,encoding='utf8') #with open(file_name, flag, encoding='utf8') as log : # log.write(content) def __getattr__(self, name): def error__(*args, **kwargs): print ("\"%s\" Nao eh um metodo da classe \"%s\"" % (name, self.__class__.__name__)) return False return error__ """ .##.....##.########.####.##........######. .##.....##....##.....##..##.......##....## .##.....##....##.....##..##.......##...... .##.....##....##.....##..##........######. .##.....##....##.....##..##.............## .##.....##....##.....##..##.......##....## ..#######.....##....####.########..######. """ def only_numbers(self, data): return re.sub(r'[^\d]','',data) def get_index(self, arr:list, index:int) : try: return arr[index] except : return None def read_file(self, path ="",flag = "r", encoding='utf8') : return self.handle_file(handle="read", path =path,flag = flag, encoding=encoding) def write_file(self, data="", path="", flag = "w+", encoding='utf8') : return self.handle_file(handle = "write",data=data, path =path,flag = flag, encoding=encoding) def handle_file(self, **kwargs) : try: with open(kwargs['path'] , kwargs['flag'], encoding=kwargs['encoding']) as file: if kwargs['handle'] == 'read': return file.read() elif kwargs['handle'] == 'write': return file.write(kwargs['data']) else: pass except IOError as error: #print(str(error)) return None #exit() def multi_replace(self, value, replacements) : for replacement in replacements : _from, _to = replacement value = value.replace(_from,_to) return value #!deprecated def readFile(self, path, encoding='utf8') : try: with open(path , 'r', encoding=encoding) as file: return file.read() except IOError as error: print(error) exit() def sleep(self): if self.request_sleep != False: sleep(self.request_sleep) def arr_first(self, arr): try: return arr[0] except ValueError: return '' def fetchFirst(self, arr, key) : if len(arr) > 0 and key in arr[0] : return arr[0][key] else: return False def hasAttr(self, attribute, _object) : return attribute in dir(_object) def call(self, *args, **kwargs) : """ Parameters ---------- `` args[0]`` str The name's funtion to be called. ``args[1:]`` parameter's function ``kwargs`` object params. ``is_required`` bool To determine if this function is required or not ``default`` None. Raises ------ ``Exception`` If args[0] not defined will raise Exception """ required = kwargs.get("is_required") if args : func = args[0] else: raise Exception("call() missing 1 required positional argument") if self.hasAttr(func, self ) : if 'is_required' in kwargs : del kwargs['is_required'] try: return getattr(self, func)(*args[1:], **kwargs) except : raise else : if required : raise Exception(f"name '{func}' is not defined, this function is required") else : return None def isFile(self, possibly_a_file) : return list(re.findall(r'^.*\.[^\\]+$', possibly_a_file)) def mountRouteParams(self, items:dict) : return "&".join(f"{key}={items[key]}" for key in items) #.format(param=key, value=items[key]) def jsonValueByPath(self, path ,json) : Json = JsonByPath(path=path, json=json) if (isinstance(Json.value, str)) : return Json.value.strip() else : return Json.value def mask(self , value , _type): if "date" in _type: return "{:%d/%m/%Y}".format(datetime.strptime(value,"%Y-%m-%d")) elif "currency" in _type : currency = '{:,.2f}'.format(float(value)) return currency.replace(',','v').replace('.',',').replace('v','.') def deepMerge(self ,d1, d2): def merge(dict1, dict2): for k in set(dict1.keys()).union(dict2.keys()): if k in dict1 and k in dict2: if isinstance(dict1[k], dict) and isinstance(dict2[k], dict): yield (k, dict(self.deepMerge(dict1[k], dict2[k]))) else: yield (k, dict2[k]) elif k in dict1: yield (k, dict1[k]) else: yield (k, dict2[k]) return dict(merge(d1,d2)) """ SELECT * FROM (SELECT REL1.* FROM TBRELACIONAID AS REL1, TBRELACIONAID AS REL2 WHERE REL1.CDRELACIONAID < REL2.CDRELACIONAID AND REL1.IDSOFTDIB = REL2.IDSOFTDIB) tmp WHERE NMTABELA ='TBPEDIDOSRETORNO'; use ebs_app_pedidos3; DROP PROCEDURE IF EXISTS sp_campos_extras; DELIMITER $$ CREATE PROCEDURE sp_campos_extras() BEGIN DECLARE finished INTEGER DEFAULT 0; DECLARE idtbaux int(11) ; DECLARE tbaux varchar(50) DEFAULT ""; DECLARE cdaux varchar(50) DEFAULT ""; DECLARE dsaux varchar(50) DEFAULT ""; DECLARE curCamposExtras CURSOR FOR SELECT IDTBCAMPOSEXTRAS, NMTABELA, NMCDCOLUNA, NMDSCOLUNA FROM TBCAMPOSEXTRAS; -- declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; OPEN curCamposExtras; getCamposExtras: LOOP FETCH curCamposExtras INTO idtbaux,tbaux,cdaux,dsaux; IF finished = 1 THEN LEAVE getCamposExtras; END IF; SELECT table_name INTO @teste FROM information_schema.tables WHERE table_schema = 'ebs_app_pedidos3' AND table_name = tbaux; IF @teste <> tbaux THEN LEAVE getCamposExtras; END IF; set @sql2 = CONCAT ("SELECT MD5( GROUP_CONCAT( CONCAT_WS('#',", dsaux,", ",cdaux,") SEPARATOR '##' ) ) into @md5 FROM ", tbaux ); PREPARE myquery FROM @sql2 ; EXECUTE myquery; DEALLOCATE PREPARE myquery; UPDATE TBCAMPOSEXTRAS SET VLHASHITENS = @md5 WHERE IDTBCAMPOSEXTRAS = idtbaux AND NMTABELA = tbaux; END LOOP getCamposExtras; CLOSE curCamposExtras; END $$ DELIMITER ;$$ """