Author Archives: sidroniolima

Cross Apply no Sql Server

Precisava criar um relatório que listasse a média das 3 primeiras vendas de clientes novos do ano atual (2018).

A query então deve conter 2 tabelas: cliente e pedidos. O filtro do cliente e dos pedidos é básico. O difícil é fazer com que os pedidos recebam o código do cliente do Select e aplicá-lo na query que traz os pedidos.

A princípio tentei fazer um Join, depois uma subquery mas por conta da operador TOP não conseguia. Logicamente o TOP 3 retornaria apenas três registros da tabela pedido e não três pedidos da tabela pedido para cada cliente da query principal.

Pesquisando um pouco eu cheguei a uma query com dois joins que não deu muito certo, mas a lógica até que é boa:

SELECT
A1_COD,
Z7.Z7_CONSULT,
Z7.Z7_NUM,
Z7.Z7_DTFECHA,
B.*
FROM
SA1010 A1
LEFT JOIN (SELECT TOP 3 Z7_NUM, Z7_CONSULT FROM SZ7010 WHERE D_E_L_E_T_ = '' AND Z7_STATUS = 'F' ORDER BY Z7_DTFECHA) B ON A1.A1_COD = B.Z7_CONSULT
LEFT JOIN SZ7010 Z7 ON Z7.Z7_NUM = B.Z7_NUM
WHERE
A1.D_E_L_E_T_ = '' AND
A1.A1_DTCAD > 20180101 AND
A1.A1_STATUS = 1
ORDER BY
A1.A1_COD

Porém não funcionou. : (

A solução foi a utilizar o operador CROSS APPLY, que utiliza uma query como entrada para outra e as uni. Analisando o problema das novatas em questão fica fácil entender o funcionamento. Veja a query:

SELECT A1_COD, A1_NOME, A1_DTCAD, CA.* FROM SA1010 A1 CROSS APPLY ( SELECT TOP 3 Z7_NUM, Z7_CONSULT, Z7_QTDCOMD, Z7_REGIAO, Z7_DTFECHA FROM SZ7010 B WHERE B.D_E_L_E_T_ = '' AND B.Z7_STATUS = 'F' AND B.Z7_SERIE <> 'DIR' AND A1.A1_COD = B.Z7_CONSULT ORDER BY Z7_DTFECHA) CA WHERE A1.D_E_L_E_T_ = '' AND A1.A1_DTCAD > 20180101 AND A1.A1_STATUS = 1 ORDER BY A1.A1_COD

A query então traz os 3 primeiros pedidos para cada cliente retornado na query inicial e junta os resultados.

109244 <<NOME>> 20180102 842355 109244 23 000122 20180203
109244 <<NOME>> 20180102 844817 109244 45 000122 20180312
109244 <<NOME>> 20180102 862112 109244 54 000122 20180419
109255 <<NOME>> 20180102 839520 109255 17 000072 20180204
109255 <<NOME>> 20180102 854113 109255 28 000072 20180313
109255 <<NOME>> 20180102 861392 109255 42 000072 20180417
109260 <<NOME>> 20180105 838082 109260 25 000093 20180205
109260 <<NOME>> 20180105 851091 109260 41 000093 20180314
109260 <<NOME>> 20180105 863113 109260 41 000093 20180418

Depois disso apenas alterei a query para que ao invés dos três pedidos retornasse a média de peças vendidas e pronto. Relatório concluido com performance e acuracidade.

Seguem dois links que tratam do operador Cross Apply que me ajudaram:

https://www.tutorialti.com/2015/02/o-que-e-cross-apply-sql-server.html

http://blog.dbaacademy.com.br/sql-server-cross-apply-e-cross-join/

Espero ter ajudado e até!

Credit Card Metadata and Validator

Hi!

There’s a service that validates the credit card number and returns the metadata.

The requisition is via http and returns a json. Just like this:

curl -H "Accept-Version: 3" "https://lookup.binlist.net/45717360"
 {
  "number": {
    "length": 16,
    "luhn": true
  },
  "scheme": "visa",
  "type": "debit",
  "brand": "Visa/Dankort",
  "prepaid": false,
  "country": {
    "numeric": "208",
    "alpha2": "DK",
    "name": "Denmark",
    "emoji": "🇩🇰",
    "currency": "DKK",
    "latitude": 56,
    "longitude": 10
  },
  "bank": {
    "name": "Jyske Bank",
    "url": "www.jyskebank.dk",
    "phone": "+4589893300",
    "city": "Hjørring"
  }
}

The link: https://binlist.net/

But if you want regex for the most common credit card brands, check below.

const elo = /((((636368)|(438935)|(504175)|(451416)|(636297)))[0-9]{4}$)|(((5067)|(4576)|(4011))[0-9]{8}$)$/;

const visa = /^4[0-9]{12}(?:[0-9]{3})$/;

const master = /^5[1-5][0-9]{14}$/;

const amex = /^3[47][0-9]{13}$/;

const diners = /^3(?:0[0-5]|[68][0-9])[0-9]{11}$/;

const discover = /^6(?:011|5[0-9]{2})[0-9]{12}$/;

const jcb = /^(?:2131|1800|35\d{3})\d{11}$/;

That’s it!

Admin-lte height bug (fixed)

Admin-Lte template

Hop!

Desenvolvendo um Web App para a Divisão de Homicídios de Niterói São Gonçalo (DH-NSG) utilizando React e o template do Admin-lte vinha enfrentando um bug na visualização da página. O conteúdo era carregado apenas até a metade da página.

Depois de ter desistido por um tempo de corrigi-lo, descobri hoje um workaround em css que corrige este bug.

Segue:

.wrapper {

/*min-height: 100%;

position: relative;

overflow: hidden;*/

width: 100%;

min-height: 100%;

height: auto !important;

position: absolute;

}

Depois de tentar algumas snippets este funcionou e com isso espero ajudar. Até…

React Native keyboard over InputText

During creating a form in React Native I faced a issue with the android keyboard. It override the text field, so I could not see what is the enter of the field.

So I found the solution, actually, three. I adopted the first one, just changing the View by KeyboardAvoidingView component.

Here is the complete solution.

Very cool and useful.

Axios Network Error

Android error image.

Trying to figure out what the hell was going on with a axios get request, I discover by axios – npm documentation a better or more complete way tho handle with error.

This is how:

axios.get(‘/user/12345‘)
  .catch(function (error) {
    if (error.response) {
      // The request was made and the server responded with a status code
      // that falls out of the range of 2xx
      console.log(error.response.data);
      console.log(error.response.status);
      console.log(error.response.headers);
    } else if (error.request) {
      // The request was made but no response was received
      // `error.request` is an instance of XMLHttpRequest in the browser and an instance of
      // http.ClientRequest in node.js
      console.log(error.request);
    } else {
      // Something happened in setting up the request that triggered an Error
      console.log(‘Error‘, error.message);
    }
    console.log(error.config);
  });

If we log just the error, it will display just the Network Error message on log.

By the way the error was this one: “_response”: “java.security.cert.CertPathValidatorException: Trust anchor for certification path not found.”,

I was trying to access a https local server. I resolved by change the protocol of the req to http.

Hope I can help!

Codenvy react project

Start with a Node workspace.

Via terminal execute

sudo npm install -g create-react-app

After generate the project with

create-react-app <<project_name>>

To create a run command, do:

command line:

cd ${current.project.path} && npm start

preview:

http://${server.port.3000}

Quem é Telefone.ninja ?

Segue os dados da hospedagem do infame site que fornece informações pessoais publicamente pela internet:

Domain Name: telefone.ninja
Registry Domain ID: 4267933f0d484676b1c51fe7bec6fd2a-RSIDE
Registrar WHOIS Server: www.netim.com/domain/tools/whois.php
Registrar URL: http://www.netim.com
Updated Date: 2017-06-06T08:18:14Z
Creation Date: 2017-05-11T19:30:36Z
Registry Expiry Date: 2018-05-11T19:30:36Z
Registrar: NETIM sarl
Registrar IANA ID: 1519
Registrar Abuse Contact Email: tld@netim.com
Registrar Abuse Contact Phone:
Domain Status: clientTransferProhibited https://icann.org/eppclientTransferProhibited
Registry Registrant ID: c7c277e5830240ce8b00f0a836dc5b45-RSIDE
Registrant Name: ricardo torres
Registrant Organization: bytecode tech inc
Registrant Street: al rio negro 545
Registrant City: SAO PAULO
Registrant State/Province:
Registrant Postal Code: 06454001
Registrant Country: BR
Registrant Phone: 55.137458745
Registrant Phone Ext:
Registrant Fax:
Registrant Fax Ext:
Registrant Email: cnpjbrasilcontato@gmail.com
Registry Admin ID: 857f0565e0374fcf9bc81020c5a16303-RSIDE
Admin Name: ricardo torres
Admin Organization: bytecode tech inc
Admin Street: al rio negro 545
Admin City: SAO PAULO
Admin State/Province:
Admin Postal Code: 06454001
Admin Country: BR
Admin Phone: 55.937458745
Admin Phone Ext:
Admin Fax:
Admin Fax Ext:
Admin Email: cnpjbrasilcontato@gmail.com
Registry Tech ID: bf07e26654264ccc923f72fed7109b43-RSIDE
Tech Name: ricardo torres
Tech Organization: bytecode tech inc
Tech Street: al rio negro 545
Tech City: SAO PAULO
Tech State/Province:
Tech Postal Code: 06454001
Tech Country: BR
Tech Phone: 55.937458745
Tech Phone Ext:
Tech Fax:
Tech Fax Ext:
Tech Email: cnpjbrasilcontato@gmail.com
Name Server: aida.ns.cloudflare.com
Name Server: curt.ns.cloudflare.com
DNSSEC: unsigned
URL of the ICANN Whois Inaccuracy Complaint Form: https://www.icann.org/wicf/
Last update of WHOIS database: 2017-06-08T14:59:16Z

Java @Schedule error

I was trying to scheduling a method from a EJB class using the @Schedule annotation of the javax.ejb package.

@Schedule(second=”*/5″, minute=”*”, hour=”*”)
public void listarMsgs()
{
logger.log(Level.INFO, “do something…”);
}

But every time the method was called it appeared a error like this:

Failed to reinstate timer…

Googling for about some time I figure out that it was a kind bug of the application server, JBoss Wildfly. I’m using the 9 version.

The solution was clear the folder under the standalone/data/timer-service-data that is where the server creates the xml of the schedule job.

Java JSON-B

Comming soon...

A post introducing the JSON-B Java API that’s going to be part of Java EE 8, which is expected to ship toward the end of 2017.

The post is at Java Indeed blog.

To see more of the forthcoming Java EE 8, which will be followed by Java EE 9, subscribe for the current issue of the Oracle Java Magazie, here.

And that’s all…

Stream intermediate operations

There are a few operations that processes a stream and output stream as source for terminal operations. Above, we can see some of these methods, yours descriptions and examples. Just short, just directly.

Continue reading →