Esempio n. 1
0
        //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.");
            }
        }
Esempio n. 2
0
        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");
            }
        }
Esempio n. 3
0
        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.");
            }
        }
Esempio n. 4
0
        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
            }
        }
Esempio n. 5
0
        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");
            }
        }
Esempio n. 6
0
        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");
            }
        }
Esempio n. 7
0
        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.");
            }
        }
Esempio n. 8
0
        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.");
            }
        }
Esempio n. 9
0
 public void UpdateInvoice(InvoiceList list)
 {
     dal.UpdateInvoice(list);
 }
Esempio n. 10
0
 public void NewInvoice(InvoiceList list)
 {
     dal.NewInvoice(list);
 }
Esempio n. 11
0
        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");
            }
        }
Esempio n. 12
0
        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

            }
        }
Esempio n. 13
0
        //Invoice

        #region NewInvoice
        public void NewInvoice(InvoiceList list)
        {
            dal.NewInvoice(list);
        }
Esempio n. 14
0
 public void UpdateInvoice(InvoiceList list)
 {
     dal.UpdateInvoice(list);
 }