/// <summary> /// Creates a new Angebot with the provided parameters and saves it in the SQLite-DB /// </summary> /// <param name="angebot">The business object with all needed data</param> public void CreateAngebot(AngebotTable angebot) { string sql = "INSERT INTO Angebot (kundenID, Angebotssumme, Angebotsdauer, Erstellungsdatum, Umsetzung, Beschreibung) VALUES (?, ?, ?, ?, ?, ?)"; try { this.SendStatementToDatabase(sql, -1, angebot.KundenID, angebot.Angebotssumme, angebot.Angebotsdauer, angebot.Erstellungsdatum, angebot.Umsetzungschance, angebot.Beschreibung); } catch (SQLiteException) { this.logger.Log(Logger.Level.Error, "An SQLite exception occured while saving a new Angebot within the SQLite database."); throw; } this.logger.Log(Logger.Level.Info, "New Angebot has been stored within the SQLite database."); }
/// <summary> /// Gets values from the GUI, validates them and then sends them to the database to create a new Angebot /// </summary> public void Create(AngebotTable angebot, Label errorlabel) { IRule doubv = new PositiveDoubleValidator(); IRule intv = new PositiveIntValidator(); IRule datev = new DateValidator(); IRule lengthv = new StringLength150Validator(); IRule percv = new PercentValidator(); IRule lnhsv = new LettersNumbersHyphenSpaceValidator(); IRule lhv = new LettersHyphenValidator(); DataBindingFramework.BindFromDouble(angebot.Angebotssumme.ToString(), "Angebotssumme", errorlabel, false, doubv); DataBindingFramework.BindFromInt(angebot.Umsetzungschance.ToString(), "Umsetzungschance", errorlabel, false, percv); DataBindingFramework.BindFromString(angebot.Angebotsdauer, "GültigBis", errorlabel, false, datev); DataBindingFramework.BindFromString(angebot.Beschreibung, "Beschreibung", errorlabel, false, lnhsv, lengthv); DataBindingFramework.BindFromInt(angebot.KundenID.ToString(), "kundenID", errorlabel, false, intv); DataBindingFramework.BindFromString(angebot.Erstellungsdatum, "Erstellungsdatum", errorlabel, false, datev); // change date formats angebot.Angebotsdauer = GlobalActions.ParseToSQLiteDateString(angebot.Angebotsdauer); if (errorlabel.Visible) { throw new InvalidInputException(); } this.logger.Log(Logger.Level.Info, "A new Angebot will be created"); // create Angebot try { DALFactory.GetDAL().CreateAngebot(angebot); } catch (SQLiteException) { throw; } }
/// <summary> /// Reads values of concerning fields and asks business layer to create a new Angebot with the provided parameters /// </summary> /// <param name="sender">The sender</param> /// <param name="e">The event params</param> private void CreateNewAngebot(object sender, EventArgs e) { // reset error/success labels this.createAngebotMsgLabel.Hide(); this.createAngebotMsgLabel.Text = string.Empty; // existing or newly to-be-created bool createKunde = false; string kundenID = null; // Table for the Kontakt KundeKontaktTable k = new KundeKontaktTable(); // new Kunde if (this.angebotErstellenSubTab.SelectedTab == this.angebotErstellenSubTab.TabPages[0]) { createKunde = true; } // existing Kunde else { // no Kunde chosen or first element chosen (which is empty) -> show error label if (this.createAngebotExistingKundeComboBox.SelectedIndex <= 0) { this.createAngebotMsgLabel.Text = "Error: kein Kunde ausgewählt"; this.createAngebotMsgLabel.ForeColor = Color.Red; this.createAngebotMsgLabel.Show(); return; // skip rest of function } // Kunde chosen - get ID out of ComboBox else { kundenID = this.createAngebotExistingKundeComboBox.SelectedItem.ToString(); kundenID = kundenID.Substring(0, kundenID.IndexOf(':')); IRule posint = new PositiveIntValidator(); k.ID = DataBindingFramework.BindFromInt(kundenID, "KundenID", this.createAngebotMsgLabel, false, posint); // Check for errors while databinding if (posint.HasErrors) { this.logger.Log(Logger.Level.Error, k.ID + " is an invalid Kunden ID"); return; } // get Kunde table out of Database KundenKontakteLoader loader = new KundenKontakteLoader(); List<KundeKontaktTable> results = loader.LoadKundenKontakte(k.ID, false); // there must be exactly one result, for ID is unique! if (results.Count != 1) { this.logger.Log(Logger.Level.Error, "More than one Kunde returned - impossible, because ID is unique!"); this.createAngebotMsgLabel.Text = "Error: Datenbank inkonsistent!"; this.createAngebotMsgLabel.Visible = true; return; // skip rest of function } // everything went fine k = results[0]; } } // Create new Kunde, if requested if (createKunde) { // define Rules IRule lhv = new LettersHyphenValidator(); IRule lnhsv = new LettersNumbersHyphenSpaceValidator(); IRule slv = new StringLength150Validator(); // validate data k.Vorname = DataBindingFramework.BindFromString(this.createAngebotNewKundeVnTextBox.Text, "Vorname", this.createAngebotMsgLabel, true, lhv, slv); k.NachnameFirmenname = DataBindingFramework.BindFromString(this.createAngebotNewKundeNnTextBox.Text, "Nachname", this.createAngebotMsgLabel, false, lnhsv, slv); k.Type = false; // Kunde if (lhv.HasErrors || lnhsv.HasErrors || slv.HasErrors) { this.logger.Log(Logger.Level.Error, "Invalid signs within create Angebot / create Kunde"); return; } KundenKontakteSaver saver = new KundenKontakteSaver(); try { k.ID = saver.SaveNewKundeKontakt(k, this.createAngebotMsgLabel); } catch (InvalidInputException ex) { this.logger.Log(Logger.Level.Error, ex.Message + ex.StackTrace); this.createAngebotMsgLabel.Text = "Error: Kundenfelder ungültig!"; this.createAngebotMsgLabel.Visible = true; return; } catch (SQLiteException ex) { this.logger.Log(Logger.Level.Error, ex.Message + ex.StackTrace); this.createAngebotMsgLabel.Text = "Error: Datenbankproblem!"; this.createAngebotMsgLabel.Visible = true; return; } this.createAngebotMsgLabel.Text = "Kunde wurde gespeichert."; } // Create Angebot business object this.logger.Log(Logger.Level.Info, "Start creating new Angebot..."); // define Rules IRule pdv2 = new PositiveDoubleValidator(); IRule pcv2 = new PercentValidator(); IRule dv2 = new DateValidator(); IRule lnhsv2 = new LettersNumbersHyphenSpaceValidator(); IRule slv2 = new StringLength150Validator(); IRule piv2 = new PositiveIntValidator(); AngebotTable angebot = new AngebotTable(); angebot.Angebotssumme = DataBindingFramework.BindFromDouble(this.createAngebotAngebotssummeTextBox.Text, "Angebotssumme", this.createAngebotMsgLabel, false, pdv2); angebot.Umsetzungschance = DataBindingFramework.BindFromInt(this.createAngebotUmsetzungswahrscheinlichkeitTextBox.Text, "Umsetzungschance", this.createAngebotMsgLabel, false, pcv2); angebot.Angebotsdauer = DataBindingFramework.BindFromString(this.angebotValidUntilDateTimePicker.Value.ToShortDateString(), "GültigBis", this.createAngebotMsgLabel, false, dv2); angebot.Beschreibung = DataBindingFramework.BindFromString(this.createAngebotDescriptionTextBox.Text, "Beschreibung", this.createAngebotMsgLabel, false, lnhsv2, slv2); angebot.KundenID = DataBindingFramework.BindFromInt(k.ID.ToString(), "kundenID", this.createAngebotMsgLabel, false, piv2); angebot.Erstellungsdatum = DateTime.Now.ToShortDateString(); // in case of errors in Databinding if (createAngebotMsgLabel.Visible) { this.logger.Log(Logger.Level.Error, "No angebot has been saved because of invalid inputs."); //createAngebotMsgLabel.ForeColor = Color.Red; //createAngebotMsgLabel.Show(); return; } // send Angebot object to database try { AngebotManager manager = new AngebotManager(); manager.Create(angebot, createAngebotMsgLabel); } catch (InvalidInputException ex) { this.logger.Log(Logger.Level.Error, ex.Message + ex.StackTrace); this.createAngebotMsgLabel.Text += "\nError: " + ex.Message; this.createAngebotMsgLabel.Show(); } catch (SQLiteException ex) { this.logger.Log(Logger.Level.Error, ex.Message + ex.StackTrace); this.createAngebotMsgLabel.Text += "\nError: " + ex.Message; this.createAngebotMsgLabel.Show(); } // show success message, if no error has been thrown if (!createAngebotMsgLabel.Visible) { this.createAngebotMsgLabel.Text += "\nAngebot wurde gespeichert."; this.createAngebotMsgLabel.ForeColor = Color.Green; this.createAngebotMsgLabel.Show(); } }
/// <summary> /// Receive params and load fitting existing Angebote from the SQLite database /// </summary> /// <param name="kid">The ID of the Kunde</param> /// <param name="from">A date string which indicates the search-begin date</param> /// <param name="until">A date string which indicates the search-end date</param> /// <returns>A resultlist of all fitting Angebote</returns> public List<AngebotTable> LoadAngebote(int kid, string from, string until, bool loadwithaid) { string sql = "SELECT * FROM Angebot WHERE "; if (loadwithaid) { sql += kid >= 0 ? "ID = ? AND angebotsdauer BETWEEN ? AND ?;" : "angebotsdauer BETWEEN ? AND ?;"; } else { sql += kid >= 0 ? "kundenID = ? AND angebotsdauer BETWEEN ? AND ?;" : "angebotsdauer BETWEEN ? AND ?;"; } List<AngebotTable> results = new List<AngebotTable>(); // open connection and get requested Kontakt(e) out of database SQLiteConnection con = null; SQLiteTransaction tra = null; SQLiteCommand cmd = null; SQLiteDataReader reader = null; try { // initialise connection con = new SQLiteConnection(ConfigFileManager.ConnectionString); con.Open(); // initialise transaction tra = con.BeginTransaction(); cmd = new SQLiteCommand(sql, con); // if it shall be searched for ID, really only search for ID and ignore other fields if (kid != -1) { SQLiteParameter p_ID = new SQLiteParameter(); p_ID.Value = kid; cmd.Parameters.Add(p_ID); } // bind from SQLiteParameter p_from = new SQLiteParameter(); p_from.Value = from; cmd.Parameters.Add(p_from); // bind to SQLiteParameter p_until = new SQLiteParameter(); p_until.Value = until; cmd.Parameters.Add(p_until); // execute and get results reader = cmd.ExecuteReader(); while (reader.Read()) { AngebotTable result = new AngebotTable(); result.ID = reader.GetInt32(0); result.KundenID = reader.GetInt32(1); result.Angebotssumme = reader.GetFloat(2); result.Angebotsdauer = reader.GetString(3); result.Erstellungsdatum = reader.GetString(4); result.Umsetzungschance = reader.GetInt32(5); result.Beschreibung = reader.GetString(6); results.Add(result); } return results; } catch (SQLiteException) { throw; } finally { if (reader != null) { reader.Dispose(); } if (tra != null) { tra.Dispose(); } if (cmd != null) { cmd.Dispose(); } if (con != null) { con.Dispose(); } } }
/// <summary> /// Gets an Angebot table with the needed parameters and saves it in the mockDB /// </summary> /// <param name="angebot">The business object</param> public void CreateAngebot(AngebotTable angebot) { MockDataBaseManager.savedAngebote.Add(angebot); this.logger.Log(Logger.Level.Info, "Angebot saved to the mockDB"); }