/// <summary>
/// Funkcja pozwalająca dodać nowy rekord do bazy danych.
/// </summary>
public void InsertIntoDB()
{
try
{
using (var database = new CollectorsManagementSystemEntities())
{
database.Readings.Add(this);
database.SaveChanges();
}
}
catch (DbEntityValidationException ex)
{
var errorMessages = String.Empty;
foreach (var validationResult in ex.EntityValidationErrors)
{
string entityName = validationResult.Entry.Entity.GetType().Name;
foreach (DbValidationError error in validationResult.ValidationErrors)
{
errorMessages += entityName + "." + error.PropertyName + ": " + error.ErrorMessage + "\n";
}
}
MessageBox.Show(errorMessages);
}
catch (DbUpdateException ex)
{
ExceptionHandling.ShowException(ex);
}
}
/// <summary>
/// Generowanie pliku z BackUp-em bazy danych SZI.
/// </summary>
/// <returns>Informacje o poprawności utworzenia pliku.</returns>
public bool GenerateBackUp(string filePath)
{
using (StreamWriter file = new StreamWriter(filePath))
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
{
file.WriteLine(ClearTable());
}
{
file.WriteLine(TableCommentBackUp("Address"));
file.WriteLine(AddressTableBackUp());
foreach (var element in dataBase.Addresses)
file.WriteLine(AddressBackUp(element.AddressId, element.HouseNo, element.FlatNo, element.AreaId));
}
{
file.WriteLine(TableCommentBackUp("Area"));
file.WriteLine(AreaTableBackUp());
foreach (var element in dataBase.Areas)
file.WriteLine(AreaBackUp(element.AreaId, element.Street, element.CollectorId));
}
{
file.WriteLine(TableCommentBackUp("Collector"));
file.WriteLine(CollectorTableBackUp());
foreach (var element in dataBase.Collectors)
file.WriteLine(CollectorBackUp(element.CollectorId, element.Name, element.LastName,
element.PostalCode, element.City, element.Address, element.PhoneNumber));
}
{
file.WriteLine(TableCommentBackUp("Counter"));
file.WriteLine(CounterTableBackUp());
foreach (var element in dataBase.Counters)
file.WriteLine(CounterBackUp(element.CounterNo, element.CircuitNo, element.AddressId, element.CustomerId));
}
{
file.WriteLine(TableCommentBackUp("Customer"));
file.WriteLine(CustomerTableBackUp());
foreach (var element in dataBase.Customers)
file.WriteLine(CustomerBackUp(element.CustomerId, element.Name, element.LastName,
element.PostalCode, element.City, element.Address, element.PhoneNumber));
}
{
file.WriteLine(TableCommentBackUp("Reading"));
file.WriteLine(ReadingTableBackUp());
foreach (var element in dataBase.Readings)
file.WriteLine(ReadingBackUp(element.ReadingId, element.Date, element.Value, element.CollectorId, element.CounterNo));
}
}
}
if (File.Exists(filePath))
return true;
else
return false;
}
/// <summary>
/// Sprawdza czy licznik z ID istnieje w bazie.
/// </summary>
/// <param name="ID">Sprawdzane ID.</param>
/// <returns>Wartośc logiczna określająca czy licznik z ID istnieje w bazie.</returns>
public static bool CounterExists(int ID)
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
var Counters = from c in dataBase.Counters
select c.CounterNo;
foreach (int counterNo in Counters)
if (counterNo == ID)
return true;
}
return false;
}
/// <summary>
/// Usuwa wszystkie rekordy z wszystkich tabel.
/// </summary>
public static void ClearDataBase()
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
string query = @"DELETE FROM Collector;" +
"DELETE FROM Customer;" +
"DELETE FROM Area;" +
"DELETE FROM Counter;" +
"DELETE FROM Address;" +
"DELETE FROM Reading;";
dataBase.Database.ExecuteSqlCommand(query);
}
}
public void ModifyRecord(string id)
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
dataBase.Database.ExecuteSqlCommand
(
"UPDATE Area SET Street={0}, CollectorId={1} WHERE AreaId={2}",
this.Street,
this.CollectorId,
id
);
dataBase.SaveChanges();
}
}
public void ModifyRecord(string id)
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
dataBase.Database.ExecuteSqlCommand
(
"UPDATE Address SET HouseNo={0}, FlatNo={1}, AreaId={2} WHERE AddressId={3}",
this.HouseNo,
this.FlatNo,
this.AreaId,
id
);
dataBase.SaveChanges();
}
}
public void ModifyRecord(string id)
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
dataBase.Database.ExecuteSqlCommand
(
"UPDATE Counter SET CircuitNo={0}, AddressId={1}, CustomerId={2} WHERE CounterNo={3}",
this.CircuitNo,
this.AddressId,
this.CustomerId,
id
);
dataBase.SaveChanges();
}
}
public void InsertIntoDB()
{
try
{
using (var database = new CollectorsManagementSystemEntities())
{
this.PostalCode = Regex.Replace(this.PostalCode, "-", "");
database.Customers.Add(this);
database.SaveChanges();
}
}
catch (DbUpdateException Ex)
{
ExceptionHandling.ShowException(Ex);
}
}
//zwraca imię i nazwisko inkasenta na podstawie jego ID
public static string FetchCollector(string CollectorID)
{
string FullName = "";
using (var database = new CollectorsManagementSystemEntities())
{
var collector = from c in database.Collectors
where c.CollectorId == CollectorID
select c;
if (collector.Count() == 1)
{
foreach (Collector c in collector)
FullName = c.Name + " " + c.LastName;
}
}
return FullName;
}
public void InsertIntoDB()
{
try
{
using (var database = new CollectorsManagementSystemEntities())
{
database.Areas.Add(this);
database.SaveChanges();
}
}
catch (DbUpdateException ex)
{
var innerEx = ex.InnerException;
while (innerEx.InnerException != null)
innerEx = innerEx.InnerException;
System.Windows.Forms.MessageBox.Show(innerEx.Message);
}
}
//zwraca przypisany do adresu teren i przypisanego do niego inkasenta
public static string FetchAreaAndCollector(Guid AreaID)
{
string AreaAndCollector = "";
using (var database = new CollectorsManagementSystemEntities())
{
var Result = from area in database.Areas
join collector in database.Collectors on area.CollectorId equals collector.CollectorId into areaJoinedCollector
where area.AreaId == AreaID
from collector in areaJoinedCollector.DefaultIfEmpty()
select new { Area = area, Collector = collector == null ? String.Empty : ": " + collector.Name + " " + collector.LastName };
if (Result.Count() == 1)
{
foreach (var item in Result)
AreaAndCollector = item.Area.Street + item.Collector;
}
}
return AreaAndCollector;
}
/// <summary>
/// Zwraca rekordy powiązane z zaznaczonym w ListView Inkasentem
/// </summary>
/// <returns>Rekordy powiązane z zaznaczonym w ListView Inkasentem</returns>
public static List<List<string>> ReturnRecordsAssociatedWithCollector(string id)
{
string CollectorID = id;
List<List<string>> AssociatedRecords = new List<List<string>>();
using (var database = new CollectorsManagementSystemEntities())
{
var foreignResult = (from f in database.Areas
where f.CollectorId == CollectorID
select f).ToList();
for (int i = 0; i < foreignResult.Count(); i++)
{
AssociatedRecords.Add(new List<string>());
AssociatedRecords[i].Add(foreignResult[i].AreaId.ToString());
AssociatedRecords[i].Add(foreignResult[i].Street);
}
}
return AssociatedRecords;
}
/// <summary>
/// Zwraca rekordy powiązane z zaznaczonym w ListView Terenem
/// </summary>
/// <returns>Rekordy powiązane z zaznaczonym w ListView Terenem</returns>
public static List<List<string>> ReturnRecordsAssociatedWithArea(string id)
{
Guid AreaID = new Guid(id);
List<List<string>> AssociatedRecords = new List<List<string>>();
using (var database = new CollectorsManagementSystemEntities())
{
var foreignResult = (from f in database.Addresses
where f.AreaId == AreaID
select f).ToList();
for (int i = 0; i < foreignResult.Count(); i++)
{
AssociatedRecords.Add(new List<string>());
AssociatedRecords[i].Add(foreignResult[i].AddressId.ToString());
AssociatedRecords[i].Add(foreignResult[i].HouseNo.ToString());
AssociatedRecords[i].Add(foreignResult[i].FlatNo.ToString());
}
}
return AssociatedRecords;
}
/// <summary>
/// Usuwanie odczytu z bazy danych.
/// </summary>
/// <param name="id">Id odczytu usuwanego.</param>
public static void DeleteReadFromDB(System.Guid id)
{
try
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
var items = from reading in dataBase.Readings
where reading.ReadingId == id
select reading;
foreach (var item in items)
{
dataBase.Readings.Remove(item);
}
dataBase.SaveChanges();
}
}
catch (DbUpdateException ex)
{
ExceptionHandling.ShowException(ex);
}
}
/// <summary>
/// Zwraca rekordy powiązane z zaznaczonym w ListView Adresem
/// </summary>
/// <returns>Rekordy powiązane z zaznaczonym w ListView Adresem</returns>
public static List<List<string>> ReturnRecordsAssociatedWithAddress(string id)
{
Guid AddressID = new Guid(id);
List<List<string>> AssociatedRecords = new List<List<string>>();
using (var database = new CollectorsManagementSystemEntities())
{
var foreignResult = (from f in database.Counters
where f.AddressId == AddressID
select f).ToList();
for (int i = 0; i < foreignResult.Count(); i++)
{
AssociatedRecords.Add(new List<string>());
AssociatedRecords[i].Add(foreignResult[i].CounterNo.ToString());
AssociatedRecords[i].Add(foreignResult[i].CircuitNo.ToString());
AssociatedRecords[i].Add(Counters.FetchFullAddress(foreignResult[i].AddressId.Value));
AssociatedRecords[i].Add(Counters.FetchCustomer(foreignResult[i].CustomerId));
}
}
return AssociatedRecords;
}
/// <summary>
/// Sprawdza czy adres z ID istnieje w bazie.
/// </summary>
/// <param name="ID">Sprawdzane ID.</param>
/// <returns>Wartośc logiczna określająca czy adres z ID istnieje w bazie.</returns>
private static bool AddressExists(Guid ID)
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
var Addresses = from a in dataBase.Addresses
select a.AddressId;
foreach (Guid addressID in Addresses)
if (addressID == ID)
return true;
}
return false;
}
/// <summary>
/// Metoda inicjalizująca wyświetlanie listy rekordów.
/// </summary>
public void InitializeForm()
{
listViewC = new ListView();
cCollection = new CountersCollection();
cCollection.collectorId = this.collectorId;
int i = 1;
using (var dataBase = new CollectorsManagementSystemEntities())
{
var items = from collector in dataBase.Collectors
join area in dataBase.Areas on collector.CollectorId equals area.CollectorId
join address in dataBase.Addresses on area.AreaId equals address.AreaId
join counter in dataBase.Counters on address.AddressId equals counter.AddressId
where collector.CollectorId == this.collectorId
select new
{
CounterNo = counter.CounterNo,
CircuitNo = counter.CircuitNo,
CounterAddress = area.Street + " " + address.HouseNo + "/" + address.FlatNo,
CustomerId = counter.CustomerId
};
foreach (var element in items)
{
var dateSub = DateTime.Now.Subtract(new TimeSpan(30, 0, 0, 0));
var firstMethod = from read in dataBase.Readings
where read.Date > dateSub
where read.CounterNo == element.CounterNo
select read;
if (firstMethod.Count() == 0)
{
string date, value, collectorId, collectorName, customerName;
date = value = collectorName = String.Empty;
var lastReading = from read in dataBase.Readings
where read.CounterNo == element.CounterNo
select read;
if (lastReading.Count() > 0)
{
var lastRead = lastReading.OrderByDescending(x => x.Date).FirstOrDefault();
date = lastRead.Date.ToShortDateString() ?? String.Empty;
value = lastRead.Value.ToString() ?? String.Empty;
collectorId = lastRead.CollectorId ?? String.Empty;
try
{
var collectorRead = (from collector in dataBase.Collectors
where collector.CollectorId == collectorId
select new { collector.Name, collector.LastName }).FirstOrDefault();
collectorName = collectorId + " " + collectorRead.Name + " " + collectorRead.LastName;
}
catch
{
collectorName = "---";
}
}
else
date = value = collectorName = String.Empty;
var cutomerRead = (from customer in dataBase.Customers
where element.CustomerId == customer.CustomerId
select new { customer.Name, customer.LastName }).FirstOrDefault();
if (cutomerRead != null)
customerName = cutomerRead.Name + " " + cutomerRead.LastName;
else
customerName = String.Empty;
{
CounterXML newItem = new CounterXML();
newItem.ReadId = i++.ToString();
newItem.CounterNo = element.CounterNo.ToString();
newItem.CircuitNo = element.CircuitNo.ToString();
newItem.Customer = customerName;
newItem.Address = element.CounterAddress;
newItem.LastReadDate = date;
newItem.LastValue = value;
newItem.LastCollector = collectorName;
newItem.NewValue = String.Empty;
cCollection.AddNewElement(newItem);
nrOfCounters++;
}
}
}
}
listViewC = ListViewConfig.ListViewInit(columnList, this.GetType().Name);
if ( cCollection.RecordsCount > 0 )
foreach (var element in cCollection.counter)
ListViewConfig.AddItem(listViewC, element.PrintStringArray);
listViewC.MultiSelect = false;
listViewC.Size = new System.Drawing.Size(750, 450);
if (nrOfCounters == 0)
btExport.Enabled = false;
this.Controls.Add(listViewC);
}
/// <summary>
/// Usuwa wybrane rekordy z tabeli Licznik. Rekordy z tabeli Odczyt powiązane z usuwanymi rekordami tabeli Licznik również są usuwane.
/// </summary>
/// <param name="IDs">Lista identyfikatorów rekordów tabeli Licznik przeznaczonych do usunięcia.</param>
/// <param name="idIsForeignKey">true - do kasowanych rekordów istnieją odniesienia w innych tabelach.</param>
private static void DeleteFromCounters(List<string> IDs, bool idIsForeignKey)
{
List<Int32> guidIDs = new List<Int32>(IDs.Count);
for (int i = 0; i < IDs.Count; i++)
guidIDs.Insert(i, Convert.ToInt32(IDs[i]));
using (var database = new CollectorsManagementSystemEntities())
{
foreach (var Id in guidIDs)
{
var counters = from counter in database.Counters
where counter.CounterNo == Id
select counter;
if (counters.Count() > 0)
{
foreach (Counter c in counters)
database.Counters.Remove(c);
}
if (idIsForeignKey)
{
var foreignResult = from f in database.Readings where f.CounterNo == Id select f;
if (foreignResult.Count() > 0)
{
List<string> foreignList = new List<string>();
foreach (Reading r in foreignResult)
foreignList.Add(r.ReadingId.ToString());
DeleteFromReadings(foreignList);
}
}
database.SaveChanges();
}
}
}
/// <summary>
/// Generuje losowo odczyty.
/// </summary>
static void GenerateReadings()
{
Random rnd = new Random();
Reading reading;
Guid g;
using (var dataBase = new CollectorsManagementSystemEntities())
{
for (int i = 0; i < numberOfReadings; i++)
{
reading = new Reading();
do
{
g = Guid.NewGuid();
} while (g == Guid.Empty);
reading.ReadingId = g;// Guid.NewGuid();
reading.CounterNo = Convert.ToInt32(ChooseRandomId(0));
//var counters = (from counter in dataBase.Counters
// select counter.CounterNo).ToList();
List<int> test = (from c in dataBase.Counters select c.CounterNo).ToList();
var tmp = (from c in dataBase.Counters where c.CounterNo == reading.CounterNo select c.AddressId).FirstOrDefault();
var tmp2 = (from a in dataBase.Addresses where a.AddressId == tmp select a.AreaId).FirstOrDefault();
reading.CollectorId = (from a in dataBase.Areas where a.AreaId == tmp2 select a.CollectorId).FirstOrDefault().ToString();
var friendsOfReading = from r in dataBase.Readings where r.CounterNo == reading.CounterNo orderby r.Date descending select r;
if (friendsOfReading.Count() == 0)
reading.Value = rnd.Next(0, 1000);
else
{
reading.Value = friendsOfReading.FirstOrDefault().Value + rnd.Next(0, 1000);
foreach (Reading friendOfReading in friendsOfReading)
friendOfReading.Date = friendOfReading.Date.AddDays(-30);
dataBase.SaveChanges();
}
reading.Date = DateTime.Now;
reading.InsertIntoDB();
}
}
}
/// <summary>
/// Umieszcza kontrolki na formularzu i inicjalizuje je oraz wybiera dla nich metody walidujące.
/// </summary>
/// <param name="ids">Identyfikatory rekordów zaznaczonych w momencie tworzenia formularza.</param>
/// <param name="selectedTab">Karta, z której otwarto formularz.</param>
public ModifyForm(List<string> ids, Tables Table, ConfigManagementForm configManagementForm)
{
InitializeComponent();
mainForm = configManagementForm;
dataBase = new CollectorsManagementSystemEntities();
ErrorProvider ep;
int i;
this.FormBorderStyle = FormBorderStyle.FixedSingle;
this.MaximizeBox = false;
this.MinimizeBox = false;
this.Text = "Modyfikacja rekordu";
this.ids = ids;
this.Table = Table;
switch (Table)
{
case Tables.Collectors:
labelsTexts = new string[] { "Id inkasenta: ", "Imię: ", "Nazwisko: ", "Kod pocztowy: ", "Miasto: ", "Ulica: ", "Telefon kontaktowy: " };
textBoxesNames = new string[] { "CollectorId", "Name", "LastName", "PostalCode", "City", "Address", "PhoneNumber" };
Collector modifiedCollector = dataBase.Collectors.SqlQuery("SELECT * FROM Collector WHERE CollectorId={0}", ids.ElementAt(0)).SingleOrDefault();
textBoxesTexts = new string[] { modifiedCollector.CollectorId, modifiedCollector.Name, modifiedCollector.LastName, Regex.Replace(modifiedCollector.PostalCode, "([0-9]{2})([0-9]{3})", "${1}-${2}"), modifiedCollector.City, modifiedCollector.Address, modifiedCollector.PhoneNumber };
break;
case Tables.Customers:
labelsTexts = new string[] { "Id klienta: ", "Imię: ", "Nazwisko: ", "Kod pocztowy: ", "Miasto: ", "Ulica: ", "Telefon kontaktowy: " };
textBoxesNames = new string[] { "CustomerId", "Name", "LastName", "PostalCode", "City", "Address", "PhoneNumber" };
Customer modifiedCustomer = dataBase.Customers.SqlQuery("SELECT * FROM Customer WHERE CustomerId={0}", ids.ElementAt(0)).SingleOrDefault();
textBoxesTexts = new string[] { modifiedCustomer.CustomerId, modifiedCustomer.Name, modifiedCustomer.LastName, Regex.Replace(modifiedCustomer.PostalCode, "([0-9]{2})([0-9]{3})", "${1}-${2}"), modifiedCustomer.City, modifiedCustomer.Address, modifiedCustomer.PhoneNumber };
break;
case Tables.Areas:
labelsTexts = new string[] { "Id terenu: ", "Ulica: ", "Id inkasenta: " };
textBoxesNames = new string[] { "AreaId", "Street"};
Area modifiedArea = dataBase.Areas.SqlQuery("SELECT * FROM Area WHERE AreaId={0}", ids.ElementAt(0)).SingleOrDefault();
textBoxesTexts = new string[] { modifiedArea.AreaId.ToString(), modifiedArea.Street};
comboBoxesNames = new string[] { "cbCollector" };
comboBoxesKeys = new string[] { modifiedArea.CollectorId };
TableNames = new string[] {"Collector"};
break;
case Tables.Counters:
labelsTexts = new string[] { "Numer licznika: ", "Numer układu: ", "Id adresu: ", "Id klienta: " };
textBoxesNames = new string[] { "CounterNo", "CircuitNo" };
Counter modifiedCounter = dataBase.Counters.SqlQuery("SELECT * FROM Counter WHERE CounterNo={0}", ids.ElementAt(0)).SingleOrDefault();
textBoxesTexts = new string[] { modifiedCounter.CounterNo.ToString(), modifiedCounter.CircuitNo.ToString() };
comboBoxesNames = new string[] { "cbAddress", "cbCustomer" };
comboBoxesKeys = new string[] { modifiedCounter.AddressId.ToString(), modifiedCounter.CustomerId };
TableNames = new string[] {"Address", "Customer"};
break;
case Tables.Addresses:
labelsTexts = new string[] { "Id adresu: ", "Numer domu: ", "Numer mieszkania: ", "Id terenu: " };
textBoxesNames = new string[] { "AddressId", "HouseNo", "FlatNo" };
Address modifiedAddress = dataBase.Addresses.SqlQuery("SELECT * FROM Address WHERE AddressId={0}", ids.ElementAt(0)).SingleOrDefault();
textBoxesTexts = new string[] { modifiedAddress.AddressId.ToString(), modifiedAddress.HouseNo.ToString(), modifiedAddress.FlatNo.ToString()};
comboBoxesNames = new string[] { "cbArea" };
comboBoxesKeys = new string[] { modifiedAddress.AreaId.ToString() };
TableNames = new string[] {"Area"};
break;
default:
break;
}
NameToMethod_Dict = Auxiliary.Modify_CreateNameToMethodDict();
Label[] labels = InitializeLabels();
TextBox[] textBoxes = InitializeTextAndCBConfigs();
ControlToEP_Dict = new Dictionary<Control, ErrorProvider>();
ControlToBool_Dict = new Dictionary<Control, bool>();
for (i = 0; i < textBoxesNames.Length; i++) //inicjowanie labeli które opisują textboxy
{
this.Controls.Add(labels[i]);
this.Controls.Add(textBoxes[i]);
if (i != 0)
{
ep = Auxiliary.InitializeErrorProvider(textBoxes[i]);
ControlToEP_Dict.Add(textBoxes[i], ep);
ControlToBool_Dict.Add(textBoxes[i], true);
textBoxes[i].Validating += Validation;
}
}
if (comboBoxesNames!=null)
for (int j = 0; j < comboBoxesNames.Length; j++)
{
this.Controls.Add(labels[i + j]);
this.Controls.Add(CBConfigs[j].comboBox);
ep = Auxiliary.InitializeErrorProvider(CBConfigs[j].comboBox);
ControlToEP_Dict.Add(CBConfigs[j].comboBox, ep);
ControlToBool_Dict.Add(CBConfigs[j].comboBox, true);
if ((int)Table != 3)
CBConfigs[j].comboBox.Validating += ComboBoxValidation;
else //jeśli modyfikowany jest wpis w Counters
CBConfigs[j].comboBox.Validating += CountersValidation;
}
}
/// <summary>
/// Pobieranie listy liczników z bazy danych i konwertowanie do formatu używanego w programie.
/// </summary>
private void GenerateCountersList()
{
List<string[]> Counters = null;
using (var database = new CollectorsManagementSystemEntities())
{
counterList = (from counter in database.Counters
select counter).ToList();
var result = (from counter in database.Counters
join address in database.Addresses on counter.AddressId equals address.AddressId into gj
join customer in database.Customers on counter.CustomerId equals customer.CustomerId into test
select new
{
circuitno = counter.CircuitNo,
counterno = counter.CounterNo,
address =
(
from subAddress in database.Addresses
where subAddress.AddressId == counter.AddressId
join area in database.Areas on subAddress.AreaId equals area.AreaId
select area.Street + " " + subAddress.HouseNo + (subAddress.FlatNo.HasValue ? "/" + subAddress.FlatNo.Value : "")
).ToList(),
customer =
(
from subCustomer in database.Customers
where subCustomer.CustomerId == counter.CustomerId
select subCustomer.Name + " " + subCustomer.LastName
).ToList()
}).ToList();
Counters = new List<string[]>(result.Count());
for (int i = 0; i < result.Count(); i++)
{
Counters.Add(new string[4]);
Counters[i][0] = result[i].counterno.ToString();
Counters[i][1] = result[i].circuitno.ToString();
Counters[i][2] = result[i].address.Count == 0 ? "" : result[i].address[0];
Counters[i][3] = result[i].customer.Count == 0 ? "" : result[i].customer[0];
}
}
this.itemList = Counters;
}
//zwraca pełny adres (ulica, numer domu i mieszkania) na podstawie AddressID
/// <summary>
/// Zwraca pełny adres (ulica, numer domu i mieszkania) na podstawie AddressID.
/// </summary>
/// <param name="AddressID">Id adresu.</param>
/// <returns>Pełny adres.</returns>
public static string FetchFullAddress(Guid AddressID)
{
string FullAddressString = "";
using (var database = new CollectorsManagementSystemEntities())
{
var FullAddress = from address in database.Addresses
join area in database.Areas on address.AreaId equals area.AreaId
where address.AddressId == AddressID
select new { Address = address, Area = area };
if (FullAddress.Count() == 1)
{
foreach (var fa in FullAddress)
{
FullAddressString = fa.Area.Street + " " + fa.Address.HouseNo.ToString();
if (fa.Address.FlatNo != null)
FullAddressString += "/" + fa.Address.FlatNo.ToString();
}
}
}
return FullAddressString;
}
/// <summary>
/// Funkcja pozwalająca inicjować formę odczyty.
/// </summary>
private void InitializeReading()
{
modifRecord = new Reading();
using (var dataBase = new CollectorsManagementSystemEntities())
{
try
{
var item = (from reading in dataBase.Readings
where reading.ReadingId == editId
select reading).FirstOrDefault();
{
modifRecord.CollectorId = item.CollectorId;
modifRecord.CounterNo = item.CounterNo;
modifRecord.Date = item.Date;
modifRecord.ReadingId = item.ReadingId;
modifRecord.Value = item.Value;
}
comboBoxesKeys = item.CollectorId;
textBoxesTexts = new string[] { item.ReadingId.ToString(), item.CounterNo.ToString(), item.Date.ToString(), item.Value.ToString() };
Label[] labels = InitializeLabels();
TextBox[] textBoxes = InitializeText();
CBConfig = new ComboBoxConfig(TableNames, comboBoxesNames, new Point(150, 30 * (5)), comboBoxesKeys);
this.Controls.AddRange(labels);
this.Controls.AddRange(textBoxes);
this.Controls.Add(CBConfig.comboBox);
}
catch
{
MessageBox.Show("Błąd ładowania odczytu!");
}
}
}
/// <summary>
/// Sprawdza czy klient z ID istnieje w bazie.
/// </summary>
/// <param name="ID">Sprawdzane ID.</param>
/// <returns>Wartośc logiczna określająca czy klient z ID istnieje w bazie.</returns>
public static bool CustomerExists(string ID)
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
var Customers = from c in dataBase.Customers
select c.CustomerId;
foreach (string customerID in Customers)
if (customerID == ID)
return true;
}
return false;
}
/// <summary>
/// Usuwa rekordy z tabeli Klient. Identyfikatory usuwanych rekordów z tej tabeli będące kluczami obcymi w tabeli Licznik zamieniane są na wartość null.
/// Zasady nałożone na bazę danych wymagają również ustawienia na wartość null odpowiednich kluczy obcych adresów w tabeli Licznik.
/// </summary>
/// <param name="IDs">Lista identyfikatorów rekordów przeznaczonych do usunięcia.</param>
/// <param name="idIsForeignKey">true - do kasowanych rekordów istnieją odniesienia w innych tabelach.</param>
private static void DeleteFromCustomers(List<string> IDs, bool idIsForeignKey)
{
using (var database = new CollectorsManagementSystemEntities())
{
foreach (var id in IDs)
{
var result = from r in database.Customers where r.CustomerId == id select r;
if (result.Count() > 0)
{
foreach (Customer c in result)
database.Customers.Remove(c);
}
if (idIsForeignKey)
{
var foreginResult = from f in database.Counters where f.CustomerId == id select f;
if (result.Count() > 0)
{
foreach (Counter c in foreginResult)
{
c.CustomerId = null;
c.AddressId = null;
}
}
}
database.SaveChanges();
}
}
}
private void GenerateItemList()
{
collectorList.Clear();
using (var dataBase = new CollectorsManagementSystemEntities())
{
foreach (var value in dataBase.Collectors)
collectorList.Add(value);
}
}
/// <summary>
/// Usuwa rekordy z tabeli Odczyt.
/// </summary>
/// <param name="IDs">Lista identyfikatorów rekordów tabeli Odczyt przeznaczonych do usunięcia.</param>
private static void DeleteFromReadings(List<string> IDs)
{
List<Guid> guidIDs = new List<Guid>(IDs.Count);
for (int i = 0; i < IDs.Count; i++)
guidIDs.Insert(i, new Guid(IDs[i]));
using (var database = new CollectorsManagementSystemEntities())
{
foreach (var id in guidIDs)
{
var result = from r in database.Readings where r.ReadingId == id select r;
if (result.Count() > 0)
{
foreach (Reading r in result)
database.Readings.Remove(r);
}
database.SaveChanges();
}
}
}
/// <summary>
/// Sprawdza, czy dla danego rekordu nie ma odniesienia w tabelach, które są w związku z tabelą, z której pochodzi rekord.
/// </summary>
/// <param name="tableName">Nazwa tabeli, z której pochodzi rekord.</param>
/// <param name="id">Klucz rekordu.</param>
/// <returns>true - odniesienie do rekordu znalezione. false - odniesienie do rekordu nieznalezione.</returns>
public static bool IdExistsInOtherTable(string tableName, string id)
{
int count;
Guid guidId;
using (var dataBase = new CollectorsManagementSystemEntities())
{
switch (tableName)
{
case "Collector":
count = (from a in dataBase.Areas where a.CollectorId == id select a).Count() + (from r in dataBase.Readings where r.CollectorId == id select r).Count();
break;
case "Customer":
count = (from c in dataBase.Counters where c.CustomerId == id select c).Count();
break;
case "Area":
guidId = new Guid(id);
count = (from a in dataBase.Addresses where a.AreaId == guidId select a).Count();
break;
case "Address":
guidId = new Guid(id);
count = (from c in dataBase.Counters where c.AddressId == guidId select c).Count();
break;
case "Counter":
int numberId = Convert.ToInt32(id);
count = (from r in dataBase.Readings where r.CounterNo == numberId select r).Count();
break;
default:
count = 0;
break;
}
}
if (count > 0)
return true;
else
return false;
}
/// <summary>
/// Modyfikuje odpowiedni rekord.
/// </summary>
/// <param name="id">Id modyfikowanego rekordu.</param>
public void ModifyRecord(string id)
{
using (var dataBase = new CollectorsManagementSystemEntities())
{
dataBase.Database.ExecuteSqlCommand
(
"UPDATE Customer SET Name={0}, LastName={1}, PostalCode={2}, City={3}, Address={4}, PhoneNumber={5} WHERE CustomerId={6}",
this.Name,
this.LastName,
Regex.Replace(this.PostalCode, "-", ""),
this.City,
this.Address,
this.PhoneNumber,
id
);
dataBase.SaveChanges();
}
}
/// <summary>
/// Wczytywanie pliku z BackUp-em bazy danych SZI.
/// </summary>
/// <returns>Informacje o poprawności wczytania pliku.</returns>
public bool RestoreBackUp(string filePath)
{
if (!File.Exists(filePath))
return false;
using (StreamReader file = new StreamReader(filePath))
{
string fileRead = file.ReadToEnd();
using (var dataBase = new CollectorsManagementSystemEntities())
{
dataBase.Database.ExecuteSqlCommand(fileRead);
}
}
return true;
}
