/* 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();
}
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();
}
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);
}
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();
}
private void fill_All()
{
var adapter = new Database_adapter();
fill_TotalAccommodation(adapter);
calculate(adapter);
fill_Arriving(adapter);
fill_ArrivingNorway(adapter);
adapter.close();
}
/* 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();
}
/* 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));
}
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;
}
}
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();
}
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);
}
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> {0} </td> <td> Booking reference: </td> <td> {1} </td>
</tr>
<tr>
<td> Booker: </td> <td> {2} </td> <td> Number of guests: </td> <td> {3} </td>
</tr>
<tr>
<td> </td> <td> </td>
</tr>
", DateTime.Parse(bDate).ToString("dd.MM.yyy"), bid.ToString(), booker, persons);
if (name != "")
{
report += string.Format(@"
<tr>
<td> Booking name: </td> <td> {0} </td>
</tr>
", name);
}
if (email != "")
{
report += string.Format(@"
<tr>
<td> E-mail: </td> <td> {0} </td>
</tr>
", email);
}
if (tlf != "")
{
report += string.Format(@"
<tr>
<td> Phone: </td> <td> {0} </td>
</tr>
", tlf);
}
if (address != "")
{
report += string.Format(@"
<tr>
<td> Address: </td> <td> {0} </td>
</tr>
", address);
}
if (postnr != "" && pLocation != "")
{
report += string.Format(@"
<tr>
<td> Zip: </td> <td> {0} </td>
</tr>
", postnr + " " + pLocation);
}
if (country != "")
{
report += string.Format(@"
<tr>
<td> Country: </td> <td> {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> E-mail: <b>[email protected]</b>
</font>
</center>
</body>
</html>";
// END REPORT
adapter.close();
Cursor.Current = Cursors.Default;
return(report);
}
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;
}
private void Fast_check_in_FormClosing(object sender, FormClosingEventArgs e)
{
adapter.close();
}
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);
}
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);
}
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);
}
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);
}
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);
}