完成03

% 20160825
% 得到母基金的股票和股指期货持仓的交易日收益率
%计算母基金equity 下 PN,PH的占比计算，分别求得相应的日占比。
clear;clc
tic
load('Rate.mat')

%% 母基金名称
ProName=['("母基金二期")'];%['("母基金二期","母基金三期"，"母基金四期")']


%% 连接数据库
conn=database('report','uxmc','uxmc-123', 'com.mysql.jdbc.Driver',...
    'jdbc:mysql://report001.mysql.rds.aliyuncs.com/report');
ping(conn);


%% 提取母基金代码
sql=sprintf('select productCode from product where productName in %s order by productCode',ProName);
cursorA=exec(conn,sql);
cursorA=fetch(cursorA);
FOFCode=cursorA.Data;
close(cursorA);


%% 提取母基金的equity值
foc= FOFCode{1,1}; %母基金名称
Index=['tradingday,productCode,equity'];
%sql=sprintf('select %s from account where productCode = %d',Index,foc);
sql=sprintf('select %s from account where productCode = %s order by tradingDay',Index,foc);
cursorA=exec(conn,sql);
cursorA=fetch(cursorA);
FOFequity=cursorA.Data;
close(cursorA);




%% 提取母基金所有股票和期货信息
FundCodeValue=eval(FOFCode{1});%cell转数值为母基金的代码    
Index=['tradingDay,instType,instrumentCode,productCode,marketValue,direction'];
sql=sprintf('select %s from positions_history where left(productCode,5)=%d order by tradingDay',Index,FundCodeValue);
cursorA=exec(conn,sql);
cursorA=fetch(cursorA);
allInst=cursorA.Data;
close(cursorA);


%% 保留‘600’、‘601’、‘603’、‘000’、‘002’和‘300’打头的股票部分
instType=allInst(:,2);
instrumentCodeAll=allInst(:,3);
x=find(strncmp(instrumentCodeAll,'600',3)|strncmp(instrumentCodeAll,'601',3)...
       |strncmp(instrumentCodeAll,'603',3)|strncmp(instrumentCodeAll,'000',3)...
       |strncmp(instrumentCodeAll,'002',3)|strncmp(instrumentCodeAll,'300',3)...
       |strcmp(instType,'期货')); %这个是找到x的位置数值
allInst=allInst(x,:);  


%% 排除商品期货部分
instType=allInst(:,2);
instrumentCodeAll=allInst(:,3);
x=find(strncmp(instrumentCodeAll,'IC',2)|strncmp(instrumentCodeAll,'IF',2)...
              |strncmp(instrumentCodeAll,'IH',2)|strcmp(instType,'股票'));
allInst=allInst(x,:);  
allInst(:,3)=strrep(allInst(:,3),'CFFEX','CFE');%'CFFEX'替换为'CFE'，便于wind查询


%% 提取股票、股指期货的日收益率
instrumentCodeUni=unique(allInst(:,3));  %存储所有不重复的股票代码
for i=1:1:length(allInst(:,1))
    dayAll{i,1}=allInst{i};              % 日期在第一列
end
dayUni=unique(dayAll);


%% 以上已经将母基金包含的信息全部得到了，下面先求子基金代码，
FundCodeValue=eval(FOFCode{1});%cell转数值为母基金的代码 ,数值转换  
Index=['productCode,accountType,parentProductCode'];
sql=sprintf('select %s from product where parentProductCode=%d ',Index,FundCodeValue);
cursorA=exec(conn,sql);
cursorA=fetch(cursorA);
Fundname=cursorA.Data;
close(cursorA);

%% 子基金运算  提取子基金的equity值
Fund = Fundname{1,1}; %子基金名称，这里仅仅是一个子基金，改行不改列
Index=['tradingday,productCode,equity'];
sql=sprintf('select %s from account where productCode = %s order by tradingDay',Index,Fund);
cursorA=exec(conn,sql);
cursorA=fetch(cursorA);
Fundequity=cursorA.Data;
close(cursorA);


%%
%当a10 为 1 时候，基金没有数据，当不等于1时有数据
a10 = length(Fundequity);
%for j=1:a10
    j=2;
    xday=Fundequity{j,1};
    Fundday = Fundequity(:,1);
    xf = find(strcmp(Fundday,xday));
    Funddayinfo=Fundequity(xf,:);    %xf 就是占位数目，同收益率 有关
    Fname = allInst(:,4);            %子基金
    xa=find(strncmp(Fname,Fund,10)); %某个子基金下的某日的股票和期货
    if length(xa)~=0 %判断是否有股票 期货
        alldayinfo=allInst(xa,:);   %可能里面有重复的
        eday = alldayinfo(:,1);     %全部日期
        xb=find(strcmp(eday,xday));
        onedayinfo02=alldayinfo(xb,:);
        %判断股票和期货是否存在
        atype = '股票';
        ctype = onedayinfo02(:,2);
        xc=find(strcmp(ctype,atype));
        if length(xc)~=0           
            onedaystock=onedayinfo02(xc,:); %利用这个onedaystock 找到所有当日收益率，列值
            aa= cell2mat(onedaystock(:,5));%xday  从rateTable 可以找到相应的收益率，然后同股票的相乘
            %kt =1;%第一只股票
            TsRate = 0; %当天的多股票累计收益率
            for kt=1:length(aa)
                yd= find(strcmp(instrumentCodeUni,onedaystock{kt,3}));
                xd= find(strcmp(dayUni,xday));
                sRate=rateTable(xd,yd); %ook
                if sRate
                    %dayStockValue= sum(aa(:,1)); %股票就全加一下子，基金判断一下再加
                    TsRate=TsRate+sRate*onedaystock{kt,5}/Funddayinfo{1,3};
                end
            end
            dayStockValue= sum(aa(:,1)); %股票就全加一下子，基金判断一下再加
            
        end
    end