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 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 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 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 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 "

$1

function create_html_head1(){
echo -e "

$1

function create_title(){
echo -e "

$1

function create_subtitle1(){
echo -e "

$1

function create_subtitle10(){
echo -e "

$1"
}

function create_table_head(){

echo -e "

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 "

function create_table_end(){
echo -e "

function create_html_end(){
echo -e ""
}

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