#!/bin/sh
. $(dirname "$0")/public_s.sh
#张明伟
#本shell适合有传出参数的标准存储过程
#v1.1 张明伟 20150107
dt=$(date -d "-1 days " +"%Y%m%d")
log=${0##*/};
log=$HOME/zmw_data/logs/${log%%.*}_$dt.log
cd ~/zmw_data
lastyend=$pub_lastyearend;
#本日为1月27号，取上年底
if [ ${dt:4:4} -ge 127 ]
then
i_dt_e=${dt:0:4}0101
lastyend=$(date -d "$i_dt_e -1 days" +"%Y%m%d" )
fi;
echo lastyend is $lastyend | tee -a $log;
debug="N" #是否调试
bkflg="N" #表备份标志
datatype="M" #日期类型,M表示月数据,D表示日数据
dt_sub="";
#运行存储过程及判断是否成功，失败则循环运行直到成功
exec_tmpfile()
{
proc_tmp=$1; #存储过程
date_tmpflg=$2; #日期参数
extp=$3; #扩展参数
while true
do
#执行存储过程
dtss1=$(date +"%Y%m%d%H%M%S")
dtss=$(date +"%s")
echo $dtss1 | tee -a $log;
db2 connect to zxjxdbs >$pub_nul;
tmp_rf=logs/$$_file;
touch $tmp_rf;
if [ ${#date_tmpflg} -eq 0 ];then
db2 -x "call $proc_tmp(?,?,?,?$extp)" >$tmp_rf;
else
db2 -x "call $proc_tmp(?,?,?,?,$date_tmpflg$extp)" >$tmp_rf;
fi;
dtse1=$(date +"%Y%m%d%H%M%S")
dtse=$(date +"%s")
echo $dtse1 | tee -a $log;
cat $tmp_rf>>$log;
d_sub=$((dtse-dtss))
echo "$proc_tmp(?,?,?,?,$date_tmpflg$extp)运行时长为:$((d_sub/3600))小时$((d_sub/60))分$((d_sub%60))秒" | tee -a $log;
#结果判断
#原失败判断标志改为成功判定标志
stext="Parameter Value : 0";
res_tmp=` grep -iE "Success|$stext" $tmp_rf | tr -d ' ' `;
if [ "$res_tmp" != "" ];then
#成功则进行下一步操作，否则继续运行
echo "$proc_tmp 运行成功" | tee -a $log;
rm -f $tmp_rf>$pub_nul;
break;
fi;
echo ".....isquiet is $isquiet";
[ "$isquiet" != "Y" ] && echo "是否需要跑其它的?";
echo "$proc_tmp 运行失败，继续运行!" | tee -a $log;
rm -f $tmp_rf>$pub_nul;
sleep 15;
done;
}
#调用函数
call_p()
{
ar="$1"
dts="$2"
extendpss="$3"
echo "存储过程参数为:$ar" | tee -a $log;
echo "月日期参数为:$dts" | tee -a $log;
echo "附加参数为:$extendpss" | tee -a $log;
if [ ${#dts} -eq 0 ];then
echo "db2 -x call $ar(?,?,?,?$extendpss)" | tee -a $log
db2 connect to zxjxdbs>$pub_nul;
if [ "$debug" != "Y" ];then
exec_tmpfile $ar $dts;
fi;
fi;
if [ ${#extendpss} -gt 0 -a ${#dts} -gt 0 ];then
echo "db2 -x call $ar(?,?,?,?,$dts$extendpss)" | tee -a $log
db2 connect to zxjxdbs>$pub_nul;
if [ "$debug" != "Y" ];then
exec_tmpfile $ar $dts "$extendpss";
fi;
fi;
for d in $dts
do
echo d is $d.......
if [ $d -lt 0 ];then
d=",$d"
echo "数据日期为：$d" | tee -a $log
fi;
dt_sub=$d;
i=0;
for a in $ar
do
#echo -n "是否确定跑此存储过程:$a..[y/n]:"
#read yn
yn='Y';
ryn=`echo $yn | tr 'a-z' 'A-Z' `
# echo $ryn
tb=${art[i]}
if [ $d -eq $lastyend -a "$a" = "PC_ZLKH" ]
then
continue;
fi;
a=` echo "$a" | tr 'a-z' 'A-Z' `;
xpc=` echo "$xpc" | tr 'a-z' 'A-Z' `;
if [ "$a" = "$xpc" ]
then
continue;
fi;
dtime=$(date +"%Y%m%d%H%M%S")
db2 connect to zxjxdbs>$pub_nul;
if [ ! -e "bak/${tb}_${d}*.del" -a "$tb" != "" -a "$bkflg" = "Y" -a "$debug" != "Y" ]
then
echo db2 "export to bak/${tb}_${d}_$dtime.del of del select * from $tb where datadate=$d" | tee -a $log;
if [ "$debug" != "Y" ]
then
db2 "export to bak/${tb}_${d}_$dtime.del of del select * from $tb where datadate=$d" | tee -a $log;
gzip bak/${tb}_${d}_$dtime.del &
fi;
fi;
if [ "$ryn" = "Y" ];then
echo "db2 -x call $a(?,?,?,?,$d$extendpss)............" | tee -a $log
if [ "$debug" != "Y" ];then
#db2 -x "call $a(?,?,?,?,$d)" | tee -a $log
exec_tmpfile "$a" "$dt_sub" "$extendpss"
fi;
elif [ "$ryn" = "N" ];then
echo "db2 -x call $a(?,?,?,?,$d$extendpss) is not run!" | tee -a $log
if [ "$debug" != "Y" ];then
#db2 -x "call $a(?,?,?,?,$d) is not run!" | tee -a $log
exec_tmpfile "$a" "$dt_sub" "$extendpss"
fi;
elif [ "$ryn" = "Q" ];then
echo "quit!"
exit;
fi;
i=$((i+1))
db2 connect reset>$pub_nul;
db2 terminate>$pub_nul;
done;
done;
}
count_i=0;
datechk()
{
dc_date=$1;
if [ ${#dc_date} -gt 0 ]
then
if [[ $dc_date -lt 19000101 || $dc_date -gt 29991231 ]]
then
echo "日期$dc_date无效!" | tee -a $log;
count_i=$((count_i+1));
exit;
fi;
fi;
}
#日期参数，一定要按顺序排列
#dtms=" 20130131 20130228 20130331 20130430 20130531 20130630 20130731 20130831 20130930 20131031 20131130 20131231 "
pub_datatype="M";
if [ $lastyend -eq $pub_lastmend ]
then
getlastyearend $lastyend;
lastyend="$pub_result";
fi;
fc_getdayebtw $lastyend $pub_lastmend
dtms="$pub_result";
#命令交互入口
while getopts "s:e:d:p:b:gt:i:x:z:jaE:" opt
do
case "$opt" in
"s") strdt=$OPTARG;; #开始日期
"t") datatype=$OPTARG;; #日期类型
"p") procs=$OPTARG;;
"x") extendp=$OPTARG;;
"z") xpc=$OPTARG;;
"e") enddt=$OPTARG;;
"i") incr=$OPTARG;; #时间步长
"d") datadate=$OPTARG;;
"j") jxflg="Y";; #每月27号绩效数据
"g") debug="Y";;
"b") bkflg=$OPTARG;;
"a") autoflg="Y";;
"E") excluop="$OPTRAG";;
*) echo "Usage: ${0##*/} [-g] [-t datatype:M/D] [-p procname] [-a] [ -s startdate -e enddate] [-d datadate] "
exit;;
esac
done
if [ "$jxflg" = "Y" -a "$bkflg" = "" ]
then
bkflg="Y";
fi;
call_usage()
{
echo "Usage: ${0##*/}
[-g] out the debug commands
[-j] excute the managers d&l between yearstart and lastmonths
[-t datatype] M or D
[-p procname] procedurece anem
[-a] autobak flag Y/N
[-s startdate -e enddate]
[-d datadate]
[-x] extend arguments
"
}
datechk $strdt;
datechk $enddt;
datechk $datadate;
[ "$debug" = "Y" ] && log=$pub_null && echo "at debug status!";
echo | tee -a $log;
date | tee -a $log;
if [ $count_i -gt 0 ]
then
echo "日期输入有误，请检查！" | tee -a $log;
exit;
fi;
#1.重新跑绩效前的准备
#上月末
lastmon=${dt:0:6}01
lastmon=$(date -d "$lastmon -1 days " +"%Y%m%d")
lastwek=$(date -d "$dt -7 days " +"%Y%m%d")
lastday=$(date -d "$dt -1 days " +"%Y%m%d")
#若指定数据日期，则不运行以下过程
if [ "$strdt" != "" ]
then
cut_dtms=0;
for sub_dtms in $dtms
do
#echo $sub_dtms
if [ $sub_dtms -ge $strdt ]
then
cut_dtms=$sub_dtms;
break;
fi;
done;
t_dtms=$cut_dtms" "${dtms##*${cut_dtms}};
if [ "$enddt" != "" ]
then
t_dtms=${t_dtms%%${enddt}*}" "$enddt;
fi;
dtms="$t_dtms";
#若指定数据日期，则重新写月日期参数变量
elif [ "$datadate" != "" ]
then
strdt=$datadate
enddt=$datadate
dtms=$datadate;
elif [ "$autoflg" = "Y" ]
then
strdt=$dt
enddt=$dt
dtms=$dt;
else
echo "";
fi;
[ "$dtms" != "" ] && datatype="D";
[ "$jxflg" = "Y" ] && datatype="M";
[ "$incr" = "" ] && incr=1;
echo "datatype is $datatype.........."
[ "$jxflg" = "Y" -a "$strdt" = "" ] && strdt=$lastyend && enddt=$lastmon;
datatype=$(echo $datatype | tr 'a-z' 'A-Z');
echo "datatype is $datatype"
if [ "$datatype" = "D" -o "$datatype" = "M" ]
then
if [ "$strdt" = "" -o "$enddt" = "" ]
then
echo "请输入开始时间和结束时间参数!"
call_usage;
exit;
fi;
ds_1=$strdt
de_1=$enddt
dtms="";
while [ $ds_1 -le $de_1 ]
do
if [ "$datatype" = "M" ]
then
ds_1=${ds_1:0:6}01
#echo ds_1 is $ds_1
ds_1=$(date -d "$ds_1 + $incr months " +"%Y%m01" )
ds_1=$(date -d "$ds_1 - $incr days " +"%Y%m%d" )
#echo "datatype is $datatype and ds_1 is $ds_1"
fi;
[ $ds_1 -le $de_1 ] && dtms="$dtms $ds_1"
ds_1=$(date -d "$ds_1 +$incr day " +"%Y%m%d");
done;
echo $dtms .....
fi;
#添加除了每个月末外的上周，昨天，今天
if [ "$jxflg" = "Y" -a "$strdt" = "$lastyend" -a "${lastday:0:4}" = "${lastyend:0:4}" ]
then
echo "开始算绩效了!!!" | tee -a $log;
dtms="$dtms $lastwek $lastday $dt";
fi;
if [ "$procs" = "" -a "$strdt" = "$lastyend" ]
then
#0.1分成关系重跑过程
aro=( PC_GENEMPSPLTRLT )
call_p "$aro" ;
aro=( PC_EMPMSTKPI_DEPYS )
call_p "$aro" ;
fi;
echo "新月日期参数为 dtms is :$dtms" | tee -a $log;
#2.跑入其它对公存储过程
aro=" PC_EMPWMSTKPI PC_EMPRMSTKPI PC_EMPMSTKPI_U PC_ACCRSKSTC2Q PC_ACCINFO_SHZX PC_KHJLFZYWJS PC_ZLKH PC_KHJL_GM"
if [ "$procs" != "" ]
then
aro="$procs";
extendps="$extendp";
fi;
#备份表数组
art=( PLAT_EMPWMSTKPI PLAT_EMPRMSTKPI PLAT_EMPMSTKPI_U PLAT_ACCINFOSH )
call_p "$aro" "$dtms" "$extendps"
[ "$jxflg" == "Y" ] && echo "绩效已经算完了!!!" | tee -a $log;
#3.交通费，张军的营销费
exit;
if [ "$datadate" = "" -o "$autoflg" = "Y" ];then
datadate=$(date -d " -1 days" +"%Y%m%d")
strdt=$datadate
enddt=$datadate
else
strdt=$datadate
enddt=$datadate
fi;
if [ "$strdt" != "" -a "$enddt" = "" ];then
echo "enddt is null,please input then -e argument"
exit
fi;
while [ $strdt -le $enddt ]
do
echo strdate is $strdt enddate is $enddt
echo db2 -x "call pc_zlkh($strdt)" | tee -a $log;
d=0
i=1
while [ $d -le 5 -a $i -lt 10 ]
do
if [ $i -gt 1 ];then
i=$((i+1))
sleep 300
fi
db2 connect to zxjxdbs>$pub_nul;
db2 "export ${ard[i]} to ${art[i]}$dtime.del of del select * from ${ard[i]}$dtime where datadate=$strdt" | tee -a $log;
dts=$(date +"%Y%m%d%H%M%S")
db2 -x "call pc_zlkh($strdt)" >>$log;
dte=$(date +"%Y%m%d%H%M%S")
d=$((dte-dts))
echo db2 -x "call pc_zlkh($strdt)" 运行第 $i 次 | tee -a $log;
echo "pc_zlkh($strdt)运行时长为:$((d/3600))小时$((d/60))分$((d%60))秒" | tee -a $log;
db2 connect reset>$pub_nul;
db2 terminate>$pub_nul;
done
strdt=$(date -d "$strdt +1 days" +"%Y%m%d")
done
