/// <summary>
/// Loads toon info not actually being processed.
/// </summary>
private void LoadToonInfo() {
string sql = "";
sql = "select * from toondir where processed = 0 order by ComputedCreatedDate desc ";
if (log.IsDebugEnabled) {
log.Debug("LoadToonInfo Start");
}
_toonList.Clear();
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("LoadToonInfo");
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read()) {
ToonDirInfo toonInfo = new ToonDirInfo();
toonInfo.IdArticle = rdr["idArticle"].ToString();
toonInfo.FileName = rdr["filename"].ToString();
var dtCreate = Convert.ToDateTime(rdr["CreateDate"]);
var dtUpdate = Convert.ToDateTime(rdr["UpdateDate"]);
var dtComputeCreatedDate = Convert.ToDateTime(rdr["ComputedCreatedDate"]);
toonInfo.CreateDate = dtCreate;
toonInfo.UpdateDate = dtUpdate;
toonInfo.Processed = Convert.ToInt32(rdr["processed"].ToString());
toonInfo.Id = Convert.ToInt32(rdr["id"].ToString());
toonInfo.ComputedCreatedDate = dtComputeCreatedDate;
toonInfo.UseComputedCreateDate = (int)rdr["UseComputedCreateDate"];
_toonList.Add(toonInfo);
}
rdr.Close();
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("LoadToonInfo End");
}
}
/// <summary>
/// All current comment-user that exist in EC are generated in a ZIP to be imported by the Iterweb system.
/// </summary>
private void ExportUser() {
// COQ Nov.06/2014
// Due to time constraints a modification needs to be made here that involves spliting the users generated
// in chunks of 10000 records and file not being zipped.
string sql = "sp_retrieveusers";
XmlDocument iterDoc = null;
XmlElement rootUsers = null;
string iterwebManifest = "";
string manifestFile = "";
string logLine = "";
long counter = 0;
int i = 1;
int numRecsPerFile = 10000;
if (log.IsDebugEnabled) {
log.Debug("UserExport Start");
}
// Create folder structure
iterwebManifest = "pkusers";
logLine = "Package " + iterwebManifest;
if (log.IsInfoEnabled) log.Info(logLine);
Console.WriteLine(logLine);
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("UserExport");
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
i = 0;
int j = 1;
while (rdr.Read()) {
counter++;
Console.Write("\rProcessing record " + counter);
if (i == 0L) {
//iterwebManifest = Convert.ToString(cnts.IterWebManifestFile).PadLeft(10, '0') + "-pkusers.xml";
iterwebManifest = j + ".xml";
manifestFile = _zipFolder + @"\" + iterwebManifest;
iterDoc = new XmlDocument();
XmlDeclaration iterDocDeclaration = iterDoc.CreateXmlDeclaration("1.0", "UTF-8", null);
rootUsers = iterDoc.CreateElement("us");
iterDoc.PreserveWhitespace = false;
iterDoc.AppendChild(rootUsers);
iterDoc.InsertBefore(iterDocDeclaration, rootUsers);
}
XmlElement user = iterDoc.CreateElement("u");
XmlElement userFields = iterDoc.CreateElement("f");
XmlElement userOptional = iterDoc.CreateElement("o");
rootUsers.AppendChild(user);
user.AppendChild(userFields);
user.AppendChild(userOptional);
UserAddFieldToXml(iterDoc, userFields, "aboid", rdr["Email"].ToString());
UserAddFieldToXml(iterDoc, userFields, "pwd", rdr["Clave"].ToString());
UserAddFieldToXml(iterDoc, userFields, "usrname", rdr["Usuario"].ToString());
UserAddFieldToXml(iterDoc, userFields, "email", rdr["Email"].ToString());
UserAddFieldToXml(iterDoc, userFields, "firstname", rdr["Nombres"].ToString());
//UserAddFieldToXml(iterDoc, userFields, "lastname", rdr["Apellidos"].ToString());
//UserAddOptionalFieldToXml(iterDoc, userOptional, "Nombre de pila", rdr["Nombres"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "Apellidos", rdr["Apellidos"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "Tipo de Documento", rdr["DescIdentificacion"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "Número de documento", rdr["NumIdentificacion"].ToString());
var birthDate = rdr["FechaNacimiento"];
string birthDateFmt = "";
if (birthDate == DBNull.Value) {
birthDateFmt = "";
}
else {
birthDateFmt = String.Format("{0:dd/MM/yyyy}", birthDate);
}
UserAddOptionalFieldToXml(iterDoc, userOptional, "Fecha Nacimiento", birthDateFmt);
UserAddOptionalFieldToXml(iterDoc, userOptional, "Género", rdr["DescSexo"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "Móvil", rdr["Movil"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "Teléfono Fijo", rdr["Telefono"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "Dirección", rdr["Direccion"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "País de residencia", rdr["DescPais"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "Nivel de educación", rdr["Estudios"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "Departamento", rdr["DescDepto"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "Ocupacion", rdr["Ocupacion"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "Ciudad", rdr["DescCiudad"].ToString());
String mapActive = "";
var a = (Boolean)rdr["Activo"];
if (a) {
mapActive = "1";
}
else {
mapActive = "0";
}
UserAddOptionalFieldToXml(iterDoc, userOptional, "He leído y acepto los Términos y Condiciones", mapActive);
UserAddOptionalFieldToXml(iterDoc, userOptional, "Recibir titulares diarios", rdr["AceptaTitulares"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "He leído y acepto el Uso de mi información personal", rdr["AceptaOfertas"].ToString());
UserAddOptionalFieldToXml(iterDoc, userOptional, "He leído y acepto el Uso de mi información por terceros", rdr["AceptaOfertasTerceros"].ToString());
i++;
if (i >= numRecsPerFile) {
SaveXmlDocument(manifestFile, iterDoc);
i = 0;
j++;
isIterWebManifestFileAffected = true;
GC.Collect();
}
}
if (iterDoc != null) {
SaveXmlDocument(manifestFile, iterDoc);
cnts.IterWebManifestFile++;
isIterWebManifestFileAffected = true;
GC.Collect();
}
rdr.Close();
transaction.Commit();
hdb.Close();
ExecuteSqlStmtBatch("update users set processed = 1 where processed = 0;");
string s = counter + " users exported";
Console.WriteLine();
Console.WriteLine(s);
if (log.IsInfoEnabled) log.Info(s);
if (log.IsDebugEnabled) {
log.Debug("UserExport End");
}
}
/// <summary>
/// Used to generate value for field UrlTitle in sitemap table.
/// </summary>
private void ComputeUrlTitle() {
if (log.IsDebugEnabled) {
log.Debug("ComputeUrlTitle Start");
}
long counter = 0;
string line = "ComputeUrlTitle Init";
Console.WriteLine(line);
if (log.IsInfoEnabled) {
log.Info(line);
}
Dictionary<long, String> urlTitleList = new Dictionary<long, String>();
string sql = "select idSitemap, urlPath from sitemap where urlTitle is null";
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("ComputeUrlTitle");
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read()) {
counter++;
Console.Write(counter + "\r");
var urlPath = rdr["urlPath"].ToString();
var id = Convert.ToInt32(rdr["idSitemap"].ToString());
if (urlPath != "") {
urlPath = urlPath.ExtractUrlTitle();
urlTitleList.Add(id, urlPath);
}
}
Console.WriteLine();
rdr.Close();
transaction.Commit();
line = "ComputeUrlTitle Updating database";
Console.WriteLine(line);
if (log.IsInfoEnabled) {
log.Info(line);
}
long counter1 = 0;
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@urltitle";
param1.SqlDbType = SqlDbType.VarChar;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@id";
param2.SqlDbType = SqlDbType.Int;
transaction = hdb.BeginTransaction("ComputeUrlTitleUpdate");
foreach (var pair in urlTitleList) {
counter1++;
Console.Write(counter1 + "\r");
sql = "update sitemap set urltitle = @urltitle where idsitemap = @id";
param1.Value = pair.Value;
param2.Value = pair.Key;
hdb.ExecSQLStmt(transaction, sql, param1, param2);
}
transaction.Commit();
hdb.Close();
Console.WriteLine();
line = "ComputeUrlTitle processed " + counter + " items";
Console.WriteLine(line);
if (log.IsInfoEnabled) {
log.Info(line);
}
if (log.IsDebugEnabled) {
log.Debug("ComputeUrlTitle End");
}
}
/// <summary>
/// Gets all useful records and validates that their URLs don't contain non-ascii characters, if one is found
/// it is reported to both screen and log.
/// </summary>
private void ValidateURLChars() {
if (log.IsDebugEnabled) {
log.Debug("ValidateURLChars Start");
}
long numInvalidSE4Docs = 0;
long numInvalidOldDocs = 0;
string msg = "";
string loadOldDocuments = "";
string loadSE4Documents = "";
string se4LayoutList = _se4LayoutToFilter;
string layoutFilter = "";
loadOldDocuments = "select idSitemap, url, urlPath from sitemap " +
"where idOld is not null and idSE4 is null and oldDocStatus = 2 and url is not null";
loadSE4Documents = "select idSitemap, url, urlPath from sitemap " +
"where idSE4 is not null and url is not null @layoutfilter@ and urlParameters = '' and processed = 0 ";
if (se4LayoutList == "") {
layoutFilter = "";
}
else {
layoutFilter = "and layout in (@se4layoutlist@) ";
layoutFilter = layoutFilter.Replace("@se4layoutlist@", se4LayoutList);
}
loadSE4Documents = loadSE4Documents.Replace("@layoutfilter@", layoutFilter);
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("DeleteInvalidUrls");
hdb.ExecSQLStmt(transaction, "delete from invalidurls");
transaction.Commit();
transaction = hdb.BeginTransaction("ValidateURLChars");
// 1. Loads valid SE4Documents to validate their URLs
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, loadSE4Documents);
numInvalidSE4Docs = 0;
while (rdr.Read()) {
string url = rdr["url"].ToString();
string urlPath = rdr["urlPath"].ToString();
long idSitemap = Convert.ToInt32(rdr["idSitemap"].ToString());
if (!url.IsUrlWithValidChars()) {
msg = "SE4Doc Url at idSitemap=[" + idSitemap + "] is not ASCII conformant. URL=[" + url + "]";
Console.WriteLine(msg);
if (log.IsInfoEnabled) {
log.Info(msg);
}
numInvalidSE4Docs++;
SaveToInvalidUrlTable(idSitemap, url, urlPath, 1);
}
}
rdr.Close();
// 2. Loads Old documents to validate their URLs.
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, loadOldDocuments);
numInvalidOldDocs = 0;
while (rdr.Read()) {
string url = rdr["url"].ToString();
string urlPath = rdr["urlPath"].ToString();
long idSitemap = Convert.ToInt32(rdr["idSitemap"].ToString());
if (!url.IsUrlWithValidChars()) {
msg = "Old DOC Url at idSitemap=[" + idSitemap + "] is not ASCII conformant. URL=[" + url + "]";
Console.WriteLine(msg);
if (log.IsInfoEnabled) {
log.Info(msg);
}
numInvalidOldDocs++;
SaveToInvalidUrlTable(idSitemap, url, urlPath, 2);
}
}
msg = "There are [" + numInvalidSE4Docs + "] invalid URls in SE4 Docs";
Console.WriteLine(msg);
if (log.IsInfoEnabled) {
log.Info(msg);
}
msg = "There are [" + numInvalidOldDocs + "] invalid URls in Old Docs";
Console.WriteLine(msg);
if (log.IsInfoEnabled) {
log.Info(msg);
}
rdr.Close();
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("ValidateURLChars End");
}
}
/// <summary>
/// Executes an SQL statement ant its results are saved in a text file for inspection. It uses
/// a file on disk to read the valid statement, using key 'SQLBatchFile'. NOTE: 'SQLBatchFile' file
/// must contain one and only one SQL Select statement to use and it must be UTF-8 encoded.
/// </summary>
private void ExportSQLToCSVWithSQLInFile() {
if (log.IsDebugEnabled) {
log.Debug("ExportSQLToCSVWithSQLInFile Start");
}
String[] readSQLStmts = null;
String line = "";
line = "Executing SQL Commands from file [" + _sqlBatchFile + "]";
Console.WriteLine(line);
if (log.IsInfoEnabled) {
log.Info(line);
}
if (!File.Exists(_sqlBatchFile)) {
line = "Batch file [" + _sqlBatchFile + "] does not exist";
Console.WriteLine(line);
if (log.IsErrorEnabled) {
log.Error(line);
}
}
else {
readSQLStmts = File.ReadAllLines(_sqlBatchFile, Encoding.UTF8);
if (readSQLStmts.Length == 0) {
line = "SQL File is empty";
Console.WriteLine(line);
if (log.IsErrorEnabled) {
log.Error(line);
}
}
else {
line = "Executing statement from file.";
if (log.IsInfoEnabled) {
log.Info(line);
}
bool trueSqlStmt = false;
String sqlExec = "";
foreach (string sql in readSQLStmts) {
if (sql != "") {
if (sql.Contains(';')) {
trueSqlStmt = true;
}
sqlExec += sql + " ";
if (trueSqlStmt) {
break;
}
}
}
if (log.IsWarnEnabled) {
log.Warn("Using SQL=[" + sqlExec + "]");
}
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("ExportSQLToCSVWithSQLInFile");
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sqlExec);
List<String> csvLines = rdr.ToCSV(true, ",");
// Let's save to disk
using (StreamWriter sw = new StreamWriter(_csvExportFile)) {
foreach (var csvLine in csvLines) {
sw.WriteLine(csvLine);
}
}
rdr.Close();
transaction.Commit();
hdb.Close();
}
}
if (log.IsDebugEnabled) {
log.Debug("ExportSQLToCSVWithSQLInFile End");
}
}
/// <summary>
/// Executes an SQL statement ant its results are saved in a text file for inspection.
/// </summary>
private void ExportSQLToCSV() {
if (log.IsDebugEnabled) {
log.Debug("ExportSQLToCSV Start");
}
if (_sqlExecStmt != "") {
if (log.IsInfoEnabled) {
log.Info("Using SQL=[" + _sqlExecStmt + "]");
}
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("ExportSQLToCSV");
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, _sqlExecStmt);
List<String> csvLines = rdr.ToCSV(true, ",");
// Let's save to disk
using (StreamWriter sw = new StreamWriter(_csvExportFile)) {
foreach (var line in csvLines) {
sw.WriteLine(line);
}
}
rdr.Close();
transaction.Commit();
hdb.Close();
}
else {
var s = "No SQL statement to execute";
Console.WriteLine(s);
if (log.IsInfoEnabled) {
log.Info(s);
}
}
if (log.IsDebugEnabled) {
log.Debug("ExportSQLToCSV End");
}
}
/// <summary>
/// Given 'idDoc' retrieves vocabularies if set for it.
/// </summary>
/// <param name="idDoc">Id of document to retrieve.</param>
/// <returns></returns>
private List<VocabularyDefinition> RetrieveVocabulariesForDoc(long idDoc) {
List<VocabularyDefinition> rslt = new List<VocabularyDefinition>();
string sql = "";
if (log.IsDebugEnabled) {
log.Debug("RetrieveVocabulariesForDoc Start");
}
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("RetrieveVocabulariesForDoc");
sql += "select distinct B.Description CatalogName, C.CategoryPath, C.Description CategoryName ";
sql += "from ArticleSectionCatalogCategory A inner join ";
sql += " catalognames B on A.idCatalog = B.idCatalog inner join ";
sql += " Categories C on A.idCategory = C.idCategory inner join ";
sql += " articlecategories D on A.idSection = D.idSeccion inner join ";
sql += " ArticleSectionCategory E on A.idSection = E.idSeccion ";
sql += "where E.usar = 1 and D.id_ObjetoSE = @iddoc ";
sql += "order by CatalogName, CategoryName ";
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@iddoc";
param1.SqlDbType = SqlDbType.Int;
param1.Value = idDoc;
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param1);
if (rdr.HasRows) {
int i = 0;
String oldCatalogName = "";
String catalogName = "";
String categoryName = "";
String categoryNamePath = "";
VocabularyDefinition vd = null;
while (rdr.Read()) {
catalogName = rdr["CatalogName"].ToString();
categoryName = rdr["CategoryName"].ToString();
categoryNamePath = rdr["CategoryPath"].ToString();
if (oldCatalogName != catalogName) {
vd = new VocabularyDefinition();
vd.Categories = new List<CategoryDefinition>();
vd.ApplyTo = GlobalConstants.ARTICLE_HISTORICAL_GENERAL;
oldCatalogName = vd.Name = catalogName.Trim();
rslt.Add(vd);
i = 0;
}
CategoryDefinition cd = new CategoryDefinition();
cd.Name = categoryName.Trim();
cd.NamePath = categoryNamePath.Trim();
cd.SetAttribute = 1;
cd.Main = 0;
if (i == 0) {
cd.Main = 1;
}
vd.Categories.Add(cd);
i++;
}
}
rdr.Close();
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("RetrieveVocabulariesForDoc End");
}
return rslt;
}
/// <summary>
/// Given the id for document in SE4, it loads all of its attribute elements to further processing.
/// </summary>
/// <param name="p">id of document to retrieve</param>
/// <param name="layout">Name of layout to check fields against</param>
/// <returns>List of useful attributes for document</returns>
private List<SE4Attribute> LoadSE4DocItems(long p, string layout) {
if (log.IsDebugEnabled) log.Debug("LoadSE4DocItems start");
string elementName = "";
List<SE4Attribute> docAttrList = new List<SE4Attribute>();
List<ValidMapField> documentValidFields = se4TemplateFieldMapping[layout];
string sql = "select A.Orden, A.Elemento, A.OrdenAtributo, A.Atributo, A.Texto, A.CodContenidoLargo, B.ContenidoLargo " +
" from objetosecontenido A left join " +
" contenidolargo B on A.CodContenidoLargo = B.id_ContenidoLargo " +
" where Codobjetose = @idSE4 " +
"order by A.CodObjetoSE, A.orden, A.OrdenAtributo ";
if (log.IsDebugEnabled) {
log.Debug("Using sql=[" + sql + "]");
}
HandleDatabase hdb = new HandleDatabase(_connStr);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@idSE4";
param1.Value = p;
param1.SqlDbType = SqlDbType.BigInt;
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("LoadSE4DocItems");
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param1);
while (rdr.Read()) {
elementName = rdr["Elemento"].ToString();
if (documentValidFields.Exists(e => e.Source == elementName && e.Use)) {
int order = Convert.ToInt32(rdr["Orden"]);
int attributeOrder = Convert.ToInt32(rdr["OrdenAtributo"]);
string attributeName = rdr["Atributo"].ToString();
string text = rdr["Texto"].ToString();
long hugeContentCode = -1;
string hugeText = null;
SE4Attribute attr = null;
if (rdr["CodContenidoLargo"] != DBNull.Value) {
hugeContentCode = Convert.ToInt64(rdr["CodContenidoLargo"]);
}
if (rdr["ContenidoLargo"] != DBNull.Value) {
hugeText = rdr["ContenidoLargo"].ToString();
}
text = ReplaceHTMLCharacters(text);
hugeText = ReplaceHTMLCharacters(hugeText);
ValidMapField vmf = documentValidFields.Find(e => e.Source == elementName && e.Use);
if (vmf.Attributes != null) {
ValidMapFieldAttributes vmfa = vmf.Attributes.Find(e => e.Source == elementName && e.SourceAttribute == attributeName && e.Use);
if (vmfa == null) {
continue;
}
}
attr = new SE4Attribute(order, elementName, attributeOrder, attributeName, text, hugeContentCode, hugeText);
docAttrList.Add(attr);
}
}
rdr.Close();
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) log.Debug("LoadSE4DocItems end");
return docAttrList;
}
