lib/eost/bigquery.rb in eost-0.1.3 vs lib/eost/bigquery.rb in eost-0.1.4

@@ -3,10 +3,11 @@
 require 'roo'
 require 'google/cloud/bigquery'
 
 module Eost
   DF = '%Y-%m-%d'
+  DI = '%Y-%m-%d %H:%M:%S'
 
   # folhas calculo comuns no bigquery
   class Bigquery
     # @return [Roo::Excelx] folha calculo a processar
     attr_reader :book
@@ -16,25 +17,20 @@
     attr_reader :api
     # @return [Google::Cloud::Bigquery::QueryJob] job bigquery
     attr_reader :job
     # @return (see sql_select)
     attr_reader :sql
-    # @return [Integer] numero conta
-    attr_reader :num
-    # @return [Hash<String, Boolean>] opcoes apagar linhas
+    # @return [Boolean] apaga linhas existentes sim/nao?
     attr_reader :apaga
 
     # permite processa folhas calculo comuns no bigquery
     #
     # @param [String] csv folha calculo para processar
-    # @param [Hash<String, Boolean>] apaga opcoes apagar linhas
-    # @option apaga [Boolean] s apaga linhas similares sim/nao?
-    # @option apaga [Boolean] e apaga linhas existentes sim/nao?
+    # @param [Boolean] apaga linhas existentes sim/nao?
     # @return [Bigquery] acesso folha calculo & bigquery
-    def initialize(csv = '', apaga = {})
+    def initialize(csv = '', apaga = false)
       @book = Roo::CSV.new(csv) if csv.size.positive?
-      @num = csv.match?(/card/i) ? 2 : 1
       @apaga = apaga
       # usa env GOOGLE_APPLICATION_CREDENTIALS para obter credentials
       # @see https://cloud.google.com/bigquery/docs/authentication/getting-started
       @api = Google::Cloud::Bigquery.new
     end
@@ -66,37 +62,32 @@
       @sql = job_bigquery?('select * ' + sql_where) ? [{}, {}] : job.data
     end
 
     # @return [String] parte sql para processamento linhas similares
     def sql_where
-      "from ab.mv where nc=#{num}" \
-      " and dl='#{row[0].strftime(DF)}'" \
-      " and vl=#{row[3]}"
+      "from coins.eos where blocknumber=#{row[0]}"
     end
 
-    # classifica linhas
-    def sql_update
-      puts 'LINHAS CLASSIFICADAS ' +
-           dml('update ab.mv set mv.ct=tt.nct' \
-               ' from (select * from ab.cl) as tt ' \
-               'where mv.dl=tt.dl and mv.dv=tt.dv' \
-               ' and mv.ds=tt.ds and mv.vl=tt.vl').to_s
-    end
-
     # @return [Integer] numero linhas inseridas
     def sql_insert
-      dml('insert ab.mv(dl,dv,ds,vl,nc,ano,mes,ct,tp) VALUES(' \
-        "'#{row[0].strftime(DF)}','#{row[1].strftime(DF)}','#{row[2]}'," \
-        "#{row[3]},#{num}" + sql_insert_calculado)
+      dml('insert coins.eos(blocknumber,time,contract,' \
+          'action,acfrom,acto,amount,symbol,memo,data,dias) VALUES(' +
+      sql_insert1 + sql_insert2)
     end
 
-    # @return [String] campos calculados da linha bigquery
-    def sql_insert_calculado
-      ",#{row[1].year},#{row[1].month},null,'#{row[3].positive? ? 'c' : 'd'}')"
+    # @return [String] campos insert da linha bigquery
+    def sql_insert1
+      "#{row[0]},'#{DateTime.parse(row[1]).strftime(DI)}','#{row[2]}',"
     end
 
+    # @return [String] campos insert da linha bigquery
+    def sql_insert2
+      "'#{row[3]}','#{row[4]}','#{row[5]}',#{row[6]}," \
+      "'#{row[7]}','#{row[8]}','#{row[9]}',0)"
+    end
+
     # @return [Integer] numero linhas apagadas
     def sql_delete
-      dml('delete ' + sql_where + " and ds='#{sql.first[:ds].strip}'")
+      dml('delete ' + sql_where)
     end
   end
 end