Class: Abank::Big

Inherits:
Object
  • Object
show all
Defined in:
lib/abank/big.rb

Direct Known Subclasses

Folha

Constant Summary collapse

DF = 
'%Y-%m-%d'

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(opcoes = {}) ⇒ Hash

acesso a base dados abank no bigquery

Parameters:

  • opcoes (Thor::CoreExt::HashWithIndifferentAccess) (defaults to: {}) —

    trabalho

Options Hash (opcoes):

  • :k (String) — default: '' —

    movimentos a apagar (keysin.mv)

  • :n (Integer) — default: 0 —

    conta apagar movimentos >3 outras (mv)

  • :c (String) — default: '' —

    id contrato arrendamento (re)

  • :d (String) — default: '' —

    data inicio contrato arrendamento (re)

  • :t (Boolean) — default: false —

    trabalha todas as rendas? (re)



25
26
27
 
# File 'lib/abank/big.rb', line 25

def initialize(opcoes = {})
  @opcao = opcoes
end

Instance Attribute Details

#bqnrs ⇒ Google::Cloud::Bigquery::QueryJob, ... (readonly)

Returns:

  • (Google::Cloud::Bigquery::QueryJob) —

    job bigquery

  • (Hash) —

    opcoes trabalho

  • (Google::Cloud::Bigquery::Data) —

    resultado do Structured Query Language (SQL) no bigquery

  • (Integer) —

    numero linhas afetadas pelo Data Manipulation Language (DML) no bigquery

  • (Integer) —

    contrato arrendamento em tratamento

  • (Integer) —

    movimento do contrato arrendamento em tratamento



15
16
17
 
# File 'lib/abank/big.rb', line 15

def bqnrs
  @bqnrs
end

#bqres ⇒ Google::Cloud::Bigquery::QueryJob, ... (readonly)

Returns:

  • (Google::Cloud::Bigquery::QueryJob) —

    job bigquery

  • (Hash) —

    opcoes trabalho

  • (Google::Cloud::Bigquery::Data) —

    resultado do Structured Query Language (SQL) no bigquery

  • (Integer) —

    numero linhas afetadas pelo Data Manipulation Language (DML) no bigquery

  • (Integer) —

    contrato arrendamento em tratamento

  • (Integer) —

    movimento do contrato arrendamento em tratamento



15
16
17
 
# File 'lib/abank/big.rb', line 15

def bqres
  @bqres
end

#ctpos ⇒ Google::Cloud::Bigquery::QueryJob, ... (readonly)

Returns:

  • (Google::Cloud::Bigquery::QueryJob) —

    job bigquery

  • (Hash) —

    opcoes trabalho

  • (Google::Cloud::Bigquery::Data) —

    resultado do Structured Query Language (SQL) no bigquery

  • (Integer) —

    numero linhas afetadas pelo Data Manipulation Language (DML) no bigquery

  • (Integer) —

    contrato arrendamento em tratamento

  • (Integer) —

    movimento do contrato arrendamento em tratamento



15
16
17
 
# File 'lib/abank/big.rb', line 15

def ctpos
  @ctpos
end

#job ⇒ Google::Cloud::Bigquery::QueryJob, ... (readonly)

Returns:

  • (Google::Cloud::Bigquery::QueryJob) —

    job bigquery

  • (Hash) —

    opcoes trabalho

  • (Google::Cloud::Bigquery::Data) —

    resultado do Structured Query Language (SQL) no bigquery

  • (Integer) —

    numero linhas afetadas pelo Data Manipulation Language (DML) no bigquery

  • (Integer) —

    contrato arrendamento em tratamento

  • (Integer) —

    movimento do contrato arrendamento em tratamento



15
16
17
 
# File 'lib/abank/big.rb', line 15

def job
  @job
end

#mvpos ⇒ Google::Cloud::Bigquery::QueryJob, ... (readonly)

Returns:

  • (Google::Cloud::Bigquery::QueryJob) —

    job bigquery

  • (Hash) —

    opcoes trabalho

  • (Google::Cloud::Bigquery::Data) —

    resultado do Structured Query Language (SQL) no bigquery

  • (Integer) —

    numero linhas afetadas pelo Data Manipulation Language (DML) no bigquery

  • (Integer) —

    contrato arrendamento em tratamento

  • (Integer) —

    movimento do contrato arrendamento em tratamento



15
16
17
 
# File 'lib/abank/big.rb', line 15

def mvpos
  @mvpos
end

#opcao ⇒ Google::Cloud::Bigquery::QueryJob, ... (readonly)

Returns:

  • (Google::Cloud::Bigquery::QueryJob) —

    job bigquery

  • (Hash) —

    opcoes trabalho

  • (Google::Cloud::Bigquery::Data) —

    resultado do Structured Query Language (SQL) no bigquery

  • (Integer) —

    numero linhas afetadas pelo Data Manipulation Language (DML) no bigquery

  • (Integer) —

    contrato arrendamento em tratamento

  • (Integer) —

    movimento do contrato arrendamento em tratamento



15
16
17
 
# File 'lib/abank/big.rb', line 15

def opcao
  @opcao
end

Instance Method Details

#cc_apaga ⇒ Big

apaga classificador

Returns:

  • (Big) —

    acesso a base dados abank no bigquery



113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
 
# File 'lib/abank/big.rb', line 113

def cc_apaga
  cc_show
  return self if bqres.empty?

  if opcao[:p1].empty? && opcao[:p2].empty?
    dml("delete from #{BD}.cc WHERE ct=@ct", ct: opcao[:c])
  elsif opcao[:p1].empty?
    dml("delete from #{BD}.cc WHERE ct=@ct and p2 IN UNNEST(@p2s)", ct: opcao[:c], p2s: opcao[:p2])
  else
    dml("delete from #{BD}.cc WHERE ct=@ct and p1 IN UNNEST(@p1s)", ct: opcao[:c], p1s: opcao[:p1])
  end
  puts("CLASSIFICADORES APAGADOS #{bqnrs}")
  return self unless opcao[:t]

  dml("update #{BD}.mv set ct=null,p1=null WHERE ct=@ct", ct: opcao[:c])
  puts("MOVIMENTOS DES-CLASSIFICADOS #{bqnrs}")
  self
end

#cc_cria ⇒ Big

cria classificador

Returns:

  • (Big) —

    acesso a base dados abank no bigquery



134
135
136
137
138
139
 
# File 'lib/abank/big.rb', line 134

def cc_cria
  vls = opcao[:p1].map.with_index { |p1, i| "('#{opcao[:c]}',#{fpli(p1)},#{fpli(opcao[:p2][i])},#{fpli(opcao[:t1][i])})" }
  dml("insert #{BD}.cc values#{vls.join(',')}")
  puts("CLASSIFICADORES CRIADOS #{bqnrs}")
  self
end

#cc_show ⇒ Object

mostra classificador



84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
 
# File 'lib/abank/big.rb', line 84

def cc_show
  if opcao[:c].to_s.empty? && opcao[:p1].empty? && opcao[:p2].empty?
    sql("SELECT * FROM #{BD}.cc")
  elsif opcao[:c].to_s.empty? && opcao[:p1].empty?
    sql("select * from #{BD}.cc WHERE p2 in unnest(@p2s)", p2s: opcao[:p2])
  elsif opcao[:c].to_s.empty? && opcao[:p2].empty?
    sql("select * from #{BD}.cc WHERE p1 in unnest(@p1s)", p1s: opcao[:p1])
  elsif opcao[:p1].empty? && opcao[:p2].empty?
    sql("select * from #{BD}.cc WHERE ct=@ct", ct: opcao[:c])
  elsif opcao[:p1].empty?
    sql("select * from #{BD}.cc WHERE ct=@ct and p2 in unnest(@p2s)", ct: opcao[:c], p2s: opcao[:p2])
  elsif opcao[:p2].empty?
    sql("select * from #{BD}.cc WHERE ct=@ct and p1 in unnest(@p1s)", ct: opcao[:c], p1s: opcao[:p1])
  else
    sql("select * from #{BD}.cc WHERE ct=@ct and p1 in unnest(@p1s) and p2 in unnest(@p2s)", ct: opcao[:c], p1s: opcao[:p1], p2s: opcao[:p2])
  end
  if bqres.empty?
    puts('CLASSIFICADOR NAO EXISTE')
  else
    @bqres = bqres.group_by { |c| c[:ct] }.map { |k, v| {ct: k, p1s: fplo(v)} }
    puts('        id palavras p1s/t1s/p2s ')
    bqres.sort_by { |i| i[:ct] }.each do |l|
      puts(format('%<ct>8s p %<p1>s', ct: l[:ct], p1: fpls(l[:p1s])))
    end
  end
end

#ct_apaga ⇒ Object 



190
191
192
193
 
# File 'lib/abank/big.rb', line 190

def ct_apaga
  @ctlct = [{ct: opcao[:c]}]
  re_delete_dml
end

#ct_cria ⇒ Big

cria contrato arrendamento no bigquery

Returns:

  • (Big) —

    acesso a base dados abank no bigquery



170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
 
# File 'lib/abank/big.rb', line 170

def ct_cria
  if sql("SELECT ct FROM #{BD}.ca WHERE ct=@ct", ct: opcao[:c]).empty?
    if valid_dc?
      dml("insert #{BD}.re select @ct,EXTRACT(YEAR FROM DATE(@dc)),0,DATE(@dc),0", ct: opcao[:c], dc: opcao[:d])
    else
      dml("insert #{BD}.re select ct,EXTRACT(YEAR FROM DATE_TRUNC(dl,MONTH)),0,DATE_TRUNC(dl,MONTH),0 from #{BD}.mv where ct=@ct order by dl limit 1", ct: opcao[:c])
    end
    puts("CONTRATO #{opcao[:c]} #{bqnrs.zero? ? 'NAO ' : ''}INSERIDO")
  else
    @bqnrs = 1
    puts('CONTRATO JA EXISTE')
  end
  @ctlct = [{ct: opcao[:c]}]
  return unless bqnrs.positive? && opcao[:t]

  # processa rendas associadas ao contrato arrendamento
  ct_dados.re_insert
end

#ct_dados ⇒ Big

optem lista dados contrato arrendamento (inclui lista movimentos novos)

Returns:

  • (Big) —

    acesso a base dados abank no bigquery



197
198
199
200
201
202
203
204
205
 
# File 'lib/abank/big.rb', line 197

def ct_dados
  ctlct.map! do |ctr|
    opcao[:c] = ctr[:ct]
    lre = sql("select * from #{BD}.glr where ct=@ct order by ano desc,cnt desc limit 1", ct: opcao[:c]).first
    lre[:dl] -= 1 if lre[:cnt].zero?
    ctr.merge(lre, mv: sql("select * from #{BD}.gmr where ct=@ct and dv>=@ud order by 1,2", ct: opcao[:c], ud: lre[:dl] + 1))
  end
  self
end

#ct_mostra ⇒ Object

mostra contrato arrendamento



151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
 
# File 'lib/abank/big.rb', line 151

def ct_mostra
  sql("SELECT * FROM #{BD}.ca WHERE ct=@ct", ct: opcao[:c])
  if bqres.empty?
    puts('CONTRATO NAO EXISTE')
    return
  end

  @bqres = bqres.first
  puts(' crontrato       data    renda')
  puts(format('%<ct>10s %<dc>10s %<vr>8.2f', ct: bqres[:ct], dc: bqres[:dc].strftime(DF), vr: bqres[:vr]))
  return unless opcao[:t]

  sql("SELECT * FROM #{BD}.gca WHERE ct=@ct order by ano desc,cnt desc", ct: opcao[:c])
  puts("\n      data  ano cnt dias")
  bqres.each { |l| puts(format('%<dl>10s %<a>4d %<c>3d %<d>4d', dl: l[:dl].strftime(DF), a: l[:ano], c: l[:cnt], d: l[:dias])) }
end

#mc_show ⇒ Object

mostra dados movimentos classificacao



59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/abank/big.rb', line 59

def mc_show
  if opcao[:c].to_s.empty?
    puts("\ndados movimentos NAO classificados")
    cp1 = sql("select distinct p1 from #{BD}.cc where p1 is not null").map { |p| p[:p1] }
    cp2 = sql("select distinct p2 from #{BD}.cc where p2 is not null").map { |p| p[:p2] }
    sql("select * from #{BD}.gpl where ct is null order by 1 desc limit @lm", lm: opcao[:n])
  else
    puts("\ndados movimentos JA classificados por:")
    cc_show
    cp1 = []
    cp2 = []
    sql("select * from #{BD}.gpl where ct=@ct order by 1 desc limit @lm", lm: opcao[:n], ct: opcao[:c])
  end
  bqres.reject! do |h|
    h[:p1s] = h[:p1s] - cp1
    h[:p2s] = h[:p2s] - cp2 - h[:p1s] - cp1
    h[:p1s].empty? && h[:p2s].empty?
  end
  return if bqres.empty?

  puts('      data         t1    valor palavras p1s/p2s')
  bqres.each { |l| puts(format('%<dl>10s %<t1>10s %<vl>8.2f %<p1>s%<p2>s', dl: l[:dl].strftime(DF), t1: l[:p1], vl: l[:vl], p1: fpls(l[:p1s], 'p1 '), p2: fpls(l[:p2s], '; p2 '))) }
end

#mv_classifica ⇒ Big

classifica movimentos no bigquery

Returns:

  • (Big) —

    acesso a base dados abank no bigquery



143
144
145
146
147
148
 
# File 'lib/abank/big.rb', line 143

def mv_classifica
  @ctlct = sql("select * from #{BD}.gnr")
  stp("call #{BD}.uct()")
  puts("MOVIMENTOS CLASSIFICADOS #{bqnrs}")
  self
end

#mv_delete ⇒ Big

apaga movimentos & suas rendas associadas no bigquery

Returns:

  • (Big) —

    acesso a base dados abank no bigquery



42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
# File 'lib/abank/big.rb', line 42

def mv_delete
  @ctlct = []
  return self if mvkys.empty? && contadel.zero?

  # obtem lista contratos arrendamento associados aos movimentos a apagar
  @ctlct =
    if contadel.zero?
      sql("select distinct ct from #{BD}.gmr where ky IN UNNEST(@kys)", kys: mvkys)
    else
      sql("select distinct ct from #{BD}.gmr where nc=@nc", nc: contadel)
    end
  re_apaga
  mv_delete_dml
  self
end

#mv_insert ⇒ Big

insere & classifica movimentos no bigquery

Returns:

  • (Big) —

    acesso a base dados abank no bigquery



31
32
33
34
35
36
37
38
 
# File 'lib/abank/big.rb', line 31

def mv_insert
  return self if mvvls.empty?

  dml("insert #{BD}.mv VALUES#{mvvls.join(',')}")
  puts("MOVIMENTOS INSERIDOS #{bqnrs}")
  mv_classifica if bqnrs.positive?
  self
end

#re_atualiza ⇒ String

(see CLI#recriare)

Returns:

  • (String) —

    sql inicio contrato arrendamento sem movimentos



221
222
223
224
225
 
# File 'lib/abank/big.rb', line 221

def re_atualiza
  # [re]cria rendas novas/todas dos contratos ativos
  @ctlct = sql("select ct from #{BD}.ca")
  re_apaga.ct_dados.re_insert
end

#re_insert ⇒ Object

insere rendas associadas a lista contratos arrendamento no bigquery



208
209
210
211
212
213
214
215
216
217
 
# File 'lib/abank/big.rb', line 208

def re_insert
  @ctpos = 0
  vls = ct_rendas.join(',')
  if vls.empty?
    puts('NAO EXISTEM RENDAS NOVAS')
  else
    dml("insert #{BD}.re VALUES#{vls}")
    puts("RENDAS #{cta.join(',')} CRIADAS #{bqnrs}")
  end
end