コード例 #1
0
        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
        }
コード例 #2
0
        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);
                }
            }
        }
コード例 #3
0
        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);
                }
            }
        }
コード例 #4
0
        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();
        }
コード例 #5
0
        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();
            }
        }
コード例 #6
0
        ///<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();
        }
コード例 #7
0
        ///<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();
        }
コード例 #8
0
        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();
                }
            }
        }
コード例 #9
0
        ///<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();
        }
コード例 #10
0
        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();
        }
コード例 #11
0
        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();
            }
        }
コード例 #12
0
        ///<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();
        }
コード例 #13
0
 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();
     }
 }
コード例 #14
0
        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
        }
コード例 #15
0
        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);
        }
コード例 #16
0
        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);
        }
コード例 #17
0
        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();
            }
        }
コード例 #18
0
        ///<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();
        }
コード例 #19
0
        ///<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();
        }
コード例 #20
0
        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);
            }
        }
コード例 #21
0
        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();
            }
        }
コード例 #22
0
        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)
            {
            }
        }
コード例 #23
0
 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);
     }
 }