#!/bin/bash 

#######################################################33333]
##########
#  每天的9，10，11，12，14，15，16，17，18，21  
#  更新3个临时表完成it商城的接口数据更新 
#
#    

NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
ORACLE_HOME=/oracle/product/10.2.0
ORACLE_SID=v880
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
PATH=/usr/ccs/bin:/usr/bin:/usr/local/bin:/usr/sbin:/usr/ucb:/etc:$ORACLE_HOME/bin:.
export NLS_LANG
export ORACLE_HOME
export ORACLE_SID
export ORA_NLS33
export PATH

cd /data/oracle/crontab/app/


TIM=`/usr/bin/date +%H`

TAB_NAME_1=v_ent_product_min_1
TAB_NAME_2=v_ent_product_max_1
TAB_NAME_3=v_ent_product_1
TAB_NAME_4=v_ent_brand_product_1
TAB_NAME_5=v_ent_news_1
TAB_NAME_6=ent_tmp_user_brand_1
TAB_NAME_7=pdl_temp_product_pic_1

# 9,   11,   14,   16,   18    # TAB_NAME = 1 
#   10,   12,  ,15,  ,17,  ,21 # TAB_NAME = 2  
#
#  v_ent_news 
#  v_ent_product
#  v_ent_product_min 
#   
#  only  refresh  this three tables  
# 
# 

if [ $TIM -eq 9 -o $TIM -eq 11 -o $TIM -eq 14 -o $TIM -eq 16 -o $TIM -eq 18  ] 
then 
	TAB_NAME_1=v_ent_product_min_1
	TAB_NAME_2=v_ent_product_max_1
	TAB_NAME_3=v_ent_product_1
	TAB_NAME_4=v_ent_brand_product_1
	TAB_NAME_5=v_ent_news_1
	TAB_NAME_6=ent_tmp_user_brand_1
	TAB_NAME_7=pdl_temp_product_pic_1
else 
	TAB_NAME_1=v_ent_product_min_2
	TAB_NAME_2=v_ent_product_max_2
	TAB_NAME_3=v_ent_product_2
	TAB_NAME_4=v_ent_brand_product_2
	TAB_NAME_5=v_ent_news_2
	TAB_NAME_6=ent_tmp_user_brand_2
	TAB_NAME_7=pdl_temp_product_pic_2
fi 

echo `date +%Y%m%d%H%m  `

echo $TAB_NAME_1
echo $TAB_NAME_2
echo $TAB_NAME_3
echo $TAB_NAME_4
echo $TAB_NAME_5
echo $TAB_NAME_6
echo $TAB_NAME_7 


 
sqlplus "/ as sysdba" << EOF

SET TIMING ON ;

alter session set current_schema=cyp_nw_app; 

set timing on ;

--v_ent_product_min

prompt  $TAB_NAME_1  ; 
spool  $TAB_NAME_1 ; 

truncate table $TAB_NAME_1  ; 


drop table r_temp ;


create table r_temp as 
select /*+index r (IDX_ENT_PRODUCT_2)*/
  r.id,r.type_id,r.user_id,r.product_id,r.name,z.status,r.CREATION_DATE,
  z.LAST_UPDATE_DATE,z.PRODUCT_ORDER,z.HAVE_PRODUCT,r.TYPE,r.SMALL_IMG,
  r.PRODUCT_INTRO,z.RETAIL_PRICE,r.RETAIL_VALID_DATE,r.VALIDATION,r.REF_PRICE,
  r.CHANGE_RATE,z.PRICE_STATUS,r.ADUIT_BY,r.ADUIT_DATE,r.VERSION,r.PROVINCE,
  r.CITY,r.DISTRICT,z.ONLINE_FLAG,z.WARRANTY,r.TITLE,r.IMG,r.BRAND_NAME,z.SELL_STATUS,
  z.COUNT,z.START_DATE,z.END_DATE,z.PAYMENT,z.ADD_COUNT,z.LAST_UPDATE_BY,
  z.FIRST_ON_SELL_TIME,z.SOLD_COUNT,r.EDITOR_RECOMMEND,r.EDITOR_SMALL_RECOMMEND,
  z.LAST_RETAIL_PRICE,
  decode(r.editor_recommend+r.editor_small_recommend,0,0,1) as e_recommend
             from ent_product r, ent_product_item z
            where r.id=z.company_product_id
          and z.status <=1
          and z.retail_price>0
          and r.type_id is not null
          and sysdate between z.start_date and z.end_date; 
          
          
create index   idx1_r1 on r_temp( user_id,type_id, product_id) ;
analyze index idx1_r1 compute statistics ;



insert /*+append*/ into  $TAB_NAME_1
select t1.* from 
  r_temp t1
    , (select r1.user_id,r1.type_id,r1.product_id,r1.e_recommend,min(r1.online_flag) as online_flag from r_temp r1,
        (select user_id,type_id,product_id,max(e_recommend) as e_recommend
          from r_temp
          group by user_id,type_id, product_id) r2
where r1.user_id=r2.user_id
      and r1.type_id=r2.type_id
      and r1.product_id=r2.product_id
      and r1.e_recommend=r2.e_recommend
    group by r1.user_id,r1.type_id,r1.product_id,r1.e_recommend ) t2
    where t1.user_id=t2.user_id
      and t1.type_id=t2.type_id
      and t1.product_id=t2.product_id
      and t1.e_recommend=t2.e_recommend
      and t1.online_flag=t2.online_flag ;
      
commit; 


##### 确保下面的表数据的更新使用到最新的数据。

create or replace  synonym  cyp_nw_app.v_ent_product_min for  $TAB_NAME_1 ;

spool off ;      

--insert /*+append*/ into $TAB_NAME_1  
--	select t1.* from (
--  select r.*,decode(editor_recommend+editor_small_recommend,0,0,1) as e_recommend
--             from ent_product r
--            where r.status <=1
--          and r.retail_price>0
--          and r.type_id is not null
--          and sysdate between r.start_date and r.end_date) t1
--    , (select r1.user_id,r1.type_id,r1.product_id,r1.e_recommend,min(r1.online_flag) as online_flag from 
--      (select r.*,decode(editor_recommend+editor_small_recommend,0,0,1) as e_recommend
--             from ent_product r
--            where r.status <=1
--          and r.retail_price>0
--          and r.type_id is not null
--          and sysdate between r.start_date and r.end_date) r1,
--        (select user_id,type_id,product_id,max(e_recommend) as e_recommend
--          from (select  /*+index r (IDX_ENT_PRODUCT_2)*/ r.*,decode(editor_recommend+editor_small_recommend,0,0,1) as e_recommend
--             from ent_product r
--            where r.status <=1
--          and r.retail_price>0
--          and r.type_id is not null
--          and sysdate between r.start_date and r.end_date)
--          group by user_id,type_id, product_id) r2
--where r1.user_id=r2.user_id 
--      and r1.type_id=r2.type_id 
--      and r1.product_id=r2.product_id 
--      and r1.e_recommend=r2.e_recommend
--    group by r1.user_id,r1.type_id,r1.product_id,r1.e_recommend ) t2
--    where t1.user_id=t2.user_id 
--      and t1.type_id=t2.type_id 
--      and t1.product_id=t2.product_id 
--      and t1.e_recommend=t2.e_recommend 
--      and t1.online_flag=t2.online_flag ;  
--	


--v_ent_product

prompt  $TAB_NAME_3 ;
SPOOL  $TAB_NAME_3  ; 

truncate table  $TAB_NAME_3  ;


insert /*+append*/ into   $TAB_NAME_3  
	select t7.* from v_ent_product_min t7 ,
		(select t5.user_id,t5.type_id,t5.product_id,t5.e_recommend,t5.online_flag, t5.last_update_date,max(t5.id) as id 
		  from v_ent_product_min t5 ,
		(select t3.user_id,t3.type_id,t3.product_id,t3.e_recommend,t3.online_flag,
			 max(t3.last_update_date) as last_update_date 
		  from v_ent_product_min t3 
		    group by t3.user_id,t3.type_id,t3.product_id,t3.e_recommend,t3.online_flag
			) t6
	where t5.user_id=t6.user_id 
	and t5.type_id=t6.type_id 
	and t5.product_id=t6.product_id 
        and t5.e_recommend=t6.e_recommend 
	and t5.online_flag=t6.online_flag
	and t5.last_update_date=t6.last_update_date
	group by t5.user_id,t5.type_id,t5.product_id,t5.e_recommend,t5.online_flag,
		t5.last_update_date) t8
	where t7.id=t8.id;  

commit; 

delete from $TAB_NAME_3  r_temp where exists (select 1 from  pdl_product t2 where r_temp.product_id=t2.id and t2.type_id<>r_temp.type_id);

COMMIT; 

spool off; 


--v_ent_news

prompt $TAB_NAME_5 ;
spool  $TAB_NAME_5  ; 

truncate table  $TAB_NAME_5   ;

insert /*+append*/ into  $TAB_NAME_5   
	  SELECT t.*, m.new_update_date from ENT_NEWS t,
	    (SELECT MAX(c.id) AS id, c.user_id, c.small_id, c.brand_id, c.province, 
		c.city,c.new_update_date 
	    FROM ( SELECT a.id, a.user_id, a.small_id, a.brand_id, a.province, a.city, 
			a.last_update_date, 
			DECODE(b.news_id,NULL, a.last_update_date, sysdate) AS new_update_date
	   FROM ENT_NEWS a, ENT_NEWS_POSITION_AD b
          	WHERE a.status<=1 AND a.id = b.news_id(+)
          ) c,
          ( SELECT user_id, small_id, brand_id, province, city, 
		MAX(new_update_date) AS new_update_date
           FROM (SELECT a.id, a.user_id, a.small_id, a.brand_id, a.province, a.city,
		 a.last_update_date, 
			DECODE(b.news_id,NULL, a.last_update_date, sysdate) AS new_update_date
           FROM ENT_NEWS a, ENT_NEWS_POSITION_AD b
            WHERE a.status<=1 AND a.id = b.news_id(+)
          )  GROUP BY user_id, small_id, brand_id, province, city
        ) d
    WHERE c.user_id = d.user_id
      AND c.small_id = d.small_id
      AND c.brand_id = d.brand_id
      AND c.province = d.province
      AND c.city = d.city
      AND c.new_update_date = d.new_update_date  
    GROUP BY c.user_id, c.small_id, c.brand_id, c.province, c.city, c.new_update_date  ) m
     where t.id=m.id ;

COMMIT; 

SPOOL OFF; 


--- 挪到上面执行了，这里屏蔽
---create or replace  synonym  cyp_nw_app.v_ent_product_min for  $TAB_NAME_1 ; 


create or replace  synonym  cyp_nw_app.v_ent_product     for  $TAB_NAME_3 ; 

--- 产品库也要 引用 v_ent_product这条表啦，需要 处理下权限问题

grant select on cyp_nw_app.$TAB_NAME_3  to product_app; 

grant select on cyp_nw_app.$TAB_NAME_3  to product_pub; 

grant select on  cyp_nw_app.v_ent_product to product_app;

create or replace synonym  product_app.v_ent_product for cyp_nw_app.v_ent_product; 

grant select on  cyp_nw_app.v_ent_product to product_pub;

create or replace synonym  product_pub.v_ent_product for cyp_nw_app.v_ent_product; 



create or replace  synonym  cyp_nw_app.v_ent_news          for  $TAB_NAME_5 ; 



EOF