using Sitecore.Data.Items;
using BusinessModules;
using Sitecore.Data.Fields;
using System.Text.RegularExpressions;
using System.Text;
using Sitecore.Data.Managers;
using Sitecore.Security.Accounts;
using System.Data.OleDb;
using HtmlAgilityPack;
using System.Text.RegularExpressions;
using System.IO;
using System.Xml;
namespace ABC.layouts
{
public partial class ScriptsOnDemand : System.Web.UI.Page
{
private void Import()
{
Sitecore.Data.Database master = Sitecore.Configuration.Factory.GetDatabase("master", false);
OleDbCommand command = null;
string con = string.Empty;
//connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\EpicCV.xls;Extended Properties='Excel 4.0;HDR=Yes;'";
try
{
Sitecore.Data.Items.Item rootItem = master.GetItem("/sitecore/content/Global Content/People/A");
List
- BiosItem = new List
- (rootItem.Axes.GetDescendants().Where(m => m.TemplateID.ToString() == "{EB65BFA3-5122-43F4-A1B0-02F430F645CA}"));
StringBuilder sb = new StringBuilder();
using (OleDbConnection connection = new OleDbConnection(con))
{
connection.Open();
command = new OleDbCommand("select * from [Report1$]", connection);
sb.Append("");
sb.Append("
");
using (OleDbDataReader dr = command.ExecuteReader())
{
int counter = 0;
while (dr.Read())
{
counter = counter + 1;
string emailID = dr["BusinessEmail"].ToString().Trim();
string bioVersionName = bioVersionName = dr["lastName"].ToString().Trim() + " " + dr["FirstName"].ToString().Trim();
string mainBiographyNarrative = formatHTML( dr["Bio Narrative"].ToString().Trim() );
string experienceNarrative = formatHTML( dr["Experience Narrative"].ToString().Trim());
List- itemMainBioList = BiosItem.Where(x => x.Fields["Email"].ToString() == emailID).ToList();
foreach (Item itemMainBio in itemMainBioList)
{
sb.Append("
");
sb.Append(" ");
if (!String.IsNullOrEmpty(emailID))
{
sb.Append(" ");
}
try
{
if (itemMainBio.Fields["Email"] != null)
{
sb.Append(" ");
sb.Append(" ");
sb.Append(" ");
TemplateItem template = master.GetTemplate("{8FFC3E2B-C6F5-4A80-913D-E2A54D02FC20}");
User currentUser;
string domainUser = @"sitecore\UserA";
currentUser = Sitecore.Security.Accounts.User.FromName(domainUser, false);
Item oldVersionItem = master.GetItem(itemMainBio.Paths.FullPath + "/Versions");
if (oldVersionItem != null)
{
oldVersionItem.Add(bioVersionName, template);
using (new UserSwitcher(currentUser))
{
Item versionItem = master.GetItem(itemMainBio.Paths.FullPath + "/Versions/" + bioVersionName);
if (versionItem != null)
{
versionItem.Editing.BeginEdit();
versionItem.Fields["VersionName"].Value = dr["Title"].ToString().Trim();
versionItem.Fields["MainBiographyNarrative"].Value = mainBiographyNarrative;
versionItem.Fields["ExperienceNarrative"].Value = experienceNarrative;
versionItem.Fields["__Workflow"].Value = "{E534CB0A-B877-49EA-BBB1-927EBD7853FE}";
versionItem.Fields["__Workflow state"].Value = "{E2CF5329-65A0-40EE-8971-88BDE0F68574}";
versionItem.Fields["__Default workflow"].Value = "{E534CB0A-B877-49EA-BBB1-927EBD7853FE}";
versionItem.Editing.AcceptChanges();
versionItem.Editing.EndEdit();
}
}
}
}
else
{
sb.Append("
sb.Append(" ");
sb.Append(" ");
}
}
catch (Exception ex)
{
string msg = ex.Message;
}
sb.Append("
");
}
}
}
sb.Append("
| Sr. No. | Name | Email Id on File | Sitecore Email ID | Version Name | Match Found |
| " + counter + " | " + dr["lastName"].ToString().Trim() + " " + dr["FirstName"].ToString().Trim() + " | " + emailID + " | " + itemMainBio.Fields["Email"].ToString() + " | " + bioVersionName + " | " + "Yes" + " | | | " + "No" + " |
");
divItemList.InnerHtml = sb.ToString();
}
}
catch (Exception ex)
{
string msg = ex.Message;
}
}
private string formatHTML(string inputHTML)
{
try
{
var document = new HtmlDocument();
document.LoadHtml(inputHTML);
StringWriter writer = new StringWriter();
XmlWriterSettings settings = new XmlWriterSettings();
settings.OmitXmlDeclaration = true;
settings.ConformanceLevel = ConformanceLevel.Fragment;
settings.CloseOutput = false;
var xmlWriter = XmlWriter.Create(writer, settings);
document.OptionOutputAsXml = true;
document.Save(xmlWriter);
var newHtml = writer.ToString();
return Convert.ToString(newHtml);
}
catch (Exception ex)
{
string msg = ex.Message;
return msg;
}
}}
