需求:导出数据库中数据,生成csv文件(定时任务去做)
入口:扫表操作
扫表的sql:(polljob.sql)
selectoid,jobId,batchCode,COALESCE(batchStartTime,'2000-01-01'),COALESCE(batchEndTime,'2030-01-01'),jobStatus,COALESCE(offsetOid,'0')
fromT_MONEY_JOBWHEREworkerPidisnullandjobStatusin('toRun');
根据扫描出的内容,做具体的操作(jobpoller.sh):
#!/bin/bash
basedir=`dirname$`
cd$basedir
whileIFS=$'\t'readoid jobId batchCode batchStartTime batchEndTime jobStatus offsetOid productOid
doargs=("$jobId""$oid""$batchCode""$batchStartTime""$batchEndTime""$jobStatus""$offsetOid""$productOid")script=csvgen.shsh$script"$"
done
进入到csvgen.sh脚本:
#!/bin/sh
key=$1
condition=$2
filetype=$key
basedir=`dirname$`
confirmDate=$(date +"%Y-%m-%d"-d'-1 day')
startTime="$00:00:00"
endTime="$23:59:59"
[$basedir=='.'] && basedir=$PWD
datadir=$basedir/$key/data
logsdir=$basedir/$key/logs
# remove old files within one same minute
oldDate=$(date-d"last month""+%Y%m%d")
if[-d$datadir/$oldDate];then
rm -rf$datadir/$oldDate
echo"完成删除动作"
if[-d$datadir/$oldDate];then
echo"删除失败"
else
echo"删除成功"fi
else
echo"目录不存在"
fi
[-d$datadir] || mkdir -p$datadir
[-d$logsdir] || mkdir -p$logsdir
today=`date +%Y%m%d`
fileDate=`date +%Y-%m-%d`
batch="`date +%Y%m%d%H%M00`"
datadir=$datadir/$today/$batch
[-d$datadir] || mkdir -p$datadir
#filepre=$datadir/customer_$key_
shift
#args: jobId,batchCode,batchStart,batchEnd,jobStatus,offsetOid,productOid
jobId=$1
batchCode=$2
batchStart=$3
batchEnd=$4
jobStatus=$5
offsetOid=$6
productOid=$7
["x$offsetOid"="x"] && offsetOid=$(date +%s)
myfiles=("customer_order_")
["$key"="income"] && myfiles=("customer_income_")
["$key"="order"] && myfiles=("customer_order_")
["$key"="hold"] && myfiles=("customer_hold_")
forxin$
do
#hold
if["$key"="hold"];then
#csv
holdSQLfile="hold_$confirmDate.sql"
holdCSVfile="$$_0.csv"
notifyContent={'"'fileType'"':'"'hold'"','"'fileName'"':'"'$holdCSVfile'"','"'fileDate'"':'"'$fileDate'"','"'filePath'"':'"'$datadir'/"'}
sed-e"s/#startTime/$startTime/g"-e"s/#endTime/$endTime/g"$basedir/hold.temp.sql >$datadir/$holdSQLfile
$mysqlcli-N -r $datadir/$holdCSVfile
cat$datadir/$holdSQLfile
echo"======================================="
#log
holdlogname="$$_0.log"
holdlogfile=$logsdir/$holdlogname
echo$(date) start >>$holdlogfile
echo"confirmDate=$, startTime=$, endTime=$">>$holdlogfile
echo$(date) end >>$holdlogfile
#income
elif["$key"="income"];then
#csv
incomeSQLfile="income_$confirmDate.sql"incomeCSVfile="$$_0.csv"
notifyContent={'"'fileType'"':'"'income'"','"'fileName'"':'"'$incomeCSVfile'"','"'fileDate'"':'"'$fileDate'"','"'filePath'"':'"'$datadir'/"'}
sed-e"s/#startTime/$startTime/g"-e"s/#endTime/$endTime/g"$basedir/income.temp.sql >$datadir/$incomeSQLfile
$mysqlcli-N -r $datadir/$incomeCSVfile
cat$datadir/$incomeSQLfile
echo$fileDate+"fileDate"
echo"======================================="
#log
incomelogname="$$_0.log"
incomelogfile=$logsdir/$incomelogname
echo$(date) start >>$incomelogfile
echo"confirmDate=$, startTime=$, endTime=$">>$incomelogfile
echo$(date) end >>$incomelogfile
elif["$key"="order"];then
#csv
orderSQLfile="order_$confirmDate.sql"
orderCSVfile="$$_0.csv"
notifyContent={'"'fileType'"':'"'order'"','"'fileName'"':'"'$orderCSVfile'"','"'fileDate'"':'"'$fileDate'"','"'filePath'"':'"'$datadir'/"'}
sed-e"s/#offsetOid/$offsetOid/g"$basedir/order.temp.sql >$datadir/$orderSQLfile
$mysqlcli-N -r $datadir/$orderCSVfile
cat$datadir/$orderSQLfile
echo"======================================="
#log
orderlogname="$$_0.log"
orderlogfile=$logsdir/$orderlogname
echo$(date) start >>$orderlogfile
echo"confirmDate=$, startTime=$, endTime=$">>$orderlogfile
echo$(date) end >>$orderlogfile
else
echo"no command execute"
fi
#lock table
sql="update t_money_job set jobStatus='finished' where oid='$condition' "$mysqlcli-e"$sql"
#save notify
notifyOiduuid=`cat /proc/sys/kernel/random/uuid`
notifyoid=`echo$notifyOiduuid| sed's/-//g'`
notifyIduuid=`cat /proc/sys/kernel/random/uuid`
notifyId=`echo$notifyIduuid| sed's/-//g'`
notifyDate=`date"+%Y-%m-%d %H:%M:%S"`
sqlnotify="insert into t_money_platform_notify(oid,notifyId,notifyType,notifyContent,errorCode,notifyStatus,notifyTimes,seqId,updateTime,createTime) values('$notifyoid','$notifyId','documentExport','$notifyContent','0','toConfirm','0','0','$notifyDate','$notifyDate')"
$mysqlcli-e"$sqlnotify"
done
echo"finished!!"
根据不同的条件去执行不同的sql:
SELECT'productOid, investorOid,totalVolume,holdVolume,toConfirmInvestVolume,toConfirmRedeemVolume,redeemableHoldVolume,lockRedeemHoldVolume,expGoldVolume,totalInvestVolume,accruableHoldVolume,value,holdTotalIncome, totalBaseIncome,totalRewardIncome,holdYesterdayIncome,yesterdayBaseIncome,yesterdayRewardIncome,incomeAmount,redeemableIncome,lockIncome,confirmDate,expectIncome,expectIncomeExt,accountType,maxHoldVolume,dayRedeemVolume,dayInvestVolume,dayRedeemCount, productAlias, holdStatus, productType'
UNIONALL
SELECTCONCAT( t1.productOid,', ',
t1.investorOid,', ',
TRUNCATE(t1.totalVolume *100,),', ',
TRUNCATE(t1.holdVolume *100,),', ',
TRUNCATE(t1.toConfirmInvestVolume *100,),', ',
TRUNCATE(t1.toConfirmRedeemVolume *100,),', ',
TRUNCATE(t1.redeemableHoldVolume *100,),', ',
TRUNCATE(t1.lockRedeemHoldVolume *100,),', ',
TRUNCATE(t1.expGoldVolume *100,),', ',
TRUNCATE(t1.totalInvestVolume *100,),', ',
TRUNCATE(t1.accruableHoldVolume *100,),', ',
TRUNCATE(t1.value *100,),', ',
TRUNCATE(t1.holdTotalIncome *100,),', ',
TRUNCATE(t1.totalBaseIncome *100,),', ',
TRUNCATE(t1.totalRewardIncome *100,),', ',
TRUNCATE(t1.holdYesterdayIncome *100,),', ',
TRUNCATE(t1.yesterdayBaseIncome *100,),', ',
TRUNCATE(t1.yesterdayRewardIncome *100,),', ',
TRUNCATE(t1.incomeAmount *100,),', ',
TRUNCATE(t1.redeemableIncome *100,),', ',
TRUNCATE(t1.lockIncome *100,),', ',
IFNULL(t1.confirmDate,'(null)'),', ',
TRUNCATE(t1.expectIncome *100,),', ',
TRUNCATE(t1.expectIncomeExt *100,),', ',
t1.accountType,', ',
TRUNCATE(t1.maxHoldVolume *100,),', ',
TRUNCATE(t1.dayRedeemVolume *100,),', ',
TRUNCATE(t1.dayInvestVolume *100,),', ',
t1.dayRedeemCount,', ',
t1.productAlias,', ',
t1.holdStatus,', ',
t2.type)
FROM`t_money_publisher_hold`t1,`t_gam_product`t2WHEREt1.productOid=t2.oid
SELECT'productOid, investorOid, incomeAmount, confirmDate, beforeVolume, afterVolume,productType '
UNIONALL
SELECTCONCAT( t1.productOid,', ',
t1.investorOid,', ',
TRUNCATE(t1.incomeAmount *100,),', ',
t1.confirmDate,', ',
TRUNCATE(t1.accureVolume *100,),', ',
TRUNCATE((t1.accureVolume + t1.incomeAmount) *100,),', ', t2.type)
FROM`t_money_publisher_investor_holdincome`t1,`t_gam_product`t2WHEREt1.confirmDate >='#startTime'ANDt1.confirmDate
SELECT'investorOid,orderCode, productOid,orderType,orderTime,orderStatus,orderAmount,productType'
UNIONALL
SELECTCONCAT( t1.investorOid,', ',
t1.`orderCode`,', ',
t1.`productOid`,', ',
t1.`orderType`,', ',
t1.`orderTime`,', ',
t1.`orderStatus`,', ',
TRUNCATE(t1.`orderAmount`*100,),', ',
t2.type)
FROM`t_money_investor_tradeorder`t1,`t_gam_product`t2WHEREt1.publisherOffsetOid ='#offsetOid'ANDt1.productOid=t2.oid
mydb=ddyin
basedir=`dirname$`
[$basedir=='.'] && basedir=$PWD
mysqlcli="mysql --defaults-extra-file=$basedir/mysqlclient.conf -N -r$mydb"
cid=2
token=5EA77F0752E2EF36973EB64D8730CC6
(mysql)配置:
#!/bin/sh
db=ddyin
[$1] && db=$1
mysql --defaults-extra-file=mysqlclient.conf$db
(mysqlclient.conf)配置:
[client]host=1.1.1.1
user=1
password=1
default-character-set=utf8
全局配置文件:(crontab)-----定义定时任务
SHELL=/bin/bashPATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/local/redis/binMAILTO=root
HOME=/
*/5 * * * * root /bin/bash /customer/gencmd/jobpoller.sh
每五秒执行一次。
数据库配置已mock(非真实)
领取专属 10元无门槛券
私享最新 技术干货