oracle积分补差,积分_ITPUB博客

为了防止乱码,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]$ vijifen.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]$ visp.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)手机号重复，请确认信息去重，否则会出现打两者积分相同的现象.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27042095/viewspace-758697/，如需转载，请注明出处，否则将追究法律责任。