溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

linux每日業(yè)務(wù)訂購數(shù)據(jù)統(tǒng)計(jì)

發(fā)布時(shí)間：2020-07-21 20:20:13 來源：網(wǎng)絡(luò) 閱讀：113 作者：benbest011 欄目：系統(tǒng)運(yùn)維

linux每日業(yè)務(wù)訂購數(shù)據(jù)統(tǒng)計(jì)

關(guān)鍵點(diǎn)：sql提數(shù)、源數(shù)據(jù)統(tǒng)計(jì)、html格式化、郵件發(fā)送、
由于博客格式導(dǎo)致執(zhí)行符合不顯示

#!/bin/bash
#xxxx業(yè)務(wù)辦理匯總

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
#清空文件內(nèi)容（>不顯示）

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

dbuser="數(shù)據(jù)庫用戶名"
dbpwd="數(shù)據(jù)庫密碼"
dbserv="數(shù)據(jù)庫名"

#########################提數(shù)###################################
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格式函數(shù) ##################################
function create_html_head(){
echo -e "<html>
<body>
<h2>$1</h2>"
}

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:華文細(xì)黑'>$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:華文細(xì)黑'>??$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='xxxx@xx.cn'

NM=wc -l order.txt| awk '{print $1}'
NM=expr $NM + 1
echo "業(yè)務(wù)代碼 業(yè)務(wù)名稱 業(yè)務(wù)辦理總量 業(yè)務(wù)成功辦理總量 業(yè)務(wù)辦理用戶數(shù) 業(yè)務(wù)辦理成功用戶數(shù)" >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
#####################輸出內(nèi)容html郵件########################
create_html_head1 "各位好，${TM3}xx業(yè)務(wù)辦理情況如下：" >> $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

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手