BSEtunes

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

namespace BSE.Platten.BO
{
	/// <summary>
    /// The mysql model class in which all the relevant database operation
    /// for the statistic system informations are implemented.
	/// </summary>
    public class StatisticModel : ModelSql
	{
		#region MethodsPublic
        /// <summary>
        /// Gets a <see cref="StatisticData"/> object with the statistic informations.
        /// </summary>
        /// <param name="strConnection">The connection string for the database queries.</param>
        /// <returns>A <see cref="StatisticData"/> object with the informations.</returns>
        public static StatisticData GetStatisticInformation(string strConnection)
        {
            StatisticData statisticData = new StatisticData();
            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();
                GetCountAlbumsGroupedByMedium(mySqlConnection,statisticData);
                GetTotalDuration(mySqlConnection,statisticData);
                GetCountAlbumsTotal(mySqlConnection,statisticData);
                GetCountAlbumsRecorded(mySqlConnection,statisticData);
                GetCountTracksTotal(mySqlConnection,statisticData);
            }
            return statisticData;
        }

		#endregion

		#region MethodsPrivate
        
        private static void GetCountAlbumsGroupedByMedium(MySqlConnection mySqlConnection, StatisticData statisticData)
        {
            ArrayList aCountAlbumsGroupedByMedium = new ArrayList();

            string strSelectAlbumsGroupedByMedium = "SELECT CONVERT(count(*), SIGNED INTEGER) 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(strSelectAlbumsGroupedByMedium, mySqlConnection))
            {
                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    while (mySqlDataReader.Read())
                    {
                        StatisticCountAlbumsGroupedByMedium statistikAlbumsGroupedByMedium = new StatisticCountAlbumsGroupedByMedium
                        {
                            Count = Convert.ToInt32(GetInt64(mySqlDataReader, "count", false, 0)),
                            Medium = GetString(mySqlDataReader, "medium", true, null)
                        };
                        aCountAlbumsGroupedByMedium.Add(statistikAlbumsGroupedByMedium);
                    }
                }
            }

            if (aCountAlbumsGroupedByMedium.Count > 0)
            {
                statisticData.CountAlbumsGroupedByMedium = new StatisticCountAlbumsGroupedByMedium[aCountAlbumsGroupedByMedium.Count];
                aCountAlbumsGroupedByMedium.CopyTo(statisticData.CountAlbumsGroupedByMedium);
            }
        }

        private static void GetTotalDuration(MySqlConnection mySqlConnection, StatisticData statisticData)
        {
            string strSelectDuration = "SELECT CAST(SUM(HOUR(dauer)) * 3600 + SUM(MINUTE(dauer)) * 60 + SUM(SECOND(dauer)) AS SIGNED INTEGER) AS second " +
                "FROM lieder l WHERE Liedpfad IS NOT NULL";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectDuration, mySqlConnection))
            {
                object objSecond = mySqlCommand.ExecuteScalar();
                if (objSecond is long)
                {
                    long ticksPerSecond = (long)objSecond * 0x989680L;
                    statisticData.TotalDuration = new TimeSpan(ticksPerSecond);
                }
            }
        }

        private static void GetCountAlbumsTotal(MySqlConnection mySqlConnection, StatisticData statisticData)
        {
            string strSelectCountAlbumsTotal = "SELECT COUNT(*) AS count FROM titel t ";
            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectCountAlbumsTotal, mySqlConnection))
            {
                object objCount = mySqlCommand.ExecuteScalar();
                if (objCount is long)
                {
                    statisticData.CountAlbumsTotal = Convert.ToInt32((long)objCount);
                }
            }
        }

        private static void GetCountAlbumsRecorded(MySqlConnection mySqlConnection, StatisticData statisticData)
        {
            string strSelectCountAlbumsRecorded = "SELECT count(DISTINCT t.titelId) as count " +
                " FROM titel t" +
                " INNER JOIN lieder l ON t.titelid = l.titelid AND l.liedpfad IS NOT NULL";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectCountAlbumsRecorded, mySqlConnection))
            {
                object objCount = mySqlCommand.ExecuteScalar();
                if (objCount is long)
                {
                    statisticData.CountAlbumsRecorded = Convert.ToInt32((long)objCount);
                }
            }
        }

        private static void GetCountTracksTotal(MySqlConnection mySqlConnection, StatisticData statisticData)
        {
            string strSelectCountTracksTotal = "SELECT COUNT(*) AS count FROM lieder t WHERE liedpfad IS NOT NULL";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectCountTracksTotal, mySqlConnection))
            {
                object objCount = mySqlCommand.ExecuteScalar();
                if (objCount is long)
                {
                    statisticData.CountTracksTotal = Convert.ToInt32((long)objCount);
                }
            }
        }

		#endregion
	}
}