/// <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>
/// Invokes sp 'sp_syncmigrationdb' to update index table used as the migration-processing-control data.
/// </summary>
private void SynchronizeDBData() {
if (log.IsDebugEnabled) {
log.Debug("SynchronizeDBData Start");
}
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("SynchronizeDBData");
hdb.ExecSQLStmt(transaction, "execute sp_syncmigrationdb");
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("SynchronizeDBData End");
}
}
/// <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;
}
/// <summary>
/// Stores in 'InvalidUrls' database table as reference.
/// </summary>
/// <param name="idSitemap">Sitemap record to use as reference</param>
/// <param name="url">Invalid url</param>
/// <param name="p">kind of record, 1: SE4 doc, 2: Old doc</param>
private void SaveToInvalidUrlTable(long idSitemap, string url, string urlPath, int p) {
if (log.IsDebugEnabled) log.Debug("SaveToInvalidUrlTable Start");
string sqlToUse = "insert into invalidurls(id, url, urlencoded, kind) values (@sitemap, @url, @urlencoded, @kind) ";
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@sitemap";
param1.Value = idSitemap;
param1.SqlDbType = SqlDbType.Int;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@url";
param2.Value = url;
param2.SqlDbType = SqlDbType.VarChar;
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@kind";
param3.Value = p;
param3.SqlDbType = SqlDbType.Int;
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@urlencoded";
param4.Value = HttpUtility.UrlEncode(urlPath);
param4.SqlDbType = SqlDbType.VarChar;
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("SaveToInvalidUrlTable");
hdb.ExecSQLStmt(transaction, sqlToUse, param1, param2, param3, param4);
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) log.Debug("SaveToInvalidUrlTable End");
}
/// <summary>
/// Exec an SQL statement in batch.
/// NOTE: Reads a file from 'SQLBatchFile' configuration parameter and assumes a true SQL statement
/// (which ends with semicolon) is read and executed accordingly and Batch file must be UTF-8 encoded.
/// SQL must end with semi-colon. SQL msut not contain any commentary in it.
/// </summary>
private void ExecuteSqlStmtBatch() {
if (log.IsDebugEnabled) {
log.Debug("ExecuteSqlStmtBatch 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 Batch File is empty";
Console.WriteLine(line);
if (log.IsErrorEnabled) {
log.Error(line);
}
}
else {
line = "Executing statements";
if (log.IsInfoEnabled) {
log.Info(line);
}
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("ExecuteSqlStmtBatch");
try {
bool trueSqlStmt = false;
String sqlExec = "";
foreach (string sql in readSQLStmts) {
if (sql != "") {
if (sql.Contains(';')) {
trueSqlStmt = true;
}
sqlExec += sql + " ";
if (trueSqlStmt) {
if (log.IsWarnEnabled) {
log.Warn("Executing SQL=[" + sqlExec + "]");
}
hdb.ExecSQLStmt(transaction, sqlExec);
trueSqlStmt = false;
sqlExec = "";
}
}
}
transaction.Commit();
}
catch (Exception e) {
Console.WriteLine("Exception caught (ExecuteSqlStmtBatch). See log for details");
line = "Processing aborted by Exception raised ";
Console.WriteLine(line);
if (log.IsErrorEnabled) {
log.Error(line);
log.Error(e.Message);
log.Error(e.StackTrace);
}
transaction.Rollback();
}
hdb.Close();
}
}
if (log.IsDebugEnabled) {
log.Debug("ExecuteSqlStmtBatch End");
}
}
/// <summary>
/// Load directory into temporary table in order to check if new files exist
/// (files are sync'ed in database.
/// </summary>
private void LoadToonsIntoTemp() {
String folderPath = _se4MediaSourceFolder + @"\Caricaturas2";
if (log.IsDebugEnabled) {
log.Debug("LoadToonsIntoTemp Start");
}
string sql = "insert into toondirtmp(fromdir, filename, dateProcessed, CreateDate, UpdateDate, processed, idArticle) values(@fromdir, @filename, @dateProcessed, @CreateDate, @UpdateDate, @processed, @idArticle)";
var files = from file in Directory.GetFiles(folderPath, "caric*_G.jpg")
orderby file descending
select file;
long sequence = 6000000L;
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@fromdir";
param1.SqlDbType = SqlDbType.VarChar;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@filename";
param2.SqlDbType = SqlDbType.VarChar;
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@dateProcessed";
param3.SqlDbType = SqlDbType.DateTime;
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@CreateDate";
param4.SqlDbType = SqlDbType.DateTime;
SqlParameter param5 = new SqlParameter();
param5.ParameterName = "@UpdateDate";
param5.SqlDbType = SqlDbType.DateTime;
SqlParameter param6 = new SqlParameter();
param6.ParameterName = "@processed";
param6.SqlDbType = SqlDbType.Int;
SqlParameter param7 = new SqlParameter();
param7.ParameterName = "@idArticle";
param7.SqlDbType = SqlDbType.VarChar;
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("LoadToonsIntoTemp");
hdb.ExecSQLStmt(transaction, "delete from toondirtmp");
foreach (var file in files) {
param1.Value = folderPath;
param2.Value = Path.GetFileName(file);
param3.Value = DateTime.Now;
param4.Value = param5.Value = File.GetLastWriteTime(file);
param6.Value = 0;
param7.Value = "EC_" + sequence++;
hdb.ExecSQLStmt(transaction, sql, param1, param2, param3, param4, param5, param6, param7);
}
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("LoadToonsIntoTemp End");
}
}
/// <summary>
/// Exec an SQL statement.
/// </summary>
private void ExecuteSqlStmt() {
if (log.IsDebugEnabled) {
log.Debug("ExecuteSqlStmt Start");
}
if (_sqlExecStmt != "") {
if (log.IsWarnEnabled) {
log.Warn("Using SQL=[" + _sqlExecStmt + "]");
}
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("ExecuteSqlStmt");
hdb.ExecSQLStmt(transaction, _sqlExecStmt);
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("ExecuteSqlStmt End");
}
}
/// <summary>
/// Exec an SQL statement in batch supplied by sql parameter. SQL must end with semi-colon. SQL msut not contain any commentary in it.
/// </summary>
/// <param name="sqlBatchProvided">In memory SQL to act upon.</param>
private void ExecuteSqlStmtBatch(string sqlBatchSupplied) {
if (log.IsDebugEnabled) {
log.Debug("ExecuteSqlStmtBatch(string) Start");
}
String[] readSQLStmts = null;
String line = "";
line = "Executing SQL Commands from [" + sqlBatchSupplied + "]";
Console.WriteLine(line);
if (log.IsInfoEnabled) {
log.Info(line);
}
line = "Executing statements";
if (log.IsInfoEnabled) {
log.Info(line);
}
readSQLStmts = sqlBatchSupplied.Split(';');
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("ExecuteSqlStmtBatch1");
try {
String sqlExec = "";
foreach (string sql in readSQLStmts) {
if (sql != "") {
sqlExec = sql;
if (log.IsWarnEnabled) {
log.Warn("Executing SQL=[" + sqlExec + "]");
}
hdb.ExecSQLStmt(transaction, sqlExec);
sqlExec = "";
}
}
transaction.Commit();
}
catch (Exception e) {
Console.WriteLine("Exception caught (ExecuteSqlStmtBatch). See log for details");
line = "Processing aborted by Exception raised ";
Console.WriteLine(line);
if (log.IsErrorEnabled) {
log.Error(line);
log.Error(e.Message);
log.Error(e.StackTrace);
}
transaction.Rollback();
}
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("ExecuteSqlStmtBatch(string) 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>
/// Marks records in list as being processed. Further iterations should not use that record again.
/// </summary>
/// <param name="processedIds">List of Documents to update.</param>
/// <param name="zipFileName">Indicates all these 'processedIds' are stored in this zip file.</param>
private void MarkToonDirRecordsAsProcessed(List<ToonDirInfo> processedIds, string zipFileName) {
if (log.IsDebugEnabled) log.Debug("MarkToonDirRecordsAsProcessed start");
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("MarkToonDirRecordsAsProcessed");
String sql = "update toondir set processed = @processed, zipFilename = @zipFileName where id = @id";
SqlParameter param1 = new SqlParameter();
SqlParameter param2 = new SqlParameter();
SqlParameter param3 = new SqlParameter();
param1.ParameterName = "@id";
param1.SqlDbType = SqlDbType.Int;
param2.ParameterName = "@zipFileName";
param2.SqlDbType = SqlDbType.VarChar;
param3.ParameterName = "@processed";
param3.SqlDbType = SqlDbType.Int;
foreach (var it in processedIds) {
param1.Value = it.Id;
param2.Value = zipFileName;
param3.Value = (it.Processed == 0 ? 1 : it.Processed);
hdb.ExecSQLStmt(transaction, sql, param1, param2, param3);
}
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) log.Debug("MarkToonDirRecordsAsProcessed end");
}
/// <summary>
/// Updates old document Template Type based in the jSonContent.
/// NOTE: Used only once.
/// </summary>
protected void OldDocUpdateTemplateType() {
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("SiteMapUpdate");
foreach (var item in _se4DocList) {
Doc examinedDoc = JsonConvert.DeserializeObject<Doc>(item.JsonContent);
if (examinedDoc != null) {
int templateType = examinedDoc.TemplateType;
string sql = "Update sitemap set oldDocTemplateType = @templateType where idOld = @id";
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@templateType";
param4.Value = examinedDoc.TemplateType;
param4.SqlDbType = SqlDbType.Int;
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@id";
param3.Value = item.IDOldDoc;
param3.SqlDbType = SqlDbType.VarChar;
hdb.ExecSQLStmt(transaction, sql, param3, param4);
}
}
transaction.Commit();
hdb.Close();
}
/// <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>
/// 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>
/// Check if table 'toondirtmp' has newer records to insert into 'toondir'
/// </summary>
private void SyncToonInfo() {
string sql = "";
if (log.IsDebugEnabled) {
log.Debug("SyncToonInfo Start");
}
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("SyncToonInfo");
sql += "insert into toondir(idArticle, fromdir, filename, dateprocessed, createdate, updatedate, processed) ";
sql += "select idArticle, fromdir, filename, dateprocessed, createdate, updatedate, processed ";
sql += "from toondirtmp a ";
sql += "where filename not in (select filename from toondir) ";
hdb.ExecSQLStmt(transaction, sql);
sql = "update toondir set idArticle = 'EC_' + cast((id + 6000000) as varchar(255))";
hdb.ExecSQLStmt(transaction, sql);
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("SyncToonInfo 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>
/// For every sitemap XML file, it is stored to the sitemap table onto the database.
/// </summary>
/// <param name="urlList">The list of sitemap XML files</param>
public void SaveToDatabase(List <string> urlList)
{
if (urlList.Count > 0)
{
string sql = "insert into sitemap(url, urlHost, urlPath, urlParameters, source, filename, dateProcessed, sitemapType, filter) values(@url, @urlHost, @urlPath, @urlParameters, @source, @filename, @dateProcessed, @sitemapType, @filter)";
string sqlDel = "delete from sitemap where filename = @filename";
string sqlSel = "select count(1) cnt from sitemap with (index(ix_url)) where url = @url";
HandleDatabase hdb = new HandleDatabase();
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@url";
param1.Value = "";
param1.SqlDbType = SqlDbType.VarChar;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@urlHost";
param2.Value = "";
param2.SqlDbType = SqlDbType.VarChar;
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@urlPath";
param3.Value = "";
param3.SqlDbType = SqlDbType.VarChar;
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@urlParameters";
param4.Value = "";
param4.SqlDbType = SqlDbType.VarChar;
SqlParameter param5 = new SqlParameter();
param5.ParameterName = "@source";
param5.Value = "";
param5.SqlDbType = SqlDbType.VarChar;
SqlParameter param6 = new SqlParameter();
param6.ParameterName = "@filename";
param6.Value = "";
param6.SqlDbType = SqlDbType.VarChar;
SqlParameter param7 = new SqlParameter();
param7.ParameterName = "@dateProcessed";
param7.Value = DateTime.Now;
param7.SqlDbType = SqlDbType.DateTime;
SqlParameter param8 = new SqlParameter();
param8.ParameterName = "@sitemapType";
param8.Value = 0;
param8.SqlDbType = SqlDbType.Int;
SqlParameter param9 = new SqlParameter();
param9.ParameterName = "@filter";
param9.Value = 0;
param9.SqlDbType = SqlDbType.VarChar;
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("sitemap");
//SqlTransaction trnDelete = hdb.BeginTransaction("sitemapDelete");
param6.Value = FileName;
hdb.ExecSQLStmt(transaction, sqlDel, param6);
//trnDelete.Commit();
foreach (var url in urlList)
{
Uri uri = new Uri(url);
param1.Value = url;
param2.Value = uri.Host;
param3.Value = uri.LocalPath;
param4.Value = uri.Query;
param5.Value = Source;
param6.Value = FileName;
param7.Value = DateTime.Now;
param8.Value = SitemapType;
param9.Value = Filter;
//SqlTransaction trnSel = hdb.BeginTransaction("sitemapSel");
int cnt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sqlSel, param1);
//trnSel.Commit();
if (cnt == 0)
{
hdb.ExecSQLStmt(transaction, sql, param1, param2, param3, param4, param5, param6, param7, param8, param9);
}
}
transaction.Commit();
hdb.Close();
}
}
/// <summary>
/// For every sitemap XML file, it is stored to the sitemap table onto the database.
/// </summary>
/// <param name="urlList">The list of sitemap XML files</param>
public void SaveToDatabase(List<string> urlList) {
if (urlList.Count > 0) {
string sql = "insert into sitemap(url, urlHost, urlPath, urlParameters, source, filename, dateProcessed, sitemapType, filter) values(@url, @urlHost, @urlPath, @urlParameters, @source, @filename, @dateProcessed, @sitemapType, @filter)";
string sqlDel = "delete from sitemap where filename = @filename";
string sqlSel = "select count(1) cnt from sitemap with (index(ix_url)) where url = @url";
HandleDatabase hdb = new HandleDatabase();
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@url";
param1.Value = "";
param1.SqlDbType = SqlDbType.VarChar;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@urlHost";
param2.Value = "";
param2.SqlDbType = SqlDbType.VarChar;
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@urlPath";
param3.Value = "";
param3.SqlDbType = SqlDbType.VarChar;
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@urlParameters";
param4.Value = "";
param4.SqlDbType = SqlDbType.VarChar;
SqlParameter param5 = new SqlParameter();
param5.ParameterName = "@source";
param5.Value = "";
param5.SqlDbType = SqlDbType.VarChar;
SqlParameter param6 = new SqlParameter();
param6.ParameterName = "@filename";
param6.Value = "";
param6.SqlDbType = SqlDbType.VarChar;
SqlParameter param7 = new SqlParameter();
param7.ParameterName = "@dateProcessed";
param7.Value = DateTime.Now;
param7.SqlDbType = SqlDbType.DateTime;
SqlParameter param8 = new SqlParameter();
param8.ParameterName = "@sitemapType";
param8.Value = 0;
param8.SqlDbType = SqlDbType.Int;
SqlParameter param9 = new SqlParameter();
param9.ParameterName = "@filter";
param9.Value = 0;
param9.SqlDbType = SqlDbType.VarChar;
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("sitemap");
//SqlTransaction trnDelete = hdb.BeginTransaction("sitemapDelete");
param6.Value = FileName;
hdb.ExecSQLStmt(transaction, sqlDel, param6);
//trnDelete.Commit();
foreach (var url in urlList) {
Uri uri = new Uri(url);
param1.Value = url;
param2.Value = uri.Host;
param3.Value = uri.LocalPath;
param4.Value = uri.Query;
param5.Value = Source;
param6.Value = FileName;
param7.Value = DateTime.Now;
param8.Value = SitemapType;
param9.Value = Filter;
//SqlTransaction trnSel = hdb.BeginTransaction("sitemapSel");
int cnt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sqlSel, param1);
//trnSel.Commit();
if (cnt == 0) {
hdb.ExecSQLStmt(transaction, sql, param1, param2, param3, param4, param5, param6, param7, param8, param9);
}
}
transaction.Commit();
hdb.Close();
}
}
