private void FillCombobox()
{
cmbMeterName.Items.Clear();
cmbMeterType.Items.Clear();
cmbMeterType.Items.Add("--- Select Meter type ---");
cmbMeterType.Items.Add("GAS");
cmbMeterType.Items.Add("ELECTRICITY");
cmbMeterType.Items.Add("HOT WATER");
cmbMeterType.Items.Add("COLD WATER");
cmbMeterType.SelectedIndex = 0;
// get unique meter names from DB, add them to combobox
using (DB_context context = new DB_context())
{
cmbMeterName.Items.Add("--- Select Meter name ---");
var meter_names = (from c in context.db_Meters select c).GroupBy(x => x.METER_NAME).Select(grp => grp.FirstOrDefault()).ToList();
foreach (var item in meter_names)
{
cmbMeterName.Items.Add(item.METER_NAME);
}
cmbMeterName.SelectedIndex = 0;
}
}
public IHttpActionResult calculateProductUnitPrice([FromBody] ProductDetailCalculatorParameter parameter)
{
try
{
using (var db = new DB_context())
{
var temp = db.Products.AsQueryable();
Dictionary <string, object> result = new Dictionary <string, object>();
var listProduct = db.Products.OrderByDescending(data => data.ProductID).ToList();
ProductCalculator calculator = new ProductCalculator(';');
foreach (var item in listProduct)
{
calculator.calculateProductUnitPrice(item, parameter);
}
db.SaveChanges();
return(Ok("Data Saved Successfully"));
}
}
catch (Exception)
{
throw;
}
}
private void LoadIntoTable()
{
using (DB_context context = new DB_context())
{
// clear contetns of the table
tblReadouts.ItemsSource = null;
readoutReports.Clear();
// get all info from both tables in DB, otherwise too many SQL requests...
listMeters = (from c in context.db_Meters select c).ToList();
listReadouts = (from c in context.db_Readouts select c).ToList();
foreach (var item in listReadouts)
{
// foreach readout get corrisponding meter name and type from meter list
ReadoutReport report = new ReadoutReport();
report.ID_READOUT_REPORT = item.ID_READOUT;
var meter = listMeters.Where(x => x.ID_METER.Equals(item.ID_METER)).First();
report.METER_NAME_REPORT = meter.METER_NAME;
report.METER_TYPE_REPORT = meter.METER_TYPE;
report.READOUT_DATE_REPORT = item.READOUT_DATE.ToString("MM / yyyy", CultureInfo.InvariantCulture);
report.READOUT_MONTH_REPORT = item.READOUT_DATE.ToString("MMMM", CultureInfo.InvariantCulture);
//get previous readout and calculate consumption value
double prev_readout = LoadPreviousReadout(meter.ID_METER, item.ID_READOUT);
report.READOUT_CONSUPMTION_REPORT = item.READOUT_VALUE - prev_readout;
// if combobox value doesn't mathc --> skip adding to list
// if datepicker range is not OK --> skip adding to list
try
{
if (!cmbMeterType.SelectedItem.ToString().Contains("---") && report.METER_TYPE_REPORT != cmbMeterType.SelectedItem.ToString())
{
continue;
}
if (!cmbMeterName.SelectedItem.ToString().Contains("---") && report.METER_NAME_REPORT != cmbMeterName.SelectedItem.ToString())
{
continue;
}
if (dpFrom.SelectedDate != null && item.READOUT_DATE < dpFrom.SelectedDate)
{
continue;
}
if (dpTo.SelectedDate != null && item.READOUT_DATE > dpTo.SelectedDate)
{
continue;
}
}
catch { }
readoutReports.Add(report);
}
// sort list descending by readout date, show data in the table
readoutReports = readoutReports.OrderByDescending(x => x.READOUT_DATE_REPORT).ToList();
tblReadouts.ItemsSource = readoutReports;
}
}
public void LoadMeterFromDB()
{
if (meter_ID == 0)
{
// new meter, nothing to load
lbl_action.Content = "Add new meter";
}
else
{
// Edit meter
lbl_action.Content = "Edit meter";
// get Meter info from DB and set into data fields
try
{
using (DB_context context = new DB_context())
{
var query = (from c in context.db_Meters where c.ID_METER == meter_ID select c).FirstOrDefault();
txtMeterName.Text = query.METER_NAME;
cmbMeterType.Text = query.METER_TYPE;
cmbMeterUnits.Text = query.METER_UNITS;
}
}
catch (Exception ex)
{
MessageBox.Show("Error! DataBase not available!" + "\r\n" + ex.Message, "Error!", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
}
} // btn save
// ========== FUNCTIONS ========== //
private void LoadMeterListToCMB()
{
/*
* Get all meters from DB
* Add them into combobox so that user can see only name and type of meter, but value is ID_METER
*/
using (DB_context context = new DB_context())
{
var query = (from c in context.db_Meters select c).ToList();
List <Meter> list_meters = new List <Meter>();
Meter firstVal = new Meter();
firstVal.MeterTitle = "--- Select Meter ---";
firstVal.Meter_ID = 0;
list_meters.Add(firstVal);
foreach (var item in query)
{
Meter meter = new Meter();
meter.MeterTitle = item.METER_NAME + " - " + item.METER_TYPE;
meter.Meter_ID = item.ID_METER;
list_meters.Add(meter);
}
cmbMeters.ItemsSource = list_meters;
cmbMeters.SelectedIndex = 0;
}
}
public IHttpActionResult CreateProductWithProductDetail([FromBody] CreateNReadModel databody)
{
using (var db = new DB_context())
{
try
{
ProductValidator val = new ProductValidator();
//Dictionary<string, ProductViewModel> dict = new Dictionary<string, ProductViewModel>();
Dictionary <string, object> result = new Dictionary <string, object>();
//Product product = dict.Add(, databody);
Product product = databody.CreateProductDictionary();
db.Products.Add(product);
db.SaveChanges();
if (val.isValidProductDetail(product.ProductDetail, product.ProductType) == true)
{
result.Add("Message", "Insert Data Success");
}
else
{
result.Add("Message", "Data is invalid ");
}
return(Ok(result));
}
catch (Exception)
{
throw;
}
}
}
public void LoadReadoutsForMeter(int ID)
{
/*
* Retrieve readouts based on meter selected
* Set values into table
*/
list_Readouts.Clear();
tbl_Readouts.ItemsSource = null;
try
{
using (DB_context context = new DB_context())
{
list_Readouts = (from c in context.db_Readouts where c.ID_METER == ID orderby c.ID_READOUT descending select c).ToList();
var meter = (from c in context.db_Meters where c.ID_METER == ID select c).FirstOrDefault();
lblReadoutsHeader.Content = "ALL READOUTS FOR: " + meter.METER_NAME.ToUpper() + " - " + meter.METER_TYPE.ToUpper();
}
}
catch (Exception ex)
{
MessageBox.Show("Error, DataBase is not available.\r\n\r\n" + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
tbl_Readouts.ItemsSource = list_Readouts;
}
public Task <IEnumerable <Order> > GetOrdersAsync()
{
List <Order> orders = new List <Order>();
using (var context = new DB_context())
{
orders = context.Orders.OrderBy(o => o.Status).ToList();
}
return(Task.FromResult(orders.AsEnumerable()));
}
public Task <Order> GetOrderByIdAsync(string id)
{
Order order = null;
using (var context = new DB_context())
{
order = context.Orders.SingleOrDefault(o => o.Id == id);
}
return(Task.FromResult(order));
}
//private IList<Customer> _customers;
//public CustomerService()
//{
// _customers = new List<Customer>();
// _customers.Add(new Customer(1, "KinetEco"));
// _customers.Add(new Customer(2, "Pixelford Photography"));
// _customers.Add(new Customer(3, "Topsy Turvy"));
// _customers.Add(new Customer(4, "Leaf & Mortar"));
//}
//public Customer GetCustomeById(int id)
//{
// return GetCustomerByIdAsync(id).Result;
//}
//public Task<Customer> GetCustomerByIdAsync(int id)
//{
// return Task.FromResult(_customers.Single(o => Equals(o.Id, id)));
//}
public Task <IEnumerable <Customer> > GetCustomersAsync()
{
List <Customer> customers = new List <Customer>();
using (var context = new DB_context())
{
customers = context.Customers.ToList();
}
return(Task.FromResult(customers.AsEnumerable()));
}
private bool SaveReadout(double new_readout_value)
{
// readout == 0 --> create new entry
if (readout_ID == 0)
{
try
{
using (DB_context context = new DB_context())
{
tbl_Readouts readout = new tbl_Readouts();
readout.ID_METER = Convert.ToInt32(cmbMeters.SelectedValue);
readout.READOUT_DATE = dp_readoutAdded.SelectedDate.Value;
readout.READOUT_VALUE = new_readout_value;
readout.READOUT_COMMENT = txtComment.Text.Trim();
// add new entry
context.db_Readouts.Add(readout);
context.SaveChanges();
}
}
catch (Exception ex)
{
MessageBox.Show("Error saving to DB. \r\n\r\n" + ex.Message, "Exception", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
}
// readout != 0 --> edit existing entry
else
{
try
{
using (DB_context context = new DB_context())
{
var query = (from c in context.db_Readouts where c.ID_READOUT == readout_ID select c).FirstOrDefault();
query.READOUT_DATE = dp_readoutAdded.SelectedDate.Value;
query.READOUT_VALUE = new_readout_value;
query.READOUT_COMMENT = txtComment.Text.Trim();
// save edited entry
context.SaveChanges();
}
}
catch (Exception ex)
{
MessageBox.Show("Error saving to DB. \r\n\r\n" + ex.Message, "Exception", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
}
return(true);
} // save readout
} // save readout
private void LoadPreviousReadout(int meter_id)
{
// get previous reading from DB bssed on selected counter
try
{
using (DB_context context = new DB_context())
{
var readout_list = (from c in context.db_Readouts where c.ID_METER == meter_id select c).ToList();
if (readout_list.Count == 0)
{
//if it is the first readout --> last = 0
txtLastReadout.Text = "0";
}
else
{
// if it is not the first readout, determine, is it new entry or edit?
if (readout_ID == 0)
{
// new
txtLastReadout.Text = readout_list.Last().READOUT_VALUE.ToString();
dp_previousReadout.SelectedDate = readout_list.Last().READOUT_DATE;
}
else
{
// edit: find current readout in the list, get previous value
var readout = (from c in context.db_Readouts where c.ID_READOUT == readout_ID select c).First();
int index = readout_list.IndexOf(readout);
if (index == 0)
{
//if it is the first readout --> last = 0
txtLastReadout.Text = "0";
dp_previousReadout.SelectedDate = null;
}
else
{
txtLastReadout.Text = readout_list[index - 1].READOUT_VALUE.ToString();
dp_previousReadout.SelectedDate = readout_list[index - 1].READOUT_DATE;
} // editing first entry?
} // new or edit?
} // first readout?
} // using db context
}
catch
{
}
}
public static void SavePlayerNameAndHighscore()
{
using (var context = new DB_context())
{
DB_player player = new DB_player();
player.Name = PlayerName;
context.DPlayer.Add(player);
context.SaveChanges();
DB_highScore highscore = new DB_highScore();
highscore.Points = PlayerPoints;
highscore.Player = context.DPlayer.Where(h => h.Id == player.Id).First();
context.DHighScore.Add(highscore);
context.SaveChanges();
}
}
public Task <Order> StartAsync(string orderId, OrderStatuses status)
{
//var order = GetById(orderId);
////order.Start();
//var orderEvent = new OrderEvent(order.Id, order.Name, OrderStatuses.PROCESSING, DateTime.Now);
//_events.AddEvent(orderEvent);
//return Task.FromResult(order);
Order order = null;
using (var context = new DB_context())
{
order = context.Orders.SingleOrDefault(p => p.Id == orderId);
order.Status = status;
context.SaveChanges();
}
return(Task.FromResult(order));
}
// ========== EVENTS ========== //
private void MetroWindow_Loaded(object sender, RoutedEventArgs e)
{
if (readout_ID == 0)
{
lbl_action.Content = "Add new readout";
if (meter_ID != 0)
{
cmbMeters.SelectedValue = meter_ID;
LoadPreviousReadout(meter_ID);
}
}
else
{
lbl_action.Content = "Edit readout";
cmbMeters.IsEnabled = false;
// load data from DB into the form
try
{
using (DB_context context = new DB_context())
{
var readout = (from c in context.db_Readouts where c.ID_READOUT == readout_ID select c).FirstOrDefault();
var meter = (from c in context.db_Meters where c.ID_METER == readout.ID_METER select c).FirstOrDefault();
cmbMeters.Text = meter.METER_NAME + " - " + meter.METER_TYPE;
dp_readoutAdded.SelectedDate = readout.READOUT_DATE;
txtNewReadout.Text = readout.READOUT_VALUE.ToString();
txtComment.Text = readout.READOUT_COMMENT;
}
}
catch (Exception ex)
{
MessageBox.Show("Error loading from DB. \r\n\r\n" + ex.Message, "Exception", MessageBoxButton.OK, MessageBoxImage.Error);
this.Close();
}
} // edit readout
} // window loaded
} // edit meter
private void DeleteReadout(object sender, RoutedEventArgs e)
{
int ID_meter = 0;
/*
* find selected row, get ID from first column (hidden column "ID")
* find entry in DB based on ID,
* delete entry, save changes
*/
MessageBoxResult result = MessageBox.Show("Delete this entry?", "Info", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (result == MessageBoxResult.Yes)
{
for (var vis = sender as Visual; vis != null; vis = VisualTreeHelper.GetParent(vis) as Visual)
{
if (vis is DataGridRow)
{
var row = (DataGridRow)vis;
int id = Convert.ToInt32((tbl_Readouts.SelectedCells[0].Column.GetCellContent(row) as TextBlock).Text);
using (DB_context context = new DB_context())
{
var query = (from c in context.db_Readouts where c.ID_READOUT == id select c).FirstOrDefault();
ID_meter = query.ID_METER;
context.db_Readouts.Remove(query);
context.SaveChanges();
MessageBox.Show("Success!", "Info", MessageBoxButton.OK, MessageBoxImage.Asterisk);
} // using
} // if is datagrid row
} // for ... cycle
} // if result == yes
else
{
return;
}
// update list on finish
LoadReadoutsForMeter(ID_meter);
}
public Task <Order> CreateAsync(Order order)
{
using (var context = new DB_context())
{
context.Orders.Add(order);
context.SaveChanges();
}
//event/Subscripcions
var orderEvent = new OrderEvent()
{
Id = order.Id,
OrderId = order.Id,
Name = order.Name,
Statuses = order.Status,
Timestamp = order.Created
};
_events.AddEvent(orderEvent);
return(Task.FromResult(order));
}
public void LoadMeterList()
{
/*
* Get all data from DB and show it in the table
*/
list_meters.Clear();
tblMeters.ItemsSource = null;
try
{
DB_context context = new DB_context();
context.db_Meters.Load();
list_meters = context.db_Meters.Local.ToList();
}
catch (Exception ex)
{
MessageBox.Show("Error, DataBase is not available.\r\n\r\n" + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
tblMeters.ItemsSource = list_meters;
}
public void LoadMeters()
{
/*
* Load all meters
* user clicks the button near the meter to load readouts for this meter
*/
list_Meters.Clear();
tbl_Meters.ItemsSource = null;
try
{
using (DB_context context = new DB_context())
{
list_Meters = (from c in context.db_Meters select c).ToList();
}
}
catch (Exception ex)
{
MessageBox.Show("Error, DataBase is not available.\r\n\r\n" + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
tbl_Meters.ItemsSource = list_Meters;
}
public static void GetHighScore()
{
using (var context = new DB_context())
{
var show = context.DHighScore
.Include(a => a.Player)
.OrderByDescending(u => u.Points)
.Take(5).ToList();
Console.SetCursorPosition(12, 2);
Console.Write("Highscore");
int x = 4;
int y = 3;
int i = 0;
foreach (var item in show)
{
Console.SetCursorPosition(x, y + i);
Console.Write("{0} [ {1} poäng ]", item.Player.Name, item.Points);
i++;
}
}
}
public UserController(DB_context context)
{
_context = context;
}
private bool SaveToDB()
{
Regex regex = new Regex(@"(^[A-Za-z0-9-.\s]*$)"); // regex for letters, numbers, '-' and spaces
Match matchName = regex.Match(txtMeterName.Text.Trim());
if (!matchName.Success)
{
MessageBox.Show("Inappropriate Meter name, please choose another." + "\r\n" + "Allowed only leters, digits and spaces", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
if (cmbMeterType.Text.Contains("-"))
{
MessageBox.Show("Select Meter type", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
if (cmbMeterUnits.Text.Contains("-"))
{
MessageBox.Show("Select Meter units", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
/*
* if no ID was sent --> create new entry in DB
* else get entry by ID, update entry and save changes
*/
if (meter_ID == 0) // new entry
{
// try save to DB
try
{
using (DB_context context = new DB_context())
{
tbl_Meters new_meter = new tbl_Meters();
new_meter.METER_NAME = txtMeterName.Text.Trim();
new_meter.METER_TYPE = cmbMeterType.Text;
new_meter.METER_UNITS = cmbMeterUnits.Text;
// add new
context.db_Meters.Add(new_meter);
context.SaveChanges();
}
}
catch (Exception ex)
{
MessageBox.Show("Error saving to DB. \r\n\r\n" + ex.Message, "Exception", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
}
else // edit entry
{
try
{
using (DB_context context = new DB_context())
{
tbl_Meters new_meter = new tbl_Meters();
var query = (from c in context.db_Meters where c.ID_METER == meter_ID select c).FirstOrDefault();
query.METER_NAME = txtMeterName.Text.Trim();
query.METER_TYPE = cmbMeterType.Text;
query.METER_UNITS = cmbMeterUnits.Text;
// save edited entry
context.SaveChanges();
}
}
catch (Exception ex)
{
MessageBox.Show("Error saving to DB. \r\n\r\n" + ex.Message, "Exception", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
}
return(true);
} // save to DB
} // edit meter
// function for deleting Meter and corresponding readings
private void DeleteMeter(object sender, RoutedEventArgs e)
{
/*
* find selected row, get ID from first column (hidden column "ID")
* find entry in DB based on ID,
*/
MessageBoxResult result = MessageBox.Show("Delete this meter and this meter readouts?", "Info", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (result == MessageBoxResult.Yes)
{
for (var vis = sender as Visual; vis != null; vis = VisualTreeHelper.GetParent(vis) as Visual)
{
if (vis is DataGridRow)
{
var row = (DataGridRow)vis;
int id = Convert.ToInt32((tblMeters.SelectedCells[0].Column.GetCellContent(row) as TextBlock).Text);
using (EnergyMetersEntities context = new EnergyMetersEntities())
{
/*
* TRY Using stored procedure to delete meter from tbl_Meters
* and all readouts with matching ID_METER from tbl_Readouts
*
* IN CASE OF STORED PROCEDURE FAIL
* try using linq expression
*/
try
{
context.DeleteMeterAndReadouts(id);
MessageBox.Show("Success!", "Info", MessageBoxButton.OK, MessageBoxImage.Asterisk);
}
catch
{
try
{
using (DB_context EF_context = new DB_context())
{
var meter = (from c in EF_context.db_Meters where c.ID_METER == id select c).FirstOrDefault();
EF_context.db_Meters.Remove(meter);
var readouts = (from c in EF_context.db_Readouts where c.ID_METER == id select c).ToList();
foreach (var item in readouts)
{
EF_context.db_Readouts.Remove(item);
}
EF_context.SaveChanges();
MessageBox.Show("Success!", "Info", MessageBoxButton.OK, MessageBoxImage.Asterisk);
} // using
}
catch (Exception ex)
{
MessageBox.Show("Error, DataBase is not available.\r\n\r\n" + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
} // catch Stored procedure
} // using ...
} // if is datagrid row
} // for ... cycle
} // if result == yes
// update list on finish
LoadMeterList();
} // delete meter
