private string LoadTourHierarchy()
{
List<Tour> sqlTours = new List<Tour>();
int nRet = GetSQLTourArrayList(sqlTours, "exec spGetWWTToursForDateRangeFromCatId 0, null, null, 0");
using (StringWriter sw = new StringWriter())
{
using (XmlTextWriter xmlWriter = new XmlTextWriter(sw))
{
xmlWriter.Formatting = Formatting.Indented;
xmlWriter.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"UTF-8\"");
xmlWriter.WriteStartElement("Folder");
if (sqlTours.Count > 0)
{
foreach (Tour tr in sqlTours)
{
xmlWriter.WriteStartElement("Tour");
xmlWriter.WriteAttributeString("Title", tr.TourTitle);
xmlWriter.WriteAttributeString("ID", tr.TourGuid.ToString());
xmlWriter.WriteAttributeString("Description", tr.TourDescription);
xmlWriter.WriteAttributeString("Classification", "Other");
xmlWriter.WriteAttributeString("AuthorEmail", tr.AuthorEmailAddress);
xmlWriter.WriteAttributeString("Author", tr.AuthorName);
xmlWriter.WriteAttributeString("AuthorUrl", tr.AuthorURL);
xmlWriter.WriteAttributeString("AverageRating", tr.AverageRating.ToString());
xmlWriter.WriteAttributeString("LengthInSecs", tr.LengthInSecs.ToString());
xmlWriter.WriteAttributeString("OrganizationUrl", tr.OrganizationURL);
xmlWriter.WriteAttributeString("OrganizationName", tr.OrganizationName);
xmlWriter.WriteEndElement();
}
}
StoredProc sp1 = new StoredProc("Select CategoryId, ParentCatID, Name, CatThumbnailUrl from TourCategories where ParentCatId = 0 and CategoryId <> 0");
DataTable dt = new DataTable();
int nRet1 = sp1.RunQuery(dt);
sp1.Dispose();
foreach (DataRow dr in dt.Rows)
{
int catId = Convert.ToInt32(dr[0]);
int parcatId = Convert.ToInt32(dr[1]);
string catName = Convert.ToString(dr[2]);
string catTnUrl = Convert.ToString(dr[3]);
xmlWriter.WriteStartElement("Folder");
xmlWriter.WriteAttributeString("Name", catName);
xmlWriter.WriteAttributeString("Group", "Tour");
xmlWriter.WriteAttributeString("Thumbnail", catTnUrl);
AddToursToChildNode(xmlWriter, catId);
xmlWriter.WriteEndElement();
}
xmlWriter.WriteEndElement();
xmlWriter.Close();
}
sw.Close();
return sw.ToString();
//StreamWriter fsw = new StreamWriter(@"c:\\test_Tour.xml");
//fsw.WriteLine(sw.ToString());
//fsw.Close();
}
return "";
}
int GetSQLTourArrayList(List<Tour> sqlTours, string query)
{
StoredProc sp = new StoredProc(query);
DataTable dt = new DataTable();
int nRet = sp.RunQuery(dt);
sp.Dispose();
foreach (DataRow dr in dt.Rows)
{
Tour tr = new Tour();
tr.TourTitle = Convert.ToString(dr["TourTitle"]);
tr.TourGuid = new Guid(dr["TourGUID"].ToString());
tr.TourDescription = Convert.ToString(dr["TourDescription"]);
tr.AuthorEmailAddress = Convert.ToString(dr["AuthorEmailAddress"]);
tr.AuthorName = Convert.ToString(dr["AuthorName"]);
tr.AuthorURL = Convert.ToString(dr["AuthorURL"]);
tr.AverageRating = Convert.ToDouble(dr["AverageRating"]);
tr.LengthInSecs = Convert.ToInt32(dr["LengthInSecs"]);
tr.OrganizationURL = Convert.ToString(dr["OrganizationURL"]);
tr.OrganizationName = Convert.ToString(dr["OrganizationName"]);
sqlTours.Add(tr);
}
return 0;
}
private void AddToursToChildNode(XmlWriter xmlWriter, int parcatId)
{
List<Tour> sqlTours = new List<Tour>();
int nRet = GetSQLTourArrayList(sqlTours, "exec spGetWWTToursForDateRangeFromCatId " + parcatId + ", null, null, 0");
foreach (Tour tr in sqlTours)
{
xmlWriter.WriteStartElement("Tour");
xmlWriter.WriteAttributeString("Title", tr.TourTitle);
xmlWriter.WriteAttributeString("ID", tr.TourGuid.ToString());
xmlWriter.WriteAttributeString("Description", tr.TourDescription);
xmlWriter.WriteAttributeString("Classification", "Other");
xmlWriter.WriteAttributeString("AuthorEmail", tr.AuthorEmailAddress);
xmlWriter.WriteAttributeString("Author", tr.AuthorName);
xmlWriter.WriteAttributeString("AuthorUrl", tr.AuthorURL);
xmlWriter.WriteAttributeString("AverageRating", tr.AverageRating.ToString());
xmlWriter.WriteAttributeString("LengthInSecs", tr.LengthInSecs.ToString());
xmlWriter.WriteAttributeString("OrganizationUrl", tr.OrganizationURL);
xmlWriter.WriteAttributeString("OrganizationName", tr.OrganizationName);
xmlWriter.WriteEndElement();
}
StoredProc sp1 = new StoredProc("Select CategoryId, ParentCatID, Name, CatThumbnailUrl from TourCategories where ParentCatId = " + parcatId.ToString() + " and CategoryId <> " + parcatId);
DataTable dt = new DataTable();
int nRet1 = sp1.RunQuery(dt);
sp1.Dispose();
foreach (DataRow dr in dt.Rows)
{
int tempcatId = Convert.ToInt32(dr[0]);
int tempparcatId = Convert.ToInt32(dr[1]);
string catName = Convert.ToString(dr[2]);
string catTnUrl = Convert.ToString(dr[3]);
xmlWriter.WriteStartElement("Folder");
xmlWriter.WriteAttributeString("Name", catName);
xmlWriter.WriteAttributeString("Group", "Tour");
xmlWriter.WriteAttributeString("Thumbnail", catTnUrl);
AddToursToChildNode(xmlWriter, tempcatId);
xmlWriter.WriteEndElement();
}
}
private void AddToursToChildNode(XmlWriter xmlWriter, int parcatId)
{
List<Tour> SQLTours = new List<Tour>();
int nRet = GetSQLTourArrayList(SQLTours, "exec spGetWWTToursForDateRangeFromCatId " + parcatId + ", null, null, 0");
foreach (Tour tr in SQLTours)
{
xmlWriter.WriteStartElement("Tour");
xmlWriter.WriteAttributeString("Title", tr.TourTitle);
xmlWriter.WriteAttributeString("ID", tr.TourGuid.ToString());
xmlWriter.WriteAttributeString("Description", tr.TourDescription);
xmlWriter.WriteAttributeString("Classification", "Other");
xmlWriter.WriteAttributeString("AuthorEmail", tr.AuthorEmailAddress);
xmlWriter.WriteAttributeString("Author", tr.AuthorName);
xmlWriter.WriteAttributeString("AuthorUrl", tr.AuthorURL);
xmlWriter.WriteAttributeString("AverageRating", tr.AverageRating.ToString());
xmlWriter.WriteAttributeString("LengthInSecs", tr.LengthInSecs.ToString());
xmlWriter.WriteAttributeString("OrganizationUrl", tr.OrganizationURL);
xmlWriter.WriteAttributeString("OrganizationName", tr.OrganizationName);
xmlWriter.WriteAttributeString("ITHList", tr.TourITHList);
xmlWriter.WriteAttributeString("AstroObjectsList", tr.TourAstroObjectList);
xmlWriter.WriteAttributeString("Keywords", tr.TourKeywordList);
xmlWriter.WriteAttributeString("RelatedTours", tr.TourExplicitTourLinkList);
xmlWriter.WriteEndElement();
}
StoredProc sp1 = new StoredProc("exec spGetSubCatDetailsFromParCatId " + parcatId.ToString());
DataTable dt = new DataTable();
int nRet1 = sp1.RunQuery(dt);
sp1.Dispose();
foreach (DataRow dr in dt.Rows)
{
int tempcatId = Convert.ToInt32(dr[0]);
int tempparcatId = Convert.ToInt32(dr[1]);
string catName = Convert.ToString(dr[2]);
string catTNUrl = Convert.ToString(dr[3]);
xmlWriter.WriteStartElement("Folder");
xmlWriter.WriteAttributeString("Name", catName);
xmlWriter.WriteAttributeString("Group", "Tour");
xmlWriter.WriteAttributeString("Thumbnail", catTNUrl);
AddToursToChildNode(xmlWriter, tempcatId);
xmlWriter.WriteEndElement();
}
}