#!ruby -Ks

        ##
        ##  DBアクセススクリプト
        ##

require "phi"
require "rdb"
require "RGUI"
include RDB
        ##
        ##  Utils   from tkUtils.rb
        ##

class Array
  def sum
    result = 0
    if defined? yield
      each{|i| result += yield(i) }
    else
      each{|i| result += i }
    end
    return result
  end
end


        ##
        ##  DateTimeを使う場合には必ず Phi.date_formatを指定しよう。
        ##
Phi.date_format = "yyyy/mm/dd"

        ##
        ##  同じセッションでは
        ##  同じ名前のデータベースを複数開くことは出来ないので、
        ##  ここで開いて、各フォームから参照する。
        ##
DB = Database_ib.new("IBLocal","SYSDBA","masterkey") unless defined? DB
##DB = Database_ib.new("APOLLO","apollo","apollo")

        ##
        ##
        ##
def insert_den( den_date, kokyaku_id )
  kokyaku_id = kokyaku_mei_to_id( kokyaku_id ) unless kokyaku_id.is_a? Integer
  den_id = DB.inc_counter( "ap01gen2" )
  DB.internal_query{|query|
    query.close
    query.sql.text = <<EOT
      INSERT INTO ap01den
             (  den_id ,  den_date,  kokyaku_id )
      VALUES ( :den_id , :den_date, :kokyaku_id )
  EOT
    query.params["den_id"] = den_id
    query.params["den_date"] = den_date
    query.params["kokyaku_id"] = kokyaku_id
    query.execute
  }
  return den_id
end
private :insert_den

def update_den( den_id , den_date, kokyaku_id )
  kokyaku_id = kokyaku_mei_to_id( kokyaku_id ) unless kokyaku_id.is_a? Integer
  DB.internal_query{|query|
    query.sql.text = <<EOT
      UPDATE ap01den
      SET den_id     = :den_id ,
          den_date   = :den_date ,
          kokyaku_id = :kokyaku_id
      WHERE den_id = :den_id
EOT
    query.params["den_id"]     = den_id
    query.params["den_date"]   = den_date
    query.params["kokyaku_id"] = kokyaku_id
    query.execute
  }
  return den_id
end
#private :update_den

def insert_meisai( den_id, meisai_id, shohin_id, suryo, tanka=nil )
  shohin_id = shohin_mei_to_id( shohin_id ) unless shohin_id.is_a? Integer
  tanka = shohin_id_to_tanka(shohin_id) unless tanka
  DB.execute <<EOT
    INSERT INTO ap01meisai
           (   den_id ,   meisai_id  ,   shohin_id ,   tanka  ,   suryo )
    VALUES ( #{den_id}, #{meisai_id} , #{shohin_id}, #{tanka} , #{suryo} )
EOT
  return meisai_id
end
private :insert_meisai

def insert_denpyo( den, meisai )
  DB.transaction{
    den_id = insert_den ( den[0] ,  den[1] )
    meisai.each_with_index{ |m,i| insert_meisai den_id, i, m[0], m[1], m[2] }
  }
  return den_id
end

def update_denpyo( den_id, den, meisai )
  DB.transaction{
    update_den ( den_id , den[0] ,  den[1] )
    DB.execute "DELETE FROM ap01meisai WHERE ( den_id = #{den_id} )"
    meisai.each_with_index{ |m,i| insert_meisai den_id, i, m[0], m[1], m[2] }
  }
  return den_id
end

def delete_denpyo( den_id )
  DB.transaction{
    DB.execute "DELETE FROM ap01den    WHERE ( den_id = #{den_id} )"
    DB.execute "DELETE FROM ap01meisai WHERE ( den_id = #{den_id} )"
  }
  return den_id
end

        ##
        ##  顧客名から顧客ＩＤを返す。
        ##  商品名から商品ＩＤを返す。
        ##
def kokyaku_mei_to_id( kokyaku_mei )
  DB.lookup "kokyaku_id", "ap01kokyaku", "kokyaku_mei = '#{kokyaku_mei}'"
end

def shohin_mei_to_id( shohin_mei )
  DB.lookup "shohin_id", "ap01shohin", "shohin_mei = '#{shohin_mei}'"
end

def shohin_id_to_tanka( shohin_id )
  DB.lookup "tanka" , "ap01shohin" , "shohin_id = #{shohin_id}"
end

def den_id_to_date( den_id )
  DB.lookup( "den_date","ap01den","den_id=#{den_id}" )
end

def den_id_to_who( den_id )
  DB.lookup "kokyaku_mei","ap01den_mei","den_id=#{den_id}"
end
  
def den_id_to_goukei( den_id )
  DB.lookup "sum(shokei)","ap01meisai_mei","den_id = #{den_id}"
end

