using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;

namespace db1
{
    class DBAccess
    {
        // using mysql, see http://dev.mysql.com/tech-resources/articles/dotnet/
        static String connectString =
                    @"DRIVER={MySQL ODBC 3.51 Driver};
                    SERVER=localhost;
                    DATABASE=football;
                    UID=root;
                    PASSWORD=oliver;
                    OPTION=3";
        static public String getGames(String country, String level, String year)
        {
            // always return something, result or errormsg
            String retString = "<html><body>";
            // set up a query based on the parameters
            String myQuery =String.Format(
                @"SELECT hteam.name,ateam.name,h_goals,a_goals FROM fmatch,team hteam, team ateam 
                    WHERE fmatch.h_team_id = hteam.team_id AND
                        fmatch.a_team_id = ateam.team_id AND liga_id in
                        (SELECT liga_id FROM liga WHERE 
                            season_id='{0}'AND country_id='{1}' AND level='{2}' 
                        )
                    ;", year,country,level);
            OdbcConnection con=null;
            OdbcDataReader myReader=null;
            try
            {
                con = new OdbcConnection(connectString);
                con.Open();
                // execute a query
                OdbcCommand myCommand = new OdbcCommand(myQuery, con);
                myReader = myCommand.ExecuteReader();
                // and process the results
                while (myReader.Read())
                    retString += "<div>"+
                        myReader.GetString(0) + " - " +
                        myReader.GetString(1) + " : " +
                        Convert.ToString(myReader.GetInt32(2))  + " - " +
                        Convert.ToString(myReader.GetInt32(3))  +
                        "</div>\r\n";
            }
            catch (Exception ex)
            {
                retString += "<p>"+ex.Message+"</p>";
            }
            if (con!=null) 
                con.Close();
            if (myReader!=null) 
                myReader.Close();
            return retString + "</body></html>";
        }

        static public String getTeams(String country, String level, String year)
        {
            // always return something, result or errormsg
            String retString = "<html><body>";
            // set up a query based on the parameters
            String myQuery = String.Format(
                @"SELECT name from team WHERE team_id IN 
                    (SELECT team_id FROM member WHERE liga_id IN 
                        (SELECT liga_id FROM liga WHERE 
                            season_id='{0}'AND country_id='{1}' AND level='{2}' 
                        )
                    );", year, country, level);
            OdbcConnection con = null;
            OdbcDataReader myReader = null;
            try
            {
                con = new OdbcConnection(connectString);
                con.Open();
                // execute a query
                OdbcCommand myCommand = new OdbcCommand(myQuery, con);
                myReader = myCommand.ExecuteReader();
                // and process the results
                while (myReader.Read())
                    retString += "<div>" + myReader.GetString(0) + "</div>\r\n";
            }
            catch (Exception ex)
            {
                retString += "<p>" + ex.Message + "</p>";
            }
            if (con != null)
                con.Close();
            if (myReader != null)
                myReader.Close();
            return retString + "</body></html>";
        }
    }
}