AccessImporterDao.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
using System.Collections;
using System.Data.Common;
using System.Data;
using System.Drawing;
using be.gaudry.observer;
using be.gaudry.model;
 
namespace be.gaudry.bibliobrol.model.dao.msaccess
{
    public sealed class AccessImporterDao : Observable, IImporterDao
    {
        private String conStr;
 
        #region singleton
        static AccessImporterDao instance = null;
        static readonly object padlock = new object();
        private DbProviderFactory dbpf;
        AccessImporterDao()
        {
            notify(new Notification(Notification.VERBOSE.debug, "AccessImporterDao singleton call", this));
            dbpf = ((AccessFactory)AccessFactory.Instance).getDbpf();
            conStr = ((AccessFactory)AccessFactory.Instance).getConnectionString();
        }
        public static AccessImporterDao Instance
        {
            get
            {
                lock (padlock)
                {
                    if (instance == null)
                    {
                        instance = new AccessImporterDao();
                    }
                    return instance;
                }
            }
        }
        #endregion
 
        #region IImporter Members
        /// <summary>
        /// Deserialize images
        /// </summary>
        /// <param name="path">(String) path of binary import file</param>
        /// <returns>A list of images</returns>
        public List<Image> importImages(string path)
        {
            FileStream fs = null;
            try
            {
                fs = new FileStream(path, FileMode.Open, FileAccess.Read);
                List<Image> images = (List<Image>)new BinaryFormatter().Deserialize(fs);
                return images;
            }
            catch (FileNotFoundException)
            {
                notify(new Notification(Notification.VERBOSE.criticalError, "Importation des images", String.Format("Fichier {0} non trouvé.", path), this));
                return new List<Image>();
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.criticalError, "Importation des images", e, this));
                return new List<Image>();
            }
            finally
            {
                fs.Close();
            }
        }
 
        /// <summary>
        /// Deserialize mediabrols
        /// Search existings mediabrols which not match exactly with new list items (don't check id's).
        /// </summary>
        /// <param name="path">(String) path of binary import file</param>
        public void importBinaryMediaBrol(string path)
        {
            DateTime startDateTime = DateTime.Now;
 
            FileStream fs = null;
            Object deserializedList = null;
            int count = 0;
            try
            {
                fs = new FileStream(path, FileMode.Open, FileAccess.Read);
                deserializedList = new BinaryFormatter().Deserialize(fs);
                count = castList(deserializedList);
            }
            catch (FileNotFoundException)
            {
                notify(new Notification(Notification.VERBOSE.criticalError, "Importation", String.Format("Fichier {0} non trouvé.",path), this));
                return;
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.criticalError, "Importation", e, this));
                return;
            }
            finally
            {
                if (fs != null) fs.Close();
 
                notify(new Notification(Notification.VERBOSE.internalNotification, "lastBrol", String.Format("Fin des {0} chargements", count), this));
                notify(new Notification(Notification.VERBOSE.internalNotification, "End", String.Format("Fin de tous les traitements ({0})", Units.getDelay(startDateTime)), this));
            }
        }
        #endregion
 
        #region private methods
        /// <summary>
        /// Test if deserialized object matches with a list of brols or a list of mediabrols
        /// And call appropriate method to import items
        /// </summary>
        /// <param name="deserializedList"></param>
        private int castList(object deserializedList)
        {
            int count;
            if (deserializedList is List<Brol>)
            {
                //first attempt : is it a list of brols ?
                List<Brol> importedList = (List<Brol>)deserializedList;
                count = importedList.Count;
                notify(new Notification(Notification.VERBOSE.internalNotification, "count", count.ToString(), this));
                importBrols(importedList);
            }
            else if (deserializedList is List<MediaBrol>)
            {
                //second attempt : is it a list of mediabrols ?
                List<MediaBrol> importedList = (List<MediaBrol>)deserializedList;
                count = importedList.Count;
                notify(new Notification(Notification.VERBOSE.internalNotification, "count", count.ToString(), this));
                importMediabrols(importedList);
            }
            else
            {
                count = 0;
                notify(new Notification(Notification.VERBOSE.error, "Importation", "Le fichier importé ne comporte ni ouvrage, ni exemplaire. Il est possible qu'il soit corrompu." , this));
            }
            return count;
        }
 
        private void saveConflicts(List<IBrol> conflictList)
        {
           if (conflictList.Count > 0)
            {
                DateTime dt = DateTime.Now;
                StringBuilder saveConflictPath = new StringBuilder(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments));
                saveConflictPath.Append(System.IO.Path.DirectorySeparatorChar);
                saveConflictPath.Append("brolConflict");
                saveConflictPath.Append(dt.Year);
                if (dt.Month < 10) saveConflictPath.Append("0");
                saveConflictPath.Append(dt.Month);
                if (dt.Day < 10) saveConflictPath.Append("0");
                saveConflictPath.Append(dt.Day);
                if (dt.Minute < 10) saveConflictPath.Append("0");
                saveConflictPath.Append(dt.Minute);
                saveConflictPath.Append(".bed");
                StringBuilder str = new StringBuilder(String.Format(
                    "{0} éléments semblent déjà présents dans l'application. Vous pouvez toutefois importer ces éléments depuis le fichier suivant : {1}\n\nListe des éléments de ce fichier :\n\n", 
                    conflictList.Count,
                    saveConflictPath.ToString()));
                if(conflictList[0].GetType().Equals(typeof(Brol)))
                {
                    foreach (Brol brol in conflictList)
                        str.AppendLine(brol.Title);
                }
                else if(conflictList[0].GetType().Equals(typeof(MediaBrol)))
                {
                    foreach (MediaBrol mediabrol in conflictList)
                        str.AppendLine(mediabrol.Brol.Title);
                } 
                //exportation of conflicts list
                BinaryFormatter bf = new BinaryFormatter();
                FileStream fs = AccessExporterDao.Instance.getFileStream(saveConflictPath.ToString(), FileMode.OpenOrCreate, FileAccess.Write);
                if (fs == null)
                {
                    notify(new Notification(Notification.VERBOSE.criticalError, "Exportation des conflits", "Impossible de trouver un flux de sortie", this));
                    str.AppendLine("ERREUR : Impossible de trouver un flux de sortie");
                }
                else
                {
                    try
                    {
                        bf.Serialize(fs, conflictList);
                        notify(new Notification(Notification.VERBOSE.internalNotification, "Exportation des conflits", String.Format("Les éléments en conflits ont été sauvés sous {0}",saveConflictPath.ToString()), this));
                    }
                    catch (Exception eSer)
                    {
                        notify(new Notification(Notification.VERBOSE.criticalError, "Exportation", eSer, this));
                        str.AppendLine(eSer.Message);
                    }
                    finally
                    {
                        fs.Close();
                    }
                }
                notify(new Notification(Notification.VERBOSE.opsResult, "Exportation", str.ToString(), this));
            }
        }
 
        /// <summary>
        /// Check imported mediabrols. 
        /// If a mediabrol don't exists, insert it.
        /// If an actor or a categorie don't exists, insert it.
        /// </summary>
        /// <param name="importedList">Deserialized mediabrols</param>
        private void importMediabrols(List<MediaBrol> importedList)
        {
            notify(new Notification(Notification.VERBOSE.internalNotification, "Mediabrols", 
                "Le fichier contient des exemplaires. Cette méthode n'est pour l'instant implémentée que pour des ouvrages.", this));
        }
        /// <summary>
        /// Check imported brols. 
        /// If a brol don't exists, insert it.
        /// If an actor or a categorie don't exists, insert it.
        /// </summary>
        /// <param name="importedList">Deserialized brols</param>
        private void importBrols(List<Brol> importedList)
        {
            int foundId;
            List<IBrol> conflicts = new List<IBrol>();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbCon.ConnectionString = conStr;
            dbDa.SelectCommand = dbCon.CreateCommand();
            //todo : getTypesHashtable with , dbCon, dbDa
            Hashtable types = getTypesHashtable();
            //todo : getCategoriesHashtable with , dbCon, dbDa
            Hashtable categories = getCategoriesHashtable(dbCon, dbDa);
            //todo : getPersonsHashtable with , dbCon, dbDa
            Hashtable persons = getPersonsHashtable();
 
            // load all persons and categories form bibliobrol to adapt ids 
            // and check if we need to insert new item
 
            //Check imported brols
            dbCon.Open();
            foreach (Brol importedBrol in importedList)
            {
                foundId = 0;
                notify(new Notification(Notification.VERBOSE.internalNotification, "count2", "5", this));
                //test if type exists
                if (types.ContainsKey(importedBrol.BrolType.Name.ToUpper()))
                    importedBrol.BrolType.Id = (int)types[importedBrol.BrolType.Name.ToUpper()];
                else
                {
                    //add type if not exists
                    //modify brol type id
                    importedBrol.BrolType.Id = AccessConfigDao.Instance.insertBrolType(importedBrol.BrolType, dbCon, dbDa);
                    //insert into existing
                    types.Add(importedBrol.BrolType.Name.ToUpper(), importedBrol.BrolType.Id);
                    notify(new Notification(Notification.VERBOSE.internalNotification, "Ajout d'un type", String.Format("Ajout du type {0} dans l'application"), this));
                }
                notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 1", this));
                //test if brol exists
                dbDa.SelectCommand.CommandText = String.Format("SELECT id FROM item WHERE UCASE(itemTitle)='{0}'", AccessUtils.escapeAndTrim(importedBrol.Title.ToUpper()));
                notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
                try
                {
                    foundId = (int)dbDa.SelectCommand.ExecuteScalar();
                    notify(new Notification(Notification.VERBOSE.debug, "foundId : " + foundId + " for " + importedBrol.Title, "", this));
                }
                catch (Exception){}
 
                notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 2", this));
                if (foundId > 0)
                {
                    //conflict
                    notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 3", this));
                    //importedBrol.Id = foundId;
                    notify(new Notification(Notification.VERBOSE.internalNotification, "Conflit", String.Format("L'élément {0} semble en conflit avec un élément existant.", importedBrol.Title), this));
                    notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 4", this));
                    conflicts.Add(importedBrol);}
                else
                    {
                        insertImportedBrol(importedBrol, dbCon, dbDa, types, categories, persons);
                        notify(new Notification(Notification.VERBOSE.internalNotification, "Import", String.Format("L'élément {0} est correstement importé.", importedBrol.Title), this));
                    }
                notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 5", this));
                notify(new Notification(Notification.VERBOSE.internalNotification, "brolEnd", "", this));
            }
            dbCon.Close();
            saveConflicts(conflicts);
        }
        /// <summary>
        /// Insert a new brol from import file
        /// </summary>
        /// <param name="importedBrol"></param>
        /// <param name="dbCon"></param>
        /// <param name="dbDa"></param>
        /// <param name="types"></param>
        /// <param name="categories"></param>
        /// <param name="persons"></param>
        /// <returns></returns>
        private int insertImportedBrol(Brol importedBrol, DbConnection dbCon, DbDataAdapter dbDa, Hashtable types, Hashtable categories, Hashtable persons)
        {
            if (dbDa.InsertCommand == null)
                dbDa.InsertCommand = dbCon.CreateCommand();
            String key;
            importedBrol.Id = -1;//we need to set this value to insert as new item (!!!! otherwise we UPDATE AN EXISTING ITEM !!!)
            foreach (BrolCategory cat in importedBrol.Categories)
            {
                key = cat.Name.ToUpper()+importedBrol.BrolType.Id;
                //exists into bibliobrol
                if (categories.Contains(key))
                {
                    cat.Id = (int)categories[key];
                }
                else
                {
                    cat.Status = STATUS.toAppAdd;
                    //insert new category into bibliobrol and set new id
                    cat.Id = AccessConfigDao.Instance.insertCategory(cat, importedBrol.BrolType, dbCon, dbDa);
                    notify(new Notification(Notification.VERBOSE.internalNotification, "Ajout", String.Format("La catégorie {0} est ajoutée.", cat.Name), this));
                    categories.Add(key, cat.Id);
                }
                cat.Status = STATUS.toAdd;
            }
            notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 3", this));
 
            foreach (Actor actor in importedBrol.Actors)
            {
                key = getPersonKey(actor);
                //exists into bibliobrol
                if (persons.Contains(key))
                {
                    actor.Id = (int)persons[key];
                }
                else
                {
                    //todo : if person img exists, prompt to delete it (it is probably an img from another actor)
                    actor.Status = STATUS.toAppAdd;
                    actor.Id = -1;//to force insertion
 
                    actor.Id = AccessPersonDao.Instance.insertPerson(actor, dbCon, dbDa, new StringBuilder());
                    notify(new Notification(Notification.VERBOSE.internalNotification, "Ajout", String.Format("La personne {0} est ajoutée.", actor.Display), this));
                    persons.Add(key, actor.Id);
                }
                actor.Status = STATUS.toAdd;
            }
            notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 4", this));
            return AccessBrolDao.Instance.insertBrol(importedBrol, dbCon, dbDa, true);
        }
 
        /// <summary>
        /// Check imported brols. 
        /// If a brol don't exists, insert it.
        /// If an actor or a categorie don't exists, insert it.
        /// </summary>
        /// <param name="importedList">Deserialized mediabrols</param>
        /// <returns>(List IBrol)List of conflict brols</returns>
        private List<IBrol> importConflictBrols(List<IBrol> importedList)
        {
            /*Brol existingBrol;
            List<Brol> existingBrols = null;
            int[] brolIds = new int[1];
            StringBuilder str = new StringBuilder("Résultat de l'importation");
            Hashtable categories = getCategoriesHashtable();
            Hashtable persons = getPersonsHashtable();
            DbConnection dbCon = dbpf.CreateConnection();
            DbDataAdapter dbDa = dbpf.CreateDataAdapter();
            dbCon.ConnectionString = conStr;
            dbDa.SelectCommand = dbCon.CreateCommand();
 
            // load all persons and categories form bibliobrol to adapt ids 
            // and check if we need to insert new item
 
            //todo : loadpersons with , dbCon, dbDa
            //todo : loadCategories with , dbCon, dbDa
 
            //Check imported brols
            String key;//key to test
            foreach (Brol importedBrol in importedList)
            {
                notify(new Notification(Notification.VERBOSE.internalNotification, "count2", "6", this));
                notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 1", this));
                //load brol if exists
                //ds = new DataSet();
                dbDa.SelectCommand.CommandText = String.Format("SELECT id FROM item WHERE UCASE(itemTitle)='{0}'", importedBrol.Title.Trim().ToUpper().Replace("'", "''"));
                notify(new Notification(Notification.VERBOSE.advancedOperation, dbDa.SelectCommand.CommandText, this));
                dbCon.Open();
                try
                {
                    brolIds[0] = (int)dbDa.SelectCommand.ExecuteScalar();
                    existingBrols = AccessExporterDao.Instance.loadSelectedBrols(brolIds, dbCon, dbDa, false);
                }
                catch (Exception) { }
                dbCon.Close();
 
                notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 2", this));
                if (existingBrols != null && existingBrols.Count > 0)
                {
                    existingBrol = existingBrols[0];
                }
                else
                {
                    existingBrol = new Brol();
                    existingBrol.Title = importedBrol.Title;
                }
                foreach (BrolCategory cat in importedBrol.Categories)
                {
                    key = cat.Name.ToUpper() + existingBrol.BrolType.Id;
                    //exists into bibliobrol
                    if (categories.Contains(key))
                    {
                        cat.Id = (int)categories[key];
 
                        //check with found Brol item
                        cat.Status = STATUS.none;
                        foreach (BrolCategory tempCat in existingBrol.Categories)
                        {
                            if (tempCat.Id == cat.Id)
                            {
                                cat.Status = STATUS.toAdd;
                                break;
                            }
                        }
                        //cat exists into bibliobrol, but not into existing brol
                        if (cat.Status.Equals(STATUS.toAdd))
                        {
                            existingBrol.addCategory(cat);
                        }
                    }
                    //don't exists into bibliobrol (and don't exists into existing brol)
                    else
                    {
                        cat.Status = STATUS.toAppAdd;
                        //insert new category into bibliobrol
                        cat.Id = AccessConfigDao.Instance.insertCategory(cat, existingBrol.BrolType, dbCon, dbDa);
                        cat.Status = STATUS.toAdd;
                        categories.Add(key, cat.Id);
                        existingBrol.addCategory(cat);
                    }
                }
                notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 3", this));
 
                foreach (Actor actor in importedBrol.Actors)
                {
                    key = getPersonKey(actor);
                    //exists into bibliobrol
                    if (persons.Contains(key))
                    {
                        actor.Id = (int)persons[key];
 
                        //check with found Brol item
                        STATUS = STATUS.none;
                        foreach (Actor tempActor in existingBrol.Actors)
                        {
                            if (tempActor.Id == actor.Id)
                            {
                                STATUS = STATUS.toAdd;
                                break;
                            }
                        }
                        //person exists into bibliobrol, but actor not exists into current loaded brol
                        if (STATUS.Equals(STATUS.toAdd))
                        {
                            existingBrol.addActor(actor);
                        }
                    }
                    //don't exists into bibliobrol (and don't exists into existing brol)
                    else
                    {
                        STATUS = STATUS.toAppAdd;
                        //insert new category into bibliobrol
                        actor.Id = AccessPersonDao.Instance.insertPerson(actor.getPerson(), dbCon, dbDa, str);
                        STATUS = STATUS.toAdd;
                        persons.Add(key, actor.Id);
                        existingBrol.addActor(actor);
                    }
                }
                notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 4", this));
                if (String.Empty.Equals(existingBrol.Comment) && !String.Empty.Equals(importedBrol.Comment))
                {
                    existingBrol.Comment = importedBrol.Comment;
                }
                if (String.Empty.Equals(existingBrol.Synopsis) && !String.Empty.Equals(importedBrol.Synopsis))
                {
                    existingBrol.Synopsis = importedBrol.Synopsis;
                }
                if ((new DateTime(0L)).Equals(existingBrol.Date) && !(new DateTime(0L)).Equals(importedBrol.Date))
                {
                    existingBrol.Date = importedBrol.Date;
                }
                if (existingBrol.Cotation < 1 && importedBrol.Cotation > 0)
                {
                    existingBrol.Cotation = importedBrol.Cotation;
                }
                notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 5", this));
                if (existingBrol.Id < 0)
                {
                    AccessBrolDao.Instance.insertBrol(existingBrol, dbCon, dbDa, false);
                }
                else
                {
                    AccessBrolDao.Instance.updateBrol(existingBrol, dbCon, dbDa, false, str);
                }
                notify(new Notification(Notification.VERBOSE.internalNotification, "pb2", "step 6", this));
                str.AppendLine(String.Format("Ouvrage {0} : {1}", existingBrol.Id, existingBrol.Title));
                notify(new Notification(Notification.VERBOSE.internalNotification, "brolEnd", "", this));
             
            }*/
            return null;
        }
        /// <summary>
        /// Get a Hashtable with all categories ids. 
        /// key : name.toUpper()+typeId
        /// value : id
        /// </summary>
        /// <returns>Hashtable with categories ids</returns>
        private Hashtable getCategoriesHashtable(DbConnection dbCon, DbDataAdapter dbDa)
        {
            Hashtable categories = new Hashtable();
            DataTable catsDt = new DataTable();
            dbDa.SelectCommand.CommandText = "SELECT id, typeId, name FROM category";
            notify(new Notification(Notification.VERBOSE.persistentOperation, dbDa.SelectCommand.CommandText, this));
            try
            {
                dbDa.Fill(catsDt);
                foreach (DataRow row in catsDt.Rows)
                {
                    if (!(row["id"] is DBNull) && !(row["name"] is DBNull))
                    {
                        categories.Add(((String)row["name"]).ToUpper()+(int)row["typeId"], (int)row["id"]);
                    }
                }
            }
            catch (Exception e)
            {
                notify(new Notification(Notification.VERBOSE.advancedOperation, "Chargement des catégories", e, this));
            }
            /*foreach (BrolCategory c in AccessBrolDao.Instance.loadCategories(-2))
            {
                categories.Add(c.Name.ToUpper(), c.Id);
            }*/
            return categories;
        }
 
        /// <summary>
        /// Get a Hashtable with all persons ids. 
        /// key : see String getPersonKey(Person person) method
        /// value : id
        /// </summary>
        /// <returns>Hashtable with persons ids</returns>
        private Hashtable getPersonsHashtable()
        {
            Hashtable persons = new Hashtable();
            foreach (Person person in AccessPersonDao.Instance.loadPersons())
            {
                persons.Add(getPersonKey(person), person.Id);
            }
            return persons;
        }
        /// <summary>
        /// Get a Hashtable with all types ids. 
        /// key : type name
        /// value : id
        /// </summary>
        /// <returns>Hashtable with persons ids</returns>
        private Hashtable getTypesHashtable()
        {
            Hashtable types = new Hashtable();
            foreach (BrolType type in AccessConfigDao.Instance.loadBrolTypes())
            {
                types.Add(type.Name.ToUpper(), type.Id);
            }
            return types;
        }
        /// <summary>
        /// Get a key for a person : FirstName.ToUpper() + LastName.ToUpper();
        /// </summary>
        /// <param name="person">(Person) from which we need a key</param>
        /// <returns>(String)person key</returns>
        private String getPersonKey(Person person)
        {
            return person.FirstName.ToUpper() + person.LastName.ToUpper();
        }
 
        #endregion
    }
}