/// <summary>
/// 同步数据
/// </summary>
/// <param name="all">同步所有数据</param>
/// <returns>影响行数</returns>
public int Synchronous(bool all = false)
{
var result = 0;
var connectionString = ConfigurationUtil.AppSettings("K8Connection", BaseSystemInfo.EncryptDbConnection);
string conditional = null;
if (!all)
{
var id = 0;
var commandText = "SELECT MAX(id) FROM BaseOrganization WHERE id < 1000000";
var maxObject = DbHelper.ExecuteScalar(commandText);
if (maxObject != null)
{
id = int.Parse(maxObject.ToString());
}
conditional = " AND ID > " + id.ToString();
}
result = ImportK8Organization(connectionString, conditional);
return(result);
}
/// <summary>
/// 同步数据
/// </summary>
/// <param name="all">同步所有数据</param>
/// <returns>影响行数</returns>
public int Synchronous(bool all = false)
{
var result = 0;
var connectionString = ConfigurationUtil.AppSettings("K8Connection", BaseSystemInfo.EncryptDbConnection);
string condition = null;
if (!all)
{
/*
* int id = 0;
* string commandText = "SELECT MAX(id) FROM BaseStaff WHERE id < 1000000";
* Object maxObject = DbHelper.ExecuteScalar(commandText);
* if (maxObject != null)
* {
* id = int.Parse(maxObject.ToString());
* }
* conditional = " AND Id > " + id.ToString();
*/
}
result = Synchronous(connectionString, condition);
return(result);
}
/// <summary>
/// 导入K8系统用户账户
/// </summary>
/// <param name="connectionString">数据库连接</param>
/// <param name="condition">条件，不需要同步所有的数据</param>
/// <returns></returns>
public int Synchronous(string connectionString = null, string condition = null)
{
var result = 0;
if (string.IsNullOrEmpty(connectionString))
{
connectionString = ConfigurationUtil.AppSettings("K8Connection", BaseSystemInfo.EncryptDbConnection);
}
if (!string.IsNullOrEmpty(connectionString))
{
// 01：可以从k8里读取公司、用户、密码的。
var dbHelper = DbHelperFactory.Create(CurrentDbType.Oracle, connectionString);
var staffManager = new BaseStaffManager(UserInfo);
if (string.IsNullOrEmpty(condition))
{
// 不不存在的用户删除掉tab_user是远程试图
/*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 10000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 10000 )";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 20000 AND id >= 10000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id <20000 AND id >= 10000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 30000 AND id >= 20000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 30000 AND id >= 20000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 40000 AND id >= 30000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 40000 AND id >= 30000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 50000 AND id >= 40000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 50000 AND id >= 40000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 60000 AND id >= 50000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 60000 AND id >= 50000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 70000 AND id >= 60000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 70000 AND id >= 60000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 80000 AND id >= 70000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 80000 AND id >= 70000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 90000 AND id >= 80000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 90000 AND id >= 80000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 100000 AND id >= 90000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 100000 AND id >= 90000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 110000 AND id >= 100000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 110000 AND id >= 100000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 120000 AND id >= 110000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 120000 AND id >= 110000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 130000 AND id >= 120000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 130000 AND id >= 120000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 200000 AND id >= 130000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 200000 AND id >= 130000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 300000 AND id >= 200000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 300000 AND id >= 200000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 400000 AND id >= 300000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 400000 AND id >= 300000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 600000 AND id >= 400000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 600000 AND id >= 400000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 800000 AND id >= 600000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 800000 AND id >= 600000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*
* commandText = "DELETE FROM " + BaseStaffEntity.CurrentTableName + " WHERE id < 1000000 AND id >= 800000 AND id NOT IN (SELECT id FROM TAB_EMPLOYEE WHERE id < 1000000 AND id >= 800000)";
* staffManager.ExecuteNonQuery(commandText);
* System.Console.WriteLine(commandText);
*/
}
// 01：可以从k8里读取公司、用户、密码的。
var commandText = "SELECT * FROM TAB_EMPLOYEE WHERE 1 = 1 ";
if (!string.IsNullOrEmpty(condition))
{
commandText += condition + " ORDER BY UPDATETIME DESC";
}
else
{
// 只更新今天有变化的数据就可以了
// commandText += " AND TO_CHAR(SYSDATE, 'yy-mm-dd') = TO_CHAR(UPDATETIME, 'yy-mm-dd') ";
}
Console.WriteLine(commandText);
//var sTaffExpressManager = new BaseUserExpressManager(UserInfo);
//var userManager = new BaseUserManager(UserInfo);
//var userLogonManager = new BaseUserLogonManager(UserInfo);
//var dataReader = DbHelper.ExecuteReader(commandText);
//if (dataReader != null && !dataReader.IsClosed)
//{
// while (dataReader.Read())
// {
// result += ImportStaff(dataReader, staffManager, sTaffExpressManager, userManager,
// userLogonManager);
// }
// dataReader.Close();
//}
// 设置用户的公司主键，有时候不需要同步所有的账户，只同步增量账户
// 设置用户的公司主键
// commandText = @"UPDATE basestaff SET companyid = (SELECT MAX(Id) FROM baseorganization WHERE baseorganization.name = basestaff.companyname AND baseorganization.Id < 1000000) WHERE companyId IS NULL OR companyId = ''";
// 公司名称重复的数据需要找出来
ExecuteNonQuery(commandText);
Console.WriteLine(commandText);
}
return(result);
}
/// <summary>
/// 导入K8系统网点信息
/// </summary>
/// <param name="connectionString">数据库连接</param>
/// <param name="conditional">条件，不需要同步所有的数据</param>
/// <returns>影响行数</returns>
public int ImportK8Organization(string connectionString = null, string conditional = null)
{
// delete from baseorganization where id < 1000000
var result = 0;
if (string.IsNullOrEmpty(connectionString))
{
connectionString = ConfigurationUtil.AppSettings("K8Connection", BaseSystemInfo.EncryptDbConnection);
}
if (!string.IsNullOrEmpty(connectionString))
{
// 01：可以从k8里读取公司、用户、密码的。
var dbHelper = DbHelperFactory.Create(CurrentDbType.Oracle, connectionString);
var organizationManager = new Business.BaseOrganizationManager(this.DbHelper, this.UserInfo);
// 不不存在的组织机构删除掉TAB_SITE是远程试图
var commandText = "DELETE FROM BASEORGANIZE WHERE id < 1000000 AND id NOT IN (SELECT id FROM TAB_SITE)";
organizationManager.ExecuteNonQuery(commandText);
// 同步数据
commandText = "SELECT * FROM TAB_SITE WHERE BL_NOT_INPUT IS NULL OR BL_NOT_INPUT = 0 ";
if (!string.IsNullOrEmpty(conditional))
{
commandText += conditional;
}
var dataReader = dbHelper.ExecuteReader(commandText);
if (dataReader != null && !dataReader.IsClosed)
{
while (dataReader.Read())
{
// 这里需要从数据库读取、否则容易造成丢失数据
var entity = organizationManager.GetEntity(dataReader["ID"].ToString());
if (entity == null)
{
entity = new BaseOrganizationEntity();
//entity.Id = dr["ID"].ToString();
}
entity.Code = dataReader["SITE_CODE"].ToString();
if (string.IsNullOrEmpty(entity.ParentName) ||
!entity.ParentName.Equals(dataReader["SUPERIOR_SITE"].ToString()))
{
entity.ParentName = dataReader["SUPERIOR_SITE"].ToString();
entity.ParentId = 0;
}
entity.Name = dataReader["SITE_NAME"].ToString();
entity.ShortName = dataReader["SITE_NAME"].ToString();
entity.CategoryCode = dataReader["TYPE"].ToString();
entity.OuterPhone = dataReader["PHONE"].ToString();
entity.Fax = dataReader["FAX"].ToString();
entity.Province = dataReader["PROVINCE"].ToString();
entity.City = dataReader["CITY"].ToString();
entity.District = dataReader["RANGE_NAME"].ToString();
entity.CostCenter = dataReader["SUPERIOR_FINANCE_CENTER"].ToString();
entity.Area = dataReader["BIG_AREA_NAME"].ToString();
entity.CompanyName = dataReader["SITE1_NAME"].ToString();
if (!string.IsNullOrEmpty(dataReader["ORDER_BY"].ToString()))
{
entity.SortCode = int.Parse(dataReader["ORDER_BY"].ToString());
}
// 02：可以把读取到的数据能写入到用户中心的。
result = organizationManager.UpdateEntity(entity);
if (result == 0)
{
organizationManager.AddEntity(entity);
}
}
dataReader.Close();
}
// 填充 parentname
// select * from baseorganization where parentname is null
commandText = @"update baseorganization set parentname = (select fullname from baseorganization t where t.id = baseorganization.parentId) where parentname is null";
ExecuteNonQuery(commandText);
// 填充 parentId
// select * from baseorganization where parentId is null
commandText = @"UPDATE baseorganization SET parentId = (SELECT Id FROM baseorganization t WHERE t.fullname = baseorganization.parentname) WHERE parentId IS NULL";
// 100000 以下是基础数据的，100000 以上是通用权限管理系统的
// UPDATE baseorganization SET parentId = (SELECT Id FROM baseorganization t WHERE t.fullname = baseorganization.parentname) WHERE parentId < 100000
ExecuteNonQuery(commandText);
// 更新错误数据
commandText = @"UPDATE baseorganization SET parentId = null WHERE id = parentId";
ExecuteNonQuery(commandText);
// 设置员工的公司主键
commandText = @"UPDATE baseuser SET companyid = (SELECT MAX(Id) FROM baseorganization WHERE baseorganization.fullname = baseuser.companyname AND baseorganization.Id < 1000000) WHERE companyId IS NULL OR companyId = ''";
ExecuteNonQuery(commandText);
}
return(result);
}