ChangeResult IPostingRepository.Create(ItemPosting itemposting, bool archive)
{
string commandText =
"INSERT INTO [dbo].[ArtPostingItems]" +
"([Filename]" +
",[Title]" +
",[Shortname]" +
",[Header]" +
",[Description]" +
",[Size]" +
",[Price]" +
",[Archive_flag]" +
",[Order]) " +
"VALUES " +
"( @filename" +
", @title" +
", @shortName" +
", @header" +
", @desc" +
", @size" +
", @price" +
", @archive" +
", @order)";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.UpdateCommand = new SqlCommand(commandText, connection, transaction);
adapter = addPostingParamsToAdapter(adapter, itemposting, archive);
updateOrderValues(connection, transaction, archive, true);
adapter.UpdateCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
return new ChangeResult(false, "Error inserting posting with filename: " + itemposting.FileName + ": " + ex.Message, HTTP_INTERNAL_SERVER_ERROR);
}
}
}
return new ChangeResult(true, "Inserted posting with filename: " + itemposting.FileName, HTTP_SUCCESS);
}
ChangeResult IPostingRepository.Delete(ItemPosting itemposting)
{
bool archive = itemposting.Archive_Flag == true;
int deleted = 0;
string commandText = "DELETE FROM [dbo].[ArtPostingItems] WHERE [Filename] = @filename";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.DeleteCommand = new SqlCommand(commandText, connection, transaction);
SqlParameter filenameParam = new SqlParameter("@filename", SqlDbType.NVarChar);
filenameParam.Value = itemposting.FileName.Normalise();
adapter.DeleteCommand.Parameters.Add(filenameParam);
updateOrderValues(connection, transaction, archive, false, itemposting.Order);
deleted = adapter.DeleteCommand.ExecuteNonQuery();
if (deleted > 0)
{
transaction.Commit();
return new ChangeResult(true, "Posting: " + itemposting.FileName + " was deleted from the database", HTTP_SUCCESS);
}
else
{
transaction.Rollback();
return new ChangeResult(false, "Posting: " + itemposting.FileName + " could not be deleted from the database", HTTP_INTERNAL_SERVER_ERROR);
}
}
catch (Exception ex)
{
transaction.Rollback();
return new ChangeResult(false, "Posting: " + itemposting.FileName + " could not be deleted from the database - error: " + ex.Message, HTTP_INTERNAL_SERVER_ERROR);
}
}
}
}
ItemPosting selectPosting(int id, string selectString, string selectField)
{
try
{
ItemPosting posting = new ItemPosting();
string commandText =
(!string.IsNullOrEmpty(selectString))
? "SELECT [Id],[Filename],[Title],[Shortname],[Header], " +
"[Description],[Size],[Price],[Archive_Flag] FROM [dbo].[ArtPostingItems]" +
" WHERE (Id = @Id) AND (" + selectField + " = '' OR " + selectField + " = @selectValue"
: "SELECT[Id],[Filename],[Title],[Shortname],[Header], " +
"[Description],[Size],[Price],[Archive_Flag] FROM [dbo].[ArtPostingItems]" +
" WHERE (Id = @Id)";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add(new SqlParameter("@Id", id));
command.Parameters.Add(new SqlParameter("@selectValue", selectString));
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
posting = new ItemPosting(
reader["Id"].ToString(),
reader["Order"].ToString(),
reader["Filename"].ToString(),
Path.Combine(webSafePictureFolder, reader["Filename"].ToString()),
reader["Title"].ToString(),
reader["Shortname"].ToString(),
reader["Header"].ToString(),
reader["Description"].ToString(),
reader["Size"].ToString(),
reader["Price"].ToString(),
reader["Archive_Flag"].ToString()
);
}
return posting;
}
}
catch (Exception ex)
{
throw (new Exception("Error retrieving item posting with id: " + id.ToString(), ex));
}
}
private SqlDataAdapter addPostingParamsToAdapter(SqlDataAdapter _adapter, ItemPosting itemposting, bool archived)
{
SqlDataAdapter adapter = _adapter;
SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int);
idParam.Value = itemposting.Id;
adapter.UpdateCommand.Parameters.Add(idParam);
SqlParameter orderParam = new SqlParameter("@order", SqlDbType.Int);
orderParam.Value = itemposting.Order;
adapter.UpdateCommand.Parameters.Add(orderParam);
SqlParameter filenameParam = new SqlParameter("@filename", SqlDbType.NVarChar);
filenameParam.Value = itemposting.FileName;
adapter.UpdateCommand.Parameters.Add(filenameParam);
SqlParameter titleParam = new SqlParameter("@title", SqlDbType.NVarChar);
titleParam.Value = itemposting.Title;
adapter.UpdateCommand.Parameters.Add(titleParam);
SqlParameter shortnameParam = new SqlParameter("@shortname", SqlDbType.NVarChar);
shortnameParam.Value = itemposting.ShortName;
adapter.UpdateCommand.Parameters.Add(shortnameParam);
SqlParameter headerParam = new SqlParameter("@header", SqlDbType.NVarChar);
headerParam.Value = itemposting.Header;
adapter.UpdateCommand.Parameters.Add(headerParam);
SqlParameter descParam = new SqlParameter("@desc", SqlDbType.NVarChar);
descParam.Value = itemposting.Description;
adapter.UpdateCommand.Parameters.Add(descParam);
SqlParameter sizeParam = new SqlParameter("@size", SqlDbType.NVarChar);
sizeParam.Value = itemposting.Size;
adapter.UpdateCommand.Parameters.Add(sizeParam);
SqlParameter priceParam = new SqlParameter("@price", SqlDbType.NVarChar);
priceParam.Value = itemposting.Price;
adapter.UpdateCommand.Parameters.Add(priceParam);
SqlParameter archiveParam = new SqlParameter("@archive", SqlDbType.NVarChar);
archiveParam.Value = archived.ToString();
adapter.UpdateCommand.Parameters.Add(archiveParam);
return adapter;
}
ChangeResult IPostingRepository.Update(ItemPosting itemposting, bool archived)
{
try
{
string commandText = "Update [dbo].[ArtPostingItems] " +
"SET [Order] = @order," +
"[Filename] = @filename, " +
"[Title] = @title, " +
"[Shortname] = @shortname, " +
"[Header] = @header, " +
"[Description] = @desc, " +
"[Size] = @size, " +
"[Price] = @price, " +
"[Archive_flag] = @archive" +
" WHERE id = @id";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
connection.Open();
adapter.UpdateCommand = connection.CreateCommand();
adapter.UpdateCommand.CommandText = commandText;
adapter = addPostingParamsToAdapter(adapter, itemposting, archived);
adapter.UpdateCommand.ExecuteNonQuery();
}
return new ChangeResult(true, "Posting: " + itemposting.Id.ToString() + " was updated", HTTP_SUCCESS);
}
catch (Exception ex)
{
return new ChangeResult(false, "Posting: " + itemposting.Id.ToString() + " could not be updated: " + ex.Message, HTTP_INTERNAL_SERVER_ERROR);
}
}
/// <summary>
/// Exchange the order values of posting1 and posting2
/// </summary>
/// <param name="posting1"></param>
/// <param name="posting2"></param>
/// <param name="archived"></param>
/// <returns></returns>
ChangeResult IPostingRepository.ExchangeOrders(ItemPosting posting1, ItemPosting posting2, bool archived)
{
int order1 = posting1.Order;
int order2 = posting2.Order;
ChangeResult result = new ChangeResult();
string commandText = "Update [dbo].[ArtPostingItems] " +
"SET [Order] = @neworder " +
"WHERE Id = @id";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
int updated = 0;
try
{
SqlDataAdapter adapter = new SqlDataAdapter();
// update #1: set 1 to 2
adapter.UpdateCommand = new SqlCommand(commandText, connection, transaction);
SqlParameter newOrderParam = new SqlParameter("@neworder", SqlDbType.Int);
newOrderParam.Value = order2;
SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int);
idParam.Value = posting1.Id;
adapter.UpdateCommand.Parameters.Add(newOrderParam);
adapter.UpdateCommand.Parameters.Add(idParam);
updated = adapter.UpdateCommand.ExecuteNonQuery();
// update #2: set 2 to 1
if (updated > 0)
{
updated = 0;
newOrderParam.Value = order1;
idParam.Value = posting2.Id;
updated = adapter.UpdateCommand.ExecuteNonQuery();
}
// commit
if (updated > 0)
{
transaction.Commit();
}
else
{
throw new Exception("There was a problem changing the orders of " + posting1.FileName + " and " + posting2.FileName);
}
}
catch (Exception ex)
{
transaction.Rollback();
return new ChangeResult(false, ex.Message, HTTP_INTERNAL_SERVER_ERROR);
}
}
result = new ChangeResult(true, "Promoted: " + posting1.FileName.Normalise(), HTTP_SUCCESS);
return result;
}
}
public ChangeResult RemoveFromDisplay(ItemPosting posting)
{
throw new NotImplementedException();
}
ChangeResult IPostingRepository.Update(ItemPosting posting, bool archived)
{
throw new NotImplementedException();
}
public ChangeResult MovePicture(string filepath, bool archivedestination, bool displaydestination)
{
// filepaths / names brought in from ajax call will have %20 spaces and will not have JS escaped single quotes
string filename = Utility.GetFilenameFromFilepath(filepath).Normalise();
// default constructor gives failed results
ChangeResult removeResult = new ChangeResult();
ChangeResult insertResult = new ChangeResult();
// initialising variables
PictureFileRecord pfr = new PictureFileRecord(filepath);
PictureFileRecord.StatusType source;
List<ItemPostingViewModel> postingVMs = new List<ItemPostingViewModel>();
// getting current database contents
postingVMs.AddRange(ArchivePostings().ToList());
postingVMs.AddRange(ShopPostings().ToList());
// getting full info on item to move
ItemPostingViewModel moveItem = postingVMs.FirstOrDefault(x => x.ItemPosting.FileName.ToUpper().Normalise() == filename.ToUpper());
ItemPosting posting = new ItemPosting();
// determine current location of move item
if (moveItem == null)
{
source = PictureFileRecord.StatusType.NotDisplayed;
}
else
{
posting = moveItem.ItemPosting;
if (posting.Archive_Flag == true)
{
source = PictureFileRecord.StatusType.Archived;
}
else
{
source = PictureFileRecord.StatusType.ForSale;
}
}
if (displaydestination)
{
// (1) the move item's being moved to the list it's already in
if (archivedestination && source == PictureFileRecord.StatusType.Archived ||
!archivedestination && source == PictureFileRecord.StatusType.ForSale)
{
return new ChangeResult(
false,
filename + " is already included in the " + ((archivedestination) ? "Archive" : "Home") + " page",
400);
}
else
{
// (2) the moveItem's being moved from one list to another
if (archivedestination && source == PictureFileRecord.StatusType.ForSale ||
!archivedestination && source == PictureFileRecord.StatusType.Archived)
{
removeResult = RemoveFromDisplay(posting);
insertResult = InsertPosting(pfr, archivedestination);
return insertResult;
}
else
{
// (3) the moveItem's being moved onto a list from not_displayed
insertResult = InsertPosting(pfr, archivedestination);
return insertResult;
}
}
}
// (4) the moveItem's being removed from all lists
else
{
if (source == PictureFileRecord.StatusType.NotDisplayed)
{
return new ChangeResult(
false,
filename + " is not currently displayed, and therefore is not in the database. Therefore it cannot be removed from the database",
500);
}
else
{
removeResult = RemoveFromDisplay(posting);
return removeResult;
}
}
}
public ChangeResult Delete(ItemPosting posting)
{
throw new NotImplementedException();
}
public ChangeResult Create(ItemPosting posting, bool archive)
{
throw new NotImplementedException();
}
public bool Create(ItemPosting posting)
{
throw new NotImplementedException();
}
public void Update(ItemPosting posting, bool archived)
{
throw new NotImplementedException();
}
private ItemPosting extractSQLItemPosting(ItemPosting nonSQLPosting)
{
ItemPosting SQLPosting = new ItemPosting();
SQLPosting.Id = nonSQLPosting.Id;
SQLPosting.Description = Utility.NormaliseString(nonSQLPosting.Description);
SQLPosting.FileName = Utility.NormaliseString(nonSQLPosting.FileName);
SQLPosting.FilePath = Utility.NormaliseString(nonSQLPosting.FilePath);
SQLPosting.Header = Utility.NormaliseString(nonSQLPosting.Header);
SQLPosting.Price = Utility.NormaliseString(nonSQLPosting.Price);
SQLPosting.ShortName = Utility.NormaliseString(nonSQLPosting.ShortName);
SQLPosting.Size = Utility.NormaliseString(nonSQLPosting.Size);
SQLPosting.Title = Utility.NormaliseString(nonSQLPosting.Title);
SQLPosting.Order = nonSQLPosting.Order;
return SQLPosting;
}
/// <summary>
/// Removes an item from the database so it is no longer displayed on any list
/// </summary>
/// <param name="posting"></param>
/// <returns></returns>
public ChangeResult RemoveFromDisplay(ItemPosting posting)
{
return repository.Delete(posting);
}
ChangeResult IPostingRepository.ExchangeOrders(ItemPosting posting1, ItemPosting posting2, bool archived)
{
throw new NotImplementedException();
}
ItemPostingViewModel IPostingService.CreateItemPostingViewModel(PictureFileRecord pfr)
{
ItemPosting posting = new ItemPosting();
posting.FileName = Utility.GetFilenameFromFilepath(pfr.FilePath);
posting.FilePath = pfr.FilePath;
posting.Archive_Flag = true;
posting.Description = "";
posting.Header = "";
posting.Order = 0;
posting.Price = "";
posting.ShortName = "";
posting.Size = "";
posting.Title = "";
ItemPostingViewModel vm = new ItemPostingViewModel();
vm.Editing = false;
vm.ItemPosting = posting;
return vm;
}
