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);
}
}
