/// <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; }