sp_pv_channel_portfolio_product

存储过程：

DELIMITER $$

USE `channel_test`$$


DROP PROCEDURE IF EXISTS `sp_pv_channel_portfolio_product`$$


CREATE DEFINER=`root`@`%` PROCEDURE `sp_pv_channel_portfolio_product`(IN `p_pagenum` INT, IN `p_pagesize` INT, IN `p_condition` VARCHAR(200))
    COMMENT '产品统计'
BEGIN
    DECLARE v_cdt_count INT DEFAULT 0;
    DECLARE v_pre_portfolio_date VARCHAR(10) DEFAULT NULL;
    DECLARE v_market_type VARCHAR(40) DEFAULT NULL; 
    DECLARE v_keyword VARCHAR(40) DEFAULT NULL; 
    DECLARE v_year VARCHAR(40) DEFAULT NULL; 
    DECLARE v_fund_id VARCHAR(40) DEFAULT NULL; 
    
    DECLARE v_market_type_sql VARCHAR(100)  DEFAULT NULL;
    DECLARE v_keyword_sql VARCHAR(100)  DEFAULT NULL;
    DECLARE v_year_sql VARCHAR(100)  DEFAULT NULL;
    DECLARE v_pre_year_sql VARCHAR(100)  DEFAULT NULL;
    DECLARE v_fund_id_sql VARCHAR(100) DEFAULT NULL;    
    
    DECLARE v_sql VARCHAR(10000) DEFAULT NULL;
    
    DECLARE v_sort_name VARCHAR(40) DEFAULT NULL;
    DECLARE v_sort_asc VARCHAR(40) DEFAULT NULL;
    DECLARE v_sort_column_sql VARCHAR(100) DEFAULT NULL; 
    
    DECLARE i INTEGER ;
    SELECT LENGTH(p_condition)- LENGTH(REPLACE(p_condition,';','')) +1 INTO v_cdt_count;
    
    SET i=1;
    WHILE i<= v_cdt_count DO
    BEGIN       
       IF((SELECT INSTR(func_get_split_string(p_condition,';',i),'market_type'))>=1) THEN
          SELECT func_get_split_string(p_condition,';',i) INTO v_market_type;
       END IF; 
     
       IF((SELECT INSTR(func_get_split_string(p_condition,';',i),'keyword'))>=1) THEN
          SELECT func_get_split_string(p_condition,';',i) INTO v_keyword;
       END IF; 
       
       IF((SELECT INSTR(func_get_split_string(p_condition,';',i),'fund_id'))>=1) THEN
          SELECT func_get_split_string(p_condition,';',i) INTO v_fund_id;
       END IF; 
       
       IF((SELECT INSTR(func_get_split_string(p_condition,';',i),'inception_year'))>=1) THEN
          SELECT func_get_split_string(p_condition,';',i) INTO v_year;
       END IF; 
       
       IF((SELECT INSTR(func_get_split_string(p_condition,';',i) ,'sort_name'))>=1) THEN
          SELECT func_get_split_string(p_condition,';',i) INTO v_sort_name;
       END IF;
       
       IF((SELECT INSTR(func_get_split_string(p_condition,';',i) ,'sort_asc'))>=1) THEN
          SELECT func_get_split_string(p_condition,';',i) INTO v_sort_asc;
       END IF;
       SET i=i+1;
    END;
    END WHILE;
    
    
    SELECT CASE WHEN UPPER(func_get_split_string(v_market_type,':',2))<>'ALL' AND func_get_split_string(v_market_type,':',2) IS NOT NULL THEN CONCAT(" AND t.market_type='",func_get_split_string(v_market_type,':',2),"'") ELSE '' END INTO v_market_type_sql;
    
    
    SELECT CASE WHEN UPPER(func_get_split_string(v_fund_id,':',2))<>'ALL' AND func_get_split_string(v_fund_id,':',2) IS NOT NULL THEN CONCAT(" AND t.fund_id='",func_get_split_string(v_fund_id,':',2),"'") ELSE '' END INTO v_fund_id_sql;
  
    
    SELECT CASE WHEN UPPER(func_get_split_string(v_year,':',2))<>'ALL' AND func_get_split_string(v_year,':',2) IS NOT NULL THEN CONCAT(" AND t.portfolio_date='",func_get_split_string(v_year,':',2),"'") ELSE '' END INTO v_year_sql;
    
    
    SELECT LAST_DAY(DATE(CONCAT(YEAR(DATE_SUB(func_get_split_string(v_year,':',2),INTERVAL 1  QUARTER)),'-',ELT(QUARTER(DATE_SUB(func_get_split_string(v_year,':',2),INTERVAL 1  QUARTER)),3,6,9,12),'-',1))) INTO v_pre_portfolio_date;
     
    SELECT CASE WHEN v_pre_portfolio_date<>'' AND v_pre_portfolio_date IS NOT NULL THEN CONCAT(" AND t.portfolio_date='",v_pre_portfolio_date,"'") ELSE '' END INTO v_pre_year_sql;   
   # SELECT CONCAT(" AND portfolio_date='",v_pre_portfolio_date,"'") INTO v_pre_year_sql;
    
  
    
    
    SELECT CASE WHEN UPPER(func_get_split_string(v_keyword,':',2))<>'ALL'  AND func_get_split_string(v_keyword,':',2) IS NOT NULL THEN CONCAT(" and t.fund_name LIKE '%",func_get_split_string(v_keyword,':',2),"%' ") ELSE '' END INTO v_keyword_sql;
   
    
    SELECT CASE WHEN UPPER(func_get_split_string(v_sort_name,':',2))<>'ALL' AND func_get_split_string(v_sort_name,':',2) IS NOT NULL THEN CONCAT(" order by ",func_get_split_string(v_sort_name,':',2),' ',IFNULL(func_get_split_string(v_sort_asc,':',2),'asc')) ELSE '' END INTO v_sort_column_sql;
    
    SET v_sql=CONCAT("SELECT res.* 
                      FROM(
                        SELECT @i:=@i+1 id,tt.*
                            FROM (SELECT distinct a.fund_id,  
       a.`fund_short_name`,
       a.`advisor_id`,
       a.`company_short_name`,
       `get_managerIds_by_fundid`(a.fund_id) AS manager_ids,
       `get_manager_by_fundid`(a.fund_id) AS manager_names,
       a.`portfolio_date`,  #zhong cang日期
       a.curre_cnts, #benqi zhongcanggu 个数
       c.pre_cnts, #shangqi zhongcanggu 个数       
       b.sec_code, #benqi  chigu bili zuida gupiao
       b.stock_name, #
       b.market_type as cuur_sec_market_type, 
       b.holding_num,  
       b.holding_ratio, 
       e.stock_industry_id AS curr_max_industry_type, 
       e.stock_industry_type AS curr_max_industry_name,
       f.stock_industry_id AS pre_max_industry_type, 
       f.stock_industry_type AS pre_max_industry_type_name
FROM 
(
SELECT t.`fund_id`, 
       t.`fund_short_name`,
       t.`advisor_id`,
       t.`company_short_name`,
       t.`portfolio_date`,
       COUNT(t.sec_code) AS curre_cnts       
FROM fund_portfolio_product_dtl t
left join `rz_hfdb_core`.`fund_attributes` as fa 
on fa.fund_id = t.fund_id
and fa.isvalid=1
WHERE t.`sec_type` =1 
and t.isvalid=1
# and t.fund_type not in(9,11,17)
and t.fund_type<>3 
and fa.tot_sign=0 
#AND t.`portfolio_date`='2013-03-31'  
#AND t.`market_type`=1 
", v_year_sql,v_market_type_sql,v_keyword_sql,v_fund_id_sql,"
GROUP BY t.`fund_id`,
       t.`fund_short_name`,
       t.`company_name`,
       t.`company_short_name`,
       t.`portfolio_date`
) a 
LEFT JOIN (
SELECT t.`fund_id`, 
       t.`portfolio_date`,
       COUNT(t.sec_code) AS pre_cnts    
FROM fund_portfolio_product_dtl t
left join `rz_hfdb_core`.`fund_attributes` as fa 
on fa.fund_id = t.fund_id
and fa.isvalid=1
WHERE t.`sec_type` =1 
and t.isvalid=1
# and t.fund_type not in(9,11,17)
and t.fund_type<>3 
and fa.tot_sign=0 -
#AND t.`portfolio_date`='2012-12-31'  
#AND t.`market_type`=1  
", v_pre_year_sql,v_market_type_sql,v_keyword_sql,v_fund_id_sql,"
GROUP BY t.`fund_id`,
       t.`portfolio_date`
) c
ON a.fund_id=c.fund_id
LEFT JOIN
     ( #本期比例最大
       SELECT heyf_tmp.portfolio_date,
       heyf_tmp.fund_id,       
       heyf_tmp.sec_code, 
       heyf_tmp.stock_name,
       heyf_tmp.market_type,
       heyf_tmp.holding_num,   
       heyf_tmp.holding_ratio,
       @rownum:=@rownum+1 ,
       IF(@pdept=heyf_tmp.fund_id,@rank:=@rank+1,@rank:=1) AS rank,
       @pdept:=heyf_tmp.fund_id
       FROM (SELECT t.`portfolio_date`,
              t.`fund_id`,              
              t.`sec_code`,
              t.`stock_name`,
              t.`market_type`,
              t.`holding_num`,
              t.`holding_ratio`
       FROM fund_portfolio_product_dtl t
       LEFT JOIN `rz_hfdb_core`.`fund_attributes` AS fa 
       ON fa.fund_id = t.fund_id
       AND fa.isvalid=1
       WHERE t.`sec_type`=1 
       AND t.isvalid=1
       # and t.fund_type not in(9,11,17)
       AND t.fund_type<>3 
       AND fa.tot_sign=0 
       #AND t.`portfolio_date`='2013-03-31'  
       #AND t.`market_type`=1  
       ",v_year_sql,v_market_type_sql,v_keyword_sql,v_fund_id_sql,"
       ORDER BY t.`portfolio_date`, t.`fund_id`, t.`holding_ratio`, t.`holding_num` DESC
       )heyf_tmp ,
            (SELECT @rownum :=0 , @pdept := NULL ,@rank:=0) a             
     ) b
     ON a.`fund_id`=b.fund_id
     AND a.`portfolio_date`=b.portfolio_date  
     AND b.rank =1
LEFT JOIN( #按行业排名
            SELECT heyf_tmp.portfolio_date,  
                   heyf_tmp.fund_id,      
                   heyf_tmp.`stock_industry_id`,
                   heyf_tmp.`stock_industry_type`,
                   heyf_tmp.industry_type_cnts,   
                   @rownum:=@rownum+1 ,
                   IF(@pdept=heyf_tmp.fund_id,@rank:=@rank+1,@rank:=1) AS rank,
                   @pdept:=heyf_tmp.fund_id
             FROM   (SELECT t.`portfolio_date`,
                 t.`fund_id`,
                 t.`stock_industry_id`,
                 t.`stock_industry_type`,
                 t.`industry_type_cnts`
            FROM  `pv_fund_portfolio_industry_type` t
            left join `rz_hfdb_core`.`fund_attributes` as fa 
            on fa.fund_id =t.fund_id
            and fa.isvalid=1
            WHERE 1=1
            and fa.tot_sign=0 -
            #and t.`portfolio_date`='2013-03-31' 
            ",v_year_sql,"
           ORDER BY t.portfolio_date,t.`fund_id`,t.`industry_type_cnts` DESC
            )heyf_tmp ,
            (SELECT @rownum :=0 , @pdept := NULL ,@rank:=0) a  
          )e    
ON a.fund_id=e.fund_id
AND a.portfolio_date=e.portfolio_date
AND e.rank=1
LEFT JOIN( 
            SELECT heyf_tmp.portfolio_date,  
                   heyf_tmp.fund_id,      
                   heyf_tmp.`stock_industry_id`,
                   heyf_tmp.`stock_industry_type`,
                   heyf_tmp.industry_type_cnts,   
                   @rownum:=@rownum+1 ,
                   IF(@pdept=heyf_tmp.fund_id,@rank:=@rank+1,@rank:=1) AS rank,
                   @pdept:=heyf_tmp.fund_id
             FROM   (SELECT t.`portfolio_date`,
                 t.`fund_id`,
                 t.`stock_industry_id`,
                 t.`stock_industry_type`,
                 t.`industry_type_cnts`
            FROM  `pv_fund_portfolio_industry_type` t
            left join `rz_hfdb_core`.`fund_attributes` as fa 
            on fa.fund_id = t.fund_id
            and fa.isvalid=1
            WHERE 1=1
            and fa.tot_sign=0 
            #and t.`portfolio_date`='2012-12-31' 
             ",v_pre_year_sql,"
           ORDER BY t.portfolio_date,t.`fund_id`,t.`industry_type_cnts` DESC
            )heyf_tmp ,
            (SELECT @rownum :=0 , @pdept := NULL ,@rank:=0) a  
          )f    
ON a.fund_id=f.fund_id
AND f.rank=1 ",v_sort_column_sql,"
 ) tt,
                                 (SELECT @i:=0) ee
                           )res 
                      WHERE id>(",p_pagenum,"-1)*",p_pagesize," AND id<=(",p_pagenum,"*",p_pagesize,")"
                     );    
#TRUNCATE TABLE `temp_fupan_test`;
#INSERT INTO `temp_fupan_test` VALUES('v_sql',v_sql);                        
SET @SQL = v_sql;
  PREPARE sl FROM @SQL;
  EXECUTE sl;
  DEALLOCATE PREPARE sl;
    END$$


DELIMITER ;