//Invoice
#region NewInvoice
public void NewInvoice(InvoiceList list)
{
foreach (var obj in list.invoice)
{
//Rechnungen
paymentDate = obj.Faelligkeit;
number = obj.Nummer;
comment = obj.Kommentar;
message = obj.Nachricht;
idContact = obj.IDKontakt;
billingadress = obj.Rechnungsadresse; //Kontakte
//Rechnungszeile
article1 = obj.Artikel1;
amount1 = obj.Menge1;
Ust1 = obj.Ust1;
stk1 = obj.Stueckpreis1;
article2 = obj.Artikel2;
amount2 = obj.Menge2;
Ust2 = obj.Ust2;
stk2 = obj.Stueckpreis2;
article3 = obj.Artikel3;
amount3 = obj.Menge3;
Ust3 = obj.Ust3;
stk3 = obj.Stueckpreis3;
}
try
{
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
string query = "INSERT INTO Rechnungen(FK_Person, Datum, Faelligkeit, Rechnungsnummer, Kommentar, Nachricht) VALUES (@idContact, GETDATE(), @paymentDate, @number, @comment, @message)";
SqlCommand cmdInsert1 = new SqlCommand(query, db);
cmdInsert1.Parameters.AddWithValue("@idContact", idContact);
cmdInsert1.Parameters.AddWithValue("@paymentDate", paymentDate);
cmdInsert1.Parameters.AddWithValue("@number", number);
cmdInsert1.Parameters.AddWithValue("@comment", comment);
cmdInsert1.Parameters.AddWithValue("@message", message);
cmdInsert1.ExecuteNonQuery();
db.Close();
SaveID(number);
SaveInvoiceRow(idRechnung, article1, amount1, Ust1, stk1, article2, amount2, Ust2, stk2, article3, amount3, Ust3, stk3);
db.Close();
}
}
catch (Exception)
{
throw new Exception("Inserting Invoice failed.");
}
}
public InvoiceList searchAmountFromTo(int AmountFrom, int AmountTo)
{
try
{
InvoiceList list = new InvoiceList();
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
string query = "SELECT ID_Rechnungen, Datum, Faelligkeit, Rechnungsnummer, Rechnungsadresse, Vorname, Nachname, Kommentar, Nachricht, Artikelname1, Menge1, Ust1, Preis1, Artikelname2, Menge2, Ust2, Preis2, Artikelname3, Menge3, Ust3, Preis3 FROM Rechnungen inner join Person on FK_Person = ID_Person inner join Kontakte on FK_Kontakte = ID_Kontakte inner join Rechnungszeile on FK_Rechnungen = ID_Rechnungen WHERE [Preis1] >= @AmountFrom and [Preis1] <= @AmountTo and [Preis2] >= @AmountFrom and [Preis2] <= @AmountTo and [Preis3] >= @AmountFrom and [Preis3] <= @AmountTo";
SqlCommand cmdSelect = new SqlCommand(query, db);
cmdSelect.Parameters.AddWithValue("@AmountFrom", AmountFrom);
cmdSelect.Parameters.AddWithValue("@AmountTo", AmountTo);
using (SqlDataReader rd = cmdSelect.ExecuteReader())
{
while (rd.Read())
{
Invoice invoice = new Invoice();
invoice.ID = rd.GetInt32(0);
invoice.Datum = rd.GetDateTime(1);
invoice.Faelligkeit = rd.GetDateTime(2);
invoice.Nummer = rd.GetInt32(3);
invoice.Rechnungsadresse = rd.GetString(4);
invoice.Vorname = rd.GetString(5);
invoice.Nachname = rd.GetString(6);
invoice.Kommentar = rd.GetString(7);
invoice.Nachricht = rd.GetString(8);
invoice.Artikel1 = rd.GetString(9);
invoice.Menge1 = rd.GetInt32(10);
invoice.Ust1 = rd.GetInt32(11);
invoice.Stueckpreis1 = rd.GetInt32(12);
invoice.Artikel2 = rd.GetString(13);
invoice.Menge2 = rd.GetInt32(14);
invoice.Ust2 = rd.GetInt32(15);
invoice.Stueckpreis2 = rd.GetInt32(16);
invoice.Artikel3 = "";
list.invoice.Add(invoice);
}
rd.Close();
}
db.Close();
return(list);
}
}
catch (Exception)
{
throw new Exception("Search Invoice by Amount failed");
}
}
public void UpdateInvoice(InvoiceList list)
{
foreach (var obj in list.invoice)
{
//Invoice
id = obj.ID;
comment = obj.Kommentar;
message = obj.Nachricht;
//InvoiceLine
article1 = obj.Artikel1;
amount1 = obj.Menge1;
Ust1 = obj.Ust1;
stk1 = obj.Stueckpreis1;
article2 = obj.Artikel2;
amount2 = obj.Menge2;
Ust2 = obj.Ust2;
stk2 = obj.Stueckpreis2;
article3 = obj.Artikel3;
amount3 = obj.Menge3;
Ust3 = obj.Ust3;
stk3 = obj.Stueckpreis3;
}
try
{
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
string query = "UPDATE Rechnungen SET Kommentar = @comment, Nachricht = @message WHERE [ID_Rechnungen] = @id";
SqlCommand cmdUpdate1 = new SqlCommand(query, db);
cmdUpdate1.Parameters.AddWithValue("@id", id);
cmdUpdate1.Parameters.AddWithValue("@number", number);
cmdUpdate1.Parameters.AddWithValue("@comment", comment);
cmdUpdate1.Parameters.AddWithValue("@message", message);
cmdUpdate1.ExecuteNonQuery();
db.Close();
Rechnungszeile(id, article1, amount1, Ust1, stk1, article2, amount2, Ust2, stk2, article3, amount3, Ust3, stk3);
}
}
catch (Exception)
{
throw new Exception("Updating Invoice 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 InvoiceList searchInvoiceDateFromTo(DateTime DateFrom, DateTime DateTo)
{
try
{
InvoiceList list = new InvoiceList();
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
string query = "SELECT ID_Rechnungen, Datum, Faelligkeit, Rechnungsnummer, Rechnungsadresse, Vorname, Nachname, Kommentar, Nachricht FROM Rechnungen inner join Person on FK_Person = ID_Person inner join Kontakte on FK_Kontakte = ID_Kontakte WHERE [Datum] >= @DateFrom and [Datum] <= @DateTo";
SqlCommand cmdSelect = new SqlCommand(query, db);
cmdSelect.Parameters.AddWithValue("@DateFrom", DateFrom);
cmdSelect.Parameters.AddWithValue("@DateTo", DateTo);
using (SqlDataReader rd = cmdSelect.ExecuteReader())
{
while (rd.Read())
{
ID = rd.GetInt32(0);
Datum = rd.GetDateTime(1);
paymentDate = rd.GetDateTime(2);
number = rd.GetInt32(3);
billingadress = rd.GetString(4);
firstname = rd.GetString(5);
lastname = rd.GetString(6);
comment = rd.GetString(7);
message = rd.GetString(8);
}
rd.Close();
}
string query2 = "SELECT Artikelname1, Menge1, Ust1, Preis1, Artikelname2, Menge2, Ust2, Preis2, Artikelname3, Menge3, Ust3, Preis3 FROM Rechnungszeile WHERE [FK_Rechnungen] = @id";
SqlCommand cmdSelect1 = new SqlCommand(query2, db);
cmdSelect1.Parameters.AddWithValue("@id", ID);
using (SqlDataReader rd = cmdSelect1.ExecuteReader())
{
while (rd.Read())
{
Invoice invoice = new Invoice();
invoice.ID = ID;
invoice.Datum = Datum;
invoice.Faelligkeit = paymentDate;
invoice.Nummer = number;
invoice.Rechnungsadresse = billingadress;
invoice.Vorname = firstname;
invoice.Nachname = lastname;
invoice.Kommentar = comment;
invoice.Nachricht = message;
invoice.Artikel1 = rd.GetString(0);
invoice.Menge1 = rd.GetInt32(1);
invoice.Ust1 = rd.GetInt32(2);
invoice.Stueckpreis1 = rd.GetInt32(3);
invoice.Artikel2 = rd.GetString(4);
invoice.Menge2 = rd.GetInt32(5);
invoice.Ust2 = rd.GetInt32(6);
invoice.Stueckpreis2 = rd.GetInt32(6);
invoice.Artikel3 = "";
//if (rd.IsDBNull(7)) invoice.Artikel3 = "";
//invoice.Artikel3 = rd.GetString(7);
//invoice.Menge3 = rd.GetInt32(8);
//invoice.Ust3 = rd.GetInt32(9);
//invoice.Stueckpreis3 = rd.GetInt32(10);
list.invoice.Add(invoice);
}
rd.Close();
}
db.Close();
return list;
}
}
catch (Exception)
{
throw new Exception("Search Invoice by Date failed");
}
}
public InvoiceList searchAmountFromTo(int AmountFrom, int AmountTo)
{
try
{
InvoiceList list = new InvoiceList();
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
string query = "SELECT ID_Rechnungen, Datum, Faelligkeit, Rechnungsnummer, Rechnungsadresse, Vorname, Nachname, Kommentar, Nachricht, Artikelname1, Menge1, Ust1, Preis1, Artikelname2, Menge2, Ust2, Preis2, Artikelname3, Menge3, Ust3, Preis3 FROM Rechnungen inner join Person on FK_Person = ID_Person inner join Kontakte on FK_Kontakte = ID_Kontakte inner join Rechnungszeile on FK_Rechnungen = ID_Rechnungen WHERE [Preis1] >= @AmountFrom and [Preis1] <= @AmountTo and [Preis2] >= @AmountFrom and [Preis2] <= @AmountTo and [Preis3] >= @AmountFrom and [Preis3] <= @AmountTo";
SqlCommand cmdSelect = new SqlCommand(query, db);
cmdSelect.Parameters.AddWithValue("@AmountFrom", AmountFrom);
cmdSelect.Parameters.AddWithValue("@AmountTo", AmountTo);
using (SqlDataReader rd = cmdSelect.ExecuteReader())
{
while (rd.Read())
{
Invoice invoice = new Invoice();
invoice.ID = rd.GetInt32(0);
invoice.Datum = rd.GetDateTime(1);
invoice.Faelligkeit = rd.GetDateTime(2);
invoice.Nummer = rd.GetInt32(3);
invoice.Rechnungsadresse = rd.GetString(4);
invoice.Vorname = rd.GetString(5);
invoice.Nachname = rd.GetString(6);
invoice.Kommentar = rd.GetString(7);
invoice.Nachricht = rd.GetString(8);
invoice.Artikel1 = rd.GetString(9);
invoice.Menge1 = rd.GetInt32(10);
invoice.Ust1 = rd.GetInt32(11);
invoice.Stueckpreis1 = rd.GetInt32(12);
invoice.Artikel2 = rd.GetString(13);
invoice.Menge2 = rd.GetInt32(14);
invoice.Ust2 = rd.GetInt32(15);
invoice.Stueckpreis2 = rd.GetInt32(16);
invoice.Artikel3 = "";
list.invoice.Add(invoice);
}
rd.Close();
}
db.Close();
return list;
}
}
catch (Exception)
{
throw new Exception("Search Invoice by Amount failed");
}
}
public void NewInvoice(InvoiceList list)
{
foreach (var obj in list.invoice)
{
//Rechnungen
paymentDate = obj.Faelligkeit;
number = obj.Nummer;
comment = obj.Kommentar;
message = obj.Nachricht;
idContact = obj.IDKontakt;
billingadress = obj.Rechnungsadresse; //Kontakte
//Rechnungszeile
article1 = obj.Artikel1;
amount1 = obj.Menge1;
Ust1 = obj.Ust1;
stk1 = obj.Stueckpreis1;
article2 = obj.Artikel2;
amount2 = obj.Menge2;
Ust2 = obj.Ust2;
stk2 = obj.Stueckpreis2;
article3 = obj.Artikel3;
amount3 = obj.Menge3;
Ust3 = obj.Ust3;
stk3 = obj.Stueckpreis3;
}
try
{
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
string query = "INSERT INTO Rechnungen(FK_Person, Datum, Faelligkeit, Rechnungsnummer, Kommentar, Nachricht) VALUES (@idContact, GETDATE(), @paymentDate, @number, @comment, @message)";
SqlCommand cmdInsert1 = new SqlCommand(query, db);
cmdInsert1.Parameters.AddWithValue("@idContact", idContact);
cmdInsert1.Parameters.AddWithValue("@paymentDate", paymentDate);
cmdInsert1.Parameters.AddWithValue("@number", number);
cmdInsert1.Parameters.AddWithValue("@comment", comment);
cmdInsert1.Parameters.AddWithValue("@message", message);
cmdInsert1.ExecuteNonQuery();
db.Close();
SaveID(number);
SaveInvoiceRow(idRechnung, article1, amount1, Ust1, stk1, article2, amount2, Ust2, stk2, article3, amount3, Ust3, stk3);
db.Close();
}
}
catch (Exception)
{
throw new Exception("Inserting Invoice failed.");
}
}
public void UpdateInvoice(InvoiceList list)
{
foreach (var obj in list.invoice)
{
//Invoice
id = obj.ID;
comment = obj.Kommentar;
message = obj.Nachricht;
//InvoiceLine
article1 = obj.Artikel1;
amount1 = obj.Menge1;
Ust1 = obj.Ust1;
stk1 = obj.Stueckpreis1;
article2 = obj.Artikel2;
amount2 = obj.Menge2;
Ust2 = obj.Ust2;
stk2 = obj.Stueckpreis2;
article3 = obj.Artikel3;
amount3 = obj.Menge3;
Ust3 = obj.Ust3;
stk3 = obj.Stueckpreis3;
}
try
{
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
string query = "UPDATE Rechnungen SET Kommentar = @comment, Nachricht = @message WHERE [ID_Rechnungen] = @id";
SqlCommand cmdUpdate1 = new SqlCommand(query, db);
cmdUpdate1.Parameters.AddWithValue("@id", id);
cmdUpdate1.Parameters.AddWithValue("@number", number);
cmdUpdate1.Parameters.AddWithValue("@comment", comment);
cmdUpdate1.Parameters.AddWithValue("@message", message);
cmdUpdate1.ExecuteNonQuery();
db.Close();
Rechnungszeile(id, article1, amount1, Ust1, stk1, article2, amount2, Ust2, stk2, article3, amount3, Ust3, stk3);
}
}
catch (Exception)
{
throw new Exception("Updating Invoice failed.");
}
}
public void UpdateInvoice(InvoiceList list)
{
dal.UpdateInvoice(list);
}
public void NewInvoice(InvoiceList list)
{
dal.NewInvoice(list);
}
public InvoiceList searchInvoiceDateFromTo(DateTime DateFrom, DateTime DateTo)
{
try
{
InvoiceList list = new InvoiceList();
using (SqlConnection db = new SqlConnection(strCon))
{
db.Open();
string query = "SELECT ID_Rechnungen, Datum, Faelligkeit, Rechnungsnummer, Rechnungsadresse, Vorname, Nachname, Kommentar, Nachricht FROM Rechnungen inner join Person on FK_Person = ID_Person inner join Kontakte on FK_Kontakte = ID_Kontakte WHERE [Datum] >= @DateFrom and [Datum] <= @DateTo";
SqlCommand cmdSelect = new SqlCommand(query, db);
cmdSelect.Parameters.AddWithValue("@DateFrom", DateFrom);
cmdSelect.Parameters.AddWithValue("@DateTo", DateTo);
using (SqlDataReader rd = cmdSelect.ExecuteReader())
{
while (rd.Read())
{
ID = rd.GetInt32(0);
Datum = rd.GetDateTime(1);
paymentDate = rd.GetDateTime(2);
number = rd.GetInt32(3);
billingadress = rd.GetString(4);
firstname = rd.GetString(5);
lastname = rd.GetString(6);
comment = rd.GetString(7);
message = rd.GetString(8);
}
rd.Close();
}
string query2 = "SELECT Artikelname1, Menge1, Ust1, Preis1, Artikelname2, Menge2, Ust2, Preis2, Artikelname3, Menge3, Ust3, Preis3 FROM Rechnungszeile WHERE [FK_Rechnungen] = @id";
SqlCommand cmdSelect1 = new SqlCommand(query2, db);
cmdSelect1.Parameters.AddWithValue("@id", ID);
using (SqlDataReader rd = cmdSelect1.ExecuteReader())
{
while (rd.Read())
{
Invoice invoice = new Invoice();
invoice.ID = ID;
invoice.Datum = Datum;
invoice.Faelligkeit = paymentDate;
invoice.Nummer = number;
invoice.Rechnungsadresse = billingadress;
invoice.Vorname = firstname;
invoice.Nachname = lastname;
invoice.Kommentar = comment;
invoice.Nachricht = message;
invoice.Artikel1 = rd.GetString(0);
invoice.Menge1 = rd.GetInt32(1);
invoice.Ust1 = rd.GetInt32(2);
invoice.Stueckpreis1 = rd.GetInt32(3);
invoice.Artikel2 = rd.GetString(4);
invoice.Menge2 = rd.GetInt32(5);
invoice.Ust2 = rd.GetInt32(6);
invoice.Stueckpreis2 = rd.GetInt32(6);
invoice.Artikel3 = "";
//if (rd.IsDBNull(7)) invoice.Artikel3 = "";
//invoice.Artikel3 = rd.GetString(7);
//invoice.Menge3 = rd.GetInt32(8);
//invoice.Ust3 = rd.GetInt32(9);
//invoice.Stueckpreis3 = rd.GetInt32(10);
list.invoice.Add(invoice);
}
rd.Close();
}
db.Close();
return(list);
}
}
catch (Exception)
{
throw new Exception("Search Invoice by Date 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
}
}
//Invoice
#region NewInvoice
public void NewInvoice(InvoiceList list)
{
dal.NewInvoice(list);
}
public void UpdateInvoice(InvoiceList list)
{
dal.UpdateInvoice(list);
}
