Пример #1
0
        private void load_Attrebutes()
        {
            var adapter = new Database_adapter();

            // Rent object name and BID on current user
            rentObjects = adapter.get_Dict(@"select name, bid from rent_objects
                left join booking_lines on rent_objects.currentUser = booking_lines.blid;");

            // BID and customer name
            bookings = adapter.get_Dict(@"select bid, name from customers
                natural join bookings
                natural join booking_lines
                where blid in (select currentUser from rent_objects)
                group by bid;");

            // BID and number of persons
            numberOfPersons = adapter.get_Dict(@"select bid, persons from bookings
                natural join booking_lines
                where blid in (select currentUser from rent_objects)
                group by bid;");

            // BID and country
            country = adapter.get_Dict(@"select bid, country from customers
                natural join bookings
                natural join booking_lines
                where blid in (select currentUser from rent_objects)
                group by bid");

            // Departure day
            departure = adapter.get_Dict(@"select bid, max(endDate) from booking_lines
                where blid in (select currentUser from rent_objects)
                group by bid");
  
            adapter.close();
        }
Пример #2
0
        /* Remove button clicked */
        private void removeButton_Click(object sender, EventArgs e)
        {
            string blid = get_SelectedBlid();

            if (blid == null)
            {
                return;
            }

            var adapter = new Database_adapter();

            // Check out
            var checkedObjects = adapter.get_List(string.Format("select name from rent_objects where currentUser = {0};", blid));

            foreach (string name in checkedObjects)
            {
                adapter.set(string.Format("update rent_objects set currentUser = 0 where name = '{0}';", name));
            }

            adapter.set("delete from booking_entries where blid = " + blid + ";");
            adapter.set("delete from booking_lines where blid = " + blid + ";");

            adapter.close();

            fill_BookingLineTable();
            fill_Overview();
        }
Пример #3
0
        private bool cancel()
        {
            DialogResult dialogResult = MessageBox.Show("Are you sure you want to cancel this booking?", "Cancel?", MessageBoxButtons.YesNo);

            if (dialogResult == DialogResult.No)
            {
                return(false);
            }

            var adapter = new Database_adapter();

            var blidList = adapter.get_List(string.Format("select blid from booking_lines where bid = {0};", bid));

            foreach (string blid in blidList)
            {
                adapter.set(string.Format("delete from booking_entries where blid = {0};", blid));
            }

            adapter.set(string.Format("delete from booking_lines where bid = {0};", bid));
            adapter.set(string.Format("delete from bookings where bid = {0};", bid));
            adapter.set(string.Format("delete from alerts where bid = {0};", bid));

            adapter.close();

            return(true);
        }
Пример #4
0
        private void ObjectDeleter_Shown(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;

            var adapter = new Database_adapter();

            var roID  = adapter.get_Value(string.Format("select roID from rent_objects where name = '{0}';", name));
            var count = Int32.Parse(adapter.get_Value(string.Format("select count(name) name from rent_objects where roID = {0}", roID)));

            var dates = adapter.get_List(string.Format(@"
                select date 
                from booking_entries
                where roID = {0}
                and date >= '{1}'
                group by date
                order by date DESC; 
            ", roID, DateTime.Now.ToString("yyyy-MM-dd")));

            deleteBar.Maximum = dates.Count;
            deleteBar.Step    = 1;
            deleteBar.Value   = 0;

            var booked = "Could not delete object. Fully booked at:\r\n";

            var delete = true;

            foreach (var date in dates)
            {
                var bookedAtDate = adapter.get_Value(string.Format("select count(beID) from booking_entries where date = '{0}' and roID = {1};", DateTime.Parse(date).ToString("yyyy-MM-dd"), roID));

                if (count - Int32.Parse(bookedAtDate) <= 0)
                {
                    delete  = false;
                    booked += DateTime.Parse(date).ToString("dd.MM.yy, ");
                }

                deleteBar.Value += 1;
            }

            Cursor.Current = Cursors.Default;

            if (delete)
            {
                DialogResult dialogResult = MessageBox.Show(string.Format("Are you sure you want to delete {0}", name), "Delete?", MessageBoxButtons.YesNo);
                if (dialogResult == DialogResult.Yes)
                {
                    adapter.set(string.Format("delete from rent_objects where name = '{0}';", name));
                }
            }
            else
            {
                MessageBox.Show(booked);
            }

            adapter.close();
            this.Close();
        }
Пример #5
0
        private void fill_BookerBox(Database_adapter adapter)
        {
            var    query = string.Format("select booker from bookings where bid = {0};", bid);
            string data  = adapter.get_Value(query);

            if (data != null)
            {
                bookerBox.Text = data;
            }
        }
Пример #6
0
        private void fill_PersonsBox(Database_adapter adapter)
        {
            var    query = string.Format("select persons from bookings where bid = {0};", bid);
            string data  = adapter.get_Value(query);

            if (data != null && data != "")
            {
                nCustomers.Value = Int32.Parse(data);
            }
        }
Пример #7
0
        private void fill_postnrBox(Database_adapter adapter)
        {
            var    query = string.Format("select postnr from customers natural join bookings where bid = {0};", bid);
            string data  = adapter.get_Value(query);

            if (data != null)
            {
                postNrBox.Text = data;
            }
        }
Пример #8
0
        /* Delete Booking */
        private void button2_Click(object sender, EventArgs e)
        {
            // Check selection
            if (bookingsView.SelectedCells.Count < 1)
            {
                MessageBox.Show("No valid selection.");
                return;
            }

            // Get selected row
            DataGridViewRow selectedRow = bookingsView.Rows[bookingsView.SelectedCells[0].RowIndex];

            // Get selected Booking ID
            string bid = selectedRow.Cells["BID"].Value.ToString();

            // Ask user if data should be deleted
            DialogResult dialogResult = MessageBox.Show(string.Format("Are you sure you want to delete booking {0} ?", bid), "Delete?", MessageBoxButtons.YesNo);

            if (dialogResult == DialogResult.No)
            {
                return;
            }

            Cursor.Current = Cursors.WaitCursor;

            // Delete data
            var adapter = new Database_adapter();

            var blidList = adapter.get_List(string.Format("select blid from booking_lines where bid = {0};", bid));

            foreach (string blid in blidList)
            {
                adapter.set(string.Format("delete from booking_entries where blid = {0};", blid));

                // Check out
                var checkedObjects = adapter.get_List(string.Format("select name from rent_objects where currentUser = {0};", blid));
                foreach (string name in checkedObjects)
                {
                    adapter.set(string.Format("update rent_objects set currentUser = 0 where name = '{0}';", name));
                }
            }

            adapter.set(string.Format("delete from booking_lines where bid = {0};", bid));
            adapter.set(string.Format("delete from bookings where bid = {0};", bid));
            adapter.set(string.Format("delete from transfers where bid = {0};", bid));
            adapter.set(string.Format("delete from alerts where bid = {0};", bid));

            adapter.close();

            Cursor.Current = Cursors.Default;

            // Update table
            fill_Table();
        }
Пример #9
0
        private void fill_All()
        {
            var adapter = new Database_adapter();

            fill_TotalAccommodation(adapter);
            calculate(adapter);
            fill_Arriving(adapter);
            fill_ArrivingNorway(adapter);

            adapter.close();
        }
Пример #10
0
        /* Create new Booking with default customer */
        private int create_Booking()
        {
            var    adapter = new Database_adapter();
            var    query   = string.Format("insert into bookings values (NULL, {0}, '{1}', NULL, NULL, NULL, '{2}');select last_insert_id();", Database.DEFAULT_CUSTOMER.ToString(), DateTime.Now.ToString("yyyy-MM-dd"), Properties.Settings.Default.company);
            string newBID  = adapter.get_Value(query);

            if (newBID == null)
            {
                return(-1);
            }

            adapter.close();

            return(System.Int32.Parse(newBID));
        }
Пример #11
0
        private void fill_TotalAccommodation(Database_adapter adapter)
        {
            var num = adapter.get_Value(string.Format(@"
                select count(roID)
                from rent_object_types
                natural join booking_entries
                natural join booking_lines
                natural join bookings
                natural join customers
                where accommodation = 'true'
                and MONTH(date) = '{0}'
                and YEAR(date) = '{1}'
                and name != 'BLOKKERING'
                ", dateTimePicker1.Value.ToString("MM"), dateTimePicker1.Value.ToString("yyyy"))
                                        );

            numAccom.Text = num;
        }
Пример #12
0
 private void fill_info()
 {
     if (cid != Database.DEFAULT_CUSTOMER)
     {
         Cursor.Current = Cursors.WaitCursor;
         var adapter = new Database_adapter();
         fill_Name_Box(adapter);
         fill_EmailBox(adapter);
         fill_PhoneBox(adapter);
         fill_AddressBox(adapter);
         fill_postnrBox(adapter);
         fill_postLocationBox(adapter);
         fill_NoteBox(adapter);
         fill_BookerBox(adapter);
         fill_PersonsBox(adapter);
         adapter.close();
         Cursor.Current = Cursors.Default;
     }
 }
Пример #13
0
        private void saveButton_Click(object sender, EventArgs e)
        {
            string arrivaltime, departuretime, arrivalflight, departureflight;

            arrivaltime = departuretime = arrivalflight = departureflight = "NULL";

            if (arrivalOnly.Checked == true || both.Checked == true)
            {
                arrivaltime   = string.Format("'{0}'", arrivalDate.Value.ToString("yyyy-MM-dd") + arrivalTime.Value.ToString(" HH:mm") + ":00");
                arrivalflight = string.Format("'{0}'", (arrivalFlight.Text != "Arrival flight") ? arrivalFlight.Text : "");
            }

            if (departureOnly.Checked == true || both.Checked == true)
            {
                departuretime   = string.Format("'{0}'", departureDate.Value.ToString("yyyy-MM-dd") + departureTime.Value.ToString(" HH:mm") + ":00");
                departureflight = string.Format("'{0}'", (departureFlight.Text != "Departure flight") ? departureFlight.Text : "");
            }

            if (new_transfer)
            {
                var adapter = new Database_adapter();
                adapter.set(string.Format(@"
                    insert into transfers
                    values(NULL, {0}, {1}, {2}, {3}, {4}, {5});
                    ", bid, arrivaltime, arrivalflight, departuretime, departureflight, personsBox.Text)
                            );

                adapter.set(string.Format("insert into alerts values (NULL, {0}, 'transfer', '{1}');", bid, DateTime.Now.ToString("yyyy-MM-dd")));
                adapter.close();
            }
            else
            {
                Database.set(String.Format(@"
                    update transfers
                    set arrivalTime = {0}, arrivalFlight = {1}, departureTime = {2}, departureFlight = {3}, personsTransfer = {4}
                    where bid = {5};  
                    ", arrivaltime, arrivalflight, departuretime, departureflight, personsBox.Text, bid)
                             );
            }

            this.Close();
        }
Пример #14
0
        private void fill_Arriving(Database_adapter adapter)
        {
            var data = adapter.get_DataSet(string.Format(@"
                select persons, startDate
                from customers
                natural join bookings
                natural join booking_lines
                where country != 'norge'
                group by bid 
                having MONTH(startDate) = '{0}'
                and YEAR(startDate) = '{1}';
            ", dateTimePicker1.Value.ToString("MM"), dateTimePicker1.Value.ToString("yyyy")));

            int total = 0;

            foreach (DataRow row in data.Tables[0].Rows)
            {
                total += int.Parse(row["persons"].ToString());
            }

            arrivingBox.Text = total.ToString();
        }
Пример #15
0
        private bool cancel()
        {
            DialogResult dialogResult = MessageBox.Show("Are you sure you want to cancel this booking?", "Cancel?", MessageBoxButtons.YesNo);

            if (dialogResult == DialogResult.No)
            {
                return(false);
            }

            Cursor.Current = Cursors.WaitCursor;

            var adapter = new Database_adapter();

            var blidList = adapter.get_List(string.Format("select blid from booking_lines where bid = {0};", bid));

            foreach (string blid in blidList)
            {
                adapter.set(string.Format("delete from booking_entries where blid = {0};", blid));

                // Check out
                var checkedObjects = adapter.get_List(string.Format("select name from rent_objects where currentUser = {0};", blid));
                foreach (string name in checkedObjects)
                {
                    adapter.set(string.Format("update rent_objects set currentUser = 0 where name = '{0}';", name));
                }
            }

            adapter.set(string.Format("delete from booking_lines where bid = {0};", bid));
            adapter.set(string.Format("delete from bookings where bid = {0};", bid));
            adapter.set(string.Format("delete from transfers where bid = {0};", bid));
            adapter.set(string.Format("delete from alerts where bid = {0};", bid));

            adapter.close();

            Cursor.Current = Cursors.Default;

            return(true);
        }
Пример #16
0
        static public string booking_Confirmation(int bid, bool display_price = true)
        {
            Cursor.Current = Cursors.WaitCursor;
            var adapter = new Database_adapter();

            var data = adapter.get_DataSet(string.Format(@"
                select bookingDate, name, email, address, postnr, postlocation, phone, country, company, notes, persons, booker
                from bookings
                natural join customers
                where bid = {0};
            ", bid)).Tables[0].Rows[0];

            string bDate     = data[0].ToString();
            string name      = data[1].ToString();
            string email     = data[2].ToString();
            string address   = data[3].ToString();
            string postnr    = data[4].ToString();
            string pLocation = data[5].ToString();
            string tlf       = data[6].ToString();
            string country   = data[7].ToString();
            string company   = data[8].ToString();
            string note      = data[9].ToString().Replace("\n", "<br>");
            string persons   = data[10].ToString();
            string booker    = data[11].ToString();

            string price    = adapter.get_Value(string.Format("select sum(price) from booking_lines natural join booking_entries natural join rent_object_types where bid = {0};", bid));
            var    transfer = adapter.get_DataSet(string.Format(@"
                select arrivalTime, arrivalFlight, departureTime, departureFlight, personsTransfer
                from transfers
                where bid = {0}
                ;", bid));

            DataSet details = adapter.get_DataSet(string.Format(@"
                select description, startDate, endDate, count(beid) 
                from booking_lines 
                natural join booking_entries 
                natural join rent_object_types 
                where bid = {0} 
                group by blid;", bid)
                                                  );

            // BEGIN REPORT
            var report = @"
                <!DOCTYPE html>
                <html>
                <body>              
            ";

            if (company == "Kingfisher")
            {
                report += @"                
                    <style>
                    .logo {
                        float: left;
                        position: absolute;
                    }

                    .kingfisher {
                        text-align: right;
                    </style>             

                    <div class=""logo""> <img src=""http://www.arctic-seasport.no/img/logo_300.jpg"" alt=""Logo""> </div>
                    <div class=""kingfisher""> <img src=""http://www.kingfisher-angelreisen.de/fileadmin/templates_kingfisher-angelreisen.de/global_gfx/logo-kingfisher.png"" alt=""kingfisher"" height=""73""> </div>
                ";
            }
            else if (company == "Arctic-Adventure")
            {
                report += @"                
                    <style>
                    .logo {
                        float: left;
                        position: absolute;
                    }

                    .kingfisher {
                        text-align: right;
                    </style>             

                    <div class=""logo""> <img src=""http://www.arctic-seasport.no/img/logo_300.jpg"" alt=""Logo""> </div>
                    <div class=""kingfisher""> <img src=""http://www.arcticadventure.se/AA_logga_webb_wp.png"" alt=""kingfisher"" height=""73""> </div>
                ";
            }
            else if (company == "Angelreisen_Hamburg")
            {
                report += @"                
                    <style>
                    .logo {
                        float: left;
                        position: absolute;
                    }

                    .kingfisher {
                        text-align: right;
                    </style>             

                    <div class=""logo""> <img src=""http://www.arctic-seasport.no/img/logo_300.jpg"" alt=""Logo""> </div>
                    <div class=""kingfisher""> <img src=""https://www.angelreisen.de/fileadmin/ang/template/img/logo.png"" alt=""kingfisher"" height=""73""> </div>
                ";
            }
            else if (company == "Angelreisen_K-N")
            {
                report += @"                
                    <style>
                    .logo {
                        float: left;
                        position: absolute;
                    }

                    .kingfisher {
                        text-align: right;
                    </style>             

                    <div class=""logo""> <img src=""http://www.arctic-seasport.no/img/logo_300.jpg"" alt=""Logo""> </div>
                    <div class=""kingfisher""> <img src=""http://www.arctic-seasport.no/img/angelreisen_k-n.jpg"" alt=""kingfisher"" height=""73""> </div>
                ";
            }
            else
            {
                report += @"
                    <img src=""http://www.arctic-seasport.no/img/logo_300.jpg"" alt=""Logo"">                    
                ";
            }


            report += string.Format(@"
                <br>
                <font size=""6"" face=""calibri""> 
                    <b>Booking confirmation</b> 
                </font>

                <br>

                    <font size=""3.5"" face=""calibri"">
                    <table cellspacing=""0"">
                        <tr>
                            <td> Booking date: </td>  <td> &nbsp; {0} </td> <td> &nbsp; Booking reference: </td> <td> &nbsp; {1} </td>
                        </tr>

                        <tr>
                            <td> Booker: </td> <td> &nbsp; {2} </td> <td> &nbsp; Number of guests: </td> <td> &nbsp; {3} </td>
                        </tr>
                        <tr>
                            <td> &nbsp; </td> <td> &nbsp; </td>
                        </tr>

                        ", DateTime.Parse(bDate).ToString("dd.MM.yyy"), bid.ToString(), booker, persons);

            if (name != "")
            {
                report += string.Format(@"
                    <tr>
                        <td> Booking name: </td>  <td> &nbsp; {0} </td>
                    </tr>        
                ", name);
            }

            if (email != "")
            {
                report += string.Format(@"
                    <tr>
                        <td> E-mail: </td>  <td> &nbsp; {0} </td>
                    </tr>        
                ", email);
            }

            if (tlf != "")
            {
                report += string.Format(@"
                    <tr>
                        <td> Phone: </td>  <td> &nbsp; {0} </td>
                    </tr>        
                ", tlf);
            }

            if (address != "")
            {
                report += string.Format(@"
                    <tr>
                        <td> Address: </td>  <td> &nbsp; {0} </td>
                    </tr>        
                ", address);
            }

            if (postnr != "" && pLocation != "")
            {
                report += string.Format(@"
                    <tr>
                        <td> Zip: </td>  <td> &nbsp; {0} </td>
                    </tr>        
                ", postnr + " " + pLocation);
            }

            if (country != "")
            {
                report += string.Format(@"
                    <tr>
                        <td> Country: </td>  <td> &nbsp; {0} </td>
                    </tr>        
                ", country);
            }

            report += @"
                </table>
                </font>
                <br>                    
                <font size=""4"" face=""calibri"">
                        <b>Details</b>
                    </font>"
            ;

            // Booking lines
            report += @"
                    <style>
                    table {
                        width:100%;
                    }
                    table, th, td {
                        border-collapse: collapse;
                    }

                    th, td {
                        padding: 5px;
                        text-align: left;
                    }

                    table th {
                        border-bottom: 1px solid #444092;
                    }

                    </style>

                    <font face=""calibri"">
                    <table>
                      <tr>
                        <th>Object</th>
                        <th>From</th>
                        <th>To</th>
                        <th>Days</th>
                      </tr>                    
            ";

            DataTable table = details.Tables[0];

            foreach (DataRow row in table.Rows)
            {
                report += string.Format("<tr> <td> {0} </td> <td> {1} </td> <td> {2} </td> <td> {3} </td> </tr>", row[0], ((DateTime)row[1]).ToString("dd.MM.yyyy"), ((DateTime)row[2]).ToString("dd.MM.yyyy"), row[3]);
            }

            report += "</table> </font>";

            // Price
            if (display_price)
            {
                report += string.Format(@"
                    <br>
                    <font size=""3"" face=""calibri"">                  
                        <div align=""left""> SUM: NOK {0},- </div>
                    </font>  
                ", price);
            }


            // Transfer
            if (transfer.Tables[0].Rows.Count > 0)
            {
                report += string.Format(@"
                    <br>
                    <br>

                    <font size=""4"" face=""calibri"">
                        <b>Transfer</b>
                    </font>

                    <font face=""calibri"">
                    <table id='t01'>
                        <tr>
                        <th>Arrival</th>
                        <th>Flight</th>
                        <th>Departure</th>
                        <th>Flight</th> 
                        <th>Persons</th>                     
                        </tr>
                ");

                table = transfer.Tables[0];
                foreach (DataRow row in table.Rows)
                {
                    report += string.Format(@"<tr> <td> {0} </td> <td> {1} </td> <td> {2} </td> <td> {3} </td> <td> {4} </td> </tr>
                                            ", (row[0].ToString() != "") ? DateTime.Parse(row[0].ToString()).ToString("dd.MM.yyyy HH:mm") : "", row[1], (row[2].ToString() != "") ? DateTime.Parse(row[2].ToString()).ToString("dd.MM.yyyy HH:mm") : "", row[3], row[4]);
                }

                report += "</table> </font>";
            }


            // Notes
            if (note != "")
            {
                report += string.Format(@"
                    <br>
                    <br>                      

                    <font size=""4"" face=""calibri"">
                        <b>Notes</b>
                    </font>

                    <hr color=""#444092"" size=""1px"">                     

                    <font size=""3"" face=""calibri"">
                        {0}
                    </font>
                ", note);
            }


            // Footer
            //<hr color=""#eff181"" size=""1px"">
            //<hr color = ""#444092"" size=""1px"">
            report += @"            
                <br>
                <br>
                <br>
                <br>


                
                
                <center>
                    <font size=""3"" face=""calibri"" color=""#444092"">
                        <b>Check inn: 14:00 - 23:00 <br>
                        Check out: before 12:00 <br></b>
                        <br>
                    </font>
                        
                        <font size=""3"" face=""calibri"">
                        Please contact us in advance if your arrival is later than 23:00.                    
                        Bed sheets and towels can be rented at Arctic Seasport.
                        For more information please visit <b>www.arctic-seasport.no</b>.

                        
        
                        


                        <hr color=""#444092"" size=""1px"">
                        <br>
                    

                        Nord-Norsk sjøsportsenter AS, Naurstad 8050 Tverlandet, Norway <br>
                        Phone: <b>+47 916 05 007</b> &nbsp; E-mail: <b>[email protected]</b>
                        


                    </font>
                </center>

                </body>
                </html>";
            // END REPORT


            adapter.close();
            Cursor.Current = Cursors.Default;

            return(report);
        }
Пример #17
0
        private void fill_Overview()
        {
            progressBar1.Value = 0;
            progressBar1.Step  = 1;

            Cursor.Current = Cursors.WaitCursor;
            Database_adapter db = new Database_adapter();

            dataGridView1.DataSource = null;
            var dates = get_Dates();
            var types = db.get_Dict("select Description, count(roID) from rent_objects natural join rent_object_types group by roID;");

            if (dates == null || types == null)
            {
                MessageBox.Show("Value error.");
                Cursor.Current = Cursors.Default;
                return;
            }

            DataTable table = new DataTable();

            foreach (var type in types)
            {
                var row = table.NewRow();
                table.Rows.Add(row);
            }

            progressBar1.Maximum = dates.Count;

            foreach (var date in dates)
            {
                progressBar1.Increment(1);

                var column = new DataColumn();
                column.DataType   = System.Type.GetType("System.Int32");
                column.ColumnName = date.ToString(collumnFormat);
                table.Columns.Add(column);

                var booked = db.get_Dict(string.Format("select Description, count(roID) from booking_entries natural join rent_object_types where date = \'{0}\' group by Description;", date.ToString("yyyy-MM-dd")));

                var i = 0;
                foreach (KeyValuePair <string, string> entry in types)
                {
                    if (booked.ContainsKey(entry.Key))
                    {
                        var booked_count = booked[entry.Key];
                        table.Rows[i][date.ToString(collumnFormat)] = Int32.Parse(entry.Value) - Int32.Parse(booked_count);
                    }
                    else
                    {
                        table.Rows[i][date.ToString(collumnFormat)] = Int32.Parse(entry.Value);
                    }
                    i++;
                }
            }

            db.close();
            dataGridView1.DataSource = table;

            var all_types = types.GetEnumerator();

            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                all_types.MoveNext();
                row.HeaderCell.Value = all_types.Current.Key;
            }

            // Set not sortable
            foreach (DataGridViewColumn col in dataGridView1.Columns)
            {
                col.SortMode = DataGridViewColumnSortMode.NotSortable;
            }

            set_Color();

            dataGridView1.AutoResizeColumns();
            dataGridView1.RowHeadersWidth = 200;
            //dataGridView1.AutoSize = true;

            dataGridView1.ClearSelection();
            dataGridView1.Show();
            Cursor.Current     = Cursors.Default;
            progressBar1.Value = 0;
        }
Пример #18
0
        private void calculate(Database_adapter adapter)
        {
            var data = adapter.get_DataSet(string.Format(@"
                select beid, date, bid, persons, country 
                from customers 
                natural join bookings 
                natural join booking_lines 
                natural join booking_entries
                natural join rent_object_types
                where MONTH(date) = '{0}'
                and YEAR(date) = '{1}'
                and accommodation = 'true'
                and name != 'BLOKKERING';
            ", dateTimePicker1.Value.ToString("MM"), dateTimePicker1.Value.ToString("yyyy")));

            List <DataRow> duplicates = new List <DataRow>();

            // Find all duplicates in data set
            foreach (DataRow rowA in data.Tables[0].Rows)
            {
                foreach (DataRow rowB in data.Tables[0].Rows)
                {
                    if (duplicates.Contains(rowA))
                    {
                        continue;
                    }

                    if (rowA == rowB)
                    {
                        continue;
                    }

                    if (rowA["bid"].ToString() == rowB["bid"].ToString() && rowA["date"].ToString() == rowB["date"].ToString())
                    {
                        if (!duplicates.Contains(rowB))
                        {
                            duplicates.Add(rowB);
                        }
                    }
                }
            }

            var countries = new Dictionary <string, int>();

            int guests = 0;

            // Count all guests and skip duplicates
            foreach (DataRow row in data.Tables[0].Rows)
            {
                if (duplicates.Contains(row))
                {
                    continue;
                }

                guests += int.Parse(row["persons"].ToString());

                if (!countries.ContainsKey(row["country"].ToString()))
                {
                    countries.Add(row["country"].ToString(), 0);
                }

                countries[row["country"].ToString()] += int.Parse(row["persons"].ToString());
            }

            totalGuests.Text = guests.ToString();

            // Sort data
            var ds = countries.ToList();

            ds.Sort((x, y) => y.Value.CompareTo(x.Value));

            dataView.DataSource = ds.ToArray();
            dataView.AutoResizeColumns();
            dataView.ClearSelection();
        }
Пример #19
0
        static public string booking_Confirmation(int bid, bool display_price = true)
        {
            Cursor.Current = Cursors.WaitCursor;
            var adapter = new Database_adapter();

            var data = adapter.get_DataSet(string.Format(@"
                select bookingDate, name, email, address, postnr, postlocation, phone, country, company, notes, persons
                from bookings
                natural join customers
                where bid = {0};
            ", bid)).Tables[0].Rows[0];

            string bDate     = data[0].ToString();
            string name      = data[1].ToString();
            string email     = data[2].ToString();
            string address   = data[3].ToString();
            string postnr    = data[4].ToString();
            string pLocation = data[5].ToString();
            string tlf       = data[6].ToString();
            string country   = data[7].ToString();
            string company   = data[8].ToString();
            string note      = data[9].ToString().Replace("\n", "<br>");
            string persons   = data[10].ToString();

            var transfer = adapter.get_DataSet(string.Format(@"
                select arrivalTime, arrivalFlight, departureTime, departureFlight, personsTransfer
                from transfers
                where bid = {0}
                ;", bid));


            DataSet details = adapter.get_DataSet(string.Format("select description, startDate, endDate from booking_lines natural join booking_entries natural join rent_object_types where bid = {0} group by blid;", bid));

            // BEGIN REPORT
            var report = @"
                <!DOCTYPE html>
                <html>
                <body> 

                <style>
                .logo {
                    float: left;
                    position: absolute;
                }

                .kingfisher {
                    text-align: right;
                </style>             
                <div class=""logo""> <img src=""http://www.arctic-seasport.no/img/logo_300.jpg"" alt=""Logo""> </div>
            ";



            report += string.Format(@"
                <div class=""kingfisher""> <img src=""{0}"" alt=""kingfisher"" height=""73""> </div>
            ", Properties.Settings.Default.logo_src);

            //report += "<br><br><br><br>";

            report += @"
                <br>
                
                <font size=""6""> Booking </font>    

                <br>
                <br
                <br>           
            ";

            report += string.Format(@"
                <font size=""4"">
                    Date: {0} <br>
                    Booking reference: {1} <br>
                    Persons: {2} <br>
                    <br>
                    {3} 
                    {4} 
                    {5}
                    {6}
                    {7}
                    {8}
                </font>            

                <br>
            
                <font size=""5"">
                    Details
                </font>
               
                ", DateTime.Parse(bDate).ToString("dd.MM.yyy"),
                                    bid.ToString(), persons, (name != "") ? name + "<br>" : "",
                                    (email != "") ? email + "<br>" : "",
                                    (tlf != "") ? tlf + "<br>" : "",
                                    (address != "") ? address + "<br>" : "",
                                    (postnr != "" && pLocation != "") ? postnr + " " + pLocation + "<br>" : "",
                                    (country != "") ? country + "<br>" : "");

            // Booking lines
            report += @"
                    <style>
                    table {
                        width:100%;
                    }
                    table, th, td {
                        border-collapse: collapse;
                    }

                    th, td {
                        padding: 5px;
                        text-align: left;
                    }

                    table th {
                        border-bottom: 1px solid black;
                    }

                    </style>


                    <table>
                      <tr>
                        <th>Object</th>
                        <th>From</th>
                        <th>To</th>
                      </tr>                    
            ";

            DataTable table = details.Tables[0];

            foreach (DataRow row in table.Rows)
            {
                report += string.Format("<tr> <td> {0} </td> <td> {1} </td> <td> {2} </td> </tr>", row[0], ((DateTime)row[1]).ToString("dd.MM.yyyy"), ((DateTime)row[2]).ToString("dd.MM.yyyy"));
            }

            report += "</table>";

            // Price

            /*if (display_price)
             * {
             *  report += string.Format(@"
             *      <br>
             *      <font size=""4"">
             *          <div align=""left""> SUM: NOK {0},- </div>
             *      </font>
             *  ", price);
             * }*/


            // Transfer
            if (transfer.Tables[0].Rows.Count > 0)
            {
                report += string.Format(@"
                    <br>
                    <br>

                    <font size=""5"">
                        Transfer
                    </font>

                    <table id='t01'>
                        <tr>
                        <th>Arrival</th>
                        <th>Flight</th>
                        <th>Departure</th>
                        <th>Flight</th> 
                        <th>Persons</th>                     
                        </tr>
                ");

                table = transfer.Tables[0];
                foreach (DataRow row in table.Rows)
                {
                    report += string.Format(@"<tr> <td> {0} </td> <td> {1} </td> <td> {2} </td> <td> {3} </td> <td> {4} </td> </tr>
                                            ", (row[0].ToString() != "") ? DateTime.Parse(row[0].ToString()).ToString("dd.MM.yyyy HH:mm") : "", row[1], (row[2].ToString() != "") ? DateTime.Parse(row[2].ToString()).ToString("dd.MM.yyyy HH:mm") : "", row[3], row[4]);
                }

                report += "</table>";
            }


            // Notes
            if (note != "")
            {
                report += string.Format(@"
                    <br>
                    <br>                      

                    <font size=""5"">
                        Notes
                    </font>

                    <hr>                      

                    <font size=""4"">
                        {0}
                    </font>
                ", note);
            }


            // Footer
            report += @"            
                <br>
                <br>
                <br>       
                <br>
                <br>
                <br>                

                <font size=""4"">
                    Arctic Seasport AS <br>
                    Naurstad <br>
                    8050 Tverlandet<br>
                    [email protected] <br>
                    +47 916 05 007
                </font>

                </body>
                </html>";
            // END REPORT


            adapter.close();
            Cursor.Current = Cursors.Default;

            return(report);
        }
Пример #20
0
 public Fast_check_in(string booking_line)
 {
     blid = booking_line;
     InitializeComponent();
     adapter = new Database_adapter();
 }
Пример #21
0
        static public string upcoming_Bookings()
        {
            Cursor.Current = Cursors.WaitCursor;
            var adapter = new Database_adapter();

            var report = @"
                <!DOCTYPE html>
                <html>
                <body>

                <style>
                .logo {
                    float: left;
                    position: absolute;
                }

                .kingfisher {
                    text-align: right;
                </style>             
    
                <div class=""logo""> <img src=""http://www.arctic-seasport.no/img/logo_300.jpg"" alt=""Logo""> </div>
            ";

            //report += "<br><br><br><br><br>";

            report += string.Format(@"
                <div class=""kingfisher""> <img src=""{0}"" alt=""kingfisher"" height=""73""> </div>
            ", Properties.Settings.Default.logo_src);

            report += @"
                <style>
                table {
                    width:100%;
                }
                table, th, td {
                    border-collapse: collapse;
                }

                th, td {
                    padding: 5px;
                    text-align: left;
                }

                table th {
                    border-bottom: 1px solid black;
                }
                </style>
            ";

            report += string.Format(@"

                <table id='t01'>
                    <tr>
                    <th>BID</th>
                    <th>Name</th>
                    <th>Persons</th>  
                    <th>From</th>
                    <th>To</th>
                    <th>Type</th>
                    <th>Transfer A.</th>
                    <th>Transfer D.</th> 
                    <th>Notes</th>                                       
                    </tr>
            ");

            var lines = adapter.get_DataSet(string.Format(@"
                select bookings.bid, name, persons, startDate, endDate, description, company, arrivalTime, departureTime, notes
                from customers
                natural join bookings
                natural join booking_lines
                natural join booking_entries
                natural join rent_object_types
                left outer join transfers on transfers.bid = bookings.bid
                where date >= '{0}'
                and company = '{1}'
                group by blid
                order by startDate, bookings.bid;
                ", DateTime.Now.ToString("yyyy-MM-dd"), Properties.Settings.Default.company)
                                            );

            DataTable table = lines.Tables[0];

            foreach (DataRow row in table.Rows)
            {
                report += string.Format(@"<tr> <td> {0} </td> <td> {1} </td> <td> {2} </td> <td> {3} </td> <td> {4} </td> <td> {5} </td> <td> {6} </td> <td> {7} </td> <td> {8} </td> </tr>
                                            ", row[0], row[1], row[2], DateTime.Parse(row[3].ToString()).ToString("dd.MM.yyyy"), DateTime.Parse(row[4].ToString()).ToString("dd.MM.yyyy"), row[5], (row[7].ToString() != "") ? DateTime.Parse(row[7].ToString()).ToString("HH:mm") : "-", (row[8].ToString() != "") ? DateTime.Parse(row[8].ToString()).ToString("HH:mm") : "-", row[9].ToString().Replace("\n", "<br>"));
            }

            report += "</table>";

            report += @"
                </body>
                </html>
            ";

            adapter.close();
            Cursor.Current = Cursors.Default;

            return(report);
        }
Пример #22
0
        static public string upcoming_Bookings()
        {
            Cursor.Current = Cursors.WaitCursor;
            var adapter = new Database_adapter();
            var total   = adapter.get_Value(string.Format(@"
                select count(DISTINCT bid) 
                from bookings
                natural join booking_lines
                natural join booking_entries                
                where date >= '{0}';
            ", DateTime.Now.ToString("yyyy-MM-dd")));

            var report = @"
                <!DOCTYPE html>
                <html>
                <body>

                <img src=""http://www.arctic-seasport.no/img/logo_300.jpg"" alt=""Logo"">
                <br>
            ";

            report += string.Format(@"
                <font size=""4"" face=""calibri""> <b>Bookings total: {0}</b> </font>
            ", total);

            report += @"

                <style>
                table {
                    width:100%;
                }
                table, th, td {
                    border-collapse: collapse;
                }

                th, td {
                    padding: 5px;
                    text-align: left;
                }

                table th {
                    border-bottom: 1px solid black;
                }
                </style>
            ";

            report += string.Format(@"
                
                <font size=""3"" face=""calibri"">
                <table id='t01'>
                    <tr>
                    <th>BID</th>
                    <th>Name</th>
                    <th>Persons</th>  
                    <th>From</th>
                    <th>To</th>
                    <th>Type</th>
                    <th>Agent</th>
                    <th>Transfer A.</th>
                    <th>Transfer D.</th> 
                    <th>Notes</th>                                       
                    </tr>
            ");

            var lines = adapter.get_DataSet(string.Format(@"
                select bookings.bid, name, persons, startDate, endDate, description, company, arrivalTime, departureTime, notes
                from customers
                natural join bookings
                natural join booking_lines
                natural join booking_entries
                natural join rent_object_types
                left outer join transfers on transfers.bid = bookings.bid
                where date >= '{0}'
                group by blid
                order by startDate, bookings.bid;
                ", DateTime.Now.ToString("yyyy-MM-dd"))
                                            );

            DataTable table = lines.Tables[0];

            foreach (DataRow row in table.Rows)
            {
                report += string.Format(@"<tr> <td> {0} </td> <td> {1} </td> <td> {2} </td> <td> {3} </td> <td> {4} </td> <td> {5} </td> <td> {6} </td> <td> {7} </td> <td> {8} </td> <td> {9} </td> </tr>
                                            ", row[0], row[1], row[2], DateTime.Parse(row[3].ToString()).ToString("dd.MM.yyyy"), DateTime.Parse(row[4].ToString()).ToString("dd.MM.yyyy"), row[5], row[6], (row[7].ToString() != "") ? DateTime.Parse(row[7].ToString()).ToString("HH:mm") : "-", (row[8].ToString() != "") ? DateTime.Parse(row[8].ToString()).ToString("HH:mm") : "-", row[9].ToString().Replace("\n", "<br>"));
            }

            report += "</table> </font>";

            report += @"
                </body>
                </html>
            ";

            adapter.close();
            Cursor.Current = Cursors.Default;

            return(report);
        }
Пример #23
0
        static public string transfers()
        {
            Cursor.Current = Cursors.WaitCursor;
            var adapter = new Database_adapter();

            var report = @"
                <!DOCTYPE html>
                <html>
                <body>

                <style>
                .arrival {
                    text-align: center;

                }
                .logo {
                    float: left;
                    position: absolute;
                }
                
                </style>

                <div class=""logo""> <img src=""http://www.arctic-seasport.no/img/logo_300.jpg"" alt=""Logo""> </div>
                <div class=""arrival""> <img src=""http://www.arctic-seasport.no/img/transfer.jpg"" alt=""arrival"" height=""60"" width=""101""> </div>
                <br>

                <font size=""6"" face=""calibri""> <b>Transfers</b> </font>

                    <style>
                    table {
                        width:100%;
                    }
                    table, th, td {
                        border-collapse: collapse;
                    }

                    th, td {
                        padding: 5px;
                        text-align: left;
                    }

                    table th {
                        border-bottom: 1px solid black;
                    }
                    </style>
            ";

            report += string.Format(@"
                <br>
                <br>

                <font size=""3"" face=""calibri"">
                <table id='t01'>
                    <tr>
                    <th>BID</th>
                    <th>Name</th>
                    <th>Arrival</th>
                    <th>Flight</th>
                    <th>Departure</th>
                    <th>Flight</th> 
                    <th>Persons</th>                     
                    </tr>
            ");

            var lines = adapter.get_DataSet(string.Format(@"
                Select bid, name, arrivalTime, arrivalFlight, departureTime, departureFlight, personsTransfer
                from transfers
                natural join bookings
                natural join customers
                where arrivalTime >= '{0}'
                or departureTime >= '{0}'
                order by arrivalTime
                ;", DateTime.Now.ToString("yyyy-MM-dd")));

            DataTable table = lines.Tables[0];

            foreach (DataRow row in table.Rows)
            {
                report += string.Format(@"<tr> <td> {0} </td> <td> {1} </td> <td> {2} </td> <td> {3} </td> <td> {4} </td> <td> {5} </td> <td> {6} </td> </tr>
                                            ", row[0], row[1], (row[2].ToString() != "") ? DateTime.Parse(row[2].ToString()).ToString("dd.MM.yyyy HH:mm") : "", row[3], (row[4].ToString() != "") ? DateTime.Parse(row[4].ToString()).ToString("dd.MM.yyyy HH:mm") : "", row[5], row[6]);
            }

            report += "</table> </font>";

            report += @"
                </body>
                </html>
            ";

            adapter.close();
            Cursor.Current = Cursors.Default;

            return(report);
        }
Пример #24
0
        static public string departures(int numberOfDays)
        {
            Cursor.Current = Cursors.WaitCursor;
            var adapter = new Database_adapter();

            var report = @"
                <!DOCTYPE html>
                <html>
                <body>

                <style>
                .arrival {
                    text-align: center;

                }
                .logo {
                    float: left;
                    position: absolute;
                }
                
                </style>

                <div class=""logo""> <img src=""http://www.arctic-seasport.no/img/logo_300.jpg"" alt=""Logo""> </div>
                <div class=""arrival""> <img src=""http://www.arctic-seasport.no/img/departure.jpg"" alt=""arrival"" height=""60"" width=""85""> </div>
                <br>

                <font size=""6"" face=""calibri""> <b>Departures</b> </font>

                    <style>
                    table {
                        width:100%;
                    }
                    table, th, td {
                        border-collapse: collapse;
                    }

                    th, td {
                        padding: 5px;
                        text-align: left;
                    }

                    table th {
                        border-bottom: 1px solid black;
                    }
                    </style>
            ";

            DateTime date = DateTime.Now;

            for (int i = 0; i < numberOfDays; i++)
            {
                var nextDay = string.Format(@"
                    <br>
                    <br>

                    <font size='4' face=""calibri""> <b>{0}</b> </font>

                    <font size='3' face=""calibri"">
                    <table id='t01'>
                      <tr>
                        <th>BID</th>
                        <th>Object</th>
                        <th>Description</th>
                        <th>Name</th>
                        <th>Ppl.</th>
                        <th>Country</th> 
                        <th>Notes</th>  
                        <th>Transfer</th>                     
                      </tr>
                ", first_To_Upper(date.AddDays(i).ToString("dddd dd.MM.yyy")));

                var lines = adapter.get_DataSet(string.Format(@"
                    select blid, endDate, bookings.bid, description, name, persons, country, notes, DATE_FORMAT(departureTime, '%k:%i')
                    from customers
                    natural join bookings
                    natural join booking_lines
                    natural join booking_entries
                    natural join rent_object_types
                    left outer join transfers on transfers.bid = bookings.bid
                    group by blid
                    having endDate = '{0}'
                    order by bid
                    ;", date.AddDays(i).ToString("yyyy-MM-dd")));

                DataTable table = lines.Tables[0];
                if (table.Rows.Count == 0)
                {
                    continue;
                }

                foreach (DataRow row in table.Rows)
                {
                    string ro = adapter.get_Value(string.Format("select name from rent_objects where currentUser = {0};", row[0]));
                    if (ro == null || ro == "")
                    {
                        ro = "-";
                    }
                    nextDay += string.Format(@"<tr> <td> {0} </td> <td> {1} </td> <td> {2} </td> <td> {3} </td> <td> {4} </td> <td> {5} </td> <td> {6} </td> <td> {7} </td> </tr>
                                                ", row[2], ro, row[3], row[4], row[5], row[6], row[7].ToString().Replace("\n", "<br>"), (row[8].ToString() == "") ? "-" : row[8]);
                }

                report += nextDay;
                report += "</table> </font>";
            }

            report += @"
                </body>
                </html>
            ";

            adapter.close();
            Cursor.Current = Cursors.Default;

            return(report);
        }