Test data generator

config.yml

method1:
    connect_username:   sa
    connect_password:   pwd
    connect_host:   172.29.140.157
    connect_database:  DB_Test
    connect_encode:  GBK
    telnet_host:  172.29.131.102
    telnet_port:  3001
    telnet_user:  zhou_meichen
    telnet_pwd:  pwd
method2:
    source_server:
      connect_username:  sa
      connect_password:   pwd
      connect_host:   172.29.140.157
      connect_database:  DB_Test
      connect_encode:  GBK
    linked_server:
      connect_username:  sa
      connect_password:   pwd
      connect_host:   172.29.140.156
      connect_database:  DB
      connect_encode:  GBK

excel_to_xml.rb

#encoding: utf-8
require 'spreadsheet'
require 'rexml/document'
include REXML

class ExcelToXML
	attr_accessor :dataResult #array，获取每个xml所需数据
	attr_accessor :resultPath #string，生成的xml存储路径
	attr_accessor :excelPath #string，excel文件路径
	attr_accessor :templatePath #string，xml模板文件路径
	attr_accessor :sheetIndex #int，excel文件数据所在sheet
	attr_accessor :interface #string，当前接口名称，用于拼凑xml的名称和存储路径
	attr_accessor :normalLineNum #int，excel文件中正常数据行数，默认为第1行
	
	def initialize
		@dataResult = Array.new
		@@otherData = Hash.new
		@normalLineNum = 1
	end

	def traversalExcel
		begin
			Spreadsheet.client_encoding= "UTF-8" #config:standard xml is UTF-8, bank xml is GB2312

			data = Spreadsheet.open(@excelPath)
			sheets = data.worksheet(@sheetIndex)
			content = Array.new

			#将sheet转换成array
			sheets.each do |row|
				temp = Array.new
				for i in (0..row.size-1)
					temp.push row[i]
				end
				content.push temp
			end
			return content
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def loadXPath
		begin
			content = traversalExcel()
			xpath = Array.new
			for j in (0..content[0].size-1)
				xpath.push content[0][j].to_s
			end
			return xpath
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def loadNormalData
		begin
			content = traversalExcel()
			for i in (1..@normalLineNum)
				array = Array.new
				for j in (0..content[0].size-1)
					array.push content[i][j].to_s
				end
				@dataResult.push array
			end
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def loadExceptionData(type)
		begin
			if type == "one"
				content = traversalExcel()
				#one exception data
				for je in (0..content[0].size-1)
					for i in (@normalLineNum+1..content.size-1)
						unless content[i][je].nil?
							array = Array.new
							array[je] = content[i][je]
							for jn in (0..content[0].size-1)
								unless je == jn
									array[jn] = content[1][jn]
								end
							end
							@dataResult.push array
						end
					end
				end
			elsif type == "multi"
				#multi exception data (2..n)
				for j in (1..content[0].size-1)
					array = Array.new
					for je in (0..j)
						array.push content[@normalLineNum+1][je].to_s #任取一行异常数据，此处取第一行异常数据
					end
					for jn in (j+1..content[0].size-1)
						array.push content[1][jn].to_s #任取一行正常数据，此处取第一行正常数据
					end
					@dataResult.push array
				end
			end
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def traversalTemplate(template)
		begin
			if File.exist? template
				file = File.open(template)
				doc = Document.new file
				return doc
			else
				puts "ERROR: can\'t find #{template}. Please try again."
			end
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def autoAddData(doc,xpath,data)
		begin
			for i in (0..xpath.size-1)
				doc.elements.each(xpath[i].to_s) do |e|
					e.add_text data[i].to_s unless data[i].to_s == "NULL" #NULL代表该字段为空值
				end
			end
			return doc
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end
	
	def manualAddData(doc) #除excel数据：固定值，随机值等
		begin
			unless @@otherData.nil?
				@@otherData.each do |k,v|
					doc.elements.each(k.to_s) do |e|
						if v.size == 1
							e.add_text v.first unless v.first == "NULL" #NULL代表该字段为空值
						else
							ke = Kernel.const_get(v[0]).new
							se = ke.send(v[1],*v[2..v.size-1])
							e.add_text se.to_s unless se.to_s == "NULL" #NULL代表该字段为空值
						end
					end
				end
			end
			return doc
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end
	
	def addData(xpath,*data) #除excel数据：固定值，随机值
		begin
			@@otherData[xpath.to_s] = *data
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def write(doc)
		begin
			sleep 1 #由于程序运行过快导致时间戳精度不够，故人工减速
			filename = "#{@interface}-#{(Time.now).strftime('%Y%m%d%H%M%S')}.xml"
			filepath = "#{@resultPath}/#{filename}"
			unless File.exist? filepath
				file = File.new(filepath,"w+")
				file.puts doc.write
				file.close
			end
		rescue
			puts "error:#{$!} at:#{$@}"
		end
		#@notes.push "#{@filename},#{@serialID.map{|id| id}.join(',')},#{@abnormal.map{|ab| ab}.join(',')}"
	end
	
	def run
		begin
			xpath = loadXPath
			@dataResult.each do |row|
				doc = traversalTemplate(@templatePath)
				doc = autoAddData(doc,xpath,row)
				doc = manualAddData(doc)
				write(doc)
			end
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end
end

mssql_to_csv.rb

#encoding: utf-8
require 'CSV' #standard
require 'tiny_tds' #db connect
require 'yaml' #standard
require 'net/telnet' #standard


#Fix Telnet Bug
class Net::Telnet
    def print(string)
      string = string.gsub(/#{IAC}/no, IAC + IAC) if @options["Telnetmode"]

      if @options["Binmode"]
        self.write(string)
      else
        if @telnet_option["BINARY"] and @telnet_option["SGA"]
          self.write(string.gsub(/\n/n, CR))
        elsif @telnet_option["SGA"]
          self.write(string.gsub(/\n/n, EOL)) ### fix here. replace CR+NULL by EOL
        else
          self.write(string.gsub(/\n/n, EOL))
        end
      end
    end
end

class MSSqlToCSV	
	attr_accessor :sql
	attr_accessor :bat_path
	
	def initialize
		config = YAML.load_file(File.expand_path("../../conf/config.yml",__FILE__))
		
		@connect_username = config["method1"]["connect_username"]
		@connect_password = config["method1"]["connect_password"]
		@connect_host = config["method1"]["connect_host"]
		@connect_database = config["method1"]["connect_database"]
		@connect_encode = config["method1"]["connect_encode"]
		
		@telnet_host = config["method1"]["telnet_host"]
		@telnet_port = config["method1"]["telnet_port"]
		@telnet_user = config["method1"]["telnet_user"]
		@telnet_pwd = config["method1"]["telnet_pwd"]
	end
	
	def dbConnect
		#1)connect
		#sql server: GBK
		@db_client = TinyTds::Client.new(:username => @connect_username, :password => @connect_password, :host => @connect_host, :database => @connect_database,:encode=>@connect_encode)
		
		return @db_client
	end
	
	def csvResult
		#2)execute
		return dbConnect.execute(@sql)
	end

	def dbClose
		@db_client.close
	end
	
	def batExecute
	#5)execute .bat
	#TODO: telnet
	server = Net::Telnet::new("Host" => @telnet_host,"Prompt"=> /C:.*>/)
	server.login(@telnet_user,@telnet_pwd){|c| print c}
	server.cmd("#{@bat_path}"){|c| print c}
	server.close
	end
end

mssql_to_mssql.rb

#encoding: utf-8
require 'CSV' #standard
require 'tiny_tds' #db connect
require 'yaml' #standard
require 'net/telnet' #standard


#if sql doesn't work, please use this class
class MSSqlToMSSql
	attr_accessor :sql
	
	def initialize
		config = YAML.load_file(File.expand_path("../../conf/config.yml",__FILE__))
		
		@source_connect_username = config["method2"]["source_server"]["connect_username"]
		@source_connect_password = config["method2"]["source_server"]["connect_password"]
		@source_connect_host = config["method2"]["source_server"]["connect_host"]
		@source_connect_database = config["method2"]["source_server"]["connect_database"]
		@source_connect_encode = config["method2"]["source_server"]["connect_encode"]
		
		@linked_connect_username = config["method2"]["linked_server"]["connect_username"]
		@linked_connect_password = config["method2"]["linked_server"]["connect_password"]
		@linked_connect_host = config["method2"]["linked_server"]["connect_host"]
		@linked_connect_database = config["method2"]["linked_server"]["connect_database"]
		@linked_connect_encode = config["method2"]["linked_server"]["connect_encode"]
	end
	
	def dbConnect(connect_flag)
		if connect_flag == "source"
			@db_client = TinyTds::Client.new(:username => @source_connect_username, :password => @source_connect_password, :host => @source_connect_host, :database => @source_connect_database,:encode=>@source_connect_encode)
		elsif connect_flag == "linked"
			@db_client = TinyTds::Client.new(:username => @linked_connect_username, :password => @linked_connect_password, :host => @linked_connect_host, :database => @linked_connect_database,:encode=>@linked_connect_encode)
		end
		return @db_client
	end
	
	def dbClose()
		@db_client.close
	end
	
	def execute(connect_flag)
		return dbConnect(connect_flag).execute(@sql)
	end
end

xml_bulkdata.rb

#encoding: utf-8
require 'rexml/document'
include REXML

class XMLBulkData
def transfer(template,startline,endline,number)
	id = nil #2014.4.10
	#kkckc = nil #2014.4.10
	
	fileline = Array.new
	result = Array.new

	if File.exist? template
		file = File.open(template)
		file.each_line do |line|	
			fileline.push line
		end
		file.close
	end
	for i in(0..startline-2)
		unless fileline[i].include? "BankSerialID"#"BankSerialID"
			result.push fileline[i] 
		else
			#id 
			id = rand(9999999999999999)
			result.push "<BankSerialID>#{id}</BankSerialID>" #付款组编号#"<BankSerialID>#{id}</BankSerialID>"
		end
	end
	for i in (0..number-1)
		for j in (startline-1..endline-1)
=begin
			if fileline[j].include? "KKCKC" and not fileline[j].include? "KKCKCO"
				kkckc = rand(999999999999)
				result.push "<KKCKC>#{kkckc}</KKCKC>" #付款组明细编号
			elsif fileline[j].include? "KIHDC"
				result.push "<KIHDC>#{khhdc}</KIHDC>" #付款组编号
			elsif fileline[j].include? "KICKC" #付款组明细编号
				result.push "<KICKC>#{kkckc}</KICKC>"
			else
				result.push fileline[j]
			end
=end

			unless fileline[j].include? "BankSerialDetailsID"
				result.push fileline[j]
			else
				id = rand(9999999999999999)
				result.push "<BankSerialDetailsID>#{id}</BankSerialDetailsID>"
			end

		end
	end
	#result.push fileline[fileline.size-2]#2014.4.10
	result.push fileline[fileline.size-1]
	file = File.new("D:/BankTemplate/#{(Time.now).strftime('%Y%m%d%H%M%S')}.xml","w+")
	file.puts result
	file.close
end
end

xml_http_get.rb

#encoding: utf-8
require 'open-uri'
require 'net/http'

	class XMLHTTPGet
		attr_accessor :url
		attr_accessor :localPath
		attr_accessor :host
		
		def getToXML
			begin
				uri = URI.parse(@url)
				req = Net::HTTP::Get.new(uri.path)
				req .content_type = 'text/xml'
				http = Net::HTTP.new(uri.host,uri.port)
				request = http.start{|h| h.request(req)}
				file = File.new(@localPath,"w+")
				file.puts request.read_body				
				file.close
			rescue
				puts "error:#{$!} at:#{$@}"
			end
		end
	end

xml_http_post.rb

#encoding: utf-8
require 'open-uri'
require 'net/http'

	class XMLHTTPPost
		attr_accessor :url
		attr_accessor :localPath
		attr_accessor :host
		
		def postFromXML
			begin
				file = File.read(@localPath)
				file.force_encoding("UTF-8")
				puts file
				uri = URI.parse(@url)
				req = Net::HTTP::Post.new(uri.path)
				req.body = file
				req.content_type = 'text/xml'
				http = Net::HTTP.new(uri.host,uri.port)
				http.start{|h| h.request(req)}
			rescue
				puts "error:#{$!} at:#{$@}"
			end
		end
	end