为了防止乱码,mutt配置：
[oracle@dbreport jifen]$ vi ~oracle/.muttrc

set send_charset="gb2312"
set assumed_charset="utf-8:gb2312:gb18030:gbk
set rfc2047_parameters=yes
set envelope_from=yes
set use_from=yes
set from=XXXX@7500.com.cn



[oracle@dbreport jifen]$ vi jifen.sh

#!/bin/bash

. /home/oracle/.bash_profile
export PATH=/usr/local/bin:$PATH
dir_path=`dirname $0`
cd ${dir_path}

sender=XXXXXX@7500.com.cn
receivers=`cat user_list.conf  | egrep -v '(^[:space:])*#'`

now=`date +%Y%m%d`
begin="`date -d -7days +%Y-%m-%d` 00:00:00"
end="`date +%Y-%m-%d` 00:00:00"
attachment="attachments/jifen_${now}.csv"
short_name=`echo $attachment| cut -d"/"  -f2`
sqlplus -s  product/g90#bk24f@CENTER-WXXRDB @sp.sql $attachment "$begin" "$end"

sed  -i  "s/'.*'/'$short_name'/g" content.txt
sed -i "s/[^【]*--[^】]*/${begin}--${end}/g" content.txt
mutt  $receivers -c $sender -s "吕梁移动积分统计表" -a $attachment  < content.txt
~

[oracle@dbreport jifen]$ vi sp.sql

set heading off
set feedback off
set term off
set pagesize 0
set linesize 200
set echo off
set trimspool on
set verify off
spool &1
@jifen.sql '&2' '&3'
spool off
exit
~

select '服务厅名' || ', ' ||  '人名' || ',' ||'号码' || ',' ||'本周推荐积分' from dual;
truncate table ep_jifen ;

insert into   ep_jifen
with ep_tmp as (
        select h.HALL_NAME,  h.STAFF_NAME , h.MOBILE ,w.business_id ,count(*) as total
        from ep_business_hall_lvliang h  ,EP_WORK_LOG  w
        where h.mobile =w.staff_id
        and w.created_date between to_date('&1', 'yyyy-mm-dd hh24:mi:ss')  and to_date('&2', 'yyyy-mm-dd hh24:mi:ss')
        group by h.HALL_NAME,h.STAFF_NAME,h.MOBILE,w.business_id order by w.business_id
        )
         select et.hall_name, et.staff_name, et.mobile , sum( et.total * er.integral) as jifen
        from ep_tmp et , EP_INTEGRAL_RULES  er where et.business_id = er.id and er.id= 4050
        group by  et.hall_name  ,et.staff_name,et.mobile ;

select hall_name || ',' ||  staff_name || ',' ||  mobile || ',' ||  jifen
from (
select eh.hall_name, eh.staff_name, eh.mobile, nvl(ej.jifen, 0) as jifen from ep_business_hall_lvliang  eh  ,ep_jifen ej where eh.mobile = ej.mobile(+) and eh.staff_name = ej.STAFF_NAME(+)
order by hall_name ,  jifen desc
) ;

[oracle@dbreport jifen]$ vi user_list.conf
XXX.XXX@163.com
YYYY.YYYY@163.com

[oracle@dbreport jifen]$ cat content.txt
Mr or Mrs:

        吕梁移动积分统计表在【附件】 'jifen_20130417.csv' 中，  统计时间段【2013-04-10 00:00:00--2013-04-17 00:00:00】。请下载！




        注释: (1) 业务员：李小霞录入数据库的个人信息不全.
              (2)李丽霞（13753892500），张解萍（1375389200)手机号重复，请确认信息去重，否则会出现打两者积分相同的现象.