/* 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); }