Exemple #1
0
        private SqlDataReader doSQL(string qStr, bool exeReader = true)
        {
            Utils.maybe_reconnect(this, ref sqlConnection, this.config.try_again_after, this.logger);

            using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
            {
                sqlCommand.CommandText    = qStr;
                sqlCommand.CommandType    = CommandType.Text;
                sqlCommand.Notification   = null;
                sqlCommand.CommandTimeout = this.config.defaultSQLQueryTimeout;

                try
                {
                    if (exeReader)
                    {
                        return(sqlCommand.ExecuteReader());
                    }
                    else
                    {
                        sqlCommand.ExecuteNonQuery();
                        return(null);
                    }
                }
                catch (Exception error)
                {
                    if (!error.Message.Contains("already an object"))
                    {
                        this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
                        this.isReadyToBeStarted = false;
                    }
                }
            }

            return(null);
        }
Exemple #2
0
        public ServiceControlCenter(Config eConfig, Logger logger)
            : base(eConfig, logger, "control")
        {
            this.logger            = logger;
            this.eConfig           = eConfig;
            this.consumerCallback += (model, args) =>
            {
                string cmdStr      = Encoding.UTF8.GetString(args.Body);
                ulong  deliveryTag = args.DeliveryTag;
#if (DEBUG)
                Console.WriteLine("{0} - {1}", cmdStr, deliveryTag);
#endif
                try
                {
                    dynamic deserializeObject = JsonConvert.DeserializeObject(cmdStr);
                    this.procesCommand(deserializeObject, deliveryTag);
                }
                catch (Exception error)
                {
                    logger.write(String.Format("Cannot execute the command {0} because {1}", cmdStr, error.Message), Logger.LOGLEVEL.ERROR);
                    this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
                }

                this.consumerBasicACK(deliveryTag);
            };

            this.consumerInit();

            this.cpuCounter = new PerformanceCounter("Process", "% Processor Time", Process.GetCurrentProcess().ProcessName);
            this.ramCounter = new PerformanceCounter("Process", "Working Set - Private", Process.GetCurrentProcess().ProcessName);
        }
Exemple #3
0
        internal void runQueryToFile(string runQuery, string resultFile, string resultType)
        {
            System.Threading.Tasks.Task.Factory.StartNew(() =>
            {
                string cusCodeEnv = String.Format("{0}{1}", this.config.cprCustomerCode, this.config.cprCustomerEnvironment);

                try
                {
                    Utils.maybe_reconnect(this, ref this.sqlConnection, this.config.try_again_after, this.logger);

                    TableDumper.runQueryToFile(this.sqlConnection, runQuery, resultFile, cusCodeEnv, this.config.defaultSQLQueryTimeout, resultType);

                    this.logger.write(String.Format("run_query {0} into {1} file successfully and uploaded to s3.", runQuery, resultFile), Logger.LOGLEVEL.INFO);
                }
                catch (S3UploadException error)
                {
                    this.logger.write(String.Format("Cannot start uploading file {0}.gz to s3 because {1}.", resultFile, error.ToString()), Logger.LOGLEVEL.ERROR);
                }
                catch (Exception error)
                {
                    this.logger.write(String.Format("Cannot start running run_query command: {0} because {1}.", runQuery, error.ToString()), Logger.LOGLEVEL.ERROR);
                    this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
                }
            });
        }
Exemple #4
0
        public void dbSchemaDump()
        {
            string dumpSchemaQuery = @"SELECT cl.table_name as 'TABLE_NAME',
                               cl.column_name as 'COLUMN_NAME',
                               t.constraint_type as 'CONSTRAINT_TYPE',
                               cl.DATA_TYPE as 'DATA_TYPE'
                            FROM INFORMATION_SCHEMA.COLUMNS cl
                            LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
                             ON cl.table_catalog = cu.table_catalog
                               AND cl.table_schema = cu.table_schema
                               AND cl.table_name = cu.table_name
                               AND cl.column_name = cu.column_name
                            LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
                             ON cu.constraint_catalog = t.constraint_catalog
                               AND cu.constraint_schema = t.constraint_schema
                               AND cu.constraint_name = t.constraint_name";
            string csvFilename     = "schema";
            string dumpResult      = "failure";

            System.Threading.Tasks.Task.Factory.StartNew(() =>
            {
                try
                {
                    this.logger.write(String.Format(@"Dumping tables schema to {1}\{0}.csv", csvFilename, Path.GetTempPath()), Logger.LOGLEVEL.INFO);

                    TableDumper.QueryToFile(this.sqlConnection,
                                            dumpSchemaQuery,
                                            csvFilename,
                                            String.Format("{0}{1}", this.config.cprCustomerCode, this.config.cprCustomerEnvironment),
                                            this.config.defaultSQLQueryTimeout);

                    this.logger.write(String.Format("Dumped tables schema into {0} file successfully and uploaded to s3.", csvFilename), Logger.LOGLEVEL.INFO);

                    dumpResult = "success";
                }
                catch (S3UploadException error)
                {
                    this.logger.write(String.Format("Cannot start uploading file {0} to s3 because {1}.", csvFilename, error.ToString()), Logger.LOGLEVEL.ERROR);
                }
                catch (Exception error)
                {
                    this.logger.write(String.Format("Cannot start dumping tables schema because {0}.", error.ToString()), Logger.LOGLEVEL.ERROR);
                    this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
                }

                this.serviceControlCenter.publishMessage(JsonConvert.SerializeObject(new Dictionary <string, object>()
                {
                    { "customer", this.config.cprCustomerCode },
                    { "environment", this.config.cprCustomerEnvironment },
                    { "uuid", Guid.NewGuid().ToString() },
                    { "type", "dump_schema" },
                    { "result", dumpResult }
                }, new JsonSerializerSettings()
                {
                    Formatting = Formatting.Indented,
                }), "ecpr-config-s3-response", 2);
            });
        }
        private object executeQueryScalar(string cText, SqlConnection _sqlConnection)
        {
            try
            {
                if (_sqlConnection.State != ConnectionState.Open)
                {
                    _sqlConnection.Open();
                }
            }
            catch (Exception)
            {
                return(null);
            }

            SqlTransaction trans;

            trans = _sqlConnection.BeginTransaction(IsolationLevel.ReadUncommitted);

            using (SqlCommand sqlCommand = _sqlConnection.CreateCommand())
            {
                sqlCommand.CommandText    = cText;
                sqlCommand.CommandType    = CommandType.Text;
                sqlCommand.Notification   = null;
                sqlCommand.CommandTimeout = this.config.defaultSQLQueryTimeout;
                sqlCommand.Transaction    = trans;

                try
                {
                    return(sqlCommand.ExecuteScalar());
                }
                catch (System.InvalidOperationException)
                {
                    return(null);
                }
                catch (SqlException error)
                {
                    if (error.Message.Contains("error: 19") ||
                        error.Message.Contains("error: 20") ||
                        error.Message.Contains("SHUTDOWN"))
                    {
                        /* A transport-level error has occurred when sending the request to the server.
                         * (provider: Session Provider, error: 19 - Physical connection is not usable) */
                    }
                    else
                    {
                        this.logger.write(ECPRERROR.SqlExceptionDetail(error), Logger.LOGLEVEL.ERROR);
                    }
                }
            }

            return(null);
        }
        public QueryNotification(
            string[] m_tableFields,
            string m_tableName,
            Logger logger,
            Config config,
            Dictionary <string, Dictionary <string, string> > json_mapping = null,
            bool m_autoCleanupIfRestart = false)
        {
            jsonSerializerSettings.Converters.Add(new CprPlusDateTimeConvertor());

            this.tableName = m_tableName;
            this.tableFieldsNotification = m_tableFields;
            this.autoCleanupIfRestart    = m_autoCleanupIfRestart;
            this.logger       = logger;
            this.config       = config;
            this.ecpr_ids     = new List <int>();
            this.json_mapping = json_mapping;

            this.reCheckTableFields();

            buildStrCommand();
            ensureCloneTable();

            try
            {
                ensureEventsTriggerIsCreated();
            }catch (Exception error)
            {
                this.logger.write(String.Format("Cannot create or update trigger for table {0} because {1}", this.tableName, error.Message), Logger.LOGLEVEL.ERROR);
                this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
            }

            if (this.autoCleanupIfRestart)
            {
                var info_msg = String.Format("Clear data in the table ecpr_{0}_logs", this.tableName);
                this.logger.write(info_msg, Logger.LOGLEVEL.INFO);

                using (SqlConnection _sqlConnection = new SqlConnection(config.cprDatabaseConnectionString))
                {
                    this.executeNonQuery(String.Format("DELETE FROM ecpr_{0}_logs", this.tableName), _sqlConnection);
                }
            }

            this.logger.write(String.Format("Subscribed on table {0}", this.tableName), Logger.LOGLEVEL.INFO);
        }
Exemple #7
0
        private void longPoll()
        {
            if (Utils.isStartingFromWindowsRestart())
            {
                Thread.Sleep(this.config.wait_before_start_from_windows_restart * 1000);
            }

            logger.write("Checking internet/network connection...", Logger.LOGLEVEL.INFO);

            while (!NetworkMonitoring.IsInternetAvailable())
            {
                logger.write(String.Format("No internet connection. Will try again after {0} secs.", this.config.try_again_after), Logger.LOGLEVEL.ERROR);
                Thread.Sleep(this.config.try_again_after * 1000);
            }

            logger.write("Internet/network connection is succesful.", Logger.LOGLEVEL.INFO);

            this.logger.ensureConnect();

            logger.write("Checking SQL SERVER connection...", Logger.LOGLEVEL.INFO);

            string errorMessage;

            while (!Utils.try_connect_to_db(this.config.cprDatabaseConnectionString, out errorMessage))
            {
                logger.write(String.Format("Had an error while trying to connect to SQL SERVER. Will try again after {0} secs.", this.config.try_again_after), Logger.LOGLEVEL.ERROR);
                logger.write(errorMessage, Logger.LOGLEVEL.ERROR);
                Thread.Sleep(this.config.try_again_after * 1000);
            }

            logger.write("SQL SERVER connection is succesful.", Logger.LOGLEVEL.INFO);

            logger.write("STARTING ECPR service...", Logger.LOGLEVEL.INFO);

            this.serviceControlCenter = new ServiceControlCenter(this.config, this.logger);
            this.serviceControlCenter.addManager(this);

            this.logger.write(String.Format("Connecting to database {0} ...", this.config.cprDatabase), Logger.LOGLEVEL.INFO);

            this.sqlConnection = new SqlConnection(connectionString);
            Utils.maybe_reconnect(this, ref this.sqlConnection, this.config.try_again_after, this.logger);

            System.Timers.Timer ecprTimer = new System.Timers.Timer();
            ecprTimer.Elapsed  += (sender, e) => Utils.maybe_reconnect(this, ref this.sqlConnection, this.config.try_again_after, this.logger);
            ecprTimer.Interval += (this.config.defaultDatabaseConnectionTimeout * 1000 + 5000);
            ecprTimer.Enabled   = true;

            if (isReadyToBeStarted)
            {
                this.ensureTablesExisting();
                this.ensureFieldsExisting();
                this.getIgnoreFieldsPK();
            }

            if (sqlConnection.State == ConnectionState.Open)
            {
                this.syncControlCenter     = new SyncControlCenter(this.config, this.logger);
                this.fullsyncControlCenter = new FullSyncControlCenter(this.config, this.logger);
            }


            if ((syncControlCenter != null && !syncControlCenter.isSubscribed) ||
                (serviceControlCenter != null && !serviceControlCenter.isSubscribed))
            {
                logger.write(
                    String.Format("Cannot subscribed to amqp channels, will try again in {0} secs !!!!!!!", intervalSecs / 1000),
                    Logger.LOGLEVEL.ERROR);
            }

            if (!this.config.needToFetchSchemaAndData)
            {
                dbSchemaPush();
                dbDataPush();
                this.config.needToFetchSchemaAndData = true;
            }

            if (sqlConnection.State == ConnectionState.Open)
            {
                this.isReadyToBeStarted = true;

                foreach (string tableName in this.config.cprTables.Keys)
                {
                    try
                    {
                        // Make sure table is exists before subscribe
                        bool          tableExists   = false;
                        SqlDataReader sqlDataReader = this.doSQL(String.Format("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME ='{0}'", tableName));

                        if (sqlDataReader != null)
                        {
                            if (sqlDataReader.HasRows)
                            {
                                tableExists = true;
                            }
                            sqlDataReader.Close();
                        }

                        if (tableExists)
                        {
                            var qN = new QueryNotification(
                                this.config.cprTables[tableName].ToArray(),
                                tableName,
                                this.logger,
                                this.config,
                                this.config.restListAPIJSONMapping,
                                this.config.autoCleanupIfRestart);
                            qN.subscribe();
                        }
                        else
                        {
                            this.logger.write(String.Format("Cannot subscribed the table {0} for query notification because it doesn't exists in database. Ignored it!", tableName), Logger.LOGLEVEL.ERROR);
                        }
                    }
                    catch (Exception error)
                    {
                        this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
                        this.isReadyToBeStarted = false;
                    }
                }
            }

            if (this.config.diff_tables != null && this.isReadyToBeStarted)
            {
                this.logger.write("Enable DIFF Tables feature.", Logger.LOGLEVEL.INFO);
                cprDataAnalysis = new CPRDataAnalysis(this.config, this.logger);
                cprDataAnalysis.Init();
            }


            this._eventsThread = new Thread(eventsProcess);
            this._eventsThread.IsBackground = false;
            this._eventsThread.Start();

            this._eventsTimer           = new System.Timers.Timer(this.intervalSecs);
            this._eventsTimer.AutoReset = false;
            this._eventsTimer.Elapsed  += (sender, e) => this._inProcessEvent.Set();
            this._eventsTimer.Start();

            logger.write("SERVICE STARTED!!!", Logger.LOGLEVEL.INFO);
        }
Exemple #8
0
        internal void dumpTable(List <string> tableList, List <string> exclude_columns = null, string export_format = "csv", string filterCond = null)
        {
            System.Threading.Tasks.Task.Factory.StartNew(() =>
            {
                foreach (string tableName in tableList)
                {
                    try
                    {
                        string fieldsName = "*";

                        if (this.config.dumpIgnoreFields != null && this.config.dumpIgnoreFields.ContainsKey(tableName.ToUpper()))
                        {
                            exclude_columns = new List <string>(this.config.dumpIgnoreFields[tableName.ToUpper()]);
                        }

                        if (exclude_columns != null && exclude_columns.Count > 0)
                        {
                            using (var command = new SqlCommand(String.Format("select lower(COLUMN_NAME) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '{0}' and COLUMN_NAME not in ({1})", tableName, "'" + String.Join("','", exclude_columns.ToArray()) + "'"), this.sqlConnection))
                            {
                                command.CommandTimeout = this.config.defaultSQLQueryTimeout;

                                using (var reader = command.ExecuteReader())
                                {
                                    if (reader.HasRows)
                                    {
                                        exclude_columns.Clear();
                                        while (reader.Read())
                                        {
                                            exclude_columns.Add(reader.GetValue(0).ToString());
                                        }
                                        fieldsName = String.Join(",", exclude_columns.ToArray());
                                    }
                                }
                            }
                        }

                        this.logger.write(String.Format("Dumping table {0} to {1}", tableName, Path.GetTempPath()), Logger.LOGLEVEL.INFO);

                        using (SqlConnection _sqlConnection = new SqlConnection(this.config.cprDatabaseConnectionString))
                        {
                            try
                            {
                                if (_sqlConnection.State != ConnectionState.Open)
                                {
                                    _sqlConnection.Open();
                                }
                            }
                            catch (Exception) { }

                            if (export_format.ToLower() == "json")
                            {
                                TableDumper.DumpTableToFile_JSON(_sqlConnection, tableName, fieldsName, filterCond, String.Format("{0}{1}", this.config.cprCustomerCode, this.config.cprCustomerEnvironment), this.config.defaultSQLQueryTimeout);
                            }
                            else
                            {
                                TableDumper.DumpTableToFile(_sqlConnection, tableName, fieldsName, filterCond, String.Format("{0}{1}", this.config.cprCustomerCode, this.config.cprCustomerEnvironment), this.config.defaultSQLQueryTimeout);
                            }
                        }

                        this.logger.write(String.Format("Dumped table {0} into {1} file successfully and uploaded to s3.", tableName, export_format), Logger.LOGLEVEL.INFO);

                        string downloadLink = S3Uploader.GenerateDownloadLink(tableName, this.config.cprCustomerCode, this.config.cprCustomerEnvironment, export_format);

                        this.serviceControlCenter.publishMessage(JsonConvert.SerializeObject(new Dictionary <string, object>()
                        {
                            { "customer", this.config.cprCustomerCode },
                            { "environment", this.config.cprCustomerEnvironment },
                            { "uuid", Guid.NewGuid().ToString() },
                            { "type", "url" },
                            { "table_name", tableName },
                            { "url", downloadLink }
                        }, new JsonSerializerSettings()
                        {
                            Formatting = Formatting.Indented,
                        }), "ecpr-config-s3-response", 2);
                    }
                    catch (S3UploadException error)
                    {
                        this.logger.write(String.Format("Cannot start uploading file {0}.{1}.gz to s3 because {2}.", tableName, export_format, error.ToString()), Logger.LOGLEVEL.ERROR);
                    }
                    catch (Exception error)
                    {
                        this.logger.write(String.Format("Cannot start dumping table {0} because {1}.", tableName, error.ToString()), Logger.LOGLEVEL.ERROR);
                        this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
                    }
                }
            });
        }
Exemple #9
0
        private void procesCommand(dynamic cmdObj, ulong deliveryTag)
        {
            if (cmdObj.config != null)
            {
                string newConfigURL = cmdObj.config;
                this.logger.write(String.Format("Change ecpr config to {0}, it need to be restarted the window service.", newConfigURL), Logger.LOGLEVEL.INFO);
                this.consumerBasicACK(deliveryTag);
                this.eConfig.changeConfigURL(newConfigURL);
                this.objManager.dbTablesCleanUp();
                this.logger.write("RESTARTING SERVICE !!!", Logger.LOGLEVEL.INFO);
                Environment.Exit(1);
            }
            else if (cmdObj.admin != null)
            {
                string subCommand = cmdObj.admin;
                switch (subCommand)
                {
                case "schema":
                    if (this.objManager != null)
                    {
                        this.objManager.dbSchemaPush();
                    }
                    break;

                case "dump_schema":
                    if (this.objManager != null)
                    {
                        this.objManager.dbSchemaDump();
                    }
                    break;

                case "fullsync":
                    if (this.objManager != null)
                    {
                        this.objManager.dbDataPush();
                    }
                    break;

                case "restart":
                    this.consumerBasicACK(deliveryTag);
                    restartService();
                    break;

                case "reload":
                    this.consumerBasicACK(deliveryTag);
                    reloadService();
                    restartService();
                    break;

                case "timezone":
                    this.get_UTC_timezone();
                    break;

                case "ping":
                    System.Threading.Tasks.Task.Factory.StartNew(() =>
                    {
                        var pingRespData = new PingResponseData();
                        string errorMessage;
                        pingRespData.type                 = "pong";
                        pingRespData.memory_usage         = Math.Round((decimal)((this.ramCounter.NextValue() / 1024) / 1024), 2);
                        pingRespData.cpu_usage            = Math.Round((decimal)this.cpuCounter.NextValue(), 2);
                        pingRespData.db_connection_status = Utils.try_connect_to_db(this.eConfig.cprDatabaseConnectionString, out errorMessage) ? "GOOD" : "DISCONNECTED";
                        pingRespData.uuid                 = cmdObj.uuid != null ? cmdObj.uuid : Guid.NewGuid().ToString();
                        this.publishMessage(JsonConvert.SerializeObject(pingRespData), "ecpr-config-response", 1);
                    });
                    break;

                case "update":
                    string service_name = ConfigurationManager.AppSettings.Get("ecpr_service_name");

                    int command_code = 255;
                    if (service_name.Contains("stable"))
                    {
                        command_code = 128;
                    }
                    else if (service_name.Contains("demo"))
                    {
                        command_code = 129;
                    }
                    else if (service_name.Contains("sandbox"))
                    {
                        command_code = 130;
                    }
                    else if (service_name.Contains("beach"))
                    {
                        command_code = 131;
                    }
                    else if (service_name.Contains("testing"))
                    {
                        command_code = 132;
                    }
                    else if (service_name.Contains("unstable"))
                    {
                        command_code = 133;
                    }

                    Console.WriteLine(service_name);
                    Console.WriteLine(command_code);

                    try
                    {
                        ServiceController serviceController = new ServiceController("ECPRAutoUpdate");
                        serviceController.ExecuteCommand(command_code);

                        this.logger.write("Sent update command to ECPR Auto Update service.");
                    }
                    catch (Exception error)
                    {
                        this.logger.write(String.Format("Cannot run update ecpr because {0}", error.Message), Logger.LOGLEVEL.ERROR);
                        this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
                    }

                    break;

                default:
                    logger.write(String.Format("Invalid admin command {0}", subCommand), Logger.LOGLEVEL.ERROR);
                    break;
                }
            }
            else if (cmdObj.sync != null)
            {
                string tableName = cmdObj.sync;
                this.objManager.dbDataPush(tableName, cmdObj.filter);
            }
            else if (cmdObj.schema != null)
            {
                string tableName = cmdObj.schema;
                this.objManager.dbSchemaPush(tableName);
            }
            else if (cmdObj.dump != null)
            {
                string        tableName       = cmdObj.dump;
                List <string> exclude_columns = null;
                string        filterCond      = null;

                if (tableName.Trim() == String.Empty)
                {
                    logger.write(String.Format("Invalid dump command: {0}", cmdObj), Logger.LOGLEVEL.ERROR);
                }
                else
                {
                    if (cmdObj.exclude_columns != null)
                    {
                        exclude_columns = cmdObj.exclude_columns.ToObject <List <string> >();
                    }

                    if (cmdObj.filter != null)
                    {
                        filterCond = cmdObj.filter;
                    }

                    this.objManager.dumpTable(new List <string> {
                        tableName
                    },
                                              exclude_columns,
                                              exportFormat(cmdObj), filterCond);
                }
            }
            else if (cmdObj.dumps != null)
            {
                List <string> listTables = cmdObj.dumps.ToObject <List <string> >();

                if (listTables == null || listTables.Count == 0)
                {
                    logger.write(String.Format("Invalid dumps command: {0}", cmdObj), Logger.LOGLEVEL.ERROR);
                }
                else
                {
                    this.objManager.dumpTable(listTables,
                                              null,
                                              exportFormat(cmdObj));
                }
            }
            else if (cmdObj.count != null)
            {
                string tableName = cmdObj.count;
                this.objManager.countTable(tableName, cmdObj.filter != null ? (string)cmdObj.filter : String.Empty);
            }
            else if (cmdObj.run_query != null)
            {
                string runQuery = cmdObj.run_query;
                runQuery = runQuery.Trim();

                // only select query is allowed
                if (runQuery.StartsWith("select", StringComparison.OrdinalIgnoreCase))
                {
                    string resultFile = String.Empty;
                    if (cmdObj.result_file != null)
                    {
                        resultFile = cmdObj.result_file;
                        resultFile = resultFile.Trim();
                    }
                    else
                    {
                        logger.write("result_file is required for run_query command.", Logger.LOGLEVEL.ERROR);
                        return;
                    }

                    string resultType = "csv";
                    if (cmdObj.result_type != null)
                    {
                        resultType = cmdObj.result_type;
                        resultType = resultType.Trim().ToLower();
                    }

                    if (resultType != "csv" && resultType != "json")
                    {
                        logger.write(String.Format("result_type = {0} is not supported for run_query command.", resultType), Logger.LOGLEVEL.ERROR);
                        return;
                    }

                    resultFile = String.Format("{0}_{1}.{2}", resultFile, DateTime.Now.ToString("MM-dd-yyyy_HH-mm-ss"), resultType);

                    this.objManager.runQueryToFile(runQuery, resultFile, resultType);
                }
                else
                {
                    logger.write("Only select query is allowed in run_query command.", Logger.LOGLEVEL.ERROR);
                }
            }
            else
            {
                logger.write(String.Format("Unknown or Invalid command {0}", cmdObj), Logger.LOGLEVEL.ERROR);
            }
        }
        private void receiveMessage(dynamic jsonData, ulong deliveryTag)
        {
            /* Receive all messages from ebridge and check if it have
             * data conflict before do update.
             *
             * for delete:
             * {
             *   "type": "delete",
             *   "source": "HR",
             *   "key": "MRN",
             *   "value": 100,
             *   "uuid": "7b832c4b-e92a-4521-9c11-575a42119348"
             *  }
             *
             * for update:
             * {
             *   "type": "update",
             *   "source": "HR",
             *   "key": "MRN",
             *   "value": 100,
             *   "uuid": "7b832c4b-e92a-4521-9c11-575a42119348",
             *   "last_seen": "07/12/2015 12:30",
             *   "data": {
             *     "first_name": "Foo"
             *   }
             * }
             *
             * for insert
             * {
             *   "type": "insert",
             *   "source": "HR",
             *   "uuid": "7b832c4b-e92a-4521-9c11-575a42119348",
             *   "data": {
             *     "first_name": "Foo",
             *     "last_name": "Bar"
             *    }
             * }
             *
             * for upsert:
             * {
             *   "type": "upsert",
             *   "source": "PRONOTES",
             *   "key": "body",
             *   "value": "%envoy_id: 123%",
             *   "uuid": "7b832c4b-e92a-4521-9c11-575a42119348",
             *   "last_seen": "07/12/2015 12:30",
             *   "data": {
             *     "body": "envoy_id: 123"
             *   }
             * }
             *
             * Determine conflict with type = update || type = upsert:
             * - if the table have touchdate field then get the value
             * and compare it with last_seen value (datetime).
             * - if last_seen < touchdate then send an error message to
             * ecpr-cud queue with the same uuid:
             *
             * {
             *   "type": "error",
             *   "error": "conflict",
             *   "uuid": "7b832c4b-e92a-4521-9c11-575a42119348"
             * }
             *
             * - if the table do not have touchdate or last_seen >= touchdate
             * then update the record.
             *
             * - if type == 'upsert' and cannot update then insert.
             *
             */

            try
            {
                string  msg_type        = jsonData.type;
                string  table_name      = jsonData.source;
                string  table_pkey      = jsonData.key;
                string  table_pval      = jsonData.value;
                string  table_last_seen = jsonData.last_seen;
                string  table_uuid      = jsonData.uuid;
                dynamic table_data      = jsonData.data;

                if (!string.IsNullOrEmpty(msg_type) && !string.IsNullOrEmpty(table_name))
                {
                    table_name = table_name.ToUpper();

                    if (msg_type == "insert")
                    {
                        string qQuery = @"
                                    SET Context_Info 0x55555
                                    INSERT INTO {0} ({1}) VALUES ({2})
                                    ";
                        string pKeys;
                        string pVals;
                        this.buildINSERTINTO(table_data, out pKeys, out pVals);

                        qQuery = String.Format(qQuery, table_name, pKeys, pVals);
                        this.executeNonQuery(qQuery);
                    }
                    else
                    {
                        if (string.IsNullOrEmpty(table_pkey) || string.IsNullOrEmpty(table_pval))
                        {
                            syncError("Cannot UPDATE or DELETE because 'key' and 'value' not found or have empty value in the message", jsonData);
                        }
                        else
                        {
                            if (msg_type == "update" || msg_type == "upsert")
                            {
                                if (string.IsNullOrEmpty(table_last_seen))
                                {
                                    syncError("Cannot UPDATE because 'last_seen' not found or have empty value in the message", jsonData);
                                }
                                else
                                {
                                    if (!this.isValidDateTimeFormat(table_last_seen))
                                    {
                                        syncError(String.Format("Cannot UPDATE because value of 'last_seen' is not correct datetime format, the correct format is: {0}", this.dateTimeFormat), jsonData);
                                    }
                                    else
                                    {
                                        string qTQuery = "SELECT TOUCHDATE FROM {0} WHERE {1}";

                                        if (table_pval.StartsWith("%") || table_pval.EndsWith("%"))
                                        {
                                            qTQuery += " LIKE ";
                                        }
                                        else
                                        {
                                            qTQuery += "=";
                                        }

                                        if (this.isNumeric(table_pval))
                                        {
                                            qTQuery += "{2}";
                                        }
                                        else
                                        {
                                            qTQuery += "'{2}'";
                                        }

                                        using (SqlConnection _sqlConnection = new SqlConnection(this.config.cprDatabaseConnectionString))
                                        {
                                            SqlDataReader sqlDataReader = this.executeQuery(String.Format(qTQuery, table_name, table_pkey, table_pval), _sqlConnection);
                                            bool          canUpdate     = true;
                                            bool          canInsert     = false;

                                            if (sqlDataReader != null)     // meant touchdate is exists in the table
                                            {
                                                if (sqlDataReader.HasRows) // meant where key=value is existing
                                                {
                                                    sqlDataReader.Read();

                                                    DateTime touchDate = sqlDataReader.GetDateTime(0);
                                                    DateTime lastSeen  = DateTime.ParseExact(table_last_seen, this.dateTimeFormat, CultureInfo.InvariantCulture);

                                                    touchDate = DateTime.ParseExact(touchDate.ToString(this.dateTimeFormat), this.dateTimeFormat, CultureInfo.InvariantCulture);

                                                    if (lastSeen < touchDate) // we have conflict
                                                    {
                                                        syncError(String.Format("Cannot UPDATE because have conflict TouchDate({0}) - LastSeen({1}).", touchDate.ToString(), lastSeen.ToString()), jsonData);
                                                        Dictionary <string, dynamic> pMsg = new Dictionary <string, dynamic> {
                                                            { "type", "error" },
                                                            { "error", "conflict" },
                                                            { "uuid", table_uuid },
                                                            { "source", table_name },
                                                            { "key", table_pkey },
                                                            { "value", jsonData.value }
                                                        };
                                                        DataChangesCache.AddItem("DATA_CONFLICT:" + table_uuid,
                                                                                 JsonConvert.SerializeObject(pMsg, Formatting.Indented));
                                                        canUpdate = false;
                                                    }
                                                }
                                                else
                                                {
                                                    canUpdate = false;
                                                    if (msg_type == "upsert")
                                                    {
                                                        canInsert = true; // insert for upsert message
                                                    }
                                                }
                                                sqlDataReader.Close();
                                            }

                                            if (canUpdate)
                                            {
                                                string qQuery = @"
                                                SET Context_Info 0x55555
                                                UPDATE {0} SET {1} WHERE {2}";

                                                if (table_pval.StartsWith("%") ||
                                                    table_pval.EndsWith("%"))
                                                {
                                                    qQuery += " LIKE ";
                                                }
                                                else
                                                {
                                                    qQuery += "=";
                                                }

                                                if (this.isNumeric(table_pval))
                                                {
                                                    qQuery += "{3};";
                                                }
                                                else
                                                {
                                                    qQuery += "'{3}';";
                                                }

                                                qQuery = String.Format(qQuery, table_name, buildSET(table_data), table_pkey, table_pval);

                                                if (msg_type == "upsert")
                                                {
                                                    if (!this.executeNonQuery(qQuery))
                                                    {
                                                        canInsert = true;
                                                    }
                                                }
                                                else
                                                {
                                                    this.executeNonQuery(qQuery);
                                                }
                                            }
                                            else
                                            {
                                                if (msg_type == "update")
                                                {
                                                    syncError(String.Format("Cannot UPDATE because the value {0} of the key {1} does not exists in db", table_pval, table_pkey), jsonData);
                                                }
                                            }

                                            if (msg_type == "upsert")
                                            {
                                                if (canInsert)
                                                {
                                                    qTQuery = @"
                                                        SET Context_Info 0x55555
                                                        INSERT INTO {0} ({1}) VALUES ({2})";

                                                    string pKeys;
                                                    string pVals;
                                                    this.buildINSERTINTO(table_data, out pKeys, out pVals);
                                                    qTQuery = String.Format(qTQuery, table_name, pKeys, pVals);

                                                    this.executeNonQuery(qTQuery);
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            else if (msg_type == "delete")
                            {
                                string qQuery = @"
                                    SET Context_Info 0x55555
                                    DELETE FROM {0} WHERE {1}=";
                                if (this.isNumeric(table_pval))
                                {
                                    qQuery += "{2};";
                                }
                                else
                                {
                                    qQuery += "'{2}';";
                                }

                                this.executeNonQuery(String.Format(qQuery, table_name, table_pkey, table_pval));
                            }
                            else
                            {
                                syncError(String.Format("Unknown type == {0} on table {1}", msg_type, table_name), jsonData);
                            }
                        }
                    }
                }
                else
                {
                    syncError("Cannot process the json message from ebridge-cud without 'source' and 'type'", jsonData);
                }
            }
            catch (SqlException error)
            {
                if (error.Message.Contains("error: 19") ||
                    error.Message.Contains("error: 20") ||
                    error.Message.Contains("ExecuteReader") ||
                    error.Message.Contains("SHUTDOWN"))
                {
                    throw error;
                }
                else
                {
                    syncError(ECPRERROR.SqlExceptionDetail(error), jsonData);
                }
            }
            catch (Exception error)
            {
                if (error.Message.Contains("error: 19") ||
                    error.Message.Contains("error: 20") ||
                    error.Message.Contains("ExecuteReader") ||
                    error.Message.Contains("SHUTDOWN"))
                {
                    throw error;
                }
                else
                {
                    syncError(error.Message, jsonData);
                }
            }
        }