private void btnCottageSearch_Click(object sender, EventArgs e)
{
try
{
//Get's data from form components, and does a query to the DB. Updates CottageDataGridView-component to show search results
string query = "SELECT * FROM mokki " +
"WHERE toimintaalue_id = " + RegionUtils.RegionNameToIndex(cbCottageRegions.Text) + " " +
"AND postinro LIKE '%" + TextBoxUtils.ModifyInput(tbCottagePostNum.Text, 5) + "%' " +
"AND mokkinimi LIKE '%" + TextBoxUtils.ModifyInput(tbCottageName.Text, 45) + "%' " +
"AND katuosoite LIKE '%" + TextBoxUtils.ModifyInput(tbCottageStreetAddress.Text, 45) + "%' " +
"AND kuvaus LIKE '%" + TextBoxUtils.ModifyInput(tbCottageDescription.Text, 500) + "%' " +
"AND henkilomaara > '" + (nudCottageCapacity.Value - 1) + "' " +
"AND varustelu LIKE '%" + cbCottageEqupment.Text + "%' " +
"AND hinta <(" + Convert.ToDouble(nudCottagePrice.Value + 1) + ");";
try
{
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(query, ConnectionUtils.connection);
adapter.Fill(table);
dgvCottage.DataSource = table;
}
catch (Exception ex)
{
MessageBox.Show("Virhe tietojen hakemisessa. Tarkista tiedot ja yritä uudelleen. Lisätietoja: " + ex.Message);
}
}
catch (Exception ex)
{
MessageBox.Show("Virhe haun tekemisessä. Tarkista tiedot ja yritä uudelleen. Lisätietoja: " + ex.Message);
}
}
private void btnServiceSearch_Click(object sender, EventArgs e)
{
try
{
//Get's data from form components, and does a query to the DB. Updates ServiceDataGridView-component to show search results
string query = "SELECT * FROM palvelu " +
"WHERE toimintaalue_id = " + RegionUtils.RegionNameToIndex(cbServiceRegion.Text) + " " +
"AND nimi LIKE '%" + TextBoxUtils.ModifyInput(tbServiceName.Text, 40) + "%' " +
"AND tyyppi LIKE '%" + tbServiceType.Text + "%' " +
"AND kuvaus LIKE '%" + TextBoxUtils.ModifyInput(tbServiceDescription.Text, 500) + "%' " +
"AND hinta <(" + (nudServicePrice.Value + 1) + ");";
try
{
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(query, ConnectionUtils.connection);
adapter.Fill(table);
dgvService.DataSource = table;
dgvService.Columns[0].HeaderText = "Palvelu ID";
dgvService.Columns[1].HeaderText = "Toiminta-alue ID";
dgvService.Columns[2].HeaderText = "Nimi";
dgvService.Columns[3].HeaderText = "Tyyppi";
dgvService.Columns[4].HeaderText = "Kuvaus";
dgvService.Columns[5].HeaderText = "Hinta (€)";
dgvService.Columns[6].HeaderText = "Alv (%)";
}
catch (Exception ex)
{
MessageBox.Show("Virhe tietojen hakemisessa. Tarkista tiedot ja yritä uudelleen. Lisätietoja: " + ex.Message);
}
}
catch (Exception ex)
{
MessageBox.Show("Virhe haun tekemisessä. Tarkista tiedot ja yritä uudelleen. Lisätietoja: " + ex.Message);
}
}
private void btnAdd_Click(object sender, EventArgs e) //add a new customer to the database
{
PostUtils.CheckPostal(tbCustomerPostalAdd.Text, tbCustomerPostOfficeAdd.Text);
ConnectionUtils.OpenConnection();
string query3 = "START TRANSACTION; " +
"INSERT INTO asiakas(asiakas_id,postinro,etunimi,sukunimi,lahiosoite,email,puhelinnro) " +
"VALUES(default,'" + TextBoxUtils.ModifyInput(tbCustomerPostalAdd.Text, tbCustomerPostalAdd.MaxLength) + "','" + TextBoxUtils.ModifyInput(tbCustomerFNameAdd.Text, tbCustomerFNameAdd.MaxLength) +
"','" + TextBoxUtils.ModifyInput(tbCustomerLNameAdd.Text, tbCustomerLNameAdd.MaxLength) + "','" + TextBoxUtils.ModifyInput(tbCustomerAddressAdd.Text, tbCustomerAddressAdd.MaxLength) +
"','" + TextBoxUtils.ModifyInput(tbCustomerEmailAdd.Text, tbCustomerEmailAdd.MaxLength) + "','" + TextBoxUtils.ModifyInput(tbCustomerPhoneAdd.Text, tbCustomerPhoneAdd.MaxLength) + "'); " +
"COMMIT;";
MySqlCommand command3 = new MySqlCommand(query3, ConnectionUtils.connection);
command3.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
this.Close();
}
///<summary>Executes a standard search query at "laskut" tab</summary>
private void PopulateDGVBilling()
{
ConnectionUtils.OpenConnection();
string query = "SELECT l.lasku_id, v.varaus_id, a.asiakas_id, CONCAT(a.etunimi, ' ', a.sukunimi) " +
", a.lahiosoite, a.puhelinnro, a.email, l.summa, v.vahvistus_pvm " +
"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 LIKE '%" + TextBoxUtils.ModifyInput(txtboxBillingInvoiceID.Text, 11) + "%' " +
"AND v.varaus_id LIKE '%" + TextBoxUtils.ModifyInput(txtboxBillingOrderID.Text, 11) + "%' " +
"AND a.asiakas_id LIKE '%" + TextBoxUtils.ModifyInput(txtboxBillingCustomerID.Text, 11) + "%' " +
"AND a.etunimi LIKE '%" + TextBoxUtils.ModifyInput(txtboxBillingSurname.Text, 20) + "%' " +
"AND a.sukunimi LIKE '%" + TextBoxUtils.ModifyInput(txtboxBillingLastname.Text, 40) + "%' " +
"AND a.email LIKE '%" + TextBoxUtils.ModifyInput(txtboxBillingEmail.Text, 50) + "%' " +
"AND a.puhelinnro LIKE '%" + TextBoxUtils.ModifyInput(txtboxBillingPhone.Text, 15) + "%' ";
if (cbBillingPaid.SelectedIndex == 0)
{
query += "AND v.vahvistus_pvm IS NOT NULL ORDER BY l.lasku_id;";
}
else if (cbBillingPaid.SelectedIndex == 1)
{
query += "AND v.vahvistus_pvm IS NULL ORDER BY l.lasku_id;";
}
else
{
query += "ORDER BY l.lasku_id;";
}
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(query, ConnectionUtils.connection);
adapter.Fill(table);
dgvBilling.DataSource = table;
dgvBilling.Columns[0].HeaderText = "Lasku ID";
dgvBilling.Columns[1].HeaderText = "Varaus ID";
dgvBilling.Columns[2].HeaderText = "Asiakas ID";
dgvBilling.Columns[3].HeaderText = "Asiakkaan nimi";
dgvBilling.Columns[4].HeaderText = "Lähiosoite";
dgvBilling.Columns[5].HeaderText = "Puhelinnumero";
dgvBilling.Columns[6].HeaderText = "Sähköposti";
dgvBilling.Columns[7].HeaderText = "Summa (€)";
dgvBilling.Columns[8].HeaderText = "Maksettu (pvm)";
BillingUtils.latestQuery = query;
ConnectionUtils.CloseConnection();
}
private void Fill_customer_values() // Fill customer data function
{
if (tbBookCustomerEmail.Text == "")
{
tbBookCustomerEmail.Text = TextBoxUtils.ModifyInput(dt.Rows[currentcustomer].Field <string>("email").ToString(), tbBookCustomerEmail.MaxLength);
}
else if (tbBookCustomerPhone.Text == "")
{
tbBookCustomerPhone.Text = TextBoxUtils.ModifyInput(dt.Rows[currentcustomer].Field <string>("puhelinnro"), tbBookCustomerPhone.MaxLength);
}
tbBookCustomerName.Text = TextBoxUtils.ModifyInput(dt.Rows[currentcustomer].Field <string>("etunimi").ToString(), tbBookCustomerName.MaxLength);
tbBookCustomerLastname.Text = TextBoxUtils.ModifyInput(dt.Rows[currentcustomer].Field <string>("sukunimi").ToString(), tbBookCustomerLastname.MaxLength);
tbBookCustomerAddress.Text = TextBoxUtils.ModifyInput(dt.Rows[currentcustomer].Field <string>("lahiosoite").ToString(), tbBookCustomerAddress.MaxLength);
tbBookCustomerPostnumber.Text = TextBoxUtils.ModifyInput(dt.Rows[currentcustomer].Field <string>("postinro").ToString(), tbBookCustomerPostnumber.MaxLength);
tbBookCustomerPostOffice.Text = TextBoxUtils.ModifyInput(PostUtils.GetPostOffice(dt.Rows[currentcustomer].Field <string>("postinro").ToString()), tbBookCustomerPostOffice.MaxLength);
customerid = dt.Rows[currentcustomer].Field <int>("asiakas_id");
lblBookCustomerID.Visible = true;
lblBookCustomerID.Text = "Asiakas ID: " + customerid.ToString();
}
private void btnModify_Click(object sender, EventArgs e) //modifies the customer's data according to the textbox inputs
{
DialogResult res = MessageBox.Show("Haluatko varmasti muuttaa valitun asiakkaan tietoja?", "Muuta asiakkaan tietoja", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (res == DialogResult.Yes)
{
PostUtils.CheckPostal(tbCustomerPostalMod.Text, tbCustomerPostOfficeMod.Text);
string query = "START TRANSACTION; " +
"UPDATE asiakas " +
"SET postinro='" + TextBoxUtils.ModifyInput(tbCustomerPostalMod.Text, tbCustomerPostalMod.MaxLength) + "',etunimi='" + TextBoxUtils.ModifyInput(tbCustomerFNameMod.Text, tbCustomerFNameMod.MaxLength) +
"',sukunimi='" + TextBoxUtils.ModifyInput(tbCustomerLNameMod.Text, tbCustomerLNameMod.MaxLength) + "',lahiosoite='" + TextBoxUtils.ModifyInput(tbCustomerAddressMod.Text, tbCustomerAddressMod.MaxLength) + "'," +
"email='" + TextBoxUtils.ModifyInput(tbCustomerEmailMod.Text, tbCustomerEmailMod.MaxLength) + "',puhelinnro='" + TextBoxUtils.ModifyInput(tbCustomerPhoneMod.Text, tbCustomerPhoneMod.MaxLength) + "' " +
"WHERE asiakas_id=" + lblCustomerIDMod.Text + "; " +
"COMMIT;";
ConnectionUtils.OpenConnection();
MySqlCommand command = new MySqlCommand(query, ConnectionUtils.connection);
command.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
this.Close();
}
}
private void btnCustomerSearch_Click(object sender, EventArgs e) //get data from asiakas-table to datagridview according to the search criteria
{
string query = "SELECT * FROM asiakas " +
"WHERE postinro LIKE '%" + TextBoxUtils.ModifyInput(tbCustomerPostal.Text, tbCustomerPostal.MaxLength) + "%' " +
"AND etunimi LIKE '%" + TextBoxUtils.ModifyInput(tbCustomerFName.Text, tbCustomerFName.MaxLength) + "%' " +
"AND sukunimi LIKE '%" + TextBoxUtils.ModifyInput(tbCustomerLName.Text, tbCustomerLName.MaxLength) + "%' " +
"AND lahiosoite LIKE '%" + TextBoxUtils.ModifyInput(tbCustomerAddress.Text, tbCustomerAddress.MaxLength) + "%' " +
"AND email LIKE '%" + TextBoxUtils.ModifyInput(tbCustomerEmail.Text, tbCustomerEmail.MaxLength) + "%' " +
"AND puhelinnro LIKE '%" + TextBoxUtils.ModifyInput(tbCustomerPhone.Text, tbCustomerPhone.MaxLength) + "%';";
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(query, ConnectionUtils.connection);
adapter.Fill(table);
dgvCustomer.DataSource = table;
dgvCustomer.Columns[0].HeaderText = "Asiakas ID";
dgvCustomer.Columns[1].HeaderText = "Postinumero";
dgvCustomer.Columns[2].HeaderText = "Etunimi";
dgvCustomer.Columns[3].HeaderText = "Sukunimi";
dgvCustomer.Columns[4].HeaderText = "Lähiosoite";
dgvCustomer.Columns[5].HeaderText = "Sähköposti";
dgvCustomer.Columns[6].HeaderText = "Puhelinnumero";
}
private void btnRegionSearch_Click(object sender, EventArgs e)
{
try
{
//Get's data from tbRegionName, and does a query to the DB. Updates dgvRegion-component to show search results
string query = "SELECT * FROM toimintaalue " +
"WHERE nimi LIKE '%" + TextBoxUtils.ModifyInput(tbRegionName.Text, 40) + "%';";
try
{
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(query, ConnectionUtils.connection);
adapter.Fill(table);
dgvRegion.DataSource = table;
}
catch (Exception ex)
{
MessageBox.Show("Virhe tietojen hakemisessa. Tarkista tiedot ja yritä uudelleen. Lisätietoja: " + ex.Message);
}
}
catch (Exception ex)
{
MessageBox.Show("Virhe haun tekemisessä. Tarkista tiedot ja yritä uudelleen. Lisätietoja: " + ex.Message);
}
}
private void tbBookCustomerPostOffice_Leave(object sender, EventArgs e)
{
tbBookCustomerPostOffice.Text = TextBoxUtils.ModifyInput(tbBookCustomerPostOffice.Text, tbBookCustomerPostOffice.MaxLength);
}
private void tbBookCustomerAddress_Leave(object sender, EventArgs e)
{
tbBookCustomerAddress.Text = TextBoxUtils.ModifyInput(tbBookCustomerAddress.Text, tbBookCustomerAddress.MaxLength);
}
