public String updateScript()
{
connection.Open();
cmd.Connection = connection;
//Get current directory for style sheets and images
string appPath = HttpRuntime.AppDomainAppVirtualPath == "/" ? appPath = "" : appPath = HttpRuntime.AppDomainAppVirtualPath;
//Base api URL
string newsURL = getNewsURL();
string baseUrl = getBaseURL();
ApiContext ac = new ApiContext(newsURL, baseUrl);
//Since this column is an identity column in the table this value does not actually get inserted.
int entryID = 0;
//Used to compare the current BraftonID from the xml feed to the Brafton IDs in the Blog_Entries table
int compareIDs;
//Blog_Entries table variables
string artBlogID;
string title;
string entry;
DateTime addedDate;
string description = string.Empty;
string published = "True";
string allowComments = "False";
string displayCopyright = "False";
string photoURL;
string byline;
string caption;
////////////////////////////
//Blog_Categories table variables
string slug;
string category;
int parentID = 0;
int intPortalID = getPortalID();
////////////////////////////
//This is for storing all of the category xml urls during the iteration
ArrayList xmlArtCatURLs = new ArrayList();
//Blog_Entry_Categories table Arrays, these are populated while populating
//The Blog_Entry DataTable and Blog_Categories DataTable
ArrayList entryIDArray = new ArrayList();
ArrayList categoryArray = new ArrayList();
/////////////////////////////////////////////////////////////////////////
DataTable articleTable = DataTables.GetTable("Blog_Entries");
//For Limit
int l = 0;
//Set the limit of the amount of articles that can be imported at a time
int limit = getLimit();
//Fill Blog_Entries DataTable
foreach (newsItem ni in ac.News)
{
if (l < limit)
{
artBlogID = ni.id.ToString();
title = ni.headline;
entry = ni.text;
description = ni.extract;
addedDate = ni.publishDate;
photoURL = ni.PhotosHref;
byline = ni.byLine;
//////////////////////////////////////////////////////////////////////////
//Passes all Brafton posts from the Database into this method and checks to see if this posts already exists
cmd.CommandText = "IF (SELECT BraftonID FROM Blog_Entries WHERE BraftonID='" + artBlogID + "') IS NOT NULL BEGIN SELECT 0 END ELSE SELECT 1";
compareIDs = (int)cmd.ExecuteScalar();
if (compareIDs == 1)
{
photo img = ni.photos.First();
photo.Instance photoInstance = img.Instances.Where(x => x.type == enumeratedTypes.enumPhotoInstanceType.Large).FirstOrDefault();
//Checks to see if large images are enabled on the feed
if (photoInstance != null)
{
photoURL = photoInstance.url.ToString();
caption = img.caption.ToString();
//Checks to see if the feed has photos enabled.
if (!string.IsNullOrEmpty(photoURL))
{
GetImages retrieveImage = new GetImages(photoURL, entry, description, appPath, caption);
retrieveImage.DownloadImage();
//The images is placed into the description and the entry here
description = retrieveImage._description;
entry = retrieveImage._entry;
//errorCheckingLabel.text = description;
}
}
//Checks to see if the feed has the byline enabled.
if (!string.IsNullOrEmpty(byline))
{
entry = entry.Insert(entry.Length, "<br /><br /><span class='byline'> By " + byline + "</span>");
}
articleTable.Rows.Add(getBlogID(), entryID, title, entry, addedDate, published, description, allowComments, displayCopyright, null, "", artBlogID);
//Place the category URL into the array for future use
xmlArtCatURLs.Add(ni.CategoriesHref);
//For Future Use with Blog_Entry_Categories
entryIDArray.Add(artBlogID);
//increment limit
l++;
}
}
}
//Check to see if the article table is empty, if it is, cancel the whole operation.
if (articleTable != null && articleTable.Rows.Count > 0)
{
//Used to copy the DataTables to the MSSQL Database
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "Blog_Entries";
bulkCopy.WriteToServer(articleTable);
}
DataTable categoryTable = DataTables.GetTable("Blog_Categories");
/////////////////////////////////////////////////////////////
//Fill Blog_Categories DataTable
for (int i = 0; i < articleTable.Rows.Count; i++)
{
XmlDocument tmpXMLDoc = new XmlDocument();
tmpXMLDoc.Load(xmlArtCatURLs[i].ToString());
XmlNode name = tmpXMLDoc.GetElementsByTagName("name")[0];
if (name != null)
{
category = name.InnerText;
}
else
{
category = "Uncategorized";
}
slug = strip(category) + ".aspx";
cmd.CommandText = "IF (SELECT Category FROM Blog_Categories WHERE Category='" + category + "') IS NOT NULL BEGIN SELECT 0 END ELSE SELECT 1";
compareIDs = (int)cmd.ExecuteScalar();
if (compareIDs == 1)
{
//Compares the rows in the categoryTable to the category currently being processed.
categoryTable.DefaultView.Sort = "Category";
int findRow = categoryTable.DefaultView.Find(category);
if (findRow == -1)
{
//Create the row in the datatable
categoryTable.Rows.Add(entryID, category, slug, parentID, intPortalID);
}
}
//For Future Use with Blog_Entry_Categories
categoryArray.Add(category);
}
//Used to copy the DataTables to the MSSQL Database
if (categoryTable != null && categoryTable.Rows.Count > 0)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "Blog_Categories";
bulkCopy.WriteToServer(categoryTable);
}
}
//Temporary variables that change with each iteration
int tempCatID;
int tempEntryID;
string tempSlug;
int PageTabId = getTabID();
//Match the CategoryIDs with the EntryIDs for the Blog_Entry_Categories Table
DataTable catEntryTable = DataTables.GetTable("Blog_Entry_Categories");
for (int i = 0; i < articleTable.Rows.Count; i++)
{
category = categoryArray[i].ToString();
artBlogID = entryIDArray[i].ToString();
cmd.CommandText = "Select EntryID From Blog_Entries Where BraftonID = '" + artBlogID + "'";
tempEntryID = (int)cmd.ExecuteScalar();
cmd.CommandText = "Select Title From Blog_Entries Where BraftonID = '" + artBlogID + "'";
tempSlug = (string)cmd.ExecuteScalar();
cmd.CommandText = "Select CatID From Blog_Categories Where Category ='" + category + "'";
tempCatID = (int)cmd.ExecuteScalar();
catEntryTable.Rows.Add(entryID, tempEntryID, tempCatID);
//For Permalinks
setPermalinks(PageTabId, tempEntryID, tempSlug, artBlogID);
}
//Used to copy the DataTables to the MSSQL Database
if (catEntryTable != null && catEntryTable.Rows.Count > 0)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "Blog_Entry_Categories";
bulkCopy.WriteToServer(catEntryTable);
}
}
//Dispose of all of the tables, datasets and commands
catEntryTable.Dispose();
categoryTable.Dispose();
}
articleTable.Dispose();
cmd.Dispose();
connection.Close();
return(description);
}
public string updateScriptDebug()
{
if (MyGlobals.ArtOrBlog == "articles")
{
MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "Start of articles<br>";
connection.Open();
cmd.Connection = connection;
//Get current directory for style sheets and images
//was getting errors from the following so simplified it to AppDomainAppPath.ToString();
string appPath = HttpRuntime.AppDomainAppVirtualPath == "/" ? appPath = "" : appPath = HttpRuntime.AppDomainAppVirtualPath;
//string appPath = HttpRuntime.AppDomainAppVirtualPath.ToString();//AppDomainAppPath
//Base api URL
string newsURL = getNewsURL();
string baseUrl = getBaseURL();
// MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "<br>************************************************<br>";
// MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "newsURL " + newsURL + "<br>";
// MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "baseUrl " + baseUrl + "<br>";
// MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "<br>************************************************<br>";
ApiContext ac;
if (MyGlobals.ArtOrBlog == "archive")
{
ac = new ApiContext(MyGlobals.ArchiveLink);
}
else
{
ac = new ApiContext(newsURL, baseUrl);
}
//Since this column is an identity column in the table this value does not actually get inserted.
int entryID = 0;
//Used to compare the current BraftonID from the xml feed to the Brafton IDs in the Blog_Entries table
int compareIDs;
//Blog_Entries table variables
string artBlogID;
string title;
string entry;
DateTime addedDate;
string description = string.Empty;
string description_debug = string.Empty;
bool published = true;
bool allowComments = false;
bool displayCopyright = false;
string photoURL;
string byline;
string caption;
int PageTabId = getTabID();
////////////////////////////
//Blog_Categories table variables
string slug;
string category;
int parentID = 0;
int intPortalID = getPortalID();
////////////////////////////
//This is for storing all of the category xml urls during the iteration
ArrayList xmlArtCatURLs = new ArrayList();
//Blog_Entry_Categories table Arrays, these are populated while populating
//The Blog_Entry DataTable and Blog_Categories DataTable
ArrayList entryIDArray = new ArrayList();
ArrayList categoryArray = new ArrayList();
/////////////////////////////////////////////////////////////////////////
DataTable articleTable = DataTables.GetTable("Blog_Entries");
//For Limit
int l = 0;
//Set the limit of the amount of articles that can be imported at a time
int limit = getLimit();
#region Article Loop DEBUG
//Fill Blog_Entries DataTable
foreach (newsItem ni in ac.News)
{
if (l < limit)
{
artBlogID = ni.id.ToString();
title = ni.headline;
entry = ni.text;
description = ni.extract;
addedDate = ni.publishDate;
photoURL = ni.PhotosHref;
byline = ni.byLine;
DateTime today = DateTime.Today;
//for operations after update or insert
int entryId;
string tempSlug;
using (DNNDataLayerDataContext dnnContext = new DNNDataLayerDataContext())
{
Blog_Entry be = dnnContext.Blog_Entries.FirstOrDefault(x => x.BraftonID == artBlogID);
if (be != null)
{
#region Update Article
MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "Start of update<br>";
//If update feed content is on we do some stuff
int updateCheck = MyGlobals.IncludeUpdatedFeedContent;
int lastDayUpd = 0;
int todayDay = 0;
lastDayUpd = be.LastUpdatedOn.Value.DayOfYear;
//todayDay = DateTime.Today.DayOfYear;
//TODO set this check back in place FJD
//if the article has not been updated today
if (lastDayUpd != todayDay)
{
//if they have updated feed content checked
if (updateCheck == 1)
{
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Update the article
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Lets get the entryID that matches the BraftonID
//But if there isn't one it means this is a new article and we should skip the update
//cmd.CommandText = "IF (SELECT EntryID FROM Blog_Entries WHERE BraftonID='" + artBlogID + "') IS NULL BEGIN SELECT 0 END ELSE (SELECT EntryID FROM Blog_Entries WHERE BraftonID=" + artBlogID + ")";
//int getDNNID = (int)cmd.ExecuteScalar();
//if (getDNNID > 0)
//{
// DateTime today = DateTime.Today;
//string displayDate = today.ToString("dd/MM/yyyy");
#region IMAGE HANDLER
//photo img = ni.photos.First();
//photo.Instance photoInstance = img.Instances.Where(x => x.type == enumeratedTypes.enumPhotoInstanceType.Medium).FirstOrDefault();
//string photoTest = photoInstance.type.ToString();
photo img = ni.photos.First();
photo.Instance photoInstanceLarge = img.Instances.Where(x => x.type == enumeratedTypes.enumPhotoInstanceType.Large).FirstOrDefault();
photo.Instance photoInstanceMedium = img.Instances.Where(x => x.type == enumeratedTypes.enumPhotoInstanceType.Medium).FirstOrDefault();
photo.Instance photoInstanceSmall = img.Instances.Where(x => x.type == enumeratedTypes.enumPhotoInstanceType.Small).FirstOrDefault();
photo.Instance photoInstance;
if (photoInstanceLarge != null)
{
photoInstance = photoInstanceLarge;
//MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "Using Large<br>";
}
else if (photoInstanceMedium != null)
{
photoInstance = photoInstanceMedium;
// MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "Using Medium<br>";
}
else if (photoInstanceSmall != null)
{
photoInstance = photoInstanceSmall;
// MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "Using Small<br>";
}
else
{
photoInstance = null;
}
//Otherwise leave it as null and move on
//Checks to see if large images are enabled on the feed
if (photoInstance != null)
{
photoURL = photoInstance.url.ToString();
caption = img.caption.ToString();
//Checks to see if the feed has photos enabled.
if (!string.IsNullOrEmpty(photoURL))
{
GetImages retrieveImage2 = new GetImages(photoURL, entry, description, appPath, caption);
retrieveImage2.DownloadImageDebug();
//The images is placed into the description and the entry here
entry = retrieveImage2._entry;
description = retrieveImage2._description;
MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "**********************************************************<br>";
MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "brafton ID: " + artBlogID + "<br>";
MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "Description: " + description + "<br>";
MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "**********************************************************<br>";
//string testUrl = HttpContext.Current.Request.Url.Host;
}
}
#endregion IMAGE HANDLER
//Checks to see if the feed has the byline enabled.
if (!string.IsNullOrEmpty(byline))
{
entry = entry.Insert(entry.Length, "<br /><br /><span class='byline'> By " + byline + "</span>");
}
#region Update Article
be.Title = title;
be.Entry = entry;
be.Description = description;
//be.Copyright = null;
be.LastUpdatedOn = today;
be.AllowComments = false;
//dnnContext.
dnnContext.SubmitChanges();
#endregion
entryId = be.EntryID;
tempSlug = title;
//Update Permalinks
setPermalinks(PageTabId, entryId, tempSlug, artBlogID);
} //end of update check
} //end of check for update today
#endregion Update Article
}
else
{
#region New Article
photo img = ni.photos.First();
photo.Instance photoInstanceLarge = img.Instances.Where(x => x.type == enumeratedTypes.enumPhotoInstanceType.Large).FirstOrDefault();
photo.Instance photoInstanceMedium = img.Instances.Where(x => x.type == enumeratedTypes.enumPhotoInstanceType.Medium).FirstOrDefault();
photo.Instance photoInstanceSmall = img.Instances.Where(x => x.type == enumeratedTypes.enumPhotoInstanceType.Small).FirstOrDefault();
photo.Instance photoInstance;
if (photoInstanceLarge != null)
{
photoInstance = photoInstanceLarge;
// MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "Using Large<br>";
}
else if (photoInstanceMedium != null)
{
photoInstance = photoInstanceMedium;
// MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "Using Medium<br>";
}
else if (photoInstanceSmall != null)
{
photoInstance = photoInstanceSmall;
// MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "Using Small<br>";
}
//Otherwise leave it as null and move on
else
{
photoInstance = null;
}
#region IMAGE HANDLER
//Checks to see if medium images are enabled on the feed
if (photoInstance != null)
{
photoURL = photoInstance.url.ToString();
caption = img.caption.ToString();
//Checks to see if the feed has photos enabled.
if (!string.IsNullOrEmpty(photoURL))
{
GetImages retrieveImage2 = new GetImages(photoURL, entry, description, appPath, caption);
retrieveImage2.DownloadImageDebug();
//The images is placed into the description and the entry here
description_debug = retrieveImage2._description;
entry = retrieveImage2._entry;
}
}
#endregion IMAGE HANDLER
//Checks to see if the feed has the byline enabled.
if (!string.IsNullOrEmpty(byline))
{
entry = entry.Insert(entry.Length, "<br /><br /><span class='byline'> By " + byline + "</span>");
}
Blog_Entry newBlogEntry = new Blog_Entry();
newBlogEntry.BlogID = getBlogID();
newBlogEntry.Title = title;
newBlogEntry.Entry = entry;
newBlogEntry.AddedDate = addedDate;
newBlogEntry.Published = published;
newBlogEntry.Description = description_debug;
newBlogEntry.AllowComments = allowComments;
newBlogEntry.DisplayCopyright = displayCopyright;
newBlogEntry.BraftonID = artBlogID;
newBlogEntry.LastUpdatedOn = today;
dnnContext.Blog_Entries.InsertOnSubmit(newBlogEntry);
dnnContext.SubmitChanges();
entryId = newBlogEntry.EntryID;
tempSlug = title;
//For Permalinks
setPermalinks(PageTabId, entryId, tempSlug, artBlogID);
///////////////////////////////////////////////////
//Place the category URL into the array for future use
xmlArtCatURLs.Add(ni.CategoriesHref);
//For Future Use with Blog_Entry_Categories
entryIDArray.Add(artBlogID);
//increment limit
l++;
/////////////////////////////////////////////////////
#endregion New Article
}
}
//////Passes all Brafton posts from the Database into this method and checks to see if this posts already exists
////cmd.CommandText = "IF (SELECT BraftonID FROM Blog_Entries WHERE BraftonID='" + artBlogID + "') IS NOT NULL BEGIN SELECT 0 END ELSE SELECT 1";
//////cmd.CommandText = "SELECT 1";
////compareIDs = (int)cmd.ExecuteScalar();
////if it doesn't exist
//if (compareIDs == 1)
//#region Write Article DEBUG
//{
// // articleTable.Rows.Add(getBlogID(), entryID, title, entry, addedDate, published, description, allowComments, displayCopyright, null, "", artBlogID);
//}
//else
// //***************************************************************************************
// //***************************************************************************************
// //***************************************************************************************
//}
//#endregion Write Article DEBUG
}
}
#endregion Article Loop DEBUG
//Check to see if the article table is empty, if it is, cancel the whole operation.
if (articleTable != null && articleTable.Rows.Count > 0)
{
///////////////////////////////////////////////////////////////////////////////////
//TODO: category stuff
///////////////////////////////////////////////////////////////////////////////////
#region Category stuff
using (DNNDataLayerDataContext dnnContext = new DNNDataLayerDataContext())
{
//Blog_Category bc = dnnContext.Blog_Categories.FirstOrDefault(x => x.Category == artBlogID);
}
//Used to copy the DataTables to the MSSQL Database
//using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
//{
// bulkCopy.DestinationTableName = "Blog_Entries";
// bulkCopy.WriteToServer(articleTable);
//}
DataTable categoryTable = DataTables.GetTable("Blog_Categories");
/////////////////////////////////////////////////////////////
//Fill Blog_Categories DataTable
for (int i = 0; i < articleTable.Rows.Count; i++)
{
XmlDocument tmpXMLDoc = new XmlDocument();
tmpXMLDoc.Load(xmlArtCatURLs[i].ToString());
XmlNode name = tmpXMLDoc.GetElementsByTagName("name")[0];
if (name != null)
{
category = name.InnerText;
}
else
{
category = "Uncategorized";
}
slug = strip(category) + ".aspx";
cmd.CommandText = "IF (SELECT Category FROM Blog_Categories WHERE Category='" + category + "') IS NOT NULL BEGIN SELECT 0 END ELSE SELECT 1";
compareIDs = (int)cmd.ExecuteScalar();
if (compareIDs == 1)
{
//Compares the rows in the categoryTable to the category currently being processed.
categoryTable.DefaultView.Sort = "Category";
int findRow = categoryTable.DefaultView.Find(category);
if (findRow == -1)
{
//Create the row in the datatable
categoryTable.Rows.Add(entryID, category, slug, parentID, intPortalID);
}
}
//For Future Use with Blog_Entry_Categories
categoryArray.Add(category);
}
//Used to copy the DataTables to the MSSQL Database
if (categoryTable != null && categoryTable.Rows.Count > 0)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "Blog_Categories";
//bulkCopy.WriteToServer(categoryTable);
}
}
//Temporary variables that change with each iteration
int tempCatID;
int tempEntryID;
string tempSlug;
//int PageTabId = getTabID();
//Match the CategoryIDs with the EntryIDs for the Blog_Entry_Categories Table
DataTable catEntryTable = DataTables.GetTable("Blog_Entry_Categories");
for (int i = 0; i < articleTable.Rows.Count; i++)
{
category = categoryArray[i].ToString();
artBlogID = entryIDArray[i].ToString();
cmd.CommandText = "Select EntryID From Blog_Entries Where BraftonID = '" + artBlogID + "'";
tempEntryID = (int)cmd.ExecuteScalar();
cmd.CommandText = "Select Title From Blog_Entries Where BraftonID = '" + artBlogID + "'";
tempSlug = (string)cmd.ExecuteScalar();
cmd.CommandText = "Select CatID From Blog_Categories Where Category ='" + category + "'";
tempCatID = (int)cmd.ExecuteScalar();
catEntryTable.Rows.Add(entryID, tempEntryID, tempCatID);
//For Permalinks
//setPermalinks(PageTabId, tempEntryID, tempSlug, artBlogID);
}
//Used to copy the DataTables to the MSSQL Database
if (catEntryTable != null && catEntryTable.Rows.Count > 0)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "Blog_Entry_Categories";
bulkCopy.WriteToServer(catEntryTable);
}
}
//Dispose of all of the tables, datasets and commands
catEntryTable.Dispose();
categoryTable.Dispose();
}
articleTable.Dispose();
cmd.Dispose();
connection.Close();
#endregion Category Stuff
return(description_debug);
}//end of articles
MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "Video Include Global =" + MyGlobals.IncludeVideo;
//If the feed includes videos
if (MyGlobals.IncludeVideo == 1)
{
ImportVideos();
}
MyGlobals.MyGlobalError = MyGlobals.MyGlobalError + "ARTORBLOG" + MyGlobals.ArtOrBlog;
string returnVal = "include video";
return(returnVal);
}