private void Fill_dgvOrderServices()
{
ConnectionUtils.OpenConnection();
MySqlCommand command = new MySqlCommand("SELECT toimintaalue_id FROM mokki WHERE mokki_id = '" + tbOrder_ModifyCottageID.Text + "'", ConnectionUtils.connection);
alue_id = Convert.ToInt32(command.ExecuteScalar());
ConnectionUtils.CloseConnection();
string query = "SELECT p.palvelu_id as 'ID', p.nimi as 'Nimi', p.kuvaus as 'Kuvaus', p.hinta as 'hinta/kpl', " +
"coalesce(lkm, 0) AS kpl FROM palvelu p LEFT outer JOIN varauksen_palvelut vp ON p.palvelu_id = vp.palvelu_id " +
"AND varaus_id = '" + lbOrder_ModifyOrderID.Text + "' " +
"WHERE toimintaalue_id = '" + alue_id + "'";
MySqlDataAdapter sda = new MySqlDataAdapter(query, ConnectionUtils.connection);
DataTable data = new DataTable();
sda.Fill(data);
dgvOrderServices.DataSource = data;
dgvOrderServices.Columns[0].Width = 30;
dgvOrderServices.Columns[1].Width = 137;
dgvOrderServices.Columns[2].Width = 240;
dgvOrderServices.Columns[3].Width = 55;
dgvOrderServices.Columns[4].Width = 35;
foreach (DataGridViewColumn dgvc in dgvOrderServices.Columns) // Make all rows non editable
{
dgvc.ReadOnly = true;
dgvOrderServices.Columns[4].DefaultCellStyle.BackColor = Color.PaleGreen;
}
dgvOrderServices.Columns[4].ReadOnly = false; // Make editable only "kpl" row
}
private void DeleteSelectedRegion(object sender, EventArgs e)
{
//Deletes selected region from database
DialogResult result = MessageBox.Show("Haluatko varmasti poistaa valitun toiminta-alueen?", "Poista toimialue", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.Yes)
{
try
{
string query = "START TRANSACTION; " +
"DELETE FROM toimintaalue " +
"WHERE toimintaalue_id=" + dgvRegion.CurrentRow.Cells[0].Value.ToString() + "; " +
"COMMIT;";
ConnectionUtils.OpenConnection();
MySqlCommand command = new MySqlCommand(query, ConnectionUtils.connection);
command.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
PopulateDGVRegion();
tbRegionName.Text = "";
}
catch (Exception ex)
{
MessageBox.Show("Alueen poisto epäonnistui. Varmista, ettei alueeseen ole liitetty palveluita tai mökkejä, ja yritä uudelleen.", "Poisto epäonnistui"
, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
private void btnServiceDelete_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Haluatko varmasti poistaa valitun palvelun tiedot?", "Poista palvelun tiedot", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.Yes)
{
try
{
string query = "START TRANSACTION; " +
"DELETE FROM palvelu " +
"WHERE palvelu_id=" + dgvService.CurrentRow.Cells[0].Value.ToString() + "; " +
"COMMIT;";
ConnectionUtils.OpenConnection();
MySqlCommand command = new MySqlCommand(query, ConnectionUtils.connection);
command.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
PopulateDGVService();
}
catch (Exception ex)
{
ConnectionUtils.CloseConnection();
MessageBox.Show("Tietojen poisto ei onnistunut. Yritä uudelleen myöhemmin. Lisätietoja: " + ex.Message);
}
}
}
private void btmSearchVarata_Click(object sender, EventArgs e) // New order
{
// Check date again, if user change data, and no do search
if (!OrderUtils.CheckCottageBookDate(Convert.ToInt32(dgSearchTable.CurrentRow.Cells[0].Value), dtpSearchFROM.Text, dtpSearchTO.Text))
{
btnSearchHae_Click(sender, e);
return;
}
ConnectionUtils.OpenConnection();
MySqlCommand command = new MySqlCommand("SELECT toimintaalue_id FROM toimintaalue WHERE nimi Like '" +
dgSearchTable.CurrentRow.Cells[1].Value.ToString() + "'", ConnectionUtils.connection);
int toimintaalueid = Convert.ToInt32(command.ExecuteScalar().ToString());
// Make object to send on Booking window
Cottage cottage = new Cottage(Convert.ToInt32(dgSearchTable.CurrentRow.Cells[0].Value), toimintaalueid,
dgSearchTable.CurrentRow.Cells[2].Value.ToString(), dgSearchTable.CurrentRow.Cells[3].Value.ToString(),
dgSearchTable.CurrentRow.Cells[4].Value.ToString(), dgSearchTable.CurrentRow.Cells[5].Value.ToString(),
Convert.ToInt32(dgSearchTable.CurrentRow.Cells[6].Value.ToString()), dgSearchTable.CurrentRow.Cells[7].Value.ToString(),
Convert.ToDouble(dgSearchTable.CurrentRow.Cells[8].Value.ToString()));
ConnectionUtils.CloseConnection();
NewBook newbook = new NewBook(cottage, dtpSearchFROM.Value.Date, dtpSearchTO.Value.Date);
Booking booking = new Booking(newbook);
booking.ShowDialog();
}
public static void CheckPostal(string postalCode, string postOffice) //function for handling posti-table -related details
{
//first we'll check whether the postal code already exists in the database
ConnectionUtils.OpenConnection();
MySqlDataReader reader = null;
string query0 = "SELECT * FROM posti " +
"WHERE postinro LIKE '" + postalCode + "';";
MySqlCommand command0 = new MySqlCommand(query0, ConnectionUtils.connection);
reader = command0.ExecuteReader();
ArrayList postalList = new ArrayList();
ArrayList officeList = new ArrayList();
while (reader.Read())
{
postalList.Add((string)reader["postinro"]);
officeList.Add((string)reader["toimipaikka"]);
}
reader.Close();
ConnectionUtils.CloseConnection();
if (postalList.Count > 0) //the postal code already exists
{
if (!officeList[0].Equals(postOffice)) //the post office in textbox is different from that of database -> solve conflict
{
DialogResult res = MessageBox.Show("Postinumeroon " + postalList[0] +
" liittyvä postitoimipaikka on " + officeList[0] +
". Haluatko varmasti muuttaa postinumeroon liittyvää postitoimipaikkaa?",
"Muuta postinumeron postitoimipaikkaa", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (res == DialogResult.Yes)
{
ConnectionUtils.OpenConnection();
string query1 = "START TRANSACTION; " +
"UPDATE posti " +
"SET postinro='" + postalList[0] + "', toimipaikka='"
+ postOffice + "' WHERE postinro ='" + postalList[0] + "'; " +
"COMMIT;";
MySqlCommand command1 = new MySqlCommand(query1, ConnectionUtils.connection);
command1.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
}
else //res == DialogResult.No
{
//tbCustomerPostOffice.Text = (string)officeList[0];
}
}
}
else //the postal code does not exist, so we create it
{
ConnectionUtils.OpenConnection();
string query2 = "START TRANSACTION; " +
"INSERT INTO posti(postinro,toimipaikka) " +
"VALUES('" + postalCode + "','" + postOffice + "');" +
"COMMIT;";
MySqlCommand command2 = new MySqlCommand(query2, ConnectionUtils.connection);
command2.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
}
}
///<summary>Uses the latest search query to update the datagridview</summary>
public static void RefreshDataGridView(DataGridView dgvBilling)
{
ConnectionUtils.OpenConnection();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(latestQuery, ConnectionUtils.connection);
adapter.Fill(table);
dgvBilling.DataSource = table;
ConnectionUtils.CloseConnection();
}
///<summary>Deletes a bill</summary>
public static void DeleteSelectedInvoice(int lasku_id)
{
ConnectionUtils.OpenConnection();
string query = "START TRANSACTION; " +
"DELETE FROM lasku " +
"WHERE lasku_id = " + lasku_id + "; " +
"COMMIT;";
MySqlCommand command = new MySqlCommand(query, ConnectionUtils.connection);
command.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
}
private void btnCustomerDeleteInfo_Click(object sender, EventArgs e) //delete customer's identifying data from database
{ //First, we need to check whether the customer has unpaid bills
ConnectionUtils.OpenConnection();
string query0 = "SELECT l.maksettu " +
"FROM lasku l JOIN varaus v " +
"ON l.varaus_id = v.varaus_id " +
"JOIN asiakas a " +
"ON v.asiakas_id = a.asiakas_id " +
"WHERE a.asiakas_id =" + dgvCustomer.CurrentRow.Cells[0].Value.ToString() + ";";
MySqlCommand command0 = new MySqlCommand(query0, ConnectionUtils.connection);
MySqlDataReader reader = null;
reader = command0.ExecuteReader();
ArrayList billList = new ArrayList();
while (reader.Read())
{
billList.Add((bool)reader["maksettu"]);
}
reader.Close();
ConnectionUtils.CloseConnection();
bool unpaidBills = false;
foreach (bool b in billList)
{
if (b.Equals(false))
{
unpaidBills = true; //unpaid bill found
}
}
if (unpaidBills)
{
MessageBox.Show("Asiakkaalla on maksamattomia laskuja. Tietoja ei voida poistaa.", "Maksamattomia laskuja", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else //the customer does not have unpaid bills, so we can proceed to remove his/her data
{
DialogResult res = MessageBox.Show("Haluatko varmasti poistaa valitun asiakkaan tiedot?", "Poista asiakkaan tiedot", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (res == DialogResult.Yes)
{
string query = "START TRANSACTION; " +
"UPDATE asiakas " +
"SET postinro='xxxxx',etunimi='',sukunimi='',lahiosoite=''," +
"email='',puhelinnro='' " +
"WHERE asiakas_id=" + dgvCustomer.CurrentRow.Cells[0].Value.ToString() + "; " +
"COMMIT;";
ConnectionUtils.OpenConnection();
MySqlCommand command = new MySqlCommand(query, ConnectionUtils.connection);
command.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
PopulateDGVCustomer();
}
}
}
///<summary>Creates a bill for a reservation and checks if there are existing bills for this reservation</summary>
public static void CreateInvoice(int varaus_id)
{
ConnectionUtils.OpenConnection();
//Let's check if there's an existing invoice for this varaus_id value
string query = "SELECT COUNT(*) " +
"FROM lasku " +
"WHERE varaus_id = " + varaus_id + ";";
MySqlCommand command = new MySqlCommand(query, ConnectionUtils.connection);
int existingInvoices = Convert.ToInt32(command.ExecuteScalar());
DialogResult result = DialogResult.No;
if (existingInvoices != 0)
{
result = MessageBox.Show("Tälle varaukselle on jo yksi tai useampi lasku. Poistetaanko vanha lasku/laskut?", "Hetkinen!", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
}
//Delete existing invoices with the same reservation number
if (result == DialogResult.Yes)
{
for (int i = 0; i < existingInvoices; i++)
{
query = "SELECT lasku_id " +
"FROM lasku " +
"WHERE varaus_id = " + varaus_id + ";";
command = new MySqlCommand(query, ConnectionUtils.connection);
int lasku_id = Convert.ToInt32(command.ExecuteScalar());
query = "START TRANSACTION; " +
"DELETE FROM lasku " +
"WHERE lasku_id = " + lasku_id + "; " +
"COMMIT;";
command = new MySqlCommand(query, ConnectionUtils.connection);
command.ExecuteNonQuery();
}
}
//Create new invoice
double summa = CalculateTotalPrice(varaus_id);
double alv = 10;
string maksettu = "false";
query = "START TRANSACTION; " +
"INSERT INTO lasku(varaus_id, summa, alv, maksettu) " +
"VALUES(" + varaus_id + ", " + summa + ", " + alv + ", " + maksettu + "); " +
"COMMIT;";
command = new MySqlCommand(query, ConnectionUtils.connection);
command.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
}
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();
}
private void btnOrderRemove_Click(object sender, EventArgs e) //Delete order from database
{
DialogResult res = MessageBox.Show("Haluatko varmasti poistaa varauksen?", "Poista varaus", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (res == DialogResult.Yes)
{
string query = "START TRANSACTION; " +
"DELETE FROM varaus " +
"WHERE varaus_id=" + dgOrder.CurrentRow.Cells[0].Value.ToString() + "; " +
"COMMIT;";
ConnectionUtils.OpenConnection();
MySqlCommand command = new MySqlCommand(query, ConnectionUtils.connection);
command.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
PopulateDGVOrder();
}
}
///<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 tbOrder_ModifyCottageID_Leave(object sender, EventArgs e)
{
if (!OrderUtils.CheckCottageID(Convert.ToInt32(tbOrder_ModifyCottageID.Text))) //Check is database contains selected cottage
{
return;
}
else
{ //Update order's id in database
string query2 = "START TRANSACTION; " +
"UPDATE varaus " +
"SET mokki_mokki_id='" + tbOrder_ModifyCottageID.Text + "' " + "WHERE varaus_id=" + lbOrder_ModifyOrderID.Text + "; " +
"COMMIT;";
ConnectionUtils.OpenConnection();
MySqlCommand command3 = new MySqlCommand(query2, ConnectionUtils.connection);
command3.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
}
}
public Booking(NewBook b)
{
InitializeComponent();
ConnectionUtils.OpenConnection();
MySqlCommand command = new MySqlCommand("SELECT nimi FROM toimintaalue WHERE toimintaalue_id Like '" +
b.Cottage.RegionID.ToString() + "'", ConnectionUtils.connection);
string region = command.ExecuteScalar().ToString();
ConnectionUtils.CloseConnection();
// Fill cottage data and book date
lblBookCottageId.Text = b.Cottage.CottageID.ToString();
lblBookEquipment.Text = "(Varustelu: " + b.Cottage.Equipment + ")";
lblBookCottageName.Text = b.Cottage.Name.ToString();
lblBookCottageAddress.Text = b.Cottage.Address.ToString() + ", " + b.Cottage.Postal.ToString();
lblBookAlue.Text = region;
lblBookMaxPersons.Text = b.Cottage.Capacity.ToString() + " hlö";
lblBookCottagePrice.Text = b.Cottage.Price.ToString() + " €/yö";
lblBookBookingDateFrom.Text = b.Alkupv.ToString("yyyy-MM-dd");
lblBookBookingDateTo.Text = b.Loppupv.ToString("yyyy-MM-dd");
cottagepriodprice = b.Cottage.Price * ((b.Loppupv - b.Alkupv).TotalDays);
lblBookDays.Text = "(" + ((b.Loppupv - b.Alkupv).TotalDays).ToString() + " pv)";
lblBookSeasonPrice.Text = cottagepriodprice.ToString() + " €";
lblBookPriceFull.Text = cottagepriodprice.ToString() + " €";
MySqlDataAdapter sda = new MySqlDataAdapter("SELECT palvelu_id as 'ID', nimi as 'Nimi', kuvaus as 'Kuvaus', " +
"hinta as 'hinta/kpl', 0 as 'kpl' FROM palvelu WHERE toimintaalue_id LIKE '" + b.Cottage.RegionID + "'", ConnectionUtils.connection);
DataTable data = new DataTable();
sda.Fill(data);
dgvBookServices.DataSource = data;
dgvBookServices.Columns[0].Width = 26;
dgvBookServices.Columns[1].Width = 200;
dgvBookServices.Columns[2].Width = 320;
dgvBookServices.Columns[3].Width = 57;
dgvBookServices.Columns[4].Width = 30;
foreach (DataGridViewColumn dgvc in dgvBookServices.Columns) // Make all rows non editable
{
dgvc.ReadOnly = true;
dgvBookServices.Columns[4].DefaultCellStyle.BackColor = Color.PaleGreen;
}
dgvBookServices.Columns[4].ReadOnly = false; // Make editable only "kpl" row
}
public static string GetPostOffice(string postalCode) //returns the post office of the corresponding postal code
{
string postOffice = "";
ConnectionUtils.OpenConnection();
MySqlDataReader reader = null;
string query0 = "SELECT * FROM posti " +
"WHERE postinro LIKE '" + postalCode + "';";
MySqlCommand command0 = new MySqlCommand(query0, ConnectionUtils.connection);
reader = command0.ExecuteReader();
while (reader.Read())
{
postOffice = (string)reader["toimipaikka"];
}
reader.Close();
ConnectionUtils.CloseConnection();
return(postOffice);
}
private string MakeQueryBook()
{
if (customerid == 0) // If created new customer, get added cutomer_id
{
ConnectionUtils.OpenConnection();
MySqlCommand command = new MySqlCommand("SELECT asiakas_id FROM asiakas WHERE etunimi LIKE '" +
tbBookCustomerName.Text + "' AND sukunimi LIKE '" + tbBookCustomerLastname.Text + "' AND lahiosoite LIKE '" +
tbBookCustomerAddress.Text + "' AND postinro LIKE '" + tbBookCustomerPostnumber.Text + "' AND email LIKE '" +
tbBookCustomerEmail.Text + "' AND puhelinnro LIKE '" + tbBookCustomerPhone.Text + "'", ConnectionUtils.connection);
customerid = Convert.ToInt32(command.ExecuteScalar());
ConnectionUtils.CloseConnection();
}
string queryBook = "START TRANSACTION; " +
"INSERT INTO varaus(asiakas_id, mokki_mokki_id, varattu_pvm, vahvistus_pvm, varattu_alkupvm, varattu_loppupvm) " +
"VALUES('" + customerid + "', '" + lblBookCottageId.Text + "', '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
"', NULL, '" + lblBookBookingDateFrom.Text + " 16:00:00', '" + lblBookBookingDateTo.Text + " 12:00:00'); " +
"COMMIT;"; // Book start time always 16:00:00 and end time always 12:00:00
return(queryBook);
}
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();
}
}
///<summary>Updates the 'vahvistus_pvm' field of varaus and 'maksettu' field of lasku</summary>
public static void SetPaymentState(int lasku_id, string paymentDate)
{
ConnectionUtils.OpenConnection();
//get varaus_id
string query = "SELECT varaus_id " +
"FROM lasku " +
"WHERE lasku_id = " + lasku_id + ";";
MySqlCommand command = new MySqlCommand(query, ConnectionUtils.connection);
int varaus_id = Convert.ToInt32(command.ExecuteScalar());
//Set the date of payment
query = "START TRANSACTION; " +
"UPDATE varaus " +
"SET vahvistus_pvm = " + paymentDate + " " +
"WHERE varaus_id = " + varaus_id + "; " +
"COMMIT;";
command = new MySqlCommand(query, ConnectionUtils.connection);
command.ExecuteNonQuery();
//Update the state of payment
bool isPaid;
if (paymentDate.Equals("NULL"))
{
isPaid = false;
}
else
{
isPaid = true;
}
query = "START TRANSACTION; " +
"UPDATE lasku " +
"SET maksettu = " + isPaid + " " +
"WHERE varaus_id = " + varaus_id + "; " +
"COMMIT;";
command = new MySqlCommand(query, ConnectionUtils.connection);
command.ExecuteNonQuery();
ConnectionUtils.CloseConnection();
}
///<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 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);
}
}
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 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 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);
}
}