运维开发网

linux每日业务订购数据统计

运维开发网 https://www.qedev.com 2020-03-27 10:37 出处:51CTO 作者:benbest011
关键词:sql提数、源数据统计、html格式化、邮件发送

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>

<h1>$1</h1>"

}

function create_html_head1(){

echo -e "<p class=MsoNormal align=left style='text-align:left;layout-grid-mode:char'>

<a name="_Toc31598"><b><span lang=EN-US style='font-size:20.0pt;font-family:黑体'>$1</span></b></a>

</p>"

}

function create_title(){

echo -e "<p class=MsoNormal align=left style='text-align:left;layout-grid-mode:char'>

<a name="_Toc31598"><b><span lang=EN-US style='font-size:16.0pt;font-family:华文细黑'>$1</span></b></a>

</p>"

}

function create_subtitle1(){

echo -e "<p class=MsoNormal align=left style='text-align:left;layout-grid-mode:char'>

<b><span style='font-size:14.0pt;font-family:华文细黑'>  $1</span></b>

</p>"

}

function create_subtitle10(){

echo -e "<p class=MsoNormal align=left style='text-align:left;layout-grid-mode:char'>

<b><span>   $1</span></b>"

}

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&lt;=NF) {print "&lt;td&gt;"$i"&lt;/td&gt;";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='[email protected]'

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

0

精彩评论

暂无评论...
验证码 换一张
取 消