public void TestImportNewData()
{
var importInstance = new PostMigragion();
importInstance.ImportNewData();
var posts = new PostsBll().QueryAll();
posts.Should()
.HaveCount(192, "because there are only 192 rows data in the table j_jcyy_basepost of data source");
var dbUpdateLogs = new DbUpdateLogBll().QueryAll();
dbUpdateLogs.Should().HaveCount(192, "because I just insert 192 rows data to the table Posts");
var relations = new PrimaryIdRelationBll().QueryAll();
relations.Should()
.HaveCount(192, "becaust there are only 192 rows data in the table j_jcyy_basepost of data source");
var maxId = new OracleTableMaxIdBll().QuerySingle($"TableName='{nameof(J_JCYY_BASEPOST)}'");
maxId.Should().NotBeNull();
maxId?.MaxId.Should().Be("226");
}
public void TestUpdateEditedData()
{
var updateInstance = new PostMigragion();
updateInstance.UpdateEditedData();
var editedModel = new PostsBll().QuerySingle("PostName='测试修改安全监控员'");
editedModel.Should().NotBeNull();
var dbUpdateLog = new DbUpdateLogBll().QuerySingle($"TableName='{nameof(Posts)}' AND UpdateType=2");
dbUpdateLog.Should().NotBeNull();
}
public void TestUpdateEditedData()
{
var updateInstance = new DepartmentMigration();
updateInstance.UpdateEditedData();
var departs = new DepartInfoBll().QueryAll().ToList();
var editedDepart = departs.Find(d => d.DepartmentName == "呼南运用车间");
editedDepart.Should().NotBeNull();
var dbUpdateLog = new DbUpdateLogBll().QueryList("TableName='DepartInfo' AND UpdateType=2");
dbUpdateLog.Should().HaveCount(2);
}
public SyncInfo Export(SyncInfo syncInfo)
{
var dbLogMaxId = syncInfo.DbUpdateLogMaxId;
var bll = new DbSyncStatusBll();
var status = bll.QuerySingle($"[DbIdentity]='{syncInfo.DbIdentity}'");
if (status == null)
{
status = new DbSyncStatus
{
DbIdentity = syncInfo.DbIdentity.ToString(),
LastTime = DateTime.Now,
Position = dbLogMaxId,
TableName = nameof(DbUpdateLog)
};
AddClientDbStatus(status);
}
if (syncInfo.ClientWriteSuccess)
{
status.Position = dbLogMaxId;
status.LastTime = DateTime.Now;
bll.Update(status);
}
else
{
dbLogMaxId = status.Position;
}
var newData = GetNewData(dbLogMaxId);
syncInfo.ServerData = newData.Data;
syncInfo.PathList = newData.PathList;
syncInfo.ClientWriteSuccess = false;
syncInfo.ServerNewDataFlag = newData.NewDataFlag;
var dbLogBll = new DbUpdateLogBll();
syncInfo.DbUpdateLogMaxId = (int)dbLogBll.GetMaxId();
return(syncInfo);
}
public void TestImportNewData()
{
// 导入员工之前必须先导入部门、岗位
var departImport = new DepartmentMigration();
departImport.ImportNewData();
var postImport = new PostMigragion();
postImport.ImportNewData();
var testInstance = new StaffMigration();
testInstance.ImportNewData();
var totalCount = 7063;
var allStaff = new PersonInfoBll().QueryAll().ToList();
allStaff.Should().HaveCount(totalCount, $"because the total count of the data source is {totalCount}");
var single = allStaff.Find(p => p.WorkNo == "2920133");
single.Should().NotBeNull();
single.Name.Should().Be("杨凤山");
single.BirthDate.Should().Be(new DateTime(1960, 12, 1, 12, 0, 0));
single.Password.Should().Be("0133".GetMd5());
var dbLogs = new DbUpdateLogBll().QueryList($"TableName='{nameof(PersonInfo)}'");
dbLogs.Should().HaveCount(totalCount);
var relations = new PrimaryIdRelationBll().QueryAll();
relations.Should().HaveCount(totalCount);
var maxId = new OracleTableMaxIdBll().QuerySingle($"TableName='{nameof(Z_JCYY_BASEPERSONNEL)}'");
maxId.Should().NotBeNull();
maxId?.MaxId.Should().NotBeNullOrEmpty()
.And.Subject.Should().Be("404990019");
}
public void TestImportNewData()
{
TestSuite.CleanTestDb();
var importInstance = new DepartmentMigration();
importInstance.ImportNewData();
var departBll = new DepartInfoBll();
var departs = departBll.QueryAll().ToList();
departs.Should().HaveCount(37, "because the table 'Y_JCYY_BASEDEPARTMENT' in oracle has 37 rows.");
//departs.Should().Contain(depart => depart.DepartmentName == "集宁机务段");
var jnDepot = departs.Find(d => d.DepartmentName == "集宁机务段");
jnDepot.Should().NotBeNull();
jnDepot?.DepartmentName.Should().NotBeNullOrEmpty().And.Be("集宁机务段");
jnDepot?.ParentId.Should().Be(0);
var jnyyc = departs.Find(d => d.DepartmentName == "集宁运用车间");
jnyyc.Should().NotBeNull();
jnyyc?.ParentId.Should().Be(jnDepot?.Id);
var dbupdateLogBll = new DbUpdateLogBll();
var dbupdateLogs = dbupdateLogBll.QueryList($"TableName='{nameof(DepartInfo)}'").ToList();
dbupdateLogs.Should().HaveCount(37);
var relationBll = new PrimaryIdRelationBll();
var relations = relationBll.QueryAll().ToList();
relations.Should().HaveCount(37);
var maxBll = new OracleTableMaxIdBll();
var maxIdModel = maxBll.QuerySingle($"TableName='Y_JCYY_BASEDEPARTMENT'");
maxIdModel.Should().NotBeNull();
maxIdModel.MaxId.Should().Be("404100000");
}
/// <summary>
/// 处理由重复的关键字引起的重复搜索结果
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDistinct2_Click(object sender, EventArgs e)
{
var bll = new TraficKeywordsBll();
var resBll = new TraficSearchResultBll();
var dbUpdateBll = new DbUpdateLogBll();
var list = bll.QueryAll();
var searchResults = resBll.QueryList("", new string[] { "Id", "KeywordsId" });
var groups = list.GroupBy(k => k.Keywords);
foreach (var group in groups)
{
if (group.Count() > 1)
{
var ids = group.Select(k => k.Id).ToList();
var firstId = ids.First();
ids.RemoveAt(0);
// 删除重复项
var idsStr = string.Join(",", ids);
var deleteKeywodsSql = $"DELETE FROM TraficKeywords WHERE Id IN({idsStr})";
var deleteKeywordsUpdates = $"DELETE FROM DbUpdateLog WHERE TargetId IN({idsStr}) AND TableName='TraficKeywords'";
var results = searchResults.Where(r => ids.Contains(r.KeywordsId));
var resIds = results.Select(r => r.Id);
var resIdsStr = string.Join(",", resIds);
var deleteResultSql = $"DELETE FROM TraficSearchResult WHERE KeywordsId IN({idsStr})";
var deleteResultUpdates = $"DELETE FROM DbUpdateLog WHERE TargetId IN({(resIdsStr == "" ? "0" : resIdsStr)}) AND TableName='TraficSearchResult'";
bll.ExecuteTransation(
() => bll.ExecuteSql(deleteKeywodsSql) >= 0,
() => bll.ExecuteSql(deleteKeywordsUpdates) >= 0,
() => bll.ExecuteSql(deleteResultSql) >= 0,
() => bll.ExecuteSql(deleteResultUpdates) >= 0
);
}
}
}
private List <DbUpdateLog> GetDbLog(int start)
{
var logBll = new DbUpdateLogBll();
return(logBll.QueryList($"Id>${start}") as List <DbUpdateLog>);
}
private void Persistent(List <Posts> posts, List <DepartInfo> departs, List <PersonInfo> staff)
{
var staffBll = new PersonInfoBll();
var dbUpdateBll = new DbUpdateLogBll();
Func <bool>[] delegates = new Func <bool> [3];
delegates[0] = () =>
{
if (posts.Count > 0)
{
var postBll = new PostsBll();
var maxId = postBll.GetMaxId();
postBll.BulkInsert(posts);
var ids = postBll.QueryList("Id>" + maxId, new[] { nameof(Posts.Id) }).Select(p => p.Id);
var dbLogs = ids.Select(id => new DbUpdateLog {
TableName = nameof(Posts), TargetId = id, UpdateType = 1, UpdateTime = DateTime.Now
});
dbUpdateBll.BulkInsert(dbLogs);
}
return(true);
};
delegates[1] = () =>
{
if (departs.Count > 0)
{
var departBll = new DepartInfoBll();
var maxId = departBll.GetMaxId();
departBll.BulkInsert(departs);
var ids = departBll.QueryList("Id>" + maxId, new[] { nameof(DepartInfo.Id) }).Select(p => p.Id);
var dbLogs = ids.Select(id => new DbUpdateLog {
TableName = nameof(DepartInfo), TargetId = id, UpdateType = 1, UpdateTime = DateTime.Now
});
dbUpdateBll.BulkInsert(dbLogs);
}
return(true);
};
delegates[2] = () =>
{
List <Posts> _posts = new PostsBll().QueryAll().ToList();
List <DepartInfo> _departs = new DepartInfoBll().QueryAll().ToList();
staff.ForEach(s =>
{
// 根据前面用 PersonId 存储的部门名称找到此员工对应的部门
// 根据前面用 PhotoPath 存储的职务名称找到此员工对应的职务
var depart = _departs.Find(d => d.DepartmentName == s.PersonId);
var post = _posts.Find(p => p.PostName == s.PhotoPath);
s.DepartmentId = depart?.Id ?? 0;
s.PostId = post?.Id ?? 0;
s.PersonId = string.Empty;
s.PhotoPath = string.Empty;
});
var maxId = staffBll.GetMaxId();
staffBll.BulkInsert(staff);
var ids = staffBll.QueryList("Id>" + maxId, new[] { nameof(PersonInfo.Id) }).Select(p => p.Id);
var dbLogs = ids.Select(id => new DbUpdateLog {
TableName = nameof(PersonInfo), TargetId = (int)id, UpdateType = 1, UpdateTime = DateTime.Now
});
dbUpdateBll.BulkInsert(dbLogs);
return(true);
};
// 若插入失败，则尝试五次
for (var i = 0; i < 5; i++)
{
var success = staffBll.ExecuteTransation(delegates);
if (success)
{
return;
}
}
}
