function GetSQLSprocNameWrapper()
{
    switch(providerID)
    {
        case "0":
            return "'";
            break;
        case "1":
            return "";
            break;
    }  
}

function GetSQLParamPrefix()
{
    switch(providerID)
    {
        case "0":
            return "_";
            break;
        case "1":
            return "@";
            break;
    }  
}

function GetMySqlTypeFromNetType( type, fieldSize )
{
    switch(type)
    {
        case "string":
            return "varchar(" + fieldSize + ")";
            break;
        case "Guid":
            return "varchar(36)";    
            break;
        case "int":
            return "INTEGER";
            break;
        case "DateTime":
            return "DATETIME";
            break;
        case "float":
            return "FLOAT";
            break;
        case "double":
            return "DOUBLE";
            break;
            case "bool":
        return "BIT"
            break;
    }
        
}

function GetSQLParamDeclarePrefix()
{
    switch(providerID)
    {
        case "0":
            return " IN ";
            break;
        case "1":
            return "";
            break;
    }  
}


function GenerateStoredProcedures()
{
    $('#txtStoredProcedures').val("");
    
    
    TagHeader('txtStoredProcedures', true);
    
    GenerateStoredProcedure_AddUpdate();
    AddLine("\n", 'txtStoredProcedures');
    GenerateStoredProcedure_SelectAll();
    AddLine("\n", 'txtStoredProcedures');
    GenerateStoredProcedure_SelectSingle();
    AddLine("\n", 'txtStoredProcedures');
    GenerateStoredProcedure_DeleteSingle();
    AddLine("\n", 'txtStoredProcedures');
}

function GenerateStoredProcedure_DeleteSingle()
{
    AddLine("\nCREATE PROCEDURE " + GetSQLSprocNameWrapper() + "sp" + className +"DeleteSingle" + GetSQLSprocNameWrapper() + " (" + GetSQLParamDeclarePrefix() + GetSQLParamPrefix() + fields[0] + " " + GetMySqlTypeFromNetType(types[0], fieldSize[0]) + ")\n", 'txtStoredProcedures');
    if( providerID == "1")
    {
        AddLine("AS", 'txtStoredProcedures');
    }
    
    AddLine("BEGIN", 'txtStoredProcedures');
    
    AddLine("DELETE FROM [" + className + "] WHERE " + fields[0] + " = " + GetSQLParamPrefix() + fields[0] + ";", 'txtStoredProcedures');
    
    AddLine("END", 'txtStoredProcedures');
     
    AddLine("GO", 'txtStoredProcedures');
}

function GenerateStoredProcedure_SelectSingle()
{
    //AddLine("using (" + GetDataAdapterType() + " adap = new " + GetDataAdapterType() + "(string.Format(\"select * from " + className + " where " + fields[0] + " = '{0}'\", " + fields[0] + ".ToString()), conn))");
    
    AddLine("\nCREATE PROCEDURE " + GetSQLSprocNameWrapper() + "sp" + className +"SelectSingle" + GetSQLSprocNameWrapper() + " (" + GetSQLParamDeclarePrefix() + GetSQLParamPrefix() + fields[0] + " " + GetMySqlTypeFromNetType(types[0], fieldSize[0]) + ")\n", 'txtStoredProcedures');
    if( providerID == "1")
    {
        AddLine("AS", 'txtStoredProcedures');
    }
    
    AddLine("BEGIN", 'txtStoredProcedures');
    
    AddLine("SELECT * FROM [" + className + "] WHERE " + fields[0] + " = " + GetSQLParamPrefix() + fields[0] + ";", 'txtStoredProcedures');
    
    AddLine("END", 'txtStoredProcedures');
     
    AddLine("GO", 'txtStoredProcedures');
}

function GenerateStoredProcedure_AddUpdate()
{    
    var paramstring = "";
    for( var i = 0; i <= MAX_FIELD_TEST; i++ )
    {
        if( fields[i] && fields[i+1] )
        {
            paramstring = paramstring + GetSQLParamDeclarePrefix() + GetSQLParamPrefix() + fields[i] + " " + GetMySqlTypeFromNetType(types[i], fieldSize[i]) + ", ";
        }
        else if ( fields[i] && !fields[i+1] )
        {
            paramstring = paramstring + GetSQLParamDeclarePrefix() +  GetSQLParamPrefix() + fields[i] + " " + GetMySqlTypeFromNetType(types[i], fieldSize[i]);
        }
    }
    
    AddLine("\nCREATE PROCEDURE " + GetSQLSprocNameWrapper() + "sp" + className +"AddUpdate" + GetSQLSprocNameWrapper() + " (" + paramstring + ")\n", 'txtStoredProcedures');
    if( providerID == "1")
    {
        AddLine("AS", 'txtStoredProcedures');
    }
    
    AddLine("BEGIN", 'txtStoredProcedures');
    
    if( providerID == "0")
    {
        AddLine("IF EXISTS (SELECT " + fields[0] + " FROM [" + className + "] WHERE " + fields[0] + " = " +  GetSQLParamPrefix() + "" + fields[0] + ") THEN", 'txtStoredProcedures');
    }
    else //sql2008 - no then
    {
        AddLine("IF EXISTS (SELECT " + fields[0] + " FROM [" + className + "] WHERE " + fields[0] + " = " +  GetSQLParamPrefix() + "" + fields[0] + ") ", 'txtStoredProcedures');
    }
    
    AddLine("UPDATE " + className +" SET", 'txtStoredProcedures');

    for( var i = 0; i < MAX_FIELD_TEST; i++ )
    {
        
        if( fields[i] && fields[i+1] )
        {
            AddLine(fields[i] + " = " + GetSQLParamPrefix() + fields[i] + ",", 'txtStoredProcedures');       
        }
        else if ( fields[i] && !fields[i+1] )
        {
             AddLine(fields[i] + " = " + GetSQLParamPrefix() + fields[i], 'txtStoredProcedures');    
        }
        
    }
    
    AddLine("WHERE " + fields[0] + " = " +  GetSQLParamPrefix() + "" + fields[0] + ";", 'txtStoredProcedures');
    
    AddLine("ELSE", 'txtStoredProcedures');

    AddLine("INSERT INTO", 'txtStoredProcedures');
    AddLine("[" + className + "]", 'txtStoredProcedures');
    AddLine("(", 'txtStoredProcedures');

    for( var i = 0; i < MAX_FIELD_TEST; i++ )
    {
        
        if( fields[i] && fields[i+1] )
        {
            AddLine(fields[i] + ",", 'txtStoredProcedures');       
        }
        else if ( fields[i] && !fields[i+1] )
        {
            AddLine(fields[i] + "", 'txtStoredProcedures');       
        }
   
    }
    
    AddLine(")", 'txtStoredProcedures');
    AddLine("VALUES", 'txtStoredProcedures');
    AddLine("(", 'txtStoredProcedures');
    
    for( var i = 0; i < MAX_FIELD_TEST; i++ )
    {
        if( fields[i] && fields[i+1] )
        {
            AddLine(GetSQLParamPrefix() + fields[i] + ",", 'txtStoredProcedures');       
        }
        else if ( fields[i] && !fields[i+1] )
        {
            AddLine(GetSQLParamPrefix() + fields[i] + "", 'txtStoredProcedures');       
        }
    }
	
	AddLine(");", 'txtStoredProcedures');
	if( providerID == "0")
    {
        AddLine("END IF;", 'txtStoredProcedures');
    }
    AddLine("END", 'txtStoredProcedures');
    AddLine("GO", 'txtStoredProcedures');
}

function GenerateStoredProcedure_SelectAll()
{  
    var paramstring = "";
    
    for( var i = 0; i < MAX_FIELD_TEST; i++ )
    {
        
        if( searchable[i] )
        {
            if( fields[i] && fields[i+1] )
            {
                paramstring = paramstring + GetSQLParamDeclarePrefix() + GetSQLParamPrefix() + fields[i] + " " + GetMySqlTypeFromNetType(types[i], fieldSize[i]) + ", ";
            }
            else if ( fields[i] && !fields[i+1] )
            {
                paramstring = paramstring + GetSQLParamDeclarePrefix() + GetSQLParamPrefix() + fields[i] + " " + GetMySqlTypeFromNetType(types[i], fieldSize[i]);
            }
        }
      
    }
     
     
    var wrappedParamString =  "(" + paramstring + ")";
    if( providerID == "1")
    {
        if( paramstring == "" )
        {
            wrappedParamString = "";
        }
    }
     
    AddLine("CREATE PROCEDURE " + GetSQLSprocNameWrapper() + "sp" + className +"SelectAll" + GetSQLSprocNameWrapper() + wrappedParamString +  "\n", 'txtStoredProcedures');
    if( providerID == "1")
    {
        AddLine("AS", 'txtStoredProcedures');
    }
    AddLine("BEGIN", 'txtStoredProcedures');
    AddLine("SELECT * FROM [" + className + "]", 'txtStoredProcedures');
    var hasWhere = false;
    for( var i = 0; i < MAX_FIELD_TEST; i++ )
    {
        if( searchable[i] )
        {
            if( ! hasWhere )
            {
                AddLine("WHERE", 'txtStoredProcedures');
                hasWhere = true;
            }
            
            if( types[i] != "string" )
            {
                var sql = "(" + GetSQLParamPrefix() + fields[i] + " IS NULL OR " + fields[i] + " = " + GetSQLParamPrefix() + fields[i] + ")";
                
                if( fields[i] && fields[i+1] )
                {
                    sql = sql + " AND ";
                }
                
                AddLine(sql, 'txtStoredProcedures');
            }
            else
            {
                var sql;
                
               if( providerID == "0")
               { 
                    sql =  "(" + GetSQLParamPrefix() + fields[i] + " IS NULL OR MATCH (" + fields[i] + ") AGAINST (" + GetSQLParamPrefix() + fields[i] + " IN BOOLEAN MODE)) ";
                }
                else//this is how we do in mssql (super basic)
                {
                    sql =  "(" + GetSQLParamPrefix() + fields[i] + " IS NULL OR " + fields[i] + " like \'%\' + " + GetSQLParamPrefix() + fields[i] + " + \'%\')";
                }
                
                if( fields[i] && fields[i+1] )
                {
                    sql = sql + " AND ";
                }
                
                AddLine(sql, 'txtStoredProcedures');
            }
        }
   
    }
    
    AddLine("ORDER BY " + fields[0] + ";", 'txtStoredProcedures');
     
    AddLine("END", 'txtStoredProcedures');
    AddLine("GO", 'txtStoredProcedures');
}