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 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; }