AccessMediaBrolDao.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
using be.gaudry.observer;
using be.gaudry.bibliobrol.model.identity;
using be.gaudry.model.exceptions;
 
namespace be.gaudry.bibliobrol.model.dao.msaccess
{
    public sealed class AccessMediaBrolDao : Observable, IMediaBrolDao
    {
 
        private String conStr;
 
        #region singleton
        static AccessMediaBrolDao instance = null;
        static readonly object padlock = new object();
        private DbProviderFactory dbpf;
        AccessMediaBrolDao()
        {
            notify(new Notification(Notification.VERBOSE.debug,"AccessMediaBrolDao singleton call", this));
            dbpf = ((AccessFactory)AccessFactory.Instance).getDbpf();
            conStr = ((AccessFactory)AccessFactory.Instance).getConnectionString();
        }
        public static AccessMediaBrolDao Instance
        {
            get
            {
                lock (padlock)
                {
                    if (instance == null)
                    {
                        instance = new AccessMediaBrolDao();
                    }
                    return instance;
                }
            }
        }
        #endregion
 
        #region IMediaBrolDao Members
 
        #region mediabrol
        public int insertMediaBrol(MediaBrol mediabrol)
        {
            StringBuilder str = new StringBuilder();
            int lastId = -1;
            if (mediabrol.Id <= 0)
            {
                DbConnection dbCon = dbpf.CreateConnection();
                DbDataAdapter dbDa = dbpf.CreateDataAdapter();
                dbDa.InsertCommand = dbCon.CreateCommand();
                dbCon.ConnectionString = conStr;
                dbCon.Open();
                bool insertionDateValid = (!mediabrol.InsertionDate.Equals(new DateTime(0L)));
                str.Append("INSERT INTO itemBiblio (biblioItemName, itemId, ownerId, mediaId, comment, localisation");
                if (insertionDateValid)
                    str.Append(", insertionDate");
                str.Append(") VALUES('");
                str.Append(AccessUtils.escapeAndTrim(mediabrol.Name));
                str.Append("',");
                str.Append(mediabrol.Brol.Id);
                str.Append(",");
                str.Append(mediabrol.Owner.Id);
                str.Append(",");
                str.Append(mediabrol.MediaType.Id);
                str.Append(",'");
                str.Append(AccessUtils.escapeAndTrim(mediabrol.Comment));
                str.Append("','");
                str.Append(AccessUtils.escapeAndTrim(mediabrol.Localisation));
                str.Append("',");
                str.Append(AccessUtils.getAccessDate((insertionDateValid)?mediabrol.InsertionDate:DateTime.Now));
                str.Append(");");
                try
                {
                    dbDa.InsertCommand.CommandText = str.ToString();
                    notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.InsertCommand.CommandText, this));
                    if (dbDa.InsertCommand.ExecuteNonQuery() != 1)
                        notify(new Notification(Notification.VERBOSE.error, "ERREUR Insertions multiples exemplaire", this));
                    //bad smell code : may not work with concurent acces
                    dbDa.InsertCommand.CommandText = "SELECT @@IDENTITY";
                    lastId = (int)dbDa.InsertCommand.ExecuteScalar();
                    mediabrol.Id = lastId;
                    if (insertQualities(mediabrol, dbDa) == -1)
                        notify(new Notification(Notification.VERBOSE.error, "ERREUR d'insertion des caractéristiques de media pour " + mediabrol.Name, this));
                }
                catch (Exception ex)
                {
                    notify(new Notification(Notification.VERBOSE.error,"Insertion d'un exemplaire", ex, this));
                }
                dbCon.Close();
            }
            str = new StringBuilder("L'exemplaire \"");
            str.Append(mediabrol.Name);
            str.Append("\" de l'ouvrage \"");
            str.Append(mediabrol.Brol.Title);
            str.Append((lastId > 0) ? "\" est ajouté." : "\" ne peut être ajouté.");
            notify(new Notification(Notification.VERBOSE.opsResult, str.ToString(), this));
            return lastId;
        }
 
        internal MediaBrol getMediaBrol(DataRow row, bool anonymous)
        {
            if (row["i.id"] is DBNull) throw new BuildObjectException("No id found");
            //Load owner infos
            Person owner = new Person();
            if (!anonymous)
            {
                if (!(row["p.id"] is DBNull))
                {
                    owner.Id = (int)row["p.id"];
                }
                if (!(row["lastName"] is DBNull))
                {
                    owner.LastName = (String)row["lastName"];
                }
                if (!(row["firstName"] is DBNull))
                {
                    owner.FirstName = (String)row["firstName"];
                }
                if (!(row["birthdate"] is DBNull))
                {
                    owner.Birthdate = (DateTime)row["birthdate"];
                }
            }
            //load mediabrol infos
            MediaBrol mediabrol = new MediaBrol();
            mediabrol.Owner = owner;
            mediabrol.Id = (int)row["i.id"];
            if (!(row["insertionDate"] is DBNull))
            {
                mediabrol.InsertionDate = (DateTime)row["insertionDate"];
            }
            if (!(row["biblioItemName"] is DBNull))
            {
                mediabrol.Name = (String)row["biblioItemName"];
            }
            if (!(row["comment"] is DBNull))
            {
                mediabrol.Comment = (String)row["comment"];
            }
            if (!(row["localisation"] is DBNull))
            {
                mediabrol.Localisation = (String)row["localisation"];
            }
            //load media infos
            if (!(row["m.id"] is DBNull))
            {
                mediabrol.MediaType = new Media(
                    (int)row["m.id"],
                    (row["name"] is DBNull) ? "" : (String)row["name"],
                    (row["type"] is DBNull) ? new BrolType() : new BrolType((int)row["type"],"")
                    );
            }
            return mediabrol;
        }
        private List<MediaBrol> loadSelectedMediaBrols(int id)
        {
            List<MediaBrol> mediabrols = new List<MediaBrol>();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT i.*, p.*, m.* FROM ");
            str.Append("(itemBiblio AS i");
            str.Append(" LEFT JOIN person AS p ON i.ownerId = p.id)");
            str.Append(" LEFT JOIN media AS m ON i.mediaId = m.id");
            str.Append(" WHERE i.id = ");
            str.Append(id);
            //todo : use another request to load qualities instead of join
            //todo : load borrows? NO : there are loades only if we click on borrows tabpage...
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            MediaBrol mediabrol;
            try
            {
                dbDa.Fill(brolDt);
                foreach (DataRow brolRow in brolDt.Rows)
                {
                    mediabrol = getMediaBrol(brolRow,false);
                    try
                    {
                        mediabrol.Brol = AccessBrolDao.Instance.loadBrol((int)brolRow["itemId"], false);
                    }
                    catch (Exception eBrol)
                    {
                        notify(new Notification(Notification.VERBOSE.error,"Chargement des éléments", eBrol, this));
                    }
                    mediabrol.Qualities = loadQualitiesCommand(mediabrol.Id, dbDa);
                    mediabrol.Borrowed = isBorrowed(mediabrol.Id, dbDa, dbCon);
                    mediabrols.Add(mediabrol);
                }
            }
            catch (Exception eMedia)
            {
                notify(new Notification(Notification.VERBOSE.advancedOperation,"Chargement des media", eMedia, this));
 
            }
            brolDt.Dispose();
            dbCon.Close();
            return mediabrols;
        }
        public MediaBrol loadMediaBrol(int id, bool editing)
        {
            List<MediaBrol> mediabrols = loadSelectedMediaBrols(id);
            /*int[] ids = { id };
            List<MediaBrol> mediabrols = AccessImportExporter.Instance.loadSelectedMediaBrols(ids);*/
            if (mediabrols.Count > 0)
            {
                //if (editing) lockBrol(id);
                return mediabrols[0];
            }
            return new MediaBrol();
        }
        /// <summary>
        /// Load all mediabrols for a brol from the persistant layer.
        /// </summary>
        /// <param name="brol"></param>
        /// <param name="fields">list of fields to load</param>
        /// <returns>(DataTable) table with selected columns</returns>
        public DataTable loadMediaBrols(Brol brol, List<DAOUtils.MEDIABROL_FIELD> fields)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT itemBiblio.id");
            if (fields.Contains(DAOUtils.MEDIABROL_FIELD.name))
                str.Append(", itemBiblio.biblioItemName AS name");
            if (fields.Contains(DAOUtils.MEDIABROL_FIELD.date))
                str.Append(", itemBiblio.insertionDate");
            bool owner = false;
            if (fields.Contains(DAOUtils.MEDIABROL_FIELD.owner))
            {
                str.Append(", [firstName]+\" \"+[lastName] AS owner");
                owner = true;
            }
            str.Append(" FROM");
            if (owner)
                str.Append(" person RIGHT JOIN itemBiblio");
            if (owner)
                str.Append(" ON person.id = itemBiblio.ownerId");
            str.Append(" WHERE itemBiblio.itemId = ");
            str.Append(brol.Id);
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error,"Chargement des exemplaires", e, this));
            }
            brolDt.Dispose();
            dbCon.Close();
            return brolDt;
        }
 
        /// <summary>
        /// Load all available medias for a type from the Access database. 
        /// i.e. for a film : DVD, VHS, CD, etc.
        /// </summary>
        /// <param name="typeId">(int) selected type (film, book, etc.)</param>
        /// <returns>List of Medias</returns>
        public List<Media> loadMedias(int typeId)
        {
            List<Media> medias = new List<Media>();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT media.id AS mi, media.name AS mn, type.id AS ti, type.name AS tn");
            str.Append(" FROM type RIGHT JOIN media ON type.id = media.type");
            str.Append(" WHERE type.id = ");
            str.Append(typeId);
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error,"Chargement des media", e, this));
            }
            String mName;
            BrolType brolType;
            foreach (DataRow brolRow in brolDt.Rows)
            {
                mName = (brolRow["mn"] is DBNull) ? "" : (String)brolRow["mn"];
                brolType = (brolRow["ti"] is DBNull) ? new BrolType() : new BrolType((int)brolRow["ti"], (String)brolRow["tn"]);
                medias.Add(new Media((int)brolRow["mi"], mName, brolType));
            }
            brolDt.Dispose();
            dbCon.Close();
            return medias;
        }
 
 
        /// <summary>
        /// Load brols value objects (only selected fields) for a type from the Access database. 
        /// </summary>
        /// <param name="fields">List of fields to load for the brols</param>
        /// <param name="typeId">
        /// (int) selected type (film, book, etc.)
        /// 0 allow to load all types
        /// </param>
        /// <returns>DataTable with selected fields for Brols</returns>
        public DataTable loadDataTableVos(List<DAOUtils.MEDIABROL_FIELD> fields, int typeId)
        {
            return loadDataTableVos(fields, typeId, null);
        }
 
        /// <summary>
        /// Load brols value objects (only selected fields) for a type from the Access database. 
        /// </summary>
        /// <param name="fields">List of fields to load for the brols</param>
        /// <param name="typeId">
        /// (int) selected type (film, book, etc.)
        /// 0 allow to load all types
        /// </param>
        /// <param name="categories">List of categories to match</param>
        /// <returns>DataTable with selected fields for Brols</returns>
        public DataTable loadDataTableVos(List<DAOUtils.MEDIABROL_FIELD> fields, int typeId, List<BrolCategory> categories)
        {
            return loadDataTableVos(fields, typeId, categories, 0);
        }
 
        /// <summary>
        /// Load brols value objects (only selected fields) for a type from the Access database. 
        /// </summary>
        /// <param name="fields">List of fields to load for the brols</param>
        /// <param name="typeId">
        /// (int) selected type (film, book, etc.)
        /// 0 allow to load all types
        /// </param>
        /// <param name="categories">List of categories to match</param>
        /// <param name="mediaId">media to match</param>
        /// <returns>DataTable with selected fields for Brols</returns>
        public DataTable loadDataTableVos(List<DAOUtils.MEDIABROL_FIELD> fields, int typeId, List<BrolCategory> categories, int mediaId)
        {
            return loadDataTableVos(fields, typeId, categories, mediaId, "");
        }
 
        /// <summary>
        /// Load brols value objects (only selected fields) for a type from the Access database. 
        /// </summary>
        /// <param name="fields">List of fields to load for the brols</param>
        /// <param name="typeId">
        /// (int) selected type (film, book, etc.)
        /// 0 allow to load all types
        /// </param>
        /// <param name="categories">List of categories to match</param>
        /// <param name="mediaId">media to match</param>
        /// <param name="title">(String) string to find into brol's title</param>
        /// <returns>DataTable with selected fields for Brols</returns>
        public DataTable loadDataTableVos(List<DAOUtils.MEDIABROL_FIELD> fields, int typeId, List<BrolCategory> categories, int mediaId, string title)
        {
            bool searchCategories = (categories != null && categories.Count > 0);
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT itemBiblio.id");
            if (fields.Contains(DAOUtils.MEDIABROL_FIELD.name))
                str.Append(", itemBiblio.biblioItemName");
            if (fields.Contains(DAOUtils.MEDIABROL_FIELD.i_title))
                str.Append(", item.itemTitle");
            if (fields.Contains(DAOUtils.MEDIABROL_FIELD.i_date))
                str.Append(", item.pubDate");
            if (fields.Contains(DAOUtils.MEDIABROL_FIELD.date))
                str.Append(", itemBiblio.insertionDate");
            bool owner = false;
            if (fields.Contains(DAOUtils.MEDIABROL_FIELD.owner))
            {
                str.Append(", [firstName]+\" \"+[lastName] AS owner");
                owner = true;
            }
            if (fields.Contains(DAOUtils.MEDIABROL_FIELD.brolId))
                str.Append(", item.id");
            str.Append(" FROM");
            if (owner)
                str.Append(" person RIGHT JOIN (");
            if (searchCategories)
                str.Append(" (categoryItemRelation INNER JOIN item ON categoryItemRelation.itemId = item.id)");
            else
                str.Append(" item");
            str.Append(" INNER JOIN itemBiblio ON item.id = itemBiblio.itemId");
            if (owner)
                str.Append(") ON person.id = itemBiblio.ownerId");
            str.Append(" WHERE item.id>0");
            if (typeId > 0)
            {
                str.Append(" AND item.typeId=");
                str.Append(typeId);
                if (searchCategories)
                {
                    str.Append(" AND categoryItemRelation.categoryId IN(0");
                    foreach(BrolCategory cat in categories)
                    {
                        str.Append(","+cat.Id);
                    }
                    str.Append(")");
                }
                if (mediaId>0)
                {
                    str.Append(" AND itemBiblio.mediaId =");
                    str.Append(mediaId);
                }
            }
            if (!String.Empty.Equals(title))
            {
                str.Append(" AND item.itemTitle LIKE ('");
                str.Append(AccessUtils.escapeAndTrim(title));
                str.Append("')");
            }
            str.Append(" ORDER BY item.itemTitle ASC");
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement des media", e, this));
            }
            brolDt.Dispose();
            dbCon.Close();
            return brolDt;
        }
 
        public bool updateMediaBrol(MediaBrol mediabrol)
        {
            bool updated = true;
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.UpdateCommand = dbCon.CreateCommand();
            dbDa.InsertCommand = dbCon.CreateCommand();
            dbDa.DeleteCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            StringBuilder str = new StringBuilder();
            try
            {
                str.Append("UPDATE itemBiblio SET biblioItemName='");
                str.Append(AccessUtils.escapeAndTrim(mediabrol.Name));
                str.Append("', itemId=");
                str.Append(mediabrol.Brol.Id);
                str.Append(", ownerId=");
                str.Append(mediabrol.Owner.Id);
                str.Append(", mediaId=");
                str.Append(mediabrol.MediaType.Id);
                str.Append(", comment='");
                str.Append(AccessUtils.escapeAndTrim(mediabrol.Comment));
                str.Append("', localisation='");
                str.Append(AccessUtils.escapeAndTrim(mediabrol.Localisation));
                str.Append("'");
                if (!mediabrol.InsertionDate.Equals(new DateTime(0L)))
                {
                    str.Append(", insertionDate=");
                    str.Append(AccessUtils.getAccessDate(mediabrol.InsertionDate));
                }
                str.Append(" WHERE id = ");
                str.Append(mediabrol.Id);
                dbDa.UpdateCommand.CommandText = str.ToString();
                notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
                int result = dbDa.UpdateCommand.ExecuteNonQuery();
                if (result < 1) updated = false;
                if (result > 1)
                    notify(new Notification(Notification.VERBOSE.error, "ERREUR modifications multiples exemplaire", this));
                //todo : check multiple insertion sql request
                if (mediabrol.Qualities != null)
                {
                    foreach (Quality q in mediabrol.Qualities)
                    {
                        switch (q.Status)
                        {
                            case STATUS.toAdd:
                                str = new StringBuilder("INSERT INTO itemBiblioQualityRelation (itemBiblioId,qualityId) VALUES(");
                                str.Append(mediabrol.Id);
                                str.Append(",");
                                str.Append(q.Id);
                                str.Append(")");
                                dbDa.InsertCommand.CommandText = str.ToString();
                                notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.InsertCommand.CommandText, this));
                                dbDa.InsertCommand.ExecuteNonQuery();
                                break;
                            case STATUS.toDelete:
                                StringBuilder strDel = new StringBuilder("DELETE FROM itemBiblioQualityRelation WHERE itemBiblioId = ");
                                strDel.Append(mediabrol.Id);
                                strDel.Append(" AND qualityId = ");
                                strDel.Append(q.Id);
                                dbDa.DeleteCommand.CommandText = strDel.ToString();
                                notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.DeleteCommand.CommandText, this));
                                dbDa.DeleteCommand.ExecuteNonQuery();
                                break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                notify(new Notification(Notification.VERBOSE.error, "Insertion d'un exemplaire", ex, this));
                updated = false;
            }
            dbCon.Close();
            str = new StringBuilder("L'exemplaire \"");
            str.Append(mediabrol.Name);
            str.Append("\" de l'ouvrage \"");
            str.Append(mediabrol.Brol.Title);
            str.Append((updated)?"\" est mis à jour.":"\" ne peut être mis à jour.");
            notify(new Notification(Notification.VERBOSE.opsResult, str.ToString(), this));
            return updated;
        }
 
        public bool lockMediaBrol(int id)
        {
            notify(new Notification(Notification.VERBOSE.lowError, "not implemented", this)); 
            throw new Exception("The method or operation is not implemented."); 
 
        }
 
        public void unlockMediaBrol(int id)
        {
            notify(new Notification(Notification.VERBOSE.lowError, "not implemented", this));
        }
        /// <summary>
        /// If no currents borrows, 
        /// delete a mediabrol, all associated borrows, and all qualities relations
        /// </summary>
        /// <param name="mediabrolId"></param>
        /// <param name="deleteBrol">Set true to delete associated brol</param>
        /// <returns></returns>
        public bool deleteMediaBrol(int mediabrolId, String title, bool deleteBrol)
        {
            bool deleted = false;
            StringBuilder str = new StringBuilder("L'exemplaire n°");
            str.Append(mediabrolId);
            str.Append(" \"");
            str.Append(title);
            if (cleanBorrows(mediabrolId, false) > -1)
            {
                DbConnection dbCon = dbpf.CreateConnection();
                DbDataAdapter dbDa = dbpf.CreateDataAdapter();
                dbDa.DeleteCommand = dbCon.CreateCommand();
                dbCon.ConnectionString = conStr;
                dbCon.Open();
                String strDel = "DELETE FROM itemBiblioQualityRelation WHERE itemBiblioId =" + mediabrolId;
                notify(new Notification(Notification.VERBOSE.persistentOperation, strDel, this));
                dbDa.DeleteCommand.CommandText = strDel;
                dbDa.DeleteCommand.ExecuteNonQuery();
                int brolId = 0;
                if (deleteBrol)
                {
                    //get brol id
                    strDel = "SELECT itemId FROM itemBiblio WHERE id =" + mediabrolId;
                    notify(new Notification(Notification.VERBOSE.persistentOperation, strDel, this));
                    dbDa.SelectCommand = dbCon.CreateCommand();
                    dbDa.SelectCommand.CommandText = strDel;
                    brolId = (int)dbDa.SelectCommand.ExecuteScalar();
                }
                strDel = "DELETE FROM itemBiblio WHERE id =" + mediabrolId;
                notify(new Notification(Notification.VERBOSE.persistentOperation, strDel, this));
                dbDa.DeleteCommand.CommandText = strDel;
                dbDa.DeleteCommand.ExecuteNonQuery();
                if (deleteBrol)
                {
                    strDel = "SELECT COUNT(id) FROM itemBiblio WHERE itemId =" + brolId;
                    notify(new Notification(Notification.VERBOSE.persistentOperation, strDel, this));
                    dbDa.SelectCommand = dbCon.CreateCommand();
                    dbDa.SelectCommand.CommandText = strDel;
                    //test if we have more than this mediabrol for this brol
                    if ((int)dbDa.SelectCommand.ExecuteScalar() == 0)
                    {
                        //call accessbroldao to
                        //delete this brol
                        AccessBrolDao.Instance.deleteBrol(new Brol(brolId,title,""));
                    }
                }
                dbCon.Close();
                deleted = true;
                str.Append("\" est supprimé.");
            }
            else
            {
                str.Append("\" ne peut être supprimé.");
                str.AppendLine("\nIl faut d'abord clôturer l'emprunt en cours pour cet exemplaire.");
            }
            notify(new Notification(Notification.VERBOSE.opsResult, str.ToString(), this));
            return deleted;
        }
        /// <summary>
        /// If no currents borrows, 
        /// delete a mediabrol, all associated borrows, and all qualities relations
        /// </summary>
        /// <param name="mediabrol"></param>
        /// <returns></returns>
        public bool deleteMediaBrol(MediaBrol mediabrol)
        {
            return deleteMediaBrol(mediabrol.Id, mediabrol.Brol.Title, false);
        }
        #endregion
 
        #region borrows
        public List<Borrow> loadBorrows(Person person)
        {
            notify(new Notification(Notification.VERBOSE.lowError, "not implemented", this));
            throw new Exception("The method or operation is not implemented.");
        }
 
        /// <summary>
        /// Load borrows and fill a DataTable. 
        /// If mediabrolId equals 0, all borrows are loaded. 
        /// </summary>
        /// <param name="mediabrolId">(int) Selected mediabrol id</param>
        /// <param name="borrowFields">List of fields to load for the borrows</param>
        /// <param name="typeId">
        /// (int) selected type (film, book, etc.)
        /// 0 allow to load all types
        /// </param>
        /// <param name="title">(String) string to find into brol's title</param>
        /// <returns>(DataTable) DataTable with borrows</returns>
        public DataTable loadBorrows(int mediabrolId, List<DAOUtils.BORROW_FIELD> fields, int typeId, bool closed, string title)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT ");
            if (fields.Contains(DAOUtils.BORROW_FIELD.id))
                str.Append("itemBiblio.id");
            else
                str.Append("borrow.id");
            if (fields.Contains(DAOUtils.BORROW_FIELD.type))
                str.Append(", type.name as typeName");
            if (fields.Contains(DAOUtils.BORROW_FIELD.borrower))
                str.Append(" ,[firstName]+\" \"+[lastName] AS borrower");
            if (fields.Contains(DAOUtils.BORROW_FIELD.name))
                str.Append(", biblioItemName AS name");
            if (fields.Contains(DAOUtils.BORROW_FIELD.startDate))
                str.Append(" ,borrow.startDate");
            if (fields.Contains(DAOUtils.BORROW_FIELD.endDate))
                str.Append(" ,borrow.endDate");
            if (fields.Contains(DAOUtils.BORROW_FIELD.planDate))
                str.Append(" ,borrow.planDate");
            if (fields.Contains(DAOUtils.BORROW_FIELD.comment))
                str.Append(" ,borrow.comment");
            if (fields.Contains(DAOUtils.BORROW_FIELD.title))
                str.Append(" ,item.itemTitle AS title");
            if (fields.Contains(DAOUtils.BORROW_FIELD.brolId))
                str.Append(" ,item.id AS brolId");
            str.Append(" FROM ");
            /*if (fields.Contains(DAOUtils.BORROW_FIELD.title))
            {*/
            str.Append("(person INNER JOIN ((borrow");
            str.Append(" LEFT JOIN itemBiblio ON borrow.itemBiblioId = itemBiblio.id)");
            str.Append(" LEFT JOIN item ON itemBiblio.itemId = item.id)");
            str.Append(" ON person.id = borrow.personId)");
            if (fields.Contains(DAOUtils.BORROW_FIELD.type))
                str.Append(" LEFT JOIN type ON item.typeId = type.id");
            /*}
            else
            {
                str.Append("(person RIGHT JOIN borrow ON person.id = borrow.personId)");
                str.Append(" LEFT JOIN itemBiblio ON borrow.itemBiblioId = itemBiblio.id");
            }*/
            str.Append(" WHERE itemBiblio.id>0");
            if (typeId > 0)
            {
                str.Append(" AND item.typeId = ");
                str.Append(typeId);
            }
            if (mediabrolId > 0)
            {
                str.Append(" AND itemBiblio.id = ");
                str.Append(mediabrolId);
            }
            if (!closed)
            {
                str.Append(" AND borrow.endDate IS NULL ");
            }
            if (!String.Empty.Equals(title))
            {
                str.Append(" AND item.itemTitle LIKE ('");
                str.Append(AccessUtils.escapeAndTrim(title));
                str.Append("')");
            }
            str.Append(" ORDER BY borrow.startDate 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", e, this));
            }
            dbCon.Close();
            return brolDt;
        }
        /// <summary>
        /// Load selected borrow
        /// </summary>
        /// <param name="mediabrol"></param>
        /// <returns></returns>
        public Borrow loadBorrow(int borrowId)
        {
            Borrow borrow;
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT person.*, borrow.*");
            str.Append(" FROM (person RIGHT JOIN borrow ON person.id = borrow.personId)");
            str.Append(" LEFT JOIN itemBiblio ON borrow.itemBiblioId = itemBiblio.id");
            str.Append(" WHERE borrow.id = ");
            str.Append(borrowId);
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.lowError, "Aucun emprunt à charger (ou alors la table est ouverte par un autre utilisateur)", e, this));
                //we may have exception if we have _ to fill
            }
            DataRow row = brolDt.Rows[0];
            borrow = new Borrow(
                borrowId,
                (row["itemBiblioId"] is DBNull) ? 0 : (int)row["itemBiblioId"],
                (row["startDate"] is DBNull) ? new DateTime(0L) : (DateTime)row["startDate"],
                (row["endDate"] is DBNull) ? new DateTime(0L) : (DateTime)row["endDate"],
                (row["planDate"] is DBNull) ? new DateTime(0L) : (DateTime)row["planDate"],
                getBorrower(row),
                (row["comment"] is DBNull) ? "" : (String)row["comment"]
                );
            brolDt.Dispose();
            dbCon.Close();
            return borrow;
        }
        private Person getBorrower(DataRow row)
        {
            if (row["person.id"] is DBNull || row["lastName"] is DBNull)
                return new Person();
            Person p = new Person((int)row["person.id"], (String)row["lastName"]);
            try
            {
 
                if (!(row["firstName"] is DBNull))
                {
                    p.FirstName = (String)row["firstName"];
                }
                if (!(row["personLocked"] is DBNull))
                {
                    p.Edited = !row["personLocked"].ToString().Equals("False");
                }
                if (!(row["sex"] is DBNull))
                {
                    SEX sex;
                    try
                    {
                        sex = (SEX)Enum.Parse(typeof(SEX), (String)row["sex"], false);
                    }
                    catch (Exception)
                    {
                        sex = SEX._;
                    }
                    p.Sex = sex;
                }
                if (!(row["birthdate"] is DBNull))
                {
                    p.Birthdate = (DateTime)row["birthdate"];
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error,"Chargement de l'emprunteur", e, this));
            }
            return p;
        }
        private bool isBorrowed(int mediabrolId, DbDataAdapter dbDa, DbConnection dbCon)
        {
            String str = String.Format("SELECT COUNT(0) FROM borrow WHERE itemBiblioId = {0} AND endDate IS NULL ", mediabrolId);
 
            if(dbDa.SelectCommand==null)
                dbDa.SelectCommand = dbCon.CreateCommand();
            dbDa.SelectCommand.CommandText = str;
 
            bool conOpen = dbCon.State.Equals(ConnectionState.Open);
            if (!conOpen)
                dbCon.Open();
            int r = (int)dbDa.SelectCommand.ExecuteScalar();
            notify(new Notification(Notification.VERBOSE.debug, String.Format("{0}\n\nResult : {1}",dbDa.SelectCommand.CommandText,r), this));
            if (!conOpen)
                dbCon.Close();
            return r > 0;
        }
        /// <summary>
        /// Start a borrow if not borrowed
        /// </summary>
        /// <param name="mediabrolId">(int) id of the mediabrol to borrow</param>
        /// <param name="borrowerId">(int) id of the borrower</param>
        /// <returns></returns>
        public bool startBorrow(int mediabrolId, int borrowerId)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.InsertCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
 
            if (isBorrowed(mediabrolId, dbDa, dbCon)) return false;
            StringBuilder str = new StringBuilder();
            str.Append("INSERT INTO borrow (itemBiblioId,personId,startDate) VALUES (");
            str.Append(mediabrolId);
            str.Append(", ");
            str.Append(borrowerId);
            str.Append(", ");
            str.Append(AccessUtils.getAccessDate(DateTime.Now));
            str.Append(")");
            dbDa.InsertCommand.CommandText = str.ToString();
            try
            {
                notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.InsertCommand.CommandText, this));
                dbDa.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                notify(new Notification(Notification.VERBOSE.error, "Insertion d'un emprunt", ex, this));
                return false;
            }
            finally
            {
                dbCon.Close();
            }
            return true;
        }
 
        public bool stopBorrow(int borrowId)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.UpdateCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            StringBuilder str = new StringBuilder("UPDATE borrow SET endDate=");
            str.Append(AccessUtils.getAccessDate(DateTime.Now));
            str.Append(" WHERE id=");
            str.Append(borrowId);
            dbDa.UpdateCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
            try
            {
                dbDa.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Retour d'emprunt", e, this));
                return true;
            }
            finally
            {
                dbCon.Close();
            }
            return true;
        }
 
        public bool stopBorrow(MediaBrol mediabrol)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.UpdateCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            dbDa.UpdateCommand.CommandText = String.Format(
                "UPDATE borrow SET endDate={0} WHERE id=(SELECT id FROM borrow WHERE itemBiblioId = {1} AND endDate IS NULL)",
                AccessUtils.getAccessDate(DateTime.Now),
                mediabrol.Id
                );
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
            try
            {
                dbDa.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Retour d'emprunt", e, this));
                return true;
            }
            finally
            {
                dbCon.Close();
            }
            return true;
        }
 
        /// <summary>
        /// Delete all borrows for a mediabrol
        /// </summary>
        /// <param name="mediabrolId">(int) mediabrol identifier</param>
        public int cleanBorrows(int mediabrolId, bool forceDelete)
        {
            int result = -1;
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbDa.DeleteCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            StringBuilder str = new StringBuilder("SELECT COUNT(id) FROM borrow WHERE itemBiblioId =");
            str.Append(mediabrolId);
            str.Append(" AND endDate IS Null");
            dbDa.SelectCommand.CommandText = str.ToString();
            result = (int)dbDa.SelectCommand.ExecuteScalar();
            str.AppendLine("\nResult of this operation : ");
            str.Append(result);
            notify(new Notification(Notification.VERBOSE.persistentOperation, str.ToString(), this));
            if ((result > 0) && (!forceDelete))
            {
                notify(new Notification(Notification.VERBOSE.lowError, "Il n'est pas possible de supprimer l'exemplaire car il est en prêt", this));
                result = -1;
            }
            else
            {
                str = new StringBuilder("DELETE FROM borrow WHERE itemBiblioId =");
                str.Append(mediabrolId);
                notify(new Notification(Notification.VERBOSE.persistentOperation, str.ToString(), this));
                dbDa.DeleteCommand.CommandText = str.ToString();
                result = dbDa.DeleteCommand.ExecuteNonQuery();
            }
            dbCon.Close();
            return result;
        }
        /// <summary>
        /// Delete selected borrow
        /// </summary>
        /// <param name="selectedBorrowId">(int) borrow identifier</param>
        public void cleanBorrow(int selectedBorrowId)
        {
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.DeleteCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            dbCon.Open();
            String str = "DELETE FROM borrow WHERE id =" + selectedBorrowId;
            notify(new Notification(Notification.VERBOSE.persistentOperation, str, this));
            dbDa.DeleteCommand.CommandText = str;
            int result = dbDa.DeleteCommand.ExecuteNonQuery();
            dbCon.Close();
        }
        /// <summary>
        /// Update a borrow
        /// </summary>
        /// <param name="borrow"></param>
        public bool updateBorrow(Borrow borrow)
        {
            if (borrow.Borrower == null ||
                borrow.Borrower.Id <= 0 ||
                borrow.MediabrolId <= 0)
            {
                notify(new Notification(Notification.VERBOSE.error, "Impossible de sauver les modifications apportées à l'emprunt.", this));
                return false;
            }
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.UpdateCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("UPDATE borrow SET ");
            str.Append("comment='");
            str.Append(AccessUtils.escapeAndTrim(borrow.Comment));
            str.Append("', personId=");
            str.Append(borrow.Borrower.Id);
            str.Append(", itemBiblioId=");
            str.Append(borrow.MediabrolId);
            str.Append(", startDate=");
            if (!borrow.StartDate.Equals(new DateTime(0L)))
            {
                str.Append(AccessUtils.getAccessDate(borrow.StartDate));
            }
            else str.Append("NULL");
            str.Append(", planDate=");
            if (!borrow.PlanDate.Equals(new DateTime(0L)))
            {
                str.Append(AccessUtils.getAccessDate(borrow.PlanDate));
            }
            else str.Append("NULL");
            str.Append(", endDate=");
            if (!borrow.EndDate.Equals(new DateTime(0L)))
            {
                str.Append(AccessUtils.getAccessDate(borrow.EndDate));
            }
            else str.Append("NULL");
            str.Append(" WHERE id=");
            str.Append(borrow.Id);
            dbCon.Open();
            dbDa.UpdateCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.UpdateCommand.CommandText, this));
            try
            {
                dbDa.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.criticalError, "Retour d'emprunt", e, this));
                return true;
            }
            finally
            {
                dbCon.Close();
            }
            return true;
        } 
        #endregion
 
        #region qualities
 
        private int insertQualities(MediaBrol mediabrol, DbDataAdapter dbDa)
        {
            if (mediabrol.Id < 0) return -1;
            int i = 0;
            foreach (Quality q in mediabrol.Qualities)
            {
                if (q.Status.Equals(STATUS.toAdd))
                {
                    ++i;
                    insertQuality(q, mediabrol.Id, dbDa.InsertCommand);
                }
                else notify(new Notification(Notification.VERBOSE.error, "Insertion des qualités : ERREUR (status != toAdd)", this));
            }
            return i;
        }
        private bool insertQuality(Quality q, int mediabrolId, DbCommand com)
        {
            if (q.Status.Equals(STATUS.toAdd))
            {
                StringBuilder str = new StringBuilder();
                str.Append("INSERT INTO itemBiblioQualityRelation (itemBiblioId, qualityId) VALUES(");
                str.Append(mediabrolId);
                str.Append(",");
                str.Append(q.Id);
                str.Append(");");
                com.CommandText = str.ToString();
                notify(new Notification(Notification.VERBOSE.persistentOperation, com.CommandText, this));
                return (com.ExecuteNonQuery() == 1);
            }
            notify(new Notification(Notification.VERBOSE.error, "Insertion d'une qualité : ERREUR (status != toAdd)", this));
            return false;
        }
        public List<Quality> loadQualities(int itemBiblioBrolId)
        {
            List<Quality> qualities;
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            qualities = loadQualitiesCommand(itemBiblioBrolId, dbDa);
            dbCon.Close();
            return qualities;
        }
 
        internal List<Quality> loadQualitiesCommand(int itemBiblioBrolId, DbDataAdapter dbDa)
        {
            List<Quality> qualities = new List<Quality>();
            StringBuilder str = new StringBuilder("SELECT quality.* ");
            str.Append("FROM itemBiblioQualityRelation AS r ");
            str.Append("LEFT JOIN quality ON r.qualityId = quality.id ");
            str.Append("WHERE r.itemBiblioId = ");
            str.Append(itemBiblioBrolId);
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement des caractéristiques du support", e, this));
            }
            foreach (DataRow brolRow in brolDt.Rows)
            {
                qualities.Add(new Quality(
                    (int)brolRow["id"],
                    (brolRow["qualName"] is DBNull) ? "" : (String)brolRow["qualName"],
                    (brolRow["qualValue"] is DBNull) ? "" : (String)brolRow["qualValue"]
                    ));
            }
            brolDt.Dispose();
            return qualities;
        }
 
        public List<Quality> loadQualities(BrolType brolType)
        {
            List<Quality> qualities = new List<Quality>();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbDa.SelectCommand = dbCon.CreateCommand();
            dbCon.ConnectionString = conStr;
            StringBuilder str = new StringBuilder("SELECT * FROM quality WHERE typeId = ");
            str.Append(brolType.Id);
            dbDa.SelectCommand.CommandText = str.ToString();
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            DataTable brolDt = new DataTable();
            try
            {
                dbDa.Fill(brolDt);
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.error, "Chargement des caractéristiques du support", e, this));
            }
            foreach (DataRow brolRow in brolDt.Rows)
            {
                qualities.Add(new Quality(
                    (int)brolRow["id"],
                    (brolRow["qualName"] is DBNull) ? "" : (String)brolRow["qualName"],
                    (brolRow["qualValue"] is DBNull) ? "" : (String)brolRow["qualValue"]
                    ));
            }
            brolDt.Dispose();
            dbCon.Close();
            return qualities;
        }
        #endregion
 
        #endregion
    }
}