% INSERT2  Insert data into the specified database in correct order,
%    guaranteed
%
%   ERROR = INSERT2(DATABASE,LOGIN,PASSWORD,TABLE,COLUMNS,DATA)  Inserts the DATA
%   into the specified TABLE's COLUMNS into the specified DATABASE with the
%   specified LOGIN and PASSWORD.  ERROR returns a value dependent on the
%   error.
%       ERROR = 0:  Successful
%             = 1:  Invalid Database
%             = 2:  Database Connection Error
%             = 3:  Data Transfer Error
%             = 4:  Connection Close Error
%
%   Example:  insert2('G21-2006-Raw','My_Login','PaS5werD','Table_1',{'DDOY','Year'},[101 2006;102 2006;103 2006])
%
% Jeremy Smith
% 9/21/06
% Last Edited: 2/12/07

function Error = insert2(DB,Login,Password,Table,Col,D)
Error = 1;  % Initialize Invalid Database
accuracy = 15;
tic

%% Error Checks
% Database Check
if ~~ischar(DB)
    DBs = getdatasources';  % Get the list of databases
    x = intersect(DBs,DB);
    if ~isempty(x)
        Error = 0;
    end
else
    error('Database must be a string.');
end

% Login Type Check
if ~ischar(Login) && ~isempty(Login)
    error('Login must be a string.');
end

% Password Type Check
if ~ischar(Password) && ~isempty(Password)
    error('Password must be a string.');
end

% Table Type CHeck
if ~ischar(Table)
    error('Table must be a string.');
end

% Column Type Check
if ~iscell(Col)
    error('Columns must be in a cell array.')
end
if ~~iscell(Col)
    for i=1:1:length(Col)
        if ~ischar(Col{i})
            error('Columns must be strings.')
        end
    end
end

% Table Check
try
    conn = database(DB,Login,Password);
    dbmeta = dmd(conn);
    Tables = tables(dbmeta,'cata');  % Get tables

    % Get columns for later use (reduce number of DB connections)
    SQL = ['SELECT * FROM ' Table];
    curs = exec(conn,SQL);
    curs = fetch(curs,1);
    Columns = columnnames(curs);
    close(conn)

    T = 0;
    for i=1:1:size(Tables,1)
        if strcmp(Tables(i,1),Table) == 1
            T = 1;
        end
    end

    if T == 0
        error([Table ' does not exist.'])
    end

    Table = ['"' Table '"'];
catch
    Error = 2;
end

% Column Check
if Error == 0
    eval(['Columns = {' Columns '};']) % Reformat into a cell array
    Columns = Columns';
    BadCol = [];  % Initialize bad column variable
    for i=1:1:length(Col)
        match = 0;  % Initialize false
        for j=1:1:length(Columns)
            if strcmp(Col{i},Columns{j}) == 1
                match = 1;  % True, column sent in exists
            end
        end

        if match == 0
            BadCol = [BadCol {Col{i}}];  % Store nonexistant column
        end
    end
    if ~isempty(BadCol)  % Error output
        BadColStr = [];
        for i=1:1:length(BadCol)  % Format output
            BadColStr = [BadColStr ', ' BadCol{i}];
        end

        E = ['The following columns do not exist in ' Table ': ' BadColStr(3:end)];  % Format error string
        error(E)
    end
end

% Data Type Check
% if ~iscell(D) && ~isnumeric(D)
%     error('Data must be either a cell array or a numeric array.')
% end
% if ~~iscell(D)
%     for i=1:1:length(D)
%         if ~ischar(D{i}) && ~isnumeric(D{i})
%             error('Data within each cell must be a string or numeric.')
%         end
%     end
% end

% Dimension Check
if length(Col) ~= size(D,2) && length(Col) ~= size(D,1)
    error('Number of Columns must equal the number of columns or rows in the Data');
elseif length(Col) == size(D,1) && length(Col) ~= size(D,2)
    D = D';
end

%% Connect to database
if Error == 0
    try
        conn = database(DB,'','');
        connHandle = conn.Handle;
        stmt = connHandle.createStatement;
    catch
        Error = 2;  % Database Connection Error
    end
end

%% Send Data
if Error == 0
    %     try
    %Format Columns
    col = ['"' Col{1} '"'];
    C = length(Col);  % Number of columns
    if C == 1
        clear Col
    elseif C == 2
        col = ['"' Col{1} '","' Col{2} '"'];
        clear Col
    else
        for i=2:1:C
            col = [col ',"' Col{i} '"'];
        end
        clear Col
    end

    if ~~iscell(D)
        for i=1:1:size(D,1)
            % Format the Values portion of the SQL statement
            if C == 1
                if isnumeric(D{i,1}) == 1  % If it's a number
                    if isnan(D{i,1}) == 1  % If it's a NaN replace with null
                        num = 'null';
                    elseif isinf(D{i,1}) == 1
                        num = 'null';
                    else
                        num = num2str(D{i,1},accuracy);
                    end
                else
                    num = D{i,1};
                end
            elseif C == 2
                if isnumeric(D{i,1}) == 1
                    if isnan(D{i,1}) == 1
                        num1 = 'null';
                    elseif isinf(D{i,1}) == 1
                        num1 = 'null';
                    else
                        num1 = num2str(D{i,1},accuracy);
                    end
                else
                    num1 = ['''' D{i,1} ''''];
                end
                if isnumeric(D{i,2}) == 1
                    if isnan(D{i,2}) == 1
                        num2 = 'null';
                    elseif isinf(D{i,2}) == 1
                        num2 = 'null';
                    else
                        num2 = num2str(D{i,2},accuracy);
                    end
                else
                    num2 = ['''' D{i,2} ''''];
                end
                num = [num1 ',' num2];
            else
                if isnumeric(D{i,1}) == 1
                    if isnan(D{i,1}) == 1
                        num = 'null';
                    elseif isinf(D{i,1}) == 1
                        num = 'null';
                    else
                        num = num2str(D{i,1},accuracy);
                    end
                else
                    num = ['''' D{i,1} ''''];
                end
                for j=2:1:size(D,2)
                    if isnumeric(D{i,j}) == 1
                        if isnan(D{i,j}) == 1
                            num = [num ',null'];
                        elseif isinf(D{i,j}) == 1
                            num = [num ',null'];
                        else
                            num = [num ',' num2str(D{i,j},accuracy)];
                        end
                    else
                        num = [num ',''' D{i,j} ''''];
                    end
                end
            end

            % Insert the current row of records
            SQL = ['INSERT INTO ' Table ' (' col ') VALUES (' num ')'];
            stmt.addBatch(SQL);
            stmt.executeBatch;
        end
    elseif ~~isnumeric(D)
        for i=1:1:size(D,1)
            % Format the Values portion of the SQL statement
            if C == 1
                if ischar(D(i,1)) == 0
                    if isnan(D(i,1)) == 1
                        num = 'null';
                    elseif isinf(D(i,1)) == 1
                        num = 'null';
                    else
                        num = num2str(D(i,1),accuracy);
                    end
                else
                    num = ['''' D(i,1) ''''];
                end
            elseif C == 2
                if isnumeric(D(i,1)) == 1
                    if isnan(D(i,1)) == 1
                        num1 = 'null';
                    elseif isinf(D(i,1)) == 1
                        num1 = 'null';
                    else
                        num1 = num2str(D(i,1),accuracy);
                    end
                else
                    num1 = ['''' D(i,1) ''''];
                end
                if isnumeric(D(i,2)) == 1
                    if isnan(D(i,2)) == 1
                        num2 = 'null';
                    elseif isinf(D(i,2)) == 1
                        num2 = 'null';
                    else
                        num2 = num2str(D(i,2),accuracy);
                    end
                else
                    num2 = ['''' D(i,2) ''''];
                end
                num = [num1 ',' num2];
            else
                if isnumeric(D(i,1)) == 1
                    if isnan(D(i,1)) == 1
                        num = 'null';
                    elseif isinf(D(i,1)) == 1
                        num = 'null';
                    else
                        num = num2str(D(i,1),accuracy);
                    end
                else
                    num = ['''' D(i,1) ''''];
                end
                for j=2:1:size(D,2)
                    if isnumeric(D(i,j)) == 1
                        if isnan(D(i,j)) == 1
                            num = [num ',null'];
                        elseif isinf(D(i,j)) == 1
                            num = [num ',null'];
                        else
                            num = [num ',' num2str(D(i,j),accuracy)];
                        end
                    else
                        num = [num ',''' D(i,j) ''''];
                    end
                end
            end

            % Insert the current row of records
            SQL = ['INSERT INTO ' Table ' (' col ') VALUES (' num ')'];
            stmt.addBatch(SQL);
            stmt.executeBatch;
        end
    end
    %     catch
    %         Error = 3;  % Data Transfer Error
    %     end
end
%% Close Connection
if Error == 0
    try
        stmt.close;
        close(conn)
        disp(sprintf('%f Minutes',toc/60))
    catch
        Error = 4;  % Connection Close Error
        assignin('base','conn',conn)
    end
end