BSEtunes

using System;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Data;
using System.Globalization;

namespace BSE.Platten.BO
{
	/// <summary>
	/// Zusammenfassung f�r CStatistikBusinessModel.
	/// </summary>
	public class CStatistikModel
	{
		#region MethodsPublic

        public static DataSet GetDataSetStatisticInformation(string strConnection)
        {
            DataSet dataSet = null;
            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                dataSet = new DataSet("Statistic");
                dataSet.Locale = CultureInfo.InvariantCulture;
                dataSet.Tables.Add(GetDataTableCountAlbumsGroupByMedium(mySqlConnection));
                dataSet.Tables.Add(GetDataTableCountSpielzeit(mySqlConnection));
                dataSet.Tables.Add(GetDataTableCountAlbums(mySqlConnection));
                dataSet.Tables.Add(GetDataTableCountRecordedAlbums(mySqlConnection));
                dataSet.Tables.Add(GetDataTableCountTracks(mySqlConnection));
            }
            return dataSet;
        }

		#endregion

		#region MethodsPrivate

        private static DataTable GetDataTableCountAlbumsGroupByMedium(MySqlConnection mySqlConnection)
        {
            DataTable dataTable = new DataTable("TitelGroupByMedium");
            dataTable.Locale = CultureInfo.InvariantCulture;
            DataColumn dataColumnCount = new DataColumn("Count", typeof(int), "", MappingType.Element);
            dataTable.Columns.Add(dataColumnCount);
            DataColumn dataColumnMedium = new DataColumn("Medium", typeof(string), "", MappingType.Element);
            dataTable.Columns.Add(dataColumnMedium);

            string strSelectTitelGroupByMedium = "SELECT count(*) AS Count, m.medium " +
                "FROM titel t " +
                "INNER JOIN medium m ON t.mediumId = m.mediumId " +
                "GROUP BY m.medium " +
                "ORDER BY count DESC";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectTitelGroupByMedium, mySqlConnection))
            {
                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    while (mySqlDataReader.Read())
                    {
                        DataRow dataRow = dataTable.NewRow();
                        dataRow["Count"] = mySqlDataReader["count"];
                        dataRow["Medium"] = mySqlDataReader["medium"];

                        dataTable.Rows.Add(dataRow);
                    }
                }
            }
            return dataTable;

        }

        private static DataTable GetDataTableCountSpielzeit(MySqlConnection mySqlConnection)
        {
            DataTable dataTable = new DataTable("Spielzeit");
            dataTable.Locale = CultureInfo.InvariantCulture;
            DataColumn dataColumnSpielzeit = new DataColumn("Spielzeit", typeof(int), "", MappingType.Element);
            dataTable.Columns.Add(dataColumnSpielzeit);

            string strSelectSpielzeit = "SELECT (SUM(HOUR(dauer)) * 3600 + SUM(MINUTE(dauer)) * 60 + SUM(SECOND(dauer))) AS second " +
                "FROM lieder l WHERE Liedpfad IS NOT NULL";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSpielzeit, mySqlConnection))
            {
                DataRow dataRow = dataTable.NewRow();
                dataRow["Spielzeit"] = mySqlCommand.ExecuteScalar();
                dataTable.Rows.Add(dataRow);
            }
            return dataTable;
        }

        private static DataTable GetDataTableCountAlbums(MySqlConnection mySqlConnection)
        {
            DataTable dataTable = new DataTable("CountPlatten");
            dataTable.Locale = CultureInfo.InvariantCulture;
            DataColumn dataColumnCountPlatten = new DataColumn("CountPlatten", typeof(int), "", MappingType.Element);
            dataTable.Columns.Add(dataColumnCountPlatten);

            string strSelectCountPlatten = "SELECT COUNT(*) AS anzahl FROM titel t ";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectCountPlatten, mySqlConnection))
            {
                DataRow dataRow = dataTable.NewRow();
                dataRow["CountPlatten"] = mySqlCommand.ExecuteScalar();
                dataTable.Rows.Add(dataRow);
            }
            return dataTable;
        }

        private static DataTable GetDataTableCountRecordedAlbums(MySqlConnection mySqlConnection)
        {
            DataTable dataTable = new DataTable("CountRecordedPlatten");
            dataTable.Locale = CultureInfo.InvariantCulture;
            DataColumn dataColumnCountRecordedPlatten = new DataColumn("CountRecordedPlatten", typeof(int), "", MappingType.Element);
            dataTable.Columns.Add(dataColumnCountRecordedPlatten);
            int iCounter = 0;

            //weil MySql 4.0.xx keine Subselects verarbeiten kann muss die Anzahl Platten �ber einen
            //DataReader ermittelt werden
            string strSelectCountRecordedPlatten = "SELECT DISTINCT t.titelId" +
                " FROM titel t" +
                " INNER JOIN lieder l ON t.titelid = l.titelid AND l.liedpfad IS NOT NULL";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectCountRecordedPlatten, mySqlConnection))
            {
                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    while (mySqlDataReader.Read())
                    {
                        iCounter++;
                    }
                    DataRow dataRow = dataTable.NewRow();
                    dataRow["CountRecordedPlatten"] = iCounter;
                    dataTable.Rows.Add(dataRow);
                }
            }
            return dataTable;
        }

        private static DataTable GetDataTableCountTracks(MySqlConnection mySqlConnection)
        {
            DataTable dataTable = new DataTable("CountTracks");
            dataTable.Locale = CultureInfo.InvariantCulture;
            DataColumn dataColumnCountTracks = new DataColumn("CountTracks", typeof(int), "", MappingType.Element);
            dataTable.Columns.Add(dataColumnCountTracks);

            string strSelectCountTracks = "SELECT COUNT(*) AS anzahl FROM lieder t WHERE liedpfad IS NOT NULL";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectCountTracks, mySqlConnection))
            {
                DataRow dataRow = dataTable.NewRow();
                dataRow["CountTracks"] = mySqlCommand.ExecuteScalar();
                dataTable.Rows.Add(dataRow);
            }
            return dataTable;
        }
		#endregion
	}
}