#!/bin/sh
. /home/db2inst1/.profile
#数据上报程序
#author huodaihao
#获得系统
DATE=$(date +%Y%m%d)
DATE_TIME=$(date +'%Y%m%d%H%M%S')
ORG="Z2017112000011"
DBNAME="pbctjuat"
USERNAME="db2admin"
PASSWORD="db2@admin"
#shell文件目录
SHELL_PATH=/home/tianjing/sh
#csv文件基本路径
CSV_PATH=/home/tianjing/csv
#csv文件路径永久路径
CSV_SAVE_PATH=/home/tianjing/savecsv
#日志存放路径
LOG_PATH=/home/tianjing/log
#定义一个正在执行
TMPFILE=/home/tianjing/sh/shell_csv.tmp
#检查是否有其他的实
if [ -e ${TMPFILE} ]
then
echo "Other instance is running!"
exit 0 #退出本脚本的执行
else
touch ${TMPFILE} #新建一个临时文件
chmod 600 ${TMPFILE}
fi
#设置一个
rap "rm -f ${TMPFILE}; exit" 0 1 2 3 9 15
echo "`date +%Y-%m-%d\ %T` ------------START----------">>${LOG_PATH}/load_data_${DATE}.log
#连接数据库 输出日志信息
db2 connect to $DBNAME user $USERNAME using $PASSWORD
echo "`date +%Y-%m-%d\ %T` -清空ods_temp下的T_SC_MERCHANT_INFO，T_SC_MERCHANT_IM_DETAIL，T_SC_MERCHANT_EX_DETAIL，T_RBYHZHXX表的数据">>${LOG_PATH}/load_data_${DATE}.log
db2 "delete from ODS_TEMP.T_SC_MERCHANT_INFO "
db2 "delete from ODS_TEMP.T_SC_MERCHANT_IM_DETAIL "
db2 "delete from ODS_TEMP.T_SC_MERCHANT_EX_DETAIL "
db2 "delete from ODS_TEMP.T_RBYHZHXX "
db2 commit
for file_all_name in ${CSV_PATH}/*.csv
do
echo "$file_all_name --------------"
#获取csv文件名称
CSV_NAME=$(basename "$file_all_name")
echo "$CSV_NAME----"
echo "`date +%Y-%m-%d\ %T` csv文件的文件名称是：${CSV_NAME}">>${LOG_PATH}/load_data_${DATE}.log
# gbk --> utf-8
if file "${file_all_name}"|grep -q UTF-8 >/dev/null ;
then
echo "csv is utf-8" >> ${LOG_PATH}/load_data_${DATE}.log
else
echo "csv is gbk to utf-8 success" >> ${LOG_PATH}/load_data_${DATE}.log
iconv -c -f gbk -t utf-8 ${file_all_name}> ${LOG_PATH}/temp_${CSV_NAME}
rm -f ${file_all_name}
mv ${LOG_PATH}/temp_${CSV_NAME} ${CSV_PATH}/${CSV_NAME}
fi
#截取1个字
FLAG=${CSV_NAME:0:6}
ORG_CODE=${CSV_NAME:9:14}
LOAD_DATE=${CSV_NAME:24:8}
#商户基本信息表
TEMPLATE_FILE_ID1='20160813173727372741975374175096280'
#商户入金支付明细
TEMPLATE_FILE_ID2='16081318351835185705507814252728000'
#商户出金支付明细
TEMPLATE_FILE_ID3='16081318360836823267029553581420000'
#融宝商户对应账号的信息
TEMPLATE_FILE_ID4='16081318365236529830235585519220000'
sed -i '1d' $file_all_name
sed -i "s/\//-/g" $file_all_name
if [ $FLAG = "SSJBXX" ]
then
db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID1}'"
echo "`date +%Y-%m-%d\ %T` load 商户基本信息数据">>${LOG_PATH}/load_data_${DATE}.log
echo "1111111111111111"
#商户基本信息
db2 "LOAD client FROM '${file_all_name}' OF DEL MODIFIED BY CODEPAGE=1208 COLDEL, DECPLUSBLANK DATESISO INSERT INTO ODS_TEMP.T_SC_MERCHANT_INFO NONRECOVERABLE "
db2 commit
fi
if [ $FLAG = "SHRJMX" ]
then
db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID2}'"
echo "`date +%Y-%m-%d\ %T` load 入金支付明细">>${LOG_PATH}/load_data_${DATE}.log
#入金支付明细
db2 "LOAD client FROM '${file_all_name}' OF DEL MODIFIED BY CODEPAGE=1208 COLDEL, DECPLUSBLANK DATESISO INSERT INTO ODS_TEMP.T_SC_MERCHANT_IM_DETAIL NONRECOVERABLE"
db2 commit
fi
if [ $FLAG = "SSCJMX" ]
then
db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID3}'"
echo "`date +%Y-%m-%d\ %T` load 出金支付">>${LOG_PATH}/load_data_${DATE}.log
#出金支付明细
db2 "LOAD client FROM '${file_all_name}' OF DEL MODIFIED BY CODEPAGE=1208 COLDEL, DECPLUSBLANK DATESISO INSERT INTO ODS_TEMP.T_SC_MERCHANT_EX_DETAIL NONRECOVERABLE"
db2 commit
fi
if [ $FLAG = "RBYHXX" ]
then
db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID4}'"
echo "`date +%Y-%m-%d\ %T` load荣宝信息">>${LOG_PATH}/load_data_${DATE}.log
#融宝银行账户信息
db2 "LOAD client FROM '${CSV_PATH}/${CSV_NAME}' OF DEL MODIFIED BY CODEPAGE=1208 COLDEL, DECPLUSBLANK DATESISO INSERT INTO ODS_TEMP.T_RBYHZHXX NONRECOVERABLE"
db2 commit
fi
mv -f ${CSV_PATH}/${CSV_NAME} ${CSV_SAVE_PATH}
done
echo "`date +%Y-%m-%d\ %T` load ods_temp success">>${LOG_PATH}/load_data_${DATE}.log
db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID1}'"
db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID2}'"
db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID3}'"
db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID4}'"
db2 "call ODS.P_PAY_TYSHXX_WLJYMX('${LOAD_DATE}','${ORG_CODE}')"
echo "`date +%Y-%m-%d\ %T` load ods success">>${LOG_PATH}/load_data_${DATE}.log
db2 "call DW.P_PAY_TYSHXX('${LOAD_DATE}','${ORG_CODE}','','')"
db2 "call DW.P_PAY_WLJYMX('${LOAD_DATE}','${ORG_CODE}','','')"
echo "`date +%Y-%m-%d\ %T` load DW success">>${LOG_PATH}/load_data_${DATE}.log
db2 commit;
db2 terminate;
rm -f ${TMPFILE}
#!/bin/sh
#----------------------------------------------------
#功能：将文件上传到FTP文件服务器
#
#时间：2015-03-22
#----------------------------------------------------
#FTP地址
IP_ADDRESS="192.168.0.111"
#FTP端存放目录
REMOTE_PATH="/load_data/files/test_data"
#shell存放目录
BASE_PATH="/load_shell"
#本地需要上传的文件存放目录
LOCAL_PATH="/home/pbc-file-maxfiles"
#本地文件上传后备份存放目录
LOCAL_ZIP_PATH="/home/pbc-file-maxfiles/data_bak"
#FTPP用户名
USERNAME="db2inst1"
#FTP密码
PASSWORD="db2inst1"
#获取系统时间到天
DATE=$(date +%Y%m%d)
#echo $DATE
echo "`date +%Y-%m-%d\ %T` : ---------------------------START----------------------------------" >>$BASE_PATH/log/ftp_up_$DATE.log
echo "`date +%Y-%m-%d\ %T` : 开始上传文件" >>$BASE_PATH/log/ftp_up_$DATE.log
ftp -in <<EOF
open $IP_ADDRESS
user $USERNAME $PASSWORD
binary
cd $REMOTE_PATH
lcd $LOCAL_PATH
espv4 off
passive
mput "*.csv"
bye
EOF
echo "`date +%Y-%m-%d\ %T` : 上传文件结束" >>$BASE_PATH/log/ftp_up_$DATE.log
#获取系统时间到天
#DATE=$(date +%Y%m)
echo "`date +%Y-%m-%d\ %T` : 准备移动文件到 ${LOCAL_ZIP_PATH} 目录 " >>$BASE_PATH/log/ftp_up_$DATE.log
for file_all_zip_name in ${LOCAL_PATH}/*;do
ZIP_FILE_NAME=$(basename "$file_all_zip_name")
#
temp_zip_file=$file_all_zip_name
#截取文件名
TABLE_NAME_STR=${ZIP_FILE_NAME:0:4}
ORG=${ZIP_FILE_NAME:4:14}
DAY=${ZIP_FILE_NAME:18:8}
MONTH=${ZIP_FILE_NAME:18:6}
FILE_NAME=${ZIP_FILE_NAME:0:26}
if [ ! -d "$LOCAL_ZIP_PATH/$MONTH" ];
then
mkdir $LOCAL_ZIP_PATH/$MONTH
fi
# echo "`date +%Y-%m-%d\ %T` : 移动文件 ${ZIP_FILE_NAME}" >>$BASE_PATH/log/ftp_up_$DATE.log
#将解压完的文件从临时文件夹移到永久保存文件夹
mv -f $temp_zip_file $LOCAL_ZIP_PATH/$MONTH
done
echo "`date +%Y-%m-%d\ %T` : 文件移动完毕" >>$BASE_PATH/log/ftp_up_$DATE.log
#cd $LOCAL_ZIP_PATH
#if [ ! -d "$LOCAL_ZIP_PATH/$DATE" ];
# then
# mkdir $LOCAL_ZIP_PATH/$DATE
#fi
#mkdir $DATE
#将解压完的文件从临时文件夹移到永久保存文件夹
#mv -f $LOCAL_PATH/* $LOCAL_ZIP_PATH/$DATE
echo "`date +%Y-%m-%d\ %T` : ---------------------------END----------------------------------" >>$BASE_PATH/log/ftp_up_$DATE.log
浙公网安备 33010602011771号