the code below is working the last think what I would appreciate is layout of this empty column
I try to answer to your appeal with the code that follows
@{
var db = Database.Open("tipy");
// convert IEnumerable to list
var selectedData = db.Query("SELECT * FROM Itinerary ORDER BY Date, Country").ToList();
}
<h1>Plan</h1>
<table border="1">
<thead>
<tr>
<th>Date</th>
<th>Country</th>
<th>City</th>
</tr>
</thead>
<tbody>
@{
// create a complementary list for the table layout
List<int> list = new List<int>();
DateTime prevDate = DateTime.MinValue;
string prevCountry = "";
int count = 0;
// scan the table rows and populate the complementary list
foreach(var row in selectedData)
{
// if is the first row
if (prevDate == DateTime.MinValue){
prevDate = row.Date;
prevCountry = row.Country;
count = 1;
// for the following rows
} else {
// if Date and Country are the same
if (row.Date == prevDate && row.Country == prevCountry){
// increase counter
count++;
// else populate complementary list
} else {
list.Add(count);
for (int i = 1; i < count; i++) {
list.Add(0);
}
prevDate = row.Date;
prevCountry = row.Country;
count = 1;
}
}
}
// if is the last row
list.Add(count);
for (int i = 1; i < count; i++) {
list.Add(0);
}
// build the table
var tblRow = "";
for (int i = 0; i < list.Count; i++)
{
// read the row type from the complementary list
switch(list[i])
{
// if is a row with only the City field
case 0:
tblRow = "<tr>";
break;
// if is a row without duplicated Date-Country
case 1:
tblRow = "<tr><td>" + selectedData[i].Date + "</td><td>" +
selectedData[i].Country + "</td>";
break;
// if is a row with duplicated Date-Country
default:
tblRow = "<tr><td rowspan='" + list[i] + "'>" + selectedData[i].Date +
"</td><td rowspan='" + list[i] + "'>" + selectedData[i].Country + "</td>";
break;
}
// display the row
tblRow += "<td>" + selectedData[i].City + "</td></tr>";
<text>@Html.Raw(tblRow)</text>
}
}
</tbody>
</table>
My approach is a little tricky, because is based on a complementary list that is populated with the row type of the table: if the row has
n following rows with the same Date-Country data, the value for this row in the complementary list is equal to
n+1, if the row hasn't rows like it is equal to 1, if the row is part of a group of row with same Date-Country data is equal to 0.
comp.list
date
country
city
2
27/12/2012
Europe
London
0
27/12/2012
Europe
Milan
1
27/12/2012
Mexico
Mexico City
3
27/12/2012
USA
New York
0
27/12/2012
USA
New Jersey
0
27/12/2012
USA
San Francisco
I have made only a short test, but it worked on my system: try it.
I have rewritten the page moving the code into an helper that takes care of the data convertion from IEnumerable to List, too.
@helper MergedRows(IEnumerable<dynamic> rows){
List<dynamic> data = rows.ToList();
DateTime prevDate = DateTime.MinValue;
string prevCountry = "";
int count = 0;
// create a complementary list for the table layout
List<int> list = new List<int>();
// scan the table rows and populate the complementary list
foreach(var row in data)
{
// if is the first row
if (prevDate == DateTime.MinValue){
prevDate = row.Date;
prevCountry = row.Country;
count = 1;
// for the following rows
} else {
// if Date and Country are the same
if (row.Date == prevDate && row.Country == prevCountry){
// increase counter
count++;
// else populate complementary list
} else {
list.Add(count);
for (int i = 1; i < count; i++) {
list.Add(0);
}
prevDate = row.Date;
prevCountry = row.Country;
count = 1;
}
}
}
// if is the last row
list.Add(count);
for (int i = 1; i < count; i++) {
list.Add(0);
}
// build the table
var tblRow = "";
for (int i = 0; i < list.Count; i++)
{
// read the row type from the complementary list
switch(list[i])
{
// if is a row with only the City field
case 0:
tblRow = "<tr>";
break;
// if is a row without duplicated Date-Country
case 1:
tblRow = "<tr><td>" + data[i].Date + "</td><td>" +
data[i].Country + "</td>";
break;
// if is a row with duplicated Date-Country
default:
tblRow = "<tr><td rowspan='" + list[i] + "'>" + data[i].Date +
"</td><td rowspan='" + list[i] + "'>" + data[i].Country + "</td>";
break;
}
// display the row
tblRow += "<td>" + data[i].City + "</td></tr>";
<text>@Html.Raw(tblRow)</text>
}
}
@{
var db = Database.Open("tipy");
var selectedData = db.Query("SELECT * FROM Itinerary ORDER BY Date, Country");
}
<h1>Plan</h1>
<table border="1">
<thead>
<tr>
<th>Date</th>
<th>Country</th>
<th>City</th>
</tr>
</thead>
<tbody>
@MergedRows(selectedData)
</tbody>
</table>
thanks, I am facing last problem in this case and this is:
when I am trying to display only current day, if there is any Itinerary on current day.
In case I have some records in actual date I get it visible, but if there is no records I get this error
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
my sql command is:
SELECT convert(nvarchar(10), Date,104) as Date, Country, City FROM Itinerary where convert(nvarchar(10), Date,104)=convert(nvarchar(10), getdate (),104)
In case I have some records in actual date I get it visible, but if there is no records I get this error
You should test if there aren't any records that match your condition.
If you want to accomplish this with the helper, you can insert an if statement at its beginning leaving the original helper code inside the else block, something like:
@helper MergedRows(IEnumerable<dynamic> rows){
if (rows.Count() == 0)
{
<tr><td colspan="3">There aren't any Itineraries</td></tr>
} else {
List<dynamic> data = rows.ToList();
/* here the remainder of the helper */
}
I know I asked a lot of question, but I had one more.
Is it possible to add column to table, where I will see flag of the country and is possible to set background color of rows where is USA to red and where is Europe to blue?
Is it possible to add column to table, where I will see flag of the country and is possible to set background color of rows where is USA to red and where is Europe to blue?
Yes, it is.
Nevertheless, if I could give you a hint, don't make the code more involved before to have completely understood how it works.
Anyway, you certainly can add a new column and put in it an image concerning the country field content. The best way is to name the images with the same country name.
You can also assign to any <tr> tag a class with the same country name and create a CSS directive for any country with the background color that you prefer.
This is my page after all this modifies:
@helper MergedRows(IEnumerable<dynamic> rows){
if (rows.Count() == 0)
{
<tr><td colspan="4">There aren't any Itineraries</td></tr>
} else {
List<dynamic> data = rows.ToList();
string prevDate = "";
string prevCountry = "";
int count = 0;
// create a complementary list for the table layout
List<int> list = new List<int>();
// scan the table rows and populate the complementary list
foreach(var row in data)
{
// if is the first row
if (prevDate == ""){
prevDate = row.Date;
prevCountry = row.Country;
count = 1;
// for the following rows
} else {
// if Date and Country are the same
if (row.Date == prevDate && row.Country == prevCountry){
// increase counter
count++;
// else populate complementary list
} else {
list.Add(count);
for (int i = 1; i < count; i++) {
list.Add(0);
}
prevDate = row.Date;
prevCountry = row.Country;
count = 1;
}
}
}
// if is the last row
list.Add(count);
for (int i = 1; i < count; i++) {
list.Add(0);
}
// build the table
var tblRow = "";
for (int i = 0; i < list.Count; i++)
{
// read the row type from the complementary list
switch(list[i])
{
// if is a row with only the City field
case 0:
tblRow = "<tr class='" + data[i].Country + "'>";
break;
// if is a row without duplicated Date-Country
case 1:
tblRow = "<tr class='" + data[i].Country + "'><td><img src='img/" + data[i].Country +
".png' width='32' height='32'></td><td>" +
data[i].Date + "</td><td>" + data[i].Country + "</td>";
break;
// if is a row with duplicated Date-Country
default:
tblRow = "<tr class='" + data[i].Country + "'><td rowspan='" + list[i] + "'><img src='img/"
+ data[i].Country + ".png' width='32' height='32'></td>" +
"<td rowspan='" + list[i] + "'>" + data[i].Date +
"</td><td rowspan='" + list[i] + "'>" + data[i].Country + "</td>";
break;
}
// display the row
tblRow += "<td>" + data[i].City + "</td></tr>";
<text>@Html.Raw(tblRow)</text>
}
}
}
@{
var db = Database.Open("RowsMerge");
var selectedData = db.Query(@"SELECT convert (nvarchar(10),[date],104) as Date,Country,City
From Itinerary WHERE City = 'piff' ORDER BY Date, Country");
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title></title>
<!-- country styles -->
<style type="text/css">
tr.Europe { background-color: #0ff; }
tr.USA { background-color: #f00; }
tr.Mexico { background-color: #ffd800; }
</style>
</head>
<body>
<h1>Plan</h1>
<table border="1">
<thead>
<tr>
<th></th>
<th>Date</th>
<th>Country</th>
<th>City</th>
</tr>
</thead>
<tbody>
@MergedRows(selectedData)
</tbody>
</table>
</body>
</html>
Marked as answer by triskac on Jan 03, 2013 10:52 AM
GmGregori
Contributor
5446 Points
735 Posts
Re: WebGrid column merge
Dec 28, 2012 09:26 PM|LINK
I try to answer to your appeal with the code that follows
@{ var db = Database.Open("tipy"); // convert IEnumerable to list var selectedData = db.Query("SELECT * FROM Itinerary ORDER BY Date, Country").ToList(); } <h1>Plan</h1> <table border="1"> <thead> <tr> <th>Date</th> <th>Country</th> <th>City</th> </tr> </thead> <tbody> @{ // create a complementary list for the table layout List<int> list = new List<int>(); DateTime prevDate = DateTime.MinValue; string prevCountry = ""; int count = 0; // scan the table rows and populate the complementary list foreach(var row in selectedData) { // if is the first row if (prevDate == DateTime.MinValue){ prevDate = row.Date; prevCountry = row.Country; count = 1; // for the following rows } else { // if Date and Country are the same if (row.Date == prevDate && row.Country == prevCountry){ // increase counter count++; // else populate complementary list } else { list.Add(count); for (int i = 1; i < count; i++) { list.Add(0); } prevDate = row.Date; prevCountry = row.Country; count = 1; } } } // if is the last row list.Add(count); for (int i = 1; i < count; i++) { list.Add(0); } // build the table var tblRow = ""; for (int i = 0; i < list.Count; i++) { // read the row type from the complementary list switch(list[i]) { // if is a row with only the City field case 0: tblRow = "<tr>"; break; // if is a row without duplicated Date-Country case 1: tblRow = "<tr><td>" + selectedData[i].Date + "</td><td>" + selectedData[i].Country + "</td>"; break; // if is a row with duplicated Date-Country default: tblRow = "<tr><td rowspan='" + list[i] + "'>" + selectedData[i].Date + "</td><td rowspan='" + list[i] + "'>" + selectedData[i].Country + "</td>"; break; } // display the row tblRow += "<td>" + selectedData[i].City + "</td></tr>"; <text>@Html.Raw(tblRow)</text> } } </tbody> </table>My approach is a little tricky, because is based on a complementary list that is populated with the row type of the table: if the row has n following rows with the same Date-Country data, the value for this row in the complementary list is equal to n+1, if the row hasn't rows like it is equal to 1, if the row is part of a group of row with same Date-Country data is equal to 0.
I have made only a short test, but it worked on my system: try it.
triskac
Member
15 Points
52 Posts
Re: WebGrid column merge
Dec 29, 2012 08:13 AM|LINK
Gregori,
it is working, thank you very much.
GmGregori
Contributor
5446 Points
735 Posts
Re: WebGrid column merge
Dec 29, 2012 02:21 PM|LINK
I have rewritten the page moving the code into an helper that takes care of the data convertion from IEnumerable to List, too.
@helper MergedRows(IEnumerable<dynamic> rows){ List<dynamic> data = rows.ToList(); DateTime prevDate = DateTime.MinValue; string prevCountry = ""; int count = 0; // create a complementary list for the table layout List<int> list = new List<int>(); // scan the table rows and populate the complementary list foreach(var row in data) { // if is the first row if (prevDate == DateTime.MinValue){ prevDate = row.Date; prevCountry = row.Country; count = 1; // for the following rows } else { // if Date and Country are the same if (row.Date == prevDate && row.Country == prevCountry){ // increase counter count++; // else populate complementary list } else { list.Add(count); for (int i = 1; i < count; i++) { list.Add(0); } prevDate = row.Date; prevCountry = row.Country; count = 1; } } } // if is the last row list.Add(count); for (int i = 1; i < count; i++) { list.Add(0); } // build the table var tblRow = ""; for (int i = 0; i < list.Count; i++) { // read the row type from the complementary list switch(list[i]) { // if is a row with only the City field case 0: tblRow = "<tr>"; break; // if is a row without duplicated Date-Country case 1: tblRow = "<tr><td>" + data[i].Date + "</td><td>" + data[i].Country + "</td>"; break; // if is a row with duplicated Date-Country default: tblRow = "<tr><td rowspan='" + list[i] + "'>" + data[i].Date + "</td><td rowspan='" + list[i] + "'>" + data[i].Country + "</td>"; break; } // display the row tblRow += "<td>" + data[i].City + "</td></tr>"; <text>@Html.Raw(tblRow)</text> } } @{ var db = Database.Open("tipy"); var selectedData = db.Query("SELECT * FROM Itinerary ORDER BY Date, Country"); } <h1>Plan</h1> <table border="1"> <thead> <tr> <th>Date</th> <th>Country</th> <th>City</th> </tr> </thead> <tbody> @MergedRows(selectedData) </tbody> </table>I think that this should be a little improvement.
triskac
Member
15 Points
52 Posts
Re: WebGrid column merge
Dec 31, 2012 01:46 PM|LINK
Hi Gregori,
and when I want see only Date and not date and time, what is necessery to change?
when I tried
var selectedData = db.Query("SELECT convert (nvarchar(10),[date],104) as Date,Country,City From Itinerary ORDER BY Date, Country");I get error
Cannot implicitly convert type 'string' to 'System.DateTime'
thanks
GmGregori
Contributor
5446 Points
735 Posts
Re: WebGrid column merge
Dec 31, 2012 03:09 PM|LINK
If you convert Date from DateTime to string you must modify two other lines of code:
to
and
if (prevDate == DateTime.MinValue){to
if (prevDate == ""){triskac
Member
15 Points
52 Posts
Re: WebGrid column merge
Jan 01, 2013 04:42 PM|LINK
Gregori,
thanks, I am facing last problem in this case and this is:
when I am trying to display only current day, if there is any Itinerary on current day.
In case I have some records in actual date I get it visible, but if there is no records I get this error
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
my sql command is:
SELECT convert(nvarchar(10), Date,104) as Date, Country, City FROM Itinerary where convert(nvarchar(10), Date,104)=convert(nvarchar(10), getdate (),104)GmGregori
Contributor
5446 Points
735 Posts
Re: WebGrid column merge
Jan 01, 2013 07:56 PM|LINK
You should test if there aren't any records that match your condition.
If you want to accomplish this with the helper, you can insert an if statement at its beginning leaving the original helper code inside the else block, something like:
@helper MergedRows(IEnumerable<dynamic> rows){ if (rows.Count() == 0) { <tr><td colspan="3">There aren't any Itineraries</td></tr> } else { List<dynamic> data = rows.ToList(); /* here the remainder of the helper */ }triskac
Member
15 Points
52 Posts
Re: WebGrid column merge
Jan 02, 2013 03:16 PM|LINK
Gregori, this is working perfect
I know I asked a lot of question, but I had one more.
Is it possible to add column to table, where I will see flag of the country and is possible to set background color of rows where is USA to red and where is Europe to blue?
Thanks
GmGregori
Contributor
5446 Points
735 Posts
Re: WebGrid column merge
Jan 02, 2013 08:49 PM|LINK
Yes, it is.
Nevertheless, if I could give you a hint, don't make the code more involved before to have completely understood how it works.
Anyway, you certainly can add a new column and put in it an image concerning the country field content. The best way is to name the images with the same country name.
You can also assign to any <tr> tag a class with the same country name and create a CSS directive for any country with the background color that you prefer.
This is my page after all this modifies:
@helper MergedRows(IEnumerable<dynamic> rows){ if (rows.Count() == 0) { <tr><td colspan="4">There aren't any Itineraries</td></tr> } else { List<dynamic> data = rows.ToList(); string prevDate = ""; string prevCountry = ""; int count = 0; // create a complementary list for the table layout List<int> list = new List<int>(); // scan the table rows and populate the complementary list foreach(var row in data) { // if is the first row if (prevDate == ""){ prevDate = row.Date; prevCountry = row.Country; count = 1; // for the following rows } else { // if Date and Country are the same if (row.Date == prevDate && row.Country == prevCountry){ // increase counter count++; // else populate complementary list } else { list.Add(count); for (int i = 1; i < count; i++) { list.Add(0); } prevDate = row.Date; prevCountry = row.Country; count = 1; } } } // if is the last row list.Add(count); for (int i = 1; i < count; i++) { list.Add(0); } // build the table var tblRow = ""; for (int i = 0; i < list.Count; i++) { // read the row type from the complementary list switch(list[i]) { // if is a row with only the City field case 0: tblRow = "<tr class='" + data[i].Country + "'>"; break; // if is a row without duplicated Date-Country case 1: tblRow = "<tr class='" + data[i].Country + "'><td><img src='img/" + data[i].Country + ".png' width='32' height='32'></td><td>" + data[i].Date + "</td><td>" + data[i].Country + "</td>"; break; // if is a row with duplicated Date-Country default: tblRow = "<tr class='" + data[i].Country + "'><td rowspan='" + list[i] + "'><img src='img/" + data[i].Country + ".png' width='32' height='32'></td>" + "<td rowspan='" + list[i] + "'>" + data[i].Date + "</td><td rowspan='" + list[i] + "'>" + data[i].Country + "</td>"; break; } // display the row tblRow += "<td>" + data[i].City + "</td></tr>"; <text>@Html.Raw(tblRow)</text> } } } @{ var db = Database.Open("RowsMerge"); var selectedData = db.Query(@"SELECT convert (nvarchar(10),[date],104) as Date,Country,City From Itinerary WHERE City = 'piff' ORDER BY Date, Country"); } <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <title></title> <!-- country styles --> <style type="text/css"> tr.Europe { background-color: #0ff; } tr.USA { background-color: #f00; } tr.Mexico { background-color: #ffd800; } </style> </head> <body> <h1>Plan</h1> <table border="1"> <thead> <tr> <th></th> <th>Date</th> <th>Country</th> <th>City</th> </tr> </thead> <tbody> @MergedRows(selectedData) </tbody> </table> </body> </html>HowandWhy
Member
13 Points
43 Posts
Re: WebGrid column merge
Apr 13, 2013 07:00 AM|LINK
Dear Mike
Where does this format line fit into the whole Web Grid set of statements?
Regards
Philip