public static void CreateEmptyTable(string tableName)
{
DropTable(tableName);
using (var db = new AftDbContext())
{
db.Database.ExecuteSqlCommand(string.Format("CREATE TABLE {0}([f1] [int])", tableName));
}
}
public static void CreateFieldMappings(FieldMapping fieldMapping)
{
using (var db = new AftDbContext())
{
db.FieldMappings.Add(fieldMapping);
db.SaveChanges();
}
}
public static void ClearAllSessions()
{
using (var db = new AftDbContext())
{
db.Database.ExecuteSqlCommand("delete from SyncEvents");
db.Database.ExecuteSqlCommand("delete from JobContexts");
db.Database.ExecuteSqlCommand("delete from SessionContexts");
}
}
public static void CreateDefaultSyncConfigs()
{
RemoveAllSyncConfigs();
using (var db = new AftDbContext())
{
db.SyncConfigs.Add(new SyncConfig {ObjectType = "supporter", SyncDirection = "export", Order = 1});
db.SyncConfigs.Add(new SyncConfig {ObjectType = "supporter", SyncDirection = "import", Order = 2});
db.SaveChanges();
}
}
public static void ClearAllQueues()
{
using (var db = new AftDbContext())
{
db.Database.ExecuteSqlCommand("delete from SalsaToAftQueue_Supporter");
db.Database.ExecuteSqlCommand("delete from SalsaToAftQueue_Supporter_History");
db.Database.ExecuteSqlCommand("delete from AftToSalsaQueue_Supporter");
db.Database.ExecuteSqlCommand("delete from AftToSalsaQueue_Supporter_History");
}
}
public void TrimImporterLogsOlderThan(int days)
{
var daysAgo = DateTime.Now.AddDays(days * -1).ToString("yyyy-MM-dd");
using(var db = new AftDbContext())
{
var sql = string.Format("delete from importerlogs where time_stamp < '{0}'", daysAgo);
Logger.Debug("Delete importerlogs generated before " + daysAgo);
db.Database.ExecuteSqlCommand(sql);
}
}
public List<string> VerifySalsaFields()
{
Logger.Debug("Verifying Salsa Fields...");
var result = new List<string>();
using (var db = new AftDbContext())
{
db.SyncConfigs.GroupBy(job => job.ObjectType).Select(g => g.Key).ToList()
.ForEach(objectType => VerifySalsaFields(db, objectType, result));
}
return result;
}
public IMapper GetMapper(string objectType)
{
Logger.Trace("Get mapper for " + objectType);
if (!mappers.Keys.Contains(objectType))
{
using (var db = new AftDbContext())
{
mappers[objectType] = new Mapper(objectType, db.FieldMappings.Where(map => map.ObjectType == objectType).ToList());
}
}
return mappers[objectType];
}
public List<string> VerifyQueues()
{
Logger.Debug("Verifying queue tables and history tables...");
var result = new List<string>();
using (var db = new AftDbContext())
{
var jobConfigs = db.SyncConfigs.ToList();
jobConfigs.ForEach(job =>
{
if (DbUtility.IsTableExist(job.QueueName))
VerifyFields(db, job.ObjectType, job.QueueName, job.SyncDirection, result);
else
result.Add(string.Format("Could not find the table for the queue {0}", job.QueueName));
if (DbUtility.IsTableExist(job.QueueHistoryName))
VerifyFields(db, job.ObjectType, job.QueueHistoryName, job.SyncDirection, result);
else
result.Add(string.Format("Could not find the history table {0}", job.QueueHistoryName));
});
}
return result;
}
private void VerifySalsaFields(AftDbContext db, string objectType, List<string> result)
{
var mappings = db.FieldMappings.Where(m => m.ObjectType == objectType).ToList();
if (_salsaClient.CountObjects(objectType) == 0) return; //do not verify if there is no record on salsa
var fields = _salsaClient.GetFieldList(objectType);
var missingFields = mappings.Where(m => !fields.Contains(m.SalsaField)).Select(m => m.SalsaField).ToList();
if(missingFields.Count > 0)
result.Add(string.Format("Could not find field(s) {0} of {1} from Salsa", string.Join(",", missingFields), objectType));
}
private void VerifyFields(AftDbContext db, string objectType, string queueName, string syncDirection, List<string> result)
{
var mappings = db.FieldMappings.Where(m => m.ObjectType == objectType).ToList();
var columns = DbUtility.GetColumnsInfo(queueName);
mappings.AddRange(_standardFieldMappings);
mappings.ForEach(mapping =>
{
if (columns.Any(column => CanMatch(column, mapping, syncDirection)))
return;
result.Add(String.Format("Could not find column {0} as {1} in queue {2}", mapping.AftField, mapping.DataType, queueName));
});
}
public static void DropTable(string tableName)
{
using (var db = new AftDbContext())
{
db.Database.ExecuteSqlCommand(string.Format("IF OBJECT_ID('dbo.{0}', 'U') IS NOT NULL DROP TABLE dbo.{0}", tableName));
}
}
public static void CreateSyncConfig(string objectType, string syncDirection, int order)
{
using (var db = new AftDbContext())
{
db.SyncConfigs.Add(new SyncConfig { ObjectType = objectType, SyncDirection = syncDirection, Order = order });
db.SaveChanges();
}
}
public static void ExecuteSql(string sql)
{
using (var db = new AftDbContext())
{
db.Database.ExecuteSqlCommand(sql);
}
}
public static void InsertSupporterToExportQueue(string email, string firstName, string lastName, DateTime? aft_Match_DateTime, string title, int salsaKey, int chapterKey = 0)
{
using (var db = new AftDbContext())
{
string sql = null;
if(aft_Match_DateTime.HasValue)
sql = String.Format("INSERT INTO AftToSalsaQueue_Supporter ( Email, First_Name, Last_Name, AFT_Match_DateTime, Title, SalsaKey, Chapter_KEY) VALUES ('{0}', '{1}', '{2}', '{3}','{4}', {5}, {6})", email, firstName, lastName, aft_Match_DateTime, title, salsaKey, chapterKey);
else
sql = String.Format("INSERT INTO AftToSalsaQueue_Supporter ( Email, First_Name, Last_Name, Title, SalsaKey, Chapter_KEY) VALUES ('{0}', '{1}', '{2}', '{3}','{4}', {5})", email, firstName, lastName, title, salsaKey, chapterKey);
db.Database.ExecuteSqlCommand(sql);
}
}
public static void RemoveSyncConfigForObjectType(string objectType)
{
using (var db = new AftDbContext())
{
db.Database.ExecuteSqlCommand(string.Format("delete from SyncConfigs where ObjectType='{0}'", objectType));
}
}
public static void RemoveFieldMappings()
{
using (var db = new AftDbContext())
{
db.Database.ExecuteSqlCommand("delete from FieldMappings");
db.SaveChanges();
}
}
public static void RemoveAllSyncConfigs()
{
using (var db = new AftDbContext())
{
db.Database.ExecuteSqlCommand("delete from syncEvents");
db.Database.ExecuteSqlCommand("delete from fieldMappings");
db.Database.ExecuteSqlCommand("delete from SyncConfigs");
}
}
public static void RecreateFieldMappingForTest()
{
using (var db = new AftDbContext())
{
db.Database.ExecuteSqlCommand("delete from FieldMappings");
db.SaveChanges();
new List<FieldMapping>
{
new FieldMapping{ObjectType = "supporter",AftField = "Title",SalsaField = "Title",
DataType = "string",MappingRule = MappingRules.salsaWins},
new FieldMapping{ObjectType = "supporter",AftField = "Phone",SalsaField = "Phone",
DataType = "string",MappingRule = MappingRules.aftWins},
new FieldMapping{ObjectType = "supporter",AftField = "First_Name",SalsaField = "First_Name",
DataType = "string",MappingRule = MappingRules.onlyIfBlank},
new FieldMapping{ObjectType = "supporter",AftField = "Last_Name",SalsaField = "Last_Name",
DataType = "string",MappingRule = MappingRules.onlyIfBlank},
new FieldMapping{ObjectType = "supporter",AftField = "Email",SalsaField = "Email",
DataType = "string",MappingRule = MappingRules.primaryKey},
new FieldMapping{ObjectType = "supporter",AftField = "AFT_Match_DateTime",SalsaField = "cdb_match_date",
DataType = "datetime",MappingRule = MappingRules.aftWins},
new FieldMapping{ObjectType = "supporter",AftField = "Chapter_KEY",SalsaField = "chapter_KEY",
DataType = "int",MappingRule = MappingRules.writeOnlyNewMembership},
new FieldMapping{ObjectType = "chapter",AftField = "Name",SalsaField = "Name",
DataType = "string",MappingRule = MappingRules.aftWins},
new FieldMapping{ObjectType = "supporter",AftField = "Last_Modified",SalsaField = "Last_Modified",
DataType = "datetime",MappingRule = MappingRules.readOnly},
}.ForEach(f => db.FieldMappings.Add(f));
db.SaveChanges();
}
}
public SyncSession(NotificationService notificationService)
{
_notificationService = notificationService;
_jobs = new List<ISyncJob>();
_db = new AftDbContext();
}
private void ConfigSync()
{
using(var db = new AftDbContext())
{
var syncConfigs = db.SyncConfigs.OrderBy(c => c.Order).ToList();
syncConfigs.ForEach(c => _syncSession.AddJob(CreateSyncJob(c)));
}
}