MySQLStatsDao.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using be.gaudry.observer;
using be.gaudry.model;
 
namespace be.gaudry.bibliobrol.model.dao.mysql
{
    class MySQLStatsDao : Observable, IStatsDao
    {
        #region singleton
        static MySQLStatsDao instance = null;
        static readonly object padlock = new object();
        private String conStr;
        private DbProviderFactory dbpf;
        MySQLStatsDao()
        {
            notify(new Notification(Notification.VERBOSE.debug, "AccessStatsDao singleton call", this));
            dbpf = ((MySQLFactory)MySQLFactory.Instance).getDbpf();
            conStr = ((MySQLFactory)MySQLFactory.Instance).getConnectionString();
        }
        public static MySQLStatsDao Instance
        {
            get
            {
                lock (padlock)
                {
                    if (instance == null)
                    {
                        instance = new MySQLStatsDao();
                    }
                    return instance;
                }
            }
        }
        #endregion
 
        #region IStatsDao Membres
 
        public Counter loadCounters()
        {
            Counter counter = new Counter();
 
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
 
            try
            {
                dbCon.Open();
                dbDa.SelectCommand.CommandText = "SELECT COUNT(id) AS nb FROM person";
                counter.PersonsCount = (int)(Int64)dbDa.SelectCommand.ExecuteScalar();
 
                dbDa.SelectCommand.CommandText = "SELECT COUNT(personId) AS nb FROM user";
                counter.UsersCount = (int)(Int64)dbDa.SelectCommand.ExecuteScalar();
 
                dbDa.SelectCommand.CommandText = "SELECT COUNT(id) AS nb FROM borrow";
                counter.BorrowsCount = (int)(Int64)dbDa.SelectCommand.ExecuteScalar();
 
                dbDa.SelectCommand.CommandText = "SELECT COUNT(id) AS nb FROM item";
                counter.BrolCount = (int)(Int64)dbDa.SelectCommand.ExecuteScalar();
 
                dbCon.Close();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement des statistiques", e, this));
            }
            finally
            {
                if (dbCon.State != ConnectionState.Closed) dbCon.Close();
            }
            return counter;
        }
 
        public Stat loadActorRoles()
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT COUNT(actor.roleId) AS actors, role.roleName AS role_name");
            str.Append(" FROM role INNER JOIN actor ON role.id = actor.roleId");
            str.Append(" GROUP BY role.roleName, actor.roleId");
            str.Append(" ORDER BY COUNT(actor.roleId) DESC");
            /*StringBuilder str = new StringBuilder("SELECT COUNT(actor.personId) AS [actors],  CONCAT_WS(' ',person.firstName,person.lastName)  AS [actor_name]");
            str.Append(" FROM person INNER JOIN actor ON person.[id] = actor.[personId]");
            str.Append(" GROUP BY person.lastName, person.firstName, actor.roleId");
            str.Append(" ORDER BY COUNT(actor.personId) DESC");*/
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (System.Data.OleDb.OleDbException e)
            {
                notify(new Notification(Notification.VERBOSE.criticalError, "Chargement des rôles pour les statistiques", e, this));
            }
            dbCon.Close();
            Stat stat = new Stat("{0:0.0%} des personnes ({1}) pour le rôle \"{2}\"");
 
            foreach (DataRow dr in brolDt.Rows)
            {
                stat.increase((String)dr["role_name"], (Int64)dr["actors"]);
            }
            return stat;
        }
 
        public Stat loadCurrentBorrows()
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT COUNT(borrow.id) AS borrows,  CONCAT_WS(' ',person.firstName,person.lastName) AS borrower");
            str.Append(" FROM person INNER JOIN borrow ON person.id = borrow.personId");
            str.Append(" GROUP BY person.lastName, person.firstName, borrow.endDate");
            str.Append(" HAVING (((Count(borrow.id))>0) AND ((borrow.endDate) Is Null))");
            str.Append(" ORDER BY COUNT(borrow.id) DESC");
            dbDa.SelectCommand.CommandText = str.ToString();
 
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (System.Data.OleDb.OleDbException e)
            {
                notify(new Notification(Notification.VERBOSE.criticalError, "Chargement des emprunts en cours  pour les statistiques", e, this));
            }
            dbCon.Close();
            Stat stat = new Stat("{0:0.0%} d'emprunts ({1}) en cours pour {2}");
 
            foreach (DataRow dr in brolDt.Rows)
            {
                stat.increase((String)dr["borrower"], (Int64)dr["borrows"]);
            }
            return stat;
        }
 
        public Stat loadAllBorrows()
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            /*
            SELECT Count(borrow.id) AS borrows, [firstName]+" "+[lastName] AS borrower
FROM person INNER JOIN borrow ON person.id = borrow.personId
GROUP BY [firstName]+" "+[lastName]
HAVING (((Count(borrow.id))>0))
ORDER BY Count(borrow.id) DESC; 
             */
            StringBuilder str = new StringBuilder("SELECT COUNT(borrow.id) AS borrows,CONCAT_WS(' ',person.firstName,person.lastName) AS borrower");
            str.Append(" FROM person INNER JOIN borrow ON person.id = borrow.personId");
            str.Append(" GROUP BY borrower");
            str.Append(" HAVING COUNT(borrow.id)>0");
            str.Append(" ORDER BY COUNT(borrow.id) DESC");
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (System.Data.OleDb.OleDbException e)
            {
                notify(new Notification(Notification.VERBOSE.criticalError, "Chargement des emprunts pour les statistiques", e, this));
            }
            dbCon.Close();
            Stat stat = new Stat("{0:0.0%} d'emprunts ({1}) sur le total pour {2}");
 
            foreach (DataRow dr in brolDt.Rows)
            {
                stat.increase((String)dr["borrower"], (Int64)dr["borrows"]);
            }
            return stat;
        }
 
        public Stat loadBrolTypes()
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT COUNT(item.id) AS items, type.name");
            str.Append(" FROM type LEFT JOIN item ON type.id = item.typeId");
            str.Append(" GROUP BY type.name");
            str.Append(" ORDER BY COUNT(item.id) DESC;");
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (System.Data.OleDb.OleDbException e)
            {
                notify(new Notification(Notification.VERBOSE.criticalError, "Chargement des types d'ouvrages pour les statistiques", e, this));
            }
            dbCon.Close();
            Stat stat = new Stat("{0:0.0%} d'ouvrages ({1}) pour le type {2}");
 
            foreach (DataRow dr in brolDt.Rows)
            {
                stat.increase((String)dr["name"], (Int64)dr["items"]);
            }
            return stat;
        }
 
        public Stat loadMediaBrolTypes()
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT COUNT(itemBiblio.id) AS items, type.name");
            str.Append(" FROM type LEFT JOIN (item LEFT JOIN itemBiblio ON item.id = itemBiblio.itemId) ON type.id = item.typeId");
            str.Append(" GROUP BY type.name");
            str.Append(" ORDER BY COUNT(itemBiblio.id) DESC;");
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (System.Data.OleDb.OleDbException e)
            {
                notify(new Notification(Notification.VERBOSE.criticalError, "Chargement des types d'exemplaires pour les statistiques", e, this));
            }
            dbCon.Close();
            Stat stat = new Stat("{0:0.0%} d'exemplaires ({1}) pour le type {2}");
 
            foreach (DataRow dr in brolDt.Rows)
            {
                stat.increase((String)dr["name"], (Int64)dr["items"]);
            }
            return stat;
        }
        #endregion
    }
}