private void btnCottageDelete_Click(object sender, EventArgs e) { try { DialogResult result = MessageBox.Show("Haluatko varmasti poistaa valitun mökin tiedot?", "Poista mökin tiedot", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (result == DialogResult.Yes) { string query = "START TRANSACTION; " + "DELETE FROM mokki " + "WHERE mokki_id=" + dgvCottage.CurrentRow.Cells[0].Value.ToString() + "; " + "COMMIT;"; ConnectionUtils.OpenConnection(); MySqlCommand command = new MySqlCommand(query, ConnectionUtils.connection); command.ExecuteNonQuery(); ConnectionUtils.CloseConnection(); PopulateDGVCottage(); } } catch (Exception ex) { ConnectionUtils.CloseConnection(); MessageBox.Show("Tietojen poisto ei onnistunut. Yritä uudelleen myöhemmin. Lisätietoja: " + ex.Message); } }
///<summary>Creates invoice.pdf file with reservation details, saves it and opens it</summary> public static void CreatePdfDocument(int lasku_id) { ConnectionUtils.OpenConnection(); string fileSaveLocation = Path.GetDirectoryName(Application.ExecutablePath) + "\\invoice.pdf"; //Next to .exe file //Customer name string query = "SELECT CONCAT(a.etunimi, ' ', a.sukunimi) AS nimi " + "FROM lasku l " + "JOIN varaus v ON l.varaus_id = v.varaus_id " + "JOIN asiakas a ON v.asiakas_id = a.asiakas_id " + "WHERE l.lasku_id = " + lasku_id + ";"; MySqlCommand cmd = new MySqlCommand(query, ConnectionUtils.connection); string customerName = cmd.ExecuteScalar().ToString(); //Customer address query = "SELECT a.lahiosoite " + "FROM lasku l " + "JOIN varaus v ON l.varaus_id = v.varaus_id " + "JOIN asiakas a ON v.asiakas_id = a.asiakas_id " + "WHERE l.lasku_id = " + lasku_id + ";"; cmd = new MySqlCommand(query, ConnectionUtils.connection); string customerAddress = cmd.ExecuteScalar().ToString(); //Customer zip code query = "SELECT CONCAT(a.postinro, ' ', p.toimipaikka) AS posti " + "FROM lasku l " + "JOIN varaus v ON l.varaus_id = v.varaus_id " + "JOIN asiakas a ON v.asiakas_id = a.asiakas_id " + "JOIN posti p ON a.postinro = p.postinro " + "WHERE l.lasku_id = " + lasku_id + ";"; cmd = new MySqlCommand(query, ConnectionUtils.connection); string customerPostal = cmd.ExecuteScalar().ToString().ToUpper(); //varaus_id for calculating the price later query = "SELECT varaus_id " + "FROM lasku " + "WHERE lasku_id = " + lasku_id + ";"; cmd = new MySqlCommand(query, ConnectionUtils.connection); int varaus_id = Convert.ToInt32(cmd.ExecuteScalar()); DateTime billingDate = DateTime.Now; string dueDate = DateTime.Now.AddDays(14).ToShortDateString(); string[] senderAddress = { "Village Newbies Oy", "Siilokatu 1", "90700 OULU" }; string[] receiverAddress = { customerName, customerAddress, customerPostal }; List <ItemRow> itemsList = GenerateItemsList(lasku_id); double totalPrice = CalculateTotalPrice(varaus_id); string totalPriceString = String.Format("{0:0.00}", totalPrice); double totalAlvAmount = 0; foreach (ItemRow item in itemsList) { totalAlvAmount += (Convert.ToDouble(item.Total) * (Convert.ToDouble(item.VAT) / 100)); } double subTotal = totalPrice - totalAlvAmount; //Creating the PDF document new InvoicerApi(SizeOption.A4, OrientationOption.Portrait, "€") .Reference(lasku_id.ToString()) .BillingDate(billingDate) .Company(Address.Make("Lähettäjä", senderAddress, "", "")) .Client(Address.Make("Vastaanottaja", receiverAddress, "", "")) .TextColor("#2E5902") .BackColor("#e7fecf") .Image(@"..\..\images\vnLogo.png", 175, 60) .Items(itemsList) .Totals(new List <TotalRow> { TotalRow.Make("Välisumma", (decimal)subTotal), TotalRow.Make("ALV", (decimal)totalAlvAmount), TotalRow.Make("Kokonaishinta", (decimal)totalPrice, true), }) .Details(new List <DetailRow> { DetailRow.Make("MAKSUTIEDOT", "SAAJAN TILINUMERO", "FI12345678910", "BIC", "NDEAFIHH", "SAAJA", "VILLAGE NEWBIES OY", "VIITENUMERO", "123456789", "ERÄPÄIVÄ", dueDate, "EURO", totalPriceString) }) .Footer("http://www.villagenewbies.fi") .Save(fileSaveLocation); //Open the document using default application System.Diagnostics.Process.Start(fileSaveLocation); ConnectionUtils.CloseConnection(); }
private void btmOrder_OrderModify_Click(object sender, EventArgs e) { if (!OrderUtils.CheckOrderCottageBookDate(Convert.ToInt32(tbOrder_ModifyCottageID.Text), Convert.ToInt32(lbOrder_ModifyOrderID.Text), dtpOrder_ModifyStartDate.Text, dtpOrder_ModifyEndDate.Text)) //Check is cottage free on selected dates { return; } DialogResult res = MessageBox.Show("Haluatko varmasti tallentaa muokatut tiedot?", "Muokkaa varaus", MessageBoxButtons.YesNo, MessageBoxIcon.Information); if (res == DialogResult.Yes) { string queryServices = "START TRANSACTION; "; foreach (DataGridViewRow row in dgvOrderServices.Rows) // Services modify { ConnectionUtils.OpenConnection(); MySqlCommand checkRow = new MySqlCommand("SELECT * FROM varauksen_palvelut WHERE varaus_id = '" + lbOrder_ModifyOrderID.Text + "' AND palvelu_id = '" + row.Cells[0].Value.ToString() + "'", ConnectionUtils.connection); MySqlDataReader reader = checkRow.ExecuteReader(); if (reader.HasRows && Convert.ToInt32(row.Cells["kpl"].Value) != 0) // If service already in database { reader.Read(); if (Convert.ToInt32(reader["lkm"]) != Convert.ToInt32(row.Cells["kpl"].Value)) { queryServices += "UPDATE varauksen_palvelut SET lkm='" + row.Cells["kpl"].Value.ToString() + "' " + "WHERE varaus_id = '" + lbOrder_ModifyOrderID.Text + "' AND palvelu_id = '" + row.Cells[0].Value.ToString() + "'; "; } } else if (!reader.HasRows && Convert.ToInt32(row.Cells["kpl"].Value) != 0) // If new service { queryServices += "INSERT INTO vn.varauksen_palvelut(varaus_id, palvelu_id, lkm) " + "VALUES(" + lbOrder_ModifyOrderID.Text + ", " + row.Cells["ID"].Value.ToString() + ", " + row.Cells["kpl"].Value.ToString() + "); "; } else if (reader.HasRows && Convert.ToInt32(row.Cells["kpl"].Value) == 0) // if service set to 0 { queryServices += "DELETE FROM varauksen_palvelut WHERE varaus_id = '" + lbOrder_ModifyOrderID.Text + "' AND palvelu_id = '" + row.Cells[0].Value.ToString() + "'; "; } ConnectionUtils.CloseConnection(); } queryServices += "COMMIT;"; if (queryServices != "START TRANSACTION; COMMIT;") // check if services not changet { try { ConnectionUtils.OpenConnection(); MySqlCommand command1 = new MySqlCommand(queryServices, ConnectionUtils.connection); command1.ExecuteNonQuery(); // Add/EDIT/REMOVE services ConnectionUtils.CloseConnection(); } catch { MessageBox.Show(queryServices); } } //Update order's start- and(or) enddate string query = "START TRANSACTION; " + "UPDATE varaus " + "SET varattu_alkupvm='" + dtpOrder_ModifyStartDate.Text + " 16:00:00',varattu_loppupvm='" + dtpOrder_ModifyEndDate.Text + " 12:00:00'" + "WHERE varaus_id=" + lbOrder_ModifyOrderID.Text + "; " + "COMMIT;"; ConnectionUtils.OpenConnection(); MySqlCommand command2 = new MySqlCommand(query, ConnectionUtils.connection); command2.ExecuteNonQuery(); ConnectionUtils.CloseConnection(); Close(); } }
private void btnBookSearch_Click(object sender, EventArgs e) { dt.Clear(); currentcustomer = 0; if (tbBookCustomerEmail.Text != "") // Search customer by email { ConnectionUtils.OpenConnection(); MySqlCommand ckech_is_user_exists = new MySqlCommand("SELECT * FROM asiakas WHERE email like '" + tbBookCustomerEmail.Text + "'", ConnectionUtils.connection); MySqlDataReader reader = ckech_is_user_exists.ExecuteReader(); dt.Load(reader); if (dt.Rows.Count == 1) // Fill data if finded one record { Fill_customer_values(); lblBookCustomerExists.Text = "Sähköpostilla löydetty 1 asiakas"; btnBookNext.Visible = false; btnBookPrev.Visible = false; lblCustomerOnSame.Visible = false; tbBookCustomerEmail.Font = new Font(tbBookCustomerEmail.Font, FontStyle.Bold); tbBookCustomerPhone.Font = new Font(tbBookCustomerPhone.Font, FontStyle.Regular); } else if (dt.Rows.Count > 1) // If finded more than 1 record { lblBookCustomerExists.Text = "Sähköpostiosoitella löytyy " + dt.Rows.Count.ToString() + " asiakasta"; Fill_customer_values(); lblCustomerOnSame.Text = "1/" + dt.Rows.Count.ToString(); btnBookNext.Visible = true; btnBookPrev.Visible = true; lblCustomerOnSame.Visible = true; tbBookCustomerEmail.Font = new Font(tbBookCustomerEmail.Font, FontStyle.Bold); tbBookCustomerPhone.Font = new Font(tbBookCustomerPhone.Font, FontStyle.Regular); } else { Erase_customer_values(); lblBookCustomerExists.Text = "Asiakasta ei löydy, syötä uuden asiakkaan tiedot."; tbBookCustomerPhone.Font = new Font(tbBookCustomerPhone.Font, FontStyle.Regular); tbBookCustomerEmail.Font = new Font(tbBookCustomerEmail.Font, FontStyle.Regular); } ConnectionUtils.CloseConnection(); } else if (tbBookCustomerPhone.Text != "") //Search customer by phonenumber { ConnectionUtils.OpenConnection(); MySqlCommand ckech_is_user_exists = new MySqlCommand("SELECT * FROM asiakas WHERE puhelinnro like '" + tbBookCustomerPhone.Text + "'", ConnectionUtils.connection); MySqlDataReader reader = ckech_is_user_exists.ExecuteReader(); dt.Load(reader); lblBookCustomerExists.Visible = true; if (dt.Rows.Count == 1) { Fill_customer_values(); lblBookCustomerExists.Text = "Puhelinnumerolla löydetty 1 asiakas"; btnBookNext.Visible = false; btnBookPrev.Visible = false; lblCustomerOnSame.Visible = false; tbBookCustomerEmail.Font = new Font(tbBookCustomerEmail.Font, FontStyle.Regular); tbBookCustomerPhone.Font = new Font(tbBookCustomerPhone.Font, FontStyle.Bold); } else if (dt.Rows.Count > 1) { lblBookCustomerExists.Text = "Puhelinnumerolla löytyy " + dt.Rows.Count.ToString() + " asiakasta"; Fill_customer_values(); lblCustomerOnSame.Text = "1/" + dt.Rows.Count.ToString(); btnBookNext.Visible = true; btnBookPrev.Visible = true; lblCustomerOnSame.Visible = true; tbBookCustomerEmail.Font = new Font(tbBookCustomerEmail.Font, FontStyle.Regular); tbBookCustomerPhone.Font = new Font(tbBookCustomerPhone.Font, FontStyle.Bold); } else { Erase_customer_values(); lblBookCustomerExists.Text = "Asiakasta ei löydy, syötä uuden asiakkaan tiedot."; tbBookCustomerPhone.Font = new Font(tbBookCustomerPhone.Font, FontStyle.Regular); tbBookCustomerEmail.Font = new Font(tbBookCustomerEmail.Font, FontStyle.Regular); } ConnectionUtils.CloseConnection(); } else { lblBookCustomerExists.Text = "Hae asiakasta sähköpostilla tai puhelinnumerolla"; Erase_customer_values(); tbBookCustomerPhone.Font = new Font(tbBookCustomerPhone.Font, FontStyle.Regular); tbBookCustomerEmail.Font = new Font(tbBookCustomerEmail.Font, FontStyle.Regular); } }
private void btnBookAddResirvation_Click(object sender, EventArgs e) // new book adding { if (tbBookCustomerEmail.Text == "" || tbBookCustomerPhone.Text == "" || tbBookCustomerName.Text == "" || tbBookCustomerLastname.Text == "" || tbBookCustomerPostnumber.Text == "" || tbBookCustomerPostOffice.Text == "" || tbBookCustomerAddress.Text == "") { MessageBox.Show("Kaikki asiakastiedot pitäisi olla täyetty.", "Tiedot puuttuu", MessageBoxButtons.OK, MessageBoxIcon.Error); return; // If some customer data not specified, cancel action } string queryCustomer, services = "\n\nLisäpalvelut:\n"; if (customerid != 0) //If customer finded, update it { PostUtils.CheckPostal(tbBookCustomerPostnumber.Text, tbBookCustomerPostOffice.Text); ConnectionUtils.OpenConnection(); MySqlCommand command = new MySqlCommand("SELECT toimipaikka FROM posti WHERE postinro='" + tbBookCustomerPostnumber.Text + "'", ConnectionUtils.connection); tbBookCustomerPostOffice.Text = command.ExecuteScalar().ToString(); ConnectionUtils.CloseConnection(); queryCustomer = "START TRANSACTION; " + "UPDATE asiakas SET etunimi='" + tbBookCustomerName.Text + "', sukunimi='" + tbBookCustomerLastname.Text + "', " + "lahiosoite='" + tbBookCustomerAddress.Text + "', postinro='" + tbBookCustomerPostnumber.Text + "', " + "email='" + tbBookCustomerEmail.Text + "', puhelinnro='" + tbBookCustomerPhone.Text + "' " + "WHERE asiakas_id='" + customerid + "'; " + "COMMIT;"; } else // if customer not finded, add new { PostUtils.CheckPostal(tbBookCustomerPostnumber.Text, tbBookCustomerPostOffice.Text); queryCustomer = "START TRANSACTION; " + "INSERT INTO asiakas(asiakas_id,postinro,etunimi,sukunimi,lahiosoite,email,puhelinnro) " + "VALUES(default,'" + tbBookCustomerPostnumber.Text + "','" + tbBookCustomerName.Text + "','" + tbBookCustomerLastname.Text + "','" + tbBookCustomerAddress.Text + "','" + tbBookCustomerEmail.Text + "','" + tbBookCustomerPhone.Text + "'); " + "COMMIT;"; } foreach (DataGridViewRow row in dgvBookServices.Rows) // Get services { if (Convert.ToInt32(row.Cells["kpl"].Value) != 0) { services += row.Cells["nimi"].Value.ToString() + " - " + row.Cells["kpl"].Value.ToString() + "kpl\n"; } } if (services == "\n\nLisäpalvelut:\n") // If all services are 0, erase services { services = ""; } // Book/order overview window DialogResult res = MessageBox.Show("\t\tYhteenveto \n\nMökki tiedot:" + "\nAlue: \t\t" + lblBookAlue.Text + "\nMökki ID: \t" + lblBookCottageId.Text + "\nMajoitusaika: \t" + lblBookBookingDateFrom.Text + " - " + lblBookBookingDateTo.Text + " " + lblBookDays.Text + "\nOsoite: \t\t" + lblBookCottageAddress.Text + "\n\nAsiakas tiedot: " + "\nNimi: \t\t" + tbBookCustomerName.Text + " " + tbBookCustomerLastname.Text + "\nOsoite: \t\t" + tbBookCustomerAddress.Text + ", " + tbBookCustomerPostnumber.Text + ", " + tbBookCustomerPostOffice.Text + "\nSähköposti: \t" + tbBookCustomerEmail.Text + "\nPuhelinnumero: \t" + tbBookCustomerPhone.Text + services + "\n\nSumma yhteensä: " + lblBookPriceFull.Text, "Yhteenveto", MessageBoxButtons.OKCancel, MessageBoxIcon.Information); if (res == DialogResult.OK) { try { ConnectionUtils.OpenConnection(); MySqlCommand command1 = new MySqlCommand(queryCustomer, ConnectionUtils.connection); command1.ExecuteNonQuery(); // 1. add/update customer string queryBook = MakeQueryBook(); ConnectionUtils.OpenConnection(); MySqlCommand command2 = new MySqlCommand(queryBook, ConnectionUtils.connection); command2.ExecuteNonQuery(); // 2. get added/updated customer ID and make book ConnectionUtils.CloseConnection(); ConnectionUtils.OpenConnection(); MySqlCommand command = new MySqlCommand("SELECT varaus_id FROM varaus WHERE asiakas_id LIKE '" + customerid + "' AND mokki_mokki_id LIKE '" + lblBookCottageId.Text + "' AND varattu_alkupvm LIKE '" + lblBookBookingDateFrom.Text + "%' AND varattu_loppupvm LIKE '" + lblBookBookingDateTo.Text + "%' ", ConnectionUtils.connection); varausid = Convert.ToInt32(command.ExecuteScalar()); // get just added order ID ConnectionUtils.CloseConnection(); if (services != "") // if services exists { string queryServices = MakeQueryServices(); ConnectionUtils.OpenConnection(); MySqlCommand command3 = new MySqlCommand(queryServices, ConnectionUtils.connection); command3.ExecuteNonQuery(); // 3. get added order ID and add services to data table ConnectionUtils.CloseConnection(); this.Close(); } BillingUtils.CreateInvoice(varausid); // Make new billing DialogResult result = MessageBox.Show("Varaus lisätty. Uusi lasku lisätty järjestelmään. Varaus id: " + varausid + " \nSiirrytäänkö laskuun?", "Prosessi onnistui", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == DialogResult.Yes) { BillingUtils.GoToCreatedInvoice(varausid); } this.Close(); } catch (Exception ex) { MessageBox.Show("Tapahtunut virhe, yritä uudelleen. Virhe: \n\n\n" + ex, "Virhe", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else if (res == DialogResult.Cancel) { } }
private void btnSearchHae_Click(object sender, EventArgs e) { DataTable data = new DataTable(); string query = "SELECT m.mokki_id, t.nimi as Toimintaalue, m.postinro as Postinumero, m.mokkinimi as 'Nimi', m.katuosoite, " + "m.kuvaus as 'Kuvaus', m.henkilomaara, m.varustelu as 'Varustelu', m.hinta " + "FROM mokki m INNER JOIN toimintaalue t " + "ON m.toimintaalue_id = t.toimintaalue_id "; if (cbSearchAlueKaikki.Checked == false) // Get alue_id { ConnectionUtils.OpenConnection(); MySqlCommand command = new MySqlCommand("SELECT toimintaalue_id FROM toimintaalue WHERE nimi Like '" + cbSearchAluet.Text + "'", ConnectionUtils.connection); string alue_id = command.ExecuteScalar().ToString(); ConnectionUtils.CloseConnection(); query += "WHERE m.toimintaalue_id LIKE '" + alue_id + "' "; } if (nudSearchHintaraja.Value != 0 && cbSearchAlueKaikki.Checked == true) // Set price limit { query += "WHERE m.hinta <= '" + nudSearchHintaraja.Value + "' "; } else if (nudSearchHintaraja.Value != 0 && cbSearchAlueKaikki.Checked == false) { query += "AND m.hinta <= '" + nudSearchHintaraja.Value + "' "; } if (nudSearchMaxhlo.Value != 0) // Set customer quantity { if (nudSearchHintaraja.Value != 0 || cbSearchAlueKaikki.Checked == false) { query += "AND "; } else { query += "WHERE "; } query += "m.henkilomaara >= '" + nudSearchMaxhlo.Value + "' "; } if (cbSearchVarustelu.Text != "Kaikki") { if (nudSearchHintaraja.Value == 0 && cbSearchAlueKaikki.Checked == true && nudSearchMaxhlo.Value == 0) { query += "WHERE varustelu = '" + cbSearchVarustelu.Text + "' "; } else { query += "AND varustelu = '" + cbSearchVarustelu.Text + "' "; } } query += "AND m.mokki_id not IN " + // Check is cottage free on dates "(SELECT mokki_mokki_id FROM varaus v " + "WHERE '" + dtpSearchFROM.Text + " 16:00:00' <= v.varattu_loppupvm and '" + dtpSearchTO.Text + " 12:00:00' >= v.varattu_alkupvm)"; MySqlDataAdapter sda = new MySqlDataAdapter(query, ConnectionUtils.connection); try { ConnectionUtils.OpenConnection(); dgSearchTable.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; dgSearchTable.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter; sda.Fill(data); dgSearchTable.DataSource = data; dgSearchTable.Columns[0].HeaderText = "Mökki ID"; dgSearchTable.Columns[4].HeaderText = "Lähiosoite"; dgSearchTable.Columns[6].HeaderText = "Henkilömäärä(max)"; dgSearchTable.Columns[8].HeaderText = "Hinta (€)"; ConnectionUtils.CloseConnection(); } catch (Exception ex) { MessageBox.Show("Virhe, yritä uudelleen\n\n" + ex, "Virhe", MessageBoxButtons.OK, MessageBoxIcon.Error); } }