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);
}
}
