public void NewFirm(Contact list)
{
string firmname = list.Name;
string UID = list.UID;
string adress = list.Adresse;
string billingadress = list.Rechnungsadresse;
string deliveryadress = list.Lieferadresse;
try
{
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
//SQL Statement zum auslesen
string query = "INSERT INTO Firma VALUES (@firmname, @UID, @lastname, @suffix, @birthday)";
string query2 = "INSERT INTO Kontakte VALUES(@adress, @billingadress, @deliveryadress)";
SqlCommand cmdInsert1 = new SqlCommand(query, db);
SqlCommand cmdInsert2 = new SqlCommand(query2, db);
cmdInsert1.Parameters.AddWithValue("@firmname", firmname);
cmdInsert1.Parameters.AddWithValue("@UID", UID);
cmdInsert2.Parameters.AddWithValue("@adress", adress);
cmdInsert2.Parameters.AddWithValue("@billingadress", billingadress);
cmdInsert2.Parameters.AddWithValue("@deliveryadress", deliveryadress);
db.Close();
}
}
catch (Exception)
{
throw new Exception("Inserting Firm failed.");
}
}
public void NewContacts(Contact list)
{
string title = list.Titel;
string firstname = list.Vorname;
string lastname = list.Nachname;
string suffix = list.Suffix;
DateTime birthday = list.Geburtsdatum;
string adress = list.Adresse;
string billingadress = list.Rechnungsadresse;
string deliveryadress = list.Lieferadresse;
try
{
//List<Contact> list = new List<Contact>();
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
//SQL Statement zum auslesen
string query = "INSERT INTO Person VALUES (@title, @firstname, @lastname, @suffix, @birthday)";
//string query2 = "INSERT INTO ";
SqlCommand cmdInsert1 = new SqlCommand(query, db);
//SqlCommand cmdUpdate2 = new SqlCommand(query2, db);
cmdInsert1.Parameters.AddWithValue("@title", title);
cmdInsert1.Parameters.AddWithValue("@firstname", firstname);
cmdInsert1.Parameters.AddWithValue("@lastname", lastname);
cmdInsert1.Parameters.AddWithValue("@suffix", suffix);
cmdInsert1.Parameters.AddWithValue("@birthday", birthday);
//cmdUpdate2.Parameters.AddWithValue("@adress", adress);
//cmdUpdate2.Parameters.AddWithValue("@billingadress", billingadress);
//cmdUpdate2.Parameters.AddWithValue("@deliveryadress", deliveryadress);
db.Close();
}
}
catch (Exception)
{
throw new Exception("Inserting Contact failed.");
}
}
public ContactsList searchID(int id)
{
try
{
ContactsList list = new ContactsList();
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
//SQL Statement zum auslesen
string query = "SELECT ID_Person, Titel, Vorname, Nachname, Suffix, Geburtsdatum, Adresse, Rechnungsadresse, Lieferadresse FROM Kontakte inner join Person on ID_Kontakte = FK_Kontakte WHERE [ID_Person] = @id";
SqlCommand cmdSelect = new SqlCommand(query, db);
cmdSelect.Parameters.AddWithValue("@id", id);
using (SqlDataReader rd = cmdSelect.ExecuteReader())
{
// Daten holen
while (rd.Read())
{
Console.WriteLine("ID:{0} & Vorname:{1}", rd["ID_Person"], rd["Vorname"]);
Contact instance = new Contact();
instance.ID = rd.GetInt32(0);
instance.Titel = rd.GetString(1);
instance.Vorname = rd.GetString(2);
instance.Nachname = rd.GetString(3);
instance.Suffix = rd.GetString(4);
instance.Geburtsdatum = rd.GetDateTime(5);
instance.Adresse = rd.GetString(6);
instance.Rechnungsadresse = rd.GetString(7);
instance.Lieferadresse = rd.GetString(8);
list.contact.Add(instance);
}
rd.Close();
}
db.Close();
return list;
}
}
catch (Exception)
{
throw new Exception("Searching Contact ID failed");
}
}
public ContactsList searchContacts(string text)
{
try
{
ContactsList list = new ContactsList();
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
string query = "SELECT ID_Person, Titel, Vorname, Nachname, Suffix, Geburtsdatum, Adresse, Rechnungsadresse, Lieferadresse FROM Kontakte inner join Person on ID_Kontakte = FK_Kontakte WHERE [Vorname] = @text or [Nachname] = @text";
SqlCommand cmdSelect = new SqlCommand(query, db);
cmdSelect.Parameters.AddWithValue("@text", text);
using (SqlDataReader rd = cmdSelect.ExecuteReader())
{
// Daten holen
while (rd.Read())
{
Console.WriteLine("ID:{0} & Vorname:{1}", rd["ID_Person"], rd["Vorname"]);
Contact contact = new Contact();
contact.ID = rd.GetInt32(0);
contact.Titel = rd.GetString(1);
contact.Vorname = rd.GetString(2);
contact.Nachname = rd.GetString(3);
contact.Suffix = rd.GetString(4);
contact.Geburtsdatum = rd.GetDateTime(5);
contact.Adresse = rd.GetString(6);
contact.Rechnungsadresse = rd.GetString(7);
contact.Lieferadresse = rd.GetString(8);
list.contact.Add(contact);
}
// DataReader schließen
rd.Close();
}
// Verbindung schließen
db.Close();
return list;
}
}
catch (Exception)
{
throw new Exception("Searching Contact failed");
}
}
public void handleRequest(Url url, NetworkStream clientStream)
{
stream = clientStream;
Url newUrl = new Url();
newUrl = (Url)url;
string plug = "Facade";
newUrl.setPluginName(plug);
string pluginName = newUrl.getPluginName();
splitUrl = newUrl.getSplitUrl();
if (pluginName == "Facade")
{
Console.WriteLine("{0}: handleRequest", pluginName);
//Contacts
#region NewContacts
if (splitUrl.ContainsValue("Contacts") && splitUrl.ContainsValue("New"))
{
splitUrl.TryGetValue("Title", out title);
splitUrl.TryGetValue("firstname", out firstname);
splitUrl.TryGetValue("lastname", out lastname);
splitUrl.TryGetValue("suffix", out suffix);
string birthday1;
splitUrl.TryGetValue("birthday", out birthday1);
string firmids;
splitUrl.TryGetValue("FirmName", out firmids);
int.TryParse(firmids, out firmId);
DateTime birthday = new DateTime();
birthday = DateTime.Parse(birthday1, System.Globalization.CultureInfo.InvariantCulture);
splitUrl.TryGetValue("address", out adress);
splitUrl.TryGetValue("deliveryaddress", out billingadress);
splitUrl.TryGetValue("billingaddress", out deliveryadress);
Contact instance = new Contact();
instance.FirmID = firmId;
instance.Titel = title;
instance.Vorname = firstname;
instance.Nachname = lastname;
instance.Suffix = suffix;
instance.Geburtsdatum = birthday;
instance.Adresse = adress;
instance.Rechnungsadresse = billingadress;
instance.Lieferadresse = deliveryadress;
ContactsList list = new ContactsList();
list.contact.Add(instance);
bl.NewContacts(list);
result2 = "Kunde erfolgreich hinzugefügt!";
sendResponse(result2);
}
#endregion
#region SearchContact
if (splitUrl.ContainsValue("Contacts") && splitUrl.ContainsValue("Search"))
{
splitUrl.TryGetValue("Name", out name);
var result = bl.searchContacts(name);
string msg = ToXmlString(result);
sendResponse(msg);
}
#endregion
#region SearchID
if (splitUrl.ContainsValue("Contacts") && splitUrl.ContainsValue("Id"))
{
int id;
string ids;
splitUrl.TryGetValue("Id", out ids);
int.TryParse(ids, out id);
var result = bl.searchID(id);
string msg = ToXmlString(result);
sendResponse(msg);
}
#endregion
#region UpdateContact
if (splitUrl.ContainsValue("Contacts") && splitUrl.ContainsValue("Update"))
{
string ids;
splitUrl.TryGetValue("Id", out ids);
int.TryParse(ids, out id);
splitUrl.TryGetValue("title", out title);
splitUrl.TryGetValue("firstname", out firstname);
splitUrl.TryGetValue("lastname", out lastname);
splitUrl.TryGetValue("suffix", out suffix);
string birthday1;
splitUrl.TryGetValue("birthday", out birthday1);
DateTime birthday = new DateTime();
birthday = DateTime.Parse(birthday1, System.Globalization.CultureInfo.InvariantCulture);
splitUrl.TryGetValue("adress", out adress);
splitUrl.TryGetValue("billingaddress", out billingadress);
splitUrl.TryGetValue("deliveryaddress", out deliveryadress);
Contact instance = new Contact();
instance.ID = id;
instance.Titel = title;
instance.Vorname = firstname;
instance.Nachname = lastname;
instance.Suffix = suffix;
instance.Geburtsdatum = birthday;
instance.Adresse = adress;
instance.Rechnungsadresse = billingadress;
instance.Lieferadresse = deliveryadress;
ContactsList list = new ContactsList();
list.contact.Add(instance);
if (list != null)
{
bl.UpdateContacts(list);
}
result2 = "Kunde erfolgreich upgedated!";
sendResponse(result2);
}
#endregion
//Firma
#region NewFirm
if (splitUrl.ContainsValue("Firm") && splitUrl.ContainsValue("New"))
{
splitUrl.TryGetValue("Name", out firmname);
splitUrl.TryGetValue("Uid", out UID);
splitUrl.TryGetValue("address", out adress);
splitUrl.TryGetValue("deliveryaddress", out deliveryadress);
splitUrl.TryGetValue("billingaddress", out billingadress);
Firma firm = new Firma();
firm.UID = UID;
firm.Name = firmname;
firm.Adresse = adress;
firm.Rechnungsadresse = billingadress;
firm.Lieferadresse = deliveryadress;
Firmlist list = new Firmlist();
list.firma.Add(firm);
bl.NewFirm(list);
result2 = "Firma erfolgreich hinzugefügt!";
sendResponse(result2);
}
#endregion
#region SearchFirm
if (splitUrl.ContainsValue("Firm") && splitUrl.ContainsValue("Search"))
{
splitUrl.TryGetValue("Name", out name);
var result = bl.searchFirm(name);
string msg = ToXmlString(result);
sendResponse(msg);
}
#endregion
#region Search Firm ID
if (splitUrl.ContainsValue("Firm") && splitUrl.ContainsValue("Id"))
{
int id;
string ids;
splitUrl.TryGetValue("Id", out ids);
int.TryParse(ids, out id);
var result = bl.searchFirmID(id);
string msg = ToXmlString(result);
sendResponse(msg);
}
#endregion
#region UpdateFirm
if (splitUrl.ContainsValue("Firm") && splitUrl.ContainsValue("Update"))
{
string ids;
splitUrl.TryGetValue("Id", out ids);
int.TryParse(ids, out id);
splitUrl.TryGetValue("name", out firmname);
splitUrl.TryGetValue("Uid", out UID);
splitUrl.TryGetValue("adress", out adress);
splitUrl.TryGetValue("billingadress", out billingadress);
splitUrl.TryGetValue("deliveryadress", out deliveryadress);
Firma instance = new Firma();
instance.ID = id;
instance.Name = firmname;
instance.UID = UID;
instance.Adresse = adress;
instance.Rechnungsadresse = billingadress;
instance.Lieferadresse = deliveryadress;
Firmlist list = new Firmlist();
list.firma.Add(instance);
bl.UpdateFirm(list);
result2 = "Firma erfolgreich upgedated!";
sendResponse(result2);
}
#endregion
//Invoice
#region NewInvoice
if (splitUrl.ContainsValue("Invoice") && splitUrl.ContainsValue("New"))
{
string numbers;
splitUrl.TryGetValue("Number", out numbers);
int.TryParse(numbers, out number);
string PayDate1;
splitUrl.TryGetValue("PayDate", out PayDate1);
DateTime paymentDate = new DateTime();
paymentDate = DateTime.Parse(PayDate1, System.Globalization.CultureInfo.InvariantCulture);
string ids;
splitUrl.TryGetValue("Id", out ids);
int.TryParse(ids, out idContact);
splitUrl.TryGetValue("Message", out message);
splitUrl.TryGetValue("Comment", out comment);
string A1;
splitUrl.TryGetValue("A1", out A1);
int.TryParse(A1, out amount1);
splitUrl.TryGetValue("Art1", out article1);
string P1;
splitUrl.TryGetValue("P1", out P1);
int.TryParse(P1, out stk1);
string U1;
splitUrl.TryGetValue("U1", out U1);
int.TryParse(U1, out Ust1);
string A2;
splitUrl.TryGetValue("A2", out A2);
int.TryParse(A2, out amount2);
splitUrl.TryGetValue("Art2", out article2);
string P2;
splitUrl.TryGetValue("P2", out P2);
int.TryParse(P2, out stk2);
string U2;
splitUrl.TryGetValue("U2", out U2);
int.TryParse(U2, out Ust2);
string A3;
splitUrl.TryGetValue("A3", out A3);
int.TryParse(A3, out amount3);
splitUrl.TryGetValue("Art3", out article3);
string P3;
splitUrl.TryGetValue("P3", out P3);
int.TryParse(P3, out stk3);
string U3;
splitUrl.TryGetValue("U3", out U3);
int.TryParse(U3, out Ust3);
Invoice invoice = new Invoice();
invoice.Nummer = number;
invoice.Faelligkeit = paymentDate;
invoice.IDKontakt = idContact;
invoice.Nachricht = message;
invoice.Kommentar = comment;
invoice.Menge1 = amount1;
invoice.Artikel1 = article1;
invoice.Stueckpreis1 = stk1;
invoice.Ust1 = Ust1;
invoice.Menge2 = amount2;
invoice.Artikel2 = article2;
invoice.Stueckpreis2 = stk2;
invoice.Ust2 = Ust2;
invoice.Menge3 = amount3;
invoice.Artikel3 = article3;
invoice.Stueckpreis3 = stk3;
invoice.Ust3 = Ust3;
InvoiceList list = new InvoiceList();
list.invoice.Add(invoice);
bl.NewInvoice(list);
result2 = "Rechnung erfolgreich hinzugefügt!";
sendResponse(result2);
}
#endregion
#region SearchInvoice
if (splitUrl.ContainsValue("Invoice") && splitUrl.ContainsValue("Search"))
{
string DateFrom1;
splitUrl.TryGetValue("DateFrom", out DateFrom1);
string DateTo1;
splitUrl.TryGetValue("DateTo", out DateTo1);
if (DateFrom1 != "" && DateTo1 != "")
{
DateFrom = DateTime.Parse(DateFrom1, System.Globalization.CultureInfo.InvariantCulture);
DateTo = DateTime.Parse(DateTo1, System.Globalization.CultureInfo.InvariantCulture);
var result = bl.searchInvoiceDateFromTo(DateFrom, DateTo);
string msg = ToXmlString(result);
Console.WriteLine(msg);
sendResponse(msg);
}
string AmountFrom1;
splitUrl.TryGetValue("AmountFrom", out AmountFrom1);
string AmountTo1;
splitUrl.TryGetValue("AmountTo", out AmountTo1);
if (AmountFrom1 != "" && AmountTo1 != "")
{
int.TryParse(AmountFrom1, out AmountFrom);
int.TryParse(AmountTo1, out AmountTo);
var result = bl.searchAmountFromTo(AmountFrom, AmountTo);
string msg = ToXmlString(result);
Console.WriteLine(msg);
sendResponse(msg);
}
splitUrl.TryGetValue("SearchContact", out searchContact);
if (searchContact != "")
{
var result = bl.searchInvoiceByName(searchContact);
string msg = ToXmlString(result);
Console.WriteLine(msg);
sendResponse(msg);
}
}
#endregion
#region Search Invoice ID
if (splitUrl.ContainsValue("Invoice") && splitUrl.ContainsValue("Id"))
{
int id;
string ids;
splitUrl.TryGetValue("Id", out ids);
int.TryParse(ids, out id);
var result = bl.searchIDInvoice(id);
string msg = ToXmlString(result);
sendResponse(msg);
}
#endregion
#region UpdateInvoice
if (splitUrl.ContainsValue("Invoice") && splitUrl.ContainsValue("Update"))
{
string ids;
splitUrl.TryGetValue("Id", out ids);
int.TryParse(ids, out id);
splitUrl.TryGetValue("Note", out message);
splitUrl.TryGetValue("Comment", out comment);
#region Artikel
string A1;
splitUrl.TryGetValue("A1", out A1);
int.TryParse(A1, out amount1);
splitUrl.TryGetValue("Art1", out article1);
string P1;
splitUrl.TryGetValue("P1", out P1);
int.TryParse(P1, out stk1);
string U1;
splitUrl.TryGetValue("U1", out U1);
int.TryParse(U1, out Ust1);
string A2;
splitUrl.TryGetValue("A2", out A2);
int.TryParse(A2, out amount2);
splitUrl.TryGetValue("Art2", out article2);
string P2;
splitUrl.TryGetValue("P2", out P2);
int.TryParse(P2, out stk2);
string U2;
splitUrl.TryGetValue("U2", out U2);
int.TryParse(U2, out Ust2);
string A3;
splitUrl.TryGetValue("A3", out A3);
int.TryParse(A3, out amount3);
splitUrl.TryGetValue("Art3", out article3);
string P3;
splitUrl.TryGetValue("P3", out P3);
int.TryParse(P3, out stk3);
string U3;
splitUrl.TryGetValue("U3", out U3);
int.TryParse(U3, out Ust3);
#endregion
Invoice invoice = new Invoice();
invoice.ID = id;
invoice.Nachricht = message;
invoice.Kommentar = comment;
invoice.Menge1 = amount1;
invoice.Artikel1 = article1;
invoice.Stueckpreis1 = stk1;
invoice.Ust1 = Ust1;
invoice.Menge2 = amount2;
invoice.Artikel2 = article2;
invoice.Stueckpreis2 = stk2;
invoice.Ust2 = Ust2;
invoice.Menge3 = amount3;
invoice.Artikel3 = article3;
invoice.Stueckpreis3 = stk3;
invoice.Ust3 = Ust3;
InvoiceList list = new InvoiceList();
list.invoice.Add(invoice);
bl.UpdateInvoice(list);
result2 = "Invoice erfolgreich upgedated!";
sendResponse(result2);
}
#endregion
}
}
public void UpdateContacts(Contact list)
{
int id = list.ID;
string title = list.Titel;
string firstname = list.Vorname;
string lastname = list.Nachname;
string suffix = list.Suffix;
DateTime birthday = list.Geburtsdatum;
string adress = list.Adresse;
string billingadress = list.Rechnungsadresse;
string deliveryadress = list.Lieferadresse;
try
{
//List<Contact> list = new List<Contact>();
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
//SQL Statement zum auslesen
string query = "UPDATE Person SET Titel = @title, Vorname = @firstname, Nachname = @lastname, Suffix = @suffix, Geburtsdatum = @birthday WHERE ID_Person = @id";
//string query = "UPDATE Person SET Titel = @title, Vorname = @firstname, Nachname = @lastname, Suffix = @suffix WHERE ID_Person = @id";
//string query2 = "UPDATE Kontakte SET Adresse = @adress, Rechnungsadresse = @billingadress, Lieferadresse = @deliveryadress WHERE ID_Kontakte = (SELECT FK_Kontakte FROM Person WHERE ID_Person = @id)";
SqlCommand cmdUpdate1 = new SqlCommand(query, db);
//SqlCommand cmdUpdate2 = new SqlCommand(query2, db);
cmdUpdate1.Parameters.AddWithValue("@id", id);
cmdUpdate1.Parameters.AddWithValue("@title", title);
cmdUpdate1.Parameters.AddWithValue("@firstname", firstname);
cmdUpdate1.Parameters.AddWithValue("@lastname", lastname);
cmdUpdate1.Parameters.AddWithValue("@suffix", suffix);
cmdUpdate1.Parameters.AddWithValue("@birthday", birthday);
//cmdUpdate2.Parameters.AddWithValue("@adress", adress);
//cmdUpdate2.Parameters.AddWithValue("@billingadress", billingadress);
//cmdUpdate2.Parameters.AddWithValue("@deliveryadress", deliveryadress);
db.Close();
}
}
catch (Exception)
{
throw new Exception("Updating Contact failed.");
}
}
public List<Contact> searchContacts(string text)
{
//checkFirma(text);
try
{
List<Contact> list = new List<Contact>();
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
//SQL Statement zum auslesen
string query = "SELECT ID_Person, Titel, Vorname, Nachname, Suffix, Geburtsdatum, Adresse, Rechnungsadresse, Lieferadresse FROM Kontakte inner join Person on ID_Kontakte = FK_Kontakte WHERE [Vorname] = @text or [Nachname] = @text";
SqlCommand cmdSelect = new SqlCommand(query, db);
cmdSelect.Parameters.AddWithValue("@text", text);
using (SqlDataReader rd = cmdSelect.ExecuteReader())
{
// Daten holen
while (rd.Read())
{
Console.WriteLine("ID:{0} & Vorname:{1}", rd["ID_Person"], rd["Vorname"]);
Contact instance = new Contact();
instance.ID = rd.GetInt32(0);
instance.Titel = rd.GetString(1);
instance.Vorname = rd.GetString(2);
instance.Nachname = rd.GetString(3);
//var value = rd["Suffix"];
//if (value != "")
//{
instance.Suffix = rd.GetString(4);
//}
//else
//{
// Console.WriteLine("null");
//}
instance.Geburtsdatum = rd.GetDateTime(5);
instance.Adresse = rd.GetString(6);
instance.Rechnungsadresse = rd.GetString(7);
instance.Lieferadresse = rd.GetString(8);
list.Add(instance);
//list db null
}
// DataReader schließen
rd.Close();
}
// Verbindung schließen
db.Close();
return list;
}
}
catch (Exception)
{
throw new Exception("Connection to Database failed");
}
}
