/// <summary>
/// Worksheet Range Data Changed
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Worksheet_RangeDataChanged(object sender, unvell.ReoGrid.Events.RangeEventArgs e)
{
bool isDeleteRows = false;
if (e.Range.Cols >= _Worksheet.UsedRange.Cols) //delete whole rows
{
isDeleteRows = true;
for (int rowIndex = e.Range.StartPos.Row; rowIndex <= e.Range.EndRow; rowIndex++)
{
IRecordModel recordModel = (from one in _Records where one.RowIndex == rowIndex select one).FirstOrDefault();
if (recordModel != null)
{
_Records.CollectionChanged -= Records_CollectionChanged;
_Records.Remove(recordModel);
_Records.CollectionChanged += Records_CollectionChanged;
}
}
if (isDeleteRows)
{
_Worksheet.DeleteRows(e.Range.StartPos.Row, e.Range.Rows);
for (int i = e.Range.StartPos.Row; i < _Records.Count; i++)
{
_Records.ElementAt(i).RowIndex = i;
}
}
}
else //delete parts of rows
{
for (int rowIndex = e.Range.StartPos.Row; rowIndex <= e.Range.EndRow; rowIndex++)
{
AddOrUpdateOneFromUi(rowIndex, e.Range.StartPos.Col, e.Range.EndCol);
}
}
}
public void UpdateRecordInDatabase(IRecordModel model)
{
string query = "UPDATE [Records] SET Client=" + model.Client + ", Car=" + model.Car + ", DateOfEntry=" + model.DateOfEntry + " , Malfunction=" + model.Malfunction + " , Mechanic=" + model.Mechanic + " Price=" + model.Price + " WHERE ID =" + model.ID.ToString();
try
{
using (connection = new System.Data.SQLite.SQLiteConnection(@"data source=Databases/RecordsDB.db"))
{
connection.Open();
using (var command = new SQLiteCommand(query, connection))
{
command.CommandText = "update Records set Client = :client, Car = :car,DateOfEntry= :dateOfEntry,Malfunction= :malfunction,Mechanic= :mechanic,Price= :price where ID=:id";
command.Parameters.Add("client", DbType.String).Value = model.Client;
command.Parameters.Add("car", DbType.String).Value = model.Car;
command.Parameters.Add("dateOfEntry", DbType.String).Value = model.DateOfEntry;
command.Parameters.Add("malfunction", DbType.String).Value = model.Malfunction;
command.Parameters.Add("mechanic", DbType.String).Value = model.Mechanic;
command.Parameters.Add("price", DbType.String).Value = model.Price;
command.Parameters.Add("id", DbType.Int64).Value = model.ID;
Console.WriteLine(command.ExecuteNonQuery());
}
connection.Close();
}
}
catch (Exception ex)
{ Console.WriteLine(ex); }
}
/// <summary>
/// Load Records
/// </summary>
private void LoadRecords()
{
if (_Records.Count > 0)
{
for (int rowIndex = 0; rowIndex < _Records.Count; rowIndex++)
{
IRecordModel record = _Records.ElementAt(rowIndex);
AddOrUpdateOneFromRecord(rowIndex, record);
}
}
}
private bool?WorksheetModel_OnBeforeChangeRecord(IRecordModel record, System.Reflection.PropertyInfo propertyInfo, object newProperyValue)
{
if (propertyInfo.Name.Equals("Price"))
{
decimal price = Convert.ToDecimal(newProperyValue);
if (price > 100m) //assume the max price is 100
{
MessageBox.Show("Max price is 100.", "Alert", MessageBoxButton.OK, MessageBoxImage.Warning);
return(true); // cancel the change
}
}
return(null);
}
public IActionResult EditRecordButton(int id)
{
if (Startup.isLogged == true)
{
try
{
list = RecordsAbstractizationFactory.ReturningRecordsList();
IRecordModel Dummy = list.Find(x => x.ID == id);
Dummy.ID = id;
return(View("EditRecords", Dummy));
}
catch (Exception ex)
{ Console.WriteLine(ex); return(RedirectToAction("Error")); }
}
else
{
return(RedirectToAction("Login", "Login"));
}
}
public IActionResult AddNewRecordSubmitButton(string Client, string Car, string DateOfEntry, string Malfunction, string Mechanic, string Price)
{
if (Startup.isLogged == true)
{
try
{
IRecordModel Dummy = RecordsDatabaseClassLibrary.RecordsAbstractizationFactory.CreateRecordInstance(Client, Car, DateOfEntry, Malfunction, Mechanic, Price);
RecordsCRUDOperations.AddNewRecordToDatabase(Dummy);
return(RedirectToAction("Records"));
}
catch (Exception ex)
{ return(RedirectToAction("Error")); }
}
else
{
return(RedirectToAction("Login", "Login"));
}
}
public IActionResult SaveModificationRecord(int ID, string Client, string Car, string DateOfEntry, string Malfunction, string Mechanic, string Price)
{
if (Startup.isLogged == true)
{
try
{
IRecordModel Dummy = RecordsAbstractizationFactory.CreateRecordInstance(ID, Client, Car, DateOfEntry, Malfunction, Mechanic, Price);
RecordsCRUDOperations.UpdateRecordInDatabase(Dummy);
return(RedirectToAction("Records"));
}
catch (Exception ex)
{ Console.WriteLine(ex); return(RedirectToAction("Error")); }
}
else
{
return(RedirectToAction("Login", "Login"));
}
}
public void AddNewRecordToDatabase(IRecordModel model)
{
try
{
string query = "INSERT INTO Records(Client,Car,DateOfEntry,Malfunction,Mechanic,Price) VALUES(@client,@car,@dateOfEntry,@malfunction,@mechanic,@price)";
var args = new Dictionary <string, object>
{
{ "@client", model.Client },
{ "@car", model.Car },
{ "@dateOfEntry", model.DateOfEntry },
{ "@malfunction", model.Malfunction },
{ "@mechanic", model.Mechanic },
{ "@price", model.Price },
};
base.ExecuteWrite(query, args);
}
catch (Exception ex)
{ Console.WriteLine(ex); }
}
/// <summary>
/// Add or update one IRecordModel object into Worksheet
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="record"></param>
private void AddOrUpdateOneFromRecord(int rowIndex, IRecordModel record)
{
_Worksheet.SuspendDataChangedEvents();
record.RowIndex = rowIndex; // set row index
for (int colIndex = 0; colIndex < _Worksheet.Columns; colIndex++)
{
CellPosition currentCellPos = new CellPosition(rowIndex, colIndex);
if (_Worksheet.ColumnHeaders[colIndex].Tag != null)
{
PropertyInfo propertyInfo = (PropertyInfo)_Worksheet.ColumnHeaders[colIndex].Tag;
object data = propertyInfo.GetValue(record);
_Worksheet.SetCellData(currentCellPos, data);
}
else
{
#if DEBUG
Console.WriteLine("LoadRecords Error: index of columns[{0}] is null!", colIndex);
#endif
}
}
_Worksheet.ResumeDataChangedEvents();
}
/// <summary>
/// Update one record
/// </summary>
/// <param name="recordModel"></param>
public void UpadteRecord(IRecordModel recordModel)
{
AddOrUpdateOneFromRecord(recordModel.RowIndex, recordModel);
}
/// <summary>
/// Data records collection changed
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Records_CollectionChanged(object sender, System.Collections.Specialized.NotifyCollectionChangedEventArgs e)
{
// Changed event, _Records has got action result
switch (e.Action)
{
case System.Collections.Specialized.NotifyCollectionChangedAction.Add:
{
foreach (IRecordModel item in e.NewItems)
{
AddOrUpdateOneFromRecord(_Records.Count - 1, item);
}
break;
}
case System.Collections.Specialized.NotifyCollectionChangedAction.Remove:
{
foreach (IRecordModel item in e.OldItems)
{
int rowIndex = item.RowIndex;
_Worksheet.DeleteRows(rowIndex, 1);
for (int i = rowIndex; i < _Records.Count; i++)
{
_Records.ElementAt(rowIndex).RowIndex = rowIndex;
}
}
break;
}
case System.Collections.Specialized.NotifyCollectionChangedAction.Replace:
{
for (int i = 0; i < e.NewItems.Count; i++)
{
IRecordModel item = e.NewItems[i] as IRecordModel;
int rowIndex = (e.OldItems[i] as IRecordModel).RowIndex;
AddOrUpdateOneFromRecord(rowIndex, item);
}
break;
}
case System.Collections.Specialized.NotifyCollectionChangedAction.Move:
{
for (int i = 0; i < _Records.Count; i++)
{
IRecordModel item = _Records.ElementAt(i);
int rowIndex = item.RowIndex;
if (rowIndex != i)
{
AddOrUpdateOneFromRecord(i, item);
}
}
break;
}
case System.Collections.Specialized.NotifyCollectionChangedAction.Reset:
{
_Worksheet.DeleteRows(0, _Worksheet.UsedRange.Rows);
break;
}
default:
break;
}
}
