#!/bin/bash
# set -x
TXTLogPath="log\/bdcSvr"
HOSTNAME="127.0.0.1"                                           #数据库信息
PORT="3306"

USERNAME="mmog"
PASSWORD="mmog"

DBNAME="DBLog"                                                       #数据库名称
TABLENAME="TLog"                                            #数据库中表的名称

#创建数据库
create_db_sql="create database IF NOT EXISTS ${DBNAME}"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"

#创建表
create_table_sql="create table IF NOT EXISTS ${DBNAME}.${TABLENAME} (event_id  varchar(20), user_id  varchar(108), open_id  varchar(108), role_id  varchar(24), role_key  varchar(24), server_id  varchar(20), client_os  varchar(8), user_ip  varchar(52), channel_id  varchar(12), app_channel_id  varchar(4), buess_time  varchar(40), transaction_id  varchar(4), user_balance  varchar(8), event_uuid  varchar(92), event_time  varchar(40), event_time2  varchar(40), free_diamond_balance  varchar(28), donate_diamond_balance  varchar(4), charge_diamond_balance  varchar(4), gold_balance  varchar(40), login_situation  varchar(4), role_level  varchar(8), vip_level  varchar(4), role_num  varchar(4), before_balance  varchar(40), after_balance  varchar(40), money_type  varchar(4), reason_id  varchar(8), reason_info  varchar(216), product_type  varchar(16), product_id  varchar(24), product_num  varchar(24), product_guid  varchar(4), binding_state  varchar(4), product_info  varchar(104), point  varchar(4), point_before  varchar(8), point_after  varchar(8), duration_time  varchar(20), pve_type  varchar(4), pve_id  varchar(20), pve_name  varchar(28), team_id  varchar(4), lineup  varchar(76), iswin  varchar(4), complete_type  varchar(4), fight_time  varchar(8), grade  varchar(4), task_type  varchar(4), task_id  varchar(16), character_id  varchar(24), role_name  varchar(28), role_extra  varchar(4), rewards_info  varchar(1112), action_type  varchar(12), buy_cost  varchar(16), sex  varchar(4), phy_balance  varchar(8), month_card_balance  varchar(4), register_ip  varchar(52), accountregister_time  varchar(40), userregister_time  varchar(40), userlast_active_time  varchar(40), total_charge  varchar(4), union_id  varchar(4), bag_info  varchar(716), currency_info  varchar(148), final_scene  varchar(4), final_action  varchar(4), online_time  varchar(20) ,one varchar(16) )"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD}  -D ${DBNAME} -e "${create_table_sql}"

#长度70
arrayKey=(event_id user_id open_id role_id role_key server_id client_os user_ip channel_id app_channel_id buess_time transaction_id user_balance event_uuid event_time event_time2 free_diamond_balance donate_diamond_balance charge_diamond_balance gold_balance login_situation role_level vip_level role_num before_balance after_balance money_type reason_id reason_info product_type product_id product_num product_guid binding_state product_info point point_before point_after duration_time pve_type pve_id pve_name team_id lineup iswin complete_type fight_time grade task_type task_id character_id role_name role_extra rewards_info action_type buy_cost sex phy_balance month_card_balance register_ip accountregister_time userregister_time userlast_active_time total_charge union_id bag_info currency_info final_scene final_action online_time)
arrayVal=(5 27 27 6 6 5 2 13 3 1 10 1 2 23 10 10 7 1 1 10 1 2 1 1 10 10 1 2 54 4 6 6 1 1 26 1 2 2 5 1 5 7 1 19 1 1 2 1 1 4 6 7 1 278 3 4 1 2 1 13 10 10 10 1 1 179 37 1 1 5)

for i in $(ls $TXTLogPath)
do 
	FullPath=$TXTLogPath/$i
	TABLENAME_INDEX=${TABLENAME}"_"`echo $i | sed -e 's/.log//g'`
	TABLENAME_INDEX=`echo $TABLENAME_INDEX | sed -e 's/-/_/g'`
	#创建表
	create_table_sql="create table IF NOT EXISTS ${DBNAME}.${TABLENAME_INDEX} (event_id  varchar(20), user_id  varchar(108), open_id  varchar(108), role_id  varchar(24), role_key  varchar(24), server_id  varchar(20), client_os  varchar(8), user_ip  varchar(52), channel_id  varchar(12), app_channel_id  varchar(4), buess_time  varchar(40), transaction_id  varchar(4), user_balance  varchar(8), event_uuid  varchar(92), event_time  varchar(40), event_time2  varchar(40), free_diamond_balance  varchar(28), donate_diamond_balance  varchar(4), charge_diamond_balance  varchar(4), gold_balance  varchar(40), login_situation  varchar(4), role_level  varchar(8), vip_level  varchar(4), role_num  varchar(4), before_balance  varchar(40), after_balance  varchar(40), money_type  varchar(4), reason_id  varchar(8), reason_info  varchar(216), product_type  varchar(16), product_id  varchar(24), product_num  varchar(24), product_guid  varchar(4), binding_state  varchar(4), product_info  varchar(104), point  varchar(4), point_before  varchar(8), point_after  varchar(8), duration_time  varchar(20), pve_type  varchar(4), pve_id  varchar(20), pve_name  varchar(28), team_id  varchar(4), lineup  varchar(76), iswin  varchar(4), complete_type  varchar(4), fight_time  varchar(8), grade  varchar(4), task_type  varchar(4), task_id  varchar(16), character_id  varchar(24), role_name  varchar(28), role_extra  varchar(4), rewards_info  varchar(1112), action_type  varchar(12), buy_cost  varchar(16), sex  varchar(4), phy_balance  varchar(8), month_card_balance  varchar(4), register_ip  varchar(52), accountregister_time  varchar(40), userregister_time  varchar(40), userlast_active_time  varchar(40), total_charge  varchar(4), union_id  varchar(4), bag_info  varchar(716), currency_info  varchar(148), final_scene  varchar(4), final_action  varchar(4), online_time  varchar(20) ,one varchar(16) )"
	mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD}  -D ${DBNAME} -e "${create_table_sql}"
	while read line
	do
		OLD_IFS="$IFS"
		IFS="|"
		array=($line)
		
		insert_sql="insert delayed into ${TABLENAME_INDEX} ("
		keyStr=""
		valStr=""
		for j in "${!array[@]}"
		do
			IFS="="
			subarray=(${array[j]})
			if [ ${#subarray[@]} == 2 ];then
				#echo ${subarray[0]}
				#echo ${subarray[1]}
				#校验key有效性，开启效率太低，关闭了又不放心
				found="false"
				for k in "${!arrayKey[@]}"
				do
					if [ ${arrayKey[k]} == ${subarray[0]} ];then
						found="true"
						if [ ${arrayVal[k]} -lt ${#subarray[1]} ];then
							arrayVal[k]=${#subarray[1]}
						fi
						break;
					fi
				done
				if [ ${found} == "false" ];then
					echo ${subarray[0]}
					arrayKey[${#arrayKey[@]}]=${subarray[0]}
					arrayVal[${#arrayVal[@]}]=${#subarray[1]}
				else
					keyStr+=${subarray[0]}","
					valStr+="\""`echo ${subarray[1]} | sed -e 's/"//g'`"\","
				fi
			fi
		done
		#导入数据库
		insert_sql+=$keyStr"one) values ("$valStr"0)"
		mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} -D ${DBNAME} -e  "${insert_sql}"
		# echo $insert_sql
	done < $FullPath &
done

#校验用的，暂时关闭(统计有哪些新的 列 现在70列)
##echo ${arrayKey[@]}
#echo ${#arrayKey[@]}
#echo ${arrayVal[@]}
#echo ${#arrayVal[@]}
all=""
for j in "${!arrayKey[@]}"
do
	if [ ${arrayKey[j]} == "{}" ];then
		continue
	fi
	
	all+=${arrayKey[j]}"  varchar("`expr ${arrayVal[j]} + ${arrayVal[j]} + ${arrayVal[j]} + ${arrayVal[j]}`"), "
done
echo ${all}