linux每日业务订购数据统计

发布时间：2020-07-21 20:20:13 作者：benbest011
来源：网络阅读：113

linux每日业务订购数据统计

关键点：sql提数、源数据统计、html格式化、邮件发送、
由于博客格式导致执行符合不显示

#!/bin/bash
#xxxx业务办理汇总

source /etc/profile #sqlplus客户端变量配置好
export ORACLE_HOME=/home/middle/oracle12client
export SQLPATH=/home/middle/oracle12client
export TNS_ADMIN=/home/middle/oracle12client
export NLS_LANG="american_america.ZHS16GBK"
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME

TM1=date -d "-1days" +%Y%m%d
TM2=date +%Y%m%d
TM3=date -d "-1days" +%Y-%m-%d

cd /home/middle/script/yewu
#清空文件内容（>不显示）

order.txt
sucess_order.txt
user_order.txt
user_sucess_order.txt
jieguo.txt

dbuser="数据库用户名"
dbpwd="数据库密码"
dbserv="数据库名"

#########################提数###################################
sqlplus -s ${dbuser}/${dbpwd}@${dbserv} << EOF
set HEADING OFF;
SET LINESIZE 1000;
SET PAGESIZE 0;
SET VERIFY OFF;
SET ECHO OFF;
SET FEEDBACK OFF;
set serverout off;
set serveroutput off;
set termout off;
set trimout on;
spool order.txt
prompt PRODUCTID count;
select t.PRODUCTID,count(t.INSERTTIME) from mmportlog.PCA_ECOP_PRODUCTORDER t
where t.INSERTTIME >=to_date('$TM1','yyyymmdd')
and t.inserttime <to_date('$TM2','yyyymmdd')
and t.source = '1'
GROUP BY t.PRODUCTID;
spool off;
spool sucess_order.txt
prompt PRODUCTID count;
select t.PRODUCTID,count(t.INSERTTIME) from mmportlog.PCA_ECOP_PRODUCTORDER t
where t.INSERTTIME >=to_date('$TM1','yyyymmdd')
and t.inserttime <to_date('$TM2','yyyymmdd')
and t.source = '1'
and t.RETCODE = '0'
GROUP BY t.PRODUCTID ;
spool off;
spool user_order.txt
prompt PRODUCTID count;
select t.PRODUCTID,count(distinct(t.phone)) from mmportlog.PCA_ECOP_PRODUCTORDER t
where t.INSERTTIME >=to_date('$TM1','yyyymmdd')
and t.inserttime <to_date('$TM2','yyyymmdd')
and t.source = '1'
GROUP BY t.PRODUCTID;
spool off;
spool user_sucess_order.txt
prompt PRODUCTID count;
select t.PRODUCTID,count(distinct(t.phone)) from mmportlog.PCA_ECOP_PRODUCTORDER t
where t.INSERTTIME >=to_date('$TM1','yyyymmdd')
and t.inserttime <to_date('$TM2','yyyymmdd')
and t.source = '1'
and t.RETCODE = '0'
GROUP BY t.PRODUCTID ;
spool off;
exit;
EOF

find /home/middle/script/yewu/ -name qd.html | xargs rm -f
qd_output=/home/middle/script/yewu/qd.html

##################html格式函数 ##################################
function create_html_head(){
echo -e "<html>
<body>
<h2>$1</h2>"
}

function create_html_head1(){
echo -e "
<a name="_Toc31598">$1</a>
"
}

function create_title(){
echo -e "
<a name="_Toc31598">$1</a>
"
}

function create_subtitle1(){
echo -e "
 $1
"
}

function create_subtitle10(){
echo -e "
 $1"
}

function create_table_head(){

echo -e "<table border=\"1px\" bordercolor=\"#000000\" cellspacing=\"0px\" style=\"border-collapse:collapse\">"
}

function create_td(){
echo $1
td_str=echo $1 | awk 'BEGIN{FS=" "}''{i=1; while(i<=NF) {print "<td>"$i"</td>";i++}}'
echo $td_str
}

function create_tr(){
create_td "$1"
echo -e "<tr>
$td_str
</tr>" >> $qd_output
}

function create_table_end(){
echo -e "</table>"
}

function create_html_end(){
echo -e "</body></html>"
}

MAIL_LIST='xxxx@xx.cn'

NM=wc -l order.txt| awk '{print $1}'
NM=expr $NM + 1
echo "业务代码业务名称业务办理总量业务成功办理总量业务办理用户数业务办理成功用户数" >jieguo.txt

if [ $NM -ge 3 ];then
for I in awk '{print $1}' order.txt | grep -v PRODUCTID
do
MC=grep -w $I taocan.txt |awk '{print $2}'
if [ Unkonw_name$MC = "Unkonw_name" ]
then
MC=Unkonw_name
fi
ZL=grep $I order.txt |awk '{print $2}'
ZL=expr $ZL + 0
SZL=grep $I sucess_order.txt |awk '{print $2}'
SZL=expr $SZL + 0
UZL=grep $I user_order.txt |awk '{print $2}'
UZL=expr $UZL + 0
USZL=grep $I user_sucess_order.txt |awk '{print $2}'
USZL=expr $USZL + 0
echo "$I $MC $ZL $SZL $UZL $USZL" >>jieguo.txt
done
#####################输出内容html邮件########################
create_html_head1 "各位好，${TM3}xx业务办理情况如下：" >> $qd_output
create_table_head >> $qd_output
while read line
do
echo $line
create_tr "$line"
done < /home/middle/script/yewu/jieguo.txt
create_table_end >> $qd_output
cat qd.html |mutt -s "${TM3} xxxx办理汇总" -e 'set content_type="text/html"' $MAIL_LIST
else
echo "各位好，$TM3 xxxx办理总量为:0" |mutt -s "$TM3 xxxx办理总量为:0" $MAIL_LIST
fi

linux每日业务订购数据统计

相关阅读