% Code created on 04/17/2008
% SE project - group 805

% Code to connect matlab and mysql database and provide values from mysql to matlab
mysql( 'open', 'localhost:3306', 'root', 'ateet' )
% Connecting to  host=localhost  port=3306  user=root  password=ateet
% Type of response expected
%Uptime: 2905  Threads: 1  Questions: 570  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 3  Queries per second avg: 0.196

days_back = 3;  
% We can give any day that we wish to test the performance of the system
% Professor may suggest a day 

mysql('use test_schema')
% Current database is "test_schema"

[Open, High, Low, Close, Volume] = mysql('select Open,High,Low,Close,Volume from organizationhistory order by date');
[ID] = mysql('select OrganizationID from organizationtable');
% provide ID open high low close volume into variables in matlab
% Note - problem with date conversion

Close = transpose(Close);
% converting the close variable from column vector to row vector

ID = transpose(ID);
% converting the ID values from column vector to row vector

lenp = length(ID);
 for xx = 1:lenp  
    
    [Close_act] = mysql(['select Close from organizationhistory where OrganizationID = ',num2str(ID(xx)),' order by Date']);
    
    Close_act = transpose(Close_act);
    
    L = length(Close_act);

% Now L is the number of days
% I consider a random permutation of days

% I consider 180 days for training
Ntrain = 180;

% I consider the remaining days for testing
Ntest  = L-(Ntrain);

N         = 10;  % length of input sequences
M         = 3;   % length of predicted sequences
buysignal = 1;   % Set this value to 0 if you want Sell Signals, 1 for Buy Signals

ordine = randperm(Ntrain-N-M);  % Randomising the values for training purpose

% Simulation parameters

%--------------------------------------------------------------------------
%----------------------------------------------------------------- TRAINING

% Memory allocation for training input sequences and target sequences
training_input  = zeros(N,Ntrain);
training_target = zeros(1,Ntrain);

% Auxiliary counter
cont = 1;
price_buy = 0;
price_sell = 0;
count_buy = 0;
count_sell = 0;

for ii=1:Ntrain-N-M
    % I consider a random day using the random permutation
    k     = ordine(ii);
    
    % Current day of trading
    myseq = Close_act(k:k+N-1);
    
    % Length of vector
    ls    = length(myseq);
    
    
        v     = myseq(1:N);
        v     = v(:);
        % Input sequence is added
        training_input(:,cont)  = v;
        vout                    = Close_act(k+N:k+N+M-1);
        
        maxb = max(vout);
        mins = min(vout);
        
        
        % Target is added
        
        if buysignal==1
            % myseq(ls) is current price (last price of input sequence)
            training_target(:,cont) = any(vout(:)>=myseq(N));
            if training_target(:,cont)== 1
                xb = maxb-myseq(N);
                price_buy = price_buy + xb;
                count_buy = count_buy + 1;
            elseif training_target(:,cont)== 0
                ys = myseq(N)-mins;
                price_sell = price_sell + ys;
                count_sell = count_sell + 1;
            end
            
            
        else
            % myseq(x1) is current price (last price of input sequence)
            training_target(:,cont) = any(vout(:)<=myseq(N));
            
            if training_target(:,cont)== 0
                xb = maxb-myseq(N)
                price_buy = price_buy + xb;
                count_buy = count_buy + 1;
            elseif training_target(:,cont)== 0
                ys = myseq(N)-mins
                price_sell = price_sell + ys;
                count_sell = count_sell + 1;
            end
        end
        % Counter is updated
        cont           = cont+1;
end


% 
% % Train neural networks
    [net]  = mytrainsystem(training_input,training_target,buysignal);
% %--------------------------------------------------------------------------
% %------------------------------------------------------------------ TESTING



testing_input  = zeros(N,Ntest);
testing_target = zeros(1,Ntest);
% % Auxiliary counter
cont = 1;
ordinenew = randperm(L-N-M);

tp1=1;
for i=1:L-N-M
  if (ordinenew(i) > Ntrain)
     final(tp1) = ordinenew(i);
     tp1=tp1+1;
  end
end


for ii = 1:Ntest-N-M
    % For testing I consider a random day using the random permutation
    % Note that there is no overlapp between training and testing days
    
    k     = final(ii);
    
    % Current day of trading
    myseq = Close_act(k:k+N-1);
    % Length of vector
    ls    = length(myseq);
    
        v     = myseq(1:N);
        v     = v(:);
        % Input sequence is added
        testing_input(:,cont)  = v;
        vout                   = Close_act(k+N:k+N+M-1);
        % Target is added
        
        if buysignal==1
            % myseq(ls) is current price (last price of input sequence)
            testing_target(:,cont) = any(vout(:)>=myseq(N));
        else
            % myseq(ls) is current price (last price of input sequence)
            testing_target(:,cont) = any(vout(:)<=myseq(N));
        end
        % Counter is updated
        cont           = cont+1;
end


% Simulate with testing patterns
[result] = simsystem(netext(xx),testing_input);
%--------------------------------------------------------------------------
%---------------------------------------------------------- DISPLAY RESULTS


% System performances
recrate(xx) = nnz(result & testing_target)/nnz(result);
disp('Recognition rate = ');
disp(recrate(xx));


predict_buy = price_buy/count_buy;
predict_sell = price_sell/count_sell;

predact = Close_act(L-9:L);

predact = predact(:);
predict_input(:,1)  = predact;
[predictsignal(xx)] = simsystem(netext(xx),predict_input);
if predictsignal(xx) ==1
    predictprice(xx) = Close_act(L) + predict_buy;
    
elseif predictsignal(xx) == 0 
    predictprice(xx) = Close_act(L) - predict_sell;
end

 

mysql(['update organizationpredict set organizationpredict.OrganizationID = ',num2str(ID(xx)),',organizationpredict.predictsignal = ',num2str(predictsignal(xx)),',organizationpredict.predictprice = ',num2str(predictprice(xx)),' where organizationpredict.OrganizationID = ',num2str(ID(xx)),'']);

mysql(['update organizationpredict set organizationpredict.OrganizationID = ',num2str(ID(xx)),',organizationpredict.recognitionrate = ',num2str(recrate(xx)),' where organizationpredict.OrganizationID = ',num2str(ID(xx)),'']);



demo_predact = Close_act(L-days_back-N+1:L-days_back);
demo_predout = Close_act(L-days_back+1:L-days_back+3);
demo_predact = demo_predact(:);
demo_predict_input(:,1)  = demo_predact;
testing_predict(:,1) = any(demo_predout(:)>Close_act(L-days_back));
[demo_predictsignal(xx)] = simsystem(netext(xx),demo_predict_input);
if demo_predictsignal(xx) ==1
    demo_predictprice(xx) = Close_act(L-days_back) + predict_buy;
    
elseif demo_predictsignal(xx) == 0 
    demo_predictprice(xx) = Close_act(L-days_back) - predict_sell;
end
mysql(['update organizationpredict set organizationpredict.OrganizationID = ',num2str(ID(xx)),',organizationpredict.demopredictsignal = ',num2str(demo_predictsignal(xx)),',organizationpredict.demopredictprice = ',num2str(demo_predictprice(xx)),' where organizationpredict.OrganizationID = ',num2str(ID(xx)),'']);

end
mysql ('closeall');
    
    
    