view · edit · sidebar · attach · print · history

20110223-output-order-bbmb

<< Masa.20110224-export-order-xls-bbmb | 2011 | Masa.20110222-testcases-updater-oddb_org >>

  1. Review the task
  2. Test code
  3. Setup xmlconv database
Goal/Estimate
  • output order bbmb / 70%
Milestones
  1. Review
  2. Test code
  3. Make an independent job script or admin command
Summary
Commits

Review the task

Goal

  • Export order data of bbmb in a xls file

Emails from Davatz-san

In order to execute bin/admin in bbmb 

$#!/usr/bin/env ruby
$: << File.expand_path('../lib', File.dirname(__FILE__))

Run bin/bbmbd 

masa@masa ~/ywesee/bbmb $ bin/bbmbd config="config.yml"

(in order to login it locally, bin/yusd bin/yusd server_url="druby://localhost:12001")

Result 

masa@masa ~/ywesee/bbmb $ bin/admin 
ch.bbmb>

Note

  • bin/admin works

Memo

  • We can directly call the methods defined in lib/bbmb/util/server.rb from bin/admin console

Experiment

  • lib/bbmb/util/server.rb 
      def masa
        "masamasa"
      end

Result 

masa@masa ~/ywesee/bbmb $ bin/admin 
ch.bbmb> masa
-> masamasa

Test code

Experiment (output the number of orders)

  • lib/bbmb/util/server.rb 
      def export_all_order
        BBMB.persistence.all(Model::Order).length
      end

Result 

ch.bbmb> export_all_order
-> 18945

Note

  • It takes some seconds only to output the number

Necessary information for the xls file

  • Customer id (Kunden numer) -> (Kunden name)
  • Date (Bestelldatum)
  • Artikel numer -> Product name (Produkt)
  • Amount (Menge)
  • Price (Preis)
  • (Mat. Nr.)

Experiment (output all the order information)

  • lib/bbmb/util/server 
      def export_all_order
        t = Time.now
        commits = []
        count = 0
        open("/home/masa/work/test.dat", "w") do |f|
          BBMB.persistence.all(Model::Order).each_with_index do |order, i|
            if customer = order.customer && order.commit_time && order.commit_time >= Time.local(2010,1,1)
              order.each do |position|
                #commits << [order.commit_time, position.article_number, position.description, order.customer_id, customer.organisation]
                commits << [order.commit_time, position.article_number, position.description, '', '']
              end
              count += 1
            end
=begin
            if customer = order.customer
              commits << [order.commit_time, customer.customer_id, customer.organisation] 
            else
              commits << [order.commit_time, '', '']
            end
=end
            #count = i
            #p count if count%100==0
            break if count==10
          end
          #f.print commits.select{|x| x != nil}.sort.join("\n")
          new_commits = []
          commits.each do |x|
            temp = ""
            if date = x[0]
              temp << date.strftime("%Y%m%d") + "\t"
            else
              temp << "00000000\t"
            end
            temp << x[1].to_s + "\t" + x[2].to_s + "\t" + x[3].to_s + "\t" + x[4].to_s + "\n"
            new_commits << temp
          end
          f.print new_commits.sort.reverse
#          f.print commits.map{|x| x[0].strftime("%Y%m%d") + ", " + x[1].to_s + ", " + x[2].to_s}.join("\n"), "\n"
        end
        return (Time.now.to_f - t.to_f).to_i.to_s + " " + count.to_s
=begin
          BBMB.persistence.all(Model::Order).select do |order|

          end
        end
=end
      end
      alias :ex :export_all_order

Result 

20101202  44019215  LADONNA ...
20101202  44018300  Losartan ...
20101202  44014764  FELODIPIN ...
...

Note

  • Looks good

suspend

Setup xmlconv database

Goal

  • Setup xmlconv database and access the data as well as bbmb

Procedure

  1. Download the sql arcihive from the server
  2. Create the database: sudo -u postgres createdb -E UTF8 -T template0 xxx_xmlconv
  3. Restore the data: zcat postgresql_database-xxx_xmlconv-backup-20110223.gz |psql -U postgres xxx_xmlconv
  4. Modifiy bin/xmlconvd a little bit 
 $: << File.expand_path("../lib", File.dirname(__FILE__))
  1. Make xmlconv.yml and put it in etc directory 
 --- 
 domain: ch.bbmb.xmlconv.masa
 log_level: DEBUG
 db_name: xxx_xmlconv
 db_user: xxx
 default_filename: "s_%s.txt"
 server_url: "druby://localhost:12006"
  1. Start 
 masa@masa ~/ywesee/xmlconv $ bin/xmlconvd config="etc/xmlconv.yml"

Result 

...
I, [2011-02-23T11:27:18.881560 #6376]  INFO -- XmlConv2: drb-service listening on druby://localhost:12006

Note

  • It looks working

Experiment (test bin/admin command) 

masa@masa ~/ywesee/xmlconv $ bin/admin 
/usr/lib64/ruby/site_ruby/1.8/rclconf/rclconf.rb:42:in `method_missing': undefined method `domain' for #<RCLConf::RCLConf:0x7f77cf495888> (NoMethodError)
        from /usr/lib64/ruby/site_ruby/1.8/rclconf/rclconf.rb:42:in `fetch'
        from /usr/lib64/ruby/site_ruby/1.8/rclconf/rclconf.rb:42:in `method_missing'
        from /usr/lib64/ruby/site_ruby/1.8/rclconf/rclconf.rb:41:in `fetch'
        from /usr/lib64/ruby/site_ruby/1.8/rclconf/rclconf.rb:41:in `method_missing'
        from /usr/lib64/ruby/site_ruby/1.8/rclconf/rclconf.rb:40:in `fetch'
        from /usr/lib64/ruby/site_ruby/1.8/rclconf/rclconf.rb:40:in `method_missing'
        from /usr/lib64/ruby/site_ruby/1.8/rclconf/rclconf.rb:39:in `fetch'
        from /usr/lib64/ruby/site_ruby/1.8/rclconf/rclconf.rb:39:in `method_missing'
        from bin/admin:23

Update

  • lib/xmlconv/config.rb 
  defaults = {
    'domain'              => 'ch.xmlconv',
...

Restart xmlconvd 

masa@masa ~/ywesee/xmlconv $ bin/xmlconvd config="etc/xmlconv.yml"

Run bin/admin 

masa@masa ~/ywesee/xmlconv $ bin/admin
ch.xmlconv>

Note

  • Works

Experiment (add a new command to bin/admin)

  • lib/xmlconv/util/application.rb 
  def masa
    'masamasa'
  end

Restart xmlconvd

Result 

masa@masa ~/ywesee/xmlconv $ bin/admin server_url="druby://localhost:12006"
ch.xmlconv> masa
-> masamasa

Note

  • It works

Experiment

  • lib/xmlconv/util/application.rb 
  def masa
    open("/home/masa/work/test.dat", "w") do |f|
      self.transactions.reverse.each do |t|
        if t.commit_time >= Time.local(2010,1,1)
        f.print t.output
        end
      end
    end
  end

xmlconv.sort.rb 

File.readlines('test.dat').collect{|x|
  ret = ""
  if x =~ /(,\d+,)(\d\d)(\d\d)(2010)(,.+,)$/\
    or x =~ /(,\d+,)(\d\d)(\d\d)(2011)(,.+,)$/
    ret = $1 + $4 + $3 + $2 + $5
  end
  ret + "\n"
}.sort.each do |line|
  print line
end

add_kunden_artikel.rb 

kunden1 = {}
kunden2 = {}
File.readlines('txt2xls/backup/Kunden.txt').each do |line|
  x = line.split(/\t/)
#  print x[0], ",", x[1], ",", x[6], "\n"
  kunden1[x[0].to_i] = x[6]
  kunden2[x[0].to_i] = x[6]
end

artikel1 = {}
artikel2 = {}
File.readlines('txt2xls/backup/Artikel.txt').each do |line|
  x = line.split(/\t/)
  #print x[0], ",", x[3], ",", x[2], "\n"
  artikel1[x[4].to_i] = x[2]
  artikel2[x[3].to_i] = x[2]
end

File.readlines('xmlconv.sorted.dat').each do |line|
  x = line.split(/,/)
  kunden = ""
  if kunden1.keys.include?(x[1].to_i)
    kunden = kunden1[x[1].to_i]
  else kunden2.keys.include?(x[1].to_i)
    kunden = kunden2[x[1].to_i]
  end
  artikel = ""
  if artikel1.keys.include?(x[4].to_i)
    artikel = artikel1[x[4].to_i]
  else artikel2.keys.include?(x[5].to_i)
    artikel = artikel2[x[5].to_i]
  end
  x.map!{|z| z || ""}
  artikel ||=""
  kunden ||=""
  print "," + x[1] + "," + kunden + "," +\
      x[2] + "," + x[3] + "," + x[4] + "," + artikel + "," + \
      "," + x[5] + "," + x[6] + "," + x[7] + "," + x[8] +\
      "," + x[9] + "," + x[10]
end
view · edit · sidebar · attach · print · history
Page last modified on May 08, 2012, at 06:55 PM