public static OperationStatus<Category> ShowWindow(Category obj)
        {
            OperationStatus<Category> status = new OperationStatus<Category>();
            status.Data = obj;
            CategoryDetails window = new CategoryDetails();
            window.DataContext = obj;
            window.uxCategoryComboBox.ItemsSource = BooksManager.BooksManager.GetCategoryList(Constants.ROOT_CATEGORY).Where(c => c.Id != obj.Id);

            var retStatus = window.ShowDialog();

            if (retStatus.HasValue && retStatus.Value)
            {
                status.Result = OperationResult.Passed;
            }
            else
                status.Result = OperationResult.Failed;

            return status;
        }
        private static IEnumerable<Category> GetSubCategories(long parentCategoryId, SQLiteConnection connection)
        {
            List<Category> list = new List<Category>();

            using (SQLiteCommand mycommand = new SQLiteCommand(connection))
            {
                mycommand.CommandText =
                    "SELECT cat.id as Id, cat.parent_id as ParentId, cat.name as Name from category cat ";

                mycommand.CommandText += "where cat.parent_id=@id";
                mycommand.Parameters.AddWithValue("@id", parentCategoryId);

                IDataReader reader = mycommand.ExecuteReader();
                DataTable table = new DataTable();

                table.Load(reader);

                if (table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Category category = new Category { Id = Int64.Parse(row["Id"].ToString()) };

                        if (row["ParentId"] != DBNull.Value)
                            category.ParentId = Int64.Parse(row["ParentId"].ToString());

                        category.Name = row["Name"].ToString();
                        list.Add(category);
                    }
                }

                reader.Close();
                if (table.Rows.Count > 0)
                {
                    foreach (Category subCategory in list.ToList())
                        list.AddRange(GetSubCategories(subCategory.Id, connection));
                }
            }
            return list;
        }
        public static OperationStatus<bool> DeleteCategory(Category category)
        {
            //TODO delete child categories in recursive query

            SQLiteConnection connection = new SQLiteConnection(String.Format(connectionString, Config.DatabaseName));
            connection.Open();

            using (SQLiteTransaction mytransaction = connection.BeginTransaction())
            {
                SetCategoryToNullInBookTable(category.Id, connection);
                using (SQLiteCommand mycommand = new SQLiteCommand(connection))
                {

                    mycommand.CommandText = "DELETE FROM category where id=@id";

                    mycommand.Parameters.AddWithValue("@id", category.Id);

                    mycommand.ExecuteNonQuery();
                }
                mytransaction.Commit();
            }
            connection.Close();

            return new OperationStatus<bool> { OperationMessage = "Kategoria zosta³a usuniêta", Result = OperationResult.Passed, };
        }
        public static OperationStatus<Category> UpdateCategory(Category category)
        {
            SQLiteConnection connection = new SQLiteConnection(String.Format(connectionString, Config.DatabaseName));
            connection.Open();

            using (SQLiteTransaction mytransaction = connection.BeginTransaction())
            {
                using (SQLiteCommand mycommand = new SQLiteCommand(connection))
                {
                    mycommand.CommandText = "UPDATE category SET parent_id=@parentId, name=@name where id=@id";

                    mycommand.Parameters.AddWithValue("@parentId", category.ParentId);
                    mycommand.Parameters.AddWithValue("@name", category.Name);
                    mycommand.Parameters.AddWithValue("@id", category.Id);

                    mycommand.ExecuteNonQuery();
                }
                mytransaction.Commit();
            }
            connection.Close();

            return new OperationStatus<Category> { OperationMessage = "Kategoria zosta³a zaktualizowana", Result = OperationResult.Passed, Data = category };
        }
        public static OperationStatus<Category> InsertCategory(Category category)
        {
            var status = new OperationStatus<Category>
                             {
                                 Data = category,
                                 OperationMessage = "Kategoria zosta³a dodana.",
                                 Result = OperationResult.Passed
                             };
            SQLiteConnection connection = new SQLiteConnection(String.Format(connectionString, Config.DatabaseName));
            connection.Open();

            using (SQLiteTransaction mytransaction = connection.BeginTransaction())
            {
                using (SQLiteCommand mycommand = new SQLiteCommand(connection))
                {
                    mycommand.CommandText = "INSERT INTO category (parent_id,name) values(@parentId,@name)";

                    mycommand.Parameters.AddWithValue("@parentId", category.ParentId);
                    mycommand.Parameters.AddWithValue("@name", category.Name);

                    mycommand.ExecuteNonQuery();

                    mycommand.CommandText = @"select last_insert_rowid()";
                    long lastId = (long)mycommand.ExecuteScalar();

                    category.Id = lastId;

                }
                mytransaction.Commit();
            }
            connection.Close();

            return status;
        }
        public static IList<Category> GetCategoryList(long rootId, SQLiteConnection connection)
        {
            List<Category> list = new List<Category>();

            using (SQLiteCommand mycommand = new SQLiteCommand(connection))
            {
                mycommand.CommandText = "SELECT cat.id as Id, cat.parent_id as ParentId, cat.name as Name from category cat ";//
                if (rootId > 0)
                {
                    mycommand.CommandText += "where cat.id=@id";
                    mycommand.Parameters.AddWithValue("@id", rootId);
                }

                IDataReader reader = mycommand.ExecuteReader();
                DataTable table = new DataTable();

                table.Load(reader);

                if (table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Category category = new Category { Id = Int64.Parse(row["Id"].ToString()) };

                        if (row["ParentId"] != DBNull.Value)
                            category.ParentId = Int64.Parse(row["ParentId"].ToString());

                        category.Name = row["Name"].ToString();
                        list.Add(category);
                    }
                }

                reader.Close();
                if (table.Rows.Count > 0)
                {
                    if (rootId > 0)
                    {
                        foreach (Category subCategory in list.ToList())
                            list.AddRange(GetSubCategories(subCategory.Id, connection));
                    }

                    foreach (var category in list.Where(cat => cat.Id > 0).ToList())
                    {
                        Category category1 = category;
                        category.Parent = list.FirstOrDefault(c => c.Id == category1.ParentId);
                        Category category2 = category;
                        category.SubCategories = list.Where(c => c.ParentId == category2.Id).ToList();
                        Category category3 = category;
                        list.RemoveAll(c => c.ParentId == category3.Id);
                    }
                }
                if (rootId == 0)
                    list.Insert(0, new Category { Id = 0, Name = "Categories (all books)", ParentId = 0 });
            }
            return list;
        }
        public static Category GetCategory(int id)
        {
            Category category = new Category();
            SQLiteConnection connection = new SQLiteConnection(String.Format(connectionString, Config.DatabaseName));
            connection.Open();

            using (SQLiteCommand mycommand = new SQLiteCommand(connection))
            {
                mycommand.CommandText = "SELECT cat.id as Id, cat.parent_id as ParentId, cat.name as Name from category cat where cat.id=@id";
                mycommand.Parameters.AddWithValue("@id", id);

                IDataReader reader = mycommand.ExecuteReader();
                DataTable table = new DataTable();

                table.Load(reader);

                if (table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        category.Id = Int64.Parse(row["Id"].ToString());

                        if (row["ParentId"] != DBNull.Value)
                            category.ParentId = Int64.Parse(row["ParentId"].ToString());

                        category.Name = row["Name"].ToString();
                    }
                }
                else
                {
                    category = null;
                }
                reader.Close();
            }

            connection.Close();

            return category;
        }
        private void OnAddCategoryItemExecute(object sender, ExecutedRoutedEventArgs e)
        {
            Category cat = new Category();
            Category cc = e.Parameter as Category;
            if (cc != null)
                cat.Parent = cc;
            OperationStatus<Category> result1 = CategoryDetails.ShowWindow(cat);

            if (result1.Result == OperationResult.Passed)
            {
                var result = BooksManager.BooksManager.InsertCategory(result1.Data);

                if (result.Result == OperationResult.Passed)
                    RefreshCategoryList();
                else
                    MessageBox.Show(result.OperationMessage);
            }
        }