private void executeQueryLogs(string cText)
{
using (SqlConnection _sqlConnection = new SqlConnection(config.cprDatabaseConnectionString))
{
SqlDataReader sqlDataReader = this.executeQuery(cText, _sqlConnection);
if (sqlDataReader != null)
{
if (sqlDataReader.HasRows)
{
foreach (CPRRowData cprRowData in this.SerializeCPR(sqlDataReader))
{
string ecpr_id = cprRowData.customer;
cprRowData.customer = this.config.cprCustomerCode;
string jsonConvert = JsonConvert.SerializeObject(cprRowData, this.jsonSerializerSettings);
//DataChangesCache.AddItem(GetHashString(jsonConvert), jsonConvert);
DataChangesCache.AddItem(String.Format("ECPR_CUD_ID:{0}:{1}", this.tableName, ecpr_id), jsonConvert);
if (cprRowData.type == "update" && this.config.diff_tables != null)
{
System.Threading.Tasks.Task.Factory.StartNew(() =>
{
CPRDataAnalysis.compareDataDiff(this.tableName, cprRowData.data, cprRowData.datetime);
});
}
}
;
}
sqlDataReader.Close();
}
}
}
public void sendMessageByKey(string pKey)
{
string jsonData = (string)DataChangesCache.GetItem(pKey);
if (jsonData == null)
{
return;
}
DataChangesCache.setPOSTing(pKey);
if (pKey.Contains("ECPR_CUD_ID:")) // e.g.: ECPR_CUD_ID:<table_name>:1
{
string[] ecpr_vals = pKey.Split(':');
int ecpr_status = 0;
if (this.publishMessage(jsonData))
{
// set ecpr_id.ecpr_status = 2 for the next delete.
Console.WriteLine("SENT SUCCESSFULLY, SET ecpr_status=2 FOR ecpr_id={0} TABLE ecpr_{1}_logs", ecpr_vals[2], ecpr_vals[1]);
ecpr_status = 2;
}
else
{
Console.WriteLine("SENDING FAILED, WILL TRY AGAIN!");
Console.WriteLine(jsonData);
// this.logger.write( "Sending CUD message failed, will try again later !", Logger.LOGLEVEL.ERROR);
// set ecpr_id.ecpr_status = 0 for the next retry.
Console.WriteLine("SENT FAILED, SET ecpr_status=0 FOR ecpr_id={0} TABLE ecpr_{1}_logs", ecpr_vals[2], ecpr_vals[1]);
}
try
{
this.executeNonQuery(String.Format("UPDATE [dbo].[ecpr_{0}_logs] SET ecpr_status={1} WHERE ecpr_id={2}", ecpr_vals[1], ecpr_status, ecpr_vals[2]));
DataChangesCache.RemoveItem(pKey);
} catch (Exception error)
{
#if DEBUG
Console.WriteLine(error);
#endif
}
}
else
{
if (this.publishMessage(jsonData))
{
DataChangesCache.RemoveItem(pKey);
}
else
{
this.logger.write("Sending CONFLICT/ERROR message failed, will try again later !", Logger.LOGLEVEL.ERROR);
}
}
DataChangesCache.removePOSTing(pKey);
}
private void get_UTC_timezone()
{
/* @returns:
*
* {
* "type": "timezone",
* "data": {
* "offset": "-08:00:00",
* "utc_now": "02/16/2016 06:28:06 AM",
* "now": "02/15/2016 10:28:06 PM"
* }
* }
*
*/
var data = new Dictionary <string, object>();
var df = "MM/dd/yyyy hh:mm:ss tt";
var offset = TimeZoneInfo.Local.GetUtcOffset(DateTime.UtcNow);
data["offset"] = offset;
/*
* Get the date and time for the current moment expressed as coordinated
* universal time (UTC).
*/
data["utc_now"] = DateTime.UtcNow.ToString(df);
/*
* Get the date and time for the current moment, adjusted to the local
* time zone.
*/
data["now"] = DateTime.Now.ToString(df);
Dictionary <string, dynamic> pMsg = new Dictionary <string, dynamic> {
{ "type", "timezone" },
{ "data", data }
};
DataChangesCache.AddItem("ADMIN:UTC_TIMEZONE",
JsonConvert.SerializeObject(pMsg, Formatting.Indented));
}
private void eventsProcess()
{
var waitHandles = new WaitHandle[] { this._stopEvent, this._inProcessEvent };
while (!this._stopEvent.WaitOne(0))
{
var handle_code = WaitHandle.WaitAny(waitHandles);
switch (handle_code)
{
case 0:
this.logger.write(String.Format("Disconnecting to database {0}", this.config.cprDatabase), Logger.LOGLEVEL.INFO);
if (this.sqlConnection != null && (this.sqlConnection.State == ConnectionState.Open))
{
this.sqlConnection.Close();
}
break;
case 1:
if (syncControlCenter != null && serviceControlCenter != null)
{
if ((!config.radius_only_customer && !syncControlCenter.isSubscribed) ||
!serviceControlCenter.isSubscribed ||
(!config.radius_only_customer && !fullsyncControlCenter.isSubscribed) ||
!this.logger.isSubscribed)
{
this.logger.write("Some of rabbitmq connections has been lost, re-connecting ...", Logger.LOGLEVEL.WARN);
if (!config.radius_only_customer && !syncControlCenter.isConnecting)
{
syncControlCenter.reConnect();
if (syncControlCenter.isSubscribed)
{
syncControlCenter.consumerInit();
}
}
if (!serviceControlCenter.isConnecting)
{
serviceControlCenter.reConnect();
if (serviceControlCenter.isSubscribed)
{
serviceControlCenter.consumerInit();
}
}
if (!config.radius_only_customer && !fullsyncControlCenter.isConnecting)
{
fullsyncControlCenter.reConnect();
}
if (!this.logger.isConnecting)
{
this.logger.ensureConnect();
}
}
}
foreach (string pKey in DataChangesCache.Keys())
{
if (!DataChangesCache.isPOSTing(pKey))
{
#if (DEBUG)
Console.WriteLine("SEND MESSAGE FOR KEY = {0}", pKey);
#endif
this.syncControlCenter.sendMessageByKey(pKey);
}
}
/*
* var mem_using = (GC.GetTotalMemory(true) / 1024) / 1024;
* if (mem_using > 1024) // Limit == 1024MB of memory
* {
* this.logger.write( "ECPR is consuming lot of memory, restart it!.", Logger.LOGLEVEL.FATAL);
* Thread.Sleep(5000);
* Environment.Exit(1);
* }*/
GC.Collect();
this._inProcessEvent.Reset();
this._eventsTimer.Start();
break;
}
}
}
public void collect_current_schema(Dictionary <string, List <string> > cprTables,
List <string> cprTableOrder, string tableEqualName = null)
{
List <CPRSchemaData> schemaList = new List <CPRSchemaData>();
List <string> pKeys = new List <string>(cprTables.Keys);
foreach (string tableName in cprTableOrder)
{
if (tableEqualName != null && tableName.ToLower() != tableEqualName.ToLower())
{
continue;
}
if (!pKeys.Contains(tableName))
{
this.logger.write(String.Format("Cannot fetch schema because table order name {0} does not exists in tables list of ecpr config.", tableName), Logger.LOGLEVEL.ERROR);
continue;
}
string inFields;
if (cprTables[tableName].Count > 0)
{
inFields = string.Join(",", cprTables[tableName].ToArray()).ToLower();
}
else
{
inFields = string.Join(",", this.reCheckTableFields(tableName)).ToLower();
}
string strQuery = String.Format("SELECT column_name,data_type FROM information_schema.columns WHERE table_name = '{0}'", tableName);
using (SqlConnection _sqlConnection = new SqlConnection(config.cprDatabaseConnectionString))
{
SqlDataReader sqlDataReader = this.executeQuery(strQuery, _sqlConnection);
if (sqlDataReader != null)
{
if (sqlDataReader.HasRows)
{
Dictionary <string, string> schemaFields = new Dictionary <string, string>();
foreach (DbDataRecord dataRecord in sqlDataReader)
{
string columnName = dataRecord.GetString(0).ToLower();
if (inFields.Contains(columnName))
{
string dataType = DataTypeMapping.map(dataRecord.GetString(1));
schemaFields.Add(columnName, dataType);
}
}
schemaList.Add(new CPRSchemaData
{
customer = this.config.cprCustomerCode,
fields = schemaFields,
datetime = Utils.getUnixTimeStamp(),
table = tableName,
uuid = Guid.NewGuid().ToString()
});
}
sqlDataReader.Close();
}
}
}
if (schemaList.Count > 0)
{
if (schemaList.Count == 1)
{
DataChangesCache.AddItem(Guid.NewGuid().ToString(), JsonConvert.SerializeObject(schemaList[0], Formatting.Indented));
}
else
{
DataChangesCache.AddItem("COLLECTTABLESCHEMAS", JsonConvert.SerializeObject(schemaList, Formatting.Indented));
}
this.logger.write(String.Format("SCHEMA PUSH :: {0}", tableName), Logger.LOGLEVEL.INFO);
}
else
{
if (tableEqualName != null)
{
this.logger.write(String.Format("Cannot fetch schema because table order name '{0}' does not exists in tables list of ecpr config.", tableEqualName), Logger.LOGLEVEL.ERROR);
}
}
schemaList = null;
pKeys = null;
}
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);
}
}
}
