コード例 #1
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
            }
        }
コード例 #2
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
            }
        }
コード例 #3
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
            }
        }
コード例 #4
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
 /// <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");
     }
 }
コード例 #5
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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;
        }
コード例 #6
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
        }
コード例 #7
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
            }
        }
コード例 #8
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
            }
        }
コード例 #9
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
 /// <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");
     }
 }
コード例 #10
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
            }

        }
コード例 #11
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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;
        }
コード例 #12
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
        }
コード例 #13
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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();
        }
コード例 #14
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
            }
        }
コード例 #15
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
            }
        }
コード例 #16
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
            }
        }
コード例 #17
0
ファイル: SE4DocMigrate.cs プロジェクト: alpermazlum/try_git
        /// <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");
            }
        }
コード例 #18
0
        /// <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();
            }
        }
コード例 #19
0
        /// <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();
            }
        }