require "rubygems"
# -*- coding: utf-8 -*-

require 'pg'
require 'open-uri'
#require 'mongo'
require 'fastercsv'
require '/var/www/lib/alertError.rb'

class UserPostgres
    def createCommDB host,dbname
	if host!=nil then
            tempconn = PG.connect(:host =>"#{host}.mobilous.com", :port =>"5432", :dbname => "mobilouscomm", :user =>"mobilous", :password => "ctcpy9823\"x~")
	else
            tempconn = PG.connect(:host =>"localhost", :port =>"5432", :dbname => "mobilouscomm", :user =>"mobilous", :password => "ctcpy9823\"x~")
	end
        sql = "create database mobilouscomm"
        tempconn.exec(sql)
    end

    def createDB host,dbname
        begin
	    if host!=nil and host!='localhost' then
            	tempconn = PG.connect(:host =>"#{host}.mobilous.com", :port =>"5432", :dbname => "mobilouscomm", :user =>"mobilous", :password => "ctcpy9823\"x~")
	    else
            	tempconn = PG.connect(:host =>"localhost", :port =>"5432", :dbname => "mobilouscomm", :user =>"mobilous", :password => "ctcpy9823\"x~")
	    end
            sql = "create database #{dbname}"
            tempconn.exec(sql)
        rescue
            puts "The database #{dbname} is not existing."
	     Alert::sendAlertEmail( "userpostgres.rb","createDB","#{host},#{dbname}","#{e}:#{host},#{dbname}","Akira" )
        end
    end

    def dropDB host,projectid
        begin
            dbname = "db_#{projectid}"
	    if host!=nil then
            	tempconn = PG.connect(:host =>"#{host}.mobilous.com", :port =>"5432", :dbname => "mobilouscomm", :user =>"mobilous", :password => "ctcpy9823\"x~")
	    else
            	tempconn = PG.connect(:host =>"localhost", :port =>"5432", :dbname => "mobilouscomm", :user =>"mobilous", :password => "ctcpy9823\"x~")
	    end
            sql = "drop database #{dbname}"
            puts "dropDB : #{sql}"
            tempconn.exec(sql)
        rescue
            puts "The database #{dbname} is not existing."
	     Alert::sendAlertEmail( "userpostgres.rb","dropDB","#{host},#{projectid}","#{e}:#{host},#{projectid}","Akira" )
        end
    end

    def connectPg host,username,projectid,dbname,psql_port=nil,console=nil

	@debug_base = false	# エラーで一時停止する。
	@debug_log = false

        # dbname : "mobilouscomm" or "db_(projectno)"
        @dbname =dbname
        @username = username
        @projectid = projectid
	if( psql_port==nil )then
	    port = "5432"
	else
	    port = psql_port
	end

	if( host!=nil )then
	    host.gsub!(".mobilous.com","")
	end

	if( console==nil ) then
	    #@password = "ctcpy9823\"x~"
	    @password = "admin123456"
            if host!=nil then
            	@host=host
	    	if( @host=="localhost" )then
		    hosturl=@host
	    	else
	    	    hosturl="#{host}.mobilous.com"
	        end
	    else
            	@host=host
	    	hosturl="localhost"
	    end
	    #username = "mobilous"
	    username = "admin"
        else
	    @host=host
	    hosturl = @host
	    @password = "mob!kaihat$ush@"
	end

	#puts "host=#{host},hosturl=#{hosturl},username=#{username},projectid=#{projectid},dbname=#{dbname}"
        begin
            #@connection = PGconn.connect(:host => hosturl, :port => port, :dbname => dbname, :user =>username, :password => @password, :connect_timeout => 180000 )
            @connection = PG.connect(:host => hosturl, :port => port, :dbname => dbname, :user =>username, :password => @password, :connect_timeout => 180000 )
        rescue => e
	    p "Error on connectPg  : host=#{host},username=#{username},projectid=#{projectid},dbname=#{dbname},port=#{psql_port},console=#{console}, pass=#{@password} ==> #{e}"
	    puts caller
	    raise StandardError,"Error on connectPg  : host=#{host},username=#{username},projectid=#{projectid},dbname=#{dbname},port=#{psql_port},console=#{console}, pass=#{@password} ==> #{e}"

	    #Alert::sendAlertEmail( "userpostgres.rb","connectPG","#{host},#{username},#{projectid},#{dbname},#{psql_port},#{console}","#{e}:#{host},#{projectid}","Akira" )
            #createDB( host,dbname )
            #@connection = PGconn.connect(:host => hosturl, :port => port, :dbname => dbname, :user =>username, :password => @password)
	    #exit(1)
        end

	#p "Connected database #{@dbname} on host #{@host}"
	if(false)then
	    p "-"*80
	    puts caller
	    p "-"*80
	end
        return @connection
    end

    def connectConsolePg host,username,pass,dbname,port
	#puts "host=#{host},username=#{username},pass=#{pass},dbname=#{dbname},port=#{port}"
        @connection = PG.connect(:host => host, :port => port, :dbname => dbname, :user =>username, :password => pass)
        return @connection
    end

    def exec sql
	begin
            res=@connection.exec( sql )
	    #p sql
	    p "exec: #{res},#{res.result_error_message}"
	    return res
	rescue =>e
            puts "Error: #{e}"
            puts "Executing Error : #{sql}"
	    puts caller
	    #Alert::sendAlertEmail( "userpostgres.rb","exec",sql,"#{e}: #{sql}","Akira" )
	    
	    return "(#{e}) : #{sql}"
	end
    end

    def disconnect
	begin
	    @connection.close
	rescue => e
	     p "Error on disconnectPg  : #{e}"
	     #gets
	     #Alert::sendAlertEmail( "userpostgres.rb","disconnect","","#{e}","Akira" )
	end
    end

    def createTable tabledef,console=nil
        mobuser='mobilous'
        if( console )then
            mobuser='mobilousdbuser'
        end

        servicename = tabledef["servicename"]
        if servicename!="Mobilous" then
            puts "#{servicename} is not Mobilous. So, skip to create db."
        end

        tablename = tabledef["tablename"]
        csvfilename = tabledef["csvfilename"]
        fields = tabledef["fields"]
        primarykeys = Array.new

        tbl = UserPostgresTable.new
        tbl.useTable( self ,tablename )
        tbl.setTableDef( tabledef )

        createcom = "create table #{tablename} ("
        count=0
        fields.each { |field|
            if count>0 then
                createcom = "#{createcom},"
            end
            count=count+1

                #"dbType" : "text",
                #"fieldname" : "bigimage",
                #"primary" : false,
                #"autoinc" : false,
                #"description" : ""

            fieldname = field["fieldname"]
            fieldname.gsub!("-","_")
            type = field["dbType"]
            if( type==nil )then
                type="text"
            end
            if( type=="datetime" )then
                type="timestamp"
            end
            if( type=="double" )then
                type="float"
            end
            primary = field["primary"]
            autoinc = field["autoinc"]

            if primary==true then
                    primarykeys.push(fieldname)
            end
            if autoinc==true then
                    tmp = "#{createcom} #{fieldname} serial"
            else
                    tmp = "#{createcom} #{fieldname} #{type}"
            end

            createcom = tmp
        }

        if primarykeys.count()>0 then
            createcom = "#{createcom},row_index serial, primary key( "
            countb=0
            primarykeys.each { |pkey|
                if countb>0 then
                    createcom = "#{createcom},"
                end
                createcom = "#{createcom} #{pkey}"
                countb += 1
            }
            createcom = "#{createcom}) );"
        else
            createcom = "#{createcom} );"
        end

        puts "SQL = #{createcom}"
        res = @connection.exec(createcom)
        
        grant = "GRANT ALL PRIVILEGES ON DATABASE #{@dbname} TO postgres ;" 
        #puts "SQL = #{grant}"
        res = @connection.exec(grant)
        grant = "GRANT ALL PRIVILEGES ON DATABASE #{@dbname} TO #{mobuser} ;" 
        #puts "SQL = #{grant}"
        res = @connection.exec(grant)
        grant = "GRANT ALL PRIVILEGES ON SCHEMA public TO #{mobuser} ;" 

        #puts "SQL = #{grant}"
        res = @connection.exec(grant)

        if csvfilename!="" then
            tbl.loadCSVfromMongo( @host,@username,@projectid,csvfilename )
        end
    end


    def createTables tabledefs,viewdefs
        #tbl = UserPostgresTable.new
        if( tabledefs==nil )then
            puts "ERROR =====> no tabledefs is defined."
            return
        end

        tbls = Array.new
        views = Array.new
        triggers = Array.new
        procedures = Array.new

        tabledefs.each { |tabledef|
            #puts "create table tabledef=#{tabledef}"
            if( tabledef!=nil )then
                if( tabledef["view"]==true )then
                    puts "===>Reserve Create View #{tabledef['tablename']}"
                    views.push( tabledef )

                elsif( tabledef["tablename"]!=nil && tabledef["tablename"]!="" )then
                    puts "===>Create Table #{tabledef['tablename']}"
                    tbls.push( tabledef )

                elsif( tabledef["procedure"]==true )then
                    puts "===>Reserve create procedure #{tabledef['tablename']}"
                    procedures.push( tabledef )

                elsif( tabledef["trigger"]==true )then
                    puts "===>reserve create trigger #{tabledef['tablename']}"
                    triggers.push( tabledef )

                else
                    puts "table name is blank : #{tabledef['tablename']}"
                end
            end
        }

        puts "----------> procedures = #{procedures}"

        triggers.each { |trigdef|
            dropTrigger( trigdef )
        }

        procedures.each { |procdef|
            dropProcedure( procdef )
        }


        views.each { |viewdef|
            dropView( viewdef["tablename"] )
        }

        tbls.each { |tabledef|
            dropTable( tabledef["tablename"] )

            createTable( tabledef )
        }
        views.each { |viewdef|
            createView( viewdef )
        }

        procedures.each { |procdef|
            createProcedure( procdef )
        }

        triggers.each { |trigdef|
            createTrigger( trigdef )
        }
    end

    def dropTable tablename
        begin
            sql = "drop table #{tablename}"
            @connection.exec(sql)
            puts "drop #{tablename} is done. #{sql}"
			return true
        rescue => e
            puts " Error on dropTable #{tablename}: #{e}"
	     Alert::sendAlertEmail( "userpostgres.rb","dropTable",tablename,"#{e}:#{tablename}","Akira" )
			return false
        end
    end

    def dropView viewname
        begin
            sql = "drop view #{viewname}"
            @connection.exec(sql)
            puts "drop #{viewname} is done. #{sql}"
			return true
        rescue => e
            puts " Error on dropView #{viewname}: #{e}"
	     Alert::sendAlertEmail( "userpostgres.rb","dropView",viewname,"#{e}:#{viewname}","Akira" )
			return false
        end
    end

    def createView viewdef
        if( viewdef==nil )then
            return
        end

        begin
            puts "#{viewdef["viewname"]} will be executed as #{viewdef["query"]}"
            @connection.exec(viewdef["query"])
            puts "create #{viewdef["viewname"]} is done."
        rescue => e
            puts " Error on createView #{viewdef["viewname"]}: #{e}"
	     Alert::sendAlertEmail( "userpostgres.rb","createView",viewdef,"#{e}:#{viewdef}","Akira" )
        end
    end

    def createViews viewdefs
        if viewdefs==nil then return end
        viewdefs.each { |viewdef|
            createView( viewdef )
        }
    end

    def dropProcedure procdef
        # 改行で分ける。
        # 
        begin
            funcname = procdef["procedurename"]
            if( funcname.index("(")==nil )then
                funcname = "#{procdef['procedurename']}()"
            end
            sql = "drop function if exists #{funcname}"
            @connection.exec(sql)
            puts "drop #{funcname} is done. #{sql}"
        rescue => e
            puts " Error on dropProcedure #{procdef['procedurename']}: #{e}"
	     Alert::sendAlertEmail( "userpostgres.rb","dropProcedure",procdef,"#{e}:#{procdef}","Akira" )
        end
    end

    def createProcedure procdef

        begin
            #puts "#{procdef['procedurename']} will be executed as #{procdef['script']}"
            @connection.exec(procdef["script"])
            puts "create #{procdef["procedurename"]} is done."
        rescue => e
            puts " Error on createProcedure #{procdef['procedurename']}: #{e}"
	     Alert::sendAlertEmail( "userpostgres.rb","createProcedure",procdef,"#{e}:#{procdef}","Akira" )
        end
        
    end


    def dropTrigger trigdef
        begin
            watch = trigdef['watch_table']
            if( watch==nil ) then
                script = trigdef['script']
                a = script.split("\r\n")    # 改行分割
                b = a[0].split("on")
                watch = b[1]
            end
            sql = "drop trigger if exists #{trigdef['triggername']} on #{watch}"
            puts "SQL=#{sql}"
            @connection.exec(sql)
            puts "drop #{trigdef['triggername']} is done. #{sql}"
        rescue => e
            puts " Error on dropTrigger #{trigdef['triggername']}: #{e}"
	     Alert::sendAlertEmail( "userpostgres.rb","dropTrigger",trigdef,"#{e}:#{trigdef}","Akira" )
        end
    end

    def createTrigger trigdef
        begin
            puts "#{trigdef['triggername']} will be executed as #{trigdef['script']}"
            @connection.exec(trigdef["script"])
            puts "create #{trigdef["triggername"]} is done."
        rescue => e
            puts " Error on createTrigger (#{trigdef['triggername']}): #{e}"
	     Alert::sendAlertEmail( "userpostgres.rb","createTrigger",trigdef,"#{e}:#{trigdef}","Akira" )
        end
    end


        def selectExec sql
            begin
            result = @connection.exec(sql)
            puts "selectExec: num of rec is #{result.ntuples} for #{sql}"  # ヒットした行
            return result
            rescue => e
            error_message = " Error on selectExec (#{sql}): #{e}"
            puts error_message
            puts caller
        
            # エラーをログファイルに書き込む
            log_error(error_message)
        
            return nil
            end
        end
      
      # エラーをログファイルに書き込むメソッド
      def log_error(message)
        File.open('database_errors.log', 'a') do |file|
          file.puts "#{Time.now}: #{message}"
        end
      rescue => e
        puts "Failed to write to log file: #{e.message}"
      end

    def insert tablename,record
	sqlkey = ""
        sqlval = ""
	i=0
        record.each{ |key,value|
            if( i==0 )then
                sqlkey = key
                sqlval = "'#{value}'"
            else
                sqlkey = "#{sqlkey},#{key}"
                sqlval = "#{sqlval},'#{value}'"
            end
	    i += 1
        }
        sql = "insert into #{tablename} (#{sqlkey}) values (#{sqlval})"
	#p sql
        @connection.exec( sql )
    end

    def update tablename,record,whr
	sqlkey = ""
	i=0
        record.each{ |key,value|
	    if( i==0 )then
		sqlkey="#{key}='#{value}'"
	    else
		sqlkey="#{sqlkey},#{key}='#{value}'"
	    end
	    i += 1 
	}

	sql = "update #{tablename} set #{sqlkey} where #{whr}"
	@connection.exec( sql )
	#p sql
    end

    # https://devlights.hatenablog.com/entry/20080226/p1
    # PostgreSQLにてテーブルやカラムの各種情報を取得するSQL 
    def getPrimaryKeys tablename
	sql = "select ccu.column_name as COLUMN_NAME from information_schema.table_constraints tc,information_schema.constraint_column_usage ccu where tc.table_catalog='#{@dbname}' and tc.table_name='#{tablename}' and tc.constraint_type='PRIMARY KEY' and tc.table_catalog=ccu.table_catalog and tc.table_schema=ccu.table_schema and tc.table_name=ccu.table_name and tc.constraint_name=ccu.constraint_name"
	keys = @connection.exec(sql)
	return keys
    end

    def getFieldList tablename
	sql = "select * from information_schema.columns where table_catalog='#{@dbname}' and table_name='#{tablename}' order by ordinal_position"
	fieldlist = @connection.exec(sql)
	fields = Hash.new
	fieldlist.each { |rec|
	    key = rec["column_name"]
	    fields[key]=rec
	}
	return fields
	#				userid		userstatus	contract_mgr	startdate	createdatetime
	# table_catalog  		db_532		db_532		db_532		db_532		db_532
	# table_schema			public		public		public		public		public
	# table_name			users		users		users		users		users
	# column_name			userid		userstatus	contract_mgr	startdate	createdatetime
	# ordinal_position		1		6		13		17		20
	# column_default				0			    '2018-06-30'::date '2018-06-30 07:00:57.687173+00'::timestamp with time zone
	# is_nullable			NO		YES		YES		YES		YES
	# data_type			text		integer		boolean		date		timestamp with time zone
	# character_maximum_length
	# character_octet_length	1073741824
	# numeric_precision				32
	# numeric_precision_radix			2
	# numeric_scale					0
	# datetime_precision								0		6
	# interval_type
	# interval_precision
	# character_set_catalog
	# character_set_schema
	# character_set_name
	# collation_catalog
	# collation_schema
	# collation_name
	# domain_catalog
	# domain_schema
	# domain_name
	# udt_catalog			db_532		db_532		db_532		db_532		db_532
	# udt_schema			pg_catalog	pg_catalog	pg_catalog	pg_catalog	pg_catalog
	# udt_name			text		int4		bool		date		timestamptz
	# scope_catalog
	# scope_schema
	# scope_name
	# maximum_cardinality
	# dtd_identifier		1		6		13		18		20
	# is_self_referencing		NO		NO		NO		NO		NO
	# is_identity			NO		NO		NO		NO		NO
	# identity_generation
	# identity_start
	# identity_increment
	# identity_maximum
	# identity_minimum
	# identity_cycle
	# is_generated			NEVER		NEVER		NEVER		NEVER		NEVER
	# generation_expression			
	# is_updatable			YES		YES		YES		YES		YES
	#

    end

end

class UserPostgresTable
    def useTable db,tablename
        #コレクション選択
        # dbinstance = UserMongoDB.new
        # db = dbinstance.useDB( dbname )
        @tablename=tablename
        @db = db
        if @tablename == nil then
            puts "[WARING] tablename(#{tablename})is not existing"
        end
    end

    def getPrimaryKeys 
	return @db.getPrimaryKeys( @tablename )
    end

    def getFieldList
	return @db.getFieldList( @tablename )
    end

    # 与えたレコードの正当性を補填する。正規化したレコードを戻す。
    def validateFieldData orig
	record = Hash.new
	fields = @db.getFieldList( @tablename )
	orig.each { |k,v|
	    field = fields[k]
	    if( field!=nil )then
	    	udt_name = field["udt_name"]
	    	if( udt_name=="date" || udt_name=="timestamp" || udt_name=="timestamptz" )then
		    if( v!="" && v!=nil )then
		    	record[k]=v
		    end
	    	else
		    record[k]=v
		end
	    end
	}

	return record
    end

    def insertExec sql
       ret = @db.exec(sql)
       return true
    end

    def exec sql
        ret = @db.exec(sql)
        if( ret.index( "Executing Error" ) )then
            return false
        else
            return true
        end
    end

    def tablename
        return @tablename
    end

    def setTableDef tabledef
        @tabledef=tabledef
    end

    def tableDef
        return @tabledef
    end

    def remove cond
        puts "delete from #{@tablename} where #{cond}"
	@db.exec("delete from #{@tablename} where #{cond}")
    end

    def count(where_clause)
        sql = "SELECT COUNT(*) FROM #{@tablename}"
        sql += " WHERE #{where_clause}" if where_clause && !where_clause.empty?
        
        result = @db.selectExec(sql)
        result[0]['count'].to_i
      rescue => e
        puts "Database error in count method: #{e.message}"
        0  # エラーの場合は0を返す
      end

# レコード抽出
# @param conf [text] where 句で使用する検索条件。where以降のみ指定。nil の場合全件抽出。
# @param sort [text] order by 句で使用するソート条件。order by 以降のみ指定。nil の場合不定。
#
    def find2 conf,sort=nil,columns=nil
        #全件取得
	sql = "select "
	if columns!=nil then
	    sql << columns 
	else
	    sql << "*"
	end

	sql << " from #{@tablename}"
	sql << " where #{conf}" if conf!=nil
	sql << " order by #{sort}" if sort!=nil
	
	#p "find2 : #{sql}"
	records = @db.selectExec( sql )
	return records
    end

# レコード抽出
# @param conf [text] where 句で使用する検索条件。where以降のみ指定。nil の場合全件抽出。
# @param sort [text] order by 句で使用するソート条件。order by 以降のみ指定。nil の場合不定。
#

    def find_for_relation(conf, sort = nil, columns = nil)
        sql = "SELECT "
        sql << (columns || "COUNT(g.*)")
        sql << " FROM #{@tablename} AS g "
        sql << " #{conf}" if conf
        sql << " ORDER BY #{sort}" if sort
        
        @db.selectExec(sql)
      rescue => e
        puts "Database error in find_for_relation method: #{e.message}"
        []  # エラーの場合は空の配列を返す
      end

    def get_unique_event_codes_sql(table_name)
        sql = "SELECT DISTINCT event_code FROM #{table_name}"
        records = @db.selectExec(sql)
        # Convert PG::Result to an array of hashes
        return records.map { |row| row.to_h }
      end

    


 # この状態ではレコードを返していないのでは？
# find2 を取り敢えず作成した。

    def find conf
        #全件取得
        if( conf!=nil )then
            #puts "select * from #{@tablename} where #{conf}"
            records = @db.exec("select * from #{@tablename} where #{conf}")
            #puts "result :#{records}; records count: #{records.count()}"
            return records
        else
            records = @db.exec("select * from #{@tablename}")
            return records
        end
    end

    def findOne conf
        #puts "select * from #{@tablename} where #{conf}"
        records = @db.selectExec("select * from #{@tablename} where #{conf}")
		    #puts records
        if( records!=nil )then
					  if( records.count>0 )then
            	return records[0]
					  end
        end
        return nil
    end

    def makeKeyCondition keys,record
		str = ""
		count = 0
		keys.each{|key|
			val = record[key]
			if( count>0 )then str += " and " end
			str += "#{key}='#{val}'"
			count += 1
		}
		return str
    end

    def makeKeySet keys,record
		str = ""
		count = 0
		keys.each{|key|
		    val = record[key]
		    if( count>0 )then str += "," end
		    if( val.is_a?(String) )then
			val2 = val.gsub("'","''")
			val = val2
			#val = val2.gsub!("\"","\\\"")
		    end
		    str += "#{key}='#{val}'"
		    count += 1
		}
		return str
    end

    def makeInsertKeySet keys0,record
		keyhash = Hash.new
		keys0.each { |key|
		    keyhash[key]=1
		}
		keys=Array.new
		keyhash.each {|key,val|
		    keys.push( key )
		}

		keyset = ""
		valset = ""
		count = 0
		keys.each { |key|
			if( count>0 )then
				keyset += ","
				valset += ","
			end
			keyset += key
			if( record[key]==true )then #|| record[key]==false )then
				valset += "1"
			elsif( record[key]==false ) then 
				valset += "0"
			else
			    if( record[key].is_a?(String) )then
				val = record[key].gsub("'","''")
				#val.gsub!("\"","\\\"")
				valset += "'#{val}'"
			    else
				valset += "'#{record[key]}'"
			    end
			end
			count += 1
		}
		sql = "insert into #{tablename} (#{keyset}) values (#{valset})"
		return sql
	end

	def updateOrInsert recordKeys,conditionKeys,record
	    begin
		#@debug_base=true
		sql_update3 = makeKeyCondition( conditionKeys,record )
		puts "update or insert where cond = #{sql_update3}  <== #{conditionKeys}" if @debug_base
		records = @db.selectExec("select * from #{@tablename} where #{sql_update3}")
		puts "HIT RECORDS=#{records}" if @debug_base
		if( records!=nil && records.count>=1 )
		   puts "record count = #{records.count}" if @debug_base
		    if( records.count>=1 )then
			sql_update1 = "update #{tablename} set "
			sql_update2 = makeKeySet( recordKeys,record )
			sql = "#{sql_update1}#{sql_update2} where #{sql_update3}"
			p "updateOrInserti(udate) : #{sql}" if @debug_log
			return @db.exec( sql )
		    end
		end
		# array を合体??
		recordKeys2 = Marshal.load(Marshal.dump(recordKeys))
		recordKeys2.push( conditionKeys )
		recordKeys2.flatten!
		sql = makeInsertKeySet( recordKeys2,record )
		p "updateOrInsert(insert) : #{sql}" if @debug_log
		return @db.exec(sql)
	    rescue => e
		puts "updateOrInsert Error : #{e}"
		puts caller
	    end
	end

    def get_unique_event_codes
        sql = "SELECT DISTINCT event_code FROM #{@tablename}"
        records = @db.selectExec(sql)
        # Convert PG::Result to an array of hashes
        return records.map { |row| row.to_h }
      end
# http://libro.tuyano.com/index3?id=1102003&page=3


    def loadCSV host,username,projectid,f
        i=0
        @fieldcount=0

        tmpstr=""
        tmpcount=0
        loop {
            line0 = f.gets("\n")
            if line0==nil then
                break;
            end

            # CSV 漢字変換対応
            begin
                line = line0.gsub(/^\xEF\xBB\xBF/, '')
            rescue
                line = line0
            end

            line.chomp! 

            puts "line=#{line}"

            if i==0 then
                @title = line
                #puts "First line:#{line}"
                titleArray = line.split(",")
                @fieldcount = titleArray.count()
                #puts "Field count = #{@fieldcount}, #{@title}"
            else
                if tmpcount==0 then 
                    #puts "Data : #{line}"
                    valuecsv=""
                    dataArray0 = line.split(",")
                    cnt=dataArray0.count()
                    dataArray=Array.new
                    dataArray0.each{ |dat|
                        if( dat=="" )then
                            dataArray.push(nil)
                        else
                            dataArray.push(dat)
                        end
                    }
                    if( cnt<@fieldcount )then
                        for i in cnt+1..@fieldcount do
                            dataArray.push(nil)
                        end
                    end
                    cnt=dataArray.count()

                    tmpstr=line
                else
                    valuecsv=""
                    tmpstr = "#{tmpstr}\n#{line}"
                    #puts "Data : #{tmpstr}"
                    dataArray0 = tmpstr.split(",")
                    cnt=dataArray0.count()
                    dataArray=Array.new
                    dataArray0.each{ |dat|
                        if( dat=="" )then
                            dataArray.push(nil)
                        else
                            dataArray.push(dat)
                        end
                    }
                    if( cnt<@fieldcount )then
                        for i in cnt+1..@fieldcount do
                            dataArray.push(nil)
                        end
                    end
                    cnt=dataArray.count()
                end
                #puts "---step 1----- count=#{cnt},fieldcount=#{@fieldcnt},tmpcount=#{tmpcount},title=#{@title},value=#{dataArray}"
                if cnt==@fieldcount then
                    if tmpcount>0 then
                        tmpcount=0
                        #puts "#{i} : #{tmpstr}"
                        tmpstr=""
                    end
                    num = 0
		            numttl = 0
#                    puts "---step 2-----"
                    loop{
                        value = dataArray[num]

			            if value!=nil then
			                firstchar = value[0,1]
			                lastchar = value[-1,1]
			                if firstchar=="\"" then
				                loop{
				                    if lastchar=="\"" then
					                    break
				                    else
					                    num += 1
					                    valuenext=dataArray[num]
					                    value = "#{value},#{valuenext}"
					                    #puts ", #{valuenext}"
					                    lastchar = value[-1,1]
				                    end
				                }
			                end
			            #else
			            #    value=''
			            end

#                        puts "value = #{value}"

                        #if value != nil then
                        #    value =   Iconv.iconv("utf-8", "windows-874", value).join
                        #    value.gsub!("\'","\\\'")
                        #end

                        if( value!=nil ) then
                            valuecsv = "#{valuecsv} '#{value}'"
                        else
                            valuecsv = "#{valuecsv} null"
                        end
                        num += 1
			            numttl += 1
                        if numttl >= @fieldcount then
                            break
                        end
                        valuecsv = "#{valuecsv},"
#                        puts "---step 3 (in loop)-----"
                    }
                    sql = "insert into #{@tablename} (#{@title}) values (#{valuecsv})"
                    #puts "SQL = #{sql}"
                    result = @db.exec(sql)

                    puts "result=#{result}, sql=#{sql}}"
                else 
                    puts "Bad count : #{i} : current count=#{cnt}, target=#{@fieldcount}"
                    tmpcount = cnt
                    break
                end
            end
            i += 1
        }
    end

    def saveMongoCSVfromLocal host,username,projectid,csvfile
        if( csvfile==nil ) then
            puts "csvfile is null..."
            return
        end

        puts "loadCSV : file=#{csvfile}"

        #mongofile=UserMongoFileSystem.new

        mongofile=UserMongoFiles.new
        mongodb = UserMongo.new
	if host!=nil then
            mongodb.connectMongo( host+'.mobilous.com',27017 )
	else
            mongodb.connectMongo( 'localhost',27017 )
	end
        db=mongodb.useDB("appexe")

        #mongofile.useCategory(db,"templates")
        mongofile.useCategory(db,"database")
        mongofile.insert(csvfile,projectid)
    end

    def extractMongoCSVtoLocal host,username,projectid,csvfile
        if( csvfile==nil ) then
            puts "csvfile is null..."
            return
        end

        puts "loadCSV : file=#{csvfile}"

        mongofile=UserMongoFiles.new
        mongodb = UserMongo.new
	if host!=nil then
            mongodb.connectMongo( host+'.mobilous.com',27017 )
	else
            mongodb.connectMongo( 'localhost',27017 )
	end
        db=mongodb.useDB("appexe")
        #mongofile.useCategory(db,"templates")
        mongofile.useCategory(db,"database")
        f=mongofile.open(csvfile,projectid)
        s = f.read()

        t=Tempfile.open("MobApp")
        t.write(s)

        return t
        #t.path
    end

    def saveCSVintoTable host,username,projectid,localcsvfile
        puts "csv=#{localcsvfile}"

        i=0;
        aa= FasterCSV.table(localcsvfile,{ :headers => true,:quote_char => '"' })
        aa.each{ |row|
            puts "row=#{i}"
            row.each{ |value|
                puts "val=#{value}"
            }
            i += 1
        }
    end





    def loadCSVfromLocalFile host,username,projectid,csvfile
        if( csvfile==nil ) then
            puts "csvfile is null..."
            return
        end

        puts "loadCSV : file=#{csvfile}"

        #mongofile=UserMongoFileSystem.new

        mongofile=UserMongoFiles.new
        mongodb = UserMongo.new
	if( host!=nil ) then
	    hosturl=host+'.mobilous.com'
	else
	    hosturl='localhost'
	end
        mongodb.connectMongo( hosturl,27017 )
        db=mongodb.useDB("appexe")

        #mongofile.useCategory(db,"templates")
        mongofile.useCategory(db,"database")
        mongofile.insert(csvfile,projectid)

        self.loadCSVfromMongo(host,username,projectid,csvfile)
    end

    def loadCSVfromMongo host,username,projectid,csvfile
        #puts "loadCSV is not implemented yet..."

        if( csvfile==nil ) then
            puts "csvfile is null..."
            return
        end

        puts "loadCSV : file=#{csvfile}"

        mongofile=UserMongoFiles.new
        mongodb = UserMongo.new
	if( host !=nil )then
             mongodb.connectMongo( host+'.mobilous.com',27017 )
	else
             mongodb.connectMongo( 'localhost',27017 )
	end
        db=mongodb.useDB("appexe")
        #mongofile.useCategory(db,"templates")
        mongofile.useCategory(db,"database")
        f=mongofile.open(csvfile,projectid)
        if( f!=nil )then
            loadCSV(host,username,projectid,f)
        end
    end

end